📄 pkg_ken_util.pck
字号:
CREATE OR REPLACE PACKAGE pkg_ken_util IS
-- Author : YINGKUN.GU
-- Created : 2006-4-28 17:39:46
-- Purpose : ETL utility tools
-- Updated by : Li.Wan
-- Updated Date : 2006-6-19
-- Purpose : Add Logic Error Handle----FUN_LOGIC_EXCEPTION
-- Public type declarations
-- Public constant declarations
g_Back_Date constant date := to_date('2006-12-01', 'YYYY-MM-DD');
g_Cutoff_Date constant date := to_date('2006-11-01', 'YYYY-MM-DD');
g_Chg_Who constant varchar2(10) := 'MIT2';
g_Province constant varchar2(10) := '浙江省';
g_ErrUpperLimit constant number := 500000;
g_SystemDt date := sysdate;
-- Public variable declarations
g_MIGCAT_Schema varchar2(10);
g_MIGADM_Schema varchar2(10);
g_MIGADM_sid varchar2(10);
g_MIGCAT_Sid varchar2(10);
e_FatalError exception;
e_ErrUpperLimit exception;
e_CfgLovMap exception;
e_CfgKenMSA exception;
e_NoIdenNum exception;
e_NoVipNum exception;
e_NoParent exception;
e_CalStream exception;
e_InitLPS exception;
e_LPSData exception;
e_CompDefineErr EXCEPTION;
e_PkgCompMembersErr EXCEPTION;
e_PkgCompnentsErr EXCEPTION;
e_AcctKenanErr EXCEPTION;
e_ServIDKenanErr EXCEPTION;
e_MemberIDKenanErr EXCEPTION;
e_PlanIDKenanErr EXCEPTION;
e_DiscountIDKenanErr EXCEPTION;
e_DiscountPercentKenanErr EXCEPTION;
e_PackageIDKenanErr EXCEPTION;
e_ChgNumErr EXCEPTION;
e_SiConfigErr EXCEPTION;
e_EmfConfigIDErr EXCEPTION;
e_DiscTypeErr EXCEPTION;
e_ContractTypesErr EXCEPTION;
e_DiscPlansErr EXCEPTION;
e_DiscDefineErr EXCEPTION;
e_RateDiscErr EXCEPTION;
e_UnitCrPlansErr EXCEPTION;
e_UnitCrDefineErr EXCEPTION;
e_RateUnitCrErr EXCEPTION;
e_SrcServErr EXCEPTION;
e_RateRcErr EXCEPTION;
e_CorridorPlanErr EXCEPTION;
e_ProdElementsErr EXCEPTION;
e_NumRateDiscountErr EXCEPTION;
e_CorridPlanGroupErr EXCEPTION;
e_StartRecErr EXCEPTION;
e_EndRecErr EXCEPTION;
e_PkgCompMembErr EXCEPTION;
e_ServNumErr EXCEPTION;
e_NrcIdErr EXCEPTION;
e_NrcTypeIdErr EXCEPTION;
pragma EXCEPTION_INIT(e_SiConfigErr, -20301);
PRAGMA EXCEPTION_INIT(e_ChgNumErr, -20302);
pragma exception_init(e_ErrUpperLimit, -20303);
pragma exception_init(e_CfgLovMap, -20304);
pragma exception_init(e_CfgKenMSA, -20305);
pragma exception_init(e_NoIdenNum, -20306);
pragma exception_init(e_NoVipNum, -20307);
pragma exception_init(e_NoParent, -20308);
pragma exception_init(e_CalStream, -20309);
pragma exception_init(e_InitLPS, -20310);
pragma exception_init(e_LPSData, -20311);
PRAGMA EXCEPTION_INIT(e_CompDefineErr, -20312);
PRAGMA EXCEPTION_INIT(e_PkgCompMembersErr, -20313);
PRAGMA EXCEPTION_INIT(e_PkgCompnentsErr, -20314);
PRAGMA EXCEPTION_INIT(e_AcctKenanErr, -20315);
PRAGMA EXCEPTION_INIT(e_ServIDKenanErr, -20316);
PRAGMA EXCEPTION_INIT(e_MemberIDKenanErr, -20318);
PRAGMA EXCEPTION_INIT(e_PlanIDKenanErr, -20319);
PRAGMA EXCEPTION_INIT(e_DiscountIDKenanErr, -20320);
PRAGMA EXCEPTION_INIT(e_DiscountPercentKenanErr, -20321);
PRAGMA EXCEPTION_INIT(e_PackageIDKenanErr, -20322);
PRAGMA EXCEPTION_INIT(e_DiscTypeErr, -20325);
PRAGMA EXCEPTION_INIT(e_FatalError, -20323);
PRAGMA EXCEPTION_INIT(e_EmfConfigIDErr, -20324);
PRAGMA EXCEPTION_INIT(e_ContractTypesErr, -20326);
PRAGMA EXCEPTION_INIT(e_DiscPlansErr, -20327);
PRAGMA EXCEPTION_INIT(e_DiscDefineErr, -20328);
PRAGMA EXCEPTION_INIT(e_RateDiscErr, -20329);
PRAGMA EXCEPTION_INIT(e_UnitCrPlansErr, -20330);
PRAGMA EXCEPTION_INIT(e_UnitCrDefineErr, -20331);
PRAGMA EXCEPTION_INIT(e_RateUnitCrErr, -20332);
PRAGMA EXCEPTION_INIT(e_SrcServErr, -20333);
PRAGMA EXCEPTION_INIT(e_RateRcErr, -20334);
PRAGMA EXCEPTION_INIT(e_CorridorPlanErr, -20335);
PRAGMA EXCEPTION_INIT(e_ProdElementsErr, -20336);
PRAGMA EXCEPTION_INIT(e_NumRateDiscountErr, -20337);
PRAGMA EXCEPTION_INIT(e_CorridPlanGroupErr, -20338);
PRAGMA EXCEPTION_INIT(e_StartRecErr, -20329);
PRAGMA EXCEPTION_INIT(e_EndRecErr, -20330);
pragma EXCEPTION_INIT(e_PkgCompMembErr, -20331);
pragma EXCEPTION_INIT(e_ServNumErr, -20332);
pragma EXCEPTION_INIT(e_NrcIdErr, -20340);
pragma EXCEPTION_INIT(e_NrcTypeIdErr, -20341);
-- Public function and procedure declarations
/*updated by Gu, Yingkun on May-12-2006*/
PROCEDURE PRC_INIT_LPS(p_Release varchar2,
p_Domain varchar2,
p_City varchar2,
p_MultiStream number,
p_StreamNo number);
/*added by Gu, Yingkun on May-12-2006*/
procedure PRC_UPD_LPS(p_ProcessName varchar2,
p_SucRec number,
p_FailRec number,
p_StreamStatus varchar2);
/*added by Gu, Yingkun on May-16-2006*/
procedure PRC_STREAM_INFO(p_ProcessName in varchar2,
o_TotalRec out number,
o_SucRec out number,
o_FailRec out number);
/*updated by Gu, Yingkun on Apr-18-2006*/
PROCEDURE PRC_HANDLE_EXCEPTION(p_ProName IN VARCHAR2, --Name of the process
p_Mode in varchar2,
p_rowid in src_cust.row_id%type);
/*added by Gu, Yingkun on Apr-21-2006*/
PROCEDURE PRC_CA_MSA(p_City in varchar2,
o_ServerID out number,
o_MIGSchema out varchar2);
/*added by Gu, Yingkun on Apr-26-2006*/
procedure PRC_PARENT_INFO(p_TableName in varchar2,
p_SrcID in varchar2,
p_OldParentID in varchar2,
o_KenParentID out varchar2,
o_ServerID out varchar2,
o_MigSchema out varchar2);
/*added by Gu, Yingkun on Apr-21-2005*/
PROCEDURE PRC_LOG_STATUS(p_TableName IN VARCHAR2,
p_ColumnName IN VARCHAR2,
p_OldID IN VARCHAR2,
p_KenanID IN VARCHAR2,
p_ServerID IN VARCHAR2,
p_ServerUser IN VARCHAR2,
p_IsProcessed IN VARCHAR2,
p_SrcID IN VARCHAR2,
p_ext_data_1 IN VARCHAR2,
p_ext_data_2 IN VARCHAR2,
p_ext_data_3 IN VARCHAR2,
p_ext_data_4 IN VARCHAR2,
p_ext_data_5 IN VARCHAR2);
/*Updated by Wan, Li on Jun-19-2006*/
FUNCTION FUN_LOGIC_EXCEPTION RETURN NUMBER;
/*Updated by Gu, Yingkun on Apr-18-2006*/
FUNCTION FUN_CFG_LOV_MAP(p_SrcID IN VARCHAR2, --SRC_ID
P_SrcType IN VARCHAR2, --SRC_TYPE
p_SrcCode IN VARCHAR2)
RETURN CFG_LOV_MAP.KEN_CODE%TYPE;
/*updated by Gu, Yingkun on May-12-2006*/
FUNCTION FUN_GET_START_ID(p_ProcessName in varchar2) RETURN NUMBER;
/*updated by Gu, Yingkun on May-12-2006*/
FUNCTION FUN_GET_END_ID(p_ProcessName in varchar2) RETURN NUMBER;
/*updated by Gu, Yingkun on Apr-17-2006*/
FUNCTION FUN_ENCODE(p_Source IN varchar2,
p_CITY IN varchar2,
domain in varchar2) RETURN varchar2;
/*Add by MaoXufei on Apr-26-2006*/
FUNCTION FUN_NAME2CODE(P_CITY IN varchar2) RETURN number;
/*updated by Gu, Yinkun on Jun-20-2006*/
FUNCTION FUN_ENCODE_SERVNUM(p_ServNum IN varchar2,
p_ServType IN number,
p_CITY IN varchar2) RETURN varchar2;
/*Add by Mao xufei Jul-12-2006*/
FUNCTION FUN_GET_CONTRACT_LEVEL(p_DiscountType in NUMBER) RETURN NUMBER;
/*Add by Mao xufei Jul-24-2006 */
FUNCTION FUN_ENCODE_SMALLNUM(p_ServNum IN varchar2,
p_ServNumType IN number,
p_CITY IN varchar2) RETURN varchar2;
END pkg_ken_util;
/
CREATE OR REPLACE PACKAGE BODY pkg_ken_util IS
-- Private type declarations
-- Private constant declarations
-- Private variable declarations
-- Private function and procedure
/*added by Gu, Yingkun on Apr-21-2006*/
FUNCTION FUN_CATADM_SCHEMA(p_category in varchar2) RETURN VARCHAR2 as
RESULT VARCHAR2(10);
begin
select mig_schema
into RESULT
from cfg_ken_msa
where mig_category = p_category;
RETURN RESULT;
exception
when no_data_found or too_many_rows then
raise_application_error(-20305,
'configuration of cfg_ken_msa for CAT/ADM is incorrect!');
end FUN_CATADM_SCHEMA;
/*added by Lu, xiaozhong on May-9-2006*/
FUNCTION FUN_CATADM_SID(p_category in varchar2) RETURN VARCHAR2 as
RESULT VARCHAR2(10);
begin
select mig_db
into RESULT
from cfg_ken_msa
where mig_category = p_category;
RETURN RESULT;
exception
when no_data_found or too_many_rows then
raise_application_error(-20305,
'configuration of cfg_ken_msa for CAT/ADM is incorrect!');
end FUN_CATADM_SID;
/*added by Gu, Yingkun on May-12-2006*/
procedure PRC_CAL_STREAM(p_City in varchar2,
p_MultiStream IN NUMBER,
p_StreamNo IN NUMBER,
p_TableName IN Varchar2,
o_StreamTotal out number,
o_StartRec out number,
o_EndRec out number) as
v_NumAvgFl NUMBER;
v_TotalRec NUMBER(10);
v_MaxRec number(10);
v_MinRec number(10);
v_RealTotal number(10);
BEGIN
execute immediate 'select MAX(ROW_ID) - min(row_id) + 1, max(row_id), min(row_id), count(1) from ' ||
p_TableName || ' Where src_id = :1 or src_id = :2'
into v_TotalRec, v_MaxRec, v_MinRec, v_RealTotal
using p_City || '97', p_City || 'Bl';
/* if v_TotalRec <> v_RealTotal then
raise e_CalStream;
end if;*/
--***row_id can be incontinuous,but for each city should not scatter
v_NumAvgFl := floor(v_TotalRec / p_MultiStream);
o_StartRec := v_NumAvgFl * (p_StreamNo - 1) + v_MinRec;
IF p_StreamNo <> p_MultiStream THEN
o_EndRec := v_NumAvgFl * p_StreamNo + v_MinRec - 1;
ELSE
o_EndRec := v_MaxRec;
END IF;
execute immediate 'select Count(1) from ' || p_TableName ||
' where row_id >=:1 and row_id <= :2 '
into o_StreamTotal
using o_StartRec, o_EndRec;
exception
when e_CalStream then
raise_application_error(-20309,
p_City || ' row_id in ' || p_TableName ||
'is not continuous!');
when others then
raise_application_error(-20309,
'Calculating stream meet unknown error!');
end PRC_CAL_STREAM;
-- Function and procedure implementations
PROCEDURE PRC_INIT_LPS(p_Release varchar2,
p_Domain varchar2,
p_City varchar2,
p_MultiStream number,
p_StreamNo number) as
l_lps log_process_status%rowtype;
l_TableName varchar2(20);
begin
select decode(p_Domain,
'CA',
'SRC_CUST',
'BA',
'SRC_ACCT',
'SI',
'SRC_SERV',
'PR',
'SRC_PROD',
'DIS',
'SRC_DISCOUNT',
'NRC',
'SRC_NRC')
into l_TableName
from dual;
l_lps.process_name := 'PRC_T_' || p_Domain || '_' || p_City || '_' ||
p_MultiStream || '_' || p_StreamNo;
PRC_CAL_STREAM(p_City,
p_MultiStream,
p_StreamNo,
l_TableName,
l_lps.total_rec,
l_lps.start_rec,
l_lps.end_rec);
l_lps.succeed_rec := 0;
l_lps.fail_rec := 0;
l_lps.start_date := sysdate;
l_lps.end_date := null;
l_lps.release := p_release;
l_lps.domain := p_domain;
l_lps.stream_no := p_StreamNo;
l_lps.stream_status := 'RD';
l_lps.multi_stream := p_MultiStream;
l_lps.city := p_City;
l_lps.t_status := 'TT';
delete from log_process_status where process_name = l_lps.process_name;
insert into log_process_status values l_lps;
commit;
exception
when e_CalStream then
rollback;
raise;
when others then
rollback;
raise_application_error(-20310,
'Initializing LOG_PROCESS_STATUS meets error!');
end PRC_INIT_LPS;
procedure PRC_UPD_LPS(p_ProcessName varchar2,
p_SucRec number,
p_FailRec number,
p_StreamStatus varchar2) as
begin
update log_process_status
set succeed_rec = p_SucRec,
fail_rec = p_FailRec,
end_date = sysdate,
stream_status = p_StreamStatus
where process_name = p_ProcessName;
end PRC_UPD_LPS;
procedure PRC_STREAM_INFO(p_ProcessName in varchar2,
o_TotalRec out number,
o_SucRec out number,
o_FailRec out number) as
begin
select a.total_rec, a.succeed_rec, a.fail_rec
into o_TotalRec, o_SucRec, o_FailRec
from log_process_status a
where process_name = p_ProcessName;
exception
when others then
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -