Environment: Oracle database, HPUX
Description: This code was run nightly as CRON script which summarizes open sales order information from JIT ERP database.
Code Listing:
set termout off
rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1996/12/18 20:31:09 $
rem $Source: /appl/jit/RCS/oe/src/oeblu.sql,v $
rem $Revision: 1.9 $
/* program oeblu.sql
date 05-14-96
author robert lawson
update 11-18-96 Added gross_ord_value & fixed week_num = 0 to be
only orders on schedule.
12-18-96 Only order type ORD
01-04-97 RobLaw Added final assembly schedule execptions and counts
purpose: create open backlog files.
Notes:
1. How to know create date of table?
- multi into xn format (table/record up to 10?
- sales_order_type: multi or backlog_detail.sales_order_type
- cust_name to header
*/
set termout on
set verify on
accept i_plant prompt "Plant: (! = all)"
prompt
accept i_sales_order prompt "Sales Order (!=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_order_status oehead.order_status%type;
o_shipto_cd oehead.shipto_cd%type;
o_ship_name oehead.ship_name%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_request_dt date; /* entered date */
o_order_date date; /* customer order date */
o_not_after_date date;
o_not_before_date date;
o_sales_id1 oehead.sales_id1%type;
o_sls_name slsmaster.sls_name%type;
o_cust_no oehead.cust_no%type;
o_family_cd cm_accts.family_cd%type;
segment varchar2(5); /* market segment */
club varchar2(5); /* retail club level */
o_hold_cd oehead.hold_cd%type;
o_hold_reason hold_codes.hold_reason%type;
o_terms_cd oehead.terms_cd%type;
o_terms_desc oehead.terms_desc%type;
o_gross_ord_value oesummary.gross_ord_value%type;
h_cube_feet numeric(15); /* sum of backlog_detail.cube_feet */
h_ext_price oedetl.ext_price%type; /* sum of backlog_detail.ext_price */
h_cube_weight numeric(15); /* sum of backlog_detail.ext_price */
plant_x backlog_header.plant_x%type; /* 31 = ata, plant 30 only */
week_num backlog_header.week_num%type; /* week# 0=in process, 0=fwip,1=curr/past due, sunday */
hold_uninv backlog_header.hold_uninv%type; /* hold code or if uninvoiced */
week_before backlog_header.week_before%type; /* week# using not before date */
week_order backlog_header.week_order%type; /* weeks order is old */
h_item_excep backlog_header.item_excep%type;
h_finish_qty backlog_header.finish_qty%type;
finish_multi backlog_header.finish_multi%type;
h_finish_excep backlog_header.finish_excep%type;
h_strap_qty backlog_header.strap_qty%type;
strap_multi backlog_header.strap_multi%type;
h_strap_excep backlog_header.strap_excep%type;
h_fabric_qty backlog_header.fabric_qty%type;
fabric_multi backlog_header.fabric_multi%type;
h_fabric_excep backlog_header.fabric_excep%type;
o_line_no oedetl.line_no%type;
o_item_no oedetl.item_no%type;
o_description item_master.description%type;
o_product_group item_master.product_group%type;
prod_type backlog_detail.prod_type%type := null; /* null for now, use to identify piece count */
o_finish_1 oedetl.finish_1%type;
o_finish_2 oedetl.finish_2%type;
o_strap_1 oedetl.strap_1%type;
o_strap_2 oedetl.strap_2%type;
o_fabric_1 oedetl.fabric_1%type;
o_fabric_2 oedetl.fabric_2%type;
o_cush_style oedetl.cush_style%type;
o_qty_order oedetl.qty_order%type;
n_qty_order oedetl.qty_order%type; /* qty with uninvoiced */
o_unit_price oedetl.unit_price%type;
o_unit_cost oedetl.unit_cost%type;
o_ext_price oedetl.ext_price%type;
o_sales_order_type oedetl.sales_order_type%type;
cube_feet backlog_detail.cube_feet%type;
o_pieces_per_carton item_price.pieces_per_carton%type;
o_gross_volume item_price.gross_volume%type;
o_gross_wt item_price.gross_wt%type;
cube_weight backlog_detail.cube_weight%type; /* cube weight */
bol_code backlog_detail.bol_code%type; /* freight class code */
bol_desc backlog_detail.bol_desc%type; /* freight class code description */
o_model_flag item_master.model_flag%type;
o_oe_line_comment oe_line_comments.oe_line_comment%type;
finish_comment backlog_detail.finish_comment%type;
strap_comment backlog_detail.strap_comment%type;
fabric_comment backlog_detail.fabric_comment%type;
side_mark backlog_detail.side_mark%type; /* 2 x 30 */
item_excep backlog_detail.item_excep%type;
finish_qty backlog_detail.finish_qty%type;
finish_excep backlog_detail.finish_excep%type;
strap_qty backlog_detail.strap_qty%type;
strap_excep backlog_detail.strap_excep%type;
fabric_qty backlog_detail.fabric_qty%type;
fabric_excep backlog_detail.fabric_excep%type;
o_hold_uninv backlog_header.hold_uninv%type;
o_prod_type backlog_detail.prod_type%type;
o_qty_week0 numeric(15,5);
o_qty_week1 numeric(15,5);
o_qty_week2 numeric(15,5);
o_qty_week3 numeric(15,5);
o_qty_week4 numeric(15,5);
o_qty_week5 numeric(15,5);
o_qty_week6 numeric(15,5);
o_qty_week7 numeric(15,5);
o_qty_week8 numeric(15,5);
o_dol_week0 numeric(15,5);
o_dol_week1 numeric(15,5);
o_dol_week2 numeric(15,5);
o_dol_week3 numeric(15,5);
o_dol_week4 numeric(15,5);
o_dol_week5 numeric(15,5);
o_dol_week6 numeric(15,5);
o_dol_week7 numeric(15,5);
o_dol_week8 numeric(15,5);
l_count numeric(10,5) := 0; /* commit counter */
finish_max numeric(2) := 40; /* max size of comments */
strap_max numeric(2) := 40; /* max size of comments */
fabric_max numeric(2) := 40; /* max size of comments */
sm_max numeric(2) := 60; /* max size of comments */
finish_len numeric(2); /* current size of comments */
strap_len numeric(2); /* current size of comments */
fabric_len numeric(2); /* current size of comments */
sm_len numeric(2); /* current size of comments */
t_len numeric(2); /* scratch variable for comment size */
t_comm oe_line_comments.oe_line_comment%type; /* scratch comment */
o_component_no backlog_demand.component_no%type;
o_qty_extended backlog_demand.qty_extended%type;
o_release_flag schedule_build_header.release_flag%type;
/*
===============================================================
Cursors
===============================================================
*/
cursor get_oehead is
SELECT
oh.plant,
oh.order_no,
oh.order_suffix,
oh.schedule_no,
oh.order_status,
oh.shipto_cd,
oh.ship_name,
oh.ship_city,
oh.ship_state,
oh.ship_zip_code,
oh.ship_country,
oh.request_dt,
oh.order_date,
oh.not_after_date,
oh.not_before_date,
oh.sales_id1,
sm.sls_name,
oh.cust_no,
cm.family_cd,
oh.hold_cd,
hc.hold_reason,
oh.terms_cd,
oh.terms_desc,
os.gross_ord_value,
decode(oh.plant,30,
decode(substr(to_char(oh.not_after_date,'YYMMDD'),1,2),'99','31',null),null),
least((sysdate - least(nvl(oh.order_date,sysdate),sysdate))/7,100),
decode(schedule_no,null,
least(greatest(CEIL((nvl(oh.not_before_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),99),0) week_before,
decode(schedule_no,null,
least(greatest(CEIL((nvl(oh.not_after_date,sysdate) -
(sysdate+decode(to_char(sysdate,'D'),7,1,
-((to_char(sysdate,'D')-1)))))/7),0),99),0),
decode(oh.order_status,'S','UNINV','B','UNINV','I','ADMIN',
decode(oh.schedule_no,null,
decode(oh.hold_cd,null,null,'Z','MFG','D','ADMIN','I','ADMIN','Y','ADMIN',
'CREDIT'),'SCHED')),
decode(cm.family_cd,'CONT','CONT','DESN','DESN','RETL'),
decode(cm.family_cd,'CONT',null,'DESN',null,'RETL',null,cm.family_cd)
FROM
slsmaster sm, hold_codes hc, cm_accts cm, oesummary os, oehead oh
WHERE
('&i_plant' = '!' or oh.plant = '&i_plant') and
('&i_sales_order' = '!' or oh.order_no = '&i_sales_order') and
oh.order_status in ('B','I','O','S') and
oh.order_type = 'ORD' and
os.order_no = oh.order_no and
os.order_suffix = oh.order_suffix and
os.plant = oh.plant and
cm.cust_no(+) = oh.cust_no and
hc.hold_cd(+) = oh.hold_cd and
sm.slsman(+) = oh.sales_id1
ORDER BY
oh.order_no, oh.order_suffix;
cursor get_oedetl is
SELECT
line_no,
od.item_no,
finish_1,
finish_2,
strap_1,
strap_2,
fabric_1,
fabric_2,
cush_style,
nvl(qty_order,0),
decode(o_order_status,'B',qty_shipped,qty_order),
nvl(unit_price,0),
nvl(unit_cost,0),
sales_order_type,
im.description,
im.product_group,
nvl(ip.pieces_per_carton,1),
nvl(ip.gross_volume,0),
nvl(ip.gross_wt,0),
decode(bc.description,null,null,substr(bc.description,58,60)),
decode(bc.description,null,null,substr(bc.description,1,20)),
im.model_flag
FROM
bol_codes bc, item_price ip, item_master im, oedetl od
WHERE
od.plant = o_plant and
od.order_no = o_order_no and
od.order_suffix = o_order_suffix and
im.plant = od.plant and
im.item_no = od.item_no and
ip.plant = od.plant and
ip.item_no = od.item_no and
bc.bol_code(+) = im.wrnty_lgth
ORDER BY
od.line_no;
cursor get_schedule is
SELECT
decode(sh.release_flag,'B','Y',sh.release_flag)
FROM
schedule_build_header sh, schedule_build_detail sd
WHERE
sd.plant = o_plant and
sd.schedule_no = o_schedule_no and
sd.order_no = o_order_no and
sd.order_suffix = o_order_suffix and
sh.plant = sd.plant and
sh.schedule_no = sd.schedule_no;
cursor get_comments is
SELECT upper(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;
cursor get_demand is
SELECT
qm.component_no,
(nvl(qm.ext_qty,0) * n_qty_order)
FROM
quote_model_detail qm
WHERE
qm.quote_no = o_order_no AND
qm.plant = o_plant AND
qm.oedetl_line_no = o_line_no;
cursor get_exceptions(p_exception varchar2, p_type varchar2) is
SELECT
'*'
FROM
pa_exceptions
WHERE
plant = o_plant AND
"EXCEPTION" = p_exception AND
type = p_type;
cursor get_backlog is
SELECT
decode(bh.plant_x,null,bh.plant,bh.plant_x),
bh.hold_uninv,
prod_type,
sum(decode(week_num,0,qty_order,0)),
sum(decode(week_num,1,qty_order,0)),
sum(decode(week_num,2,qty_order,0)),
sum(decode(week_num,3,qty_order,0)),
sum(decode(week_num,4,qty_order,0)),
sum(decode(week_num,5,qty_order,0)),
sum(decode(week_num,6,qty_order,0)),
sum(decode(week_num,7,qty_order,0)),
sum(decode(greatest(week_num,7),7,0,1) * qty_order),
sum(decode(week_num,0,bd.ext_price,0)),
sum(decode(week_num,1,bd.ext_price,0)),
sum(decode(week_num,2,bd.ext_price,0)),
sum(decode(week_num,3,bd.ext_price,0)),
sum(decode(week_num,4,bd.ext_price,0)),
sum(decode(week_num,5,bd.ext_price,0)),
sum(decode(week_num,6,bd.ext_price,0)),
sum(decode(week_num,7,bd.ext_price,0)),
sum(decode(greatest(week_num,7),7,0,1) * bd.ext_price)
FROM backlog_detail bd, backlog_header bh
WHERE
bd.order_no = bh.order_no and
bd.order_suffix = bh.order_suffix and
bd.plant = bh.plant
GROUP BY decode(bh.plant_x,null,bh.plant,bh.plant_x),
hold_uninv, prod_type;
/*
===============================================================
In the beginning
===============================================================
*/
begin
-- truncate table backlog_header;
delete from backlog_header;
commit;
-- truncate table backlog_detail;
delete from backlog_detail;
commit;
-- truncate table backlog_demand;
delete from backlog_demand;
commit;
-- truncate table backlog_summary;
delete from backlog_summary;
commit;
/*
===============================================================
Get header
===============================================================
*/
open get_oehead;
loop
fetch get_oehead into
o_plant, o_order_no, o_order_suffix, o_schedule_no, o_order_status, o_shipto_cd,
o_ship_name, o_ship_city, o_ship_state, o_ship_zip_code, o_ship_country, o_request_dt,
o_order_date, o_not_after_date, o_not_before_date, o_sales_id1, o_sls_name, o_cust_no,
o_family_cd, o_hold_cd, o_hold_reason, o_terms_cd, o_terms_desc, o_gross_ord_value,
plant_x, week_order, week_before, week_num, hold_uninv, segment, club;
exit when get_oehead%notfound;
h_cube_feet := 0; h_ext_price := 0; h_cube_weight := 0;
h_item_excep := null; h_finish_excep := null; h_strap_excep := null; h_fabric_excep := null;
h_finish_qty := 0; h_strap_qty := 0; h_fabric_qty := 0;
finish_multi := null; strap_multi := null; fabric_multi := null;
o_release_flag := null;
if o_schedule_no is not null then
open get_schedule;
fetch get_schedule into o_release_flag;
close get_schedule;
end if;
-- dbms_output.put_line('*** 'o_order_no' ***');
/*
===============================================================
Get line items
===============================================================
*/
open get_oedetl;
loop
fetch get_oedetl into
o_line_no, o_item_no, o_finish_1, o_finish_2, o_strap_1, o_strap_2, o_fabric_1,
o_fabric_2, o_cush_style, o_qty_order, n_qty_order, o_unit_price, o_unit_cost,
o_sales_order_type, o_description, o_product_group, o_pieces_per_carton,
o_gross_volume, o_gross_wt, bol_code, bol_desc, o_model_flag;
exit when get_oedetl%notfound;
item_excep := null; finish_excep := null; strap_excep := null; fabric_excep := null;
finish_qty := 0; strap_qty := 0; fabric_qty := 0;
o_ext_price := n_qty_order * o_unit_price; /* reclac, dont use oedetl value */
cube_feet := ceil(n_qty_order/o_pieces_per_carton) * o_gross_volume;
cube_weight := ceil(n_qty_order/o_pieces_per_carton) * o_gross_wt;
h_cube_feet := h_cube_feet + cube_feet;
h_ext_price := h_ext_price + o_ext_price;
h_cube_weight := h_cube_weight + cube_weight;
/*
===============================================================
Get exceptions, colors and quantities
===============================================================
*/
open get_exceptions(o_item_no,'ITEM_NO');
fetch get_exceptions into item_excep;
if get_exceptions%FOUND then
h_item_excep := '*';
end if;
close get_exceptions;
if o_finish_1 is not null then
finish_qty := n_qty_order;
h_finish_qty := h_finish_qty + finish_qty;
open get_exceptions(o_finish_1,'FINISH_1');
fetch get_exceptions into finish_excep;
if get_exceptions%FOUND then
h_finish_excep := '*';
end if;
close get_exceptions;
open get_exceptions(o_finish_2,'FINISH_2');
fetch get_exceptions into finish_excep;
if get_exceptions%FOUND then
h_finish_excep := '*';
end if;
close get_exceptions;
-- 1/05/97 RobLaw no finish_2 multi logic as makes no sense today
if finish_multi is null then
finish_multi := o_finish_1; -- first color
else
if finish_multi <> o_finish_1 and finish_multi <> 'MULTI' then
finish_multi := 'MULTI';
end if;
end if;
end if; /* finish*/
if o_strap_1 is not null then
strap_qty := n_qty_order;
h_strap_qty := h_strap_qty + strap_qty;
open get_exceptions(o_strap_1,'VINYL_1');
fetch get_exceptions into strap_excep;
if get_exceptions%FOUND then
h_strap_excep := '*';
end if;
close get_exceptions;
open get_exceptions(o_strap_2,'VINYL_2');
fetch get_exceptions into strap_excep;
if get_exceptions%FOUND then
h_strap_excep := '*';
end if;
close get_exceptions;
if strap_multi is null then
strap_multi := o_strap_1; -- first color
else
if strap_multi <> o_strap_1 and strap_multi <> 'MULTI' then
strap_multi := 'MULTI';
end if;
if strap_multi <> o_strap_2 and strap_multi <> 'MULTI' then
strap_multi := 'MULTI';
end if;
end if;
end if; /* strap */
if o_fabric_1 is not null then
fabric_qty := n_qty_order;
h_fabric_qty := h_fabric_qty + fabric_qty;
open get_exceptions(o_fabric_1,'FABRIC_1');
fetch get_exceptions into fabric_excep;
if get_exceptions%FOUND then
h_fabric_excep := '*';
end if;
close get_exceptions;
open get_exceptions(o_fabric_2,'FABRIC_2');
fetch get_exceptions into fabric_excep;
if get_exceptions%FOUND then
h_fabric_excep := '*';
end if;
close get_exceptions;
if fabric_multi is null then
fabric_multi := o_fabric_1; -- first color
else
if fabric_multi <> o_fabric_1 and fabric_multi <> 'MULTI' then
fabric_multi := 'MULTI';
end if;
if fabric_multi <> o_fabric_2 and fabric_multi <> 'MULTI' then
fabric_multi := 'MULTI';
end if;
end if;
end if; /* fabric */
/*
===============================================================
Get comments
===============================================================
*/
finish_comment := null; finish_len := 0;
strap_comment := null; strap_len := 0;
fabric_comment := null; fabric_len := 0;
side_mark := null; sm_len := 0;
open get_comments;
loop
fetch get_comments into o_oe_line_comment;
exit when get_comments%notfound;
if upper(substr(o_oe_line_comment,1,8)) = 'FINISH: ' and
(finish_len <> (finish_max - finish_len) then
t_len := (finish_max - finish_len);
end if;
finish_comment := finish_comment substr(t_comm,1,t_len);
finish_len := finish_len + t_len;
-- SPCL obsolete as of 1/20/97, but here for compatibility
elsif upper(substr(o_oe_line_comment,1,6)) = 'SPCL: ' and
(finish_len <> (finish_max - finish_len) then
t_len := (finish_max - finish_len);
end if;
finish_comment := finish_comment substr(t_comm,1,t_len);
finish_len := finish_len + t_len;
elsif upper(substr(o_oe_line_comment,1,7)) = 'FRAME: ' and
(finish_len <> (finish_max - finish_len) then
t_len := (finish_max - finish_len);
end if;
finish_comment := finish_comment substr(t_comm,1,t_len);
finish_len := finish_len + t_len;
elsif upper(substr(o_oe_line_comment,1,7)) = 'STRAP: ' and
(strap_len <> (strap_max - strap_len) then
t_len := (strap_max - strap_len);
end if;
strap_comment := strap_comment substr(t_comm,1,t_len);
strap_len := strap_len + t_len;
elsif upper(substr(o_oe_line_comment,1,8)) = 'FABRIC: ' and
(fabric_len <> (fabric_max - fabric_len) then
t_len := (fabric_max - fabric_len);
end if;
fabric_comment := fabric_comment substr(t_comm,1,t_len);
fabric_len := fabric_len + t_len;
elsif upper(substr(o_oe_line_comment,1,5)) = 'S/M: ' and
(sm_len <> (sm_max - sm_len) then
t_len := (sm_max - sm_len);
end if;
side_mark := side_mark substr(t_comm,1,t_len);
sm_len := sm_len + t_len;
end if;
end loop; /* oe_line_comments */
close get_comments;
/*
===============================================================
Insert line item
===============================================================
*/
INSERT INTO backlog_detail
(plant, order_no, order_suffix, line_no, item_no, description, PRODUCT_GROUP,
prod_type, finish_1, finish_2, strap_1, strap_2, fabric_1, fabric_2,
CUSH_STYLE, finish_comment, strap_comment, fabric_comment, qty_order,
UNIT_PRICE, unit_cost, EXT_PRICE, SALES_ORDER_TYPE, cube_feet, cube_weight,
bol_code, bol_desc, item_excep, finish_qty, finish_excep, strap_qty,
strap_excep, fabric_qty, fabric_excep, side_mark)
VALUES
(o_plant, o_order_no, o_order_suffix, o_line_no, o_item_no, o_description, o_PRODUCT_GROUP,
prod_type, o_finish_1, o_finish_2, o_strap_1, o_strap_2, o_fabric_1, o_fabric_2,
o_CUSH_STYLE, finish_comment, strap_comment, fabric_comment, n_qty_order,
o_UNIT_PRICE, o_unit_cost, o_EXT_PRICE, o_SALES_ORDER_TYPE, cube_feet, cube_weight,
bol_code, bol_desc, item_excep, finish_qty, finish_excep, strap_qty,
strap_excep, fabric_qty, fabric_excep, side_mark);
/*
===============================================================
Demand
note: if model, then 'consists of' list of parts, else use SO part
===============================================================
*/
if o_model_flag <> 'Y' then
INSERT INTO backlog_demand
(plant, order_no, order_suffix, line_no, component_no, qty_extended)
VALUES
(o_plant, o_order_no, o_order_suffix, o_line_no, o_item_no, n_qty_order);
else
open get_demand;
loop
fetch get_demand into o_component_no, o_qty_extended;
exit when get_demand%notfound;
INSERT INTO backlog_demand
(plant, order_no, order_suffix, line_no, component_no, qty_extended)
VALUES
(o_plant, o_order_no, o_order_suffix, o_line_no, o_component_no, o_qty_extended);
end loop; /* demand */
close get_demand;
end if;
end loop; /* oedetl */
close get_oedetl;
/*
===============================================================
Header
===============================================================
*/
INSERT INTO backlog_header
(plant, order_no, order_suffix, schedule_no, order_status, shipto_cd,
ship_name, ship_city, ship_state, ship_zip_code, ship_country, request_dt,
order_date, not_after_date, not_before_date, sales_id1, sls_name, cust_no,
segment, club, hold_cd, hold_reason, terms_cd, terms_desc, gross_ord_value, plant_x,
week_order, week_before, week_num, hold_uninv, ext_price, cube_feet, cube_weight,
release_flag, item_excep, finish_qty, finish_multi, finish_excep, strap_qty,
strap_multi, strap_excep, fabric_qty, fabric_multi, fabric_excep
)
VALUES
(o_plant, o_order_no, o_order_suffix, o_schedule_no, o_order_status, o_shipto_cd,
o_ship_name, o_ship_city, o_ship_state, o_ship_zip_code, o_ship_country, o_request_dt,
o_order_date, o_not_after_date, o_not_before_date, o_sales_id1, o_sls_name, o_cust_no,
segment, club, o_hold_cd, o_hold_reason, o_terms_cd, o_terms_desc, o_gross_ord_value, plant_x,
week_order, week_before, week_num, hold_uninv, h_ext_price, h_cube_feet, h_cube_weight,
o_release_flag, h_item_excep, h_finish_qty, finish_multi, h_finish_excep, h_strap_qty,
strap_multi, h_strap_excep, h_fabric_qty, fabric_multi, h_fabric_excep);
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;
/*
===============================================================
Summary tables
===============================================================
*/
open get_backlog;
loop
fetch get_backlog into
o_plant, o_hold_uninv, o_prod_type, o_qty_week0, o_qty_week1, o_qty_week2, o_qty_week3,
o_qty_week4, o_qty_week5, o_qty_week6, o_qty_week7, o_qty_week8, o_dol_week0, o_dol_week1,
o_dol_week2, o_dol_week3, o_dol_week4, o_dol_week5, o_dol_week6, o_dol_week7, o_dol_week8;
exit when get_backlog%notfound;
INSERT INTO backlog_summary
(plant, hold_uninv, prod_type, qty_week0, qty_week1, qty_week2, qty_week3,
qty_week4, qty_week5, qty_week6, qty_week7, qty_week8, dol_week0, dol_week1,
dol_week2, dol_week3, dol_week4, dol_week5, dol_week6, dol_week7, dol_week8)
VALUES
(o_plant, o_hold_uninv, o_prod_type, o_qty_week0, o_qty_week1, o_qty_week2, o_qty_week3,
o_qty_week4, o_qty_week5, o_qty_week6, o_qty_week7, o_qty_week8, o_dol_week0, o_dol_week1,
o_dol_week2, o_dol_week3, o_dol_week4, o_dol_week5, o_dol_week6, o_dol_week7, o_dol_week8);
end loop; /* backlog */
close get_backlog;
/*
===============================================================
El Fin
===============================================================
*/
commit;
end;
/
exit
No comments:
Post a Comment