[Home] [Help]
14: SELECT
15: V.CONCATENATED_SEGMENTS PARENT_CODE,
16: V1.CONCATENATED_SEGMENTS CHILD_CODE,
17: DECODE(F.SUMMARY_FLAG, 'Y', 'P', 'C') RANGE_ATTRIBUTE
18: FROM MTL_CATEGORY_SET_VALID_CATS T, MTL_CATEGORIES_KFV V, MTL_CATEGORIES_KFV V1, FND_FLEX_VALUES F
19: WHERE T.CATEGORY_SET_ID = g_catset_id
20: AND T.CATEGORY_ID = V1.CATEGORY_ID
21: AND T.PARENT_CATEGORY_ID = V.CATEGORY_ID
22: AND V1.CONCATENATED_SEGMENTS = F.FLEX_VALUE
33: CURSOR c_hierarchy_top_node(l_top_node VARCHAR2) IS
34: select l_top_node parent_code,
35: concatenated_segments child_code,
36: decode(f.summary_flag,'Y','P','C') range_attribute
37: from mtl_category_set_valid_cats a,
38: mtl_categories_kfv b, fnd_flex_values f
39: where parent_category_id is null
40: and category_set_id = g_catset_id
41: and a.category_id = b.category_id
60: -- value set with a circular reference
61:
62: CURSOR c_hierarchy_loop(l_top_node VARCHAR2) IS
63: SELECT b.concatenated_segments nodes
64: FROM mtl_category_set_valid_cats a, mtl_categories_kfv b
65: WHERE a.category_set_id = g_catset_id
66: AND a.category_id = b.category_id
67: AND b.structure_id = l_struct_id
68: INTERSECT
317: V.ENABLED_FLAG,
318: T.DESCRIPTION,
319: V.START_DATE_ACTIVE,
320: V.DISABLE_DATE,
321: NVL((SELECT ''Y'' FROM MTL_CATEGORY_SET_VALID_CATS C
322: WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
323: AND C.PARENT_CATEGORY_ID = V.CATEGORY_ID
324: AND ROWNUM = 1), ''N'') SUMMARY_FLAG
325: FROM MTL_CATEGORIES_KFV V,
323: AND C.PARENT_CATEGORY_ID = V.CATEGORY_ID
324: AND ROWNUM = 1), ''N'') SUMMARY_FLAG
325: FROM MTL_CATEGORIES_KFV V,
326: MTL_CATEGORIES_TL T,
327: MTL_CATEGORY_SET_VALID_CATS H
328: WHERE V.STRUCTURE_ID = :l_struct_id
329: AND V.CATEGORY_ID = T.CATEGORY_ID
330: AND T.LANGUAGE = USERENV(''LANG'')
331: AND V.CATEGORY_ID = H.CATEGORY_ID
409: SELECT
410: F.FLEX_VALUE_ID CHILD_VALUE_ID,
411: F.FLEX_VALUE CHILD_CODE,
412: T.DESCRIPTION NEW_DESCRIPTION,
413: NVL((SELECT V.ENABLED_FLAG FROM MTL_CATEGORY_SET_VALID_CATS C
414: WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
415: AND C.CATEGORY_ID = V.CATEGORY_ID
416: AND ROWNUM = 1),''N'') NEW_ENABLED_FLAG,
417: V.START_DATE_ACTIVE NEW_START_DATE,
420: F.START_DATE_ACTIVE OLD_START_DATE,
421: F.END_DATE_ACTIVE OLD_END_DATE,
422: F.SUMMARY_FLAG OLD_SUMMARY_FLAG,
423: FT.DESCRIPTION OLD_DESCRIPTION,
424: NVL((SELECT ''Y'' FROM MTL_CATEGORY_SET_VALID_CATS C
425: WHERE C.CATEGORY_SET_ID = H.CATEGORY_SET_ID
426: AND C.PARENT_CATEGORY_ID = V.CATEGORY_ID
427: AND ROWNUM = 1), ''N'') NEW_SUMMARY_FLAG
428: FROM MTL_CATEGORIES_KFV V, MTL_CATEGORIES_TL T,
427: AND ROWNUM = 1), ''N'') NEW_SUMMARY_FLAG
428: FROM MTL_CATEGORIES_KFV V, MTL_CATEGORIES_TL T,
429: FND_FLEX_VALUES F,
430: FND_FLEX_VALUES_TL FT,
431: MTL_CATEGORY_SET_VALID_CATS H
432: WHERE V.STRUCTURE_ID = :l_struct_id
433: AND V.CATEGORY_ID = T.CATEGORY_ID
434: AND T.LANGUAGE = USERENV(''LANG'')
435: AND F.flex_value= V.CONCATENATED_SEGMENTS
656: not in (
657: select nvl(a.concatenated_segments,l_top_node),
658: b.concatenated_segments,
659: b.concatenated_segments,
660: NVL((select 'P' from mtl_category_set_valid_cats v
661: where v.category_set_id = c.category_set_id
662: and v.parent_category_id = b.category_id
663: and rownum = 1), 'C')
664: -- decode(b.summary_flag,'Y','P','C')
663: and rownum = 1), 'C')
664: -- decode(b.summary_flag,'Y','P','C')
665: from mtl_categories_kfv a,
666: mtl_categories_kfv b,
667: mtl_category_set_valid_cats c
668: where a.structure_id(+) = l_struct_id
669: and b.structure_id = l_struct_id
670: and c.category_set_id = g_catset_id
671: and c.parent_category_id = a.category_id(+)