bmxu.sql: bill of material utility

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This code ran nightly as CRON job to update the bill of material information into a format the NEW JIT system required. Shows examples using sy_error.log and cursors with argument lists.
Code Listing:

rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1999/04/19 15:25:26 $
rem $Source: /appl/jit/RCS/bm/src/bmxu.sql,v $
rem $Revision: 1.11 $
set termout off
/*
file   : bmxu.sql
purpose: create data for SemiFlatBOM for FlowTech
date   : 01/13/98
whom   : Robert Lawson
         Robert Lawson 07/13/98  Added ExpAssLabor table logic
         Robert Lawson 07/24/98  Added top level BOM explosion
         Robert Lawson 08/10/98  Include top level BOM purchase parts
         Robert Lawson 09/16/98  'A' are make part in plant 30
         Robert Lawson 02/24/99  Stopped ExpAssLab process, not used
         Robert Lawson 04/19/99  Bug: BOM didn't start with ECL
         Robert Lawson 06/14/99  Plug TROP_MOD_DATE for replication
*/
set termout on
accept i_plant prompt  "Plant (!=all): "

truncate table SemiFlatBOM;
set termout off
set echo off
set verify off
/* =========================================================== */
DECLARE

TYPE
LevelTabTyp IS TABLE OF
NUMBER(15,5)
INDEX BY BINARY_INTEGER;
level_tab       LevelTabTyp;

l_bom_qty                        SemiFlatBOM.Ext_QPA%type;
lo_bom_qty                       SemiFlatBOM.Ext_QPA%type;
i_level                          BINARY_INTEGER;
l_program                        debug_table.program%type := 'bmxu';
l_stamp                          VARCHAR2(100);
make_buy                         SemiFlatBOM.make_buy%type;
description                      item_master.description%type;
routing_rev                      routing_header.routing_rev%type;
sequence                         SemiFlatBOM.sequence%type;
code                             SemiFlatBOM.code%type;
total_unit_hour                  ExpAssLab.total_unit_hour%type;
total_unit_cost                  ExpAssLab.total_unit_cost%type;
l_key                            VARCHAR2(100);
Sys_Date                         DATE;

CURSOR mbom IS
SELECT DISTINCT
mb.parent_plant,
mb.component_no,
mb.parent_ecl
FROM bill_of_material bm, model_bom mb
WHERE
('&i_plant' = '!' or mb.parent_plant = '&i_plant') and
mb.std_qty_per > 0 and
nvl(mb.component_type,' ') = 'S' and
bm.parent_no    = mb.parent_no and
bm.parent_plant = mb.parent_plant and
bm.parent_ecl   = mb.parent_ecl and
bm.component_no = mb.component_no and
(trunc(sysdate) >= nvl(bm.effective_from,trunc(sysdate)-1) and
 trunc(sysdate) <= nvl(bm.effective_thru,trunc(sysdate)+1));  CURSOR bom (i_plant     IN bill_of_material.parent_plant%type, i_parent_no  IN bill_of_material.parent_no%type, i_ecl        IN bill_of_material.parent_ecl%type) IS SELECT level,bill_of_material.* FROM bill_of_material CONNECT BY PRIOR component_no = parent_no and PRIOR component_ecl = parent_ecl and PRIOR component_plant = parent_plant START WITH parent_plant = i_plant and parent_no = i_parent_no and parent_ecl = i_ecl;  CURSOR im (i_plant     IN item_master.plant%type, i_item_no    IN item_master.item_no%type) IS SELECT decode(nvl(ip.make_buy_flag,' '),'T','B',nvl(ip.make_buy_flag,' ')), im.description, im.current_rtg_rev FROM item_planning ip, item_master im WHERE im.plant = i_plant and im.item_no = i_item_no and ip.plant = im.plant and ip.item_no = im.item_no;  /* ExpAssLabor cursors */ -- Get all top level items (models) CURSOR mbom2 IS SELECT DISTINCT mb.parent_plant, mb.parent_no, mb.parent_ecl FROM bill_of_material bm, model_bom mb WHERE ('&i_plant' = '!' or mb.parent_plant = '&i_plant') and mb.std_qty_per > 0 and
bm.parent_no    = mb.parent_no and
bm.parent_plant = mb.parent_plant and
bm.parent_ecl   = mb.parent_ecl and
bm.component_no = mb.component_no and
(trunc(sysdate) >= nvl(bm.effective_from,trunc(sysdate)-1) and
 trunc(sysdate) <= nvl(bm.effective_thru,trunc(sysdate)+1));  CURSOR Routing (i_plant      IN item_master.plant%type, i_item_no     IN item_master.item_no%type, i_routing_rev IN routing_header.routing_rev%type) IS SELECT rd.work_center_no, rd.oper_seq, wc.description wc_description, wc.department, dp.dept_name, nvl(rh.acct_lot_size, 1) acct_lot_size, rd.hours_setup, nvl(rc1.rate,0) rate_setup, rd.hours_per_unit, nvl(rc2.rate,0) rate_run FROM rate_codes rc1, rate_codes rc2, dept_org_unit dp, wc_work_centers wc,  routing_detail rd, routing_header rh WHERE rh.plant = i_plant AND rh.item_no = i_item_no AND rh.routing_rev = i_routing_rev AND rd.plant = rh.plant AND rd.item_no = rh.item_no AND rd.routing_rev = rh.routing_rev AND wc.plant = rd.plant AND wc.work_center_no = rd.work_center_no AND dp.dept_org_unit_id = wc.department AND rc1.plant(+) = rd.plant AND rc1.rate_code(+) = rd.rate_setup  AND rc2.plant(+) = rd.plant AND rc2.rate_code(+) = rd.rate_direct;  /* Top Level BOM Explosion */ CURSOR XBOM is SELECT mb.parent_plant, mb.parent_no, sf.COMPONENT_NO, sf.SEQUENCE, sf.EXT_QPA * mb.std_qty_per Ext_QPA, sf.MAKE_BUY, sf.CODE + 10 code FROM semiflatbom sf, bill_of_material bm, model_bom mb WHERE ('&i_plant' = '!' or mb.parent_plant = '&i_plant') and mb.std_qty_per > 0 and
nvl(mb.component_type,' ') = 'S' and
bm.parent_no    = mb.parent_no and
bm.parent_plant = mb.parent_plant and
bm.parent_ecl   = mb.parent_ecl and
bm.component_no = mb.component_no and
(trunc(sysdate) >= nvl(bm.effective_from,trunc(sysdate)-1) and
 trunc(sysdate) <= nvl(bm.effective_thru,trunc(sysdate)+1)) and sf.plant = mb.parent_plant and sf.parent_no = mb.component_no;  CURSOR TopPurchasePart IS SELECT mb.parent_plant, mb.parent_no, mb.component_no, mb.std_qty_per, ip.MAKE_BUY_FLAG FROM bill_of_material bm, item_planning ip, model_bom mb WHERE ('&i_plant' = '!' or mb.parent_plant = '&i_plant') and mb.std_qty_per > 0 and
