eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HR_FORM_DATA_GROUPS_PKG

Source


1 package body HR_FORM_DATA_GROUPS_PKG as
2 /* $Header: hrfdglct.pkb 115.1 2002/12/10 10:49:27 hjonnala noship $ */
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   insert into HR_FORM_DATA_GROUPS_TL (
67     CREATED_BY,
68     CREATION_DATE,
69     USER_DATA_GROUP_NAME,
70     DESCRIPTION,
71     LAST_UPDATE_DATE,
72     LAST_UPDATED_BY,
73     LAST_UPDATE_LOGIN,
74     FORM_DATA_GROUP_ID,
75     LANGUAGE,
76     SOURCE_LANG
77   ) select
78     X_CREATED_BY,
79     X_CREATION_DATE,
80     X_USER_DATA_GROUP_NAME,
81     X_DESCRIPTION,
82     X_LAST_UPDATE_DATE,
83     X_LAST_UPDATED_BY,
84     X_LAST_UPDATE_LOGIN,
85     X_FORM_DATA_GROUP_ID,
86     L.LANGUAGE_CODE,
87     userenv('LANG')
88   from FND_LANGUAGES L
89   where L.INSTALLED_FLAG in ('I', 'B')
90   and not exists
91     (select NULL
92     from HR_FORM_DATA_GROUPS_TL T
93     where T.FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
94     and T.LANGUAGE = L.LANGUAGE_CODE);
95 
96   open c;
97   fetch c into X_ROWID;
98   if (c%notfound) then
99     close c;
100     raise no_data_found;
101   end if;
102   close c;
103 
104 end INSERT_ROW;
105 
106 procedure LOCK_ROW (
107   X_FORM_DATA_GROUP_ID in NUMBER,
108   X_OBJECT_VERSION_NUMBER in NUMBER,
109   X_APPLICATION_ID in NUMBER,
110   X_FORM_ID in NUMBER,
111   X_DATA_GROUP_NAME in VARCHAR2,
112   X_USER_DATA_GROUP_NAME in VARCHAR2,
113   X_DESCRIPTION in VARCHAR2
114 ) is
115   cursor c is select
116       OBJECT_VERSION_NUMBER,
117       APPLICATION_ID,
118       FORM_ID,
119       DATA_GROUP_NAME
120     from HR_FORM_DATA_GROUPS_B
121     where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
122     for update of FORM_DATA_GROUP_ID nowait;
123   recinfo c%rowtype;
124 
125   cursor c1 is select
126       USER_DATA_GROUP_NAME,
127       DESCRIPTION,
128       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
129     from HR_FORM_DATA_GROUPS_TL
130     where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
131     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
132     for update of FORM_DATA_GROUP_ID nowait;
133 begin
134   open c;
135   fetch c into recinfo;
136   if (c%notfound) then
137     close c;
138     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
139     app_exception.raise_exception;
140   end if;
141   close c;
142   if (    (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
143       AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
144       AND (recinfo.FORM_ID = X_FORM_ID)
145       AND (recinfo.DATA_GROUP_NAME = X_DATA_GROUP_NAME)
146   ) then
147     null;
148   else
149     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
150     app_exception.raise_exception;
151   end if;
152 
153   for tlinfo in c1 loop
154     if (tlinfo.BASELANG = 'Y') then
155       if (    (tlinfo.USER_DATA_GROUP_NAME = X_USER_DATA_GROUP_NAME)
156           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
157                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
158       ) then
159         null;
160       else
161         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
162         app_exception.raise_exception;
163       end if;
164     end if;
165   end loop;
166   return;
167 end LOCK_ROW;
168 
169 procedure UPDATE_ROW (
170   X_FORM_DATA_GROUP_ID in NUMBER,
171   X_OBJECT_VERSION_NUMBER in NUMBER,
172   X_APPLICATION_ID in NUMBER,
173   X_FORM_ID in NUMBER,
174   X_DATA_GROUP_NAME in VARCHAR2,
175   X_USER_DATA_GROUP_NAME in VARCHAR2,
176   X_DESCRIPTION in VARCHAR2,
177   X_LAST_UPDATE_DATE in DATE,
178   X_LAST_UPDATED_BY in NUMBER,
179   X_LAST_UPDATE_LOGIN in NUMBER
180 ) is
181 begin
182   update HR_FORM_DATA_GROUPS_B set
183     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
184     APPLICATION_ID = X_APPLICATION_ID,
185     FORM_ID = X_FORM_ID,
186     DATA_GROUP_NAME = X_DATA_GROUP_NAME,
187     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
188     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
189     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
190   where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID;
191 
192   if (sql%notfound) then
193     raise no_data_found;
194   end if;
195 
196   update HR_FORM_DATA_GROUPS_TL set
197     USER_DATA_GROUP_NAME = X_USER_DATA_GROUP_NAME,
198     DESCRIPTION = X_DESCRIPTION,
199     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
200     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
201     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
202     SOURCE_LANG = userenv('LANG')
203   where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID
204   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
205 
206   if (sql%notfound) then
207     raise no_data_found;
208   end if;
209 end UPDATE_ROW;
210 
211 procedure DELETE_ROW (
212   X_FORM_DATA_GROUP_ID in NUMBER
213 ) is
214 begin
215   delete from HR_FORM_DATA_GROUPS_TL
216   where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID;
217 
218   if (sql%notfound) then
219     raise no_data_found;
220   end if;
221 
222   delete from HR_FORM_DATA_GROUPS_B
223   where FORM_DATA_GROUP_ID = X_FORM_DATA_GROUP_ID;
224 
225   if (sql%notfound) then
226     raise no_data_found;
227   end if;
228 end DELETE_ROW;
229 
230 procedure ADD_LANGUAGE
231 is
232 begin
233   delete from HR_FORM_DATA_GROUPS_TL T
234   where not exists
235     (select NULL
236     from HR_FORM_DATA_GROUPS_B B
237     where B.FORM_DATA_GROUP_ID = T.FORM_DATA_GROUP_ID
238     );
239 
240   update HR_FORM_DATA_GROUPS_TL T set (
241       USER_DATA_GROUP_NAME,
242       DESCRIPTION
243     ) = (select
244       B.USER_DATA_GROUP_NAME,
245       B.DESCRIPTION
246     from HR_FORM_DATA_GROUPS_TL B
247     where B.FORM_DATA_GROUP_ID = T.FORM_DATA_GROUP_ID
248     and B.LANGUAGE = T.SOURCE_LANG)
249   where (
250       T.FORM_DATA_GROUP_ID,
251       T.LANGUAGE
252   ) in (select
253       SUBT.FORM_DATA_GROUP_ID,
254       SUBT.LANGUAGE
255     from HR_FORM_DATA_GROUPS_TL SUBB, HR_FORM_DATA_GROUPS_TL SUBT
256     where SUBB.FORM_DATA_GROUP_ID = SUBT.FORM_DATA_GROUP_ID
257     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
258     and (SUBB.USER_DATA_GROUP_NAME <> SUBT.USER_DATA_GROUP_NAME
259       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
260       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
261       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
262   ));
263 
264   insert into HR_FORM_DATA_GROUPS_TL (
265     CREATED_BY,
266     CREATION_DATE,
267     USER_DATA_GROUP_NAME,
268     DESCRIPTION,
269     LAST_UPDATE_DATE,
270     LAST_UPDATED_BY,
271     LAST_UPDATE_LOGIN,
272     FORM_DATA_GROUP_ID,
273     LANGUAGE,
274     SOURCE_LANG
275   ) select
276     B.CREATED_BY,
277     B.CREATION_DATE,
278     B.USER_DATA_GROUP_NAME,
279     B.DESCRIPTION,
280     B.LAST_UPDATE_DATE,
281     B.LAST_UPDATED_BY,
282     B.LAST_UPDATE_LOGIN,
283     B.FORM_DATA_GROUP_ID,
284     L.LANGUAGE_CODE,
285     B.SOURCE_LANG
286   from HR_FORM_DATA_GROUPS_TL B, FND_LANGUAGES L
287   where L.INSTALLED_FLAG in ('I', 'B')
288   and B.LANGUAGE = userenv('LANG')
289   and not exists
290     (select NULL
291     from HR_FORM_DATA_GROUPS_TL T
292     where T.FORM_DATA_GROUP_ID = B.FORM_DATA_GROUP_ID
293     and T.LANGUAGE = L.LANGUAGE_CODE);
294 end ADD_LANGUAGE;
295 
296 procedure TRANSLATE_ROW (
297   X_APPLICATION_SHORT_NAME in VARCHAR2,
298   X_FORM_NAME in VARCHAR2,
299   X_DATA_GROUP_NAME in VARCHAR2,
300   X_OWNER in VARCHAR2,
301   X_USER_DATA_GROUP_NAME in VARCHAR2,
302   X_DESCRIPTION in VARCHAR2) is
303   X_ROWID ROWID;
304   X_CREATION_DATE DATE;
305   X_CREATED_BY NUMBER;
306   X_LAST_UPDATE_DATE DATE;
307   X_LAST_UPDATED_BY NUMBER;
308   X_LAST_UPDATE_LOGIN NUMBER;
309   X_FORM_ID NUMBER;
310   X_APPLICATION_ID NUMBER;
311   X_FORM_DATA_GROUP_ID NUMBER;
312 begin
313 
314   OWNER_TO_WHO (
315     X_OWNER,
316     X_CREATION_DATE,
317     X_CREATED_BY,
318     X_LAST_UPDATE_DATE,
319     X_LAST_UPDATED_BY,
320     X_LAST_UPDATE_LOGIN
321   );
322 
323  select application_id
324  into x_application_id
325  from fnd_application
326  where application_short_name = x_application_short_name;
327 
328  select form_id
329  into x_form_id
330  from fnd_form
331  where form_name = x_form_name
332  and application_id = x_application_id;
333 
334  select form_data_group_id
335  into x_form_data_group_id
336  from hr_form_data_groups_b
337  where data_group_name =  x_data_group_name
338  and application_id = x_application_id
339  and form_id = x_form_id;
340 
341  update HR_FORM_DATA_GROUPS_TL set
342   DESCRIPTION = X_DESCRIPTION,
343   USER_DATA_GROUP_NAME = X_USER_DATA_GROUP_NAME,
344   LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
345   LAST_UPDATED_BY = X_LAST_UPDATED_BY,
346   LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
347   SOURCE_LANG = userenv('LANG')
348  where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
349  and form_data_group_id = x_form_data_group_id;
350 
351 end TRANSLATE_ROW;
352 
353 procedure LOAD_ROW (
354   X_APPLICATION_SHORT_NAME in VARCHAR2,
355   X_FORM_NAME in VARCHAR2,
356   X_DATA_GROUP_NAME in VARCHAR2,
357   X_OWNER in VARCHAR2,
358   X_OBJECT_VERSION_NUMBER in VARCHAR2,
359   X_USER_DATA_GROUP_NAME in VARCHAR2,
360   X_DESCRIPTION in VARCHAR2) is
361   X_ROWID ROWID;
362   X_CREATION_DATE DATE;
363   X_CREATED_BY NUMBER;
364   X_LAST_UPDATE_DATE DATE;
365   X_LAST_UPDATED_BY NUMBER;
366   X_LAST_UPDATE_LOGIN NUMBER;
367   X_FORM_ID NUMBER;
368   X_APPLICATION_ID NUMBER;
369   X_FORM_DATA_GROUP_ID NUMBER;
370 begin
371 
372   OWNER_TO_WHO (
373     X_OWNER,
374     X_CREATION_DATE,
375     X_CREATED_BY,
376     X_LAST_UPDATE_DATE,
377     X_LAST_UPDATED_BY,
378     X_LAST_UPDATE_LOGIN
379   );
380 
381  select application_id
382  into x_application_id
383  from fnd_application
384  where application_short_name = x_application_short_name;
385 
386  select form_id
387  into x_form_id
388  from fnd_form
389  where form_name = x_form_name
390  and application_id = x_application_id;
391 
392  begin
393    select form_data_group_id
394    into x_form_data_group_id
395    from hr_form_data_groups_b
396    where data_group_name =  x_data_group_name
397    and application_id = x_application_id
398    and form_id = x_form_id;
399  exception
400    when no_data_found then
401      select hr_form_data_groups_b_s.nextval
402      into x_form_data_group_id
403      from dual;
404  end;
405 
406  begin
407    UPDATE_ROW (
408      X_FORM_DATA_GROUP_ID,
409      to_number(X_OBJECT_VERSION_NUMBER),
410      X_APPLICATION_ID,
411      X_FORM_ID,
412      X_DATA_GROUP_NAME,
413      X_USER_DATA_GROUP_NAME,
414      X_DESCRIPTION,
415      X_LAST_UPDATE_DATE,
416      X_LAST_UPDATED_BY,
417      X_LAST_UPDATE_LOGIN
418    );
419 
420  exception
421    when no_data_found then
422      INSERT_ROW (
423        X_ROWID,
424        X_FORM_DATA_GROUP_ID,
425        to_number(X_OBJECT_VERSION_NUMBER),
426        X_APPLICATION_ID,
427        X_FORM_ID,
428        X_DATA_GROUP_NAME,
429        X_USER_DATA_GROUP_NAME,
430        X_DESCRIPTION,
431        X_CREATION_DATE,
432        X_CREATED_BY,
433        X_LAST_UPDATE_DATE,
434        X_LAST_UPDATED_BY,
435        X_LAST_UPDATE_LOGIN);
436  end;
437 
438 end LOAD_ROW;
439 end HR_FORM_DATA_GROUPS_PKG;