Environment: Oracle database, HPUX
Description: This script was run nightly via CRON to create e-form print file for purchase orders from the JIT ERP database.
Code Listing:
rem set echo on
set termout off
rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1995/08/30 20:57:08 $
rem $Source: /appl/jit/RCS/po/src/popp.sql,v $
rem $Revision: 1.0 $
/* program popp.sql
date 04-sep-95
author robert lawson
purpose: create purchase orders to use with eforms
Notes:
1. Set l_page_pos
*/
set termout on
prompt v1.0.02
accept i_plant prompt "plant code? "
prompt print/sort option:
prompt 0. Receiving
prompt 1. Vendor
prompt 2. Purchasing
prompt 3. Accounts Payable
accept p_option prompt "p_option? "
prompt select/update option:
prompt 0. Y to N
prompt 1. Y to 1
prompt 2. 1 to 2
prompt 3. 2 to N
prompt 4. 1 to N
prompt 5. Y to Y
accept u_option number prompt "u_option? "
accept i_form prompt "e_form (popp)? "
set termout off
declare
/* po_header fields */
o_plant VARCHAR2( 2);
o_po_no VARCHAR2(10);
o_mod_level VARCHAR2( 3);
o_po_date DATE;
o_v_name VARCHAR2(30);
o_vendno VARCHAR2(10);
o_v_addr1 VARCHAR2(30);
o_v_addr2 VARCHAR2(30);
o_v_city VARCHAR2(20);
o_v_state VARCHAR2( 4);
o_v_zip VARCHAR2(10);
o_v_country VARCHAR2(20);
o_v_contact VARCHAR2(30);
o_ship_to_loc VARCHAR2(10);
o_total_net_value NUMBER(15,2);
o_misc1_cost NUMBER(15,2);
o_misc2_cost NUMBER(15,2);
o_freight_cost NUMBER(15,2);
o_total_tax NUMBER(15,2);
o_total_value NUMBER(15,2);
o_loc_name VARCHAR2(30);
o_loc_addr1 VARCHAR2(30);
o_loc_addr2 VARCHAR2(30);
o_loc_city VARCHAR2(20);
o_loc_state VARCHAR2( 4);
o_loc_zip VARCHAR2(10);
o_shipvia_desc VARCHAR2(30);
o_fob_desc VARCHAR2(30);
o_terms_desc VARCHAR2(30);
o_buyer_name VARCHAR2(30);
/* po_line fields */
o_line_no NUMBER(3);
o_item_no VARCHAR2(50);
o_po_unit_meas VARCHAR2(4);
o_curr_prom_date DATE;
o_qty_ordered NUMBER(15,2);
o_qty_received NUMBER(15,2);
o_unit_cost NUMBER(15,2);
o_ext_cost NUMBER(15,2);
o_description VARCHAR2(100);
/* po_header_comm fields */
o_comment_text VARCHAR2(80);
/* po_line_comm fields */
o_l_comment_text VARCHAR2(80);
/* po_trailer_comments fields */
o_comm VARCHAR2(50);
/* local report variables */
l_document VARCHAR2(6) := 'popp';
e_form VARCHAR2(6); /* eform file to use */
l_sort_key VARCHAR2(15);
l_doc_id_no VARCHAR2(15);
l_fill NUMBER(2); /* lines to fill for page */
l_page NUMBER(2);
l_seq_no NUMBER(5) := 1; /* continuous sequence */
l_cntr NUMBER(2); /* physical lines printed */
l_init_value VARCHAR2(1) :='Y'; /* select flag value */
l_new_value VARCHAR2(1) := 'Y'; /* update flag value */
l_header_size NUMBER(2); /* header size in lines */
l_page_size NUMBER(2) := 58; /* page size in lines */
l_lines NUMBER(2); /* lines to print */
l_tof VARCHAR2(4) := '\NEW'; /* TOF to use */
l_page_line NUMBER(2); /* line that page number is on */
l_page_pos NUMBER(3); /* position page number starts */
l_distrib VARCHAR2(20); /* Marginal Word */
l_hcnt NUMBER(3); /* Number of header comments */
/* newpage variables */
n_text VARCHAR2(132);
n_seq_no NUMBER(5);
/* ==================================================================== */
cursor get_header is
SELECT h.plant,
h.po_no,
nvl(h.mod_level,' '),
h.po_date,
nvl(h.v_name,' '),
h.vendno,
nvl(h.v_addr1,' '),
nvl(h.v_addr2,' '),
nvl(h.v_city,' '),
nvl(h.v_state,' '),
nvl(h.v_zip,' '),
nvl(h.v_country,' '),
nvl(h.v_contact,' '),
nvl(h.ship_to_loc,' '),
nvl(h.total_net_value,0),
nvl(h.misc1_cost,0),
nvl(h.misc2_cost,0),
nvl(h.freight_cost,0),
nvl(h.total_tax,0),
nvl(h.total_value,0),
nvl(s.loc_name,' '),
nvl(s.loc_addr1,' '),
nvl(s.loc_addr2,' '),
nvl(s.loc_city,' '),
nvl(s.loc_state,' '),
nvl(s.loc_zip,' '),
nvl(v.shipvia_desc,' '),
nvl(f.fob_desc,' '),
nvl(t.terms_desc,' '),
nvl(b.buyer_name,' ')
FROM po_header h,
po_ship_to s,
shipvia v,
buyer_code b,
fob f,
terms t
WHERE h.plant = '&i_plant'
and h.print_status = l_init_value
and h.status <> 'X'
and s.plant = h.plant
and s.ship_to_loc(+) = h.ship_to_loc
and b.buyer_code(+) = h.buyer_code
and v.shipvia_cd(+) = h.ship_via_code
and f.fob_cd(+) = h.fob_code
and t.terms_cd(+) = h.terms_cd;
/* ==================================================================== */
cursor get_header_comments is
SELECT comment_text
FROM po_header_comm
WHERE plant = o_plant and
po_no = o_po_no AND
print_flag = 'Y'
ORDER BY comment_seq;
/* ==================================================================== */
cursor get_detail_comments is
SELECT comment_text
FROM po_line_comm
WHERE plant = o_plant and
po_no = o_po_no AND
line_no = o_line_no AND
print_flag = 'Y'
ORDER BY comment_seq;
/* ==================================================================== */
cursor get_trailer is
SELECT comm
FROM po_trailer_comments
WHERE plant = o_plant and
po_no = o_po_no AND
comm_loc = 'Y'
ORDER BY line_no;
/* ==================================================================== */
cursor get_detail is
SELECT d.line_no,
d.item_no,
nvl(d.po_unit_meas,' '),
d.curr_prom_date,
nvl(d.qty_ordered,0),
nvl(d.qty_received,0),
nvl(d.unit_cost,0),
nvl(d.ext_cost,0),
nvl(d.description,' ')
FROM po_line d
WHERE d.plant = o_plant
and d.po_no = o_po_no
and d.line_status <> 'X'
ORDER BY line_no;
/* ==================================================================== */
cursor get_newpage_header is
select text ,
seq_no
from temp_4_reports
where username = user
and document = l_document
and sort_key = l_sort_key
and doc_id_no = l_doc_id_no
and seq_no <= l_header_size order by seq_no; /* ==================================================================== */ begin /* Header */ delete from temp_4_reports where username = user and document = l_document; commit; /* Remove old stuff */ if '&i_form' is null then e_form := l_document; else e_form := '&i_form'; end if; if &u_option = 0 then l_init_value := 'Y'; l_new_value := 'N'; elsif &u_option = 1 then l_init_value := 'Y'; l_new_value := '1'; elsif &u_option = 2 then l_init_value := '1'; l_new_value := '2'; elsif &u_option = 3 then l_init_value := '2'; l_new_value := 'N'; elsif &u_option = 4 then l_init_value := '1'; l_new_value := 'N'; end if; open get_header; loop fetch get_header into o_plant, o_po_no, o_mod_level, o_po_date, o_v_name, o_vendno, o_v_addr1, o_v_addr2, o_v_city, o_v_state, o_v_zip, o_v_country, o_v_contact, o_ship_to_loc, o_total_net_value, o_misc1_cost, o_misc2_cost, o_freight_cost, o_total_tax, o_total_value, o_loc_name, o_loc_addr1, o_loc_addr2, o_loc_city, o_loc_state, o_loc_zip, o_shipvia_desc, o_fob_desc, o_terms_desc, o_buyer_name; exit when get_header%notfound; if &p_option = 0 then l_sort_key := o_po_no; l_distrib := 'Receiving'; elsif &p_option = 1 then l_sort_key := rtrim(o_vendno) o_po_no; l_distrib := 'Vendor'; elsif &p_option = 2 then l_sort_key := o_po_no; l_distrib := 'Purchasing'; else /* 4 */ l_sort_key := o_po_no; l_distrib := 'Accounts Payable'; end if; l_doc_id_no := o_po_no ; l_page := 1; l_cntr := 0; l_seq_no := 1; /* First page and master page for following pages */ insert into temp_4_reports /* TOF */ (document, username, sort_key, doc_id_no, seq_no, text) values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, l_tof ); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; for i in 1..8 loop insert into temp_4_reports /* Blank */ (document, username, sort_key, doc_id_no, seq_no, text) values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' '); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; end loop; 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, lpad(' ',12) rpad(o_v_name,31) rpad(o_vendno,48) rpad(o_loc_name,31) o_ship_to_loc); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; 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, lpad(' ',12) rpad(o_v_addr1,79) o_loc_addr1 ); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; 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, lpad(' ',12) rpad(o_v_addr2,79) o_loc_addr2 ); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; 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, lpad(' ',12) rpad(o_v_city ', ' o_v_state ' ' o_v_zip,79) o_loc_city ', ' o_loc_state ' ' o_loc_zip ); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; 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, lpad(' ',12) decode(o_v_country,'USA',' ',o_v_country) ); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; /* ==================================================================== */ begin /* header comments */ open get_header_comments; l_hcnt := 3;l_fill := l_hcnt; for i in 1..l_hcnt loop fetch get_header_comments into o_comment_text; exit when get_header_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, lpad(' ',12) o_comment_text); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; l_fill := l_fill - 1; end loop; for i in 1..l_fill loop insert into temp_4_reports /* Blank */ (document, username, sort_key, doc_id_no, seq_no, text) values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' '); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; end loop; close get_header_comments; end; /* header comments */ /* ==================================================================== */ for i in 1..3 loop insert into temp_4_reports /* Blank */ (document, username, sort_key, doc_id_no, seq_no, text) values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' '); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; end loop; 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, lpad(decode(&p_option,0,' ',to_char(o_total_net_value,'9,999,999.90')),13) lpad(decode(&p_option,0,' ',to_char(o_misc2_cost,'9,999,999.90')),13) lpad(decode(&p_option,0,' ',to_char(o_misc1_cost,'9,999,999.90')),13) lpad(decode(&p_option,0,' ',to_char(o_freight_cost,'9,999,999.90')),13) lpad(decode(&p_option,0,' ',to_char(o_total_tax,'9,999,999.90')),13) lpad(decode(&p_option,0,' ',to_char(o_total_value,'9,999,999.90')),13) ); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; insert into temp_4_reports /* Blank */ (document, username, sort_key, doc_id_no, seq_no, text) values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' '); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; l_page_line := l_seq_no; l_page_pos := 98; 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, rpad(to_char(o_po_date,'MM/DD/YY'),9) rpad(o_shipvia_desc,31) rpad(o_fob_desc,31) rpad(o_plant,3) rpad(o_po_no,11) rpad(o_mod_level,3) to_char(sysdate,'MM/DD/YY') to_char(l_page,'9999') ); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; insert into temp_4_reports /* Blank */ (document, username, sort_key, doc_id_no, seq_no, text) values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' '); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; 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, rpad(o_terms_desc,31) rpad(o_v_contact,31) o_buyer_name ); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; insert into temp_4_reports /* Blank */ (document, username, sort_key, doc_id_no, seq_no, text) values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' '); l_seq_no := l_seq_no+1; l_cntr := l_cntr+1; l_header_size := l_cntr; /* Reuse initial header */ /* ==================================================================== */ begin /* detail */ open get_detail; loop fetch get_detail into o_line_no, o_item_no, o_po_unit_meas, o_curr_prom_date, o_qty_ordered, o_qty_received, o_unit_cost, o_ext_cost, o_description ; exit when get_detail%notfound; l_lines := 2;if (l_cntr + l_lines) > l_page_size then /* new page */
l_fill := l_page_size - l_cntr;
for i in 1..l_fill loop
insert into temp_4_reports /* Blank */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' ');
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop;
insert into temp_4_reports /* Marginal Word */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'[' e_form ']' rpad(l_document,50) l_distrib );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
begin /* newpage header */
l_cntr := 0; l_page := l_page + 1;
open get_newpage_header;
loop
fetch get_newpage_header into
n_text, n_seq_no;
exit when get_newpage_header%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,
decode(n_seq_no, l_page_line, substr(n_text,1,l_page_pos)
to_char(l_page,'9999'), n_text) );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop;
close get_newpage_header;
end; /* newpage header */
end if; /* new page */
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,
rpad(o_line_no,4) rpad(substr(o_item_no,1,18),19)
rpad(o_po_unit_meas,35) rpad(to_char(o_curr_prom_date,'MM/DD/YY'),9)
to_char(o_qty_ordered,'99,999,999') to_char(o_qty_received,'99,999,999')
rpad(decode(&p_option,0,' ',to_char(o_unit_cost,'99,999,990.00')),13)
rpad(decode(&p_option,0,' ',to_char(o_ext_cost,'99,999,990.00')),13) );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
insert into temp_4_reports /* Blank */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
lpad(' ',12) o_description );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
insert into temp_4_reports /* Blank */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' ');
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
/* ==================================================================== */
begin /* detail comments */
open get_detail_comments;
loop
fetch get_detail_comments into
o_l_comment_text;
exit when get_detail_comments%notfound;
l_lines := 1;if (l_cntr + l_lines) > l_page_size then /* new page */
l_fill := l_page_size - l_cntr;
for i in 1..l_fill loop
insert into temp_4_reports /* Blank */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' ');
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop;
insert into temp_4_reports /* Marginal Word */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'[' e_form ']' rpad(l_document,50) l_distrib );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
begin /* newpage header */
l_cntr := 0; l_page := l_page + 1;
open get_newpage_header;
loop
fetch get_newpage_header into
n_text, n_seq_no;
exit when get_newpage_header%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,
decode(n_seq_no, l_page_line, substr(n_text,1,l_page_pos)
to_char(l_page,'9999'), n_text) );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop;
close get_newpage_header;
end; /* newpage header */
end if; /* new page */
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,
lpad(' ',12) o_l_comment_text );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop; /* detail comments */
close get_detail_comments;
end; /* detail comments */
end loop; /* detail */
close get_detail;
end; /* detail */
/* ==================================================================== */
begin /* trailer comments */
open get_trailer;
loop
fetch get_trailer into
o_comm;
exit when get_trailer%notfound;
l_lines := 1;if (l_cntr + l_lines) > l_page_size then /* new page */
l_fill := l_page_size - l_cntr;
for i in 1..l_fill loop
insert into temp_4_reports /* Blank */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' ');
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop;
insert into temp_4_reports /* Marginal Word */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'[' e_form ']' rpad(l_document,50) l_distrib );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
begin /* newpage header */
l_cntr := 0; l_page := l_page + 1;
open get_newpage_header;
loop
fetch get_newpage_header into
n_text, n_seq_no;
exit when get_newpage_header%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,
decode(n_seq_no, l_page_line, substr(n_text,1,l_page_pos)
to_char(l_page,'9999'), n_text) );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop;
close get_newpage_header;
end; /* newpage header */
end if; /* new page */
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,
lpad(' ',12) o_comm);
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop; /* trailer comments */
close get_trailer;
end; /* trailer comments */
/* ==================================================================== */
l_fill := l_page_size - l_cntr;
for i in 1..l_fill loop
insert into temp_4_reports /* Blank */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no, ' ');
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop;
insert into temp_4_reports /* Marginal Word */
(document, username, sort_key, doc_id_no, seq_no, text)
values (l_document, user, l_sort_key, l_doc_id_no, l_seq_no,
'[' e_form ']' rpad(l_document,50) l_distrib );
l_seq_no := l_seq_no+1; l_cntr := l_cntr+1;
end loop; /* Header */
close get_header;
/* ==================================================================== */
update po_header
set print_status = l_new_value
where print_status = l_init_value
and po_no in
(select distinct doc_id_no
from temp_4_reports
where document = 'popp' and username = user);
end; /* Header */
/* ==================================================================== */
/
set heading off
set linesize 132
set feedback off
set pagesize 60
spool popp
select text
from temp_4_reports
where document = 'popp'
and username = user
order by sort_key, doc_id_no, seq_no
/
spool off
exit
No comments:
Post a Comment