mb.component_type = 'S' and
ip.plant =  mb.parent_plant and
ip.item_no = mb.component_no and
ip.make_buy_flag in ('B','T') and
bm.parent_no    = mb.parent_no and
bm.parent_plant = mb.parent_plant and
bm.parent_ecl   = mb.parent_ecl and
bm.component_no = mb.component_no and
(trunc(sysdate) >= nvl(bm.effective_from,trunc(sysdate)-1) and
 trunc(sysdate) <= nvl(bm.effective_thru,trunc(sysdate)+1));  /* =========================================================== */ BEGIN -- GOTO ExpBOM_Section; IF '&i_plant' = '!' THEN   DELETE FROM SemiFlatBOM; ELSE   DELETE FROM SemiFlatBOM WHERE PLANT = '&i_plant'; END IF; Sys_Date := SYSDATE;   -- Date all entries the same  l_stamp := substr(l_program  ' created: '  to_char(SYSDATE,'MM/DD/YY HH:MI AM')  '; User: '  USER  '; Plant: '  '&i_plant',1,100); INSERT INTO SemiFlatBOM (plant, parent_no, TROP_MOD_DATE) VALUES ('**',substr(l_stamp,1,50), Sys_Date); commit;  FOR r_mbom IN mbom LOOP   sequence := 0;   l_bom_qty := 1;   lo_bom_qty := 1;   l_key  := substr(r_mbom.parent_plant  ' '  rtrim(r_mbom.component_no),1,100);   FOR r_bom IN bom(r_mbom.parent_plant, r_mbom.component_no, r_mbom.parent_ecl) LOOP     i_level := r_bom.level;     level_tab(i_level) := r_bom.qty_per;     l_bom_qty := lo_bom_qty;     FOR i IN 1 .. i_level LOOP       l_bom_qty := l_bom_qty * level_tab(i);     END LOOP;      OPEN im(r_bom.parent_plant, r_bom.component_no);     FETCH im INTO make_buy, description, routing_rev;     CLOSE im;  -- bug if R part at F level???     If substr(r_bom.component_no,1,1) = 'M' or make_buy = 'B' or       (substr(r_bom.component_no,1,1) = 'A' and r_mbom.parent_plant = '30') then       If make_buy = 'B' then          code := 2;       elsif substr(r_bom.parent_no,1,1) = 'M' then          code := 1;       else -- Must be 1st level M part          code := 0;       end if;       INSERT INTO SemiFlatBOM       (plant, parent_no, component_no, sequence, Ext_QPA, Make_Buy, code, TROP_MOD_DATE)       VALUES       (r_bom.parent_plant, r_mbom.component_no, r_bom.component_no,        sequence, l_bom_qty, make_buy, code, Sys_Date);       sequence := sequence + 1;     End If;   END LOOP; -- bom   COMMIT; END LOOP; -- mbom  /* =========================================================== */ <<>>
-- Model BOM make parts
FOR r_Xbom IN XBOM LOOP
  INSERT INTO SemiFlatBOM
  (plant, parent_no, component_no, sequence, Ext_QPA, Make_Buy, code, TROP_MOD_DATE)
  VALUES
  (r_Xbom.parent_plant, r_Xbom.parent_no, r_Xbom.component_no,
  r_Xbom.sequence, r_Xbom.Ext_QPA, r_Xbom.make_buy, r_Xbom.code, Sys_Date);
  COMMIT;
END LOOP; -- Xbom

-- Model BOM purhase parts
FOR r_TPP IN TopPurchasePart LOOP
  INSERT INTO SemiFlatBOM
  (plant, parent_no, component_no, sequence, Ext_QPA, Make_Buy, code, TROP_MOD_DATE)
  VALUES
  (r_TPP.parent_plant, r_TPP.parent_no, r_TPP.component_no,
   0, r_TPP.std_qty_per, r_TPP.MAKE_BUY_FLAG, 12, Sys_Date);
  COMMIT;
END LOOP; -- Xbom

GOTO Exit_Section;
/* =========================================================== */
<<>>
DELETE FROM ExpAssLab;

l_stamp := substr(l_program  ' created: '  to_char(SYSDATE,'MM/DD/YY HH:MI AM')
'; User: '  USER  '; Plant: '  '&i_plant',1,100);
INSERT INTO ExpAssLab (plant, description) VALUES ('**',l_stamp);
commit;

FOR r_mbom IN mbom2 LOOP
    l_key  := substr(r_mbom.parent_plant  ' '  rtrim(r_mbom.parent_no),1,100);
    l_bom_qty := 1;
    lo_bom_qty := 1;

    -- Do the top level part's routing
    OPEN im(r_mbom.parent_plant, r_mbom.parent_no);
    FETCH im INTO make_buy, description, routing_rev;
    CLOSE im;

    FOR r_rout IN routing(r_mbom.parent_plant, r_mbom.parent_no, routing_rev) LOOP
        total_unit_hour := l_bom_qty * ((r_rout.hours_setup /r_rout.acct_lot_size)+ r_rout.hours_per_unit);
        total_unit_cost := l_bom_qty * (((r_rout.hours_setup /r_rout.acct_lot_size)
         * r_rout.rate_setup) + (r_rout.hours_per_unit*r_rout.rate_run));

        INSERT INTO ExpAssLab
        (PLANT, ITEM_NO, PARENT_NO, COMPONENT_NO,
         DESCRIPTION, BLEVEL, OPER_SEQ, WORK_CENTER_NO, WC_DESCRIPTION,
         DEPARTMENT, DEPT_NAME, ACCT_LOT_SIZE, X_QPA,
         HOURS_SETUP, RATE_SETUP, HOURS_PER_UNIT, RATE_RUN,
         TOTAL_UNIT_HOUR, TOTAL_UNIT_COST)
        VALUES
        (r_mbom.parent_plant, r_mbom.parent_no, r_mbom.parent_no, r_mbom.parent_no,
         description, 0, r_rout.oper_seq, r_rout.work_center_no, r_rout.wc_description,
         r_rout.department, r_rout.dept_name, r_rout.acct_lot_size, l_bom_qty,
         r_rout.hours_setup, r_rout.rate_setup, r_rout.hours_per_unit, r_rout.rate_run,
         total_unit_hour, total_unit_cost);
    END LOOP; -- routing

   -- Do the parts routing that make up the top level
   FOR r_bom IN bom(r_mbom.parent_plant, r_mbom.parent_no, r_mbom.parent_ecl) LOOP
    i_level := r_bom.level;
    level_tab(i_level) := r_bom.qty_per;
    l_bom_qty := lo_bom_qty;
    FOR i IN 1 .. i_level LOOP
      l_bom_qty := l_bom_qty * level_tab(i);
    END LOOP;

-- Use component_no to get full item/parent/component relationship in table
    OPEN im(r_bom.parent_plant, r_bom.component_no);
    FETCH im INTO make_buy, description, routing_rev;
    CLOSE im;

    FOR r_rout IN routing(r_mbom.parent_plant, r_bom.component_no, routing_rev) LOOP
        total_unit_hour := l_bom_qty * ((r_rout.hours_setup /r_rout.acct_lot_size)+ r_rout.hours_per_unit);
        total_unit_cost := l_bom_qty * (((r_rout.hours_setup /r_rout.acct_lot_size)
         * r_rout.rate_setup) + (r_rout.hours_per_unit*r_rout.rate_run));

        INSERT INTO ExpAssLab
        (PLANT, ITEM_NO, PARENT_NO, COMPONENT_NO,
         DESCRIPTION, BLEVEL, OPER_SEQ, WORK_CENTER_NO, WC_DESCRIPTION,
         DEPARTMENT, DEPT_NAME, ACCT_LOT_SIZE, X_QPA,
         HOURS_SETUP, RATE_SETUP, HOURS_PER_UNIT, RATE_RUN,
         TOTAL_UNIT_HOUR, TOTAL_UNIT_COST)
        VALUES
        (r_mbom.parent_plant, r_mbom.parent_no, r_bom.parent_no, r_bom.component_no,
         description, i_level, r_rout.oper_seq, r_rout.work_center_no, r_rout.wc_description,
         r_rout.department, r_rout.dept_name, r_rout.acct_lot_size, l_bom_qty,
         r_rout.hours_setup, r_rout.rate_setup, r_rout.hours_per_unit, r_rout.rate_run,
         total_unit_hour, total_unit_cost);
    END LOOP; -- routing
  END LOOP; -- bom
  COMMIT;
END LOOP; -- mbom2
<<>>
NULL; -- dummy for exit_section
/* =========================================================== */
-- trap on 01436 (SQLCODE) and resume, but write error
EXCEPTION
WHEN OTHERS THEN
  sy_error.log(l_program,999,l_key);
END;
/
exit

pn103.sql: create csv file for orders

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This code was run on-demand by user to create csv file from order information
from JIT ERP database.
Code Listing:

rem $Author: roblaw $
rem $Locker:  $
rem $Date: 1997/01/30 23:06:02 $
rem $Source: /appl/jit/RCS/ps/src/pn103.sql,v $
rem $Revision: 1.2 $
rem put schedule information to comma delimited file for 123
rem converted from Databrowser schedwip
set termout on
set verify off
prompt
accept i_plant prompt "Plant: "
prompt
accept i_schedule prompt "Schedule (!=all): "
prompt
accept i_component prompt "Component (!=all, F%%, F01024): "

set termout off
-- set echo on
set feedback off

set heading off
set linesize 132
set pagesize 50000
set wrap off
set space 0

ttitle 'pn103; TimeStamp: ' todayvar '; UserID: ' sql.user ',,,' s -
'Options: Plant ' &i_plant '; Schedule ' &i_schedule '; Component ' -
 &i_component ',,,' s -
'bh.plant, oh.schedule_no, bx.component_no,  sum(bx.qty_extended)'

column x_qty format 99999999
column today noprint new_value todayvar
spool pn103.txt
-- replace(substr(item_no,1,12),',',' ')

SELECT
to_char(sysdate,'MM/DD/YY HH:MI AM') today,
bh.plant,
',',
oh.schedule_no,
',',
substr(bx.component_no,1,20),
',',
sum(bx.qty_extended) x_qty
FROM
backlog_demand bx, oehead oh, backlog_header bh
WHERE
bh.plant = '&i_plant' and
oh.order_no = bh.order_no and
oh.order_suffix = bh.order_suffix and
oh.plant = bh.plant and
(('&i_schedule' = '!' and oh.schedule_no is not null) or
   oh.schedule_no = '&i_schedule') and
bx.order_no = bh.order_no and
bx.order_suffix = bh.order_suffix and
bx.plant = bh.plant and
('&i_component' = '!' or bx.component_no like '&i_component')
GROUP BY bh.plant, oh.schedule_no, substr(bx.component_no,1,20)
ORDER BY 3,4
/
spool off
exit

pnsdu.sql: create demand and forecast file

Author: Robert Lawson
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

oeorf.sql: transfer order information

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This code was run on-demand by user to tranfer order information to subcontractor vendors from JIT ERP database.
Code Listing:

set termout off
rem $Author: roblaw $
rem $Locker:  $
rem $Date: 1996/07/12 15:24:37 $
rem $Source: /appl/jit/RCS/oe/src/oeorf.sql,v $
rem $Revision: 1.7 $
/* program oeorf.sql
   date  OCT-26-96
   author robert lawson

 purpose: create open backlog file for electronic transfer.  Note the
 backlog files are used, which oeblu creates, as much faster

Notes:
1. shipto_cd got left out of oeorf layout
2. freight, address and sidemark not part of oeblu
3. alter table temp_4_reports modify text varchar2(400)
4. Numbers have no commas!  Same length
*/

set termout on
set verify on
accept i_plant prompt "Plant: "
prompt
accept i_schedule_no prompt "Schedule Number (!=all): "
set termout off
-- set echo on
-- set serveroutput on

declare
/*
===============================================================
Variable declared
===============================================================
*/
o_plant           oehead.plant%type;
o_order_no        oehead.order_no%type;
o_order_suffix    oehead.order_suffix%type;
o_schedule_no     oehead.schedule_no%type;
o_shipto_cd       oehead.shipto_cd%type; /* not passed now */
o_ship_name       oehead.ship_name%type;
o_ship_addr1      oehead.ship_addr1%type;
o_ship_addr2      oehead.ship_addr2%type;
o_ship_addr3      oehead.ship_addr3%type;
o_ship_city       oehead.ship_city%type;
o_ship_state      oehead.ship_state%type;
o_ship_zip_code   oehead.ship_zip_code%type;
o_ship_country    oehead.ship_country%type;
o_freight_desc    oehead.freight_desc%type;
o_order_date      date;  /* customer order date */
o_not_after_date  date;
o_not_before_date date;
o_hold_cd         oehead.hold_cd%type; /* not passed now */

o_line_no         backlog_detail.line_no%type;
o_item_no         backlog_detail.item_no%type;
o_description     backlog_detail.description%type;
o_finish_1        backlog_detail.finish_1%type;
o_strap_1         backlog_detail.strap_1%type; /* not passed now */
o_fabric_1        backlog_detail.fabric_1%type;
o_fabric_2        backlog_detail.fabric_2%type;
o_cush_style      backlog_detail.cush_style%type; /* not passed now */
o_finish_comment  backlog_detail.finish_comment%type;
o_fabric_comment  backlog_detail.fabric_comment%type;
o_side_mark       varchar2(40) := ' ';
o_qty_order       backlog_detail.qty_order%type;
o_cube_feet   backlog_detail.cube_feet%type;
o_cube_weight     backlog_detail.cube_weight%type;
o_bol_code        backlog_detail.bol_code%type;
o_bol_desc        backlog_detail.bol_desc%type;

oh_seq_no         oehead_comments.seq_no%type;
oh_comment_text   oehead_comments.comment_text%type;

ot_line_no        oe_trailer_comments.line_no%type;
ot_comm           oe_trailer_comments.comm %type;

ol_seq_no         oe_line_comments.seq_no%type;
o_oe_line_comment oe_line_comments.oe_line_comment%type;

l_count           numeric(10,5) := 0; /* commit counter */
l_document        VARCHAR2(6) := 'oeorf';
l_sort_key        VARCHAR2(50) := NULL ; /* not used */
l_doc_id_no       VARCHAR2(15) := NULL ; /* not used */
l_seq_no          NUMBER(5) := 1; /* continuous sequence */

/*
===============================================================
Cursors
===============================================================
*/
cursor get_oehead is
SELECT
oh.plant,
oh.order_no,
oh.order_suffix,
oh.schedule_no,
decode(oh.shipto_cd,'SAME',oh.cust_no,oh.shipto_cd),
nvl(oh.ship_name,' '),
nvl(oh.ship_addr1,' '),
nvl(oh.ship_addr2,' '),
nvl(oh.ship_addr3,' '),
nvl(oh.ship_city,' '),
oh.ship_state,
oh.ship_zip_code,
nvl(oh.ship_country,' '),
nvl(oh.freight_desc,' '),
oh.order_date,
oh.not_after_date,
oh.not_before_date,
nvl(oh.hold_cd,' ')
FROM
oehead oh, backlog_header bh
WHERE
bh.plant = '&i_plant' and
oh.order_no = bh.order_no and
oh.order_suffix = bh.order_suffix and
oh.plant = bh.plant and
('&i_schedule_no' = '!' or oh.schedule_no = '&i_schedule_no') and
oh.order_status = 'O'
ORDER BY
oh.order_no, oh.order_suffix;

cursor get_backlog_detail is
SELECT
line_no,
item_no,
nvl(description,' '),
nvl(finish_1,' '),
nvl(strap_1,' '),
nvl(fabric_1,' '),
nvl(fabric_2,' '),
nvl(cush_style,' '),
nvl(finish_comment,' '),
nvl(fabric_comment,' '),
qty_order,
cube_feet,
cube_weight,
bol_code,
nvl(bol_desc,' ')
FROM
backlog_detail bd
WHERE
bd.plant = o_plant and
bd.order_no = o_order_no and
bd.order_suffix = o_order_suffix
ORDER BY bd.line_no;

cursor get_oehead_comments is
SELECT
seq_no,
comment_text
FROM oehead_comments oc
WHERE
order_no = o_order_no and
order_suffix = o_order_suffix and
plant = o_plant and
print_flag = 'Y'
ORDER BY 1;

cursor get_trailer_comments is
SELECT
line_no,
comm
FROM
oe_trailer_comments
WHERE
order_no = o_order_no and
order_suffix = o_order_suffix and
plant = o_plant
ORDER BY 1;

