VIEW: APPS.MSC_WL_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, 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, mpr.inventory_item_id, msc_get_name.item_name(mpr.inventory_item_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id) item_name, msc_get_name.item_desc(mpr.inventory_item_id, mpr.organization_id, mpr.plan_id, mpr.sr_instance_id) item_desc, mpr.supplier_id, mpr.supplier_site_id, msc_get_name.supplier(mpr.supplier_id) supplier_name, msc_get_name.supplier_site(mpr.supplier_site_id) supplier_site_name, mpr.customer_id, mpr.customer_site_id, msc_get_name.customer(mpr.customer_id) customer_name, msc_get_name.customer(mpr.customer_site_id) customer_site_name, msc_get_name.org_code(mpr.organization_id, mpr.sr_instance_id) org_code, msc_get_name.category_name(mpr.category_id, -1, mpr.organization_id, mpr.sr_instance_id) category_name, MPR.planner_code, mpr.sequence_id, mpr.created_by, mpr.creation_date, mpr.last_update_date, mpr.last_updated_by, mpr.last_update_login, groupby_org, groupby_item, groupby_category, groupby_planner, groupby_customer, groupby_customer_SITE, groupby_supplier, groupby_supplier_SITE, mpr.priority, DETAIL_QUERY_ID, TRANSACTION_ID, mpr.CATEGORY_ID FROM msc_pq_results mpr, msc_personal_queries mpf, msc_plans mp WHERE mpr.query_id = mpf.query_id AND mpr.summary_data = 1 AND mpf.query_type = 10 AND mpr.plan_id = mp.plan_id ORDER BY mpr.priority

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
, 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
, MPR.INVENTORY_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(MPR.INVENTORY_ITEM_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID) ITEM_NAME
, MSC_GET_NAME.ITEM_DESC(MPR.INVENTORY_ITEM_ID
, MPR.ORGANIZATION_ID
, MPR.PLAN_ID
, MPR.SR_INSTANCE_ID) ITEM_DESC
, MPR.SUPPLIER_ID
, MPR.SUPPLIER_SITE_ID
, MSC_GET_NAME.SUPPLIER(MPR.SUPPLIER_ID) SUPPLIER_NAME
, MSC_GET_NAME.SUPPLIER_SITE(MPR.SUPPLIER_SITE_ID) SUPPLIER_SITE_NAME
, MPR.CUSTOMER_ID
, MPR.CUSTOMER_SITE_ID
, MSC_GET_NAME.CUSTOMER(MPR.CUSTOMER_ID) CUSTOMER_NAME
, MSC_GET_NAME.CUSTOMER(MPR.CUSTOMER_SITE_ID) CUSTOMER_SITE_NAME
, MSC_GET_NAME.ORG_CODE(MPR.ORGANIZATION_ID
, MPR.SR_INSTANCE_ID) ORG_CODE
, MSC_GET_NAME.CATEGORY_NAME(MPR.CATEGORY_ID
, -1
, MPR.ORGANIZATION_ID
, MPR.SR_INSTANCE_ID) CATEGORY_NAME
, MPR.PLANNER_CODE
, MPR.SEQUENCE_ID
, MPR.CREATED_BY
, MPR.CREATION_DATE
, MPR.LAST_UPDATE_DATE
, MPR.LAST_UPDATED_BY
, MPR.LAST_UPDATE_LOGIN
, GROUPBY_ORG
, GROUPBY_ITEM
, GROUPBY_CATEGORY
, GROUPBY_PLANNER
, GROUPBY_CUSTOMER
, GROUPBY_CUSTOMER_SITE
, GROUPBY_SUPPLIER
, GROUPBY_SUPPLIER_SITE
, MPR.PRIORITY
, DETAIL_QUERY_ID
, TRANSACTION_ID
, MPR.CATEGORY_ID
FROM MSC_PQ_RESULTS MPR
, MSC_PERSONAL_QUERIES MPF
, MSC_PLANS MP
WHERE MPR.QUERY_ID = MPF.QUERY_ID
AND MPR.SUMMARY_DATA = 1
AND MPF.QUERY_TYPE = 10
AND MPR.PLAN_ID = MP.PLAN_ID ORDER BY MPR.PRIORITY