WIP Useful Scripts .

Query to find WIP Job details

[sql] view plain copy print ?

1. SELECT  
2.  E.WIP_ENTITY_ID,  
3.  E.WIP_ENTITY_NAME Job,  
4.  M.SEGMENT1 ITEM, --M.INVENTORY_ITEM_ID,   
5.  M.DESCRIPTION Item_Desc,  
6.  D.DESCRIPTION JOB_DESCRIPTION,  
7.  M.PRIMARY_UOM_CODE UOM,  
8.  D.START_QUANTITY Start_Quantity,  
9.  D.JOB_TYPE Job_Type,  
10.  D.COMPLETION_SUBINVENTORY Subinventory,  
11.  D.COMPLETION_LOCATOR_ID Compl_Loc_Id,  
12.  D.BOM_REVISION BOM_Revision,  
13.  D.BOM_REVISION_DATE BOM_REVISION_DATE,  
14.  SUBSTR(TO_CHAR(D.BOM_REVISION_DATE,'HH24:MI'),1,5) BOM_TIME,  
15.  D.SCHEDULED_START_DATE Scheduled_Start,  
16.  SUBSTR(TO_CHAR(D.SCHEDULED_START_DATE,'HH24:MI'),1,5) START_TIME,  
17.  LU.MEANING Status,  
18.  D.ROUTING_REVISION Routing_Revision,  
19.  D.ROUTING_REVISION_DATE ROUTING_REVISION_DATE,  
20.  SUBSTR(TO_CHAR(D.ROUTING_REVISION_DATE,'HH24:MI'),1,5) ROUTING_TIME,  
21.  D.SCHEDULED_COMPLETION_DATE Scheduled_Complete,  
22.  SI2.DESCRIPTION Bill_Ref_Description,  
23.  SI3.DESCRIPTION Rout_Ref_Description, SUBSTR(TO_CHAR(D.SCHEDULED_COMPLETION_DATE,'HH24:MI'),1,5) COMPLETION_TIME,  
24.  SG.SCHEDULE_GROUP_NAME,  
25.  D.BUILD_SEQUENCE,  
26.  WL.LINE_CODE  
27. FROM MTL_SYSTEM_ITEMS_VL M,  
28.  MTL_SYSTEM_ITEMS SI2,  
29.  MTL_SYSTEM_ITEMS SI3,  
30.  MFG_LOOKUPS LU,  
31.  MTL_ITEM_LOCATIONS L,  
32.  WIP_LINES WL,  
33.  WIP_SCHEDULE_GROUPS SG,  
34.  WIP_ENTITIES E,  
35.  WIP_DISCRETE_JOBS D  
36. WHERE E.WIP_ENTITY_NAME='17303'  
37. AND E.ORGANIZATION_ID = 207  
38. AND SG.SCHEDULE_GROUP_ID (+) = D.SCHEDULE_GROUP_ID  
39. AND SG.ORGANIZATION_ID(+) = D.ORGANIZATION_ID  
40. AND WL.LINE_ID (+) = D.LINE_ID  
41. AND WL.ORGANIZATION_ID (+) = D.ORGANIZATION_ID  
42. AND L.INVENTORY_LOCATION_ID(+) = NVL(D.COMPLETION_LOCATOR_ID,'-1')  
43. AND L.ORGANIZATION_ID (+) = D.ORGANIZATION_ID  
44. AND D.WIP_ENTITY_ID = E.WIP_ENTITY_ID  
45. AND M.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_ID  
46. AND LU.LOOKUP_TYPE = 'WIP_JOB_STATUS'  
47. AND LU.LOOKUP_CODE = D.STATUS_TYPE  
48. --AND D.STATUS_TYPE IN (1,3,4,6)   
49. AND E.ENTITY_TYPE in (1,5)  
50. AND D.ORGANIZATION_ID = 207  
51. AND M.ORGANIZATION_ID(+) = 207  
52. AND    SI2.ORGANIZATION_ID (+) = 207  
53. AND    SI3.ORGANIZATION_ID (+) = 207  
54. AND    SI2.INVENTORY_ITEM_ID (+) = D.BOM_REFERENCE_ID  
55. AND    SI3.INVENTORY_ITEM_ID (+) = D.ROUTING_REFERENCE_ID  
56. ORDER BY 1  
57. /  

