pkg_ken_etl.pck

数据迁移使用的ETL程序包

create or replace package PKG_KEN_ETL is

  -- Author  : YINGKUN.GU
  -- Created : 2006-4-28 17:39:46
  -- Purpose : ETL CA/BA/SI/PR/DIS

  -- Public type declarations

  -- Public constant declarations

  -- Public variable declarations

  -- Public function and procedure declarations
  PROCEDURE PRC_T_CA(p_CITY       IN VARCHAR2,
                     p_PROCESSNUM IN NUMBER,
                     p_CURRENTNUM IN NUMBER,
                     p_MODE       IN VARCHAR2);

  PROCEDURE PRC_T_BA(p_CITY       IN VARCHAR2,
                     p_PROCESSNUM IN NUMBER,
                     p_CURRENTNUM IN NUMBER,
                     p_MODE       IN VARCHAR2);

  PROCEDURE PRC_T_SI(p_CITY       IN VARCHAR2,
                     p_PROCESSNUM IN NUMBER,
                     p_CURRENTNUM IN NUMBER,
                     p_MODE       IN VARCHAR2);

  PROCEDURE PRC_T_PR(p_CITY           IN VARCHAR2,
                     p_TotalProcesses IN NUMBER,
                     p_NumProcesses   IN NUMBER,
                     p_MODE           IN VARCHAR2);

  PROCEDURE PRC_T_DC(p_CITY           IN VARCHAR2,
                     p_TotalProcesses IN NUMBER,
                     p_NumProcesses   IN NUMBER,
                     p_mode           in varchar2);
  PROCEDURE PRC_T_NRC(p_CITY       IN VARCHAR2,
                      p_PROCESSNUM IN NUMBER,
                      p_CURRENTNUM IN NUMBER,
                      p_MODE       IN VARCHAR2);