cursor get_line_comments is
SELECT
seq_no,
oe_line_comment
FROM oe_line_comments
WHERE
plant = o_plant and
order_no = o_order_no and
order_suffix = o_order_suffix and
order_line_no = o_line_no
ORDER BY 1;

/*
===============================================================
In the beginning
===============================================================
*/
begin

begin
delete from temp_4_reports
where username = user
      and document = l_document;
commit; /* Remove old stuff */
end;
/*
===============================================================
Get header
===============================================================
*/
open get_oehead;
loop
fetch get_oehead into
o_plant, o_order_no, o_order_suffix, o_schedule_no, o_shipto_cd,
o_ship_name, o_ship_addr1, o_ship_addr2, o_ship_addr3, o_ship_city,
o_ship_state, o_ship_zip_code, o_ship_country, o_freight_desc,
o_order_date, o_not_after_date, o_not_before_date, o_hold_cd;
exit when get_oehead%notfound;

dbms_output.put_line('*** 'o_order_no' ***');

insert into temp_4_reports
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'HH'  rpad(o_plant,2)  rpad(o_order_no,10)  to_char(o_order_suffix,'90')
rpad(o_schedule_no,8)  rpad(o_shipto_cd,10)  rpad(o_ship_name,30)
rpad(o_ship_addr1,30)  rpad(o_ship_addr2,30)   rpad(o_ship_addr3,30)
rpad(o_ship_city,20)  rpad(o_ship_state,4)  rpad(o_ship_zip_code,12)
rpad(o_ship_country,25)  rpad(o_freight_desc,30)  to_char(SYSDATE,'YYYYMMDD')
to_char(o_order_date,'YYYYMMDD')  to_char(o_not_after_date,'YYYYMMDD')
to_char(o_not_before_date,'YYYYMMDD'));
l_seq_no := l_seq_no+1;

/*
===============================================================
Get header comments
===============================================================
*/
open get_oehead_comments;
loop
fetch get_oehead_comments into oh_seq_no, oh_comment_text;
exit when get_oehead_comments%notfound;

insert into temp_4_reports
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'HC'  rpad(o_plant,2)  rpad(o_order_no,10)  to_char(o_order_suffix,'90')
to_char(oh_seq_no,'999')  oh_comment_text);
l_seq_no := l_seq_no+1;

end loop; /* oe_oehead_comments */
close get_oehead_comments;

open get_trailer_comments;
loop
fetch get_trailer_comments into ot_line_no, ot_comm;
exit when get_trailer_comments%notfound;

insert into temp_4_reports
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'HC'  rpad(o_plant,2)  rpad(o_order_no,10)  to_char(o_order_suffix,'90')
to_char(ot_line_no,'999')  ot_comm);
l_seq_no := l_seq_no+1;

end loop; /* trailer_comments */
close get_trailer_comments;

/*
===============================================================
Get line items
===============================================================
*/
open get_backlog_detail;
loop
fetch get_backlog_detail into
o_line_no, o_item_no, o_description, o_finish_1, o_strap_1, o_fabric_1,
o_fabric_2, o_cush_style, o_finish_comment, o_fabric_comment, o_qty_order,
o_cube_feet, o_cube_weight, o_bol_code, o_bol_desc;
exit when get_backlog_detail%notfound;
-- o_side_mark

insert into temp_4_reports
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'DD'  rpad(o_plant,2)  rpad(o_order_no,10)  to_char(o_order_suffix,'90')
to_char(o_line_no,'999')  rpad(substr(o_item_no,1,20),20)
rpad(substr(o_description,1,40),40)  rpad(o_finish_1,5)
rpad(o_fabric_1,5)  rpad(o_fabric_2,5)  rpad(o_finish_comment,40)
rpad(o_fabric_comment,40)  rpad(o_side_mark,40)  to_char(o_qty_order,'9999999999990')
to_char(o_cube_feet,'9999999999990')  to_char(o_cube_weight,'9999999999990')
rpad(o_bol_code,4)  rpad(o_bol_desc,20) );
l_seq_no := l_seq_no+1;

/*
===============================================================
Get line comments
===============================================================
*/
open get_line_comments;
loop
fetch get_line_comments into ol_seq_no, o_oe_line_comment;
exit when get_line_comments%notfound;

insert into temp_4_reports
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'DC'  rpad(o_plant,2)  rpad(o_order_no,10)  to_char(o_order_suffix,'90')
to_char(o_line_no,'999')  to_char(ol_seq_no,'999')  o_oe_line_comment);
l_seq_no := l_seq_no+1;

end loop; /* line_comments */
close get_line_comments;
/*
===============================================================

===============================================================
*/
end loop; /* backlog_detail */
close get_backlog_detail;

l_count := l_count +1;
if mod(l_count,20) = 0 then
 commit;
 l_count := 0;
end if;

end loop; /* oehead */

close get_oehead;
commit;

end;
/
/*
===============================================================
Create file
===============================================================
*/
set heading off
set linesize 400
set feedback off
set pagesize 0
spool oeorf

SELECT text
FROM temp_4_reports
WHERE document = 'oeorf'
      and username = user
ORDER BY seq_no;

spool off

delete from temp_4_reports
where username = user
      and document = 'oeorf';

exit

upd_cost.sql: cost update fix

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: sample program to fix cost problem on order information.
Code Listing:

/* program: /users/common/sql/update_cost.sql [upd_cost.sql]
   date   : 09/18/96
   author : Robert Lawson
   purpose: Fix bug in oelsc that missed cost updates for backorders
*/
--set termout on
-- set echo on
set heading off
set linesize 132
set feedback off
set pagesize 60
/*
===============================================================
Report
===============================================================
*/
spool /users/common/oracle/upd_cost
SELECT
od.order_no,
od.order_suffix,
od.line_no,
od.UNIT_COST wrong_cost,
od0.unit_cost corrt_cost
FROM oedetl od0, oedetl od
WHERE
od.order_suffix <> 0 and
od0.plant = od.plant and
od0.order_no = od.order_no and
od0.order_suffix = 0 and
od0.line_no = od.line_no and
nvl(od0.unit_cost,0) <> nvl(od.unit_cost,0)
ORDER BY 1,2,3;
spool off

/*
===============================================================
Update backorder items
===============================================================
*/
-- set serveroutput on

DECLARE

o_plant    oedetl.plant%TYPE;
o_order_no   oedetl.order_no%TYPE;
o_order_suffix   oedetl.order_suffix%TYPE;
o_line_no  oedetl.line_no%TYPE;
o_unit_cost   oedetl.unit_cost%TYPE;
o_UNIT_LABOR_COST oedetl.unit_labor_cost%TYPE;
o_UNIT_MATL_COST  oedetl.unit_matl_cost%TYPE;
o_UNIT_OVHD_COST oedetl.unit_ovhd_cost%TYPE;
o_rowid                 ROWID;

n_unit_cost   oedetl.unit_cost%TYPE;
n_UNIT_LABOR_COST oedetl.unit_labor_cost%TYPE;
n_UNIT_MATL_COST  oedetl.unit_matl_cost%TYPE;
n_UNIT_OVHD_COST oedetl.unit_ovhd_cost%TYPE;

l_count           numeric(10,5) := 0; /* commit counter */

cursor oedetl_n is
SELECT
od.plant,
od.order_no,
od.order_suffix,
od.line_no,
nvl(od.UNIT_COST,0),
nvl(od.UNIT_LABOR_COST,0),
nvl(od.UNIT_MATL_COST,0),
nvl(od.UNIT_OVHD_COST,0),
rowid
FROM oedetl od
WHERE
od.order_suffix <> 0
ORDER BY 1,2,3;

