[Home] [Help]
PACKAGE BODY: APPS.HR_FORM_DATA_GROUPS_PKG
Source
1 package body HR_FORM_DATA_GROUPS_PKG as
2 /* $Header: hrfdglct.pkb 120.1 2011/04/28 11:59:46 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_DATA_GROUP_ID in NUMBER,
26 X_OBJECT_VERSION_NUMBER in NUMBER,
27 X_APPLICATION_ID in NUMBER,
28 X_FORM_ID in NUMBER,
29 X_DATA_GROUP_NAME in VARCHAR2,
30 X_USER_DATA_GROUP_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_DATA_GROUPS_B
39 where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
40 ;
41 begin
42 insert into HR_FORM_DATA_GROUPS_B (
43 OBJECT_VERSION_NUMBER,
44 APPLICATION_ID,
45 FORM_ID,
46 DATA_GROUP_NAME,
47 FORM_DATA_GROUP_ID,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN
53 ) values (
54 X_OBJECT_VERSION_NUMBER,
55 X_APPLICATION_ID,
56 X_FORM_ID,
57 X_DATA_GROUP_NAME,
58 X_FORM_DATA_GROUP_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 BEGIN
71 PER_RIC_PKG.chk_integrity (
72 p_entity_name => 'HR_FORM_DATA_GROUPS_TL',
73 p_ref_entity => 'HR_FORM_DATA_GROUPS_B',
74 p_ref_column_name => 'FORM_DATA_GROUP_ID',
75 p_ref_col_value_number => X_FORM_DATA_GROUP_ID,
76 p_ref_col_value_varchar => NULL,
77 p_ref_col_value_date => NULL,
78 p_ref_type => 'INS');
79
80 END;
81 --
82 -- Code Ends Here.
83 --
84
85 insert into HR_FORM_DATA_GROUPS_TL (
86 CREATED_BY,
87 CREATION_DATE,
88 USER_DATA_GROUP_NAME,
89 DESCRIPTION,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_LOGIN,
93 FORM_DATA_GROUP_ID,
94 LANGUAGE,
95 SOURCE_LANG
96 ) select
97 X_CREATED_BY,
98 X_CREATION_DATE,
99 X_USER_DATA_GROUP_NAME,
100 X_DESCRIPTION,
101 X_LAST_UPDATE_DATE,
102 X_LAST_UPDATED_BY,
103 X_LAST_UPDATE_LOGIN,
104 X_FORM_DATA_GROUP_ID,
105 L.LANGUAGE_CODE,
106 userenv('LANG')
107 from FND_LANGUAGES L
108 where L.INSTALLED_FLAG in ('I', 'B')
109 and not exists
110 (select NULL
111 from HR_FORM_DATA_GROUPS_TL T
112 where T.FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
113 and T.LANGUAGE = L.LANGUAGE_CODE);
114
115 open c;
116 fetch c into X_ROWID;
117 if (c%notfound) then
118 close c;
119 raise no_data_found;
120 end if;
121 close c;
122
123 end INSERT_ROW;
124
125 procedure LOCK_ROW (
126 X_FORM_DATA_GROUP_ID in NUMBER,
127 X_OBJECT_VERSION_NUMBER in NUMBER,
128 X_APPLICATION_ID in NUMBER,
129 X_FORM_ID in NUMBER,
130 X_DATA_GROUP_NAME in VARCHAR2,
131 X_USER_DATA_GROUP_NAME in VARCHAR2,
132 X_DESCRIPTION in VARCHAR2
133 ) is
134 cursor c is select
135 OBJECT_VERSION_NUMBER,
136 APPLICATION_ID,
137 FORM_ID,
138 DATA_GROUP_NAME
139 from HR_FORM_DATA_GROUPS_B
140 where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
141 for update of FORM_DATA_GROUP_ID nowait;
142 recinfo c%rowtype;
143
144 cursor c1 is select
145 USER_DATA_GROUP_NAME,
146 DESCRIPTION,
147 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
148 from HR_FORM_DATA_GROUPS_TL
149 where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
150 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
151 for update of FORM_DATA_GROUP_ID nowait;
152 begin
153 open c;
154 fetch c into recinfo;
155 if (c%notfound) then
156 close c;
157 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
158 app_exception.raise_exception;
159 end if;
160 close c;
161 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
162 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
163 AND (recinfo.FORM_ID = X_FORM_ID)
164 AND (recinfo.DATA_GROUP_NAME = X_DATA_GROUP_NAME)
165 ) then
166 null;
167 else
168 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
169 app_exception.raise_exception;
170 end if;
171
172 for tlinfo in c1 loop
173 if (tlinfo.BASELANG = 'Y') then
174 if ( (tlinfo.USER_DATA_GROUP_NAME = X_USER_DATA_GROUP_NAME)
175 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
176 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
177 ) then
178 null;
179 else
180 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
181 app_exception.raise_exception;
182 end if;
183 end if;
184 end loop;
185 return;
186 end LOCK_ROW;
187
188 procedure UPDATE_ROW (
189 X_FORM_DATA_GROUP_ID in NUMBER,
190 X_OBJECT_VERSION_NUMBER in NUMBER,
191 X_APPLICATION_ID in NUMBER,
192 X_FORM_ID in NUMBER,
193 X_DATA_GROUP_NAME in VARCHAR2,
194 X_USER_DATA_GROUP_NAME in VARCHAR2,
195 X_DESCRIPTION in VARCHAR2,
196 X_LAST_UPDATE_DATE in DATE,
197 X_LAST_UPDATED_BY in NUMBER,
198 X_LAST_UPDATE_LOGIN in NUMBER
199 ) is
200 begin
201 update HR_FORM_DATA_GROUPS_B set
202 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
203 APPLICATION_ID = X_APPLICATION_ID,
204 FORM_ID = X_FORM_ID,
205 DATA_GROUP_NAME = X_DATA_GROUP_NAME,
206 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
207 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
208 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
209 where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID;
210
211 if (sql%notfound) then
212 raise no_data_found;
213 end if;
214
215 update HR_FORM_DATA_GROUPS_TL set
216 USER_DATA_GROUP_NAME = X_USER_DATA_GROUP_NAME,
217 DESCRIPTION = X_DESCRIPTION,
218 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
219 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
220 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
221 SOURCE_LANG = userenv('LANG')
222 where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
223 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
224
225 if (sql%notfound) then
226 raise no_data_found;
227 end if;
228 end UPDATE_ROW;
229
230 procedure DELETE_ROW (
231 X_FORM_DATA_GROUP_ID in NUMBER
232 ) is
233 begin
234 delete from HR_FORM_DATA_GROUPS_TL
235 where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID;
236
237 if (sql%notfound) then
238 raise no_data_found;
239 end if;
240
241 --
242 -- Added the following code as a part of Zero Downtime Patching Project.
243 -- Code Starts Here.
244 --
245
246 BEGIN
247 PER_RIC_PKG.chk_integrity (
248 p_entity_name => 'HR_FORM_DATA_GROUPS_B',
249 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
250 PER_RIC_PKG.ref_info_rec('HR_FORM_DATA_GROUPS_TL', PER_RIC_PKG.column_info_tbl(
251 PER_RIC_PKG.col_info_rec('FORM_DATA_GROUP_ID',NULL,X_FORM_DATA_GROUP_ID,NULL))),
252 PER_RIC_PKG.ref_info_rec('HR_FORM_DATA_GROUP_ITEMS', PER_RIC_PKG.column_info_tbl(
253 PER_RIC_PKG.col_info_rec('FORM_DATA_GROUP_ID',NULL,X_FORM_DATA_GROUP_ID,NULL))),
254 PER_RIC_PKG.ref_info_rec('HR_TEMPLATE_DATA_GROUPS', PER_RIC_PKG.column_info_tbl(
255 PER_RIC_PKG.col_info_rec('FORM_DATA_GROUP_ID',NULL,X_FORM_DATA_GROUP_ID,NULL)))
256 ),
257 p_ref_type => 'DEL');
258
259 END;
260
261 --
262 -- Code Ends Here.
263 --
264
265 delete from HR_FORM_DATA_GROUPS_B
266 where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID;
267
268 if (sql%notfound) then
269 raise no_data_found;
270 end if;
271 end DELETE_ROW;
272
273 procedure ADD_LANGUAGE
274 is
275 begin
276 delete from HR_FORM_DATA_GROUPS_TL T
277 where not exists
278 (select NULL
279 from HR_FORM_DATA_GROUPS_B B
280 where B.FORM_DATA_GROUP_ID = T.FORM_DATA_GROUP_ID
281 );
282
283 update HR_FORM_DATA_GROUPS_TL T set (
284 USER_DATA_GROUP_NAME,
285 DESCRIPTION
286 ) = (select
287 B.USER_DATA_GROUP_NAME,
288 B.DESCRIPTION
289 from HR_FORM_DATA_GROUPS_TL B
290 where B.FORM_DATA_GROUP_ID = T.FORM_DATA_GROUP_ID
291 and B.LANGUAGE = T.SOURCE_LANG)
292 where (
293 T.FORM_DATA_GROUP_ID,
294 T.LANGUAGE
295 ) in (select
296 SUBT.FORM_DATA_GROUP_ID,
297 SUBT.LANGUAGE
298 from HR_FORM_DATA_GROUPS_TL SUBB, HR_FORM_DATA_GROUPS_TL SUBT
299 where SUBB.FORM_DATA_GROUP_ID = SUBT.FORM_DATA_GROUP_ID
300 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
301 and (SUBB.USER_DATA_GROUP_NAME <> SUBT.USER_DATA_GROUP_NAME
302 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
303 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
304 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
305 ));
306
307
308 insert into HR_FORM_DATA_GROUPS_TL (
309 CREATED_BY,
310 CREATION_DATE,
311 USER_DATA_GROUP_NAME,
312 DESCRIPTION,
313 LAST_UPDATE_DATE,
314 LAST_UPDATED_BY,
315 LAST_UPDATE_LOGIN,
316 FORM_DATA_GROUP_ID,
317 LANGUAGE,
318 SOURCE_LANG
319 ) select
320 B.CREATED_BY,
321 B.CREATION_DATE,
322 B.USER_DATA_GROUP_NAME,
323 B.DESCRIPTION,
324 B.LAST_UPDATE_DATE,
325 B.LAST_UPDATED_BY,
326 B.LAST_UPDATE_LOGIN,
327 B.FORM_DATA_GROUP_ID,
328 L.LANGUAGE_CODE,
329 B.SOURCE_LANG
330 from HR_FORM_DATA_GROUPS_TL B, FND_LANGUAGES L
331 where L.INSTALLED_FLAG in ('I', 'B')
332 and B.LANGUAGE = userenv('LANG')
333 and not exists
334 (select NULL
335 from HR_FORM_DATA_GROUPS_TL T
336 where T.FORM_DATA_GROUP_ID = B.FORM_DATA_GROUP_ID
337 and T.LANGUAGE = L.LANGUAGE_CODE);
338 end ADD_LANGUAGE;
339
340 procedure TRANSLATE_ROW (
341 X_APPLICATION_SHORT_NAME in VARCHAR2,
342 X_FORM_NAME in VARCHAR2,
343 X_DATA_GROUP_NAME in VARCHAR2,
344 X_OWNER in VARCHAR2,
345 X_USER_DATA_GROUP_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_DATA_GROUP_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
381 where data_group_name = x_data_group_name
378 select form_data_group_id
379 into x_form_data_group_id
380 from hr_form_data_groups_b
382 and application_id = x_application_id
383 and form_id = x_form_id;
384
385 update HR_FORM_DATA_GROUPS_TL set
386 DESCRIPTION = X_DESCRIPTION,
387 USER_DATA_GROUP_NAME = X_USER_DATA_GROUP_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_data_group_id = x_form_data_group_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_DATA_GROUP_NAME in VARCHAR2,
401 X_OWNER in VARCHAR2,
402 X_OBJECT_VERSION_NUMBER in VARCHAR2,
403 X_USER_DATA_GROUP_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_DATA_GROUP_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_data_group_id
438 into x_form_data_group_id
439 from hr_form_data_groups_b
440 where data_group_name = x_data_group_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_data_groups_b_s.nextval
446 into x_form_data_group_id
447 from dual;
448 end;
449
450 begin
451 UPDATE_ROW (
452 X_FORM_DATA_GROUP_ID,
453 to_number(X_OBJECT_VERSION_NUMBER),
454 X_APPLICATION_ID,
455 X_FORM_ID,
456 X_DATA_GROUP_NAME,
457 X_USER_DATA_GROUP_NAME,
458 X_DESCRIPTION,
459 X_LAST_UPDATE_DATE,
460 X_LAST_UPDATED_BY,
461 X_LAST_UPDATE_LOGIN
462 );
463
464 exception
465 when no_data_found then
466 INSERT_ROW (
467 X_ROWID,
468 X_FORM_DATA_GROUP_ID,
469 to_number(X_OBJECT_VERSION_NUMBER),
470 X_APPLICATION_ID,
471 X_FORM_ID,
472 X_DATA_GROUP_NAME,
473 X_USER_DATA_GROUP_NAME,
474 X_DESCRIPTION,
475 X_CREATION_DATE,
476 X_CREATED_BY,
477 X_LAST_UPDATE_DATE,
478 X_LAST_UPDATED_BY,
479 X_LAST_UPDATE_LOGIN);
480 end;
481
482 end LOAD_ROW;
483 end HR_FORM_DATA_GROUPS_PKG;