[Home] [Help]
PACKAGE BODY: APPS.HXC_APPROVAL_UTILITIES
Source
1 PACKAGE BODY hxc_approval_utilities AS
2 /* $Header: hxcaprutil.pkb 120.7.12010000.3 2010/04/21 11:27:17 amakrish ship $ */
3
4 TYPE approval_notification is RECORD(
5 time_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE
6 ,object_version_number hxc_time_building_blocks.object_version_number%TYPE
7 ,start_time hxc_time_building_blocks.start_time%TYPE
8 ,stop_time hxc_time_building_blocks.stop_time%TYPE
9 ,approval_status hxc_time_building_blocks.approval_status%TYPE
10 ,employee_name per_all_people_f.full_name%TYPE
11 ,comment_text hxc_time_building_blocks.comment_text%TYPE
12 ,application_name fnd_application_tl.application_name%TYPE
13 ,resource_id hxc_time_building_blocks.resource_id%TYPE
14 ,total_hours NUMBER(15, 3)
15 ,premium_hours NUMBER(15, 3)
16 ,non_worked_hours NUMBER(15, 3)
17 ,time_recipient_id NUMBER(15)
18 ,employee_number VARCHAR2(30)
19 ,transferred_to VARCHAR2(400)
20 );
21
22 type cur_type is REF CURSOR;
23
24 g_transaction_id NUMBER := NULL;
25 g_package VARCHAR2(100) := 'hxc_approval_utilities.';
26 g_debug BOOLEAN := hr_utility.debug_enabled;
27
28 FUNCTION is_selected(
29 p_selected_ids IN hxc_deposit_wrapper_utilities.t_simple_table
30 ,p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
31 )
32 RETURN BOOLEAN
33 IS
34 l_proc VARCHAR2(100);
35
36 BEGIN
37 if g_debug then
38 l_proc := 'is_selected';
39 hr_utility.set_location(g_package||l_proc, 10);
40 end if;
41
42 FOR l_index IN p_selected_ids.first .. p_selected_ids.last
43 LOOP
44 IF p_selected_ids(l_index) = TO_CHAR(p_block_id)
45 THEN
46
47 RETURN TRUE;
48 END IF;
49
50 END LOOP;
51
52 RETURN FALSE;
53 END is_selected;
54
55
56
57 PROCEDURE add_records(
58 p_approval_array IN OUT NOCOPY HXC_NOTIFICATION_TABLE_TYPE
59 ,p_record IN HXC_NOTIFICATION_TYPE
60 )
61 IS
62 l_index NUMBER;
63
64 l_proc VARCHAR2(100);
65
66 BEGIN
67 if g_debug then
68 l_proc:= 'add_records';
69 hr_utility.set_location(g_package||l_proc, 10);
70 end if;
71 l_index := p_approval_array.count;
72
73 p_approval_array.extend;
74
75 p_approval_array(l_index + 1) := p_record;
76
77 END add_records;
78
79 FUNCTION get_block_ids(
80 p_approval_array IN HXC_NOTIFICATION_TABLE_TYPE
81 )
82 RETURN VARCHAR2
83 IS
84 l_block_ids VARCHAR2(32767) := null;
85
86 l_proc VARCHAR2(100);
87
88 BEGIN
89 if g_debug then
90 l_proc:= 'get_block_ids';
91 hr_utility.set_location(g_package||l_proc, 10);
92 end if;
93
94 FOR l_block_index IN p_approval_array.first .. p_approval_array.last
95 LOOP
96 IF l_block_ids IS NOT NULL
97 THEN
98 l_block_ids := l_block_ids || ', ';
99 END IF;
100
101 l_block_ids := l_block_ids || p_approval_array(l_block_index).time_building_block_id;
102
103 END LOOP;
104
105 RETURN l_block_ids;
106 END get_block_ids;
107
108 FUNCTION has_comment(
109 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
110 ,p_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
111 ,p_operator IN VARCHAR2
112 ,p_comment IN VARCHAR2
113
114 ) RETURN VARCHAR2
115 IS
116 l_like_string VARCHAR2(1);
117 l_yes VARCHAR2(1);
118 l_sql VARCHAR2(32767);
119 c_sql cur_type;
120
121 BEGIN
122
123
124 IF instr(p_operator, 'LIKE') <> 0
125 THEN
126 l_like_string := '%';
127 ELSE
128 l_like_string := '';
129 END IF;
130
131 -- 115.10 Change kSethi
132 -- Fix for Bug no. 2463798
133 -- Commenting below and adding new query
134 /*
135 l_sql := 'SELECT ''Y'''
136 || ' FROM hxc_time_attribute_usages htau,'
137 || ' hxc_time_attributes hta'
138 || ' WHERE ' || p_block_id || ' = htau.time_building_block_id'
139 || ' AND ' || p_block_ovn || '= htau.time_building_block_ovn'
143 || p_operator
140 || ' AND htau.time_attribute_id = hta.time_attribute_id'
141 || ' AND hta.attribute_category = ''APPROVAL'''
142 || ' AND NVL(hta.attribute6, '' '') '
144 || ' '''
145 || l_like_string
146 || p_comment
147 || l_like_string
148 || '''';
149 */
150 -- New l_sql
151 l_sql := 'SELECT ''Y'''
152 || ' FROM hxc_time_building_blocks htbb,'
153 || ' hxc_time_building_blocks htbb_tc'
154 || ' WHERE htbb.time_building_block_id = ' || p_block_id
155 || ' AND htbb.object_version_number = ' || p_block_ovn
156 || ' AND htbb_tc.scope = ''TIMECARD'''
157 || ' AND htbb_tc.resource_id = htbb.resource_id'
158 || ' AND TRUNC(htbb_tc.start_time) >= TRUNC(htbb.start_time)'
159 || ' AND TRUNC(htbb_tc.stop_time) <= TRUNC(htbb.stop_time)'
160 || ' AND htbb_tc.date_to = hr_general.end_of_time'
161 || ' AND NVL(htbb_tc.comment_text, '' '') '
162 || p_operator
163 || ' '''
164 || l_like_string
165 || p_comment
166 || l_like_string
167 || '''';
168 -- End Fix for Bug no. 2463798
169 OPEN c_sql for l_sql;
170 FETCH c_sql INTO l_yes;
171
172 IF c_sql%NOTFOUND
173 THEN
174 RETURN 'N';
175 END IF;
176
177 RETURN 'Y';
178 END has_comment;
179
180 FUNCTION has_detail_comment(
181 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
182 ,p_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
183 ,p_operator IN VARCHAR2
184 ,p_comment IN VARCHAR2
185
186 ) RETURN VARCHAR2
187 IS
188 l_like_string VARCHAR2(1);
189 l_yes VARCHAR2(1);
190 l_sql VARCHAR2(32767);
191 c_sql cur_type;
192
193 BEGIN
194
195 IF instr(p_operator, 'LIKE') <> 0
196 THEN
197 l_like_string := '%';
198 ELSE
199 l_like_string := '';
200 END IF;
201
202 l_sql := 'SELECT ''Y'''
203 || ' FROM hxc_ap_detail_links aplinks,'
204 || ' hxc_time_building_blocks htbb_detail'
205 || ' WHERE aplinks.application_period_id = ' || p_block_id
206 || ' AND aplinks.time_building_block_id = htbb_detail.time_building_block_id '
210 || p_operator
207 || ' AND aplinks.time_building_block_ovn = htbb_detail.object_version_number '
208 || ' AND htbb_detail.date_to = hr_general.end_of_time'
209 || ' AND NVL(htbb_detail.comment_text, '' '') '
211 || ' '''
212 || l_like_string
213 || p_comment
214 || l_like_string
215 || '''';
216
217 OPEN c_sql for l_sql;
218 FETCH c_sql INTO l_yes;
219
220 IF c_sql%NOTFOUND
221 THEN
222 RETURN 'N';
223 END IF;
224
225 RETURN 'Y';
226
227
228 END has_detail_comment;
229
230 PROCEDURE get_mapping_component(
231 p_field_name IN VARCHAR2
232 ,p_context OUT NOCOPY VARCHAR2
233 ,p_segment OUT NOCOPY VARCHAR2
234 ,p_bld_blk_info_type_id OUT NOCOPY NUMBER
235 )
236 IS
237 CURSOR c_mapping_segment(
238 p_field_name VARCHAR2
239 )
240 IS
241 SELECT context, segment, bld_blk_info_type_id
242 FROM hxc_mapping_attributes_v
243 WHERE map = 'OTL Deposit Process Mapping'
244 AND upper(field_name) = upper(p_field_name);
245 BEGIN
246
247
248 OPEN c_mapping_segment(p_field_name);
249 FETCH c_mapping_segment INTO p_context, p_segment, p_bld_blk_info_type_id;
250
251 IF c_mapping_segment%NOTFOUND
252 THEN
253 CLOSE c_mapping_segment;
254
255
256 FND_MESSAGE.set_name('HXC','HXC_NO_MAPPING_COMPONENT');
257 FND_MESSAGE.RAISE_ERROR;
258 END IF;
259
260 CLOSE c_mapping_segment;
261 END get_mapping_component;
262
263
264
265 FUNCTION attribute_search(
266 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
267 ,p_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
268 ,p_search_by IN VARCHAR2
269 ,p_search_value IN VARCHAR2
270 ,p_search_operator IN VARCHAR2
271 ,p_resource_id IN VARCHAR2
272 )
273 RETURN VARCHAR2
274 IS
275 l_context VARCHAR2(100);
276 l_segment VARCHAR2(100);
277 l_bld_blk_info_type_id NUMBER;
278 l_flex_search_value VARCHAR2(32767);
279 c_sql cur_type;
280 l_sql VARCHAR2(32767) := NULL;
281 l_yes VARCHAR2(1);
285
282 l_like_string VARCHAR2(1);
283 l_dummy Number(15);
284
286 BEGIN
287
288 l_dummy := hxc_timecard_properties.setup_mo_global_params(p_resource_id);
289
290 get_mapping_component(
291 p_field_name => p_search_by
292 ,p_context => l_context
293 ,p_segment => l_segment
294 ,p_bld_blk_info_type_id => l_bld_blk_info_type_id
295 );
296
297 l_flex_search_value :=
298 hxc_timecard_search_pkg.get_attributes(
299 p_search_by => p_search_by
300 ,p_search_value => p_search_value
301 ,p_flex_segment => l_segment
302 ,p_flex_context => l_context
303 ,p_flex_name => 'OTC Information Types'
304 ,p_application_short_name => 'HXC'
305 ,p_operator => p_search_operator
306 ,p_resource_id => p_resource_id
307 ,p_field_name => p_search_by
308 );
309
310
311
312 if l_flex_search_value = hxc_timecard_search_pkg.c_no_valueset_attached
313 then
314
315 IF instr(p_search_operator, 'LIKE') <> 0
316 THEN
317 l_like_string := '%';
318 ELSE
319 l_like_string := '';
320 END IF;
321
322
323 l_sql := 'SELECT ''Y'''
324 || ' FROM hxc_ap_detail_links aplinks,'
325 || ' hxc_time_building_blocks htbb_detail,'
326 || ' hxc_time_attribute_usages htau,'
327 || ' hxc_time_attributes hta'
328 || ' WHERE aplinks.application_period_id = ' || p_block_id
329 || ' AND aplinks.time_building_block_id = htbb_detail.time_building_block_id '
330 || ' AND aplinks.time_building_block_ovn = htbb_detail.object_version_number '
331 || ' AND htbb_detail.date_to = hr_general.end_of_time'
332 || ' AND htau.time_building_block_id = htbb_detail.time_building_block_id'
333 || ' AND htau.time_building_block_ovn = htbb_detail.object_version_number'
334 || ' AND htau.time_attribute_id = hta.time_attribute_id'
335 || ' AND hta.attribute_category = ' || '''' || l_context || ''''
336 || ' AND hta.' || l_segment ||' '|| p_search_operator||' '||''''||l_like_string || p_search_value||l_like_string||'''';
337 else
338 l_sql := 'SELECT ''Y'''
339 || ' FROM hxc_ap_detail_links aplinks,'
340 || ' hxc_time_building_blocks htbb_detail,'
341 || ' hxc_time_attribute_usages htau,'
342 || ' hxc_time_attributes hta'
343 || ' WHERE aplinks.application_period_id = ' || p_block_id
344 || ' AND aplinks.time_building_block_id = htbb_detail.time_building_block_id '
345 || ' AND aplinks.time_building_block_ovn = htbb_detail.object_version_number '
346 || ' AND htbb_detail.date_to = hr_general.end_of_time'
347 || ' AND htau.time_building_block_id = htbb_detail.time_building_block_id'
348 || ' AND htau.time_building_block_ovn = htbb_detail.object_version_number'
349 || ' AND htau.time_attribute_id = hta.time_attribute_id'
350 || ' AND hta.attribute_category = ' || '''' || l_context || ''''
351 || ' AND hta.' || l_segment || ' IN (' || l_flex_search_value || ')';
352 end if;
353
354 OPEN c_sql for l_sql;
355 FETCH c_sql INTO l_yes;
356
357
358 IF c_sql%NOTFOUND
359 THEN
360
361 RETURN 'N';
362 END IF;
363
364
365 RETURN 'Y';
366
367
368 END attribute_search;
369
370 PROCEDURE adv_search(
371 p_block_ids IN VARCHAR2
372 ,p_adv_search IN VARCHAR2
373 ,p_selected_ids OUT NOCOPY hxc_deposit_wrapper_utilities.t_simple_table
374 )
375 IS
376
377 l_adv_table hxc_deposit_wrapper_utilities.t_simple_table;
378
379 l_search_by VARCHAR2(100);
380 l_search_operator VARCHAR2(30);
381 l_search_value VARCHAR2(1000);
382 l_search_connector VARCHAR2(5);
383 l_detail_join_flag BOOLEAN := FALSE;
384 l_attribute_flag BOOLEAN := FALSE;
385 l_like_string VARCHAR2(1);
386 l_sql_select VARCHAR2(1000);
387 l_sql_from VARCHAR2(1000);
388 l_flex_search_value VARCHAR2(32767);
389 l_sql_where VARCHAR2(32767);
390 l_additional_where VARCHAR2(32767);
394 l_selected_id_index NUMBER;
391 l_one_where VARCHAR2(32767);
392 l_complete_sql VARCHAR2(32767);
393 l_adv_table_index NUMBER;
395
396 l_context VARCHAR2(100);
397 l_segment VARCHAR2(100);
398 l_bld_blk_info_type_id NUMBER;
399 l_temp_search_by VARCHAR2(100);
400
401 c_sql cur_type;
402
403 l_proc VARCHAR2(100);
404
405 BEGIN
406 if g_debug then
407 l_proc := 'adv_search';
408 hr_utility.set_location(g_package||l_proc, 10);
409 end if;
410 --Setting the MOAC params as part of R12 changes.
411 hxc_deposit_wrapper_utilities.string_to_table(
412 p_separator => '|'
413 ,p_string => p_adv_search
414 ,p_table => l_adv_table
415 );
416
417 if g_debug then
418 hr_utility.set_location(g_package||l_proc, 20);
419 end if;
420
421 l_sql_select := 'SELECT time_building_block_id';
422 l_sql_from := 'FROM hxc_time_building_blocks htbb';
423 l_sql_where := 'WHERE htbb.time_building_block_id IN (' || p_block_ids || ')'
427 hr_utility.set_location(g_package||l_proc, 30);
424 || ' AND htbb.date_to = hr_general.end_of_time';
425
426 if g_debug then
428 end if;
429 l_additional_where := NULL;
430
431 l_adv_table_index := 0;
432
433 LOOP
434 EXIT WHEN NOT l_adv_table.exists(l_adv_table_index);
435
436 if g_debug then
437 hr_utility.set_location(g_package||l_proc, 40);
438 end if;
439
440 l_search_by := l_adv_table(l_adv_table_index);
441 l_search_operator := l_adv_table(l_adv_table_index + 1);
442 l_search_value := l_adv_table(l_adv_table_index + 2);
443 l_search_connector := l_adv_table(l_adv_table_index + 3);
444
445
446
447 IF instr(l_search_operator, 'LIKE') <> 0
448 THEN
449 l_like_string := '%';
450 ELSE
451 l_like_string := '';
452 END IF;
453
454 IF l_search_by = 'PERIOD_STARTS'
455 THEN
456
457 if g_debug then
458 hr_utility.set_location(g_package||l_proc, 50);
459 end if;
460 l_one_where := 'TRUNC(htbb.start_time) '
461 || l_search_operator
462 || ' TO_DATE('
463 || ''''
464 || l_search_value
465 || ''''
466 || ', ''RRRR/MM/DD'')';
467
468 ELSIF l_search_by = 'PERIOD_ENDS'
469 THEN
470 if g_debug then
471 hr_utility.set_location(g_package||l_proc, 60);
472 end if;
473 l_one_where := 'TRUNC(htbb.stop_time) '
474 || l_search_operator
475 || ' TO_DATE('
476 || ''''
477 || l_search_value
478 || ''''
479 || ', ''RRRR/MM/DD'')';
480
481 ELSIF l_search_by = 'SUBMISSION_DATE'
482 THEN
483 if g_debug then
484 hr_utility.set_location(g_package||l_proc, 70);
485 end if;
486 l_one_where := 'TRUNC(htbb.creation_date) '
487 || l_search_operator
488 || ' TO_DATE('
489 || ''''
490 || l_search_value
491 || ''''
492 || ', ''RRRR/MM/DD'')';
493
494 ELSIF l_search_by = 'TIMECARD_COMMENT'
495 THEN
496 l_one_where := 'hxc_approval_utilities.has_comment(htbb.time_building_block_id, htbb.object_version_number,'''
497 || l_search_operator || ''',''' || l_search_value || ''') = ''Y''';
498
499 ELSIF l_search_by = 'DETAIL_COMMENT'
500 THEN
501 l_one_where := 'hxc_approval_utilities.has_detail_comment(htbb.time_building_block_id, htbb.object_version_number, '''
502 || l_search_operator || ''',''' || l_search_value || ''') = ''Y''';
503
504 ELSIF l_search_by = 'STATUS_CODE'
505 THEN
506 l_one_where := 'hr_general.decode_lookup(''HXC_APPROVAL_STATUS'', htbb.approval_status)'
507 || l_search_operator
508 || ' '''
509 || l_like_string
510 || l_search_value
511 || l_like_string
512 || '''';
513
514 ELSIF l_search_by = 'HOURS_WORKED'
515 THEN
516 l_one_where := 'hxc_time_category_utils_pkg.category_app_period_tc_hrs(htbb.start_time,
517 htbb.stop_time, htbb.resource_id, '''', htbb.time_building_block_id) '
518 || l_search_operator
519 || l_search_value;
520
521 ELSIF l_search_by = 'PERSON_TYPE' --Added for PO Integration for CWK Support
522 THEN
523
524 l_one_where := 'htbb.resource_id in (select p.person_id
525 from per_people_f p,per_person_types ppt,
526 per_person_type_usages_f pptu
527 where pptu.person_id = p.person_id and
528 ppt.person_type_id = pptu.person_type_id and
529 ppt.user_person_type '
530 || l_search_operator ||''''||l_like_string||l_search_value
531 ||l_like_string||''''||')';
532
533 ELSIF l_search_by = 'SUPPLIER' --Added for PO Integration for CWK Support
534 THEN
535
536 l_temp_search_by := 'PO Line Id';
537
538 get_mapping_component(
539 p_field_name => l_temp_search_by
540 ,p_context => l_context
541 ,p_segment => l_segment
542 ,p_bld_blk_info_type_id => l_bld_blk_info_type_id
543 );
544
545 l_one_where := 'htbb.time_building_block_id in (select distinct hadl.APPLICATION_PERIOD_ID
546 from hxc_time_attributes hta, hxc_time_attribute_usages htau,
547 po_vendors pv, po_headers_all pha, hxc_time_building_blocks detail,
548 hxc_ap_detail_links hadl, po_lines_all pla
549 where hta.attribute_category ='||''''||l_context||''''
550 ||' and hta.'||l_segment
551 ||'= pla.po_line_id and pv.vendor_name '
552 ||l_search_operator||''''||l_like_string
553 ||l_search_value||l_like_string||''''
554 ||' and pha.vendor_id=pv.VENDOR_ID
555 and pla.po_header_id= pha.po_header_id
556 and htau.TIME_ATTRIBUTE_ID = hta.time_attribute_id
557 and htau.time_building_block_id = detail.time_building_block_id
558 and detail.date_to = hr_general.end_of_time
559 and hadl.time_building_block_id = detail.time_building_block_id
560 and hadl.time_building_block_ovn = detail.object_version_number
564
561 and hta.bld_blk_info_type_id ='|| l_bld_blk_info_type_id||')';
562
563
565 ELSE
566 --attribute search
567 l_one_where := 'hxc_approval_utilities.attribute_search(htbb.time_building_block_id, htbb.object_version_number,'
568 || '''' || l_search_by || ''',''' || l_search_value || ''',''' || l_search_operator
569 || ''', htbb.resource_id) = ''Y''';
570
571
572 END IF;
573
574 if g_debug then
575 hr_utility.set_location(g_package||l_proc, 100);
576 end if;
577 l_additional_where := l_additional_where
578 || ' '
579 || l_search_connector
580 || ' '
581 || l_one_where;
582
583 l_adv_table_index := l_adv_table_index + 4;
584
585 END LOOP;
586
587 if g_debug then
588 hr_utility.set_location(g_package||l_proc, 110);
589 end if;
590
591 IF l_additional_where IS NOT NULL
592 THEN
593 if g_debug then
594 hr_utility.set_location(g_package||l_proc, 120);
595 end if;
596 l_sql_where := l_sql_where
597 || ' AND ('
598 || l_additional_where
599 || ' )';
600 END IF;
601
602 if g_debug then
603 hr_utility.set_location(g_package||l_proc, 130);
604 end if;
605 l_complete_sql := l_sql_select
606 || ' '
607 || l_sql_from
608 || ' '
609 || l_sql_where;
610 if g_debug then
611 hr_utility.set_location(g_package||l_proc, 140);
612 end if;
613 --execute query
614 l_selected_id_index := 0;
615
616 OPEN c_sql for l_complete_sql;
617 LOOP
618 FETCH c_sql INTO p_selected_ids(l_selected_id_index);
619
620 EXIT WHEN c_sql%NOTFOUND;
621
622 if g_debug then
623 hr_utility.trace('selected id=' || p_selected_ids(l_selected_id_index));
624 end if;
625 l_selected_id_index := l_selected_id_index + 1;
626 END LOOP;
627
628 CLOSE c_sql;
629
630 if g_debug then
631 hr_utility.set_location(g_package||l_proc, 150);
632 end if;
633 END adv_search;
634
635 PROCEDURE release_locks
636 IS
637 l_success BOOLEAN;
638 BEGIN
639 IF g_transaction_id IS NOT NULL
640 THEN
641 hxc_lock_api.release_lock(
642 p_row_lock_id => NULL
643 ,p_process_locker_type => hxc_lock_util.c_ss_approval_action
644 ,p_transaction_lock_id => g_transaction_id
645 ,p_released_success => l_success
646 );
647
648 g_transaction_id := NULL;
649 END IF;
650 END release_locks;
651
652 FUNCTION get_name(
653 p_person_id IN per_all_people_f.person_id%TYPE
654 )
655 RETURN VARCHAR2
656 IS
657
658 CURSOR c_name(
659 p_person_id per_all_people_f.person_id%TYPE
660 )
661 IS
662 SELECT full_name
663 FROM per_all_people_f
664 WHERE person_id = p_person_id
665 AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
666
667 l_name per_all_people_f.full_name%TYPE;
668 BEGIN
669 OPEN c_name(p_person_id);
670 FETCH c_name INTO l_name;
671 CLOSE c_name;
672
673 RETURN l_name;
674 END get_name;
675
676 PROCEDURE get_open_notifications(
677 p_approver_id IN NUMBER
678 ,p_approval_array OUT NOCOPY HXC_NOTIFICATION_TABLE_TYPE
679 ,p_resource_id IN VARCHAR2
680 ,p_from_date IN VARCHAR2
681 ,p_to_date IN VARCHAR2
682 ,p_adv_search IN VARCHAR2
683 )
684 IS
685 -- l_approver_name per_all_people_f.full_name%TYPE;
686 l_item_type wf_item_activity_statuses.item_type%TYPE := 'HXCEMP';
687 l_item_key wf_item_activity_statuses.item_key%TYPE;
688 l_app_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
689 l_app_bb_ovn hxc_time_building_blocks.object_version_number%TYPE;
690 l_approval_record approval_notification;
691 l_array_index NUMBER := 0;
692 l_match BOOLEAN;
693 l_resource_id NUMBER := -1;
694 l_start_date DATE;
695 l_end_date DATE;
696 l_approval_array HXC_NOTIFICATION_TABLE_TYPE;
697 l_selected_ids hxc_deposit_wrapper_utilities.t_simple_table;
698 l_index NUMBER;
699
700 l_messages HXC_MESSAGE_TABLE_TYPE;
701 l_lock_id ROWID;
702 l_success BOOLEAN;
703
704 l_proc VARCHAR2(100);
705
706
707 CURSOR c_notification_item_keys(
708 p_item_type IN wf_item_activity_statuses.item_type%TYPE
709 ,p_approver_id IN NUMBER
710 )
711 IS
712 SELECT wias.item_key
713 FROM WF_NOTIFICATIONS wn,
714 wf_item_activity_statuses wias,
715 fnd_user fu
716 WHERE wn.recipient_role = fu.user_name
717 AND wn.status = 'OPEN'
718 AND wn.message_name IN ('TIMECARD_APPROVAL','TIMECARD_APPROVAL_INLINE', 'TIMECARD_APPROVAL_INLINE_ABS')
719 AND wias.notification_id = wn.notification_id
720 AND wias.activity_status = 'NOTIFIED'
721 AND wias.item_type = p_item_type
722 AND fu.employee_id = p_approver_id
723 ORDER BY from_user desc;
724
725 CURSOR c_approval_periods(
726 p_app_bb_id IN hxc_time_building_blocks.time_building_block_id%TYPE
730 SELECT /*+ leading(apsum) */
727 ,p_app_bb_ovn IN hxc_time_building_blocks.object_version_number%TYPE
728 )
729 IS
731 apsum.application_period_id
732 ,apsum.application_period_ovn
733 ,apsum.start_time
734 ,apsum.stop_time
735 ,hr_general.decode_lookup('HXC_APPROVAL_STATUS', apsum.approval_status)
736 ,ppf.full_name
737 ,htbb.comment_text
738 ,favtl.application_name
739 ,apsum.resource_id
740 ,hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, '', apsum.application_period_id)
741 ,hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, 'Total2', apsum.application_period_id),
742 hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, 'Total3', apsum.application_period_id),
743 apsum.time_recipient_id
744 ,nvl(ppf.employee_number,ppf.NPW_NUMBER)
745 ,hxc_self_service_timecard.get_timecard_transferred_to(hts.timecard_id, hts.timecard_ovn)
746 FROM hxc_app_period_summary apsum
747 ,hxc_time_building_blocks htbb
748 ,fnd_application_tl favtl
749 ,per_all_people_f ppf
750 ,hxc_time_recipients htr
751 ,hxc_tc_ap_links htal
752 ,hxc_timecard_summary hts
753
754
755 WHERE apsum.application_period_id = p_app_bb_id
756 AND apsum.application_period_ovn = p_app_bb_ovn
757 AND htal.application_period_id = apsum.application_period_id
758 AND hts.timecard_id = htal.timecard_id
759 AND apsum.resource_id = ppf.person_id
760 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
761 AND htbb.time_building_block_id = apsum.application_period_id
762 AND htbb.object_version_number = apsum.application_period_ovn
763 AND favtl.application_id = htr.application_id
764 AND htr.time_recipient_id = apsum.time_recipient_id
765 AND favtl.language = userenv('LANG');
766
767 BEGIN
768 if g_debug then
769 l_proc := 'get_approval_notifications';
770 hr_utility.set_location(g_package||l_proc, 10);
771 end if;
772 release_locks;
773
774 SELECT HXC_TRANSACTION_LOCK_S.nextval
775 INTO g_transaction_id
776 FROM DUAL;
777
778 p_approval_array := HXC_NOTIFICATION_TABLE_TYPE();
779 l_approval_array := HXC_NOTIFICATION_TABLE_TYPE();
780
781 if g_debug then
782 hr_utility.set_location(g_package||l_proc, 20);
783 end if;
784 OPEN c_notification_item_keys(
785 p_item_type => l_item_type
786 ,p_approver_id => p_approver_id
787 );
788
789
790 LOOP
791 FETCH c_notification_item_keys INTO l_item_key;
792 EXIT WHEN c_notification_item_keys%NOTFOUND;
793
794 l_match := TRUE;
795
796 IF p_resource_id IS NOT NULL
797 THEN
798 SELECT NUMBER_VALUE
799 INTO l_resource_id
800 FROM wf_item_attribute_values
801 WHERE item_type = l_item_type
802 AND item_key = l_item_key
803 AND name = 'RESOURCE_ID';
804
805 IF l_resource_id IS NOT NULL
806 AND l_resource_id = TO_NUMBER(p_resource_id)
807 THEN
808 NULL;
809 ELSE
810 l_match := FALSE;
811 END IF;
812 END IF;
813
814 if g_debug then
815 hr_utility.set_location(g_package||l_proc, 30);
816 end if;
817 IF l_match
818 THEN
819 IF p_from_date IS NOT NULL
820 THEN
821 SELECT DATE_VALUE
822 INTO l_end_date
823 FROM wf_item_attribute_values
824 WHERE item_type = l_item_type
825 AND item_key = l_item_key
826 AND name = 'APP_END_DATE';
827
828
829 IF TRUNC(l_end_date) < TO_DATE(p_from_date, 'YYYY/MM/DD')
830 THEN
831 l_match := FALSE;
832 END IF;
833
834 END IF;
835 END IF;
836
837 if g_debug then
838 hr_utility.set_location(g_package||l_proc, 40);
839 end if;
840 IF l_match
841 THEN
842 IF p_to_date IS NOT NULL
843 THEN
844 SELECT DATE_VALUE
845 INTO l_start_date
846 FROM wf_item_attribute_values
847 WHERE item_type = l_item_type
848 AND item_key = l_item_key
849 AND name = 'APP_START_DATE';
850
851
852 IF TRUNC(l_start_date) > TO_DATE(p_to_date, 'YYYY/MM/DD')
853 THEN
854 l_match := FALSE;
855 END IF;
856
857 END IF;
858 END IF;
859
860 if g_debug then
861 hr_utility.set_location(g_package||l_proc, 50);
862 end if;
863 IF l_match
864 THEN
865
866 SELECT NUMBER_VALUE
867 INTO l_app_bb_id
868 FROM wf_item_attribute_values
869 WHERE item_type = l_item_type
870 AND item_key = l_item_key
871 AND name = 'APP_BB_ID';
872
873 SELECT NUMBER_VALUE
874 INTO l_app_bb_ovn
875 FROM wf_item_attribute_values
876 WHERE item_type = l_item_type
877 AND item_key = l_item_key
878 AND name = 'APP_BB_OVN';
879
880 if g_debug then
881 hr_utility.set_location(g_package||l_proc, 60);
882 end if;
883 OPEN c_approval_periods(
884 p_app_bb_id => l_app_bb_id
885 ,p_app_bb_ovn => l_app_bb_ovn
886 );
887
888 FETCH c_approval_periods INTO l_approval_record;
892 NULL;
889 IF c_approval_periods%NOTFOUND
890 THEN
891
893 ELSE
894 --request lock
895 l_lock_id := null;
896 hxc_lock_api.request_lock(
897 p_process_locker_type => hxc_lock_util.c_ss_approval_action
898 ,p_time_building_block_id => l_app_bb_id
899 ,p_time_building_block_ovn => l_app_bb_ovn
900 ,p_transaction_lock_id => g_transaction_id
901 ,p_messages => l_messages
902 ,p_row_lock_id => l_lock_id
903 ,p_locked_success => l_success
904 );
905
906 IF l_success
907 THEN
908
909 if g_debug then
910 hr_utility.set_location(g_package||l_proc, 70);
911 end if;
912 l_approval_array.extend;
913
914 l_array_index := l_array_index + 1;
915
916 l_approval_array(l_array_index) :=
917 HXC_NOTIFICATION_TYPE(
918 l_approval_record.time_building_block_id
919 ,l_approval_record.object_version_number
920 ,l_approval_record.start_time
921 ,l_approval_record.stop_time
922 ,'Pending Approval'
923 ,l_approval_record.employee_name
924 ,l_approval_record.comment_text
925 ,get_name(p_approver_id)
926 ,l_approval_record.application_name
927 ,l_item_key
928 ,l_approval_record.resource_id
929 ,l_approval_record.total_hours
930 ,l_approval_record.premium_hours
931 ,l_approval_record.non_worked_hours
932 ,l_approval_record.time_recipient_id
933 ,l_approval_record.employee_number
934 ,l_approval_record.transferred_to
935 );
936 END IF;
937 END IF;
938
939 CLOSE c_approval_periods;
940 END IF; -- if l_match
941 END LOOP;
942
943 CLOSE c_notification_item_keys;
944
945 if g_debug then
946 hr_utility.set_location(g_package||l_proc, 80);
947 end if;
948
949 IF l_approval_array.count = 0
950 THEN
951 RETURN;
952 END IF;
953
954 --adv search
955 IF p_adv_search IS NOT NULL
956 THEN
957 if g_debug then
958 hr_utility.set_location(g_package||l_proc, 90);
959 end if;
960
961 adv_search(
962 p_block_ids => get_block_ids(l_approval_array)
963 ,p_adv_search => p_adv_search
964 ,p_selected_ids => l_selected_ids
965 );
966
967 if g_debug then
968 hr_utility.set_location(g_package||l_proc, 100);
969 end if;
970
971 IF l_selected_ids.count > 0
972 THEN
973 if g_debug then
974 hr_utility.set_location(g_package||l_proc, 110);
975 end if;
976
977 --populate p_approval_array and return
978 l_index := l_approval_array.first;
979
980 LOOP
981 EXIT WHEN NOT l_approval_array.exists(l_index);
982
983 IF is_selected(l_selected_ids,
984 l_approval_array(l_index).time_building_block_id)
985 THEN
986
987 add_records(p_approval_array, l_approval_array(l_index));
988 END IF;
989
990 l_index := l_approval_array.next(l_index);
991
992 END LOOP;
993
994 if g_debug then
995 hr_utility.set_location(g_package||l_proc, 120);
996 end if;
997 END IF;
998
999 ELSE
1000 if g_debug then
1001 hr_utility.set_location(g_package||l_proc, 130);
1002 end if;
1003 --simple search
1004 p_approval_array := l_approval_array;
1005
1006 if g_debug then
1007 hr_utility.set_location(g_package||l_proc, 140);
1008 end if;
1009 END IF;
1010
1011 if g_debug then
1012 hr_utility.set_location(g_package||l_proc, 150);
1013 end if;
1014
1015
1016 END get_open_notifications;
1017
1018 PROCEDURE get_approval_history(
1019 p_approver_id IN NUMBER
1020 ,p_approval_array OUT NOCOPY HXC_NOTIFICATION_TABLE_TYPE
1021 ,p_resource_id IN VARCHAR2
1022 ,p_from_date IN VARCHAR2
1023 ,p_to_date IN VARCHAR2
1024 ,p_adv_search IN VARCHAR2
1025 )
1026 IS
1027 CURSOR c_app_periods(
1028 p_approver_id hxc_time_building_blocks.time_building_block_id%TYPE
1029 )
1030 IS
1031 SELECT /*+ leading(apsum) */
1032 apsum.application_period_id
1033 ,apsum.application_period_ovn
1034 ,apsum.start_time
1035 ,apsum.stop_time
1036 ,hr_general.decode_lookup('HXC_APPROVAL_STATUS', apsum.approval_status)
1037 ,ppf.full_name
1038 ,htbb.comment_text
1039 ,favtl.application_name
1040 ,apsum.resource_id
1041 ,hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, '', apsum.application_period_id)
1042 ,hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, 'Total2', apsum.application_period_id),
1043 hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, 'Total3', apsum.application_period_id),
1044 apsum.time_recipient_id
1045 ,nvl(ppf.employee_number,ppf.NPW_NUMBER)
1046 ,hxc_self_service_timecard.get_timecard_transferred_to(hts.timecard_id, hts.timecard_ovn)
1047 FROM hxc_app_period_summary apsum
1048 ,hxc_time_building_blocks htbb
1049 ,fnd_application_tl favtl
1050 ,per_all_people_f ppf
1051 ,hxc_time_recipients htr
1052 ,hxc_tc_ap_links htal
1056 AND apsum.approval_status <> 'SUBMITTED'
1053 ,hxc_timecard_summary hts
1054
1055 WHERE apsum.approver_id = p_approver_id
1057 AND htal.application_period_id = apsum.application_period_id
1058 AND hts.timecard_id = htal.timecard_id
1059 AND apsum.resource_id = ppf.person_id
1060 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1061 AND htbb.time_building_block_id = apsum.application_period_id
1062 AND htbb.object_version_number = apsum.application_period_ovn
1063 AND favtl.application_id = htr.application_id
1064 AND htr.time_recipient_id = apsum.time_recipient_id
1065 AND favtl.language = userenv('LANG')
1066 AND NVL(p_resource_id, apsum.resource_id) = apsum.resource_id
1067 AND NVL(p_from_date, TO_CHAR(apsum.stop_time, 'YYYY/MM/DD')) <= TO_CHAR(apsum.stop_time, 'YYYY/MM/DD')
1068 AND NVL(p_to_date, TO_CHAR(apsum.start_time, 'YYYY/MM/DD')) >= TO_CHAR(apsum.start_time, 'YYYY/MM/DD')
1069 ORDER BY ppf.full_name desc, apsum.start_time desc;
1070
1071 l_approval_record approval_notification;
1072 l_approval_array HXC_NOTIFICATION_TABLE_TYPE;
1073 l_array_index NUMBER := 0;
1074 l_selected_ids hxc_deposit_wrapper_utilities.t_simple_table;
1075 l_index NUMBER;
1076
1077 l_proc VARCHAR2(500);
1078 BEGIN
1079 p_approval_array := HXC_NOTIFICATION_TABLE_TYPE();
1080 l_approval_array := HXC_NOTIFICATION_TABLE_TYPE();
1081
1082
1083 OPEN c_app_periods(p_approver_id);
1084
1085 LOOP
1086 FETCH c_app_periods INTO l_approval_record;
1087 EXIT WHEN c_app_periods%NOTFOUND;
1088
1089 if g_debug then
1090 l_proc := 'get_approval_history';
1091 hr_utility.set_location(g_package||l_proc, 70);
1092 end if;
1093
1094 l_approval_array.extend;
1095
1096 l_array_index := l_array_index + 1;
1097
1098 l_approval_array(l_array_index) :=
1099 HXC_NOTIFICATION_TYPE(
1100 l_approval_record.time_building_block_id
1101 ,l_approval_record.object_version_number
1102 ,l_approval_record.start_time
1103 ,l_approval_record.stop_time
1104 ,l_approval_record.approval_status
1105 ,l_approval_record.employee_name
1106 ,l_approval_record.comment_text
1107 ,get_name(p_approver_id)
1108 ,l_approval_record.application_name
1109 ,''
1110 ,l_approval_record.resource_id
1111 ,l_approval_record.total_hours
1112 ,l_approval_record.premium_hours
1113 ,l_approval_record.non_worked_hours
1114 ,l_approval_record.time_recipient_id
1115 ,l_approval_record.employee_number
1116 ,l_approval_record.transferred_to
1117 );
1118
1119 END LOOP;
1120
1121 CLOSE c_app_periods;
1122
1123 IF l_approval_array.count = 0
1124 THEN
1125 RETURN;
1126 END IF;
1127
1128 --adv search
1129 IF p_adv_search IS NOT NULL
1130 THEN
1131 if g_debug then
1132 hr_utility.set_location(g_package||l_proc, 90);
1133 end if;
1134 adv_search(
1135 p_block_ids => get_block_ids(l_approval_array)
1136 ,p_adv_search => p_adv_search
1137 ,p_selected_ids => l_selected_ids
1138 );
1139
1140 if g_debug then
1141 hr_utility.set_location(g_package||l_proc, 100);
1142 end if;
1143
1144 IF l_selected_ids.count > 0
1145 THEN
1146 if g_debug then
1147 hr_utility.set_location(g_package||l_proc, 110);
1148 end if;
1149
1150 --populate p_approval_array and return
1151 l_index := l_approval_array.first;
1152
1153 LOOP
1154 EXIT WHEN NOT l_approval_array.exists(l_index);
1155
1156 IF is_selected(l_selected_ids,
1157 l_approval_array(l_index).time_building_block_id)
1158 THEN
1159
1160 add_records(p_approval_array, l_approval_array(l_index));
1161 END IF;
1162
1163 l_index := l_approval_array.next(l_index);
1164
1165 END LOOP;
1166
1167 if g_debug then
1168 hr_utility.set_location(g_package||l_proc, 120);
1169 end if;
1170 END IF;
1171
1172 ELSE
1173 if g_debug then
1174 hr_utility.set_location(g_package||l_proc, 130);
1175 end if;
1176 --simple search
1177 p_approval_array := l_approval_array;
1178
1179 if g_debug then
1180 hr_utility.set_location(g_package||l_proc, 140);
1181 end if;
1182 END IF;
1183
1184 if g_debug then
1185 hr_utility.set_location(g_package||l_proc, 150);
1186 end if;
1187 END get_approval_history;
1188
1189 PROCEDURE get_approval_notifications(
1190 p_approver_id IN NUMBER
1191 ,p_approval_array OUT NOCOPY HXC_NOTIFICATION_TABLE_TYPE
1192 ,p_resource_id IN VARCHAR2
1193 ,p_from_date IN VARCHAR2
1194 ,p_to_date IN VARCHAR2
1195 ,p_adv_search IN VARCHAR2
1196 ,p_mode IN VARCHAR2 DEFAULT 'PENDING'
1197 )
1198 IS
1199 BEGIN
1200 IF p_mode = 'PENDING'
1201 THEN
1202 get_open_notifications(
1203 p_approver_id => p_approver_id
1204 ,p_approval_array => p_approval_array
1205 ,p_resource_id => p_resource_id
1206 ,p_from_date => p_from_date
1207 ,p_to_date => p_to_date
1208 ,p_adv_search => p_adv_search
1209 );
1210 ELSE
1211 get_approval_history(
1212 p_approver_id => p_approver_id
1213 ,p_approval_array => p_approval_array
1214 ,p_resource_id => p_resource_id
1215 ,p_from_date => p_from_date
1216 ,p_to_date => p_to_date
1220 END get_approval_notifications;
1217 ,p_adv_search => p_adv_search
1218 );
1219 END IF;
1221
1222 END hxc_approval_utilities;
1223