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;
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);
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);
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: );
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;
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:
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;
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:
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;
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:
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;
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:
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; */
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
487: );
490: EXCEPTION
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: );
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 (
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
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: );
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;
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: );
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: );
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 (
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
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: );