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;

SQL Queries for the Oracle fusion Project Management

 Here are some sample Oracle Fusion SQL queries for various aspects of project management:

1. Query to Retrieve Project Details

SELECT project_id,
       project_name,
       project_number,
       project_status,
       start_date,
       end_date
FROM   pjf_projects_all
WHERE  project_status = 'APPROVED';

2. Query to Retrieve Task Details for a Specific Project

SELECT task_id,
       task_name,
       task_number,
       task_status,
       start_date,
       end_date
FROM   pjf_tasks
WHERE  project_id = :project_id; -- Replace with the specific project ID

3. Query to Retrieve Expenditure Items for a Specific Project

SELECT expenditure_item_id,
       expenditure_type,
       expenditure_date,
       amount
FROM   pjc_expenditure_items
WHERE  project_id = :project_id; -- Replace with the specific project ID

4. Query to Retrieve Budget Lines for a Specific Project

SELECT budget_version_id,
       budget_line_id,
       budget_amount,
       budget_date
FROM   pjf_budget_lines
WHERE  project_id = :project_id; -- Replace with the specific project ID

5. Query to Retrieve Resource Assignments for a Specific Project

SELECT assignment_id,
       resource_id,
       task_id,
       start_date,
       end_date
FROM   pjr_assignments
WHERE  project_id = :project_id; -- Replace with the specific project ID

6. Query to Retrieve Contract Details for a Specific Project

SELECT contract_id,
       contract_number,
       contract_name,
       start_date,
       end_date,
       contract_amount
FROM   pjb_contracts_all
WHERE  project_id = :project_id; -- Replace with the specific project ID

7. Query to Retrieve Invoice Details for a Specific Project

SELECT invoice_id,
       invoice_number,
       invoice_date,
       amount
FROM   pjb_invoices_all
WHERE  project_id = :project_id; -- Replace with the specific project ID

8. Query to Retrieve Cost Distribution Lines for a Specific Project

SELECT cost_distribution_line_id,
       transaction_id,
       amount,
       distribution_date
FROM   pjc_cost_distribution_lines
WHERE  project_id = :project_id; -- Replace with the specific project ID

These sample queries can be tailored to specific needs by modifying the WHERE clause conditions or by adding more columns to the SELECT statement.

Note, please refer to the Oracle documentations for any changes to the latest release and the updates.

SQL Queries tables in Oracle Fusion Project Management

 In Oracle Fusion Project Management, several key tables store data related to various aspects of project management. These tables are integral for managing project finances, resources, and operations. Here are some of the key tables:

1. Projects and Tasks

   • PJF_PROJECTS_ALL: Stores information about projects.
   • PJF_TASKS: Stores details of tasks associated with projects.

2. Expenditures

   • PJC_EXPENDITURE_ITEMS: Holds expenditure item data.
   • PJC_EXPENDITURE_TYPES: Stores types of expenditures.

3. Budgets and Forecasts

   • PJF_BUDGET_VERSIONS: Contains budget versions for projects.
   • PJF_BUDGET_LINES: Stores individual budget lines.

4. Project Costing

   • PJC_COST_DISTRIBUTION_LINES: Contains cost distribution lines.
   • PJC_TRANSACTIONS: Stores project transaction details.

5. Billing

   • PJB_INVOICES_ALL: Contains billing invoice data.
   • PJB_INVOICE_LINES_ALL: Stores invoice lines.

6. Resources and Assignments

   • PJR_RESOURCES: Contains information about resources assigned to projects.
   • PJR_ASSIGNMENTS: Stores details of resource assignments to tasks.

7. Contracts and Funding

   • PJB_CONTRACTS_ALL: Holds contract information.
   • PJB_FUNDING_DISTRIBUTIONS: Contains funding distribution details.

8. Project Integration and Reporting

   • PJF_PROJECT_REPORTING: Stores data for project reporting purposes.
   • PJF_INTEGRATION_EVENTS: Contains integration event data for project management.