SELECT
 E.WIP_ENTITY_ID,
 E.WIP_ENTITY_NAME Job,
 M.SEGMENT1 ITEM, --M.INVENTORY_ITEM_ID,
 M.DESCRIPTION Item_Desc,
 D.DESCRIPTION JOB_DESCRIPTION,
 M.PRIMARY_UOM_CODE UOM,
 D.START_QUANTITY Start_Quantity,
 D.JOB_TYPE Job_Type,
 D.COMPLETION_SUBINVENTORY Subinventory,
 D.COMPLETION_LOCATOR_ID Compl_Loc_Id,
 D.BOM_REVISION BOM_Revision,
 D.BOM_REVISION_DATE BOM_REVISION_DATE,
 SUBSTR(TO_CHAR(D.BOM_REVISION_DATE,'HH24:MI'),1,5) BOM_TIME,
 D.SCHEDULED_START_DATE Scheduled_Start,
 SUBSTR(TO_CHAR(D.SCHEDULED_START_DATE,'HH24:MI'),1,5) START_TIME,
 LU.MEANING Status,
 D.ROUTING_REVISION Routing_Revision,
 D.ROUTING_REVISION_DATE ROUTING_REVISION_DATE,
 SUBSTR(TO_CHAR(D.ROUTING_REVISION_DATE,'HH24:MI'),1,5) ROUTING_TIME,
 D.SCHEDULED_COMPLETION_DATE Scheduled_Complete,
 SI2.DESCRIPTION Bill_Ref_Description,
 SI3.DESCRIPTION Rout_Ref_Description, SUBSTR(TO_CHAR(D.SCHEDULED_COMPLETION_DATE,'HH24:MI'),1,5) COMPLETION_TIME,
 SG.SCHEDULE_GROUP_NAME,
 D.BUILD_SEQUENCE,
 WL.LINE_CODE
FROM MTL_SYSTEM_ITEMS_VL M,
 MTL_SYSTEM_ITEMS SI2,
 MTL_SYSTEM_ITEMS SI3,
 MFG_LOOKUPS LU,
 MTL_ITEM_LOCATIONS L,
 WIP_LINES WL,
 WIP_SCHEDULE_GROUPS SG,
 WIP_ENTITIES E,
 WIP_DISCRETE_JOBS D
WHERE E.WIP_ENTITY_NAME='17303'
AND E.ORGANIZATION_ID = 207
AND SG.SCHEDULE_GROUP_ID (+) = D.SCHEDULE_GROUP_ID
AND SG.ORGANIZATION_ID(+) = D.ORGANIZATION_ID
AND WL.LINE_ID (+) = D.LINE_ID
AND WL.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
AND L.INVENTORY_LOCATION_ID(+) = NVL(D.COMPLETION_LOCATOR_ID,'-1')
AND L.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
AND D.WIP_ENTITY_ID = E.WIP_ENTITY_ID
AND M.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_ID
AND LU.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU.LOOKUP_CODE = D.STATUS_TYPE
--AND D.STATUS_TYPE IN (1,3,4,6)
AND E.ENTITY_TYPE in (1,5)
AND D.ORGANIZATION_ID = 207
AND M.ORGANIZATION_ID(+) = 207
AND    SI2.ORGANIZATION_ID (+) = 207
AND    SI3.ORGANIZATION_ID (+) = 207
AND    SI2.INVENTORY_ITEM_ID (+) = D.BOM_REFERENCE_ID
AND    SI3.INVENTORY_ITEM_ID (+) = D.ROUTING_REFERENCE_ID
ORDER BY 1
/

Query to find WIP Job components

[sql] view plain copy print ?

