Query to get supplier details in oracle fusion

Query to get supplier details in oracle fusion

In this post , We will be discuss about query to get supplier details in oracle fusion. This query will help to extract the supplier complete details in oracle fusion. we can run this supplier query directly in BIP tool in oracle fusion. This is the complete sql query to extract the important supplier informations in oracle fusion. We can use this query to develop custom BIP supplier reports in oracle fusion. Here below is the complete details about Query to get supplier details in oracle fusion.

Important Tables used by Query to get supplier details in oracle fusion

1.poz_supplier_sites_all_m

2.hz_locations

3.hz_party_sites

4.POZ_SUPPLIERS_V

Query to get supplier details in oracle fusion

Detail SQL Query to get supplier details in oracle fusion

Here below is the query to extract supplier details in fusion.

SELECT PS.VENDOR_NAME,

PS.SEGMENT1 SUPPLIER_NUMBER,

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

PS.EMPLOYEE_ID,

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