Query to get Purchase order(PO) header detail in oracle fusion

 select distinct 
hou.name Company_Code,
poh.segment1 Purchase_Order_Number,
poh.CURRENCY_CODE  Currency,
XX.SEGMENT1 Release_Number,
Null E_Invoic_Flag,
(
select username from per_users where PERSON_ID= poh.agent_id and  
TRUNC(SYSDATE) BETWEEN (START_DATE) AND  NVL(END_DATE,SYSDATE+1)
and rownum=1
) GR_Responsible_SSO,
(select full_name from Per_person_names_f ppf  where PERSON_ID =  poh.agent_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND  NVL(PPF.EFFECTIVE_END_DATE,SYSDATE+1)
and rownum=1
) GR_Responsible_Name, 
(select EMAIL_ADDRESS from PER_EMAIL_ADDRESSES pea  where PERSON_ID =  poh.agent_id 
AND TRUNC(SYSDATE) BETWEEN (pea.DATE_FROM) AND  NVL(pea.DATE_TO,SYSDATE+1)
and rownum=1
) GR_Responsible_Email ,
(
select username from per_users where PERSON_ID= poh.agent_id and  
TRUNC(SYSDATE) BETWEEN (START_DATE) AND  NVL(END_DATE,SYSDATE+1)
and rownum=1
) PO_Buyer_SSO,
(
select full_name from Per_person_names_f ppf  where PERSON_ID =  poh.agent_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND  NVL(PPF.EFFECTIVE_END_DATE,SYSDATE+1)
and rownum=1
) PO_Buyer_Name, 
(
select EMAIL_ADDRESS from PER_EMAIL_ADDRESSES pea  where PERSON_ID =  poh.agent_id 
AND TRUNC(SYSDATE) BETWEEN (pea.DATE_FROM) AND  NVL(pea.DATE_TO,SYSDATE+1)
and rownum=1
) PO_Buyer_Email ,
(
select PU.username from Per_person_names_f ppf, per_users pu  
where ppf.PERSON_ID =  pu.person_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND TRUNC(SYSDATE) BETWEEN (pu.START_DATE) AND  NVL(pu.END_DATE,SYSDATE+1)
AND upper(PPF.first_Name||'.'||ppf.last_name)= poh.created_by
and rownum=1
) PO_Creator_SSO,
poh.created_by PO_Creator_Name, 
(
select EMAIL_ADDRESS from Per_person_names_f ppf, PER_EMAIL_ADDRESSES pea  
where ppf.PERSON_ID =  pea.person_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND TRUNC(SYSDATE) BETWEEN (pea.DATE_FROM) AND  NVL(pea.DATE_TO,SYSDATE+1)
AND upper(PPF.first_Name||'.'||ppf.last_name)= poh.created_by
and rownum=1
) PO_Creator_Email_Address,
(
select pea.username from Per_person_names_f ppf, per_users pea  
where ppf.PERSON_ID =  pea.person_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND TRUNC(SYSDATE) BETWEEN (pea.start_date) AND  NVL(pea.end_Date,SYSDATE+1)
AND upper(PPF.first_Name||'.'||ppf.last_name) in 
(
(
select prda.created_by from  po_distributions_All pda ,
POR_REQ_DISTRIBUTIONS_ALL prda 
where pda.req_distribution_id= prda.DISTRIBUTION_ID
and pda.po_header_id= poh.po_header_id
and rownum=1
)
)
and rownum=1
) PO_Requestor_SSO, 
(
select prda.created_by from  po_distributions_All pda ,
POR_REQ_DISTRIBUTIONS_ALL prda 
where pda.req_distribution_id= prda.DISTRIBUTION_ID
and pda.po_header_id= poh.po_header_id
and rownum=1
) PO_Requestor_Name,
(
select EMAIL_ADDRESS from Per_person_names_f ppf, PER_EMAIL_ADDRESSES pea  
where ppf.PERSON_ID =  pea.person_id 
AND TRUNC(SYSDATE) BETWEEN (PPF.EFFECTIVE_START_DATE) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE)
AND TRUNC(SYSDATE) BETWEEN (pea.DATE_FROM) AND  NVL(pea.DATE_TO,SYSDATE+1)
AND upper(PPF.first_Name||'.'||ppf.last_name) in 
(
(
select prda.created_by from  po_distributions_All pda ,
POR_REQ_DISTRIBUTIONS_ALL prda 
where pda.req_distribution_id= prda.DISTRIBUTION_ID
and pda.po_header_id= poh.po_header_id
and rownum=1
)
)
and rownum=1
) PO_Requestor_Email,
nvl((select sum(nvl(pol.unit_price,0) * nvl(pol.quantity,0)) from po_lines_All pol where po_header_id=poh.po_header_id),0) Net_Amount,
nvl((
select sum(nvl(pol.unit_price,0) * nvl(pol.quantity,0))+ sum(nvl(RECOVERABLE_TAX,0)+nvl(NONRECOVERABLE_TAX,0)) from po_lines_All pol , po_distributions_All pda
where pol.po_header_id=poh.po_header_id
and pol.PO_LINE_ID=pda.PO_LINE_ID
),0) Total_Amount,
to_char(poh.creation_Date,'yyyy-MM-dd') PO_Date,
 poh.DOCUMENT_STATUS PO_Status ,
 poh.REVISION_NUM PO_Version,
 poh.TYPE_LOOKUP_CODE  Purchase_Order_Type, 
 decode(poh.ship_to_location_id,null,null,
 (select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'||hou.name||'-'||(SELECT location_id FROM hr_locations
WHERE location_id = poh.ship_to_location_id
)) Ship_to_Location_Code_Unique_Key,
 xep.NAME Legal_Entity_Name,
 poh.Last_update_Date,
 poh.attribute1  Temp_1,
 poh.attribute2  Temp_2,
 poh.attribute3  Temp_3,
 poh.attribute4  Temp_4,
 poh.attribute5  Temp_5,
 poh.attribute6  Temp_6,
 poh.attribute7  Temp_7,
 poh.attribute8  Temp_8,
 poh.attribute9  Temp_9,
 poh.attribute10  Temp_10
from po_headers_All poh, HR_ORGANIZATION_UNITS_F_TL hou, poz_suppliers ps, poz_supplier_sites_All_m possa,
ap_terms att, xle_entity_profiles xep,
(
select poh.segment1 , pol.from_header_id
from PO_LINES_ALL POL,po_headers_All POH 
where pol.po_header_id=POH.PO_HEADER_ID
) xx
where poh.prc_bu_id =  hou.ORGANIZATION_id
and hoU.LANGUAGE = USERENV('LANG')
and poh.vendor_id=ps.vendor_id(+)
and poh.vendor_id=possa.vendor_id(+)
and poh.Vendor_Site_id=possa.Vendor_Site_id(+)
and poh.prc_bu_id=possa.prc_bu_id
and poh.terms_id = att.term_id
and poh.SOLDTO_LE_ID =  xep.legal_entity_id(+)
and poh.approved_flag ='Y'
and poh.TYPE_LOOKUP_CODE not in ('BLANKET','CONTRACT')
and poh.DOCUMENT_STATUS not in ('CANCELED','REJECTED','FINALLY CLOSED','CLOSED','CLOSED FOR INVOICING','INCOMPLETE')
and poh.po_header_id= xx.from_header_id(+)
and hou.name = nvl(:P_BU_NAME,hou.name)
and trunc(poh.Last_update_Date) between NVL(:P_FROM_DATE,trunc(poh.Last_update_Date)) and NVL(:P_END_DATE,trunc(poh.Last_update_Date))