VIEW: APPS.MRPFV_PLAN_EXCEPTION_DETAILS

Source

View Text - Preformatted

SELECT MED.COMPILE_DESIGNATOR , MTP.ORGANIZATION_CODE , HRORG.NAME , '_KF:INV:MSTK:sysitem' , DECODE(MED.EXCEPTION_TYPE, 1, MED.DATE1, to_date(NULL )) , 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, to_date(NULL)) , DECODE(MED.EXCEPTION_TYPE, 12, MED.DATE1,TO_DATE( NULL )) , DECODE(MED.EXCEPTION_TYPE, 13, MED.DATE1, 14, MED.DATE1, TO_DATE(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

View Text - HTML Formatted

SELECT MED.COMPILE_DESIGNATOR
, MTP.ORGANIZATION_CODE
, HRORG.NAME
, '_KF:INV:MSTK:SYSITEM'
, DECODE(MED.EXCEPTION_TYPE
, 1
, MED.DATE1
, TO_DATE(NULL ))
, 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
, TO_DATE(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 12
, MED.DATE1
, TO_DATE( NULL ))
, DECODE(MED.EXCEPTION_TYPE
, 13
, MED.DATE1
, 14
, MED.DATE1
, TO_DATE(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