📄 pkg_ken_etl.pck
字号:
/**************** 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_AcctDateActive;
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_AcctDateActive;
End if;
i_csc := i_csc + 1;
End Loop;
/******************** CMF_EXT_DATA ********************************************************************/
pkg_ken_migapi.t_ced(1).ACCOUNT_NO := v_AccountNo;
pkg_ken_migapi.t_ced(1).PARAM_ID := 1;
pkg_ken_migapi.t_ced(1).PARAM_VALUE := 0;
pkg_ken_migapi.t_ced(1).PARAM_DATATYPE := 2;
pkg_ken_migapi.t_ced(2).ACCOUNT_NO := v_AccountNo;
pkg_ken_migapi.t_ced(2).PARAM_ID := 100;
pkg_ken_migapi.t_ced(2).PARAM_VALUE := to_char(rec_acct.ACCT_STATUS_DATE,
'YYYY-MM-DD HH24:MI:SS');
pkg_ken_migapi.t_ced(2).PARAM_DATATYPE := 2;
pkg_ken_migapi.t_ced(3).ACCOUNT_NO := v_AccountNo;
pkg_ken_migapi.t_ced(3).PARAM_ID := 103;
pkg_ken_migapi.t_ced(3).PARAM_VALUE := v_IsSendCash;
pkg_ken_migapi.t_ced(3).PARAM_DATATYPE := 2;
pkg_ken_migapi.t_ced(3).ACCOUNT_NO := v_AccountNo;
pkg_ken_migapi.t_ced(3).PARAM_ID := 106;
pkg_ken_migapi.t_ced(3).PARAM_VALUE := v_Prepay;
pkg_ken_migapi.t_ced(3).PARAM_DATATYPE := 2;
/********** Public cmf ****************************************************/
pkg_ken_migapi.t_cmf(1).account_no := v_AccountNo;
pkg_ken_migapi.t_cmf(1).child_count := 0;
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 := 1;
pkg_ken_migapi.t_cmf(1).account_category := v_ExtData1;
pkg_ken_migapi.t_cmf(1).mkt_code := v_ExtData2;
pkg_ken_migapi.t_cmf(1).prev_bill_refno := v_BillRefNo;
pkg_ken_migapi.t_cmf(1).prev_bill_ref_resets := 0;
pkg_ken_migapi.t_cmf(1).prev_balance_refno := v_BillRefNo;
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 := 50;
pkg_ken_migapi.t_cmf(1).account_status := v_AccountStatus;
pkg_ken_migapi.t_cmf(1).account_status_dt := rec_acct.ACCT_STATUS_DATE;
pkg_ken_migapi.t_cmf(1).NO_BILL := 0;
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_acct.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_AcctDateActive;
pkg_ken_migapi.t_cmf(1).date_created := rec_acct.ACCT_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 := 1;
pkg_ken_migapi.t_cmf(1).parent_id := v_ParertID;
pkg_ken_migapi.t_cmf(1).HIERARCHY_ID := v_ParertID;
pkg_ken_migapi.t_cmf(1).bill_lname := trim(rec_acct.ACCT_NAME);
pkg_ken_migapi.t_cmf(1).cust_phone1 := rec_acct.ACCT_PHONE;
pkg_ken_migapi.t_cmf(1).bill_city := trim(rec_acct.BILL_ADDR_CITY);
pkg_ken_migapi.t_cmf(1).bill_address1 := trim(rec_acct.BILL_ADDR);
pkg_ken_migapi.t_cmf(1).bill_zip := rec_acct.BILL_ADDR_ZIPCODE;
pkg_ken_migapi.t_cmf(1).cust_email := rec_acct.ACCT_EMAIL;
pkg_ken_migapi.t_cmf(1).bill_county := trim(rec_acct.BILL_ADDR_COUNTY);
pkg_ken_migapi.t_cmf(1).bill_country_code := 156;
pkg_ken_migapi.t_cmf(1).bill_period := 'M01';
pkg_ken_migapi.t_cmf(1).billing_frequency := 3;
pkg_ken_migapi.t_cmf(1).msg_grp_id := 1;
pkg_ken_migapi.t_cmf(1).date_active := v_AcctDateActive;
pkg_ken_migapi.t_cmf(1).prev_cutoff_date := v_PrevCutDate;
pkg_ken_migapi.t_cmf(1).prev_bill_date := v_PrevBillDate;
pkg_ken_migapi.t_cmf(1).next_bill_date := v_NextBillDate;
pkg_ken_migapi.t_cmf(1).cust_country_code := 156;
pkg_ken_migapi.t_cmf(1).CUST_FRANCHISE_TAX_CODE := 1;
pkg_ken_migapi.t_cmf(1).CUST_GEOCODE := 156001000000000;
pkg_ken_migapi.t_cmf(1).BILL_FRANCHISE_TAX_CODE := 1;
pkg_ken_migapi.t_cmf(1).BILL_GEOCODE := 156001000000000;
pkg_ken_migapi.t_cmf(1).BILL_STATE := pkg_ken_util.g_Province;
pkg_ken_migapi.t_cmf(1).bill_fmt_opt := 1;
pkg_ken_migapi.t_cmf(1).bill_disp_meth := 0;
pkg_ken_migapi.t_cmf(1).insert_grp_id := 1;
pkg_ken_migapi.t_cmf(1).rate_class_default := v_AccountArea;
pkg_ken_migapi.t_cmf(1).RATE_CLASS_SPECIAL := 1;
pkg_ken_migapi.t_cmf(1).EXRATE_CLASS := 1;
pkg_ken_migapi.t_cmf(1).remark := rec_acct.REMARK;
pkg_ken_migapi.t_cmf(1).pay_method := v_PayMethod;
pkg_ken_migapi.t_cmf(1).clearing_house_id := v_BankID;
pkg_ken_migapi.t_cmf(1).bank_agency_code := v_BankAgencyId;
pkg_ken_migapi.t_cmf(1).bank_agency_name := trim(v_BankAgencyName);
pkg_ken_migapi.t_cmf(1).cust_bank_acc_name := trim(v_CustBankAccName);
pkg_ken_migapi.t_cmf(1).cust_bank_acc_num := v_CustBankAccNum;
pkg_ken_migapi.t_cmf(1).cust_bank_sort_code := v_BankID ||
v_BankAgencyId;
/************* CMF_STATUS_HISTORY ***********************************************************************/
pkg_ken_migapi.t_csh(1).account_no := v_AccountNo;
pkg_ken_migapi.t_csh(1).cmf_status_type := 2;
pkg_ken_migapi.t_csh(1).cmf_status := 20;
pkg_ken_migapi.t_csh(1).cmf_status_chg_reason := 1;
pkg_ken_migapi.t_csh(1).active_dt := v_AcctDateActive;
pkg_ken_migapi.t_csh(1).chg_who := pkg_ken_util.g_Chg_Who;
pkg_ken_migapi.t_csh(1).chg_dt := v_AcctDateActive;
/************ CUSTOMER_ID_ACCT_MAP ********************************************************************/
pkg_ken_migapi.t_ciam(1).external_id := v_ExternalID;
pkg_ken_migapi.t_ciam(1).account_no := v_AccountNo;
pkg_ken_migapi.t_ciam(1).external_id_type := 1;
pkg_ken_migapi.t_ciam(1).is_current := 1;
pkg_ken_migapi.t_ciam(1).active_date := v_AcctDateActive;
pkg_ken_migapi.t_ciam(2).external_id := v_ExternalID;
pkg_ken_migapi.t_ciam(2).account_no := v_AccountNo;
pkg_ken_migapi.t_ciam(2).external_id_type := 2;
pkg_ken_migapi.t_ciam(2).is_current := 1;
pkg_ken_migapi.t_ciam(2).active_date := v_AcctDateActive;
/*****************catalog SERVER_LOOKUP ************************************************************************/
pkg_ken_migapi.t_sl(1).account_no := v_AccountNo;
pkg_ken_migapi.t_sl(1).account_category := v_ExtData1;
pkg_ken_migapi.t_sl(1).server_id := v_ServerID;
pkg_ken_migapi.t_sl(1).acct_seg_id := v_AcctSegId;
pkg_ken_migapi.t_sl(1).bill_lname := trim(rec_acct.ACCT_NAME);
pkg_ken_migapi.t_sl(1).bill_lname_find := trim(UPPER(rec_acct.ACCT_NAME));
pkg_ken_migapi.t_sl(1).bill_address1 := trim(rec_acct.BILL_ADDR);
pkg_ken_migapi.t_sl(1).bill_zip := rec_acct.BILL_ADDR_ZIPCODE;
pkg_ken_migapi.t_sl(1).bill_city := trim(rec_acct.BILL_ADDR_CITY);
pkg_ken_migapi.t_sl(1).bill_state := pkg_ken_util.g_Province;
pkg_ken_migapi.t_sl(1).cust_phone1 := rec_acct.ACCT_PHONE;
/************catalog EXTERNAL_ID_ACCT_MAP *******************************************************************/
pkg_ken_migapi.t_eiam(1).external_id := v_ExternalID;
pkg_ken_migapi.t_eiam(1).account_no := v_AccountNo;
pkg_ken_migapi.t_eiam(1).external_id_type := 1;
pkg_ken_migapi.t_eiam(1).server_id := v_ServerID;
pkg_ken_migapi.t_eiam(1).active_date := v_AcctDateActive;
pkg_ken_migapi.t_eiam(2).external_id := v_ExternalID;
pkg_ken_migapi.t_eiam(2).account_no := v_AccountNo;
pkg_ken_migapi.t_eiam(2).external_id_type := 2;
pkg_ken_migapi.t_eiam(2).server_id := v_ServerID;
pkg_ken_migapi.t_eiam(2).active_date := v_AcctDateActive;
exception
when others then
if pkg_ken_util.FUN_LOGIC_EXCEPTION = 1 then
pkg_ken_util.PRC_LOG_STATUS('SRC_ACCT',
'acct_nbr_97',
rec_acct.ACCT_NBR_97,
null,
null,
null,
'F',
rec_acct.src_id,
null,
null,
null,
null,
null);
if p_Mode = 'N' then
v_FailRecord := v_FailRecord + 1;
end if;
if v_FailRecord < pkg_ken_util.g_ErrUpperLimit then
GOTO COMMITPOINT;
else
raise_application_error(-20303,
'The number of records in ERROR CENTER reach upper limit!');
end if;
else
raise;
end if;
end;
--Bill_invoice
if v_BillFlag = 'Y' then
pkg_ken_migapi.prc_ins_bi(v_DBUSER);
end if;
--CMF_BALANCE
pkg_ken_migapi.prc_ins_cb(v_DBUSER);
--CMF_BALANCE_DETAIL
pkg_ken_migapi.prc_ins_cbd(v_DBUSER);
--CMF_JOIN
pkg_ken_migapi.prc_ins_cj(v_DBUSER);
--CUSTOMER_SERVICE_CENTERS
pkg_ken_migapi.prc_ins_csc(v_DBUSER);
--CMF_EXT_DATA
pkg_ken_migapi.prc_ins_ced(v_DBUSER);
-- CMF
pkg_ken_migapi.prc_ins_cmf(v_DBUSER);
--CMF_STATUS_HISTORY
pkg_ken_migapi.prc_ins_csh(v_DBUSER);
--CUSTOMER_ID_ACCT_MAP
pkg_ken_migapi.prc_ins_ciam(v_DBUSER);
-- catalog.server_lookup
pkg_ken_migapi.prc_ins_sl(v_MigCatSchema);
-- catalog.external_id_acct_map
pkg_ken_migapi.prc_ins_eiam(v_MigCatSchema);
pkg_ken_util.PRC_LOG_STATUS('SRC_ACCT',
'acct_nbr_97',
rec_acct.ACCT_NBR_97,
v_AccountNo,
v_ServerID,
v_DBUser,
'S',
rec_acct.src_id,
'M01',
to_char(v_NextBillDate, 'YYYY-MM-DD'),
to_char(v_PrevCutDate, 'YYYY-MM-DD'),
to_char(rec_acct.acct_created_date,
'YYYY-MM-DD'),
v_AccountArea);
if p_MODE = 'N' then
v_SucceedRecord := v_SucceedRecord + 1;
elsif p_MODE = 'F' then
v_SucceedRecord := v_SucceedRecord + 1;
v_FailRecord := v_FailRecord - 1;
end if;
--define the commit point
<<COMMITPOINT>>
IF (p_MODE = 'N' and
((v_SucceedRecord + v_FailRecord) mod c_CommitCounter = 0) or
(v_SucceedRecord + v_FailRecord = v_TotalRecord)) or p_MODE = 'F' THEN
pkg_ken_util.prc_upd_lps(v_ProcessName,
v_SucceedRecord,
v_FailRecord,
'RN');
/* Execute IMMEDIATE 'select t_status from log_process_status where process_name = :v_ProcessName'
into v_status
using v_ProcessName;
if v_status = 'FF' then
raise_application_error(-30000,
'The number of records in ERROR CENTER reach upper limit!');
end if;*/
commit;
END IF;
END LOOP;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -