📄 pkg_ken_util.pck
字号:
raise_application_error(-20311,
'The data of this stream in LOG_PROCESS_STATUS is wrong!');
end PRC_STREAM_INFO;
PROCEDURE PRC_HANDLE_EXCEPTION(p_ProName IN VARCHAR2, --Name of the process
p_Mode in varchar2,
p_rowid in src_cust.row_id%type) AS
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(1000); --Information about the error
BEGIN
v_ErrorCode := SQLCODE;
v_ErrorMsg := SUBSTR(SQLERRM, 1, 1000);
v_CurrentUser := USER;
v_Information := 'Program was Terminated because Serious Error encoutered on ' ||
TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||
' by database user ' || v_CurrentUser;
ROLLBACK;
if p_Mode = 'N' then
update log_process_status
set stream_status = 'RT'
where process_name = p_ProName;
elsif p_Mode = 'F' then
update log_process_status
set stream_status = 'FT'
where process_name = p_ProName;
end if;
--insert the log message into log_table when senious error occur.
INSERT INTO LOG_PROCESS_ERROR
(PROCESS_NAME, ERROR_CODE, ERROR_MESSAGE, ERROR_INFO, row_id)
VALUES
(p_ProName, v_ErrorCode, v_ErrorMsg, v_Information, p_rowid);
COMMIT;
END PRC_HANDLE_EXCEPTION;
PROCEDURE PRC_CA_MSA(p_City in varchar2,
o_ServerID out number,
o_MIGSchema out varchar2) as
l_dbcount number;
begin
select count(1)
into l_dbcount
from cfg_ken_msa
where mig_category = p_City;
if l_dbcount = 1 then
select server_id, mig_schema
into o_ServerID, o_MIGSchema
from cfg_ken_msa
where mig_category = p_City;
elsif l_dbcount > 1 then
case p_City
when 'VIP' then
select ctzj_vip_seq.nextval into o_ServerID from dual;
when 'WZ' then
select ctzj_wz_seq.nextval into o_ServerID from dual;
when 'HZ' then
select ctzj_hz_seq.nextval into o_ServerID from dual;
when 'QZ' then
select ctzj_qz_seq.nextval into o_ServerID from dual;
when 'ZS' then
select ctzj_zs_seq.nextval into o_ServerID from dual;
when 'LS' then
select ctzj_ls_seq.nextval into o_ServerID from dual;
when 'NB' then
select ctzj_nb_seq.nextval into o_ServerID from dual;
/* when 'HH' then
select ctzj_hh_seq.nextval into o_ServerID from dual;*/
when 'JX' then
select ctzj_jx_seq.nextval into o_ServerID from dual;
when 'TZ' then
select ctzj_tz_seq.nextval into o_ServerID from dual;
when 'SX' then
select ctzj_sx_seq.nextval into o_ServerID from dual;
when 'JH' then
select ctzj_jh_seq.nextval into o_ServerID from dual;
end case;
select mig_schema
into o_MIGSchema
from cfg_ken_msa
where server_id = o_ServerID;
else
raise e_CfgKenMSA;
end if;
exception
when no_data_found or too_many_rows then
raise_application_error(-20305,
'configuration of cfg_ken_msa for CA is incorrect!');
end PRC_CA_MSA;
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) as
begin
execute immediate 'select kenan_id, server_id, server_user from ' ||
p_TableName ||
' where (src_id = :1 or src_id = :2) and old_id = :3 and is_processed = :4'
into o_KenParentID, o_ServerID, o_MigSchema
using substr(p_SrcID, 1, 2) || '97', substr(p_SrcID, 1, 2) || 'Bl', p_OldParentID, 'S';
exception
when no_data_found or too_many_rows then
raise_application_error(-20308,
'Parent of this object cannot be found or more than one!');
end PRC_PARENT_INFO;
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) as
v_ExistInErrCtr number;
v_ErrorCode NUMBER;
v_ErrorMsg VARCHAR2(2000);
begin
execute immediate 'select count(1) from ' || p_TableName ||
'_STATUS where src_id = :1 and old_id = :2'
into v_ExistInErrCtr
using p_SrcID, p_OldID;
-- NORMAL MODE
if v_ExistInErrCtr = 0 then
if p_IsProcessed = 'S' then
v_ErrorCode := null;
v_ErrorMsg := null;
elsif p_IsProcessed = 'F' then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SUBSTR(SQLERRM, 1, 1000);
end if;
EXECUTE IMMEDIATE 'INSERT INTO ' || p_TableName ||
'_STATUS VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)'
USING p_OldID, p_KenanID, p_ServerID, p_ServerUser, p_IsProcessed, v_ErrorCode, v_ErrorMsg, p_SrcID, p_ext_data_1, p_ext_data_2, p_ext_data_3, p_ext_data_4, p_ext_data_5;
execute immediate 'UPDATE ' || p_TableName ||
' SET is_processed = :1 where ' || p_ColumnName ||
' = :2 and src_id = :3'
using p_IsProcessed, p_OldID, p_SrcID;
-- ERROR MODE
elsif v_ExistInErrCtr = 1 then
if p_IsProcessed = 'S' then
-- keep previous error message
execute immediate 'update ' || p_TableName ||
'_STATUS set kenan_id = :1, server_id = :2, server_user = :3, is_processed = :4, ext_data_1 = :5, ext_data_2 = :6, ext_data_3 = :7, ext_data_4 = :8, ext_data_5 = :9' ||
' where src_id = :10 and old_id = :11'
using p_KenanID, p_ServerID, p_ServerUser, p_IsProcessed, p_ext_data_1, p_ext_data_2, p_ext_data_3, p_ext_data_4, p_ext_data_5, p_SrcID, p_OldID;
execute immediate 'UPDATE ' || p_TableName ||
' SET is_processed = :1 where ' || p_ColumnName ||
' = :2 and src_id = :3'
using p_IsProcessed, p_OldID, p_SrcID;
elsif p_IsProcessed = 'F' then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SUBSTR(SQLERRM, 1, 1000);
execute immediate 'update ' || p_TableName ||
'_STATUS set err_code = :1, err_msg = :2' ||
' where src_id = :3 and old_id = :4'
using v_ErrorCode, v_ErrorMsg, p_SrcID, p_OldID;
end if;
--else ERROR CENTER data is inconsistent
end if;
end PRC_LOG_STATUS;
/*added by Wan, Li on Jun-19-2006*/
FUNCTION FUN_LOGIC_EXCEPTION RETURN NUMBER as
begin
if sqlcode <= -20000 or sqlcode in (-6502) then
RETURN 1;
ELSE
RETURN 0;
end if;
end FUN_LOGIC_EXCEPTION;
FUNCTION FUN_CFG_LOV_MAP(p_SrcID IN VARCHAR2, --SRC_ID
p_SrcType IN VARCHAR2, --SRC_TYPE
p_SrcCode IN VARCHAR2) --SRC_CODE
RETURN CFG_LOV_MAP.KEN_CODE%TYPE AS
v_KenCode CFG_LOV_MAP.KEN_CODE%TYPE; --The ruturn kenan code
BEGIN
SELECT /*+ richs_secret_hint */
TRIM(KEN_CODE)
INTO v_KenCode
FROM CFG_LOV_MAP
WHERE SRC_ID = p_SrcID
AND SRC_TYPE = p_SrcType
AND SRC_CODE = p_SrcCode;
return v_KenCode;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20304,
p_SrcType || '#' || p_SrcCode || '#' ||
' hasn''t defined in CFG_LOV_MAP yet!');
WHEN TOO_MANY_ROWS THEN
raise_application_error(-20304,
p_SrcType || '#' || p_SrcCode || '#' ||
' has MORE THAN ONE defined in CFG_LOV_MAP yet!');
END FUN_CFG_LOV_MAP;
FUNCTION FUN_GET_START_ID(p_ProcessName in varchar2) RETURN NUMBER AS
l_StartRec number(10);
BEGIN
select start_rec
into l_StartRec
from log_process_status
where process_name = p_ProcessName;
RETURN l_StartRec;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20329,
'can not found start record in log_process_status!');
WHEN TOO_MANY_ROWS THEN
raise_application_error(-20329,
'get more than one start records in log_process_status!');
END FUN_GET_START_ID;
FUNCTION FUN_GET_END_ID(p_ProcessName in varchar2) RETURN NUMBER AS
l_EndRec number(10);
BEGIN
select end_rec
into l_EndRec
from log_process_status
where process_name = p_ProcessName;
RETURN l_EndRec;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20330,
'can not found end record in log_process_status!');
WHEN TOO_MANY_ROWS THEN
raise_application_error(-20330,
'get more than one end records in log_process_status!');
END FUN_GET_END_ID;
FUNCTION FUN_ENCODE(p_Source IN varchar2,
p_CITY IN varchar2,
domain varchar2) RETURN varchar2 IS
Result varchar2(50);
BEGIN
if domain = 'CA' then
result := '1' || pkg_ken_util.FUN_NAME2CODE(p_city) || '0' ||
p_Source;
elsif domain = 'BA' then
result := '2' || pkg_ken_util.FUN_NAME2CODE(p_city) || '0' ||
p_Source;
elsif domain = 'SI' then
result := pkg_ken_util.FUN_NAME2CODE(p_city) || '0' || p_Source;
--else raise error
end if;
RETURN(Result);
END FUN_ENCODE;
FUNCTION FUN_NAME2CODE(P_CITY IN varchar2) RETURN number IS
v_Code NUMBER(6);
BEGIN
case p_city
when 'HZ' then
v_Code := '71';
when 'HH' then
v_Code := '72';
when 'JX' then
v_Code := '73';
when 'NB' then
v_Code := '74';
when 'SX' then
v_Code := '75';
when 'TZ' then
v_Code := '76';
when 'WZ' then
v_Code := '77';
when 'LS' then
v_Code := '78';
when 'JH' then
v_Code := '79';
when 'QZ' then
v_Code := '70';
when 'ZS' then
v_Code := '80';
--else raise error;
end case;
RETURN(v_Code);
END FUN_NAME2CODE;
FUNCTION FUN_ENCODE_SERVNUM(p_ServNum IN varchar2,
p_ServType IN number,
p_CITY IN varchar2) RETURN varchar2 is
Result varchar2(50);
BEGIN
-- + area digit
if p_ServType in
(1001, 1002, 1004, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1017, 1018, 1019, 1020, 1022, 1023, 1024, 4001, 6003, 6004) then
Result := '5' || pkg_ken_util.FUN_NAME2CODE(p_CITY) || p_ServNum;
-- + area digit + 0
elsif p_ServType in
(1003, 1005, 2001, 2002, 2003, 2005, 2006, 2007, 2008, 3001, 3002, 3003, 4005, 5002, 5003, 5005, 5006, 5007, 7001, 7003, 7004, 7005, 7006, 7007, 7008, 7009, 7010, 7011, 7012, 7013, 7014, 7016) then
Result := '5' || pkg_ken_util.FUN_NAME2CODE(p_CITY) || '0' ||
p_ServNum;
-- non prefix
elsif p_ServType in
(1014, 1015, 1021, 2004, 4002, 4003, 4004, 5001, 5004, 6001, 6002, 7002, 7015, 8888) then
Result := p_ServNum;
-- raise error
else
raise_application_error(-20324,
'service number of emf_config_id(' ||
p_ServType || ') is not defined!');
end if;
RETURN(Result);
END FUN_ENCODE_SERVNUM;
FUNCTION FUN_GET_CONTRACT_LEVEL(p_DiscountType in NUMBER) RETURN NUMBER as
RESULT NUMBER(6);
begin
case p_DiscountType
when 2 then
RESULT := 1;
when 3 then
RESULT := 3;
when 4 then
RESULT := 5;
when 5 then
RESULT := 1;
else
raise_application_error(-20325,
'discount_type can not mapping contract level');
end case;
RETURN(RESULT);
end FUN_GET_CONTRACT_LEVEL;
FUNCTION FUN_ENCODE_SMALLNUM(p_ServNum IN varchar2,
p_ServNumType IN number,
p_CITY IN varchar2) RETURN varchar2 AS
Result varchar2(40);
BEGIN
IF p_ServNumType = '360' THEN
Result := pkg_ken_util.FUN_NAME2CODE(p_city) || '0' || p_ServNum;
elsif p_ServNumType in (10, 11, 300, 310, 331) then
Result := '5' || pkg_ken_util.FUN_NAME2CODE(p_CITY) || p_ServNum;
elsif p_ServNumType in (191) then
Result := '5' || pkg_ken_util.FUN_NAME2CODE(p_CITY) || '0' ||
p_ServNum;
elsif p_ServNumType in (190, 241, 351, 371, 391) then
Result := p_ServNum;
else
raise_application_error(-20332, 'Smallnum is not defined ');
END IF;
RETURN Result;
END FUN_ENCODE_SMALLNUM;
BEGIN
g_MIGCAT_Schema := FUN_CATADM_SCHEMA('CAT');
g_MIGADM_Schema := FUN_CATADM_SCHEMA('ADM');
g_MIGADM_Sid := FUN_CATADM_SID('ADM');
g_MIGCAT_Sid := FUN_CATADM_SID('CAT');
END pkg_ken_util;
/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -