CREATE OR REPLACE
PROCEDURE prc_isdn_bearer_id_seq_24
  --(v_run_id NUMBER default null)

IS
  v_sql              VARCHAR2(4000);
  v_run_id           NUMBER :=0909;
  v_bearer_seq_no    VARCHAR2(10) ;
  v_sql_insert       VARCHAR2(4000);
  v_isdn_bearer_id   VARCHAR2(30);
  v_rec_count        NUMBER         :=0;
  v_chk_rec_count    NUMBER         :=0;
  v_chk_berrec_count NUMBER         :=0;
  v_pm_bearer_count  NUMBER         :=0;
  v_err_code         VARCHAR2(30)   :='MC_ERR_FM18';
  v_err_msg          VARCHAR2(4000) := 'Valid Bearer Id not found';
TYPE t_type_bearer_id
IS
  TABLE OF VARCHAR2(30) ;
  v_type_bearer_id t_type_bearer_id;
TYPE t_type_pm_bearer_count
IS
  TABLE OF VARCHAR2(30) ;
  v_type_pm_bearer_count t_type_pm_bearer_count;
TYPE t_type_bearer_chnls
IS
  TABLE OF VARCHAR2(30) ;
  v_type_bearer_chnls t_type_bearer_chnls;
BEGIN
  --
  -- get the list of all the billing accounts by joining
  -- driver_product_profile dpp,src_css_installation_products cip,src_css_cp_note ccn,lov_isdn li

  EXECUTE Immediate 'DELETE FROM fld_map_isdn_bearer WHERE run_id = ' || v_run_Id;
  FOR I IN
  ( SELECT DISTINCT bill_accnt_key,
    pm.district_code district_code ,
    pm.billing_account_no billing_account_no ,
    pm.inst_sequence_no inst_sequence_no,
    COUNT(pm_bearers) pm_bearers
  FROM product_map_isdn_24 pm,
    (SELECT 'CSS'
      || cip.district_code
      || cip.billing_account_no AS bill_accnt_key ,
      cip.billing_account_no ,
      cip.district_code ,
      Cip.Inst_Sequence_No
    FROM DRIVER_PRODUCT_PROFILE_24 DPP,
      SRC_CSS_CP_NOTE_24 CCN,
      SRC_CSS_INST_PRODUCTS_24 CIP,
      SRC_CSS_INSTALLATION_24 CI
    WHERE NVL(ci.WLR3_FLAG,'X') <>'Y'
    AND cip.DISTRICT_CODE        =ci.DISTRICT_CODE
    AND cip.billing_account_no   = ci.billing_account_no
    AND dpp.product_id           = cip.product_code
    AND dpp.contract_type        = cip.contract_type
    AND NVL(trim(dpp.mc_flag),0) = NVL(trim(cip.maintenance_contract),0)
    AND dpp.special_info LIKE '%Bearer%'
    AND dpp.prod_cat ='ISDN'
      --  AND SUBSTR(bcl.bill_account_key,4,2) = cip.district_code
      --    AND SUBSTR(bcl.bill_account_key,6) = cip.billing_account_no
      --   AND bcl.run_id = v_run_id
      --    AND bcl.bac_status ='CSS PENDING CHECK - SUCCESS'
      --    AND bcl.failure_flag='N'
      --     And Bcl.Stage0_Or_Isdn_Flag = 'Y'
    AND Cip.District_Code                     = Ccn.District_Code(+)
    AND Cip.Billing_Account_No                = Ccn.Billing_Account_No(+)
    AND Cip.Inst_Sequence_No                  = Ccn.Inst_Sequence_No(+)
    AND Cip.Product_Code                      = Ccn.Product_Code(+)
    AND Cip.Contract_Type                     = Ccn.Contract_Type(+)
    AND NVL(trim(cip.maintenance_contract),0) = NVL(trim(ccn.maintenance_contract(+)),0)
    ORDER BY ccn.ts_last_updated DESC
    )s
  WHERE pm.district_code   =s.district_code
  AND pm.billing_account_no=s.billing_account_no
  AND pm.inst_sequence_no  =s.inst_sequence_no
  GROUP BY s.bill_accnt_key,
    pm.district_code,
    pm.billing_account_no,
    pm.inst_sequence_no
  )
  LOOP

    -- to get the bearer id
    -- to check if the product note has the prefix from lov prefix table
    -- also check if the product note has bearer prefix along with 5/6 digits numeric value
    --
    -- SELECT COUNT(pm.pm_bearers)
    -- INTO v_pm_bearer_count
    -- FROM product_map_isdn_24 pm
    -- WHERE pm.district_code   =I.district_code
    -- AND pm.billing_account_no=I.billing_account_no
    -- AND pm.inst_sequence_no  =I.inst_sequence_no
    --     and rownum=1;
    --  group by  pm.district_code,pm.billing_account_no,pm.inst_sequence_no;
    dbms_output.put_line(I.pm_bearers);
   IF I.pm_bearers > '1' THEN
       v_sql :=
    'SELECT substr(regexp_replace((REGEXP_SUBSTR(TRIM(ccn.product_note)||'' '',''MIGR:''||li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1})''
    ,1,1,''i'')),''-$''),6,LENGTH(REGEXP_SUBSTR(TRIM(product_note)||'' '',''MIGR:''|| li.filter_condition||
    ''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1})'',1,1,''i'')) -1),
    substr(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''),2
    ,length(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''))-2)
    FROM bac_lov_lookup_24 li, src_css_cp_note_24 ccn,DRIVER_PRODUCT_PROFILE_24 DPP
    WHERE li.variable_name =''FM.BearerID''
    AND INSTR(upper(ccn.product_note),li.filter_condition)>=1
    AND DPP.product_id = ccn.Product_Code
    AND  DPP.contract_type = ccn.contract_type
    AND NVL(trim(dpp.mc_flag),0) =  NVL(trim(ccn.maintenance_contract(+)),0)
    AND dpp.special_info like ''%Bearer%''
    AND dpp.prod_cat =''ISDN''
    --AND NVL(trim(ccn.district_code),0) = NVL(trim(:1),0)
    --AND ccn.billing_account_no = :2
    AND NVL(trim(ccn.inst_sequence_no),0) = ''0001''
    --NVL(trim(:3),0)
    AND TRIM(ccn.maintenance_contract) IS NULL
    AND REGEXP_LIKE(TRIM(ccn.product_note)||'' '',''MIGR:''|| li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1})'',''i'')
    AND (to_number( substr(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''),2
    ,length(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''))-2))
    between 8 and 30)
    AND  length(trim(translate(to_number( substr(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''),2
    ,length(REGEXP_SUBSTR(TRIM(ccn.product_note),''-''||''([[:digit:]]{1,100})([^[:digit:]]{1})'',1,1,''i''))-2)),'' +-.0123456789'','' '')))
    is null
    ORDER BY ccn.ts_last_updated DESC'*/
    --  ;
    --  dbms_output.put_line(v_sql);
   ELSE
    -- dbms_output.put_line(I.pm_bearers);
    v_sql :=
    'SELECT SUBSTR(REGEXP_SUBSTR(TRIM(ccn.product_note)||'' '', li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1}|[[:space:]]{1})'',1,1,''i''),            
