Query to get GRN Header detail in Oracle fusion

 SELECT DISTINCT  
   hout.name Company_Code,
   (select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1) ||'-'||hout.name Company_Code_Unique_Key,   
   pha.segment1 PO_Number,
   rsh.shipment_header_id GRN_Id,
rsh.receipt_num GRN_Number,
to_char(rsh.CREATION_DATE,'yyyy-MM-dd') GRN_Date,
to_char(rsh.expected_receipt_date,'yyyy-MM-dd') Expected_Receipt_Date,
to_char(rsh.last_update_date,'yyyy-MM-dd HH:mm:ss') last_update_date,
rsh.attribute1 Temp_1,
rsh.attribute1 Temp_2,
rsh.attribute1 Temp_3,
rsh.attribute1 Temp_4,
rsh.attribute1 Temp_5,
rsh.attribute1 Temp_6,
rsh.attribute1 Temp_7,
rsh.attribute1 Temp_8,
rsh.attribute1 Temp_9,
rsh.attribute1 Temp_10
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_headers_All PHA,
poz_suppliers ps, 
hz_parties hp, 
poz_supplier_sites_All_m pssa ,
hr_organization_units_f_tl hout,
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 rsh.shipment_header_id=rsl.shipment_header_id
and  rsl.po_header_id=PHA.po_header_id
and rsh.vendor_id= ps.vendor_id
and  ps.party_id= hp.party_id
and rsh.vendor_Site_id=pssa.vendor_Site_id
and ps.vendor_id=pssa.vendor_id
and pha.prc_bu_id=hout.organization_id
and hout.LANGUAGE = USERENV('LANG')
and PHA.DOCUMENT_STATUS not in ('CANCELED','REJECTED','FINALLY CLOSED','CLOSED','CLOSED FOR INVOICING'
)
and PHA.po_header_id= xx.from_header_id(+)
and xep.LEGAL_ENTITY_ID =PHA.soldto_le_id
and hout.name = NVL(:P_BU_NAME,hout.name)
and trunc(rsh.last_update_date) between nvl(:P_FROM_DATE,trunc(rsh.last_update_date)) and nvl(:P_END_DATE,trunc(rsh.last_update_date))

Query to get purchase order(PO) line detail in oracle fusion

 select
