1 PACKAGE BODY JTF_PhysicalMap_GRP AS
2 /* $Header: JTFGPSLB.pls 115.6 2004/07/09 18:50:12 applrt ship $ */
3 g_yes VARCHAR2(1) := 'Y';
4 g_no VARCHAR2(1) := 'N';
5
6 ---------------------------------------------------------------------
7 -- PROCEDURE
8 -- save_physicalmap
9 --
10 -- PURPOSE
11 -- Save a collection of physical_site_language mappings for a physical
12 -- attachment and one mini-site
13 --
14 -- PARAMETERS
15 -- p_attachment_id: the associated attachment
16 -- p_msite_id: the associated mini-site
17 -- p_language_code_tbl: A collection of associated language codes for the
18 -- the given physical attachment and mini-site
19 --
20 -- NOTES
21 -- 1. Raises an exception if the api_version is not valid
22 -- 2. Raises an exception if the attachment or mini-site doesn't exist
23 -- 3. First delete all the records in JTF_LGL_PHYS_MAP for the given
24 -- attachment and mini-site; then insert a record into
25 -- JTF_LGL_PHYS_MAP for each language_code in
26 -- p_language_code_tbl which is supported at the given site
27 -- 4. Raises an exception if there is any duplicate mappings defined
28 -- for the same logical deliverable - roll back
29 -- 5. Ignore the non-existent or the non-supported language code
30 -- for the given site; pass out a warning message
31 -- 6. Raise an exception for any other errors
32 ---------------------------------------------------------------------
33 PROCEDURE save_physicalmap (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
36 p_commit IN VARCHAR2 := FND_API.g_false,
37 x_return_status OUT VARCHAR2,
38 x_msg_count OUT NUMBER,
39 x_msg_data OUT VARCHAR2,
40 p_attachment_id IN NUMBER,
41 p_msite_id IN NUMBER,
42 p_language_code_tbl IN LANGUAGE_CODE_TBL_TYPE) IS
43
44 l_api_name CONSTANT VARCHAR2(30) := 'save_physicalmap';
45 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
46
47 l_msite_type VARCHAR2(10) := 'SITE';
48 l_default_msite VARCHAR2(1) := g_no;
49 l_default_lang VARCHAR2(1) := g_no;
50
51 l_deliverable_id NUMBER;
52 l_msite_id NUMBER;
53 l_language_code VARCHAR2(4);
54 l_lgl_phys_map_id NUMBER;
55
56 l_index NUMBER;
57
58 CURSOR lgl_phys_map_id_seq IS
59 SELECT JTF_DSP_LGL_PHYS_MAP_S1.NEXTVAL FROM DUAL;
60
61 BEGIN
62
63 -- Standard start of API savepoint
64 SAVEPOINT save_physicalmap_grp;
65
66 -- Standard call to check for call compatibility
67 IF NOT FND_API.compatible_api_call(
68 g_api_version,
69 p_api_version,
70 l_api_name,
71 g_pkg_name
72 ) THEN
73 RAISE FND_API.g_exc_unexpected_error;
74 END IF;
75
76 -- Initialize message list if p_init_msg_list is set to TRUE
77 IF FND_API.to_boolean(p_init_msg_list) THEN
78 FND_MSG_PUB.initialize;
79 END IF;
80
81 -- Initialize API rturn status to success
82 x_return_status := FND_API.g_ret_sts_success;
83
84 -- API body
85
86 -- Check if the attachment id exists
87 l_deliverable_id := JTF_DSPMGRVALIDATION_GRP.check_attachment_deliverable(
88 p_attachment_id);
89 IF l_deliverable_id IS NULL THEN
90 RAISE FND_API.g_exc_error;
91 END IF;
92
93 -- Check if the mini-site id exists
94 l_msite_id := p_msite_id;
95 IF p_msite_id IS NOT NULL THEN
96 IF NOT JTF_DSPMGRVALIDATION_GRP.check_msite_exists(p_msite_id) THEN
97 RAISE FND_API.g_exc_error;
98 END IF;
99
100 -- Delete all the existing mappings
101 DELETE FROM JTF_DSP_LGL_PHYS_MAP
102 WHERE ( (attachment_id = p_attachment_id)
103 AND (default_site = l_default_msite)
104 AND (msite_id = p_msite_id) );
105 ELSE
106 l_msite_type := 'ALLSITES';
107 l_default_msite := g_yes;
108
109 l_msite_id := JTF_DSPMGRVALIDATION_GRP.check_master_msite_exists;
110 IF l_msite_id IS NULL THEN
111 RAISE FND_API.g_exc_error;
112 END IF;
113
114 -- Delete all the existing mappings
115 DELETE FROM JTF_DSP_LGL_PHYS_MAP
116 WHERE ( (attachment_id = p_attachment_id)
117 AND (default_site = g_yes) );
118 END IF;
119
120 -- Check if the language code exists or supported at the given mini-site
121
122 IF p_language_code_tbl IS NOT NULL THEN
123 l_language_code := TRIM(p_language_code_tbl(1));
124 IF l_language_code IS NULL THEN
125 -- temporarily using US. change later
126 l_language_code := 'US';
127 l_default_lang := g_yes;
128
129 OPEN lgl_phys_map_id_seq;
130 FETCH lgl_phys_map_id_seq INTO l_lgl_phys_map_id;
131 CLOSE lgl_phys_map_id_seq;
132
133 INSERT INTO JTF_DSP_LGL_PHYS_MAP (
134 lgl_phys_map_id,
135 object_version_number,
136 last_update_date,
137 last_updated_by,
138 creation_date,
139 created_by,
140 last_update_login,
141 msite_id,
142 language_code,
143 attachment_id,
144 item_id,
145 default_site,
146 default_language
147 ) VALUES (
148 l_lgl_phys_map_id,
149 1,
150 SYSDATE,
151 FND_GLOBAL.user_id,
152 SYSDATE,
153 FND_GLOBAL.user_id,
154 FND_GLOBAL.login_id,
155 l_msite_id,
156 l_language_code,
157 p_attachment_id,
158 l_deliverable_id,
159 l_default_msite,
160 l_default_lang);
161
162 ELSE
163 FOR l_index IN 1..p_language_code_tbl.COUNT LOOP
164 BEGIN
165
166 SAVEPOINT save_one_physicalmap_grp;
167
168 -- Check if the language is supported at the given site
169 IF (l_msite_type = 'SITE') THEN
170 IF NOT JTF_DSPMGRVALIDATION_GRP.check_language_supported(
171 p_msite_id,
172 p_language_code_tbl(l_index)) THEN
173 RAISE FND_API.g_exc_error;
174 END IF;
175 END IF;
176
177 OPEN lgl_phys_map_id_seq;
178 FETCH lgl_phys_map_id_seq INTO l_lgl_phys_map_id;
179 CLOSE lgl_phys_map_id_seq;
180
181 INSERT INTO JTF_DSP_LGL_PHYS_MAP (
182 lgl_phys_map_id,
183 object_version_number,
184 last_update_date,
185 last_updated_by,
186 creation_date,
187 created_by,
188 last_update_login,
189 msite_id,
190 language_code,
191 attachment_id,
192 item_id,
193 default_site,
194 default_language
195 ) VALUES (
196 l_lgl_phys_map_id,
197 1,
198 SYSDATE,
199 FND_GLOBAL.user_id,
200 SYSDATE,
201 FND_GLOBAL.user_id,
202 FND_GLOBAL.login_id,
203 l_msite_id,
204 p_language_code_tbl(l_index),
205 p_attachment_id,
206 l_deliverable_id,
207 l_default_msite,
208 l_default_lang
209 );
210
211 EXCEPTION
212
213 WHEN FND_API.g_exc_error THEN
214 ROLLBACK TO save_one_physicalmap_grp;
215 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
216 x_return_status := FND_API.g_ret_sts_error;
217 END IF;
218
219 WHEN dup_val_on_index THEN
220 ROLLBACK TO save_one_physicalmap_grp;
221 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
222 x_return_status := FND_API.g_ret_sts_error;
223 END IF;
224 IF FND_MSG_PUB.check_msg_level(
225 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
226 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
227
228 IF l_default_msite = g_yes THEN
229 FND_MESSAGE.set_name(
230 'JTF',
231 'JTF_DSP_PHYSMAP_ALL_LNG_EXISTS');
232 FND_MESSAGE.set_token(
233 'LANG',
234 p_language_code_tbl(l_index));
235 ELSE
236 FND_MESSAGE.set_name(
237 'JTF',
238 'JTF_DSP_PHYSMAP_EXISTS');
239 FND_MESSAGE.set_token(
240 'MSITE_ID',
241 TO_CHAR(l_msite_id));
242 FND_MESSAGE.set_token(
243 'LANG',
244 p_language_code_tbl(l_index));
245 END IF;
246 FND_MESSAGE.set_token(
247 'ID',
248 TO_CHAR(l_deliverable_id));
249 FND_MSG_PUB.add;
250 END IF;
251
252 WHEN OTHERS THEN
253 ROLLBACK TO save_one_physicalmap_grp;
254 x_return_status := FND_API.g_ret_sts_unexp_error ;
255
256 IF FND_MSG_PUB.check_msg_level(
257 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
258 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
259 END IF;
260
261 END;
262 END LOOP;
263 END IF;
264
265 END IF;
266
267 -- Check if the caller requested to commit ,
268 -- If p_commit set to true, commit the transaction
269 IF FND_API.to_boolean(p_commit) THEN
270 COMMIT;
271 END IF;
272
273 -- Standard call to get message count and if count is 1, get message info
274 FND_MSG_PUB.count_and_get(
275 p_encoded => FND_API.g_false,
276 p_count => x_msg_count,
277 p_data => x_msg_data
278 );
279
280
281 EXCEPTION
282
283 WHEN FND_API.g_exc_error THEN
284 ROLLBACK TO save_physicalmap_grp;
285 x_return_status := FND_API.g_ret_sts_error;
286 FND_MSG_PUB.count_and_get(
287 p_encoded => FND_API.g_false,
288 p_count => x_msg_count,
289 p_data => x_msg_data
290 );
291
292 WHEN FND_API.g_exc_unexpected_error THEN
293 ROLLBACK TO save_physicalmap_grp;
294 x_return_status := FND_API.g_ret_sts_unexp_error;
295 FND_MSG_PUB.count_and_get(
296 p_encoded => FND_API.g_false,
297 p_count => x_msg_count,
298 p_data => x_msg_data
299 );
300
301 WHEN dup_val_on_index THEN
302 ROLLBACK TO save_physicalmap_grp;
303 x_return_status := FND_API.g_ret_sts_error;
304 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
305 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
306
307 IF l_default_msite = g_no THEN
308 FND_MESSAGE.set_name('JTF', 'JTF_DSP_PHYSMAP_ALL_ALL_EXISTS');
309 ELSE
310 FND_MESSAGE.set_name('JTF', 'JTF_DSP_PHYSMAP_STE_ALL_EXISTS');
311 FND_MESSAGE.set_token('MSITE_ID', TO_CHAR(l_msite_id));
312 END IF;
313 FND_MESSAGE.set_token('ID', TO_CHAR(l_deliverable_id));
314 FND_MSG_PUB.add;
315 END IF;
316
317 FND_MSG_PUB.count_and_get(
318 p_encoded => FND_API.g_false,
319 p_count => x_msg_count,
320 p_data => x_msg_data
321 );
322
323 WHEN OTHERS THEN
324 ROLLBACK TO save_physicalmap_grp;
325 x_return_status := FND_API.g_ret_sts_unexp_error ;
326
327 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
328 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
329 END IF;
330
331 FND_MSG_PUB.count_and_get(
332 p_encoded => FND_API.g_false,
333 p_count => x_msg_count,
334 p_data => x_msg_data
335 );
336
337 END save_physicalmap;
338
339
340 ---------------------------------------------------------------------
341 -- PROCEDURE
342 -- save_physicalmap
343 --
344 -- PURPOSE
345 -- Save a collection of physical_site_language mappings for a physical
346 -- attachment and multiple mini-sites
347 --
348 -- PARAMETERS
349 -- p_attachment_id: the associated attachment
350 -- p_msite_lang_tbl: a collection of records of associated mini-site and
351 -- the number of language codes selected for this mini-site
352 -- p_language_code_tbl: A collection of associated language codes for the
353 -- the given physical attachment and mini-sites. The language codes
354 -- are grouped by associted mini-site and keep in the same order as
355 -- p_msite_lang_tbl
356 --
357 -- NOTES
358 -- 1. Raises an exception if the api_version is not valid
359 -- 2. Raises an exception if the attachment or mini-sites doesn't exist
360 -- 3. First delete all the records in JTF_LGL_PHYS_MAP for the given
361 -- attachment and mini-sites; then insert a record into
362 -- JTF_LGL_PHYS_MAP for each language_code in
363 -- p_language_code_tbl which is supported at the given site
364 -- 4. Raises an exception if there is any duplicate mappings defined
365 -- for the same logical deliverable - roll back
366 -- 5. Ignore the non-existent or the non-supported language code
367 -- for the given site; pass out a warning message
368 -- 6. Raise an exception for any other errors
369 ---------------------------------------------------------------------
370 PROCEDURE save_physicalmap (
371 p_api_version IN NUMBER,
372 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
373 p_commit IN VARCHAR2 := FND_API.g_false,
374 x_return_status OUT VARCHAR2,
375 x_msg_count OUT NUMBER,
376 x_msg_data OUT VARCHAR2,
377 p_attachment_id IN NUMBER,
378 p_msite_lang_tbl IN MSITE_LANG_TBL_TYPE,
379 p_language_code_tbl IN LANGUAGE_CODE_TBL_TYPE) IS
380
381 l_api_name CONSTANT VARCHAR2(30) := 'save_physicalmap';
382 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
383
384 l_deliverable_id NUMBER;
385
386 l_language_code_tbl LANGUAGE_CODE_TBL_TYPE;
387 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
388
389 l_index NUMBER;
390 l_index1 NUMBER;
391 l_count NUMBER := 0;
392
393 -- l_msg_data VARCHAR2(200);
394
395 BEGIN
396
397 -- Standard start of API savepoint
398 SAVEPOINT save_physicalmap_grp;
399
400 -- Standard call to check for call compatibility
401 IF NOT FND_API.compatible_api_call(
402 g_api_version,
403 p_api_version,
404 l_api_name,
405 g_pkg_name
406 ) THEN
407 RAISE FND_API.g_exc_unexpected_error;
408 END IF;
409
410 -- Initialize message list if p_init_msg_list is set to TRUE
411 IF FND_API.to_boolean(p_init_msg_list) THEN
412 FND_MSG_PUB.initialize;
413 END IF;
414
415 -- Initialize API rturn status to success
416 x_return_status := FND_API.g_ret_sts_success;
417
418 -- API body
419
420 -- Check if the input parameters are valid
421 IF p_msite_lang_tbl IS NOT NULL THEN
422 l_count := 0;
423 FOR l_index1 IN 1..p_msite_lang_tbl.COUNT LOOP
424 IF p_msite_lang_tbl(l_index1).lang_count IS NULL
425 OR p_msite_lang_tbl(l_index1).lang_count < 0 THEN
426 RAISE FND_API.g_exc_unexpected_error;
427 END IF;
428
429 l_count := l_count + p_msite_lang_tbl(l_index1).lang_count;
430 END LOOP;
431
432 IF l_count > 0 THEN
433 IF p_language_code_tbl IS NULL OR
434 l_count <> p_language_code_tbl.COUNT THEN
435 RAISE FND_API.g_exc_unexpected_error;
436 END IF;
437 END IF;
438
439 ELSE
440 RETURN;
441
442 END IF;
443
444 -- Check if the attachment id exists
445 l_deliverable_id := JTF_DSPMGRVALIDATION_GRP.check_attachment_deliverable(
446 p_attachment_id);
447 IF l_deliverable_id IS NULL THEN
448 RAISE FND_API.g_exc_error;
449 END IF;
450
451 l_count := 0;
452 FOR l_index1 IN 1..p_msite_lang_tbl.COUNT LOOP
453
454 l_return_status := FND_API.g_ret_sts_success;
455 l_language_code_tbl := NULL;
456
457 IF p_msite_lang_tbl(l_index1).lang_count > 0 THEN
458 /*
459 l_language_code_tbl := p_language_code_tbl;
460 l_language_code_tbl.DELETE(1, l_count);
461 IF l_language_code_tbl.COUNT >
462 p_msite_lang_tbl(l_index1).lang_count THEN
463 l_language_code_tbl.DELETE(
464 p_msite_lang_tbl(l_index1).lang_count + 1,
465 l_language_code_tbl.COUNT);
466 END IF;
467 */
468
469 l_language_code_tbl := LANGUAGE_CODE_TBL_TYPE(
470 p_language_code_tbl(l_count + 1));
471
472 IF p_msite_lang_tbl(l_index1).lang_count > 1 THEN
473 l_language_code_tbl.EXTEND(
474 p_msite_lang_tbl(l_index1).lang_count - 1);
475 FOR l_index IN 2..p_msite_lang_tbl(l_index1).lang_count LOOP
476 l_language_code_tbl(l_index)
477 := p_language_code_tbl(l_count + l_index);
478 END LOOP;
479 END IF;
480
481 l_count := l_count + p_msite_lang_tbl(l_index1).lang_count;
482
483 END IF;
484
485 save_physicalmap(
486 p_api_version => p_api_version,
487 x_return_status => l_return_status,
488 x_msg_count => x_msg_count,
489 x_msg_data => x_msg_data,
490 p_attachment_id => p_attachment_id,
491 p_msite_id => p_msite_lang_tbl(l_index1).msite_id,
492 p_language_code_tbl => l_language_code_tbl
493 );
494
495 IF l_return_status <> FND_API.g_ret_sts_success THEN
496 x_return_status := l_return_status;
497 END IF;
498
499 /*
500 if p_msite_lang_tbl(l_index1).msite_id IS NULL THEN
501 l_msg_data := l_msg_data || ' null';
502 ELSE
503 l_msg_data := l_msg_data || p_msite_lang_tbl(l_index1).msite_id;
504 END IF;
505
506 l_msg_data := l_msg_data || ' ' || l_language_code_tbl.COUNT;
507 FOR l_index IN 1..l_language_code_tbl.COUNT LOOP
508 -- null;
509 l_msg_data := l_msg_data || ' ' || l_language_code_tbl(l_index);
510 -- l_msg_data := l_msg_data || ' ' || p_language_code_tbl(l_index);
511 END LOOP;
512 */
513
514 END LOOP;
515
516 /*
517 x_msg_data := l_msg_data;
518 return;
519 */
520
521 -- Check if the caller requested to commit ,
522 -- If p_commit set to true, commit the transaction
523 IF FND_API.to_boolean(p_commit) THEN
524 COMMIT;
525 END IF;
526
527 -- Standard call to get message count and if count is 1, get message info
528 FND_MSG_PUB.count_and_get(
529 p_encoded => FND_API.g_false,
530 p_count => x_msg_count,
531 p_data => x_msg_data
532 );
533
534 EXCEPTION
535
536 WHEN FND_API.g_exc_error THEN
537 ROLLBACK TO save_physicalmap_grp;
538 x_return_status := FND_API.g_ret_sts_error;
539 FND_MSG_PUB.count_and_get(
540 p_encoded => FND_API.g_false,
541 p_count => x_msg_count,
542 p_data => x_msg_data
543 );
544
545 WHEN FND_API.g_exc_unexpected_error THEN
546 ROLLBACK TO save_physicalmap_grp;
547 x_return_status := FND_API.g_ret_sts_unexp_error;
548 FND_MSG_PUB.count_and_get(
549 p_encoded => FND_API.g_false,
550 p_count => x_msg_count,
551 p_data => x_msg_data
552 );
553
554 WHEN OTHERS THEN
555 ROLLBACK TO save_physicalmap_grp;
556 x_return_status := FND_API.g_ret_sts_unexp_error ;
557
558 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
559 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
560 END IF;
561
562 FND_MSG_PUB.count_and_get(
563 p_encoded => FND_API.g_false,
564 p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567
568 END save_physicalmap;
569
570
571 --------------------------------------------------------------------
572 -- PROCEDURE
573 -- delete_physicalmap
574 --
575 -- PURPOSE
576 -- To delete a collection of physical_site_language mappings
577 --
578 -- PARAMETERS
579 -- p_lgl_phys_map_id_tbl : A collection of physical_site_language mappings
580 -- to be deleted
581 --
582 -- NOTES
583 -- 1. Deletes all the mappings in the table based on lgl_phys_map_id
584 -- 2. Ignore the non-existing physical_site_language mappings; pass out
585 -- a warning message
586 -- 3. Raise an exception for any other errors
587 --------------------------------------------------------------------
588 PROCEDURE delete_physicalmap(
589 p_api_version IN NUMBER,
590 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
591 p_commit IN VARCHAR2 := FND_API.g_false,
592 x_return_status OUT VARCHAR2,
593 x_msg_count OUT NUMBER,
594 x_msg_data OUT VARCHAR2,
595 p_lgl_phys_map_id_tbl IN LGL_PHYS_MAP_ID_TBL_TYPE) IS
596
597 l_api_name CONSTANT VARCHAR2(30) := 'delete_physicalmap';
598 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
599
600 l_index NUMBER;
601
602 BEGIN
603
604 -- Standard start of API savepoint
605 SAVEPOINT delete_physicalmap_grp;
606
607 -- Standard call to check for call compatibility
608 IF NOT FND_API.compatible_api_call(
609 g_api_version,
610 p_api_version,
611 l_api_name,
612 g_pkg_name
613 ) THEN
614 RAISE FND_API.g_exc_unexpected_error;
615 END IF;
616
617 -- Initialize message list if p_init_msg_list is set to TRUE
618 IF FND_API.to_boolean(p_init_msg_list) THEN
619 FND_MSG_PUB.initialize;
620 END IF;
621
622 -- Initialize API rturn status to success
623 x_return_status := FND_API.g_ret_sts_success;
624
625 -- API body
626
627 IF p_lgl_phys_map_id_tbl IS NOT NULL THEN
628 FOR l_index IN 1..p_lgl_phys_map_id_tbl.COUNT LOOP
629 BEGIN
630
631 -- Check if the physicalMap id exists
632 IF NOT JTF_DSPMGRVALIDATION_GRP.check_physicalmap_exists(
633 p_lgl_phys_map_id_tbl(l_index)) THEN
634 RAISE FND_API.g_exc_error;
635 END IF;
636
637 DELETE FROM JTF_DSP_LGL_PHYS_MAP
638 WHERE lgl_phys_map_id = p_lgl_phys_map_id_tbl(l_index);
639 IF SQL%NOTFOUND THEN
640 -- RAISE JTF_DSPMGRVALIDATION_GRP.physmap_not_exists_exception;
641 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
642 FND_MESSAGE.set_name('JTF', 'JTF_DSP_PHYSMAP_NOT_EXISTS');
643 FND_MESSAGE.set_token(
644 'ID',
645 TO_CHAR(p_lgl_phys_map_id_tbl(l_index)));
646 FND_MSG_PUB.add;
647 END IF;
648 RAISE FND_API.g_exc_error;
649 END IF;
650
651 EXCEPTION
652
653 WHEN FND_API.g_exc_error THEN
654 /*
655 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
656 x_return_status := FND_API.g_ret_sts_error;
657 ENF IF;
658 */
659 -- only warning; no error status
660 NULL;
661
662 /*
663 WHEN JTF_DSPMGRVALIDATION_GRP.physmap_not_exists_exception THEN
664 -- only warning; no error
665 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
666 FND_MESSAGE.set_name('JTF', 'JTF_DSP_PHYSMAP_NOT_EXISTS');
667 FND_MESSAGE.set_token(
668 'ID',
669 TO_CHAR(p_lgl_phys_map_id_tbl(l_index)));
670 FND_MSG_PUB.add;
671 END IF;
672 */
673
674 WHEN OTHERS THEN
675 x_return_status := FND_API.g_ret_sts_unexp_error ;
676 IF FND_MSG_PUB.check_msg_level(
677 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
678 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
679 END IF;
680
681 END;
682 END LOOP;
683 END IF;
684
685 -- Check if the caller requested to commit ,
686 -- If p_commit set to true, commit the transaction
687 IF FND_API.to_boolean(p_commit) THEN
688 COMMIT;
689 END IF;
690
691 -- Standard call to get message count and if count is 1, get message info
692 FND_MSG_PUB.count_and_get(
693 p_encoded => FND_API.g_false,
694 p_count => x_msg_count,
695 p_data => x_msg_data
696 );
697
698 EXCEPTION
699
700 WHEN FND_API.g_exc_error THEN
701 ROLLBACK TO delete_physicalmap_grp;
702 x_return_status := FND_API.g_ret_sts_error;
703 FND_MSG_PUB.count_and_get(
704 p_encoded => FND_API.g_false,
705 p_count => x_msg_count,
706 p_data => x_msg_data
707 );
708
709 WHEN FND_API.g_exc_unexpected_error THEN
710 ROLLBACK TO delete_physicalmap_grp;
711 x_return_status := FND_API.g_ret_sts_unexp_error ;
712 FND_MSG_PUB.count_and_get(
713 p_encoded => FND_API.g_false,
714 p_count => x_msg_count,
715 p_data => x_msg_data
716 );
717
718 WHEN OTHERS THEN
719 ROLLBACK TO delete_physicalmap_grp;
720 x_return_status := FND_API.g_ret_sts_unexp_error ;
721
722 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
723 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
724 END IF;
725
726 FND_MSG_PUB.count_and_get(
727 p_encoded => FND_API.g_false,
728 p_count => x_msg_count,
729 p_data => x_msg_data
730 );
731
732 END delete_physicalmap;
733
734
735 -- PROCEDURE
736 -- delete_attachment
737 --
738 -- PURPOSE
739 -- To delete all the physical_site_language_mappings for the given attachment
740 --
741 -- PARAMETERS
742 -- p_attachment_id : ID of the associated physical attachment
743 --
744 -- NOTES
745 -- 1. Deletes all the mappings associated with the physical attachment
746 -- 2. Raise an exception for any other errors
747 --------------------------------------------------------------------
748 PROCEDURE delete_attachment(
749 p_attachment_id IN NUMBER) IS
750
751 l_api_name CONSTANT VARCHAR2(30) := 'delete_attachment';
752 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
753
754 BEGIN
755
756 -- Standard start of API savepoint
757 SAVEPOINT delete_attachment_grp;
758
759 -- API body
760
761 DELETE FROM JTF_DSP_LGL_PHYS_MAP
762 WHERE attachment_id = p_attachment_id;
763
764 EXCEPTION
765
766 WHEN OTHERS THEN
767 ROLLBACK TO delete_attachment_grp;
768
769 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
770 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
771 END IF;
772
773 END delete_attachment;
774
775
776 -- PROCEDURE
777 -- delete_deliverable
778 --
779 -- PURPOSE
780 -- To delete all the physical_site_language_mappings for the given deliverable
781 --
782 -- PARAMETERS
783 -- p_deliverable_id: ID of the associated deliverable
784 --
785 -- NOTES
786 -- 1. Delete all the mappings associated with the deliverable
787 -- 2. Raise an exception for any other errors
788 --------------------------------------------------------------------
789 PROCEDURE delete_deliverable(
790 p_deliverable_id IN NUMBER) IS
791
792 l_api_name CONSTANT VARCHAR2(30) := 'delete_deliverable';
793 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
794
795 BEGIN
796
797 -- Standard start of API savepoint
798 SAVEPOINT delete_deliverable_grp;
799
800 -- API body
801
802 DELETE FROM JTF_DSP_LGL_PHYS_MAP
803 WHERE item_id = p_deliverable_id;
804
805 EXCEPTION
806
807 WHEN OTHERS THEN
808 ROLLBACK TO delete_deliverable_grp;
809
810 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
811 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
812 END IF;
813
814 END delete_deliverable;
815
816
817 -- PROCEDURE
818 -- delete_msite
819 --
820 -- PURPOSE
821 -- To delete all the physical_site_language_mappings for the given mini-site
822 --
823 -- PARAMETERS
824 -- p_msite_id: ID of the associated mini-site
825 --
826 -- NOTES
827 -- 1. Delete all the mappings associated with the mini-site
828 -- 2. Raise an exception for any other errors
829 --------------------------------------------------------------------
830 PROCEDURE delete_msite(
831 p_msite_id IN NUMBER) IS
832
833 l_api_name CONSTANT VARCHAR2(30) := 'delete_msite';
834 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
835
836 BEGIN
837
838 -- Standard start of API savepint
839 SAVEPOINT delete_msite_grp;
840
841 -- API body
842
843 DELETE FROM JTF_DSP_LGL_PHYS_MAP
844 WHERE ( (msite_id = p_msite_id) AND (default_site = g_no) );
845
846 EXCEPTION
847
848 WHEN OTHERS THEN
849 ROLLBACK TO delete_msite_grp;
850
851 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
852 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
853 END IF;
854
855 END delete_msite;
856
857
858 -- PROCEDURE
859 -- delete_msite_language
860 --
861 -- PURPOSE
862 -- To delete all the physical_site_language_mappings involved the given
863 -- mini-site and the languages which have been de-supported at the mini-site
864 --
865 -- PARAMETERS
866 -- p_msite_id: ID of the associated mini-site
867 --
868 -- NOTES
869 -- 1. Delete all the mappings associated with the mini-site and the languages
870 -- which have been de-supported
871 -- 2. Raise an exception for any other errors
872 --------------------------------------------------------------------
873 PROCEDURE delete_msite_language(
874 p_msite_id IN NUMBER) IS
875
876 l_api_name CONSTANT VARCHAR2(30) := 'delete_msite_language';
877 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
878
879 BEGIN
880
881 -- Standard start of API savepint
882 SAVEPOINT delete_msite_language_grp;
883
884 -- API body
885
886 DELETE FROM JTF_DSP_LGL_PHYS_MAP
887 WHERE msite_id = p_msite_id
888 AND default_site = g_no
889 AND default_language = g_no
890 AND language_code NOT IN (SELECT language_code
891 FROM JTF_MSITE_LANGUAGES WHERE msite_id = p_msite_id);
892
893 EXCEPTION
894
895 WHEN OTHERS THEN
896 ROLLBACK TO delete_msite_language_grp;
897
898 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
899 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
900 END IF;
901
902 END delete_msite_language;
903
904
905 -- PROCEDURE
906 -- delete_attachment_msite
907 --
908 -- PURPOSE
909 -- To delete all the physical_site_language_mappings for the given mini-sites
910 -- and attachment
911 --
912 -- PARAMETERS
913 -- p_attachment_id: ID of the associated attachment
914 -- p_msite_id_tbl: the collction of IDs of the associated mini-sites
915 --
916 -- NOTES
917 -- 1. Delete all the mappings associated with the mini-sites and attachment
918 -- 2. Raise an exception for any other errors
919 PROCEDURE delete_attachment_msite(
920 p_api_version IN NUMBER,
921 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
922 p_commit IN VARCHAR2 := FND_API.g_false,
923 x_return_status OUT VARCHAR2,
924 x_msg_count OUT NUMBER,
925 x_msg_data OUT VARCHAR2,
926 p_attachment_id IN NUMBER,
927 p_msite_id_tbl IN MSITE_ID_TBL_TYPE) IS
928
929 l_api_name CONSTANT VARCHAR2(30) := 'delete_attachment_msite';
930 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
931
932 BEGIN
933
934 -- Standard start of API savepoint
935 SAVEPOINT delete_attachment_msite_grp;
936
937 -- Standard call to check for call compatibility
938 IF NOT FND_API.compatible_api_call(
939 g_api_version,
940 p_api_version,
941 l_api_name,
942 g_pkg_name
943 ) THEN
944 RAISE FND_API.g_exc_unexpected_error;
945 END IF;
946
947 -- Initialize message list if p_init_msg_list is set to TRUE
948 IF FND_API.to_boolean(p_init_msg_list) THEN
949 FND_MSG_PUB.initialize;
950 END IF;
951
952 -- Initialize API rturn status to success
953 x_return_status := FND_API.g_ret_sts_success;
954
955 -- API body
956
957 IF p_msite_id_tbl IS NOT NULL THEN
958 FOR l_index IN 1..p_msite_id_tbl.COUNT LOOP
959 BEGIN
960
961 SAVEPOINT delete_one_ath_msite_grp;
962
963 -- Check if it is all-sites
964 IF p_msite_id_tbl(l_index) IS NULL THEN
965 DELETE FROM JTF_DSP_LGL_PHYS_MAP
966 WHERE ( (attachment_id = p_attachment_id)
967 AND (default_site = g_yes) );
968 ELSE
969 DELETE FROM JTF_DSP_LGL_PHYS_MAP
970 WHERE ( (attachment_id = p_attachment_id)
971 AND (msite_id = p_msite_id_tbl(l_index))
972 AND (default_site = g_no) );
973 END IF;
974
975 EXCEPTION
976
977 WHEN OTHERS THEN
978 ROLLBACK TO delete_one_ath_msite_grp;
979 x_return_status := FND_API.g_ret_sts_unexp_error ;
980 IF FND_MSG_PUB.check_msg_level(
981 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
982 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
983 END IF;
984
985 END;
986 END LOOP;
987 END IF;
988
989 -- Check if the caller requested to commit ,
990 -- If p_commit set to true, commit the transaction
991 IF FND_API.to_boolean(p_commit) THEN
992 COMMIT;
993 END IF;
994
995 -- Standard call to get message count and if count is 1, get message info
996 FND_MSG_PUB.count_and_get(
997 p_encoded => FND_API.g_false,
998 p_count => x_msg_count,
999 p_data => x_msg_data
1000 );
1001
1002 EXCEPTION
1003
1004 WHEN FND_API.g_exc_error THEN
1005 ROLLBACK TO delete_attachment_msite_grp;
1006 x_return_status := FND_API.g_ret_sts_error;
1007 FND_MSG_PUB.count_and_get(
1008 p_encoded => FND_API.g_false,
1009 p_count => x_msg_count,
1010 p_data => x_msg_data
1011 );
1012
1013 WHEN FND_API.g_exc_unexpected_error THEN
1014 ROLLBACK TO delete_attachment_msite_grp;
1015 x_return_status := FND_API.g_ret_sts_unexp_error ;
1016 FND_MSG_PUB.count_and_get(
1017 p_encoded => FND_API.g_false,
1018 p_count => x_msg_count,
1019 p_data => x_msg_data
1020 );
1021
1022 WHEN OTHERS THEN
1023 ROLLBACK TO delete_attachment_msite_grp;
1024 x_return_status := FND_API.g_ret_sts_unexp_error ;
1025
1026 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1027 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1028 END IF;
1029
1030 FND_MSG_PUB.count_and_get(
1031 p_encoded => FND_API.g_false,
1032 p_count => x_msg_count,
1033 p_data => x_msg_data
1034 );
1035
1036
1037 END delete_attachment_msite;
1038
1039
1040 -- PROCEDURE
1041 -- delete_dlv_all_all
1042 --
1043 -- PURPOSE
1044 -- To delete the all-site and all-language mappings for the given deliverable
1045 --
1046 -- PARAMETERS
1047 -- p_deliverable_id: ID of the associated deliverable
1048 --
1049 -- NOTES
1050 -- 1. Delete the all-site and all-language mappings for the given deliverable
1051 -- 2. Raise an exception for any other errors
1052 PROCEDURE delete_dlv_all_all(
1053 p_deliverable_id IN NUMBER) IS
1054
1055 l_api_name CONSTANT VARCHAR2(30) := 'delete_dlv_all_all';
1056 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1057
1058 BEGIN
1059
1060 -- Standard start of API savepoint
1061 SAVEPOINT delete_dlv_all_all_grp;
1062
1063 -- API body
1064
1065 DELETE FROM JTF_DSP_LGL_PHYS_MAP
1066 WHERE item_id = p_deliverable_id
1067 AND default_site = g_yes
1068 AND default_language = g_yes;
1069
1070 EXCEPTION
1071
1072 WHEN OTHERS THEN
1073 ROLLBACK TO delete_dlv_all_all_grp;
1074
1075 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1076 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1077 END IF;
1078
1079 END delete_dlv_all_all;
1080
1081
1082 END JTF_PhysicalMap_GRP;
[Home] [Help]