[Home] [Help]
PACKAGE BODY: APPS.FEM_DIS_USR_DIM9_HIER_PKG
Source
1 package body FEM_DIS_USR_DIM9_HIER_PKG as
2 /* $Header: fem_disusrd9_pkb.plb 120.0 2005/10/19 19:31:50 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_OBJECT_ID in NUMBER,
6 X_OBJECT_DEFINITION_ID in NUMBER,
7 X_VALUE_SET_ID in NUMBER,
8 X_LEVEL1_ID in NUMBER,
9 X_LEVEL2_ID in NUMBER,
10 X_LEVEL3_ID in NUMBER,
11 X_LEVEL4_ID in NUMBER,
12 X_LEVEL5_ID in NUMBER,
13 X_LEVEL6_ID in NUMBER,
14 X_LEVEL7_ID in NUMBER,
15 X_LEVEL8_ID in NUMBER,
16 X_LEVEL9_ID in NUMBER,
17 X_LEVEL10_ID in NUMBER,
18 X_LEVEL11_ID in NUMBER,
19 X_LEVEL12_ID in NUMBER,
20 X_LEVEL13_ID in NUMBER,
21 X_LEVEL14_ID in NUMBER,
22 X_LEVEL15_ID in NUMBER,
23 X_LEVEL16_ID in NUMBER,
24 X_LEVEL17_ID in NUMBER,
25 X_LEVEL18_ID in NUMBER,
26 X_LEVEL19_ID in NUMBER,
27 X_LEVEL20_ID in NUMBER,
28 X_LEVEL1_DISPLAY_CODE in VARCHAR2,
29 X_LEVEL2_DISPLAY_CODE in VARCHAR2,
30 X_LEVEL3_DISPLAY_CODE in VARCHAR2,
31 X_LEVEL4_DISPLAY_CODE in VARCHAR2,
32 X_LEVEL5_DISPLAY_CODE in VARCHAR2,
33 X_LEVEL6_DISPLAY_CODE in VARCHAR2,
34 X_LEVEL7_DISPLAY_CODE in VARCHAR2,
35 X_LEVEL8_DISPLAY_CODE in VARCHAR2,
36 X_LEVEL9_DISPLAY_CODE in VARCHAR2,
37 X_LEVEL10_DISPLAY_CODE in VARCHAR2,
38 X_LEVEL11_DISPLAY_CODE in VARCHAR2,
39 X_LEVEL12_DISPLAY_CODE in VARCHAR2,
40 X_LEVEL13_DISPLAY_CODE in VARCHAR2,
41 X_LEVEL14_DISPLAY_CODE in VARCHAR2,
42 X_LEVEL15_DISPLAY_CODE in VARCHAR2,
43 X_LEVEL16_DISPLAY_CODE in VARCHAR2,
44 X_LEVEL17_DISPLAY_CODE in VARCHAR2,
45 X_LEVEL18_DISPLAY_CODE in VARCHAR2,
46 X_LEVEL19_DISPLAY_CODE in VARCHAR2,
47 X_LEVEL20_DISPLAY_CODE in VARCHAR2,
48 X_LEVEL1_DISPLAY_ORDER_NUM in NUMBER,
49 X_LEVEL2_DISPLAY_ORDER_NUM in NUMBER,
50 X_LEVEL3_DISPLAY_ORDER_NUM in NUMBER,
51 X_LEVEL4_DISPLAY_ORDER_NUM in NUMBER,
52 X_LEVEL5_DISPLAY_ORDER_NUM in NUMBER,
53 X_LEVEL6_DISPLAY_ORDER_NUM in NUMBER,
54 X_LEVEL7_DISPLAY_ORDER_NUM in NUMBER,
55 X_LEVEL8_DISPLAY_ORDER_NUM in NUMBER,
56 X_LEVEL9_DISPLAY_ORDER_NUM in NUMBER,
57 X_LEVEL10_DISPLAY_ORDER_NUM in NUMBER,
58 X_LEVEL11_DISPLAY_ORDER_NUM in NUMBER,
59 X_LEVEL12_DISPLAY_ORDER_NUM in NUMBER,
60 X_LEVEL13_DISPLAY_ORDER_NUM in NUMBER,
61 X_LEVEL14_DISPLAY_ORDER_NUM in NUMBER,
62 X_LEVEL15_DISPLAY_ORDER_NUM in NUMBER,
63 X_LEVEL16_DISPLAY_ORDER_NUM in NUMBER,
64 X_LEVEL17_DISPLAY_ORDER_NUM in NUMBER,
65 X_LEVEL18_DISPLAY_ORDER_NUM in NUMBER,
66 X_LEVEL19_DISPLAY_ORDER_NUM in NUMBER,
67 X_LEVEL20_DISPLAY_ORDER_NUM in NUMBER,
68 X_OBJECT_NAME in VARCHAR2,
69 X_OBJECT_DEFINITION_NAME in VARCHAR2,
70 X_LEVEL1_NAME in VARCHAR2,
71 X_LEVEL2_NAME in VARCHAR2,
72 X_LEVEL3_NAME in VARCHAR2,
73 X_LEVEL4_NAME in VARCHAR2,
74 X_LEVEL5_NAME in VARCHAR2,
75 X_LEVEL6_NAME in VARCHAR2,
76 X_LEVEL7_NAME in VARCHAR2,
77 X_LEVEL8_NAME in VARCHAR2,
78 X_LEVEL9_NAME in VARCHAR2,
79 X_LEVEL10_NAME in VARCHAR2,
80 X_LEVEL11_NAME in VARCHAR2,
81 X_LEVEL12_NAME in VARCHAR2,
82 X_LEVEL13_NAME in VARCHAR2,
83 X_LEVEL14_NAME in VARCHAR2,
84 X_LEVEL15_NAME in VARCHAR2,
85 X_LEVEL16_NAME in VARCHAR2,
86 X_LEVEL17_NAME in VARCHAR2,
87 X_LEVEL18_NAME in VARCHAR2,
88 X_LEVEL19_NAME in VARCHAR2,
89 X_LEVEL20_NAME in VARCHAR2,
90 X_LEVEL1_DESCRIPTION in VARCHAR2,
91 X_LEVEL2_DESCRIPTION in VARCHAR2,
92 X_LEVEL3_DESCRIPTION in VARCHAR2,
93 X_LEVEL4_DESCRIPTION in VARCHAR2,
94 X_LEVEL5_DESCRIPTION in VARCHAR2,
95 X_LEVEL6_DESCRIPTION in VARCHAR2,
96 X_LEVEL7_DESCRIPTION in VARCHAR2,
97 X_LEVEL8_DESCRIPTION in VARCHAR2,
98 X_LEVEL9_DESCRIPTION in VARCHAR2,
99 X_LEVEL10_DESCRIPTION in VARCHAR2,
100 X_LEVEL11_DESCRIPTION in VARCHAR2,
101 X_LEVEL12_DESCRIPTION in VARCHAR2,
102 X_LEVEL13_DESCRIPTION in VARCHAR2,
103 X_LEVEL14_DESCRIPTION in VARCHAR2,
104 X_LEVEL15_DESCRIPTION in VARCHAR2,
105 X_LEVEL16_DESCRIPTION in VARCHAR2,
106 X_LEVEL17_DESCRIPTION in VARCHAR2,
107 X_LEVEL18_DESCRIPTION in VARCHAR2,
108 X_LEVEL19_DESCRIPTION in VARCHAR2,
109 X_LEVEL20_DESCRIPTION in VARCHAR2,
110 X_CREATION_DATE in DATE,
111 X_CREATED_BY in NUMBER,
112 X_LAST_UPDATE_DATE in DATE,
113 X_LAST_UPDATED_BY in NUMBER,
114 X_LAST_UPDATE_LOGIN in NUMBER
115 ) is
116 cursor C is select ROWID from FEM_DIS_USR_DIM9_HIER_B
117 where OBJECT_ID = X_OBJECT_ID
118 and OBJECT_DEFINITION_ID = X_OBJECT_DEFINITION_ID
119 and VALUE_SET_ID = X_VALUE_SET_ID
120 and LEVEL1_ID = X_LEVEL1_ID
121 and LEVEL2_ID = X_LEVEL2_ID
122 and LEVEL3_ID = X_LEVEL3_ID
123 and LEVEL4_ID = X_LEVEL4_ID
124 and LEVEL5_ID = X_LEVEL5_ID
125 and LEVEL6_ID = X_LEVEL6_ID
126 and LEVEL7_ID = X_LEVEL7_ID
127 and LEVEL8_ID = X_LEVEL8_ID
128 and LEVEL9_ID = X_LEVEL9_ID
129 and LEVEL10_ID = X_LEVEL10_ID
130 and LEVEL11_ID = X_LEVEL11_ID
131 and LEVEL12_ID = X_LEVEL12_ID
132 and LEVEL13_ID = X_LEVEL13_ID
133 and LEVEL14_ID = X_LEVEL14_ID
134 and LEVEL15_ID = X_LEVEL15_ID
135 and LEVEL16_ID = X_LEVEL16_ID
136 and LEVEL17_ID = X_LEVEL17_ID
137 and LEVEL18_ID = X_LEVEL18_ID
138 and LEVEL19_ID = X_LEVEL19_ID
139 and LEVEL20_ID = X_LEVEL20_ID
140 ;
141 begin
142 insert into FEM_DIS_USR_DIM9_HIER_B (
143 OBJECT_ID,
144 OBJECT_DEFINITION_ID,
145 VALUE_SET_ID,
146 LEVEL1_ID,
147 LEVEL2_ID,
148 LEVEL3_ID,
149 LEVEL4_ID,
150 LEVEL5_ID,
151 LEVEL6_ID,
152 LEVEL7_ID,
153 LEVEL8_ID,
154 LEVEL9_ID,
155 LEVEL10_ID,
156 LEVEL11_ID,
157 LEVEL12_ID,
158 LEVEL13_ID,
159 LEVEL14_ID,
160 LEVEL15_ID,
161 LEVEL16_ID,
162 LEVEL17_ID,
163 LEVEL18_ID,
164 LEVEL19_ID,
165 LEVEL20_ID,
166 LEVEL1_DISPLAY_CODE,
167 LEVEL2_DISPLAY_CODE,
168 LEVEL3_DISPLAY_CODE,
169 LEVEL4_DISPLAY_CODE,
170 LEVEL5_DISPLAY_CODE,
171 LEVEL6_DISPLAY_CODE,
172 LEVEL7_DISPLAY_CODE,
173 LEVEL8_DISPLAY_CODE,
174 LEVEL9_DISPLAY_CODE,
175 LEVEL10_DISPLAY_CODE,
176 LEVEL11_DISPLAY_CODE,
177 LEVEL12_DISPLAY_CODE,
178 LEVEL13_DISPLAY_CODE,
179 LEVEL14_DISPLAY_CODE,
180 LEVEL15_DISPLAY_CODE,
181 LEVEL16_DISPLAY_CODE,
182 LEVEL17_DISPLAY_CODE,
183 LEVEL18_DISPLAY_CODE,
184 LEVEL19_DISPLAY_CODE,
185 LEVEL20_DISPLAY_CODE,
186 LEVEL1_DISPLAY_ORDER_NUM,
187 LEVEL2_DISPLAY_ORDER_NUM,
188 LEVEL3_DISPLAY_ORDER_NUM,
189 LEVEL4_DISPLAY_ORDER_NUM,
190 LEVEL5_DISPLAY_ORDER_NUM,
191 LEVEL6_DISPLAY_ORDER_NUM,
192 LEVEL7_DISPLAY_ORDER_NUM,
193 LEVEL8_DISPLAY_ORDER_NUM,
194 LEVEL9_DISPLAY_ORDER_NUM,
195 LEVEL10_DISPLAY_ORDER_NUM,
196 LEVEL11_DISPLAY_ORDER_NUM,
197 LEVEL12_DISPLAY_ORDER_NUM,
198 LEVEL13_DISPLAY_ORDER_NUM,
199 LEVEL14_DISPLAY_ORDER_NUM,
200 LEVEL15_DISPLAY_ORDER_NUM,
201 LEVEL16_DISPLAY_ORDER_NUM,
202 LEVEL17_DISPLAY_ORDER_NUM,
203 LEVEL18_DISPLAY_ORDER_NUM,
204 LEVEL19_DISPLAY_ORDER_NUM,
205 LEVEL20_DISPLAY_ORDER_NUM,
206 CREATION_DATE,
207 CREATED_BY,
208 LAST_UPDATE_DATE,
209 LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN
211 ) values (
212 X_OBJECT_ID,
213 X_OBJECT_DEFINITION_ID,
214 X_VALUE_SET_ID,
215 X_LEVEL1_ID,
216 X_LEVEL2_ID,
217 X_LEVEL3_ID,
218 X_LEVEL4_ID,
219 X_LEVEL5_ID,
220 X_LEVEL6_ID,
221 X_LEVEL7_ID,
222 X_LEVEL8_ID,
223 X_LEVEL9_ID,
224 X_LEVEL10_ID,
225 X_LEVEL11_ID,
226 X_LEVEL12_ID,
227 X_LEVEL13_ID,
228 X_LEVEL14_ID,
229 X_LEVEL15_ID,
230 X_LEVEL16_ID,
231 X_LEVEL17_ID,
232 X_LEVEL18_ID,
233 X_LEVEL19_ID,
234 X_LEVEL20_ID,
235 X_LEVEL1_DISPLAY_CODE,
236 X_LEVEL2_DISPLAY_CODE,
237 X_LEVEL3_DISPLAY_CODE,
238 X_LEVEL4_DISPLAY_CODE,
239 X_LEVEL5_DISPLAY_CODE,
240 X_LEVEL6_DISPLAY_CODE,
241 X_LEVEL7_DISPLAY_CODE,
242 X_LEVEL8_DISPLAY_CODE,
243 X_LEVEL9_DISPLAY_CODE,
244 X_LEVEL10_DISPLAY_CODE,
245 X_LEVEL11_DISPLAY_CODE,
246 X_LEVEL12_DISPLAY_CODE,
247 X_LEVEL13_DISPLAY_CODE,
248 X_LEVEL14_DISPLAY_CODE,
249 X_LEVEL15_DISPLAY_CODE,
250 X_LEVEL16_DISPLAY_CODE,
251 X_LEVEL17_DISPLAY_CODE,
252 X_LEVEL18_DISPLAY_CODE,
253 X_LEVEL19_DISPLAY_CODE,
254 X_LEVEL20_DISPLAY_CODE,
255 X_LEVEL1_DISPLAY_ORDER_NUM,
256 X_LEVEL2_DISPLAY_ORDER_NUM,
257 X_LEVEL3_DISPLAY_ORDER_NUM,
258 X_LEVEL4_DISPLAY_ORDER_NUM,
259 X_LEVEL5_DISPLAY_ORDER_NUM,
260 X_LEVEL6_DISPLAY_ORDER_NUM,
261 X_LEVEL7_DISPLAY_ORDER_NUM,
262 X_LEVEL8_DISPLAY_ORDER_NUM,
263 X_LEVEL9_DISPLAY_ORDER_NUM,
264 X_LEVEL10_DISPLAY_ORDER_NUM,
265 X_LEVEL11_DISPLAY_ORDER_NUM,
266 X_LEVEL12_DISPLAY_ORDER_NUM,
267 X_LEVEL13_DISPLAY_ORDER_NUM,
268 X_LEVEL14_DISPLAY_ORDER_NUM,
269 X_LEVEL15_DISPLAY_ORDER_NUM,
270 X_LEVEL16_DISPLAY_ORDER_NUM,
271 X_LEVEL17_DISPLAY_ORDER_NUM,
272 X_LEVEL18_DISPLAY_ORDER_NUM,
273 X_LEVEL19_DISPLAY_ORDER_NUM,
274 X_LEVEL20_DISPLAY_ORDER_NUM,
275 X_CREATION_DATE,
276 X_CREATED_BY,
277 X_LAST_UPDATE_DATE,
278 X_LAST_UPDATED_BY,
279 X_LAST_UPDATE_LOGIN
280 );
281
282 insert into FEM_DIS_USR_DIM9_HIER_TL (
283 OBJECT_ID,
284 OBJECT_DEFINITION_ID,
285 VALUE_SET_ID,
286 LEVEL1_ID,
287 LEVEL2_ID,
288 LEVEL3_ID,
289 LEVEL4_ID,
290 LEVEL5_ID,
291 LEVEL6_ID,
292 LEVEL7_ID,
293 LEVEL8_ID,
294 LEVEL9_ID,
295 LEVEL10_ID,
296 LEVEL11_ID,
297 LEVEL12_ID,
298 LEVEL13_ID,
299 LEVEL14_ID,
300 LEVEL15_ID,
301 LEVEL16_ID,
302 LEVEL17_ID,
303 LEVEL18_ID,
304 LEVEL19_ID,
305 LEVEL20_ID,
306 OBJECT_NAME,
307 OBJECT_DEFINITION_NAME,
308 LEVEL1_NAME,
309 LEVEL2_NAME,
310 LEVEL3_NAME,
311 LEVEL4_NAME,
312 LEVEL5_NAME,
313 LEVEL6_NAME,
314 LEVEL7_NAME,
315 LEVEL8_NAME,
316 LEVEL9_NAME,
317 LEVEL10_NAME,
318 LEVEL11_NAME,
319 LEVEL12_NAME,
320 LEVEL13_NAME,
321 LEVEL14_NAME,
322 LEVEL15_NAME,
323 LEVEL16_NAME,
324 LEVEL17_NAME,
325 LEVEL18_NAME,
326 LEVEL19_NAME,
327 LEVEL20_NAME,
328 LEVEL1_DESCRIPTION,
329 LEVEL2_DESCRIPTION,
330 LEVEL3_DESCRIPTION,
331 LEVEL4_DESCRIPTION,
332 LEVEL5_DESCRIPTION,
333 LEVEL6_DESCRIPTION,
334 LEVEL7_DESCRIPTION,
335 LEVEL8_DESCRIPTION,
336 LEVEL9_DESCRIPTION,
337 LEVEL10_DESCRIPTION,
338 LEVEL11_DESCRIPTION,
339 LEVEL12_DESCRIPTION,
340 LEVEL13_DESCRIPTION,
341 LEVEL14_DESCRIPTION,
342 LEVEL15_DESCRIPTION,
343 LEVEL16_DESCRIPTION,
344 LEVEL17_DESCRIPTION,
345 LEVEL18_DESCRIPTION,
346 LEVEL19_DESCRIPTION,
347 LEVEL20_DESCRIPTION,
348 CREATION_DATE,
349 CREATED_BY,
350 LAST_UPDATE_DATE,
351 LAST_UPDATED_BY,
352 LAST_UPDATE_LOGIN,
353 LANGUAGE,
354 SOURCE_LANG
355 ) select
356 X_OBJECT_ID,
357 X_OBJECT_DEFINITION_ID,
358 X_VALUE_SET_ID,
359 X_LEVEL1_ID,
360 X_LEVEL2_ID,
361 X_LEVEL3_ID,
362 X_LEVEL4_ID,
363 X_LEVEL5_ID,
364 X_LEVEL6_ID,
365 X_LEVEL7_ID,
366 X_LEVEL8_ID,
367 X_LEVEL9_ID,
368 X_LEVEL10_ID,
369 X_LEVEL11_ID,
370 X_LEVEL12_ID,
371 X_LEVEL13_ID,
372 X_LEVEL14_ID,
373 X_LEVEL15_ID,
374 X_LEVEL16_ID,
375 X_LEVEL17_ID,
376 X_LEVEL18_ID,
377 X_LEVEL19_ID,
378 X_LEVEL20_ID,
379 X_OBJECT_NAME,
380 X_OBJECT_DEFINITION_NAME,
381 X_LEVEL1_NAME,
382 X_LEVEL2_NAME,
383 X_LEVEL3_NAME,
384 X_LEVEL4_NAME,
385 X_LEVEL5_NAME,
386 X_LEVEL6_NAME,
387 X_LEVEL7_NAME,
388 X_LEVEL8_NAME,
389 X_LEVEL9_NAME,
390 X_LEVEL10_NAME,
391 X_LEVEL11_NAME,
392 X_LEVEL12_NAME,
393 X_LEVEL13_NAME,
394 X_LEVEL14_NAME,
395 X_LEVEL15_NAME,
396 X_LEVEL16_NAME,
397 X_LEVEL17_NAME,
398 X_LEVEL18_NAME,
399 X_LEVEL19_NAME,
400 X_LEVEL20_NAME,
401 X_LEVEL1_DESCRIPTION,
402 X_LEVEL2_DESCRIPTION,
403 X_LEVEL3_DESCRIPTION,
404 X_LEVEL4_DESCRIPTION,
405 X_LEVEL5_DESCRIPTION,
406 X_LEVEL6_DESCRIPTION,
407 X_LEVEL7_DESCRIPTION,
408 X_LEVEL8_DESCRIPTION,
409 X_LEVEL9_DESCRIPTION,
410 X_LEVEL10_DESCRIPTION,
411 X_LEVEL11_DESCRIPTION,
412 X_LEVEL12_DESCRIPTION,
413 X_LEVEL13_DESCRIPTION,
414 X_LEVEL14_DESCRIPTION,
415 X_LEVEL15_DESCRIPTION,
416 X_LEVEL16_DESCRIPTION,
417 X_LEVEL17_DESCRIPTION,
418 X_LEVEL18_DESCRIPTION,
419 X_LEVEL19_DESCRIPTION,
420 X_LEVEL20_DESCRIPTION,
421 X_CREATION_DATE,
422 X_CREATED_BY,
423 X_LAST_UPDATE_DATE,
424 X_LAST_UPDATED_BY,
425 X_LAST_UPDATE_LOGIN,
426 L.LANGUAGE_CODE,
427 userenv('LANG')
428 from FND_LANGUAGES L
429 where L.INSTALLED_FLAG in ('I', 'B')
430 and not exists
431 (select NULL
432 from FEM_DIS_USR_DIM9_HIER_TL T
433 where T.OBJECT_ID = X_OBJECT_ID
434 and T.OBJECT_DEFINITION_ID = X_OBJECT_DEFINITION_ID
435 and T.VALUE_SET_ID = X_VALUE_SET_ID
436 and T.LEVEL1_ID = X_LEVEL1_ID
437 and T.LEVEL2_ID = X_LEVEL2_ID
438 and T.LEVEL3_ID = X_LEVEL3_ID
439 and T.LEVEL4_ID = X_LEVEL4_ID
440 and T.LEVEL5_ID = X_LEVEL5_ID
441 and T.LEVEL6_ID = X_LEVEL6_ID
442 and T.LEVEL7_ID = X_LEVEL7_ID
443 and T.LEVEL8_ID = X_LEVEL8_ID
444 and T.LEVEL9_ID = X_LEVEL9_ID
445 and T.LEVEL10_ID = X_LEVEL10_ID
446 and T.LEVEL11_ID = X_LEVEL11_ID
447 and T.LEVEL12_ID = X_LEVEL12_ID
448 and T.LEVEL13_ID = X_LEVEL13_ID
449 and T.LEVEL14_ID = X_LEVEL14_ID
450 and T.LEVEL15_ID = X_LEVEL15_ID
451 and T.LEVEL16_ID = X_LEVEL16_ID
452 and T.LEVEL17_ID = X_LEVEL17_ID
453 and T.LEVEL18_ID = X_LEVEL18_ID
457
454 and T.LEVEL19_ID = X_LEVEL19_ID
455 and T.LEVEL20_ID = X_LEVEL20_ID
456 and T.LANGUAGE = L.LANGUAGE_CODE);
458 open c;
459 fetch c into X_ROWID;
460 if (c%notfound) then
461 close c;
462 raise no_data_found;
463 end if;
464 close c;
465
466 end INSERT_ROW;
467
468 procedure LOCK_ROW (
469 X_OBJECT_ID in NUMBER,
470 X_OBJECT_DEFINITION_ID in NUMBER,
471 X_VALUE_SET_ID in NUMBER,
472 X_LEVEL1_ID in NUMBER,
473 X_LEVEL2_ID in NUMBER,
474 X_LEVEL3_ID in NUMBER,
475 X_LEVEL4_ID in NUMBER,
476 X_LEVEL5_ID in NUMBER,
477 X_LEVEL6_ID in NUMBER,
478 X_LEVEL7_ID in NUMBER,
479 X_LEVEL8_ID in NUMBER,
480 X_LEVEL9_ID in NUMBER,
481 X_LEVEL10_ID in NUMBER,
482 X_LEVEL11_ID in NUMBER,
483 X_LEVEL12_ID in NUMBER,
484 X_LEVEL13_ID in NUMBER,
485 X_LEVEL14_ID in NUMBER,
486 X_LEVEL15_ID in NUMBER,
487 X_LEVEL16_ID in NUMBER,
488 X_LEVEL17_ID in NUMBER,
489 X_LEVEL18_ID in NUMBER,
490 X_LEVEL19_ID in NUMBER,
491 X_LEVEL20_ID in NUMBER,
492 X_LEVEL1_DISPLAY_CODE in VARCHAR2,
493 X_LEVEL2_DISPLAY_CODE in VARCHAR2,
494 X_LEVEL3_DISPLAY_CODE in VARCHAR2,
495 X_LEVEL4_DISPLAY_CODE in VARCHAR2,
496 X_LEVEL5_DISPLAY_CODE in VARCHAR2,
497 X_LEVEL6_DISPLAY_CODE in VARCHAR2,
498 X_LEVEL7_DISPLAY_CODE in VARCHAR2,
499 X_LEVEL8_DISPLAY_CODE in VARCHAR2,
500 X_LEVEL9_DISPLAY_CODE in VARCHAR2,
501 X_LEVEL10_DISPLAY_CODE in VARCHAR2,
502 X_LEVEL11_DISPLAY_CODE in VARCHAR2,
503 X_LEVEL12_DISPLAY_CODE in VARCHAR2,
504 X_LEVEL13_DISPLAY_CODE in VARCHAR2,
505 X_LEVEL14_DISPLAY_CODE in VARCHAR2,
506 X_LEVEL15_DISPLAY_CODE in VARCHAR2,
507 X_LEVEL16_DISPLAY_CODE in VARCHAR2,
508 X_LEVEL17_DISPLAY_CODE in VARCHAR2,
509 X_LEVEL18_DISPLAY_CODE in VARCHAR2,
510 X_LEVEL19_DISPLAY_CODE in VARCHAR2,
511 X_LEVEL20_DISPLAY_CODE in VARCHAR2,
512 X_LEVEL1_DISPLAY_ORDER_NUM in NUMBER,
513 X_LEVEL2_DISPLAY_ORDER_NUM in NUMBER,
514 X_LEVEL3_DISPLAY_ORDER_NUM in NUMBER,
515 X_LEVEL4_DISPLAY_ORDER_NUM in NUMBER,
516 X_LEVEL5_DISPLAY_ORDER_NUM in NUMBER,
517 X_LEVEL6_DISPLAY_ORDER_NUM in NUMBER,
518 X_LEVEL7_DISPLAY_ORDER_NUM in NUMBER,
519 X_LEVEL8_DISPLAY_ORDER_NUM in NUMBER,
520 X_LEVEL9_DISPLAY_ORDER_NUM in NUMBER,
521 X_LEVEL10_DISPLAY_ORDER_NUM in NUMBER,
522 X_LEVEL11_DISPLAY_ORDER_NUM in NUMBER,
523 X_LEVEL12_DISPLAY_ORDER_NUM in NUMBER,
524 X_LEVEL13_DISPLAY_ORDER_NUM in NUMBER,
525 X_LEVEL14_DISPLAY_ORDER_NUM in NUMBER,
526 X_LEVEL15_DISPLAY_ORDER_NUM in NUMBER,
527 X_LEVEL16_DISPLAY_ORDER_NUM in NUMBER,
528 X_LEVEL17_DISPLAY_ORDER_NUM in NUMBER,
529 X_LEVEL18_DISPLAY_ORDER_NUM in NUMBER,
530 X_LEVEL19_DISPLAY_ORDER_NUM in NUMBER,
531 X_LEVEL20_DISPLAY_ORDER_NUM in NUMBER,
532 X_OBJECT_NAME in VARCHAR2,
533 X_OBJECT_DEFINITION_NAME in VARCHAR2,
534 X_LEVEL1_NAME in VARCHAR2,
535 X_LEVEL2_NAME in VARCHAR2,
536 X_LEVEL3_NAME in VARCHAR2,
537 X_LEVEL4_NAME in VARCHAR2,
538 X_LEVEL5_NAME in VARCHAR2,
539 X_LEVEL6_NAME in VARCHAR2,
540 X_LEVEL7_NAME in VARCHAR2,
541 X_LEVEL8_NAME in VARCHAR2,
542 X_LEVEL9_NAME in VARCHAR2,
543 X_LEVEL10_NAME in VARCHAR2,
544 X_LEVEL11_NAME in VARCHAR2,
545 X_LEVEL12_NAME in VARCHAR2,
546 X_LEVEL13_NAME in VARCHAR2,
547 X_LEVEL14_NAME in VARCHAR2,
548 X_LEVEL15_NAME in VARCHAR2,
549 X_LEVEL16_NAME in VARCHAR2,
550 X_LEVEL17_NAME in VARCHAR2,
551 X_LEVEL18_NAME in VARCHAR2,
552 X_LEVEL19_NAME in VARCHAR2,
553 X_LEVEL20_NAME in VARCHAR2,
554 X_LEVEL1_DESCRIPTION in VARCHAR2,
555 X_LEVEL2_DESCRIPTION in VARCHAR2,
556 X_LEVEL3_DESCRIPTION in VARCHAR2,
557 X_LEVEL4_DESCRIPTION in VARCHAR2,
558 X_LEVEL5_DESCRIPTION in VARCHAR2,
559 X_LEVEL6_DESCRIPTION in VARCHAR2,
560 X_LEVEL7_DESCRIPTION in VARCHAR2,
561 X_LEVEL8_DESCRIPTION in VARCHAR2,
562 X_LEVEL9_DESCRIPTION in VARCHAR2,
563 X_LEVEL10_DESCRIPTION in VARCHAR2,
564 X_LEVEL11_DESCRIPTION in VARCHAR2,
565 X_LEVEL12_DESCRIPTION in VARCHAR2,
566 X_LEVEL13_DESCRIPTION in VARCHAR2,
567 X_LEVEL14_DESCRIPTION in VARCHAR2,
568 X_LEVEL15_DESCRIPTION in VARCHAR2,
569 X_LEVEL16_DESCRIPTION in VARCHAR2,
570 X_LEVEL17_DESCRIPTION in VARCHAR2,
571 X_LEVEL18_DESCRIPTION in VARCHAR2,
572 X_LEVEL19_DESCRIPTION in VARCHAR2,
573 X_LEVEL20_DESCRIPTION in VARCHAR2
574 ) is
575 cursor c is select
576 LEVEL1_DISPLAY_CODE,
577 LEVEL2_DISPLAY_CODE,
578 LEVEL3_DISPLAY_CODE,
579 LEVEL4_DISPLAY_CODE,
580 LEVEL5_DISPLAY_CODE,
581 LEVEL6_DISPLAY_CODE,
582 LEVEL7_DISPLAY_CODE,
583 LEVEL8_DISPLAY_CODE,
584 LEVEL9_DISPLAY_CODE,
585 LEVEL10_DISPLAY_CODE,
586 LEVEL11_DISPLAY_CODE,
587 LEVEL12_DISPLAY_CODE,
588 LEVEL13_DISPLAY_CODE,
589 LEVEL14_DISPLAY_CODE,
590 LEVEL15_DISPLAY_CODE,
591 LEVEL16_DISPLAY_CODE,
592 LEVEL17_DISPLAY_CODE,
593 LEVEL18_DISPLAY_CODE,
594 LEVEL19_DISPLAY_CODE,
595 LEVEL20_DISPLAY_CODE,
596 LEVEL1_DISPLAY_ORDER_NUM,
597 LEVEL2_DISPLAY_ORDER_NUM,
598 LEVEL3_DISPLAY_ORDER_NUM,
599 LEVEL4_DISPLAY_ORDER_NUM,
600 LEVEL5_DISPLAY_ORDER_NUM,
601 LEVEL6_DISPLAY_ORDER_NUM,
602 LEVEL7_DISPLAY_ORDER_NUM,
603 LEVEL8_DISPLAY_ORDER_NUM,
604 LEVEL9_DISPLAY_ORDER_NUM,
605 LEVEL10_DISPLAY_ORDER_NUM,
606 LEVEL11_DISPLAY_ORDER_NUM,
607 LEVEL12_DISPLAY_ORDER_NUM,
608 LEVEL13_DISPLAY_ORDER_NUM,
609 LEVEL14_DISPLAY_ORDER_NUM,
610 LEVEL15_DISPLAY_ORDER_NUM,
611 LEVEL16_DISPLAY_ORDER_NUM,
615 LEVEL20_DISPLAY_ORDER_NUM
612 LEVEL17_DISPLAY_ORDER_NUM,
613 LEVEL18_DISPLAY_ORDER_NUM,
614 LEVEL19_DISPLAY_ORDER_NUM,
616 from FEM_DIS_USR_DIM9_HIER_B
617 where OBJECT_ID = X_OBJECT_ID
618 and OBJECT_DEFINITION_ID = X_OBJECT_DEFINITION_ID
619 and VALUE_SET_ID = X_VALUE_SET_ID
620 and LEVEL1_ID = X_LEVEL1_ID
621 and LEVEL2_ID = X_LEVEL2_ID
622 and LEVEL3_ID = X_LEVEL3_ID
623 and LEVEL4_ID = X_LEVEL4_ID
624 and LEVEL5_ID = X_LEVEL5_ID
625 and LEVEL6_ID = X_LEVEL6_ID
626 and LEVEL7_ID = X_LEVEL7_ID
627 and LEVEL8_ID = X_LEVEL8_ID
628 and LEVEL9_ID = X_LEVEL9_ID
629 and LEVEL10_ID = X_LEVEL10_ID
630 and LEVEL11_ID = X_LEVEL11_ID
631 and LEVEL12_ID = X_LEVEL12_ID
632 and LEVEL13_ID = X_LEVEL13_ID
633 and LEVEL14_ID = X_LEVEL14_ID
634 and LEVEL15_ID = X_LEVEL15_ID
635 and LEVEL16_ID = X_LEVEL16_ID
636 and LEVEL17_ID = X_LEVEL17_ID
637 and LEVEL18_ID = X_LEVEL18_ID
638 and LEVEL19_ID = X_LEVEL19_ID
639 and LEVEL20_ID = X_LEVEL20_ID
640 for update of OBJECT_ID nowait;
641 recinfo c%rowtype;
642
643 cursor c1 is select
644 OBJECT_NAME,
645 OBJECT_DEFINITION_NAME,
646 LEVEL1_NAME,
647 LEVEL2_NAME,
648 LEVEL3_NAME,
649 LEVEL4_NAME,
650 LEVEL5_NAME,
651 LEVEL6_NAME,
652 LEVEL7_NAME,
653 LEVEL8_NAME,
654 LEVEL9_NAME,
655 LEVEL10_NAME,
656 LEVEL11_NAME,
657 LEVEL12_NAME,
658 LEVEL13_NAME,
659 LEVEL14_NAME,
660 LEVEL15_NAME,
661 LEVEL16_NAME,
662 LEVEL17_NAME,
663 LEVEL18_NAME,
664 LEVEL19_NAME,
665 LEVEL20_NAME,
666 LEVEL1_DESCRIPTION,
667 LEVEL2_DESCRIPTION,
668 LEVEL3_DESCRIPTION,
669 LEVEL4_DESCRIPTION,
670 LEVEL5_DESCRIPTION,
671 LEVEL6_DESCRIPTION,
672 LEVEL7_DESCRIPTION,
673 LEVEL8_DESCRIPTION,
674 LEVEL9_DESCRIPTION,
675 LEVEL10_DESCRIPTION,
676 LEVEL11_DESCRIPTION,
677 LEVEL12_DESCRIPTION,
678 LEVEL13_DESCRIPTION,
679 LEVEL14_DESCRIPTION,
680 LEVEL15_DESCRIPTION,
681 LEVEL16_DESCRIPTION,
682 LEVEL17_DESCRIPTION,
683 LEVEL18_DESCRIPTION,
684 LEVEL19_DESCRIPTION,
685 LEVEL20_DESCRIPTION,
686 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
687 from FEM_DIS_USR_DIM9_HIER_TL
688 where OBJECT_ID = X_OBJECT_ID
689 and OBJECT_DEFINITION_ID = X_OBJECT_DEFINITION_ID
690 and VALUE_SET_ID = X_VALUE_SET_ID
691 and LEVEL1_ID = X_LEVEL1_ID
692 and LEVEL2_ID = X_LEVEL2_ID
693 and LEVEL3_ID = X_LEVEL3_ID
694 and LEVEL4_ID = X_LEVEL4_ID
695 and LEVEL5_ID = X_LEVEL5_ID
696 and LEVEL6_ID = X_LEVEL6_ID
697 and LEVEL7_ID = X_LEVEL7_ID
698 and LEVEL8_ID = X_LEVEL8_ID
699 and LEVEL9_ID = X_LEVEL9_ID
700 and LEVEL10_ID = X_LEVEL10_ID
701 and LEVEL11_ID = X_LEVEL11_ID
702 and LEVEL12_ID = X_LEVEL12_ID
703 and LEVEL13_ID = X_LEVEL13_ID
704 and LEVEL14_ID = X_LEVEL14_ID
705 and LEVEL15_ID = X_LEVEL15_ID
706 and LEVEL16_ID = X_LEVEL16_ID
707 and LEVEL17_ID = X_LEVEL17_ID
708 and LEVEL18_ID = X_LEVEL18_ID
709 and LEVEL19_ID = X_LEVEL19_ID
710 and LEVEL20_ID = X_LEVEL20_ID
711 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
712 for update of OBJECT_ID nowait;
713 begin
714 open c;
715 fetch c into recinfo;
716 if (c%notfound) then
717 close c;
718 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
719 app_exception.raise_exception;
720 end if;
721 close c;
722 if ( (recinfo.LEVEL1_DISPLAY_CODE = X_LEVEL1_DISPLAY_CODE)
723 AND (recinfo.LEVEL2_DISPLAY_CODE = X_LEVEL2_DISPLAY_CODE)
724 AND (recinfo.LEVEL3_DISPLAY_CODE = X_LEVEL3_DISPLAY_CODE)
725 AND (recinfo.LEVEL4_DISPLAY_CODE = X_LEVEL4_DISPLAY_CODE)
726 AND (recinfo.LEVEL5_DISPLAY_CODE = X_LEVEL5_DISPLAY_CODE)
727 AND (recinfo.LEVEL6_DISPLAY_CODE = X_LEVEL6_DISPLAY_CODE)
728 AND (recinfo.LEVEL7_DISPLAY_CODE = X_LEVEL7_DISPLAY_CODE)
729 AND (recinfo.LEVEL8_DISPLAY_CODE = X_LEVEL8_DISPLAY_CODE)
730 AND (recinfo.LEVEL9_DISPLAY_CODE = X_LEVEL9_DISPLAY_CODE)
731 AND (recinfo.LEVEL10_DISPLAY_CODE = X_LEVEL10_DISPLAY_CODE)
732 AND (recinfo.LEVEL11_DISPLAY_CODE = X_LEVEL11_DISPLAY_CODE)
733 AND (recinfo.LEVEL12_DISPLAY_CODE = X_LEVEL12_DISPLAY_CODE)
734 AND (recinfo.LEVEL13_DISPLAY_CODE = X_LEVEL13_DISPLAY_CODE)
735 AND (recinfo.LEVEL14_DISPLAY_CODE = X_LEVEL14_DISPLAY_CODE)
736 AND (recinfo.LEVEL15_DISPLAY_CODE = X_LEVEL15_DISPLAY_CODE)
737 AND (recinfo.LEVEL16_DISPLAY_CODE = X_LEVEL16_DISPLAY_CODE)
738 AND (recinfo.LEVEL17_DISPLAY_CODE = X_LEVEL17_DISPLAY_CODE)
739 AND (recinfo.LEVEL18_DISPLAY_CODE = X_LEVEL18_DISPLAY_CODE)
740 AND (recinfo.LEVEL19_DISPLAY_CODE = X_LEVEL19_DISPLAY_CODE)
741 AND (recinfo.LEVEL20_DISPLAY_CODE = X_LEVEL20_DISPLAY_CODE)
742 AND (recinfo.LEVEL1_DISPLAY_ORDER_NUM = X_LEVEL1_DISPLAY_ORDER_NUM)
743 AND (recinfo.LEVEL2_DISPLAY_ORDER_NUM = X_LEVEL2_DISPLAY_ORDER_NUM)
744 AND (recinfo.LEVEL3_DISPLAY_ORDER_NUM = X_LEVEL3_DISPLAY_ORDER_NUM)
745 AND (recinfo.LEVEL4_DISPLAY_ORDER_NUM = X_LEVEL4_DISPLAY_ORDER_NUM)
746 AND (recinfo.LEVEL5_DISPLAY_ORDER_NUM = X_LEVEL5_DISPLAY_ORDER_NUM)
747 AND (recinfo.LEVEL6_DISPLAY_ORDER_NUM = X_LEVEL6_DISPLAY_ORDER_NUM)
748 AND (recinfo.LEVEL7_DISPLAY_ORDER_NUM = X_LEVEL7_DISPLAY_ORDER_NUM)
749 AND (recinfo.LEVEL8_DISPLAY_ORDER_NUM = X_LEVEL8_DISPLAY_ORDER_NUM)
750 AND (recinfo.LEVEL9_DISPLAY_ORDER_NUM = X_LEVEL9_DISPLAY_ORDER_NUM)
754 AND (recinfo.LEVEL13_DISPLAY_ORDER_NUM = X_LEVEL13_DISPLAY_ORDER_NUM)
751 AND (recinfo.LEVEL10_DISPLAY_ORDER_NUM = X_LEVEL10_DISPLAY_ORDER_NUM)
752 AND (recinfo.LEVEL11_DISPLAY_ORDER_NUM = X_LEVEL11_DISPLAY_ORDER_NUM)
753 AND (recinfo.LEVEL12_DISPLAY_ORDER_NUM = X_LEVEL12_DISPLAY_ORDER_NUM)
755 AND (recinfo.LEVEL14_DISPLAY_ORDER_NUM = X_LEVEL14_DISPLAY_ORDER_NUM)
756 AND (recinfo.LEVEL15_DISPLAY_ORDER_NUM = X_LEVEL15_DISPLAY_ORDER_NUM)
757 AND (recinfo.LEVEL16_DISPLAY_ORDER_NUM = X_LEVEL16_DISPLAY_ORDER_NUM)
758 AND (recinfo.LEVEL17_DISPLAY_ORDER_NUM = X_LEVEL17_DISPLAY_ORDER_NUM)
759 AND (recinfo.LEVEL18_DISPLAY_ORDER_NUM = X_LEVEL18_DISPLAY_ORDER_NUM)
760 AND (recinfo.LEVEL19_DISPLAY_ORDER_NUM = X_LEVEL19_DISPLAY_ORDER_NUM)
761 AND (recinfo.LEVEL20_DISPLAY_ORDER_NUM = X_LEVEL20_DISPLAY_ORDER_NUM)
762 ) then
763 null;
764 else
765 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
766 app_exception.raise_exception;
767 end if;
768
769 for tlinfo in c1 loop
770 if (tlinfo.BASELANG = 'Y') then
771 if ( (tlinfo.OBJECT_NAME = X_OBJECT_NAME)
772 AND (tlinfo.OBJECT_DEFINITION_NAME = X_OBJECT_DEFINITION_NAME)
773 AND (tlinfo.LEVEL1_NAME = X_LEVEL1_NAME)
774 AND (tlinfo.LEVEL2_NAME = X_LEVEL2_NAME)
775 AND (tlinfo.LEVEL3_NAME = X_LEVEL3_NAME)
776 AND (tlinfo.LEVEL4_NAME = X_LEVEL4_NAME)
777 AND (tlinfo.LEVEL5_NAME = X_LEVEL5_NAME)
778 AND (tlinfo.LEVEL6_NAME = X_LEVEL6_NAME)
779 AND (tlinfo.LEVEL7_NAME = X_LEVEL7_NAME)
780 AND (tlinfo.LEVEL8_NAME = X_LEVEL8_NAME)
781 AND (tlinfo.LEVEL9_NAME = X_LEVEL9_NAME)
782 AND (tlinfo.LEVEL10_NAME = X_LEVEL10_NAME)
783 AND (tlinfo.LEVEL11_NAME = X_LEVEL11_NAME)
784 AND (tlinfo.LEVEL12_NAME = X_LEVEL12_NAME)
785 AND (tlinfo.LEVEL13_NAME = X_LEVEL13_NAME)
786 AND (tlinfo.LEVEL14_NAME = X_LEVEL14_NAME)
787 AND (tlinfo.LEVEL15_NAME = X_LEVEL15_NAME)
788 AND (tlinfo.LEVEL16_NAME = X_LEVEL16_NAME)
789 AND (tlinfo.LEVEL17_NAME = X_LEVEL17_NAME)
790 AND (tlinfo.LEVEL18_NAME = X_LEVEL18_NAME)
791 AND (tlinfo.LEVEL19_NAME = X_LEVEL19_NAME)
792 AND (tlinfo.LEVEL20_NAME = X_LEVEL20_NAME)
793 AND ((tlinfo.LEVEL1_DESCRIPTION = X_LEVEL1_DESCRIPTION)
794 OR ((tlinfo.LEVEL1_DESCRIPTION is null) AND (X_LEVEL1_DESCRIPTION is null)))
795 AND ((tlinfo.LEVEL2_DESCRIPTION = X_LEVEL2_DESCRIPTION)
796 OR ((tlinfo.LEVEL2_DESCRIPTION is null) AND (X_LEVEL2_DESCRIPTION is null)))
797 AND ((tlinfo.LEVEL3_DESCRIPTION = X_LEVEL3_DESCRIPTION)
798 OR ((tlinfo.LEVEL3_DESCRIPTION is null) AND (X_LEVEL3_DESCRIPTION is null)))
799 AND ((tlinfo.LEVEL4_DESCRIPTION = X_LEVEL4_DESCRIPTION)
800 OR ((tlinfo.LEVEL4_DESCRIPTION is null) AND (X_LEVEL4_DESCRIPTION is null)))
801 AND ((tlinfo.LEVEL5_DESCRIPTION = X_LEVEL5_DESCRIPTION)
802 OR ((tlinfo.LEVEL5_DESCRIPTION is null) AND (X_LEVEL5_DESCRIPTION is null)))
803 AND ((tlinfo.LEVEL6_DESCRIPTION = X_LEVEL6_DESCRIPTION)
804 OR ((tlinfo.LEVEL6_DESCRIPTION is null) AND (X_LEVEL6_DESCRIPTION is null)))
805 AND ((tlinfo.LEVEL7_DESCRIPTION = X_LEVEL7_DESCRIPTION)
806 OR ((tlinfo.LEVEL7_DESCRIPTION is null) AND (X_LEVEL7_DESCRIPTION is null)))
807 AND ((tlinfo.LEVEL8_DESCRIPTION = X_LEVEL8_DESCRIPTION)
808 OR ((tlinfo.LEVEL8_DESCRIPTION is null) AND (X_LEVEL8_DESCRIPTION is null)))
809 AND ((tlinfo.LEVEL9_DESCRIPTION = X_LEVEL9_DESCRIPTION)
810 OR ((tlinfo.LEVEL9_DESCRIPTION is null) AND (X_LEVEL9_DESCRIPTION is null)))
811 AND ((tlinfo.LEVEL10_DESCRIPTION = X_LEVEL10_DESCRIPTION)
812 OR ((tlinfo.LEVEL10_DESCRIPTION is null) AND (X_LEVEL10_DESCRIPTION is null)))
813 AND ((tlinfo.LEVEL11_DESCRIPTION = X_LEVEL11_DESCRIPTION)
814 OR ((tlinfo.LEVEL11_DESCRIPTION is null) AND (X_LEVEL11_DESCRIPTION is null)))
815 AND ((tlinfo.LEVEL12_DESCRIPTION = X_LEVEL12_DESCRIPTION)
816 OR ((tlinfo.LEVEL12_DESCRIPTION is null) AND (X_LEVEL12_DESCRIPTION is null)))
817 AND ((tlinfo.LEVEL13_DESCRIPTION = X_LEVEL13_DESCRIPTION)
818 OR ((tlinfo.LEVEL13_DESCRIPTION is null) AND (X_LEVEL13_DESCRIPTION is null)))
819 AND ((tlinfo.LEVEL14_DESCRIPTION = X_LEVEL14_DESCRIPTION)
820 OR ((tlinfo.LEVEL14_DESCRIPTION is null) AND (X_LEVEL14_DESCRIPTION is null)))
821 AND ((tlinfo.LEVEL15_DESCRIPTION = X_LEVEL15_DESCRIPTION)
822 OR ((tlinfo.LEVEL15_DESCRIPTION is null) AND (X_LEVEL15_DESCRIPTION is null)))
823 AND ((tlinfo.LEVEL16_DESCRIPTION = X_LEVEL16_DESCRIPTION)
824 OR ((tlinfo.LEVEL16_DESCRIPTION is null) AND (X_LEVEL16_DESCRIPTION is null)))
825 AND ((tlinfo.LEVEL17_DESCRIPTION = X_LEVEL17_DESCRIPTION)
826 OR ((tlinfo.LEVEL17_DESCRIPTION is null) AND (X_LEVEL17_DESCRIPTION is null)))
827 AND ((tlinfo.LEVEL18_DESCRIPTION = X_LEVEL18_DESCRIPTION)
828 OR ((tlinfo.LEVEL18_DESCRIPTION is null) AND (X_LEVEL18_DESCRIPTION is null)))
829 AND ((tlinfo.LEVEL19_DESCRIPTION = X_LEVEL19_DESCRIPTION)
830 OR ((tlinfo.LEVEL19_DESCRIPTION is null) AND (X_LEVEL19_DESCRIPTION is null)))
831 AND ((tlinfo.LEVEL20_DESCRIPTION = X_LEVEL20_DESCRIPTION)
832 OR ((tlinfo.LEVEL20_DESCRIPTION is null) AND (X_LEVEL20_DESCRIPTION is null)))
833 ) then
834 null;
835 else
836 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
837 app_exception.raise_exception;
838 end if;
839 end if;
840 end loop;
841 return;
842 end LOCK_ROW;
843
844 procedure UPDATE_ROW (
845 X_OBJECT_ID in NUMBER,
846 X_OBJECT_DEFINITION_ID in NUMBER,
847 X_VALUE_SET_ID in NUMBER,
848 X_LEVEL1_ID in NUMBER,
849 X_LEVEL2_ID in NUMBER,
850 X_LEVEL3_ID in NUMBER,
854 X_LEVEL7_ID in NUMBER,
851 X_LEVEL4_ID in NUMBER,
852 X_LEVEL5_ID in NUMBER,
853 X_LEVEL6_ID in NUMBER,
855 X_LEVEL8_ID in NUMBER,
856 X_LEVEL9_ID in NUMBER,
857 X_LEVEL10_ID in NUMBER,
858 X_LEVEL11_ID in NUMBER,
859 X_LEVEL12_ID in NUMBER,
860 X_LEVEL13_ID in NUMBER,
861 X_LEVEL14_ID in NUMBER,
862 X_LEVEL15_ID in NUMBER,
863 X_LEVEL16_ID in NUMBER,
864 X_LEVEL17_ID in NUMBER,
865 X_LEVEL18_ID in NUMBER,
866 X_LEVEL19_ID in NUMBER,
867 X_LEVEL20_ID in NUMBER,
868 X_LEVEL1_DISPLAY_CODE in VARCHAR2,
869 X_LEVEL2_DISPLAY_CODE in VARCHAR2,
870 X_LEVEL3_DISPLAY_CODE in VARCHAR2,
871 X_LEVEL4_DISPLAY_CODE in VARCHAR2,
872 X_LEVEL5_DISPLAY_CODE in VARCHAR2,
873 X_LEVEL6_DISPLAY_CODE in VARCHAR2,
874 X_LEVEL7_DISPLAY_CODE in VARCHAR2,
875 X_LEVEL8_DISPLAY_CODE in VARCHAR2,
876 X_LEVEL9_DISPLAY_CODE in VARCHAR2,
877 X_LEVEL10_DISPLAY_CODE in VARCHAR2,
878 X_LEVEL11_DISPLAY_CODE in VARCHAR2,
879 X_LEVEL12_DISPLAY_CODE in VARCHAR2,
880 X_LEVEL13_DISPLAY_CODE in VARCHAR2,
881 X_LEVEL14_DISPLAY_CODE in VARCHAR2,
882 X_LEVEL15_DISPLAY_CODE in VARCHAR2,
883 X_LEVEL16_DISPLAY_CODE in VARCHAR2,
884 X_LEVEL17_DISPLAY_CODE in VARCHAR2,
885 X_LEVEL18_DISPLAY_CODE in VARCHAR2,
886 X_LEVEL19_DISPLAY_CODE in VARCHAR2,
887 X_LEVEL20_DISPLAY_CODE in VARCHAR2,
888 X_LEVEL1_DISPLAY_ORDER_NUM in NUMBER,
889 X_LEVEL2_DISPLAY_ORDER_NUM in NUMBER,
890 X_LEVEL3_DISPLAY_ORDER_NUM in NUMBER,
891 X_LEVEL4_DISPLAY_ORDER_NUM in NUMBER,
892 X_LEVEL5_DISPLAY_ORDER_NUM in NUMBER,
893 X_LEVEL6_DISPLAY_ORDER_NUM in NUMBER,
894 X_LEVEL7_DISPLAY_ORDER_NUM in NUMBER,
895 X_LEVEL8_DISPLAY_ORDER_NUM in NUMBER,
896 X_LEVEL9_DISPLAY_ORDER_NUM in NUMBER,
897 X_LEVEL10_DISPLAY_ORDER_NUM in NUMBER,
898 X_LEVEL11_DISPLAY_ORDER_NUM in NUMBER,
899 X_LEVEL12_DISPLAY_ORDER_NUM in NUMBER,
900 X_LEVEL13_DISPLAY_ORDER_NUM in NUMBER,
901 X_LEVEL14_DISPLAY_ORDER_NUM in NUMBER,
902 X_LEVEL15_DISPLAY_ORDER_NUM in NUMBER,
903 X_LEVEL16_DISPLAY_ORDER_NUM in NUMBER,
904 X_LEVEL17_DISPLAY_ORDER_NUM in NUMBER,
905 X_LEVEL18_DISPLAY_ORDER_NUM in NUMBER,
906 X_LEVEL19_DISPLAY_ORDER_NUM in NUMBER,
907 X_LEVEL20_DISPLAY_ORDER_NUM in NUMBER,
908 X_OBJECT_NAME in VARCHAR2,
909 X_OBJECT_DEFINITION_NAME in VARCHAR2,
910 X_LEVEL1_NAME in VARCHAR2,
911 X_LEVEL2_NAME in VARCHAR2,
912 X_LEVEL3_NAME in VARCHAR2,
913 X_LEVEL4_NAME in VARCHAR2,
914 X_LEVEL5_NAME in VARCHAR2,
915 X_LEVEL6_NAME in VARCHAR2,
916 X_LEVEL7_NAME in VARCHAR2,
917 X_LEVEL8_NAME in VARCHAR2,
918 X_LEVEL9_NAME in VARCHAR2,
919 X_LEVEL10_NAME in VARCHAR2,
920 X_LEVEL11_NAME in VARCHAR2,
921 X_LEVEL12_NAME in VARCHAR2,
922 X_LEVEL13_NAME in VARCHAR2,
923 X_LEVEL14_NAME in VARCHAR2,
924 X_LEVEL15_NAME in VARCHAR2,
925 X_LEVEL16_NAME in VARCHAR2,
926 X_LEVEL17_NAME in VARCHAR2,
927 X_LEVEL18_NAME in VARCHAR2,
928 X_LEVEL19_NAME in VARCHAR2,
929 X_LEVEL20_NAME in VARCHAR2,
930 X_LEVEL1_DESCRIPTION in VARCHAR2,
931 X_LEVEL2_DESCRIPTION in VARCHAR2,
932 X_LEVEL3_DESCRIPTION in VARCHAR2,
933 X_LEVEL4_DESCRIPTION in VARCHAR2,
934 X_LEVEL5_DESCRIPTION in VARCHAR2,
935 X_LEVEL6_DESCRIPTION in VARCHAR2,
936 X_LEVEL7_DESCRIPTION in VARCHAR2,
937 X_LEVEL8_DESCRIPTION in VARCHAR2,
938 X_LEVEL9_DESCRIPTION in VARCHAR2,
939 X_LEVEL10_DESCRIPTION in VARCHAR2,
940 X_LEVEL11_DESCRIPTION in VARCHAR2,
941 X_LEVEL12_DESCRIPTION in VARCHAR2,
942 X_LEVEL13_DESCRIPTION in VARCHAR2,
943 X_LEVEL14_DESCRIPTION in VARCHAR2,
944 X_LEVEL15_DESCRIPTION in VARCHAR2,
945 X_LEVEL16_DESCRIPTION in VARCHAR2,
946 X_LEVEL17_DESCRIPTION in VARCHAR2,
947 X_LEVEL18_DESCRIPTION in VARCHAR2,
948 X_LEVEL19_DESCRIPTION in VARCHAR2,
949 X_LEVEL20_DESCRIPTION in VARCHAR2,
950 X_LAST_UPDATE_DATE in DATE,
951 X_LAST_UPDATED_BY in NUMBER,
952 X_LAST_UPDATE_LOGIN in NUMBER
953 ) is
954 begin
955 update FEM_DIS_USR_DIM9_HIER_B set
956 LEVEL1_DISPLAY_CODE = X_LEVEL1_DISPLAY_CODE,
957 LEVEL2_DISPLAY_CODE = X_LEVEL2_DISPLAY_CODE,
958 LEVEL3_DISPLAY_CODE = X_LEVEL3_DISPLAY_CODE,
959 LEVEL4_DISPLAY_CODE = X_LEVEL4_DISPLAY_CODE,
960 LEVEL5_DISPLAY_CODE = X_LEVEL5_DISPLAY_CODE,
961 LEVEL6_DISPLAY_CODE = X_LEVEL6_DISPLAY_CODE,
962 LEVEL7_DISPLAY_CODE = X_LEVEL7_DISPLAY_CODE,
963 LEVEL8_DISPLAY_CODE = X_LEVEL8_DISPLAY_CODE,
964 LEVEL9_DISPLAY_CODE = X_LEVEL9_DISPLAY_CODE,
965 LEVEL10_DISPLAY_CODE = X_LEVEL10_DISPLAY_CODE,
966 LEVEL11_DISPLAY_CODE = X_LEVEL11_DISPLAY_CODE,
967 LEVEL12_DISPLAY_CODE = X_LEVEL12_DISPLAY_CODE,
968 LEVEL13_DISPLAY_CODE = X_LEVEL13_DISPLAY_CODE,
969 LEVEL14_DISPLAY_CODE = X_LEVEL14_DISPLAY_CODE,
970 LEVEL15_DISPLAY_CODE = X_LEVEL15_DISPLAY_CODE,
971 LEVEL16_DISPLAY_CODE = X_LEVEL16_DISPLAY_CODE,
972 LEVEL17_DISPLAY_CODE = X_LEVEL17_DISPLAY_CODE,
973 LEVEL18_DISPLAY_CODE = X_LEVEL18_DISPLAY_CODE,
974 LEVEL19_DISPLAY_CODE = X_LEVEL19_DISPLAY_CODE,
975 LEVEL20_DISPLAY_CODE = X_LEVEL20_DISPLAY_CODE,
976 LEVEL1_DISPLAY_ORDER_NUM = X_LEVEL1_DISPLAY_ORDER_NUM,
977 LEVEL2_DISPLAY_ORDER_NUM = X_LEVEL2_DISPLAY_ORDER_NUM,
978 LEVEL3_DISPLAY_ORDER_NUM = X_LEVEL3_DISPLAY_ORDER_NUM,
979 LEVEL4_DISPLAY_ORDER_NUM = X_LEVEL4_DISPLAY_ORDER_NUM,
980 LEVEL5_DISPLAY_ORDER_NUM = X_LEVEL5_DISPLAY_ORDER_NUM,
981 LEVEL6_DISPLAY_ORDER_NUM = X_LEVEL6_DISPLAY_ORDER_NUM,
982 LEVEL7_DISPLAY_ORDER_NUM = X_LEVEL7_DISPLAY_ORDER_NUM,
983 LEVEL8_DISPLAY_ORDER_NUM = X_LEVEL8_DISPLAY_ORDER_NUM,
984 LEVEL9_DISPLAY_ORDER_NUM = X_LEVEL9_DISPLAY_ORDER_NUM,
985 LEVEL10_DISPLAY_ORDER_NUM = X_LEVEL10_DISPLAY_ORDER_NUM,
986 LEVEL11_DISPLAY_ORDER_NUM = X_LEVEL11_DISPLAY_ORDER_NUM,
990 LEVEL15_DISPLAY_ORDER_NUM = X_LEVEL15_DISPLAY_ORDER_NUM,
987 LEVEL12_DISPLAY_ORDER_NUM = X_LEVEL12_DISPLAY_ORDER_NUM,
988 LEVEL13_DISPLAY_ORDER_NUM = X_LEVEL13_DISPLAY_ORDER_NUM,
989 LEVEL14_DISPLAY_ORDER_NUM = X_LEVEL14_DISPLAY_ORDER_NUM,
991 LEVEL16_DISPLAY_ORDER_NUM = X_LEVEL16_DISPLAY_ORDER_NUM,
992 LEVEL17_DISPLAY_ORDER_NUM = X_LEVEL17_DISPLAY_ORDER_NUM,
993 LEVEL18_DISPLAY_ORDER_NUM = X_LEVEL18_DISPLAY_ORDER_NUM,
994 LEVEL19_DISPLAY_ORDER_NUM = X_LEVEL19_DISPLAY_ORDER_NUM,
995 LEVEL20_DISPLAY_ORDER_NUM = X_LEVEL20_DISPLAY_ORDER_NUM,
996 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
997 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
998 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
999 where OBJECT_ID = X_OBJECT_ID
1000 and OBJECT_DEFINITION_ID = X_OBJECT_DEFINITION_ID
1001 and VALUE_SET_ID = X_VALUE_SET_ID
1002 and LEVEL1_ID = X_LEVEL1_ID
1003 and LEVEL2_ID = X_LEVEL2_ID
1004 and LEVEL3_ID = X_LEVEL3_ID
1005 and LEVEL4_ID = X_LEVEL4_ID
1006 and LEVEL5_ID = X_LEVEL5_ID
1007 and LEVEL6_ID = X_LEVEL6_ID
1008 and LEVEL7_ID = X_LEVEL7_ID
1009 and LEVEL8_ID = X_LEVEL8_ID
1010 and LEVEL9_ID = X_LEVEL9_ID
1011 and LEVEL10_ID = X_LEVEL10_ID
1012 and LEVEL11_ID = X_LEVEL11_ID
1013 and LEVEL12_ID = X_LEVEL12_ID
1014 and LEVEL13_ID = X_LEVEL13_ID
1015 and LEVEL14_ID = X_LEVEL14_ID
1016 and LEVEL15_ID = X_LEVEL15_ID
1017 and LEVEL16_ID = X_LEVEL16_ID
1018 and LEVEL17_ID = X_LEVEL17_ID
1019 and LEVEL18_ID = X_LEVEL18_ID
1020 and LEVEL19_ID = X_LEVEL19_ID
1021 and LEVEL20_ID = X_LEVEL20_ID;
1022
1023 if (sql%notfound) then
1024 raise no_data_found;
1025 end if;
1026
1027 update FEM_DIS_USR_DIM9_HIER_TL set
1028 OBJECT_NAME = X_OBJECT_NAME,
1029 OBJECT_DEFINITION_NAME = X_OBJECT_DEFINITION_NAME,
1030 LEVEL1_NAME = X_LEVEL1_NAME,
1031 LEVEL2_NAME = X_LEVEL2_NAME,
1032 LEVEL3_NAME = X_LEVEL3_NAME,
1033 LEVEL4_NAME = X_LEVEL4_NAME,
1034 LEVEL5_NAME = X_LEVEL5_NAME,
1035 LEVEL6_NAME = X_LEVEL6_NAME,
1036 LEVEL7_NAME = X_LEVEL7_NAME,
1037 LEVEL8_NAME = X_LEVEL8_NAME,
1038 LEVEL9_NAME = X_LEVEL9_NAME,
1039 LEVEL10_NAME = X_LEVEL10_NAME,
1040 LEVEL11_NAME = X_LEVEL11_NAME,
1041 LEVEL12_NAME = X_LEVEL12_NAME,
1042 LEVEL13_NAME = X_LEVEL13_NAME,
1043 LEVEL14_NAME = X_LEVEL14_NAME,
1044 LEVEL15_NAME = X_LEVEL15_NAME,
1045 LEVEL16_NAME = X_LEVEL16_NAME,
1046 LEVEL17_NAME = X_LEVEL17_NAME,
1047 LEVEL18_NAME = X_LEVEL18_NAME,
1048 LEVEL19_NAME = X_LEVEL19_NAME,
1049 LEVEL20_NAME = X_LEVEL20_NAME,
1050 LEVEL1_DESCRIPTION = X_LEVEL1_DESCRIPTION,
1051 LEVEL2_DESCRIPTION = X_LEVEL2_DESCRIPTION,
1052 LEVEL3_DESCRIPTION = X_LEVEL3_DESCRIPTION,
1053 LEVEL4_DESCRIPTION = X_LEVEL4_DESCRIPTION,
1054 LEVEL5_DESCRIPTION = X_LEVEL5_DESCRIPTION,
1055 LEVEL6_DESCRIPTION = X_LEVEL6_DESCRIPTION,
1056 LEVEL7_DESCRIPTION = X_LEVEL7_DESCRIPTION,
1057 LEVEL8_DESCRIPTION = X_LEVEL8_DESCRIPTION,
1058 LEVEL9_DESCRIPTION = X_LEVEL9_DESCRIPTION,
1059 LEVEL10_DESCRIPTION = X_LEVEL10_DESCRIPTION,
1060 LEVEL11_DESCRIPTION = X_LEVEL11_DESCRIPTION,
1061 LEVEL12_DESCRIPTION = X_LEVEL12_DESCRIPTION,
1062 LEVEL13_DESCRIPTION = X_LEVEL13_DESCRIPTION,
1063 LEVEL14_DESCRIPTION = X_LEVEL14_DESCRIPTION,
1064 LEVEL15_DESCRIPTION = X_LEVEL15_DESCRIPTION,
1065 LEVEL16_DESCRIPTION = X_LEVEL16_DESCRIPTION,
1066 LEVEL17_DESCRIPTION = X_LEVEL17_DESCRIPTION,
1067 LEVEL18_DESCRIPTION = X_LEVEL18_DESCRIPTION,
1068 LEVEL19_DESCRIPTION = X_LEVEL19_DESCRIPTION,
1069 LEVEL20_DESCRIPTION = X_LEVEL20_DESCRIPTION,
1070 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1071 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1072 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1073 SOURCE_LANG = userenv('LANG')
1074 where OBJECT_ID = X_OBJECT_ID
1075 and OBJECT_DEFINITION_ID = X_OBJECT_DEFINITION_ID
1076 and VALUE_SET_ID = X_VALUE_SET_ID
1077 and LEVEL1_ID = X_LEVEL1_ID
1078 and LEVEL2_ID = X_LEVEL2_ID
1079 and LEVEL3_ID = X_LEVEL3_ID
1080 and LEVEL4_ID = X_LEVEL4_ID
1081 and LEVEL5_ID = X_LEVEL5_ID
1082 and LEVEL6_ID = X_LEVEL6_ID
1083 and LEVEL7_ID = X_LEVEL7_ID
1084 and LEVEL8_ID = X_LEVEL8_ID
1085 and LEVEL9_ID = X_LEVEL9_ID
1086 and LEVEL10_ID = X_LEVEL10_ID
1087 and LEVEL11_ID = X_LEVEL11_ID
1088 and LEVEL12_ID = X_LEVEL12_ID
1089 and LEVEL13_ID = X_LEVEL13_ID
1090 and LEVEL14_ID = X_LEVEL14_ID
1091 and LEVEL15_ID = X_LEVEL15_ID
1092 and LEVEL16_ID = X_LEVEL16_ID
1093 and LEVEL17_ID = X_LEVEL17_ID
1094 and LEVEL18_ID = X_LEVEL18_ID
1095 and LEVEL19_ID = X_LEVEL19_ID
1096 and LEVEL20_ID = X_LEVEL20_ID
1097 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1098
1099 if (sql%notfound) then
1100 raise no_data_found;
1101 end if;
1102 end UPDATE_ROW;
1103
1104 procedure DELETE_ROW (
1105 X_OBJECT_ID in NUMBER,
1106 X_OBJECT_DEFINITION_ID in NUMBER,
1107 X_VALUE_SET_ID in NUMBER,
1108 X_LEVEL1_ID in NUMBER,
1109 X_LEVEL2_ID in NUMBER,
1110 X_LEVEL3_ID in NUMBER,
1111 X_LEVEL4_ID in NUMBER,
1112 X_LEVEL5_ID in NUMBER,
1113 X_LEVEL6_ID in NUMBER,
1114 X_LEVEL7_ID in NUMBER,
1115 X_LEVEL8_ID in NUMBER,
1116 X_LEVEL9_ID in NUMBER,
1117 X_LEVEL10_ID in NUMBER,
1118 X_LEVEL11_ID in NUMBER,
1119 X_LEVEL12_ID in NUMBER,
1120 X_LEVEL13_ID in NUMBER,
1121 X_LEVEL14_ID in NUMBER,
1122 X_LEVEL15_ID in NUMBER,
1123 X_LEVEL16_ID in NUMBER,
1124 X_LEVEL17_ID in NUMBER,
1125 X_LEVEL18_ID in NUMBER,
1126 X_LEVEL19_ID in NUMBER,
1127 X_LEVEL20_ID in NUMBER
1128 ) is
1129 begin
1130 delete from FEM_DIS_USR_DIM9_HIER_TL
1131 where OBJECT_ID = X_OBJECT_ID
1132 and OBJECT_DEFINITION_ID = X_OBJECT_DEFINITION_ID
1133 and VALUE_SET_ID = X_VALUE_SET_ID
1137 and LEVEL4_ID = X_LEVEL4_ID
1134 and LEVEL1_ID = X_LEVEL1_ID
1135 and LEVEL2_ID = X_LEVEL2_ID
1136 and LEVEL3_ID = X_LEVEL3_ID
1138 and LEVEL5_ID = X_LEVEL5_ID
1139 and LEVEL6_ID = X_LEVEL6_ID
1140 and LEVEL7_ID = X_LEVEL7_ID
1141 and LEVEL8_ID = X_LEVEL8_ID
1142 and LEVEL9_ID = X_LEVEL9_ID
1143 and LEVEL10_ID = X_LEVEL10_ID
1144 and LEVEL11_ID = X_LEVEL11_ID
1145 and LEVEL12_ID = X_LEVEL12_ID
1146 and LEVEL13_ID = X_LEVEL13_ID
1147 and LEVEL14_ID = X_LEVEL14_ID
1148 and LEVEL15_ID = X_LEVEL15_ID
1149 and LEVEL16_ID = X_LEVEL16_ID
1150 and LEVEL17_ID = X_LEVEL17_ID
1151 and LEVEL18_ID = X_LEVEL18_ID
1152 and LEVEL19_ID = X_LEVEL19_ID
1153 and LEVEL20_ID = X_LEVEL20_ID;
1154
1155 if (sql%notfound) then
1156 raise no_data_found;
1157 end if;
1158
1159 delete from FEM_DIS_USR_DIM9_HIER_B
1160 where OBJECT_ID = X_OBJECT_ID
1161 and OBJECT_DEFINITION_ID = X_OBJECT_DEFINITION_ID
1162 and VALUE_SET_ID = X_VALUE_SET_ID
1163 and LEVEL1_ID = X_LEVEL1_ID
1164 and LEVEL2_ID = X_LEVEL2_ID
1165 and LEVEL3_ID = X_LEVEL3_ID
1166 and LEVEL4_ID = X_LEVEL4_ID
1167 and LEVEL5_ID = X_LEVEL5_ID
1168 and LEVEL6_ID = X_LEVEL6_ID
1169 and LEVEL7_ID = X_LEVEL7_ID
1170 and LEVEL8_ID = X_LEVEL8_ID
1171 and LEVEL9_ID = X_LEVEL9_ID
1172 and LEVEL10_ID = X_LEVEL10_ID
1173 and LEVEL11_ID = X_LEVEL11_ID
1174 and LEVEL12_ID = X_LEVEL12_ID
1175 and LEVEL13_ID = X_LEVEL13_ID
1176 and LEVEL14_ID = X_LEVEL14_ID
1177 and LEVEL15_ID = X_LEVEL15_ID
1178 and LEVEL16_ID = X_LEVEL16_ID
1179 and LEVEL17_ID = X_LEVEL17_ID
1180 and LEVEL18_ID = X_LEVEL18_ID
1181 and LEVEL19_ID = X_LEVEL19_ID
1182 and LEVEL20_ID = X_LEVEL20_ID;
1183
1184 if (sql%notfound) then
1185 raise no_data_found;
1186 end if;
1187 end DELETE_ROW;
1188
1189 procedure ADD_LANGUAGE
1190 is
1191 begin
1192 delete from FEM_DIS_USR_DIM9_HIER_TL T
1193 where not exists
1194 (select NULL
1195 from FEM_DIS_USR_DIM9_HIER_B B
1196 where B.OBJECT_ID = T.OBJECT_ID
1197 and B.OBJECT_DEFINITION_ID = T.OBJECT_DEFINITION_ID
1198 and B.VALUE_SET_ID = T.VALUE_SET_ID
1199 and B.LEVEL1_ID = T.LEVEL1_ID
1200 and B.LEVEL2_ID = T.LEVEL2_ID
1201 and B.LEVEL3_ID = T.LEVEL3_ID
1202 and B.LEVEL4_ID = T.LEVEL4_ID
1203 and B.LEVEL5_ID = T.LEVEL5_ID
1204 and B.LEVEL6_ID = T.LEVEL6_ID
1205 and B.LEVEL7_ID = T.LEVEL7_ID
1206 and B.LEVEL8_ID = T.LEVEL8_ID
1207 and B.LEVEL9_ID = T.LEVEL9_ID
1208 and B.LEVEL10_ID = T.LEVEL10_ID
1209 and B.LEVEL11_ID = T.LEVEL11_ID
1210 and B.LEVEL12_ID = T.LEVEL12_ID
1211 and B.LEVEL13_ID = T.LEVEL13_ID
1212 and B.LEVEL14_ID = T.LEVEL14_ID
1213 and B.LEVEL15_ID = T.LEVEL15_ID
1214 and B.LEVEL16_ID = T.LEVEL16_ID
1215 and B.LEVEL17_ID = T.LEVEL17_ID
1216 and B.LEVEL18_ID = T.LEVEL18_ID
1217 and B.LEVEL19_ID = T.LEVEL19_ID
1218 and B.LEVEL20_ID = T.LEVEL20_ID
1219 );
1220
1221 update FEM_DIS_USR_DIM9_HIER_TL T set (
1222 OBJECT_NAME,
1223 OBJECT_DEFINITION_NAME,
1224 LEVEL1_NAME,
1225 LEVEL2_NAME,
1226 LEVEL3_NAME,
1227 LEVEL4_NAME,
1228 LEVEL5_NAME,
1229 LEVEL6_NAME,
1230 LEVEL7_NAME,
1231 LEVEL8_NAME,
1232 LEVEL9_NAME,
1233 LEVEL10_NAME,
1234 LEVEL11_NAME,
1235 LEVEL12_NAME,
1236 LEVEL13_NAME,
1237 LEVEL14_NAME,
1238 LEVEL15_NAME,
1239 LEVEL16_NAME,
1240 LEVEL17_NAME,
1241 LEVEL18_NAME,
1242 LEVEL19_NAME,
1243 LEVEL20_NAME,
1244 LEVEL1_DESCRIPTION,
1245 LEVEL2_DESCRIPTION,
1246 LEVEL3_DESCRIPTION,
1247 LEVEL4_DESCRIPTION,
1248 LEVEL5_DESCRIPTION,
1249 LEVEL6_DESCRIPTION,
1250 LEVEL7_DESCRIPTION,
1251 LEVEL8_DESCRIPTION,
1252 LEVEL9_DESCRIPTION,
1253 LEVEL10_DESCRIPTION,
1254 LEVEL11_DESCRIPTION,
1255 LEVEL12_DESCRIPTION,
1256 LEVEL13_DESCRIPTION,
1257 LEVEL14_DESCRIPTION,
1258 LEVEL15_DESCRIPTION,
1259 LEVEL16_DESCRIPTION,
1260 LEVEL17_DESCRIPTION,
1261 LEVEL18_DESCRIPTION,
1262 LEVEL19_DESCRIPTION,
1263 LEVEL20_DESCRIPTION
1264 ) = (select
1265 B.OBJECT_NAME,
1266 B.OBJECT_DEFINITION_NAME,
1267 B.LEVEL1_NAME,
1268 B.LEVEL2_NAME,
1269 B.LEVEL3_NAME,
1270 B.LEVEL4_NAME,
1271 B.LEVEL5_NAME,
1272 B.LEVEL6_NAME,
1273 B.LEVEL7_NAME,
1274 B.LEVEL8_NAME,
1275 B.LEVEL9_NAME,
1276 B.LEVEL10_NAME,
1277 B.LEVEL11_NAME,
1278 B.LEVEL12_NAME,
1279 B.LEVEL13_NAME,
1280 B.LEVEL14_NAME,
1281 B.LEVEL15_NAME,
1282 B.LEVEL16_NAME,
1283 B.LEVEL17_NAME,
1284 B.LEVEL18_NAME,
1285 B.LEVEL19_NAME,
1286 B.LEVEL20_NAME,
1287 B.LEVEL1_DESCRIPTION,
1288 B.LEVEL2_DESCRIPTION,
1289 B.LEVEL3_DESCRIPTION,
1290 B.LEVEL4_DESCRIPTION,
1291 B.LEVEL5_DESCRIPTION,
1292 B.LEVEL6_DESCRIPTION,
1293 B.LEVEL7_DESCRIPTION,
1294 B.LEVEL8_DESCRIPTION,
1295 B.LEVEL9_DESCRIPTION,
1296 B.LEVEL10_DESCRIPTION,
1297 B.LEVEL11_DESCRIPTION,
1298 B.LEVEL12_DESCRIPTION,
1299 B.LEVEL13_DESCRIPTION,
1300 B.LEVEL14_DESCRIPTION,
1301 B.LEVEL15_DESCRIPTION,
1302 B.LEVEL16_DESCRIPTION,
1303 B.LEVEL17_DESCRIPTION,
1304 B.LEVEL18_DESCRIPTION,
1305 B.LEVEL19_DESCRIPTION,
1306 B.LEVEL20_DESCRIPTION
1307 from FEM_DIS_USR_DIM9_HIER_TL B
1311 and B.LEVEL1_ID = T.LEVEL1_ID
1308 where B.OBJECT_ID = T.OBJECT_ID
1309 and B.OBJECT_DEFINITION_ID = T.OBJECT_DEFINITION_ID
1310 and B.VALUE_SET_ID = T.VALUE_SET_ID
1312 and B.LEVEL2_ID = T.LEVEL2_ID
1313 and B.LEVEL3_ID = T.LEVEL3_ID
1314 and B.LEVEL4_ID = T.LEVEL4_ID
1315 and B.LEVEL5_ID = T.LEVEL5_ID
1316 and B.LEVEL6_ID = T.LEVEL6_ID
1317 and B.LEVEL7_ID = T.LEVEL7_ID
1318 and B.LEVEL8_ID = T.LEVEL8_ID
1319 and B.LEVEL9_ID = T.LEVEL9_ID
1320 and B.LEVEL10_ID = T.LEVEL10_ID
1321 and B.LEVEL11_ID = T.LEVEL11_ID
1322 and B.LEVEL12_ID = T.LEVEL12_ID
1323 and B.LEVEL13_ID = T.LEVEL13_ID
1324 and B.LEVEL14_ID = T.LEVEL14_ID
1325 and B.LEVEL15_ID = T.LEVEL15_ID
1326 and B.LEVEL16_ID = T.LEVEL16_ID
1327 and B.LEVEL17_ID = T.LEVEL17_ID
1328 and B.LEVEL18_ID = T.LEVEL18_ID
1329 and B.LEVEL19_ID = T.LEVEL19_ID
1330 and B.LEVEL20_ID = T.LEVEL20_ID
1331 and B.LANGUAGE = T.SOURCE_LANG)
1332 where (
1333 T.OBJECT_ID,
1334 T.OBJECT_DEFINITION_ID,
1335 T.VALUE_SET_ID,
1336 T.LEVEL1_ID,
1337 T.LEVEL2_ID,
1338 T.LEVEL3_ID,
1339 T.LEVEL4_ID,
1340 T.LEVEL5_ID,
1341 T.LEVEL6_ID,
1342 T.LEVEL7_ID,
1343 T.LEVEL8_ID,
1344 T.LEVEL9_ID,
1345 T.LEVEL10_ID,
1346 T.LEVEL11_ID,
1347 T.LEVEL12_ID,
1348 T.LEVEL13_ID,
1349 T.LEVEL14_ID,
1350 T.LEVEL15_ID,
1351 T.LEVEL16_ID,
1352 T.LEVEL17_ID,
1353 T.LEVEL18_ID,
1354 T.LEVEL19_ID,
1355 T.LEVEL20_ID,
1356 T.LANGUAGE
1357 ) in (select
1358 SUBT.OBJECT_ID,
1359 SUBT.OBJECT_DEFINITION_ID,
1360 SUBT.VALUE_SET_ID,
1361 SUBT.LEVEL1_ID,
1362 SUBT.LEVEL2_ID,
1363 SUBT.LEVEL3_ID,
1364 SUBT.LEVEL4_ID,
1365 SUBT.LEVEL5_ID,
1366 SUBT.LEVEL6_ID,
1367 SUBT.LEVEL7_ID,
1368 SUBT.LEVEL8_ID,
1369 SUBT.LEVEL9_ID,
1370 SUBT.LEVEL10_ID,
1371 SUBT.LEVEL11_ID,
1372 SUBT.LEVEL12_ID,
1373 SUBT.LEVEL13_ID,
1374 SUBT.LEVEL14_ID,
1375 SUBT.LEVEL15_ID,
1376 SUBT.LEVEL16_ID,
1377 SUBT.LEVEL17_ID,
1378 SUBT.LEVEL18_ID,
1379 SUBT.LEVEL19_ID,
1380 SUBT.LEVEL20_ID,
1381 SUBT.LANGUAGE
1382 from FEM_DIS_USR_DIM9_HIER_TL SUBB, FEM_DIS_USR_DIM9_HIER_TL SUBT
1383 where SUBB.OBJECT_ID = SUBT.OBJECT_ID
1384 and SUBB.OBJECT_DEFINITION_ID = SUBT.OBJECT_DEFINITION_ID
1385 and SUBB.VALUE_SET_ID = SUBT.VALUE_SET_ID
1386 and SUBB.LEVEL1_ID = SUBT.LEVEL1_ID
1387 and SUBB.LEVEL2_ID = SUBT.LEVEL2_ID
1388 and SUBB.LEVEL3_ID = SUBT.LEVEL3_ID
1389 and SUBB.LEVEL4_ID = SUBT.LEVEL4_ID
1390 and SUBB.LEVEL5_ID = SUBT.LEVEL5_ID
1391 and SUBB.LEVEL6_ID = SUBT.LEVEL6_ID
1392 and SUBB.LEVEL7_ID = SUBT.LEVEL7_ID
1393 and SUBB.LEVEL8_ID = SUBT.LEVEL8_ID
1394 and SUBB.LEVEL9_ID = SUBT.LEVEL9_ID
1395 and SUBB.LEVEL10_ID = SUBT.LEVEL10_ID
1396 and SUBB.LEVEL11_ID = SUBT.LEVEL11_ID
1397 and SUBB.LEVEL12_ID = SUBT.LEVEL12_ID
1398 and SUBB.LEVEL13_ID = SUBT.LEVEL13_ID
1399 and SUBB.LEVEL14_ID = SUBT.LEVEL14_ID
1400 and SUBB.LEVEL15_ID = SUBT.LEVEL15_ID
1401 and SUBB.LEVEL16_ID = SUBT.LEVEL16_ID
1402 and SUBB.LEVEL17_ID = SUBT.LEVEL17_ID
1403 and SUBB.LEVEL18_ID = SUBT.LEVEL18_ID
1404 and SUBB.LEVEL19_ID = SUBT.LEVEL19_ID
1405 and SUBB.LEVEL20_ID = SUBT.LEVEL20_ID
1406 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1407 and (SUBB.OBJECT_NAME <> SUBT.OBJECT_NAME
1408 or SUBB.OBJECT_DEFINITION_NAME <> SUBT.OBJECT_DEFINITION_NAME
1409 or SUBB.LEVEL1_NAME <> SUBT.LEVEL1_NAME
1410 or SUBB.LEVEL2_NAME <> SUBT.LEVEL2_NAME
1411 or SUBB.LEVEL3_NAME <> SUBT.LEVEL3_NAME
1412 or SUBB.LEVEL4_NAME <> SUBT.LEVEL4_NAME
1413 or SUBB.LEVEL5_NAME <> SUBT.LEVEL5_NAME
1414 or SUBB.LEVEL6_NAME <> SUBT.LEVEL6_NAME
1415 or SUBB.LEVEL7_NAME <> SUBT.LEVEL7_NAME
1416 or SUBB.LEVEL8_NAME <> SUBT.LEVEL8_NAME
1417 or SUBB.LEVEL9_NAME <> SUBT.LEVEL9_NAME
1418 or SUBB.LEVEL10_NAME <> SUBT.LEVEL10_NAME
1419 or SUBB.LEVEL11_NAME <> SUBT.LEVEL11_NAME
1420 or SUBB.LEVEL12_NAME <> SUBT.LEVEL12_NAME
1421 or SUBB.LEVEL13_NAME <> SUBT.LEVEL13_NAME
1422 or SUBB.LEVEL14_NAME <> SUBT.LEVEL14_NAME
1423 or SUBB.LEVEL15_NAME <> SUBT.LEVEL15_NAME
1424 or SUBB.LEVEL16_NAME <> SUBT.LEVEL16_NAME
1425 or SUBB.LEVEL17_NAME <> SUBT.LEVEL17_NAME
1426 or SUBB.LEVEL18_NAME <> SUBT.LEVEL18_NAME
1427 or SUBB.LEVEL19_NAME <> SUBT.LEVEL19_NAME
1428 or SUBB.LEVEL20_NAME <> SUBT.LEVEL20_NAME
1429 or SUBB.LEVEL1_DESCRIPTION <> SUBT.LEVEL1_DESCRIPTION
1430 or (SUBB.LEVEL1_DESCRIPTION is null and SUBT.LEVEL1_DESCRIPTION is not null)
1431 or (SUBB.LEVEL1_DESCRIPTION is not null and SUBT.LEVEL1_DESCRIPTION is null)
1432 or SUBB.LEVEL2_DESCRIPTION <> SUBT.LEVEL2_DESCRIPTION
1433 or (SUBB.LEVEL2_DESCRIPTION is null and SUBT.LEVEL2_DESCRIPTION is not null)
1434 or (SUBB.LEVEL2_DESCRIPTION is not null and SUBT.LEVEL2_DESCRIPTION is null)
1435 or SUBB.LEVEL3_DESCRIPTION <> SUBT.LEVEL3_DESCRIPTION
1436 or (SUBB.LEVEL3_DESCRIPTION is null and SUBT.LEVEL3_DESCRIPTION is not null)
1437 or (SUBB.LEVEL3_DESCRIPTION is not null and SUBT.LEVEL3_DESCRIPTION is null)
1438 or SUBB.LEVEL4_DESCRIPTION <> SUBT.LEVEL4_DESCRIPTION
1439 or (SUBB.LEVEL4_DESCRIPTION is null and SUBT.LEVEL4_DESCRIPTION is not null)
1440 or (SUBB.LEVEL4_DESCRIPTION is not null and SUBT.LEVEL4_DESCRIPTION is null)
1441 or SUBB.LEVEL5_DESCRIPTION <> SUBT.LEVEL5_DESCRIPTION
1442 or (SUBB.LEVEL5_DESCRIPTION is null and SUBT.LEVEL5_DESCRIPTION is not null)
1443 or (SUBB.LEVEL5_DESCRIPTION is not null and SUBT.LEVEL5_DESCRIPTION is null)
1447 or SUBB.LEVEL7_DESCRIPTION <> SUBT.LEVEL7_DESCRIPTION
1444 or SUBB.LEVEL6_DESCRIPTION <> SUBT.LEVEL6_DESCRIPTION
1445 or (SUBB.LEVEL6_DESCRIPTION is null and SUBT.LEVEL6_DESCRIPTION is not null)
1446 or (SUBB.LEVEL6_DESCRIPTION is not null and SUBT.LEVEL6_DESCRIPTION is null)
1448 or (SUBB.LEVEL7_DESCRIPTION is null and SUBT.LEVEL7_DESCRIPTION is not null)
1449 or (SUBB.LEVEL7_DESCRIPTION is not null and SUBT.LEVEL7_DESCRIPTION is null)
1450 or SUBB.LEVEL8_DESCRIPTION <> SUBT.LEVEL8_DESCRIPTION
1451 or (SUBB.LEVEL8_DESCRIPTION is null and SUBT.LEVEL8_DESCRIPTION is not null)
1452 or (SUBB.LEVEL8_DESCRIPTION is not null and SUBT.LEVEL8_DESCRIPTION is null)
1453 or SUBB.LEVEL9_DESCRIPTION <> SUBT.LEVEL9_DESCRIPTION
1454 or (SUBB.LEVEL9_DESCRIPTION is null and SUBT.LEVEL9_DESCRIPTION is not null)
1455 or (SUBB.LEVEL9_DESCRIPTION is not null and SUBT.LEVEL9_DESCRIPTION is null)
1456 or SUBB.LEVEL10_DESCRIPTION <> SUBT.LEVEL10_DESCRIPTION
1457 or (SUBB.LEVEL10_DESCRIPTION is null and SUBT.LEVEL10_DESCRIPTION is not null)
1458 or (SUBB.LEVEL10_DESCRIPTION is not null and SUBT.LEVEL10_DESCRIPTION is null)
1459 or SUBB.LEVEL11_DESCRIPTION <> SUBT.LEVEL11_DESCRIPTION
1460 or (SUBB.LEVEL11_DESCRIPTION is null and SUBT.LEVEL11_DESCRIPTION is not null)
1461 or (SUBB.LEVEL11_DESCRIPTION is not null and SUBT.LEVEL11_DESCRIPTION is null)
1462 or SUBB.LEVEL12_DESCRIPTION <> SUBT.LEVEL12_DESCRIPTION
1463 or (SUBB.LEVEL12_DESCRIPTION is null and SUBT.LEVEL12_DESCRIPTION is not null)
1464 or (SUBB.LEVEL12_DESCRIPTION is not null and SUBT.LEVEL12_DESCRIPTION is null)
1465 or SUBB.LEVEL13_DESCRIPTION <> SUBT.LEVEL13_DESCRIPTION
1466 or (SUBB.LEVEL13_DESCRIPTION is null and SUBT.LEVEL13_DESCRIPTION is not null)
1467 or (SUBB.LEVEL13_DESCRIPTION is not null and SUBT.LEVEL13_DESCRIPTION is null)
1468 or SUBB.LEVEL14_DESCRIPTION <> SUBT.LEVEL14_DESCRIPTION
1469 or (SUBB.LEVEL14_DESCRIPTION is null and SUBT.LEVEL14_DESCRIPTION is not null)
1470 or (SUBB.LEVEL14_DESCRIPTION is not null and SUBT.LEVEL14_DESCRIPTION is null)
1471 or SUBB.LEVEL15_DESCRIPTION <> SUBT.LEVEL15_DESCRIPTION
1472 or (SUBB.LEVEL15_DESCRIPTION is null and SUBT.LEVEL15_DESCRIPTION is not null)
1473 or (SUBB.LEVEL15_DESCRIPTION is not null and SUBT.LEVEL15_DESCRIPTION is null)
1474 or SUBB.LEVEL16_DESCRIPTION <> SUBT.LEVEL16_DESCRIPTION
1475 or (SUBB.LEVEL16_DESCRIPTION is null and SUBT.LEVEL16_DESCRIPTION is not null)
1476 or (SUBB.LEVEL16_DESCRIPTION is not null and SUBT.LEVEL16_DESCRIPTION is null)
1477 or SUBB.LEVEL17_DESCRIPTION <> SUBT.LEVEL17_DESCRIPTION
1478 or (SUBB.LEVEL17_DESCRIPTION is null and SUBT.LEVEL17_DESCRIPTION is not null)
1479 or (SUBB.LEVEL17_DESCRIPTION is not null and SUBT.LEVEL17_DESCRIPTION is null)
1480 or SUBB.LEVEL18_DESCRIPTION <> SUBT.LEVEL18_DESCRIPTION
1481 or (SUBB.LEVEL18_DESCRIPTION is null and SUBT.LEVEL18_DESCRIPTION is not null)
1482 or (SUBB.LEVEL18_DESCRIPTION is not null and SUBT.LEVEL18_DESCRIPTION is null)
1483 or SUBB.LEVEL19_DESCRIPTION <> SUBT.LEVEL19_DESCRIPTION
1484 or (SUBB.LEVEL19_DESCRIPTION is null and SUBT.LEVEL19_DESCRIPTION is not null)
1485 or (SUBB.LEVEL19_DESCRIPTION is not null and SUBT.LEVEL19_DESCRIPTION is null)
1486 or SUBB.LEVEL20_DESCRIPTION <> SUBT.LEVEL20_DESCRIPTION
1487 or (SUBB.LEVEL20_DESCRIPTION is null and SUBT.LEVEL20_DESCRIPTION is not null)
1488 or (SUBB.LEVEL20_DESCRIPTION is not null and SUBT.LEVEL20_DESCRIPTION is null)
1489 ));
1490
1491 insert into FEM_DIS_USR_DIM9_HIER_TL (
1492 OBJECT_ID,
1493 OBJECT_DEFINITION_ID,
1494 VALUE_SET_ID,
1495 LEVEL1_ID,
1496 LEVEL2_ID,
1497 LEVEL3_ID,
1498 LEVEL4_ID,
1499 LEVEL5_ID,
1500 LEVEL6_ID,
1501 LEVEL7_ID,
1502 LEVEL8_ID,
1503 LEVEL9_ID,
1504 LEVEL10_ID,
1505 LEVEL11_ID,
1506 LEVEL12_ID,
1507 LEVEL13_ID,
1508 LEVEL14_ID,
1509 LEVEL15_ID,
1510 LEVEL16_ID,
1511 LEVEL17_ID,
1512 LEVEL18_ID,
1513 LEVEL19_ID,
1514 LEVEL20_ID,
1515 OBJECT_NAME,
1516 OBJECT_DEFINITION_NAME,
1517 LEVEL1_NAME,
1518 LEVEL2_NAME,
1519 LEVEL3_NAME,
1520 LEVEL4_NAME,
1521 LEVEL5_NAME,
1522 LEVEL6_NAME,
1523 LEVEL7_NAME,
1524 LEVEL8_NAME,
1525 LEVEL9_NAME,
1526 LEVEL10_NAME,
1527 LEVEL11_NAME,
1528 LEVEL12_NAME,
1529 LEVEL13_NAME,
1530 LEVEL14_NAME,
1531 LEVEL15_NAME,
1532 LEVEL16_NAME,
1533 LEVEL17_NAME,
1534 LEVEL18_NAME,
1535 LEVEL19_NAME,
1536 LEVEL20_NAME,
1537 LEVEL1_DESCRIPTION,
1538 LEVEL2_DESCRIPTION,
1539 LEVEL3_DESCRIPTION,
1540 LEVEL4_DESCRIPTION,
1541 LEVEL5_DESCRIPTION,
1542 LEVEL6_DESCRIPTION,
1543 LEVEL7_DESCRIPTION,
1544 LEVEL8_DESCRIPTION,
1545 LEVEL9_DESCRIPTION,
1546 LEVEL10_DESCRIPTION,
1547 LEVEL11_DESCRIPTION,
1548 LEVEL12_DESCRIPTION,
1549 LEVEL13_DESCRIPTION,
1550 LEVEL14_DESCRIPTION,
1551 LEVEL15_DESCRIPTION,
1552 LEVEL16_DESCRIPTION,
1553 LEVEL17_DESCRIPTION,
1554 LEVEL18_DESCRIPTION,
1555 LEVEL19_DESCRIPTION,
1556 LEVEL20_DESCRIPTION,
1557 CREATION_DATE,
1558 CREATED_BY,
1559 LAST_UPDATE_DATE,
1560 LAST_UPDATED_BY,
1561 LAST_UPDATE_LOGIN,
1562 LANGUAGE,
1563 SOURCE_LANG
1564 ) select /*+ ORDERED */
1565 B.OBJECT_ID,
1566 B.OBJECT_DEFINITION_ID,
1567 B.VALUE_SET_ID,
1568 B.LEVEL1_ID,
1569 B.LEVEL2_ID,
1570 B.LEVEL3_ID,
1571 B.LEVEL4_ID,
1572 B.LEVEL5_ID,
1573 B.LEVEL6_ID,
1574 B.LEVEL7_ID,
1575 B.LEVEL8_ID,
1576 B.LEVEL9_ID,
1577 B.LEVEL10_ID,
1578 B.LEVEL11_ID,
1579 B.LEVEL12_ID,
1580 B.LEVEL13_ID,
1584 B.LEVEL17_ID,
1581 B.LEVEL14_ID,
1582 B.LEVEL15_ID,
1583 B.LEVEL16_ID,
1585 B.LEVEL18_ID,
1586 B.LEVEL19_ID,
1587 B.LEVEL20_ID,
1588 B.OBJECT_NAME,
1589 B.OBJECT_DEFINITION_NAME,
1590 B.LEVEL1_NAME,
1591 B.LEVEL2_NAME,
1592 B.LEVEL3_NAME,
1593 B.LEVEL4_NAME,
1594 B.LEVEL5_NAME,
1595 B.LEVEL6_NAME,
1596 B.LEVEL7_NAME,
1597 B.LEVEL8_NAME,
1598 B.LEVEL9_NAME,
1599 B.LEVEL10_NAME,
1600 B.LEVEL11_NAME,
1601 B.LEVEL12_NAME,
1602 B.LEVEL13_NAME,
1603 B.LEVEL14_NAME,
1604 B.LEVEL15_NAME,
1605 B.LEVEL16_NAME,
1606 B.LEVEL17_NAME,
1607 B.LEVEL18_NAME,
1608 B.LEVEL19_NAME,
1609 B.LEVEL20_NAME,
1610 B.LEVEL1_DESCRIPTION,
1611 B.LEVEL2_DESCRIPTION,
1612 B.LEVEL3_DESCRIPTION,
1613 B.LEVEL4_DESCRIPTION,
1614 B.LEVEL5_DESCRIPTION,
1615 B.LEVEL6_DESCRIPTION,
1616 B.LEVEL7_DESCRIPTION,
1617 B.LEVEL8_DESCRIPTION,
1618 B.LEVEL9_DESCRIPTION,
1619 B.LEVEL10_DESCRIPTION,
1620 B.LEVEL11_DESCRIPTION,
1621 B.LEVEL12_DESCRIPTION,
1622 B.LEVEL13_DESCRIPTION,
1623 B.LEVEL14_DESCRIPTION,
1624 B.LEVEL15_DESCRIPTION,
1625 B.LEVEL16_DESCRIPTION,
1626 B.LEVEL17_DESCRIPTION,
1627 B.LEVEL18_DESCRIPTION,
1628 B.LEVEL19_DESCRIPTION,
1629 B.LEVEL20_DESCRIPTION,
1630 B.CREATION_DATE,
1631 B.CREATED_BY,
1632 B.LAST_UPDATE_DATE,
1633 B.LAST_UPDATED_BY,
1634 B.LAST_UPDATE_LOGIN,
1635 L.LANGUAGE_CODE,
1636 B.SOURCE_LANG
1637 from FEM_DIS_USR_DIM9_HIER_TL B, FND_LANGUAGES L
1638 where L.INSTALLED_FLAG in ('I', 'B')
1639 and B.LANGUAGE = userenv('LANG')
1640 and not exists
1641 (select NULL
1642 from FEM_DIS_USR_DIM9_HIER_TL T
1643 where T.OBJECT_ID = B.OBJECT_ID
1644 and T.OBJECT_DEFINITION_ID = B.OBJECT_DEFINITION_ID
1645 and T.VALUE_SET_ID = B.VALUE_SET_ID
1646 and T.LEVEL1_ID = B.LEVEL1_ID
1647 and T.LEVEL2_ID = B.LEVEL2_ID
1648 and T.LEVEL3_ID = B.LEVEL3_ID
1649 and T.LEVEL4_ID = B.LEVEL4_ID
1650 and T.LEVEL5_ID = B.LEVEL5_ID
1651 and T.LEVEL6_ID = B.LEVEL6_ID
1652 and T.LEVEL7_ID = B.LEVEL7_ID
1653 and T.LEVEL8_ID = B.LEVEL8_ID
1654 and T.LEVEL9_ID = B.LEVEL9_ID
1655 and T.LEVEL10_ID = B.LEVEL10_ID
1656 and T.LEVEL11_ID = B.LEVEL11_ID
1657 and T.LEVEL12_ID = B.LEVEL12_ID
1658 and T.LEVEL13_ID = B.LEVEL13_ID
1659 and T.LEVEL14_ID = B.LEVEL14_ID
1660 and T.LEVEL15_ID = B.LEVEL15_ID
1661 and T.LEVEL16_ID = B.LEVEL16_ID
1662 and T.LEVEL17_ID = B.LEVEL17_ID
1663 and T.LEVEL18_ID = B.LEVEL18_ID
1664 and T.LEVEL19_ID = B.LEVEL19_ID
1665 and T.LEVEL20_ID = B.LEVEL20_ID
1666 and T.LANGUAGE = L.LANGUAGE_CODE);
1667 end ADD_LANGUAGE;
1668 PROCEDURE TRANSLATE_ROW(
1669 x_OBJECT_ID in number,
1670 x_OBJECT_DEFINITION_ID in number,
1671 x_VALUE_SET_ID in number,
1672 x_LEVEL1_ID in number,
1673 x_LEVEL2_ID in number,
1674 x_LEVEL3_ID in number,
1675 x_LEVEL4_ID in number,
1676 x_LEVEL5_ID in number,
1677 x_LEVEL6_ID in number,
1678 x_LEVEL7_ID in number,
1679 x_LEVEL8_ID in number,
1680 x_LEVEL9_ID in number,
1681 x_LEVEL10_ID in number,
1682 x_LEVEL11_ID in number,
1683 x_LEVEL12_ID in number,
1684 x_LEVEL13_ID in number,
1685 x_LEVEL14_ID in number,
1686 x_LEVEL15_ID in number,
1687 x_LEVEL16_ID in number,
1688 x_LEVEL17_ID in number,
1689 x_LEVEL18_ID in number,
1690 x_LEVEL19_ID in number,
1691 x_LEVEL20_ID in number,
1692 x_owner in varchar2,
1693 x_last_update_date in varchar2,
1694 x_OBJECT_NAME in varchar2,
1695 x_OBJECT_DEFINITION_NAME in varchar2,
1696 x_LEVEL1_NAME in varchar2,
1697 x_LEVEL2_NAME in varchar2,
1698 x_LEVEL3_NAME in varchar2,
1699 x_LEVEL4_NAME in varchar2,
1700 x_LEVEL5_NAME in varchar2,
1701 x_LEVEL6_NAME in varchar2,
1702 x_LEVEL7_NAME in varchar2,
1703 x_LEVEL8_NAME in varchar2,
1704 x_LEVEL9_NAME in varchar2,
1705 x_LEVEL10_NAME in varchar2,
1706 x_LEVEL11_NAME in varchar2,
1707 x_LEVEL12_NAME in varchar2,
1708 x_LEVEL13_NAME in varchar2,
1709 x_LEVEL14_NAME in varchar2,
1710 x_LEVEL15_NAME in varchar2,
1711 x_LEVEL16_NAME in varchar2,
1712 x_LEVEL17_NAME in varchar2,
1713 x_LEVEL18_NAME in varchar2,
1714 x_LEVEL19_NAME in varchar2,
1715 x_LEVEL20_NAME in varchar2,
1716 x_custom_mode in varchar2) is
1717
1718 owner_id number;
1719 ludate date;
1720 row_id varchar2(64);
1721 f_luby number; -- entity owner in file
1722 f_ludate date; -- entity update date in file
1723 db_luby number; -- entity owner in db
1724 db_ludate date; -- entity update date in db
1725 begin
1726
1727
1728 -- Translate owner to file_last_updated_by
1729 f_luby := fnd_load_util.owner_id(x_owner);
1730
1731 -- Translate char last_update_date to date
1732 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1733 begin
1734 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1735 into db_luby, db_ludate
1736 from FEM_DIS_USR_DIM9_HIER_TL
1737 where OBJECT_ID = x_OBJECT_ID
1738 and OBJECT_DEFINITION_ID = x_OBJECT_DEFINITION_ID
1739 and VALUE_SET_ID = x_VALUE_SET_ID
1740 and LEVEL1_ID = x_LEVEL1_ID
1744 and LEVEL5_ID = x_LEVEL5_ID
1741 and LEVEL2_ID = x_LEVEL2_ID
1742 and LEVEL3_ID = x_LEVEL3_ID
1743 and LEVEL4_ID = x_LEVEL4_ID
1745 and LEVEL6_ID = x_LEVEL6_ID
1746 and LEVEL7_ID = x_LEVEL7_ID
1747 and LEVEL8_ID = x_LEVEL8_ID
1748 and LEVEL9_ID = x_LEVEL9_ID
1749 and LEVEL10_ID = x_LEVEL10_ID
1750 and LEVEL11_ID = x_LEVEL11_ID
1751 and LEVEL12_ID = x_LEVEL12_ID
1752 and LEVEL13_ID = x_LEVEL13_ID
1753 and LEVEL14_ID = x_LEVEL14_ID
1754 and LEVEL15_ID = x_LEVEL15_ID
1755 and LEVEL16_ID = x_LEVEL16_ID
1756 and LEVEL17_ID = x_LEVEL17_ID
1757 and LEVEL18_ID = x_LEVEL18_ID
1758 and LEVEL19_ID = x_LEVEL19_ID
1759 and LEVEL20_ID = x_LEVEL20_ID
1760 and LANGUAGE = userenv('LANG');
1761
1762 -- Test for customization and version
1763 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1764 db_ludate, x_custom_mode)) then
1765 -- Update translations for this language
1766 update FEM_DIS_USR_DIM9_HIER_TL set
1767 OBJECT_NAME = nvl(x_OBJECT_NAME, OBJECT_NAME),
1768 OBJECT_DEFINITION_NAME = nvl(x_OBJECT_DEFINITION_NAME, OBJECT_DEFINITION_NAME),
1769 LEVEL1_NAME = nvl(x_LEVEL1_NAME, LEVEL1_NAME),
1770 LEVEL2_NAME = nvl(x_LEVEL2_NAME, LEVEL2_NAME),
1771 LEVEL3_NAME = nvl(x_LEVEL3_NAME, LEVEL3_NAME),
1772 LEVEL4_NAME = nvl(x_LEVEL4_NAME, LEVEL4_NAME),
1773 LEVEL5_NAME = nvl(x_LEVEL5_NAME, LEVEL5_NAME),
1774 LEVEL6_NAME = nvl(x_LEVEL6_NAME, LEVEL6_NAME),
1775 LEVEL7_NAME = nvl(x_LEVEL7_NAME, LEVEL7_NAME),
1776 LEVEL8_NAME = nvl(x_LEVEL8_NAME, LEVEL8_NAME),
1777 LEVEL9_NAME = nvl(x_LEVEL9_NAME, LEVEL9_NAME),
1778 LEVEL10_NAME = nvl(x_LEVEL10_NAME, LEVEL10_NAME),
1779 LEVEL11_NAME = nvl(x_LEVEL11_NAME, LEVEL11_NAME),
1780 LEVEL12_NAME = nvl(x_LEVEL12_NAME, LEVEL12_NAME),
1781 LEVEL13_NAME = nvl(x_LEVEL13_NAME, LEVEL13_NAME),
1782 LEVEL14_NAME = nvl(x_LEVEL14_NAME, LEVEL14_NAME),
1783 LEVEL15_NAME = nvl(x_LEVEL15_NAME, LEVEL15_NAME),
1784 LEVEL16_NAME = nvl(x_LEVEL16_NAME, LEVEL16_NAME),
1785 LEVEL17_NAME = nvl(x_LEVEL17_NAME, LEVEL17_NAME),
1786 LEVEL18_NAME = nvl(x_LEVEL18_NAME, LEVEL18_NAME),
1787 LEVEL19_NAME = nvl(x_LEVEL19_NAME, LEVEL19_NAME),
1788 LEVEL20_NAME = nvl(x_LEVEL20_NAME, LEVEL20_NAME),
1789 LAST_UPDATE_DATE = f_ludate,
1790 LAST_UPDATED_BY = f_luby,
1791 LAST_UPDATE_LOGIN = 0,
1792 SOURCE_LANG = userenv('LANG')
1793 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1794 and OBJECT_ID = x_OBJECT_ID
1795 and OBJECT_DEFINITION_ID = x_OBJECT_DEFINITION_ID
1796 and VALUE_SET_ID = x_VALUE_SET_ID
1797 and LEVEL1_ID = x_LEVEL1_ID
1798 and LEVEL2_ID = x_LEVEL2_ID
1799 and LEVEL3_ID = x_LEVEL3_ID
1800 and LEVEL4_ID = x_LEVEL4_ID
1801 and LEVEL5_ID = x_LEVEL5_ID
1802 and LEVEL6_ID = x_LEVEL6_ID
1803 and LEVEL7_ID = x_LEVEL7_ID
1804 and LEVEL8_ID = x_LEVEL8_ID
1805 and LEVEL9_ID = x_LEVEL9_ID
1806 and LEVEL10_ID = x_LEVEL10_ID
1807 and LEVEL11_ID = x_LEVEL11_ID
1808 and LEVEL12_ID = x_LEVEL12_ID
1809 and LEVEL13_ID = x_LEVEL13_ID
1810 and LEVEL14_ID = x_LEVEL14_ID
1811 and LEVEL15_ID = x_LEVEL15_ID
1812 and LEVEL16_ID = x_LEVEL16_ID
1813 and LEVEL17_ID = x_LEVEL17_ID
1814 and LEVEL18_ID = x_LEVEL18_ID
1815 and LEVEL19_ID = x_LEVEL19_ID
1816 and LEVEL20_ID = x_LEVEL20_ID;
1817 end if;
1818 exception
1819 when no_data_found then
1820 -- Do not insert missing translations, skip this row
1821 null;
1822 end;
1823 end TRANSLATE_ROW;
1824
1825
1826 end FEM_DIS_USR_DIM9_HIER_PKG;