These tables are crucial for the functionality of Oracle Fusion Project Management, allowing users to manage projects comprehensively from budgeting and costing to billing and resource management.

Please refer to the Oracle documentations for any changes in the latest releases.

SQL Queries tables in Oracle Fusion Procurement

 In Oracle Fusion Procurement, several key tables store data related to purchasing, suppliers, purchase orders, and related procurement activities. Here’s an overview of some important tables in Oracle Fusion Procurement, along with sample SQL queries which will be able to leveraged and can retrieve data from these tables.

Key Tables in Oracle Fusion Procurement

1. PO_HEADERS_ALL

   • Contains header information for purchase orders.
   • Key columns: PO_HEADER_ID, PO_NUMBER, VENDOR_ID, CREATION_DATE, STATUS, etc.

2. PO_LINES_ALL

   • Stores line-level details for purchase orders.
   • Key columns: PO_LINE_ID, PO_HEADER_ID, LINE_ID, ITEM_ID, QUANTITY, UNIT_PRICE, etc.

3. PO_DISTRIBUTIONS_ALL

   • Contains distribution information for purchase order lines.
   • Key columns: PO_DISTRIBUTION_ID, PO_LINE_ID, ACCOUNTING_DATE, DISTRIBUTION_AMOUNT, etc.

4. PO_SUPPLIERS

   • Information about suppliers.
   • Key columns: SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_TYPE, STATUS, ADDRESS, etc.

5. PO_DOCUMENTS

   • Stores documents related to purchase orders.
   • Key columns: DOCUMENT_ID, PO_HEADER_ID, DOCUMENT_TYPE, DOCUMENT_NAME, etc.

6. PO_AGREEMENTS

   • Contains details about procurement agreements.
   • Key columns: AGREEMENT_ID, AGREEMENT_NUMBER, SUPPLIER_ID, START_DATE, END_DATE, etc.

7. PO_AGREEMENT_LINES

   • Details of lines in procurement agreements.
   • Key columns: AGREEMENT_LINE_ID, AGREEMENT_ID, ITEM_ID, QUANTITY, PRICE, etc.

8. PO_APPROVALS

   • Contains approval information for purchase orders.
   • Key columns: APPROVAL_ID, PO_HEADER_ID, APPROVAL_STATUS, APPROVER_ID, APPROVAL_DATE, etc.

9. PO_RECEIPTS

   • Stores information about goods receipts against purchase orders.
   • Key columns: RECEIPT_ID, PO_HEADER_ID, RECEIPT_DATE, QUANTITY_RECEIVED, ITEM_ID, etc.

10. PO_PAYMENTS

    • Contains details about payments made to suppliers.
    • Key columns: PAYMENT_ID, PO_HEADER_ID, PAYMENT_AMOUNT, PAYMENT_DATE, PAYMENT_STATUS, etc.

Sample SQL Queries

1. Retrieve Purchase Order Header Information

SELECT
    p.PO_HEADER_ID,
    p.PO_NUMBER,
    p.VENDOR_ID,
    v.SUPPLIER_NAME,
    p.CREATION_DATE,
    p.STATUS
FROM
    PO_HEADERS_ALL p
    JOIN PO_SUPPLIERS v ON p.VENDOR_ID = v.SUPPLIER_ID
WHERE
    p.CREATION_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
    p.CREATION_DATE DESC;

2. Get Purchase Order Line Details

SELECT
    l.PO_LINE_ID,
    l.PO_HEADER_ID,
    l.LINE_ID,
    l.ITEM_ID,
    i.ITEM_NAME,
    l.QUANTITY,
    l.UNIT_PRICE
FROM
    PO_LINES_ALL l
    JOIN INV_MTL_SYSTEM_ITEMS_B i ON l.ITEM_ID = i.ITEM_ID
