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_PQ_RESULTS_V

Source

View Text - Preformatted


SELECT distinct mpf.query_id, mpf.query_type, mpr.plan_id, mpr.sr_instance_id, mpr.organization_id, mpr.exception_type, mpr.source_type, to_char(null), to_number(null), mpr.inventory_item_id, decode(mpf.query_type, 1, msc_get_name.item_name(mpr.inventory_item_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id), 5, msc_get_name.item_name(mpr.inventory_item_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id), to_char(null)) item_name, decode(mpf.query_type, 1, msc_get_name.item_desc(mpr.inventory_item_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id), 5, msc_get_name.item_desc(mpr.inventory_item_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id), to_char(null) ) item_desc, mpr.department_id, decode(mpf.query_type, 2, msc_get_name.department_code(decode(mpr.resource_id, -1, 1, 2), mpr.department_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id), to_char(null)) department_code, mpr.resource_id, decode(mpf.query_type, 2, msc_get_name.resource_code_all(mpr.resource_id, mpr.department_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id, mpr.resource_type), to_char(null)) resource_code, decode(mpf.query_type, 2, msc_get_name.resource_desc_all(mpr.resource_id, mpr.department_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id, mpr.resource_type), to_char(null)) resource_desc, mpr.resource_type, mpr.supplier_id, mpr.supplier_site_id, decode(mpf.query_type, 5, msc_get_name.supplier(mpr.supplier_id), to_char(null)) supplier_name, decode(mpf.query_type, 5, msc_get_name.supplier_site(mpr.supplier_site_id), to_char(null)) supplier_site_name, msc_get_name.org_code(mpr.organization_id, mpr.sr_instance_id) org_code, decode(mpf.query_type, 6,mpr.from_org_id, to_number(null)) , decode(mpf.query_type, 6,mpr.from_org_instance_id, to_number(null)) , decode(mpf.query_type, 6,msc_get_name.org_code(mpr.from_org_id, mpr.from_org_instance_id), to_char(null)) from_org_code ,decode(mpf.query_type, 6,mpr.to_org_id, to_number(null)) , decode(mpf.query_type, 6,mpr.to_org_instance_id, to_number(null)) , decode(mpf.query_type, 6,msc_get_name.org_code(mpr.to_org_id, mpr.to_org_instance_id), to_char(null)) to_org_code, decode(mpf.query_type, 6,mpr.shipment_id, to_number(null)), to_number(null) sequence_id, mpr.created_by, mpr.creation_date, mpr.last_update_date, mpr.last_updated_by , mpr.last_update_login, to_char(null), to_char(null), to_char(null), to_char(null), to_char(null) , to_char(null), to_char(null), to_char(null) , to_char(null), to_char(null), to_char(null) , to_char(null), to_char(null), to_char(null) from msc_pq_results mpr, msc_personal_queries mpf WHERE mpr.query_id = mpf.query_id and summary_data = 1 and mpf.query_type not in (4) union all SELECT distinct mpf.query_id, mpf.query_type, mpr.plan_id, mpr.sr_instance_id, mpr.organization_id, mpr.exception_type, mpr.source_type, decode(mpr.source_type, 1, decode(mp.curr_plan_type,5, decode(exception_type, 2, msc_get_name.lookup_meaning('MSC_DRP_CHANGED_EXCEPTION_NAME', 2), 37, msc_get_name.lookup_meaning('MSC_DRP_CHANGED_EXCEPTION_NAME', 37), msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE',mpr.exception_type)), msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE',mpr.exception_type)), msc_get_name.cp_exception_type_text(mpr.exception_type)) exception_type_text, mpr.exception_count exception_count, to_number(null), to_char(null), to_char(null), to_number(null), to_char(null), to_number(null), to_char(null), to_char(null), to_number(null), to_number(null), to_number(null), to_char(null), to_char(null), to_char(null), to_number(null), to_number(null), to_char(null), to_number(null), to_number(null), to_char(null), to_number(null), mpt.sequence_id, mpr.created_by, mpr.creation_date, mpr.last_update_date, mpr.last_updated_by , mpr.last_update_login, mpr.char1, mpr.char2, mpr.char3, mpr.char4, mpr.char5, groupby_org, groupby_item, groupby_supply_item, groupby_category, groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier from msc_pq_results mpr, msc_pq_types mpt, msc_personal_queries mpf, msc_plans mp WHERE mpr.query_id = mpf.query_id and mpr.query_id = mpt.query_id and summary_data = 1 and mpf.query_type=4 and mpr.exception_type=mpt.object_type and mpr.source_type=mpt.source_type and mpr.sequence_id = mpt.sequence_id and mpr.plan_id = mp.plan_id order by sequence_id

