PL/SQL

create or replace package csiowner.LINE_OF_BALANCE authid current_user as g_column CONSTANT NUMBER := 50; p_part_nbr VARCHAR2(100); p_from_date VARCHAR2(50); p_to_date VARCHAR2(50); FUNCTION check_shared_part(p_part_nbr VARCHAR2) RETURN VARCHAR2; PROCEDURE insert_parent_part; PROCEDURE gen_report_summary; PROCEDURE gen_report_details; PROCEDURE cal_order_qty; PROCEDURE update_total_qty; PROCEDURE update_qty_for_abs; PROCEDURE update_inspecation; FUNCTION before_report RETURN BOOLEAN; FUNCTION after_report RETURN BOOLEAN; end;

create or replace package body csiowner.LINE_OF_BALANCE
as
FUNCTION check_shared_part(p_part_nbr VARCHAR2)
RETURN VARCHAR2
IS
l_return_val VARCHAR2(1):='N';
l_count NUMBER;
BEGIN
BEGIN
SELECT COUNT(0)
INTO l_count
FROM csiowner.bill
WHERE comp_part_nbr = p_part_nbr
AND trunc(SYSDATE) BETWEEN begn_eff_dt AND end_eff_dt;
IF l_count > 1 THEN
l_return_val := 'Y';
END IF;
END;
RETURN l_return_val;
END check_shared_part;


PROCEDURE insert_parent_part
IS
CURSOR c_parent IS
select 0 sort_id, 0 bill_level, 0 qty_in_bom, part_nbr, part_desc, part_type,
csiowner.line_of_balance.check_shared_part(part_nbr) shared_part, null inspecation, lead_time,
nvl(ord_qty_shelf, 0) ord_qty_shelf, nvl(ord_qty_wip, 0) ord_qty_wip, nvl(total_qty, 0) total_qty
from(
SELECT part_nbr,part_desc,part_type, lead_time,SUM(ORD_QTY_SHELF) ORD_QTY_SHELF ,SUM(ORD_QTY_WIP) ORD_QTY_WIP,SUM(ORD_QTY_SHELF)+SUM(ORD_QTY_WIP) Total_QTY
FROM
(select o.part_nbr,
t.part_type,
t.part_desc,
t.lead_time lead_time,
0 ORD_QTY_SHELF,
o.QTY_ON_ORD - o.QTY_SCRAP - o.QTY_RECVD + o.QTY_NET_YIELD ORD_QTY_WIP
from csiowner.oord o, rrgsadmin.wip_cord d,csiowner.PART T
where o.ord_nbr = d.ord_nbr
and o.sub_ord_nbr = d.sub_ord_nbr
AND o.ord_stat = 'OP'
and o.ord_ctrl_code <> 'NA'
AND o.QTY_ON_ORD - o.QTY_SCRAP - o.QTY_RECVD + o.QTY_NET_YIELD > 0
AND T.part_nbr=o.part_nbr
AND t.part_nbr=rpad(p_part_nbr, 25, ' ')
UNION
select t.part_nbr,
t.part_type,
t.part_desc,
t.lead_time lead_time,
t.qty_on_hand ORD_QTY_SHELF,
0 ORD_QTY_WIP
from csiowner.part t
WHERE t.part_nbr=rpad(p_part_nbr, 25, ' '))
GROUP BY part_nbr,part_desc,part_type,lead_time);
BEGIN
FOR x IN c_parent LOOP
INSERT INTO csiowner.line_of_balance_tmp(
sort_id,
bill_level,
qty_in_bom,
part_nbr,
part_desc,
part_type,
shared_part,
inspecation,
lead_time,
ord_qty_shelf,
ord_qty_wip,
total_qty)
VALUES( x.sort_id,
x.bill_level,
x.qty_in_bom,
trim(x.part_nbr),
x.part_desc,
x.part_type,
x.shared_part,
x.inspecation,
x.lead_time,
x.ord_qty_shelf,
x.ord_qty_wip,
x.total_qty);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('insert_parent_part error:'||substr(SQLERRM,1,200));
raise_application_error(-20000,'insert_parent_part error:'||substr(SQLERRM,1,200));
END insert_parent_part;


PROCEDURE gen_report_summary
IS
CURSOR c_summary IS
select sort_id, bill_level, b.qty_per qty_in_bom, c.part_nbr, c.part_desc,c.part_type,
csiowner.line_of_balance.check_shared_part(c.part_nbr) shared_part, null inspecation, c.lead_time,
nvl(ord_qty_shelf, 0) ord_qty_shelf, nvl(ord_qty_wip, 0) ord_qty_wip, nvl(total_qty, 0) total_qty
from(
SELECT part_nbr,part_desc,part_type,lead_time,SUM(ORD_QTY_SHELF) ORD_QTY_SHELF ,SUM(ORD_QTY_WIP) ORD_QTY_WIP,SUM(ORD_QTY_SHELF)+SUM(ORD_QTY_WIP) Total_QTY
FROM
(select o.part_nbr,
t.part_type,
t.part_desc,
t.lead_time lead_time,
0 ORD_QTY_SHELF,
o.QTY_ON_ORD - o.QTY_SCRAP - o.QTY_RECVD + o.QTY_NET_YIELD ORD_QTY_WIP
from csiowner.oord o, rrgsadmin.wip_cord d,csiowner.PART T
where o.ord_nbr = d.ord_nbr
and o.sub_ord_nbr = d.sub_ord_nbr
AND o.ord_stat = 'OP'
and o.ord_ctrl_code <> 'NA'
AND o.QTY_ON_ORD - o.QTY_SCRAP - o.QTY_RECVD + o.QTY_NET_YIELD > 0
AND T.part_nbr=o.part_nbr
UNION
select t.part_nbr,
t.part_type,
t.part_desc,
t.lead_time lead_time,
t.qty_on_hand ORD_QTY_SHELF,
0 ORD_QTY_WIP
from csiowner.part t
WHERE T.QTY_ON_HAND <>0)
GROUP BY part_nbr,part_desc,part_type,lead_time) a,
(select lpad(' ',2*(level-1))||level bill_level, comp_part_nbr, rownum sort_id, bom_doc_nbr, qty_per
from csiowner.bill
start with bom_doc_nbr=rpad(p_part_nbr, 25, ' ')
connect by prior comp_part_nbr = bom_doc_nbr) b, csiowner.part c
where a.part_nbr(+) = b.comp_part_nbr and b.comp_part_nbr = c.part_nbr
and c.part_nbr not in('(OPEN)')
order by sort_id;
BEGIN
FOR x IN c_summary LOOP
INSERT INTO csiowner.line_of_balance_tmp(
sort_id,
bill_level,
qty_in_bom,
part_nbr,
part_desc,
part_type,
shared_part,
inspecation,
lead_time,
ord_qty_shelf,
ord_qty_wip,
total_qty)
VALUES( x.sort_id,
x.bill_level,
x.qty_in_bom,
trim(x.part_nbr),
x.part_desc,
x.part_type,
x.shared_part,
x.inspecation,
x.lead_time,
x.ord_qty_shelf,
x.ord_qty_wip,
x.total_qty);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('gen_report_summary error:'||substr(SQLERRM,1,200));
raise_application_error(-20000,'gen_report_summary error:'||substr(SQLERRM,1,200));
END gen_report_summary;


