pkg_ken_migapi.pck

数据迁移使用的ETL程序包

                          ,RATE_CLASS_DEFAULT      
                          ,RATE_CLASS_SPECIAL      
                          ,EXRATE_CLASS            
                          ,PLAN_ID_DISCOUNT        
                          ,PLAN_ID_CREDIT          
                          ,ACCOUNT_STATUS          
                          ,ACCOUNT_STATUS_DT       
                          ,DISTR_CHAN              
                          ,SALES_CODE              
                          ,MKT_CODE                
                          ,SIC_CODE                
                          ,IN_USE                  
                          ,NO_BILL                 
                          ,COLLECTION_INDICATOR    
                          ,COLLECTION_STATUS     
                          ,COLLECTION_HISTORY     
                          ,VIP_CODE               
                          ,PURCHASE_ORDER         
                          ,CODEWORD               
                          ,REMARK                
                          ,CUST_EMAIL             
                          ,STATEMENT_TO_EMAIL     
                          ,CHG_WHO                
                          ,CHG_DATE               
                          ,DATE_CREATED           
                          ,DATE_ACTIVE            
                          ,DATE_INACTIVE         
                          ,NEW_CUST_BANK_SORT_CODE 
                          ,CLEARING_HOUSE_ID      
                          ,REV_RCV_COST_CTR      
                          ,OWNING_COST_CTR        
                          ,ACCT_SEG_ID            
                          ,CONVERTED              
                          ,BANK_AGENCY_NAME        
                          ,BANK_AGENCY_CODE       
                          ,CHARGE_THRESHOLD        
                          ,THRESHOLD              
                          ,CYCLICAL_THRESHOLD     
                          ,USG_AMT_LIMIT         
                          ,USG_UNITS_LIMIT        
                          ,REGULATORY_ID          
                          ,GLOBAL_CONTRACT_STATUS  
                          ,ALT_LNAME             
                          ,ALT_FNAME             
                          ,ALT_COMPANY_NAME     
                          ,BANK_CODE             
                          ,BANK_ACCOUNT_TYPE      
                          ,BRANCH_NAME           
                          ,BRANCH_CODE           
                          ,GENDER                
                          ,DEPT                   
                          ,TITLE  
                       )
                VALUES (
                           :V_ACCOUNT_NO            
                          ,:V_PARENT_ID             
                          ,:V_CHILD_COUNT          
                          ,:V_HIERARCHY_ID         
                          ,:V_TIE_CODE              
                          ,:V_BILL_SEQUENCE_NUM     
                          ,:V_CURRENCY_CODE         
                          ,:V_LANGUAGE_CODE         
                          ,:V_ACCOUNT_TYPE          
                          ,:V_ACCOUNT_CATEGORY      
                          ,:V_PREV_CUTOFF_DATE      
                          ,:V_PREV_BILL_DATE          
                          ,:V_NEXT_BILL_DATE          
                          ,:V_PREV_BILL_REFNO         
                          ,:V_PREV_BILL_REF_RESETS    
                          ,:V_PREV_BALANCE_REFNO      
                          ,:V_PREV_BALANCE_REF_RESETS 
                          ,:V_BILL_NAME_PRE           
                          ,:V_BILL_FNAME              
                          ,:V_BILL_MINIT              
                          ,:V_BILL_LNAME              
                          ,:V_BILL_NAME_GENERATION    
                          ,:V_BILL_TITLE              
                          ,:V_BILL_COMPANY            
                          ,:V_CUST_ADDRESS1           
                          ,:V_CUST_ADDRESS2           
                          ,:V_CUST_ADDRESS3           
                          ,:V_CUST_CITY               
                          ,:V_CUST_STATE              
                          ,:V_CUST_ZIP                
                          ,:V_CUST_COUNTRY_CODE       
                          ,:V_CUST_FRANCHISE_TAX_CODE 
                          ,:V_CUST_GEOCODE            
                          ,:V_CUST_COUNTY             
                          ,:V_CUST_PHONE1             
                          ,:V_CUST_PHONE2             
                          ,:V_BILL_ADDRESS1           
                          ,:V_BILL_ADDRESS2           
                          ,:V_BILL_ADDRESS3           
                          ,:V_BILL_CITY               
                          ,:V_BILL_STATE              
                          ,:V_BILL_ZIP                
                          ,:V_BILL_COUNTRY_CODE       
                          ,:V_BILL_FRANCHISE_TAX_CODE 
                          ,:V_BILL_COUNTY             
                          ,:V_BILL_GEOCODE            
                          ,:V_SSN                     
                          ,:V_CONTACT1_NAME           
                          ,:V_CONTACT2_NAME           
                          ,:V_CONTACT1_PHONE          
                          ,:V_CONTACT2_PHONE          
                          ,:V_CUST_FAXNO              
                          ,:V_STATEMENT_TO_FAXNO      
                          ,:V_DEFAULT_CCARD_ID        
                          ,:V_DEFAULT_CCARD_ID_SERV   
                          ,:V_CUST_BANK_SORT_CODE     
                          ,:V_CUST_BANK_ACC_NUM       
                          ,:V_CUST_BANK_ACC_NAME      
                          ,:V_ALT_BANK_ACC_NUM        
                          ,:V_CREDIT_THRESH           
                          ,:V_CRED_STATUS             
                          ,:V_CREDIT_RATING           
                          ,:V_DISCONNECT_REASON       
                          ,:V_DISC_RCV_OPT            
                          ,:V_THRESH_REF              
                          ,:V_PAY_METHOD              
                          ,:V_BILL_PERIOD             
                          ,:V_BILLING_FREQUENCY       
                          ,:V_BILL_FMT_OPT            
                          ,:V_BILL_DISP_METH          
                          ,:V_BILL_HOLD_CODE          
                          ,:V_MSG_GRP_ID              
                          ,:V_INSERT_GRP_ID           
                          ,:V_RATE_CLASS_DEFAULT      
                          ,:V_RATE_CLASS_SPECIAL      
                          ,:V_EXRATE_CLASS            
                          ,:V_PLAN_ID_DISCOUNT        
                          ,:V_PLAN_ID_CREDIT          
                          ,:V_ACCOUNT_STATUS          
                          ,:V_ACCOUNT_STATUS_DT       
                          ,:V_DISTR_CHAN              
                          ,:V_SALES_CODE              
                          ,:V_MKT_CODE                
                          ,:V_SIC_CODE                
                          ,:V_IN_USE                  
                          ,:V_NO_BILL                 
                          ,:V_COLLECTION_INDICATOR    
                          ,:V_COLLECTION_STATUS     
                          ,:V_COLLECTION_HISTORY     
                          ,:V_VIP_CODE               
                          ,:V_PURCHASE_ORDER         
                          ,:V_CODEWORD               
                          ,:V_REMARK                
                          ,:V_CUST_EMAIL             
                          ,:V_STATEMENT_TO_EMAIL     
                          ,:V_CHG_WHO                
                          ,:V_CHG_DATE               
                          ,:V_DATE_CREATED           
                          ,:V_DATE_ACTIVE            
                          ,:V_DATE_INACTIVE         
                          ,:V_NEW_CUST_BANK_SORT_CODE 
                          ,:V_CLEARING_HOUSE_ID      
                          ,:V_REV_RCV_COST_CTR      
                          ,:V_OWNING_COST_CTR        
                          ,:V_ACCT_SEG_ID            
                          ,:V_CONVERTED              
                          ,:V_BANK_AGENCY_NAME        
                          ,:V_BANK_AGENCY_CODE       
                          ,:V_CHARGE_THRESHOLD        
                          ,:V_THRESHOLD              
                          ,:V_CYCLICAL_THRESHOLD     
                          ,:V_USG_AMT_LIMIT         
                          ,:V_USG_UNITS_LIMIT        
                          ,:V_REGULATORY_ID          
                          ,:V_GLOBAL_CONTRACT_STATUS  
                          ,:V_ALT_LNAME             
                          ,:V_ALT_FNAME             
                          ,:V_ALT_COMPANY_NAME     
                          ,:V_BANK_CODE             
                          ,:V_BANK_ACCOUNT_TYPE      
                          ,:V_BRANCH_NAME           
                          ,:V_BRANCH_CODE           
                          ,:V_GENDER                
                          ,:V_DEPT                   
                          ,:V_TITLE     
                       )'
        using t_cmf(indx).ACCOUNT_NO, t_cmf(indx).PARENT_ID, t_cmf(indx).CHILD_COUNT, t_cmf(indx).HIERARCHY_ID, t_cmf(indx).TIE_CODE, t_cmf(indx).BILL_SEQUENCE_NUM, t_cmf(indx).CURRENCY_CODE, t_cmf(indx).LANGUAGE_CODE, t_cmf(indx).ACCOUNT_TYPE, t_cmf(indx).ACCOUNT_CATEGORY, t_cmf(indx).PREV_CUTOFF_DATE, t_cmf(indx).PREV_BILL_DATE, t_cmf(indx).NEXT_BILL_DATE, t_cmf(indx).PREV_BILL_REFNO, t_cmf(indx).PREV_BILL_REF_RESETS, t_cmf(indx).PREV_BALANCE_REFNO, t_cmf(indx).PREV_BALANCE_REF_RESETS, t_cmf(indx).BILL_NAME_PRE, t_cmf(indx).BILL_FNAME, t_cmf(indx).BILL_MINIT, t_cmf(indx).BILL_LNAME, t_cmf(indx).BILL_NAME_GENERATION, t_cmf(indx).BILL_TITLE, t_cmf(indx).BILL_COMPANY, t_cmf(indx).CUST_ADDRESS1, t_cmf(indx).CUST_ADDRESS2, t_cmf(indx).CUST_ADDRESS3, t_cmf(indx).CUST_CITY, t_cmf(indx).CUST_STATE, t_cmf(indx).CUST_ZIP, t_cmf(indx).CUST_COUNTRY_CODE, t_cmf(indx).CUST_FRANCHISE_TAX_CODE, t_cmf(indx).CUST_GEOCODE, t_cmf(indx).CUST_COUNTY, t_cmf(indx).CUST_PHONE1, t_cmf(indx).CUST_PHONE2, t_cmf(indx).BILL_ADDRESS1, t_cmf(indx).BILL_ADDRESS2, t_cmf(indx).BILL_ADDRESS3, t_cmf(indx).BILL_CITY, t_cmf(indx).BILL_STATE, t_cmf(indx).BILL_ZIP, t_cmf(indx).BILL_COUNTRY_CODE, t_cmf(indx).BILL_FRANCHISE_TAX_CODE, t_cmf(indx).BILL_COUNTY, t_cmf(indx).BILL_GEOCODE, t_cmf(indx).SSN, t_cmf(indx).CONTACT1_NAME, t_cmf(indx).CONTACT2_NAME, t_cmf(indx).CONTACT1_PHONE, t_cmf(indx).CONTACT2_PHONE, t_cmf(indx).CUST_FAXNO, t_cmf(indx).STATEMENT_TO_FAXNO, t_cmf(indx).DEFAULT_CCARD_ID, t_cmf(indx).DEFAULT_CCARD_ID_SERV, t_cmf(indx).CUST_BANK_SORT_CODE, t_cmf(indx).CUST_BANK_ACC_NUM, t_cmf(indx).CUST_BANK_ACC_NAME, t_cmf(indx).ALT_BANK_ACC_NUM, t_cmf(indx).CREDIT_THRESH, t_cmf(indx).CRED_STATUS, t_cmf(indx).CREDIT_RATING, t_cmf(indx).DISCONNECT_REASON, t_cmf(indx).DISC_RCV_OPT, t_cmf(indx).THRESH_REF, t_cmf(indx).PAY_METHOD, t_cmf(indx).BILL_PERIOD, t_cmf(indx).BILLING_FREQUENCY, t_cmf(indx).BILL_FMT_OPT, t_cmf(indx).BILL_DISP_METH, t_cmf(indx).BILL_HOLD_CODE, t_cmf(indx).MSG_GRP_ID, t_cmf(indx).INSERT_GRP_ID, t_cmf(indx).RATE_CLASS_DEFAULT, t_cmf(indx).RATE_CLASS_SPECIAL, t_cmf(indx).EXRATE_CLASS, t_cmf(indx).PLAN_ID_DISCOUNT, t_cmf(indx).PLAN_ID_CREDIT, t_cmf(indx).ACCOUNT_STATUS, t_cmf(indx).ACCOUNT_STATUS_DT, t_cmf(indx).DISTR_CHAN, t_cmf(indx).SALES_CODE, t_cmf(indx).MKT_CODE, t_cmf(indx).SIC_CODE, t_cmf(indx).IN_USE, t_cmf(indx).NO_BILL, t_cmf(indx).COLLECTION_INDICATOR, t_cmf(indx).COLLECTION_STATUS, t_cmf(indx).COLLECTION_HISTORY, t_cmf(indx).VIP_CODE, t_cmf(indx).PURCHASE_ORDER, t_cmf(indx).CODEWORD, t_cmf(indx).REMARK, t_cmf(indx).CUST_EMAIL, t_cmf(indx).STATEMENT_TO_EMAIL, t_cmf(indx).CHG_WHO, t_cmf(indx).CHG_DATE, t_cmf(indx).DATE_CREATED, t_cmf(indx).DATE_ACTIVE, t_cmf(indx).DATE_INACTIVE, t_cmf(indx).NEW_CUST_BANK_SORT_CODE, t_cmf(indx).CLEARING_HOUSE_ID, t_cmf(indx).REV_RCV_COST_CTR, t_cmf(indx).OWNING_COST_CTR, t_cmf(indx).ACCT_SEG_ID, t_cmf(indx).CONVERTED, t_cmf(indx).BANK_AGENCY_NAME, t_cmf(indx).BANK_AGENCY_CODE, t_cmf(indx).CHARGE_THRESHOLD, t_cmf(indx).THRESHOLD, t_cmf(indx).CYCLICAL_THRESHOLD, t_cmf(indx).USG_AMT_LIMIT, t_cmf(indx).USG_UNITS_LIMIT, t_cmf(indx).REGULATORY_ID, t_cmf(indx).GLOBAL_CONTRACT_STATUS, t_cmf(indx).ALT_LNAME, t_cmf(indx).ALT_FNAME, t_cmf(indx).ALT_COMPANY_NAME, t_cmf(indx).BANK_CODE, t_cmf(indx).BANK_ACCOUNT_TYPE, t_cmf(indx).BRANCH_NAME, t_cmf(indx).BRANCH_CODE, t_cmf(indx).GENDER, t_cmf(indx).DEPT, t_cmf(indx).TITLE;
    
      indx := indx + 1;
      EXIT WHEN indx = t_cmf.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_cmf;

  /**************************** CMF_JOIN *************************/
  procedure prc_ins_cj(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'cmf_join 
                       (
                         ACCOUNT_NO
                       )
                VALUES (
                         :V_ACCOUNT_NO
                       )'
        using t_cj(indx).ACCOUNT_NO;
      indx := indx + 1;
      EXIT WHEN indx = t_cj.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_cj;

  /************************ CMF_EXT_DATA  *****************************************************************/

  procedure prc_ins_ced(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'CMF_EXT_DATA 
                       (
                         ACCOUNT_NO     
                        ,PARAM_ID       
                        ,PARAM_VALUE    
                        ,PARAM_DATATYPE 
                       )
                VALUES (
                         :V_ACCOUNT_NO     
                        ,:V_PARAM_ID       
                        ,:V_PARAM_VALUE    
                        ,:V_PARAM_DATATYPE    
                       )'
        using t_ced(indx).ACCOUNT_NO, t_ced(indx).PARAM_ID, t_ced(indx).PARAM_VALUE, t_ced(indx).PARAM_DATATYPE;
      indx := indx + 1;
      EXIT WHEN indx = t_ced.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_ced;

  /********************  BILL_INVOICE *************************************************/
  procedure prc_ins_bi(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'BILL_INVOICE 
                       (
                            ACCOUNT_NO                
                            ,BILL_REF_NO             
                            ,BILL_REF_RESETS         
                            ,BILL_SEQUENCE_NUM       
                            ,PROCESS_NUM             
                            ,PREV_BILL_REFNO         
                            ,PREV_BILL_REF_RESETS    
                            ,PREV_BALANCE_REFNO      
                            ,PREV_BALANCE_REF_RESETS 
                            ,ZIP                     
                            ,FROM_DATE               
                            ,TO_DATE                 
                            ,NEXT_TO_DATE            
                            ,PREP_DATE               
                            ,TAX_DATE                
                            ,STATEMENT_DATE          
                            ,PAYMENT_DUE_DATE        
                            ,PREV_PPDD               
                            ,PREV_CUTOFF_DATE        
                            ,BILL_PERIOD             
                            ,CURRENCY_CODE           
                            ,PAY_METHOD              
                            ,BILL_DISP_METH          
                            ,TAX_JOURNAL_STATUS