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.