📄 pkg_val_kpd.pck
字号:
if p_Mode = 1 then
-- TABLE ACCESS FULL
l_Query := l_SubQuery ||
'from src_prod_kenan sp
left outer join
src_serv ss
on substr(sp.src_id, 1,2) = substr(ss.src_id, 1,2)
and sp.serv_id = ss.serv_id
and ss.is_processed =''N''
where (sp.row_id > :1 and sp.row_id <= :2) ';
else
-- CHECK ERROR RECORDS
l_Query := l_SubQuery ||
'from src_prod_kenan sp,
left outer join
src_serv ss
on substr(sp.src_id, 1,2) = substr(ss.src_id, 1,2)
and sp.serv_id = ss.serv_id
and ss.is_processed =''N''
where (sp.row_id > :1 and sp.row_id <= :2) and sp.is_processed = ''E''';
end if;
open c_Pr for l_Query
using l_StartRec, l_EndRec;
<<NEXTLOOP01555>>
loop
fetch c_Pr
into rec_Pr;
exit when c_Pr%notfound;
-- OPERATION LOGIC
-- PR011.
if rec_Pr.si_serv_id is null and rec_Pr.is_processed 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) := 'PR01';
t_CSE5(l_Count) := C_ErrMsg_PR01;
l_Count := l_Count + 1;
GOTO NEXTLOOP01555;
end if;
if rec_Pr.kenan_product_id is null or rec_Pr.kenan_product_id < 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) := 'PR29';
t_CSE5(l_Count) := C_ErrMsg_PR29;
l_Count := l_Count + 1;
end if;
if rec_Pr.kenan_product_id in
(40260, 40261, 40262, 40263, 40264, 40265, 40266, 40267, 40268,
40269, 40270, 40271) and rec_Pr.is_overriden = 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) := 'PR32';
t_CSE5(l_Count) := C_ErrMsg_PR32;
l_Count := l_Count + 1;
end if;
if rec_Pr.kenan_product_id in ('132', '40004', '40005') and
rec_Pr.is_overriden = 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) := 'PR33';
t_CSE5(l_Count) := C_ErrMsg_PR33;
l_Count := l_Count + 1;
end if;
-- 24: 25: 26 for guding RC
BEGIN
select count(*)
into l_CountEmf0
from cfg_ken_gui
where emf_config_id = rec_Pr.equip_type;
if l_CountEmf0 > 0 then
select /*+index(SRC_PROD_KENAN IT_SP_SS)*/
count(1)
into l_CountEmf1
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
(select component_id
from cfg_ken_gui
where emf_config_id <> rec_Pr.equip_type);
if l_CountEmf1 > 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) := 'PR28';
t_CSE5(l_Count) := C_ErrMsg_PR28;
l_Count := l_Count + 1;
end if;
select /*+index(SRC_PROD_KENAN IT_SP_SS)*/
count(1)
into l_CountEmf
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
(select component_id
from cfg_ken_gui
where emf_config_id = rec_Pr.equip_type);
if l_CountEmf = 0 then
--No guiding RC
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) := 'PR24';
t_CSE5(l_Count) := C_ErrMsg_PR24;
l_Count := l_Count + 1;
elsif l_CountEmf > 1 then
---more than one
select /*+index(SRC_PROD_KENAN IT_SP_SS)*/
count(1)
into l_CountEmf2
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 end_dt is null
and kenan_product_id in
(select component_id
from cfg_ken_gui
where emf_config_id = rec_Pr.equip_type);
if l_CountEmf2 > 1 then
--more than one guiding RC
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) := 'PR25';
t_CSE5(l_Count) := C_ErrMsg_PR25;
l_Count := l_Count + 1;
else
--check overlap
declare
begin
for rec_gui in (select serv_id,
start_dt,
end_dt,
src_id,
row_id,
kenan_product_id
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
(select component_id
from cfg_ken_gui
where emf_config_id =
rec_Pr.equip_type)
order by start_dt) loop
if l_CountEmf > 1 then
l_CountEmf := l_CountEmf - 1;
select min(start_dt)
into l_StartDt
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
(select component_id
from cfg_ken_gui
where emf_config_id = rec_Pr.equip_type)
and start_dt > rec_gui.start_dt;
if (rec_gui.end_dt is not null and
l_StartDt < rec_gui.end_dt) or
rec_gui.end_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) := 'PR26';
t_CSE5(l_Count) := C_ErrMsg_PR26;
l_Count := l_Count + 1;
end if;
end if;
end loop;
end;
end if;
end if;
end if;
END;
-- 27:
if rec_Pr.is_overriden = 0 and rec_Pr.rc_rate 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) := 'PR27';
t_CSE5(l_Count) := C_ErrMsg_PR27;
l_Count := l_Count + 1;
end if;
-- 2.
/*if rec_Pr.kenan_product_id <> 81803 and
rec_Pr.rc_rate 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) := 'PR02';
t_CSE3(l_Count) := C_ErrMsg_PR02;
l_Count := l_Count + 1;
end if;*/
-- 3.
declare
l_UnitsCount number(10);
begin
if t_CU(rec_Pr.kenan_product_id) is not null then
select count(1)
into l_UnitsCount
from cfg_ken_uau
where component_id = to_number(rec_Pr.kenan_product_id)
and rate_class in
(to_number(t_KCLM(rec_Pr.si_src_id || 'SERV_AREA' ||
rec_Pr.serv_area)), 0)
and units_type = rec_Pr.units_type
and units_lower_limit <= rec_Pr.units
and nvl(units_upper_limit, 999999999) > rec_Pr.units;
if l_UnitsCount = 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) := 'PR03';
t_CSE5(l_Count) := C_ErrMsg_PR03;
l_Count := l_Count + 1;
end if;
end if;
exception
when no_data_found then
null;
end;
-- 4.
-- 5
if rec_Pr.kenan_product_id in ('132', '40004', '40005') and
rec_Pr.status = 'A' and
(rec_Pr.suspend_initiative = 'N' or
rec_Pr.equip_type not in ('1001', '1002', '1004', '1012', '1018',
'4002', '4003', '4004', '6003', '6004')) 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) := 'PR05';
t_CSE5(l_Count) := C_ErrMsg_PR05;
l_Count := l_Count + 1;
end if;
-- PR06:
if rec_Pr.suspend_initiative = 'Y' and rec_Pr.status = 'A' and
rec_Pr.equip_type in
('1001', '1002', '1004', '1012', '1018', '6003', '6004') then
--'4002', '4003', '4004',
declare
l_CountPr06 number(10);
begin
select /*+index(SRC_PROD_KENAN IT_SP_SS)*/
count(1)
into l_CountPr06
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'); -- '40004', '40005'
if l_CountPr06 = 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) := 'PR06';
t_CSE5(l_Count) := C_ErrMsg_PR06;
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
('1001', '1002', '1004', '1012', '1018', '6003', '6004') then
--'4002', '4003', '4004',
declare
l_CountPr35 number(10);
begin
select /*+index(SRC_PROD_KENAN IT_SP_SS)*/
count(1)
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -