[Home] [Help]
PACKAGE BODY: APPS.POR_IFT_ADMIN_PKG
Source
1 PACKAGE BODY por_ift_admin_pkg AS
2 /* $Header: PORIFTAB.pls 115.9 2003/08/25 17:26:17 liwang ship $ */
3
4 PROCEDURE insert_template(p_name IN VARCHAR2,
5 p_org_id IN NUMBER,
6 p_attach_cat_id IN NUMBER,
7 p_user_id IN NUMBER,
8 p_login_id IN NUMBER,
9 p_template_code IN OUT NOCOPY VARCHAR2,
10 p_row_id OUT NOCOPY VARCHAR2) IS
11 l_progress VARCHAR2(10) := '000';
12 l_template_code VARCHAR2(30) := NULL;
13 l_count_obj NUMBER;
14 BEGIN
15
16 l_progress := '001';
17 INSERT INTO POR_TEMPLATES_ALL_B (
18 TEMPLATE_CODE,
19 ORG_ID,
20 ATTACHMENT_CATEGORY_ID,
21 CREATED_BY,
22 CREATION_DATE,
23 LAST_UPDATED_BY,
24 LAST_UPDATE_DATE,
25 LAST_UPDATE_LOGIN)
26 VALUES (
27 'IFT_'||to_char(por_templates_s.NEXTVAL),
28 p_org_id,
29 p_attach_cat_id,
30 p_user_id,
31 sysdate,
32 p_user_id,
33 sysdate,
34 p_login_id)
35 RETURNING TEMPLATE_CODE INTO l_template_code;
36
37
38 l_progress := '003';
39 INSERT INTO POR_TEMPLATES_ALL_TL(
40 TEMPLATE_CODE,
41 SOURCE_LANG,
42 LANGUAGE,
43 TEMPLATE_NAME,
44 CREATED_BY,
45 CREATION_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATE_LOGIN)
49 SELECT
50 l_template_code,
51 userenv('LANG'),
52 FL.language_code,
53 p_name,
54 p_user_id,
55 sysdate,
56 p_user_id,
57 sysdate,
58 p_login_id
59 FROM FND_LANGUAGES FL
60 WHERE FL.INSTALLED_FLAG IN ('B','I');
61
62 p_template_code := l_template_code;
63
64 l_progress := '006';
65 SELECT row_id
66 INTO p_row_id
67 FROM por_templates_v
68 WHERE template_code = l_template_code;
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 po_message_s.sql_error('por_ift_admin_pkg.insert_template', l_progress, SQLCODE);
73 RAISE app_exception.application_exception;
74 END insert_template;
75
76
77 PROCEDURE lock_template(p_row_id IN VARCHAR2,
78 p_template_code IN VARCHAR2,
79 p_name IN VARCHAR2,
80 p_user_id IN NUMBER,
81 p_login_id IN NUMBER) IS
82 CURSOR c_rec(p_row_id VARCHAR2) IS
83 SELECT *
84 FROM por_templates_all_b
85 WHERE rowid = p_row_id
86 FOR UPDATE NOWAIT;
87
88 CURSOR c_tl_rec(p_template_code VARCHAR2) IS
89 SELECT
90 TEMPLATE_NAME,
91 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
92 from POR_TEMPLATES_ALL_TL
93 where TEMPLATE_CODE=p_template_code
94 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
95 for update nowait;
96 l_rec c_rec%ROWTYPE;
97 l_progress VARCHAR2(10) := '000';
98 BEGIN
99
100 l_progress := '001';
101 OPEN c_rec(p_row_id);
102
103 l_progress := '002';
104 FETCH c_rec INTO l_rec;
105
106 IF (c_rec%NOTFOUND) THEN
107 l_progress := '003';
108 CLOSE c_rec;
109 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
110 app_exception.raise_exception;
111 END IF;
112 CLOSE c_rec;
113
114 -- Note the use of NULL statements to handle possible
115 -- null values in the IF conditions.
116 l_progress := '004';
117 IF (l_rec.template_code = p_template_code) THEN
118 NULL;
119 ELSE
120 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
121 app_exception.raise_exception;
122 END IF;
123
124 l_progress := '005';
125 for tlinfo in c_tl_rec(p_template_code) loop
126 if (tlinfo.BASELANG = 'Y') then
127 if(tlinfo.template_name = p_name)
128 then
129 null;
130 else
131 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
132 app_exception.raise_exception;
133 end if;
134 end if;
135 end loop;
136 EXCEPTION
137 WHEN app_exception.application_exception THEN
138 RAISE;
139 WHEN OTHERS THEN
140 po_message_s.sql_error('por_ift_admin_pkg.lock_template', l_progress, SQLCODE);
141 RAISE app_exception.application_exception;
142 END lock_template;
143
144 PROCEDURE update_template(p_row_id IN VARCHAR2,
145 p_template_code IN VARCHAR2,
146 p_name IN VARCHAR2,
147 p_org_id IN NUMBER,
148 p_attach_cat_id IN NUMBER,
149 p_user_id IN NUMBER,
150 p_login_id IN NUMBER) IS
151 l_progress VARCHAR2(10) := '000';
152 BEGIN
153
154 l_progress := '001';
155 UPDATE POR_TEMPLATES_ALL_B
156 SET
157 ORG_ID = p_org_id,
158 ATTACHMENT_CATEGORY_ID = p_attach_cat_id,
159 LAST_UPDATED_BY = p_user_id,
160 LAST_UPDATE_DATE = sysdate,
161 LAST_UPDATE_LOGIN = p_login_id
162 WHERE ROWID = p_row_id;
163
164 l_progress := '002';
165 UPDATE POR_TEMPLATES_ALL_TL
166 SET
167 TEMPLATE_NAME = p_name,
168 LAST_UPDATED_BY = p_user_id,
169 LAST_UPDATE_DATE = sysdate,
170 LAST_UPDATE_LOGIN = p_login_id,
171 SOURCE_LANG = USERENV('LANG')
172 WHERE TEMPLATE_CODE = p_template_code
173 AND USERENV('LANG') in (LANGUAGE, SOURCE_LANG);
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 po_message_s.sql_error('por_ift_admin_pkg.update_template', l_progress, SQLCODE);
178 RAISE app_exception.application_exception;
179 END update_template;
180
181
182 PROCEDURE delete_template(p_row_id IN VARCHAR2) IS
183 l_progress VARCHAR2(10) := '000';
184 l_template_code VARCHAR2(30);
185 BEGIN
186
187 l_progress := '001';
188 SELECT template_code
189 INTO l_template_code
190 FROM por_templates_v
191 WHERE row_id = p_row_id;
192
193 l_progress := '002';
194 DELETE FROM POR_TEMPLATE_ATTRIBUTES_TL
195 WHERE ATTRIBUTE_CODE IN
196 (
197 SELECT ATTRIBUTE_CODE
198 FROM POR_TEMPLATE_ATTRIBUTES_B
199 WHERE TEMPLATE_CODE = l_template_code
200 );
201
202 l_progress := '003';
203 DELETE FROM POR_TEMPLATE_ATTRIBUTES_B
204 WHERE TEMPLATE_CODE = l_template_code;
205
206 l_progress := '004';
207 DELETE FROM POR_TEMPLATES_ALL_B
208 WHERE TEMPLATE_CODE = l_template_code;
209
210 l_progress := '005';
211 DELETE FROM POR_TEMPLATES_ALL_TL
212 WHERE TEMPLATE_CODE = l_template_code;
213
214 l_progress := '006';
215 DELETE FROM POR_TEMPLATE_ASSOC
216 WHERE region_code = l_template_code;
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 po_message_s.sql_error('por_ift_admin_pkg.delete_template', l_progress, SQLCODE);
221 RAISE app_exception.application_exception;
222 END delete_template;
223
224
225 PROCEDURE insert_template_attribute(p_template_code IN VARCHAR2,
226 p_display_sequence IN NUMBER,
227 p_attribute_name IN VARCHAR2,
228 p_description IN VARCHAR2,
229 p_default_value IN VARCHAR2,
230 p_flex_value_set_id IN NUMBER,
231 p_required_flag IN VARCHAR2,
232 p_node_display_flag IN VARCHAR2,
233 p_user_id IN NUMBER,
234 p_login_id IN NUMBER,
235 p_attribute_code IN OUT NOCOPY VARCHAR2,
236 p_row_id OUT NOCOPY VARCHAR2) IS
237 l_progress VARCHAR2(10) := '000';
238 l_attribute_code VARCHAR2(30) := NULL;
239 BEGIN
240
241 l_progress := '002';
242 INSERT INTO POR_TEMPLATE_ATTRIBUTES_B (
243 TEMPLATE_CODE,
244 ATTRIBUTE_CODE,
245 SEQUENCE,
246 FLEX_VALUE_SET_ID,
247 REQUIRED_FLAG,
248 NODE_DISPLAY_FLAG,
249 CREATED_BY,
250 CREATION_DATE,
251 LAST_UPDATED_BY,
252 LAST_UPDATE_DATE,
253 LAST_UPDATE_LOGIN)
254 VALUES (
255 p_template_code,
256 'IFT_'||to_char(por_template_attributes_s.NEXTVAL),
257 p_display_sequence,
258 p_flex_value_set_id,
259 p_required_flag,
260 p_node_display_flag,
261 p_user_id,
262 sysdate,
263 p_user_id,
264 sysdate,
265 p_login_id)
266 RETURNING ATTRIBUTE_CODE into l_attribute_code;
267
268 l_progress := '003';
269 INSERT INTO POR_TEMPLATE_ATTRIBUTES_TL(
270 ATTRIBUTE_CODE,
271 SOURCE_LANG,
272 LANGUAGE,
273 ATTRIBUTE_NAME,
274 DESCRIPTION,
275 DEFAULT_VALUE,
276 CREATED_BY,
277 CREATION_DATE,
278 LAST_UPDATED_BY,
279 LAST_UPDATE_DATE,
280 LAST_UPDATE_LOGIN)
281 SELECT
282 l_attribute_code,
283 userenv('LANG'),
284 FL.language_code,
285 p_attribute_name,
286 p_description,
287 p_default_value,
288 p_user_id,
289 sysdate,
290 p_user_id,
291 sysdate,
292 p_login_id
293 FROM FND_LANGUAGES FL
294 WHERE INSTALLED_FLAG IN ('B','I');
295
296 p_attribute_code := l_attribute_code;
297
298 l_progress := '008';
299 SELECT row_id
300 INTO p_row_id
301 FROM por_template_attributes_v
302 WHERE template_code = p_template_code
303 AND attribute_code = l_attribute_code;
304
305 EXCEPTION
306 WHEN OTHERS THEN
307 po_message_s.sql_error('por_ift_admin_pkg.insert_template_attribute', l_progress, SQLCODE);
308 RAISE app_exception.application_exception;
309 END insert_template_attribute;
310
311
312 PROCEDURE lock_template_attribute(p_row_id IN VARCHAR2,
313 p_template_code IN VARCHAR2,
314 p_attribute_code IN VARCHAR2,
315 p_display_sequence IN NUMBER,
316 p_attribute_name IN VARCHAR2,
317 p_description IN VARCHAR2,
318 p_default_value IN VARCHAR2,
319 p_flex_value_set_id IN NUMBER,
320 p_required_flag IN VARCHAR2,
321 p_node_display_flag IN VARCHAR2,
322 p_user_id IN NUMBER,
323 p_login_id IN NUMBER) IS
324 CURSOR c_rec(p_row_id VARCHAR2) IS
325 SELECT *
326 FROM por_template_attributes_b
327 WHERE rowid = p_row_id
328 FOR UPDATE NOWAIT;
329
330 CURSOR c_tl_rec(p_attribute_code VARCHAR2) IS
331 SELECT
332 ATTRIBUTE_NAME,
333 DESCRIPTION,
334 DEFAULT_VALUE,
335 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
336 from POR_TEMPLATE_ATTRIBUTES_TL
337 where ATTRIBUTE_CODE=p_attribute_code
338 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
339 for update nowait;
340
341 l_rec c_rec%ROWTYPE;
342 l_progress VARCHAR2(10) := '000';
343 BEGIN
344
345
346 l_progress := '001';
347 OPEN c_rec(p_row_id);
348
349 l_progress := '002';
350 FETCH c_rec INTO l_rec;
351
352 IF (c_rec%NOTFOUND) THEN
353 l_progress := '003';
354 CLOSE c_rec;
355 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
356 app_exception.raise_exception;
357 END IF;
358 CLOSE c_rec;
359
360 -- Note the use of NULL statements to handle possible
361 -- null values in the IF conditions.
362 l_progress := '004';
363 IF (l_rec.template_code = p_template_code) THEN
364 NULL;
365 ELSE
366 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
367 app_exception.raise_exception;
368 END IF;
369 l_progress := '005';
370 IF (l_rec.sequence = p_display_sequence) THEN
371 NULL;
372 ELSE
373 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
374 app_exception.raise_exception;
375 END IF;
376 l_progress := '006';
377 IF (l_rec.attribute_code = p_attribute_code) THEN
378 NULL;
379 ELSE
380 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
381 app_exception.raise_exception;
382 END IF;
383
384 l_progress := '007';
385 IF (l_rec.flex_value_set_id = p_flex_value_set_id) OR
386 (l_rec.flex_value_set_id IS NULL AND p_flex_value_set_id IS NULL) THEN
387 NULL;
388 ELSE
389 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED4');
390 app_exception.raise_exception;
391 END IF;
392
393 l_progress := '011';
394 IF (l_rec.required_flag = p_required_flag) OR
395 (l_rec.required_flag IS NULL AND p_required_flag IS NULL) THEN
396 NULL;
397 ELSE
398 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED8');
399 app_exception.raise_exception;
400 END IF;
401
402 l_progress := '012';
403 IF (l_rec.node_display_flag = p_node_display_flag) OR
404 (l_rec.node_display_flag IS NULL AND p_node_display_flag IS NULL) THEN
405 NULL;
406 ELSE
407 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED9');
408 app_exception.raise_exception;
409 END IF;
410
411 for tlinfo in c_tl_rec(p_attribute_code) loop
412 if (tlinfo.BASELANG = 'Y') then
413 if ( (tlinfo.attribute_name = p_attribute_name)
414 AND ((tlinfo.DESCRIPTION = p_DESCRIPTION)
415 OR ((tlinfo.DESCRIPTION is null) AND (p_DESCRIPTION is null)))
416 AND ((tlinfo.default_value = p_default_value)
417 OR ((tlinfo.default_value is null) AND (p_default_value is null)))
418 ) then
419 null;
420 else
421 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
422 app_exception.raise_exception;
423 end if;
424 end if;
425 end loop;
426 EXCEPTION
427 WHEN app_exception.application_exception THEN
428 RAISE;
429 WHEN OTHERS THEN
430 po_message_s.sql_error('por_ift_admin_pkg.lock_template_attribute', l_progress, SQLCODE);
431 RAISE app_exception.application_exception;
432 END lock_template_attribute;
433
434
435 PROCEDURE update_template_attribute(p_row_id IN VARCHAR2,
436 p_template_code IN VARCHAR2,
437 p_attribute_code IN VARCHAR2,
438 p_display_sequence IN NUMBER,
439 p_attribute_name IN VARCHAR2,
440 p_description IN VARCHAR2,
441 p_default_value IN VARCHAR2,
442 p_flex_value_set_id IN NUMBER,
443 p_required_flag IN VARCHAR2,
444 p_node_display_flag IN VARCHAR2,
445 p_user_id IN NUMBER,
446 p_login_id IN NUMBER) IS
447 l_progress VARCHAR2(10) := '000';
448 BEGIN
449 l_progress := '001';
450 UPDATE POR_TEMPLATE_ATTRIBUTES_B
451 SET
452 SEQUENCE = p_display_sequence,
453 FLEX_VALUE_SET_ID = p_flex_value_set_id,
454 REQUIRED_FLAG = p_required_flag,
455 NODE_DISPLAY_FLAG = p_node_display_flag,
456 LAST_UPDATED_BY = p_user_id,
457 LAST_UPDATE_DATE = sysdate,
458 LAST_UPDATE_LOGIN = p_login_id
459 WHERE ROWID = p_row_id;
460
461 l_progress := '003';
462 UPDATE POR_TEMPLATE_ATTRIBUTES_TL
463 SET
464 ATTRIBUTE_NAME = p_attribute_name,
465 DESCRIPTION = p_description,
466 DEFAULT_VALUE = p_default_value,
467 LAST_UPDATED_BY = p_user_id,
468 LAST_UPDATE_DATE = sysdate,
469 LAST_UPDATE_LOGIN = p_login_id,
470 SOURCE_LANG = USERENV('LANG')
471 WHERE ATTRIBUTE_CODE = p_attribute_code
472 AND USERENV('LANG') in (LANGUAGE, SOURCE_LANG);
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 po_message_s.sql_error('por_ift_admin_pkg.update_template_attribute', l_progress, SQLCODE);
477 RAISE app_exception.application_exception;
478 END update_template_attribute;
479
480 PROCEDURE delete_template_attribute(p_row_id IN VARCHAR2) IS
481 l_progress VARCHAR2(10) := '000';
482 l_attribute_code_old VARCHAR2(30);
483 BEGIN
484
485 l_progress := '001';
486 SELECT attribute_code
487 INTO l_attribute_code_old
488 FROM por_template_attributes_v
489 WHERE row_id = p_row_id;
490
491 l_progress := '002';
492 DELETE FROM por_template_attributes_b
493 WHERE rowid = p_row_id;
494
495 l_progress := '003';
496 DELETE FROM por_template_attributes_tl
497 WHERE attribute_code = l_attribute_code_old;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 po_message_s.sql_error('por_ift_admin_pkg.delete_template_attribute', l_progress, SQLCODE);
502 RAISE app_exception.application_exception;
503 END delete_template_attribute;
504
505 PROCEDURE insert_template_assoc(p_region_code IN VARCHAR2,
506 p_item_or_category_flag IN VARCHAR2,
507 p_item_or_category_id IN NUMBER,
508 p_user_id IN NUMBER,
509 p_login_id IN NUMBER,
510 p_template_assoc_id OUT NOCOPY NUMBER,
511 p_row_id OUT NOCOPY VARCHAR2) IS
512 l_progress VARCHAR2(10) := '000';
513 l_template_assoc_id NUMBER;
514 BEGIN
515
516 l_progress := '001';
517 SELECT por_template_assoc_s.NEXTVAL
518 INTO l_template_assoc_id
519 FROM SYS.DUAL;
520
521 l_progress := '002';
522 INSERT INTO por_template_assoc (
523 template_assoc_id,
524 region_code,
525 item_or_category_flag,
526 item_or_category_id,
527 created_by,
528 creation_date,
529 last_updated_by,
530 last_update_date,
531 last_update_login)
532 VALUES (
533 l_template_assoc_id,
534 p_region_code,
535 p_item_or_category_flag,
536 p_item_or_category_id,
537 p_user_id,
538 SYSDATE,
539 p_user_id,
540 SYSDATE,
541 p_login_id);
542
543 p_template_assoc_id := l_template_assoc_id;
544
545 l_progress := '003';
546 SELECT row_id
547 INTO p_row_id
548 FROM por_template_assoc_v
549 WHERE template_assoc_id = l_template_assoc_id;
550
551 EXCEPTION
552 WHEN OTHERS THEN
553 po_message_s.sql_error('por_ift_admin_pkg.insert_template_assoc', l_progress, SQLCODE);
554 RAISE app_exception.application_exception;
555 END insert_template_assoc;
556
557 PROCEDURE lock_template_assoc(p_row_id IN VARCHAR2,
558 p_template_assoc_id IN NUMBER,
559 p_region_code IN VARCHAR2,
560 p_item_or_category_flag IN VARCHAR2,
561 p_item_or_category_id IN NUMBER,
562 p_user_id IN NUMBER,
563 p_login_id IN NUMBER) IS
564 CURSOR c_rec(p_row_id VARCHAR2) IS
565 SELECT *
566 FROM por_template_assoc
567 WHERE rowid = p_row_id
568 FOR UPDATE NOWAIT;
569 l_rec c_rec%ROWTYPE;
570 l_progress VARCHAR2(10) := '000';
571 BEGIN
572
573 IF (p_row_id IS NULL) THEN
574 RETURN;
575 END IF;
576
577 l_progress := '001';
578 OPEN c_rec(p_row_id);
579
580 l_progress := '002';
581 FETCH c_rec INTO l_rec;
582
583 IF (c_rec%NOTFOUND) THEN
584 l_progress := '003';
585 CLOSE c_rec;
586 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
587 app_exception.raise_exception;
588 END IF;
589 CLOSE c_rec;
590
591 -- Note the use of NULL statements to handle possible
592 -- null values in the IF conditions.
593 l_progress := '004';
594 IF (l_rec.region_code = p_region_code) THEN
595 NULL;
596 ELSE
597 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
598 app_exception.raise_exception;
599 END IF;
600 l_progress := '005';
601 IF (l_rec.item_or_category_flag = p_item_or_category_flag) THEN
602 NULL;
603 ELSE
604 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
605 app_exception.raise_exception;
606 END IF;
607 l_progress := '006';
608 IF (l_rec.item_or_category_id = p_item_or_category_id) THEN
609 NULL;
610 ELSE
611 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
612 app_exception.raise_exception;
613 END IF;
614
615 EXCEPTION
616 WHEN app_exception.application_exception THEN
617 RAISE;
618 WHEN OTHERS THEN
619 po_message_s.sql_error('por_ift_admin_pkg.lock_template_assoc', l_progress, SQLCODE);
620 RAISE app_exception.application_exception;
621 END lock_template_assoc;
622
623 PROCEDURE update_template_assoc(p_row_id IN VARCHAR2,
624 p_template_assoc_id IN NUMBER,
625 p_region_code IN VARCHAR2,
626 p_item_or_category_flag IN VARCHAR2,
627 p_item_or_category_id IN NUMBER,
628 p_user_id IN NUMBER,
629 p_login_id IN NUMBER) IS
630 l_progress VARCHAR2(10) := '000';
631 BEGIN
632
633 l_progress := '001';
634 UPDATE por_template_assoc
635 SET template_assoc_id = p_template_assoc_id,
636 region_code = p_region_code,
637 item_or_category_flag = p_item_or_category_flag,
638 item_or_category_id = p_item_or_category_id,
639 last_updated_by = p_user_id,
640 last_update_date = SYSDATE,
641 last_update_login = p_login_id
642 WHERE rowid = p_row_id;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 po_message_s.sql_error('por_ift_admin_pkg.update_template_assoc', l_progress, SQLCODE);
647 RAISE app_exception.application_exception;
648 END update_template_assoc;
649
650
651 PROCEDURE delete_template_assoc(p_row_id IN VARCHAR2) IS
652 l_progress VARCHAR2(10) := '000';
653 BEGIN
654
655 l_progress := '001';
656 DELETE FROM por_template_assoc
657 WHERE rowid = p_row_id;
658
659 EXCEPTION
660 WHEN OTHERS THEN
661 po_message_s.sql_error('por_ift_admin_pkg.delete_template_assoc', l_progress, SQLCODE);
662 RAISE app_exception.application_exception;
663 END delete_template_assoc;
664
665 PROCEDURE delete_all_template_assoc(p_region_code IN VARCHAR2) IS
666 l_progress VARCHAR2(10) := '000';
667 BEGIN
668
669 l_progress := '001';
670 DELETE FROM por_template_assoc
671 WHERE region_code = p_region_code;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 po_message_s.sql_error('por_ift_admin_pkg.delete_all_template_assoc', l_progress, SQLCODE);
676 RAISE app_exception.application_exception;
677 END delete_all_template_assoc;
678
679 PROCEDURE add_language IS
680 BEGIN
681 INSERT INTO POR_TEMPLATES_ALL_TL(
682 TEMPLATE_CODE,
683 SOURCE_LANG,
684 LANGUAGE,
685 TEMPLATE_NAME,
686 CREATED_BY,
687 CREATION_DATE,
688 LAST_UPDATED_BY,
689 LAST_UPDATE_DATE,
690 LAST_UPDATE_LOGIN)
691 SELECT
692 B.TEMPLATE_CODE,
693 B.SOURCE_LANG,
694 L.LANGUAGE_CODE,
695 B.TEMPLATE_NAME,
696 B.CREATED_BY,
697 B.CREATION_DATE,
698 B.LAST_UPDATED_BY,
699 B.LAST_UPDATE_DATE,
700 B.LAST_UPDATE_LOGIN
701 FROM POR_TEMPLATES_ALL_TL B, FND_LANGUAGES L
702 WHERE L.INSTALLED_FLAG in ('I', 'B')
703 AND B.LANGUAGE = USERENV('LANG')
704 AND NOT EXISTS
705 (SELECT NULL
706 FROM POR_TEMPLATES_ALL_TL T
707 WHERE T.TEMPLATE_CODE = B.TEMPLATE_CODE
708 AND T.LANGUAGE = L.LANGUAGE_CODE);
709
710 INSERT INTO POR_TEMPLATE_ATTRIBUTES_TL(
711 ATTRIBUTE_CODE,
712 SOURCE_LANG,
713 LANGUAGE,
714 ATTRIBUTE_NAME,
715 DESCRIPTION,
716 DEFAULT_VALUE,
717 CREATED_BY,
718 CREATION_DATE,
719 LAST_UPDATED_BY,
720 LAST_UPDATE_DATE,
721 LAST_UPDATE_LOGIN)
722 SELECT
723 B.ATTRIBUTE_CODE,
724 B.SOURCE_LANG,
725 L.LANGUAGE_CODE,
726 B.ATTRIBUTE_NAME,
727 B.DESCRIPTION,
728 B.DEFAULT_VALUE,
729 B.CREATED_BY,
730 B.CREATION_DATE,
731 B.LAST_UPDATED_BY,
732 B.LAST_UPDATE_DATE,
733 B.LAST_UPDATE_LOGIN
734 FROM POR_TEMPLATE_ATTRIBUTES_TL B, FND_LANGUAGES L
735 WHERE L.INSTALLED_FLAG in ('I', 'B')
736 AND B.LANGUAGE = userenv('LANG')
737 AND NOT EXISTS
738 (SELECT NULL
739 FROM POR_TEMPLATE_ATTRIBUTES_TL T
740 WHERE T.ATTRIBUTE_CODE = B.ATTRIBUTE_CODE
741 AND T.LANGUAGE = L.LANGUAGE_CODE);
742
743 END add_language;
744
745 END por_ift_admin_pkg;