[Home] [Help]
PACKAGE BODY: APPS.BNE_PARAM_GROUPS_PKG
Source
1 package body BNE_PARAM_GROUPS_PKG as
2 /* $Header: bnepargb.pls 120.2 2005/06/29 03:40:35 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_PARAM_LIST_CODE in VARCHAR2,
8 X_SEQUENCE_NUM in NUMBER,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_ATTRIBUTE_APP_ID in NUMBER,
11 X_ATTRIBUTE_CODE in VARCHAR2,
12 X_GROUP_RESOLVER in VARCHAR2,
13 X_USER_NAME in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from BNE_PARAM_GROUPS_B
21 where APPLICATION_ID = X_APPLICATION_ID
22 and PARAM_LIST_CODE = X_PARAM_LIST_CODE
23 and SEQUENCE_NUM = X_SEQUENCE_NUM
24 ;
25 begin
26 insert into BNE_PARAM_GROUPS_B (
27 APPLICATION_ID,
28 PARAM_LIST_CODE,
29 SEQUENCE_NUM,
30 OBJECT_VERSION_NUMBER,
31 ATTRIBUTE_APP_ID,
32 ATTRIBUTE_CODE,
33 GROUP_RESOLVER,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN
39 ) values (
40 X_APPLICATION_ID,
41 X_PARAM_LIST_CODE,
42 X_SEQUENCE_NUM,
43 X_OBJECT_VERSION_NUMBER,
44 X_ATTRIBUTE_APP_ID,
45 X_ATTRIBUTE_CODE,
46 X_GROUP_RESOLVER,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN
52 );
53
54 insert into BNE_PARAM_GROUPS_TL (
55 APPLICATION_ID,
56 PARAM_LIST_CODE,
57 SEQUENCE_NUM,
58 USER_NAME,
59 CREATED_BY,
60 CREATION_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATE_LOGIN,
64 LANGUAGE,
65 SOURCE_LANG
66 ) select
67 X_APPLICATION_ID,
68 X_PARAM_LIST_CODE,
69 X_SEQUENCE_NUM,
70 X_USER_NAME,
71 X_CREATED_BY,
72 X_CREATION_DATE,
73 X_LAST_UPDATED_BY,
74 X_LAST_UPDATE_DATE,
75 X_LAST_UPDATE_LOGIN,
76 L.LANGUAGE_CODE,
77 userenv('LANG')
78 from FND_LANGUAGES L
79 where L.INSTALLED_FLAG in ('I', 'B')
80 and not exists
81 (select NULL
82 from BNE_PARAM_GROUPS_TL T
83 where T.APPLICATION_ID = X_APPLICATION_ID
84 and T.PARAM_LIST_CODE = X_PARAM_LIST_CODE
85 and T.SEQUENCE_NUM = X_SEQUENCE_NUM
86 and T.LANGUAGE = L.LANGUAGE_CODE);
87
88 open c;
89 fetch c into X_ROWID;
90 if (c%notfound) then
91 close c;
92 raise no_data_found;
93 end if;
94 close c;
95
96 end INSERT_ROW;
97
98 procedure LOCK_ROW (
99 X_APPLICATION_ID in NUMBER,
100 X_PARAM_LIST_CODE in VARCHAR2,
101 X_SEQUENCE_NUM in NUMBER,
102 X_OBJECT_VERSION_NUMBER in NUMBER,
103 X_ATTRIBUTE_APP_ID in NUMBER,
104 X_ATTRIBUTE_CODE in VARCHAR2,
105 X_GROUP_RESOLVER in VARCHAR2,
106 X_USER_NAME in VARCHAR2
107 ) is
108 cursor c is select
109 OBJECT_VERSION_NUMBER,
110 ATTRIBUTE_APP_ID,
111 ATTRIBUTE_CODE,
112 GROUP_RESOLVER
113 from BNE_PARAM_GROUPS_B
114 where APPLICATION_ID = X_APPLICATION_ID
115 and PARAM_LIST_CODE = X_PARAM_LIST_CODE
116 and SEQUENCE_NUM = X_SEQUENCE_NUM
117 for update of APPLICATION_ID nowait;
118 recinfo c%rowtype;
119
120 cursor c1 is select
121 USER_NAME,
122 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
123 from BNE_PARAM_GROUPS_TL
124 where APPLICATION_ID = X_APPLICATION_ID
125 and PARAM_LIST_CODE = X_PARAM_LIST_CODE
126 and SEQUENCE_NUM = X_SEQUENCE_NUM
127 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
128 for update of APPLICATION_ID nowait;
129 begin
130 open c;
131 fetch c into recinfo;
132 if (c%notfound) then
133 close c;
134 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
135 app_exception.raise_exception;
136 end if;
137 close c;
138 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
139 AND ((recinfo.ATTRIBUTE_APP_ID = X_ATTRIBUTE_APP_ID)
140 OR ((recinfo.ATTRIBUTE_APP_ID is null) AND (X_ATTRIBUTE_APP_ID is null)))
141 AND ((recinfo.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE)
142 OR ((recinfo.ATTRIBUTE_CODE is null) AND (X_ATTRIBUTE_CODE is null)))
143 AND ((recinfo.GROUP_RESOLVER = X_GROUP_RESOLVER)
144 OR ((recinfo.GROUP_RESOLVER is null) AND (X_GROUP_RESOLVER is null)))
145 ) then
146 null;
147 else
148 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
149 app_exception.raise_exception;
150 end if;
151
152 for tlinfo in c1 loop
153 if (tlinfo.BASELANG = 'Y') then
154 if ( (tlinfo.USER_NAME = X_USER_NAME)
155 ) then
156 null;
157 else
158 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
159 app_exception.raise_exception;
160 end if;
161 end if;
162 end loop;
163 return;
164 end LOCK_ROW;
165
166 procedure UPDATE_ROW (
167 X_APPLICATION_ID in NUMBER,
168 X_PARAM_LIST_CODE in VARCHAR2,
169 X_SEQUENCE_NUM in NUMBER,
170 X_OBJECT_VERSION_NUMBER in NUMBER,
171 X_ATTRIBUTE_APP_ID in NUMBER,
172 X_ATTRIBUTE_CODE in VARCHAR2,
173 X_GROUP_RESOLVER in VARCHAR2,
174 X_USER_NAME in VARCHAR2,
175 X_LAST_UPDATE_DATE in DATE,
176 X_LAST_UPDATED_BY in NUMBER,
177 X_LAST_UPDATE_LOGIN in NUMBER
178 ) is
179 begin
180 update BNE_PARAM_GROUPS_B set
181 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
182 ATTRIBUTE_APP_ID = X_ATTRIBUTE_APP_ID,
183 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE,
184 GROUP_RESOLVER = X_GROUP_RESOLVER,
185 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
186 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
187 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
188 where APPLICATION_ID = X_APPLICATION_ID
189 and PARAM_LIST_CODE = X_PARAM_LIST_CODE
190 and SEQUENCE_NUM = X_SEQUENCE_NUM;
191
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195
196 update BNE_PARAM_GROUPS_TL set
197 USER_NAME = X_USER_NAME,
198 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
199 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
200 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
201 SOURCE_LANG = userenv('LANG')
202 where APPLICATION_ID = X_APPLICATION_ID
203 and PARAM_LIST_CODE = X_PARAM_LIST_CODE
204 and SEQUENCE_NUM = X_SEQUENCE_NUM
205 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210 end UPDATE_ROW;
211
212 procedure DELETE_ROW (
213 X_APPLICATION_ID in NUMBER,
214 X_PARAM_LIST_CODE in VARCHAR2,
215 X_SEQUENCE_NUM in NUMBER
216 ) is
217 begin
218 delete from BNE_PARAM_GROUPS_TL
219 where APPLICATION_ID = X_APPLICATION_ID
220 and PARAM_LIST_CODE = X_PARAM_LIST_CODE
221 and SEQUENCE_NUM = X_SEQUENCE_NUM;
222
223 if (sql%notfound) then
224 raise no_data_found;
225 end if;
226
227 delete from BNE_PARAM_GROUPS_B
228 where APPLICATION_ID = X_APPLICATION_ID
229 and PARAM_LIST_CODE = X_PARAM_LIST_CODE
230 and SEQUENCE_NUM = X_SEQUENCE_NUM;
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 BNE_PARAM_GROUPS_TL T
241 where not exists
242 (select NULL
243 from BNE_PARAM_GROUPS_B B
244 where B.APPLICATION_ID = T.APPLICATION_ID
245 and B.PARAM_LIST_CODE = T.PARAM_LIST_CODE
246 and B.SEQUENCE_NUM = T.SEQUENCE_NUM
247 );
248
249 update BNE_PARAM_GROUPS_TL T set (
250 USER_NAME
251 ) = (select
252 B.USER_NAME
253 from BNE_PARAM_GROUPS_TL B
254 where B.APPLICATION_ID = T.APPLICATION_ID
255 and B.PARAM_LIST_CODE = T.PARAM_LIST_CODE
256 and B.SEQUENCE_NUM = T.SEQUENCE_NUM
257 and B.LANGUAGE = T.SOURCE_LANG)
258 where (
259 T.APPLICATION_ID,
260 T.PARAM_LIST_CODE,
261 T.SEQUENCE_NUM,
262 T.LANGUAGE
263 ) in (select
264 SUBT.APPLICATION_ID,
265 SUBT.PARAM_LIST_CODE,
266 SUBT.SEQUENCE_NUM,
267 SUBT.LANGUAGE
268 from BNE_PARAM_GROUPS_TL SUBB, BNE_PARAM_GROUPS_TL SUBT
269 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
270 and SUBB.PARAM_LIST_CODE = SUBT.PARAM_LIST_CODE
271 and SUBB.SEQUENCE_NUM = SUBT.SEQUENCE_NUM
272 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
273 and (SUBB.USER_NAME <> SUBT.USER_NAME
274 ));
275
276 insert into BNE_PARAM_GROUPS_TL (
277 APPLICATION_ID,
278 PARAM_LIST_CODE,
279 SEQUENCE_NUM,
280 USER_NAME,
281 CREATED_BY,
282 CREATION_DATE,
283 LAST_UPDATED_BY,
284 LAST_UPDATE_DATE,
285 LAST_UPDATE_LOGIN,
286 LANGUAGE,
287 SOURCE_LANG
288 ) select /*+ ORDERED */
289 B.APPLICATION_ID,
290 B.PARAM_LIST_CODE,
291 B.SEQUENCE_NUM,
292 B.USER_NAME,
293 B.CREATED_BY,
294 B.CREATION_DATE,
295 B.LAST_UPDATED_BY,
296 B.LAST_UPDATE_DATE,
297 B.LAST_UPDATE_LOGIN,
298 L.LANGUAGE_CODE,
299 B.SOURCE_LANG
300 from BNE_PARAM_GROUPS_TL B, FND_LANGUAGES L
301 where L.INSTALLED_FLAG in ('I', 'B')
302 and B.LANGUAGE = userenv('LANG')
303 and not exists
304 (select NULL
305 from BNE_PARAM_GROUPS_TL T
306 where T.APPLICATION_ID = B.APPLICATION_ID
307 and T.PARAM_LIST_CODE = B.PARAM_LIST_CODE
308 and T.SEQUENCE_NUM = B.SEQUENCE_NUM
309 and T.LANGUAGE = L.LANGUAGE_CODE);
310 end ADD_LANGUAGE;
311
312 --------------------------------------------------------------------------------
313 -- PROCEDURE: TRANSLATE_ROW --
314 -- --
315 -- DESCRIPTION: Load a translation into the BNE_PARAM_GROUPS entity. --
316 -- This proc is called from the apps loader. --
317 -- --
318 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
319 -- --
320 -- --
321 -- MODIFICATION HISTORY --
322 -- Date Username Description --
323 -- 21-Apr-04 DGROVES CREATED --
324 --------------------------------------------------------------------------------
325 procedure TRANSLATE_ROW(
326 x_param_list_asn IN VARCHAR2,
327 x_param_list_code IN VARCHAR2,
328 x_sequence_num IN VARCHAR2,
329 x_user_name IN VARCHAR2,
330 x_owner IN VARCHAR2,
331 x_last_update_date IN VARCHAR2,
332 x_custom_mode IN VARCHAR2
333 )
334 is
335 l_app_id number;
336 f_luby number; -- entity owner in file
337 f_ludate date; -- entity update date in file
338 db_luby number; -- entity owner in db
339 db_ludate date; -- entity update date in db
340 begin
341 -- translate values to IDs
342 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_param_list_asn);
343
344 -- Translate owner to file_last_updated_by
345 f_luby := fnd_load_util.owner_id(x_owner);
346
347 -- Translate char last_update_date to date
348 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
349 begin
350 select LAST_UPDATED_BY, LAST_UPDATE_DATE
351 into db_luby, db_ludate
352 from BNE_PARAM_GROUPS_TL
353 where APPLICATION_ID = l_app_id
354 and PARAM_LIST_CODE = x_param_list_code
355 and SEQUENCE_NUM = x_sequence_num
356 and LANGUAGE = userenv('LANG');
357
358 -- Test for customization and version
359 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
360 db_ludate, x_custom_mode)) then
361
362 update BNE_PARAM_GROUPS_TL
363 set USER_NAME = x_user_name,
364 LAST_UPDATE_DATE = f_ludate,
365 LAST_UPDATED_BY = f_luby,
366 LAST_UPDATE_LOGIN = 0,
367 SOURCE_LANG = userenv('LANG')
368 where APPLICATION_ID = l_app_id
369 AND PARAM_LIST_CODE = x_param_list_code
370 and SEQUENCE_NUM = x_sequence_num
371 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
372 ;
373 end if;
374 exception
375 when no_data_found then
376 -- Do not insert missing translations, skip this row
377 null;
378 end;
379 end TRANSLATE_ROW;
380
381
382
383 --------------------------------------------------------------------------------
384 -- PROCEDURE: LOAD_ROW --
385 -- --
386 -- DESCRIPTION: Load a row into the BNE_PARAM_GROUPS entity. --
387 -- This proc is called from the apps loader. --
388 -- --
389 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
390 -- --
391 -- --
392 -- MODIFICATION HISTORY --
393 -- Date Username Description --
394 -- 21-Apr-04 DGROVES CREATED --
395 --------------------------------------------------------------------------------
396 procedure LOAD_ROW(
397 x_param_list_asn IN VARCHAR2,
398 x_param_list_code IN VARCHAR2,
399 x_sequence_num IN VARCHAR2,
400 x_object_version_number IN VARCHAR2,
401 x_attribute_asn IN VARCHAR2,
402 x_attribute_code IN VARCHAR2,
403 x_group_resolver IN VARCHAR2,
404 x_user_name IN VARCHAR2,
405 x_owner IN VARCHAR2,
406 x_last_update_date IN VARCHAR2,
407 x_custom_mode IN VARCHAR2
408 )
409 is
410 l_app_id number;
411 l_attrib_app_id number;
412 l_row_id varchar2(64);
413 f_luby number; -- entity owner in file
414 f_ludate date; -- entity update date in file
415 db_luby number; -- entity owner in db
416 db_ludate date; -- entity update date in db
417 begin
418 -- translate values to IDs
419 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_param_list_asn);
420 l_attrib_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_attribute_asn);
421
422 -- Translate owner to file_last_updated_by
423 f_luby := fnd_load_util.owner_id(x_owner);
424
425 -- Translate char last_update_date to date
426 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
427 begin
428 select LAST_UPDATED_BY, LAST_UPDATE_DATE
429 into db_luby, db_ludate
430 from BNE_PARAM_GROUPS_B
431 where APPLICATION_ID = l_app_id
432 and PARAM_LIST_CODE = x_param_list_code
433 and SEQUENCE_NUM = x_sequence_num;
434
438 -- Update existing row
435 -- Test for customization and version
436 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
437 db_ludate, x_custom_mode)) then
439 BNE_PARAM_GROUPS_PKG.Update_Row(
440 X_APPLICATION_ID => l_app_id,
441 X_PARAM_LIST_CODE => x_param_list_code,
442 X_SEQUENCE_NUM => x_sequence_num,
443 X_OBJECT_VERSION_NUMBER => x_object_version_number,
444 X_ATTRIBUTE_APP_ID => l_attrib_app_id,
445 X_ATTRIBUTE_CODE => x_attribute_code,
446 X_GROUP_RESOLVER => x_group_resolver,
447 X_USER_NAME => x_user_name,
448 X_LAST_UPDATE_DATE => f_ludate,
449 X_LAST_UPDATED_BY => f_luby,
450 X_LAST_UPDATE_LOGIN => 0
451 );
452 end if;
453 exception
454 when no_data_found then
455 -- Record doesn't exist - insert in all cases
456 BNE_PARAM_GROUPS_PKG.Insert_Row(
457 X_ROWID => l_row_id,
458 X_APPLICATION_ID => l_app_id,
459 X_PARAM_LIST_CODE => x_param_list_code,
460 X_SEQUENCE_NUM => x_sequence_num,
461 X_OBJECT_VERSION_NUMBER => x_object_version_number,
462 X_ATTRIBUTE_APP_ID => l_attrib_app_id,
463 X_ATTRIBUTE_CODE => x_attribute_code,
464 X_GROUP_RESOLVER => x_group_resolver,
465 X_USER_NAME => x_user_name,
466 X_CREATION_DATE => f_ludate,
467 X_CREATED_BY => f_luby,
468 X_LAST_UPDATE_DATE => f_ludate,
469 X_LAST_UPDATED_BY => f_luby,
470 X_LAST_UPDATE_LOGIN => 0
471 );
472 end;
473 end LOAD_ROW;
474
475 end BNE_PARAM_GROUPS_PKG;