📄 pkg_ken_dm.pck
字号:
select db_sid
into l_DbCatSid
from cfg_ken_msa
where mig_release = 'CAT';
for c_sad in (select distinct city
from cfg_ken_ms
where release = p_RELEASE) loop
for rec_acct in (select *
from src_acct_dm
where src_id like c_sad.city || '%'
and is_processed = 'N') loop
l_RowId := rec_acct.row_id;
l_OwningCostCtr := pkg_ken_util.FUN_NAME2CODE(c_sad.city);
l_ExternalID := pkg_ken_util.FUN_ENCODE(rec_acct.acct_nbr_97,
c_sad.city,
'BA');
EXECUTE IMMEDIATE 'select count(*) from arbor.external_id_acct_map' || '@' ||
l_DbCatSid ||
' where external_id = :l_ExternalID and external_id_type = 2'
into l_count
using l_ExternalID;
if l_count > 0 then
EXECUTE IMMEDIATE 'select distinct account_no,server_id from arbor.external_id_acct_map' || '@' ||
l_DbCatSid ||
' where external_id = :l_ExternalID and external_id_type = 2'
into l_ACCOUNT_NO, l_OldServerID
using l_ExternalID;
/* l_AccountStatus := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.SRC_ID,
'ACCT_STATUS',
rec_acct.ACCT_STATUS));*/
l_AccountArea := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.SRC_ID,
'ACCT_AREA',
rec_acct.ACCT_AREA));
l_PayMethod := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.SRC_ID,
'PAYMT_METH',
rec_acct.PAYMT_METH));
l_BankID := null;
l_BankAgencyId := null;
l_BankAgencyName := null;
l_CustBankAccName := null;
l_CustBankAccNum := null;
if l_PayMethod = 1 then
l_IsSendCash := 'N';
elsif l_PayMethod = 103 then
l_IsSendCash := 'Y';
l_PayMethod := 1;
elsif l_PayMethod = 3 then
l_BankID := pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.src_id,
'BANK_ID',
rec_acct.bank_id);
l_BankAgencyId := pkg_ken_util.FUN_CFG_LOV_MAP(rec_acct.src_id,
'BANK_BRANCH_ID',
rec_acct.bank_branch_id);
l_BankAgencyName := rec_acct.bank_branch_name;
l_CustBankAccName := rec_acct.bk_accnt_name;
l_CustBankAccNum := SUBSTRB(rec_acct.bk_accnt_num, 1, 30);
l_IsSendCash := 'N';
end if;
l_AcctDateActive := TRUNC(rec_acct.ACCT_CREATED_DATE);
IF l_AcctDateActive < pkg_ken_util.g_Cutoff_Date THEN
l_AccountStatus := 0;
else
l_AccountStatus := -1;
end if;
EXECUTE IMMEDIATE 'insert into src_acct_patch
(
ACCT_NBR_97
,CUST_ID
,ACCT_STATUS
,ACCT_AREA
,ACCT_PHONE
,BILL_ADDR_CITY
,BILL_ADDR
,BILL_ADDR_ZIPCODE
,ACCT_EMAIL
,PAYMT_METH
,BANK_ID
,BANK_BRANCH_ID
,BANK_BRANCH_NAME
,BK_ACCNT_NAME
,BK_ACCNT_NUM
,ACCT_CREATED_DATE
,ACCT_STATUS_DATE
,REMARK
,BILL_ADDR_COUNTY
,SRC_ID
,SRC_FILESET
,SERVER_ID
,COLLECTION_FLAG
,ACCT_NAME
,ACCOUNT_NO
,OWNING_COST_CTR
,IsSendCash
,CITY
,is_processed
)
values
(
:ACCT_NBR_97
,:CUST_ID
,:ACCT_STATUS
,:ACCT_AREA
,:ACCT_PHONE
,:BILL_ADDR_CITY
,:BILL_ADDR
,:BILL_ADDR_ZIPCODE
,:ACCT_EMAIL
,:PAYMT_METH
,:BANK_ID
,:BANK_BRANCH_ID
,:BANK_BRANCH_NAME
,:BK_ACCNT_NAME
,:BK_ACCNT_NUM
,:ACCT_CREATED_DATE
,:ACCT_STATUS_DATE
,:REMARK
,:BILL_ADDR_COUNTY
,:SRC_ID
,:SRC_FILESET
,:SERVER_ID
,:COLLECTION_FLAG
,:ACCT_NAME
,:ACCOUNT_NO
,:OWNING_COST_CTR
,:IsSendCash
,:CITY
,:is_processed
)'
using rec_acct.ACCT_NBR_97, rec_acct.CUST_ID, l_AccountStatus, l_AccountArea, rec_acct.ACCT_PHONE, rec_acct.BILL_ADDR_CITY, rec_acct.BILL_ADDR, rec_acct.BILL_ADDR_ZIPCODE, rec_acct.ACCT_EMAIL, l_PayMethod, l_BankID, l_BankAgencyId, l_BankAgencyName, l_CustBankAccName, l_CustBankAccNum, l_AcctDateActive, rec_acct.ACCT_STATUS_DATE, rec_acct.REMARK, rec_acct.BILL_ADDR_COUNTY, rec_acct.SRC_ID, rec_acct.SRC_FILESET, l_OldServerID, nvl(rec_acct.COLLECTION_FLAG, 1), rec_acct.ACCT_NAME, l_ACCOUNT_NO, l_OwningCostCtr, l_IsSendCash, c_sad.city, 'P';
EXECUTE IMMEDIATE 'update src_acct_dm set is_processed =:1 where ACCT_NBR_97 = :2 and src_id =:3'
using 'P', rec_acct.ACCT_NBR_97, rec_acct.src_id;
else
EXECUTE IMMEDIATE 'insert into src_acct select * from src_acct_dm where ACCT_NBR_97 = :ACCT_NBR_97 and src_id =:src_id'
using rec_acct.ACCT_NBR_97, rec_acct.src_id;
EXECUTE IMMEDIATE 'update src_acct_dm set is_processed =:1 where ACCT_NBR_97 = :2 and src_id =:3'
using 'T', rec_acct.ACCT_NBR_97, rec_acct.src_id;
end if;
l_Counter := l_Counter + 1;
IF l_Counter = c_CommitCounter THEN
COMMIT;
l_Counter := 0;
END IF;
end loop;
commit;
end loop;
EXCEPTION
WHEN OTHERS THEN
v_ErrorCode := SQLCODE;
v_ErrorMsg := SUBSTR(SQLERRM, 1, 2000);
v_CurrentUser := USER;
v_Information := 'row_id=' || to_char(l_RowId) ||
'. Error encoutered on ' ||
TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') ||
' by database user ' || v_CurrentUser;
rollback;
l_ProName := 'Filter_BA';
insert into log_process_error
(PROCESS_NAME, ERROR_CODE, ERROR_MESSAGE, ERROR_INFO, ROW_ID)
VALUES
(l_ProName || l_city,
v_ErrorCode,
v_ErrorMsg,
v_Information,
l_RowId);
commit;
end Filter_BA;
/************ Filter others *************************************/
/*procedure Filter_others as
v_ErrorCode NUMBER; --Code for the error
v_ErrorMsg VARCHAR2(2000); --Message text for the error
v_Information VARCHAR2(100); --Information about the error
l_ProName varchar2(100);
begin
insert into src_serv
select * from src_serv_dm;
insert into src_serv_num
select * from src_serv_num_dm;
insert into src_serv_chg_num
select * from src_serv_chg_num_dm;
insert into src_prod
select * from src_prod_dm;
insert into src_discount
select * from src_discount_dm;
commit;
exception
WHEN OTHERS THEN
v_ErrorCode := SQLCODE;
v_ErrorMsg := SUBSTR(SQLERRM, 1, 2000);
v_Information := null;
rollback;
l_ProName := 'Filter_others';
insert into log_process_error
(PROCESS_NAME, ERROR_CODE, ERROR_MESSAGE, ERROR_INFO, ROW_ID)
VALUES
(l_ProName, v_ErrorCode, v_ErrorMsg, v_Information, 9999);
commit;
end Filter_others;*/
/********* PATCH CA ***********************************************/
procedure patch_UpdateCA_dm(p_RELEASE in varchar2) is
l_CountIdenNum number;
/* l_CountParamId number;*/
l_CountVipNbr number;
l_CountStraLevel number;
l_DbUserSid varchar2(10);
l_DbCatSid varchar2(10);
l_ServerId number(20);
l_Count number(10) := 0;
l_ExternalID varchar2(50);
l_ExternalIdType number(6);
l_ChgWho varchar2(20);
l_TruncSysdate date;
cursor c_cust_patch is
select * from src_cust_patch where is_processed = 'P';
rec_patch c_cust_patch%rowtype;
begin
l_ChgWho := pkg_ken_util.g_Chg_Who;
l_TruncSysdate := trunc(g_SystemDt);
OPEN c_cust_patch;
<<NEXTLOOP01555>>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -