WIP Tables & Random SQLs – Work In Progress, Discrete Job Tables

 

Business Case:

Some random SQLs related to WIP (Work In Progress/Process) module functionalities. Useful for analyzing and troubleshooting issues related to WIP Job failures, stuck jobs, on-screen errors, etc.

 

Tables:

Major Tables:

WIP_DISCRETE_JOBS
WIP_LINES
WIP_OPERATIONS
WIP_ENTITIES
WIP_TRANSACTIONS

 

Other Useful Tables:

WIP_MOVE_TRANSACTIONS
WIP_DISCRETE_JOBS
WIP_REPETITIVE_SCHEDULES
WIP_REQUIREMENT_OPERATIONS WRO - COMPONENT TABLE

 

Interface Tables:

WIP_MOVE_TXN_INTERFACE
WIP_COST_TXN_INTERFACE
WIP_JOB_SHEDULE_INTERFACE
WIP_JOB_DTLS_INTERFACE

 

There are many more tables in Oracle EBS WIP Module. Only the major ones are listed here. For details, please check Oracle eTRM.

 

SQLs:

WIP Job – Header Level Details:

SELECT WE.WIP_ENTITY_ID,      -- SOURCE HEADER REFERENCE ID IN INSTALL BASE
      WE.WIP_ENTITY_NAME,        -- SOURCE HEADER REFERENCE NUMBER IN INSTALL BASE
      WE.CREATION_DATE AS WE_CREATION_DATE,
      DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
      MSIB.SEGMENT1 ASSEMBLY_ITEM_NAME,
      MSIB.DESCRIPTION,
      DSRJ.CLASS_CODE,
      LU1.MEANING STATUS,
      DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
      DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
      DSRJ.START_QUANTITY,
      DSRJ.NET_QUANTITY,
      LU2.MEANING WIP_SUPPLY_TYPE,
      DSRJ.COMPLETION_SUBINVENTORY,
      BD.DEPARTMENT_CODE
FROM WIP_ENTITIES WE,
      WIP_DISCRETE_JOBS DSRJ,
      MTL_SYSTEM_ITEMS_B MSIB ,
      MFG_LOOKUPS LU1,
      MFG_LOOKUPS LU2,
      WIP_OPERATIONS WO,
      BOM_DEPARTMENTS BD,
      BOM_RESOURCES BR,
      WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU1.LOOKUP_CODE = DSRJ.STATUS_TYPE
AND LU2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU2.LOOKUP_CODE = DSRJ.WIP_SUPPLY_TYPE
AND WE.PRIMARY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WO.ORGANIZATION_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;

  

WIP Job – Header Level Details, Simpler Query: Same as above, but only required/basic columns selected.

SELECT  WE.PRIMARY_ITEM_ID AS FINAL_PRODUCT, WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
 DSRJ.CLASS_CODE,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
    DSRJ.COMPLETION_SUBINVENTORY
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ, 
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;

  

WIP Job Details – Components and Final Item Details:

SELECT  WE.PRIMARY_ITEM_ID AS FINAL_PRODUCT, WRO.INVENTORY_ITEM_ID AS COMPONENT_ITEM_ID, WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
 DSRJ.CLASS_CODE,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
    DSRJ.COMPLETION_SUBINVENTORY
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ, 
       WIP_REQUIREMENT_OPERATIONS WRO,
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WRO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WRO.ORGANIZATION_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;
 

  

WIP Job Details – Many QIP Job Related Details:

-- Query with many useful WIP Job Related Details
SELECT WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE)JOB_TYPE,
 MSIB.SEGMENT1 ASSEMBLY_ITEM_NAME,
 MSIB.DESCRIPTION,
 DSRJ.CLASS_CODE,
 LU1.MEANING STATUS,
 MMT.TRANSACTION_UOM UOM,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
 LU2.MEANING WIP_SUPPLY_TYPE,
    DSRJ.COMPLETION_SUBINVENTORY,
    MSIV.CONCATENATED_SEGMENTS,
    BD.DEPARTMENT_CODE
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ,        
MTL_SYSTEM_ITEMS_B MSIB ,
MFG_LOOKUPS LU1,
MFG_LOOKUPS LU2,
MTL_MATERIAL_TRANSACTIONS MMT,
 MTL_SYSTEM_ITEMS_VL MSIV,
       WIP_REQUIREMENT_OPERATIONS WRO,
        WIP_OPERATIONS WO,
      BOM_DEPARTMENTS BD,
        BOM_RESOURCES BR,
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='3401881'
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND WE.PRIMARY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU1.LOOKUP_CODE = DSRJ.STATUS_TYPE
AND LU2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU2.LOOKUP_CODE = DSRJ.WIP_SUPPLY_TYPE
AND WE.PRIMARY_ITEM_ID=MMT.INVENTORY_ITEM_ID
AND WE.WIP_ENTITY_ID=WRO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WRO.ORGANIZATION_ID
AND  MSIV.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIV.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WO.ORGANIZATION_ID
 AND BR.RESOURCE_ID = WOR.RESOURCE_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;
  

Keywords:

WIP, Work in Progress, Work In Process, Discrete Manufacturing, EBS, R12, R12.2.6, PL/SQL, Query, SQL, SQLPLUS