pkg_ken_migapi.pck

数据迁移使用的ETL程序包

create or replace package pkg_ken_migapi is

  -- Author  : YINGKUN.GU
  -- Created : 2006-4-11 18:05:57
  -- Purpose : API of inserting data for migration tables (trigger was disabled).

  -- Public type declarations
  TYPE EIAM_TABLE IS TABLE OF external_id_acct_map%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE SL_TABLE IS TABLE OF SERVER_LOOKUP%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE SC_TABLE IS TABLE OF SRC_CUST%ROWTYPE INDEX BY PLS_INTEGER;

  TYPE CMF_TABLE IS TABLE OF CMF%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CJ_TABLE IS TABLE OF CMF_JOIN%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CED_TABLE IS TABLE OF CMF_EXT_DATA%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE BI_TABLE IS TABLE OF BILL_INVOICE%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CSC_TABLE IS TABLE OF CUSTOMER_SERVICE_CENTERS%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CIAM_TABLE IS TABLE OF CUSTOMER_ID_ACCT_MAP%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CB_TABLE IS TABLE OF CMF_BALANCE%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CBD_TABLE IS TABLE OF CMF_BALANCE_DETAIL%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CSH_TABLE IS TABLE OF CMF_STATUS_HISTORY%ROWTYPE INDEX BY PLS_INTEGER;

  ---update by xiaozhong.lu
  ---add product related table
  TYPE PRO_TABLE IS TABLE OF PRODUCT_RATE_OVERRIDE%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE PRK_TABLE IS TABLE OF PRODUCT_RATE_KEY%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE PCM_TABLE IS TABLE OF PRODUCT_CHARGE_MAP%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE P_TABLE IS TABLE OF PRODUCT%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE PV_TABLE IS TABLE OF PRODUCT_VIEW%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE PK_TABLE IS TABLE OF PRODUCT_KEY%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CCE_TABLE IS TABLE OF CMF_COMPONENT_ELEMENT%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CPC_TABLE IS TABLE OF CMF_PACKAGE_COMPONENT%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CP_TABLE IS TABLE OF CMF_PACKAGE%ROWTYPE INDEX BY PLS_INTEGER;

  ---discount
  TYPE RDO_TABLE IS TABLE OF rate_discount_overrides%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE RUCO_TABLE IS TABLE OF RATE_UNIT_CR_OVERRIDES%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CCK_TABLE IS TABLE OF CUSTOMER_CONTRACT_KEY%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CCV_TABLE IS TABLE OF CUSTOMER_CONTRACT_VIEW%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CC_TABLE IS TABLE OF CUSTOMER_CONTRACT%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CAH_TABLE IS TABLE OF CT_CONTRACT_HQ_GROUP_MAP%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CCED_TABLE IS TABLE OF CT_CAH_EXT_DATA%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CP_D_TABLE IS TABLE OF CMF_PACKAGE_D%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CPC_D_TABLE IS TABLE OF CMF_PACKAGE_COMPONENT_D%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CCE_D_TABLE IS TABLE OF CMF_COMPONENT_ELEMENT_D%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CUC_TABLE IS TABLE OF CONTRACT_UNIT_CR%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CCS_TABLE IS TABLE OF CUSTOMER_CORRIDORS%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE KOG_TABLE IS TABLE OF KB_BIL_GD_TBL%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE SBU_TABLE IS TABLE OF SBU_CUSTOMER_CONTRACTS%ROWTYPE INDEX BY PLS_INTEGER;

  --Service
  TYPE SK_TABLE IS TABLE OF SERVICE_KEY%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE SV_TABLE IS TABLE OF SERVICE_VIEW%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE SERVICE_TABLE IS TABLE OF SERVICE%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE SED_TABLE IS TABLE OF SERVICE_EXT_DATA%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE SB_TABLE IS TABLE OF SERVICE_BILLING%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE EJ_TABLE IS TABLE OF EMF_JOIN%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE ABS_TABLE IS TABLE OF ACCOUNT_BILLED_SERVICES%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CIEMK_TABLE IS TABLE OF CUSTOMER_ID_EQUIP_MAP_KEY%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CIEMV_TABLE IS TABLE OF CUSTOMER_ID_EQUIP_MAP_VIEW%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE CIEM_TABLE IS TABLE OF CUSTOMER_ID_EQUIP_MAP%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE SS_TABLE IS TABLE OF SERVICE_STATUS%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE EIEMV_TABLE IS TABLE OF EXTERNAL_ID_EQUIP_MAP_VIEW%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE EIEM_TABLE IS TABLE OF EXTERNAL_ID_EQUIP_MAP%ROWTYPE INDEX BY PLS_INTEGER;
  TYPE EIEMK_TABLE IS TABLE OF EXTERNAL_ID_EQUIP_MAP_KEY%ROWTYPE INDEX BY PLS_INTEGER;

  -- Public constant declarations

  -- Public variable declarations
  t_cb   CB_TABLE;
  t_cbd  CBD_TABLE;
  t_cj   CJ_TABLE;
  t_csc  CSC_TABLE;
  t_cmf  CMF_TABLE;
  t_csh  CSH_TABLE;
  t_sl   SL_TABLE;
  t_eiam EIAM_TABLE;
  t_ciam CIAM_TABLE;
  t_ced  CED_TABLE;
  t_bi   BI_TABLE;

  t_pro PRO_TABLE;
  t_prk PRK_TABLE;
  t_pcm PCM_TABLE;
  t_p   P_TABLE;
  t_pv  PV_TABLE;
  t_pk  PK_TABLE;
  t_cp  CP_TABLE;
  t_cpc CPC_TABLE;
  t_cce CCE_TABLE;

  t_rdo   RDO_TABLE;
  t_ruco  RUCO_TABLE;
  t_cck   CCK_TABLE;
  t_ccv   CCV_TABLE;
  t_cc    CC_TABLE;
  t_cah   CAH_TABLE;
  t_cced  CCED_TABLE;
  t_cce_d CCE_D_TABLE;
  t_cp_d  CP_D_TABLE;
  t_cpc_d CPC_D_TABLE;
  t_cuc   CUC_TABLE;
  t_ccs   CCS_TABLE;
  t_kog   KOG_TABLE;
  t_sbu   SBU_TABLE;

  t_sk      SK_TABLE;
  t_sv      SV_TABLE;
  t_service SERVICE_TABLE;
  t_sed     SED_TABLE;
  t_sb      SB_TABLE;
  t_ej      EJ_TABLE;
  t_abs     ABS_TABLE;
  t_ciemk   CIEMK_TABLE;
  t_ciemv   CIEMV_TABLE;
  t_ciem    CIEM_TABLE;
  t_ss      SS_TABLE;
  t_eiemv   EIEMV_TABLE;
  t_eiem    EIEM_TABLE;
  t_eiemk   EIEMK_TABLE;

  -- Public function and procedure declarations
  -- clear data in all memory tables.
  procedure prc_clear_all;

  procedure prc_clear_ca;
  procedure prc_clear_ba;
  procedure prc_clear_si;

  -- clear data in PROD_RATED memory tables.
  procedure prc_clear_prod;

  procedure prc_clear_disc;

  -- catalog.external_id_acct_map CB_TABLE
  procedure prc_ins_eiam(schema_name varchar2);

  -- catalog.server_lookup
  procedure prc_ins_sl(schema_name varchar2);

  -- CMF
  procedure prc_ins_cmf(schema_name varchar2);

  --CMF_JOIN
  procedure prc_ins_cj(schema_name varchar2);

  --CMF_EXT_DATA
  procedure prc_ins_ced(schema_name varchar2);

  --BILL_INVOICE
  procedure prc_ins_bi(schema_name varchar2);

  --CUSTOMER_SERVICE_CENTERS
  procedure prc_ins_csc(schema_name varchar2);

  --CUSTOMER_ID_ACCT_MAP 
  procedure prc_ins_ciam(schema_name varchar2);

  --CMF_BALANCE
  procedure prc_ins_cb(schema_name varchar2);

  --CMF_BALANCE_DETAIL
  procedure prc_ins_cbd(schema_name varchar2);

  --CMF_STATUS_HISTORY
  procedure prc_ins_csh(schema_name varchar2);

  ----start to add by luxiaozhong
  ----product related
  --product_rate_overriders
  procedure prc_ins_pro(schema_name varchar2);

  --product_rate_key
  procedure prc_ins_prk(schema_name varchar2);

  --product_charge_map
  procedure prc_ins_pcm(schema_name varchar2);

  --product
  procedure prc_ins_p(schema_name varchar2);

  --product_view
  procedure prc_ins_pv(schema_name varchar2);

  --product_key
  procedure prc_ins_pk(schema_name varchar2);

  --cmf_package
  procedure prc_ins_cp(schema_name varchar2);

  --cmf_package_component
  procedure prc_ins_cpc(schema_name varchar2);

  --cmf_component_element
  procedure prc_ins_cce(schema_name varchar2);

  ---package for product/procut_view/product_key
  procedure prc_ins_3p(schema_name varchar2);

  ----discount related---
  procedure prc_ins_rdo(schema_name varchar2);
  procedure prc_ins_ruco(schema_name varchar2);
  procedure prc_ins_cck(schema_name varchar2);
  procedure prc_ins_ccv(schema_name varchar2);
  procedure prc_ins_cc(schema_name varchar2);
  procedure prc_ins_cah(schema_name varchar2);
  procedure prc_ins_cced(schema_name varchar2);
  procedure prc_ins_cce_d(schema_name varchar2);
  procedure prc_ins_cp_d(schema_name varchar2);
  procedure prc_ins_cpc_d(schema_name varchar2);
  procedure prc_ins_cuc(schema_name varchar2);
  procedure prc_ins_ccs(schema_name varchar2);
  procedure prc_ins_kog(schema_name varchar2); ---mit3.cat
  procedure prc_ins_sbu(schema_name varchar2);
  
  procedure prc_ins_2cc(schema_name varchar2);
  ----end to add by luxiaozhong

  /*******  SERVICE_KEY *********************************************************/

  --SERVICE_KEY
  procedure prc_ins_sk(schema_name varchar2);
  procedure prc_ins_sv(schema_name varchar2);
  procedure prc_ins_service(schema_name varchar2);
  procedure prc_ins_sed(schema_name varchar2);
  procedure prc_ins_sb(schema_name varchar2);
  procedure prc_ins_ej(schema_name varchar2);
  procedure prc_ins_abs(schema_name varchar2);
  procedure prc_ins_ciemk(schema_name varchar2);
  procedure prc_ins_ciemv(schema_name varchar2);
  procedure prc_ins_ciem(schema_name varchar2);
  procedure prc_ins_ss(schema_name varchar2);
  procedure prc_ins_eiemv(schema_name varchar2);
  procedure prc_ins_eiem(schema_name varchar2);
  procedure prc_ins_eiemk(schema_name varchar2);
  procedure prc_ins_3S(schema_name varchar2);
  procedure prc_ins_3E(schema_name varchar2);
  procedure prc_ins_3C(schema_name varchar2);

end pkg_ken_migapi;
/
create or replace package body pkg_ken_migapi is

  -- Private type declarations

  -- Private constant declarations

  -- Private variable declarations

  -- Private function and procedure
  procedure prc_print_exception as
    v_ErrorCode   NUMBER; --Code for the error
    v_ErrorMsg    VARCHAR2(1000); --Message text for the error
    v_currentUser VARCHAR2(20); --Current database user
    v_Information VARCHAR2(100); --Information about the error  
  begin
    v_ErrorCode   := SQLCODE;
    v_ErrorMsg    := SUBSTR(SQLERRM, 1, 1000);
    v_CurrentUser := USER;
    v_Information := 'SUMMARY: Error encoutered on ' ||
                     TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') ||
                     ' by database user ' || v_CurrentUser;
    dbms_output.put_line(v_Information);
    dbms_output.put_line('ERRORCODE: ' || v_ErrorCode);
    dbms_output.put_line('ERRORMSG: ' || v_ErrorMsg);
  end prc_print_exception;

  -- Function and procedure implementations
  procedure prc_clear_all 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;
    t_pro.delete;
    t_prk.delete;