1 PACKAGE BODY IBE_PhysicalMap_GRP AS
2 /* $Header: IBEGPSLB.pls 120.2 2005/10/19 14:08:48 abhandar 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 IBE_LGL_PHYS_MAP for the given
24 -- attachment and mini-site; then insert a record into
25 -- IBE_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 NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY 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);
48 l_default_msite VARCHAR2(1);
49 l_default_lang VARCHAR2(1);
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 l_seed_data_exists Boolean := false;
58
59 CURSOR lgl_phys_map_id_seq IS
60 SELECT IBE_DSP_LGL_PHYS_MAP_S1.NEXTVAL FROM DUAL;
61
62 BEGIN
63
64 -- Standard start of API savepoint
65 SAVEPOINT save_physicalmap_grp;
66 l_msite_type := 'SITE';
67 l_default_msite := g_no;
68 l_default_lang := g_no;
69
70 -- Standard call to check for call compatibility
71 IF NOT FND_API.compatible_api_call(
72 g_api_version,
73 p_api_version,
74 l_api_name,
75 g_pkg_name
76 ) THEN
77 RAISE FND_API.g_exc_unexpected_error;
78 END IF;
79
80 -- Initialize message list if p_init_msg_list is set to TRUE
81 IF FND_API.to_boolean(p_init_msg_list) THEN
82 FND_MSG_PUB.initialize;
83 END IF;
84
85 -- Initialize API rturn status to success
86 x_return_status := FND_API.g_ret_sts_success;
87
88 -- API body
89
90 -- Check if the attachment id exists
91 l_deliverable_id := IBE_DSPMGRVALIDATION_GRP.check_attachment_deliverable(
92 p_attachment_id);
93 IF l_deliverable_id IS NULL THEN
94 RAISE FND_API.g_exc_error;
95 END IF;
96
97 -- Check if the mini-site id exists
98 l_msite_id := p_msite_id;
99 IF p_msite_id IS NOT NULL THEN
100 IF NOT IBE_DSPMGRVALIDATION_GRP.check_msite_exists(p_msite_id) THEN
101 RAISE FND_API.g_exc_error;
102 END IF;
103
104 -- Delete all the existing mappings
105 DELETE FROM IBE_DSP_LGL_PHYS_MAP
106 WHERE ( (attachment_id = p_attachment_id)
107 AND (default_site = l_default_msite)
108 AND (msite_id = p_msite_id) );
109 ELSE
110 l_msite_type := 'ALLSITES';
111 l_default_msite := g_yes;
112
113 l_msite_id := IBE_DSPMGRVALIDATION_GRP.check_master_msite_exists;
114 IF l_msite_id IS NULL THEN
115 RAISE FND_API.g_exc_error;
116 END IF;
117
118 -- Added by YAXU, check if the seeded physicalMap exists
119 IF p_language_code_tbl IS NOT NULL THEN
120 l_language_code := TRIM(p_language_code_tbl(1));
121 IF l_language_code IS NULL THEN
122 l_seed_data_exists := true;
123 BEGIN
124 SELECT lgl_phys_map_id INTO l_lgl_phys_map_id
125 FROM IBE_DSP_LGL_PHYS_MAP
126 WHERE attachment_id = p_attachment_id
127 AND default_site = g_yes
128 AND default_language = g_yes
129 AND lgl_phys_map_id < 10000;
130 EXCEPTION
131 WHEN NO_DATA_FOUND THEN
132 l_seed_data_exists := false;
133 END;
134 END IF;
135 END IF;
136
137
138 -- Delete all the existing mappings
139 IF l_seed_data_exists = false THEN -- Added by YAXU, don't delete the seeded physicalMap
140 DELETE FROM IBE_DSP_LGL_PHYS_MAP
141 WHERE ( (attachment_id = p_attachment_id)
142 AND (default_site = g_yes)
143 AND (lgl_phys_map_id > 10000) );
144 END IF;
145 END IF;
146
147 -- Check if the language code exists or supported at the given mini-site
148
149 IF p_language_code_tbl IS NOT NULL THEN
150 l_language_code := TRIM(p_language_code_tbl(1));
151 IF l_language_code IS NULL THEN
152 -- temporarily using US. change later
153 l_language_code := 'US';
154 l_default_lang := g_yes;
155
156 IF l_seed_data_exists = false THEN -- Added by YAXU, don't insert the seeded physicalMap again
157 OPEN lgl_phys_map_id_seq;
158 FETCH lgl_phys_map_id_seq INTO l_lgl_phys_map_id;
159 CLOSE lgl_phys_map_id_seq;
160
161 INSERT INTO IBE_DSP_LGL_PHYS_MAP (
162 lgl_phys_map_id,
163 object_version_number,
164 last_update_date,
165 last_updated_by,
166 creation_date,
167 created_by,
168 last_update_login,
169 msite_id,
170 language_code,
171 attachment_id,
172 item_id,
173 default_site,
174 default_language
175 ) VALUES (
176 l_lgl_phys_map_id,
177 1,
178 SYSDATE,
179 FND_GLOBAL.user_id,
180 SYSDATE,
181 FND_GLOBAL.user_id,
182 FND_GLOBAL.login_id,
183 l_msite_id,
184 l_language_code,
185 p_attachment_id,
186 l_deliverable_id,
187 l_default_msite,
188 l_default_lang);
189 END IF;
190 ELSE
191 FOR l_index IN 1..p_language_code_tbl.COUNT LOOP
192 BEGIN
193
194 SAVEPOINT save_one_physicalmap_grp;
195
196 -- Check if the language is supported at the given site
197 IF (l_msite_type = 'SITE') THEN
198 IF NOT IBE_DSPMGRVALIDATION_GRP.check_language_supported(
199 p_msite_id,
200 p_language_code_tbl(l_index)) THEN
201 RAISE FND_API.g_exc_error;
202 END IF;
203 END IF;
204
205 OPEN lgl_phys_map_id_seq;
206 FETCH lgl_phys_map_id_seq INTO l_lgl_phys_map_id;
207 CLOSE lgl_phys_map_id_seq;
208
209 INSERT INTO IBE_DSP_LGL_PHYS_MAP (
210 lgl_phys_map_id,
211 object_version_number,
212 last_update_date,
213 last_updated_by,
214 creation_date,
215 created_by,
216 last_update_login,
217 msite_id,
218 language_code,
219 attachment_id,
220 item_id,
221 default_site,
222 default_language
223 ) VALUES (
224 l_lgl_phys_map_id,
225 1,
226 SYSDATE,
227 FND_GLOBAL.user_id,
228 SYSDATE,
229 FND_GLOBAL.user_id,
230 FND_GLOBAL.login_id,
231 l_msite_id,
232 p_language_code_tbl(l_index),
233 p_attachment_id,
234 l_deliverable_id,
235 l_default_msite,
236 l_default_lang
237 );
238
239 EXCEPTION
240
241 WHEN FND_API.g_exc_error THEN
242 ROLLBACK TO save_one_physicalmap_grp;
243 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
244 x_return_status := FND_API.g_ret_sts_error;
245 END IF;
246
247 WHEN dup_val_on_index THEN
248 ROLLBACK TO save_one_physicalmap_grp;
249 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
250 x_return_status := FND_API.g_ret_sts_error;
251 END IF;
252 IF FND_MSG_PUB.check_msg_level(
253 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
254 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
255
256 IF l_default_msite = g_yes THEN
257 FND_MESSAGE.set_name(
258 'IBE',
259 'IBE_DSP_PHYSMAP_ALL_LNG_EXISTS');
260 FND_MESSAGE.set_token(
261 'LANG',
262 p_language_code_tbl(l_index));
263 ELSE
264 FND_MESSAGE.set_name(
265 'IBE',
266 'IBE_DSP_PHYSMAP_EXISTS');
267 FND_MESSAGE.set_token(
268 'MSITE_ID',
269 TO_CHAR(l_msite_id));
270 FND_MESSAGE.set_token(
271 'LANG',
272 p_language_code_tbl(l_index));
273 END IF;
274 FND_MESSAGE.set_token(
275 'ID',
276 TO_CHAR(l_deliverable_id));
277 FND_MSG_PUB.add;
278 END IF;
279
280 WHEN OTHERS THEN
281 ROLLBACK TO save_one_physicalmap_grp;
282 x_return_status := FND_API.g_ret_sts_unexp_error ;
283
284 IF FND_MSG_PUB.check_msg_level(
285 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
286 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
287 END IF;
288
289 END;
290 END LOOP;
291 END IF;
292
293 END IF;
294
295 -- Check if the caller requested to commit ,
296 -- If p_commit set to true, commit the transaction
297 IF FND_API.to_boolean(p_commit) THEN
298 COMMIT;
299 END IF;
300
301 -- Standard call to get message count and if count is 1, get message info
302 FND_MSG_PUB.count_and_get(
303 p_encoded => FND_API.g_false,
304 p_count => x_msg_count,
305 p_data => x_msg_data
306 );
307
308
309 EXCEPTION
310
311 WHEN FND_API.g_exc_error THEN
312 ROLLBACK TO save_physicalmap_grp;
313 x_return_status := FND_API.g_ret_sts_error;
314 FND_MSG_PUB.count_and_get(
315 p_encoded => FND_API.g_false,
316 p_count => x_msg_count,
317 p_data => x_msg_data
318 );
319
320 WHEN FND_API.g_exc_unexpected_error THEN
321 ROLLBACK TO save_physicalmap_grp;
322 x_return_status := FND_API.g_ret_sts_unexp_error;
323 FND_MSG_PUB.count_and_get(
324 p_encoded => FND_API.g_false,
325 p_count => x_msg_count,
326 p_data => x_msg_data
327 );
328
329 WHEN dup_val_on_index THEN
330 ROLLBACK TO save_physicalmap_grp;
331 x_return_status := FND_API.g_ret_sts_error;
332 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
333 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
334
335 --added by YAXU on 08/02/2002
336 IF l_default_msite = g_yes and l_default_lang = g_yes THEN
337 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_ALL_ALL_EXISTS');
338 END IF;
339 IF l_default_msite = g_yes and l_default_lang = g_no THEN
340 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_ALL_LNG_EXISTS');
341 FND_MESSAGE.set_token('LANG', p_language_code_tbl(l_index));
342 END IF;
343 IF l_default_msite = g_no and l_default_lang = g_yes THEN
344 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_STE_ALL_EXISTS');
345 FND_MESSAGE.set_token('MSITE_ID', TO_CHAR(l_msite_id));
346 END IF;
347 IF l_default_msite = g_no and l_default_lang = g_no THEN
348 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_EXISTS');
349 FND_MESSAGE.set_token('LANG', p_language_code_tbl(l_index));
350 FND_MESSAGE.set_token('MSITE_ID', TO_CHAR(l_msite_id));
351 END IF;
352 FND_MESSAGE.set_token('ID', TO_CHAR(l_deliverable_id));
353 FND_MSG_PUB.add;
354
355 END IF;
356
357 FND_MSG_PUB.count_and_get(
358 p_encoded => FND_API.g_false,
359 p_count => x_msg_count,
360 p_data => x_msg_data
361 );
362
363 WHEN OTHERS THEN
364 ROLLBACK TO save_physicalmap_grp;
365 x_return_status := FND_API.g_ret_sts_unexp_error ;
366
367 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
368 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
369 END IF;
370
371 FND_MSG_PUB.count_and_get(
372 p_encoded => FND_API.g_false,
373 p_count => x_msg_count,
374 p_data => x_msg_data
375 );
376
377 END save_physicalmap;
378
379
380 ---------------------------------------------------------------------
381 -- PROCEDURE
382 -- save_physicalmap
383 --
384 -- PURPOSE
385 -- Save a collection of physical_site_language mappings for a physical
386 -- attachment and multiple mini-sites
387 --
388 -- PARAMETERS
389 -- p_attachment_id: the associated attachment
390 -- p_msite_lang_tbl: a collection of records of associated mini-site and
391 -- the number of language codes selected for this mini-site
392 -- p_language_code_tbl: A collection of associated language codes for the
393 -- the given physical attachment and mini-sites. The language codes
394 -- are grouped by associted mini-site and keep in the same order as
395 -- p_msite_lang_tbl
396 --
397 -- NOTES
398 -- 1. Raises an exception if the api_version is not valid
399 -- 2. Raises an exception if the attachment or mini-sites doesn't exist
400 -- 3. First delete all the records in IBE_LGL_PHYS_MAP for the given
401 -- attachment and mini-sites; then insert a record into
402 -- IBE_LGL_PHYS_MAP for each language_code in
403 -- p_language_code_tbl which is supported at the given site
404 -- 4. Raises an exception if there is any duplicate mappings defined
405 -- for the same logical deliverable - roll back
406 -- 5. Ignore the non-existent or the non-supported language code
407 -- for the given site; pass out a warning message
408 -- 6. Raise an exception for any other errors
409 ---------------------------------------------------------------------
410 PROCEDURE save_physicalmap (
411 p_api_version IN NUMBER,
412 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
413 p_commit IN VARCHAR2 := FND_API.g_false,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2,
417 p_attachment_id IN NUMBER,
418 p_msite_lang_tbl IN MSITE_LANG_TBL_TYPE,
419 p_language_code_tbl IN LANGUAGE_CODE_TBL_TYPE) IS
420
421 l_api_name CONSTANT VARCHAR2(30) := 'save_physicalmap';
422 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
423
424 l_deliverable_id NUMBER;
425
426 l_language_code_tbl LANGUAGE_CODE_TBL_TYPE;
427 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
428
429 l_index NUMBER;
430 l_index1 NUMBER;
431 l_count NUMBER := 0;
432
433 -- l_msg_data VARCHAR2(200);
434
435 BEGIN
436
437 -- Standard start of API savepoint
438 SAVEPOINT save_physicalmap_grp;
439
440 -- Standard call to check for call compatibility
441 IF NOT FND_API.compatible_api_call(
442 g_api_version,
443 p_api_version,
444 l_api_name,
445 g_pkg_name
446 ) THEN
447 RAISE FND_API.g_exc_unexpected_error;
448 END IF;
449
450 -- Initialize message list if p_init_msg_list is set to TRUE
451 IF FND_API.to_boolean(p_init_msg_list) THEN
452 FND_MSG_PUB.initialize;
453 END IF;
454
455 -- Initialize API rturn status to success
456 x_return_status := FND_API.g_ret_sts_success;
457
458 -- API body
459
460 -- Check if the input parameters are valid
461 IF p_msite_lang_tbl IS NOT NULL THEN
462 l_count := 0;
463 FOR l_index1 IN 1..p_msite_lang_tbl.COUNT LOOP
464 IF p_msite_lang_tbl(l_index1).lang_count IS NULL
465 OR p_msite_lang_tbl(l_index1).lang_count < 0 THEN
466 RAISE FND_API.g_exc_unexpected_error;
467 END IF;
468
469 l_count := l_count + p_msite_lang_tbl(l_index1).lang_count;
470 END LOOP;
471
472 IF l_count > 0 THEN
473 IF p_language_code_tbl IS NULL OR
474 l_count <> p_language_code_tbl.COUNT THEN
475 RAISE FND_API.g_exc_unexpected_error;
476 END IF;
477 END IF;
478
479 ELSE
480 RETURN;
481
482 END IF;
483
484 -- Check if the attachment id exists
485 l_deliverable_id := IBE_DSPMGRVALIDATION_GRP.check_attachment_deliverable(
486 p_attachment_id);
487 IF l_deliverable_id IS NULL THEN
488 RAISE FND_API.g_exc_error;
489 END IF;
490
491 l_count := 0;
492 FOR l_index1 IN 1..p_msite_lang_tbl.COUNT LOOP
493
494 l_return_status := FND_API.g_ret_sts_success;
495 l_language_code_tbl := NULL;
496
497 IF p_msite_lang_tbl(l_index1).lang_count > 0 THEN
498 /*
499 l_language_code_tbl := p_language_code_tbl;
500 l_language_code_tbl.DELETE(1, l_count);
501 IF l_language_code_tbl.COUNT >
502 p_msite_lang_tbl(l_index1).lang_count THEN
503 l_language_code_tbl.DELETE(
504 p_msite_lang_tbl(l_index1).lang_count + 1,
505 l_language_code_tbl.COUNT);
506 END IF;
507 */
508
509 l_language_code_tbl := LANGUAGE_CODE_TBL_TYPE(
510 p_language_code_tbl(l_count + 1));
511
512 IF p_msite_lang_tbl(l_index1).lang_count > 1 THEN
513 l_language_code_tbl.EXTEND(
514 p_msite_lang_tbl(l_index1).lang_count - 1);
515 FOR l_index IN 2..p_msite_lang_tbl(l_index1).lang_count LOOP
516 l_language_code_tbl(l_index)
517 := p_language_code_tbl(l_count + l_index);
518 END LOOP;
519 END IF;
520
521 l_count := l_count + p_msite_lang_tbl(l_index1).lang_count;
522
523 END IF;
524
525 save_physicalmap(
526 p_api_version => p_api_version,
527 x_return_status => l_return_status,
528 x_msg_count => x_msg_count,
529 x_msg_data => x_msg_data,
530 p_attachment_id => p_attachment_id,
531 p_msite_id => p_msite_lang_tbl(l_index1).msite_id,
532 p_language_code_tbl => l_language_code_tbl
533 );
534
535 IF l_return_status <> FND_API.g_ret_sts_success THEN
536 x_return_status := l_return_status;
537 END IF;
538
539 /*
540 if p_msite_lang_tbl(l_index1).msite_id IS NULL THEN
541 l_msg_data := l_msg_data || ' null';
542 ELSE
543 l_msg_data := l_msg_data || p_msite_lang_tbl(l_index1).msite_id;
544 END IF;
545
546 l_msg_data := l_msg_data || ' ' || l_language_code_tbl.COUNT;
547 FOR l_index IN 1..l_language_code_tbl.COUNT LOOP
548 -- null;
549 l_msg_data := l_msg_data || ' ' || l_language_code_tbl(l_index);
550 -- l_msg_data := l_msg_data || ' ' || p_language_code_tbl(l_index);
551 END LOOP;
552 */
553
554 END LOOP;
555
556 /*
557 x_msg_data := l_msg_data;
558 return;
559 */
560
561 -- Check if the caller requested to commit ,
562 -- If p_commit set to true, commit the transaction
563 IF FND_API.to_boolean(p_commit) THEN
564 COMMIT;
565 END IF;
566
567 -- Standard call to get message count and if count is 1, get message info
568 FND_MSG_PUB.count_and_get(
569 p_encoded => FND_API.g_false,
570 p_count => x_msg_count,
571 p_data => x_msg_data
572 );
573
574 EXCEPTION
575
576 WHEN FND_API.g_exc_error THEN
577 ROLLBACK TO save_physicalmap_grp;
578 x_return_status := FND_API.g_ret_sts_error;
579 FND_MSG_PUB.count_and_get(
580 p_encoded => FND_API.g_false,
581 p_count => x_msg_count,
582 p_data => x_msg_data
583 );
584
585 WHEN FND_API.g_exc_unexpected_error THEN
586 ROLLBACK TO save_physicalmap_grp;
587 x_return_status := FND_API.g_ret_sts_unexp_error;
588 FND_MSG_PUB.count_and_get(
589 p_encoded => FND_API.g_false,
590 p_count => x_msg_count,
591 p_data => x_msg_data
592 );
593
594 WHEN OTHERS THEN
595 ROLLBACK TO save_physicalmap_grp;
596 x_return_status := FND_API.g_ret_sts_unexp_error ;
597
598 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
599 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
600 END IF;
601
602 FND_MSG_PUB.count_and_get(
603 p_encoded => FND_API.g_false,
604 p_count => x_msg_count,
605 p_data => x_msg_data
606 );
607
608 END save_physicalmap;
609
610
611 --------------------------------------------------------------------
612 -- PROCEDURE
613 -- delete_physicalmap
614 --
615 -- PURPOSE
616 -- To delete a collection of physical_site_language mappings
617 --
618 -- PARAMETERS
619 -- p_lgl_phys_map_id_tbl : A collection of physical_site_language mappings
620 -- to be deleted
621 --
622 -- NOTES
623 -- 1. Deletes all the mappings in the table based on lgl_phys_map_id
624 -- 2. Ignore the non-existing physical_site_language mappings; pass out
625 -- a warning message
626 -- 3. Raise an exception for any other errors
627 --------------------------------------------------------------------
628 PROCEDURE delete_physicalmap(
629 p_api_version IN NUMBER,
630 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
631 p_commit IN VARCHAR2 := FND_API.g_false,
632 x_return_status OUT NOCOPY VARCHAR2,
633 x_msg_count OUT NOCOPY NUMBER,
634 x_msg_data OUT NOCOPY VARCHAR2,
635 p_lgl_phys_map_id_tbl IN LGL_PHYS_MAP_ID_TBL_TYPE) IS
636
637 l_api_name CONSTANT VARCHAR2(30) := 'delete_physicalmap';
638 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
639
640 l_index NUMBER;
641
642 BEGIN
643
644 -- Standard start of API savepoint
645 SAVEPOINT delete_physicalmap_grp;
646
647 -- Standard call to check for call compatibility
648 IF NOT FND_API.compatible_api_call(
649 g_api_version,
650 p_api_version,
651 l_api_name,
652 g_pkg_name
653 ) THEN
654 RAISE FND_API.g_exc_unexpected_error;
655 END IF;
656
657 -- Initialize message list if p_init_msg_list is set to TRUE
658 IF FND_API.to_boolean(p_init_msg_list) THEN
659 FND_MSG_PUB.initialize;
660 END IF;
661
662 -- Initialize API rturn status to success
663 x_return_status := FND_API.g_ret_sts_success;
664
665 -- API body
666
667 IF p_lgl_phys_map_id_tbl IS NOT NULL THEN
668 FOR l_index IN 1..p_lgl_phys_map_id_tbl.COUNT LOOP
669 BEGIN
670
671 -- Check if the physicalMap id exists
672 IF NOT IBE_DSPMGRVALIDATION_GRP.check_physicalmap_exists(
673 p_lgl_phys_map_id_tbl(l_index)) THEN
674 RAISE FND_API.g_exc_error;
675 END IF;
676
677 DELETE FROM IBE_DSP_LGL_PHYS_MAP
678 WHERE lgl_phys_map_id = p_lgl_phys_map_id_tbl(l_index);
679 IF SQL%NOTFOUND THEN
680 -- RAISE IBE_DSPMGRVALIDATION_GRP.physmap_not_exists_exception;
681 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
682 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_NOT_EXISTS');
683 FND_MESSAGE.set_token(
684 'ID',
685 TO_CHAR(p_lgl_phys_map_id_tbl(l_index)));
686 FND_MSG_PUB.add;
687 END IF;
688 RAISE FND_API.g_exc_error;
689 END IF;
690
691 EXCEPTION
692
693 WHEN FND_API.g_exc_error THEN
694 /*
695 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
696 x_return_status := FND_API.g_ret_sts_error;
697 ENF IF;
698 */
699 -- only warning; no error status
700 NULL;
701
702 /*
703 WHEN IBE_DSPMGRVALIDATION_GRP.physmap_not_exists_exception THEN
704 -- only warning; no error
705 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
706 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_NOT_EXISTS');
707 FND_MESSAGE.set_token(
708 'ID',
709 TO_CHAR(p_lgl_phys_map_id_tbl(l_index)));
710 FND_MSG_PUB.add;
711 END IF;
712 */
713
714 WHEN OTHERS THEN
715 x_return_status := FND_API.g_ret_sts_unexp_error ;
716 IF FND_MSG_PUB.check_msg_level(
717 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
718 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
719 END IF;
720
721 END;
722 END LOOP;
723 END IF;
724
725 -- Check if the caller requested to commit ,
726 -- If p_commit set to true, commit the transaction
727 IF FND_API.to_boolean(p_commit) THEN
728 COMMIT;
729 END IF;
730
731 -- Standard call to get message count and if count is 1, get message info
732 FND_MSG_PUB.count_and_get(
733 p_encoded => FND_API.g_false,
734 p_count => x_msg_count,
735 p_data => x_msg_data
736 );
737
738 EXCEPTION
739
740 WHEN FND_API.g_exc_error THEN
741 ROLLBACK TO delete_physicalmap_grp;
742 x_return_status := FND_API.g_ret_sts_error;
743 FND_MSG_PUB.count_and_get(
744 p_encoded => FND_API.g_false,
745 p_count => x_msg_count,
746 p_data => x_msg_data
747 );
748
749 WHEN FND_API.g_exc_unexpected_error THEN
750 ROLLBACK TO delete_physicalmap_grp;
751 x_return_status := FND_API.g_ret_sts_unexp_error ;
752 FND_MSG_PUB.count_and_get(
753 p_encoded => FND_API.g_false,
754 p_count => x_msg_count,
755 p_data => x_msg_data
756 );
757
758 WHEN OTHERS THEN
759 ROLLBACK TO delete_physicalmap_grp;
760 x_return_status := FND_API.g_ret_sts_unexp_error ;
761
762 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
763 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
764 END IF;
765
766 FND_MSG_PUB.count_and_get(
767 p_encoded => FND_API.g_false,
768 p_count => x_msg_count,
769 p_data => x_msg_data
770 );
771
772 END delete_physicalmap;
773
774
775 -- PROCEDURE
776 -- delete_attachment
777 --
778 -- PURPOSE
779 -- To delete all the physical_site_language_mappings for the given attachment
780 --
781 -- PARAMETERS
782 -- p_attachment_id : ID of the associated physical attachment
783 --
784 -- NOTES
785 -- 1. Deletes all the mappings associated with the physical attachment
786 -- 2. Raise an exception for any other errors
787 --------------------------------------------------------------------
788 PROCEDURE delete_attachment(
789 p_attachment_id IN NUMBER) IS
790
791 l_api_name CONSTANT VARCHAR2(30) := 'delete_attachment';
792 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
793
794 BEGIN
795
796 -- Standard start of API savepoint
797 SAVEPOINT delete_attachment_grp;
798
799 -- API body
800
801 DELETE FROM IBE_DSP_LGL_PHYS_MAP
802 WHERE attachment_id = p_attachment_id;
803
804 EXCEPTION
805
806 WHEN OTHERS THEN
807 ROLLBACK TO delete_attachment_grp;
808
809 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
810 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
811 END IF;
812
813 END delete_attachment;
814
815
816 -- PROCEDURE
817 -- delete_deliverable
818 --
819 -- PURPOSE
820 -- To delete all the physical_site_language_mappings for the given deliverable
821 --
822 -- PARAMETERS
823 -- p_deliverable_id: ID of the associated deliverable
824 --
825 -- NOTES
826 -- 1. Delete all the mappings associated with the deliverable
827 -- 2. Raise an exception for any other errors
828 --------------------------------------------------------------------
829 PROCEDURE delete_deliverable(
830 p_deliverable_id IN NUMBER) IS
831
832 l_api_name CONSTANT VARCHAR2(30) := 'delete_deliverable';
833 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
834
835 l_attachment_id NUMBER;
836 l_api_version NUMBER := 1.0;
837 x_return_status VARCHAR2(1);
838 x_msg_count NUMBER;
839 x_msg_data VARCHAR2(2000);
840
841 l_obj_ver NUMBER;
842
843
844 CURSOR attachment_ids IS
845 SELECT distinct b.attachment_id
846 from ibe_dsp_lgl_phys_map a, jtf_amv_attachments b
847 where a.item_id = p_deliverable_id
848 and a.attachment_id = b.attachment_id
849 and b.attachment_used_by_id = -1
850 and (b.file_id <=0 or b.file_id is null)
851 and b.application_id = 671
852 and b.attachment_id >= 10000;
853
854 BEGIN
855
856 -- Standard start of API savepoint
857 SAVEPOINT delete_deliverable_grp;
858
859 -- API body
860
861 -- delete the associated attachments if theie attachment_used_by_id = -1 and file_id is not >0
862 open attachment_ids;
863 fetch attachment_ids into l_attachment_id;
864 while attachment_ids%found
865 loop
866 --hft
867 -- delete from jtf_amv_attachments
868 -- where attachment_id = l_attachment_id;
869 SELECT OBJECT_VERSION_NUMBER into l_obj_ver FROM JTF_AMV_ATTACHMENTS
870 WHERE attachment_id = l_attachment_id;
871 JTF_AMV_ATTACHMENT_PUB.delete_act_attachment(
872 p_api_version => l_api_version,
873 x_return_status => x_return_status,
874 x_msg_count => x_msg_count,
875 x_msg_data => x_msg_data,
876 p_act_attachment_id =>l_attachment_id,
877 p_object_version =>l_obj_ver);
878 --hft end
879 fetch attachment_ids into l_attachment_id;
880 end loop;
881 close attachment_ids;
882
883 -- delete the mappings
884 DELETE FROM IBE_DSP_LGL_PHYS_MAP
885 WHERE item_id = p_deliverable_id;
886
887 EXCEPTION
888
889 WHEN OTHERS THEN
890 ROLLBACK TO delete_deliverable_grp;
891
892 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
893 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
894 END IF;
895
896 END delete_deliverable;
897
898
899 -- PROCEDURE
900 -- delete_msite
901 --
902 -- PURPOSE
903 -- To delete all the physical_site_language_mappings for the given mini-site
904 --
905 -- PARAMETERS
906 -- p_msite_id: ID of the associated mini-site
907 --
908 -- NOTES
909 -- 1. Delete all the mappings associated with the mini-site
910 -- 2. Raise an exception for any other errors
911 --------------------------------------------------------------------
912 PROCEDURE delete_msite(
913 p_msite_id IN NUMBER) IS
914
915 l_api_name CONSTANT VARCHAR2(30) := 'delete_msite';
916 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
917
918 BEGIN
919
920 -- Standard start of API savepint
921 SAVEPOINT delete_msite_grp;
922
923 -- API body
924
925 DELETE FROM IBE_DSP_LGL_PHYS_MAP
926 WHERE ( (msite_id = p_msite_id) AND (default_site = g_no) );
927
928 EXCEPTION
929
930 WHEN OTHERS THEN
931 ROLLBACK TO delete_msite_grp;
932
933 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
934 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
935 END IF;
936
937 END delete_msite;
938
939
940 -- PROCEDURE
941 -- delete_msite_language
942 --
943 -- PURPOSE
944 -- To delete all the physical_site_language_mappings involved the given
945 -- mini-site and the languages which have been de-supported at the mini-site
946 --
947 -- PARAMETERS
948 -- p_msite_id: ID of the associated mini-site
949 --
950 -- NOTES
951 -- 1. Delete all the mappings associated with the mini-site and the languages
952 -- which have been de-supported
953 -- 2. Raise an exception for any other errors
954 --------------------------------------------------------------------
955 PROCEDURE delete_msite_language(
956 p_msite_id IN NUMBER) IS
957
958 l_api_name CONSTANT VARCHAR2(30) := 'delete_msite_language';
959 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
960
961 BEGIN
962
963 -- Standard start of API savepint
964 SAVEPOINT delete_msite_language_grp;
965
966 -- API body
967
968 DELETE FROM IBE_DSP_LGL_PHYS_MAP
969 WHERE msite_id = p_msite_id
970 AND default_site = g_no
971 AND default_language = g_no
972 AND language_code NOT IN (SELECT language_code
973 FROM IBE_MSITE_LANGUAGES WHERE msite_id = p_msite_id);
974
975 EXCEPTION
976
977 WHEN OTHERS THEN
978 ROLLBACK TO delete_msite_language_grp;
979
980 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
981 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
982 END IF;
983
984 END delete_msite_language;
985
986
987 -- PROCEDURE
988 -- delete_attachment_msite
989 --
990 -- PURPOSE
991 -- To delete all the physical_site_language_mappings for the given mini-sites
992 -- and attachment
993 --
994 -- PARAMETERS
995 -- p_attachment_id: ID of the associated attachment
996 -- p_msite_id_tbl: the collction of IDs of the associated mini-sites
997 --
998 -- NOTES
999 -- 1. Delete all the mappings associated with the mini-sites and attachment
1000 -- 2. Raise an exception for any other errors
1001 PROCEDURE delete_attachment_msite(
1002 p_api_version IN NUMBER,
1003 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1004 p_commit IN VARCHAR2 := FND_API.g_false,
1005 x_return_status OUT NOCOPY VARCHAR2,
1006 x_msg_count OUT NOCOPY NUMBER,
1007 x_msg_data OUT NOCOPY VARCHAR2,
1008 p_attachment_id IN NUMBER,
1009 p_msite_id_tbl IN MSITE_ID_TBL_TYPE) IS
1010
1011 l_api_name CONSTANT VARCHAR2(30) := 'delete_attachment_msite';
1012 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1013
1014 BEGIN
1015
1016 -- Standard start of API savepoint
1017 SAVEPOINT delete_attachment_msite_grp;
1018
1019 -- Standard call to check for call compatibility
1020 IF NOT FND_API.compatible_api_call(
1021 g_api_version,
1022 p_api_version,
1023 l_api_name,
1024 g_pkg_name
1025 ) THEN
1026 RAISE FND_API.g_exc_unexpected_error;
1027 END IF;
1028
1029 -- Initialize message list if p_init_msg_list is set to TRUE
1030 IF FND_API.to_boolean(p_init_msg_list) THEN
1031 FND_MSG_PUB.initialize;
1032 END IF;
1033
1034 -- Initialize API rturn status to success
1035 x_return_status := FND_API.g_ret_sts_success;
1036
1037 -- API body
1038
1039 IF p_msite_id_tbl IS NOT NULL THEN
1040 FOR l_index IN 1..p_msite_id_tbl.COUNT LOOP
1041 BEGIN
1042
1043 SAVEPOINT delete_one_ath_msite_grp;
1044
1045 -- Check if it is all-sites
1046 IF p_msite_id_tbl(l_index) IS NULL THEN
1047 DELETE FROM IBE_DSP_LGL_PHYS_MAP
1048 WHERE ( (attachment_id = p_attachment_id)
1049 AND (default_site = g_yes) );
1050 ELSE
1051 DELETE FROM IBE_DSP_LGL_PHYS_MAP
1052 WHERE ( (attachment_id = p_attachment_id)
1053 AND (msite_id = p_msite_id_tbl(l_index))
1054 AND (default_site = g_no) );
1055 END IF;
1056
1057 EXCEPTION
1058
1059 WHEN OTHERS THEN
1060 ROLLBACK TO delete_one_ath_msite_grp;
1061 x_return_status := FND_API.g_ret_sts_unexp_error ;
1062 IF FND_MSG_PUB.check_msg_level(
1063 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1064 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1065 END IF;
1066
1067 END;
1068 END LOOP;
1069 END IF;
1070
1071 -- Check if the caller requested to commit ,
1072 -- If p_commit set to true, commit the transaction
1073 IF FND_API.to_boolean(p_commit) THEN
1074 COMMIT;
1075 END IF;
1076
1077 -- Standard call to get message count and if count is 1, get message info
1078 FND_MSG_PUB.count_and_get(
1079 p_encoded => FND_API.g_false,
1080 p_count => x_msg_count,
1081 p_data => x_msg_data
1082 );
1083
1084 EXCEPTION
1085
1086 WHEN FND_API.g_exc_error THEN
1087 ROLLBACK TO delete_attachment_msite_grp;
1088 x_return_status := FND_API.g_ret_sts_error;
1089 FND_MSG_PUB.count_and_get(
1090 p_encoded => FND_API.g_false,
1091 p_count => x_msg_count,
1092 p_data => x_msg_data
1093 );
1094
1095 WHEN FND_API.g_exc_unexpected_error THEN
1096 ROLLBACK TO delete_attachment_msite_grp;
1097 x_return_status := FND_API.g_ret_sts_unexp_error ;
1098 FND_MSG_PUB.count_and_get(
1099 p_encoded => FND_API.g_false,
1100 p_count => x_msg_count,
1101 p_data => x_msg_data
1102 );
1103
1104 WHEN OTHERS THEN
1105 ROLLBACK TO delete_attachment_msite_grp;
1106 x_return_status := FND_API.g_ret_sts_unexp_error ;
1107
1108 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1109 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1110 END IF;
1111
1112 FND_MSG_PUB.count_and_get(
1113 p_encoded => FND_API.g_false,
1114 p_count => x_msg_count,
1115 p_data => x_msg_data
1116 );
1117
1118
1119 END delete_attachment_msite;
1120
1121
1122 -- PROCEDURE
1123 -- delete_dlv_all_all
1124 --
1125 -- PURPOSE
1126 -- To delete the all-site and all-language mappings for the given deliverable
1127 --
1128 -- PARAMETERS
1129 -- p_deliverable_id: ID of the associated deliverable
1130 --
1131 -- NOTES
1132 -- 1. Delete the all-site and all-language mappings for the given deliverable
1133 -- 2. Raise an exception for any other errors
1134 PROCEDURE delete_dlv_all_all(
1135 p_deliverable_id IN NUMBER) IS
1136
1137 l_api_name CONSTANT VARCHAR2(30) := 'delete_dlv_all_all';
1138 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1139
1140 BEGIN
1141
1142 -- Standard start of API savepoint
1143 SAVEPOINT delete_dlv_all_all_grp;
1144
1145 -- API body
1146
1147 DELETE FROM IBE_DSP_LGL_PHYS_MAP
1148 WHERE item_id = p_deliverable_id
1149 AND default_site = g_yes
1150 AND default_language = g_yes;
1151
1152 EXCEPTION
1153
1154 WHEN OTHERS THEN
1155 ROLLBACK TO delete_dlv_all_all_grp;
1156
1157 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1158 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1159 END IF;
1160
1161 END delete_dlv_all_all;
1162
1163
1164 PROCEDURE save_physicalmap(
1165 p_api_version IN NUMBER,
1166 p_init_msg_list IN VARCHAR2 := FND_API.g_false, --modified by YAXU, ewmove DEFAULT
1167 p_commit IN VARCHAR2 := FND_API.g_false,
1168 x_return_status OUT NOCOPY VARCHAR2,
1169 x_msg_count OUT NOCOPY NUMBER,
1170 x_msg_data OUT NOCOPY VARCHAR2,
1171 p_deliverable_id IN NUMBER,
1172 p_old_content_key IN VARCHAR2,
1173 p_new_content_key IN VARCHAR2,
1174 p_msite_id IN NUMBER,
1175 p_language_code_tbl IN LANGUAGE_CODE_TBL_TYPE)
1176 IS
1177 l_api_name CONSTANT VARCHAR2(50) := 'save_physicalmap';
1178 l_api_version NUMBER := 1.0;
1179
1180 l_ocm_integration VARCHAR2(30);
1181 l_msite_id NUMBER;
1182
1183 l_index NUMBER;
1184 l_deliverable_id NUMBER;
1185 l_old_attachment_id NUMBER;
1186 l_old_content_item_key VARCHAR2(100);
1187 l_attachment_id NUMBER;
1188 l_content_item_key VARCHAR2(100);
1189
1190 l_msite_type VARCHAR2(10);
1191 l_default_msite VARCHAR2(1);
1192 l_default_lang VARCHAR2(1);
1193 l_language_code VARCHAR2(4);
1194 l_lgl_phys_map_id NUMBER;
1195 l_index NUMBER;
1196
1197 -- added by YAXU
1198 l_seed_lgl_phys_map_id NUMBER;
1199 l_seed_data_exists Boolean := false;
1200 l_object_version_number NUMBER;
1201
1202 CURSOR lgl_phys_map_id_seq IS
1203 SELECT IBE_DSP_LGL_PHYS_MAP_S1.NEXTVAL FROM DUAL;
1204 BEGIN
1205 SAVEPOINT SAVE_PHYSICALMAP;
1206 l_deliverable_id := p_deliverable_id;
1207 l_msite_type := 'SITE';
1208 l_default_msite := g_no;
1209 l_default_lang := g_no;
1210
1211
1212 IF NOT FND_API.Compatible_API_Call(l_api_version,
1213 p_api_version,
1214 l_api_name,
1215 G_PKG_NAME) THEN
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217 END IF;
1218 IF FND_API.to_Boolean(p_init_msg_list) THEN
1219 FND_MSG_PUB.initialize;
1220 END IF;
1221 -- Initialize API return status to SUCCESS
1222 x_return_status := FND_API.G_RET_STS_SUCCESS;
1223 l_ocm_integration
1224 := FND_PROFILE.value('IBE_M_USE_CONTENT_INTEGRATION');
1225 IF (l_ocm_integration IS NOT NULL)
1226 AND (l_ocm_integration = 'Y') THEN
1227 l_attachment_id := -1;
1228 l_content_item_key := p_new_content_key;
1229 l_old_attachment_id := -1;
1230 l_old_content_item_key := NVL(p_old_content_key,'-1');
1231 ELSE
1232 l_attachment_id := TO_NUMBER(p_new_content_key);
1233 l_content_item_key := NULL;
1234 l_old_attachment_id := TO_NUMBER(NVL(p_old_content_key,'-1'));
1235 l_old_content_item_key := NULL;
1236 END IF;
1237
1238 -- Validate minisite id
1239 l_msite_id := p_msite_id;
1240 IF p_msite_id IS NOT NULL THEN
1241 IF NOT IBE_DSPMGRVALIDATION_GRP.check_msite_exists
1242 (p_msite_id) THEN
1243 RAISE FND_API.g_exc_error;
1244 END IF;
1245 ELSE
1246 l_msite_type := 'ALLSITES';
1247 l_default_msite := g_yes;
1248 l_msite_id :=
1249 IBE_DSPMGRVALIDATION_GRP.check_master_msite_exists;
1250 IF l_msite_id IS NULL THEN
1251 RAISE FND_API.g_exc_error;
1252 END IF;
1253 END IF;
1254
1255 IF (l_ocm_integration IS NULL)
1256 OR (l_ocm_integration = 'N') THEN
1257 -- Delete all the mappings fro the minisites, added by YAXU
1258 DELETE FROM IBE_DSP_LGL_PHYS_MAP
1259 WHERE ((item_id = l_deliverable_id)
1260 AND (attachment_id = l_attachment_id)
1261 AND (content_item_key is null)
1262 AND (default_site = l_default_msite)
1263 AND (msite_id = l_msite_id)
1264 AND (lgl_phys_map_id >= 10000));
1265 END IF;
1266
1267 -- Validate language code
1268 IF p_language_code_tbl IS NOT NULL THEN
1269 l_language_code := TRIM(p_language_code_tbl(1));
1270 IF l_language_code IS NULL THEN
1271 -- temporarily using US. change later
1272 IF (l_ocm_integration IS NOT NULL) AND (l_ocm_integration = 'Y') THEN
1273 l_language_code := 'OCM';
1274 ELSE
1275 l_language_code := 'US';
1276 END IF;
1277 l_default_lang := g_yes;
1278
1279 IF(l_default_msite = g_yes) -- check if the seed default mapping exists
1280 THEN
1281 l_seed_data_exists := true;
1282 IF (l_ocm_integration IS NOT NULL) AND (l_ocm_integration = 'Y')
1283 THEN
1284 BEGIN
1285 SELECT lgl_phys_map_id, object_version_number
1286 INTO l_seed_lgl_phys_map_id,l_object_version_number
1287 FROM IBE_DSP_LGL_PHYS_MAP
1288 WHERE item_id = l_deliverable_id
1289 AND content_item_key = l_old_content_item_key
1290 AND attachment_id = -1
1291 AND default_site = g_yes
1292 AND default_language = g_yes
1293 AND lgl_phys_map_id < 10000;
1294 EXCEPTION
1295 WHEN NO_DATA_FOUND THEN
1296 l_seed_data_exists := false;
1297 END;
1298 ELSE
1299 BEGIN
1300 SELECT lgl_phys_map_id, object_version_number
1301 INTO l_seed_lgl_phys_map_id,l_object_version_number
1302 FROM IBE_DSP_LGL_PHYS_MAP
1303 WHERE item_id = l_deliverable_id
1304 AND attachment_id = l_attachment_id
1305 AND content_item_key is null
1306 AND default_site = g_yes
1307 AND default_language = g_yes
1308 AND lgl_phys_map_id < 10000;
1309 EXCEPTION
1310 WHEN NO_DATA_FOUND THEN
1311 l_seed_data_exists := false;
1312 END;
1313 END IF;
1314 END IF;
1315
1316 IF(l_seed_data_exists = false)
1317 THEN
1318 OPEN lgl_phys_map_id_seq;
1319 FETCH lgl_phys_map_id_seq INTO l_lgl_phys_map_id;
1320 CLOSE lgl_phys_map_id_seq;
1321 INSERT INTO IBE_DSP_LGL_PHYS_MAP (
1322 lgl_phys_map_id, object_version_number, last_update_date,
1323 last_updated_by, creation_date, created_by,
1324 last_update_login, msite_id, language_code,
1325 attachment_id, item_id, default_site,
1326 default_language, content_item_key)
1327 VALUES(l_lgl_phys_map_id, 1, SYSDATE,
1328 FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id,
1329 FND_GLOBAL.login_id, l_msite_id, l_language_code,
1330 l_attachment_id, l_deliverable_id, l_default_msite,
1331 l_default_lang, l_content_item_key);
1332 ELSE -- update the seeded mapping
1333 IF((l_ocm_integration IS NOT NULL)
1334 AND (l_ocm_integration = 'Y')
1335 AND (p_old_content_key <> p_new_content_key))
1336 THEN
1337 UPDATE IBE_DSP_LGL_PHYS_MAP
1338 SET attachment_id = l_attachment_id,
1339 content_item_key = l_content_item_key,
1340 object_version_number = l_object_version_number+1,
1341 last_update_date = SYSDATE,
1342 last_updated_by = FND_GLOBAL.user_id,
1343 last_update_login = FND_GLOBAL.login_id
1344 WHERE lgl_phys_map_id = l_seed_lgl_phys_map_id;
1345 END IF;
1346 END IF;
1347 ELSE
1348 FOR l_index IN 1..p_language_code_tbl.COUNT LOOP
1349 BEGIN
1350 SAVEPOINT SAVE_ONE_PHYSICALMAP;
1351 IF (l_ocm_integration IS NOT NULL) AND (l_ocm_integration = 'Y') THEN
1352 l_language_code := 'OCM';
1353 ELSE
1354 IF (l_msite_type = 'SITE') THEN
1355 -- Check if the language is supported at the given site
1356 -- For OCM integration, this check is not necessary.
1357 IF NOT IBE_DSPMGRVALIDATION_GRP.check_language_supported
1358 (p_msite_id, p_language_code_tbl(l_index)) THEN
1359 RAISE FND_API.g_exc_error;
1360 END IF;
1361 END IF;
1362 l_language_code := p_language_code_tbl(l_index);
1363 END IF;
1364 OPEN lgl_phys_map_id_seq;
1365 FETCH lgl_phys_map_id_seq INTO l_lgl_phys_map_id;
1366 CLOSE lgl_phys_map_id_seq;
1367 INSERT INTO IBE_DSP_LGL_PHYS_MAP (
1368 lgl_phys_map_id, object_version_number, last_update_date,
1369 last_updated_by, creation_date, created_by,
1370 last_update_login, msite_id, language_code,
1371 attachment_id, item_id, default_site,
1372 default_language, content_item_key)
1373 VALUES(l_lgl_phys_map_id, 1, SYSDATE,
1374 FND_GLOBAL.user_id, SYSDATE, FND_GLOBAL.user_id,
1375 FND_GLOBAL.login_id, l_msite_id, l_language_code,
1376 l_attachment_id, l_deliverable_id, l_default_msite,
1377 l_default_lang, l_content_item_key);
1378 EXCEPTION
1379 WHEN FND_API.g_exc_error THEN
1380 ROLLBACK TO SAVE_ONE_PHYSICALMAP;
1381 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
1382 x_return_status := FND_API.g_ret_sts_error;
1383 END IF;
1384 WHEN dup_val_on_index THEN
1385 ROLLBACK TO SAVE_ONE_PHYSICALMAP;
1386 IF x_return_status <> FND_API.g_ret_sts_unexp_error THEN
1387 x_return_status := FND_API.g_ret_sts_error;
1388 END IF;
1389 IF FND_MSG_PUB.check_msg_level
1390 (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1391 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1392 IF l_default_msite = g_yes THEN
1393 FND_MESSAGE.set_name('IBE',
1394 'IBE_DSP_PHYSMAP_ALL_LNG_EXISTS');
1395 FND_MESSAGE.set_token('LANG',
1396 p_language_code_tbl(l_index));
1397 ELSE
1398 FND_MESSAGE.set_name('IBE',
1399 'IBE_DSP_PHYSMAP_EXISTS');
1400 FND_MESSAGE.set_token('MSITE_ID',
1401 TO_CHAR(l_msite_id));
1402 FND_MESSAGE.set_token(
1403 'LANG', p_language_code_tbl(l_index));
1404 END IF;
1405 FND_MESSAGE.set_token('ID', TO_CHAR(l_deliverable_id));
1406 FND_MSG_PUB.add;
1407 END IF;
1408 WHEN OTHERS THEN
1409 ROLLBACK TO SAVE_ONE_PHYSICALMAP;
1410 x_return_status := FND_API.g_ret_sts_unexp_error;
1411 IF FND_MSG_PUB.check_msg_level(
1412 FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1413 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1414 END IF;
1415 END;
1416 END LOOP;
1417 END IF;
1418 END IF;
1419 IF (FND_API.To_Boolean(p_commit)) THEN
1420 COMMIT WORK;
1421 END IF;
1422 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1423 p_data => x_msg_data,
1424 p_encoded => 'F');
1425 EXCEPTION
1426 WHEN FND_API.G_EXC_ERROR THEN
1427 ROLLBACK TO SAVE_PHYSICALMAP;
1428 x_return_status := FND_API.G_RET_STS_ERROR;
1429 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1430 p_data => x_msg_data,
1431 p_encoded => 'F');
1432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1433 ROLLBACK TO SAVE_PHYSICALMAP;
1434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1435 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1436 p_data => x_msg_data,
1437 p_encoded => 'F');
1438
1439 --added by YAXU on 01/02/2004
1440 WHEN dup_val_on_index THEN
1441 ROLLBACK TO SAVE_PHYSICALMAP;
1442 x_return_status := FND_API.g_ret_sts_error;
1443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1444 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1445
1446 IF l_default_msite = g_yes and l_default_lang = g_yes THEN
1447 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_ALL_ALL_EXISTS');
1448 END IF;
1449 IF l_default_msite = g_no and l_default_lang = g_yes THEN
1450 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_STE_ALL_EXISTS');
1451 FND_MESSAGE.set_token('MSITE_ID', TO_CHAR(l_msite_id));
1452 END IF;
1453 FND_MESSAGE.set_token('ID', TO_CHAR(l_deliverable_id));
1454 FND_MSG_PUB.add;
1455
1456 END IF;
1457
1458 FND_MSG_PUB.count_and_get(
1459 p_encoded => FND_API.g_false,
1460 p_count => x_msg_count,
1461 p_data => x_msg_data
1462 );
1463
1464
1465 WHEN OTHERS THEN
1466 ROLLBACK TO SAVE_PHYSICALMAP;
1467 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1468 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1469 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1470 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1471 FND_MSG_PUB.Add;
1472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1473 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1474 THEN
1475 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1476 END IF;
1477 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1478 p_data => x_msg_data,
1479 p_encoded => 'F');
1480 END save_physicalmap;
1481
1482 PROCEDURE save_physicalmap(
1483 p_api_version IN NUMBER,
1484 p_init_msg_list IN VARCHAR2 := FND_API.g_false, --modified by YAXU, ewmove DEFAULT
1485 p_commit IN VARCHAR2 := FND_API.g_false,
1486 x_return_status OUT NOCOPY VARCHAR2,
1487 x_msg_count OUT NOCOPY NUMBER,
1488 x_msg_data OUT NOCOPY VARCHAR2,
1489 p_deliverable_id IN NUMBER,
1490 p_old_content_key IN VARCHAR2,
1491 p_new_content_key IN VARCHAR2,
1492 p_msite_lang_tbl IN MSITE_LANG_TBL_TYPE,
1493 p_language_code_tbl IN LANGUAGE_CODE_TBL_TYPE)
1494 IS
1495 l_api_name CONSTANT VARCHAR2(50) := 'save_physicalmap';
1496 l_api_version NUMBER := 1.0;
1497
1498 l_count NUMBER;
1499 l_index NUMBER;
1500
1501 l_language_code_tbl LANGUAGE_CODE_TBL_TYPE;
1502 l_return_status VARCHAR2(1);
1503
1504 l_ocm_integration VARCHAR2(30);
1505 l_attachment_id NUMBER;
1506 l_old_attachment_id NUMBER;
1507 l_content_item_key VARCHAR2(100);
1508 l_old_content_item_key VARCHAR2(100);
1509
1510 BEGIN
1511 SAVEPOINT SAVE_PHYSICALMAP;
1512 l_return_status := FND_API.g_ret_sts_success;
1513 IF NOT FND_API.Compatible_API_Call(l_api_version,
1514 p_api_version,
1515 l_api_name,
1516 G_PKG_NAME) THEN
1517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1518 END IF;
1519 IF FND_API.to_Boolean(p_init_msg_list) THEN
1520 FND_MSG_PUB.initialize;
1521 END IF;
1522 -- Initialize API return status to SUCCESS
1523 x_return_status := FND_API.G_RET_STS_SUCCESS;
1524 -- Validate the input parameters
1525 IF p_msite_lang_tbl IS NOT NULL THEN
1526 l_count := 0;
1527 FOR l_index IN 1..p_msite_lang_tbl.COUNT LOOP
1528 IF p_msite_lang_tbl(l_index).lang_count IS NULL
1529 OR p_msite_lang_tbl(l_index).lang_count < 0 THEN
1530 RAISE FND_API.g_exc_unexpected_error;
1531 END IF;
1532 l_count := l_count + p_msite_lang_tbl(l_index).lang_count;
1533 END LOOP;
1534 IF (l_count > 0) THEN
1535 IF (p_language_code_tbl IS NULL) OR
1536 l_count <> p_language_code_tbl.COUNT THEN
1537 RAISE FND_API.g_exc_unexpected_error;
1538 END IF;
1539 END IF;
1540 -- ELSE -- removed by YAXU
1541 -- RETURN;
1542 END IF;
1543
1544 IF (p_old_content_key IS NOT NULL) THEN
1545 l_ocm_integration
1546 := FND_PROFILE.value('IBE_M_USE_CONTENT_INTEGRATION');
1547 IF (l_ocm_integration IS NOT NULL)
1548 AND (l_ocm_integration = 'Y') THEN
1549 -- delete all the exisitng mapping for the p_old_content_key
1550 l_old_content_item_key := p_old_content_key;
1551 DELETE FROM IBE_DSP_LGL_PHYS_MAP
1552 WHERE ((attachment_id = -1)
1553 AND (item_id = p_deliverable_id)
1554 AND (content_item_key = l_old_content_item_key)
1555 AND (lgl_phys_map_id >=10000));
1556 ELSE
1557 IF(p_old_content_key <> p_new_content_key) -- modified by YAXU to update the mapping
1558 THEN
1559 l_old_attachment_id := TO_NUMBER(p_old_content_key);
1560 l_attachment_id := TO_NUMBER(p_new_content_key);
1561 UPDATE IBE_DSP_LGL_PHYS_MAP
1562 set attachment_id = l_attachment_id,
1563 last_update_date = SYSDATE,
1564 last_updated_by = FND_GLOBAL.user_id,
1565 last_update_login = FND_GLOBAL.login_id
1566 WHERE ((attachment_id = l_old_attachment_id)
1567 AND (item_id = p_deliverable_id)
1568 AND (content_item_key is null));
1569 END IF;
1570 END IF;
1571 END IF;
1572
1573 -- Start saving
1574 IF( p_msite_lang_tbl IS NOT NULL) THEN -- added by YAXU
1575 l_count := 0;
1576 FOR l_index IN 1..p_msite_lang_tbl.COUNT LOOP
1577 l_return_status := FND_API.g_ret_sts_success;
1578 l_language_code_tbl := NULL;
1579 IF p_msite_lang_tbl(l_index).lang_count > 0 THEN
1580 l_language_code_tbl := LANGUAGE_CODE_TBL_TYPE(
1581 p_language_code_tbl(l_count + 1));
1582 IF p_msite_lang_tbl(l_index).lang_count > 1 THEN
1583 l_language_code_tbl.EXTEND(p_msite_lang_tbl(l_index).lang_count - 1);
1584 FOR l_i IN 2..p_msite_lang_tbl(l_index).lang_count LOOP
1585 l_language_code_tbl(l_i) := p_language_code_tbl(l_count + l_i);
1586 END LOOP;
1587 END IF;
1588 l_count := l_count + p_msite_lang_tbl(l_index).lang_count;
1589 END IF;
1590 save_physicalmap(
1591 p_api_version => p_api_version,
1592 x_return_status => l_return_status,
1593 x_msg_count => x_msg_count,
1594 x_msg_data => x_msg_data,
1595 p_deliverable_id => p_deliverable_id,
1596 p_old_content_key => p_old_content_key,
1597 p_new_content_key => p_new_content_key,
1598 p_msite_id => p_msite_lang_tbl(l_index).msite_id,
1599 p_language_code_tbl => l_language_code_tbl);
1600 IF l_return_status <> FND_API.g_ret_sts_success THEN
1601 x_return_status := l_return_status;
1602 END IF;
1603 END LOOP;
1604 END IF;
1605
1606 IF (FND_API.To_Boolean(p_commit)) THEN
1607 COMMIT WORK;
1608 END IF;
1609 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1610 p_data => x_msg_data,
1611 p_encoded => 'F');
1612 EXCEPTION
1613 WHEN FND_API.G_EXC_ERROR THEN
1614 ROLLBACK TO SAVE_PHYSICALMAP;
1615 x_return_status := FND_API.G_RET_STS_ERROR;
1616 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1617 p_data => x_msg_data,
1618 p_encoded => 'F');
1619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1620 ROLLBACK TO SAVE_PHYSICALMAP;
1621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1622 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1623 p_data => x_msg_data,
1624 p_encoded => 'F');
1625 WHEN OTHERS THEN
1626 ROLLBACK TO SAVE_PHYSICALMAP;
1627 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1628 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1629 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1630 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1631 FND_MSG_PUB.Add;
1632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1633 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1634 THEN
1635 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1636 END IF;
1637 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1638 p_data => x_msg_data,
1639 p_encoded => 'F');
1640 END save_physicalmap;
1641
1642 PROCEDURE delete_physicalmap(
1643 p_api_version IN NUMBER,
1644 p_init_msg_list IN VARCHAR2 := FND_API.g_false, --modified by YAXU, ewmove DEFAULT
1645 p_commit IN VARCHAR2 := FND_API.g_false,
1646 x_return_status OUT NOCOPY VARCHAR2,
1647 x_msg_count OUT NOCOPY NUMBER,
1648 x_msg_data OUT NOCOPY VARCHAR2,
1649 p_deliverable_id IN NUMBER,
1650 p_content_key IN VARCHAR2)
1651 IS
1652 l_api_name CONSTANT VARCHAR2(50) := 'delete_physicalmap';
1653 l_api_version NUMBER := 1.0;
1654
1655 l_ocm_integration VARCHAR2(30);
1656 l_deliverable_id NUMBER;
1657 l_attachment_id NUMBER;
1658 l_content_item_key VARCHAR2(100);
1659
1660 l_file_id NUMBER;
1661 l_other_item_count NUMBER;
1662 l_obj_ver NUMBER;
1663
1664 BEGIN
1665 SAVEPOINT DELETE_PHYSICALMAP;
1666 l_deliverable_id := p_deliverable_id;
1667 IF NOT FND_API.Compatible_API_Call(l_api_version,
1668 p_api_version,
1669 l_api_name,
1670 G_PKG_NAME) THEN
1671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1672 END IF;
1673 IF FND_API.to_Boolean(p_init_msg_list) THEN
1674 FND_MSG_PUB.initialize;
1675 END IF;
1676 -- Initialize API return status to SUCCESS
1677 x_return_status := FND_API.G_RET_STS_SUCCESS;
1678 l_ocm_integration
1679 := FND_PROFILE.value('IBE_M_USE_CONTENT_INTEGRATION');
1680 IF (l_ocm_integration IS NOT NULL)
1681 AND (l_ocm_integration = 'Y') THEN
1682 l_content_item_key := p_content_key;
1683 DELETE FROM IBE_DSP_LGL_PHYS_MAP
1684 WHERE attachment_id = -1
1685 AND content_item_key = l_content_item_key
1686 AND item_id = l_deliverable_id;
1687 ELSE
1688 l_attachment_id := TO_NUMBER(p_content_key);
1689 DELETE FROM IBE_DSP_LGL_PHYS_MAP
1690 WHERE attachment_id = l_attachment_id
1691 AND content_item_key IS NULL
1692 AND item_id = l_deliverable_id;
1693
1694 -- added by YAXU, delete the attachment if the its file_id =0 and no realted mapping
1695 SELECT file_id into l_file_id
1696 FROM JTF_AMV_ATTACHMENTS
1697 WHERE attachment_id = l_attachment_id;
1698
1699 IF l_file_id <= 0 or l_file_id is null THEN
1700 SELECT count(1) into l_other_item_count
1701 FROM IBE_DSP_LGL_PHYS_MAP
1702 WHERE attachment_id = l_attachment_id
1703 AND content_item_key IS NULL
1704 AND item_id <> l_deliverable_id;
1705
1706 IF l_other_item_count = 0 THEN
1707 -- delete the attachment
1708 --hft
1709 -- DELETE FROM JTF_AMV_ATTACHMENTS
1710 -- WHERE attachment_id = l_attachment_id;
1711 SELECT OBJECT_VERSION_NUMBER into l_obj_ver FROM JTF_AMV_ATTACHMENTS
1712 WHERE attachment_id = l_attachment_id;
1713 JTF_AMV_ATTACHMENT_PUB.delete_act_attachment(
1714 p_api_version => l_api_version,
1715 x_return_status => x_return_status,
1716 x_msg_count => x_msg_count,
1717 x_msg_data => x_msg_data,
1718 p_act_attachment_id =>l_attachment_id,
1719 p_object_version =>l_obj_ver);
1720 --hft end
1721 END IF;
1722 END IF;
1723
1724 END IF;
1725 IF (FND_API.To_Boolean(p_commit)) THEN
1726 COMMIT WORK;
1727 END IF;
1728 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1729 p_data => x_msg_data,
1730 p_encoded => 'F');
1731 EXCEPTION
1732 WHEN FND_API.G_EXC_ERROR THEN
1733 ROLLBACK TO DELETE_PHYSICALMAP;
1734 x_return_status := FND_API.G_RET_STS_ERROR;
1735 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1736 p_data => x_msg_data,
1737 p_encoded => 'F');
1738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1739 ROLLBACK TO DELETE_PHYSICALMAP;
1740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1741 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1742 p_data => x_msg_data,
1743 p_encoded => 'F');
1744 WHEN OTHERS THEN
1745 ROLLBACK TO DELETE_PHYSICALMAP;
1746 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1747 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1748 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1749 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1750 FND_MSG_PUB.Add;
1751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1752 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1753 THEN
1754 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1755 END IF;
1756 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1757 p_data => x_msg_data,
1758 p_encoded => 'F');
1759 END delete_physicalmap;
1760
1761 PROCEDURE replace_content(
1762 p_api_version IN NUMBER,
1763 p_init_msg_list IN VARCHAR2 := FND_API.g_false, --modified by YAXU, ewmove DEFAULT
1764 p_commit IN VARCHAR2 := FND_API.g_false,
1765 x_return_status OUT NOCOPY VARCHAR2,
1766 x_msg_count OUT NOCOPY NUMBER,
1767 x_msg_data OUT NOCOPY VARCHAR2,
1768 p_old_content_key IN VARCHAR2,
1769 p_new_content_key IN VARCHAR2)
1770 IS
1771 l_api_name CONSTANT VARCHAR2(50) := 'replace_content';
1772 l_api_version NUMBER := 1.0;
1773
1774 l_ocm_integration VARCHAR2(30);
1775 l_attachment_id NUMBER;
1776 l_content_item_key VARCHAR2(100);
1777 l_old_attachment_id NUMBER;
1778 l_old_content_item_key VARCHAR2(100);
1779 BEGIN
1780 SAVEPOINT REPLACE_CONTENT;
1781 IF NOT FND_API.Compatible_API_Call(l_api_version,
1782 p_api_version,
1783 l_api_name,
1784 G_PKG_NAME) THEN
1785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1786 END IF;
1787 IF FND_API.to_Boolean(p_init_msg_list) THEN
1788 FND_MSG_PUB.initialize;
1789 END IF;
1790 -- Initialize API return status to SUCCESS
1791 x_return_status := FND_API.G_RET_STS_SUCCESS;
1792 l_ocm_integration
1793 := FND_PROFILE.value('IBE_M_USE_CONTENT_INTEGRATION');
1794 IF (l_ocm_integration IS NOT NULL)
1795 AND (l_ocm_integration = 'Y') THEN
1796 l_content_item_key := p_new_content_key;
1797 l_old_content_item_key := p_old_content_key;
1798 UPDATE IBE_DSP_LGL_PHYS_MAP
1799 SET content_item_key = l_content_item_key
1800 WHERE content_item_key = l_old_content_item_key
1801 AND attachment_id = -1;
1802 ELSE
1803 l_attachment_id := TO_NUMBER(p_new_content_key);
1804 l_old_attachment_id := TO_NUMBER(p_old_content_key);
1805 UPDATE IBE_DSP_LGL_PHYS_MAP
1806 SET attachment_id = l_attachment_id
1807 WHERE attachment_id = l_old_attachment_id
1808 AND content_item_key IS NULL;
1809 END IF;
1810 IF (FND_API.To_Boolean(p_commit)) THEN
1811 COMMIT WORK;
1812 END IF;
1813 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1814 p_data => x_msg_data,
1815 p_encoded => 'F');
1816 EXCEPTION
1817 WHEN FND_API.G_EXC_ERROR THEN
1818 ROLLBACK TO REPLACE_CONTENT;
1819 x_return_status := FND_API.G_RET_STS_ERROR;
1820 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1821 p_data => x_msg_data,
1822 p_encoded => 'F');
1823 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1824 ROLLBACK TO REPLACE_CONTENT;
1825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1826 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1827 p_data => x_msg_data,
1828 p_encoded => 'F');
1829 WHEN OTHERS THEN
1830 ROLLBACK TO REPLACE_CONTENT;
1831 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1832 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1833 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1834 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1835 FND_MSG_PUB.Add;
1836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1837 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1838 THEN
1839 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1840 END IF;
1841 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1842 p_data => x_msg_data,
1843 p_encoded => 'F');
1844 END replace_content;
1845
1846
1847
1848 PROCEDURE LOAD_SEED_ROW(
1849 P_LGL_PHYS_MAP_ID IN NUMBER,
1850 P_OBJECT_VERSION_NUMBER IN NUMBER,
1851 P_MSITE_ID IN NUMBER,
1852 P_LANGUAGE_CODE IN VARCHAR2,
1853 P_ATTACHMENT_ID IN NUMBER,
1854 P_ITEM_ID IN NUMBER,
1855 P_DEFAULT_LANGUAGE IN VARCHAR2,
1856 P_DEFAULT_SITE IN VARCHAR2,
1857 P_OWNER IN VARCHAR2,
1858 P_LAST_UPDATE_DATE IN VARCHAR2,
1859 P_CUSTOM_MODE IN VARCHAR2,
1860 P_UPLOAD_MODE IN VARCHAR2)
1861
1862 IS
1863 BEGIN
1864 IF (P_UPLOAD_MODE = 'NLS') THEN
1865 null;
1866 ELSE
1867 LOAD_ROW(
1868 P_LGL_PHYS_MAP_ID,
1869 P_OBJECT_VERSION_NUMBER,
1870 P_MSITE_ID,
1871 P_LANGUAGE_CODE,
1872 P_ATTACHMENT_ID,
1873 P_ITEM_ID,
1874 P_DEFAULT_LANGUAGE,
1875 P_DEFAULT_SITE,
1876 P_OWNER,
1877 P_LAST_UPDATE_DATE,
1878 P_CUSTOM_MODE);
1879 END IF;
1880 END LOAD_SEED_ROW;
1881
1882
1883
1884 PROCEDURE LOAD_ROW(
1885 P_LGL_PHYS_MAP_ID IN NUMBER,
1886 P_OBJECT_VERSION_NUMBER IN NUMBER,
1887 P_MSITE_ID IN NUMBER,
1888 P_LANGUAGE_CODE IN VARCHAR2,
1889 P_ATTACHMENT_ID IN NUMBER,
1890 P_ITEM_ID IN NUMBER,
1891 P_DEFAULT_LANGUAGE IN VARCHAR2,
1892 P_DEFAULT_SITE IN VARCHAR2,
1893 P_OWNER IN VARCHAR2,
1894 P_LAST_UPDATE_DATE IN VARCHAR2,
1895 P_CUSTOM_MODE IN VARCHAR2)
1896
1897 IS
1898 l_rowExists NUMBER;
1899 l_indexExists NUMBER;
1900 f_luby NUMBER; -- entity owner in file
1901 f_ludate DATE; -- entity update date in file
1902 db_luby NUMBER; -- entity owner in db
1903 db_ludate DATE; -- entity update date in db
1904
1905 BEGIN
1906
1907 -- Translate owner to file_last_updated_by
1908 f_luby := fnd_load_util.owner_id(P_OWNER);
1909
1910 -- Translate char last_update_date to date
1911 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
1912
1913 -- Get the value of the db_luby and db_ludate from the database.
1914 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
1915 INTO db_luby, db_ludate
1916 FROM ibe_dsp_lgl_phys_map
1917 WHERE lgl_phys_map_id = P_LGL_PHYS_MAP_ID;
1918
1919
1920 --Invoke standard merge comparison routine UPLOAD_TEST.
1921 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, P_CUSTOM_MODE)) THEN
1922 UPDATE ibe_dsp_lgl_phys_map
1923 SET LGL_PHYS_MAP_ID = P_LGL_PHYS_MAP_ID,
1924 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
1925 LAST_UPDATED_BY = f_luby,
1926 LAST_UPDATE_DATE = f_ludate,
1927 MSITE_ID = P_MSITE_ID,
1928 LANGUAGE_CODE = userenv('LANG'),
1929 ATTACHMENT_ID = P_ATTACHMENT_ID,
1930 ITEM_ID = P_ITEM_ID,
1931 DEFAULT_LANGUAGE = P_DEFAULT_LANGUAGE,
1932 DEFAULT_SITE = P_DEFAULT_SITE
1933 WHERE lgl_phys_map_id = P_LGL_PHYS_MAP_ID;
1934 END IF;
1935 EXCEPTION
1936 WHEN NO_DATA_FOUND THEN
1937 -- bug fix 4685390
1938 -- check for unique index, insert the new row only if there is no unique
1939 -- constraint violation. This is required because we allow the Customer to
1940 -- delete the default All All mapping for the media object. Customer can
1941 -- then create their own mapping similar to the default seeded mapping.
1942 -- If we do not do this check then while inserting a seeded mapping row
1943 -- a unique contraint violation will be encountered
1944
1945 BEGIN
1946 select 1 into l_indexExists from ibe_dsp_lgl_phys_map where
1947 item_id= to_number(P_ITEM_ID) and
1948 language_code= P_LANGUAGE_CODE and
1949 msite_id = to_number(P_MSITE_ID) and
1950 default_language= P_DEFAULT_LANGUAGE and
1951 default_site= P_DEFAULT_SITE ;
1952 EXCEPTION
1953 when NO_DATA_FOUND then
1954 l_indexExists:=null;
1955 END;
1956 IF (l_indexExists is null) then
1957 INSERT INTO ibe_dsp_lgl_phys_map (
1958 LGL_PHYS_MAP_ID,
1959 OBJECT_VERSION_NUMBER,
1960 CREATED_BY,
1961 CREATION_DATE,
1962 LAST_UPDATED_BY,
1963 LAST_UPDATE_DATE,
1964 LAST_UPDATE_LOGIN,
1965 MSITE_ID,
1966 LANGUAGE_CODE,
1967 ATTACHMENT_ID,
1968 ITEM_ID,
1969 DEFAULT_LANGUAGE,
1970 DEFAULT_SITE)
1971 VALUES (
1972 P_LGL_PHYS_MAP_ID,
1973 P_OBJECT_VERSION_NUMBER,
1974 f_luby,
1975 f_ludate,
1976 f_luby,
1977 f_ludate,
1978 f_luby,
1979 P_MSITE_ID,
1980 P_LANGUAGE_CODE,
1981 P_ATTACHMENT_ID,
1982 P_ITEM_ID,
1983 P_DEFAULT_LANGUAGE,
1984 P_DEFAULT_SITE);
1985 END IF;
1986
1987 END LOAD_ROW;
1988
1989
1990 END IBE_PhysicalMap_GRP;
[Home] [Help]