[Home] [Help]
PACKAGE BODY: APPS.JTF_TERRITORY_PUB
Source
1 PACKAGE BODY JTF_TERRITORY_PUB AS
2 /* $Header: jtfpterb.pls 120.8 2010/12/01 06:18:05 srguntur ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERRITORY_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force core territory manager public api's.
10 -- This package is a public API for inserting territory
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 -- Procedures:
16 -- (see below for specification)
17 --
18 -- NOTES
19 -- This package is publicly available for use
20 --
21 -- HISTORY
22 -- 06/09/99 VNEDUNGA Created
23 -- 07/14/99 JDOCHERT Updated existing public APIs and
24 -- added new public APIs
25 -- 12/22/99 VNEDUNGA Changing the procedure to include
26 -- changes coresponding to JTF_TERR-RSC
27 -- chnages
28 -- 03/22/00 VNEDUNGA Putting in FND_MESSAGE calls and
29 -- changes corresponding to FULL_ACCESS_FLAG
30 -- addition
31 -- 06/08/00 VNEDUNGA Changing convert pub to pvt resource record
32 -- 07/14/00 JDOCHERT BUG# 1355914 - FIX
33 -- 09/17/00 JDOCHERT BUG#1408610 FIX
34 -- 12/03/04 achanda added value4_id : bug # 3726007
35 --
36 -- End of Comments
37 --
38 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_TERRITORY_PUB';
39 g_file_name CONSTANT VARCHAR2(12) := 'jtfpterb.pls';
40
41 -- -------------------------------------------------
42 -- Package Name : Convert_TerrRec_PubToPvt
43 -- -------------------------------------------------
44 -- Purpose : This utility procedure will convert
45 -- jtf_terr_rec from pub to pvt before
46 -- calling the JTF_TERRITORY_PVT pkg.
47 -- Notes :
48 -- History :
49 -- 08/23/99 VNEDUNGA Created
50 -- -------------------------------------------------
51 PROCEDURE convert_terrrec_pubtopvt (
52 p_terr_rec IN jtf_territory_pub.terr_all_rec_type
53 := g_miss_terr_all_rec,
54 x_terr_rec OUT NOCOPY jtf_territory_pvt.terr_all_rec_type
55 )
56 AS
57 BEGIN
58 --dbms_output.put_line('Convert_TerrRec_PubToPvt: Entering API');
59 x_terr_rec.terr_id := p_terr_rec.terr_id;
60 x_terr_rec.last_update_date := p_terr_rec.last_update_date;
61 x_terr_rec.last_updated_by := p_terr_rec.last_updated_by;
62 x_terr_rec.creation_date := p_terr_rec.creation_date;
63 x_terr_rec.created_by := p_terr_rec.created_by;
64 x_terr_rec.last_update_login := p_terr_rec.last_update_login;
65 x_terr_rec.application_short_name := p_terr_rec.application_short_name;
66 x_terr_rec.name := p_terr_rec.name;
67 x_terr_rec.enabled_flag := p_terr_rec.enabled_flag;
68 x_terr_rec.request_id := p_terr_rec.request_id;
69 x_terr_rec.program_application_id := p_terr_rec.program_application_id;
70 x_terr_rec.program_id := p_terr_rec.program_id;
71 x_terr_rec.program_update_date := p_terr_rec.program_update_date;
72 x_terr_rec.start_date_active := p_terr_rec.start_date_active;
73 x_terr_rec.rank := p_terr_rec.rank;
74 x_terr_rec.end_date_active := p_terr_rec.end_date_active;
75 x_terr_rec.description := p_terr_rec.description;
76 x_terr_rec.update_flag := p_terr_rec.update_flag;
77 x_terr_rec.auto_assign_resources_flag :=
78 p_terr_rec.auto_assign_resources_flag;
79 x_terr_rec.planned_flag := p_terr_rec.planned_flag;
80 x_terr_rec.territory_type_id := p_terr_rec.territory_type_id;
81 x_terr_rec.parent_territory_id := p_terr_rec.parent_territory_id;
82 x_terr_rec.template_flag := p_terr_rec.template_flag;
83 x_terr_rec.template_territory_id := p_terr_rec.template_territory_id;
84 x_terr_rec.escalation_territory_flag :=
85 p_terr_rec.escalation_territory_flag;
86 x_terr_rec.escalation_territory_id :=
87 p_terr_rec.escalation_territory_id;
88 x_terr_rec.overlap_allowed_flag := p_terr_rec.overlap_allowed_flag;
89 x_terr_rec.attribute_category := p_terr_rec.attribute_category;
90 x_terr_rec.attribute1 := p_terr_rec.attribute1;
91 x_terr_rec.attribute2 := p_terr_rec.attribute2;
92 x_terr_rec.attribute3 := p_terr_rec.attribute3;
93 x_terr_rec.attribute4 := p_terr_rec.attribute4;
94 x_terr_rec.attribute5 := p_terr_rec.attribute5;
95 x_terr_rec.attribute6 := p_terr_rec.attribute6;
96 x_terr_rec.attribute7 := p_terr_rec.attribute7;
97 x_terr_rec.attribute8 := p_terr_rec.attribute8;
98 x_terr_rec.attribute9 := p_terr_rec.attribute9;
99 x_terr_rec.attribute10 := p_terr_rec.attribute10;
100 x_terr_rec.attribute11 := p_terr_rec.attribute11;
101 x_terr_rec.attribute12 := p_terr_rec.attribute12;
102 x_terr_rec.attribute13 := p_terr_rec.attribute13;
103 x_terr_rec.attribute14 := p_terr_rec.attribute14;
104 x_terr_rec.attribute15 := p_terr_rec.attribute15;
105 x_terr_rec.org_id := p_terr_rec.org_id;
106 x_terr_rec.num_winners := p_terr_rec.num_winners;
107 --
108
109 END convert_terrrec_pubtopvt;
110
111 -- --------------------------------------------------------
112 -- Package Name : Convert_TerrQualTbl_PubToPvt
113 -- --------------------------------------------------------
114 -- Purpose : This utility procedure will convert
115 -- JTF_TERR_QUAL_TBL from pub to pvt
116 -- before calling the JTF_TERRITORY_PVT
117 -- pkg.
118 -- Notes :
119 -- History :
120 -- 08/23/99 VNEDUNGA Created
121 -- ---------------------------------------------------------
122 PROCEDURE convert_terrqualtbl_pubtopvt (
123 p_terrqual_tbl IN jtf_territory_pub.terr_qual_tbl_type,
124 x_terrqual_tbl OUT NOCOPY jtf_territory_pvt.terr_qual_tbl_type
125 )
126 AS
127 l_counter NUMBER;
128 --
129 BEGIN
130 --dbms_output.put_line('Convert_TerrQualTbl_PubToPvt: Entering API');
131 IF p_terrqual_tbl.COUNT = 0
132 THEN
133 RETURN;
134 END IF;
135
136 FOR l_counter IN 1 .. p_terrqual_tbl.COUNT
137 LOOP
138 --
139 x_terrqual_tbl (l_counter).terr_qual_id :=
140 p_terrqual_tbl (l_counter).terr_qual_id;
141 x_terrqual_tbl (l_counter).last_update_date :=
142 p_terrqual_tbl (l_counter).last_update_date;
143 x_terrqual_tbl (l_counter).last_updated_by :=
144 p_terrqual_tbl (l_counter).last_updated_by;
145 x_terrqual_tbl (l_counter).creation_date :=
146 p_terrqual_tbl (l_counter).creation_date;
147 x_terrqual_tbl (l_counter).created_by :=
148 p_terrqual_tbl (l_counter).created_by;
149 x_terrqual_tbl (l_counter).last_update_login :=
150 p_terrqual_tbl (l_counter).last_update_login;
151 x_terrqual_tbl (l_counter).terr_id :=
152 p_terrqual_tbl (l_counter).terr_id;
153 x_terrqual_tbl (l_counter).qual_usg_id :=
154 p_terrqual_tbl (l_counter).qual_usg_id;
155 x_terrqual_tbl (l_counter).use_to_name_flag :=
156 p_terrqual_tbl (l_counter).use_to_name_flag;
157 x_terrqual_tbl (l_counter).generate_flag :=
158 p_terrqual_tbl (l_counter).generate_flag;
159 x_terrqual_tbl (l_counter).overlap_allowed_flag :=
160 p_terrqual_tbl (l_counter).overlap_allowed_flag;
161 x_terrqual_tbl (l_counter).qualifier_mode :=
162 p_terrqual_tbl (l_counter).qualifier_mode;
163 x_terrqual_tbl (l_counter).org_id :=
164 p_terrqual_tbl (l_counter).org_id;
165 --
166 END LOOP;
167 --
168 END convert_terrqualtbl_pubtopvt;
169
170 -- --------------------------------------------------------
171 -- Package Name : Convert_TerrQualrec_PubToPvt
172 -- --------------------------------------------------------
173 -- Purpose : This utility procedure will convert
174 -- JTF_TERR_QUAL_REC from pub to pvt
175 -- before calling the JTF_TERRITORY_PVT
176 -- pkg.
177 -- Notes :
178 -- History :
179 -- VPALLE Created
180 -- ---------------------------------------------------------
181 PROCEDURE convert_terrqualrec_pubtopvt (
182 p_terrqual_rec IN jtf_territory_pub.terr_qual_rec_type,
183 x_terrqual_rec OUT NOCOPY jtf_territory_pvt.terr_qual_rec_type
184 )
185 AS
186 l_counter NUMBER;
187 --
188 BEGIN
189 --
190 x_terrqual_rec.terr_qual_id := p_terrqual_rec .terr_qual_id;
191 x_terrqual_rec .last_update_date := p_terrqual_rec.last_update_date;
192 x_terrqual_rec.last_updated_by := p_terrqual_rec.last_updated_by;
193 x_terrqual_rec .creation_date := p_terrqual_rec .creation_date;
194 x_terrqual_rec .created_by := p_terrqual_rec.created_by;
195 x_terrqual_rec.last_update_login := p_terrqual_rec.last_update_login;
196 x_terrqual_rec.terr_id := p_terrqual_rec.terr_id;
197 x_terrqual_rec.qual_usg_id := p_terrqual_rec.qual_usg_id;
198 x_terrqual_rec.use_to_name_flag := p_terrqual_rec.use_to_name_flag;
199 x_terrqual_rec.generate_flag := p_terrqual_rec.generate_flag;
200 x_terrqual_rec.overlap_allowed_flag := p_terrqual_rec.overlap_allowed_flag;
201 x_terrqual_rec.qualifier_mode := p_terrqual_rec.qualifier_mode;
202 x_terrqual_rec.org_id := p_terrqual_rec.org_id;
203 --
204 END convert_terrqualrec_pubtopvt;
205
206 -- -----------------------------------------------------
207 -- Package Name : Convert_TerrValueTbl_PubToPvt
208 -- -----------------------------------------------------
209 -- Purpose : This utility procedure will convert
210 -- JTF_TERR_VALUES_TBL from pub to pvt
211 -- before calling the JTF_TERRITORY_PVT
212 -- pkg.
213 -- Notes :
214 -- History :
215 -- 08/23/99 VNEDUNGA Created
216 -- -----------------------------------------------------
217 PROCEDURE convert_terrvaluetbl_pubtopvt (
218 p_terrvalues_tbl IN jtf_territory_pub.terr_values_tbl_type,
219 x_terrvalues_tbl OUT NOCOPY jtf_territory_pvt.terr_values_tbl_type
220 )
221 AS
222 --
223 l_counter NUMBER;
224 --
225 BEGIN
226 --dbms_output.put_line('Convert_TerrValueTbl_PubToPvt: Entering API' || to_char(p_TerrValues_Tbl.Count));
227 IF p_terrvalues_tbl.COUNT = 0
228 THEN
229 RETURN;
230 END IF;
231
232 FOR l_counter IN 1 .. p_terrvalues_tbl.COUNT
233 LOOP
234 --
235 --dbms_output.put_line('Counter - ' || to_char(l_Counter));
236 x_terrvalues_tbl (l_counter).terr_value_id :=
237 p_terrvalues_tbl (l_counter).terr_value_id;
238 x_terrvalues_tbl (l_counter).last_update_date :=
239 p_terrvalues_tbl (l_counter).last_update_date;
240 x_terrvalues_tbl (l_counter).last_updated_by :=
241 p_terrvalues_tbl (l_counter).last_updated_by;
242 x_terrvalues_tbl (l_counter).creation_date :=
243 p_terrvalues_tbl (l_counter).creation_date;
244 x_terrvalues_tbl (l_counter).created_by :=
245 p_terrvalues_tbl (l_counter).created_by;
246 x_terrvalues_tbl (l_counter).last_update_login :=
247 p_terrvalues_tbl (l_counter).last_update_login;
248 x_terrvalues_tbl (l_counter).terr_qual_id :=
249 p_terrvalues_tbl (l_counter).terr_qual_id;
250 x_terrvalues_tbl (l_counter).include_flag :=
251 p_terrvalues_tbl (l_counter).include_flag;
252 x_terrvalues_tbl (l_counter).comparison_operator :=
253 p_terrvalues_tbl (l_counter).comparison_operator;
254 x_terrvalues_tbl (l_counter).low_value_char :=
255 p_terrvalues_tbl (l_counter).low_value_char;
256 x_terrvalues_tbl (l_counter).high_value_char :=
257 p_terrvalues_tbl (l_counter).high_value_char;
258 x_terrvalues_tbl (l_counter).low_value_number :=
259 p_terrvalues_tbl (l_counter).low_value_number;
260 x_terrvalues_tbl (l_counter).high_value_number :=
261 p_terrvalues_tbl (l_counter).high_value_number;
262 x_terrvalues_tbl (l_counter).value_set :=
263 p_terrvalues_tbl (l_counter).value_set;
264 x_terrvalues_tbl (l_counter).interest_type_id :=
265 p_terrvalues_tbl (l_counter).interest_type_id;
266 x_terrvalues_tbl (l_counter).primary_interest_code_id :=
267 p_terrvalues_tbl (l_counter).primary_interest_code_id;
268 x_terrvalues_tbl (l_counter).secondary_interest_code_id :=
269 p_terrvalues_tbl (l_counter).secondary_interest_code_id;
270 x_terrvalues_tbl (l_counter).currency_code :=
271 p_terrvalues_tbl (l_counter).currency_code;
272 x_terrvalues_tbl (l_counter).id_used_flag :=
273 p_terrvalues_tbl (l_counter).id_used_flag;
274 x_terrvalues_tbl (l_counter).low_value_char_id :=
275 p_terrvalues_tbl (l_counter).low_value_char_id;
276 x_terrvalues_tbl (l_counter).qualifier_tbl_index :=
277 p_terrvalues_tbl (l_counter).qualifier_tbl_index;
278 x_terrvalues_tbl (l_counter).org_id :=
279 p_terrvalues_tbl (l_counter).org_id;
280
281 x_terrvalues_tbl (l_counter).cnr_group_id :=
282 p_terrvalues_tbl (l_counter).cnr_group_id;
283
284 x_terrvalues_tbl (l_counter).value1_id :=
285 p_terrvalues_tbl (l_counter).value1_id;
286
287 x_terrvalues_tbl (l_counter).value2_id :=
288 p_terrvalues_tbl (l_counter).value2_id;
289
290 x_terrvalues_tbl (l_counter).value3_id :=
291 p_terrvalues_tbl (l_counter).value3_id;
292
293 x_terrvalues_tbl (l_counter).value4_id :=
294 p_terrvalues_tbl (l_counter).value4_id;
295
296 --
297 END LOOP;
298 --dbms_output.put_line('Convert_TerrValueTbl_PubToPvt: Exiting API');
299 --
300 END convert_terrvaluetbl_pubtopvt;
301
302 -- -----------------------------------------------------
303 -- Package Name : Convert_TerrUsgsTbl_PubToPvt
304 -- -----------------------------------------------------
305 -- Purpose : This utility procedure will convert
306 -- JTF_TERR_USGS_TBL from pub to pvt
307 -- before calling the JTF_TERRITORY_PVT
308 -- pkg.
309 -- Notes :
310 -- History :
311 -- 08/23/99 VNEDUNGA Created
312 -- -----------------------------------------------------
313 PROCEDURE convert_terrusgstbl_pubtopvt (
314 p_terrusgs_tbl IN jtf_territory_pub.terr_usgs_tbl_type,
315 x_terrusgs_tbl OUT NOCOPY jtf_territory_pvt.terr_usgs_tbl_type
316 )
317 AS
318 l_counter NUMBER;
319 BEGIN
320 --dbms_output.put_line('Convert_TerrUsgsTbl_PubToPvt: Entering API');
321 IF p_terrusgs_tbl.COUNT = 0
322 THEN
323 RETURN;
324 END IF;
325
326 FOR l_counter IN 1 .. p_terrusgs_tbl.COUNT
327 LOOP
328 --
329 x_terrusgs_tbl (l_counter).terr_usg_id :=
330 p_terrusgs_tbl (l_counter).terr_usg_id;
331 x_terrusgs_tbl (l_counter).source_id :=
332 p_terrusgs_tbl (l_counter).source_id;
333 x_terrusgs_tbl (l_counter).terr_id :=
334 p_terrusgs_tbl (l_counter).terr_id;
335 x_terrusgs_tbl (l_counter).last_update_date :=
336 p_terrusgs_tbl (l_counter).last_update_date;
337 x_terrusgs_tbl (l_counter).last_updated_by :=
338 p_terrusgs_tbl (l_counter).last_updated_by;
339 x_terrusgs_tbl (l_counter).creation_date :=
340 p_terrusgs_tbl (l_counter).creation_date;
341 x_terrusgs_tbl (l_counter).created_by :=
342 p_terrusgs_tbl (l_counter).created_by;
343 x_terrusgs_tbl (l_counter).last_update_login :=
344 p_terrusgs_tbl (l_counter).last_update_login;
345 x_terrusgs_tbl (l_counter).org_id :=
346 p_terrusgs_tbl (l_counter).org_id;
347 --
348 END LOOP;
349 END convert_terrusgstbl_pubtopvt;
350
351 -- -----------------------------------------------------
352 -- Package Name : Convert_TerQTypUsgTbl_PubToPvt
353 -- -----------------------------------------------------
354 -- Purpose : This utility procedure will convert
355 -- JTF_TERR_QUAL_TYPE_USGS_TBL from pub
356 -- to pvt before calling the JTF_TERRITORY_PVT
357 -- pkg.
358 -- Notes :
359 -- History :
360 -- 08/23/99 VNEDUNGA Created
361 -- -----------------------------------------------------
362 PROCEDURE convert_terqtypusgtbl_pubtopvt (
363 p_terrqtypeusgs_tbl IN jtf_territory_pub.terr_qualtypeusgs_tbl_type,
364 x_terrqtypeusgs_tbl OUT NOCOPY jtf_territory_pvt.terr_qualtypeusgs_tbl_type
365 )
366 AS
367 l_counter NUMBER;
368 BEGIN
369 --dbms_output.put_line('Convert_TerQTypUsgTbl_PubToPvt: Entering API');
370 IF p_terrqtypeusgs_tbl.COUNT = 0
371 THEN
372 RETURN;
373 END IF;
374
375 --
376 FOR l_counter IN 1 .. p_terrqtypeusgs_tbl.COUNT
377 LOOP
378 --
379 x_terrqtypeusgs_tbl (l_counter).terr_qual_type_usg_id :=
380 p_terrqtypeusgs_tbl (l_counter).terr_qual_type_usg_id;
381 x_terrqtypeusgs_tbl (l_counter).terr_id :=
382 p_terrqtypeusgs_tbl (l_counter).terr_id;
383 x_terrqtypeusgs_tbl (l_counter).qual_type_usg_id :=
384 p_terrqtypeusgs_tbl (l_counter).qual_type_usg_id;
385 x_terrqtypeusgs_tbl (l_counter).last_update_date :=
386 p_terrqtypeusgs_tbl (l_counter).last_update_date;
387 x_terrqtypeusgs_tbl (l_counter).last_updated_by :=
388 p_terrqtypeusgs_tbl (l_counter).last_updated_by;
389 x_terrqtypeusgs_tbl (l_counter).creation_date :=
390 p_terrqtypeusgs_tbl (l_counter).creation_date;
391 x_terrqtypeusgs_tbl (l_counter).created_by :=
392 p_terrqtypeusgs_tbl (l_counter).created_by;
393 x_terrqtypeusgs_tbl (l_counter).last_update_login :=
394 p_terrqtypeusgs_tbl (l_counter).last_update_login;
395 x_terrqtypeusgs_tbl (l_counter).org_id :=
396 p_terrqtypeusgs_tbl (l_counter).org_id;
397 --
398 END LOOP;
399 --
400 END convert_terqtypusgtbl_pubtopvt;
401
402 --
403 -- -------------------------------------------------
404 -- Package Name : Convert_TerrOut_PvtToPub
405 -- -------------------------------------------------
406 -- Purpose : This utility procedure will convert
407 -- terr_out_rec from pvt to pub
408 -- after calling JTF_TERRITORY_TYPE_PVT pkg.
409 -- Notes :
410 -- History :
411 -- 08/23/99 VNEDUNGA Created
412 -- -------------------------------------------------
413 PROCEDURE convert_terrout_pvttopub (
414 p_terr_rec IN jtf_territory_pvt.terr_all_out_rec_type,
415 x_terr_rec OUT NOCOPY jtf_territory_pub.terr_all_out_rec_type
416 )
417 AS
418 BEGIN
419 --
420 x_terr_rec.terr_id := p_terr_rec.terr_id;
421 x_terr_rec.return_status := p_terr_rec.return_status;
422 --
423 END convert_terrout_pvttopub;
424
425 --
426 -- -------------------------------------------------
427 -- Package Name : Convert_TerrUsgOut_PvtToPub
428 -- -------------------------------------------------
429 -- Purpose : This utility procedure will convert
430 -- Terr_Usgs_out_Tbl from pvt to pub
431 -- after calling JTF_TERRITORY_PVT pkg.
432 -- Notes :
433 -- History :
434 -- 08/23/99 VNEDUNGA Created
435 -- -------------------------------------------------
436 PROCEDURE convert_terrusgout_pvttopub (
437 p_terrusgsout_tbl IN jtf_territory_pvt.terr_usgs_out_tbl_type,
438 x_terrusgsout_tbl OUT NOCOPY jtf_territory_pub.terr_usgs_out_tbl_type
439 )
440 AS
441 l_counter NUMBER;
442 BEGIN
443 IF p_terrusgsout_tbl.COUNT = 0
444 THEN
445 RETURN;
446 END IF;
447
448 --
449 FOR l_counter IN 1 .. p_terrusgsout_tbl.COUNT
450 LOOP
451 x_terrusgsout_tbl (l_counter).terr_usg_id :=
452 p_terrusgsout_tbl (l_counter).terr_usg_id;
453 x_terrusgsout_tbl (l_counter).return_status :=
454 p_terrusgsout_tbl (l_counter).return_status;
455 END LOOP;
456 --
457
458 END convert_terrusgout_pvttopub;
459
460 -- -------------------------------------------------
461 -- Package Name : Convert_TerrQTUsgOut_PvtToPub
462 -- -------------------------------------------------
463 -- Purpose : This utility procedure will convert
464 -- Terr_QualTypeUsgs_Out_Tbl from pvt
465 -- to pub after calling JTF_TERRITORY_PVT
466 -- pkg.
467 -- Notes :
468 -- History :
469 -- 08/23/99 VNEDUNGA Created
470 -- -------------------------------------------------
471 PROCEDURE convert_terrqtusgout_pvttopub (
472 p_terrqualtypeusgs_tbl IN jtf_territory_pvt.terr_qualtypeusgs_out_tbl_type,
473 x_terrqualtypeusgs_tbl OUT NOCOPY jtf_territory_pub.terr_qualtypeusgs_out_tbl_type
474 )
475 AS
476 l_counter NUMBER;
477 BEGIN
478 IF p_terrqualtypeusgs_tbl.COUNT = 0
479 THEN
480 RETURN;
481 END IF;
482
483 --
484 FOR l_counter IN 1 .. p_terrqualtypeusgs_tbl.COUNT
485 LOOP
486 --
487 x_terrqualtypeusgs_tbl (l_counter).terr_qual_type_usg_id :=
488 p_terrqualtypeusgs_tbl (l_counter).terr_qual_type_usg_id;
489 x_terrqualtypeusgs_tbl (l_counter).return_status :=
490 p_terrqualtypeusgs_tbl (l_counter).return_status;
491 --
492 END LOOP;
493 --
494 END convert_terrqtusgout_pvttopub;
495
496 -- -------------------------------------------------
497 -- Purpose : This utility procedure will convert
498 -- Terr_Qual_Out_Tbl from pvt
499 -- to pub after calling JTF_TERRITORY_PVT
500 -- pkg.
501 -- Notes :
502 -- History :
503 -- 08/23/99 VNEDUNGA Created
504 -- -------------------------------------------------
505 PROCEDURE convert_terrqualout_pvttopub (
506 p_terrqual_tbl IN jtf_territory_pvt.terr_qual_out_tbl_type,
507 x_terrqual_tbl OUT NOCOPY jtf_territory_pub.terr_qual_out_tbl_type
508 )
509 AS
510 l_counter NUMBER;
511 BEGIN
512 --
513 IF p_terrqual_tbl.COUNT = 0
514 THEN
515 RETURN;
516 END IF;
517
518 --
519 FOR l_counter IN 1 .. p_terrqual_tbl.COUNT
520 LOOP
521 --
522 x_terrqual_tbl (l_counter).terr_qual_id :=
523 p_terrqual_tbl (l_counter).terr_qual_id;
524 x_terrqual_tbl (l_counter).return_status :=
525 p_terrqual_tbl (l_counter).return_status;
526 --
527 END LOOP;
528 --
529 END convert_terrqualout_pvttopub;
530
531 -- -------------------------------------------------
532 -- Purpose : This utility procedure will convert
533 -- Terr_Qual_Out_Tbl from pvt
534 -- to pub after calling JTF_TERRITORY_PVT
535 -- pkg.
536 -- Notes :
537 -- History :
538 -- 08/23/99 VNEDUNGA Created
539 -- -------------------------------------------------
540 PROCEDURE convert_terrqualout_pvttopub (
541 p_terrqual_rec IN jtf_territory_pvt.terr_qual_out_rec_type,
542 x_terrqual_rec OUT NOCOPY jtf_territory_pub.terr_qual_out_rec_type
543 )
544 AS
545 l_counter NUMBER;
546 BEGIN
547
548 x_terrqual_rec.terr_qual_id := p_terrqual_rec.terr_qual_id;
549 x_terrqual_rec.return_status := p_terrqual_rec.return_status;
550
551 END convert_terrqualout_pvttopub;
552
553 -- -------------------------------------------------
554 -- Package Name : Convert_TerrValuesOut_PvtToPub
555 -- -------------------------------------------------
556 -- Purpose : This utility procedure will convert
557 -- Terr_Values_Out_Tbl from pvt
558 -- to pub after calling JTF_TERRITORY_PVT
559 -- pkg.
560 -- Notes :
561 -- History :
562 -- 08/23/99 VNEDUNGA Created
563 -- -------------------------------------------------
564 PROCEDURE convert_terrvaluesout_pvttopub (
565 p_terrvaluesout_tbl IN jtf_territory_pvt.terr_values_out_tbl_type,
566 x_terrvaluesout_tbl OUT NOCOPY jtf_territory_pub.terr_values_out_tbl_type
567 )
568 AS
569 l_counter NUMBER;
570 BEGIN
571 --
572 IF p_terrvaluesout_tbl.COUNT = 0
573 THEN
574 RETURN;
575 END IF;
576
577 --
578 FOR l_counter IN 1 .. p_terrvaluesout_tbl.COUNT
579 LOOP
580 --
581 x_terrvaluesout_tbl (l_counter).terr_value_id :=
582 p_terrvaluesout_tbl (l_counter).terr_value_id;
583 x_terrvaluesout_tbl (l_counter).return_status :=
584 p_terrvaluesout_tbl (l_counter).return_status;
585 --
586
587 END LOOP;
588 --
589 END convert_terrvaluesout_pvttopub;
590
591 -- -------------------------------------------------
592 -- Package Name : Convert_TerRsc_PubToPvt
593 -- -------------------------------------------------
594 -- Purpose : This utility procedure will convert
595 -- jtf_terrResource_Tbl from pub to pvt
596 -- before calling the JTF_TERRITORY_RESOURCE_PVT
597 -- pkg.
598 -- Notes :
599 -- History :
600 -- 08/23/99 VNEDUNGA Created
601 -- -------------------------------------------------
602 PROCEDURE convert_terrrsc_pubtopvt (
603 p_terrrsc_tbl IN jtf_territory_pub.terrresource_tbl_type,
604 x_terrrsc_tbl OUT NOCOPY jtf_territory_resource_pvt.terrresource_tbl_type
605 )
606 AS
607 l_counter NUMBER;
608 BEGIN
609 -- If the table is empty
610 IF p_terrrsc_tbl.COUNT = 0
611 THEN
612 RETURN;
613 END IF;
614
615 --
616 FOR l_counter IN 1 .. p_terrrsc_tbl.COUNT
617 LOOP
618 --
619 x_terrrsc_tbl (l_counter).terr_rsc_id :=
620 p_terrrsc_tbl (l_counter).terr_rsc_id;
621 x_terrrsc_tbl (l_counter).last_update_date :=
622 p_terrrsc_tbl (l_counter).last_update_date;
623 x_terrrsc_tbl (l_counter).last_updated_by :=
624 p_terrrsc_tbl (l_counter).last_updated_by;
625 x_terrrsc_tbl (l_counter).creation_date :=
626 p_terrrsc_tbl (l_counter).creation_date;
627 x_terrrsc_tbl (l_counter).created_by :=
628 p_terrrsc_tbl (l_counter).created_by;
629 x_terrrsc_tbl (l_counter).last_update_login :=
630 p_terrrsc_tbl (l_counter).last_update_login;
631 x_terrrsc_tbl (l_counter).terr_id :=
632 p_terrrsc_tbl (l_counter).terr_id;
633 x_terrrsc_tbl (l_counter).resource_id :=
634 p_terrrsc_tbl (l_counter).resource_id;
635 x_terrrsc_tbl (l_counter).group_id :=
636 p_terrrsc_tbl (l_counter).group_id;
637 x_terrrsc_tbl (l_counter).resource_type :=
638 p_terrrsc_tbl (l_counter).resource_type;
639 x_terrrsc_tbl (l_counter).role := p_terrrsc_tbl (l_counter).role;
640 x_terrrsc_tbl (l_counter).primary_contact_flag :=
641 p_terrrsc_tbl (l_counter).primary_contact_flag;
642
643 /* BUG# 1355914 - FIX START*/
644 x_terrrsc_tbl (l_counter).start_date_active :=
645 p_terrrsc_tbl (l_counter).start_date_active;
646 x_terrrsc_tbl (l_counter).end_date_active :=
647 p_terrrsc_tbl (l_counter).end_date_active;
648 /* BUG# 1355914 - FIX END*/
649
650 x_terrrsc_tbl (l_counter).full_access_flag :=
651 p_terrrsc_tbl (l_counter).full_access_flag;
652 x_terrrsc_tbl (l_counter).org_id := p_terrrsc_tbl (l_counter).org_id;
653 -- Adding the attribute columns as fix for bug 7168485.
654 x_terrrsc_tbl (l_counter).ATTRIBUTE_CATEGORY := p_terrrsc_tbl (l_counter).ATTRIBUTE_CATEGORY;
655 x_terrrsc_tbl (l_counter).ATTRIBUTE1 := p_terrrsc_tbl (l_counter).ATTRIBUTE1;
656 x_terrrsc_tbl (l_counter).ATTRIBUTE2 := p_terrrsc_tbl (l_counter).ATTRIBUTE2;
657 x_terrrsc_tbl (l_counter).ATTRIBUTE3 := p_terrrsc_tbl (l_counter).ATTRIBUTE3;
658 x_terrrsc_tbl (l_counter).ATTRIBUTE4 := p_terrrsc_tbl (l_counter).ATTRIBUTE4;
659 x_terrrsc_tbl (l_counter).ATTRIBUTE5 := p_terrrsc_tbl (l_counter).ATTRIBUTE5;
660 x_terrrsc_tbl (l_counter).ATTRIBUTE6 := p_terrrsc_tbl (l_counter).ATTRIBUTE6;
661 x_terrrsc_tbl (l_counter).ATTRIBUTE7 := p_terrrsc_tbl (l_counter).ATTRIBUTE7;
662 x_terrrsc_tbl (l_counter).ATTRIBUTE8 := p_terrrsc_tbl (l_counter).ATTRIBUTE8;
663 x_terrrsc_tbl (l_counter).ATTRIBUTE9 := p_terrrsc_tbl (l_counter).ATTRIBUTE9;
664 x_terrrsc_tbl (l_counter).ATTRIBUTE10 := p_terrrsc_tbl (l_counter).ATTRIBUTE10;
665 x_terrrsc_tbl (l_counter).ATTRIBUTE11 := p_terrrsc_tbl (l_counter).ATTRIBUTE11;
666 x_terrrsc_tbl (l_counter).ATTRIBUTE12 := p_terrrsc_tbl (l_counter).ATTRIBUTE12;
667 x_terrrsc_tbl (l_counter).ATTRIBUTE13 := p_terrrsc_tbl (l_counter).ATTRIBUTE13;
668 x_terrrsc_tbl (l_counter).ATTRIBUTE14 := p_terrrsc_tbl (l_counter).ATTRIBUTE14;
669 x_terrrsc_tbl (l_counter).ATTRIBUTE15 := p_terrrsc_tbl (l_counter).ATTRIBUTE15;
670 END LOOP;
671 --
672 END convert_terrrsc_pubtopvt;
673
674 -- -------------------------------------------------
675 -- Package Name : Convert_TerrRscAcc_PubToPvt
676 -- -------------------------------------------------
677 -- Purpose : This utility procedure will convert
678 -- TerrRsc_Access_tbl from pub to pvt before
679 -- calling the JTF_TERRITORY_RESOURCE_PVT pkg.
680 -- Notes :
681 -- History :
682 -- 08/23/99 VNEDUNGA Created
683 -- -------------------------------------------------
684 PROCEDURE convert_terrrscacc_pubtopvt (
685 p_terrrscacc_tbl IN jtf_territory_pub.terrrsc_access_tbl_type,
686 x_terrrscacc_tbl OUT NOCOPY jtf_territory_resource_pvt.terrrsc_access_tbl_type
687 )
688 AS
689 l_counter NUMBER;
690 BEGIN
691 -- If the table is empty
692 IF p_terrrscacc_tbl.COUNT = 0
693 THEN
694 RETURN;
695 END IF;
696
697 --
698 FOR l_counter IN 1 .. p_terrrscacc_tbl.COUNT
699 LOOP
700 --
701 x_terrrscacc_tbl (l_counter).terr_rsc_access_id :=
702 p_terrrscacc_tbl (l_counter).terr_rsc_access_id;
703 x_terrrscacc_tbl (l_counter).last_update_date :=
704 p_terrrscacc_tbl (l_counter).last_update_date;
705 x_terrrscacc_tbl (l_counter).last_updated_by :=
706 p_terrrscacc_tbl (l_counter).last_updated_by;
707 x_terrrscacc_tbl (l_counter).creation_date :=
708 p_terrrscacc_tbl (l_counter).creation_date;
709 x_terrrscacc_tbl (l_counter).created_by :=
710 p_terrrscacc_tbl (l_counter).created_by;
711 x_terrrscacc_tbl (l_counter).last_update_login :=
712 p_terrrscacc_tbl (l_counter).last_update_login;
713 x_terrrscacc_tbl (l_counter).terr_rsc_id :=
714 p_terrrscacc_tbl (l_counter).terr_rsc_id;
715 x_terrrscacc_tbl (l_counter).access_type :=
716 p_terrrscacc_tbl (l_counter).access_type;
717 x_terrrscacc_tbl (l_counter).org_id :=
718 p_terrrscacc_tbl (l_counter).org_id;
719 x_terrrscacc_tbl (l_counter).qualifier_tbl_index :=
720 p_terrrscacc_tbl (l_counter).qualifier_tbl_index;
721 x_terrrscacc_tbl (l_counter).TRANS_ACCESS_CODE :=
722 p_terrrscacc_tbl (l_counter).TRANS_ACCESS_CODE;
723 --
724
725 END LOOP;
726 --
727
728 END convert_terrrscacc_pubtopvt;
729
730 -- -------------------------------------------------
731 -- Package Name : Convert_TerrRscOut_PvtToPub
732 -- -------------------------------------------------
733 -- Purpose : This utility procedure will convert
734 -- jtf_terrResource_Out_Tbl from pvt to pub
735 -- after calling JTF_TERRITORY_RESOURCE_PVT
736 -- pkg.
737 -- Notes :
738 -- History :
739 -- 08/23/99 VNEDUNGA Created
740 -- -------------------------------------------------
741 PROCEDURE convert_terrrscout_pvttopub (
742 p_terrrscout_tbl IN jtf_territory_resource_pvt.terrresource_out_tbl_type,
743 x_terrrscout_tbl OUT NOCOPY jtf_territory_pub.terrresource_out_tbl_type
744 )
745 AS
746 l_counter NUMBER;
747 BEGIN
748 -- If the table is empty
749 IF p_terrrscout_tbl.COUNT = 0
750 THEN
751 RETURN;
752 END IF;
753
754 --
755 FOR l_counter IN 1 .. p_terrrscout_tbl.COUNT
756 LOOP
757 --
758 x_terrrscout_tbl (l_counter).terr_rsc_id :=
759 p_terrrscout_tbl (l_counter).terr_rsc_id;
760 x_terrrscout_tbl (l_counter).return_status :=
761 p_terrrscout_tbl (l_counter).return_status;
762 --
763
764 END LOOP;
765 --
766
767 END convert_terrrscout_pvttopub;
768
769 --
770 -- -------------------------------------------------
771 -- Package Name : Convert_TerRscAccOut_PvtToPub
772 -- -------------------------------------------------
773 -- Purpose : This utility procedure will convert
774 -- TerrRsc_Access_Out_tbl from pub to pvt after
775 -- calling the JTF_TERRITORY_RESOURCE_PVT pkg.
776 -- Notes :
777 -- History :
778 -- 08/23/99 VNEDUNGA Created
779 -- -------------------------------------------------
780 PROCEDURE convert_terrscaccout_pvttopub (
781 p_terrrscaccout_tbl IN jtf_territory_resource_pvt.terrrsc_access_out_tbl_type,
782 x_terrrscaccout_tbl OUT NOCOPY jtf_territory_pub.terrrsc_access_out_tbl_type
783 )
784 AS
785 l_counter NUMBER;
786 BEGIN
787 -- If the table is empty
788 IF p_terrrscaccout_tbl.COUNT = 0
789 THEN
790 RETURN;
791 END IF;
792
793 --
794 FOR l_counter IN 1 .. p_terrrscaccout_tbl.COUNT
795 LOOP
796 --
797 x_terrrscaccout_tbl (l_counter).terr_rsc_access_id :=
798 p_terrrscaccout_tbl (l_counter).terr_rsc_access_id;
799 x_terrrscaccout_tbl (l_counter).return_status :=
800 p_terrrscaccout_tbl (l_counter).return_status;
801 --
802 END LOOP;
803 --
804 END convert_terrscaccout_pvttopub;
805
806 -- ***************************************************
807 -- start of comments
808 -- ***************************************************
809 -- API name : Create_Territory
810 -- Type : PUBLIC
811 -- Function : To create Territories - which inludes the creation of following
812 -- Territory Header, Territory Qualifier, terr Usages,
813 -- Territory Qualifiers and Territory Qualifier Values.
814 -- P_terr_values_tbl.QUALIFIER_TBL_INDEX is, associates the values with the Qualifier,
815 -- the index of qualifier record of the qualifier table. Atleast one qualifier value must be passed, otherwise,
816 -- Qualifiers won't be created.
817 -- Pre-reqs :
818 -- Parameters:
819 -- IN :
820 -- Required
821 -- Parameter Name Data Type Default
822 -- p_Api_Version_Number NUMBER
823 -- p_Terr_All_Rec Terr_All_Rec_Type := G_Miss_Terr_All_Rec
824 -- p_Terr_Usgs_Tbl Terr_Usgs_Tbl_Type := G_MISS_Terr_Usgs_Tbl
825 -- p_Terr_Qual_Tbl Terr_Qual_Tbl_Type := G_Miss_Terr_Qual_Tbl
826 -- p_Terr_Values_Tbl Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl
827 --
828 -- Optional
829 -- Parameter Name Data Type Default
830 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
831 -- p_Commit VARCHAR2 := FND_API.G_FALSE
832 --
833 -- OUT NOCOPY :
834 -- Parameter Name Data Type
835 -- x_Return_Status VARCHAR2(1)
836 -- x_Msg_Count NUMBER
837 -- x_Msg_Data VARCHAR2(2000)
838 -- x_Terr_Id NUMBER
839 -- x_Terr_Usgs_Out_Tbl Terr_Usgs_Out_Tbl,
840 -- x_Terr_QualTypeUsgs_Out_Tbl Terr_QualTypeUsgs_Out_Tbl,
841 -- x_Terr_Qual_Out_Tbl Terr_Qual_Out_Tbl,
842 -- x_Terr_Values_Out_Tbl Terr_Values_Out_Tbl
843 --
844 --
845 -- Notes:
846 --
847 --
848 -- End of Comments
849 --
850 PROCEDURE create_territory (
851 p_api_version_number IN NUMBER,
852 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
853 p_commit IN VARCHAR2 := fnd_api.g_false,
854 x_return_status OUT NOCOPY VARCHAR2,
855 x_msg_count OUT NOCOPY NUMBER,
856 x_msg_data OUT NOCOPY VARCHAR2,
857 p_terr_all_rec IN terr_all_rec_type
858 := g_miss_terr_all_rec,
859 p_terr_usgs_tbl IN terr_usgs_tbl_type
860 := g_miss_terr_usgs_tbl,
861 -- Qual Type usages created created based on Territory Type.
862 --p_terr_qualtypeusgs_tbl IN terr_qualtypeusgs_tbl_type
863 -- := g_miss_terr_qualtypeusgs_tbl,
864 p_terr_qual_tbl IN terr_qual_tbl_type
865 := g_miss_terr_qual_tbl,
866 p_terr_values_tbl IN terr_values_tbl_type
867 := g_miss_terr_values_tbl,
868 x_terr_id OUT NOCOPY NUMBER,
869 x_terr_usgs_out_tbl OUT NOCOPY terr_usgs_out_tbl_type,
870 x_terr_qualtypeusgs_out_tbl OUT NOCOPY terr_qualtypeusgs_out_tbl_type,
871 x_terr_qual_out_tbl OUT NOCOPY terr_qual_out_tbl_type,
872 x_terr_values_out_tbl OUT NOCOPY terr_values_out_tbl_type
873 )
874 IS
875 l_api_name CONSTANT VARCHAR2(30) := 'Create_Territory';
876 l_api_version_number CONSTANT NUMBER := 1.0;
877 l_return_status VARCHAR2(1);
878 l_terr_usgs_out_tbl jtf_territory_pvt.terr_usgs_out_tbl_type;
879 l_terr_qualtypeusgs_out_tbl jtf_territory_pvt.terr_qualtypeusgs_out_tbl_type;
880 l_terr_qual_out_tbl jtf_territory_pvt.terr_qual_out_tbl_type;
881 l_terr_values_out_tbl jtf_territory_pvt.terr_values_out_tbl_type;
882 l_terr_rec jtf_territory_pvt.terr_all_rec_type;
883 l_terr_usgs_tbl jtf_territory_pvt.terr_usgs_tbl_type;
884 l_terr_qualtypeusgs_tbl jtf_territory_pvt.terr_qualtypeusgs_tbl_type;
885 l_terr_qual_tbl jtf_territory_pvt.terr_qual_tbl_type;
886 l_terr_values_tbl jtf_territory_pvt.terr_values_tbl_type;
887 --
888
889 BEGIN
890 -- Standard Start of API savepoint
891 SAVEPOINT create_territory_pub;
892
893 -- Standard call to check for call compatibility.
894 IF NOT fnd_api.compatible_api_call (
895 l_api_version_number,
896 p_api_version_number,
897 l_api_name,
898 g_pkg_name
899 )
900 THEN
901 RAISE fnd_api.g_exc_unexpected_error;
902 END IF;
903
904 -- Initialize message list if p_init_msg_list is set to TRUE.
905 IF fnd_api.to_boolean (p_init_msg_list)
906 THEN
907 fnd_msg_pub.initialize;
908 END IF;
909
910 -- Debug Message
911 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
912 THEN
913 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
914 fnd_message.set_name ('PROC_NAME', l_api_name);
915 fnd_msg_pub.add;
916 END IF;
917
918 -- Initialize API return status to success
919 x_return_status := fnd_api.g_ret_sts_success;
920 --
921 -- API body
922 --
923 convert_terrrec_pubtopvt (
924 p_terr_rec => p_terr_all_rec,
925 x_terr_rec => l_terr_rec
926 );
927 convert_terrqualtbl_pubtopvt (
928 p_terrqual_tbl => p_terr_qual_tbl,
929 x_terrqual_tbl => l_terr_qual_tbl
930 );
931 convert_terrvaluetbl_pubtopvt (
932 p_terrvalues_tbl => p_terr_values_tbl,
933 x_terrvalues_tbl => l_terr_values_tbl
934 );
935 convert_terrusgstbl_pubtopvt (
936 p_terrusgs_tbl => p_terr_usgs_tbl,
937 x_terrusgs_tbl => l_terr_usgs_tbl
938 );
939 /* convert_terqtypusgtbl_pubtopvt (
940 p_terrqtypeusgs_tbl => p_terr_qualtypeusgs_tbl,
941 x_terrqtypeusgs_tbl => l_terr_qualtypeusgs_tbl
942 ); */
943 -- ******************************************************************
944 jtf_territory_pvt.create_territory (
945 p_api_version_number => 1.0,
946 p_init_msg_list => fnd_api.g_false,
947 p_commit => fnd_api.g_false,
948 p_validation_level => fnd_api.g_valid_level_full,
949 x_return_status => l_return_status,
950 x_msg_count => x_msg_count,
951 x_msg_data => x_msg_data,
952 p_terr_all_rec => l_terr_rec,
953 p_terr_usgs_tbl => l_terr_usgs_tbl,
954 p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
955 p_terr_qual_tbl => l_terr_qual_tbl,
956 p_terr_values_tbl => l_terr_values_tbl,
957 x_terr_id => x_terr_id,
958 x_terr_usgs_out_tbl => l_terr_usgs_out_tbl,
959 x_terr_qualtypeusgs_out_tbl => l_terr_qualtypeusgs_out_tbl,
960 x_terr_qual_out_tbl => l_terr_qual_out_tbl,
961 x_terr_values_out_tbl => l_terr_values_out_tbl
962 );
963 convert_terrusgout_pvttopub (
964 p_terrusgsout_tbl => l_terr_usgs_out_tbl,
965 x_terrusgsout_tbl => x_terr_usgs_out_tbl
966 );
967 convert_terrqtusgout_pvttopub (
968 p_terrqualtypeusgs_tbl => l_terr_qualtypeusgs_out_tbl,
969 x_terrqualtypeusgs_tbl => x_terr_qualtypeusgs_out_tbl
970 );
971 convert_terrqualout_pvttopub (
972 p_terrqual_tbl => l_terr_qual_out_tbl,
973 x_terrqual_tbl => x_terr_qual_out_tbl
974 );
975 convert_terrvaluesout_pvttopub (
976 p_terrvaluesout_tbl => l_terr_values_out_tbl,
977 x_terrvaluesout_tbl => x_terr_values_out_tbl
978 );
979 x_return_status := l_return_status;
980
981 IF l_return_status = fnd_api.g_ret_sts_error
982 THEN
983 RAISE fnd_api.g_exc_error;
984 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
985 THEN
986 RAISE fnd_api.g_exc_unexpected_error;
987 END IF;
988
989 --
990 -- End of API body.
991 --
992
993 -- Standard check of p_commit.
994 IF fnd_api.to_boolean (p_commit)
995 THEN
996 COMMIT WORK;
997 END IF;
998
999 -- Debug Message
1000 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1001 THEN
1002 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1003 fnd_message.set_name ('PROC_NAME', l_api_name);
1004 fnd_msg_pub.add;
1005 END IF;
1006
1007 -- Standard call to get message count and if count is 1, get message info.
1008 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1009 EXCEPTION
1010 WHEN fnd_api.g_exc_error
1011 THEN
1012 ROLLBACK TO create_territory_pub;
1013 x_return_status := fnd_api.g_ret_sts_error;
1014 fnd_msg_pub.count_and_get (
1015 p_count => x_msg_count,
1016 p_data => x_msg_data
1017 );
1018 WHEN fnd_api.g_exc_unexpected_error
1019 THEN
1020 ROLLBACK TO create_territory_pub;
1021 x_return_status := fnd_api.g_ret_sts_unexp_error;
1022 fnd_msg_pub.count_and_get (
1023 p_count => x_msg_count,
1024 p_data => x_msg_data
1025 );
1026 WHEN OTHERS
1027 THEN
1028 ROLLBACK TO create_territory_pub;
1029 x_return_status := fnd_api.g_ret_sts_unexp_error;
1030
1031 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1032 THEN
1033 fnd_msg_pub.add_exc_msg (
1034 'Others exception in Create_Territory PUB' || SQLERRM
1035 );
1036 END IF;
1037
1038 fnd_msg_pub.count_and_get (
1039 p_count => x_msg_count,
1040 p_data => x_msg_data
1041 );
1042 --
1043
1044 END create_territory;
1045
1046 -- ***************************************************
1047 -- start of comments
1048 -- ***************************************************
1049 --
1050 -- API name : Delete_Territory
1051 -- Type : PUBLIC
1052 -- Function : To delete Territories - which would also delete
1053 -- Territory Header, Territory Qualifier, Territory Qual Types
1054 -- Territory Qualifier Values and Resources.
1055 --
1056 -- Pre-reqs :
1057 -- Parameters:
1058 -- IN :
1059 -- Required
1060 -- Parameter Name Data Type Default
1061 -- p_Api_Version_Number NUMBER
1062 -- p_Terr_Id NUMBER
1063 --
1064 -- Optional
1065 -- Parameter Name Data Type Default
1066 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
1067 -- p_Commit VARCHAR2 FND_API.G_FALSE
1068 --
1069 -- OUT :
1070 -- Parameter Name Data Type
1071 -- p_Return_Status VARCHAR2(1)
1072 -- p_Msg_Count NUMBER
1073 -- p_Msg_Data VARCHAR2(2000)
1074 --
1075 --
1076 -- Notes:
1077 -- Rules for deletion have to be very strict.
1078 --
1079 -- End of Comments
1080 --
1081 PROCEDURE delete_territory (
1082 p_api_version_number IN NUMBER,
1083 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1084 p_commit IN VARCHAR2 := fnd_api.g_false,
1085 x_return_status OUT NOCOPY VARCHAR2,
1086 x_msg_count OUT NOCOPY NUMBER,
1087 x_msg_data OUT NOCOPY VARCHAR2,
1088 p_terr_id IN NUMBER
1089 )
1090 AS
1091 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Territory';
1092 l_api_version_number CONSTANT NUMBER := 1.0;
1093 --l_return_status VARCHAR2(1);
1094 BEGIN
1095 -- Standard Start of API savepoint
1096 SAVEPOINT delete_territory_pub;
1097
1098 -- Standard call to check for call compatibility.
1099 IF NOT fnd_api.compatible_api_call (
1100 l_api_version_number,
1101 p_api_version_number,
1102 l_api_name,
1103 g_pkg_name
1104 )
1105 THEN
1106 RAISE fnd_api.g_exc_unexpected_error;
1107 END IF;
1108
1109 -- Initialize message list if p_init_msg_list is set to TRUE.
1110 IF fnd_api.to_boolean (p_init_msg_list)
1111 THEN
1112 fnd_msg_pub.initialize;
1113 END IF;
1114
1115 -- Debug Message
1116 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1117 THEN
1118 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1119 fnd_message.set_name ('PROC_NAME', l_api_name);
1120 fnd_msg_pub.add;
1121 END IF;
1122
1123 -- Initialize API return status to success
1124 x_return_status := fnd_api.g_ret_sts_success;
1125 --
1126 -- API body
1127 --
1128 jtf_territory_pvt.delete_territory (
1129 p_api_version_number => p_api_version_number,
1130 p_init_msg_list => p_init_msg_list,
1131 p_commit => p_commit,
1132 x_return_status => x_return_status,
1133 x_msg_count => x_msg_count,
1134 x_msg_data => x_msg_data,
1135 p_terr_id => p_terr_id
1136 );
1137
1138 --l_return_status := x_return_status;
1139
1140 --
1141 IF x_return_status = fnd_api.g_ret_sts_error
1142 THEN
1143 RAISE fnd_api.g_exc_error;
1144 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1145 THEN
1146 RAISE fnd_api.g_exc_unexpected_error;
1147 END IF;
1148
1149 -- Standard check of p_commit.
1150 IF fnd_api.to_boolean (p_commit)
1151 THEN
1152 COMMIT WORK;
1153 END IF;
1154
1155 -- Debug Message
1156 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1157 THEN
1158 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1159 fnd_message.set_name ('PROC_NAME', l_api_name);
1160 fnd_msg_pub.add;
1161 END IF;
1162
1163 -- Standard call to get message count and if count is 1, get message info.
1164 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1165 EXCEPTION
1166 WHEN fnd_api.g_exc_error
1167 THEN
1168 ROLLBACK TO delete_territory_pub;
1169 x_return_status := fnd_api.g_ret_sts_error;
1170 fnd_msg_pub.count_and_get (
1171 p_count => x_msg_count,
1172 p_data => x_msg_data
1173 );
1174 WHEN fnd_api.g_exc_unexpected_error
1175 THEN
1176 ROLLBACK TO delete_territory_pub;
1177 x_return_status := fnd_api.g_ret_sts_unexp_error;
1178 fnd_msg_pub.count_and_get (
1179 p_count => x_msg_count,
1180 p_data => x_msg_data
1181 );
1182 WHEN OTHERS
1183 THEN
1184 ROLLBACK TO delete_territory_pub;
1185 x_return_status := fnd_api.g_ret_sts_unexp_error;
1186
1187 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1188 THEN
1189 fnd_msg_pub.add_exc_msg (
1190 'Others exception in Delete_Territory PUB' || SQLERRM
1191 );
1192 END IF;
1193
1194 fnd_msg_pub.count_and_get (
1195 p_count => x_msg_count,
1196 p_data => x_msg_data
1197 );
1198 --
1199
1200 END delete_territory;
1201
1202 -- ***************************************************
1203 -- start of comments
1204 -- ***************************************************
1205 -- API name : Update_Territory
1206 -- Type : PUBLIC
1207 -- Function : To update existing Territory Header whcich will update
1208 -- the records in JTF_TERR_ALL table.
1209 -- We can't update the territory usage and Territory Qual Types.
1210 -- Updating Qualifier Values can be done with Update_Qualifier_Value procedure.
1211 -- Pre-reqs :
1212 -- Parameters:
1213 -- IN :
1214 -- Required
1215 -- Parameter Name Data Type Default
1216 -- p_Api_Version_Number NUMBER
1217 -- p_Terr_All_Rec Terr_All_Rec_Type := G_Miss_Terr_All_Rec
1218 --
1219 -- Optional
1220 -- Parameter Name Data Type Default
1221 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1222 -- p_Commit VARCHAR2 := FND_API.G_FALSE
1223 --
1224 -- OUT NOCOPY :
1225 -- Parameter Name Data Type
1226 -- p_Return_Status VARCHAR2(1)
1227 -- p_Msg_Count NUMBER
1228 -- p_Msg_Data VARCHAR2(2000)
1229 -- p_Terr_All_Out_Rec Terr_All_Out_Rec
1230 --
1231 --
1232 -- Notes:
1233 --
1234 --
1235 -- End of Comments
1236 --
1237 PROCEDURE update_territory (
1238 p_api_version_number IN NUMBER,
1239 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1240 p_commit IN VARCHAR2 := fnd_api.g_false,
1241 x_return_status OUT NOCOPY VARCHAR2,
1242 x_msg_count OUT NOCOPY NUMBER,
1243 x_msg_data OUT NOCOPY VARCHAR2,
1244 p_terr_all_rec IN terr_all_rec_type
1245 := g_miss_terr_all_rec,
1246 /* Territory Usage and Transaction types cant be updated in R12.
1247 p_terr_usgs_tbl IN terr_usgs_tbl_type
1248 := g_miss_terr_usgs_tbl,
1249 p_terr_qualtypeusgs_tbl IN terr_qualtypeusgs_tbl_type
1250 := g_miss_terr_qualtypeusgs_tbl,
1251 p_terr_qual_tbl IN terr_qual_tbl_type
1252 := g_miss_terr_qual_tbl,
1253 p_terr_values_tbl IN terr_values_tbl_type
1254 := g_miss_terr_values_tbl, */
1255 x_terr_all_out_rec OUT NOCOPY terr_all_out_rec_type
1256 -- x_terr_usgs_out_tbl OUT NOCOPY terr_usgs_out_tbl_type,
1257 -- x_terr_qualtypeusgs_out_tbl OUT NOCOPY terr_qualtypeusgs_out_tbl_type,
1258 -- x_terr_qual_out_tbl OUT NOCOPY terr_qual_out_tbl_type,
1259 -- x_terr_values_out_tbl OUT NOCOPY terr_values_out_tbl_type
1260 )
1261 AS
1262 l_api_name CONSTANT VARCHAR2(30) := 'Update_Territory';
1263 l_api_version_number CONSTANT NUMBER := 1.0;
1264 l_return_status VARCHAR2(1);
1265 l_terr_out_rec jtf_territory_pvt.terr_all_out_rec_type;
1266 l_terr_usgs_out_tbl jtf_territory_pvt.terr_usgs_out_tbl_type;
1267 l_terr_qualtypeusgs_out_tbl jtf_territory_pvt.terr_qualtypeusgs_out_tbl_type;
1268 l_terr_qual_out_tbl jtf_territory_pvt.terr_qual_out_tbl_type;
1269 l_terr_values_out_tbl jtf_territory_pvt.terr_values_out_tbl_type;
1270 l_terr_rec jtf_territory_pvt.terr_all_rec_type;
1271 l_terr_usgs_tbl jtf_territory_pvt.terr_usgs_tbl_type;
1272 l_terr_qualtypeusgs_tbl jtf_territory_pvt.terr_qualtypeusgs_tbl_type;
1273 l_terr_qual_tbl jtf_territory_pvt.terr_qual_tbl_type;
1274 l_terr_values_tbl jtf_territory_pvt.terr_values_tbl_type;
1275 BEGIN
1276 --dbms_output.put_line('Update_Territory PUB: Entering API');
1277
1278 -- Standard Start of API savepoint
1279 SAVEPOINT update_territory_pub;
1280
1281 -- Standard call to check for call compatibility.
1282 IF NOT fnd_api.compatible_api_call (
1283 l_api_version_number,
1284 p_api_version_number,
1285 l_api_name,
1286 g_pkg_name
1287 )
1288 THEN
1289 RAISE fnd_api.g_exc_unexpected_error;
1290 END IF;
1291
1292 -- Initialize message list if p_init_msg_list is set to TRUE.
1293 IF fnd_api.to_boolean (p_init_msg_list)
1294 THEN
1295 fnd_msg_pub.initialize;
1296 END IF;
1297
1298 -- Debug Message
1299 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1300 THEN
1301 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1302 fnd_message.set_name ('PROC_NAME', l_api_name);
1303 fnd_msg_pub.add;
1304 END IF;
1305
1306 -- Initialize API return status to success
1307 x_return_status := fnd_api.g_ret_sts_success;
1308 --
1309 -- API body
1310 --
1311 --
1312 --dbms_output.put_line('Update_Territory PUB: Before Calling Convertion routines');
1313 convert_terrrec_pubtopvt (
1314 p_terr_rec => p_terr_all_rec,
1315 x_terr_rec => l_terr_rec
1316 );
1317 /* convert_terrqualtbl_pubtopvt (
1318 p_terrqual_tbl => p_terr_qual_tbl,
1319 x_terrqual_tbl => l_terr_qual_tbl
1320 );
1321 convert_terrvaluetbl_pubtopvt (
1322 p_terrvalues_tbl => p_terr_values_tbl,
1323 x_terrvalues_tbl => l_terr_values_tbl
1324 );
1325
1326 convert_terrusgstbl_pubtopvt (
1327 p_terrusgs_tbl => p_terr_usgs_tbl,
1328 x_terrusgs_tbl => l_terr_usgs_tbl
1329 );
1330 convert_terqtypusgtbl_pubtopvt (
1331 p_terrqtypeusgs_tbl => p_terr_qualtypeusgs_tbl,
1332 x_terrqtypeusgs_tbl => l_terr_qualtypeusgs_tbl
1333 ); */
1334 --dbms_output.put_line('Update_Territory PUB: Before Calling JTF_TERRITORY_PVT.Update_Territory');
1335 jtf_territory_pvt.update_territory (
1336 p_api_version_number => 1.0,
1337 p_init_msg_list => fnd_api.g_false,
1338 p_commit => fnd_api.g_false,
1339 p_validation_level => fnd_api.g_valid_level_full,
1340 x_return_status => l_return_status,
1341 x_msg_count => x_msg_count,
1342 x_msg_data => x_msg_data,
1343 p_terr_all_rec => l_terr_rec,
1344 -- p_terr_usgs_tbl => l_terr_usgs_tbl,
1345 -- p_terr_qualtypeusgs_tbl => l_terr_qualtypeusgs_tbl,
1346 -- p_terr_qual_tbl => l_terr_qual_tbl,
1347 -- p_terr_values_tbl => l_terr_values_tbl,
1348 x_terr_all_out_rec => l_terr_out_rec
1349 -- x_terr_usgs_out_tbl => l_terr_usgs_out_tbl,
1350 -- x_terr_qualtypeusgs_out_tbl => l_terr_qualtypeusgs_out_tbl,
1351 -- x_terr_qual_out_tbl => l_terr_qual_out_tbl,
1352 -- x_terr_values_out_tbl => l_terr_values_out_tbl
1353 );
1354 --dbms_output.put_line('Update_Territory PUB: Before Calling Convertion routine from PVT to PUB');
1355 convert_terrout_pvttopub (
1356 p_terr_rec => l_terr_out_rec,
1357 x_terr_rec => x_terr_all_out_rec
1358 );
1359 /* convert_terrusgout_pvttopub (
1360 p_terrusgsout_tbl => l_terr_usgs_out_tbl,
1361 x_terrusgsout_tbl => x_terr_usgs_out_tbl
1362 );
1363 convert_terrqtusgout_pvttopub (
1364 p_terrqualtypeusgs_tbl => l_terr_qualtypeusgs_out_tbl,
1365 x_terrqualtypeusgs_tbl => x_terr_qualtypeusgs_out_tbl
1366 );
1367 convert_terrqualout_pvttopub (
1368 p_terrqual_tbl => l_terr_qual_out_tbl,
1369 x_terrqual_tbl => x_terr_qual_out_tbl
1370 );
1371 convert_terrvaluesout_pvttopub (
1372 p_terrvaluesout_tbl => l_terr_values_out_tbl,
1373 x_terrvaluesout_tbl => x_terr_values_out_tbl
1374 );
1375 */
1376 --
1377
1378 x_return_status := l_return_status;
1379
1380 IF l_return_status <> fnd_api.g_ret_sts_success
1381 THEN
1382 RAISE fnd_api.g_exc_error;
1383 END IF;
1384
1385 -- Debug Message
1386 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1387 THEN
1388 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1389 fnd_message.set_name ('PROC_NAME', l_api_name);
1390 fnd_msg_pub.add;
1391 END IF;
1392
1393 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1394
1395 -- Standard check for p_commit
1396 IF fnd_api.to_boolean (p_commit)
1397 THEN
1398 COMMIT WORK;
1399 END IF;
1400 --dbms_output.put_line('Update_Territory PUB: Exiting API');
1401 --
1402 EXCEPTION
1403 --
1404 WHEN fnd_api.g_exc_error
1405 THEN
1406 --dbms_output.put_line('Update_Territory PUB: FND_API.G_EXC_ERROR');
1407 ROLLBACK TO update_territory_pub;
1408 x_return_status := fnd_api.g_ret_sts_error;
1409 fnd_msg_pub.count_and_get (
1410 p_count => x_msg_count,
1411 p_data => x_msg_data
1412 );
1413 WHEN fnd_api.g_exc_unexpected_error
1414 THEN
1415 --dbms_output.put_line('Update_Territory PUB: FND_API.G_EXC_UNEXPECTED_ERROR');
1416 ROLLBACK TO update_territory_pub;
1417 x_return_status := fnd_api.g_ret_sts_unexp_error;
1418 fnd_msg_pub.count_and_get (
1419 p_count => x_msg_count,
1420 p_data => x_msg_data
1421 );
1422 WHEN OTHERS
1423 THEN
1424 --dbms_output.put_line('Update_Territory PUB: OTHERS - ' || SQLERRM);
1425 ROLLBACK TO update_territory_pub;
1426 x_return_status := fnd_api.g_ret_sts_unexp_error;
1427
1428 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1429 THEN
1430 fnd_msg_pub.add_exc_msg (
1431 'Others exception in Update_Territory PUB' || SQLERRM
1432 );
1433 END IF;
1434
1435 fnd_msg_pub.count_and_get (
1436 p_count => x_msg_count,
1437 p_data => x_msg_data
1438 );
1439 --
1440
1441 END update_territory;
1442
1443 -- ***************************************************
1444 -- start of comments
1445 -- ***************************************************
1446 --
1447 -- API name : Deactivate_Territory
1448 -- Type : PUBLIC
1449 -- Function : To deactivate Territories - this API also deactivates
1450 -- any sub-territories of this territory.
1451 --
1452 -- Pre-reqs :
1453 -- Parameters:
1454 -- IN :
1455 -- Required
1456 -- Parameter Name Data Type Default
1457 -- p_Api_Version_Number NUMBER
1458 -- p_Terr_Id NUMBER
1459 --
1460 -- Optional
1461 -- Parameter Name Data Type Default
1462 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
1463 -- p_Commit VARCHAR2 FND_API.G_FALSE
1464 --
1465 -- OUT NOCOPY :
1466 -- Parameter Name Data Type
1467 -- x_Return_Status VARCHAR2(1)
1468 -- x_Msg_Count NUMBER
1469 -- x_Msg_Data VARCHAR2(2000)
1470 --
1471 --
1472 -- Notes:
1473 --
1474 --
1475 -- End of Comments
1476 --
1477 PROCEDURE deactivate_territory (
1478 p_api_version_number IN NUMBER,
1479 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1480 p_commit IN VARCHAR2 := fnd_api.g_false,
1481 x_return_status OUT NOCOPY VARCHAR2,
1482 x_msg_count OUT NOCOPY NUMBER,
1483 x_msg_data OUT NOCOPY VARCHAR2,
1484 p_terr_id IN NUMBER
1485 )
1486 AS
1487 l_api_name CONSTANT VARCHAR2(30) := 'Deactivate_Territory';
1488 l_api_version_number CONSTANT NUMBER := 1.0;
1489 --l_return_status VARCHAR2(1);
1490 BEGIN
1491 -- Standard Start of API savepoint
1492 SAVEPOINT deactivate_territory_pub;
1493
1494 -- Standard call to check for call compatibility.
1495 IF NOT fnd_api.compatible_api_call (
1496 l_api_version_number,
1497 p_api_version_number,
1498 l_api_name,
1499 g_pkg_name
1500 )
1501 THEN
1502 RAISE fnd_api.g_exc_unexpected_error;
1503 END IF;
1504
1505 -- Initialize message list if p_init_msg_list is set to TRUE.
1506 IF fnd_api.to_boolean (p_init_msg_list)
1507 THEN
1508 fnd_msg_pub.initialize;
1509 END IF;
1510
1511 -- Debug Message
1512 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1513 THEN
1514 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1515 fnd_message.set_name ('PROC_NAME', l_api_name);
1516 fnd_msg_pub.add;
1517 END IF;
1518
1519 -- Initialize API return status to success
1520 x_return_status := fnd_api.g_ret_sts_success;
1521 --
1522 -- API body
1523 --
1524 jtf_territory_pvt.deactivate_territory (
1525 p_api_version_number => p_api_version_number,
1526 p_init_msg_list => p_init_msg_list,
1527 p_commit => p_commit,
1528 x_return_status => x_return_status,
1529 x_msg_count => x_msg_count,
1530 x_msg_data => x_msg_data,
1531 p_terr_id => p_terr_id
1532 );
1533 --x_return_status := l_return_status;
1534
1535 --
1536 IF x_return_status = fnd_api.g_ret_sts_error
1537 THEN
1538 RAISE fnd_api.g_exc_error;
1539 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1540 THEN
1541 RAISE fnd_api.g_exc_unexpected_error;
1542 END IF;
1543
1544 -- Standard check of p_commit.
1545 IF fnd_api.to_boolean (p_commit)
1546 THEN
1547 COMMIT WORK;
1548 END IF;
1549
1550 -- Debug Message
1551 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1552 THEN
1553 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1554 fnd_message.set_name ('PROC_NAME', l_api_name);
1555 fnd_msg_pub.add;
1556 END IF;
1557
1558 -- Standard call to get message count and if count is 1, get message info.
1559 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1560 EXCEPTION
1561 WHEN fnd_api.g_exc_error
1562 THEN
1563 ROLLBACK TO deactivate_territory_pub;
1564 x_return_status := fnd_api.g_ret_sts_error;
1565 fnd_msg_pub.count_and_get (
1566 p_count => x_msg_count,
1567 p_data => x_msg_data
1568 );
1569 WHEN fnd_api.g_exc_unexpected_error
1570 THEN
1571 ROLLBACK TO deactivate_territory_pub;
1572 x_return_status := fnd_api.g_ret_sts_unexp_error;
1573 fnd_msg_pub.count_and_get (
1574 p_count => x_msg_count,
1575 p_data => x_msg_data
1576 );
1577 WHEN OTHERS
1578 THEN
1579 ROLLBACK TO deactivate_territory_pub;
1580 x_return_status := fnd_api.g_ret_sts_unexp_error;
1581
1582 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1583 THEN
1584 fnd_msg_pub.add_exc_msg (
1585 'Others exception in Deactivate_Territory PUB' || SQLERRM
1586 );
1587 END IF;
1588
1589 fnd_msg_pub.count_and_get (
1590 p_count => x_msg_count,
1591 p_data => x_msg_data
1592 );
1593 --
1594
1595 END deactivate_territory;
1596
1597 -- ***************************************************
1598 -- start of comments
1599 -- ***************************************************
1600 -- API name : Create_Territory_Qualifier
1601 -- Type : PUBLIC
1602 -- Function : To create Territories Qualifier and it's Values.
1603 -- P_terr_values_tbl.QUALIFIER_TBL_INDEX is, associates the values with the Qualifier,
1604 -- the index of qualifier record of the qualifier table.
1605 -- Atleast one qualifier value must be passed, other wise, Qualifier can't be created.
1606 -- Pre-reqs :
1607 -- Parameters:
1608 -- IN :
1609 -- Required
1610 -- Parameter Name Data Type Default
1611 -- p_Api_Version_Number NUMBER
1612 -- p_Terr_Qual_Rec Terr_Qual_Rec_Type := G_Miss_Terr_Qual_Tbl
1613 -- p_Terr_Values_Tbl Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl
1614 --
1615 -- Optional
1616 -- Parameter Name Data Type Default
1617 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1618 -- p_Commit VARCHAR2 := FND_API.G_FALSE
1619 -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL,
1620 --
1621 -- OUT :
1622 -- Parameter Name Data Type
1623 -- x_Return_Status VARCHAR2(1)
1624 -- x_Msg_Count NUMBER
1625 -- x_Msg_Data VARCHAR2(2000)
1626 -- x_Terr_Qual_Out_Rec Terr_Qual_Out_Rec_Type,
1627 -- x_Terr_Values_Out_Tbl Terr_Values_Out_Tbl
1628 --
1629 -- End of Comments
1630 --
1631 PROCEDURE Create_Terr_qualifier
1632 (
1633 p_Api_Version_Number IN NUMBER,
1634 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1635 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1636 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1637 x_Return_Status OUT NOCOPY VARCHAR2,
1638 x_Msg_Count OUT NOCOPY NUMBER,
1639 x_Msg_Data OUT NOCOPY VARCHAR2,
1640 P_Terr_Qual_Rec IN Terr_Qual_Rec_Type := G_Miss_Terr_Qual_Rec,
1641 p_Terr_Values_Tbl IN Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl,
1642 X_Terr_Qual_Out_Rec OUT NOCOPY Terr_Qual_Out_Rec_Type,
1643 x_Terr_Values_Out_Tbl OUT NOCOPY Terr_Values_Out_Tbl_Type
1644 )
1645 AS
1646 l_api_name CONSTANT VARCHAR2(30) := 'Create_Territory_Qualifier';
1647 l_api_version_number CONSTANT NUMBER := 1.0;
1648 l_return_status VARCHAR2(1);
1649 l_terr_qual_out_rec jtf_territory_pvt.terr_qual_out_rec_type;
1650 l_terr_values_out_tbl jtf_territory_pvt.terr_values_out_tbl_type;
1651 l_terr_qual_rec jtf_territory_pvt.terr_qual_rec_type;
1652 l_terr_values_tbl jtf_territory_pvt.terr_values_tbl_type;
1653 --
1654
1655 BEGIN
1656 -- Standard Start of API savepoint
1657 SAVEPOINT create_territory_pub;
1658
1659 -- Standard call to check for call compatibility.
1660 IF NOT fnd_api.compatible_api_call (
1661 l_api_version_number,
1662 p_api_version_number,
1663 l_api_name,
1664 g_pkg_name
1665 )
1666 THEN
1667 RAISE fnd_api.g_exc_unexpected_error;
1668 END IF;
1669 -- Initialize message list if p_init_msg_list is set to TRUE.
1670 IF fnd_api.to_boolean (p_init_msg_list)
1671 THEN
1672 fnd_msg_pub.initialize;
1673 END IF;
1674
1675 -- Debug Message
1676 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1677 THEN
1678 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1679 fnd_message.set_name ('PROC_NAME', l_api_name);
1680 fnd_msg_pub.add;
1681 END IF;
1682
1683 -- Initialize API return status to success
1684 x_return_status := fnd_api.g_ret_sts_success;
1685 --
1686 -- API body
1687 --
1688 convert_terrqualrec_pubtopvt (
1689 p_terrqual_rec => p_terr_qual_rec,
1690 x_terrqual_rec => l_terr_qual_rec
1691 );
1692 convert_terrvaluetbl_pubtopvt (
1693 p_terrvalues_tbl => p_terr_values_tbl,
1694 x_terrvalues_tbl => l_terr_values_tbl
1695 );
1696 -- ******************************************************************
1697 jtf_territory_pvt.Create_Terr_qualifier (
1698 p_api_version_number => 1.0,
1699 p_init_msg_list => fnd_api.g_false,
1700 p_commit => fnd_api.g_false,
1701 p_validation_level => fnd_api.g_valid_level_full,
1702 x_return_status => l_return_status,
1703 x_msg_count => x_msg_count,
1704 x_msg_data => x_msg_data,
1705 p_terr_qual_rec => l_terr_qual_rec,
1706 p_terr_values_tbl => l_terr_values_tbl,
1707 x_terr_qual_out_rec => l_terr_qual_out_rec,
1708 x_terr_values_out_tbl => l_terr_values_out_tbl
1709 );
1710 convert_terrqualout_pvttopub (
1711 p_terrqual_rec => l_terr_qual_out_rec,
1712 x_terrqual_rec => x_terr_qual_out_rec
1713 );
1714 convert_terrvaluesout_pvttopub (
1715 p_terrvaluesout_tbl => l_terr_values_out_tbl,
1716 x_terrvaluesout_tbl => x_terr_values_out_tbl
1717 );
1718
1719 x_return_status := l_return_status;
1720
1721 IF l_return_status = fnd_api.g_ret_sts_error
1722 THEN
1723 RAISE fnd_api.g_exc_error;
1724 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1725 THEN
1726 RAISE fnd_api.g_exc_unexpected_error;
1727 END IF;
1728
1729 --
1730 -- End of API body.
1731 --
1732
1733 -- Standard check of p_commit.
1734 IF fnd_api.to_boolean (p_commit)
1735 THEN
1736 COMMIT WORK;
1737 END IF;
1738
1739 -- Debug Message
1740 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1741 THEN
1742 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1743 fnd_message.set_name ('PROC_NAME', l_api_name);
1744 fnd_msg_pub.add;
1745 END IF;
1746
1747 -- Standard call to get message count and if count is 1, get message info.
1748 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1749 EXCEPTION
1750 WHEN fnd_api.g_exc_error
1751 THEN
1752 ROLLBACK TO create_territory_pub;
1753 x_return_status := fnd_api.g_ret_sts_error;
1754 fnd_msg_pub.count_and_get (
1755 p_count => x_msg_count,
1756 p_data => x_msg_data
1757 );
1758 WHEN fnd_api.g_exc_unexpected_error
1759 THEN
1760 ROLLBACK TO create_territory_pub;
1761 x_return_status := fnd_api.g_ret_sts_unexp_error;
1762 fnd_msg_pub.count_and_get (
1763 p_count => x_msg_count,
1764 p_data => x_msg_data
1765 );
1766 WHEN OTHERS
1767 THEN
1768 ROLLBACK TO create_territory_pub;
1769 x_return_status := fnd_api.g_ret_sts_unexp_error;
1770
1771 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1772 THEN
1773 fnd_msg_pub.add_exc_msg (
1774 'Others exception in Create_Territory PUB' || SQLERRM
1775 );
1776 END IF;
1777
1778 fnd_msg_pub.count_and_get (
1779 p_count => x_msg_count,
1780 p_data => x_msg_data
1781 );
1782 --
1783 END Create_Terr_qualifier;
1784
1785
1786 -- ***************************************************
1787 -- start of comments
1788 -- ***************************************************
1789 --
1790 -- API name : Delete_Terr_Qualifier
1791 -- Type : PUBLIC
1792 -- Function : To delete a Territory Qualifier and its Values
1793 -- Pre-reqs :
1794 -- Parameters:
1795 -- IN :
1796 -- Required
1797 -- Parameter Name Data Type Default
1798 -- p_Api_Version_Number NUMBER
1799 -- P_Terr_Qual_Id NUMBER
1800 --
1801 -- Optional
1802 -- Parameter Name Data Type Default
1803 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
1804 -- p_Commit VARCHAR2 FND_API.G_FALSE
1805 --
1806 -- OUT :
1807 -- Parameter Name Data Type
1808 -- p_Return_Status VARCHAR2(1)
1809 -- p_Msg_Count NUMBER
1810 -- p_Msg_Data VARCHAR2(2000)
1811 --
1812 --
1813 -- Notes:
1814 -- Rules for deletion have to be very strict.
1815 --
1816 -- End of Comments
1817 --
1818 PROCEDURE Delete_Terr_Qualifier (
1819 p_api_version_number IN NUMBER,
1820 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1821 p_commit IN VARCHAR2 := fnd_api.g_false,
1822 x_return_status OUT NOCOPY VARCHAR2,
1823 x_msg_count OUT NOCOPY NUMBER,
1824 x_msg_data OUT NOCOPY VARCHAR2,
1825 P_Terr_Qual_Id IN NUMBER
1826 )
1827 AS
1828 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Qualifier';
1829 l_api_version_number CONSTANT NUMBER := 1.0;
1830 l_return_status VARCHAR2(1);
1831 BEGIN
1832 -- Standard Start of API savepoint
1833 SAVEPOINT delete_territory_pub;
1834
1835 -- Standard call to check for call compatibility.
1836 IF NOT fnd_api.compatible_api_call (
1837 l_api_version_number,
1838 p_api_version_number,
1839 l_api_name,
1840 g_pkg_name
1841 )
1842 THEN
1843 RAISE fnd_api.g_exc_unexpected_error;
1844 END IF;
1845
1846 -- Initialize message list if p_init_msg_list is set to TRUE.
1847 IF fnd_api.to_boolean (p_init_msg_list)
1848 THEN
1849 fnd_msg_pub.initialize;
1850 END IF;
1851
1852 -- Debug Message
1853 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1854 THEN
1855 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1856 fnd_message.set_name ('PROC_NAME', l_api_name);
1857 fnd_msg_pub.add;
1858 END IF;
1859
1860 -- Initialize API return status to success
1861 x_return_status := fnd_api.g_ret_sts_success;
1862 --
1863 -- API body
1864 --
1865 jtf_territory_pvt.Delete_Terr_Qualifier (
1866 p_api_version_number => p_api_version_number,
1867 p_init_msg_list => p_init_msg_list,
1868 p_commit => p_commit,
1869 x_return_status => l_return_status,
1870 x_msg_count => x_msg_count,
1871 x_msg_data => x_msg_data,
1872 P_Terr_Qual_Id => P_Terr_Qual_Id
1873 );
1874
1875 x_return_status := l_return_status;
1876
1877 --
1878 IF l_return_status = fnd_api.g_ret_sts_error
1879 THEN
1880 RAISE fnd_api.g_exc_error;
1881 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1882 THEN
1883 RAISE fnd_api.g_exc_unexpected_error;
1884 END IF;
1885
1886 -- Standard check of p_commit.
1887 IF fnd_api.to_boolean (p_commit)
1888 THEN
1889 COMMIT WORK;
1890 END IF;
1891
1892 -- Debug Message
1893 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
1894 THEN
1895 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1896 fnd_message.set_name ('PROC_NAME', l_api_name);
1897 fnd_msg_pub.add;
1898 END IF;
1899
1900 -- Standard call to get message count and if count is 1, get message info.
1901 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1902 EXCEPTION
1903 WHEN fnd_api.g_exc_error
1904 THEN
1905 ROLLBACK TO delete_territory_pub;
1906 x_return_status := fnd_api.g_ret_sts_error;
1907 fnd_msg_pub.count_and_get (
1908 p_count => x_msg_count,
1909 p_data => x_msg_data
1910 );
1911 WHEN fnd_api.g_exc_unexpected_error
1912 THEN
1913 ROLLBACK TO delete_territory_pub;
1914 x_return_status := fnd_api.g_ret_sts_unexp_error;
1915 fnd_msg_pub.count_and_get (
1916 p_count => x_msg_count,
1917 p_data => x_msg_data
1918 );
1919 WHEN OTHERS
1920 THEN
1921 ROLLBACK TO delete_territory_pub;
1922 x_return_status := fnd_api.g_ret_sts_unexp_error;
1923
1924 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1925 THEN
1926 fnd_msg_pub.add_exc_msg (
1927 'Others exception in Delete_Terr_Qualifier PUB' || SQLERRM
1928 );
1929 END IF;
1930
1931 fnd_msg_pub.count_and_get (
1932 p_count => x_msg_count,
1933 p_data => x_msg_data
1934 );
1935 --
1936
1937 END Delete_Terr_Qualifier;
1938
1939 -- ***************************************************
1940 -- start of comments
1941 -- ***************************************************
1942 -- API name : Create_Qualifier_Value
1943 -- Type : PUBLIC
1944 -- Function : To create Territory Qualifier Values.
1945 -- Pre-reqs :
1946 -- Parameters:
1947 -- IN :
1948 -- Required
1949 -- Parameter Name Data Type Default
1950 -- p_Api_Version_Number NUMBER
1951 -- p_terr_qual_id NUMBER (Territory Qualifier ID)
1952 -- p_Terr_Values_Tbl Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl
1953 --
1954 -- Optional
1955 -- Parameter Name Data Type Default
1956 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
1957 -- p_Commit VARCHAR2 := FND_API.G_FALSE
1958 --
1959 -- OUT NOCOPY :
1960 -- Parameter Name Data Type
1961 -- p_Return_Status VARCHAR2(1)
1962 -- p_Msg_Count NUMBER
1963 -- p_Msg_Data VARCHAR2(2000)
1964 -- p_Terr_Values_Out_Tbl Terr_Values_Out_Tbl
1965 --
1966 -- Notes: Custmer need to verify the validity of the Territory Qualifier Value being passed to the procedure.
1967 -- Example : The city name as 'ADDION', instead of ADDISON, is not validated.
1968 --
1969 -- End of Comments
1970 --
1971 PROCEDURE Create_Qualifier_Value (
1972 p_api_version_number IN NUMBER,
1973 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1974 p_commit IN VARCHAR2 := fnd_api.g_false,
1975 x_return_status OUT NOCOPY VARCHAR2,
1976 x_msg_count OUT NOCOPY NUMBER,
1977 x_msg_data OUT NOCOPY VARCHAR2,
1978 p_terr_qual_id IN NUMBER,
1979 p_terr_values_tbl IN terr_values_tbl_type
1980 := g_miss_terr_values_tbl,
1981 x_terr_values_out_tbl OUT NOCOPY terr_values_out_tbl_type
1982 )
1983 AS
1984 l_api_name CONSTANT VARCHAR2(30) := 'create_terr_values';
1985 l_api_version_number CONSTANT NUMBER := 1.0;
1986 l_return_status VARCHAR2(1);
1987 l_terr_values_out_tbl jtf_territory_pvt.terr_values_out_tbl_type;
1988 l_terr_values_tbl jtf_territory_pvt.terr_values_tbl_type;
1989 l_Terr_Qual_Id NUMBER;
1990 BEGIN
1991 --dbms_output.put_line('create_terr_value PUB: Entering API');
1992
1993 -- Standard Start of API savepoint
1994 SAVEPOINT update_territory_pub;
1995
1996 -- Standard call to check for call compatibility.
1997 IF NOT fnd_api.compatible_api_call (
1998 l_api_version_number,
1999 p_api_version_number,
2000 l_api_name,
2001 g_pkg_name
2002 )
2003 THEN
2004 RAISE fnd_api.g_exc_unexpected_error;
2005 END IF;
2006
2007 -- Initialize message list if p_init_msg_list is set to TRUE.
2008 IF fnd_api.to_boolean (p_init_msg_list)
2009 THEN
2010 fnd_msg_pub.initialize;
2011 END IF;
2012
2013 -- Debug Message
2014 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2015 THEN
2016 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2017 fnd_message.set_name ('PROC_NAME', l_api_name);
2018 fnd_msg_pub.add;
2019 END IF;
2020
2021 -- Initialize API return status to success
2022 x_return_status := fnd_api.g_ret_sts_success;
2023 --
2024 -- API body
2025 --
2026 --
2027 --dbms_output.put_line('Update_Territory PUB: Before Calling Convertion routines');
2028
2029 convert_terrvaluetbl_pubtopvt (
2030 p_terrvalues_tbl => p_terr_values_tbl,
2031 x_terrvalues_tbl => l_terr_values_tbl
2032 );
2033
2034 -- Check whether ant data is passed for update of value table
2035 If P_Terr_Values_Tbl.Count > 0 Then
2036 --dbms_output('Update_Territory PVT: Before Calling Update_Terr_Value');
2037 jtf_territory_pvt.create_terr_value( P_Api_Version_Number => 1.0,
2038 P_Init_Msg_List => fnd_api.g_false,
2039 P_Commit => fnd_api.g_false,
2040 p_validation_level => fnd_api.g_valid_level_full,
2041 P_Terr_Id => null,
2042 p_terr_qual_id => p_terr_qual_id,
2043 P_Terr_Value_Tbl => l_Terr_Values_Tbl,
2044 X_Return_Status => l_Return_Status,
2045 X_Msg_Count => x_Msg_Count,
2046 X_Msg_Data => x_Msg_Data,
2047 X_Terr_Value_Out_Tbl => l_Terr_Values_Out_Tbl);
2048 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2049 RAISE FND_API.G_EXC_ERROR;
2050 END IF;
2051 End If;
2052
2053 --dbms_output.put_line('Update_Territory PUB: Before Calling Convertion routine from PVT to PUB');
2054 convert_terrvaluesout_pvttopub (
2055 p_terrvaluesout_tbl => l_terr_values_out_tbl,
2056 x_terrvaluesout_tbl => x_terr_values_out_tbl
2057 );
2058
2059 x_return_status := l_return_status;
2060 --
2061 IF l_return_status <> fnd_api.g_ret_sts_success
2062 THEN
2063 RAISE fnd_api.g_exc_error;
2064 END IF;
2065
2066 -- Debug Message
2067 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2068 THEN
2069 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2070 fnd_message.set_name ('PROC_NAME', l_api_name);
2071 fnd_msg_pub.add;
2072 END IF;
2073
2074 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2075
2076 -- Standard check for p_commit
2077 IF fnd_api.to_boolean (p_commit)
2078 THEN
2079 COMMIT WORK;
2080 END IF;
2081 --dbms_output.put_line('create_terr_value PUB: Exiting API');
2082 --
2083 EXCEPTION
2084 --
2085 WHEN fnd_api.g_exc_error
2086 THEN
2087 --dbms_output.put_line('Update_Territory Values PUB: FND_API.G_EXC_ERROR');
2088 ROLLBACK TO update_territory_pub;
2089 x_return_status := fnd_api.g_ret_sts_error;
2090 fnd_msg_pub.count_and_get (
2091 p_count => x_msg_count,
2092 p_data => x_msg_data
2093 );
2094 WHEN fnd_api.g_exc_unexpected_error
2095 THEN
2096 --dbms_output.put_line('Update_Territory_Values PUB: FND_API.G_EXC_UNEXPECTED_ERROR');
2097 ROLLBACK TO update_territory_pub;
2098 x_return_status := fnd_api.g_ret_sts_unexp_error;
2099 fnd_msg_pub.count_and_get (
2100 p_count => x_msg_count,
2101 p_data => x_msg_data
2102 );
2103 WHEN OTHERS
2104 THEN
2105 --dbms_output.put_line('Update_Territory Values PUB: OTHERS - ' || SQLERRM);
2106 ROLLBACK TO update_territory_pub;
2107 x_return_status := fnd_api.g_ret_sts_unexp_error;
2108
2109 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2110 THEN
2111 fnd_msg_pub.add_exc_msg (
2112 'Others exception in create_terr_values ' || SQLERRM
2113 );
2114 END IF;
2115
2116 fnd_msg_pub.count_and_get (
2117 p_count => x_msg_count,
2118 p_data => x_msg_data
2119 );
2120 --
2121
2122 END Create_Qualifier_Value;
2123
2124 -- ***************************************************
2125 -- start of comments
2126 -- ***************************************************
2127 -- API name : Update_Terr_Value
2128 -- Type : PUBLIC
2129 -- Function : To update existing Territory Qualifier Values
2130 -- Pre-reqs :
2131 -- Parameters:
2132 -- IN :
2133 -- Required
2134 -- Parameter Name Data Type Default
2135 -- p_Api_Version_Number NUMBER
2136 -- p_Terr_Values_Tbl Terr_Values_Tbl_Type := G_Miss_Terr_Values_Tbl
2137 --
2138 -- Optional
2139 -- Parameter Name Data Type Default
2140 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
2141 -- p_Commit VARCHAR2 := FND_API.G_FALSE
2142 --
2143 -- OUT NOCOPY :
2144 -- Parameter Name Data Type
2145 -- p_Return_Status VARCHAR2(1)
2146 -- p_Msg_Count NUMBER
2147 -- p_Msg_Data VARCHAR2(2000)
2148 -- p_Terr_Values_Out_Tbl Terr_Values_Out_Tbl
2149 --
2150 -- Notes: Custmoer need to verify the validity of the Territory Qualifier Value being passed to the procedure.
2151 -- Example : The city name as 'ADDION', instead of ADDISON, is not validated.
2152 --
2153 -- End of Comments
2154 --
2155 PROCEDURE Update_Qualifier_Value (
2156 p_api_version_number IN NUMBER,
2157 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2158 p_commit IN VARCHAR2 := fnd_api.g_false,
2159 x_return_status OUT NOCOPY VARCHAR2,
2160 x_msg_count OUT NOCOPY NUMBER,
2161 x_msg_data OUT NOCOPY VARCHAR2,
2162 p_terr_values_tbl IN terr_values_tbl_type
2163 := g_miss_terr_values_tbl,
2164 x_terr_values_out_tbl OUT NOCOPY terr_values_out_tbl_type
2165 )
2166 AS
2167 l_api_name CONSTANT VARCHAR2(30) := 'Update_Territory';
2168 l_api_version_number CONSTANT NUMBER := 1.0;
2169 l_return_status VARCHAR2(1);
2170 l_terr_values_out_tbl jtf_territory_pvt.terr_values_out_tbl_type;
2171 l_terr_values_tbl jtf_territory_pvt.terr_values_tbl_type;
2172 BEGIN
2173 --dbms_output.put_line('Update_Territory PUB: Entering API');
2174
2175 -- Standard Start of API savepoint
2176 SAVEPOINT update_territory_pub;
2177
2178 -- Standard call to check for call compatibility.
2179 IF NOT fnd_api.compatible_api_call (
2180 l_api_version_number,
2181 p_api_version_number,
2182 l_api_name,
2183 g_pkg_name
2184 )
2185 THEN
2186 RAISE fnd_api.g_exc_unexpected_error;
2187 END IF;
2188
2189 -- Initialize message list if p_init_msg_list is set to TRUE.
2190 IF fnd_api.to_boolean (p_init_msg_list)
2191 THEN
2192 fnd_msg_pub.initialize;
2193 END IF;
2194
2195 -- Debug Message
2196 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2197 THEN
2198 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2199 fnd_message.set_name ('PROC_NAME', l_api_name);
2200 fnd_msg_pub.add;
2201 END IF;
2202
2203 -- Initialize API return status to success
2204 x_return_status := fnd_api.g_ret_sts_success;
2205 --
2206 -- API body
2207 --
2208 --
2209 --dbms_output.put_line('Update_Territory PUB: Before Calling Convertion routines');
2210
2211 convert_terrvaluetbl_pubtopvt (
2212 p_terrvalues_tbl => p_terr_values_tbl,
2213 x_terrvalues_tbl => l_terr_values_tbl
2214 );
2215
2216 -- Check whether ant data is passed for update of value table
2217 If P_Terr_Values_Tbl.Count > 0 Then
2218 --dbms_output('Update_Territory PVT: Before Calling Update_Terr_Value');
2219 jtf_territory_pvt.Update_Terr_Value( P_Api_Version_Number => 1.0,
2220 P_Init_Msg_List => fnd_api.g_false,
2221 P_Commit => fnd_api.g_false,
2222 p_validation_level => fnd_api.g_valid_level_full,
2223 P_Terr_Value_Tbl => l_Terr_Values_Tbl,
2224 X_Return_Status => l_Return_Status,
2225 X_Msg_Count => x_Msg_Count,
2226 X_Msg_Data => x_Msg_Data,
2227 X_Terr_Value_Out_Tbl => l_Terr_Values_Out_Tbl);
2228 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2229 RAISE FND_API.G_EXC_ERROR;
2230 END IF;
2231 End If;
2232
2233 --dbms_output.put_line('Update_Territory PUB: Before Calling Convertion routine from PVT to PUB');
2234 convert_terrvaluesout_pvttopub (
2235 p_terrvaluesout_tbl => l_terr_values_out_tbl,
2236 x_terrvaluesout_tbl => x_terr_values_out_tbl
2237 );
2238
2239 x_return_status := l_return_status;
2240 --
2241 IF l_return_status <> fnd_api.g_ret_sts_success
2242 THEN
2243 RAISE fnd_api.g_exc_error;
2244 END IF;
2245
2246 -- Debug Message
2247 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2248 THEN
2249 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2250 fnd_message.set_name ('PROC_NAME', l_api_name);
2251 fnd_msg_pub.add;
2252 END IF;
2253
2254 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2255
2256 -- Standard check for p_commit
2257 IF fnd_api.to_boolean (p_commit)
2258 THEN
2259 COMMIT WORK;
2260 END IF;
2261 --dbms_output.put_line('Update_Territory values PUB: Exiting API');
2262 --
2263 EXCEPTION
2264 --
2265 WHEN fnd_api.g_exc_error
2266 THEN
2267 --dbms_output.put_line('Update_Territory Values PUB: FND_API.G_EXC_ERROR');
2268 ROLLBACK TO update_territory_pub;
2269 x_return_status := fnd_api.g_ret_sts_error;
2270 fnd_msg_pub.count_and_get (
2271 p_count => x_msg_count,
2272 p_data => x_msg_data
2273 );
2274 WHEN fnd_api.g_exc_unexpected_error
2275 THEN
2276 --dbms_output.put_line('Update_Territory_Values PUB: FND_API.G_EXC_UNEXPECTED_ERROR');
2277 ROLLBACK TO update_territory_pub;
2278 x_return_status := fnd_api.g_ret_sts_unexp_error;
2279 fnd_msg_pub.count_and_get (
2280 p_count => x_msg_count,
2281 p_data => x_msg_data
2282 );
2283 WHEN OTHERS
2284 THEN
2285 --dbms_output.put_line('Update_Territory Values PUB: OTHERS - ' || SQLERRM);
2286 ROLLBACK TO update_territory_pub;
2287 x_return_status := fnd_api.g_ret_sts_unexp_error;
2288
2289 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2290 THEN
2291 fnd_msg_pub.add_exc_msg (
2292 'Others exception in Update_Territory Values ' || SQLERRM
2293 );
2294 END IF;
2295
2296 fnd_msg_pub.count_and_get (
2297 p_count => x_msg_count,
2298 p_data => x_msg_data
2299 );
2300 --
2301
2302 END Update_Qualifier_Value;
2303
2304 -- ***************************************************
2305 -- start of comments
2306 -- ***************************************************
2307 --
2308 -- API name : Delete_Qualifier_Value
2309 -- Type : PUBLIC
2310 -- Function : To delete a Territoriy Qualifier Value
2311 -- Pre-reqs :
2312 -- Parameters:
2313 -- IN :
2314 -- Required
2315 -- Parameter Name Data Type Default
2316 -- p_Api_Version_Number NUMBER
2317 -- P_Terr_Value_Id NUMBER
2318 --
2319 -- Optional
2320 -- Parameter Name Data Type Default
2321 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
2322 -- p_Commit VARCHAR2 FND_API.G_FALSE
2323 --
2324 -- OUT :
2325 -- Parameter Name Data Type
2326 -- p_Return_Status VARCHAR2(1)
2327 -- p_Msg_Count NUMBER
2328 -- p_Msg_Data VARCHAR2(2000)
2329 --
2330 --
2331 -- Notes:
2332 -- Rules for deletion have to be very strict.
2333 --
2334 -- End of Comments
2335 --
2336 PROCEDURE Delete_Qualifier_Value (
2337 p_api_version_number IN NUMBER,
2338 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2339 p_commit IN VARCHAR2 := fnd_api.g_false,
2340 x_return_status OUT NOCOPY VARCHAR2,
2341 x_msg_count OUT NOCOPY NUMBER,
2342 x_msg_data OUT NOCOPY VARCHAR2,
2343 P_Terr_Value_Id IN NUMBER
2344 )
2345 AS
2346 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Value';
2347 l_api_version_number CONSTANT NUMBER := 1.0;
2348 l_return_status VARCHAR2(1);
2349 BEGIN
2350 -- Standard Start of API savepoint
2351 SAVEPOINT delete_territory_pub;
2352
2353 -- Standard call to check for call compatibility.
2354 IF NOT fnd_api.compatible_api_call (
2355 l_api_version_number,
2356 p_api_version_number,
2357 l_api_name,
2358 g_pkg_name
2359 )
2360 THEN
2361 RAISE fnd_api.g_exc_unexpected_error;
2362 END IF;
2363
2364 -- Initialize message list if p_init_msg_list is set to TRUE.
2365 IF fnd_api.to_boolean (p_init_msg_list)
2366 THEN
2367 fnd_msg_pub.initialize;
2368 END IF;
2369
2370 -- Debug Message
2371 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2372 THEN
2373 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2374 fnd_message.set_name ('PROC_NAME', l_api_name);
2375 fnd_msg_pub.add;
2376 END IF;
2377
2378 -- Initialize API return status to success
2379 x_return_status := fnd_api.g_ret_sts_success;
2380 --
2381 -- API body
2382 --
2383
2384 jtf_territory_pvt.Delete_Terr_Value (
2385 p_api_version_number => p_api_version_number,
2386 p_init_msg_list => p_init_msg_list,
2387 p_commit => p_commit,
2388 x_return_status => l_return_status,
2389 x_msg_count => x_msg_count,
2390 x_msg_data => x_msg_data,
2391 P_Terr_Value_Id => P_Terr_Value_Id
2392 );
2393
2394 x_return_status := l_return_status;
2395
2396 --
2397 IF l_return_status = fnd_api.g_ret_sts_error
2398 THEN
2399 RAISE fnd_api.g_exc_error;
2400 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2401 THEN
2402 RAISE fnd_api.g_exc_unexpected_error;
2403 END IF;
2404
2405 -- Standard check of p_commit.
2406 IF fnd_api.to_boolean (p_commit)
2407 THEN
2408 COMMIT WORK;
2409 END IF;
2410
2411 -- Debug Message
2412 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2413 THEN
2414 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2415 fnd_message.set_name ('PROC_NAME', l_api_name);
2416 fnd_msg_pub.add;
2417 END IF;
2418
2419 -- Standard call to get message count and if count is 1, get message info.
2420 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2421 EXCEPTION
2422 WHEN fnd_api.g_exc_error
2423 THEN
2424 ROLLBACK TO delete_territory_pub;
2425 x_return_status := fnd_api.g_ret_sts_error;
2426 fnd_msg_pub.count_and_get (
2427 p_count => x_msg_count,
2428 p_data => x_msg_data
2429 );
2430 WHEN fnd_api.g_exc_unexpected_error
2431 THEN
2432 ROLLBACK TO delete_territory_pub;
2433 x_return_status := fnd_api.g_ret_sts_unexp_error;
2434 fnd_msg_pub.count_and_get (
2435 p_count => x_msg_count,
2436 p_data => x_msg_data
2437 );
2438 WHEN OTHERS
2439 THEN
2440 ROLLBACK TO delete_territory_pub;
2441 x_return_status := fnd_api.g_ret_sts_unexp_error;
2442
2443 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2444 THEN
2445 fnd_msg_pub.add_exc_msg (
2446 'Others exception in Delete_Terr_Value PUB' || SQLERRM
2447 );
2448 END IF;
2449
2450 fnd_msg_pub.count_and_get (
2451 p_count => x_msg_count,
2452 p_data => x_msg_data
2453 );
2454 --
2455
2456 END Delete_Qualifier_Value;
2457
2458 -- ***************************************************
2459 -- start of comments
2460 -- ***************************************************
2461 -- API name : Create_TerrResource
2462 -- Type : PUBLIC
2463 -- Function : To create Territory Resources - which will insert
2464 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
2465 -- tables.
2466 -- If the user doesnot provide access records, this procedure
2467 -- inserts the rows into jtf_terr_rsc_access_all for all Transaction Types
2468 -- associated with the territory with access as 'FULL_ACCESS'
2469 --
2470 -- Pre-reqs :
2471 -- Parameters:
2472 -- IN :
2473 -- Required
2474 -- Parameter Name Data Type Default
2475 -- p_Api_Version_Number NUMBER
2476 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
2477 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
2478 --
2479 -- Optional
2480 -- Parameter Name Data Type Default
2481 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
2482 -- p_Commit VARCHAR2 := FND_API.G_FALSE
2483 --
2484 -- OUT NOCOPY :
2485 -- Parameter Name Data Type
2486 -- x_Return_Status VARCHAR2(1)
2487 -- x_Msg_Count NUMBER
2488 -- x_Msg_Data VARCHAR2(2000)
2489 -- x_TerrRsc_Id NUMBER
2490 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
2491 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
2492 --
2493 -- Notes:
2494 --
2495 --
2496 -- End of Comments
2497 --
2498 PROCEDURE create_terrresource (
2499 p_api_version_number IN NUMBER,
2500 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2501 p_commit IN VARCHAR2 := fnd_api.g_false,
2502 x_return_status OUT NOCOPY VARCHAR2,
2503 x_msg_count OUT NOCOPY NUMBER,
2504 x_msg_data OUT NOCOPY VARCHAR2,
2505 p_terrrsc_tbl IN terrresource_tbl_type
2506 := g_miss_terrresource_tbl,
2507 p_terrrsc_access_tbl IN terrrsc_access_tbl_type
2508 := g_miss_terrrsc_access_tbl,
2509 x_terrrsc_out_tbl OUT NOCOPY terrresource_out_tbl_type,
2510 x_terrrsc_access_out_tbl OUT NOCOPY terrrsc_access_out_tbl_type
2511 )
2512 IS
2513 l_api_name CONSTANT VARCHAR2(30) := 'Create_TerrResource';
2514 l_api_version_number CONSTANT NUMBER := 1.0;
2515 l_return_status VARCHAR2(1);
2516 l_terrrsc_tbl jtf_territory_resource_pvt.terrresource_tbl_type;
2517 l_terrrsc_access_tbl jtf_territory_resource_pvt.terrrsc_access_tbl_type;
2518 l_terrrsc_out_tbl jtf_territory_resource_pvt.terrresource_out_tbl_type;
2519 l_terrrsc_access_out_tbl jtf_territory_resource_pvt.terrrsc_access_out_tbl_type;
2520 --
2521
2522 BEGIN
2523 -- Standard Start of API savepoint
2524 SAVEPOINT create_terrresource_pub;
2525
2526 -- Standard call to check for call compatibility.
2527 IF NOT fnd_api.compatible_api_call (
2528 l_api_version_number,
2529 p_api_version_number,
2530 l_api_name,
2531 g_pkg_name
2532 )
2533 THEN
2534 RAISE fnd_api.g_exc_unexpected_error;
2535 END IF;
2536
2537 -- Initialize message list if p_init_msg_list is set to TRUE.
2538 IF fnd_api.to_boolean (p_init_msg_list)
2539 THEN
2540 fnd_msg_pub.initialize;
2541 END IF;
2542 fnd_msg_pub.initialize;
2543
2544 -- Debug Message
2545 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2546 THEN
2547 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2548 fnd_message.set_name ('PROC_NAME', l_api_name);
2549 fnd_msg_pub.add;
2550 END IF;
2551
2552 -- Initialize API return status to success
2553 x_return_status := fnd_api.g_ret_sts_success;
2554 -- Convert incomming data from public to private Tbl format
2555 convert_terrrsc_pubtopvt (
2556 p_terrrsc_tbl => p_terrrsc_tbl,
2557 x_terrrsc_tbl => l_terrrsc_tbl
2558 );
2559 convert_terrrscacc_pubtopvt (
2560 p_terrrscacc_tbl => p_terrrsc_access_tbl,
2561 x_terrrscacc_tbl => l_terrrsc_access_tbl
2562 );
2563 --
2564 -- API body
2565 --
2566 jtf_territory_resource_pvt.create_terrresource (
2567 p_api_version_number => 1.0,
2568 p_init_msg_list => fnd_api.g_false,
2569 p_commit => fnd_api.g_false,
2570 p_validation_level => fnd_api.g_valid_level_full,
2571 x_return_status => l_return_status,
2572 x_msg_count => x_msg_count,
2573 x_msg_data => x_msg_data,
2574 p_terrrsc_tbl => l_terrrsc_tbl,
2575 p_terrrsc_access_tbl => l_terrrsc_access_tbl,
2576 x_terrrsc_out_tbl => l_terrrsc_out_tbl,
2577 x_terrrsc_access_out_tbl => l_terrrsc_access_out_tbl
2578 );
2579 convert_terrrscout_pvttopub (
2580 p_terrrscout_tbl => l_terrrsc_out_tbl,
2581 x_terrrscout_tbl => x_terrrsc_out_tbl
2582 );
2583 convert_terrscaccout_pvttopub (
2584 p_terrrscaccout_tbl => l_terrrsc_access_out_tbl,
2585 x_terrrscaccout_tbl => x_terrrsc_access_out_tbl
2586 );
2587
2588 x_return_status := l_return_status;
2589
2590 IF l_return_status = fnd_api.g_ret_sts_error
2591 THEN
2592 RAISE fnd_api.g_exc_error;
2593 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2594 THEN
2595 RAISE fnd_api.g_exc_unexpected_error;
2596 END IF;
2597
2598 --
2599 -- End of API body.
2600 --
2601
2602 -- Standard check of p_commit.
2603 IF fnd_api.to_boolean (p_commit)
2604 THEN
2605 COMMIT WORK;
2606 END IF;
2607
2608 -- Debug Message
2609 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2610 THEN
2611 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2612 fnd_message.set_name ('PROC_NAME', l_api_name);
2613 fnd_msg_pub.add;
2614 END IF;
2615
2616 -- Standard call to get message count and if count is 1, get message info.
2617 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2618 EXCEPTION
2619 WHEN fnd_api.g_exc_error
2620 THEN
2621 ROLLBACK TO create_terrresource_pub;
2622 x_return_status := fnd_api.g_ret_sts_error;
2623 fnd_msg_pub.count_and_get (
2624 p_count => x_msg_count,
2625 p_data => x_msg_data
2626 );
2627
2628 WHEN fnd_api.g_exc_unexpected_error
2629 THEN
2630 ROLLBACK TO create_terrresource_pub;
2631 x_return_status := fnd_api.g_ret_sts_unexp_error;
2632 fnd_msg_pub.count_and_get (
2633 p_count => x_msg_count,
2634 p_data => x_msg_data
2635 );
2636
2637 WHEN OTHERS
2638 THEN
2639 ROLLBACK TO create_terrresource_pub;
2640 x_return_status := fnd_api.g_ret_sts_unexp_error;
2641
2642 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2643 THEN
2644 fnd_msg_pub.add_exc_msg (
2645 'Others exception in Create_TerrResource PUB' || SQLERRM
2646 );
2647 END IF;
2648
2649 fnd_msg_pub.count_and_get (
2650 p_count => x_msg_count,
2651 p_data => x_msg_data
2652 );
2653 --
2654
2655 END create_terrresource;
2656
2657 -- ***************************************************
2658 -- start of comments
2659 -- ***************************************************
2660 --
2661 -- API name : Delete_TerrResource
2662 -- Type : PUBLIC
2663 -- Function : To delete Territories - which would also delete
2664 -- records from jtf_terr_rsc_access_all, jtf_terr_rsc_all
2665 -- tables.
2666 --
2667 -- Pre-reqs :
2668 -- Parameters:
2669 -- IN :
2670 -- Required
2671 -- Parameter Name Data Type Default
2672 -- p_Api_Version_Number NUMBER
2673 -- p_TerrRsc_Id NUMBER
2674 --
2675 -- Optional
2676 -- Parameter Name Data Type Default
2677 -- p_Init_Msg_List VARCHAR2 FND_API.G_FALSE
2678 -- p_Commit VARCHAR2 FND_API.G_FALSE
2679 --
2680 -- OUT NOCOPY :
2681 -- Parameter Name Data Type
2682 -- p_Return_Status VARCHAR2(1)
2683 -- p_Msg_Count NUMBER
2684 -- p_Msg_Data VARCHAR2(2000)
2685 --
2686 --
2687 -- Notes:
2688 -- Rules for deletion have to be very strict.
2689 --
2690 -- End of Comments
2691 --
2692 PROCEDURE delete_terrresource (
2693 p_api_version_number IN NUMBER,
2694 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2695 p_commit IN VARCHAR2 := fnd_api.g_false,
2696 x_return_status OUT NOCOPY VARCHAR2,
2697 x_msg_count OUT NOCOPY NUMBER,
2698 x_msg_data OUT NOCOPY VARCHAR2,
2699 p_terrrsc_id IN NUMBER
2700 )
2701 AS
2702 l_api_name CONSTANT VARCHAR2(30) := 'Delete_TerrResource';
2703 l_api_version_number CONSTANT NUMBER := 1.0;
2704 l_return_status VARCHAR2(1);
2705 BEGIN
2706 -- Standard Start of API savepoint
2707 SAVEPOINT delete_terrresource_pub;
2708
2709 -- Standard call to check for call compatibility.
2710 IF NOT fnd_api.compatible_api_call (
2711 l_api_version_number,
2712 p_api_version_number,
2713 l_api_name,
2714 g_pkg_name
2715 )
2716 THEN
2717 RAISE fnd_api.g_exc_unexpected_error;
2718 END IF;
2719
2720 -- Initialize message list if p_init_msg_list is set to TRUE.
2721 IF fnd_api.to_boolean (p_init_msg_list)
2722 THEN
2723 fnd_msg_pub.initialize;
2724 END IF;
2725
2726 -- Debug Message
2727 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2728 THEN
2729 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2730 fnd_message.set_name ('PROC_NAME', l_api_name);
2731 fnd_msg_pub.add;
2732 END IF;
2733
2734 -- Initialize API return status to success
2735 x_return_status := fnd_api.g_ret_sts_success;
2736 --
2737 -- API body
2738 --
2739 jtf_territory_resource_pvt.delete_terr_resource (
2740 p_api_version_number => p_api_version_number,
2741 p_init_msg_list => p_init_msg_list,
2742 p_commit => p_commit,
2743 p_validation_level => fnd_api.g_valid_level_full,
2744 x_return_status => l_return_status,
2745 x_msg_count => x_msg_count,
2746 x_msg_data => x_msg_data,
2747 p_terrrsc_id => p_terrrsc_id
2748 );
2749
2750 x_return_status := l_return_status;
2751
2752 --
2753 IF l_return_status = fnd_api.g_ret_sts_error
2754 THEN
2755 RAISE fnd_api.g_exc_error;
2756 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2757 THEN
2758 RAISE fnd_api.g_exc_unexpected_error;
2759 END IF;
2760
2761 -- Standard check of p_commit.
2762 IF fnd_api.to_boolean (p_commit)
2763 THEN
2764 COMMIT WORK;
2765 END IF;
2766
2767 -- Debug Message
2768 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2769 THEN
2770 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2771 fnd_message.set_name ('PROC_NAME', l_api_name);
2772 fnd_msg_pub.add;
2773 END IF;
2774
2775 -- Standard call to get message count and if count is 1, get message info.
2776 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2777 EXCEPTION
2778 WHEN fnd_api.g_exc_error
2779 THEN
2780 ROLLBACK TO delete_terrresource_pub;
2781 x_return_status := fnd_api.g_ret_sts_error;
2782 fnd_msg_pub.count_and_get (
2783 p_count => x_msg_count,
2784 p_data => x_msg_data
2785 );
2786 WHEN fnd_api.g_exc_unexpected_error
2787 THEN
2788 ROLLBACK TO delete_terrresource_pub;
2789 x_return_status := fnd_api.g_ret_sts_unexp_error;
2790 fnd_msg_pub.count_and_get (
2791 p_count => x_msg_count,
2792 p_data => x_msg_data
2793 );
2794 WHEN OTHERS
2795 THEN
2796 ROLLBACK TO delete_terrresource_pub;
2797 x_return_status := fnd_api.g_ret_sts_unexp_error;
2798
2799 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2800 THEN
2801 fnd_msg_pub.add_exc_msg (
2802 'Others exception in Delete_TerrResource PUB' || SQLERRM
2803 );
2804 END IF;
2805
2806 fnd_msg_pub.count_and_get (
2807 p_count => x_msg_count,
2808 p_data => x_msg_data
2809 );
2810 --
2811
2812 END delete_terrresource;
2813
2814 -- ***************************************************
2815 -- start of comments
2816 -- ***************************************************
2817 -- API name : Update_TerrResource
2818 -- Type : PUBLIC
2819 -- Function : To Update Territory Resources - which will update
2820 -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
2821 -- tables.
2822 --
2823 -- Pre-reqs :
2824 -- Parameters:
2825 -- IN :
2826 -- Required
2827 -- Parameter Name Data Type Default
2828 -- p_Api_Version_Number NUMBER
2829 -- p_TerrRsc_Tbl TerrResource_tbl_type := G_MISS_TERRRESOURCE_TBL
2830 -- p_TerrRsc_Access_Tbl TerrRsc_Access_tbl_type := G_MISS_TERRRSC_ACCESS_TBL
2831 --
2832 -- Optional
2833 -- Parameter Name Data Type Default
2834 -- p_Init_Msg_List VARCHAR2 := FND_API.G_FALSE
2835 -- p_Commit VARCHAR2 := FND_API.G_FALSE
2836 --
2837 -- OUT NOCOPY :
2838 -- Parameter Name Data Type
2839 -- x_Return_Status VARCHAR2(1)
2840 -- x_Msg_Count NUMBER
2841 -- x_Msg_Data VARCHAR2(2000)
2842 -- x_TerrRsc_Id NUMBER
2843 -- x_Terr_Usgs_Out_Tbl TerrResource_out_tbl_type
2844 -- x_Terr_QualTypeUsgs_Out_Tbl TerrRes_Access_out_tbl_type
2845 --
2846 -- Notes:
2847 --
2848 --
2849 -- End of Comments
2850 --
2851 PROCEDURE update_terrresource (
2852 p_api_version_number IN NUMBER,
2853 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2854 p_commit IN VARCHAR2 := fnd_api.g_false,
2855 x_return_status OUT NOCOPY VARCHAR2,
2856 x_msg_count OUT NOCOPY NUMBER,
2857 x_msg_data OUT NOCOPY VARCHAR2,
2858 p_terrrsc_tbl IN terrresource_tbl_type
2859 := g_miss_terrresource_tbl,
2860 p_terrrsc_access_tbl IN terrrsc_access_tbl_type
2861 := g_miss_terrrsc_access_tbl,
2862 x_terrrsc_out_tbl OUT NOCOPY terrresource_out_tbl_type,
2863 x_terrrsc_access_out_tbl OUT NOCOPY terrrsc_access_out_tbl_type
2864 )
2865 AS
2866 l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrResource';
2867 l_api_version_number CONSTANT NUMBER := 1.0;
2868 l_return_status VARCHAR2(1);
2869 l_terrrsc_tbl jtf_territory_resource_pvt.terrresource_tbl_type;
2870 l_terrrsc_access_tbl jtf_territory_resource_pvt.terrrsc_access_tbl_type;
2871 l_terrrsc_out_tbl jtf_territory_resource_pvt.terrresource_out_tbl_type;
2872 l_terrrsc_access_out_tbl jtf_territory_resource_pvt.terrrsc_access_out_tbl_type;
2873 --
2874
2875 BEGIN
2876 -- Standard Start of API savepoint
2877 SAVEPOINT update_terrresource_pub;
2878
2879 -- Standard call to check for call compatibility.
2880 IF NOT fnd_api.compatible_api_call (
2881 l_api_version_number,
2882 p_api_version_number,
2883 l_api_name,
2884 g_pkg_name
2885 )
2886 THEN
2887 RAISE fnd_api.g_exc_unexpected_error;
2888 END IF;
2889
2890 -- Initialize message list if p_init_msg_list is set to TRUE.
2891 IF fnd_api.to_boolean (p_init_msg_list)
2892 THEN
2893 fnd_msg_pub.initialize;
2894 END IF;
2895
2896 -- Debug Message
2897 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2898 THEN
2899 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
2900 fnd_message.set_name ('PROC_NAME', l_api_name);
2901 fnd_msg_pub.add;
2902 END IF;
2903
2904 -- Initialize API return status to success
2905 x_return_status := fnd_api.g_ret_sts_success;
2906 --
2907 -- API body
2908 --
2909 -- Convert incomming data from public to private Tbl format
2910 convert_terrrsc_pubtopvt (
2911 p_terrrsc_tbl => p_terrrsc_tbl,
2912 x_terrrsc_tbl => l_terrrsc_tbl
2913 );
2914 convert_terrrscacc_pubtopvt (
2915 p_terrrscacc_tbl => p_terrrsc_access_tbl,
2916 x_terrrscacc_tbl => l_terrrsc_access_tbl
2917 );
2918 --
2919 jtf_territory_resource_pvt.update_terrresource (
2920 p_api_version_number => 1.0,
2921 p_init_msg_list => fnd_api.g_false,
2922 p_commit => fnd_api.g_false,
2923 p_validation_level => fnd_api.g_valid_level_full,
2924 x_return_status => l_return_status,
2925 x_msg_count => x_msg_count,
2926 x_msg_data => x_msg_data,
2927 p_terrrsc_tbl => l_terrrsc_tbl,
2928 p_terrrsc_access_tbl => l_terrrsc_access_tbl,
2929 x_terrrsc_out_tbl => l_terrrsc_out_tbl,
2930 x_terrrsc_access_out_tbl => l_terrrsc_access_out_tbl
2931 );
2932 convert_terrrscout_pvttopub (
2933 p_terrrscout_tbl => l_terrrsc_out_tbl,
2934 x_terrrscout_tbl => x_terrrsc_out_tbl
2935 );
2936 convert_terrscaccout_pvttopub (
2937 p_terrrscaccout_tbl => l_terrrsc_access_out_tbl,
2938 x_terrrscaccout_tbl => x_terrrsc_access_out_tbl
2939 );
2940
2941 x_return_status := l_return_status;
2942 --
2943 IF l_return_status <> fnd_api.g_ret_sts_success
2944 THEN
2945 RAISE fnd_api.g_exc_error;
2946 END IF;
2947
2948 -- Debug Message
2949 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_debug_low)
2950 THEN
2951 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
2952 fnd_message.set_name ('PROC_NAME', l_api_name);
2953 fnd_msg_pub.add;
2954 END IF;
2955
2956 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2957
2958 -- Standard check for p_commit
2959 IF fnd_api.to_boolean (p_commit)
2960 THEN
2961 COMMIT WORK;
2962 END IF;
2963 --
2964 EXCEPTION
2965 --
2966 WHEN fnd_api.g_exc_error
2967 THEN
2968 ROLLBACK TO update_terrresource_pub;
2969 x_return_status := fnd_api.g_ret_sts_error;
2970 fnd_msg_pub.count_and_get (
2971 p_count => x_msg_count,
2972 p_data => x_msg_data
2973 );
2974 WHEN fnd_api.g_exc_unexpected_error
2975 THEN
2976 ROLLBACK TO update_terrresource_pub;
2977 x_return_status := fnd_api.g_ret_sts_unexp_error;
2978 fnd_msg_pub.count_and_get (
2979 p_count => x_msg_count,
2980 p_data => x_msg_data
2981 );
2982
2983 WHEN OTHERS
2984 THEN
2985 ROLLBACK TO update_terrresource_pub;
2986 x_return_status := fnd_api.g_ret_sts_unexp_error;
2987
2988 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2989 THEN
2990 fnd_msg_pub.add_exc_msg (
2991 'Others exception in Update_TerrResource PUB' || SQLERRM
2992 );
2993
2994 END IF;
2995 fnd_msg_pub.count_and_get (
2996 p_count => x_msg_count,
2997 p_data => x_msg_data
2998 );
2999 --
3000
3001 END update_terrresource;
3002 --
3003
3004 END jtf_territory_pub;