VIEW: APPS.MRP_EXCEPTION_DETAILS_V

Source

View Text - Preformatted

SELECT MED.EXCEPTION_ID , MED.COMPILE_DESIGNATOR , MED.ORGANIZATION_ID , MED.INVENTORY_ITEM_ID , MED.EXCEPTION_TYPE , MED.LAST_UPDATE_DATE , MED.LAST_UPDATED_BY , MED.CREATION_DATE , MED.CREATED_BY , MED.LAST_UPDATE_LOGIN , DECODE(MED.EXCEPTION_TYPE, 28, NULL, MTP.ORGANIZATION_CODE) , DECODE(MED.INVENTORY_ITEM_ID, NULL,NULL, -1,NULL, MRP_EXCEPTION_SC.ITEM_NUMBER(MED.ORGANIZATION_ID, MED.INVENTORY_ITEM_ID)), MRP_GET_PROJECT.LOOKUP_MEANING('MRP_EXCEPTION_CODE_TYPE', MED.EXCEPTION_TYPE) , 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, 17, NVL(MED.CHAR1,'0'), 18, NVL(MED.CHAR1,'0'), 19, DECODE(MED.DATE1, NULL, NVL(MOQ.PLANNING_GROUP,'0'), NVL(MR.PLANNING_GROUP,'0')), '0') , 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, 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, 19, MRP_GET_PROJECT.PROJECT(MGR2.PROJECT_ID), NULL) , DECODE(MED.EXCEPTION_TYPE, 19, MRP_GET_PROJECT.TASK(MGR2.TASK_ID), NULL) , DECODE(MED.EXCEPTION_TYPE, 2, TO_DATE(NULL), 3, TO_DATE(NULL), 6, TO_DATE(NULL), 7, TO_DATE(NULL), 17, TO_DATE(NULL), 18, TO_DATE(NULL), 20, TO_DATE(NULL), 21, TO_DATE(NULL), 22, TO_DATE(NULL), 23,TO_DATE(NULL), MED.DATE1) , 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, 28, MED.DATE1, NULL) , MED.DATE2, DECODE(MED.EXCEPTION_TYPE, 9, MR.SCHEDULE_COMPRESSION_DAYS, TO_NUMBER( NULL)) , DECODE(MED.EXCEPTION_TYPE, 21, NULL, 22, NULL, 23, NULL, 28, NULL, MED.QUANTITY) , DECODE(MED.EXCEPTION_TYPE, 12, MIL.INVENTORY_LOT_NUMBER, TO_NUMBER( NULL)) , 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, MRP_GET_PROJECT.LOOKUP_MEANING( 'MRP_FLP_SUPPLY_DEMAND_TYPE', 18), MRP_GET_PROJECT.LOOKUP_MEANING( 'MRP_ORDER_TYPE', MR.ORDER_TYPE)), MRP_GET_PROJECT.LOOKUP_MEANING( 'MRP_ORDER_TYPE', MR.ORDER_TYPE)) , DECODE(MED.EXCEPTION_TYPE, 15, DECODE(MGR2.INVENTORY_ITEM_ID, NULL, NULL, MRP_EXCEPTION_SC.ITEM_NUMBER(MGR2.ORGANIZATION_ID, MGR2.INVENTORY_ITEM_ID)), 16, DECODE(MGR2.INVENTORY_ITEM_ID, NULL, NULL, MRP_EXCEPTION_SC.ITEM_NUMBER(MGR2.ORGANIZATION_ID, MGR2.INVENTORY_ITEM_ID)), 19, DECODE(MGR2.INVENTORY_ITEM_ID, NULL, NULL, MRP_EXCEPTION_SC.ITEM_NUMBER(MGR2.ORGANIZATION_ID, MGR2.INVENTORY_ITEM_ID)), 23, DECODE(MGR2.INVENTORY_ITEM_ID, NULL, NULL, MRP_EXCEPTION_SC.ITEM_NUMBER(MGR2.ORGANIZATION_ID, MGR2.INVENTORY_ITEM_ID)), NULL) , 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, 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 , NVL(DEPT.DEPARTMENT_CODE,LINE.LINE_CODE) , RES.RESOURCE_CODE , DECODE(MED.EXCEPTION_TYPE, 21, MED.QUANTITY, 22, MED.QUANTITY, 23, MED.QUANTITY, 28, MED.QUANTITY, NULL), MED.supplier_id, DECODE (MED.EXCEPTION_TYPE, 28, MRP_EXCEPTION_SC.supplier (MED.supplier_id), TO_CHAR(NULL)), MED.supplier_site_id, DECODE (MED.EXCEPTION_TYPE, 28, MRP_EXCEPTION_SC.supplier_site (MED.supplier_site_id), TO_CHAR(NULL)) FROM MRP_EXCEPTION_DETAILS MED, MTL_PARAMETERS MTP, MRP_FULL_PEGGING MFP, MRP_GROSS_REQUIREMENTS MGR2, MRP_GROSS_REQUIREMENTS MGR, MRP_INVENTORY_LOTS MIL, MRP_RECOMMENDATIONS MR, MRP_ONHAND_QUANTITIES MOQ, BOM_DEPARTMENTS DEPT, BOM_RESOURCES RES, WIP_LINES LINE WHERE MOQ.TRANSACTION_ID (+) = MED.NUMBER1 AND MTP.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 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

