📄 pkg_ken_dm.pck
字号:
create or replace package PKG_KEN_DM is
-- Author : Mao,Xufei
-- Created : 2006-5-31 19:11:40
-- Purpose :
-- Public variable declarations
g_SystemDt date := sysdate;
-- Public type declarations
procedure Filter_CA(p_RELEASE in varchar2);
Procedure Filter_BA(p_RELEASE in varchar2);
/* procedure Filter_others;*/
procedure patch_UpdateCA_dm(p_RELEASE in varchar2);
PROCEDURE patch_UpdateBA_dm(p_RELEASE in varchar2);
/*PROCEDURE DM_SI_create_temp_table(SI_LIST_TABLE VARCHAR2);
PROCEDURE dm_si_backup_tbl_create(CAT_SERVICE_NAME VARCHAR2,
adm_service_name varchar2);
PROCEDURE dm_Trigger_Constraints_Op(OP VARCHAR2, IS_who VARCHAR2);
PROCEDURE dm_si_clean(bk_level VARCHAR2,
log_level VARCHAR2,
cat_service_name VARCHAR2,
adm_service_name varchar2);*/
end PKG_KEN_DM;
/
create or replace package body PKG_KEN_DM is
procedure Filter_CA(p_RELEASE in varchar2) is
c_CommitCounter CONSTANT NUMBER := 5000;
l_Counter NUMBER := 0;
l_ExternalID external_id_acct_map.external_id%type;
l_AccountCategory cmf.account_category%type;
l_account_no external_id_acct_map.account_no%type;
l_AcctSegId CMF.ACCT_SEG_ID%TYPE;
l_OwningCostCtr CMF.OWNING_COST_CTR%TYPE;
l_CustIdentType CUSTOMER_ID_ACCT_MAP.External_Id_Type%TYPE; --cust indent type
l_CustMkt CMF.mkt_code%TYPE; --cust market code
l_count number;
l_city varchar2(10);
l_ProName varchar2(20);
l_RowId src_cust.row_id%type;
l_DbCatSid varchar2(10);
v_ErrorCode NUMBER; --Code for the error
v_ErrorMsg VARCHAR2(2000); --Message text for the error
v_currentUser VARCHAR2(20); --Current database user
v_Information VARCHAR2(100); --Information about the error
e_CaPatchEiamErr exception;
pragma exception_init(e_CaPatchEiamErr, -20008);
begin
select db_sid
into l_DbCatSid
from cfg_ken_msa
where mig_release = 'CAT';
for c_scd in (select distinct city
from cfg_ken_ms
where release = p_RELEASE) loop
for rec_cust in (select *
from src_cust_dm
where src_id like c_scd.city || '%'
and is_processed = 'N') loop
l_RowId := rec_cust.row_id;
l_OwningCostCtr := pkg_ken_util.FUN_NAME2CODE(c_scd.city);
l_ExternalID := pkg_ken_util.FUN_ENCODE(rec_cust.cust_id,
c_scd.city,
'CA');
l_AccountCategory := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_cust.SRC_ID,
'CUST_TYPE',
rec_cust.CUST_TYPE));
l_CustIdentType := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_cust.SRC_ID,
'IDEN_TYPE',
rec_cust.IDEN_TYPE));
l_CustMkt := to_number(pkg_ken_util.FUN_CFG_LOV_MAP(rec_cust.SRC_ID,
'MKT_CODE',
rec_cust.MKT_CODE));
IF l_AccountCategory = 1 THEN
-- vip
l_AcctSegId := 10;
ELSE
-- normal
l_AcctSegId := l_OwningCostCtr;
END IF;
EXECUTE IMMEDIATE 'select count(*) from arbor.external_id_acct_map' || '@' ||
l_DbCatSid ||
' where external_id = :l_ExternalID and external_id_type = 1'
into l_count
using l_ExternalID;
if l_count > 0 then
begin
EXECUTE IMMEDIATE 'select distinct account_no from arbor.external_id_acct_map' || '@' ||
l_DbCatSid ||
' where external_id = :l_ExternalID and external_id_type =1'
into l_account_no
using l_ExternalID;
exception
when no_data_found or too_many_rows then
raise_application_error(-20008,
'external_id_acct_map for CA_PATCH is not correct');
end;
EXECUTE IMMEDIATE 'insert into src_cust_patch
(
CUST_ID
,SRC_ID
,CUST_NAME
,CUST_PHONE
,CUST_TYPE
,PAR_CUST_ID
,CUST_FAX
,STAFF_ID
,CUST_ADDR_CITY
,CUST_ADDR_COUNTY
,CUST_ADDR_ZIPCODE
,CUST_ADDR
,CUST_INDUSTRY
,IDEN_TYPE
,IDEN_NUM
,STAR_LEVEL
,VIP_NBR
,CUST_EMAIL
,MKT_CODE
,CUST_CHANNEL
,CONTACT_NAME
,CONTACT_DATE_PHONE
,CUST_CREATED_DATE
,CUST_STATUS_DATE
,REMARK
,COLLECTION_FLAG
,SRC_FILESET
,ACCT_SEG_ID
,ACCOUNT_NO
,owning_cost_ctr
,city
,is_processed
)
values
(
:CUST_ID
,:SRC_ID
,:CUST_NAME
,:CUST_PHONE
,:CUST_TYPE
,:PAR_CUST_ID
,:CUST_FAX
,:STAFF_ID
,:CUST_ADDR_CITY
,:CUST_ADDR_COUNTY
,:CUST_ADDR_ZIPCODE
,:CUST_ADDR
,:CUST_INDUSTRY
,:IDEN_TYPE
,:IDEN_NUM
,:STAR_LEVEL
,:VIP_NBR
,:CUST_EMAIL
,:MKT_CODE
,:CUST_CHANNEL
,:CONTACT_NAME
,:CONTACT_DATE_PHONE
,:CUST_CREATED_DATE
,:CUST_STATUS_DATE
,:REMARK
,:COLLECTION_FLAG
,:SRC_FILESET
,:ACCT_SEG_ID
,:ACCOUNT_NO
,:owning_cost_ctr
,:CITY
,:Is_processed
)'
using rec_cust.CUST_ID, rec_cust.SRC_ID, rec_cust.CUST_NAME, rec_cust.CUST_PHONE, l_AccountCategory, rec_cust.PAR_CUST_ID, rec_cust.CUST_FAX, rec_cust.STAFF_ID, rec_cust.CUST_ADDR_CITY, rec_cust.CUST_ADDR_COUNTY, rec_cust.CUST_ADDR_ZIPCODE, rec_cust.CUST_ADDR, rec_cust.CUST_INDUSTRY, l_CustIdentType, rec_cust.IDEN_NUM, rec_cust.STAR_LEVEL, rec_cust.VIP_NBR, rec_cust.CUST_EMAIL, l_CustMkt, rec_cust.CUST_CHANNEL, rec_cust.CONTACT_NAME, rec_cust.CONTACT_DATE_PHONE, rec_cust.CUST_CREATED_DATE, rec_cust.CUST_STATUS_DATE, rec_cust.REMARK, nvl(rec_cust.COLLECTION_FLAG, 1), rec_cust.SRC_FILESET, l_AcctSegId, l_account_no, l_OwningCostCtr, c_scd.city, 'P';
EXECUTE IMMEDIATE 'update src_cust_dm set is_processed =:1 where cust_id = :2 and src_id =:3'
using 'P', rec_cust.cust_id, rec_cust.src_id;
else
EXECUTE IMMEDIATE 'insert into src_cust select * from src_cust_dm where cust_id = :cust_id and src_id =:src_id'
using rec_cust.cust_id, rec_cust.src_id;
EXECUTE IMMEDIATE 'update src_cust_dm set is_processed =:1 where cust_id = :2 and src_id =:3'
using 'T', rec_cust.cust_id, rec_cust.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_CA';
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_CA;
/******************** Filter_BA *************************************/
procedure Filter_BA(p_RELEASE in varchar2) is
c_CommitCounter CONSTANT NUMBER := 5000;
l_Counter NUMBER := 0;
l_ExternalID external_id_acct_map.external_id%type;
l_account_no external_id_acct_map.account_no%type;
l_OwningCostCtr CMF.OWNING_COST_CTR%TYPE;
l_IsSendCash CMF_EXT_DATA.PARAM_VALUE%TYPE; --XIAN JIN SONG JIAO
l_BankID CMF.clearing_house_id%TYPE; --bank id
l_BankAgencyId CMF.BANK_AGENCY_CODE%TYPE; --bank agency id
l_BankAgencyName CMF.Bank_Agency_Name%TYPE; --bank agency name
l_CustBankAccName CMF.cust_bank_acc_name%TYPE; --bank acct name
l_CustBankAccNum CMF.cust_bank_acc_num%TYPE; --bank acct num
l_AcctDateActive CMF.DATE_ACTIVE%TYPE; --acct date active
l_AccountStatus CMF.account_status%TYPE; --account status
l_AccountArea CMF.rate_class_default%TYPE; --account area
l_PayMethod CMF.pay_method%TYPE; --pay method
l_count number;
l_city varchar2(10);
l_ProName varchar2(20);
l_OldServerID external_id_acct_map.server_id%TYPE;
l_RowId src_acct.row_id%type;
l_DbCatSid varchar2(10);
v_ErrorCode NUMBER; --Code for the error
v_ErrorMsg VARCHAR2(2000); --Message text for the error
v_currentUser VARCHAR2(20); --Current database user
v_Information VARCHAR2(100); --Information about the error
e_CaPatchEiamErr exception;
pragma exception_init(e_CaPatchEiamErr, -20008);
begin
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -