📄 pkg_ken_dm.pck
字号:
EXECUTE IMMEDIATE ' update arbor.CMF_EXT_DATA' || '@' ||
l_DbUserSid ||
' set
PARAM_VALUE = :STAR_LEVEL
where account_no = :l_account_no and PARAM_ID = 102'
using rec_patch.STAR_LEVEL, rec_patch.ACCOUNT_NO;
else
EXECUTE IMMEDIATE 'insert into arbor.CMF_EXT_DATA' || '@' ||
l_DbUserSid ||
' (ACCOUNT_NO
,PARAM_ID
,PARAM_VALUE
,PARAM_DATATYPE
)
values
(
:ACCOUNT_NO
,102
,:STAR_LEVEL
,2
)'
using rec_patch.account_no, rec_patch.STAR_LEVEL;
end if;
/* EXECUTE IMMEDIATE 'select count(*) from arbor.CMF_EXT_DATA' || '@' ||
l_DbUserSid ||
' where account_no = :account_no and PARAM_ID = 100'
into l_CountParamId
using rec_patch.account_no;
if l_CountParamId > 0 then*/
EXECUTE IMMEDIATE 'update arbor.CMF_EXT_DATA' || '@' || l_DbUserSid ||
' set
PARAM_VALUE = :PARAM_VALUE
where account_no = :account_no and PARAM_ID = :PARAM_ID'
using g_SystemDt, rec_patch.account_no, 100;
/* else
EXECUTE IMMEDIATE 'insert into arbor.CMF_EXT_DATA' || '@' ||
l_DbUserSid || '
(ACCOUNT_NO
,PARAM_ID
,PARAM_VALUE
,PARAM_DATATYPE
)
values
(
:ACCOUNT_NO
,100
,to_char(sysdate,''YYYY-MM-DD HH24:MI:SS'')
,2
)'
using rec_patch.account_no;
end if;*/
end if;
update src_cust_patch
set is_processed = 'S'
where cust_id = rec_patch.cust_id
and src_id = rec_patch.src_id;
l_Count := l_Count + 1;
if mod(l_Count, 5000) = 0 then
commit;
end if;
end loop;
commit;
close c_cust_patch;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error CUSTOMER ACCOUNT_NO is:' ||
rec_patch.ACCOUNT_NO);
DBMS_OUTPUT.put_line(SUBSTR(SQLERRM, 1, 200));
close c_cust_patch;
ROLLBACK;
END patch_UpdateCA_dm;
/*********** PATCH_BA ************************************************/
PROCEDURE patch_UpdateBA_dm(p_RELEASE in varchar2) AS
l_DbUserSid varchar2(10);
l_DbCatSid varchar2(10);
l_ServerId number(20);
l_Count number(10) := 0;
l_ChgWho varchar2(20);
l_ParertID CMF.parent_id%TYPE;
l_DBUSER cfg_ken_msa.mig_schema%type;
v_ExtData1 varchar2(40); --CUST_TYPE
v_ExtData2 varchar2(40); --MKT_CODE
l_AccountCategory cmf.account_category%type;
l_AcctSegId CMF.ACCT_SEG_ID%TYPE;
l_TruncSysdate date;
cursor c_acct_patch is
select * from src_acct_patch where is_processed = 'P';
rec_patch c_acct_patch%rowtype;
begin
l_ChgWho := pkg_ken_util.g_Chg_Who;
l_TruncSysdate := trunc(g_SystemDt);
OPEN c_acct_patch;
<<NEXTLOOP01555>>
LOOP
BEGIN
FETCH c_acct_patch
INTO rec_patch;
EXIT WHEN c_acct_patch%NOTFOUND;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1555 THEN
ROLLBACK;
CLOSE c_acct_patch;
OPEN c_acct_patch;
GOTO NEXTLOOP01555;
ELSE
RAISE;
END IF;
END;
pkg_ken_util.PRC_PARENT_INFO('SRC_CUST_STATUS',
rec_patch.SRC_ID,
rec_patch.CUST_ID,
l_ParertID,
l_ServerID,
l_DBUSER);
EXECUTE IMMEDIATE 'select db_sid from cfg_ken_msa where server_id =:1 and mig_release in(''VIP'',:2)'
into l_DbUserSid
using rec_patch.server_id, p_RELEASE;
if l_ServerID <> rec_patch.server_id then
update src_acct_patch
set is_processed = 'MS'
where src_id = rec_patch.SRC_ID
and acct_nbr_97 = rec_patch.acct_nbr_97;
update src_acct_dm
set is_processed = 'MS'
where src_id = rec_patch.SRC_ID
and acct_nbr_97 = rec_patch.acct_nbr_97;
EXECUTE IMMEDIATE 'update arbor.cmf' || '@' || l_DbUserSid ||
' set parent_id =:1,
HIERARCHY_ID =:2,
remark =:3
where account_no =:4'
Using l_ParertID, l_ParertID, 'AMP', rec_patch.account_no;
-- GOTO NEXTLOOP01555;
end if;
EXECUTE IMMEDIATE 'select db_sid from cfg_ken_msa where mig_release = :2'
into l_DbCatSid
using 'CAT';
select ext_data_1, ext_data_2
into v_ExtData1, v_ExtData2
from src_cust_status
where old_id = rec_patch.CUST_ID
and src_id = rec_patch.SRC_ID;
l_AccountCategory := v_ExtData1;
IF l_AccountCategory = 1 THEN
-- vip
l_AcctSegId := 10;
ELSE
-- normal
l_AcctSegId := rec_patch.OWNING_COST_CTR;
END IF;
EXECUTE IMMEDIATE 'update arbor.CMF_EXT_DATA' || '@' || l_DbUserSid ||
' set
PARAM_VALUE =:ISSENDCASH
where account_no = :account_no and PARAM_ID = 103'
using rec_patch.ISSENDCASH, rec_patch.account_no;
EXECUTE IMMEDIATE 'update arbor.CMF_EXT_DATA' || '@' || l_DbUserSid ||
' set
PARAM_VALUE = :PARAM_VALUE
where account_no = :account_no and PARAM_ID = :PARAM_ID'
using g_SystemDt, rec_patch.account_no, 100;
EXECUTE IMMEDIATE 'update arbor.cmf' || '@' || l_DbUserSid ||
' set
account_category = :account_category
,mkt_code = :mkt_code
,account_status = :account_status
,account_status_dt = :account_status_dt
,VIP_CODE = :VIP_CODE
,chg_date = :chg_date
,owning_cost_ctr = :owning_cost_ctr
,acct_seg_id = :acct_seg_id
,bill_lname = :bill_lname
,cust_phone1 = :cust_phone1
,bill_city = :bill_city
,bill_address1 = :bill_address1
,bill_zip = :bill_zip
,cust_email = :cust_email
,bill_county = :bill_county
,rate_class_default = :rate_class_default
,remark = :remark
,pay_method = :pay_method
,clearing_house_id = :clearing_house_id
,bank_agency_code = :bank_agency_code
,bank_agency_name = :bank_agency_name
,cust_bank_acc_name = :cust_bank_acc_name
,cust_bank_acc_num = :cust_bank_acc_num
,cust_bank_sort_code = :cust_bank_sort_code
,chg_who =:l_ChgWho
,date_created =least(:date_created,date_created)
,date_active =least(:date_active,date_active)
where account_no = :account_no'
using l_AccountCategory, v_ExtData2, rec_patch.ACCT_STATUS, rec_patch.ACCT_STATUS_DATE, rec_patch.COLLECTION_FLAG, l_TruncSysdate, rec_patch.owning_cost_ctr, l_AcctSegId, rec_patch.ACCT_NAME, rec_patch.ACCT_PHONE, rec_patch.BILL_ADDR_CITY, rec_patch.BILL_ADDR, rec_patch.BILL_ADDR_ZIPCODE, rec_patch.ACCT_EMAIL, rec_patch.BILL_ADDR_COUNTY, rec_patch.ACCT_AREA, rec_patch.remark, rec_patch.paymt_meth, rec_patch.BANK_ID, rec_patch.BANK_BRANCH_ID, rec_patch.BANK_BRANCH_NAME, rec_patch.BK_ACCNT_NAME, rec_patch.BK_ACCNT_NUM, rec_patch.BANK_ID || rec_patch.BANK_BRANCH_ID, l_ChgWho, rec_patch.acct_created_date, rec_patch.acct_created_date, rec_patch.account_no;
EXECUTE IMMEDIATE 'update arbor.SERVER_LOOKUP' || '@' || l_DbCatSid ||
' set
account_category = :account_category
,acct_seg_id = :acct_seg_id
,bill_lname = :CUST_NAME
,bill_lname_find = :CUST_NAME
,bill_address1 = :CUST_ADDR
,bill_zip = :CUST_ADDR_ZIPCODE
,bill_city = :CUST_ADDR_CITY
,cust_phone1 = :CUST_PHONE
where account_no = :l_account_no'
using l_AccountCategory, l_AcctSegId, rec_patch.ACCT_NAME, UPPER(rec_patch.ACCT_NAME), rec_patch.BILL_ADDR, rec_patch.BILL_ADDR_ZIPCODE, rec_patch.BILL_ADDR_CITY, rec_patch.ACCT_PHONE, rec_patch.account_no;
EXECUTE IMMEDIATE 'update arbor.cmf_package' || '@' || l_DbUserSid ||
' set active_dt = :1
where parent_account_no =:2
and active_dt >= :3'
Using rec_patch.acct_created_date, rec_patch.account_no, rec_patch.acct_created_date;
update src_acct_patch
set is_processed = 'S'
where acct_nbr_97 = rec_patch.acct_nbr_97
and src_id = rec_patch.src_id;
l_Count := l_Count + 1;
if mod(l_Count, 5000) = 0 then
commit;
end if;
end loop;
COMMIT;
close c_acct_patch;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error BILL ACCOUNT_NO is:' ||
rec_patch.ACCOUNT_NO);
DBMS_OUTPUT.put_line(SUBSTR(SQLERRM, 1, 200));
close c_acct_patch;
ROLLBACK;
END patch_UpdateBA_dm;
/********* END CA/BA PATCH **********************************/
begin
-- Initialization
null;
end PKG_KEN_DM;
/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -