View: MSC_ITEM_EXCEPTION_V2

Product:	MSC - Advanced Supply Chain Planning
Description:	this view picks the item exceptions at the plan level
Implementation/DBA Data:	APPS.MSC_ITEM_EXCEPTION_V2

View Text

SELECT MIE.EXCEPTION_TYPE
, MIE.EXCEPTION_GROUP
, 2
, MIE.PLAN_ID
, MIE.ORGANIZATION_ID
, MIE.SR_INSTANCE_ID
, MIE.PROJECT_ID
, MIE.TASK_ID
, MIE.VERSION
, MIE.EXCEPTION_COUNT
, MIE.INVENTORY_ITEM_ID
, MIE.PLANNING_GROUP
, MIE.DEPARTMENT_ID
, MIE.RESOURCE_ID
, MIE.DISPLAY
, ROWIDTOCHAR(MIE.ROWID)
, MIE.SUPPLIER_ID
, MIE.SUPPLIER_SITE_ID
, TO_DATE(NULL)
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, NVL(MSI.PRODUCT_FAMILY_ID
, MSI.BASE_ITEM_ID)
, NVL(MIE.NEW_EXCEPTION_COUNT
, MIE.EXCEPTION_COUNT)
FROM MSC_ITEM_EXCEPTIONS MIE
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
WHERE NVL(MIE.DISPLAY
, 1)=1
AND MIE.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MIE.SR_INSTANCE_ID = MIC.SR_INSTANCE_ID
AND MIE.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MIE.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIE.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MIE.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIE.PLAN_ID = MSI.PLAN_ID
AND MIE.INVENTORY_ITEM_ID <> -1 UNION ALL SELECT MIE.EXCEPTION_TYPE
, MIE.EXCEPTION_GROUP
, 2
, MIE.PLAN_ID
, MIE.ORGANIZATION_ID
, MIE.SR_INSTANCE_ID
, MIE.PROJECT_ID
, MIE.TASK_ID
, MIE.VERSION
, MIE.EXCEPTION_COUNT
, MIE.INVENTORY_ITEM_ID
, MIE.PLANNING_GROUP
, MIE.DEPARTMENT_ID
, MIE.RESOURCE_ID
, MIE.DISPLAY
, ROWIDTOCHAR(MIE.ROWID)
, MIE.SUPPLIER_ID
, MIE.SUPPLIER_SITE_ID
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NVL(MIE.NEW_EXCEPTION_COUNT
, MIE.EXCEPTION_COUNT)
FROM MSC_ITEM_EXCEPTIONS MIE
WHERE NVL(MIE.DISPLAY
, 1)=1
AND MIE.INVENTORY_ITEM_ID = -1 UNION ALL SELECT /*+ FIRST_ROWS */ DECODE(S.ORDER_TYPE
, 13
, 104
, 5
, DECODE(I.BUILD_IN_WIP_FLAG
, 1
, DECODE(S.SOURCE_ORGANIZATION_ID
, S.ORGANIZATION_ID
, DECODE( S.SOURCE_SR_INSTANCE_ID
, S.SR_INSTANCE_ID
, DECODE( S.CFM_ROUTING_FLAG
, 1
, 103
, 102)
, DECODE( S.SOURCE_ORGANIZATION_ID
, NULL
, DECODE(I.PLANNING_MAKE_BUY_CODE
, 2
, 101
, DECODE(S.CFM_ROUTING_FLAG
, 1
, 103
, 102))
, 101)
, 101)
, 101)
, 101))
, 10
, 2
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.PROJECT_ID
, S.TASK_ID
, TO_NUMBER(NULL)
, 1
, S.INVENTORY_ITEM_ID
, S.PLANNING_GROUP
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 1
, NULL
, S.SOURCE_SUPPLIER_ID
, S.SOURCE_SUPPLIER_SITE_ID
, S.NEW_ORDER_PLACEMENT_DATE
, IC.CATEGORY_SET_ID
, IC.SR_CATEGORY_ID
, IC.CATEGORY_NAME
, NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, 1
FROM MSC_SYSTEM_ITEMS I
, MSC_SUPPLIES S
, MSC_ITEM_CATEGORIES IC
WHERE I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND I.PLAN_ID = S.PLAN_ID
AND I.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = IC.INVENTORY_ITEM_ID
AND I.SR_INSTANCE_ID = IC.SR_INSTANCE_ID
AND I.ORGANIZATION_ID = IC.ORGANIZATION_ID
AND ((ORDER_TYPE = 13) OR (ORDER_TYPE = 5
AND NVL(S.IMPLEMENTED_QUANTITY
, 0)+NVL(S.QUANTITY_IN_PROCESS
, 0) < NVL(S.FIRM_QUANTITY
, S.NEW_ORDER_QUANTITY)
AND (((S.SOURCE_ORGANIZATION_ID <> S.ORGANIZATION_ID OR S.SOURCE_SR_INSTANCE_ID <> S.SR_INSTANCE_ID OR S.SOURCE_SUPPLIER_ID IS NOT NULL)
AND I.PURCHASING_ENABLED_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID IS NULL
AND S.SOURCE_SUPPLIER_ID IS NULL
AND I.PLANNING_MAKE_BUY_CODE = 2
AND I.PURCHASING_ENABLED_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID = S.ORGANIZATION_ID
AND S.SOURCE_SR_INSTANCE_ID =S.SR_INSTANCE_ID
AND I.BUILD_IN_WIP_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID IS NULL
AND S.SOURCE_SUPPLIER_ID IS NULL
AND I.PLANNING_MAKE_BUY_CODE = 1
AND I.BUILD_IN_WIP_FLAG = 1))))

Columns