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