select 'HO' BRANCH,trim(rownum) Seq,trim(EXTRACT(year FROM tbk_date)) Year, trim(EXTRACT(month FROM tbk_date)) Month,
trim(TBK_DATE) BK_Date,trim(TBK_EXECUTIVE_CODE) TC,
trim(TBK_BOOKING_TYPE) BK_TYPE,trim(TBK_BOOKING_NUMBER)BK_No,trim(TBSVH_SV_NUMBER) SV_NO,
replace(trim(TBSVH_PROVIDER),',',' ') Provider,
replace(trim(TBSVD_SERVICE),',',' ') Service,replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') Service_Name,
(SELECT TBSM_SERVICE_TYPE FROM KE014T3.id_tb_service_master WHERE TBSM_SERVICE_CODE=TBSVD_SERVICE AND ROWNUM=1) SERVICE_CLASS,
trim(TBSVD_CURRENCY) CCY, TBSVD_FOREIGN_AMOUNT F_AMOUNT, TBSVD_LOCAL_AMOUNT KES_AMOUNT,
trim(TBSVD_FROM_DATE) CHECKIN,trim(TBSVD_TO_DATE) CHECKOUT
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 TBSVD_SV_SERIAL=TBSVH_SERIAL
and a.tbk_date between '01-JUN-2017' and sysdate and TBSVH_STATUS in('P','T')
and tbsvh_provider in ('WHITESANDT','WHITESANDS','SWH','SV SARO001','NA SARO001','GK-SARO001','GK-WHIT001','SAROVA','STANLEY','LION','SAR','PANAFRIC','SHABA','SAROVA SAL','SV SARO002')
union all
-- SAfaricom
select 'SAFCOM' BRANCH,
trim(rownum) Seq,trim(EXTRACT(year FROM tbk_date)) Year, trim(EXTRACT(month FROM tbk_date)) Month,
trim(TBK_DATE) BK_Date,trim(TBK_EXECUTIVE_CODE) TC,
trim(TBK_BOOKING_TYPE) BK_TYPE,trim(TBK_BOOKING_NUMBER)BK_No,trim(TBSVH_SV_NUMBER) SV_NO,
replace(trim(TBSVH_PROVIDER),',',' ') Provider,
replace(trim(TBSVD_SERVICE),',',' ') Service,replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') Service_Name,
(SELECT TBSM_SERVICE_TYPE FROM KE014T3.id_tb_service_master WHERE TBSM_SERVICE_CODE=TBSVD_SERVICE AND ROWNUM=1) SERVICE_CLASS,
trim(TBSVD_CURRENCY) CCY, TBSVD_FOREIGN_AMOUNT F_AMOUNT, TBSVD_LOCAL_AMOUNT KES_AMOUNT,
trim(TBSVD_FROM_DATE) CHECKIN,trim(TBSVD_TO_DATE) CHECKOUT
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 TBSVH_AP_CODE=AP_CODE
and TBSVD_SV_SERIAL=TBSVH_SERIAL
and a.tbk_date between '01-JUL-2018' and sysdate and TBSVH_STATUS in('P','T')
and tbsvh_provider in ('WHITESANDT','WHITESANDS','SWH','SV SARO001','NA SARO001','GK-SARO001','GK-WHIT001','SAROVA','STANLEY','LION','SAR','PANAFRIC','SHABA','SAROVA SAL','SV SARO002')