cursor oedetl_0 is
SELECT
nvl(od.UNIT_COST,0),
nvl(od.UNIT_LABOR_COST,0),
nvl(od.UNIT_MATL_COST,0),
nvl(od.UNIT_OVHD_COST,0)
FROM oedetl od
WHERE
od.plant = o_plant and
od.order_no = o_order_no and
od.order_suffix = 0 and
od.line_no = o_line_no;

/*
===============================================================
Get backorder lines
===============================================================
*/
BEGIN
open oedetl_n;
loop
fetch oedetl_n into
o_plant, o_order_no, o_order_suffix, o_line_no, o_UNIT_COST, o_UNIT_LABOR_COST,
o_UNIT_MATL_COST, o_UNIT_OVHD_COST, o_rowid;
exit when oedetl_n%notfound;

open oedetl_0;
loop
fetch oedetl_0 into
n_UNIT_COST, n_UNIT_LABOR_COST, n_UNIT_MATL_COST, n_UNIT_OVHD_COST;
exit when oedetl_0%notfound;

if (n_unit_cost <> o_unit_cost or n_UNIT_LABOR_COST <> o_UNIT_LABOR_COST or
n_UNIT_MATL_COST <> o_UNIT_MATL_COST or n_UNIT_OVHD_COST <> o_UNIT_OVHD_COST)
then
  dbms_output.put_line(o_order_no' 'o_order_suffix' ' o_line_no' o: 'o_unit_cost' n: 'n_unit_cost);
  UPDATE oedetl SET
  UNIT_COST = n_unit_cost, UNIT_LABOR_COST = n_UNIT_LABOR_COST,
  UNIT_MATL_COST = n_UNIT_MATL_COST, UNIT_OVHD_COST = n_UNIT_OVHD_COST
  WHERE rowid = o_rowid;

  l_count := l_count +1;
  if mod(l_count,20) = 0 then
    commit;
    l_count := 0;
  end if;
end if;
end loop; /* oedetl_0 */
close oedetl_0;

end loop; /* oedetl_n */
close oedetl_n;
commit;

END;
/
exit

db_prof.sql: report database triggers

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: Report database triggers from JIT ERP system.
Code Listing:

/*
program: db_prof.sql
purpose: profile of database
date   : 06-30-96 (a blue moon today)
author : robert lawson
changes:

*/
set wrap off
set linesize 300
set pagesize 50000
set longc 10000
set echo on
set termout on
-- set pause on

/*
-- Triggers
- all_triggers: TRIGGER_BODY, TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE
- all_trigger_cols:
*/
SELECT
-- substr(ao.owner,1,12) owner,
substr(ao.object_name,1,30) trigger_name,
ao.created,
ao.status,
substr(ag.table_name,1,20) table_name
FROM all_triggers ag, all_objects ao
WHERE
ao.object_type = 'TRIGGER' and
ao.owner <> 'SYS' and
ag.trigger_name = ao.object_name
ORDER BY 1;

/*
- Procedures, not in packages (odd balls)

*/
SELECT
substr(ao.owner,1,12) owner,
substr(ao.object_name,1,30) name,
ao.created,
ao.status
FROM all_objects ao
WHERE
ao.object_type = 'PROCEDURE'
-- ao.owner <> 'SYS'
ORDER BY 1, 2;

oecu: utility to reconfigure sale orders

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This code was run nightly as batch job, or it could be run on request. This is an example of using procedures, and calling sy_error.log error log. This was used with JIT ERP system.
Code Listing:

set termout off
rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1999/05/27 22:15:19 $
rem $Source: /appl/jit/RCS/oe/src/oecu.sql,v $
rem $Revision: 1.16 $
/*
07/08/96 Robert Lawson  Allow that ? options to reconfigure
05/29/97 Robert Lawson  Added error trapping
05/27/99 Robert Lawson  Chged main cursor to use Backlog_Header
*/
set termout on
accept i_plant prompt "Enter Plant ('!' for All): "

accept i_order_no prompt "Enter Order No. ('!' for All): "

accept i_dist prompt "Distribution: "

set echo on
set termout on
set verify on
set feedback on
/*
  ==========================================================
  Procedure: rebuild_configuration
  ==========================================================
*/
declare
       v_item_model_flag    item_master.model_flag%type;
       v_model_flag_for_wo  oedetl.model_flag_for_wo%type;
       v_item_no            oedetl.item_no%type;
       v_finish_1           oedetl.finish_1%type;
       v_finish_2           oedetl.finish_2%type;
       v_fabric_1           oedetl.fabric_1%type;
       v_fabric_2           oedetl.fabric_2%type;
       v_strap_1            oedetl.strap_1%type;
       v_strap_2            oedetl.strap_2%type;
       v_cush_style         oedetl.cush_style%type;
       v_order_no           oedetl.order_no%type;
       v_order_suffix       oedetl.order_suffix%type;
       v_line_no            oedetl.line_no%type;
       v_plant              oedetl.plant%type;
       v_schedule_no        oehead.schedule_no%type;
       v_plant_change_ok    varchar2(1);
       v_dummy              varchar2(1);
       v_exception          varchar2(55);
       v_rowid              rowid;
       l_program            debug_table.program%type := 'oecu.sql';

l_document                       VARCHAR2(6)        := 'oecu';
l_sort_key                       VARCHAR2(50);
l_doc_id_no                      VARCHAR2(15) := NULL;
l_seq_no                         NUMBER(5) := 1; /* continuous sequence */
l_cntr                           NUMBER(2) := 0; /* physical lines printed */

/*
  ==========================================================
  Procedure: rebuild_configuration
  ==========================================================
*/
      Procedure rebuild_configuration IS
      BEGIN
      declare

cursor jit_oedetl is
SELECT
od.item_no,
im.model_flag,
finish_1,
finish_2,
fabric_1,
fabric_2,
strap_1,
strap_2,
cush_style,
od.order_no,
od.order_suffix,
od.plant,
od.line_no,
od.rowid
FROM item_master im, oedetl od, backlog_header bh
WHERE
(bh.plant = '&i_plant' or '&i_plant' = '!') and
(bh.order_no = '&i_order_no' or '&i_order_no' = '!') and
od.order_no = bh.order_no and
od.order_suffix = bh.order_suffix and
od.plant = bh.plant and
(nvl(od.qty_order,0) - nvl(od.qty_shipped,0) - nvl(od.qty_bo,0) > 0) and
im.item_no = od.item_no and
im.plant = od.plant and
nvl(im.model_flag,'N') = 'Y';

CURSOR get_head IS
SELECT
nvl(schedule_no,' ')
FROM oehead
WHERE
order_no = v_order_no and
order_suffix = v_order_suffix and
plant = v_plant;

/*
  ==========================================================
  Procedure: exception_proc
  ==========================================================
*/
       Procedure exception_proc is
       BEGIN
       if nvl(v_plant_change_ok, ' ') = 'N' then
         open get_head;
         FETCH get_head into v_schedule_no;
         close get_head;

         l_sort_key := v_plant  ' '  v_order_no  to_char(v_order_suffix)
         to_char(v_line_no)   substr(v_item_no,1,15);

         insert into temp_4_reports
         (document, username, sort_key, doc_id_no, seq_no, text)
         values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
         v_plant  ' '  rpad(v_order_no  '.'  to_char(v_order_suffix)  '-'  to_char(v_line_no),15)
           ' '  rpad(substr(v_item_no,1,15),15)  ' '  rpad(v_schedule_no,8)
         ' '  v_exception);
         l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
         -- commit;
       end if;
       EXCEPTION
       WHEN OTHERS THEN
         sy_error.log(l_program,10,'SO:'  v_order_no  '.'  v_order_suffix  '-'  v_line_no);
       -- COMMIT;
       END;
/*
  ==========================================================
  Main outer block
  ==========================================================
*/
       BEGIN -- outer
-- debug.reset(l_program);
-- debug.debug(l_program,'**begin');
         open jit_oedetl;
         LOOP
           v_plant_change_ok := 'Y';
           v_exception := null;
           FETCH jit_oedetl into
           v_item_no, v_model_flag_for_wo, v_finish_1, v_finish_2,
           v_fabric_1, v_fabric_2, v_strap_1, v_strap_2, v_cush_style,
           v_order_no, v_order_suffix, v_plant, v_line_no, v_rowid;
    EXIT when jit_oedetl%notfound;
/*
  ==========================================================
  Inner: check for problems with options
  ==========================================================
*/
           BEGIN -- inner
           -- commit;
           v_plant_change_ok := 'Y';
           v_exception := null;
           IF nvl(v_model_flag_for_wo,'N') = 'Y' then
            IF v_finish_1 is not null and nvl(v_finish_1,'*') <> '?' THEN
            BEGIN  -- finish_1
             -- Check for option finish_1 in model_bom table
             SELECT 'x'
             INTO v_dummy
             FROM model_bom mb
             WHERE mb.parent_no = v_item_no
              and  mb.parent_plant = v_plant
              and  mb.required_option_group = 'FIN'
              and  (
                     ( nvl(mb.component_type, ' ') not in ('I','Z')
                       and mb.short_name =  v_finish_1
                       and exists(select x1.item_no from item_master x1
                                  where x1.plant = v_plant
                                    and x1.item_no = mb.component_no)
                     )
                     OR
                     ( nvl(mb.component_type, ' ') in ('I','Z')
                       and v_finish_1 in
                          (select i.short_name from model_bom i
                           where i.parent_plant = v_plant
                             and i.parent_no = mb.component_no
                             and i.required_option_group = 'FIN'
                             and exists
                                (select x.item_no from item_master x
                                 where x.plant = v_plant
                                   and x.item_no = i.component_no)
                          )
                     )
                   );
             Exception
               When no_data_found then
                 v_plant_change_ok := 'N';
                  v_exception :=
                 'FINISH_1 '  v_finish_1
   ' not exists/Comp inactive in Pl:' v_plant;
                 exception_proc;
               When too_many_rows then null;
               When others then null;
            END; -- finish_1
            END IF; -- finish_1
            IF v_finish_2 is not null and nvl(v_finish_2,'*') <> '?' THEN
            BEGIN -- finish_2
             -- Check for option finish_2 in model_bom table
             SELECT 'x'
             INTO v_dummy
             FROM model_bom mb
             WHERE mb.parent_no = v_item_no
              and  mb.parent_plant = v_plant
              and  mb.required_option_group = 'FIN2'
              and  (
                     ( nvl(mb.component_type, ' ') not in ('I','Z')
                       and mb.short_name =  v_finish_2
                       and exists(select x1.item_no from item_master x1
                                  where x1.plant = v_plant
                                    and x1.item_no = mb.component_no)
                     )
                     OR
                     ( nvl(mb.component_type, ' ') in ('I','Z')
                       and v_finish_2 in
                          (select i.short_name from model_bom i
                           where i.parent_plant = v_plant
                             and i.parent_no = mb.component_no
                             and i.required_option_group = 'FIN2'
                             and exists
                                (select x.item_no from item_master x
                                 where x.plant = v_plant
                                   and x.item_no = i.component_no)
                          )
                     )
                   );
             Exception
               When no_data_found then
                 v_plant_change_ok := 'N';
                  v_exception :=
                 'FINISH_2 '  v_finish_2
   ' not exists/Comp inactive in Pl:' v_plant;
                 exception_proc;
               When too_many_rows then null;
               When others then null;
            END; -- finish_2
            END IF; -- finish_2
            IF v_strap_1 is not null and nvl(v_strap_1,'*') <> '?' THEN
            BEGIN -- strap_1
             -- Check for option strap_1 in model_bom table
             SELECT 'x'
             INTO v_dummy
             FROM model_bom mb
             WHERE mb.parent_no = v_item_no
              and  mb.parent_plant = v_plant
              and  mb.required_option_group = 'STRP'
              and  (
                     ( nvl(mb.component_type, ' ') not in ('I','Z')
                       and mb.short_name =  v_strap_1
                       and exists(select x1.item_no from item_master x1
                                  where x1.plant = v_plant
                                    and x1.item_no = mb.component_no)
                     )
                     OR
                     ( nvl(mb.component_type, ' ') in ('I','Z')
                       and v_strap_1 in
                          (select i.short_name from model_bom i
                           where i.parent_plant = v_plant
                             and i.parent_no = mb.component_no
                             and i.required_option_group = 'STRP'
                             and exists
                                (select x.item_no from item_master x
                                 where x.plant = v_plant
                                   and x.item_no = i.component_no)
                          )
                     )
                   );
             Exception
               When no_data_found then
                 v_plant_change_ok := 'N';
                  v_exception :=
                 'STRAP_1 '  v_strap_1
   ' not exists/Comp inactive in Pl:' v_plant;
                 exception_proc;
               When too_many_rows then null;
               When others then null;
            END; -- strap_1
            END IF; -- strap_1
            IF v_strap_2 is not null and nvl(v_strap_2,'*') <> '?' THEN
            BEGIN -- strap_2
             -- Check for option strap_2 in model_bom table
             SELECT 'x'
             INTO v_dummy
             FROM model_bom mb
             WHERE mb.parent_no = v_item_no
              and  mb.parent_plant = v_plant
              and  mb.required_option_group = 'STR2'
              and  (
                     ( nvl(mb.component_type, ' ') not in ('I','Z')
                       and mb.short_name =  v_strap_2
                       and exists(select x1.item_no from item_master x1
                                  where x1.plant = v_plant
                                    and x1.item_no = mb.component_no)
                     )
                     OR
                     ( nvl(mb.component_type, ' ') in ('I','Z')
                       and v_strap_2 in
                          (select i.short_name from model_bom i
                           where i.parent_plant = v_plant
                             and i.parent_no = mb.component_no
                             and i.required_option_group = 'STR2'
                             and exists
                                (select x.item_no from item_master x
                                 where x.plant = v_plant
                                   and x.item_no = i.component_no)
                          )
                     )
                   );
             Exception
               When no_data_found then
                 v_plant_change_ok := 'N';
                  v_exception :=
                 'STRAP_2 '  v_strap_2
   ' not exists/Comp inactive in Pl:' v_plant;
                 exception_proc;
               When too_many_rows then null;
               When others then null;
            END; -- strap_2
            END IF; -- strap_2
            IF v_fabric_1 is not null and nvl(v_fabric_1,'*') <> '?' THEN
            BEGIN -- fabric_1
             -- Check for option fabric_1 in model_bom table
             SELECT 'x'
             INTO v_dummy
             FROM model_bom mb
             WHERE mb.parent_no = v_item_no
              and  mb.parent_plant = v_plant
              and  mb.required_option_group = 'FAB'
              and  (
                     ( nvl(mb.component_type, ' ') not in ('I','Z')
                       and mb.short_name =  v_fabric_1
                       and exists(select x1.item_no from item_master x1
                                  where x1.plant = v_plant
                                    and x1.item_no = mb.component_no)
                     )
                     OR
                     ( nvl(mb.component_type, ' ') in ('I','Z')
                       and v_fabric_1 in
                          (select i.short_name from model_bom i
                           where i.parent_plant = v_plant
                             and i.parent_no = mb.component_no
                             and i.required_option_group = 'FAB'
                             and exists
                                (select x.item_no from item_master x
                                 where x.plant = v_plant
                                   and x.item_no = i.component_no)
                          )
                     )
                   );
             Exception
               When no_data_found then
                 v_plant_change_ok := 'N';
                  v_exception :=
                 'FABRIC_1 '  v_fabric_1
   ' not exists/Comp inactive in Pl:' v_plant;
                 exception_proc;
               When too_many_rows then null;
               When others then null;
            END; -- fabric_1
            END IF;-- fabric_1
            IF v_fabric_2 is not null and nvl(v_fabric_2,'*') <> '?' THEN
            BEGIN -- fabric_2
             -- Check for option fabric_2 in model_bom table
             SELECT 'x'
             INTO v_dummy
             FROM model_bom mb
             WHERE mb.parent_no = v_item_no
              and  mb.parent_plant = v_plant
              and  mb.required_option_group = 'FAB2'
              and  (
                     ( nvl(mb.component_type, ' ') not in ('I','Z')
                       and mb.short_name =  v_fabric_2
                       and exists(select x1.item_no from item_master x1
                                  where x1.plant = v_plant
                                    and x1.item_no = mb.component_no)
                     )
                     OR
                     ( nvl(mb.component_type, ' ') in ('I','Z')
                       and v_fabric_2 in
                          (select i.short_name from model_bom i
                           where i.parent_plant = v_plant
                             and i.parent_no = mb.component_no
                             and i.required_option_group = 'FAB2'
                             and exists
                                (select x.item_no from item_master x
                                 where x.plant = v_plant
                                   and x.item_no = i.component_no)
                          )
                     )
                   );
             Exception
               When no_data_found then
                 v_plant_change_ok := 'N';
                  v_exception :=
                 'FABRIC_2 '  v_fabric_2
   ' not exists/Comp inactive in Pl:' v_plant;
                 exception_proc;
               When too_many_rows then null;
               When others then null;
            END; -- fabric_2
            END IF; -- fabric_2
            IF v_cush_style is not null and nvl(v_cush_style,'*') <> '?' THEN
            BEGIN -- cush_style
             -- Check for option cushion in model_bom table
             SELECT 'x'
             INTO v_dummy
             FROM model_bom mb
             WHERE mb.parent_no = v_item_no
              and  mb.parent_plant = v_plant
              and  mb.required_option_group = 'CUSH'
              and  (
                     ( nvl(mb.component_type, ' ') not in ('I','Z')
                       and mb.short_name =  v_cush_style
                       and exists(select x1.item_no from item_master x1
                                  where x1.plant = v_plant
                                    and x1.item_no = mb.component_no)
                     )
                     OR
                     ( nvl(mb.component_type, ' ') in ('I','Z')
                       and v_cush_style in
                          (select i.short_name from model_bom i
                           where i.parent_plant = v_plant
                             and i.parent_no = mb.component_no
                             and i.required_option_group = 'CUSH'
                             and exists
                                (select x.item_no from item_master x
                                 where x.plant = v_plant
                                   and x.item_no = i.component_no)
                          )
                     )
                   );
             Exception
               When no_data_found then
                 v_plant_change_ok := 'N';
                  v_exception :=
               'CUSH_STYLE '  v_cush_style
        ' not exists/Comp inactive in Pl:' v_plant;
                 exception_proc;
               When too_many_rows then null;
               When others then null;
            END; -- cush_style
            END IF; -- cush_style

