[Home] [Help]
46:
47: -- Bug # 5723438 fixed by ateotia (+)
48: /* CURSOR c_hier_exists (l_start_node_id IN NUMBER) IS
49: SELECT DISTINCT heirarchy_id
50: FROM ozf_terr_levels_all
51: WHERE heirarchy_id = l_start_node_id; */
52: -- Bug # 5723438 fixed by ateotia (-)
53: BEGIN
54:
103:
104: -- Update active_flag to 'N' for all OZF Territories to identify the old territories.
105: ozf_utility_pvt.write_conc_log ('Update active_flag to N for all OZF Territories to identify the old territories.');
106:
107: UPDATE ozf_terr_levels_all
108: SET active_flag = 'N';
109:
110: ozf_utility_pvt.write_conc_log ('Territory Insertion Call');
111: ozf_terr_levels_pvt.insert_terr_levels (
122: ELSE
123: -- Update active_flag to 'N' for all OZF Territories to identify the old territories.
124: ozf_utility_pvt.write_conc_log ('Update active_flag to N for all OZF Territories to identify the old territories.');
125:
126: UPDATE ozf_terr_levels_all
127: SET active_flag = 'N';
128:
129: ozf_utility_pvt.write_conc_log ('Bulk Territories Insertion Call');
130: -- Bug # 5723438 fixed by ateotia (-)
207: SELECT NVL (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10), -99)
208: FROM DUAL;
209:
210: CURSOR c_get_seq IS
211: SELECT ozf_terr_levels_all_s.NEXTVAL
212: FROM DUAL;
213:
214:
215: -- cursor to check uniqueness
228: WHERE jtf.terr_id = l_terr_id;
229:
230: CURSOR c_level_value (p_hierarchy_id IN NUMBER, p_terr_type_id IN NUMBER) IS
231: SELECT olv.level_depth
232: FROM ozf_terr_levels_all olv
233: WHERE olv.heirarchy_id = p_hierarchy_id
234: AND olv.terr_type_id = p_terr_type_id;
235:
236:
349: -- 07/13/2001 mpande added . We want ot purge all the hierarchy that was created for the territories that
350: -- are now a part of this new hierarchy
351: -- note no territory can be a part of 2 hierarchies
352: IF l_terr_level_rec.terr_id <> p_start_node_id THEN
353: DELETE FROM ozf_terr_levels_all
354: WHERE heirarchy_id = l_terr_level_rec.terr_id;
355: END IF;
356:
357: --DBMS_OUTPUT.put_line ( 'TT:= '|| l_terr_level_rec.territory_type_id || 'LD = ' || l_terr_level_rec.level_depth );
380: FETCH c_get_seq INTO l_terr_level_id;
381: CLOSE c_get_seq;
382:
383: ozf_utility_pvt.write_conc_log('******* Insert into OZF Schema *******');
384: INSERT INTO ozf_terr_levels_all
385: (terr_level_id
386: ,creation_date
387: ,created_by
388: ,last_update_date
460: );
461:
462: ozf_utility_pvt.write_conc_log('******* Delete Duplicates *******');
463: -- Bug # 5723438 fixed by ateotia (+)
464: DELETE from ozf_terr_levels_all
465: WHERE active_flag = 'N'
466: AND territory_id = l_terr_level_rec.terr_id
467: AND parent_territory_id = l_terr_level_rec.parent_territory_id;
468: -- Bug # 5723438 fixed by ateotia (-)
609: l_api_name CONSTANT VARCHAR2 (30) := 'Delete_terr_levels';
610: l_api_version CONSTANT NUMBER := 1.0;
611: l_org_id NUMBER;
612: l_insert_count NUMBER;
613: l_terr_level_rec ozf_terr_levels_all%ROWTYPE;
614: l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
615: || '.'
616: || l_api_name;
617: l_terr_level_id NUMBER;
620: --cursor changed later because now once allocation is done to a heirarchy you cannot update the resord
621: /* mpadne 07/13/2001 -- we will delete all records for that hierarhcy and recreate it
622: CURSOR c_delete_terr (l_hierarchy_id IN NUMBER) IS
623: SELECT *
624: FROM ozf_terr_levels_all a
625: WHERE a.heirarchy_id = l_hierarchy_id;
626: */
627:
628: BEGIN
640: -- Initialize API return status to success
641: x_return_status := fnd_api.g_ret_sts_success;
642:
643: -- 07/13/2001 mpande delete the whole hierarchy structure to recreate it
644: DELETE FROM ozf_terr_levels_all
645: WHERE heirarchy_id = p_hierarchy_id;
646: -- API body
647: /*
648: FOR l_terr_level_rec IN c_delete_terr (p_hierarchy_id)
649: LOOP
650: ozf_utility_pvt.debug_message ( l_full_name
651: || ': begin');
652:
653: DELETE FROM ozf_terr_levels_all
654: WHERE terr_level_id = l_terr_level_rec.terr_level_id;
655: END LOOP;
656:
657: IF fnd_api.to_boolean (p_commit) THEN