[Home] [Help]
63: -- parameters :
64:
65: -- in:
66: -- p_api_version_number in number required
67: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
68: -- p_commit in varchar2 optional --default = fnd_api.g_false
69: -- p_TerrType_rec in TerrType_rec_type required --default = g_miss_TerrType_rec,
70: -- p_TerrTypequal_tbl in TerrTypequal_tbl_type required --default = g_miss_TerrTypequal_rec,
71: -- p_TerrTypeusgs_tbl in TerrTypeusgs_rec_type required --default = g_miss_tersrc_rec
64:
65: -- in:
66: -- p_api_version_number in number required
67: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
68: -- p_commit in varchar2 optional --default = fnd_api.g_false
69: -- p_TerrType_rec in TerrType_rec_type required --default = g_miss_TerrType_rec,
70: -- p_TerrTypequal_tbl in TerrTypequal_tbl_type required --default = g_miss_TerrTypequal_rec,
71: -- p_TerrTypeusgs_tbl in TerrTypeusgs_rec_type required --default = g_miss_tersrc_rec
72: -- p_TypequalTypeusgs_tbl in TypeQualTypeUsgs_tbl_type required --default = g_miss_tersrc_tbl,
90: --
91: -- end of comments
92: PROCEDURE create_terrtype (
93: p_api_version_number IN NUMBER,
94: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
95: p_commit IN VARCHAR2 := fnd_api.g_false,
96: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
97: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
98: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
91: -- end of comments
92: PROCEDURE create_terrtype (
93: p_api_version_number IN NUMBER,
94: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
95: p_commit IN VARCHAR2 := fnd_api.g_false,
96: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
97: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
98: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
99: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
92: PROCEDURE create_terrtype (
93: p_api_version_number IN NUMBER,
94: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
95: p_commit IN VARCHAR2 := fnd_api.g_false,
96: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
97: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
98: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
99: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
100: p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
132: -- Standard Start of API savepoint
133: SAVEPOINT create_terrtype_pvt;
134:
135: -- Standard call to check for call compatibility.
136: IF NOT fnd_api.compatible_api_call (
137: l_api_version_number,
138: p_api_version_number,
139: l_api_name,
140: g_pkg_name
139: l_api_name,
140: g_pkg_name
141: )
142: THEN
143: RAISE fnd_api.g_exc_unexpected_error;
144: END IF;
145:
146: -- Initialize message list if p_init_msg_list is set to TRUE.
147: IF fnd_api.to_boolean (p_init_msg_list)
143: RAISE fnd_api.g_exc_unexpected_error;
144: END IF;
145:
146: -- Initialize message list if p_init_msg_list is set to TRUE.
147: IF fnd_api.to_boolean (p_init_msg_list)
148: THEN
149: fnd_msg_pub.initialize;
150: END IF;
151:
157: fnd_msg_pub.add;
158: END IF;
159:
160: -- Initialize API return status to success
161: x_return_status := fnd_api.g_ret_sts_success;
162: --
163: -- API body
164: --
165: --
190: x_terrtypeusgs_out_tbl := l_terrtypeusgs_out_tbl;
191: x_typequaltypeusgs_out_tbl := l_typequaltypeusgs_out_tbl;
192: x_return_status := l_return_status;
193:
194: IF l_return_status <> fnd_api.g_ret_sts_success
195: THEN
196: RAISE fnd_api.g_exc_error;
197: END IF;
198:
192: x_return_status := l_return_status;
193:
194: IF l_return_status <> fnd_api.g_ret_sts_success
195: THEN
196: RAISE fnd_api.g_exc_error;
197: END IF;
198:
199: --dbms_ourtput.put_line('create_terrtype PVT: Before Calling Create_TerrType_Qualifier PVT');
200: create_terrtype_qualifier (
211: );
212: x_terrtypequal_out_tbl := l_terrtypequal_out_tbl;
213: x_return_status := l_return_status;
214:
215: IF l_return_status <> fnd_api.g_ret_sts_success
216: THEN
217: RAISE fnd_api.g_exc_error;
218: END IF;
219:
213: x_return_status := l_return_status;
214:
215: IF l_return_status <> fnd_api.g_ret_sts_success
216: THEN
217: RAISE fnd_api.g_exc_error;
218: END IF;
219:
220: -- Debug Message
221: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
227:
228: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
229:
230: -- Standard check for p_commit
231: IF fnd_api.to_boolean (p_commit)
232: THEN
233: COMMIT WORK;
234: END IF;
235: --dbms_ourtput.put_line('create_terrtype PVT: Exiting API');
234: END IF;
235: --dbms_ourtput.put_line('create_terrtype PVT: Exiting API');
236: EXCEPTION
237: --
238: WHEN fnd_api.g_exc_error
239: THEN
240: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_ERROR');
241: ROLLBACK TO create_terrtype_pvt;
242: x_return_status := fnd_api.g_ret_sts_error;
236: EXCEPTION
237: --
238: WHEN fnd_api.g_exc_error
239: THEN
240: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_ERROR');
241: ROLLBACK TO create_terrtype_pvt;
242: x_return_status := fnd_api.g_ret_sts_error;
243: fnd_msg_pub.count_and_get (
244: p_count => x_msg_count,
238: WHEN fnd_api.g_exc_error
239: THEN
240: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_ERROR');
241: ROLLBACK TO create_terrtype_pvt;
242: x_return_status := fnd_api.g_ret_sts_error;
243: fnd_msg_pub.count_and_get (
244: p_count => x_msg_count,
245: p_data => x_msg_data
246: );
243: fnd_msg_pub.count_and_get (
244: p_count => x_msg_count,
245: p_data => x_msg_data
246: );
247: WHEN fnd_api.g_exc_unexpected_error
248: THEN
249: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
250: ROLLBACK TO create_terrtype_pvt;
251: x_return_status := fnd_api.g_ret_sts_unexp_error;
245: p_data => x_msg_data
246: );
247: WHEN fnd_api.g_exc_unexpected_error
248: THEN
249: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
250: ROLLBACK TO create_terrtype_pvt;
251: x_return_status := fnd_api.g_ret_sts_unexp_error;
252: fnd_msg_pub.count_and_get (
253: p_count => x_msg_count,
247: WHEN fnd_api.g_exc_unexpected_error
248: THEN
249: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
250: ROLLBACK TO create_terrtype_pvt;
251: x_return_status := fnd_api.g_ret_sts_unexp_error;
252: fnd_msg_pub.count_and_get (
253: p_count => x_msg_count,
254: p_data => x_msg_data
255: );
256: WHEN OTHERS
257: THEN
258: --dbms_ourtput.put_line('create_terrtype PVT: OTHERS - ' || SQLERRM);
259: ROLLBACK TO create_terrtype_pvt;
260: x_return_status := fnd_api.g_ret_sts_unexp_error;
261: fnd_msg_pub.count_and_get (
262: p_count => x_msg_count,
263: p_data => x_msg_data
264: );
276: -- parameters :
277:
278: -- in:
279: -- p_api_version_number in number required
280: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
281: -- p_commit in varchar2 optional --default = fnd_api.g_false
282: -- p_TerrType_rec in TerrType_rec_type required --default = g_miss_TerrType_rec,
283: -- p_TerrTypequal_tbl in TerrTypequal_tbl_type required --default = g_miss_TerrTypequal_rec,
284: -- p_TerrTypesrc_tbl in TerrTypesrc_rec_type required --default = g_miss_tersrc_rec
277:
278: -- in:
279: -- p_api_version_number in number required
280: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
281: -- p_commit in varchar2 optional --default = fnd_api.g_false
282: -- p_TerrType_rec in TerrType_rec_type required --default = g_miss_TerrType_rec,
283: -- p_TerrTypequal_tbl in TerrTypequal_tbl_type required --default = g_miss_TerrTypequal_rec,
284: -- p_TerrTypesrc_tbl in TerrTypesrc_rec_type required --default = g_miss_tersrc_rec
285: -- p_TerrTypeSrcType_tbl in TerrTypeSrcType_tbl_type required --default = g_miss_tersrc_tbl,
301: --
302: -- end of comments
303: PROCEDURE update_terrtype (
304: p_api_version_number IN NUMBER,
305: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
306: p_commit IN VARCHAR2 := fnd_api.g_false,
307: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
308: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
309: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
302: -- end of comments
303: PROCEDURE update_terrtype (
304: p_api_version_number IN NUMBER,
305: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
306: p_commit IN VARCHAR2 := fnd_api.g_false,
307: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
308: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
309: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
310: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
303: PROCEDURE update_terrtype (
304: p_api_version_number IN NUMBER,
305: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
306: p_commit IN VARCHAR2 := fnd_api.g_false,
307: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
308: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
309: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
310: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
311: p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
327: -- Standard Start of API savepoint
328: SAVEPOINT update_terrtype_pvt;
329:
330: -- Standard call to check for call compatibility.
331: IF NOT fnd_api.compatible_api_call (
332: l_api_version_number,
333: p_api_version_number,
334: l_api_name,
335: g_pkg_name
334: l_api_name,
335: g_pkg_name
336: )
337: THEN
338: RAISE fnd_api.g_exc_unexpected_error;
339: END IF;
340:
341: -- Initialize message list if p_init_msg_list is set to TRUE.
342: IF fnd_api.to_boolean (p_init_msg_list)
338: RAISE fnd_api.g_exc_unexpected_error;
339: END IF;
340:
341: -- Initialize message list if p_init_msg_list is set to TRUE.
342: IF fnd_api.to_boolean (p_init_msg_list)
343: THEN
344: fnd_msg_pub.initialize;
345: END IF;
346:
352: fnd_msg_pub.add;
353: END IF;
354:
355: -- Initialize API return status to success
356: x_return_status := fnd_api.g_ret_sts_success;
357: --
358: -- API body
359: --
360: --dbms_ourtput.put_line('Update_Terrtype PVT: Before Calling Update_TerrType_Record');
370: x_terrtype_out_rec => x_terrtype_out_rec
371: );
372:
373: --
374: IF l_return_status <> fnd_api.g_ret_sts_success
375: THEN
376: RAISE fnd_api.g_exc_error;
377: END IF;
378:
372:
373: --
374: IF l_return_status <> fnd_api.g_ret_sts_success
375: THEN
376: RAISE fnd_api.g_exc_error;
377: END IF;
378:
379: --
380: --dbms_ourtput.put_line('Update_Terrtype PVT: Before Calling Update_TerrTypeQualType_Usage');
390: x_typequaltypeusgs_out_tbl => x_typequaltypeusgs_out_tbl
391: );
392:
393: --
394: IF l_return_status <> fnd_api.g_ret_sts_success
395: THEN
396: RAISE fnd_api.g_exc_error;
397: END IF;
398:
392:
393: --
394: IF l_return_status <> fnd_api.g_ret_sts_success
395: THEN
396: RAISE fnd_api.g_exc_error;
397: END IF;
398:
399: --
400: --dbms_ourtput.put_line('Update_Terrtype PVT: Before Calling Update_TerrType_Usages');
410: x_terrtypeusgs_out_tbl => x_terrtypeusgs_out_tbl
411: );
412:
413: --
414: IF l_return_status <> fnd_api.g_ret_sts_success
415: THEN
416: RAISE fnd_api.g_exc_error;
417: END IF;
418:
412:
413: --
414: IF l_return_status <> fnd_api.g_ret_sts_success
415: THEN
416: RAISE fnd_api.g_exc_error;
417: END IF;
418:
419: --
420: --dbms_ourtput.put_line('Update_Terrtype PVT: Before Calling Update_TerrType_Qualifier');
430: x_terrtypequal_out_tbl => x_terrtypequal_out_tbl
431: );
432:
433: --
434: IF l_return_status <> fnd_api.g_ret_sts_success
435: THEN
436: RAISE fnd_api.g_exc_error;
437: END IF;
438:
432:
433: --
434: IF l_return_status <> fnd_api.g_ret_sts_success
435: THEN
436: RAISE fnd_api.g_exc_error;
437: END IF;
438:
439: -- Debug Message
440: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
446:
447: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
448:
449: -- Standard check for p_commit
450: IF fnd_api.to_boolean (p_commit)
451: THEN
452: COMMIT WORK;
453: END IF;
454: --dbms_ourtput.put_line('Update_Terrtype PVT: Exiting API');
454: --dbms_ourtput.put_line('Update_Terrtype PVT: Exiting API');
455: --
456: EXCEPTION
457: --
458: WHEN fnd_api.g_exc_error
459: THEN
460: --dbms_ourtput.put_line('Update_Terrtype PVT: FND_API.G_EXC_ERROR');
461: ROLLBACK TO update_terrtype_pvt;
462: x_return_status := fnd_api.g_ret_sts_error;
456: EXCEPTION
457: --
458: WHEN fnd_api.g_exc_error
459: THEN
460: --dbms_ourtput.put_line('Update_Terrtype PVT: FND_API.G_EXC_ERROR');
461: ROLLBACK TO update_terrtype_pvt;
462: x_return_status := fnd_api.g_ret_sts_error;
463: fnd_msg_pub.count_and_get (
464: p_count => x_msg_count,
458: WHEN fnd_api.g_exc_error
459: THEN
460: --dbms_ourtput.put_line('Update_Terrtype PVT: FND_API.G_EXC_ERROR');
461: ROLLBACK TO update_terrtype_pvt;
462: x_return_status := fnd_api.g_ret_sts_error;
463: fnd_msg_pub.count_and_get (
464: p_count => x_msg_count,
465: p_data => x_msg_data
466: );
463: fnd_msg_pub.count_and_get (
464: p_count => x_msg_count,
465: p_data => x_msg_data
466: );
467: WHEN fnd_api.g_exc_unexpected_error
468: THEN
469: --dbms_ourtput.put_line('Update_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
470: ROLLBACK TO update_terrtype_pvt;
471: x_return_status := fnd_api.g_ret_sts_unexp_error;
465: p_data => x_msg_data
466: );
467: WHEN fnd_api.g_exc_unexpected_error
468: THEN
469: --dbms_ourtput.put_line('Update_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
470: ROLLBACK TO update_terrtype_pvt;
471: x_return_status := fnd_api.g_ret_sts_unexp_error;
472: fnd_msg_pub.count_and_get (
473: p_count => x_msg_count,
467: WHEN fnd_api.g_exc_unexpected_error
468: THEN
469: --dbms_ourtput.put_line('Update_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
470: ROLLBACK TO update_terrtype_pvt;
471: x_return_status := fnd_api.g_ret_sts_unexp_error;
472: fnd_msg_pub.count_and_get (
473: p_count => x_msg_count,
474: p_data => x_msg_data
475: );
476: WHEN OTHERS
477: THEN
478: --dbms_ourtput.put_line('Update_Terrtype PVT: OTHERS - ' || SQLERRM);
479: ROLLBACK TO update_terrtype_pvt;
480: x_return_status := fnd_api.g_ret_sts_unexp_error;
481: fnd_msg_pub.count_and_get (
482: p_count => x_msg_count,
483: p_data => x_msg_data
484: );
496: -- parameters :
497: --
498: -- in:
499: -- p_api_version_number in number required
500: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
501: -- p_commit in varchar2 optional --default = fnd_api.g_false
502: -- p_TerrType_id in number
503: --
504: -- out:
497: --
498: -- in:
499: -- p_api_version_number in number required
500: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
501: -- p_commit in varchar2 optional --default = fnd_api.g_false
502: -- p_TerrType_id in number
503: --
504: -- out:
505: -- p_return_status out varchar2(1)
516: --
517: -- end of comments
518: PROCEDURE delete_terrtype (
519: p_api_version_number IN NUMBER,
520: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
521: p_commit IN VARCHAR2 := fnd_api.g_false,
522: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
523: p_terrtype_id IN NUMBER,
524: x_return_status OUT NOCOPY VARCHAR2,
517: -- end of comments
518: PROCEDURE delete_terrtype (
519: p_api_version_number IN NUMBER,
520: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
521: p_commit IN VARCHAR2 := fnd_api.g_false,
522: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
523: p_terrtype_id IN NUMBER,
524: x_return_status OUT NOCOPY VARCHAR2,
525: x_msg_count OUT NOCOPY NUMBER,
518: PROCEDURE delete_terrtype (
519: p_api_version_number IN NUMBER,
520: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
521: p_commit IN VARCHAR2 := fnd_api.g_false,
522: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
523: p_terrtype_id IN NUMBER,
524: x_return_status OUT NOCOPY VARCHAR2,
525: x_msg_count OUT NOCOPY NUMBER,
526: x_msg_data OUT NOCOPY VARCHAR2
566: -- Standard Start of API savepoint
567: SAVEPOINT delete_territory_pvt;
568:
569: -- Standard call to check for call compatibility.
570: IF NOT fnd_api.compatible_api_call (
571: l_api_version_number,
572: p_api_version_number,
573: l_api_name,
574: g_pkg_name
573: l_api_name,
574: g_pkg_name
575: )
576: THEN
577: RAISE fnd_api.g_exc_unexpected_error;
578: END IF;
579:
580: -- Initialize message list if p_init_msg_list is set to TRUE.
581: IF fnd_api.to_boolean (p_init_msg_list)
577: RAISE fnd_api.g_exc_unexpected_error;
578: END IF;
579:
580: -- Initialize message list if p_init_msg_list is set to TRUE.
581: IF fnd_api.to_boolean (p_init_msg_list)
582: THEN
583: fnd_msg_pub.initialize;
584: END IF;
585:
591: fnd_msg_pub.add;
592: END IF;
593:
594: -- Initialize API return status to success
595: x_return_status := fnd_api.g_ret_sts_success;
596:
597: --
598: -- API body
599: --
598: -- API body
599: --
600: --Is_Territory_Deletable(....);
601: --
602: --IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
603: -- RAISE FND_API.G_EXC_ERROR;
604: --END IF;
605:
606: --dbms_ourtput.put_line('Delete_Terrtype PVT: Before opening C_GetTerrTypeQual');
599: --
600: --Is_Territory_Deletable(....);
601: --
602: --IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
603: -- RAISE FND_API.G_EXC_ERROR;
604: --END IF;
605:
606: --dbms_ourtput.put_line('Delete_Terrtype PVT: Before opening C_GetTerrTypeQual');
607: FOR c IN c_getterrtypequal
616: x_msg_count => x_msg_count,
617: x_msg_data => x_msg_data
618: );
619:
620: IF x_return_status <> fnd_api.g_ret_sts_success
621: THEN
622: RAISE fnd_api.g_exc_error;
623: END IF;
624: --
618: );
619:
620: IF x_return_status <> fnd_api.g_ret_sts_success
621: THEN
622: RAISE fnd_api.g_exc_error;
623: END IF;
624: --
625:
626: END LOOP;
640: x_msg_count => x_msg_count,
641: x_msg_data => x_msg_data
642: );
643:
644: IF x_return_status <> fnd_api.g_ret_sts_success
645: THEN
646: RAISE fnd_api.g_exc_error;
647: END IF;
648: --
642: );
643:
644: IF x_return_status <> fnd_api.g_ret_sts_success
645: THEN
646: RAISE fnd_api.g_exc_error;
647: END IF;
648: --
649:
650: END LOOP;
664: x_msg_count => x_msg_count,
665: x_msg_data => x_msg_data
666: );
667:
668: IF x_return_status <> fnd_api.g_ret_sts_success
669: THEN
670: RAISE fnd_api.g_exc_error;
671: END IF;
672: --
666: );
667:
668: IF x_return_status <> fnd_api.g_ret_sts_success
669: THEN
670: RAISE fnd_api.g_exc_error;
671: END IF;
672: --
673:
674: END LOOP;
683: x_msg_count => x_msg_count,
684: x_msg_data => x_msg_data
685: );
686:
687: IF x_return_status <> fnd_api.g_ret_sts_success
688: THEN
689: RAISE fnd_api.g_exc_error;
690: END IF;
691:
685: );
686:
687: IF x_return_status <> fnd_api.g_ret_sts_success
688: THEN
689: RAISE fnd_api.g_exc_error;
690: END IF;
691:
692: -- Debug Message
693: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
699:
700: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
701:
702: -- Standard check for p_commit
703: IF fnd_api.to_boolean (p_commit)
704: THEN
705: COMMIT WORK;
706: END IF;
707: --dbms_ourtput.put_line('Delete_Terrtype PVT: Exiting API');
706: END IF;
707: --dbms_ourtput.put_line('Delete_Terrtype PVT: Exiting API');
708: EXCEPTION
709: --
710: WHEN fnd_api.g_exc_error
711: THEN
712: --dbms_ourtput.put_line('Delete_Terrtype PVT: FND_API.G_EXC_ERROR');
713: ROLLBACK TO delete_territory_pvt;
714: x_return_status := fnd_api.g_ret_sts_error;
708: EXCEPTION
709: --
710: WHEN fnd_api.g_exc_error
711: THEN
712: --dbms_ourtput.put_line('Delete_Terrtype PVT: FND_API.G_EXC_ERROR');
713: ROLLBACK TO delete_territory_pvt;
714: x_return_status := fnd_api.g_ret_sts_error;
715: fnd_msg_pub.count_and_get (
716: p_count => x_msg_count,
710: WHEN fnd_api.g_exc_error
711: THEN
712: --dbms_ourtput.put_line('Delete_Terrtype PVT: FND_API.G_EXC_ERROR');
713: ROLLBACK TO delete_territory_pvt;
714: x_return_status := fnd_api.g_ret_sts_error;
715: fnd_msg_pub.count_and_get (
716: p_count => x_msg_count,
717: p_data => x_msg_data
718: );
715: fnd_msg_pub.count_and_get (
716: p_count => x_msg_count,
717: p_data => x_msg_data
718: );
719: WHEN fnd_api.g_exc_unexpected_error
720: THEN
721: --dbms_ourtput.put_line('Delete_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
722: ROLLBACK TO delete_territory_pvt;
723: x_return_status := fnd_api.g_ret_sts_unexp_error;
717: p_data => x_msg_data
718: );
719: WHEN fnd_api.g_exc_unexpected_error
720: THEN
721: --dbms_ourtput.put_line('Delete_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
722: ROLLBACK TO delete_territory_pvt;
723: x_return_status := fnd_api.g_ret_sts_unexp_error;
724: fnd_msg_pub.count_and_get (
725: p_count => x_msg_count,
719: WHEN fnd_api.g_exc_unexpected_error
720: THEN
721: --dbms_ourtput.put_line('Delete_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
722: ROLLBACK TO delete_territory_pvt;
723: x_return_status := fnd_api.g_ret_sts_unexp_error;
724: fnd_msg_pub.count_and_get (
725: p_count => x_msg_count,
726: p_data => x_msg_data
727: );
728: WHEN OTHERS
729: THEN
730: --dbms_ourtput.put_line('Delete_Terrtype PVT: OTHERS - ' || SQLERRM);
731: ROLLBACK TO delete_territory_pvt;
732: x_return_status := fnd_api.g_ret_sts_unexp_error;
733: fnd_msg_pub.count_and_get (
734: p_count => x_msg_count,
735: p_data => x_msg_data
736: );
746: -- parameters :
747: --
748: -- in:
749: -- p_api_version_number in number required
750: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
751: -- p_commit in varchar2 optional --default = fnd_api.g_false
752: -- p_TerrType_id in number
753: --
754: -- out:
747: --
748: -- in:
749: -- p_api_version_number in number required
750: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
751: -- p_commit in varchar2 optional --default = fnd_api.g_false
752: -- p_TerrType_id in number
753: --
754: -- out:
755: -- p_return_status out varchar2(1)
766: --
767: -- end of comments
768: PROCEDURE deactivate_terrtype (
769: p_api_version_number IN NUMBER,
770: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
771: p_commit IN VARCHAR2 := fnd_api.g_false,
772: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
773: p_terrtype_id IN NUMBER,
774: x_return_status OUT NOCOPY VARCHAR2,
767: -- end of comments
768: PROCEDURE deactivate_terrtype (
769: p_api_version_number IN NUMBER,
770: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
771: p_commit IN VARCHAR2 := fnd_api.g_false,
772: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
773: p_terrtype_id IN NUMBER,
774: x_return_status OUT NOCOPY VARCHAR2,
775: x_msg_count OUT NOCOPY NUMBER,
768: PROCEDURE deactivate_terrtype (
769: p_api_version_number IN NUMBER,
770: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
771: p_commit IN VARCHAR2 := fnd_api.g_false,
772: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
773: p_terrtype_id IN NUMBER,
774: x_return_status OUT NOCOPY VARCHAR2,
775: x_msg_count OUT NOCOPY NUMBER,
776: x_msg_data OUT NOCOPY VARCHAR2
824: -- Standard Start of API savepoint
825: SAVEPOINT deactivate_territory_pvt;
826:
827: -- Standard call to check for call compatibility.
828: IF NOT fnd_api.compatible_api_call (
829: l_api_version_number,
830: p_api_version_number,
831: l_api_name,
832: g_pkg_name
831: l_api_name,
832: g_pkg_name
833: )
834: THEN
835: RAISE fnd_api.g_exc_unexpected_error;
836: END IF;
837:
838: -- Initialize message list if p_init_msg_list is set to TRUE.
839: IF fnd_api.to_boolean (p_init_msg_list)
835: RAISE fnd_api.g_exc_unexpected_error;
836: END IF;
837:
838: -- Initialize message list if p_init_msg_list is set to TRUE.
839: IF fnd_api.to_boolean (p_init_msg_list)
840: THEN
841: fnd_msg_pub.initialize;
842: END IF;
843:
849: fnd_msg_pub.add;
850: END IF;
851:
852: -- Initialize API return status to success
853: x_return_status := fnd_api.g_ret_sts_success;
854: --
855: -- API body
856: --
857: -- Initialize API return status to SUCCESS
854: --
855: -- API body
856: --
857: -- Initialize API return status to SUCCESS
858: x_return_status := fnd_api.g_ret_sts_success;
859: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: Before opening C_GetTerrType');
860: OPEN c_getterrtype (p_terrtype_id);
861: --
862: --dbms_ourtput.put_line('Update_TerrType_Record PVT:Before fetch');
902: fnd_message.set_token ('PK_ID', TO_CHAR (p_terrtype_id));
903: fnd_msg_pub.add;
904: END IF;
905:
906: RAISE fnd_api.g_exc_error;
907: END IF;
908:
909: CLOSE c_getterrtype;
910: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: Before Calling JTF_TERR_TYPES_PKG.Update_Row');
939: x_attribute14 => l_ref_terrtype_rec.attribute14,
940: x_attribute15 => l_ref_terrtype_rec.attribute15,
941: x_org_id => l_ref_terrtype_rec.org_id
942: );
943: x_return_status := fnd_api.g_ret_sts_success;
944:
945: -- Debug Message
946: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
947: THEN
952:
953: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
954:
955: -- Standard check for p_commit
956: IF fnd_api.to_boolean (p_commit)
957: THEN
958: COMMIT WORK;
959: END IF;
960: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: Exiting API');
958: COMMIT WORK;
959: END IF;
960: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: Exiting API');
961: EXCEPTION
962: WHEN fnd_api.g_exc_error
963: THEN
964: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: FND_API.G_EXC_ERROR');
965: ROLLBACK TO deactivate_territory_pvt;
966: x_return_status := fnd_api.g_ret_sts_error;
960: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: Exiting API');
961: EXCEPTION
962: WHEN fnd_api.g_exc_error
963: THEN
964: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: FND_API.G_EXC_ERROR');
965: ROLLBACK TO deactivate_territory_pvt;
966: x_return_status := fnd_api.g_ret_sts_error;
967: fnd_msg_pub.count_and_get (
968: p_count => x_msg_count,
962: WHEN fnd_api.g_exc_error
963: THEN
964: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: FND_API.G_EXC_ERROR');
965: ROLLBACK TO deactivate_territory_pvt;
966: x_return_status := fnd_api.g_ret_sts_error;
967: fnd_msg_pub.count_and_get (
968: p_count => x_msg_count,
969: p_data => x_msg_data
970: );
967: fnd_msg_pub.count_and_get (
968: p_count => x_msg_count,
969: p_data => x_msg_data
970: );
971: WHEN fnd_api.g_exc_unexpected_error
972: THEN
973: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
974: ROLLBACK TO deactivate_territory_pvt;
975: x_return_status := fnd_api.g_ret_sts_unexp_error;
969: p_data => x_msg_data
970: );
971: WHEN fnd_api.g_exc_unexpected_error
972: THEN
973: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
974: ROLLBACK TO deactivate_territory_pvt;
975: x_return_status := fnd_api.g_ret_sts_unexp_error;
976: fnd_msg_pub.count_and_get (
977: p_count => x_msg_count,
971: WHEN fnd_api.g_exc_unexpected_error
972: THEN
973: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
974: ROLLBACK TO deactivate_territory_pvt;
975: x_return_status := fnd_api.g_ret_sts_unexp_error;
976: fnd_msg_pub.count_and_get (
977: p_count => x_msg_count,
978: p_data => x_msg_data
979: );
980: WHEN OTHERS
981: THEN
982: --dbms_ourtput.put_line('De-Activate_Terrtype PVT: OTHERS - ' || SQLERRM);
983: ROLLBACK TO deactivate_territory_pvt;
984: x_return_status := fnd_api.g_ret_sts_unexp_error;
985:
986: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
987: THEN
988: fnd_msg_pub.add_exc_msg (
1002: -- parameters :
1003: --
1004: -- in:
1005: -- p_api_version_number in number required
1006: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
1007: -- p_commit in varchar2 optional --default = fnd_api.g_false
1008: -- p_TerrType_id in number
1009: -- p_TerrType_Name in varchar2
1010: -- p_Start_Date in date
1003: --
1004: -- in:
1005: -- p_api_version_number in number required
1006: -- p_init_msg_list in varchar2 optional --default = fnd_api.g_false
1007: -- p_commit in varchar2 optional --default = fnd_api.g_false
1008: -- p_TerrType_id in number
1009: -- p_TerrType_Name in varchar2
1010: -- p_Start_Date in date
1011: -- p_End_Date in date
1025: --
1026: -- end of comments
1027: PROCEDURE copy_terrtype (
1028: p_api_version_number IN NUMBER,
1029: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1030: p_commit IN VARCHAR2 := fnd_api.g_false,
1031: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1032: p_terrtype_id IN NUMBER,
1033: p_terrtype_name IN VARCHAR2,
1026: -- end of comments
1027: PROCEDURE copy_terrtype (
1028: p_api_version_number IN NUMBER,
1029: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1030: p_commit IN VARCHAR2 := fnd_api.g_false,
1031: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1032: p_terrtype_id IN NUMBER,
1033: p_terrtype_name IN VARCHAR2,
1034: p_terrtype_description IN VARCHAR2,
1027: PROCEDURE copy_terrtype (
1028: p_api_version_number IN NUMBER,
1029: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1030: p_commit IN VARCHAR2 := fnd_api.g_false,
1031: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1032: p_terrtype_id IN NUMBER,
1033: p_terrtype_name IN VARCHAR2,
1034: p_terrtype_description IN VARCHAR2,
1035: p_enabled_flag IN VARCHAR2,
1117: -- Standard Start of API savepoint
1118: SAVEPOINT copy_terrtype_pvt;
1119:
1120: -- Standard call to check for call compatibility.
1121: IF NOT fnd_api.compatible_api_call (
1122: l_api_version_number,
1123: p_api_version_number,
1124: l_api_name,
1125: g_pkg_name
1124: l_api_name,
1125: g_pkg_name
1126: )
1127: THEN
1128: RAISE fnd_api.g_exc_unexpected_error;
1129: END IF;
1130:
1131: -- Initialize message list if p_init_msg_list is set to TRUE.
1132: IF fnd_api.to_boolean (p_init_msg_list)
1128: RAISE fnd_api.g_exc_unexpected_error;
1129: END IF;
1130:
1131: -- Initialize message list if p_init_msg_list is set to TRUE.
1132: IF fnd_api.to_boolean (p_init_msg_list)
1133: THEN
1134: fnd_msg_pub.initialize;
1135: END IF;
1136:
1142: fnd_msg_pub.add;
1143: END IF;
1144:
1145: -- Initialize API return status to success
1146: x_return_status := fnd_api.g_ret_sts_success;
1147: -- Save the old record in the record type
1148: SELECT terr_type_id,
1149: last_update_date,
1150: last_updated_by,
1225: x_terrtype_id => l_terrtype_id,
1226: x_terrtype_out_rec => l_terrtype_out_rec
1227: );
1228:
1229: IF l_return_status <> fnd_api.g_ret_sts_success
1230: THEN
1231: RAISE fnd_api.g_exc_error;
1232: END IF;
1233:
1227: );
1228:
1229: IF l_return_status <> fnd_api.g_ret_sts_success
1230: THEN
1231: RAISE fnd_api.g_exc_error;
1232: END IF;
1233:
1234: --dbms_output.put_line('Before opening C_GetTerrTypeUsgs l_TerrType_Id - ' || l_TerrType_Id);
1235:
1265: x_msg_data => x_msg_data,
1266: x_terrtypeusgs_out_tbl => l_terrtypeusgs_out_tbl
1267: );
1268:
1269: IF l_return_status <> fnd_api.g_ret_sts_success
1270: THEN
1271: RAISE fnd_api.g_exc_error;
1272: END IF;
1273:
1267: );
1268:
1269: IF l_return_status <> fnd_api.g_ret_sts_success
1270: THEN
1271: RAISE fnd_api.g_exc_error;
1272: END IF;
1273:
1274: --
1275: l_terrtypeusg_id := l_terrtypeusgs_out_tbl (1).terr_type_usg_id;
1311: x_typequaltypeusgs_out_tbl => l_typequaltypeusgs_out_tbl
1312: );
1313:
1314: --
1315: IF l_return_status <> fnd_api.g_ret_sts_success
1316: THEN
1317: RAISE fnd_api.g_exc_error;
1318: END IF;
1319:
1313:
1314: --
1315: IF l_return_status <> fnd_api.g_ret_sts_success
1316: THEN
1317: RAISE fnd_api.g_exc_error;
1318: END IF;
1319:
1320: --
1321: --dbms_output.put_line('Before opening C_GetTerrTypeQual');
1349: x_msg_data => x_msg_data,
1350: x_terrtypequal_out_tbl => l_terrtypequal_out_tbl
1351: );
1352:
1353: IF l_return_status <> fnd_api.g_ret_sts_success
1354: THEN
1355: RAISE fnd_api.g_exc_error;
1356: END IF;
1357:
1351: );
1352:
1353: IF l_return_status <> fnd_api.g_ret_sts_success
1354: THEN
1355: RAISE fnd_api.g_exc_error;
1356: END IF;
1357:
1358: -- Debug Message
1359: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1365:
1366: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1367:
1368: -- Standard check for p_commit
1369: IF fnd_api.to_boolean (p_commit)
1370: THEN
1371: COMMIT WORK;
1372: END IF;
1373:
1375: x_terrtype_id := l_terrtype_id;
1376: --dbms_output.put_line('Copy_Terrtype PVT: Exiting API');
1377: EXCEPTION
1378: --
1379: WHEN fnd_api.g_exc_error
1380: THEN
1381: --dbms_output.put_line('Copy_Terrtype PVT: FND_API.G_EXC_ERROR' || SQLERRM);
1382: ROLLBACK TO copy_terrtype_pvt;
1383: x_return_status := fnd_api.g_ret_sts_error;
1377: EXCEPTION
1378: --
1379: WHEN fnd_api.g_exc_error
1380: THEN
1381: --dbms_output.put_line('Copy_Terrtype PVT: FND_API.G_EXC_ERROR' || SQLERRM);
1382: ROLLBACK TO copy_terrtype_pvt;
1383: x_return_status := fnd_api.g_ret_sts_error;
1384: fnd_msg_pub.count_and_get (
1385: p_count => x_msg_count,
1379: WHEN fnd_api.g_exc_error
1380: THEN
1381: --dbms_output.put_line('Copy_Terrtype PVT: FND_API.G_EXC_ERROR' || SQLERRM);
1382: ROLLBACK TO copy_terrtype_pvt;
1383: x_return_status := fnd_api.g_ret_sts_error;
1384: fnd_msg_pub.count_and_get (
1385: p_count => x_msg_count,
1386: p_data => x_msg_data
1387: );
1384: fnd_msg_pub.count_and_get (
1385: p_count => x_msg_count,
1386: p_data => x_msg_data
1387: );
1388: WHEN fnd_api.g_exc_unexpected_error
1389: THEN
1390: --dbms_output.put_line('Copy_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR' || SQLERRM);
1391: ROLLBACK TO copy_terrtype_pvt;
1392: x_return_status := fnd_api.g_ret_sts_unexp_error;
1386: p_data => x_msg_data
1387: );
1388: WHEN fnd_api.g_exc_unexpected_error
1389: THEN
1390: --dbms_output.put_line('Copy_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR' || SQLERRM);
1391: ROLLBACK TO copy_terrtype_pvt;
1392: x_return_status := fnd_api.g_ret_sts_unexp_error;
1393: fnd_msg_pub.count_and_get (
1394: p_count => x_msg_count,
1388: WHEN fnd_api.g_exc_unexpected_error
1389: THEN
1390: --dbms_output.put_line('Copy_Terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR' || SQLERRM);
1391: ROLLBACK TO copy_terrtype_pvt;
1392: x_return_status := fnd_api.g_ret_sts_unexp_error;
1393: fnd_msg_pub.count_and_get (
1394: p_count => x_msg_count,
1395: p_data => x_msg_data
1396: );
1397: WHEN OTHERS
1398: THEN
1399: --dbms_output.put_line('Copy_Terrtype PVT: OTHERS - ' || SQLERRM);
1400: ROLLBACK TO copy_terrtype_pvt;
1401: x_return_status := fnd_api.g_ret_sts_unexp_error;
1402: fnd_msg_pub.count_and_get (
1403: p_count => x_msg_count,
1404: p_data => x_msg_data
1405: );
1425: -- P_TypeQualTypeUsgs_Tbl TypeQualTypeUsgs_Tbl_Type := G_Miss_TypeQualTypeUsgs_Tbl
1426: --
1427: -- Optional
1428: -- Parameter Name Data Type Default
1429: -- P_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1430: -- P_Commit VARCHAR2 := FND_API.G_FALSE
1431: -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
1432: --
1433: -- OUT :
1426: --
1427: -- Optional
1428: -- Parameter Name Data Type Default
1429: -- P_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1430: -- P_Commit VARCHAR2 := FND_API.G_FALSE
1431: -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
1432: --
1433: -- OUT :
1434: -- Parameter Name Data Type
1427: -- Optional
1428: -- Parameter Name Data Type Default
1429: -- P_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1430: -- P_Commit VARCHAR2 := FND_API.G_FALSE
1431: -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
1432: --
1433: -- OUT :
1434: -- Parameter Name Data Type
1435: -- X_Return_Status VARCHAR2(1)
1446: -- End of Comments
1447: --
1448: PROCEDURE create_terrtype_header (
1449: p_api_version_number IN NUMBER,
1450: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1451: p_commit IN VARCHAR2 := fnd_api.g_false,
1452: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1453: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
1454: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
1447: --
1448: PROCEDURE create_terrtype_header (
1449: p_api_version_number IN NUMBER,
1450: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1451: p_commit IN VARCHAR2 := fnd_api.g_false,
1452: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1453: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
1454: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
1455: p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
1448: PROCEDURE create_terrtype_header (
1449: p_api_version_number IN NUMBER,
1450: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1451: p_commit IN VARCHAR2 := fnd_api.g_false,
1452: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1453: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
1454: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
1455: p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
1456: x_return_status OUT NOCOPY VARCHAR2,
1484: fnd_msg_pub.add;
1485: END IF;
1486:
1487: -- Initialize API return status to success
1488: x_return_status := fnd_api.g_ret_sts_success;
1489: --dbms_ourtput.put_line('Create_terrtype PVT: Before Calling Create_TerrType_Record PVT');
1490: -- Call create_territory_record API
1491: create_terrtype_record (
1492: p_api_version_number => p_api_version_number,
1507: --Save the out status record
1508: x_terrtype_out_rec := l_terrtype_out_rec;
1509:
1510: --If there is a major error
1511: IF l_return_status <> fnd_api.g_ret_sts_success
1512: THEN
1513: RAISE fnd_api.g_exc_error;
1514: END IF;
1515:
1509:
1510: --If there is a major error
1511: IF l_return_status <> fnd_api.g_ret_sts_success
1512: THEN
1513: RAISE fnd_api.g_exc_error;
1514: END IF;
1515:
1516: --
1517: --
1537: l_terrtypeusg_id := l_terrtypeusgs_out_tbl (1).terr_type_usg_id;
1538:
1539: --
1540: --If there is a major error
1541: IF l_return_status <> fnd_api.g_ret_sts_success
1542: THEN
1543: RAISE fnd_api.g_exc_error;
1544: END IF;
1545:
1539: --
1540: --If there is a major error
1541: IF l_return_status <> fnd_api.g_ret_sts_success
1542: THEN
1543: RAISE fnd_api.g_exc_error;
1544: END IF;
1545:
1546: --
1547: -- Call api to insert records into jtf_terr_qualtype_usgs
1567: --Save the out status record
1568: x_typequaltypeusgs_out_tbl := l_typequaltypeusgs_out_tbl;
1569:
1570: --If there is a major error
1571: IF l_return_status <> fnd_api.g_ret_sts_success
1572: THEN
1573: RAISE fnd_api.g_exc_error;
1574: END IF;
1575:
1569:
1570: --If there is a major error
1571: IF l_return_status <> fnd_api.g_ret_sts_success
1572: THEN
1573: RAISE fnd_api.g_exc_error;
1574: END IF;
1575:
1576: --
1577: -- Debug Message
1581: fnd_msg_pub.add;
1582: END IF;
1583: --dbms_ourtput.put_line('Create_TerrType_Header PVT: Exiting API');
1584: EXCEPTION
1585: WHEN fnd_api.g_exc_error
1586: THEN
1587: --dbms_ourtput.put_line('Create_TerrType_Header PVT: FND_API.G_EXC_ERROR');
1588: x_return_status := fnd_api.g_ret_sts_error;
1589: fnd_msg_pub.count_and_get (
1583: --dbms_ourtput.put_line('Create_TerrType_Header PVT: Exiting API');
1584: EXCEPTION
1585: WHEN fnd_api.g_exc_error
1586: THEN
1587: --dbms_ourtput.put_line('Create_TerrType_Header PVT: FND_API.G_EXC_ERROR');
1588: x_return_status := fnd_api.g_ret_sts_error;
1589: fnd_msg_pub.count_and_get (
1590: p_count => x_msg_count,
1591: p_data => x_msg_data
1584: EXCEPTION
1585: WHEN fnd_api.g_exc_error
1586: THEN
1587: --dbms_ourtput.put_line('Create_TerrType_Header PVT: FND_API.G_EXC_ERROR');
1588: x_return_status := fnd_api.g_ret_sts_error;
1589: fnd_msg_pub.count_and_get (
1590: p_count => x_msg_count,
1591: p_data => x_msg_data
1592: );
1589: fnd_msg_pub.count_and_get (
1590: p_count => x_msg_count,
1591: p_data => x_msg_data
1592: );
1593: WHEN fnd_api.g_exc_unexpected_error
1594: THEN
1595: --dbms_ourtput.put_line('Create_TerrType_Header PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
1596: x_return_status := fnd_api.g_ret_sts_unexp_error;
1597: fnd_msg_pub.count_and_get (
1591: p_data => x_msg_data
1592: );
1593: WHEN fnd_api.g_exc_unexpected_error
1594: THEN
1595: --dbms_ourtput.put_line('Create_TerrType_Header PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
1596: x_return_status := fnd_api.g_ret_sts_unexp_error;
1597: fnd_msg_pub.count_and_get (
1598: p_count => x_msg_count,
1599: p_data => x_msg_data
1592: );
1593: WHEN fnd_api.g_exc_unexpected_error
1594: THEN
1595: --dbms_ourtput.put_line('Create_TerrType_Header PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
1596: x_return_status := fnd_api.g_ret_sts_unexp_error;
1597: fnd_msg_pub.count_and_get (
1598: p_count => x_msg_count,
1599: p_data => x_msg_data
1600: );
1600: );
1601: WHEN OTHERS
1602: THEN
1603: --dbms_ourtput.put_line('Create_TerrType_Header PVT: OTHERS - ' || SQLERRM);
1604: x_return_status := fnd_api.g_ret_sts_unexp_error;
1605: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1606: THEN
1607: fnd_msg_pub.add_exc_msg (
1608: g_pkg_name,
1639: -- End of Comments
1640: --
1641: PROCEDURE create_terrtype_record (
1642: p_api_version_number IN NUMBER,
1643: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1644: p_commit IN VARCHAR2 := fnd_api.g_false,
1645: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1646: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
1647: x_return_status OUT NOCOPY VARCHAR2,
1640: --
1641: PROCEDURE create_terrtype_record (
1642: p_api_version_number IN NUMBER,
1643: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1644: p_commit IN VARCHAR2 := fnd_api.g_false,
1645: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1646: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
1647: x_return_status OUT NOCOPY VARCHAR2,
1648: x_msg_count OUT NOCOPY NUMBER,
1641: PROCEDURE create_terrtype_record (
1642: p_api_version_number IN NUMBER,
1643: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1644: p_commit IN VARCHAR2 := fnd_api.g_false,
1645: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1646: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
1647: x_return_status OUT NOCOPY VARCHAR2,
1648: x_msg_count OUT NOCOPY NUMBER,
1649: x_msg_data OUT NOCOPY VARCHAR2,
1661: -- Standard Start of API savepoint
1662: SAVEPOINT create_terr_rec_pvt;
1663:
1664: -- Standard call to check for call compatibility.
1665: IF NOT fnd_api.compatible_api_call (
1666: l_api_version_number,
1667: p_api_version_number,
1668: l_api_name,
1669: g_pkg_name
1668: l_api_name,
1669: g_pkg_name
1670: )
1671: THEN
1672: RAISE fnd_api.g_exc_unexpected_error;
1673: END IF;
1674:
1675: -- Initialize message list if p_init_msg_list is set to TRUE.
1676: IF fnd_api.to_boolean (p_init_msg_list)
1672: RAISE fnd_api.g_exc_unexpected_error;
1673: END IF;
1674:
1675: -- Initialize message list if p_init_msg_list is set to TRUE.
1676: IF fnd_api.to_boolean (p_init_msg_list)
1677: THEN
1678: fnd_msg_pub.initialize;
1679: END IF;
1680:
1686: fnd_msg_pub.add;
1687: END IF;
1688:
1689: -- Initialize API return status to success
1690: x_return_status := fnd_api.g_ret_sts_success;
1691:
1692: -- VAalidate
1693: IF (p_validation_level >= fnd_api.g_valid_level_full)
1694: THEN
1689: -- Initialize API return status to success
1690: x_return_status := fnd_api.g_ret_sts_success;
1691:
1692: -- VAalidate
1693: IF (p_validation_level >= fnd_api.g_valid_level_full)
1694: THEN
1695: -- Debug message
1696: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1697: THEN
1702:
1703: --
1704: -- Invoke validation procedures
1705: validate_terrtype_record (
1706: p_init_msg_list => fnd_api.g_false,
1707: x_return_status => x_return_status,
1708: x_msg_count => x_msg_count,
1709: x_msg_data => x_msg_data,
1710: p_terr_type_rec => p_terrtype_rec
1709: x_msg_data => x_msg_data,
1710: p_terr_type_rec => p_terrtype_rec
1711: );
1712:
1713: IF x_return_status <> fnd_api.g_ret_sts_success
1714: THEN
1715: RAISE fnd_api.g_exc_error;
1716: END IF;
1717: END IF;
1711: );
1712:
1713: IF x_return_status <> fnd_api.g_ret_sts_success
1714: THEN
1715: RAISE fnd_api.g_exc_error;
1716: END IF;
1717: END IF;
1718:
1719: --Intialize the application short name
1718:
1719: --Intialize the application short name
1720: g_app_short_name := fnd_global.application_short_name;
1721:
1722: /* Intialise to NULL if FND_API.G_MISS_NUM,
1723: ** otherwise used passed in value
1724: */
1725: IF (l_terrtype_id = FND_API.G_MISS_NUM) THEN
1726: l_terrtype_id := NULL;
1721:
1722: /* Intialise to NULL if FND_API.G_MISS_NUM,
1723: ** otherwise used passed in value
1724: */
1725: IF (l_terrtype_id = FND_API.G_MISS_NUM) THEN
1726: l_terrtype_id := NULL;
1727: END IF;
1728:
1729: --dbms_output.put_line('Value of l_terrtype_id='||TO_CHAR(l_terrtype_id));
1763: x_org_id => p_terrtype_rec.org_id
1764: );
1765: x_terrtype_out_rec.terr_type_id := l_terrtype_id;
1766: x_terrtype_id := l_terrtype_id;
1767: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_success;
1768:
1769: -- Debug Message
1770: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1771: THEN
1774: fnd_msg_pub.add;
1775: END IF;
1776:
1777: -- Standard check for p_commit
1778: IF fnd_api.to_boolean (p_commit)
1779: THEN
1780: COMMIT WORK;
1781: END IF;
1782:
1783: -- Standard call to get message count and if count is 1, get message info.
1784: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1785: --dbms_ourtput.put_line('Create_TerrType_Record PVT: Exiting API');
1786: EXCEPTION
1787: WHEN fnd_api.g_exc_error
1788: THEN
1789: --dbms_output.put_line('HELLO create_terrtype PVT: FND_API.G_EXC_ERROR');
1790: ROLLBACK TO create_terr_rec_pvt;
1791: x_return_status := fnd_api.g_ret_sts_error;
1785: --dbms_ourtput.put_line('Create_TerrType_Record PVT: Exiting API');
1786: EXCEPTION
1787: WHEN fnd_api.g_exc_error
1788: THEN
1789: --dbms_output.put_line('HELLO create_terrtype PVT: FND_API.G_EXC_ERROR');
1790: ROLLBACK TO create_terr_rec_pvt;
1791: x_return_status := fnd_api.g_ret_sts_error;
1792: fnd_msg_pub.count_and_get (
1793: p_count => x_msg_count,
1787: WHEN fnd_api.g_exc_error
1788: THEN
1789: --dbms_output.put_line('HELLO create_terrtype PVT: FND_API.G_EXC_ERROR');
1790: ROLLBACK TO create_terr_rec_pvt;
1791: x_return_status := fnd_api.g_ret_sts_error;
1792: fnd_msg_pub.count_and_get (
1793: p_count => x_msg_count,
1794: p_data => x_msg_data
1795: );
1792: fnd_msg_pub.count_and_get (
1793: p_count => x_msg_count,
1794: p_data => x_msg_data
1795: );
1796: WHEN fnd_api.g_exc_unexpected_error
1797: THEN
1798: --dbms_output.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
1799: ROLLBACK TO create_terr_rec_pvt;
1800: x_return_status := fnd_api.g_ret_sts_unexp_error;
1794: p_data => x_msg_data
1795: );
1796: WHEN fnd_api.g_exc_unexpected_error
1797: THEN
1798: --dbms_output.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
1799: ROLLBACK TO create_terr_rec_pvt;
1800: x_return_status := fnd_api.g_ret_sts_unexp_error;
1801: fnd_msg_pub.count_and_get (
1802: p_count => x_msg_count,
1796: WHEN fnd_api.g_exc_unexpected_error
1797: THEN
1798: --dbms_output.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
1799: ROLLBACK TO create_terr_rec_pvt;
1800: x_return_status := fnd_api.g_ret_sts_unexp_error;
1801: fnd_msg_pub.count_and_get (
1802: p_count => x_msg_count,
1803: p_data => x_msg_data
1804: );
1804: );
1805: WHEN OTHERS
1806: THEN
1807: --dbms_output.put_line('Create_TerrType_Record PVT: OTHERS - ' || SQLERRM);
1808: x_return_status := fnd_api.g_ret_sts_unexp_error;
1809: ROLLBACK TO create_terr_rec_pvt;
1810: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
1811:
1812: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1806: THEN
1807: --dbms_output.put_line('Create_TerrType_Record PVT: OTHERS - ' || SQLERRM);
1808: x_return_status := fnd_api.g_ret_sts_unexp_error;
1809: ROLLBACK TO create_terr_rec_pvt;
1810: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
1811:
1812: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1813: THEN
1814: fnd_msg_pub.add_exc_msg (
1847: -- End of Comments
1848: --
1849: PROCEDURE create_terrtype_usages (
1850: p_api_version_number IN NUMBER,
1851: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1852: p_commit IN VARCHAR2 := fnd_api.g_false,
1853: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1854: p_terrtype_id IN NUMBER,
1855: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type
1848: --
1849: PROCEDURE create_terrtype_usages (
1850: p_api_version_number IN NUMBER,
1851: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1852: p_commit IN VARCHAR2 := fnd_api.g_false,
1853: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1854: p_terrtype_id IN NUMBER,
1855: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type
1856: := g_miss_terrtypeusgs_tbl,
1849: PROCEDURE create_terrtype_usages (
1850: p_api_version_number IN NUMBER,
1851: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1852: p_commit IN VARCHAR2 := fnd_api.g_false,
1853: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1854: p_terrtype_id IN NUMBER,
1855: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type
1856: := g_miss_terrtypeusgs_tbl,
1857: x_return_status OUT NOCOPY VARCHAR2,
1874: -- Standard Start of API savepoint
1875: SAVEPOINT create_terr_type_usg_pvt;
1876:
1877: -- Standard call to check for call compatibility.
1878: IF NOT fnd_api.compatible_api_call (
1879: l_api_version_number,
1880: p_api_version_number,
1881: l_api_name,
1882: g_pkg_name
1881: l_api_name,
1882: g_pkg_name
1883: )
1884: THEN
1885: RAISE fnd_api.g_exc_unexpected_error;
1886: END IF;
1887:
1888: -- Initialize message list if p_init_msg_list is set to TRUE.
1889: IF fnd_api.to_boolean (p_init_msg_list)
1885: RAISE fnd_api.g_exc_unexpected_error;
1886: END IF;
1887:
1888: -- Initialize message list if p_init_msg_list is set to TRUE.
1889: IF fnd_api.to_boolean (p_init_msg_list)
1890: THEN
1891: fnd_msg_pub.initialize;
1892: END IF;
1893:
1904: --
1905: BEGIN
1906: -- dbms_ourtput.put_line('Create_TerrType_Usages PVT: Inside Loop JTF_TERR_TYPE_USGS_PKG.Insert_Row');
1907: -- Initialize API return status to success
1908: x_return_status := fnd_api.g_ret_sts_success;
1909:
1910: IF (p_validation_level >= fnd_api.g_valid_level_full)
1911: THEN
1912: -- Debug message
1906: -- dbms_ourtput.put_line('Create_TerrType_Usages PVT: Inside Loop JTF_TERR_TYPE_USGS_PKG.Insert_Row');
1907: -- Initialize API return status to success
1908: x_return_status := fnd_api.g_ret_sts_success;
1909:
1910: IF (p_validation_level >= fnd_api.g_valid_level_full)
1911: THEN
1912: -- Debug message
1913: IF fnd_msg_pub.check_msg_level (
1914: fnd_msg_pub.g_msg_lvl_debug_low
1921:
1922: --
1923: -- Invoke validation procedures
1924: validate_terrtype_usage (
1925: p_init_msg_list => fnd_api.g_false,
1926: x_return_status => x_return_status,
1927: x_msg_count => x_msg_count,
1928: x_msg_data => x_msg_data,
1929: p_terr_type_id => p_terrtype_id,
1930: p_terrtypeusgs_rec => p_terrtypeusgs_tbl (l_counter)
1931: );
1932:
1933: --
1934: IF x_return_status <> fnd_api.g_ret_sts_success
1935: THEN
1936: RAISE fnd_api.g_exc_error;
1937: END IF;
1938: --
1932:
1933: --
1934: IF x_return_status <> fnd_api.g_ret_sts_success
1935: THEN
1936: RAISE fnd_api.g_exc_error;
1937: END IF;
1938: --
1939:
1940: END IF;
1940: END IF;
1941:
1942: l_terrtypeusg_id := p_terrtypeusgs_tbl (l_counter).terr_type_usg_id;
1943:
1944: /* Intialise to NULL if FND_API.G_MISS_NUM,
1945: ** otherwise used passed in value
1946: */
1947: IF (l_terrtypeusg_id = FND_API.G_MISS_NUM) THEN
1948: l_terrtypeusg_id := NULL;
1943:
1944: /* Intialise to NULL if FND_API.G_MISS_NUM,
1945: ** otherwise used passed in value
1946: */
1947: IF (l_terrtypeusg_id = FND_API.G_MISS_NUM) THEN
1948: l_terrtypeusg_id := NULL;
1949: END IF;
1950:
1951: --
1966: l_terrtypeusg_id;
1967:
1968: -- If successful then save the success status for the record
1969: x_terrtypeusgs_out_tbl (l_counter).return_status :=
1970: fnd_api.g_ret_sts_success;
1971: EXCEPTION
1972: WHEN OTHERS
1973: THEN
1974: --dbms_ourtput.put_line('Create_TerrType_Usages PVT:Inside LOOP OTHERS - ' || SQLERRM);
1971: EXCEPTION
1972: WHEN OTHERS
1973: THEN
1974: --dbms_ourtput.put_line('Create_TerrType_Usages PVT:Inside LOOP OTHERS - ' || SQLERRM);
1975: x_return_status := fnd_api.g_ret_sts_unexp_error;
1976: x_terrtypeusgs_out_tbl (l_counter).terr_type_usg_id := NULL;
1977: x_terrtypeusgs_out_tbl (l_counter).return_status :=
1978: x_return_status;
1979:
1991:
1992: END LOOP;
1993:
1994: --Get the API overall return status
1995: x_return_status := fnd_api.g_ret_sts_success;
1996: --Get number of records in the ouput table
1997: l_terrtypeusgs_out_tbl_count := x_terrtypeusgs_out_tbl.COUNT;
1998: l_terrtypeusgs_out_tbl := x_terrtypeusgs_out_tbl;
1999:
1999:
2000: FOR l_counter IN 1 .. l_terrtypeusgs_out_tbl_count
2001: LOOP
2002: IF l_terrtypeusgs_out_tbl (l_counter).return_status =
2003: fnd_api.g_ret_sts_unexp_error
2004: OR l_terrtypeusgs_out_tbl (l_counter).return_status =
2005: fnd_api.g_ret_sts_error
2006: THEN
2007: x_return_status := fnd_api.g_ret_sts_error;
2001: LOOP
2002: IF l_terrtypeusgs_out_tbl (l_counter).return_status =
2003: fnd_api.g_ret_sts_unexp_error
2004: OR l_terrtypeusgs_out_tbl (l_counter).return_status =
2005: fnd_api.g_ret_sts_error
2006: THEN
2007: x_return_status := fnd_api.g_ret_sts_error;
2008: END IF;
2009: END LOOP;
2003: fnd_api.g_ret_sts_unexp_error
2004: OR l_terrtypeusgs_out_tbl (l_counter).return_status =
2005: fnd_api.g_ret_sts_error
2006: THEN
2007: x_return_status := fnd_api.g_ret_sts_error;
2008: END IF;
2009: END LOOP;
2010:
2011: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2016: END IF;
2017:
2018: --
2019: -- Standard check for p_commit
2020: IF fnd_api.to_boolean (p_commit)
2021: THEN
2022: COMMIT WORK;
2023: END IF;
2024:
2060: -- End of Comments
2061: --
2062: PROCEDURE create_terrtypequaltype_usage (
2063: p_api_version_number IN NUMBER,
2064: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2065: p_commit IN VARCHAR2 := fnd_api.g_false,
2066: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2067: p_terrtype_id IN NUMBER,
2068: p_terrtypeusg_id IN NUMBER,
2061: --
2062: PROCEDURE create_terrtypequaltype_usage (
2063: p_api_version_number IN NUMBER,
2064: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2065: p_commit IN VARCHAR2 := fnd_api.g_false,
2066: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2067: p_terrtype_id IN NUMBER,
2068: p_terrtypeusg_id IN NUMBER,
2069: p_typequaltypeusgs_rec IN typequaltypeusgs_rec_type := g_miss_typequaltypeusgs_rec,
2062: PROCEDURE create_terrtypequaltype_usage (
2063: p_api_version_number IN NUMBER,
2064: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2065: p_commit IN VARCHAR2 := fnd_api.g_false,
2066: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2067: p_terrtype_id IN NUMBER,
2068: p_terrtypeusg_id IN NUMBER,
2069: p_typequaltypeusgs_rec IN typequaltypeusgs_rec_type := g_miss_typequaltypeusgs_rec,
2070: x_return_status OUT NOCOPY VARCHAR2,
2085: -- Standard Start of API savepoint
2086: SAVEPOINT create_type_qtype_usg_pvt;
2087:
2088: -- Standard call to check for call compatibility.
2089: IF NOT fnd_api.compatible_api_call (
2090: l_api_version_number,
2091: p_api_version_number,
2092: l_api_name,
2093: g_pkg_name
2092: l_api_name,
2093: g_pkg_name
2094: )
2095: THEN
2096: RAISE fnd_api.g_exc_unexpected_error;
2097: END IF;
2098:
2099: -- Initialize message list if p_init_msg_list is set to TRUE.
2100: IF fnd_api.to_boolean (p_init_msg_list)
2096: RAISE fnd_api.g_exc_unexpected_error;
2097: END IF;
2098:
2099: -- Initialize message list if p_init_msg_list is set to TRUE.
2100: IF fnd_api.to_boolean (p_init_msg_list)
2101: THEN
2102: fnd_msg_pub.initialize;
2103: END IF;
2104:
2110: fnd_msg_pub.add;
2111: END IF;
2112:
2113: -- Initialize API return status to success
2114: x_return_status := fnd_api.g_ret_sts_success;
2115:
2116: IF (p_validation_level >= fnd_api.g_valid_level_full)
2117: THEN
2118: -- Debug message
2112:
2113: -- Initialize API return status to success
2114: x_return_status := fnd_api.g_ret_sts_success;
2115:
2116: IF (p_validation_level >= fnd_api.g_valid_level_full)
2117: THEN
2118: -- Debug message
2119: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2120: THEN
2132: p_terr_type_id => p_terrtype_id,
2133: p_type_qualtypeusgs_rec => p_typequaltypeusgs_rec
2134: );
2135:
2136: IF x_return_status <> fnd_api.g_ret_sts_success
2137: THEN
2138: RAISE fnd_api.g_exc_error;
2139: END IF;
2140: END IF;
2134: );
2135:
2136: IF x_return_status <> fnd_api.g_ret_sts_success
2137: THEN
2138: RAISE fnd_api.g_exc_error;
2139: END IF;
2140: END IF;
2141:
2142: --
2141:
2142: --
2143: --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage REC: Calling JTF_TYPE_QTYPE_USGS_PKG.Insert_Row');
2144:
2145: /* Intialise to NULL if FND_API.G_MISS_NUM,
2146: ** otherwise used passed in value
2147: */
2148: IF (l_typequaltype_usg_id = FND_API.G_MISS_NUM) THEN
2149: l_typequaltype_usg_id := NULL;
2144:
2145: /* Intialise to NULL if FND_API.G_MISS_NUM,
2146: ** otherwise used passed in value
2147: */
2148: IF (l_typequaltype_usg_id = FND_API.G_MISS_NUM) THEN
2149: l_typequaltype_usg_id := NULL;
2150: END IF;
2151:
2152: -- Call insert terr_Qual_Type_Usgs table handler
2167: -- Save the terr_usg_id and
2168: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
2169: l_typequaltype_usg_id;
2170: -- If successful then save the success status for the record
2171: x_typequaltypeusgs_out_rec.return_status := fnd_api.g_ret_sts_success;
2172:
2173: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2174: THEN
2175: fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2177: fnd_msg_pub.add;
2178: END IF;
2179:
2180: -- Standard check for p_commit
2181: IF fnd_api.to_boolean (p_commit)
2182: THEN
2183: COMMIT WORK;
2184: END IF;
2185:
2186: -- Standard call to get message count and if count is 1, get message info.
2187: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2188: --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage REC: Exiting API');
2189: EXCEPTION
2190: WHEN fnd_api.g_exc_error
2191: THEN
2192: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_ERROR');
2193: ROLLBACK TO create_type_qtype_usg_pvt;
2194: x_return_status := fnd_api.g_ret_sts_error;
2188: --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage REC: Exiting API');
2189: EXCEPTION
2190: WHEN fnd_api.g_exc_error
2191: THEN
2192: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_ERROR');
2193: ROLLBACK TO create_type_qtype_usg_pvt;
2194: x_return_status := fnd_api.g_ret_sts_error;
2195: fnd_msg_pub.count_and_get (
2196: p_count => x_msg_count,
2190: WHEN fnd_api.g_exc_error
2191: THEN
2192: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_ERROR');
2193: ROLLBACK TO create_type_qtype_usg_pvt;
2194: x_return_status := fnd_api.g_ret_sts_error;
2195: fnd_msg_pub.count_and_get (
2196: p_count => x_msg_count,
2197: p_data => x_msg_data
2198: );
2195: fnd_msg_pub.count_and_get (
2196: p_count => x_msg_count,
2197: p_data => x_msg_data
2198: );
2199: WHEN fnd_api.g_exc_unexpected_error
2200: THEN
2201: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
2202: ROLLBACK TO create_type_qtype_usg_pvt;
2203: x_return_status := fnd_api.g_ret_sts_unexp_error;
2197: p_data => x_msg_data
2198: );
2199: WHEN fnd_api.g_exc_unexpected_error
2200: THEN
2201: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
2202: ROLLBACK TO create_type_qtype_usg_pvt;
2203: x_return_status := fnd_api.g_ret_sts_unexp_error;
2204: fnd_msg_pub.count_and_get (
2205: p_count => x_msg_count,
2199: WHEN fnd_api.g_exc_unexpected_error
2200: THEN
2201: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
2202: ROLLBACK TO create_type_qtype_usg_pvt;
2203: x_return_status := fnd_api.g_ret_sts_unexp_error;
2204: fnd_msg_pub.count_and_get (
2205: p_count => x_msg_count,
2206: p_data => x_msg_data
2207: );
2207: );
2208: WHEN OTHERS
2209: THEN
2210: --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage REC: OTHERS - ' || SQLERRM);
2211: x_return_status := fnd_api.g_ret_sts_unexp_error;
2212: --
2213: ROLLBACK TO create_type_qtype_usg_pvt;
2214: x_typequaltypeusgs_out_rec.type_qual_type_usg_id := NULL;
2215: x_typequaltypeusgs_out_rec.return_status := x_return_status;
2255: -- End of Comments
2256: --
2257: PROCEDURE create_terrtypequaltype_usage (
2258: p_api_version_number IN NUMBER,
2259: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2260: p_commit IN VARCHAR2 := fnd_api.g_false,
2261: p_validation_level IN NUMBER
2262: := fnd_api.g_valid_level_full,
2263: p_terrtype_id IN NUMBER,
2256: --
2257: PROCEDURE create_terrtypequaltype_usage (
2258: p_api_version_number IN NUMBER,
2259: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2260: p_commit IN VARCHAR2 := fnd_api.g_false,
2261: p_validation_level IN NUMBER
2262: := fnd_api.g_valid_level_full,
2263: p_terrtype_id IN NUMBER,
2264: p_terrtypeusg_id IN NUMBER,
2258: p_api_version_number IN NUMBER,
2259: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2260: p_commit IN VARCHAR2 := fnd_api.g_false,
2261: p_validation_level IN NUMBER
2262: := fnd_api.g_valid_level_full,
2263: p_terrtype_id IN NUMBER,
2264: p_terrtypeusg_id IN NUMBER,
2265: p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type
2266: := g_miss_typequaltypeusgs_tbl,
2287: -- Standard Start of API savepoint
2288: SAVEPOINT create_terr_qtype_usg_pvt;
2289:
2290: -- Standard call to check for call compatibility.
2291: IF NOT fnd_api.compatible_api_call (
2292: l_api_version_number,
2293: p_api_version_number,
2294: l_api_name,
2295: g_pkg_name
2294: l_api_name,
2295: g_pkg_name
2296: )
2297: THEN
2298: RAISE fnd_api.g_exc_unexpected_error;
2299: END IF;
2300:
2301: -- Initialize message list if p_init_msg_list is set to TRUE.
2302: IF fnd_api.to_boolean (p_init_msg_list)
2298: RAISE fnd_api.g_exc_unexpected_error;
2299: END IF;
2300:
2301: -- Initialize message list if p_init_msg_list is set to TRUE.
2302: IF fnd_api.to_boolean (p_init_msg_list)
2303: THEN
2304: fnd_msg_pub.initialize;
2305: END IF;
2306:
2312: fnd_msg_pub.add;
2313: END IF;
2314:
2315: -- Initialize API return status to success
2316: x_return_status := fnd_api.g_ret_sts_success;
2317:
2318: -- Call insert terr_Qual_Type_Usgs table handler
2319: FOR l_counter IN 1 .. l_typequaltypeusgs_tbl_count
2320: LOOP
2336: );
2337:
2338: --
2339: --If there is a major error
2340: IF l_return_status <> fnd_api.g_ret_sts_success
2341: THEN
2342: --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
2343: -- Save the terr_usg_id and
2344: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
2338: --
2339: --If there is a major error
2340: IF l_return_status <> fnd_api.g_ret_sts_success
2341: THEN
2342: --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
2343: -- Save the terr_usg_id and
2344: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
2345: NULL;
2346: -- If save the ERROR status for the record
2344: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
2345: NULL;
2346: -- If save the ERROR status for the record
2347: x_typequaltypeusgs_out_tbl (l_counter).return_status :=
2348: fnd_api.g_ret_sts_unexp_error;
2349: ELSE
2350: --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
2351: -- Save the terr_usg_id and
2352: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
2346: -- If save the ERROR status for the record
2347: x_typequaltypeusgs_out_tbl (l_counter).return_status :=
2348: fnd_api.g_ret_sts_unexp_error;
2349: ELSE
2350: --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
2351: -- Save the terr_usg_id and
2352: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
2353: l_typequaltypeusg_id;
2354:
2353: l_typequaltypeusg_id;
2354:
2355: -- If successful then save the success status for the record
2356: x_typequaltypeusgs_out_tbl (l_counter).return_status :=
2357: fnd_api.g_ret_sts_success;
2358: END IF;
2359: --
2360:
2361: END LOOP;
2361: END LOOP;
2362:
2363: --Get the API overall return status
2364: -- Initialize API return status to success
2365: x_return_status := fnd_api.g_ret_sts_success;
2366: --Get number of records in the ouput table
2367: l_typequaltypeusgs_tbl_count := x_typequaltypeusgs_out_tbl.COUNT;
2368: l_typequaltypeusgs_out_tbl := x_typequaltypeusgs_out_tbl;
2369:
2369:
2370: FOR l_counter IN 1 .. l_typequaltypeusgs_tbl_count
2371: LOOP
2372: IF l_typequaltypeusgs_out_tbl (l_counter).return_status =
2373: fnd_api.g_ret_sts_unexp_error OR
2374: l_typequaltypeusgs_out_tbl (l_counter).return_status =
2375: fnd_api.g_ret_sts_error
2376: THEN
2377: x_return_status := fnd_api.g_ret_sts_error;
2371: LOOP
2372: IF l_typequaltypeusgs_out_tbl (l_counter).return_status =
2373: fnd_api.g_ret_sts_unexp_error OR
2374: l_typequaltypeusgs_out_tbl (l_counter).return_status =
2375: fnd_api.g_ret_sts_error
2376: THEN
2377: x_return_status := fnd_api.g_ret_sts_error;
2378: END IF;
2379: END LOOP;
2373: fnd_api.g_ret_sts_unexp_error OR
2374: l_typequaltypeusgs_out_tbl (l_counter).return_status =
2375: fnd_api.g_ret_sts_error
2376: THEN
2377: x_return_status := fnd_api.g_ret_sts_error;
2378: END IF;
2379: END LOOP;
2380:
2381: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2385: fnd_msg_pub.add;
2386: END IF;
2387:
2388: -- Standard check for p_commit
2389: IF fnd_api.to_boolean (p_commit)
2390: THEN
2391: COMMIT WORK;
2392: END IF;
2393:
2427: -- End of Comments
2428: --
2429: PROCEDURE create_terrtype_qualifier (
2430: p_api_version_number IN NUMBER,
2431: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2432: p_commit IN VARCHAR2 := fnd_api.g_false,
2433: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2434: p_terrtype_id IN NUMBER,
2435: p_terrtypequal_rec IN terrtypequal_rec_type := g_miss_terrtypequal_rec,
2428: --
2429: PROCEDURE create_terrtype_qualifier (
2430: p_api_version_number IN NUMBER,
2431: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2432: p_commit IN VARCHAR2 := fnd_api.g_false,
2433: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2434: p_terrtype_id IN NUMBER,
2435: p_terrtypequal_rec IN terrtypequal_rec_type := g_miss_terrtypequal_rec,
2436: x_return_status OUT NOCOPY VARCHAR2,
2429: PROCEDURE create_terrtype_qualifier (
2430: p_api_version_number IN NUMBER,
2431: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2432: p_commit IN VARCHAR2 := fnd_api.g_false,
2433: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2434: p_terrtype_id IN NUMBER,
2435: p_terrtypequal_rec IN terrtypequal_rec_type := g_miss_terrtypequal_rec,
2436: x_return_status OUT NOCOPY VARCHAR2,
2437: x_msg_count OUT NOCOPY NUMBER,
2451: -- Standard Start of API savepoint
2452: SAVEPOINT create_terr_type_qual_pvt;
2453:
2454: -- Standard call to check for call compatibility.
2455: IF NOT fnd_api.compatible_api_call (
2456: l_api_version_number,
2457: p_api_version_number,
2458: l_api_name,
2459: g_pkg_name
2458: l_api_name,
2459: g_pkg_name
2460: )
2461: THEN
2462: RAISE fnd_api.g_exc_unexpected_error;
2463: END IF;
2464:
2465: -- Initialize message list if p_init_msg_list is set to TRUE.
2466: IF fnd_api.to_boolean (p_init_msg_list)
2462: RAISE fnd_api.g_exc_unexpected_error;
2463: END IF;
2464:
2465: -- Initialize message list if p_init_msg_list is set to TRUE.
2466: IF fnd_api.to_boolean (p_init_msg_list)
2467: THEN
2468: fnd_msg_pub.initialize;
2469: END IF;
2470:
2476: fnd_msg_pub.add;
2477: END IF;
2478:
2479: -- Initialize API return status to success
2480: x_return_status := fnd_api.g_ret_sts_success;
2481:
2482: -- Check the validation level
2483: IF (p_validation_level >= fnd_api.g_valid_level_full)
2484: THEN
2479: -- Initialize API return status to success
2480: x_return_status := fnd_api.g_ret_sts_success;
2481:
2482: -- Check the validation level
2483: IF (p_validation_level >= fnd_api.g_valid_level_full)
2484: THEN
2485: -- Debug message
2486: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2487: THEN
2492:
2493: --
2494: -- Invoke validation procedures
2495: validate_qualifier (
2496: p_init_msg_list => fnd_api.g_false,
2497: p_terrtypequal_rec => p_terrtypequal_rec,
2498: p_terr_type_id => p_terrtype_id,
2499: x_return_status => x_return_status,
2500: x_msg_count => x_msg_count,
2500: x_msg_count => x_msg_count,
2501: x_msg_data => x_msg_data
2502: );
2503:
2504: IF x_return_status <> fnd_api.g_ret_sts_success
2505: THEN
2506: --dbms_output.put_line('Validate_Qualifier Procedure failed');
2507: RAISE fnd_api.g_exc_error;
2508: END IF;
2503:
2504: IF x_return_status <> fnd_api.g_ret_sts_success
2505: THEN
2506: --dbms_output.put_line('Validate_Qualifier Procedure failed');
2507: RAISE fnd_api.g_exc_error;
2508: END IF;
2509: --
2510:
2511: END IF;
2509: --
2510:
2511: END IF;
2512:
2513: /* Intialise to NULL if FND_API.G_MISS_NUM,
2514: ** otherwise used passed in value
2515: */
2516: IF (l_terrtypequal_id = FND_API.G_MISS_NUM) THEN
2517: l_terrtypequal_id := NULL;
2512:
2513: /* Intialise to NULL if FND_API.G_MISS_NUM,
2514: ** otherwise used passed in value
2515: */
2516: IF (l_terrtypequal_id = FND_API.G_MISS_NUM) THEN
2517: l_terrtypequal_id := NULL;
2518: END IF;
2519:
2520: -- Call insert terrtype_Qualifier table handler
2542: -- Save the terr_usg_id and
2543: x_terrtypequal_out_rec.terr_type_qual_id := l_terrtypequal_id;
2544:
2545: -- If successful then save the success status for the record
2546: x_terrtypequal_out_rec.return_status := fnd_api.g_ret_sts_success;
2547:
2548: --
2549: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2550: THEN
2553: fnd_msg_pub.add;
2554: END IF;
2555:
2556: -- Standard check for p_commit
2557: IF fnd_api.to_boolean (p_commit)
2558: THEN
2559: COMMIT WORK;
2560: END IF;
2561:
2563: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2564:
2565: --dbms_ourtput.put_line('Create_TerrType_Qualifier PVT: Exiting API');
2566: EXCEPTION
2567: WHEN fnd_api.g_exc_error
2568: THEN
2569: --dbms_output.put_line('GOODBYE create_terrtype PVT: FND_API.G_EXC_ERROR');
2570: ROLLBACK TO create_terr_type_qual_pvt;
2571: x_return_status := fnd_api.g_ret_sts_error;
2565: --dbms_ourtput.put_line('Create_TerrType_Qualifier PVT: Exiting API');
2566: EXCEPTION
2567: WHEN fnd_api.g_exc_error
2568: THEN
2569: --dbms_output.put_line('GOODBYE create_terrtype PVT: FND_API.G_EXC_ERROR');
2570: ROLLBACK TO create_terr_type_qual_pvt;
2571: x_return_status := fnd_api.g_ret_sts_error;
2572: fnd_msg_pub.count_and_get (
2573: p_count => x_msg_count,
2567: WHEN fnd_api.g_exc_error
2568: THEN
2569: --dbms_output.put_line('GOODBYE create_terrtype PVT: FND_API.G_EXC_ERROR');
2570: ROLLBACK TO create_terr_type_qual_pvt;
2571: x_return_status := fnd_api.g_ret_sts_error;
2572: fnd_msg_pub.count_and_get (
2573: p_count => x_msg_count,
2574: p_data => x_msg_data
2575: );
2572: fnd_msg_pub.count_and_get (
2573: p_count => x_msg_count,
2574: p_data => x_msg_data
2575: );
2576: WHEN fnd_api.g_exc_unexpected_error
2577: THEN
2578: --dbms_output.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
2579: ROLLBACK TO create_terr_type_qual_pvt;
2580: x_return_status := fnd_api.g_ret_sts_unexp_error;
2574: p_data => x_msg_data
2575: );
2576: WHEN fnd_api.g_exc_unexpected_error
2577: THEN
2578: --dbms_output.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
2579: ROLLBACK TO create_terr_type_qual_pvt;
2580: x_return_status := fnd_api.g_ret_sts_unexp_error;
2581: fnd_msg_pub.count_and_get (
2582: p_count => x_msg_count,
2576: WHEN fnd_api.g_exc_unexpected_error
2577: THEN
2578: --dbms_output.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
2579: ROLLBACK TO create_terr_type_qual_pvt;
2580: x_return_status := fnd_api.g_ret_sts_unexp_error;
2581: fnd_msg_pub.count_and_get (
2582: p_count => x_msg_count,
2583: p_data => x_msg_data
2584: );
2584: );
2585: WHEN OTHERS
2586: THEN
2587: --dbms_output.put_line('Create_TerrType_Qualifier PVT: OTHERS - ' || SQLERRM);
2588: x_return_status := fnd_api.g_ret_sts_unexp_error;
2589: ROLLBACK TO create_terr_type_qual_pvt;
2590: --
2591: x_terrtypequal_out_rec.terr_type_qual_id := NULL;
2592: x_terrtypequal_out_rec.return_status := x_return_status;
2633: -- End of Comments
2634: --
2635: PROCEDURE create_terrtype_qualifier (
2636: p_api_version_number IN NUMBER,
2637: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2638: p_commit IN VARCHAR2 := fnd_api.g_false,
2639: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2640: p_terrtype_id IN NUMBER,
2641: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
2634: --
2635: PROCEDURE create_terrtype_qualifier (
2636: p_api_version_number IN NUMBER,
2637: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2638: p_commit IN VARCHAR2 := fnd_api.g_false,
2639: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2640: p_terrtype_id IN NUMBER,
2641: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
2642: x_return_status OUT NOCOPY VARCHAR2,
2635: PROCEDURE create_terrtype_qualifier (
2636: p_api_version_number IN NUMBER,
2637: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2638: p_commit IN VARCHAR2 := fnd_api.g_false,
2639: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2640: p_terrtype_id IN NUMBER,
2641: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
2642: x_return_status OUT NOCOPY VARCHAR2,
2643: x_msg_count OUT NOCOPY NUMBER,
2662: -- Standard Start of API savepoint
2663: SAVEPOINT create_terr_qual_pvt;
2664:
2665: -- Standard call to check for call compatibility.
2666: IF NOT fnd_api.compatible_api_call (
2667: l_api_version_number,
2668: p_api_version_number,
2669: l_api_name,
2670: g_pkg_name
2669: l_api_name,
2670: g_pkg_name
2671: )
2672: THEN
2673: RAISE fnd_api.g_exc_unexpected_error;
2674: END IF;
2675:
2676: -- Initialize message list if p_init_msg_list is set to TRUE.
2677: IF fnd_api.to_boolean (p_init_msg_list)
2673: RAISE fnd_api.g_exc_unexpected_error;
2674: END IF;
2675:
2676: -- Initialize message list if p_init_msg_list is set to TRUE.
2677: IF fnd_api.to_boolean (p_init_msg_list)
2678: THEN
2679: fnd_msg_pub.initialize;
2680: END IF;
2681:
2686: fnd_msg_pub.add;
2687: END IF;
2688:
2689: -- Initialize API return status to success
2690: x_return_status := fnd_api.g_ret_sts_success;
2691:
2692: -- Call overloaded Create_Terr_Qualifier procedure
2693: --
2694: FOR l_counter IN 1 .. l_terrtypequal_tbl_count
2710: );
2711:
2712: --
2713: --If there is a major error
2714: IF l_return_status <> fnd_api.g_ret_sts_success
2715: THEN
2716: --dbms_ourtput.put_line('Create_TerrType_Qualifier TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
2717: -- Save the terr_usg_id and
2718: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id := NULL;
2712: --
2713: --If there is a major error
2714: IF l_return_status <> fnd_api.g_ret_sts_success
2715: THEN
2716: --dbms_ourtput.put_line('Create_TerrType_Qualifier TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
2717: -- Save the terr_usg_id and
2718: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id := NULL;
2719:
2720: -- If save the ERROR status for the record
2718: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id := NULL;
2719:
2720: -- If save the ERROR status for the record
2721: x_terrtypequal_out_tbl (l_counter).return_status :=
2722: fnd_api.g_ret_sts_unexp_error;
2723: ELSE
2724: --dbms_ourtput.put_line('Create_TerrType_Qualifier TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
2725: -- Save the terr_usg_id and
2726: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id :=
2720: -- If save the ERROR status for the record
2721: x_terrtypequal_out_tbl (l_counter).return_status :=
2722: fnd_api.g_ret_sts_unexp_error;
2723: ELSE
2724: --dbms_ourtput.put_line('Create_TerrType_Qualifier TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
2725: -- Save the terr_usg_id and
2726: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id :=
2727: l_terrtypequal_id;
2728:
2727: l_terrtypequal_id;
2728:
2729: -- If successful then save the success status for the record
2730: x_terrtypequal_out_tbl (l_counter).return_status :=
2731: fnd_api.g_ret_sts_success;
2732: END IF;
2733: --
2734:
2735: END LOOP;
2734:
2735: END LOOP;
2736:
2737: --Get the API overall return status
2738: x_return_status := fnd_api.g_ret_sts_success;
2739:
2740: --Get number of records in the ouput table
2741: l_terrtypequal_out_tbl_count := x_terrtypequal_out_tbl.COUNT;
2742: l_terrtypequal_out_tbl := x_terrtypequal_out_tbl;
2743:
2744: FOR l_counter IN 1 .. l_terrtypequal_out_tbl_count
2745: LOOP
2746: IF l_terrtypequal_out_tbl (l_counter).return_status =
2747: fnd_api.g_ret_sts_unexp_error OR
2748: l_terrtypequal_out_tbl (l_counter).return_status =
2749: fnd_api.g_ret_sts_error
2750: THEN
2751: x_return_status := fnd_api.g_ret_sts_error;
2745: LOOP
2746: IF l_terrtypequal_out_tbl (l_counter).return_status =
2747: fnd_api.g_ret_sts_unexp_error OR
2748: l_terrtypequal_out_tbl (l_counter).return_status =
2749: fnd_api.g_ret_sts_error
2750: THEN
2751: x_return_status := fnd_api.g_ret_sts_error;
2752: END IF;
2753: END LOOP;
2747: fnd_api.g_ret_sts_unexp_error OR
2748: l_terrtypequal_out_tbl (l_counter).return_status =
2749: fnd_api.g_ret_sts_error
2750: THEN
2751: x_return_status := fnd_api.g_ret_sts_error;
2752: END IF;
2753: END LOOP;
2754:
2755: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2758: fnd_msg_pub.add;
2759: END IF;
2760:
2761: -- Standard check for p_commit
2762: IF fnd_api.to_boolean (p_commit)
2763: THEN
2764: COMMIT WORK;
2765: END IF;
2766:
2796: -- End of Comments
2797: --
2798: PROCEDURE update_terrtype_record (
2799: p_api_version_number IN NUMBER,
2800: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2801: p_commit IN VARCHAR2 := fnd_api.g_false,
2802: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2803: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
2804: x_return_status OUT NOCOPY VARCHAR2,
2797: --
2798: PROCEDURE update_terrtype_record (
2799: p_api_version_number IN NUMBER,
2800: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2801: p_commit IN VARCHAR2 := fnd_api.g_false,
2802: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2803: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
2804: x_return_status OUT NOCOPY VARCHAR2,
2805: x_msg_count OUT NOCOPY NUMBER,
2798: PROCEDURE update_terrtype_record (
2799: p_api_version_number IN NUMBER,
2800: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2801: p_commit IN VARCHAR2 := fnd_api.g_false,
2802: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2803: p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
2804: x_return_status OUT NOCOPY VARCHAR2,
2805: x_msg_count OUT NOCOPY NUMBER,
2806: x_msg_data OUT NOCOPY VARCHAR2,
2856: -- Standard start of PAI savepoint
2857: SAVEPOINT update_terrtype_pvt;
2858:
2859: -- Standard call to check for call compatibility.
2860: IF NOT fnd_api.compatible_api_call (
2861: l_api_version_number,
2862: p_api_version_number,
2863: l_api_name,
2864: g_pkg_name
2863: l_api_name,
2864: g_pkg_name
2865: )
2866: THEN
2867: RAISE fnd_api.g_exc_unexpected_error;
2868: END IF;
2869:
2870: -- Initialize message list if p_init_msg_list is set to TRUE.
2871: IF fnd_api.to_boolean (p_init_msg_list)
2867: RAISE fnd_api.g_exc_unexpected_error;
2868: END IF;
2869:
2870: -- Initialize message list if p_init_msg_list is set to TRUE.
2871: IF fnd_api.to_boolean (p_init_msg_list)
2872: THEN
2873: fnd_msg_pub.initialize;
2874: END IF;
2875:
2881: fnd_msg_pub.add;
2882: END IF;
2883:
2884: -- Initialize API return status to SUCCESS
2885: x_return_status := fnd_api.g_ret_sts_success;
2886: --
2887: --dbms_ourtput.put_line('Update_TerrType_Record PVT: Opening C_GetTerrType' || to_char(nvl(P_TerrType_Rec.Terr_Type_Id,0)));
2888: OPEN c_getterrtype (p_terrtype_rec.terr_type_id);
2889: --
2929: fnd_message.set_token ('INFO', 'TERRITORY_TYPE', FALSE);
2930: fnd_msg_pub.add;
2931: END IF;
2932:
2933: RAISE fnd_api.g_exc_error;
2934: END IF;
2935:
2936: --
2937: CLOSE c_getterrtype;
2936: --
2937: CLOSE c_getterrtype;
2938:
2939: -- VAalidate
2940: IF (p_validation_level >= fnd_api.g_valid_level_full)
2941: THEN
2942: -- Debug message
2943: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2944: THEN
2949:
2950: --
2951: -- Invoke validation procedures
2952: validate_terrtype_record (
2953: p_init_msg_list => fnd_api.g_false,
2954: x_return_status => x_return_status,
2955: x_msg_count => x_msg_count,
2956: x_msg_data => x_msg_data,
2957: p_terr_type_rec => p_terrtype_rec
2956: x_msg_data => x_msg_data,
2957: p_terr_type_rec => p_terrtype_rec
2958: );
2959:
2960: IF x_return_status <> fnd_api.g_ret_sts_success
2961: THEN
2962: RAISE fnd_api.g_exc_error;
2963: END IF;
2964: END IF;
2958: );
2959:
2960: IF x_return_status <> fnd_api.g_ret_sts_success
2961: THEN
2962: RAISE fnd_api.g_exc_error;
2963: END IF;
2964: END IF;
2965:
2966: --
2997: x_attribute15 => p_terrtype_rec.attribute15,
2998: x_org_id => p_terrtype_rec.org_id
2999: );
3000: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3001: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_success;
3002: x_return_status := fnd_api.g_ret_sts_success;
3003:
3004: -- Debug Message
3005: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2998: x_org_id => p_terrtype_rec.org_id
2999: );
3000: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3001: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_success;
3002: x_return_status := fnd_api.g_ret_sts_success;
3003:
3004: -- Debug Message
3005: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3006: THEN
3011:
3012: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3013:
3014: -- Standard check for p_commit
3015: IF fnd_api.to_boolean (p_commit)
3016: THEN
3017: COMMIT WORK;
3018: END IF;
3019:
3018: END IF;
3019:
3020: --dbms_ourtput.put_line('Update_TerrType_Record PVT: Exiting API');
3021: EXCEPTION
3022: WHEN fnd_api.g_exc_error
3023: THEN
3024: ROLLBACK TO update_terrtype_pvt;
3025: x_return_status := fnd_api.g_ret_sts_error;
3026: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3021: EXCEPTION
3022: WHEN fnd_api.g_exc_error
3023: THEN
3024: ROLLBACK TO update_terrtype_pvt;
3025: x_return_status := fnd_api.g_ret_sts_error;
3026: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3027: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
3028: fnd_msg_pub.count_and_get (
3029: p_count => x_msg_count,
3023: THEN
3024: ROLLBACK TO update_terrtype_pvt;
3025: x_return_status := fnd_api.g_ret_sts_error;
3026: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3027: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
3028: fnd_msg_pub.count_and_get (
3029: p_count => x_msg_count,
3030: p_data => x_msg_data
3031: );
3028: fnd_msg_pub.count_and_get (
3029: p_count => x_msg_count,
3030: p_data => x_msg_data
3031: );
3032: WHEN fnd_api.g_exc_unexpected_error
3033: THEN
3034: ROLLBACK TO update_terrtype_pvt;
3035: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3036: x_return_status := fnd_api.g_ret_sts_unexp_error;
3031: );
3032: WHEN fnd_api.g_exc_unexpected_error
3033: THEN
3034: ROLLBACK TO update_terrtype_pvt;
3035: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3036: x_return_status := fnd_api.g_ret_sts_unexp_error;
3037: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3038: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
3039: fnd_msg_pub.count_and_get (
3032: WHEN fnd_api.g_exc_unexpected_error
3033: THEN
3034: ROLLBACK TO update_terrtype_pvt;
3035: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3036: x_return_status := fnd_api.g_ret_sts_unexp_error;
3037: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3038: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
3039: fnd_msg_pub.count_and_get (
3040: p_count => x_msg_count,
3034: ROLLBACK TO update_terrtype_pvt;
3035: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3036: x_return_status := fnd_api.g_ret_sts_unexp_error;
3037: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3038: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
3039: fnd_msg_pub.count_and_get (
3040: p_count => x_msg_count,
3041: p_data => x_msg_data
3042: );
3043: WHEN OTHERS
3044: THEN
3045: ROLLBACK TO update_terrtype_pvt;
3046: --dbms_ourtput.put_line('Update_TerrType_Record PVT: OTHERS - ' || SQLERRM);
3047: x_return_status := fnd_api.g_ret_sts_unexp_error;
3048: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3049: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
3050:
3051: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3045: ROLLBACK TO update_terrtype_pvt;
3046: --dbms_ourtput.put_line('Update_TerrType_Record PVT: OTHERS - ' || SQLERRM);
3047: x_return_status := fnd_api.g_ret_sts_unexp_error;
3048: x_terrtype_out_rec.terr_type_id := p_terrtype_rec.terr_type_id;
3049: x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_unexp_error;
3050:
3051: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3052: THEN
3053: fnd_msg_pub.add_exc_msg (
3085: -- End of Comments
3086: --
3087: PROCEDURE update_terrtype_usages (
3088: p_api_version_number IN NUMBER,
3089: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3090: p_commit IN VARCHAR2 := fnd_api.g_false,
3091: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3092: p_terrtypeusgs_rec IN terrtypeusgs_rec_type := g_miss_terrtypeusgs_rec,
3093: x_return_status OUT NOCOPY VARCHAR2,
3086: --
3087: PROCEDURE update_terrtype_usages (
3088: p_api_version_number IN NUMBER,
3089: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3090: p_commit IN VARCHAR2 := fnd_api.g_false,
3091: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3092: p_terrtypeusgs_rec IN terrtypeusgs_rec_type := g_miss_terrtypeusgs_rec,
3093: x_return_status OUT NOCOPY VARCHAR2,
3094: x_msg_count OUT NOCOPY NUMBER,
3087: PROCEDURE update_terrtype_usages (
3088: p_api_version_number IN NUMBER,
3089: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3090: p_commit IN VARCHAR2 := fnd_api.g_false,
3091: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3092: p_terrtypeusgs_rec IN terrtypeusgs_rec_type := g_miss_terrtypeusgs_rec,
3093: x_return_status OUT NOCOPY VARCHAR2,
3094: x_msg_count OUT NOCOPY NUMBER,
3095: x_msg_data OUT NOCOPY VARCHAR2,
3121: -- Standard start of PAI savepoint
3122: SAVEPOINT update_terrtype_usgs_pvt;
3123:
3124: -- Standard call to check for call compatibility.
3125: IF NOT fnd_api.compatible_api_call (
3126: l_api_version_number,
3127: p_api_version_number,
3128: l_api_name,
3129: g_pkg_name
3128: l_api_name,
3129: g_pkg_name
3130: )
3131: THEN
3132: RAISE fnd_api.g_exc_unexpected_error;
3133: END IF;
3134:
3135: -- Initialize message list if p_init_msg_list is set to TRUE.
3136: IF fnd_api.to_boolean (p_init_msg_list)
3132: RAISE fnd_api.g_exc_unexpected_error;
3133: END IF;
3134:
3135: -- Initialize message list if p_init_msg_list is set to TRUE.
3136: IF fnd_api.to_boolean (p_init_msg_list)
3137: THEN
3138: fnd_msg_pub.initialize;
3139: END IF;
3140:
3146: fnd_msg_pub.add;
3147: END IF;
3148:
3149: -- Initialize API return status to SUCCESS
3150: x_return_status := fnd_api.g_ret_sts_success;
3151:
3152: --dbms_ourtput.put_line('Update_TerrType_Usages REC: opening cursor C_GetTerrTypeUsage');
3153: OPEN c_getterrtypeusage (p_terrtypeusgs_rec.terr_type_usg_id);
3154: FETCH c_getterrtypeusage
3171: fnd_message.set_token ('INFO', 'TERRITORY_TYPE_USAGE', FALSE);
3172: fnd_msg_pub.add;
3173: END IF;
3174:
3175: RAISE fnd_api.g_exc_error;
3176: END IF;
3177:
3178: CLOSE c_getterrtypeusage;
3179:
3176: END IF;
3177:
3178: CLOSE c_getterrtypeusage;
3179:
3180: IF (p_validation_level >= fnd_api.g_valid_level_full)
3181: THEN
3182: -- Debug message
3183: IF fnd_msg_pub.check_msg_level (
3184: fnd_msg_pub.g_msg_lvl_debug_low
3191:
3192: --
3193: -- Invoke validation procedures
3194: validate_terrtype_usage (
3195: p_init_msg_list => fnd_api.g_false,
3196: x_return_status => x_return_status,
3197: x_msg_count => x_msg_count,
3198: x_msg_data => x_msg_data,
3199: p_terr_type_id => p_terrtypeusgs_rec.terr_type_id,
3200: p_terrtypeusgs_rec => p_terrtypeusgs_rec
3201: );
3202:
3203: --
3204: IF x_return_status <> fnd_api.g_ret_sts_success
3205: THEN
3206: RAISE fnd_api.g_exc_error;
3207: END IF;
3208: End If;
3202:
3203: --
3204: IF x_return_status <> fnd_api.g_ret_sts_success
3205: THEN
3206: RAISE fnd_api.g_exc_error;
3207: END IF;
3208: End If;
3209:
3210: --dbms_ourtput.put_line('Update_TerrType_Usages REC: Before Calling JTF_TERR_TYPE_USGS_PKG.Update_Row');
3222: x_org_id => p_terrtypeusgs_rec.org_id
3223: );
3224: --
3225: x_terrtypeusgs_out_rec.terr_type_usg_id := p_terrtypeusgs_rec.terr_type_id;
3226: x_terrtypeusgs_out_rec.return_status := fnd_api.g_ret_sts_success;
3227: x_return_status := fnd_api.g_ret_sts_success;
3228:
3229: -- Debug Message
3230: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3223: );
3224: --
3225: x_terrtypeusgs_out_rec.terr_type_usg_id := p_terrtypeusgs_rec.terr_type_id;
3226: x_terrtypeusgs_out_rec.return_status := fnd_api.g_ret_sts_success;
3227: x_return_status := fnd_api.g_ret_sts_success;
3228:
3229: -- Debug Message
3230: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3231: THEN
3236:
3237: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3238:
3239: -- Standard check for p_commit
3240: IF fnd_api.to_boolean (p_commit)
3241: THEN
3242: COMMIT WORK;
3243: END IF;
3244: --dbms_ourtput.put_line('Update_TerrType_Usages REC: Exiting API');
3242: COMMIT WORK;
3243: END IF;
3244: --dbms_ourtput.put_line('Update_TerrType_Usages REC: Exiting API');
3245: EXCEPTION
3246: WHEN fnd_api.g_exc_error
3247: THEN
3248: ROLLBACK TO update_terrtype_usgs_pvt;
3249: x_return_status := fnd_api.g_ret_sts_error;
3250: x_terrtypeusgs_out_rec.terr_type_usg_id :=
3245: EXCEPTION
3246: WHEN fnd_api.g_exc_error
3247: THEN
3248: ROLLBACK TO update_terrtype_usgs_pvt;
3249: x_return_status := fnd_api.g_ret_sts_error;
3250: x_terrtypeusgs_out_rec.terr_type_usg_id :=
3251: p_terrtypeusgs_rec.terr_type_id;
3252: x_terrtypeusgs_out_rec.return_status :=
3253: fnd_api.g_ret_sts_unexp_error;
3249: x_return_status := fnd_api.g_ret_sts_error;
3250: x_terrtypeusgs_out_rec.terr_type_usg_id :=
3251: p_terrtypeusgs_rec.terr_type_id;
3252: x_terrtypeusgs_out_rec.return_status :=
3253: fnd_api.g_ret_sts_unexp_error;
3254: fnd_msg_pub.count_and_get (
3255: p_count => x_msg_count,
3256: p_data => x_msg_data
3257: );
3254: fnd_msg_pub.count_and_get (
3255: p_count => x_msg_count,
3256: p_data => x_msg_data
3257: );
3258: WHEN fnd_api.g_exc_unexpected_error
3259: THEN
3260: ROLLBACK TO update_terrtype_usgs_pvt;
3261: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3262: x_return_status := fnd_api.g_ret_sts_unexp_error;
3257: );
3258: WHEN fnd_api.g_exc_unexpected_error
3259: THEN
3260: ROLLBACK TO update_terrtype_usgs_pvt;
3261: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3262: x_return_status := fnd_api.g_ret_sts_unexp_error;
3263: x_terrtypeusgs_out_rec.terr_type_usg_id :=
3264: p_terrtypeusgs_rec.terr_type_id;
3265: x_terrtypeusgs_out_rec.return_status :=
3258: WHEN fnd_api.g_exc_unexpected_error
3259: THEN
3260: ROLLBACK TO update_terrtype_usgs_pvt;
3261: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3262: x_return_status := fnd_api.g_ret_sts_unexp_error;
3263: x_terrtypeusgs_out_rec.terr_type_usg_id :=
3264: p_terrtypeusgs_rec.terr_type_id;
3265: x_terrtypeusgs_out_rec.return_status :=
3266: fnd_api.g_ret_sts_unexp_error;
3262: x_return_status := fnd_api.g_ret_sts_unexp_error;
3263: x_terrtypeusgs_out_rec.terr_type_usg_id :=
3264: p_terrtypeusgs_rec.terr_type_id;
3265: x_terrtypeusgs_out_rec.return_status :=
3266: fnd_api.g_ret_sts_unexp_error;
3267: fnd_msg_pub.count_and_get (
3268: p_count => x_msg_count,
3269: p_data => x_msg_data
3270: );
3271: WHEN OTHERS
3272: THEN
3273: ROLLBACK TO update_terrtype_usgs_pvt;
3274: --dbms_ourtput.put_line('Update_TerrType_Usages REC: OTHERS - ' || SQLERRM);
3275: x_return_status := fnd_api.g_ret_sts_unexp_error;
3276: x_terrtypeusgs_out_rec.terr_type_usg_id :=
3277: p_terrtypeusgs_rec.terr_type_id;
3278: x_terrtypeusgs_out_rec.return_status :=
3279: fnd_api.g_ret_sts_unexp_error;
3275: x_return_status := fnd_api.g_ret_sts_unexp_error;
3276: x_terrtypeusgs_out_rec.terr_type_usg_id :=
3277: p_terrtypeusgs_rec.terr_type_id;
3278: x_terrtypeusgs_out_rec.return_status :=
3279: fnd_api.g_ret_sts_unexp_error;
3280:
3281: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3282: THEN
3283: fnd_msg_pub.add_exc_msg (
3314: -- End of Comments
3315: --
3316: PROCEDURE update_terrtype_usages (
3317: p_api_version_number IN NUMBER,
3318: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3319: p_commit IN VARCHAR2 := fnd_api.g_false,
3320: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3321: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
3322: x_return_status OUT NOCOPY VARCHAR2,
3315: --
3316: PROCEDURE update_terrtype_usages (
3317: p_api_version_number IN NUMBER,
3318: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3319: p_commit IN VARCHAR2 := fnd_api.g_false,
3320: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3321: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
3322: x_return_status OUT NOCOPY VARCHAR2,
3323: x_msg_count OUT NOCOPY NUMBER,
3316: PROCEDURE update_terrtype_usages (
3317: p_api_version_number IN NUMBER,
3318: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3319: p_commit IN VARCHAR2 := fnd_api.g_false,
3320: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3321: p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
3322: x_return_status OUT NOCOPY VARCHAR2,
3323: x_msg_count OUT NOCOPY NUMBER,
3324: x_msg_data OUT NOCOPY VARCHAR2,
3336: BEGIN
3337: --dbms_ourtput.put_line('Update_TerrType_Usages TBL: Entering API');
3338:
3339: -- Initialize API return status to success
3340: x_return_status := fnd_api.g_ret_sts_success;
3341:
3342: -- Call insert terr_Qual_Type_Usgs table handler
3343: --
3344: FOR l_counter IN 1 .. l_terrtypeusgs_tbl_count
3358: );
3359:
3360: --
3361: --If there is a major error
3362: IF l_return_status <> fnd_api.g_ret_sts_success
3363: THEN
3364: --dbms_ourtput.put_line('Update_TerrType_Usages TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
3365: -- Save the terr_usg_id and
3366: x_terrtypeusgs_out_tbl (l_counter).terr_type_usg_id :=
3360: --
3361: --If there is a major error
3362: IF l_return_status <> fnd_api.g_ret_sts_success
3363: THEN
3364: --dbms_ourtput.put_line('Update_TerrType_Usages TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
3365: -- Save the terr_usg_id and
3366: x_terrtypeusgs_out_tbl (l_counter).terr_type_usg_id :=
3367: l_terrtypeusgs_out_rec.terr_type_usg_id;
3368:
3367: l_terrtypeusgs_out_rec.terr_type_usg_id;
3368:
3369: -- If save the ERROR status for the record
3370: x_terrtypeusgs_out_tbl (l_counter).return_status :=
3371: fnd_api.g_ret_sts_unexp_error;
3372: ELSE
3373: --dbms_ourtput.put_line('Update_TerrType_Usages TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
3374: -- Save the terr_usg_id and
3375: x_terrtypeusgs_out_tbl (l_counter).terr_type_usg_id :=
3369: -- If save the ERROR status for the record
3370: x_terrtypeusgs_out_tbl (l_counter).return_status :=
3371: fnd_api.g_ret_sts_unexp_error;
3372: ELSE
3373: --dbms_ourtput.put_line('Update_TerrType_Usages TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
3374: -- Save the terr_usg_id and
3375: x_terrtypeusgs_out_tbl (l_counter).terr_type_usg_id :=
3376: l_terrtypeusgs_out_rec.terr_type_usg_id;
3377:
3376: l_terrtypeusgs_out_rec.terr_type_usg_id;
3377:
3378: -- If successful then save the success status for the record
3379: x_terrtypeusgs_out_tbl (l_counter).return_status :=
3380: fnd_api.g_ret_sts_success;
3381: END IF;
3382: --
3383:
3384: END LOOP;
3384: END LOOP;
3385:
3386: --Get the API overall return status
3387: -- Initialize API return status to success
3388: x_return_status := fnd_api.g_ret_sts_success;
3389: --
3390: --Get number of records in the ouput table
3391: l_terrtypeusgs_out_tbl_count := x_terrtypeusgs_out_tbl.COUNT;
3392: l_terrtypeusgs_out_tbl := x_terrtypeusgs_out_tbl;
3394: --
3395: FOR l_counter IN 1 .. l_terrtypeusgs_out_tbl_count
3396: LOOP
3397: IF l_terrtypeusgs_out_tbl (l_counter).return_status =
3398: fnd_api.g_ret_sts_unexp_error OR
3399: l_terrtypeusgs_out_tbl (l_counter).return_status =
3400: fnd_api.g_ret_sts_error
3401: THEN
3402: x_return_status := fnd_api.g_ret_sts_error;
3396: LOOP
3397: IF l_terrtypeusgs_out_tbl (l_counter).return_status =
3398: fnd_api.g_ret_sts_unexp_error OR
3399: l_terrtypeusgs_out_tbl (l_counter).return_status =
3400: fnd_api.g_ret_sts_error
3401: THEN
3402: x_return_status := fnd_api.g_ret_sts_error;
3403: END IF;
3404: END LOOP;
3398: fnd_api.g_ret_sts_unexp_error OR
3399: l_terrtypeusgs_out_tbl (l_counter).return_status =
3400: fnd_api.g_ret_sts_error
3401: THEN
3402: x_return_status := fnd_api.g_ret_sts_error;
3403: END IF;
3404: END LOOP;
3405: --dbms_ourtput.put_line('Update_TerrType_Usages TBL: Exiting API');
3406: --
3427: -- End of Comments
3428: --
3429: PROCEDURE update_terrtypequaltype_usage (
3430: p_api_version_number IN NUMBER,
3431: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3432: p_commit IN VARCHAR2 := fnd_api.g_false,
3433: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3434: p_typequaltypeusgs_rec IN typequaltypeusgs_rec_type := g_miss_typequaltypeusgs_rec,
3435: x_return_status OUT NOCOPY VARCHAR2,
3428: --
3429: PROCEDURE update_terrtypequaltype_usage (
3430: p_api_version_number IN NUMBER,
3431: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3432: p_commit IN VARCHAR2 := fnd_api.g_false,
3433: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3434: p_typequaltypeusgs_rec IN typequaltypeusgs_rec_type := g_miss_typequaltypeusgs_rec,
3435: x_return_status OUT NOCOPY VARCHAR2,
3436: x_msg_count OUT NOCOPY NUMBER,
3429: PROCEDURE update_terrtypequaltype_usage (
3430: p_api_version_number IN NUMBER,
3431: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3432: p_commit IN VARCHAR2 := fnd_api.g_false,
3433: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3434: p_typequaltypeusgs_rec IN typequaltypeusgs_rec_type := g_miss_typequaltypeusgs_rec,
3435: x_return_status OUT NOCOPY VARCHAR2,
3436: x_msg_count OUT NOCOPY NUMBER,
3437: x_msg_data OUT NOCOPY VARCHAR2,
3466: -- Standard start of PAI savepoint
3467: SAVEPOINT update_typeqtype_usgs_pvt;
3468:
3469: -- Standard call to check for call compatibility.
3470: IF NOT fnd_api.compatible_api_call (
3471: l_api_version_number,
3472: p_api_version_number,
3473: l_api_name,
3474: g_pkg_name
3473: l_api_name,
3474: g_pkg_name
3475: )
3476: THEN
3477: RAISE fnd_api.g_exc_unexpected_error;
3478: END IF;
3479:
3480: -- Initialize message list if p_init_msg_list is set to TRUE.
3481: IF fnd_api.to_boolean (p_init_msg_list)
3477: RAISE fnd_api.g_exc_unexpected_error;
3478: END IF;
3479:
3480: -- Initialize message list if p_init_msg_list is set to TRUE.
3481: IF fnd_api.to_boolean (p_init_msg_list)
3482: THEN
3483: fnd_msg_pub.initialize;
3484: END IF;
3485:
3491: fnd_msg_pub.add;
3492: END IF;
3493:
3494: -- Initialize API return status to SUCCESS
3495: x_return_status := fnd_api.g_ret_sts_success;
3496: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage REC: opening cursor C_GetTypeQualTypeUsgs');
3497: OPEN c_gettypequaltypeusgs (p_typequaltypeusgs_rec.type_qual_type_usg_id);
3498: FETCH c_gettypequaltypeusgs
3499: INTO l_rowid,
3515: fnd_message.set_token ('INFO', 'TERRITORY', FALSE);
3516: fnd_msg_pub.add;
3517: END IF;
3518:
3519: RAISE fnd_api.g_exc_error;
3520: END IF;
3521:
3522: CLOSE c_gettypequaltypeusgs;
3523:
3520: END IF;
3521:
3522: CLOSE c_gettypequaltypeusgs;
3523:
3524: IF (p_validation_level >= fnd_api.g_valid_level_full)
3525: THEN
3526: -- Debug message
3527: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3528: THEN
3540: p_terr_type_id => p_typequaltypeusgs_rec.terr_type_id,
3541: p_type_qualtypeusgs_rec => p_typequaltypeusgs_rec
3542: );
3543:
3544: IF x_return_status <> fnd_api.g_ret_sts_success
3545: THEN
3546: RAISE fnd_api.g_exc_error;
3547: END IF;
3548: END IF;
3542: );
3543:
3544: IF x_return_status <> fnd_api.g_ret_sts_success
3545: THEN
3546: RAISE fnd_api.g_exc_error;
3547: END IF;
3548: END IF;
3549:
3550: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage REC: JTF_TYPE_QTYPE_USGS_PKG.Update_Row');
3562: );
3563: --
3564: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3565: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3566: x_typequaltypeusgs_out_rec.return_status := fnd_api.g_ret_sts_success;
3567: x_return_status := fnd_api.g_ret_sts_success;
3568:
3569: -- Debug Message
3570: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3563: --
3564: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3565: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3566: x_typequaltypeusgs_out_rec.return_status := fnd_api.g_ret_sts_success;
3567: x_return_status := fnd_api.g_ret_sts_success;
3568:
3569: -- Debug Message
3570: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3571: THEN
3576:
3577: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3578:
3579: -- Standard check for p_commit
3580: IF fnd_api.to_boolean (p_commit)
3581: THEN
3582: COMMIT WORK;
3583: END IF;
3584: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage REC: Exiting API');
3582: COMMIT WORK;
3583: END IF;
3584: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage REC: Exiting API');
3585: EXCEPTION
3586: WHEN fnd_api.g_exc_error
3587: THEN
3588: ROLLBACK TO update_typeqtype_usgs_pvt;
3589: x_return_status := fnd_api.g_ret_sts_error;
3590: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3585: EXCEPTION
3586: WHEN fnd_api.g_exc_error
3587: THEN
3588: ROLLBACK TO update_typeqtype_usgs_pvt;
3589: x_return_status := fnd_api.g_ret_sts_error;
3590: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3591: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3592: x_typequaltypeusgs_out_rec.return_status := fnd_api.g_ret_sts_error;
3593: fnd_msg_pub.count_and_get (
3588: ROLLBACK TO update_typeqtype_usgs_pvt;
3589: x_return_status := fnd_api.g_ret_sts_error;
3590: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3591: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3592: x_typequaltypeusgs_out_rec.return_status := fnd_api.g_ret_sts_error;
3593: fnd_msg_pub.count_and_get (
3594: p_count => x_msg_count,
3595: p_data => x_msg_data
3596: );
3593: fnd_msg_pub.count_and_get (
3594: p_count => x_msg_count,
3595: p_data => x_msg_data
3596: );
3597: WHEN fnd_api.g_exc_unexpected_error
3598: THEN
3599: ROLLBACK TO update_typeqtype_usgs_pvt;
3600: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3601: x_return_status := fnd_api.g_ret_sts_unexp_error;
3596: );
3597: WHEN fnd_api.g_exc_unexpected_error
3598: THEN
3599: ROLLBACK TO update_typeqtype_usgs_pvt;
3600: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3601: x_return_status := fnd_api.g_ret_sts_unexp_error;
3602: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3603: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3604: x_typequaltypeusgs_out_rec.return_status :=
3597: WHEN fnd_api.g_exc_unexpected_error
3598: THEN
3599: ROLLBACK TO update_typeqtype_usgs_pvt;
3600: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3601: x_return_status := fnd_api.g_ret_sts_unexp_error;
3602: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3603: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3604: x_typequaltypeusgs_out_rec.return_status :=
3605: fnd_api.g_ret_sts_unexp_error;
3601: x_return_status := fnd_api.g_ret_sts_unexp_error;
3602: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3603: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3604: x_typequaltypeusgs_out_rec.return_status :=
3605: fnd_api.g_ret_sts_unexp_error;
3606: fnd_msg_pub.count_and_get (
3607: p_count => x_msg_count,
3608: p_data => x_msg_data
3609: );
3610: WHEN OTHERS
3611: THEN
3612: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage REC: OTHERS - ' || SQLERRM);
3613: ROLLBACK TO update_typeqtype_usgs_pvt;
3614: x_return_status := fnd_api.g_ret_sts_unexp_error;
3615: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3616: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3617: x_typequaltypeusgs_out_rec.return_status :=
3618: fnd_api.g_ret_sts_unexp_error;
3614: x_return_status := fnd_api.g_ret_sts_unexp_error;
3615: x_typequaltypeusgs_out_rec.type_qual_type_usg_id :=
3616: p_typequaltypeusgs_rec.type_qual_type_usg_id;
3617: x_typequaltypeusgs_out_rec.return_status :=
3618: fnd_api.g_ret_sts_unexp_error;
3619:
3620: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3621: THEN
3622: fnd_msg_pub.add_exc_msg (
3650: -- End of Comments
3651: --
3652: PROCEDURE update_terrtypequaltype_usage (
3653: p_api_version_number IN NUMBER,
3654: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3655: p_commit IN VARCHAR2 := fnd_api.g_false,
3656: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3657: p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
3658: x_return_status OUT NOCOPY VARCHAR2,
3651: --
3652: PROCEDURE update_terrtypequaltype_usage (
3653: p_api_version_number IN NUMBER,
3654: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3655: p_commit IN VARCHAR2 := fnd_api.g_false,
3656: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3657: p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
3658: x_return_status OUT NOCOPY VARCHAR2,
3659: x_msg_count OUT NOCOPY NUMBER,
3652: PROCEDURE update_terrtypequaltype_usage (
3653: p_api_version_number IN NUMBER,
3654: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3655: p_commit IN VARCHAR2 := fnd_api.g_false,
3656: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3657: p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
3658: x_return_status OUT NOCOPY VARCHAR2,
3659: x_msg_count OUT NOCOPY NUMBER,
3660: x_msg_data OUT NOCOPY VARCHAR2,
3672: BEGIN
3673: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage TBL: Entering API');
3674:
3675: -- Initialize API return status to success
3676: x_return_status := fnd_api.g_ret_sts_success;
3677:
3678: -- Call insert terr_Qual_Type_Usgs table handler
3679: --
3680: FOR l_counter IN 1 .. l_typequaltypeusgs_tbl_count
3694: );
3695:
3696: --
3697: --If there is a major error
3698: IF l_return_status <> fnd_api.g_ret_sts_success
3699: THEN
3700: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
3701: -- Save the terr_usg_id and
3702: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
3696: --
3697: --If there is a major error
3698: IF l_return_status <> fnd_api.g_ret_sts_success
3699: THEN
3700: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
3701: -- Save the terr_usg_id and
3702: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
3703: l_typeqtypusgs_out_rec.type_qual_type_usg_id;
3704: -- If save the ERROR status for the record
3702: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
3703: l_typeqtypusgs_out_rec.type_qual_type_usg_id;
3704: -- If save the ERROR status for the record
3705: x_typequaltypeusgs_out_tbl (l_counter).return_status :=
3706: fnd_api.g_ret_sts_unexp_error;
3707: ELSE
3708: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
3709: -- Save the terr_usg_id and
3710: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
3704: -- If save the ERROR status for the record
3705: x_typequaltypeusgs_out_tbl (l_counter).return_status :=
3706: fnd_api.g_ret_sts_unexp_error;
3707: ELSE
3708: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
3709: -- Save the terr_usg_id and
3710: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
3711: l_typeqtypusgs_out_rec.type_qual_type_usg_id;
3712: -- If successful then save the success status for the record
3710: x_typequaltypeusgs_out_tbl (l_counter).type_qual_type_usg_id :=
3711: l_typeqtypusgs_out_rec.type_qual_type_usg_id;
3712: -- If successful then save the success status for the record
3713: x_typequaltypeusgs_out_tbl (l_counter).return_status :=
3714: fnd_api.g_ret_sts_success;
3715: END IF;
3716: --
3717:
3718: END LOOP;
3718: END LOOP;
3719:
3720: --Get the API overall return status
3721: -- Initialize API return status to success
3722: x_return_status := fnd_api.g_ret_sts_success;
3723: --Get number of records in the ouput table
3724: l_typeqtypusgs_out_tbl_count := x_typequaltypeusgs_out_tbl.COUNT;
3725: l_typeqtypusgs_out_tbl := x_typequaltypeusgs_out_tbl;
3726:
3726:
3727: FOR l_counter IN 1 .. l_typeqtypusgs_out_tbl_count
3728: LOOP
3729: IF l_typeqtypusgs_out_tbl (l_counter).return_status =
3730: fnd_api.g_ret_sts_unexp_error OR
3731: l_typeqtypusgs_out_tbl (l_counter).return_status =
3732: fnd_api.g_ret_sts_error
3733: THEN
3734: x_return_status := fnd_api.g_ret_sts_error;
3728: LOOP
3729: IF l_typeqtypusgs_out_tbl (l_counter).return_status =
3730: fnd_api.g_ret_sts_unexp_error OR
3731: l_typeqtypusgs_out_tbl (l_counter).return_status =
3732: fnd_api.g_ret_sts_error
3733: THEN
3734: x_return_status := fnd_api.g_ret_sts_error;
3735: END IF;
3736: END LOOP;
3730: fnd_api.g_ret_sts_unexp_error OR
3731: l_typeqtypusgs_out_tbl (l_counter).return_status =
3732: fnd_api.g_ret_sts_error
3733: THEN
3734: x_return_status := fnd_api.g_ret_sts_error;
3735: END IF;
3736: END LOOP;
3737: --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage TBL: Exiting API');
3738: --
3762: -- End of Comments
3763: --
3764: PROCEDURE update_terrtype_qualifier (
3765: p_api_version_number IN NUMBER,
3766: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3767: p_commit IN VARCHAR2 := fnd_api.g_false,
3768: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3769: p_terrtypequal_rec IN terrtypequal_rec_type := g_miss_terrtypequal_rec,
3770: x_return_status OUT NOCOPY VARCHAR2,
3763: --
3764: PROCEDURE update_terrtype_qualifier (
3765: p_api_version_number IN NUMBER,
3766: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3767: p_commit IN VARCHAR2 := fnd_api.g_false,
3768: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3769: p_terrtypequal_rec IN terrtypequal_rec_type := g_miss_terrtypequal_rec,
3770: x_return_status OUT NOCOPY VARCHAR2,
3771: x_msg_count OUT NOCOPY NUMBER,
3764: PROCEDURE update_terrtype_qualifier (
3765: p_api_version_number IN NUMBER,
3766: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3767: p_commit IN VARCHAR2 := fnd_api.g_false,
3768: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
3769: p_terrtypequal_rec IN terrtypequal_rec_type := g_miss_terrtypequal_rec,
3770: x_return_status OUT NOCOPY VARCHAR2,
3771: x_msg_count OUT NOCOPY NUMBER,
3772: x_msg_data OUT NOCOPY VARCHAR2,
3805: -- Standard start of PAI savepoint
3806: SAVEPOINT update_terrtype_qualifier;
3807:
3808: -- Standard call to check for call compatibility.
3809: IF NOT fnd_api.compatible_api_call (
3810: l_api_version_number,
3811: p_api_version_number,
3812: l_api_name,
3813: g_pkg_name
3812: l_api_name,
3813: g_pkg_name
3814: )
3815: THEN
3816: RAISE fnd_api.g_exc_unexpected_error;
3817: END IF;
3818:
3819: -- Initialize message list if p_init_msg_list is set to TRUE.
3820: IF fnd_api.to_boolean (p_init_msg_list)
3816: RAISE fnd_api.g_exc_unexpected_error;
3817: END IF;
3818:
3819: -- Initialize message list if p_init_msg_list is set to TRUE.
3820: IF fnd_api.to_boolean (p_init_msg_list)
3821: THEN
3822: fnd_msg_pub.initialize;
3823: END IF;
3824:
3830: fnd_msg_pub.add;
3831: END IF;
3832:
3833: -- Initialize API return status to SUCCESS
3834: x_return_status := fnd_api.g_ret_sts_success;
3835: --dbms_ourtput.put_line('Update_TerrType_Qualifier REC: opening cursor C_GetTerrQualifier');
3836: --
3837: OPEN c_getterrqualifier (p_terrtypequal_rec.terr_type_qual_id);
3838: --
3858: fnd_message.set_token ('INFO', 'TERRITORY_TYPE_QUALIFIER', FALSE);
3859: fnd_msg_pub.add;
3860: END IF;
3861:
3862: RAISE fnd_api.g_exc_error;
3863: END IF;
3864:
3865: CLOSE c_getterrqualifier;
3866:
3865: CLOSE c_getterrqualifier;
3866:
3867:
3868: -- Check the validation level
3869: IF (p_validation_level >= fnd_api.g_valid_level_full)
3870: THEN
3871: -- Debug message
3872: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3873: THEN
3878:
3879: --
3880: -- Invoke validation procedures
3881: validate_qualifier (
3882: p_init_msg_list => fnd_api.g_false,
3883: p_terrtypequal_rec => p_terrtypequal_rec,
3884: p_terr_type_id => p_terrtypequal_rec.terr_type_id,
3885: x_return_status => x_return_status,
3886: x_msg_count => x_msg_count,
3886: x_msg_count => x_msg_count,
3887: x_msg_data => x_msg_data
3888: );
3889:
3890: IF x_return_status <> fnd_api.g_ret_sts_success
3891: THEN
3892: --dbms_ourtput.put_line('Validate_Qualifier Procedure failed');
3893: RAISE fnd_api.g_exc_error;
3894: END IF;
3889:
3890: IF x_return_status <> fnd_api.g_ret_sts_success
3891: THEN
3892: --dbms_ourtput.put_line('Validate_Qualifier Procedure failed');
3893: RAISE fnd_api.g_exc_error;
3894: END IF;
3895: --
3896: END IF;
3897:
3914: );
3915: --Call the update table handler
3916: x_terrtypequal_out_rec.terr_type_qual_id :=
3917: p_terrtypequal_rec.terr_type_qual_id;
3918: x_terrtypequal_out_rec.return_status := fnd_api.g_ret_sts_success;
3919: x_return_status := fnd_api.g_ret_sts_success;
3920:
3921: -- Debug Message
3922: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3915: --Call the update table handler
3916: x_terrtypequal_out_rec.terr_type_qual_id :=
3917: p_terrtypequal_rec.terr_type_qual_id;
3918: x_terrtypequal_out_rec.return_status := fnd_api.g_ret_sts_success;
3919: x_return_status := fnd_api.g_ret_sts_success;
3920:
3921: -- Debug Message
3922: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3923: THEN
3928:
3929: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3930:
3931: -- Standard check for p_commit
3932: IF fnd_api.to_boolean (p_commit)
3933: THEN
3934: COMMIT WORK;
3935: END IF;
3936:
3935: END IF;
3936:
3937: --dbms_ourtput.put_line('Update_TerrType_Qualifier REC: Exiting API');
3938: EXCEPTION
3939: WHEN fnd_api.g_exc_error
3940: THEN
3941: ROLLBACK TO update_terrtype_qualifier;
3942: x_return_status := fnd_api.g_ret_sts_error;
3943: x_terrtypequal_out_rec.terr_type_qual_id :=
3938: EXCEPTION
3939: WHEN fnd_api.g_exc_error
3940: THEN
3941: ROLLBACK TO update_terrtype_qualifier;
3942: x_return_status := fnd_api.g_ret_sts_error;
3943: x_terrtypequal_out_rec.terr_type_qual_id :=
3944: p_terrtypequal_rec.terr_type_qual_id;
3945: x_terrtypequal_out_rec.return_status :=
3946: fnd_api.g_ret_sts_unexp_error;
3942: x_return_status := fnd_api.g_ret_sts_error;
3943: x_terrtypequal_out_rec.terr_type_qual_id :=
3944: p_terrtypequal_rec.terr_type_qual_id;
3945: x_terrtypequal_out_rec.return_status :=
3946: fnd_api.g_ret_sts_unexp_error;
3947: fnd_msg_pub.count_and_get (
3948: p_count => x_msg_count,
3949: p_data => x_msg_data
3950: );
3947: fnd_msg_pub.count_and_get (
3948: p_count => x_msg_count,
3949: p_data => x_msg_data
3950: );
3951: WHEN fnd_api.g_exc_unexpected_error
3952: THEN
3953: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3954: ROLLBACK TO update_terrtype_qualifier;
3955: x_return_status := fnd_api.g_ret_sts_unexp_error;
3949: p_data => x_msg_data
3950: );
3951: WHEN fnd_api.g_exc_unexpected_error
3952: THEN
3953: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3954: ROLLBACK TO update_terrtype_qualifier;
3955: x_return_status := fnd_api.g_ret_sts_unexp_error;
3956: x_terrtypequal_out_rec.terr_type_qual_id :=
3957: p_terrtypequal_rec.terr_type_qual_id;
3951: WHEN fnd_api.g_exc_unexpected_error
3952: THEN
3953: --dbms_ourtput.put_line('create_terrtype PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
3954: ROLLBACK TO update_terrtype_qualifier;
3955: x_return_status := fnd_api.g_ret_sts_unexp_error;
3956: x_terrtypequal_out_rec.terr_type_qual_id :=
3957: p_terrtypequal_rec.terr_type_qual_id;
3958: x_terrtypequal_out_rec.return_status :=
3959: fnd_api.g_ret_sts_unexp_error;
3955: x_return_status := fnd_api.g_ret_sts_unexp_error;
3956: x_terrtypequal_out_rec.terr_type_qual_id :=
3957: p_terrtypequal_rec.terr_type_qual_id;
3958: x_terrtypequal_out_rec.return_status :=
3959: fnd_api.g_ret_sts_unexp_error;
3960: fnd_msg_pub.count_and_get (
3961: p_count => x_msg_count,
3962: p_data => x_msg_data
3963: );
3964: WHEN OTHERS
3965: THEN
3966: --dbms_ourtput.put_line('Update_TerrType_Qualifier REC: OTHERS - ' || SQLERRM);
3967: ROLLBACK TO update_terrtype_qualifier;
3968: x_return_status := fnd_api.g_ret_sts_unexp_error;
3969: x_terrtypequal_out_rec.terr_type_qual_id :=
3970: p_terrtypequal_rec.terr_type_qual_id;
3971: x_terrtypequal_out_rec.return_status :=
3972: fnd_api.g_ret_sts_unexp_error;
3968: x_return_status := fnd_api.g_ret_sts_unexp_error;
3969: x_terrtypequal_out_rec.terr_type_qual_id :=
3970: p_terrtypequal_rec.terr_type_qual_id;
3971: x_terrtypequal_out_rec.return_status :=
3972: fnd_api.g_ret_sts_unexp_error;
3973:
3974: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3975: THEN
3976: fnd_msg_pub.add_exc_msg (
4004: -- End of Comments
4005: --
4006: PROCEDURE update_terrtype_qualifier (
4007: p_api_version_number IN NUMBER,
4008: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4009: p_commit IN VARCHAR2 := fnd_api.g_false,
4010: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
4011: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
4012: x_return_status OUT NOCOPY VARCHAR2,
4005: --
4006: PROCEDURE update_terrtype_qualifier (
4007: p_api_version_number IN NUMBER,
4008: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4009: p_commit IN VARCHAR2 := fnd_api.g_false,
4010: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
4011: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
4012: x_return_status OUT NOCOPY VARCHAR2,
4013: x_msg_count OUT NOCOPY NUMBER,
4006: PROCEDURE update_terrtype_qualifier (
4007: p_api_version_number IN NUMBER,
4008: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4009: p_commit IN VARCHAR2 := fnd_api.g_false,
4010: p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
4011: p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
4012: x_return_status OUT NOCOPY VARCHAR2,
4013: x_msg_count OUT NOCOPY NUMBER,
4014: x_msg_data OUT NOCOPY VARCHAR2,
4025: BEGIN
4026: --dbms_ourtput.put_line('Update_TerrType_Qualifier TBL: Entering API');
4027:
4028: -- Initialize API return status to success
4029: x_return_status := fnd_api.g_ret_sts_success;
4030:
4031: -- Call overloaded Create_Terr_Qualifier procedure
4032: --
4033: FOR l_counter IN 1 .. l_terrtypequal_tbl_count
4047: );
4048:
4049: --
4050: --If there is a major error
4051: IF l_return_status <> fnd_api.g_ret_sts_success
4052: THEN
4053: --dbms_ourtput.put_line('Update_TerrType_Qualifier TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
4054: -- Save the terr_usg_id and
4055: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id :=
4049: --
4050: --If there is a major error
4051: IF l_return_status <> fnd_api.g_ret_sts_success
4052: THEN
4053: --dbms_ourtput.put_line('Update_TerrType_Qualifier TBL: l_return_status <> FND_API.G_RET_STS_SUCCESS');
4054: -- Save the terr_usg_id and
4055: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id :=
4056: l_terrtypequal_out_rec.terr_type_qual_id;
4057: -- If save the ERROR status for the record
4055: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id :=
4056: l_terrtypequal_out_rec.terr_type_qual_id;
4057: -- If save the ERROR status for the record
4058: x_terrtypequal_out_tbl (l_counter).return_status :=
4059: fnd_api.g_ret_sts_unexp_error;
4060: ELSE
4061: --dbms_ourtput.put_line('Update_TerrType_Qualifier TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
4062: -- Save the terr_usg_id and
4063: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id :=
4057: -- If save the ERROR status for the record
4058: x_terrtypequal_out_tbl (l_counter).return_status :=
4059: fnd_api.g_ret_sts_unexp_error;
4060: ELSE
4061: --dbms_ourtput.put_line('Update_TerrType_Qualifier TBL: l_return_status = FND_API.G_RET_STS_SUCCESS');
4062: -- Save the terr_usg_id and
4063: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id :=
4064: l_terrtypequal_out_rec.terr_type_qual_id;
4065: -- If successful then save the success status for the record
4063: x_terrtypequal_out_tbl (l_counter).terr_type_qual_id :=
4064: l_terrtypequal_out_rec.terr_type_qual_id;
4065: -- If successful then save the success status for the record
4066: x_terrtypequal_out_tbl (l_counter).return_status :=
4067: fnd_api.g_ret_sts_success;
4068: END IF;
4069: --
4070:
4071: END LOOP;
4071: END LOOP;
4072:
4073: --Get the API overall return status
4074: -- Initialize API return status to success
4075: x_return_status := fnd_api.g_ret_sts_success;
4076:
4077: --Get number of records in the ouput table
4078: l_terrtypequal_out_tbl_count := x_terrtypequal_out_tbl.COUNT;
4079: l_terrtypequal_out_tbl := x_terrtypequal_out_tbl;
4080:
4081: FOR l_counter IN 1 .. l_terrtypequal_out_tbl_count
4082: LOOP
4083: IF l_terrtypequal_out_tbl (l_counter).return_status =
4084: fnd_api.g_ret_sts_unexp_error OR
4085: l_terrtypequal_out_tbl (l_counter).return_status =
4086: fnd_api.g_ret_sts_error
4087: THEN
4088: x_return_status := fnd_api.g_ret_sts_error;
4082: LOOP
4083: IF l_terrtypequal_out_tbl (l_counter).return_status =
4084: fnd_api.g_ret_sts_unexp_error OR
4085: l_terrtypequal_out_tbl (l_counter).return_status =
4086: fnd_api.g_ret_sts_error
4087: THEN
4088: x_return_status := fnd_api.g_ret_sts_error;
4089: END IF;
4090: END LOOP;
4084: fnd_api.g_ret_sts_unexp_error OR
4085: l_terrtypequal_out_tbl (l_counter).return_status =
4086: fnd_api.g_ret_sts_error
4087: THEN
4088: x_return_status := fnd_api.g_ret_sts_error;
4089: END IF;
4090: END LOOP;
4091: --
4092: --dbms_ourtput.put_line('Update_TerrType_Qualifier TBL: Exiting API');
4104: -- IN
4105: -- Required:
4106: -- Parameter Name Data Type Default
4107: -- P_Api_Version_Number NUMBER,
4108: -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
4109: -- P_Commit VARCHAR2
4110: -- P_TerrType_Id NUMBER
4111: --
4112: -- Optional:
4119: -- End of Comments
4120: --
4121: PROCEDURE delete_terrtype_record (
4122: p_api_version_number IN NUMBER,
4123: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4124: p_commit IN VARCHAR2 := fnd_api.g_false,
4125: p_terrtype_id IN NUMBER,
4126: x_return_status OUT NOCOPY VARCHAR2,
4127: x_msg_count OUT NOCOPY VARCHAR2,
4120: --
4121: PROCEDURE delete_terrtype_record (
4122: p_api_version_number IN NUMBER,
4123: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4124: p_commit IN VARCHAR2 := fnd_api.g_false,
4125: p_terrtype_id IN NUMBER,
4126: x_return_status OUT NOCOPY VARCHAR2,
4127: x_msg_count OUT NOCOPY VARCHAR2,
4128: x_msg_data OUT NOCOPY VARCHAR2
4138: -- Standard start of PAI savepoint
4139: SAVEPOINT delete_terr_record_pvt;
4140:
4141: -- Standard call to check for call compatibility.
4142: IF NOT fnd_api.compatible_api_call (
4143: l_api_version_number,
4144: p_api_version_number,
4145: l_api_name,
4146: g_pkg_name
4145: l_api_name,
4146: g_pkg_name
4147: )
4148: THEN
4149: RAISE fnd_api.g_exc_unexpected_error;
4150: END IF;
4151:
4152: -- Initialize message list if p_init_msg_list is set to TRUE.
4153: IF fnd_api.to_boolean (p_init_msg_list)
4149: RAISE fnd_api.g_exc_unexpected_error;
4150: END IF;
4151:
4152: -- Initialize message list if p_init_msg_list is set to TRUE.
4153: IF fnd_api.to_boolean (p_init_msg_list)
4154: THEN
4155: fnd_msg_pub.initialize;
4156: END IF;
4157:
4163: fnd_msg_pub.add;
4164: END IF;
4165:
4166: --Initialize the return status to success
4167: x_return_status := fnd_api.g_ret_sts_success;
4168: --
4169: --dbms_ourtput.put_line('Delete_TerrType_Record PVT: Before Calling JTF_TERR_TYPES_PKG.Delete_Row');
4170: jtf_terr_types_pkg.delete_row (x_terr_type_id => p_terrtype_id);
4171: --
4174:
4175: IF SQL%FOUND
4176: THEN
4177: --dbms_ourtput.put_line('Delete_TerrType_Record PVT: NO-RCORDS-FOUND');
4178: x_return_status := fnd_api.g_ret_sts_success;
4179: l_row_count := SQL%ROWCOUNT;
4180: END IF;
4181:
4182: --Prepare message token
4195:
4196: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4197:
4198: -- Standard check for p_commit
4199: IF fnd_api.to_boolean (p_commit)
4200: THEN
4201: COMMIT WORK;
4202: END IF;
4203: --
4201: COMMIT WORK;
4202: END IF;
4203: --
4204: EXCEPTION
4205: WHEN fnd_api.g_exc_unexpected_error
4206: THEN
4207: --dbms_ourtput.put_line('Delete_TerrType_Record PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4208: ROLLBACK TO delete_terr_record_pvt;
4209: x_return_status := fnd_api.g_ret_sts_unexp_error;
4203: --
4204: EXCEPTION
4205: WHEN fnd_api.g_exc_unexpected_error
4206: THEN
4207: --dbms_ourtput.put_line('Delete_TerrType_Record PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4208: ROLLBACK TO delete_terr_record_pvt;
4209: x_return_status := fnd_api.g_ret_sts_unexp_error;
4210: fnd_msg_pub.count_and_get (
4211: p_count => x_msg_count,
4205: WHEN fnd_api.g_exc_unexpected_error
4206: THEN
4207: --dbms_ourtput.put_line('Delete_TerrType_Record PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4208: ROLLBACK TO delete_terr_record_pvt;
4209: x_return_status := fnd_api.g_ret_sts_unexp_error;
4210: fnd_msg_pub.count_and_get (
4211: p_count => x_msg_count,
4212: p_data => x_msg_data
4213: );
4215: WHEN OTHERS
4216: THEN
4217: --dbms_ourtput.put_line('Delete_TerrType_Record PVT: OTHERS - ' || SQLERRM);
4218: ROLLBACK TO delete_terr_record_pvt;
4219: x_return_status := fnd_api.g_ret_sts_unexp_error;
4220:
4221: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4222: THEN
4223: fnd_msg_pub.add_exc_msg (
4239: -- IN
4240: -- Required:
4241: -- Parameter Name Data Type Default
4242: -- P_Api_Version_Number NUMBER,
4243: -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
4244: -- P_Commit VARCHAR2
4245: -- P_TerrTypeUsg_Id NUMBER
4246: --
4247: -- Optional:
4254: -- End of Comments
4255: --
4256: PROCEDURE delete_terrtype_usages (
4257: p_api_version_number IN NUMBER,
4258: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4259: p_commit IN VARCHAR2 := fnd_api.g_false,
4260: p_terrtypeusg_id IN NUMBER,
4261: x_return_status OUT NOCOPY VARCHAR2,
4262: x_msg_count OUT NOCOPY VARCHAR2,
4255: --
4256: PROCEDURE delete_terrtype_usages (
4257: p_api_version_number IN NUMBER,
4258: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4259: p_commit IN VARCHAR2 := fnd_api.g_false,
4260: p_terrtypeusg_id IN NUMBER,
4261: x_return_status OUT NOCOPY VARCHAR2,
4262: x_msg_count OUT NOCOPY VARCHAR2,
4263: x_msg_data OUT NOCOPY VARCHAR2
4273: -- Standard start of PAI savepoint
4274: SAVEPOINT delete_terrtype_usgs_pvt;
4275:
4276: -- Standard call to check for call compatibility.
4277: IF NOT fnd_api.compatible_api_call (
4278: l_api_version_number,
4279: p_api_version_number,
4280: l_api_name,
4281: g_pkg_name
4280: l_api_name,
4281: g_pkg_name
4282: )
4283: THEN
4284: RAISE fnd_api.g_exc_unexpected_error;
4285: END IF;
4286:
4287: -- Initialize message list if p_init_msg_list is set to TRUE.
4288: IF fnd_api.to_boolean (p_init_msg_list)
4284: RAISE fnd_api.g_exc_unexpected_error;
4285: END IF;
4286:
4287: -- Initialize message list if p_init_msg_list is set to TRUE.
4288: IF fnd_api.to_boolean (p_init_msg_list)
4289: THEN
4290: fnd_msg_pub.initialize;
4291: END IF;
4292:
4298: fnd_msg_pub.add;
4299: END IF;
4300:
4301: --Initialize the return status to success
4302: x_return_status := fnd_api.g_ret_sts_success;
4303: --
4304: --dbms_ourtput.put_line('Delete_TerrType_Usages PVT: Before Calling JTF_TERR_TYPE_USGS_PKG.Delete_Row');
4305: jtf_terr_type_usgs_pkg.delete_row (
4306: x_terr_type_usg_id => p_terrtypeusg_id
4311:
4312: IF SQL%FOUND
4313: THEN
4314: --dbms_ourtput.put_line('Delete_TerrType_Usages PVT: NO-RCORDS-FOUND');
4315: x_return_status := fnd_api.g_ret_sts_success;
4316: l_row_count := SQL%ROWCOUNT;
4317: END IF;
4318:
4319: --Prepare message token
4331:
4332: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4333:
4334: -- Standard check for p_commit
4335: IF fnd_api.to_boolean (p_commit)
4336: THEN
4337: COMMIT WORK;
4338: END IF;
4339: --
4339: --
4340: --dbms_ourtput.put_line('Delete_TerrType_Usages PVT: Exiting API');
4341: --
4342: EXCEPTION
4343: WHEN fnd_api.g_exc_unexpected_error
4344: THEN
4345: --dbms_ourtput.put_line('Delete_TerrType_Usages PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4346: ROLLBACK TO delete_terrtype_usgs_pvt;
4347: x_return_status := fnd_api.g_ret_sts_unexp_error;
4341: --
4342: EXCEPTION
4343: WHEN fnd_api.g_exc_unexpected_error
4344: THEN
4345: --dbms_ourtput.put_line('Delete_TerrType_Usages PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4346: ROLLBACK TO delete_terrtype_usgs_pvt;
4347: x_return_status := fnd_api.g_ret_sts_unexp_error;
4348: fnd_msg_pub.count_and_get (
4349: p_count => x_msg_count,
4343: WHEN fnd_api.g_exc_unexpected_error
4344: THEN
4345: --dbms_ourtput.put_line('Delete_TerrType_Usages PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4346: ROLLBACK TO delete_terrtype_usgs_pvt;
4347: x_return_status := fnd_api.g_ret_sts_unexp_error;
4348: fnd_msg_pub.count_and_get (
4349: p_count => x_msg_count,
4350: p_data => x_msg_data
4351: );
4352: WHEN OTHERS
4353: THEN
4354: --dbms_ourtput.put_line('Delete_TerrType_Usages PVT: OTHERS - ' || SQLERRM);
4355: ROLLBACK TO delete_terrtype_usgs_pvt;
4356: x_return_status := fnd_api.g_ret_sts_unexp_error;
4357:
4358: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4359: THEN
4360: fnd_msg_pub.add_exc_msg (
4377: -- IN
4378: -- Required:
4379: -- Parameter Name Data Type Default
4380: -- P_Api_Version_Number NUMBER,
4381: -- P_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
4382: -- P_Commit VARCHAR2 := FND_API.G_FALSE
4383: -- P_Terr_Qual_Type_Usg_Id NUMBER
4384: --
4385: -- Optional:
4378: -- Required:
4379: -- Parameter Name Data Type Default
4380: -- P_Api_Version_Number NUMBER,
4381: -- P_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
4382: -- P_Commit VARCHAR2 := FND_API.G_FALSE
4383: -- P_Terr_Qual_Type_Usg_Id NUMBER
4384: --
4385: -- Optional:
4386: -- OUT:
4392: -- End of Comments
4393: --
4394: PROCEDURE delete_terrtypequaltype_usage (
4395: p_api_version_number IN NUMBER,
4396: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4397: p_commit IN VARCHAR2 := fnd_api.g_false,
4398: p_terrtypequaltype_usg_id IN NUMBER,
4399: x_return_status OUT NOCOPY VARCHAR2,
4400: x_msg_count OUT NOCOPY VARCHAR2,
4393: --
4394: PROCEDURE delete_terrtypequaltype_usage (
4395: p_api_version_number IN NUMBER,
4396: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4397: p_commit IN VARCHAR2 := fnd_api.g_false,
4398: p_terrtypequaltype_usg_id IN NUMBER,
4399: x_return_status OUT NOCOPY VARCHAR2,
4400: x_msg_count OUT NOCOPY VARCHAR2,
4401: x_msg_data OUT NOCOPY VARCHAR2
4412: -- Standard start of PAI savepoint
4413: SAVEPOINT delete_typequaltypeusg_pvt;
4414:
4415: -- Standard call to check for call compatibility.
4416: IF NOT fnd_api.compatible_api_call (
4417: l_api_version_number,
4418: p_api_version_number,
4419: l_api_name,
4420: g_pkg_name
4419: l_api_name,
4420: g_pkg_name
4421: )
4422: THEN
4423: RAISE fnd_api.g_exc_unexpected_error;
4424: END IF;
4425:
4426: -- Initialize message list if p_init_msg_list is set to TRUE.
4427: IF fnd_api.to_boolean (p_init_msg_list)
4423: RAISE fnd_api.g_exc_unexpected_error;
4424: END IF;
4425:
4426: -- Initialize message list if p_init_msg_list is set to TRUE.
4427: IF fnd_api.to_boolean (p_init_msg_list)
4428: THEN
4429: fnd_msg_pub.initialize;
4430: END IF;
4431:
4437: fnd_msg_pub.add;
4438: END IF;
4439:
4440: --Initialize the return status to success
4441: x_return_status := fnd_api.g_ret_sts_success;
4442: --
4443: --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: Before Calling JTF_TYPE_QTYPE_USGS_PKG.Delete_Row');
4444: jtf_type_qtype_usgs_pkg.delete_row (
4445: x_type_qtype_usg_id => p_terrtypequaltype_usg_id
4452: --
4453: IF SQL%FOUND
4454: THEN
4455: --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: NO-RCORDS-FOUND');
4456: x_return_status := fnd_api.g_ret_sts_success;
4457: l_row_count := SQL%ROWCOUNT;
4458: END IF;
4459:
4460: --Prepare message token
4472:
4473: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4474:
4475: -- Standard check for p_commit
4476: IF fnd_api.to_boolean (p_commit)
4477: THEN
4478: COMMIT WORK;
4479: END IF;
4480: --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: Exiting API');
4479: END IF;
4480: --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: Exiting API');
4481: --
4482: EXCEPTION
4483: WHEN fnd_api.g_exc_unexpected_error
4484: THEN
4485: --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4486: ROLLBACK TO delete_typequaltypeusg_pvt;
4487: x_return_status := fnd_api.g_ret_sts_unexp_error;
4481: --
4482: EXCEPTION
4483: WHEN fnd_api.g_exc_unexpected_error
4484: THEN
4485: --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4486: ROLLBACK TO delete_typequaltypeusg_pvt;
4487: x_return_status := fnd_api.g_ret_sts_unexp_error;
4488: fnd_msg_pub.count_and_get (
4489: p_count => x_msg_count,
4483: WHEN fnd_api.g_exc_unexpected_error
4484: THEN
4485: --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4486: ROLLBACK TO delete_typequaltypeusg_pvt;
4487: x_return_status := fnd_api.g_ret_sts_unexp_error;
4488: fnd_msg_pub.count_and_get (
4489: p_count => x_msg_count,
4490: p_data => x_msg_data
4491: );
4492: WHEN OTHERS
4493: THEN
4494: --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: OTHERS - ' || SQLERRM);
4495: ROLLBACK TO delete_typequaltypeusg_pvt;
4496: x_return_status := fnd_api.g_ret_sts_unexp_error;
4497:
4498: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4499: THEN
4500: fnd_msg_pub.add_exc_msg (
4517: -- IN
4518: -- Required:
4519: -- Parameter Name Data Type Default
4520: -- P_Api_Version_Number NUMBER,
4521: -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
4522: -- P_Commit VARCHAR2
4523: -- P_TerrTypeQual_Id NUMBER
4524: --
4525: -- Optional:
4532: -- End of Comments
4533: --
4534: PROCEDURE delete_terrtype_qualifier (
4535: p_api_version_number IN NUMBER,
4536: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4537: p_commit IN VARCHAR2 := fnd_api.g_false,
4538: p_terrtypequal_id IN NUMBER,
4539: x_return_status OUT NOCOPY VARCHAR2,
4540: x_msg_count OUT NOCOPY VARCHAR2,
4533: --
4534: PROCEDURE delete_terrtype_qualifier (
4535: p_api_version_number IN NUMBER,
4536: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4537: p_commit IN VARCHAR2 := fnd_api.g_false,
4538: p_terrtypequal_id IN NUMBER,
4539: x_return_status OUT NOCOPY VARCHAR2,
4540: x_msg_count OUT NOCOPY VARCHAR2,
4541: x_msg_data OUT NOCOPY VARCHAR2
4552: -- Standard start of PAI savepoint
4553: SAVEPOINT delete_terrtypequal_pvt;
4554:
4555: -- Standard call to check for call compatibility.
4556: IF NOT fnd_api.compatible_api_call (
4557: l_api_version_number,
4558: p_api_version_number,
4559: l_api_name,
4560: g_pkg_name
4559: l_api_name,
4560: g_pkg_name
4561: )
4562: THEN
4563: RAISE fnd_api.g_exc_unexpected_error;
4564: END IF;
4565:
4566: -- Initialize message list if p_init_msg_list is set to TRUE.
4567: IF fnd_api.to_boolean (p_init_msg_list)
4563: RAISE fnd_api.g_exc_unexpected_error;
4564: END IF;
4565:
4566: -- Initialize message list if p_init_msg_list is set to TRUE.
4567: IF fnd_api.to_boolean (p_init_msg_list)
4568: THEN
4569: fnd_msg_pub.initialize;
4570: END IF;
4571:
4577: fnd_msg_pub.add;
4578: END IF;
4579:
4580: --Initialize the return status to success
4581: x_return_status := fnd_api.g_ret_sts_success;
4582: --
4583: --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: Before Calling JTF_TERR_TYPE_QUAL_PKG.Delete_Row');
4584: jtf_terr_type_qual_pkg.delete_row (
4585: x_terr_type_qual_id => p_terrtypequal_id
4590:
4591: IF SQL%FOUND
4592: THEN
4593: --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: NO-RCORDS-FOUND');
4594: x_return_status := fnd_api.g_ret_sts_success;
4595: l_row_count := SQL%ROWCOUNT;
4596: END IF;
4597:
4598: --Prepare message token
4610:
4611: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4612:
4613: -- Standard check for p_commit
4614: IF fnd_api.to_boolean (p_commit)
4615: THEN
4616: COMMIT WORK;
4617: END IF;
4618: --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: Exiting API');
4617: END IF;
4618: --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: Exiting API');
4619: --
4620: EXCEPTION
4621: WHEN fnd_api.g_exc_unexpected_error
4622: THEN
4623: --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4624: ROLLBACK TO delete_terrtypequal_pvt;
4625: x_return_status := fnd_api.g_ret_sts_unexp_error;
4619: --
4620: EXCEPTION
4621: WHEN fnd_api.g_exc_unexpected_error
4622: THEN
4623: --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4624: ROLLBACK TO delete_terrtypequal_pvt;
4625: x_return_status := fnd_api.g_ret_sts_unexp_error;
4626: fnd_msg_pub.count_and_get (
4627: p_count => x_msg_count,
4621: WHEN fnd_api.g_exc_unexpected_error
4622: THEN
4623: --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
4624: ROLLBACK TO delete_terrtypequal_pvt;
4625: x_return_status := fnd_api.g_ret_sts_unexp_error;
4626: fnd_msg_pub.count_and_get (
4627: p_count => x_msg_count,
4628: p_data => x_msg_data
4629: );
4630: WHEN OTHERS
4631: THEN
4632: --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: OTHERS - ' || SQLERRM);
4633: ROLLBACK TO delete_terrtypequal_pvt;
4634: x_return_status := fnd_api.g_ret_sts_unexp_error;
4635:
4636: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4637: THEN
4638: fnd_msg_pub.add_exc_msg (
4645: --
4646: -- Validate the Territory Type RECORD
4647: -- Validate Territory Type Name
4648: PROCEDURE validate_terrtype_record (
4649: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4650: p_terr_type_rec IN terrtype_rec_type := g_miss_terrtype_rec,
4651: x_return_status OUT NOCOPY VARCHAR2,
4652: x_msg_count OUT NOCOPY NUMBER,
4653: x_msg_data OUT NOCOPY VARCHAR2
4656: BEGIN
4657: --dbms_output.put_line ('Validate_TerrType_Header: Entering API');
4658:
4659: -- Initialize the status to success
4660: x_return_status := fnd_api.g_ret_sts_success;
4661:
4662: -- Check whether the territory Name is specified
4663: --
4664: IF (p_terr_type_rec.name IS NULL) OR
4661:
4662: -- Check whether the territory Name is specified
4663: --
4664: IF (p_terr_type_rec.name IS NULL) OR
4665: (p_terr_type_rec.name = fnd_api.g_miss_char)
4666: THEN
4667: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4668: THEN
4669: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4670: fnd_message.set_token ('COL_NAME', 'NAME');
4671: fnd_msg_pub.add;
4672: END IF;
4673:
4674: x_return_status := fnd_api.g_ret_sts_error;
4675: END IF;
4676:
4677: -- Check whether application short name is specified
4678: --
4676:
4677: -- Check whether application short name is specified
4678: --
4679: IF (p_terr_type_rec.application_short_name IS NULL)
4680: OR (p_terr_type_rec.application_short_name = fnd_api.g_miss_char)
4681: THEN
4682: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4683: THEN
4684: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4685: fnd_message.set_token ('COL_NAME', 'APPLICATION_SHORT_NAME');
4686: fnd_msg_pub.add;
4687: END IF;
4688:
4689: x_return_status := fnd_api.g_ret_sts_error;
4690: END IF;
4691:
4692: -- Check whether the enabled_flag is specified
4693: --
4691:
4692: -- Check whether the enabled_flag is specified
4693: --
4694: IF (p_terr_type_rec.enabled_flag IS NULL)
4695: OR (p_terr_type_rec.enabled_flag = fnd_api.g_miss_char)
4696: THEN
4697: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4698: THEN
4699: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4700: fnd_message.set_token ('COL_NAME', 'ENABLED_FLAG');
4701: fnd_msg_pub.add;
4702: END IF;
4703:
4704: x_return_status := fnd_api.g_ret_sts_error;
4705: END IF;
4706:
4707: /* -- Check for ORG_ID - Not Required: ORG_ID is NULLable
4708: IF (p_terr_type_rec.org_id IS NULL)
4705: END IF;
4706:
4707: /* -- Check for ORG_ID - Not Required: ORG_ID is NULLable
4708: IF (p_terr_type_rec.org_id IS NULL)
4709: OR (p_terr_type_rec.org_id = fnd_api.g_miss_num)
4710: THEN
4711: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4712: THEN
4713: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4714: fnd_message.set_token ('COL_NAME', 'ORG_ID');
4715: fnd_msg_pub.add;
4716: END IF;
4717:
4718: x_return_status := fnd_api.g_ret_sts_error;
4719: END IF;
4720: */
4721:
4722: --Check created by
4720: */
4721:
4722: --Check created by
4723: IF ( p_terr_type_rec.created_by IS NULL
4724: OR p_terr_type_rec.created_by = fnd_api.g_miss_num)
4725: THEN
4726: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4727: THEN
4728: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4729: fnd_message.set_token ('COL_NAME', 'CREATED_BY');
4730: fnd_msg_pub.add;
4731: END IF;
4732:
4733: x_return_status := fnd_api.g_ret_sts_error;
4734: END IF;
4735:
4736: --Check creation date
4737: IF ( p_terr_type_rec.creation_date IS NULL
4734: END IF;
4735:
4736: --Check creation date
4737: IF ( p_terr_type_rec.creation_date IS NULL
4738: OR p_terr_type_rec.creation_date = fnd_api.g_miss_date)
4739: THEN
4740: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4741: THEN
4742: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4743: fnd_message.set_token ('COL_NAME', 'CREATION_DATE');
4744: fnd_msg_pub.add;
4745: END IF;
4746:
4747: x_return_status := fnd_api.g_ret_sts_error;
4748: END IF;
4749:
4750: -- Validate last updated by
4751: IF ( p_terr_type_rec.last_updated_by IS NULL
4748: END IF;
4749:
4750: -- Validate last updated by
4751: IF ( p_terr_type_rec.last_updated_by IS NULL
4752: OR p_terr_type_rec.last_updated_by = fnd_api.g_miss_num)
4753: THEN
4754: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4755: THEN
4756: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4757: fnd_message.set_token ('COL_NAME', 'LAST_UPDATED_BY');
4758: fnd_msg_pub.add;
4759: END IF;
4760:
4761: x_return_status := fnd_api.g_ret_sts_error;
4762: END IF;
4763:
4764: -- Check last update date
4765: IF ( p_terr_type_rec.last_update_date IS NULL
4762: END IF;
4763:
4764: -- Check last update date
4765: IF ( p_terr_type_rec.last_update_date IS NULL
4766: OR p_terr_type_rec.last_update_date = fnd_api.g_miss_date)
4767: THEN
4768: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4769: THEN
4770: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4771: fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_DATE');
4772: fnd_msg_pub.add;
4773: END IF;
4774:
4775: x_return_status := fnd_api.g_ret_sts_error;
4776: END IF;
4777:
4778: --Check last update login
4779: IF ( p_terr_type_rec.last_update_login IS NULL
4776: END IF;
4777:
4778: --Check last update login
4779: IF ( p_terr_type_rec.last_update_login IS NULL
4780: OR p_terr_type_rec.last_update_login = fnd_api.g_miss_num)
4781: THEN
4782: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4783: THEN
4784: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4785: fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
4786: fnd_msg_pub.add;
4787: END IF;
4788:
4789: x_return_status := fnd_api.g_ret_sts_error;
4790: END IF;
4791:
4792: -- Since the message stack is already set
4793: --
4793: --
4794: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4795: EXCEPTION
4796: --
4797: WHEN fnd_api.g_exc_error
4798: THEN
4799: --dbms_output.put_line (
4800: -- 'Validate_TerrType_Header: FND_API.G_EXC_ERROR'
4801: --);
4796: --
4797: WHEN fnd_api.g_exc_error
4798: THEN
4799: --dbms_output.put_line (
4800: -- 'Validate_TerrType_Header: FND_API.G_EXC_ERROR'
4801: --);
4802: x_return_status := fnd_api.g_ret_sts_error;
4803: fnd_msg_pub.count_and_get (
4804: p_count => x_msg_count,
4798: THEN
4799: --dbms_output.put_line (
4800: -- 'Validate_TerrType_Header: FND_API.G_EXC_ERROR'
4801: --);
4802: x_return_status := fnd_api.g_ret_sts_error;
4803: fnd_msg_pub.count_and_get (
4804: p_count => x_msg_count,
4805: p_data => x_msg_data
4806: );
4803: fnd_msg_pub.count_and_get (
4804: p_count => x_msg_count,
4805: p_data => x_msg_data
4806: );
4807: WHEN fnd_api.g_exc_unexpected_error
4808: THEN
4809: --dbms_output.put_line (
4810: -- 'Validate_TerrType_Header: FND_API.G_EXC_UNEXPECTED_ERROR'
4811: --);
4806: );
4807: WHEN fnd_api.g_exc_unexpected_error
4808: THEN
4809: --dbms_output.put_line (
4810: -- 'Validate_TerrType_Header: FND_API.G_EXC_UNEXPECTED_ERROR'
4811: --);
4812: x_return_status := fnd_api.g_ret_sts_unexp_error;
4813: fnd_msg_pub.count_and_get (
4814: p_count => x_msg_count,
4808: THEN
4809: --dbms_output.put_line (
4810: -- 'Validate_TerrType_Header: FND_API.G_EXC_UNEXPECTED_ERROR'
4811: --);
4812: x_return_status := fnd_api.g_ret_sts_unexp_error;
4813: fnd_msg_pub.count_and_get (
4814: p_count => x_msg_count,
4815: p_data => x_msg_data
4816: );
4818: THEN
4819: --dbms_output.put_line (
4820: -- 'Validate_TerrType_Header: OTHERS - ' || SQLERRM
4821: --);
4822: x_return_status := fnd_api.g_ret_sts_unexp_error;
4823: fnd_msg_pub.count_and_get (
4824: p_count => x_msg_count,
4825: p_data => x_msg_data
4826: );
4835: PROCEDURE validate_qualifier (
4836: p_terrtypequal_rec IN terrtypequal_rec_type
4837: := g_miss_terrtypequal_rec,
4838: p_terr_type_id IN NUMBER,
4839: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
4840: x_return_status OUT NOCOPY VARCHAR2,
4841: x_msg_count OUT NOCOPY NUMBER,
4842: x_msg_data OUT NOCOPY VARCHAR2
4843: )
4846: l_temp VARCHAR2(01);
4847: BEGIN
4848: --dbms_output.put_line ('Validate_Qualifier: Entering API - p_Terr_Type_Id ' || to_char(p_Terr_Type_Id));
4849: -- Initialize the status to success
4850: x_return_status := fnd_api.g_ret_sts_success;
4851: --
4852: --dbms_output.put_line (
4853: -- 'Validate P_TerrTypequal_Rec.Qual_Usg_Id - ' ||
4854: -- TO_CHAR (p_terrtypequal_rec.qual_usg_id)
4879: THEN
4880: --dbms_output.put_line (
4881: -- 'Validate_Qualifier: NO_DATA_FOUND Exception'
4882: --);
4883: x_return_status := fnd_api.g_ret_sts_error;
4884: fnd_message.set_name ('JTF', 'JTF_TERR_DISABLED_TERR_QUAL');
4885: fnd_msg_pub.add;
4886: fnd_msg_pub.count_and_get (
4887: p_count => x_msg_count,
4890: END;
4891:
4892: /* -- Check for ORG_ID - not required: ORG_ID is NULLable
4893: IF ( p_terrtypequal_rec.org_id IS NULL
4894: OR p_terrtypequal_rec.org_id = fnd_api.g_miss_num)
4895: THEN
4896: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4897: THEN
4898: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4899: fnd_message.set_token ('COL_NAME', 'ORG_ID');
4900: fnd_msg_pub.add;
4901: END IF;
4902:
4903: x_return_status := fnd_api.g_ret_sts_error;
4904: END IF;
4905: */
4906:
4907: --Check created by
4905: */
4906:
4907: --Check created by
4908: IF ( p_terrtypequal_rec.created_by IS NULL
4909: OR p_terrtypequal_rec.created_by = fnd_api.g_miss_num)
4910: THEN
4911: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4912: THEN
4913: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4914: fnd_message.set_token ('COL_NAME', 'CREATED_BY');
4915: fnd_msg_pub.add;
4916: END IF;
4917:
4918: x_return_status := fnd_api.g_ret_sts_error;
4919: END IF;
4920:
4921: --Check creation date
4922: IF ( p_terrtypequal_rec.creation_date IS NULL
4919: END IF;
4920:
4921: --Check creation date
4922: IF ( p_terrtypequal_rec.creation_date IS NULL
4923: OR p_terrtypequal_rec.creation_date = fnd_api.g_miss_date)
4924: THEN
4925: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4926: THEN
4927: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4928: fnd_message.set_token ('COL_NAME', 'CREATION_DATE');
4929: fnd_msg_pub.add;
4930: END IF;
4931:
4932: x_return_status := fnd_api.g_ret_sts_error;
4933: END IF;
4934:
4935: -- Validate last updated by
4936: IF ( p_terrtypequal_rec.last_updated_by IS NULL
4933: END IF;
4934:
4935: -- Validate last updated by
4936: IF ( p_terrtypequal_rec.last_updated_by IS NULL
4937: OR p_terrtypequal_rec.last_updated_by = fnd_api.g_miss_num)
4938: THEN
4939: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4940: THEN
4941: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4942: fnd_message.set_token ('COL_NAME', 'LAST_UPDATED_BY');
4943: fnd_msg_pub.add;
4944: END IF;
4945:
4946: x_return_status := fnd_api.g_ret_sts_error;
4947: END IF;
4948:
4949: -- Check last update date
4950: IF ( p_terrtypequal_rec.last_update_date IS NULL
4947: END IF;
4948:
4949: -- Check last update date
4950: IF ( p_terrtypequal_rec.last_update_date IS NULL
4951: OR p_terrtypequal_rec.last_update_date = fnd_api.g_miss_date)
4952: THEN
4953: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4954: THEN
4955: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4956: fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_DATE');
4957: fnd_msg_pub.add;
4958: END IF;
4959:
4960: x_return_status := fnd_api.g_ret_sts_error;
4961: END IF;
4962:
4963: --Check last update login
4964: IF ( p_terrtypequal_rec.last_update_login IS NULL
4961: END IF;
4962:
4963: --Check last update login
4964: IF ( p_terrtypequal_rec.last_update_login IS NULL
4965: OR p_terrtypequal_rec.last_update_login = fnd_api.g_miss_num)
4966: THEN
4967: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
4968: THEN
4969: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
4970: fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
4971: fnd_msg_pub.add;
4972: END IF;
4973:
4974: x_return_status := fnd_api.g_ret_sts_error;
4975: END IF;
4976:
4977: --
4978: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4981: --
4982: WHEN OTHERS
4983: THEN
4984: --dbms_output.put_line ('Validate_Qualifier: Others Exception');
4985: x_return_status := fnd_api.g_ret_sts_unexp_error;
4986: fnd_message.set_name ('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
4987: fnd_msg_pub.add;
4988: fnd_msg_pub.count_and_get (
4989: p_count => x_msg_count,
5002: -- Make sure the Territory Type Id is valid
5003: -- Make sure the territory Type usage Id is Valid
5004: ---------------------------------------------------------------------
5005: PROCEDURE validate_terrtype_usage (
5006: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
5007: x_return_status OUT NOCOPY VARCHAR2,
5008: x_msg_count OUT NOCOPY NUMBER,
5009: x_msg_data OUT NOCOPY VARCHAR2,
5010: p_terrtypeusgs_rec IN terrtypeusgs_rec_type
5016: l_validate_id NUMBER;
5017: BEGIN
5018: --dbms_output.put_line ('Validate_TerrType_Usage: Entering API');
5019: -- Initialize the status to success
5020: x_return_status := fnd_api.g_ret_sts_success;
5021:
5022: -- Validate the territory Id
5023: IF p_terr_type_id IS NOT NULL
5024: THEN
5028: l_validate_id,
5029: 'TERR_TYPE_ID',
5030: 'JTF_TERR_TYPES'
5031: ) <>
5032: fnd_api.g_true
5033: THEN
5034: --dbms_output.put_line (
5035: -- 'Validate_Territory_Usage: l_status <> FND_API.G_TRUE'
5036: --);
5031: ) <>
5032: fnd_api.g_true
5033: THEN
5034: --dbms_output.put_line (
5035: -- 'Validate_Territory_Usage: l_status <> FND_API.G_TRUE'
5036: --);
5037: fnd_message.set_name ('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5038: fnd_message.set_token ('TABLE_NAME', 'JTF_TERR_TYPES');
5039: fnd_message.set_token ('COLUMN_NAME', 'TERR_TYPE_ID');
5037: fnd_message.set_name ('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5038: fnd_message.set_token ('TABLE_NAME', 'JTF_TERR_TYPES');
5039: fnd_message.set_token ('COLUMN_NAME', 'TERR_TYPE_ID');
5040: fnd_msg_pub.add;
5041: x_return_status := fnd_api.g_ret_sts_error;
5042: END IF;
5043:
5044: --dbms_output.put_line (
5045: -- 'Validate_Territory_Usage: TERR_TYPE_ID(' ||
5056: l_validate_id,
5057: 'SOURCE_ID',
5058: 'JTF_SOURCES'
5059: ) <>
5060: fnd_api.g_true
5061: THEN
5062: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5063: THEN
5064: --dbms_output.put_line ('Validate_Territory_Usage: FND_MSG_PUB.ADD');
5067: fnd_message.set_token ('COLUMN_NAME', 'SOURCE_ID');
5068: fnd_msg_pub.add;
5069: END IF;
5070:
5071: x_return_status := fnd_api.g_ret_sts_error;
5072: END IF;
5073:
5074: /* -- Check for ORG_ID - Not Required: ORG_ID is NULLable
5075: IF (p_terrtypeusgs_rec.org_id IS NULL)
5072: END IF;
5073:
5074: /* -- Check for ORG_ID - Not Required: ORG_ID is NULLable
5075: IF (p_terrtypeusgs_rec.org_id IS NULL)
5076: OR (p_terrtypeusgs_rec.org_id = fnd_api.g_miss_num)
5077: THEN
5078: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5079: THEN
5080: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5081: fnd_message.set_token ('COL_NAME', 'ORG_ID');
5082: fnd_msg_pub.add;
5083: END IF;
5084:
5085: x_return_status := fnd_api.g_ret_sts_error;
5086: END IF;
5087: */
5088:
5089: --Check created by
5087: */
5088:
5089: --Check created by
5090: IF ( p_terrtypeusgs_rec.created_by IS NULL
5091: OR p_terrtypeusgs_rec.created_by = fnd_api.g_miss_num)
5092: THEN
5093: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5094: THEN
5095: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5096: fnd_message.set_token ('COL_NAME', 'CREATED_BY');
5097: fnd_msg_pub.add;
5098: END IF;
5099:
5100: x_return_status := fnd_api.g_ret_sts_error;
5101: END IF;
5102:
5103: --Check creation date
5104: IF ( p_terrtypeusgs_rec.creation_date IS NULL
5101: END IF;
5102:
5103: --Check creation date
5104: IF ( p_terrtypeusgs_rec.creation_date IS NULL
5105: OR p_terrtypeusgs_rec.creation_date = fnd_api.g_miss_date)
5106: THEN
5107: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5108: THEN
5109: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5110: fnd_message.set_token ('COL_NAME', 'CREATION_DATE');
5111: fnd_msg_pub.add;
5112: END IF;
5113:
5114: x_return_status := fnd_api.g_ret_sts_error;
5115: END IF;
5116:
5117: -- Validate last updated by
5118: IF ( p_terrtypeusgs_rec.last_updated_by IS NULL
5115: END IF;
5116:
5117: -- Validate last updated by
5118: IF ( p_terrtypeusgs_rec.last_updated_by IS NULL
5119: OR p_terrtypeusgs_rec.last_updated_by = fnd_api.g_miss_num)
5120: THEN
5121: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5122: THEN
5123: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5124: fnd_message.set_token ('COL_NAME', 'LAST_UPDATED_BY');
5125: fnd_msg_pub.add;
5126: END IF;
5127:
5128: x_return_status := fnd_api.g_ret_sts_error;
5129: END IF;
5130:
5131: -- Check last update date
5132: IF ( p_terrtypeusgs_rec.last_update_date IS NULL
5129: END IF;
5130:
5131: -- Check last update date
5132: IF ( p_terrtypeusgs_rec.last_update_date IS NULL
5133: OR p_terrtypeusgs_rec.last_update_date = fnd_api.g_miss_date)
5134: THEN
5135: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5136: THEN
5137: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5138: fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_DATE');
5139: fnd_msg_pub.add;
5140: END IF;
5141:
5142: x_return_status := fnd_api.g_ret_sts_error;
5143: END IF;
5144:
5145: --Check last update login
5146: IF ( p_terrtypeusgs_rec.last_update_login IS NULL
5143: END IF;
5144:
5145: --Check last update login
5146: IF ( p_terrtypeusgs_rec.last_update_login IS NULL
5147: OR p_terrtypeusgs_rec.last_update_login = fnd_api.g_miss_num)
5148: THEN
5149: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5150: THEN
5151: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5152: fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
5153: fnd_msg_pub.add;
5154: END IF;
5155:
5156: x_return_status := fnd_api.g_ret_sts_error;
5157: END IF;
5158:
5159: --
5160: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5159: --
5160: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5161: EXCEPTION
5162: --
5163: WHEN fnd_api.g_exc_error
5164: THEN
5165: --dbms_output.put_line ('Validate_TerrType_Usage: FND_API.G_EXC_ERROR');
5166: x_return_status := fnd_api.g_ret_sts_error;
5167: fnd_msg_pub.count_and_get (
5161: EXCEPTION
5162: --
5163: WHEN fnd_api.g_exc_error
5164: THEN
5165: --dbms_output.put_line ('Validate_TerrType_Usage: FND_API.G_EXC_ERROR');
5166: x_return_status := fnd_api.g_ret_sts_error;
5167: fnd_msg_pub.count_and_get (
5168: p_count => x_msg_count,
5169: p_data => x_msg_data
5162: --
5163: WHEN fnd_api.g_exc_error
5164: THEN
5165: --dbms_output.put_line ('Validate_TerrType_Usage: FND_API.G_EXC_ERROR');
5166: x_return_status := fnd_api.g_ret_sts_error;
5167: fnd_msg_pub.count_and_get (
5168: p_count => x_msg_count,
5169: p_data => x_msg_data
5170: );
5167: fnd_msg_pub.count_and_get (
5168: p_count => x_msg_count,
5169: p_data => x_msg_data
5170: );
5171: WHEN fnd_api.g_exc_unexpected_error
5172: THEN
5173: --dbms_output.put_line (
5174: -- 'Validate_TerrType_Usage: FND_API.G_EXC_UNEXPECTED_ERROR'
5175: --);
5170: );
5171: WHEN fnd_api.g_exc_unexpected_error
5172: THEN
5173: --dbms_output.put_line (
5174: -- 'Validate_TerrType_Usage: FND_API.G_EXC_UNEXPECTED_ERROR'
5175: --);
5176: x_return_status := fnd_api.g_ret_sts_unexp_error;
5177: fnd_msg_pub.count_and_get (
5178: p_count => x_msg_count,
5172: THEN
5173: --dbms_output.put_line (
5174: -- 'Validate_TerrType_Usage: FND_API.G_EXC_UNEXPECTED_ERROR'
5175: --);
5176: x_return_status := fnd_api.g_ret_sts_unexp_error;
5177: fnd_msg_pub.count_and_get (
5178: p_count => x_msg_count,
5179: p_data => x_msg_data
5180: );
5182: THEN
5183: --dbms_output.put_line (
5184: -- 'Validate_TerrType_Usage: OTHERS - ' || SQLERRM
5185: --);
5186: x_return_status := fnd_api.g_ret_sts_unexp_error;
5187: fnd_msg_pub.count_and_get (
5188: p_count => x_msg_count,
5189: p_data => x_msg_data
5190: );
5200: -- Make sure the Territory Id is valid
5201: -- Make sure the QUAL_TYPE_USG_ID is valid
5202: ---------------------------------------------------------------------
5203: PROCEDURE validate_type_qtype_usage (
5204: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
5205: x_return_status OUT NOCOPY VARCHAR2,
5206: x_msg_count OUT NOCOPY NUMBER,
5207: x_msg_data OUT NOCOPY VARCHAR2,
5208: p_type_qualtypeusgs_rec IN typequaltypeusgs_rec_type
5217: l_qual_type_usg_id NUMBER;
5218: BEGIN
5219: --dbms_output.put_line ('Validate_Type_Qtype_Usage: Entering API - p_Terr_Type_Id ' || to_char(p_Terr_Type_Id));
5220: -- Initialize the status to success
5221: x_return_status := fnd_api.g_ret_sts_success;
5222:
5223: -- This block will validate territory
5224: -- qual_Type_Usg_id specified
5225: BEGIN
5235: WHEN NO_DATA_FOUND
5236: THEN
5237: fnd_message.set_name ('JTF', 'JTF_TERR_INVALID_TERR_QTYPE');
5238: fnd_msg_pub.add;
5239: RAISE fnd_api.g_exc_unexpected_error;
5240: END;
5241:
5242: -- Validate the territory Id
5243: l_validate_id := p_terr_type_id;
5254: l_validate_id,
5255: 'TERR_TYPE_ID',
5256: 'JTF_TERR_TYPES'
5257: ) <>
5258: fnd_api.g_true
5259: THEN
5260: --dbms_output.put_line (
5261: -- 'Validate_Foreign_Key: l_status <> FND_API.G_TRUE'
5262: --);
5257: ) <>
5258: fnd_api.g_true
5259: THEN
5260: --dbms_output.put_line (
5261: -- 'Validate_Foreign_Key: l_status <> FND_API.G_TRUE'
5262: --);
5263:
5264: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5265: THEN
5268: fnd_message.set_token ('COLUMN_NAME', 'TERR_TYPE_ID');
5269: fnd_msg_pub.add;
5270: END IF;
5271:
5272: x_return_status := fnd_api.g_ret_sts_error;
5273: END IF;
5274: END IF;
5275:
5276: --
5276: --
5277: --
5278: /* -- Check for ORG_ID - Not Required: ORG_ID is NULLable
5279: IF (p_type_qualtypeusgs_rec.org_id IS NULL)
5280: OR (p_type_qualtypeusgs_rec.org_id = fnd_api.g_miss_num)
5281: THEN
5282: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5283: THEN
5284: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5285: fnd_message.set_token ('COL_NAME', 'ORG_ID');
5286: fnd_msg_pub.add;
5287: END IF;
5288:
5289: x_return_status := fnd_api.g_ret_sts_error;
5290: END IF;
5291: */
5292:
5293: --Check created by
5291: */
5292:
5293: --Check created by
5294: IF ( p_type_qualtypeusgs_rec.created_by IS NULL
5295: OR p_type_qualtypeusgs_rec.created_by = fnd_api.g_miss_num)
5296: THEN
5297: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5298: THEN
5299: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5300: fnd_message.set_token ('COL_NAME', 'CREATED_BY');
5301: fnd_msg_pub.add;
5302: END IF;
5303:
5304: x_return_status := fnd_api.g_ret_sts_error;
5305: END IF;
5306:
5307: --Check creation date
5308: IF ( p_type_qualtypeusgs_rec.creation_date IS NULL
5305: END IF;
5306:
5307: --Check creation date
5308: IF ( p_type_qualtypeusgs_rec.creation_date IS NULL
5309: OR p_type_qualtypeusgs_rec.creation_date = fnd_api.g_miss_date)
5310: THEN
5311: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5312: THEN
5313: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5314: fnd_message.set_token ('COL_NAME', 'CREATION_DATE');
5315: fnd_msg_pub.add;
5316: END IF;
5317:
5318: x_return_status := fnd_api.g_ret_sts_error;
5319: END IF;
5320:
5321: -- Validate last updated by
5322: IF ( p_type_qualtypeusgs_rec.last_updated_by IS NULL
5319: END IF;
5320:
5321: -- Validate last updated by
5322: IF ( p_type_qualtypeusgs_rec.last_updated_by IS NULL
5323: OR p_type_qualtypeusgs_rec.last_updated_by = fnd_api.g_miss_num)
5324: THEN
5325: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5326: THEN
5327: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5328: fnd_message.set_token ('COL_NAME', 'LAST_UPDATED_BY');
5329: fnd_msg_pub.add;
5330: END IF;
5331:
5332: x_return_status := fnd_api.g_ret_sts_error;
5333: END IF;
5334:
5335: -- Check last update date
5336: IF ( p_type_qualtypeusgs_rec.last_update_date IS NULL
5333: END IF;
5334:
5335: -- Check last update date
5336: IF ( p_type_qualtypeusgs_rec.last_update_date IS NULL
5337: OR p_type_qualtypeusgs_rec.last_update_date = fnd_api.g_miss_date)
5338: THEN
5339: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5340: THEN
5341: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5342: fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_DATE');
5343: fnd_msg_pub.add;
5344: END IF;
5345:
5346: x_return_status := fnd_api.g_ret_sts_error;
5347: END IF;
5348:
5349: --Check last update login
5350: IF ( p_type_qualtypeusgs_rec.last_update_login IS NULL
5347: END IF;
5348:
5349: --Check last update login
5350: IF ( p_type_qualtypeusgs_rec.last_update_login IS NULL
5351: OR p_type_qualtypeusgs_rec.last_update_login = fnd_api.g_miss_num)
5352: THEN
5353: IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5354: THEN
5355: fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5356: fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
5357: fnd_msg_pub.add;
5358: END IF;
5359:
5360: x_return_status := fnd_api.g_ret_sts_error;
5361: END IF;
5362:
5363: --
5364: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5363: --
5364: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5365: EXCEPTION
5366: --
5367: WHEN fnd_api.g_exc_error
5368: THEN
5369: --dbms_output.put_line (
5370: -- 'Validate_Type_Qtype_Usage: FND_API.G_EXC_ERROR'
5371: --);
5366: --
5367: WHEN fnd_api.g_exc_error
5368: THEN
5369: --dbms_output.put_line (
5370: -- 'Validate_Type_Qtype_Usage: FND_API.G_EXC_ERROR'
5371: --);
5372: x_return_status := fnd_api.g_ret_sts_error;
5373: fnd_msg_pub.count_and_get (
5374: p_count => x_msg_count,
5368: THEN
5369: --dbms_output.put_line (
5370: -- 'Validate_Type_Qtype_Usage: FND_API.G_EXC_ERROR'
5371: --);
5372: x_return_status := fnd_api.g_ret_sts_error;
5373: fnd_msg_pub.count_and_get (
5374: p_count => x_msg_count,
5375: p_data => x_msg_data
5376: );
5373: fnd_msg_pub.count_and_get (
5374: p_count => x_msg_count,
5375: p_data => x_msg_data
5376: );
5377: WHEN fnd_api.g_exc_unexpected_error
5378: THEN
5379: --dbms_output.put_line (
5380: -- 'Validate_Type_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR'
5381: --);
5376: );
5377: WHEN fnd_api.g_exc_unexpected_error
5378: THEN
5379: --dbms_output.put_line (
5380: -- 'Validate_Type_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR'
5381: --);
5382: x_return_status := fnd_api.g_ret_sts_unexp_error;
5383: fnd_msg_pub.count_and_get (
5384: p_count => x_msg_count,
5378: THEN
5379: --dbms_output.put_line (
5380: -- 'Validate_Type_Qtype_Usage: FND_API.G_EXC_UNEXPECTED_ERROR'
5381: --);
5382: x_return_status := fnd_api.g_ret_sts_unexp_error;
5383: fnd_msg_pub.count_and_get (
5384: p_count => x_msg_count,
5385: p_data => x_msg_data
5386: );
5388: THEN
5389: --dbms_output.put_line (
5390: -- 'Validate_Type_Qtype_Usage: OTHERS - ' || SQLERRM
5391: --);
5392: x_return_status := fnd_api.g_ret_sts_unexp_error;
5393: fnd_msg_pub.count_and_get (
5394: p_count => x_msg_count,
5395: p_data => x_msg_data
5396: );
5402: -- This procedure is called from the form before
5403: -- deleting a territory Type
5404: --
5405: PROCEDURE is_terrtype_deletable (
5406: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
5407: p_terrtype_id IN NUMBER,
5408: x_return_status OUT NOCOPY VARCHAR2,
5409: x_msg_count OUT NOCOPY VARCHAR2,
5410: x_msg_data OUT NOCOPY VARCHAR2
5412: AS
5413: l_count NUMBER;
5414: BEGIN
5415: -- Initialize the status to success
5416: x_return_status := fnd_api.g_ret_sts_success;
5417: --
5418: SELECT COUNT (*)
5419: INTO l_count
5420: FROM jtf_terr
5423: --
5424: -- If there are therritories that use this territory Type
5425: IF l_count > 0
5426: THEN
5427: x_return_status := fnd_api.g_ret_sts_error;
5428: ELSE
5429: x_return_status := fnd_api.g_ret_sts_success;
5430: END IF;
5431:
5425: IF l_count > 0
5426: THEN
5427: x_return_status := fnd_api.g_ret_sts_error;
5428: ELSE
5429: x_return_status := fnd_api.g_ret_sts_success;
5430: END IF;
5431:
5432: --
5433: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5432: --
5433: fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5434: EXCEPTION
5435: --
5436: WHEN fnd_api.g_exc_error
5437: THEN
5438: x_return_status := fnd_api.g_ret_sts_error;
5439: fnd_msg_pub.count_and_get (
5440: p_count => x_msg_count,
5434: EXCEPTION
5435: --
5436: WHEN fnd_api.g_exc_error
5437: THEN
5438: x_return_status := fnd_api.g_ret_sts_error;
5439: fnd_msg_pub.count_and_get (
5440: p_count => x_msg_count,
5441: p_data => x_msg_data
5442: );
5441: p_data => x_msg_data
5442: );
5443: WHEN OTHERS
5444: THEN
5445: x_return_status := fnd_api.g_ret_sts_unexp_error;
5446: fnd_message.set_name ('JTF', 'JTF_TERR_UNKNOWN_ERROR');
5447: fnd_message.set_name ('P_TEXT', SQLERRM);
5448: fnd_msg_pub.add;
5449: fnd_msg_pub.count_and_get (