View Text - HTML Formatted

SELECT DISTINCT MPF.QUERY_ID
, MPF.QUERY_TYPE
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID
, MPR.ORGANIZATION_ID
, MPR.EXCEPTION_TYPE
, MPR.SOURCE_TYPE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, MPR.INVENTORY_ITEM_ID
, DECODE(MPF.QUERY_TYPE
, 1
, MSC_GET_NAME.ITEM_NAME(MPR.INVENTORY_ITEM_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID)
, 5
, MSC_GET_NAME.ITEM_NAME(MPR.INVENTORY_ITEM_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID)
, TO_CHAR(NULL)) ITEM_NAME
, DECODE(MPF.QUERY_TYPE
, 1
, MSC_GET_NAME.ITEM_DESC(MPR.INVENTORY_ITEM_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID)
, 5
, MSC_GET_NAME.ITEM_DESC(MPR.INVENTORY_ITEM_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID)
, TO_CHAR(NULL) ) ITEM_DESC
, MPR.DEPARTMENT_ID
, DECODE(MPF.QUERY_TYPE
, 2
, MSC_GET_NAME.DEPARTMENT_CODE(DECODE(MPR.RESOURCE_ID
, -1
, 1
, 2)
, MPR.DEPARTMENT_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID)
, TO_CHAR(NULL)) DEPARTMENT_CODE
, MPR.RESOURCE_ID
, DECODE(MPF.QUERY_TYPE
, 2
, MSC_GET_NAME.RESOURCE_CODE_ALL(MPR.RESOURCE_ID
, MPR.DEPARTMENT_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID
, MPR.RESOURCE_TYPE)
, TO_CHAR(NULL)) RESOURCE_CODE
, DECODE(MPF.QUERY_TYPE
, 2
, MSC_GET_NAME.RESOURCE_DESC_ALL(MPR.RESOURCE_ID
, MPR.DEPARTMENT_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID
, MPR.RESOURCE_TYPE)
, TO_CHAR(NULL)) RESOURCE_DESC
, MPR.RESOURCE_TYPE
, MPR.SUPPLIER_ID
, MPR.SUPPLIER_SITE_ID
, DECODE(MPF.QUERY_TYPE
, 5
, MSC_GET_NAME.SUPPLIER(MPR.SUPPLIER_ID)
, TO_CHAR(NULL)) SUPPLIER_NAME
, DECODE(MPF.QUERY_TYPE
, 5
, MSC_GET_NAME.SUPPLIER_SITE(MPR.SUPPLIER_SITE_ID)
, TO_CHAR(NULL)) SUPPLIER_SITE_NAME
, MSC_GET_NAME.ORG_CODE(MPR.ORGANIZATION_ID
, MPR.SR_INSTANCE_ID) ORG_CODE
, DECODE(MPF.QUERY_TYPE
, 6
, MPR.FROM_ORG_ID
, TO_NUMBER(NULL))
, DECODE(MPF.QUERY_TYPE
, 6
, MPR.FROM_ORG_INSTANCE_ID
, TO_NUMBER(NULL))
, DECODE(MPF.QUERY_TYPE
, 6
, MSC_GET_NAME.ORG_CODE(MPR.FROM_ORG_ID
, MPR.FROM_ORG_INSTANCE_ID)
, TO_CHAR(NULL)) FROM_ORG_CODE
, DECODE(MPF.QUERY_TYPE
, 6
, MPR.TO_ORG_ID
, TO_NUMBER(NULL))
, DECODE(MPF.QUERY_TYPE
, 6
, MPR.TO_ORG_INSTANCE_ID
, TO_NUMBER(NULL))
, DECODE(MPF.QUERY_TYPE
, 6
, MSC_GET_NAME.ORG_CODE(MPR.TO_ORG_ID
, MPR.TO_ORG_INSTANCE_ID)
, TO_CHAR(NULL)) TO_ORG_CODE
, DECODE(MPF.QUERY_TYPE
, 6
, MPR.SHIPMENT_ID
, TO_NUMBER(NULL))
, TO_NUMBER(NULL) SEQUENCE_ID
, MPR.CREATED_BY
, MPR.CREATION_DATE
, MPR.LAST_UPDATE_DATE
, MPR.LAST_UPDATED_BY
, MPR.LAST_UPDATE_LOGIN
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
FROM MSC_PQ_RESULTS MPR
, MSC_PERSONAL_QUERIES MPF
WHERE MPR.QUERY_ID = MPF.QUERY_ID
AND SUMMARY_DATA = 1
AND MPF.QUERY_TYPE NOT IN (4) UNION ALL SELECT DISTINCT MPF.QUERY_ID
, MPF.QUERY_TYPE
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID
, MPR.ORGANIZATION_ID
, MPR.EXCEPTION_TYPE
, MPR.SOURCE_TYPE
, DECODE(MPR.SOURCE_TYPE
, 1
, DECODE(MP.CURR_PLAN_TYPE
, 5
, DECODE(EXCEPTION_TYPE
, 2
, MSC_GET_NAME.LOOKUP_MEANING('MSC_DRP_CHANGED_EXCEPTION_NAME'
, 2)
, 37
, MSC_GET_NAME.LOOKUP_MEANING('MSC_DRP_CHANGED_EXCEPTION_NAME'
, 37)
, MSC_GET_NAME.LOOKUP_MEANING('MRP_EXCEPTION_CODE_TYPE'
, MPR.EXCEPTION_TYPE))
, MSC_GET_NAME.LOOKUP_MEANING('MRP_EXCEPTION_CODE_TYPE'
, MPR.EXCEPTION_TYPE))
, MSC_GET_NAME.CP_EXCEPTION_TYPE_TEXT(MPR.EXCEPTION_TYPE)) EXCEPTION_TYPE_TEXT
, MPR.EXCEPTION_COUNT EXCEPTION_COUNT
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, MPT.SEQUENCE_ID
, MPR.CREATED_BY
, MPR.CREATION_DATE
, MPR.LAST_UPDATE_DATE
, MPR.LAST_UPDATED_BY
, MPR.LAST_UPDATE_LOGIN
, MPR.CHAR1
, MPR.CHAR2
, MPR.CHAR3
, MPR.CHAR4
, MPR.CHAR5
, GROUPBY_ORG
, GROUPBY_ITEM
, GROUPBY_SUPPLY_ITEM
, GROUPBY_CATEGORY
, GROUPBY_PLANNER
, GROUPBY_DEPT
, GROUPBY_RES
, GROUPBY_CUSTOMER
, GROUPBY_SUPPLIER
FROM MSC_PQ_RESULTS MPR
, MSC_PQ_TYPES MPT
, MSC_PERSONAL_QUERIES MPF
, MSC_PLANS MP
WHERE MPR.QUERY_ID = MPF.QUERY_ID
AND MPR.QUERY_ID = MPT.QUERY_ID
AND SUMMARY_DATA = 1
AND MPF.QUERY_TYPE=4
AND MPR.EXCEPTION_TYPE=MPT.OBJECT_TYPE
AND MPR.SOURCE_TYPE=MPT.SOURCE_TYPE
AND MPR.SEQUENCE_ID = MPT.SEQUENCE_ID
AND MPR.PLAN_ID = MP.PLAN_ID ORDER BY SEQUENCE_ID