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

 

Oracle EBS – Workflow Background SQLs

 

Business Need:

Some random useful SQLs/Queries related to Oracle Applications (EBS) concurrent programs, schedules, workflow background programs, requisition import process, etc.

 

Queries:

All Concurrent Jobs ran more than 10 times during previous day

-- all concurrent job executions during a single day, more than 10
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-2.1 AND SYSDATE-1.1
--AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
HAVING COUNT(*) > 9
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Get name of above Concurrent Program ID

SELECT *
FROM APPS.FND_CONCURRENT_PROGRAMS_VL CP
--WHERE USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%%%';          -- use name from above SQL
WHERE CONCURRENT_PROGRAM_NAME LIKE 'XX%%%';
 

 

Get more details on frequent program – Eg: Workflow Background

-- qry sql - notes - check concurrent program running prod wfbg
-- WFBG & ReqImport Only, last 2.4 hrs ~around 72 should be good
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-.104 AND SYSDATE+.004
AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Requests by a specific user

-- Programs ran by <USER> during past 15 minutes
SELECT CR.REQUEST_ID, CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, TO_CHAR(CR.REQUEST_DATE, 'Mon/dd hh24miss') REQUEST_DATE, CR.REQUESTED_BY
  , CR.PHASE_CODE, DECODE(CR.PHASE_CODE, 'C', 'Completed', 'P', 'Pending', 'zOthers') AS PHASE_NAME
  , CR.STATUS_CODE, DECODE(CR.STATUS_CODE, 'C', 'Normal', 'I', 'Scheduled', 'zOthers') AS STATUS_NAME
  , CR.LOGFILE_NAME, CR.OUTFILE_NAME, CR.ARGUMENT_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-.004 AND SYSDATE+.004
-- AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = 1151              -- use user id of the given user
ORDER BY CR.REQUEST_DATE DESC;
 

All Requests during last 24 hours

-- Conc Requests ran during past 24 hours
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-1.004 AND SYSDATE+.004
-- AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Similar to above

SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-1.004 AND SYSDATE+.004
 AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;

  

Keywords:

Oracle EBS, R12, R12.2.8, Workflow, Background, Concurrent Programs, Concurrent Jobs, Executions, WFBG, FND, Query, SQL, SQLPLUS