[Home] [Help]
PACKAGE BODY: APPS.HR_FORM_TEMPLATES_PKG
Source
1 package body HR_FORM_TEMPLATES_PKG as
2 /* $Header: hrtmplct.pkb 115.3 2002/12/11 10:29:58 raranjan noship $ */
3 procedure OWNER_TO_WHO (
4 X_OWNER in VARCHAR2,
5 X_CREATION_DATE out nocopy DATE,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATE_DATE out nocopy DATE,
8 X_LAST_UPDATED_BY out nocopy NUMBER,
9 X_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12 if X_OWNER = 'SEED' then
13 X_CREATED_BY := 1;
14 X_LAST_UPDATED_BY := 1;
15 else
16 X_CREATED_BY := 0;
17 X_LAST_UPDATED_BY := 0;
18 end if;
19 X_CREATION_DATE := sysdate;
20 X_LAST_UPDATE_DATE := sysdate;
21 X_LAST_UPDATE_LOGIN := 0;
22 end OWNER_TO_WHO;
23 --
24 procedure DELETE_CHILDREN (
25 X_FORM_TEMPLATE_ID in NUMBER
26 )
27 is
28 CURSOR csr_items IS
29 SELECT tim.template_item_id
30 ,tim.object_version_number
31 FROM hr_template_items_b tim
32 WHERE tim.form_template_id = X_FORM_TEMPLATE_ID;
33 CURSOR csr_windows IS
34 SELECT twn.template_window_id
35 ,twn.object_version_number
36 FROM hr_template_windows_b twn
37 WHERE twn.form_template_id = X_FORM_TEMPLATE_ID;
38 begin
39 hr_form_templates_api.set_seed_data_session_mode;
40 FOR l_item IN csr_items LOOP
41 hr_template_items_api.delete_template_item
42 (p_template_item_id => l_item.template_item_id
43 ,p_object_version_number => l_item.object_version_number
44 ,p_delete_children_flag => 'Y'
45 );
46 END LOOP;
47 FOR l_window IN csr_windows LOOP
48 hr_template_windows_api.delete_template_window
49 (p_template_window_id => l_window.template_window_id
50 ,p_object_version_number => l_window.object_version_number
51 ,p_delete_children_flag => 'Y'
52 );
53 END LOOP;
54 end DELETE_CHILDREN;
55 --
56 procedure INSERT_ROW (
57 X_ROWID in out nocopy VARCHAR2,
58 X_FORM_TEMPLATE_ID in NUMBER,
59 X_OBJECT_VERSION_NUMBER in NUMBER,
60 X_APPLICATION_ID in NUMBER,
61 X_FORM_ID in NUMBER,
62 X_TEMPLATE_NAME in VARCHAR2,
63 X_ENABLED_FLAG in VARCHAR2,
64 X_LEGISLATION_CODE in VARCHAR2,
65 X_ATTRIBUTE_CATEGORY in VARCHAR2,
66 X_ATTRIBUTE1 in VARCHAR2,
67 X_ATTRIBUTE2 in VARCHAR2,
68 X_ATTRIBUTE3 in VARCHAR2,
69 X_ATTRIBUTE4 in VARCHAR2,
70 X_ATTRIBUTE5 in VARCHAR2,
71 X_ATTRIBUTE6 in VARCHAR2,
72 X_ATTRIBUTE7 in VARCHAR2,
73 X_ATTRIBUTE8 in VARCHAR2,
74 X_ATTRIBUTE9 in VARCHAR2,
75 X_ATTRIBUTE10 in VARCHAR2,
76 X_ATTRIBUTE11 in VARCHAR2,
77 X_ATTRIBUTE12 in VARCHAR2,
78 X_ATTRIBUTE13 in VARCHAR2,
79 X_ATTRIBUTE14 in VARCHAR2,
80 X_ATTRIBUTE15 in VARCHAR2,
81 X_ATTRIBUTE16 in VARCHAR2,
82 X_ATTRIBUTE17 in VARCHAR2,
83 X_ATTRIBUTE18 in VARCHAR2,
84 X_ATTRIBUTE19 in VARCHAR2,
85 X_ATTRIBUTE20 in VARCHAR2,
86 X_ATTRIBUTE21 in VARCHAR2,
87 X_ATTRIBUTE22 in VARCHAR2,
88 X_ATTRIBUTE23 in VARCHAR2,
89 X_ATTRIBUTE27 in VARCHAR2,
90 X_ATTRIBUTE28 in VARCHAR2,
91 X_ATTRIBUTE29 in VARCHAR2,
92 X_ATTRIBUTE30 in VARCHAR2,
93 X_ATTRIBUTE24 in VARCHAR2,
94 X_ATTRIBUTE25 in VARCHAR2,
95 X_ATTRIBUTE26 in VARCHAR2,
96 X_USER_TEMPLATE_NAME in VARCHAR2,
97 X_DESCRIPTION in VARCHAR2,
98 X_CREATION_DATE in DATE,
99 X_CREATED_BY in NUMBER,
100 X_LAST_UPDATE_DATE in DATE,
101 X_LAST_UPDATED_BY in NUMBER,
102 X_LAST_UPDATE_LOGIN in NUMBER
103 ) is
104 cursor C is select ROWID from HR_FORM_TEMPLATES_B
105 where FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID
106 ;
107 begin
108 insert into HR_FORM_TEMPLATES_B (
109 OBJECT_VERSION_NUMBER,
110 APPLICATION_ID,
111 FORM_ID,
112 TEMPLATE_NAME,
113 ENABLED_FLAG,
114 LEGISLATION_CODE,
115 ATTRIBUTE_CATEGORY,
116 ATTRIBUTE1,
117 ATTRIBUTE2,
118 ATTRIBUTE3,
119 ATTRIBUTE4,
120 ATTRIBUTE5,
121 ATTRIBUTE6,
122 ATTRIBUTE7,
123 ATTRIBUTE8,
124 ATTRIBUTE9,
125 ATTRIBUTE10,
126 ATTRIBUTE11,
127 ATTRIBUTE12,
128 ATTRIBUTE13,
129 ATTRIBUTE14,
130 ATTRIBUTE15,
131 ATTRIBUTE16,
132 ATTRIBUTE17,
133 ATTRIBUTE18,
134 ATTRIBUTE19,
135 ATTRIBUTE20,
136 ATTRIBUTE21,
137 ATTRIBUTE22,
138 ATTRIBUTE23,
139 FORM_TEMPLATE_ID,
140 ATTRIBUTE27,
141 ATTRIBUTE28,
142 ATTRIBUTE29,
143 ATTRIBUTE30,
144 ATTRIBUTE24,
145 ATTRIBUTE25,
146 ATTRIBUTE26,
147 CREATION_DATE,
148 CREATED_BY,
149 LAST_UPDATE_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN
152 ) values (
153 X_OBJECT_VERSION_NUMBER,
154 X_APPLICATION_ID,
155 X_FORM_ID,
156 X_TEMPLATE_NAME,
157 X_ENABLED_FLAG,
158 X_LEGISLATION_CODE,
159 X_ATTRIBUTE_CATEGORY,
160 X_ATTRIBUTE1,
161 X_ATTRIBUTE2,
162 X_ATTRIBUTE3,
163 X_ATTRIBUTE4,
164 X_ATTRIBUTE5,
165 X_ATTRIBUTE6,
166 X_ATTRIBUTE7,
167 X_ATTRIBUTE8,
168 X_ATTRIBUTE9,
169 X_ATTRIBUTE10,
170 X_ATTRIBUTE11,
171 X_ATTRIBUTE12,
172 X_ATTRIBUTE13,
173 X_ATTRIBUTE14,
174 X_ATTRIBUTE15,
175 X_ATTRIBUTE16,
176 X_ATTRIBUTE17,
177 X_ATTRIBUTE18,
178 X_ATTRIBUTE19,
179 X_ATTRIBUTE20,
180 X_ATTRIBUTE21,
181 X_ATTRIBUTE22,
182 X_ATTRIBUTE23,
183 X_FORM_TEMPLATE_ID,
184 X_ATTRIBUTE27,
185 X_ATTRIBUTE28,
186 X_ATTRIBUTE29,
187 X_ATTRIBUTE30,
188 X_ATTRIBUTE24,
189 X_ATTRIBUTE25,
190 X_ATTRIBUTE26,
191 X_CREATION_DATE,
192 X_CREATED_BY,
193 X_LAST_UPDATE_DATE,
194 X_LAST_UPDATED_BY,
195 X_LAST_UPDATE_LOGIN
196 );
197
198 insert into HR_FORM_TEMPLATES_TL (
199 FORM_TEMPLATE_ID,
200 USER_TEMPLATE_NAME,
201 DESCRIPTION,
202 LAST_UPDATE_DATE,
203 LAST_UPDATED_BY,
204 LAST_UPDATE_LOGIN,
205 CREATED_BY,
206 CREATION_DATE,
207 LANGUAGE,
208 SOURCE_LANG
209 ) select
210 X_FORM_TEMPLATE_ID,
211 X_USER_TEMPLATE_NAME,
212 X_DESCRIPTION,
213 X_LAST_UPDATE_DATE,
214 X_LAST_UPDATED_BY,
215 X_LAST_UPDATE_LOGIN,
216 X_CREATED_BY,
217 X_CREATION_DATE,
218 L.LANGUAGE_CODE,
219 userenv('LANG')
220 from FND_LANGUAGES L
221 where L.INSTALLED_FLAG in ('I', 'B')
222 and not exists
223 (select NULL
224 from HR_FORM_TEMPLATES_TL T
225 where T.FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID
226 and T.LANGUAGE = L.LANGUAGE_CODE);
227
228 open c;
229 fetch c into X_ROWID;
230 if (c%notfound) then
231 close c;
232 raise no_data_found;
233 end if;
234 close c;
235
236 end INSERT_ROW;
237
238 procedure LOCK_ROW (
239 X_FORM_TEMPLATE_ID in NUMBER,
240 X_OBJECT_VERSION_NUMBER in NUMBER,
241 X_APPLICATION_ID in NUMBER,
242 X_FORM_ID in NUMBER,
243 X_TEMPLATE_NAME in VARCHAR2,
244 X_ENABLED_FLAG in VARCHAR2,
245 X_LEGISLATION_CODE in VARCHAR2,
246 X_ATTRIBUTE_CATEGORY in VARCHAR2,
247 X_ATTRIBUTE1 in VARCHAR2,
248 X_ATTRIBUTE2 in VARCHAR2,
249 X_ATTRIBUTE3 in VARCHAR2,
250 X_ATTRIBUTE4 in VARCHAR2,
251 X_ATTRIBUTE5 in VARCHAR2,
252 X_ATTRIBUTE6 in VARCHAR2,
253 X_ATTRIBUTE7 in VARCHAR2,
254 X_ATTRIBUTE8 in VARCHAR2,
255 X_ATTRIBUTE9 in VARCHAR2,
256 X_ATTRIBUTE10 in VARCHAR2,
257 X_ATTRIBUTE11 in VARCHAR2,
258 X_ATTRIBUTE12 in VARCHAR2,
259 X_ATTRIBUTE13 in VARCHAR2,
260 X_ATTRIBUTE14 in VARCHAR2,
261 X_ATTRIBUTE15 in VARCHAR2,
262 X_ATTRIBUTE16 in VARCHAR2,
263 X_ATTRIBUTE17 in VARCHAR2,
264 X_ATTRIBUTE18 in VARCHAR2,
265 X_ATTRIBUTE19 in VARCHAR2,
266 X_ATTRIBUTE20 in VARCHAR2,
267 X_ATTRIBUTE21 in VARCHAR2,
268 X_ATTRIBUTE22 in VARCHAR2,
269 X_ATTRIBUTE23 in VARCHAR2,
270 X_ATTRIBUTE27 in VARCHAR2,
271 X_ATTRIBUTE28 in VARCHAR2,
272 X_ATTRIBUTE29 in VARCHAR2,
273 X_ATTRIBUTE30 in VARCHAR2,
274 X_ATTRIBUTE24 in VARCHAR2,
275 X_ATTRIBUTE25 in VARCHAR2,
276 X_ATTRIBUTE26 in VARCHAR2,
277 X_USER_TEMPLATE_NAME in VARCHAR2,
278 X_DESCRIPTION in VARCHAR2
279 ) is
280 cursor c is select
281 OBJECT_VERSION_NUMBER,
282 APPLICATION_ID,
283 FORM_ID,
284 TEMPLATE_NAME,
285 ENABLED_FLAG,
286 LEGISLATION_CODE,
287 ATTRIBUTE_CATEGORY,
288 ATTRIBUTE1,
289 ATTRIBUTE2,
290 ATTRIBUTE3,
291 ATTRIBUTE4,
292 ATTRIBUTE5,
293 ATTRIBUTE6,
294 ATTRIBUTE7,
295 ATTRIBUTE8,
296 ATTRIBUTE9,
297 ATTRIBUTE10,
298 ATTRIBUTE11,
299 ATTRIBUTE12,
300 ATTRIBUTE13,
301 ATTRIBUTE14,
302 ATTRIBUTE15,
303 ATTRIBUTE16,
304 ATTRIBUTE17,
305 ATTRIBUTE18,
306 ATTRIBUTE19,
307 ATTRIBUTE20,
308 ATTRIBUTE21,
309 ATTRIBUTE22,
310 ATTRIBUTE23,
311 ATTRIBUTE27,
312 ATTRIBUTE28,
313 ATTRIBUTE29,
314 ATTRIBUTE30,
315 ATTRIBUTE24,
316 ATTRIBUTE25,
317 ATTRIBUTE26
318 from HR_FORM_TEMPLATES_B
319 where FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID
320 for update of FORM_TEMPLATE_ID nowait;
321 recinfo c%rowtype;
322
323 cursor c1 is select
324 USER_TEMPLATE_NAME,
325 DESCRIPTION,
326 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
327 from HR_FORM_TEMPLATES_TL
328 where FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID
329 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
330 for update of FORM_TEMPLATE_ID nowait;
331 begin
332 open c;
333 fetch c into recinfo;
334 if (c%notfound) then
335 close c;
336 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
337 app_exception.raise_exception;
338 end if;
339 close c;
340 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
341 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
342 AND (recinfo.FORM_ID = X_FORM_ID)
343 AND (recinfo.TEMPLATE_NAME = X_TEMPLATE_NAME)
344 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
345 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
346 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
347 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
348 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
349 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
350 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
351 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
352 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
353 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
354 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
355 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
356 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
357 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
358 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
359 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
360 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
361 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
362 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
363 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
364 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
365 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
366 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
367 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
368 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
369 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
370 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
371 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
372 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
373 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
374 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
375 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
376 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
377 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
378 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
379 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
380 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
381 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
382 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
383 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
384 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
385 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
386 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
387 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
388 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
389 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
390 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
391 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
392 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
393 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
394 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
395 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
396 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
397 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
398 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
399 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
400 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
401 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
402 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
403 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
404 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
405 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
406 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
407 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
408 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
409 ) then
410 null;
411 else
412 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
413 app_exception.raise_exception;
414 end if;
415
416 for tlinfo in c1 loop
417 if (tlinfo.BASELANG = 'Y') then
418 if ( (tlinfo.USER_TEMPLATE_NAME = X_USER_TEMPLATE_NAME)
419 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
420 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
421 ) then
422 null;
423 else
424 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
425 app_exception.raise_exception;
426 end if;
427 end if;
428 end loop;
429 return;
430 end LOCK_ROW;
431
432 procedure UPDATE_ROW (
433 X_FORM_TEMPLATE_ID in NUMBER,
434 X_OBJECT_VERSION_NUMBER in NUMBER,
435 X_APPLICATION_ID in NUMBER,
436 X_FORM_ID in NUMBER,
437 X_TEMPLATE_NAME in VARCHAR2,
438 X_ENABLED_FLAG in VARCHAR2,
439 X_LEGISLATION_CODE in VARCHAR2,
440 X_ATTRIBUTE_CATEGORY in VARCHAR2,
441 X_ATTRIBUTE1 in VARCHAR2,
442 X_ATTRIBUTE2 in VARCHAR2,
443 X_ATTRIBUTE3 in VARCHAR2,
444 X_ATTRIBUTE4 in VARCHAR2,
445 X_ATTRIBUTE5 in VARCHAR2,
449 X_ATTRIBUTE9 in VARCHAR2,
446 X_ATTRIBUTE6 in VARCHAR2,
447 X_ATTRIBUTE7 in VARCHAR2,
448 X_ATTRIBUTE8 in VARCHAR2,
450 X_ATTRIBUTE10 in VARCHAR2,
451 X_ATTRIBUTE11 in VARCHAR2,
452 X_ATTRIBUTE12 in VARCHAR2,
453 X_ATTRIBUTE13 in VARCHAR2,
454 X_ATTRIBUTE14 in VARCHAR2,
455 X_ATTRIBUTE15 in VARCHAR2,
456 X_ATTRIBUTE16 in VARCHAR2,
457 X_ATTRIBUTE17 in VARCHAR2,
458 X_ATTRIBUTE18 in VARCHAR2,
459 X_ATTRIBUTE19 in VARCHAR2,
460 X_ATTRIBUTE20 in VARCHAR2,
461 X_ATTRIBUTE21 in VARCHAR2,
462 X_ATTRIBUTE22 in VARCHAR2,
463 X_ATTRIBUTE23 in VARCHAR2,
464 X_ATTRIBUTE27 in VARCHAR2,
465 X_ATTRIBUTE28 in VARCHAR2,
466 X_ATTRIBUTE29 in VARCHAR2,
467 X_ATTRIBUTE30 in VARCHAR2,
468 X_ATTRIBUTE24 in VARCHAR2,
469 X_ATTRIBUTE25 in VARCHAR2,
470 X_ATTRIBUTE26 in VARCHAR2,
471 X_USER_TEMPLATE_NAME in VARCHAR2,
472 X_DESCRIPTION in VARCHAR2,
473 X_LAST_UPDATE_DATE in DATE,
474 X_LAST_UPDATED_BY in NUMBER,
475 X_LAST_UPDATE_LOGIN in NUMBER
476 ) is
477 begin
478 update HR_FORM_TEMPLATES_B set
479 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
480 APPLICATION_ID = X_APPLICATION_ID,
481 FORM_ID = X_FORM_ID,
482 TEMPLATE_NAME = X_TEMPLATE_NAME,
483 ENABLED_FLAG = X_ENABLED_FLAG,
484 LEGISLATION_CODE = X_LEGISLATION_CODE,
485 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
486 ATTRIBUTE1 = X_ATTRIBUTE1,
487 ATTRIBUTE2 = X_ATTRIBUTE2,
488 ATTRIBUTE3 = X_ATTRIBUTE3,
489 ATTRIBUTE4 = X_ATTRIBUTE4,
490 ATTRIBUTE5 = X_ATTRIBUTE5,
491 ATTRIBUTE6 = X_ATTRIBUTE6,
492 ATTRIBUTE7 = X_ATTRIBUTE7,
493 ATTRIBUTE8 = X_ATTRIBUTE8,
494 ATTRIBUTE9 = X_ATTRIBUTE9,
495 ATTRIBUTE10 = X_ATTRIBUTE10,
496 ATTRIBUTE11 = X_ATTRIBUTE11,
497 ATTRIBUTE12 = X_ATTRIBUTE12,
498 ATTRIBUTE13 = X_ATTRIBUTE13,
499 ATTRIBUTE14 = X_ATTRIBUTE14,
500 ATTRIBUTE15 = X_ATTRIBUTE15,
501 ATTRIBUTE16 = X_ATTRIBUTE16,
502 ATTRIBUTE17 = X_ATTRIBUTE17,
503 ATTRIBUTE18 = X_ATTRIBUTE18,
504 ATTRIBUTE19 = X_ATTRIBUTE19,
505 ATTRIBUTE20 = X_ATTRIBUTE20,
506 ATTRIBUTE21 = X_ATTRIBUTE21,
507 ATTRIBUTE22 = X_ATTRIBUTE22,
508 ATTRIBUTE23 = X_ATTRIBUTE23,
509 ATTRIBUTE27 = X_ATTRIBUTE27,
510 ATTRIBUTE28 = X_ATTRIBUTE28,
511 ATTRIBUTE29 = X_ATTRIBUTE29,
512 ATTRIBUTE30 = X_ATTRIBUTE30,
513 ATTRIBUTE24 = X_ATTRIBUTE24,
514 ATTRIBUTE25 = X_ATTRIBUTE25,
515 ATTRIBUTE26 = X_ATTRIBUTE26,
516 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
517 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
518 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
519 where FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID;
520
521 if (sql%notfound) then
522 raise no_data_found;
523 end if;
524
525 update HR_FORM_TEMPLATES_TL set
526 USER_TEMPLATE_NAME = X_USER_TEMPLATE_NAME,
527 DESCRIPTION = X_DESCRIPTION,
528 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
529 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
530 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
531 SOURCE_LANG = userenv('LANG')
532 where FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID
533 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
534
535 if (sql%notfound) then
536 raise no_data_found;
537 end if;
538 end UPDATE_ROW;
539
540 procedure DELETE_ROW (
541 X_FORM_TEMPLATE_ID in NUMBER
542 ) is
543 begin
544 delete from HR_FORM_TEMPLATES_TL
545 where FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID;
546
547 if (sql%notfound) then
548 raise no_data_found;
549 end if;
550
551 delete from HR_FORM_TEMPLATES_B
552 where FORM_TEMPLATE_ID = X_FORM_TEMPLATE_ID;
553
554 if (sql%notfound) then
555 raise no_data_found;
556 end if;
557 end DELETE_ROW;
558
559 procedure ADD_LANGUAGE
560 is
561 begin
562 delete from HR_FORM_TEMPLATES_TL T
563 where not exists
564 (select NULL
565 from HR_FORM_TEMPLATES_B B
566 where B.FORM_TEMPLATE_ID = T.FORM_TEMPLATE_ID
567 );
568
569 update HR_FORM_TEMPLATES_TL T set (
570 USER_TEMPLATE_NAME,
571 DESCRIPTION
572 ) = (select
573 B.USER_TEMPLATE_NAME,
574 B.DESCRIPTION
575 from HR_FORM_TEMPLATES_TL B
576 where B.FORM_TEMPLATE_ID = T.FORM_TEMPLATE_ID
577 and B.LANGUAGE = T.SOURCE_LANG)
578 where (
579 T.FORM_TEMPLATE_ID,
580 T.LANGUAGE
581 ) in (select
582 SUBT.FORM_TEMPLATE_ID,
583 SUBT.LANGUAGE
584 from HR_FORM_TEMPLATES_TL SUBB, HR_FORM_TEMPLATES_TL SUBT
585 where SUBB.FORM_TEMPLATE_ID = SUBT.FORM_TEMPLATE_ID
586 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
587 and (SUBB.USER_TEMPLATE_NAME <> SUBT.USER_TEMPLATE_NAME
588 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
589 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
590 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
591 ));
592
593 insert into HR_FORM_TEMPLATES_TL (
594 FORM_TEMPLATE_ID,
595 USER_TEMPLATE_NAME,
596 DESCRIPTION,
597 LAST_UPDATE_DATE,
598 LAST_UPDATED_BY,
599 LAST_UPDATE_LOGIN,
600 CREATED_BY,
604 ) select
601 CREATION_DATE,
602 LANGUAGE,
603 SOURCE_LANG
605 B.FORM_TEMPLATE_ID,
606 B.USER_TEMPLATE_NAME,
607 B.DESCRIPTION,
608 B.LAST_UPDATE_DATE,
609 B.LAST_UPDATED_BY,
610 B.LAST_UPDATE_LOGIN,
611 B.CREATED_BY,
612 B.CREATION_DATE,
613 L.LANGUAGE_CODE,
614 B.SOURCE_LANG
615 from HR_FORM_TEMPLATES_TL B, FND_LANGUAGES L
616 where L.INSTALLED_FLAG in ('I', 'B')
617 and B.LANGUAGE = userenv('LANG')
618 and not exists
619 (select NULL
620 from HR_FORM_TEMPLATES_TL T
621 where T.FORM_TEMPLATE_ID = B.FORM_TEMPLATE_ID
622 and T.LANGUAGE = L.LANGUAGE_CODE);
623 end ADD_LANGUAGE;
624
625 procedure TRANSLATE_ROW (
626 X_APPLICATION_SHORT_NAME in VARCHAR2,
627 X_FORM_NAME in VARCHAR2,
628 X_TEMPLATE_NAME in VARCHAR2,
629 X_TERRITORY_SHORT_NAME in VARCHAR2,
630 X_OWNER in VARCHAR2,
631 X_USER_TEMPLATE_NAME in VARCHAR2,
632 X_DESCRIPTION in VARCHAR2) is
633 X_ROWID ROWID;
634 X_CREATION_DATE DATE;
635 X_CREATED_BY NUMBER;
636 X_LAST_UPDATE_DATE DATE;
637 X_LAST_UPDATED_BY NUMBER;
638 X_LAST_UPDATE_LOGIN NUMBER;
639 X_FORM_ID NUMBER;
640 X_LEGISLATION_CODE VARCHAR2(4);
641 X_APPLICATION_ID NUMBER;
642 X_FORM_TEMPLATE_ID NUMBER;
643 begin
644
645 OWNER_TO_WHO (
646 X_OWNER,
647 X_CREATION_DATE,
648 X_CREATED_BY,
649 X_LAST_UPDATE_DATE,
650 X_LAST_UPDATED_BY,
651 X_LAST_UPDATE_LOGIN
652 );
653
654 select application_id
655 into x_application_id
656 from fnd_application
657 where application_short_name = x_application_short_name;
658
659 select form_id
660 into x_form_id
661 from fnd_form
662 where form_name = x_form_name
663 and application_id = x_application_id;
664
665 begin
666 select form_template_id
667 into x_form_template_id
668 from hr_form_templates_b
669 where template_name = x_template_name
670 and ( (legislation_code is null and x_territory_short_name is null)
671 or (legislation_code = x_territory_short_name) )
672 and application_id = x_application_id
673 and form_id = x_form_id;
674 end;
675
676 update HR_FORM_TEMPLATES_TL set
677 DESCRIPTION = X_DESCRIPTION,
678 USER_TEMPLATE_NAME = X_USER_TEMPLATE_NAME,
679 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
680 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
681 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
682 SOURCE_LANG = userenv('LANG')
683 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
684 and form_template_id = x_form_template_id;
685
686 end TRANSLATE_ROW;
687 procedure LOAD_ROW (
688 X_APPLICATION_SHORT_NAME in VARCHAR2,
689 X_FORM_NAME in VARCHAR2,
690 X_TEMPLATE_NAME in VARCHAR2,
691 X_TERRITORY_SHORT_NAME in VARCHAR2,
692 X_OWNER in VARCHAR2,
696 X_ATTRIBUTE1 in VARCHAR2,
693 X_OBJECT_VERSION_NUMBER in VARCHAR2,
694 X_ENABLED_FLAG in VARCHAR2,
695 X_ATTRIBUTE_CATEGORY in VARCHAR2,
697 X_ATTRIBUTE2 in VARCHAR2,
698 X_ATTRIBUTE3 in VARCHAR2,
699 X_ATTRIBUTE4 in VARCHAR2,
700 X_ATTRIBUTE5 in VARCHAR2,
701 X_ATTRIBUTE6 in VARCHAR2,
702 X_ATTRIBUTE7 in VARCHAR2,
703 X_ATTRIBUTE8 in VARCHAR2,
704 X_ATTRIBUTE9 in VARCHAR2,
705 X_ATTRIBUTE10 in VARCHAR2,
706 X_ATTRIBUTE11 in VARCHAR2,
707 X_ATTRIBUTE12 in VARCHAR2,
708 X_ATTRIBUTE13 in VARCHAR2,
709 X_ATTRIBUTE14 in VARCHAR2,
710 X_ATTRIBUTE15 in VARCHAR2,
711 X_ATTRIBUTE16 in VARCHAR2,
712 X_ATTRIBUTE17 in VARCHAR2,
713 X_ATTRIBUTE18 in VARCHAR2,
714 X_ATTRIBUTE19 in VARCHAR2,
715 X_ATTRIBUTE20 in VARCHAR2,
716 X_ATTRIBUTE21 in VARCHAR2,
717 X_ATTRIBUTE22 in VARCHAR2,
718 X_ATTRIBUTE23 in VARCHAR2,
719 X_ATTRIBUTE24 in VARCHAR2,
720 X_ATTRIBUTE25 in VARCHAR2,
721 X_ATTRIBUTE26 in VARCHAR2,
722 X_ATTRIBUTE27 in VARCHAR2,
723 X_ATTRIBUTE28 in VARCHAR2,
724 X_ATTRIBUTE29 in VARCHAR2,
725 X_ATTRIBUTE30 in VARCHAR2,
726 X_USER_TEMPLATE_NAME in VARCHAR2,
727 X_DESCRIPTION in VARCHAR2) is
728 X_ROWID ROWID;
729 X_CREATION_DATE DATE;
730 X_CREATED_BY NUMBER;
731 X_LAST_UPDATE_DATE DATE;
732 X_LAST_UPDATED_BY NUMBER;
733 X_LAST_UPDATE_LOGIN NUMBER;
734 X_FORM_ID NUMBER;
735 X_LEGISLATION_CODE VARCHAR2(4) := X_TERRITORY_SHORT_NAME;
736 X_APPLICATION_ID NUMBER;
737 X_FORM_TEMPLATE_ID NUMBER;
738 begin
739
740 OWNER_TO_WHO (
741 X_OWNER,
742 X_CREATION_DATE,
743 X_CREATED_BY,
744 X_LAST_UPDATE_DATE,
745 X_LAST_UPDATED_BY,
746 X_LAST_UPDATE_LOGIN
747 );
748
749 select application_id
750 into x_application_id
751 from fnd_application
752 where application_short_name = x_application_short_name;
753
754 select form_id
755 into x_form_id
756 from fnd_form
757 where form_name = x_form_name
758 and application_id = x_application_id;
759
760
761 begin
762 select form_template_id
763 into x_form_template_id
764 from hr_form_templates_b
765 where template_name = x_template_name
766 and ( (legislation_code is null and x_territory_short_name is null)
767 or (legislation_code = x_territory_short_name) )
768 and application_id = x_application_id
769 and form_id = x_form_id;
770 exception
771 when no_data_found then
772 select hr_form_templates_b_s.nextval
773 into x_form_template_id
774 from dual;
775 end;
776
777 -- Delete all children before reloading data
778 DELETE_CHILDREN(X_FORM_TEMPLATE_ID);
779
780 begin
781
782 UPDATE_ROW (
783 X_FORM_TEMPLATE_ID,
784 to_number(X_OBJECT_VERSION_NUMBER),
785 X_APPLICATION_ID,
786 X_FORM_ID,
787 X_TEMPLATE_NAME,
788 X_ENABLED_FLAG,
789 X_LEGISLATION_CODE,
790 X_ATTRIBUTE_CATEGORY,
791 X_ATTRIBUTE1,
792 X_ATTRIBUTE2,
793 X_ATTRIBUTE3,
794 X_ATTRIBUTE4,
795 X_ATTRIBUTE5,
796 X_ATTRIBUTE6,
797 X_ATTRIBUTE7,
798 X_ATTRIBUTE8,
799 X_ATTRIBUTE9,
800 X_ATTRIBUTE10,
801 X_ATTRIBUTE11,
802 X_ATTRIBUTE12,
803 X_ATTRIBUTE13,
804 X_ATTRIBUTE14,
805 X_ATTRIBUTE15,
806 X_ATTRIBUTE16,
807 X_ATTRIBUTE17,
808 X_ATTRIBUTE18,
809 X_ATTRIBUTE19,
810 X_ATTRIBUTE20,
811 X_ATTRIBUTE21,
812 X_ATTRIBUTE22,
813 X_ATTRIBUTE23,
814 X_ATTRIBUTE27,
815 X_ATTRIBUTE28,
816 X_ATTRIBUTE29,
817 X_ATTRIBUTE30,
818 X_ATTRIBUTE24,
819 X_ATTRIBUTE25,
820 X_ATTRIBUTE26,
821 X_USER_TEMPLATE_NAME,
822 X_DESCRIPTION,
823 X_LAST_UPDATE_DATE,
824 X_LAST_UPDATED_BY,
825 X_LAST_UPDATE_LOGIN
826 );
827 exception
828 when no_data_found then
829 INSERT_ROW (
830 X_ROWID,
831 X_FORM_TEMPLATE_ID,
832 to_number(X_OBJECT_VERSION_NUMBER),
833 X_APPLICATION_ID,
834 X_FORM_ID,
835 X_TEMPLATE_NAME,
836 X_ENABLED_FLAG,
837 X_LEGISLATION_CODE,
838 X_ATTRIBUTE_CATEGORY,
839 X_ATTRIBUTE1,
840 X_ATTRIBUTE2,
841 X_ATTRIBUTE3,
842 X_ATTRIBUTE4,
843 X_ATTRIBUTE5,
844 X_ATTRIBUTE6,
845 X_ATTRIBUTE7,
846 X_ATTRIBUTE8,
847 X_ATTRIBUTE9,
848 X_ATTRIBUTE10,
849 X_ATTRIBUTE11,
850 X_ATTRIBUTE12,
851 X_ATTRIBUTE13,
852 X_ATTRIBUTE14,
853 X_ATTRIBUTE15,
854 X_ATTRIBUTE16,
855 X_ATTRIBUTE17,
856 X_ATTRIBUTE18,
857 X_ATTRIBUTE19,
858 X_ATTRIBUTE20,
859 X_ATTRIBUTE21,
860 X_ATTRIBUTE22,
861 X_ATTRIBUTE23,
862 X_ATTRIBUTE27,
863 X_ATTRIBUTE28,
864 X_ATTRIBUTE29,
865 X_ATTRIBUTE30,
866 X_ATTRIBUTE24,
867 X_ATTRIBUTE25,
868 X_ATTRIBUTE26,
869 X_USER_TEMPLATE_NAME,
870 X_DESCRIPTION,
871 X_CREATION_DATE,
872 X_CREATED_BY,
873 X_LAST_UPDATE_DATE,
874 X_LAST_UPDATED_BY,
875 X_LAST_UPDATE_LOGIN);
876
877 end;
878
879 end LOAD_ROW;
880 end HR_FORM_TEMPLATES_PKG;