pkg_ken_migapi.pck

数据迁移使用的ETL程序包

                            ,ACCOUNT_STATUS          
                            ,IMAGE_REQ               
                            ,IMAGE_DONE              
                            ,SPECIAL_CODE            
                            ,PREP_TASK               
                            ,PREP_STATUS             
                            ,FORMAT_STATUS           
                            ,DISPATCH_COUNT          
                            ,DISPATCH_DATE           
                            ,FILE_NAME               
                            ,START_OFFSET            
                            ,END_OFFSET              
                            ,PAGE_COUNT              
                            ,BILL_HOLD_CODE          
                            ,PREP_ERROR_CODE         
                            ,FORMAT_ERROR_CODE       
                            ,BACKOUT_STATUS          
                            ,COPY_TYPE               
                            ,ORIG_BILL_REFNO         
                            ,ORIG_BILL_REF_RESETS    
                            ,COLLECTION_HISTORY      
                            ,JNL_EARNED_THRU_DT      
                            ,JNL_STATUS              
                            ,TEST_FLAG               
                            ,ARCH_FLAG               
                            ,INTERIM_BILL_FLAG       
                            ,INCLUDE_NRC             
                            ,INCLUDE_RC              
                            ,INCLUDE_ADJ             
                            ,INCLUDE_USAGE           
                            ,INCLUDE_BMF             
                            ,LANGUAGE_CODE           
                            ,CONVERTED               
                            ,WARM_BILL_FLAG          
                            ,BILL_ORDER_NUMBER                     
                        
                       )
                VALUES (
                             :V_ACCOUNT_NO                
                            ,:V_BILL_REF_NO             
                            ,:V_BILL_REF_RESETS         
                            ,:V_BILL_SEQUENCE_NUM       
                            ,:V_PROCESS_NUM             
                            ,:V_PREV_BILL_REFNO         
                            ,:V_PREV_BILL_REF_RESETS    
                            ,:V_PREV_BALANCE_REFNO      
                            ,:V_PREV_BALANCE_REF_RESETS 
                            ,:V_ZIP                     
                            ,:V_FROM_DATE               
                            ,:V_TO_DATE                 
                            ,:V_NEXT_TO_DATE            
                            ,:V_PREP_DATE               
                            ,:V_TAX_DATE                
                            ,:V_STATEMENT_DATE          
                            ,:V_PAYMENT_DUE_DATE        
                            ,:V_PREV_PPDD               
                            ,:V_PREV_CUTOFF_DATE        
                            ,:V_BILL_PERIOD             
                            ,:V_CURRENCY_CODE           
                            ,:V_PAY_METHOD              
                            ,:V_BILL_DISP_METH          
                            ,:V_TAX_JOURNAL_STATUS      
                            ,:V_ACCOUNT_STATUS          
                            ,:V_IMAGE_REQ               
                            ,:V_IMAGE_DONE              
                            ,:V_SPECIAL_CODE            
                            ,:V_PREP_TASK               
                            ,:V_PREP_STATUS             
                            ,:V_FORMAT_STATUS           
                            ,:V_DISPATCH_COUNT          
                            ,:V_DISPATCH_DATE           
                            ,:V_FILE_NAME               
                            ,:V_START_OFFSET            
                            ,:V_END_OFFSET              
                            ,:V_PAGE_COUNT              
                            ,:V_BILL_HOLD_CODE          
                            ,:V_PREP_ERROR_CODE         
                            ,:V_FORMAT_ERROR_CODE       
                            ,:V_BACKOUT_STATUS          
                            ,:V_COPY_TYPE               
                            ,:V_ORIG_BILL_REFNO         
                            ,:V_ORIG_BILL_REF_RESETS    
                            ,:V_COLLECTION_HISTORY      
                            ,:V_JNL_EARNED_THRU_DT      
                            ,:V_JNL_STATUS              
                            ,:V_TEST_FLAG               
                            ,:V_ARCH_FLAG               
                            ,:V_INTERIM_BILL_FLAG       
                            ,:V_INCLUDE_NRC             
                            ,:V_INCLUDE_RC              
                            ,:V_INCLUDE_ADJ             
                            ,:V_INCLUDE_USAGE           
                            ,:V_INCLUDE_BMF             
                            ,:V_LANGUAGE_CODE           
                            ,:V_CONVERTED               
                            ,:V_WARM_BILL_FLAG          
                            ,:V_BILL_ORDER_NUMBER       
                       
                       )'
        using t_bi(indx).ACCOUNT_NO, t_bi(indx).BILL_REF_NO, t_bi(indx).BILL_REF_RESETS, t_bi(indx).BILL_SEQUENCE_NUM, t_bi(indx).PROCESS_NUM, t_bi(indx).PREV_BILL_REFNO, t_bi(indx).PREV_BILL_REF_RESETS, t_bi(indx).PREV_BALANCE_REFNO, t_bi(indx).PREV_BALANCE_REF_RESETS, t_bi(indx).ZIP, t_bi(indx).FROM_DATE, t_bi(indx).TO_DATE, t_bi(indx).NEXT_TO_DATE, t_bi(indx).PREP_DATE, t_bi(indx).TAX_DATE, t_bi(indx).STATEMENT_DATE, t_bi(indx).PAYMENT_DUE_DATE, t_bi(indx).PREV_PPDD, t_bi(indx).PREV_CUTOFF_DATE, t_bi(indx).BILL_PERIOD, t_bi(indx).CURRENCY_CODE, t_bi(indx).PAY_METHOD, t_bi(indx).BILL_DISP_METH, t_bi(indx).TAX_JOURNAL_STATUS, t_bi(indx).ACCOUNT_STATUS, t_bi(indx).IMAGE_REQ, t_bi(indx).IMAGE_DONE, t_bi(indx).SPECIAL_CODE, t_bi(indx).PREP_TASK, t_bi(indx).PREP_STATUS, t_bi(indx).FORMAT_STATUS, t_bi(indx).DISPATCH_COUNT, t_bi(indx).DISPATCH_DATE, t_bi(indx).FILE_NAME, t_bi(indx).START_OFFSET, t_bi(indx).END_OFFSET, t_bi(indx).PAGE_COUNT, t_bi(indx).BILL_HOLD_CODE, t_bi(indx).PREP_ERROR_CODE, t_bi(indx).FORMAT_ERROR_CODE, t_bi(indx).BACKOUT_STATUS, t_bi(indx).COPY_TYPE, t_bi(indx).ORIG_BILL_REFNO, t_bi(indx).ORIG_BILL_REF_RESETS, t_bi(indx).COLLECTION_HISTORY, t_bi(indx).JNL_EARNED_THRU_DT, t_bi(indx).JNL_STATUS, t_bi(indx).TEST_FLAG, t_bi(indx).ARCH_FLAG, t_bi(indx).INTERIM_BILL_FLAG, t_bi(indx).INCLUDE_NRC, t_bi(indx).INCLUDE_RC, t_bi(indx).INCLUDE_ADJ, t_bi(indx).INCLUDE_USAGE, t_bi(indx).INCLUDE_BMF, t_bi(indx).LANGUAGE_CODE, t_bi(indx).CONVERTED, t_bi(indx).WARM_BILL_FLAG, t_bi(indx).BILL_ORDER_NUMBER;
    
      indx := indx + 1;
      EXIT WHEN indx = t_bi.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_bi;

  /****************** CUSTOMER_SERVICE_CENTERS **********************/
  procedure prc_ins_csc(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'CUSTOMER_SERVICE_CENTERS 
                       (
                           ACCOUNT_NO           
                          ,SERVICE_CENTER_ID    
                          ,SERVICE_CENTER_TYPE  
                          ,CHG_WHO              
                          ,CHG_DATE
                       )
                VALUES (
                           :V_ACCOUNT_NO           
                          ,:V_SERVICE_CENTER_ID    
                          ,:V_SERVICE_CENTER_TYPE  
                          ,:V_CHG_WHO              
                          ,:V_CHG_DATE 
                       )'
        using t_csc(indx).ACCOUNT_NO, t_csc(indx).SERVICE_CENTER_ID, t_csc(indx).SERVICE_CENTER_TYPE, t_csc(indx).CHG_WHO, t_csc(indx).CHG_DATE;
      indx := indx + 1;
      EXIT WHEN indx = t_csc.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_csc;

  /*************  CUSTOMER_ID_ACCT_MAP  ********************************/
  procedure prc_ins_ciam(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'CUSTOMER_ID_ACCT_MAP                                                                                                                                                        
                      ( 
                         EXTERNAL_ID
                        ,ACCOUNT_NO
                        ,EXTERNAL_ID_TYPE
                        ,IS_CURRENT
                        ,ACTIVE_DATE
                        ,INACTIVE_DATE                                                                                                                                                                            
                                                                                                                                                                                                    
                      )                                                                                                                                                                             
               VALUES (  
                         :V_EXTERNAL_ID
                        ,:V_ACCOUNT_NO
                        ,:V_EXTERNAL_ID_TYPE
                        ,:V_IS_CURRENT
                        ,:V_ACTIVE_DATE
                        ,:V_INACTIVE_DATE                                                                                                                                                                               
                                                                                                                                                                                                    
                      )'
        using t_ciam(indx).external_id, t_ciam(indx).account_no, t_ciam(indx).external_id_type, t_ciam(indx).IS_CURRENT, t_ciam(indx).active_date, t_ciam(indx).inactive_date;
      indx := indx + 1;
      EXIT WHEN indx = t_ciam.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_ciam;

  /************ CMF_BALANCE ***********************************/

  procedure prc_ins_cb(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'CMF_BALANCE                         
                        (                                              
                           ACCOUNT_NO            
                          ,CURRENCY_CODE        
                          ,BILL_REF_NO          
                          ,BILL_REF_RESETS      
                          ,PPDD_DATE            
                          ,ORIG_PPDD_DATE       
                          ,CLOSED_DATE          
                          ,NEW_CHARGES          
                          ,NET_NEW_CHARGES      
                          ,TOTAL_DUE            
                          ,TOTAL_ADJ            
                          ,TOTAL_PAID           
                          ,BALANCE_DUE          
                          ,DISPUTE_AMT          
                          ,LATE_EXEMPT_CHARGES  
                          ,COLLECTION_INDICATOR 
                          ,CHG_DATE             
                          ,CHG_WHO              
                          ,GL_AMOUNT            
                          ,CONVERTED            
                          ,NEW_CHARGE_CREDITS   
                                               
                        )                                              
                 VALUES ( 
                           :V_ACCOUNT_NO            
                          ,:V_CURRENCY_CODE        
                          ,:V_BILL_REF_NO          
                          ,:V_BILL_REF_RESETS      
                          ,:V_PPDD_DATE            
                          ,:V_ORIG_PPDD_DATE       
                          ,:V_CLOSED_DATE          
                          ,:V_NEW_CHARGES          
                          ,:V_NET_NEW_CHARGES      
                          ,:V_TOTAL_DUE            
                          ,:V_TOTAL_ADJ            
                          ,:V_TOTAL_PAID           
                          ,:V_BALANCE_DUE          
                          ,:V_DISPUTE_AMT          
                          ,:V_LATE_EXEMPT_CHARGES  
                          ,:V_COLLECTION_INDICATOR 
                          ,:V_CHG_DATE             
                          ,:V_CHG_WHO              
                          ,:V_GL_AMOUNT            
                          ,:V_CONVERTED            
                          ,:V_NEW_CHARGE_CREDITS                                                      
                        )'
        using t_cb(indx).ACCOUNT_NO, t_cb(indx).CURRENCY_CODE, t_cb(indx).BILL_REF_NO, t_cb(indx).BILL_REF_RESETS, t_cb(indx).PPDD_DATE, t_cb(indx).ORIG_PPDD_DATE, t_cb(indx).CLOSED_DATE, t_cb(indx).NEW_CHARGES, t_cb(indx).NET_NEW_CHARGES, t_cb(indx).TOTAL_DUE, t_cb(indx).TOTAL_ADJ, t_cb(indx).TOTAL_PAID, t_cb(indx).BALANCE_DUE, t_cb(indx).DISPUTE_AMT, t_cb(indx).LATE_EXEMPT_CHARGES, t_cb(indx).COLLECTION_INDICATOR, t_cb(indx).CHG_DATE, t_cb(indx).CHG_WHO, t_cb(indx).GL_AMOUNT, t_cb(indx).CONVERTED, t_cb(indx).NEW_CHARGE_CREDITS;
    
      indx := indx + 1;
      EXIT WHEN indx = t_cb.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_cb;

  /******************* CMF_BALANCE_DETAIL ***************/

  procedure prc_ins_cbd(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'CMF_BALANCE_DETAIL                                   
                          (                                                      
                             ACCOUNT_NO                  
                            ,CURRENCY_CODE              
                            ,BILL_REF_NO                
                            ,BILL_REF_RESETS            
                            ,PPDD_DATE                  
                            ,ORIG_PPDD_DATE             
                            ,CLOSED_DATE                
                            ,NEW_CHARGES                
                            ,NET_NEW_CHARGES            
                            ,TOTAL_DUE                  
                            ,TOTAL_ADJ                  
                            ,TOTAL_PAID                 
                            ,BALANCE_DUE                
                            ,DISPUTE_AMT                
                            ,LATE_EXEMPT_CHARGES        
                            ,COLLECTION_INDICATOR       
                            ,CHG_DATE                   
                            ,CHG_WHO                    
                            ,GL_AMOUNT                  
                            ,REFINANCE_TRACKING_ID      
                            ,REFINANCE_TRACKING_ID_SERV 
                            ,OPEN_ITEM_ID               
                            ,EXTERNAL_ID