📄 pkg_val_kpd.pck
字号:
create or replace package PKG_VAL_KPD is
-- Author : Wan, Li
-- Created : 6/27/2006 12:56:03 PM
-- Purpose : Validation src_prod_kenan, src_discount_kenan for kenan trnasformation
-- Public type declarations
-- type <TypeName> is <Datatype>;
type REFCURSOR is ref cursor;
type CLMTABLE IS table of varchar2(100) index by varchar2(1000);
type SSSTABLE IS table of varchar2(100) index by varchar2(1000);
type CSETABLE1 is table of vld_kpd_detail.row_id%type index by pls_integer;
type CSETABLE2 is table of vld_kpd_detail.src_id%type index by pls_integer;
type CSETABLE3 is table of vld_kpd_detail.primary_id%type index by pls_integer;
type CSETABLE4 is table of vld_kpd_detail.error_code%type index by pls_integer;
type CSETABLE5 is table of vld_kpd_detail.error_msg%type index by pls_integer;
-- Public constant declarations
-- <ConstantName> constant <Datatype> := <Value>;
C_BACK_DATE constant date := pkg_val_cbs.G_BACK_DATE;
C_CUTOFF_DATE constant date := pkg_val_cbs.G_CUTOFF_DATE;
C_ErrMsg_PR01 constant varchar2(1000) := 'PR01: can not find parent SI
找不到SI';
C_ErrMsg_PR02 constant varchar2(1000) := 'PR02: if kenan_product_id <> 81803, product rc_rate must be not null';
C_ErrMsg_PR03 constant varchar2(1000) := 'PR03: if SI equip_type is in cfg_ken_uu, units and units_type must be mapped
如果该设备是按批价进行计费的，那么units和units_type必须在配置表中找的到';
C_ErrMsg_PR04 constant varchar2(1000) := 'PR04: 同一用户下不可以有相互冲突的程控业务';
C_ErrMsg_PR05 constant varchar2(1000) := 'PR05: SI cannot have suspended RC, if it is active
如果SI是在用的，那么下面不能挂停机保号月租,
只有括号内的这几种设备才可以挂停机保号月租( 1001 , 1002 , 1004 , 1012 , 1018 , 4002 , 4003 , 4004 , 6003 , 6004 )';
C_ErrMsg_PR06 constant varchar2(1000) := 'PR06: SI must have RC(132/40004/40005) if SI is suspended
如果SI停机，那么必须挂停机保号的月租';
C_ErrMsg_PR07 constant varchar2(1000) := 'PR07: product start_dt cannot be null
产品的开始时间不能为空';
C_ErrMsg_PR08 constant varchar2(1000) := 'PR08: START_DT cannot be earlier than start_dt of its parent SI
产品的开始时间不能早于资产的开始时间';
C_ErrMsg_PR09 constant varchar2(1000) := 'PR09: START_DT (123/40004/40005) must be equal with CR_SUS_DT, if its parent SI is suspended
如果SI资产已经主动停机，那么停机保号产品的开始时间必须等于资产停机的时间';
C_ErrMsg_PR10 constant varchar2(1000) := 'PR10: END_DT cannot be earlier than START_DT
产品的结束时间不能早于产品开始时间';
C_ErrMsg_PR11 constant varchar2(1000) := 'PR11: END_DT cannot be null and must be earlier than end_dt of SI or be equal with its START_DT, if its parent SI is disconected
如果SI资产已经拆机，那么产品的结束时间不能为空并且必须早于资产的结束时间或者产品开始时间＝产品结束时间';
C_ErrMsg_PR12 constant varchar2(1000) := 'PR12: BILLED_THRU_DATE cannot be earlier than START_DT
上一次出帐的日期不能早于产品的开始时间';
C_ErrMsg_PR13 constant varchar2(1000) := 'PR13: BILLED_THRU_DATE cannot be later than END_DT
上一次出帐日期不能晚于产品的结束时间，也就是说产品都已经结束就不能再出帐了';
C_ErrMsg_PR14 constant varchar2(1000) := 'PR14: BILLED_THTU_DATE cannot be null or > CR_SUS_DT if START_DT <= LEAST(G_CUTOFF_DATE, CR_SUS_DT), when BILL_WHILE_SUSPEND = 0:
当停机期间不收钱： 如果产品开始小与（割接日期和停机时间）中较小的一个，那么上一次出帐日期不能为空并且不能晚于停机时间';
C_ErrMsg_PR15 constant varchar2(1000) := 'PR15: BILLED_THRU_DATE must be null if its START_DT > LEAST(G_CUTOFF_DATE, CR_SUS_DT), when BILL_WHILE_SUSPEND = 0
当停机不收钱：如果产品开始大于（割接时间，停机时间）较小的一个，那么上一次出帐日期必须为空';
C_ErrMsg_PR16 constant varchar2(1000) := 'PR16: BILLED_THTU_DATE cannot be null if START_DT < G_CUTOFF_DATE, when BILL_WHILE_SUSPEND = 0 and SI is not suspended
当停机期间不收钱并且资产是在用的：如果产品的开始时间早于割接日期，那么上一次出帐的日期不能为空';
C_ErrMsg_PR17 constant varchar2(1000) := 'PR17: BILLED_THRU_DATE must be null if its START_DT >= G_CUTOFF_DATE, when BILL_WHILE_SUSPEND = 0 and SI is not suspended
当停机期间不收钱并且资产是在用的：如果产品的开始时间晚于割接日期，那么上一次出帐日期必须为空';
C_ErrMsg_PR18 constant varchar2(1000) := 'PR18: BILLED_THTU_DATE cannot be null if START_DT < G_CUTOFF_DATE, when BILL_WHILE_SUSPEND = 1
当停机期间要收钱：如果产品的开始时间小于割接日期，那么上一次出帐日期不能为空';
C_ErrMsg_PR19 constant varchar2(1000) := 'PR19: BILLED_THRU_DATE must be null if its START_DT >= G_CUTOFF_DATE, when BILL_WHILE_SUSPEND = 1
当停机期间要收钱：果产品的开始时间大于割接日期，那么上一次出帐日期必须为空';
C_ErrMsg_PR20 constant varchar2(1000) := 'PR20: START_DT must be equal with its parnt SI START_DT, if it is a suspend rc
如果SI停机，那么产品的开始时间必须等于SI停机时间';
C_ErrMsg_PR21 constant varchar2(1000) := 'PR21: monthly fee for 800 must pertain to 800 device
800月租必须挂在800设备';
C_ErrMsg_PR22 constant varchar2(1000) := 'PR22: montyly fee for 11800 must pertain to 11800 device
11800设备必须挂11800月租';
C_ErrMsg_PR23 constant varchar2(1000) := 'PR23: COMPONENT_ID must be defined in CFG_KEN_CAT';
C_ErrMsg_PR24 constant varchar2(1000) := 'PR24: 这种设备必须要有guding RC';
C_ErrMsg_PR25 constant varchar2(1000) := 'PR25: 这种设备超过1个guding RC';
C_ErrMsg_PR26 constant varchar2(1000) := 'PR26: 有2个或2个以上的guding RC，时间不能overlap';
C_ErrMsg_PR27 constant varchar2(1000) := 'PR27: 如果产品的is_overriden=0，其资费不能为空';
C_ErrMsg_PR28 constant varchar2(1000) := 'PR28: 该产品不应该挂在该设备下';
C_ErrMsg_PR29 constant varchar2(1000) := 'PR29: 产品的component_id不能为空';
C_ErrMsg_PR30 constant varchar2(1000) := 'PR30: 宽带类产品的如果停机，那么必须要有停机保号费（40004或40005）';
C_ErrMsg_PR31 constant varchar2(1000) := 'PR31: 9种子产品的和设备类型互斥';
C_ErrMsg_PR32 constant varchar2(1000) := 'PR32: 宽带Dummy产品的不允许做覆盖';
C_ErrMsg_PR33 constant varchar2(1000) := 'PR33: 停机保号费产品的不允许做覆盖';
C_ErrMsg_PR34 constant varchar2(1000) := 'PR34: 宽带设备只能挂宽带类的停机保号产品';
C_ErrMsg_PR35 constant varchar2(1000) := 'PR35: 固化设备只能挂固化类的停机保号产品';
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C_ErrMsg_DC01 constant varchar2(1000) := 'DC01: DISCOUNT_TYPE must be in (2,3,4,5) except for corridor
除了corridor以外的所有优惠类型必须是定义在（2,3,4,5）中';
C_ErrMsg_DC02 constant varchar2(1000) := 'DC02: SERV_ID must exist in SRC_SERV if DISCOUNT_TYPE = 3
如果是SI级别的优惠类型，那么serv_id必须存在src_serv表中';
C_ErrMsg_DC03 constant varchar2(1000) := 'DC03: ACCT_NBR_97 must exist in SRC_ACCT if its DISCOUNT_TYPE in (2,4,5)
如果是BA级别的优惠类型，那么acct_nbr_97必须存在src_acct表中';
C_ErrMsg_DC04 constant varchar2(1000) := 'DC04: GROUP_NO cannot be null when DISCOUNT_TYPE in (4, 5)';
C_ErrMsg_DC05 constant varchar2(1000) := 'DC05: DISCT_METHOD must be in (1,2,3,4,5,6)
优惠的方法必须定义于(1,2,3,4,5,6)中';
C_ErrMsg_DC06 constant varchar2(1000) := 'DC06: X/Y/Z/W/V/X6/X7 cannot be null according to its DISCT_METHOD';
C_ErrMsg_DC07 constant varchar2(1000) := 'DC07: PAY X GET Y discount for one SI/BA cannot be more than 1 in same duration';
C_ErrMsg_DC08 constant varchar2(1000) := 'DC08: START_DT cannot be null
优惠的开始时间不能为空';
C_ErrMsg_DC09 constant varchar2(1000) := 'DC09: START_DT must be trunced without time info';
C_ErrMsg_DC10 constant varchar2(1000) := 'DC10: START_DT cannot be earlier than START_DT of SI if its DISCOUNT_TYPE = 3
如果是SI级别的优惠，那么优惠的开始时间不能早于SI资产的时间';
C_ErrMsg_DC11 constant varchar2(1000) := 'DC11: START_DT cannot be earlier than ACCT_CREATED_DATE of BA if its DISCOUNT_TYPE = 2,4,5
如果是BA级别的优惠，那么优惠的开始时间不能早于BA的创建日期';
C_ErrMsg_DC12 constant varchar2(1000) := 'DC12: END_DT cannot be earlier than START_DT
优惠的结束时间不能早于优惠开始时间';
C_ErrMsg_DC13 constant varchar2(1000) := 'DC13: END_DT cannot be null and must be earlier than end_dt of SI or be equal with its START_DT, when DISCOUNT_TYPE is 3 and its parent SI is disconected
如果是SI级别的优惠并且SI资产已经拆机，那么优惠的结束时间不能为空并且必须早于SI资产的结束时间，或者优惠的开始时间＝优惠结束时间';
C_ErrMsg_DC14 constant varchar2(1000) := 'DC14: COMPONENT_ID must be defined in CFG_KEN_CAT';
C_ErrMsg_DC15 constant varchar2(1000) := 'DC15: all paying_sbu_account in src_discount must also exist in src_acct';
C_ErrMsg_DC16 constant varchar2(1000) := 'DC16: 一个用户下面只能挂一个corridor';
C_ErrMsg_DC17 constant varchar2(1000) := 'DC17: 打折百分比不能大于100';
C_ErrMsg_DC31 constant varchar2(1000) := 'DC31: 同一个SI用户下的亲情网优惠不能有冲突';
C_ErrMsg_DC32 constant varchar2(1000) := 'DC32: SI欠费双停必须要求欠停包';
C_ErrMsg_DC33 constant varchar2(1000) := 'DC33: SI欠费单停不能有欠停包';
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C_ErrMsg_GDC01 constant varchar2(1000) := 'GDC01: record in src_discount_kenan, not in src_contract_assignment_hq';
C_ErrMsg_GDC02 constant varchar2(1000) := 'GDC02: record in src_contract_assignment_hq, not in src_discount_kenan';
C_ErrMsg_GDC03 constant varchar2(1000) := 'GDC03: record in src_cah_ext_data, not in src_contract_assignment_hq';
C_ErrMsg_GDC04 constant varchar2(1000) := 'GDC04: record in src_contract_assignment_hq, not in src_cah_ext_data';
C_ErrMsg_GDC05 constant varchar2(1000) := 'GDC05: 一个群组号不能有重复的优惠类型';
C_ErrMsg_GDC06 constant varchar2(1000) := 'GDC06: (src_cah_ext_data.service_start_dt >= src_contract_assignment_hq.start_dt, src_cah_ext_data.service_end_dt <= src_contract_assignment_hq.end_dt) must be true';
C_ErrMsg_GDC07 constant varchar2(1000) := 'GDC07: (src_contract_assignment_hq.start_dt >= src_discount_kenan.start_dt, src_contract_assignment_hq.end_dt <= src_discount_kenan.end_dt) must be true';
C_ErrMsg_GDC08 constant varchar2(1000) := 'GDC08: (src_contract_assignment_hq.start_dt >= src_acct.acct_created_dt) must be true';
C_ErrMSg_GDC09 constant varchar2(1000) := 'GDC09: (src_cah_ext_data.service_start_dt >= src_serv.start_dt, src_cah_ext_data.service_end_dt <= src_serv.end_dt) must be true';
C_ErrMSg_GDC10 constant varchar2(1000) := 'GDC10: (src_cah_ext_data.acct_nbr_97 <> src_serv.acct_nbr_97) 群组优惠对应的BA与三户对应的BA不一致';
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C_ErrMsg_NRC01 constant varchar2(1000) := 'NRC01: 找不到SI服务实例';
C_ErrMsg_NRC02 constant varchar2(1000) := 'NRC02: NRC不是当月的';
C_ErrMsg_NRC03 constant varchar2(1000) := 'NRC03: 账目类型不匹配';
C_ErrMsg_NRC04 constant varchar2(1000) := 'NRC04: NRC 的钱不能为负';
C_ErrMsg_NRC05 constant varchar2(1000) := 'NRC05: NRC的生效时间必须落在SI之间';
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Public variable declarations
t_KCLM CLMTABLE;
t_CKC CLMTABLE;
t_CAB CLMTABLE;
t_CU CLMTABLE;
t_CUT CLMTABLE;
-- Public function and procedure declarations
-- function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
procedure prc_preval_pr(p_Seq number, p_Mode number);
procedure prc_val_pr(p_Seq number,
p_Mode number,
p_TotalStream number,
p_StreamNo number);
procedure prc_posval_pr(p_Seq number, p_Mode number);
procedure prc_preval_dc(p_Seq number, p_Mode number);
procedure prc_val_dc(p_Seq number,
p_Mode number,
p_TotalStream number,
p_StreamNo number);
procedure prc_posval_dc(p_Seq number, p_Mode number);
procedure prc_val_grp_dc(p_Seq number, p_Mode number);
procedure prc_preval_nrc(p_Seq number, p_Mode number);
procedure prc_val_nrc(p_Seq number,
p_Mode number,
p_TotalStream number,
p_StreamNo number);
procedure prc_posval_nrc(p_Seq number, p_Mode number);
end PKG_VAL_KPD;
/
create or replace package body PKG_VAL_KPD is
-- Private type declarations
-- type <TypeName> is <Datatype>;
-- Private constant declarations
-- <ConstantName> constant <Datatype> := <Value>;
-- Private variable declarations
-- <VariableName> <Datatype>;
-- Function and procedure implementations
/***************************************************************************/
procedure prc_preval_pr(p_Seq number, p_Mode number) is
/***************************************************************************/
-- Author : Wan, Li
-- Created : 6/27/2006 12:56:03 PM
-- Purpose : update row_id for src_prod
/***************************************************************************/
l_ProName varchar2(20) := 'PRC_PREVAL_PR';
l_ErrMsg varchar2(100);
l_NullRowID number(10);
l_MaxRowID number(10);
begin
select count(1)
into l_NullRowID
from src_prod_kenan
where row_id is null;
if l_NullRowID > 0 then
insert into vld_log
values
(l_ProName, p_Seq, p_Mode, sysdate, null, null, null, null, null);
commit;
/*end initialize check_log*/
select nvl(max(row_id), 0) into l_MaxRowID from src_prod_kenan;
update src_prod_kenan
set row_id = l_MaxRowID + rownum, is_processed = 'E'
where row_id is null;
update vld_log
set end_time = sysdate, exec_msg = 'Completed'
where procedure_name = l_ProName
and batch_seq = p_Seq
and vld_mode = p_Mode;
commit;
end if;
exception
when others then
rollback;
update vld_log
set exec_msg = l_ErrMsg
where procedure_name = l_ProName
and batch_seq = p_Seq
and vld_mode = p_Mode;
commit;
end prc_preval_pr;
procedure prc_val_pr(p_Seq number,
p_Mode number,
p_TotalStream number,
p_StreamNo number) is
/***************************************************************************/
-- Author : Wan, Li
-- Created : 6/27/2006 12:56:03 PM
-- Purpose : Validation for src_prod_kenan
/***************************************************************************/
type LOOPREC is record(
serv_id src_prod_kenan.serv_id%type,
src_id src_prod_kenan.src_id%type,
kenan_product_id src_prod_kenan.kenan_product_id%type,
prod_start_dt src_prod_kenan.start_dt%type,
prod_end_dt src_prod_kenan.end_dt%type,
billed_thru_dt src_prod_kenan.billed_thru_date%type,
units src_prod_kenan.units%type,
units_type src_prod_kenan.units_type%type,
rc_rate src_prod_kenan.rc_rate%type,
row_id src_prod_kenan.row_id%type,
si_src_id src_serv.src_id%type,
si_serv_id src_serv.serv_id%type,
status src_serv.status%type,
serv_area src_serv.serv_area%type,
si_start_dt src_serv.start_dt%type,
si_end_dt src_serv.end_dt%type,
equip_type src_serv.equip_type%type,
suspend_initiative src_serv.suspend_initiative%type,
cr_sus_dt src_serv.cr_sus_dt%type,
is_processed src_serv.is_processed%type,
is_overriden src_prod_kenan.is_overriden%type);
c_Pr REFCURSOR;
rec_Pr LOOPREC;
t_SI SSSTABLE;
t_CSE1 CSETABLE1;
t_CSE2 CSETABLE2;
t_CSE3 CSETABLE3;
t_CSE4 CSETABLE4;
t_CSE5 CSETABLE5;
l_TblName varchar2(20) := 'SRC_PROD_KENAN';
l_ProName varchar2(20) := 'PRC_VAL_PR_' || to_char(p_TotalStream) || '_' ||
to_char(p_StreamNo);
l_StartRec number(10);
l_EndRec number(10);
l_ErrMsg varchar2(100);
l_TotalNum number(10) := 0;
l_SuccessNum number(10) := 0;
l_ErrorNum number(10) := 0;
l_SubQuery varchar2(2000);
l_Query varchar2(3000);
l_Count number(10) := 1;
l_Count1 number(10) := 0;
l_CountEmf number(10) := 0;
l_CountEmf0 number(10) := 0;
l_CountEmf1 number(10) := 0;
l_CountEmf2 number(10) := 0;
l_StartDt date;
--l_flag number(10) := 0;
begin
/*************************************************************************/
/*initialize check_log*/
select max(row_id) into l_EndRec from src_prod_kenan;
l_StartRec := floor(l_EndRec * (p_StreamNo - 1) / p_TotalStream);
l_EndRec := floor(l_EndRec * p_StreamNo / p_TotalStream);
select count(1)
into l_TotalNum
from src_prod_kenan
where row_id > l_StartRec
and row_id <= l_EndRec;
insert into vld_log
values
(l_ProName,
p_Seq,
p_Mode,
sysdate,
null,
l_TotalNum,
null,
null,
null);
commit;
t_SI.delete;
t_CSE1.delete;
t_CSE2.delete;
t_CSE3.delete;
t_CSE4.delete;
t_CSE5.delete;
/*end initialize check_log*/
l_SubQuery := 'select sp.serv_id, sp.src_id, sp.kenan_product_id, sp.start_dt, sp.end_dt, sp.billed_thru_date,' ||
'sp.units, sp.units_type, sp.rc_rate, sp.row_id, ss.src_id, ss.serv_id,' ||
'ss.status, ss.serv_area, ss.start_dt, ss.end_dt, ss.equip_type, ss.suspend_initiative, ss.cr_sus_dt, ss.is_processed , sp.is_overriden ';
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -