Environment: Oracle database, HPUX
Description: This code was run nightly as CRON script which summarizes demand and forecast information from JIT ERP database.
Code Listing:
set termout off
rem $Author: roblaw $
rem $Locker: $
rem $Date: 1996/08/30 22:36:23 $
rem $Source: /appl/jit/RCS/ps/src/pnsdu.sql,v $
rem $Revision: 1.4 $
/* program pnsdu.sql
date 06-18-96
author robert lawson
modified roblaw 01-22-97 Z(MFG) + I(ncomplete) are not to be treated
as 'hold'.
roblaw 02-15-97 added po requisitions, intercompany transfers,
ship & book ytd.
purpose: create demand & forecast table
Notes:
1. Don't be tempted to change putting checks in decodes, as
they get you dates AND quantities.
Unfortunately they're rather hard to read!
s pnsdu.sql del
transmit 'sqlplus jit/xxx @pnsdu^m!^mY^m10-01-1990^m^m^m'
*/
set termout on
accept i_plant prompt "Plant Code? (!=all): "
accept i_clear prompt "Clear first? (Yes/No): "
accept b_date prompt "Begin date book/shipments (MM-DD-YYYY,= today): "
accept e_date prompt "End date book/shipments (MM-DD-YYYY,= today): "
-- set termout off
set echo on
-- set serveroutput on
declare
/*
===============================================================
Variable declared
===============================================================
*/
o_plant item_summary.plant%type;
o_item_no item_summary.item_no%type;
o_description item_summary.description%type;
o_family_cd item_summary.family_cd%type;
o_status_flag item_summary.status_flag%type;
o_abc_class item_summary.abc_class %type;
o_stock_um item_summary.stock_um%type;
o_planning_group item_summary.planning_group%type;
o_planner_code item_summary.planner_code%type;
o_unit_cost item_summary.unit_cost%type;
o_last_activity_date item_summary.last_activity_date%type;
o_last_count_date item_summary.last_count_date%type;
o_complete_date item_summary.complete_date%type;
o_request_date item_summary.order_date%type;
o_order_date item_summary.cust_no%type;
o_cust_no item_summary.cust_no%type;
o_qoh_qty item_summary.qoh_qty%type;
o_so_qty_hold item_summary.so_qty_hold%type;
o_so_qty_other item_summary.so_qty_other%type;
o_so_qty_sch_s item_summary.so_qty_other%type;
o_so_qty_sch_f item_summary.so_qty_sch_f%type;
o_so_dm_qty_w0 item_summary.so_dm_qty_w0%type;
o_so_dm_qty_w1 item_summary.so_dm_qty_w1%type;
o_so_dm_qty_w2 item_summary.so_dm_qty_w2%type;
o_so_dm_qty_w3 item_summary.so_dm_qty_w3%type;
o_so_dm_qty_w4 item_summary.so_dm_qty_w4%type;
o_so_dm_qty_w5 item_summary.so_dm_qty_w5%type;
o_so_dm_qty_w6 item_summary.so_dm_qty_w6%type;
o_so_dm_qty_w7 item_summary.so_dm_qty_w7%type;
o_so_dm_qty_w8 item_summary.so_dm_qty_w8%type;
o_wo_po_qty_w0 item_summary.wo_po_qty_w0%type;
o_wo_po_qty_w1 item_summary.wo_po_qty_w1%type;
o_wo_po_qty_w2 item_summary.wo_po_qty_w2%type;
o_wo_po_qty_w3 item_summary.wo_po_qty_w3%type;
o_wo_po_qty_w4 item_summary.wo_po_qty_w4%type;
o_wo_po_qty_w5 item_summary.wo_po_qty_w5%type;
o_wo_po_qty_w6 item_summary.wo_po_qty_w6%type;
o_wo_po_qty_w7 item_summary.wo_po_qty_w7%type;
o_wo_po_qty_w8 item_summary.wo_po_qty_w8%type;
o_wo_qty_w0 item_summary.wo_po_qty_w0%type;
o_wo_qty_w1 item_summary.wo_po_qty_w1%type;
o_wo_qty_w2 item_summary.wo_po_qty_w2%type;
o_wo_qty_w3 item_summary.wo_po_qty_w3%type;
o_wo_qty_w4 item_summary.wo_po_qty_w4%type;
o_wo_qty_w5 item_summary.wo_po_qty_w5%type;
o_wo_qty_w6 item_summary.wo_po_qty_w6%type;
o_wo_qty_w7 item_summary.wo_po_qty_w7%type;
o_wo_qty_w8 item_summary.wo_po_qty_w8%type;
o_po_qty_w0 item_summary.wo_po_qty_w0%type;
o_po_qty_w1 item_summary.wo_po_qty_w1%type;
o_po_qty_w2 item_summary.wo_po_qty_w2%type;
o_po_qty_w3 item_summary.wo_po_qty_w3%type;
o_po_qty_w4 item_summary.wo_po_qty_w4%type;
o_po_qty_w5 item_summary.wo_po_qty_w5%type;
o_po_qty_w6 item_summary.wo_po_qty_w6%type;
o_po_qty_w7 item_summary.wo_po_qty_w7%type;
o_po_qty_w8 item_summary.wo_po_qty_w8%type;
o_ip_qty_w0 item_summary.wo_po_qty_w0%type;
o_ip_qty_w1 item_summary.wo_po_qty_w1%type;
o_ip_qty_w2 item_summary.wo_po_qty_w2%type;
o_ip_qty_w3 item_summary.wo_po_qty_w3%type;
o_ip_qty_w4 item_summary.wo_po_qty_w4%type;
o_ip_qty_w5 item_summary.wo_po_qty_w5%type;
o_ip_qty_w6 item_summary.wo_po_qty_w6%type;
o_ip_qty_w7 item_summary.wo_po_qty_w7%type;
o_ip_qty_w8 item_summary.wo_po_qty_w8%type;
o_ship_ytd item_summary.ship_ytd%type;
o_credit_ytd item_summary.credit_ytd%type;
o_book_ytd item_summary.book_ytd%type;
o_wo_po_req item_summary.wo_po_req%type;
l_last_activity_date item_summary.last_activity_date%type;
l_last_count_date item_summary.last_count_date%type;
l_complete_date item_summary.complete_date%type;
l_request_date item_summary.request_date%type;
l_order_date item_summary.order_date%type;
l_cust_no item_summary.cust_no%type;
l_count numeric(10,5) := 0; /* commit counter */
l_summary_exists boolean; /* is there an existing item_summary? */
summary_rowid rowid;
l_stamp item_summary.description%type;
/*
===============================================================
Cursors
===============================================================
*/
cursor get_im is
SELECT
im.plant,
im.item_no,
im.description,
im.stock_um,
im.status_flag,
im.abc_class,
nvl(ci.unit_cost,0),
ip.planning_group,
ip.planner_code
FROM
item_planning ip, cost_item ci, item_master im
WHERE
('&i_plant' = '!' or im.plant = '&i_plant') and
nvl(im.model_flag,'N') = 'N' and
-- im.item_no in ('R10B5526','R10B5902','R10B5900') and
ci.plant(+) = im.plant and
ci.item_no(+) = im.item_no and
ci.cost_type(+) = 'S' and
ip.plant(+) = im.plant and
ip.item_no(+) = im.item_no;
cursor get_wo is
SELECT
MAX(wm.complete_date) c_date,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),0,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_0,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),1,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_1,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),2,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_2,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),3,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_3,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),4,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_4,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),5,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_5,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),6,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_6,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),7,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_7,
SUM(decode(least(greatest(CEIL((nvl(wm.due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),8,
(nvl(wm.qty_ordered,0) - nvl(wm.qty_complete,0)),0)) week_8
FROM
wo_master wm
WHERE
wm.plant = o_plant and
wm.item_no = o_item_no and
order_status not in ('A','X') and
nvl(qty_ordered,0) > nvl(qty_complete,0);
cursor get_po is
SELECT
MAX(pl.request_date),
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),0,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_0,
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),1,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_1,
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),2,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_2,
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),3,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_3,
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),4,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_4,
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),5,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_5,
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),6,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_6,
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),7,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_7,
SUM(decode(least(greatest(CEIL((nvl(pl.curr_prom_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),8,
decode(pl.line_status,'O',greatest((nvl(pl.qty_ordered,0) -
nvl(pl.qty_received,0)),0),0),0)) week_8
FROM
po_line pl
WHERE
pl.plant = o_plant and
pl.item_no = o_item_no and
line_status = 'O';
cursor get_qoh is
SELECT
max(il.last_activity_date),
max(il.last_count_date),
sum(greatest(nvl(il.qty,0),0))
FROM
item_location il
WHERE
il.plant = o_plant and
il.item_no = o_item_no and
nvl(il.qty,0) > 0;
cursor get_so is
SELECT
max(order_date) o_date,
SUM(Decode(bh.hold_cd,null,0,'Z',0,'I',0,bx.qty_extended)) hold,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,0,bx.qty_extended),0)) other,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,0,
decode(bh.release_flag,'Y',0,bx.qty_extended)),0),0)) sch_s,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,0,
decode(bh.release_flag,'Y',bx.qty_extended,0)),0),0)) sch_f,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(bh.week_num,0,bx.qty_extended,0),0),0),0)) week0,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(bh.week_num,1,bx.qty_extended,0),0),0),0)) week1,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(bh.week_num,2,bx.qty_extended,0),0),0),0)) week2,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(bh.week_num,3,bx.qty_extended,0),0),0),0)) week3,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(bh.week_num,4,bx.qty_extended,0),0),0),0)) week4,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(bh.week_num,5,bx.qty_extended,0),0),0),0)) week5,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(bh.week_num,6,bx.qty_extended,0),0),0),0)) week6,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(bh.week_num,7,bx.qty_extended,0),0),0),0)) week7,
SUM(Decode(bh.hold_cd,null,
decode(bh.plant_x,null,
decode(bh.schedule_no,null,
decode(greatest(bh.week_num,7),7,0,bx.qty_extended),0),0),0)) week8
FROM
backlog_header bh, backlog_demand bx
WHERE
bx.plant = o_plant and
bx.component_no = o_item_no and
bh.order_no = bx.order_no and
bh.order_suffix = bx.order_suffix and
bh.plant = bx.plant;
cursor get_summary is
SELECT
last_activity_date,
last_count_date,
complete_date,
request_date,
order_date,
rowid
FROM
item_summary it
WHERE
it.plant = o_plant and
it.item_no = o_item_no;
cursor get_req is
SELECT
-- rd.date_required,
sum(rd.qty_required)
FROM requisition rd
WHERE
rd.plant = o_plant and
rd.item_no = o_item_no and
rd.status = 'O' and
rd.requested_by <> 'MRP';
cursor get_booked is
SELECT
SUM(nvl(qm.ext_qty,0) * decode(oh.order_status,'B',od.qty_shipped,od.qty_order))
FROM oedetl od, oehead oh, quote_model_detail qm
WHERE
qm.plant = o_plant and
qm.component_no = o_item_no and
oh.order_no = qm.quote_no and
oh.plant = qm.plant and
oh.order_type = 'ORD' and
trunc(oh.order_date) >=
decode('&b_date',null,trunc(SYSDATE),to_date('&b_date','MM-DD-YYYY')) and
trunc(oh.order_date) <= decode('&e_date',null,trunc(SYSDATE),to_date('&e_date','MM-DD-YYYY')) and od.order_no = qm.quote_no and od.line_no = qm.oedetl_line_no and od.plant = qm.plant; cursor get_ship_credit is SELECT SUM(decode(order_type,'ORD',(nvl(qm.ext_qty,0) * od.qty_shipped),0)), SUM(decode(order_type,'CM',(nvl(qm.ext_qty,0) * od.qty_shipped), 'CN',(nvl(qm.ext_qty,0) * od.qty_shipped),0)) FROM oedetl od, oehead oh, quote_model_detail qm WHERE qm.plant = o_plant and qm.component_no = o_item_no and oh.order_no = qm.quote_no and oh.plant = qm.plant and oh.order_type <> 'DM' and
trunc(oh.ship_dt) >=
decode('&b_date',null,trunc(SYSDATE),to_date('&b_date','MM-DD-YYYY')) and
trunc(oh.ship_dt) <= decode('&e_date',null,trunc(SYSDATE),to_date('&e_date','MM-DD-YYYY')) and od.order_no = qm.quote_no and od.line_no = qm.oedetl_line_no and od.plant = qm.plant; cursor get_ip_po is SELECT SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),0, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_0, SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),1, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_1, SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),2, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_2, SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),3, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_3, SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),4, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_4, SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),5, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_5, SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),6, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_6, SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),7, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_7, SUM(decode(least(greatest(CEIL((nvl(ip.rcv_due_date,sysdate) - (sysdate+decode(to_char(sysdate,'D'),7,1, -((to_char(sysdate,'D')-1)))))/7),0),8),8, (greatest(ip.qty_required,nvl(ip.qty_shipped,0)) - nvl(ip.qty_received,0)),0)) week_8 FROM ip_requirements ip WHERE ip.rcv_plant = o_plant and ip.item_no = o_item_no and ip.status = 'O' and greatest(ip.qty_required,nvl(ip.qty_shipped,0)) > nvl(ip.qty_received,0);
cursor get_ip_so is
SELECT
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),0,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_0,
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),1,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_1,
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),2,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_2,
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),3,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_3,
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),4,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_4,
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),5,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_5,
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),6,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_6,
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),7,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_7,
SUM(decode(least(greatest(CEIL((nvl(ip.ship_due_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),8),8,
(least(nvl(ip.qty_shipped,0),ip.qty_required) - ip.qty_required),0)) week_8
FROM ip_requirements ip
WHERE
ip.ship_plant = o_plant and
ip.item_no = o_item_no and
ip.status = 'O' and
ip.qty_required > least(nvl(ip.qty_shipped,0),ip.qty_required);
/*
===============================================================
In the beginning
===============================================================
*/
begin
if upper(nvl('&i_clear','N')) = 'Y' then
delete from item_summary;
end if;
l_stamp := 'Created: ' to_char(SYSDATE,'MM/DD/YY HH:MI AM') '; User: ' USER;
UPDATE item_summary SET
description = l_stamp
WHERE plant = '**';
if sql%notfound then
INSERT INTO item_summary
(plant, description)
VALUES ('**',l_stamp);
end if;
commit;
/*
===============================================================
Get parts
===============================================================
*/
open get_im;
loop
fetch get_im into
o_plant, o_item_no, o_description, o_stock_um, o_status_flag,
o_abc_class, o_unit_cost, o_planning_group, o_planner_code;
exit when get_im%notfound;
dbms_output.put_line('1**plant 'o_plant' item_no 'o_item_no);
/*
===============================================================
Get work orders
===============================================================
*/
open get_wo;
fetch get_wo into o_complete_date, o_wo_qty_w0, o_wo_qty_w1,
o_wo_qty_w2, o_wo_qty_w3, o_wo_qty_w4, o_wo_qty_w5, o_wo_qty_w6,
o_wo_qty_w7, o_wo_qty_w8;
close get_wo;
o_wo_qty_w0 := nvl(o_wo_qty_w0,0); o_wo_qty_w1 := nvl(o_wo_qty_w1,0);
o_wo_qty_w2 := nvl(o_wo_qty_w2,0); o_wo_qty_w3 := nvl(o_wo_qty_w3,0);
o_wo_qty_w4 := nvl(o_wo_qty_w4,0); o_wo_qty_w5 := nvl(o_wo_qty_w5,0);
o_wo_qty_w6 := nvl(o_wo_qty_w6,0); o_wo_qty_w7 := nvl(o_wo_qty_w7,0);
o_wo_qty_w8 := nvl(o_wo_qty_w8,0);
dbms_output.put_line('2**plant 'o_plant' item_no 'o_item_no
'*'o_wo_qty_w0'*'o_wo_qty_w1'*'o_wo_qty_w2'*'o_wo_qty_w3);
/*
===============================================================
Get purchase orders
===============================================================
*/
open get_po;
fetch get_po into o_request_date, o_po_qty_w0, o_po_qty_w1,
o_po_qty_w2, o_po_qty_w3, o_po_qty_w4, o_po_qty_w5, o_po_qty_w6,
o_po_qty_w7, o_po_qty_w8;
close get_po;
o_po_qty_w0 := nvl(o_po_qty_w0,0); o_po_qty_w1 := nvl(o_po_qty_w1,0);
o_po_qty_w2 := nvl(o_po_qty_w2,0); o_po_qty_w3 := nvl(o_po_qty_w3,0);
o_po_qty_w4 := nvl(o_po_qty_w4,0); o_po_qty_w5 := nvl(o_po_qty_w5,0);
o_po_qty_w6 := nvl(o_po_qty_w6,0); o_po_qty_w7 := nvl(o_po_qty_w7,0);
o_po_qty_w8 := nvl(o_po_qty_w8,0);
dbms_output.put_line('3**plant 'o_plant' item_no 'o_item_no
'*'o_po_qty_w0'*'o_po_qty_w1'*'o_po_qty_w2'*'o_po_qty_w3);
/*
===============================================================
Get intercompany po's (supply)
===============================================================
*/
open get_ip_po;
fetch get_ip_po into o_ip_qty_w0, o_ip_qty_w1, o_ip_qty_w2, o_ip_qty_w3,
o_ip_qty_w4, o_ip_qty_w5, o_ip_qty_w6, o_ip_qty_w7, o_ip_qty_w8;
close get_ip_po;
o_ip_qty_w0 := nvl(o_ip_qty_w0,0); o_ip_qty_w1 := nvl(o_ip_qty_w1,0);
o_ip_qty_w2 := nvl(o_ip_qty_w2,0); o_ip_qty_w3 := nvl(o_ip_qty_w3,0);
o_ip_qty_w4 := nvl(o_ip_qty_w4,0); o_ip_qty_w5 := nvl(o_ip_qty_w5,0);
o_ip_qty_w6 := nvl(o_ip_qty_w6,0); o_ip_qty_w7 := nvl(o_ip_qty_w7,0);
o_ip_qty_w8 := nvl(o_ip_qty_w8,0);
dbms_output.put_line('4**plant 'o_plant' item_no 'o_item_no
'*'o_ip_qty_w0'*'o_ip_qty_w1'*'o_ip_qty_w2'*'o_ip_qty_w3);
-- add intercompany po's to vendor po's
o_po_qty_w0 := o_po_qty_w0 + o_ip_qty_w0;
o_po_qty_w1 := o_po_qty_w1 + o_ip_qty_w1;
o_po_qty_w2 := o_po_qty_w2 + o_ip_qty_w2;
o_po_qty_w3 := o_po_qty_w3 + o_ip_qty_w3;
o_po_qty_w4 := o_po_qty_w4 + o_ip_qty_w4;
o_po_qty_w5 := o_po_qty_w5 + o_ip_qty_w5;
o_po_qty_w6 := o_po_qty_w6 + o_ip_qty_w6;
o_po_qty_w7 := o_po_qty_w7 + o_ip_qty_w7;
o_po_qty_w8 := o_po_qty_w8 + o_ip_qty_w8;
/*
===============================================================
Get purchase order requisitions
===============================================================
*/
open get_req;
fetch get_req into o_wo_po_req;
close get_req;
o_wo_po_req := nvl(o_wo_po_req,0);
dbms_output.put_line('5**plant 'o_plant' item_no 'o_item_no
'*'o_wo_po_req);
/*
===============================================================
Get sales orders
===============================================================
*/
open get_so;
fetch get_so into o_order_date,
o_so_qty_hold, o_so_qty_other, o_so_qty_sch_s, o_so_qty_sch_f,
o_so_dm_qty_w0, o_so_dm_qty_w1, o_so_dm_qty_w2, o_so_dm_qty_w3,
o_so_dm_qty_w4, o_so_dm_qty_w5, o_so_dm_qty_w6, o_so_dm_qty_w7,
o_so_dm_qty_w8;
close get_so;
o_so_qty_hold := nvl(o_so_qty_hold,0); o_so_qty_other := nvl(o_so_qty_other,0);
o_so_qty_sch_s := nvl(o_so_qty_sch_s,0); o_so_qty_sch_f := nvl(o_so_qty_sch_f,0);
o_so_dm_qty_w0 := nvl(o_so_dm_qty_w0,0); o_so_dm_qty_w1 := nvl(o_so_dm_qty_w1,0);
o_so_dm_qty_w2 := nvl(o_so_dm_qty_w2,0); o_so_dm_qty_w3 := nvl(o_so_dm_qty_w3,0);
o_so_dm_qty_w4 := nvl(o_so_dm_qty_w4,0); o_so_dm_qty_w5 := nvl(o_so_dm_qty_w5,0);
o_so_dm_qty_w6 := nvl(o_so_dm_qty_w6,0); o_so_dm_qty_w7 := nvl(o_so_dm_qty_w7,0);
o_so_dm_qty_w8 := nvl(o_so_dm_qty_w8,0);
dbms_output.put_line('6**plant 'o_plant' item_no 'o_item_no);
/*
===============================================================
Get intercompany so's (demand)
===============================================================
*/
open get_ip_so;
fetch get_ip_so into o_ip_qty_w0, o_ip_qty_w1, o_ip_qty_w2, o_ip_qty_w3,
o_ip_qty_w4, o_ip_qty_w5, o_ip_qty_w6, o_ip_qty_w7, o_ip_qty_w8;
close get_ip_so;
o_ip_qty_w0 := nvl(o_ip_qty_w0,0); o_ip_qty_w1 := nvl(o_ip_qty_w1,0);
o_ip_qty_w2 := nvl(o_ip_qty_w2,0); o_ip_qty_w3 := nvl(o_ip_qty_w3,0);
o_ip_qty_w4 := nvl(o_ip_qty_w4,0); o_ip_qty_w5 := nvl(o_ip_qty_w5,0);
o_ip_qty_w6 := nvl(o_ip_qty_w6,0); o_ip_qty_w7 := nvl(o_ip_qty_w7,0);
o_ip_qty_w8 := nvl(o_ip_qty_w8,0);
dbms_output.put_line('7**plant 'o_plant' item_no 'o_item_no
'*'o_ip_qty_w0'*'o_ip_qty_w1'*'o_ip_qty_w2'*'o_ip_qty_w3);
-- add intercompany so's to customer so's
o_so_dm_qty_w0 := o_so_dm_qty_w0 + o_ip_qty_w0;
o_so_dm_qty_w1 := o_so_dm_qty_w1 + o_ip_qty_w1;
o_so_dm_qty_w2 := o_so_dm_qty_w2 + o_ip_qty_w2;
o_so_dm_qty_w3 := o_so_dm_qty_w3 + o_ip_qty_w3;
o_so_dm_qty_w4 := o_so_dm_qty_w4 + o_ip_qty_w4;
o_so_dm_qty_w5 := o_so_dm_qty_w5 + o_ip_qty_w5;
o_so_dm_qty_w6 := o_so_dm_qty_w6 + o_ip_qty_w6;
o_so_dm_qty_w7 := o_so_dm_qty_w7 + o_ip_qty_w7;
o_so_dm_qty_w8 := o_so_dm_qty_w8 + o_ip_qty_w8;
/*
===============================================================
Get YTD booked orders
===============================================================
*/
open get_booked;
fetch get_booked into o_book_ytd;
close get_booked;
o_book_ytd := nvl(o_book_ytd,0);
dbms_output.put_line('8**plant 'o_plant' item_no 'o_item_no
' o_book_ytd ' o_book_ytd);
/*
===============================================================
Get YTD shipments and credits
===============================================================
*/
open get_ship_credit;
fetch get_ship_credit into o_ship_ytd, o_credit_ytd;
close get_ship_credit;
o_ship_ytd := nvl(o_ship_ytd,0);
o_credit_ytd := nvl(o_credit_ytd,0);
dbms_output.put_line('9**plant 'o_plant' item_no 'o_item_no
' o_ship_ytd ' o_ship_ytd ' o_credit_ytd ' o_credit_ytd);
/*
===============================================================
Get quantity on hand
===============================================================
*/
open get_qoh;
fetch get_qoh into o_last_activity_date, o_last_count_date,
o_qoh_qty;
close get_qoh;
o_qoh_qty := nvl(o_qoh_qty,0);
dbms_output.put_line('10**plant 'o_plant' item_no 'o_item_no);
/*
===============================================================
Get existing item_summary
===============================================================
*/
-- reuse information, especially if detail has been removed
open get_summary;
fetch get_summary into l_last_activity_date, l_last_count_date,
l_complete_date, l_request_date, l_order_date, summary_rowid;
l_summary_exists := get_summary%found;
if l_summary_exists then
if o_last_activity_date is null and
l_last_activity_date is not null then
o_last_activity_date := l_last_activity_date;
elsif o_last_count_date is null and
l_last_count_date is not null then
o_last_count_date := l_last_count_date;
elsif o_request_date is null and
l_request_date is not null then
o_request_date := l_request_date;
elsif o_order_date is null and
l_order_date is not null then
o_order_date := l_order_date;
elsif o_cust_no is null and
l_cust_no is not null then
o_cust_no := l_cust_no;
end if;
else
l_last_activity_date := NULL; l_last_count_date := NULL;
l_request_date := NULL; l_order_date := NULL; l_cust_no := NULL;
end if;
close get_summary;
dbms_output.put_line('11**plant 'o_plant' item_no 'o_item_no);
/*
===============================================================
Add up supply
===============================================================
*/
o_wo_po_qty_w0 := o_wo_qty_w0 + o_po_qty_w0;
o_wo_po_qty_w1 := o_wo_qty_w1 + o_po_qty_w1;
o_wo_po_qty_w2 := o_wo_qty_w2 + o_po_qty_w2;
o_wo_po_qty_w3 := o_wo_qty_w3 + o_po_qty_w3;
o_wo_po_qty_w4 := o_wo_qty_w4 + o_po_qty_w4;
o_wo_po_qty_w5 := o_wo_qty_w5 + o_po_qty_w5;
o_wo_po_qty_w6 := o_wo_qty_w6 + o_po_qty_w6;
o_wo_po_qty_w7 := o_wo_qty_w7 + o_po_qty_w7;
o_wo_po_qty_w8 := o_wo_qty_w8 + o_po_qty_w8;
dbms_output.put_line('12**plant 'o_plant' item_no 'o_item_no
'*'o_wo_po_qty_w0'*'o_wo_po_qty_w1'*'o_wo_po_qty_w2'*'o_wo_po_qty_w3);
/*
===============================================================
Insert
===============================================================
*/
if l_summary_exists then
UPDATE item_summary SET
description = o_description, family_cd = o_family_cd, status_flag = o_status_flag,
abc_class = o_abc_class, stock_um = o_stock_um, planning_group = o_planning_group,
planner_code = o_planner_code, unit_cost = o_unit_cost,
last_activity_date = o_last_activity_date, last_count_date = o_last_count_date,
complete_date = o_complete_date, request_date = o_request_date,
order_date = o_order_date, cust_no = o_cust_no, qoh_qty = o_qoh_qty,
so_qty_hold = o_so_qty_hold, so_qty_other = o_so_qty_other,
so_qty_sch_s = o_so_qty_sch_s, so_qty_sch_f = o_so_qty_sch_f,
so_dm_qty_w0 = o_so_dm_qty_w0, so_dm_qty_w1 = o_so_dm_qty_w1,
so_dm_qty_w2 = o_so_dm_qty_w2, so_dm_qty_w3 = o_so_dm_qty_w3,
so_dm_qty_w4 = o_so_dm_qty_w4, so_dm_qty_w5 = o_so_dm_qty_w5,
so_dm_qty_w6 = o_so_dm_qty_w6, so_dm_qty_w7 = o_so_dm_qty_w7,
so_dm_qty_w8 = o_so_dm_qty_w8,
wo_po_qty_w0 = o_wo_po_qty_w0, wo_po_qty_w1 = o_wo_po_qty_w0,
wo_po_qty_w2 = o_wo_po_qty_w2, wo_po_qty_w3 = o_wo_po_qty_w3,
wo_po_qty_w4 = o_wo_po_qty_w4, wo_po_qty_w5 = o_wo_po_qty_w5,
wo_po_qty_w6 = o_wo_po_qty_w6, wo_po_qty_w7 = o_wo_po_qty_w7,
wo_po_qty_w8 = o_wo_po_qty_w8, wo_po_req = o_wo_po_req,
ship_ytd = o_ship_ytd, credit_ytd = o_credit_ytd,
book_ytd = o_book_ytd
WHERE rowid = summary_rowid;
dbms_output.put_line('13**plant 'o_plant' item_no 'o_item_no);
else
INSERT INTO item_summary
(plant, item_no, description, family_cd, status_flag,
abc_class, stock_um, planning_group, planner_code, unit_cost,
last_activity_date, last_count_date, complete_date,
request_date, order_date, cust_no, qoh_qty, so_qty_hold,
so_qty_other, so_qty_sch_s, so_qty_sch_f, so_dm_qty_w0,
so_dm_qty_w1, so_dm_qty_w2, so_dm_qty_w3, so_dm_qty_w4,
so_dm_qty_w5, so_dm_qty_w6, so_dm_qty_w7, so_dm_qty_w8,
wo_po_qty_w0, wo_po_qty_w1, wo_po_qty_w2, wo_po_qty_w3,
wo_po_qty_w4, wo_po_qty_w5, wo_po_qty_w6, wo_po_qty_w7,
wo_po_qty_w8, wo_po_req, ship_ytd, credit_ytd, book_ytd)
VALUES
(o_plant, o_item_no, o_description, o_family_cd, o_status_flag,
o_abc_class, o_stock_um, o_planning_group, o_planner_code, o_unit_cost,
o_last_activity_date, o_last_count_date, o_complete_date,
o_request_date, o_order_date, o_cust_no, o_qoh_qty, o_so_qty_hold,
o_so_qty_other, o_so_qty_sch_s, o_so_qty_sch_f, o_so_dm_qty_w0,
o_so_dm_qty_w1, o_so_dm_qty_w2, o_so_dm_qty_w3, o_so_dm_qty_w4,
o_so_dm_qty_w5, o_so_dm_qty_w6, o_so_dm_qty_w7, o_so_dm_qty_w8,
o_wo_po_qty_w0, o_wo_po_qty_w1, o_wo_po_qty_w2, o_wo_po_qty_w3,
o_wo_po_qty_w4, o_wo_po_qty_w5, o_wo_po_qty_w6, o_wo_po_qty_w7,
o_wo_po_qty_w8, o_wo_po_req, o_ship_ytd, o_credit_ytd, o_book_ytd);
dbms_output.put_line('14**plant 'o_plant' item_no 'o_item_no);
end if;
dbms_output.put_line('15**plant 'o_plant' item_no 'o_item_no);
l_count := l_count + 1;
if mod(l_count,20) = 0 then
commit;
l_count := 0;
end if;
end loop; /* get_im */
close get_im;
/*
===============================================================
El Fin
===============================================================
*/
commit;
end;
/
exit
No comments:
Post a Comment