REM ---------------------------------------------------------
REM About Author of this Script
REM Ronney Rajapathirana
REM Created for BCD Travels
REM Project : eTours 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;

REM	mem_filename:=Z:\Staff-Files\eTours-Bulk_Data\exported_etours_data_.&sysdt..csv;

REM	spool &mem_filename.;


	spool D:\eTravelVer3\eToursBackup\2_1HR_MICE_safcom_2019_not_invoiced_&sysdt..csv;

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

select  
'BRANCH'||','||'SEQ'||','||'YEAR'||','||'MONTH'||','||'CUSTOMER_CODE'||','||'BK_DATE'||','||'TC'||','||'BK_TYPE'||','||'BK_NO'||','||'SV_NO'||','||'PROVIDER'||','||'AP_CODE'||','||'SUP_PIN'||','||'SERVICE'||','||'SERVICE_NAME'||','||'CCY'||','||'F_AMOUNT'||','||'KES_AMOUNT'||','||'CHECKIN'||','||'CHECKOUT'||','||'INVOICE'||','||'INVOICE_STATUS'||','||'PAX_NAME'||','||'BK_STATUS'||','||'SV_STATUS'
from dual;

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

COLUMN TBSVD_FOREIGN_AMOUNT FORMAT 99,999,999.99
COLUMN TBSVD_LOCAL_AMOUNT FORMAT 99,999,999.99

COLUMN TBSVD_FOREIGN_AMOUNT FORMAT 99,999,999.99
COLUMN TBSVD_LOCAL_AMOUNT FORMAT 99,999,999.99

REM ---------------------------------------------------------
REM Selecting Data from Data Tables
REM ---------------------------------------------------------
REM =========================================================================================================================================JUNE 2019================
select 'HO' ||','||
trim(rownum) ||','||
trim(EXTRACT(year FROM tbk_date)) ||','||
trim(EXTRACT(month FROM tbk_date)) ||','||
TBK_CUSTOMER ||','||
trim(TBK_DATE) ||','||
trim(TBK_EXECUTIVE_CODE) ||','||
trim(TBK_BOOKING_TYPE) ||','||
trim(TBK_BOOKING_NUMBER)||','||
trim(TBSVH_SV_NUMBER) ||','||
replace(trim(TBSVH_PROVIDER),',',' ') ||','||
TBSVH_AP_CODE ||','||
d.AP_VAT_REGISTRATION_NO ||','||
replace(trim(TBSVD_SERVICE),',',' ') ||','||
replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') ||','||
trim(TBSVD_CURRENCY) ||','||
TBSVD_FOREIGN_AMOUNT ||','||
TBSVD_LOCAL_AMOUNT ||','||
trim(TBSVD_FROM_DATE) ||','||
trim(TBSVD_TO_DATE) ||','||
(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1) ||','||
Ke014t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number) ||','||
replace(trim(TBK_TMP_CUST_NAME),',',' ') ||','||
decode(tbk_next_process_number,3,'OPEN',4,'Partially Supplied',5,'Fully Supplied',6,'Partially Invoiced','Fully Invoiced') ||','||
decode(tbsvh_status,'T','NOTPOSTED','POSTED')
from
ke014t3.id_tb_booking_header a,ke014t3.ID_TB_SERVICE_VOUCHER_DETAILS B, ke014t3.id_tb_service_voucher_header c,ke014t3.id_ap_master d
where TBK_BOOKING_NUMBER=TBSVD_BOOKING_NUMBER
and tbk_status<>'F'
and a.tbk_customer IN ('S0171','S0175','S0177','S0171A','S0171B','S0171C')
and TBSVH_AP_CODE=AP_CODE
and TBK_NEXT_PROCESS_NUMBER<7
and TBSVD_SV_SERIAL=TBSVH_SERIAL
and a.tbk_date between '01-JUN-2017' and sysdate and TBSVH_STATUS in('P','T')
and 
(TBSVD_LOCAL_AMOUNT+nvl((select -APT_LOCAL_AMOUNT from ke074t3_support.id_ap_trans where apt_detail_3=TBK_BOOKING_NUMBER and apt_detail_4=TBSVH_SV_NUMBER and rownum=1),0))>0;
select 'SAFCOM' ||','||
trim(rownum) ||','||
trim(EXTRACT(year FROM tbk_date)) ||','||
trim(EXTRACT(month FROM tbk_date)) ||','||
TBK_CUSTOMER ||','||
trim(TBK_DATE) ||','||
trim(TBK_EXECUTIVE_CODE) ||','||
trim(TBK_BOOKING_TYPE) ||','||
trim(TBK_BOOKING_NUMBER)||','||
trim(TBSVH_SV_NUMBER) ||','||
replace(trim(TBSVH_PROVIDER),',',' ') ||','||
TBSVH_AP_CODE ||','||
d.AP_VAT_REGISTRATION_NO ||','||
replace(trim(TBSVD_SERVICE),',',' ') ||','||
replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') ||','||
trim(TBSVD_CURRENCY) ||','||
TBSVD_FOREIGN_AMOUNT ||','||
TBSVD_LOCAL_AMOUNT ||','||
trim(TBSVD_FROM_DATE) ||','||
trim(TBSVD_TO_DATE) ||','||
(select Inv_nr from ke074t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1) ||','||
Ke074t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number) ||','||
replace(trim(TBK_TMP_CUST_NAME),',',' ') ||','||
decode(tbk_next_process_number,3,'OPEN',4,'Partially Supplied',5,'Fully Supplied',6,'Partially Invoiced','Fully Invoiced') ||','||
decode(tbsvh_status,'T','NOTPOSTED','POSTED') 
from
ke074t3.id_tb_booking_header a,ke074t3.ID_TB_SERVICE_VOUCHER_DETAILS B, ke074t3.id_tb_service_voucher_header c,ke074t3.id_ap_master d
where TBK_BOOKING_NUMBER=TBSVD_BOOKING_NUMBER
and tbk_status<>'F'
and a.tbk_customer IN ('S0171','S0175','S0177','S0171A','S0171B','S0171C')
and TBK_NEXT_PROCESS_NUMBER<7
and TBSVH_AP_CODE=AP_CODE
and TBSVD_SV_SERIAL=TBSVH_SERIAL
and a.tbk_date between '01-JUN-2018' and sysdate and TBSVH_STATUS in('P','T')
and 
(TBSVD_LOCAL_AMOUNT+nvl((select -APT_LOCAL_AMOUNT from ke074t3_support.id_ap_trans where apt_detail_3=TBK_BOOKING_NUMBER and apt_detail_4=TBSVH_SV_NUMBER and rownum=1),0))>0;
REM ==========================================================================================================================================JUNE 2019===============
spool off;

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


quit;