/*
  ==========================================================
  Inner: update oedetl to fire OEDETL$AUR trigger
  ==========================================================
*/
           if nvl(v_plant_change_ok, 'N') = 'N' then
              BEGIN -- oedetl_update
          
              NULL;
              /* update oedetl
              set finish_1 = v_finish_1
                , finish_2 = v_finish_2
                , fabric_1 = v_fabric_1
                , fabric_2 = v_fabric_2
                , strap_1  = v_strap_1
                , strap_2  = v_strap_2
                , cush_style = v_cush_style
              where rowid = v_rowid;*/
              EXCEPTION
              WHEN OTHERS THEN
                v_exception := '** Failure to Configure **';
                exception_proc;
              END; -- oedetl_update
           -- COMMIT; -- Force writes for rollback error
           end if; -- v_plant_change_ok
           -- syp$post_no_msg;
           v_plant_change_ok := 'Y' ;
           v_exception := null;
           END IF; -- item is model or not if stmt.

           EXCEPTION
           WHEN OTHERS THEN
             v_exception := '** Failure to Configure **';
             exception_proc;
           END;  -- inner
       -- COMMIT;
       END LOOP; -- jit_oedetl



       EXCEPTION
       WHEN OTHERS THEN
         sy_error.log(l_program,100,'SO:'  v_order_no  '.'  v_order_suffix  '-'  v_line_no);
         v_exception := '** Failure to Configure **';
         exception_proc;
--       COMMIT;
       END; -- Outer

    EXCEPTION
    WHEN OTHERS THEN
      sy_error.log(l_program,110,'SO:'  v_order_no  '.'  v_order_suffix  '-'  v_line_no);
--      v_exception := '** Failure to Configure **';
--      exception_proc;
    -- COMMIT;
    END; --  Procedure rebuild_configuration
/*
  ==========================================================
  Actual main program
  ==========================================================
*/
BEGIN
  rebuild_configuration;
EXCEPTION
WHEN OTHERS THEN
  sy_error.log(l_program,999,'**SO: '  v_order_no  '.'  v_order_suffix  '-'  v_line_no);
-- COMMIT;
end; /* Outer */
/
SELECT to_char(sysdate,'MM/DD/YY HH:MI AM') today FROM dual;
/*
  ==========================================================
  update
  ==========================================================
*/

DECLARE
l_key varchar2(20);

CURSOR detail_cursor IS
select od.rowid row_id,
(od.order_no  '.'  od.order_suffix  '-'  od.line_no) SO
from item_master im, oedetl od, backlog_header bh
WHERE
(bh.plant = '&i_plant' or '&i_plant' = '!') AND
(bh.order_no = '&i_order_no' or '&i_order_no' = '!') AND
od.order_no = bh.order_no AND
od.order_suffix = bh.order_suffix AND
od.plant = bh.plant AND
im.item_no = od.item_no AND
im.plant = od.plant AND
im.model_flag = 'Y';

BEGIN
FOR r_detail IN detail_cursor LOOP
  l_key := r_detail.SO;
  update oedetl set
  finish_1 = finish_1  , finish_2 = finish_2
  where rowid = r_detail.row_id;

  COMMIT;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
   sy_error.log('oecu',998,'Failed on: '  l_key);
END;
/
SELECT to_char(sysdate,'MM/DD/YY HH:MI AM') today FROM dual;
/*
  ==========================================================
  Reportin' Time Again
  ==========================================================
*/
set termout off
set echo off
set verify off
set feedback off

COLUMN today noprint new_value todayvar
SELECT
to_char(sysdate,'MM/DD/YY HH:MI AM') today
FROM dual;

set wrap off
set space 1
set pagesize 60
set linesize 132
set heading off
spool oecu

ttitle left 'oecu' -
       center 'Reconfigure Utility Exceptions' -
       right 'Page: ' format 99 sql.pno s -
       left '  ' todayvar -
       center '==============================' -
       right 'Dist: '&i_dist s -
       center 'Sorted by: Plant, Order/Sfx, Line No' s s -
'Pl Order.Sfx-L#    Item            Sched    Message' s -
'-- --------------- --------------- -------- --------------------------------------------------'

select '*** No Entries Found ***'
from   dual
where  not exists
       (select 'x' from temp_4_reports
        where username = user
        and document = 'oecu');

select text
from temp_4_reports
where document = 'oecu'
      and username = user
order by sort_key, doc_id_no, seq_no;

ttitle off
select '** End Of Report **'
from dual;

spool off
/*
===============================================================
Clean up
===============================================================
*/
delete from temp_4_reports
where username = user
      and document = 'oecu';

exit

aropu.sql: create open AR files

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This script was run nightly via CRON to summarize Accounts Receivable information from JIT ERP database.
Code Listing:

rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1996/05/24 22:22:44 $
rem $Source: /appl/jit/RCS/ar/src/aropu.sql,v $
rem $Revision: 1.1 $
/* program aropu.sql
   date  05-16-96
   author robert lawson

purpose: create open AR (accounts receivable) files.

Notes:
1. How to know create date of table?

*/

