1 PACKAGE BODY EGO_USER_ATTRS_COMMON_PVT AS
2 /* $Header: EGOPEFCB.pls 120.20.12020000.2 2012/11/14 09:10:29 liswang ship $ */
3
4
5
6 --=======================================================================--
7 --=*********************************************************************=--
8 --=*===================================================================*=--
9 --=*= =*=--
10 --=*= NOTE: This is a PRIVATE package; it is for internal use only, =*=--
11 --=*= and it is not supported for customer use. =*=--
12 --=*= =*=--
13 --=*===================================================================*=--
14 --=*********************************************************************=--
15 --=======================================================================--
16
17
18
19 ------------------------
20 -- Private Data Types --
21 ------------------------
22
23 TYPE LOCAL_DATE_TABLE IS TABLE OF DATE
24 INDEX BY BINARY_INTEGER;
25
26 TYPE LOCAL_BIG_VARCHAR_TABLE IS TABLE OF VARCHAR2(5000)
27 INDEX BY BINARY_INTEGER;
28
29 ---------------------------------------------------------------------
30 -- Type for caching of Attribute Group metadata; the type includes --
31 -- a LOCAL_DATE_TABLE field to keep track of which Attribute Group --
32 -- metadata record has gone unused for the longest amount of time, --
33 -- which is how we decide which Attribute Group to replace when --
34 -- adding a new record to a full cache --
35 ---------------------------------------------------------------------
36 TYPE EGO_ATTR_GROUP_BATCH_REC IS RECORD
37 (
38 CACHED_ATTR_GROUP_METADATA_1 EGO_ATTR_GROUP_METADATA_OBJ
39 ,CACHED_ATTR_GROUP_METADATA_2 EGO_ATTR_GROUP_METADATA_OBJ
40 ,CACHED_ATTR_GROUP_METADATA_3 EGO_ATTR_GROUP_METADATA_OBJ
41 ,CACHED_ATTR_GROUP_METADATA_4 EGO_ATTR_GROUP_METADATA_OBJ
42 ,CACHED_ATTR_GROUP_METADATA_5 EGO_ATTR_GROUP_METADATA_OBJ
43 ,CACHED_ATTR_GROUP_METADATA_6 EGO_ATTR_GROUP_METADATA_OBJ
44 ,CACHED_ATTR_GROUP_METADATA_7 EGO_ATTR_GROUP_METADATA_OBJ
45 ,CACHED_ATTR_GROUP_METADATA_8 EGO_ATTR_GROUP_METADATA_OBJ
46 ,CACHED_ATTR_GROUP_METADATA_9 EGO_ATTR_GROUP_METADATA_OBJ
47 ,CACHED_ATTR_GROUP_METADATA_10 EGO_ATTR_GROUP_METADATA_OBJ
48 ,access_times_table LOCAL_DATE_TABLE
49 );
50
51 ---------------------------------------------------------------------
52 -- Type for caching of Extension Table metadata; the type includes --
53 -- a LOCAL_DATE_TABLE field to keep track of which Extension Table --
54 -- metadata record has gone unused for the longest amount of time, --
55 -- which is how we decide which Extension Table to replace when --
56 -- adding a new record to a full cache --
57 ---------------------------------------------------------------------
58 TYPE EGO_EXT_TABLE_BATCH_REC IS RECORD
59 (
60 CACHED_EXT_TABLE_METADATA_1 EGO_EXT_TABLE_METADATA_OBJ
61 ,CACHED_EXT_TABLE_METADATA_2 EGO_EXT_TABLE_METADATA_OBJ
62 ,CACHED_EXT_TABLE_METADATA_3 EGO_EXT_TABLE_METADATA_OBJ
63 ,CACHED_EXT_TABLE_METADATA_4 EGO_EXT_TABLE_METADATA_OBJ
64 ,CACHED_EXT_TABLE_METADATA_5 EGO_EXT_TABLE_METADATA_OBJ
65 ,access_times_table LOCAL_DATE_TABLE
66 );
67
68 ---------------------------------------------------------------------
69 -- Type for caching of Data Level metadata; the type includes --
70 -- a LOCAL_DATE_TABLE field to keep track of which Data Level --
71 -- metadata record has gone unused for the longest amount of time, --
72 -- which is how we decide which Extension Table to replace when --
73 -- adding a new record to a full cache --
74 ---------------------------------------------------------------------
75 TYPE EGO_DATA_LEVEL_BATCH_REC IS RECORD
76 (
77 CACHED_DATA_LEVEL_METADATA_1 EGO_DATA_LEVEL_METADATA_OBJ
78 ,CACHED_DATA_LEVEL_METADATA_2 EGO_DATA_LEVEL_METADATA_OBJ
79 ,CACHED_DATA_LEVEL_METADATA_3 EGO_DATA_LEVEL_METADATA_OBJ
80 ,CACHED_DATA_LEVEL_METADATA_4 EGO_DATA_LEVEL_METADATA_OBJ
81 ,CACHED_DATA_LEVEL_METADATA_5 EGO_DATA_LEVEL_METADATA_OBJ
82 ,CACHED_DATA_LEVEL_METADATA_6 EGO_DATA_LEVEL_METADATA_OBJ
83 ,CACHED_DATA_LEVEL_METADATA_7 EGO_DATA_LEVEL_METADATA_OBJ
84 ,CACHED_DATA_LEVEL_METADATA_8 EGO_DATA_LEVEL_METADATA_OBJ
85 ,CACHED_DATA_LEVEL_METADATA_9 EGO_DATA_LEVEL_METADATA_OBJ
86 ,CACHED_DATA_LEVEL_METADATA_10 EGO_DATA_LEVEL_METADATA_OBJ
87 ,access_times_table LOCAL_DATE_TABLE
88 );
89
90 ------------------------------
91 -- Private Global Variables --
92 ------------------------------
93
94 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_USER_ATTRS_COMMON_PVT';
95 G_CURRENT_USER_ID NUMBER := FND_GLOBAL.User_Id;
96 G_CURRENT_LOGIN_ID NUMBER := FND_GLOBAL.Login_Id;
97
98 G_AG_METADATA_BATCH_1 EGO_ATTR_GROUP_BATCH_REC;
99 G_AG_METADATA_BATCH_2 EGO_ATTR_GROUP_BATCH_REC;
100 G_AG_METADATA_BATCH_3 EGO_ATTR_GROUP_BATCH_REC;
101 G_AG_METADATA_BATCH_4 EGO_ATTR_GROUP_BATCH_REC;
102 G_AG_METADATA_BATCH_5 EGO_ATTR_GROUP_BATCH_REC;
103
104 G_EXT_TABLE_METADATA_BATCH_1 EGO_EXT_TABLE_BATCH_REC;
105
106 G_DATA_LEVEL_METADATA_BATCH_1 EGO_DATA_LEVEL_BATCH_REC;
107
108 --
109 -- Bug 12998006. Added caching logic for has_column_in_table()
110 -- function. Global PLSQL collection for the cache.
111 -- sreharih. Mon Sep 19 13:36:13 PDT 2011
112 --
113 TYPE EGO_TABLE_COLUMN_METADATA IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
114 G_TABLE_COL_METADATA EGO_TABLE_COLUMN_METADATA;
115
116
117 ----------------------------------------------------------------------
118
119 --------------------------------------
120 -- Caching Procedures and Functions --
121 --------------------------------------
122
123 ----------------------------------------------------------------------
124
125 procedure code_debug (msg IN VARCHAR2
126 ,debug_level IN NUMBER default 3
127 ) IS
128 BEGIN
129 null;
130 -- sri_debug('EGOPEFCB '||msg);
131 EGO_USER_ATTRS_DATA_PVT.Debug_Msg(msg , debug_level);
132 -- dbms_output.put_line(msg);
133 END code_debug;
134
135 --
136 -- Bug 12998006. Added caching logic for has_column_in_table()
137 -- function. New methods to support caching.
138 -- sreharih. Mon Sep 19 13:36:13 PDT 2011
139 --
140
141 --
142 -- Build cache key.
143 --
144 FUNCTION build_key (p_owner IN VARCHAR2,
145 p_table_name IN VARCHAR2,
146 p_column_name IN VARCHAR2) RETURN VARCHAR2 IS
147
148 BEGIN
149 RETURN substr(upper(p_owner) || '.' || upper(p_table_name) || '.' || upper(p_column_name),1,100);
150 END build_key;
151
152 FUNCTION get_cached_value(p_key IN VARCHAR2) RETURN VARCHAR2 IS
153
154 BEGIN
155 IF G_TABLE_COL_METADATA.EXISTS(p_key) THEN
156 --code_debug('key => '|| p_key || ' CACHE HIT: value => ' || G_TABLE_COL_METADATA(p_key));
157 RETURN G_TABLE_COL_METADATA(p_key);
158 ELSE
159 --code_debug('key => '|| p_key || ' CACHE MISS ');
160 RETURN NULL;
161 END IF;
162 END get_cached_value;
163
164 PROCEDURE cache_value(p_key IN VARCHAR2,
165 p_value IN VARCHAR2) IS
166
167 BEGIN
168 G_TABLE_COL_METADATA(p_key) := substr(p_value,1,100);
169 --code_debug('key => '|| p_key || ' CACHE value => ' || G_TABLE_COL_METADATA(p_key));
170 END cache_value;
171 --
172 -- Get cached value
173 --
174 FUNCTION get_cached_tablecol_data(p_owner IN VARCHAR2,
175 p_table_name IN VARCHAR2,
176 p_column_name IN VARCHAR2) RETURN VARCHAR2 IS
177
178 l_key VARCHAR2(100);
179 BEGIN
180 l_key := build_key(p_owner => p_owner,
181 p_table_name => p_table_name,
182 p_column_name => p_column_name);
183
184 RETURN get_cached_value(p_key => l_key);
185 END get_cached_tablecol_data;
186
187 --
188 -- Cache table col value.
189 --
190 PROCEDURE cache_tablecol_data(p_owner IN VARCHAR2,
191 p_table_name IN VARCHAR2,
192 p_column_name IN VARCHAR2,
193 p_value IN VARCHAR2) IS
194
195 l_key VARCHAR2(100);
196 BEGIN
197 l_key := build_key(p_owner => p_owner,
198 p_table_name => p_table_name,
199 p_column_name => p_column_name);
200 cache_value(p_key => l_key,
201 p_value => p_value);
202
203 END cache_tablecol_data;
204
205 --
206 -- get cached owner value
207 --
208 FUNCTION get_cached_owner(p_table_name IN VARCHAR2) RETURN VARCHAR2 IS
209 l_key VARCHAR2(100);
210 BEGIN
211 l_key := upper(p_table_name);
212
213 RETURN get_cached_value(p_key => l_key);
214
215 END get_cached_owner;
216
217 --
218 -- Cache table owner value.
219 --
220 PROCEDURE cache_tableowner_data(p_table_name IN VARCHAR2,
221 p_value IN VARCHAR2) IS
222
223 l_key VARCHAR2(100);
224 BEGIN
225 l_key := upper(p_table_name);
226 cache_value(p_key => l_key,
227 p_value => p_value);
228
229
230 END cache_tableowner_data;
231
232 -- End of bug 12998006.
233 ----------------------------------------------------------------------
234 PROCEDURE Reset_Cache_And_Globals
235 IS
236
237 l_null_ag_metadata_batch EGO_ATTR_GROUP_BATCH_REC;
238 l_null_ext_table_batch EGO_EXT_TABLE_BATCH_REC;
239 l_null_data_level_batch EGO_DATA_LEVEL_BATCH_REC;
240
241 BEGIN
242
243 G_CURRENT_USER_ID := FND_GLOBAL.User_Id;
244 G_CURRENT_LOGIN_ID := FND_GLOBAL.Login_Id;
245
246 G_AG_METADATA_BATCH_1 := l_null_ag_metadata_batch;
247 G_AG_METADATA_BATCH_2 := l_null_ag_metadata_batch;
248 G_AG_METADATA_BATCH_3 := l_null_ag_metadata_batch;
249 G_AG_METADATA_BATCH_4 := l_null_ag_metadata_batch;
250 G_AG_METADATA_BATCH_5 := l_null_ag_metadata_batch;
251 G_EXT_TABLE_METADATA_BATCH_1 := l_null_ext_table_batch;
252 G_DATA_LEVEL_METADATA_BATCH_1 := l_null_data_level_batch;
253
254 END Reset_Cache_And_Globals;
255
256 ----------------------------------------------------------------------
257
258 FUNCTION Find_Oldest_Element_Info (
259 p_access_times_table IN LOCAL_DATE_TABLE
260 ,x_oldest_time OUT NOCOPY DATE
261 )
262 RETURN NUMBER
263 IS
264
268 BEGIN
265 l_oldest_index NUMBER;
266 l_current_index NUMBER;
267
269
270 IF (p_access_times_table.COUNT > 0) THEN
271 x_oldest_time := SYSDATE;
272 l_oldest_index := l_current_index;
273 l_current_index := p_access_times_table.FIRST;
274 WHILE (l_current_index <= p_access_times_table.LAST)
275 LOOP
276
277 IF (p_access_times_table(l_current_index) < x_oldest_time) THEN
278 x_oldest_time := p_access_times_table(l_current_index);
279 l_oldest_index := l_current_index;
280 END IF;
281
282 l_current_index := p_access_times_table.NEXT(l_current_index);
283 END LOOP;
284 END IF;
285
286 RETURN l_oldest_index;
287
288 END Find_Oldest_Element_Info;
289
290 ----------------------------------------------------------------------
291
292 FUNCTION Is_Room_In_AG_Batch (
293 p_attr_group_batch_rec IN EGO_ATTR_GROUP_BATCH_REC
294 )
295 RETURN BOOLEAN
296 IS
297
298 BEGIN
299
300 RETURN (p_attr_group_batch_rec.access_times_table.COUNT < 10);
301
302 END Is_Room_In_AG_Batch;
303
304 ----------------------------------------------------------------------
305
306 FUNCTION Is_Room_In_ET_Batch (
307 p_ext_table_batch_rec IN EGO_EXT_TABLE_BATCH_REC
308 )
309 RETURN BOOLEAN
310 IS
311
312 BEGIN
313
314 RETURN (p_ext_table_batch_rec.access_times_table.COUNT < 5);
315
316 END Is_Room_In_ET_Batch;
317
318 ----------------------------------------------------------------------
319
320 FUNCTION Is_Room_In_DL_Batch (
321 p_data_level_batch_rec IN EGO_DATA_LEVEL_BATCH_REC
322 )
323 RETURN BOOLEAN
324 IS
325
326 BEGIN
327
328 RETURN (p_data_level_batch_rec.access_times_table.COUNT < 10);
329
330 END Is_Room_In_DL_Batch;
331
332 ----------------------------------------------------------------------
333
334
335 FUNCTION Find_Oldest_AG_Batch_Index
336 RETURN NUMBER
337 IS
338
339 l_dummy_index_variable NUMBER;
340 l_batch_1_oldest_time DATE;
341 l_batch_2_oldest_time DATE;
342 l_batch_3_oldest_time DATE;
343 l_batch_4_oldest_time DATE;
344 l_batch_5_oldest_time DATE;
345 l_oldest_batch_index NUMBER;
346
347 BEGIN
348
349 l_dummy_index_variable := Find_Oldest_Element_Info(G_AG_METADATA_BATCH_1.access_times_table, l_batch_1_oldest_time);
350 l_dummy_index_variable := Find_Oldest_Element_Info(G_AG_METADATA_BATCH_2.access_times_table, l_batch_2_oldest_time);
351 l_dummy_index_variable := Find_Oldest_Element_Info(G_AG_METADATA_BATCH_3.access_times_table, l_batch_3_oldest_time);
352 l_dummy_index_variable := Find_Oldest_Element_Info(G_AG_METADATA_BATCH_4.access_times_table, l_batch_4_oldest_time);
353 l_dummy_index_variable := Find_Oldest_Element_Info(G_AG_METADATA_BATCH_5.access_times_table, l_batch_5_oldest_time);
354
355 IF (l_batch_1_oldest_time < l_batch_2_oldest_time AND
356 l_batch_1_oldest_time < l_batch_3_oldest_time AND
357 l_batch_1_oldest_time < l_batch_4_oldest_time AND
358 l_batch_1_oldest_time < l_batch_5_oldest_time) THEN
359
360 l_oldest_batch_index := 1;
361
362 ELSIF (l_batch_2_oldest_time < l_batch_1_oldest_time AND
363 l_batch_2_oldest_time < l_batch_3_oldest_time AND
364 l_batch_2_oldest_time < l_batch_4_oldest_time AND
365 l_batch_2_oldest_time < l_batch_5_oldest_time) THEN
366
367 l_oldest_batch_index := 2;
368
369 ELSIF (l_batch_3_oldest_time < l_batch_1_oldest_time AND
370 l_batch_3_oldest_time < l_batch_2_oldest_time AND
371 l_batch_3_oldest_time < l_batch_4_oldest_time AND
372 l_batch_3_oldest_time < l_batch_5_oldest_time) THEN
373
374 l_oldest_batch_index := 3;
375
376 ELSIF (l_batch_4_oldest_time < l_batch_1_oldest_time AND
377 l_batch_4_oldest_time < l_batch_2_oldest_time AND
378 l_batch_4_oldest_time < l_batch_3_oldest_time AND
379 l_batch_4_oldest_time < l_batch_5_oldest_time) THEN
380
381 l_oldest_batch_index := 4;
382
383 ELSIF (l_batch_4_oldest_time < l_batch_1_oldest_time AND
384 l_batch_4_oldest_time < l_batch_2_oldest_time AND
385 l_batch_4_oldest_time < l_batch_3_oldest_time AND
386 l_batch_4_oldest_time < l_batch_4_oldest_time) THEN
387
388 l_oldest_batch_index := 5;
389
390 END IF;
391
392 RETURN l_oldest_batch_index;
393
394 END Find_Oldest_AG_Batch_Index;
395
396 ----------------------------------------------------------------------
397
398 FUNCTION Do_AG_PKs_Match (
399 p_attr_group_id IN NUMBER
400 ,p_application_id IN NUMBER
401 ,p_attr_group_type IN VARCHAR2
402 ,p_attr_group_name IN VARCHAR2
403 ,p_attr_group_metadata_obj IN EGO_ATTR_GROUP_METADATA_OBJ
404 )
405 RETURN BOOLEAN
406 IS
407
408 BEGIN
409
410 RETURN (p_attr_group_metadata_obj.ATTR_GROUP_ID = p_attr_group_id OR
411 (p_attr_group_metadata_obj.APPLICATION_ID = p_application_id AND
412 p_attr_group_metadata_obj.ATTR_GROUP_TYPE = p_attr_group_type AND
413 p_attr_group_metadata_obj.ATTR_GROUP_NAME = p_attr_group_name));
414
415 END Do_AG_PKs_Match;
416
417 ----------------------------------------------------------------------
418
419 FUNCTION Find_Attr_Group_In_Batch (
420 p_attr_group_id IN NUMBER
424 ,px_attr_group_batch_rec IN OUT NOCOPY EGO_ATTR_GROUP_BATCH_REC
421 ,p_application_id IN NUMBER
422 ,p_attr_group_type IN VARCHAR2
423 ,p_attr_group_name IN VARCHAR2
425 )
426 RETURN EGO_ATTR_GROUP_METADATA_OBJ
427 IS
428
429 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
430
431 BEGIN
432
433 IF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
434 ,p_attr_group_type, p_attr_group_name
435 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_1)) THEN
436
437 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_1;
438 px_attr_group_batch_rec.access_times_table(1) := SYSDATE;
439
440 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
441 ,p_attr_group_type, p_attr_group_name
442 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_2)) THEN
443
444 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_2;
445 px_attr_group_batch_rec.access_times_table(2) := SYSDATE;
446
447 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
448 ,p_attr_group_type, p_attr_group_name
449 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_3)) THEN
450
451 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_3;
452 px_attr_group_batch_rec.access_times_table(3) := SYSDATE;
453
454 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
455 ,p_attr_group_type, p_attr_group_name
456 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_4)) THEN
457
458 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_4;
459 px_attr_group_batch_rec.access_times_table(4) := SYSDATE;
460
461 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
462 ,p_attr_group_type, p_attr_group_name
463 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_5)) THEN
464
465 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_5;
466 px_attr_group_batch_rec.access_times_table(5) := SYSDATE;
467
468 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
469 ,p_attr_group_type, p_attr_group_name
470 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_6)) THEN
471
472 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_6;
473 px_attr_group_batch_rec.access_times_table(6) := SYSDATE;
474
475 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
476 ,p_attr_group_type, p_attr_group_name
477 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_7)) THEN
478
479 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_7;
480 px_attr_group_batch_rec.access_times_table(7) := SYSDATE;
481
482 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
483 ,p_attr_group_type, p_attr_group_name
484 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_8)) THEN
485
486 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_8;
487 px_attr_group_batch_rec.access_times_table(8) := SYSDATE;
488
489 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
490 ,p_attr_group_type, p_attr_group_name
491 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_9)) THEN
492
493 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_9;
494 px_attr_group_batch_rec.access_times_table(9) := SYSDATE;
495
496 ELSIF (Do_AG_PKs_Match(p_attr_group_id, p_application_id
497 ,p_attr_group_type, p_attr_group_name
498 ,px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_10)) THEN
499
500 l_attr_group_metadata_obj := px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_10;
501 px_attr_group_batch_rec.access_times_table(10) := SYSDATE;
502
503 END IF;
504
505 RETURN l_attr_group_metadata_obj; --It may still be null here, and that's OK
506
507 END Find_Attr_Group_In_Batch;
508
509 ----------------------------------------------------------------------
510
511 FUNCTION Find_Attr_Group_In_Cache (
512 p_attr_group_id IN NUMBER
513 ,p_application_id IN NUMBER
514 ,p_attr_group_type IN VARCHAR2
515 ,p_attr_group_name IN VARCHAR2
516 )
517 RETURN EGO_ATTR_GROUP_METADATA_OBJ
518 IS
519
520 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
521
522 BEGIN
523
524 l_attr_group_metadata_obj := Find_Attr_Group_In_Batch(p_attr_group_id
525 ,p_application_id
526 ,p_attr_group_type
527 ,p_attr_group_name
528 ,G_AG_METADATA_BATCH_1);
529 IF (l_attr_group_metadata_obj IS NULL) THEN
530 l_attr_group_metadata_obj := Find_Attr_Group_In_Batch(p_attr_group_id
531 ,p_application_id
532 ,p_attr_group_type
533 ,p_attr_group_name
534 ,G_AG_METADATA_BATCH_2);
535 IF (l_attr_group_metadata_obj IS NULL) THEN
536 l_attr_group_metadata_obj := Find_Attr_Group_In_Batch(p_attr_group_id
540 ,G_AG_METADATA_BATCH_3);
537 ,p_application_id
538 ,p_attr_group_type
539 ,p_attr_group_name
541 IF (l_attr_group_metadata_obj IS NULL) THEN
542 l_attr_group_metadata_obj := Find_Attr_Group_In_Batch(p_attr_group_id
543 ,p_application_id
544 ,p_attr_group_type
545 ,p_attr_group_name
546 ,G_AG_METADATA_BATCH_4);
547 IF (l_attr_group_metadata_obj IS NULL) THEN
548 l_attr_group_metadata_obj := Find_Attr_Group_In_Batch(p_attr_group_id
549 ,p_application_id
550 ,p_attr_group_type
551 ,p_attr_group_name
552 ,G_AG_METADATA_BATCH_5);
553 END IF;
554 END IF;
555 END IF;
556 END IF;
557
558 RETURN l_attr_group_metadata_obj;
559
560 END Find_Attr_Group_In_Cache;
561
562 ----------------------------------------------------------------------
563
564 PROCEDURE Add_Attr_Group_To_Batch (
565 p_attr_group_metadata_obj IN EGO_ATTR_GROUP_METADATA_OBJ
566 ,p_replace_oldest_attr_group IN BOOLEAN
567 ,px_attr_group_batch_rec IN OUT NOCOPY EGO_ATTR_GROUP_BATCH_REC
568 ) IS
569
570 l_oldest_rec_index NUMBER;
571 l_dummy_time_variable DATE;
572 l_access_times_table LOCAL_DATE_TABLE;
573
574 BEGIN
575
576 IF (p_replace_oldest_attr_group) THEN
577 l_oldest_rec_index := Find_Oldest_Element_Info(px_attr_group_batch_rec.access_times_table, l_dummy_time_variable);
578 END IF;
579
580 IF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_1 IS NULL OR
581 l_oldest_rec_index = 1) THEN
582
583 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_1 := p_attr_group_metadata_obj;
584 px_attr_group_batch_rec.access_times_table(1) := SYSDATE;
585
586 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_2 IS NULL OR
587 l_oldest_rec_index = 2) THEN
588
589 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_2 := p_attr_group_metadata_obj;
590 px_attr_group_batch_rec.access_times_table(2) := SYSDATE;
591
592 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_3 IS NULL OR
593 l_oldest_rec_index = 3) THEN
594
595 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_3 := p_attr_group_metadata_obj;
596 px_attr_group_batch_rec.access_times_table(3) := SYSDATE;
597
598 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_4 IS NULL OR
599 l_oldest_rec_index = 4) THEN
600
601 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_4 := p_attr_group_metadata_obj;
602 px_attr_group_batch_rec.access_times_table(4) := SYSDATE;
603
604 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_5 IS NULL OR
605 l_oldest_rec_index = 5) THEN
606
607 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_5 := p_attr_group_metadata_obj;
608 px_attr_group_batch_rec.access_times_table(5) := SYSDATE;
609
610 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_6 IS NULL OR
611 l_oldest_rec_index = 6) THEN
612
613 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_6 := p_attr_group_metadata_obj;
614 px_attr_group_batch_rec.access_times_table(6) := SYSDATE;
615
616 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_7 IS NULL OR
617 l_oldest_rec_index = 7) THEN
618
619 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_7 := p_attr_group_metadata_obj;
620 px_attr_group_batch_rec.access_times_table(7) := SYSDATE;
621
622 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_8 IS NULL OR
623 l_oldest_rec_index = 8) THEN
624
625 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_8 := p_attr_group_metadata_obj;
626 px_attr_group_batch_rec.access_times_table(8) := SYSDATE;
627
628 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_9 IS NULL OR
629 l_oldest_rec_index = 9) THEN
630
631 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_9 := p_attr_group_metadata_obj;
632 px_attr_group_batch_rec.access_times_table(9) := SYSDATE;
633
634 ELSIF (px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_10 IS NULL OR
635 l_oldest_rec_index = 10) THEN
636
637 px_attr_group_batch_rec.CACHED_ATTR_GROUP_METADATA_10 := p_attr_group_metadata_obj;
638 px_attr_group_batch_rec.access_times_table(10) := SYSDATE;
639
640 END IF;
641
642 END Add_Attr_Group_To_Batch;
643
644 ----------------------------------------------------------------------
645
646 PROCEDURE Add_Attr_Group_To_Cache (
647 p_attr_group_metadata_obj IN EGO_ATTR_GROUP_METADATA_OBJ
648 ) IS
649
650 l_index_of_batch_to_update NUMBER;
651
652 BEGIN
653
654 IF (Is_Room_In_AG_Batch(G_AG_METADATA_BATCH_1)) THEN
655
656 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
657 ,FALSE
658 ,G_AG_METADATA_BATCH_1);
659
660 ELSIF (Is_Room_In_AG_Batch(G_AG_METADATA_BATCH_2)) THEN
661
662 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
663 ,FALSE
667
664 ,G_AG_METADATA_BATCH_2);
665
666 ELSIF (Is_Room_In_AG_Batch(G_AG_METADATA_BATCH_3)) THEN
668 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
669 ,FALSE
670 ,G_AG_METADATA_BATCH_3);
671
672 ELSIF (Is_Room_In_AG_Batch(G_AG_METADATA_BATCH_4)) THEN
673
674 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
675 ,FALSE
676 ,G_AG_METADATA_BATCH_4);
677
678 ELSIF (Is_Room_In_AG_Batch(G_AG_METADATA_BATCH_5)) THEN
679
680 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
681 ,FALSE
682 ,G_AG_METADATA_BATCH_5);
683
684 ELSE
685
686 l_index_of_batch_to_update := Find_Oldest_AG_Batch_Index();
687
688 IF (l_index_of_batch_to_update = 1) THEN
689 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
690 ,TRUE
691 ,G_AG_METADATA_BATCH_1);
692 ELSIF (l_index_of_batch_to_update = 2) THEN
693 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
694 ,TRUE
695 ,G_AG_METADATA_BATCH_2);
696 ELSIF (l_index_of_batch_to_update = 3) THEN
697 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
698 ,TRUE
699 ,G_AG_METADATA_BATCH_3);
700 ELSIF (l_index_of_batch_to_update = 4) THEN
701 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
702 ,TRUE
703 ,G_AG_METADATA_BATCH_4);
704 ELSIF (l_index_of_batch_to_update = 5) THEN
705 Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
706 ,TRUE
707 ,G_AG_METADATA_BATCH_5);
708 END IF;
709 END IF;
710
711 END Add_Attr_Group_To_Cache;
712
713 ----------------------------------------------------------------------
714
715 PROCEDURE Remove_OrderBy_Clause(
716 px_where_clause IN OUT NOCOPY VARCHAR2
717 ) IS
718
719 l_search_ordby_clause LONG;
720 l_ord_index NUMBER;
721 l_by_index NUMBER;
722 l_last_ord_index NUMBER;
723 --bug 5119374
724 l_has_order_by BOOLEAN := FALSE;
725
726 BEGIN
727 -- initialize variables
728
729 l_search_ordby_clause := UPPER(px_where_clause);
730 l_ord_index := INSTR(l_search_ordby_clause, 'ORDER ');
731
732 IF l_ord_index <> 0 THEN
733 l_last_ord_index := l_ord_index;
734 --bug 5119374
735 l_has_order_by :=TRUE;
736 ELSE
737 l_last_ord_index := length(px_where_clause);
738 END IF;
739
740 -- find the index of the last 'ORDER BY' clause
741
742 WHILE (l_ord_index <> 0) LOOP
743 l_by_index := INSTR(SUBSTR(l_search_ordby_clause, l_ord_index + 5), 'BY ');
744 IF l_by_index <> 0 THEN
745 l_search_ordby_clause := SUBSTR(l_search_ordby_clause, l_ord_index + 5 + l_by_index + 2);
746 l_ord_index := INSTR(l_search_ordby_clause, 'ORDER ');
747
748 -- if there are more 'ORDER BY' clauses, increment index:
749 -- l_last_ord_index += 5 letters for 'ORDER' +
750 -- + index of 'BY' + 2 letters for 'BY'
751 -- + (index of the next 'ORDER BY' - 1)
752
753 IF l_ord_index <> 0 THEN
754 l_last_ord_index := l_last_ord_index + 5 + l_by_index + 2 + l_ord_index - 1;
755 END IF;
756 ELSE
757 l_ord_index := 0;
758 END IF;
759 END LOOP;
760
761 -- if there is a close bracket: 'ORDER BY' clause is nested -> do nothing
762 -- if no close bracket, remove 'ORDER BY' clause
763 -- if after ORDER BY both '(' and also ')' exist then is not a subquery if and only if there is again no ')' after the previous
764 -- checks. e.g. "ORDER BY TO_NUMBER()" or "ORDER BY TO_NUMBER(X) ) "
765 -- ^ for closing the subquery
766 IF (l_has_order_by
767 AND ( INSTR(l_search_ordby_clause, ')') = 0
768 OR ( INSTR(l_search_ordby_clause, '(') <> 0
769 AND INSTR(l_search_ordby_clause, ')') <> 0
770 AND (
771 INSTR(l_search_ordby_clause,')')= length(l_search_ordby_clause) --bug 12630681
772 OR INSTR(SUBSTR(l_search_ordby_clause, INSTR(l_search_ordby_clause,')') + 1 ), ')' ) = 0 )
773 )
774 )
775 ) THEN --bug 5119374
776 px_where_clause := RTRIM(SUBSTR(px_where_clause, 0, l_last_ord_index - 1));
777 END IF;
778
779 END Remove_OrderBy_Clause;
780
781 ----------------------------------------------------------------------
782
783 PROCEDURE Build_Sql_Queries_For_Value (
784 p_value_set_id IN NUMBER
785 ,p_validation_code IN VARCHAR2
786 ,px_attr_group_metadata_obj IN OUT NOCOPY EGO_ATTR_GROUP_METADATA_OBJ
787 ,px_attr_metadata_obj IN OUT NOCOPY EGO_ATTR_METADATA_OBJ
788 ) IS
789
790 l_validation_table_info_row EGO_VALIDATION_TABLE_INFO_V%ROWTYPE;
791 l_column_name VARCHAR2(4000); -- Bug 4030107
792 l_where_clause LONG;
793
794 BEGIN
795
796 IF (p_validation_code = EGO_EXT_FWK_PUB.G_INDEPENDENT_VALIDATION_CODE OR p_validation_code = EGO_EXT_FWK_PUB.G_TRANS_IND_VALIDATION_CODE) THEN
797
801 -- the query that uses a bind variable in place of the VS ID, so --
798 --------------------------------------------------------------------
799 -- We only use this query, which has the Value Set ID hard-coded, --
800 -- in Get_User_Attrs_Data; elsewhere we use a dynamic version of --
802 -- that the SQL engine doesn't have to re-parse for each VS ID --
803 --------------------------------------------------------------------
804 px_attr_metadata_obj.INT_TO_DISP_VAL_QUERY := 'SELECT DISTINCT DISPLAY_NAME '||
805 'FROM EGO_VALUE_SET_VALUES_V '||
806 'WHERE VALUE_SET_ID = '||p_value_set_id||
807 ' AND ENABLED_CODE = ''Y'' '||
808 'AND (NVL(START_DATE, SYSDATE - 1) < SYSDATE) '||
809 'AND (NVL(END_DATE, SYSDATE + 1) > SYSDATE) '||
810 'AND INTERNAL_NAME = ';
811
812 ELSIF (p_validation_code = EGO_EXT_FWK_PUB.G_TABLE_VALIDATION_CODE) THEN
813 SELECT APPLICATION_TABLE_NAME
814 ,ID_COLUMN_NAME
815 ,VALUE_COLUMN_NAME
816 ,ADDITIONAL_WHERE_CLAUSE
817 INTO l_validation_table_info_row.APPLICATION_TABLE_NAME
818 ,l_validation_table_info_row.ID_COLUMN_NAME
819 ,l_validation_table_info_row.VALUE_COLUMN_NAME
820 ,l_validation_table_info_row.ADDITIONAL_WHERE_CLAUSE
821 FROM FND_FLEX_VALIDATION_TABLES
822 WHERE FLEX_VALUE_SET_ID = p_value_set_id;
823
824 IF (l_validation_table_info_row.ID_COLUMN_NAME IS NOT NULL) THEN
825 l_column_name := l_validation_table_info_row.ID_COLUMN_NAME;
826 ELSE
827 l_column_name := l_validation_table_info_row.VALUE_COLUMN_NAME;
828 END IF;
829
830 ---------------------------------
831 -- Trim off any leading spaces --
832 ---------------------------------
833 l_where_clause := LTRIM(l_validation_table_info_row.ADDITIONAL_WHERE_CLAUSE);
834
835 ---------------------------------------------
836 -- Check whether the trimmed string starts --
837 -- with 'WHERE'; if so, trim the 'WHERE' --
838 ---------------------------------------------
839 IF (INSTR(UPPER(SUBSTR(l_where_clause, 1, 6)), 'WHERE') <> 0) THEN
840 l_where_clause := SUBSTR(l_where_clause, 6);
841 END IF;
842 Remove_OrderBy_Clause(l_where_clause);
843 -----------------------------------------------------
844 -- Now, if where clause is non-empty, add an 'AND' --
845 -- so that we can append our own where criteria --
846 -----------------------------------------------------
847 IF (LENGTH(l_where_clause) > 0) THEN
848
849 ------------------------------------------------------
850 -- In case the where clause has new line or tabs --
851 -- we need to remove it BugFix:4101091 --
852 ------------------------------------------------------
853 SELECT REPLACE(l_where_clause,FND_GLOBAL.LOCAL_CHR(10),FND_GLOBAL.LOCAL_CHR(32)) INTO l_where_clause FROM dual; --replacing new line character
854 SELECT REPLACE(l_where_clause,FND_GLOBAL.LOCAL_CHR(13),FND_GLOBAL.LOCAL_CHR(32)) INTO l_where_clause FROM dual; --removing carriage return
855 -------------------------------------------------------------------------
856 -- well if there is still some special character left we cant help it. --
857 -------------------------------------------------------------------------
858
859 ------------------------------------------------------
860 -- In case the where clause starts with an Order By --
861 -- we need to add a 1=1 before the order by --
862 ------------------------------------------------------
863 IF ( INSTR(LTRIM(UPPER(l_where_clause)),'ORDER ') = 1 ) THEN
864 IF (INSTR(UPPER(
865 SUBSTR(LTRIM(l_where_clause),INSTR(LTRIM(UPPER(l_where_clause)),'ORDER ')+6 )
866 ),'BY ') <> 0) THEN
867 l_where_clause := ' 1=1 ' || l_where_clause ;
868 END IF;
869 END IF;
870
871 l_where_clause := ' AND ' || l_where_clause ; --BugFix: 4101266 we need to have a wrapper select statement on top of
872 --the value set query and have our where clause on the outer select.
873 ----------------------------------------------------------------------
874 -- If the where clause has an Attribute bind value, set the flag so --
875 -- we'll know to sort Attr values for the AG in Validate_Row (which --
876 -- we need to do to ensure that all bind values have been replaced --
877 -- by the time they're needed as token replacements); also mark the --
878 -- Attribute so that when we do sort, it will go at the end --
879 ----------------------------------------------------------------------
880 IF (INSTR(UPPER(l_where_clause), ':$ATTRIBUTEGROUP$.') > 0) THEN
881
882 px_attr_group_metadata_obj.SORT_ATTR_VALUES_FLAG := 'Y';
883
884 IF (px_attr_metadata_obj.VS_BIND_VALUES_CODE = 'O') THEN
885 px_attr_metadata_obj.VS_BIND_VALUES_CODE := 'B';
886 ELSE
887 px_attr_metadata_obj.VS_BIND_VALUES_CODE := 'A';
888 END IF;
889
890 ELSIF (INSTR(UPPER(l_where_clause), ':$OBJECT$.') > 0) THEN
891
892 IF (px_attr_metadata_obj.VS_BIND_VALUES_CODE = 'A') THEN
893 px_attr_metadata_obj.VS_BIND_VALUES_CODE := 'B';
894 ELSE
895 px_attr_metadata_obj.VS_BIND_VALUES_CODE := 'O';
896 END IF;
897
898 END IF;
899
900 END IF;
901
905 l_validation_table_info_row.VALUE_COLUMN_NAME ||' = ';
902 px_attr_metadata_obj.DISP_TO_INT_VAL_QUERY := 'SELECT DISTINCT '||l_column_name ||
903 ' FROM '||l_validation_table_info_row.APPLICATION_TABLE_NAME||
904 ' WHERE 1=1 '||l_where_clause||' AND '||
906
907 /***
908 TO DO: see if you can move the Additional Where Clause to the end (in case
909 the user passes something like a :1); wait, does that even matter? Won't
910 it break anyway?
911 Investigate.
912 ***/
913
914 px_attr_metadata_obj.INT_TO_DISP_VAL_QUERY := 'SELECT DISTINCT '|| l_validation_table_info_row.VALUE_COLUMN_NAME||
915 ' FROM '||l_validation_table_info_row.APPLICATION_TABLE_NAME||
916 ' WHERE 1=1 '||l_where_clause||' AND '||
917 l_column_name||' = ';
918
919 END IF;
920
921 END Build_Sql_Queries_For_Value;
922
923 ----------------------------------------------------------------------
924
925 PROCEDURE Build_Attr_Metadata_Table (
926 px_attr_group_metadata_obj IN OUT NOCOPY EGO_ATTR_GROUP_METADATA_OBJ
927 ) IS
928
929 l_attr_metadata_table EGO_ATTR_METADATA_TABLE := EGO_ATTR_METADATA_TABLE();
930 l_attr_metadata_obj EGO_ATTR_METADATA_OBJ;
931 l_sql_query LONG;
932
933 CURSOR attrs_cursor (
934 cp_application_id IN NUMBER
935 ,cp_attr_group_type IN VARCHAR2
936 ,cp_attr_group_name IN VARCHAR2
937 ) IS
938 -- 9478005, add hint for 10g optimizer issue, it has duplicate data at 10g
939 SELECT /*+ OPTIMIZER_FEATURES_ENABLE('9.2.0') */ EXT.ATTR_ID,
940 FLX_EXT.ATTR_GROUP_ID,
941 A.END_USER_COLUMN_NAME,
942 TL.FORM_LEFT_PROMPT,
943 EXT.DATA_TYPE,
944 FC.MEANING DATA_TYPE_MEANING,
945 A.COLUMN_SEQ_NUM,
946 EXT.UNIQUE_KEY_FLAG,
947 A.DEFAULT_VALUE,
948 EXT.INFO_1,
949 VS.MAXIMUM_SIZE,
950 A.REQUIRED_FLAG,
951 A.APPLICATION_COLUMN_NAME,
952 VS.FLEX_VALUE_SET_ID,
953 VS.VALIDATION_TYPE,
954 VS.MINIMUM_VALUE,
955 VS.MAXIMUM_VALUE,
956 EXT.UOM_CLASS,
957 UOM.UOM_CODE,
958 EXT.VIEW_IN_HIERARCHY_CODE,
959 EXT.EDIT_IN_HIERARCHY_CODE
960 FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT,
961 FND_DESCR_FLEX_COLUMN_USAGES A,
962 FND_DESCR_FLEX_COL_USAGE_TL TL,
963 EGO_FND_DF_COL_USGS_EXT EXT,
964 EGO_VS_FORMAT_CODES_V FC,
965 FND_FLEX_VALUE_SETS VS,
966 MTL_UNITS_OF_MEASURE UOM
967 WHERE FLX_EXT.APPLICATION_ID = cp_application_id
968 AND FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
969 AND FLX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
970 AND A.APPLICATION_ID = cp_application_id
971 AND A.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
972 AND A.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
973 AND TL.APPLICATION_ID = cp_application_id
974 AND TL.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
975 AND TL.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
976 AND EXT.APPLICATION_ID = cp_application_id
977 AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
978 AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
979 AND FC.LOOKUP_CODE(+) = EXT.DATA_TYPE
980 AND A.ENABLED_FLAG = 'Y'
981 AND TL.APPLICATION_COLUMN_NAME = A.APPLICATION_COLUMN_NAME
982 AND TL.LANGUAGE = USERENV('LANG')
983 AND EXT.APPLICATION_COLUMN_NAME = A.APPLICATION_COLUMN_NAME
984 AND A.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID (+)
985 AND UOM.UOM_CLASS(+) = EXT.UOM_CLASS
986 AND UOM.BASE_UOM_FLAG(+) = 'Y'
987 ORDER BY A.COLUMN_SEQ_NUM;
988
989 BEGIN
990
991 ----------------------------------------------------------------------------
992 -- The SORT_ATTR_VALUES_FLAG flag records whether any Attributes in this --
993 -- collection have a Value Set of type "Table"; if so, we will need to --
994 -- sort the Attr values when we process a row in order to ensure that any --
995 -- bind values needed by the Value Set are converted before the Value Set --
996 -- is processed --
997 ----------------------------------------------------------------------------
998
999 -------------------------------------------------------
1000 -- The UNIQUE_KEY_ATTRS_COUNT records how many Attrs --
1001 -- in this Attribute Group are part of a Unique Key --
1002 -------------------------------------------------------
1003
1004 --------------------------------------------------------------------
1005 -- The TRANS_ATTRS_COUNT records how many translatable Attributes --
1006 -- this Attribute Group has; it will be used in Update_Row --
1007 --------------------------------------------------------------------
1008
1009 FOR attrs_rec IN attrs_cursor(px_attr_group_metadata_obj.APPLICATION_ID
1010 ,px_attr_group_metadata_obj.ATTR_GROUP_TYPE
1011 ,px_attr_group_metadata_obj.ATTR_GROUP_NAME)
1012 LOOP
1013 l_attr_metadata_obj := EGO_ATTR_METADATA_OBJ(
1017 ,attrs_rec.END_USER_COLUMN_NAME
1014 attrs_rec.ATTR_ID
1015 ,attrs_rec.ATTR_GROUP_ID
1016 ,px_attr_group_metadata_obj.ATTR_GROUP_NAME
1018 ,attrs_rec.FORM_LEFT_PROMPT
1019 ,attrs_rec.DATA_TYPE
1020 ,attrs_rec.DATA_TYPE_MEANING
1021 ,attrs_rec.COLUMN_SEQ_NUM
1022 ,attrs_rec.UNIQUE_KEY_FLAG
1023 ,attrs_rec.DEFAULT_VALUE
1024 ,attrs_rec.INFO_1
1025 ,attrs_rec.MAXIMUM_SIZE
1026 ,attrs_rec.REQUIRED_FLAG
1027 ,attrs_rec.APPLICATION_COLUMN_NAME
1028 ,attrs_rec.FLEX_VALUE_SET_ID
1029 ,attrs_rec.VALIDATION_TYPE
1030 ,attrs_rec.MINIMUM_VALUE
1031 ,attrs_rec.MAXIMUM_VALUE
1032 ,attrs_rec.UOM_CLASS
1033 ,attrs_rec.UOM_CODE
1034 ,null -- DISP_TO_INT_VAL_QUERY
1035 ,null -- INT_TO_DISP_VAL_QUERY
1036 ,'N'
1037 ,attrs_rec.VIEW_IN_HIERARCHY_CODE
1038 ,attrs_rec.EDIT_IN_HIERARCHY_CODE
1039 );
1040
1041 IF (attrs_rec.UNIQUE_KEY_FLAG = 'Y') THEN
1042
1043 px_attr_group_metadata_obj.UNIQUE_KEY_ATTRS_COUNT :=
1044 px_attr_group_metadata_obj.UNIQUE_KEY_ATTRS_COUNT + 1;
1045
1046 END IF;
1047
1048 IF (attrs_rec.DATA_TYPE = EGO_EXT_FWK_PUB.G_TRANS_TEXT_DATA_TYPE) THEN
1049
1050 px_attr_group_metadata_obj.TRANS_ATTRS_COUNT :=
1051 px_attr_group_metadata_obj.TRANS_ATTRS_COUNT + 1;
1052
1053 END IF;
1054
1055 IF (attrs_rec.VALIDATION_TYPE = EGO_EXT_FWK_PUB.G_INDEPENDENT_VALIDATION_CODE OR
1056 attrs_rec.VALIDATION_TYPE = EGO_EXT_FWK_PUB.G_TABLE_VALIDATION_CODE OR
1057 attrs_rec.VALIDATION_TYPE = EGO_EXT_FWK_PUB.G_TRANS_IND_VALIDATION_CODE) THEN--Bug fix 4645598
1058
1059 -----------------------------------------------------------------
1060 -- If this Attribute has a Value Set with Internal and Display --
1061 -- Values, we build SQL to transform one into the other (and --
1062 -- if the Value Set is of type "Table", we set the sort flag --
1063 -- in our Attribute Group metadata object to 'Y') --
1064 -----------------------------------------------------------------
1065
1066 Build_Sql_Queries_For_Value(attrs_rec.FLEX_VALUE_SET_ID
1067 ,attrs_rec.VALIDATION_TYPE
1068 ,px_attr_group_metadata_obj
1069 ,l_attr_metadata_obj);
1070 END IF;
1071
1072 ------------------------------------------------------------------
1073 -- For hierarchy security, we need to keep track of whether any --
1074 -- of the attributes requires propagation (EIH code of LP/AP) --
1075 -- for leaf/all propagation --
1076 ------------------------------------------------------------------
1077 IF (attrs_rec.EDIT_IN_HIERARCHY_CODE = 'LP' OR
1078 attrs_rec.EDIT_IN_HIERARCHY_CODE = 'AP') THEN
1079
1080 px_attr_group_metadata_obj.HIERARCHY_PROPAGATE_FLAG := 'Y';
1081 code_debug('In Build_Attr_Metadata_Table, found LP/AP: '||px_attr_group_metadata_obj.ATTR_GROUP_NAME||' '||attrs_rec.ATTR_ID, 2);
1082
1083 END IF;
1084
1085 l_attr_metadata_table.EXTEND();
1086 l_attr_metadata_table(l_attr_metadata_table.LAST) := l_attr_metadata_obj;
1087
1088 END LOOP;
1089
1090 px_attr_group_metadata_obj.attr_metadata_table := l_attr_metadata_table;
1091
1092 END Build_Attr_Metadata_Table;
1093
1094 ----------------------------------------------------------------------
1095
1096 FUNCTION Get_Attr_Group_Metadata (
1097 p_attr_group_id IN NUMBER DEFAULT NULL
1098 ,p_application_id IN NUMBER DEFAULT NULL
1099 ,p_attr_group_type IN VARCHAR2 DEFAULT NULL
1100 ,p_attr_group_name IN VARCHAR2 DEFAULT NULL
1101 ,p_pick_from_cache IN BOOLEAN DEFAULT TRUE
1102 )
1103 RETURN EGO_ATTR_GROUP_METADATA_OBJ
1104 IS
1105
1106 l_table_index NUMBER;
1107 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
1108
1109 BEGIN
1110
1111 IF (p_pick_from_cache) THEN
1112 l_attr_group_metadata_obj := Find_Attr_Group_In_Cache(p_attr_group_id
1113 ,p_application_id
1114 ,p_attr_group_type
1115 ,p_attr_group_name);
1116 ELSE
1117 l_attr_group_metadata_obj := NULL;
1118 END IF;
1119 code_debug('in Get_Attr_Group_Metadata 1');
1120 ---------------------------------------------------------
1121 -- If we don't have cached data, we query in order to --
1122 -- build a record, which we then cache and also return --
1123 ---------------------------------------------------------
1124 IF (l_attr_group_metadata_obj IS NULL) THEN
1125
1126 l_attr_group_metadata_obj := EGO_ATTR_GROUP_METADATA_OBJ(
1127 p_attr_group_id
1128 ,p_application_id
1129 ,p_attr_group_type
1130 ,p_attr_group_name
1131 ,null -- ATTR_GROUP_DISP_NAME
1135 ,null -- EDIT_PRIVILEGE
1132 ,null -- AGV_NAME
1133 ,null -- MULTI_ROW_CODE
1134 ,null -- VIEW_PRIVILEGE
1136 ,null -- EXT_TABLE_B_NAME
1137 ,null -- EXT_TABLE_TL_NAME
1138 ,null -- EXT_TABLE_VL_NAME
1139 ,'N' -- SORT_ATTR_VALUES_FLAG
1140 ,0 -- UNIQUE_KEY_ATTRS_COUNT
1141 ,0 -- TRANS_ATTRS_COUNT
1142 ,null -- attr_metadata_table
1143 ,null -- ATTR_GROUP_ID_FLAG
1144 ,null -- HIERARCHY_NODE_QUERY
1145 ,null -- HIERARCHY_PROPAGATION_API
1146 ,null -- HIERARCHY_PROPAGATE_FLAG
1147 ,null -- ENABLED_DATA_LEVELS(EGO_DATA_LEVEL_TABLE)
1148 ,null -- VARIANT_CODE
1149 );
1150
1151 ---------------------------------------------------------
1152 -- We query only on Attribute Groups that are enabled. --
1153 -- If we have the three Attribute Group Primary Keys, --
1154 -- we use them; otherwise, we assume the ATTR_GROUP_ID --
1155 -- was passed in, and we use it to query. --
1156 ---------------------------------------------------------
1157 IF (l_attr_group_metadata_obj.APPLICATION_ID IS NOT NULL AND
1158 l_attr_group_metadata_obj.ATTR_GROUP_TYPE IS NOT NULL AND
1159 l_attr_group_metadata_obj.ATTR_GROUP_NAME IS NOT NULL) THEN
1160
1161 SELECT EXT.ATTR_GROUP_ID
1162 ,FLX_TL.DESCRIPTIVE_FLEX_CONTEXT_NAME
1163 ,EXT.AGV_NAME
1164 ,EXT.MULTI_ROW
1165 ,VPF.FUNCTION_NAME
1166 ,EPF.FUNCTION_NAME
1167 ,FLX.APPLICATION_TABLE_NAME
1168 ,FLX_EXT.APPLICATION_TL_TABLE_NAME
1169 ,FLX_EXT.APPLICATION_VL_NAME
1170 ,FLX_EXT.HIERARCHY_NODE_QUERY
1171 ,FLX_EXT.HIERARCHY_PROPAGATION_API
1172 ,EXT.VARIANT
1173 INTO l_attr_group_metadata_obj.ATTR_GROUP_ID
1174 ,l_attr_group_metadata_obj.ATTR_GROUP_DISP_NAME
1175 ,l_attr_group_metadata_obj.AGV_NAME
1176 ,l_attr_group_metadata_obj.MULTI_ROW_CODE
1177 ,l_attr_group_metadata_obj.VIEW_PRIVILEGE
1178 ,l_attr_group_metadata_obj.EDIT_PRIVILEGE
1179 ,l_attr_group_metadata_obj.EXT_TABLE_B_NAME
1180 ,l_attr_group_metadata_obj.EXT_TABLE_TL_NAME
1181 ,l_attr_group_metadata_obj.EXT_TABLE_VL_NAME
1182 ,l_attr_group_metadata_obj.HIERARCHY_NODE_QUERY
1183 ,l_attr_group_metadata_obj.HIERARCHY_PROPAGATION_API
1184 ,l_attr_group_metadata_obj.VARIANT
1185 FROM EGO_FND_DSC_FLX_CTX_EXT EXT
1186 ,FND_DESCRIPTIVE_FLEXS FLX
1187 ,FND_FORM_FUNCTIONS VPF
1188 ,FND_FORM_FUNCTIONS EPF
1189 ,FND_DESCR_FLEX_CONTEXTS_TL FLX_TL
1190 ,EGO_FND_DESC_FLEXS_EXT FLX_EXT
1191 WHERE EXT.APPLICATION_ID = p_application_id
1192 AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
1193 AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
1194 AND VPF.FUNCTION_ID(+) = EXT.VIEW_PRIVILEGE_ID
1195 AND EPF.FUNCTION_ID(+) = EXT.EDIT_PRIVILEGE_ID
1196 AND FLX_TL.APPLICATION_ID = EXT.APPLICATION_ID
1197 AND FLX_TL.DESCRIPTIVE_FLEXFIELD_NAME = EXT.DESCRIPTIVE_FLEXFIELD_NAME
1198 AND FLX_TL.DESCRIPTIVE_FLEX_CONTEXT_CODE = EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE
1199 AND FLX_TL.LANGUAGE = USERENV('LANG')
1200 AND FLX.APPLICATION_ID = EXT.APPLICATION_ID
1201 AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = EXT.DESCRIPTIVE_FLEXFIELD_NAME
1202 AND FLX_EXT.APPLICATION_ID(+) = EXT.APPLICATION_ID
1203 AND FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+) = EXT.DESCRIPTIVE_FLEXFIELD_NAME;
1204
1205 ELSE
1206
1207 SELECT EXT.APPLICATION_ID
1208 ,EXT.DESCRIPTIVE_FLEXFIELD_NAME
1209 ,EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE
1210 ,FLX_TL.DESCRIPTIVE_FLEX_CONTEXT_NAME
1211 ,EXT.AGV_NAME
1212 ,EXT.MULTI_ROW
1213 ,VPF.FUNCTION_NAME
1214 ,EPF.FUNCTION_NAME
1215 ,FLX.APPLICATION_TABLE_NAME
1216 ,FLX_EXT.APPLICATION_TL_TABLE_NAME
1217 ,FLX_EXT.APPLICATION_VL_NAME
1218 ,FLX_EXT.HIERARCHY_NODE_QUERY
1219 ,FLX_EXT.HIERARCHY_PROPAGATION_API
1220 ,EXT.VARIANT
1221 INTO l_attr_group_metadata_obj.APPLICATION_ID
1222 ,l_attr_group_metadata_obj.ATTR_GROUP_TYPE
1223 ,l_attr_group_metadata_obj.ATTR_GROUP_NAME
1224 ,l_attr_group_metadata_obj.ATTR_GROUP_DISP_NAME
1225 ,l_attr_group_metadata_obj.AGV_NAME
1226 ,l_attr_group_metadata_obj.MULTI_ROW_CODE
1227 ,l_attr_group_metadata_obj.VIEW_PRIVILEGE
1228 ,l_attr_group_metadata_obj.EDIT_PRIVILEGE
1229 ,l_attr_group_metadata_obj.EXT_TABLE_B_NAME
1230 ,l_attr_group_metadata_obj.EXT_TABLE_TL_NAME
1231 ,l_attr_group_metadata_obj.EXT_TABLE_VL_NAME
1232 ,l_attr_group_metadata_obj.HIERARCHY_NODE_QUERY
1233 ,l_attr_group_metadata_obj.HIERARCHY_PROPAGATION_API
1234 ,l_attr_group_metadata_obj.VARIANT
1235 FROM EGO_FND_DSC_FLX_CTX_EXT EXT
1236 ,FND_FORM_FUNCTIONS VPF
1237 ,FND_FORM_FUNCTIONS EPF
1238 ,FND_DESCR_FLEX_CONTEXTS_TL FLX_TL
1239 ,FND_DESCRIPTIVE_FLEXS FLX
1243 AND EPF.FUNCTION_ID(+) = EXT.EDIT_PRIVILEGE_ID
1240 ,EGO_FND_DESC_FLEXS_EXT FLX_EXT
1241 WHERE EXT.ATTR_GROUP_ID = l_attr_group_metadata_obj.ATTR_GROUP_ID
1242 AND VPF.FUNCTION_ID(+) = EXT.VIEW_PRIVILEGE_ID
1244 AND EXT.APPLICATION_ID = FLX_TL.APPLICATION_ID
1245 AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = FLX_TL.DESCRIPTIVE_FLEXFIELD_NAME
1246 AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FLX_TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
1247 AND FLX_TL.LANGUAGE = USERENV('LANG')
1248 AND FLX.APPLICATION_ID = FLX_TL.APPLICATION_ID
1249 AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = FLX_TL.DESCRIPTIVE_FLEXFIELD_NAME
1250 AND FLX_EXT.APPLICATION_ID(+) = FLX_TL.APPLICATION_ID
1251 AND FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+) = FLX_TL.DESCRIPTIVE_FLEXFIELD_NAME;
1252
1253 END IF;
1254
1255 --------------------------------------------------------------
1256 -- Add the enabled data Level metadata to the AG object.
1257 --------------------------------------------------------------
1258 code_debug('in Get_Attr_Group_Metadata before Get_Enabled_Data_Levels_For_AG');
1259
1260 l_attr_group_metadata_obj.ENABLED_DATA_LEVELS := Get_Enabled_Data_Levels_For_AG(l_attr_group_metadata_obj.ATTR_GROUP_ID);
1261 code_debug('in Get_Attr_Group_Metadata after Get_Enabled_Data_Levels_For_AG');
1262 -------------------------------------------------------------
1263 -- B/Tl table needn't have ATTR_GROUP_ID column. --
1264 -- We look in FND_COLUMNS to determine if we need to --
1265 -- use ATTR_GROUP_ID column. Going against B table as --
1266 -- all Group Types neednt have TL table. --
1267 -- Assuming here that if B table doesnt have ATTR_GROUP_ID --
1268 -- then TL table will not have it too. --
1269 -------------------------------------------------------------
1270
1271 IF (l_attr_group_metadata_obj.APPLICATION_ID IS NOT NULL) THEN
1272 SELECT DECODE(COUNT(*), 0, 'N', 'Y')
1273 INTO l_attr_group_metadata_obj.ATTR_GROUP_ID_FLAG
1274 FROM FND_COLUMNS
1275 WHERE COLUMN_NAME = 'ATTR_GROUP_ID'
1276 AND APPLICATION_ID = l_attr_group_metadata_obj.APPLICATION_ID
1277 AND TABLE_ID = (SELECT TABLE_ID
1278 FROM FND_TABLES
1279 WHERE TABLE_NAME = l_attr_group_metadata_obj.EXT_TABLE_B_NAME
1280 AND APPLICATION_ID = l_attr_group_metadata_obj.APPLICATION_ID);
1281
1282 END IF;
1283
1284 -----------------------------------------------------------------
1285 -- If the Attr Group has no specified privileges, then we try --
1286 -- to use a default privilege for View and Edit; these default --
1287 -- privileges are hard-coded by Attr Group Type --
1288 -----------------------------------------------------------------
1289 IF (l_attr_group_metadata_obj.VIEW_PRIVILEGE IS NULL) THEN
1290
1291 IF (l_attr_group_metadata_obj.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP') THEN
1292 l_attr_group_metadata_obj.VIEW_PRIVILEGE := 'EGO_VIEW_ITEM';
1293 END IF;
1294
1295 END IF;
1296 IF (l_attr_group_metadata_obj.EDIT_PRIVILEGE IS NULL) THEN
1297
1298 IF (l_attr_group_metadata_obj.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP') THEN
1299 l_attr_group_metadata_obj.EDIT_PRIVILEGE := 'EGO_EDIT_ITEM';
1300 END IF;
1301
1302 END IF;
1303
1304 Build_Attr_Metadata_Table(l_attr_group_metadata_obj);
1305
1306 Add_Attr_Group_To_Cache(l_attr_group_metadata_obj);
1307
1308 END IF;
1309
1310 RETURN l_attr_group_metadata_obj;
1311
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 code_debug('in Get_Attr_Group_Metadata EXCEPTION-'||SQLERRM);
1315 RETURN NULL;
1316
1317 END Get_Attr_Group_Metadata;
1318
1319 ----------------------------------------------------------------------
1320
1321 FUNCTION Find_Metadata_For_Attr (
1322 p_attr_metadata_table IN EGO_ATTR_METADATA_TABLE
1323 ,p_attr_name IN VARCHAR2 DEFAULT NULL
1324 ,p_attr_id IN NUMBER DEFAULT NULL
1325 ,p_db_column_name IN VARCHAR2 DEFAULT NULL
1326 )
1327 RETURN EGO_ATTR_METADATA_OBJ
1328 IS
1329
1330 l_table_index NUMBER;
1331 l_attr_metadata_obj EGO_ATTR_METADATA_OBJ;
1332
1333 BEGIN
1334
1335 code_debug('In Find_Metadata_For_Attr, starting for p_attr_name '||p_attr_name, 2);
1336
1337 l_table_index := p_attr_metadata_table.FIRST;
1338
1339 IF (p_attr_name IS NOT NULL OR
1340 p_attr_id IS NOT NULL OR
1341 p_db_column_name IS NOT NULL) THEN
1342 WHILE (l_table_index <= p_attr_metadata_table.LAST)
1343 LOOP
1344 EXIT WHEN (p_attr_metadata_table(l_table_index).ATTR_NAME = p_attr_name OR
1345 p_attr_metadata_table(l_table_index).ATTR_ID = p_attr_id OR
1346 p_attr_metadata_table(l_table_index).DATABASE_COLUMN = p_db_column_name);
1347
1348 l_table_index := p_attr_metadata_table.NEXT(l_table_index);
1349 END LOOP;
1350
1351 -----------------------------------------------
1352 -- Make sure we have the correct table index --
1353 -----------------------------------------------
1354 IF (l_table_index IS NOT NULL AND
1355 (p_attr_metadata_table(l_table_index).ATTR_NAME = p_attr_name OR
1356 p_attr_metadata_table(l_table_index).ATTR_ID = p_attr_id OR
1357 p_attr_metadata_table(l_table_index).DATABASE_COLUMN = p_db_column_name)) THEN
1358 l_attr_metadata_obj := p_attr_metadata_table(l_table_index);
1359 END IF;
1360 END IF;
1361
1362 code_debug('In Find_Metadata_For_Attr, done', 2);
1363 code_debug('In Find_Metadata_For_Attr, ind: '||l_table_index||' name: '||l_attr_metadata_obj.ATTR_NAME, 2);
1364 code_debug('In Find_Metadata_For_Attr, id: '||l_attr_metadata_obj.ATTR_ID||' dbc: '||l_attr_metadata_obj.DATABASE_COLUMN, 2);
1365 RETURN l_attr_metadata_obj;
1366
1367 END Find_Metadata_For_Attr;
1368
1369 ----------------------------------------------------------------------
1370
1371 FUNCTION Find_Ext_Table_In_Cache (
1372 p_object_id IN NUMBER
1373 )
1374 RETURN EGO_EXT_TABLE_METADATA_OBJ
1375 IS
1376
1377 l_ext_table_metadata_obj EGO_EXT_TABLE_METADATA_OBJ;
1378
1379 BEGIN
1380
1381 IF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_1.OBJECT_ID = p_object_id) THEN
1382
1383 l_ext_table_metadata_obj := G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_1;
1384
1385 ELSIF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_2.OBJECT_ID = p_object_id) THEN
1386
1387 l_ext_table_metadata_obj := G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_2;
1388
1389 ELSIF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_3.OBJECT_ID = p_object_id) THEN
1390
1391 l_ext_table_metadata_obj := G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_3;
1392
1393 ELSIF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_4.OBJECT_ID = p_object_id) THEN
1394
1395 l_ext_table_metadata_obj := G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_4;
1396
1397 ELSIF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_5.OBJECT_ID = p_object_id) THEN
1398
1399 l_ext_table_metadata_obj := G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_5;
1400
1401 END IF;
1402
1403 RETURN l_ext_table_metadata_obj; --It may still be null here, and that's OK
1404
1405 END Find_Ext_Table_In_Cache;
1406
1407 ----------------------------------------------------------------------
1408
1409 PROCEDURE Add_Ext_Table_To_Cache (
1410 p_ext_table_metadata_obj IN EGO_EXT_TABLE_METADATA_OBJ
1411 ) IS
1412
1413 l_oldest_rec_index NUMBER;
1414 l_dummy_time_variable DATE;
1415
1416 BEGIN
1417
1418 IF (Is_Room_In_ET_Batch(G_EXT_TABLE_METADATA_BATCH_1)) THEN
1419
1420 IF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_1 IS NULL) THEN
1421
1422 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_1 := p_ext_table_metadata_obj;
1423 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(1) := SYSDATE;
1424
1425 ELSIF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_2 IS NULL) THEN
1426
1427 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_2 := p_ext_table_metadata_obj;
1428 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(2) := SYSDATE;
1429
1430 ELSIF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_3 IS NULL) THEN
1431
1432 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_3 := p_ext_table_metadata_obj;
1433 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(3) := SYSDATE;
1434
1435 ELSIF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_4 IS NULL) THEN
1436
1437 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_4 := p_ext_table_metadata_obj;
1438 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(4) := SYSDATE;
1439
1440 ELSIF (G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_5 IS NULL) THEN
1441
1442 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_5 := p_ext_table_metadata_obj;
1443 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(5) := SYSDATE;
1444
1445 END IF;
1446
1447 ELSE
1448
1449 l_oldest_rec_index := Find_Oldest_Element_Info(G_EXT_TABLE_METADATA_BATCH_1.access_times_table, l_dummy_time_variable);
1450
1451 IF (l_oldest_rec_index = 1) THEN
1452
1453 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_1 := p_ext_table_metadata_obj;
1457
1454 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(1) := SYSDATE;
1455
1456 ELSIF (l_oldest_rec_index = 2) THEN
1458 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_2 := p_ext_table_metadata_obj;
1459 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(2) := SYSDATE;
1460
1461 ELSIF (l_oldest_rec_index = 3) THEN
1462
1463 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_3 := p_ext_table_metadata_obj;
1464 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(3) := SYSDATE;
1465
1466 ELSIF (l_oldest_rec_index = 4) THEN
1467
1468 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_4 := p_ext_table_metadata_obj;
1469 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(4) := SYSDATE;
1470
1471 ELSIF (l_oldest_rec_index = 5) THEN
1472
1473 G_EXT_TABLE_METADATA_BATCH_1.CACHED_EXT_TABLE_METADATA_5 := p_ext_table_metadata_obj;
1474 G_EXT_TABLE_METADATA_BATCH_1.access_times_table(5) := SYSDATE;
1475
1476 END IF;
1477
1478 END IF;
1479
1480 END Add_Ext_Table_To_Cache;
1481
1482 ----------------------------------------------------------------------
1483
1484 --
1485 -- this is retained only for backward compatibility to R12
1486 -- must not be used from R12C and beyond
1487 --
1488 FUNCTION Get_Ext_Table_Metadata (
1489 p_object_id IN NUMBER
1490 )
1491 RETURN EGO_EXT_TABLE_METADATA_OBJ
1492 IS
1493
1494 l_ext_table_metadata_obj EGO_EXT_TABLE_METADATA_OBJ;
1495 l_obj_name FND_OBJECTS.OBJ_NAME%TYPE;-- 4105308 OBJ_NAME IN FND_OBJECTS LENGTH HAS BEEN INCREASED
1496 l_pk1_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1497 l_pk2_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1498 l_pk3_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1499 l_pk4_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1500 l_pk5_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1501 l_class_code_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1502 l_num_data_level_cols NUMBER;
1503 l_data_level1_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1504 l_data_level2_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1505 l_data_level3_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
1506
1507 CURSOR data_level_meanings_cursor (cp_obj_name VARCHAR2)
1508 IS
1509 SELECT MEANING
1510 FROM FND_LOOKUP_VALUES
1511 WHERE LOOKUP_TYPE = 'EGO_EF_DATA_LEVEL'
1512 AND ATTRIBUTE1 = cp_obj_name
1513 AND VIEW_APPLICATION_ID = 0
1514 ORDER BY ATTRIBUTE2;
1515
1516 BEGIN
1517
1518 l_ext_table_metadata_obj := Find_Ext_Table_In_Cache(p_object_id);
1519
1520 IF (l_ext_table_metadata_obj IS NULL) THEN
1521
1522 SELECT O.OBJ_NAME
1523 ,O.PK1_COLUMN_NAME
1524 ,O.PK2_COLUMN_NAME
1525 ,O.PK3_COLUMN_NAME
1526 ,O.PK4_COLUMN_NAME
1527 ,O.PK5_COLUMN_NAME
1528 ,O.PK1_COLUMN_TYPE
1529 ,O.PK2_COLUMN_TYPE
1530 ,O.PK3_COLUMN_TYPE
1531 ,O.PK4_COLUMN_TYPE
1532 ,O.PK5_COLUMN_TYPE
1533 ,E.CLASSIFICATION_COL_NAME
1534 ,E.CLASSIFICATION_COL_TYPE
1535 ,L.ATTRIBUTE2
1536 ,L.ATTRIBUTE3
1537 ,L.ATTRIBUTE4
1538 ,L.ATTRIBUTE5
1539 ,L.ATTRIBUTE6
1540 ,L.ATTRIBUTE7
1541 ,L.ATTRIBUTE8
1542 INTO l_obj_name
1543 ,l_pk1_obj.col_name
1544 ,l_pk2_obj.col_name
1545 ,l_pk3_obj.col_name
1546 ,l_pk4_obj.col_name
1547 ,l_pk5_obj.col_name
1548 ,l_pk1_obj.data_type
1549 ,l_pk2_obj.data_type
1550 ,l_pk3_obj.data_type
1551 ,l_pk4_obj.data_type
1552 ,l_pk5_obj.data_type
1553 ,l_class_code_obj.col_name
1554 ,l_class_code_obj.data_type
1555 ,l_num_data_level_cols
1556 ,l_data_level1_obj.col_name
1557 ,l_data_level1_obj.data_type
1558 ,l_data_level2_obj.col_name
1559 ,l_data_level2_obj.data_type
1560 ,l_data_level3_obj.col_name
1561 ,l_data_level3_obj.data_type
1562 FROM FND_OBJECTS O
1563 ,EGO_FND_OBJECTS_EXT E
1564 ,FND_LOOKUP_VALUES L
1565 WHERE O.OBJECT_ID = p_object_id
1566 AND O.OBJ_NAME = E.OBJECT_NAME
1567 AND L.LOOKUP_TYPE(+) = 'EGO_EF_DATA_LEVEL'
1568 AND L.ATTRIBUTE1(+) = O.OBJ_NAME
1569 AND L.ATTRIBUTE2(+) > 0
1570 AND L.LANGUAGE(+) = USERENV('LANG');
1571
1572 l_ext_table_metadata_obj := EGO_EXT_TABLE_METADATA_OBJ(
1573 p_object_id
1574 ,l_obj_name
1575 ,null -- DATA_LEVEL_MEANING_1
1576 ,null -- DATA_LEVEL_MEANING_2
1577 ,null -- DATA_LEVEL_MEANING_3
1578 ,null -- pk_column_metadata
1579 ,EGO_COL_METADATA_ARRAY(
1580 l_class_code_obj
1581 )
1582 ,null -- data_level_metadata
1583 );
1584
1585 ------------------------------------------------------------
1586 -- Create an array for the primary key column information --
1587 -- and add the array to the ext table metadata object --
1588 ------------------------------------------------------------
1589 IF (l_pk5_obj.COL_NAME IS NOT NULL) THEN
1590 l_ext_table_metadata_obj.pk_column_metadata := EGO_COL_METADATA_ARRAY(
1591 l_pk1_obj
1592 ,l_pk2_obj
1593 ,l_pk3_obj
1594 ,l_pk4_obj
1595 ,l_pk5_obj
1596 );
1597 ELSIF (l_pk4_obj.COL_NAME IS NOT NULL) THEN
1598 l_ext_table_metadata_obj.pk_column_metadata := EGO_COL_METADATA_ARRAY(
1599 l_pk1_obj
1600 ,l_pk2_obj
1601 ,l_pk3_obj
1602 ,l_pk4_obj
1603 );
1604 ELSIF (l_pk3_obj.COL_NAME IS NOT NULL) THEN
1605 l_ext_table_metadata_obj.pk_column_metadata := EGO_COL_METADATA_ARRAY(
1606 l_pk1_obj
1607 ,l_pk2_obj
1608 ,l_pk3_obj
1609 );
1610 ELSIF (l_pk2_obj.COL_NAME IS NOT NULL) THEN
1611 l_ext_table_metadata_obj.pk_column_metadata := EGO_COL_METADATA_ARRAY(
1612 l_pk1_obj
1613 ,l_pk2_obj
1614 );
1615 ELSE -- i.e., only l_pk1_obj.COL_NAME is not NULL
1616 l_ext_table_metadata_obj.pk_column_metadata := EGO_COL_METADATA_ARRAY(
1617 l_pk1_obj
1618 );
1619 END IF;
1620
1621 -----------------------------------------------------------
1622 -- Create an array for the data level column information --
1623 -- and add the array to the ext table metadata object --
1624 -----------------------------------------------------------
1625 IF (l_num_data_level_cols = 1) THEN
1626 l_ext_table_metadata_obj.data_level_metadata := EGO_COL_METADATA_ARRAY(
1627 l_data_level1_obj
1628 );
1629 ELSIF (l_num_data_level_cols = 2) THEN
1630 l_ext_table_metadata_obj.data_level_metadata := EGO_COL_METADATA_ARRAY(
1631 l_data_level1_obj
1632 ,l_data_level2_obj
1633 );
1634 ELSIF (l_num_data_level_cols = 3) THEN
1635 l_ext_table_metadata_obj.data_level_metadata := EGO_COL_METADATA_ARRAY(
1639 );
1636 l_data_level1_obj
1637 ,l_data_level2_obj
1638 ,l_data_level3_obj
1640 END IF;
1641
1642 ----------------------------------------
1643 -- Add the user-friendly names of the --
1644 -- data levels (for error-reporting) --
1645 ----------------------------------------
1646 FOR data_level_rec IN data_level_meanings_cursor(l_obj_name)
1647 LOOP
1648
1649 IF (data_level_meanings_cursor%ROWCOUNT = 1) THEN
1650
1651 l_ext_table_metadata_obj.DATA_LEVEL_MEANING_1 := data_level_rec.MEANING;
1652
1653 ELSIF (data_level_meanings_cursor%ROWCOUNT = 2) THEN
1654
1655 l_ext_table_metadata_obj.DATA_LEVEL_MEANING_2 := data_level_rec.MEANING;
1656
1657 ELSIF (data_level_meanings_cursor%ROWCOUNT = 3) THEN
1658
1659 l_ext_table_metadata_obj.DATA_LEVEL_MEANING_3 := data_level_rec.MEANING;
1660
1661 END IF;
1662
1663 END LOOP;
1664
1665 Add_Ext_Table_To_Cache(l_ext_table_metadata_obj);
1666
1667 END IF;
1668
1669 RETURN l_ext_table_metadata_obj;
1670
1671 EXCEPTION
1672 WHEN NO_DATA_FOUND THEN
1673 RETURN NULL;
1674
1675 END Get_Ext_Table_Metadata;
1676
1677 ---------------------------------------------------
1678 -- Miscellaneous Common Procedures and Functions --
1679 ---------------------------------------------------
1680
1681 ----------------------------------------------------------------------
1682
1683 -- This function's allowable p_mode values are:
1684 -- 'NAMES': returns a comma-delimited list of column names
1685 -- 'VALUES': returns a comma-delimited list of column values
1686 -- 'EQUALS': returns a list of the form 'Name1 = Value1 AND Name2 = Value2...'
1687 -- 'VALUES_ALL_CC': as 'VALUES', but also has all *related* Classification Codes
1688
1689 -- Note: This function does not currently support 'DATE'-type columns.
1690
1691 FUNCTION Get_List_For_Table_Cols (
1692 p_col_metadata_array IN EGO_COL_METADATA_ARRAY
1693 ,p_col_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1694 ,p_mode IN VARCHAR2
1695 ,p_use_binds IN BOOLEAN DEFAULT FALSE
1696 ,p_prefix IN VARCHAR2 DEFAULT NULL
1697 )
1698 RETURN VARCHAR2
1699 IS
1700
1701 l_separator VARCHAR2(6);
1702 l_col_name VARCHAR2(30);
1703 l_col_type VARCHAR2(8);
1704 l_col_values_index NUMBER;
1705 l_col_value VARCHAR2(1000);
1706 l_col_string VARCHAR2(1775) := '';
1707 l_prev_string_index NUMBER;
1708 l_string_index NUMBER;
1709 l_next_element VARCHAR2(150);
1710 l_is_last_element BOOLEAN;
1711 l_val_begin_pos NUMBER;
1712
1713 BEGIN
1714
1715 code_debug('In Get_List_For_Table_Cols for mode - '||p_mode||' prefix - '||p_prefix||', starting', 2);
1716 IF p_use_binds THEN
1717 code_debug('In Get_List_For_Table_Cols using binds ', 2);
1718 ELSE
1719 code_debug('In Get_List_For_Table_Cols using NO binds ', 2);
1720 END IF;
1721
1722 IF (p_col_name_value_pairs IS NOT NULL OR p_mode = 'NAMES') THEN
1723 --------------------------------------------------------------
1724 -- Figure out the separator for this list based on the mode --
1725 --------------------------------------------------------------
1726 IF (UPPER(p_mode) = 'EQUALS') THEN
1727 l_separator := ' AND ';
1728 ELSE
1729 l_separator := ', ';
1730 END IF;
1731
1732 IF (p_col_metadata_array IS NOT NULL AND p_col_metadata_array.COUNT > 0) THEN
1733
1734 FOR i IN p_col_metadata_array.FIRST .. p_col_metadata_array.LAST
1735 LOOP
1736 code_debug('In Get_List_For_Table_Cols for col - '||i||' - '||p_col_metadata_array(i).COL_NAME);
1737 IF (p_col_metadata_array(i).COL_NAME IS NOT NULL) THEN
1738 --------------------------------------------------------
1739 -- Append the separator to the previous list element, --
1740 -- and prepend the prefix argument to the first list --
1741 -- element. --
1742 --------------------------------------------------------
1743 IF (i = p_col_metadata_array.FIRST) THEN
1744 IF (p_use_binds) THEN
1745 FND_DSQL.Add_Text(p_prefix);
1746 END IF;
1747 l_col_string := l_col_string || p_prefix;
1748 ELSE
1749 IF (p_use_binds) THEN
1750 FND_DSQL.Add_Text(l_separator);
1751 END IF;
1752 l_col_string := l_col_string || l_separator;
1753 END IF;
1754
1755 ------------------------------------------------
1756 -- Loop for each column in the metadata array --
1757 ------------------------------------------------
1758 l_col_name := p_col_metadata_array(i).COL_NAME;
1759 l_col_value := NULL;
1760 -----------------------------------------------
1761 -- Get the column type for this column value --
1762 -----------------------------------------------
1763 l_col_type := p_col_metadata_array(i).DATA_TYPE;
1764
1765 IF (UPPER(p_mode) <> 'NAMES') THEN
1766
1767 ------------------------------------------------------
1768 -- Try to find the index for this column's value by --
1772 ------------------------------------------------------
1769 -- matching up the column name with l_col_name, and --
1770 -- then get the value itself (we may not find the --
1771 -- value or it may be NULL, which will be fine). --
1773 IF (p_col_name_value_pairs IS NOT NULL AND
1774 p_col_name_value_pairs.COUNT > 0) THEN
1775
1776 l_col_values_index := p_col_name_value_pairs.FIRST;
1777 WHILE (l_col_values_index <= p_col_name_value_pairs.LAST)
1778 LOOP
1779 IF (p_col_name_value_pairs(l_col_values_index).NAME = l_col_name) THEN
1780 l_col_value := p_col_name_value_pairs(l_col_values_index).VALUE;
1781 END IF;
1782 l_col_values_index := p_col_name_value_pairs.NEXT(l_col_values_index);
1783 END LOOP;
1784 END IF;
1785 END IF; -- (UPPER(p_mode) <> 'NAMES')
1786
1787 --------------------------------------------
1788 -- 4). Add this column's info to the list --
1789 --------------------------------------------
1790 IF (UPPER(p_mode) = 'NAMES') THEN
1791 IF (p_use_binds) THEN
1792 FND_DSQL.Add_Text(l_col_name);
1793 END IF;
1794 l_col_string := l_col_string || l_col_name;
1795
1796 ELSIF (UPPER(p_mode) = 'VALUES' OR UPPER(p_mode) = 'VALUES_ALL_CC') THEN
1797 IF (l_col_value IS NULL) THEN
1798 IF (p_use_binds) THEN
1799 FND_DSQL.Add_Text('NULL');
1800 END IF;
1801 l_col_string := l_col_string || 'NULL';
1802 ELSE
1803 IF (l_col_type = 'NUMBER' OR l_col_type = 'INTEGER') THEN
1804 IF (p_use_binds) THEN
1805 EGO_USER_ATTRS_DATA_PVT.Add_Bind(p_value => TO_NUMBER(l_col_value));
1806 END IF;
1807 l_col_string := l_col_string || l_col_value;
1808 ELSIF (l_col_type = 'VARCHAR' OR l_col_type = 'VARCHAR2') THEN
1809 IF (p_use_binds) THEN
1810 EGO_USER_ATTRS_DATA_PVT.Add_Bind(p_value => l_col_value);
1811 END IF;
1812 l_col_string := l_col_string ||''''|| l_col_value ||'''';
1813 END IF;
1814 END IF;
1815
1816 ELSIF (UPPER(p_mode) = 'EQUALS') THEN
1817 IF (l_col_value IS NULL) THEN
1818 IF (p_use_binds) THEN
1819 FND_DSQL.Add_Text(l_col_name || ' IS NULL');
1820 END IF;
1821 l_col_string := l_col_string || l_col_name || ' IS NULL';
1822 ELSE
1823 IF (l_col_type = 'NUMBER' OR l_col_type = 'INTEGER') THEN
1824 IF (p_use_binds) THEN
1825 FND_DSQL.Add_Text(l_col_name || ' = ');
1826 EGO_USER_ATTRS_DATA_PVT.Add_Bind(p_value => TO_NUMBER(l_col_value));
1827 END IF;
1828 l_col_string := l_col_string || l_col_name || ' = ' || l_col_value;
1829 ELSIF (l_col_type = 'VARCHAR' OR l_col_type = 'VARCHAR2') THEN
1830 IF (p_use_binds) THEN
1831 FND_DSQL.Add_Text(l_col_name || ' = ');
1832 EGO_USER_ATTRS_DATA_PVT.Add_Bind(p_value => l_col_value);
1833 END IF;
1834 l_col_string := l_col_string ||l_col_name || ' = ''' || l_col_value || '''';
1835 END IF;
1836 END IF;
1837 END IF; -- (UPPER(p_mode) = 'EQUALS')
1838 END IF; -- p_col_metadata_array(i).COL_NAME IS NOT NULL
1839 END LOOP;
1840
1841 -------------------------------------------------------------------------
1842 -- 5). If the mode is 'VALUES_ALL_CC', append the related Class Codes --
1843 -- by looping through the passed-in name/value pairs again to find all --
1844 -- elements starting with 'RELATED_CLASS_CODE_LIST' --
1845 -------------------------------------------------------------------------
1846 IF (UPPER(p_mode) = 'VALUES_ALL_CC' AND
1847 p_col_name_value_pairs IS NOT NULL AND
1848 p_col_name_value_pairs.COUNT > 0) THEN
1849
1850 -------------------------------------------------------------------
1851 -- Assume the first element in the metadata array represents the --
1852 -- class code column. --
1853 -------------------------------------------------------------------
1854 l_col_type := p_col_metadata_array(p_col_metadata_array.FIRST).DATA_TYPE;
1855 l_col_values_index := p_col_name_value_pairs.FIRST;
1856 WHILE (l_col_values_index <= p_col_name_value_pairs.LAST)
1857 LOOP
1858
1859 IF (INSTR(UPPER(p_col_name_value_pairs(l_col_values_index).NAME), 'RELATED_CLASS_CODE_LIST') <> 0 AND
1860 p_col_name_value_pairs(l_col_values_index).VALUE IS NOT NULL AND
1861 LENGTH(p_col_name_value_pairs(l_col_values_index).VALUE) > 0) THEN
1862
1863 -------------------------------------------------------
1864 -- Append the separator to the previous list element --
1865 -------------------------------------------------------
1866 IF (LENGTH(l_col_string) > 0) THEN
1867 IF (p_use_binds) THEN
1868 FND_DSQL.Add_Text(l_separator);
1869 END IF;
1870 l_col_string := l_col_string || l_separator;
1871 ELSE
1872 IF (p_use_binds) THEN
1873 FND_DSQL.Add_Text(p_prefix);
1874 END IF;
1875 l_col_string := l_col_string || p_prefix;
1876 END IF;
1877
1878 -----------------------------------
1882
1879 -- Append the related class code --
1880 -----------------------------------
1881 IF (p_use_binds) THEN
1883 -- in the case of using binds, we have to parse the value string to bind
1884 -- each individual value separately.
1885 l_prev_string_index := 1;
1886 l_is_last_element := FALSE;
1887
1888 LOOP
1889
1890 l_string_index := INSTR(p_col_name_value_pairs(l_col_values_index).VALUE, ',', l_prev_string_index);
1891
1892 IF (l_string_index = 0) THEN
1893 l_string_index := LENGTH(p_col_name_value_pairs(l_col_values_index).VALUE) + 1;
1894 l_is_last_element := TRUE;
1895 END IF;
1896
1897 l_next_element := SUBSTR(p_col_name_value_pairs(l_col_values_index).VALUE
1898 ,l_prev_string_index
1899 ,l_string_index - l_prev_string_index);
1900 IF (l_col_type = 'NUMBER' OR l_col_type = 'INTEGER') THEN
1901 EGO_USER_ATTRS_DATA_PVT.Add_Bind(p_value => TO_NUMBER(l_next_element));
1902 ELSIF (l_col_type = 'VARCHAR' OR l_col_type = 'VARCHAR2') THEN
1903 l_val_begin_pos := INSTR(l_next_element, '''') + 1;
1904 EGO_USER_ATTRS_DATA_PVT.Add_Bind( p_value =>
1905 SUBSTR(l_next_element,l_val_begin_pos,
1906 INSTR(l_next_element, '''', -1, 1) - l_val_begin_pos)
1907 );
1908 END IF;
1909
1910 EXIT WHEN (l_is_last_element);
1911
1912 l_prev_string_index := l_string_index + 1;
1913 FND_DSQL.Add_Text(', ');
1914
1915 END LOOP;
1916
1917 END IF;
1918
1919 -- append the value string when binds aren't being considered
1920 l_col_string := l_col_string ||
1921 p_col_name_value_pairs(l_col_values_index).VALUE;
1922
1923 END IF;
1924
1925 l_col_values_index := p_col_name_value_pairs.NEXT(l_col_values_index);
1926 END LOOP;
1927
1928 END IF;
1929 END IF;
1930 END IF;
1931
1932 code_debug('In Get_List_For_Table_Cols for mode '||p_mode||', done', 2);
1933 RETURN l_col_string;
1934
1935 END Get_List_For_Table_Cols;
1936
1937 ----------------------------------------------------------------------
1938
1939 FUNCTION Create_DB_Col_Alias_If_Needed (
1940 p_attr_metadata_obj IN EGO_ATTR_METADATA_OBJ
1941 ) RETURN VARCHAR2
1942 IS
1943
1944 l_db_column_alias VARCHAR2(90);
1945
1946 BEGIN
1947
1948 ----------------------------------------
1949 -- Start with just the DB column name --
1950 ----------------------------------------
1951 l_db_column_alias := p_attr_metadata_obj.DATABASE_COLUMN;
1952
1953 -------------------------------------------------------------------------
1954 -- Set up an alias to convert Date, Number values to formatted strings --
1955 -------------------------------------------------------------------------
1956 IF (p_attr_metadata_obj.DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_NUMBER_DATA_TYPE) THEN
1957
1958 l_db_column_alias := 'TO_CHAR('||p_attr_metadata_obj.DATABASE_COLUMN||')';
1959
1960 ---------------------------------------------------------------
1961 -- In the Date and Date Time case, use SUBSTR to get around --
1962 -- the strange PL/SQL behavior of adding an initial space to --
1963 -- Dates that have been TO_CHAR'ed --
1964 ---------------------------------------------------------------
1965 ELSIF (p_attr_metadata_obj.DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_DATE_DATA_TYPE) THEN
1966
1967 l_db_column_alias := 'SUBSTR(TO_CHAR(TRUNC('||p_attr_metadata_obj.DATABASE_COLUMN||'), '''||EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||'''), 2)';
1968
1969 ELSIF (p_attr_metadata_obj.DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_DATE_TIME_DATA_TYPE) THEN
1970
1971 l_db_column_alias := 'SUBSTR(TO_CHAR('||p_attr_metadata_obj.DATABASE_COLUMN||', '''||EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||'''), 2)';
1972
1973 END IF;
1974
1975 RETURN l_db_column_alias;
1976
1977 END Create_DB_Col_Alias_If_Needed;
1978
1979 /*
1980 * Bug:11854366
1981 * Description:This function return database column alias with the table or view alias
1982 */
1983 FUNCTION Create_DB_Col_Alias_If_Needed (
1984 p_attr_metadata_obj IN EGO_ATTR_METADATA_OBJ
1985 ,table_view_alias IN VARCHAR2
1986 ) RETURN VARCHAR2
1987 IS
1988
1989 l_db_column_alias VARCHAR2(90);
1990
1991 BEGIN
1992
1993 ----------------------------------------
1994 -- Start with just the DB column name --
1995 ----------------------------------------
1996 l_db_column_alias := table_view_alias||'.'||p_attr_metadata_obj.DATABASE_COLUMN;
1997
1998 -------------------------------------------------------------------------
1999 -- Set up an alias to convert Date, Number values to formatted strings --
2000 -------------------------------------------------------------------------
2001 IF (p_attr_metadata_obj.DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_NUMBER_DATA_TYPE) THEN
2002
2003 l_db_column_alias := 'TO_CHAR('||table_view_alias||'.'||p_attr_metadata_obj.DATABASE_COLUMN||')';
2004
2005 ---------------------------------------------------------------
2009 ---------------------------------------------------------------
2006 -- In the Date and Date Time case, use SUBSTR to get around --
2007 -- the strange PL/SQL behavior of adding an initial space to --
2008 -- Dates that have been TO_CHAR'ed --
2010 ELSIF (p_attr_metadata_obj.DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_DATE_DATA_TYPE) THEN
2011
2012 l_db_column_alias := 'SUBSTR(TO_CHAR(TRUNC('||table_view_alias||'.'||p_attr_metadata_obj.DATABASE_COLUMN||'), '''||EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||'''), 2)';
2013
2014 ELSIF (p_attr_metadata_obj.DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_DATE_TIME_DATA_TYPE) THEN
2015
2016 l_db_column_alias := 'SUBSTR(TO_CHAR('||table_view_alias||'.'||p_attr_metadata_obj.DATABASE_COLUMN||', '''||EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||'''), 2)';
2017
2018 END IF;
2019
2020 RETURN l_db_column_alias;
2021
2022 END Create_DB_Col_Alias_If_Needed;
2023
2024 ----------------------------------------------------------------------
2025 --bug 5094087
2026 -------------------------------------------------------------------------------------
2027 -- API Name: Get_User_Pref_Date_Time_Val --
2028 -- --
2029 -- Description:This Function retruns the Formatted Date or Date Time Value --
2030 -- depending on the type of the Attribute Passed in and the Value passed in --
2031 -- Parameters: The Value of date or DateTime and the Attribute Type with X for --
2032 -- Date Type or Y for Date_time Type --
2033 -------------------------------------------------------------------------------------
2034 FUNCTION Get_User_Pref_Date_Time_Val (
2035 p_date IN DATE
2036 ,p_attr_type IN VARCHAR2
2037 ,x_return_status OUT NOCOPY VARCHAR2
2038 ,x_msg_count OUT NOCOPY NUMBER
2039 ,x_msg_data OUT NOCOPY VARCHAR2
2040 ) RETURN VARCHAR2 IS
2041
2042 l_api_name VARCHAR2(30):='Get_User_Pref_Date_Time_Val';
2043 l_attr_int_value VARCHAR2(100);
2044 l_date_factor VARCHAR2(30);
2045 l_time_factor VARCHAR2(30);
2046 l_space_pos NUMBER;
2047 BEGIN
2048 -- check for valid parameters, return message if invalid.
2049 IF p_attr_type NOT IN ('X','Y') THEN
2050 x_return_status := FND_API.G_RET_STS_ERROR;
2051 FND_MESSAGE.Set_Name('EGO', 'EGO_API_INVALID_PARAMS');
2052 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
2053 FND_MESSAGE.Set_Token('PROC_NAME"', l_api_name);
2054 FND_MSG_PUB.Add;
2055 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2056 ,p_count => x_msg_count
2057 ,p_data => x_msg_data);
2058 RETURN p_date;
2059 END IF;
2060 --return the date as it is if it is null or ''
2061 IF (p_date IS NULL OR p_date = '') THEN
2062 RETURN p_date;
2063 END IF;
2064
2065 --process only if the attribute is of type date or date time
2066 l_attr_int_value := TRIM(TO_CHAR(p_date,EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT));
2067 l_space_pos := Instr(l_attr_int_value,' ');
2068 l_time_factor := SUBSTR(l_attr_int_value,l_space_pos+1);
2069 l_attr_int_value := TO_CHAR(p_date);
2070 IF p_attr_type = 'Y' THEN
2071 l_attr_int_value := l_attr_int_value||' '||l_time_factor;
2072 END IF;
2073 RETURN l_attr_int_value;
2074 EXCEPTION
2075 WHEN OTHERS THEN
2076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2077 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
2078 FND_MESSAGE.SET_TOKEN('ROUTINE',G_PKG_NAME||l_api_name);
2079 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE);
2080 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM);
2081 FND_MSG_PUB.Add;
2082 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2083 ,p_count => x_msg_count
2084 ,p_data => x_msg_data);
2085 RETURN NULL;
2086 END Get_User_Pref_Date_Time_Val;
2087
2088 ----------------------------------------------------------------------
2089
2090 FUNCTION Find_Data_Level_In_Cache (
2091 p_data_level_id IN NUMBER
2092 )
2093 RETURN EGO_DATA_LEVEL_METADATA_OBJ
2094 IS
2095
2096 l_data_level_metadata_obj EGO_DATA_LEVEL_METADATA_OBJ := NULL;
2097
2098 BEGIN
2099 IF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_1.DATA_LEVEL_ID = p_data_level_id) THEN
2100 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_1;
2101 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_2.DATA_LEVEL_ID = p_data_level_id) THEN
2102 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_2;
2103 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_3.DATA_LEVEL_ID = p_data_level_id) THEN
2104 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_3;
2105 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_4.DATA_LEVEL_ID = p_data_level_id) THEN
2106 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_4;
2107 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_5.DATA_LEVEL_ID = p_data_level_id) THEN
2108 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_5;
2109 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_6.DATA_LEVEL_ID = p_data_level_id) THEN
2110 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_6;
2111 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_7.DATA_LEVEL_ID = p_data_level_id) THEN
2112 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_7;
2116 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_9;
2113 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_8.DATA_LEVEL_ID = p_data_level_id) THEN
2114 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_8;
2115 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_9.DATA_LEVEL_ID = p_data_level_id) THEN
2117 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_10.DATA_LEVEL_ID = p_data_level_id) THEN
2118 l_data_level_metadata_obj := G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_10;
2119 END IF;
2120 RETURN l_data_level_metadata_obj; --It may still be null here, and that's OK
2121
2122 END Find_Data_Level_In_Cache;
2123
2124 ----------------------------------------------------------------------
2125 PROCEDURE Add_Data_Level_To_Cache (
2126 p_data_level_metadata_obj IN EGO_DATA_LEVEL_METADATA_OBJ
2127 ) IS
2128
2129 l_oldest_rec_index NUMBER;
2130 l_dummy_time_variable DATE;
2131
2132 BEGIN
2133
2134 IF (Is_Room_In_DL_Batch(G_DATA_LEVEL_METADATA_BATCH_1)) THEN
2135 IF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_1 IS NULL) THEN
2136 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_1 := p_DATA_LEVEL_METADATA_obj;
2137 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(1) := SYSDATE;
2138 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_2 IS NULL) THEN
2139 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_2 := p_DATA_LEVEL_METADATA_obj;
2140 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(2) := SYSDATE;
2141 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_3 IS NULL) THEN
2142 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_3 := p_DATA_LEVEL_METADATA_obj;
2143 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(3) := SYSDATE;
2144 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_4 IS NULL) THEN
2145 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_4 := p_DATA_LEVEL_METADATA_obj;
2146 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(4) := SYSDATE;
2147 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_5 IS NULL) THEN
2148 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_5 := p_DATA_LEVEL_METADATA_obj;
2149 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(5) := SYSDATE;
2150 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_6 IS NULL) THEN
2151 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_6 := p_DATA_LEVEL_METADATA_obj;
2152 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(6) := SYSDATE;
2153 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_7 IS NULL) THEN
2154 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_7 := p_DATA_LEVEL_METADATA_obj;
2155 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(7) := SYSDATE;
2156 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_8 IS NULL) THEN
2157 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_8 := p_DATA_LEVEL_METADATA_obj;
2158 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(8) := SYSDATE;
2159 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_9 IS NULL) THEN
2160 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_9 := p_DATA_LEVEL_METADATA_obj;
2161 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(9) := SYSDATE;
2162 ELSIF (G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_10 IS NULL) THEN
2163 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_10 := p_DATA_LEVEL_METADATA_obj;
2164 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(10) := SYSDATE;
2165 END IF;
2166 ELSE
2167 l_oldest_rec_index := Find_Oldest_Element_Info(G_DATA_LEVEL_METADATA_BATCH_1.access_times_table, l_dummy_time_variable);
2168 IF (l_oldest_rec_index = 1) THEN
2169 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_1 := p_DATA_LEVEL_METADATA_obj;
2170 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(1) := SYSDATE;
2171 ELSIF (l_oldest_rec_index = 2) THEN
2172 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_2 := p_DATA_LEVEL_METADATA_obj;
2173 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(2) := SYSDATE;
2174 ELSIF (l_oldest_rec_index = 3) THEN
2175 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_3 := p_DATA_LEVEL_METADATA_obj;
2176 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(3) := SYSDATE;
2177 ELSIF (l_oldest_rec_index = 4) THEN
2178 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_4 := p_DATA_LEVEL_METADATA_obj;
2179 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(4) := SYSDATE;
2180 ELSIF (l_oldest_rec_index = 5) THEN
2181 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_5 := p_DATA_LEVEL_METADATA_obj;
2182 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(5) := SYSDATE;
2183 ELSIF (l_oldest_rec_index = 6) THEN
2184 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_6 := p_DATA_LEVEL_METADATA_obj;
2185 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(6) := SYSDATE;
2186 ELSIF (l_oldest_rec_index = 7) THEN
2187 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_7 := p_DATA_LEVEL_METADATA_obj;
2188 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(7) := SYSDATE;
2189 ELSIF (l_oldest_rec_index = 8) THEN
2190 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_8 := p_DATA_LEVEL_METADATA_obj;
2191 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(8) := SYSDATE;
2192 ELSIF (l_oldest_rec_index = 9) THEN
2193 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_9 := p_DATA_LEVEL_METADATA_obj;
2194 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(9) := SYSDATE;
2195 ELSIF (l_oldest_rec_index = 10) THEN
2196 G_DATA_LEVEL_METADATA_BATCH_1.CACHED_DATA_LEVEL_METADATA_10 := p_DATA_LEVEL_METADATA_obj;
2197 G_DATA_LEVEL_METADATA_BATCH_1.access_times_table(10) := SYSDATE;
2198 END IF;
2199 END IF;
2200
2201 END Add_Data_Level_To_Cache;
2202
2206 RETURN EGO_DATA_LEVEL_METADATA_OBJ
2203 ----------------------------------------------------------------------
2204
2205 FUNCTION Get_Data_Level_Metadata (p_data_level_id IN NUMBER)
2207 IS
2208
2209 l_data_level_mdata EGO_DATA_LEVEL_METADATA_OBJ;
2210
2211 BEGIN
2212 code_debug ('In Get_Data_Level_Metadata start ');
2213 l_data_level_mdata := Find_Data_Level_In_Cache(p_data_level_id);
2214
2215 IF (l_data_level_mdata IS NULL) THEN
2216 code_debug ('In Get_Data_Level_Metadata creating new data level metadata ');
2217 l_data_level_mdata := EGO_DATA_LEVEL_METADATA_OBJ(null,null,null,null,null,null,null,null,null,null,null,null,null,null);
2218
2219 SELECT DATA_LEVEL_ID
2220 ,DATA_LEVEL_NAME
2221 ,USER_DATA_LEVEL_NAME
2222 ,PK1_COLUMN_NAME
2223 ,PK2_COLUMN_NAME
2224 ,PK3_COLUMN_NAME
2225 ,PK4_COLUMN_NAME
2226 ,PK5_COLUMN_NAME
2227 ,PK1_COLUMN_TYPE
2228 ,PK2_COLUMN_TYPE
2229 ,PK3_COLUMN_TYPE
2230 ,PK4_COLUMN_TYPE
2231 ,PK5_COLUMN_TYPE
2232 INTO l_data_level_mdata.DATA_LEVEL_ID
2233 ,l_data_level_mdata.DATA_LEVEL_NAME
2234 ,l_data_level_mdata.USER_DATA_LEVEL_NAME
2235 ,l_data_level_mdata.PK_COLUMN_NAME1
2236 ,l_data_level_mdata.PK_COLUMN_NAME2
2237 ,l_data_level_mdata.PK_COLUMN_NAME3
2238 ,l_data_level_mdata.PK_COLUMN_NAME4
2239 ,l_data_level_mdata.PK_COLUMN_NAME5
2240 ,l_data_level_mdata.PK_COLUMN_TYPE1
2241 ,l_data_level_mdata.PK_COLUMN_TYPE2
2242 ,l_data_level_mdata.PK_COLUMN_TYPE3
2243 ,l_data_level_mdata.PK_COLUMN_TYPE4
2244 ,l_data_level_mdata.PK_COLUMN_TYPE5
2245 FROM EGO_DATA_LEVEL_VL
2246 WHERE DATA_LEVEL_ID = p_data_level_id;
2247
2248 IF(l_data_level_mdata.DATA_LEVEL_ID IS NOT NULL) THEN
2249 code_debug ('In Get_Data_Level_Metadata creating add data level metadata to cache ');
2250 Add_Data_Level_To_Cache(p_data_level_metadata_obj => l_data_level_mdata);
2251 RETURN l_data_level_mdata;
2252 ELSE
2253 code_debug ('In Get_Data_Level_Metadata cannot find data level metadata ');
2254 RETURN NULL;
2255 END IF;
2256 ELSE
2257 code_debug ('In Get_Data_Level_Metadata data level metadata observed in cache ');
2258 RETURN l_data_level_mdata;
2259 END IF;
2260
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 code_debug ('In Get_Data_Level_Metadata EXCEPTION ');
2264 RETURN l_data_level_mdata;
2265
2266 END Get_Data_Level_Metadata;
2267
2268 ------------------------------------------------------------------------
2269
2270 FUNCTION Get_Enabled_Data_Levels_For_AG (p_attr_group_id IN NUMBER)
2271 RETURN EGO_DATA_LEVEL_TABLE
2272 IS
2273 CURSOR enabled_data_levels(p_attr_group_id IN NUMBER)
2274 IS
2275 SELECT DATA_LEVEL_ID, VIEW_PRIVILEGE_ID,EDIT_PRIVILEGE_ID,RAISE_PRE_EVENT,RAISE_POST_EVENT, DEFAULTING
2276 FROM EGO_ATTR_GROUP_DL
2277 WHERE ATTR_GROUP_ID = p_attr_group_id;
2278
2279 l_data_level_table EGO_DATA_LEVEL_TABLE := EGO_DATA_LEVEL_TABLE();
2280 l_data_level_obj EGO_DATA_LEVEL_ROW_OBJ;
2281 l_data_level_rec enabled_data_levels%ROWTYPE;
2282 l_data_level_mdata_obj EGO_DATA_LEVEL_METADATA_OBJ;
2283 BEGIN
2284 code_debug('in Get_Enabled_Data_Levels_For_AG');
2285 l_data_level_obj := EGO_DATA_LEVEL_ROW_OBJ(null,null,null,null,null,null,null,null);
2286 OPEN enabled_data_levels(p_attr_group_id);
2287 LOOP
2288 FETCH enabled_data_levels INTO l_data_level_rec;
2289 EXIT WHEN enabled_data_levels%NOTFOUND;
2290 code_debug('in Get_Enabled_Data_Levels_For_AG in the LOOP -'||l_data_level_rec.DATA_LEVEL_ID);
2291
2292 l_data_level_obj.DATA_LEVEL_ID := l_data_level_rec.DATA_LEVEL_ID;
2293 l_data_level_obj.RAISE_PRE_EVENT := l_data_level_rec.RAISE_PRE_EVENT;
2294 l_data_level_obj.RAISE_POST_EVENT := l_data_level_rec.RAISE_POST_EVENT;
2295 l_data_level_obj.VIEW_PRIVILEGE_ID := l_data_level_rec.VIEW_PRIVILEGE_ID;
2296 l_data_level_obj.EDIT_PRIVILEGE_ID := l_data_level_rec.EDIT_PRIVILEGE_ID;
2297 l_data_level_obj.DEFAULTING := l_data_level_rec.DEFAULTING;
2298
2299 l_data_level_mdata_obj := Get_Data_Level_Metadata(l_data_level_obj.DATA_LEVEL_ID);
2300
2301 l_data_level_obj.DATA_LEVEL_NAME := l_data_level_mdata_obj.DATA_LEVEL_NAME;
2302 l_data_level_obj.USER_DATA_LEVEL_NAME := l_data_level_mdata_obj.USER_DATA_LEVEL_NAME;
2303
2304 l_data_level_table.EXTEND();
2305 l_data_level_table(l_data_level_table.LAST) := l_data_level_obj;
2306
2307 END LOOP;
2308 CLOSE enabled_data_levels;
2309
2310 RETURN l_data_level_table;
2311
2312 END Get_Enabled_Data_Levels_For_AG;
2313
2314 -----------------------------------------------------------------------
2315
2316 FUNCTION Get_Data_Level_Col_Array( p_application_id IN NUMBER
2317 ,p_attr_group_type IN VARCHAR2)
2318 RETURN EGO_COL_METADATA_ARRAY
2319 IS
2320
2321 CURSOR associated_dl_cursor(p_app_id IN NUMBER, p_ag_type IN VARCHAR2)
2322 IS
2323 SELECT DATA_LEVEL_ID
2324 FROM EGO_DATA_LEVEL_B
2325 WHERE APPLICATION_ID = p_app_id
2326 AND ATTR_GROUP_TYPE = p_ag_type;
2327
2328 l_data_level_id NUMBER;
2329 l_dl_mdata_obj EGO_DATA_LEVEL_METADATA_OBJ;
2330 l_data_level_obj EGO_COL_METADATA_OBJ := EGO_COL_METADATA_OBJ(null, null);
2331 l_dl_col_list VARCHAR2(2000);
2332 l_dl_col_array EGO_COL_METADATA_ARRAY;
2333
2334 BEGIN
2335
2336 l_dl_col_array := EGO_COL_METADATA_ARRAY();
2337 code_debug('In Get_Data_Level_Col_Array starting: p_application_id-'||p_application_id||' p_attr_group_type-'||p_attr_group_type);
2338 l_dl_col_list := ' ';
2342 EXIT WHEN associated_dl_cursor%NOTFOUND;
2339 OPEN associated_dl_cursor(p_application_id, p_attr_group_type);
2340 LOOP
2341 FETCH associated_dl_cursor INTO l_data_level_id;
2343
2344 l_dl_mdata_obj := Get_Data_Level_Metadata(l_data_level_id);
2345 code_debug('In Get_Data_Level_Col_Array l_data_level_id ='||l_data_level_id||' l_dl_col_list-'||l_dl_col_list||' pk1-'||l_dl_mdata_obj.PK_COLUMN_NAME1);
2346 IF(l_dl_mdata_obj.PK_COLUMN_NAME5 IS NOT NULL AND INSTR(l_dl_col_list,l_dl_mdata_obj.PK_COLUMN_NAME5) = 0) THEN
2347 l_data_level_obj.COL_NAME := l_dl_mdata_obj.PK_COLUMN_NAME5;
2348 l_data_level_obj.DATA_TYPE := l_dl_mdata_obj.PK_COLUMN_TYPE5;
2349 l_dl_col_list := l_dl_col_list||' '||l_dl_mdata_obj.PK_COLUMN_NAME5||' ';
2350 l_dl_col_array.EXTEND();
2351 l_dl_col_array(l_dl_col_array.LAST) := EGO_COL_METADATA_OBJ(l_dl_mdata_obj.PK_COLUMN_NAME5, l_dl_mdata_obj.PK_COLUMN_TYPE5);
2352 END IF;
2353 IF(l_dl_mdata_obj.PK_COLUMN_NAME4 IS NOT NULL AND INSTR(l_dl_col_list,l_dl_mdata_obj.PK_COLUMN_NAME4) = 0) THEN
2354 l_data_level_obj.COL_NAME := l_dl_mdata_obj.PK_COLUMN_NAME4;
2355 l_data_level_obj.DATA_TYPE := l_dl_mdata_obj.PK_COLUMN_TYPE4;
2356 l_dl_col_list := l_dl_col_list||' '||l_dl_mdata_obj.PK_COLUMN_NAME4||' ';
2357 l_dl_col_array.EXTEND();
2358 l_dl_col_array(l_dl_col_array.LAST) := EGO_COL_METADATA_OBJ(l_dl_mdata_obj.PK_COLUMN_NAME4, l_dl_mdata_obj.PK_COLUMN_TYPE4);
2359 END IF;
2360 IF(l_dl_mdata_obj.PK_COLUMN_NAME3 IS NOT NULL AND INSTR(l_dl_col_list,l_dl_mdata_obj.PK_COLUMN_NAME3) = 0) THEN
2361 l_data_level_obj.COL_NAME := l_dl_mdata_obj.PK_COLUMN_NAME3;
2362 l_data_level_obj.DATA_TYPE := l_dl_mdata_obj.PK_COLUMN_TYPE3;
2363 l_dl_col_list := l_dl_col_list||' '||l_dl_mdata_obj.PK_COLUMN_NAME3||' ';
2364 l_dl_col_array.EXTEND();
2365 l_dl_col_array(l_dl_col_array.LAST) := EGO_COL_METADATA_OBJ(l_dl_mdata_obj.PK_COLUMN_NAME3, l_dl_mdata_obj.PK_COLUMN_TYPE3);
2366 END IF;
2367 IF(l_dl_mdata_obj.PK_COLUMN_NAME2 IS NOT NULL AND INSTR(l_dl_col_list,l_dl_mdata_obj.PK_COLUMN_NAME2) = 0) THEN
2368 l_data_level_obj.COL_NAME := l_dl_mdata_obj.PK_COLUMN_NAME2;
2369 l_data_level_obj.DATA_TYPE := l_dl_mdata_obj.PK_COLUMN_TYPE2;
2370 l_dl_col_list := l_dl_col_list||' '||l_dl_mdata_obj.PK_COLUMN_NAME2||' ';
2371 l_dl_col_array.EXTEND();
2372 l_dl_col_array(l_dl_col_array.LAST) := EGO_COL_METADATA_OBJ(l_dl_mdata_obj.PK_COLUMN_NAME2, l_dl_mdata_obj.PK_COLUMN_TYPE2);
2373 END IF;
2374 IF(l_dl_mdata_obj.PK_COLUMN_NAME1 IS NOT NULL AND INSTR(l_dl_col_list,l_dl_mdata_obj.PK_COLUMN_NAME1) = 0) THEN
2375 l_data_level_obj.COL_NAME := l_dl_mdata_obj.PK_COLUMN_NAME1;
2376 l_data_level_obj.DATA_TYPE := l_dl_mdata_obj.PK_COLUMN_TYPE1;
2377 l_dl_col_list := l_dl_col_list||' '||l_dl_mdata_obj.PK_COLUMN_NAME1||' ';
2378 l_dl_col_array.EXTEND();
2379 l_dl_col_array(l_dl_col_array.LAST) := EGO_COL_METADATA_OBJ(l_dl_mdata_obj.PK_COLUMN_NAME1, l_dl_mdata_obj.PK_COLUMN_TYPE1);
2380 END IF;
2381 code_debug('In Get_Data_Level_Col_Array ... for '||l_data_level_obj.COL_NAME||' l_dl_col_list-'||l_dl_col_list);
2382 END LOOP;
2383 code_debug('In Get_Data_Level_Col_Array l_dl_col_array.COUNT:'||l_dl_col_array.COUNT);
2384 IF(l_dl_col_array.COUNT <1) THEN
2385 RETURN NULL;
2386 ELSE
2387 RETURN l_dl_col_array;
2388 END IF;
2389
2390 END Get_Data_Level_Col_Array;
2391
2392 -------------------------------------------------------------------------------
2393
2394 FUNCTION Get_Data_Levels_For_AGType ( p_application_id IN NUMBER
2395 ,p_attr_group_type IN VARCHAR2
2396 )
2397 RETURN EGO_DATA_LEVEL_METADATA_TABLE
2398 IS
2399
2400 CURSOR associated_data_levels ( p_application_id IN NUMBER
2401 ,p_attr_group_type IN VARCHAR2
2402 )
2403 IS
2404 SELECT DATA_LEVEL_ID
2405 FROM EGO_DATA_LEVEL_VL
2406 WHERE APPLICATION_ID = p_application_id
2407 AND ATTR_GROUP_TYPE = p_attr_Group_type;
2408
2409 l_dl_mdata EGO_DATA_LEVEL_METADATA_OBJ;
2410 l_dl_id NUMBER;
2411 l_dl_mdata_table EGO_DATA_LEVEL_METADATA_TABLE := EGO_DATA_LEVEL_METADATA_TABLE();
2412
2413 BEGIN
2414
2415 OPEN associated_data_levels(p_application_id, p_attr_Group_type);
2416 LOOP
2417 FETCH associated_data_levels INTO l_dl_id;
2418 EXIT WHEN associated_data_levels%NOTFOUND;
2419 l_dl_mdata_table.EXTEND;
2420 l_dl_mdata_table(l_dl_mdata_table.LAST) := Get_Data_Level_Metadata(l_dl_id);
2421 END LOOP;
2422
2423 RETURN l_dl_mdata_table;
2424
2425 END Get_Data_Levels_For_AGType;
2426
2427 ---------------------------------------------------------------------------------
2428
2429 FUNCTION Get_All_Data_Level_PK_Names ( p_application_id IN NUMBER
2430 ,p_attr_group_type IN VARCHAR2)
2431 RETURN VARCHAR2 IS
2432
2433 CURSOR get_all_pk_names (cp_application_id IN NUMBER
2434 ,cp_attr_group_type IN VARCHAR2)
2435 IS
2436 SELECT DISTINCT pk1_column_name column_name
2437 FROM ego_data_level_b
2438 WHERE application_id=cp_application_id
2439 AND attr_group_type = cp_attr_group_type
2440 AND pk1_column_name IS NOT NULL
2441 UNION
2442 SELECT DISTINCT pk2_column_name column_name
2443 FROM ego_data_level_b
2444 WHERE application_id=cp_application_id
2445 AND attr_group_type = cp_attr_group_type
2446 AND pk2_column_name IS NOT NULL
2447 UNION
2448 SELECT DISTINCT pk3_column_name column_name
2449 FROM ego_data_level_b
2450 WHERE application_id=cp_application_id
2451 AND attr_group_type = cp_attr_group_type
2452 AND pk3_column_name IS NOT NULL
2453 UNION
2454 SELECT DISTINCT pk4_column_name column_name
2455 FROM ego_data_level_b
2456 WHERE application_id=cp_application_id
2457 AND attr_group_type = cp_attr_group_type
2458 AND pk4_column_name IS NOT NULL
2459 UNION
2460 SELECT DISTINCT pk5_column_name column_name
2461 FROM ego_data_level_b
2462 WHERE application_id=cp_application_id
2463 AND attr_group_type = cp_attr_group_type
2464 AND pk5_column_name IS NOT NULL;
2465
2466 l_dl_col_list VARCHAR2(1000) := null;
2467
2468 BEGIN
2469 FOR cr in get_all_pk_names (cp_application_id => p_application_id
2470 ,cp_attr_group_type => p_attr_group_type) LOOP
2471 IF l_dl_col_list IS NULL THEN
2472 l_dl_col_list := cr.column_name;
2473 ELSE
2474 l_dl_col_list := l_dl_col_list||', '||cr.column_name;
2475 END IF;
2476 END LOOP;
2477 return l_dl_col_list;
2478 END Get_All_Data_Level_PK_Names;
2479
2480 ---------------------------------------------------------------------------------
2481
2482 --
2483 -- Bug 12998006. Added caching logic to
2484 -- reduce the number of executions.
2485 -- sreharih. Thu Sep 22 11:33:10 PDT 2011
2486 --
2487 FUNCTION HAS_COLUMN_IN_TABLE (p_table_name IN VARCHAR2
2488 ,p_column_name IN VARCHAR2
2489 )
2490 RETURN VARCHAR2 IS
2491 l_dummy_number NUMBER;
2492 l_value VARCHAR2(10); -- bug 12998006.
2493 l_owner sys.all_tables.owner%TYPE;
2494
2495 /***
2496 -- fix for 12.2 OLP compatible
2497
2498 CURSOR c IS
2499 SELECT 1
2500 FROM dual
2501 WHERE exists (SELECT NULL
2502 FROM sys.all_tab_columns
2503 WHERE table_name = p_table_name
2504 AND column_name = p_column_name
2505 AND owner = l_owner);
2506
2507 CURSOR c1 IS
2508 SELECT owner
2509 FROM sys.all_tables
2510 WHERE table_name = p_table_name;
2511 ***/
2512
2513 -- bug#15835530 fix for 12.2 OLP compatible
2514 CURSOR c IS
2515 SELECT 1
2516 FROM dual
2517 WHERE exists (SELECT NULL
2518 FROM user_synonyms syn, dba_tab_columns col
2519 WHERE syn.synonym_name = p_table_name
2520 AND col.owner = syn.table_owner
2521 AND col.table_name = syn.table_name
2522 AND col.column_name = p_column_name
2523 AND col.owner = l_owner);
2524
2525 CURSOR c1 IS
2526 SELECT table_owner
2527 FROM user_synonyms
2528 WHERE synonym_name = p_table_name;
2529
2530
2531 BEGIN
2532
2533 l_owner := get_cached_owner(p_table_name => p_table_name);
2534
2535 IF l_owner IS NULL THEN
2536 OPEN c1;
2537 FETCH c1 INTO l_owner;
2538 CLOSE c1;
2539
2540 l_owner := nvl(l_owner,'NOTEXISTS');
2541 cache_tableowner_data(p_table_name => p_table_name,
2542 p_value => l_owner);
2543 END IF;
2544
2545 --
2546 -- added to support old nodata found logic.
2547 -- this condition is met when a view name is
2548 -- passed to the procedure. could be buggy but
2549 -- not in scope of the fix 12998006. Ideally this
2550 -- procedure should take owner name as input rather
2551 -- than deriving it.
2552 --
2553
2554 IF l_owner = 'NOTEXISTS' THEN
2555 RETURN FND_API.G_FALSE;
2556 END IF;
2557 --
2558 -- bug 12998006. Add PLSQL session level caching.
2559 -- Added caching to the logic.
2560 -- sreharih. Mon Sep 19 13:36:13 PDT 2011
2561 --
2562 l_value := get_cached_tablecol_data(p_owner => l_owner,
2563 p_table_name => p_table_name,
2564 p_column_name => p_column_name);
2565
2566 IF l_value IS NULL THEN
2567 OPEN c;
2568 FETCH c INTO l_dummy_number;
2569 CLOSE c;
2570
2571 IF l_dummy_number = 1 THEN
2572 l_value := FND_API.G_TRUE;
2573 ELSE
2574 l_value := FND_API.G_FALSE;
2575 END IF;
2576 cache_tablecol_data(p_owner => l_owner,
2577 p_table_name => p_table_name,
2578 p_column_name => p_column_name,
2579 p_value => l_value);
2580
2581 END IF;
2582 RETURN l_value;
2583
2584 EXCEPTION
2585 WHEN OTHERS THEN
2586 --code_debug('has_column_in_table() : '|| 'Exception ' || SQLERRM);
2587 RETURN FND_API.G_FALSE;
2588
2589 END has_column_in_table;
2590
2591
2592 END EGO_USER_ATTRS_COMMON_PVT;
[Home] [Help]