select 'HO' BRANCH,
trim(rownum) Seq,
trim(EXTRACT(year FROM tbk_date)) Year, 
trim(EXTRACT(month FROM tbk_date)) Month,
TBK_CUSTOMER CUSTOMER_CODE, 
(SELECT AR_NAME FROM 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 ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE)),',',' ') TC_Name,
DECODE((SELECT USER_STATUS FROM KE014T3.ID_USER_MASTER WHERE USER_CODE=TBK_EXECUTIVE_CODE AND ROWNUM=1),'A','ACTIVE','NOT ACTIVE') EMP_STATUS,
trim(TBK_BOOKING_TYPE) BK_TYPE,
trim(TBK_BOOKING_NUMBER)BK_No,
trim(TBSVH_SV_NUMBER) SV_NO,
(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,
(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1) invoice,
(select SUB_AR_code  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)) Invoiced_to_AR,
(select SUB_AR_DATE  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)) Invoice_date,
(select SUB_AR_USER  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)) Invoice_Created_by,
(select SUB_AR_status  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)) Invoice_Now_Status,

Ke014t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number) Invoice_Status,
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.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')
union all
select 'SAFCOM' BRANCH,
trim(rownum) Seq,
trim(EXTRACT(year FROM tbk_date)) Year, 
trim(EXTRACT(month FROM tbk_date)) Month,
TBK_CUSTOMER CUSTOMER_CODE, 
(SELECT AR_NAME FROM ke074t3.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 ke074t3.ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE)),',',' ') TC_Name,
DECODE((SELECT USER_STATUS FROM ke074t3.ID_USER_MASTER WHERE USER_CODE=TBK_EXECUTIVE_CODE AND ROWNUM=1),'A','ACTIVE','NOT ACTIVE') EMP_STATUS,
trim(TBK_BOOKING_TYPE) BK_TYPE,
trim(TBK_BOOKING_NUMBER)BK_No,
trim(TBSVH_SV_NUMBER) SV_NO,
(select apt_number from ke074t3.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 ke074t3.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 ke074t3.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 KE074T3.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 ke074t3.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 ke074t3.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,
(select Inv_nr from ke074t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1) invoice,
(select SUB_AR_code  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)) Invoiced_to_AR,
(select SUB_AR_DATE  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)) Invoice_date,
(select SUB_AR_USER  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)) Invoice_Created_by,
(select SUB_AR_status  from id_tb_ar_header where SUB_AR_DOC_NUMBER=(select Inv_nr from ke014t3.BCD_AUDIT_SAFCOM_SV_N_INVOICES where tbsvh_sv_number=invsearchsv and rownum=1)) Invoice_Now_Status,
Ke074t3_support.BCD_ICT_GET_INV_STATUS_4SV(tbsvh_sv_number) Invoice_Status,
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 ke074t3.id_ap_trans where apt_detail_3=TBK_BOOKING_NUMBER and apt_detail_4=TBSVH_SV_NUMBER and rownum=1) Adjusted_Reason
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')
