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