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
No comments:
Post a Comment