select 
(select Branch from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) SV_Branch,
(select SV_DATE from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) sv_date,
(select Status from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) SV_Status,
(select Provider from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) SV_Provider,
(select Provider_Name from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) SV_Provider_Name,
(select AP_Code from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) SV_AP_CODE,
(select AP_NAME from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) SV_AP_NAME,
(select SV_CCY from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) SV_CCY,
(select SV_VALUE from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) SV_VALUE,
PH_DOC_DATE, PH_DOC_NUMBER, ALOC_DETAIL_4 IB, ALOC_DETAIL_5 SV,
(select invoice_number from ke074t3_support.BCD_ICT_GET_INV_4SV_COMBI_NEW where sv_number=aloc_detail_5 and rownum=1) Invoice,
(select receipt from ke074t3_support.BCD_ICT_S0171_RECEIPT_WITH_INV where invoice=
(select invoice_number from ke074t3_support.BCD_ICT_GET_INV_4SV_COMBI_NEW where sv_number=aloc_detail_5 and rownum=1)
and rownum=1) Receipt,
PH_CURRENCY Paid_CCY, PH_CHEQUE_NUMBER CHQ, PH_CASH_BANK_CODE Bank, ALOC_FOREIGN_AMOUNT Paid_Amount
from ke074t3_support.id_payment_allocation_details a, ke074t3_support.id_payment_header b
where 
ph_doc_number=aloc_doc_number
and ALOC_DETAIL_1<>'APVR'
and ALOC_DETAIL_5 like 'SV%'
and b.PH_STATUS='P'
and (select SV_DATE from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1) <'01-JUL-2018'
and (select Branch from ke014t3_support.ho_and_SAFCOM_SV where booking=aloc_detail_4 and sv=aloc_detail_5 and rownum=1)='HO'


--select * from all_objects where object_name like '%BCD_ICT_%' and object_type='VIEW'

--select * from BCD_ICT_GET_INV_4SV_COMBI_NEW 

