SET TERMOUT OFF;
SET VERIFY OFF;
SET ECHO OFF;
COLUMN TODAY NEW_VALUE TRIM(_DATE) NOPRINT;
COLUMN rowcount NEW_VALUE v_rowcount NOPRINT;
COLUMN v_error_no_var NEW_VALUE v_exit_code HEADING 'Error Code' FORMAT 9999999999 NOPRINT;
SET FEEDBACK OFF;
SET LINESIZE 350;
SET PAGESIZE 6400;
REPHEADER OFF;

WHENEVER SQLERROR EXIT 98;
WHENEVER OSERROR EXIT 99;

VARIABLE gn_error_no NUMBER;
VARIABLE gc_batch_run_date CHAR;
VARIABLE gn_count_rows NUMBER;

VARIABLE v_file_path VARCHAR;

SELECT 0 rowcount FROM DUAL;

SET UNDERLINE OFF
COLUMN creation_date HEADING 'CREATION_DATE,' FORMAT A15 TRUNC PRINT;
COLUMN asn_line_id HEADING 'ASN_LINE_ID,' FORMAT A12 TRUNC PRINT;
COLUMN asn_header_id HEADING 'ASN_HEADER_ID,' FORMAT A23 TRUNC PRINT;
BREAK ON ASN_LINE_ID;

SET TERMOUT ON;
SPOOL shipment_detail_report.csv
SET HEADING ON;
SELECT NVL(ROWNUM,0) ROWCOUNT
       ,TO_DATE(IOALT.CREATION_DATE,'DD/MM/YYYY') CREATION_DATE
       ,','||IOALT.ASN_LINE_ID ASN_LINE_ID
       ,','||IOALT.ASN_HEADER_ID ASN_HEADER_ID
FROM table1 IOALT,
     table2 IOAHT,
     table3 IOAOT
WHERE IOALT.ASN_HEADER_ID=IOAHT.ASN_HEADER_ID
AND IOALT.ASN_ORDER_ID=IOAOT.ASN_ORDER_ID
AND IOAHT.ASN_HEADER_ID= IOAOT.ASN_HEADER_ID
AND IOALT.WAREHOUSE_CODE='FRT'
AND IOALT.INTERFACE_CODE='AWSD'
AND IOAHT.DELIVERY_CUST_ACCT_NUMBER='123'
AND IOALT.PROCESSED_FLAG='P';
REPHEADER OFF;
SET TERMOUT OFF;
COLUMN v_error_desc_var HEADING 'Error Description' FORMAT A50 NOPRINT;

SET FEEDBACK OFF;
SPOOL OFF;

SELECT DECODE(NVL(&v_rowcount,0),0,3,0) v_error_no_var
, DECODE(TO_CHAR(NVL(&v_rowcount,0))
,'0' ,'Shipment_Detail_Report Exception Report Success (No data returned)'
,'Shipment_Detail_Report Exception Report Success (Data returned)') v_error_desc_var
FROM DUAL; 

SET VERIFY ON;
SET ECHO ON;
SET TERMOUT ON;
SET FEEDBACK ON;

WHENEVER SQLERROR CONTINUE;
WHENEVER OSERROR CONTINUE;

EXIT v_exit_code;

SET TERMOUT OFF;
SET VERIFY OFF;
SET ECHO OFF;
SET FEEDBACK OFF;
SET LINESIZE 350;
SET PAGESIZE 6400;

REPHEADER OFF;
SET UNDERLINE OFF

COLUMN creation_date HEADING 'CREATION_DATE,' FORMAT A15 TRUNC PRINT;

SPOOL shipment_detail_report.csv

SET HEADING ON;
SET TERMOUT ON;

SELECT SYSDATE FROM DUAL;

REPHEADER OFF;
SET TERMOUT OFF;
SET FEEDBACK OFF;
SPOOL OFF;
SET VERIFY ON;
SET ECHO ON;
SET TERMOUT ON;
SET FEEDBACK ON;

SYSDATE
18/06/2007 08:08:10