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.

Common Oracle SQL Queries to fetch the database table in Oracle fusion

Here are some of the common Oracle SQL queries to retrieve metadata in Oracle databases, including table details, columns, constraints, indexes, and more. 

These queries are especially useful when working with Oracle Fusion to find the underlying table metadata information.

Note : These are the sample queries, please modify accordingly as per your requirements. 

1. SQL Query to list All Tables in a Schema

SELECT table_name
FROM all_tables
WHERE owner = 'FUSION';     -- Replace 'FUSION' with the schema name

2. SQL Query to List All Columns in a Table

SELECT column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

3. SQL Query to Search for Tables with Specific Column Names

SELECT table_name, column_name
FROM all_tab_columns
WHERE column_name LIKE '%PERSON_ID%'; -- Replace with the column keyword

4. SQL Query to View Primary Key Information

SELECT cols.table_name, cols.column_name, cons.constraint_name
FROM all_cons_columns cols
JOIN all_constraints cons
  ON cols.constraint_name = cons.constraint_name
WHERE cons.constraint_type = 'P'
  AND cols.table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

5. SQL Query to View Foreign Key Information

SELECT a.table_name, a.column_name, a.constraint_name, 
c_pk.table_name AS referenced_table, c_pk.constraint_name AS referenced_constraint
FROM all_cons_columns a
JOIN all_constraints c
  ON a.constraint_name = c.constraint_name
JOIN all_constraints c_pk
  ON c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
  AND a.table_name = 'PER_ALL_ASSIGNMENTS_F'; -- Replace with your table name

6. SQL Query to View All Indexes on a Table

SELECT index_name, column_name, uniqueness
FROM all_ind_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

7. SQL Query to View Constraints on a Table

SELECT constraint_name, constraint_type, status
FROM all_constraints
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

8. SQL Query to Search for a Keyword in Table or Column Names

SELECT table_name, column_name
FROM all_tab_columns
WHERE table_name LIKE '%PERSON%' OR column_name LIKE '%PERSON%';

9. SQL Query to List All Views in a Schema

SELECT view_name
FROM all_views
WHERE owner = 'FUSION'; -- Replace with the schema name

10. SQL Query to View Dependencies of a Table

SELECT name, type, referenced_name, referenced_type
FROM all_dependencies
WHERE name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

11. SQL Query to Identify Data Types and Lengths

SELECT column_name, data_type, data_length, data_precision, data_scale
FROM all_tab_columns
WHERE table_name = 'PER_ALL_PEOPLE_F'; -- Replace with your table name

12. SQL Query to List All Synonyms in a Schema

SELECT synonym_name, table_owner, table_name
FROM all_synonyms
WHERE owner = 'FUSION'; -- Replace with your schema name

13. SQL Query to Search Materialized Views

SELECT mview_name
FROM all_mviews
WHERE owner = 'FUSION'; -- Replace with the schema name

14. SQL Query to Find Columns with Date Data Type

SELECT table_name, column_name
FROM all_tab_columns
WHERE data_type = 'DATE';

Oracle Metadata Tables

• ALL_TABLES: Contains details about all tables accessible to the user.
• ALL_TAB_COLUMNS: Stores metadata about all columns.
• ALL_CONSTRAINTS: Contains information about constraints on tables.
• ALL_VIEWS: Provides details on views.
• ALL_SYNONYMS: Contains synonym information.

SQL Query to get all the tables in Oracle Fusion.

 Here is the sample query to get all the tables in Oracle fusion.

SELECT table_name

FROM all_tables

WHERE owner = 'FUSION';

SQL Query to get the Metadata details for the tables in Oracle Fusion

 Here is the sample query to get the metadata details for the Oracle tables.

Note : Please modify as per your requirements accordingly.

SELECT atc.table_name, atc.column_name, atc.*

FROM all_tab_columns atc

WHERE column_name LIKE '%EMPLOYEE%' OR column_name LIKE '%PERSON%';

Tables in Oracle HCM

 Oracle Fusion HCM (Human Capital Management) is a comprehensive suite of HR tools. 

Commonly Used HCM Tables

Here are some common examples of tables in Oracle Fusion HCM:

1. Core HR Tables

• PER_ALL_PEOPLE_F: Holds person details like name, date of birth, and hire date.
• PER_ALL_ASSIGNMENTS_F: Stores employee assignment details.
• PER_GRADES_F: Contains information about grades.
• PER_JOBS_F: Holds job information.

2. Workforce Structures

• HR_ORGANIZATION_UNITS_F: Organizational unit details.
• HR_ALL_POSITIONS_F: Information about positions.

3. Payroll Tables

• PAY_PERSONAL_PAYMENT_METHODS_F: Stores employee payment methods.
• PAY_PAYROLL_REL_ACTIONS: Information about payroll run actions.
• PAY_RUN_RESULTS: Results of payroll runs.

4. Security and User Tables

• FND_USER: Stores user account details.
• FND_USER_RESPONSIBILITY: User responsibilities.

SQL Query to fetch the Employee Payroll information in Oracle Cloud Fusion - HCM

Here is the sample SQL Query for Oracle Fusion HCM.  These queries will help to retrieve the basic information related to the employee payroll information.

This is the basic query, use this table accordingly and modify as per your requirements. 

SQL Query :

SELECT 

    ppf.person_number,

    ppf.full_name,

    pap.assignment_number,

    ppa.payroll_name,

    ppa.element_entry_id,

    ppa.salary_amount,

    ppa.currency_code

FROM 

    pay_element_entries_f ppa

JOIN 

    per_all_assignments_f pap ON ppa.assignment_id = pap.assignment_id

JOIN 

    per_all_people_f ppf ON pap.person_id = ppf.person_id

WHERE 

    ppa.element_name = 'Salary'

    AND SYSDATE BETWEEN ppa.effective_start_date AND ppa.effective_end_date;

SQL Query to fetch the Employee Assignment Information in Oracle Cloud Fusion - HCM

 Here is the sample SQL Query for Oracle Fusion HCM.  These queries will help to retrieve the basic information related to the employee assignment information.

This is the basic query, use this table accordingly and modify as per your requirements. 

SQL Query :

SELECT 

    a.assignment_number,

    p.person_number,

    p.full_name,

    a.job_id,

    j.job_name,

    a.position_id,

    pos.position_name,

    a.department_id,

    d.department_name,

    a.location_id,

    loc.location_code AS location_name,

    a.hire_date,

    a.assignment_status_type

FROM 

    per_all_assignments_f a

JOIN 

    per_all_people_f p ON a.person_id = p.person_id

JOIN 

    per_jobs j ON a.job_id = j.job_id

JOIN 

    per_positions pos ON a.position_id = pos.position_id

JOIN 

    hr_organization_units d ON a.department_id = d.organization_id

JOIN 

    hr_locations loc ON a.location_id = loc.location_id

WHERE 

    SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date;

SQL Query to fetch the Employee Information in Oracle Cloud Fusion - HCM

Here is the sample SQL Query for Oracle Fusion HCM.  These queries will help to retrieve the basic information related to the employee information.

Use this table accordingly and modify as per your requirements.

SQL Query : 

SELECT 

    per.person_number,

    per.full_name,

    per.email_address,

    per.phone_number,

    per.date_of_birth,

    per.effective_start_date AS start_date,

    per.national_identifier AS ssn,

    per.*

FROM 

    per_all_people_f per

WHERE 

    SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date;