View: MSC_PDR_MAT_RES_EXC_V

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

View Text

SELECT PP.EXCEPTION_TYPE_TEXT
, PP.ORGANIZATION_CODE
, PP.ITEM_SEGMENTS
, PP.RESOURCE_CODE
, PP.RESOURCE_TYPE_CODE
, PP.FROM_DATE
, PP.TO_DATE
, PP.QUANTITY
, DECODE(PP.EXCEPTION_TYPE
, 36
, TO_NUMBER(NULL)
, 37
, TO_NUMBER(NULL)
, PP.UTILIZATION_RATE)
, PP.ORDER_NUMBER
, PP.PROJECT_NUMBER
, PP.TASK_NUMBER
, PP.SUPPLIER_NAME || DECODE(PP.SUPPLIER_NAME
, NULL
, NULL
, '
, ') || SUPPLIER_SITE
, PP.DEPARTMENT_LINE_CODE
, PP.PLANNER_CODE
, PP.BUYER_NAME
, TO_CHAR(NULL)
, TRUNC(SYSDATE)
, PP.PLAN_ID
, PP.ORGANIZATION_ID
, PP.SR_INSTANCE_ID
, PP.CATEGORY_SET_ID
, NULL
, NULL
, DECODE(PP.INVENTORY_ITEM_ID
, -1
, NULL
, PP.INVENTORY_ITEM_ID)
, NULL
, PP.BUYER_NAME
, NULL
, NULL
, NULL
, NULL
, PP.DEPARTMENT_ID
, DECODE(PP.RESOURCE_ID
, -1
, NULL
, PP.RESOURCE_ID)
, NULL
, DECODE(PP.EXCEPTION_TYPE
, 36
, PP.UTILIZATION_RATE
, 37
, PP.UTILIZATION_RATE
, TO_NUMBER(NULL))
, PP.ORDER_TYPE
FROM MSC_EXCEPTION_DETAILS_V PP
WHERE PP.EXCEPTION_TYPE IN (2
, 3
, 17
, 18
, 19
, 20
, 21
, 22
, 36
, 37)
AND PP.PLAN_ID IN (SELECT PLAN_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID)
AND ( (NVL(PP.ORGANIZATION_ID
, -1)
, NVL(PP.SR_INSTANCE_ID
, -1)) IN (SELECT NVL(ORGANIZATION_ID
, NVL(PP.ORGANIZATION_ID
, -1))
, NVL(SR_INSTANCE_ID
, NVL(PP.SR_INSTANCE_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(ORGANIZATION_ID
, -2) <> -1 ) )
AND (NVL(PP.CATEGORY_SET_ID
, -1) IN (SELECT NVL(CATEGORY_SET_ID
, NVL(PP.CATEGORY_SET_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID )) /*
AND NVL(CATEGORY_SET_ID
, -2) <> -1))
AND (NVL(MIC.SR_CATEGORY_ID
, -1) IN (SELECT NVL(PRODUCT_CATEGORY_ID
, NVL(MIC.SR_CATEGORY_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(PRODUCT_CATEGORY_ID
, -2) <> -1))
AND (NVL(MSI.ABC_CLASS
, -1) IN (SELECT NVL(ABC_CLASS_ID
, NVL(MSI.ABC_CLASS
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(ABC_CLASS_ID
, -2) <> -1)) */AND (NVL(PP.INVENTORY_ITEM_ID
, -1) IN (SELECT NVL(INVENTORY_ITEM_ID
, NVL(PP.INVENTORY_ITEM_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(INVENTORY_ITEM_ID
, -2) <> -1))
AND (NVL(PP.PLANNER_CODE
, '-1') IN (SELECT NVL(PLANNER_CODE_ID
, NVL(PP.PLANNER_CODE
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(PLANNER_CODE_ID
, '-2') <> '-1'))
AND (NVL(PP.BUYER_NAME
, '-1') IN (SELECT NVL(BUYER_NAME_ID
, NVL(PP.BUYER_NAME
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(BUYER_NAME_ID
, '-2') <> '-1'))
AND (NVL(PP.SUPPLIER_ID
, -1) IN (SELECT NVL(SUPPLIER_ID
, NVL(PP.SUPPLIER_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(SUPPLIER_ID
, -2) <> -1))
AND (NVL(PP.PLANNING_GROUP
, '-1') IN (SELECT NVL(PLANNING_GROUP_ID
, NVL(PP.PLANNING_GROUP
, '-1'))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(PLANNING_GROUP_ID
, '-2') <> '-1'))
AND (NVL(PP.PROJECT_ID
, -1) IN (SELECT NVL( PROJECT_ID
, NVL(PP.PROJECT_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(PROJECT_ID
, -2) <> -1))
AND (NVL(PP.DEPARTMENT_ID
, -1) IN (SELECT NVL(DEPT_LINE_ID
, NVL(PP.DEPARTMENT_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(DEPT_LINE_ID
, -2) <> -1))
AND (NVL(PP.RESOURCE_ID
, -1) IN (SELECT NVL(RESOURCE_ID
, NVL(PP.RESOURCE_ID
, -1))
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND NVL(RESOURCE_ID
, -2) <> -1))
AND (TRUNC(PP.FROM_DATE) >= (SELECT TRUNC(NVL(MPP.REPORT_PERIOD_FROM
, TRUNC(PP.FROM_DATE)))
FROM MSC_PDR_PARAMETERS MPP
, MSC_PLANS MP
WHERE MPP.USER_ID = FND_GLOBAL.USER_ID
AND MP.PLAN_ID = (SELECT DISTINCT PLAN_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND REPORT_DATE_REC = 2)
AND MPP.REPORT_DATE_REC = 1))
AND (TRUNC(PP.FROM_DATE) <= (SELECT TRUNC(NVL(MPP.REPORT_PERIOD_TO
, TRUNC(PP.FROM_DATE)))
FROM MSC_PDR_PARAMETERS MPP
, MSC_PLANS MP
WHERE MPP.USER_ID = FND_GLOBAL.USER_ID
AND MP.PLAN_ID = (SELECT DISTINCT PLAN_ID
FROM MSC_PDR_PARAMETERS
WHERE USER_ID = FND_GLOBAL.USER_ID
AND REPORT_DATE_REC = 2)
AND MPP.REPORT_DATE_REC = 1 ))

Columns