hou.name Company_Code,
poh.segment1 PO_Number,
pol.LINE_NUM PO_Line_No,
pol.po_line_id  PO_Line_Id,
null Shipment_Id,
pol.LINE_STATUS PO_Line_Status,
plla.SCHEDULE_STATUS Schedule_Status,
plla.SHIPMENT_NUM Schedule_Number,
plla.QUANTITY_RECEIVED Shipment_Quantity_Received,
plla.QUANTITY_ACCEPTED Shipment_Quantity_Accepted,
plla.QUANTITY_BILLED Shipment_Quantity_Billed,
plla.QUANTITY_CANCELLED Shipment_Quantity_Cancelled,
plla.QUANTITY  Shipment_Quantity_Ordered,
plla.CANCEL_FLAG Shipment_Cancel_Flag,
replace(XX.SEGMENT1,'-',null) Release_Number,
poh.DOCUMENT_STATUS Authorization_Status, 
CASE
when plla.RECEIPT_REQUIRED_FLAG = 'N'
then '2-Way'
when  plla.RECEIPT_REQUIRED_FLAG = 'Y' AND plla.INSPECTION_REQUIRED_FLAG ='N'
THEN '3-Way'
when  plla.RECEIPT_REQUIRED_FLAG = 'Y' AND plla.INSPECTION_REQUIRED_FLAG ='Y'
THEN '4-Way'
end
Match_Approval_Level,
pol.QUANTITY PO_Line_Quantity,
pol.QUANTITY * pol.UNIT_PRICE PO_Line_Amount,
null Schedule_Qty, 
null Schedule_Amount, 
pol.QUANTITY - plla.QUANTITY_RECEIVED Shipment_Remaining_Qty,
(pol.QUANTITY - plla.QUANTITY_RECEIVED)*pol.UNIT_PRICE  Shipment_Remaining_Amount,
(select ITEM_NUMBER from egp_system_items_b where inventory_item_id= pol.item_id and rownum=1) Material_No ,
to_char(plla.NEED_BY_DATE ,'yyyy-MM-dd') Need_By_Date,
pol.ITEM_DESCRIPTION Product_Description, 
to_char(plla.PROMISED_DATE ,'yyyy-MM-dd') Promised_Date,
pol.UNIT_PRICE Unit_Price,
pol.LIST_PRICE_PER_UNIT Price_Unit,
pol.UOM_CODE UOM,
pol.tax_code_id Tax_Code_Unique_Key,
null UNSPSC_Code,
pol.last_update_Date,
pol.attribute1 Temp_1,
pol.attribute2 Temp_2,
pol.attribute3 Temp_3,
pol.attribute4 Temp_4,
pol.attribute5 Temp_5,
pol.attribute6 Temp_6,
pol.attribute7 Temp_7,
pol.attribute8 Temp_8,
pol.attribute9 Temp_9,
pol.attribute10 Temp_10   
from po_headers_all poh, 
po_lines_all pol, 
po_line_locations_All plla,
--PO_DISTRIBUTIONS_ALL pda,
HR_ORGANIZATION_UNITS_F_TL hou, 
hr_locations hl,
PO_LINE_TYPES_TL pltt,
(
select distinct '-'||poh.segment1 segment1, pol.from_header_id, pol.from_line_id
from PO_LINES_ALL POL,po_headers_All POH 
where pol.po_header_id=POH.PO_HEADER_ID
) xx
where poh.po_header_id=pol.po_header_id(+)
and pol.po_line_id=plla.po_line_id(+)
--and pol.po_header_id=pda.po_header_id(+)
--and pol.po_line_id=pda.po_line_id(+)
--and plla.line_location_id=pda.line_location_id(+)
and poh.PRC_BU_ID=hou.ORGANIZATION_id
and hou.LANGUAGE = USERENV('LANG')
and plla.SHIP_TO_LOCATION_ID= hl.location_id(+)
and pol.LINE_TYPE_ID =  pltt.LINE_TYPE_ID(+)
and hou.LANGUAGE=  pltt.LANGUAGE
--and poh.segment1='80306'
--and nvl(poh.LINE_STATUS,'XX')  not in ('CANCELED','CLOSED', 'REJECTED','CLOSED FOR INVOICING','FINALLY CLOSED','CLOSED FOR RECEIVING')
and poh.DOCUMENT_STATUS not in ('CANCELED','INCOMPLETE','REJECTED','FINALLY CLOSED','CLOSED','CLOSED FOR INVOICING')
and poh.po_header_id= xx.from_header_id(+)
and pol.PO_Line_Id=xx.from_line_id(+)
and hou.name= nvl(:P_bu_name,hou.name)
and trunc(pol.last_update_Date) between nvl(:P_FROM_DATE,trunc(pol.Last_update_Date)) and nvl(:P_END_DATE,trunc(pol.Last_update_Date))

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))

Query to get supplier bank master data in oracle fusion

 select 
    PS.segment1    Supplier_Number,
pvs.vendor_Site_id Supplier_Site_Id,
    pvs.vendor_site_code Supplier_Site_Code,   
    IEBA.EXT_BANK_ACCOUNT_ID Bank_Account_Id,
    ieba.BANK_ACCOUNT_NUM Bank_Account_No,
  TO_cHAR(ieba.START_DATE,'yyyy-MM-dd') Bank_Account_Start_Date,
TO_cHAR(ieba.end_DATE,'yyyy-MM-dd') Bank_Account_end_Date,
decode(hp_bank.STATus,'A','Y','I','N',hp_bank.STATus) Bank_Active_Status,
hp_branch.Party_Name Bank_Branch_Name,
        hp_branch.CITY Bank_City,  
    hp_branch.COUNTRY Bank_Country_Code ,
    hp_bank.PArty_Name Bank_Name,
