[Home] [Help]
PACKAGE BODY: APPS.IBE_LOGICALCONTENT_GRP
Source
1 PACKAGE BODY IBE_LogicalContent_GRP AS
2 /* $Header: IBEGLCTB.pls 120.1 2005/12/28 13:26:46 savarghe noship $ */
3
4 -- HISTORY
5 -- 12/12/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
6 -- *********************************************************************************
7
8 --- Generate primary key for the table
9 CURSOR obj_lgl_ctnt_id_seq IS
10 SELECT ibe_dsp_obj_lgl_ctnt_s1.NEXTVAL
11 FROM DUAL;
12
13
14 PROCEDURE delete_logical_content(
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
17 p_commit IN VARCHAR2 := FND_API.g_false,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 p_object_type IN VARCHAR2,
22 p_lgl_ctnt_rec IN OBJ_LGL_CTNT_REC_TYPE )
23 IS
24 l_api_name CONSTANT VARCHAR2(30) := 'delete_logical_content';
25 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
26 l_return_status VARCHAR2(1);
27 l_index NUMBER;
28 l_context_id NUMBER;
29 l_exists NUMBER;
30 BEGIN
31 --------------------- initialize -----------------------+
32 SAVEPOINT delete_logical_content;
33
34 IF NOT FND_API.compatible_api_call(
35 g_api_version,
36 p_api_version,
37 l_api_name,
38 g_pkg_name )
39 THEN
40 RAISE FND_API.g_exc_unexpected_error;
41 END IF;
42
43 IF FND_API.to_boolean(p_init_msg_list)
44 THEN
45 FND_MSG_PUB.initialize;
46 END IF;
47
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49
50 IF p_lgl_ctnt_rec.obj_lgl_ctnt_id IS NOT NULL
51 THEN
52 --- Check if the object logical content exists
53 IF ibe_dspmgrvalidation_grp.check_lgl_ctnt_id_exists(
54 p_lgl_ctnt_rec.obj_lgl_ctnt_id,
55 p_lgl_ctnt_rec.object_version_number) = false
56 THEN
57 RAISE FND_API.g_exc_error;
58 END IF;
59
60 DELETE FROM ibe_dsp_obj_lgl_ctnt
61 WHERE obj_lgl_ctnt_id = p_lgl_ctnt_rec.obj_lgl_ctnt_id
62 AND object_version_number = p_lgl_ctnt_rec.object_version_number;
63 ELSE
64 RAISE ibe_dspmgrvalidation_grp.lglctnt_id_req_exception;
65 END IF;
66
67 --- Check if the caller requested to commit ,
68 --- If p_commit set to true, commit the transaction
69
70 IF FND_API.to_boolean(p_commit)
71 THEN
72 COMMIT;
73 END IF;
74
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 FND_MSG_PUB.count_and_get(
78 p_encoded => FND_API.g_false,
79 p_count => x_msg_count,
80 p_data => x_msg_data );
81 EXCEPTION
82 WHEN FND_API.g_exc_error THEN
83 ROLLBACK TO delete_logical_content;
84 x_return_status := FND_API.g_ret_sts_error;
85 FND_MSG_PUB.count_and_get(
86 p_encoded => FND_API.g_false,
87 p_count => x_msg_count,
88 p_data => x_msg_data );
89
90 WHEN FND_API.g_exc_unexpected_error THEN
91 ROLLBACK TO delete_logical_content;
92 x_return_status := FND_API.g_ret_sts_error ;
93 FND_MSG_PUB.count_and_get(
94 p_encoded => FND_API.g_false,
95 p_count => x_msg_count,
96 p_data => x_msg_data );
97
98 WHEN ibe_dspmgrvalidation_grp.lglctnt_id_req_exception THEN
99 ROLLBACK TO delete_logical_content;
100 x_return_status := FND_API.g_ret_sts_error;
101 FND_MESSAGE.set_name('IBE','IBE_DSP_LGLCTNT_ID_REQ');
102 FND_MSG_PUB.ADD;
103 FND_MSG_PUB.count_and_get(
104 p_encoded => FND_API.g_false,
105 p_count => x_msg_count,
106 p_data => x_msg_data );
107
108 WHEN OTHERS THEN
109 ROLLBACK TO delete_logical_content;
110 x_return_status := FND_API.g_ret_sts_unexp_error ;
111 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
112 THEN
113 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
114 END IF;
115 FND_MSG_PUB.count_and_get(
116 p_encoded => FND_API.g_false,
117 p_count => x_msg_count,
118 p_data => x_msg_data);
119 END delete_logical_content;
120
121 PROCEDURE save_logical_content(
122 p_api_version IN NUMBER,
123 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
124 p_commit IN VARCHAR2 := FND_API.g_false,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2,
128 p_object_type IN VARCHAR2,
129 p_lgl_ctnt_rec IN OBJ_LGL_CTNT_REC_TYPE
130 )
131 IS
132 l_api_name CONSTANT VARCHAR2(30) := 'save_logical_content';
133 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
134 l_return_status VARCHAR2(1);
135 l_index NUMBER ;
136 l_context_id NUMBER;
137 l_deliverable_id NUMBER := null;
138 l_exists NUMBER := null;
139 l_context_type VARCHAR2(100);
140 l_obj_lgl_ctnt_id NUMBER;
141 l_object_type VARCHAR2(40);
142 l_applicable_to VARCHAR2(40);
143 BEGIN
144 --------------------- initialize -----------------------+
145 SAVEPOINT save_logical_content;
146
147 IF NOT FND_API.compatible_api_call(
148 g_api_version,
149 p_api_version,
150 l_api_name,
151 g_pkg_name )
152 THEN
153 RAISE FND_API.g_exc_unexpected_error;
154 END IF;
155
156 IF FND_API.to_boolean(p_init_msg_list)
157 THEN
158 FND_MSG_PUB.initialize;
159 END IF;
160
161 --- Initialize API return status to success
162 x_return_status := FND_API.g_ret_sts_success;
163
164 --- check for existence of the object id
165 l_object_type := trim(p_object_type);
166
167 IF l_object_type = 'I' AND
168 p_lgl_ctnt_rec.obj_lgl_ctnt_id IS NULL AND
169 p_lgl_ctnt_rec.deliverable_id IS NULL
170 THEN
171 IF FND_API.to_boolean(p_commit)
172 THEN
173 COMMIT;
174 END IF;
175
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178 FND_MSG_PUB.count_and_get(
179 p_encoded => FND_API.g_false,
180 p_count => x_msg_count,
181 p_data => x_msg_data );
182
183 RETURN;
184 END IF;
185
186 IF ibe_dspmgrvalidation_grp.check_lgl_object_exists(
187 p_object_type,p_lgl_ctnt_rec.object_id) = false
188 THEN
189 RAISE FND_API.g_exc_error;
190 END IF;
191
192 --- check if the context exists
193 IF p_lgl_ctnt_rec.context_id IS NOT NULL OR
194 p_lgl_ctnt_rec.context_id <> FND_API.g_miss_num
195 THEN
196 l_context_type := ibe_dspmgrvalidation_grp.check_context_type_code(
197 p_lgl_ctnt_rec.context_id);
198
199 IF l_context_type IS NULL
200 THEN
201 RAISE FND_API.g_exc_error;
202 END IF;
203 ELSE
204 RAISE ibe_dspmgrvalidation_grp.context_req_exception;
205 END IF;
206
207 --- check if the deliverable exists
208 --- if the deliverable is passed, make sure the type is the same as
209 --- context type
210 -- Do not add error message to the message stack
211 -- IF l_context_type = 'TEMPLATE' AND
212 -- p_object_type = 'S'
213 -- THEN
214 -- FND_MESSAGE.set_name('IBE','IBE_DSP_LGLCTNT_SCT_INVLD');
215 -- FND_MSG_PUB.ADD;
216 -- END IF;
217
218 IF (p_lgl_ctnt_rec.deliverable_id IS NOT NULL)
219 AND (l_context_type = 'MEDIA')
220 THEN
221 IF(l_object_type = 'I')
222 THEN
223 l_applicable_to := 'CATEGORY';
224 ELSIF (l_object_type = 'C')
225 THEN
226 l_applicable_to := 'CATEGORY';
227 ELSIF (l_object_type = 'S')
228 THEN
229 l_applicable_to := 'SECTION';
230 END IF;
231
232 IF ibe_dspmgrvalidation_grp.check_deliverable_type_exists(
233 p_lgl_ctnt_rec.deliverable_id,
234 l_context_type,
235 l_applicable_to) = false
236 THEN
237 RAISE FND_API.g_exc_error;
238 END IF;
239
240 END IF;
241
242 IF l_object_type = 'I' AND
243 p_lgl_ctnt_rec.obj_lgl_ctnt_id IS NOT NULL AND
244 p_lgl_ctnt_rec.deliverable_id = null
245 THEN
246 --- Check if the object logical content id exists
247 IF ibe_dspmgrvalidation_grp.check_lgl_ctnt_id_exists(
248 p_lgl_ctnt_rec.obj_lgl_ctnt_id,
249 p_lgl_ctnt_rec.object_version_number) = false
250 THEN
251 RAISE FND_API.g_exc_error;
252 END IF;
253
254 DELETE FROM IBE_DSP_OBJ_LGL_ctnt
255 WHERE obj_lgl_ctnt_id = p_lgl_ctnt_rec.obj_lgl_ctnt_id
256 AND object_version_number = p_lgl_ctnt_rec.object_version_number
257 AND object_type = l_object_type;
258 ELSE
259 IF p_lgl_ctnt_rec.obj_lgl_ctnt_id IS NULL
260 THEN
261 OPEN obj_lgl_ctnt_id_seq;
262 FETCH obj_lgl_ctnt_id_seq INTO l_obj_lgl_ctnt_id;
263 CLOSE obj_lgl_ctnt_id_seq;
264
265 INSERT INTO IBE_DSP_OBJ_LGL_CTNT (
266 OBJ_LGL_CTNT_ID,
267 OBJECT_VERSION_NUMBER,
268 LAST_UPDATE_DATE,
269 LAST_UPDATED_BY,
270 CREATION_DATE,
271 CREATED_BY,
272 LAST_UPDATE_LOGIN,
273 OBJECT_ID,
274 OBJECT_TYPE,
275 CONTEXT_ID,
276 ITEM_ID )
277 VALUES (
278 l_obj_lgl_ctnt_id,
279 1,
280 SYSDATE,
281 FND_GLOBAL.user_id,
282 SYSDATE,
283 FND_GLOBAL.user_id,
284 FND_GLOBAL.user_id,
285 p_lgl_ctnt_rec.object_id,
286 l_object_type,
287 p_lgl_ctnt_rec.context_id,
288 p_lgl_ctnt_rec.deliverable_id);
289 ELSE
290 --- Check if the object logical content id exists
291 IF ibe_dspmgrvalidation_grp.check_lgl_ctnt_id_exists(
292 p_lgl_ctnt_rec.obj_lgl_ctnt_id,
293 p_lgl_ctnt_rec.object_version_number) = false
294 THEN
295 RAISE FND_API.g_exc_error;
296 END IF;
297
298 UPDATE IBE_DSP_OBJ_LGL_CTNT
299 SET LAST_UPDATE_DATE = SYSDATE,
300 LAST_UPDATED_BY = FND_GLOBAL.user_id,
301 LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
302 OBJECT_ID = p_lgl_ctnt_rec.object_id,
303 OBJECT_TYPE = l_object_type,
304 CONTEXT_id = p_lgl_ctnt_rec.context_id,
305 ITEM_id = p_lgl_ctnt_rec.deliverable_id ,
306 OBJECT_VERSION_NUMBER = p_lgl_ctnt_rec.object_version_number + 1
307 WHERE OBJ_LGL_CTNT_id = p_lgl_ctnt_rec.obj_lgl_ctnt_id
308 AND OBJECT_VERSION_NUMBER = p_lgl_ctnt_rec.object_version_number;
309 END IF;
310 END IF;
311 --- Check if the caller requested to commit ,
312 --- If p_commit set to true, commit the transaction
313
314 IF FND_API.to_boolean(p_commit)
315 THEN
316 COMMIT;
317 END IF;
318
319 x_return_status := FND_API.G_RET_STS_SUCCESS;
320
321 FND_MSG_PUB.count_and_get(
322 p_encoded => FND_API.g_false,
323 p_count => x_msg_count,
324 p_data => x_msg_data );
325 EXCEPTION
326 WHEN FND_API.g_exc_error THEN
327 ROLLBACK TO save_logical_content;
328 x_return_status := FND_API.g_ret_sts_error;
329 FND_MSG_PUB.count_and_get(
330 p_encoded => FND_API.g_false,
331 p_count => x_msg_count,
332 p_data => x_msg_data );
333
334 WHEN DUP_VAL_ON_INDEX THEN
335 ROLLBACK TO save_logical_content;
336 x_return_status := FND_API.g_ret_sts_error ;
337 FND_MESSAGE.set_name('IBE','IBE_DSP_LGLCTNT_ROW_EXISTS');
338 FND_MESSAGE.set_token('ID', p_lgl_ctnt_rec.object_id);
339 FND_MESSAGE.set_token('TYPE', p_object_type);
340 FND_MESSAGE.set_token('CTX_ID', p_lgl_ctnt_rec.context_id);
341 FND_MSG_PUB.ADD;
342 FND_MSG_PUB.count_and_get(
343 p_encoded => FND_API.g_false,
344 p_count => x_msg_count,
345 p_data => x_msg_data );
346
347 WHEN FND_API.g_exc_unexpected_error THEN
348 ROLLBACK TO save_logical_content;
349 x_return_status := FND_API.g_ret_sts_error ;
350 FND_MSG_PUB.count_and_get(
351 p_encoded => FND_API.g_false,
352 p_count => x_msg_count,
353 p_data => x_msg_data );
354
355 WHEN ibe_dspmgrvalidation_grp.context_req_exception THEN
356 ROLLBACK TO save_logical_content;
357 x_return_status := FND_API.g_ret_sts_unexp_error ;
358 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
359 THEN
360 FND_MESSAGE.set_name('IBE','IBE_DSP_CONTEXT_REQ');
361 FND_MSG_PUB.ADD;
362 END IF;
363 FND_MSG_PUB.count_and_get(
364 p_encoded => FND_API.g_false,
365 p_count => x_msg_count,
366 p_data => x_msg_data );
367
368 WHEN OTHERS THEN
369 ROLLBACK TO save_logical_content;
370 x_return_status := FND_API.g_ret_sts_unexp_error ;
371 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
372 THEN
373 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
374 END IF;
375 FND_MSG_PUB.count_and_get(
376 p_encoded => FND_API.g_false,
377 p_count => x_msg_count,
378 p_data => x_msg_data );
379 END save_logical_content;
380
381 PROCEDURE save_delete_lgl_ctnt(
382 p_api_version IN NUMBER,
383 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
384 p_commit IN VARCHAR2 := FND_API.g_false,
385 x_return_status OUT NOCOPY VARCHAR2,
386 x_msg_count OUT NOCOPY NUMBER,
387 x_msg_data OUT NOCOPY VARCHAR2,
388 p_object_type_code IN VARCHAR2,
389 p_lgl_ctnt_tbl IN OBJ_LGL_CTNT_TBL_TYPE )
390 IS
391 l_api_name CONSTANT VARCHAR2(30) := 'save_delete_itm_lgl_ctnt';
392 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
393 l_return_status VARCHAR2(1);
394 l_index NUMBER ;
395 l_context_id NUMBER;
396 l_deliverable_id NUMBER := null;
397 l_msg_count NUMBER;
398 l_msg_data VARCHAR2(80);
399 l_object_type_code VARCHAR2(1) := null;
400 BEGIN
401 --------------------- initialize -----------------------+
402 SAVEPOINT save_delete_itm_lgl_ctnt;
403
404 IF NOT FND_API.compatible_api_call(
405 g_api_version,
406 p_api_version,
407 l_api_name,
408 g_pkg_name )
409 THEN
410 RAISE FND_API.g_exc_unexpected_error;
411 END IF;
412
413 IF FND_API.to_boolean(p_init_msg_list)
414 THEN
415 FND_MSG_PUB.initialize;
416 END IF;
417
418 --- Initialize API return status to success
419 x_return_status := FND_API.g_ret_sts_success;
420
421 IF ibe_dspmgrvalidation_grp.check_valid_object_type(
422 p_object_type_code) = false
423 THEN
424 RAISE FND_API.g_exc_error;
425 END IF;
426
427 FOR l_index IN 1..p_lgl_ctnt_tbl.COUNT
428 LOOP
429 IF p_lgl_ctnt_tbl(l_index).obj_lgl_ctnt_delete = FND_API.g_true
430 THEN
431 delete_logical_content(
432 p_api_version,
433 FND_API.g_false,
434 FND_API.g_false,
435 l_return_status,
436 l_msg_count,
437 l_msg_data,
438 p_object_type_code,
439 p_lgl_ctnt_tbl(l_index));
440
441 ELSIF p_lgl_ctnt_tbl(l_index).obj_lgl_ctnt_delete = FND_API.g_false
442 THEN
443 save_logical_content(
444 p_api_version,
445 FND_API.g_false,
446 p_commit,
447 l_return_status,
448 l_msg_count,
449 l_msg_data,
450 p_object_type_code,
451 p_lgl_ctnt_tbl(l_index));
452 END IF;
453
454 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
455 THEN
456 x_return_status := l_return_status;
457 END IF;
458 END LOOP;
459
460 FND_MSG_PUB.count_and_get(
461 p_encoded => FND_API.g_false,
462 p_count => x_msg_count,
463 p_data => x_msg_data );
464 EXCEPTION
465 WHEN FND_API.g_exc_error THEN
466 ROLLBACK TO save_delete_itm_lgl_ctnt;
467 x_return_status := FND_API.g_ret_sts_error;
468 FND_MSG_PUB.count_and_get(
469 p_encoded => FND_API.g_false,
470 p_count => x_msg_count,
471 p_data => x_msg_data );
472
473 WHEN FND_API.g_exc_unexpected_error THEN
474 ROLLBACK TO save_delete_itm_lgl_ctnt;
475 x_return_status := FND_API.g_ret_sts_error ;
476 FND_MSG_PUB.count_and_get(
477 p_encoded => FND_API.g_false,
478 p_count => x_msg_count,
479 p_data => x_msg_data );
480
481 WHEN OTHERS THEN
482 ROLLBACK TO save_delete_itm_lgl_ctnt;
483 x_return_status := FND_API.g_ret_sts_unexp_error ;
484 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
485 THEN
486 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
487 END IF;
488 FND_MSG_PUB.count_and_get(
489 p_encoded => FND_API.g_false,
490 p_count => x_msg_count,
491 p_data => x_msg_data );
492 END save_delete_lgl_ctnt;
493
494 PROCEDURE delete_section(p_section_id IN NUMBER )
495 IS
496 l_api_name CONSTANT VARCHAR2(30) := 'delete_section';
497 BEGIN
498 SAVEPOINT delete_section;
499
500 IF ibe_dspmgrvalidation_grp.check_section_exists(
501 p_section_id) = false
502 THEN
503 RAISE FND_API.g_exc_error;
504 END IF;
505
506 DELETE FROM IBE_DSP_OBJ_LGL_ctnt
507 WHERE OBJECT_TYPE = 'S'
508 AND OBJECT_ID = p_section_id;
509 EXCEPTION
510 WHEN OTHERS THEN
511 ROLLBACK TO delete_section;
512 END delete_section;
513
514 PROCEDURE delete_deliverable(p_deliverable_id IN NUMBER)
515 IS
516 l_api_name CONSTANT VARCHAR2(30) := 'delete_deliverable';
517 BEGIN
518 SAVEPOINT delete_deliverable;
519
520 IF p_deliverable_id IS NOT NULL
521 THEN
522 UPDATE IBE_DSP_OBJ_LGL_CTNT SET
523 ITEM_ID = null where
524 ITEM_ID = p_deliverable_id ;
525 END IF;
526 EXCEPTION
527 WHEN OTHERS THEN
528 ROLLBACK TO delete_deliverable;
529 END delete_deliverable;
530
531 PROCEDURE delete_category(p_category_id IN NUMBER)
532 IS
533 l_api_name CONSTANT VARCHAR2(30) := 'delete_category';
534 BEGIN
535 -- SAVEPOINT delete_category;
536 IF p_category_id IS NOT NULL
537 THEN
538 DELETE FROM IBE_DSP_OBJ_LGL_ctnt
539 WHERE OBJECT_TYPE = 'C'
540 AND OBJECT_ID = p_category_id;
541 END IF;
542 END delete_category;
543
544 PROCEDURE delete_item(p_item_id IN NUMBER )
545 IS
546 l_api_name CONSTANT VARCHAR2(30) := 'delete_item';
547 BEGIN
548 -- SAVEPOINT delete_item;
549 IF p_item_id IS NOT NULL
550 THEN
551 DELETE FROM IBE_DSP_OBJ_LGL_ctnt
552 WHERE OBJECT_TYPE = 'I'
553 AND OBJECT_id = p_item_id;
554 END IF;
555 END delete_item;
556
557 PROCEDURE delete_context(p_context_id IN NUMBER )
558 IS
559 l_api_name CONSTANT VARCHAR2(30) := 'delete_context';
560 BEGIN
561 SAVEPOINT delete_context;
562
563 IF p_context_id IS NOT NULL
564 THEN
565 DELETE IBE_DSP_OBJ_LGL_CTNT
566 WHERE context_id = p_context_id;
567 END IF;
568 EXCEPTION
569 WHEN OTHERS THEN
570 ROLLBACK TO delete_context;
571 END delete_context;
572
573 PROCEDURE delete_category_dlv(
574 p_category_id IN NUMBER,
575 p_deliverable_id IN NUMBER )
576 IS
577 l_api_name CONSTANT VARCHAR2(30) := 'delete_category_dlv';
578 BEGIN
579 SAVEPOINT delete_category_dlv;
580
581 IF p_category_id IS NOT NULL AND
582 p_deliverable_id IS NOT NULL
583 THEN
584 UPDATE IBE_DSP_OBJ_LGL_CTNT
585 SET item_id = NULL
586 WHERE object_type = 'C'
587 AND object_id = p_category_id
588 AND item_id = p_deliverable_id;
589 END IF;
590 EXCEPTION
591 WHEN OTHERS THEN
592 ROLLBACK TO delete_category_dlv;
593 END delete_category_dlv;
594 END IBE_LogicalContent_GRP;