[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.12020000.2 2012/07/04 09:25:38 amnaraya ship $ */
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 and language = userenv('LANG');
405
406 l_get_otl_alias get_otl_alias%rowtype;
407
408 l_blocks hxc_self_service_time_deposit.timecard_info;
409 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
410 l_day_bb_id NUMBER;
411 l_day_bb_ovn NUMBER;
412 l_tim_bb_id NUMBER;
413 l_tim_bb_ovn NUMBER;
414 l_block_array HXC_BLOCK_TABLE_TYPE;
415 l_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
416 l_attribute_index NUMBER;
417
418 Begin
419 get_block_info(p_det_bb_id, p_det_bb_ovn, p_get_type, l_blocks);
420 if(l_blocks.count>0) then
421 get_attributes(p_det_bb_id, p_det_bb_ovn, l_attributes);
422
423 l_day_bb_id := l_blocks(l_blocks.count).parent_building_block_id;
424 l_day_bb_ovn := l_blocks(l_blocks.count).parent_building_block_ovn;
425
426 get_block_info(l_day_bb_id, l_day_bb_ovn, p_get_type, l_blocks);
427 get_attributes(l_day_bb_id, l_day_bb_ovn, l_attributes);
428
429 l_tim_bb_id := l_blocks(l_blocks.count).parent_building_block_id;
430 l_tim_bb_ovn := l_blocks(l_blocks.count).parent_building_block_ovn;
431
432 get_block_info(l_tim_bb_id, l_tim_bb_ovn, p_get_type, l_blocks);
433 get_attributes(l_tim_bb_id, l_tim_bb_ovn, l_attributes);
434
435 l_block_array := hxc_deposit_wrapper_utilities.blocks_to_array
436 (p_blocks => l_blocks);
437 l_attribute_array := hxc_deposit_wrapper_utilities.attributes_to_array
438 (p_attributes => l_attributes);
439
440 translate_alias_timecards
441 (p_resource_id => l_blocks(l_blocks.last).resource_id,
442 p_start_time => l_blocks(l_blocks.last).start_time,
443 p_stop_time => l_blocks(l_blocks.last).stop_time,
444 p_block_array => l_block_array,
445 p_attribute_array => l_attribute_array
446 );
447
448 l_attribute_index := l_attribute_array.first;
449 Loop
450 exit when not l_attribute_array.exists(l_attribute_index);
451 if((l_attribute_array(l_attribute_index).building_block_id = p_det_bb_id)
452 and
453 (UPPER(l_attribute_array(l_attribute_index).attribute_category) = upper(p_context))
454 ) then
455 open get_otl_alias(to_number(l_attribute_array(l_attribute_index).attribute1));
456 fetch get_otl_alias into l_get_otl_alias;
457 if(get_otl_alias%notfound) then
458 close get_otl_alias;
459 return('');
460 else
461 close get_otl_alias;
462 return(l_get_otl_alias.alias_value_name);
463 end if;
464 end if;
465
466 l_attribute_index := l_attribute_array.next(l_attribute_index);
467
468 end loop;
469 end if; -- no detail building block as required
470
471 return('');
472
473 END get_olt_alias;
474
475 function context_index
476 (p_context_details in context_details_table,
477 p_context in varchar2,
478 p_block_id in hxc_time_building_blocks.time_building_block_id%type
479 ) return pls_integer is
480 l_index pls_integer;
481 begin
482 l_index := p_context_details.first;
483 loop
484 exit when not p_context_details.exists(l_index);
485 if(
486 (p_context_details(l_index).context = p_context)
487 AND
488 (p_context_details(l_index).block_id = p_block_id)
489 )then
490 return l_index;
491 end if;
492 l_index := p_context_details.next(l_index);
493 end loop;
494 return null;
495 end context_index;
496
497 function add_day_detail_record
498 (p_day_detail in HXC_DAY_DETAIL_TYPE,
499 p_context_details in context_details_table)
500 return boolean is
501 l_return boolean;
502 begin
503 l_return := true;
504
505 if(instr(p_day_detail.context,'PAEXPITDFF')>0) then
506 if( NOT p_context_details(context_index(p_context_details,p_day_detail.context,p_day_detail.detail_bb_id)).entered) then
507 l_return := false;
508 end if;
509 end if;
510
511 return l_return;
512
513 end add_day_detail_record;
514
515 function parse_day_details_table
516 (p_day_details_table in HXC_DAY_DETAIL_TABLE_TYPE,
517 p_context_details in context_details_table)
518 return HXC_DAY_DETAIL_TABLE_TYPE is
519
520 l_day_details_table HXC_DAY_DETAIL_TABLE_TYPE;
521 l_index number;
522
523 Begin
524 l_day_details_table := HXC_DAY_DETAIL_TABLE_TYPE();
525
526 l_index := p_day_details_table.first;
527 Loop
528 Exit when not p_day_details_table.exists(l_index);
529
530 if(add_day_detail_record(p_day_details_table(l_index),p_context_details)) then
531 l_day_details_table.extend;
532 l_day_details_table(l_day_details_table.last) := p_day_details_table(l_index);
533 end if;
534
535 l_index := p_day_details_table.next(l_index);
536 End Loop;
537
538 return l_day_details_table;
539
540 End parse_day_details_table;
541
542 procedure maintain_context_details
543 (p_context_details in out nocopy context_details_table,
544 p_block_id in hxc_time_building_blocks.time_building_block_id%type,
545 p_context in varchar2,
546 p_new_entry in varchar2,
547 p_old_entry in varchar2) is
548 l_index pls_integer;
549 begin
550 if(instr(p_context,'PAEXPITDFF') > 0) then
551 l_index := context_index(p_context_details,p_context,p_block_id);
552 if(l_index is null) then
553 l_index := nvl(p_context_details.last,0) + 1;
554 p_context_details(l_index).context := p_context;
555 p_context_details(l_index).block_id := p_block_id;
556 p_context_details(l_index).entered := false;
557 end if;
558
559 if(p_context_details(l_index).entered) then
560 null;
561 else
562 if((p_new_entry is not null) or (p_old_entry is not null)) then
563 p_context_details(l_index).entered := true;
564 end if;
565 end if;
566 end if;
567
568 end maintain_context_details;
569
570 PROCEDURE fetch_day_details
571 (p_app_bb_id IN NUMBER,
572 p_tk_audit IN VARCHAR2,
573 p_day_detail_array IN OUT NOCOPY HXC_DAY_DETAIL_TABLE_TYPE,
574 p_message_string OUT NOCOPY VARCHAR2
575 ) AS
576
577 l_table_name VARCHAR2(30);
578 l_context VARCHAR2(30);
579 l_column VARCHAR2(15);
580 l_old_det_ovn NUMBER;
581 l_dd_count NUMBER;
582 l_det_bb_id NUMBER;
583 l_det_bb_ovn NUMBER;
584 l_last_creation_date DATE;
585
586 l_day_detail_array HXC_DAY_DETAIL_TABLE_TYPE;
587 l_index pls_integer;
588 l_string varchar2(1);
589 l_context_details context_details_table;
590
591 cursor get_last_creation_dates(p_app_bb_id NUMBER) is
592 select tab.creation_date
593 from (select distinct htbb.creation_date creation_date
594 from hxc_time_building_blocks htbb, hxc_ap_detail_links hadl
595 where htbb.time_building_block_id = hadl.time_building_block_id
596 and hadl.application_period_id = p_app_bb_id
597 order by creation_date desc) tab
598 where rownum <= 2;
599
600 cursor get_tk_last_creation_dates(p_app_bb_id NUMBER) is
601 select tab.creation_date
602 from (select distinct detail.creation_date creation_date
603 from hxc_time_building_blocks detail,hxc_time_building_blocks day
604 where day.parent_building_block_id =p_app_bb_id
605 and detail.parent_building_block_id = day.time_building_block_id
606 and detail.parent_building_block_ovn = day.object_version_number
607 and detail.scope='DETAIL'
608 order by 1 desc) tab
609 where rownum <= 2;
610
611 cursor get_old_det_ovn(p_det_bb_id NUMBER,p_last_creation_date DATE) is
612 select NVL(max(object_version_number),-1)
613 from hxc_time_building_blocks htbb
614 where htbb.creation_date <= (p_last_creation_date+0.000011574)
615 and date_to <> hr_general.end_of_time
616 and htbb.time_building_block_id = p_det_bb_id
617 and htbb.approval_status = hxc_timecard.c_submitted_status;
618
619 BEGIN
620
621 g_debug := hr_utility.debug_enabled;
622
623 l_day_detail_array := p_day_detail_array;
624
625 if p_tk_audit ='YES' then
626 open get_tk_last_creation_dates(p_app_bb_id);
627 loop
628 fetch get_tk_last_creation_dates into l_last_creation_date;
629 exit when get_tk_last_creation_dates%notfound;
630 end loop;
631
632 IF(get_tk_last_creation_dates%rowcount<2) THEN
633 l_last_creation_date := null;
634 END IF;
635 close get_tk_last_creation_dates;
636 else
637 open get_last_creation_dates(p_app_bb_id);
638 loop
639 fetch get_last_creation_dates into l_last_creation_date;
640 exit when get_last_creation_dates%notfound;
641 end loop;
642
643 IF(get_last_creation_dates%rowcount<2) THEN
644 l_last_creation_date := null;
645 END IF;
646 close get_last_creation_dates;
647
648 end if;
649
650 l_dd_count := l_day_detail_array.first;
651
652 LOOP
653 EXIT WHEN NOT l_day_detail_array.exists(l_dd_count);
654
655 l_det_bb_id := l_day_detail_array(l_dd_count).detail_bb_id;
656 l_det_bb_ovn := l_day_detail_array(l_dd_count).detail_bb_ovn;
657
658 l_table_name := get_table_name(l_day_detail_array(l_dd_count).tabaccess);
659 l_context := l_day_detail_array(l_dd_count).context;
660 l_column := l_day_detail_array(l_dd_count).attribute;
661 maintain_context_details
662 (l_context_details,
663 l_det_bb_id,
664 l_context,
665 l_day_detail_array(l_dd_count).new_entry,
666 l_day_detail_array(l_dd_count).old_entry);
667 IF(instr(upper(l_context),'OTL_ALIAS')>0) THEN
668
669 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);
670 OPEN get_old_det_ovn(l_det_bb_id, l_last_creation_date);
671 FETCH get_old_det_ovn INTO l_old_det_ovn;
672
673 IF(l_last_creation_date IS null) THEN
674 l_day_detail_array(l_dd_count).old_entry := '';
675 ELSE
676 IF (l_old_det_ovn = -1) THEN
677 l_day_detail_array(l_dd_count).old_entry := '';
678 ELSE
679 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);
680 END IF;
681 END IF;
682 CLOSE get_old_det_ovn;
683
684 ELSE
685
686 IF(l_table_name = 'HXC_TIME_ATTRIBUTES') THEN
687 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);
688 ELSIF (l_table_name = 'HXC_TIME_BUILDING_BLOCKS') THEN
689 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);
690 END IF;
691
692
693 IF(l_last_creation_date IS null) THEN
694 l_day_detail_array(l_dd_count).old_entry := '';
695 ELSE
696 OPEN get_old_det_ovn(l_det_bb_id,l_last_creation_date);
697 FETCH get_old_det_ovn INTO l_old_det_ovn;
698
699 IF (l_old_det_ovn = -1) THEN
700 l_day_detail_array(l_dd_count).old_entry := '';
701 ELSE
702 IF(l_table_name = 'HXC_TIME_ATTRIBUTES') THEN
703 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);
704 ELSIF (l_table_name = 'HXC_TIME_BUILDING_BLOCKS') THEN
705 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);
706 END IF;
707 END IF;
708
709 CLOSE get_old_det_ovn;
710
711 END IF;
712 END IF;
713 maintain_context_details
714 (l_context_details,
715 l_det_bb_id,
716 l_context,
717 l_day_detail_array(l_dd_count).new_entry,
718 l_day_detail_array(l_dd_count).old_entry);
719 l_dd_count := l_day_detail_array.next(l_dd_count);
720
721 END LOOP;
722
723 p_day_detail_array := parse_day_details_table(l_day_detail_array, l_context_details);
724
725 END fetch_day_details;
726
727 procedure tokenizer ( iStart IN NUMBER,
728 sPattern in VARCHAR2,
729 sBuffer in VARCHAR2,
730 sResult OUT NOCOPY VARCHAR2,
731 iNextPos OUT NOCOPY NUMBER)
732 AS
733 nPos1 number;
734 nPos2 number;
735 BEGIN
736
737 nPos1 := Instr (sBuffer ,sPattern ,iStart);
738 IF nPos1 = 0 then
739 sResult := NULL ;
740 ELSE
741 nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
742 IF nPos2 = 0 then
743 sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
744 iNextPos := nPos2;
745 else
746 sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
747 iNextPos := nPos2;
748 END IF;
749 END IF;
750 END tokenizer ;
751
752
753 PROCEDURE get_alias_values_from_db
754 (p_bb_id IN NUMBER,
755 p_bb_ovn IN NUMBER,
756 p_layout_comp_id IN NUMBER,
757 p_alias_value_list OUT NOCOPY VARCHAR2
758 )
759 IS
760 TYPE AliasValueCurTyp IS REF CURSOR;
761
762 aliasval_cv AliasValueCurTyp;
763
764 l_select hxc_layout_comp_qualifiers.qualifier_attribute27%type;
765 l_time_building_block_id number;
766 l_time_building_block_ovn number;
767 l_query varchar2(32000);
768 l_bld_blk_info_type hxc_bld_blk_info_types.bld_blk_info_type%type;
769 l_separator varchar2(10);
770 l_position number;
771 l_start_position number;
772
773 p_alias_value varchar2(250);
774 p_alias_name varchar2(250);
775 l_alias_list varchar2(250);
776 l_alias_name varchar2(250);
777 l_dummy_alias_name varchar2(250);
778
779 cursor get_alias_list(p_layout_comp_id in number)
780 is
781 select QUALIFIER_ATTRIBUTE28||'|'||QUALIFIER_ATTRIBUTE28||'|'||QUALIFIER_ATTRIBUTE7
782 from HXC_LAYOUT_COMP_QUALIFIERS where LAYOUT_COMPONENT_ID = p_layout_comp_id ;
783
784 cursor get_alias_location(p_layout_comp_id in number,p_alias_name in varchar2)
785 is
786 SELECT
787 Distinct A.Qualifier_Attribute26,
788 A.Qualifier_Attribute27
789 FROM
790 Hxc_Layout_Comp_Qualifiers A,
791 Hxc_Layout_Components B,
792 Hxc_Layouts C
793 WHERE
794 C.Layout_Id = (Select Layout_Id From Hxc_Layout_Components Where Layout_Component_Id = P_Layout_Comp_Id) And
795 A.Layout_Component_Id = B.Layout_Component_Id And
796 A.Qualifier_Attribute28=p_alias_name;
797
798
799 BEGIN
800
801 open get_alias_list(p_layout_comp_id );
802 fetch get_alias_list into l_alias_list;
803 close get_alias_list;
804
805 l_alias_list := '|'||l_alias_list;
806 l_position:=-1;
807 l_separator := '|';
808 l_start_position := 1;
809
810 while (l_position <> 0)
811 loop
812 --Tokenize twice to get the alias name from alias - cui name pair like
813 --STATE|State|COUNTY|County|CITY|City for work location lov
814 tokenizer (l_start_position ,l_separator,l_alias_list,l_alias_name,l_position);
815 l_start_position := l_position;
816 tokenizer (l_start_position ,l_separator,l_alias_list,l_dummy_alias_name,l_position);
817 l_start_position := l_position;
818
819 open get_alias_location(p_layout_comp_id ,l_alias_name ) ;
820 fetch get_alias_location into l_bld_blk_info_type,l_select;
821 close get_alias_location;
822
823 --Dynamic query to select alias value as get_alias_location gives storing attribute
824 -- and building block info type for each alias name
825 l_query:= 'select ta.'|| l_select;
826 l_query:= l_query || ' from
827 hxc_time_attributes ta,
828 hxc_time_attribute_usages tau,
829 HXC_BLD_BLK_INFO_TYPES bbit
830 where tau.time_building_block_id = :l_time_building_block_id and tau.time_building_block_ovn = :l_time_building_block_ovn
831 and tau.time_attribute_id = ta.time_attribute_id
832 and bbit.bld_blk_info_type_id = ta.bld_blk_info_type_id
833 and bbit.bld_blk_info_type = :l_bld_blk_info_type';
834
835 OPEN aliasval_cv FOR l_query using p_bb_id,p_bb_ovn,l_bld_blk_info_type;
836 FETCH aliasval_cv INTO p_alias_value;
837 close aliasval_cv;
838
839 p_alias_value_list:=p_alias_value_list || p_alias_value ||'*#*';
840 end loop;
841 end get_alias_values_from_db;
842
843 END hxc_inline_notif_utils_pkg;