WHERE
    l.PO_HEADER_ID = :PO_HEADER_ID
ORDER BY
    l.LINE_ID;

3. Retrieve Purchase Order Distributions

SELECT
    d.PO_DISTRIBUTION_ID,
    d.PO_LINE_ID,
    d.ACCOUNTING_DATE,
    d.DISTRIBUTION_AMOUNT,
    l.ITEM_ID
FROM
    PO_DISTRIBUTIONS_ALL d
    JOIN PO_LINES_ALL l ON d.PO_LINE_ID = l.PO_LINE_ID
WHERE
    d.PO_HEADER_ID = :PO_HEADER_ID
ORDER BY
    d.ACCOUNTING_DATE;

4. Get Supplier Information

SELECT
    s.SUPPLIER_ID,
    s.SUPPLIER_NAME,
    s.SUPPLIER_TYPE,
    s.STATUS,
    s.ADDRESS
FROM
    PO_SUPPLIERS s
WHERE
    s.STATUS = 'Active'
ORDER BY
    s.SUPPLIER_NAME;

5. Retrieve Purchase Order Receipts

SELECT
    r.RECEIPT_ID,
    r.PO_HEADER_ID,
    p.PO_NUMBER,
    r.RECEIPT_DATE,
    r.QUANTITY_RECEIVED,
    l.ITEM_ID
FROM
    PO_RECEIPTS r
    JOIN PO_HEADERS_ALL p ON r.PO_HEADER_ID = p.PO_HEADER_ID
    JOIN PO_LINES_ALL l ON r.PO_HEADER_ID = l.PO_HEADER_ID
WHERE
    r.RECEIPT_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
    r.RECEIPT_DATE DESC;

6. List Procurement Agreements and Lines

SELECT
    a.AGREEMENT_ID,
    a.AGREEMENT_NUMBER,
    a.START_DATE,
    a.END_DATE,
    s.SUPPLIER_NAME
FROM
    PO_AGREEMENTS a
    JOIN PO_SUPPLIERS s ON a.SUPPLIER_ID = s.SUPPLIER_ID
WHERE
    a.START_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
    a.START_DATE;

SELECT
    al.AGREEMENT_LINE_ID,
    al.ITEM_ID,
    i.ITEM_NAME,
    al.QUANTITY,
    al.PRICE
FROM
    PO_AGREEMENT_LINES al
    JOIN INV_MTL_SYSTEM_ITEMS_B i ON al.ITEM_ID = i.ITEM_ID
WHERE
    al.AGREEMENT_ID = :AGREEMENT_ID
ORDER BY
    al.ITEM_ID;

Notes

• Refer to the Oracle documentations for any changes to the table names and column names. 
• For large datasets, consider optimizing queries with appropriate indexing and filtering to enhance performance.

These tables and queries should help you effectively manage and analyze procurement data within Oracle Fusion Applications and can be leveraged for building custom requirements.

SQL Queries Tables in Oracle Fusion HCM

 In Oracle Fusion Human Capital Management (HCM), various tables store information related to employees, payroll, benefits, and other HR functions. Here are some key tables in Oracle Fusion HCM and sample SQL queries to illustrate how to retrieve data from these tables:

Key Tables in Oracle Fusion HCM

1. PER_PEOPLE_F

   • Contains information about individuals in the organization.
   • Key columns: PERSON_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, GENDER, etc.

2. PER_JOBS_F

   • Stores job information for employees.
   • Key columns: JOB_ID, JOB_NAME, DEPARTMENT_ID, LOCATION_ID, START_DATE, END_DATE, etc.

3. PER_ASSIGNMENTS_F

   • Details of employee assignments including job, department, and location.
   • Key columns: ASSIGNMENT_ID, PERSON_ID, JOB_ID, DEPARTMENT_ID, LOCATION_ID, ASSIGNMENT_STATUS, etc.

