1 PACKAGE BODY HXC_ALIAS_TRANSLATOR AS
2 /* $Header: hxcalttlr.pkb 120.8.12020000.2 2012/07/04 09:12:44 amnaraya ship $ */
3
4 g_debug boolean:= hr_utility.debug_enabled;
5
6 -- Bug 13511713
7 CURSOR g_csr_alias_values
8 IS
9 select alias_value_id ,alias_value_name
10 from hxc_alias_values
11 where alias_definition_id = 1
12 and enabled_flag = 'Y'
13 and date_from <= SYSDATE
14 and nvl(date_to,hr_general.end_of_time) >= SYSDATE;
15
16 TYPE AVTAB IS TABLE OF g_csr_alias_values%ROWTYPE;
17 TYPE AD_AVTAB IS TABLE OF AVTAB INDEX BY VARCHAR2(100);
18 g_ad_avtab AD_AVTAB;
19
20
21
22 -- ----------------------------------------------------------------------------
23 -- |---------------------------< do_deposit_translation>----------------------|
24 -- ----------------------------------------------------------------------------
25 PROCEDURE do_deposit_translation
26 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE--hxc_self_service_time_deposit.building_block_attribute_info
27 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
28 ) IS
29
30 -- index pl/sql table
31 l_index_attribute NUMBER;
32 l_index_att_to_match NUMBER;
33 l_alias_definition_id NUMBER;
34 l_alias_old_value_id NUMBER;
35 --l_alias_value_id NUMBER;
36 l_attribute_index NUMBER;
37 l_attribute_found BOOLEAN;
38 l_last_attribute NUMBER;
39 l_index_to_delete NUMBER;
40
41 -- pl/sql table
42 l_tbb_id_reference_table hxc_alias_utility.t_tbb_id_reference;
43 l_alias_val_att_to_match hxc_alias_utility.t_alias_val_att_to_match;
44
45 l_bld_blk_info_type_id NUMBER;
46 l_bld_blk_info_type VARCHAR2(80);
47 l_time_building_block_id NUMBER;
48 l_neg_attribute_id NUMBER;
49 l_segment VARCHAR2(80);
50 l_changed VARCHAR2(80);
51 l_value VARCHAR2(350);
52 l_alias_type VARCHAR2(30);
53
54 l_att_to_delete BOOLEAN;
55
56 l_reference_object VARCHAR2(80);
57 l_prompt VARCHAR2(80);
58 --l_select VARCHAR2(200);
59 --l_where_clause VARCHAR2(300);
60 --l_apps_table VARCHAR2(80);
61
62 --l_number_column_id NUMBER;
63
64 BEGIN
65
66 g_debug:=hr_utility.debug_enabled;
67
68 if g_debug then
69 hr_utility.trace('BEFORE DEPOSIT');
70 end if;
71 --hxc_alias_utility.dump_bb_attribute_info(p_attributes);
72 if g_debug then
73 hr_utility.trace('------------------------');
74 hr_utility.trace('------------------------');
75 hr_utility.trace('------------------------');
76 end if;
77
78 -- create the reference attribute index table for each tbb_id
79 hxc_alias_utility.get_tbb_id_reference_table
80 (p_attributes => p_attributes,
81 p_tbb_id_reference_table => l_tbb_id_reference_table);
82
83
84 -- pick the last attribute and increant it of 1
85 /*
86 if (p_attributes.count = 0) THEN
87 l_last_attribute := 1;
88 else
89 l_last_attribute := p_attributes.last + 1;
90 END IF;
91 */
92
93 -- find also the first negative index available
94 l_neg_attribute_id :=
95 hxc_alias_utility.get_next_negative_attribute_id
96 (p_attributes => p_attributes);
97
98 if g_debug then
99 hr_utility.trace('Joel : l_neg_attribute_id'||l_neg_attribute_id);
100 end if;
101
102 -- now we are going to every attribute and if we need to we are doing the translation
103 l_index_attribute := p_attributes.first;
104 LOOP
105 EXIT WHEN
106 (NOT p_attributes.exists(l_index_attribute));
107
108 -- reset some values
109 l_alias_definition_id := NULL;
110 l_alias_old_value_id := NULL;
111 --l_alias_value_id := NULL;
112
113 if g_debug then
114 hr_utility.trace('Joel : p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY'||p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY);
115 end if;
116
117 -- we found an attribute to translate
118 IF (p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY like 'OTL_ALIAS%') THEN
119
120 IF hxc_alias_utility.process_attribute(p_attributes(l_index_attribute)) THEN
121
122 l_time_building_block_id := p_attributes(l_index_attribute).BUILDING_BLOCK_ID;
123 l_changed := p_attributes(l_index_attribute).CHANGED;
124
125 if g_debug then
126 hr_utility.trace('l_alias_definition_id'||p_attributes(l_index_attribute).ATTRIBUTE2);
127 hr_utility.trace('l_alias_old_value_id'||p_attributes(l_index_attribute).ATTRIBUTE3);
128 hr_utility.trace('l_alias_type'||p_attributes(l_index_attribute).ATTRIBUTE4);
129 end if;
130
131 --l_alias_value_id := p_attributes(l_index_attribute).ATTRIBUTE1;
132 -- look for the alias_definition_id associated
133 l_alias_definition_id := to_number(p_attributes(l_index_attribute).ATTRIBUTE2);
134 --l_alias_old_value_id := to_number(p_attributes(l_index_attribute).ATTRIBUTE3);
135 l_alias_type := p_attributes(l_index_attribute).ATTRIBUTE4;
136
137 IF l_alias_definition_id is null or l_alias_type is null THEN
138 -- get the alias definition from the alias value
139 IF l_alias_definition_id is null THEN
140 l_alias_definition_id :=
141 hxc_alias_utility.get_alias_def_from_value(to_number(p_attributes(l_index_attribute).ATTRIBUTE1));
142 END IF;
143
144 hxc_alias_utility.get_alias_definition_info
145 (p_alias_definition_id => l_alias_definition_id,
146 p_alias_type => l_alias_type,
147 p_reference_object => l_reference_object,
148 p_prompt => l_prompt);
149 END IF;
150
151 if g_debug then
152 hr_utility.trace('l_alias_definition_id'||p_attributes(l_index_attribute).ATTRIBUTE2);
153 hr_utility.trace('l_alias_old_value_id'||p_attributes(l_index_attribute).ATTRIBUTE3);
154 hr_utility.trace('l_alias_type'||p_attributes(l_index_attribute).ATTRIBUTE4);
155 end if;
156
157 IF l_alias_type = 'OTL_ALT_DDF' THEN
158 l_alias_old_value_id := to_number(p_attributes(l_index_attribute).ATTRIBUTE3);
159 END IF;
160 --l_bld_blk_info_type_id := to_number(p_attributes(l_index_attribute).ATTRIBUTE3);
161 --l_segment := p_attributes(l_index_attribute).ATTRIBUTE4;
162 --l_bld_blk_info_type := p_attributes(l_index_attribute).ATTRIBUTE5;
163
164 -- prepare alias_vall_to_match
165 l_att_to_delete := FALSE;
166
167 hxc_alias_utility.get_alias_att_to_match_to_dep
168 (p_alias_definition_id => l_alias_definition_id
169 ,p_alias_old_value_id => l_alias_old_value_id
170 ,p_alias_type => l_alias_type
171 ,p_original_value => p_attributes(l_index_attribute).ATTRIBUTE1
172 ,p_alias_val_att_to_match => l_alias_val_att_to_match
173 ,p_att_to_delete => l_att_to_delete);
174
175 --
176 -- debug
177 --
178
179 if g_debug then
180 hr_utility.trace('before debug table');
181 end if;
182
183 --hxc_alias_utility.dump_alias_val_att_to_match(l_alias_val_att_to_match);
184
185 if g_debug then
186 hr_utility.trace('After debug table');
187 end if;
188
189 -- look for the attribute if the bld_blk_exists in the attributes table
190 -- already.
191 -- for each element of the alias_val_att_to_match we are actually doing the
192 -- translation
193 l_index_att_to_match := l_alias_val_att_to_match.first;
194 LOOP
195 EXIT WHEN
196 (NOT l_alias_val_att_to_match.exists(l_index_att_to_match));
197
198 -- set the information
199 l_bld_blk_info_type := l_alias_val_att_to_match(l_index_att_to_match).BLD_BLK_INFO_TYPE;
200 l_bld_blk_info_type_id := l_alias_val_att_to_match(l_index_att_to_match).BLD_BLK_INFO_TYPE_ID;
201 l_segment := l_alias_val_att_to_match(l_index_att_to_match).segment;
202 -- we are getting the value now the correct value now
203 hxc_alias_utility.get_attribute_to_match_info
204 (p_attribute_to_match => l_alias_val_att_to_match,
205 p_index_in_table => l_index_att_to_match,
206 p_attribute_to_get => l_segment,
207 p_get_value => l_value);
208
209 --l_attribute_index :=
210 hxc_alias_utility.attribute_check
211 (p_bld_blk_info_type_id => l_bld_blk_info_type_id
212 ,p_time_building_block_id => l_time_building_block_id
213 ,p_attributes => p_attributes
214 ,p_tbb_id_reference_table => l_tbb_id_reference_table
215 ,p_attribute_index => l_attribute_index
216 ,p_attribute_found => l_attribute_found
217 );
218
219 if g_debug then
220 hr_utility.trace('l_bld_blk_info_type_id '||l_bld_blk_info_type_id);
221 hr_utility.trace('l_bld_blk_info_type '||l_bld_blk_info_type);
222 end if;
223 -- now we need to check if we need to create an attribute or do an update
224 IF l_attribute_found = FALSE AND l_att_to_delete = FALSE THEN
225
226 if g_debug then
227 hr_utility.trace('create');
228 end if;
229 l_neg_attribute_id := l_neg_attribute_id -1;
230
231 p_attributes.extend;
232 l_last_attribute := p_attributes.last;
233 p_attributes (l_last_attribute) :=
234 hxc_attribute_type
235 (l_neg_attribute_id,
236 l_time_building_block_id,
237 l_bld_blk_info_type,
238 null,
239 null,
240 null,
241 null,
242 null,
243 null,
244 null,
245 null,
246 null,
247 null,
248 null,
249 null,
250 null,
251 null,
252 null,
253 null,
254 null,
255 null,
256 null,
257 null,
258 null,
259 null,
260 null,
261 null,
262 null,
263 null,
264 null,
265 null,
266 null,
267 null,
268 l_bld_blk_info_type_id,
269 1,
270 'Y',
271 'Y',
272 l_bld_blk_info_type,
273 null,
274 null);
275
276
277 /*
278 -- create a new attribute
279 p_attributes(l_neg_attribute_id).TIME_ATTRIBUTE_ID := l_neg_attribute_id;
280 p_attributes(l_neg_attribute_id).BLD_BLK_INFO_TYPE := l_bld_blk_info_type;
281 p_attributes(l_neg_attribute_id).ATTRIBUTE_CATEGORY := l_bld_blk_info_type;
282
283 p_attributes(l_neg_attribute_id).BUILDING_BLOCK_ID := l_time_building_block_id;
284 p_attributes(l_neg_attribute_id).BLD_BLK_INFO_TYPE_ID := l_bld_blk_info_type_id;
285 p_attributes(l_neg_attribute_id).OBJECT_VERSION_NUMBER := 1;
286 p_attributes(l_neg_attribute_id).CHANGED := 'Y';
287 p_attributes(l_neg_attribute_id).NEW := 'Y';
288 */
289
290 -- now we need to place the id of this alias at the right place
291 if g_debug then
292 hr_utility.trace('l_last_attribute '||l_last_attribute);
293 hr_utility.trace('l_segment '||l_segment);
294 hr_utility.trace('p_value_to_set '||l_value);
295 end if;
296
297 hxc_alias_utility.set_attribute_information
298 (p_attributes => p_attributes,
299 p_index_in_table => l_last_attribute,--l_neg_attribute_id,
300 p_attribute_to_set => l_segment,
301 p_value_to_set => l_value);
302
303 -- add the new attribute in the ref table
304 IF l_tbb_id_reference_table.exists (l_time_building_block_id) THEN
305 l_tbb_id_reference_table(l_time_building_block_id).ATTRIBUTE_INDEX :=
306 l_tbb_id_reference_table(l_time_building_block_id).ATTRIBUTE_INDEX ||'|'||l_last_attribute;--l_neg_attribute_id;
307 ELSE
308 l_tbb_id_reference_table(l_time_building_block_id).ATTRIBUTE_INDEX := '|'||l_last_attribute;--l_neg_attribute_id;
309 END IF;
310
311 --l_last_attribute := l_neg_attribute_id -1; --l_last_attribute + 1;
312 --l_neg_attribute_id := l_neg_attribute_id -1;
313
314 ELSE
315 IF (l_attribute_found) THEN
316
317 if g_debug then
318 hr_utility.trace('l_segment'||l_segment);
319 hr_utility.trace('l_value'||l_value);
320 hr_utility.trace('l_changed'||l_changed);
321 end if;
322 -- update the attribute
323 -- now we need to place the id of this alias at the right place
324 IF l_att_to_delete = FALSE THEN
325 hxc_alias_utility.set_attribute_information
326 (p_attributes => p_attributes,
327 p_index_in_table => l_attribute_index,
328 p_attribute_to_set => l_segment,
329 p_value_to_set => l_value);
330 ELSE
331 if g_debug then
332 hr_utility.trace('DELETE');
333 hr_utility.trace('l_attribute_index'||l_attribute_index);
334 hr_utility.trace('l_changed'||l_changed);
335 end if;
336 hxc_alias_utility.set_attribute_information
337 (p_attributes => p_attributes,
338 p_index_in_table => l_attribute_index,
339 p_attribute_to_set => l_segment,
340 p_value_to_set => null);
341 END IF;
342
343 IF p_attributes(l_attribute_index).CHANGED <> 'Y'
344 THEN
345 p_attributes(l_attribute_index).CHANGED :=
346 nvl(l_changed,p_attributes(l_attribute_index).CHANGED);
347 END IF;
348 if g_debug then
349 hr_utility.trace('p_attributes(l_attribute_index).CHANGED'||p_attributes(l_attribute_index).CHANGED);
350 end if;
351 END IF;
352
353 END IF;
354
355 l_index_att_to_match := l_alias_val_att_to_match.next(l_index_att_to_match);
356
357 END LOOP;
358
359 END IF;
360
361 l_index_to_delete := l_index_attribute;
362
363 END IF;
364 -- go to the next attribute
365 l_index_attribute := p_attributes.next(l_index_attribute);
366
367 -- we delete the OTL_ALIAS attribute now
368 IF p_attributes.exists(l_index_to_delete) THEN
369 p_attributes.delete(l_index_to_delete);
370 END if;
371
372 END LOOP;
373
374 --
375
376 --hxc_alias_utility.remove_empty_attribute
377 -- (p_attribute_table => p_attributes);
378
379
380 --
381 -- debug
382 --
383 if g_debug then
384 hr_utility.trace(' AFTER TRANSLATION');
385 end if;
386
387 --hxc_alias_utility.dump_bb_attribute_info(p_attributes);
388
389
390
391
392
393
394
395 END do_deposit_translation;
396
397
398 -- ----------------------------------------------------------------------------
399 -- |----------------< do_retrieval_translation >--------------------|
400 -- ----------------------------------------------------------------------------
401 PROCEDURE do_retrieval_translation
402 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
403 p_tbb_id_reference_table IN OUT NOCOPY hxc_alias_utility.t_tbb_id_reference,
404 p_alias_val_att_to_match IN OUT NOCOPY hxc_alias_utility.t_alias_val_att_to_match,
405 p_item_attribute_category IN VARCHAR2,
406 p_alias_definition_id IN NUMBER,
407 p_alias_value_id IN NUMBER,
408 p_alias_value_name IN VARCHAR2,
409 p_alias_type IN VARCHAR2,
410 p_alias_ref_object IN VARCHAR2,
411 p_tbb_date_reference_table IN OUT NOCOPY hxc_alias_utility.t_tbb_date_reference_table,
412 p_alias_def_start_date IN DATE,
413 p_alias_def_end_date IN DATE,
414 p_alias_att_ref IN OUT NOCOPY hxc_alias_utility.t_alias_att_ref_table,
415 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE)
416
417 IS
418
419 -- index for pl/sql table
420 --l_index_alias_def NUMBER;
421 l_time_building_block_id NUMBER;
422 l_index_next NUMBER;
423 l_index_start NUMBER;
424 l_result VARCHAR2(10);
425 l_index_string VARCHAR2(350);
426 l_index_value_to_match NUMBER;
427 l_number_attribute_to_find NUMBER;
428 l_attribute_match_found NUMBER;
429 --l_attribute_id NUMBER := -1;
430 l_attribute_last NUMBER;
431 --
432 l_value VARCHAR2(350);
433 l_value_to_match VARCHAR2(350);
434 l_value_id VARCHAR2(350);
435 --l_bld_blk_info_type_id NUMBER;
436 --l_segment VARCHAR2(80);
437 --l_bld_blk_info_type VARCHAR2(80);
438
439 l_tbb_start_date DATE;
440 l_tbb_end_date DATE;
441
442 l_time_building_block_ovn NUMBER;
443
444 l_create_otl_alias BOOLEAN := TRUE;
445
446 n number;
447 l_alias_value_id NUMBER;
448
449 -- Bug No: 6943339
450 -- The below cursor was added to pull out the value set formats
451 -- for format conversion from canonical to display formats.
452 -- Takes in a reference object, which is the flex_value_set_id
453 -- and returns the format type.
454
455 CURSOR get_ref_datatype ( p_reference_object NUMBER)
456 IS SELECT format_type
457 FROM fnd_flex_value_sets
458 WHERE flex_value_set_id = p_reference_object;
459
460 l_vset_data_type VARCHAR2(5);
461
462 BEGIN
463
464 l_number_attribute_to_find := p_alias_val_att_to_match.count;
465 -- flowing the alias type we will have different type of
466 -- translation we are supporting value_set_table type only
467 -- for the moment
468 l_time_building_block_id := p_tbb_id_reference_table.first;
469 /*
470 n:=p_tbb_date_reference_table.first;
471 dbms_output.put_line(p_tbb_date_reference_table.count);
472 loop
473 exit when not (p_tbb_date_reference_table.exists(n)) ;
474 dbms_output.put_line(n);
475 dbms_output.put_line(p_tbb_date_reference_table(n).START_TIME);
476 dbms_output.put_line(p_tbb_date_reference_table(N).STOP_TIME);
477 n:=p_tbb_date_reference_table.next(n);
478 end loop;
479 */
480
481
482 LOOP
483 EXIT WHEN
484 (NOT p_tbb_id_reference_table.exists(l_time_building_block_id));
485
486 l_index_string := p_tbb_id_reference_table(l_time_building_block_id).ATTRIBUTE_INDEX;
487
488 --dbms_output.put_line('l_index_string '||l_index_string);
489
490 l_tbb_start_date := SYSDATE;
491 l_tbb_end_date := HR_GENERAL.END_OF_TIME;
492 --dbms_output.put_line('l_time_building_block_id '||l_time_building_block_id);
493
494 IF (p_tbb_date_reference_table.exists(l_time_building_block_id)) THEN
495 l_tbb_start_date := p_tbb_date_reference_table(l_time_building_block_id).START_TIME;
496 l_tbb_end_date := p_tbb_date_reference_table(l_time_building_block_id).STOP_TIME;
497 END IF;
498
499 -- go through the string and check the to_check
500 l_index_start := INSTR(l_index_string,'|',1,1)+1;
501
502 -- reset
503 l_attribute_match_found := 0;
504 l_value := NULL;
505 l_value_id := NULL;
506 --l_bld_blk_info_type_id := NULL;
507 --dbms_output.put_line('p_alias_def_end_date '||p_alias_def_end_date);
508 --dbms_output.put_line('p_alias_def_start_date '||p_alias_def_start_date);
509 --dbms_output.put_line('l_tbb_start_date '||l_tbb_start_date);
510 --dbms_output.put_line('l_tbb_start_date '||l_tbb_start_date);
511
512
513 -- we are processing this block only if the date of the block
514 -- are part of the alias
515 IF (l_tbb_start_date <= p_alias_def_end_date
516 AND l_tbb_start_date >= p_alias_def_start_date) THEN
517
518 LOOP
519 l_index_next := INSTR(l_index_string,'|',l_index_start,1);
520
521 IF(l_index_next = 0) THEN
522 l_result := SUBSTR(l_index_string,
523 l_index_start,
524 length(l_index_string)+1-l_index_start);
525 ELSE
526 l_result := SUBSTR(l_index_string,
527 l_index_start,
528 l_index_next-l_index_start);
529 END IF;
530
531 -- first we need to find if the attribute as the same bld_blk_type_id
532 l_index_value_to_match := p_alias_val_att_to_match.first;
533 --l_value := NULL;
534 --l_value_id := NULL;
535
536 --dbms_output.put_line
537 -- ('l_result: '||l_result);
538
539 --dbms_output.put_line
540 -- ('p_alias_val_att_to_match.count: '
541 -- ||p_alias_val_att_to_match.count);
542
543 LOOP
544 EXIT WHEN
545 (NOT p_alias_val_att_to_match.exists(l_index_value_to_match));
546
547 --dbms_output.put_line
548 -- ('p_attributes(l_result).BLD_BLK_INFO_TYPE: '||p_attributes(l_result).BLD_BLK_INFO_TYPE);
549 --dbms_output.put_line
550 -- ('p_alias_val_att_to_match(l_index_value_to_match).BLD_BLK_INFO_TYPE_ID: '
551 -- ||p_alias_val_att_to_match(l_index_value_to_match).BLD_BLK_INFO_TYPE);
552
553
554 l_time_building_block_ovn := p_attributes(l_result).BUILDING_BLOCK_OVN;
555
556 IF (p_attributes(l_result).BLD_BLK_INFO_TYPE_ID =
557 p_alias_val_att_to_match(l_index_value_to_match).BLD_BLK_INFO_TYPE_ID)
558 THEN
559
560 -- following the type of the alternate we will have two processing
561 -- first if the alias type is 'OTL_ALT_DDF'
562 -- we need to check if the value of the match table is found in
563 -- the attribute table.
564
565 -- we need to find the value
566 hxc_alias_utility.get_attribute_information
567 (p_attributes => p_attributes,
568 p_index_in_table => l_result,
569 p_attribute_to_get => p_alias_val_att_to_match(l_index_value_to_match).segment,
570 p_get_value => l_value);
571
572
573 IF p_alias_type = 'OTL_ALT_DDF' THEN
574
575 -- in this case we need to look if the value to match exists in
576 -- attribute table.
577 -- find the value in the attribute table to match
578 hxc_alias_utility.get_attribute_to_match_info
579 (p_attribute_to_match => p_alias_val_att_to_match,
580 p_index_in_table => l_index_value_to_match,
581 p_attribute_to_get => p_alias_val_att_to_match(l_index_value_to_match).segment,
582 p_get_value => l_value_to_match);
583 /*
584 n:=p_alias_val_att_to_match.first;
585 loop
586 exit when not (p_alias_val_att_to_match.exists(n)) ;
587 dbms_output.put_line('att1 '||p_alias_val_att_to_match(n).ATTRIBUTE1);
588 dbms_output.put_line('att2 '||p_alias_val_att_to_match(n).ATTRIBUTE2);
589 n:=p_alias_val_att_to_match.next(n);
590 end loop;
591 */
592
593 --dbms_output.put_line
594 -- ('l_value: '||l_value);
595 --dbms_output.put_line
596 -- ('l_value_to_match: '||l_value_to_match);
597 IF l_value = l_value_to_match or l_value_to_match is null THEN
598
599 l_attribute_match_found := l_attribute_match_found + 1;
600
601 END IF;
602 --dbms_output.put_line
603 -- ('l_attribute_match_found: '||l_attribute_match_found);
604
605 --l_bld_blk_info_type_id := p_alias_val_att_to_match(l_index_value_to_match).BLD_BLK_INFO_TYPE_ID;
606 --l_bld_blk_info_type := p_alias_val_att_to_match(l_index_value_to_match).BLD_BLK_INFO_TYPE;
607 --l_segment := p_alias_val_att_to_match(l_index_value_to_match).segment;
608 l_value_id := p_alias_value_id;
609
610 ELSE
611
612 -- we need to find the value
613 hxc_alias_utility.get_attribute_information
614 (p_attributes => p_attributes,
615 p_index_in_table => l_result,
616 p_attribute_to_get => p_alias_val_att_to_match(l_index_value_to_match).segment,
617 p_get_value => l_value);
618
619
620 l_attribute_match_found := l_attribute_match_found + 1;
621
622 -- we need to look now if the this alias to match is the id
623 -- if yes then we need to find it
624 IF p_alias_val_att_to_match(l_index_value_to_match).component_type = 'COLUMN_ID' THEN
625 -- find now where is the id
626 --dbms_output.put_line
627 -- ('l_value: '||l_value);
628
629
630 IF l_value_id is not null THEN
631 l_value_id := l_value_id ||'ALIAS_SEPARATOR'|| l_value;
632 ELSE
633 l_value_id := l_value;
634 END IF;
635 --dbms_output.put_line
636 -- ('l_value_id: '||l_value_id);
637
638 --l_bld_blk_info_type_id := p_alias_val_att_to_match(l_index_value_to_match).BLD_BLK_INFO_TYPE_ID;
639 --l_bld_blk_info_type := p_alias_val_att_to_match(l_index_value_to_match).BLD_BLK_INFO_TYPE;
640 --l_segment := p_alias_val_att_to_match(l_index_value_to_match).segment;
641 --ELSE
642 --l_value_id := NULL;
643 END IF;
644
645 IF p_alias_val_att_to_match(l_index_value_to_match).component_type = 'VALUE'
646 THEN
647
648 -- Bug No : 6943339
649 -- Added the below construct for format conversion for the alias values.
650 -- If the alias value to match above is VALUE, the value id has to be assigned
651 -- the value. But the values would be stored in attributes table in canonical
652 -- format, and hence needs a conversion. We are doing this only for Value Set
653 -- - None type, because the translation to canonical is done while deposit
654 -- only for value set none types.
655 -- * Check if there exists a value for the format type in the associative
656 -- array.
657 -- * Pick up the datatype and store in the assoc array, if it doesnt exist
658 -- already.
659 -- * If the format is X ( based on the standard system lookup FIELD_TYPES )
660 -- it is standard date type. Hence convert it to date display format.
661 -- * If the format is N ( again based on FIELD_TYPES ), its number and
662 -- convert to number display format.
663
664
665 IF p_alias_type = 'VALUE_SET_NONE'
666 THEN
667 IF NOT g_vset_fmt.EXISTS(TO_CHAR(p_alias_val_att_to_match(l_index_value_to_match).reference_object))
668 THEN
669 OPEN get_ref_datatype(p_alias_val_att_to_match(l_index_value_to_match).reference_object);
670 FETCH get_ref_datatype
671 INTO l_vset_data_type;
672 CLOSE get_ref_datatype;
673
674 g_vset_fmt(TO_CHAR(p_alias_val_att_to_match(l_index_value_to_match).reference_object))
675 := l_vset_data_type;
676 END IF;
677
678 -- The below conversion constructs are put inside a BEGIN END
679 -- block to avoid any exception for the existing timecards.
680 -- Existing data wont be in canonical format,and if a format conversion
681 -- error occurs, settle for the value stored, ie. just like the
682 -- way it was before this fix.
683
684 BEGIN
685 IF g_vset_fmt(TO_CHAR(p_alias_val_att_to_match(l_index_value_to_match).reference_object)) = 'X'
686 THEN
687 l_value_id := hr_chkfmt.changeformat(l_value,'D',NULL);
688 ELSIF g_vset_fmt(TO_CHAR(p_alias_val_att_to_match(l_index_value_to_match).reference_object)) = 'N'
689 THEN
690 l_value_id := FND_NUMBER.CANONICAL_TO_NUMBER(l_value);
691 ELSE
692 l_value_id := l_value;
693 END IF;
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 l_value_id := l_value;
698 END;
699
700 ELSE
701 l_value_id := l_value;
702
703 END IF;
704
705 END IF;
706
707 END IF;
708
709
710 END IF;
711
712 l_index_value_to_match := p_alias_val_att_to_match.next(l_index_value_to_match);
713
714 END LOOP;
715
716 l_index_start := l_index_next + 1;
717 l_result := NULL;
718
719 EXIT WHEN l_index_next = 0;
720 END LOOP; -- attribute for a tbb_id
721
722 --dbms_output.put_line
723 -- ('l_attribute_match_found: '||l_attribute_match_found);
724 --dbms_output.put_line
725 -- ('l_number_attribute_to_find: '||l_number_attribute_to_find);
726 --dbms_output.put_line
727 -- ('l_value_id: '||l_value_id);
728
729 l_create_otl_alias := FALSE;
730
731 -- we look if we need to do the translation
732 IF l_attribute_match_found = l_number_attribute_to_find
733 and l_value_id is not null THEN
734
735 --dbms_output.put_line
736 -- ('created the attribute: ');
737 -- before adding the row we need to check if the
738 -- time building block has already an alias.
739 --IF not(p_alias_att_ref.exists(l_time_building_block_id)) THEN
740
741 l_create_otl_alias := TRUE;
742
743 --ELSE
744 -- if the type of the alias is PARTIAL then
745 -- we are overwritting this partial
746 -- with this one
747 IF p_alias_att_ref.exists(l_time_building_block_id) THEN
748
749 -- start bug 3899872
750 -- if the time building block has already an alias with the same att cat
751 -- we are not creating a new otl alias
752 IF p_alias_att_ref(l_time_building_block_id).OTL_ALIAS_ATT = p_item_attribute_category
753 AND p_alias_att_ref(l_time_building_block_id).OTL_ALIAS_TYPE = 'FULL'
754 THEN
755
756 l_create_otl_alias := FALSE;
757
758 ELSIF p_alias_att_ref(l_time_building_block_id).OTL_ALIAS_ATT = p_item_attribute_category
759 AND p_alias_att_ref(l_time_building_block_id).OTL_ALIAS_TYPE = 'PARTIAL'
760 THEN
761 -- first we are deleting the partial attribute
762 p_attributes.delete(to_number(p_alias_att_ref(l_time_building_block_id).ATTRIBUTE_INDEX));
763
764 l_create_otl_alias := TRUE;
765
766 END IF;
767 -- end bug 3899872
768
769 END IF;
770
771 IF (l_create_otl_alias) THEN
772
773 -- then we are creating the attribute
774 -- we do the translation
775 -- create a new attribute.
776 g_attribute_id := g_attribute_id - 1;
777 --l_attribute_last := p_attributes.last + 1;
778
779 p_attributes.extend;
780 l_attribute_last := p_attributes.last;-- + 1;
781 p_attributes (l_attribute_last) :=
782 hxc_attribute_type
783 (g_attribute_id,
784 l_time_building_block_id,
785 p_item_attribute_category,
786 l_value_id,
787 p_alias_definition_id,
788 p_alias_value_id,
789 p_alias_type,
790 null,
791 null,
792 null,
793 null,
794 null,
795 null,--10
796 null,
797 null,
798 null,
799 null,
800 null,
801 null,
802 null,
803 null,
804 null,
805 null,--20
806 null,
807 null,
808 null,
809 null,
810 null,
811 null,
812 null,
813 null,
814 p_alias_ref_object,--29
815 p_alias_value_name,--30
816 null,
817 1,
818 'N',
819 'N',
820 p_item_attribute_category,
821 null,
822 l_time_building_block_ovn);
823
824 --p_attributes(l_attribute_last).TIME_ATTRIBUTE_ID := l_attribute_id;
825 --p_attributes(l_attribute_last).BUILDING_BLOCK_ID := l_time_building_block_id;
826 --p_attributes(l_attribute_last).BLD_BLK_INFO_TYPE
827 -- := p_item_attribute_category;
828 --p_attributes(l_attribute_last).ATTRIBUTE_CATEGORY
829 -- := p_item_attribute_category;
830 --p_attributes(l_attribute_last).ATTRIBUTE1 := l_value_id;
831
832 -- we store extra information to do the translation back
833 --p_attributes(l_attribute_last).ATTRIBUTE2 := p_alias_definition_id;
834 --p_attributes(l_attribute_last).ATTRIBUTE3 := p_alias_value_id;
835 --p_attributes(l_attribute_last).ATTRIBUTE4 := p_alias_type;
836 --p_attributes(l_attribute_last).ATTRIBUTE3 := l_bld_blk_info_type_id;
837 --p_attributes(l_attribute_last).ATTRIBUTE4 := l_segment;
838 --p_attributes(l_attribute_last).ATTRIBUTE5 := l_bld_blk_info_type;
839
840 --p_attributes(l_attribute_last).OBJECT_VERSION_NUMBER := 1;
841 --p_attributes(l_attribute_last).CHANGED := 'N';
842 --p_attributes(l_attribute_last).NEW := 'N';
843
844 l_value_id := NULL;
845
846 -- add this otl alias in the reference table
847 p_alias_att_ref(l_time_building_block_id).OTL_ALIAS_TYPE := 'FULL';
848 p_alias_att_ref(l_time_building_block_id).OTL_ALIAS_ATT := p_item_attribute_category;
849 p_alias_att_ref(l_time_building_block_id).ATTRIBUTE_INDEX := l_attribute_last;
850
851 END IF;
852
853 ELSIF l_attribute_match_found <> l_number_attribute_to_find and
854 not(p_alias_att_ref.exists(l_time_building_block_id)) THEN
855
856 -- we do the translation
857 -- create a new attribute.
858 g_attribute_id := g_attribute_id - 1;
859 --l_attribute_last := p_attributes.last + 1;
860
861 if(l_attribute_match_found = 0) then
862 l_alias_value_id := null;
863 else
864 l_alias_value_id := p_alias_value_id;
865 end if;
866
867 p_attributes.extend;
868 l_attribute_last := p_attributes.last;-- + 1;
869 p_attributes (l_attribute_last) :=
870 hxc_attribute_type
871 (g_attribute_id,
872 l_time_building_block_id,
873 p_item_attribute_category,
874 null,
875 p_alias_definition_id,
876 l_alias_value_id,
877 p_alias_type,
878 null,
879 null,
880 null,
881 null,
882 null,
883 null,--10
884 null,
885 null,
886 null,
887 null,
888 null,
889 null,
890 null,
891 null,
892 null,
893 null,--20
894 null,
895 null,
896 null,
897 null,
898 null,
899 null,
900 null,
901 null,
902 p_alias_ref_object,--29
903 p_alias_value_name,--30
904 null,
905 1,
906 'N',
907 'N',
908 p_item_attribute_category,
909 null,
910 null);
911 --p_attributes(l_attribute_last).TIME_ATTRIBUTE_ID := l_attribute_id;
912 --p_attributes(l_attribute_last).BUILDING_BLOCK_ID := l_time_building_block_id;
913 --p_attributes(l_attribute_last).BLD_BLK_INFO_TYPE
914 -- := p_item_attribute_category;
915 --p_attributes(l_attribute_last).ATTRIBUTE_CATEGORY
916 -- := p_item_attribute_category;
917 --p_attributes(l_attribute_last).ATTRIBUTE1 := null;
918
919 -- we store extra information to do the translation back
920 --p_attributes(l_attribute_last).ATTRIBUTE2 := p_alias_definition_id;
921 --p_attributes(l_attribute_last).ATTRIBUTE3 := p_alias_value_id;
922 --p_attributes(l_attribute_last).ATTRIBUTE4 := p_alias_type;
923 --p_attributes(l_attribute_last).ATTRIBUTE3 := l_bld_blk_info_type_id;
924 --p_attributes(l_attribute_last).ATTRIBUTE4 := l_segment;
925 --p_attributes(l_attribute_last).ATTRIBUTE5 := l_bld_blk_info_type;
926
927 --p_attributes(l_attribute_last).OBJECT_VERSION_NUMBER := 1;
928 --p_attributes(l_attribute_last).CHANGED := 'N';
929 --p_attributes(l_attribute_last).NEW := 'N';
930
931 -- add this otl alias in the reference table
932 p_alias_att_ref(l_time_building_block_id).OTL_ALIAS_TYPE := 'PARTIAL';
933 p_alias_att_ref(l_time_building_block_id).ATTRIBUTE_INDEX := l_attribute_last;
934 p_alias_att_ref(l_time_building_block_id).OTL_ALIAS_ATT := p_item_attribute_category;
935
936
937 --p_tbb_id_reference_table.delete(l_time_building_block_id);
938
939 --that means we find one attribute but no translation
940 /*
941 hxc_timecard_message_helper.addErrorToCollection
942 (p_messages
943 ,'HXC_PARTICIAL_TRANSLATION'
944 ,hxc_timecard.c_warning
945 ,null
946 ,null
947 ,hxc_timecard.c_hxc
948 ,null
949 ,null
950 ,null
951 ,null
952 );
953 */
954 END if;
955 END IF; --end of the checking on the date
956
957 l_attribute_match_found := 0;
958 -- go to the next tbb_id
959 l_time_building_block_id := p_tbb_id_reference_table.next(l_time_building_block_id);
960
961 END LOOP;
962
963 /*
964 n:=p_alias_val_att_to_match.first;
965 loop
966 exit when not (p_alias_val_att_to_match.exists(n)) ;
967 dbms_output.put_line('att1 '||p_alias_val_att_to_match(n).BLD_BLK_INFO_TYPE_ID);
968 dbms_output.put_line('att2 '||p_alias_val_att_to_match(n).BLD_BLK_INFO_TYPE);
969 n:=p_alias_val_att_to_match.next(n);
970 end loop;
971 */
972
973 END do_retrieval_translation;
974
975 -- ----------------------------------------------------------------------------
976 -- |----------------< do_retrieval_translation >--------------------|
977 -- ----------------------------------------------------------------------------
978 PROCEDURE do_retrieval_translation
979 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE--hxc_self_service_time_deposit.building_block_attribute_info
980 ,p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE--hxc_self_service_time_deposit.timecard_info
981 ,p_start_time IN DATE DEFAULT sysdate
982 ,p_stop_time IN DATE DEFAULT hr_general.end_of_time
983 ,p_resource_id IN NUMBER -- timekeeper or resource
984 ,p_processing_mode IN VARCHAR2 DEFAULT hxc_alias_utility.c_ss_processing
985 ,p_add_alias_display_value IN BOOLEAN DEFAULT FALSE
986 ,p_add_alias_ref_object IN BOOLEAN DEFAULT FALSE
987 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
988 ) IS
989
990 CURSOR csr_alias_values(p_alias_definition_id NUMBER,
991 p_start_time DATE,
992 p_stop_time DATE)
993 IS
994 select alias_value_id ,alias_value_name
995 from hxc_alias_values
996 where alias_definition_id = p_alias_definition_id
997 and enabled_flag = 'Y'
998 and date_from <= p_stop_time
999 and nvl(date_to,hr_general.end_of_time) >= p_start_time;
1000
1001 -- index for pl/sql table
1002 l_index_alias_def NUMBER;
1003 --l_time_building_block_id NUMBER;
1004 --l_index_next NUMBER;
1005 --l_index_start NUMBER;
1006 --l_result VARCHAR2(10);
1007 --l_index_string VARCHAR2(350);
1008 --l_index_value_to_match NUMBER;
1009 --l_number_attribute_to_find NUMBER;
1010 --l_attribute_match_found NUMBER;
1011 --l_attribute_id NUMBER := -1;
1012 --l_attribute_last NUMBER;
1013
1014 -- pl/sql table
1015 l_alias_def_item_tab hxc_alias_utility.t_alias_def_item;
1016 l_tbb_id_reference_table hxc_alias_utility.t_tbb_id_reference;
1017 l_alias_val_att_to_match hxc_alias_utility.t_alias_val_att_to_match;
1018 l_tbb_date_reference_table hxc_alias_utility.t_tbb_date_reference_table;
1019
1020 l_alias_type hxc_alias_types.alias_type%TYPE;
1021 l_reference_object hxc_alias_types.reference_object%TYPE;
1022 l_prompt hxc_alias_definitions_tl.prompt%TYPE;
1023
1024 --p_attributes HXC_ATTRIBUTE_TABLE_TYPE;
1025 --p_blocks HXC_BLOCK_TABLE_TYPE;
1026 --
1027 --l_value VARCHAR2(350);
1028 --l_value_id VARCHAR2(350);
1029 --l_bld_blk_info_type_id NUMBER;
1030 --l_segment VARCHAR2(80);
1031 --l_bld_blk_info_type VARCHAR2(80);
1032
1033 l_alias_att_ref hxc_alias_utility.t_alias_att_ref_table;
1034
1035 l_stop_time DATE;
1036 l_start_time DATE;
1037
1038 l_old_alias_type hxc_alias_types.alias_type%TYPE;
1039
1040 l_alias_value_name VARCHAR2(80);
1041
1042 n number;
1043 l_array_index VARCHAR2(50);
1044 c_alias_value csr_alias_values%ROWTYPE;
1045
1046 BEGIN
1047
1048 g_debug:=hr_utility.debug_enabled;
1049
1050
1051 if g_debug then
1052 hr_utility.trace('p_start_time '||p_start_time);
1053 hr_utility.trace('p_stop_time '||p_stop_time);
1054 hr_utility.trace('p_resource_id '||p_resource_id);
1055 hr_utility.trace('p_processing_mode '||p_processing_mode);
1056 hr_utility.trace(dbms_utility.format_call_stack);
1057 end if;
1058
1059 --p_attributes := hxc_alias_utility.convert_attribute_to_type(p_attributes_tmp);
1060 --p_blocks := hxc_alias_utility.convert_timecard_to_type(p_blocks_tmp);
1061
1062 -- create the reference attribute index table for each tbb_id
1063 hxc_alias_utility.get_tbb_id_reference_table
1064 (p_attributes => p_attributes,
1065 p_tbb_id_reference_table => l_tbb_id_reference_table);
1066
1067 -- create the reference date index table for each 'DETAIL' tbb_id
1068 hxc_alias_utility.get_tbb_date_reference_table
1069 (p_blocks => p_blocks,
1070 p_tbb_date_reference_table => l_tbb_date_reference_table,
1071 p_timecard_start_time => l_start_time,
1072 p_timecard_stop_time => l_stop_time);
1073
1074 /*
1075 n:=l_tbb_date_reference_table.first;
1076 dbms_output.put_line('Ici '||l_tbb_date_reference_table.count);
1077 loop
1078 exit when not (l_tbb_date_reference_table.exists(n)) ;
1079 dbms_output.put_line(n);
1080 dbms_output.put_line(l_tbb_date_reference_table(n).START_TIME);
1081 dbms_output.put_line(l_tbb_date_reference_table(N).STOP_TIME);
1082 n:=l_tbb_date_reference_table.next(n);
1083 end loop;
1084 */
1085
1086
1087 -- first check that the date are checked, if not then
1088 -- we are taking the sysdate for start date
1089
1090 IF p_start_time is not null THEN
1091 l_start_time := p_start_time;
1092 END IF;
1093
1094 IF p_stop_time is not null THEN
1095 l_stop_time := p_stop_time;
1096 END IF;
1097
1098
1099
1100 --Following the processing mode we are doing the switching
1101 IF p_processing_mode = HXC_ALIAS_UTILITY.c_ss_processing THEN
1102
1103 -- Initialize the global table used to cache preferences to NULL so that
1104 -- the old pref is cleared. Used for Persistent responsibility and
1105 -- session responsibility eligibility criteria.
1106
1107 hxc_alias_utility.initialize;
1108
1109
1110 -- work out on the resource
1111 hxc_alias_utility.get_alias_def_item
1112 (p_resource_id => p_resource_id,
1113 p_attributes => p_attributes,
1114 p_alias_def_item => l_alias_def_item_tab,
1115 p_start_time => l_start_time,
1116 p_stop_time => l_stop_time);
1117 /*
1118 if g_debug then
1119 hr_utility.trace('count '||l_alias_def_item_tab.count);
1120 hr_utility.trace('ALIAS_DEFINITION_ID '||l_alias_def_item_tab(l_alias_def_item_tab.first).ALIAS_DEFINITION_ID);
1121 hr_utility.trace('ITEM_ATTRIBUTE_CATEGORY '||l_alias_def_item_tab(l_alias_def_item_tab.first).ITEM_ATTRIBUTE_CATEGORY);
1122 hr_utility.trace('RESOURCE_ID '||l_alias_def_item_tab(l_alias_def_item_tab.first).RESOURCE_ID);
1123 hr_utility.trace('LAYOUT_ID '||l_alias_def_item_tab(l_alias_def_item_tab.first).LAYOUT_ID);
1124 hr_utility.trace('ALIAS_LABEL '||l_alias_def_item_tab(l_alias_def_item_tab.first).ALIAS_LABEL);
1125 hr_utility.trace('PREF_START_DATE '||l_alias_def_item_tab(l_alias_def_item_tab.first).PREF_START_DATE);
1126 hr_utility.trace('PREF_END_DATE '||l_alias_def_item_tab(l_alias_def_item_tab.first).PREF_END_DATE);
1127 end if;
1128 */
1129 ELSIF p_processing_mode = HXC_ALIAS_UTILITY.c_tk_processing THEN
1130 -- get from the timekeeper preference the list of alias definition
1131 -- to use to do the translation
1132 hxc_alias_utility.get_alias_def_item
1133 (p_timekeeper_id => p_resource_id,
1134 p_alias_def_item => l_alias_def_item_tab);
1135 ELSE
1136 -- exit of the translation
1137 RETURN;
1138 END IF;
1139
1140 --hxc_alias_utility.dump_alias_def_item (l_alias_def_item_tab);
1141
1142 -- now for each alias definition we need to find first the
1143 -- mapping to find and then to look into the attributes table
1144 -- if we can do the translation.
1145 l_index_alias_def := l_alias_def_item_tab.first;
1146
1147 LOOP
1148 EXIT WHEN
1149 (NOT l_alias_def_item_tab.exists(l_index_alias_def));
1150
1151 IF g_debug
1152 THEN
1153 hr_utility.trace('Came here , Alias is '||l_alias_def_item_tab(l_index_alias_def).alias_definition_id);
1154 END IF;
1155
1156 -- get the type of the alias
1157 -- we need find out the information following the type of the alias
1158 hxc_alias_utility.get_alias_definition_info
1159 (l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1160 l_alias_type,
1161 l_reference_object,
1162 l_prompt);
1163
1164 -- first we delete the alias attribute reference table
1165 -- since we are working on a different alias definition.
1166
1167 --bug 3083904. quick fix in the case that
1168 --we have 2 same AN values in the set of alternate
1169 -- name. But this fix will work only if the AN
1170 -- definition which have the same type are
1171 -- consecutively set in the preference.
1172 IF p_processing_mode = HXC_ALIAS_UTILITY.c_ss_processing THEN
1173 IF l_old_alias_type <> l_alias_type THEN
1174 l_alias_att_ref.delete;
1175 END IF;
1176 ELSE
1177 l_alias_att_ref.delete;
1178 END IF;
1179
1180 l_old_alias_type := l_alias_type;
1181
1182 --dbms_output.put_line
1183 -- ('l_alias_def_item_tab(l_index_alias_def).alias_definition_id: '||l_alias_def_item_tab(l_index_alias_def).alias_definition_id);
1184 --dbms_output.put_line
1185 -- ('l_alias_type: '||l_alias_type);
1186 --dbms_output.put_line
1187 -- ('l_reference_object: '||l_reference_object);
1188 if g_debug then
1189 hr_utility.trace
1190 ('l_alias_def_item_tab(l_index_alias_def).alias_definition_id: '||l_alias_def_item_tab(l_index_alias_def).alias_definition_id);
1191 hr_utility.trace
1192 ('l_alias_type: '||l_alias_type);
1193 hr_utility.trace
1194 ('l_reference_object: '||l_reference_object);
1195 end if;
1196
1197 --reset the table.
1198 l_alias_val_att_to_match.delete;
1199
1200 l_alias_value_name := null;
1201
1202 IF l_alias_type = 'OTL_ALT_DDF' THEN
1203 -- we need to open the cursor to find how many
1204 -- values is attached to this alias definition
1205 -- for each alias of the alias definition
1206
1207 -- Bug 13511713
1208 -- Caching logic applied here on.
1209 -- For each Alias def id-start_time-stop_time, we store the values in cache to stop hitting the DB
1210 -- again and again.
1211
1212 IF g_debug
1213 THEN
1214 hr_utility.trace('Came here again , Alias is '||l_alias_def_item_tab(l_index_alias_def).alias_definition_id);
1215 hr_utility.trace('Came here again date '||l_start_time);
1216 hr_utility.trace('Came here again date '||l_stop_time);
1217 END IF;
1218
1219 -- Create the inded for the Cache table by concatenating alias_def_id and dates.
1220
1221 l_array_index := to_char(l_alias_def_item_tab(l_index_alias_def).alias_definition_id)
1222 ||'-'||to_char(l_start_time,'yyyymmdd')
1223 ||'-'||to_char(l_stop_time,'yyyymmdd');
1224 hr_utility.trace('Array index = '||l_array_index);
1225
1226 -- If the cache does not exist already,
1227
1228 IF NOT g_ad_avtab.EXISTS(l_array_index)
1229 THEN
1230
1231 hr_utility.trace('Picking up for '||l_array_index);
1232
1233 OPEN csr_alias_values(l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1234 l_start_time,
1235 l_stop_time);
1236 FETCH csr_alias_values BULK
1237 COLLECT INTO g_ad_avtab(l_array_index);
1238
1239 CLOSE csr_alias_values;
1240 -- If it exists, we will use cache.
1241 ELSE
1242
1243 hr_utility.trace('Using Cache here '||l_array_index);
1244
1245 END IF;
1246
1247 IF g_ad_avtab.EXISTS(l_array_index)
1248 AND g_ad_avtab(l_array_index).COUNT >0
1249 THEN
1250
1251 FOR i IN g_ad_avtab(l_array_index).FIRST..g_ad_avtab(l_array_index).LAST
1252 LOOP
1253 c_alias_value := g_ad_avtab(l_array_index)(i);
1254 -- get the alias value attribute to match table
1255 l_alias_val_att_to_match.delete;
1256
1257 hxc_alias_utility.get_alias_val_att_to_match
1258 (l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1259 c_alias_value.alias_value_id,
1260 l_alias_val_att_to_match);
1261
1262 ----dbms_output.put_line
1263 -- ('c_alias_value.alias_value_id: '||c_alias_value.alias_value_id);
1264
1265 if g_debug then
1266 hr_utility.trace
1267 ('c_alias_value.alias_value_id: '||c_alias_value.alias_value_id);
1268 end if;
1269
1270 --hxc_alias_utility.dump_alias_val_att_to_match( l_alias_val_att_to_match);
1271
1272 IF (p_add_alias_display_value) THEN
1273 l_alias_value_name := c_alias_value.alias_value_name;
1274 ELSE
1275 l_alias_value_name := null;
1276 END IF;
1277
1278 IF not(p_add_alias_ref_object) THEN
1279 l_reference_object := null;
1280 END IF;
1281
1282
1283
1284 do_retrieval_translation
1285 (p_attributes => p_attributes,
1286 p_tbb_id_reference_table => l_tbb_id_reference_table,
1287 p_alias_val_att_to_match => l_alias_val_att_to_match,
1288 p_item_attribute_category => l_alias_def_item_tab(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY,
1289 p_alias_definition_id => l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1290 p_alias_value_id => c_alias_value.alias_value_id,
1291 p_alias_value_name => l_alias_value_name,
1292 p_alias_type => l_alias_type,
1293 p_alias_ref_object => l_reference_object,
1294 p_tbb_date_reference_table => l_tbb_date_reference_table,
1295 p_alias_def_start_date => l_alias_def_item_tab(l_index_alias_def).pref_start_date,
1296 p_alias_def_end_date => l_alias_def_item_tab(l_index_alias_def).pref_end_date,
1297 p_alias_att_ref => l_alias_att_ref,
1298 p_messages => p_messages
1299 );
1300
1301 END LOOP;
1302
1303 END IF;
1304
1305
1306 /*
1307
1308 -- Bug 13511713
1309 -- Commented out this old code because it queries the DB too many times.
1310
1311 FOR c_alias_value IN
1312 csr_alias_values(l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1313 l_start_time,
1314 l_stop_time) LOOP
1315
1316 -- get the alias value attribute to match table
1317 l_alias_val_att_to_match.delete;
1318
1319 hxc_alias_utility.get_alias_val_att_to_match
1320 (l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1321 c_alias_value.alias_value_id,
1322 l_alias_val_att_to_match);
1323
1324 ----dbms_output.put_line
1325 -- ('c_alias_value.alias_value_id: '||c_alias_value.alias_value_id);
1326
1327 if g_debug then
1328 hr_utility.trace
1329 ('c_alias_value.alias_value_id: '||c_alias_value.alias_value_id);
1330 end if;
1331
1332 --hxc_alias_utility.dump_alias_val_att_to_match( l_alias_val_att_to_match);
1333
1334 IF (p_add_alias_display_value) THEN
1335 l_alias_value_name := c_alias_value.alias_value_name;
1336 ELSE
1337 l_alias_value_name := null;
1338 END IF;
1339
1340 IF not(p_add_alias_ref_object) THEN
1341 l_reference_object := null;
1342 END IF;
1343
1344
1345
1346 do_retrieval_translation
1347 (p_attributes => p_attributes,
1348 p_tbb_id_reference_table => l_tbb_id_reference_table,
1349 p_alias_val_att_to_match => l_alias_val_att_to_match,
1350 p_item_attribute_category => l_alias_def_item_tab(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY,
1351 p_alias_definition_id => l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1352 p_alias_value_id => c_alias_value.alias_value_id,
1353 p_alias_value_name => l_alias_value_name,
1354 p_alias_type => l_alias_type,
1355 p_alias_ref_object => l_reference_object,
1356 p_tbb_date_reference_table => l_tbb_date_reference_table,
1357 p_alias_def_start_date => l_alias_def_item_tab(l_index_alias_def).pref_start_date,
1358 p_alias_def_end_date => l_alias_def_item_tab(l_index_alias_def).pref_end_date,
1359 p_alias_att_ref => l_alias_att_ref,
1360 p_messages => p_messages
1361 );
1362
1363 END LOOP;
1364
1365 */
1366
1367
1368
1369 -- get the alias attribute values to match with the attribute.
1370 ELSE
1371
1372
1373 IF not(p_add_alias_display_value) THEN
1374 l_alias_value_name := null;
1375 END IF;
1376
1377 IF not(p_add_alias_ref_object) THEN
1378 l_reference_object := null;
1379 END IF;
1380
1381
1382 hxc_alias_utility.get_alias_val_att_to_match
1383 (l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1384 l_alias_val_att_to_match);
1385
1386 --hxc_alias_utility.dump_alias_val_att_to_match( l_alias_val_att_to_match);
1387
1388 do_retrieval_translation
1389 (p_attributes => p_attributes,
1390 p_tbb_id_reference_table => l_tbb_id_reference_table,
1391 p_alias_val_att_to_match => l_alias_val_att_to_match,
1392 p_item_attribute_category => l_alias_def_item_tab(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY,
1393 p_alias_definition_id => l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
1394 p_alias_value_id => null,
1395 p_alias_value_name => l_alias_value_name,
1396 p_alias_type => l_alias_type,
1397 p_alias_ref_object => l_reference_object,
1398 p_tbb_date_reference_table => l_tbb_date_reference_table,
1399 p_alias_def_start_date => l_alias_def_item_tab(l_index_alias_def).pref_start_date,
1400 p_alias_def_end_date => l_alias_def_item_tab(l_index_alias_def).pref_end_date,
1401 p_alias_att_ref => l_alias_att_ref,
1402 p_messages => p_messages
1403 );
1404
1405
1406 END IF;
1407
1408 l_index_alias_def := l_alias_def_item_tab.next(l_index_alias_def);
1409
1410 END LOOP;
1411
1412 --hxc_alias_utility.dump_bb_attribute_info(p_attributes);
1413
1414
1415 END do_retrieval_translation;
1416
1417 END HXC_ALIAS_TRANSLATOR;
[Home] [Help]