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 

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 

PS.VENDOR_NAME,

PS.SEGMENT1 SUPPLIER_NUMBER,

NVL2(PS.TAX_REPORTING_NAME, 'Secure', NULL) TAX_REPORTING_NAME,

ps.vendor_type_lookup_Code supplier_type,

ps.organization_type_lookup_code Tax_Organization_Type,

ps.business_relationship ,

ps.standard_industry_class sic,

hzl.country country,

pvs.vendor_site_spk_id, 

pvs.vendor_site_id, 

pvs.effective_end_date,

pvs.effective_start_date, 

pvs.effective_sequence,

pvs.object_version_number, 

pvs.inactive_date, 

pvs.vendor_id,

pvs.prc_bu_id, 

pvs.location_id, 

pvs.party_site_id,

pvs.vendor_site_code, 

pvs.purchasing_site_flag, 

pvs.rfq_only_site_flag,

pvs.pay_site_flag, pvs.tp_header_id, 

pvs.services_tolerance_id,

pvs.tolerance_id, pvs.terms_id, 

pvs.exclude_freight_from_discount,

pvs.bank_charge_bearer, 

pvs.pay_on_code, 

pvs.default_pay_site_id,

pvs.pay_on_receipt_summary_code, 

pvs.ece_tp_location_code,

pvs.pcard_site_flag, 

pvs.match_option, 

pvs.country_of_origin_code,

pvs.create_debit_memo_flag, 

pvs.supplier_notif_method,

pvs.email_address, 

pvs.primary_pay_site_flag, 

pvs.shipping_control,

pvs.selling_company_identifier, 

pvs.gapless_inv_num_flag,

pvs.retainage_rate, 

pvs.auto_calculate_interest_flag, 

pvs.hold_by,

pvs.hold_date, pvs.hold_flag, 

pvs.purchasing_hold_reason,

pvs.vendor_site_code_alt, 

pvs.attention_ar_flag, 

pvs.area_code,

pvs.phone, pvs.customer_num, 

pvs.ship_via_lookup_code,

pvs.freight_terms_lookup_code, 

pvs.fob_lookup_code, pvs.fax,

pvs.fax_area_code, pvs.telex, 

pvs.terms_date_basis,

pvs.pay_group_lookup_code, 

pvs.payment_priority,

pvs.invoice_amount_limit, 

pvs.pay_date_basis_lookup_code,

pvs.always_take_disc_flag, 

pvs.invoice_currency_code,

pvs.payment_currency_code, 

pvs.hold_all_payments_flag,

pvs.hold_future_payments_flag, 

pvs.hold_reason,

pvs.hold_unmatched_invoices_flag, 

pvs.payment_hold_date,

pvs.tax_reporting_site_flag, 

pvs.last_update_date, 

pvs.last_updated_by,

pvs.last_update_login, 

pvs.creation_date, 

pvs.created_by,

pvs.aging_period_days,

pvs.aging_onset_point, 

pvs.consumption_advice_frequency,

pvs.consumption_advice_summary, 

pvs.pay_on_use_flag,

pvs.mode_of_transport, 

pvs.service_level, 

hzl.address1 address_line1,

hzl.address2 address_line2, 

hzl.address3 address_line3,

hzl.address4 address_line4, 

hzl.address_style address_style,

hzl.address_lines_phonetic address_lines_alt, 

hzl.city city,

hzl.state state, 

hzl.county county, 

hzl.country country,

hzl.postal_code zip, 

hzl.province province,

hzl.location_language language_code, 

hps.duns_number_c duns_number

FROM 

poz_supplier_sites_all_m pvs, 

hz_locations hzl, 

hz_party_sites hps,

POZ_SUPPLIERS_V PS

WHERE hzl.location_id = pvs.location_id

AND hps.party_site_id = pvs.party_site_id

AND pvs.vendor_id=PS.VENDOR_ID

and PS.SEGMENT1 in ('508643','508411')

or try this

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’