hp_branch.ADDRESS1 Bank_Street1,
    hp_branch.ADDRESS2 Bank_Street2,
    hp_branch.ADDRESS3 Bank_Street3,
    ieba.BANK_ACCOUNT_NAME Bank_Account_Name, 
    hp_branch.ADDRESS4 Bank_Name_Alt ,
    ieba.CURRENCY_CODE Currency, 
    ieba.iban Iban_No,
    hp_branch.CITY Local_Bank_City,
    hp_bank.PArty_Name Local_Bank_Name,
    null Order_of_Preference, 
    null Partner_Bank_Type,
    ieba.EFT_SWIFT_CODE Swift_Code,
ieba.last_update_date,
ieba.attribute1  Temp_1,
ieba.attribute2  Temp_2,
ieba.attribute3  Temp_3,
ieba.attribute4  Temp_4,
ieba.attribute5  Temp_5,
ieba.attribute6  Temp_6,
ieba.attribute7  Temp_7,
ieba.attribute8  Temp_8,
ieba.attribute9  Temp_9,
ieba.attribute10  Temp_10   
from 
poz_suppliers PS,  
poz_supplier_sites_all_m pvs,
hz_parties hp,
hr_organization_units_f_tl hout,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
iby_ext_bank_accounts ieba,
--hz_party_sites bank_address,
--hz_locations hl,
hz_parties hp_bank ,
hz_parties hp_branch 
where ps.vendor_id = pvs.vendor_id
--AND ps.enabled_flag ='Y'
--AND nvl(ps.end_Date_Active, sysdate+1) >= sysdate
--AND nvl(pvs.EFFECTIVE_END_DATE,sysdate+1) >= sysdate
and ps.party_id=hp.party_id
and pvs.prc_bu_id=hout.organization_id 
and hout.LANGUAGE = USERENV('LANG')
and iepa.payee_party_id = PS.party_id
--and trunc(IEPA.end_Date) >= trunc(sysdate)
and pvs.party_site_id=iepa.party_site_id
and iepa.supplier_Site_id= pvs.vendor_Site_id
and ipiua.ext_pmt_party_id = IEPA.ext_payee_id
--and trunc(ipiua.end_Date) >= trunc(sysdate)
and ieba.ext_bank_account_id = ipiua.instrument_id
--and pvs.party_site_id = bank_address.party_site_id
--and bank_address.location_id=hl.location_id
and ieba.bank_id = hp_bank.party_id
and ieba.branch_id = hp_branch.party_id
and hout.name = NVL(:P_BU_NAME,hout.name) 
--and pvs.vendor_Site_id=300000185706839 
and ieba.OBJECT_VERSION_NUMBER
IN(
select max(OBJECT_VERSION_NUMBER) from iby_ext_bank_accounts ieba1
where ieba1.ext_bank_account_id = ieba.ext_bank_account_id
and ieba1.bank_id = ieba.bank_id
)
and EXISTS
   (
   select 1 from POZ_SITE_ASSIGNMENTS_ALL_M psaa 
   where psaa.VENDOR_SITE_ID = pvs.VENDOR_SITE_ID
   and psaa.bu_id= pvs.PRC_BU_ID
--   and nvl(psaa.EFFECTIVE_END_DATE,sysdate+1) >= sysdate
   )
