select 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,
replace(trim((select upper( EXEC_NAME)  from ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE)),',',' ') TC_Name,
replace(trim(TBK_FLEX_2),',','') PR,replace(trim(TBK_FLEX_3),',','') PO,replace(trim(TBK_FLEX_4),',',' ') Booker,trim(TBK_BOOKING_TYPE) BK_TYPE,trim(TBK_BOOKING_NUMBER)BK_No,trim(TBSVH_SV_NUMBER) SV_NO,
replace(trim(TBSVH_PROVIDER),',',' ') Proider,replace(trim((select tbpm_name from id_tb_provider_master where tbpm_provider=tbsvh_provider and rownum=1)),',',' ') Provider_Name,replace(trim(TBSVD_SERVICE),',',' ') Service,replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') Service_Name,
trim(TBSVD_CURRENCY) CCY, TBSVD_FOREIGN_AMOUNT F_AMOUNT, TBSVD_LOCAL_AMOUNT KES_AMOUNT,trim(TBSVD_FROM_DATE) CHECKIN,trim(TBSVD_TO_DATE) CHECKOUT,
--decode(tbsvh_status,'T','NOTINVOICED','INVOICED') INV_STATUS,
(select Inv_nr from ke014t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1) invoice,
Ke074t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number) Invoice_Status,
replace(trim(TBK_TMP_CUST_NAME),',',' ') PAX_NAME
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')
and TBSVH_AP_CODE=AP_CODE
and TBSVD_SV_SERIAL=TBSVH_SERIAL
and a.tbk_date between '01-JAN-2017' and sysdate and TBSVH_STATUS in('V')
and TBSVH_SV_NUMBER in (select * from ke074t3_support.SAFCOM_UNBILLED_27MIL)
union all
select 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,
replace(trim((select upper( EXEC_NAME)  from ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE)),',',' ') TC_Name,
replace(trim(TBK_FLEX_2),',','') PR,replace(trim(TBK_FLEX_3),',','') PO,replace(trim(TBK_FLEX_4),',',' ') Booker,trim(TBK_BOOKING_TYPE) BK_TYPE,trim(TBK_BOOKING_NUMBER)BK_No,trim(TBSVH_SV_NUMBER) SV_NO,
replace(trim(TBSVH_PROVIDER),',',' ') Proider,replace(trim((select tbpm_name from id_tb_provider_master where tbpm_provider=tbsvh_provider and rownum=1)),',',' ') Provider_Name,replace(trim(TBSVD_SERVICE),',',' ') Service,replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' ') Service_Name,
trim(TBSVD_CURRENCY) CCY, TBSVD_FOREIGN_AMOUNT F_AMOUNT, TBSVD_LOCAL_AMOUNT KES_AMOUNT,trim(TBSVD_FROM_DATE) CHECKIN,trim(TBSVD_TO_DATE) CHECKOUT,
--decode(tbsvh_status,'T','NOTINVOICED','INVOICED') INV_STATUS,
(select Inv_nr from ke074t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1) invoice,
Ke074t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number) Invoice_Status,
replace(trim(TBK_TMP_CUST_NAME),',',' ') PAX_NAME
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')
and TBSVH_AP_CODE=AP_CODE
and TBSVD_SV_SERIAL=TBSVH_SERIAL
and a.tbk_date between '01-JAN-2017' and sysdate and TBSVH_STATUS in('V')
and TBSVH_SV_NUMBER in (select * from ke074t3_support.SAFCOM_UNBILLED_27MIL)