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