[Home] [Help]
PACKAGE BODY: APPS.HXC_ALIAS_UTILITY
Source
1 PACKAGE BODY HXC_ALIAS_UTILITY AS
2 /* $Header: hxcaltutl.pkb 120.16.12020000.3 2013/02/05 10:45:48 jnerella ship $ */
3
4
5 -- initialize the global variable
6
7 g_debug boolean := hr_utility.debug_enabled;
8 -- Bug 13511713
9 -- New global variable to cache Alias values.
10
11 TYPE ALIASREC IS RECORD
12 ( attribute1 VARCHAR2(150),
13 attribute2 VARCHAR2(150),
14 attribute3 VARCHAR2(150),
15 attribute4 VARCHAR2(150),
16 attribute5 VARCHAR2(150),
17 attribute6 VARCHAR2(150),
18 attribute7 VARCHAR2(150),
19 attribute8 VARCHAR2(150),
20 attribute9 VARCHAR2(150),
21 attribute10 VARCHAR2(150),
22 attribute11 VARCHAR2(150),
23 attribute12 VARCHAR2(150),
24 attribute13 VARCHAR2(150),
25 attribute14 VARCHAR2(150),
26 attribute15 VARCHAR2(150),
27 attribute16 VARCHAR2(150),
28 attribute17 VARCHAR2(150),
29 attribute18 VARCHAR2(150),
30 attribute19 VARCHAR2(150),
31 attribute20 VARCHAR2(150),
32 attribute21 VARCHAR2(150),
33 attribute22 VARCHAR2(150),
34 attribute23 VARCHAR2(150),
35 attribute24 VARCHAR2(150),
36 attribute25 VARCHAR2(150),
37 attribute26 VARCHAR2(150),
38 attribute27 VARCHAR2(150),
39 attribute28 VARCHAR2(150),
40 attribute29 VARCHAR2(150),
41 attribute30 VARCHAR2(150));
42
43 TYPE ALIASTAB IS TABLE OF ALIASREC INDEX BY VARCHAR2(20);
44
45 g_alias_tab ALIASTAB;
46
47 cursor g_mapping_info is
48 select hmc.bld_blk_info_type_id,segment,bld_blk_info_type,
49 hatc.component_type,hatc.component_name,bldu.building_block_category,
50 reference_object
51 from hxc_mapping_components hmc,
52 hxc_alias_types hat,
53 hxc_alias_type_components hatc,
54 hxc_alias_definitions had,
55 hxc_bld_blk_info_type_usages bldu,
56 hxc_bld_blk_info_types bld
57 where had.alias_definition_id = 1
58 and had.alias_type_id = hat.alias_type_id
59 and hatc.alias_type_id = hat.alias_type_id
60 and hmc.mapping_component_id = hatc.mapping_component_id
61 and bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
62 and bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
63 and bld.bld_blk_info_type_id = bldu.bld_blk_info_type_id
64 order by hatc.component_name;
65
66 TYPE MAPPINGTAB IS TABLE OF g_mapping_info%ROWTYPE;
67
68 TYPE DEFNTAB IS TABLE OF MAPPINGTAB INDEX BY VARCHAR2(20);
69
70 g_mapping_tab DEFNTAB;
71
72
73 PROCEDURE initialize IS
74
75 BEGIN
76
77 g_alias_def_item.delete;
78 g_alias_val_att_to_match.delete;
79 g_alias_def_att_to_match.delete;
80 g_alias_def_att_rec.delete;
81 g_alias_def_val_att_rec.delete;
82 g_alias_definition_info.delete;
83 g_alias_att_info.delete;
84 g_alias_apps_tab_info.delete;
85
86 END initialize;
87
88 -- -----------------------------------------------------------------------------|
89 -- |------------------------< process_attribute >---------------------|
90 -- -----------------------------------------------------------------------------|
91
92 FUNCTION process_attribute(p_attribute HXC_ATTRIBUTE_TYPE)
93 RETURN BOOLEAN IS
94
95 BEGIN
96
97 IF p_attribute.new = 'Y'
98 AND
99 p_attribute.attribute1 is NULL
100 AND
101 p_attribute.attribute2 is NULL
102 AND
103 p_attribute.attribute3 is NULL
104 AND
105 p_attribute.attribute4 is NULL
106 AND
107 p_attribute.attribute5 is NULL
108 AND
109 p_attribute.attribute6 is NULL
110 AND
111 p_attribute.attribute7 is NULL
112 AND
113 p_attribute.attribute8 is NULL
114 AND
115 p_attribute.attribute9 is NULL
116 AND
117 p_attribute.attribute10 is NULL
118 AND
119 p_attribute.attribute11 is NULL
120 AND
121 p_attribute.attribute12 is NULL
122 AND
123 p_attribute.attribute13 is NULL
124 AND
125 p_attribute.attribute14 is NULL
126 AND
127 p_attribute.attribute15 is NULL
128 AND
129 p_attribute.attribute16 is NULL
130 AND
131 p_attribute.attribute17 is NULL
132 AND
133 p_attribute.attribute18 is NULL
134 AND
135 p_attribute.attribute19 is NULL
136 AND
137 p_attribute.attribute20 is NULL
138 AND
139 p_attribute.attribute21 is NULL
140 AND
141 p_attribute.attribute22 is NULL
142 AND
143 p_attribute.attribute23 is NULL
144 AND
145 p_attribute.attribute24 is NULL
146 AND
147 p_attribute.attribute25 is NULL
148 AND
149 p_attribute.attribute26 is NULL
150 AND
151 p_attribute.attribute27 is NULL
152 AND
153 p_attribute.attribute28 is NULL
154 AND
155 p_attribute.attribute29 is NULL
156 AND
157 p_attribute.attribute30 is NULL
158 THEN
159 return FALSE;
160 ELSE
161 return TRUE;
162 END IF;
163
164 END;
165
166 -- -----------------------------------------------------------------------------|
167 -- |------------------------< get_alias_att_info >---------------------|
168 -- -----------------------------------------------------------------------------|
169 -- | This procedure is used to return the alias information attached to a |
170 -- | timekeeper. That is returning a pl/sql table that contains the info |
171 -- ----------------------------------------------------------------------------
172 PROCEDURE get_alias_att_info
173 (p_timekeeper_id IN NUMBER,
174 p_alias_att_info IN OUT NOCOPY t_alias_att_info)
175 IS
176
177 l_found boolean := FALSE;
178
179 l_alias_def_item_tab t_alias_def_item;
180 l_alias_val_att_to_match t_alias_val_att_to_match;
181
182 l_index_alias_val_att number;
183 l_index_alias_def number;
184
185 l_alias_type hxc_alias_types.alias_type%TYPE;
186 l_reference_object hxc_alias_types.reference_object%TYPE;
187 l_prompt hxc_alias_definitions_tl.prompt%TYPE;
188
189 BEGIN
190
191 g_debug:=hr_utility.debug_enabled;
192
193 -- first we look if the we have already
194 IF g_alias_att_info.exists(1) THEN
195 -- populate the out parameter
196 IF g_alias_att_info(1).TIMEKEEPER_ID = p_timekeeper_id THEN
197 p_alias_att_info :=g_alias_att_info;
198 l_found := true;
199 END IF;
200
201 END IF;
202
203 IF not(l_found) THEN
204
205
206 -- get the alias definition item
207 get_alias_def_item
208 (p_timekeeper_id => P_TIMEKEEPER_ID,
209 p_alias_def_item => l_alias_def_item_tab);
210
211 l_index_alias_def := l_alias_def_item_tab.first;
212
213 LOOP
214 EXIT WHEN
215 (NOT l_alias_def_item_tab.exists(l_index_alias_def));
216 -- get the alias attribute values to match with the attribute.
217
218 -- get the alias type
219 get_alias_definition_info
220 (l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
221 l_alias_type,
222 l_reference_object,
223 l_prompt);
224
225 --if g_debug then
226 --hr_utility.trace('l_alias_def_item_tab(l_index_alias_def).alias_definition_id'||l_alias_def_item_tab(l_index_alias_def).alias_definition_id);
227 --end if;
228
229
230 --IF l_alias_type like 'VALUE_SET%' THEN
231 -- get_alias_val_att_to_match
232 -- (l_alias_def_item_tab(l_index_alias_def).alias_definition_id,
233 -- l_alias_val_att_to_match);
234 --ELSE
235
236
237
238 --l_index_alias_val_att:=l_alias_val_att_to_match.first;
239 -- LOOP
240 -- EXIT WHEN
241 -- NOT (l_alias_val_att_to_match.exists(l_index_alias_val_att)) ;
242
243 p_alias_att_info(
244 substr(l_alias_def_item_tab(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY,16)).TIMEKEEPER_ID
245 :=p_timekeeper_id;
246
247 p_alias_att_info(
248 substr(l_alias_def_item_tab(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY,16)).alias_definition_id
249 :=l_alias_def_item_tab(l_index_alias_def).alias_definition_id;
250
251 p_alias_att_info(
252 substr(l_alias_def_item_tab(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY,16)).alias_type
253 :=l_alias_type;
254 --p_alias_att_info(
255 -- substr(l_alias_def_item_tab(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY,16)).BLD_BLK_INFO_TYPE_ID
256 -- := NULL;-- l_alias_val_att_to_match(l_index_alias_val_att).BLD_BLK_INFO_TYPE_ID;
257 -- p_alias_att_info(
258 -- substr(l_alias_def_item_tab(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY,16)).BLD_BLK_INFO_TYPE
259 -- :=NULL; --l_alias_val_att_to_match(l_index_alias_val_att).BLD_BLK_INFO_TYPE;
260
261 --l_index_alias_val_att:=l_alias_val_att_to_match.next(l_index_alias_val_att);
262 --END LOOP;
263 l_index_alias_def := l_alias_def_item_tab.next(l_index_alias_def);
264 END LOOP;
265
266 END IF;
267
268
269 END get_alias_att_info;
270
271
272 -- -----------------------------------------------------------------------------
273 -- |----------------------< get_next_negative_attribute_id>---------------------|
274 -- -----------------------------------------------------------------------------
275 -- | This procedure return the next negative id. We are looking into |
276 -- | p_attributes to find out what is the next negative index |
277 -- ----------------------------------------------------------------------------
278
279 FUNCTION get_next_negative_attribute_id(
280 p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE--hxc_self_service_time_deposit.building_block_attribute_info
281 )
282 RETURN NUMBER
283 IS
284
285 l_attribute_index NUMBER;
286 l_next_att_id NUMBER;
287
288 BEGIN
289 l_next_att_id := -2;
290
291 l_attribute_index := p_attributes.first;
292 LOOP
293 EXIT WHEN NOT p_attributes.exists(l_attribute_index);
294
295 IF p_attributes(l_attribute_index).TIME_ATTRIBUTE_ID <= l_next_att_id THEN
296 l_next_att_id := p_attributes(l_attribute_index).TIME_ATTRIBUTE_ID - 1;
297 END IF;
298
299 l_attribute_index := p_attributes.next(l_attribute_index);
300 END LOOP;
301
302 RETURN l_next_att_id;
303
304 END get_next_negative_attribute_id;
305
306
307 -- ----------------------------------------------------------------------------
308 -- |--------------------------< set_attribute_information>--------------------|
309 -- ----------------------------------------------------------------------------
310 -- | This procedure set the value on a certain attribute into |
311 -- | p_attributes at a certain index. |
312 -- ----------------------------------------------------------------------------
313 PROCEDURE set_attribute_information
314 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
315 p_index_in_table IN NUMBER,
316 p_attribute_to_set IN VARCHAR2,
317 p_value_to_set IN VARCHAR2) IS
318
319 BEGIN
320
321 if p_attribute_to_set = 'ATTRIBUTE_CATEGORY' THEN
322 p_attributes(p_index_in_table).ATTRIBUTE_CATEGORY := p_value_to_set;
323 elsif p_attribute_to_set = 'ATTRIBUTE1' THEN
324 p_attributes(p_index_in_table).ATTRIBUTE1 := p_value_to_set;
325 elsif p_attribute_to_set = 'ATTRIBUTE2' THEN
326 p_attributes(p_index_in_table).ATTRIBUTE2 := p_value_to_set;
327 elsif p_attribute_to_set = 'ATTRIBUTE3' THEN
328 p_attributes(p_index_in_table).ATTRIBUTE3 := p_value_to_set;
329 elsif p_attribute_to_set = 'ATTRIBUTE4' THEN
330 p_attributes(p_index_in_table).ATTRIBUTE4 := p_value_to_set;
331 elsif p_attribute_to_set = 'ATTRIBUTE5' THEN
332 p_attributes(p_index_in_table).ATTRIBUTE5 := p_value_to_set;
333 elsif p_attribute_to_set = 'ATTRIBUTE6' THEN
334 p_attributes(p_index_in_table).ATTRIBUTE6 := p_value_to_set;
335 elsif p_attribute_to_set = 'ATTRIBUTE7' THEN
336 p_attributes(p_index_in_table).ATTRIBUTE7 := p_value_to_set;
337 elsif p_attribute_to_set = 'ATTRIBUTE8' THEN
338 p_attributes(p_index_in_table).ATTRIBUTE8 := p_value_to_set;
339 elsif p_attribute_to_set = 'ATTRIBUTE9' THEN
340 p_attributes(p_index_in_table).ATTRIBUTE9 := p_value_to_set;
341 elsif p_attribute_to_set = 'ATTRIBUTE10' THEN
342 p_attributes(p_index_in_table).ATTRIBUTE10 := p_value_to_set;
343 elsif p_attribute_to_set = 'ATTRIBUTE11' THEN
344 p_attributes(p_index_in_table).ATTRIBUTE11 := p_value_to_set;
345 elsif p_attribute_to_set = 'ATTRIBUTE12' THEN
346 p_attributes(p_index_in_table).ATTRIBUTE12 := p_value_to_set;
347 elsif p_attribute_to_set = 'ATTRIBUTE13' THEN
348 p_attributes(p_index_in_table).ATTRIBUTE13 := p_value_to_set;
349 elsif p_attribute_to_set = 'ATTRIBUTE14' THEN
350 p_attributes(p_index_in_table).ATTRIBUTE14 := p_value_to_set;
351 elsif p_attribute_to_set = 'ATTRIBUTE15' THEN
352 p_attributes(p_index_in_table).ATTRIBUTE15 := p_value_to_set;
353 elsif p_attribute_to_set = 'ATTRIBUTE16' THEN
354 p_attributes(p_index_in_table).ATTRIBUTE16 := p_value_to_set;
355 elsif p_attribute_to_set = 'ATTRIBUTE17' THEN
356 p_attributes(p_index_in_table).ATTRIBUTE17 := p_value_to_set;
357 elsif p_attribute_to_set = 'ATTRIBUTE18' THEN
358 p_attributes(p_index_in_table).ATTRIBUTE18 := p_value_to_set;
359 elsif p_attribute_to_set = 'ATTRIBUTE19' THEN
360 p_attributes(p_index_in_table).ATTRIBUTE19 := p_value_to_set;
361 elsif p_attribute_to_set = 'ATTRIBUTE20' THEN
362 p_attributes(p_index_in_table).ATTRIBUTE20 := p_value_to_set; -- bug 16229648
363 elsif p_attribute_to_set = 'ATTRIBUTE21' THEN
364 p_attributes(p_index_in_table).ATTRIBUTE21 := p_value_to_set;
365 elsif p_attribute_to_set = 'ATTRIBUTE22' THEN
366 p_attributes(p_index_in_table).ATTRIBUTE22 := p_value_to_set;
367 elsif p_attribute_to_set = 'ATTRIBUTE23' THEN
368 p_attributes(p_index_in_table).ATTRIBUTE23 := p_value_to_set;
369 elsif p_attribute_to_set = 'ATTRIBUTE24' THEN
370 p_attributes(p_index_in_table).ATTRIBUTE24 := p_value_to_set;
371 elsif p_attribute_to_set = 'ATTRIBUTE25' THEN
372 p_attributes(p_index_in_table).ATTRIBUTE25 := p_value_to_set;
373 elsif p_attribute_to_set = 'ATTRIBUTE26' THEN
374 p_attributes(p_index_in_table).ATTRIBUTE26 := p_value_to_set;
375 elsif p_attribute_to_set = 'ATTRIBUTE27' THEN
376 p_attributes(p_index_in_table).ATTRIBUTE27 := p_value_to_set;
377 elsif p_attribute_to_set = 'ATTRIBUTE28' THEN
378 p_attributes(p_index_in_table).ATTRIBUTE28 := p_value_to_set;
379 elsif p_attribute_to_set = 'ATTRIBUTE29' THEN
380 p_attributes(p_index_in_table).ATTRIBUTE29 := p_value_to_set;
381 elsif p_attribute_to_set = 'ATTRIBUTE30' THEN
382 p_attributes(p_index_in_table).ATTRIBUTE30 := p_value_to_set;
383 END if;
384
385 END;
386
387
388 -- ----------------------------------------------------------------------------
389 -- |--------------------------< get_attribute_information>--------------------|
390 -- ----------------------------------------------------------------------------
391 -- | This procedure get the value on a certain attribute into |
392 -- | p_attributes at a certain index. |
393 -- ----------------------------------------------------------------------------
394 PROCEDURE get_attribute_information
395 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
396 p_index_in_table IN NUMBER,
397 p_attribute_to_get IN VARCHAR2,
398 p_get_value OUT NOCOPY VARCHAR2) IS
399
400 BEGIN
401
402 if p_attribute_to_get = 'ATTRIBUTE_CATEGORY' THEN
403 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE_CATEGORY;
404 elsif p_attribute_to_get = 'ATTRIBUTE1' THEN
405 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE1;
406 elsif p_attribute_to_get = 'ATTRIBUTE2' THEN
407 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE2;
408 elsif p_attribute_to_get = 'ATTRIBUTE3' THEN
409 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE3;
410 elsif p_attribute_to_get = 'ATTRIBUTE4' THEN
411 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE4;
412 elsif p_attribute_to_get = 'ATTRIBUTE5' THEN
413 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE5;
414 elsif p_attribute_to_get = 'ATTRIBUTE6' THEN
415 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE6;
416 elsif p_attribute_to_get = 'ATTRIBUTE7' THEN
417 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE7;
418 elsif p_attribute_to_get = 'ATTRIBUTE8' THEN
419 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE8;
420 elsif p_attribute_to_get = 'ATTRIBUTE9' THEN
421 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE9;
422 elsif p_attribute_to_get = 'ATTRIBUTE10' THEN
423 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE10;
424 elsif p_attribute_to_get = 'ATTRIBUTE11' THEN
425 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE11;
426 elsif p_attribute_to_get = 'ATTRIBUTE12' THEN
427 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE12;
428 elsif p_attribute_to_get = 'ATTRIBUTE13' THEN
429 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE13;
430 elsif p_attribute_to_get = 'ATTRIBUTE14' THEN
431 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE14;
432 elsif p_attribute_to_get = 'ATTRIBUTE15' THEN
433 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE15;
434 elsif p_attribute_to_get = 'ATTRIBUTE16' THEN
435 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE16;
436 elsif p_attribute_to_get = 'ATTRIBUTE17' THEN
437 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE17;
438 elsif p_attribute_to_get = 'ATTRIBUTE18' THEN
439 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE18;
440 elsif p_attribute_to_get = 'ATTRIBUTE19' THEN
441 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE19;
442 elsif p_attribute_to_get = 'ATTRIBUTE20' THEN
443 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE20;
444 elsif p_attribute_to_get = 'ATTRIBUTE21' THEN
445 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE21;
446 elsif p_attribute_to_get = 'ATTRIBUTE22' THEN
447 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE22;
448 elsif p_attribute_to_get = 'ATTRIBUTE23' THEN
449 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE23;
450 elsif p_attribute_to_get = 'ATTRIBUTE24' THEN
451 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE24;
452 elsif p_attribute_to_get = 'ATTRIBUTE25' THEN
453 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE25;
454 elsif p_attribute_to_get = 'ATTRIBUTE26' THEN
455 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE26;
456 elsif p_attribute_to_get = 'ATTRIBUTE27' THEN
457 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE27;
458 elsif p_attribute_to_get = 'ATTRIBUTE28' THEN
459 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE28;
460 elsif p_attribute_to_get = 'ATTRIBUTE29' THEN
461 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE29;
462 elsif p_attribute_to_get = 'ATTRIBUTE30' THEN
463 p_get_value := p_attributes(p_index_in_table).ATTRIBUTE30;
464 END if;
465
466
467 END;
468
469 -- ----------------------------------------------------------------------------
470 -- |--------------------------< get_attribute_to_match_info>------------------|
471 -- ----------------------------------------------------------------------------
472 -- | This procedure get the value on a certain attribute into |
473 -- | p_attribute_to_match at a certain index. |
474 -- ----------------------------------------------------------------------------
475 PROCEDURE get_attribute_to_match_info
476 (p_attribute_to_match IN OUT NOCOPY t_alias_val_att_to_match,
477 p_index_in_table IN NUMBER,
478 p_attribute_to_get IN VARCHAR2,
479 p_get_value OUT NOCOPY VARCHAR2) IS
480
481 BEGIN
482
483 if p_attribute_to_get = 'ATTRIBUTE_CATEGORY' THEN
484 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE_CATEGORY;
485 elsif p_attribute_to_get = 'ATTRIBUTE1' THEN
486 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE1;
487 elsif p_attribute_to_get = 'ATTRIBUTE2' THEN
488 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE2;
489 elsif p_attribute_to_get = 'ATTRIBUTE3' THEN
490 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE3;
491 elsif p_attribute_to_get = 'ATTRIBUTE4' THEN
492 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE4;
493 elsif p_attribute_to_get = 'ATTRIBUTE5' THEN
494 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE5;
495 elsif p_attribute_to_get = 'ATTRIBUTE6' THEN
496 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE6;
497 elsif p_attribute_to_get = 'ATTRIBUTE7' THEN
498 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE7;
499 elsif p_attribute_to_get = 'ATTRIBUTE8' THEN
500 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE8;
501 elsif p_attribute_to_get = 'ATTRIBUTE9' THEN
502 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE9;
503 elsif p_attribute_to_get = 'ATTRIBUTE10' THEN
504 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE10;
505 elsif p_attribute_to_get = 'ATTRIBUTE11' THEN
506 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE11;
507 elsif p_attribute_to_get = 'ATTRIBUTE12' THEN
508 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE12;
509 elsif p_attribute_to_get = 'ATTRIBUTE13' THEN
510 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE13;
511 elsif p_attribute_to_get = 'ATTRIBUTE14' THEN
512 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE14;
513 elsif p_attribute_to_get = 'ATTRIBUTE15' THEN
514 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE15;
515 elsif p_attribute_to_get = 'ATTRIBUTE16' THEN
516 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE16;
517 elsif p_attribute_to_get = 'ATTRIBUTE17' THEN
518 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE17;
519 elsif p_attribute_to_get = 'ATTRIBUTE18' THEN
520 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE18;
521 elsif p_attribute_to_get = 'ATTRIBUTE19' THEN
522 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE19;
523 elsif p_attribute_to_get = 'ATTRIBUTE20' THEN
524 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE20;
525 elsif p_attribute_to_get = 'ATTRIBUTE21' THEN
526 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE21;
527 elsif p_attribute_to_get = 'ATTRIBUTE22' THEN
528 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE22;
529 elsif p_attribute_to_get = 'ATTRIBUTE23' THEN
530 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE23;
531 elsif p_attribute_to_get = 'ATTRIBUTE24' THEN
532 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE24;
533 elsif p_attribute_to_get = 'ATTRIBUTE25' THEN
534 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE25;
535 elsif p_attribute_to_get = 'ATTRIBUTE26' THEN
536 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE26;
537 elsif p_attribute_to_get = 'ATTRIBUTE27' THEN
538 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE27;
539 elsif p_attribute_to_get = 'ATTRIBUTE28' THEN
540 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE28;
541 elsif p_attribute_to_get = 'ATTRIBUTE29' THEN
542 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE29;
543 elsif p_attribute_to_get = 'ATTRIBUTE30' THEN
544 p_get_value := p_attribute_to_match(p_index_in_table).ATTRIBUTE30;
545 END if;
546
547
548 END get_attribute_to_match_info;
549
550 -- ----------------------------------------------------------------------------
551 -- |--------------------------< set_attribute_to_match_info>------------------|
552 -- ----------------------------------------------------------------------------
553 -- | This procedure set the value on a certain attribute into |
554 -- | p_attribute_to_match at a certain index. |
555 -- ----------------------------------------------------------------------------
556 PROCEDURE set_attribute_to_match_info
557 (p_attribute_to_match IN OUT NOCOPY t_alias_val_att_to_match,
558 p_index_in_table IN NUMBER,
559 p_attribute_to_set IN VARCHAR2,
560 p_bld_blk_info_type IN VARCHAR2,
561 p_mapping_att_cat IN VARCHAR2,
562 p_value_to_set IN VARCHAR2) IS
563
564 BEGIN
565
566 --if g_debug then
567 --hr_utility.trace('p_bld_blk_info_type :'||p_bld_blk_info_type);
568 --hr_utility.trace('p_mapping_att_cat :'||p_mapping_att_cat);
569 --hr_utility.trace('p_attribute_to_set :'||p_attribute_to_set);
570 --end if;
571
572 if p_attribute_to_set = 'ATTRIBUTE_CATEGORY' THEN
573 IF (p_bld_blk_info_type like 'Dummy%') THEN
574 p_attribute_to_match(p_index_in_table).ATTRIBUTE_CATEGORY :=
575 p_mapping_att_cat||' - '||p_value_to_set;
576 ELSE
577 p_attribute_to_match(p_index_in_table).ATTRIBUTE_CATEGORY := p_value_to_set;
578 END IF;
579 elsif p_attribute_to_set = 'ATTRIBUTE1' THEN
580 p_attribute_to_match(p_index_in_table).ATTRIBUTE1 := p_value_to_set;
581 elsif p_attribute_to_set = 'ATTRIBUTE2' THEN
582 p_attribute_to_match(p_index_in_table).ATTRIBUTE2 := p_value_to_set;
583 elsif p_attribute_to_set = 'ATTRIBUTE3' THEN
584 p_attribute_to_match(p_index_in_table).ATTRIBUTE3 := p_value_to_set;
585 elsif p_attribute_to_set = 'ATTRIBUTE4' THEN
586 p_attribute_to_match(p_index_in_table).ATTRIBUTE4 := p_value_to_set;
587 elsif p_attribute_to_set = 'ATTRIBUTE5' THEN
588 p_attribute_to_match(p_index_in_table).ATTRIBUTE5 := p_value_to_set;
589 elsif p_attribute_to_set = 'ATTRIBUTE6' THEN
590 p_attribute_to_match(p_index_in_table).ATTRIBUTE6 := p_value_to_set;
591 elsif p_attribute_to_set = 'ATTRIBUTE7' THEN
592 p_attribute_to_match(p_index_in_table).ATTRIBUTE7 := p_value_to_set;
593 elsif p_attribute_to_set = 'ATTRIBUTE8' THEN
594 p_attribute_to_match(p_index_in_table).ATTRIBUTE8 := p_value_to_set;
595 elsif p_attribute_to_set = 'ATTRIBUTE9' THEN
596 p_attribute_to_match(p_index_in_table).ATTRIBUTE9 := p_value_to_set;
597 elsif p_attribute_to_set = 'ATTRIBUTE10' THEN
598 p_attribute_to_match(p_index_in_table).ATTRIBUTE10 := p_value_to_set;
599 elsif p_attribute_to_set = 'ATTRIBUTE11' THEN
600 p_attribute_to_match(p_index_in_table).ATTRIBUTE11 := p_value_to_set;
601 elsif p_attribute_to_set = 'ATTRIBUTE12' THEN
602 p_attribute_to_match(p_index_in_table).ATTRIBUTE12 := p_value_to_set;
603 elsif p_attribute_to_set = 'ATTRIBUTE13' THEN
604 p_attribute_to_match(p_index_in_table).ATTRIBUTE13 := p_value_to_set;
605 elsif p_attribute_to_set = 'ATTRIBUTE14' THEN
606 p_attribute_to_match(p_index_in_table).ATTRIBUTE14 := p_value_to_set;
607 elsif p_attribute_to_set = 'ATTRIBUTE15' THEN
608 p_attribute_to_match(p_index_in_table).ATTRIBUTE15 := p_value_to_set;
609 elsif p_attribute_to_set = 'ATTRIBUTE16' THEN
610 p_attribute_to_match(p_index_in_table).ATTRIBUTE16 := p_value_to_set;
611 elsif p_attribute_to_set = 'ATTRIBUTE17' THEN
612 p_attribute_to_match(p_index_in_table).ATTRIBUTE17 := p_value_to_set;
613 elsif p_attribute_to_set = 'ATTRIBUTE18' THEN
614 p_attribute_to_match(p_index_in_table).ATTRIBUTE18 := p_value_to_set;
615 elsif p_attribute_to_set = 'ATTRIBUTE19' THEN
616 p_attribute_to_match(p_index_in_table).ATTRIBUTE19 := p_value_to_set;
617 elsif p_attribute_to_set = 'ATTRIBUTE20' THEN
618 p_attribute_to_match(p_index_in_table).ATTRIBUTE20 := p_value_to_set; -- Bug 16229648
619 elsif p_attribute_to_set = 'ATTRIBUTE21' THEN
620 p_attribute_to_match(p_index_in_table).ATTRIBUTE21 := p_value_to_set;
621 elsif p_attribute_to_set = 'ATTRIBUTE22' THEN
622 p_attribute_to_match(p_index_in_table).ATTRIBUTE22 := p_value_to_set;
623 elsif p_attribute_to_set = 'ATTRIBUTE23' THEN
624 p_attribute_to_match(p_index_in_table).ATTRIBUTE23 := p_value_to_set;
625 elsif p_attribute_to_set = 'ATTRIBUTE24' THEN
626 p_attribute_to_match(p_index_in_table).ATTRIBUTE24 := p_value_to_set;
627 elsif p_attribute_to_set = 'ATTRIBUTE25' THEN
628 p_attribute_to_match(p_index_in_table).ATTRIBUTE25 := p_value_to_set;
629 elsif p_attribute_to_set = 'ATTRIBUTE26' THEN
630 p_attribute_to_match(p_index_in_table).ATTRIBUTE26 := p_value_to_set;
631 elsif p_attribute_to_set = 'ATTRIBUTE27' THEN
632 p_attribute_to_match(p_index_in_table).ATTRIBUTE27 := p_value_to_set;
633 elsif p_attribute_to_set = 'ATTRIBUTE28' THEN
634 p_attribute_to_match(p_index_in_table).ATTRIBUTE28 := p_value_to_set;
635 elsif p_attribute_to_set = 'ATTRIBUTE29' THEN
636 p_attribute_to_match(p_index_in_table).ATTRIBUTE29 := p_value_to_set;
637 elsif p_attribute_to_set = 'ATTRIBUTE30' THEN
638 p_attribute_to_match(p_index_in_table).ATTRIBUTE30 := p_value_to_set;
639 END if;
640
641 END set_attribute_to_match_info;
642
643
644 -- ----------------------------------------------------------------------------
645 -- |------------------------< attribute_check >--------------------|
646 -- ----------------------------------------------------------------------------
647 -- | Thie procedure is used to find out if a bld_blk_info_type_id is |
648 -- | attached to a particular time_building_block_id. |
649 -- | This procedure is used while the deposit translation |
650 -- ----------------------------------------------------------------------------
651 PROCEDURE attribute_check
652 (p_bld_blk_info_type_id IN NUMBER
653 ,p_time_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
654 ,p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE--hxc_self_service_time_deposit.building_block_attribute_info
655 ,p_tbb_id_reference_table IN OUT NOCOPY t_tbb_id_reference
656 ,p_attribute_index IN OUT NOCOPY NUMBER
657 ,p_attribute_found IN OUT NOCOPY BOOLEAN)
658 IS
659
660 l_attribute_count NUMBER;
661 l_doesnt_exist BOOLEAN := TRUE;
662
663 l_index_string VARCHAR2(2000);
664 --l_index_return NUMBER := -1;
665
666 l_index_next NUMBER;
667 l_index_start NUMBER;
668 l_result VARCHAR2(10);
669
670 l_attribute_category VARCHAR2(80);
671 l_to_check VARCHAR2(80);
672
673 BEGIN
674
675 p_attribute_found := FALSE;
676
677 IF (p_tbb_id_reference_table.exists(p_time_building_block_id)) THEN
678 -- we need to send the check if all index have an attribute_category = p_to_check
679 -- get the index string
680 l_index_string := p_tbb_id_reference_table(p_time_building_block_id).ATTRIBUTE_INDEX;
681
682 --if g_debug then
683 --hr_utility.trace('Joel : p_bld_blk_info_type_id'||p_bld_blk_info_type_id);
684 --hr_utility.trace('Joel : p_time_building_block_id'||p_time_building_block_id);
685 --hr_utility.trace('Joel : l_index_string'||l_index_string);
686 --end if;
687
688 -- go through the string and check the to_check
689 l_index_start:=INSTR(l_index_string,'|',1,1)+1;
690 LOOP
691
692 l_index_next := INSTR(l_index_string,'|',l_index_start,1);
693
694 IF(l_index_next = 0) THEN
695 l_result := SUBSTR(l_index_string,
696 l_index_start,
697 length(l_index_string)+1-l_index_start);
698 ELSE
699 l_result := SUBSTR(l_index_string,
700 l_index_start,
701 l_index_next-l_index_start);
702 END IF;
703
704 IF (l_result is not null) THEN
705 if p_attributes.exists(l_result) THEN
706 IF (p_attributes(l_result).BLD_BLK_INFO_TYPE_ID = p_bld_blk_info_type_id) THEN
707 p_attribute_index := l_result;
708 p_attribute_found := TRUE;
709 END IF;
710 END if;
711 END if;
712
713 l_index_start := l_index_next + 1;
714 l_result := NULL;
715
716 EXIT WHEN l_index_next = 0;
717 END LOOP; -- attribute for a tbb_id
718 END if;
719
720 --return l_index_return;
721
722 END attribute_check;
723
724 -- ----------------------------------------------------------------------------
725 -- |------------------------< set_attribute_on_att_to_match>--------------------|
726 -- ----------------------------------------------------------------------------
727 PROCEDURE set_attribute_on_att_to_match(
728 p_alias_val_att_to_match_row IN OUT NOCOPY r_alias_val_att_to_match,
729 p_alias_value_id NUMBER,
730 p_component_type VARCHAR2,
731 p_segment VARCHAR2,
732 p_bld_blk_info_type VARCHAR2,
733 p_attribute_mapping_category VARCHAR2,
734 p_match_to_delete OUT NOCOPY BOOLEAN)
735 IS
736
737 CURSOR csr_alias_value_attribute
738 IS
739 select attribute1
740 ,attribute2
741 ,attribute3
742 ,attribute4
743 ,attribute5
744 ,attribute6
745 ,attribute7
746 ,attribute8
747 ,attribute9
748 ,attribute10
749 ,attribute11
750 ,attribute12
751 ,attribute13
752 ,attribute14
753 ,attribute15
754 ,attribute16
755 ,attribute17
756 ,attribute18
757 ,attribute19
758 ,attribute20
759 ,attribute21
760 ,attribute22
761 ,attribute23
762 ,attribute24
763 ,attribute25
764 ,attribute26
765 ,attribute27
766 ,attribute28
767 ,attribute29
768 ,attribute30
769 from hxc_alias_values
770 where alias_value_id = p_alias_value_id;
771
772 l_alias_attribute_value VARCHAR2(150);
773
774 BEGIN
775
776 -- following the p_component_type we need to pick up the right information
777 -- of hxc_alias_values, then following the segment name, we will put it into
778 -- the right segment in p_alias_val_att_to_match_row
779
780 IF NOT g_alias_tab.EXISTS(to_char(p_alias_value_id))
781 THEN
782
783 -- Bug 13511713
784 -- If value is not already retrieved, run the cursor.
785 -- Fetch the values and cache it for next time.
786
787 FOR c_alias_value_attribute in csr_alias_value_attribute LOOP
788 -- first we look for the attribute to find
789 IF p_component_type = 'ATTRIBUTE1' THEN
790 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE1;
791 ELSIF p_component_type = 'ATTRIBUTE2' THEN
792 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE2;
793 ELSIF p_component_type = 'ATTRIBUTE3' THEN
794 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE3;
795 ELSIF p_component_type = 'ATTRIBUTE4' THEN
796 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE4;
797 ELSIF p_component_type = 'ATTRIBUTE5' THEN
798 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE5;
799 ELSIF p_component_type = 'ATTRIBUTE6' THEN
800 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE6;
801 ELSIF p_component_type = 'ATTRIBUTE7' THEN
802 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE7;
803 ELSIF p_component_type = 'ATTRIBUTE8' THEN
804 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE8;
805 ELSIF p_component_type = 'ATTRIBUTE9' THEN
806 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE9;
807 ELSIF p_component_type = 'ATTRIBUTE10' THEN
808 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE10;
809 ELSIF p_component_type = 'ATTRIBUTE11' THEN
810 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE11;
811 ELSIF p_component_type = 'ATTRIBUTE12' THEN
812 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE12;
813 ELSIF p_component_type = 'ATTRIBUTE13' THEN
814 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE13;
815 ELSIF p_component_type = 'ATTRIBUTE14' THEN
816 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE14;
817 ELSIF p_component_type = 'ATTRIBUTE15' THEN
818 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE15;
819 ELSIF p_component_type = 'ATTRIBUTE16' THEN
820 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE16;
821 ELSIF p_component_type = 'ATTRIBUTE17' THEN
822 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE17;
823 ELSIF p_component_type = 'ATTRIBUTE18' THEN
824 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE18;
825 ELSIF p_component_type = 'ATTRIBUTE19' THEN
826 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE19;
827 ELSIF p_component_type = 'ATTRIBUTE20' THEN
828 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE20;
829 ELSIF p_component_type = 'ATTRIBUTE21' THEN
830 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE21;
831 ELSIF p_component_type = 'ATTRIBUTE22' THEN
832 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE22;
833 ELSIF p_component_type = 'ATTRIBUTE23' THEN
834 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE23;
835 ELSIF p_component_type = 'ATTRIBUTE24' THEN
836 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE24;
837 ELSIF p_component_type = 'ATTRIBUTE25' THEN
838 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE25;
839 ELSIF p_component_type = 'ATTRIBUTE26' THEN
840 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE26;
841 ELSIF p_component_type = 'ATTRIBUTE27' THEN
842 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE27;
843 ELSIF p_component_type = 'ATTRIBUTE28' THEN
844 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE28;
845 ELSIF p_component_type = 'ATTRIBUTE29' THEN
846 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE29;
847 ELSIF p_component_type = 'ATTRIBUTE30' THEN
848 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE30;
849 END IF;
850
851 g_alias_tab(TO_CHAR(p_alias_value_id)) := c_alias_value_attribute;
852
853 END LOOP;
854
855
856 ELSE
857 -- Already retrieved and cached, so copy the values from Cache.
858
859 IF p_component_type = 'ATTRIBUTE1' THEN
860 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE1;
861 ELSIF p_component_type = 'ATTRIBUTE2' THEN
862 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE2;
863 ELSIF p_component_type = 'ATTRIBUTE3' THEN
864 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE3;
865 ELSIF p_component_type = 'ATTRIBUTE4' THEN
866 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE4;
867 ELSIF p_component_type = 'ATTRIBUTE5' THEN
868 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE5;
869 ELSIF p_component_type = 'ATTRIBUTE6' THEN
870 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE6;
871 ELSIF p_component_type = 'ATTRIBUTE7' THEN
872 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE7;
873 ELSIF p_component_type = 'ATTRIBUTE8' THEN
874 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE8;
875 ELSIF p_component_type = 'ATTRIBUTE9' THEN
876 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE9;
877 ELSIF p_component_type = 'ATTRIBUTE10' THEN
878 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE10;
879 ELSIF p_component_type = 'ATTRIBUTE11' THEN
880 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE11;
881 ELSIF p_component_type = 'ATTRIBUTE12' THEN
882 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE12;
883 ELSIF p_component_type = 'ATTRIBUTE13' THEN
884 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE13;
885 ELSIF p_component_type = 'ATTRIBUTE14' THEN
886 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE14;
887 ELSIF p_component_type = 'ATTRIBUTE15' THEN
888 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE15;
889 ELSIF p_component_type = 'ATTRIBUTE16' THEN
890 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE16;
891 ELSIF p_component_type = 'ATTRIBUTE17' THEN
892 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE17;
893 ELSIF p_component_type = 'ATTRIBUTE18' THEN
894 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE18;
895 ELSIF p_component_type = 'ATTRIBUTE19' THEN
896 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE19;
897 ELSIF p_component_type = 'ATTRIBUTE20' THEN
898 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE20;
899 ELSIF p_component_type = 'ATTRIBUTE21' THEN
900 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE21;
901 ELSIF p_component_type = 'ATTRIBUTE22' THEN
902 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE22;
903 ELSIF p_component_type = 'ATTRIBUTE23' THEN
904 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE23;
905 ELSIF p_component_type = 'ATTRIBUTE24' THEN
906 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE24;
907 ELSIF p_component_type = 'ATTRIBUTE25' THEN
908 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE25;
909 ELSIF p_component_type = 'ATTRIBUTE26' THEN
910 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE26;
911 ELSIF p_component_type = 'ATTRIBUTE27' THEN
912 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE27;
913 ELSIF p_component_type = 'ATTRIBUTE28' THEN
914 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE28;
915 ELSIF p_component_type = 'ATTRIBUTE29' THEN
916 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE29;
917 ELSIF p_component_type = 'ATTRIBUTE30' THEN
918 l_alias_attribute_value := g_alias_tab(TO_CHAR(p_alias_value_id)).ATTRIBUTE30;
919 END IF;
920
921 END IF;
922
923 /*
924 FOR c_alias_value_attribute in csr_alias_value_attribute LOOP
925 -- first we look for the attribute to find
926 IF p_component_type = 'ATTRIBUTE1' THEN
927 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE1;
928 ELSIF p_component_type = 'ATTRIBUTE2' THEN
929 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE2;
930 ELSIF p_component_type = 'ATTRIBUTE3' THEN
931 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE3;
932 ELSIF p_component_type = 'ATTRIBUTE4' THEN
933 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE4;
934 ELSIF p_component_type = 'ATTRIBUTE5' THEN
935 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE5;
936 ELSIF p_component_type = 'ATTRIBUTE6' THEN
937 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE6;
938 ELSIF p_component_type = 'ATTRIBUTE7' THEN
939 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE7;
940 ELSIF p_component_type = 'ATTRIBUTE8' THEN
941 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE8;
942 ELSIF p_component_type = 'ATTRIBUTE9' THEN
943 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE9;
944 ELSIF p_component_type = 'ATTRIBUTE10' THEN
945 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE10;
946 ELSIF p_component_type = 'ATTRIBUTE11' THEN
947 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE11;
948 ELSIF p_component_type = 'ATTRIBUTE12' THEN
949 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE12;
950 ELSIF p_component_type = 'ATTRIBUTE13' THEN
951 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE13;
952 ELSIF p_component_type = 'ATTRIBUTE14' THEN
953 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE14;
954 ELSIF p_component_type = 'ATTRIBUTE15' THEN
955 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE15;
956 ELSIF p_component_type = 'ATTRIBUTE16' THEN
957 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE16;
958 ELSIF p_component_type = 'ATTRIBUTE17' THEN
959 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE17;
960 ELSIF p_component_type = 'ATTRIBUTE18' THEN
961 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE18;
962 ELSIF p_component_type = 'ATTRIBUTE19' THEN
963 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE19;
964 ELSIF p_component_type = 'ATTRIBUTE20' THEN
965 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE20;
966 ELSIF p_component_type = 'ATTRIBUTE21' THEN
967 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE21;
968 ELSIF p_component_type = 'ATTRIBUTE22' THEN
969 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE22;
970 ELSIF p_component_type = 'ATTRIBUTE23' THEN
971 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE23;
972 ELSIF p_component_type = 'ATTRIBUTE24' THEN
973 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE24;
974 ELSIF p_component_type = 'ATTRIBUTE25' THEN
975 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE25;
976 ELSIF p_component_type = 'ATTRIBUTE26' THEN
977 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE26;
978 ELSIF p_component_type = 'ATTRIBUTE27' THEN
979 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE27;
980 ELSIF p_component_type = 'ATTRIBUTE28' THEN
981 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE28;
982 ELSIF p_component_type = 'ATTRIBUTE29' THEN
983 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE29;
984 ELSIF p_component_type = 'ATTRIBUTE30' THEN
985 l_alias_attribute_value := c_alias_value_attribute.ATTRIBUTE30;
986 END IF;
987 END LOOP;
988
989 */
990 ----dbms_output.put_line
991 --('l_alias_attribute_value: '||l_alias_attribute_value);
992
993 -- which mean we don't need to find this match.
994 IF l_alias_attribute_value is null THEN
995 p_match_to_delete := TRUE;
996 RETURN;
997 END IF;
998
999 -- now we have the attribute information of the alias value
1000 -- we need to put at the right place in p_alias_val_att_to_match_row
1001 IF p_segment = 'ATTRIBUTE_CATEGORY' THEN
1002 IF (p_bld_blk_info_type like 'Dummy%') THEN
1003 p_alias_val_att_to_match_row.attribute_category :=
1004 p_attribute_mapping_category||' - '||l_alias_attribute_value;
1005 ELSE
1006 p_alias_val_att_to_match_row.attribute_category := l_alias_attribute_value;
1007 END IF;
1008
1009 ELSIF p_segment = 'ATTRIBUTE1' THEN
1010 p_alias_val_att_to_match_row.ATTRIBUTE1 := l_alias_attribute_value;
1011 ELSIF p_segment = 'ATTRIBUTE2' THEN
1012 p_alias_val_att_to_match_row.ATTRIBUTE2 := l_alias_attribute_value;
1013 ELSIF p_segment = 'ATTRIBUTE3' THEN
1014 p_alias_val_att_to_match_row.ATTRIBUTE3 := l_alias_attribute_value;
1015 ELSIF p_segment = 'ATTRIBUTE4' THEN
1016 p_alias_val_att_to_match_row.ATTRIBUTE4 := l_alias_attribute_value;
1017 ELSIF p_segment = 'ATTRIBUTE5' THEN
1018 p_alias_val_att_to_match_row.ATTRIBUTE5 := l_alias_attribute_value;
1019 ELSIF p_segment = 'ATTRIBUTE6' THEN
1020 p_alias_val_att_to_match_row.ATTRIBUTE6 := l_alias_attribute_value;
1021 ELSIF p_segment = 'ATTRIBUTE7' THEN
1022 p_alias_val_att_to_match_row.ATTRIBUTE7 := l_alias_attribute_value;
1023 ELSIF p_segment = 'ATTRIBUTE8' THEN
1024 p_alias_val_att_to_match_row.ATTRIBUTE8 := l_alias_attribute_value;
1025 ELSIF p_segment = 'ATTRIBUTE9' THEN
1026 p_alias_val_att_to_match_row.ATTRIBUTE9 := l_alias_attribute_value;
1027 ELSIF p_segment = 'ATTRIBUTE10' THEN
1028 p_alias_val_att_to_match_row.ATTRIBUTE10 := l_alias_attribute_value;
1029 ELSIF p_segment = 'ATTRIBUTE11' THEN
1030 p_alias_val_att_to_match_row.ATTRIBUTE11 := l_alias_attribute_value;
1031 ELSIF p_segment = 'ATTRIBUTE12' THEN
1032 p_alias_val_att_to_match_row.ATTRIBUTE12 := l_alias_attribute_value;
1033 ELSIF p_segment = 'ATTRIBUTE13' THEN
1034 p_alias_val_att_to_match_row.ATTRIBUTE13 := l_alias_attribute_value;
1035 ELSIF p_segment = 'ATTRIBUTE14' THEN
1036 p_alias_val_att_to_match_row.ATTRIBUTE14 := l_alias_attribute_value;
1037 ELSIF p_segment = 'ATTRIBUTE15' THEN
1038 p_alias_val_att_to_match_row.ATTRIBUTE15 := l_alias_attribute_value;
1039 ELSIF p_segment = 'ATTRIBUTE16' THEN
1040 p_alias_val_att_to_match_row.ATTRIBUTE16 := l_alias_attribute_value;
1041 ELSIF p_segment = 'ATTRIBUTE17' THEN
1042 p_alias_val_att_to_match_row.ATTRIBUTE17 := l_alias_attribute_value;
1043 ELSIF p_segment = 'ATTRIBUTE18' THEN
1044 p_alias_val_att_to_match_row.ATTRIBUTE18 := l_alias_attribute_value;
1045 ELSIF p_segment = 'ATTRIBUTE19' THEN
1046 p_alias_val_att_to_match_row.ATTRIBUTE19 := l_alias_attribute_value;
1047 ELSIF p_segment = 'ATTRIBUTE20' THEN
1048 p_alias_val_att_to_match_row.ATTRIBUTE20 := l_alias_attribute_value;
1049 ELSIF p_segment = 'ATTRIBUTE21' THEN
1050 p_alias_val_att_to_match_row.ATTRIBUTE21 := l_alias_attribute_value;
1051 ELSIF p_segment = 'ATTRIBUTE22' THEN
1052 p_alias_val_att_to_match_row.ATTRIBUTE22 := l_alias_attribute_value;
1053 ELSIF p_segment = 'ATTRIBUTE23' THEN
1054 p_alias_val_att_to_match_row.ATTRIBUTE23 := l_alias_attribute_value;
1055 ELSIF p_segment = 'ATTRIBUTE24' THEN
1056 p_alias_val_att_to_match_row.ATTRIBUTE24 := l_alias_attribute_value;
1057 ELSIF p_segment = 'ATTRIBUTE25' THEN
1058 p_alias_val_att_to_match_row.ATTRIBUTE25 := l_alias_attribute_value;
1059 ELSIF p_segment = 'ATTRIBUTE26' THEN
1060 p_alias_val_att_to_match_row.ATTRIBUTE26 := l_alias_attribute_value;
1061 ELSIF p_segment = 'ATTRIBUTE27' THEN
1062 p_alias_val_att_to_match_row.ATTRIBUTE27 := l_alias_attribute_value;
1063 ELSIF p_segment = 'ATTRIBUTE28' THEN
1064 p_alias_val_att_to_match_row.ATTRIBUTE28 := l_alias_attribute_value;
1065 ELSIF p_segment = 'ATTRIBUTE29' THEN
1066 p_alias_val_att_to_match_row.ATTRIBUTE29 := l_alias_attribute_value;
1067 ELSIF p_segment = 'ATTRIBUTE30' THEN
1068 p_alias_val_att_to_match_row.ATTRIBUTE30 := l_alias_attribute_value;
1069 END IF;
1070
1071
1072 END set_attribute_on_att_to_match;
1073
1074 -- --------------------------------------------------------------------------
1075 -- |------------------------< get_alias_val_att_to_match>--------------------|
1076 -- --------------------------------------------------------------------------
1077 -- | This procedure is used to create the alias_val_att_to_match for a |
1078 -- | certain alias_value_id (use when DDF context type of alias |
1079 -- --------------------------------------------------------------------------
1080 PROCEDURE get_alias_val_att_to_match
1081 (p_alias_definition_id IN NUMBER,
1082 p_alias_value_id IN NUMBER,
1083 p_alias_val_att_to_match IN OUT NOCOPY t_alias_val_att_to_match)
1084 IS
1085
1086
1087 cursor crs_mapping_info is
1088 select hmc.bld_blk_info_type_id,segment,bld_blk_info_type,
1089 hatc.component_type,hatc.component_name,bldu.building_block_category,
1090 reference_object
1091 from hxc_mapping_components hmc,
1092 hxc_alias_types hat,
1093 hxc_alias_type_components hatc,
1094 hxc_alias_definitions had,
1095 hxc_bld_blk_info_type_usages bldu,
1096 hxc_bld_blk_info_types bld
1097 where had.alias_definition_id = p_alias_definition_id
1098 and had.alias_type_id = hat.alias_type_id
1099 and hatc.alias_type_id = hat.alias_type_id
1100 and hmc.mapping_component_id = hatc.mapping_component_id
1101 and bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
1102 and bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
1103 and bld.bld_blk_info_type_id = bldu.bld_blk_info_type_id
1104 order by hatc.component_name;
1105
1106
1107 l_index NUMBER;
1108 l_match_to_delete BOOLEAN;
1109 c_mapping_info crs_mapping_info%rowtype;
1110
1111 BEGIN
1112
1113 -- first we need to look if the definition of this alias
1114 -- exists in the reference global pl/sql table.
1115 IF g_alias_def_val_att_rec.exists(p_alias_value_id) THEN
1116 -- populate the out parameter
1117 FOR l_index in
1118 g_alias_def_val_att_rec(p_alias_value_id).start_index..g_alias_def_val_att_rec(p_alias_value_id).end_index
1119 LOOP
1120 p_alias_val_att_to_match(l_index) := g_alias_val_att_to_match(l_index);
1121 END LOOP;
1122
1123 ELSE
1124 -- find the first available index in the g_alias_val_att_to_match
1125 IF g_alias_val_att_to_match.exists(g_alias_val_att_to_match.last) THEN
1126 l_index := g_alias_val_att_to_match.last + 1;
1127 ELSE
1128 l_index := 1 ;
1129 END if;
1130
1131 -- record then the index
1132 g_alias_def_val_att_rec(p_alias_value_id).start_index := l_index;
1133
1134 -- Bug 13511713
1135 -- Fetch and cache mappings.
1136
1137 IF NOT g_mapping_tab.EXISTS(TO_CHAR(p_alias_definition_id))
1138 THEN
1139 OPEN crs_mapping_info;
1140 FETCH crs_mapping_info BULK COLLECT INTO g_mapping_tab(to_char(p_alias_definition_id));
1141 CLOSE crs_mapping_info;
1142
1143 END IF;
1144
1145 IF g_mapping_tab(to_char(p_alias_definition_id)).COUNT > 0
1146 THEN
1147 FOR i IN g_mapping_tab(to_char(p_alias_definition_id)).FIRST..
1148 g_mapping_tab(to_char(p_alias_definition_id)).LAST
1149 LOOP
1150 c_mapping_info := g_mapping_tab(to_char(p_alias_definition_id))(i);
1151 p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE_ID := c_mapping_info.bld_blk_info_type_id;
1152 p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE := c_mapping_info.bld_blk_info_type;
1153 p_alias_val_att_to_match(l_index).COMPONENT_NAME := c_mapping_info.component_name;
1154 p_alias_val_att_to_match(l_index).COMPONENT_TYPE := c_mapping_info.component_type;
1155 p_alias_val_att_to_match(l_index).REFERENCE_OBJECT := c_mapping_info.reference_object;
1156 p_alias_val_att_to_match(l_index).SEGMENT := c_mapping_info.segment;
1157 p_alias_val_att_to_match(l_index).MAPPING_ATT_CAT := c_mapping_info.building_block_category;
1158
1159
1160 -- for the specify component_type we need to find the value to put in the
1161 -- attribute information.
1162 set_attribute_on_att_to_match(
1163 p_alias_val_att_to_match_row => p_alias_val_att_to_match(l_index),
1164 p_alias_value_id => p_alias_value_id,
1165 p_component_type => c_mapping_info.component_type,
1166 p_segment => c_mapping_info.segment,
1167 p_bld_blk_info_type => c_mapping_info.bld_blk_info_type,
1168 p_attribute_mapping_category => c_mapping_info.building_block_category,
1169 p_match_to_delete => l_match_to_delete);
1170
1171
1172 IF (l_match_to_delete = TRUE) THEN
1173 p_alias_val_att_to_match.delete(l_index);
1174 ELSE
1175 g_alias_val_att_to_match(l_index) := p_alias_val_att_to_match(l_index);
1176
1177 l_index := l_index + 1;
1178 END IF;
1179
1180 END LOOP;
1181 END IF;
1182
1183 /*
1184 FOR c_mapping_info in crs_mapping_info LOOP
1185
1186
1187 ----dbms_output.put_line
1188 -- ('p_alias_val_att_to_match(l_index).SEGMENT: '||p_alias_val_att_to_match(l_index).SEGMENT);
1189
1190 p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE_ID := c_mapping_info.bld_blk_info_type_id;
1191 p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE := c_mapping_info.bld_blk_info_type;
1192 p_alias_val_att_to_match(l_index).COMPONENT_NAME := c_mapping_info.component_name;
1193 p_alias_val_att_to_match(l_index).COMPONENT_TYPE := c_mapping_info.component_type;
1194 p_alias_val_att_to_match(l_index).REFERENCE_OBJECT := c_mapping_info.reference_object;
1195 p_alias_val_att_to_match(l_index).SEGMENT := c_mapping_info.segment;
1196 p_alias_val_att_to_match(l_index).MAPPING_ATT_CAT := c_mapping_info.building_block_category;
1197
1198
1199 -- for the specify component_type we need to find the value to put in the
1200 -- attribute information.
1201 set_attribute_on_att_to_match(
1202 p_alias_val_att_to_match_row => p_alias_val_att_to_match(l_index),
1203 p_alias_value_id => p_alias_value_id,
1204 p_component_type => c_mapping_info.component_type,
1205 p_segment => c_mapping_info.segment,
1206 p_bld_blk_info_type => c_mapping_info.bld_blk_info_type,
1207 p_attribute_mapping_category => c_mapping_info.building_block_category,
1208 p_match_to_delete => l_match_to_delete);
1209
1210
1211 IF (l_match_to_delete = TRUE) THEN
1212 p_alias_val_att_to_match.delete(l_index);
1213 ELSE
1214 g_alias_val_att_to_match(l_index) := p_alias_val_att_to_match(l_index);
1215
1216 l_index := l_index + 1;
1217 END IF;
1218
1219 END LOOP;
1220 */
1221
1222
1223 -- keep the record in the global table
1224 g_alias_def_val_att_rec(p_alias_value_id).end_index := g_alias_val_att_to_match.last;
1225
1226 END IF;
1227
1228 END get_alias_val_att_to_match;
1229
1230
1231 -- --------------------------------------------------------------------------
1232 -- |------------------------< get_alias_val_att_to_match>--------------------|
1233 -- --------------------------------------------------------------------------
1234 -- | This procedure is used to create the alias_val_att_to_match for a |
1235 -- | certain alias_value_id (use when DDF context type of alias |
1236 -- --------------------------------------------------------------------------
1237 PROCEDURE get_alias_val_att_to_match
1238 (p_alias_definition_id IN NUMBER,
1239 p_alias_val_att_to_match IN OUT NOCOPY t_alias_val_att_to_match)
1240 IS
1241
1242
1243 cursor crs_mapping_info is
1244 select hmc.bld_blk_info_type_id,segment,bld_blk_info_type,
1245 hatc.component_type,hatc.component_name,bldu.building_block_category,
1246 reference_object
1247 from hxc_mapping_components hmc,
1248 hxc_alias_types hat,
1249 hxc_alias_type_components hatc,
1250 hxc_alias_definitions had,
1251 hxc_bld_blk_info_type_usages bldu,
1252 hxc_bld_blk_info_types bld
1253 where had.alias_definition_id = p_alias_definition_id
1254 and had.alias_type_id = hat.alias_type_id
1255 and hatc.alias_type_id = hat.alias_type_id
1256 and hmc.mapping_component_id = hatc.mapping_component_id
1257 and bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
1258 and bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
1259 and bld.bld_blk_info_type_id = bldu.bld_blk_info_type_id
1260 order by hatc.component_name;
1261
1262
1263 l_index NUMBER;
1264 l_match_to_delete BOOLEAN;
1265
1266 c_mapping_info crs_mapping_info%rowtype;
1267
1268 BEGIN
1269
1270 -- first we need to look if the definition of this alias
1271 -- exists in the reference global pl/sql table.
1272 IF g_alias_def_att_rec.exists(p_alias_definition_id) THEN
1273 -- populate the out parameter
1274 FOR l_index in
1275 g_alias_def_att_rec(p_alias_definition_id).start_index..g_alias_def_att_rec(p_alias_definition_id).end_index
1276 LOOP
1277 p_alias_val_att_to_match(l_index) := g_alias_def_att_to_match(l_index);
1278 END LOOP;
1279
1280 ELSE
1281 -- find the first available index in the g_alias_def_att_to_match
1282 IF g_alias_def_att_to_match.exists(g_alias_def_att_to_match.last) THEN
1283 l_index := g_alias_def_att_to_match.last + 1;
1284 ELSE
1285 l_index := 1 ;
1286 END if;
1287
1288 -- record then the index
1289 g_alias_def_att_rec(p_alias_definition_id).start_index := l_index;
1290
1291 -- Bug 13511713
1292 -- Fetch and cache mappings and use the cache in the below code.
1293
1294 IF NOT g_mapping_tab.EXISTS(TO_CHAR(p_alias_definition_id))
1295 THEN
1296 OPEN crs_mapping_info;
1297 FETCH crs_mapping_info BULK COLLECT INTO g_mapping_tab(to_char(p_alias_definition_id));
1298 CLOSE crs_mapping_info;
1299
1300 END IF;
1301
1302 IF g_mapping_tab(to_char(p_alias_definition_id)).COUNT > 0
1303 THEN
1304 FOR i IN g_mapping_tab(to_char(p_alias_definition_id)).FIRST..
1305 g_mapping_tab(to_char(p_alias_definition_id)).LAST
1306 LOOP
1307 c_mapping_info := g_mapping_tab(to_char(p_alias_definition_id))(i);
1308 p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE_ID := c_mapping_info.bld_blk_info_type_id;
1309 p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE := c_mapping_info.bld_blk_info_type;
1310 p_alias_val_att_to_match(l_index).COMPONENT_NAME := c_mapping_info.component_name;
1311 p_alias_val_att_to_match(l_index).COMPONENT_TYPE := c_mapping_info.component_type;
1312 p_alias_val_att_to_match(l_index).REFERENCE_OBJECT := c_mapping_info.reference_object;
1313 p_alias_val_att_to_match(l_index).SEGMENT := c_mapping_info.segment;
1314 p_alias_val_att_to_match(l_index).MAPPING_ATT_CAT := c_mapping_info.building_block_category;
1315
1316
1317 -- for the specify component_type we need to find the value to put in the
1318 -- attribute information.
1319 set_attribute_on_att_to_match(
1320 p_alias_val_att_to_match_row => p_alias_val_att_to_match(l_index),
1321 p_alias_value_id => null,
1322 p_component_type => c_mapping_info.component_type,
1323 p_segment => c_mapping_info.segment,
1324 p_bld_blk_info_type => c_mapping_info.bld_blk_info_type,
1325 p_attribute_mapping_category => c_mapping_info.building_block_category,
1326 p_match_to_delete => l_match_to_delete);
1327
1328
1329 g_alias_def_att_to_match(l_index) := p_alias_val_att_to_match(l_index);
1330
1331 l_index := l_index + 1;
1332
1333 END LOOP;
1334 END IF;
1335
1336
1337
1338 /*
1339 FOR c_mapping_info in crs_mapping_info LOOP
1340
1341
1342 ----dbms_output.put_line
1343 -- ('p_alias_val_att_to_match(l_index).SEGMENT: '||p_alias_val_att_to_match(l_index).SEGMENT);
1344
1345 p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE_ID := c_mapping_info.bld_blk_info_type_id;
1346 p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE := c_mapping_info.bld_blk_info_type;
1347 p_alias_val_att_to_match(l_index).COMPONENT_NAME := c_mapping_info.component_name;
1348 p_alias_val_att_to_match(l_index).COMPONENT_TYPE := c_mapping_info.component_type;
1349 p_alias_val_att_to_match(l_index).REFERENCE_OBJECT := c_mapping_info.reference_object;
1350 p_alias_val_att_to_match(l_index).SEGMENT := c_mapping_info.segment;
1351 p_alias_val_att_to_match(l_index).MAPPING_ATT_CAT := c_mapping_info.building_block_category;
1352
1353
1354 -- for the specify component_type we need to find the value to put in the
1355 -- attribute information.
1356 set_attribute_on_att_to_match(
1357 p_alias_val_att_to_match_row => p_alias_val_att_to_match(l_index),
1358 p_alias_value_id => null,
1359 p_component_type => c_mapping_info.component_type,
1360 p_segment => c_mapping_info.segment,
1361 p_bld_blk_info_type => c_mapping_info.bld_blk_info_type,
1362 p_attribute_mapping_category => c_mapping_info.building_block_category,
1363 p_match_to_delete => l_match_to_delete);
1364
1365
1366 g_alias_def_att_to_match(l_index) := p_alias_val_att_to_match(l_index);
1367
1368 l_index := l_index + 1;
1369
1370 END LOOP;
1371
1372 */
1373
1374
1375
1376 -- keep the record in the global table
1377 g_alias_def_att_rec(p_alias_definition_id).end_index := g_alias_def_att_to_match.last;
1378
1379 END IF;
1380
1381 END get_alias_val_att_to_match;
1382
1383 -- --------------------------------------------------------------------------
1384 -- |------------------------< get_tbb_id_reference_table>--------------------|
1385 -- --------------------------------------------------------------------------
1386 -- | This procedure creates a pl/sql reference table for attributes index |
1387 -- | attached to a tim_building_block_id |
1388 -- --------------------------------------------------------------------------
1389
1390 PROCEDURE get_tbb_id_reference_table
1391 (p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
1392 p_tbb_id_reference_table IN OUT NOCOPY t_tbb_id_reference)
1393 IS
1394
1395 l_attribute_index NUMBER;
1396
1397 l_tbb_id NUMBER;
1398 l_att_id NUMBER;
1399
1400 BEGIN
1401 -- for each tbb_id create an entry in the tbb_id_reference_table
1402 l_attribute_index := p_attributes.first;
1403
1404 LOOP
1405 EXIT WHEN NOT p_attributes.exists(l_attribute_index);
1406
1407 l_tbb_id := p_attributes(l_attribute_index).BUILDING_BLOCK_ID;
1408 l_att_id := p_attributes(l_attribute_index).TIME_ATTRIBUTE_ID;
1409
1410 IF p_tbb_id_reference_table.exists (l_tbb_id) THEN
1411
1412 p_tbb_id_reference_table(l_tbb_id).ATTRIBUTE_INDEX :=
1413 p_tbb_id_reference_table(l_tbb_id).ATTRIBUTE_INDEX ||'|'||l_attribute_index;
1414 ELSE
1415 p_tbb_id_reference_table(l_tbb_id).ATTRIBUTE_INDEX := '|'||l_attribute_index;
1416 END IF;
1417
1418 l_attribute_index := p_attributes.next(l_attribute_index);
1419
1420 END LOOP;
1421
1422 END get_tbb_id_reference_table;
1423
1424 -- ----------------------------------------------------------------------------
1425 -- |------------------------< get_tbb_date_reference_table>--------------------|
1426 -- ----------------------------------------------------------------------------
1427 -- | This procedure creates a pl/sql reference table for the day date |
1428 -- | attached to a detail time_building_block_id |
1429 -- ----------------------------------------------------------------------------
1430 PROCEDURE get_tbb_date_reference_table
1431 (p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,--hxc_self_service_time_deposit.timecard_info,
1432 p_tbb_date_reference_table IN OUT NOCOPY t_tbb_date_reference_table,
1433 p_timecard_start_time OUT NOCOPY DATE,
1434 p_timecard_stop_time OUT NOCOPY DATE)
1435 IS
1436
1437 l_tbb_index NUMBER;
1438
1439 l_tbb_id NUMBER;
1440 l_parent_tbb_id NUMBER;
1441 l_start_time DATE;
1442 l_stop_time DATE;
1443 l_scope hxc_time_building_blocks.SCOPE%TYPE;
1444 --l_template BOOLEAN := FALSE;
1445 --l_tbb_date_reference_table t_tbb_date_reference_table;
1446
1447 BEGIN
1448
1449 -- The first round we are looking for the date attached to the day.
1450 -- for each tbb_id create an entry in the tbb_id_reference_table
1451 l_tbb_index := p_blocks.first;
1452
1453 LOOP
1454 EXIT WHEN NOT p_blocks.exists(l_tbb_index);
1455
1456 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_tbb_index).START_TIME);
1457 l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_tbb_index).STOP_TIME);
1458 l_tbb_id := p_blocks(l_tbb_index).TIME_BUILDING_BLOCK_ID;
1459 l_scope := p_blocks(l_tbb_index).SCOPE;
1460
1461
1462
1463 IF (l_scope = 'TIMECARD' or l_scope = 'TIMECARD_TEMPLATE')
1464 THEN
1465 p_timecard_start_time := l_start_time;
1466 p_timecard_stop_time := l_stop_time;
1467 END IF;
1468
1469
1470 --dbms_output.put_line ('l_start_time:'||l_start_time);
1471 --dbms_output.put_line ('l_stop_time:'||l_stop_time);
1472 --dbms_output.put_line ('l_tbb_id:'||l_tbb_id);
1473 --dbms_output.put_line ('l_scope:'||l_scope);
1474
1475
1476
1477 -- we are recording the date attached to the day
1478 IF( not(p_tbb_date_reference_table.exists(l_tbb_id) )
1479 AND l_scope = 'DAY') THEN
1480 --dbms_output.put_line ('add one '||l_tbb_id);
1481
1482 p_tbb_date_reference_table(l_tbb_id).START_TIME := l_start_time;
1483 p_tbb_date_reference_table(l_tbb_id).STOP_TIME := l_stop_time;
1484
1485 END IF;
1486
1487 l_tbb_index := p_blocks.next(l_tbb_index);
1488
1489 END LOOP;
1490
1491 -- The second round we are looking for the date attached to the detail.
1492 -- by looking into the table previsouly created
1493 l_tbb_index := p_blocks.first;
1494
1495 LOOP
1496 EXIT WHEN NOT p_blocks.exists(l_tbb_index);
1497
1498 l_tbb_id := p_blocks(l_tbb_index).TIME_BUILDING_BLOCK_ID;
1499 l_parent_tbb_id := p_blocks(l_tbb_index).PARENT_BUILDING_BLOCK_ID;
1500 l_scope := p_blocks(l_tbb_index).SCOPE;
1501
1502 -- we are recording the date attached to the day
1503 IF (not(p_tbb_date_reference_table.exists(l_tbb_id))
1504 AND l_scope = 'DETAIL') THEN
1505 -- we are looking for his parent
1506 l_start_time := p_tbb_date_reference_table(l_parent_tbb_id).START_TIME;
1507 l_stop_time := p_tbb_date_reference_table(l_parent_tbb_id).STOP_TIME;
1508 --dbms_output.put_line ('add one '||l_tbb_id);
1509
1510 --dbms_output.put_line ('l_start_time:'||l_start_time);
1511 --dbms_output.put_line ('l_stop_time:'||l_stop_time);
1512
1513
1514 p_tbb_date_reference_table(l_tbb_id).START_TIME := l_start_time;
1515 p_tbb_date_reference_table(l_tbb_id).STOP_TIME := l_stop_time;
1516
1517 END IF;
1518
1519 l_tbb_index := p_blocks.next(l_tbb_index);
1520
1521 END LOOP;
1522
1523 END get_tbb_date_reference_table;
1524
1525
1526 -- ----------------------------------------------------------------------------
1527 -- |------------------< check_alternate_with_layout for SS >--------------------|
1528 -- ----------------------------------------------------------------------------
1529 -- | This function check that the reference object of the alias is the same |
1530 -- | of the layout |
1531 --------------------------------------------------------------------------------
1532 FUNCTION check_alias_with_layout
1533 (p_layout_alias_ref_name IN VARCHAR2,
1534 p_layout_att_cat IN VARCHAR2,
1535 p_resource_id IN NUMBER,
1536 p_layout_id IN NUMBER,
1537 p_alias_label IN VARCHAR2,
1538 p_alias_definition_id IN NUMBER,
1539 p_pref_start_date IN DATE,
1540 p_pref_end_date IN DATE)
1541 RETURN BOOLEAN IS
1542
1543 l_alias_type hxc_alias_types.alias_type%TYPE;
1544 l_reference_object hxc_alias_types.reference_object%TYPE;
1545 l_prompt hxc_alias_definitions_tl.prompt%TYPE;
1546
1547 l_index_global NUMBER;
1548
1549 l_find_alias BOOLEAN := FALSE;
1550
1551 BEGIN
1552
1553 get_alias_definition_info
1554 (p_alias_definition_id,
1555 l_alias_type,
1556 l_reference_object,
1557 l_prompt);
1558
1559 IF (g_alias_def_item.exists(g_alias_def_item.last)) THEN
1560 l_index_global := g_alias_def_item.last + 1;
1561 ELSE
1562 l_index_global := 1;
1563 END IF;
1564
1565
1566 IF p_layout_alias_ref_name = l_reference_object THEN
1567 -- we are adding the information into the alias table
1568 -- to do the translation
1569 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := p_alias_definition_id;
1570 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := p_layout_att_cat;
1571 g_alias_def_item(l_index_global).RESOURCE_ID := p_resource_id;
1572 g_alias_def_item(l_index_global).LAYOUT_ID := p_layout_id;
1573 g_alias_def_item(l_index_global).ALIAS_LABEL := p_alias_label;
1574 g_alias_def_item(l_index_global).PREF_START_DATE := p_pref_start_date;
1575 g_alias_def_item(l_index_global).PREF_END_DATE := p_pref_end_date;
1576
1577 -- bug 3083904 (minimal impact)
1578 -- implemented this way just in case to
1579 -- revert back the solution.
1580 l_find_alias := FALSE;
1581
1582 END IF;
1583
1584 RETURN l_find_alias;
1585
1586 END check_alias_with_layout;
1587
1588 -- ----------------------------------------------------------------------------
1589 -- |------------------------< get_alias_def_item for SS >--------------------|
1590 -- ----------------------------------------------------------------------------
1591 -- | This procedure returns by looking on the preference of the timekeeper |
1592 -- | a pl/sql table that contains the alias attribute information |
1593 --------------------------------------------------------------------------------
1594 PROCEDURE get_alias_def_item
1595 (p_resource_id IN NUMBER,
1596 p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,--hxc_self_service_time_deposit.building_block_attribute_info,
1597 p_alias_def_item IN OUT NOCOPY t_alias_def_item,
1598 p_start_time IN DATE,
1599 p_stop_time IN DATE,
1600 p_cache_label IN BOOLEAN DEFAULT FALSE) IS
1601
1602
1603 CURSOR csr_lay_alias_comp(p_layout_id NUMBER)
1604 IS
1605 select layout_component_id, QUALIFIER_ATTRIBUTE24,
1606 QUALIFIER_ATTRIBUTE27,QUALIFIER_ATTRIBUTE26,LABEL
1607 from hxc_layout_components_v
1608 where layout_id = p_layout_id;
1609 --and QUALIFIER_ATTRIBUTE26 like 'OTL_ALIAS%';
1610
1611 l_attribute_index NUMBER;
1612 l_pref_index NUMBER;
1613 l_alternate_name_index NUMBER;
1614 l_index_global NUMBER;
1615 l_comp_index NUMBER;
1616
1617 l_layout_id NUMBER;
1618
1619 l_pref_table hxc_preference_evaluation.t_pref_table;
1620
1621 l_layout_comp_id NUMBER := NULL;
1622 l_att_cat varchar2(80) := NULL;
1623 l_alias_ref varchar2(80) := NULL;
1624 l_alias_label varchar2(80) := NULL;
1625
1626 l_index_public_temp NUMBER;
1627 l_public_template BOOLEAN := FALSE;
1628 l_public_template_for_pref_evl BOOLEAN := FALSE;
1629 l_find_alias BOOLEAN;
1630 l_timecard_layout_id number;
1631
1632 BEGIN
1633
1634 -- first we need to find the layout id
1635 -- by looking into the attribute table
1636 l_attribute_index := p_attributes.first;
1637 LOOP
1638 EXIT WHEN
1639 (NOT p_attributes.exists(l_attribute_index));
1640
1641 IF (p_attributes(l_attribute_index).ATTRIBUTE_CATEGORY = 'LAYOUT') THEN
1642 --found the layout id
1643 l_layout_id := to_number(p_attributes(l_attribute_index).ATTRIBUTE2);
1644 l_timecard_layout_id := to_number(p_attributes(l_attribute_index).ATTRIBUTE1);
1645
1646 END IF;
1647
1648 IF (p_attributes(l_attribute_index).ATTRIBUTE_CATEGORY = 'TEMPLATES'
1649 AND p_attributes(l_attribute_index).ATTRIBUTE2 = 'PUBLIC')
1650 THEN
1651 -- we are in the case of the public template.
1652 -- Therefore we need to take alias definition id from the
1653 -- attributes.
1654
1655 --Always use the timecard layoutid for the publictemplates.
1656 l_layout_id := l_timecard_layout_id;
1657
1658 IF (p_attributes(l_attribute_index).ATTRIBUTE5 is not null
1659 or p_attributes(l_attribute_index).ATTRIBUTE6 is not null
1660 or p_attributes(l_attribute_index).ATTRIBUTE7 is not null
1661 or p_attributes(l_attribute_index).ATTRIBUTE8 is not null
1662 or p_attributes(l_attribute_index).ATTRIBUTE9 is not null
1663 or p_attributes(l_attribute_index).ATTRIBUTE10 is not null
1664 or p_attributes(l_attribute_index).ATTRIBUTE11 is not null
1665 or p_attributes(l_attribute_index).ATTRIBUTE12 is not null
1666 or p_attributes(l_attribute_index).ATTRIBUTE13 is not null
1667 or p_attributes(l_attribute_index).ATTRIBUTE14 is not null
1668 ) THEN
1669 l_public_template := TRUE;
1670 l_index_public_temp := l_attribute_index;
1671 END IF;
1672 l_public_template_for_pref_evl := TRUE;
1673 -- The reason for adding a new variable here is because l_public_template
1674 -- variable is specifically used to check the whether the alias translation
1675 -- needs to be done for public templates. This variable would yield false
1676 -- for project specific layouts, since there wouldnt be any alternate name.
1677 -- if we take out the not null checks for all the attributes, then alias
1678 -- translation process would be carried for projects timecard also which would
1679 -- have a slight performance impact. Need to confirm with joel regarding this
1680 --point.
1681
1682 END IF;
1683
1684 l_attribute_index := p_attributes.next(l_attribute_index);
1685
1686 END LOOP;
1687
1688 -- If the layout_id is null then we
1689 -- need to pick the one attached to the
1690 -- preference at the sysdate
1691 IF l_layout_id is null THEN
1692
1693 l_layout_id := to_number(
1694 hxc_preference_evaluation.resource_preferences(
1695 p_resource_id ,'TC_W_TCRD_LAYOUT',2));
1696 END IF;
1697
1698 IF l_timecard_layout_id is null THEN
1699
1700 l_timecard_layout_id:= to_number(
1701 hxc_preference_evaluation.resource_preferences(
1702 p_resource_id ,'TC_W_TCRD_LAYOUT',1));
1703 END IF;
1704
1705
1706
1707 -- Before going further we need to check if we are in the case of the
1708 -- public template and work on the attribute which could contain the
1709 -- alias definition id;
1710 IF (l_public_template) THEN
1711
1712 g_alias_def_item.delete;
1713 g_comp_label.delete;
1714
1715 FOR layout_comp IN csr_lay_alias_comp(l_layout_id) LOOP
1716
1717 --l_layout_comp_id := layout_comp.layout_component_id;
1718 l_att_cat := layout_comp.QUALIFIER_ATTRIBUTE26;
1719 l_alias_ref := layout_comp.QUALIFIER_ATTRIBUTE24;
1720 l_alias_label := layout_comp.label;
1721
1722 l_find_alias := FALSE;
1723
1724 IF p_attributes(l_index_public_temp).ATTRIBUTE5 is not null THEN
1725
1726 l_find_alias :=
1727 check_alias_with_layout
1728 (p_layout_alias_ref_name => l_alias_ref,
1729 p_layout_att_cat => l_att_cat,
1730 p_resource_id => p_resource_id,
1731 p_layout_id => l_layout_id,
1732 p_alias_label => l_alias_label,
1733 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE5),
1734 p_pref_start_date => p_start_time,
1735 p_pref_end_date => p_stop_time);
1736
1737 END IF;
1738
1739 IF p_attributes(l_index_public_temp).ATTRIBUTE6 is not null THEN
1740
1741 l_find_alias :=
1742 check_alias_with_layout
1743 (p_layout_alias_ref_name => l_alias_ref,
1744 p_layout_att_cat => l_att_cat,
1745 p_resource_id => p_resource_id,
1746 p_layout_id => l_layout_id,
1747 p_alias_label => l_alias_label,
1748 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE6),
1749 p_pref_start_date => p_start_time,
1750 p_pref_end_date => p_stop_time);
1751
1752 END IF;
1753
1754 IF p_attributes(l_index_public_temp).ATTRIBUTE7 is not null THEN
1755
1756 l_find_alias :=
1757 check_alias_with_layout
1758 (p_layout_alias_ref_name => l_alias_ref,
1759 p_layout_att_cat => l_att_cat,
1760 p_resource_id => p_resource_id,
1761 p_layout_id => l_layout_id,
1762 p_alias_label => l_alias_label,
1763 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE7),
1764 p_pref_start_date => p_start_time,
1765 p_pref_end_date => p_stop_time);
1766
1767 END IF;
1768
1769 IF p_attributes(l_index_public_temp).ATTRIBUTE8 is not null THEN
1770
1771 l_find_alias :=
1772 check_alias_with_layout
1773 (p_layout_alias_ref_name => l_alias_ref,
1774 p_layout_att_cat => l_att_cat,
1775 p_resource_id => p_resource_id,
1776 p_layout_id => l_layout_id,
1777 p_alias_label => l_alias_label,
1778 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE8),
1779 p_pref_start_date => p_start_time,
1780 p_pref_end_date => p_stop_time);
1781
1782 END IF;
1783
1784 IF p_attributes(l_index_public_temp).ATTRIBUTE9 is not null THEN
1785
1786 l_find_alias :=
1787 check_alias_with_layout
1788 (p_layout_alias_ref_name => l_alias_ref,
1789 p_layout_att_cat => l_att_cat,
1790 p_resource_id => p_resource_id,
1791 p_layout_id => l_layout_id,
1792 p_alias_label => l_alias_label,
1793 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE9),
1794 p_pref_start_date => p_start_time,
1795 p_pref_end_date => p_stop_time);
1796
1797 END IF;
1798
1799 IF p_attributes(l_index_public_temp).ATTRIBUTE10 is not null THEN
1800
1801 l_find_alias :=
1802 check_alias_with_layout
1803 (p_layout_alias_ref_name => l_alias_ref,
1804 p_layout_att_cat => l_att_cat,
1805 p_resource_id => p_resource_id,
1806 p_layout_id => l_layout_id,
1807 p_alias_label => l_alias_label,
1808 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE10),
1809 p_pref_start_date => p_start_time,
1810 p_pref_end_date => p_stop_time);
1811
1812 END IF;
1813
1814 IF p_attributes(l_index_public_temp).ATTRIBUTE11 is not null THEN
1815
1816 l_find_alias :=
1817 check_alias_with_layout
1818 (p_layout_alias_ref_name => l_alias_ref,
1819 p_layout_att_cat => l_att_cat,
1820 p_resource_id => p_resource_id,
1821 p_layout_id => l_layout_id,
1822 p_alias_label => l_alias_label,
1823 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE11),
1824 p_pref_start_date => p_start_time,
1825 p_pref_end_date => p_stop_time);
1826
1827 END IF;
1828
1829 IF p_attributes(l_index_public_temp).ATTRIBUTE12 is not null THEN
1830
1831 l_find_alias :=
1832 check_alias_with_layout
1833 (p_layout_alias_ref_name => l_alias_ref,
1834 p_layout_att_cat => l_att_cat,
1835 p_resource_id => p_resource_id,
1836 p_layout_id => l_layout_id,
1837 p_alias_label => l_alias_label,
1838 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE12),
1839 p_pref_start_date => p_start_time,
1840 p_pref_end_date => p_stop_time);
1841
1842 END IF;
1843
1844 IF p_attributes(l_index_public_temp).ATTRIBUTE13 is not null THEN
1845
1846 l_find_alias :=
1847 check_alias_with_layout
1848 (p_layout_alias_ref_name => l_alias_ref,
1849 p_layout_att_cat => l_att_cat,
1850 p_resource_id => p_resource_id,
1851 p_layout_id => l_layout_id,
1852 p_alias_label => l_alias_label,
1853 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE13),
1854 p_pref_start_date => p_start_time,
1855 p_pref_end_date => p_stop_time);
1856
1857 END IF;
1858
1859 IF p_attributes(l_index_public_temp).ATTRIBUTE14 is not null THEN
1860
1861 l_find_alias :=
1862 check_alias_with_layout
1863 (p_layout_alias_ref_name => l_alias_ref,
1864 p_layout_att_cat => l_att_cat,
1865 p_resource_id => p_resource_id,
1866 p_layout_id => l_layout_id,
1867 p_alias_label => l_alias_label,
1868 p_alias_definition_id => to_number(p_attributes(l_index_public_temp).ATTRIBUTE14),
1869 p_pref_start_date => p_start_time,
1870 p_pref_end_date => p_stop_time);
1871
1872 END IF;
1873
1874 END LOOP;
1875 -- associate the global table to the out parameters
1876 p_alias_def_item := g_alias_def_item;
1877 -- in this case we stop here.
1878 return;
1879
1880 END IF;
1881
1882
1883
1884 -- look if we have already the information of the resource into
1885 -- the global table
1886 -- looking the first row is enought. This table handle only
1887 -- the information for one layout therefore if the layout_id
1888 -- in the table is different with the parameter we are deleting the
1889 -- table info.
1890
1891 --if g_debug then
1892 --hr_utility.trace('layout_id '||l_layout_id);
1893 --hr_utility.trace('g_alias_def_item '||g_alias_def_item.first);
1894 --end if;
1895
1896 l_index_global := g_alias_def_item.first;
1897
1898
1899 -- we will not use
1900 IF g_alias_def_item.exists(l_index_global) THEN
1901
1902 IF g_alias_def_item(l_index_global).LAYOUT_ID = l_layout_id and
1903 trunc(g_alias_def_item(l_index_global).PREF_START_DATE) = trunc(p_start_time) and
1904 trunc(g_alias_def_item(l_index_global).PREF_END_DATE) = trunc(p_stop_time) and
1905 g_alias_def_item(l_index_global).RESOURCE_ID = p_resource_id THEN
1906 --
1907 p_alias_def_item := g_alias_def_item;
1908
1909 -- before return let check that the g_comp_label
1910 -- is not null
1911 IF (p_cache_label and g_comp_label.count = 0) THEN
1912
1913 FOR layout_comp IN csr_lay_alias_comp(l_timecard_layout_id) LOOP
1914
1915 --l_layout_comp_id := layout_comp.layout_component_id;
1916 l_att_cat := layout_comp.QUALIFIER_ATTRIBUTE26;
1917 l_alias_ref := layout_comp.QUALIFIER_ATTRIBUTE24;
1918 l_alias_label := layout_comp.label;
1919
1920 --IF (l_att_cat is not null) THEN
1921 l_comp_index := nvl(g_comp_label.last,0) + 1;
1922
1923 IF l_att_cat is null and layout_comp.QUALIFIER_ATTRIBUTE27 is null THEN
1924 null;
1925 ELSE
1926 g_comp_label(l_comp_index).MAPPING_ATT_CAT := l_att_cat;
1927 g_comp_label(l_comp_index).SEGMENT := layout_comp.QUALIFIER_ATTRIBUTE27;
1928 g_comp_label(l_comp_index).ATTRIBUTE1 := l_alias_label;
1929 END IF;
1930 --END IF;
1931
1932 END LOOP;
1933 END IF;
1934 -- finally we return
1935 /*
1936 if g_debug then
1937 hr_utility.trace('count '||p_alias_def_item.count);
1938 hr_utility.trace('ALIAS_DEFINITION_ID '||p_alias_def_item(p_alias_def_item.first).ALIAS_DEFINITION_ID);
1939 hr_utility.trace('ITEM_ATTRIBUTE_CATEGORY '||p_alias_def_item(p_alias_def_item.first).ITEM_ATTRIBUTE_CATEGORY);
1940 hr_utility.trace('RESOURCE_ID '||p_alias_def_item(p_alias_def_item.first).RESOURCE_ID);
1941 hr_utility.trace('LAYOUT_ID '||p_alias_def_item(p_alias_def_item.first).LAYOUT_ID);
1942 hr_utility.trace('ALIAS_LABEL '||p_alias_def_item(p_alias_def_item.first).ALIAS_LABEL);
1943 hr_utility.trace('PREF_START_DATE '||p_alias_def_item(p_alias_def_item.first).PREF_START_DATE);
1944 hr_utility.trace('PREF_END_DATE '||p_alias_def_item(p_alias_def_item.first).PREF_END_DATE);
1945 end if;
1946 */
1947 return;
1948 ELSE
1949 --
1950 g_alias_def_item.delete;
1951 g_comp_label.delete;
1952 END IF;
1953
1954 END IF;
1955
1956 --g_alias_def_item.delete;
1957 --dbms_output.put_line ('set 3 :');
1958
1959 -- we are calling the preference now for the resource
1960 if(l_public_template_for_pref_evl) THEN
1961 hxc_preference_evaluation.resource_preferences(
1962 p_resource_id => p_resource_id ,
1963 p_start_evaluation_date => SYSDATE,--FND_DATE.CANONICAL_TO_DATE(p_start_time),
1964 p_end_evaluation_date => hr_general.end_of_time,--FND_DATE.CANONICAL_TO_DATE(p_stop_time),
1965 p_pref_table => l_pref_table);
1966 ELSE
1967 hxc_preference_evaluation.resource_preferences(
1968 p_resource_id => p_resource_id ,
1969 p_start_evaluation_date => p_start_time,--FND_DATE.CANONICAL_TO_DATE(p_start_time),
1970 p_end_evaluation_date => p_stop_time,--FND_DATE.CANONICAL_TO_DATE(p_stop_time),
1971 p_pref_table => l_pref_table);
1972 END IF;
1973
1974
1975
1976 -- find the index of the alternate name preference and
1977 -- we are taking care in the case of a null layout
1978 l_pref_index :=l_pref_table.FIRST;
1979
1980 LOOP
1981 EXIT WHEN
1982 (NOT l_pref_table.exists(l_pref_index));
1983
1984 IF(l_pref_table(l_pref_index).preference_code = 'TC_W_TCRD_ALIASES') THEN
1985
1986 l_alternate_name_index := l_pref_index;
1987
1988 -- ELSIF (l_pref_table(l_pref_index).preference_code = 'TC_W_TCRD_LAYOUT'
1989 -- AND l_layout_id is null) THEN
1990 -- l_layout_id := l_pref_table(l_pref_index).attribute1;
1991 -- END IF;
1992
1993 -- l_pref_index := l_pref_table.next(l_pref_index);
1994
1995 --END LOOP;
1996
1997 --
1998 -- now we are ready to work out what alias need to go for the translation
1999 --
2000
2001 -- first we find the type of the alias in the layout
2002 -- we could have more than one.
2003 FOR layout_comp IN csr_lay_alias_comp(l_layout_id) LOOP
2004
2005 --l_layout_comp_id := layout_comp.layout_component_id;
2006 l_att_cat := layout_comp.QUALIFIER_ATTRIBUTE26;
2007 l_alias_ref := layout_comp.QUALIFIER_ATTRIBUTE24;
2008 l_alias_label := layout_comp.label;
2009
2010 l_find_alias := FALSE;
2011 /*
2012 IF (p_cache_label) THEN -- and l_att_cat is not null) THEN
2013
2014 l_comp_index := nvl(g_comp_label.last,0) + 1;
2015 IF l_att_cat is null and layout_comp.QUALIFIER_ATTRIBUTE27 is null THEN
2016 null;
2017 ELSE
2018 g_comp_label(l_comp_index).MAPPING_ATT_CAT := l_att_cat;
2019 g_comp_label(l_comp_index).SEGMENT := layout_comp.QUALIFIER_ATTRIBUTE27;
2020 g_comp_label(l_comp_index).ATTRIBUTE1 := l_alias_label;
2021 END IF;
2022
2023 END IF;
2024 */
2025
2026 -- we need to find the corresponding alias in the
2027 -- alternate name preference. We are checking on the
2028 -- reference object attached to the alias definition
2029 -- it is a little bit uguly here need to look
2030 -- on each attribute of the preference => 30 segs
2031 IF l_pref_table(l_alternate_name_index).ATTRIBUTE1 is not null THEN
2032
2033 l_find_alias :=
2034 check_alias_with_layout
2035 (p_layout_alias_ref_name => l_alias_ref,
2036 p_layout_att_cat => l_att_cat,
2037 p_resource_id => p_resource_id,
2038 p_layout_id => l_layout_id,
2039 p_alias_label => l_alias_label,
2040 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE1),
2041 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2042 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2043
2044 END IF;
2045
2046 IF l_pref_table(l_alternate_name_index).ATTRIBUTE2 is not null THEN
2047
2048 l_find_alias :=
2049 check_alias_with_layout
2050 (p_layout_alias_ref_name => l_alias_ref,
2051 p_layout_att_cat => l_att_cat,
2052 p_resource_id => p_resource_id,
2053 p_alias_label => l_alias_label,
2054 p_layout_id => l_layout_id,
2055 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE2),
2056 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2057 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2058
2059
2060 END IF;
2061
2062
2063 IF l_pref_table(l_alternate_name_index).ATTRIBUTE3 is not null THEN
2064
2065 l_find_alias :=
2066 check_alias_with_layout
2067 (p_layout_alias_ref_name => l_alias_ref,
2068 p_layout_att_cat => l_att_cat,
2069 p_resource_id => p_resource_id,
2070 p_layout_id => l_layout_id,
2071 p_alias_label => l_alias_label,
2072 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE3),
2073 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2074 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2075
2076
2077 END IF;
2078 IF l_pref_table(l_alternate_name_index).ATTRIBUTE4 is not null THEN
2079
2080 l_find_alias :=
2081 check_alias_with_layout
2082 (p_layout_alias_ref_name => l_alias_ref,
2083 p_layout_att_cat => l_att_cat,
2084 p_resource_id => p_resource_id,
2085 p_layout_id => l_layout_id,
2086 p_alias_label => l_alias_label,
2087 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE4),
2088 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2089 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2090
2091
2092 END IF;
2093 IF l_pref_table(l_alternate_name_index).ATTRIBUTE5 is not null THEN
2094
2095 l_find_alias :=
2096 check_alias_with_layout
2097 (p_layout_alias_ref_name => l_alias_ref,
2098 p_layout_att_cat => l_att_cat,
2099 p_resource_id => p_resource_id,
2100 p_layout_id => l_layout_id,
2101 p_alias_label => l_alias_label,
2102 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE5),
2103 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2104 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2105
2106
2107 END IF;
2108 IF l_pref_table(l_alternate_name_index).ATTRIBUTE6 is not null THEN
2109
2110 l_find_alias :=
2111 check_alias_with_layout
2112 (p_layout_alias_ref_name => l_alias_ref,
2113 p_layout_att_cat => l_att_cat,
2114 p_resource_id => p_resource_id,
2115 p_layout_id => l_layout_id,
2116 p_alias_label => l_alias_label,
2117 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE6),
2118 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2119 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2120
2121
2122 END IF;
2123 IF l_pref_table(l_alternate_name_index).ATTRIBUTE7 is not null THEN
2124
2125 l_find_alias :=
2126 check_alias_with_layout
2127 (p_layout_alias_ref_name => l_alias_ref,
2128 p_layout_att_cat => l_att_cat,
2129 p_resource_id => p_resource_id,
2130 p_layout_id => l_layout_id,
2131 p_alias_label => l_alias_label,
2132 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE7),
2133 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2134 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2135
2136
2137 END IF;
2138 IF l_pref_table(l_alternate_name_index).ATTRIBUTE8 is not null THEN
2139
2140 l_find_alias :=
2141 check_alias_with_layout
2142 (p_layout_alias_ref_name => l_alias_ref,
2143 p_layout_att_cat => l_att_cat,
2144 p_resource_id => p_resource_id,
2145 p_layout_id => l_layout_id,
2146 p_alias_label => l_alias_label,
2147 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE8),
2148 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2149 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2150
2151
2152 END IF;
2153 IF l_pref_table(l_alternate_name_index).ATTRIBUTE9 is not null THEN
2154
2155 l_find_alias :=
2156 check_alias_with_layout
2157 (p_layout_alias_ref_name => l_alias_ref,
2158 p_layout_att_cat => l_att_cat,
2159 p_resource_id => p_resource_id,
2160 p_layout_id => l_layout_id,
2161 p_alias_label => l_alias_label,
2162 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE9),
2163 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2164 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2165
2166
2167 END IF;
2168 IF l_pref_table(l_alternate_name_index).ATTRIBUTE10 is not null THEN
2169
2170 l_find_alias :=
2171 check_alias_with_layout
2172 (p_layout_alias_ref_name => l_alias_ref,
2173 p_layout_att_cat => l_att_cat,
2174 p_resource_id => p_resource_id,
2175 p_layout_id => l_layout_id,
2176 p_alias_label => l_alias_label,
2177 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE10),
2178 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2179 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2180
2181
2182 END IF;
2183 IF l_pref_table(l_alternate_name_index).ATTRIBUTE11 is not null THEN
2184
2185 l_find_alias :=
2186 check_alias_with_layout
2187 (p_layout_alias_ref_name => l_alias_ref,
2188 p_layout_att_cat => l_att_cat,
2189 p_resource_id => p_resource_id,
2190 p_layout_id => l_layout_id,
2191 p_alias_label => l_alias_label,
2192 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE11),
2193 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2194 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2195
2196
2197 END IF;
2198 IF l_pref_table(l_alternate_name_index).ATTRIBUTE12 is not null THEN
2199
2200 l_find_alias :=
2201 check_alias_with_layout
2202 (p_layout_alias_ref_name => l_alias_ref,
2203 p_layout_att_cat => l_att_cat,
2204 p_resource_id => p_resource_id,
2205 p_layout_id => l_layout_id,
2206 p_alias_label => l_alias_label,
2207 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE12),
2208 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2209 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2210
2211
2212 END IF;
2213 IF l_pref_table(l_alternate_name_index).ATTRIBUTE13 is not null THEN
2214
2215 l_find_alias :=
2216 check_alias_with_layout
2217 (p_layout_alias_ref_name => l_alias_ref,
2218 p_layout_att_cat => l_att_cat,
2219 p_resource_id => p_resource_id,
2220 p_layout_id => l_layout_id,
2221 p_alias_label => l_alias_label,
2222 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE13),
2223 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2224 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2225
2226
2227 END IF;
2228 IF l_pref_table(l_alternate_name_index).ATTRIBUTE14 is not null THEN
2229
2230 l_find_alias :=
2231 check_alias_with_layout
2232 (p_layout_alias_ref_name => l_alias_ref,
2233 p_layout_att_cat => l_att_cat,
2234 p_resource_id => p_resource_id,
2235 p_layout_id => l_layout_id,
2236 p_alias_label => l_alias_label,
2237 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE14),
2238 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2239 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2240
2241
2242 END IF;
2243 IF l_pref_table(l_alternate_name_index).ATTRIBUTE15 is not null THEN
2244
2245 l_find_alias :=
2246 check_alias_with_layout
2247 (p_layout_alias_ref_name => l_alias_ref,
2248 p_layout_att_cat => l_att_cat,
2249 p_resource_id => p_resource_id,
2250 p_layout_id => l_layout_id,
2251 p_alias_label => l_alias_label,
2252 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE15),
2253 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2254 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2255
2256
2257 END IF;
2258 IF l_pref_table(l_alternate_name_index).ATTRIBUTE16 is not null THEN
2259
2260 l_find_alias :=
2261 check_alias_with_layout
2262 (p_layout_alias_ref_name => l_alias_ref,
2263 p_layout_att_cat => l_att_cat,
2264 p_resource_id => p_resource_id,
2265 p_layout_id => l_layout_id,
2266 p_alias_label => l_alias_label,
2267 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE16),
2268 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2269 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2270
2271
2272 END IF;
2273 IF l_pref_table(l_alternate_name_index).ATTRIBUTE17 is not null THEN
2274
2275 l_find_alias :=
2276 check_alias_with_layout
2277 (p_layout_alias_ref_name => l_alias_ref,
2278 p_layout_att_cat => l_att_cat,
2279 p_resource_id => p_resource_id,
2280 p_layout_id => l_layout_id,
2281 p_alias_label => l_alias_label,
2282 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE17),
2283 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2284 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2285
2286
2287 END IF;
2288 IF l_pref_table(l_alternate_name_index).ATTRIBUTE18 is not null THEN
2289
2290 l_find_alias :=
2291 check_alias_with_layout
2292 (p_layout_alias_ref_name => l_alias_ref,
2293 p_layout_att_cat => l_att_cat,
2294 p_resource_id => p_resource_id,
2295 p_layout_id => l_layout_id,
2296 p_alias_label => l_alias_label,
2297 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE18),
2298 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2299 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2300
2301
2302 END IF;
2303 IF l_pref_table(l_alternate_name_index).ATTRIBUTE18 is not null THEN
2304
2305 l_find_alias :=
2306 check_alias_with_layout
2307 (p_layout_alias_ref_name => l_alias_ref,
2308 p_layout_att_cat => l_att_cat,
2309 p_resource_id => p_resource_id,
2310 p_layout_id => l_layout_id,
2311 p_alias_label => l_alias_label,
2312 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE19),
2313 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2314 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2315
2316
2317 END IF;
2318 IF l_pref_table(l_alternate_name_index).ATTRIBUTE20 is not null THEN
2319
2320 l_find_alias :=
2321 check_alias_with_layout
2322 (p_layout_alias_ref_name => l_alias_ref,
2323 p_layout_att_cat => l_att_cat,
2324 p_resource_id => p_resource_id,
2325 p_layout_id => l_layout_id,
2326 p_alias_label => l_alias_label,
2327 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE20),
2328 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2329 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2330
2331
2332 END IF;
2333 IF l_pref_table(l_alternate_name_index).ATTRIBUTE21 is not null THEN
2334
2335 l_find_alias :=
2336 check_alias_with_layout
2337 (p_layout_alias_ref_name => l_alias_ref,
2338 p_layout_att_cat => l_att_cat,
2339 p_resource_id => p_resource_id,
2340 p_layout_id => l_layout_id,
2341 p_alias_label => l_alias_label,
2342 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE21),
2343 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2344 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2345
2346
2347 END IF;
2348 IF l_pref_table(l_alternate_name_index).ATTRIBUTE22 is not null THEN
2349
2350 l_find_alias :=
2351 check_alias_with_layout
2352 (p_layout_alias_ref_name => l_alias_ref,
2353 p_layout_att_cat => l_att_cat,
2354 p_resource_id => p_resource_id,
2355 p_layout_id => l_layout_id,
2356 p_alias_label => l_alias_label,
2357 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE22),
2358 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2359 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2360
2361
2362 END IF;
2363 IF l_pref_table(l_alternate_name_index).ATTRIBUTE23 is not null THEN
2364
2365 l_find_alias :=
2366 check_alias_with_layout
2367 (p_layout_alias_ref_name => l_alias_ref,
2368 p_layout_att_cat => l_att_cat,
2369 p_resource_id => p_resource_id,
2370 p_layout_id => l_layout_id,
2371 p_alias_label => l_alias_label,
2372 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE23),
2373 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2374 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2375
2376
2377 END IF;
2378 IF l_pref_table(l_alternate_name_index).ATTRIBUTE24 is not null THEN
2379
2380 l_find_alias :=
2381 check_alias_with_layout
2382 (p_layout_alias_ref_name => l_alias_ref,
2383 p_layout_att_cat => l_att_cat,
2384 p_resource_id => p_resource_id,
2385 p_layout_id => l_layout_id,
2386 p_alias_label => l_alias_label,
2387 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE24),
2388 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2389 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2390
2391
2392 END IF;
2393 IF l_pref_table(l_alternate_name_index).ATTRIBUTE25 is not null THEN
2394
2395 l_find_alias :=
2396 check_alias_with_layout
2397 (p_layout_alias_ref_name => l_alias_ref,
2398 p_layout_att_cat => l_att_cat,
2399 p_resource_id => p_resource_id,
2400 p_layout_id => l_layout_id,
2401 p_alias_label => l_alias_label,
2402 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE25),
2403 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2404 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2405
2406
2407 END IF;
2408
2409 IF l_pref_table(l_alternate_name_index).ATTRIBUTE26 is not null THEN
2410
2411 l_find_alias :=
2412 check_alias_with_layout
2413 (p_layout_alias_ref_name => l_alias_ref,
2414 p_layout_att_cat => l_att_cat,
2415 p_resource_id => p_resource_id,
2416 p_layout_id => l_layout_id,
2417 p_alias_label => l_alias_label,
2418 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE26),
2419 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2420 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2421
2422
2423 END IF;
2424 IF l_pref_table(l_alternate_name_index).ATTRIBUTE27 is not null THEN
2425
2426 l_find_alias :=
2427 check_alias_with_layout
2428 (p_layout_alias_ref_name => l_alias_ref,
2429 p_layout_att_cat => l_att_cat,
2430 p_resource_id => p_resource_id,
2431 p_layout_id => l_layout_id,
2432 p_alias_label => l_alias_label,
2433 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE27),
2434 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2435 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2436
2437
2438 END IF;
2439 IF l_pref_table(l_alternate_name_index).ATTRIBUTE28 is not null THEN
2440
2441 l_find_alias :=
2442 check_alias_with_layout
2443 (p_layout_alias_ref_name => l_alias_ref,
2444 p_layout_att_cat => l_att_cat,
2445 p_resource_id => p_resource_id,
2446 p_layout_id => l_layout_id,
2447 p_alias_label => l_alias_label,
2448 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE28),
2449 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2450 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2451
2452
2453 END IF;
2454 IF l_pref_table(l_alternate_name_index).ATTRIBUTE29 is not null THEN
2455
2456 l_find_alias :=
2457 check_alias_with_layout
2458 (p_layout_alias_ref_name => l_alias_ref,
2459 p_layout_att_cat => l_att_cat,
2460 p_resource_id => p_resource_id,
2461 p_layout_id => l_layout_id,
2462 p_alias_label => l_alias_label,
2463 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE29),
2464 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2465 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2466
2467
2468 END IF;
2469 IF l_pref_table(l_alternate_name_index).ATTRIBUTE30 is not null THEN
2470
2471 l_find_alias :=
2472 check_alias_with_layout
2473 (p_layout_alias_ref_name => l_alias_ref,
2474 p_layout_att_cat => l_att_cat,
2475 p_resource_id => p_resource_id,
2476 p_layout_id => l_layout_id,
2477 p_alias_label => l_alias_label,
2478 p_alias_definition_id => to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE30),
2479 p_pref_start_date => l_pref_table(l_alternate_name_index).START_DATE,
2480 p_pref_end_date => l_pref_table(l_alternate_name_index).END_DATE);
2481
2482
2483 END IF;
2484
2485
2486 END LOOP;
2487 END IF;
2488
2489 l_pref_index := l_pref_table.next(l_pref_index);
2490
2491 END LOOP;
2492
2493 --hr_utility.trace_on('','JOEL');
2494 --hr_utility.trace(' l_timecard_layout_id '||l_timecard_layout_id);
2495
2496 -- add an extra check at the end of the procedure
2497 -- to make sure the g_comp_label is not null
2498 IF (p_cache_label and g_comp_label.count = 0) THEN
2499
2500 --hr_utility.trace(' inside loop ');
2501
2502 FOR layout_comp IN csr_lay_alias_comp(l_timecard_layout_id) LOOP
2503
2504 --l_layout_comp_id := layout_comp.layout_component_id;
2505 l_att_cat := layout_comp.QUALIFIER_ATTRIBUTE26;
2506 l_alias_ref := layout_comp.QUALIFIER_ATTRIBUTE24;
2507 l_alias_label := layout_comp.label;
2508
2509 --IF (l_att_cat is not null) THEN
2510
2511 l_comp_index := nvl(g_comp_label.last,0) + 1;
2512
2513 IF l_att_cat is null and layout_comp.QUALIFIER_ATTRIBUTE27 is null THEN
2514 null;
2515 ELSE
2516 g_comp_label(l_comp_index).MAPPING_ATT_CAT := l_att_cat;
2517 g_comp_label(l_comp_index).SEGMENT := layout_comp.QUALIFIER_ATTRIBUTE27;
2518 g_comp_label(l_comp_index).ATTRIBUTE1 := l_alias_label;
2519 END IF;
2520
2521 --END IF;
2522
2523 END LOOP;
2524 END IF;
2525
2526 --hr_utility.trace_off;
2527
2528 -- associate the global table to the out parameters
2529 p_alias_def_item := g_alias_def_item;
2530
2531 END get_alias_def_item;
2532
2533 -- ----------------------------------------------------------------------------
2534 -- |------------------------< get_alias_def_item for TK >--------------------|
2535 -- ----------------------------------------------------------------------------
2536 -- | This procedure returns by looking on the preference of the timekeeper |
2537 -- | a pl/sql table that contains the alias attribute information |
2538 --------------------------------------------------------------------------------
2539 PROCEDURE get_alias_def_item
2540 (p_timekeeper_id in NUMBER
2541 ,p_alias_def_item IN OUT NOCOPY t_alias_def_item) IS
2542
2543 l_index NUMBER;
2544 l_index_global NUMBER;
2545
2546 l_pref_table hxc_preference_evaluation.t_pref_table;
2547
2548
2549 BEGIN
2550
2551 -- look if we have already the information of the timekeeper into
2552 -- the global table
2553 -- looking the first row is enought. This table handle only
2554 -- the information for one timekeeper therefore if the timekeeper_id
2555 -- in the table is different with the parameter we are deleting the
2556 -- table info.
2557
2558 l_index_global := g_alias_def_item.first;
2559
2560 IF g_alias_def_item.exists(l_index_global) THEN
2561
2562 IF g_alias_def_item(l_index_global).RESOURCE_ID = p_timekeeper_id THEN
2563 --
2564 p_alias_def_item := g_alias_def_item;
2565 return;
2566 ELSE
2567 --
2568 g_alias_def_item.delete;
2569 END IF;
2570
2571 END IF;
2572
2573 -- otherwise we need to do the calculation
2574 -- first we need to find the preference for the timekeeper
2575 hxc_preference_evaluation.resource_preferences(p_resource_id => p_timekeeper_id,
2576 p_pref_table => l_pref_table,
2577 p_evaluation_date => sysdate );
2578
2579 --
2580 l_index_global := 0;
2581
2582 -- look now into the pref table the information that we need to.
2583 l_index := l_pref_table.first;
2584
2585 LOOP
2586 EXIT WHEN
2587 (NOT l_pref_table.exists(l_index));
2588
2589 IF (l_pref_table(l_index).preference_code = 'TK_TCARD_ATTRIBUTES_DEFINITION') THEN
2590
2591 IF l_pref_table(l_index).ATTRIBUTE1 is not null THEN
2592
2593 l_index_global := l_index_global + 1;
2594 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE1;
2595 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_1';
2596 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2597 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2598 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2599
2600
2601
2602 END IF;
2603
2604 IF l_pref_table(l_index).ATTRIBUTE2 is not null THEN
2605
2606 l_index_global := l_index_global + 1;
2607 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE2;
2608 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_2';
2609 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2610 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2611 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2612
2613 END IF;
2614
2615 IF l_pref_table(l_index).ATTRIBUTE3 is not null THEN
2616
2617 l_index_global := l_index_global + 1;
2618 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE3;
2619 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_3';
2620 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2621 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2622 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2623
2624 END IF;
2625
2626 IF l_pref_table(l_index).ATTRIBUTE4 is not null THEN
2627
2628 l_index_global := l_index_global + 1;
2629 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE4;
2630 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_4';
2631 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2632 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2633 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2634
2635 END IF;
2636
2637 IF l_pref_table(l_index).ATTRIBUTE5 is not null THEN
2638
2639 l_index_global := l_index_global + 1;
2640 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE5;
2641 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_5';
2642 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2643 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2644 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2645
2646 END IF;
2647
2648 IF l_pref_table(l_index).ATTRIBUTE6 is not null THEN
2649
2650 l_index_global := l_index_global + 1;
2651 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE6;
2652 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_6';
2653 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2654 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2655 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2656
2657 END IF;
2658
2659 IF l_pref_table(l_index).ATTRIBUTE7 is not null THEN
2660
2661 l_index_global := l_index_global + 1;
2662 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE7;
2663 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_7';
2664 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2665 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2666 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2667
2668 END IF;
2669
2670 IF l_pref_table(l_index).ATTRIBUTE8 is not null THEN
2671
2672 l_index_global := l_index_global + 1;
2673 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE8;
2674 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_8';
2675 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2676 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2677 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2678
2679 END IF;
2680
2681 IF l_pref_table(l_index).ATTRIBUTE9 is not null THEN
2682
2683 l_index_global := l_index_global + 1;
2684 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE9;
2685 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_9';
2686 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2687 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2688 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2689
2690 END IF;
2691
2692 IF l_pref_table(l_index).ATTRIBUTE10 is not null THEN
2693
2694 l_index_global := l_index_global + 1;
2695 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE10;
2696 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_10';
2697 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2698 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2699 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2700
2701 END IF;
2702
2703 IF l_pref_table(l_index).ATTRIBUTE11 is not null THEN
2704
2705 l_index_global := l_index_global + 1;
2706 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE11;
2707 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_11';
2708 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2709 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2710 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2711
2712 END IF;
2713
2714 IF l_pref_table(l_index).ATTRIBUTE12 is not null THEN
2715
2716 l_index_global := l_index_global + 1;
2717 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE12;
2718 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_12';
2719 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2720 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2721 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2722
2723 END IF;
2724
2725 IF l_pref_table(l_index).ATTRIBUTE13 is not null THEN
2726
2727 l_index_global := l_index_global + 1;
2728 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE13;
2729 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_13';
2730 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2731 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2732 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2733
2734 END IF;
2735
2736 IF l_pref_table(l_index).ATTRIBUTE14 is not null THEN
2737
2738 l_index_global := l_index_global + 1;
2739 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE14;
2740 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_14';
2741 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2742 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2743 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2744
2745 END IF;
2746
2747 IF l_pref_table(l_index).ATTRIBUTE15 is not null THEN
2748
2749 l_index_global := l_index_global + 1;
2750 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE15;
2751 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_15';
2752 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2753 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2754 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2755
2756 END IF;
2757
2758 IF l_pref_table(l_index).ATTRIBUTE16 is not null THEN
2759
2760 l_index_global := l_index_global + 1;
2761 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE16;
2762 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_16';
2763 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2764 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2765 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2766
2767 END IF;
2768
2769 IF l_pref_table(l_index).ATTRIBUTE17 is not null THEN
2770
2771 l_index_global := l_index_global + 1;
2772 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE17;
2773 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_17';
2774 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2775 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2776 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2777
2778 END IF;
2779
2780 IF l_pref_table(l_index).ATTRIBUTE18 is not null THEN
2781
2782 l_index_global := l_index_global + 1;
2783 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE18;
2784 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_18';
2785 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2786 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2787 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2788
2789 END IF;
2790
2791 IF l_pref_table(l_index).ATTRIBUTE19 is not null THEN
2792
2793 l_index_global := l_index_global + 1;
2794 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE19;
2795 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_19';
2796 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2797 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2798 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2799
2800 END IF;
2801
2802 IF l_pref_table(l_index).ATTRIBUTE20 is not null THEN
2803
2804 l_index_global := l_index_global + 1;
2805 g_alias_def_item(l_index_global).ALIAS_DEFINITION_ID := l_pref_table(l_index).ATTRIBUTE20;
2806 g_alias_def_item(l_index_global).ITEM_ATTRIBUTE_CATEGORY := 'OTL_ALIAS_ITEM_20';
2807 g_alias_def_item(l_index_global).RESOURCE_ID := p_timekeeper_id;
2808 g_alias_def_item(l_index_global).PREF_START_DATE := HR_GENERAL.START_OF_TIME;
2809 g_alias_def_item(l_index_global).PREF_END_DATE := HR_GENERAL.END_OF_TIME;
2810
2811 END IF;
2812 END IF;
2813
2814 l_index := l_pref_table.next(l_index);
2815 END LOOP;
2816
2817 -- associate the global table to the out parameters
2818 p_alias_def_item := g_alias_def_item;
2819
2820
2821 END get_alias_def_item;
2822
2823
2824
2825 -- ----------------------------------------------------------------------------
2826 -- |------------------------< get_alias_definition_info >--------------------|
2827 -- ----------------------------------------------------------------------------
2828 -- | This procedure returns the alias_type, reference_object, prompt for a |
2829 -- | specific alias_definition_id |
2830 -- ----------------------------------------------------------------------------
2831 PROCEDURE get_alias_definition_info
2832 (p_alias_definition_id in number,
2833 p_alias_type out nocopy varchar2,
2834 p_reference_object out nocopy varchar2,
2835 p_prompt out nocopy varchar2)
2836 IS
2837
2838 --
2839 -- Cursor to find the type
2840 --
2841 cursor c_alias_type is
2842 select alias_type,reference_object,prompt
2843 from hxc_alias_types hat
2844 , hxc_alias_definitions_tl hadtl
2845 , hxc_alias_definitions had
2846 where hat.alias_type_id = had.alias_type_id
2847 and had.alias_definition_id = p_alias_definition_id
2848 and hadtl.language = userenv('LANG')
2849 and hadtl.alias_definition_id = p_alias_definition_id;
2850
2851 l_alias_type hxc_alias_types.alias_type%TYPE;
2852 l_reference_object hxc_alias_types.reference_object%TYPE;
2853
2854 BEGIN
2855
2856 -- first look in the global table that we don't have the information
2857 IF g_alias_definition_info.exists(p_alias_definition_id) THEN
2858 p_alias_type := g_alias_definition_info(p_alias_definition_id).alias_type;
2859 p_reference_object := g_alias_definition_info(p_alias_definition_id).reference_object;
2860 p_prompt := g_alias_definition_info(p_alias_definition_id).prompt;
2861 ELSE
2862
2863 -- open the cursor
2864 open c_alias_type;
2865 Fetch c_alias_type Into p_alias_type,p_reference_object,p_prompt;
2866 close c_alias_type;
2867
2868 -- record the information in the global table
2869 g_alias_definition_info(p_alias_definition_id).alias_type := p_alias_type;
2870 g_alias_definition_info(p_alias_definition_id).reference_object := p_reference_object;
2871 g_alias_definition_info(p_alias_definition_id).prompt := p_prompt;
2872
2873 END IF;
2874
2875 END get_alias_definition_info;
2876
2877 -- ----------------------------------------------------------------------------
2878 -- |------------------------< get_alias_definition_info >--------------------|
2879 -- ----------------------------------------------------------------------------
2880 -- | This function returns the alias_definition_id attached to an value id |
2881 -- ----------------------------------------------------------------------------
2882
2883 FUNCTION get_alias_def_from_value
2884 (p_alias_value_id in number)
2885 RETURN NUMBER
2886 IS
2887
2888 l_alias_definition_id NUMBER := NULL;
2889
2890 BEGIN
2891
2892 begin
2893 select alias_definition_id
2894 into l_alias_definition_id
2895 from hxc_alias_values
2896 where alias_value_id = p_alias_value_id;
2897 EXCEPTION
2898 WHEN OTHERS
2899 THEN
2900 return l_alias_definition_id;
2901 END;
2902
2903 RETURN l_alias_definition_id;
2904
2905 END get_alias_def_from_value;
2906
2907
2908 -- ----------------------------------------------------------------------------
2909 -- |------------------------< make_stmt >--------------------|
2910 -- ----------------------------------------------------------------------------
2911 -- | This procedure returns the select statement for a specific value set id |
2912 -- ----------------------------------------------------------------------------
2913 FUNCTION make_stmt (p_vset_id in number,
2914 p_alias_type in varchar2)
2915 return varchar2 is
2916
2917 c number;
2918 l_stmt varchar2(2000);
2919 l_where varchar2(2000) := null;
2920
2921 BEGIN
2922
2923 IF p_alias_type = 'VALUE_SET_TABLE' THEN
2924
2925 select 'select to_char('||value_column_name||') display_value, '||
2926 replace(id_column_name, ',', ' || ''ALIAS_SEPARATOR'' || ')||' id_value '||
2927 ' from '||application_table_name
2928 into l_stmt
2929 from fnd_flex_validation_tables t
2930 where t.flex_value_set_id = p_vset_id;
2931
2932 select additional_where_clause
2933 into l_where
2934 from fnd_flex_validation_tables t
2935 where t.flex_value_set_id = p_vset_id;
2936
2937 if l_where is not null then
2938 l_stmt := l_stmt ||' '||l_where;
2939 end if;
2940
2941 ELSIF p_alias_type = 'VALUE_SET_INDEPENDENT' THEN
2942
2943 l_stmt := 'select flex_value_meaning display_value, '||
2944 ' flex_value_id id_value '||
2945 ' from fnd_flex_values_vl t'||
2946 ' where t.flex_value_set_id = '||p_vset_id||
2947 ' and t.enabled_flag=''Y''' ||
2948 ' and nvl(t.start_date_active,hr_general.start_of_time) <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
2949 ' and nvl(t.end_date_active,hr_general.end_of_time) >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') ';
2950
2951
2952 END IF;
2953 -- test the SQL
2954 c := dbms_sql.open_cursor ;
2955 --##MS trap any exceptions raised here so that we can close the cursor before
2956 -- raising the error
2957 BEGIN
2958 dbms_sql.parse(c , l_stmt , dbms_sql.native) ;
2959 EXCEPTION
2960 WHEN OTHERS THEN
2961 dbms_sql.close_cursor(c);
2962 RAISE;
2963 END;
2964
2965 --##MS close cursor if the parse raised no exceptions
2966 dbms_sql.close_cursor(c);
2967
2968 return (l_stmt);
2969
2970 exception
2971 when others then
2972 fnd_message.set_name('HXC', 'HXC_------INVALID_SQL_STATMENT');
2973 fnd_message.raise_error;
2974 return ( null );
2975
2976 END make_stmt;
2977
2978
2979
2980 -- ----------------------------------------------------------------------------
2981 -- |------------------------< replace_profile >--------------------|
2982 -- ----------------------------------------------------------------------------
2983 -- | This procedure replace the TK profile into the select statement |
2984 -- | with the value of the TK form items |
2985 -- ----------------------------------------------------------------------------
2986 FUNCTION replace_profile
2987 (x_select IN VARCHAR2,
2988 p_block_name IN VARCHAR2)
2989 RETURN VARCHAR2
2990 IS
2991
2992 l_x_select VARCHAR2(2000);
2993
2994 BEGIN
2995
2996 l_x_select:=x_select;
2997
2998 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_1'')', ':'||p_block_name||'ATTR_ID_1')
2999 into l_x_select
3000 from dual;
3001
3002 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_2'')', ':'||p_block_name||'ATTR_ID_2')
3003 into l_x_select
3004 from dual;
3005
3006 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_3'')', ':'||p_block_name||'ATTR_ID_3')
3007 into l_x_select
3008 from dual;
3009
3010 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_4'')', ':'||p_block_name||'ATTR_ID_4')
3011 into l_x_select
3012 from dual;
3013
3014 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_5'')', ':'||p_block_name||'ATTR_ID_5')
3015 into l_x_select
3016 from dual;
3017
3018 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_6'')', ':'||p_block_name||'ATTR_ID_6')
3019 into l_x_select
3020 from dual;
3021
3022 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_7'')', ':'||p_block_name||'ATTR_ID_7')
3023 into l_x_select
3024 from dual;
3025
3026 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_8'')', ':'||p_block_name||'ATTR_ID_8')
3027 into l_x_select
3028 from dual;
3029
3030 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_9'')', ':'||p_block_name||'ATTR_ID_9')
3031 into l_x_select
3032 from dual;
3033
3034 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_10'')', ':'||p_block_name||'ATTR_ID_10')
3035 into l_x_select
3036 from dual;
3037
3038 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_11'')', ':'||p_block_name||'ATTR_ID_11')
3039 into l_x_select
3040 from dual;
3041
3042 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_12'')', ':'||p_block_name||'ATTR_ID_12')
3043 into l_x_select
3044 from dual;
3045
3046 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_13'')', ':'||p_block_name||'ATTR_ID_13')
3047 into l_x_select
3048 from dual;
3049
3050 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_14'')', ':'||p_block_name||'ATTR_ID_14')
3051 into l_x_select
3052 from dual;
3053
3054 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_15'')', ':'||p_block_name||'ATTR_ID_15')
3055 into l_x_select
3056 from dual;
3057
3058 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_16'')', ':'||p_block_name||'ATTR_ID_16')
3059 into l_x_select
3060 from dual;
3061
3062 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_17'')', ':'||p_block_name||'ATTR_ID_17')
3063 into l_x_select
3064 from dual;
3065
3066 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_18'')', ':'||p_block_name||'ATTR_ID_18')
3067 into l_x_select
3068 from dual;
3069
3070 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_19'')', ':'||p_block_name||'ATTR_ID_19')
3071 into l_x_select
3072 from dual;
3073
3074 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_ATTR_20'')', ':'||p_block_name||'ATTR_ID_20')
3075 into l_x_select
3076 from dual;
3077
3078 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_RESOURCE_ID'')', ':'||'TIMECARD_INFO.'||'RESOURCE_ID')
3079 into l_x_select
3080 from dual;
3081
3082 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_TIMEKEEPER_ID'')', ':'||'HXCTKSTA.TIMEKEEPER_ID')
3083 into l_x_select
3084 from dual;
3085
3086 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_START_DATE'')', ':'||'HXCTKSTA.'||'FRDT')
3087 into l_x_select
3088 from dual;
3089
3090 select replace (upper(l_x_select),'FND_PROFILE.VALUE(''OTL_TK_END_DATE'')', ':'||'HXCTKSTA.'||'TODT')
3091 into l_x_select
3092 from dual;
3093
3094
3095 return l_x_select;
3096
3097
3098 end replace_profile;
3099
3100 -----------------------------------------------------------------
3101 -- get_vset_id_col_type
3102 -----------------------------------------------------------------
3103 FUNCTION get_vset_id_col_type (p_vset_id in number)
3104 return varchar2 is
3105
3106 l_id_col_type VARCHAR2(1);
3107 l_id_col_type_return VARCHAR2(80) := 'CHAR';
3108 l_to_char_in_col_id NUMBER := 0;
3109
3110 BEGIN
3111
3112 select id_column_type,instr(id_column_name,'to_char')
3113 into l_id_col_type, l_to_char_in_col_id
3114 from fnd_flex_validation_tables t
3115 where t.flex_value_set_id = p_vset_id;
3116
3117 -- if the type is a NUMBER
3118 IF l_id_col_type = 'N' THEN
3119 -- if there is not a to_char in the column_name
3120 IF l_to_char_in_col_id = 0 THEN
3121 l_id_col_type_return := 'NUMBER';
3122 END IF;
3123
3124 END IF;
3125
3126 return l_id_col_type_return;
3127
3128 exception
3129 when others then
3130 fnd_message.set_name('HXC', 'HXC_------INVALID_ID_COL_TYPE');
3131 fnd_message.raise_error;
3132 return ( null );
3133
3134
3135 END get_vset_id_col_type;
3136
3137
3138
3139 -- ----------------------------------------------------------------------------
3140 -- |------------------------< get_vset_table_type_select >--------------------|
3141 -- ----------------------------------------------------------------------------
3142 -- | This procedure returns the select statement for a specific value table |
3143 -- | set id |
3144 -- ----------------------------------------------------------------------------
3145 PROCEDURE get_vset_table_type_select
3146 (p_alias_definition_id IN NUMBER,
3147 x_select OUT NOCOPY VARCHAR2,
3148 p_id_type OUT NOCOPY VARCHAR2) IS
3149
3150 l_alias_type hxc_alias_types.alias_type%TYPE;
3151 l_reference_object hxc_alias_types.reference_object%TYPE;
3152 l_prompt hxc_alias_definitions_tl.prompt%TYPE;
3153
3154 l_select VARCHAR2(200);
3155 l_success NUMBER(15);
3156
3157 BEGIN
3158 -- first we need to check if the type associated
3159 -- to the alias_definition is 'VALUE_SET_TABLE'
3160 get_alias_definition_info
3161 (p_alias_definition_id,
3162 l_alias_type,
3163 l_reference_object,
3164 l_prompt);
3165
3166
3167 IF l_alias_type <> 'VALUE_SET_TABLE' THEN
3168 fnd_message.set_name('HXC', 'HXC_------_INVALID_ALIAS_TYPE');
3169 fnd_message.raise_error;
3170 END IF;
3171
3172 -- call the FND package to get the SQL
3173 if l_reference_object is not null THEN
3174 x_select := make_stmt (l_reference_object,l_alias_type);
3175 p_id_type := get_vset_id_col_type(l_reference_object);
3176 ELSE
3177 fnd_message.set_name('HXC', 'HXC_------_INVALID_REFERENCE_OBJECT');
3178 fnd_message.raise_error;
3179 END IF;
3180
3181 -- before sending back the information we do the profile replacement
3182 -- x_select := replace_profile(x_select,p_block_name);
3183
3184 END get_vset_table_type_select;
3185
3186 -- ----------------------------------------------------------------------------
3187 -- |------------------------< get_vset_indep_type_select >--------------------|
3188 -- ----------------------------------------------------------------------------
3189 -- | This procedure returns the select statement for a specific value |
3190 -- | independant set id |
3191 -- ----------------------------------------------------------------------------
3192 PROCEDURE get_vset_indep_type_select
3193 (p_alias_definition_id IN NUMBER,
3194 x_select OUT NOCOPY VARCHAR2) IS
3195
3196 l_alias_type hxc_alias_types.alias_type%TYPE;
3197 l_reference_object hxc_alias_types.reference_object%TYPE;
3198 l_prompt hxc_alias_definitions_tl.prompt%TYPE;
3199
3200 l_select VARCHAR2(200);
3201 l_success NUMBER(15);
3202
3203 BEGIN
3204 -- first we need to check if the type associated
3205 -- to the alias_definition is 'VALUE_SET_TABLE'
3206 get_alias_definition_info
3207 (p_alias_definition_id,
3208 l_alias_type,
3209 l_reference_object,
3210 l_prompt);
3211
3212
3213 IF l_alias_type <> 'VALUE_SET_INDEPENDENT' THEN
3214 fnd_message.set_name('HXC', 'HXC_------_INVALID_ALIAS_TYPE');
3215 fnd_message.raise_error;
3216 END IF;
3217
3218 -- call the FND package to get the SQL
3219 if l_reference_object is not null THEN
3220 x_select := make_stmt (l_reference_object,l_alias_type);
3221 ELSE
3222 fnd_message.set_name('HXC', 'HXC_------_INVALID_REFERENCE_OBJECT');
3223 fnd_message.raise_error;
3224 END IF;
3225
3226 END get_vset_indep_type_select;
3227
3228
3229 -- ----------------------------------------------------------------------------
3230 -- |------------------------< get_vset_none_type_property >--------------------|
3231 -- ----------------------------------------------------------------------------
3232 -- | This procedure returns the select statement for a specific value |
3233 -- | none set id |
3234 -- ----------------------------------------------------------------------------
3235 PROCEDURE get_vset_none_type_property
3236 (p_alias_definition_id IN NUMBER,
3237 p_format_type OUT NOCOPY VARCHAR2,
3238 p_maximum_size OUT NOCOPY NUMBER,
3239 p_minimum_value OUT NOCOPY NUMBER,
3240 p_maximum_value OUT NOCOPY NUMBER,
3241 p_number_precision OUT NOCOPY NUMBER
3242 ) IS
3243
3244
3245 -- Bug 7488230
3246 -- The cursor below used to pull out the properties
3247 -- for Value Set None type reference objects.
3248 -- There could have been two issues
3249 -- 1. Value Set with Date Validation, with a min value and max value
3250 -- Ideally another cursor should handle it, but since we have not
3251 -- been intending this yet, it could be dealt with later. The changes
3252 -- would include a second cursor and variables, and thus a redefined package
3253 -- Think it should work even without the min/max values, because the
3254 -- Timekeeper would anyway trim it off.
3255 -- The below cursor would not return any error anyways.
3256 -- 2. Value Set with Number validation, but the session expects a different
3257 -- format. Here, the Min value could be something like 10.45. The value
3258 -- set table would always have it in canonical format, ie.10.45.
3259 -- When you are in an Env with 10.000,00 format for number, rdbms expects
3260 -- ',' in place of '.'. Hence a conversion of canonical to number is put in.
3261 -- The Decode to ensure that this would happen only for a Number type value set
3262 -- If a date type value set, it would return NULL.
3263
3264 /*
3265 cursor c_vset_none_info (p_value_set_id number) is
3266 select format_type,maximum_size,minimum_value,maximum_value,number_precision
3267 from fnd_flex_value_sets
3268 where validation_type = 'N'
3269 and flex_value_set_id = p_value_set_id;
3270 */
3271
3272 CURSOR c_vset_none_info (p_value_set_id number)
3273 IS
3274 SELECT format_type,
3275 maximum_size,
3276 DECODE(format_type,'N',fnd_number.canonical_to_number(minimum_value),
3277 NULL),
3278 DECODE(format_type,'N',fnd_number.canonical_to_number(maximum_value),
3279 NULL),
3280 number_precision
3281 FROM fnd_flex_value_sets
3282 WHERE validation_type = 'N'
3283 AND flex_value_set_id = p_value_set_id;
3284
3285
3286 l_alias_type hxc_alias_types.alias_type%TYPE;
3287 l_reference_object hxc_alias_types.reference_object%TYPE;
3288 l_prompt hxc_alias_definitions_tl.prompt%TYPE;
3289
3290 l_select VARCHAR2(200);
3291 l_success NUMBER(15);
3292
3293 BEGIN
3294 -- first we need to check if the type associated
3295 -- to the alias_definition is 'VALUE_SET_TABLE'
3296 get_alias_definition_info
3297 (p_alias_definition_id,
3298 l_alias_type,
3299 l_reference_object,
3300 l_prompt);
3301
3302
3303 IF l_alias_type <> 'VALUE_SET_NONE' THEN
3304 fnd_message.set_name('HXC', 'HXC_------_INVALID_ALIAS_TYPE');
3305 fnd_message.raise_error;
3306 END IF;
3307
3308 -- call the FND package to get the property
3309 if l_reference_object is not null THEN
3310 OPEN c_vset_none_info (l_reference_object);
3311 FETCH c_vset_none_info
3312 into p_format_type,p_maximum_size,p_minimum_value,p_maximum_value,p_number_precision;
3313 CLOSE c_vset_none_info;
3314 ELSE
3315 fnd_message.set_name('HXC', 'HXC_------_INVALID_REFERENCE_OBJECT');
3316 fnd_message.raise_error;
3317 END IF;
3318
3319 END get_vset_none_type_property;
3320
3321
3322 -- ----------------------------------------------------------------------------
3323 -- |------------------------< get_otl_an_context_type_select >-----------------|
3324 -- ----------------------------------------------------------------------------
3325 -- | This procedure returns the select statement for a specific alias_defintion|
3326 -- | which is based on OTL DFF context |
3327 -- ----------------------------------------------------------------------------
3328 PROCEDURE get_otl_an_context_type_select
3329 (p_alias_definition_id IN NUMBER,
3330 p_timekeeper_person_type IN VARCHAR2 DEFAULT NULL,
3331 x_select OUT NOCOPY VARCHAR2) IS
3332
3333 l_alias_type hxc_alias_types.alias_type%TYPE;
3334 l_reference_object hxc_alias_types.reference_object%TYPE;
3335 l_prompt hxc_alias_definitions_tl.prompt%TYPE;
3336
3337 l_select VARCHAR2(200);
3338 l_success NUMBER(15);
3339
3340 BEGIN
3341 -- first we need to check if the type associated
3342 -- to the alias_definition is 'VALUE_SET_TABLE'
3343 get_alias_definition_info
3344 (p_alias_definition_id,
3345 l_alias_type,
3346 l_reference_object,
3347 l_prompt);
3348
3349
3350
3351 IF l_alias_type <> 'OTL_ALT_DDF' THEN
3352 fnd_message.set_name('HXC', 'HXC_------_INVALID_ALIAS_TYPE');
3353 fnd_message.raise_error;
3354 END IF;
3355
3356 -- call the FND package to get the SQL
3357 IF l_reference_object is not null THEN --and p_timekeeper_person_type <> 'CWK' THEN
3358 -- mutilple case here
3359 IF l_reference_object = 'ELEMENTS_EXPENDITURE_SLF' THEN
3360
3361 x_select := 'select distinct(havtl.alias_value_name) display_value, havtl.alias_value_id id_value '||
3362 'from hxc_alias_values_tl havtl,'||
3363 'hxc_alias_values hav,'||
3364 'hxc_alias_definitions had,'||
3365 'pa_online_expenditure_types_v pa,'||
3366 'PAY_ELEMENT_TYPES_F_TL ELEMENTTL, '||
3367 'PAY_ELEMENT_TYPES_F ELEMENT, '||
3368 'PAY_ELEMENT_CLASSIFICATIONS_TL CLASSIFICATIONTL, '||
3369 'PAY_ELEMENT_CLASSIFICATIONS CLASSIFICATION, '||
3370 'BEN_BENEFIT_CLASSIFICATIONS BENEFIT, '||
3371 'PAY_ELEMENT_LINKS_F LINK, '||
3372 'PER_ALL_ASSIGNMENTS_F ASGT, '||
3373 'PER_PERIODS_OF_SERVICE SERVICE_PERIOD '||
3374 'where hav.alias_definition_id = had.alias_definition_id '||
3375 'and havtl.alias_value_id = hav.alias_value_id '||
3376 'and havtl.language = userenv(''LANG'') '||
3377 'and hav.attribute2 = pa.expenditure_type '||
3378 'and hav.attribute3 = pa.system_linkage_function '||
3379 'and hav.attribute1 = ELEMENT.element_type_id '||
3380 'and hav.enabled_flag=''Y'' '||
3381 'and ELEMENT.ELEMENT_TYPE_ID = ELEMENTTL.ELEMENT_TYPE_ID '||
3382 'AND ELEMENTTL.LANGUAGE = USERENV(''LANG'') '||
3383 'AND CLASSIFICATION.CLASSIFICATION_ID = CLASSIFICATIONTL.CLASSIFICATION_ID (+) '||
3384 'AND DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID,NULL,''1'',CLASSIFICATIONTL.LANGUAGE) = '||
3385 'DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID,NULL,''1'',USERENV(''LANG'')) '||
3386 'AND ASGT.BUSINESS_GROUP_ID = LINK.BUSINESS_GROUP_ID '||
3387 'AND ELEMENT.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID '||
3388 'AND ELEMENT.BENEFIT_CLASSIFICATION_ID = BENEFIT.BENEFIT_CLASSIFICATION_ID (+) '||
3389 'AND ELEMENT.CLASSIFICATION_ID = CLASSIFICATION.CLASSIFICATION_ID '||
3390 'AND SERVICE_PERIOD.PERIOD_OF_SERVICE_ID = ASGT.PERIOD_OF_SERVICE_ID '||
3391 'AND ELEMENT.INDIRECT_ONLY_FLAG = ''N'' '||
3392 'AND UPPER (ELEMENT.ELEMENT_NAME) <> ''VERTEX'' '||
3393 'AND not exists '||
3394 '(select 1 '||
3395 'from HR_ORGANIZATION_INFORMATION HOI, '||
3396 'PAY_LEGISLATION_RULES PLR '||
3397 'WHERE plr.rule_type in '||
3398 '(''ADVANCE'',''ADVANCE_INDICATOR'',''ADV_DEDUCTION'', '||
3399 '''PAY_ADVANCE_INDICATOR'',''ADV_CLEARUP'',''DEFER_PAY'') '||
3400 'AND plr.rule_mode = to_char(element.element_type_id) '||
3401 'AND plr.legislation_code = hoi.org_information9 '||
3402 'AND HOI.ORGANIZATION_ID = ASGT.ORGANIZATION_ID '||
3403 ') '||
3404 'AND ELEMENT.CLOSED_FOR_ENTRY_FLAG = ''N'' '||
3405 'AND ELEMENT.ADJUSTMENT_ONLY_FLAG = ''N'' '||
3406 'AND ((LINK.PAYROLL_ID IS NOT NULL AND LINK.PAYROLL_ID = ASGT.PAYROLL_ID) '||
3407 'OR (LINK.LINK_TO_ALL_PAYROLLS_FLAG = ''Y'' AND ASGT.PAYROLL_ID IS NOT NULL) '||
3408 'OR (LINK.PAYROLL_ID IS NULL AND LINK.LINK_TO_ALL_PAYROLLS_FLAG = ''N'')) '||
3409 'AND (LINK.ORGANIZATION_ID = ASGT.ORGANIZATION_ID OR LINK.ORGANIZATION_ID IS NULL) '||
3410 'AND (LINK.POSITION_ID = ASGT.POSITION_ID OR LINK.POSITION_ID IS NULL) '||
3411 'AND (LINK.JOB_ID = ASGT.JOB_ID OR LINK.JOB_ID IS NULL) '||
3412 'AND (LINK.GRADE_ID = ASGT.GRADE_ID OR LINK.GRADE_ID IS NULL) '||
3413 'AND (LINK.LOCATION_ID = ASGT.LOCATION_ID OR LINK.LOCATION_ID IS NULL) '||
3414 'AND (LINK.PAY_BASIS_ID = ASGT.PAY_BASIS_ID OR LINK.PAY_BASIS_ID IS NULL) '||
3415 'AND (LINK.EMPLOYMENT_CATEGORY = ASGT.EMPLOYMENT_CATEGORY OR '||
3416 'LINK.EMPLOYMENT_CATEGORY IS NULL) '||
3417 'AND (ELEMENT.PROCESSING_TYPE = ''R'' OR ASGT.PAYROLL_ID IS NOT NULL) '||
3418 'and asgt.person_id = nvl(FND_PROFILE.VALUE(''OTL_TK_RESOURCE_ID''),FND_PROFILE.VALUE(''OTL_TK_TIMEKEEPER_ID'')) '||
3419 'and '||p_alias_definition_id ||' = had.alias_definition_id '||
3420
3421 'AND ELEMENT.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3422 'AND ELEMENT.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
3423 'AND date_from <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3424 'AND nvl(date_to,hr_general.end_of_time) >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
3425 'AND ASGT.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3426 'AND ASGT.EFFECTIVE_END_DATE >=FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
3427 'AND LINK.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3428 'AND LINK.EFFECTIVE_END_DATE >=FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
3429 'AND (LINK.PEOPLE_GROUP_ID IS NULL '||
3430 ' OR EXISTS ( '||
3431 ' SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE '||
3432 ' WHERE USAGE.ASSIGNMENT_ID = ASGT.ASSIGNMENT_ID '||
3433 ' AND USAGE.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID '||
3434 ' AND USAGE.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3435 ' AND USAGE.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE''))) '||
3436 ' AND (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NULL '||
3437 ' OR (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NOT NULL '||
3438 ' AND FND_PROFILE.VALUE(''OTL_TK_START_DATE'') <= DECODE(ELEMENT.POST_TERMINATION_RULE, '||
3439 ' ''L'', SERVICE_PERIOD.LAST_STANDARD_PROCESS_DATE, '||
3440 ' ''F'', NVL(SERVICE_PERIOD.FINAL_PROCESS_DATE, '||
3441 ' hr_general.end_of_time), '||
3442 ' SERVICE_PERIOD.ACTUAL_TERMINATION_DATE))) ';
3443
3444 ELSIF l_reference_object = 'PAYROLL_ELEMENTS' THEN
3445
3446 x_select := 'select distinct(havt.alias_value_name) Display_Value,'||
3447 ' hav.alias_value_id id_value '||
3448 'from hxc_alias_values hav, '||
3449 ' hxc_alias_values_tl havt, '||
3450 ' hxc_alias_definitions had, '||
3451 ' PAY_ELEMENT_TYPES_F_TL ELEMENTTL, '||
3452 ' PAY_ELEMENT_TYPES_F ELEMENT, '||
3453 ' PAY_ELEMENT_CLASSIFICATIONS_TL CLASSIFICATIONTL, '||
3454 ' PAY_ELEMENT_CLASSIFICATIONS CLASSIFICATION, '||
3455 ' BEN_BENEFIT_CLASSIFICATIONS BENEFIT, '||
3456 ' PAY_ELEMENT_LINKS_F LINK, '||
3457 ' PER_ALL_ASSIGNMENTS_F ASGT, '||
3458 ' PER_PERIODS_OF_SERVICE SERVICE_PERIOD '||
3459 'where asgt.person_id = nvl(FND_PROFILE.VALUE(''OTL_TK_RESOURCE_ID''),FND_PROFILE.VALUE(''OTL_TK_TIMEKEEPER_ID'')) '||
3460 ' and hav.attribute_category=''PAYROLL_ELEMENTS'' '||
3461 ' and hav.attribute1 = ELEMENT.element_type_id '||
3462 ' and hav.enabled_flag=''Y'' '||
3463 ' and had.alias_definition_id = hav.alias_definition_id '||
3464 ' and had.alias_definition_id = '||p_alias_definition_id||
3465 ' AND ELEMENT.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3466 ' AND ELEMENT.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
3467 ' and havt.language = USERENV(''LANG'') '||
3468 ' and havt.alias_value_id = hav.alias_value_id '||
3469 ' and hav.date_from <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3470 ' and nvl(hav.date_to,hr_general.end_of_time) >=FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
3471 ' and ELEMENT.ELEMENT_TYPE_ID = ELEMENTTL.ELEMENT_TYPE_ID '||
3472 ' AND ELEMENTTL.LANGUAGE = USERENV(''LANG'') '||
3473 ' AND CLASSIFICATION.CLASSIFICATION_ID = CLASSIFICATIONTL.CLASSIFICATION_ID (+) '||
3474 ' AND DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID,NULL,''1'',CLASSIFICATIONTL.LANGUAGE) = '||
3475 ' DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID,NULL,''1'',USERENV(''LANG'')) '||
3476 ' AND ASGT.BUSINESS_GROUP_ID = LINK.BUSINESS_GROUP_ID '||
3477 ' AND ELEMENT.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID '||
3478 ' AND ELEMENT.BENEFIT_CLASSIFICATION_ID = BENEFIT.BENEFIT_CLASSIFICATION_ID (+) '||
3479 ' AND ELEMENT.CLASSIFICATION_ID = CLASSIFICATION.CLASSIFICATION_ID '||
3480 ' AND SERVICE_PERIOD.PERIOD_OF_SERVICE_ID = ASGT.PERIOD_OF_SERVICE_ID '||
3481 ' AND ASGT.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3482 ' AND ASGT.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
3483 ' AND LINK.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3484 ' AND LINK.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') '||
3485 ' AND ELEMENT.INDIRECT_ONLY_FLAG = ''N'' '||
3486 ' AND UPPER (ELEMENT.ELEMENT_NAME) <> ''VERTEX'' '||
3487 ' AND not exists '||
3488 ' (select 1 '||
3489 ' from HR_ORGANIZATION_INFORMATION HOI, '||
3490 ' PAY_LEGISLATION_RULES PLR '||
3491 ' WHERE plr.rule_type in '||
3492 ' (''ADVANCE'',''ADVANCE_INDICATOR'',''ADV_DEDUCTION'', '||
3493 ' ''PAY_ADVANCE_INDICATOR'',''ADV_CLEARUP'',''DEFER_PAY'') '||
3494 ' AND plr.rule_mode = to_char(element.element_type_id) '||
3495 ' AND plr.legislation_code = hoi.org_information9 '||
3496 ' AND HOI.ORGANIZATION_ID = ASGT.ORGANIZATION_ID '||
3497 ' ) '||
3498 'AND ELEMENT.CLOSED_FOR_ENTRY_FLAG = ''N'' '||
3499 ' AND ELEMENT.ADJUSTMENT_ONLY_FLAG = ''N'' '||
3500 ' AND ((LINK.PAYROLL_ID IS NOT NULL AND LINK.PAYROLL_ID = ASGT.PAYROLL_ID) '||
3501 ' OR (LINK.LINK_TO_ALL_PAYROLLS_FLAG = ''Y'' AND ASGT.PAYROLL_ID IS NOT NULL) '||
3502 ' OR (LINK.PAYROLL_ID IS NULL AND LINK.LINK_TO_ALL_PAYROLLS_FLAG = ''N'')) '||
3503 ' AND (LINK.ORGANIZATION_ID = ASGT.ORGANIZATION_ID OR LINK.ORGANIZATION_ID IS NULL) '||
3504 ' AND (LINK.POSITION_ID = ASGT.POSITION_ID OR LINK.POSITION_ID IS NULL) '||
3505 ' AND (LINK.JOB_ID = ASGT.JOB_ID OR LINK.JOB_ID IS NULL) '||
3506 ' AND (LINK.GRADE_ID = ASGT.GRADE_ID OR LINK.GRADE_ID IS NULL) '||
3507 ' AND (LINK.LOCATION_ID = ASGT.LOCATION_ID OR LINK.LOCATION_ID IS NULL) '||
3508 ' AND (LINK.PAY_BASIS_ID = ASGT.PAY_BASIS_ID OR LINK.PAY_BASIS_ID IS NULL) '||
3509 ' AND (LINK.EMPLOYMENT_CATEGORY = ASGT.EMPLOYMENT_CATEGORY OR '||
3510 ' LINK.EMPLOYMENT_CATEGORY IS NULL) '||
3511 ' AND (LINK.PEOPLE_GROUP_ID IS NULL '||
3512 ' OR EXISTS ( '||
3513 ' SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE '||
3514 ' WHERE USAGE.ASSIGNMENT_ID = ASGT.ASSIGNMENT_ID '||
3515 ' AND USAGE.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID '||
3516 ' AND (USAGE.EFFECTIVE_START_DATE <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3517 ' AND USAGE.EFFECTIVE_END_DATE >= FND_PROFILE.VALUE(''OTL_TK_START_DATE'') ))) '||
3518 ' AND (ELEMENT.PROCESSING_TYPE = ''R'' OR ASGT.PAYROLL_ID IS NOT NULL) '||
3519 ' AND (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NULL '||
3520 ' OR (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NOT NULL '||
3521 ' AND FND_PROFILE.VALUE(''OTL_TK_START_DATE'') <= DECODE(ELEMENT.POST_TERMINATION_RULE, '||
3522 ' ''L'', NVL(SERVICE_PERIOD.LAST_STANDARD_PROCESS_DATE, hr_general.end_of_time),'||
3523 ' ''F'', NVL(SERVICE_PERIOD.FINAL_PROCESS_DATE, '||
3524 ' hr_general.end_of_time), '||
3525 ' SERVICE_PERIOD.ACTUAL_TERMINATION_DATE))) ';
3526 ELSE
3527
3528 x_select := 'select havt.alias_value_name Display_Value,'||
3529 ' hav.alias_value_id id_value '||
3530 'from hxc_alias_values hav, '||
3531 ' hxc_alias_values_tl havt, '||
3532 ' hxc_alias_definitions had '||
3533 'where hav.enabled_flag=''Y'' '||
3534 ' and had.alias_definition_id = hav.alias_definition_id '||
3535 ' and had.alias_definition_id = '||p_alias_definition_id||
3536 ' and havt.language = USERENV(''LANG'') '||
3537 ' and havt.alias_value_id = hav.alias_value_id '||
3538 ' and hav.date_from <= FND_PROFILE.VALUE(''OTL_TK_END_DATE'') '||
3539 ' and nvl(hav.date_to,hr_general.end_of_time) >=FND_PROFILE.VALUE(''OTL_TK_START_DATE'') ';
3540 END IF;
3541
3542 ELSE
3543 fnd_message.set_name('HXC', 'HXC_------_INVALID_REFERENCE_OBJECT');
3544 fnd_message.raise_error;
3545 END IF;
3546
3547 --if g_debug then
3548 --hr_utility.trace('x_select'||x_select);
3549 --end if;
3550
3551 END get_otl_an_context_type_select;
3552
3553
3554 -- ---------------------------------------------------------------------
3555 -- |------------------------< get_value_from_index >--------------------|
3556 -- ---------------------------------------------------------------------
3557 -- | This procedure returns for a specific index the string value |
3558 -- | i.e.: val1 - val2 and index =2 will return val2 |
3559 -- ---------------------------------------------------------------------
3560 FUNCTION get_value_from_index
3561 (p_str VARCHAR2
3562 ,p_index NUMBER
3563 )
3564 RETURN VARCHAR2
3565 IS
3566
3567 l_pos NUMBER := 1;
3568 l_current_index NUMBER := 1;
3569 l_end_index NUMBER := 0;
3570
3571 BEGIN
3572 IF (p_index = 1) THEN
3573 IF INSTR(p_str, hxc_alias_utility.ALIAS_SEPARATOR) = 0 THEN
3574 RETURN p_str;
3575 ELSE
3576 RETURN SUBSTR(p_str, 1, (INSTR(p_str, hxc_alias_utility.ALIAS_SEPARATOR) - 1));
3577 END IF;
3578 END IF;
3579
3580 LOOP
3581 EXIT WHEN l_end_index = LENGTH(p_str);
3582
3583 l_pos := l_pos + 1;
3584
3585 l_current_index := INSTR(p_str, hxc_alias_utility.ALIAS_SEPARATOR, (l_current_index + 1) );
3586
3587 IF (l_current_index = -1) THEN
3588 RETURN SUBSTR(p_str, l_end_index + 1, length(p_str) - l_end_index);
3589 ELSE
3590 l_end_index := INSTR(p_str, hxc_alias_utility.ALIAS_SEPARATOR, (l_current_index + 1));
3591
3592 IF (l_end_index = 0) THEN
3593 l_end_index := LENGTH(p_str);
3594 ELSE
3595 l_end_index := l_end_index - l_current_index - 15;
3596 END IF;
3597 END IF;
3598
3599 IF (l_pos = p_index) THEN
3600 RETURN SUBSTR(p_str, l_current_index + 15, l_end_index);
3601 END IF;
3602
3603 END LOOP;
3604
3605 RETURN NULL;
3606
3607 END get_value_from_index;
3608
3609 -- ---------------------------------------------------------------------
3610 -- |------------------------< query_invoice >--------------------|
3611 -- ---------------------------------------------------------------------
3612 -- | This procedure returns the result of a select statment |
3613 -- ---------------------------------------------------------------------
3614 FUNCTION query_invoice(p_select IN VARCHAR2)
3615 RETURN VARCHAR2 IS
3616
3617 TYPE cur_typ IS REF CURSOR;
3618 c cur_typ;
3619
3620 l_result VARCHAR2(300);
3621
3622 BEGIN
3623
3624 g_debug:=hr_utility.debug_enabled;
3625
3626 --if g_debug then
3627 --hr_utility.trace('p_select '||p_select);
3628 --end if;
3629
3630 OPEN c FOR p_select;
3631 FETCH c INTO l_result;
3632 CLOSE c;
3633
3634 RETURN l_result;
3635
3636 END query_invoice;
3637
3638 -- ---------------------------------------------------------------------
3639 -- |-----------------< get_apps_table_from_type >--------------------|
3640 -- ---------------------------------------------------------------------
3641 -- | This procedure returns the apps table of an alias definiton |
3642 -- ---------------------------------------------------------------------
3643 FUNCTION get_apps_table_from_type(p_alias_definition_id IN VARCHAR2)
3644 RETURN VARCHAR2 IS
3645
3646 l_apps_table VARCHAR2(240);
3647
3648 BEGIN
3649
3650 -- first look in the global table that we don't have the information
3651 IF g_alias_apps_tab_info.exists(p_alias_definition_id) THEN
3652 l_apps_table := g_alias_apps_tab_info(p_alias_definition_id).APPS_TAB_NAME;
3653 ELSE
3654
3655 select application_table_name
3656 into l_apps_table
3657 from fnd_flex_validation_tables t,
3658 hxc_alias_types h,
3659 hxc_alias_definitions hd
3660 where t.flex_value_set_id = h.reference_object
3661 and hd.alias_definition_id = p_alias_definition_id
3662 and hd.alias_type_id = h.alias_type_id;
3663
3664 -- add it into the global pl/sql table
3665 g_alias_apps_tab_info(p_alias_definition_id).APPS_TAB_NAME := l_apps_table;
3666
3667 END IF;
3668
3669 RETURN l_apps_table;
3670
3671 EXCEPTION
3672 WHEN OTHERS THEN
3673 RETURN NULL;
3674
3675
3676 END get_apps_table_from_type;
3677
3678
3679 -- ---------------------------------------------------------------------
3680 -- |------------------------< get_sfl_from_alias_value >---------------|
3681 -- ---------------------------------------------------------------------
3682 -- | This procedure returns the system linkage function attached to |
3683 -- | a specific alias value
3684 -- ---------------------------------------------------------------------
3685 FUNCTION get_sfl_from_alias_value(p_alias_value_id IN VARCHAR2)
3686 RETURN VARCHAR2 IS
3687
3688 l_sfl VARCHAR2(80);
3689
3690 BEGIN
3691
3692 select attribute3
3693 into l_sfl
3694 from hxc_alias_values
3695 where alias_value_id = p_alias_value_id;
3696
3697 RETURN l_sfl;
3698
3699 EXCEPTION
3700 WHEN OTHERS THEN
3701 RETURN NULL;
3702
3703
3704 END get_sfl_from_alias_value;
3705
3706 -- ---------------------------------------------------------------------
3707 -- |-------------------< get_alias_att_to_match_to_dep >---------------|
3708 -- ---------------------------------------------------------------------
3709 -- | This procedure creates the pl/sql table to match while the deposit |
3710 -- | of the attribute |
3711 -- ---------------------------------------------------------------------
3712 PROCEDURE get_alias_att_to_match_to_dep(p_alias_definition_id NUMBER
3713 ,p_alias_old_value_id NUMBER
3714 ,p_alias_type VARCHAR2
3715 ,p_original_value VARCHAR2
3716 ,p_alias_val_att_to_match OUT NOCOPY t_alias_val_att_to_match
3717 ,p_att_to_delete OUT NOCOPY BOOLEAN) IS
3718
3719 l_alias_val_att_to_match t_alias_val_att_to_match;
3720 l_index_att_to_match NUMBER;
3721
3722 l_select VARCHAR2(200) := NULL;
3723 l_where_clause VARCHAR2(300) := NULL;
3724 l_apps_table VARCHAR2(240);
3725
3726 l_number_column_id NUMBER;
3727
3728 l_value VARCHAR2(200);
3729
3730
3731 -- Bug No : 6943339
3732 -- The below cursor pulls out the datatype of the reference
3733 -- object based on which the alias value is built, and
3734 -- returns it.
3735
3736 CURSOR get_ref_datatype (p_reference_object NUMBER)
3737 IS SELECT format_type
3738 FROM fnd_flex_value_sets
3739 WHERE flex_value_set_id = p_reference_object ;
3740
3741 l_vset_data_type VARCHAR2(5);
3742
3743
3744 BEGIN
3745
3746 g_debug := hr_utility.debug_enabled;
3747 l_value := p_original_value;
3748 p_att_to_delete := FALSE;
3749
3750 -- look for the alias definition information
3751 -- get the alias attribute values to match with the attribute.
3752
3753 --if g_debug then
3754 --hr_utility.trace('p_alias_type'||p_alias_type);
3755 --hr_utility.trace('p_alias_definition_id'||p_alias_definition_id);
3756 --end if;
3757
3758 IF p_alias_type like 'VALUE_SET%' THEN
3759
3760 get_alias_val_att_to_match
3761 (p_alias_definition_id,
3762 l_alias_val_att_to_match);
3763
3764 --dump_alias_val_att_to_match(l_alias_val_att_to_match);
3765
3766 -- but here we need to understand what to do.
3767 -- the most simple is we just have one column_id
3768 -- which mean the value will be store
3769 IF l_alias_val_att_to_match.count = 1 THEN
3770 -- we are checking the alias_component_type to be 'COLUMN_ID' or 'VALUE
3771 IF l_alias_val_att_to_match.exists(l_alias_val_att_to_match.first) THEN
3772
3773 IF l_alias_val_att_to_match(l_alias_val_att_to_match.first).COMPONENT_TYPE = 'COLUMN_ID'
3774 or l_alias_val_att_to_match(l_alias_val_att_to_match.first).COMPONENT_TYPE = 'VALUE'
3775 THEN
3776
3777 -- Bug No : 6943339
3778 -- Added the below code to do conversions to canonical format, if
3779 -- the value is getting stored for the alias.
3780 -- Doing it only for Value Set None. Here we store the exact values
3781 -- and to enable proper retrieval, need to convert to canonical
3782 -- format.
3783 -- * Check in the associative array for value set formats if
3784 -- the data type exists.
3785 -- * If not, find that out and store it.
3786 -- * If the format is X ( based on FIELD_TYPE lookup ) it is a standard
3787 -- date format, so convert it to canonical date.
3788 -- * If the format is N, it is a standard number, convert to canonical number.
3789
3790 IF p_alias_type = 'VALUE_SET_NONE'
3791 THEN
3792 IF NOT hxc_alias_translator.g_vset_fmt.EXISTS(TO_CHAR(l_alias_val_att_to_match(l_alias_val_att_to_match.FIRST).reference_object))
3793 THEN
3794 OPEN get_ref_datatype(l_alias_val_att_to_match(l_alias_val_att_to_match.FIRST).reference_object);
3795 FETCH get_ref_datatype
3796 INTO l_vset_data_type;
3797 CLOSE get_ref_datatype;
3798 hxc_alias_translator.g_vset_fmt(TO_CHAR(l_alias_val_att_to_match(l_alias_val_att_to_match.FIRST).reference_object))
3799 := l_vset_data_type;
3800 END IF;
3801
3802 IF hxc_alias_translator.g_vset_fmt(TO_CHAR(l_alias_val_att_to_match(l_alias_val_att_to_match.FIRST).reference_object)) = 'X'
3803 THEN
3804
3805 l_value := FND_DATE.DATE_TO_CANONICAL(l_value);
3806
3807 ELSIF hxc_alias_translator.g_vset_fmt(TO_CHAR(l_alias_val_att_to_match(l_alias_val_att_to_match.FIRST).reference_object)) = 'N'
3808 THEN
3809 l_value := FND_NUMBER.NUMBER_TO_CANONICAL(l_value);
3810 END IF;
3811
3812
3813 END IF;
3814
3815
3816 -- we are updating the information in the
3817 -- attribute_to_match table.
3818 set_attribute_to_match_info
3819 (p_attribute_to_match => l_alias_val_att_to_match,
3820 p_index_in_table => l_alias_val_att_to_match.first,
3821 p_attribute_to_set => l_alias_val_att_to_match(l_alias_val_att_to_match.first).segment,
3822 p_bld_blk_info_type => l_alias_val_att_to_match(l_alias_val_att_to_match.first).bld_blk_info_type,
3823 p_mapping_att_cat => l_alias_val_att_to_match(l_alias_val_att_to_match.first).MAPPING_ATT_CAT,
3824 p_value_to_set => l_value);
3825
3826 END IF;
3827 END IF;
3828 ELSE
3829 -- there are some jobs to do here to calculate what to do.
3830 -- if there is more than one column_id then we need to split
3831 -- the number of value
3832 -- first we need to count the number of column_id contain
3833 -- in l_value_att_to_match table.
3834 l_number_column_id := 1;
3835
3836 l_index_att_to_match := l_alias_val_att_to_match.first;
3837
3838 -- first loop in the match table to find the column
3839 -- In this look we are setting up the column_id value
3840 LOOP
3841 EXIT WHEN
3842 (NOT l_alias_val_att_to_match.exists(l_index_att_to_match));
3843
3844 IF l_alias_val_att_to_match(l_index_att_to_match).COMPONENT_TYPE = 'COLUMN_ID' THEN
3845 -- go through the string and check the to_check
3846
3847 l_value :=
3848 get_value_from_index
3849 (p_str => p_original_value
3850 ,p_index => l_number_column_id
3851 );
3852
3853 -- we are matching here the column value.
3854 set_attribute_to_match_info
3855 (p_attribute_to_match => l_alias_val_att_to_match,
3856 p_index_in_table => l_index_att_to_match,
3857 p_attribute_to_set => l_alias_val_att_to_match(l_index_att_to_match).segment,
3858 p_bld_blk_info_type => l_alias_val_att_to_match(l_index_att_to_match).bld_blk_info_type,
3859 p_mapping_att_cat => l_alias_val_att_to_match(l_index_att_to_match).MAPPING_ATT_CAT,
3860 p_value_to_set => l_value);
3861
3862 l_number_column_id := l_number_column_id + 1;
3863
3864 -- we need also to build the where clause to find the column
3865 IF l_where_clause is null THEN
3866 l_where_clause := l_alias_val_att_to_match(l_index_att_to_match).COMPONENT_NAME||'='''||l_value||'''';
3867
3868 ELSE
3869 l_where_clause := l_where_clause ||'and '
3870 ||l_alias_val_att_to_match(l_index_att_to_match).COMPONENT_NAME||
3871 '='''||l_value||'''';
3872 END IF;
3873
3874 END IF;
3875
3876 l_index_att_to_match := l_alias_val_att_to_match.next(l_index_att_to_match);
3877
3878 END LOOP;
3879
3880 -- now we are looping to find the column and value to add in the
3881 -- match table.
3882
3883 l_index_att_to_match := l_alias_val_att_to_match.first;
3884
3885 -- from the type we need to know what is the application table that
3886 -- we need to run the query.
3887 l_apps_table :=
3888 get_apps_table_from_type (p_alias_definition_id => p_alias_definition_id);
3889
3890 LOOP
3891 EXIT WHEN
3892 (NOT l_alias_val_att_to_match.exists(l_index_att_to_match));
3893
3894 IF l_alias_val_att_to_match(l_index_att_to_match).COMPONENT_TYPE = 'COLUMN' THEN
3895
3896 l_select := 'select '||l_alias_val_att_to_match(l_index_att_to_match).COMPONENT_NAME||
3897 ' from '||l_apps_table||
3898 ' where '||l_where_clause;
3899
3900 l_value := query_invoice(p_select => l_select);
3901
3902 -- we are matching here the column value.
3903 set_attribute_to_match_info
3904 (p_attribute_to_match => l_alias_val_att_to_match,
3905 p_index_in_table => l_index_att_to_match,
3906 p_attribute_to_set => l_alias_val_att_to_match(l_index_att_to_match).segment,
3907 p_bld_blk_info_type => l_alias_val_att_to_match(l_index_att_to_match).bld_blk_info_type,
3908 p_mapping_att_cat => l_alias_val_att_to_match(l_index_att_to_match).MAPPING_ATT_CAT,
3909 p_value_to_set => l_value);
3910
3911 END IF;
3912
3913 l_index_att_to_match := l_alias_val_att_to_match.next(l_index_att_to_match);
3914
3915 END LOOP;
3916
3917 END IF;
3918
3919 -- else we are on the alias_value situation.
3920 ELSE
3921 -- little bit more compilcated here.
3922 -- first we need to check what this the next value
3923 --if g_debug then
3924 --hr_utility.trace('p_alias_old_value_id'||p_alias_old_value_id);
3925 --hr_utility.trace('p_original_value'||p_original_value);
3926 --end if;
3927 IF p_original_value is not null THEN
3928 -- find the attribute to match
3929 get_alias_val_att_to_match
3930 (p_alias_definition_id,
3931 p_original_value,
3932 l_alias_val_att_to_match);
3933
3934 --dump_alias_val_att_to_match(l_alias_val_att_to_match);
3935
3936 ELSE
3937 IF p_alias_old_value_id is not null THEN
3938 -- find the attribute to match and then to blank out or delete
3939 get_alias_val_att_to_match
3940 (p_alias_definition_id,
3941 p_alias_old_value_id,
3942 l_alias_val_att_to_match);
3943
3944 p_att_to_delete := TRUE;
3945 ELSE
3946 -- in the case that everything is null
3947 -- we need to remove all the attribute
3948 -- that match this definition
3949 get_alias_val_att_to_match
3950 (p_alias_definition_id,
3951 l_alias_val_att_to_match);
3952
3953 p_att_to_delete := TRUE;
3954
3955 END IF;
3956
3957 END IF;
3958
3959 END IF;
3960
3961 p_alias_val_att_to_match := l_alias_val_att_to_match;
3962
3963 END get_alias_att_to_match_to_dep;
3964
3965 -- ----------------------------------------------------------------------------
3966 -- |---------------------------< time_entry_rules_segment_trans >--------------------|
3967 -- ----------------------------------------------------------------------------
3968 PROCEDURE time_entry_rules_segment_trans
3969 (p_timecard_id IN NUMBER
3970 ,p_timecard_ovn IN NUMBER
3971 ,p_start_time IN DATE
3972 ,p_stop_time IN DATE
3973 ,p_resource_id IN NUMBER
3974 ,p_attr_change_table IN OUT NOCOPY hxc_time_entry_rules_utils_pkg.t_change_att_tab)
3975 IS
3976
3977 cursor c_layout is
3978 select
3979 a.time_attribute_id
3980 ,au.time_building_block_id
3981 ,null bld_blk_info_type
3982 ,a.attribute_category
3983 ,a.attribute1
3984 ,a.attribute2
3985 ,a.attribute3
3986 ,a.attribute4
3987 ,a.attribute5
3988 ,a.attribute6
3989 ,a.attribute7
3990 ,a.attribute8
3991 ,a.attribute9
3992 ,a.attribute10
3993 ,a.attribute11
3994 ,a.attribute12
3995 ,a.attribute13
3996 ,a.attribute14
3997 ,a.attribute15
3998 ,a.attribute16
3999 ,a.attribute17
4000 ,a.attribute18
4001 ,a.attribute19
4002 ,a.attribute20
4003 ,a.attribute21
4004 ,a.attribute22
4005 ,a.attribute23
4006 ,a.attribute24
4007 ,a.attribute25
4008 ,a.attribute26
4009 ,a.attribute27
4010 ,a.attribute28
4011 ,a.attribute29
4012 ,a.attribute30
4013 ,a.bld_blk_info_type_id
4014 ,a.object_version_number
4015 ,'N' NEW
4016 ,'N' CHANGED
4017 ,'N' PROCESS
4018 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
4019 from hxc_time_attribute_usages au,
4020 hxc_time_attributes a,
4021 hxc_time_building_blocks htbb
4022 where a.time_attribute_id = au.time_attribute_id
4023 and au.time_building_block_id = htbb.time_building_block_id
4024 and au.time_building_block_ovn = htbb.object_version_number
4025 and htbb.scope = 'TIMECARD'
4026 and htbb.time_building_block_id = p_timecard_id
4027 and htbb.object_version_number = p_timecard_ovn
4028 and htbb.resource_id = p_resource_id
4029 and a.attribute_category = 'LAYOUT';
4030
4031
4032 l_alias_def_item t_alias_def_item;
4033 l_alias_val_att_to_match t_alias_val_att_to_match;
4034 l_attributes HXC_ATTRIBUTE_TABLE_TYPE;
4035
4036 l_index_alias_def NUMBER;
4037 l_index_change_att NUMBER;
4038 l_index_alias NUMBER;
4039 l_index NUMBER;
4040 l_index_comp NUMBER;
4041
4042 l_change_att_cat VARCHAR2(80);
4043 l_changed_att VARCHAR2(240);
4044
4045 l_match_found BOOLEAN;
4046
4047 BEGIN
4048
4049 l_attributes := HXC_ATTRIBUTE_TABLE_TYPE();
4050
4051 -- first of all we need to check if we have the layout_id
4052 -- of this timecard in the cache table
4053 IF g_layout_attribute.exists(p_timecard_id) THEN
4054
4055 l_attributes := g_layout_attribute;
4056
4057 ELSE
4058
4059 g_layout_attribute := HXC_ATTRIBUTE_TABLE_TYPE();
4060 -- we delete the global table
4061 g_layout_attribute.delete;
4062 -- we fetch the layout_id for the timecard
4063
4064 -- finally we populate the global table
4065 FOR c_detail_attribute in c_layout LOOP
4066
4067 g_layout_attribute.extend;
4068 g_layout_attribute(g_layout_attribute.last) :=
4069 hxc_attribute_type (
4070 c_detail_attribute.time_attribute_id,
4071 c_detail_attribute.time_building_block_id,
4072 c_detail_attribute.attribute_category,
4073 c_detail_attribute.attribute1,
4074 c_detail_attribute.attribute2,
4075 c_detail_attribute.attribute3,
4076 c_detail_attribute.attribute4,
4077 c_detail_attribute.attribute5,
4078 c_detail_attribute.attribute6,
4079 c_detail_attribute.attribute7,
4080 c_detail_attribute.attribute8,
4081 c_detail_attribute.attribute9,
4082 c_detail_attribute.attribute10,
4083 c_detail_attribute.attribute11,
4084 c_detail_attribute.attribute12,
4085 c_detail_attribute.attribute13,
4086 c_detail_attribute.attribute14,
4087 c_detail_attribute.attribute15,
4088 c_detail_attribute.attribute16,
4089 c_detail_attribute.attribute17,
4090 c_detail_attribute.attribute18,
4091 c_detail_attribute.attribute19,
4092 c_detail_attribute.attribute20,
4093 c_detail_attribute.attribute21,
4094 c_detail_attribute.attribute22,
4095 c_detail_attribute.attribute23,
4096 c_detail_attribute.attribute24,
4097 c_detail_attribute.attribute25,
4098 c_detail_attribute.attribute26,
4099 c_detail_attribute.attribute27,
4100 c_detail_attribute.attribute28,
4101 c_detail_attribute.attribute29,
4102 c_detail_attribute.attribute30,
4103 c_detail_attribute.bld_blk_info_type_id,
4104 c_detail_attribute.object_version_number,
4105 c_detail_attribute.NEW,
4106 c_detail_attribute.CHANGED,
4107 c_detail_attribute.bld_blk_info_type,
4108 c_detail_attribute.PROCESS,
4109 c_detail_attribute.BUILDING_BLOCK_OVN);
4110
4111 END LOOP;
4112
4113 l_attributes := g_layout_attribute;
4114
4115 END IF;
4116
4117 -- now we call the procedure to get the alias definition item
4118 -- now I know the attribute_category and the alias definition id for
4119 -- the timecard
4120
4121
4122 get_alias_def_item
4123 (p_resource_id => p_resource_id,
4124 p_attributes => l_attributes,
4125 p_alias_def_item => l_alias_def_item,
4126 p_start_time => p_start_time,
4127 p_stop_time => p_stop_time,
4128 p_cache_label => TRUE);
4129
4130 -- now we are getting the attribute to match for the each alias_definition
4131 --if g_debug then
4132 --hr_utility.trace('--------------JOEL START----------------------------');
4133 --end if;
4134 --dump_alias_val_att_to_match(g_comp_label);
4135 --if g_debug then
4136 --hr_utility.trace('--------------JOEL END----------------------------');
4137 --end if;
4138
4139 l_index_alias_def := l_alias_def_item.first;
4140
4141 LOOP
4142 EXIT WHEN
4143 (NOT l_alias_def_item.exists(l_index_alias_def));
4144
4145 l_alias_val_att_to_match.delete;
4146
4147 get_alias_val_att_to_match
4148 (p_alias_definition_id => l_alias_def_item(l_index_alias_def).alias_definition_id,
4149 p_alias_val_att_to_match => l_alias_val_att_to_match);
4150
4151 --dump_alias_val_att_to_match(l_alias_val_att_to_match);
4152 -- now we need to go through the change attribute to see if we
4153 -- find a match
4154
4155 l_match_found := FALSE;
4156
4157 l_index_change_att := p_attr_change_table.first;
4158 LOOP
4159 EXIT WHEN
4160 (NOT p_attr_change_table.exists(l_index_change_att));
4161
4162 l_change_att_cat := p_attr_change_table(l_index_change_att).attribute_category;
4163 l_changed_att := p_attr_change_table(l_index_change_att).changed_attribute;
4164
4165 -- now we need to go through the alias to match table
4166 l_index_alias := l_alias_val_att_to_match.first;
4167 LOOP
4168 EXIT WHEN
4169 (NOT l_alias_val_att_to_match.exists(l_index_alias));
4170
4171 IF l_change_att_cat = l_alias_val_att_to_match(l_index_alias).BLD_BLK_INFO_TYPE
4172 AND l_changed_att = l_alias_val_att_to_match(l_index_alias).SEGMENT THEN
4173 l_match_found := TRUE;
4174 END IF;
4175
4176 l_index_alias := l_alias_val_att_to_match.next(l_index_alias);
4177
4178 END LOOP;
4179
4180 -- and we are populating the field name based on the global
4181 -- pl/sql table
4182 l_index_comp := g_comp_label.first;
4183 LOOP
4184 EXIT WHEN
4185 (NOT g_comp_label.exists(l_index_comp));
4186 /*
4187 if g_debug then
4188 hr_utility.trace('-------STEP 1 ------');
4189 hr_utility.trace('upper(l_change_att_cat) '||nvl(upper(l_change_att_cat),-1));
4190 hr_utility.trace('upper(g_comp_label(l_index_comp).MAPPING_ATT_CAT '||nvl(upper(g_comp_label(l_index_comp).MAPPING_ATT_CAT),-1));
4191 hr_utility.trace('upper(l_changed_att) '||upper(l_changed_att));
4192 hr_utility.trace('upper(g_comp_label(l_index_comp).SEGMENT '||upper(g_comp_label(l_index_comp).SEGMENT));
4193 hr_utility.trace('-------------');
4194 end if;
4195 */
4196 IF nvl(upper(l_change_att_cat),-1) =
4197 nvl(upper(g_comp_label(l_index_comp).MAPPING_ATT_CAT),-1)
4198 AND upper(l_changed_att) = upper(g_comp_label(l_index_comp).SEGMENT)
4199 THEN
4200 -- populate the field name
4201 p_attr_change_table(l_index_change_att).field_name :=
4202 g_comp_label(l_index_comp).ATTRIBUTE1;
4203 --if g_debug then
4204 --hr_utility.trace(' g_comp_label(l_index_comp).ATTRIBUTE1 '|| g_comp_label(l_index_comp).ATTRIBUTE1);
4205 --end if;
4206 END IF;
4207
4208 l_index_comp := g_comp_label.next(l_index_comp);
4209
4210 END LOOP;
4211
4212 l_index_change_att := p_attr_change_table.next(l_index_change_att);
4213
4214 END LOOP;
4215
4216 -- before we go the next alias definition
4217 -- we need to check if we need or not to add the OTL_ALIAS
4218 -- in p_attr_change_table
4219 IF (l_match_found) THEN
4220 -- we are adding the information
4221 l_index := p_attr_change_table.last+1;
4222 p_attr_change_table(l_index).attribute_category
4223 := l_alias_def_item(l_index_alias_def).ITEM_ATTRIBUTE_CATEGORY;
4224 p_attr_change_table(l_index).field_name
4225 := l_alias_def_item(l_index_alias_def).ALIAS_LABEL;
4226 p_attr_change_table(l_index).changed_attribute := 'ATTRIBUTE1';
4227
4228 END IF;
4229
4230 l_index_alias_def := l_alias_def_item.next(l_index_alias_def);
4231
4232 END LOOP;
4233
4234 -- add a condition in the case we don't have
4235 -- an alias in the layout.
4236 IF (l_alias_def_item.count = 0) THEN
4237
4238 l_index_change_att := p_attr_change_table.first;
4239 LOOP
4240 EXIT WHEN
4241 (NOT p_attr_change_table.exists(l_index_change_att));
4242
4243 l_change_att_cat := p_attr_change_table(l_index_change_att).attribute_category;
4244 l_changed_att := p_attr_change_table(l_index_change_att).changed_attribute;
4245
4246 -- and we are populating the field name based on the global
4247 -- pl/sql table
4248 l_index_comp := g_comp_label.first;
4249 LOOP
4250 EXIT WHEN
4251 (NOT g_comp_label.exists(l_index_comp));
4252 /*
4253 if g_debug then
4254 hr_utility.trace('-------STEP 2------');
4255 hr_utility.trace('upper(l_change_att_cat) '||nvl(upper(l_change_att_cat),-1));
4256 hr_utility.trace('upper(g_comp_label(l_index_comp).MAPPING_ATT_CAT '||nvl(upper(g_comp_label(l_index_comp).MAPPING_ATT_CAT),-1));
4257 hr_utility.trace('upper(l_changed_att) '||upper(l_changed_att));
4258 hr_utility.trace('upper(g_comp_label(l_index_comp).SEGMENT '||upper(g_comp_label(l_index_comp).SEGMENT));
4259 hr_utility.trace('-------------');
4260 end if;
4261 */
4262
4263 IF nvl(upper(l_change_att_cat),-1)
4264 = nvl(upper(g_comp_label(l_index_comp).MAPPING_ATT_CAT),-1)
4265 AND upper(l_changed_att) = upper(g_comp_label(l_index_comp).SEGMENT)
4266 THEN
4267 -- populate the field name
4268 p_attr_change_table(l_index_change_att).field_name :=
4269 g_comp_label(l_index_comp).ATTRIBUTE1;
4270 --if g_debug then
4271 --hr_utility.trace(' g_comp_label(l_index_comp).ATTRIBUTE1 '|| g_comp_label(l_index_comp).ATTRIBUTE1);
4272 --end if;
4273 END IF;
4274
4275 l_index_comp := g_comp_label.next(l_index_comp);
4276
4277 END LOOP;
4278
4279 l_index_change_att := p_attr_change_table.next(l_index_change_att);
4280
4281 END LOOP;
4282
4283 END IF;
4284
4285 --if g_debug then
4286 --hr_utility.trace('--------------JOEL END---------------------------');
4287 --end if;
4288
4289 /*
4290 FOR I IN 1..p_attr_change_table.COUNT LOOP
4291 if g_debug then
4292 hr_utility.trace(' catg ='|| p_attr_change_table(i).attribute_category ||
4293 ' changed ='|| p_attr_change_table(i).changed_attribute ||
4294 ' field name ='|| p_attr_change_table(i).field_name ||
4295 ' org_att_catg ='|| p_attr_change_table(i).org_attribute_category ||
4296 ' org_changed_attr ='|| p_attr_change_table(i).ORG_changed_attribute
4297 );
4298 end if;
4299 END LOOP;
4300 */
4301
4302 END time_entry_rules_segment_trans;
4303
4304
4305
4306
4307 -- ---------------------------------------------------------------------
4308 -- |-------------------< remove_empty_attribute >---------------|
4309 -- ---------------------------------------------------------------------
4310 -- | Not in use |
4311 -- ---------------------------------------------------------------------
4312 /*
4313 PROCEDURE remove_empty_attribute
4314 (p_attributes in out NOCOPY hxc_self_service_time_deposit.building_block_attribute_info) IS
4315
4316
4317 l_index NUMBER;
4318 l_index_to_delete NUMBER;
4319
4320 l_to_delete BOOLEAN;
4321
4322 BEGIN
4323
4324 l_index := p_attributes.first;
4325 LOOP
4326 EXIT WHEN
4327 (NOT p_attributes.exists(l_index));
4328
4329 l_to_delete := TRUE;
4330
4331 -- we need to check that at least on attribute is not null
4332 -- otherwise we are removing it.
4333 IF p_attributes(l_index).ATTRIBUTE1 is not NULL THEN
4334 l_to_delete := FALSE;
4335 ELSIF p_attributes(l_index).ATTRIBUTE2 is not NULL THEN
4336 l_to_delete := FALSE;
4337 ELSIF p_attributes(l_index).ATTRIBUTE3 is not NULL THEN
4338 l_to_delete := FALSE;
4339 ELSIF p_attributes(l_index).ATTRIBUTE4 is not NULL THEN
4340 l_to_delete := FALSE;
4341 ELSIF p_attributes(l_index).ATTRIBUTE5 is not NULL THEN
4342 l_to_delete := FALSE;
4343 ELSIF p_attributes(l_index).ATTRIBUTE6 is not NULL THEN
4344 l_to_delete := FALSE;
4345 ELSIF p_attributes(l_index).ATTRIBUTE7 is not NULL THEN
4346 l_to_delete := FALSE;
4347 ELSIF p_attributes(l_index).ATTRIBUTE8 is not NULL THEN
4348 l_to_delete := FALSE;
4349 ELSIF p_attributes(l_index).ATTRIBUTE9 is not NULL THEN
4350 l_to_delete := FALSE;
4351 ELSIF p_attributes(l_index).ATTRIBUTE10 is not NULL THEN
4352 l_to_delete := FALSE;
4353 ELSIF p_attributes(l_index).ATTRIBUTE11 is not NULL THEN
4354 l_to_delete := FALSE;
4355 ELSIF p_attributes(l_index).ATTRIBUTE12 is not NULL THEN
4356 l_to_delete := FALSE;
4357 ELSIF p_attributes(l_index).ATTRIBUTE13 is not NULL THEN
4358 l_to_delete := FALSE;
4359 ELSIF p_attributes(l_index).ATTRIBUTE14 is not NULL THEN
4360 l_to_delete := FALSE;
4361 ELSIF p_attributes(l_index).ATTRIBUTE15 is not NULL THEN
4362 l_to_delete := FALSE;
4363 ELSIF p_attributes(l_index).ATTRIBUTE16 is not NULL THEN
4364 l_to_delete := FALSE;
4365 ELSIF p_attributes(l_index).ATTRIBUTE17 is not NULL THEN
4366 l_to_delete := FALSE;
4367 ELSIF p_attributes(l_index).ATTRIBUTE18 is not NULL THEN
4368 l_to_delete := FALSE;
4369 ELSIF p_attributes(l_index).ATTRIBUTE19 is not NULL THEN
4370 l_to_delete := FALSE;
4371 ELSIF p_attributes(l_index).ATTRIBUTE20 is not NULL THEN
4372 l_to_delete := FALSE;
4373 ELSIF p_attributes(l_index).ATTRIBUTE21 is not NULL THEN
4374 l_to_delete := FALSE;
4375 ELSIF p_attributes(l_index).ATTRIBUTE22 is not NULL THEN
4376 l_to_delete := FALSE;
4377 ELSIF p_attributes(l_index).ATTRIBUTE23 is not NULL THEN
4378 l_to_delete := FALSE;
4379 ELSIF p_attributes(l_index).ATTRIBUTE24 is not NULL THEN
4380 l_to_delete := FALSE;
4381 ELSIF p_attributes(l_index).ATTRIBUTE25 is not NULL THEN
4382 l_to_delete := FALSE;
4383 ELSIF p_attributes(l_index).ATTRIBUTE26 is not NULL THEN
4384 l_to_delete := FALSE;
4385 ELSIF p_attributes(l_index).ATTRIBUTE27 is not NULL THEN
4386 l_to_delete := FALSE;
4387 ELSIF p_attributes(l_index).ATTRIBUTE28 is not NULL THEN
4388 l_to_delete := FALSE;
4389 ELSIF p_attributes(l_index).ATTRIBUTE29 is not NULL THEN
4390 l_to_delete := FALSE;
4391 ELSIF p_attributes(l_index).ATTRIBUTE30 is not NULL THEN
4392 l_to_delete := FALSE;
4393 END IF;
4394
4395 IF l_to_delete = FALSE AND p_attributes(l_index).ATTRIBUTE_CATEGORY is null THEN
4396
4397 l_to_delete := TRUE;
4398
4399 ELSIF l_to_delete AND p_attributes(l_index).BLD_BLK_INFO_TYPE like 'Dummy%'
4400 AND p_attributes(l_index).BLD_BLK_INFO_TYPE <> 'Dummy Paexpitdff Context' THEN
4401 l_to_delete := FALSE;
4402 END IF;
4403
4404 IF l_to_delete THEN
4405 l_index_to_delete := l_index;
4406 END IF;
4407
4408 l_index := p_attributes.next(l_index);
4409
4410 -- now we can delete it
4411 IF l_to_delete THEN
4412 p_attributes.delete(l_index_to_delete);
4413 END IF;
4414
4415 END LOOP;
4416
4417 END remove_empty_attribute;
4418 */
4419
4420 -- ---------------------------------------------------------------------
4421 -- |-------------------< dump_alias_val_att_to_match >---------------|
4422 -- ---------------------------------------------------------------------
4423 -- | Debug Procedure |
4424 -- ---------------------------------------------------------------------
4425 PROCEDURE dump_alias_val_att_to_match (p_alias_val_att_to_match IN OUT NOCOPY t_alias_val_att_to_match) IS
4426
4427 l_index number;
4428
4429 BEGIN
4430
4431 l_index := p_alias_val_att_to_match.first;
4432 g_debug := hr_utility.debug_enabled;
4433 LOOP
4434 EXIT WHEN
4435 (NOT p_alias_val_att_to_match.exists(l_index));
4436 /*
4437 if g_debug then
4438 hr_utility.trace('HXC_ALIAS_TRANSLATOR.dump_alias_attribute');
4439 hr_utility.trace('ATT_CAT:'||p_alias_val_att_to_match(l_index).attribute_category);
4440 hr_utility.trace('ATT1:'||p_alias_val_att_to_match(l_index).attribute1);
4441 hr_utility.trace('ATT2:'||p_alias_val_att_to_match(l_index).attribute2);
4442 hr_utility.trace('ATT3:'||p_alias_val_att_to_match(l_index).attribute3);
4443 hr_utility.trace('ATT4:'||p_alias_val_att_to_match(l_index).attribute4);
4444 hr_utility.trace('ATT5:'||p_alias_val_att_to_match(l_index).attribute5);
4445 hr_utility.trace('ATT6:'||p_alias_val_att_to_match(l_index).attribute6);
4446 hr_utility.trace('ATT7:'||p_alias_val_att_to_match(l_index).attribute7);
4447 hr_utility.trace('ATT8:'||p_alias_val_att_to_match(l_index).attribute8);
4448 hr_utility.trace('ATT9:'||p_alias_val_att_to_match(l_index).attribute9);
4449 hr_utility.trace('ATT10:'||p_alias_val_att_to_match(l_index).attribute10);
4450 hr_utility.trace('BLD_BLK_INFO_TYPE:'||p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE);
4451 hr_utility.trace('BLD_BLK_INFO_TYPE_ID:'||p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE_ID);
4452 hr_utility.trace('COMPONENT_TYPE:'||p_alias_val_att_to_match(l_index).COMPONENT_TYPE);
4453 hr_utility.trace('COMPONENT_NAME:'||p_alias_val_att_to_match(l_index).COMPONENT_NAME);
4454 hr_utility.trace('REFERENCE_OBJECT:'||p_alias_val_att_to_match(l_index).REFERENCE_OBJECT);
4455 end if;
4456 */
4457 if g_debug then
4458 hr_utility.trace('MAPP:'||p_alias_val_att_to_match(l_index).MAPPING_ATT_CAT);
4459 hr_utility.trace('SEGMENT:'||p_alias_val_att_to_match(l_index).SEGMENT);
4460 end if;
4461 /*
4462 dbms_output.put_line('HXC_ALIAS_TRANSLATOR.dump_alias_attribute');
4463 dbms_output.put_line('ATT_CAT:'||p_alias_val_att_to_match(l_index).attribute_category);
4464 dbms_output.put_line('ATT1:'||p_alias_val_att_to_match(l_index).attribute1);
4465 dbms_output.put_line('ATT2:'||p_alias_val_att_to_match(l_index).attribute2);
4466 dbms_output.put_line('ATT3:'||p_alias_val_att_to_match(l_index).attribute3);
4467 dbms_output.put_line('ATT4:'||p_alias_val_att_to_match(l_index).attribute4);
4468 dbms_output.put_line('ATT5:'||p_alias_val_att_to_match(l_index).attribute5);
4469 dbms_output.put_line('ATT6:'||p_alias_val_att_to_match(l_index).attribute6);
4470 dbms_output.put_line('ATT7:'||p_alias_val_att_to_match(l_index).attribute7);
4471 dbms_output.put_line('ATT8:'||p_alias_val_att_to_match(l_index).attribute8);
4472 dbms_output.put_line('ATT9:'||p_alias_val_att_to_match(l_index).attribute9);
4473 dbms_output.put_line('ATT10:'||p_alias_val_att_to_match(l_index).attribute10);
4474 dbms_output.put_line('BLD_BLK_INFO_TYPE:'||p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE);
4475 dbms_output.put_line('BLD_BLK_INFO_TYPE_ID:'||p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE_ID);
4476 dbms_output.put_line('COMPONENT_TYPE:'||p_alias_val_att_to_match(l_index).COMPONENT_TYPE);
4477 dbms_output.put_line('COMPONENT_NAME:'||p_alias_val_att_to_match(l_index).COMPONENT_NAME);
4478 dbms_output.put_line('REFERENCE_OBJECT:'||p_alias_val_att_to_match(l_index).REFERENCE_OBJECT);
4479 dbms_output.put_line('SEGMENT:'||p_alias_val_att_to_match(l_index).SEGMENT);
4480 */
4481 l_index := p_alias_val_att_to_match.next(l_index);
4482 END LOOP;
4483
4484
4485 END dump_alias_val_att_to_match;
4486
4487
4488 -- ---------------------------------------------------------------------
4489 -- |-------------------< dump_bb_attribute_info >---------------|
4490 -- ---------------------------------------------------------------------
4491 -- | Debug Procedure |
4492 -- ---------------------------------------------------------------------
4493 PROCEDURE dump_bb_attribute_info
4494 (p_attributes in out NOCOPY HXC_ATTRIBUTE_TABLE_TYPE) IS--hxc_self_service_time_deposit.building_block_attribute_info) IS
4495
4496 l_index NUMBER;
4497
4498 BEGIN
4499
4500 l_index := p_attributes.first;
4501 g_debug :=hr_utility.debug_enabled;
4502 LOOP
4503 EXIT WHEN
4504 (NOT p_attributes.exists(l_index));
4505
4506 IF p_attributes(l_index).ATTRIBUTE_CATEGORY <> 'SECURITY' THEN
4507 if g_debug then
4508 hr_utility.trace('HXC_ALIAS_TRANSLATOR.dump_bb_attribute_info');
4509 hr_utility.trace('time_attribute_id:'||p_attributes(l_index).TIME_ATTRIBUTE_ID);
4510 hr_utility.trace('index:'||l_index);
4511 hr_utility.trace('BBID:'||p_attributes(l_index).BUILDING_BLOCK_ID);
4512 hr_utility.trace('BBITYPE:'||p_attributes(l_index).BLD_BLK_INFO_TYPE);
4513 hr_utility.trace('BBITYPE_ID:'||p_attributes(l_index).BLD_BLK_INFO_TYPE_ID);
4514 hr_utility.trace('ATT_CAT:'||p_attributes(l_index).ATTRIBUTE_CATEGORY);
4515 hr_utility.trace('CHANGED :'||p_attributes(l_index).CHANGED);
4516 hr_utility.trace('NEW :'||p_attributes(l_index).NEW);
4517 hr_utility.trace(' ATT1:'||p_attributes(l_index).ATTRIBUTE1);
4518 hr_utility.trace(' ATT2:'||p_attributes(l_index).ATTRIBUTE2);
4519 hr_utility.trace(' ATT3:'||p_attributes(l_index).ATTRIBUTE3);
4520 hr_utility.trace(' ATT4:'||p_attributes(l_index).ATTRIBUTE4);
4521 hr_utility.trace(' ATT5:'||p_attributes(l_index).ATTRIBUTE5);
4522 hr_utility.trace(' ATT6:'||p_attributes(l_index).ATTRIBUTE6);
4523 hr_utility.trace(' ATT7:'||p_attributes(l_index).ATTRIBUTE7);
4524 hr_utility.trace(' ATT8:'||p_attributes(l_index).ATTRIBUTE8);
4525 hr_utility.trace(' ATT9:'||p_attributes(l_index).ATTRIBUTE9);
4526 hr_utility.trace(' ATT10:'||p_attributes(l_index).ATTRIBUTE10);
4527 hr_utility.trace(' ATT11:'||p_attributes(l_index).ATTRIBUTE11);
4528 hr_utility.trace(' ATT12:'||p_attributes(l_index).ATTRIBUTE12);
4529 hr_utility.trace(' ATT13:'||p_attributes(l_index).ATTRIBUTE13);
4530 hr_utility.trace(' ATT14:'||p_attributes(l_index).ATTRIBUTE14);
4531 hr_utility.trace(' ATT15:'||p_attributes(l_index).ATTRIBUTE15);
4532 hr_utility.trace(' ATT16:'||p_attributes(l_index).ATTRIBUTE16);
4533 hr_utility.trace(' ATT17:'||p_attributes(l_index).ATTRIBUTE17);
4534 hr_utility.trace(' ATT18:'||p_attributes(l_index).ATTRIBUTE18);
4535 hr_utility.trace(' ATT19:'||p_attributes(l_index).ATTRIBUTE19);
4536 hr_utility.trace(' ATT20:'||p_attributes(l_index).ATTRIBUTE20);
4537 hr_utility.trace(' ATT21:'||p_attributes(l_index).ATTRIBUTE21);
4538 hr_utility.trace(' ATT22:'||p_attributes(l_index).ATTRIBUTE22);
4539 hr_utility.trace(' ATT23:'||p_attributes(l_index).ATTRIBUTE23);
4540 hr_utility.trace(' ATT24:'||p_attributes(l_index).ATTRIBUTE24);
4541 hr_utility.trace(' ATT25:'||p_attributes(l_index).ATTRIBUTE25);
4542 hr_utility.trace(' ATT26:'||p_attributes(l_index).ATTRIBUTE26);
4543 hr_utility.trace(' ATT27:'||p_attributes(l_index).ATTRIBUTE27);
4544 hr_utility.trace(' ATT28:'||p_attributes(l_index).ATTRIBUTE28);
4545 hr_utility.trace(' ATT29:'||p_attributes(l_index).ATTRIBUTE29);
4546 hr_utility.trace(' ATT30:'||p_attributes(l_index).ATTRIBUTE30);
4547 end if;
4548 END IF;
4549
4550 l_index := p_attributes.next(l_index);
4551
4552 END LOOP;
4553
4554 END dump_bb_attribute_info;
4555
4556
4557 -- ---------------------------------------------------------------------
4558 -- |-------------------< dump_alias_def_item >---------------|
4559 -- ---------------------------------------------------------------------
4560 -- | Debug Procedure |
4561 -- ---------------------------------------------------------------------
4562 PROCEDURE dump_alias_def_item (p_alias_def_item IN OUT NOCOPY t_alias_def_item) IS
4563
4564 l_index number;
4565
4566 BEGIN
4567 --null;
4568
4569 l_index := p_alias_def_item.first;
4570 g_debug := hr_utility.debug_enabled;
4571
4572 LOOP
4573 EXIT WHEN
4574 (NOT p_alias_def_item.exists(l_index));
4575 /*
4576 if g_debug then
4577 hr_utility.trace('HXC_ALIAS_TRANSLATOR.dump_alias_attribute');
4578 hr_utility.trace('ATT_CAT:'||p_alias_val_att_to_match(l_index).attribute_category);
4579 hr_utility.trace('ATT1:'||p_alias_val_att_to_match(l_index).attribute1);
4580 hr_utility.trace('ATT2:'||p_alias_val_att_to_match(l_index).attribute2);
4581 hr_utility.trace('ATT3:'||p_alias_val_att_to_match(l_index).attribute3);
4582 hr_utility.trace('ATT4:'||p_alias_val_att_to_match(l_index).attribute4);
4583 hr_utility.trace('ATT5:'||p_alias_val_att_to_match(l_index).attribute5);
4584 hr_utility.trace('ATT6:'||p_alias_val_att_to_match(l_index).attribute6);
4585 hr_utility.trace('ATT7:'||p_alias_val_att_to_match(l_index).attribute7);
4586 hr_utility.trace('ATT8:'||p_alias_val_att_to_match(l_index).attribute8);
4587 hr_utility.trace('ATT9:'||p_alias_val_att_to_match(l_index).attribute9);
4588 hr_utility.trace('ATT10:'||p_alias_val_att_to_match(l_index).attribute10);
4589 hr_utility.trace('BLD_BLK_INFO_TYPE:'||p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE);
4590 hr_utility.trace('BLD_BLK_INFO_TYPE_ID:'||p_alias_val_att_to_match(l_index).BLD_BLK_INFO_TYPE_ID);
4591 hr_utility.trace('COMPONENT_TYPE:'||p_alias_val_att_to_match(l_index).COMPONENT_TYPE);
4592 hr_utility.trace('COMPONENT_NAME:'||p_alias_val_att_to_match(l_index).COMPONENT_NAME);
4593 hr_utility.trace('REFERENCE_OBJECT:'||p_alias_val_att_to_match(l_index).REFERENCE_OBJECT);
4594 hr_utility.trace('SEGMENT:'||p_alias_val_att_to_match(l_index).SEGMENT);
4595 end if;
4596 */
4597 if g_debug then
4598 hr_utility.trace('HXC_ALIAS_TRANSLATOR.dump_alias_def_item');
4599 hr_utility.trace('ATT_DEF_ID: '||p_alias_def_item(l_index).ALIAS_DEFINITION_ID);
4600 hr_utility.trace('ATT_CATEGORY: '||p_alias_def_item(l_index).ITEM_ATTRIBUTE_CATEGORY);
4601 hr_utility.trace('LAYOUT_ID: '||p_alias_def_item(l_index).LAYOUT_ID);
4602 end if;
4603
4604 l_index := p_alias_def_item.next(l_index);
4605 END LOOP;
4606
4607
4608 END dump_alias_def_item;
4609
4610 ----------------------------------------------------------------------------------
4611 --- TEMPORARY FUNCTION
4612 ----------------------------------------------------------------------------------
4613
4614 --
4615 -- USE IN SS....NEED TO ASK ANDREW
4616 --
4617 PROCEDURE alias_def_comma_list(p_alias_type IN VARCHAR2
4618 ,p_start_time IN VARCHAR2
4619 ,p_stop_time IN VARCHAR2
4620 ,p_resource_id IN NUMBER
4621 ,p_alias_def_comma OUT NOCOPY VARCHAR2)
4622 IS
4623
4624
4625 l_alias_def_comma VARCHAR2(80) := null;
4626 l_alias_definition t_alias_def_item;
4627 l_index NUMBER;
4628
4629 BEGIN
4630 IF(1>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4631 fnd_log.string(1
4632 ,'HXC_ALIAS_UTILITY.alias_def_comma_list'
4633 ,'p_alias_type:'||p_alias_type||
4634 ' p_start_time:'||p_start_time||
4635 ' p_stop_time :'||p_stop_time||
4636 ' p_resource_id:'||p_resource_id);
4637 END IF;
4638
4639
4640 l_alias_definition:=
4641 get_list_alias_id
4642 (p_alias_type => p_alias_type
4643 ,p_start_time => p_start_time
4644 ,p_stop_time => p_stop_time
4645 ,p_resource_id => to_number(p_resource_id));
4646
4647
4648 l_index := l_alias_definition.FIRST;
4649 LOOP
4650 EXIT WHEN
4651 (NOT l_alias_definition.exists(l_index));
4652 IF (l_alias_definition(l_index).ALIAS_DEFINITION_ID is not null) THEN
4653
4654 IF (l_index = l_alias_definition.FIRST) THEN
4655 l_alias_def_comma := ','||l_alias_definition(l_index).ALIAS_DEFINITION_ID;
4656
4657 IF(1>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4658 fnd_log.string(1
4659 ,'HXC_ALIAS_UTILITY.alias_def_comma_list'
4660 ,'l_alias_def_comma1:'||l_alias_def_comma);
4661 END IF;
4662 ELSE
4663 l_alias_def_comma := l_alias_def_comma||','||l_alias_definition(l_index).ALIAS_DEFINITION_ID;
4664 IF(1>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4665 fnd_log.string(1
4666 ,'HXC_ALIAS_UTILITY.alias_def_comma_list'
4667 ,'l_alias_def_comma2:'||l_alias_def_comma);
4668 END IF;
4669 END IF;
4670 END IF;
4671
4672 l_index := l_alias_definition.next(l_index);
4673
4674 END LOOP;
4675
4676 l_alias_def_comma := l_alias_def_comma||',';
4677 IF(1>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4678 fnd_log.string(1
4679 ,'HXC_ALIAS_UTILITY.alias_def_comma_list'
4680 ,'l_alias_def_comma3:'||l_alias_def_comma);
4681 END IF;
4682
4683 p_alias_def_comma := l_alias_def_comma;
4684
4685
4686 /*
4687 l_alias_def_comma VARCHAR2(80) := null;
4688 l_alternate_name_index NUMBER;
4689
4690 l_pref_index NUMBER;
4691 l_pref_table hxc_preference_evaluation.t_pref_table;
4692
4693 l_alias_type hxc_alias_types.alias_type%TYPE;
4694 l_reference_object hxc_alias_types.reference_object%TYPE;
4695 l_prompt hxc_alias_definitions_tl.prompt%TYPE;
4696
4697
4698 BEGIN
4699
4700 fnd_log.string(1
4701 ,'HXC_ALIAS_UTILITY.alias_def_comma_list'
4702 ,'p_alias_type:'||p_alias_type||
4703 ' p_start_time:'||p_start_time||
4704 ' p_stop_time :'||p_stop_time||
4705 ' p_resource_id:'||p_resource_id);
4706
4707
4708 -- we are calling the preference now for the resource
4709 hxc_preference_evaluation.resource_preferences(
4710 p_resource_id => p_resource_id ,
4711 p_start_evaluation_date => FND_DATE.CANONICAL_TO_DATE(p_start_time),
4712 p_end_evaluation_date => FND_DATE.CANONICAL_TO_DATE(p_stop_time),
4713 p_pref_table => l_pref_table);
4714
4715 -- find the index of the alternate name preference and
4716 -- we are taking care in the case of a null layout
4717 l_pref_index :=l_pref_table.FIRST;
4718
4719 LOOP
4720 EXIT WHEN
4721 (NOT l_pref_table.exists(l_pref_index));
4722
4723 IF(l_pref_table(l_pref_index).preference_code = 'TC_W_TCRD_ALIASES') THEN
4724 l_alternate_name_index := l_pref_index;
4725 exit;
4726 END IF;
4727
4728 l_pref_index := l_pref_table.next(l_pref_index);
4729
4730 END LOOP;
4731
4732
4733 IF l_pref_table(l_alternate_name_index).ATTRIBUTE1 is not null THEN
4734
4735 get_alias_definition_info
4736 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE1),
4737 l_alias_type,
4738 l_reference_object,
4739 l_prompt);
4740
4741 IF p_alias_type = l_reference_object THEN
4742 -- not sure we need put the ',' anymore
4743 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE1;
4744 RETURN;
4745 END IF;
4746
4747 END IF;
4748
4749 IF l_pref_table(l_alternate_name_index).ATTRIBUTE2 is not null THEN
4750
4751 get_alias_definition_info
4752 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE2),
4753 l_alias_type,
4754 l_reference_object,
4755 l_prompt);
4756
4757 IF p_alias_type = l_reference_object THEN
4758 -- not sure we need put the ',' anymore
4759 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE2;
4760 RETURN;
4761 END IF;
4762
4763 END IF;
4764
4765 IF l_pref_table(l_alternate_name_index).ATTRIBUTE3 is not null THEN
4766
4767 get_alias_definition_info
4768 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE3),
4769 l_alias_type,
4770 l_reference_object,
4771 l_prompt);
4772
4773 IF p_alias_type = l_reference_object THEN
4774 -- not sure we need put the ',' anymore
4775 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE3;
4776 RETURN;
4777 END IF;
4778
4779 END IF;
4780
4781 IF l_pref_table(l_alternate_name_index).ATTRIBUTE4 is not null THEN
4782
4783 get_alias_definition_info
4784 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE4),
4785 l_alias_type,
4786 l_reference_object,
4787 l_prompt);
4788
4789 IF p_alias_type = l_reference_object THEN
4790 -- not sure we need put the ',' anymore
4791 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE4;
4792 RETURN;
4793 END IF;
4794
4795 END IF;
4796
4797 IF l_pref_table(l_alternate_name_index).ATTRIBUTE5 is not null THEN
4798
4799 get_alias_definition_info
4800 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE5),
4801 l_alias_type,
4802 l_reference_object,
4803 l_prompt);
4804
4805 IF p_alias_type = l_reference_object THEN
4806 -- not sure we need put the ',' anymore
4807 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE5;
4808 RETURN;
4809 END IF;
4810
4811 END IF;
4812
4813 IF l_pref_table(l_alternate_name_index).ATTRIBUTE6 is not null THEN
4814
4815 get_alias_definition_info
4816 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE6),
4817 l_alias_type,
4818 l_reference_object,
4819 l_prompt);
4820
4821 IF p_alias_type = l_reference_object THEN
4822 -- not sure we need put the ',' anymore
4823 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE6;
4824 RETURN;
4825 END IF;
4826
4827 END IF;
4828
4829 IF l_pref_table(l_alternate_name_index).ATTRIBUTE7 is not null THEN
4830
4831 get_alias_definition_info
4832 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE7),
4833 l_alias_type,
4834 l_reference_object,
4835 l_prompt);
4836
4837 IF p_alias_type = l_reference_object THEN
4838 -- not sure we need put the ',' anymore
4839 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE7;
4840 RETURN;
4841 END IF;
4842
4843 END IF;
4844
4845 IF l_pref_table(l_alternate_name_index).ATTRIBUTE8 is not null THEN
4846
4847 get_alias_definition_info
4848 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE8),
4849 l_alias_type,
4850 l_reference_object,
4851 l_prompt);
4852
4853 IF p_alias_type = l_reference_object THEN
4854 -- not sure we need put the ',' anymore
4855 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE8;
4856 RETURN;
4857 END IF;
4858
4859 END IF;
4860
4861 IF l_pref_table(l_alternate_name_index).ATTRIBUTE9 is not null THEN
4862
4863 get_alias_definition_info
4864 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE9),
4865 l_alias_type,
4866 l_reference_object,
4867 l_prompt);
4868
4869 IF p_alias_type = l_reference_object THEN
4870 -- not sure we need put the ',' anymore
4871 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE9;
4872 RETURN;
4873 END IF;
4874
4875 END IF;
4876
4877 IF l_pref_table(l_alternate_name_index).ATTRIBUTE10 is not null THEN
4878
4879 get_alias_definition_info
4880 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE10),
4881 l_alias_type,
4882 l_reference_object,
4883 l_prompt);
4884
4885 IF p_alias_type = l_reference_object THEN
4886 -- not sure we need put the ',' anymore
4887 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE10;
4888 RETURN;
4889 END IF;
4890
4891 END IF;
4892
4893 IF l_pref_table(l_alternate_name_index).ATTRIBUTE11 is not null THEN
4894
4895 get_alias_definition_info
4896 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE11),
4897 l_alias_type,
4898 l_reference_object,
4899 l_prompt);
4900
4901 IF p_alias_type = l_reference_object THEN
4902 -- not sure we need put the ',' anymore
4903 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE11;
4904 RETURN;
4905 END IF;
4906
4907 END IF;
4908
4909 IF l_pref_table(l_alternate_name_index).ATTRIBUTE12 is not null THEN
4910
4911 get_alias_definition_info
4912 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE12),
4913 l_alias_type,
4914 l_reference_object,
4915 l_prompt);
4916
4917 IF p_alias_type = l_reference_object THEN
4918 -- not sure we need put the ',' anymore
4919 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE12;
4920 RETURN;
4921 END IF;
4922
4923 END IF;
4924
4925 IF l_pref_table(l_alternate_name_index).ATTRIBUTE13 is not null THEN
4926
4927 get_alias_definition_info
4928 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE13),
4929 l_alias_type,
4930 l_reference_object,
4931 l_prompt);
4932
4933 IF p_alias_type = l_reference_object THEN
4934 -- not sure we need put the ',' anymore
4935 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE13;
4936 RETURN;
4937 END IF;
4938
4939 END IF;
4940
4941 IF l_pref_table(l_alternate_name_index).ATTRIBUTE14 is not null THEN
4942
4943 get_alias_definition_info
4944 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE14),
4945 l_alias_type,
4946 l_reference_object,
4947 l_prompt);
4948
4949 IF p_alias_type = l_reference_object THEN
4950 -- not sure we need put the ',' anymore
4951 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE14;
4952 RETURN;
4953 END IF;
4954
4955 END IF;
4956
4957 IF l_pref_table(l_alternate_name_index).ATTRIBUTE15 is not null THEN
4958
4959 get_alias_definition_info
4960 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE15),
4961 l_alias_type,
4962 l_reference_object,
4963 l_prompt);
4964
4965 IF p_alias_type = l_reference_object THEN
4966 -- not sure we need put the ',' anymore
4967 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE15;
4968 RETURN;
4969 END IF;
4970
4971 END IF;
4972
4973 IF l_pref_table(l_alternate_name_index).ATTRIBUTE16 is not null THEN
4974
4975 get_alias_definition_info
4976 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE16),
4977 l_alias_type,
4978 l_reference_object,
4979 l_prompt);
4980
4981 IF p_alias_type = l_reference_object THEN
4982 -- not sure we need put the ',' anymore
4983 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE16;
4984 RETURN;
4985 END IF;
4986
4987 END IF;
4988
4989 IF l_pref_table(l_alternate_name_index).ATTRIBUTE17 is not null THEN
4990
4991 get_alias_definition_info
4992 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE17),
4993 l_alias_type,
4994 l_reference_object,
4995 l_prompt);
4996
4997 IF p_alias_type = l_reference_object THEN
4998 -- not sure we need put the ',' anymore
4999 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE17;
5000 RETURN;
5001 END IF;
5002
5003 END IF;
5004
5005 IF l_pref_table(l_alternate_name_index).ATTRIBUTE18 is not null THEN
5006
5007 get_alias_definition_info
5008 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE18),
5009 l_alias_type,
5010 l_reference_object,
5011 l_prompt);
5012
5013 IF p_alias_type = l_reference_object THEN
5014 -- not sure we need put the ',' anymore
5015 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE18;
5016 RETURN;
5017 END IF;
5018
5019 END IF;
5020
5021 IF l_pref_table(l_alternate_name_index).ATTRIBUTE19 is not null THEN
5022
5023 get_alias_definition_info
5024 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE19),
5025 l_alias_type,
5026 l_reference_object,
5027 l_prompt);
5028
5029 IF p_alias_type = l_reference_object THEN
5030 -- not sure we need put the ',' anymore
5031 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE19;
5032 RETURN;
5033 END IF;
5034
5035 END IF;
5036 IF l_pref_table(l_alternate_name_index).ATTRIBUTE20 is not null THEN
5037
5038 get_alias_definition_info
5039 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE20),
5040 l_alias_type,
5041 l_reference_object,
5042 l_prompt);
5043
5044 IF p_alias_type = l_reference_object THEN
5045 -- not sure we need put the ',' anymore
5046 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE20;
5047 RETURN;
5048 END IF;
5049
5050 END IF;
5051
5052 IF l_pref_table(l_alternate_name_index).ATTRIBUTE21 is not null THEN
5053
5054 get_alias_definition_info
5055 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE21),
5056 l_alias_type,
5057 l_reference_object,
5058 l_prompt);
5059
5060 IF p_alias_type = l_reference_object THEN
5061 -- not sure we need put the ',' anymore
5062 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE21;
5063 RETURN;
5064 END IF;
5065
5066 END IF;
5067
5068
5069 IF l_pref_table(l_alternate_name_index).ATTRIBUTE22 is not null THEN
5070
5071 get_alias_definition_info
5072 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE22),
5073 l_alias_type,
5074 l_reference_object,
5075 l_prompt);
5076
5077 IF p_alias_type = l_reference_object THEN
5078 -- not sure we need put the ',' anymore
5079 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE22;
5080 RETURN;
5081 END IF;
5082
5083 END IF;
5084
5085 IF l_pref_table(l_alternate_name_index).ATTRIBUTE23 is not null THEN
5086
5087 get_alias_definition_info
5088 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE23),
5089 l_alias_type,
5090 l_reference_object,
5091 l_prompt);
5092
5093 IF p_alias_type = l_reference_object THEN
5094 -- not sure we need put the ',' anymore
5095 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE23;
5096 RETURN;
5097 END IF;
5098
5099 END IF;
5100
5101 IF l_pref_table(l_alternate_name_index).ATTRIBUTE24 is not null THEN
5102
5103 get_alias_definition_info
5104 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE24),
5105 l_alias_type,
5106 l_reference_object,
5107 l_prompt);
5108
5109 IF p_alias_type = l_reference_object THEN
5110 -- not sure we need put the ',' anymore
5111 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE24;
5112 RETURN;
5113 END IF;
5114
5115 END IF;
5116
5117 IF l_pref_table(l_alternate_name_index).ATTRIBUTE25 is not null THEN
5118
5119 get_alias_definition_info
5120 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE25),
5121 l_alias_type,
5122 l_reference_object,
5123 l_prompt);
5124
5125 IF p_alias_type = l_reference_object THEN
5126 -- not sure we need put the ',' anymore
5127 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE25;
5128 RETURN;
5129 END IF;
5130
5131 END IF;
5132
5133 IF l_pref_table(l_alternate_name_index).ATTRIBUTE26 is not null THEN
5134
5135 get_alias_definition_info
5136 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE26),
5137 l_alias_type,
5138 l_reference_object,
5139 l_prompt);
5140
5141 IF p_alias_type = l_reference_object THEN
5142 -- not sure we need put the ',' anymore
5143 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE26;
5144 RETURN;
5145 END IF;
5146
5147 END IF;
5148
5149 IF l_pref_table(l_alternate_name_index).ATTRIBUTE27 is not null THEN
5150
5151 get_alias_definition_info
5152 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE27),
5153 l_alias_type,
5154 l_reference_object,
5155 l_prompt);
5156
5157 IF p_alias_type = l_reference_object THEN
5158 -- not sure we need put the ',' anymore
5159 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE27;
5160 RETURN;
5161 END IF;
5162
5163 END IF;
5164
5165 IF l_pref_table(l_alternate_name_index).ATTRIBUTE28 is not null THEN
5166
5167 get_alias_definition_info
5168 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE28),
5169 l_alias_type,
5170 l_reference_object,
5171 l_prompt);
5172
5173 IF p_alias_type = l_reference_object THEN
5174 -- not sure we need put the ',' anymore
5175 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE28;
5176 RETURN;
5177 END IF;
5178
5179 END IF;
5180
5181 IF l_pref_table(l_alternate_name_index).ATTRIBUTE29 is not null THEN
5182
5183 get_alias_definition_info
5184 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE29),
5185 l_alias_type,
5186 l_reference_object,
5187 l_prompt);
5188
5189 IF p_alias_type = l_reference_object THEN
5190 -- not sure we need put the ',' anymore
5191 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE29;
5192 RETURN;
5193 END IF;
5194
5195 END IF;
5196
5197 IF l_pref_table(l_alternate_name_index).ATTRIBUTE30 is not null THEN
5198
5199 get_alias_definition_info
5200 (to_number(l_pref_table(l_alternate_name_index).ATTRIBUTE30),
5201 l_alias_type,
5202 l_reference_object,
5203 l_prompt);
5204
5205 IF p_alias_type = l_reference_object THEN
5206 -- not sure we need put the ',' anymore
5207 p_alias_def_comma := ','||l_pref_table(l_alternate_name_index).ATTRIBUTE30;
5208 RETURN;
5209 END IF;
5210
5211 END IF;
5212
5213 */
5214 END alias_def_comma_list;
5215
5216 --
5217 -- This function is still used in the deposit wrapper utilities
5218 -- NEED TO SPEAK WITH ANDY.
5219 --
5220 FUNCTION get_list_alias_id(p_alias_type IN VARCHAR2
5221 ,p_start_time IN VARCHAR2
5222 ,p_stop_time IN VARCHAR2
5223 ,p_resource_id IN NUMBER) RETURN t_alias_def_item IS
5224
5225
5226 l_pref_table hxc_preference_evaluation.t_pref_table;
5227 l_alias_definition t_alias_def_item;
5228 l_index NUMBER;
5229 l_pref_value t_alias_def_item;
5230 l_index_pref_val NUMBER;
5231 l_alias_index NUMBER := 1;
5232 l_alias_type VARCHAR2(80);
5233 l_found BOOLEAN := FALSE;
5234
5235 BEGIN
5236 /*
5237 fnd_log.string(1
5238 ,'HXC_ALIAS_UTILITY.get_list_alias_id'
5239 ,'arrived:');
5240
5241 fnd_log.string(1
5242 ,'HXC_ALIAS_UTILITY.get_list_alias_id'
5243 ,'p_alias_type:'||p_alias_type||
5244 ' p_start_time:'||p_start_time||
5245 ' p_stop_time :'||p_stop_time||
5246 ' p_resource_id:'||p_resource_id);
5247 */
5248 l_pref_table.DELETE;
5249
5250 hxc_preference_evaluation.resource_preferences(
5251 p_resource_id => p_resource_id ,
5252 p_start_evaluation_date => FND_DATE.CANONICAL_TO_DATE(p_start_time),
5253 p_end_evaluation_date => FND_DATE.CANONICAL_TO_DATE(p_stop_time),
5254 p_pref_table => l_pref_table);
5255
5256 l_index:=l_pref_table.FIRST;
5257 /*
5258 fnd_log.string(1
5259 ,'HXC_ALIAS_UTILITY.get_list_alias_id'
5260 ,'passed the pref :');
5261 */
5262
5263 LOOP
5264 EXIT WHEN
5265 (NOT l_pref_table.exists(l_index));
5266
5267 IF(l_pref_table(l_index).preference_code = 'TC_W_TCRD_ALIASES') then
5268
5269 l_index_pref_val := 1;
5270 l_pref_value.delete;
5271
5272 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute1;
5273 l_index_pref_val := l_index_pref_val + 1;
5274 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute2;
5275 l_index_pref_val := l_index_pref_val + 1;
5276 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute3;
5277 l_index_pref_val := l_index_pref_val + 1;
5278 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute4;
5279 l_index_pref_val := l_index_pref_val + 1;
5280 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute5;
5281 l_index_pref_val := l_index_pref_val + 1;
5282 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute6;
5283 l_index_pref_val := l_index_pref_val + 1;
5284 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute7;
5285 l_index_pref_val := l_index_pref_val + 1;
5286 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute8;
5287 l_index_pref_val := l_index_pref_val + 1;
5288 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute9;
5289 l_index_pref_val := l_index_pref_val + 1;
5290 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute10;
5291 l_index_pref_val := l_index_pref_val + 1;
5292 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute11;
5293 l_index_pref_val := l_index_pref_val + 1;
5294 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute12;
5295 l_index_pref_val := l_index_pref_val + 1;
5296 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute13;
5297 l_index_pref_val := l_index_pref_val + 1;
5298 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute14;
5299 l_index_pref_val := l_index_pref_val + 1;
5300 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute15;
5301 l_index_pref_val := l_index_pref_val + 1;
5302 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute16;
5303 l_index_pref_val := l_index_pref_val + 1;
5304 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute17;
5305 l_index_pref_val := l_index_pref_val + 1;
5306 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute18;
5307 l_index_pref_val := l_index_pref_val + 1;
5308 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute19;
5309 l_index_pref_val := l_index_pref_val + 1;
5310 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute20;
5311 l_index_pref_val := l_index_pref_val + 1;
5312 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute21;
5313 l_index_pref_val := l_index_pref_val + 1;
5314 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute22;
5315 l_index_pref_val := l_index_pref_val + 1;
5316 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute23;
5317 l_index_pref_val := l_index_pref_val + 1;
5318 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute24;
5319 l_index_pref_val := l_index_pref_val + 1;
5320 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute25;
5321 l_index_pref_val := l_index_pref_val + 1;
5322 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute26;
5323 l_index_pref_val := l_index_pref_val + 1;
5324 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute27;
5325 l_index_pref_val := l_index_pref_val + 1;
5326 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute28;
5327 l_index_pref_val := l_index_pref_val + 1;
5328 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute29;
5329 l_index_pref_val := l_index_pref_val + 1;
5330 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID := l_pref_table(l_index).attribute30;
5331 l_index_pref_val := l_index_pref_val + 1;
5332
5333 --fnd_log.string(1
5334 -- ,'HXC_ALIAS_UTILITY.get_list_alias_id'
5335 -- ,'l_index_pref_val1:'||l_index_pref_val);
5336
5337 l_index_pref_val := l_pref_value.first;
5338
5339 --fnd_log.string(1
5340 -- ,'HXC_ALIAS_UTILITY.get_list_alias_id'
5341 -- ,'l_index_pref_val2:'||l_index_pref_val);
5342
5343
5344 LOOP
5345 EXIT WHEN
5346 (NOT l_pref_value.exists(l_index_pref_val));
5347
5348 IF (l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID is not null) THEN
5349
5350 --fnd_log.string(1
5351 --,'HXC_ALIAS_UTILITY.get_list_alias_id'
5352 --,'l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID :'||
5353 -- l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID);
5354
5355
5356 select reference_object
5357 into l_alias_type
5358 from hxc_alias_definitions had,hxc_alias_types hat
5359 where hat.alias_type_id =had.alias_type_id
5360 and alias_definition_id =
5361 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID;
5362
5363 --fnd_log.string(1
5364 --,'HXC_ALIAS_UTILITY.get_list_alias_id'
5365 --,'l_alias_type:'||l_alias_type||
5366 --' p_alias_type:'||p_alias_type);
5367
5368
5369 IF l_alias_type = p_alias_type THEN
5370 -- add into the list
5371 l_alias_definition(l_alias_index).ALIAS_DEFINITION_ID :=
5372 l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID;
5373 l_alias_definition(l_alias_index).PREF_START_DATE :=
5374 l_pref_table(l_index).START_DATE;
5375 l_alias_definition(l_alias_index).PREF_END_DATE :=
5376 l_pref_table(l_index).END_DATE;
5377 l_alias_index := l_alias_index + 1;
5378 l_found := true;
5379 --fnd_log.string(1
5380 --,'HXC_ALIAS_UTILITY.get_list_alias_id'
5381 --,'l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID'||
5382 -- l_pref_value(l_index_pref_val).ALIAS_DEFINITION_ID||
5383 --' l_alias_definition(l_alias_index).START_DATE'||
5384 -- l_pref_table(l_index).START_DATE);
5385
5386
5387 END IF;
5388 END IF;
5389
5390 l_index_pref_val := l_pref_value.next(l_index_pref_val);
5391
5392 END LOOP;
5393
5394 END IF;
5395
5396 l_index := l_pref_table.next(l_index);
5397
5398 END LOOP;
5399
5400 --IF not(l_found) THEN
5401 -- fnd_message.set_name('HXC', 'HXC_ALIAS_DEF_NOT_FOUND');
5402 -- fnd_msg_pub.add;
5403 --END IF;
5404
5405 return l_alias_definition;
5406
5407 END get_list_alias_id;
5408
5409 -- USE IN TK
5410 -- NEED TO SPEAK WITH NITIN
5411 -- ----------------------------------------------------------------------------
5412 -- |---------------------------< get_bld_blk_type_id >--------------------|
5413 -- ----------------------------------------------------------------------------
5414 FUNCTION get_bld_blk_type_id(p_type IN varchar2) RETURN NUMBER IS
5415
5416 CURSOR csr_bld_blk_id(p_type IN varchar2) IS
5417 SELECT bld_blk_info_type_id
5418 FROM hxc_bld_blk_info_types
5419 WHERE bld_blk_info_type = p_type;
5420
5421 l_type_id NUMBER;
5422
5423 BEGIN
5424
5425 OPEN csr_bld_blk_id(p_type);
5426 FETCH csr_bld_blk_id INTO l_type_id;
5427
5428 IF csr_bld_blk_id%NOTFOUND THEN
5429 CLOSE csr_bld_blk_id;
5430 FND_MESSAGE.SET_NAME('HXC','HXC_NO_BLD_BLK_TYPE_ID');
5431 fnd_msg_pub.add;
5432
5433 END IF;
5434
5435 CLOSE csr_bld_blk_id;
5436
5437 RETURN l_type_id;
5438
5439 EXCEPTION
5440 WHEN OTHERS THEN
5441 fnd_message.set_name('HXC', 'HXC_ALIAS_EXCEPTION');
5442 fnd_msg_pub.add;
5443
5444
5445
5446 END get_bld_blk_type_id;
5447
5448 ---
5449 /*
5450 Function convert_attribute_to_type
5451 (p_attributes in HXC_SELF_SERVICE_TIME_DEPOSIT.building_block_attribute_info)
5452 return HXC_ATTRIBUTE_TABLE_TYPE is
5453
5454 l_attributes HXC_ATTRIBUTE_TABLE_TYPE;--hxc_self_service_time_deposit.building_block_attribute_info;
5455 l_index NUMBER;
5456
5457 Begin
5458
5459 -- Initialize the collection
5460
5461 l_attributes := HXC_ATTRIBUTE_TABLE_TYPE();
5462
5463 l_index := p_attributes.first;
5464
5465 LOOP
5466
5467 EXIT WHEN NOT p_attributes.exists(l_index);
5468
5469 l_attributes.extend;
5470
5471 l_attributes(l_attributes.last) :=
5472 HXC_ATTRIBUTE_TYPE
5473 (p_attributes(l_index).TIME_ATTRIBUTE_ID
5474 ,p_attributes(l_index).BUILDING_BLOCK_ID
5475 ,p_attributes(l_index).ATTRIBUTE_CATEGORY
5476 ,p_attributes(l_index).ATTRIBUTE1
5477 ,p_attributes(l_index).ATTRIBUTE2
5478 ,p_attributes(l_index).ATTRIBUTE3
5479 ,p_attributes(l_index).ATTRIBUTE4
5480 ,p_attributes(l_index).ATTRIBUTE5
5481 ,p_attributes(l_index).ATTRIBUTE6
5482 ,p_attributes(l_index).ATTRIBUTE7
5483 ,p_attributes(l_index).ATTRIBUTE8
5484 ,p_attributes(l_index).ATTRIBUTE9
5485 ,p_attributes(l_index).ATTRIBUTE10
5486 ,p_attributes(l_index).ATTRIBUTE11
5487 ,p_attributes(l_index).ATTRIBUTE12
5488 ,p_attributes(l_index).ATTRIBUTE13
5489 ,p_attributes(l_index).ATTRIBUTE14
5490 ,p_attributes(l_index).ATTRIBUTE15
5491 ,p_attributes(l_index).ATTRIBUTE16
5492 ,p_attributes(l_index).ATTRIBUTE17
5493 ,p_attributes(l_index).ATTRIBUTE18
5494 ,p_attributes(l_index).ATTRIBUTE19
5495 ,p_attributes(l_index).ATTRIBUTE20
5496 ,p_attributes(l_index).ATTRIBUTE21
5497 ,p_attributes(l_index).ATTRIBUTE22
5498 ,p_attributes(l_index).ATTRIBUTE23
5499 ,p_attributes(l_index).ATTRIBUTE24
5500 ,p_attributes(l_index).ATTRIBUTE25
5501 ,p_attributes(l_index).ATTRIBUTE26
5502 ,p_attributes(l_index).ATTRIBUTE27
5503 ,p_attributes(l_index).ATTRIBUTE28
5504 ,p_attributes(l_index).ATTRIBUTE29
5505 ,p_attributes(l_index).ATTRIBUTE30
5506 ,p_attributes(l_index).BLD_BLK_INFO_TYPE_ID
5507 ,p_attributes(l_index).OBJECT_VERSION_NUMBER
5508 ,p_attributes(l_index).NEW
5509 ,p_attributes(l_index).CHANGED
5510 ,p_attributes(l_index).BLD_BLK_INFO_TYPE
5511 ,'N' -- New process flag
5512 ,null -- building block ovn
5513 );
5514
5515 l_index := p_attributes.next(l_index);
5516
5517 END LOOP;
5518
5519 return l_attributes;
5520
5521 End convert_attribute_to_type;
5522
5523
5524 Function convert_timecard_to_type
5525 (p_blocks in HXC_SELF_SERVICE_TIME_DEPOSIT.timecard_info)
5526 return HXC_BLOCK_TABLE_TYPE is
5527
5528 l_blocks HXC_BLOCK_TABLE_TYPE;
5529 l_index NUMBER;
5530
5531
5532 BEGIN
5533
5534 -- Initialize the collection
5535
5536 l_blocks := HXC_BLOCK_TABLE_TYPE();
5537
5538 l_index := p_blocks.first;
5539
5540 LOOP
5541
5542 EXIT WHEN NOT p_blocks.exists(l_index);
5543
5544 l_blocks.extend;
5545
5546 l_blocks(l_blocks.last) :=
5547 HXC_BLOCK_TYPE
5548 (p_blocks(l_index).TIME_BUILDING_BLOCK_ID
5549 ,p_blocks(l_index).TYPE
5550 ,p_blocks(l_index).MEASURE
5551 ,p_blocks(l_index).UNIT_OF_MEASURE
5552 ,p_blocks(l_index).START_TIME
5553 ,p_blocks(l_index).STOP_TIME
5554 ,p_blocks(l_index).PARENT_BUILDING_BLOCK_ID
5555 ,p_blocks(l_index).PARENT_IS_NEW
5556 ,p_blocks(l_index).SCOPE
5557 ,p_blocks(l_index).OBJECT_VERSION_NUMBER
5558 ,p_blocks(l_index).APPROVAL_STATUS
5559 ,p_blocks(l_index).RESOURCE_ID
5560 ,p_blocks(l_index).RESOURCE_TYPE
5561 ,p_blocks(l_index).APPROVAL_STYLE_ID
5562 ,p_blocks(l_index).DATE_FROM
5563 ,p_blocks(l_index).DATE_TO
5564 ,p_blocks(l_index).COMMENT_TEXT
5565 ,p_blocks(l_index).PARENT_BUILDING_BLOCK_OVN
5566 ,p_blocks(l_index).NEW
5567 ,p_blocks(l_index).CHANGED
5568 ,NULL
5569 ,NULL
5570 );
5571
5572 l_index := p_blocks.next(l_index);
5573
5574 END LOOP;
5575
5576 return l_blocks;
5577
5578 End convert_timecard_to_type;
5579
5580 */
5581 PROCEDURE get_translated_detail (p_detail_bb_id in NUMBER,
5582 p_detail_bb_ovn in NUMBER,
5583 p_resource_id in NUMBER,
5584 p_attributes OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
5585 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE)
5586 IS
5587
5588 cursor crs_tbb is
5589 select
5590 timecard.TIME_BUILDING_BLOCK_ID tc_TIME_BUILDING_BLOCK_ID
5591 ,timecard.START_TIME tc_START_TIME
5592 ,timecard.STOP_TIME tc_STOP_TIME
5593 ,timecard.PARENT_BUILDING_BLOCK_ID tc_PARENT_BUILDING_BLOCK_ID
5594 ,timecard.PARENT_BUILDING_BLOCK_OVN tc_PARENT_BUILDING_BLOCK_OVN
5595 ,timecard.SCOPE tc_SCOPE
5596 ,timecard.OBJECT_VERSION_NUMBER tc_OBJECT_VERSION_NUMBER
5597 ,day.TIME_BUILDING_BLOCK_ID day_TIME_BUILDING_BLOCK_ID
5598 ,day.START_TIME day_START_TIME
5599 ,day.STOP_TIME day_STOP_TIME
5600 ,day.PARENT_BUILDING_BLOCK_ID day_PARENT_BUILDING_BLOCK_ID
5601 ,day.PARENT_BUILDING_BLOCK_OVN day_PARENT_BUILDING_BLOCK_OVN
5602 ,day.SCOPE day_SCOPE
5603 ,day.OBJECT_VERSION_NUMBER day_OBJECT_VERSION_NUMBER
5604 ,detail.TIME_BUILDING_BLOCK_ID detail_TIME_BUILDING_BLOCK_ID
5605 ,detail.START_TIME detail_START_TIME
5606 ,detail.STOP_TIME detail_STOP_TIME
5607 ,detail.PARENT_BUILDING_BLOCK_ID detail_PARENT_BB_ID
5608 ,detail.PARENT_BUILDING_BLOCK_OVN detail_PARENT_BB_OVN
5609 ,detail.SCOPE detail_SCOPE
5610 ,detail.OBJECT_VERSION_NUMBER detail_OBJECT_VERSION_NUMBER
5611 FROM hxc_time_building_blocks timecard,
5612 hxc_time_building_blocks day,
5613 hxc_time_building_blocks detail
5614 where detail.time_building_block_id = p_detail_bb_id
5615 and detail.object_version_number = p_detail_bb_ovn
5616 and detail.resource_id = p_resource_id
5617 and detail.scope = 'DETAIL'
5618 and day.scope = 'DAY'
5619 and day.resource_id = p_resource_id
5620 and detail.parent_building_block_id = day.time_building_block_id
5621 and detail.parent_building_block_ovn = day.object_version_number
5622 and timecard.scope = 'TIMECARD'
5623 and timecard.resource_id = p_resource_id
5624 and day.parent_building_block_id = timecard.time_building_block_id
5625 and day.parent_building_block_ovn = timecard.object_version_number;
5626
5627 cursor crs_timecard_attribute
5628 (p_timecard_id in number,p_timecard_ovn in number) is
5629 select
5630 a.time_attribute_id
5631 ,au.time_building_block_id
5632 ,bbit.bld_blk_info_type
5633 ,a.attribute_category
5634 ,a.attribute1
5635 ,a.attribute2
5636 ,a.attribute3
5637 ,a.attribute4
5638 ,a.attribute5
5639 ,a.attribute6
5640 ,a.attribute7
5641 ,a.attribute8
5642 ,a.attribute9
5643 ,a.attribute10
5644 ,a.attribute11
5645 ,a.attribute12
5646 ,a.attribute13
5647 ,a.attribute14
5648 ,a.attribute15
5649 ,a.attribute16
5650 ,a.attribute17
5651 ,a.attribute18
5652 ,a.attribute19
5653 ,a.attribute20
5654 ,a.attribute21
5655 ,a.attribute22
5656 ,a.attribute23
5657 ,a.attribute24
5658 ,a.attribute25
5659 ,a.attribute26
5660 ,a.attribute27
5661 ,a.attribute28
5662 ,a.attribute29
5663 ,a.attribute30
5664 ,a.bld_blk_info_type_id
5665 ,a.object_version_number
5666 ,'N' NEW
5667 ,'N' CHANGED
5668 ,'N' PROCESS
5669 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
5670 from hxc_bld_blk_info_types bbit,
5671 hxc_time_attribute_usages au,
5672 hxc_time_attributes a,
5673 hxc_time_building_blocks htbb
5674 where a.time_attribute_id = au.time_attribute_id
5675 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
5676 and au.time_building_block_id = htbb.time_building_block_id
5677 and au.time_building_block_ovn = htbb.object_version_number
5678 and htbb.scope = 'TIMECARD'
5679 and htbb.time_building_block_id = p_timecard_id
5680 and htbb.object_version_number = p_timecard_ovn
5681 and htbb.resource_id = p_resource_id;
5682
5683
5684 cursor crs_detail_attribute
5685 IS
5686 select
5687 a.time_attribute_id
5688 ,au.time_building_block_id
5689 ,bbit.bld_blk_info_type
5690 ,a.attribute_category
5691 ,a.attribute1
5692 ,a.attribute2
5693 ,a.attribute3
5694 ,a.attribute4
5695 ,a.attribute5
5696 ,a.attribute6
5697 ,a.attribute7
5698 ,a.attribute8
5699 ,a.attribute9
5700 ,a.attribute10
5701 ,a.attribute11
5702 ,a.attribute12
5703 ,a.attribute13
5704 ,a.attribute14
5705 ,a.attribute15
5706 ,a.attribute16
5707 ,a.attribute17
5708 ,a.attribute18
5709 ,a.attribute19
5710 ,a.attribute20
5711 ,a.attribute21
5712 ,a.attribute22
5713 ,a.attribute23
5714 ,a.attribute24
5715 ,a.attribute25
5716 ,a.attribute26
5717 ,a.attribute27
5718 ,a.attribute28
5719 ,a.attribute29
5720 ,a.attribute30
5721 ,a.bld_blk_info_type_id
5722 ,a.object_version_number
5723 ,'N' NEW
5724 ,'N' CHANGED
5725 ,'N' PROCESS
5726 ,au.time_building_block_ovn BUILDING_BLOCK_OVN
5727 from hxc_bld_blk_info_types bbit,
5728 hxc_time_attribute_usages au,
5729 hxc_time_attributes a,
5730 hxc_time_building_blocks htbb
5731 where a.time_attribute_id = au.time_attribute_id
5732 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
5733 and au.time_building_block_id = htbb.time_building_block_id
5734 and au.time_building_block_ovn = htbb.object_version_number
5735 and htbb.scope = 'DETAIL'
5736 and htbb.time_building_block_id = p_detail_bb_id
5737 and htbb.object_version_number = p_detail_bb_ovn
5738 and htbb.resource_id = p_resource_id;
5739
5740 l_tbb_block HXC_BLOCK_TABLE_TYPE;
5741
5742 l_index NUMBER := 1;
5743 l_att_index NUMBER := 1;
5744
5745 BEGIN
5746
5747 l_tbb_block := HXC_BLOCK_TABLE_TYPE ();
5748 p_attributes := HXC_ATTRIBUTE_TABLE_TYPE();
5749
5750
5751 -- now we need to build the type table
5752 FOR c_tbb_info in crs_tbb LOOP
5753
5754 -- insert the timecard
5755 l_tbb_block.extend;
5756 l_index := l_tbb_block.last;
5757 l_tbb_block(l_index) :=
5758 hxc_block_type (
5759 c_tbb_info.tc_TIME_BUILDING_BLOCK_ID,
5760 null,
5761 null,
5762 null,
5763 fnd_date.date_to_canonical(c_tbb_info.tc_START_TIME),
5764 fnd_date.date_to_canonical(c_tbb_info.tc_STOP_TIME),
5765 c_tbb_info.tc_PARENT_BUILDING_BLOCK_ID,
5766 null,
5767 c_tbb_info.tc_SCOPE,
5768 c_tbb_info.tc_OBJECT_VERSION_NUMBER,
5769 null,
5770 null,
5771 null,
5772 null,
5773 null,
5774 null,
5775 null,
5776 c_tbb_info.tc_PARENT_BUILDING_BLOCK_OVN,
5777 null,
5778 null,
5779 null,
5780 null,
5781 null);
5782
5783 -- insert the day
5784 l_tbb_block.extend;
5785 l_index := l_tbb_block.last;
5786 l_tbb_block(l_index) :=
5787 hxc_block_type (
5788 c_tbb_info.day_TIME_BUILDING_BLOCK_ID,
5789 null,
5790 null,
5791 null,
5792 fnd_date.date_to_canonical(c_tbb_info.day_START_TIME),
5793 fnd_date.date_to_canonical(c_tbb_info.day_STOP_TIME),
5794 c_tbb_info.day_PARENT_BUILDING_BLOCK_ID,
5795 null,
5796 c_tbb_info.day_SCOPE,
5797 c_tbb_info.day_OBJECT_VERSION_NUMBER,
5798 null,
5799 null,
5800 null,
5801 null,
5802 null,
5803 null,
5804 null,
5805 c_tbb_info.day_PARENT_BUILDING_BLOCK_OVN,
5806 null,
5807 null,
5808 null,
5809 null,
5810 null);
5811
5812 --insert the detail
5813 l_tbb_block.extend;
5814 l_index := l_tbb_block.last;
5815 l_tbb_block(l_index) :=
5816 hxc_block_type (
5817 c_tbb_info.detail_TIME_BUILDING_BLOCK_ID,
5818 null,
5819 null,
5820 null,
5821 fnd_date.date_to_canonical(c_tbb_info.detail_START_TIME),
5822 fnd_date.date_to_canonical(c_tbb_info.detail_STOP_TIME),
5823 c_tbb_info.detail_PARENT_BB_ID,
5824 null,
5825 c_tbb_info.detail_SCOPE,
5826 c_tbb_info.detail_OBJECT_VERSION_NUMBER,
5827 null,
5828 null,
5829 null,
5830 null,
5831 null,
5832 null,
5833 null,
5834 c_tbb_info.detail_PARENT_BB_OVN,
5835 null,
5836 null,
5837 null,
5838 null,
5839 null);
5840
5841 -- insert the timecard attribute
5842 FOR c_timecard_attribute in crs_timecard_attribute
5843 (c_tbb_info.tc_TIME_BUILDING_BLOCK_ID
5844 ,c_tbb_info.tc_OBJECT_VERSION_NUMBER) LOOP
5845
5846 p_attributes.extend;
5847 l_att_index := p_attributes.last;
5848 p_attributes(l_att_index) :=
5849 hxc_attribute_type (
5850 c_timecard_attribute.time_attribute_id,
5851 c_timecard_attribute.time_building_block_id,
5852 c_timecard_attribute.attribute_category,
5853 c_timecard_attribute.attribute1,
5854 c_timecard_attribute.attribute2,
5855 c_timecard_attribute.attribute3,
5856 c_timecard_attribute.attribute4,
5857 c_timecard_attribute.attribute5,
5858 c_timecard_attribute.attribute6,
5859 c_timecard_attribute.attribute7,
5860 c_timecard_attribute.attribute8,
5861 c_timecard_attribute.attribute9,
5862 c_timecard_attribute.attribute10,
5863 c_timecard_attribute.attribute11,
5864 c_timecard_attribute.attribute12,
5865 c_timecard_attribute.attribute13,
5866 c_timecard_attribute.attribute14,
5867 c_timecard_attribute.attribute15,
5868 c_timecard_attribute.attribute16,
5869 c_timecard_attribute.attribute17,
5870 c_timecard_attribute.attribute18,
5871 c_timecard_attribute.attribute19,
5872 c_timecard_attribute.attribute20,
5873 c_timecard_attribute.attribute21,
5874 c_timecard_attribute.attribute22,
5875 c_timecard_attribute.attribute23,
5876 c_timecard_attribute.attribute24,
5877 c_timecard_attribute.attribute25,
5878 c_timecard_attribute.attribute26,
5879 c_timecard_attribute.attribute27,
5880 c_timecard_attribute.attribute28,
5881 c_timecard_attribute.attribute29,
5882 c_timecard_attribute.attribute30,
5883 c_timecard_attribute.bld_blk_info_type_id,
5884 c_timecard_attribute.object_version_number,
5885 c_timecard_attribute.NEW,
5886 c_timecard_attribute.CHANGED,
5887 c_timecard_attribute.bld_blk_info_type,
5888 c_timecard_attribute.PROCESS,
5889 c_timecard_attribute.BUILDING_BLOCK_OVN);
5890
5891 END LOOP;
5892
5893
5894 -- insert the detail attribute
5895 -- now we are populating the attribute of this detail
5896 FOR c_detail_attribute in crs_detail_attribute LOOP
5897
5898 p_attributes.extend;
5899 l_att_index := p_attributes.last;
5900 p_attributes(l_att_index) :=
5901 hxc_attribute_type (
5902 c_detail_attribute.time_attribute_id,
5903 c_detail_attribute.time_building_block_id,
5904 c_detail_attribute.attribute_category,
5905 c_detail_attribute.attribute1,
5906 c_detail_attribute.attribute2,
5907 c_detail_attribute.attribute3,
5908 c_detail_attribute.attribute4,
5909 c_detail_attribute.attribute5,
5910 c_detail_attribute.attribute6,
5911 c_detail_attribute.attribute7,
5912 c_detail_attribute.attribute8,
5913 c_detail_attribute.attribute9,
5914 c_detail_attribute.attribute10,
5915 c_detail_attribute.attribute11,
5916 c_detail_attribute.attribute12,
5917 c_detail_attribute.attribute13,
5918 c_detail_attribute.attribute14,
5919 c_detail_attribute.attribute15,
5920 c_detail_attribute.attribute16,
5921 c_detail_attribute.attribute17,
5922 c_detail_attribute.attribute18,
5923 c_detail_attribute.attribute19,
5924 c_detail_attribute.attribute20,
5925 c_detail_attribute.attribute21,
5926 c_detail_attribute.attribute22,
5927 c_detail_attribute.attribute23,
5928 c_detail_attribute.attribute24,
5929 c_detail_attribute.attribute25,
5930 c_detail_attribute.attribute26,
5931 c_detail_attribute.attribute27,
5932 c_detail_attribute.attribute28,
5933 c_detail_attribute.attribute29,
5934 c_detail_attribute.attribute30,
5935 c_detail_attribute.bld_blk_info_type_id,
5936 c_detail_attribute.object_version_number,
5937 c_detail_attribute.NEW,
5938 c_detail_attribute.CHANGED,
5939 c_detail_attribute.bld_blk_info_type,
5940 c_detail_attribute.PROCESS,
5941 c_detail_attribute.BUILDING_BLOCK_OVN);
5942
5943 END LOOP;
5944
5945 -- call the alias translator
5946 hxc_alias_translator.do_retrieval_translation
5947 (p_attributes => p_attributes
5948 ,p_blocks => l_tbb_block
5949 ,p_start_time => c_tbb_info.tc_start_time
5950 ,p_stop_time => c_tbb_info.tc_stop_time
5951 ,p_resource_id => p_resource_id
5952 ,p_processing_mode => hxc_alias_utility.c_ss_processing
5953 ,p_add_alias_display_value => true
5954 ,p_add_alias_ref_object => true
5955 ,p_messages => p_messages
5956 );
5957
5958 END LOOP;
5959
5960
5961 END get_translated_detail;
5962
5963
5964 END HXC_ALIAS_UTILITY;