select
'HO' sv_Branch,
trim(EXTRACT(year FROM tbk_date)) Year, 
trim(EXTRACT(month FROM tbk_date)) Month,
TBK_CUSTOMER CUSTOMER_CODE, 
(SELECT AR_NAME FROM ke014t3_support.ID_AR_MASTER WHERE AR_CODE=TBK_CUSTOMER AND ROWNUM=1) CUSTOMER_NAME,
trim(TBK_DATE) BK_Date,
trim(TBK_EXECUTIVE_CODE) TC,
--replace(trim((select upper( EXEC_NAME)  from ke014t3_support.ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE)),',',' ') TC_Name,
trim(TBK_BOOKING_TYPE) BK_TYPE,
trim(TBK_BOOKING_NUMBER)BK_No,
trim(TBSVH_SV_NUMBER) SV_NO,
(select Inv_nr from ke014t3_support.BCD_AUDIT_SAFCOM_SV_N_INVOICES where invsearchsv=tbsvh_sv_number and rownum=1) invoice,
(select BPV_NO from ke014t3_support.bcd_ict_pd_sv_vw_07_2019_ho where SV_NO=tbsvh_sv_number and rownum=1) BPV,
(select ap_code from ke014t3_support.bcd_ict_pd_sv_vw_07_2019_ho where SV_NO=tbsvh_sv_number and rownum=1) BPV_PAID_ACT,
--(select apt_number from ke014t3.id_ap_trans where apt_detail_3=TBK_BOOKING_NUMBER and apt_detail_4=TBSVH_SV_NUMBER and rownum=1) Adjusted_Via,
replace(trim(TBSVH_PROVIDER),',',' ') Provider,
replace(trim((select tbpm_name from id_tb_provider_master where tbpm_provider=tbsvh_provider and rownum=1)),',',' ') Provider_Name,
TBSVH_AP_CODE AP_Code,
--replace(replace((Select AP_NAME from ke014t3_support.id_ap_master where ap_code=tbsvh_ap_code and rownum=1),',','-'),'/','-') AP_Name,
d.AP_VAT_REGISTRATION_NO SUP_PIN,
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,
--(select -APT_LOCAL_AMOUNT from ke014t3_support.id_ap_trans where apt_detail_3=TBK_BOOKING_NUMBER and apt_detail_4=TBSVH_SV_NUMBER and rownum=1) Adjusted_Amount,
--(TBSVD_LOCAL_AMOUNT+nvl((select -APT_LOCAL_AMOUNT from ke014t3_support.id_ap_trans where apt_detail_3=TBK_BOOKING_NUMBER and apt_detail_4=TBSVH_SV_NUMBER and rownum=1),0)) New_INV_Value,
trim(TBSVD_FROM_DATE) CHECKIN,
trim(TBSVD_TO_DATE) CHECKOUT,
replace(trim(TBK_TMP_CUST_NAME),',',' ') PAX_NAME,
decode(tbk_next_process_number,3,'OPEN',4,'Partially Supplied',5,'Fully Supplied',6,'Partially Invoiced','Fully Invoiced') BK_Status,
decode(tbsvh_status,'T','NOTPOSTED','POSTED') SV_STATUS
--(select apt_Description from ke014t3_support.id_ap_trans where apt_detail_3=TBK_BOOKING_NUMBER and apt_detail_4=TBSVH_SV_NUMBER and rownum=1) Adjusted_Reason
from
ke014t3.id_tb_booking_header a,ke014t3.ID_TB_SERVICE_VOUCHER_DETAILS B, ke014t3.id_tb_service_voucher_header c,ke014t3_support.id_ap_master d
where TBK_BOOKING_NUMBER=TBSVD_BOOKING_NUMBER
and tbk_status<>'F' 
and a.tbk_customer NOT 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-JAN-2017' and sysdate and TBSVH_STATUS in('P','T')
and tbsvh_provider='POA'
