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