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_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:
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:
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:
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:
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
References:
http://snroracle.blogspot.com/2012/09/wip-job-related-scripts.html
No comments:
Post a Comment