VIEW: APPS.MSC_PHUB_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 distinct         category_set_id,         sr_category_id,         sr_instance_id,         category_name,         description     from msc_item_categories     where category_set_id in (select category_set_id from t where category_set_id is not null)     union all     select distinct         t.category_set_id,         to_number(-23453) sr_category_id,         mai.sr_instance_id,         to_char(null) category_name,         to_char(null) description     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 DISTINCT CATEGORY_SET_ID
, SR_CATEGORY_ID
, SR_INSTANCE_ID
, CATEGORY_NAME
, DESCRIPTION
FROM MSC_ITEM_CATEGORIES
WHERE CATEGORY_SET_ID IN (SELECT CATEGORY_SET_ID
FROM T
WHERE CATEGORY_SET_ID IS NOT NULL) UNION ALL SELECT DISTINCT T.CATEGORY_SET_ID
, TO_NUMBER(-23453) SR_CATEGORY_ID
, MAI.SR_INSTANCE_ID
, TO_CHAR(NULL) CATEGORY_NAME
, TO_CHAR(NULL) DESCRIPTION
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