Supplier Details Query in Oracle Fusion

Supplier or Vendor Details is something you are bound to query if you have implemented Purchasing/ Payable modules. After all supplier would have been created to supply the material which you would consume in your business and in return you will have to pay the supplier. So most probably you will need to query the vendor from Purchasing side or Payable Side.
Base table to store the supplier information is POZ_Suppliers. Query to fetch the supplier details is given below. In case you are looking to get the details for Supplier Sites or the payment details, following posts can be helpful

Supplier Site Query ( SQL) – General in Fusion
Vendor Site – Invoicing & Payments Query
How To Fetch (Query) The Payment Details In Fusion
Query to fetch the details of a Payment Process Request in Fusion

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Below is the query you can use. Following are the columns query will fetch:
1. Vendor Name
2. Vendor Number
3. Vendor Type
4. Tax Organization Type
5. Business Relationship
6. DUNS Number
7. Customer Number
8. SIC
9. Registry Id
10. Year Established
11. Mission Statement
12. Taxpayer Id
13. Party Site Name
14. Party Site Address
15. Contact Person

Supplier Detail Query:

select hp.party_name Vendor_Name,
ps.segment1 supplier_number,
ps.vendor_type_lookup_Code supplier_type,
ps.organization_type_lookup_code Tax_Organization_Type,
ps.business_relationship ,
hop.DUNS_NUMBER_C DUNS_NUMBER,
ps.customer_num,
ps.standard_industry_class sic,
hop.party_number Registry_id,
hop.year_established,
hop.mission_statement,
psp.INCOME_TAX_ID Taxpayer_ID,
hps.PARTY_SITE_NAME Address_Name,
hp.address1,
hp.address2,
hp.city ,
hp.state,
hp.county,
hp_contact.person_last_name||’, ‘||hp_contact.PERSON_PRE_NAME_ADJUNCT||’ ‘||hp_contact.person_first_name Contact_Person
from poz_suppliers ps,
hz_parties hp ,
hz_organization_profiles hop,
POZ_SUPPLIERS_PII psp,
hz_party_sites hps,
hz_parties hp_contact
where hp.party_id = ps.party_id
AND hop.party_id = ps.party_id
AND psp.vendor_id(+) = ps.vendor_id
AND hps.party_site_id(+) = hp.iden_addr_party_site_id
AND hp_contact.party_id(+) = hp.preferred_contact_person_id
and hp.party_name = ‘ABC’

Related Posts:

Vendor Site Query ( SQL) – General in Fusion
Vendor Site – Invoicing & Payments Query
How To Fetch (Query) The Payment Details In Fusion
Query to fetch the details of a Payment Process Request in Fusion

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com