View: MSC_EXC_DETAIL_ITEM_V

Product:	MSC - Advanced Supply Chain Planning
Description:
Implementation/DBA Data:	APPS.MSC_EXC_DETAIL_ITEM_V

View Text

SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, DECODE(MED.EXCEPTION_TYPE
, 17
, MED.NUMBER1
, 18
, MED.NUMBER1
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 17
, MSC_GET_NAME.PROJECT(MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 18
, MSC_GET_NAME.PROJECT(MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 17
, MED.NUMBER2
, 18
, MED.NUMBER2
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 17
, MSC_GET_NAME.TASK(MED.NUMBER2
, MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 18
, MSC_GET_NAME.TASK(MED.NUMBER2
, MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, NULL)
, TO_NUMBER(NULL)
, NULL
, MED.SUPPLIER_ID
, DECODE(MED.EXCEPTION_TYPE
, 28
, MSC_GET_NAME.SUPPLIER( MED.SUPPLIER_ID))
, DECODE(MED.EXCEPTION_TYPE
, 28
, MSC_GET_NAME.SUPPLIER_SITE( MED.SUPPLIER_SITE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 17
, MED.QUANTITY
, 18
, MED.QUANTITY
, 28
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 17
, MED.QUANTITY
, 18
, MED.QUANTITY
, 28
, TO_NUMBER(NULL)
, MED.QUANTITY)
, MED.DATE1
, MED.DATE2
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, MSI.DESCRIPTION
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
WHERE MSI.INVENTORY_ITEM_ID=MED.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MSI.PLAN_ID=MED.PLAN_ID
AND MTP.PARTNER_TYPE=3
AND MTP.SR_TP_ID=MED.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MED.EXCEPTION_TYPE IN (1
, 2
, 3
, 4
, 5
, 11
, 17
, 18
, 20
, 28) UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, MS.PROJECT_ID
, MSC_GET_NAME.PROJECT(MS.PROJECT_ID
, MS.ORGANIZATION_ID
, MS.PLAN_ID
, MS.SR_INSTANCE_ID)
, MS.TASK_ID
, MSC_GET_NAME.TASK(MS.TASK_ID
, MS.PROJECT_ID
, MS.ORGANIZATION_ID
, MS.PLAN_ID
, MS.SR_INSTANCE_ID)
, TO_NUMBER(NULL)
, NULL
, NVL(MED.SUPPLIER_ID
, MS.SUPPLIER_ID)
, MSC_GET_NAME.SUPPLIER(NVL(MED.SUPPLIER_ID
, MS.SUPPLIER_ID))
, DECODE(MED.EXCEPTION_TYPE
, 37
, MSC_GET_NAME.SUPPLIER_SITE( MED.SUPPLIER_SITE_ID)
, 44
, MSC_GET_NAME.SUPPLIER_SITE( MED.SUPPLIER_SITE_ID)
, NULL)
, MED.QUANTITY
, MS.NEW_ORDER_QUANTITY
, MED.DATE1
, MED.DATE2
, MS.NEW_SCHEDULE_DATE
, MS.OLD_SCHEDULE_DATE
, TO_DATE(NULL)
, MS.ORDER_TYPE
, ML.MEANING
, NVL(MS.ORDER_NUMBER
, MS.TRANSACTION_ID)
, MS.PURCH_LINE_NUM
, TO_NUMBER( DECODE(MED.EXCEPTION_TYPE
, 9
, MS.SCHEDULE_COMPRESS_DAYS
, 10
, ROUND(ABS(SYSDATE - MS.OLD_SCHEDULE_DATE)
, 2)
, 6
, MS.RESCHEDULE_DAYS
, 7
, MS.RESCHEDULE_DAYS
, 0))
, DECODE(MED.EXCEPTION_TYPE
, 12
, MS.LOT_NUMBER
, 31
, MSC_GET_NAME.ALTERNATE_BOM(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.NUMBER2)
, 32
, MSC_GET_NAME.ALTERNATE_RTG(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.NUMBER2)
, 33
, MSC_GET_NAME.ITEM_NAME(MED.NUMBER2
, NULL
, NULL
, NULL)
, 34
, MSC_GET_NAME.RESOURCE_CODE(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 43
, MSC_GET_NAME.ORG_CODE(MED.NUMBER2
, MED.NUMBER3)
, 44
, MSC_GET_NAME.SUPPLIER(MED.SUPPLIER_ID))
, TO_NUMBER(NULL)
, NULL
, NULL
, MSI.DESCRIPTION
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSC_SUPPLIES MS
, MFG_LOOKUPS ML
WHERE MSI.INVENTORY_ITEM_ID=MED.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MSI.PLAN_ID=MED.PLAN_ID
AND MTP.PARTNER_TYPE=3
AND MTP.SR_TP_ID=MED.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MS.PLAN_ID=MED.PLAN_ID
AND MS.TRANSACTION_ID=MED.TRANSACTION_ID
AND MS.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND ML.LOOKUP_CODE = MS.ORDER_TYPE
AND ML.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND MED.EXCEPTION_TYPE IN (6
, 7
, 8
, 9
, 10
, 12
, 31
, 32
, 33
, 34
, 37
, 43
, 44) UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, MD.PROJECT_ID
, MSC_GET_NAME.PROJECT(MD.PROJECT_ID
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, MD.TASK_ID
, MSC_GET_NAME.TASK(MD.TASK_ID
, MD.PROJECT_ID
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, MD.CUSTOMER_ID
, MSC_GET_NAME.CUSTOMER(MD.CUSTOMER_ID)
, MED.SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(MED.SUPPLIER_ID)
, MSC_GET_NAME.SUPPLIER_SITE(MED.SUPPLIER_SITE_ID)
, DECODE(MED.EXCEPTION_TYPE
, 41
, MED.QUANTITY
, 42
, MED.QUANTITY
, TO_NUMBER(NULL))
, MD.USING_REQUIREMENT_QUANTITY
, MED.DATE1
, MED.DATE2
, MD.USING_ASSEMBLY_DEMAND_DATE
, MD.OLD_DEMAND_DATE
, MD.DMD_SATISFIED_DATE
, MD.ORIGINATION_TYPE
, ML.MEANING
, NVL(MD.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MD.SCHEDULE_DESIGNATOR_ID))
, MD.SALES_ORDER_LINE_ID
, ROUND(ABS(DECODE(MED.EXCEPTION_TYPE
, 13
, MD.OLD_DEMAND_DATE
, 14
, MD.OLD_DEMAND_DATE
, MD.USING_ASSEMBLY_DEMAND_DATE) -DECODE(MED.EXCEPTION_TYPE
, 13
, SYSDATE
, 14
, SYSDATE
, 15
, MED.DATE1
, 16
, MED.DATE1
, MD.DMD_SATISFIED_DATE))
, 2)
, NULL
, MD.DEMAND_PRIORITY
, NULL
, NULL
, MSI.DESCRIPTION
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSC_DEMANDS MD
, MFG_LOOKUPS ML
WHERE MSI.INVENTORY_ITEM_ID=MED.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MSI.PLAN_ID=MED.PLAN_ID
AND MTP.PARTNER_TYPE=3
AND MTP.SR_TP_ID=MED.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MD.PLAN_ID=MED.PLAN_ID
AND MD.DEMAND_ID=MED.DEMAND_ID
AND MD.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND ML.LOOKUP_CODE = MD.ORIGINATION_TYPE
AND ML.LOOKUP_TYPE = 'MSC_DEMAND_ORIGINATION'
AND MED.EXCEPTION_TYPE IN (13
, 14
, 15
, 16
, 24
, 25
, 26
, 27
, 41
, 42) UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, NVL(MS.PROJECT_ID
, MED.NUMBER4)
, MSC_GET_NAME.PROJECT(NVL(MS.PROJECT_ID
, MED.NUMBER4)
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, NVL(MS.TASK_ID
, MED.NUMBER1)
, MSC_GET_NAME.TASK(NVL(MS.TASK_ID
, MED.NUMBER1)
, NVL(MS.PROJECT_ID
, MED.NUMBER4)
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, MED.QUANTITY
, MD.USING_REQUIREMENT_QUANTITY
, MED.DATE1
, MED.DATE2
, MD.USING_ASSEMBLY_DEMAND_DATE
, MD.OLD_DEMAND_DATE
, MD.DMD_SATISFIED_DATE
, MD.ORIGINATION_TYPE
, NULL
, NVL(MD.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MD.SCHEDULE_DESIGNATOR_ID))
, MD.SALES_ORDER_LINE_ID
, ROUND(ABS( MD.USING_ASSEMBLY_DEMAND_DATE - MD.DMD_SATISFIED_DATE)
, 2)
, NULL
, MD.DEMAND_PRIORITY
, MSC_GET_NAME.PROJECT(NVL(MD.PROJECT_ID
, MED.NUMBER3)
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(NVL(MD.TASK_ID
, MED.NUMBER2)
, NVL(MD.PROJECT_ID
, MED.NUMBER3)
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, MSI.DESCRIPTION
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSC_DEMANDS MD
, MSC_SUPPLIES MS
WHERE MSI.INVENTORY_ITEM_ID=MED.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MSI.PLAN_ID=MED.PLAN_ID
AND MTP.PARTNER_TYPE=3
AND MTP.SR_TP_ID=MED.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MD.PLAN_ID(+)=MED.PLAN_ID
AND MD.DEMAND_ID(+)=MED.DEMAND_ID
AND MD.SR_INSTANCE_ID(+)=MED.SR_INSTANCE_ID
AND MS.PLAN_ID(+)=MED.PLAN_ID
AND MS.TRANSACTION_ID(+)=MED.TRANSACTION_ID
AND MS.SR_INSTANCE_ID(+)=MED.SR_INSTANCE_ID
AND MED.EXCEPTION_TYPE =19

Columns