View: MSC_EXCEPTION_DETAILS_V

Product:	MSC - Advanced Supply Chain Planning
Description:	this view picks up all the valid exception details
Implementation/DBA Data:	APPS.MSC_EXCEPTION_DETAILS_V

View Text

SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, DECODE(MED.EXCEPTION_TYPE
, 28
, TO_NUMBER(NULL)
, MED.ORGANIZATION_ID)
, MED.SR_INSTANCE_ID
, DECODE(MED.INVENTORY_ITEM_ID
, -1
, MR.INVENTORY_ITEM_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
, TO_CHAR(NULL)
, 38
, MSC_GET_NAME.FROM_ORG(MED.PLAN_ID
, MED.DEPARTMENT_ID
, MED.SR_INSTANCE_ID)
, 39
, MSC_GET_NAME.FROM_ORG(MED.PLAN_ID
, MED.DEPARTMENT_ID
, MED.SR_INSTANCE_ID)
, 50
, MSC_GET_NAME.FROM_ORG(MED.PLAN_ID
, MED.DEPARTMENT_ID
, MED.SR_INSTANCE_ID)
, 51
, MSC_GET_NAME.FROM_ORG(MED.PLAN_ID
, MED.DEPARTMENT_ID
, MED.SR_INSTANCE_ID)
, MSC_GET_NAME.ORG_CODE(MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID))
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, DECODE(MED.INVENTORY_ITEM_ID
, -1
, MSC_GET_NAME.ITEM_NAME(MR.INVENTORY_ITEM_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MIF.ITEM_NAME)
, DECODE(MED.INVENTORY_ITEM_ID
, -1
, MSC_GET_NAME.ITEM_DESC(MR.INVENTORY_ITEM_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MIF.DESCRIPTION)
, MIF.BUYER_NAME
, MIF.PLANNER_CODE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_EXCEPTION_CODE_TYPE'
, MED.EXCEPTION_TYPE)
, DECODE(MED.EXCEPTION_TYPE
, 18
, NVL(MED.NUMBER1
, 0)
, 17
, NVL(MED.NUMBER1
, 0)
, 19
, NVL(MED.NUMBER4
, MR.PROJECT_ID)
, 24
, NVL(MGR.PROJECT_ID
, 0)
, 26
, NVL(MGR.PROJECT_ID
, 0)
, 68
, NVL(MGR.PROJECT_ID
, 0)
, 0)
, DECODE(MED.EXCEPTION_TYPE
, 18
, NVL(MED.NUMBER2
, 0)
, 17
, NVL(MED.NUMBER2
, 0)
, 19
, DECODE(MED.NUMBER4
, NULL
, MR.TASK_ID
, MED.NUMBER1)
, 24
, NVL(MGR.TASK_ID
, 0)
, 26
, NVL(MGR.TASK_ID
, 0)
, 68
, NVL(MGR.TASK_ID
, 0)
, 0)
, DECODE(MED.EXCEPTION_TYPE
, 17
, NVL(MED.CHAR1
, '0')
, 18
, NVL(MED.CHAR1
, '0')
, 19
, 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
, 28
, NULL
, 35
, NULL
, 36
, NULL
, 37
, NULL
, 38
, NULL
, 39
, NULL
, 50
, NULL
, 51
, NULL
, 40
, NULL
, 41
, NULL
, 42
, NULL
, 18
, MSC_GET_NAME.PROJECT(MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 17
, MSC_GET_NAME.PROJECT(MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 13
, MSC_GET_NAME.PROJECT(MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, 24
, MSC_GET_NAME.PROJECT(MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, 25
, MSC_GET_NAME.PROJECT(MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, 19
, DECODE(MED.NUMBER4
, NULL
, MSC_GET_NAME.PROJECT(MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MSC_GET_NAME.PROJECT(MED.NUMBER4
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID))
, 26
, MSC_GET_NAME.PROJECT(MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, 68
, MSC_GET_NAME.PROJECT(MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, MSC_GET_NAME.PROJECT(MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_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
, 28
, NULL
, 35
, NULL
, 36
, NULL
, 37
, NULL
, 38
, NULL
, 39
, NULL
, 50
, NULL
, 51
, NULL
, 40
, NULL
, 41
, NULL
, 42
, NULL
, 18
, MSC_GET_NAME.TASK(MED.NUMBER2
, MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 17
, MSC_GET_NAME.TASK(MED.NUMBER2
, MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 13
, MSC_GET_NAME.TASK(MGR.TASK_ID
, MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, 24
, MSC_GET_NAME.TASK(MGR.TASK_ID
, MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, 25
, MSC_GET_NAME.TASK(MGR.TASK_ID
, MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, 19
, DECODE(MED.NUMBER4
, NULL
, MSC_GET_NAME.TASK(MR.TASK_ID
, MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MED.NUMBER1
, MED.NUMBER4
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID))
, 26
, MSC_GET_NAME.TASK(MGR.TASK_ID
, MR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, 68
, MSC_GET_NAME.PROJECT(MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MR.TASK_ID
, MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_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
, 28
, NULL
, 35
, NULL
, 36
, NULL
, 37
, NULL
, 38
, NULL
, 39
, NULL
, 50
, NULL
, 51
, NULL
, 40
, NULL
, 41
, NULL
, 42
, NULL
, 13
, MGR.PLANNING_GROUP
, 17
, MED.CHAR1
, 18
, MED.CHAR1
, 19
, MR.PLANNING_GROUP
, 24
, MGR.PLANNING_GROUP
, 25
, MGR.PLANNING_GROUP
, 26
, MGR.PLANNING_GROUP
, 68
, MGR.PLANNING_GROUP
, MR.PLANNING_GROUP)
, DECODE(MED.EXCEPTION_TYPE
, 19
, DECODE(MED.NUMBER3
, NULL
, MSC_GET_NAME.PROJECT(MGR2.PROJECT_ID
, MGR2.ORGANIZATION_ID
, MGR2.PLAN_ID
, MGR2.SR_INSTANCE_ID)
, MSC_GET_NAME.PROJECT(MED.NUMBER3
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID))
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 19
, DECODE(MED.NUMBER3
, NULL
, MSC_GET_NAME.TASK(MGR2.TASK_ID
, MGR2.PROJECT_ID
, MGR2.ORGANIZATION_ID
, MGR2.PLAN_ID
, MGR2.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MED.NUMBER2
, MED.NUMBER3
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID))
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 2
, TO_DATE(NULL)
, 3
, TO_DATE(NULL)
, 6
, TO_DATE(NULL)
, 7
, TO_DATE(NULL)
, 10
, MR.NEW_SCHEDULE_DATE
, 17
, MED.DATE1
, 18
, TO_DATE(NULL)
, 20
, TO_DATE(NULL)
, 21
, TO_DATE(NULL)
, 22
, TO_DATE(NULL)
, 23
, MGR2.USING_ASSEMBLY_DEMAND_DATE
, 27
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 28
, TO_DATE(NULL)
, 35
, MGR2.USING_ASSEMBLY_DEMAND_DATE
, 36
, TO_DATE(NULL)
, 37
, MR.NEW_SCHEDULE_DATE
, 38
, TO_DATE(NULL)
, 39
, TO_DATE(NULL)
, 50
, TO_DATE(NULL)
, 51
, TO_DATE(NULL)
, 40
, MR.NEW_SHIP_DATE
, 41
, MGR2.USING_ASSEMBLY_DEMAND_DATE
, 42
, MGR2.USING_ASSEMBLY_DEMAND_DATE
, 24
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 25
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 26
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 37
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 45
, TO_DATE(NULL)
, 46
, TO_DATE(NULL)
, 19
, DECODE( MED.NUMBER4
, NULL
, DECODE( NVL(MED.NUMBER1
, -1)
, MR.TRANSACTION_ID
, MR.NEW_SCHEDULE_DATE
, MED.DATE1)
, MED.DATE1)
, 52
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 54
, MR.NEW_SCHEDULE_DATE
, 55
, MR.NEW_SHIP_DATE
, 56
, MR.NEW_SHIP_DATE
, 61
, MR.NEW_SHIP_DATE
, 67
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 68
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 14
, MGR.USING_ASSEMBLY_DEMAND_DATE
, 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
, 35
, MED.DATE1
, 36
, MED.DATE1
, 37
, MED.DATE1
, 38
, MED.DATE1
, 39
, MED.DATE1
, 50
, MED.DATE1
, 51
, MED.DATE1
, 40
, MED.DATE1
, 41
, MED.DATE1
, 42
, MED.DATE1
, 45
, MED.DATE1
, 46
, MED.DATE1
, 48
, MED.DATE1
, 53
, MED.DATE1
, 54
, MR.NEW_DOCK_DATE
, 58
, MED.DATE1
, 59
, MED.DATE1
, 60
, MED.DATE1
, 55
, MED.DATE1
, 56
, MED.DATE1
, 57
, MED.DATE1
, TO_DATE(NULL))
, DECODE ( MED.EXCEPTION_TYPE
, 37
, MR.NEW_DOCK_DATE
, MED.DATE2 )
, DECODE(MED.EXCEPTION_TYPE
, 9
, MR.SCHEDULE_COMPRESS_DAYS
, TO_NUMBER( NULL))
, DECODE(MED.EXCEPTION_TYPE
, 21
, TO_NUMBER(NULL)
, 22
, TO_NUMBER(NULL)
, 23
, TO_NUMBER(NULL)
, 28
, TO_NUMBER(NULL)
, 35
, TO_NUMBER(NULL)
, 36
, MED.NUMBER4
, 37
, MED.NUMBER3
, 38
, TO_NUMBER(NULL)
, 39
, TO_NUMBER(NULL)
, 50
, TO_NUMBER(NULL)
, 51
, TO_NUMBER(NULL)
, 40
, TO_NUMBER(NULL)
, 41
, TO_NUMBER(NULL)
, 42
, TO_NUMBER(NULL)
, 45
, TO_NUMBER(NULL)
, 46
, TO_NUMBER(NULL)
, 49
, MSC_GET_NAME.DEMAND_QUANTITY(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.SUPPLIER_ID)
, 52
, MGR.USING_REQUIREMENT_QUANTITY
, 53
, TO_NUMBER(NULL)
, 54
, TO_NUMBER(NULL)
, 55
, TO_NUMBER(NULL)
, 56
, TO_NUMBER(NULL)
, 57
, MR.NEW_ORDER_QUANTITY
, 58
, MR.NEW_ORDER_QUANTITY
, 59
, MR.NEW_ORDER_QUANTITY
, 60
, MR.NEW_ORDER_QUANTITY
, 61
, TO_NUMBER(NULL)
, 62
, TO_NUMBER(NULL)
, 63
, TO_NUMBER(NULL)
, 64
, TO_NUMBER(NULL)
, 65
, TO_NUMBER(NULL)
, 66
, TO_NUMBER(NULL)
, 67
, MGR.USING_REQUIREMENT_QUANTITY
, MED.QUANTITY)
, DECODE(MED.EXCEPTION_TYPE
, 12
, MR.LOT_NUMBER
, TO_NUMBER( NULL))
, DECODE(MED.EXCEPTION_TYPE
, 1
, NULL
, 2
, NULL
, 3
, NULL
, 4
, NULL
, 5
, NULL
, 11
, NULL
, 12
, NULL
, 13
, MGR.ORDER_NUMBER
, 14
, NVL(MGR.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID))
, 17
, NULL
, 18
, NULL
, 20
, NULL
, 21
, NULL
, 22
, NULL
, 23
, NULL
, 24
, MGR.ORDER_NUMBER
, 25
, MGR.ORDER_NUMBER
, 26
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID)
, 27
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID)
, 28
, NULL
, 35
, NULL
, 38
, NULL
, 39
, NULL
, 50
, NULL
, 51
, NULL
, 40
, NULL
, 41
, NULL
, 42
, NULL
, 45
, NULL
, 46
, NULL
, 13
, MGR.ORDER_NUMBER
, 49
, MSC_GET_NAME.DEMAND_ORDER_NUMBER(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.SUPPLIER_ID)
, 52
, NVL(MGR.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID))
, 67
, NVL(MGR.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID))
, 68
, MGR.ORDER_NUMBER
, NVL(MR.ORDER_NUMBER
, MR.TRANSACTION_ID))
, NULL
, DECODE(MED.EXCEPTION_TYPE
, 15
, MSC_GET_NAME.ITEM_NAME(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 16
, MSC_GET_NAME.ITEM_NAME(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 19
, MSC_GET_NAME.ITEM_NAME(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 23
, MSC_GET_NAME.ITEM_NAME(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 24
, MSC_GET_NAME.ITEM_NAME(MGR.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 25
, MSC_GET_NAME.ITEM_NAME(MGR.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 26
, MSC_GET_NAME.ITEM_NAME(MGR.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 27
, MSC_GET_NAME.ITEM_NAME(MGR.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 35
, MSC_GET_NAME.ITEM_NAME(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 41
, MSC_GET_NAME.ITEM_NAME(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 42
, MSC_GET_NAME.ITEM_NAME(MGR2.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, 47
, MSC_GET_NAME.ITEM_NAME(MED.NUMBER2
, NULL
, NULL
, NULL)
, 68
, MSC_GET_NAME.ITEM_NAME(MGR.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 47
, MSC_GET_NAME.ITEM_NAME(MED.NUMBER2
, NULL
, NULL
, NULL)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 15
, MGR2.ORDER_NUMBER
, 19
, MGR2.ORDER_NUMBER
, 23
, NVL(MGR2.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MGR2.SCHEDULE_DESIGNATOR_ID))
, 24
, MGR.ORDER_NUMBER
, 25
, MGR.ORDER_NUMBER
, 26
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID)
, 27
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID)
, 35
, MSC_GET_NAME.DESIGNATOR(MGR2.SCHEDULE_DESIGNATOR_ID)
, 41
, MSC_GET_NAME.DESIGNATOR(MGR2.SCHEDULE_DESIGNATOR_ID)
, 42
, NVL(MGR2.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MGR2.SCHEDULE_DESIGNATOR_ID))
, 68
, MGR.ORDER_NUMBER
, 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)
, 24
, TO_NUMBER(NULL)
, 25
, TO_NUMBER(NULL)
, 26
, TO_NUMBER(NULL)
, 27
, TO_NUMBER(NULL)
, 28
, TO_NUMBER(NULL)
, 35
, TO_NUMBER(NULL)
, 36
, MED.NUMBER1
, 37
, MED.NUMBER1
, 38
, TO_NUMBER(NULL)
, 39
, TO_NUMBER(NULL)
, 50
, TO_NUMBER(NULL)
, 51
, TO_NUMBER(NULL)
, 40
, TO_NUMBER(NULL)
, 41
, MFP.TRANSACTION_ID
, 42
, TO_NUMBER(NULL)
, 49
, MED.SUPPLIER_SITE_ID
, 68
, 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
, 23
, MFP.DEMAND_ID
, 24
, MGR.DEMAND_ID
, 25
, MGR.DEMAND_ID
, 26
, MGR.DEMAND_ID
, 27
, MGR.DEMAND_ID
, 35
, MGR. DEMAND_ID
, 41
, MFP.DEMAND_ID
, 42
, MFP.DEMAND_ID
, 49
, MED.SUPPLIER_ID
, 52
, MED.NUMBER1
, 67
, MED.NUMBER1
, 68
, MGR.DEMAND_ID
, TO_NUMBER( NULL))
, DECODE(MED.DEPARTMENT_ID
, -1
, TO_NUMBER(NULL)
, MED.DEPARTMENT_ID)
, DECODE(MED.DEPARTMENT_ID
, -1
, TO_NUMBER(NULL)
, MED.RESOURCE_ID)
, DECODE ( MED.EXCEPTION_TYPE
, 55
, MR.SHIP_METHOD
, 56
, MR.SHIP_METHOD
, 57
, MR.SHIP_METHOD
, 59
, MR.SHIP_METHOD
, 40
, MR.SHIP_METHOD
, 61
, MR.SHIP_METHOD
, 38
, MSC_GET_NAME.SHIP_METHOD(MED.PLAN_ID
, MED.DEPARTMENT_ID
, MED.SR_INSTANCE_ID)
, 39
, MSC_GET_NAME.SHIP_METHOD(MED.PLAN_ID
, MED.DEPARTMENT_ID
, MED.SR_INSTANCE_ID)
, 50
, MSC_GET_NAME.SHIP_METHOD(MED.PLAN_ID
, MED.DEPARTMENT_ID
, MED.SR_INSTANCE_ID)
, 51
, MSC_GET_NAME.SHIP_METHOD(MED.PLAN_ID
, MED.DEPARTMENT_ID
, MED.SR_INSTANCE_ID)
, MSC_GET_NAME.DEPARTMENT_CODE( DECODE (MED.RESOURCE_ID
, -1
, 1
, 2)
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID))
, MSC_GET_NAME.RESOURCE_CODE(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, MSC_GET_NAME.RESOURCE_TYPE(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, MSC_GET_NAME.RESOURCE_TYPE(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 21
, MED.QUANTITY
, 22
, MED.QUANTITY
, 23
, MED.QUANTITY
, 28
, MED.QUANTITY
, 35
, MED.QUANTITY
, 36
, MED.QUANTITY
, 37
, MED.QUANTITY
, 38
, MED.QUANTITY
, 39
, MED.QUANTITY
, 50
, MED.QUANTITY
, 51
, MED.QUANTITY
, 40
, MED.QUANTITY
, 41
, MED.QUANTITY
, 42
, MED.QUANTITY
, 45
, MED.QUANTITY
, 46
, MED.QUANTITY
, 53
, MED.QUANTITY
, 54
, MED.QUANTITY
, 55
, MED.QUANTITY
, 56
, MED.QUANTITY
, 61
, MED.QUANTITY
, 67
, MED.QUANTITY
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, NULL
, NVL(MED.SUPPLIER_ID
, MR.SUPPLIER_ID))
, NVL(MED.SUPPLIER_ID
, MR.SUPPLIER_ID))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, NULL
, MSC_GET_NAME.SUPPLIER(NVL(MED.SUPPLIER_ID
, MR.SUPPLIER_ID)))
, MSC_GET_NAME.SUPPLIER(NVL(MED.SUPPLIER_ID
, MR.SUPPLIER_ID)))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, NULL
, NVL(MED.SUPPLIER_SITE_ID
, MR.SUPPLIER_SITE_ID))
, NVL(MED.SUPPLIER_SITE_ID
, MR.SUPPLIER_SITE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, NULL
, MSC_GET_NAME.SUPPLIER_SITE(NVL(MED.SUPPLIER_SITE_ID
, MR.SUPPLIER_SITE_ID)))
, MSC_GET_NAME.SUPPLIER_SITE(NVL(MED.SUPPLIER_SITE_ID
, MR.SUPPLIER_SITE_ID)))
, DECODE(MED.EXCEPTION_TYPE
, 24
, MGR.CUSTOMER_ID
, 25
, MGR.CUSTOMER_ID
, 26
, MGR.CUSTOMER_ID
, 27
, MGR.CUSTOMER_ID
, 52
, MGR.CUSTOMER_ID
, 13
, MGR.CUSTOMER_ID
, 67
, MGR.CUSTOMER_ID
, 68
, MGR.CUSTOMER_ID
, MGR2.CUSTOMER_ID)
, DECODE(MED.EXCEPTION_TYPE
, 24
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, 25
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, 26
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, 27
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, 52
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, 13
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, 67
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, 68
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, MSC_GET_NAME.CUSTOMER(MGR2.CUSTOMER_ID))
, DECODE(MED.EXCEPTION_TYPE
, 24
, MGR.CUSTOMER_SITE_ID
, 25
, MGR.CUSTOMER_SITE_ID
, 26
, MGR.CUSTOMER_SITE_ID
, 27
, MGR.CUSTOMER_SITE_ID
, 52
, MGR.CUSTOMER_SITE_ID
, 13
, MGR.CUSTOMER_SITE_ID
, 67
, MGR.CUSTOMER_SITE_ID
, 68
, MGR.SHIP_TO_SITE_ID
, MGR2.CUSTOMER_SITE_ID)
, DECODE(MED.EXCEPTION_TYPE
, 24
, MSC_GET_NAME.CUSTOMER_SITE(MGR.CUSTOMER_SITE_ID)
, 25
, MSC_GET_NAME.CUSTOMER_SITE(MGR.CUSTOMER_SITE_ID)
, 26
, MSC_GET_NAME.CUSTOMER_SITE(MGR.CUSTOMER_SITE_ID)
, 27
, MSC_GET_NAME.CUSTOMER_SITE(MGR.CUSTOMER_SITE_ID)
, 52
, MSC_GET_NAME.CUSTOMER_SITE(MGR.CUSTOMER_SITE_ID)
, 13
, MSC_GET_NAME.CUSTOMER_SITE(MGR.CUSTOMER_SITE_ID)
, 67
, MSC_GET_NAME.CUSTOMER_SITE(MGR.CUSTOMER_SITE_ID)
, 68
, MSC_GET_NAME.CUSTOMER_SITE(MGR.SHIP_TO_SITE_ID)
, MSC_GET_NAME.CUSTOMER_SITE(MGR2.CUSTOMER_SITE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 15
, MGR2.DEMAND_PRIORITY
, 16
, MGR2.DEMAND_PRIORITY
, 19
, MGR2.DEMAND_PRIORITY
, 23
, MGR2.DEMAND_PRIORITY
, 35
, MGR2.DEMAND_PRIORITY
, 41
, MGR2.DEMAND_PRIORITY
, 42
, MGR2.DEMAND_PRIORITY
, 23
, MGR2.DEMAND_PRIORITY
, MGR.DEMAND_PRIORITY)
, DECODE(MED.EXCEPTION_TYPE
, 15
, MGR2.USING_ASSEMBLY_DEMAND_DATE
, 16
, MGR2.USING_ASSEMBLY_DEMAND_DATE
, 19
, MGR2.DMD_SATISFIED_DATE
, 23
, MGR2.DMD_SATISFIED_DATE
, 35
, MGR2.DMD_SATISFIED_DATE
, 41
, MGR2.DMD_SATISFIED_DATE
, 42
, MGR2.DMD_SATISFIED_DATE
, 57
, MP.PLAN_START_DATE + NUMBER4
, 58
, MP.PLAN_START_DATE + NUMBER4
, 59
, MP.PLAN_START_DATE + NUMBER4
, 60
, MP.PLAN_START_DATE + NUMBER4
, MGR.DMD_SATISFIED_DATE)
, DECODE(MED.EXCEPTION_TYPE
, 33
, MSC_GET_NAME.ITEM_NAME(MED.NUMBER2
, NULL
, NULL
, NULL)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 32
, MSC_GET_NAME.ALTERNATE_RTG(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.NUMBER2)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 31
, MSC_GET_NAME.ALTERNATE_BOM(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.NUMBER2)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, MED.SUPPLIER_ID
, NULL)
, MR.SOURCE_ORGANIZATION_ID)
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, MED.SUPPLIER_SITE_ID
, NULL)
, MR.SOURCE_SR_INSTANCE_ID)
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, MSC_GET_NAME.ORG_CODE(MED.SUPPLIER_ID
, MED.SUPPLIER_SITE_ID)
, NULL)
, MSC_GET_NAME.ORG_CODE(MR.SOURCE_ORGANIZATION_ID
, MR.SOURCE_SR_INSTANCE_ID))
, MR.NEW_SCHEDULE_DATE
, MFP.END_PEGGING_ID
, DECODE ( MED.EXCEPTION_TYPE
, 52
, MGR.USING_REQUIREMENT_QUANTITY
, 67
, MGR.USING_REQUIREMENT_QUANTITY
, MGR2.USING_REQUIREMENT_QUANTITY )
, DECODE(MED.EXCEPTION_TYPE
, 10
, MED.DATE1
, 13
, MGR.OLD_DEMAND_DATE
, 14
, MGR.OLD_DEMAND_DATE
, MR.OLD_SCHEDULE_DATE)
, DECODE(MED.EXCEPTION_TYPE
, 52
, MSC_GET_NAME.LOOKUP_MEANING('MSC_DEMAND_ORIGINATION'
, MGR.ORIGINATION_TYPE)
, 67
, MSC_GET_NAME.LOOKUP_MEANING('MSC_DEMAND_ORIGINATION'
, MGR.ORIGINATION_TYPE)
, MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE'
, MR.ORDER_TYPE))
, DECODE ( MED.EXCEPTION_TYPE
, 52
, MGR.ORIGINATION_TYPE
, 67
, MGR.ORIGINATION_TYPE
, MR.ORDER_TYPE )
, MSC_GET_NAME.RESOURCE_BATCHABLE_FLAG(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, MSC_GET_NAME.RESOURCE_MIN_CAPACITY(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, MSC_GET_NAME.RESOURCE_MAX_CAPACITY(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.NUMBER3
, MED.NUMBER1)
, DECODE(MED.EXCEPTION_TYPE
, 45
, MED.NUMBER1
, 46
, MED.NUMBER1
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 45
, MED.NUMBER2
, 46
, MED.NUMBER2
, 36
, MED.NUMBER4
, 37
, MED.NUMBER4
, 40
, (MIF.UNIT_WEIGHT * MR.NEW_ORDER_QUANTITY)
, 55
, (MIF.UNIT_WEIGHT * MR.NEW_ORDER_QUANTITY)
, 56
, (MIF.UNIT_VOLUME * MR.NEW_ORDER_QUANTITY)
, 61
, (MIF.UNIT_VOLUME * MR.NEW_ORDER_QUANTITY)
, 53
, MED.NUMBER4
, 54
, MED.NUMBER4
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 24
, MGR.QUANTITY_BY_DUE_DATE
, 26
, MGR.QUANTITY_BY_DUE_DATE
, 68
, MED.NUMBER3
, TO_NUMBER(NULL))
, MSC_GET_NAME.RESOURCE_OVER_UTIL_COST(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, DECODE(MED.EXCEPTION_TYPE
, 48
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 48
, MED.NUMBER1
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 48
, ABS(MED.QUANTITY-MED.NUMBER1)
, TO_NUMBER(NULL))
, NVL(MED.ACTION_TAKEN
, 2)
, MED.ROWID
, ROUND(DECODE(MED.EXCEPTION_TYPE
, 15
, GREATEST( MR.NEW_SCHEDULE_DATE - MED.DATE2
, 0.01)
, 16
, GREATEST( MR.NEW_SCHEDULE_DATE - MED.DATE2
, 0.01)
, 24
, GREATEST( MGR.DMD_SATISFIED_DATE - MGR.USING_ASSEMBLY_DEMAND_DATE
, 0.01)
, 26
, GREATEST(MGR.DMD_SATISFIED_DATE - MGR.USING_ASSEMBLY_DEMAND_DATE
, 0.01)
, 25
, LEAST( MGR.DMD_SATISFIED_DATE - MGR.USING_ASSEMBLY_DEMAND_DATE
, -0.01)
, 27
, LEAST( MGR.DMD_SATISFIED_DATE - MGR.USING_ASSEMBLY_DEMAND_DATE
, -0.01)
, 62
, MED.QUANTITY
, 64
, MED.QUANTITY * -1
, 63
, MED.QUANTITY
, 65
, MED.QUANTITY * -1
, 66
, MED.QUANTITY
, 68
, GREATEST( MGR.DMD_SATISFIED_DATE - MGR.USING_ASSEMBLY_DEMAND_DATE
, 0.01)
, TO_NUMBER(NULL))
, 2)
, DECODE(MED.EXCEPTION_TYPE
, 49
, MED.NUMBER1
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 49
, MSC_GET_NAME.ITEM_NAME(MED.NUMBER1
, MED.NUMBER2
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 49
, MED.NUMBER2
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 49
, MSC_GET_NAME.ORG_CODE(MED.NUMBER2
, MED.SR_INSTANCE_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 49
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 31
, MSC_GET_NAME.PROCESS_PRIORITY(MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.ORGANIZATION_ID
, MR.INVENTORY_ITEM_ID
, MR.PROCESS_SEQ_ID)
, NULL)
, NVL(MED.NUMBER4
, 1)
, DECODE(NVL(MED.NUMBER4
, 1)
, 2
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ORIGINATION_CODE_TYPE'
, MED.NUMBER4)
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ORIGINATION_CODE_TYPE'
, 1))
, DECODE(MED.EXCEPTION_TYPE
, 15
, MED.NUMBER3
, 24
, MED.NUMBER3
, 67
, MED.QUANTITY
, 68
, MED.NUMBER3
, TO_NUMBER(NULL))
, MGR.DEMAND_CLASS
, DECODE ( MED.EXCEPTION_TYPE
, 36
, MED.NUMBER2
, 53
, MED.NUMBER2
, 58
, MED.NUMBER2
, 60
, MED.NUMBER2
, 63
, MED.NUMBER3
, 65
, MED.NUMBER3
, TO_NUMBER(NULL) )
, DECODE ( MED.EXCEPTION_TYPE
, 36
, MED.NUMBER3
, 53
, MED.NUMBER3
, 58
, MED.NUMBER3
, 60
, MED.NUMBER3
, 63
, MED.NUMBER4
, 65
, MED.NUMBER4
, TO_NUMBER(NULL) )
, DECODE ( MED.EXCEPTION_TYPE
, 62
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, MED.NUMBER2)
, 64
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, MED.NUMBER2)
, 63
, MSC_GET_NAME.LOOKUP_MEANING('RESOURCE_FIRM_TYPE'
, MED.NUMBER2)
, 65
, MSC_GET_NAME.LOOKUP_MEANING('RESOURCE_FIRM_TYPE'
, MED.NUMBER2)
, NULL )
, DECODE(MED.EXCEPTION_TYPE
, 57
, MP.PLAN_START_DATE + MED.NUMBER4
, 58
, MP.PLAN_START_DATE + MED.NUMBER4
, 59
, MP.PLAN_START_DATE + MED.NUMBER4
, 60
, MP.PLAN_START_DATE + MED.NUMBER4
, TO_DATE(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 57
, MED.QUANTITY
, 58
, MED.QUANTITY
, 59
, MED.QUANTITY
, 60
, MED.QUANTITY
, TO_NUMBER(NULL))
, MR.NEW_DOCK_DATE
, DECODE(MED.EXCEPTION_TYPE
, 36
, (MED.NUMBER4 -MED.QUANTITY)
, 37
, (MED.NUMBER4 -MED.QUANTITY)
, 53
, (MED.NUMBER4 -MED.QUANTITY)
, 54
, (MED.NUMBER4 -MED.QUANTITY)
, 40
, (MIF.UNIT_WEIGHT*MR.NEW_ORDER_QUANTITY -MED.QUANTITY)
, 55
, (MIF.UNIT_WEIGHT*MR.NEW_ORDER_QUANTITY -MED.QUANTITY)
, 56
, (MIF.UNIT_VOLUME*MR.NEW_ORDER_QUANTITY -MED.QUANTITY)
, 61
, (MIF.UNIT_VOLUME*MR.NEW_ORDER_QUANTITY -MED.QUANTITY)
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 67
, MED.QUANTITY
, TO_NUMBER(NULL))
, ROUND(DECODE(MED.EXCEPTION_TYPE
, 53
, MED.NUMBER5
, 57
, MED.NUMBER5
, 58
, MED.NUMBER5
, TO_NUMBER(NULL))
, 2)
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MIF
, MSC_ITEM_CATEGORIES MIC
, MSC_SUPPLIES MR
, MSC_DEMANDS MGR
, MSC_FULL_PEGGING MFP
, MSC_DEMANDS MGR2
, MSC_PLANS MP
WHERE MIC.ORGANIZATION_ID(+) = MIF.ORGANIZATION_ID
AND MIC.SR_INSTANCE_ID(+) = MIF.SR_INSTANCE_ID
AND MIC.INVENTORY_ITEM_ID(+) = MIF.INVENTORY_ITEM_ID
AND MIF.INVENTORY_ITEM_ID(+) = MED.INVENTORY_ITEM_ID
AND MIF.ORGANIZATION_ID(+) = MED.ORGANIZATION_ID
AND MIF.SR_INSTANCE_ID(+) = MED.SR_INSTANCE_ID
AND MIF.PLAN_ID(+) = MED.PLAN_ID
AND MR.SR_INSTANCE_ID (+) = MED.SR_INSTANCE_ID
AND MR.TRANSACTION_ID (+) = MED.NUMBER1
AND MR.PLAN_ID (+) = MED.PLAN_ID
AND MGR.SR_INSTANCE_ID (+) = MED.SR_INSTANCE_ID
AND MGR.DEMAND_ID (+) = MED.NUMBER1
AND MGR.PLAN_ID (+) = MED.PLAN_ID
AND MFP.PEGGING_ID (+) = MED.NUMBER2
AND MFP.PLAN_ID (+) = MED.PLAN_ID
AND MGR2.DEMAND_ID (+) = MFP.DEMAND_ID
AND MGR2.PLAN_ID (+) = MFP.PLAN_ID
AND MP.PLAN_ID = MED.PLAN_ID

Columns