4. PER_LOCATIONS

   • Contains location details.
   • Key columns: LOCATION_ID, LOCATION_NAME, ADDRESS, CITY, STATE, COUNTRY, etc.

5. PER_DEPARTMENTS

   • Information about departments within the organization.
   • Key columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID, etc.

6. PAY_ELEMENT_ENTRIES_F

   • Stores payroll element entries, including earnings and deductions.
   • Key columns: ELEMENT_ENTRY_ID, PERSON_ID, ELEMENT_ID, PAYMENT_AMOUNT, DEDUCTION_AMOUNT, START_DATE, END_DATE, etc.

7. PAY_PAYROLLS_F

   • Contains payroll run information.
   • Key columns: PAYROLL_ID, PAYROLL_NAME, PAYROLL_STATUS, PAYROLL_RUN_DATE, etc.

8. PER_GRADES

   • Details of job grades and salary ranges.
   • Key columns: GRADE_ID, GRADE_NAME, MIN_SALARY, MAX_SALARY, etc.

9. PER_BENEFIT_PROGRAMS

   • Information about benefit programs available to employees.
   • Key columns: BENEFIT_PROGRAM_ID, PROGRAM_NAME, PROGRAM_TYPE, START_DATE, END_DATE, etc.

10. PER_LEAVE_ENTITLEMENTS

    • Contains data on employee leave entitlements.
    • Key columns: ENTITLEMENT_ID, PERSON_ID, LEAVE_TYPE, ENTITLEMENT_AMOUNT, BALANCE, etc.

Sample SQL Queries

1. Retrieve Basic Employee Information

SELECT
    p.PERSON_ID,
    p.FIRST_NAME,
    p.LAST_NAME,
    p.DATE_OF_BIRTH,
    j.JOB_NAME,
    d.DEPARTMENT_NAME,
    l.LOCATION_NAME
FROM
    PER_PEOPLE_F p
    JOIN PER_ASSIGNMENTS_F a ON p.PERSON_ID = a.PERSON_ID
    JOIN PER_JOBS_F j ON a.JOB_ID = j.JOB_ID
    JOIN PER_DEPARTMENTS d ON a.DEPARTMENT_ID = d.DEPARTMENT_ID
    JOIN PER_LOCATIONS l ON a.LOCATION_ID = l.LOCATION_ID
WHERE
    p.ACTIVE = 'Y'
ORDER BY
    p.LAST_NAME;

2. Get Payroll Information for Employees

SELECT
    p.PERSON_ID,
    p.FIRST_NAME,
    p.LAST_NAME,
    e.ELEMENT_ID,
    e.PAYMENT_AMOUNT,
    e.DEDUCTION_AMOUNT,
    pa.PAYROLL_NAME,
    pa.PAYROLL_RUN_DATE
FROM
    PAY_ELEMENT_ENTRIES_F e
    JOIN PER_PEOPLE_F p ON e.PERSON_ID = p.PERSON_ID
    JOIN PAY_PAYROLLS_F pa ON e.PAYROLL_ID = pa.PAYROLL_ID
WHERE
    pa.PAYROLL_RUN_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
    p.LAST_NAME;

3. Retrieve Leave Entitlements for Employees

SELECT
    p.PERSON_ID,
    p.FIRST_NAME,
    p.LAST_NAME,
    l.LEAVE_TYPE,
    l.ENTITLEMENT_AMOUNT,
    l.BALANCE
FROM
    PER_LEAVE_ENTITLEMENTS l
    JOIN PER_PEOPLE_F p ON l.PERSON_ID = p.PERSON_ID
WHERE
    1 = 1
ORDER BY
    p.LAST_NAME;

4. Get Job Information and Salary Grades

SELECT
    j.JOB_ID,
    j.JOB_NAME,
    g.GRADE_NAME,
    g.MIN_SALARY,
    g.MAX_SALARY
FROM
    PER_JOBS_F j
    JOIN PER_GRADES g ON j.GRADE_ID = g.GRADE_ID
