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