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