1. SELECT WRO.WIP_ENTITY_ID WIP_Entity_ID,  
2.        SI.SEGMENT1 ITEM,  
3.        SI.DESCRIPTION CI_Description,  
4.        WRO.OPERATION_SEQ_NUM CI_Op_Seq,  
5.        SI.PRIMARY_UOM_CODE CI_UOM,  
6.        WRO.REQUIRED_QUANTITY Required_Quantity,  
7.        WRO.QUANTITY_ISSUED Quantity_Issued,  
8.        ( SELECT NVL(SUM(OHQD.PRIMARY_TRANSACTION_QUANTITY),0)  
9.            FROM MTL_ONHAND_QUANTITIES_DETAIL OHQD  
10.            WHERE OHQD.ORGANIZATION_ID = WRO.ORGANIZATION_ID  
11.              AND OHQD.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID  
12.          ) Quantity_On_Hand,  
13.        WRO.DATE_REQUIRED Date_Required,  
14.        ( SELECT ML1.MEANING  
15.            FROM MFG_LOOKUPS ML1  
16.            WHERE ML1.LOOKUP_TYPE = 'SYS_YES_NO'  
17.              AND ML1.LOOKUP_CODE = WRO.MRP_NET_FLAG  
18.          ) MRP_Net,  
19.        ( SELECT ML2.MEANING  
20.            FROM MFG_LOOKUPS ML2  
21.            WHERE ML2.LOOKUP_TYPE = 'WIP_SUPPLY_SHORT'  
22.              AND ML2.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE  
23.          ) CI_Supply_Type,  
24.        WRO.SUPPLY_SUBINVENTORY Subinv,  
25.        ( SELECT WRO.SUPPLY_LOCATOR_ID  
26.            FROM MTL_ITEM_LOCATIONS IL  
27.            WHERE IL.INVENTORY_LOCATION_ID = WRO.SUPPLY_LOCATOR_ID  
28.              AND IL.ORGANIZATION_ID = WRO.ORGANIZATION_ID  
29.          ) C_Supply_Loc_Data  
30.   FROM  MTL_SYSTEM_ITEMS_VL SI,  
31.         WIP_REQUIREMENT_OPERATIONS WRO  
32.  WHERE  WRO.WIP_ENTITY_ID = 31649  
33.  AND   WRO.ORGANIZATION_ID = 207  
34.  AND    SI.ORGANIZATION_ID = 207  
35.  AND    SI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID  
36.  AND    WRO.WIP_SUPPLY_TYPE <> 6  
37. ORDER BY 1,2  
38. /  

SELECT WRO.WIP_ENTITY_ID WIP_Entity_ID,
       SI.SEGMENT1 ITEM,
       SI.DESCRIPTION CI_Description,
       WRO.OPERATION_SEQ_NUM CI_Op_Seq,
       SI.PRIMARY_UOM_CODE CI_UOM,
       WRO.REQUIRED_QUANTITY Required_Quantity,
       WRO.QUANTITY_ISSUED Quantity_Issued,
       ( SELECT NVL(SUM(OHQD.PRIMARY_TRANSACTION_QUANTITY),0)
           FROM MTL_ONHAND_QUANTITIES_DETAIL OHQD
           WHERE OHQD.ORGANIZATION_ID = WRO.ORGANIZATION_ID
             AND OHQD.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
         ) Quantity_On_Hand,
       WRO.DATE_REQUIRED Date_Required,
       ( SELECT ML1.MEANING
           FROM MFG_LOOKUPS ML1
           WHERE ML1.LOOKUP_TYPE = 'SYS_YES_NO'
             AND ML1.LOOKUP_CODE = WRO.MRP_NET_FLAG
         ) MRP_Net,
       ( SELECT ML2.MEANING
           FROM MFG_LOOKUPS ML2
           WHERE ML2.LOOKUP_TYPE = 'WIP_SUPPLY_SHORT'
             AND ML2.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
         ) CI_Supply_Type,
       WRO.SUPPLY_SUBINVENTORY Subinv,
       ( SELECT WRO.SUPPLY_LOCATOR_ID
           FROM MTL_ITEM_LOCATIONS IL
           WHERE IL.INVENTORY_LOCATION_ID = WRO.SUPPLY_LOCATOR_ID
             AND IL.ORGANIZATION_ID = WRO.ORGANIZATION_ID
         ) C_Supply_Loc_Data
  FROM  MTL_SYSTEM_ITEMS_VL SI,
        WIP_REQUIREMENT_OPERATIONS WRO
 WHERE  WRO.WIP_ENTITY_ID = 31649
 AND   WRO.ORGANIZATION_ID = 207
 AND    SI.ORGANIZATION_ID = 207
 AND    SI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
 AND    WRO.WIP_SUPPLY_TYPE <> 6
ORDER BY 1,2
/

Query to find WIP Job operations

[sql] view plain copy print ?

1. SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,  
2.        WO.OPERATION_SEQ_NUM Op_Seq,  
3.        BD.DEPARTMENT_CODE Department,  
4.        ML1.MEANING Count_Point_Type,  
5.        ML2.MEANING Backflush,  
6.        TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE) Op_Cmpl_Date,  
7.        TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE, 'HH24:MI') Op_Cmpl_Time,  
8.        WO.SCHEDULED_QUANTITY Op_Scheduled_Qty,  
9.        (WO.QUANTITY_IN_QUEUE +  
10.        WO.QUANTITY_RUNNING +  
11.        WO.QUANTITY_WAITING_TO_MOVE +  
12.        WO.QUANTITY_REJECTED +  
13.        WO.QUANTITY_SCRAPPED) Qty_In_Operation,  
14.        WO.QUANTITY_COMPLETED Op_Qty_Completed,  
15.        WO.DESCRIPTION Description  
16. FROM   MFG_LOOKUPS ML1,  
17.        MFG_LOOKUPS ML2,  
18.        BOM_DEPARTMENTS BD,  
19.        WIP_DISCRETE_JOBS DJ,  
20.        WIP_OPERATIONS WO,  
21.        WIP_ENTITIES WE  
22. WHERE WO.WIP_ENTITY_ID = 31649  
23. AND    WO.ORGANIZATION_ID = 207  
24. AND    DJ.ORGANIZATION_ID = 207  
25. AND    BD.ORGANIZATION_ID = 207  
26. AND    DJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID  
27. AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID  
28. AND    WO.DEPARTMENT_ID = BD.DEPARTMENT_ID  
29. AND    ML1.LOOKUP_TYPE = 'BOM_COUNT_POINT_TYPE'  
30. AND    ML1.LOOKUP_CODE = WO.COUNT_POINT_TYPE  
31. AND    ML2.LOOKUP_TYPE = 'SYS_YES_NO'  
32. AND    ML2.LOOKUP_CODE = WO.BACKFLUSH_FLAG  
33. ORDER BY WO.OPERATION_SEQ_NUM  
34. /  

SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WO.OPERATION_SEQ_NUM Op_Seq,
       BD.DEPARTMENT_CODE Department,
       ML1.MEANING Count_Point_Type,
       ML2.MEANING Backflush,
       TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE) Op_Cmpl_Date,
       TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE, 'HH24:MI') Op_Cmpl_Time,
       WO.SCHEDULED_QUANTITY Op_Scheduled_Qty,
       (WO.QUANTITY_IN_QUEUE +
       WO.QUANTITY_RUNNING +
       WO.QUANTITY_WAITING_TO_MOVE +
       WO.QUANTITY_REJECTED +
       WO.QUANTITY_SCRAPPED) Qty_In_Operation,
       WO.QUANTITY_COMPLETED Op_Qty_Completed,
       WO.DESCRIPTION Description
FROM   MFG_LOOKUPS ML1,
       MFG_LOOKUPS ML2,
       BOM_DEPARTMENTS BD,
       WIP_DISCRETE_JOBS DJ,
       WIP_OPERATIONS WO,
       WIP_ENTITIES WE
WHERE WO.WIP_ENTITY_ID = 31649
AND    WO.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    BD.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND    ML1.LOOKUP_TYPE = 'BOM_COUNT_POINT_TYPE'
AND    ML1.LOOKUP_CODE = WO.COUNT_POINT_TYPE
AND    ML2.LOOKUP_TYPE = 'SYS_YES_NO'
AND    ML2.LOOKUP_CODE = WO.BACKFLUSH_FLAG
ORDER BY WO.OPERATION_SEQ_NUM
/

Query to find WIP Job resources

[sql] view plain copy print ?

1. SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,  
2.        WOR.OPERATION_SEQ_NUM R_Op_Seq,  
3.        WOR.RESOURCE_SEQ_NUM Res_Seq,  
4.        WOR.SCHEDULE_SEQ_NUM Sched_Seq,  
5.        BR.RESOURCE_CODE Res_Name,  
6.        WOR.UOM_CODE Res_Uom,  
7.        ML1.MEANING Basis_Type,  
8.        WOR.USAGE_RATE_OR_AMOUNT Usage_Rate,  
9.        NVL(WOR.ASSIGNED_UNITS,0) Assigned_Units,  
10.        ML2.MEANING Scheduled,  
11.        CA.ACTIVITY Activity,  
12.        ML3.MEANING Autocharge,  
13.        ML4.MEANING Standard_Rate,  
14.        DECODE(WOR.BASIS_TYPE,1,WOR.USAGE_RATE_OR_AMOUNT *  
15.        DJ.START_QUANTITY,2,WOR.USAGE_RATE_OR_AMOUNT) Standard_Units,  
16.        WOR.APPLIED_RESOURCE_UNITS Units_Applied  
17. FROM   MFG_LOOKUPS ML1,  
18.        MFG_LOOKUPS ML2,  
19.        MFG_LOOKUPS ML3,  
20.        MFG_LOOKUPS ML4,  
21.        BOM_RESOURCES BR,  
22.        CST_ACTIVITIES CA,  
23.        WIP_OPERATION_RESOURCES WOR,  
24.        WIP_DISCRETE_JOBS DJ,  
25.        WIP_ENTITIES WE  
26. WHERE  WOR.WIP_ENTITY_ID = 31649  
27. AND    WOR.ORGANIZATION_ID = 207  
28. AND    DJ.ORGANIZATION_ID = 207  
29. AND    BR.ORGANIZATION_ID = 207  
30. AND    DJ.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID  
31. AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID  
32. AND    WOR.RESOURCE_ID = BR.RESOURCE_ID  
33. AND    WOR.ACTIVITY_ID = CA.ACTIVITY_ID (+)  
34. AND    ML1.LOOKUP_TYPE = 'CST_BASIS'  
35. AND    ML1.LOOKUP_CODE = WOR.BASIS_TYPE  
36. AND    ML2.LOOKUP_TYPE = 'BOM_RESOURCE_SCHEDULE_TYPE'  
37. AND    ML2.LOOKUP_CODE = WOR.SCHEDULED_FLAG  
38. AND    ML3.LOOKUP_TYPE = 'BOM_AUTOCHARGE_TYPE'  
39. AND    ML3.LOOKUP_CODE = WOR.AUTOCHARGE_TYPE  
40. AND    ML4.LOOKUP_TYPE = 'SYS_YES_NO'  
41. AND    ML4.LOOKUP_CODE = WOR.STANDARD_RATE_FLAG  
42. ORDER BY WOR.OPERATION_SEQ_NUM, WOR.RESOURCE_SEQ_NUM  
43. /  

SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WOR.OPERATION_SEQ_NUM R_Op_Seq,
       WOR.RESOURCE_SEQ_NUM Res_Seq,
       WOR.SCHEDULE_SEQ_NUM Sched_Seq,
       BR.RESOURCE_CODE Res_Name,
       WOR.UOM_CODE Res_Uom,
       ML1.MEANING Basis_Type,
       WOR.USAGE_RATE_OR_AMOUNT Usage_Rate,
       NVL(WOR.ASSIGNED_UNITS,0) Assigned_Units,
       ML2.MEANING Scheduled,
       CA.ACTIVITY Activity,
       ML3.MEANING Autocharge,
       ML4.MEANING Standard_Rate,
       DECODE(WOR.BASIS_TYPE,1,WOR.USAGE_RATE_OR_AMOUNT *
       DJ.START_QUANTITY,2,WOR.USAGE_RATE_OR_AMOUNT) Standard_Units,
       WOR.APPLIED_RESOURCE_UNITS Units_Applied
FROM   MFG_LOOKUPS ML1,
       MFG_LOOKUPS ML2,
       MFG_LOOKUPS ML3,
       MFG_LOOKUPS ML4,
       BOM_RESOURCES BR,
       CST_ACTIVITIES CA,
       WIP_OPERATION_RESOURCES WOR,
       WIP_DISCRETE_JOBS DJ,
       WIP_ENTITIES WE
WHERE  WOR.WIP_ENTITY_ID = 31649
AND    WOR.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    BR.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    WOR.RESOURCE_ID = BR.RESOURCE_ID
AND    WOR.ACTIVITY_ID = CA.ACTIVITY_ID (+)
AND    ML1.LOOKUP_TYPE = 'CST_BASIS'
AND    ML1.LOOKUP_CODE = WOR.BASIS_TYPE
AND    ML2.LOOKUP_TYPE = 'BOM_RESOURCE_SCHEDULE_TYPE'
AND    ML2.LOOKUP_CODE = WOR.SCHEDULED_FLAG
AND    ML3.LOOKUP_TYPE = 'BOM_AUTOCHARGE_TYPE'
AND    ML3.LOOKUP_CODE = WOR.AUTOCHARGE_TYPE
AND    ML4.LOOKUP_TYPE = 'SYS_YES_NO'
AND    ML4.LOOKUP_CODE = WOR.STANDARD_RATE_FLAG
ORDER BY WOR.OPERATION_SEQ_NUM, WOR.RESOURCE_SEQ_NUM
/