ORDER BY
    j.JOB_NAME;

5. List Employees and Their Benefit Programs

SELECT
    p.PERSON_ID,
    p.FIRST_NAME,
    p.LAST_NAME,
    b.PROGRAM_NAME,
    b.PROGRAM_TYPE
FROM
    PER_PEOPLE_F p
    JOIN PER_BENEFIT_PROGRAMS b ON p.PERSON_ID = b.PERSON_ID
ORDER BY
    p.LAST_NAME, b.PROGRAM_NAME;

Notes

• Refer to the Oracle Fusion HCM implementation documentations for the latest updates. 
• For large datasets, consider optimizing queries with appropriate indexing and filtering to improve performance.

These tables and queries should help you manage and analyze various aspects of HCM data within Oracle Fusion Applications and develop the objects accordingly. 

SQL Queries Tables in Oracle Fusion Manufacturing

 In Oracle Fusion Applications, the Manufacturing module involves several key tables that store information related to production, work orders, inventory, and other manufacturing processes. Below are some of the primary tables involved in Manufacturing, along with sample SQL queries to illustrate how you can retrieve information from these tables.

Key Tables in Oracle Fusion Manufacturing

1. WIP_OPERATIONS

   • Stores details about work order operations.
   • Key columns: OPERATION_ID, WORK_ORDER_ID, OPERATION_SEQUENCE_ID, OPERATION_NAME, OPERATION_STATUS, etc.

2. WIP_WORK_ORDERS

   • Contains information about work orders.
   • Key columns: WORK_ORDER_ID, WORK_ORDER_NUMBER, PRODUCT_ID, START_DATE, END_DATE, STATUS, etc.

3. WIP_JOB_SCHEDULES

   • Manages job schedules related to work orders.
   • Key columns: JOB_SCHEDULE_ID, WORK_ORDER_ID, SCHEDULE_DATE, SCHEDULED_START_TIME, SCHEDULED_END_TIME, etc.

4. INV_MTL_SYSTEM_ITEMS_B

   • Stores item details from the inventory.
   • Key columns: INVENTORY_ITEM_ID, ITEM_ID, ITEM_CODE, ITEM_DESCRIPTION, CATEGORY_ID, etc.

5. INV_MTL_ON_HAND_QUANTITIES

   • Contains on-hand inventory quantities.
   • Key columns: INVENTORY_ITEM_ID, LOCATION_ID, ON_HAND_QUANTITY, LOT_NUMBER, etc.

6. WIP_DISCREPANCIES

   • Manages discrepancies in work orders.
   • Key columns: DISCREPANCY_ID, WORK_ORDER_ID, DISCREPANCY_TYPE, QUANTITY, STATUS, etc.

7. WIP_BOM

   • Bill of Materials (BOM) information for items.
   • Key columns: BOM_ID, ITEM_ID, COMPONENT_ID, QUANTITY_REQUIRED, BOM_LEVEL, etc.

8. WIP_ASSEMBLY_HISTORY

   • Historical data on assemblies and production activities.
   • Key columns: ASSEMBLY_HISTORY_ID, WORK_ORDER_ID, PRODUCT_ID, QUANTITY_PRODUCED, DATE_PRODUCED, etc.

9. WIP_WO_RESOURCES

   • Resources allocated to work orders.
   • Key columns: RESOURCE_ID, WORK_ORDER_ID, RESOURCE_TYPE, RESOURCE_QUANTITY, RESOURCE_STATUS, etc.

10. WIP_PROCESS_MATERIALS

    • Information about materials used in processes.
    • Key columns: PROCESS_ID, MATERIAL_ID, QUANTITY_USED, UNIT_OF_MEASURE, etc.

Sample SQL Queries

Here are some example queries that can be used to retrieve data from these tables:

1. Retrieve Basic Work Order Information

