VIEW: APPS.ENI_ITEM_ITM_CAT_V

Source

View Text - Preformatted

SELECT ID, decode(ID, -1, (Select description || ' (' || meaning || ') ' from fnd_lookup_values where lookup_type ='ITEM_CATG' and lookup_code ='-1' and language = userenv('LANG')), mcat.description || ' (' || mcat.catalog_concat_segs || ') ' ) value, parent_id, to_char(node) node from ( select item_catalog_group_id id, parent_catalog_group_id parent_id, parent_catalog_group_id node from mtl_item_catalog_groups_b where parent_catalog_group_id is not null union all select item_catalog_group_id id, parent_catalog_group_id parent_id, item_catalog_group_id node from mtl_item_catalog_groups_b union all select parent_catalog_group_id id, to_number(null) parent_id, item_catalog_group_id node from mtl_item_catalog_groups_b where parent_catalog_group_id is not null union all select item_catalog_group_id, to_number(null), to_number(null) from mtl_item_catalog_groups_b where parent_catalog_group_id is null union all select -1, to_number(null), to_number(null) from dual ) VCAT, mtl_item_catalog_groups_v MCAT WHERE MCAT.item_catalog_group_id(+) = VCAT.id WITH READ ONLY

View Text - HTML Formatted

SELECT ID
, DECODE(ID
, -1
, (SELECT DESCRIPTION || ' (' || MEANING || ') '
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE ='ITEM_CATG'
AND LOOKUP_CODE ='-1'
AND LANGUAGE = USERENV('LANG'))
, MCAT.DESCRIPTION || ' (' || MCAT.CATALOG_CONCAT_SEGS || ') ' ) VALUE
, PARENT_ID
, TO_CHAR(NODE) NODE
FROM ( SELECT ITEM_CATALOG_GROUP_ID ID
, PARENT_CATALOG_GROUP_ID PARENT_ID
, PARENT_CATALOG_GROUP_ID NODE
FROM MTL_ITEM_CATALOG_GROUPS_B
WHERE PARENT_CATALOG_GROUP_ID IS NOT NULL UNION ALL SELECT ITEM_CATALOG_GROUP_ID ID
, PARENT_CATALOG_GROUP_ID PARENT_ID
, ITEM_CATALOG_GROUP_ID NODE
FROM MTL_ITEM_CATALOG_GROUPS_B UNION ALL SELECT PARENT_CATALOG_GROUP_ID ID
, TO_NUMBER(NULL) PARENT_ID
, ITEM_CATALOG_GROUP_ID NODE
FROM MTL_ITEM_CATALOG_GROUPS_B
WHERE PARENT_CATALOG_GROUP_ID IS NOT NULL UNION ALL SELECT ITEM_CATALOG_GROUP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MTL_ITEM_CATALOG_GROUPS_B
WHERE PARENT_CATALOG_GROUP_ID IS NULL UNION ALL SELECT -1
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM DUAL ) VCAT
, MTL_ITEM_CATALOG_GROUPS_V MCAT
WHERE MCAT.ITEM_CATALOG_GROUP_ID(+) = VCAT.ID WITH READ ONLY