[Home] [Help]
PACKAGE BODY: APPS.HXC_INLINE_NOTIF_UTILS_PKG
Source
1 Package Body hxc_inline_notif_utils_pkg as
2 /* $Header: hxcinnotiutl.pkb 120.15 2006/10/02 23:34:10 arundell noship $ */
3
4 c_end_of_time_string CONSTANT VARCHAR2(19) := '4712/12/31 00:00:00';
5 c_new_get_type CONSTANT VARCHAR2(3) := 'NEW';
6 c_old_get_type CONSTANT VARCHAR2(3) := 'OLD';
7
8 g_debug boolean := hr_utility.debug_enabled;
9
10 type context_details is record
11 (context fnd_descr_flex_contexts.descriptive_flex_context_code%type,
12 block_id hxc_time_building_blocks.time_building_block_id%type,
13 entered boolean
14 );
15
16 type context_details_table is table of context_details index by binary_integer;
17
18 FUNCTION get_table_name(p_access IN VARCHAR2)
19 RETURN VARCHAR2
20 AS
21 BEGIN
22 IF (instr(upper(p_access),'TIME')>0) THEN
23 RETURN 'HXC_TIME_BUILDING_BLOCKS';
24 ELSIF (instr(upper(p_access),'FLEX')>0) THEN
25 RETURN 'HXC_TIME_ATTRIBUTES';
26 ELSE
27 RETURN '';
28 END IF;
29 END get_table_name;
30
31 FUNCTION get_flex_value
32 (p_column IN VARCHAR2,
33 p_context IN VARCHAR2,
34 p_det_bb_id IN NUMBER,
35 p_det_bb_ovn IN NUMBER,
36 p_get_type IN VARCHAR2
37 )
38 RETURN VARCHAR2
39 AS
40
41 Cursor cur_flex_value_set(p_context IN VARCHAR2,p_column IN VARCHAR2) is
42 (select a.FLEX_VALUE_SET_ID from
43 FND_DESCR_FLEX_COLUMN_USAGES a,
44 fnd_flex_value_sets b
45 where a.descriptive_flexfield_name = 'OTC Information Types'
46 and a.application_id = 809
47 and a.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context
48 and a.flex_value_set_id = b.flex_value_set_id
49 and b.validation_type in ('F','I')
50 AND a.APPLICATION_COLUMN_NAME = p_column);
51
52 TYPE GetFlexVal IS REF CURSOR;
53 flex_cr GetFlexVal;
54
55 l_value VARCHAR2(150);
56 l_query VARCHAR2(3000);
57
58 l_id VARCHAR2(150);
59 l_flex_valuset_id VARCHAR2(150);
60
61 BEGIN
62 if g_debug then
63 hr_utility.trace(' p_context '||p_context);
64 hr_utility.trace(' p_det_bb_id '||p_det_bb_id);
65 hr_utility.trace(' p_det_bb_ovn '||p_det_bb_ovn);
66 end if;
67
68 l_query := ' select hta.'||p_column||fnd_global.local_chr('10');
69
70 if(p_get_type = c_new_get_type) then
71 if p_context like 'PAEXPITDFF%' THEN
72 l_query := l_query ||
73 'from hxc_time_building_blocks tbb, hxc_time_attribute_usages htau, hxc_time_attributes hta
74 where htau.time_building_block_id= :p_det_bb_id
75 and htau.time_building_block_ovn = :p_det_bb_ovn
76 and tbb.time_building_block_id = htau.time_building_block_id
77 and tbb.object_version_number = htau.time_building_block_ovn
78 and tbb.date_to = hr_general.end_of_time
79 and htau.time_attribute_id = hta.time_attribute_id
80 and hta.attribute_category = :p_context';
81 else
82 l_query := l_query ||
83 'from hxc_time_building_blocks tbb, hxc_time_attribute_usages htau, hxc_time_attributes hta, hxc_bld_blk_info_types bbit
84 where htau.time_building_block_id= :p_det_bb_id
85 and htau.time_building_block_ovn = :p_det_bb_ovn
86 and tbb.time_building_block_id = htau.time_building_block_id
87 and tbb.object_version_number = htau.time_building_block_ovn
88 and tbb.date_to = hr_general.end_of_time
89 and htau.time_attribute_id = hta.time_attribute_id
90 and hta.bld_blk_info_type_id = bbit.bld_blk_info_type_id
91 and bbit.bld_blk_info_type = :p_context';
92 end if;
93 else
94 if p_context like 'PAEXPITDFF%' THEN
95 l_query := l_query ||
96 'from hxc_time_attribute_usages htau, hxc_time_attributes hta
97 where htau.time_building_block_id= :p_det_bb_id
98 and htau.time_building_block_ovn = :p_det_bb_ovn
99 and htau.time_attribute_id = hta.time_attribute_id
100 and hta.attribute_category = :p_context';
101 else
102 l_query := l_query ||
103 'from hxc_time_attribute_usages htau, hxc_time_attributes hta, hxc_bld_blk_info_types bbit
104 where htau.time_building_block_id= :p_det_bb_id
105 and htau.time_building_block_ovn = :p_det_bb_ovn
106 and htau.time_attribute_id = hta.time_attribute_id
107 and hta.bld_blk_info_type_id = bbit.bld_blk_info_type_id
108 and bbit.bld_blk_info_type = :p_context';
109 end if;
110 end if;
111
112 OPEN flex_cr FOR l_query USING p_det_bb_id,p_det_bb_ovn,p_context;
113 FETCH flex_cr INTO l_id;
114
115 if g_debug then
116 hr_utility.trace(' l_id '||l_id);
117 end if;
118
119 IF (flex_cr%found) THEN
120 CLOSE flex_cr;
121
122
123 IF (p_context like 'PAEXPITDFF%') THEN
124 OPEN cur_flex_value_set(p_context,p_column);
125 FETCH cur_flex_value_set INTO l_flex_valuset_id;
126 if g_debug then
127 hr_utility.trace(' l_flex_valuset_id '||l_flex_valuset_id);
128 end if;
129 IF (cur_flex_value_set%found) and (l_flex_valuset_id is not null)
130 and (l_id is not null)
131 THEN
132
133 CLOSE cur_flex_value_set;
134 l_value := hxc_time_category_utils_pkg.get_flex_value
135 (p_flex_value_set_id => l_flex_valuset_id
136 ,p_id => l_id );
137
138
139 IF (l_value is null) THEN
140 RETURN(l_id);
141 ELSE
142 RETURN(l_value);
143 END IF;
144
145 ELSE
146 CLOSE cur_flex_value_set;
147 RETURN(l_id);
148 END IF;
149 ELSE
150 RETURN(l_id);
151 END IF;
152
153 ELSE
154 CLOSE flex_cr;
155 RETURN '';
156 END IF;
157
158 END get_flex_value;
159
160
161
162 FUNCTION get_tbb_value
163 (p_column IN VARCHAR2,
164 p_det_bb_id IN NUMBER,
165 p_det_bb_ovn IN NUMBER,
166 p_get_type IN VARCHAR2
167 )
168 RETURN VARCHAR2
169 AS
170
171 TYPE GetFlexVal IS REF CURSOR;
172 tbb_cr GetFlexVal;
173 l_query VARCHAR2(3000);
174 l_value VARCHAR2(2000);
175 l_column VARCHAR2(50);
176
177 BEGIN
178
179 IF((upper(p_column) like 'START_TIME')or (upper(p_column) like 'STOP_TIME')) THEN
180 l_column := 'to_char('||p_column||','||''''||'yyyy/MM/dd HH24:MI:ss'||''''||')';
181 ELSE
182 l_column := p_column;
183 END IF;
184
185 l_query := 'select '||l_column||'
186 from hxc_time_building_blocks
187 where time_building_block_id = :p_det_bb_id
188 and object_version_number = :p_det_bb_ovn';
189
190 if(p_get_type=c_new_get_type) then
191 l_query := l_query || fnd_global.local_chr('10') ||'and date_to = hr_general.end_of_time';
192 end if;
193
194 OPEN tbb_cr FOR l_query USING p_det_bb_id,p_det_bb_ovn;
195 FETCH tbb_cr INTO l_value;
196 IF (tbb_cr%found) THEN
197 CLOSE tbb_cr;
198 RETURN(l_value);
199 ELSE
200 CLOSE tbb_cr;
201 RETURN '';
202 END IF;
203
204 END get_tbb_value;
205
206 PROCEDURE get_block_info
207 (p_det_bb_id IN NUMBER,
208 p_det_bb_ovn IN NUMBER,
209 p_get_type IN VARCHAR2,
210 p_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info)
211 AS
212
213 CURSOR c_time_building_blocks
214 (p_bb_id IN HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE,
215 p_bb_ovn IN HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE,
216 p_get_type in varchar2) is
217 select tbb1.TIME_BUILDING_BLOCK_ID
218 ,tbb1.TYPE
219 ,tbb1.MEASURE
220 ,tbb1.UNIT_OF_MEASURE
221 ,tbb1.START_TIME
222 ,tbb1.STOP_TIME
223 ,tbb1.PARENT_BUILDING_BLOCK_ID
224 ,'N' PARENT_IS_NEW
225 ,tbb1.SCOPE
226 ,tbb1.OBJECT_VERSION_NUMBER
227 ,tbb1.APPROVAL_STATUS
228 ,tbb1.RESOURCE_ID
229 ,tbb1.RESOURCE_TYPE
230 ,tbb1.APPROVAL_STYLE_ID
231 ,tbb1.DATE_FROM
232 ,tbb1.DATE_TO
233 ,tbb1.COMMENT_TEXT
234 ,tbb1.PARENT_BUILDING_BLOCK_OVN
235 ,'N' NEW
236 ,'N' CHANGED
237 ,'N' PROCESS
238 ,tbb1.application_set_id
239 ,tbb1.translation_display_key
240 from hxc_time_building_blocks tbb1
241 where tbb1.TIME_BUILDING_BLOCK_ID = p_bb_id
242 and tbb1.OBJECT_VERSION_NUMBER = p_bb_ovn
243 and decode(p_get_type,'NEW',hr_general.end_of_time,tbb1.date_to) = tbb1.date_to;
244
245 l_block hxc_self_service_time_deposit.building_block_info;
246 l_block_index NUMBER;
247
248 BEGIN
249
250 IF p_blocks.count = 0
251 THEN
252 l_block_index := 1;
253 ELSE
254 l_block_index := p_blocks.last + 1;
255 END IF;
256
257 open c_time_building_blocks(p_det_bb_id,p_det_bb_ovn,p_get_type);
258 fetch c_time_building_blocks into l_block;
259 if(c_time_building_blocks%found) then
260 p_blocks(l_block_index) := l_block;
261 end if;
262 close c_time_building_blocks;
263
264 END;
265
266 PROCEDURE get_attributes(
267 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
268 ,p_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
269 ,p_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info
270 )
271 IS
272 l_attribute_index NUMBER;
273 l_temp_attribute hxc_self_service_time_deposit.attribute_info;
274
275 CURSOR c_block_attributes(
276 p_building_block_id IN HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
277 ,p_ovn IN HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
278 )
279 IS
280 select a.time_attribute_id
281 ,au.time_building_block_id
282 ,bbit.bld_blk_info_type
283 ,a.attribute_category
284 ,a.attribute1
285 ,a.attribute2
286 ,a.attribute3
287 ,a.attribute4
288 ,a.attribute5
289 ,a.attribute6
290 ,a.attribute7
291 ,a.attribute8
292 ,a.attribute9
293 ,a.attribute10
294 ,a.attribute11
295 ,a.attribute12
296 ,a.attribute13
297 ,a.attribute14
298 ,a.attribute15
299 ,a.attribute16
300 ,a.attribute17
301 ,a.attribute18
302 ,a.attribute19
303 ,a.attribute20
304 ,a.attribute21
305 ,a.attribute22
306 ,a.attribute23
307 ,a.attribute24
308 ,a.attribute25
309 ,a.attribute26
310 ,a.attribute27
311 ,a.attribute28
312 ,a.attribute29
313 ,a.attribute30
314 ,a.bld_blk_info_type_id
315 ,a.object_version_number
316 ,'N' NEW
317 ,'N' CHANGED
318 ,'N' PROCESS
319 from hxc_time_attributes a,
320 hxc_time_attribute_usages au,
321 hxc_bld_blk_info_types bbit
322 where au.time_building_block_id = p_building_block_id
323 and au.time_building_block_ovn = p_ovn
324 and au.time_attribute_id = a.time_attribute_id
325 and (not (a.attribute_category = 'SECURITY'))
326 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
327
328
329 BEGIN
330
331 IF p_attributes.count = 0
332 THEN
333 l_attribute_index := 1;
334 ELSE
335 l_attribute_index := p_attributes.last + 1;
336 END IF;
337
338 OPEN c_block_attributes(
339 p_building_block_id => p_block_id
340 ,p_ovn => p_block_ovn
341 );
342
343 LOOP
344 FETCH c_block_attributes INTO l_temp_attribute;
345 EXIT WHEN c_block_attributes%NOTFOUND;
346
347 p_attributes(l_attribute_index) := l_temp_attribute;
348
349 l_attribute_index := l_attribute_index + 1;
350 END LOOP;
351
352 CLOSE c_block_attributes;
353 END get_attributes;
354
355 PROCEDURE translate_alias_timecards(
356 p_resource_id IN VARCHAR2
357 ,p_start_time IN VARCHAR2
358 ,p_stop_time IN VARCHAR2
359 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
360 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
361 )
362 IS
363 l_resource_id VARCHAR2(50) := NULL;
364
365 l_messages_table HXC_MESSAGE_TABLE_TYPE;
366
367 l_bb_count NUMBER;
368 l_att_count NUMBER;
369
370 BEGIN
371 -- call translator alias package
372 l_resource_id := p_resource_id;
373
374 IF l_resource_id IS NULL
375 THEN
376 l_resource_id := p_block_array(1).resource_id;
377 END IF;
378
379
380 HXC_ALIAS_TRANSLATOR.do_retrieval_translation(
381 p_attributes => p_attribute_array
382 ,p_blocks => p_block_array
383 ,p_start_time => p_start_time --FND_DATE.CANONICAL_TO_DATE(p_start_time)
384 ,p_stop_time => p_stop_time --FND_DATE.CANONICAL_TO_DATE(p_stop_time)
385 ,p_resource_id => l_resource_id
386 ,p_messages => l_messages_table
387 );
388
389 END translate_alias_timecards;
390
391
392
393 FUNCTION get_olt_alias
394 (p_det_bb_id in number,
395 p_det_bb_ovn in number,
396 p_get_type in varchar2,
397 p_context in varchar2
398 ) return varchar2 is
399
400 CURSOR get_otl_alias(p_alias_value_id NUMBER) IS
401 SELECT alias_value_name
402 FROM hxc_alias_values_tl
403 WHERE alias_value_id = p_alias_value_id;
404
405 l_get_otl_alias get_otl_alias%rowtype;
406
407 l_blocks hxc_self_service_time_deposit.timecard_info;
408 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
409 l_day_bb_id NUMBER;
410 l_day_bb_ovn NUMBER;
411 l_tim_bb_id NUMBER;
412 l_tim_bb_ovn NUMBER;
413 l_block_array HXC_BLOCK_TABLE_TYPE;
414 l_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
415 l_attribute_index NUMBER;
416
417 Begin
418 get_block_info(p_det_bb_id, p_det_bb_ovn, p_get_type, l_blocks);
419 if(l_blocks.count>0) then
420 get_attributes(p_det_bb_id, p_det_bb_ovn, l_attributes);
421
422 l_day_bb_id := l_blocks(l_blocks.count).parent_building_block_id;
423 l_day_bb_ovn := l_blocks(l_blocks.count).parent_building_block_ovn;
424
425 get_block_info(l_day_bb_id, l_day_bb_ovn, p_get_type, l_blocks);
426 get_attributes(l_day_bb_id, l_day_bb_ovn, l_attributes);
427
428 l_tim_bb_id := l_blocks(l_blocks.count).parent_building_block_id;
429 l_tim_bb_ovn := l_blocks(l_blocks.count).parent_building_block_ovn;
430
431 get_block_info(l_tim_bb_id, l_tim_bb_ovn, p_get_type, l_blocks);
432 get_attributes(l_tim_bb_id, l_tim_bb_ovn, l_attributes);
433
434 l_block_array := hxc_deposit_wrapper_utilities.blocks_to_array
435 (p_blocks => l_blocks);
436 l_attribute_array := hxc_deposit_wrapper_utilities.attributes_to_array
437 (p_attributes => l_attributes);
438
439 translate_alias_timecards
440 (p_resource_id => l_blocks(l_blocks.last).resource_id,
441 p_start_time => l_blocks(l_blocks.last).start_time,
442 p_stop_time => l_blocks(l_blocks.last).stop_time,
443 p_block_array => l_block_array,
444 p_attribute_array => l_attribute_array
445 );
446
447 l_attribute_index := l_attribute_array.first;
448 Loop
449 exit when not l_attribute_array.exists(l_attribute_index);
450 if((l_attribute_array(l_attribute_index).building_block_id = p_det_bb_id)
451 and
452 (UPPER(l_attribute_array(l_attribute_index).attribute_category) = upper(p_context))
453 ) then
454 open get_otl_alias(to_number(l_attribute_array(l_attribute_index).attribute1));
455 fetch get_otl_alias into l_get_otl_alias;
456 if(get_otl_alias%notfound) then
457 close get_otl_alias;
458 return('');
459 else
460 close get_otl_alias;
461 return(l_get_otl_alias.alias_value_name);
462 end if;
463 end if;
464
465 l_attribute_index := l_attribute_array.next(l_attribute_index);
466
467 end loop;
468 end if; -- no detail building block as required
469
470 return('');
471
472 END get_olt_alias;
473
474 function context_index
475 (p_context_details in context_details_table,
476 p_context in varchar2,
477 p_block_id in hxc_time_building_blocks.time_building_block_id%type
478 ) return pls_integer is
479 l_index pls_integer;
480 begin
481 l_index := p_context_details.first;
482 loop
483 exit when not p_context_details.exists(l_index);
484 if(
485 (p_context_details(l_index).context = p_context)
486 AND
487 (p_context_details(l_index).block_id = p_block_id)
488 )then
489 return l_index;
490 end if;
491 l_index := p_context_details.next(l_index);
492 end loop;
493 return null;
494 end context_index;
495
496 function add_day_detail_record
497 (p_day_detail in HXC_DAY_DETAIL_TYPE,
498 p_context_details in context_details_table)
499 return boolean is
500 l_return boolean;
501 begin
502 l_return := true;
503
504 if(instr(p_day_detail.context,'PAEXPITDFF')>0) then
505 if( NOT p_context_details(context_index(p_context_details,p_day_detail.context,p_day_detail.detail_bb_id)).entered) then
506 l_return := false;
507 end if;
508 end if;
509
510 return l_return;
511
512 end add_day_detail_record;
513
514 function parse_day_details_table
515 (p_day_details_table in HXC_DAY_DETAIL_TABLE_TYPE,
516 p_context_details in context_details_table)
517 return HXC_DAY_DETAIL_TABLE_TYPE is
518
519 l_day_details_table HXC_DAY_DETAIL_TABLE_TYPE;
520 l_index number;
521
522 Begin
523 l_day_details_table := HXC_DAY_DETAIL_TABLE_TYPE();
524
525 l_index := p_day_details_table.first;
526 Loop
527 Exit when not p_day_details_table.exists(l_index);
528
529 if(add_day_detail_record(p_day_details_table(l_index),p_context_details)) then
530 l_day_details_table.extend;
531 l_day_details_table(l_day_details_table.last) := p_day_details_table(l_index);
532 end if;
533
534 l_index := p_day_details_table.next(l_index);
535 End Loop;
536
537 return l_day_details_table;
538
539 End parse_day_details_table;
540
541 procedure maintain_context_details
542 (p_context_details in out nocopy context_details_table,
543 p_block_id in hxc_time_building_blocks.time_building_block_id%type,
544 p_context in varchar2,
545 p_new_entry in varchar2,
546 p_old_entry in varchar2) is
547 l_index pls_integer;
548 begin
549 if(instr(p_context,'PAEXPITDFF') > 0) then
550 l_index := context_index(p_context_details,p_context,p_block_id);
551 if(l_index is null) then
552 l_index := nvl(p_context_details.last,0) + 1;
553 p_context_details(l_index).context := p_context;
554 p_context_details(l_index).block_id := p_block_id;
555 p_context_details(l_index).entered := false;
556 end if;
557
558 if(p_context_details(l_index).entered) then
559 null;
560 else
561 if((p_new_entry is not null) or (p_old_entry is not null)) then
562 p_context_details(l_index).entered := true;
563 end if;
564 end if;
565 end if;
566
567 end maintain_context_details;
568
569 PROCEDURE fetch_day_details
570 (p_app_bb_id IN NUMBER,
571 p_tk_audit IN VARCHAR2,
572 p_day_detail_array IN OUT NOCOPY HXC_DAY_DETAIL_TABLE_TYPE,
573 p_message_string OUT NOCOPY VARCHAR2
574 ) AS
575
576 l_table_name VARCHAR2(30);
577 l_context VARCHAR2(30);
578 l_column VARCHAR2(15);
579 l_old_det_ovn NUMBER;
580 l_dd_count NUMBER;
581 l_det_bb_id NUMBER;
582 l_det_bb_ovn NUMBER;
583 l_last_creation_date DATE;
584
585 l_day_detail_array HXC_DAY_DETAIL_TABLE_TYPE;
586 l_index pls_integer;
587 l_string varchar2(1);
588 l_context_details context_details_table;
589
590 cursor get_last_creation_dates(p_app_bb_id NUMBER) is
591 select tab.creation_date
592 from (select distinct htbb.creation_date creation_date
593 from hxc_time_building_blocks htbb, hxc_ap_detail_links hadl
594 where htbb.time_building_block_id = hadl.time_building_block_id
595 and hadl.application_period_id = p_app_bb_id
596 order by creation_date desc) tab
597 where rownum <= 2;
598
599 cursor get_tk_last_creation_dates(p_app_bb_id NUMBER) is
600 select tab.creation_date
601 from (select distinct detail.creation_date creation_date
602 from hxc_time_building_blocks detail,hxc_time_building_blocks day
603 where day.parent_building_block_id =p_app_bb_id
604 and detail.parent_building_block_id = day.time_building_block_id
605 and detail.parent_building_block_ovn = day.object_version_number
606 and detail.scope='DETAIL'
607 order by 1 desc) tab
608 where rownum <= 2;
609
610 cursor get_old_det_ovn(p_det_bb_id NUMBER,p_last_creation_date DATE) is
611 select NVL(max(object_version_number),-1)
612 from hxc_time_building_blocks htbb
613 where htbb.creation_date <= (p_last_creation_date+0.000011574)
614 and date_to <> hr_general.end_of_time
615 and htbb.time_building_block_id = p_det_bb_id
616 and htbb.approval_status = hxc_timecard.c_submitted_status;
617
618 BEGIN
619
620 g_debug := hr_utility.debug_enabled;
621
622 l_day_detail_array := p_day_detail_array;
623
624 if p_tk_audit ='YES' then
625 open get_tk_last_creation_dates(p_app_bb_id);
626 loop
627 fetch get_tk_last_creation_dates into l_last_creation_date;
628 exit when get_tk_last_creation_dates%notfound;
629 end loop;
630
631 IF(get_tk_last_creation_dates%rowcount<2) THEN
632 l_last_creation_date := null;
633 END IF;
634 close get_tk_last_creation_dates;
635 else
636 open get_last_creation_dates(p_app_bb_id);
637 loop
638 fetch get_last_creation_dates into l_last_creation_date;
639 exit when get_last_creation_dates%notfound;
640 end loop;
641
642 IF(get_last_creation_dates%rowcount<2) THEN
643 l_last_creation_date := null;
644 END IF;
645 close get_last_creation_dates;
646
647 end if;
648
649 l_dd_count := l_day_detail_array.first;
650
651 LOOP
652 EXIT WHEN NOT l_day_detail_array.exists(l_dd_count);
653
654 l_det_bb_id := l_day_detail_array(l_dd_count).detail_bb_id;
655 l_det_bb_ovn := l_day_detail_array(l_dd_count).detail_bb_ovn;
656
657 l_table_name := get_table_name(l_day_detail_array(l_dd_count).tabaccess);
658 l_context := l_day_detail_array(l_dd_count).context;
659 l_column := l_day_detail_array(l_dd_count).attribute;
660 maintain_context_details
661 (l_context_details,
662 l_det_bb_id,
663 l_context,
664 l_day_detail_array(l_dd_count).new_entry,
665 l_day_detail_array(l_dd_count).old_entry);
666 IF(instr(upper(l_context),'OTL_ALIAS')>0) THEN
667
668 l_day_detail_array(l_dd_count).new_entry := get_olt_alias(l_det_bb_id,l_det_bb_ovn,c_new_get_type,l_context);
669 OPEN get_old_det_ovn(l_det_bb_id, l_last_creation_date);
670 FETCH get_old_det_ovn INTO l_old_det_ovn;
671
672 IF(l_last_creation_date IS null) THEN
673 l_day_detail_array(l_dd_count).old_entry := '';
674 ELSE
675 IF (l_old_det_ovn = -1) THEN
676 l_day_detail_array(l_dd_count).old_entry := '';
677 ELSE
678 l_day_detail_array(l_dd_count).old_entry := get_olt_alias(l_det_bb_id,l_old_det_ovn,c_old_get_type,l_context);
679 END IF;
680 END IF;
681 CLOSE get_old_det_ovn;
682
683 ELSE
684
685 IF(l_table_name = 'HXC_TIME_ATTRIBUTES') THEN
686 l_day_detail_array(l_dd_count).new_entry := get_flex_value(l_column,l_context,l_det_bb_id,l_det_bb_ovn,c_new_get_type);
687 ELSIF (l_table_name = 'HXC_TIME_BUILDING_BLOCKS') THEN
688 l_day_detail_array(l_dd_count).new_entry := get_tbb_value(l_column,l_det_bb_id,l_det_bb_ovn,c_new_get_type);
689 END IF;
690
691
692 IF(l_last_creation_date IS null) THEN
693 l_day_detail_array(l_dd_count).old_entry := '';
694 ELSE
695 OPEN get_old_det_ovn(l_det_bb_id,l_last_creation_date);
696 FETCH get_old_det_ovn INTO l_old_det_ovn;
697
698 IF (l_old_det_ovn = -1) THEN
699 l_day_detail_array(l_dd_count).old_entry := '';
700 ELSE
701 IF(l_table_name = 'HXC_TIME_ATTRIBUTES') THEN
702 l_day_detail_array(l_dd_count).old_entry := get_flex_value(l_column,l_context,l_det_bb_id,l_old_det_ovn,c_old_get_type);
703 ELSIF (l_table_name = 'HXC_TIME_BUILDING_BLOCKS') THEN
704 l_day_detail_array(l_dd_count).old_entry := get_tbb_value(l_column,l_det_bb_id,l_old_det_ovn,c_old_get_type);
705 END IF;
706 END IF;
707
708 CLOSE get_old_det_ovn;
709
710 END IF;
711 END IF;
712 maintain_context_details
713 (l_context_details,
714 l_det_bb_id,
715 l_context,
716 l_day_detail_array(l_dd_count).new_entry,
717 l_day_detail_array(l_dd_count).old_entry);
718 l_dd_count := l_day_detail_array.next(l_dd_count);
719
720 END LOOP;
721
722 p_day_detail_array := parse_day_details_table(l_day_detail_array, l_context_details);
723
724 END fetch_day_details;
725
726 procedure tokenizer ( iStart IN NUMBER,
727 sPattern in VARCHAR2,
728 sBuffer in VARCHAR2,
729 sResult OUT NOCOPY VARCHAR2,
730 iNextPos OUT NOCOPY NUMBER)
731 AS
732 nPos1 number;
733 nPos2 number;
734 BEGIN
735
736 nPos1 := Instr (sBuffer ,sPattern ,iStart);
737 IF nPos1 = 0 then
738 sResult := NULL ;
739 ELSE
740 nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
741 IF nPos2 = 0 then
742 sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
743 iNextPos := nPos2;
744 else
745 sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
746 iNextPos := nPos2;
747 END IF;
748 END IF;
749 END tokenizer ;
750
751
752 PROCEDURE get_alias_values_from_db
753 (p_bb_id IN NUMBER,
754 p_bb_ovn IN NUMBER,
755 p_layout_comp_id IN NUMBER,
756 p_alias_value_list OUT NOCOPY VARCHAR2
757 )
758 IS
759 TYPE AliasValueCurTyp IS REF CURSOR;
760
761 aliasval_cv AliasValueCurTyp;
762
763 l_select hxc_layout_comp_qualifiers.qualifier_attribute27%type;
764 l_time_building_block_id number;
765 l_time_building_block_ovn number;
766 l_query varchar2(32000);
767 l_bld_blk_info_type hxc_bld_blk_info_types.bld_blk_info_type%type;
768 l_separator varchar2(10);
769 l_position number;
770 l_start_position number;
771
772 p_alias_value varchar2(250);
773 p_alias_name varchar2(250);
774 l_alias_list varchar2(250);
775 l_alias_name varchar2(250);
776 l_dummy_alias_name varchar2(250);
777
778 cursor get_alias_list(p_layout_comp_id in number)
779 is
780 select QUALIFIER_ATTRIBUTE28||'|'||QUALIFIER_ATTRIBUTE28||'|'||QUALIFIER_ATTRIBUTE7
781 from HXC_LAYOUT_COMP_QUALIFIERS where LAYOUT_COMPONENT_ID = p_layout_comp_id ;
782
783 cursor get_alias_location(p_layout_comp_id in number,p_alias_name in varchar2)
784 is
785 SELECT
786 Distinct A.Qualifier_Attribute26,
787 A.Qualifier_Attribute27
788 FROM
789 Hxc_Layout_Comp_Qualifiers A,
790 Hxc_Layout_Components B,
791 Hxc_Layouts C
792 WHERE
793 C.Layout_Id = (Select Layout_Id From Hxc_Layout_Components Where Layout_Component_Id = P_Layout_Comp_Id) And
794 A.Layout_Component_Id = B.Layout_Component_Id And
795 A.Qualifier_Attribute28=p_alias_name;
796
797
798 BEGIN
799
800 open get_alias_list(p_layout_comp_id );
801 fetch get_alias_list into l_alias_list;
802 close get_alias_list;
803
804 l_alias_list := '|'||l_alias_list;
805 l_position:=-1;
806 l_separator := '|';
807 l_start_position := 1;
808
809 while (l_position <> 0)
810 loop
811 --Tokenize twice to get the alias name from alias - cui name pair like
812 --STATE|State|COUNTY|County|CITY|City for work location lov
813 tokenizer (l_start_position ,l_separator,l_alias_list,l_alias_name,l_position);
814 l_start_position := l_position;
815 tokenizer (l_start_position ,l_separator,l_alias_list,l_dummy_alias_name,l_position);
816 l_start_position := l_position;
817
818 open get_alias_location(p_layout_comp_id ,l_alias_name ) ;
819 fetch get_alias_location into l_bld_blk_info_type,l_select;
820 close get_alias_location;
821
822 --Dynamic query to select alias value as get_alias_location gives storing attribute
823 -- and building block info type for each alias name
824 l_query:= 'select ta.'|| l_select;
825 l_query:= l_query || ' from
826 hxc_time_attributes ta,
827 hxc_time_attribute_usages tau,
828 HXC_BLD_BLK_INFO_TYPES bbit
829 where tau.time_building_block_id = :l_time_building_block_id and tau.time_building_block_ovn = :l_time_building_block_ovn
830 and tau.time_attribute_id = ta.time_attribute_id
831 and bbit.bld_blk_info_type_id = ta.bld_blk_info_type_id
832 and bbit.bld_blk_info_type = :l_bld_blk_info_type';
833
834 OPEN aliasval_cv FOR l_query using p_bb_id,p_bb_ovn,l_bld_blk_info_type;
835 FETCH aliasval_cv INTO p_alias_value;
836 close aliasval_cv;
837
838 p_alias_value_list:=p_alias_value_list || p_alias_value ||'*#*';
839 end loop;
840 end get_alias_values_from_db;
841
842 END hxc_inline_notif_utils_pkg;