PROCEDURE gen_report_details
IS
CURSOR c_details IS
select *
from (
select 'SO' lab_type,
'Order #'||s.ord_nbr||chr(13)||to_char(s.rqst_shp_dt,'mm/dd/yyyy') lab,
trim(s.prt_nbr) prt_nbr,
s.rqst_shp_dt due_date,
sum(s.qty_ordered - s.tot_qty_shp) qty
from csiowner.sord s
where rec_cd='DT'
and ord_itm_status not in ('CL','CT')
and s.rqst_shp_dt between to_date(p_from_date,'mm/dd/yyyy') and to_date(p_to_date, 'mm/dd/yyyy')
and s.qty_ordered - s.tot_qty_shp > 0
group by s.ord_nbr, s.prt_nbr, s.rqst_shp_dt
union all
select 'PO' lab_type,
'PO #'||d.purchase_order_nbr||chr(13)||to_char(d.date_due_in_stock,'mm/dd/yyyy') lab,
trim(d.part_nbr) prt_nbr,
d.date_due_in_stock due_date,
sum(d.qty_order - d.qty_received_total) qty
from csiowner.pord d
where d.date_due_in_stock between to_date(p_from_date,'mm/dd/yyyy') and to_date(p_to_date, 'mm/dd/yyyy')
and d.qty_order - d.qty_received_total > 0
and d.code_status not in('CL')
group by d.purchase_order_nbr, d.part_nbr, d.date_due_in_stock
)
where prt_nbr in(
select trim(comp_part_nbr)
from csiowner.bill
start with bom_doc_nbr=rpad(p_part_nbr, 25, ' ')
connect by prior comp_part_nbr = bom_doc_nbr
union
select p_part_nbr from dual
)
order by 4, 2, 1;

l_count NUMBER;
l_label_prev VARCHAR2(200);
l_sql_label VARCHAR2(2000);
l_sql_data VARCHAR2(2000);
BEGIN
l_count := 0;
l_label_prev := '###@@@$$$';
FOR x IN c_details LOOP
IF l_label_prev <> x.lab THEN
l_count := l_count + 1;
END IF;
IF l_count <= g_column THEN
-- setup label
l_sql_label := 'update csiowner.line_of_balance_tmp set label_order_' || l_count || ' = ''' || x.lab ||''' where sort_id = ''-1''';
EXECUTE IMMEDIATE l_sql_label;

-- setup detail column
IF x.lab_type = 'SO' THEN
l_sql_data := 'update csiowner.line_of_balance_tmp set qty_order_' || l_count || ' = -' || x.qty ||' where part_nbr = ''' || x.prt_nbr || '''';
ELSE
l_sql_data := 'update csiowner.line_of_balance_tmp set qty_order_' || l_count || ' = ' || x.qty ||' where part_nbr = ''' || x.prt_nbr || '''';
END IF;
--dbms_output.put_line(l_sql_data);

EXECUTE IMMEDIATE l_sql_data;
END IF;
l_label_prev := x.lab;
END LOOP;

UPDATE csiowner.line_of_balance_tmp
SET total_qty = nvl(l_count, 0)
WHERE sort_id = -1;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('gen_report_details error:'||substr(SQLERRM,1,200));
raise_application_error(-20000,'gen_report_details error:'||substr(SQLERRM,1,200));
END gen_report_details;


PROCEDURE cal_order_qty
IS
l_col NUMBER;
l_sql VARCHAR2(32000);
BEGIN
FOR l_col IN 1 .. g_column LOOP
l_sql := '
DECLARE
CURSOR c_details IS
SELECT *
FROM csiowner.line_of_balance_tmp
WHERE sort_id>=0
ORDER BY sort_id;

TYPE t_qty_in_bom IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_qty_in_bom t_qty_in_bom;
l_parent_qty NUMBER;
l_parent_level NUMBER;
i NUMBER;
l_prev_rowid NUMBER;
l_qty_order_tmp NUMBER;
l_meet_cnt NUMBER;
BEGIN
l_parent_qty := 0;
l_meet_cnt := 99999;
FOR x IN c_details LOOP
i := to_number(trim(x.bill_level));
IF i <= l_parent_level THEN
l_meet_cnt := l_meet_cnt + 1;
END IF;
IF abs(nvl(x.qty_order_' || l_col || ',0)) > 0 THEN
IF l_meet_cnt > 1 THEN
l_parent_qty := x.qty_order_'|| l_col ||';
l_parent_level := i;
l_meet_cnt := 1;
END IF;
END IF;

IF i > l_parent_level AND l_meet_cnt = 1 THEN
l_qty_in_bom(i) := x.qty_in_bom;
l_qty_order_tmp := NULL;
FOR j IN l_parent_level+1 .. i LOOP
l_qty_order_tmp := nvl(l_qty_order_tmp, l_parent_qty) * l_qty_in_bom(j);
END LOOP;
UPDATE csiowner.line_of_balance_tmp SET qty_order_'|| l_col ||' = l_qty_order_tmp WHERE sort_id = x.sort_id;
END IF;
END LOOP;
END;
';
EXECUTE IMMEDIATE l_sql;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('cal_order_qty error:'||substr(SQLERRM,1,200));
raise_application_error(-20000,'cal_order_qty error:'||substr(SQLERRM,1,200));
END cal_order_qty;


PROCEDURE update_total_qty
IS
l_sql varchar2(2000);
l_tmp_sql varchar2(2000);
BEGIN
for i in 1 .. g_column loop
l_tmp_sql := '';
for j in 1 .. i loop
if j = 1 then
l_tmp_sql := ' + nvl(qty_order_1, 0)';
else
l_tmp_sql := l_tmp_sql || ' + nvl(qty_order_'|| j ||', 0)';
end if;
end loop;
l_sql := 'total_order_'|| i ||' = decode(qty_order_'||i||', null, null, total_qty'|| l_tmp_sql ||')' ;
l_sql := 'update csiowner.line_of_balance_tmp set ' || l_sql;
EXECUTE IMMEDIATE l_sql;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('update_total_qty error:'||substr(SQLERRM,1,200));
raise_application_error(-20000,'update_total_qty error:'||substr(SQLERRM,1,200));
END update_total_qty;


PROCEDURE update_qty_for_abs
IS
l_tmp_sql varchar2(4000);
BEGIN
l_tmp_sql := 'update csiowner.line_of_balance_tmp set ';
for i in 1 .. g_column loop
if i = 1 then
l_tmp_sql := l_tmp_sql || ' qty_order_'||i ||'= abs(qty_order_'||i||')';
else
l_tmp_sql := l_tmp_sql || ', qty_order_'||i ||' = abs(qty_order_'||i||')';
end if;
end loop;
EXECUTE IMMEDIATE l_tmp_sql;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('update_qty_for_abs error:'||substr(SQLERRM,1,200));
raise_application_error(-20000,'update_qty_for_abs error:'||substr(SQLERRM,1,200));
END update_qty_for_abs;

PROCEDURE update_inspecation
IS
BEGIN
UPDATE csiowner.line_of_balance_tmp t
SET inspecation = (SELECT nvl(SUM(qty_on_hand), 0)
FROM csiowner.invd d
WHERE d.part_nbr = rpad(t.part_nbr, 25, ' ')
AND qty_on_hand > 0
AND location_key IN ('LAB', 'INS', '******')
);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('update_inspecation error:'||substr(SQLERRM,1,200));
raise_application_error(-20000,'update_inspecation error:'||substr(SQLERRM,1,200));
END update_inspecation;

FUNCTION before_report RETURN BOOLEAN
IS
BEGIN
EXECUTE IMMEDIATE 'delete from csiowner.line_of_balance_tmp';

-- insert label row
INSERT INTO csiowner.line_of_balance_tmp(sort_id) VALUES(-1);

insert_parent_part;
gen_report_summary;
gen_report_details;
--
cal_order_qty;
update_total_qty;
update_qty_for_abs;
update_inspecation;
RETURN TRUE;
END;

FUNCTION after_report RETURN BOOLEAN
IS
BEGIN
RETURN TRUE;
END;
end;

[@more@]