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