[Home] [Help]
PACKAGE BODY: APPS.JTF_TERRITORY_TYPE_PVT
Source
1 PACKAGE BODY JTF_TERRITORY_TYPE_PVT AS
2 /* $Header: jtfvttyb.pls 120.0 2005/06/02 18:23:08 appldev ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERRITORY_TYPE_PVT
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force core territory manager public api's.
10 -- This package is a public API for inserting territory type
11 -- related information in to information into JTF tables.
12 -- It contains specification for pl/sql records and tables
13 -- and the Public territory related API's.
14 --
15 -- PROCEDURE:
16 -- (see below for specification)
17 --
18 -- NOTES
19 -- This package is for private use only
20 --
21 -- HISTORY
22 -- 07/29/99 VNEDUNGA Created
23 -- 11/29/99 Vnedunga Added a new validation routine
24 -- Is_TerrType_Deletable
25 -- 01/25/00 VNEDUNGA Adding Copy terr Type procedure
26 -- 02/10/00 VNEDUNGA Change the call to TABLE HANDLER
27 -- to pass in the dates from the record
28 -- passed
29 -- 02/20/00 VNEDUNGA Chaging the Insert_Row/Update_Row
30 -- to pass org_id to table handler
31 -- 02/25/00 VNEDUNGA Fixing the Insert_row and update_row
32 -- to use the id passed to the API
33 -- 03/09/00 VNEDUNGA Chnages to validaton routines
34 -- 05/02/00 VNEDUNGA Addding rownum < 2 in validate_qualifer
35 -- 07/20/00 JDOCHERT Changed as follows in Create_terrtype_record
36 -- as this meant that a terr_type_id passed
37 -- into Create API was ignored:
38 -- l_terr_type_id := 0;
39 -- TO
40 -- l_terrtype_id NUMBER := P_TERRTYPE_REC.TERR_TYPE_ID;
41 --
42 -- End of Comments
43 -- ***************************************************
44 -- GLOBAL VARIABLES
45 -- ***************************************************
46 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_TERRITORY_TYPE_PVT';
47 g_file_name CONSTANT VARCHAR2(12) := 'jtfvttyb.pls';
48 g_appl_id NUMBER := fnd_global.prog_appl_id;
49 g_login_id NUMBER := fnd_global.conc_login_id;
50 g_program_id NUMBER := fnd_global.conc_program_id;
51 g_user_id NUMBER := fnd_global.user_id;
52 g_request_id NUMBER := fnd_global.conc_request_id;
53 g_app_short_name VARCHAR2(15)
54 := fnd_global.application_short_name;
55
56 -- ***************************************************
57 -- start of comments
58 -- ***************************************************
59 -- api name : create_terrtype
60 -- type : public.
61 -- function : creates territory type defnitions
62 -- pre-reqs : Territory qualifiers has to be enabled.
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
72 -- p_TypequalTypeusgs_tbl in TypeQualTypeUsgs_tbl_type required --default = g_miss_tersrc_tbl,
73 --
74 -- out:
75 -- p_return_status out varchar2(1)
76 -- p_msg_count out number
77 -- p_msg_data out varchar2(2000)
78 -- p_TerrType_id out number
79 -- p_TerrTypequal_out_tbl out TerrTypequal_out_tbl_type
80 -- p_TerrTypeusgs_out_tbl out TerrTypeusgs_out_tbl_type
81 -- p_TypeQualTypeUsgs_out_tbl out TypeQualTypeUsgs_out_tbl_type
82 --
83 -- requirements :
84 -- business rules :
85
86 -- version : current version 1.0
87 -- initial version: initial version 1.0
88 --
89 -- notes: api for creating territory types
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,
99 p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
100 p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2,
104 x_terrtype_id OUT NOCOPY NUMBER,
105 x_terrtypequal_out_tbl OUT NOCOPY terrtypequal_out_tbl_type,
106 x_terrtypeusgs_out_tbl OUT NOCOPY terrtypeusgs_out_tbl_type,
107 x_typequaltypeusgs_out_tbl OUT NOCOPY typequaltypeusgs_out_tbl_type
108 )
109 AS
110 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrType';
111 l_api_version_number CONSTANT NUMBER := 1.0;
112 -- Status Local Variables
113 l_return_status VARCHAR2(1); -- Return value from procedures
114 l_return_status_full VARCHAR2(1); -- Calculated return status from
115
116 -- all return values
117 l_terrtype_out_rec terrtype_out_rec_type;
118 l_terrtypeusgs_out_tbl terrtypeusgs_out_tbl_type;
119 l_typequaltypeusgs_out_tbl typequaltypeusgs_out_tbl_type;
120 l_terrtypequal_out_tbl terrtypequal_out_tbl_type;
121 l_terrtypequal_tbl terrtypequal_tbl_type;
122 l_terrtype_id NUMBER := 0;
123 l_terrtypequal_id NUMBER := 0;
124 l_qual_counter NUMBER := 1;
125 l_counter NUMBER := 0;
126 l_index NUMBER := 0;
127 l_msg_count NUMBER;
128 l_msg_data VARCHAR2(2000);
129 BEGIN
130 --dbms_ourtput.put_line('create_terrtype PVT: Entering API');
131
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
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)
148 THEN
149 fnd_msg_pub.initialize;
150 END IF;
151
152 -- Debug Message
153 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
154 THEN
155 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
156 fnd_message.set_name ('PROC_NAME', l_api_name);
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 --
166 --
167 -- If incomming data is good
168 -- Start creating territory related records
169 --
170 --dbms_ourtput.put_line('create_terrtype PVT: Before Calling Create_TerrType_Header PVT');
171 --
172 create_terrtype_header (
173 p_api_version_number => p_api_version_number,
174 p_init_msg_list => p_init_msg_list,
175 p_commit => p_commit,
176 p_validation_level => p_validation_level,
177 p_terrtype_rec => p_terrtype_rec,
178 p_terrtypeusgs_tbl => p_terrtypeusgs_tbl,
179 p_typequaltypeusgs_tbl => p_typequaltypeusgs_tbl,
180 x_return_status => l_return_status,
181 x_msg_count => l_msg_count,
182 x_msg_data => l_msg_data,
183 x_terrtype_out_rec => l_terrtype_out_rec,
184 x_terrtypeusgs_out_tbl => l_terrtypeusgs_out_tbl,
185 x_typequaltypeusgs_out_tbl => l_typequaltypeusgs_out_tbl
186 );
187 --Save the territory id for later use
188 l_terrtype_id := l_terrtype_out_rec.terr_type_id;
189 x_terrtype_id := l_terrtype_out_rec.terr_type_id;
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
199 --dbms_ourtput.put_line('create_terrtype PVT: Before Calling Create_TerrType_Qualifier PVT');
200 create_terrtype_qualifier (
201 p_api_version_number => p_api_version_number,
202 p_init_msg_list => p_init_msg_list,
203 p_commit => p_commit,
204 p_validation_level => p_validation_level,
205 p_terrtype_id => l_terrtype_id,
206 p_terrtypequal_tbl => p_terrtypequal_tbl,
207 x_return_status => l_return_status,
208 x_msg_count => x_msg_count,
209 x_msg_data => x_msg_data,
210 x_terrtypequal_out_tbl => l_terrtypequal_out_tbl
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
220 -- Debug Message
221 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
222 THEN
223 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
224 fnd_message.set_name ('PROC_NAME', l_api_name);
225 fnd_msg_pub.add;
226 END IF;
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');
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,
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,
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 );
265 --
266
267 END create_terrtype;
268
269 -- ***************************************************
270 -- start of comments
271 -- ***************************************************
272 -- api name : update_Terrtype
273 -- type : public.
274 -- function : Update territory type defnitions
275 -- pre-reqs : Territory qualifiers has to be enabled.
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
285 -- p_TerrTypeSrcType_tbl in TerrTypeSrcType_tbl_type required --default = g_miss_tersrc_tbl,
286 -- out:
287 -- p_return_status out varchar2(1)
288 -- p_msg_count out number
289 -- p_msg_data out varchar2(2000)
290 -- p_TerrTypequal_out_tbl out TerrTypequal_out_tbl_type,
291 -- p_TerrTypesrc_out_tbl out TerrTypeSrc_out_tbl_type,
292 -- p_TerrTypeSrcType_out_tbl out TerrTypeSrcType_out_tbl_type
293 --
294 -- requirements :
295 -- business rules :
296
297 -- version : current version 1.0
298 -- initial version: initial version 1.0
299 --
300 -- notes: api for Updating territory types
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,
310 p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
311 p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2,
315 x_terrtype_out_rec OUT NOCOPY terrtype_out_rec_type,
316 x_terrtypequal_out_tbl OUT NOCOPY terrtypequal_out_tbl_type,
317 x_terrtypeusgs_out_tbl OUT NOCOPY terrtypeusgs_out_tbl_type,
318 x_typequaltypeusgs_out_tbl OUT NOCOPY typequaltypeusgs_out_tbl_type
319 )
320 AS
321 l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrType';
322 l_api_version_number CONSTANT NUMBER := 1.0;
323 l_return_status VARCHAR2(1);
324 BEGIN
325 --dbms_ourtput.put_line('Update_Terrtype PVT: Entering API');
326
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
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.
345 END IF;
342 IF fnd_api.to_boolean (p_init_msg_list)
343 THEN
344 fnd_msg_pub.initialize;
346
347 -- Debug Message
348 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
349 THEN
350 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
351 fnd_message.set_name ('PROC_NAME', l_api_name);
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');
361 update_terrtype_record (
362 p_api_version_number => p_api_version_number,
363 p_init_msg_list => p_init_msg_list,
364 p_commit => p_commit,
365 p_validation_level => p_validation_level,
366 p_terrtype_rec => p_terrtype_rec,
367 x_return_status => l_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data,
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
379 --
380 --dbms_ourtput.put_line('Update_Terrtype PVT: Before Calling Update_TerrTypeQualType_Usage');
381 update_terrtypequaltype_usage (
382 p_api_version_number => p_api_version_number,
383 p_init_msg_list => p_init_msg_list,
384 p_commit => p_commit,
385 p_validation_level => p_validation_level,
386 p_typequaltypeusgs_tbl => p_typequaltypeusgs_tbl,
387 x_return_status => l_return_status,
388 x_msg_count => x_msg_count,
389 x_msg_data => x_msg_data,
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
399 --
400 --dbms_ourtput.put_line('Update_Terrtype PVT: Before Calling Update_TerrType_Usages');
401 update_terrtype_usages (
402 p_api_version_number => p_api_version_number,
403 p_init_msg_list => p_init_msg_list,
404 p_commit => p_commit,
405 p_validation_level => p_validation_level,
406 p_terrtypeusgs_tbl => p_terrtypeusgs_tbl,
407 x_return_status => l_return_status,
408 x_msg_count => x_msg_count,
409 x_msg_data => x_msg_data,
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
419 --
420 --dbms_ourtput.put_line('Update_Terrtype PVT: Before Calling Update_TerrType_Qualifier');
421 update_terrtype_qualifier (
422 p_api_version_number => p_api_version_number,
423 p_init_msg_list => p_init_msg_list,
424 p_commit => p_commit,
425 p_validation_level => p_validation_level,
426 p_terrtypequal_tbl => p_terrtypequal_tbl,
427 x_return_status => l_return_status,
428 x_msg_count => x_msg_count,
429 x_msg_data => x_msg_data,
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
439 -- Debug Message
440 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
441 THEN
442 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
443 fnd_message.set_name ('PROC_NAME', l_api_name);
444 fnd_msg_pub.add;
445 END IF;
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');
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;
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;
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;
483 p_data => x_msg_data
480 x_return_status := fnd_api.g_ret_sts_unexp_error;
481 fnd_msg_pub.count_and_get (
482 p_count => x_msg_count,
484 );
485 --
486
487 END;
488
489 -- ***************************************************
490 -- start of comments
491 -- ***************************************************
492 -- api name : Delete_TerrType
493 -- type : public.
494 -- function : Delete territory type defnitions
495 -- pre-reqs : Territory qualifiers has to be enabled.
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:
505 -- p_return_status out varchar2(1)
506 -- p_msg_count out number
507 -- p_msg_data out varchar2(2000)
508 --
509 -- requirements :
510 -- business rules :
511
512 -- version : current version 1.0
513 -- initial version: initial version 1.0
514 --
515 -- notes: API for Deleting territory types
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,
525 x_msg_count OUT NOCOPY NUMBER,
526 x_msg_data OUT NOCOPY VARCHAR2
527 )
528 AS
529 l_terrtype_id NUMBER := p_terrtype_id;
530
531 --Declare cursor to get Terr Qualifier
532 CURSOR c_getterrtypequal
533 IS
534 SELECT jtq.terr_type_qual_id
535 FROM jtf_terr_type_qual jtq
536 WHERE jtq.terr_type_id = l_terrtype_id
537 FOR UPDATE;
538
539 --Declare cursor to get qualifiers Type usages
540 CURSOR c_gettypequaltypeusgs
541 IS
542 SELECT jtqu.type_qtype_usg_id
543 FROM jtf_type_qtype_usgs jtqu
544 WHERE jtqu.terr_type_id = l_terrtype_id
545 FOR UPDATE;
546
547 --Declare cursor to get Terr Usages
548 CURSOR c_getterrtypeusgs
549 IS
550 SELECT jtu.terr_type_usg_id
551 FROM jtf_terr_type_usgs jtu
552 WHERE jtu.terr_type_id = l_terrtype_id
553 FOR UPDATE;
554
555 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Territory';
556 l_api_version_number CONSTANT NUMBER := 1.0;
557 l_return_status VARCHAR2(1);
558 l_terr_value_id NUMBER;
559 l_terr_qual_id NUMBER;
560 l_terr_usg_id NUMBER;
561 l_terr_qual_type_usg_id NUMBER;
562 BEGIN
563 --
564 --dbms_ourtput.put_line('Delete_Terrtype PVT: Entering API');
565
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
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)
582 THEN
583 fnd_msg_pub.initialize;
584 END IF;
585
586 -- Debug Message
587 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
588 THEN
589 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
590 fnd_message.set_name ('PROC_NAME', l_api_name);
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 --
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
608 LOOP
609 --dbms_ourtput.put_line('Delete_Terrtype PVT: Before calling Delete_TerrType_Qualifier');
610 delete_terrtype_qualifier (
611 p_api_version_number => p_api_version_number,
612 p_init_msg_list => p_init_msg_list,
613 p_commit => p_commit,
614 p_terrtypequal_id => c.terr_type_qual_id,
615 x_return_status => x_return_status,
616 x_msg_count => x_msg_count,
620 IF x_return_status <> fnd_api.g_ret_sts_success
617 x_msg_data => x_msg_data
618 );
619
621 THEN
622 RAISE fnd_api.g_exc_error;
623 END IF;
624 --
625
626 END LOOP;
627
628 --
629 --dbms_ourtput.put_line('Delete_Terrtype PVT: Before opening C_GetTypeQualTypeUsgs');
630 FOR c IN c_gettypequaltypeusgs
631 LOOP
632 --
633 --dbms_ourtput.put_line('Delete_Terrtype PVT: Before calling Delete_TerrTypeQualType_Usage');
634 delete_terrtypequaltype_usage (
635 p_api_version_number => p_api_version_number,
636 p_init_msg_list => p_init_msg_list,
637 p_commit => p_commit,
638 p_terrtypequaltype_usg_id => c.type_qtype_usg_id,
639 x_return_status => x_return_status,
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 --
649
650 END LOOP;
651
652 --
653 --dbms_ourtput.put_line('Delete_Terrtype PVT: Before opening C_GetTerrTypeUsgs');
654 FOR c IN c_getterrtypeusgs
655 LOOP
656 --
657 --dbms_ourtput.put_line('Delete_Terrtype PVT: Before calling Delete_TerrType_Usages');
658 delete_terrtype_usages (
659 p_api_version_number => p_api_version_number,
660 p_init_msg_list => p_init_msg_list,
661 p_commit => p_commit,
662 p_terrtypeusg_id => c.terr_type_usg_id,
663 x_return_status => x_return_status,
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 --
673
674 END LOOP;
675
676 --dbms_ourtput.put_line('Delete_Terrtype PVT: Before calling Delete_TerrType_Record');
677 delete_terrtype_record (
678 p_api_version_number => p_api_version_number,
679 p_init_msg_list => p_init_msg_list,
680 p_commit => p_commit,
681 p_terrtype_id => l_terrtype_id,
682 x_return_status => x_return_status,
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
692 -- Debug Message
693 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
694 THEN
695 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
696 fnd_message.set_name ('PROC_NAME', l_api_name);
697 fnd_msg_pub.add;
698 END IF;
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');
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,
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,
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 );
737 END delete_terrtype;
738
739 -- ***************************************************
740 -- start of comments
741 -- ***************************************************
742 -- api name : Deactivate_TerrType
743 -- type : public.
744 -- function : Deactivate territory type defnitions
745 -- pre-reqs : Territory qualifiers has to be enabled.
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:
755 -- p_return_status out varchar2(1)
759 -- requirements :
756 -- p_msg_count out number
757 -- p_msg_data out varchar2(2000)
758 --
760 -- business rules :
761
762 -- version : current version 1.0
763 -- initial version: initial version 1.0
764 --
765 -- notes: API for Deleting territory types
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,
775 x_msg_count OUT NOCOPY NUMBER,
776 x_msg_data OUT NOCOPY VARCHAR2
777 )
778 AS
779 CURSOR c_getterrtype (l_terrtype_id NUMBER)
780 IS
781 SELECT ROWID,
782 terr_type_id,
783 last_updated_by,
784 last_update_date,
785 created_by,
786 creation_date,
787 last_update_login,
788 application_short_name,
789 name,
790 enabled_flag,
791 description,
792 start_date_active,
793 end_date_active,
794 org_id,
795 attribute_category,
796 attribute1,
797 attribute2,
798 attribute3,
799 attribute4,
800 attribute5,
801 attribute6,
802 attribute7,
803 attribute8,
804 attribute9,
805 attribute10,
806 attribute11,
807 attribute12,
808 attribute13,
809 attribute14,
810 attribute15
811 FROM jtf_terr_types
812 WHERE terr_type_id = l_terrtype_id
813 FOR UPDATE NOWAIT;
814
815 --Local variable declaration
816 l_api_name CONSTANT VARCHAR2(30) := 'Deactivate_TerrType';
817 l_api_version_number CONSTANT NUMBER := 1.0;
818 l_rowid VARCHAR2(50);
819 l_return_status VARCHAR2(1);
820 l_ref_terrtype_rec terrtype_rec_type;
821 BEGIN
822 --dbms_ourtput.put_line('De-Activate_Terrtype PVT: Entering API');
823
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
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)
840 THEN
841 fnd_msg_pub.initialize;
842 END IF;
843
844 -- Debug Message
845 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
846 THEN
847 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
848 fnd_message.set_name ('PROC_NAME', l_api_name);
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
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');
863 FETCH c_getterrtype INTO l_rowid,
864 l_ref_terrtype_rec.terr_type_id,
865 l_ref_terrtype_rec.last_updated_by,
866 l_ref_terrtype_rec.last_update_date,
867 l_ref_terrtype_rec.created_by,
868 l_ref_terrtype_rec.creation_date,
869 l_ref_terrtype_rec.last_update_login,
870 l_ref_terrtype_rec.application_short_name,
871 l_ref_terrtype_rec.name,
872 l_ref_terrtype_rec.enabled_flag,
873 l_ref_terrtype_rec.description,
874 l_ref_terrtype_rec.start_date_active,
875 l_ref_terrtype_rec.end_date_active,
876 l_ref_terrtype_rec.org_id,
877 l_ref_terrtype_rec.attribute_category,
878 l_ref_terrtype_rec.attribute1,
879 l_ref_terrtype_rec.attribute2,
880 l_ref_terrtype_rec.attribute3,
881 l_ref_terrtype_rec.attribute4,
885 l_ref_terrtype_rec.attribute8,
882 l_ref_terrtype_rec.attribute5,
883 l_ref_terrtype_rec.attribute6,
884 l_ref_terrtype_rec.attribute7,
886 l_ref_terrtype_rec.attribute9,
887 l_ref_terrtype_rec.attribute10,
888 l_ref_terrtype_rec.attribute11,
889 l_ref_terrtype_rec.attribute12,
890 l_ref_terrtype_rec.attribute13,
891 l_ref_terrtype_rec.attribute14,
892 l_ref_terrtype_rec.attribute15;
893
894 --
895 IF (c_getterrtype%NOTFOUND)
896 THEN
897 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
898 THEN
899 --dbms_ourtput.put_line('De-Activate Terrtype PVT: C_GetTerrType%NOTFOUND');
900 fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
901 fnd_message.set_token ('TABLE_NAME', 'JTF_TERR_TYPES');
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');
911 jtf_terr_types_pkg.update_row (
912 x_rowid => l_rowid,
913 x_terr_type_id => l_ref_terrtype_rec.terr_type_id,
914 x_last_updated_by => l_ref_terrtype_rec.last_updated_by,
915 x_last_update_date => l_ref_terrtype_rec.last_update_date,
916 x_created_by => l_ref_terrtype_rec.created_by,
917 x_creation_date => l_ref_terrtype_rec.creation_date,
918 x_last_update_login => l_ref_terrtype_rec.last_update_login,
919 x_application_short_name => g_app_short_name,
920 x_name => l_ref_terrtype_rec.name,
921 x_enabled_flag => 'N',
922 x_description => l_ref_terrtype_rec.description,
923 x_start_date_active => (SYSDATE - 1),
924 x_end_date_active => (SYSDATE - 1),
925 x_attribute_category => l_ref_terrtype_rec.attribute_category,
926 x_attribute1 => l_ref_terrtype_rec.attribute1,
927 x_attribute2 => l_ref_terrtype_rec.attribute2,
928 x_attribute3 => l_ref_terrtype_rec.attribute3,
929 x_attribute4 => l_ref_terrtype_rec.attribute4,
930 x_attribute5 => l_ref_terrtype_rec.attribute5,
931 x_attribute6 => l_ref_terrtype_rec.attribute6,
932 x_attribute7 => l_ref_terrtype_rec.attribute7,
933 x_attribute8 => l_ref_terrtype_rec.attribute8,
934 x_attribute9 => l_ref_terrtype_rec.attribute9,
935 x_attribute10 => l_ref_terrtype_rec.attribute10,
936 x_attribute11 => l_ref_terrtype_rec.attribute11,
937 x_attribute12 => l_ref_terrtype_rec.attribute12,
938 x_attribute13 => l_ref_terrtype_rec.attribute13,
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
948 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
949 fnd_message.set_name ('PROC_NAME', l_api_name);
950 fnd_msg_pub.add;
951 END IF;
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');
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,
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,
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 (
989 g_pkg_name,
990 'Error inside Deactivate_TerrType' || SQLERRM
991 );
992 END IF;
993 END deactivate_terrtype;
994
995 -- ***************************************************
996 -- start of comments
1000 -- function : Copy_territory type defnitions
997 -- ***************************************************
998 -- api name : Copy_TerrType
999 -- type : public.
1001 -- pre-reqs : Territory qualifiers has to be enabled.
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
1011 -- p_End_Date in date
1012 --
1013 -- out:
1014 -- p_return_status out varchar2(1)
1015 -- p_msg_count out number
1016 -- p_msg_data out varchar2(2000)
1017 --
1018 -- requirements :
1019 -- business rules :
1020
1021 -- version : current version 1.0
1022 -- initial version: initial version 1.0
1023 --
1024 -- notes: API for Copying territory types
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,
1034 p_terrtype_description IN VARCHAR2,
1035 p_enabled_flag IN VARCHAR2,
1036 p_start_date IN DATE,
1037 p_end_date IN DATE,
1038 x_return_status OUT NOCOPY VARCHAR2,
1039 x_msg_count OUT NOCOPY NUMBER,
1040 x_msg_data OUT NOCOPY VARCHAR2,
1041 x_terrtype_id OUT NOCOPY NUMBER
1042 )
1043 AS
1044 --Declare cursor to get Terr Qualifier
1045 CURSOR c_getterrtypequal
1046 IS
1047 SELECT jtq.terr_type_qual_id,
1048 jtq.last_update_date,
1049 jtq.last_updated_by,
1050 jtq.creation_date,
1051 jtq.created_by,
1052 jtq.last_update_login,
1053 jtq.qual_usg_id,
1054 jtq.terr_type_id,
1055 jtq.exclusive_use_flag,
1056 jtq.overlap_allowed_flag,
1057 jtq.in_use_flag,
1058 jtq.qualifier_mode,
1059 jtq.org_id
1060 FROM jtf_terr_type_qual jtq
1061 WHERE jtq.terr_type_id = p_terrtype_id
1062 FOR UPDATE;
1063
1064 --Declare cursor to get qualifiers Type usages
1065 CURSOR c_gettypequaltypeusgs
1066 IS
1067 SELECT jtqu.type_qtype_usg_id,
1068 jtqu.terr_type_id,
1069 jtqu.qual_type_usg_id,
1070 jtqu.last_update_date,
1071 jtqu.last_updated_by,
1072 jtqu.creation_date,
1073 jtqu.created_by,
1074 jtqu.last_update_login,
1075 jtqu.org_id
1076 FROM jtf_type_qtype_usgs jtqu
1077 WHERE jtqu.terr_type_id = p_terrtype_id
1078 FOR UPDATE;
1079
1080 --Declare cursor to get Terr Usages
1081 CURSOR c_getterrtypeusgs
1082 IS
1083 SELECT jtu.terr_type_usg_id,
1084 jtu.source_id,
1085 jtu.terr_type_id,
1086 jtu.last_update_date,
1087 jtu.last_updated_by,
1088 jtu.creation_date,
1089 jtu.created_by,
1090 jtu.last_update_login,
1091 jtu.org_id
1092 FROM jtf_terr_type_usgs jtu
1093 WHERE jtu.terr_type_id = p_terrtype_id
1094 FOR UPDATE;
1095
1096 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Territory_Type';
1097 l_api_version_number CONSTANT NUMBER := 1.0;
1098 l_return_status VARCHAR2(1);
1099 l_terrtypeusg_id NUMBER;
1100 l_terrtype_id NUMBER;
1101 l_terrtype_rec terrtype_rec_type;
1102 l_terrtypeusgs_rec terrtypeusgs_rec_type;
1103 l_terrtypeusgs_tbl terrtypeusgs_tbl_type;
1104 l_typequaltypeusgs_tbl typequaltypeusgs_tbl_type;
1105 l_typequaltypeusgs_rec typequaltypeusgs_rec_type;
1106 l_terrtypequal_rec terrtypequal_rec_type;
1107 l_terrtypequal_tbl terrtypequal_tbl_type;
1108 l_terrtype_out_rec terrtype_out_rec_type;
1109 l_terrtypeusgs_out_tbl terrtypeusgs_out_tbl_type;
1110 l_typequaltypeusgs_out_tbl typequaltypeusgs_out_tbl_type;
1111 l_terrtypequal_out_tbl terrtypequal_out_tbl_type;
1112 l_counter NUMBER := 0;
1113 BEGIN
1114 --
1115 --dbms_output.put_line('Copy_Terrtype PVT: Entering API - ' || p_enabled_flag);
1116
1117 -- Standard Start of API savepoint
1118 SAVEPOINT copy_terrtype_pvt;
1119
1120 -- Standard call to check for call compatibility.
1124 l_api_name,
1121 IF NOT fnd_api.compatible_api_call (
1122 l_api_version_number,
1123 p_api_version_number,
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)
1133 THEN
1134 fnd_msg_pub.initialize;
1135 END IF;
1136
1137 -- Debug Message
1138 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1139 THEN
1140 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1141 fnd_message.set_name ('PROC_NAME', l_api_name);
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,
1151 creation_date,
1152 created_by,
1153 last_update_login,
1154 application_short_name,
1155 name,
1156 enabled_flag,
1157 start_date_active,
1158 end_date_active,
1159 description,
1160 org_id,
1161 attribute_category,
1162 attribute1,
1163 attribute2,
1164 attribute3,
1165 attribute4,
1166 attribute5,
1167 attribute6,
1168 attribute7,
1169 attribute8,
1170 attribute9,
1171 attribute10,
1172 attribute11,
1173 attribute12,
1174 attribute13,
1175 attribute14,
1176 attribute15
1177 INTO l_terrtype_rec.terr_type_id,
1178 l_terrtype_rec.last_update_date,
1179 l_terrtype_rec.last_updated_by,
1180 l_terrtype_rec.creation_date,
1181 l_terrtype_rec.created_by,
1182 l_terrtype_rec.last_update_login,
1183 l_terrtype_rec.application_short_name,
1184 l_terrtype_rec.name,
1185 l_terrtype_rec.enabled_flag,
1186 l_terrtype_rec.start_date_active,
1187 l_terrtype_rec.end_date_active,
1188 l_terrtype_rec.description,
1189 l_terrtype_rec.org_id,
1190 l_terrtype_rec.attribute_category,
1191 l_terrtype_rec.attribute1,
1192 l_terrtype_rec.attribute2,
1193 l_terrtype_rec.attribute3,
1194 l_terrtype_rec.attribute4,
1195 l_terrtype_rec.attribute5,
1196 l_terrtype_rec.attribute6,
1197 l_terrtype_rec.attribute7,
1198 l_terrtype_rec.attribute8,
1199 l_terrtype_rec.attribute9,
1200 l_terrtype_rec.attribute10,
1201 l_terrtype_rec.attribute11,
1202 l_terrtype_rec.attribute12,
1203 l_terrtype_rec.attribute13,
1204 l_terrtype_rec.attribute14,
1205 l_terrtype_rec.attribute15
1206 FROM jtf_terr_types
1207 WHERE terr_type_id = p_terrtype_id;
1208 l_terrtype_rec.terr_type_id := NULL;
1209 l_terrtype_rec.name := p_terrtype_name;
1210 l_terrtype_rec.description := p_terrtype_description;
1211 l_terrtype_rec.enabled_flag := p_enabled_flag;
1212 l_terrtype_rec.start_date_active := p_start_date;
1213 l_terrtype_rec.end_date_active := p_end_date;
1214 -- Create Territory Record
1215 --dbms_output.put_line('Copy_Terrtype PVT: Before calling Copy_TerrType_Record');
1216 create_terrtype_record (
1217 p_api_version_number => p_api_version_number,
1218 p_init_msg_list => p_init_msg_list,
1219 p_commit => p_commit,
1220 p_validation_level => p_validation_level,
1221 p_terrtype_rec => l_terrtype_rec,
1222 x_return_status => l_return_status,
1223 x_msg_count => x_msg_count,
1224 x_msg_data => x_msg_data,
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
1234 --dbms_output.put_line('Before opening C_GetTerrTypeUsgs l_TerrType_Id - ' || l_TerrType_Id);
1235
1236 -- Create Territory Type Usage
1237 OPEN c_getterrtypeusgs;
1238 FETCH c_getterrtypeusgs INTO l_terrtypeusgs_rec;
1239
1240 --dbms_output.put_line('TERR_TYPE_USG_ID - ' || to_char(l_TerrTypeUsgs_Rec.TERR_TYPE_USG_ID) );
1241 WHILE c_getterrtypeusgs%FOUND
1242 LOOP
1243 --
1244 --dbms_output.put_line('Inside while LOOP Create_terrtype PVT: Create_TerrType_Usages');
1245 l_terrtypeusgs_rec.terr_type_usg_id := NULL;
1246 l_counter := l_counter + 1;
1247 l_terrtypeusgs_tbl (l_counter) := l_terrtypeusgs_rec;
1248 --fetche the next record
1249 FETCH c_getterrtypeusgs INTO l_terrtypeusgs_rec;
1250 --
1251
1252 END LOOP;
1253
1254 CLOSE c_getterrtypeusgs;
1258 p_init_msg_list => p_init_msg_list,
1255 --dbms_output.put_line('Create_terrtype PVT: Before Calling Create_TerrType_Usages PVT');
1256 create_terrtype_usages (
1257 p_api_version_number => p_api_version_number,
1259 p_commit => p_commit,
1260 p_validation_level => p_validation_level,
1261 p_terrtype_id => l_terrtype_id,
1262 p_terrtypeusgs_tbl => l_terrtypeusgs_tbl,
1263 x_return_status => l_return_status,
1264 x_msg_count => x_msg_count,
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
1274 --
1275 l_terrtypeusg_id := l_terrtypeusgs_out_tbl (1).terr_type_usg_id;
1276 --
1277 --dbms_output.put_line('Before opening C_GetTypeQualTypeUsgs');
1278 l_counter := 0;
1279 OPEN c_gettypequaltypeusgs;
1280 FETCH c_gettypequaltypeusgs INTO l_typequaltypeusgs_rec;
1281
1282 WHILE c_gettypequaltypeusgs%FOUND
1283 LOOP
1284 --
1285 --dbms_output.put_line('Inside while LOOP Create_terrtype PVT: C_GetTypeQualTypeUsgs');
1286 l_counter := l_counter + 1;
1287 l_typequaltypeusgs_rec.type_qual_type_usg_id := NULL;
1288 l_typequaltypeusgs_tbl (l_counter) := l_typequaltypeusgs_rec;
1289 --fetch the next record
1290 FETCH c_gettypequaltypeusgs INTO l_typequaltypeusgs_rec;
1291 --
1292
1293 END LOOP;
1294
1295 CLOSE c_gettypequaltypeusgs;
1296 --
1297 -- Call api to insert records into jtf_terr_qualtype_usgs
1298 --
1299 --dbms_output.put_line('Create_terrtype PVT: Before Calling Create_TerrTypeQualType_Usage PVT');
1300 create_terrtypequaltype_usage (
1301 p_api_version_number => p_api_version_number,
1302 p_init_msg_list => p_init_msg_list,
1303 p_commit => p_commit,
1304 p_validation_level => p_validation_level,
1305 p_terrtype_id => l_terrtype_id,
1306 p_terrtypeusg_id => l_terrtypeusg_id,
1307 p_typequaltypeusgs_tbl => l_typequaltypeusgs_tbl,
1308 x_return_status => l_return_status,
1309 x_msg_count => x_msg_count,
1310 x_msg_data => x_msg_data,
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
1320 --
1321 --dbms_output.put_line('Before opening C_GetTerrTypeQual');
1322 l_counter := 0;
1323 OPEN c_getterrtypequal;
1324 FETCH c_getterrtypequal INTO l_terrtypequal_rec;
1325
1326 WHILE c_getterrtypequal%FOUND
1327 LOOP
1328 --
1329 --dbms_output.put_line('Inside while LOOP Create_terrtype PVT: C_GetTerrTypeQual');
1330 l_counter := l_counter + 1;
1331 l_terrtypequal_rec.terr_type_qual_id := NULL;
1332 l_terrtypequal_tbl (l_counter) := l_terrtypequal_rec;
1333 FETCH c_getterrtypequal INTO l_terrtypequal_rec;
1334 --
1335
1336 END LOOP;
1337
1338 CLOSE c_getterrtypequal;
1339 --dbms_output.put_line('create_terrtype PVT: Before Calling Create_TerrType_Qualifier PVT');
1340 create_terrtype_qualifier (
1341 p_api_version_number => p_api_version_number,
1342 p_init_msg_list => p_init_msg_list,
1343 p_commit => p_commit,
1344 p_validation_level => p_validation_level,
1345 p_terrtype_id => l_terrtype_id,
1346 p_terrtypequal_tbl => l_terrtypequal_tbl,
1347 x_return_status => l_return_status,
1348 x_msg_count => x_msg_count,
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
1358 -- Debug Message
1359 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1360 THEN
1361 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1362 fnd_message.set_name ('PROC_NAME', l_api_name);
1363 fnd_msg_pub.add;
1364 END IF;
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
1374 -- If Copy was successfull move the new territory type id to o/p parameter
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;
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
1393 fnd_msg_pub.count_and_get (
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;
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 );
1406 END copy_terrtype;
1407
1408 --
1409 -- ***************************************************
1410 -- start of comments
1411 -- ***************************************************
1412 -- API name : Create_TerrType_Header
1413 -- Type : PUBLIC
1414 -- Function : To create Territories Types - which inludes the creation of following
1415 -- Territory Type Header, Territory Type Usages, Territory Type qualifier
1416 -- type usages table.
1417 -- Pre-reqs :
1418 -- Parameters:
1419 -- IN :
1420 -- Required
1421 -- Parameter Name Data Type Default
1422 -- P_Api_Version_Number NUMBER
1423 -- P_TerrType_Rec TerrType_Rec_Type := G_Miss_TerrType_Rec
1424 -- P_TerrTypeUsgs_Tbl TerrTypeusgs_Tbl_Type := G_MISS_TerrTypeusgs_Tbl
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 :
1434 -- Parameter Name Data Type
1435 -- X_Return_Status VARCHAR2(1)
1436 -- X_Msg_Count NUMBER
1437 -- X_Msg_Data VARCHAR2(2000)
1438 -- X_TerrType_Out_Rec TerrType_Out_Rec_Type
1439 -- X_TerrTypeusgs_Out_Tbl TerrTypeusgs_Out_Tbl_Type
1440 -- X_TypeQualTypeUsgs_Out_Tbl TypeQualTypeUsgs_Out_Tbl_Type
1441 --
1442 --
1443 -- Notes:
1444 --
1445 --
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,
1455 p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
1456 x_return_status OUT NOCOPY VARCHAR2,
1457 x_msg_count OUT NOCOPY NUMBER,
1458 x_msg_data OUT NOCOPY VARCHAR2,
1459 x_terrtype_out_rec OUT NOCOPY terrtype_out_rec_type,
1460 x_terrtypeusgs_out_tbl OUT NOCOPY terrtypeusgs_out_tbl_type,
1461 x_typequaltypeusgs_out_tbl OUT NOCOPY typequaltypeusgs_out_tbl_type
1462 )
1463 AS
1464 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrType_Header';
1465 l_rowid ROWID;
1466 l_return_status VARCHAR2(1);
1467 l_terrtype_id NUMBER;
1468 l_terrtypeusg_id NUMBER;
1469 l_typequaltypeusg_id NUMBER;
1470 l_terrtypeusgs_tbl_count NUMBER := p_terrtypeusgs_tbl.COUNT;
1471 l_typequaltypeusgs_tbl_count NUMBER := p_typequaltypeusgs_tbl.COUNT;
1472 l_counter NUMBER;
1473 l_terrtype_out_rec terrtype_out_rec_type;
1474 l_terrtypeusgs_out_tbl terrtypeusgs_out_tbl_type;
1475 l_typequaltypeusgs_out_tbl typequaltypeusgs_out_tbl_type;
1476 BEGIN
1477 --dbms_ourtput.put_line('Create_TerrType_Header PVT: Entering API');
1478
1479 -- Debug Message
1480 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1481 THEN
1482 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1483 fnd_message.set_name ('PROC_NAME', l_api_name);
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,
1493 p_init_msg_list => p_init_msg_list,
1494 p_commit => p_commit,
1495 p_validation_level => p_validation_level,
1496 p_terrtype_rec => p_terrtype_rec,
1497 x_return_status => l_return_status,
1498 x_msg_count => x_msg_count,
1499 x_msg_data => x_msg_data,
1500 x_terrtype_id => l_terrtype_id,
1504 -- Save the statuses
1501 x_terrtype_out_rec => l_terrtype_out_rec
1502 );
1503 --
1505 x_return_status := l_return_status;
1506 --
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
1516 --
1517 --
1518 --dbms_ourtput.put_line('Create_terrtype PVT: Before Calling Create_TerrType_Usages PVT');
1519 create_terrtype_usages (
1520 p_api_version_number => p_api_version_number,
1521 p_init_msg_list => p_init_msg_list,
1522 p_commit => p_commit,
1523 p_validation_level => p_validation_level,
1524 p_terrtype_id => l_terrtype_id,
1525 p_terrtypeusgs_tbl => p_terrtypeusgs_tbl,
1526 x_return_status => l_return_status,
1527 x_msg_count => x_msg_count,
1528 x_msg_data => x_msg_data,
1529 x_terrtypeusgs_out_tbl => l_terrtypeusgs_out_tbl
1530 );
1531 --
1532 -- Save the statuses
1533 x_return_status := l_return_status;
1534 --
1535 --Save the out status record
1536 x_terrtypeusgs_out_tbl := l_terrtypeusgs_out_tbl;
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
1546 --
1547 -- Call api to insert records into jtf_terr_qualtype_usgs
1548 --
1549 --dbms_ourtput.put_line('Create_terrtype PVT: Before Calling Create_TerrTypeQualType_Usage PVT');
1550 create_terrtypequaltype_usage (
1551 p_api_version_number => p_api_version_number,
1552 p_init_msg_list => p_init_msg_list,
1553 p_commit => p_commit,
1554 p_validation_level => p_validation_level,
1555 p_terrtype_id => l_terrtype_id,
1556 p_terrtypeusg_id => l_terrtypeusg_id,
1557 p_typequaltypeusgs_tbl => p_typequaltypeusgs_tbl,
1558 x_return_status => l_return_status,
1559 x_msg_count => x_msg_count,
1560 x_msg_data => x_msg_data,
1561 x_typequaltypeusgs_out_tbl => l_typequaltypeusgs_out_tbl
1562 );
1563 --
1564 -- Save the statuses
1565 x_return_status := l_return_status;
1566 --
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
1576 --
1577 -- Debug Message
1578 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1579 THEN
1580 fnd_message.set_name ('JTF', 'Create TerrType Header: End');
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 (
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 (
1598 p_count => x_msg_count,
1599 p_data => x_msg_data
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,
1609 'Error inside create_terrtype_header ' || sqlerrm);
1610 END IF;
1611 END create_terrtype_header;
1612
1613 --
1614 --
1615 -- ***************************************************
1616 -- start of comments
1617 -- ***************************************************
1618 -- API name : Create_TerrType_record
1619 -- Type : PUBLIC
1620 -- Function : To create a records in jtf_Terr_Type_all table
1621 --
1622 -- Pre-reqs :
1623 -- Parameters:
1624 -- IN :
1625 -- Required
1626 -- Parameter Name Data Type Default
1627 -- X_TerrType_Rec TerrType_Rec_Type := G_Miss_TerrType_Rec,
1628 --
1629 -- OUT :
1630 -- Parameter Name Data Type
1631 -- X_terr_id NUMBER;
1632 -- X_Return_Status VARCHAR2(1)
1633 -- X_TerrType_Out_Rec TerrType_Out_Rec_Type
1634 --
1635 --
1636 -- Notes:
1637 --
1638 --
1639 -- End of Comments
1640 --
1641 PROCEDURE create_terrtype_record (
1645 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
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,
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,
1650 x_terrtype_id OUT NOCOPY NUMBER,
1651 x_terrtype_out_rec OUT NOCOPY terrtype_out_rec_type
1652 )
1653 AS
1654 l_rowid ROWID;
1655 l_terrtype_id NUMBER := P_TERRTYPE_REC.TERR_TYPE_ID;
1656 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrType_Record';
1657 l_api_version_number CONSTANT NUMBER := 1.0;
1658 BEGIN
1659 --dbms_ourtput.put_line('Create_TerrType_Record PVT: Entering API');
1660
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
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)
1677 THEN
1678 fnd_msg_pub.initialize;
1679 END IF;
1680
1681 -- Debug Message
1682 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1683 THEN
1684 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1685 fnd_message.set_name ('PROC_NAME', l_api_name);
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
1695 -- Debug message
1696 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1697 THEN
1698 fnd_message.set_name ('JTF', 'JTF_TERR_VALIDATE_MSG');
1699 fnd_message.set_token ('PROC_NAME', 'Validate_TerrType_Record');
1700 fnd_msg_pub.add;
1701 END IF;
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
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
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;
1727 END IF;
1728
1729 --dbms_output.put_line('Value of l_terrtype_id='||TO_CHAR(l_terrtype_id));
1730
1731 -- Call insert terr_all table handler
1732 --dbms_ourtput.put_line('Create_TerrType_Record PVT: Before calling JTF_TERR_TYPES_PKG.Insert_Row');
1733 jtf_terr_types_pkg.insert_row (
1734 x_rowid => l_rowid,
1735 x_terr_type_id => l_terrtype_id,
1736 x_last_updated_by => p_terrtype_rec.last_updated_by,
1737 x_last_update_date => p_terrtype_rec.last_update_date,
1738 x_created_by => p_terrtype_rec.created_by,
1739 x_creation_date => p_terrtype_rec.creation_date,
1740 x_last_update_login => p_terrtype_rec.last_update_login,
1741 x_application_short_name => p_terrtype_rec.application_short_name,
1742 x_name => p_terrtype_rec.name,
1743 x_enabled_flag => p_terrtype_rec.enabled_flag,
1744 x_description => p_terrtype_rec.description,
1745 x_start_date_active => p_terrtype_rec.start_date_active,
1746 x_end_date_active => p_terrtype_rec.end_date_active,
1747 x_attribute_category => p_terrtype_rec.attribute_category,
1748 x_attribute1 => p_terrtype_rec.attribute1,
1749 x_attribute2 => p_terrtype_rec.attribute2,
1750 x_attribute3 => p_terrtype_rec.attribute3,
1751 x_attribute4 => p_terrtype_rec.attribute4,
1752 x_attribute5 => p_terrtype_rec.attribute5,
1753 x_attribute6 => p_terrtype_rec.attribute6,
1754 x_attribute7 => p_terrtype_rec.attribute7,
1755 x_attribute8 => p_terrtype_rec.attribute8,
1756 x_attribute9 => p_terrtype_rec.attribute9,
1757 x_attribute10 => p_terrtype_rec.attribute10,
1758 x_attribute11 => p_terrtype_rec.attribute11,
1759 x_attribute12 => p_terrtype_rec.attribute12,
1760 x_attribute13 => p_terrtype_rec.attribute13,
1761 x_attribute14 => p_terrtype_rec.attribute14,
1762 x_attribute15 => p_terrtype_rec.attribute15,
1763 x_org_id => p_terrtype_rec.org_id
1767 x_terrtype_out_rec.return_status := fnd_api.g_ret_sts_success;
1764 );
1765 x_terrtype_out_rec.terr_type_id := l_terrtype_id;
1766 x_terrtype_id := l_terrtype_id;
1768
1769 -- Debug Message
1770 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1771 THEN
1772 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1773 fnd_message.set_name ('PROC_NAME', l_api_name);
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;
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;
1801 fnd_msg_pub.count_and_get (
1802 p_count => x_msg_count,
1803 p_data => x_msg_data
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)
1813 THEN
1814 fnd_msg_pub.add_exc_msg (
1815 g_pkg_name,
1816 'Insert_Territory_Type_Record'
1817 );
1818 END IF;
1819 END create_terrtype_record;
1820
1821 --
1822 --
1823 -- ***************************************************
1824 -- start of comments
1825 -- ***************************************************
1826 -- API name : Create_TerrType_Usages
1827 -- Type : PUBLIC
1828 -- Function : To create Territories Type usages
1829 --
1830 -- Pre-reqs :
1831 -- Parameters:
1832 -- IN :
1833 -- Required
1834 -- Parameter Name Data Type Default
1835 -- p_terr_id NUMBER;
1836 -- P_TerrTypeUsgs_Tbl TerrTypeUsgs_Tbl_Type := G_MISS_TerrTypeUsgs_Tbl
1837 --
1838 -- OUT :
1839 -- Parameter Name Data Type
1840 -- X_Return_Status VARCHAR2(1)
1841 -- X_TerrTypeUsgs_Out_Tbl TerrTypeUsgs_Out_Tbl,
1842 --
1843 --
1844 -- Notes:
1845 --
1846 --
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
1856 := g_miss_terrtypeusgs_tbl,
1857 x_return_status OUT NOCOPY VARCHAR2,
1858 x_msg_count OUT NOCOPY NUMBER,
1859 x_msg_data OUT NOCOPY VARCHAR2,
1860 x_terrtypeusgs_out_tbl OUT NOCOPY terrtypeusgs_out_tbl_type
1861 )
1862 AS
1863 l_rowid ROWID;
1864 l_terrtypeusg_id NUMBER;
1865 l_terrtypeusgs_tbl_count NUMBER := p_terrtypeusgs_tbl.COUNT;
1866 l_terrtypeusgs_out_tbl_count NUMBER;
1867 l_terrtypeusgs_out_tbl terrtypeusgs_out_tbl_type;
1868 l_counter NUMBER;
1869 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrType_Usages';
1870 l_api_version_number CONSTANT NUMBER := 1.0;
1871 BEGIN
1872 --dbms_ourtput.put_line('Create_TerrType_Usages PVT: Entering API');
1873
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
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)
1890 THEN
1891 fnd_msg_pub.initialize;
1892 END IF;
1893
1894 -- Debug Message
1895 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1896 THEN
1900 END IF;
1897 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1898 fnd_message.set_name ('PROC_NAME', l_api_name);
1899 fnd_msg_pub.add;
1901
1902 FOR l_counter IN 1 .. l_terrtypeusgs_tbl_count
1903 LOOP
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
1913 IF fnd_msg_pub.check_msg_level (
1914 fnd_msg_pub.g_msg_lvl_debug_low
1915 )
1916 THEN
1917 fnd_message.set_name ('JTF', 'JTF_TERR_VALIDATE_MSG');
1918 fnd_message.set_token ('PROC_NAME', 'Validate_TerrType_Usage');
1919 fnd_msg_pub.add;
1920 END IF;
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 --
1939
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;
1949 END IF;
1950
1951 --
1952 jtf_terr_type_usgs_pkg.insert_row (
1953 x_rowid => l_rowid,
1954 x_terr_type_usg_id => l_terrtypeusg_id,
1955 x_last_update_date => p_terrtypeusgs_tbl (l_counter).last_update_date,
1956 x_last_updated_by => p_terrtypeusgs_tbl (l_counter).last_updated_by,
1957 x_creation_date => p_terrtypeusgs_tbl (l_counter).creation_date,
1958 x_created_by => p_terrtypeusgs_tbl (l_counter).created_by,
1959 x_last_update_login => p_terrtypeusgs_tbl (l_counter).last_update_login,
1960 x_terr_type_id => p_terrtype_id,
1961 x_source_id => p_terrtypeusgs_tbl (l_counter).source_id,
1962 x_org_id => p_terrtypeusgs_tbl (l_counter).org_id
1963 );
1964 -- Save the terr_usg_id and
1965 x_terrtypeusgs_out_tbl (l_counter).terr_type_usg_id :=
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);
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
1980 IF fnd_msg_pub.check_msg_level (
1981 fnd_msg_pub.g_msg_lvl_unexp_error
1982 )
1983 THEN
1984 fnd_msg_pub.add_exc_msg (
1985 g_pkg_name,
1986 'Others exception in Insert_Territory_Type_Usages'
1987 );
1988 END IF;
1989 END;
1990 --
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
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;
2008 END IF;
2009 END LOOP;
2010
2011 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2012 THEN
2013 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2014 fnd_message.set_name ('PROC_NAME', l_api_name);
2015 fnd_msg_pub.add;
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
2028 --dbms_ourtput.put_line('Create_TerrType_Usages PVT: Exiting API');
2025 -- Standard call to get message count and if count is 1, get message info.
2026 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2027 --
2029 --
2030
2031 END create_terrtype_usages;
2032
2033 --
2034 --
2035 -- ***************************************************
2036 -- start of comments
2037 -- ***************************************************
2038 -- API name : Create_TerrTypeQualType_Usage
2039 -- Type : PUBLIC
2040 -- Function : To create Territory type qualifier type
2041 -- usages
2042 --
2043 -- Pre-reqs :
2044 -- Parameters:
2045 -- IN :
2046 -- Required
2047 -- Parameter Name Data Type Default
2048 -- p_terr_id NUMBER
2049 -- p_terr_usg_id NUMBER;
2050 -- P_Terr_QualTypeUsgs_Rec Terr_QualTypeUsgs_Rec_Type := G_Miss_Terr_QualTypeUsgs_Rec
2051 --
2052 -- OUT :
2053 -- Parameter Name Data Type
2054 -- X_Return_Status VARCHAR2(1)
2055 -- X_Terr_QualTypeUsgs_Out_Tbl Terr_QualTypeUsgs_Out_Tbl,
2056 --
2057 -- Notes:
2058 --
2059 --
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,
2069 p_typequaltypeusgs_rec IN typequaltypeusgs_rec_type := g_miss_typequaltypeusgs_rec,
2070 x_return_status OUT NOCOPY VARCHAR2,
2071 x_msg_count OUT NOCOPY NUMBER,
2072 x_msg_data OUT NOCOPY VARCHAR2,
2073 x_typequaltypeusgs_id OUT NOCOPY NUMBER,
2074 x_typequaltypeusgs_out_rec OUT NOCOPY typequaltypeusgs_out_rec_type
2075 )
2076 AS
2077 l_rowid ROWID;
2078 l_typequaltype_usg_id NUMBER := p_typequaltypeusgs_rec.type_qual_type_usg_id;
2079 l_api_name CONSTANT VARCHAR2(30)
2080 := 'Create_TerrTypeQualType_Usage';
2081 l_api_version_number CONSTANT NUMBER := 1.0;
2082 BEGIN
2083 --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage REC: Entering API');
2084
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
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)
2101 THEN
2102 fnd_msg_pub.initialize;
2103 END IF;
2104
2105 -- Debug Message
2106 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2107 THEN
2108 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2109 fnd_message.set_name ('PROC_NAME', l_api_name);
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
2119 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2120 THEN
2121 fnd_message.set_name ('JTF', 'JTF_TERR_VALIDATE_MSG');
2122 fnd_message.set_token ('PROC_NAME', 'Validate_Territory_Usage');
2123 fnd_msg_pub.add;
2124 END IF;
2125
2126 -- Invoke validation procedures
2127 validate_type_qtype_usage (
2128 p_init_msg_list => p_init_msg_list,
2129 x_return_status => x_return_status,
2130 x_msg_count => x_msg_count,
2131 x_msg_data => x_msg_data,
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;
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;
2150 END IF;
2151
2152 -- Call insert terr_Qual_Type_Usgs table handler
2153 jtf_type_qtype_usgs_pkg.insert_row (
2154 x_rowid => l_rowid,
2155 x_type_qtype_usg_id => l_typequaltype_usg_id,
2159 x_creation_date => p_typequaltypeusgs_rec.creation_date,
2156 x_last_updated_by => p_typequaltypeusgs_rec.last_updated_by,
2157 x_last_update_date => p_typequaltypeusgs_rec.last_update_date,
2158 x_created_by => p_typequaltypeusgs_rec.created_by,
2160 x_last_update_login => p_typequaltypeusgs_rec.last_update_login,
2161 x_terr_type_id => p_terrtype_id,
2162 x_qual_type_usg_id => p_typequaltypeusgs_rec.qual_type_usg_id,
2163 x_org_id => p_typequaltypeusgs_rec.org_id
2164 );
2165 -- Save the typequaltype_usg_id
2166 x_typequaltypeusgs_id := l_typequaltype_usg_id;
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');
2176 fnd_message.set_name ('PROC_NAME', l_api_name);
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;
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;
2204 fnd_msg_pub.count_and_get (
2205 p_count => x_msg_count,
2206 p_data => x_msg_data
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;
2216
2217 --
2218 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2219 THEN
2220 fnd_msg_pub.add_exc_msg (
2221 g_pkg_name,
2222 'Create_TerrTypeQualType_Usage' || SQLERRM
2223 );
2224 END IF;
2225 --
2226
2227 END create_terrtypequaltype_usage;
2228
2229 --
2230 --
2231 -- ***************************************************
2232 -- start of comments
2233 -- ***************************************************
2234 -- API name : Create_TerrTypeQualType_Usage
2235 -- Type : PUBLIC
2236 -- Function : To create Territories type qualifier usages
2237 --
2238 -- Pre-reqs :
2239 -- Parameters:
2240 -- IN :
2241 -- Required
2242 -- Parameter Name Data Type Default
2243 -- P_TerrType_Id NUMBER
2244 -- P_TerrTypeUsg_Id NUMBER;
2245 -- P_TypeQualTypeUsgs_Tbl TypeQualTypeUsgs_Tbl_Type := G_Miss_TypeQualTypeUsgs_Tbl
2246 --
2247 -- OUT :
2248 -- Parameter Name Data Type
2249 -- X_Return_Status VARCHAR2(1)
2250 -- X_TypeQualTypeUsgs_Out_Tbl TypeQualTypeUsgs_Out_Tbl,
2251 --
2252 -- Notes:
2253 --
2254 --
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,
2264 p_terrtypeusg_id IN NUMBER,
2265 p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type
2266 := g_miss_typequaltypeusgs_tbl,
2267 x_return_status OUT NOCOPY VARCHAR2,
2268 x_msg_count OUT NOCOPY NUMBER,
2269 x_msg_data OUT NOCOPY VARCHAR2,
2270 x_typequaltypeusgs_out_tbl OUT NOCOPY typequaltypeusgs_out_tbl_type
2271 )
2272 AS
2273 l_rowid ROWID;
2274 l_return_status VARCHAR2(1);
2275 l_typequaltypeusg_id NUMBER;
2276 l_typequaltypeusgs_tbl_count NUMBER := p_typequaltypeusgs_tbl.COUNT;
2277 l_typeqtypusg_out_tbl_count NUMBER;
2278 l_typequaltypeusgs_out_rec typequaltypeusgs_out_rec_type;
2282 := 'Create_Terr_Qualtype_Usage';
2279 l_typequaltypeusgs_out_tbl typequaltypeusgs_out_tbl_type;
2280 l_counter NUMBER;
2281 l_api_name CONSTANT VARCHAR2(30)
2283 l_api_version_number CONSTANT NUMBER := 1.0;
2284 BEGIN
2285 --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage TBL: Entering API');
2286
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
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)
2303 THEN
2304 fnd_msg_pub.initialize;
2305 END IF;
2306
2307 -- Debug Message
2308 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2309 THEN
2310 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2311 fnd_message.set_name ('PROC_NAME', l_api_name);
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
2321 --
2322 --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage TBL: Before Calling Create_TerrTypeQualType_Usage');
2323 create_terrtypequaltype_usage (
2324 p_api_version_number => p_api_version_number,
2325 p_init_msg_list => p_init_msg_list,
2326 p_commit => p_commit,
2327 p_validation_level => p_validation_level,
2328 p_terrtype_id => p_terrtype_id,
2329 p_terrtypeusg_id => p_terrtypeusg_id,
2330 p_typequaltypeusgs_rec => p_typequaltypeusgs_tbl (l_counter),
2331 x_typequaltypeusgs_id => l_typequaltypeusg_id,
2332 x_return_status => l_return_status,
2333 x_msg_count => x_msg_count,
2334 x_msg_data => x_msg_data,
2335 x_typequaltypeusgs_out_rec => l_typequaltypeusgs_out_rec
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 :=
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 :=
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;
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
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;
2378 END IF;
2379 END LOOP;
2380
2381 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2382 THEN
2383 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2384 fnd_message.set_name ('PROC_NAME', l_api_name);
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
2394 -- Standard call to get message count and if count is 1, get message info.
2395 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2396 --dbms_ourtput.put_line('Create_TerrTypeQualType_Usage TBL: Exiting API');
2397 --
2398
2399 END create_terrtypequaltype_usage;
2400
2401 --
2402 -- ***************************************************
2403 -- start of comments
2404 -- ***************************************************
2405 -- API name : Create_TerrType_QualIfier
2406 -- Type : PUBLIC
2410 -- Pre-reqs :
2407 -- Function : To create Territories qualifier
2408 --
2409 --
2411 -- Parameters:
2412 -- IN :
2413 -- Required
2414 -- Parameter Name Data Type Default
2415 -- p_terrType_id NUMBER
2416 -- P_TerrTypeQual_Rec TerrTypeQual_Rec_Type := G_Miss_TerrTypeQual_Rec
2417 --
2418 -- OUT NOCOPY :
2419 -- Parameter Name Data Type
2420 -- X_TerrTypeQual_Id NUMBER
2421 -- X_Return_Status VARCHAR2(1)
2422 -- X_TerrTypeQual_Out_Rec TerrTypeQual_Out_Rec
2423 --
2424 -- Notes: This is a an overloaded procedure
2425 --
2426 --
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,
2436 x_return_status OUT NOCOPY VARCHAR2,
2437 x_msg_count OUT NOCOPY NUMBER,
2438 x_msg_data OUT NOCOPY VARCHAR2,
2439 x_terrtypequal_id OUT NOCOPY NUMBER,
2440 x_terrtypequal_out_rec OUT NOCOPY terrtypequal_out_rec_type
2441 )
2442 AS
2443 l_rowid ROWID;
2444 l_terrtypequal_id NUMBER := p_terrtypequal_rec.terr_type_qual_id;
2445 l_api_name CONSTANT VARCHAR2(30)
2446 := 'Create_TerrType_Qualifier';
2447 l_api_version_number CONSTANT NUMBER := 1.0;
2448 BEGIN
2449 --dbms_ourtput.put_line('Create_TerrType_Qualifier REC: Entering API');
2450
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
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)
2467 THEN
2468 fnd_msg_pub.initialize;
2469 END IF;
2470
2471 -- Debug Message
2472 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2473 THEN
2474 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2475 fnd_message.set_name ('PROC_NAME', l_api_name);
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
2485 -- Debug message
2486 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2487 THEN
2488 fnd_message.set_name ('JTF', 'JTF_TERR_VALIDATE_MSG');
2489 fnd_message.set_token ('PROC_NAME', 'validate_qualifier');
2490 fnd_msg_pub.add;
2491 END IF;
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,
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;
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;
2518 END IF;
2519
2520 -- Call insert terrtype_Qualifier table handler
2521 --dbms_ourtput.put_line('Create_TerrType_Qualifier REC: JTF_TERR_TYPE_QUAL_PKG.Insert_Row');
2522 jtf_terr_type_qual_pkg.insert_row (
2523 x_rowid => l_rowid,
2524 x_terr_type_qual_id => l_terrtypequal_id,
2525 x_last_update_date => p_terrtypequal_rec.last_update_date,
2526 x_last_updated_by => p_terrtypequal_rec.last_updated_by,
2527 x_creation_date => p_terrtypequal_rec.creation_date,
2528 x_created_by => p_terrtypequal_rec.created_by,
2529 x_last_update_login => p_terrtypequal_rec.last_update_login,
2530 x_terr_type_id => p_terrtype_id,
2531 x_qual_usg_id => p_terrtypequal_rec.qual_usg_id,
2532 x_in_use_flag => p_terrtypequal_rec.in_use_flag,
2533 x_exclusive_use_flag => p_terrtypequal_rec.exclusive_use_flag,
2534 x_overlap_allowed_flag => p_terrtypequal_rec.overlap_allowed_flag,
2538 --
2535 x_qualifier_mode => p_terrtypequal_rec.qualifier_mode,
2536 x_org_id => p_terrtypequal_rec.org_id
2537 );
2539 -- Save the terr_qual_id returned by the table handler
2540 x_terrtypequal_id := l_terrtypequal_id;
2541
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
2551 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2552 fnd_message.set_name ('PROC_NAME', l_api_name);
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
2562 -- Standard call to get message count and if count is 1, get message info.
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;
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;
2581 fnd_msg_pub.count_and_get (
2582 p_count => x_msg_count,
2583 p_data => x_msg_data
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;
2593
2594 --
2595 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2596 THEN
2597 fnd_msg_pub.add_exc_msg (
2598 g_pkg_name,
2599 'Error create_terrtype_qualifier ' || SQLERRM
2600 );
2601 END IF;
2602 --
2603
2604 END create_terrtype_qualifier;
2605
2606 --
2607 --
2608 -- ***************************************************
2609 -- start of comments
2610 -- ***************************************************
2611 -- API name : Create_TerrType_Qualifier
2612 -- Type : PUBLIC
2613 -- Function : To create Territories type qualifier
2614 --
2615 -- Pre-reqs :
2616 -- Parameters:
2617 -- IN :
2618 -- Required
2619 -- Parameter Name Data Type Default
2620 -- p_terrType_id NUMBER
2621 -- P_TerrTypeQual_Tbl TerrTypeQual_Tbl_Type := G_Miss_TerrTypeQual_Tbl
2622 --
2623 -- OUT :
2624 -- Parameter Name Data Type
2625 -- X_Return_Status VARCHAR2(1)
2626 -- X_TerrTypeQual_Out_Tbl TerrTypeQual_Out_Tbl
2627 --
2628 -- Notes: This is a an overloaded procedure. This one
2629 -- will call the overloade procedure for records
2630 -- creation
2631 --
2632 --
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,
2642 x_return_status OUT NOCOPY VARCHAR2,
2643 x_msg_count OUT NOCOPY NUMBER,
2644 x_msg_data OUT NOCOPY VARCHAR2,
2645 x_terrtypequal_out_tbl OUT NOCOPY terrtypequal_out_tbl_type
2646 )
2647 AS
2648 --l_rowid ROWID;
2649 l_terrtypequal_id NUMBER;
2650 l_return_status VARCHAR2(1);
2651 l_terrtypequal_tbl_count NUMBER := p_terrtypequal_tbl.COUNT;
2652 l_terrtypequal_out_tbl_count NUMBER;
2653 l_terrtypequal_out_tbl terrtypequal_out_tbl_type;
2654 l_terrtypequal_out_rec terrtypequal_out_rec_type;
2655 l_counter NUMBER;
2656 l_api_name CONSTANT VARCHAR2(30)
2657 := 'Create_TerrType_Qualifier';
2658 l_api_version_number CONSTANT NUMBER := 1.0;
2659 BEGIN
2660 --dbms_ourtput.put_line('Create_TerrType_Qualifier TBL: Entering API');
2661
2662 -- Standard Start of API savepoint
2666 IF NOT fnd_api.compatible_api_call (
2663 SAVEPOINT create_terr_qual_pvt;
2664
2665 -- Standard call to check for call compatibility.
2667 l_api_version_number,
2668 p_api_version_number,
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)
2678 THEN
2679 fnd_msg_pub.initialize;
2680 END IF;
2681
2682 -- Debug Message
2683 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2684 THEN
2685 fnd_message.set_name ('JTF', 'Create_Type_Qual PVT: Start');
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
2695 LOOP
2696 --
2697 --dbms_ourtput.put_line('Create_TerrType_Qualifier TBL: Before Calling Create_TerrType_Qualifier');
2698 create_terrtype_qualifier (
2699 p_api_version_number => p_api_version_number,
2700 p_init_msg_list => p_init_msg_list,
2701 p_commit => p_commit,
2702 p_validation_level => p_validation_level,
2703 p_terrtype_id => p_terrtype_id,
2704 p_terrtypequal_rec => p_terrtypequal_tbl (l_counter),
2705 x_terrtypequal_id => l_terrtypequal_id,
2706 x_return_status => l_return_status,
2707 x_msg_count => x_msg_count,
2708 x_msg_data => x_msg_data,
2709 x_terrtypequal_out_rec => l_terrtypequal_out_rec
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;
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 :=
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;
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;
2752 END IF;
2753 END LOOP;
2754
2755 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2756 THEN
2757 fnd_message.set_name ('JTF', 'Create_Type_Qual PVT: End');
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
2767 -- Standard call to get message count and if count is 1, get message info.
2768 fnd_msg_pub.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2769
2770 --dbms_ourtput.put_line('Create_TerrType_Qualifier TBL: Exiting API');
2771 --
2772
2773 END create_terrtype_qualifier;
2774
2775 --
2776 -- *******************************************************
2777 -- Start of Comments
2778 -- *******************************************************
2779 -- API Name: Update_TerrType_Record
2780 -- Type :
2781 -- Pre-Req :
2782 -- Parameters:
2783 -- IN
2784 -- Required:
2785 -- Parameter Name Data Type Default
2786 -- P_TerrType_Rec TerrType_Rec_Type := G_MISS_TERRTYPE_REC
2787 --
2788 -- Optional:
2789 -- OUT:
2790 -- Parameter Name Data Type Default
2791 -- X_Return_Status VARCHAR2
2792 -- X_TerrType_Out_rec TerrType_Out_Rec_Type
2793 --
2794 -- Note:
2795 --
2796 -- End of Comments
2797 --
2798 PROCEDURE update_terrtype_record (
2799 p_api_version_number IN NUMBER,
2803 p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
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,
2804 x_return_status OUT NOCOPY VARCHAR2,
2805 x_msg_count OUT NOCOPY NUMBER,
2806 x_msg_data OUT NOCOPY VARCHAR2,
2807 x_terrtype_out_rec OUT NOCOPY terrtype_out_rec_type
2808 )
2809 AS
2810 CURSOR c_getterrtype (l_terrtype_id NUMBER)
2811 IS
2812 SELECT ROWID,
2813 terr_type_id,
2814 last_updated_by,
2815 last_update_date,
2816 created_by,
2817 creation_date,
2818 last_update_login,
2819 application_short_name,
2820 name,
2821 enabled_flag,
2822 description,
2823 start_date_active,
2824 end_date_active,
2825 org_id,
2826 attribute_category,
2827 attribute1,
2828 attribute2,
2829 attribute3,
2830 attribute4,
2831 attribute5,
2832 attribute6,
2833 attribute7,
2834 attribute8,
2835 attribute9,
2836 attribute10,
2837 attribute11,
2838 attribute12,
2839 attribute13,
2840 attribute14,
2841 attribute15
2842 FROM jtf_terr_types
2843 WHERE terr_type_id = l_terrtype_id
2844 FOR UPDATE NOWAIT;
2845
2846 --
2847 --Local variable declaration
2848 l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrType_Record';
2849 l_rowid VARCHAR2(50);
2850 l_api_version_number CONSTANT NUMBER := 1.0;
2851 l_return_status VARCHAR2(1);
2852 l_ref_terrtype_rec terrtype_rec_type;
2853 BEGIN
2854 --dbms_ourtput.put_line('Update_TerrType_Record PVT: Entering API');
2855
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
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)
2872 THEN
2873 fnd_msg_pub.initialize;
2874 END IF;
2875
2876 -- Debug Message
2877 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2878 THEN
2879 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2880 fnd_message.set_name ('PROC_NAME', l_api_name);
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 --
2890 --dbms_ourtput.put_line('Update_TerrType_Record PVT:Before fetch');
2891 FETCH c_getterrtype INTO l_rowid,
2892 l_ref_terrtype_rec.terr_type_id,
2893 l_ref_terrtype_rec.last_updated_by,
2894 l_ref_terrtype_rec.last_update_date,
2895 l_ref_terrtype_rec.created_by,
2896 l_ref_terrtype_rec.creation_date,
2897 l_ref_terrtype_rec.last_update_login,
2898 l_ref_terrtype_rec.application_short_name,
2899 l_ref_terrtype_rec.name,
2900 l_ref_terrtype_rec.enabled_flag,
2901 l_ref_terrtype_rec.description,
2902 l_ref_terrtype_rec.start_date_active,
2903 l_ref_terrtype_rec.end_date_active,
2904 l_ref_terrtype_rec.org_id,
2905 l_ref_terrtype_rec.attribute_category,
2906 l_ref_terrtype_rec.attribute1,
2907 l_ref_terrtype_rec.attribute2,
2908 l_ref_terrtype_rec.attribute3,
2909 l_ref_terrtype_rec.attribute4,
2910 l_ref_terrtype_rec.attribute5,
2911 l_ref_terrtype_rec.attribute6,
2912 l_ref_terrtype_rec.attribute7,
2913 l_ref_terrtype_rec.attribute8,
2914 l_ref_terrtype_rec.attribute9,
2915 l_ref_terrtype_rec.attribute10,
2916 l_ref_terrtype_rec.attribute11,
2917 l_ref_terrtype_rec.attribute12,
2918 l_ref_terrtype_rec.attribute13,
2922 --
2919 l_ref_terrtype_rec.attribute14,
2920 l_ref_terrtype_rec.attribute15;
2921
2923 --dbms_ourtput.put_line('Update_TerrType_Record PVT: After fetch');
2924 IF (c_getterrtype%NOTFOUND)
2925 THEN
2926 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2927 THEN
2928 fnd_message.set_name ('JTF', 'API_MISSING_UPDATE_TARGET');
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;
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
2945 fnd_message.set_name ('JTF', 'JTF_TERR_VALIDATE_MSG');
2946 fnd_message.set_token ('PROC_NAME', 'Validate_TerrType_Record');
2947 fnd_msg_pub.add;
2948 END IF;
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
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 --
2967 --dbms_ourtput.put_line('Update_TerrType_Record PVT: Before Calling JTF_TERR_TYPES_PKG.Update_Row');
2968 jtf_terr_types_pkg.update_row (
2969 x_rowid => l_rowid,
2970 x_terr_type_id => p_terrtype_rec.terr_type_id,
2971 x_last_updated_by => p_terrtype_rec.last_updated_by,
2972 x_last_update_date => p_terrtype_rec.last_update_date,
2973 x_created_by => p_terrtype_rec.created_by,
2974 x_creation_date => p_terrtype_rec.creation_date,
2975 x_last_update_login => p_terrtype_rec.last_update_login,
2976 x_application_short_name => p_terrtype_rec.application_short_name,
2977 x_name => p_terrtype_rec.name,
2978 x_enabled_flag => p_terrtype_rec.enabled_flag,
2979 x_description => p_terrtype_rec.description,
2980 x_start_date_active => p_terrtype_rec.start_date_active,
2981 x_end_date_active => p_terrtype_rec.end_date_active,
2982 x_attribute_category => p_terrtype_rec.attribute_category,
2983 x_attribute1 => p_terrtype_rec.attribute1,
2984 x_attribute2 => p_terrtype_rec.attribute2,
2985 x_attribute3 => p_terrtype_rec.attribute3,
2986 x_attribute4 => p_terrtype_rec.attribute4,
2987 x_attribute5 => p_terrtype_rec.attribute5,
2988 x_attribute6 => p_terrtype_rec.attribute6,
2989 x_attribute7 => p_terrtype_rec.attribute7,
2990 x_attribute8 => p_terrtype_rec.attribute8,
2991 x_attribute9 => p_terrtype_rec.attribute9,
2992 x_attribute10 => p_terrtype_rec.attribute10,
2993 x_attribute11 => p_terrtype_rec.attribute11,
2994 x_attribute12 => p_terrtype_rec.attribute12,
2995 x_attribute13 => p_terrtype_rec.attribute13,
2996 x_attribute14 => p_terrtype_rec.attribute14,
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)
3006 THEN
3007 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
3008 fnd_message.set_name ('PROC_NAME', l_api_name);
3009 fnd_msg_pub.add;
3010 END IF;
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
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;
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 );
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,
3041 p_data => x_msg_data
3045 ROLLBACK TO update_terrtype_pvt;
3042 );
3043 WHEN OTHERS
3044 THEN
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 (
3054 g_pkg_name,
3055 'Error inside Update_TerrType_Record ' || SQLERRM
3056 );
3057 END IF;
3058 --
3059
3060 END update_terrtype_record;
3061
3062 --
3063 --
3064 -- *******************************************************
3065 -- Start of Comments
3066 -- *******************************************************
3067 -- API Name: Update_TerrType_Usages
3068 -- Type :
3069 -- Pre-Req :
3070 -- Parameters:
3071 -- IN
3072 -- Required:
3073 -- Parameter Name Data Type Default
3074 -- P_TerrTypeUsgs_Rec TerrTypeUsgs_Rec_Type := G_MISS_TERRTYPEUSGS_REC
3075 --
3076 -- Optional:
3077 -- OUT:
3078 -- Parameter Name Data Type Default
3079 -- X_Return_Status VARCHAR2
3080 -- X_TerrTypeUsgs_Out_Rec TerrTypeUsgs_Out_Rec_Type
3081 --
3082 --
3083 -- Note:
3084 --
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,
3094 x_msg_count OUT NOCOPY NUMBER,
3095 x_msg_data OUT NOCOPY VARCHAR2,
3096 x_terrtypeusgs_out_rec OUT NOCOPY terrtypeusgs_out_rec_type
3097 )
3098 AS
3099 CURSOR c_getterrtypeusage (l_terr_type_usg_id NUMBER)
3100 IS
3101 SELECT ROWID,
3102 terr_type_usg_id,
3103 last_updated_by,
3104 last_update_date,
3105 created_by,
3106 creation_date,
3107 last_update_login,
3108 terr_type_id,
3109 source_id
3110 FROM jtf_terr_type_usgs
3111 WHERE terr_type_usg_id = l_terr_type_usg_id
3112 FOR UPDATE NOWAIT;
3113
3114 --Local variable declaration
3115 l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrType_Usages';
3116 l_rowid VARCHAR2(50);
3117 l_api_version_number CONSTANT NUMBER := 1.0;
3118 l_return_status VARCHAR2(1);
3119 l_ref_terrtypeusgs_rec terrtypeusgs_rec_type;
3120 BEGIN
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
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)
3137 THEN
3138 fnd_msg_pub.initialize;
3139 END IF;
3140
3141 -- Debug Message
3142 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3143 THEN
3144 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
3145 fnd_message.set_name ('PROC_NAME', l_api_name);
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
3155 INTO l_rowid,
3156 l_ref_terrtypeusgs_rec.terr_type_usg_id,
3157 l_ref_terrtypeusgs_rec.last_updated_by,
3158 l_ref_terrtypeusgs_rec.last_update_date,
3159 l_ref_terrtypeusgs_rec.created_by,
3160 l_ref_terrtypeusgs_rec.creation_date,
3161 l_ref_terrtypeusgs_rec.last_update_login,
3162 l_ref_terrtypeusgs_rec.terr_type_id,
3163 l_ref_terrtypeusgs_rec.source_id;
3164
3165 IF (c_getterrtypeusage%NOTFOUND)
3166 THEN
3167 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3168 THEN
3169 --dbms_ourtput.put_line('Update_TerrType_Usages REC: C_GetTerrTypeUsage%NOTFOUND');
3170 fnd_message.set_name ('JTF', 'API_MISSING_UPDATE_TARGET');
3171 fnd_message.set_token ('INFO', 'TERRITORY_TYPE_USAGE', FALSE);
3172 fnd_msg_pub.add;
3173 END IF;
3174
3178 CLOSE c_getterrtypeusage;
3175 RAISE fnd_api.g_exc_error;
3176 END IF;
3177
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
3185 )
3186 THEN
3187 fnd_message.set_name ('JTF', 'JTF_TERR_VALIDATE_MSG');
3188 fnd_message.set_token ('PROC_NAME', 'Validate_TerrType_Usage');
3189 fnd_msg_pub.add;
3190 END IF;
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;
3209
3210 --dbms_ourtput.put_line('Update_TerrType_Usages REC: Before Calling JTF_TERR_TYPE_USGS_PKG.Update_Row');
3211 -- Call insert terr_Qual_Type_Usgs table handler
3212 jtf_terr_type_usgs_pkg.update_row (
3213 x_rowid => l_rowid,
3214 x_terr_type_usg_id => p_terrtypeusgs_rec.terr_type_usg_id,
3215 x_last_update_date => p_terrtypeusgs_rec.last_update_date,
3216 x_last_updated_by => p_terrtypeusgs_rec.last_updated_by,
3217 x_creation_date => p_terrtypeusgs_rec.creation_date,
3218 x_created_by => p_terrtypeusgs_rec.created_by,
3219 x_last_update_login => p_terrtypeusgs_rec.last_update_login,
3220 x_terr_type_id => p_terrtypeusgs_rec.terr_type_id,
3221 x_source_id => p_terrtypeusgs_rec.source_id,
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)
3231 THEN
3232 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
3233 fnd_message.set_name ('PROC_NAME', l_api_name);
3234 fnd_msg_pub.add;
3235 END IF;
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');
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;
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;
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;
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 (
3284 g_pkg_name,
3285 'Error inside Update_Territory_Usages ' || SQLERRM
3286 );
3287 END IF;
3288 --
3289
3290 END update_terrtype_usages;
3291
3292 --
3293 -- *******************************************************
3294 -- Start of Comments
3295 -- *******************************************************
3296 -- API Name: Update_TerrType_Usages
3297 -- Type :
3298 -- Pre-Req :
3299 -- Parameters:
3300 -- IN
3301 -- Required:
3302 -- Parameter Name Data Type Default
3303 -- P_TerrTypeUsgs_Tbl TerrTypeUsgs_Tbl_Type := G_MISS_TERRTYPEUSGS_TBL
3304 --
3305 -- Optional:
3306 -- OUT:
3310 --
3307 -- Parameter Name Data Type Default
3308 -- X_Return_Status VARCHAR2
3309 -- X_TerrTypeUsgs_Out_Tbl TerrTypeUsgs_Out_Tbl_Type
3311 --
3312 -- Note:
3313 --
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,
3323 x_msg_count OUT NOCOPY NUMBER,
3324 x_msg_data OUT NOCOPY VARCHAR2,
3325 x_terrtypeusgs_out_tbl OUT NOCOPY terrtypeusgs_out_tbl_type
3326 )
3327 AS
3328 l_rowid ROWID;
3329 l_return_status VARCHAR2(1);
3330 l_terr_qual_type_usg_id NUMBER;
3331 l_terrtypeusgs_tbl_count NUMBER := p_terrtypeusgs_tbl.COUNT;
3332 l_terrtypeusgs_out_tbl_count NUMBER;
3333 l_terrtypeusgs_out_tbl terrtypeusgs_out_tbl_type;
3334 l_terrtypeusgs_out_rec terrtypeusgs_out_rec_type;
3335 l_counter NUMBER;
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
3345 LOOP
3346 --
3347 --dbms_ourtput.put_line('Update_TerrType_Usages TBL: Before Calling Update_TerrType_Usages REC');
3348 update_terrtype_usages (
3349 p_api_version_number => p_api_version_number,
3350 p_init_msg_list => p_init_msg_list,
3351 p_commit => p_commit,
3352 p_validation_level => p_validation_level,
3353 p_terrtypeusgs_rec => p_terrtypeusgs_tbl (l_counter),
3354 x_return_status => l_return_status,
3355 x_msg_count => x_msg_count,
3356 x_msg_data => x_msg_data,
3357 x_terrtypeusgs_out_rec => l_terrtypeusgs_out_rec
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 :=
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 :=
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;
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;
3393
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;
3403 END IF;
3404 END LOOP;
3405 --dbms_ourtput.put_line('Update_TerrType_Usages TBL: Exiting API');
3406 --
3407 END update_terrtype_usages;
3408
3409 --
3410 -- *******************************************************
3411 -- Start of Comments
3412 -- *******************************************************
3413 -- API Name: Update_TerrTypeQualType_Usage
3414 -- Type :
3415 -- Pre-Req :
3416 -- Parameters:
3417 -- IN
3418 -- Required:
3419 -- Parameter Name Data Type Default
3420 --
3421 -- Optional:
3422 -- OUT:
3423 -- Parameter Name Data Type Default
3424 --
3425 -- Note:
3426 --
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,
3436 x_msg_count OUT NOCOPY NUMBER,
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,
3437 x_msg_data OUT NOCOPY VARCHAR2,
3438 x_typequaltypeusgs_out_rec OUT NOCOPY typequaltypeusgs_out_rec_type
3439 )
3440 AS
3441 CURSOR c_gettypequaltypeusgs (l_type_qual_type_usg_id NUMBER)
3442 IS
3443 SELECT ROWID,
3444 type_qtype_usg_id,
3445 last_updated_by,
3446 last_update_date,
3447 created_by,
3448 creation_date,
3449 last_update_login,
3450 terr_type_id,
3451 qual_type_usg_id
3452 FROM jtf_type_qtype_usgs
3453 WHERE type_qtype_usg_id = l_type_qual_type_usg_id
3454 FOR UPDATE NOWAIT;
3455
3456 --Local variable declaration
3457 l_api_name CONSTANT VARCHAR2(30)
3458 := 'Update_TerrTypeQualType_Usage';
3459 l_rowid VARCHAR2(50);
3460 l_api_version_number CONSTANT NUMBER := 1.0;
3461 l_return_status VARCHAR2(1);
3462 l_ref_typequaltypeusgs_rec typequaltypeusgs_rec_type;
3463 BEGIN
3464 --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage REC: Entering API');
3465
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
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)
3482 THEN
3483 fnd_msg_pub.initialize;
3484 END IF;
3485
3486 -- Debug Message
3487 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3488 THEN
3489 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
3490 fnd_message.set_name ('PROC_NAME', l_api_name);
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,
3500 l_ref_typequaltypeusgs_rec.type_qual_type_usg_id,
3501 l_ref_typequaltypeusgs_rec.last_updated_by,
3502 l_ref_typequaltypeusgs_rec.last_update_date,
3503 l_ref_typequaltypeusgs_rec.created_by,
3504 l_ref_typequaltypeusgs_rec.creation_date,
3505 l_ref_typequaltypeusgs_rec.last_update_login,
3506 l_ref_typequaltypeusgs_rec.terr_type_id,
3507 l_ref_typequaltypeusgs_rec.qual_type_usg_id;
3508
3509 IF (c_gettypequaltypeusgs%NOTFOUND)
3510 THEN
3511 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3512 THEN
3513 --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage REC: C_GetTypeQualTypeUsgs%NOTFOUND');
3514 fnd_message.set_name ('JTF', 'API_MISSING_UPDATE_TARGET');
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
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
3529 fnd_message.set_name ('JTF', 'JTF_TERR_VALIDATE_MSG');
3530 fnd_message.set_token ('PROC_NAME', 'Validate_Territory_Usage');
3531 fnd_msg_pub.add;
3532 END IF;
3533
3534 -- Invoke validation procedures
3535 validate_type_qtype_usage (
3536 p_init_msg_list => p_init_msg_list,
3537 x_return_status => x_return_status,
3538 x_msg_count => x_msg_count,
3539 x_msg_data => x_msg_data,
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;
3549
3550 --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage REC: JTF_TYPE_QTYPE_USGS_PKG.Update_Row');
3551 jtf_type_qtype_usgs_pkg.update_row (
3552 x_rowid => l_rowid,
3553 x_type_qtype_usg_id => p_typequaltypeusgs_rec.type_qual_type_usg_id,
3554 x_last_updated_by => p_typequaltypeusgs_rec.last_updated_by,
3555 x_last_update_date => p_typequaltypeusgs_rec.last_update_date,
3556 x_created_by => p_typequaltypeusgs_rec.created_by,
3560 x_qual_type_usg_id => p_typequaltypeusgs_rec.qual_type_usg_id,
3557 x_creation_date => p_typequaltypeusgs_rec.creation_date,
3558 x_last_update_login => p_typequaltypeusgs_rec.last_update_login,
3559 x_terr_type_id => p_typequaltypeusgs_rec.terr_type_id,
3561 x_org_id => p_typequaltypeusgs_rec.org_id
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)
3571 THEN
3572 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
3573 fnd_message.set_name ('PROC_NAME', l_api_name);
3574 fnd_msg_pub.add;
3575 END IF;
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');
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 (
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;
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;
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 (
3623 g_pkg_name,
3624 'Update error inside Update_TerrTypeQualType_Usage'
3625 );
3626 END IF;
3627 --
3628
3629 END update_terrtypequaltype_usage;
3630
3631 --
3632 --
3633 -- *******************************************************
3634 -- Start of Comments
3635 -- *******************************************************
3636 -- API Name: Update_TerrTypeQualType_Usage
3637 -- Type :
3638 -- Pre-Req :
3639 -- Parameters:
3640 -- IN
3641 -- Required:
3642 -- Parameter Name Data Type Default
3643 --
3644 -- Optional:
3645 -- OUT:
3646 -- Parameter Name Data Type Default
3647 --
3648 -- Note:
3649 --
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,
3659 x_msg_count OUT NOCOPY NUMBER,
3660 x_msg_data OUT NOCOPY VARCHAR2,
3661 x_typequaltypeusgs_out_tbl OUT NOCOPY typequaltypeusgs_out_tbl_type
3662 )
3663 AS
3664 l_rowid ROWID;
3665 l_return_status VARCHAR2(1);
3666 l_typequaltypeusg_id NUMBER;
3667 l_typequaltypeusgs_tbl_count NUMBER := p_typequaltypeusgs_tbl.COUNT;
3668 l_typeqtypusgs_out_tbl_count NUMBER;
3669 l_typeqtypusgs_out_tbl typequaltypeusgs_out_tbl_type;
3670 l_typeqtypusgs_out_rec typequaltypeusgs_out_rec_type;
3671 l_counter NUMBER;
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
3681 LOOP
3682 --
3686 p_init_msg_list => p_init_msg_list,
3683 --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage TBL: Before Calling Update_TerrTypeQualType_Usage REC');
3684 update_terrtypequaltype_usage (
3685 p_api_version_number => p_api_version_number,
3687 p_commit => p_commit,
3688 p_validation_level => p_validation_level,
3689 p_typequaltypeusgs_rec => p_typequaltypeusgs_tbl (l_counter),
3690 x_return_status => l_return_status,
3691 x_msg_count => x_msg_count,
3692 x_msg_data => x_msg_data,
3693 x_typequaltypeusgs_out_rec => l_typeqtypusgs_out_rec
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 :=
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 :=
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;
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
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;
3735 END IF;
3736 END LOOP;
3737 --dbms_ourtput.put_line('Update_TerrTypeQualType_Usage TBL: Exiting API');
3738 --
3739
3740 END update_terrtypequaltype_usage;
3741
3742 --
3743 -- *******************************************************
3744 -- Start of Comments
3745 -- *******************************************************
3746 -- API Name: Update_TerrType_Qualifier
3747 -- Type :
3748 -- Pre-Req :
3749 -- Parameters:
3750 -- IN :
3751 -- Required
3752 -- Parameter Name Data Type Default
3753 -- P_TerrTypeQual_Rec TerrTypeQual_Rec_Type := G_Miss_TerrTypeQual_Rec
3754 --
3755 -- OUT NOCOPY :
3756 -- Parameter Name Data Type
3757 -- X_Return_Status VARCHAR2
3758 -- X_Terr_Qual_Out_Rec Terr_Qual_Out_Rec_Type
3759 --
3760 -- Note:
3761 --
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,
3771 x_msg_count OUT NOCOPY NUMBER,
3772 x_msg_data OUT NOCOPY VARCHAR2,
3773 x_terrtypequal_out_rec OUT NOCOPY terrtypequal_out_rec_type
3774 )
3775 AS
3776 CURSOR c_getterrqualifier (l_terrtypequal_id NUMBER)
3777 IS
3778 SELECT ROWID,
3779 terr_type_qual_id,
3780 last_updated_by,
3781 last_update_date,
3782 created_by,
3783 creation_date,
3784 last_update_login,
3785 qual_usg_id,
3786 terr_type_id,
3787 exclusive_use_flag,
3788 overlap_allowed_flag,
3789 in_use_flag
3790 FROM jtf_terr_type_qual
3791 WHERE terr_type_qual_id = l_terrtypequal_id
3792 FOR UPDATE NOWAIT;
3793
3794 --
3795 --Local variable declaration
3796 l_api_name CONSTANT VARCHAR2(30)
3797 := 'Update_Terr_Type_Qualifier';
3798 l_rowid VARCHAR2(50);
3799 l_api_version_number CONSTANT NUMBER := 1.0;
3800 l_return_status VARCHAR2(1);
3801 l_ref_terrtypequal_rec terrtypequal_rec_type;
3802 BEGIN
3803 --dbms_ourtput.put_line('Update_TerrType_Qualifier REC: Entering API');
3807
3804
3805 -- Standard start of PAI savepoint
3806 SAVEPOINT update_terrtype_qualifier;
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
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)
3821 THEN
3822 fnd_msg_pub.initialize;
3823 END IF;
3824
3825 -- Debug Message
3826 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
3827 THEN
3828 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
3829 fnd_message.set_name ('PROC_NAME', l_api_name);
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 --
3839 FETCH c_getterrqualifier
3840 INTO l_rowid,
3841 l_ref_terrtypequal_rec.terr_type_qual_id,
3842 l_ref_terrtypequal_rec.last_updated_by,
3843 l_ref_terrtypequal_rec.last_update_date,
3844 l_ref_terrtypequal_rec.created_by,
3845 l_ref_terrtypequal_rec.creation_date,
3846 l_ref_terrtypequal_rec.last_update_login,
3847 l_ref_terrtypequal_rec.qual_usg_id,
3848 l_ref_terrtypequal_rec.terr_type_id,
3849 l_ref_terrtypequal_rec.exclusive_use_flag,
3850 l_ref_terrtypequal_rec.overlap_allowed_flag,
3851 l_ref_terrtypequal_rec.in_use_flag;
3852 --
3853 IF (c_getterrqualifier%NOTFOUND)
3854 THEN
3855 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3856 THEN
3857 fnd_message.set_name ('JTF', 'API_MISSING_UPDATE_TARGET');
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
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
3874 fnd_message.set_name ('JTF', 'JTF_TERR_VALIDATE_MSG');
3875 fnd_message.set_token ('PROC_NAME', 'validate_qualifier');
3876 fnd_msg_pub.add;
3877 END IF;
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,
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;
3895 --
3896 END IF;
3897
3898 --dbms_ourtput.put_line('Update_TerrType_Qualifier REC: Before Calling JTF_TERR_TYPE_QUAL_PKG.Update_Row');
3899 jtf_terr_type_qual_pkg.update_row (
3900 x_rowid => l_rowid,
3901 x_terr_type_qual_id => p_terrtypequal_rec.terr_type_qual_id,
3902 x_last_update_date => p_terrtypequal_rec.last_update_date,
3903 x_last_updated_by => p_terrtypequal_rec.last_updated_by,
3904 x_creation_date => p_terrtypequal_rec.creation_date,
3905 x_created_by => p_terrtypequal_rec.created_by,
3906 x_last_update_login => p_terrtypequal_rec.last_update_login,
3907 x_qual_usg_id => p_terrtypequal_rec.qual_usg_id,
3908 x_terr_type_id => p_terrtypequal_rec.terr_type_id,
3909 x_exclusive_use_flag => p_terrtypequal_rec.exclusive_use_flag,
3910 x_overlap_allowed_flag => p_terrtypequal_rec.overlap_allowed_flag,
3911 x_in_use_flag => p_terrtypequal_rec.in_use_flag,
3912 x_qualifier_mode => p_terrtypequal_rec.qualifier_mode,
3913 x_org_id => p_terrtypequal_rec.org_id
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)
3923 THEN
3924 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
3925 fnd_message.set_name ('PROC_NAME', l_api_name);
3926 fnd_msg_pub.add;
3927 END IF;
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)
3936
3933 THEN
3934 COMMIT WORK;
3935 END IF;
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 :=
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 );
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;
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;
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 (
3977 g_pkg_name,
3978 'Error inside Update_TerrType_Qualifer ' || SQLERRM
3979 );
3980 END IF;
3981 END update_terrtype_qualifier;
3982
3983 --
3984 --
3985 -- *******************************************************
3986 -- Start of Comments
3987 -- *******************************************************
3988 -- API Name: Update_TerrType_Qualifier
3989 -- Type :
3990 -- Pre-Req :
3991 -- Parameters:
3992 -- IN :
3993 -- Required
3994 -- Parameter Name Data Type Default
3995 -- P_TerrTypeQual_Tbl TerrTypeQual_Tbl_Type := G_Miss_TerrTypeQual_Tbl
3996 --
3997 -- OUT NOCOPY :
3998 -- Parameter Name Data Type
3999 -- X_Return_Status VARCHAR2
4000 -- X_TerrTypeQual_Out_Tbl TerrTypeQual_Out_Tbl_Type
4001 --
4002 -- Note:
4003 --
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,
4013 x_msg_count OUT NOCOPY NUMBER,
4014 x_msg_data OUT NOCOPY VARCHAR2,
4015 x_terrtypequal_out_tbl OUT NOCOPY terrtypequal_out_tbl_type
4016 )
4017 AS
4018 l_terr_qual_id NUMBER;
4019 l_return_status VARCHAR2(1);
4020 l_terrtypequal_tbl_count NUMBER := p_terrtypequal_tbl.COUNT;
4021 l_terrtypequal_out_tbl_count NUMBER;
4022 l_terrtypequal_out_tbl terrtypequal_out_tbl_type;
4023 l_terrtypequal_out_rec terrtypequal_out_rec_type;
4024 l_counter NUMBER;
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
4034 LOOP
4035 --
4036 --dbms_ourtput.put_line('Update_TerrType_Qualifier TBL: Before calling Update_TerrType_Qualifier');
4037 update_terrtype_qualifier (
4038 p_api_version_number => p_api_version_number,
4039 p_init_msg_list => p_init_msg_list,
4040 p_commit => p_commit,
4041 p_validation_level => p_validation_level,
4042 p_terrtypequal_rec => p_terrtypequal_tbl (l_counter),
4043 x_return_status => l_return_status,
4044 x_msg_count => x_msg_count,
4045 x_msg_data => x_msg_data,
4046 x_terrtypequal_out_rec => l_terrtypequal_out_rec
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 :=
4059 fnd_api.g_ret_sts_unexp_error;
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 :=
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
4066 x_terrtypequal_out_tbl (l_counter).return_status :=
4067 fnd_api.g_ret_sts_success;
4068 END IF;
4069 --
4070
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;
4089 END IF;
4090 END LOOP;
4091 --
4092 --dbms_ourtput.put_line('Update_TerrType_Qualifier TBL: Exiting API');
4093 --
4094 END update_terrtype_qualifier;
4095
4096 --
4097 -- *******************************************************
4098 -- Start of Comments
4099 -- *******************************************************
4100 -- API Name: Delete_TerrType_Record
4101 -- Type :
4102 -- Pre-Req :
4103 -- Parameters:
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:
4113 -- OUT:
4114 -- Parameter Name Data Type Default
4115 -- X_Return_Status VARCHAR2
4116 --
4117 -- Note:
4118 --
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,
4128 x_msg_data OUT NOCOPY VARCHAR2
4129 )
4130 AS
4131 l_row_count NUMBER;
4132 l_api_name CONSTANT VARCHAR2(30) := 'Delete_TerrType_Record';
4133 l_api_version_number CONSTANT NUMBER := 1.0;
4134 l_return_status VARCHAR2(1);
4135 BEGIN
4136 --dbms_ourtput.put_line('Delete_TerrType_Record PVT: Entering API');
4137
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
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)
4154 THEN
4155 fnd_msg_pub.initialize;
4156 END IF;
4157
4158 -- Debug Message
4159 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
4160 THEN
4161 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
4162 fnd_message.set_name ('PROC_NAME', l_api_name);
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 --
4172 --Prepare message name
4173 fnd_message.set_name ('JTF', 'TERRTYPE_RECORDS_DELETED');
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
4183 fnd_message.set_name ('ITEMS_DELETED', l_row_count);
4184
4185 --Add message to API message list
4186 fnd_msg_pub.add;
4187
4188 -- Debug Message
4189 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
4190 THEN
4194 END IF;
4191 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
4192 fnd_message.set_name ('PROC_NAME', l_api_name);
4193 fnd_msg_pub.add;
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 --
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,
4212 p_data => x_msg_data
4213 );
4214
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 (
4224 g_pkg_name,
4225 'Error inside Delete_TerrType_Record ' || SQLERRM
4226 );
4227 END IF;
4228 --
4229 END delete_terrtype_record;
4230
4231 --
4232 -- *******************************************************
4233 -- Start of Comments
4234 -- *******************************************************
4235 -- API Name: Delete_TerrType_Usages
4236 -- Type :
4237 -- Pre-Req :
4238 -- Parameters:
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:
4248 -- OUT:
4249 -- Parameter Name Data Type Default
4250 -- X_Return_Status VARCHAR2
4251 --
4252 -- Note:
4253 --
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,
4263 x_msg_data OUT NOCOPY VARCHAR2
4264 )
4265 AS
4266 l_row_count NUMBER;
4267 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Territory_Usages';
4268 l_api_version_number CONSTANT NUMBER := 1.0;
4269 l_return_status VARCHAR2(1);
4270 BEGIN
4271 --dbms_ourtput.put_line('Delete_TerrType_Usages PVT: Entering API');
4272
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
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)
4289 THEN
4290 fnd_msg_pub.initialize;
4291 END IF;
4292
4293 -- Debug Message
4294 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
4295 THEN
4296 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
4297 fnd_message.set_name ('PROC_NAME', l_api_name);
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
4307 );
4308 --
4309 --Prepare message name
4310 fnd_message.set_name ('JTF', 'TERRTYPE_USGS_DELETED');
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
4320 fnd_message.set_name ('ITEMS_DELETED', l_row_count);
4321 --Add message to API message list
4322 fnd_msg_pub.add;
4323
4324 -- Debug Message
4325 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
4326 THEN
4327 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
4328 fnd_message.set_name ('PROC_NAME', l_api_name);
4329 fnd_msg_pub.add;
4330 END IF;
4331
4335 IF fnd_api.to_boolean (p_commit)
4332 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4333
4334 -- Standard check for p_commit
4336 THEN
4337 COMMIT WORK;
4338 END IF;
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;
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 (
4361 g_pkg_name,
4362 'Error inside Delete_TerrType_Usages ' || SQLERRM
4363 );
4364 END IF;
4365 --
4366
4367 END delete_terrtype_usages;
4368
4369 --
4370 -- *******************************************************
4371 -- Start of Comments
4372 -- *******************************************************
4373 -- API Name: Delete_TerrTypeQualType_Usage
4374 -- Type :
4375 -- Pre-Req :
4376 -- Parameters
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:
4386 -- OUT:
4387 -- Parameter Name Data Type Default
4388 -- X_Return_Status VARCHAR2
4389 --
4390 -- Note:
4391 --
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,
4401 x_msg_data OUT NOCOPY VARCHAR2
4402 )
4403 AS
4404 l_row_count NUMBER;
4405 l_api_name CONSTANT VARCHAR2(30)
4406 := 'Delete_TerrTypeQualType_Usage';
4407 l_api_version_number CONSTANT NUMBER := 1.0;
4408 l_return_status VARCHAR2(1);
4409 BEGIN
4410 --dbms_ourtput.put_line('Delete_TerrTypeQualType_Usage PVT: Entering API');
4411
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
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)
4428 THEN
4429 fnd_msg_pub.initialize;
4430 END IF;
4431
4432 -- Debug Message
4433 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
4434 THEN
4435 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
4436 fnd_message.set_name ('PROC_NAME', l_api_name);
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
4446 );
4447 --
4448 --
4449 --Prepare message name
4450 fnd_message.set_name ('JTF', 'TERRTYPE_QUALUSGS_DELETED');
4451
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
4461 fnd_message.set_name ('ITEMS_DELETED', l_row_count);
4462 --Add message to API message list
4463 fnd_msg_pub.add;
4464
4465 -- Debug Message
4466 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
4467 THEN
4468 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
4469 fnd_message.set_name ('PROC_NAME', l_api_name);
4470 fnd_msg_pub.add;
4471 END IF;
4472
4476 IF fnd_api.to_boolean (p_commit)
4473 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4474
4475 -- Standard check for p_commit
4477 THEN
4478 COMMIT WORK;
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;
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 (
4501 g_pkg_name,
4502 'Error inside Delete_TerrTypeQualType_Usage ' || SQLERRM
4503 );
4504 END IF;
4505 --
4506
4507 END delete_terrtypequaltype_usage;
4508
4509 --
4510 -- *******************************************************
4511 -- Start of Comments
4512 -- *******************************************************
4513 -- API Name: Delete_TerrType_Qualifier
4514 -- Type :
4515 -- Pre-Req :
4516 -- Parameters:
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:
4526 -- OUT:
4527 -- Parameter Name Data Type Default
4528 -- X_Return_Status VARCHAR2
4529 --
4530 -- Note:
4531 --
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,
4541 x_msg_data OUT NOCOPY VARCHAR2
4542 )
4543 AS
4544 l_row_count NUMBER;
4545 l_api_name CONSTANT VARCHAR2(30)
4546 := 'Delete_TerrType_Qualifier';
4547 l_api_version_number CONSTANT NUMBER := 1.0;
4548 l_return_status VARCHAR2(1);
4549 BEGIN
4550 --dbms_ourtput.put_line('Delete_TerrType_Qualifier PVT: Entering API');
4551
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
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)
4568 THEN
4569 fnd_msg_pub.initialize;
4570 END IF;
4571
4572 -- Debug Message
4573 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
4574 THEN
4575 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
4576 fnd_message.set_name ('PROC_NAME', l_api_name);
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
4586 );
4587 --
4588 --Prepare message name
4589 fnd_message.set_name ('JTF', 'TERRTYPE_QUALIFIERS_DELETED');
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
4599 fnd_message.set_name ('ITEMS_DELETED', l_row_count);
4600 --Add message to API message list
4601 fnd_msg_pub.add ();
4602
4603 -- Debug Message
4604 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
4605 THEN
4606 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
4607 fnd_message.set_name ('PROC_NAME', l_api_name);
4608 fnd_msg_pub.add;
4609 END IF;
4610
4611 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4612
4616 COMMIT WORK;
4613 -- Standard check for p_commit
4614 IF fnd_api.to_boolean (p_commit)
4615 THEN
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;
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 (
4639 g_pkg_name,
4640 'Delete error inside Delete_TerrType_Qualifier'
4641 );
4642 END IF;
4643 END delete_terrtype_qualifier;
4644
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
4654 )
4655 AS
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
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 --
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 --
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)
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
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
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
4750 -- Validate last updated by
4747 x_return_status := fnd_api.g_ret_sts_error;
4748 END IF;
4749
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
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
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 --
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 --);
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 );
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,
4815 p_data => x_msg_data
4816 );
4817 WHEN OTHERS
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 );
4827 --
4828
4829 END validate_terrtype_record;
4830
4831 --
4832 -- This procedure will check whether the qualifiers passed are
4833 -- valid.
4834 --
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 )
4844 AS
4845 l_counter NUMBER;
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)
4855 --);
4856
4857 --
4858 -- Check whether the qualfier is enabled and
4859 BEGIN
4860 IF p_terr_type_id IS NOT NULL
4861 THEN
4862 SELECT 'x'
4863 INTO l_temp
4864 FROM jtf_qual_usgs jqu,
4865 jtf_qual_type_usgs jqtu,
4866 jtf_type_qtype_usgs jtqu
4867 WHERE jtqu.terr_type_id = p_terr_type_id
4868 AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
4869 AND jqu.qual_usg_id = p_terrtypequal_rec.qual_usg_id
4870 AND jqu.enabled_flag = 'Y'
4871 AND jqtu.qual_type_id IN
4872 ( SELECT related_id
4873 FROM jtf_qual_type_denorm_v
4874 WHERE qual_type_id = jqtu.qual_type_id)
4875 AND rownum < 2;
4876 END IF;
4877 EXCEPTION
4878 WHEN NO_DATA_FOUND
4879 THEN
4880 --dbms_output.put_line (
4881 -- 'Validate_Qualifier: NO_DATA_FOUND Exception'
4882 --);
4886 fnd_msg_pub.count_and_get (
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;
4887 p_count => x_msg_count,
4888 p_data => x_msg_data
4889 );
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
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
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
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
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
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);
4979 --dbms_output.put_line ('Validate_Qualifier: Exiting API');
4980 EXCEPTION
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,
4990 p_data => x_msg_data
4991 );
4992 --
4993
4994 END validate_qualifier;
4995
4996 --
4997 ---------------------------------------------------------------------
4998 -- Validate the Territory Type Usage
4999 ---------------------------------------------------------------------
5000 -- Columns Validated
5001 -- Make sure a Territory Type Usage is specified
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
5011 := g_miss_terrtypeusgs_rec,
5012 p_terr_type_id IN NUMBER
5013 )
5017 BEGIN
5014 AS
5015 l_rec_counter NUMBER;
5016 l_validate_id NUMBER;
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
5025 l_validate_id := p_terr_type_id;
5026
5027 IF jtf_ctm_utility_pvt.fk_id_is_valid (
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 --);
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(' ||
5046 -- TO_CHAR (l_validate_id) ||
5047 -- ') is valid'
5048 --);
5049 END IF;
5050
5051 -- Validate the source_id
5052 l_validate_id := p_terrtypeusgs_rec.source_id;
5053
5054 -- Make sure the foreign key source_id is valid
5055 IF jtf_ctm_utility_pvt.fk_id_is_valid (
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');
5065 fnd_message.set_name ('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5066 fnd_message.set_token ('TABLE_NAME', 'JTF_SOURCES');
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)
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
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
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
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
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
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)
5153 fnd_msg_pub.add;
5150 THEN
5151 fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
5152 fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
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);
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
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,
5179 p_data => x_msg_data
5180 );
5181 WHEN OTHERS
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 );
5191 --
5192
5193 END validate_terrtype_usage;
5194
5195 ---------------------------------------------------------------------
5196 -- Validate the Territory Qualifer Type Usage
5197 ---------------------------------------------------------------------
5198 -- Columns Validated
5199 -- Make sure a Territory Qual Type Usage is specified
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
5209 := g_miss_typequaltypeusgs_rec,
5210 p_terr_type_id IN NUMBER
5211 )
5212 AS
5213 l_rec_counter NUMBER;
5214 l_validate_id NUMBER;
5215 l_dummy NUMBER;
5216 l_source_id NUMBER;
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
5226 l_qual_type_usg_id := p_type_qualtypeusgs_rec.qual_type_usg_id;
5227 --Check the qual_type_usg_id specified is valid
5228 SELECT 1
5229 INTO l_dummy
5230 FROM jtf_terr_type_usgs jtu, jtf_qual_type_usgs jqtu
5231 WHERE jtu.terr_type_id = p_terr_type_id
5232 AND jtu.source_id = jqtu.source_id
5233 AND jqtu.qual_type_usg_id = l_qual_type_usg_id;
5234 EXCEPTION
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;
5244
5245 IF p_terr_type_id IS NOT NULL
5246 THEN
5247 --dbms_output.put_line (
5248 -- 'Validate_Terr_Qtype_Usage: TERR_TYPE_ID(' ||
5249 -- TO_CHAR (l_validate_id) ||
5250 -- ')'
5251 --);
5252
5253 IF jtf_ctm_utility_pvt.fk_id_is_valid (
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 --);
5263
5264 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
5265 THEN
5266 fnd_message.set_name ('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
5267 fnd_message.set_token ('TABLE_NAME', 'JTF_TERR_TYPES');
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 --
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
5285 fnd_message.set_token ('COL_NAME', 'ORG_ID');
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');
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
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
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
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
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
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);
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 --);
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 );
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,
5385 p_data => x_msg_data
5386 );
5387 WHEN OTHERS
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 );
5397 --
5398
5399 END validate_type_qtype_usage;
5400
5401 --
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
5411 )
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
5421 WHERE territory_type_id = p_terrtype_id;
5422
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
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,
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 (
5450 p_count => x_msg_count,
5451 p_data => x_msg_data
5452 );
5453 --
5454
5455 END is_terrtype_deletable;
5456
5457 --
5458 -- Package body
5459 END JTF_TERRITORY_TYPE_PVT;
5460
5461