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