VIEW: APPS.ENI_ITEM_VBH_CAT_V

Source

View Text - Preformatted

SELECT ID 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.CATEGORY_CONCAT_SEGS||')' ) VALUE, PARENT_ID, to_char(node) NODE, MDFT.CATEGORY_SET_ID FROM MTL_CATEGORIES_V MCAT, MTL_DEFAULT_CATEGORY_SETS MDFT, (SELECT category_id ID, parent_category_id PARENT_ID, parent_category_id NODE, category_set_id CATEGORY_SET_ID FROM mtl_category_set_valid_cats WHERE parent_category_id IS NOT NULL UNION ALL SELECT a.category_id, a.parent_category_id, a.category_id, a.category_set_id FROM mtl_category_set_valid_cats a, mtl_category_sets_b b WHERE a.category_set_id = b.category_set_id AND b.validate_flag = 'Y' UNION ALL SELECT parent_category_id, to_number(null), category_id, category_set_id FROM mtl_category_set_valid_cats WHERE parent_category_id is not null UNION ALL SELECT child_id, to_number(null), to_number(null), object_id FROM eni_denorm_hierarchies WHERE object_type = 'CATEGORY_SET' AND parent_id = child_id AND top_node_flag = 'Y' UNION ALL select a.child_id, to_number(null), a.parent_id, a.object_id from eni_denorm_hierarchies a, mtl_category_sets_b b where object_type='CATEGORY_SET' and a.object_id = b.category_set_id and validate_flag = 'N' and top_node_flag = 'Y' ) VCAT WHERE MCAT.CATEGORY_ID (+) = VCAT.ID AND vcat.category_set_id = mdft.category_set_id AND mdft.functional_area_id = 11

View Text - HTML Formatted

SELECT ID 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.CATEGORY_CONCAT_SEGS||')' ) VALUE
, PARENT_ID
, TO_CHAR(NODE) NODE
, MDFT.CATEGORY_SET_ID
FROM MTL_CATEGORIES_V MCAT
, MTL_DEFAULT_CATEGORY_SETS MDFT
, (SELECT CATEGORY_ID ID
, PARENT_CATEGORY_ID PARENT_ID
, PARENT_CATEGORY_ID NODE
, CATEGORY_SET_ID CATEGORY_SET_ID
FROM MTL_CATEGORY_SET_VALID_CATS
WHERE PARENT_CATEGORY_ID IS NOT NULL UNION ALL SELECT A.CATEGORY_ID
, A.PARENT_CATEGORY_ID
, A.CATEGORY_ID
, A.CATEGORY_SET_ID
FROM MTL_CATEGORY_SET_VALID_CATS A
, MTL_CATEGORY_SETS_B B
WHERE A.CATEGORY_SET_ID = B.CATEGORY_SET_ID
AND B.VALIDATE_FLAG = 'Y' UNION ALL SELECT PARENT_CATEGORY_ID
, TO_NUMBER(NULL)
, CATEGORY_ID
, CATEGORY_SET_ID
FROM MTL_CATEGORY_SET_VALID_CATS
WHERE PARENT_CATEGORY_ID IS NOT NULL UNION ALL SELECT CHILD_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OBJECT_ID
FROM ENI_DENORM_HIERARCHIES
WHERE OBJECT_TYPE = 'CATEGORY_SET'
AND PARENT_ID = CHILD_ID
AND TOP_NODE_FLAG = 'Y' UNION ALL SELECT A.CHILD_ID
, TO_NUMBER(NULL)
, A.PARENT_ID
, A.OBJECT_ID
FROM ENI_DENORM_HIERARCHIES A
, MTL_CATEGORY_SETS_B B
WHERE OBJECT_TYPE='CATEGORY_SET'
AND A.OBJECT_ID = B.CATEGORY_SET_ID
AND VALIDATE_FLAG = 'N'
AND TOP_NODE_FLAG = 'Y' ) VCAT
WHERE MCAT.CATEGORY_ID (+) = VCAT.ID
AND VCAT.CATEGORY_SET_ID = MDFT.CATEGORY_SET_ID
AND MDFT.FUNCTIONAL_AREA_ID = 11