📄 pkg_ken_etl.pck
字号:
pkg_ken_migapi.t_cmf(1).cust_phone1 := rec_cust.CUST_PHONE;
pkg_ken_migapi.t_cmf(1).cust_faxno := rec_cust.CUST_FAX;
pkg_ken_migapi.t_cmf(1).sales_code := rec_cust.STAFF_ID;
pkg_ken_migapi.t_cmf(1).cust_city := trim(rec_cust.CUST_ADDR_CITY);
pkg_ken_migapi.t_cmf(1).cust_zip := rec_cust.CUST_ADDR_ZIPCODE;
pkg_ken_migapi.t_cmf(1).cust_address1 := trim(rec_cust.CUST_ADDR);
pkg_ken_migapi.t_cmf(1).cust_state := pkg_ken_util.g_Province;
pkg_ken_migapi.t_cmf(1).cust_franchise_tax_code := v_FranTaxCode;
pkg_ken_migapi.t_cmf(1).cust_email := rec_cust.CUST_EMAIL;
pkg_ken_migapi.t_cmf(1).mkt_code := v_CustMkt;
pkg_ken_migapi.t_cmf(1).distr_chan := rec_cust.CUST_CHANNEL;
pkg_ken_migapi.t_cmf(1).contact1_name := trim(rec_cust.CONTACT_NAME);
pkg_ken_migapi.t_cmf(1).contact1_phone := rec_cust.CONTACT_DATE_PHONE;
pkg_ken_migapi.t_cmf(1).cust_county := trim(rec_cust.CUST_ADDR_COUNTY);
pkg_ken_migapi.t_cmf(1).date_active := v_CustDateActive;
pkg_ken_migapi.t_cmf(1).cust_country_code := 156;
pkg_ken_migapi.t_cmf(1).bill_country_code := 156;
pkg_ken_migapi.t_cmf(1).msg_grp_id := 1;
pkg_ken_migapi.t_cmf(1).CUST_GEOCODE := 156001000000000;
pkg_ken_migapi.t_cmf(1).pay_method := 1;
pkg_ken_migapi.t_cmf(1).bill_period := 'M01';
pkg_ken_migapi.t_cmf(1).billing_frequency := 3;
pkg_ken_migapi.t_cmf(1).remark := rec_cust.REMARK;
/******************** 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 := trunc(v_CustDateActive);
pkg_ken_migapi.t_csh(1).chg_who := pkg_ken_util.g_Chg_Who;
pkg_ken_migapi.t_csh(1).chg_dt := v_CustDateActive;
/**************** SERVER_LOOKUP *******************************************/
pkg_ken_migapi.t_sl(1).account_no := v_AccountNo;
pkg_ken_migapi.t_sl(1).account_category := v_AccountCategory;
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_cust.CUST_NAME);
pkg_ken_migapi.t_sl(1).bill_lname_find := trim(UPPER(rec_cust.CUST_NAME));
pkg_ken_migapi.t_sl(1).bill_address1 := trim(rec_cust.CUST_ADDR);
pkg_ken_migapi.t_sl(1).bill_zip := rec_cust.CUST_ADDR_ZIPCODE;
pkg_ken_migapi.t_sl(1).bill_city := trim(rec_cust.CUST_ADDR_CITY);
pkg_ken_migapi.t_sl(1).bill_state := pkg_ken_util.g_Province;
pkg_ken_migapi.t_sl(1).cust_phone1 := rec_cust.CUST_PHONE;
/*build up the relationship between external_id(Siebel or 97 cust_id) and account_no(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 := trunc(v_CustDateActive);
/***************** 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 := trunc(v_CustDateActive);
/*************** EXTERNAL_ID_ACCT_MAP *********************************************************/
IF rec_cust.IDEN_NUM IS NOT NULL THEN
pkg_ken_migapi.t_eiam(2).external_id := rec_cust.IDEN_NUM;
pkg_ken_migapi.t_eiam(2).account_no := v_AccountNo;
pkg_ken_migapi.t_eiam(2).external_id_type := v_CustIdentType;
pkg_ken_migapi.t_eiam(2).server_id := v_ServerID;
pkg_ken_migapi.t_eiam(2).active_date := trunc(v_CustDateActive);
/************************ CUSTOMER_ID_ACCT_MAP ***********************************************/
pkg_ken_migapi.t_ciam(2).external_id := rec_cust.IDEN_NUM;
pkg_ken_migapi.t_ciam(2).account_no := v_AccountNo;
pkg_ken_migapi.t_ciam(2).external_id_type := v_CustIdentType;
pkg_ken_migapi.t_ciam(2).is_current := 1;
pkg_ken_migapi.t_ciam(2).active_date := trunc(v_CustDateActive);
END IF;
/**************** CMF_EXT_DATA ************************************************************/
IF rec_cust.VIP_NBR IS NOT NULL THEN
pkg_ken_migapi.t_ced(i_ced).ACCOUNT_NO := v_AccountNo;
pkg_ken_migapi.t_ced(i_ced).PARAM_ID := 101;
pkg_ken_migapi.t_ced(i_ced).PARAM_VALUE := rec_cust.VIP_NBR;
pkg_ken_migapi.t_ced(i_ced).PARAM_DATATYPE := 2;
i_ced := i_ced + 1;
v_VipFlag := 'Y';
END IF;
IF rec_cust.STAR_LEVEL IS NOT NULL THEN
pkg_ken_migapi.t_ced(i_ced).ACCOUNT_NO := v_AccountNo;
pkg_ken_migapi.t_ced(i_ced).PARAM_ID := 102;
pkg_ken_migapi.t_ced(i_ced).PARAM_VALUE := v_StarLevel;
pkg_ken_migapi.t_ced(i_ced).PARAM_DATATYPE := 2;
i_ced := i_ced + 1;
v_StarFlag := 'Y';
END IF;
exception
when others then
if pkg_ken_util.FUN_LOGIC_EXCEPTION = 1 then
pkg_ken_util.PRC_LOG_STATUS('SRC_CUST',
'CUST_ID',
rec_cust.cust_id,
null,
null,
null,
'F',
rec_cust.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;
--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
pkg_ken_migapi.prc_ins_cmf(v_DBUSER);
--CMF_STATUS_HISTORY
pkg_ken_migapi.prc_ins_csh(v_DBUSER);
-- catalog.server_lookup
pkg_ken_migapi.prc_ins_sl(v_MigCatSchema);
--external_id_acct_map
pkg_ken_migapi.prc_ins_eiam(v_MigCatSchema);
--CUSTOMER_ID_ACCT_MAP
pkg_ken_migapi.prc_ins_ciam(v_DBUSER);
--CMF_EXT_DATA
If v_VipFlag = 'Y' or v_StarFlag = 'Y' then
pkg_ken_migapi.prc_ins_ced(v_DBUSER);
END IF;
pkg_ken_util.PRC_LOG_STATUS('SRC_CUST',
'CUST_ID',
rec_cust.cust_id,
v_AccountNo,
v_ServerID,
v_DBUser,
'S',
rec_cust.src_id,
v_AccountCategory,
v_CustMkt,
null,
null,
null);
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;
if v_FailRecord <> 0 then
pkg_ken_util.prc_upd_lps(v_ProcessName,
v_SucceedRecord,
v_FailRecord,
'SP');
else
pkg_ken_util.prc_upd_lps(v_ProcessName,
v_SucceedRecord,
v_FailRecord,
'CM');
end if;
commit;
close c_CUST;
EXCEPTION
WHEN OTHERS THEN
close c_CUST;
pkg_ken_util.prc_handle_exception(v_ProcessName, p_Mode, v_RowId);
END PRC_T_CA;
/**************************** BA **************************/
PROCEDURE PRC_T_BA(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) := 'BA';
/*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_RowID src_acct.row_id%TYPE;
/*variable related to kenan tables*/
v_AccountNo CMF.account_no%TYPE; --KENAN internal acct id
v_BillRefNo BILL_INVOICE.BILL_REF_NO%TYPE;
v_AccountStatus CMF.account_status%TYPE; --account status
v_AccountArea CMF.rate_class_default%TYPE; --account area
v_PayMethod CMF.pay_method%TYPE; --pay method
v_IsSendCash CMF_EXT_DATA.PARAM_VALUE%TYPE; --XIAN JIN SONG JIAO
v_BankID CMF.clearing_house_id%TYPE; --bank id
v_BankAgencyId CMF.BANK_AGENCY_CODE%TYPE; --bank agency id
v_BankAgencyName CMF.Bank_Agency_Name%TYPE; --bank agency name
v_CustBankAccName CMF.cust_bank_acc_name%TYPE; --bank acct name
v_CustBankAccNum CMF.cust_bank_acc_num%TYPE; --bank acct num
v_AcctDateActive CMF.DATE_ACTIVE%TYPE; --acct date active
v_ParertID CMF.parent_id%TYPE;
v_OwningCostCtr CMF.OWNING_COST_CTR%TYPE; --owning cost center
v_PrevCutDate DATE; --prev cutoff date
v_PrevBillDate DATE; --prev bill date
v_NextBillDate DATE;
v_AcctSegId CMF.ACCT_SEG_ID%TYPE;
v_ExternalID EXTERNAL_ID_ACCT_MAP.EXTERNAL_ID%TYPE;
v_MigCatSchema varchar2(12) := PKG_KEN_UTIL.g_MIGCAT_Schema;
v_MigAdmSchema varchar2(12) := PKG_KEN_UTIL.g_MIGADM_Schema;
v_ExtData1 varchar2(40);
v_ExtData2 varchar2(40);
v_Prepay NUMBER(3);
/*variable related to MSA*/
v_ServerID EXTERNAL_ID_ACCT_MAP.server_id%TYPE; --server id of customer db
v_DBUSER CFG_KEN_MSA.MIG_SCHEMA%TYPE;
i_csc number;
v_BillFlag varchar2(2);
/* v_status varchar2(10);*/
CURSOR c_ACCT IS
SELECT *
FROM SRC_ACCT sa
WHERE sa.is_processed = p_MODE
AND (sa.SRC_ID = p_CITY || '97' OR sa.SRC_ID = p_CITY || 'Bl')
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -