pkg_ken_migapi.pck

数据迁移使用的ETL程序包

    t_pcm.delete;
    t_p.delete;
    t_pv.delete;
    t_pk.delete;
    t_cp.delete;
    t_cpc.delete;
    t_cce.delete;
    t_sk.delete;
    t_sv.delete;
    t_service.delete;
    t_sed.delete;
    t_sb.delete;
    t_ej.delete;
    t_abs.delete;
    t_ciemk.delete;
    t_ciemv.delete;
    t_ciem.delete;
    t_ss.delete;
    t_eiemv.delete;
    t_eiem.delete;
    t_eiemk.delete;
  
    /* ins_recs_p.delete;
    ins_recs_pk.delete;*/
  
  end;

  procedure prc_clear_ca as
  begin
    t_eiam.delete;
    t_sl.delete;
    t_cmf.delete;
    t_cj.delete;
    t_ced.delete;
    t_csc.delete;
    t_ciam.delete;
    t_cb.delete;
    t_cbd.delete;
    t_csh.delete;
  
  end;
  procedure prc_clear_ba as
  begin
    t_eiam.delete;
    t_sl.delete;
    t_cmf.delete;
    t_cj.delete;
    t_ced.delete;
    t_bi.delete;
    t_csc.delete;
    t_ciam.delete;
    t_cb.delete;
    t_cbd.delete;
    t_csh.delete;
  end;
  procedure prc_clear_si as
  begin
    t_sk.delete;
    t_sv.delete;
    t_service.delete;
    t_sed.delete;
    t_sb.delete;
    t_ej.delete;
    t_abs.delete;
    t_ciemk.delete;
    t_ciemv.delete;
    t_ciem.delete;
    t_ss.delete;
    t_eiemv.delete;
    t_eiem.delete;
    t_eiemk.delete;
  
  end;
  /*clean prod rated table*/
  procedure prc_clear_prod as
  begin
    t_pcm.delete;
    t_p.delete;
    t_pv.delete;
    t_pk.delete;
    t_cp.delete;
    t_cpc.delete;
    t_cce.delete;
  end;
  /*clean disc rated table*/
  procedure prc_clear_disc as
  begin
    t_rdo.delete;
    t_ruco.delete;
    t_cck.delete;
    t_ccv.delete;
    t_cc.delete;
    t_cah.delete;
    t_cced.delete;
    t_cce.delete;
    t_cp_d.delete;
    t_cpc_d.delete;
    t_cuc.delete;
    t_ccs.delete;
    t_kog.delete;
  end;
  /************* external_id_acct_map *******************/
  procedure prc_ins_eiam(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'external_id_acct_map 
                       (
                         external_id
                        ,account_no
                        ,external_id_type
                        ,server_id
                        ,active_date
                        ,inactive_date
                       )
                VALUES (
                        :v_external_id
                       ,:v_account_no
                       ,:v_external_id_type
                       ,:v_server_id
                       ,:v_active_date
                       ,:v_inactive_date
                       )'
        using t_eiam(indx).external_id, t_eiam(indx).account_no, t_eiam(indx).external_id_type, t_eiam(indx).server_id, t_eiam(indx).active_date, t_eiam(indx).inactive_date;
      indx := indx + 1;
      EXIT WHEN indx = t_eiam.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_eiam;

  /************************* SERVER_LOOKUP****************************************/
  procedure prc_ins_sl(schema_name varchar2) AS
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert into ' || schema_name ||
                        'server_lookup 
                       (
                           ACCOUNT_NO
                          ,ACCOUNT_CATEGORY
                          ,BILL_FNAME
                          ,BILL_FNAME_FIND
                          ,BILL_LNAME
                          ,BILL_LNAME_FIND
                          ,BILL_COMPANY
                          ,BILL_COMPANY_FIND
                          ,BILL_ADDRESS1
                          ,BILL_CITY
                          ,BILL_STATE
                          ,BILL_ZIP
                          ,CUST_PHONE1
                          ,CUST_PHONE2
                          ,CODEWORD
                          ,SERVER_ID
                          ,ACCT_SEG_ID
                          ,ALT_LNAME
                          ,ALT_FNAME
                          ,ALT_COMPANY_NAME
                       )
                VALUES (
                           :V_ACCOUNT_NO
                          ,:V_ACCOUNT_CATEGORY
                          ,:V_BILL_FNAME
                          ,:V_BILL_FNAME_FIND
                          ,:V_BILL_LNAME
                          ,:V_BILL_LNAME_FIND
                          ,:V_BILL_COMPANY
                          ,:V_BILL_COMPANY_FIND
                          ,:V_BILL_ADDRESS1
                          ,:V_BILL_CITY
                          ,:V_BILL_STATE
                          ,:V_BILL_ZIP
                          ,:V_CUST_PHONE1
                          ,:V_CUST_PHONE2
                          ,:V_CODEWORD
                          ,:V_SERVER_ID
                          ,:V_ACCT_SEG_ID
                          ,:V_ALT_LNAME
                          ,:V_ALT_FNAME
                          ,:V_ALT_COMPANY_NAME
                       )'
        using t_sl(indx).ACCOUNT_NO, t_sl(indx).ACCOUNT_CATEGORY, t_sl(indx).BILL_FNAME, t_sl(indx).BILL_FNAME_FIND, t_sl(indx).BILL_LNAME, t_sl(indx).BILL_LNAME_FIND, t_sl(indx).BILL_COMPANY, t_sl(indx).BILL_COMPANY_FIND, t_sl(indx).BILL_ADDRESS1, t_sl(indx).BILL_CITY, t_sl(indx).BILL_STATE, t_sl(indx).BILL_ZIP, t_sl(indx).CUST_PHONE1, t_sl(indx).CUST_PHONE2, t_sl(indx).CODEWORD, t_sl(indx).SERVER_ID, t_sl(indx).ACCT_SEG_ID, t_sl(indx).ALT_LNAME, t_sl(indx).ALT_FNAME, t_sl(indx).ALT_COMPANY_NAME;
      indx := indx + 1;
      EXIT WHEN indx = t_sl.LAST + 1;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      prc_print_exception;
      raise;
  end prc_ins_sl;

  /***************************  CMF  ***********************/
  procedure prc_ins_cmf(schema_name varchar2) as
    indx NUMBER;
  BEGIN
  
    indx := 1;
    LOOP
      EXECUTE IMMEDIATE 'insert /*+ APPEND */ into ' || schema_name ||
                        'cmf 
                       (
                           ACCOUNT_NO            
                          ,PARENT_ID             
                          ,CHILD_COUNT          
                          ,HIERARCHY_ID         
                          ,TIE_CODE              
                          ,BILL_SEQUENCE_NUM     
                          ,CURRENCY_CODE         
                          ,LANGUAGE_CODE         
                          ,ACCOUNT_TYPE          
                          ,ACCOUNT_CATEGORY      
                          ,PREV_CUTOFF_DATE      
                          ,PREV_BILL_DATE          
                          ,NEXT_BILL_DATE          
                          ,PREV_BILL_REFNO         
                          ,PREV_BILL_REF_RESETS    
                          ,PREV_BALANCE_REFNO      
                          ,PREV_BALANCE_REF_RESETS 
                          ,BILL_NAME_PRE           
                          ,BILL_FNAME              
                          ,BILL_MINIT              
                          ,BILL_LNAME              
                          ,BILL_NAME_GENERATION    
                          ,BILL_TITLE              
                          ,BILL_COMPANY            
                          ,CUST_ADDRESS1           
                          ,CUST_ADDRESS2           
                          ,CUST_ADDRESS3           
                          ,CUST_CITY               
                          ,CUST_STATE              
                          ,CUST_ZIP                
                          ,CUST_COUNTRY_CODE       
                          ,CUST_FRANCHISE_TAX_CODE 
                          ,CUST_GEOCODE            
                          ,CUST_COUNTY             
                          ,CUST_PHONE1             
                          ,CUST_PHONE2             
                          ,BILL_ADDRESS1           
                          ,BILL_ADDRESS2           
                          ,BILL_ADDRESS3           
                          ,BILL_CITY               
                          ,BILL_STATE              
                          ,BILL_ZIP                
                          ,BILL_COUNTRY_CODE       
                          ,BILL_FRANCHISE_TAX_CODE 
                          ,BILL_COUNTY             
                          ,BILL_GEOCODE            
                          ,SSN                     
                          ,CONTACT1_NAME           
                          ,CONTACT2_NAME           
                          ,CONTACT1_PHONE          
                          ,CONTACT2_PHONE          
                          ,CUST_FAXNO              
                          ,STATEMENT_TO_FAXNO      
                          ,DEFAULT_CCARD_ID        
                          ,DEFAULT_CCARD_ID_SERV   
                          ,CUST_BANK_SORT_CODE     
                          ,CUST_BANK_ACC_NUM       
                          ,CUST_BANK_ACC_NAME      
                          ,ALT_BANK_ACC_NUM        
                          ,CREDIT_THRESH           
                          ,CRED_STATUS             
                          ,CREDIT_RATING           
                          ,DISCONNECT_REASON       
                          ,DISC_RCV_OPT            
                          ,THRESH_REF              
                          ,PAY_METHOD              
                          ,BILL_PERIOD             
                          ,BILLING_FREQUENCY       
                          ,BILL_FMT_OPT            
                          ,BILL_DISP_METH          
                          ,BILL_HOLD_CODE          
                          ,MSG_GRP_ID              
                          ,INSERT_GRP_ID