prc_nrc.prc

数据迁移使用的ETL程序包

create or replace procedure prc_nrc(l_Release IN VARCHAR2) as
  l_CommitCount      number(10) := 0;
  l_ServerID         EXTERNAL_ID_ACCT_MAP.server_id%TYPE; --服务器标识
  l_AccountNo        EXTERNAL_ID_ACCT_MAP.account_no%TYPE;
  l_mig_db           log_load_status.mig_db%type;
  l_TRACKING_ID      number(10);
  l_NrcId            nrc_id_comp.nrc_id%type;
  l_ViewId           number(20);
  l_SubscrNo         customer_id_equip_map.subscr_no%type;
  l_RowId            number(10);
  l_RowId1           number(10);
  l_flag             number(6);
  l_MigDbCat         varchar2(12 char);
  l_ExternalId       varchar2(40 char);
  l_TRACKING_VIEW_ID number(12);
  l_SrcID            src_inr_batch_nrc.src_id%type;
  l_ServId           src_inr_batch_nrc.serv_id%type;
  l_OpenItemID       src_inr_batch_nrc.open_item_id%type;
  l_NrcEffectiveDate src_inr_batch_nrc.nrc_effective_date%type;
  l_NrcAmount        src_inr_batch_nrc.nrc_amount%type;
  l_AcctNbr          src_inr_batch_nrc.acct_nbr%type;
  l_MigSchema        CFG_KEN_MSA.MIG_SCHEMA%TYPE; --CUSTOMER的username
  l_TypeIdNrc        number(10);
  l_MigDbAdm         varchar2(20 char);

  l_ProName     varchar2(20);
  v_ErrorCode   NUMBER; --Code for the error
  v_ErrorMsg    VARCHAR2(2000); --Message text for the error
  v_currentUser VARCHAR2(20); --Current database user
  v_Information VARCHAR2(100); --Information about the error
begin
  select distinct mig_db
    into l_MigDbCat
    from log_load_status
   where mig_schema = 'catalog.';

  select distinct mig_db
    into l_MigDbAdm
    from log_load_status
   where mig_schema = 'admin.';

  for c_ckm in (select distinct city
                  from cfg_ken_ms
                 where release = l_Release) loop
    declare
      type c_nrc is ref cursor;
      rec_nrc c_nrc;
    begin
      OPEN rec_nrc for
        select row_id,
               src_id,
               acct_nbr,
               serv_id,
               open_item_id,
               nrc_effective_date,
               nrc_amount
          from src_inr_batch_nrc
         where status = 'R'
           and src_id like c_ckm.city || '%';
      <<NEXTLOOP01555>>
      LOOP
        BEGIN
          FETCH rec_nrc
            INTO l_RowId, l_SrcID, l_AcctNbr, l_ServId, l_OpenItemID, l_NrcEffectiveDate, l_NrcAmount;
          EXIT WHEN rec_nrc%NOTFOUND;
        EXCEPTION
          WHEN OTHERS THEN
            RAISE;
        END;
      
        Begin
          l_RowId1     := l_RowId;
          l_ExternalId := pkg_ken_util.FUN_ENCODE(l_ServId,
                                                  c_ckm.city,
                                                  'SI');
          BEGIN
            EXECUTE IMMEDIATE 'select distinct subscr_no,account_no,server_id from arbor.external_id_equip_map' ||
                              l_MigDbCat ||
                              ' where external_id =:1 and external_id_type =3'
              into l_SubscrNo, l_AccountNo, l_ServerID
              using l_ExternalId;
          EXCEPTION
            when no_data_found or too_many_rows then
              raise;
          END;
        
          /*      pkg_ken_util.PRC_PARENT_INFO('SRC_ACCT_STATUS',
          l_SrcID,
          l_AcctNbr,
          l_AccountNo,
          l_ServerID,
          l_ServerUser);*/
        
          /*          EXECUTE IMMEDIATE 'select distinct mig_db from log_load_status where mig_schema =:l_ServerUser'
          into l_mig_db
          using l_ServerUser;*/
        
          EXECUTE IMMEDIATE 'select mig_db, mig_schema from cfg_ken_msa where server_id = :1'
            INTO l_mig_db, l_MigSchema
            USING l_ServerID;
        
          EXECUTE IMMEDIATE 'select ' || l_MigSchema ||
                            'NRC_SEQ.nextval  from DUAL'
            into l_TRACKING_ID;
        
          EXECUTE IMMEDIATE 'select ' || l_MigSchema ||
                            'NRC_VIEW_SEQ.nextval  from DUAL'
            into l_TRACKING_VIEW_ID;
        
          l_ViewId := l_TRACKING_VIEW_ID * 1000 + l_ServerID;
          EXECUTE IMMEDIATE 'select distinct nrc_id from  nrc_id_comp where obs_id =:1'
            into l_NrcId
            using l_OpenItemID;
        
          EXECUTE IMMEDIATE 'SELECT type_id_nrc FROM arbor.NRC_TRANS_DESCR ' ||
                            l_MigDbAdm || ' WHERE type_id_nrc =:1'
            INTO l_TypeIdNrc
            USING l_NrcId;
        
          --nrc_key  
          EXECUTE IMMEDIATE 'insert into arbor.nrc_key' || l_mig_db ||
                            ' (  
                                   TRACKING_ID         
                                  ,TRACKING_ID_SERV   
                                  ,BILL_REF_NO        
                                  ,BILL_REF_RESETS    
                                  ,REQUEST_STATUS     
                                  ,CREATE_DT          
                                  ,REVIEW_DT          
                                  ,LAST_REVIEWED_BY   
                                  ,SUPERVISOR_NAME    
                                  ,ARCH_FLAG 
                                  )
                                 values
                                 (
                                  :l_TRACKING_ID
                                 ,:l_ServerID     
                                 ,0
                                 ,0
                                 ,1
                                 ,sysdate
                                 ,sysdate
                                 ,''MIG''
                                 ,''MIG''
                                 ,0
                                 )'
            using l_TRACKING_ID, l_ServerID;
        
        --nrc_view
          EXECUTE IMMEDIATE 'insert into arbor.nrc_view' || l_mig_db || ' (
                                   VIEW_ID                              
                                  ,VIEW_STATUS                         
                                  ,VIEW_CREATED_DT                     
                                  ,VIEW_EFFECTIVE_DT                   
                                  ,INTENDED_VIEW_EFFECTIVE_DT          
                                  ,PREV_VIEW_ID                        
                                  ,TRACKING_ID                         
                                  ,TRACKING_ID_SERV                    
                                  ,TYPE_ID_NRC                         
                                  ,BILLING_ACCOUNT_NO                  
                                  ,OPEN_ITEM_ID                        
                                  ,PARENT_SUBSCR_NO                    
                                  ,PARENT_SUBSCR_NO_RESETS             
                                  ,PARENT_TRACKING_ID                  
                                  ,PARENT_TRACKING_ID_SERV             
                                  ,ELEMENT_ID                          
                                  ,TRANSACT_DATE                       
                                  ,EFFECTIVE_DATE                      
                                  ,ANNOTATION                          
                                  ,ANNOTATION2                         
                                  ,NRC_CATEGORY                        
                                  ,NO_BILL                             
                                  ,CURRENCY_CODE                       
                                  ,RATE                                
                                  ,RATE_DT                             
                                  ,ORDER_NUMBER                        
                                  ,CUSTOMER_ORDER_NUMBER               
                                  ,BILL_ORDER_NUMBER                   
                                  ,CCARD_ID                            
                                  ,CCARD_ID_SERV                       
                                  ,SHIP_FROM_GEOCODE                   
                                  ,SHIP_TO_GEOCODE                     
                                  ,CHG_WHO                             
                                  ,CHG_DT                              
                                  ,DATE_NRC_JOURNALABLE                
                                  ,TOTAL_INSTALLMENTS                  
                                  ,CURRENT_INSTALLMENT                 
                                  ,SALES_CHANNEL_ID                    
                                  ,TAX_PKG_INST_ID                     
                                  ,TAX_TYPE_CODE                       
                                  ,FEDERAL_TAX                         
                                  ,STATE_TAX                           
                                  ,COUNTY_TAX                          
                                  ,CITY_TAX                            
                                  ,OTHER_TAX                           
                                  ,SALES_ID                            
                                  ,CONTRACT_ASSOCIATION_TYPE           
                                  ,CONTRACT_TRACKING_ID                
                                  ,CONTRACT_TRACKING_ID_SERV