REM ---------------------------------------------------------
REM About Author of this Script
REM Ronney Rajapathirana
REM Created for BCD Travels
REM Project : Ticket Exception Automation Project
REM ---------------------------------------------------------

REM ---------------------------------------------------------
REM Setting up the Environmental Variables
REM ---------------------------------------------------------

set feedback off;
set echo off;
set verify off;

set pagesize 50000;
set pages 0;
SET LINESIZE 20000;

set term off;
set heading off;
set headsep off;

set termout off;

set trimspool on;
set wrap off;
SET SQLBLANKLINES Off;


REM ---------------------------------------------------------
REM Configuring the Spool File Name with Date and Time Stamp
REM ---------------------------------------------------------

	col sysdt noprint new_value sysdt;
   	SELECT TO_CHAR(SYSDATE, 'ddmmyyyy_hh24miss') sysdt FROM DUAL;


	spool D:\eTravelVer3\eToursBackup\exported_Ticket_data_&sysdt..csv;

REM ---------------------------------------------------------
REM Select the Column Headings
REM ---------------------------------------------------------

select 'BKD_YEAR'||','||'BKD_MONTH'||','||'CUSTOMER'||','||'SALE_DATE'||','||'TICKET_NO'||','||'INVOICE'||','||'FLOWN_AL'||','||'AIRLINE_NAME'||','||'ROUTINE'||','||
'REGION'||','||'PAX'||','||'TC'||','||'TC_NAME'||','||'PNR'||','||'BOOKER'||','||'PR_TR'||','||'CCY'||','||'RECEIVABLE_AMT'||','||'INV_STATUS'||','||'EX_RATE'||','||'LOCAL_VALUE'||','||'MODIFIED BY'||','||'MODEFIED ON' from dual;


REM ---------------------------------------------------------
REM Formatting Columns
REM ---------------------------------------------------------

COLUMN TICKET_NET_RECEIVABLE FORMAT 99,999,999.99

REM ---------------------------------------------------------
REM Selecting Data from Data Tables
REM ---------------------------------------------------------

select to_number(EXTRACT( year FROM ticket_date))||','||to_number(EXTRACT(month FROM ticket_date))||','||TICKET_CUSTOMER_1||','||ticket_date||','||to_char(ticket_number)||','|| to_char(TICKET_DOC_NUMBER1)
||','||to_char(REPORING_AIRLINE)||','||to_char(AIRLINE_NAME)||','||TICKET_ROUTING||','||TICKET_LOCAL_INTER||','||
substr(trim(to_char(replace(replace(replace(TICKET_PAX_NAME,'&','-'),'/','-'),',','-'))),0,50)
||','||TICKET_BOOKING_CLERK||','||(select upper( EXEC_NAME)  from ID_EXECUTIVE_MASTER where TICKET_BOOKING_CLERK=EXEC_CODE)||','||
to_char(TICKET_FLEX_V_2)||','||approved_by||','||PROJECT_NUMBER||','||TICKET_currency||','||TICKET_NET_RECEIVABLE||','||decode(TICKET_DOC_NUMBER1,'','NOTINVOICED','INVOICED')||','||TICKET_EX_RATE||','||round(TICKET_NET_RECEIVABLE*TICKET_EX_RATE,2)||','||ticket_created_by||','||Ticket_created_on
from ke014t3.ticket_view_t3 where TICKET_CUSTOMER_1='S0171' and Ticket_date between '01-JUN-2017' and '30-JUN-2018' and ticket_mode=2 and TICKET_DOC_NUMBER1 is null
order by REPORING_AIRLINE, ticket_date;
select to_number(EXTRACT( year FROM ticket_date))||','||to_number(EXTRACT(month FROM ticket_date))||','||ticket_customer_1||','||ticket_date||','||to_char(ticket_number)||','|| to_char(TICKET_DOC_NUMBER1)
||','||to_char(REPORING_AIRLINE)||','||to_char(AIRLINE_NAME)||','||TICKET_ROUTING||','||TICKET_LOCAL_INTER||','||
substr(trim(to_char(replace(replace(replace(TICKET_PAX_NAME,'&','-'),'/','-'),',','-'))),0,50)
||','||TICKET_BOOKING_CLERK||','||(select upper( EXEC_NAME)  from ID_EXECUTIVE_MASTER where TICKET_BOOKING_CLERK=EXEC_CODE)||','||
to_char(TICKET_FLEX_V_2)||','||approved_by||','||PROJECT_NUMBER||','||TICKET_currency||','||TICKET_NET_RECEIVABLE||','||decode(TICKET_DOC_NUMBER1,'','NOTINVOICED','INVOICED')||','||TICKET_EX_RATE||','||round(TICKET_NET_RECEIVABLE*TICKET_EX_RATE,2)||','||ticket_created_by||','||Ticket_created_on
from ke074t3.ticket_view_t3 where Ticket_date between '01-JUL-2018' and sysdate and ticket_mode=2 and TICKET_DOC_NUMBER1 is null
order by REPORING_AIRLINE, ticket_date;

spool off;


REM ---------------------------------------------------------
REM Data File Has been Created
REM ---------------------------------------------------------

quit;