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