and trunc(ieba.last_update_date) between nvl(:P_FROM_DATE,trunc(ieba.last_update_date)) and nvl(:P_END_DATE,trunc(ieba.last_update_date))
UNION ALL
select 
(select 'Oracle-'|| instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'||PS.segment1||'-'||IEBA.EXT_BANK_ACCOUNT_ID AP_External_Key__c,  
(select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1) Source_System, 
(select 'Oracle-'||instance_name||'-'||to_Char(sysdate,'yyyymmddhhmmss') from gv$instance  where INSTANCE_NUMBER =1)  Batch_Id,
NULL Company_Code_Unique_Key, 
(select 'Oracle-'||instance_name from gv$instance  where INSTANCE_NUMBER =1) ||'-'||PS.segment1 Supplier_Unique_Key,
    PS.segment1    Supplier_Number,
NULL Supplier_Site_Id,
NULL  Supplier_Site_Code,   
    IEBA.EXT_BANK_ACCOUNT_ID Bank_Account_Id,
    ieba.BANK_ACCOUNT_NUM Bank_Account_No,
  TO_cHAR(ieba.START_DATE,'yyyy-MM-dd') Bank_Account_Start_Date,
TO_cHAR(ieba.end_DATE,'yyyy-MM-dd') Bank_Account_end_Date,
decode(hp_bank.STATus,'A','Y','I','N',hp_bank.STATus) Bank_Active_Status,
hp_branch.Party_Name Bank_Branch_Name,
        hp_branch.CITY Bank_City,  
    hp_branch.COUNTRY Bank_Country_Code ,
    hp_bank.PArty_Name Bank_Name,
hp_branch.ADDRESS1 Bank_Street1,
    hp_branch.ADDRESS2 Bank_Street2,
    hp_branch.ADDRESS3 Bank_Street3,
    ieba.BANK_ACCOUNT_NAME Bank_Account_Name, 
    hp_branch.ADDRESS4 Bank_Name_Alt ,
    ieba.CURRENCY_CODE Currency, 
    ieba.iban Iban_No,
    hp_branch.CITY Local_Bank_City,
    hp_bank.PArty_Name Local_Bank_Name,
    null Order_of_Preference, 
    null Partner_Bank_Type,
    ieba.EFT_SWIFT_CODE Swift_Code,
ieba.last_update_date,
ieba.attribute1  Temp_1,
ieba.attribute2  Temp_2,
ieba.attribute3  Temp_3,
ieba.attribute4  Temp_4,
ieba.attribute5  Temp_5,
ieba.attribute6  Temp_6,
ieba.attribute7  Temp_7,
ieba.attribute8  Temp_8,
ieba.attribute9  Temp_9,
ieba.attribute10  Temp_10   
from 
poz_suppliers PS,  
--poz_supplier_sites_all_m pvs,
hz_parties hp,
--hr_organization_units_f_tl hout,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
iby_ext_bank_accounts ieba,
--hz_party_sites bank_address,
--hz_locations hl,
hz_parties hp_bank ,
hz_parties hp_branch 
where 1=1
and ps.party_id=hp.party_id
and iepa.payee_party_id = PS.party_id
--and trunc(IEPA.end_Date) >= trunc(sysdate)
and iepa.party_site_id is null
and ipiua.ext_pmt_party_id = IEPA.ext_payee_id
--and trunc(ipiua.end_Date) >= trunc(sysdate)
and ieba.ext_bank_account_id = ipiua.instrument_id
--and pvs.party_site_id = bank_address.party_site_id
--and bank_address.location_id=hl.location_id
and ieba.bank_id = hp_bank.party_id
and ieba.branch_id = hp_branch.party_id
and ieba.OBJECT_VERSION_NUMBER
IN(
select max(OBJECT_VERSION_NUMBER) from iby_ext_bank_accounts ieba1
where ieba1.ext_bank_account_id = ieba.ext_bank_account_id
and ieba1.bank_id = ieba.bank_id
)
and trunc(ieba.last_update_date) between nvl(:P_FROM_DATE,trunc(ieba.last_update_date)) and nvl(:P_END_DATE,trunc(ieba.last_update_date))

Query to get supplier master data in oracle fusion

 SELECT 
   PS.segment1  Supplier_Number,
   PS.vendor_id Supplier_Id,
   pvs.vendor_Site_id Supplier_Site_Id,
   pvs.vendor_site_code Supplier_Site_Code,
   hp.party_name Supplier_Name,
       Tax.REGISTRATION_NUMBER Supplier_Tax_No,
   ps.Vendor_Type_Lookup_Code Supplier_Type,
   ps.AWT_GROUP_ID WHT_Inv_Group_Id, 
   Pay_method.payment_method_code Payment_Method, 
   Pay_method.description Payment_Method_Desc,
   hzl.city                                    Local_City,
   hzl.country                                 Local_Country,
   hzl.state                                   Local_State,
   hzl.ADDRESS1                                Local_Supplier_Address1,
       hzl.address2                                Local_Supplier_Address2,
       hzl.address3                                Local_Supplier_Address3,
   hzl.postal_code                             Local_Postal_Code,
   hzl.address_lines_phonetic                  Alternate_Address,
   pvs.email_address Primary_Email,
   Pay_method.remit_advice_email Remitt_to_email,
   pvs.phone Supplier_Phone,    
   case when nvl(pvs.inactive_date,sysdate+1) < sysdate THEN 'N'
   else 'Y'  end  Active_Status, 
   decode(att.term_id, null,null,(select 'Oracle-'|| instance_name from gv$instance  where INSTANCE_NUMBER =1)||'-'|| ATT.TERM_ID ) Payment_Term_Unique_Key , 
   null E_Invoice,
   pvs.pay_group_lookup_code Supplier_Group,
   pvs.pay_site_flag,
   hzl.ADDRESS1                                Address1,
       hzl.address2                                Address2,
       hzl.address3                                Address3,
   hzl.address4                                Address4,
   NULL                                   Address5,
   hzl.city                                    city,
   hzl.postal_code                             Postal_Code,
   hzl.state                                   state,
   hzl.country                                 country,
   null MSME ,
   TO_cHAR(ps.creation_date,'yyyy-MM-dd HH:mm:ss') Supplier_Creation_Date,
   Pay_method.EXCLUSIVE_PAYMENT_FLAG Pay_Alone_Flag,
   NULL Direct_Debit_Supplier_Flag,
   pvs.ALLOW_UNORDERED_RECEIPTS_FLAG NPO_Supplier_Flag,
   CASE when pvs.PAY_SITE_FLAG ='Y' and pvs.PURCHASING_SITE_FLAG ='Y' 
   then 
    'Pay_Site'
when pvs.PAY_SITE_FLAG ='N' AND pvs.PURCHASING_SITE_FLAG ='Y' 
then 'Purchasing_Site'
WHEN pvs.PAY_SITE_FLAG ='Y' AND pvs.PURCHASING_SITE_FLAG ='N' 
then 
    'Pay_Site'
END   
   Supplier_Site_Type,
   NULL Tax_Rate,
   PVS.invoice_currency_code Currency,
   hzl.county County,
   to_char(ps.last_update_Date,'yyyy-MM-dd HH:mm:ss') last_update_Date,
   ps.attribute1  Temp_1,
   ps.attribute2  Temp_2,
   ps.attribute3  Temp_3,
   ps.attribute4  Temp_4,
   ps.attribute5  Temp_5,
   ps.attribute6  Temp_6,
   ps.attribute7  Temp_7,
   ps.attribute8  Temp_8,
   ps.attribute9  Temp_9,
   ps.attribute10 Temp_10    
FROM   poz_suppliers PS,  
   POZ_SUPPLIER_SITES_ALL_M pvs,
       hz_locations hzl,
       hz_party_sites hps,       
   hz_parties hp,
   hr_organization_units_f_tl hout,
   ap_terms att    ,
   (
   SELECT ppm.payment_method_code,               
               iep.remit_advice_email,
               iep.EXCLUSIVE_PAYMENT_FLAG,  
   iep.supplier_Site_id,
   ppmt.description,
               MAX (ppm.object_version_number)
          FROM IBY_EXTERNAL_PAYEES_ALL  iep,
               IBY_EXT_PARTY_PMT_MTHDS  ppm,
   iby_payment_methods_tl ppmt
         WHERE iep.ext_payee_id = ppm.ext_pmt_party_id
           AND ppm.primary_flag = 'Y'
           AND NVL (ppm.inactive_date, SYSDATE+1) >= SYSDATE
           AND NVL (iep.inactive_date, SYSDATE+1) >= SYSDATE
   AND ppm.payment_method_code=ppmt.payment_method_code
   AND ppmt.LANGUAGE = USERENV('LANG')
         GROUP BY ppm.payment_method_code,               
               iep.remit_advice_email,
               iep.EXCLUSIVE_PAYMENT_FLAG,
   iep.supplier_Site_id ,ppmt.description
   ) Pay_method,
   (
   select distinct zpt.party_id, zr.REGISTRATION_NUMBER  from ZX_REGISTRATIONS zr, ZX_PARTY_TAX_PROFILE  zpt
   where zr.PARTY_TAX_PROFILE_ID=zpt.PARTY_TAX_PROFILE_ID
   and rownum=1
) Tax
WHERE  ps.vendor_id = pvs.vendor_id
   AND ps.enabled_flag ='Y'
--  AND nvl(ps.end_Date_Active, sysdate+1) >= sysdate
   AND pvs.location_id = hzl.location_id(+)  
       AND pvs.party_site_id =hps.party_site_id 
   AND PS.PARTY_ID=HP.PARTY_ID
   --AND nvl(pvs.INACTIVE_DATE,sysdate+1) >= sysdate
   and pvs.prc_bu_id=hout.organization_id 
   and hout.LANGUAGE = USERENV('LANG')
   and pvs.terms_id=att.term_id(+)
   and pvs.VENDOR_SITE_ID=Pay_method.supplier_Site_id(+)
   and hp.party_id=Tax.party_id(+)
   and EXISTS
   (
   select 1 from POZ_SITE_ASSIGNMENTS_ALL_M psaa 
   where psaa.VENDOR_SITE_ID = pvs.VENDOR_SITE_ID
   and psaa.bu_id= pvs.PRC_BU_ID
  --and nvl(psaa.INACTIVE_DATE,sysdate+1) >= sysdate
   )    
   and trunc(pvs.last_update_Date) between nvl(:P_FROM_DATE,trunc(pvs.last_update_Date)) and nvl(:P_END_DATE,trunc(pvs.last_update_Date))

Currency Master Data Query in oracle Fusion

 select fcb.CURRENCY_CODE, fct.DESCRIPTION,fcb.PRECISION ,
to_Char(fcb.LAST_UPDATE_DATE,'yyyy-MM-dd HH:mm:ss')  LAST_UPDATE_DATE
from FND_CURRENCIES_B fcb , FND_CURRENCIES_tl fct
where fcb.CURRENCY_CODE= fct.CURRENCY_CODE
and fct.LANGUAGE = USERENV('LANG')

Query to get Business Unit in Oracle Fusion

 select 
fab.SHORT_CODE Operating_Unit_Code,
fab.BU_NAME Operating_Unit_Name,GLB.currency_code Currency_Code,
hl.MAINPHONE_COUNTRY_CODE1 Country_Code,
hl.COUNTRY Country,
xr.REGISTRATION_NUMBER Company_VAT_ID, 
hl.ADDRESS_LINE_1 Address_1, hl.ADDRESS_LINE_2 Address_2, hl.ADDRESS_LINE_3 Address_3,
hl.TOWN_OR_CITY City, hl.region_1 State, hl.Postal_Code,
fab.last_update_Date
from FUN_ALL_BUSINESS_UNITS_V fab, gl_ledgers  GLB, hr_locations hl, XLE_REGISTRATIONS XR
where FAB.PRIMARY_LEDGER_ID= GLB.LEDGER_ID(+)
and fab.location_id=hl.LOCATION_ID(+)
and fab.BU_ID=xr.source_id(+)
--and fab.bu_name like 'ABC BUSINESS UNIT'
and EXISTS
(
select 1 from FUN_BU_USAGES_V fbu
where fbu.SHORT_CODE = fab.SHORT_CODE
and MODULE_KEY in 
(
'PAYABLES_INVOICING_BF',
'PROJECT_ACCOUNTING_BF',
'PAYABLES_PAYMENT_BF',
'REQUISITIONING_BF',
'RECEIVING_BF',
'EXPENSE_MANAGEMENT_BF',
'PROCUREMENT_BF',
'PRC_CONTRACT_MANAGEMENT_BF'
)
)