42: , p_attribute30 varchar2
43: , p_owner VARCHAR2
44: , p_custom_mode VARCHAR2 ) IS
45:
46: l_pref_hierarchy_id hxc_pref_hierarchies.pref_hierarchy_id%TYPE;
47: l_parent_pref_hierarchy_id hxc_pref_hierarchies.parent_pref_hierarchy_id%TYPE;
48: l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
49:
50: l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
43: , p_owner VARCHAR2
44: , p_custom_mode VARCHAR2 ) IS
45:
46: l_pref_hierarchy_id hxc_pref_hierarchies.pref_hierarchy_id%TYPE;
47: l_parent_pref_hierarchy_id hxc_pref_hierarchies.parent_pref_hierarchy_id%TYPE;
48: l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
49:
50: l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
51: l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
44: , p_custom_mode VARCHAR2 ) IS
45:
46: l_pref_hierarchy_id hxc_pref_hierarchies.pref_hierarchy_id%TYPE;
47: l_parent_pref_hierarchy_id hxc_pref_hierarchies.parent_pref_hierarchy_id%TYPE;
48: l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
49:
50: l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
51: l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
52: l_attribute3 hxc_pref_hierarchies.attribute1%TYPE;
46: l_pref_hierarchy_id hxc_pref_hierarchies.pref_hierarchy_id%TYPE;
47: l_parent_pref_hierarchy_id hxc_pref_hierarchies.parent_pref_hierarchy_id%TYPE;
48: l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
49:
50: l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
51: l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
52: l_attribute3 hxc_pref_hierarchies.attribute1%TYPE;
53: l_attribute4 hxc_pref_hierarchies.attribute1%TYPE;
54: l_attribute5 hxc_pref_hierarchies.attribute1%TYPE;
47: l_parent_pref_hierarchy_id hxc_pref_hierarchies.parent_pref_hierarchy_id%TYPE;
48: l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
49:
50: l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
51: l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
52: l_attribute3 hxc_pref_hierarchies.attribute1%TYPE;
53: l_attribute4 hxc_pref_hierarchies.attribute1%TYPE;
54: l_attribute5 hxc_pref_hierarchies.attribute1%TYPE;
55: l_attribute6 hxc_pref_hierarchies.attribute1%TYPE;
48: l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
49:
50: l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
51: l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
52: l_attribute3 hxc_pref_hierarchies.attribute1%TYPE;
53: l_attribute4 hxc_pref_hierarchies.attribute1%TYPE;
54: l_attribute5 hxc_pref_hierarchies.attribute1%TYPE;
55: l_attribute6 hxc_pref_hierarchies.attribute1%TYPE;
56:
49:
50: l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
51: l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
52: l_attribute3 hxc_pref_hierarchies.attribute1%TYPE;
53: l_attribute4 hxc_pref_hierarchies.attribute1%TYPE;
54: l_attribute5 hxc_pref_hierarchies.attribute1%TYPE;
55: l_attribute6 hxc_pref_hierarchies.attribute1%TYPE;
56:
57: l_ovn hxc_pref_hierarchies.object_version_number%TYPE := NULL;
50: l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
51: l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
52: l_attribute3 hxc_pref_hierarchies.attribute1%TYPE;
53: l_attribute4 hxc_pref_hierarchies.attribute1%TYPE;
54: l_attribute5 hxc_pref_hierarchies.attribute1%TYPE;
55: l_attribute6 hxc_pref_hierarchies.attribute1%TYPE;
56:
57: l_ovn hxc_pref_hierarchies.object_version_number%TYPE := NULL;
58: l_owner VARCHAR2(6);
51: l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
52: l_attribute3 hxc_pref_hierarchies.attribute1%TYPE;
53: l_attribute4 hxc_pref_hierarchies.attribute1%TYPE;
54: l_attribute5 hxc_pref_hierarchies.attribute1%TYPE;
55: l_attribute6 hxc_pref_hierarchies.attribute1%TYPE;
56:
57: l_ovn hxc_pref_hierarchies.object_version_number%TYPE := NULL;
58: l_owner VARCHAR2(6);
59: l_dummy VARCHAR2(6);
53: l_attribute4 hxc_pref_hierarchies.attribute1%TYPE;
54: l_attribute5 hxc_pref_hierarchies.attribute1%TYPE;
55: l_attribute6 hxc_pref_hierarchies.attribute1%TYPE;
56:
57: l_ovn hxc_pref_hierarchies.object_version_number%TYPE := NULL;
58: l_owner VARCHAR2(6);
59: l_dummy VARCHAR2(6);
60:
61:
60:
61:
62: CURSOR csr_get_owner ( p_pref_hierarchy_id NUMBER ) IS
63: SELECT DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
64: FROM hxc_pref_hierarchies;
65:
66: FUNCTION get_pref_def_id ( p_pref_def_name VARCHAR2 ) RETURN NUMBER IS
67:
68: l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
64: FROM hxc_pref_hierarchies;
65:
66: FUNCTION get_pref_def_id ( p_pref_def_name VARCHAR2 ) RETURN NUMBER IS
67:
68: l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
69:
70: CURSOR csr_get_pref_def_id IS
71: SELECT pd.pref_definition_id
72: FROM hxc_pref_definitions pd
288: END IF;
289:
290: -- check to see if the preference we are about to load already exists
291:
292: hxc_pref_hierarchies_api.get_node_data (
293: p_preference_full_name => p_parent_name
294: , p_name => p_name
295: , p_business_group_id => null
296: , p_legislation_code => p_legislation_code
308:
309: IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
310: THEN
311:
312: hxc_pref_hierarchies_api.update_pref_hierarchies (
313: p_effective_date => sysdate
314: , p_name => p_name
315: , p_business_group_id => null
316: , p_legislation_code => p_legislation_code
356: END IF;
357:
358: ELSE
359:
360: hxc_pref_hierarchies_api.create_pref_hierarchies (
361: p_effective_date => sysdate
362: , p_name => p_name
363: , p_business_group_id => null
364: , p_legislation_code => p_legislation_code
564: , p_pref_node VARCHAR2
565: , p_pref_level NUMBER
566: , p_count NUMBER ) RETURN VARCHAR2 IS
567:
568: l_level_one hxc_pref_hierarchies.name%TYPE := NULL;
569: l_level_two hxc_pref_hierarchies.name%TYPE := NULL;
570: l_level_three hxc_pref_hierarchies.name%TYPE := NULL;
571: l_level_four hxc_pref_hierarchies.name%TYPE := NULL;
572: l_level_five hxc_pref_hierarchies.name%TYPE := NULL;
565: , p_pref_level NUMBER
566: , p_count NUMBER ) RETURN VARCHAR2 IS
567:
568: l_level_one hxc_pref_hierarchies.name%TYPE := NULL;
569: l_level_two hxc_pref_hierarchies.name%TYPE := NULL;
570: l_level_three hxc_pref_hierarchies.name%TYPE := NULL;
571: l_level_four hxc_pref_hierarchies.name%TYPE := NULL;
572: l_level_five hxc_pref_hierarchies.name%TYPE := NULL;
573:
566: , p_count NUMBER ) RETURN VARCHAR2 IS
567:
568: l_level_one hxc_pref_hierarchies.name%TYPE := NULL;
569: l_level_two hxc_pref_hierarchies.name%TYPE := NULL;
570: l_level_three hxc_pref_hierarchies.name%TYPE := NULL;
571: l_level_four hxc_pref_hierarchies.name%TYPE := NULL;
572: l_level_five hxc_pref_hierarchies.name%TYPE := NULL;
573:
574: l_full_name VARCHAR2(500);
567:
568: l_level_one hxc_pref_hierarchies.name%TYPE := NULL;
569: l_level_two hxc_pref_hierarchies.name%TYPE := NULL;
570: l_level_three hxc_pref_hierarchies.name%TYPE := NULL;
571: l_level_four hxc_pref_hierarchies.name%TYPE := NULL;
572: l_level_five hxc_pref_hierarchies.name%TYPE := NULL;
573:
574: l_full_name VARCHAR2(500);
575:
568: l_level_one hxc_pref_hierarchies.name%TYPE := NULL;
569: l_level_two hxc_pref_hierarchies.name%TYPE := NULL;
570: l_level_three hxc_pref_hierarchies.name%TYPE := NULL;
571: l_level_four hxc_pref_hierarchies.name%TYPE := NULL;
572: l_level_five hxc_pref_hierarchies.name%TYPE := NULL;
573:
574: l_full_name VARCHAR2(500);
575:
576: l_last_level NUMBER(1) := 1;
578: CURSOR get_pref_hierarchy ( p_top_node VARCHAR2 ) IS
579: SELECT name
580: , level
581: , rownum cnt
582: from hxc_pref_hierarchies
583: start with name = p_top_node
584: connect by prior pref_hierarchy_id = parent_pref_hierarchy_id;
585:
586: BEGIN