View Text - HTML Formatted

SELECT MED.EXCEPTION_ID
, MED.COMPILE_DESIGNATOR
, MED.ORGANIZATION_ID
, MED.INVENTORY_ITEM_ID
, MED.EXCEPTION_TYPE
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
, DECODE(MED.EXCEPTION_TYPE
, 28
, NULL
, MTP.ORGANIZATION_CODE)
, DECODE(MED.INVENTORY_ITEM_ID
, NULL
, NULL
, -1
, NULL
, MRP_EXCEPTION_SC.ITEM_NUMBER(MED.ORGANIZATION_ID
, MED.INVENTORY_ITEM_ID))
, MRP_GET_PROJECT.LOOKUP_MEANING('MRP_EXCEPTION_CODE_TYPE'
, MED.EXCEPTION_TYPE)
, 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
, 17
, NVL(MED.CHAR1
, '0')
, 18
, NVL(MED.CHAR1
, '0')
, 19
, DECODE(MED.DATE1
, NULL
, NVL(MOQ.PLANNING_GROUP
, '0')
, NVL(MR.PLANNING_GROUP
, '0'))
, '0')
, 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
, 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
, 19
, MRP_GET_PROJECT.PROJECT(MGR2.PROJECT_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 19
, MRP_GET_PROJECT.TASK(MGR2.TASK_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 2
, TO_DATE(NULL)
, 3
, TO_DATE(NULL)
, 6
, TO_DATE(NULL)
, 7
, TO_DATE(NULL)
, 17
, TO_DATE(NULL)
, 18
, TO_DATE(NULL)
, 20
, TO_DATE(NULL)
, 21
, TO_DATE(NULL)
, 22
, TO_DATE(NULL)
, 23
, TO_DATE(NULL)
, MED.DATE1)
, 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
, 28
, MED.DATE1
, NULL)
, MED.DATE2
, DECODE(MED.EXCEPTION_TYPE
, 9
, MR.SCHEDULE_COMPRESSION_DAYS
, TO_NUMBER( NULL))
, DECODE(MED.EXCEPTION_TYPE
, 21
, NULL
, 22
, NULL
, 23
, NULL
, 28
, NULL
, MED.QUANTITY)
, DECODE(MED.EXCEPTION_TYPE
, 12
, MIL.INVENTORY_LOT_NUMBER
, TO_NUMBER( NULL))
, 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
, MRP_GET_PROJECT.LOOKUP_MEANING( 'MRP_FLP_SUPPLY_DEMAND_TYPE'
, 18)
, MRP_GET_PROJECT.LOOKUP_MEANING( 'MRP_ORDER_TYPE'
, MR.ORDER_TYPE))
, MRP_GET_PROJECT.LOOKUP_MEANING( 'MRP_ORDER_TYPE'
, MR.ORDER_TYPE))
, DECODE(MED.EXCEPTION_TYPE
, 15
, DECODE(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, MRP_EXCEPTION_SC.ITEM_NUMBER(MGR2.ORGANIZATION_ID
, MGR2.INVENTORY_ITEM_ID))
, 16
, DECODE(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, MRP_EXCEPTION_SC.ITEM_NUMBER(MGR2.ORGANIZATION_ID
, MGR2.INVENTORY_ITEM_ID))
, 19
, DECODE(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, MRP_EXCEPTION_SC.ITEM_NUMBER(MGR2.ORGANIZATION_ID
, MGR2.INVENTORY_ITEM_ID))
, 23
, DECODE(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, MRP_EXCEPTION_SC.ITEM_NUMBER(MGR2.ORGANIZATION_ID
, MGR2.INVENTORY_ITEM_ID))
, NULL)
, 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
, 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
, NVL(DEPT.DEPARTMENT_CODE
, LINE.LINE_CODE)
, RES.RESOURCE_CODE
, DECODE(MED.EXCEPTION_TYPE
, 21
, MED.QUANTITY
, 22
, MED.QUANTITY
, 23
, MED.QUANTITY
, 28
, MED.QUANTITY
, NULL)
, MED.SUPPLIER_ID
, DECODE (MED.EXCEPTION_TYPE
, 28
, MRP_EXCEPTION_SC.SUPPLIER (MED.SUPPLIER_ID)
, TO_CHAR(NULL))
, MED.SUPPLIER_SITE_ID
, DECODE (MED.EXCEPTION_TYPE
, 28
, MRP_EXCEPTION_SC.SUPPLIER_SITE (MED.SUPPLIER_SITE_ID)
, TO_CHAR(NULL))
FROM MRP_EXCEPTION_DETAILS MED
, MTL_PARAMETERS MTP
, MRP_FULL_PEGGING MFP
, MRP_GROSS_REQUIREMENTS MGR2
, MRP_GROSS_REQUIREMENTS MGR
, MRP_INVENTORY_LOTS MIL
, MRP_RECOMMENDATIONS MR
, MRP_ONHAND_QUANTITIES MOQ
, BOM_DEPARTMENTS DEPT
, BOM_RESOURCES RES
, WIP_LINES LINE
WHERE MOQ.TRANSACTION_ID (+) = MED.NUMBER1
AND MTP.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 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