[Home] [Help]
PACKAGE BODY: APPS.BNE_PARAM_DEFNS_PKG
Source
1 package body BNE_PARAM_DEFNS_PKG as
2 /* $Header: bnepardb.pls 120.3 2005/08/18 06:36:53 dagroves noship $ */
3
4 /* ============================================================*/
5 /* WARNING WARNING WARNING WARNING WARNING WARNING WARNING */
6 /* This is not the default package body generated by tltblgen. */
7 /* X_DEFAULT_STRING_TRANS_FLAG has been added. */
8 /* ============================================================*/
9
10 procedure INSERT_ROW (
11 X_ROWID in out NOCOPY VARCHAR2,
12 X_APPLICATION_ID in NUMBER,
13 X_PARAM_DEFN_CODE in VARCHAR2,
14 X_OBJECT_VERSION_NUMBER in NUMBER,
15 X_PARAM_NAME in VARCHAR2,
16 X_PARAM_SOURCE in VARCHAR2,
17 X_PARAM_CATEGORY in NUMBER,
18 X_DATATYPE in NUMBER,
19 X_ATTRIBUTE_APP_ID in NUMBER,
20 X_ATTRIBUTE_CODE in VARCHAR2,
21 X_PARAM_RESOLVER in VARCHAR2,
22 X_DEFAULT_REQUIRED_FLAG in VARCHAR2,
23 X_DEFAULT_VISIBLE_FLAG in VARCHAR2,
24 X_DEFAULT_USER_MODIFYABLE_FLAG in VARCHAR2,
25 X_DEFAULT_DATE in DATE,
26 X_DEFAULT_NUMBER in NUMBER,
27 X_DEFAULT_BOOLEAN_FLAG in VARCHAR2,
28 X_DEFAULT_FORMULA in VARCHAR2,
29 X_VAL_TYPE in VARCHAR2,
30 X_VAL_VALUE in VARCHAR2,
31 X_MAX_SIZE in NUMBER,
32 X_DISPLAY_TYPE in NUMBER,
33 X_DISPLAY_STYLE in NUMBER,
34 X_DISPLAY_SIZE in NUMBER,
35 X_HELP_URL in VARCHAR2,
36 X_FORMAT_MASK in VARCHAR2,
37 X_USER_NAME in VARCHAR2,
38 X_DEFAULT_STRING in VARCHAR2,
39 X_DEFAULT_STRING_TRANS_FLAG in VARCHAR2,
40 X_DEFAULT_DESC in VARCHAR2,
41 X_PROMPT_LEFT in VARCHAR2,
42 X_PROMPT_ABOVE in VARCHAR2,
43 X_USER_TIP in VARCHAR2,
44 X_ACCESS_KEY in VARCHAR2,
45 X_CREATION_DATE in DATE,
46 X_CREATED_BY in NUMBER,
47 X_LAST_UPDATE_DATE in DATE,
48 X_LAST_UPDATED_BY in NUMBER,
49 X_LAST_UPDATE_LOGIN in NUMBER,
50 X_OA_FLEX_APPLICATION_ID in NUMBER,
51 X_OA_FLEX_CODE in VARCHAR2,
52 X_OA_FLEX_NUM in VARCHAR2
53 ) is
54 cursor C is select ROWID from BNE_PARAM_DEFNS_B
55 where APPLICATION_ID = X_APPLICATION_ID
56 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
57 ;
58 l_base_default_string bne_param_defns_b.default_string%type;
59 l_trans_default_string bne_param_defns_tl.default_string%type;
60 begin
61 if X_DEFAULT_STRING_TRANS_FLAG = 'Y'
62 then
63 l_base_default_string := null;
64 l_trans_default_string := X_DEFAULT_STRING;
65 else
66 l_base_default_string := X_DEFAULT_STRING;
67 l_trans_default_string := null;
68 end if;
69
70 insert into BNE_PARAM_DEFNS_B (
71 APPLICATION_ID,
72 PARAM_DEFN_CODE,
73 OBJECT_VERSION_NUMBER,
74 PARAM_NAME,
75 PARAM_SOURCE,
76 PARAM_CATEGORY,
77 DATATYPE,
78 ATTRIBUTE_APP_ID,
79 ATTRIBUTE_CODE,
80 PARAM_RESOLVER,
81 DEFAULT_REQUIRED_FLAG,
82 DEFAULT_VISIBLE_FLAG,
83 DEFAULT_USER_MODIFYABLE_FLAG,
84 DEFAULT_DATE,
85 DEFAULT_NUMBER,
86 DEFAULT_BOOLEAN_FLAG,
87 DEFAULT_FORMULA,
88 DEFAULT_STRING,
89 VAL_TYPE,
90 VAL_VALUE,
91 MAX_SIZE,
92 DISPLAY_TYPE,
93 DISPLAY_STYLE,
94 DISPLAY_SIZE,
95 HELP_URL,
96 FORMAT_MASK,
97 CREATION_DATE,
98 CREATED_BY,
99 LAST_UPDATE_DATE,
100 LAST_UPDATED_BY,
101 LAST_UPDATE_LOGIN,
102 OA_FLEX_APPLICATION_ID,
103 OA_FLEX_CODE,
104 OA_FLEX_NUM
105 ) values (
106 X_APPLICATION_ID,
107 X_PARAM_DEFN_CODE,
108 X_OBJECT_VERSION_NUMBER,
109 X_PARAM_NAME,
110 X_PARAM_SOURCE,
111 X_PARAM_CATEGORY,
112 X_DATATYPE,
113 X_ATTRIBUTE_APP_ID,
114 X_ATTRIBUTE_CODE,
115 X_PARAM_RESOLVER,
116 X_DEFAULT_REQUIRED_FLAG,
117 X_DEFAULT_VISIBLE_FLAG,
118 X_DEFAULT_USER_MODIFYABLE_FLAG,
119 X_DEFAULT_DATE,
120 X_DEFAULT_NUMBER,
121 X_DEFAULT_BOOLEAN_FLAG,
122 X_DEFAULT_FORMULA,
123 l_base_default_string,
124 X_VAL_TYPE,
125 X_VAL_VALUE,
126 X_MAX_SIZE,
127 X_DISPLAY_TYPE,
128 X_DISPLAY_STYLE,
129 X_DISPLAY_SIZE,
130 X_HELP_URL,
131 X_FORMAT_MASK,
132 X_CREATION_DATE,
133 X_CREATED_BY,
134 X_LAST_UPDATE_DATE,
135 X_LAST_UPDATED_BY,
136 X_LAST_UPDATE_LOGIN,
137 X_OA_FLEX_APPLICATION_ID,
138 X_OA_FLEX_CODE,
139 X_OA_FLEX_NUM
140 );
141
142 insert into BNE_PARAM_DEFNS_TL (
143 USER_NAME,
144 APPLICATION_ID,
145 PARAM_DEFN_CODE,
146 DEFAULT_STRING,
147 DEFAULT_DESC,
148 PROMPT_LEFT,
149 PROMPT_ABOVE,
150 USER_TIP,
151 ACCESS_KEY,
152 CREATED_BY,
153 CREATION_DATE,
154 LAST_UPDATED_BY,
155 LAST_UPDATE_DATE,
156 LAST_UPDATE_LOGIN,
157 LANGUAGE,
158 SOURCE_LANG
159 ) select
160 X_USER_NAME,
161 X_APPLICATION_ID,
162 X_PARAM_DEFN_CODE,
163 l_trans_default_string,
164 X_DEFAULT_DESC,
165 X_PROMPT_LEFT,
166 X_PROMPT_ABOVE,
167 X_USER_TIP,
168 X_ACCESS_KEY,
169 X_CREATED_BY,
170 X_CREATION_DATE,
171 X_LAST_UPDATED_BY,
172 X_LAST_UPDATE_DATE,
173 X_LAST_UPDATE_LOGIN,
174 L.LANGUAGE_CODE,
175 userenv('LANG')
176 from FND_LANGUAGES L
177 where L.INSTALLED_FLAG in ('I', 'B')
178 and not exists
179 (select NULL
180 from BNE_PARAM_DEFNS_TL T
181 where T.APPLICATION_ID = X_APPLICATION_ID
182 and T.PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
183 and T.LANGUAGE = L.LANGUAGE_CODE);
184
185 open c;
186 fetch c into X_ROWID;
187 if (c%notfound) then
188 close c;
189 raise no_data_found;
190 end if;
191 close c;
192
193 end INSERT_ROW;
194
195 procedure LOCK_ROW (
196 X_APPLICATION_ID in NUMBER,
197 X_PARAM_DEFN_CODE in VARCHAR2,
198 X_OBJECT_VERSION_NUMBER in NUMBER,
199 X_PARAM_NAME in VARCHAR2,
200 X_PARAM_SOURCE in VARCHAR2,
201 X_PARAM_CATEGORY in NUMBER,
202 X_DATATYPE in NUMBER,
203 X_ATTRIBUTE_APP_ID in NUMBER,
204 X_ATTRIBUTE_CODE in VARCHAR2,
205 X_PARAM_RESOLVER in VARCHAR2,
206 X_DEFAULT_REQUIRED_FLAG in VARCHAR2,
207 X_DEFAULT_VISIBLE_FLAG in VARCHAR2,
208 X_DEFAULT_USER_MODIFYABLE_FLAG in VARCHAR2,
209 X_DEFAULT_DATE in DATE,
210 X_DEFAULT_NUMBER in NUMBER,
211 X_DEFAULT_BOOLEAN_FLAG in VARCHAR2,
212 X_DEFAULT_FORMULA in VARCHAR2,
213 X_VAL_TYPE in VARCHAR2,
214 X_VAL_VALUE in VARCHAR2,
215 X_MAX_SIZE in NUMBER,
216 X_DISPLAY_TYPE in NUMBER,
217 X_DISPLAY_STYLE in NUMBER,
218 X_DISPLAY_SIZE in NUMBER,
219 X_HELP_URL in VARCHAR2,
220 X_FORMAT_MASK in VARCHAR2,
221 X_USER_NAME in VARCHAR2,
222 X_DEFAULT_STRING in VARCHAR2,
223 X_DEFAULT_STRING_TRANS_FLAG in VARCHAR2,
224 X_DEFAULT_DESC in VARCHAR2,
225 X_PROMPT_LEFT in VARCHAR2,
226 X_PROMPT_ABOVE in VARCHAR2,
227 X_USER_TIP in VARCHAR2,
228 X_ACCESS_KEY in VARCHAR2,
229 X_OA_FLEX_APPLICATION_ID in NUMBER,
230 X_OA_FLEX_CODE in VARCHAR2,
231 X_OA_FLEX_NUM in VARCHAR2
232 ) is
233 cursor c is select
234 OBJECT_VERSION_NUMBER,
235 PARAM_NAME,
236 PARAM_SOURCE,
237 PARAM_CATEGORY,
238 DATATYPE,
239 ATTRIBUTE_APP_ID,
240 ATTRIBUTE_CODE,
241 PARAM_RESOLVER,
242 DEFAULT_REQUIRED_FLAG,
243 DEFAULT_VISIBLE_FLAG,
244 DEFAULT_USER_MODIFYABLE_FLAG,
245 DEFAULT_DATE,
246 DEFAULT_NUMBER,
247 DEFAULT_BOOLEAN_FLAG,
248 DEFAULT_FORMULA,
249 VAL_TYPE,
250 VAL_VALUE,
251 MAX_SIZE,
252 DISPLAY_TYPE,
253 DISPLAY_STYLE,
254 DISPLAY_SIZE,
255 HELP_URL,
256 FORMAT_MASK,
257 OA_FLEX_APPLICATION_ID,
258 OA_FLEX_CODE,
259 OA_FLEX_NUM
260 from BNE_PARAM_DEFNS_B
261 where APPLICATION_ID = X_APPLICATION_ID
262 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
263 for update of APPLICATION_ID nowait;
264 recinfo c%rowtype;
265
266 cursor c1 is select
267 USER_NAME,
268 DEFAULT_STRING,
269 DEFAULT_DESC,
270 PROMPT_LEFT,
271 PROMPT_ABOVE,
272 USER_TIP,
273 ACCESS_KEY,
274 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
275 from BNE_PARAM_DEFNS_TL
276 where APPLICATION_ID = X_APPLICATION_ID
277 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
278 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
279 for update of APPLICATION_ID nowait;
280 begin
281 open c;
282 fetch c into recinfo;
283 if (c%notfound) then
284 close c;
285 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
286 app_exception.raise_exception;
287 end if;
288 close c;
289 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
290 AND (recinfo.PARAM_NAME = X_PARAM_NAME)
291 AND (recinfo.PARAM_SOURCE = X_PARAM_SOURCE)
292 AND (recinfo.PARAM_CATEGORY = X_PARAM_CATEGORY)
293 AND (recinfo.DATATYPE = X_DATATYPE)
294 AND ((recinfo.ATTRIBUTE_APP_ID = X_ATTRIBUTE_APP_ID)
295 OR ((recinfo.ATTRIBUTE_APP_ID is null) AND (X_ATTRIBUTE_APP_ID is null)))
296 AND ((recinfo.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE)
297 OR ((recinfo.ATTRIBUTE_CODE is null) AND (X_ATTRIBUTE_CODE is null)))
298 AND ((recinfo.PARAM_RESOLVER = X_PARAM_RESOLVER)
299 OR ((recinfo.PARAM_RESOLVER is null) AND (X_PARAM_RESOLVER is null)))
300 AND (recinfo.DEFAULT_REQUIRED_FLAG = X_DEFAULT_REQUIRED_FLAG)
301 AND (recinfo.DEFAULT_VISIBLE_FLAG = X_DEFAULT_VISIBLE_FLAG)
302 AND (recinfo.DEFAULT_USER_MODIFYABLE_FLAG = X_DEFAULT_USER_MODIFYABLE_FLAG)
303 AND ((recinfo.DEFAULT_DATE = X_DEFAULT_DATE)
304 OR ((recinfo.DEFAULT_DATE is null) AND (X_DEFAULT_DATE is null)))
305 AND ((recinfo.DEFAULT_NUMBER = X_DEFAULT_NUMBER)
306 OR ((recinfo.DEFAULT_NUMBER is null) AND (X_DEFAULT_NUMBER is null)))
307 AND ((recinfo.DEFAULT_BOOLEAN_FLAG = X_DEFAULT_BOOLEAN_FLAG)
308 OR ((recinfo.DEFAULT_BOOLEAN_FLAG is null) AND (X_DEFAULT_BOOLEAN_FLAG is null)))
309 AND ((recinfo.DEFAULT_FORMULA = X_DEFAULT_FORMULA)
310 OR ((recinfo.DEFAULT_FORMULA is null) AND (X_DEFAULT_FORMULA is null)))
311 AND (recinfo.VAL_TYPE = X_VAL_TYPE)
312 AND ((recinfo.VAL_VALUE = X_VAL_VALUE)
313 OR ((recinfo.VAL_VALUE is null) AND (X_VAL_VALUE is null)))
314 AND (recinfo.MAX_SIZE = X_MAX_SIZE)
315 AND (recinfo.DISPLAY_TYPE = X_DISPLAY_TYPE)
316 AND (recinfo.DISPLAY_STYLE = X_DISPLAY_STYLE)
317 AND (recinfo.DISPLAY_SIZE = X_DISPLAY_SIZE)
318 AND ((recinfo.HELP_URL = X_HELP_URL)
319 OR ((recinfo.HELP_URL is null) AND (X_HELP_URL is null)))
320 AND ((recinfo.FORMAT_MASK = X_FORMAT_MASK)
321 OR ((recinfo.FORMAT_MASK is null) AND (X_FORMAT_MASK is null)))
322 AND ((recinfo.OA_FLEX_APPLICATION_ID = X_OA_FLEX_APPLICATION_ID)
323 OR ((recinfo.OA_FLEX_APPLICATION_ID is null) AND (X_OA_FLEX_APPLICATION_ID is null)))
324 AND ((recinfo.OA_FLEX_CODE = X_OA_FLEX_CODE)
325 OR ((recinfo.OA_FLEX_CODE is null) AND (X_OA_FLEX_CODE is null)))
326 AND ((recinfo.OA_FLEX_NUM = X_OA_FLEX_NUM)
327 OR ((recinfo.OA_FLEX_NUM is null) AND (X_OA_FLEX_NUM is null)))
328 ) then
329 null;
330 else
331 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
332 app_exception.raise_exception;
333 end if;
334
335 for tlinfo in c1 loop
336 if (tlinfo.BASELANG = 'Y') then
337 if ( (tlinfo.USER_NAME = X_USER_NAME)
338 AND ((tlinfo.DEFAULT_STRING = X_DEFAULT_STRING)
339 OR ((tlinfo.DEFAULT_STRING is null) AND (X_DEFAULT_STRING is null)))
340 AND ((tlinfo.DEFAULT_DESC = X_DEFAULT_DESC)
341 OR ((tlinfo.DEFAULT_DESC is null) AND (X_DEFAULT_DESC is null)))
342 AND ((tlinfo.PROMPT_LEFT = X_PROMPT_LEFT)
343 OR ((tlinfo.PROMPT_LEFT is null) AND (X_PROMPT_LEFT is null)))
344 AND ((tlinfo.PROMPT_ABOVE = X_PROMPT_ABOVE)
345 OR ((tlinfo.PROMPT_ABOVE is null) AND (X_PROMPT_ABOVE is null)))
346 AND ((tlinfo.USER_TIP = X_USER_TIP)
347 OR ((tlinfo.USER_TIP is null) AND (X_USER_TIP is null)))
348 AND ((tlinfo.ACCESS_KEY = X_ACCESS_KEY)
349 OR ((tlinfo.ACCESS_KEY is null) AND (X_ACCESS_KEY is null)))
350 ) then
351 null;
352 else
353 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
354 app_exception.raise_exception;
355 end if;
356 end if;
357 end loop;
358 return;
359 end LOCK_ROW;
360
361 procedure UPDATE_ROW (
362 X_APPLICATION_ID in NUMBER,
363 X_PARAM_DEFN_CODE in VARCHAR2,
364 X_OBJECT_VERSION_NUMBER in NUMBER,
365 X_PARAM_NAME in VARCHAR2,
366 X_PARAM_SOURCE in VARCHAR2,
367 X_PARAM_CATEGORY in NUMBER,
368 X_DATATYPE in NUMBER,
369 X_ATTRIBUTE_APP_ID in NUMBER,
370 X_ATTRIBUTE_CODE in VARCHAR2,
371 X_PARAM_RESOLVER in VARCHAR2,
372 X_DEFAULT_REQUIRED_FLAG in VARCHAR2,
373 X_DEFAULT_VISIBLE_FLAG in VARCHAR2,
374 X_DEFAULT_USER_MODIFYABLE_FLAG in VARCHAR2,
375 X_DEFAULT_DATE in DATE,
376 X_DEFAULT_NUMBER in NUMBER,
377 X_DEFAULT_BOOLEAN_FLAG in VARCHAR2,
378 X_DEFAULT_FORMULA in VARCHAR2,
379 X_VAL_TYPE in VARCHAR2,
380 X_VAL_VALUE in VARCHAR2,
381 X_MAX_SIZE in NUMBER,
382 X_DISPLAY_TYPE in NUMBER,
383 X_DISPLAY_STYLE in NUMBER,
384 X_DISPLAY_SIZE in NUMBER,
385 X_HELP_URL in VARCHAR2,
386 X_FORMAT_MASK in VARCHAR2,
387 X_USER_NAME in VARCHAR2,
388 X_DEFAULT_STRING in VARCHAR2,
389 X_DEFAULT_STRING_TRANS_FLAG in VARCHAR2,
390 X_DEFAULT_DESC in VARCHAR2,
391 X_PROMPT_LEFT in VARCHAR2,
392 X_PROMPT_ABOVE in VARCHAR2,
393 X_USER_TIP in VARCHAR2,
394 X_ACCESS_KEY in VARCHAR2,
395 X_LAST_UPDATE_DATE in DATE,
396 X_LAST_UPDATED_BY in NUMBER,
397 X_LAST_UPDATE_LOGIN in NUMBER,
398 X_OA_FLEX_APPLICATION_ID in NUMBER,
399 X_OA_FLEX_CODE in VARCHAR2,
400 X_OA_FLEX_NUM in VARCHAR2
401 ) is
402 l_base_default_string bne_param_defns_b.default_string%type;
403 l_trans_default_string bne_param_defns_tl.default_string%type;
404 begin
405 if X_DEFAULT_STRING_TRANS_FLAG = 'Y'
406 then
407 l_base_default_string := null;
408 l_trans_default_string := X_DEFAULT_STRING;
409 else
410 l_base_default_string := X_DEFAULT_STRING;
411 l_trans_default_string := null;
412 end if;
413
414 update BNE_PARAM_DEFNS_B set
415 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
416 PARAM_NAME = X_PARAM_NAME,
417 PARAM_SOURCE = X_PARAM_SOURCE,
418 PARAM_CATEGORY = X_PARAM_CATEGORY,
419 DATATYPE = X_DATATYPE,
420 ATTRIBUTE_APP_ID = X_ATTRIBUTE_APP_ID,
421 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE,
422 PARAM_RESOLVER = X_PARAM_RESOLVER,
423 DEFAULT_REQUIRED_FLAG = X_DEFAULT_REQUIRED_FLAG,
424 DEFAULT_VISIBLE_FLAG = X_DEFAULT_VISIBLE_FLAG,
425 DEFAULT_USER_MODIFYABLE_FLAG = X_DEFAULT_USER_MODIFYABLE_FLAG,
426 DEFAULT_DATE = X_DEFAULT_DATE,
427 DEFAULT_NUMBER = X_DEFAULT_NUMBER,
428 DEFAULT_BOOLEAN_FLAG = X_DEFAULT_BOOLEAN_FLAG,
429 DEFAULT_FORMULA = X_DEFAULT_FORMULA,
430 DEFAULT_STRING = l_base_default_string,
431 VAL_TYPE = X_VAL_TYPE,
432 VAL_VALUE = X_VAL_VALUE,
433 MAX_SIZE = X_MAX_SIZE,
434 DISPLAY_TYPE = X_DISPLAY_TYPE,
435 DISPLAY_STYLE = X_DISPLAY_STYLE,
436 DISPLAY_SIZE = X_DISPLAY_SIZE,
437 HELP_URL = X_HELP_URL,
438 FORMAT_MASK = X_FORMAT_MASK,
439 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
440 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
441 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
442 OA_FLEX_APPLICATION_ID = X_OA_FLEX_APPLICATION_ID,
443 OA_FLEX_CODE = X_OA_FLEX_CODE,
444 OA_FLEX_NUM = X_OA_FLEX_NUM
445 where APPLICATION_ID = X_APPLICATION_ID
446 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE;
447
448 if (sql%notfound) then
449 raise no_data_found;
450 end if;
451
452 update BNE_PARAM_DEFNS_TL set
453 USER_NAME = X_USER_NAME,
454 DEFAULT_STRING = l_trans_default_string,
455 DEFAULT_DESC = X_DEFAULT_DESC,
456 PROMPT_LEFT = X_PROMPT_LEFT,
457 PROMPT_ABOVE = X_PROMPT_ABOVE,
458 USER_TIP = X_USER_TIP,
459 ACCESS_KEY = X_ACCESS_KEY,
460 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
461 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
462 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
463 SOURCE_LANG = userenv('LANG')
464 where APPLICATION_ID = X_APPLICATION_ID
465 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
466 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
467
468 if (sql%notfound) then
469 raise no_data_found;
470 end if;
471 end UPDATE_ROW;
472
473 procedure DELETE_ROW (
474 X_APPLICATION_ID in NUMBER,
475 X_PARAM_DEFN_CODE in VARCHAR2
476 ) is
477 begin
478 delete from BNE_PARAM_DEFNS_TL
479 where APPLICATION_ID = X_APPLICATION_ID
480 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE;
481
482 if (sql%notfound) then
483 raise no_data_found;
484 end if;
485
486 delete from BNE_PARAM_DEFNS_B
487 where APPLICATION_ID = X_APPLICATION_ID
488 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE;
489
490 if (sql%notfound) then
491 raise no_data_found;
492 end if;
493 end DELETE_ROW;
494
495 procedure ADD_LANGUAGE
496 is
497 begin
498 delete from BNE_PARAM_DEFNS_TL T
499 where not exists
500 (select NULL
501 from BNE_PARAM_DEFNS_B B
502 where B.APPLICATION_ID = T.APPLICATION_ID
503 and B.PARAM_DEFN_CODE = T.PARAM_DEFN_CODE
504 );
505
506 update BNE_PARAM_DEFNS_TL T set (
507 USER_NAME,
508 DEFAULT_STRING,
509 DEFAULT_DESC,
510 PROMPT_LEFT,
511 PROMPT_ABOVE,
512 USER_TIP,
513 ACCESS_KEY
514 ) = (select
515 B.USER_NAME,
516 B.DEFAULT_STRING,
517 B.DEFAULT_DESC,
518 B.PROMPT_LEFT,
519 B.PROMPT_ABOVE,
520 B.USER_TIP,
521 B.ACCESS_KEY
522 from BNE_PARAM_DEFNS_TL B
523 where B.APPLICATION_ID = T.APPLICATION_ID
524 and B.PARAM_DEFN_CODE = T.PARAM_DEFN_CODE
525 and B.LANGUAGE = T.SOURCE_LANG)
526 where (
527 T.APPLICATION_ID,
528 T.PARAM_DEFN_CODE,
529 T.LANGUAGE
530 ) in (select
531 SUBT.APPLICATION_ID,
532 SUBT.PARAM_DEFN_CODE,
533 SUBT.LANGUAGE
534 from BNE_PARAM_DEFNS_TL SUBB, BNE_PARAM_DEFNS_TL SUBT
535 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
536 and SUBB.PARAM_DEFN_CODE = SUBT.PARAM_DEFN_CODE
537 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
538 and (SUBB.USER_NAME <> SUBT.USER_NAME
539 or SUBB.DEFAULT_STRING <> SUBT.DEFAULT_STRING
540 or (SUBB.DEFAULT_STRING is null and SUBT.DEFAULT_STRING is not null)
541 or (SUBB.DEFAULT_STRING is not null and SUBT.DEFAULT_STRING is null)
542 or SUBB.DEFAULT_DESC <> SUBT.DEFAULT_DESC
543 or (SUBB.DEFAULT_DESC is null and SUBT.DEFAULT_DESC is not null)
544 or (SUBB.DEFAULT_DESC is not null and SUBT.DEFAULT_DESC is null)
545 or SUBB.PROMPT_LEFT <> SUBT.PROMPT_LEFT
546 or (SUBB.PROMPT_LEFT is null and SUBT.PROMPT_LEFT is not null)
547 or (SUBB.PROMPT_LEFT is not null and SUBT.PROMPT_LEFT is null)
548 or SUBB.PROMPT_ABOVE <> SUBT.PROMPT_ABOVE
549 or (SUBB.PROMPT_ABOVE is null and SUBT.PROMPT_ABOVE is not null)
550 or (SUBB.PROMPT_ABOVE is not null and SUBT.PROMPT_ABOVE is null)
551 or SUBB.USER_TIP <> SUBT.USER_TIP
552 or (SUBB.USER_TIP is null and SUBT.USER_TIP is not null)
553 or (SUBB.USER_TIP is not null and SUBT.USER_TIP is null)
554 or SUBB.ACCESS_KEY <> SUBT.ACCESS_KEY
555 or (SUBB.ACCESS_KEY is null and SUBT.ACCESS_KEY is not null)
556 or (SUBB.ACCESS_KEY is not null and SUBT.ACCESS_KEY is null)
557 ));
558
559 insert into BNE_PARAM_DEFNS_TL (
560 USER_NAME,
561 APPLICATION_ID,
562 PARAM_DEFN_CODE,
563 DEFAULT_STRING,
564 DEFAULT_DESC,
565 PROMPT_LEFT,
566 PROMPT_ABOVE,
567 USER_TIP,
568 ACCESS_KEY,
569 CREATED_BY,
570 CREATION_DATE,
571 LAST_UPDATED_BY,
572 LAST_UPDATE_DATE,
573 LAST_UPDATE_LOGIN,
574 LANGUAGE,
575 SOURCE_LANG
576 ) select
577 B.USER_NAME,
578 B.APPLICATION_ID,
579 B.PARAM_DEFN_CODE,
580 B.DEFAULT_STRING,
581 B.DEFAULT_DESC,
582 B.PROMPT_LEFT,
583 B.PROMPT_ABOVE,
584 B.USER_TIP,
585 B.ACCESS_KEY,
586 B.CREATED_BY,
587 B.CREATION_DATE,
588 B.LAST_UPDATED_BY,
589 B.LAST_UPDATE_DATE,
590 B.LAST_UPDATE_LOGIN,
591 L.LANGUAGE_CODE,
592 B.SOURCE_LANG
593 from BNE_PARAM_DEFNS_TL B, FND_LANGUAGES L
594 where L.INSTALLED_FLAG in ('I', 'B')
595 and B.LANGUAGE = userenv('LANG')
596 and not exists
597 (select NULL
598 from BNE_PARAM_DEFNS_TL T
599 where T.APPLICATION_ID = B.APPLICATION_ID
600 and T.PARAM_DEFN_CODE = B.PARAM_DEFN_CODE
601 and T.LANGUAGE = L.LANGUAGE_CODE);
602 end ADD_LANGUAGE;
603
604 --------------------------------------------------------------------------------
605 -- PROCEDURE: TRANSLATE_ROW --
606 -- --
607 -- DESCRIPTION: Load a translation into the BNE_PARAM_DEFNS entity. --
608 -- This proc is called from the apps loader. --
609 -- --
610 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
611 -- --
612 -- --
613 -- MODIFICATION HISTORY --
614 -- Date Username Description --
615 -- 1-Oct-02 DGROVES CREATED --
616 --------------------------------------------------------------------------------
617 procedure TRANSLATE_ROW(
618 x_param_defn_asn in VARCHAR2,
619 x_param_defn_code in VARCHAR2,
620 x_default_string_trans in VARCHAR2,
621 x_user_name in VARCHAR2,
622 x_default_desc in VARCHAR2,
623 x_prompt_left in VARCHAR2,
624 x_prompt_above in VARCHAR2,
625 x_user_tip in VARCHAR2,
626 x_access_key in VARCHAR2,
627 x_owner in VARCHAR2,
628 x_last_update_date in VARCHAR2,
629 x_custom_mode in VARCHAR2
630 )
631 is
632 l_app_id number;
633 f_luby number; -- entity owner in file
634 f_ludate date; -- entity update date in file
635 db_luby number; -- entity owner in db
636 db_ludate date; -- entity update date in db
637 begin
638 -- translate values to IDs
639 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_param_defn_asn);
640
641 -- Translate owner to file_last_updated_by
642 f_luby := fnd_load_util.owner_id(x_owner);
643
644 -- Translate char last_update_date to date
645 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
646 begin
647 select LAST_UPDATED_BY, LAST_UPDATE_DATE
648 into db_luby, db_ludate
649 from BNE_PARAM_DEFNS_TL
650 where APPLICATION_ID = l_app_id
651 and PARAM_DEFN_CODE = x_param_defn_code
652 and LANGUAGE = userenv('LANG');
653
654 -- Test for customization and version
655 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
656 db_ludate, x_custom_mode)) then
657
658
659 update BNE_PARAM_DEFNS_TL
660 set USER_TIP = x_user_tip,
661 PROMPT_LEFT = x_prompt_left,
662 PROMPT_ABOVE = x_prompt_above,
663 USER_NAME = x_user_name,
664 DEFAULT_DESC = x_default_desc,
665 DEFAULT_STRING = x_default_string_trans,
666 ACCESS_KEY = x_access_key,
667 LAST_UPDATE_DATE = f_ludate,
668 LAST_UPDATED_BY = f_luby,
669 LAST_UPDATE_LOGIN = 0,
670 SOURCE_LANG = userenv('LANG')
671 where APPLICATION_ID = l_app_id
672 AND PARAM_DEFN_CODE = x_param_defn_code
673 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
674 ;
675 end if;
676 exception
677 when no_data_found then
678 -- Do not insert missing translations, skip this row
679 null;
680 end;
681
682 end TRANSLATE_ROW;
683
684
685 --------------------------------------------------------------------------------
686 -- PROCEDURE: LOAD_ROW --
687 -- --
688 -- DESCRIPTION: Load a row into the BNE_PARAM_DEFNS entity. --
689 -- This proc is called from the apps loader. --
690 -- --
691 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
692 -- --
693 -- --
694 -- MODIFICATION HISTORY --
695 -- Date Username Description --
696 -- 1-Oct-02 DGROVES CREATED --
697 --------------------------------------------------------------------------------
698
699 procedure LOAD_ROW(
700 x_param_defn_asn in VARCHAR2,
701 x_param_defn_code in VARCHAR2,
702 x_object_version_number in VARCHAR2,
703 x_param_name in VARCHAR2,
704 x_param_source in VARCHAR2,
705 x_param_category in VARCHAR2,
706 x_datatype in VARCHAR2,
707 x_attribute_asn in VARCHAR2,
708 x_attribute_code in VARCHAR2,
709 x_param_resolver in VARCHAR2,
710 x_default_required_flag in VARCHAR2,
711 x_default_visible_flag in VARCHAR2,
712 x_default_user_modifyable_flag in VARCHAR2,
713 x_default_string in VARCHAR2,
714 x_default_string_trans_flag in VARCHAR2,
715 x_default_date in VARCHAR2,
716 x_default_number in VARCHAR2,
717 x_default_boolean_flag in VARCHAR2,
718 x_default_formula in VARCHAR2,
719 x_val_type in VARCHAR2,
720 x_val_value in VARCHAR2,
721 x_max_size in VARCHAR2,
722 x_display_type in VARCHAR2,
723 x_display_style in VARCHAR2,
724 x_display_size in VARCHAR2,
725 x_help_url in VARCHAR2,
726 x_format_mask in VARCHAR2,
727 x_user_name in VARCHAR2,
728 x_default_desc in VARCHAR2,
729 x_prompt_left in VARCHAR2,
730 x_prompt_above in VARCHAR2,
731 x_user_tip in VARCHAR2,
732 x_access_key in VARCHAR2,
733 x_owner in VARCHAR2,
734 x_last_update_date in VARCHAR2,
735 x_custom_mode in VARCHAR2,
736 x_oa_flex_asn in VARCHAR2,
737 x_oa_flex_code in VARCHAR2,
738 x_oa_flex_num in VARCHAR2
739 )
740 is
741 l_app_id number;
742 l_attrib_app_id number;
743 l_flex_app_id number;
744 l_row_id varchar2(64);
745 l_default_number number;
746 l_default_date date;
747 f_luby number; -- entity owner in file
748 f_ludate date; -- entity update date in file
749 db_luby number; -- entity owner in db
750 db_ludate date; -- entity update date in db
751 begin
752 -- translate values to IDs
753 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_param_defn_asn);
754 l_attrib_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_attribute_asn);
755 l_flex_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_oa_flex_asn);
756
757 l_default_number := null;
758 l_default_date := null;
759 if x_default_number is not null
760 then
761 l_default_number := to_number(x_default_number);
762 end if;
763 if x_default_date is not null
764 then
765 l_default_date := to_date(x_default_date, 'YYYY/MM/DD-HH24:MI:SS');
766 end if;
767
768 -- Translate owner to file_last_updated_by
769 f_luby := fnd_load_util.owner_id(x_owner);
770
771 -- Translate char last_update_date to date
772 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
773 begin
774 select LAST_UPDATED_BY, LAST_UPDATE_DATE
775 into db_luby, db_ludate
776 from BNE_PARAM_DEFNS_B
777 where APPLICATION_ID = l_app_id
778 and PARAM_DEFN_CODE = x_param_defn_code;
779
780 -- Test for customization and version
781 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
782 db_ludate, x_custom_mode)) then
783 -- Update existing row
784
785 BNE_PARAM_DEFNS_PKG.Update_Row(
786 X_APPLICATION_ID => l_app_id,
787 X_PARAM_DEFN_CODE => x_param_defn_code,
788 X_OBJECT_VERSION_NUMBER => x_object_version_number,
789 X_PARAM_NAME => x_param_name,
790 X_PARAM_SOURCE => x_param_source,
791 X_PARAM_CATEGORY => x_param_category,
792 X_DATATYPE => x_datatype,
793 X_ATTRIBUTE_APP_ID => l_attrib_app_id,
794 X_ATTRIBUTE_CODE => x_attribute_code,
795 X_PARAM_RESOLVER => x_param_resolver,
796 X_DEFAULT_REQUIRED_FLAG => x_default_required_flag,
797 X_DEFAULT_VISIBLE_FLAG => x_default_visible_flag,
798 X_DEFAULT_USER_MODIFYABLE_FLAG => x_default_user_modifyable_flag,
799 X_DEFAULT_DATE => l_default_date,
800 X_DEFAULT_NUMBER => l_default_number,
801 X_DEFAULT_BOOLEAN_FLAG => x_default_boolean_flag,
802 X_DEFAULT_FORMULA => x_default_formula,
803 X_VAL_TYPE => x_val_type,
804 X_VAL_VALUE => x_val_value,
805 X_MAX_SIZE => x_max_size,
806 X_DISPLAY_TYPE => x_display_type,
807 X_DISPLAY_STYLE => x_display_style,
808 X_DISPLAY_SIZE => x_display_size,
809 X_HELP_URL => x_help_url,
810 X_FORMAT_MASK => x_format_mask,
811 X_USER_NAME => x_user_name,
812 X_DEFAULT_STRING => x_default_string,
813 X_DEFAULT_STRING_TRANS_FLAG => x_default_string_trans_flag,
814 X_DEFAULT_DESC => x_default_desc,
815 X_PROMPT_LEFT => x_prompt_left,
816 X_PROMPT_ABOVE => x_prompt_above,
817 X_USER_TIP => x_user_tip,
818 X_ACCESS_KEY => x_access_key,
819 X_LAST_UPDATE_DATE => f_ludate,
820 X_LAST_UPDATED_BY => f_luby,
821 X_LAST_UPDATE_LOGIN => 0,
822 X_OA_FLEX_APPLICATION_ID=> l_flex_app_id,
823 X_OA_FLEX_CODE => x_oa_flex_code,
824 X_OA_FLEX_NUM => x_oa_flex_num
825 );
826 end if;
827 exception
828 when no_data_found then
829 -- Record doesn't exist - insert in all cases
830 BNE_PARAM_DEFNS_PKG.Insert_Row(
831 X_ROWID => l_row_id,
832 X_APPLICATION_ID => l_app_id,
833 X_PARAM_DEFN_CODE => x_param_defn_code,
834 X_OBJECT_VERSION_NUMBER => x_object_version_number,
835 X_PARAM_NAME => x_param_name,
836 X_PARAM_SOURCE => x_param_source,
837 X_PARAM_CATEGORY => x_param_category,
838 X_DATATYPE => x_datatype,
839 X_ATTRIBUTE_APP_ID => l_attrib_app_id,
840 X_ATTRIBUTE_CODE => x_attribute_code,
841 X_PARAM_RESOLVER => x_param_resolver,
842 X_DEFAULT_REQUIRED_FLAG => x_default_required_flag,
843 X_DEFAULT_VISIBLE_FLAG => x_default_visible_flag,
844 X_DEFAULT_USER_MODIFYABLE_FLAG => x_default_user_modifyable_flag,
845 X_DEFAULT_DATE => l_default_date,
846 X_DEFAULT_NUMBER => l_default_number,
847 X_DEFAULT_BOOLEAN_FLAG => x_default_boolean_flag,
848 X_DEFAULT_FORMULA => x_default_formula,
849 X_VAL_TYPE => x_val_type,
850 X_VAL_VALUE => x_val_value,
851 X_MAX_SIZE => x_max_size,
852 X_DISPLAY_TYPE => x_display_type,
853 X_DISPLAY_STYLE => x_display_style,
854 X_DISPLAY_SIZE => x_display_size,
855 X_HELP_URL => x_help_url,
856 X_FORMAT_MASK => x_format_mask,
857 X_USER_NAME => x_user_name,
858 X_DEFAULT_STRING => x_default_string,
859 X_DEFAULT_STRING_TRANS_FLAG => x_default_string_trans_flag,
860 X_DEFAULT_DESC => x_default_desc,
861 X_PROMPT_LEFT => x_prompt_left,
862 X_PROMPT_ABOVE => x_prompt_above,
863 X_USER_TIP => x_user_tip,
864 X_ACCESS_KEY => x_access_key,
865 X_CREATION_DATE => f_ludate,
866 X_CREATED_BY => f_luby,
867 X_LAST_UPDATE_DATE => f_ludate,
868 X_LAST_UPDATED_BY => f_luby,
869 X_LAST_UPDATE_LOGIN => 0,
870 X_OA_FLEX_APPLICATION_ID=> l_flex_app_id,
871 X_OA_FLEX_CODE => x_oa_flex_code,
872 X_OA_FLEX_NUM => x_oa_flex_num
873 );
874
875 end;
876 end LOAD_ROW;
877
878
879
880 end BNE_PARAM_DEFNS_PKG;