View: CRP_RESOURCE_REQUIREMENTS_V

Product:	CRP - Capacity
Description:	View of detailed resource requirements
Implementation/DBA Data:	APPS.CRP_RESOURCE_REQUIREMENTS_V

View Text

SELECT RES.ROWID
, 'CRP_RESOURCE_PLAN'
, RES.TRANSACTION_ID
, RES.ORGANIZATION_ID
, MTL.ORGANIZATION_CODE
, RES.DESIGNATOR
, RES.DEPARTMENT_ID
, DEPT.DEPARTMENT_CODE
, RES.RESOURCE_ID
, BOM_RES.RESOURCE_CODE
, RES.RESOURCE_DATE
, RES.RESOURCE_END_DATE
, RES.ASSEMBLY_ITEM_ID
, IFF.CONCATENATED_SEGMENTS
, RES.SOURCE_ITEM_ID
, DECODE(RES.SOURCE_ITEM_ID
, NULL
, NULL
, IFF2.CONCATENATED_SEGMENTS)
, RES.SOURCE_TRANSACTION_ID
, RES.RESOURCE_HOURS
, RES.DAILY_RESOURCE_HOURS
, RES.OPERATION_SEQ_NUM
, MR.NEW_SCHEDULE_DATE
, MR.NEW_ORDER_QUANTITY
, MRP_GET_ORDER.SUPPLY_ORDER(MR.ORDER_TYPE
, MR.DISPOSITION_ID
, MR.COMPILE_DESIGNATOR
, MR.ORGANIZATION_ID
, MR.INVENTORY_ITEM_ID
, MR.BY_PRODUCT_USING_ASSY_ID)
, L.MEANING
, MR.DAILY_RATE
, MR.LAST_UNIT_COMPLETION_DATE
, RES.LOAD_RATE
, RES.REPETITIVE_TYPE
FROM MFG_LOOKUPS L
, MTL_PARAMETERS MTL
, BOM_RESOURCES BOM_RES
, BOM_DEPARTMENTS DEPT
, MTL_SYSTEM_ITEMS_KFV IFF
, MTL_SYSTEM_ITEMS_KFV IFF2
, MRP_RECOMMENDATIONS MR
, CRP_RESOURCE_PLAN RES
WHERE L.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L.LOOKUP_CODE = MR.ORDER_TYPE
AND MTL.ORGANIZATION_ID = RES.ORGANIZATION_ID
AND DEPT.DEPARTMENT_ID = RES.DEPARTMENT_ID
AND BOM_RES.RESOURCE_ID = RES.RESOURCE_ID
AND IFF2.ORGANIZATION_ID = RES.ORGANIZATION_ID
AND IFF2.INVENTORY_ITEM_ID = NVL(RES.SOURCE_ITEM_ID
, RES.ASSEMBLY_ITEM_ID)
AND IFF.ORGANIZATION_ID = RES.ORGANIZATION_ID
AND IFF.INVENTORY_ITEM_ID = RES.ASSEMBLY_ITEM_ID
AND MR.TRANSACTION_ID = RES.SOURCE_TRANSACTION_ID
AND RES.RESOURCE_ID <> -1 UNION ALL SELECT RES.ROWID
, 'CRP_RESOURCE_PLAN'
, RES.TRANSACTION_ID
, RES.ORGANIZATION_ID
, MTL.ORGANIZATION_CODE
, RES.DESIGNATOR
, RES.DEPARTMENT_ID
, LINE.LINE_CODE
, RES.RESOURCE_ID
, NULL
, RES.RESOURCE_DATE
, RES.RESOURCE_END_DATE
, RES.ASSEMBLY_ITEM_ID
, IFF.CONCATENATED_SEGMENTS
, RES.SOURCE_ITEM_ID
, DECODE(RES.SOURCE_ITEM_ID
, NULL
, NULL
, IFF2.CONCATENATED_SEGMENTS)
, RES.SOURCE_TRANSACTION_ID
, RES.RESOURCE_HOURS
, TO_NUMBER(NULL)
, RES.OPERATION_SEQ_NUM
, MR.NEW_SCHEDULE_DATE
, MR.NEW_ORDER_QUANTITY
, MRP_GET_ORDER.SUPPLY_ORDER(MR.ORDER_TYPE
, MR.DISPOSITION_ID
, MR.COMPILE_DESIGNATOR
, MR.ORGANIZATION_ID
, MR.INVENTORY_ITEM_ID
, MR.BY_PRODUCT_USING_ASSY_ID)
, L.MEANING
, RES.DAILY_RESOURCE_HOURS
, MR.LAST_UNIT_COMPLETION_DATE
, RES.LOAD_RATE
, RES.REPETITIVE_TYPE
FROM MFG_LOOKUPS L
, MTL_PARAMETERS MTL
, WIP_LINES LINE
, MTL_SYSTEM_ITEMS_KFV IFF
, MTL_SYSTEM_ITEMS_KFV IFF2
, MRP_RECOMMENDATIONS MR
, CRP_RESOURCE_PLAN RES
WHERE L.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L.LOOKUP_CODE = MR.ORDER_TYPE
AND MTL.ORGANIZATION_ID = RES.ORGANIZATION_ID
AND LINE.LINE_ID = RES.DEPARTMENT_ID
AND IFF2.ORGANIZATION_ID = RES.ORGANIZATION_ID
AND IFF2.INVENTORY_ITEM_ID = NVL(RES.SOURCE_ITEM_ID
, RES.ASSEMBLY_ITEM_ID)
AND IFF.ORGANIZATION_ID = RES.ORGANIZATION_ID
AND IFF.INVENTORY_ITEM_ID = RES.ASSEMBLY_ITEM_ID
AND MR.TRANSACTION_ID = RES.SOURCE_TRANSACTION_ID
AND RES.RESOURCE_ID = -1 UNION ALL SELECT SUGG.ROWID
, 'MRP_SUGG_REP_SCHEDULES'
, -1
, SUGG.ORGANIZATION_ID
, MTL.ORGANIZATION_CODE
, SUGG.COMPILE_DESIGNATOR
, SUGG.REPETITIVE_LINE
, LINES.LINE_CODE
, -1
, NULL
, SUGG.FIRST_UNIT_START_DATE
, SUGG.LAST_UNIT_START_DATE
, SUGG.INVENTORY_ITEM_ID
, IFF.CONCATENATED_SEGMENTS
, SUGG.SOURCE_ITEM_ID
, DECODE(SUGG.SOURCE_ITEM_ID
, NULL
, NULL
, IFF.CONCATENATED_SEGMENTS)
, SUGG.TRANSACTION_ID
, SUGG.DAILY_RATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MR.NEW_SCHEDULE_DATE
, MR.NEW_ORDER_QUANTITY
, NULL
, L.MEANING
, MR.DAILY_RATE
, MR.LAST_UNIT_COMPLETION_DATE
, SUGG.LOAD_FACTOR_RATE
, 1
FROM MFG_LOOKUPS L
, MTL_SYSTEM_ITEMS_KFV IFF
, MTL_PARAMETERS MTL
, WIP_LINES LINES
, MRP_RECOMMENDATIONS MR
, MRP_SUGG_REP_SCHEDULES SUGG
WHERE L.LOOKUP_CODE = 13
AND L.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND IFF.INVENTORY_ITEM_ID = SUGG.INVENTORY_ITEM_ID
AND IFF.ORGANIZATION_ID = SUGG.ORGANIZATION_ID
AND MTL.ORGANIZATION_ID = SUGG.ORGANIZATION_ID
AND LINES.LINE_ID = SUGG.REPETITIVE_LINE
AND MR.TRANSACTION_ID = SUGG.TRANSACTION_ID UNION ALL SELECT MR.ROWID
, 'MRP_RECOMMENDATIONS'
, -2
, MR.ORGANIZATION_ID
, MTL.ORGANIZATION_CODE
, MR.COMPILE_DESIGNATOR
, MR.LINE_ID
, LINES.LINE_CODE
, -1
, NULL
, NVL(MR.NEW_WIP_START_DATE
, MR.NEW_SCHEDULE_DATE)
, NVL(MR.NEW_WIP_START_DATE
, MR.NEW_SCHEDULE_DATE)
, MR.INVENTORY_ITEM_ID
, IFF.CONCATENATED_SEGMENTS
, MR.SOURCE_ITEM_ID
, DECODE(MR.SOURCE_ITEM_ID
, NULL
, NULL
, IFF.CONCATENATED_SEGMENTS)
, MR.TRANSACTION_ID
, MR.NEW_ORDER_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MR.NEW_SCHEDULE_DATE
, MR.NEW_ORDER_QUANTITY
, MRP_GET_ORDER.SUPPLY_ORDER(MR.ORDER_TYPE
, MR.DISPOSITION_ID
, MR.COMPILE_DESIGNATOR
, MR.ORGANIZATION_ID
, MR.INVENTORY_ITEM_ID
, MR.BY_PRODUCT_USING_ASSY_ID)
, L.MEANING
, MR.NEW_ORDER_QUANTITY
, MR.LAST_UNIT_COMPLETION_DATE
, MR.NEW_ORDER_QUANTITY
, 2
FROM MFG_LOOKUPS L
, MTL_SYSTEM_ITEMS_KFV IFF
, MTL_PARAMETERS MTL
, WIP_LINES LINES
, MRP_RECOMMENDATIONS MR
WHERE L.LOOKUP_CODE = MR.ORDER_TYPE
AND L.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND IFF.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID
AND IFF.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MTL.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND LINES.LINE_ID = MR.LINE_ID
AND MR.DISPOSITION_STATUS_TYPE <> 2
AND MR.LINE_ID IS NOT NULL
AND MR.ORDER_TYPE NOT IN (13
, 16
, 4)

Columns