Supplier Site Query ( SQL) – General in Fusion

Supplier site is one of the most important set ups in Oracle Fusion Finance. if you have implemented P2P, vendor site and reports fetching the data are inevitable. in this blog, let me share the general query for supplier site. Of course there will be posts as the follow up to this post to find other important fields:

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...

Supplier Site Query Output:

This query will return you following columns:
1. Vendor Site Code
2. Procurement Business Unit
3. Alternate Vendor Site Code
4. Customer Number
5. Purchasing Site Flag
6. Pay Site Flag
7. Procurement Site Flag
8. Primary Pay Site Flag
9. Tax Reporting Site Flag
10. Document Category
11. Site Address
12. Site Status

Supplier Site Query:

SELECT ste.vendor_site_code,
hou.name procurement_bu,
ste.vendor_site_code_alt alternate_site_name,
ste.customer_num customer_number,
ste.purchasing_site_flag,
ste.pay_site_flag,
ste.pcard_site_flag procurement_site_flag ,
ste.primary_pay_site_flag,
ste.tax_reporting_site_flag income_tax_reporting_site,
b2b_site_code b2b_supplier_site_code ,
dsc.name document_category,
hl.address1 ||’, ‘|| hl.address2||’, ‘||hl.city||’, ‘||hl.state||’, ‘|| hl.postal_code|| ‘, ‘||hl.country site_address,
CASE
WHEN NVL(ste.INACTIVE_DATE, SYSDATE +1) > SYSDATE
THEN
‘Active’
ELSE
‘Inactive’
END status
FROM poz_supplier_sites_all_m ste,
hz_party_sites hps,
hz_locations hl,
hr_operating_units hou,
fnd_doc_sequence_categories dsc
WHERE 1=1
AND hps.party_site_id = ste.party_site_id
AND hl.location_id = ste.location_id
AND hou.organization_id = ste.PRC_BU_ID
AND dsc.code(+) = ste.global_attribute1
AND ste.vendor_site_code= :vendor_site_code

Here :vendor_site_code is the input parameter. But in case you want to get all the sites of the supplier, you can add POZ_SUPPLIERS table in the above query and connect POZ_SUPPLIER_SITES_ALL_M table using vendor_id.

Related Posts:

Supplier Details Query in Oracle Fusion – POZ_SUPPLIERS
Vendor Site – Invoicing & Payments

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