APPS.HZ_GEOGRAPHY_PUB dependencies on HZ_HIERARCHY_NODES

Line 98: FROM hz_hierarchy_nodes

94: -- This query does the grouping based on parent id and geography type
95: -- Note : sysdate is not truncated in check because we want to not pick
96: -- up those records which are just now end dated (end dated in current flow)
97: SELECT parent_id, parent_object_type, level_number
98: FROM hz_hierarchy_nodes
99: WHERE hierarchy_type = 'MASTER_REF'
100: AND SYSDATE+0.0001 BETWEEN effective_start_date AND effective_end_date
101: AND child_table_name = 'HZ_GEOGRAPHIES'
102: AND NVL(status,'A') = 'A'

Line 138: FROM HZ_HIERARCHY_NODES

134: x_return_status IN OUT NOCOPY VARCHAR2) IS
135:
136: CURSOR c_get_all_parents IS
137: SELECT parent_id,parent_object_type
138: FROM HZ_HIERARCHY_NODES
139: WHERE child_id = p_geography_id
140: AND child_object_type = p_geography_type
141: AND child_table_name = 'HZ_GEOGRAPHIES'
142: AND hierarchy_type = 'MASTER_REF'

Line 273: FROM HZ_HIERARCHY_NODES

269: ) IS
270:
271: CURSOR c_get_all_children IS
272: SELECT child_id,child_object_type
273: FROM HZ_HIERARCHY_NODES
274: WHERE parent_id=p_geography_id
275: AND parent_object_type=p_geography_type
276: AND parent_table_name = 'HZ_GEOGRAPHIES'
277: AND hierarchy_type='MASTER_REF'

Line 325: FROM hz_hierarchy_nodes

321: -- check if all parents of geography_id are of same geography_type
322: BEGIN
323:
324: SELECT distinct parent_object_type INTO l_parent_object_type
325: FROM hz_hierarchy_nodes
326: WHERE child_id = p_geography_id
327: AND child_object_type=p_geography_type
328: AND child_table_name = 'HZ_GEOGRAPHIES'
329: AND hierarchy_type='MASTER_REF'

Line 369: FROM hz_hierarchy_nodes

365: --dbms_output.put_line ('geo_element_column is '||l_geo_element_col);
366:
367: -- check if all parents of geography_id have same parent
368: SELECT count(distinct parent_id) INTO l_count
369: FROM hz_hierarchy_nodes
370: WHERE child_id in ( SELECT parent_id
371: FROM hz_hierarchy_nodes
372: WHERE child_id = p_geography_id
373: AND child_object_type = p_geography_type

Line 371: FROM hz_hierarchy_nodes

367: -- check if all parents of geography_id have same parent
368: SELECT count(distinct parent_id) INTO l_count
369: FROM hz_hierarchy_nodes
370: WHERE child_id in ( SELECT parent_id
371: FROM hz_hierarchy_nodes
372: WHERE child_id = p_geography_id
373: AND child_object_type = p_geography_type
374: AND child_table_name = 'HZ_GEOGRAPHIES'
375: AND hierarchy_type = 'MASTER_REF'

Line 760: SELECT count(*) INTO l_count FROM HZ_HIERARCHY_NODES

756: END;
757: -- a geography can not have two countries as its parents
758: IF l_parent_geography_type = 'COUNTRY' THEN
759:
760: SELECT count(*) INTO l_count FROM HZ_HIERARCHY_NODES
761: WHERE hierarchy_type='MASTER_REF'
762: AND child_id=p_master_relation_rec.geography_id
763: AND parent_object_type='COUNTRY'
764: AND NVL(status,'A') = 'A'

Line 913: FROM hz_hierarchy_nodes

909: l_relationship_type VARCHAR2(30);
910: --p_subject_flag VARCHAR2(1);
911: CURSOR c_get_all_children IS
912: SELECT child_id, child_object_type
913: FROM hz_hierarchy_nodes
914: WHERE hierarchy_type='MASTER_REF'
915: AND parent_id=l_geography_id
916: AND child_table_name = 'HZ_GEOGRAPHIES'
917: AND NVL(status,'A') = 'A'

Line 2308: /* update hz_hierarchy_nodes

2304:
2305: END LOOP;
2306: CLOSE c_get_all_relationships;
2307:
2308: /* update hz_hierarchy_nodes
2309: set effective_end_date = p_end_date
2310: where hierarchy_type=l_geography_use
2311: and parent_id = p_geography_id
2312: or child_id = p_geography_id ; */

Line 2736: FROM hz_hierarchy_nodes

2732: IF l_limited_by_geography_id IS NOT NULL THEN
2733: --included geography_id should have a relationship with limited_by_geography_id either at level >= 0
2734: BEGIN
2735: SELECT 1 INTO l_count
2736: FROM hz_hierarchy_nodes
2737: WHERE parent_id = l_limited_by_geography_id
2738: AND child_id = p_zone_relation_tbl(i).included_geography_id
2739: AND hierarchy_type = 'MASTER_REF'
2740: AND NVL(status,'A') = 'A'