Oracle SQL Queries related to the Suppliers Data Model in Oracle Fusion

Here are some of the common SQL queries related to the Supplier Model in Oracle Fusion, involving  tables typically used for supplier data 

 Supplier profiles, 

 Supplier addresses, 

 Supplier Contacts

 Supplier Sites

 and data related to payments

1. SQL Query to List All Suppliers

SELECT vendor_id, vendor_name, vendor_number, creation_date, last_update_date
FROM po_vendors;

2. SQL Query to Find Supplier Details by Name or Number

SELECT vendor_id, vendor_name, vendor_number, vendor_type_lookup_code, enabled_flag, creation_date
FROM po_vendors
WHERE UPPER(vendor_name) LIKE '%SUPPLIER_NAME%' -- Replace 'SUPPLIER_NAME' with your search keyword
   OR vendor_number = 'SUPPLIER_NUMBER'; -- Replace 'SUPPLIER_NUMBER' with your number

3. SQL Query to Retrieve Supplier Sites

SELECT vendor_site_id, vendor_id, vendor_site_code, address_line1, city, state, postal_code, country
FROM po_vendor_sites_all
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

4. SQL Query to List Supplier Contacts

SELECT vendor_contact_id, vendor_id, first_name, last_name, email_address, phone_number
















FROM po_vendor_contacts
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

5. SQL Query to Get Supplier Site Payment Details

SELECT vendor_site_id, payment_method_lookup_code, payment_currency_code, bank_account_num, bank_name
FROM po_vendor_sites_all
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

6. SQL Query to List Supplier Payment Methods

SELECT vendor_id, vendor_name, payment_method_lookup_code
FROM po_vendors
WHERE payment_method_lookup_code IS NOT NULL;

7. SQL Query to Find Active Suppliers

SELECT vendor_id, vendor_name, enabled_flag, creation_date
FROM po_vendors
WHERE enabled_flag = 'Y';

8. SQL Query to List All Bank Accounts Associated with Suppliers

SELECT ext_bank_account_id, bank_account_name, bank_account_num, currency_code, vendor_id
FROM iby_ext_bank_accounts
WHERE party_type_lookup_code = 'SUPPLIER';

9. SQL Query to Get Supplier Invoices

SELECT invoice_id, invoice_num, invoice_date, invoice_amount, payment_status_flag
FROM ap_invoices_all
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

10. SQL Query to Find Purchase Orders by Supplier

SELECT po_header_id, segment1 AS po_number, type_lookup_code, creation_date, total_amount
FROM po_headers_all
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

11. SQL Query to Find Supplier Purchase Agreements

SELECT agreement_id, vendor_id, agreement_type, status, start_date, end_date, total_amount
FROM po_agreements
WHERE vendor_id = :VENDOR_ID; -- Replace ':VENDOR_ID' with the supplier ID

12. SQL Query to List Suppliers with No Recent Activity

SELECT vendor_id, vendor_name, last_update_date
FROM po_vendors
WHERE last_update_date < ADD_MONTHS(SYSDATE, -6); -- Suppliers with no updates in the last 6 months

13. SQL Query to Retrieve Supplier Category Assignments

SELECT pac.vendor_id, pv.vendor_name, pac.category_id, msc.segment1 AS category
FROM po_assign_categories pac
JOIN po_vendors pv ON pac.vendor_id = pv.vendor_id
JOIN mtl_categories_b msc ON pac.category_id = msc.category_id;

14. SQL Query to List Suppliers by Country

SELECT pv.vendor_id, pv.vendor_name, pvsa.country
FROM po_vendors pv
JOIN po_vendor_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
WHERE pvsa.country = 'USA'; -- Replace 'USA' with your desired country

15. SQL Query to Find Suppliers with Specific Payment Terms

SELECT pv.vendor_id, pv.vendor_name, pvsa.payment_terms_id, hl.meaning AS payment_terms
FROM po_vendors pv
JOIN po_vendor_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
JOIN hr_lookups hl ON pvsa.payment_terms_id = hl.lookup_code
WHERE hl.meaning = 'Net 30'; -- Replace 'Net 30' with your payment terms

Key Oracle fusion tables in Supplier Model

• PO_VENDORS: Stores supplier header information.
• PO_VENDOR_SITES_ALL: Stores supplier site details.
• PO_VENDOR_CONTACTS: Contains supplier contact information.
• IBY_EXT_BANK_ACCOUNTS: Stores external bank account information for suppliers.
• AP_INVOICES_ALL: Holds invoice details.
• PO_HEADERS_ALL: Stores purchase order information.
• PO_AGREEMENTS: Contains blanket or contract purchase agreement details.