View: MRPFV_PLAN_EXCEPTION_DETAILS

Product:	MRP - Master Scheduling/MRP
Description:	- Retrofitted
Implementation/DBA Data:	APPS.MRPFV_PLAN_EXCEPTION_DETAILS

View Text

SELECT MED.COMPILE_DESIGNATOR
, MTP.ORGANIZATION_CODE
, HRORG.NAME
, '_KF:INV:MSTK:SYSITEM'
, TO_DATE(DECODE(MED.EXCEPTION_TYPE
, 1
, MED.DATE1
, NULL ))
, TO_DATE(DECODE(MED.EXCEPTION_TYPE
, 8
, MED.DATE1
, 9
, MED.DATE1
, 10
, MED.DATE1
, 15
, MED.DATE1
, 16
, MED.DATE1
, 19
, MED.DATE1
, 23
, MED.DATE1
, NULL))
, TO_DATE(DECODE(MED.EXCEPTION_TYPE
, 12
, MED.DATE1
, NULL ))
, TO_DATE(DECODE(MED.EXCEPTION_TYPE
, 13
, MED.DATE1
, 14
, MED.DATE1
, NULL ))
, ML.MEANING
, DECODE(MED.EXCEPTION_TYPE
, 1
, NULL
, 2
, NULL
, 3
, NULL
, 4
, NULL
, 5
, NULL
, 11
, NULL
, 12
, NULL
, 14
, NULL
, 17
, NULL
, 18
, NULL
, 20
, NULL
, 21
, NULL
, 22
, NULL
, 23
, NULL
, 13
, MRP_GET_ORDER.SALES_ORDER(MGR.DISPOSITION_ID)
, 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))
, DECODE(MED.EXCEPTION_TYPE
, 13
, NULL
, 14
, NULL
, 21
, NULL
, 22
, NULL
, 23
, NULL
, 19
, DECODE(MED.DATE1
, NULL
, ML2.MEANING
, ML1.MEANING)
, ML1.MEANING)
, DECODE(MED.EXCEPTION_TYPE
, 21
, NULL
, 22
, NULL
, 23
, NULL
, MED.QUANTITY)
, DECODE(MED.EXCEPTION_TYPE
, 2
, MED.DATE1
, 3
, MED.DATE1
, 6
, MED.DATE1
, 7
, MED.DATE1
, 17
, MED.DATE1
, 18
, MED.DATE1
, 20
, MED.DATE1
, 21
, MED.DATE1
, 22
, MED.DATE1
, 23
, MED.DATE1
, NULL)
, MED.DATE2
, DECODE(MED.EXCEPTION_TYPE
, 9
, MR.SCHEDULE_COMPRESSION_DAYS
, TO_NUMBER( NULL))
, DECODE(MED.EXCEPTION_TYPE
, 12
, MIL.INVENTORY_LOT_NUMBER
, TO_NUMBER( NULL))
, '_KF:INV:MSTK:SYSITEM1'
, DECODE(MED.EXCEPTION_TYPE
, 15
, MRP_GET_ORDER.SALES_ORDER(MGR2.DISPOSITION_ID)
, 19
, MRP_GET_ORDER.SALES_ORDER(MGR2.DISPOSITION_ID)
, 23
, MRP_GET_ORDER.SALES_ORDER(MGR2.DISPOSITION_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 1
, NULL
, 2
, NULL
, 3
, NULL
, 4
, NULL
, 5
, NULL
, 11
, NULL
, 12
, NULL
, 20
, NULL
, 21
, NULL
, 22
, NULL
, 23
, NULL
, 13
, MGR.PLANNING_GROUP
, 17
, MED.CHAR1
, 18
, MED.CHAR1
, 19
, DECODE(MED.DATE1
, NULL
, MOQ.PLANNING_GROUP
, MR.PLANNING_GROUP)
, MR.PLANNING_GROUP)
, DECODE(MED.EXCEPTION_TYPE
, 1
, NULL
, 2
, NULL
, 3
, NULL
, 4
, NULL
, 5
, NULL
, 11
, NULL
, 12
, NULL
, 20
, NULL
, 21
, NULL
, 22
, NULL
, 23
, NULL
, 18
, MRP_GET_PROJECT.PROJECT(MED.NUMBER1)
, 17
, MRP_GET_PROJECT.PROJECT(MED.NUMBER1)
, 13
, MRP_GET_PROJECT.PROJECT(MGR.PROJECT_ID)
, 19
, DECODE(MED.DATE1
, NULL
, MRP_GET_PROJECT.PROJECT(MOQ.PROJECT_ID)
, MRP_GET_PROJECT.PROJECT(MR.PROJECT_ID))
, MRP_GET_PROJECT.PROJECT(MR.PROJECT_ID))
, DECODE(MED.EXCEPTION_TYPE
, 1
, NULL
, 2
, NULL
, 3
, NULL
, 4
, NULL
, 5
, NULL
, 11
, NULL
, 12
, NULL
, 20
, NULL
, 21
, NULL
, 22
, NULL
, 23
, NULL
, 18
, MRP_GET_PROJECT.TASK(MED.NUMBER2)
, 17
, MRP_GET_PROJECT.TASK(MED.NUMBER2)
, 13
, MRP_GET_PROJECT.TASK(MGR.TASK_ID)
, 19
, DECODE(MED.DATE1
, NULL
, MRP_GET_PROJECT.TASK(MOQ.TASK_ID)
, MRP_GET_PROJECT.TASK(MR.TASK_ID))
, MRP_GET_PROJECT.TASK(MR.TASK_ID))
, DECODE(MED.EXCEPTION_TYPE
, 19
, MRP_GET_PROJECT.PROJECT(MGR2.PROJECT_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 19
, MRP_GET_PROJECT.TASK(MGR2.TASK_ID)
, NULL)
, LINE.LINE_CODE
, DEPT.DEPARTMENT_CODE
, RES.RESOURCE_CODE
, DECODE(MED.EXCEPTION_TYPE
, 21
, MED.QUANTITY
, 22
, MED.QUANTITY
, 23
, MED.QUANTITY
, NULL)
, MED.ORGANIZATION_ID
, MED.INVENTORY_ITEM_ID
, DECODE(MED.EXCEPTION_TYPE
, 12
, TO_NUMBER(NULL)
, 17
, TO_NUMBER( NULL)
, 18
, TO_NUMBER( NULL)
, 13
, TO_NUMBER(NULL)
, 14
, TO_NUMBER(NULL)
, 21
, TO_NUMBER( NULL)
, 22
, TO_NUMBER(NULL)
, 23
, TO_NUMBER(NULL)
, MED.NUMBER1)
, DECODE(MED.EXCEPTION_TYPE
, 15
, MFP.DEMAND_ID
, 16
, MFP.DEMAND_ID
, 19
, MFP.DEMAND_ID
, 13
, MGR.DEMAND_ID
, 14
, MGR.DEMAND_ID
, TO_NUMBER( NULL))
, MED.DEPARTMENT_ID
, MED.RESOURCE_ID
, MED.LINE_ID
, DECODE(MED.EXCEPTION_TYPE
, 18
, NVL(MED.NUMBER1
, 0)
, 17
, NVL(MED.NUMBER1
, 0)
, 19
, DECODE(MED.DATE1
, NULL
, NVL(MOQ.PROJECT_ID
, 0)
, NVL(MR.PROJECT_ID
, 0))
, 0)
, DECODE(MED.EXCEPTION_TYPE
, 18
, NVL(MED.NUMBER2
, 0)
, 17
, NVL(MED.NUMBER2
, 0)
, 19
, DECODE(MED.DATE1
, NULL
, NVL(MOQ.TASK_ID
, 0)
, NVL(MR.TASK_ID
, 0))
, 0)
, DECODE(MED.EXCEPTION_TYPE
, 19
, MGR2.PROJECT_ID
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 19
, MGR2.TASK_ID
, NULL)
, MED.EXCEPTION_ID
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
FROM MTL_SYSTEM_ITEMS SYSITEM1
, MRP_GROSS_REQUIREMENTS MGR2
, MRP_FULL_PEGGING MFP
, MRP_GROSS_REQUIREMENTS MGR
, MRP_INVENTORY_LOTS MIL
, MRP_RECOMMENDATIONS MR
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML
, MTL_SYSTEM_ITEMS SYSITEM
, MTL_PARAMETERS MTP
, HR_ALL_ORGANIZATION_UNITS HRORG
, MRP_ONHAND_QUANTITIES MOQ
, BOM_DEPARTMENTS DEPT
, BOM_RESOURCES RES
, WIP_LINES LINE
, MRP_EXCEPTION_DETAILS MED
WHERE ML.LOOKUP_TYPE = 'MRP_EXCEPTION_CODE_TYPE'
AND ML.LOOKUP_CODE = MED.EXCEPTION_TYPE
AND ML1.LOOKUP_TYPE(+) = 'MRP_ORDER_TYPE'
AND ML1.LOOKUP_CODE(+) = MR.ORDER_TYPE
AND ML2.LOOKUP_TYPE = 'MRP_FLP_SUPPLY_DEMAND_TYPE'
AND ML2.LOOKUP_CODE = 18
AND MOQ.TRANSACTION_ID(+) = MED.NUMBER1
AND SYSITEM.ORGANIZATION_ID(+) = MED.ORGANIZATION_ID
AND SYSITEM.INVENTORY_ITEM_ID(+) = MED.INVENTORY_ITEM_ID
AND MTP.ORGANIZATION_ID = MED.ORGANIZATION_ID
AND HRORG.ORGANIZATION_ID = MED.ORGANIZATION_ID
AND MR.TRANSACTION_ID(+) = MED.NUMBER1
AND MGR.DEMAND_ID(+) = MED.NUMBER1
AND MFP.PEGGING_ID(+) = MED.NUMBER2
AND MGR2.DEMAND_ID(+) = MFP.DEMAND_ID
AND SYSITEM1.ORGANIZATION_ID(+) = MGR2.ORGANIZATION_ID
AND SYSITEM1.INVENTORY_ITEM_ID(+) = MGR2.INVENTORY_ITEM_ID
AND MIL.COMPILE_DESIGNATOR(+) = MED.COMPILE_DESIGNATOR
AND MIL.ORGANIZATION_ID(+) = MED.ORGANIZATION_ID
AND MIL.INVENTORY_ITEM_ID(+) = MED.INVENTORY_ITEM_ID
AND MIL.EXPIRED_LOT_ID(+) = MED.NUMBER1
AND DEPT.DEPARTMENT_ID(+)= MED.DEPARTMENT_ID
AND RES.RESOURCE_ID(+) = MED.RESOURCE_ID
AND LINE.LINE_ID(+) = MED.LINE_ID
AND LINE.ORGANIZATION_ID(+) = MED.ORGANIZATION_ID WITH READ ONLY

Columns