📄 pkg_ken_dm.pck
字号:
LOOP
BEGIN
FETCH c_cust_patch
INTO rec_patch;
EXIT WHEN c_cust_patch%NOTFOUND;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1555 THEN
ROLLBACK;
CLOSE c_cust_patch;
OPEN c_cust_patch;
GOTO NEXTLOOP01555;
ELSE
RAISE;
END IF;
END;
EXECUTE IMMEDIATE 'select server_id from src_cust_status where old_id =:1 and src_id =:2'
into l_ServerId
using rec_patch.cust_id, rec_patch.src_id;
EXECUTE IMMEDIATE 'select db_sid from cfg_ken_msa where server_id =:1 and mig_release in(''VIP'',:2)'
into l_DbUserSid
using l_ServerId, p_RELEASE;
EXECUTE IMMEDIATE 'select db_sid from cfg_ken_msa where mig_release = :2'
into l_DbCatSid
using 'CAT';
EXECUTE IMMEDIATE 'update arbor.cmf' || '@' || l_DbUserSid ||
' set
bill_lname = :CUST_NAME
,cust_phone1 = :CUST_PHONE
,account_category = :l_AccountCategory
,acct_seg_id = :l_AcctSegId
,cust_city = :CUST_ADDR_CITY
,cust_zip = :CUST_ADDR_ZIPCODE
,cust_address1 = :CUST_ADDR
,cust_faxno = :CUST_FAX
,sales_code = :STAFF_ID
,cust_county = :CUST_ADDR_COUNTY
,cust_franchise_tax_code = :CUST_INDUSTRY
,cust_email = :CUST_EMAIL
,mkt_code = :l_CustMkt
,distr_chan = :CUST_CHANNEL
,contact1_name = :CONTACT_NAME
,contact1_phone = :CONTACT_DATE_PHONE
,account_status_dt = :CUST_STATUS_DATE
,remark = :REMARK
,vip_code = :COLLECTION_FLAG
,chg_date = :chg_date
,chg_who = :l_ChgWho
,owning_cost_ctr = :l_OwningCostCtr
where account_no = :l_account_no'
using rec_patch.CUST_NAME, rec_patch.CUST_PHONE, rec_patch.CUST_TYPE, rec_patch.ACCT_SEG_ID, rec_patch.CUST_ADDR_CITY, rec_patch.CUST_ADDR_ZIPCODE, rec_patch.CUST_ADDR, rec_patch.CUST_FAX, rec_patch.STAFF_ID, rec_patch.CUST_ADDR_COUNTY, rec_patch.CUST_INDUSTRY, rec_patch.CUST_EMAIL, rec_patch.MKT_CODE, rec_patch.CUST_CHANNEL, rec_patch.CONTACT_NAME, rec_patch.CONTACT_DATE_PHONE, rec_patch.CUST_STATUS_DATE, rec_patch.REMARK, rec_patch.COLLECTION_FLAG, g_SystemDt, l_ChgWho, rec_patch.owning_cost_ctr, rec_patch.ACCOUNT_NO;
/* EXECUTE IMMEDIATE 'update CMF_STATUS_HISTORY' || '@' || l_DbUserSid ||
' set
chg_dt = sysdate
where account_no = :l_account_no'
using rec_patch.ACCOUNT_NO;*/
EXECUTE IMMEDIATE 'update arbor.SERVER_LOOKUP' || '@' || l_DbCatSid ||
' set
account_category = :l_AccountCategory
,acct_seg_id = :l_AcctSegId
,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 rec_patch.CUST_TYPE, rec_patch.ACCT_SEG_ID, rec_patch.CUST_NAME, UPPER(rec_patch.CUST_NAME), rec_patch.CUST_ADDR, rec_patch.CUST_ADDR_ZIPCODE, rec_patch.CUST_ADDR_CITY, rec_patch.CUST_PHONE, rec_patch.ACCOUNT_NO;
IF rec_patch.IDEN_NUM IS NOT NULL THEN
EXECUTE IMMEDIATE 'select count(*) from arbor.EXTERNAL_ID_ACCT_MAP' || '@' ||
l_DbCatSid ||
' where external_id_type in (501,502,503,504,505,506,507) and account_no = :l_account_no'
into l_CountIdenNum
using rec_patch.ACCOUNT_NO;
--IF EXIST THEN INACTIVE
if l_CountIdenNum > 0 then
EXECUTE IMMEDIATE ' select external_id, external_id_type
from arbor.external_id_acct_map' || '@' ||
l_DbCatSid ||
' where external_id_type in (501, 502, 503, 504, 505, 506, 507)
and account_no = :1
and inactive_date is null'
INTO l_ExternalID, l_ExternalIdType
USING rec_patch.ACCOUNT_NO;
if l_ExternalID <> rec_patch.IDEN_NUM or
l_ExternalIdType <> rec_patch.IDEN_TYPE then
EXECUTE IMMEDIATE 'update arbor.EXTERNAL_ID_ACCT_MAP' || '@' ||
l_DbCatSid ||
' set
INACTIVE_DATE = :inactive_date
where account_no = :l_account_no and external_id_type in (501,502,503,504,505,506,507)
and INACTIVE_DATE is null'
using l_TruncSysdate, rec_patch.ACCOUNT_NO;
EXECUTE IMMEDIATE 'update arbor.CUSTOMER_ID_ACCT_MAP' || '@' ||
l_DbUserSid ||
' set
INACTIVE_DATE = :INACTIVE_DATE,
is_current =:is_current
where account_no = :l_account_no and external_id_type in (501,502,503,504,505,506,507)
and INACTIVE_DATE is null'
using l_TruncSysdate,0, rec_patch.ACCOUNT_NO;
--insert new record
EXECUTE IMMEDIATE 'insert into arbor.EXTERNAL_ID_ACCT_MAP' || '@' ||
l_DbCatSid || '
(external_id
,account_no
,external_id_type
,server_id
,active_date
)
values
(
:external_id
,:account_no
,:external_id_type
,:server_id
,:active_date
)'
using rec_patch.IDEN_NUM, rec_patch.ACCOUNT_NO, rec_patch.IDEN_TYPE, l_ServerId, l_TruncSysdate;
EXECUTE IMMEDIATE 'insert into arbor.CUSTOMER_ID_ACCT_MAP' || '@' ||
l_DbUserSid || '
(external_id
,account_no
,external_id_type
,is_current
,active_date
)
values
(
:external_id
,:account_no
,:external_id_type
,1
,:active_date
)'
using rec_patch.IDEN_NUM, rec_patch.ACCOUNT_NO, rec_patch.IDEN_TYPE, l_TruncSysdate;
End if;
else
EXECUTE IMMEDIATE 'insert into arbor.EXTERNAL_ID_ACCT_MAP' || '@' ||
l_DbCatSid || '
(external_id
,account_no
,external_id_type
,server_id
,active_date
)
values
(
:external_id
,:account_no
,:external_id_type
,:server_id
,:active_date
)'
using rec_patch.IDEN_NUM, rec_patch.ACCOUNT_NO, rec_patch.IDEN_TYPE, l_ServerId, l_TruncSysdate;
EXECUTE IMMEDIATE 'insert into arbor.CUSTOMER_ID_ACCT_MAP' || '@' ||
l_DbUserSid || '
(external_id
,account_no
,external_id_type
,is_current
,active_date
)
values
(
:external_id
,:account_no
,:external_id_type
,1
,:active_date
)'
using rec_patch.IDEN_NUM, rec_patch.ACCOUNT_NO, rec_patch.IDEN_TYPE, l_TruncSysdate;
End if;
END IF;
IF rec_patch.VIP_NBR IS NOT NULL THEN
EXECUTE IMMEDIATE 'select count(*) from arbor.CMF_EXT_DATA' || '@' ||
l_DbUserSid ||
' where account_no = :account_no and PARAM_ID = 101'
into l_CountVipNbr
using rec_patch.account_no;
if l_CountVipNbr > 0 then
EXECUTE IMMEDIATE ' update arbor.CMF_EXT_DATA' || '@' ||
l_DbUserSid ||
' set
PARAM_VALUE = :VIP_NBR
where account_no = :l_account_no and PARAM_ID = 101'
using rec_patch.VIP_NBR, 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
,101
,:VIP_NBR
,2
)'
using rec_patch.account_no, rec_patch.VIP_NBR;
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;
IF rec_patch.STAR_LEVEL IS NOT NULL THEN
EXECUTE IMMEDIATE 'select count(*) from arbor.CMF_EXT_DATA' || '@' ||
l_DbUserSid ||
' where account_no = :account_no and PARAM_ID = 102'
into l_CountStraLevel
using rec_patch.account_no;
if l_CountStraLevel > 0 then
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -