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