Oracle Cloud Fusion: Supplier Bank Accounts Query

SELECT (SELECT NAME
        FROM   hr_operating_units
        WHERE  organization_id = pssm.prc_bu_id) BU_name,
   ps.segment1 supplier_number,
       hzp.party_name supplier_name,
       pssm.vendor_site_code supplier_site,
       bank.party_name bank_name,
       branch.party_name branch_name,
       ieb.bank_account_name,
       ieb.bank_account_num,
       ieb.currency_code,
       ieb.IBAN
FROM   poz_suppliers ps,
       poz_supplier_sites_all_m pssm,
       iby_external_payees_all payee,
       iby_pmt_instr_uses_all uses,
       iby_ext_bank_accounts ieb,
       hz_parties bank,
       hz_parties branch,
       hz_parties HZP
WHERE  ps.vendor_id = pssm.vendor_id
       AND ps.party_id = payee.payee_party_id
       AND payee.supplier_site_id = pssm.vendor_site_id
       AND uses.instrument_type = 'BANKACCOUNT'
       AND payee.ext_payee_id = uses.ext_pmt_party_id
       AND uses.payment_function = 'PAYABLES_DISB'
       AND uses.instrument_id = ieb.ext_bank_account_id
       AND ieb.bank_id = bank.party_id(+)
       AND ieb.branch_id = branch.party_id(+)
       AND hzp.party_id = ps.party_id
       AND SYSDATE BETWEEN Nvl(uses.start_date, SYSDATE) AND
                           Nvl(uses.end_date, SYSDATE)
       AND SYSDATE BETWEEN Nvl(ieb.start_date, SYSDATE) AND
                           Nvl(ieb.end_date, SYSDATE)
       AND Nvl(ps.end_date_active, SYSDATE + 1) > Trunc (SYSDATE)
       AND Nvl(pssm.inactive_date, SYSDATE + 1) > Trunc (SYSDATE) 

Oracle Cloud Fusion: Query to get the Supplier/Site Attachment Details

--------------------------------------------------------------------

-- Supplier Attachment

--------------------------------------------------------------------

SELECT psv.vendor_name supplier_name,

       fad.category_name,

       fdt.dm_type,

       fdt.file_name,

       fdt.title,

       fdt.description

FROM   poz_suppliers_v psv,

       fnd_attached_documents fad,

       fnd_documents_tl fdt

WHERE  1 = 1

       AND To_char (psv.vendor_id) = fad.pk1_value

       AND fad.document_id = fdt.document_id

       AND fad.entity_name = 'POZ_SUPPLIERS'

       AND fdt.language = Userenv ('LANG') 

   

--------------------------------------------------------------------

-- Supplier Site Attachment

--------------------------------------------------------------------

SELECT psv.vendor_name supplier_name,

       (SELECT NAME

        FROM   hr_operating_units

        WHERE  organization_id = pssv.prc_bu_id) Procurement_BU,

       pssv.vendor_site_code supplier_site,

       fad.category_name,

       fdt.dm_type,

       fdt.file_name,

       fdt.title,

       fdt.description

FROM   poz_suppliers_v psv,

       poz_supplier_sites_v pssv,

       fnd_attached_documents fad,

       fnd_documents_tl fdt

WHERE  1 = 1

       AND psv.vendor_id = pssv.vendor_id

       AND To_char (pssv.vendor_site_id) = fad.pk1_value

       AND fad.document_id = fdt.document_id

       AND fad.entity_name = 'POZ_SUPPLIER_SITES_ALL_M'

       AND fdt.language = Userenv ('LANG')