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_DIM_ATTRIBUTES_PKG

Source


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