Query to find WIP Job reservations

[sql] view plain copy print ?

1. SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,  
2.        WRV.DEMAND_SOURCE_LINE_NUMBER CR_Line,  
3.        WRV.DEMAND_CLASS_CODE Reservation_Demand_Class,  
4.        WRV.PRIMARY_QUANTITY  Reserved_Primary_Qty,  
5.        WRV.PRIMARY_UOM_CODE  Reserved_Primary_UOM,  
6.         WRV.RESERVED_LINE_UOM_QUANTITY Reserved_Line_Qty,  
7.         WRV.ORDER_LINE_UOM_CODE Reserved_Line_UOM  
8. FROM   MTL_SALES_ORDERS MS,  
9.        WIP_DISCRETE_JOBS DJ,  
10.        WIP_RESERVATIONS_V WRV,  
11.        WIP_ENTITIES WE  
12. WHERE  WRV.WIP_ENTITY_ID = 31649  
13. AND    WRV.ORGANIZATION_ID = 207  
14. AND    DJ.ORGANIZATION_ID = 207  
15. AND    DJ.WIP_ENTITY_ID = WRV.WIP_ENTITY_ID  
16. AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID  
17. AND    MS.SALES_ORDER_ID = WRV.DEMAND_SOURCE_HEADER_ID  
18.   
19. ORDER BY  WRV.DEMAND_SOURCE_LINE_NUMBER  

SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WRV.DEMAND_SOURCE_LINE_NUMBER CR_Line,
       WRV.DEMAND_CLASS_CODE Reservation_Demand_Class,
       WRV.PRIMARY_QUANTITY  Reserved_Primary_Qty,
       WRV.PRIMARY_UOM_CODE  Reserved_Primary_UOM,
        WRV.RESERVED_LINE_UOM_QUANTITY Reserved_Line_Qty,
        WRV.ORDER_LINE_UOM_CODE Reserved_Line_UOM
FROM   MTL_SALES_ORDERS MS,
       WIP_DISCRETE_JOBS DJ,
       WIP_RESERVATIONS_V WRV,
       WIP_ENTITIES WE
WHERE  WRV.WIP_ENTITY_ID = 31649
AND    WRV.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WRV.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    MS.SALES_ORDER_ID = WRV.DEMAND_SOURCE_HEADER_ID

ORDER BY  WRV.DEMAND_SOURCE_LINE_NUMBER

Some Basic WIP Related Queries

1.BOM

SELECT * FROM BOM_STRUCTURES_B WHERE ASSEMBLY_ITEM_ID =  242956;

SELECT * FROM BOM_COMPONENTS_B
WHERE BILL_SEQUENCE_ID in (SELECT BILL_SEQUENCE_ID FROM bom_structures_b WHERE ASSEMBLY_ITEM_ID =  242956);

SQL Scripts to print BOM Hierarchy

refer: http://blog.csdn.net/pan_tian/article/details/8003586

2.Routing

SELECT * FROM BOM_OPERATIONAL_ROUTINGS WHERE ASSEMBLY_ITEM_ID =  242956;

SELECT * FROM BOM_OPERATION_SEQUENCES
WHERE ROUTING_SEQUENCE_ID IN (SELECT ROUTING_SEQUENCE_ID FROM BOM_OPERATIONAL_ROUTINGS WHERE ASSEMBLY_ITEM_ID =  242956);

3.WIP

SELECT * FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'pt_job01';

SELECT * FROM WIP_DISCRETE_JOBS WHERE WIP_ENTITY_ID = (SELECT WIP_ENTITY_ID FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'pt_job01') ;

SELECT * FROM WIP_REQUIREMENT_OPERATIONS WHERE WIP_ENTITY_ID = (SELECT WIP_ENTITY_ID FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'pt_job01');

SELECT * FROM WIP_TRANSACTIONS WHERE WIP_ENTITY_ID = 751074

SELECT * FROM WIP_OPERATIONS WHERE WIP_ENTITY_ID = 751074

SELECT * FROM WIP_OPERATION_RESOURCES WHERE WIP_ENTITY_ID = 751074

Oracle官方提供一个wipjob11i.sql脚本，这个脚本基本可以把一个Discrete Job相关的所有数据都抓出来。

Discrete Job/EAM Work Order Diagnostic script wipjob11i.sql [ID 316142.1]