WIP Useful Scripts

Query to find WIP Job details

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_CODEFROM 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 DWHERE E.WIP_ENTITY_NAME='17303'AND E.ORGANIZATION_ID = 207AND SG.SCHEDULE_GROUP_ID (+) = D.SCHEDULE_GROUP_IDAND SG.ORGANIZATION_ID(+) = D.ORGANIZATION_IDAND WL.LINE_ID (+) = D.LINE_IDAND WL.ORGANIZATION_ID (+) = D.ORGANIZATION_IDAND L.INVENTORY_LOCATION_ID(+) = NVL(D.COMPLETION_LOCATOR_ID,'-1')AND L.ORGANIZATION_ID (+) = D.ORGANIZATION_IDAND D.WIP_ENTITY_ID = E.WIP_ENTITY_IDAND M.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_IDAND 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 = 207AND M.ORGANIZATION_ID(+) = 207AND    SI2.ORGANIZATION_ID (+) = 207AND    SI3.ORGANIZATION_ID (+) = 207AND    SI2.INVENTORY_ITEM_ID (+) = D.BOM_REFERENCE_IDAND    SI3.INVENTORY_ITEM_ID (+) = D.ROUTING_REFERENCE_IDORDER BY 1/

Query to find WIP Job components

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 <> 6ORDER BY 1,2/

Query to find WIP Job operations

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 DescriptionFROM   MFG_LOOKUPS ML1,       MFG_LOOKUPS ML2,       BOM_DEPARTMENTS BD,       WIP_DISCRETE_JOBS DJ,       WIP_OPERATIONS WO,       WIP_ENTITIES WEWHERE WO.WIP_ENTITY_ID = 31649AND    WO.ORGANIZATION_ID = 207AND    DJ.ORGANIZATION_ID = 207AND    BD.ORGANIZATION_ID = 207AND    DJ.WIP_ENTITY_ID = WO.WIP_ENTITY_IDAND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_IDAND    WO.DEPARTMENT_ID = BD.DEPARTMENT_IDAND    ML1.LOOKUP_TYPE = 'BOM_COUNT_POINT_TYPE'AND    ML1.LOOKUP_CODE = WO.COUNT_POINT_TYPEAND    ML2.LOOKUP_TYPE = 'SYS_YES_NO'AND    ML2.LOOKUP_CODE = WO.BACKFLUSH_FLAGORDER BY WO.OPERATION_SEQ_NUM/

Query to find WIP Job resources

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_AppliedFROM   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 WEWHERE  WOR.WIP_ENTITY_ID = 31649AND    WOR.ORGANIZATION_ID = 207AND    DJ.ORGANIZATION_ID = 207AND    BR.ORGANIZATION_ID = 207AND    DJ.WIP_ENTITY_ID = WOR.WIP_ENTITY_IDAND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_IDAND    WOR.RESOURCE_ID = BR.RESOURCE_IDAND    WOR.ACTIVITY_ID = CA.ACTIVITY_ID (+)AND    ML1.LOOKUP_TYPE = 'CST_BASIS'AND    ML1.LOOKUP_CODE = WOR.BASIS_TYPEAND    ML2.LOOKUP_TYPE = 'BOM_RESOURCE_SCHEDULE_TYPE'AND    ML2.LOOKUP_CODE = WOR.SCHEDULED_FLAGAND    ML3.LOOKUP_TYPE = 'BOM_AUTOCHARGE_TYPE'AND    ML3.LOOKUP_CODE = WOR.AUTOCHARGE_TYPEAND    ML4.LOOKUP_TYPE = 'SYS_YES_NO'AND    ML4.LOOKUP_CODE = WOR.STANDARD_RATE_FLAGORDER BY WOR.OPERATION_SEQ_NUM, WOR.RESOURCE_SEQ_NUM/

Query to find WIP Job reservations

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_UOMFROM   MTL_SALES_ORDERS MS,       WIP_DISCRETE_JOBS DJ,       WIP_RESERVATIONS_V WRV,       WIP_ENTITIES WEWHERE  WRV.WIP_ENTITY_ID = 31649AND    WRV.ORGANIZATION_ID = 207AND    DJ.ORGANIZATION_ID = 207AND    DJ.WIP_ENTITY_ID = WRV.WIP_ENTITY_IDAND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_IDAND    MS.SALES_ORDER_ID = WRV.DEMAND_SOURCE_HEADER_IDORDER 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]

部分脚本摘自：http://snroracle.blogspot.jp/2012/09/wip-job-related-scripts.html