[Home] [Help]
PACKAGE BODY: APPS.IBE_TPLCATEGORY_GRP
Source
1 PACKAGE BODY IBE_TplCategory_GRP AS
2 /* $Header: IBEGTCGB.pls 115.1 2002/12/14 07:50:59 schak ship $ */
3
4
5 -- HISTORY
6 -- 12/12/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
7 -- *********************************************************************************
8
9 ---- Generate primary key from sequence
10 CURSOR dsp_tpl_seq IS
11 SELECT ibe_dsp_tpl_ctg_s1.NEXTVAL
12 FROM DUAL;
13
14 ---------------------------------------------------------------------+
15 -- NOTES
16 -- 1. Raises an exception if the api_version is not valid
17 -- 2. Raises an exception if the category id does not exist
18 -- 3. Raises an exception if the template_id is missing or invalid
19 -- The template_id should have DELIVERABLE_TYPE_CODE = TEMPLATE
20 -- and APPLICABLE_TO_CODE = CATEGORY (JTF_AMV_ITEMS_B)
21 -- 4. If the template-category relationship already exists,
22 -- no error is raised
23 ---------------------------------------------------------------------+
24 PROCEDURE add_tpl_ctg_rec(
25 p_api_version IN NUMBER,
26 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
27 p_commit IN VARCHAR2 := FND_API.g_false,
28 x_return_status OUT NOCOPY VARCHAR2,
29 p_template_id IN NUMBER,
30 p_category_id IN NUMBER )
31 IS
32 l_api_name CONSTANT VARCHAR2(30) := 'add_tpl_ctg_rec';
33 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
34 l_dsp_tpl_seq_id NUMBER;
35 l_category_id NUMBER;
36 l_template_id NUMBER;
37 l_row_exists NUMBER;
38 l_type VARCHAR2(30) := 'TEMPLATE';
39 l_applicable_to VARCHAR2(30) := 'CATEGORY';
40 BEGIN
41 SAVEPOINT add_tpl_ctg_rec;
42
43 IF NOT FND_API.compatible_api_call(
44 g_api_version,
45 p_api_version,
46 l_api_name,
47 g_pkg_name )
48 THEN
49 RAISE FND_API.g_exc_unexpected_error;
50 END IF;
51
52 IF FND_API.to_boolean(p_init_msg_list) THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55
56 x_return_status := FND_API.g_ret_sts_success;
57
58 --- Check if the deliverable id exists .
59 IF p_template_id <> FND_API.g_miss_num AND
60 p_template_id IS NOT NULL
61 THEN
62 IF ibe_dspmgrvalidation_grp.check_deliverable_type_exists(
63 p_template_id,l_type ,l_applicable_to)
64 THEN
65 IF p_category_id <> FND_API.g_miss_num AND
66 p_category_id IS NOT NULL
67 THEN
68 IF ibe_dspmgrvalidation_grp.check_category_exists(p_category_id)
69 THEN
70 IF ibe_dspmgrvalidation_grp.check_ctg_tpl_relation_exists(
71 p_category_id,
72 p_template_id) = false
73 THEN
74 OPEN dsp_tpl_seq;
75 FETCH dsp_tpl_seq INTO l_dsp_tpl_seq_id;
76 CLOSE dsp_tpl_seq;
77
78 INSERT INTO IBE_DSP_TPL_CTG (
79 TPL_CTG_ID,
80 OBJECT_VERSION_NUMBER,
81 LAST_UPDATE_DATE,
82 LAST_UPDATED_BY,
83 CREATION_DATE,
84 CREATED_BY,
85 LAST_UPDATE_LOGIN,
86 ITEM_ID,
87 CATEGORY_ID )
88 VALUES (
89 l_dsp_tpl_seq_id,
90 1,
91 SYSDATE,
92 FND_GLOBAL.user_id,
93 SYSDATE,
94 FND_GLOBAL.user_id,
95 FND_GLOBAL.user_id,
96 p_template_id,
97 p_category_id);
98
99 END IF; /* category - template relation not exists check */
100 ELSE
101 RAISE FND_API.g_exc_error;
102 END IF; /* category exists check */
103 ELSE
104 RAISE ibe_dspmgrvalidation_grp.category_req_exception;
105 END IF; /* category id is not null check */
106 ELSE
107 RAISE FND_API.g_exc_error;
108 END IF;/* deliverable exists check */
109 ELSE
110 RAISE ibe_dspmgrvalidation_grp.template_req_exception;
111 END IF; /*deliverable id is not null check */
112
113 --- Check if the caller requested to commit ,
114 --- If p_commit set to true, commit the transaction
115 IF FND_API.to_boolean(p_commit)
116 THEN
117 COMMIT;
118 END IF;
119
120 x_return_status := FND_API.g_ret_sts_success;
121 EXCEPTION
122 WHEN FND_API.g_exc_error THEN
123 ROLLBACK TO add_tpl_ctg_rec;
124 x_return_status := FND_API.g_ret_sts_error;
125
126 WHEN FND_API.g_exc_unexpected_error THEN
127 ROLLBACK TO add_tpl_ctg_rec;
128 x_return_status := FND_API.g_ret_sts_unexp_error ;
129
130 WHEN ibe_dspmgrvalidation_grp.category_req_exception THEN
131 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
132 THEN
133 x_return_status := FND_API.g_ret_sts_error;
134 FND_MESSAGE.set_name('IBE','IBE_DSP_CATEGORY_REQ');
135 FND_MSG_PUB.add;
136 END IF;
137
138 WHEN ibe_dspmgrvalidation_grp.template_req_exception THEN
139 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
140 THEN
141 x_return_status := FND_API.g_ret_sts_error;
142 FND_MESSAGE.set_name('IBE','IBE_DSP_TEMPLATE_REQ');
143 FND_MSG_PUB.add;
144 END IF;
145
146 WHEN OTHERS THEN
147 ROLLBACK TO add_tpl_ctg_rec;
148 x_return_status := FND_API.g_ret_sts_unexp_error ;
149
150 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
151 THEN
152 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
153 END IF;
154 END add_tpl_ctg_rec;
155 -----------------------------------------------------------------------+
156 -- NOTES
157 -- 1. Raises an exception if the api_version is not valid
158 -- 2. Raises an exception if the template_id is missing or invalid
159 -- The template_id should have DELIVERABLE_TYPE_CODE = TEMPLATE
160 -- and APPLICABLE_TO_CODE = CATEGORY (JTF_AMV_ITEMS_B)
161 -- 3. Raises an exception if any invalid category is passed in
162 -- p_category_id_tbl
163 --
164 ---------------------------------------------------------------------+
165 PROCEDURE add_tpl_ctg(
166 p_api_version IN NUMBER,
167 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
168 p_commit IN VARCHAR2 := FND_API.g_false,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2,
172 p_template_id IN NUMBER,
173 p_category_id_tbl IN category_ID_TBL_TYPE )
174 IS
175 l_api_name CONSTANT VARCHAR2(30) := 'add_tpl_ctg';
176 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
177 l_return_status VARCHAR2(1);
178 l_deliverable_id NUMBER;
179 l_category_id NUMBER;
180 l_dsp_tpl_seq_id NUMBER;
181 l_index NUMBER;
182 l_row_exists NUMBER;
183 l_type VARCHAR2(30) := 'TEMPLATE';
184 l_applicable_to VARCHAR2(30) := 'CATEGORY';
185 BEGIN
186 --------------------- initialize -----------------------+
187 SAVEPOINT add_tpl_ctg;
188 IF NOT FND_API.compatible_api_call(
189 g_api_version,
190 p_api_version,
191 l_api_name,
192 g_pkg_name )
193 THEN
194 RAISE FND_API.g_exc_unexpected_error;
195 END IF;
196
197 IF FND_API.to_boolean(p_init_msg_list)
198 THEN
199 FND_MSG_PUB.initialize;
200 END IF;
201
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204 IF p_template_id IS NOT NULL AND
205 p_template_id <> FND_API.g_miss_num
206 THEN
207 IF ibe_dspmgrvalidation_grp.check_deliverable_type_exists(
208 p_template_id ,
209 l_type,
210 l_applicable_to) = false
211 THEN
212 RAISE FND_API.g_exc_error;
213 END IF;/* deliverable exists check */
214 ELSE
215 RAISE ibe_dspmgrvalidation_grp.template_req_exception;
216 END IF; /*deliverable id is not null check */
217
218 FOR l_index IN 1..p_category_id_tbl.COUNT
219 LOOP
220 add_tpl_ctg_rec(
221 p_api_version,
222 FND_API.g_false,
223 FND_API.g_false,
224 l_return_status ,
225 p_template_id,
226 p_category_id_tbl(l_index));
227
228 IF l_return_status <> FND_API.g_ret_sts_success
229 THEN
230 x_return_status := l_return_status;
231 END IF;
232 END LOOP;
233
234 --- Check if the caller requested to commit ,
235 --- If p_commit set to true, commit the transaction
236 IF FND_API.to_boolean(p_commit)
237 THEN
238 COMMIT;
239 END IF;
240
241 FND_MSG_PUB.count_and_get(
242 p_encoded => FND_API.g_false,
243 p_count => x_msg_count,
244 p_data => x_msg_data );
245 EXCEPTION
246 WHEN FND_API.g_exc_error THEN
247 ROLLBACK TO add_tpl_ctg;
248 x_return_status := FND_API.g_ret_sts_error;
249 FND_MSG_PUB.count_and_get(
250 p_encoded => FND_API.g_false,
251 p_count => x_msg_count,
252 p_data => x_msg_data );
253
254 WHEN FND_API.g_exc_unexpected_error THEN
255 ROLLBACK TO add_tpl_ctg;
256 x_return_status := FND_API.g_ret_sts_unexp_error ;
257 FND_MSG_PUB.count_and_get(
258 p_encoded => FND_API.g_false,
259 p_count => x_msg_count,
260 p_data => x_msg_data );
261
262 WHEN ibe_dspmgrvalidation_grp.template_req_exception THEN
263 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
264 THEN
265 x_return_status := FND_API.g_ret_sts_error;
266 FND_MESSAGE.set_name('IBE','IBE_DSP_TEMPLATE_REQ');
267 FND_MSG_PUB.add;
268 END IF;
269 FND_MSG_PUB.count_and_get(
270 p_encoded => FND_API.g_false,
271 p_count => x_msg_count,
272 p_data => x_msg_data );
273
274 WHEN OTHERS THEN
275 ROLLBACK TO add_tpl_ctg;
276 x_return_status := FND_API.g_ret_sts_unexp_error ;
277
278 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
279 THEN
280 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
281 END IF;
282
283 FND_MSG_PUB.count_and_get(
284 p_encoded => FND_API.g_false,
285 p_count => x_msg_count,
286 p_data => x_msg_data );
287 END add_tpl_ctg;
288
289 -----------------------------------------------------------------------+
290 -- NOTES
291 -- 1. Raise exception if the p_api_version doesn't match.
292 -- 2. Deletes the association of the template to the category
293 -- 3. Deletes the category to template association in IBE_OBJ_LGL_CTNT
294 -- for all display contexts
295 --------------------------------------------------------------------+
296 PROCEDURE delete_tpl_ctg_relation(
297 p_api_version IN NUMBER,
298 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
299 p_commit IN VARCHAR2 := FND_API.g_false,
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_msg_count OUT NOCOPY NUMBER,
302 x_msg_data OUT NOCOPY VARCHAR2,
303 p_tpl_ctg_id_tbl IN tpl_ctg_id_TBL_TYPE )
304 IS
305 l_api_name CONSTANT VARCHAR2(30) := 'delete_tpl_ctg_relation';
306 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
307 l_category_id NUMBER ;
308 l_deliverable_id NUMBER;
309 l_index NUMBER;
310
311 CURSOR category_cur(p_tpl_ctg_id IN NUMBER) IS
312 SELECT category_id ,ITEM_ID
313 FROM ibe_dsp_tpl_ctg
314 WHERE tpl_ctg_id = p_tpl_ctg_id;
315 BEGIN
316 --------------------- initialize -----------------------+
317 SAVEPOINT delete_tpl_ctg_relation;
318 IF NOT FND_API.compatible_api_call(
319 g_api_version,
320 p_api_version,
321 l_api_name,
322 g_pkg_name )
323 THEN
324 RAISE FND_API.g_exc_unexpected_error;
325 END IF;
326
327 IF FND_API.to_boolean(p_init_msg_list)
328 THEN
329 FND_MSG_PUB.initialize;
330 END IF;
331
332 x_return_status := FND_API.G_RET_STS_SUCCESS;
333
334 FOR l_index IN 1..p_tpl_ctg_id_tbl.COUNT
335 LOOP
336 BEGIN
337 SAVEPOINT delete_ctg_relation;
338 --- Check if the context_id exists
339 IF p_tpl_ctg_id_tbl(l_index) <> FND_API.g_miss_num AND
340 p_tpl_ctg_id_tbl(l_index) IS NOT NULL
341 THEN
342 --- Delete all the entries matching category id and deliverable id
343 OPEN category_cur(p_tpl_ctg_id_tbl(l_index) );
344 FETCH category_cur into l_category_id,l_deliverable_id;
345 CLOSE category_cur;
346
347 DELETE FROM IBE_DSP_TPL_CTG
348 WHERE TPL_CTG_ID = p_tpl_ctg_id_tbl(l_index);
349
350 IBE_LogicalContent_grp.delete_category_dlv(
351 l_category_id,
352 l_deliverable_id);
353 END IF;
354 EXCEPTION
355 WHEN OTHERS THEN
356 ROLLBACK TO delete_ctg_relation;
357 x_return_status := FND_API.g_ret_sts_error;
358 END;
359 END LOOP;
360
361 --- Check if the caller requested to commit ,
362 --- If p_commit set to true, commit the transaction
363 IF FND_API.to_boolean(p_commit)
364 THEN
365 COMMIT;
366 END IF;
367
368 FND_MSG_PUB.count_and_get(
369 p_encoded => FND_API.g_false,
370 p_count => x_msg_count,
371 p_data => x_msg_data );
372 EXCEPTION
373 WHEN FND_API.g_exc_error THEN
374 ROLLBACK TO delete_tpl_ctg_relation;
375 x_return_status := FND_API.g_ret_sts_error;
376 FND_MSG_PUB.count_and_get(
377 p_encoded => FND_API.g_false,
378 p_count => x_msg_count,
379 p_data => x_msg_data );
380
381 WHEN FND_API.g_exc_unexpected_error THEN
382 ROLLBACK TO delete_tpl_ctg_relation;
383 x_return_status := FND_API.g_ret_sts_unexp_error ;
384 FND_MSG_PUB.count_and_get(
385 p_encoded => FND_API.g_false,
386 p_count => x_msg_count,
387 p_data => x_msg_data );
388
389 WHEN OTHERS THEN
390 ROLLBACK TO delete_tpl_ctg_relation;
391 x_return_status := FND_API.g_ret_sts_unexp_error ;
392
393 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
394 THEN
395 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
396 END IF;
397
398 FND_MSG_PUB.count_and_get(
399 p_encoded => FND_API.g_false,
400 p_count => x_msg_count,
401 p_data => x_msg_data );
402 END delete_tpl_ctg_relation;
403
404 -----------------------------------------------------------------------+
405 -- NOTES
406 -- 1. Raises an exception if the api_version is not valid
407 -- 2. Raises an exception if the category_id is missing or invalid
411 ---------------------------------------------------------------------+
408 -- 3. Raises an exception if any invalid template_id is passed in
409 -- p_template_id_tbl
410 -- 4. Creates a category to templates relationship (IBE_DSP_TPL_CTG)
412 PROCEDURE add_ctg_tpl(
413 p_api_version IN NUMBER,
414 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
415 p_commit IN VARCHAR2 := FND_API.g_false,
416 x_return_status OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER,
418 x_msg_data OUT NOCOPY VARCHAR2,
419 p_category_id IN NUMBER,
420 p_template_id_tbl IN template_ID_TBL_TYPE )
421 IS
422 l_api_name CONSTANT VARCHAR2(30) := 'add_ctg_tpl';
423 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
424 l_return_status VARCHAR2(1);
425 l_category_id NUMBER ;
426 l_deliverable_id NUMBER;
427 l_dsp_tpl_seq_id NUMBER;
428 l_index NUMBER;
429 l_row_exists NUMBER;
430 BEGIN
431 --------------------- initialize -----------------------+
432 SAVEPOINT add_ctg_tpl;
433
434 IF NOT FND_API.compatible_api_call(
435 g_api_version,
436 p_api_version,
437 l_api_name,
438 g_pkg_name )
439 THEN
440 RAISE FND_API.g_exc_unexpected_error;
441 END IF;
442
443 IF FND_API.to_boolean(p_init_msg_list)
444 THEN
445 FND_MSG_PUB.initialize;
446 END IF;
447
448 x_return_status := FND_API.g_ret_sts_success;
449
450 IF p_category_id <> FND_API.g_miss_num OR
451 p_category_id IS NOT NULL
452 THEN
453 IF ibe_dspmgrvalidation_grp.check_category_exists(
454 p_category_id) = false
455 THEN
456 RAISE FND_API.g_exc_error;
457 END IF; /* category exists check */
458 ELSE
459 RAISE ibe_dspmgrvalidation_grp.category_req_exception;
460 end if; /* category id is not null check */
461
462 --- Add all the entries
463 FOR l_index IN 1..p_template_id_tbl.COUNT
464 LOOP
465 add_tpl_ctg_rec(
466 p_api_version,
467 FND_API.g_false,
468 FND_API.g_false,
469 l_return_status ,
470 p_template_id_tbl(l_index),
471 p_category_id);
472
473 IF l_return_status <> FND_API.g_ret_sts_success
474 THEN
475 x_return_status := l_return_status;
476 END IF;
477 END LOOP;
478
479 --- Check if the caller requested to commit ,
480 --- If p_commit set to true, commit the transaction
481 IF FND_API.to_boolean(p_commit)
482 THEN
483 COMMIT;
484 END IF;
485
486 FND_MSG_PUB.count_and_get(
487 p_encoded => FND_API.g_false,
488 p_count => x_msg_count,
489 p_data => x_msg_data );
490 EXCEPTION
491 WHEN FND_API.g_exc_error THEN
492 ROLLBACK TO add_ctg_tpl;
493 x_return_status := FND_API.g_ret_sts_error;
494 FND_MSG_PUB.count_and_get(
495 p_encoded => FND_API.g_false,
496 p_count => x_msg_count,
497 p_data => x_msg_data );
498
499 WHEN FND_API.g_exc_unexpected_error THEN
500 ROLLBACK TO add_ctg_tpl;
501 x_return_status := FND_API.g_ret_sts_unexp_error ;
502 FND_MSG_PUB.count_and_get(
503 p_encoded => FND_API.g_false,
504 p_count => x_msg_count,
505 p_data => x_msg_data );
506
507 WHEN ibe_dspmgrvalidation_grp.category_req_exception THEN
508 ROLLBACK TO add_ctg_tpl;
509 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
510 THEN
511 x_return_status := FND_API.g_ret_sts_error;
512 FND_MESSAGE.set_name('IBE','IBE_DSP_CATEGORY_REQ');
513 FND_MSG_PUB.add;
514 END IF;
515 FND_MSG_PUB.count_and_get(
516 p_encoded => FND_API.g_false,
517 p_count => x_msg_count,
518 p_data => x_msg_data );
519
520 WHEN OTHERS THEN
521 ROLLBACK TO add_ctg_tpl;
522 x_return_status := FND_API.g_ret_sts_unexp_error ;
523
524 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
525 THEN
526 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
527 END IF;
528
529 FND_MSG_PUB.count_and_get(
530 p_encoded => FND_API.g_false,
531 p_count => x_msg_count,
532 p_data => x_msg_data );
533 END ADD_CTG_TPL;
534
535 -----------------------------------------------------------------------+
536 -- NOTES
537 -- 1. Deletes all the category-template_id association for the
538 -- template_id passed
539 -- Note : This method should not be called from the application
540 ---------------------------------------------------------------------+
541 PROCEDURE delete_deliverable(p_template_id IN NUMBER )
542 IS
543 BEGIN
544 --------------------- initialize -----------------------+
545 SAVEPOINT delete_deliverable;
546
547 --- Delete the deliverable from the table
548 DELETE FROM ibe_dsp_tpl_ctg
549 WHERE item_id = p_template_id;
550 EXCEPTION
551 WHEN OTHERS THEN
552 ROLLBACK TO delete_deliverable;
553 END delete_deliverable;
554
555 -----------------------------------------------------------------------+
556 -- NOTES
557 -- 1. Deletes all the category-template_id association for the
558 -- category id passed
559 -- Note : This method should not be called from the application
560 ---------------------------------------------------------------------+
561 PROCEDURE delete_category(p_category_id IN NUMBER )
562 IS
563 BEGIN
564 --------------------- initialize -----------------------+
565 SAVEPOINT delete_category;
566
567 --- Delete the deliverable from the table
568 DELETE FROM ibe_dsp_tpl_ctg
569 WHERE category_id = p_category_id;
570 EXCEPTION
571 WHEN OTHERS THEN
572 ROLLBACK TO delete_category;
573 END delete_category;
574 END IBE_TplCategory_GRP;