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