View: MSC_ITEM_EXCEPTION_V

Product:	MSC - Advanced Supply Chain Planning
Description:	This view picks up all the item exceptions
Implementation/DBA Data:	APPS.MSC_ITEM_EXCEPTION_V

View Text

SELECT MIE.EXCEPTION_TYPE
, TO_CHAR(NULL)
, MIE.EXCEPTION_GROUP
, 2
, MP.COMPILE_DESIGNATOR
, MIE.PLAN_ID
, MIE.ORGANIZATION_ID
, MIE.SR_INSTANCE_ID
, MIE.PROJECT_ID
, MSC_GET_NAME.PROJECT(MIE.PROJECT_ID
, MIE.ORGANIZATION_ID
, MIE.PLAN_ID
, MIE.SR_INSTANCE_ID)
, MIE.TASK_ID
, MSC_GET_NAME.TASK(MIE.TASK_ID
, MIE.PROJECT_ID
, MIE.ORGANIZATION_ID
, MIE.PLAN_ID
, MIE.SR_INSTANCE_ID)
, MIE.VERSION
, TO_CHAR(MIE.VERSION)
, MIE.EXCEPTION_COUNT
, MIE.INVENTORY_ITEM_ID
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, MIE.PLANNING_GROUP
, MSI.ITEM_NAME
, NVL(MSI.PRODUCT_FAMILY_ID
, MSI.BASE_ITEM_ID)
, MSC_GET_NAME.ITEM_NAME(NVL(MSI.PRODUCT_FAMILY_ID
, MSI.BASE_ITEM_ID)
, MSI.ORGANIZATION_ID
, MSI.PLAN_ID
, MSI.SR_INSTANCE_ID)
, MTP.ORGANIZATION_CODE
, MIE.DEPARTMENT_ID
, MIE.RESOURCE_ID
, DEPT.DEPARTMENT_CODE
, DEPT.RESOURCE_CODE
, DEPT.DEPARTMENT_CLASS
, DEPT.RESOURCE_GROUP_NAME
, DEPT.RESOURCE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, DEPT.RESOURCE_TYPE)
, MIE.DISPLAY
, ROWIDTOCHAR(MIE.ROWID)
, MSI.PLANNER_CODE
, MSI.BUYER_ID
, MSI.BUYER_NAME
, MSI.ABC_CLASS
, MIE.SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(MIE.SUPPLIER_ID)
, MIE.SUPPLIER_SITE_ID
, NVL(MIE.NEW_EXCEPTION_COUNT
, MIE.EXCEPTION_COUNT)
FROM MSC_ITEM_EXCEPTIONS MIE
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
, MSC_TRADING_PARTNERS MTP
, MSC_PLANS MP
, MSC_DEPARTMENT_RESOURCES DEPT
WHERE MP.PLAN_ID = MIE.PLAN_ID
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 MTP.SR_TP_ID(+) = MIE.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID(+) = MIE.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE(+) = 3
AND DEPT.DEPARTMENT_ID(+) = MIE.DEPARTMENT_ID
AND DEPT.RESOURCE_ID(+) = MIE.RESOURCE_ID
AND DEPT.PLAN_ID(+) = MIE.PLAN_ID
AND DEPT.ORGANIZATION_ID(+) = MIE.ORGANIZATION_ID
AND DEPT.SR_INSTANCE_ID(+) = MIE.SR_INSTANCE_ID
AND NVL(MIE.DISPLAY
, 1)=1 UNION ALL SELECT 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))
, TO_CHAR(NULL)
, 10
, 2
, P.COMPILE_DESIGNATOR
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.PROJECT_ID
, MSC_GET_NAME.PROJECT(S.PROJECT_ID
, S.ORGANIZATION_ID
, S.PLAN_ID
, S.SR_INSTANCE_ID)
, S.TASK_ID
, MSC_GET_NAME.TASK(S.TASK_ID
, S.PROJECT_ID
, S.ORGANIZATION_ID
, S.PLAN_ID
, S.SR_INSTANCE_ID)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, COUNT(1)
, I.INVENTORY_ITEM_ID
, IC.CATEGORY_SET_ID
, IC.SR_CATEGORY_ID
, IC.CATEGORY_NAME
, S.PLANNING_GROUP
, I.ITEM_NAME
, NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, MSC_GET_NAME.ITEM_NAME(NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, I.ORGANIZATION_ID
, I.PLAN_ID
, I.SR_INSTANCE_ID)
, I.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, 1
, NULL
, I.PLANNER_CODE
, I.BUYER_ID
, I.BUYER_NAME
, I.ABC_CLASS
, S.SOURCE_SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(S.SOURCE_SUPPLIER_ID)
, S.SOURCE_SUPPLIER_SITE_ID
, 1
FROM MSC_WORKBENCH_DISPLAY_OPTIONS W
, MSC_ITEM_CATEGORIES IC
, MSC_PLANS P
, MSC_SUPPLIES S
, MSC_SYSTEM_ITEMS I
WHERE 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 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 W.USER_ID = FND_GLOBAL.USER_ID
AND S.PLAN_ID = P.PLAN_ID
AND S.NEW_ORDER_PLACEMENT_DATE <= (P.PLAN_START_DATE + NVL(W.RECOMMENDATION_DAYS
, 0))
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)))) GROUP BY 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))
, P.COMPILE_DESIGNATOR
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.PROJECT_ID
, S.TASK_ID
, I.INVENTORY_ITEM_ID
, IC.CATEGORY_SET_ID
, IC.SR_CATEGORY_ID
, IC.CATEGORY_NAME
, S.PLANNING_GROUP
, I.ITEM_NAME
, NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, MSC_GET_NAME.ITEM_NAME(NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, I.ORGANIZATION_ID
, I.PLAN_ID
, I.SR_INSTANCE_ID)
, I.ORGANIZATION_CODE
, I.PLANNER_CODE
, I.BUYER_ID
, I.BUYER_NAME
, I.ABC_CLASS
, S.SOURCE_SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(S.SOURCE_SUPPLIER_ID)
, S.SOURCE_SUPPLIER_SITE_ID

Columns