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.FEM_DIMENSION_GRPS_PKG

Source


1 package body FEM_DIMENSION_GRPS_PKG as
2 /* $Header: fem_dimgrp_pkb.plb 120.0 2005/06/06 19:58:16 appldev noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_DIMENSION_GROUP_ID in NUMBER,
6   X_TIME_DIMENSION_GROUP_KEY in NUMBER,
7   X_DIMENSION_ID in NUMBER,
8   X_DIMENSION_GROUP_SEQ in NUMBER,
9   X_TIME_GROUP_TYPE_CODE in VARCHAR2,
10   X_READ_ONLY_FLAG in VARCHAR2,
11   X_OBJECT_VERSION_NUMBER in NUMBER,
12   X_PERSONAL_FLAG in VARCHAR2,
13   X_ENABLED_FLAG in VARCHAR2,
14   X_DIMENSION_GROUP_DISPLAY_CODE in VARCHAR2,
15   X_DIMENSION_GROUP_NAME in VARCHAR2,
16   X_DESCRIPTION in VARCHAR2,
17   X_CREATION_DATE in DATE,
18   X_CREATED_BY in NUMBER,
19   X_LAST_UPDATE_DATE in DATE,
20   X_LAST_UPDATED_BY in NUMBER,
21   X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23   cursor C is select ROWID from FEM_DIMENSION_GRPS_B
24     where DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID
25     ;
26 begin
27   insert into FEM_DIMENSION_GRPS_B (
28     TIME_DIMENSION_GROUP_KEY,
29     DIMENSION_GROUP_ID,
30     DIMENSION_ID,
31     DIMENSION_GROUP_SEQ,
32     TIME_GROUP_TYPE_CODE,
33     READ_ONLY_FLAG,
34     OBJECT_VERSION_NUMBER,
35     PERSONAL_FLAG,
36     ENABLED_FLAG,
37     DIMENSION_GROUP_DISPLAY_CODE,
38     CREATION_DATE,
39     CREATED_BY,
40     LAST_UPDATE_DATE,
41     LAST_UPDATED_BY,
42     LAST_UPDATE_LOGIN
43   ) values (
44     X_TIME_DIMENSION_GROUP_KEY,
45     X_DIMENSION_GROUP_ID,
46     X_DIMENSION_ID,
47     X_DIMENSION_GROUP_SEQ,
48     X_TIME_GROUP_TYPE_CODE,
49     X_READ_ONLY_FLAG,
50     X_OBJECT_VERSION_NUMBER,
51     X_PERSONAL_FLAG,
52     X_ENABLED_FLAG,
53     X_DIMENSION_GROUP_DISPLAY_CODE,
54     X_CREATION_DATE,
55     X_CREATED_BY,
56     X_LAST_UPDATE_DATE,
57     X_LAST_UPDATED_BY,
58     X_LAST_UPDATE_LOGIN
59   );
60 
61   insert into FEM_DIMENSION_GRPS_TL (
62     DIMENSION_GROUP_NAME,
63     DESCRIPTION,
64     CREATION_DATE,
65     CREATED_BY,
66     LAST_UPDATED_BY,
67     LAST_UPDATE_DATE,
68     LAST_UPDATE_LOGIN,
69     DIMENSION_ID,
70     DIMENSION_GROUP_ID,
71     LANGUAGE,
72     SOURCE_LANG
73   ) select
74     X_DIMENSION_GROUP_NAME,
75     X_DESCRIPTION,
76     X_CREATION_DATE,
77     X_CREATED_BY,
78     X_LAST_UPDATED_BY,
79     X_LAST_UPDATE_DATE,
80     X_LAST_UPDATE_LOGIN,
81     X_DIMENSION_ID,
82     X_DIMENSION_GROUP_ID,
83     L.LANGUAGE_CODE,
84     userenv('LANG')
85   from FND_LANGUAGES L
86   where L.INSTALLED_FLAG in ('I', 'B')
87   and not exists
88     (select NULL
89     from FEM_DIMENSION_GRPS_TL T
90     where T.DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID
91     and T.LANGUAGE = L.LANGUAGE_CODE);
92 
93   open c;
94   fetch c into X_ROWID;
95   if (c%notfound) then
96     close c;
97     raise no_data_found;
98   end if;
99   close c;
100 
101 end INSERT_ROW;
102 
103 procedure LOCK_ROW (
104   X_DIMENSION_GROUP_ID in NUMBER,
105   X_TIME_DIMENSION_GROUP_KEY in NUMBER,
106   X_DIMENSION_ID in NUMBER,
107   X_DIMENSION_GROUP_SEQ in NUMBER,
108   X_TIME_GROUP_TYPE_CODE in VARCHAR2,
109   X_READ_ONLY_FLAG in VARCHAR2,
110   X_OBJECT_VERSION_NUMBER in NUMBER,
111   X_PERSONAL_FLAG in VARCHAR2,
112   X_ENABLED_FLAG in VARCHAR2,
113   X_DIMENSION_GROUP_DISPLAY_CODE in VARCHAR2,
114   X_DIMENSION_GROUP_NAME in VARCHAR2,
115   X_DESCRIPTION in VARCHAR2
116 ) is
117   cursor c is select
118       TIME_DIMENSION_GROUP_KEY,
119       DIMENSION_ID,
120       DIMENSION_GROUP_SEQ,
121       TIME_GROUP_TYPE_CODE,
122       READ_ONLY_FLAG,
123       OBJECT_VERSION_NUMBER,
124       PERSONAL_FLAG,
125       ENABLED_FLAG,
126       DIMENSION_GROUP_DISPLAY_CODE
127     from FEM_DIMENSION_GRPS_B
128     where DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID
129     for update of DIMENSION_GROUP_ID nowait;
130   recinfo c%rowtype;
131 
132   cursor c1 is select
133       DIMENSION_GROUP_NAME,
134       DESCRIPTION,
135       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
136     from FEM_DIMENSION_GRPS_TL
137     where DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID
138     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
139     for update of DIMENSION_GROUP_ID nowait;
140 begin
141   open c;
142   fetch c into recinfo;
143   if (c%notfound) then
144     close c;
145     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
146     app_exception.raise_exception;
147   end if;
148   close c;
149   if (    ((recinfo.TIME_DIMENSION_GROUP_KEY = X_TIME_DIMENSION_GROUP_KEY)
150            OR ((recinfo.TIME_DIMENSION_GROUP_KEY is null) AND (X_TIME_DIMENSION_GROUP_KEY is null)))
151       AND (recinfo.DIMENSION_ID = X_DIMENSION_ID)
152       AND (recinfo.DIMENSION_GROUP_SEQ = X_DIMENSION_GROUP_SEQ)
153       AND ((recinfo.TIME_GROUP_TYPE_CODE = X_TIME_GROUP_TYPE_CODE)
154            OR ((recinfo.TIME_GROUP_TYPE_CODE is null) AND (X_TIME_GROUP_TYPE_CODE is null)))
155       AND (recinfo.READ_ONLY_FLAG = X_READ_ONLY_FLAG)
156       AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
157       AND (recinfo.PERSONAL_FLAG = X_PERSONAL_FLAG)
158       AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
159       AND (recinfo.DIMENSION_GROUP_DISPLAY_CODE = X_DIMENSION_GROUP_DISPLAY_CODE)
160   ) then
161     null;
162   else
163     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
164     app_exception.raise_exception;
165   end if;
166 
167   for tlinfo in c1 loop
168     if (tlinfo.BASELANG = 'Y') then
169       if (    (tlinfo.DIMENSION_GROUP_NAME = X_DIMENSION_GROUP_NAME)
170           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
171                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
172       ) then
173         null;
174       else
175         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
176         app_exception.raise_exception;
177       end if;
178     end if;
179   end loop;
180   return;
181 end LOCK_ROW;
182 
183 procedure UPDATE_ROW (
184   X_DIMENSION_GROUP_ID in NUMBER,
185   X_TIME_DIMENSION_GROUP_KEY in NUMBER,
186   X_DIMENSION_ID in NUMBER,
187   X_DIMENSION_GROUP_SEQ in NUMBER,
188   X_TIME_GROUP_TYPE_CODE in VARCHAR2,
189   X_READ_ONLY_FLAG in VARCHAR2,
190   X_OBJECT_VERSION_NUMBER in NUMBER,
191   X_PERSONAL_FLAG in VARCHAR2,
192   X_ENABLED_FLAG in VARCHAR2,
193   X_DIMENSION_GROUP_DISPLAY_CODE in VARCHAR2,
194   X_DIMENSION_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 FEM_DIMENSION_GRPS_B set
202     TIME_DIMENSION_GROUP_KEY = X_TIME_DIMENSION_GROUP_KEY,
203     DIMENSION_ID = X_DIMENSION_ID,
204     DIMENSION_GROUP_SEQ = X_DIMENSION_GROUP_SEQ,
205     TIME_GROUP_TYPE_CODE = X_TIME_GROUP_TYPE_CODE,
206     READ_ONLY_FLAG = X_READ_ONLY_FLAG,
207     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
208     PERSONAL_FLAG = X_PERSONAL_FLAG,
209     ENABLED_FLAG = X_ENABLED_FLAG,
210     DIMENSION_GROUP_DISPLAY_CODE = X_DIMENSION_GROUP_DISPLAY_CODE,
211     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
212     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
213     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
214   where DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID;
215 
216   if (sql%notfound) then
217     raise no_data_found;
218   end if;
219 
220   update FEM_DIMENSION_GRPS_TL set
221     DIMENSION_GROUP_NAME = X_DIMENSION_GROUP_NAME,
222     DESCRIPTION = X_DESCRIPTION,
223     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
224     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
225     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
226     SOURCE_LANG = userenv('LANG')
227   where DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID
228   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
229 
230   if (sql%notfound) then
231     raise no_data_found;
232   end if;
233 end UPDATE_ROW;
234 
235 procedure DELETE_ROW (
236   X_DIMENSION_GROUP_ID in NUMBER
237 ) is
238 begin
239   delete from FEM_DIMENSION_GRPS_TL
240   where DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID;
241 
242   if (sql%notfound) then
243     raise no_data_found;
244   end if;
245 
246   delete from FEM_DIMENSION_GRPS_B
247   where DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID;
248 
249   if (sql%notfound) then
250     raise no_data_found;
251   end if;
252 end DELETE_ROW;
253 
254 procedure ADD_LANGUAGE
255 is
256 begin
257   delete from FEM_DIMENSION_GRPS_TL T
258   where not exists
259     (select NULL
260     from FEM_DIMENSION_GRPS_B B
261     where B.DIMENSION_GROUP_ID = T.DIMENSION_GROUP_ID
262     );
263 
264   update FEM_DIMENSION_GRPS_TL T set (
265       DIMENSION_GROUP_NAME,
266       DESCRIPTION
267     ) = (select
268       B.DIMENSION_GROUP_NAME,
269       B.DESCRIPTION
270     from FEM_DIMENSION_GRPS_TL B
271     where B.DIMENSION_GROUP_ID = T.DIMENSION_GROUP_ID
272     and B.LANGUAGE = T.SOURCE_LANG)
273   where (
274       T.DIMENSION_GROUP_ID,
275       T.LANGUAGE
276   ) in (select
277       SUBT.DIMENSION_GROUP_ID,
278       SUBT.LANGUAGE
279     from FEM_DIMENSION_GRPS_TL SUBB, FEM_DIMENSION_GRPS_TL SUBT
280     where SUBB.DIMENSION_GROUP_ID = SUBT.DIMENSION_GROUP_ID
281     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
282     and (SUBB.DIMENSION_GROUP_NAME <> SUBT.DIMENSION_GROUP_NAME
283       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
284       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
285       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
286   ));
287 
288   insert into FEM_DIMENSION_GRPS_TL (
289     DIMENSION_GROUP_NAME,
290     DESCRIPTION,
291     CREATION_DATE,
292     CREATED_BY,
293     LAST_UPDATED_BY,
294     LAST_UPDATE_DATE,
295     LAST_UPDATE_LOGIN,
296     DIMENSION_ID,
297     DIMENSION_GROUP_ID,
298     LANGUAGE,
299     SOURCE_LANG
300   ) select /*+ ORDERED */
301     B.DIMENSION_GROUP_NAME,
302     B.DESCRIPTION,
303     B.CREATION_DATE,
304     B.CREATED_BY,
305     B.LAST_UPDATED_BY,
306     B.LAST_UPDATE_DATE,
307     B.LAST_UPDATE_LOGIN,
308     B.DIMENSION_ID,
309     B.DIMENSION_GROUP_ID,
310     L.LANGUAGE_CODE,
311     B.SOURCE_LANG
312   from FEM_DIMENSION_GRPS_TL B, FND_LANGUAGES L
313   where L.INSTALLED_FLAG in ('I', 'B')
314   and B.LANGUAGE = userenv('LANG')
315   and not exists
316     (select NULL
317     from FEM_DIMENSION_GRPS_TL T
318     where T.DIMENSION_GROUP_ID = B.DIMENSION_GROUP_ID
319     and T.LANGUAGE = L.LANGUAGE_CODE);
320 end ADD_LANGUAGE;
321 PROCEDURE TRANSLATE_ROW(
322         x_DIMENSION_GROUP_ID in number,
323         x_owner in varchar2,
324         x_last_update_date in varchar2,
325         x_DIMENSION_GROUP_NAME in varchar2,
326         x_description in varchar2,
327         x_custom_mode in varchar2) is
328 
329         owner_id number;
330         ludate date;
331         row_id varchar2(64);
332         f_luby    number;  -- entity owner in file
333         f_ludate  date;    -- entity update date in file
334         db_luby   number;  -- entity owner in db
335         db_ludate date;    -- entity update date in db
336     begin
337 
338 
339         -- Translate owner to file_last_updated_by
340         f_luby := fnd_load_util.owner_id(x_owner);
341 
342         -- Translate char last_update_date to date
343         f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
344         begin
345           select LAST_UPDATED_BY, LAST_UPDATE_DATE
346           into db_luby, db_ludate
347           from FEM_DIMENSION_GRPS_TL
348           where DIMENSION_GROUP_ID = x_DIMENSION_GROUP_ID
352           if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
349           and LANGUAGE = userenv('LANG');
350 
351 	  -- Test for customization and version
353                                         db_ludate, x_custom_mode)) then
354             -- Update translations for this language
355             update FEM_DIMENSION_GRPS_TL set
356               DIMENSION_GROUP_NAME = decode(x_DIMENSION_GROUP_NAME,
357 			       fnd_load_util.null_value, null, -- Real null
358 			       null, x_DIMENSION_GROUP_NAME,                  -- No change
359 			       x_DIMENSION_GROUP_NAME),
360               DESCRIPTION = nvl(x_description, DESCRIPTION),
361               LAST_UPDATE_DATE = f_ludate,
362               LAST_UPDATED_BY = f_luby,
363               LAST_UPDATE_LOGIN = 0,
364               SOURCE_LANG = userenv('LANG')
365             where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
366             and DIMENSION_GROUP_ID = x_DIMENSION_GROUP_ID;
367          end if;
368         exception
369           when no_data_found then
370             -- Do not insert missing translations, skip this row
371             null;
372         end;
373      end TRANSLATE_ROW;
374 
375 
376 end FEM_DIMENSION_GRPS_PKG;