1,Length(REGEXP_SUBSTR(TRIM(ccn.product_note)||'' '', li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1}|[[:space:]]{1})'',1,1,''i''))-1)                          
FROM bac_lov_lookup_24 li, src_css_cp_note_24 ccn,DRIVER_PRODUCT_PROFILE_24 DPP                        
WHERE li.variable_name =''FM.BearerID''                                                         
AND INSTR(upper(ccn.product_note),li.filter_condition)>=1                                                         
AND DPP.product_id = ccn.Product_Code                                                         
AND  DPP.contract_type = ccn.contract_type                                                         
AND NVL(trim(dpp.mc_flag),0) =  NVL(trim(ccn.maintenance_contract(+)),0)                                                         
AND dpp.special_info like ''%Bearer%''                                                            
AND dpp.prod_cat =''ISDN''                             
AND NVL(trim(ccn.district_code),0) = NVL(trim(:1),0)                             
AND ccn.billing_account_no = :2                             
AND NVL(trim(ccn.inst_sequence_no),0) =NVL(trim(:3),0)                             
AND TRIM(ccn.maintenance_contract) IS NULL                             
AND REGEXP_LIKE(TRIM(ccn.product_note)||'' '', li.filter_condition||''([[:digit:]]{5}|[[:digit:]]{6})([^[:digit:]]{1}|[[:space:]]{1}){1}'',''i'')                          
ORDER BY ccn.ts_last_updated DESC'
    ;
    dbms_output.put_line(v_sql);
    dbms_output.put_line(I.inst_sequence_no);
    dbms_output.put_line(I.district_code);
    dbms_output.put_line(I.billing_account_no);
    dbms_output.put_line(I.pm_bearers);
    dbms_output.put_line('why it is 0');
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_type_bearer_id,v_type_bearer_chnls USING I.district_code,I.billing_account_no,I.inst_sequence_no;
    --  END IF;
    -- dbms_output.put_line(v_type_bearer_id(I));
    dbms_output.put_line('why it is 0'||v_type_bearer_id.COUNT);
    IF v_type_bearer_id.COUNT >0 THEN
      FOR idx                IN v_type_bearer_id.FIRST .. v_type_bearer_id.LAST
      LOOP
        v_bearer_seq_no := '01';
        dbms_output.put_line(v_type_bearer_id(idx));
        dbms_output.put_line(v_type_bearer_chnls(idx));
        --    end;
        --  fm_load_bearer_chnls := v_type_bearer_chnls(idx);
        -- check bacs where same bearer is found on multiple bacs
        SELECT COUNT(1)
        INTO v_chk_rec_count
        FROM fld_map_isdn_bearer fmib
        WHERE fmib.district_code    = I.district_code
        AND fmib.billing_account_no = I.billing_account_no
        AND fmib.inst_sequence_no   = I.inst_sequence_no
        AND run_id                  = v_run_id;
        IF v_chk_rec_count          = 0 THEN
          --
          -- insert a record into fld_map_isdn_bearer if all the conditions are satisfied for bearer id

          INSERT
          INTO fld_map_isdn_bearer_24
            (
              run_id ,
              bill_accnt_key ,
              billing_account_no ,
              district_code ,
              inst_sequence_no ,
              fm_bearerid ,
              fm_bearerseqno ,
              fm_bearerchnls
            )
            VALUES
            (
              v_run_id ,
              I.bill_accnt_key ,
              I.billing_account_no ,
              I.district_code ,
              I.inst_sequence_no ,
              UPPER(v_type_bearer_id(idx)) ,
              v_bearer_seq_no ,
              UPPER(v_type_bearer_chnls(idx))
            );
        END IF;
      END LOOP;
     
    ELSE
      INSERT
        /*+ APPEND */
      INTO invalid_fm_bac_24
        (
          RUN_ID,
          RECORD_ID,
          BILL_ACCOUNT_KEY,
          ERROR_CODE,
          ERROR_REASON
        )
        VALUES
        (
          v_run_id,
          RECORD_ID_FM_SEQ.nextval,
          I.bill_accnt_key,
          v_err_code,
          v_err_msg
        );
    END IF;
  END LOOP;
  -- to handle same bearer id on multiple bacs beg
  --
  INSERT
  INTO invalid_fm_bac_24
    (
      RUN_ID,
      RECORD_ID,
      BILL_ACCOUNT_KEY,
      ERROR_CODE,
      ERROR_REASON
    )
  SELECT v_run_id,
    RECORD_ID_FM_SEQ.nextval,
    bill_accnt_key,
    v_err_code,
    v_err_msg
  FROM fld_map_isdn_bearer_24 fmib,
    (SELECT fm_bearerid,
      COUNT(1)
    FROM fld_map_isdn_bearer
    WHERE run_id = v_run_id
    GROUP BY fm_bearerid
    HAVING COUNT(1) >1
    ) fm_b
  WHERE fmib.fm_bearerid = fm_b.fm_bearerid
  AND fmib.run_id        = v_run_id;
  DELETE
  FROM fld_map_isdn_bearer_24 fmb
  WHERE fmb.fm_bearerid IN
    (SELECT fmib.fm_bearerid
    FROM fld_map_isdn_bearer_24 fmib
    WHERE fmib.fm_bearerid = fmb.fm_bearerid
    AND fmib.run_id        = v_run_id
    GROUP BY fmib.fm_bearerid
    HAVING COUNT(fmib.fm_bearerid) >1
    )
  AND fmb.run_id = v_run_id;
  --
  -- to handle same bearer id on multiple bacs end
  --
  --
  -- update the status in bac_candidate_list table for all the billing accounts that got rejected during Bearer id computation
  --
  --  UPDATE bac_candidate_list bcl
  --    SET bcl.bac_status = 'PRODUCT TABLES EXTRACTION - FAILED'
  --       , bcl.failure_flag ='Y'
  --   WHERE EXISTS ( SELECT 1
  --                               FROM invalid_fm_bac ifb
  --                          WHERE ifb.bill_account_key = bcl.bill_account_key
  --                               AND ifb.run_id = bcl.run_id
  --                              AND ifb.error_code = v_err_code
  --                              AND ifb.error_reason = v_err_msg
  --                        )
  --      AND bcl.run_id = v_run_id
  --    AND bcl.stage0_or_isdn_flag = 'Y';
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  NULL;
  --   DBMS_OUTPUT.PUT_LINE('Error While Executing the Procedure --prc_compute_isdn ' || sqlerrm || ' for Run Id ' || v_run_id);
END prc_isdn_bearer_id_seq_24;

Bill acct key	district code	bill accnt num	inst no	pm_bearers
CSSSW38485041	SW	        38485041	0001	1
CSSWM38396668	WM	        38396668	0001	1
CSSWM38199380	WM	        38199380	0001	1
CSSWM34962678	WM	        34962678	0001	3
CSSSM38710752	SM	        38710752	0001	2
CSSWM39100299	WM	        39100299	0001	1