WIP transactions table in Oracle Fusion

Hi friends, we are going to discuss about the WIP transactions table in oracle Fusion. We will share some of the important WIP transactions tables which we need in our day to day operations while working in the Oracle fusion ERP application. In Oracle Fusion, WIP transactions are quite changes as compared to the Oracle apps r12 wip transaction table. We will also share some of the important WIP transactions sql queries too which helps to extract the different types of WIP data in oracle fusion. Using these queries , We can develop the custom WIP reports in Oracle Fusion using BIP tool. We should have the complete idea of the WIP tables in oracle fusion if we are working as a Manufacturing consultant in Oracle Fusion Application. Please find below the complete detail about WIP transactions table in oracle Fusion.

WIP transactions table in Oracle Fusion

Top 6 WIP transactions table in oracle Fusion

1.INV_MATERIAL_TXNS

2.EGP_SYSTEM_ITEMS

3.INV_ORGANIZATION_DEFINITIONS_V

4.WIE_WO_OPERATIONS_V

5.WIE_OPERATION_TRANSACTIONS

6.WIE_WO_OPERATIONS_V

Important SQL Query to Extract the WIP Transactions data in Oracle fusion.

Here below is the detail sql query which helps to fetch the complete WIP transactions informations in oracle fusion.

SELECT ESI.ITEM_NUMBER COMP_CODE,

       ESI.DESCRIPTION COMP_DESC,

       SUM(IMT.TRANSACTION_QUANTITY) + NVL((SELECT SUM(WOT.TRANSACTION_QUANTITY)

                                            FROM WIE_OPERATION_TRANSACTIONS WOT,

                                                 WIE_WO_OPERATIONS_V WO

                                           WHERE WOT.ATTRIBUTE_CHAR9 IS NULL

                                             AND WOT.TO_DISPATCH_STATE = 'REJECT'

                                             AND WOT.WORK_ORDER_ID = WO.WORK_ORDER_ID

                                             AND WOT.WO_OPERATION_ID = WO.WO_OPERATION_ID

                                             AND WOT.ORGANIZATION_ID = 85

                                             AND TRUNC(WOT.TRANSACTION_DATE) >= :P_FROM_DATE

                                             AND TRUNC(WOT.TRANSACTION_DATE) <= :P_TO_DATE

                                             AND WOT.INVENTORY_ITEM_ID = IMT.INVENTORY_ITEM_ID

                                             AND WO.WORK_CENTER_NAME = WWO.WORK_CENTER_NAME),0) PROD_QTY,

       WWO.WORK_CENTER_NAME DEPT,

       IMT.ORGANIZATION_ID,

       IMT.INVENTORY_ITEM_ID,

       IMT.INVENTORY_ITEM_ID||'-'||WWO.WORK_CENTER_NAME UNI_LINK,

       (SELECT SUM(WOT.TRANSACTION_QUANTITY) REJ_QTY

        FROM WIE_OPERATION_TRANSACTIONS WOT,

             WIE_WO_OPERATIONS_V WO

       WHERE (WOT.ATTRIBUTE_CHAR9 IS NOT NULL AND WOT.ATTRIBUTE_CHAR9 != 'REWORK')

         AND WOT.WORK_ORDER_ID = WO.WORK_ORDER_ID

         AND WOT.WO_OPERATION_ID = WO.WO_OPERATION_ID

         AND WOT.ORGANIZATION_ID = IMT.ORGANIZATION_ID

         AND WOT.INVENTORY_ITEM_ID = IMT.INVENTORY_ITEM_ID

         AND WO.WORK_CENTER_NAME = WWO.WORK_CENTER_NAME

         AND TRUNC(WOT.TRANSACTION_DATE) >= :P_FROM_DATE

         AND TRUNC(WOT.TRANSACTION_DATE) <= :P_TO_DATE ) REJ_QTY,

         (SELECT SUM(WOT.TRANSACTION_QUANTITY)

            FROM WIE_OPERATION_TRANSACTIONS WOT,

                 WIE_WO_OPERATIONS_V WO

           WHERE WOT.ATTRIBUTE_CHAR9 = 'REWORK'

             AND WOT.WORK_ORDER_ID = WO.WORK_ORDER_ID

             AND WOT.WO_OPERATION_ID = WO.WO_OPERATION_ID

             AND WOT.ORGANIZATION_ID =85

             AND TRUNC(WOT.TRANSACTION_DATE) >= :P_FROM_DATE

             AND TRUNC(WOT.TRANSACTION_DATE) <= :P_TO_DATE

             AND WOT.INVENTORY_ITEM_ID = IMT.INVENTORY_ITEM_ID

             AND WO.WORK_CENTER_NAME = WWO.WORK_CENTER_NAME) REWORK_QTY,

         ( SELECT SUM(NVL(HOLD_QTY,0)) FROM

         (SELECT NVL(WOT.TRANSACTION_QUANTITY,0) HOLD_QTY

            FROM WIE_OPERATION_TRANSACTIONS WOT,

                 WIE_WO_OPERATIONS_V WO

           WHERE WOT.ATTRIBUTE_CHAR9 IS NULL

             AND WOT.TO_DISPATCH_STATE = 'REJECT'

             AND WOT.WORK_ORDER_ID = WO.WORK_ORDER_ID

             AND WOT.WO_OPERATION_ID = WO.WO_OPERATION_ID

             AND WOT.ORGANIZATION_ID =85

             AND TRUNC(WOT.TRANSACTION_DATE) >= :P_FROM_DATE

             AND TRUNC(WOT.TRANSACTION_DATE) <= :P_TO_DATE

             AND WOT.INVENTORY_ITEM_ID = IMT.INVENTORY_ITEM_ID

             AND WO.WORK_CENTER_NAME = WWO.WORK_CENTER_NAME

             UNION ALL 

          SELECT ABS(NVL(WOT.TRANSACTION_QUANTITY,0)) * -1

            FROM WIE_OPERATION_TRANSACTIONS WOT,

                 WIE_WO_OPERATIONS_V WO

           WHERE WOT.TO_DISPATCH_STATE = 'COMPLETE'

             AND WOT.TRANSACTION_TYPE_CODE = 'REJECT_TO_OP_COMPLETION'

             AND WOT.WORK_ORDER_ID = WO.WORK_ORDER_ID

             AND WOT.WO_OPERATION_ID = WO.WO_OPERATION_ID

             AND WOT.ORGANIZATION_ID =85

             AND TRUNC(WOT.TRANSACTION_DATE) >= :P_FROM_DATE

             AND TRUNC(WOT.TRANSACTION_DATE) <= :P_TO_DATE

             AND WOT.INVENTORY_ITEM_ID = IMT.INVENTORY_ITEM_ID

             AND WO.WORK_CENTER_NAME = WWO.WORK_CENTER_NAME) ) HOLD_QTY

FROM INV_MATERIAL_TXNS IMT,

     EGP_SYSTEM_ITEMS ESI,

     INV_ORGANIZATION_DEFINITIONS_V IOD,

     WIE_WO_OPERATIONS_V WWO

WHERE IMT.ORGANIZATION_ID=85

AND TRUNC(IMT.TRANSACTION_DATE) >= :P_FROM_DATE

AND TRUNC(IMT.TRANSACTION_DATE) <= :P_TO_DATE

AND IMT.TRANSACTION_TYPE_ID IN (17, 44)

AND IMT.ORGANIZATION_ID = ESI.ORGANIZATION_ID

AND IMT.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID

AND IMT.ORGANIZATION_ID = IOD.ORGANIZATION_ID

AND IMT.TRANSACTION_SOURCE_ID = WWO.WORK_ORDER_ID

AND IMT.OPERATION_SEQ_NUMBER_ID = WWO.WO_OPERATION_ID

GROUP BY ESI.ITEM_NUMBER,

       ESI.DESCRIPTION,

       WWO.WORK_CENTER_NAME,

       IMT.ORGANIZATION_ID,

       IMT.INVENTORY_ITEM_ID

WIP transactions table in Oracle Fusion

WIP transactions table in Oracle Fusion