end PKG_KEN_ETL;
/
create or replace package body PKG_KEN_ETL is

  -- Private type declarations

  -- Private constant declarations

  -- Private variable declarations

  -- Function and procedure implementations
  PROCEDURE PRC_T_CA(p_CITY       IN VARCHAR2,
                     p_PROCESSNUM IN NUMBER,
                     p_CURRENTNUM IN NUMBER,
                     p_MODE       IN VARCHAR2) AS
    /*constant variable*/
    c_CommitCounter CONSTANT NUMBER := 5000;
    c_Domain        constant varchar2(3) := 'CA';
  
    /*variable related to workflow*/
    v_ProcessName   varchar2(30) := 'PRC_T_' || c_Domain || '_' || p_CITY || '_' ||
                                    p_PROCESSNUM || '_' || p_CURRENTNUM;
    v_TotalRecord   LOG_PROCESS_STATUS.Total_Rec%Type;
    v_SucceedRecord LOG_PROCESS_STATUS.succeed_rec%TYPE;
    v_FailRecord    LOG_PROCESS_STATUS.fail_rec%TYPE;
    v_RecordStart   NUMBER := pkg_ken_util.fun_get_start_id(v_ProcessName);
    v_RecordEnd     NUMBER := pkg_ken_util.fun_get_end_id(v_ProcessName);
    v_VipFlag       varchar2(2);
    v_StarFlag      varchar2(2);
    v_MigCatSchema  varchar2(12) := PKG_KEN_UTIL.g_MIGCAT_Schema;
    v_RowId         src_cust.row_id%type;
    v_StarLevel     SRC_CUST.STAR_LEVEL%TYPE;
    /*  v_status    varchar2(10);*/
  
    /*variable related to kenan tables*/
    v_ExternalID      EXTERNAL_ID_ACCT_MAP.EXTERNAL_ID%TYPE;
    v_AccountNo       CMF.ACCOUNT_NO%TYPE; --kenan internal cust_id   
    v_AccountCategory CMF.account_category%TYPE; --account category
    v_CustStatus      CMF.Account_Status%TYPE := -1; --cust status
    v_CustIdentType   CUSTOMER_ID_ACCT_MAP.External_Id_Type%TYPE; --cust indent type 
    v_CustMkt         CMF.mkt_code%TYPE; --cust market code 
    v_CustDateActive  CMF.DATE_ACTIVE%TYPE := to_date('10/01/1949',
                                                      'MM/DD/YYYY'); --cust date active
    v_OwningCostCtr   CMF.OWNING_COST_CTR%TYPE;
    v_HierarchyID     CMF.HIERARCHY_ID%TYPE;
    v_AcctSegId       CMF.ACCT_SEG_ID%TYPE;
    v_FranTaxCode     CMF.CUST_FRANCHISE_TAX_CODE%TYPE;
  
    /*variable related to MSA*/
    v_ServerID EXTERNAL_ID_ACCT_MAP.server_id%TYPE; --server_id of the kenan customer db
    v_DBUSER   CFG_KEN_MSA.MIG_SCHEMA%TYPE;
  
    i_ced number;
    i_csc number;
  
    CURSOR c_CUST IS
      SELECT *
        FROM SRC_CUST sc
       WHERE sc.is_processed = p_MODE
         AND (sc.src_id = p_CITY || '97' or sc.src_id = p_CITY || 'Bl')
         AND (sc.ROW_ID >= v_RecordStart AND sc.ROW_ID <= v_RecordEnd);
  
    rec_cust c_cust%rowtype;
  BEGIN
    v_OwningCostCtr := pkg_ken_util.FUN_NAME2CODE(P_CITY);
    pkg_ken_util.PRC_STREAM_INFO(v_ProcessName,
                                 v_TotalRecord,
                                 v_SucceedRecord,
                                 v_FailRecord);
  
    OPEN c_CUST;
    <<NEXTLOOP01555>>
    LOOP
      BEGIN
        FETCH c_CUST
          INTO rec_cust;
        EXIT WHEN c_CUST%NOTFOUND;
      EXCEPTION
        WHEN OTHERS THEN
          IF SQLCODE = -1555 THEN
            ROLLBACK;
            CLOSE c_CUST;
            pkg_ken_util.PRC_STREAM_INFO(v_ProcessName,
                                         v_TotalRecord,
                                         v_SucceedRecord,
                                         v_FailRecord);
            OPEN c_CUST;
            GOTO NEXTLOOP01555;
          ELSE
            RAISE;
          END IF;
      END;
      begin
        v_RowId := rec_cust.row_id;
        pkg_ken_migapi.prc_clear_ca;
        i_ced      := 1;
        i_csc      := 1;
        v_VipFlag  := 'N';
        v_StarFlag := 'N';
      
        v_ExternalID      := pkg_ken_util.FUN_ENCODE(rec_cust.cust_id,
                                                     p_city,
                                                     'CA');
        v_AccountCategory := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_cust.SRC_ID,
                                                                    'CUST_TYPE',
                                                                    rec_cust.CUST_TYPE));
        v_CustIdentType   := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_cust.SRC_ID,
                                                                    'IDEN_TYPE',
                                                                    rec_cust.IDEN_TYPE));
        v_CustMkt         := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_cust.SRC_ID,
                                                                    'MKT_CODE',
                                                                    rec_cust.MKT_CODE));
        v_StarLevel       := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_cust.SRC_ID,
                                                                    'STAR_LEVEL',
                                                                    rec_cust.STAR_LEVEL));
        v_FranTaxCode     := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_cust.SRC_ID,
                                                                    'CUST_INDUSTRY',
                                                                    rec_cust.cust_industry));
      
        IF v_AccountCategory = 1 THEN
          -- vip
          v_AcctSegId := 10;
          pkg_ken_util.PRC_CA_MSA('VIP', v_ServerID, v_DBUSER);
        
        ELSE
          -- normal
          v_AcctSegId := v_OwningCostCtr;
          pkg_ken_util.PRC_CA_MSA(p_CITY, v_ServerID, v_DBUSER);
        END IF;
        execute immediate 'select ' || v_MigCatSchema ||
                          'ACCOUNT_ID_SEQ_NUM_SEQ.nextval from dual'
          into v_AccountNo;
      
        v_HierarchyID := v_AccountNo;
      
        /****************  CMF_BALANCE   *************************************************************/
        pkg_ken_migapi.t_cb(1).account_no := v_AccountNo;
        pkg_ken_migapi.t_cb(1).currency_code := 14;
        pkg_ken_migapi.t_cb(1).bill_ref_no := 0;
        pkg_ken_migapi.t_cb(1).bill_ref_resets := 0;
        pkg_ken_migapi.t_cb(1).new_charges := 0;
        pkg_ken_migapi.t_cb(1).net_new_charges := 0;
        pkg_ken_migapi.t_cb(1).total_due := 0;
        pkg_ken_migapi.t_cb(1).total_adj := 0;
        pkg_ken_migapi.t_cb(1).total_paid := 0;
        pkg_ken_migapi.t_cb(1).balance_due := 0;
        pkg_ken_migapi.t_cb(1).dispute_amt := 0;
        pkg_ken_migapi.t_cb(1).late_exempt_charges := 0;
        pkg_ken_migapi.t_cb(1).collection_indicator := 0;
        pkg_ken_migapi.t_cb(1).chg_date := v_CustDateActive;
        pkg_ken_migapi.t_cb(1).chg_who := pkg_ken_util.g_Chg_Who;
        pkg_ken_migapi.t_cb(1).converted := 0;
        pkg_ken_migapi.t_cb(1).new_charge_credits := 0;
        pkg_ken_migapi.t_cb(1).gl_amount := 0;
      
        /****************  CMF_BALANCE_DETAIL   *************************************************************/
      
        pkg_ken_migapi.t_cbd(1).account_no := v_AccountNo;
        pkg_ken_migapi.t_cbd(1).currency_code := 14;
        pkg_ken_migapi.t_cbd(1).bill_ref_no := 0;
        pkg_ken_migapi.t_cbd(1).bill_ref_resets := 0;
        pkg_ken_migapi.t_cbd(1).new_charges := 0;
        pkg_ken_migapi.t_cbd(1).net_new_charges := 0;
        pkg_ken_migapi.t_cbd(1).total_due := 0;
        pkg_ken_migapi.t_cbd(1).total_adj := 0;
        pkg_ken_migapi.t_cbd(1).total_paid := 0;
        pkg_ken_migapi.t_cbd(1).balance_due := 0;
        pkg_ken_migapi.t_cbd(1).dispute_amt := 0;
        pkg_ken_migapi.t_cbd(1).late_exempt_charges := 0;
        pkg_ken_migapi.t_cbd(1).collection_indicator := 0;
        pkg_ken_migapi.t_cbd(1).chg_date := v_CustDateActive;
        pkg_ken_migapi.t_cbd(1).chg_who := pkg_ken_util.g_Chg_Who;
        pkg_ken_migapi.t_cbd(1).converted := 0;
        pkg_ken_migapi.t_cbd(1).new_charge_credits := 0;
        pkg_ken_migapi.t_cbd(1).open_item_id := 0;
        pkg_ken_migapi.t_cbd(1).gl_amount := 0;
      
        /****************  CMF_JOIN   *************************************************************/
        pkg_ken_migapi.t_cj(1).account_no := v_AccountNo;
      
        /**************  CUSTOMER_SERVICE_CENTERS  ************************************************/
      
        While i_csc <= 5 Loop
          If i_csc <> 5 then
            pkg_ken_migapi.t_csc(i_csc).account_no := v_AccountNo;
            pkg_ken_migapi.t_csc(i_csc).service_center_id := i_csc;
            pkg_ken_migapi.t_csc(i_csc).service_center_type := i_csc;
            pkg_ken_migapi.t_csc(i_csc).chg_who := pkg_ken_util.g_Chg_Who;
            pkg_ken_migapi.t_csc(i_csc).chg_date := v_CustDateActive;
          Elsif i_csc = 5 then
            pkg_ken_migapi.t_csc(i_csc).account_no := v_AccountNo;
            pkg_ken_migapi.t_csc(i_csc).service_center_id := 5;
            pkg_ken_migapi.t_csc(i_csc).service_center_type := 6;
            pkg_ken_migapi.t_csc(i_csc).chg_who := pkg_ken_util.g_Chg_Who;
            pkg_ken_migapi.t_csc(i_csc).chg_date := v_CustDateActive;
          End if;
          i_csc := i_csc + 1;
        End Loop;
      
        /************************** CMF **************************************/
        pkg_ken_migapi.t_cmf(1).account_no := v_AccountNo;
        pkg_ken_migapi.t_cmf(1).child_count := TO_NUMBER(rec_cust.CHILD_COUNT);
        pkg_ken_migapi.t_cmf(1).bill_sequence_num := 0;
        pkg_ken_migapi.t_cmf(1).currency_code := 14;
        pkg_ken_migapi.t_cmf(1).language_code := 2;
        pkg_ken_migapi.t_cmf(1).account_type := 0;
        pkg_ken_migapi.t_cmf(1).account_category := v_AccountCategory;
        pkg_ken_migapi.t_cmf(1).prev_bill_refno := 0;
        pkg_ken_migapi.t_cmf(1).prev_bill_ref_resets := 0;
        pkg_ken_migapi.t_cmf(1).prev_balance_refno := 0;
        pkg_ken_migapi.t_cmf(1).prev_balance_ref_resets := 0;
        pkg_ken_migapi.t_cmf(1).Default_Ccard_Id := 0;
        pkg_ken_migapi.t_cmf(1).DEFAULT_CCARD_ID_SERV := 0;
        pkg_ken_migapi.t_cmf(1).CREDIT_RATING := 0;
        pkg_ken_migapi.t_cmf(1).account_status := v_CustStatus;
        pkg_ken_migapi.t_cmf(1).account_status_dt := rec_cust.CUST_STATUS_DATE;
        pkg_ken_migapi.t_cmf(1).NO_BILL := 1;
        pkg_ken_migapi.t_cmf(1).COLLECTION_INDICATOR := 0;
        pkg_ken_migapi.t_cmf(1).COLLECTION_STATUS := 0;
        pkg_ken_migapi.t_cmf(1).VIP_CODE := nvl(rec_cust.collection_flag, 1);
        pkg_ken_migapi.t_cmf(1).chg_who := pkg_ken_util.g_Chg_Who;
        pkg_ken_migapi.t_cmf(1).chg_date := v_CustDateActive;
        pkg_ken_migapi.t_cmf(1).date_created := rec_cust.CUST_CREATED_DATE;
        pkg_ken_migapi.t_cmf(1).rev_rcv_cost_ctr := 99;
        pkg_ken_migapi.t_cmf(1).owning_cost_ctr := v_OwningCostCtr;
        pkg_ken_migapi.t_cmf(1).acct_seg_id := v_AcctSegId;
        pkg_ken_migapi.t_cmf(1).converted := 0;
        pkg_ken_migapi.t_cmf(1).charge_threshold := 0;
        pkg_ken_migapi.t_cmf(1).threshold := 0;
        pkg_ken_migapi.t_cmf(1).cyclical_threshold := 0;
        pkg_ken_migapi.t_cmf(1).regulatory_id := 1;
        pkg_ken_migapi.t_cmf(1).global_contract_status := 0;
        pkg_ken_migapi.t_cmf(1).HIERARCHY_ID := v_HierarchyID;
        pkg_ken_migapi.t_cmf(1).bill_lname := trim(rec_cust.CUST_NAME);