View: MSC_PDR_LATE_ORDER_EXC_V

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

View Text

SELECT PP.EXCEPTION_TYPE_TEXT
, PP.ORGANIZATION_CODE
, PP.ITEM_SEGMENTS
, PP.RESOURCE_CODE
, PP.ORDER_NUMBER
, PP.DMD_SATISFIED_DATE
, PP.DUE_DATE
, DECODE(PP.EXCEPTION_TYPE
, 24
, ( TRUNC(PP.DMD_SATISFIED_DATE) -TRUNC( PP.DUE_DATE))
, 26
, (TRUNC(PP.DMD_SATISFIED_DATE) -TRUNC( PP.DUE_DATE))
, TO_DATE(NULL))
, PP.QUANTITY
, PP.QUANTITY_BY_DUE_DATE
, PP.PROJECT_NUMBER
, PP.TASK_NUMBER
, PP.SUPPLIER_NAME || DECODE(PP.SUPPLIER_NAME
, NULL
, NULL
, '
, ') || PP.SUPPLIER_SITE
, PP.CUSTOMER_NAME || DECODE(PP.CUSTOMER_NAME
, NULL
, NULL
, '
, ') || PP.CUSTOMER_SITE
, PP.ORDER_PRIORITY
, 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
, MIC.SR_CATEGORY_ID
, NULL
, PP.INVENTORY_ITEM_ID
, PP.PLANNER_CODE
, PP.BUYER_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, PP.RESOURCE_ID
, NULL
, SUPPLY_TYPE
FROM MSC_EXCEPTION_DETAILS_V PP
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
WHERE EXCEPTION_TYPE IN (10
, 13
, 14
, 15
, 16
, 24
, 26
, 30)
AND MIC.CATEGORY_SET_ID = PP.CATEGORY_SET_ID
AND MIC.SR_INSTANCE_ID = PP.SR_INSTANCE_ID
AND MIC.ORGANIZATION_ID = PP.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = PP.INVENTORY_ITEM_ID
AND PP.PLAN_ID = MSI.PLAN_ID
AND PP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND PP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND PP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
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 (TRUNC(PP.DUE_DATE) >= (SELECT TRUNC(NVL(MPP.REPORT_PERIOD_FROM
, TRUNC(PP.DUE_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.DUE_DATE) <= (SELECT TRUNC(NVL(MPP.REPORT_PERIOD_TO
, TRUNC(PP.DUE_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