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_MAT_RES_EXC_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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