[Home] [Help]
PACKAGE BODY: APPS.HR_FORM_TAB_PAGES_PKG
Source
1 package body HR_FORM_TAB_PAGES_PKG as
2 /* $Header: hrftplct.pkb 120.1 2011/04/28 11:55:52 sidsaxen ship $ */
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 procedure INSERT_ROW (
24 X_ROWID in out nocopy VARCHAR2,
25 X_FORM_TAB_PAGE_ID in NUMBER,
26 X_VISIBLE_OVERRIDE in NUMBER,
27 X_OBJECT_VERSION_NUMBER in NUMBER,
28 X_FORM_CANVAS_ID in NUMBER,
29 X_TAB_PAGE_NAME in VARCHAR2,
30 X_DISPLAY_ORDER in NUMBER,
31 X_USER_TAB_PAGE_NAME in VARCHAR2,
32 X_DESCRIPTION in VARCHAR2,
33 X_CREATION_DATE in DATE,
34 X_CREATED_BY in NUMBER,
35 X_LAST_UPDATE_DATE in DATE,
36 X_LAST_UPDATED_BY in NUMBER,
37 X_LAST_UPDATE_LOGIN in NUMBER
38 ) is
39 cursor C is select ROWID from HR_FORM_TAB_PAGES_B
40 where FORM_TAB_PAGE_ID = X_FORM_TAB_PAGE_ID
41 ;
42 begin
43
44 --
45 -- Added the following code as a part of Zero Downtime Patching Project.
46 -- Code Starts Here.
47 --
48
49 BEGIN
50 PER_RIC_PKG.chk_integrity (
51 p_entity_name => 'HR_FORM_TAB_PAGES_B',
52 p_ref_entity => 'HR_FORM_CANVASES_B',
53 p_ref_column_name => 'FORM_CANVAS_ID',
54 p_ref_col_value_number => X_FORM_CANVAS_ID,
55 p_ref_col_value_varchar => NULL,
56 p_ref_col_value_date => NULL,
57 p_ref_type => 'INS');
58
59 END;
60 --
61 -- Code Ends Here.
62 --
63 insert into HR_FORM_TAB_PAGES_B (
64 VISIBLE_OVERRIDE,
65 FORM_TAB_PAGE_ID,
66 OBJECT_VERSION_NUMBER,
67 FORM_CANVAS_ID,
68 TAB_PAGE_NAME,
69 DISPLAY_ORDER,
70 CREATION_DATE,
71 CREATED_BY,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 LAST_UPDATE_LOGIN
75 ) values (
76 X_VISIBLE_OVERRIDE,
77 X_FORM_TAB_PAGE_ID,
78 X_OBJECT_VERSION_NUMBER,
79 X_FORM_CANVAS_ID,
80 X_TAB_PAGE_NAME,
81 X_DISPLAY_ORDER,
82 X_CREATION_DATE,
83 X_CREATED_BY,
84 X_LAST_UPDATE_DATE,
85 X_LAST_UPDATED_BY,
86 X_LAST_UPDATE_LOGIN
87 );
88
89 --
90 -- Added the following code as a part of Zero Downtime Patching Project.
91 -- Code Starts Here.
92 --
93
94 BEGIN
95 PER_RIC_PKG.chk_integrity (
96 p_entity_name => 'HR_FORM_TAB_PAGES_TL',
97 p_ref_entity => 'HR_FORM_TAB_PAGES_B',
98 p_ref_column_name => 'FORM_TAB_PAGE_ID',
99 p_ref_col_value_number => X_FORM_TAB_PAGE_ID,
100 p_ref_col_value_varchar => NULL,
101 p_ref_col_value_date => NULL,
102 p_ref_type => 'INS');
103
104 END;
105 --
106 -- Code Ends Here.
107 --
108
109 insert into HR_FORM_TAB_PAGES_TL (
110 USER_TAB_PAGE_NAME,
111 DESCRIPTION,
112 LAST_UPDATE_DATE,
113 LAST_UPDATED_BY,
114 LAST_UPDATE_LOGIN,
115 CREATED_BY,
116 CREATION_DATE,
117 FORM_TAB_PAGE_ID,
118 LANGUAGE,
119 SOURCE_LANG
120 ) select
121 X_USER_TAB_PAGE_NAME,
122 X_DESCRIPTION,
123 X_LAST_UPDATE_DATE,
124 X_LAST_UPDATED_BY,
125 X_LAST_UPDATE_LOGIN,
126 X_CREATED_BY,
127 X_CREATION_DATE,
128 X_FORM_TAB_PAGE_ID,
129 L.LANGUAGE_CODE,
130 userenv('LANG')
131 from FND_LANGUAGES L
132 where L.INSTALLED_FLAG in ('I', 'B')
133 and not exists
134 (select NULL
135 from HR_FORM_TAB_PAGES_TL T
136 where T.FORM_TAB_PAGE_ID = X_FORM_TAB_PAGE_ID
137 and T.LANGUAGE = L.LANGUAGE_CODE);
138
139 open c;
140 fetch c into X_ROWID;
141 if (c%notfound) then
142 close c;
143 raise no_data_found;
144 end if;
145 close c;
146
147 end INSERT_ROW;
148
149 procedure LOCK_ROW (
150 X_FORM_TAB_PAGE_ID in NUMBER,
151 X_VISIBLE_OVERRIDE in NUMBER,
152 X_OBJECT_VERSION_NUMBER in NUMBER,
153 X_FORM_CANVAS_ID in NUMBER,
154 X_TAB_PAGE_NAME in VARCHAR2,
155 X_DISPLAY_ORDER in NUMBER,
156 X_USER_TAB_PAGE_NAME in VARCHAR2,
157 X_DESCRIPTION in VARCHAR2
158 ) is
159 cursor c is select
160 VISIBLE_OVERRIDE,
161 OBJECT_VERSION_NUMBER,
162 FORM_CANVAS_ID,
163 TAB_PAGE_NAME,
164 DISPLAY_ORDER
165 from HR_FORM_TAB_PAGES_B
166 where FORM_TAB_PAGE_ID = X_FORM_TAB_PAGE_ID
167 for update of FORM_TAB_PAGE_ID nowait;
168 recinfo c%rowtype;
169
170 cursor c1 is select
171 USER_TAB_PAGE_NAME,
172 DESCRIPTION,
173 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
174 from HR_FORM_TAB_PAGES_TL
175 where FORM_TAB_PAGE_ID = X_FORM_TAB_PAGE_ID
176 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
177 for update of FORM_TAB_PAGE_ID nowait;
178 begin
179 open c;
180 fetch c into recinfo;
181 if (c%notfound) then
182 close c;
183 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
184 app_exception.raise_exception;
185 end if;
186 close c;
187 if ( ((recinfo.VISIBLE_OVERRIDE = X_VISIBLE_OVERRIDE)
188 OR ((recinfo.VISIBLE_OVERRIDE is null) AND (X_VISIBLE_OVERRIDE is null)))
189 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
190 AND (recinfo.FORM_CANVAS_ID = X_FORM_CANVAS_ID)
191 AND (recinfo.TAB_PAGE_NAME = X_TAB_PAGE_NAME)
192 AND (recinfo.DISPLAY_ORDER = X_DISPLAY_ORDER)
193 ) then
194 null;
195 else
196 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
197 app_exception.raise_exception;
198 end if;
199
200 for tlinfo in c1 loop
201 if (tlinfo.BASELANG = 'Y') then
202 if ( (tlinfo.USER_TAB_PAGE_NAME = X_USER_TAB_PAGE_NAME)
203 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
204 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
205 ) then
206 null;
207 else
208 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
209 app_exception.raise_exception;
210 end if;
211 end if;
212 end loop;
213 return;
214 end LOCK_ROW;
215
216 procedure UPDATE_ROW (
217 X_FORM_TAB_PAGE_ID in NUMBER,
218 X_VISIBLE_OVERRIDE in NUMBER,
219 X_OBJECT_VERSION_NUMBER in NUMBER,
220 X_FORM_CANVAS_ID in NUMBER,
221 X_TAB_PAGE_NAME in VARCHAR2,
222 X_DISPLAY_ORDER in NUMBER,
223 X_USER_TAB_PAGE_NAME in VARCHAR2,
224 X_DESCRIPTION in VARCHAR2,
225 X_LAST_UPDATE_DATE in DATE,
226 X_LAST_UPDATED_BY in NUMBER,
227 X_LAST_UPDATE_LOGIN in NUMBER
228 ) is
229 begin
230 update HR_FORM_TAB_PAGES_B set
231 VISIBLE_OVERRIDE = X_VISIBLE_OVERRIDE,
232 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
233 FORM_CANVAS_ID = X_FORM_CANVAS_ID,
234 TAB_PAGE_NAME = X_TAB_PAGE_NAME,
235 DISPLAY_ORDER = X_DISPLAY_ORDER,
236 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
237 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
238 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
239 where FORM_TAB_PAGE_ID = X_FORM_TAB_PAGE_ID;
240
241 if (sql%notfound) then
242 raise no_data_found;
243 end if;
244
245 update HR_FORM_TAB_PAGES_TL set
246 USER_TAB_PAGE_NAME = X_USER_TAB_PAGE_NAME,
247 DESCRIPTION = X_DESCRIPTION,
248 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
249 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
250 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
251 SOURCE_LANG = userenv('LANG')
252 where FORM_TAB_PAGE_ID = X_FORM_TAB_PAGE_ID
253 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
254
255 if (sql%notfound) then
256 raise no_data_found;
257 end if;
258 end UPDATE_ROW;
259
260 procedure DELETE_ROW (
261 X_FORM_TAB_PAGE_ID in NUMBER
262 ) is
263 begin
264 delete from HR_FORM_TAB_PAGES_TL
265 where FORM_TAB_PAGE_ID = X_FORM_TAB_PAGE_ID;
266
267 if (sql%notfound) then
268 raise no_data_found;
269 end if;
270 --
271 -- Added the following code as a part of Zero Downtime Patching Project.
272 -- Code Starts Here.
273 --
274
275 BEGIN
276 PER_RIC_PKG.chk_integrity (
277 p_entity_name => 'HR_FORM_TAB_PAGES_B',
278 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
279 PER_RIC_PKG.ref_info_rec('HR_FORM_ITEMS_B', PER_RIC_PKG.column_info_tbl(
280 PER_RIC_PKG.col_info_rec('FORM_TAB_PAGE_ID',NULL,X_FORM_TAB_PAGE_ID,NULL))),
281 PER_RIC_PKG.ref_info_rec('HR_FORM_ITEMS_B', PER_RIC_PKG.column_info_tbl(
282 PER_RIC_PKG.col_info_rec('FORM_TAB_PAGE_ID_OVERRIDE',NULL,X_FORM_TAB_PAGE_ID,NULL))),
283 PER_RIC_PKG.ref_info_rec('HR_FORM_TAB_PAGES_TL', PER_RIC_PKG.column_info_tbl(
284 PER_RIC_PKG.col_info_rec('FORM_TAB_PAGE_ID',NULL,X_FORM_TAB_PAGE_ID,NULL))),
285 PER_RIC_PKG.ref_info_rec('HR_FORM_TAB_STACKED_CANVASES', PER_RIC_PKG.column_info_tbl(
286 PER_RIC_PKG.col_info_rec('FORM_TAB_PAGE_ID',NULL,X_FORM_TAB_PAGE_ID,NULL))),
287 PER_RIC_PKG.ref_info_rec('HR_TAB_PAGE_PROPERTIES_B', PER_RIC_PKG.column_info_tbl(
288 PER_RIC_PKG.col_info_rec('FORM_TAB_PAGE_ID',NULL,X_FORM_TAB_PAGE_ID,NULL))),
289 PER_RIC_PKG.ref_info_rec('HR_TEMPLATE_TAB_PAGES_B', PER_RIC_PKG.column_info_tbl(
290 PER_RIC_PKG.col_info_rec('FORM_TAB_PAGE_ID',NULL,X_FORM_TAB_PAGE_ID,NULL)))
291 ),
292 p_ref_type => 'DEL');
293
294 END;
295 --
296 -- Code Ends Here.
297 --
298 delete from HR_FORM_TAB_PAGES_B
299 where FORM_TAB_PAGE_ID = X_FORM_TAB_PAGE_ID;
300
301 if (sql%notfound) then
302 raise no_data_found;
303 end if;
304 end DELETE_ROW;
305
306 procedure ADD_LANGUAGE
307 is
308 begin
309 delete from HR_FORM_TAB_PAGES_TL T
310 where not exists
311 (select NULL
312 from HR_FORM_TAB_PAGES_B B
313 where B.FORM_TAB_PAGE_ID = T.FORM_TAB_PAGE_ID
314 );
315
316 update HR_FORM_TAB_PAGES_TL T set (
317 USER_TAB_PAGE_NAME,
318 DESCRIPTION
319 ) = (select
320 B.USER_TAB_PAGE_NAME,
321 B.DESCRIPTION
322 from HR_FORM_TAB_PAGES_TL B
323 where B.FORM_TAB_PAGE_ID = T.FORM_TAB_PAGE_ID
324 and B.LANGUAGE = T.SOURCE_LANG)
325 where (
326 T.FORM_TAB_PAGE_ID,
327 T.LANGUAGE
328 ) in (select
329 SUBT.FORM_TAB_PAGE_ID,
330 SUBT.LANGUAGE
331 from HR_FORM_TAB_PAGES_TL SUBB, HR_FORM_TAB_PAGES_TL SUBT
332 where SUBB.FORM_TAB_PAGE_ID = SUBT.FORM_TAB_PAGE_ID
333 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
334 and (SUBB.USER_TAB_PAGE_NAME <> SUBT.USER_TAB_PAGE_NAME
335 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
336 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
337 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
338 ));
339
340 insert into HR_FORM_TAB_PAGES_TL (
341 USER_TAB_PAGE_NAME,
342 DESCRIPTION,
343 LAST_UPDATE_DATE,
344 LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN,
346 CREATED_BY,
347 CREATION_DATE,
348 FORM_TAB_PAGE_ID,
349 LANGUAGE,
350 SOURCE_LANG
351 ) select
352 B.USER_TAB_PAGE_NAME,
353 B.DESCRIPTION,
354 B.LAST_UPDATE_DATE,
355 B.LAST_UPDATED_BY,
356 B.LAST_UPDATE_LOGIN,
357 B.CREATED_BY,
358 B.CREATION_DATE,
359 B.FORM_TAB_PAGE_ID,
360 L.LANGUAGE_CODE,
361 B.SOURCE_LANG
362 from HR_FORM_TAB_PAGES_TL B, FND_LANGUAGES L
363 where L.INSTALLED_FLAG in ('I', 'B')
364 and B.LANGUAGE = userenv('LANG')
365 and not exists
366 (select NULL
367 from HR_FORM_TAB_PAGES_TL T
368 where T.FORM_TAB_PAGE_ID = B.FORM_TAB_PAGE_ID
369 and T.LANGUAGE = L.LANGUAGE_CODE);
370 end ADD_LANGUAGE;
371 procedure TRANSLATE_ROW (
372 X_APPLICATION_SHORT_NAME in VARCHAR2,
373 X_FORM_NAME in VARCHAR2,
374 X_TAB_PAGE_NAME in VARCHAR2,
375 X_CANVAS_NAME in VARCHAR2,
376 X_WINDOW_NAME in VARCHAR2,
377 X_OWNER in VARCHAR2,
378 X_USER_TAB_PAGE_NAME in VARCHAR2,
382 X_CREATED_BY NUMBER;
379 X_DESCRIPTION in VARCHAR2) is
380 X_ROWID ROWID;
381 X_CREATION_DATE DATE;
383 X_LAST_UPDATE_DATE DATE;
384 X_LAST_UPDATED_BY NUMBER;
385 X_LAST_UPDATE_LOGIN NUMBER;
386 X_FORM_ID NUMBER;
387 X_APPLICATION_ID NUMBER;
388 X_FORM_CANVAS_ID NUMBER;
389 X_FORM_TAB_PAGE_ID NUMBER;
390 X_FORM_WINDOW_ID NUMBER;
391 begin
392
393 OWNER_TO_WHO (
394 X_OWNER,
395 X_CREATION_DATE,
396 X_CREATED_BY,
397 X_LAST_UPDATE_DATE,
398 X_LAST_UPDATED_BY,
399 X_LAST_UPDATE_LOGIN
400 );
401
402 select application_id
403 into x_application_id
404 from fnd_application
405 where application_short_name = x_application_short_name;
406
407 select form_id
408 into x_form_id
409 from fnd_form
410 where form_name = x_form_name
411 and application_id = x_application_id;
412
413 select hfc.form_canvas_id,hfw.form_window_id,ftp.form_tab_page_id
414 into x_form_canvas_id,x_form_window_id,x_form_tab_page_id
415 from hr_form_canvases_b hfc
416 ,hr_form_windows_b hfw
417 ,hr_form_tab_pages_b ftp
418 where hfc.canvas_name = x_canvas_name
419 and hfw.application_id = x_application_id
420 and hfw.form_id = x_form_id
421 and hfc.form_window_id = hfw.form_window_id
422 and hfc.form_canvas_id = ftp.form_canvas_id
423 and ftp.tab_page_name = x_tab_page_name
424 and hfw.window_name = x_window_name;
425
426 update HR_FORM_TAB_PAGES_TL set
427 DESCRIPTION = X_DESCRIPTION,
428 USER_TAB_PAGE_NAME = X_USER_TAB_PAGE_NAME,
429 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
430 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
431 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
432 SOURCE_LANG = userenv('LANG')
433 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
434 and form_tab_page_id = x_form_tab_page_id;
435
436 end TRANSLATE_ROW;
437
438 procedure LOAD_ROW (
439 X_APPLICATION_SHORT_NAME in VARCHAR2,
440 X_FORM_NAME in VARCHAR2,
441 X_TAB_PAGE_NAME in VARCHAR2,
442 X_CANVAS_NAME in VARCHAR2,
443 X_WINDOW_NAME in VARCHAR2,
444 X_OWNER in VARCHAR2,
445 X_OBJECT_VERSION_NUMBER in VARCHAR2,
446 X_DISPLAY_ORDER in VARCHAR2,
447 X_VISIBLE_OVERRIDE in VARCHAR2,
448 X_USER_TAB_PAGE_NAME in VARCHAR2,
449 X_DESCRIPTION in VARCHAR2) is
450 X_ROWID ROWID;
451 X_CREATION_DATE DATE;
452 X_CREATED_BY NUMBER;
453 X_LAST_UPDATE_DATE DATE;
454 X_LAST_UPDATED_BY NUMBER;
455 X_LAST_UPDATE_LOGIN NUMBER;
456 X_FORM_ID NUMBER;
457 X_APPLICATION_ID NUMBER;
458 X_FORM_CANVAS_ID NUMBER;
459 X_FORM_TAB_PAGE_ID NUMBER;
460 X_FORM_WINDOW_ID NUMBER;
461 begin
462
463 OWNER_TO_WHO (
464 X_OWNER,
465 X_CREATION_DATE,
466 X_CREATED_BY,
467 X_LAST_UPDATE_DATE,
468 X_LAST_UPDATED_BY,
469 X_LAST_UPDATE_LOGIN
470 );
471
472 select application_id
473 into x_application_id
474 from fnd_application
475 where application_short_name = x_application_short_name;
476
477 select form_id
478 into x_form_id
479 from fnd_form
480 where form_name = x_form_name
481 and application_id = x_application_id;
482
483 select hfw.form_window_id
484 into x_form_window_id
485 from hr_form_windows_b hfw
486 where hfw.application_id = x_application_id
487 and hfw.form_id = x_form_id
488 and hfw.window_name = x_window_name;
489
490 select hfc.form_canvas_id
491 into x_form_canvas_id
492 from hr_form_canvases_b hfc
493 where hfc.canvas_name = x_canvas_name
494 and hfc.form_window_id = x_form_window_id;
495
496 begin
497 select ftp.form_tab_page_id
498 into x_form_tab_page_id
499 from hr_form_tab_pages_b ftp
500 where ftp.form_canvas_id = x_form_canvas_id
501 and ftp.tab_page_name = x_tab_page_name;
502 exception
503 when no_data_found then
504 select hr_form_tab_pages_b_s.nextval
505 into x_form_tab_page_id
506 from dual;
507 end;
508
509 begin
510 UPDATE_ROW ( X_FORM_TAB_PAGE_ID,
511 to_number(X_VISIBLE_OVERRIDE),
512 to_number(X_OBJECT_VERSION_NUMBER),
513 X_FORM_CANVAS_ID,
514 X_TAB_PAGE_NAME,
515 to_number(X_DISPLAY_ORDER),
516 X_USER_TAB_PAGE_NAME,
517 X_DESCRIPTION,
518 X_LAST_UPDATE_DATE,
519 X_LAST_UPDATED_BY,
520 X_LAST_UPDATE_LOGIN);
521
522 exception
523 when no_data_found then
524 INSERT_ROW (
525 X_ROWID,
526 X_FORM_TAB_PAGE_ID,
527 to_number(X_VISIBLE_OVERRIDE),
528 to_number(X_OBJECT_VERSION_NUMBER),
529 X_FORM_CANVAS_ID,
530 X_TAB_PAGE_NAME,
531 to_number(X_DISPLAY_ORDER),
532 X_USER_TAB_PAGE_NAME,
533 X_DESCRIPTION,
534 X_CREATION_DATE,
535 X_CREATED_BY,
536 X_LAST_UPDATE_DATE,
537 X_LAST_UPDATED_BY,
538 X_LAST_UPDATE_LOGIN);
539 end;
540
541 end LOAD_ROW;
542 end HR_FORM_TAB_PAGES_PKG;