📄 pkg_ken_etl.pck
字号:
AND (sa.ROW_ID >= v_RecordStart AND sa.ROW_ID <= v_RecordEnd);
rec_acct c_ACCT%rowtype;
BEGIN
v_OwningCostCtr := pkg_ken_util.FUN_NAME2CODE(P_CITY);
v_NextBillDate := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date, 2) + 4;
pkg_ken_util.PRC_STREAM_INFO(v_ProcessName,
v_TotalRecord,
v_SucceedRecord,
v_FailRecord);
OPEN c_ACCT;
<<NEXTLOOP01555>>
LOOP
BEGIN
FETCH c_ACCT
INTO rec_acct;
EXIT WHEN c_ACCT%NOTFOUND;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1555 THEN
ROLLBACK;
CLOSE c_ACCT;
pkg_ken_util.PRC_STREAM_INFO(v_ProcessName,
v_TotalRecord,
v_SucceedRecord,
v_FailRecord);
OPEN c_ACCT;
GOTO NEXTLOOP01555;
ELSE
RAISE;
END IF;
END;
Begin
v_RowID := rec_acct.row_id;
pkg_ken_migapi.prc_clear_ba;
i_csc := 1;
v_BillFlag := 'N';
-- get parent_id, server_id, server_user
pkg_ken_util.PRC_PARENT_INFO('SRC_CUST_STATUS',
rec_acct.SRC_ID,
rec_acct.CUST_ID,
v_ParertID,
v_ServerID,
v_DBUSER);
v_ExternalID := pkg_ken_util.FUN_ENCODE(rec_acct.acct_nbr_97,
p_city,
'BA');
v_AccountStatus := -1;
-- get account_status, account_area, pay_method
/* v_AccountStatus := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.SRC_ID,
'ACCT_STATUS',
rec_acct.ACCT_STATUS));*/
v_AccountArea := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.SRC_ID,
'ACCT_AREA',
rec_acct.ACCT_AREA));
v_PayMethod := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.SRC_ID,
'PAYMT_METH',
rec_acct.PAYMT_METH));
v_AcctDateActive := TRUNC(rec_acct.ACCT_CREATED_DATE);
IF rec_acct.PAYMT_METH IN ('D', '1G', '1P') THEN
v_Prepay := 99;
ELSE
v_Prepay := 0;
END IF;
select ext_data_1, ext_data_2
into v_ExtData1, v_ExtData2
from src_cust_status
where old_id = rec_acct.CUST_ID
and src_id = rec_acct.SRC_ID;
IF v_ExtData1 = 1 THEN
v_AcctSegId := 10;
ELSE
v_AcctSegId := v_OwningCostCtr;
END IF;
v_BankID := null;
v_BankAgencyId := null;
v_BankAgencyName := null;
v_CustBankAccName := null;
v_CustBankAccNum := null;
if v_PayMethod = 1 then
v_IsSendCash := 'N';
elsif v_PayMethod = 103 then
v_IsSendCash := 'Y';
v_PayMethod := 1;
elsif v_PayMethod = 3 then
v_BankID := pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.src_id,
'BANK_ID',
rec_acct.bank_id);
v_BankAgencyId := pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.src_id,
'BANK_BRANCH_ID',
rec_acct.bank_branch_id);
v_BankAgencyName := rec_acct.bank_branch_name;
v_CustBankAccName := rec_acct.bk_accnt_name;
v_CustBankAccNum := SUBSTRB(rec_acct.bk_accnt_num, 1, 30);
v_IsSendCash := 'N';
end if;
/*GET ACCOUNT_NO FROM SEQ*/
execute immediate 'select ' || v_MigCatSchema ||
'ACCOUNT_ID_SEQ_NUM_SEQ.nextval from dual'
into v_AccountNo;
v_BillRefNo := 0;
v_PrevCutDate := '';
v_PrevBillDate := '';
/********************** 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 := v_BillRefNo;
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_AcctDateActive;
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 := v_BillRefNo;
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_AcctDateActive;
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;
-- generate invoice info. for active BA
IF v_AcctDateActive < pkg_ken_util.g_Cutoff_Date THEN
v_AccountStatus := 0;
v_PrevCutDate := pkg_ken_util.g_Cutoff_Date;
v_PrevBillDate := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date + 4, 1);
/*GET BILL_INVOICE FROM SEQ*/
execute immediate 'select ' || v_MigAdmSchema ||
'BILL_INVOICE_SEQ.nextval from dual'
into v_BillRefNo;
/********************** BILL_INVOICE *************************************************************/
pkg_ken_migapi.t_bi(1).ACCOUNT_NO := v_AccountNo;
pkg_ken_migapi.t_bi(1).BILL_REF_NO := v_BillRefNo;
pkg_ken_migapi.t_bi(1).BILL_REF_RESETS := 0;
pkg_ken_migapi.t_bi(1).BILL_SEQUENCE_NUM := 0;
pkg_ken_migapi.t_bi(1).PROCESS_NUM := 'M';
pkg_ken_migapi.t_bi(1).PREV_BILL_REFNO := 0;
pkg_ken_migapi.t_bi(1).PREV_BILL_REF_RESETS := 0;
pkg_ken_migapi.t_bi(1).PREV_BALANCE_REFNO := 0;
pkg_ken_migapi.t_bi(1).PREV_BALANCE_REF_RESETS := 0;
pkg_ken_migapi.t_bi(1).ZIP := rec_acct.BILL_ADDR_ZIPCODE;
pkg_ken_migapi.t_bi(1).FROM_DATE := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date,
-1);
pkg_ken_migapi.t_bi(1).TO_DATE := TRUNC(pkg_ken_util.g_Cutoff_Date,
'MM');
pkg_ken_migapi.t_bi(1).NEXT_TO_DATE := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date,
1);
pkg_ken_migapi.t_bi(1).PREP_DATE := pkg_ken_util.g_Cutoff_Date;
pkg_ken_migapi.t_bi(1).STATEMENT_DATE := pkg_ken_util.g_Cutoff_Date;
pkg_ken_migapi.t_bi(1).PAYMENT_DUE_DATE := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date + 4,
1);
pkg_ken_migapi.t_bi(1).PREV_PPDD := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date + 4,
0);
pkg_ken_migapi.t_bi(1).PREV_CUTOFF_DATE := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date,
-1);
pkg_ken_migapi.t_bi(1).BILL_PERIOD := 'M01';
pkg_ken_migapi.t_bi(1).CURRENCY_CODE := 14;
pkg_ken_migapi.t_bi(1).PAY_METHOD := v_PayMethod;
pkg_ken_migapi.t_bi(1).BILL_DISP_METH := 0;
pkg_ken_migapi.t_bi(1).ACCOUNT_STATUS := v_AccountStatus;
pkg_ken_migapi.t_bi(1).IMAGE_REQ := 0;
pkg_ken_migapi.t_bi(1).IMAGE_DONE := 0;
pkg_ken_migapi.t_bi(1).SPECIAL_CODE := 0;
pkg_ken_migapi.t_bi(1).PREP_TASK := 'BIPMIG';
pkg_ken_migapi.t_bi(1).PREP_STATUS := 1;
pkg_ken_migapi.t_bi(1).FORMAT_STATUS := 2;
pkg_ken_migapi.t_bi(1).PAGE_COUNT := 1;
pkg_ken_migapi.t_bi(1).BACKOUT_STATUS := 0;
pkg_ken_migapi.t_bi(1).JNL_STATUS := -1;
pkg_ken_migapi.t_bi(1).TEST_FLAG := 0;
pkg_ken_migapi.t_bi(1).ARCH_FLAG := 0;
pkg_ken_migapi.t_bi(1).INTERIM_BILL_FLAG := 0;
pkg_ken_migapi.t_bi(1).INCLUDE_NRC := 0;
pkg_ken_migapi.t_bi(1).INCLUDE_RC := 0;
pkg_ken_migapi.t_bi(1).INCLUDE_ADJ := 0;
pkg_ken_migapi.t_bi(1).INCLUDE_USAGE := 0;
pkg_ken_migapi.t_bi(1).INCLUDE_BMF := 0;
pkg_ken_migapi.t_bi(1).LANGUAGE_CODE := 2;
pkg_ken_migapi.t_bi(1).CONVERTED := 0;
pkg_ken_migapi.t_bi(1).WARM_BILL_FLAG := 0;
pkg_ken_migapi.t_bi(1).JNL_EARNED_THRU_DT := pkg_ken_util.g_Cutoff_Date;
v_BillFlag := 'Y';
/************************** CMF_BALANCE *****************************************************************/
pkg_ken_migapi.t_cb(2).account_no := v_AccountNo;
pkg_ken_migapi.t_cb(2).currency_code := 14;
pkg_ken_migapi.t_cb(2).bill_ref_no := v_BillRefNo;
pkg_ken_migapi.t_cb(2).bill_ref_resets := 0;
pkg_ken_migapi.t_cb(2).new_charges := 500000000;
pkg_ken_migapi.t_cb(2).net_new_charges := 500000000;
pkg_ken_migapi.t_cb(2).total_due := 500000000;
pkg_ken_migapi.t_cb(2).total_adj := 0;
pkg_ken_migapi.t_cb(2).total_paid := 0;
pkg_ken_migapi.t_cb(2).balance_due := 500000000;
pkg_ken_migapi.t_cb(2).dispute_amt := 0;
pkg_ken_migapi.t_cb(2).late_exempt_charges := 0;
pkg_ken_migapi.t_cb(2).collection_indicator := 0;
pkg_ken_migapi.t_cb(2).chg_date := v_AcctDateActive;
pkg_ken_migapi.t_cb(2).chg_who := pkg_ken_util.g_Chg_Who;
pkg_ken_migapi.t_cb(2).converted := 0;
pkg_ken_migapi.t_cb(2).new_charge_credits := 0;
pkg_ken_migapi.t_cb(2).gl_amount := 500000000;
pkg_ken_migapi.t_cb(2).ppdd_date := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date + 4,
1);
pkg_ken_migapi.t_cb(2).orig_ppdd_date := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date + 4,
1);
/*********************** CMF_BALANCE_DETAIL ******************************************************************/
pkg_ken_migapi.t_cbd(2).account_no := v_AccountNo;
pkg_ken_migapi.t_cbd(2).currency_code := 14;
pkg_ken_migapi.t_cbd(2).bill_ref_no := v_BillRefNo;
pkg_ken_migapi.t_cbd(2).bill_ref_resets := 0;
pkg_ken_migapi.t_cbd(2).new_charges := 500000000;
pkg_ken_migapi.t_cbd(2).net_new_charges := 500000000;
pkg_ken_migapi.t_cbd(2).total_due := 500000000;
pkg_ken_migapi.t_cbd(2).total_adj := 0;
pkg_ken_migapi.t_cbd(2).total_paid := 0;
pkg_ken_migapi.t_cbd(2).balance_due := 500000000;
pkg_ken_migapi.t_cbd(2).dispute_amt := 0;
pkg_ken_migapi.t_cbd(2).late_exempt_charges := 0;
pkg_ken_migapi.t_cbd(2).collection_indicator := 0;
pkg_ken_migapi.t_cbd(2).chg_date := v_AcctDateActive;
pkg_ken_migapi.t_cbd(2).chg_who := pkg_ken_util.g_Chg_Who;
pkg_ken_migapi.t_cbd(2).converted := 0;
pkg_ken_migapi.t_cbd(2).new_charge_credits := 0;
pkg_ken_migapi.t_cbd(2).open_item_id := 1;
pkg_ken_migapi.t_cbd(2).gl_amount := 500000000;
pkg_ken_migapi.t_cbd(2).ppdd_date := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date + 4,
1);
pkg_ken_migapi.t_cbd(2).orig_ppdd_date := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date + 4,
1);
END IF;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -