1 package body BIS_BUCKET_CUSTOMIZATIONS_PKG as
2 /* $Header: BISPBUCB.pls 115.1 2004/02/15 21:55:32 ankgoel noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ID in NUMBER,
6 X_RANGE8_HIGH in NUMBER,
7 X_RANGE9_LOW in NUMBER,
8 X_RANGE9_HIGH in NUMBER,
9 X_RANGE10_LOW in NUMBER,
10 X_RANGE10_HIGH in NUMBER,
11 X_CUSTOMIZED in VARCHAR2,
12 X_RANGE7_LOW in NUMBER,
13 X_RANGE7_HIGH in NUMBER,
14 X_RANGE8_LOW in NUMBER,
15 X_BUCKET_ID in NUMBER,
16 X_USER_ID in NUMBER,
17 X_RESPONSIBILITY_ID in NUMBER,
18 X_APPLICATION_ID in NUMBER,
19 X_ORG_ID in NUMBER,
20 X_SITE_ID in NUMBER,
21 X_PAGE_ID in NUMBER,
22 X_FUNCTION_ID in NUMBER,
23 X_RANGE1_LOW in NUMBER,
24 X_RANGE1_HIGH in NUMBER,
25 X_RANGE2_LOW in NUMBER,
26 X_RANGE2_HIGH in NUMBER,
27 X_RANGE3_LOW in NUMBER,
28 X_RANGE3_HIGH in NUMBER,
29 X_RANGE4_LOW in NUMBER,
30 X_RANGE4_HIGH in NUMBER,
31 X_RANGE5_LOW in NUMBER,
32 X_RANGE5_HIGH in NUMBER,
33 X_RANGE6_LOW in NUMBER,
34 X_RANGE6_HIGH in NUMBER,
35 X_RANGE1_NAME in VARCHAR2,
36 X_RANGE2_NAME in VARCHAR2,
37 X_RANGE3_NAME in VARCHAR2,
38 X_RANGE4_NAME in VARCHAR2,
39 X_RANGE5_NAME in VARCHAR2,
40 X_RANGE6_NAME in VARCHAR2,
41 X_RANGE7_NAME in VARCHAR2,
42 X_RANGE8_NAME in VARCHAR2,
43 X_RANGE9_NAME in VARCHAR2,
44 X_RANGE10_NAME 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 ) is
51 cursor C is select ROWID from BIS_BUCKET_CUSTOMIZATIONS
52 where ID = X_ID
53 ;
54 begin
55 insert into BIS_BUCKET_CUSTOMIZATIONS (
56 RANGE8_HIGH,
57 RANGE9_LOW,
58 RANGE9_HIGH,
59 RANGE10_LOW,
60 RANGE10_HIGH,
61 CUSTOMIZED,
62 RANGE7_LOW,
63 RANGE7_HIGH,
64 RANGE8_LOW,
65 ID,
66 BUCKET_ID,
67 USER_ID,
68 RESPONSIBILITY_ID,
69 APPLICATION_ID,
70 ORG_ID,
71 SITE_ID,
72 PAGE_ID,
73 FUNCTION_ID,
74 RANGE1_LOW,
75 RANGE1_HIGH,
76 RANGE2_LOW,
77 RANGE2_HIGH,
78 RANGE3_LOW,
79 RANGE3_HIGH,
80 RANGE4_LOW,
81 RANGE4_HIGH,
82 RANGE5_LOW,
83 RANGE5_HIGH,
84 RANGE6_LOW,
85 RANGE6_HIGH,
86 CREATION_DATE,
87 CREATED_BY,
88 LAST_UPDATE_DATE,
89 LAST_UPDATED_BY,
90 LAST_UPDATE_LOGIN
91 ) values (
92 X_RANGE8_HIGH,
93 X_RANGE9_LOW,
94 X_RANGE9_HIGH,
95 X_RANGE10_LOW,
96 X_RANGE10_HIGH,
97 X_CUSTOMIZED,
98 X_RANGE7_LOW,
99 X_RANGE7_HIGH,
100 X_RANGE8_LOW,
101 X_ID,
102 X_BUCKET_ID,
103 X_USER_ID,
104 X_RESPONSIBILITY_ID,
105 X_APPLICATION_ID,
106 X_ORG_ID,
107 X_SITE_ID,
108 X_PAGE_ID,
109 X_FUNCTION_ID,
110 X_RANGE1_LOW,
111 X_RANGE1_HIGH,
112 X_RANGE2_LOW,
113 X_RANGE2_HIGH,
114 X_RANGE3_LOW,
115 X_RANGE3_HIGH,
116 X_RANGE4_LOW,
117 X_RANGE4_HIGH,
118 X_RANGE5_LOW,
119 X_RANGE5_HIGH,
120 X_RANGE6_LOW,
121 X_RANGE6_HIGH,
122 X_CREATION_DATE,
123 X_CREATED_BY,
124 X_LAST_UPDATE_DATE,
125 X_LAST_UPDATED_BY,
126 X_LAST_UPDATE_LOGIN
127 );
128
129 insert into BIS_BUCKET_CUSTOMIZATIONS_TL (
130 CREATED_BY,
131 CREATION_DATE,
132 LAST_UPDATED_BY,
133 LAST_UPDATE_DATE,
134 LAST_UPDATE_LOGIN,
135 RANGE7_NAME,
136 RANGE8_NAME,
137 RANGE9_NAME,
138 RANGE10_NAME,
139 ID,
140 RANGE1_NAME,
141 RANGE2_NAME,
142 RANGE3_NAME,
143 RANGE4_NAME,
144 RANGE5_NAME,
145 RANGE6_NAME,
146 LANGUAGE,
147 SOURCE_LANG
148 ) select
149 X_CREATED_BY,
150 X_CREATION_DATE,
151 X_LAST_UPDATED_BY,
152 X_LAST_UPDATE_DATE,
153 X_LAST_UPDATE_LOGIN,
154 X_RANGE7_NAME,
155 X_RANGE8_NAME,
156 X_RANGE9_NAME,
157 X_RANGE10_NAME,
158 X_ID,
159 X_RANGE1_NAME,
160 X_RANGE2_NAME,
161 X_RANGE3_NAME,
162 X_RANGE4_NAME,
163 X_RANGE5_NAME,
164 X_RANGE6_NAME,
165 L.LANGUAGE_CODE,
166 userenv('LANG')
167 from FND_LANGUAGES L
168 where L.INSTALLED_FLAG in ('I', 'B')
169 and not exists
170 (select NULL
171 from BIS_BUCKET_CUSTOMIZATIONS_TL T
172 where T.ID = X_ID
173 and T.LANGUAGE = L.LANGUAGE_CODE);
174
175 open c;
176 fetch c into X_ROWID;
177 if (c%notfound) then
178 close c;
179 raise no_data_found;
180 end if;
181 close c;
182
183 end INSERT_ROW;
184
185 procedure LOCK_ROW (
186 X_ID in NUMBER,
187 X_RANGE8_HIGH in NUMBER,
188 X_RANGE9_LOW in NUMBER,
189 X_RANGE9_HIGH in NUMBER,
190 X_RANGE10_LOW in NUMBER,
191 X_RANGE10_HIGH in NUMBER,
192 X_CUSTOMIZED in VARCHAR2,
193 X_RANGE7_LOW in NUMBER,
194 X_RANGE7_HIGH in NUMBER,
195 X_RANGE8_LOW in NUMBER,
196 X_BUCKET_ID in NUMBER,
197 X_USER_ID in NUMBER,
198 X_RESPONSIBILITY_ID in NUMBER,
199 X_APPLICATION_ID in NUMBER,
200 X_ORG_ID in NUMBER,
201 X_SITE_ID in NUMBER,
202 X_PAGE_ID in NUMBER,
203 X_FUNCTION_ID in NUMBER,
204 X_RANGE1_LOW in NUMBER,
205 X_RANGE1_HIGH in NUMBER,
206 X_RANGE2_LOW in NUMBER,
207 X_RANGE2_HIGH in NUMBER,
208 X_RANGE3_LOW in NUMBER,
209 X_RANGE3_HIGH in NUMBER,
210 X_RANGE4_LOW in NUMBER,
211 X_RANGE4_HIGH in NUMBER,
212 X_RANGE5_LOW in NUMBER,
213 X_RANGE5_HIGH in NUMBER,
214 X_RANGE6_LOW in NUMBER,
215 X_RANGE6_HIGH in NUMBER,
216 X_RANGE1_NAME in VARCHAR2,
217 X_RANGE2_NAME in VARCHAR2,
218 X_RANGE3_NAME in VARCHAR2,
219 X_RANGE4_NAME in VARCHAR2,
220 X_RANGE5_NAME in VARCHAR2,
221 X_RANGE6_NAME in VARCHAR2,
222 X_RANGE7_NAME in VARCHAR2,
223 X_RANGE8_NAME in VARCHAR2,
224 X_RANGE9_NAME in VARCHAR2,
225 X_RANGE10_NAME in VARCHAR2
226 ) is
227 cursor c is select
228 RANGE8_HIGH,
229 RANGE9_LOW,
230 RANGE9_HIGH,
231 RANGE10_LOW,
232 RANGE10_HIGH,
233 CUSTOMIZED,
234 RANGE7_LOW,
235 RANGE7_HIGH,
236 RANGE8_LOW,
237 BUCKET_ID,
238 USER_ID,
239 RESPONSIBILITY_ID,
240 APPLICATION_ID,
241 ORG_ID,
242 SITE_ID,
243 PAGE_ID,
244 FUNCTION_ID,
245 RANGE1_LOW,
246 RANGE1_HIGH,
247 RANGE2_LOW,
248 RANGE2_HIGH,
249 RANGE3_LOW,
250 RANGE3_HIGH,
251 RANGE4_LOW,
252 RANGE4_HIGH,
253 RANGE5_LOW,
254 RANGE5_HIGH,
255 RANGE6_LOW,
256 RANGE6_HIGH
257 from BIS_BUCKET_CUSTOMIZATIONS
258 where ID = X_ID
259 for update of ID nowait;
260 recinfo c%rowtype;
261
262 cursor c1 is select
263 RANGE1_NAME,
264 RANGE2_NAME,
265 RANGE3_NAME,
266 RANGE4_NAME,
267 RANGE5_NAME,
268 RANGE6_NAME,
269 RANGE7_NAME,
270 RANGE8_NAME,
271 RANGE9_NAME,
272 RANGE10_NAME,
273 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
274 from BIS_BUCKET_CUSTOMIZATIONS_TL
275 where ID = X_ID
276 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
277 for update of ID nowait;
278 begin
279 open c;
280 fetch c into recinfo;
281 if (c%notfound) then
282 close c;
283 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
284 app_exception.raise_exception;
285 end if;
286 close c;
287 if ( ((recinfo.RANGE8_HIGH = X_RANGE8_HIGH)
288 OR ((recinfo.RANGE8_HIGH is null) AND (X_RANGE8_HIGH is null)))
289 AND ((recinfo.RANGE9_LOW = X_RANGE9_LOW)
290 OR ((recinfo.RANGE9_LOW is null) AND (X_RANGE9_LOW is null)))
291 AND ((recinfo.RANGE9_HIGH = X_RANGE9_HIGH)
292 OR ((recinfo.RANGE9_HIGH is null) AND (X_RANGE9_HIGH is null)))
293 AND ((recinfo.RANGE10_LOW = X_RANGE10_LOW)
294 OR ((recinfo.RANGE10_LOW is null) AND (X_RANGE10_LOW is null)))
295 AND ((recinfo.RANGE10_HIGH = X_RANGE10_HIGH)
296 OR ((recinfo.RANGE10_HIGH is null) AND (X_RANGE10_HIGH is null)))
297 AND ((recinfo.CUSTOMIZED = X_CUSTOMIZED)
298 OR ((recinfo.CUSTOMIZED is null) AND (X_CUSTOMIZED is null)))
299 AND ((recinfo.RANGE7_LOW = X_RANGE7_LOW)
300 OR ((recinfo.RANGE7_LOW is null) AND (X_RANGE7_LOW is null)))
301 AND ((recinfo.RANGE7_HIGH = X_RANGE7_HIGH)
302 OR ((recinfo.RANGE7_HIGH is null) AND (X_RANGE7_HIGH is null)))
303 AND ((recinfo.RANGE8_LOW = X_RANGE8_LOW)
304 OR ((recinfo.RANGE8_LOW is null) AND (X_RANGE8_LOW is null)))
305 AND (recinfo.BUCKET_ID = X_BUCKET_ID)
306 AND ((recinfo.USER_ID = X_USER_ID)
307 OR ((recinfo.USER_ID is null) AND (X_USER_ID is null)))
308 AND ((recinfo.RESPONSIBILITY_ID = X_RESPONSIBILITY_ID)
309 OR ((recinfo.RESPONSIBILITY_ID is null) AND (X_RESPONSIBILITY_ID is null)))
310 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
311 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
312 AND ((recinfo.ORG_ID = X_ORG_ID)
313 OR ((recinfo.ORG_ID is null) AND (X_ORG_ID is null)))
314 AND ((recinfo.SITE_ID = X_SITE_ID)
315 OR ((recinfo.SITE_ID is null) AND (X_SITE_ID is null)))
316 AND ((recinfo.PAGE_ID = X_PAGE_ID)
317 OR ((recinfo.PAGE_ID is null) AND (X_PAGE_ID is null)))
318 AND ((recinfo.FUNCTION_ID = X_FUNCTION_ID)
319 OR ((recinfo.FUNCTION_ID is null) AND (X_FUNCTION_ID is null)))
320 AND ((recinfo.RANGE1_LOW = X_RANGE1_LOW)
321 OR ((recinfo.RANGE1_LOW is null) AND (X_RANGE1_LOW is null)))
322 AND ((recinfo.RANGE1_HIGH = X_RANGE1_HIGH)
323 OR ((recinfo.RANGE1_HIGH is null) AND (X_RANGE1_HIGH is null)))
324 AND ((recinfo.RANGE2_LOW = X_RANGE2_LOW)
325 OR ((recinfo.RANGE2_LOW is null) AND (X_RANGE2_LOW is null)))
326 AND ((recinfo.RANGE2_HIGH = X_RANGE2_HIGH)
327 OR ((recinfo.RANGE2_HIGH is null) AND (X_RANGE2_HIGH is null)))
328 AND ((recinfo.RANGE3_LOW = X_RANGE3_LOW)
329 OR ((recinfo.RANGE3_LOW is null) AND (X_RANGE3_LOW is null)))
330 AND ((recinfo.RANGE3_HIGH = X_RANGE3_HIGH)
331 OR ((recinfo.RANGE3_HIGH is null) AND (X_RANGE3_HIGH is null)))
332 AND ((recinfo.RANGE4_LOW = X_RANGE4_LOW)
333 OR ((recinfo.RANGE4_LOW is null) AND (X_RANGE4_LOW is null)))
334 AND ((recinfo.RANGE4_HIGH = X_RANGE4_HIGH)
335 OR ((recinfo.RANGE4_HIGH is null) AND (X_RANGE4_HIGH is null)))
336 AND ((recinfo.RANGE5_LOW = X_RANGE5_LOW)
337 OR ((recinfo.RANGE5_LOW is null) AND (X_RANGE5_LOW is null)))
338 AND ((recinfo.RANGE5_HIGH = X_RANGE5_HIGH)
339 OR ((recinfo.RANGE5_HIGH is null) AND (X_RANGE5_HIGH is null)))
340 AND ((recinfo.RANGE6_LOW = X_RANGE6_LOW)
341 OR ((recinfo.RANGE6_LOW is null) AND (X_RANGE6_LOW is null)))
342 AND ((recinfo.RANGE6_HIGH = X_RANGE6_HIGH)
343 OR ((recinfo.RANGE6_HIGH is null) AND (X_RANGE6_HIGH is null)))
344 ) then
345 null;
346 else
347 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
348 app_exception.raise_exception;
352 if (tlinfo.BASELANG = 'Y') then
349 end if;
350
351 for tlinfo in c1 loop
353 if ( ((tlinfo.RANGE1_NAME = X_RANGE1_NAME)
354 OR ((tlinfo.RANGE1_NAME is null) AND (X_RANGE1_NAME is null)))
355 AND ((tlinfo.RANGE2_NAME = X_RANGE2_NAME)
356 OR ((tlinfo.RANGE2_NAME is null) AND (X_RANGE2_NAME is null)))
357 AND ((tlinfo.RANGE3_NAME = X_RANGE3_NAME)
358 OR ((tlinfo.RANGE3_NAME is null) AND (X_RANGE3_NAME is null)))
359 AND ((tlinfo.RANGE4_NAME = X_RANGE4_NAME)
360 OR ((tlinfo.RANGE4_NAME is null) AND (X_RANGE4_NAME is null)))
361 AND ((tlinfo.RANGE5_NAME = X_RANGE5_NAME)
362 OR ((tlinfo.RANGE5_NAME is null) AND (X_RANGE5_NAME is null)))
363 AND ((tlinfo.RANGE6_NAME = X_RANGE6_NAME)
364 OR ((tlinfo.RANGE6_NAME is null) AND (X_RANGE6_NAME is null)))
365 AND ((tlinfo.RANGE7_NAME = X_RANGE7_NAME)
366 OR ((tlinfo.RANGE7_NAME is null) AND (X_RANGE7_NAME is null)))
367 AND ((tlinfo.RANGE8_NAME = X_RANGE8_NAME)
368 OR ((tlinfo.RANGE8_NAME is null) AND (X_RANGE8_NAME is null)))
369 AND ((tlinfo.RANGE9_NAME = X_RANGE9_NAME)
370 OR ((tlinfo.RANGE9_NAME is null) AND (X_RANGE9_NAME is null)))
371 AND ((tlinfo.RANGE10_NAME = X_RANGE10_NAME)
372 OR ((tlinfo.RANGE10_NAME is null) AND (X_RANGE10_NAME is null)))
373 ) then
374 null;
375 else
376 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
377 app_exception.raise_exception;
378 end if;
379 end if;
380 end loop;
381 return;
382 end LOCK_ROW;
383
384 procedure UPDATE_ROW (
385 X_ID in NUMBER,
386 X_RANGE8_HIGH in NUMBER,
387 X_RANGE9_LOW in NUMBER,
388 X_RANGE9_HIGH in NUMBER,
389 X_RANGE10_LOW in NUMBER,
390 X_RANGE10_HIGH in NUMBER,
391 X_CUSTOMIZED in VARCHAR2,
392 X_RANGE7_LOW in NUMBER,
393 X_RANGE7_HIGH in NUMBER,
394 X_RANGE8_LOW in NUMBER,
395 X_BUCKET_ID in NUMBER,
396 X_USER_ID in NUMBER,
397 X_RESPONSIBILITY_ID in NUMBER,
398 X_APPLICATION_ID in NUMBER,
399 X_ORG_ID in NUMBER,
400 X_SITE_ID in NUMBER,
401 X_PAGE_ID in NUMBER,
402 X_FUNCTION_ID in NUMBER,
403 X_RANGE1_LOW in NUMBER,
404 X_RANGE1_HIGH in NUMBER,
405 X_RANGE2_LOW in NUMBER,
406 X_RANGE2_HIGH in NUMBER,
407 X_RANGE3_LOW in NUMBER,
408 X_RANGE3_HIGH in NUMBER,
409 X_RANGE4_LOW in NUMBER,
410 X_RANGE4_HIGH in NUMBER,
411 X_RANGE5_LOW in NUMBER,
412 X_RANGE5_HIGH in NUMBER,
413 X_RANGE6_LOW in NUMBER,
414 X_RANGE6_HIGH in NUMBER,
415 X_RANGE1_NAME in VARCHAR2,
416 X_RANGE2_NAME in VARCHAR2,
417 X_RANGE3_NAME in VARCHAR2,
418 X_RANGE4_NAME in VARCHAR2,
419 X_RANGE5_NAME in VARCHAR2,
420 X_RANGE6_NAME in VARCHAR2,
421 X_RANGE7_NAME in VARCHAR2,
422 X_RANGE8_NAME in VARCHAR2,
423 X_RANGE9_NAME in VARCHAR2,
424 X_RANGE10_NAME in VARCHAR2,
425 X_LAST_UPDATE_DATE in DATE,
426 X_LAST_UPDATED_BY in NUMBER,
427 X_LAST_UPDATE_LOGIN in NUMBER
428 ) is
429 begin
430 update BIS_BUCKET_CUSTOMIZATIONS set
431 RANGE8_HIGH = X_RANGE8_HIGH,
432 RANGE9_LOW = X_RANGE9_LOW,
433 RANGE9_HIGH = X_RANGE9_HIGH,
434 RANGE10_LOW = X_RANGE10_LOW,
435 RANGE10_HIGH = X_RANGE10_HIGH,
436 CUSTOMIZED = X_CUSTOMIZED,
437 RANGE7_LOW = X_RANGE7_LOW,
438 RANGE7_HIGH = X_RANGE7_HIGH,
439 RANGE8_LOW = X_RANGE8_LOW,
440 BUCKET_ID = X_BUCKET_ID,
441 USER_ID = X_USER_ID,
442 RESPONSIBILITY_ID = X_RESPONSIBILITY_ID,
443 APPLICATION_ID = X_APPLICATION_ID,
444 ORG_ID = X_ORG_ID,
445 SITE_ID = X_SITE_ID,
446 PAGE_ID = X_PAGE_ID,
447 FUNCTION_ID = X_FUNCTION_ID,
448 RANGE1_LOW = X_RANGE1_LOW,
449 RANGE1_HIGH = X_RANGE1_HIGH,
450 RANGE2_LOW = X_RANGE2_LOW,
451 RANGE2_HIGH = X_RANGE2_HIGH,
452 RANGE3_LOW = X_RANGE3_LOW,
453 RANGE3_HIGH = X_RANGE3_HIGH,
454 RANGE4_LOW = X_RANGE4_LOW,
455 RANGE4_HIGH = X_RANGE4_HIGH,
456 RANGE5_LOW = X_RANGE5_LOW,
457 RANGE5_HIGH = X_RANGE5_HIGH,
458 RANGE6_LOW = X_RANGE6_LOW,
459 RANGE6_HIGH = X_RANGE6_HIGH,
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 where ID = X_ID;
464
465 if (sql%notfound) then
466 raise no_data_found;
467 end if;
468
469 update BIS_BUCKET_CUSTOMIZATIONS_TL set
470 RANGE1_NAME = X_RANGE1_NAME,
471 RANGE2_NAME = X_RANGE2_NAME,
472 RANGE3_NAME = X_RANGE3_NAME,
473 RANGE4_NAME = X_RANGE4_NAME,
474 RANGE5_NAME = X_RANGE5_NAME,
475 RANGE6_NAME = X_RANGE6_NAME,
476 RANGE7_NAME = X_RANGE7_NAME,
477 RANGE8_NAME = X_RANGE8_NAME,
478 RANGE9_NAME = X_RANGE9_NAME,
479 RANGE10_NAME = X_RANGE10_NAME,
480 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
481 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
482 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
483 SOURCE_LANG = userenv('LANG')
484 where ID = X_ID
485 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
486
487 if (sql%notfound) then
488 raise no_data_found;
489 end if;
490 end UPDATE_ROW;
491
492 procedure DELETE_ROW (
493 X_ID in NUMBER
494 ) is
495 begin
496 delete from BIS_BUCKET_CUSTOMIZATIONS_TL
497 where ID = X_ID;
498
499 if (sql%notfound) then
500 raise no_data_found;
501 end if;
502
503 delete from BIS_BUCKET_CUSTOMIZATIONS
504 where ID = X_ID;
505
506 if (sql%notfound) then
510
507 raise no_data_found;
508 end if;
509 end DELETE_ROW;
511 procedure ADD_LANGUAGE
512 is
513 begin
514 delete from BIS_BUCKET_CUSTOMIZATIONS_TL T
515 where not exists
516 (select NULL
517 from BIS_BUCKET_CUSTOMIZATIONS B
518 where B.ID = T.ID
519 );
520
521 update BIS_BUCKET_CUSTOMIZATIONS_TL T set (
522 RANGE1_NAME,
523 RANGE2_NAME,
524 RANGE3_NAME,
525 RANGE4_NAME,
526 RANGE5_NAME,
527 RANGE6_NAME,
528 RANGE7_NAME,
529 RANGE8_NAME,
530 RANGE9_NAME,
531 RANGE10_NAME
532 ) = (select
533 B.RANGE1_NAME,
534 B.RANGE2_NAME,
535 B.RANGE3_NAME,
536 B.RANGE4_NAME,
537 B.RANGE5_NAME,
538 B.RANGE6_NAME,
539 B.RANGE7_NAME,
540 B.RANGE8_NAME,
541 B.RANGE9_NAME,
542 B.RANGE10_NAME
543 from BIS_BUCKET_CUSTOMIZATIONS_TL B
544 where B.ID = T.ID
545 and B.LANGUAGE = T.SOURCE_LANG)
546 where (
547 T.ID,
548 T.LANGUAGE
549 ) in (select
550 SUBT.ID,
551 SUBT.LANGUAGE
552 from BIS_BUCKET_CUSTOMIZATIONS_TL SUBB, BIS_BUCKET_CUSTOMIZATIONS_TL SUBT
553 where SUBB.ID = SUBT.ID
554 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
555 and (SUBB.RANGE1_NAME <> SUBT.RANGE1_NAME
556 or (SUBB.RANGE1_NAME is null and SUBT.RANGE1_NAME is not null)
557 or (SUBB.RANGE1_NAME is not null and SUBT.RANGE1_NAME is null)
558 or SUBB.RANGE2_NAME <> SUBT.RANGE2_NAME
559 or (SUBB.RANGE2_NAME is null and SUBT.RANGE2_NAME is not null)
560 or (SUBB.RANGE2_NAME is not null and SUBT.RANGE2_NAME is null)
561 or SUBB.RANGE3_NAME <> SUBT.RANGE3_NAME
562 or (SUBB.RANGE3_NAME is null and SUBT.RANGE3_NAME is not null)
563 or (SUBB.RANGE3_NAME is not null and SUBT.RANGE3_NAME is null)
564 or SUBB.RANGE4_NAME <> SUBT.RANGE4_NAME
565 or (SUBB.RANGE4_NAME is null and SUBT.RANGE4_NAME is not null)
566 or (SUBB.RANGE4_NAME is not null and SUBT.RANGE4_NAME is null)
567 or SUBB.RANGE5_NAME <> SUBT.RANGE5_NAME
568 or (SUBB.RANGE5_NAME is null and SUBT.RANGE5_NAME is not null)
569 or (SUBB.RANGE5_NAME is not null and SUBT.RANGE5_NAME is null)
570 or SUBB.RANGE6_NAME <> SUBT.RANGE6_NAME
571 or (SUBB.RANGE6_NAME is null and SUBT.RANGE6_NAME is not null)
572 or (SUBB.RANGE6_NAME is not null and SUBT.RANGE6_NAME is null)
573 or SUBB.RANGE7_NAME <> SUBT.RANGE7_NAME
574 or (SUBB.RANGE7_NAME is null and SUBT.RANGE7_NAME is not null)
575 or (SUBB.RANGE7_NAME is not null and SUBT.RANGE7_NAME is null)
576 or SUBB.RANGE8_NAME <> SUBT.RANGE8_NAME
577 or (SUBB.RANGE8_NAME is null and SUBT.RANGE8_NAME is not null)
578 or (SUBB.RANGE8_NAME is not null and SUBT.RANGE8_NAME is null)
579 or SUBB.RANGE9_NAME <> SUBT.RANGE9_NAME
580 or (SUBB.RANGE9_NAME is null and SUBT.RANGE9_NAME is not null)
581 or (SUBB.RANGE9_NAME is not null and SUBT.RANGE9_NAME is null)
582 or SUBB.RANGE10_NAME <> SUBT.RANGE10_NAME
583 or (SUBB.RANGE10_NAME is null and SUBT.RANGE10_NAME is not null)
584 or (SUBB.RANGE10_NAME is not null and SUBT.RANGE10_NAME is null)
585 ));
586
587 insert into BIS_BUCKET_CUSTOMIZATIONS_TL (
588 CREATED_BY,
589 CREATION_DATE,
590 LAST_UPDATED_BY,
591 LAST_UPDATE_DATE,
592 LAST_UPDATE_LOGIN,
593 RANGE7_NAME,
594 RANGE8_NAME,
595 RANGE9_NAME,
596 RANGE10_NAME,
597 ID,
598 RANGE1_NAME,
599 RANGE2_NAME,
600 RANGE3_NAME,
601 RANGE4_NAME,
602 RANGE5_NAME,
603 RANGE6_NAME,
604 LANGUAGE,
605 SOURCE_LANG
606 ) select
607 B.CREATED_BY,
608 B.CREATION_DATE,
609 B.LAST_UPDATED_BY,
610 B.LAST_UPDATE_DATE,
611 B.LAST_UPDATE_LOGIN,
612 B.RANGE7_NAME,
613 B.RANGE8_NAME,
614 B.RANGE9_NAME,
615 B.RANGE10_NAME,
616 B.ID,
617 B.RANGE1_NAME,
618 B.RANGE2_NAME,
619 B.RANGE3_NAME,
620 B.RANGE4_NAME,
621 B.RANGE5_NAME,
622 B.RANGE6_NAME,
623 L.LANGUAGE_CODE,
624 B.SOURCE_LANG
625 from BIS_BUCKET_CUSTOMIZATIONS_TL B, FND_LANGUAGES L
626 where L.INSTALLED_FLAG in ('I', 'B')
627 and B.LANGUAGE = userenv('LANG')
628 and not exists
629 (select NULL
630 from BIS_BUCKET_CUSTOMIZATIONS_TL T
631 where T.ID = B.ID
632 and T.LANGUAGE = L.LANGUAGE_CODE);
633 end ADD_LANGUAGE;
634
635 end BIS_BUCKET_CUSTOMIZATIONS_PKG;
[Home] [Help]