-- set echo on
set termout on
set verify on
accept i_customer prompt "Customer (! = all): "

accept i_date prompt "Age Date MM/DD/YY ( = sysdate): "
set termout off

declare
/*
===============================================================
Variable declared
===============================================================
*/
o_CUST_NO                    arinvsum.cust_no%type;
o_INVNO                      arinvsum.invno%type;
o_INVOICE_TYPE               arinvsum.invoice_type%type;
o_ORIG_AMT                   arinvsum.orig_amt%type;
open_balance                 arinvsum.orig_amt%type;
days_current                 arinvsum.orig_amt%type;
days_01_30                   arinvsum.orig_amt%type;
days_31_60                   arinvsum.orig_amt%type;
days_61_90                   arinvsum.orig_amt%type;
days_91pls                   arinvsum.orig_amt%type;
o_PLANT                      arinvsum.plant%type;
o_ORDER_NO                   arinvsum.order_no%type;
o_ORDER_SUFFIX               arinvsum.order_suffix%type;
o_REFERENCE_ID               arinvsum.reference_id%type;
o_INVDATE                    arinvsum.invdate%type;
o_INV_DUE_DATE               arinvsum.inv_due_date%type;
o_DATE_LAST_APPLIED          arinvsum.date_last_applied%type;
o_TERMS_CD                   arinvsum.terms_cd%type;
o_TERMS_DESC                 arinvsum.terms_desc%type;
o_SALES_ID1             oehead.sales_id1%type;
o_CUST_NAME                  oehead.cust_name%type;
o_CUST_CITY                  oehead.cust_city%type;
o_CUST_STATE                 oehead.cust_state%type;
o_CUST_ZIP_CODE              oehead.cust_zip_code%type;
o_COUNTRY                    oehead.country%type;
o_sls_name                   slsmaster.sls_name%type;
segment                      varchar2(5); /* market segment */
club                      varchar2(5);/* retail club level */
bucket                       numeric(2); /* 0=cur, 1=30, 2=60, 3=90, 4=90+ */
b_date                       date; /* bucket date */

o_open_balance               ar_summary.open_balance%type;
o_days_current               ar_summary.days_current%type;
o_days_01_30                 ar_summary.days_01_30%type;
o_days_31_60                 ar_summary.days_31_60%type;
o_days_61_90                 ar_summary.days_61_90%type;
o_days_91pls                 ar_summary.days_91pls%type;

l_count                      numeric(10,5) := 0; /* commit counter */

/*
===============================================================
Cursors
===============================================================
*/
cursor get_arinvsum is
SELECT
ar.cust_no,
ar.invno,
ar.invoice_type,
nvl(ar.orig_amt,0),
(nvl(ar.orig_amt,0) + nvl(ar.totalpay,0) + nvl(ar.otheramt,0) +
 nvl(ar.amt_xfer,0) + nvl(ar.adjustments,0) + nvl(ar.disc_taken,0)),
ar.plant,
ar.order_no,
ar.order_suffix,
ar.reference_id,
ar.invdate,
ar.inv_due_date,
ar.date_last_applied,
ar.terms_cd,
ar.terms_desc,
oh.sales_id1,
oh.cust_name,
oh.cust_city,
oh.cust_state,
oh.cust_zip_code,
oh.country,
sm.sls_name,
decode(cm.family_cd,'CONT','CONT','DESN','DESN','RETL'),
decode(cm.family_cd,'CONT',null,'DESN',null,'RETL',null,cm.family_cd)
FROM
slsmaster sm, cm_accts cm, oehead oh, arinvsum ar
WHERE
('&i_customer' = '!' or ar.cust_no = upper('&i_customer')) and
(nvl(ar.orig_amt,0) + nvl(ar.totalpay,0) + nvl(ar.otheramt,0) +
 nvl(ar.amt_xfer,0) + nvl(ar.adjustments,0) + nvl(ar.disc_taken,0) <> 0) and
oh.invno(+) = ar.invno and
cm.cust_no(+) = ar.cust_no and
sm.slsman(+) = oh.sales_id1;

cursor get_ar is
SELECT
cust_no,
sum(open_balance),
sum(days_current),
sum(days_01_30),
sum(days_31_60),
sum(days_61_90),
sum(days_91pls)
FROM ar_header
GROUP BY cust_no;

/*
===============================================================
In the beginning
===============================================================
*/
begin

-- truncate table ar_header;
delete from ar_header;
commit;
-- truncate table ar_summary;
delete from ar_summary;
commit;

/*
===============================================================
Get header
===============================================================
*/
open get_arinvsum;
loop
fetch get_arinvsum into
o_cust_no, o_invno, o_invoice_type, o_orig_amt, open_balance, o_plant,
o_order_no, o_order_suffix, o_reference_id, o_invdate, o_inv_due_date, o_date_last_applied,
o_terms_cd, o_terms_desc, o_sales_id1, o_cust_name, o_cust_city, o_cust_state,
o_cust_zip_code, o_country, o_sls_name, segment, club ;
exit when get_arinvsum%notfound;

days_current := 0; days_01_30 := 0; days_31_60 := 0; days_61_90 := 0; days_91pls := 0;

if '&i_date' is null then
 b_date := sysdate;
else
 b_date := to_date('&i_date','MM/DD/YY');
end if;
bucket := least(4,ceil(greatest(0,(b_date - o_INV_DUE_DATE))/30));

if    bucket = 0 then
  days_current :=  days_current + open_balance;
elsif bucket = 1 then
  days_01_30 :=  days_01_30 + open_balance;
elsif bucket = 2 then
  days_31_60  := days_31_60 + open_balance;
elsif bucket = 3 then
  days_61_90  := days_61_90 + open_balance;
else /* 4 */
  days_91pls :=  days_91pls + open_balance;
end if;
/*
===============================================================
Insert rows
===============================================================
*/
INSERT INTO ar_header
(CUST_NO, INVNO, INVOICE_TYPE, ORIG_AMT, open_balance, days_current,
days_01_30, days_31_60, days_61_90, days_91pls, PLANT,
ORDER_NO, ORDER_SUFFIX, REFERENCE_ID, INVDATE, INV_DUE_DATE,
DATE_LAST_APPLIED, TERMS_CD, TERMS_DESC, SALES_ID1,
CUST_NAME, CUST_CITY, CUST_STATE, CUST_ZIP_CODE ,COUNTRY,
SLS_NAME, segment,  club )
VALUES
(o_CUST_NO, o_INVNO, o_INVOICE_TYPE, o_ORIG_AMT, open_balance, days_current,
days_01_30, days_31_60, days_61_90, days_91pls, o_PLANT,
o_ORDER_NO, o_ORDER_SUFFIX, o_REFERENCE_ID, o_INVDATE, o_INV_DUE_DATE,
o_DATE_LAST_APPLIED, o_TERMS_CD, o_TERMS_DESC, o_SALES_ID1,
o_CUST_NAME, o_CUST_CITY, o_CUST_STATE, o_CUST_ZIP_CODE, o_COUNTRY,
o_SLS_NAME, segment,  club);

l_count := l_count + 1;
if mod(l_count,20) = 0 then
 commit;
 l_count := 0;
end if;

end loop; /* arinvsum */
close get_arinvsum;
commit;
/*
===============================================================
Summary tables
===============================================================
*/
open get_ar;
loop
fetch get_ar into
o_cust_no, o_open_balance, o_days_current, o_days_01_30, o_days_31_60,
o_days_61_90, o_days_91pls;
exit when get_ar%notfound;

INSERT INTO ar_summary
(cust_no, open_balance, days_current, days_01_30, days_31_60, days_61_90, days_91pls)
VALUES
(o_cust_no, o_open_balance, o_days_current, o_days_01_30, o_days_31_60,
o_days_61_90, o_days_91pls);

end loop; /* ar */
close get_ar;
/*
===============================================================
El Fin
===============================================================
*/
commit;
end;
/
exit