eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSC_PDR_LATE_ORDER_EXC_V

Source

View Text - Preformatted


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 ))

View Text - HTML Formatted

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 ))