Query to find list of suppliers who has attachments

Often times you might have to find a list of suppliers who has a specific attachment. For example, if you need to find list of suppliers who has attached with a W9 form, then in that case use the below query.

SELECT asup.vendor_name supplier_name, asup.segment1 supplier_number,

       asup.enabled_flag, asup.creation_date,

       asup.vendor_type_lookup_code lookup_code,asup.num_1099,

       asup.tax_reporting_name, assa.vendor_site_code, assa.inactive_date,

       assa.address_line1, assa.address_line2, assa.city, assa.state,

       assa.zip, assa.country,assa.org_id, fdt.description, fdt.title

  FROM apps.ap_suppliers asup,

       apps.ap_supplier_sites_all assa,

       apps.fnd_attached_documents fad,

       apps.fnd_documents_tl fdt

 WHERE asup.vendor_id = assa.vendor_id

   AND TO_CHAR ( asup.vendor_id ) = fad.pk1_value

   AND fad.document_id = fdt.document_id

   AND fad.entity_name = 'PO_VENDORS'

   AND fdt.title IN   ( 'W9', 'W8', 'W-9', 'W-8', 'w9', 'w8', 'w-9', 'w-8' )

   AND fdt.LANGUAGE = USERENV ( 'LANG' )

   --AND asup.vendor_id = 408801

;