[Home] [Help]
PACKAGE BODY: APPS.OZF_TERR_LEVELS_PVT
Source
1 PACKAGE BODY ozf_terr_levels_pvt AS
2 /*$Header: ozfvtlvb.pls 120.2.12010000.2 2008/08/08 09:31:51 ateotia ship $*/
3 /*---------------------------------------------------------------------
4 -- PROCEDURE
5 --
6 --
7 -- HISTORY
8 -- 03/07/00 mpande Created.
9 -- 07/12/2001 mpande Updated .
10 -- We want to delete the hierarchy and then recreate it after creation
11 -- 11/04/2002 yzhao change to ozf tables/views
12 -- 06/09/2005 kdass Bug 4415878 SQL Repository Fix - removed update_terr_levels as it is not used anywhere
13 -- 01-Aug-2008 ateotia Bug # 5723438 fixed.
14 -- FP:11510-R12 5533277 - TERRITORY DETAIL'S END DATE IS NOT WORKING
15 ---------------------------------------------------------------------
16 */
17 g_pkg_name CONSTANT VARCHAR2 (30) := 'OZF_TERR_LEVELS_PVT';
18 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
19 g_bulk_limit CONSTANT NUMBER := 1000; -- yzhao: Sep 8,2005 bulk fetch limit. It should get from profile.
20
21
22 TYPE terrIdTbl IS TABLE OF jtf_terr_all.terr_id%TYPE;
23
24
25 /*---------------------------------------------------------------------
26 -- PROCEDURE
27 --
28 --
29 -- HISTORY
30 --
31 -- this pacakage is called from the concurrent process manager
32 -- p_start_node_id: if specified, import territory level for that node
33 -- if not specified, import all territory levels under Trade Management
34 --------------------------------------------------------------------------*/
35 PROCEDURE create_terr_hierarchy (
36 errbuf OUT NOCOPY VARCHAR2
37 ,retcode OUT NOCOPY NUMBER
38 ,p_start_node_id IN NUMBER
39 ) IS
40 x_return_status VARCHAR2 (10);
41 x_msg_data VARCHAR2 (240);
42 x_msg_count NUMBER;
43 x_index NUMBER;
44 l_hierarchy_id NUMBER;
45 l_start_node_id NUMBER;
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
55
56 x_return_status := fnd_api.g_ret_sts_success;
57 ozf_utility_pvt.write_conc_log ('Import Territory Hierarchy: begin process');
58
59 IF g_debug THEN
60 ozf_utility_pvt.debug_message ('create_terr_hierarchy: p_start_node_id=' || p_start_node_id);
61 END IF;
62
63 IF p_start_node_id IS NOT NULL THEN
64
65 -- Bug # 5723438 fixed by ateotia (+)
66 -- Removed the delete step, instead the records are inserted & then duplicates are removed later.
67 /* OPEN c_hier_exists (p_start_node_id);
68 FETCH c_hier_exists INTO l_start_node_id;
69
70 -- if no hierarchy found then insert
71 -- 07/13/2001 mpande if found then delete and recreate it .
72 IF c_hier_exists%FOUND THEN
73 CLOSE c_hier_exists;
74 ozf_terr_levels_pvt.delete_terr_levels (
75 p_api_version => 1.0
76 ,p_init_msg_list => fnd_api.g_true
77 ,p_commit => fnd_api.g_false
78 ,p_validation_level => fnd_api.g_valid_level_full
79 ,x_return_status => x_return_status
80 ,x_msg_data => x_msg_data
81 ,x_msg_count => x_msg_count
82 ,p_hierarchy_id => l_start_node_id
83 );
84
85 IF x_return_status <> fnd_api.g_ret_sts_success THEN
86 ozf_utility_pvt.write_conc_log (' Failed to delete existing levels for terr_id ' || l_start_node_id);
87 ---write all messages in the concurrent manager log
88 IF (x_msg_count > 0) THEN
89 FOR i IN 1 .. x_msg_count
90 LOOP
91 x_msg_data := fnd_msg_pub.get (i, fnd_api.g_false);
92 ozf_utility_pvt.write_conc_log (' delete_terr_levels returns error. Msg count='
93 || i
94 || '-'
95 || x_msg_data);
96 --DBMS_OUTPUT.put_line ( 'message :' || x_msg_data);
97 END LOOP;
98 END IF;
99 RAISE fnd_api.g_exc_unexpected_error;
100 END IF;
101 ELSE
102 CLOSE c_hier_exists; */
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 (
112 p_api_version => 1.0
113 ,p_init_msg_list => fnd_api.g_true
114 ,p_commit => fnd_api.g_false
115 ,p_validation_level => fnd_api.g_valid_level_full
116 ,x_return_status => x_return_status
117 ,x_msg_data => x_msg_data
118 ,x_msg_count => x_msg_count
119 ,p_start_node_id => p_start_node_id
120 );
121 -- END IF;
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 (-)
131
132 ozf_terr_levels_pvt.bulk_insert_terr_levels (
133 p_api_version => 1.0
134 ,p_init_msg_list => fnd_api.g_true
135 ,p_commit => fnd_api.g_false
136 ,p_validation_level => fnd_api.g_valid_level_full
137 ,x_return_status => x_return_status
138 ,x_msg_data => x_msg_data
139 ,x_msg_count => x_msg_count
140 );
141 END IF; -- IF p_start_node_id IS NOT NULL THEN
142
143 IF x_return_status <> fnd_api.g_ret_sts_success THEN
144 ozf_utility_pvt.write_conc_log (' Failed to insert levels for terr_id ' || l_start_node_id);
145 ---write all messages in the concurrent manager log
146 IF (x_msg_count > 0) THEN
147 FOR i IN 1 .. x_msg_count
148 LOOP
149 x_msg_data := fnd_msg_pub.get (i, fnd_api.g_false);
150 ozf_utility_pvt.write_conc_log (' insert_terr_levels returns error. Msg count='
151 || i
152 || '-'
153 || x_msg_data);
154 --DBMS_OUTPUT.put_line ( 'message :' || x_msg_data);
155 END LOOP;
156 END IF;
157 RAISE fnd_api.g_exc_unexpected_error;
158 END IF;
159
160 ozf_utility_pvt.write_conc_log ('Import Territory Hierarchy: SUCCESS');
161 EXCEPTION
162 WHEN OTHERS THEN
163 ozf_utility_pvt.write_conc_log ('Import Territory Hierarchy: EXCEPTION');
164 x_return_status := fnd_api.g_ret_sts_error;
165 fnd_msg_pub.count_and_get (
166 p_encoded=> fnd_api.g_false
167 ,p_count=> x_msg_count
168 ,p_data=> x_msg_data
169 );
170 END create_terr_hierarchy;
171
172
173 /*---------------------------------------------------------------------
174 -- PROCEDURE
175 --
176 --
177 -- HISTORY
178 -- 03/07/00 mpande Created.
179 -- this pacakage is called from the above create_terr_hier program
180 --------------------------------------------------------------------------*/
181 PROCEDURE insert_terr_levels (
182 p_api_version IN NUMBER
183 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
184 ,p_commit IN VARCHAR2 := fnd_api.g_false
185 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
186 ,x_return_status OUT NOCOPY VARCHAR2
187 ,x_msg_data OUT NOCOPY VARCHAR2
188 ,x_msg_count OUT NOCOPY NUMBER
189 ,p_start_node_id IN NUMBER
190 ) IS
191 l_api_name CONSTANT VARCHAR2 (30) := 'Insert_terr_levels';
192 l_api_version CONSTANT NUMBER := 1.0;
193 l_org_id NUMBER;
194 l_insert_count NUMBER;
195 l_terr_level_id NUMBER;
196 l_terr_level NUMBER;
197 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
198 || '.'
199 || l_api_name;
200 l_terr_id NUMBER;
201 l_terr_type_id NUMBER;
202
203
204 -- Cursor for getting the org_id
205
206 CURSOR org_csr IS
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
216 CURSOR c_terr_id (l_terr_id IN NUMBER) IS
217 SELECT terr_id
218 ,territory_type_id
219 FROM jtf_terr_all jtf
220 WHERE jtf.terr_id = l_terr_id
221 AND jtf.parent_territory_id = 1;
222
223
224 -- cursor to check territory type exists for each territory
225 CURSOR c_terr_type (l_terr_id IN NUMBER) IS
226 SELECT territory_type_id
227 FROM jtf_terr_all jtf
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
237 -- cursor to import territories
238 CURSOR c_get_terr_levels (l_start_node_id NUMBER) IS
239 SELECT DISTINCT creation_date
240 ,created_by
241 ,last_update_date
242 ,last_updated_by
243 ,last_update_login
244 ,program_application_id
245 ,program_id
246 ,program_update_date
247 ,request_id
248 ,territory_type_id
249 ,TO_NUMBER (LEVEL) level_depth
250 ,attribute_category
251 ,attribute1
252 ,attribute2
253 ,attribute3
254 ,attribute4
255 ,attribute5
256 ,attribute6
257 ,attribute7
258 ,attribute8
259 ,attribute9
260 ,attribute10
261 ,attribute11
262 ,attribute12
263 ,attribute13
264 ,attribute14
265 ,attribute15
266 ,org_id
267 ,terr_id
268 ,parent_territory_id
269 -- Bug # 5723438 fixed by ateotia (+)
270 ,end_date_active
271 ,name
272 ,enabled_flag
273 -- Bug # 5723438 fixed by ateotia (-)
274 FROM jtf_terr_all
275
276 -- 07/13/2001 mpande removed the where condition instead put a error message so that the user sets the
277 -- territory type properly
278 -- WHERE TERRITORY_TYPE_ID is not null
279 CONNECT BY parent_territory_id = PRIOR terr_id
280 START WITH terr_id = l_start_node_id;
281 BEGIN
282 SAVEPOINT insert_terr_levels;
283
284 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
285 RAISE fnd_api.g_exc_unexpected_error;
286 END IF;
287
288 -- Initialize message list if p_init_msg_list is set to TRUE.
289 IF fnd_api.to_boolean (p_init_msg_list) THEN
290 fnd_msg_pub.initialize;
291 END IF;
292
293 -- Initialize API return status to success
294 x_return_status := fnd_api.g_ret_sts_success;
295
296 ozf_utility_pvt.write_conc_log('**********Start of Hierarchy Insert*******' );
297 ozf_utility_pvt.write_conc_log('**********Territory Details *******' );
298
299 -- API body
300 -- check this for each record
301 OPEN c_terr_id (p_start_node_id);
302 FETCH c_terr_id INTO l_terr_id, l_terr_type_id;
303 ozf_utility_pvt.write_conc_log('l_terr_id:=' || l_terr_id );
304 ozf_utility_pvt.write_conc_log('l_terr_type_id:=' || l_terr_type_id );
305 CLOSE c_terr_id;
306
307 IF l_terr_id IS NULL THEN
308 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
309 fnd_message.set_name ('OZF', 'OZF_TERR_ID_NOT_FOUND');
310 fnd_msg_pub.ADD;
311 END IF;
312
313 RAISE fnd_api.g_exc_error;
314 END IF;
315
316 IF l_terr_type_id IS NULL THEN
317 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
318 fnd_message.set_name ('OZF', 'OZF_TERR_TYPE_ID_NOT_FOUND');
319 fnd_msg_pub.ADD;
320 END IF;
321
322 RAISE fnd_api.g_exc_error;
323 END IF;
324
325
326 ------------check for data integrity---------------------------
327 FOR l_terr_level_rec IN c_get_terr_levels (p_start_node_id)
328 LOOP
329 -- check the territory type exists for each record -- 07/13/2001 mpande
330 -- initialize the variable
331 l_terr_type_id := NULL;
332 OPEN c_terr_type (l_terr_level_rec.terr_id);
333 FETCH c_terr_type INTO l_terr_type_id;
334 CLOSE c_terr_type;
335
336 IF l_terr_type_id IS NULL THEN
337 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
338 fnd_message.set_name ('OZF', 'OZF_TERR_TYPE_ID_NOT_FOUND');
339 fnd_msg_pub.ADD;
340 END IF;
341
342 RAISE fnd_api.g_exc_error;
343 END IF;
344
345 -- Record level validation
346 ozf_utility_pvt.debug_message ( l_full_name
347 || ': begin');
348
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
357 --DBMS_OUTPUT.put_line ( 'TT:= '|| l_terr_level_rec.territory_type_id || 'LD = ' || l_terr_level_rec.level_depth );
354 WHERE heirarchy_id = l_terr_level_rec.terr_id;
355 END IF;
356
358 -- 07/13/2001 mpande check that in the same hierarchy , terr_type do not appear in 2 diifernet levels
359 l_terr_level := NULL;
360 OPEN c_level_value (p_start_node_id, l_terr_level_rec.territory_type_id);
361 FETCH c_level_value INTO l_terr_level;
362 CLOSE c_level_value;
363
364 --DBMS_OUTPUT.put_line (l_terr_level);
365
366 -- Bug # 5723438 fixed by ateotia (+)
367 -- Commented not to check for level_depth while insertion
368 /* IF l_terr_level IS NOT NULL
369 AND l_terr_level <> l_terr_level_rec.level_depth THEN
370 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
371 fnd_message.set_name ('OZF', 'OZF_TERR_TYPE_DUPLICATE_RECORD');
372 fnd_msg_pub.ADD;
373 END IF;
374
375 RAISE fnd_api.g_exc_error;
376 END IF; */
377 -- Bug # 5723438 fixed by ateotia (-)
378
379 OPEN c_get_seq;
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
389 ,last_updated_by
390 ,last_update_login
391 ,program_application_id
392 ,program_id
393 ,program_update_date
394 ,request_id
395 ,terr_type_id
396 ,level_depth
397 ,attribute1
398 ,attribute2
399 ,attribute3
400 ,attribute4
401 ,attribute5
402 ,attribute6
403 ,attribute7
404 ,attribute8
405 ,attribute9
406 ,attribute10
407 ,attribute11
408 ,attribute12
409 ,attribute13
410 ,attribute14
411 ,attribute15
412 ,org_id
413 ,territory_id
414 ,parent_territory_id
415 ,object_version_number
416 ,heirarchy_id
417 -- Bug # 5723438 fixed by ateotia (+)
418 ,hierarchy_name
419 ,end_date_active
420 ,enabled_flag
421 -- Bug # 5723438 fixed by ateotia (-)
422 )
423 VALUES (l_terr_level_id
424 ,SYSDATE
425 ,fnd_global.user_id
426 ,SYSDATE
427 ,fnd_global.user_id
428 ,fnd_global.conc_login_id
429 ,l_terr_level_rec.program_application_id
430 ,l_terr_level_rec.program_id
431 ,l_terr_level_rec.program_update_date
432 ,l_terr_level_rec.request_id
433 ,l_terr_level_rec.territory_type_id
434 ,l_terr_level_rec.level_depth
435 ,l_terr_level_rec.attribute1
436 ,l_terr_level_rec.attribute2
437 ,l_terr_level_rec.attribute3
438 ,l_terr_level_rec.attribute4
439 ,l_terr_level_rec.attribute5
440 ,l_terr_level_rec.attribute6
441 ,l_terr_level_rec.attribute7
442 ,l_terr_level_rec.attribute8
443 ,l_terr_level_rec.attribute9
444 ,l_terr_level_rec.attribute10
445 ,l_terr_level_rec.attribute11
446 ,l_terr_level_rec.attribute12
447 ,l_terr_level_rec.attribute13
448 ,l_terr_level_rec.attribute14
449 ,l_terr_level_rec.attribute15
450 ,l_org_id
451 ,l_terr_level_rec.terr_id
452 ,l_terr_level_rec.parent_territory_id
453 ,1
454 ,p_start_node_id
455 -- Bug # 5723438 fixed by ateotia (+)
456 ,l_terr_level_rec.name
457 ,l_terr_level_rec.end_date_active
458 ,l_terr_level_rec.enabled_flag
459 -- Bug # 5723438 fixed by ateotia (-)
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 (-)
469
470 IF SQL%FOUND THEN
471 l_insert_count := SQL%ROWCOUNT;
472 x_return_status := fnd_api.g_ret_sts_success;
473 END IF;
474
475 x_return_status := fnd_api.g_ret_sts_success;
476 END LOOP;
477
478 IF fnd_api.to_boolean (p_commit)
479 AND x_return_status = fnd_api.g_ret_sts_success THEN
480 COMMIT;
481 END IF;
482
483 fnd_msg_pub.count_and_get (
484 p_encoded=> fnd_api.g_false
485 ,p_count=> x_msg_count
486 ,p_data=> x_msg_data
490 EXCEPTION
487 );
488 ozf_utility_pvt.debug_message ( l_full_name
489 || ': end');
491 WHEN fnd_api.g_exc_error THEN
492 ROLLBACK TO insert_terr_levels;
493 x_return_status := fnd_api.g_ret_sts_error;
494 fnd_msg_pub.count_and_get (
495 p_encoded=> fnd_api.g_false
496 ,p_count=> x_msg_count
497 ,p_data=> x_msg_data
498 );
499 WHEN fnd_api.g_exc_unexpected_error THEN
500 ROLLBACK TO insert_terr_levels;
501 x_return_status := fnd_api.g_ret_sts_unexp_error;
502 fnd_msg_pub.count_and_get (
503 p_encoded=> fnd_api.g_false
504 ,p_count=> x_msg_count
505 ,p_data=> x_msg_data
506 );
507 WHEN OTHERS THEN
508 ROLLBACK TO insert_terr_levels;
509 x_return_status := fnd_api.g_ret_sts_unexp_error;
510
511 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
512 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
513 END IF;
514
515 fnd_msg_pub.count_and_get (
516 p_encoded=> fnd_api.g_false
517 ,p_count=> x_msg_count
518 ,p_data=> x_msg_data
519 );
520 END insert_terr_levels;
521
522
523 /*---------------------------------------------------------------------
524 -- PROCEDURE
525 --
526 --
527 -- HISTORY
528 -- 09/21/05 yzhao Created.
529 -- this pacakage is called from the above create_terr_hier program
530 -- to import all hierarchy levels defined under 'Trade Management'
531 --------------------------------------------------------------------------*/
532 PROCEDURE bulk_insert_terr_levels (
533 p_api_version IN NUMBER
534 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
535 ,p_commit IN VARCHAR2 := fnd_api.g_false
536 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
537 ,x_return_status OUT NOCOPY VARCHAR2
538 ,x_msg_data OUT NOCOPY VARCHAR2
539 ,x_msg_count OUT NOCOPY NUMBER
540 ) IS
541 l_api_name CONSTANT VARCHAR2 (30) := 'bulk_insert_terr_levels';
542 l_api_version CONSTANT NUMBER := 1.0;
543 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
544 || '.'
545 || l_api_name;
546
547 l_terrIDTbl terrIdTbl;
548
549 -- same as value set OZF_SRS_TERR_HIER_START_NODE used for concurrent program start node parameter
550 CURSOR c_get_all_root_nodes IS
551 SELECT JTR.TERR_ID
552 FROM JTF_TERR_ALL JTR , JTF_TERR_USGS_ALL JTU , JTF_SOURCES_ALL JSE
553 WHERE JTU.TERR_ID = JTR.TERR_ID
554 AND JTU.SOURCE_ID = JSE.SOURCE_ID
555 AND JTU.SOURCE_ID = -1003
556 AND JTR.PARENT_TERRITORY_ID = 1
557 AND NVL(JTR.ORG_ID, -99) = NVL(JTU.ORG_ID, NVL(JTR.ORG_ID, -99))
558 AND JSE.ORG_ID IS NULL
559 AND JTR.TERRITORY_TYPE_ID IS NOT NULL
560 AND NVL(JTR.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ' , NULL, SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99)) =
561 NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ', NULL, SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99);
562
563 BEGIN
564 OPEN c_get_all_root_nodes;
565 LOOP
566 FETCH c_get_all_root_nodes BULK COLLECT INTO l_terrIdTbl LIMIT g_bulk_limit;
567 FOR i IN NVL(l_terrIdTbl.FIRST, 1) .. NVL(l_terrIdTbl.LAST, 0) LOOP
568 ozf_terr_levels_pvt.insert_terr_levels (
569 p_api_version => 1.0
570 ,p_init_msg_list => fnd_api.g_true
571 ,p_commit => fnd_api.g_false
572 ,p_validation_level => fnd_api.g_valid_level_full
573 ,x_return_status => x_return_status
574 ,x_msg_data => x_msg_data
575 ,x_msg_count => x_msg_count
576 ,p_start_node_id => l_terrIdTbl(i)
577 );
578 IF x_return_status <> fnd_api.g_ret_sts_success THEN
579 ozf_utility_pvt.write_conc_log(' /****** Failed to bulk insert level for hier id ' || l_terrIdTbl(i));
580 ELSE
581 ozf_utility_pvt.debug_message(' D: ' || l_api_name || ' successfully insert levels for terr id' || l_terrIdTbl(i));
582 END IF;
583 END LOOP; -- FOR i IN NVL(l_terrIdTbl.FIRST, 1) .. NVL(l_terrIdTbl.LAST, 0) LOOP
584
585 EXIT WHEN c_get_all_root_nodes%NOTFOUND;
586 END LOOP; -- bulk fetch loop
587 END bulk_insert_terr_levels;
588
589
590
591 /*---------------------------------------------------------------------
592 -- PROCEDURE
593 --
594 --
595 -- HISTORY
596 -- 03/07/00 mpande Created.
597 --------------------------------------------------------------------------*/
598
599 PROCEDURE delete_terr_levels (
600 p_api_version IN NUMBER
601 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
602 ,p_commit IN VARCHAR2 := fnd_api.g_false
603 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
604 ,x_return_status OUT NOCOPY VARCHAR2
605 ,x_msg_data OUT NOCOPY VARCHAR2
606 ,x_msg_count OUT NOCOPY NUMBER
607 ,p_hierarchy_id IN NUMBER
608 ) IS
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;
618 l_terr_count NUMBER;
619
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
629 SAVEPOINT delete_terr_levels;
630
631 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
632 RAISE fnd_api.g_exc_unexpected_error;
633 END IF;
634
635 -- Initialize message list if p_init_msg_list is set to TRUE.
636 IF fnd_api.to_boolean (p_init_msg_list) THEN
637 fnd_msg_pub.initialize;
638 END IF;
639
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
658 COMMIT;
659 END IF;
660 */
661
662 fnd_msg_pub.count_and_get (
663 p_encoded=> fnd_api.g_false
664 ,p_count=> x_msg_count
665 ,p_data=> x_msg_data
666 );
667 ozf_utility_pvt.debug_message ( l_full_name
668 || ': end');
669 EXCEPTION
670 WHEN fnd_api.g_exc_error THEN
671 ROLLBACK TO delete_terr_levels;
672 x_return_status := fnd_api.g_ret_sts_error;
673 fnd_msg_pub.count_and_get (
674 p_encoded=> fnd_api.g_false
675 ,p_count=> x_msg_count
676 ,p_data=> x_msg_data
677 );
678 WHEN fnd_api.g_exc_unexpected_error THEN
679 ROLLBACK TO delete_terr_levels;
680 x_return_status := fnd_api.g_ret_sts_unexp_error;
681 fnd_msg_pub.count_and_get (
682 p_encoded=> fnd_api.g_false
683 ,p_count=> x_msg_count
684 ,p_data=> x_msg_data
685 );
686 WHEN OTHERS THEN
687 ROLLBACK TO delete_terr_levels;
688 x_return_status := fnd_api.g_ret_sts_unexp_error;
689
690 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
691 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
692 END IF;
693
694 fnd_msg_pub.count_and_get (
695 p_encoded=> fnd_api.g_false
696 ,p_count=> x_msg_count
697 ,p_data=> x_msg_data
698 );
699 END delete_terr_levels;
700 END;