SELECT
    w.WORK_ORDER_ID,
    w.WORK_ORDER_NUMBER,
    w.PRODUCT_ID,
    w.START_DATE,
    w.END_DATE,
    w.STATUS,
    i.ITEM_CODE AS PRODUCT_CODE
FROM
    WIP_WORK_ORDERS w
    JOIN INV_MTL_SYSTEM_ITEMS_B i ON w.PRODUCT_ID = i.ITEM_ID
WHERE
    w.START_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
    w.START_DATE DESC;

2. Get Work Order Operations Details

SELECT
    o.OPERATION_ID,
    o.WORK_ORDER_ID,
    o.OPERATION_SEQUENCE_ID,
    o.OPERATION_NAME,
    o.OPERATION_STATUS,
    w.WORK_ORDER_NUMBER
FROM
    WIP_OPERATIONS o
    JOIN WIP_WORK_ORDERS w ON o.WORK_ORDER_ID = w.WORK_ORDER_ID
WHERE
    w.STATUS = 'In Progress'
ORDER BY
    o.OPERATION_SEQUENCE_ID;

3. Check Inventory Levels for Items

SELECT
    i.ITEM_CODE,
    i.ITEM_DESCRIPTION,
    q.LOCATION_ID,
    q.ON_HAND_QUANTITY
FROM
    INV_MTL_SYSTEM_ITEMS_B i
    JOIN INV_MTL_ON_HAND_QUANTITIES q ON i.INVENTORY_ITEM_ID = q.INVENTORY_ITEM_ID
WHERE
    i.ITEM_CODE = 'ITEMX'
ORDER BY
    q.LOCATION_ID;

4. List Discrepancies for Work Orders

SELECT
    d.DISCREPANCY_ID,
    d.WORK_ORDER_ID,
    d.DISCREPANCY_TYPE,
    d.QUANTITY,
    d.STATUS,
    w.WORK_ORDER_NUMBER
FROM
    WIP_DISCREPANCIES d
    JOIN WIP_WORK_ORDERS w ON d.WORK_ORDER_ID = w.WORK_ORDER_ID
WHERE
    d.STATUS = 'Open'
ORDER BY
    d.DISCREPANCY_ID;

5. Retrieve Bill of Materials for a Product

SELECT
    b.BOM_ID,
    b.ITEM_ID AS PRODUCT_ID,
    b.COMPONENT_ID,
    b.QUANTITY_REQUIRED,
    c.ITEM_CODE AS COMPONENT_CODE
FROM
    WIP_BOM b
    JOIN INV_MTL_SYSTEM_ITEMS_B c ON b.COMPONENT_ID = c.ITEM_ID
WHERE
    b.ITEM_ID = 'PRODUCTX'
ORDER BY
    b.BOM_LEVEL;

Notes

• The table names and columns might vary depending on your specific Oracle Fusion implementation and customization. Always refer to the schema documentation
• For large datasets, consider optimizing your queries with appropriate indexing and filtering to improve performance.

By utilizing these tables and queries, you can effectively manage and analyze manufacturing data within Oracle Fusion Applications.

SQL Queries Tables in Oracle Fusion Accounts Receivables

In Oracle Fusion Applications, Accounts Receivables (AR) involves several key tables that store various aspects of financial transactions, customer details, invoices, payments, and related information. Here’s a list of some important tables related to Accounts Receivables in Oracle Fusion:

Key Tables in Oracle Fusion Accounts Receivables

1. AR_INVOICE_HEADERS_ALL

   • Contains header information for invoices.
   • Key columns: INVOICE_ID, INVOICE_NUMBER, CUSTOMER_ID, STATUS, INVOICE_DATE, etc.

2. AR_INVOICE_LINES_ALL

   • Stores detailed line information for invoices.
   • Key columns: INVOICE_LINE_ID, INVOICE_ID, LINE_ID, LINE_AMOUNT, PRODUCT_ID, etc.

