📄 pkg_val_kpd.pck
字号:
into l_CountPr35
from src_prod_kenan
where serv_id = rec_Pr.serv_id
and substr(src_id, 1, 2) = substr(rec_Pr.src_id, 1, 2)
and kenan_product_id in ('40004', '40005'); -- '40004', '40005'
if l_CountPr35 > 0 then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR35';
t_CSE5(l_Count) := C_ErrMsg_PR35;
l_Count := l_Count + 1;
end if;
end;
end if;
--PR30
if rec_Pr.suspend_initiative = 'Y' and rec_Pr.status = 'A' and
rec_Pr.equip_type in ('4002', '4003', '4004') then
declare
l_CountPr30 number(10);
begin
select /*+index(SRC_PROD_KENAN IT_SP_SS)*/
count(1)
into l_CountPr30
from src_prod_kenan
where serv_id = rec_Pr.serv_id
and substr(src_id, 1, 2) = substr(rec_Pr.src_id, 1, 2)
and kenan_product_id in ('40004', '40005'); --
if l_CountPr30 = 0 then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR30';
t_CSE5(l_Count) := C_ErrMsg_PR30;
l_Count := l_Count + 1;
end if;
end;
end if;
if rec_Pr.suspend_initiative = 'Y' and rec_Pr.status = 'A' and
rec_Pr.equip_type in ('4002', '4003', '4004') then
declare
l_CountPr34 number(10);
begin
select /*+index(SRC_PROD_KENAN IT_SP_SS)*/
count(1)
into l_CountPr34
from src_prod_kenan
where serv_id = rec_Pr.serv_id
and substr(src_id, 1, 2) = substr(rec_Pr.src_id, 1, 2)
and kenan_product_id in ('132'); --
if l_CountPr34 > 0 then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR34';
t_CSE5(l_Count) := C_ErrMsg_PR34;
l_Count := l_Count + 1;
end if;
end;
end if;
-- 21
if rec_Pr.kenan_product_id = '131' and rec_Pr.equip_type <> '1014' then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR21';
t_CSE5(l_Count) := C_ErrMsg_PR21;
l_Count := l_Count + 1;
-- 22
elsif rec_Pr.kenan_product_id = '36' and rec_Pr.equip_type <> '1021' then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR22';
t_CSE5(l_Count) := C_ErrMsg_PR22;
l_Count := l_Count + 1;
end if;
if rec_Pr.kenan_product_id in (42, 43, 44, 48, 57, 58, 59, 62, 63) then
declare
l_Count31 number(10) := 0;
begin
select count(*)
into l_Count31
from sub_prod_from_siebel
where sub_comp = rec_Pr.kenan_product_id
and equip_type = rec_Pr.equip_type;
if l_Count31 = 0 then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR31';
t_CSE5(l_Count) := C_ErrMsg_PR31;
l_Count := l_Count + 1;
end if;
end;
end if;
-- 23
begin
if t_CKC(to_char(rec_Pr.kenan_product_id) || '1') = '65' then
null;
end if;
exception
when no_data_found then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR23';
t_CSE5(l_Count) := C_ErrMsg_PR23;
l_Count := l_Count + 1;
end;
-- TIME CHECK
-- 7.
if rec_Pr.prod_start_dt is null then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR07';
t_CSE5(l_Count) := C_ErrMsg_PR07;
l_Count := l_Count + 1;
end if;
-- 8.
if rec_Pr.prod_start_dt < rec_Pr.si_start_dt then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR08';
t_CSE5(l_Count) := C_ErrMsg_PR08;
l_Count := l_Count + 1;
end if;
-- 9.
if rec_Pr.kenan_product_id in ('132', '40004', '40005') and
rec_Pr.suspend_initiative = 'Y' and rec_Pr.status = 'A' and
trunc(rec_Pr.prod_start_dt) < trunc(rec_Pr.cr_sus_dt) then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR09';
t_CSE5(l_Count) := C_ErrMsg_PR09;
l_Count := l_Count + 1;
end if;
-- 10.
if rec_Pr.prod_end_dt < rec_Pr.prod_start_dt then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR10';
t_CSE5(l_Count) := C_ErrMsg_PR10;
l_Count := l_Count + 1;
end if;
-- 11.
if rec_Pr.status = 'D' and
(rec_Pr.prod_end_dt is null or
(rec_Pr.prod_end_dt > rec_Pr.si_end_dt and
rec_Pr.prod_end_dt <> rec_Pr.prod_start_dt)) then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR11';
t_CSE5(l_Count) := C_ErrMsg_PR11;
l_Count := l_Count + 1;
end if;
-- 12:
if rec_Pr.billed_thru_dt < rec_Pr.prod_start_dt then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR12';
t_CSE5(l_Count) := C_ErrMsg_PR12;
l_Count := l_Count + 1;
end if;
-- 13:
if rec_Pr.billed_thru_dt > rec_Pr.prod_end_dt then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR13';
t_CSE5(l_Count) := C_ErrMsg_PR13;
l_Count := l_Count + 1;
end if;
-- 14&15&16&17&18&19
begin
if t_CAB(rec_Pr.kenan_product_id) = 0 then
if rec_Pr.suspend_initiative = 'Y' and rec_Pr.status = 'A' then
if rec_Pr.prod_start_dt <
least(nvl(rec_Pr.cr_sus_dt,
to_date('3000-01-01', 'yyyy-mm-dd')),
C_CUTOFF_DATE) and
(rec_Pr.billed_thru_dt is null or
rec_Pr.billed_thru_dt >
nvl(rec_Pr.cr_sus_dt, to_date('3000-01-01', 'yyyy-mm-dd'))) then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR14';
t_CSE5(l_Count) := C_ErrMsg_PR14;
l_Count := l_Count + 1;
elsif rec_Pr.prod_start_dt >=
least(nvl(rec_Pr.cr_sus_dt,
to_date('3000-01-01', 'yyyy-mm-dd')),
C_CUTOFF_DATE) and
rec_Pr.billed_thru_dt is not null then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR15';
t_CSE5(l_Count) := C_ErrMsg_PR15;
l_Count := l_Count + 1;
end if;
elsif rec_Pr.suspend_initiative = 'N' AND rec_Pr.status = 'A' then
if rec_Pr.prod_start_dt < C_CUTOFF_DATE and
nvl(rec_Pr.prod_end_dt, to_date('3000-01-01', 'yyyy-mm-dd')) >
rec_Pr.prod_start_dt and rec_Pr.billed_thru_dt is null then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR16';
t_CSE5(l_Count) := C_ErrMsg_PR16;
l_Count := l_Count + 1;
elsif rec_Pr.prod_start_dt >= C_CUTOFF_DATE and
rec_Pr.billed_thru_dt is not null then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR17';
t_CSE5(l_Count) := C_ErrMsg_PR17;
l_Count := l_Count + 1;
end if;
end if;
elsif t_CAB(rec_Pr.kenan_product_id) = 1 then
if rec_Pr.prod_start_dt < C_CUTOFF_DATE and
rec_Pr.billed_thru_dt is null then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR18';
t_CSE5(l_Count) := C_ErrMsg_PR18;
l_Count := l_Count + 1;
elsif rec_Pr.prod_start_dt >= C_CUTOFF_DATE and
rec_Pr.billed_thru_dt is not null then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR19';
t_CSE5(l_Count) := C_ErrMsg_PR19;
l_Count := l_Count + 1;
end if;
end if;
exception
when no_data_found then
null;
end;
-- PR20:
if rec_Pr.kenan_product_id in ('132', '40004', '40005') and
trunc(rec_Pr.prod_start_dt) < trunc(rec_Pr.cr_sus_dt) then
t_CSE1(l_Count) := rec_Pr.row_id;
t_CSE2(l_Count) := rec_Pr.src_id;
t_CSE3(l_Count) := rec_Pr.serv_id;
t_CSE4(l_Count) := 'PR20';
t_CSE5(l_Count) := C_ErrMsg_PR20;
l_Count := l_Count + 1;
end if;
/*INSERT INTO VLD_CBS_DETAIL*/
if l_Count > 1000 then
forall i in t_CSE1.first .. t_CSE1.last
insert into vld_kpd_detail
values
(l_TblName,
p_Seq,
p_Mode,
t_CSE1(i),
t_CSE2(i),
t_CSE3(i),
t_CSE4(i),
t_CSE5(i),
null);
commit;
l_Count := 1;
t_CSE1.delete;
t_CSE2.delete;
t_CSE3.delete;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -