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