VIEW: APPS.MSC_PHUB_ITEM_CATEGORIES_MV#

Source

View Text - Preformatted

with t as (         select category_set_id1 category_set_id from msc_apcc_config where rownum=1         union select category_set_id2 from msc_apcc_config where rownum=1         union select category_set_id3 from msc_apcc_config where rownum=1         union select pg_category_set_id from msc_apcc_config where rownum=1     )     select         category_set_id,         sr_instance_id,         organization_id,         inventory_item_id,         min(sr_category_id) sr_category_id     from msc_item_categories     where category_set_id in (select category_set_id from t where category_set_id is not null)     group by         category_set_id,         sr_instance_id,         organization_id,         inventory_item_id     union all     select distinct         t.category_set_id,         mai.sr_instance_id,         to_number(-23453) organization_id,         to_number(-23453) inventory_item_id,         to_number(-23453) sr_category_id     from (select category_set_id from t where category_set_id is not null) t,         (select distinct instance_id sr_instance_id from msc_apps_instances         union all select to_number(-23453) from dual) mai

View Text - HTML Formatted

WITH T AS ( SELECT CATEGORY_SET_ID1 CATEGORY_SET_ID
FROM MSC_APCC_CONFIG
WHERE ROWNUM=1 UNION SELECT CATEGORY_SET_ID2
FROM MSC_APCC_CONFIG
WHERE ROWNUM=1 UNION SELECT CATEGORY_SET_ID3
FROM MSC_APCC_CONFIG
WHERE ROWNUM=1 UNION SELECT PG_CATEGORY_SET_ID
FROM MSC_APCC_CONFIG
WHERE ROWNUM=1 ) SELECT CATEGORY_SET_ID
, SR_INSTANCE_ID
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, MIN(SR_CATEGORY_ID) SR_CATEGORY_ID
FROM MSC_ITEM_CATEGORIES
WHERE CATEGORY_SET_ID IN (SELECT CATEGORY_SET_ID
FROM T
WHERE CATEGORY_SET_ID IS NOT NULL) GROUP BY CATEGORY_SET_ID
, SR_INSTANCE_ID
, ORGANIZATION_ID
, INVENTORY_ITEM_ID UNION ALL SELECT DISTINCT T.CATEGORY_SET_ID
, MAI.SR_INSTANCE_ID
, TO_NUMBER(-23453) ORGANIZATION_ID
, TO_NUMBER(-23453) INVENTORY_ITEM_ID
, TO_NUMBER(-23453) SR_CATEGORY_ID
FROM (SELECT CATEGORY_SET_ID
FROM T
WHERE CATEGORY_SET_ID IS NOT NULL) T
, (SELECT DISTINCT INSTANCE_ID SR_INSTANCE_ID
FROM MSC_APPS_INSTANCES UNION ALL SELECT TO_NUMBER(-23453)
FROM DUAL) MAI