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

sy_error.log: application log procedure

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This procedure is used to write appilcation errors to event log.
Sample Use:
close get_backlog;
EXCEPTIONWHEN OTHERS THEN
sy_error.log(l_program,165,'SO: ' o_order_no '.' o_order_suffix '-' o_line_no);end;
Code Listing:

rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1997/06/12 15:56:36 $
rem $Source: /appl/jit/RCS/ddl/table_s/sy_error.sql,v $
rem $Revision: 1.3 $
set echo on
CREATE OR REPLACE PACKAGE sy_error AS
/*  File   : sy_error.sql
    Author : robert lawson/05-29-1997
    Purpose: manage program event log
*/

PROCEDURE log (p_program IN VARCHAR2, p_location NUMBER, p_message IN VARCHAR2);
END sy_error;
/
CREATE OR REPLACE PACKAGE BODY sy_error AS

tot_log NUMBER :=  0;
max_log NUMBER := 10;
/*
procedure log
p_program  A10 Passed  Program name
p_location NUM  Passed  Location in code (s/b sequence by 10, last is 999)
p_message  A200 Passed  Message of error, should have values
*/

PROCEDURE log (p_program IN VARCHAR2, p_location NUMBER, p_message IN VARCHAR2) IS
  err_num  NUMBER;
  err_msg  CHAR(100);
  l_message VARCHAR2(200);
BEGIN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
if tot_log 

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

upd_indx.sql: update table indexes

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This is sample table index script used first in test instance, then in production, from JIT ERP system. This script matched indexes to data use by company.
Code Listing:

/* program: upd_indx.sql
   date   : 08/13/96
   author : Robert Lawson
   purpose: Mass update of indexes
*/
set termout on
set echo on
-- set heading off
-- set linesize 132
-- set feedback off
-- set pagesize 60
/*
-- /appl/jit/src/CUST302/ddl/table_q/alt_quote_model_dtl.sql
-- redoes this index with line_no
DROP INDEX QUOTE_MODEL_DETAIL_INDX1;
CREATE INDEX QUOTE_MODEL_DETAIL_INDX1
 ON QUOTE_MODEL_DETAIL (QUOTE_NO, OEDETL_LINE_NO, plant)
 tablespace JITINDX
 storage ( initial     5M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );
*/
-- new index
DROP INDEX QUOTE_MODEL_DETAIL_IDX3;
CREATE INDEX QUOTE_MODEL_DETAIL_IDX3
 ON QUOTE_MODEL_DETAIL (parent_no, plant)
 tablespace JITINDX
 storage ( initial      3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_b/alt_bom_log.sql (new)
-- only index! bmli
DROP INDEX BOM_LOG_IDX1;
CREATE INDEX BOM_LOG_IDX1
 ON BILL_OF_MATERIAL_LOG (parent_no, parent_plant)
 tablespace JITINDX
 storage ( initial      3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_b/alt_blownup_bill.sql (new)
-- only index!
DROP INDEX BLOWNUP_BILL_IDX1;
CREATE INDEX BLOWNUP_BILL_IDX1
 ON BLOWNUP_BILL (plant , order_no, component_no)
 tablespace JITINDX
 storage ( initial        3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_line_comm.sql (new)
-- only index!
DROP INDEX PO_LINE_COMM_IDX1;
CREATE INDEX PO_LINE_COMM_IDX1
 ON PO_LINE_COMM (plant , po_no, line_no, comment_seq)
 tablespace JITINDX
 storage ( initial       1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_blanket.sql (new)
-- added line to index
DROP INDEX PO_BLANKET_IDX;
CREATE INDEX PO_BLANKET_IDX
 ON PO_BLANKET (plant , po_no, line_no)
 tablespace JITINDX
 storage ( initial       1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_o/alter_oesummary.sql
-- added index for customer
DROP INDEX OESUMMARY_IDX1;
CREATE INDEX OESUMMARY_IDX1
 ON OESUMMARY (cust_no, order_no, order_suffix)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_o/alt_oe_entry_register.sql (new)
-- added index for customer
DROP INDEX OE_ENTRY_REG_IDX1;
CREATE INDEX OE_ENTRY_REG_IDX1
 ON OE_ENTRY_REGISTER (order_no, order_suffix, plant)
 tablespace JITINDX
 storage ( initial        1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_r/alt_requisition.sql (new)
-- added index for vendor
DROP INDEX REQUISITION_IDX4;
CREATE INDEX REQUISITION_IDX4
 ON REQUISITION (plant, vendno, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for vendor
DROP INDEX REQUISITION_IDX5;
CREATE INDEX REQUISITION_IDX5
 ON REQUISITION (plant, requested_by, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for buyer
DROP INDEX REQUISITION_IDX6;
CREATE INDEX REQUISITION_IDX6
 ON REQUISITION (plant, buyer_code, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_r/alt_req_header.sql (new)

-- added index for requestor
DROP INDEX REQ_HEADER_IDX2;
CREATE INDEX REQ_HEADER_IDX2
 ON REQ_HEADER (plant, requested_by, req_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_header.sql (new)
-- added index for vendor
DROP INDEX PO_HEADER_IDX2;
CREATE INDEX PO_HEADER_IDX2
 ON PO_HEADER (plant, vendno, req_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for requestor
DROP INDEX PO_HEADER_IDX3;
CREATE INDEX PO_HEADER_IDX3
 ON PO_HEADER (plant, buyer_code, req_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_c/alt_custcntrcts.sql (new)
-- added customer
DROP INDEX CUSTCNTRCTS_IDX1;
CREATE INDEX CUSTCNTRCTS_IDX1
 ON CUSTCNTRCTS (cust_no, item_no, plant)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_a/alt_ar_cia_invno.sql (new)
-- added index
DROP INDEX AR_CIA_INVNO_IDX1;
CREATE INDEX AR_CIA_INVNO_IDX1
 ON AR_CIA_INVNO (org_unit_id, order_no)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index
DROP INDEX AR_CIA_INVNO_IDX2;
CREATE INDEX AR_CIA_INVNO_IDX2
 ON AR_CIA_INVNO (org_unit_id, cia_invno)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

upd_indx.sql: update indexes

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: Sample script to update database indexes. This would be used in test instance and then ultimately in production.
Code Listing:

/* program: upd_indx.sql
   date   : 08/13/96
   author : Robert Lawson
   purpose: Mass update of indexes
*/
set termout on
set echo on
-- set heading off
-- set linesize 132
-- set feedback off
-- set pagesize 60
/*
-- /appl/jit/src/CUST302/ddl/table_q/alt_quote_model_dtl.sql
-- redoes this index with line_no
DROP INDEX QUOTE_MODEL_DETAIL_INDX1;
CREATE INDEX QUOTE_MODEL_DETAIL_INDX1
 ON QUOTE_MODEL_DETAIL (QUOTE_NO, OEDETL_LINE_NO, plant)
 tablespace JITINDX
 storage ( initial     5M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );
*/
-- new index
DROP INDEX QUOTE_MODEL_DETAIL_IDX3;
CREATE INDEX QUOTE_MODEL_DETAIL_IDX3
 ON QUOTE_MODEL_DETAIL (parent_no, plant)
 tablespace JITINDX
 storage ( initial      3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_b/alt_bom_log.sql (new)
-- only index! bmli
DROP INDEX BOM_LOG_IDX1;
CREATE INDEX BOM_LOG_IDX1
 ON BILL_OF_MATERIAL_LOG (parent_no, parent_plant)
 tablespace JITINDX
 storage ( initial      3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_b/alt_blownup_bill.sql (new)
-- only index!
DROP INDEX BLOWNUP_BILL_IDX1;
CREATE INDEX BLOWNUP_BILL_IDX1
 ON BLOWNUP_BILL (plant , order_no, component_no)
 tablespace JITINDX
 storage ( initial        3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_line_comm.sql (new)
-- only index!
DROP INDEX PO_LINE_COMM_IDX1;
CREATE INDEX PO_LINE_COMM_IDX1
 ON PO_LINE_COMM (plant , po_no, line_no, comment_seq)
 tablespace JITINDX
 storage ( initial       1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_blanket.sql (new)
-- added line to index
DROP INDEX PO_BLANKET_IDX;
CREATE INDEX PO_BLANKET_IDX
 ON PO_BLANKET (plant , po_no, line_no)
 tablespace JITINDX
 storage ( initial       1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_o/alter_oesummary.sql
-- added index for customer
DROP INDEX OESUMMARY_IDX1;
CREATE INDEX OESUMMARY_IDX1
 ON OESUMMARY (cust_no, order_no, order_suffix)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_o/alt_oe_entry_register.sql (new)
-- added index for customer
DROP INDEX OE_ENTRY_REG_IDX1;
CREATE INDEX OE_ENTRY_REG_IDX1
 ON OE_ENTRY_REGISTER (order_no, order_suffix, plant)
 tablespace JITINDX
 storage ( initial        1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_r/alt_requisition.sql (new)
-- added index for vendor
DROP INDEX REQUISITION_IDX4;
CREATE INDEX REQUISITION_IDX4
 ON REQUISITION (plant, vendno, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for vendor
DROP INDEX REQUISITION_IDX5;
CREATE INDEX REQUISITION_IDX5
 ON REQUISITION (plant, requested_by, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for buyer
DROP INDEX REQUISITION_IDX6;
CREATE INDEX REQUISITION_IDX6
 ON REQUISITION (plant, buyer_code, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_r/alt_req_header.sql (new)

-- added index for requestor
DROP INDEX REQ_HEADER_IDX2;
CREATE INDEX REQ_HEADER_IDX2
 ON REQ_HEADER (plant, requested_by, req_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_header.sql (new)
-- added index for vendor
DROP INDEX PO_HEADER_IDX2;
CREATE INDEX PO_HEADER_IDX2
 ON PO_HEADER (plant, vendno, req_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for requestor
DROP INDEX PO_HEADER_IDX3;
CREATE INDEX PO_HEADER_IDX3
 ON PO_HEADER (plant, buyer_code, req_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_c/alt_custcntrcts.sql (new)
-- added customer
DROP INDEX CUSTCNTRCTS_IDX1;
CREATE INDEX CUSTCNTRCTS_IDX1
 ON CUSTCNTRCTS (cust_no, item_no, plant)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_a/alt_ar_cia_invno.sql (new)
-- added index
DROP INDEX AR_CIA_INVNO_IDX1;
CREATE INDEX AR_CIA_INVNO_IDX1
 ON AR_CIA_INVNO (org_unit_id, order_no)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index
DROP INDEX AR_CIA_INVNO_IDX2;
CREATE INDEX AR_CIA_INVNO_IDX2
 ON AR_CIA_INVNO (org_unit_id, cia_invno)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

bkd_tab.sql: create table script

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This is sample table creation script used first in test instance, then in production, from JIT ERP system.
Code Listing:

rem $Author: roblaw $
rem $Locker:  $
rem $Date: 1996/07/07 17:38:06 $
rem $Source: /appl/jit/RCS/ddl/table_b/cre_booking_detail.sql
rem $Revision: 1.1 $           [bkd_tab.sql]
/*
===============================================================
Table
===============================================================
-schedule_no v2(6), hold_cd v2(1)

*/

drop table booking_detail;
CREATE TABLE booking_detail
(
plant            VARCHAR2(2),
order_no         VARCHAR2(10),
order_suffix     NUMERIC(2),
line_no          NUMERIC(3),
sys_date  DATE,
fiscal           VARCHAR2(4), -- YYMM
oracle_user_id   VARCHAR2(30),
reason           VARCHAR2(3), -- INS, DEL, UPD
item_no          VARCHAR2(50),
cust_no          VARCHAR2(10),
ext_price_d      NUMERIC(15,5), -- net dollar change
order_qty_d      NUMERIC(15,5),
unit_price_d     NUMERIC(15,5),
unit_cost_d      NUMERIC(15,5),
freight_d        NUMERIC(15,5),
tax_d            NUMERIC(15,5),
finish_1_o       VARCHAR2(5),
finish_1_n       VARCHAR2(5),
strap_1_o        VARCHAR2(5),
strap_1_n        VARCHAR2(5),
fabric_1_o       VARCHAR2(5),
fabric_1_n       VARCHAR2(5),
fabric_2_o       VARCHAR2(5),
fabric_2_n       VARCHAR2(5),
cush_style_o     VARCHAR2(5),
cush_style_n     VARCHAR2(5),
ship_zip_code_o  VARCHAR2(12),
ship_zip_code_n  VARCHAR2(12),
not_after_date_o DATE,
not_after_date_n DATE,

)
 tablespace JITDATA
 storage ( initial    1m   next        100k
           minextents     1 maxextents   121
           pctincrease    0 );
/*
===============================================================
Index
===============================================================
*/
DROP INDEX booking_detail _idx1;
CREATE INDEX booking_detail _idx1
 ON booking_detail (ORDER_NO, ORDER_SUFFIX, plant)
 tablespace JITINDX
 storage ( initial      500K   next       50K
           minextents     1 maxextents   121
           pctincrease    0 );

DROP INDEX booking_detail _idx2;
CREATE  INDEX booking_detail _idx2
 ON BACKLOG_DEMAND (ITEM_NO, PLANT)
 tablespace JITINDX
 storage ( initial      500K   next       50K
           minextents     1 maxextents   121
           pctincrease    0 );

DROP INDEX booking_detail _idx3;
CREATE  INDEX booking_detail _idx2
 ON BACKLOG_DEMAND (cust_no)
 tablespace JITINDX
 storage ( initial      500K   next       50K
           minextents     1 maxextents   121
           pctincrease    0 );

/*
===============================================================
Security
===============================================================
*/
drop public synonym booking_detail;
create public synonym booking_detail for JIT. booking_detail;

grant all on booking_detail to public;
-- grant all on booking_detail to jituser;
-- grant select on booking_detail to readjit;
/*
===============================================================
Comments
===============================================================
*/
COMMNET ON TABLE booking_detail IS
' The booking detail captures all significant changes to booked
orders.  The user id, date and reason for change are captured;
along with quantity, price and cost.  Other succulent nuggets are
captured to help explain many of our corporate mystries.
 The booking_detail is populated from these triggers:
 oedetl_trg, oehead_trg and oesummary_trg.'

COMMENT ON COLUMN booking_detail.plant IS
'oedetl.plant.  If plant code change, then there will be a
separate entry for each plant.';
COMMENT ON COLUMN booking_detailorder_no IS
'oedetl.order_no.';
COMMENT ON COLUMN booking_detail.order_suffix IS
'oedetl.order_suffix.';
COMMENT ON COLUMN booking_detail.line_no IS
'oedetl.line_no. 0=if header change, oehead or oesummary.';
COMMENT ON COLUMN booking_detail.sys_date IS
'Date change was made.';
COMMENT ON COLUMN booking_detail.fiscal IS
'Fiscal period change was made.';
COMMENT ON COLUMN booking_detail.oracle_user_id IS
'User who made change.';
COMMENT ON COLUMN booking_detail.reason IS
'Reason change happened: INSert, DELete, UPDate.';
COMMENT ON COLUMN booking_detail.item_no IS
'oedetl.item_no.';
COMMENT ON COLUMN booking_detail.cust_no IS
'oehead.cust_no.';
COMMENT ON COLUMN booking_detail.ext_price_d IS
'Net dollar change = order_qty_d * unit_price_d.';
COMMENT ON COLUMN booking_detail.order_qty_d IS
'Net quantity change = new.order_qty - old.order_qty.';
COMMENT ON COLUMN booking_detail.unit_price_d IS
'Net unit price change = new.unit_price - old.unit_price';
COMMENT ON COLUMN booking_detail.freight_d IS
'Net freight dollar change.  oesummary';
COMMENT ON COLUMN booking_detail.tax_d IS
'Net tax dollar change.  oesummary.';
COMMENT ON COLUMN booking_detail.finish_1_o IS
'Old oedetl.finish_1 value. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.finish_1_n IS
'New oedetl.finish_1 value. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.strap_1_o IS
'Old value of oedetl.strap_1.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.strap_1_n IS
'New value of oedetl.strap_1. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.fabric_1_o IS
'Old value of oedetl.fatric_1.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.fabric_1_n IS
'New value of oedetl.fabric_1. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.fabric_2_o IS
'Old value of oedetl.fabric_2.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.fabric_2_n IS
'New value of oedetl.fabric_2. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.cush_style_o IS
'Old value of oedetl.cush_style. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.cush_style_n IS
'New value of oedetl.cush_style. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.ship_zip_code_o IS
'Old value of oehead.ship_zip_code.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.ship_zip_code_n IS
'New value of oehead.ship_zip_code. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.not_after_date_o IS
'Old value of oehead.not_after_date.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.not_after_date_n IS
'New value of oehead.not_after_date. NULL if reason is DELete.';

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;

tab_abh.sql: alter table and comments

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: Sample SQL script to modify database table and to add database documentation. This script would be used on test instance, then ultimately be used to make changes in production.
Code Listing:

rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1996/06/26 00:00:47 $
rem $Source: /appl/jit/RCS/ddl/table_b/alt_backlog_header.sql [bld_alt.sql]
rem $Revision: 1.1 $
set echo on
/*
06/25/96 roblaw schedule_build_header.release_flag to see if in process
*/
alter table backlog_header add (
release_flag    varchar2(1),
GROSS_ORD_VALUE NUMBER(13,2)
);

COMMENT ON COLUMN BACKLOG_HEADER.RELEASE_FLAG IS
'Is SO scheduled and released to production?
 null = not scheduled and thus not released
 N    = scheduled, but not released
 Y    = scheduled and released';

/*
01/02/97 roblaw: new columns for final assembly scheduling
*/
alter table backlog_header add (
week_before    NUMERIC(2),
week_order     NUMERIC(3,1),
item_excep     VARCHAR2(1),
finish_qty     NUMERIC(15,5),
finish_multi   VARCHAR2(5),
finish_excep   VARCHAR2(1),
strap_qty      NUMERIC(15,5),
strap_multi    VARCHAR2(5),
strap_excep    VARCHAR2(1),
fabric_qty     NUMERIC(15,5),
fabric_multi   VARCHAR2(5),
fabric_excep   VARCHAR2(1));

COMMENT ON COLUMN BACKLOG_HEADER.week_before IS
'Week number using not_before_date, sunday logic same as week_num.';

COMMENT ON COLUMN BACKLOG_HEADER.week_order IS
'Number of weeks old from order_date, value range 0-100.';

COMMENT ON COLUMN BACKLOG_HEADER.item_excep IS
'Value based on sum of backlog_detail.item_excep
  = * if any value is *
  = null, if all values are null';

COMMENT ON COLUMN BACKLOG_HEADER.finish_qty IS
'Sum of backlog_detail.finish_qty';

COMMENT ON COLUMN BACKLOG_HEADER.finish_multi IS
'Value is set based on all SO line items, where backlog_detail.finish_1 has
  = null, if all values are null
  = color, if only one color
  = MULTI, if more than one color';

COMMENT ON COLUMN BACKLOG_HEADER.finish_excep IS
'Value based on sum of backlog_detail.finish_excep
  = * if any value is *
  = null, if all values are null';

COMMENT ON COLUMN BACKLOG_HEADER.strap_qty IS
'Sum of backlog_detail.strap_qty';

COMMENT ON COLUMN BACKLOG_HEADER.strap_multi IS
'Value is set based on all SO line items, where backlog_detail.strap_1
 and backlog_detail.strap_2 has
  = null, if all values are null
  = color, if only one color
  = MULTI, if more than one color';

COMMENT ON COLUMN BACKLOG_HEADER.strap_excep IS
'Value based on sum of backlog_detail.strap_excep
  = * if any value is *
  = null, if all values are null';

COMMENT ON COLUMN BACKLOG_HEADER.fabric_qty IS
'sum of backlog_detail.fabric_qty';

COMMENT ON COLUMN BACKLOG_HEADER.fabric_multi IS
'Value is set based on all SO line items, where backlog_detail.fabric_1
 and backlog_detail.fabric_2 has
  = null, if all values are null
  = color, if only one color
  = MULTI, if more than one color';

COMMENT ON COLUMN BACKLOG_HEADER.fabric_excep IS
'Value based on sum of backlog_detail.fabric_excep
  = * if any value is *
  = null, if all values are null';

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