3. AR_CASH_RECEIPTS_ALL

   • Contains information about cash receipts.
   • Key columns: CASH_RECEIPT_ID, RECEIPT_NUMBER, CUSTOMER_ID, RECEIPT_DATE, AMOUNT_RECEIVED, etc.

4. AR_PAYMENT_SCHEDULES_ALL

   • Details payment schedules associated with invoices.
   • Key columns: PAYMENT_SCHEDULE_ID, INVOICE_ID, DUE_DATE, AMOUNT_DUE, etc.

5. AR_RECEIPT_APPLICATIONS_ALL

   • Manages the application of receipts to invoices.
   • Key columns: RECEIPT_APPLICATION_ID, RECEIPT_ID, INVOICE_ID, APPLIED_AMOUNT, etc.

6. AR_CUSTOMERS

   • Stores basic information about customers.
   • Key columns: CUSTOMER_ID, CUSTOMER_NAME, ACCOUNT_NUMBER, STATUS, etc.

7. AR_CUSTOMER_ACCOUNTS

   • Contains information about customer accounts.
   • Key columns: CUSTOMER_ACCOUNT_ID, CUSTOMER_ID, ACCOUNT_NUMBER, STATUS, etc.

8. AR_DISTRIBUTIONS_ALL

   • Includes details of the distribution of invoices and receipts.
   • Key columns: DISTRIBUTION_ID, INVOICE_ID, RECEIPT_ID, ACCOUNT_ID, AMOUNT, etc.

9. AR_ADJUSTMENTS_ALL

   • Manages adjustments made to invoices and receipts.
   • Key columns: ADJUSTMENT_ID, INVOICE_ID, RECEIPT_ID, ADJUSTMENT_AMOUNT, ADJUSTMENT_TYPE, etc.

10. AR_COLLECTIONS_ALL

    • Contains information about collections and collection activities.
    • Key columns: COLLECTION_ID, CUSTOMER_ID, COLLECTION_DATE, AMOUNT_COLLECTED, etc.

11. AR_TAX_LINES_ALL

    • Stores tax details associated with invoices.
    • Key columns: TAX_LINE_ID, INVOICE_ID, TAX_AMOUNT, TAX_CODE, etc.

12. AR_INVOICE_ACCOUNTS

    • Holds information on the accounting aspects of invoices.
    • Key columns: INVOICE_ACCOUNT_ID, INVOICE_ID, ACCOUNTING_DATE, ACCOUNTING_AMOUNT, etc.

Sample SQL Query

Here’s a sample SQL query that joins some of these tables to retrieve basic invoice and payment information:

SELECT
    i.INVOICE_ID,
    i.INVOICE_NUMBER,
    i.INVOICE_DATE,
    i.CUSTOMER_ID,
    c.CUSTOMER_NAME,
    l.LINE_ID,
    l.LINE_AMOUNT,
    r.RECEIPT_ID,
    r.AMOUNT_RECEIVED
FROM
    AR_INVOICE_HEADERS_ALL i
    JOIN AR_INVOICE_LINES_ALL l ON i.INVOICE_ID = l.INVOICE_ID
    LEFT JOIN AR_CASH_RECEIPTS_ALL r ON i.INVOICE_ID = r.INVOICE_ID
    JOIN AR_CUSTOMERS c ON i.CUSTOMER_ID = c.CUSTOMER_ID
WHERE
    i.INVOICE_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
    i.INVOICE_DATE DESC;

Notes

• The actual table names and structures might vary depending on your Oracle Fusion implementation and customizations. It’s important to consult your specific Fusion schema or documentation.
• Ensure you have the necessary permissions to access these tables and views.
• When querying large datasets, consider performance optimization techniques such as indexing and filtering.

Using these tables, you can manage and analyze various aspects of Accounts Receivables within Oracle Fusion, helping to streamline financial processes and improve reporting accuracy.