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

;
 

  • August 5, 2016 | 16 views