[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_SEARCH_PKG
Source
1 package body hxc_timecard_search_pkg as
2 /* $Header: hxcserch.pkb 120.1 2006/03/23 02:56:53 gsirigin noship $ */
3
4 TYPE search_record IS RECORD(
5 search_by VARCHAR2(100)
6 ,search_operator VARCHAR2(30)
7 ,search_value VARCHAR2(1000)
8 ,search_connector VARCHAR2(5)
9 );
10
11 TYPE search_table is TABLE OF
12 search_record
13 INDEX BY BINARY_INTEGER;
14
15 TYPE application_period is RECORD(
16 approval_status hxc_time_building_blocks.approval_status%type,
17 start_time hxc_time_building_blocks.start_time%type,
18 stop_time hxc_time_building_blocks.stop_time%type
19 );
20
21 TYPE app_period_table is TABLE of Application_Period index by binary_integer;
22
23 TYPE cur_type is REF CURSOR;
24
25 FUNCTION add_value_to_string(
26 p_string IN VARCHAR2
27 ,p_value IN VARCHAR2
28 )
29 RETURN VARCHAR2
30 IS
31 l_value VARCHAR2(300);
32
33 BEGIN
34 IF p_value IS NULL
35 THEN
36 RETURN p_string;
37 END IF;
38
39 l_value := replace(p_value, '''', '''''');
40
41 IF p_string IS NOT NULL
42 THEN
43 RETURN p_string || ', ''' || l_value || '''';
44 ELSE
45 RETURN '''' || l_value || '''';
46 END IF;
47
48 END add_value_to_string;
49
50 -- get a list of this person's element values so we can eliminate the
51 -- return values from valueset definition when there is an open query
52 FUNCTION get_user_value_list(
53 p_resource_id IN VARCHAR2
54 ,p_field_name IN VARCHAR2
55 ,p_flex_segment IN VARCHAR2
56 ,p_flex_context IN VARCHAR2
57 )
58 RETURN VARCHAR2
59 IS
60 l_prefix VARCHAR2(15) := NULL;
61 l_user_list_sql VARCHAR2(1000) := NULL;
62 l_user_value_list VARCHAR2(32767) := NULL;
63 l_attribute_value VARCHAR2(100) := NULL;
64 l_user_list_cursor cur_type;
65 BEGIN
66
67 l_user_list_sql := 'SELECT distinct hta.'
68 || p_flex_segment
69 || ' FROM hxc_time_building_blocks htbb_detail, '
70 || 'hxc_time_attribute_usages htau, '
71 || 'hxc_time_attributes hta'
72 || ' WHERE htbb_detail.resource_id = '
73 || ':p_resource_id'
74 || ' AND htbb_detail.date_to = hr_general.end_of_time'
75 || ' AND htau.time_building_block_id = htbb_detail.time_building_block_id'
76 || ' AND htau.time_building_block_ovn = htbb_detail.object_version_number'
77 || ' AND htau.time_attribute_id = hta.time_attribute_id'
78 || ' AND hta.attribute_category = '
79 || ':p_flex_context';
80
81 OPEN l_user_list_cursor FOR l_user_list_sql using p_resource_id,p_flex_context;
82 LOOP
83 FETCH l_user_list_cursor INTO l_attribute_value;
84 EXIT WHEN l_user_list_cursor%NOTFOUND;
85
86 l_user_value_list := add_value_to_string(l_user_value_list, l_attribute_value);
87
88 END LOOP;
89 CLOSE l_user_list_cursor;
90
91 IF l_user_value_list IS NULL
92 THEN
93 RETURN NULL;
94 END IF;
95
96 -- process 'DUMMY ELEMENT CONTEXT' and 'DUMMY COST CONTEXT' fields
97 IF p_field_name = 'DUMMY ELEMENT CONTEXT'
98 THEN
99 l_prefix := 'ELEMENT - ';
100 ELSIF p_field_name = 'DUMMY COST CONTEXT'
101 THEN
102 l_prefix := 'COST - ';
103 END IF;
104
105 IF l_prefix IS NOT NULL
106 THEN
107 l_user_value_list := replace(l_user_value_list, l_prefix, '');
108 END IF;
109
110 RETURN l_user_value_list;
111 END get_user_value_list;
112
113
114 -- get_search_attribute
115 --
116 -- procedure
117 -- wrapper package that brings back
118 -- searchable ids from flex fields for various
119 -- search criteria
120 --
121 -- description
122 --
123 -- parameters
124 -- p_flex_search_value - to be searched item
125 -- p_flex_segment - segment of flex
126 -- p_flex_context - context of flex
127 -- p_flex_name - name of flex
128 -- p_application_short_name - short name of application
129 --
130 FUNCTION get_attributes_by_flex(
131 p_flex_search_value IN VARCHAR2
132 ,p_flex_segment IN VARCHAR2
133 ,p_flex_context IN VARCHAR2
134 ,p_flex_name IN VARCHAR2
135 ,p_application_short_name IN VARCHAR2
136 ,p_operator IN VARCHAR2
137 ,p_resource_id IN VARCHAR2
138 ,p_field_name IN VARCHAR2
139 ,p_user_set IN VARCHAR2 DEFAULT 'Y'
140 )
141 RETURN VARCHAR2
142 IS
143 l_flexfield fnd_dflex.dflex_r;
144 l_flexinfo fnd_dflex.dflex_dr;
145 l_contexts fnd_dflex.contexts_dr;
146 i BINARY_INTEGER;
147 j BINARY_INTEGER;
148 l_segments fnd_dflex.segments_dr;
149 l_vset fnd_vset.valueset_r;
150 l_fmt fnd_vset.valueset_dr;
151 l_found BOOLEAN;
152 l_row NUMBER;
153 l_value fnd_vset.value_dr;
154 l_select_st varchar2(10000);
155 l_meaning_column varchar2(300);
156 l_value_column varchar2(300);
157 l_id_column varchar2(300);
158 l_id_string VARCHAR2(300);
159 l_value_string VARCHAR2(300);
160 l_meaning_string VARCHAR2(300);
161 l_like VARCHAR2(5);
162 l_complete_select_st VARCHAR2(32767) := '';
163 l_mapping_code varchar2(1000);
164 l_flag number;
165 l_valcursor cur_type;
166 l_search_value varchar2(100);
167 l_user_value_list VARCHAR2(32767) := NULL;
168
169 l_loop_id number;
170 l_search_meaning varchar2(100);
171 l_return_string VARCHAR2(32767) := '';
172
173 e_null_flex_name exception;
174 e_null_flex_context exception;
175 e_null_flex_segment exception;
176
177
178 BEGIN
179 fnd_msg_pub.initialize;
180
181 if ( p_flex_name = null or p_flex_name = '' )
182 then
183 raise e_null_flex_name ;
184 end if;
185
186 if ( p_flex_segment = null or p_flex_segment = '' )
187 then
188 raise e_null_flex_segment;
189 end if;
190
191 if ( p_flex_context = null or p_flex_context = '') then
192 raise e_null_flex_context ;
193 end if;
194
195
196 -- Get the flex field
197 fnd_dflex.get_flexfield(
198 p_application_short_name,
199 p_flex_name,
200 l_flexfield,
201 l_flexinfo
202 );
203
204
205 -- Get the contexts for the flex field
206 fnd_dflex.get_contexts(l_flexfield, l_contexts);
207
208 -- Loop for all contexts
209 FOR i IN 1 .. l_contexts.ncontexts LOOP
210 fnd_dflex.get_segments(
211 fnd_dflex.make_context(
212 l_flexfield,
213 l_contexts.context_code(i)
214 ),
215 l_segments,
216 TRUE
217 );
218
219 -- Check if Context is equal to given context
220
221 IF (l_contexts.context_code(i) = p_flex_context)
222 THEN
223 -- Loop through all segments
224 FOR j IN 1 .. l_segments.nsegments LOOP
225
226 -- Check if Segment is Equal to given segment
227 IF ( l_segments.application_column_name(j) = p_flex_segment )
228 THEN
229 IF (l_segments.value_set(j) is not null)
230 THEN
231 fnd_vset.get_valueset(l_segments.value_set(j), l_vset, l_fmt);
232
233 -- Get select statement for value set
234 fnd_flex_val_api.get_table_vset_select(
235 p_value_set_id => l_vset.vsid,
236 p_check_enabled_flag => 'N',
237 p_check_validation_date => 'N',
238 p_inc_addtl_where_clause => 'N',
239 x_select => l_select_st,
240 x_mapping_code => l_mapping_code,
241 x_success =>l_flag
242 );
243
244 IF (l_select_st is not null)
245 THEN
246 -- Append where clause for meaning
247 l_meaning_column := l_vset.table_info.meaning_column_name;
248 l_value_column := l_vset.table_info.value_column_name;
249 l_id_column := l_vset.table_info.id_column_name;
250
251 -- Add legislation and business_group checks
252 l_complete_select_st :=
253 l_select_st;
254
255 IF instr(p_operator, 'LIKE') = 0
256 THEN
257 l_like := '';
258 ELSE
259 l_like := '%';
260 END IF;
261
262 IF (l_meaning_column is not null)
263 THEN
264 l_complete_select_st :=
265 l_complete_select_st ||
266 ' and (' ||
267 l_value_column ||
268 ' ' ||
269 p_operator ||
270 '''' ||
271 l_like ||
272 p_flex_search_value ||
273 l_like ||
274 '''' ||
275 ' OR ' ||
276 l_meaning_column ||
277 ' ' ||
278 p_operator ||
279 '''' ||
280 l_like ||
281 p_flex_search_value ||
282 l_like ||
283 ''')';
284 ELSE
285 l_complete_select_st :=
286 l_complete_select_st ||
287 ' and (' ||
288 l_value_column ||
289 ' ' ||
290 p_operator ||
291 '''' ||
292 l_like ||
293 p_flex_search_value ||
294 l_like ||
295 ''')';
296 END IF;
297
298 IF p_user_set = 'Y'
299 THEN
300 l_user_value_list :=
301 get_user_value_list(
302 p_resource_id => p_resource_id
303 ,p_field_name => UPPER(p_field_name)
304 ,p_flex_segment => p_flex_segment
305 ,p_flex_context => p_flex_context
306 );
307 END IF;
308
309 IF l_user_value_list IS NOT NULL
310 THEN
311 IF l_id_column IS NOT NULL
312 THEN
313 l_complete_select_st :=
314 l_complete_select_st ||
315 ' AND ' ||
316 l_id_column ||
317 ' IN (' ||
318 l_user_value_list ||
319 ')';
320 ELSE
321 l_complete_select_st :=
322 l_complete_select_st ||
323 ' AND ' ||
324 l_value_column ||
325 ' IN (' ||
326 l_user_value_list ||
327 ')';
328 END IF;
329 END IF;
330
331 IF l_id_column IS NOT NULL
332 AND l_meaning_column IS NOT NULL
333 THEN
334 BEGIN
335 OPEN l_valcursor FOR l_complete_select_st;
336 LOOP
337 FETCH l_valcursor INTO l_value_string , l_id_string, l_meaning_string;
338 EXIT WHEN l_valcursor%NOTFOUND;
339
340 l_return_string := add_value_to_string(l_return_string, l_id_string);
341
342 END LOOP;
343 --Bug 4259255. Added exception handling for corrupt data in hxc_time_attributes table.
344 --Change for version 115.46
345 exception
346 WHEN INVALID_NUMBER THEN
347 fnd_message.set_name('HXC', 'HXC_INVALID_PROJECT_ID');
348 FND_MSG_PUB.ADD;
349
350 END;
351 CLOSE l_valcursor;
352
353 ELSIF l_id_column IS NOT NULL
354 AND l_meaning_column IS NULL
355 THEN
356 OPEN l_valcursor FOR l_complete_select_st;
357 LOOP
358 FETCH l_valcursor INTO l_value_string , l_id_string;
359 EXIT WHEN l_valcursor%NOTFOUND;
360
361 l_return_string := add_value_to_string(l_return_string, l_id_string);
362
363 END LOOP;
364
365 CLOSE l_valcursor;
366
367 ELSIF l_id_column IS NULL
368 AND l_meaning_column IS NOT NULL
369 THEN
370 OPEN l_valcursor FOR l_complete_select_st;
371 LOOP
372 FETCH l_valcursor INTO l_value_string, l_meaning_string;
373 EXIT WHEN l_valcursor%NOTFOUND;
374
375
376 l_return_string := add_value_to_string(l_return_string, l_value_string);
377
378 END LOOP;
379 CLOSE l_valcursor;
380
381 ELSIF l_id_column IS NULL
382 AND l_meaning_column IS NULL
383 THEN
384 open l_valcursor for l_complete_select_st;
385 LOOP
386 FETCH l_valcursor INTO l_value_string;
387 EXIT WHEN l_valcursor%NOTFOUND;
388
389 l_return_string := add_value_to_string(l_return_string, l_value_string);
390
391 END LOOP;
392 CLOSE l_valcursor;
393 END IF;
394
395 IF l_return_string IS NULL
396 THEN
397 l_return_string := ''''||'-1'||''''; --Fix for Bug#3362876
398 END IF;
399
400 RETURN l_return_string;
401 END IF;
402
403 ELSE
404 RETURN(hxc_timecard_search_pkg.c_no_valueset_attached); --if value set not attached to a segment
405 END IF;
406
407 END IF;
408 END LOOP;
409 END IF;
410 END LOOP;
411
412 RETURN (''''||'-1'||''''); --Fix for Bug#3362876
413 exception
414 when e_null_flex_name then
415 fnd_message.set_name('HXC', 'HXC_FLEX_NAME_NULL');
416 fnd_message.raise_error;
417
418
419 when e_null_flex_context then
420 fnd_message.set_name('HXC', 'HXC_FLEX_CONTEXT_NULL');
421 fnd_message.raise_error;
422
423 when e_null_flex_segment then
424 fnd_message.set_name('HXC', 'HXC_FLEX_SEGMENT_NULL');
425 fnd_message.raise_error;
426
427 when others then
428 -- fnd_message.set_name('HXC', 'HXC_FLEX_CANNOT_BE_SEARCHED');
429 -- fnd_message.raise_error;
430 raise;
431
432 END get_attributes_by_flex;
433
434
435 --
436 -- function get_timecard_status_meaning
437 --
438 --
442 --
439 -- description Calculates the status of a timecard after looking
440 -- into the status of application periods within or
441 -- surrounding the timecard.
443 -- parameters
444 -- bb_id - Building block id of timecard
445 -- bb_ovn - Building block ovn of the timecard
446 --
447 -- returns Status of timecard
448 --
449
450 FUNCTION get_timecard_status_meaning(bb_id number, bb_ovn number)
451 return varchar2
452 is
453 l_status_code varchar2(100);
454 l_status_meaning varchar2(100);
455 begin
456
457 l_status_code := get_timecard_status_code(bb_id,bb_ovn);
458
459 -- Bug 2486271; Changed hr_general.decode_lookup to hr_bis.bis_decode_lookup.
460 select hr_general.decode_lookup('HXC_APPROVAL_STATUS',l_status_code) into l_status_meaning
461 from dual;
462 -- select hr_bis.bis_decode_lookup('HXC_APPROVAL_STATUS',l_status_code) into l_status_meaning
463 -- from dual;
464
465 return l_status_meaning;
466
467
468 end get_timecard_status_meaning;
469
470 -- Start Changes 115.35
471 --
472 -- function get_timecard_cla_status
473 --
474 --
475 -- description Calculates the status of a timecard after looking
476 -- into the associated attributes to find if any
477 -- Change or Late Audit reasons are associated with
478 -- with the timecard.
479 --
480 -- parameters
481 -- bb_id - Building block id of timecard
482 -- bb_ovn - Building block ovn of the timecard
483 --
484 -- returns CLA Status of timecard
485 --
486
487 FUNCTION get_timecard_cla_status(bb_id number, bb_ovn number)
488 return varchar2
489 is
490 cursor csr_get_cla_status
491 (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
492 ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
493 ) is
494 SELECT 'Yes'
495 FROM DUAL
496 WHERE EXISTS ( SELECT '1'
497 FROM hxc_time_building_blocks days,
498 hxc_time_building_blocks details,
499 hxc_time_attribute_usages tau,
500 hxc_time_attributes ta
501 WHERE tau.time_building_block_id =
502 details.time_building_block_id
503 AND tau.time_building_block_ovn =
504 details.object_version_number
505 AND tau.time_attribute_id = ta.time_attribute_id
506 AND ta.attribute_category = 'REASON'
507 AND details.scope = 'DETAIL'
508 -- AND details.date_to = hr_general.end_of_time
509 AND details.parent_building_block_id =
510 days.time_building_block_id
511 -- AND details.parent_building_block_ovn =
512 -- days.object_version_number
513 AND days.scope = 'DAY'
514 -- AND days.date_to = hr_general.end_of_time
515 AND days.parent_building_block_id = p_timecard_id
516 AND days.parent_building_block_ovn = p_timecard_ovn);
517
518 l_cla_status varchar2(3);
519
520 BEGIN
521
522 open csr_get_cla_status(bb_id,bb_ovn);
523 fetch csr_get_cla_status into l_cla_status;
524
525 IF csr_get_cla_status%NOTFOUND then
526 l_cla_status := 'No';
527 END IF;
528
529 close csr_get_cla_status;
530
531
532 RETURN l_cla_status;
533 END get_timecard_cla_status;
534
535 -- End Changes 115.35
536
537
538
539 --
540 -- function get_timecard_status_code
541 --
542 --
543 -- description Calculates the status (Menaing) of a timecard after looking
544 -- into the status of application periods within or
545 -- surrounding the timecard.
546 --
547 -- parameters
548 -- bb_id - Building block id of timecard
549 -- bb_ovn - Building block ovn of the timecard
550 --
551 -- returns Status of timecard
552 --
553
554 FUNCTION get_timecard_status_code(bb_id number, bb_ovn number)
555 return varchar2
556 is
557
558 cursor c_timecard_status
559 (p_timecard_id in hxc_timecard_summary.timecard_id%type,
560 p_timecard_ovn in hxc_timecard_summary.timecard_ovn%type) is
561 select approval_status
562 from hxc_timecard_summary
563 where timecard_id = p_timecard_id
564 and timecard_ovn = p_timecard_ovn;
565
566 cursor c_timecard_no_ovn_status
567 (p_timecard_id in hxc_timecard_summary.timecard_id%type) is
568 select approval_status
569 from hxc_timecard_summary
570 where timecard_id = p_timecard_id;
571
572 cursor c_last_timecard_status
573 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
574 select approval_status
575 from hxc_time_building_blocks tbb1
576 where tbb1.time_building_block_id = p_timecard_id
577 and tbb1.object_version_number =
578 (select max(tbb2.object_version_number)
579 from hxc_time_building_blocks tbb2
583 l_tcstatus hxc_timecard_summary.approval_status%type;
580 where tbb2.time_building_block_Id = tbb1.time_building_block_id
581 );
582
584
585 BEGIN
586
587 open c_timecard_status(bb_id,bb_ovn);
588 fetch c_timecard_status into l_tcstatus;
589 if (c_timecard_status%notfound) then
590 close c_timecard_status;
591 /**
592 * If we get here it means the CBO has chosen a plan that is evaluating the
593 * status of a timecard, which has a lower OVN than the one currently
594 * summarized. Thus, simply look for the status of the timecard in the
595 * summary table, since this will be the valid last status.
596 */
597 open c_timecard_no_ovn_status(bb_id);
598 fetch c_timecard_no_ovn_status into l_tcstatus;
599 if(c_timecard_no_ovn_status%notfound) then
600 close c_timecard_no_ovn_status;
601 /**
602 * If we get here, it means the CBO has chosen a plan that is evaluating
603 * a timecard status for a deleted timecard. The view will ultimately
604 * filter out the timecard, so we can simply return the last status
605 * when the timecard was deleted without fear.
606 */
607 open c_last_timecard_status(bb_id);
608 fetch c_last_timecard_status into l_tcstatus;
609 if(c_last_timecard_status%notfound) then
610 close c_last_timecard_status;
611 /**
612 * If we get here, it means the building block id did not exist
613 * in the time building blocks table (partioned?), and we should error.
614 */
615 fnd_message.set_name('HXC','HXC_APR_NO_TIMECARD_INFO');
616 fnd_message.set_token('TIMECARD_ID',to_char(bb_id));
617 fnd_message.raise_error;
618 else
619 close c_last_timecard_status;
620 end if;
621 else
622 close c_timecard_no_ovn_status;
623 end if;
624 else
625 close c_timecard_status;
626 end if;
627
628 return l_tcstatus;
629
630 END get_timecard_status_code;
631
632
633
634 --
635 -- overloaded function get_timecard_status_code
636 --
637 --
638 -- description Calculates the status (Meaning) of a timecard after looking
639 -- into the status of application periods within or
640 -- surrounding the timecard.
641 --
642 -- parameters
643 -- bb_id - Building block id of timecard
644 -- bb_ovn - Building block ovn of the timecard
645 -- p_mode - Migration mode or normal mode.
646 -- - in case of normal mode, the overloaded version
647 -- - given above will be used.
648 --
649 -- returns Status of timecard
650 --
651
652 FUNCTION get_timecard_status_code(bb_id number, bb_ovn number, p_mode varchar2)
653 return varchar2
654 is
655
656 cursor c_get_approval_status
657 (p_tc_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
658 ,p_tc_stop_time in HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
659 ,p_resource_id in HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
660 ,p_approval_after in HXC_TIME_BUILDING_BLOCKS.CREATION_DATE%TYPE) is
661 select
662 hap.approval_status
663 ,hap.start_time
664 ,hap.stop_time
665 from
666 hxc_time_building_blocks hap
667 where
668 hap.scope = 'APPLICATION_PERIOD'
669 and hap.type = 'RANGE'
670 and hap.date_to = hr_general.end_of_time
671 and hap.resource_type = 'PERSON'
672 and hap.start_time <= p_tc_stop_time
673 and hap.stop_time >= p_tc_start_time
674 and hap.resource_id = p_resource_id
675 and hap.creation_date >= p_approval_after;
676
677 cursor csr_get_latest_block
678 (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
679 ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
680 ) is
681 select max(details.creation_date)
682 from hxc_time_building_blocks details
683 ,hxc_time_building_blocks days
684 where days.parent_building_block_id = p_timecard_id
685 and days.parent_building_block_ovn = p_timecard_ovn
686 and days.scope = 'DAY'
687 and days.date_to = hr_general.end_of_time
688 and details.parent_building_block_id = days.time_building_block_id
689 and details.parent_building_block_ovn = days.object_version_number
690 and details.date_to = hr_general.end_of_time
691 and details.scope = 'DETAIL';
692
693 cursor csr_get_latest_day
694 (p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
695 ,p_timecard_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
696 ) is
697 select max(creation_date)
698 from hxc_time_building_blocks
699 where parent_building_block_id = p_timecard_id
700 and parent_building_block_ovn = p_timecard_ovn
701 and scope = 'DAY'
702 and date_to = hr_general.end_of_time;
703
704 l_app_periods app_period_table;
705
706 l_count number;
707 l_start_time date;
708 l_stop_time date;
709 l_resource_id number;
710 l_tcstatus varchar2(1000);
711 l_submitted varchar2(1000);
712 l_latest_block_date DATE;
713 l_day_count number;
717 l_day_set BOOLEAN;
714 l_day_number number;
715 l_day DATE;
716 l_day_status hxc_time_building_blocks.approval_status%type;
718 l_app_period_count number;
719
720 BEGIN
721
722 if p_mode = hxc_timecard_summary_pkg.c_normal_mode or p_mode is null then
723
724 return get_timecard_status_code(bb_id, bb_ovn);
725
726 elsif p_mode = hxc_timecard_summary_pkg.c_migration_mode then
727 l_submitted := 'false';
728
729 -- Original Timecard Status
730 select approval_status, start_time, stop_time, resource_id into l_tcstatus, l_start_time, l_stop_time, l_resource_id
731 from hxc_time_building_blocks
732 where time_building_block_id = bb_id
733 and object_version_number = bb_ovn;
734
735 IF l_tcstatus = 'WORKING' or l_tcstatus = 'ERROR'
736 THEN
737 RETURN l_tcstatus;
738 END IF;
739
740 open csr_get_latest_block(bb_id,bb_ovn);
741 fetch csr_get_latest_block into l_latest_block_date;
742 close csr_get_latest_block;
743
744 if(l_latest_block_date is null) then
745 open csr_get_latest_day(bb_id,bb_ovn);
746 fetch csr_get_latest_day into l_latest_block_date;
747 close csr_get_latest_day;
748 end if;
749 --
750 -- Ok, now check the application period building blocks if there
751 -- are any
752 --
753
754 l_count := 0;
755
756 FOR apr_rec in c_get_approval_status(l_start_time, l_stop_time, l_resource_id, l_latest_block_date) LOOP
757
758 l_count:= l_count +1;
759
760 if(apr_rec.approval_status = 'REJECTED') then
761
762 l_tcstatus := apr_rec.approval_status;
763
764 elsif (apr_rec.approval_status = 'SUBMITTED') then
765
766 if(l_tcstatus = 'APPROVED') then
767
768 l_tcstatus := apr_rec.approval_status;
769
770 end if;
771
772 elsif (apr_rec.approval_status = 'APPROVED') then
773
774 if(l_count=1) then
775
776 l_tcstatus := apr_rec.approval_status;
777
778 end if;
779
780 end if;
781
782 l_app_periods(l_count).approval_status := apr_rec.approval_status;
783 l_app_periods(l_count).start_time := apr_rec.start_time;
784 l_app_periods(l_count).stop_time := apr_rec.stop_time;
785
786 END LOOP;
787
788
789 if(l_tcstatus = 'APPROVED') then
790
791 l_day_number := trunc(l_stop_time) - trunc(l_start_time);
792
793 l_day_count := 0;
794
795 while (l_day_count < (l_day_number+1)) LOOP
796
797 l_day := l_start_time + l_day_count;
798 l_day_status := 'SUBMITTED';
799 l_day_set := FALSE;
800
801 for l_app_period_count in l_app_periods.first..l_app_periods.last loop
802
803 if(l_day between l_app_periods(l_app_period_count).start_time
804 and l_app_periods(l_app_period_count).stop_time) then
805
806 if(NOT l_day_set) then
807 l_day_set := TRUE;
808 l_day_status := l_app_periods(l_app_period_count).approval_status;
809 else
810 if (l_app_periods(l_app_period_count).approval_status = 'REJECTED') then
811 l_day_status := 'REJECTED';
812 elsif (l_app_periods(l_app_period_count).approval_status = 'SUBMITTED') then
813 if (l_day_status <> 'REJECTED') then
814 l_day_status := l_app_periods(l_app_period_count).approval_status;
815 end if;
816 end if;
817 end if;
818 end if;
819
820 exit when (l_day_status = 'REJECTED');
821
822 end loop;
823
824 exit when (l_day_status <> 'APPROVED');
825
826 l_day_count := l_day_count +1;
827
828 end loop;
829
830 if(l_day_status <> l_tcstatus) then
831 l_tcstatus := l_day_status;
832 end if;
833
834 end if;
835
836 return l_tcstatus;
837
838 end if;
839
840 exception
841
842 when others then
843 fnd_message.set_name('HXC', 'HXC_ERROR_FINDING_TIMECARD_STATUS');
844 fnd_message.raise_error;
845
846
847 END get_timecard_status_code;
848
849
850
851 --helper function
852 FUNCTION get_value_from_string(
853 p_string IN VARCHAR2
854 ,p_value_index IN NUMBER
855 )
856 RETURN VARCHAR2
857 IS
858 l_separator VARCHAR2(1) := '|';
859 l_value VARCHAR2(2000);
860
861 BEGIN
862
863 IF (INSTR(p_string, l_separator, 1, p_value_index+1) = 0) THEN
864 --
865 -- We need to send back the very last thing in the string, i.e.
866 -- everything from the final g_separator.
867 --
868 l_value := SUBSTR(p_string,(INSTR(p_string, l_separator,1,p_value_index)+1));
869 ELSE
870
871
872 l_value := SUBSTR(p_string
873 ,(INSTR(p_string,l_separator,1,p_value_index)+1)
874 ,((INSTR(p_string,l_separator,1,(p_value_index+1))-1)
875 -INSTR(p_string,l_separator,1,p_value_index))
876 );
877 END IF;
878
879 RETURN l_value;
880
881 END get_value_from_string;
882
883 FUNCTION get_attributes_by_alias(
887 )
884 p_alias_definition_id IN hxc_alias_values.alias_definition_id%TYPE
885 ,p_search_operator IN VARCHAR2
886 ,p_search_value IN VARCHAR2
888 RETURN VARCHAR2
889 IS
890 l_alias_sql VARCHAR2(300);
891 l_like_string VARCHAR2(1);
892 l_return_string VARCHAR2(32767) := '';
893 l_value_string VARCHAR2(300);
894 type cur_type is REF CURSOR;
895 l_cursor cur_type;
896 BEGIN
897 IF INSTR(p_search_operator, 'LIKE') > 0
898 THEN
899 l_like_string := '%';
900 ELSE
901 l_like_string := '';
902 END IF;
903
904 l_alias_sql := 'SELECT attribute1'
905 || ' FROM hxc_alias_values_v'
906 ||' WHERE alias_definition_id = :p_alias_definition_id'
907 || ' AND alias_value_name '
908 || p_search_operator
909 || ':l_like_string||:p_search_value||:l_like_string';
910
911 OPEN l_cursor for l_alias_sql using p_alias_definition_id,l_like_string,p_search_value,l_like_string;
912 LOOP
913 FETCH l_cursor INTO l_value_string;
914 EXIT WHEN l_cursor%NOTFOUND;
915
916 l_return_string := add_value_to_string(l_return_string, l_value_string);
917
918 END LOOP;
919 CLOSE l_cursor;
920
921 IF l_return_string IS NULL
922 THEN
923 l_return_string := '-1';
924 END IF;
925
926 RETURN l_return_string;
927 END get_attributes_by_alias;
928
929
930
931 FUNCTION get_attributes(
932 p_search_by IN VARCHAR2
933 ,p_search_value IN VARCHAR2
934 ,p_flex_segment IN VARCHAR2
935 ,p_flex_context IN VARCHAR2
936 ,p_flex_name IN VARCHAR2
937 ,p_application_short_name IN VARCHAR2
938 ,p_operator IN VARCHAR2
939 ,p_resource_id IN VARCHAR2
940 ,p_field_name IN VARCHAR2
941 ,p_user_set IN VARCHAR2 DEFAULT 'Y'
942 )
943 RETURN VARCHAR2
944 IS
945 l_alias_definition_id hxc_alias_values.alias_definition_id%TYPE;
946 l_alias_used BOOLEAN := FALSE;
947 l_field_name hxc_alias_definitions.timecard_field%TYPE;
948
949 CURSOR c_field_name(
950 p_alias_definition_id hxc_alias_values.alias_definition_id%TYPE
951 )
952 IS
953 SELECT timecard_field
954 FROM hxc_alias_definitions
955 WHERE alias_definition_id = p_alias_definition_id;
956
957 BEGIN
958 IF p_search_by = 'DUMMY ELEMENT CONTEXT'
959 THEN
960 l_alias_definition_id := hxc_preference_evaluation.resource_preferences(
961 p_resource_id,
962 'TC_W_TCRD_ALIASES',
963 1);
964
965 OPEN c_field_name (
966 p_alias_definition_id => l_alias_definition_id
967 );
968
969 FETCH c_field_name INTO l_field_name;
970 IF c_field_name%NOTFOUND
971 THEN
972 CLOSE c_field_name;
973 ELSE
974 CLOSE c_field_name;
975 IF l_field_name = 'Hours Type'
976 THEN
977 l_alias_used := TRUE;
978 END IF;
979 END IF;
980 END IF;
981
982 IF l_alias_used
983 THEN
984
985 RETURN get_attributes_by_alias(
986 p_alias_definition_id => l_alias_definition_id
987 ,p_search_operator => p_operator
988 ,p_search_value => p_search_value
989 );
990 ELSE
991 RETURN get_attributes_by_flex(
992 p_flex_search_value => p_search_value
993 ,p_flex_segment => p_flex_segment
994 ,p_flex_context => p_flex_context
995 ,p_flex_name => p_flex_name
996 ,p_application_short_name => p_application_short_name
997 ,p_operator => p_operator
998 ,p_resource_id => p_resource_id
999 ,p_field_name => p_field_name
1000 ,p_user_set => p_user_set
1001 );
1002 END IF;
1003
1004 END get_attributes;
1005
1006
1007 -- =========================================================================
1008 -- This procedure builds a complete sql for advanced search. Java code
1009 -- calls this routine to build the VO.
1010 -- =========================================================================
1011
1012 PROCEDURE get_search_sql(
1013 p_resource_id IN VARCHAR2
1014 ,p_search_start_time IN VARCHAR2
1015 ,p_search_stop_time IN VARCHAR2
1016 ,p_search_rows IN VARCHAR2
1017 ,p_search_input_string IN VARCHAR2
1018 ,p_result OUT NOCOPY VARCHAR2
1019 )
1020 IS
1021 l_search_table search_table;
1022 l_table_index NUMBER:= 1;
1023 l_value_index NUMBER:= 1;
1024 l_search_rows NUMBER := TO_NUMBER(p_search_rows);
1025 l_search_by VARCHAR2(100);
1026 l_search_operator VARCHAR2(30);
1027 l_search_value VARCHAR2(1000);
1028 l_search_connector VARCHAR2(5);
1029 l_detail_join_flag BOOLEAN := FALSE;
1030 l_attribute_flag BOOLEAN := FALSE;
1031 l_like_string VARCHAR2(1);
1032 l_context VARCHAR2(100);
1036 l_sql_select VARCHAR2(1000);
1033 l_segment VARCHAR2(100);
1034 l_bld_blk_info_type_id NUMBER;
1035 l_prefix VARCHAR2(50);
1037 l_sql_from VARCHAR2(1000);
1038 l_flex_search_value VARCHAR2(32767);
1039 l_sql_where VARCHAR2(32767);
1040 l_additional_where VARCHAR2(32767);
1041 l_one_where VARCHAR2(32767);
1042 l_complete_sql VARCHAR2(32767);
1043 l_result VARCHAR2(32767);
1044 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1045 l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1046 l_status_code VARCHAR(30);
1047 l_status_meaning VARCHAR(100);
1048 l_period_starts hxc_time_building_blocks.start_time%TYPE;
1049 l_period_ends hxc_time_building_blocks.stop_time%TYPE;
1050 l_hours_worked NUMBER;
1051 l_submission_date DATE;
1052 c_sql cur_type;
1053
1054
1055 CURSOR c_mapping_segment(
1056 p_field_name VARCHAR2
1057 )
1058 IS
1059 SELECT context, segment, bld_blk_info_type_id
1060 FROM hxc_mapping_attributes_v
1061 WHERE map = 'OTL Deposit Process Mapping'
1062 AND upper(field_name) = upper(p_field_name);
1063
1064 BEGIN
1065
1066 -- put values in table
1067
1068 FOR l_table_index IN 1..l_search_rows LOOP
1069 l_search_table(l_table_index).search_by
1070 := get_value_from_string(p_search_input_string, l_value_index);
1071 l_value_index := l_value_index + 1;
1072
1073 l_search_table(l_table_index).search_operator
1074 := get_value_from_string(p_search_input_string, l_value_index);
1075 l_value_index := l_value_index + 1;
1076
1077
1078 l_search_table(l_table_index).search_value
1079 := get_value_from_string(p_search_input_string, l_value_index);
1080 l_value_index := l_value_index + 1;
1081
1082 l_search_table(l_table_index).search_connector
1083 := get_value_from_string(p_search_input_string, l_value_index);
1084 l_value_index := l_value_index + 1;
1085
1086 END LOOP;
1087
1088
1089 l_sql_select := 'SELECT distinct hrt.timecard_id'
1090 || ' ,hrt.timecard_ovn'
1091 || ' ,hxc_timecard_search_pkg.get_timecard_status_code('
1092 || ' hrt.timecard_id, '
1093 || ' hrt.timecard_ovn'
1094 || ' ) as status_code'
1095 || ' ,hxc_timecard_search_pkg.get_timecard_status_meaning('
1096 || ' hrt.timecard_id, '
1097 || ' hrt.timecard_ovn'
1098 || ' ) as status_name'
1099 || ' ,hrt.period_starts'
1100 || ' ,hrt.period_ends'
1101 || ' ,hrt.hours_worked'
1102 || ' ,hrt.submission_date';
1103
1104 l_sql_from := 'FROM hxc_resource_timecards_v hrt';
1105
1106 l_sql_where := 'WHERE hrt.resource_id = ' || p_resource_id;
1107
1108 IF p_search_start_time IS NOT NULL
1109 THEN
1110 l_sql_where := l_sql_where
1111 || ' AND TRUNC(hrt.period_starts) >= TO_DATE('
1112 || ''''
1113 || p_search_start_time
1114 || ''''
1115 || ', ''RRRR/MM/DD'')';
1116 END IF;
1117
1118 IF p_search_stop_time IS NOT NULL
1119 THEN
1120 l_sql_where := l_sql_where
1121 || ' AND TRUNC(hrt.period_ends) <= TO_DATE('
1122 || ''''
1123 || p_search_stop_time
1124 || ''''
1125 || ', ''RRRR/MM/DD'')';
1126 END IF;
1127
1128
1129 l_additional_where := NULL;
1130
1131 FOR l_index IN 1..l_search_rows LOOP
1132 l_search_by := l_search_table(l_index).search_by;
1133 l_search_operator := l_search_table(l_index).search_operator;
1134 l_search_value := l_search_table(l_index).search_value;
1135 l_search_connector := l_search_table(l_index).search_connector;
1136
1137 IF instr(l_search_operator, 'LIKE') <> 0
1138 THEN
1139 l_like_string := '%';
1140 ELSE
1141 l_like_string := '';
1142 END IF;
1143
1144 IF l_search_by = 'SUBMISSION_DATE'
1145 OR l_search_by = 'PERIOD_ENDS'
1146 OR l_search_by = 'PERIOD_STARTS'
1147 THEN
1148 l_one_where := 'TRUNC(hrt.'
1149 || l_search_by
1150 || ') '
1151 || l_search_operator
1152 || ' TO_DATE('
1153 || ''''
1154 || l_search_value
1155 || ''''
1156 || ', ''RRRR/MM/DD'')';
1157
1158 ELSIF l_search_by = 'TIMECARD_COMMENT'
1159 THEN
1160 l_one_where := 'NVL(hrt.timecard_comment, '' '') '
1161 || l_search_operator
1162 || ''''
1163 || l_like_string
1164 || l_search_value
1165 || l_like_string
1166 || '''';
1167 ELSIF l_search_by = 'DETAIL_COMMENT'
1168 THEN
1169 IF NOT l_detail_join_flag
1170 THEN
1171 l_sql_from := l_sql_from
1175
1172 || ' ,hxc_time_building_blocks htbb_day'
1173 || ' ,hxc_time_building_blocks htbb_detail';
1174
1176 l_sql_where := l_sql_where
1177 || ' AND htbb_day.parent_building_block_id = hrt.timecard_id'
1178 || ' AND htbb_day.parent_building_block_ovn = hrt.timecard_ovn'
1179 || ' AND htbb_day.date_to = hr_general.end_of_time'
1180 || ' AND htbb_detail.parent_building_block_id = htbb_day.time_building_block_id'
1181 || ' AND htbb_detail.parent_building_block_ovn = htbb_day.object_version_number'
1182 || ' AND htbb_detail.date_to = hr_general.end_of_time';
1183
1184
1185 l_detail_join_flag := TRUE;
1186 END IF;
1187
1188 l_one_where := 'NVL(htbb_detail.comment_text, '' '') '
1189 || l_search_operator
1190 || ' '''
1191 || l_like_string
1192 || l_search_value
1193 || l_like_string
1194 || '''';
1195 ELSIF l_search_by = 'STATUS_CODE'
1196 THEN
1197 l_one_where := 'hrt.status_name ' -- should modify hxc_resource_timecards_v
1198 || l_search_operator
1199 || ' '''
1200 || l_like_string
1201 || l_search_value
1202 || l_like_string
1203 || '''';
1204 ELSIF l_search_by = 'HOURS_WORKED'
1205 THEN
1206 l_one_where := 'hrt.'
1207 || l_search_by
1208 || ' '
1209 || l_search_operator
1210 || ' '''
1211 || l_like_string
1212 || l_search_value
1213 || l_like_string
1214 || '''';
1215 ELSE
1216 -- attribute search
1217 OPEN c_mapping_segment(l_search_by);
1218 FETCH c_mapping_segment INTO l_context, l_segment, l_bld_blk_info_type_id;
1219
1220 IF c_mapping_segment%NOTFOUND
1221 THEN
1222 CLOSE c_mapping_segment;
1223
1224 FND_MESSAGE.set_name('HXC','HXC_NO_MAPPING_COMPONENT');
1225 FND_MESSAGE.RAISE_ERROR;
1226 END IF;
1227
1228 CLOSE c_mapping_segment;
1229
1230
1231 l_flex_search_value :=
1232 get_attributes(
1233 p_search_by => l_search_by
1234 ,p_search_value => l_search_value
1235 ,p_flex_segment => l_segment
1236 ,p_flex_context => l_context
1237 ,p_flex_name => 'OTC Information Types'
1238 ,p_application_short_name => 'HXC'
1239 ,p_operator => l_search_operator
1240 ,p_resource_id => p_resource_id
1241 ,p_field_name => l_search_by
1242 );
1243
1244 IF NOT l_attribute_flag
1245 THEN
1246 l_sql_from := l_sql_from
1247 || ' ,hxc_time_attribute_usages htau'
1248 || ' ,hxc_time_attributes hta';
1249
1250 IF NOT l_detail_join_flag
1251 THEN
1252 l_sql_from := l_sql_from
1253 || ' ,hxc_time_building_blocks htbb_day'
1254 || ' ,hxc_time_building_blocks htbb_detail';
1255
1256
1257 l_sql_where := l_sql_where
1258 || ' AND htbb_day.parent_building_block_id = hrt.timecard_id'
1259 || ' AND htbb_day.parent_building_block_ovn = hrt.timecard_ovn'
1260 || ' AND htbb_day.date_to = hr_general.end_of_time'
1261 || ' AND htbb_detail.parent_building_block_id = htbb_day.time_building_block_id'
1262 || ' AND htbb_detail.parent_building_block_ovn = htbb_day.object_version_number'
1263 || ' AND htbb_detail.date_to = hr_general.end_of_time';
1264
1265
1266 l_detail_join_flag := TRUE;
1267 END IF;
1268
1269 l_sql_where := l_sql_where
1270 || ' AND htau.time_building_block_id = htbb_detail.time_building_block_id'
1271 || ' AND htau.time_building_block_ovn = htbb_detail.object_version_number'
1272 || ' AND htau.time_attribute_id = hta.time_attribute_id';
1273
1274 l_attribute_flag := TRUE;
1275 END IF;
1276
1277 --for DUMMY ELEMENT CONTEXT, the stored values are 'ELEMENT - id',
1278 --for DUMMY COST CONTEXT, the stored values are 'COST - id',
1279 --we should extract the ids from them.
1280
1281 l_search_by := UPPER(l_search_by);
1282
1283 IF l_search_by = 'DUMMY ELEMENT CONTEXT'
1284 THEN
1285 l_prefix := 'ELEMENT - ';
1286 ELSIF l_search_by = 'DUMMY COST CONTEXT'
1287 THEN
1288 l_prefix := 'COST - ';
1289 END IF;
1290
1291 IF l_prefix IS NOT NULL
1292 THEN
1293 /*
1294 l_one_where := ' hta.attribute_category = '
1295 || ''''
1296 || l_context
1297 || ''''
1298 || ' AND SUBSTR(hta.'
1299 || l_segment
1300 || ', LENGTH('''
1301 || l_prefix
1305 */
1302 || ''') + 1) IN ('
1303 || l_flex_search_value
1304 || ')';
1306 l_one_where := ' hta.bld_blk_info_type_id = '
1307 || l_bld_blk_info_type_id
1308 || ' AND SUBSTR(hta.'
1309 || l_segment
1310 || ', LENGTH('''
1311 || l_prefix
1312 || ''') + 1) IN ('
1313 || l_flex_search_value
1314 || ')';
1315 ELSE
1316 l_one_where := ' hta.attribute_category = '
1317 || ''''
1318 || l_context
1319 || ''''
1320 || ' AND hta.'
1321 || l_segment
1322 || ' IN ('
1323 || l_flex_search_value
1324 || ')';
1325
1326 END IF;
1327 END IF;
1328
1329 l_additional_where := l_additional_where
1330 || ' '
1331 || l_search_connector
1332 || ' '
1333 || l_one_where;
1334
1335 END LOOP;
1336
1337 IF l_additional_where IS NOT NULL
1338 THEN
1339 l_sql_where := l_sql_where
1340 || ' AND ('
1341 || l_additional_where
1342 || ' )';
1343 END IF;
1344
1345 l_complete_sql := l_sql_select
1346 || ' '
1347 || l_sql_from
1348 || ' '
1349 || l_sql_where;
1350
1351
1352 /*
1353 l_result := NULL;
1354 OPEN c_sql for l_complete_sql;
1355 LOOP
1356 FETCH c_sql INTO l_timecard_id, l_timecard_ovn, l_status_code, l_status_meaning,
1357 l_period_starts, l_period_ends, l_hours_worked, l_submission_date;
1358
1359 EXIT WHEN c_sql%NOTFOUND;
1360
1361 l_result := l_result || '|'
1362 || l_timecard_id || '|'
1363 || l_timecard_ovn || '|'
1364 || NVL(l_status_code, 'NULL') || '|'
1365 || NVL(l_status_meaning, 'NULL') || '|'
1366 || TO_CHAR(l_period_starts, 'YYYY/MM/DD') || '|'
1367 || TO_CHAR(l_period_ends, 'YYYY/MM/DD') || '|'
1368 || l_hours_worked || '|'
1369 || TO_CHAR(l_submission_date, 'YYYY/MM/DD');
1370 END LOOP;
1371
1372
1373 p_result := l_result;
1374 */
1375 p_result := l_complete_sql;
1376 END get_search_sql;
1377
1378 -- ==========================================================================================
1379 -- this function returns the where clause for attribute search
1380 -- NOTE: if this is called from timecard search screen, p_resource_id will be the person who
1381 -- is performing the search. If this is called from approval screen, p_resource_id will
1382 -- be the approver who is performing the search.
1383 -- ==========================================================================================
1384 PROCEDURE get_sql_where(
1385 p_resource_id IN VARCHAR2
1386 ,p_search_rows IN VARCHAR2
1387 ,p_search_input_string IN VARCHAR2
1388 ,p_where OUT NOCOPY VARCHAR2
1389
1390 )
1391 IS
1392 l_search_table search_table;
1393 l_table_index NUMBER:= 1;
1394 l_value_index NUMBER:= 1;
1395 l_search_rows NUMBER := TO_NUMBER(p_search_rows);
1396 l_search_by VARCHAR2(100);
1397 l_search_operator VARCHAR2(30);
1398 l_search_value VARCHAR2(1000);
1399 l_search_connector VARCHAR2(5);
1400 l_detail_join_flag BOOLEAN := FALSE;
1401 l_attribute_flag BOOLEAN := FALSE;
1402 l_like_string VARCHAR2(1);
1403 l_context VARCHAR2(100);
1404 l_segment VARCHAR2(100);
1405 l_bld_blk_info_type_id NUMBER;
1406 l_prefix VARCHAR2(50);
1407 l_sql_select VARCHAR2(1000);
1408 l_sql_from VARCHAR2(1000);
1409 l_flex_search_value VARCHAR2(32767);
1410 l_sql_where VARCHAR2(32767);
1411 l_additional_where VARCHAR2(32767);
1412 l_one_where VARCHAR2(32767);
1413 l_complete_sql VARCHAR2(32767);
1414 l_result VARCHAR2(32767);
1415 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1416 l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
1417 l_status_code VARCHAR(30);
1418 l_status_meaning VARCHAR(100);
1419 l_period_starts hxc_time_building_blocks.start_time%TYPE;
1420 l_period_ends hxc_time_building_blocks.stop_time%TYPE;
1421 l_hours_worked NUMBER;
1422 l_submission_date DATE;
1423 c_sql cur_type;
1424
1425 --column names
1426 l_period_starts_column VARCHAR2(300);
1427 l_period_ends_column VARCHAR2(300);
1428 l_submission_date_column VARCHAR2(300);
1429 l_comment_column VARCHAR2(300);
1430 l_status_column VARCHAR2(300);
1431 l_hours_column VARCHAR2(300);
1432
1433 l_detail_join_where VARCHAR2(3000);
1434
1435 CURSOR c_mapping_segment(
1436 p_field_name VARCHAR2
1437 )
1438 IS
1439 SELECT context, segment, bld_blk_info_type_id
1443
1440 FROM hxc_mapping_attributes_v
1441 WHERE map = 'OTL Deposit Process Mapping'
1442 AND upper(field_name) = upper(p_field_name);
1444 BEGIN
1445
1446 -- Bug Fix for 2581640 Start
1447 -- Initialize the multi-message stack.
1448
1449 fnd_msg_pub.initialize;
1450
1451 -- Bug Fix for 2581640 End
1452
1453 -- put values in table
1454
1455 FOR l_table_index IN 1..l_search_rows LOOP
1456 l_search_table(l_table_index).search_by
1457 := get_value_from_string(p_search_input_string, l_value_index);
1458 l_value_index := l_value_index + 1;
1459
1460 l_search_table(l_table_index).search_operator
1461 := get_value_from_string(p_search_input_string, l_value_index);
1462 l_value_index := l_value_index + 1;
1463
1464
1465 l_search_table(l_table_index).search_value
1466 := get_value_from_string(p_search_input_string, l_value_index);
1467 l_value_index := l_value_index + 1;
1468
1469 l_search_table(l_table_index).search_connector
1470 := get_value_from_string(p_search_input_string, l_value_index);
1471 l_value_index := l_value_index + 1;
1472
1473 END LOOP;
1474
1475 p_where := '';
1476
1477 FOR l_index IN 1..l_search_rows LOOP
1478 l_search_by := l_search_table(l_index).search_by;
1479 l_search_operator := l_search_table(l_index).search_operator;
1480 l_search_value := l_search_table(l_index).search_value;
1481 l_search_connector := l_search_table(l_index).search_connector;
1482
1483 IF instr(l_search_operator, 'LIKE') <> 0
1484 THEN
1485 l_like_string := '%';
1486 ELSE
1487 l_like_string := '';
1488 END IF;
1489
1490 -- attribute search
1491 OPEN c_mapping_segment(l_search_by);
1492 FETCH c_mapping_segment INTO l_context, l_segment, l_bld_blk_info_type_id;
1493
1494 IF c_mapping_segment%NOTFOUND
1495 THEN
1496 CLOSE c_mapping_segment;
1497
1498 FND_MESSAGE.set_name('HXC','HXC_NO_MAPPING_COMPONENT');
1499
1500 -- Bug Fix for 2581640 Start
1501 -- Add error to the multi-message stack and retreive the error
1502 -- message in TimecardSearch.java. No need for RAISE_ERROR.
1503 FND_MSG_PUB.ADD;
1504 -- FND_MESSAGE.RAISE_ERROR;
1505 -- Bug Fix for 2581640 End
1506
1507 END IF;
1508
1509 CLOSE c_mapping_segment;
1510
1511 l_flex_search_value :=
1512 get_attributes(
1513 p_search_by => l_search_by
1514 ,p_search_value => l_search_value
1518 ,p_application_short_name => 'HXC'
1515 ,p_flex_segment => l_segment
1516 ,p_flex_context => l_context
1517 ,p_flex_name => 'OTC Information Types'
1519 ,p_operator => l_search_operator
1520 ,p_resource_id => p_resource_id
1521 ,p_field_name => l_search_by
1522 );
1523
1524 --for DUMMY ELEMENT CONTEXT, the stored values are 'ELEMENT - id',
1525 --for DUMMY COST CONTEXT, the stored values are 'COST - id',
1526 --we should extract the ids from them.
1527
1528 l_search_by := UPPER(l_search_by);
1529
1530 IF l_search_by = 'DUMMY ELEMENT CONTEXT'
1531 THEN
1532 l_prefix := 'ELEMENT - ';
1533 ELSIF l_search_by = 'DUMMY COST CONTEXT'
1534 THEN
1535 l_prefix := 'COST - ';
1536 END IF;
1537
1538
1539
1540 IF l_prefix IS NOT NULL
1541 THEN
1542
1543 l_one_where := ' hta.bld_blk_info_type_id = '
1544 || l_bld_blk_info_type_id
1545 || ' AND SUBSTR(hta.'
1546 || l_segment
1547 || ', LENGTH('''
1548 || l_prefix
1549 || ''') + 1) IN ('
1550 || l_flex_search_value
1551 || ')';
1552 ELSE
1553
1554 if l_flex_search_value = hxc_timecard_search_pkg.c_no_valueset_attached
1555 then
1556 l_one_where := ' hta.bld_blk_info_type_id = '
1557 || l_bld_blk_info_type_id
1558 || ' AND hta.'
1559 || l_segment
1560 || ' '
1561 || l_search_operator
1562 || ' '
1563 || ''''
1564 || l_like_string
1565 || l_search_value
1566 || l_like_string
1567 || '''';
1568
1569 else
1570 l_one_where := ' hta.bld_blk_info_type_id = '
1571 || l_bld_blk_info_type_id
1572 || ' AND hta.'
1573 || l_segment
1574 || ' IN ('
1575 || l_flex_search_value
1576 || ')';
1577 end if;
1578
1579 END IF;
1580
1581 -- Bug 3616179
1582
1583 if (l_index = 1) then
1584 -- Incase of first attribute search option,l_search_connector might be not-NULL.
1585 -- So place only the left parenthesis after the connector.
1586
1587 p_where := l_search_connector
1588 || ' '
1589 || '('
1590 || '('
1591 || l_one_where
1592 || ')';
1593
1594 else
1595 p_where := p_where
1596 || ' '
1597 || l_search_connector
1598 || ' '
1599 || '('
1600 || l_one_where
1601 || ')';
1602 end if;
1603
1604
1605 END LOOP;
1606
1607 -- Bug 3616179
1608 -- Finally place the right parenthesis before returning the value.
1609 p_where := p_where || ')';
1610
1611
1612 END get_sql_where;
1613
1614
1615 END hxc_timecard_search_pkg;