[Home] [Help]
PACKAGE BODY: APPS.HXC_APPROVAL_WF_PKG
Source
1 PACKAGE BODY hxc_approval_wf_pkg AS
2 /* $Header: hxcapprwf.pkb 120.50.12020000.5 2012/12/13 12:53:29 asrajago ship $ */
3 --
4 -- Possible Notification Status Constants.
5 --
6 c_not_notified constant varchar2(12) := 'NOT_NOTIFIED';
7 c_notified constant varchar2(8) := 'NOTIFIED';
8 c_finished constant varchar2(8) := 'FINISHED';
9 g_debug BOOLEAN :=hr_utility.debug_enabled;
10
11 g_trace VARCHAR2(2000);
12
13 TYPE approval_comp IS RECORD(
14 approval_comp_id hxc_approval_comps.approval_comp_id%TYPE
15 ,object_version_number hxc_approval_comps.object_version_number%TYPE
16 ,approval_mechanism hxc_approval_comps.approval_mechanism%TYPE
17 ,approval_mechanism_id hxc_approval_comps.approval_mechanism_id%TYPE
18 ,wf_item_type hxc_approval_comps.wf_item_type%TYPE
19 ,wf_name hxc_approval_comps.wf_name%TYPE
20 ,time_category_id hxc_approval_comps.time_category_id%TYPE
21 ,approval_order hxc_approval_comps.approval_order%TYPE
22 );
23
24
25 TYPE approval_attribute is RECORD(
26 time_recipient_id VARCHAR2(150)
27 ,item_key VARCHAR2(150)
28 --all the following fields are not needed. they are here just so custom code won't break
29 ,approver_id VARCHAR2(150)
30 ,notified_status VARCHAR2(150)
31 ,approved_time VARCHAR2(150)
32 ,approver_comment VARCHAR2(150)
33 ,approval_status VARCHAR2(150)
34 );
35
36 TYPE block_info IS RECORD(
37 block_id hxc_time_building_blocks.time_building_block_id%TYPE
38 ,block_ovn hxc_time_building_blocks.object_version_number%TYPE
39 ,added VARCHAR2(1)
40 );
41
42 TYPE block_table IS TABLE OF
43 block_info
44 INDEX BY BINARY_INTEGER;
45
46 ------ Project manager changes
47 g_tab_project_id hxc_proj_manager_approval_pkg.tab_project_id;
48
49 g_block_exist_for_ap varchar2(1);
50
51 -- Added for bug 9076079
52 TYPE old_item_key_rec IS RECORD(is_diff_tc VARCHAR2(1));
53
54 TYPE old_item_key_tab IS TABLE OF
55 old_item_key_rec
56 INDEX BY BINARY_INTEGER;
57
58 g_old_item_key old_item_key_tab;
59
60
61 FUNCTION get_creation_date(
62 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
63 ,p_app_ovn hxc_time_building_blocks.object_version_number%TYPE
64 )
65 RETURN DATE
66 IS
67 CURSOR c_creation_date(
68 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
69 ,p_app_ovn hxc_time_building_blocks.object_version_number%TYPE
70 )
71 IS
72 SELECT creation_date
73 FROM hxc_time_building_blocks
74 WHERE time_building_block_id = p_app_id
75 AND object_version_number = p_app_ovn;
76
77 l_creation_date hxc_time_building_blocks.creation_date%TYPE := NULL;
78 BEGIN
79 OPEN c_creation_date(
80 p_app_id => p_app_id
81 ,p_app_ovn => p_app_ovn
82 );
83
84 FETCH c_creation_date INTO l_creation_date;
85 CLOSE c_creation_date;
86
87 RETURN l_creation_date;
88
89 END get_creation_date;
90
91
92
93 --this procedure gets all the detail blocks associated with
94 --timecard p_timecard_id and also fall between p_start_time
95 --and p_stop_time of an application period
96
97 -- Bug 10164135
98 -- Added a new param to process ELP specific timecards. Default Value 0 so
99 -- that existing functionality is untouched.
100 PROCEDURE get_detail_blocks(
101 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
102 ,p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
103 ,p_start_time IN hxc_time_building_blocks.start_time%TYPE
104 ,p_stop_time IN hxc_time_building_blocks.stop_time%TYPE
105 ,p_detail_blocks IN OUT NOCOPY block_table
106 ,p_new_detail_blocks IN OUT NOCOPY hxc_block_table_type
107 ,p_time_recipient_id IN NUMBER DEFAULT 0
108 )
109 IS
110 CURSOR c_detail_blocks(
111 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
112 ,p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
113 ,p_start_time hxc_time_building_blocks.start_time%TYPE
114 ,p_stop_time hxc_time_building_blocks.stop_time%TYPE
115 )
116 IS
117 SELECT
118 details.TIME_BUILDING_BLOCK_ID,
119 details.TYPE,
120 details.MEASURE,
121 details.UNIT_OF_MEASURE,
122 DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.start_time),
123 FND_DATE.DATE_TO_CANONICAL(days.start_time) ) CANONICAL_START_TIME,
124 DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.stop_time),
125 FND_DATE.DATE_TO_CANONICAL(days.stop_time) ) CANONICAL_STOP_TIME,
126 details.PARENT_BUILDING_BLOCK_ID,
127 NULL PARENT_IS_NEW,
128 details.SCOPE,
129 details.OBJECT_VERSION_NUMBER,
130 details.APPROVAL_STATUS,
131 details.RESOURCE_ID,
132 details.RESOURCE_TYPE,
133 details.APPROVAL_STYLE_ID,
134 FND_DATE.DATE_TO_CANONICAL(details.date_from) CANONICAL_DATE_FROM,
135 FND_DATE.DATE_TO_CANONICAL(details.date_to) CANONICAL_DATE_TO,
136 details.COMMENT_TEXT,
137 details.PARENT_BUILDING_BLOCK_OVN,
138 'N' NEW,
139 'N' CHANGED,
140 'N' PROCESS,
141 details.APPLICATION_SET_ID,
142 details.TRANSLATION_DISPLAY_KEY
143 FROM hxc_time_building_blocks days
144 ,hxc_time_building_blocks details
145 WHERE days.parent_building_block_id = p_timecard_id
146 AND days.parent_building_block_ovn = p_timecard_ovn
147 AND days.scope = 'DAY'
148 AND TRUNC(days.start_time) BETWEEN TRUNC(p_start_time) AND TRUNC(p_stop_time)
149 AND days.date_to = hr_general.end_of_time
150 AND details.scope = 'DETAIL'
151 AND details.parent_building_block_id = days.time_building_block_id
152 AND details.parent_building_block_ovn = days.object_version_number
153 AND details.date_to = hr_general.end_of_time;
154
155 -- Bug 10164135
156 -- New cursor mimicking the above one, but with a new EXISTS clause
157 -- on Application set.
158 CURSOR c_detail_blocks2(
159 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
160 ,p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
161 ,p_start_time hxc_time_building_blocks.start_time%TYPE
162 ,p_stop_time hxc_time_building_blocks.stop_time%TYPE
163 ,p_time_recipient_id NUMBER
164 )
165 IS
166 SELECT
167 details.TIME_BUILDING_BLOCK_ID,
168 details.TYPE,
169 details.MEASURE,
170 details.UNIT_OF_MEASURE,
171 DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.start_time),
172 FND_DATE.DATE_TO_CANONICAL(days.start_time) ) CANONICAL_START_TIME,
173 DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.stop_time),
174 FND_DATE.DATE_TO_CANONICAL(days.stop_time) ) CANONICAL_STOP_TIME,
175 details.PARENT_BUILDING_BLOCK_ID,
176 NULL PARENT_IS_NEW,
177 details.SCOPE,
178 details.OBJECT_VERSION_NUMBER,
179 details.APPROVAL_STATUS,
180 details.RESOURCE_ID,
181 details.RESOURCE_TYPE,
182 details.APPROVAL_STYLE_ID,
183 FND_DATE.DATE_TO_CANONICAL(details.date_from) CANONICAL_DATE_FROM,
184 FND_DATE.DATE_TO_CANONICAL(details.date_to) CANONICAL_DATE_TO,
185 details.COMMENT_TEXT,
186 details.PARENT_BUILDING_BLOCK_OVN,
187 'N' NEW,
188 'N' CHANGED,
189 'N' PROCESS,
190 details.APPLICATION_SET_ID,
191 details.TRANSLATION_DISPLAY_KEY
192 FROM hxc_time_building_blocks days
193 ,hxc_time_building_blocks details
194 WHERE days.parent_building_block_id = p_timecard_id
195 AND days.parent_building_block_ovn = p_timecard_ovn
196 AND days.scope = 'DAY'
197 AND TRUNC(days.start_time) BETWEEN TRUNC(p_start_time) AND TRUNC(p_stop_time)
198 AND days.date_to = hr_general.end_of_time
199 AND details.scope = 'DETAIL'
200 AND details.parent_building_block_id = days.time_building_block_id
201 AND details.parent_building_block_ovn = days.object_version_number
202 AND details.date_to = hr_general.end_of_time
203 AND EXISTS ( SELECT 1
204 FROM hxc_application_set_comps_v hasc
205 WHERE hasc.application_set_id = details.application_set_id
206 AND hasc.time_recipient_id = p_time_recipient_id) ;
207
208
209 l_cursor_blocks c_detail_blocks%ROWTYPE;
210
211 l_detail_blocks block_table;
212 l_new_detail_blocks hxc_block_table_type := hxc_block_table_type ();
213 l_block_index PLS_INTEGER := 1;
214
215 BEGIN
216
217 -- Bug 10164135
218 -- If no recipient id is passed follow normal path.
219 IF p_time_recipient_id = 0
220 THEN
221 OPEN c_detail_blocks(
222 p_timecard_id => p_timecard_id
223 ,p_timecard_ovn => p_timecard_ovn
224 ,p_start_time => p_start_time
225 ,p_stop_time => p_stop_time
226 );
227
228 LOOP
229 FETCH c_detail_blocks INTO l_cursor_blocks;
230 EXIT WHEN c_detail_blocks%NOTFOUND;
231
232 l_detail_blocks(l_block_index).block_id := l_cursor_blocks.time_building_block_id;
233 l_detail_blocks(l_block_index).block_ovn := l_cursor_blocks.object_version_number;
234 l_detail_blocks(l_block_index).added := 'N';
235
236 l_new_detail_blocks.extend();
237 l_new_detail_blocks(l_block_index) := HXC_BLOCK_TYPE(l_cursor_blocks.TIME_BUILDING_BLOCK_ID,
238 l_cursor_blocks.TYPE,
239 l_cursor_blocks.MEASURE,
240 l_cursor_blocks.UNIT_OF_MEASURE,
241 l_cursor_blocks.CANONICAL_START_TIME,
242 l_cursor_blocks.CANONICAL_STOP_TIME,
243 l_cursor_blocks.PARENT_BUILDING_BLOCK_ID,
244 l_cursor_blocks.PARENT_IS_NEW,
245 l_cursor_blocks.SCOPE,
246 l_cursor_blocks.OBJECT_VERSION_NUMBER,
247 l_cursor_blocks.APPROVAL_STATUS,
248 l_cursor_blocks.RESOURCE_ID,
249 l_cursor_blocks.RESOURCE_TYPE,
250 l_cursor_blocks.APPROVAL_STYLE_ID,
251 l_cursor_blocks.CANONICAL_DATE_FROM,
252 l_cursor_blocks.CANONICAL_DATE_TO,
253 l_cursor_blocks.COMMENT_TEXT,
254 l_cursor_blocks.PARENT_BUILDING_BLOCK_OVN,
255 l_cursor_blocks.NEW,
256 l_cursor_blocks.CHANGED,
257 l_cursor_blocks.PROCESS,
258 l_cursor_blocks.APPLICATION_SET_ID,
259 l_cursor_blocks.TRANSLATION_DISPLAY_KEY);
260
261 l_block_index := l_block_index + 1;
262 END LOOP;
263
264 CLOSE c_detail_blocks;
265
266 -- Bug 10164135
267 -- When there is a valid recipient, pick details with respect to Application set.
268 ELSE
269 OPEN c_detail_blocks2(
270 p_timecard_id => p_timecard_id
271 ,p_timecard_ovn => p_timecard_ovn
272 ,p_start_time => p_start_time
273 ,p_stop_time => p_stop_time
274 ,p_time_recipient_id => p_time_recipient_id
275 );
276
277 LOOP
278 FETCH c_detail_blocks2 INTO l_cursor_blocks;
279 EXIT WHEN c_detail_blocks2%NOTFOUND;
280
281 l_detail_blocks(l_block_index).block_id := l_cursor_blocks.time_building_block_id;
282 l_detail_blocks(l_block_index).block_ovn := l_cursor_blocks.object_version_number;
283 l_detail_blocks(l_block_index).added := 'N';
284
285 l_new_detail_blocks.extend();
286 l_new_detail_blocks(l_block_index) := HXC_BLOCK_TYPE(l_cursor_blocks.TIME_BUILDING_BLOCK_ID,
287 l_cursor_blocks.TYPE,
288 l_cursor_blocks.MEASURE,
289 l_cursor_blocks.UNIT_OF_MEASURE,
290 l_cursor_blocks.CANONICAL_START_TIME,
291 l_cursor_blocks.CANONICAL_STOP_TIME,
292 l_cursor_blocks.PARENT_BUILDING_BLOCK_ID,
293 l_cursor_blocks.PARENT_IS_NEW,
294 l_cursor_blocks.SCOPE,
295 l_cursor_blocks.OBJECT_VERSION_NUMBER,
296 l_cursor_blocks.APPROVAL_STATUS,
297 l_cursor_blocks.RESOURCE_ID,
298 l_cursor_blocks.RESOURCE_TYPE,
299 l_cursor_blocks.APPROVAL_STYLE_ID,
300 l_cursor_blocks.CANONICAL_DATE_FROM,
301 l_cursor_blocks.CANONICAL_DATE_TO,
302 l_cursor_blocks.COMMENT_TEXT,
303 l_cursor_blocks.PARENT_BUILDING_BLOCK_OVN,
304 l_cursor_blocks.NEW,
305 l_cursor_blocks.CHANGED,
306 l_cursor_blocks.PROCESS,
307 l_cursor_blocks.APPLICATION_SET_ID,
308 l_cursor_blocks.TRANSLATION_DISPLAY_KEY);
309
310 l_block_index := l_block_index + 1;
311 END LOOP;
312
313 CLOSE c_detail_blocks2;
314 END IF;
315
316 p_detail_blocks := l_detail_blocks;
317 p_new_detail_blocks := l_new_detail_blocks;
318
319
320 END get_detail_blocks;
321
322
323 --this function returns all the attributes associated with the detail blocks
324 PROCEDURE get_detail_attributes(
325 p_detail_blocks IN block_table,
326 p_detail_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info,
327 p_new_detail_attributes IN OUT NOCOPY hxc_attribute_table_type
328 )
329
330 IS
331 l_attribute_index PLS_INTEGER := 1;
332 l_new_attributes hxc_attribute_table_type := hxc_attribute_table_type ();
333 l_detail_attributes hxc_self_service_time_deposit.building_block_attribute_info;
334 l_block_index PLS_INTEGER;
335
336 CURSOR c_block_attributes(
337 p_detail_id hxc_time_building_blocks.time_building_block_id%TYPE
338 ,p_detail_ovn hxc_time_building_blocks.object_version_number%TYPE
339 )
340 IS
341 select a.time_attribute_id
342 ,au.time_building_block_id building_block_id
343 ,bbit.bld_blk_info_type
344 ,a.attribute_category
345 ,a.attribute1
346 ,a.attribute2
347 ,a.attribute3
348 ,a.attribute4
349 ,a.attribute5
350 ,a.attribute6
351 ,a.attribute7
352 ,a.attribute8
353 ,a.attribute9
354 ,a.attribute10
355 ,a.attribute11
356 ,a.attribute12
357 ,a.attribute13
358 ,a.attribute14
359 ,a.attribute15
360 ,a.attribute16
361 ,a.attribute17
362 ,a.attribute18
363 ,a.attribute19
364 ,a.attribute20
365 ,a.attribute21
366 ,a.attribute22
367 ,a.attribute23
368 ,a.attribute24
369 ,a.attribute25
370 ,a.attribute26
371 ,a.attribute27
372 ,a.attribute28
373 ,a.attribute29
374 ,a.attribute30
375 ,a.bld_blk_info_type_id
376 ,a.object_version_number
377 ,'N' NEW
378 ,'N' CHANGED
379 ,'N' PROCESS
380 from hxc_time_attributes a,
381 hxc_time_attribute_usages au,
382 hxc_bld_blk_info_types bbit
383 where au.time_building_block_id = p_detail_id
384 and au.time_building_block_ovn = p_detail_ovn
385 and au.time_attribute_id = a.time_attribute_id
386 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
387
388 l_cursor_attributes c_block_attributes%ROWTYPE;
389
390
391 BEGIN
392 l_block_index := p_detail_blocks.first;
393
394 LOOP
395 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
396
397 OPEN c_block_attributes(
398 p_detail_id => p_detail_blocks(l_block_index).block_id
399 ,p_detail_ovn => p_detail_blocks(l_block_index).block_ovn
400 );
401
402 LOOP
403 FETCH c_block_attributes INTO l_detail_attributes(l_attribute_index);
404 EXIT WHEN c_block_attributes%NOTFOUND;
405
406
407 -- populate new structure
408 l_new_attributes.extend();
409 l_new_attributes(l_attribute_index) := HXC_ATTRIBUTE_TYPE (l_detail_attributes(l_attribute_index).TIME_ATTRIBUTE_ID,
410 l_detail_attributes(l_attribute_index).BUILDING_BLOCK_ID,
411 l_detail_attributes(l_attribute_index).ATTRIBUTE_CATEGORY,
412 l_detail_attributes(l_attribute_index).ATTRIBUTE1,
413 l_detail_attributes(l_attribute_index).ATTRIBUTE2,
414 l_detail_attributes(l_attribute_index).ATTRIBUTE3,
415 l_detail_attributes(l_attribute_index).ATTRIBUTE4,
416 l_detail_attributes(l_attribute_index).ATTRIBUTE5,
417 l_detail_attributes(l_attribute_index).ATTRIBUTE6,
418 l_detail_attributes(l_attribute_index).ATTRIBUTE7,
419 l_detail_attributes(l_attribute_index).ATTRIBUTE8,
420 l_detail_attributes(l_attribute_index).ATTRIBUTE9,
421 l_detail_attributes(l_attribute_index).ATTRIBUTE10,
422 l_detail_attributes(l_attribute_index).ATTRIBUTE11,
423 l_detail_attributes(l_attribute_index).ATTRIBUTE12,
424 l_detail_attributes(l_attribute_index).ATTRIBUTE13,
425 l_detail_attributes(l_attribute_index).ATTRIBUTE14,
426 l_detail_attributes(l_attribute_index).ATTRIBUTE15,
427 l_detail_attributes(l_attribute_index).ATTRIBUTE16,
428 l_detail_attributes(l_attribute_index).ATTRIBUTE17,
429 l_detail_attributes(l_attribute_index).ATTRIBUTE18,
430 l_detail_attributes(l_attribute_index).ATTRIBUTE19,
431 l_detail_attributes(l_attribute_index).ATTRIBUTE20,
432 l_detail_attributes(l_attribute_index).ATTRIBUTE21,
433 l_detail_attributes(l_attribute_index).ATTRIBUTE22,
434 l_detail_attributes(l_attribute_index).ATTRIBUTE23,
435 l_detail_attributes(l_attribute_index).ATTRIBUTE24,
436 l_detail_attributes(l_attribute_index).ATTRIBUTE25,
437 l_detail_attributes(l_attribute_index).ATTRIBUTE26,
438 l_detail_attributes(l_attribute_index).ATTRIBUTE27,
439 l_detail_attributes(l_attribute_index).ATTRIBUTE28,
440 l_detail_attributes(l_attribute_index).ATTRIBUTE29,
441 l_detail_attributes(l_attribute_index).ATTRIBUTE30,
442 l_detail_attributes(l_attribute_index).BLD_BLK_INFO_TYPE_ID,
443 l_detail_attributes(l_attribute_index).OBJECT_VERSION_NUMBER,
444 l_detail_attributes(l_attribute_index).NEW,
445 l_detail_attributes(l_attribute_index).CHANGED,
446 l_detail_attributes(l_attribute_index).BLD_BLK_INFO_TYPE,
447 NULL,
448 1 );
449
450 l_attribute_index := l_attribute_index + 1;
451 END LOOP;
452
453 CLOSE c_block_attributes;
454
455 l_block_index := p_detail_blocks.next(l_block_index);
456 END LOOP;
457
458 p_detail_attributes := l_detail_attributes;
459 p_new_detail_attributes := l_new_attributes;
460
461 END get_detail_attributes;
462
463 Function same_block
464 (p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
465 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
466 p_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
467 ) return boolean is
468
469 cursor c_block
470 (p_app_id in hxc_time_building_blocks.time_building_block_id%TYPE,
471 p_block_id in hxc_time_building_blocks.time_building_block_id%TYPE
472 ) is
473 select max(time_building_block_ovn)
474 from hxc_ap_detail_links
475 where application_period_id = p_app_id
476 and time_building_block_id = p_block_id;
477
478 cursor c_test_translation_key
479 (p_block1_id in hxc_time_building_blocks.time_building_block_id%TYPE,
480 p_block1_ovn in hxc_time_building_blocks.object_version_number%TYPE,
481 p_block2_ovn in hxc_time_building_blocks.object_version_number%TYPE
482 ) is
483 select tbb2.object_version_number,
484 tbb1.translation_display_key, -- 9747521
485 tbb2.translation_display_key -- 9747521
486 from hxc_time_building_blocks tbb1,
487 hxc_time_building_blocks tbb2
488 where tbb1.time_building_block_id = p_block1_id
489 and tbb1.time_building_block_id = tbb2.time_building_block_id
490 and tbb1.object_version_number = p_block1_ovn
491 and tbb2.object_version_number = p_block2_ovn
492 and tbb1.type = tbb2.type
493 and nvl(tbb1.measure,hr_api.g_number) = nvl(tbb2.measure,hr_api.g_number)
494 and nvl(tbb1.unit_of_measure,hr_api.g_varchar2) = nvl(tbb2.unit_of_measure,hr_api.g_varchar2)
495 and nvl(tbb1.start_time,hr_api.g_date) = nvl(tbb2.start_time,hr_api.g_date)
496 and nvl(tbb1.stop_time,hr_api.g_date) = nvl(tbb2.stop_time,hr_api.g_date)
497 and tbb1.approval_status = tbb2.approval_status
498 and nvl(tbb1.approval_style_id,hr_api.g_number) = nvl(tbb2.approval_style_id,hr_api.g_number)
499 and nvl(tbb1.comment_text,hr_api.g_varchar2) = nvl(tbb2.comment_text,hr_api.g_varchar2)
500 and nvl(tbb1.application_set_id,hr_api.g_number) = nvl(tbb1.application_set_id,hr_api.g_number)
501 and nvl(tbb1.data_set_id,hr_api.g_number) = nvl(tbb1.data_set_id,hr_api.g_number)
502 and nvl(tbb1.translation_display_key,hr_api.g_varchar2) <> nvl(tbb2.translation_display_key,hr_api.g_varchar2);
503
504 l_block_ovn hxc_ap_detail_links.time_building_block_ovn%type;
505 l_trans_disp_key1 hxc_time_building_blocks.translation_display_key%TYPE; -- Bug 9747521
506 l_trans_disp_key2 hxc_time_building_blocks.translation_display_key%TYPE; -- Bug 9747521
507
508 Begin
509 open c_block(p_app_id, p_block_id);
510 fetch c_block into l_block_ovn;
511
512 if g_debug then
513 hr_utility.trace('OTL:Entered same_block');
514 hr_utility.trace('OTL:p_app_id = '||p_app_id);
515 hr_utility.trace('OTL:p_block_id = '||p_block_id);
516 hr_utility.trace('OTL:p_block_ovn = '||p_block_ovn);
517 hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
518 end if;
519
520 if (c_block%notfound) then
521 close c_block;
522 return false;
523 elsif (p_block_ovn = l_block_ovn) then
524 close c_block;
525 return true;
526 else
527 close c_block;
528 --
529 -- check to see if it is just the translation display key
530 -- that is different
531 --
532 open c_test_translation_key(p_block_id,p_block_ovn,l_block_ovn);
533 fetch c_test_translation_key into l_block_ovn, l_trans_disp_key1, l_trans_disp_key2;
534 if(c_test_translation_key%found) then
535 close c_test_translation_key;
536 if g_debug then
537 hr_utility.trace('OTL: translation display key%FOUND');
538 hr_utility.trace('OTL:l_block_ovn = '||l_block_ovn);
539 hr_utility.trace('OTL:l_trans_disp_key1 = '||l_trans_disp_key1);
540 hr_utility.trace('OTL:l_trans_disp_key2 = '||l_trans_disp_key2);
541 end if;
542 -- Added this IF block for bug 9747521
543 IF ((l_trans_disp_key1 IS NULL and l_trans_disp_key2 IS NOT NULL)
544 OR
545 (l_trans_disp_key1 IS NOT NULL and l_trans_disp_key2 IS NULL)) THEN
546 return false;
547 END IF;
548
549 return true;
550 else
551 close c_test_translation_key;
552 if g_debug then
553 hr_utility.trace('OTL: translation display key%NOTFOUND');
554 end if;
555
556 return false;
557 end if;
558 end if;
559
560 End same_block;
561
562 function no_blocks(
563 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
564 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
565 )
566
567 RETURN NUMBER
568 IS
569 CURSOR c_no_blocks(
570 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
571 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
572 )
573 IS
574 SELECT count(p_app_id)
575 FROM hxc_ap_detail_links apdetail
576 ,hxc_time_building_blocks days
577 ,hxc_time_building_blocks details
578 WHERE apdetail.application_period_id = p_app_id
579 AND days.parent_building_block_id = p_timecard_id
580 AND details.parent_building_block_id = days.time_building_block_id
581 AND details.time_building_block_id = apdetail.time_building_block_id
582 AND details.object_version_number = apdetail.time_building_block_ovn
583 AND details.date_to <> hr_general.end_of_time
584 and not exists(
585 select 1
586 from hxc_time_building_blocks details2
587 where details2.time_building_block_id = details.time_building_block_id
588 and details2.date_to = hr_general.end_of_time
589 );
590
591 l_count number := 0;
592
593 BEGIN
594 OPEN c_no_blocks(p_app_id, p_timecard_id);
595 FETCH c_no_blocks INTO l_count;
596 CLOSE c_no_blocks;
597
598 RETURN l_count;
599 END no_blocks;
600
601 FUNCTION changed(
602 p_detail_blocks IN OUT NOCOPY block_table
603 ,p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
604 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
605 ,p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
606 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
607 )
608 RETURN BOOLEAN
609 IS
610 l_block_index NUMBER;
611 l_count number := 0;
612 l_same boolean := true;
613
614 l_proc varchar2(50) := 'HXC_APPROVAL_WF_PKG.changed';
615 BEGIN
616 g_debug:=hr_utility.debug_enabled;
617 if g_debug then
618 hr_utility.set_location(l_proc, 10);
619 end if;
620
621 IF p_time_category_id IS NULL OR p_time_category_id = 0
622 THEN
623 l_block_index := p_detail_blocks.first;
624 LOOP
625 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
626 if g_debug then
627 hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
628 hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
629 hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
630 end if;
631
632 IF p_detail_blocks(l_block_index).added <> 'Y'
633 THEN
634 l_count := l_count + 1;
635 if g_debug then
636 hr_utility.set_location(l_proc, 20);
637 end if;
638
639 IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
640 , p_detail_blocks(l_block_index).block_ovn)
641 THEN
642 if g_debug then
643 hr_utility.set_location(l_proc, 30);
644 end if;
645
646 g_block_exist_for_ap := 'Y';
647
648 RETURN TRUE;
649 END IF;
650
651 END IF;
652
653 l_block_index := p_detail_blocks.next(l_block_index);
654 END LOOP;
655
656 ELSE
657 if g_debug then
658 hr_utility.set_location(l_proc, 40);
659 end if;
660
661 hxc_time_category_utils_pkg.initialise_time_category(
662 p_time_category_id => p_time_category_id
663 ,p_tco_att => p_attributes
664 );
665
666 l_block_index := p_detail_blocks.first;
667 LOOP
668 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
669 IF hxc_time_category_utils_pkg.chk_tc_bb_ok
670 (p_detail_blocks(l_block_index).block_id)THEN
671 p_detail_blocks(l_block_index).added := 'Y';
672 l_count := l_count + 1;
673
674 IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
675 , p_detail_blocks(l_block_index).block_ovn)
676 THEN
677 if g_debug then
678 hr_utility.set_location(l_proc, 60);
679 end if;
680
681 g_block_exist_for_ap := 'Y';
682
683 RETURN TRUE;
684 END IF;
685
686 END IF;
687
688 l_block_index := p_detail_blocks.next(l_block_index);
689 END LOOP;
690 END IF;
691
692 IF no_blocks(p_app_id, p_timecard_id) = 0
693 THEN
694 if g_debug then
695 hr_utility.trace('number not changed');
696 end if;
697 RETURN FALSE;
698 ELSE
699 if g_debug then
700 hr_utility.trace('number changed');
701 end if;
702 RETURN TRUE;
703 END IF;
704 END changed;
705
706 PROCEDURE remove_ap_detail_links(
707 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
708 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
709 )
710 IS
711 CURSOR c_detail_blocks(
712 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
713 )
714 IS
715 SELECT details.time_building_block_id
716 ,details.object_version_number
717 FROM hxc_time_building_blocks days
718 ,hxc_time_building_blocks details
719 WHERE days.parent_building_block_id = p_timecard_id
720 AND details.parent_building_block_id = days.time_building_block_id
721 AND days.scope = 'DAY'
722 AND details.scope = 'DETAIL';
723
724 CURSOR c_old_blocks(
725 p_app_period hxc_time_building_blocks.time_building_block_id%TYPE
726 )
727 IS
728 SELECT details.time_building_block_id
729 ,details.object_version_number
730 FROM hxc_ap_detail_links apdetail
731 ,hxc_time_building_blocks details
732 WHERE apdetail.application_period_id = p_app_period
733 AND apdetail.time_building_block_id = details.time_building_block_id
734 AND apdetail.time_building_block_ovn = details.object_version_number
735 AND details.date_to <> hr_general.end_of_time;
736
737
738 l_detail_id hxc_time_building_blocks.time_building_block_id%TYPE;
739 l_detail_ovn hxc_time_building_blocks.object_version_number%TYPE;
740 BEGIN
741 OPEN c_detail_blocks(p_timecard_id);
742
743 LOOP
744 FETCH c_detail_blocks INTO l_detail_id, l_detail_ovn;
745 EXIT WHEN c_detail_blocks%NOTFOUND;
746
747 delete from hxc_ap_detail_links
748 where time_building_block_id = l_detail_id
749 and time_building_block_ovn = l_detail_ovn
750 and application_period_id = p_app_id;
751
752 END LOOP;
753
754 CLOSE c_detail_blocks;
755
756 OPEN c_old_blocks(p_app_id);
757 LOOP
758 FETCH c_old_blocks INTO l_detail_id, l_detail_ovn;
759 EXIT WHEN c_old_blocks%NOTFOUND;
760
761 delete from hxc_ap_detail_links
762 where time_building_block_id = l_detail_id
763 and time_building_block_ovn = l_detail_ovn
764 and application_period_id = p_app_id;
765 END LOOP;
766
767 CLOSE c_old_blocks;
768
769 END remove_ap_detail_links;
770
771
772 FUNCTION is_empty(
773 p_detail_blocks IN OUT NOCOPY block_table
774 ,p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
775 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
776 )
777 RETURN BOOLEAN
778 IS
779 l_block_index NUMBER;
780 BEGIN
781
782 --
783 -- Check the attributes are ok
784
785 hxc_time_category_utils_pkg.initialise_time_category(
786 p_time_category_id => p_time_category_id
787 ,p_tco_att => p_attributes
788 );
789
790 l_block_index := p_detail_blocks.first;
791 LOOP
792 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
793
794 if g_debug then
795 hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
796 hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
797 hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
798 end if;
799
800 IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
801 p_detail_blocks(l_block_index).block_id
802 )
803 THEN
804 RETURN FALSE;
805 END IF;
806
807 l_block_index := p_detail_blocks.next(l_block_index);
808 END LOOP;
809
810 RETURN TRUE;
811 END is_empty;
812
813
814
815 PROCEDURE link_ap_details(
816 p_detail_blocks IN OUT NOCOPY block_table
817 ,p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
818 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
819 ,p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
820 )
821 IS
822 l_block_index NUMBER;
823 BEGIN
824
825 hxc_time_category_utils_pkg.initialise_time_category(
826 p_time_category_id => p_time_category_id
827 ,p_tco_att => p_attributes
828 );
829
830 l_block_index := p_detail_blocks.first;
831 LOOP
832 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
833
834 if g_debug then
835 hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
836 hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
837 hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
838 end if;
839
840 IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
841 p_detail_blocks(l_block_index).block_id
842 )
843 THEN
844 --set added flag
845 --we use this flag to find all the category 0 blocks
846 p_detail_blocks(l_block_index).added := 'Y';
847
848 --insert a line in hxc_detail_summary;
849 hxc_ap_detail_links_pkg.insert_summary_row(
850 p_app_id
851 ,p_detail_blocks(l_block_index).block_id
852 ,p_detail_blocks(l_block_index).block_ovn
853 );
854
855 if g_debug then
856 hr_utility.trace('linked!');
857 end if;
858
859 END IF;
860
861 l_block_index := p_detail_blocks.next(l_block_index);
862 END LOOP;
863
864 END link_ap_details;
865
866 PROCEDURE link_ap_details_all(
867 p_detail_blocks IN OUT NOCOPY block_table
868 ,p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
869 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
870 )
871 IS
872 l_block_index NUMBER;
873
874 BEGIN
875
876 if g_debug then
877 hr_utility.trace('in link_ap_details_all');
878 end if;
879
880 l_block_index := p_detail_blocks.first;
881 LOOP
882 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
883
884 if g_debug then
885 hr_utility.trace('block_id=' || p_detail_blocks(l_block_index).block_id);
886 hr_utility.trace('block_ovn=' || p_detail_blocks(l_block_index).block_ovn);
887 hr_utility.trace('added=' || p_detail_blocks(l_block_index).added);
888 end if;
889 l_block_index := p_detail_blocks.next(l_block_index);
890 END LOOP;
891
892 l_block_index := p_detail_blocks.first;
893
894 LOOP
895 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
896
897 IF p_time_category_id IS NULL
898 OR (p_time_category_id = 0 and p_detail_blocks(l_block_index).added <> 'Y')
899 THEN
900 --set added flag
901 --we use this flag to find all the category 0 blocks
902 if g_debug then
903 hr_utility.trace('inserting id=' || p_detail_blocks(l_block_index).block_id
904 || '|ovn=' || p_detail_blocks(l_block_index).block_ovn);
905 end if;
906
907 p_detail_blocks(l_block_index).added := 'Y';
908
909
910 --insert a line in hxc_detail_summary;
911 hxc_ap_detail_links_pkg.insert_summary_row(
912 p_app_id
913 ,p_detail_blocks(l_block_index).block_id
914 ,p_detail_blocks(l_block_index).block_ovn
915 );
916
917
918 END IF;
919
920 l_block_index := p_detail_blocks.next(l_block_index);
921
922 END LOOP;
923
924 if g_debug then
925 hr_utility.trace('end link_ap_details_all');
926 end if;
927 END link_ap_details_all;
928
929 FUNCTION get_person_id(
930 p_user_name IN fnd_user.user_name%TYPE
931 )
932 RETURN fnd_user.employee_id%TYPE
933 IS
934 CURSOR c_person_id(p_user_name fnd_user.user_name%TYPE)
935 IS
936 SELECT u.employee_id
937 FROM FND_USER u
938 WHERE u.user_name = p_user_name;
939
940 l_person_id fnd_user.employee_id%TYPE;
941 BEGIN
942 OPEN c_person_id(p_user_name);
943
944 FETCH c_person_id INTO l_person_id;
945 IF c_person_id%NOTFOUND
946 THEN
947 CLOSE c_person_id;
948
949 --raise; ???
950 END IF;
951
952 CLOSE c_person_id;
953
954 RETURN l_person_id;
955 END get_person_id;
956
957
958 FUNCTION get_empty_attribute
959 RETURN hxc_time_attributes_api.timecard
960 IS
961 t_attributes hxc_time_attributes_api.timecard;
962
963 BEGIN
964 t_attributes.delete;
965 t_attributes(1).attribute_name := NULL;
966 t_attributes(1).attribute_value := NULL;
967 t_attributes(1).information_type := NULL;
968 t_attributes(1).column_name := NULL;
969 t_attributes(1).info_mapping_type := NULL;
970
971 RETURN t_attributes;
972
973 END get_empty_attribute;
974 Function find_mysterious_approver
975 (p_item_type in wf_items.item_type%type
976 ,p_item_key in wf_item_activity_statuses.item_key%type
977 ) return number is
978
979 cursor c_find_approver_role
980 (itemType in wf_items.item_type%type
981 ,itemKey in wf_item_activity_statuses.item_key%type) is
982 select wlr.orig_system, wlr.orig_system_id
983 from wf_notifications wn, wf_process_activities pa, wf_item_activity_statuses wias, wf_local_roles wlr
984 where pa.activity_name in('TC_APR_NOTIFICATION', 'TC_APR_NOTIFICATION_ABS')
985 and pa.activity_item_type = itemType
986 and pa.instance_id = wias.process_activity
987 and wias.notification_id = wn.notification_id
988 and wias.item_key = itemKey
989 and wlr.name = wn.recipient_role
990 and wias.item_type = pa.activity_item_type;
991
992 cursor c_find_employee_id
993 (userId in fnd_user.user_id%type) is
994 select employee_id
995 from fnd_user
996 where user_id = userId;
997
998 l_approver_id wf_local_roles.orig_system_id%type;
999 l_approver_system wf_local_roles.orig_system%type;
1000
1001
1002 Begin
1003
1004 open c_find_approver_role(p_item_type,p_item_key);
1005 fetch c_find_approver_role into l_approver_system, l_approver_id;
1006 if(c_find_approver_role%notfound) then
1007 close c_find_approver_role;
1008 l_approver_id := -1;
1009 else
1010 close c_find_approver_role;
1011 if((l_approver_system <> 'PER') AND (l_approver_system <> 'FND_USR')) then
1012 l_approver_id := -1;
1013 elsif(l_approver_system = 'FND_USR') then
1014 open c_find_employee_id(l_approver_id);
1015 fetch c_find_employee_id into l_approver_id;
1016 if (c_find_employee_id%notfound) then
1017 close c_find_employee_id;
1018 l_approver_id := -1;
1019 else
1020 close c_find_employee_id;
1021 end if;
1022 end if; -- other option is PER, and then it's already set properly
1023 end if;
1024
1025 return l_approver_id;
1026
1027 End find_mysterious_approver;
1028
1029 PROCEDURE update_latest_details(p_app_bb_id in number)
1030 is
1031
1032 l_bb_id number;
1033 l_bb_ovn number;
1034 l_other_app_id number;
1035
1036 cursor get_building_blocks(p_app_bb_id in number)
1037 is
1038 select time_building_block_id, time_building_block_ovn
1039 from hxc_ap_detail_links
1040 where application_period_id = p_app_bb_id;
1041
1042 cursor get_app_period(p_bb_id in number, p_bb_ovn in number ,p_app_bb_id in number)
1043 is
1044 select adl.application_period_id
1045 from hxc_ap_detail_links adl,
1046 hxc_app_period_summary haps
1047 where adl.time_building_block_id = p_bb_id
1048 and adl.time_building_block_ovn = p_bb_ovn
1049 and adl.application_period_id <> p_app_bb_id
1050 and adl.application_period_id = haps.application_period_id
1051 and haps.approval_status <> 'APPROVED';
1052 begin
1053
1054 open get_building_blocks(p_app_bb_id);
1055 fetch get_building_blocks into l_bb_id,l_bb_ovn;
1056
1057 LOOP
1058 exit when get_building_blocks%notfound;
1059
1060 open get_app_period(l_bb_id, l_bb_ovn, p_app_bb_id);
1061 fetch get_app_period into l_other_app_id;
1062
1063 IF get_app_period%notfound then
1064 update hxc_latest_details
1065 set last_update_date = sysdate
1066 where time_building_block_id = l_bb_id
1067 and object_version_number = l_bb_ovn;
1068
1069 -- Bug 14090430
1070 -- Added the following UPDATEs to take care of the
1071 -- tables for Upgraded Retrieval Process.
1072 update hxc_pa_latest_details
1073 set last_update_date = sysdate
1074 where time_building_block_id = l_bb_id
1075 and object_version_number = l_bb_ovn;
1076
1077 update hxc_pay_latest_details
1078 set last_update_date = sysdate
1079 where time_building_block_id = l_bb_id
1080 and object_version_number = l_bb_ovn;
1081
1082
1083 END IF;
1084 close get_app_period;
1085
1086 fetch get_building_blocks into l_bb_id, l_bb_ovn;
1087
1088 END LOOP;
1089
1090 close get_building_blocks;
1091
1092 END update_latest_details;
1093
1094 PROCEDURE update_app_period(
1095 itemtype IN varchar2,
1096 itemkey IN varchar2,
1097 actid IN number,
1098 funcmode IN varchar2,
1099 result IN OUT NOCOPY varchar2
1100 )
1101 IS
1102 t_attributes hxc_time_attributes_api.timecard;
1103 l_attribute approval_attribute;
1104 l_approver varchar2(150);
1105 l_user_name varchar2(150);
1106 l_appl_period_bb_id number;
1107 l_appl_period_bb_ovn number;
1108 l_tc_resource_id number;
1109 l_period_start_date date;
1110 l_period_end_date date;
1111 l_approval_status hxc_time_building_blocks.approval_status%type;
1112 l_approver_comment hxc_time_building_blocks.comment_text%TYPE;
1113 l_creation_date hxc_time_building_blocks.creation_date%TYPE;
1114 l_wf_item_type varchar2(500) := NULL;
1115 l_is_blank varchar2(10);
1116 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.update_appl_period';
1117
1118
1119 BEGIN
1120 g_debug:=hr_utility.debug_enabled;
1121 if g_debug then
1122 hr_utility.set_location(l_proc, 10);
1123 end if;
1124
1125 l_tc_resource_id := wf_engine.GetItemAttrNumber(
1126 itemtype => itemtype,
1127 itemkey => itemkey ,
1128 aname => 'RESOURCE_ID');
1129
1130
1131 if g_debug then
1132 hr_utility.set_location(l_proc, 30);
1133 end if;
1134
1135 l_period_start_date := wf_engine.GetItemAttrDate(
1136 itemtype => itemtype,
1137 itemkey => itemkey ,
1138 aname => 'APP_START_DATE');
1139
1140 if g_debug then
1141 hr_utility.set_location(l_proc, 40);
1142 end if;
1143
1144 l_period_end_date := wf_engine.GetItemAttrDate(
1145 itemtype => itemtype,
1146 itemkey => itemkey ,
1147 aname => 'APP_END_DATE');
1148
1149 if g_debug then
1150 hr_utility.set_location(l_proc, 50);
1151 end if;
1152
1153 l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
1154 itemtype => itemtype,
1155 itemkey => itemkey,
1156 aname => 'APP_BB_ID');
1157
1158 if g_debug then
1159 hr_utility.set_location(l_proc, 60);
1160 end if;
1161
1162 l_appl_period_bb_ovn := wf_engine.GetItemAttrNumber(
1163 itemtype => itemtype,
1164 itemkey => itemkey,
1165 aname => 'APP_BB_OVN');
1166
1167 if g_debug then
1168 hr_utility.set_location(l_proc, 70);
1169 end if;
1170
1171 -- Set up the approval status - get the value for the APPROVAL_STATUS
1172 -- attribute, which is set up in the activity previous to this one.
1173 --
1174 l_approval_status := wf_engine.GetItemAttrText(
1175 itemtype => itemtype,
1176 itemkey => itemkey ,
1177 aname => 'APPROVAL_STATUS');
1178
1179 l_approver_comment := wf_engine.GetItemAttrText(
1180 itemtype => itemtype,
1181 itemkey => itemkey,
1182 aname => 'APR_REJ_REASON');
1183
1184 if g_debug then
1185 hr_utility.set_location(l_proc, 80);
1186
1187 hr_utility.trace('l_approval_status is : ' || l_approval_status);
1188 end if;
1189
1190 --get approver id
1191 --what happens to l_approver if AUTO_APPROVE??
1192
1193 l_wf_item_type := wf_engine.GetItemAttrText(
1194 itemtype => itemtype
1195 ,itemkey => itemkey
1196 ,aname => 'WF_ITEM_TYPE'
1197 );
1198
1199 IF l_wf_item_type IS NOT NULL
1200 THEN
1201 --current workflow doesn't populate this fied for custom
1202 --workflow either
1203 l_approver := NULL;
1204 ELSE
1205 IF l_approver_comment = 'AUTO_APPROVE'
1206 OR l_approver_comment = 'TIMED_OUT'
1207 THEN
1208 l_approver := NULL;
1209 ELSE
1210 --
1211 -- 115.90 Change. Since this could be an e-mail notification
1212 -- response, our first check is to use the find approver
1213 -- function, since the employee id could be anyone in the case
1214 -- of e-mail. The notification information definitely will
1215 -- provide the right approver.
1216 --
1217 l_approver := find_mysterious_approver
1218 (itemtype, itemkey);
1219 END IF;
1220 END IF;
1221 /*
1222
1223 Bug: 3205338: If the approver id is -1,
1224 i.e. fnd_global.employee_id is
1225
1226 */
1227
1228 hr_utility.trace('OTL:pass 100 - '||itemkey);
1229
1230 -- Added for bug 9076079
1231 IF g_old_item_key.exists(itemkey) then
1232 hr_utility.trace('OTL: Exists in g table');
1233 l_is_blank := g_old_item_key(itemkey).is_diff_tc;
1234 ELSE
1235 hr_utility.trace('OTL: Not exists in g table');
1236 l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
1237 itemkey => itemkey ,
1238 aname => 'IS_DIFF_TC',
1239 ignore_notfound => true);
1240 END IF;
1241
1242 hr_utility.trace('OTL:pass 110');
1243
1244 if l_is_blank = 'Y' then
1245 l_approver_comment := l_approver_comment ||'BLANK_NOTIFICATION';
1246 end if;
1247
1248 t_attributes := get_empty_attribute;
1249
1250 hxc_deposit_process_pkg.execute_deposit_process(
1251 p_process_name => g_process_name
1252 ,p_source_name => g_source_name
1253 ,p_effective_date => trunc(sysdate)
1254 ,p_type => 'RANGE'
1255 ,p_measure => null
1256 ,p_unit_of_measure => null
1257 ,p_start_time => l_period_start_date
1258 ,p_stop_time => l_period_end_date
1259 ,p_parent_building_block_id => null
1260 ,p_parent_building_block_ovn => null
1261 ,p_scope => 'APPLICATION_PERIOD'
1262 ,p_approval_style_id => NULL
1263 ,p_approval_status => l_approval_status
1264 ,p_resource_id => l_tc_resource_id
1265 ,p_resource_type => g_resource_type
1266 ,p_comment_text => l_approver_comment
1267 ,p_timecard => t_attributes
1268 ,p_time_building_block_id => l_appl_period_bb_id
1269 ,p_object_version_number => l_appl_period_bb_ovn
1270 );
1271
1272
1273 if(l_approver = -1) then
1274 l_approver := find_mysterious_approver(itemtype,itemkey);
1275 end if;
1276
1277 if g_debug then
1278 hr_utility.set_location(l_proc, 90);
1279 end if;
1280
1281 --update hxc_application_period_summary table
1282 l_creation_date := get_creation_date(l_appl_period_bb_id, l_appl_period_bb_ovn);
1283 update hxc_app_period_summary
1284 set application_period_ovn = l_appl_period_bb_ovn
1285 ,approval_status = l_approval_status
1286 ,approver_id = l_approver
1287 ,notification_status = 'FINISHED'
1288 ,creation_date = l_creation_date
1289 where application_period_id = l_appl_period_bb_id;
1290
1291 hxc_timecard_summary_api.reevaluate_timecard_statuses
1292 (p_application_period_id => l_appl_period_bb_id);
1293
1294 update_latest_details(l_appl_period_bb_id);
1295
1296 -- Set up the result as APPROVED or REJECTED, so that the process_appl_periods
1297 -- is only done again if this row has been APPROVED.
1298 --
1299 IF upper(l_approval_status) = 'APPROVED' THEN
1300 result := 'COMPLETE:APPROVED';
1301 ELSIF upper(l_approval_status) = 'REJECTED' THEN
1302 result := 'COMPLETE:REJECTED';
1303 END IF;
1304
1305
1306 if g_debug then
1307 hr_utility.set_location(l_proc, 110);
1308 end if;
1309
1310 /*
1311 Since this could be the last operation in the approvals process
1312 as at the end of the workflow, there will be no more approval
1313 components to process and hence process_appl_periods (the next
1314 activity in the sequence), won't actually do anything, we issue
1315 a commit at this point, to commit the outstanding approval data
1316 from this transaction
1317
1318 This is bug 3449786
1319 */
1320
1321 commit;
1322
1323 return;
1324
1325 exception
1326 when others then
1327 -- The line below records this function call in the error system
1328 -- in the case of an exception.
1329 --
1330 if g_debug then
1331 hr_utility.trace(sqlerrm);
1332 hr_utility.trace('lllllllllllllllllll');
1333 hr_utility.trace(hr_message.last_message_name);
1334 hr_utility.trace('----');
1335 end if;
1336 IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1337 RETURN;
1338 END IF;
1339 --
1340 if g_debug then
1341 hr_utility.set_location(l_proc, 999);
1342 hr_utility.trace('IN EXCEPTION IN update_appl_period');
1343 end if;
1344 --
1345 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.update_appl_period',
1346 itemtype, itemkey, to_char(actid), funcmode);
1347 raise;
1348 result := '';
1349 return;
1350 --
1351 --
1352 END update_app_period;
1353
1354
1355 --this procedure basically creates a duplicate of the current application period
1356 --we need this for HR Supervisor mechanism
1357 PROCEDURE create_next_period(
1358 itemtype IN varchar2,
1359 itemkey IN varchar2,
1360 actid IN number,
1361 funcmode IN varchar2,
1362 result IN OUT NOCOPY varchar2
1363 )
1364 IS
1365 CURSOR c_current_period(
1366 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
1367 )
1368 IS
1369 SELECT *
1370 FROM hxc_app_period_summary
1371 WHERE application_period_id = p_app_id;
1372
1373 CURSOR c_timecards(
1374 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
1375 )
1376 IS
1377 SELECT timecard_id
1378 FROM hxc_tc_ap_links
1379 WHERE application_period_id = p_app_id;
1380
1381 CURSOR c_ap_details(
1382 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
1383 )
1384 IS
1385 SELECT time_building_block_id
1386 ,time_building_block_ovn
1387 FROM hxc_ap_detail_links
1388 WHERE application_period_id = p_app_id;
1389
1390
1391 l_current_period hxc_app_period_summary%rowtype;
1392 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1393 l_detail_id hxc_time_building_blocks.time_building_block_id%TYPE;
1394 l_detail_ovn hxc_time_building_blocks.object_version_number%TYPE;
1395 l_new_appl_bb_id hxc_time_building_blocks.time_building_block_id%TYPE := NULL;
1396 l_new_appl_bb_ovn hxc_time_building_blocks.object_version_number%TYPE := NULL;
1397 l_period_start_date hxc_time_building_blocks.start_time%TYPE;
1398 l_period_end_date hxc_time_building_blocks.stop_time%TYPE;
1399 l_tc_resource_id hxc_time_building_blocks.resource_id%TYPE;
1400 l_attribute approval_attribute;
1401 t_attributes hxc_time_attributes_api.timecard;
1402 l_period_id hxc_time_building_blocks.time_building_block_id%TYPE;
1403 l_creation_date hxc_time_building_blocks.creation_date%TYPE;
1404
1405 l_proc VARCHAR2(150) := 'create_next_period';
1406 BEGIN
1407 g_debug:=hr_utility.debug_enabled;
1408 if g_debug then
1409 hr_utility.trace('in create_next_period');
1410 end if;
1411
1412 IF funcmode = 'RUN'
1413 THEN
1414 l_period_id := wf_engine.GetItemAttrNumber(
1415 itemtype => itemtype,
1416 itemkey => itemkey ,
1417 aname => 'APP_BB_ID');
1418
1419 l_period_start_date := wf_engine.GetItemAttrDate(
1420 itemtype => itemtype,
1421 itemkey => itemkey ,
1422 aname => 'APP_START_DATE');
1423
1424 l_period_end_date := wf_engine.GetItemAttrDate(
1425 itemtype => itemtype,
1426 itemkey => itemkey ,
1427 aname => 'APP_END_DATE');
1428
1429 l_tc_resource_id := wf_engine.GetItemAttrNumber(
1430 itemtype => itemtype,
1431 itemkey => itemkey ,
1432 aname => 'RESOURCE_ID');
1433
1434 t_attributes := get_empty_attribute;
1435
1436 hxc_deposit_process_pkg.execute_deposit_process
1437 (p_process_name => g_process_name
1438 ,p_source_name => g_source_name
1439 ,p_effective_date => trunc(sysdate)
1440 ,p_type => 'RANGE'
1441 ,p_measure => null
1442 ,p_unit_of_measure => null
1443 ,p_start_time => trunc(l_period_start_date)
1444 ,p_stop_time => trunc(l_period_end_date)
1445 ,p_parent_building_block_id => null
1446 ,p_parent_building_block_ovn => null
1447 ,p_scope => 'APPLICATION_PERIOD'
1448 ,p_approval_style_id => NULL
1449 ,p_approval_status => 'SUBMITTED'
1450 ,p_resource_id => l_tc_resource_id
1451 ,p_resource_type => g_resource_type
1452 ,p_comment_text => null
1453 ,p_timecard => t_attributes
1454 ,p_time_building_block_id => l_new_appl_bb_id
1455 ,p_object_version_number => l_new_appl_bb_ovn);
1456
1457 if g_debug then
1458 hr_utility.trace('next period created=' || l_new_appl_bb_id);
1459 hr_utility.trace('next period created=' || l_new_appl_bb_ovn);
1460 end if;
1461
1462 --populating summary tables
1463 l_creation_date := get_creation_date(l_new_appl_bb_id, l_new_appl_bb_ovn);
1464
1465 OPEN c_current_period(
1466 p_app_id => l_period_id
1467 );
1468
1469 FETCH c_current_period INTO l_current_period;
1470 IF c_current_period%NOTFOUND
1471 THEN
1472 CLOSE c_current_period;
1473
1474 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1475 hr_utility.set_message_token('PROCEDURE', l_proc);
1476 hr_utility.set_message_token('STEP', '20');
1477 hr_utility.raise_error;
1478 END IF;
1479
1480 CLOSE c_current_period;
1481
1482 g_trace := l_proc || '80';
1483 /*
1484 --populate hxc_app_period_summary with the new row
1485 INSERT INTO hxc_app_period_summary
1486 (APPLICATION_PERIOD_ID
1487 ,APPLICATION_PERIOD_OVN
1488 ,APPROVAL_STATUS
1489 ,TIME_RECIPIENT_ID
1490 ,TIME_CATEGORY_ID
1491 ,START_TIME
1492 ,STOP_TIME
1493 ,RESOURCE_ID
1494 ,RECIPIENT_SEQUENCE
1495 ,CATEGORY_SEQUENCE
1496 ,CREATION_DATE
1497 ,NOTIFICATION_STATUS
1498 ,APPROVER_ID
1499 ,APPROVAL_COMP_ID
1500 )
1501 VALUES
1502 */
1503 hxc_app_period_summary_pkg.insert_summary_row
1504 (l_new_appl_bb_id
1505 ,l_new_appl_bb_ovn
1506 ,'SUBMITTED'
1507 ,l_current_period.time_recipient_id
1508 ,l_current_period.time_category_id
1509 ,l_current_period.start_time
1510 ,l_current_period.stop_time
1511 ,l_current_period.resource_id
1512 ,l_current_period.recipient_sequence
1513 ,l_current_period.category_sequence
1514 ,l_creation_date
1515 ,'NOTIFIED'
1516 ,NULL
1517 ,l_current_period.approval_comp_id
1518 ,NULL
1519 ,NULL
1520 ,Null
1521 ,l_current_period.data_set_id
1522 );
1523
1524 --populate hxc_tc_ap_links
1525 OPEN c_timecards(
1526 p_app_id => l_period_id
1527 );
1528
1529 LOOP
1530 FETCH c_timecards into l_timecard_id;
1531
1532 EXIT WHEN c_timecards%NOTFOUND;
1533 -- 115.76 Not changed this one, since this procedure
1534 -- no longer seems to be called.
1535 hxc_tc_ap_links_pkg.insert_summary_row(
1536 l_timecard_id
1537 ,l_new_appl_bb_id);
1538
1539 --Bug 5554020.
1540 hxc_timecard_summary_api.reevaluate_timecard_statuses(l_new_appl_bb_id);
1541
1542 END LOOP;
1543
1544 CLOSE c_timecards;
1545
1546 --populating hxc_ap_detail_links
1547 OPEN c_ap_details(
1548 p_app_id => l_period_id
1549 );
1550
1551 LOOP
1552 FETCH c_ap_details INTO l_detail_id, l_detail_ovn;
1553
1554 EXIT WHEN c_ap_details%NOTFOUND;
1555 /*
1556 INSERT INTO hxc_ap_detail_links
1557 (application_period_id,
1558 time_building_block_id,
1559 time_building_block_ovn)
1560 VALUES
1561 */
1562
1563 hxc_ap_detail_links_pkg.insert_summary_row
1564 (l_new_appl_bb_id,
1565 l_detail_id,
1566 l_detail_ovn);
1567 END LOOP;
1568
1569 CLOSE c_ap_details;
1570
1571 --now set workflow attribute to the new application period
1572 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1573 itemkey => itemkey,
1574 aname => 'NEXT_APP_BB_ID',
1575 avalue => l_new_appl_bb_id);
1576
1577 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1578 itemkey => itemkey,
1579 aname => 'NEXT_APP_BB_OVN',
1580 avalue => l_new_appl_bb_ovn);
1581
1582 result := 'COMPLETE';
1583 return;
1584 END IF;
1585
1586 exception
1587 when others then
1588 -- The line below records this function call in the error system
1589 -- in the case of an exception.
1590 --
1591 if g_debug then
1592 hr_utility.set_location(l_proc, 999);
1593 --
1594 hr_utility.trace('IN EXCEPTION IN create_next_period');
1595 --
1596 end if;
1597 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.create_next_period',
1598 itemtype, itemkey, to_char(actid), funcmode);
1599 raise;
1600 result := '';
1601 return;
1602
1603 END create_next_period;
1604
1605
1606 ------------------------- get_approval_period_id --------------------------
1607 --
1608 FUNCTION get_approval_period_id(
1609 p_resource_id in HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
1610 ,p_time_recipient_id in HXC_TIME_RECIPIENTS.TIME_RECIPIENT_ID%TYPE
1611 ,p_day_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
1612 ,p_timecard_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
1613 ,p_timecard_stop_time in HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
1614 )
1615 RETURN HXC_RECURRING_PERIODS.RECURRING_PERIOD_ID%TYPE
1616 IS
1617
1618 cursor csr_get_app_rec_period(
1619 p_time_recipient_id number,
1620 p_app_periods number)
1621 is
1622 select hapc.recurring_period_id
1623 from hxc_approval_period_comps hapc,
1624 hxc_approval_period_sets haps
1625 where haps.approval_period_set_id = p_app_periods
1626 and hapc.approval_period_set_id = haps.approval_period_set_id
1627 and hapc.time_recipient_id = p_time_recipient_id;
1628
1629 l_app_periods HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE;
1630 l_recurring_period_id HXC_RECURRING_PERIODS.RECURRING_PERIOD_ID%TYPE;
1631 l_day_number NUMBER;
1632 l_day_count NUMBER;
1633 l_pref_found BOOLEAN;
1634 l_day_check DATE;
1635
1636 BEGIN
1637
1638 -- Attempt to find the approval period preference value
1639
1640 BEGIN
1641
1642 l_app_periods := hxc_preference_evaluation.resource_preferences(
1643 p_resource_id => p_resource_id,
1644 p_pref_code => 'TS_PER_APPROVAL_PERIODS',
1645 p_attribute_n => 1,
1646 p_evaluation_date => trunc(p_day_start_time));
1647
1648 EXCEPTION
1649 when others then
1650 --
1651 -- Ok, now we loop over all the days in the timecard period
1652 -- looking for an application period preference
1653 --
1654 l_day_number := trunc(p_timecard_stop_time) - trunc(p_timecard_start_time);
1655 l_day_count := 0;
1656 l_pref_found := false;
1657
1658 LOOP
1659 EXIT WHEN l_day_count > l_day_number;
1660 EXIT WHEN l_pref_found;
1661 l_day_check := trunc(p_timecard_start_time) + l_day_count;
1662 BEGIN
1663
1664 l_app_periods := hxc_preference_evaluation.resource_preferences(
1665 p_resource_id => p_resource_id,
1666 p_pref_code => 'TS_PER_APPROVAL_PERIODS',
1667 p_attribute_n => 1,
1668 p_evaluation_date => l_day_check);
1669
1670 l_pref_found := true;
1671
1672 EXCEPTION
1673 When others then
1674 null;
1675 END;
1676
1677 l_day_count := l_day_count +1;
1678
1679 END LOOP;
1680
1681 if (NOT l_pref_found) then
1682
1683 g_error_count := g_error_count + 1;
1684 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_NO_APRL_PERIOD_PREF';
1685 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
1686 --
1687 FND_MESSAGE.SET_NAME('HXC','HXC_NO_APRL_PERIOD_PREF');
1688 FND_MESSAGE.SET_TOKEN('DATE',FND_DATE.DATE_TO_CANONICAL(p_day_start_time));
1689 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',p_resource_id);
1690 FND_MESSAGE.RAISE_ERROR;
1691
1692 end if;
1693
1694 END;
1695
1696 --
1697 -- Use the application period id to get the recurring period id
1698 --
1699
1700 open csr_get_app_rec_period(p_time_recipient_id,to_number(l_app_periods));
1701 fetch csr_get_app_rec_period into l_recurring_period_id;
1702
1703 if csr_get_app_rec_period%NOTFOUND then
1704 close csr_get_app_rec_period;
1705 g_error_count := g_error_count + 1;
1706 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_REC_PERIOD';
1707 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
1708 --
1709 FND_MESSAGE.SET_NAME('HXC','HXC_APR_NO_REC_PERIOD');
1710 FND_MESSAGE.SET_TOKEN('TIME_RECIPIENT',p_time_recipient_id);
1711 FND_MESSAGE.SET_TOKEN('APP_PERIOD_PREF',l_app_periods);
1712 FND_MESSAGE.RAISE_ERROR;
1713 else
1714 close csr_get_app_rec_period;
1715 end if;
1716
1717 return l_recurring_period_id;
1718
1719 END get_approval_period_id;
1720
1721
1722 PROCEDURE get_application_period(
1723 p_app_period_func IN VARCHAR2
1724 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1725 ,p_day IN hxc_time_building_blocks.start_time%TYPE
1726 ,p_time_recipient IN hxc_time_recipients.time_recipient_id%TYPE
1727 ,p_tc_start_time IN hxc_time_building_blocks.start_time%TYPE
1728 ,p_tc_stop_time IN hxc_time_building_blocks.stop_time%TYPE
1729 ,p_assignment_periods IN hxc_timecard_utilities.periods
1730 ,p_period_start OUT NOCOPY hxc_time_building_blocks.start_time%TYPE
1731 ,p_period_end OUT NOCOPY hxc_time_building_blocks.stop_time%TYPE
1732 )
1733 IS
1734 l_period_start_date date;
1735 l_period_end_date date;
1736 l_override_allowed boolean;
1737 l_app_period hxc_timecard_utilities.time_period;
1738 l_valid_periods hxc_timecard_utilities.periods;
1739
1740
1741 l_call_proc varchar2(2000);
1742 l_cursor number;
1743 l_ret number;
1744
1745 l_rec_period_id number;
1746 l_rec_start_date date;
1747 l_rec_period_type varchar2(80);
1748 l_duration_in_days number(10);
1749
1750 l_proc varchar2(50) := 'get_application_period';
1751
1752 cursor csr_get_rec_period_info(p_recurring_period_id number) is
1753 select hrp.start_date,
1754 hrp.period_type,
1755 hrp.duration_in_days
1756 from hxc_recurring_periods hrp
1757 where hrp.recurring_period_id = p_recurring_period_id;
1758
1759 BEGIN
1760
1761 IF p_app_period_func IS NOT NULL
1762 THEN
1763 if g_debug then
1764 hr_utility.set_location(l_proc, 95);
1765 end if;
1766
1767 l_call_proc := p_app_period_func ||
1768 '(p_building_block_date => ' || p_day ||
1769 ',p_resource_id => ' || p_resource_id ||
1770 ',p_period_start_date => l_period_start_date' ||
1771 ',p_period_end_date => l_period_end_date' ||
1772 ',p_override_allowed => l_override_allowed)';
1773
1774 if g_debug then
1775 hr_utility.trace('Period Start Date (from function) is : ' ||
1776 to_char(l_period_start_date, 'DD-MM-YYYY'));
1777 hr_utility.trace('Period End Date (from function) is : ' ||
1778 to_char(l_period_end_date, 'DD-MM-YYYY'));
1779 end if;
1780
1781 l_cursor := dbms_sql.open_cursor;
1782 dbms_sql.parse(l_cursor, l_call_proc, DBMS_SQL.V7);
1783 l_ret := dbms_sql.execute(l_cursor);
1784 dbms_sql.close_cursor(l_cursor);
1785
1786 if g_debug then
1787 hr_utility.set_location(l_proc, 110);
1788 end if;
1789 ELSE
1790
1791 l_override_allowed := TRUE;
1792
1793 END IF;
1794
1795 -- If override allowed then, get application period start and
1796 -- end dates.
1797
1798 IF l_override_allowed
1799 THEN
1800
1801 if g_debug then
1802 hr_utility.set_location(l_proc, 120);
1803 end if;
1804
1805 l_rec_period_id := get_approval_period_id(
1806 p_resource_id
1807 ,p_time_recipient
1808 ,p_day
1809 ,p_tc_start_time
1810 ,p_tc_stop_time
1811 );
1812
1813 if g_debug then
1814 hr_utility.trace('Recurring Period ID is : ' || to_char(l_rec_period_id));
1815 end if;
1816
1817 open csr_get_rec_period_info(l_rec_period_id);
1818 fetch csr_get_rec_period_info into l_rec_start_date,
1819 l_rec_period_type,
1820 l_duration_in_days;
1821 close csr_get_rec_period_info;
1822
1823 hxc_timecard_utilities.find_current_period(
1824 p_rec_period_start_date => l_rec_start_date
1825 ,p_period_type => l_rec_period_type
1826 ,p_duration_in_days => l_duration_in_days
1827 ,p_current_date => p_day
1828 ,p_period_start => l_period_start_date
1829 ,p_period_end => l_period_end_date
1830 );
1831
1832 if g_debug then
1833 hr_utility.trace('Appl Period Start Date is : ' ||
1834 to_char(l_period_start_date, 'DD-MM-YYYY'));
1835 hr_utility.trace('Appl Period End Date is : ' ||
1836 to_char(l_period_end_date, 'DD-MM-YYYY'));
1837 end if;
1838
1839 END IF;
1840
1841 p_period_start := l_period_start_date;
1842 p_period_end := l_period_end_date;
1843
1844 -- JOEL
1845 --processing assignment to remove days without an active assignment
1846 l_app_period.start_date := l_period_start_date;
1847 l_app_period.end_date := l_period_end_date;
1848
1849 l_valid_periods.delete;
1850
1851 hxc_timecard_utilities.process_assignments(
1852 p_period => l_app_period
1853 ,p_assignment_periods => p_assignment_periods
1854 ,p_return_periods => l_valid_periods
1855 );
1856
1857 FOR i IN l_valid_periods.first .. l_valid_periods.last
1858 LOOP
1859 IF p_day BETWEEN l_valid_periods(i).start_date
1860 AND l_valid_periods(i).end_date
1861 THEN
1862 p_period_start := l_valid_periods(i).start_date;
1863 p_period_end := l_valid_periods(i).end_date;
1864
1865 EXIT;
1866 END IF;
1867 END LOOP;
1868 -- JOEL
1869
1870 END get_application_period;
1871
1872
1873 FUNCTION get_rest_detail_blocks(
1874 p_detail_blocks IN block_table
1875 )
1876 RETURN NUMBER
1877 IS
1878
1879 l_block_index NUMBER;
1880 l_block_count NUMBER := 0;
1881
1882 BEGIN
1883 IF p_detail_blocks.count = 0
1884 THEN
1885 RETURN 0;
1886 END IF;
1887 FOR l_block_index in p_detail_blocks.first .. p_detail_blocks.last LOOP
1888 IF p_detail_blocks(l_block_index).added <> 'Y'
1889 THEN
1890 l_block_count := l_block_count + 1;
1891 END IF;
1892 END LOOP;
1893
1894 RETURN l_block_count;
1895
1896 END;
1897
1898
1899 FUNCTION has_details(
1900 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
1901 )
1902 RETURN BOOLEAN
1903 IS
1904 CURSOR c_details(
1905 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
1906 )
1907 IS
1908 SELECT details.time_building_block_id
1909 FROM hxc_ap_detail_links details
1910 ,hxc_time_building_blocks blocks
1911 WHERE details.application_period_id = p_app_id
1912 AND details.time_building_block_id = blocks.time_building_block_id
1913 AND details.time_building_block_ovn = blocks.object_version_number
1914 AND blocks.date_to = hr_general.end_of_time;
1915
1916 l_detail_id hxc_time_building_blocks.time_building_block_id%TYPE;
1917 BEGIN
1918 OPEN c_details(p_app_id);
1919 FETCH c_details INTO l_detail_id;
1920 IF c_details%NOTFOUND
1921 THEN
1922 CLOSE c_details;
1923
1924 RETURN FALSE;
1925 END IF;
1926
1927 CLOSE c_details;
1928 RETURN TRUE;
1929 END has_details;
1930
1931 Procedure get_detail_links(p_app_id in hxc_time_building_blocks.time_building_block_id%TYPE,
1932 p_timecard_id in hxc_time_building_blocks.time_building_block_id%TYPE,
1933 p_blocks out nocopy block_table )
1934 IS
1935 Cursor c_detail_links IS
1936 select time_building_block_id, time_building_block_ovn
1937 from hxc_ap_detail_links
1938 where application_period_id = p_app_id
1939 and time_building_block_id
1940 not in ( select detail.time_building_block_id
1941 from hxc_time_building_blocks detail,
1942 hxc_time_building_blocks day
1943 where detail.parent_building_block_id = day.time_building_block_id
1944 and detail.parent_building_block_ovn = day.object_version_number
1945 and day.scope = 'DAY'
1946 and detail.scope = 'DETAIL'
1947 and day.parent_building_block_id = p_timecard_id
1948 );
1949
1950
1951 l_block_index BINARY_INTEGER;
1952 BEGIN
1953 l_block_index := 1;
1954
1955 Open c_detail_links;
1956 Loop
1957 Fetch c_detail_links into p_blocks(l_block_index).block_id,
1958 p_blocks(l_block_index).block_ovn;
1959 Exit when c_detail_links%notfound;
1960 l_block_index := l_block_index + 1;
1961 End Loop;
1962 Close c_detail_links;
1963 End get_detail_links;
1964
1965 Procedure create_removed_links(p_removed_blocks block_table,
1966 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE)
1967
1968 IS
1969 Cursor c_detail_exists (p_app_id hxc_time_building_blocks.time_building_block_id%TYPE,
1970 p_time_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE
1971 ) is
1972 select 1
1973 from hxc_ap_detail_links
1974 where application_period_id = p_app_id
1975 and time_building_block_id = p_time_building_block_id;
1976
1977 l_block_index BINARY_INTEGER;
1978 l_dummy PLS_INTEGER;
1979 BEGIN
1980
1981 l_block_index := p_removed_blocks.first;
1982
1983 LOOP
1984 EXIT WHEN NOT p_removed_blocks.exists(l_block_index);
1985
1986
1987 open c_detail_exists(p_app_id,
1988 p_removed_blocks(l_block_index).block_id
1989 );
1990
1991 fetch c_detail_exists into l_dummy;
1992
1993 if c_detail_exists%notfound then
1994 --insert a line in hxc_detail_summary;
1995 hxc_ap_detail_links_pkg.insert_summary_row(
1996 p_app_id
1997 ,p_removed_blocks(l_block_index).block_id
1998 ,p_removed_blocks(l_block_index).block_ovn
1999 );
2000 end if;
2001 close c_detail_exists;
2002
2003 l_block_index := p_removed_blocks.next(l_block_index);
2004
2005 END LOOP;
2006
2007 End create_removed_links;
2008
2009 FUNCTION item_attribute_exists
2010 (p_item_type in wf_items.item_type%type,
2011 p_item_key in wf_item_activity_statuses.item_key%type,
2012 p_name in wf_item_attribute_values.name%type)
2013 return boolean is
2014
2015 l_dummy varchar2(1);
2016
2017 BEGIN
2018
2019 select 'Y'
2020 into l_dummy
2021 from wf_item_attribute_values
2022 where item_type = p_item_type
2023 and item_key = p_item_key
2024 and name = p_name;
2025
2026 return true;
2027
2028 Exception
2029 When others then
2030 return false;
2031
2032 END item_attribute_exists;
2033
2034
2035 PROCEDURE generate_app_period(
2036 p_item_type IN wf_item_types.name%type
2037 ,p_item_key IN wf_item_attribute_values.item_key%type
2038 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
2039 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
2040 ,p_start_time IN hxc_time_building_blocks.start_time%TYPE
2041 ,p_stop_time IN hxc_time_building_blocks.stop_time%TYPE
2042 ,p_time_recipient_id IN hxc_time_recipients.time_recipient_id%TYPE
2043 ,p_recipient_sequence IN hxc_approval_comps.approval_order%TYPE
2044 ,p_approval_comp IN approval_comp
2045 ,p_tc_resubmitted IN VARCHAR2
2046 -- ,p_first IN VARCHAR2
2047 ,p_detail_blocks IN OUT NOCOPY block_table
2048 ,p_detail_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
2049 )
2050 IS
2051
2052 CURSOR c_app_period(
2053 p_resource_id hxc_time_building_blocks.resource_id%TYPE
2054 ,p_start_time hxc_time_building_blocks.start_time%TYPE
2055 ,p_stop_time hxc_time_building_blocks.stop_time%TYPE
2056 ,p_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE
2057 ,p_recipient_sequence IN hxc_approval_comps.approval_order%TYPE
2058 ,p_time_category_id hxc_time_categories.time_category_id%TYPE
2059 ,p_category_sequence hxc_approval_comps.approval_order%TYPE
2060 )
2061 IS
2062 SELECT application_period_id
2063 ,application_period_ovn
2064 ,approval_status
2065 ,notification_status
2066 ,approval_comp_id
2067 FROM hxc_app_period_summary
2068 WHERE resource_id = p_resource_id
2069 AND start_time = p_start_time
2070 AND stop_time = p_stop_time
2071 AND time_recipient_id = p_time_recipient_id
2072 AND recipient_sequence = p_recipient_sequence
2073 AND NVL(time_category_id, -1) = NVL(p_time_category_id, -1)
2074 AND NVL(category_sequence, -1) = NVL(p_category_sequence, -1)
2075 --following added may12 for hr supervisor
2076 ORDER BY application_period_id asc;
2077
2078 CURSOR c_tc_ap_link(
2079 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
2080 ,p_app_period_id hxc_time_building_blocks.time_building_block_id%TYPE
2081 )
2082 IS
2083 SELECT 'Y'
2084 FROM hxc_tc_ap_links
2085 WHERE timecard_id = p_timecard_id
2086 AND application_period_id = p_app_period_id;
2087
2088 cursor c_previous_actioner(
2089 p_app_period_id in hxc_app_period_summary.application_period_id%type) is
2090 select approver_id
2091 from hxc_app_period_summary
2092 where application_period_id = p_app_period_id;
2093
2094 CURSOR c_get_detail_blocks(p_application_period_id in hxc_time_building_blocks.time_building_block_id%type)
2095 is
2096 select adl.time_building_block_id,
2097 adl.time_building_block_ovn
2098 from hxc_ap_detail_links adl
2099 where adl.application_period_id = p_application_period_id;
2100
2101 cursor get_max_ovn(p_bb_id in hxc_time_building_blocks.time_building_block_id%type)
2102 is
2103 select max(object_version_number)
2104 from hxc_time_building_blocks
2105 where time_building_block_id = p_bb_id;
2106
2107 cursor get_item_key(p_bb_id in number)
2108 is
2109 select approval_item_key
2110 from hxc_app_period_summary
2111 where application_period_id = p_bb_id;
2112
2113
2114 l_app_id hxc_time_building_blocks.time_building_block_id%TYPE := NULL;
2115 l_app_ovn hxc_time_building_blocks.object_version_number%TYPE := NULL;
2116 l_approval_status hxc_time_building_blocks.approval_status%TYPE := NULL;
2117 l_notification_status VARCHAR2(150) := NULL;
2118 l_app_comp_id hxc_app_period_summary.approval_comp_id%type;
2119 l_app_id_temp hxc_time_building_blocks.time_building_block_id%TYPE := NULL;
2120 l_app_ovn_temp hxc_time_building_blocks.object_version_number%TYPE := NULL;
2121 l_app_status_temp hxc_time_building_blocks.approval_status%TYPE := NULL;
2122 l_notif_status_temp VARCHAR2(150) := NULL;
2123 l_app_comp_id_temp hxc_app_period_summary.approval_comp_id%type;
2124 l_first_app_period BOOLEAN;
2125 l_time_category_id hxc_time_categories.time_category_id%TYPE := NULL;
2126 l_category_sequence hxc_app_period_summary.category_sequence%TYPE := NULL;
2127 l_creation_date DATE;
2128 t_attributes hxc_time_attributes_api.timecard;
2129 l_tc_ap_link_exists VARCHAR2(50) := NULL;
2130 l_app_exists BOOLEAN;
2131 l_removed_blocks block_table;
2132
2133 l_is_empty Boolean := true;
2134 l_item_key_exists NUMBER := 0;
2135
2136 l_dummy number;
2137 l_number_of_details number;
2138 i number;
2139 l_max_ovn number;
2140 l_item_key number ;
2141 l_blank varchar2(2) := 'N';
2142 l_active_details NUMBER; -- 8620917
2143 type rec_type is record(p_id hxc_time_building_blocks.time_building_block_id%TYPE,
2144 p_ovn hxc_time_building_blocks.time_building_block_id%TYPE);
2145
2146
2147 TYPE tab_type IS TABLE OF rec_type INDEX BY BINARY_INTEGER;
2148
2149 l_tab_type_a tab_type;
2150 l_proc VARCHAR2(100) := g_package || 'generate_app_period';
2151
2152 l_tc_details NUMBER; -- Bug 8685110
2153 -- Added for 13095894
2154 l_timecard_id NUMBER;
2155 l_total_hours NUMBER;
2156
2157 BEGIN
2158 g_debug := true;
2159 g_trace := l_proc || '10';
2160 l_item_key := null;
2161 if g_debug then
2162 hr_utility.trace('start generating period');
2163 end if;
2164
2165 l_time_category_id := p_approval_comp.time_category_id;
2166
2167 IF l_time_category_id IS NULL
2168 THEN
2169 l_category_sequence := NULL;
2170 ELSE
2171 l_category_sequence := p_approval_comp.approval_order;
2172 END IF;
2173
2174 OPEN c_app_period(
2175 p_resource_id
2176 ,p_start_time
2177 ,p_stop_time
2178 ,p_time_recipient_id
2179 ,p_recipient_sequence
2180 ,l_time_category_id
2181 ,l_category_sequence
2182 );
2183
2184 l_first_app_period := TRUE;
2185
2186 LOOP
2187
2188 FETCH c_app_period INTO l_app_id_temp,
2189 l_app_ovn_temp,
2190 l_app_status_temp,
2191 l_notif_status_temp,
2192 l_app_comp_id_temp;
2193
2194 EXIT WHEN c_app_period%NOTFOUND;
2195
2196 hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2197 hr_utility.trace('|--------------------------------------------------------------------|');
2198 hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2199 hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2200 hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2201 hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2202 hr_utility.trace('|--------------------------------------------------------------------|');
2203 hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2204
2205 --for hr supervisor, end date all the later supervisor's app periods
2206 --only keep the first supervisor's app period. we will regenereate new
2207 --app periods for later supervisors
2208 --Here we also delete app period for default approval style when the new
2209 --timecard doesn't have any block for default approval style.
2210 IF l_first_app_period
2211 THEN
2212
2213 l_app_id := l_app_id_temp;
2214 l_app_ovn := l_app_ovn_temp;
2215 l_approval_status := l_app_status_temp;
2216 l_notification_status := l_notif_status_temp;
2217 l_app_comp_id := l_app_comp_id_temp;
2218
2219 l_first_app_period := FALSE;
2220
2221 ELSE
2222 --should also end date them from hxc_time_building_blocks
2223 --remove them from hxc_app_period_summary
2224 --from hxc_tc_ap_links and hxc_ap_detail_links
2225
2226 hxc_app_period_summary_api.app_period_delete(l_app_id_temp);
2227
2228 END IF;
2229
2230 END LOOP;
2231
2232 CLOSE c_app_period;
2233 hr_utility.trace('l_time_category:'||l_time_category_id);
2234
2235 --
2236 -- If this is a new application period, and has no associated details
2237 -- do not create it. However, if it has been previously created,
2238 -- we must resend, even if empty, to ensure ELA timecards can be
2239 -- approved. 115.87
2240 -- 115.91 Change: We have some competing requirements here for
2241 -- Project Manager verses the rest of ELA. Making this logic
2242 -- a bit clearer.
2243 IF l_time_category_id IS NOT NULL then
2244 -- This is an ELA application period
2245 IF l_time_category_id <> 0 then
2246 l_is_empty := is_empty(p_detail_blocks,p_detail_attributes,l_time_category_id);
2247 -- This is not the default ELA approval style
2248 IF l_is_empty then
2249 -- There are no details associated with this application period
2250 IF l_app_id is null then
2251 -- This is a new application, do not generate a new, empty
2252 -- application period.
2253 hr_utility.trace('Return(0)');
2254 RETURN;
2255 END IF;
2256 IF ((l_app_id is not null)
2257 AND (l_app_ovn = 1)
2258 AND (l_notification_status = c_notified OR l_notification_status = c_not_notified )) then
2259 -- Application period is not new, but the approver has not seen it
2260 hr_utility.trace('Return(1)');
2261 RETURN;
2262 END IF;
2263 END IF;
2264 END IF;
2265 END IF;
2266
2267 get_detail_links(l_app_id, p_timecard_id, l_removed_blocks);
2268
2269 --if this timecard doesn't have any block for default style,
2270 --delete existing app period if any, and do nothing.
2271 IF l_time_category_id = 0
2272 AND p_detail_blocks.count > 0
2273 THEN
2274
2275 l_is_empty := false; -- Bug 5640516.
2276
2277 IF get_rest_detail_blocks(p_detail_blocks) = 0
2278 THEN
2279 IF l_app_id IS NOT NULL
2280 THEN
2281 --check to see if all the detail blocks associated with this
2282 --app period have been deleted. If so, delete this app period.
2283 --otherwise, it means, this app period still has details from
2284 --other timecards, can't delete the app period
2285 IF NOT has_details(l_app_id)
2286 THEN
2287 hxc_app_period_summary_api.app_period_delete(l_app_id);
2288 END IF;
2289 END IF;
2290
2291 hr_utility.trace('Return(2)');
2292 RETURN;
2293 END IF;
2294 END IF;
2295 --
2296 -- 115.93 Change: Include check that the approval component id
2297 -- is the same in the statement below, otherwise we will surely
2298 -- need to regenerate the application period. Bug 4302997.
2299 --
2300 hr_utility.trace('l_app_id:'||l_app_id);
2301 hr_utility.trace('l_approval_status:'||l_approval_status);
2302 hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2303
2304 if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2305 hr_utility.trace('changed is true');
2306 else
2307 hr_utility.trace('changed is false');
2308 end if;
2309
2310 select count(*) into l_number_of_details
2311 from hxc_ap_detail_links
2312 where application_period_id = l_app_id;
2313
2314 hr_utility.trace('Count for '||l_app_id||' is:'||l_number_of_details);
2315
2316 hr_utility.trace('check if the timecard ever had any details attached to it');
2317 hr_utility.trace('p_resource_id ::'||p_resource_id);
2318 hr_utility.trace('trunc(p_start_time) ::'||trunc(p_start_time));
2319 hr_utility.trace('trunc(p_stop_time) ::'||trunc(p_stop_time));
2320
2321 -- Bug 8685110
2322 -- this is to check if the timecard ever had any details attached to it
2323 select count(*) into l_tc_details
2324 from hxc_latest_details
2325 where resource_id = p_resource_id
2326 and trunc(start_time) >= trunc(p_start_time)
2327 and trunc(stop_time) <= trunc(p_stop_time);
2328
2329 hr_utility.trace('l_tc_details = '||l_tc_details);
2330
2331 -- Bug 13095894
2332 -- Check total hours recorded for the timecard
2333
2334 /*
2335 select nvl(recorded_hours,0) + nvl(absence_hours,0)
2336 into l_total_hours
2337 from hxc_timecard_summary
2338 where resource_id = p_resource_id
2339 and start_time = p_start_time
2340 and trunc(stop_time) = trunc(p_stop_time);
2341 */
2342
2343 -- Bug 14498023
2344 -- The start time and stop time would not be right
2345 -- in this query in case of Midperiod Terminated/Rehired employees
2346 select nvl(recorded_hours,0) + nvl(absence_hours,0)
2347 into l_total_hours
2348 from hxc_timecard_summary
2349 where timecard_id = p_timecard_id;
2350
2351
2352
2353 hr_utility.trace('l_total_hours = '||l_total_hours);
2354
2355
2356 IF l_app_id IS NOT NULL
2357 AND p_approval_comp.approval_comp_id = l_app_comp_id
2358 AND (NOT changed( p_detail_blocks,p_detail_attributes,l_time_category_id
2359 ,l_app_id, p_timecard_id))
2360 THEN
2361 --
2362 -- 115.107: An empty complete period should not be reattached to the
2363 -- timecard.
2364 --
2365 if(l_is_empty) then
2366 --
2367 -- 115.115 - Bug 5182298
2368 -- Now have two possibliities:
2369 -- 1. The app period is empty now, and it was before, in which case
2370 -- we must just return and do nothing (discard this application period)
2371 -- 2. The app period is empty now, but it wasn't before, in which case
2372 -- we should continue and generate the notification / app period
2373
2374 if(l_number_of_details = 0 AND l_approval_status <> 'SUBMITTED') then
2375 -- it is case 1
2376
2377 hr_utility.trace('into case1');
2378 -- if an empty timecard is approved/rejected and then resubmitted again, it has to go
2379 -- through the approval process, so skip this call
2380
2381 IF (l_tc_details <> 0) -- Bug 8685110
2382 AND (l_total_hours <> 0) -- Bug 13095894
2383 THEN
2384 hr_utility.trace('Return(3) - empty actioned App Period - do nothing');
2385 RETURN;
2386 end if;
2387 else
2388 -- it is case 2
2389
2390 -- if an empty timecard is waiting for approval and it is resubmitted before the
2391 -- approver can act on the notification, the new notification should be sent to the
2392 -- approver again, so skip this call
2393
2394 IF (l_tc_details <> 0) -- Bug 8685110
2395 AND (l_total_hours <> 0) -- Bug 13095894
2396 THEN
2397 hr_utility.trace('Return(3.5) - newly empty period - notify previous approver');
2398 open get_item_key(l_app_id);
2399 fetch get_item_key into l_item_key;
2400 close get_item_key;
2401
2402 hr_utility.trace('OTL:-10- before - '||l_item_key);
2403
2404 If l_item_key is not null then
2405
2406 -- Added for bug 9076079
2407 select count(*)
2408 into l_item_key_exists
2409 from wf_items
2410 where item_key = to_char(l_item_key)
2411 and item_type = 'HXCEMP'
2412 and rownum < 2;
2413
2414
2415 hr_utility.trace('OTL:-10- before - l_item_key_exists = '||l_item_key_exists);
2416
2417 IF l_item_key_exists <> 0 THEN
2418 hr_utility.trace('pass 11');
2419 if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2420
2421 hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 01');
2422
2423 wf_engine.SetItemAttrText(
2424 itemtype => 'HXCEMP',
2425 itemkey => l_item_key,
2426 aname => 'IS_DIFF_TC',
2427 avalue => 'Y');
2428 else
2429
2430 hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 02');
2431
2432 wf_engine.additemattr
2433 (itemtype => 'HXCEMP',
2434 itemkey => l_item_key,
2435 aname => 'IS_DIFF_TC',
2436 text_value => 'Y');
2437 end if;
2438 ELSE
2439 -- Added for bug 9076079
2440 hr_utility.trace('OTL:pass 12');
2441 g_old_item_key(l_item_key).is_diff_tc := 'Y';
2442
2443 END IF;
2444
2445 hr_utility.trace('OTL:-10- after');
2446
2447 end if;
2448
2449 l_blank := 'Y';
2450 null;
2451 end if; -- Bug 8685110
2452 end if;
2453
2454 else
2455 IF l_approval_status <> 'SUBMITTED' THEN
2456 --
2457 -- 115.76 Change. Ensure this link is created using the normal
2458 -- interface, not this one which is internal.
2459 -- hxc_tc_ap_links_pkg.insert_summary_row(p_timecard_id, l_app_id);
2460 hxc_tc_ap_links_pkg.create_app_period_links(l_app_id);
2461 --
2462 -- 115.91 Change: At this point we also ensure the timecard
2463 -- status is reevaluated so that if a period is not sent due
2464 -- to other changes in the timecard, the timecard has the
2465 -- appropriate status.
2466 --
2467 hxc_timecard_summary_api.reevaluate_timecard_statuses(l_app_id);
2468 hr_utility.trace('Return(4) - populated actioned App period - no changes. Use, but do not renotify');
2469 RETURN;
2470 END IF;
2471 end if;
2472 END IF;
2473 g_block_exist_for_ap := 'N';
2474
2475 hr_utility.trace('l_number_of_details = '||l_number_of_details);
2476 hr_utility.trace('g_block_exist_for_ap = '||g_block_exist_for_ap);
2477 hr_utility.trace('no_blocks(l_app_id, p_timecard_id) = '||no_blocks(l_app_id, p_timecard_id));
2478 hr_utility.trace('l_approval_status = '||l_approval_status);
2479
2480 -- Reverted the changes in bug 8322444 and introduced new changes via 8620917
2481 /* After a approval notification is approved/rejected, if the user
2482 deletes a rejected/approved timecard row, then a blank notification has to be sent to the approver.
2483
2484 If the row deletion is followed by a SAVE and SUBMIT operation then the foll. happens
2485
2486 changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id) = TRUE
2487 l_number_of_details <> 0
2488 g_block_exist_for_ap <> 'Y',
2489 BUT no_blocks(l_app_id, p_timecard_id) = l_number_of_details CONDITION FAILS.
2490
2491 In case of ELA approval, if a rejected timecard row is FULLY deleted, a blank FYI notification
2492 has to be sent to the previous approver
2493
2494 Hence substituting the BLANK notification if the number of active detail RECORDS
2495 for the l_app_id is 0 and set l_blank = 'Y'
2496 */
2497
2498 -- modified this query for bug 8920827
2499 SELECT count(*)
2500 INTO l_active_details
2501 FROM hxc_ap_detail_links apdetail
2502 ,hxc_time_building_blocks detail
2503 ,hxc_latest_details latest
2504 WHERE apdetail.application_period_id = l_app_id
2505 AND apdetail.time_building_block_id = latest.time_building_block_id
2506 AND latest.time_building_block_id = detail.time_building_block_id
2507 AND latest.object_version_number = detail.object_version_number
2508 AND detail.date_to = hr_general.end_of_time;
2509
2510
2511 hr_utility.trace('l_active_details = '||l_active_details);
2512
2513
2514 ---
2515 IF changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id) AND
2516 l_number_of_details <> 0 AND g_block_exist_for_ap <> 'Y'
2517 and (no_blocks(l_app_id, p_timecard_id) = l_number_of_details
2518 OR l_active_details = 0) -- Bug 8620917
2519 and (l_tc_details <> 0) -- Bug 8685110 , no need to send blank notification for empty timecard
2520 and (l_total_hours <> 0) -- Bug 13095894
2521 THEN
2522
2523 hr_utility.trace('to set l_blank');
2524
2525 open get_item_key(l_app_id);
2526 fetch get_item_key into l_item_key;
2527 close get_item_key;
2528
2529 If l_item_key is not null then
2530
2531 select count(*)
2532 into l_item_key_exists
2533 from wf_items
2534 where item_key = to_char(l_item_key)
2535 and item_type = 'HXCEMP'
2536 and rownum < 2;
2537
2538 hr_utility.trace('OTL:-20- before - l_item_key_exists = '||l_item_key_exists);
2539 hr_utility.trace('OTL:-20- before - '||l_item_key);
2540
2541 IF l_item_key_exists <> 0 THEN
2542 hr_utility.trace('OTL: Item key exists in workflow tables');
2543 if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2544
2545 hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 03');
2546 wf_engine.SetItemAttrText(
2547 itemtype => 'HXCEMP',
2548 itemkey => l_item_key,
2549 aname => 'IS_DIFF_TC',
2550 avalue => 'Y');
2551 else
2552 hr_utility.trace('SetItemAttrText of IS_DIFF_TC to Y - 04');
2553 wf_engine.additemattr
2554 (itemtype => 'HXCEMP',
2555 itemkey => l_item_key,
2556 aname => 'IS_DIFF_TC',
2557 text_value => 'Y');
2558 end if;
2559 ELSE
2560 HR_UTILITY.TRACE('OTL: Adding to g table');
2561 g_old_item_key(l_item_key).is_diff_tc := 'Y';
2562 END IF;
2563
2564
2565
2566 hr_utility.trace('OTL:-20- after');
2567 end if;
2568
2569 l_blank := 'Y';
2570 END IF;
2571
2572 IF l_blank = 'Y' THEN
2573
2574 open c_get_detail_blocks(l_app_id);
2575 fetch c_get_detail_blocks bulk collect INTO l_tab_type_a;
2576 close c_get_detail_blocks;
2577
2578 END IF;
2579
2580 IF l_app_id IS NOT NULL
2581 THEN
2582 remove_ap_detail_links(l_app_id, p_timecard_id);
2583 END IF;
2584
2585 --jxtan: when modifying detail blocks and resubmit the timecard,
2586 --p_tc_resubmitted is set to NO in deposit wrapper. Need to ask
2587 --andrew. For now added the extra logic here to deal with the scenario:
2588 --a timecard is approved, but resubmitted. in this case, update the ovn
2589 --of the application period
2590 IF l_app_id IS NULL -- no row
2591 OR (l_app_id IS NOT NULL AND l_approval_status <> 'SUBMITTED')
2592 THEN
2593 if(l_app_id is not null) then
2594 --
2595 -- Keep the previous approver, in case we need it!
2596 -- 115.92 Change
2597 --
2598 l_dummy := hxc_approval_wf_util.keep_previous_approver
2599 (p_item_type,
2600 p_item_key,
2601 l_app_id
2602 );
2603 end if;
2604
2605 g_trace := l_proc || '30';
2606
2607 if g_debug then
2608 hr_utility.trace('Generate it!');
2609 end if;
2610
2611 t_attributes := get_empty_attribute;
2612
2613 hxc_deposit_process_pkg.execute_deposit_process(
2614 p_process_name => g_process_name
2615 ,p_source_name => g_source_name
2616 ,p_effective_date => trunc(sysdate)
2617 ,p_type => 'RANGE'
2618 ,p_measure => null
2619 ,p_unit_of_measure => null
2620 ,p_start_time => trunc(p_start_time)
2621 ,p_stop_time => trunc(p_stop_time)
2622 ,p_parent_building_block_id => null
2623 ,p_parent_building_block_ovn => null
2624 ,p_scope => 'APPLICATION_PERIOD'
2625 ,p_approval_style_id => NULL
2626 ,p_approval_status => 'SUBMITTED'
2627 ,p_resource_id => p_resource_id
2628 ,p_resource_type => g_resource_type
2629 ,p_comment_text => null
2630 ,p_timecard => t_attributes
2631 ,p_time_building_block_id => l_app_id
2632 ,p_object_version_number => l_app_ovn
2633 );
2634
2635 g_trace := l_proc || '40';
2636 if g_debug then
2637 hr_utility.trace('Generated the period');
2638 hr_utility.trace('app_id=' || l_app_id);
2639 hr_utility.trace('app_ovn=' || l_app_ovn);
2640 hr_utility.trace('Populating hxc_app_period_summary');
2641 end if;
2642 l_creation_date := get_creation_date(l_app_id, l_app_ovn);
2643
2644 g_trace := l_proc || '80';
2645
2646 --populate hxc_app_period_summary with the new row
2647 hxc_app_period_summary_api.app_period_create(
2648 p_application_period_id => l_app_id
2649 ,p_application_period_ovn => l_app_ovn
2650 ,p_approval_status => 'SUBMITTED'
2651 ,p_time_recipient_id => p_time_recipient_id
2652 ,p_time_category_id => p_approval_comp.time_category_id
2653 ,p_start_time => p_start_time
2654 ,p_stop_time => p_stop_time
2655 ,p_resource_id => p_resource_id
2656 ,p_recipient_sequence => p_recipient_sequence
2657 ,p_category_sequence => l_category_sequence
2658 ,p_creation_date => l_creation_date
2659 ,p_notification_status => 'NOT_NOTIFIED'
2660 ,p_approver_id => NULL
2661 ,p_approval_comp_id => p_approval_comp.approval_comp_id
2662 ,p_approval_item_key => l_item_key
2663 );
2664
2665 g_trace := l_proc || '90';
2666
2667 ELSE
2668
2669 IF l_notification_status <> 'NOT_NOTIFIED' THEN
2670 -- don't create a new application period, but need to change status
2671 UPDATE hxc_app_period_summary
2672 SET notification_status = 'NOT_NOTIFIED'
2673 ,approval_comp_id = p_approval_comp.approval_comp_id
2674 WHERE application_period_id = l_app_id;
2675 elsif(p_approval_comp.approval_comp_id <> l_app_comp_id) then
2676 -- do not create a new application period, but ensure the
2677 -- correct approval component id is used.
2678 UPDATE hxc_app_period_summary
2679 SET approval_comp_id = p_approval_comp.approval_comp_id
2680 WHERE application_period_id = l_app_id;
2681 END IF;
2682
2683 END IF;
2684
2685 if g_debug then
2686 hr_utility.trace('Populating hxc_ap_detail_links');
2687 end if;
2688 --populate hxc_ap_detail_links
2689 IF l_time_category_id IS NULL
2690 OR l_time_category_id = 0
2691 THEN
2692 g_trace := l_proc || '100';
2693
2694 if g_debug then
2695 hr_utility.trace('Populating all');
2696 end if;
2697 link_ap_details_all(
2698 p_detail_blocks => p_detail_blocks
2699 ,p_app_id => l_app_id
2700 ,p_time_category_id => l_time_category_id
2701 );
2702
2703 ELSE
2704 g_trace := l_proc || '110';
2705
2706 if g_debug then
2707 hr_utility.trace('Populating time category : ' || l_time_category_id );
2708 end if;
2709 link_ap_details(
2710 p_detail_blocks => p_detail_blocks
2711 ,p_attributes => p_detail_attributes
2712 ,p_time_category_id => l_time_category_id
2713 ,p_app_id => l_app_id
2714 );
2715
2716 g_trace := l_proc || '120';
2717 END IF;
2718
2719 IF (l_removed_blocks.COUNT > 0) THEN -- Bug 8685110, this count is 0 for empty timecards
2720 create_removed_links(l_removed_blocks, l_app_id);
2721 END IF;
2722
2723
2724 IF l_tab_type_a.COUNT > 0 THEN -- Bug 8685110
2725 IF l_blank = 'Y' THEN
2726
2727 FOR i IN l_tab_type_a.first..l_tab_type_a.last LOOP
2728
2729 open get_max_ovn(l_tab_type_a(i).p_id);
2730 fetch get_max_ovn into l_max_ovn;
2731 close get_max_ovn;
2732
2733 hxc_ap_detail_links_pkg.insert_summary_row(l_app_id, l_tab_type_a(i).p_id, l_max_ovn);
2734
2735 END LOOP;
2736 END IF;
2737 END IF;
2738
2739 g_trace := l_proc || '130';
2740
2741
2742 if g_debug then
2743 hr_utility.trace('Populating hxc_tc_ap_links');
2744 end if;
2745 --populate hxc_tc_ap_links
2746
2747 OPEN c_tc_ap_link(p_timecard_id, l_app_id);
2748 FETCH c_tc_ap_link INTO l_tc_ap_link_exists;
2749 CLOSE c_tc_ap_link;
2750
2751 g_trace := l_proc || '160';
2752
2753 IF l_tc_ap_link_exists IS NULL
2754 THEN
2755 g_trace := l_proc || '170';
2756 --
2757 -- 115.76 Change. It is ok to leave this call, since
2758 -- the link is explicitly checked not to exist in the
2759 -- first place.
2760 --
2761 hxc_tc_ap_links_pkg.insert_summary_row(
2762 p_timecard_id => p_timecard_id
2763 ,p_application_period_id => l_app_id
2764 );
2765 --
2766 --115.118, Bug - 5554020
2767 --
2768 hxc_timecard_summary_api.reevaluate_timecard_statuses(l_app_id);
2769
2770 g_trace := l_proc || '180';
2771 END IF;
2772
2773 if g_debug then
2774 hr_utility.trace('End generating app period');
2775 end if;
2776
2777 EXCEPTION
2778 WHEN OTHERS THEN
2779 hr_utility.trace('exception in generate_app_period - '||sqlerrm);
2780 RAISE;
2781
2782 END generate_app_period;
2783
2784
2785
2786 ------------------------- get_approval_style_id ----------------------------
2787 --
2788 FUNCTION get_approval_style_id(p_period_start_date date,
2789 p_period_end_date date,
2790 p_resource_id number) RETURN NUMBER IS
2791 --
2792 -- Andrew: Bug 3211251: Use date_to = end of time filter, instead of
2793 -- max ovn sub-query. Faster, and avoids picking up approval styles
2794 -- from deleted timecards.
2795 --
2796 -- Andrew: Bug 4178239: This cursor could previously pick up the
2797 -- approval styles associated with templates created in the same
2798 -- week as the timecard being approved. This could lead to the
2799 -- incorrect approval style being used. Thus, check the day
2800 -- driving the style is attached to an active timecard before
2801 -- choosing that style.
2802 --
2803 cursor csr_get_appr_style is
2804 SELECT day1.approval_style_id
2805 FROM hxc_time_building_blocks day1,
2806 hxc_time_building_blocks timecard
2807 WHERE day1.resource_id = p_resource_id
2808 AND day1.scope = 'DAY'
2809 AND day1.start_time BETWEEN p_period_start_date AND p_period_end_date
2810 AND day1.date_to = hr_general.end_of_time
2811 AND timecard.time_building_block_id = day1.parent_building_block_id
2812 AND timecard.object_version_number = day1.parent_building_block_ovn
2813 AND timecard.scope = 'TIMECARD'
2814 AND timecard.date_to = hr_general.end_of_time
2815 ORDER BY day1.start_time desc;
2816 --
2817 l_approval_style_id number;
2818 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.get_approval_style_id';
2819 --
2820 BEGIN
2821 --
2822 if g_debug then
2823 hr_utility.set_location(l_proc, 10);
2824 end if;
2825 --
2826 open csr_get_appr_style;
2827 fetch csr_get_appr_style into l_approval_style_id;
2828 IF csr_get_appr_style%NOTFOUND THEN
2829 --
2830 if g_debug then
2831 hr_utility.set_location(l_proc, 20);
2832 end if;
2833 --
2834 g_error_count := g_error_count + 1;
2835 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPR_STYLE';
2836 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2837 --
2838 hr_utility.set_message(809, 'HXC_APR_NO_APPR_STYLE');
2839 hr_utility.raise_error;
2840 --
2841 END IF;
2842 --
2843 close csr_get_appr_style;
2844 --
2845 RETURN(l_approval_style_id);
2846 --
2847 END get_approval_style_id;
2848
2849 Function dayHasActiveAssignment
2850 (p_assignment_periods in hxc_timecard_utilities.periods,
2851 p_day_start in date) return boolean is
2852 l_asg_index binary_integer;
2853 l_found boolean;
2854 Begin
2855 l_asg_index := p_assignment_periods.first;
2856 l_found := false;
2857 Loop
2858 Exit when (not p_assignment_periods.exists(l_asg_index) OR l_found);
2859 if(trunc(p_day_start) between trunc(p_assignment_periods(l_asg_index).start_date)
2860 and trunc(p_assignment_periods(l_asg_index).end_date)) then
2861 l_found := true;
2862 end if;
2863 l_asg_index := p_assignment_periods.next(l_asg_index);
2864 End Loop;
2865 return l_found;
2866 End dayHasActiveAssignment;
2867
2868 PROCEDURE create_appl_period_info(itemtype IN varchar2,
2869 itemkey IN varchar2,
2870 actid IN number,
2871 funcmode IN varchar2,
2872 result IN OUT NOCOPY varchar2) is
2873 --
2874 cursor csr_get_app_set_from_tc
2875 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type,
2876 p_timecard_ovn in hxc_time_building_blocks.object_version_number%type) is
2877 select to_char(application_set_id)
2878 from hxc_time_building_blocks
2879 where time_building_block_id = p_timecard_id
2880 and object_version_number = p_timecard_ovn;
2881 --
2882 cursor csr_get_tc_info(p_bld_blk_id number,
2883 p_ovn number) is
2884 select tc.resource_id, tc.start_time, tc.stop_time
2885 from hxc_time_building_blocks tc
2886 where tc.time_building_block_id = p_bld_blk_id
2887 and tc.object_version_number = p_ovn;
2888 --
2889 cursor csr_get_apps(p_app_set varchar2) is
2890 select htr.name,
2891 htr.application_id,
2892 htr.application_period_function,
2893 htr.time_recipient_id
2894 from hxc_application_sets_v has,
2895 hxc_application_set_comps_v hasc,
2896 hxc_time_recipients htr
2897 where to_char(has.application_set_id) = p_app_set
2898 and hasc.application_set_id = has.application_set_id
2899 and hasc.time_recipient_id = htr.time_recipient_id;
2900 --
2901 cursor csr_get_days(p_tc_bld_blk_id number,
2902 p_tc_ovn number) is
2903 select day.time_building_block_id,
2904 day.start_time,
2905 day.stop_time,
2906 day.object_version_number
2907 from hxc_time_building_blocks day
2908 where day.parent_building_block_id = p_tc_bld_blk_id
2909 and day.parent_building_block_ovn = p_tc_ovn
2910 and day.scope = 'DAY'
2911 and day.object_version_number = (select max(day2.object_version_number)
2912 from hxc_time_building_blocks day2
2913 where day.time_building_block_id =
2914 day2.time_building_block_id)
2915 order by 2;
2916 --
2917 ------ Project manager changes
2918 l_detail_project_id NUMBER;
2919 l_tab_project_id hxc_proj_manager_approval_pkg.tab_project_id;
2920
2921 l_index number;
2922 l_index_1 number;
2923 l_index_2 number;
2924 l_index_3 number;
2925 l_no_project_manager number;
2926 l_already_present number;
2927 l_approval_style_id number;
2928 l_original_approval_order number;
2929
2930
2931
2932
2933 l_tc_bld_blk_id number;
2934 l_tc_date_from date;
2935 l_tc_date_to date;
2936 l_tc_ovn number;
2937 l_tc_resubmitted varchar2(10);
2938 --
2939 l_tc_resource_id number;
2940 l_tc_start_time date;
2941 l_tc_stop_time date;
2942 --
2943 l_exist_bb_id number;
2944 l_exist_status varchar2(30);
2945 l_exist_ovn number;
2946 --l_first number;
2947 --
2948 l_app_set varchar2(150);
2949 l_application varchar2(80);
2950 l_application_id number;
2951 l_time_recipient_id number;
2952 l_time_recipient varchar2(150);
2953 l_app_period_func varchar2(240) := NULL;
2954 --
2955
2956 --
2957 l_rec_period_id number;
2958 --
2959 l_day_bld_blk_id number;
2960 l_day_start_time date;
2961 l_day_stop_time date;
2962 l_day_ovn number;
2963 l_appl_period_bb_id number;
2964 l_appl_period_bb_ovn number;
2965 l_period_start_date date;
2966 l_period_end_date date;
2967 l_override_allowed boolean;
2968 t_attributes hxc_time_attributes_api.timecard;
2969 --
2970 l_all_apps varchar2(1000);
2971 l_cnt number;
2972 --
2973 l_exists varchar2(1);
2974 l_chk_days varchar2(1);
2975 l_item_key wf_items.item_key%type;
2976 l_process_name varchar2(30);
2977 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.create_appl_period_info';
2978
2979 --
2980 l_detail_blocks block_table;
2981 l_detail_attributes hxc_self_service_time_deposit.building_block_attribute_info;
2982
2983 l_new_detail_blocks hxc_block_table_type := hxc_block_table_type ();
2984 l_new_detail_attributes hxc_attribute_table_type := hxc_attribute_table_type ();
2985
2986 l_approval_style hxc_approval_styles.approval_style_id%TYPE;
2987 l_assignment_periods hxc_timecard_utilities.periods;
2988
2989
2990 CURSOR c_approval_comp(
2991 p_approval_style hxc_approval_styles.approval_style_id%TYPE
2992 ,p_time_recipient hxc_time_recipients.time_recipient_id%TYPE
2993 )
2994 IS
2995 SELECT approval_comp_id
2996 ,object_version_number
2997 ,approval_mechanism
2998 ,approval_mechanism_id
2999 ,wf_item_type
3000 ,wf_name
3001 ,time_category_id
3002 ,approval_order
3003 FROM hxc_approval_comps
3004 WHERE approval_style_id = p_approval_style
3005 AND time_recipient_id = p_time_recipient;
3006
3007
3008 CURSOR c_ela_comps(
3009 p_comp_id hxc_approval_comps.approval_comp_id%TYPE
3010 ,p_comp_ovn hxc_approval_comps.object_version_number%TYPE
3011 )
3012 IS
3013 SELECT approval_comp_id
3014 ,object_version_number
3015 ,approval_mechanism
3016 ,approval_mechanism_id
3017 ,wf_item_type
3018 ,wf_name
3019 ,time_category_id
3020 ,approval_order
3021 FROM hxc_approval_comps
3022 WHERE parent_comp_id = p_comp_id
3023 AND parent_comp_ovn = p_comp_ovn
3024 ORDER BY time_category_id desc;
3025
3026
3027 -- Bug 13817976
3028 -- Modified cursor to pick up only a subset of time categories.
3029 CURSOR c_ela_comps2(
3030 p_comp_id hxc_approval_comps.approval_comp_id%TYPE
3031 ,p_comp_ovn hxc_approval_comps.object_version_number%TYPE
3032 )
3033 IS
3034 SELECT approval_comp_id
3035 ,object_version_number
3036 ,approval_mechanism
3037 ,approval_mechanism_id
3038 ,wf_item_type
3039 ,wf_name
3040 ,time_category_id
3041 ,approval_order
3042 FROM hxc_approval_comps
3043 WHERE parent_comp_id = p_comp_id
3044 AND parent_comp_ovn = p_comp_ovn
3045 AND ( time_category_id = 0
3046 OR time_category_id IN ( SELECT detail_id
3047 FROM hxc_archive_temp))
3048 ORDER BY time_category_id desc;
3049
3050
3051 -- Picks up the subset as required in the above cursor.
3052 -- Currently this can work only for Projects application or
3053 -- any application for which Attribute1 is always populated.
3054
3055 CURSOR get_time_categories
3056 IS SELECT /*+ LEADING(tmp)
3057 INDEX(tcc HXC_HTCC2_FK1) */
3058 DISTINCT time_category_id
3059 FROM hxc_tmp_atts tmp,
3060 hxc_time_category_comps2 tcc
3061 WHERE tmp.bld_blk_info_type_id = tcc.bld_blk_info_type_id
3062 AND tmp.attribute_category = tcc.attribute_category
3063 AND tmp.attribute1 = tcc.attribute1
3064 AND (tmp.attribute2 = tcc.attribute2
3065 OR tcc.attribute2 = 'NULL')
3066 AND (tmp.attribute3 = tcc.attribute3
3067 OR tcc.attribute3 = 'NULL')
3068 AND (tmp.attribute4 = tcc.attribute4
3069 OR tcc.attribute4 = 'NULL')
3070 AND (tmp.attribute5 = tcc.attribute5
3071 OR tcc.attribute5 = 'NULL')
3072 AND (tmp.attribute6 = tcc.attribute6
3073 OR tcc.attribute6 = 'NULL')
3074 AND (tmp.attribute7 = tcc.attribute7
3075 OR tcc.attribute7 = 'NULL')
3076 AND (tmp.attribute8 = tcc.attribute8
3077 OR tcc.attribute8 = 'NULL')
3078 AND (tmp.attribute9 = tcc.attribute9
3079 OR tcc.attribute9 = 'NULL')
3080 AND (tmp.attribute10 = tcc.attribute10
3081 OR tcc.attribute10 = 'NULL')
3082 AND (tmp.attribute11 = tcc.attribute11
3083 OR tcc.attribute11 = 'NULL')
3084 AND (tmp.attribute12 = tcc.attribute12
3085 OR tcc.attribute12 = 'NULL')
3086 AND (tmp.attribute13 = tcc.attribute13
3087 OR tcc.attribute13 = 'NULL')
3088 AND (tmp.attribute14 = tcc.attribute14
3089 OR tcc.attribute14 = 'NULL')
3090 AND (tmp.attribute15 = tcc.attribute15
3091 OR tcc.attribute15 = 'NULL')
3092 AND (tmp.attribute16 = tcc.attribute16
3093 OR tcc.attribute16 = 'NULL')
3094 AND (tmp.attribute17 = tcc.attribute17
3095 OR tcc.attribute17 = 'NULL')
3096 AND (tmp.attribute18 = tcc.attribute18
3097 OR tcc.attribute18 = 'NULL')
3098 AND (tmp.attribute19 = tcc.attribute19
3099 OR tcc.attribute19 = 'NULL')
3100 AND (tmp.attribute20 = tcc.attribute20
3101 OR tcc.attribute20 = 'NULL')
3102 AND (tmp.attribute21 = tcc.attribute21
3103 OR tcc.attribute21 = 'NULL')
3104 AND (tmp.attribute22 = tcc.attribute22
3105 OR tcc.attribute22 = 'NULL')
3106 AND (tmp.attribute23 = tcc.attribute23
3107 OR tcc.attribute23 = 'NULL')
3108 AND (tmp.attribute24 = tcc.attribute24
3109 OR tcc.attribute24 = 'NULL')
3110 AND (tmp.attribute25 = tcc.attribute25
3111 OR tcc.attribute25 = 'NULL')
3112 AND (tmp.attribute26 = tcc.attribute26
3113 OR tcc.attribute26 = 'NULL')
3114 AND (tmp.attribute27 = tcc.attribute27
3115 OR tcc.attribute27 = 'NULL')
3116 AND (tmp.attribute28 = tcc.attribute28
3117 OR tcc.attribute28 = 'NULL')
3118 AND (tmp.attribute29 = tcc.attribute29
3119 OR tcc.attribute29 = 'NULL')
3120 AND (tmp.attribute30 = tcc.attribute30
3121 OR tcc.attribute30 = 'NULL');
3122
3123
3124 TYPE NUMTAB IS TABLE OF NUMBER;
3125 l_cat_tab NUMTAB;
3126
3127
3128 cursor c_app_overlap_data_set(l_app_start_date hxc_time_building_blocks.start_time%type,
3129 l_app_stop_date hxc_time_building_blocks.start_time%type,
3130 l_tc_start_time hxc_time_building_blocks.start_time%type )
3131 is
3132 select '1' from hxc_data_sets
3133 where (((l_app_start_date between start_date and end_date) and (l_app_start_date<l_tc_start_time))
3134 or l_app_stop_date between start_date and end_date)
3135 and status in('OFF_LINE','RESTORE_IN_PROGRESS','BACKUP_IN_PROGRESS');
3136
3137 l_approval_comp approval_comp;
3138 l_ela_comp approval_comp;
3139 l_default_comp approval_comp;
3140 l_count NUMBER;
3141 l_dummy NUMBER;
3142
3143 -- Bug 10164135
3144 -- New variables.
3145 l_pref_table hxc_preference_evaluation.t_pref_table;
3146 l_elp_rule NUMBER := 0;
3147
3148 l_processed_app_start hxc_time_building_blocks.start_time%TYPE;
3149 l_processed_app_stop hxc_time_building_blocks.stop_time%TYPE;
3150 l_gen_app_period boolean;
3151
3152 BEGIN
3153
3154 g_debug:=hr_utility.debug_enabled;
3155 g_trace := '10';
3156 if g_debug then
3157 hr_utility.set_location(l_proc, 10);
3158 end if;
3159
3160 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
3161 (itemtype => itemtype,
3162 itemkey => itemkey,
3163 aname => 'TC_BLD_BLK_ID');
3164
3165 if g_debug then
3166 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
3167 end if;
3168 l_tc_ovn := wf_engine.GetItemAttrNumber
3169 (itemtype => itemtype,
3170 itemkey => itemkey,
3171 aname => 'TC_BLD_BLK_OVN');
3172
3173 if g_debug then
3174 hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
3175 end if;
3176
3177 l_tc_resubmitted := wf_engine.GetItemAttrText
3178 (itemtype => itemtype,
3179 itemkey => itemkey,
3180 aname => 'TC_RESUBMITTED');
3181
3182 if g_debug then
3183 hr_utility.trace('Timecard Resubmitted is : ' || l_tc_resubmitted);
3184 end if;
3185
3186 g_trace := '20';
3187
3188 open csr_get_tc_info(l_tc_bld_blk_id,
3189 l_tc_ovn);
3190 fetch csr_get_tc_info into l_tc_resource_id,
3191 l_tc_start_time,
3192 l_tc_stop_time;
3193
3194
3195 IF csr_get_tc_info%NOTFOUND
3196 THEN
3197
3198 g_trace := '30';
3199
3200 g_error_count := g_error_count + 1;
3201 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
3202 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3203
3204 hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
3205 hr_utility.raise_error;
3206 END IF;
3207 g_trace := '40';
3208
3209 close csr_get_tc_info;
3210
3211
3212 -- Bug 10164135
3213 -- Picking up the ELP time entry rule.
3214 hxc_preference_evaluation.resource_preferences(p_resource_id => l_tc_resource_id,
3215 p_start_evaluation_date => l_tc_start_time,
3216 p_end_evaluation_date => TRUNC(l_tc_stop_time),
3217 p_pref_table => l_pref_table);
3218
3219
3220 l_index := l_pref_table.FIRST;
3221 LOOP
3222 IF l_pref_table(l_index).preference_code = 'TS_PER_ELP_RULES'
3223 THEN
3224 l_elp_rule := l_pref_table(l_index).attribute1;
3225 hr_utility.trace('l_elp_rule = '||l_elp_rule);
3226 EXIT;
3227 END IF;
3228 l_index := l_pref_table.NEXT(l_index);
3229 EXIT WHEN NOT l_pref_table.EXISTS(l_index);
3230 END LOOP;
3231
3232
3233
3234
3235 -- Bug 4716082, try the application set on the timecard first, not the current preference
3236 open csr_get_app_set_from_tc(l_tc_bld_blk_id,l_tc_ovn);
3237 fetch csr_get_app_set_from_tc into l_app_set;
3238 if((csr_get_app_set_from_tc%notfound)OR(l_app_set is null)) then
3239 close csr_get_app_set_from_tc;
3240 g_trace := '45';
3241 l_app_set := hxc_preference_evaluation.resource_preferences
3242 (p_resource_id => l_tc_resource_id,
3243 p_pref_code => 'TS_PER_APPLICATION_SET',
3244 p_attribute_n => 1);
3245 else
3246 g_trace := '47 -'||l_app_set;
3247 close csr_get_app_set_from_tc;
3248 end if;
3249
3250 g_trace := '50';
3251
3252 open csr_get_apps(l_app_set);
3253 fetch csr_get_apps into l_application,
3254 l_application_id,
3255 l_app_period_func,
3256 l_time_recipient_id;
3257
3258 IF csr_get_apps%NOTFOUND
3259 THEN
3260
3261 g_trace := '60';
3262
3263 g_error_count := g_error_count + 1;
3264 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPL_SET_PREF';
3265 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3266
3267 hr_utility.set_message(809, 'HXC_APR_NO_APPL_SET_PREF');
3268 hr_utility.raise_error;
3269
3270 END IF;
3271 close csr_get_apps;
3272
3273 l_assignment_periods
3274 := hxc_timecard_utilities.get_assignment_periods(l_tc_resource_id);
3275
3276
3277 g_trace := '70';
3278
3279 open csr_get_apps(l_app_set);
3280
3281 LOOP -- loop through all apps for this timecard
3282
3283 g_trace := '80';
3284
3285 fetch csr_get_apps into l_application,
3286 l_application_id,
3287 l_app_period_func,
3288 l_time_recipient_id;
3289
3290 exit when csr_get_apps%NOTFOUND;
3291
3292 g_trace := '90 Application=' || l_application;
3293 g_trace := '90 Time Recipient ID=' || to_char(l_time_recipient_id);
3294
3295 if g_debug then
3296 hr_utility.trace('90 Application=' || l_application);
3297 hr_utility.trace('90 Time Recipient ID=' || to_char(l_time_recipient_id));
3298 end if;
3299
3300 l_processed_app_start := NULL;
3301 l_processed_app_stop := NULL;
3302
3303 -- open cursor to get all related DAY blocks for this timecard.
3304
3305 open csr_get_days(l_tc_bld_blk_id, l_tc_ovn);
3306 LOOP -- loop through all related days
3307 g_trace := '100';
3308
3309 fetch csr_get_days into l_day_bld_blk_id,
3310 l_day_start_time,
3311 l_day_stop_time,
3312 l_day_ovn;
3313 exit when csr_get_days%NOTFOUND;
3314
3315 if(dayHasActiveAssignment(l_assignment_periods,l_day_start_time))then
3316
3317 if g_debug then
3318 hr_utility.set_location(l_proc, 90);
3319 hr_utility.trace('day start=' || to_char(l_day_start_time, 'YYYY/MM/DD'));
3320 end if;
3321
3322 get_application_period
3323 (p_app_period_func => l_app_period_func,
3324 p_resource_id => l_tc_resource_id,
3325 p_day => l_day_start_time,
3326 p_time_recipient => l_time_recipient_id,
3327 p_tc_start_time => l_tc_start_time,
3328 p_tc_stop_time => l_tc_stop_time,
3329 p_assignment_periods => l_assignment_periods,
3330 p_period_start => l_period_start_date,
3331 p_period_end => l_period_end_date
3332 );
3333
3334 g_trace := '120' || 'app_start=' || to_char(l_period_start_date, 'YYYY/MM/DD')
3335 || '|app_end=' || to_char(l_period_end_date, 'YYYY/MM/DD');
3336
3337
3338 l_gen_app_period:=true;
3339
3340 open c_app_overlap_data_set(l_period_start_date,l_period_end_date,l_tc_start_time);
3341 fetch c_app_overlap_data_set into l_dummy;
3342 if(c_app_overlap_data_set%found) then
3343 l_gen_app_period:=false;
3344 else
3345 l_gen_app_period:=true;
3346 end if;
3347 close c_app_overlap_data_set;
3348
3349 IF l_processed_app_start IS NULL
3350 OR (l_processed_app_start IS NOT NULL
3351 AND l_processed_app_stop IS NOT NULL
3352 AND l_processed_app_start <> l_period_start_date) THEN
3353 l_processed_app_start := l_period_start_date;
3354 l_processed_app_stop := l_period_end_date;
3355
3356 l_approval_style := get_approval_style_id
3357 (p_period_start_date => l_period_start_date,
3358 p_period_end_date => l_period_end_date,
3359 p_resource_id => l_tc_resource_id
3360 );
3361
3362 g_trace := '130 approval style_id=' || l_approval_style;
3363 --prepare data
3364
3365
3366 IF g_debug THEN
3367 hr_utility.trace('l_approval_style ::'||l_approval_style);
3368 hr_utility.trace('l_elp_rule ::'||l_elp_rule);
3369 hr_utility.trace('Profile value of OTL: Verify Entry Level Application Set for Approval is :: '||
3370 NVL(FND_PROFILE.value('HXC_VERIFY_APPLN_SET_FOR_APPROVAL'),'N'));
3371 END IF;
3372
3373 -- Bug 10164135
3374 -- Check if ELP rule is set. Call accordingly.
3375 IF l_elp_rule <> 0
3376
3377 -- Bug 11075779
3378 -- Added the profile option to toggle this behavior.
3379 AND NVL(FND_PROFILE.value('HXC_VERIFY_APPLN_SET_FOR_APPROVAL'),'N')
3380 = 'Y'
3381
3382 THEN
3383 IF g_debug
3384 THEN
3385 hr_utility.trace('ELP Rule Turned ON '||l_elp_rule);
3386 hr_utility.trace('l_time_reciipeint '||l_time_recipient_id);
3387 END IF;
3388 get_detail_blocks(p_timecard_id => l_tc_bld_blk_id,
3389 p_timecard_ovn => l_tc_ovn,
3390 p_start_time => l_period_start_date,
3391 p_stop_time => l_period_end_date,
3392 p_detail_blocks => l_detail_blocks,
3393 p_new_detail_blocks => l_new_detail_blocks,
3394 p_time_recipient_id => l_time_recipient_id
3395 );
3396
3397 -- Bug 13036988
3398 -- Below condition will be called only when ELP rule is set.
3399 IF g_debug THEN
3400 hr_utility.trace('l_new_detail_blocks.COUNT ::'||l_new_detail_blocks.COUNT);
3401 hr_utility.trace('l_detail_blocks.COUNT ::'||l_detail_blocks.COUNT );
3402 END IF;
3403
3404
3405 IF l_new_detail_blocks.COUNT = 0
3406 OR l_detail_blocks.COUNT = 0
3407 THEN
3408 EXIT ;
3409 END IF;
3410
3411 IF g_debug
3412 THEN
3413 hr_utility.trace(' Printing detail blocks ');
3414 l_index := l_detail_blocks.FIRST;
3415 LOOP
3416 hr_utility.trace('l_detail_blocks(l_index).id '||l_detail_blocks(l_index).block_id);
3417 hr_utility.trace('l_detail_blocks(l_index).ovn '||l_detail_blocks(l_index).block_ovn);
3418 l_index := l_detail_blocks.NEXT(l_index);
3419 EXIT WHEN NOT l_detail_blocks.EXISTS(l_index);
3420 END LOOP;
3421 END IF;
3422
3423 ELSE
3424 get_detail_blocks(p_timecard_id => l_tc_bld_blk_id,
3425 p_timecard_ovn => l_tc_ovn,
3426 p_start_time => l_period_start_date,
3427 p_stop_time => l_period_end_date,
3428 p_detail_blocks => l_detail_blocks,
3429 p_new_detail_blocks => l_new_detail_blocks
3430 );
3431 END IF;
3432
3433
3434 OPEN c_approval_comp(l_approval_style, l_time_recipient_id);
3435 FETCH c_approval_comp INTO l_approval_comp;
3436
3437 if c_approval_comp%notfound then
3438 g_trace := l_approval_style||' - '||l_time_recipient_id;
3439 close c_approval_comp;
3440 fnd_message.set_name(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3441 fnd_message.set_token('PROCEDURE', l_proc);
3442 fnd_message.set_token('STEP', '130');
3443 fnd_message.raise_error;
3444 end if;
3445
3446 close c_approval_comp;
3447
3448 /* We check whether the approval style is Project manager, if so we
3449 need to replace it with the special ELA approval style created */
3450
3451
3452 IF l_approval_comp.approval_mechanism = 'PROJECT_MANAGER' THEN
3453
3454 -- Bug 4297436. Setting the original Project Manager Sequenceto the special ELA style app. period.
3455 -- Change for version 115.94
3456
3457 l_original_approval_order := l_approval_comp.approval_order ;
3458
3459 l_approval_style_id := l_approval_style;
3460 get_detail_attributes(p_detail_blocks => l_detail_blocks,
3461 p_detail_attributes => l_detail_attributes,
3462 p_new_detail_attributes => l_new_detail_attributes);
3463
3464 /* looping through all the projects present in the Timecard */
3465
3466 l_index_1 := 1;
3467 l_index := l_detail_attributes.first;
3468 WHILE l_index IS NOT NULL LOOP
3469 IF l_detail_attributes(l_index).attribute_category = 'PROJECTS' THEN
3470 l_detail_project_id := l_detail_attributes(l_index).attribute1;
3471 l_already_present := 0;
3472 /*l_no_project_manager := 0;
3473
3474 IF NOT ( g_tab_project_id.exists(l_detail_project_id ) ) THEN
3475 g_tab_project_id( l_detail_project_id).manager_id := NULL;
3476 l_no_project_manager := 1;
3477 ELSIF g_tab_project_id(l_detail_project_id).manager_id IS NULL THEN
3478 l_no_project_manager := 1;
3479 END IF;
3480
3481 IF l_no_project_manager = 1 THEN*/
3482
3483 l_index_2 := l_tab_project_id.first;
3484 WHILE l_index_2 IS NOT NULL LOOP
3485 IF l_tab_project_id(l_index_2).project_id = l_detail_project_id THEN
3486 l_already_present := 1;
3487 EXIT;
3488 END IF;
3489 l_index_2 := l_tab_project_id.next (l_index_2 );
3490 END LOOP;
3491 IF l_already_present = 0 THEN
3492 l_tab_project_id(l_index_1).project_id := l_detail_attributes(l_index).attribute1;
3493 l_index_1 := l_index_1 + 1;
3494 END IF;
3495 --END IF; -- if no project manager block
3496 END IF; -- if PROJECTS block
3497 l_index := l_detail_attributes.next(l_index );
3498 END LOOP;
3499
3500 /* call the procedure to replace the proj. manager approval style by the special ELA approval style */
3501
3502 hxc_proj_manager_approval_pkg.replace_projman_by_spl_ela
3503 (p_tab_project_id => l_tab_project_id,
3504 p_new_spl_ela_style_id => l_approval_style);
3505
3506 l_index_3 := l_tab_project_id.first;
3507
3508
3509 WHILE l_index_3 IS NOT NULL LOOP
3510 g_tab_project_id( l_tab_project_id( l_index_3 ).project_id ).manager_id := l_tab_project_id( l_index_3 ).manager_id;
3511 l_index_3 := l_tab_project_id.next( l_index_3 ) ;
3512 END LOOP;
3513
3514 /* After replacement finding the approval comp associated with the spl. ela style */
3515
3516 OPEN c_approval_comp(l_approval_style, l_time_recipient_id);
3517 FETCH c_approval_comp INTO l_approval_comp;
3518 CLOSE c_approval_comp;
3519
3520 l_approval_comp.approval_order := l_original_approval_order ;
3521
3522 END IF; --- If Project manager block
3523
3524
3525
3526 IF l_approval_comp.approval_mechanism = 'ENTRY_LEVEL_APPROVAL' THEN
3527 if g_debug then
3528 hr_utility.trace(l_proc || 'Entry level approvals 150');
3529 end if;
3530
3531 g_trace := '150 ELA';
3532
3533 --get detail attributes
3534 get_detail_attributes
3535 (p_detail_blocks => l_detail_blocks,
3536 p_detail_attributes => l_detail_attributes,
3537 p_new_detail_attributes => l_new_detail_attributes );
3538
3539
3540 -- push the block and attribute structures into the temporary
3541 -- tables used by Time Categories
3542
3543 hxc_time_category_utils_pkg.push_timecard ( l_new_detail_blocks, l_new_detail_attributes, TRUE );
3544
3545 if g_debug then
3546 hr_utility.trace(l_proc || 'Entry level approvals 160');
3547 end if;
3548
3549 -- Bug 13817976
3550 -- Added the first half of the following IF construct.
3551 -- New profile option used to switch on the behavior.
3552 -- Description inline.
3553
3554 IF NVL(FND_PROFILE.VALUE('HXC_ELA_RESTRUCTURED_TC'),'N') = 'Y'
3555 THEN
3556
3557 -- Delete everything from the GTT, just in case a previous timecard left something.
3558 DELETE FROM hxc_archive_temp;
3559
3560 -- Get the time categories that matter for this timecard.
3561 OPEN get_time_categories;
3562
3563 FETCH get_time_categories BULK COLLECT INTO l_cat_tab;
3564
3565 CLOSE get_time_categories;
3566
3567 -- Insert these time categories to the temp table.
3568 IF l_cat_tab.COUNT > 0
3569 THEN
3570 FORALL i IN l_cat_tab.FIRST..l_cat_tab.LAST
3571 INSERT INTO hxc_archive_temp
3572 (detail_id)
3573 VALUES
3574 (l_cat_tab(i));
3575
3576 END IF;
3577
3578
3579 -- Open the changed cursor to pick up only the time categories that matter.
3580 OPEN c_ela_comps2(l_approval_comp.approval_comp_id,
3581 l_approval_comp.object_version_number);
3582
3583 LOOP
3584 FETCH c_ela_comps2 INTO l_ela_comp;
3585 EXIT WHEN c_ela_comps2%NOTFOUND;
3586
3587 IF g_debug
3588 THEN
3589 hr_utility.trace('ASH:Y-TC '||l_ela_comp.time_category_id);
3590 END IF;
3591
3592 IF l_ela_comp.time_category_id = 0 THEN
3593 l_default_comp := l_ela_comp;
3594 ELSE
3595 if g_debug then
3596 hr_utility.trace(l_proc || 'Entry level approvals 170-1');
3597 end if;
3598 g_trace := '170 ELA generating period-1';
3599
3600 if(l_gen_app_period) then
3601 generate_app_period(p_item_type => itemtype,
3602 p_item_key => itemkey,
3603 p_timecard_id => l_tc_bld_blk_id,
3604 p_resource_id => l_tc_resource_id,
3605 p_start_time => l_period_start_date,
3606 p_stop_time => l_period_end_date,
3607 p_time_recipient_id => l_time_recipient_id,
3608 p_recipient_sequence=> l_approval_comp.approval_order,
3609 p_approval_comp => l_ela_comp,
3610 p_tc_resubmitted => l_tc_resubmitted,
3611 p_detail_blocks => l_detail_blocks,
3612 p_detail_attributes => l_detail_attributes
3613 );
3614 end if;
3615
3616 if g_debug then
3617 hr_utility.trace(l_proc || 'Entry level approvals 180-1');
3618 end if;
3619 g_trace :=' 180 ELA finish generating period-1';
3620
3621 END IF;
3622
3623 END LOOP;
3624
3625 CLOSE c_ela_comps2;
3626
3627 ELSE
3628
3629 OPEN c_ela_comps(l_approval_comp.approval_comp_id,
3630 l_approval_comp.object_version_number);
3631
3632 LOOP
3633 FETCH c_ela_comps INTO l_ela_comp;
3634 EXIT WHEN c_ela_comps%NOTFOUND;
3635
3636 IF g_debug
3637 THEN
3638 hr_utility.trace('ASH:N-TC '||l_ela_comp.time_category_id);
3639 END IF;
3640
3641 IF l_ela_comp.time_category_id = 0 THEN
3642 l_default_comp := l_ela_comp;
3643 ELSE
3644 if g_debug
3645 then
3646 hr_utility.trace(l_proc || 'Entry level approvals 170');
3647 end if;
3648 g_trace := '170 ELA generating period';
3649
3650 if(l_gen_app_period)
3651 then
3652 generate_app_period(p_item_type => itemtype,
3653 p_item_key => itemkey,
3654 p_timecard_id => l_tc_bld_blk_id,
3655 p_resource_id => l_tc_resource_id,
3656 p_start_time => l_period_start_date,
3657 p_stop_time => l_period_end_date,
3658 p_time_recipient_id => l_time_recipient_id,
3659 p_recipient_sequence=> l_approval_comp.approval_order,
3660 p_approval_comp => l_ela_comp,
3661 p_tc_resubmitted => l_tc_resubmitted,
3662 p_detail_blocks => l_detail_blocks,
3663 p_detail_attributes => l_detail_attributes
3664 );
3665 end if;
3666
3667 if g_debug
3668 then
3669 hr_utility.trace(l_proc || 'Entry level approvals 180');
3670 end if;
3671 g_trace :=' 180 ELA finish generating period';
3672
3673 END IF;
3674
3675 END LOOP;
3676
3677 CLOSE c_ela_comps;
3678 END IF;
3679
3680 if g_debug then
3681 hr_utility.trace(l_proc || 'Entry level approvals 200');
3682 end if;
3683 g_trace := '200 any detail left??';
3684
3685 --Now take care of the rest of the blocks
3686 l_count := get_rest_detail_blocks(l_detail_blocks);
3687
3688 if g_debug then
3689 hr_utility.trace('210 rest_detail_count=' || l_count);
3690 end if;
3691
3692 g_trace := '210 rest_detail_count=' || l_count;
3693 if g_debug then
3694 hr_utility.trace('220 rest_detail_count > 0');
3695 end if;
3696
3697 g_trace := '220 rest_detail_count > 0';
3698
3699 if(l_gen_app_period) then
3700 generate_app_period(p_item_type => itemtype,
3701 p_item_key => itemkey,
3702 p_timecard_id => l_tc_bld_blk_id,
3703 p_resource_id => l_tc_resource_id,
3704 p_start_time => l_period_start_date,
3705 p_stop_time => l_period_end_date,
3706 p_time_recipient_id => l_time_recipient_id,
3707 p_recipient_sequence=> l_approval_comp.approval_order,
3708 p_approval_comp => l_default_comp,
3709 p_tc_resubmitted => l_tc_resubmitted,
3710 p_detail_blocks => l_detail_blocks,
3711 p_detail_attributes => l_detail_attributes
3712 );
3713 end if;
3714
3715 if g_debug then
3716 hr_utility.trace('230 finished generating period for rest details');
3717 end if;
3718 g_trace := '230 finished generating period for rest details';
3719 ELSE
3720 if g_debug then
3721 hr_utility.trace('250 NON ELA');
3722 end if;
3723 g_trace := '250 NON ELA';
3724
3725 -- non ELA mechanism
3726 if(l_gen_app_period) then
3727 generate_app_period(p_item_type => itemtype,
3728 p_item_key => itemkey,
3729 p_timecard_id => l_tc_bld_blk_id,
3730 p_resource_id => l_tc_resource_id,
3731 p_start_time => l_period_start_date,
3732 p_stop_time => l_period_end_date,
3733 p_time_recipient_id => l_time_recipient_id,
3734 p_recipient_sequence=> l_approval_comp.approval_order,
3735 p_approval_comp => l_approval_comp,
3736 p_tc_resubmitted => l_tc_resubmitted,
3737 p_detail_blocks => l_detail_blocks,
3738 p_detail_attributes => l_detail_attributes
3739 );
3740 end if;
3741
3742 if g_debug then
3743 hr_utility.trace( '260 finished generating period for NON ELA');
3744 end if;
3745 g_trace := '260 finished generating period for NON ELA';
3746 END IF;
3747 END IF;
3748
3749 End If; -- Is the day within an active assignment.
3750
3751 END LOOP; -- loop through all related days
3752
3753 close csr_get_days;
3754
3755 END LOOP; -- loop through all apps for this timecard
3756
3757 --OIT Enhancement.
3758 --FYI Notification to WORKER on timecard SUBMISSION
3759 hxc_approval_wf_helper.set_notif_attribute_values
3760 (itemtype,
3761 itemkey,
3762 hxc_app_comp_notifications_api.c_action_submission,
3763 hxc_app_comp_notifications_api.c_recipient_worker
3764 );
3765
3766 if g_debug then
3767 hr_utility.set_location(l_proc, 200);
3768 end if;
3769 close csr_get_apps;
3770
3771 if g_debug then
3772 hr_utility.trace('300 END of create_appl_period_info');
3773 end if;
3774
3775
3776 g_trace := '300 END of create_appl_period_info';
3777
3778 result := '';
3779 return;
3780
3781 exception
3782 when others then
3783 -- The line below records this function call in the error system
3784 -- in the case of an exception.
3785 --
3786 if g_debug then
3787 hr_utility.set_location(l_proc, 999);
3788 --
3789 hr_utility.trace('IN EXCEPTION IN create_appl_period_info');
3790 --
3791 end if;
3792 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.create_appl_period_info',
3793 itemtype, itemkey, to_char(actid), funcmode, g_trace);
3794
3795 raise;
3796 result := '';
3797 return;
3798 --
3799 --
3800
3801 END create_appl_period_info;
3802 --
3803
3804 --
3805
3806
3807 FUNCTION chk_app_approved(
3808 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
3809 ,p_period_start_date IN hxc_time_building_blocks.start_time%TYPE
3810 ,p_period_end_date IN hxc_time_building_blocks.stop_time%TYPE
3811 ,p_time_recipient_id IN hxc_time_recipients.time_recipient_id%TYPE
3812 ,p_recipient_sequence IN hxc_app_period_summary.recipient_sequence%TYPE
3813 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
3814 ,p_category_sequence IN hxc_app_period_summary.category_sequence%TYPE
3815 )
3816 RETURN VARCHAR2
3817 IS
3818
3819 CURSOR csr_chk(
3820 p_date DATE
3821 ,p_resource_id hxc_time_building_blocks.resource_id%TYPE
3822 ,p_period_start_date hxc_time_building_blocks.start_time%TYPE
3823 ,p_period_end_date hxc_time_building_blocks.stop_time%TYPE
3824 ,p_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE
3825 ,p_recipient_sequence hxc_app_period_summary.recipient_sequence%TYPE
3826 ,p_time_category_id hxc_time_categories.time_category_id%TYPE
3827 ,p_category_sequence hxc_app_period_summary.category_sequence%TYPE
3828 )
3829 IS
3830 SELECT aps.approval_status
3831 FROM hxc_app_period_summary aps
3832 WHERE aps.resource_id = p_resource_id
3833 AND p_date BETWEEN aps.start_time AND aps.stop_time
3834 AND aps.approval_status <> 'APPROVED'
3835 AND (
3836 (aps.recipient_sequence < p_recipient_sequence)
3837 OR (p_category_sequence IS NOT NULL
3838 AND aps.time_recipient_id = p_time_recipient_id
3839 AND aps.recipient_sequence = p_recipient_sequence
3840 AND aps.time_category_id IS NOT NULL
3841 AND aps.category_sequence IS NOT NULL
3842 AND aps.time_category_id = p_time_category_id
3843 AND aps.category_sequence < p_category_sequence)
3844 )
3845 AND exists
3846 (select 'Y'
3847 from hxc_tc_ap_links tcl
3848 where tcl.application_period_id = aps.application_period_id
3849 );
3850
3851 l_days number := p_period_end_date - p_period_start_date + 1;
3852 l_date date;
3853 l_approved varchar2(1);
3854 l_approval_status varchar2(30);
3855 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_app_approved';
3856
3857 BEGIN
3858
3859 if g_debug then
3860 hr_utility.set_location(l_proc, 10);
3861 end if;
3862 l_date := p_period_start_date;
3863
3864 l_approved := 'Y';
3865
3866 FOR i in 1 .. l_days LOOP
3867
3868 OPEN csr_chk(
3869 p_date => l_date
3870 ,p_resource_id => p_resource_id
3871 ,p_period_start_date => p_period_start_date
3872 ,p_period_end_date => p_period_end_date
3873 ,p_time_recipient_id => p_time_recipient_id
3874 ,p_recipient_sequence => p_recipient_sequence
3875 ,p_time_category_id => p_time_category_id
3876 ,p_category_sequence => p_category_sequence
3877 );
3878
3879 FETCH csr_chk into l_approval_status;
3880
3881 IF csr_chk%FOUND
3882 THEN
3883 CLOSE csr_chk;
3884
3885 RETURN 'N'; --not completed approved
3886 END IF;
3887
3888 CLOSE csr_chk;
3889
3890 l_date := p_period_start_date + i;
3891
3892 END LOOP;
3893
3894 if g_debug then
3895 hr_utility.set_location(l_proc, 4);
3896 end if;
3897
3898 RETURN 'Y';
3899
3900 END chk_app_approved;
3901
3902
3903 FUNCTION has_valid_assign(
3904 p_day IN DATE
3905 ,p_assignment_periods IN hxc_timecard_utilities.periods
3906 )
3907 RETURN BOOLEAN
3908 IS
3909 i pls_integer;
3910 Begin
3911
3912 i := p_assignment_periods.first;
3913 Loop
3914 Exit when NOT p_assignment_periods.exists(i);
3915 IF p_day BETWEEN p_assignment_periods(i).start_date AND p_assignment_periods(i).end_date then
3916 return true;
3917 End if;
3918 i := p_assignment_periods.next(i);
3919 End loop;
3920
3921 return false;
3922
3923 End has_valid_assign;
3924
3925 FUNCTION is_submitted(
3926 p_day IN DATE
3927 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
3928 )
3929 RETURN BOOLEAN
3930 IS
3931 CURSOR c_submitted(
3932 p_day IN DATE
3933 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
3934 )
3935 IS
3936 SELECT 'Y'
3937 FROM hxc_time_building_blocks day
3938 ,hxc_time_building_blocks tc
3939 WHERE TRUNC(day.start_time) = p_day
3940 AND day.scope = 'DAY'
3941 AND day.approval_status = 'SUBMITTED'
3942 AND day.resource_id = p_resource_id
3943 AND day.date_to = hr_general.end_of_time
3944 AND day.parent_building_block_id = tc.time_building_block_id
3945 AND day.parent_building_block_ovn = tc.object_version_number
3946 AND tc.scope = 'TIMECARD'
3947 AND tc.date_to = hr_general.end_of_time;
3948
3949 l_submitted VARCHAR2(1);
3950 BEGIN
3951 OPEN c_submitted(p_day, p_resource_id);
3952 FETCH c_submitted INTO l_submitted;
3953
3954 IF c_submitted%NOTFOUND
3955 THEN
3956 CLOSE c_submitted;
3957 RETURN FALSE;
3958 END IF;
3959
3960 CLOSE c_submitted;
3961 RETURN TRUE;
3962 END is_submitted;
3963
3964 --
3965 -------------------------- chk_submitted_days ------------------------------
3966 --
3967 FUNCTION chk_submitted_days(p_period_start_date date,
3968 p_period_end_date date,
3969 p_resource_id number)
3970 RETURN VARCHAR2 IS
3971
3972
3973 l_assignment_periods hxc_timecard_utilities.periods;
3974 l_day DATE;
3975 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_submitted_days';
3976 --
3977 BEGIN
3978 --
3979 if g_debug then
3980 hr_utility.set_location(l_proc, 10);
3981 end if;
3982 --
3983 BEGIN
3984 --added by jxtan to fix mid period hiring
3985 -- Modified for Bug 12387815
3986 l_assignment_periods := hxc_timecard_utilities.get_assignment_periods(p_resource_id,p_period_start_date);
3987
3988 l_day := p_period_start_date;
3989 LOOP
3990 EXIT WHEN l_day > p_period_end_date;
3991 IF has_valid_assign(l_day, l_assignment_periods)
3992 THEN
3993 IF NOT is_submitted(l_day, p_resource_id)
3994 THEN
3995 RETURN 'N';
3996 END IF;
3997 END IF;
3998 l_day := l_day + 1;
3999 END LOOP;
4000
4001 RETURN 'Y';
4002 END;
4003
4004 END chk_submitted_days;
4005
4006 PROCEDURE process_appl_periods(itemtype IN varchar2,
4007 itemkey IN varchar2,
4008 actid IN number,
4009 funcmode IN varchar2,
4010 result IN OUT NOCOPY varchar2)
4011 IS
4012
4013 CURSOR csr_get_tc_info(
4014 p_bld_blk_id number,
4015 p_ovn number
4016 )
4017 IS
4018 select tc.resource_id, tc.start_time, tc.stop_time,tc.last_updated_by
4019 from hxc_time_building_blocks tc
4020 where tc.time_building_block_id = p_bld_blk_id
4021 and tc.object_version_number = p_ovn;
4022
4023
4024 CURSOR csr_get_appl_periods(
4025 p_resource_id in hxc_app_period_summary.resource_id%type
4026 ,p_timecard_id in hxc_timecard_summary.timecard_id%type
4027 )
4028 IS
4029 select aps.application_period_id,
4030 aps.start_time, -- period_start_date
4031 aps.stop_time, -- period_end_date
4032 aps.application_period_ovn,
4033 aps.time_recipient_id,
4034 aps.recipient_sequence,
4035 aps.time_category_id,
4036 aps.category_sequence,
4037 aps.approval_item_key
4038 from hxc_app_period_summary aps, hxc_tc_ap_links tcl
4039 where aps.resource_id = p_resource_id
4040 and aps.approval_status = 'SUBMITTED'
4041 and aps.notification_status = 'NOT_NOTIFIED'
4042 and aps.application_period_id = tcl.application_period_id
4043 and tcl.timecard_id = p_timecard_id;
4044
4045
4046 CURSOR c_period_notified(
4047 p_period_id number
4048 )
4049 IS
4050 select 'N'
4051 from hxc_app_period_summary
4052 where application_period_id = p_period_id
4053 and approval_status = 'SUBMITTED'
4054 and notification_status = 'NOT_NOTIFIED';
4055
4056 l_notified_status varchar2(1);
4057 l_tc_bld_blk_id number;
4058 l_tc_ovn number;
4059 l_tc_resubmitted varchar2(10);
4060 l_bb_new varchar2(10);
4061 --
4062 l_tc_url varchar2(1000);
4063 l_tc_resource_id number;
4064 l_tc_start_time date;
4065 l_tc_stop_time date;
4066 --
4067 l_application varchar2(80);
4068 l_application_id number;
4069 l_time_recipient_id number;
4070 l_time_recipient varchar2(150);
4071 l_time_recipient_seq number;
4072 --
4073
4074 l_approval_recipient number;
4075 l_approver_seq number;
4076 --
4077 l_day_bld_blk_id number;
4078 l_day_start_time date;
4079 l_day_stop_time date;
4080 l_day_ovn number;
4081 l_appl_period_bb_id number;
4082 l_appl_period_bb_ovn number;
4083 l_period_start_date date;
4084 l_period_end_date date;
4085 --
4086 l_cnt number;
4087 l_approval_style_id number;
4088 l_exists varchar2(1);
4089 l_chk_days varchar2(1);
4090 l_approved varchar2(1);
4091 l_not_notified varchar2(1);
4092 l_item_key wf_items.item_key%type;
4093 l_process_name varchar2(30);
4094 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.process_appl_periods';
4095 l_last_updated_by number;
4096
4097 l_recipient_sequence hxc_approval_comps.approval_order%TYPE;
4098 l_time_category_id hxc_time_categories.time_category_id%TYPE;
4099 l_category_sequence hxc_approval_comps.approval_order%TYPE;
4100 l_approval_item_key wf_items.item_key%type;
4101 l_is_blank varchar2(1) := NULL;
4102 l_process varchar2(1) := 'Y';
4103
4104 BEGIN
4105 l_approval_item_key := null;
4106 g_debug:=hr_utility.debug_enabled;
4107 if g_debug then
4108 hr_utility.set_location(l_proc, 10);
4109 end if;
4110 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
4111 (itemtype => itemtype,
4112 itemkey => itemkey,
4113 aname => 'TC_BLD_BLK_ID');
4114
4115 if g_debug then
4116 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
4117 end if;
4118 l_tc_ovn := wf_engine.GetItemAttrNumber
4119 (itemtype => itemtype,
4120 itemkey => itemkey,
4121 aname => 'TC_BLD_BLK_OVN');
4122
4123 if g_debug then
4124 hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
4125 end if;
4126 l_tc_resubmitted := wf_engine.GetItemAttrText
4127 (itemtype => itemtype,
4128 itemkey => itemkey,
4129 aname => 'TC_RESUBMITTED');
4130
4131 if g_debug then
4132 hr_utility.trace('Timecard Resubmitted is : ' || l_tc_resubmitted);
4133 end if;
4134 l_bb_new := wf_engine.GetItemAttrText
4135 (itemtype => itemtype,
4136 itemkey => itemkey,
4137 aname => 'BB_NEW');
4138
4139 if g_debug then
4140 hr_utility.trace('Building Block New is : ' || l_bb_new);
4141 hr_utility.set_location(l_proc, 20);
4142 end if;
4143
4144 open csr_get_tc_info(l_tc_bld_blk_id,
4145 l_tc_ovn);
4146 fetch csr_get_tc_info into l_tc_resource_id,
4147 l_tc_start_time,
4148 l_tc_stop_time,
4149 l_last_updated_by;
4150
4151 if g_debug then
4152 hr_utility.set_location(l_proc, 30);
4153 hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
4154 hr_utility.trace('Timecard Start Time is : ' ||
4155 to_char(l_tc_start_time, 'DD-MM-YYYY'));
4156 hr_utility.trace('Timecard End Time is : ' ||
4157 to_char(l_tc_stop_time, 'DD-MM-YYYY'));
4158 end if;
4159 IF csr_get_tc_info%NOTFOUND
4160 THEN
4161
4162 if g_debug then
4163 hr_utility.set_location(l_proc, 40);
4164 end if;
4165
4166 g_error_count := g_error_count + 1;
4167 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
4168 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
4169
4170 hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
4171 hr_utility.raise_error;
4172
4173 END IF;
4174
4175 if g_debug then
4176 hr_utility.set_location(l_proc, 50);
4177 end if;
4178
4179 close csr_get_tc_info;
4180
4181 l_process_name := 'HXC_APPLY_NOTIFY';
4182
4183 if g_debug then
4184 hr_utility.set_location(l_proc, 60);
4185 end if;
4186
4187 l_approval_style_id := get_approval_style_id(l_tc_start_time,
4188 l_tc_stop_time,
4189 l_tc_resource_id);
4190 --
4191 if g_debug then
4192 hr_utility.set_location(l_proc, 70);
4193 --
4194 hr_utility.trace('l_approval_style_id is : ' || to_char(l_approval_style_id));
4195 -- For all the application periods created for this timecard,
4196 -- create a workflow process to continue with the approval - to
4197 -- apply the approval rules and notify the approver(s).
4198
4199 hr_utility.set_location(l_proc, 110);
4200 end if;
4201
4202 open csr_get_appl_periods(l_tc_resource_id, l_tc_bld_blk_id);
4203
4204 LOOP
4205
4206 if g_debug then
4207 hr_utility.set_location(l_proc, 120);
4208 end if;
4209 fetch csr_get_appl_periods into l_appl_period_bb_id,
4210 l_period_start_date,
4211 l_period_end_date,
4212 l_appl_period_bb_ovn,
4213 l_time_recipient,
4214 l_recipient_sequence,
4215 l_time_category_id,
4216 l_category_sequence,
4217 l_approval_item_key;
4218
4219
4220 exit when csr_get_appl_periods%NOTFOUND;
4221
4222 if g_debug then
4223 hr_utility.trace('l_appl_period_bb_id is : ' ||
4224 to_char(l_appl_period_bb_id));
4225 hr_utility.trace('l_appl_period_bb_ovn is : ' ||
4226 to_char(l_appl_period_bb_ovn));
4227 hr_utility.trace('l_period_start_date is : ' ||
4228 to_char(l_period_start_date, 'DD-MM-YYYY'));
4229 hr_utility.trace('l_period_end_date is : ' ||
4230 to_char(l_period_end_date, 'DD-MM-YYYY'));
4231 hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4232 hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
4233
4234 hr_utility.set_location(l_proc, 150);
4235 end if;
4236 -- Check to see if all the days in the application period have
4237 -- submitted days.
4238 --
4239
4240 l_chk_days := chk_submitted_days(l_period_start_date,
4241 l_period_end_date,
4242 l_tc_resource_id);
4243
4244 IF l_tc_stop_time < l_period_end_date
4245 AND l_tc_resubmitted <> 'YES'
4246 THEN
4247 l_process := 'N';
4248 END IF;
4249
4250 if g_debug then
4251 hr_utility.trace('Checked days:'||l_chk_days);
4252 hr_utility.set_location(l_proc, 160);
4253 end if;
4254 IF l_chk_days = 'Y' AND l_process = 'Y' THEN
4255
4256 if g_debug then
4257 hr_utility.set_location(l_proc, 170);
4258 end if;
4259 --
4260 -- Check to see if all applications before this one in the approval
4261 -- style have approved all the days in this period.
4262 --
4263 l_approved := chk_app_approved
4264 (p_resource_id => l_tc_resource_id
4265 ,p_period_start_date => l_period_start_date
4266 ,p_period_end_date => l_period_end_date
4267 ,p_time_recipient_id => l_time_recipient
4268 ,p_recipient_sequence => l_recipient_sequence
4269 ,p_time_category_id => l_time_category_id
4270 ,p_category_sequence => l_category_sequence);
4271
4272 if g_debug then
4273 hr_utility.trace('All previous approved:'||l_approved);
4274 end if;
4275
4276 IF l_approved = 'Y' THEN
4277
4278 OPEN c_period_notified(l_appl_period_bb_id);
4279 FETCH c_period_notified INTO l_notified_status;
4280
4281 IF c_period_notified%NOTFOUND THEN
4282 CLOSE c_period_notified;
4283 if g_debug then
4284 hr_utility.trace('already processed ' || l_appl_period_bb_id);
4285 end if;
4286 ELSE
4287 CLOSE c_period_notified;
4288 if g_debug then
4289 hr_utility.set_location(l_proc, 210);
4290 hr_utility.trace('itemtype is : ' || itemtype);
4291 hr_utility.trace('l_process_name is : ' || l_process_name);
4292 end if;
4293 --
4294 -- Setup l_item_key from a sequence.
4295 --
4296 if l_approval_item_key is not null then
4297 hr_utility.trace('OTL:pass 200 - '||l_approval_item_key);
4298
4299 IF g_old_item_key.exists(l_approval_item_key) then
4300 hr_utility.trace('OTL: 200: exists in g table');
4301 l_is_blank := g_old_item_key(l_approval_item_key).is_diff_tc;
4302 ELSE
4303
4304 l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
4305 itemkey => l_approval_item_key ,
4306 aname => 'IS_DIFF_TC',
4307 ignore_notfound => true);
4308 END IF;
4309 hr_utility.trace('OTL:pass 210');
4310 else
4311 l_is_blank := null;
4312 end if;
4313
4314 SELECT hxc_approval_item_key_s.nextval
4315 INTO l_item_key
4316 FROM dual;
4317
4318 update hxc_app_period_summary
4319 set notification_status = 'NOTIFIED',
4320 approval_item_type = itemtype,
4321 approval_process_name = l_process_name,
4322 approval_item_key = l_item_key
4323 where application_period_id = l_appl_period_bb_id
4324 and application_period_ovn = l_appl_period_bb_ovn;
4325
4326 if g_debug then
4327 hr_utility.trace('l_item_key is : ' || l_item_key);
4328 end if;
4329
4330 wf_engine.CreateProcess(itemtype => itemtype,
4331 itemkey => l_item_key,
4332 process => l_process_name);
4333 wf_engine.setitemowner(itemtype,
4334 l_item_key,
4335 HXC_FIND_NOTIFY_APRS_PKG.get_login(p_person_id=>l_tc_resource_id,
4336 p_user_id => l_last_updated_by)
4337 );
4338 if g_debug then
4339 hr_utility.set_location(l_proc, 260);
4340 end if;
4341 if(item_attribute_exists(itemtype,l_item_key,'IS_DIFF_TC')) then
4342
4343 hr_utility.trace('SetItemAttrText of IS_DIFF_TC to '||l_is_blank||' - 05');
4344
4345 wf_engine.SetItemAttrText(
4346 itemtype => itemtype,
4347 itemkey => l_item_key,
4348 aname => 'IS_DIFF_TC',
4349 avalue => l_is_blank);
4350 else
4351
4352 hr_utility.trace('SetItemAttrText of IS_DIFF_TC to '||l_is_blank||' - 06');
4353
4354 wf_engine.additemattr
4355 (itemtype => itemtype,
4356 itemkey => l_item_key,
4357 aname => 'IS_DIFF_TC',
4358 text_value => l_is_blank);
4359 end if;
4360
4361 wf_engine.SetItemAttrDate(itemtype => itemtype,
4362 itemkey => l_item_key,
4363 aname => 'APP_START_DATE',
4364 avalue => l_period_start_date);
4365
4366 wf_engine.SetItemAttrText(itemtype => itemtype,
4367 itemkey => l_item_key,
4368 aname => 'FORMATTED_APP_START_DATE',
4369 avalue => to_char(l_period_start_date,'YYYY/MM/DD'));
4370 if g_debug then
4371 hr_utility.set_location(l_proc, 270);
4372 hr_utility.trace('APP_START_DATE is : ' ||
4373 to_char(l_period_start_date, 'DD-MM-YYYY'));
4374 end if;
4375
4376 wf_engine.SetItemAttrDate(itemtype => itemtype,
4377 itemkey => l_item_key,
4378 aname => 'APP_END_DATE',
4379 avalue => l_period_end_date);
4380
4381 if g_debug then
4382 hr_utility.set_location(l_proc, 280);
4383 hr_utility.trace('APP_END_DATE is : ' ||
4384 to_char(l_period_end_date, 'DD-MM-YYYY'));
4385 end if;
4386
4387 wf_engine.SetItemAttrNumber(itemtype => itemtype,
4388 itemkey => l_item_key,
4389 aname => 'APP_BB_ID',
4390 avalue => l_appl_period_bb_id);
4391
4392 if g_debug then
4393 hr_utility.set_location(l_proc, 290);
4394 hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
4395 end if;
4396
4397 wf_engine.SetItemAttrNumber(itemtype => itemtype,
4398 itemkey => l_item_key,
4399 aname => 'APP_BB_OVN',
4400 avalue => l_appl_period_bb_ovn);
4401
4402 if g_debug then
4403 hr_utility.set_location(l_proc, 300);
4404 hr_utility.trace('APP_BB_OVN is : ' ||
4405 to_char(l_appl_period_bb_ovn));
4406 end if;
4407
4408 wf_engine.SetItemAttrNumber(itemtype => itemtype,
4409 itemkey => l_item_key,
4410 aname => 'RESOURCE_ID',
4411 avalue => l_tc_resource_id);
4412
4413 if g_debug then
4414 hr_utility.set_location(l_proc, 310);
4415 hr_utility.trace('RESOURCE_ID is : ' || to_char(l_tc_resource_id));
4416 end if;
4417
4418 wf_engine.SetItemAttrText(itemtype => itemtype,
4419 itemkey => l_item_key,
4420 aname => 'TIME_RECIPIENT_ID',
4421 avalue => l_time_recipient);
4422
4423 if g_debug then
4424 hr_utility.set_location(l_proc, 320);
4425 hr_utility.trace('TIME_RECIPIENT_ID is : ' || l_time_recipient);
4426 end if;
4427
4428 wf_engine.SetItemAttrText(itemtype => itemtype,
4429 itemkey => l_item_key,
4430 aname => 'TC_RESUBMITTED',
4431 avalue => l_tc_resubmitted);
4432
4433 if g_debug then
4434 hr_utility.set_location(l_proc, 330);
4435 hr_utility.trace('TC_RESUBMITTED is : ' || l_tc_resubmitted);
4436 end if;
4437
4438 wf_engine.SetItemAttrText(itemtype => itemtype,
4439 itemkey => l_item_key,
4440 aname => 'BB_NEW',
4441 avalue => l_bb_new);
4442
4443 if g_debug then
4444 hr_utility.set_location(l_proc, 335);
4445 hr_utility.trace('BB_NEW is : ' || l_bb_new);
4446 end if;
4447
4448 wf_engine.SetItemAttrNumber(itemtype => itemtype,
4449 itemkey => l_item_key,
4450 aname => 'TC_BLD_BLK_ID',
4451 avalue => l_tc_bld_blk_id);
4452
4453 if g_debug then
4454 hr_utility.set_location(l_proc, 340);
4455 hr_utility.trace('TC_BLD_BLK_ID is : ' || to_char(l_tc_bld_blk_id));
4456 end if;
4457
4458 wf_engine.SetItemAttrNumber(itemtype => itemtype,
4459 itemkey => l_item_key,
4460 aname => 'TC_BLD_BLK_OVN',
4461 avalue => l_tc_ovn);
4462
4463 if g_debug then
4464 hr_utility.set_location(l_proc, 350);
4465 hr_utility.trace('TC_BLD_BLK_OVN is : ' || to_char(l_tc_ovn));
4466 end if;
4467
4468 wf_engine.SetItemAttrNumber(itemtype => itemtype,
4469 itemkey => l_item_key,
4470 aname => 'APPROVAL_STYLE_ID',
4471 avalue => l_approval_style_id);
4472
4473 l_tc_url :='JSP:OA_HTML/OA.jsp?akRegionCode=HXCAPRVPAGE&akRegionApplicationId=' ||
4474 '809&retainAM=Y&Action=Details&AprvTimecardId=' || l_appl_period_bb_id ||
4475 '&AprvTimecardOvn=' || l_appl_period_bb_ovn ||
4476 '&AprvStartTime=' || to_char(l_period_start_date,'YYYY/MM/DD')||
4477 '&AprvStopTime=' || to_char(l_period_end_date,'YYYY/MM/DD') ||
4478 '&AprvResourceId=' || to_char(l_tc_resource_id) ||
4479 '&OAFunc=HXC_TIME_ENTER'||
4480 '&NtfId=-&#NID-';
4481
4482
4483 wf_engine.SetItemAttrText(itemtype => itemtype,
4484 itemkey => l_item_key,
4485 aname => 'HXC_TIMECARD_URL',
4486 avalue => l_tc_url);
4487
4488 --
4489 -- For bug 4291206, copy the previous approvers
4490 -- in the new process
4491 -- 115.92 Change.
4492 --
4493 hxc_approval_wf_util.copy_previous_approvers
4494 (p_item_type => itemtype,
4495 p_current_key => itemkey,
4496 p_copyto_key => l_item_key);
4497
4498 -- Update attribute4 with NOTIFIED and attribute2 with the Item Key.
4499
4500 if g_debug then
4501 hr_utility.trace('APP_BB_OVN is : ' ||
4502 to_char(l_appl_period_bb_ovn));
4503 hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
4504 hr_utility.trace('Before Update');
4505 hr_utility.set_location(l_proc, 360);
4506 end if;
4507
4508 update hxc_app_period_summary
4509 set notification_status = 'NOTIFIED'
4510 where application_period_id = l_appl_period_bb_id
4511 and application_period_ovn = l_appl_period_bb_ovn;
4512
4513
4514 wf_engine.StartProcess(itemtype => itemtype,
4515 itemkey => l_item_key);
4516
4517 if g_debug then
4518 hr_utility.set_location(l_proc, 365);
4519 end if;
4520 END IF; -- if not notified;
4521 END IF; -- approved
4522
4523 END IF; -- l_chk_days
4524
4525 if g_debug then
4526 hr_utility.set_location(l_proc, 380);
4527 end if;
4528
4529 END LOOP;
4530
4531 if g_debug then
4532 hr_utility.trace('OUTSIDE END LOOP');
4533 end if;
4534
4535 close csr_get_appl_periods;
4536
4537 result := '';
4538 return;
4539 exception
4540 when others then
4541 -- The line below records this function call in the error system
4542 -- in the case of an exception.
4543 --
4544 if g_debug then
4545 hr_utility.set_location(l_proc, 999);
4546 --
4547 hr_utility.trace('IN EXCEPTION IN process_appl_periods');
4548 end if;
4549 --
4550 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.process_appl_periods',
4551 itemtype, itemkey, to_char(actid), funcmode);
4552 raise;
4553 result := '';
4554 return;
4555
4556 END process_appl_periods;
4557
4558
4559
4560 -----------------------------------------new procedures
4561
4562 /*
4563 FUNCTION get_approval_style(
4564 p_app_id IN hxc_time_building_blocks.time_building_block_id
4565 ,p_app_ovn IN hxc_time_building_blocks.object_version_number
4566 ) RETURN hxc_approval_styles.approval_style_id%TYPE
4567 IS
4568 l_approval_style hxc_approval_styles.approval_style_id%TYPE;
4569
4570 CURSOR c_approval_style(
4571 p_app_id IN hxc_time_building_blocks.time_building_block_id
4572 )
4573 IS
4574 SELECT tc.approval_style_id
4575 FROM hxc_timecard_summary tc
4576 ,hxc_timecard_application_summary ta
4577 WHERE ta.application_period_id = p_app_id
4578 AND tc.time_building_block_id = ta.time_building_block_id
4579
4580 BEGIN
4581 OPEN c_approval_style(p_app_id);
4582 FETCH c_approval_style INTO l_appproval_style;
4583
4584 IF c_approval_style%NOTFOUND
4585 THEN
4586 CLOSE c_approval_style;
4587
4588 RETURN NULL;
4589 END IF;
4590
4591 RETURN l_approval_style;
4592 END get_approval_style;
4593 */
4594
4595
4596 FUNCTION get_approval_style(
4597 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
4598 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
4599 ) RETURN hxc_approval_styles.approval_style_id%TYPE
4600 IS
4601 l_approval_style_id hxc_approval_styles.approval_style_id%TYPE := NULL;
4602 /*
4603 CURSOR c_approval_style(
4604 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
4605 )
4606 IS
4607 select approval_style_id
4608 from hxc_timecard_summary
4609 where timecard_id = p_timecard_id;
4610 */
4611 BEGIN
4612 /*
4613 OPEN c_approval_style(p_timecard_id);
4614
4615 FETCH c_approval_style INTO l_approval_style_id;
4616 IF c_approval_style%NOTFOUND
4617 THEN
4618 CLOSE c_approval_style;
4619 END IF;
4620 */
4621 RETURN l_approval_style_id;
4622 END get_approval_style;
4623
4624
4625 -- Procedure
4626 -- start_approval_wf_process
4627 --
4628 -- Description
4629 -- Start the Approval workflow process for the given timecard.
4630 -- This overloaded version added by A.Rundell, version 115.49, for
4631 -- the second generation deposit wrapper.
4632 --
4633 PROCEDURE start_approval_wf_process
4634 (p_item_type IN varchar2
4635 ,p_item_key IN varchar2
4636 ,p_process_name IN varchar2
4637 ,p_tc_bb_id IN number
4638 ,p_tc_ovn IN number
4639 ,p_tc_resubmitted IN varchar2
4640 ,p_bb_new IN varchar2
4641 )is
4642
4643 l_proc varchar2(70) := 'HXC_APPROVAL_WF_PKG.start_approval_wf_process';
4644 l_defer FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%type;
4645
4646 Begin
4647 --
4648 -- Fetch the defer option
4649 --
4650
4651 l_defer := fnd_profile.value('HXC_DEFER_WORKFLOW');
4652
4653 if(l_defer is null) then
4654 l_defer := 'Y';
4655 end if;
4656
4657 --
4658 -- Initialization
4659 --
4660 g_error_table.delete;
4661 g_error_count := 0;
4662 if(l_defer='Y') then
4663 wf_engine.threshold := -1; -- Ensures a deferred process
4664 else
4665 wf_engine.threshold := 100;
4666 end if;
4667
4668 wf_engine.createProcess
4669 (itemtype => p_item_type
4670 ,itemkey => p_item_key
4671 ,process => p_process_name
4672 );
4673
4674 wf_engine.SetItemAttrNumber
4675 (itemtype => p_item_type
4676 ,itemkey => p_item_key
4677 ,aname => 'TC_BLD_BLK_ID'
4678 ,avalue => p_tc_bb_id
4679 );
4680
4681 wf_engine.SetItemAttrNumber
4682 (itemtype => p_item_type
4683 ,itemkey => p_item_key
4684 ,aname => 'TC_BLD_BLK_OVN'
4685 ,avalue => p_tc_ovn
4686 );
4687
4688 wf_engine.SetItemAttrText
4689 (itemtype => p_item_type
4690 ,itemkey => p_item_key
4691 ,aname => 'TC_RESUBMITTED'
4692 ,avalue => p_tc_resubmitted
4693 );
4694
4695 wf_engine.SetItemAttrText
4696 (itemtype => p_item_type
4697 ,itemkey => p_item_key
4698 ,aname => 'BB_NEW'
4699 ,avalue => p_bb_new
4700 );
4701
4702 wf_engine.StartProcess
4703 (itemtype => p_item_type
4704 ,itemkey => p_item_key
4705 );
4706
4707 wf_engine.threshold := 50;
4708
4709 END start_approval_wf_process;
4710
4711
4712 --
4713 --
4714 -- Procedure
4715 -- start_approval_wf_process
4716 --
4717 -- Description
4718 -- Start the Approval workflow process for the given timecard
4719 --
4720 PROCEDURE start_approval_wf_process
4721 (p_item_type IN varchar2
4722 ,p_item_key IN varchar2
4723 ,p_tc_bb_id IN number
4724 ,p_tc_ovn IN number
4725 ,p_tc_resubmitted IN varchar2
4726 ,p_error_table OUT NOCOPY hxc_self_service_time_deposit.message_table
4727 ,p_time_building_blocks IN hxc_self_service_time_deposit.timecard_info
4728 ,p_time_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
4729 ,p_bb_new IN varchar2)
4730 is
4731 --
4732 --
4733 -- l_item_key wf_items.item_key%type;
4734 -- l_process_name varchar2(30);
4735 --
4736 l_process_name wf_process_activities.process_name%type;
4737 --
4738 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.start_approval_wf_process';
4739 --
4740 BEGIN
4741 g_debug:=hr_utility.debug_enabled;
4742 --
4743 if g_debug then
4744 hr_utility.set_location(l_proc, 10);
4745 --
4746 hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4747 hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4748 hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4749 --
4750 end if;
4751 -- Nulls out the error table.
4752 --
4753 g_error_table.delete;
4754 g_error_count := 0;
4755 --
4756 -- Sets up global variables for timecard records.
4757 --
4758 g_time_building_blocks := p_time_building_blocks;
4759 g_time_attributes := p_time_attributes;
4760 --
4761 -- Creates a new runtime process for the WF item type passed.
4762 -- p_process_name is HXC_APPROVAL.
4763 --
4764 l_process_name := 'HXC_APPROVAL';
4765 --
4766 wf_engine.threshold := -1;
4767 --
4768 wf_engine.createProcess(itemtype => p_item_type,
4769 itemkey => p_item_key,
4770 process => l_process_name);
4771 --
4772 if g_debug then
4773 hr_utility.set_location(l_proc, 20);
4774 end if;
4775 --
4776 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
4777 itemkey => p_item_key,
4778 aname => 'TC_BLD_BLK_ID',
4779 avalue => p_tc_bb_id);
4780 --
4781 if g_debug then
4782 hr_utility.set_location(l_proc, 30);
4783 end if;
4784 --
4785 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
4786 itemkey => p_item_key,
4787 aname => 'TC_BLD_BLK_OVN',
4788 avalue => p_tc_ovn);
4789 --
4790 if g_debug then
4791 hr_utility.set_location(l_proc, 40);
4792 end if;
4793 --
4794 wf_engine.SetItemAttrText(itemtype => p_item_type,
4795 itemkey => p_item_key,
4796 aname => 'TC_RESUBMITTED',
4797 avalue => p_tc_resubmitted);
4798 --
4799 if g_debug then
4800 hr_utility.set_location(l_proc, 50);
4801 end if;
4802 --
4803 IF p_bb_new = 'Y' THEN
4804 wf_engine.SetItemAttrText(itemtype => p_item_type,
4805 itemkey => p_item_key,
4806 aname => 'BB_NEW',
4807 avalue => 'YES');
4808 ELSE
4809 wf_engine.SetItemAttrText(itemtype => p_item_type,
4810 itemkey => p_item_key,
4811 aname => 'BB_NEW',
4812 avalue => 'NO');
4813 END IF;
4814 --
4815 if g_debug then
4816 hr_utility.set_location(l_proc, 60);
4817 end if;
4818 --
4819 wf_engine.StartProcess(itemtype => p_item_type,
4820 itemkey => p_item_key);
4821 --
4822 wf_engine.threshold := 50;
4823 --
4824 if g_debug then
4825 hr_utility.set_location(l_proc, 70);
4826 end if;
4827 --
4828 p_error_table := g_error_table;
4829 --
4830
4831 --
4832 END start_approval_wf_process;
4833
4834
4835 --
4836 ---------------------------- upd_apr_details ------------------------------
4837 --
4838 PROCEDURE upd_apr_details(p_app_bb_id IN number,
4839 p_app_bb_ovn IN number,
4840 p_approver_id IN number,
4841 p_approved_time IN date,
4842 p_approval_comment IN varchar2,
4843 p_approval_status IN varchar2,
4844 p_delegated_for IN varchar2) is
4845
4846 --
4847 /*
4848 cursor csr_get_attributes(p_app_bb_id in number,
4849 p_app_bb_ovn in number) is
4850 select attribute1, attribute2,
4851 attribute8, attribute9
4852 from hxc_time_attributes
4853 where time_attribute_id = (select min(time_attribute_id)
4854 from hxc_time_attribute_usages
4855 where time_building_block_id = p_app_bb_id
4856 and time_building_block_ovn = p_app_bb_ovn);
4857 */
4858 --
4859 t_attributes hxc_time_attributes_api.timecard;
4860 l_appl_period_bb_id number := p_app_bb_id;
4861 l_appl_period_bb_ovn number := p_app_bb_ovn;
4862 l_approver_id number := p_approver_id;
4863 -- l_time_recipient varchar2(150);
4864 -- l_item_key varchar2(150);
4865 l_notified_status varchar2(150) := 'FINISHED';
4866 l_approved_time varchar2(150)
4867 := fnd_date.date_to_canonical(p_approved_time);
4868 -- := to_char(p_approved_time, 'DD-MM-YYYY HH:MM:SS');
4869 l_approval_comment varchar2(150) := p_approval_comment;
4870 l_approved_status varchar2(150) := p_approval_status;
4871 l_delegated_for varchar2(150) := p_delegated_for;
4872 -- l_approver_sequence varchar2(150);
4873 --
4874 l_time_attribute_id number;
4875 l_ovn number;
4876 --
4877 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.upd_apr_details';
4878 --
4879 BEGIN
4880 g_debug:=hr_utility.debug_enabled;
4881 --
4882 if g_debug then
4883 hr_utility.set_location(l_proc, 10);
4884 --
4885 hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4886 hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4887 hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4888 hr_utility.trace('l_approved_time is : ' || l_approved_time);
4889 hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4890 hr_utility.trace('l_approved_status is : ' || l_approved_status);
4891 --
4892 hr_utility.set_location(l_proc, 20);
4893 end if;
4894 -- Perf Rep Fix - SQL ID :3170802
4895 -- Added attribute_category = APPROVAL to the where clause.
4896
4897 update hxc_time_attributes
4898 set attribute4 = l_notified_status,
4899 attribute5 = l_approved_time,
4900 attribute6 = l_approval_comment,
4901 attribute7 = l_approved_status,
4902 attribute8 = l_delegated_for
4903 where time_attribute_id in (select time_attribute_id
4904 from hxc_time_attribute_usages
4905 where time_building_block_id = p_app_bb_id
4906 and time_building_block_ovn = p_app_bb_ovn)
4907 and attribute3 = to_char(l_approver_id)
4908 and attribute_category = 'APPROVAL';
4909 --
4910 if g_debug then
4911 hr_utility.set_location(l_proc, 30);
4912 end if;
4913 --
4914 END upd_apr_details;
4915 --
4916 --
4917 -- Don't need these procedures for now. Not using them but leaving them
4918 -- here in case they are needed in future.
4919 --
4920
4921 --
4922 FUNCTION code_chk (p_code IN VARCHAR2) RETURN BOOLEAN IS
4923 --
4924 l_package_name user_source.name%TYPE;
4925 -- Bug 15963472
4926 l_proc_name user_procedures.procedure_name%TYPE;
4927 l_dummy VARCHAR2(1);
4928 l_code BOOLEAN := FALSE;
4929 --
4930 BEGIN
4931 --
4932 l_package_name := SUBSTR(p_code,1,INSTR(p_code,'.')-1);
4933 -- Bug 15963472
4934 -- Cut off the package name from the string to get the procedure name
4935 l_proc_name := REPLACE(p_code,l_package_name||'.');
4936 --
4937 BEGIN
4938
4939 -- Bug 15963472
4940 -- Commented this below query on sys.obj$ because this table has a problem in NZD mode.
4941 -- The issue is that multiple entries for the object exists because of the multiple editions.
4942 -- These tables were used because they are the base tables in DBA_OBJECTS view, and it was unnecessary
4943 -- to refer to the view as it is.
4944 -- However, in 11g, with edition based redefinition available, even DBA_OBJECTS accesses a new table,
4945 -- SYS."_CURRENT_EDITION_OBJ".
4946 -- But we are going to the view instead of the SYS tables because of two reasons.
4947
4948 -- 1. This is a dual maintained code, and we need the same code from 12.1 onwards. 12.1 is still allowed to
4949 -- to work in 10g, where this new SYS table is absent.
4950 -- 2. Even if we break dual checkin, the code wouldnt take the notation used. Even if it does, we are not
4951 -- sure if tomorrow we might need to change the whole query once again when the SYS table undergoes another feature change.
4952 --
4953 -- There sure is a perf trade off, but we chose the best view available. If there is a perf problem somehow in this
4954 -- functionality, we know where to look for and what to change.
4955
4956 /*
4957 SELECT 'Y' into l_dummy
4958 FROM SYS.OBJ$ O, SYS.SOURCE$ S
4959 WHERE O.OBJ# = S.OBJ#
4960 AND O.TYPE# = 11 --PACKAGE BODY
4961 AND O.OWNER# = USERENV('SCHEMAID')
4962 AND O.NAME = l_package_name
4963 AND S.LINE = 1;
4964
4965 */
4966
4967 SELECT 'Y'
4968 INTO l_dummy
4969 FROM user_procedures
4970 WHERE object_name = l_package_name
4971 AND object_type = 'PACKAGE'
4972 AND procedure_name = l_proc_name;
4973
4974
4975 --
4976 l_code := TRUE;
4977 --
4978 EXCEPTION
4979 WHEN OTHERS THEN
4980 l_code := FALSE;
4981 END;
4982 --
4983 RETURN l_code;
4984 --
4985 END code_chk;
4986 ------------------------ is_appr_required ----------------------------
4987 --
4988 PROCEDURE is_appr_required(itemtype IN varchar2,
4989 itemkey IN varchar2,
4990 actid IN number,
4991 funcmode IN varchar2,
4992 result IN OUT NOCOPY varchar2) is
4993 --
4994 cursor csr_get_extension(p_time_recipient number) is
4995 select htr.extension_function1
4996 from hxc_time_recipients htr
4997 where htr.time_recipient_id = p_time_recipient;
4998
4999 cursor c_appr_comp(p_app_bb_id number,p_app_bb_ovn number)
5000 is
5001 select approval_comp_id
5002 from hxc_app_period_summary
5003 where application_period_id = p_app_bb_id
5004 and application_period_ovn = p_app_bb_ovn;
5005
5006 cursor c_app_comp_pm(p_bb_id number,p_bb_ovn number)
5007 is
5008 select hac.approval_comp_id
5009 from hxc_approval_comps hac,
5010 hxc_approval_styles has,
5011 hxc_time_building_blocks htb
5012 where htb.time_building_block_id =p_bb_id
5013 and htb.object_version_number = p_bb_ovn
5014 and htb.approval_style_id = has.approval_style_id
5015 and has.approval_style_id = hac.APPROVAL_STYLE_ID
5016 and hac.approval_mechanism = 'PROJECT_MANAGER'
5017 and hac.parent_comp_id is null
5018 and hac.parent_comp_ovn is null;
5019
5020 --
5021 l_tc_bld_blk_id number;
5022 l_tc_ovn number;
5023 l_time_recipient varchar2(150);
5024 l_ext_func1 varchar2(2000);
5025 l_auto_approval_flag varchar2(1);
5026 l_message varchar2(2000);
5027 l_message_table hxc_self_service_time_deposit.message_table;
5028 l_func_sql varchar2(2000);
5029 l_app_bld_blk_id number;
5030 l_app_ovn number;
5031 l_token_table hxc_deposit_wrapper_utilities.t_simple_table;
5032 l_exception varchar2(10000);
5033 l_approval_component_id number;
5034 --
5035 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.is_appr_required';
5036
5037 --
5038 BEGIN
5039 g_debug:=hr_utility.debug_enabled;
5040
5041 --
5042 if g_debug then
5043 hr_utility.set_location(l_proc, 10);
5044 end if;
5045 --
5046 l_app_bld_blk_id := wf_engine.GetItemAttrNumber
5047 (itemtype => itemtype,
5048 itemkey => itemkey ,
5049 aname => 'APP_BB_ID');
5050 --
5051 if g_debug then
5052 hr_utility.set_location(l_proc, 20);
5053 end if;
5054 --
5055 l_app_ovn := wf_engine.GetItemAttrNumber
5056 (itemtype => itemtype,
5057 itemkey => itemkey ,
5058 aname => 'APP_BB_OVN');
5059 --
5060 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
5061 (itemtype => itemtype,
5062 itemkey => itemkey,
5063 aname => 'TC_BLD_BLK_ID');
5064 --
5065 if g_debug then
5066 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
5067 end if;
5068 --
5069 l_tc_ovn := wf_engine.GetItemAttrNumber
5070 (itemtype => itemtype,
5071 itemkey => itemkey,
5072 aname => 'TC_BLD_BLK_OVN');
5073 --
5074 l_time_recipient := wf_engine.GetItemAttrText(
5075 itemtype => itemtype,
5076 itemkey => itemkey ,
5077 aname => 'TIME_RECIPIENT_ID');
5078
5079 if g_debug then
5080 hr_utility.set_location(l_proc, 30);
5081 end if;
5082 --
5083 -- Sets up global variables for timecard records.
5084 --
5085 hxc_self_service_time_deposit.get_timecard_tables(
5086 p_timecard_id => l_tc_bld_blk_id
5087 ,p_timecard_ovn => l_tc_ovn
5088 ,p_timecard_blocks => g_time_building_blocks
5089 ,p_timecard_app_attributes => g_time_app_attributes
5090 ,p_time_recipient_id => to_number(l_time_recipient));
5091
5092 if g_debug then
5093 hr_utility.set_location(l_proc, 40);
5094 end if;
5095 --
5096 -- Get the package.procedure from the extension_function1 column
5097 -- on hxc_time_recipients.
5098 --
5099 -- package.procedure(
5100 -- p_timecard_building_blocks => g_time_building_blocks,
5101 -- p_time_attributes => g_time_attributes,
5102 -- x_autoapproval_flag => l_auto_approval_flag,
5103 -- x_messages => l_message);
5104 --
5105 -- 115.96 change.
5106 -- Uncommenting this code that was commented out in version 115.91
5107
5108 --added to support OIT desuport
5109
5110 open c_appr_comp(l_app_bld_blk_id,l_app_ovn);
5111 fetch c_appr_comp into l_approval_component_id;
5112 close c_appr_comp;
5113
5114 --In the case of PM mechanism, the approval comp id on the app period summary table will be
5115 --different to the app comp id on approval style, hence we need to fetch it from using timcard id
5116 --for non PM cases the below cursor will not be found hence it retains the app com id found in the above cursor.
5117 --If it is PM case then the original app comp id is fetched.
5118 open c_app_comp_pm(l_tc_bld_blk_id,l_tc_ovn);
5119 fetch c_app_comp_pm into l_approval_component_id;
5120 close c_app_comp_pm;
5121
5122 if(hxc_notification_helper.run_extensions(l_approval_component_id)) then
5123 open csr_get_extension(to_number(l_time_recipient));
5124 fetch csr_get_extension into l_ext_func1;
5125 close csr_get_extension;
5126 --
5127 IF l_ext_func1 IS NOT NULL THEN
5128
5129 if g_debug then
5130 hr_utility.set_location(l_proc, 50);
5131 end if;
5132
5133 IF code_chk(l_ext_func1) THEN
5134
5135 if g_debug then
5136 hr_utility.set_location(l_proc, 60);
5137 end if;
5138 --
5139 l_func_sql := 'BEGIN '||fnd_global.newline
5140 ||l_ext_func1 ||fnd_global.newline
5141 ||'(x_autoapproval_flag => :1' ||fnd_global.newline
5142 ||',x_messages => :2);' ||fnd_global.newline
5143 ||'END;';
5144 --
5145 EXECUTE IMMEDIATE l_func_sql
5146 using IN OUT l_auto_approval_flag,
5147 IN OUT l_message;
5148 --
5149 END IF;
5150 END IF;
5151
5152 ELSE
5153 l_auto_approval_flag := 'N';
5154 END IF;
5155 if g_debug then
5156 hr_utility.set_location(l_proc, 70);
5157 end if;
5158 --
5159 IF l_auto_approval_flag = 'Y' THEN
5160
5161 if g_debug then
5162 hr_utility.set_location(l_proc, 80);
5163 end if;
5164 --
5165 wf_engine.SetItemAttrText(itemtype => itemtype,
5166 itemkey => itemkey,
5167 aname => 'APPR_REQ',
5168 avalue => 'NO');
5169 --
5170 END IF;
5171
5172 if g_debug then
5173 hr_utility.set_location(l_proc, 90);
5174 end if;
5175 --
5176 l_exception := NULL;
5177 --
5178 IF l_message IS NOT NULL THEN
5179
5180 if g_debug then
5181 hr_utility.set_location(l_proc, 100);
5182 end if;
5183 --
5184 l_message_table := hxc_deposit_wrapper_utilities.string_to_messages
5185 (p_message_string => l_message);
5186 --
5187 IF l_message_table.COUNT <> 0 THEN
5188 --
5189 FOR i in l_message_table.first .. l_message_table.last LOOP
5190 --
5191 FND_MESSAGE.SET_NAME
5192 (l_message_table(i).application_short_name
5193 ,l_message_table(i).message_name
5194 );
5195 --
5196 IF l_message_table(i).message_tokens IS NOT NULL THEN
5197 --
5198 -- parse string into a more accessible form
5199 --
5200 hxc_deposit_wrapper_utilities.string_to_table('&',
5201 '&'||l_message_table(i).message_tokens,
5202 l_token_table);
5203 --
5204 FOR l_token in 0..(l_token_table.count/2)-1 LOOP
5205 --
5206 FND_MESSAGE.SET_TOKEN
5207 (TOKEN => l_token_table(2*l_token)
5208 ,VALUE => l_token_table(2*l_token+1)
5209 );
5210 --
5211 END LOOP;
5212 --
5213 END IF;
5214 --
5215 l_exception := SUBSTR((l_exception||fnd_message.get),1,10000);
5216 --
5217 END LOOP;
5218 --
5219 END IF;
5220
5221 if g_debug then
5222 hr_utility.set_location(l_proc, 110);
5223 end if;
5224 --jxtan in new implementation, comment is save in hxc_time_building_blocks
5225 UPDATE hxc_time_building_blocks
5226 SET comment_text = substr(l_exception, 1, 2000)
5227 WHERE time_building_block_id = l_app_bld_blk_id
5228 AND object_version_number = l_app_ovn;
5229
5230 if g_debug then
5231 hr_utility.set_location(l_proc, 120);
5232 end if;
5233 --
5234 wf_engine.SetItemAttrText(itemtype => itemtype,
5235 itemkey => itemkey,
5236 aname => 'EXT_MESSAGE',
5237 avalue => l_exception);
5238 --
5239 END IF;
5240
5241 if g_debug then
5242 hr_utility.set_location(l_proc, 130);
5243 end if;
5244 --
5245 IF upper(wf_engine.GetItemAttrText(itemtype => itemtype,
5246 itemkey => itemkey ,
5247 aname => 'APPR_REQ')) = 'YES' THEN
5248 --
5249 result := 'COMPLETE:Y';
5250 --
5251 if g_debug then
5252 hr_utility.set_location(l_proc, 140);
5253 --
5254 hr_utility.trace('APPR_REQ attribute is : YES');
5255 --
5256 end if;
5257 return;
5258 --
5259 ELSE
5260 --
5261 if g_debug then
5262 hr_utility.set_location(l_proc, 150);
5263 --
5264 hr_utility.trace('APPR_REQ attribute is : NO');
5265 --
5266 end if;
5267 wf_engine.SetItemAttrText(itemtype => itemtype,
5268 itemkey => itemkey,
5269 aname => 'APPROVAL_STATUS',
5270 avalue => 'APPROVED');
5271 --
5272 wf_engine.SetItemAttrText(itemtype => itemtype,
5273 itemkey => itemkey,
5274 aname => 'APR_REJ_REASON',
5275 avalue => 'AUTO_APPROVE');
5276
5277 if g_debug then
5278 hr_utility.set_location(l_proc, 160);
5279 --
5280 --
5281 hr_utility.trace('APPROVAL_STATUS attribute is : APPROVED');
5282 --
5283 end if;
5284 --OIT Enhancement.
5285 --FYI Notification to WORKER on timecard AUTO APPROVE
5286 HXC_APPROVAL_WF_HELPER.set_notif_attribute_values
5287 (itemtype,
5288 itemkey,
5289 hxc_app_comp_notifications_api.c_action_auto_approve,
5290 hxc_app_comp_notifications_api.c_recipient_worker
5291 );
5292 result := 'COMPLETE:N';
5293 return;
5294 --
5295 END IF;
5296 --
5297 if g_debug then
5298 hr_utility.set_location(l_proc, 170);
5299 end if;
5300 --
5301 exception
5302 when others then
5303 -- The line below records this function call in the error system
5304 -- in the case of an exception.
5305 --
5306 if g_debug then
5307 hr_utility.set_location(l_proc, 999);
5308 --
5309 hr_utility.trace('IN EXCEPTION IN is_appr_required');
5310 --
5311 end if;
5312 wf_core.context('HCAPPRWF',
5313 'hxc_approval_wf_pkg.is_appr_required',
5314 itemtype, itemkey, to_char(actid), funcmode);
5315 raise;
5316 result := '';
5317 return;
5318 --
5319 --
5320 END is_appr_required;
5321 --
5322 --
5323 ------------------------ chk_appr_rules ----------------------------
5324 --
5325 PROCEDURE chk_appr_rules(itemtype IN varchar2,
5326 itemkey IN varchar2,
5327 actid IN number,
5328 funcmode IN varchar2,
5329 result IN OUT NOCOPY varchar2) is
5330 --
5331 cursor csr_get_tc_info(p_app_bld_blk_id number,
5332 p_app_ovn number) is
5333 select day.resource_id,
5334 day.time_building_block_id,
5335 day.approval_style_id,
5336 max(day.object_version_number)
5337 from hxc_time_building_blocks day,
5338 hxc_time_building_blocks app
5339 where app.time_building_block_id = p_app_bld_blk_id
5340 and app.object_version_number = p_app_ovn
5341 and app.scope = 'APPLICATION_PERIOD'
5342 and app.resource_id = day.resource_id
5343 and day.scope = 'DAY'
5344 and day.start_time between app.start_time and app.stop_time
5345 group by day.resource_id,
5346 day.time_building_block_id,
5347 day.approval_style_id,
5348 day.object_version_number
5349 order by day.time_building_block_id;
5350 --
5351 cursor csr_get_appr_rule_id(p_appr_style_id number,
5352 p_time_recipient_id varchar2) is
5353 select dru.time_entry_rule_id
5354 from hxc_data_app_rule_usages dru
5355 where dru.approval_style_id = p_appr_style_id
5356 and to_char(dru.time_recipient_id) = p_time_recipient_id;
5357 --
5358 -- l_tc_bld_blk_id number;
5359 -- l_tc_ovn number;
5360 l_app_bld_blk_id number;
5361 l_app_ovn number;
5362 l_tc_date_from date;
5363 l_tc_date_to date;
5364 --
5365 l_tc_resource_id number;
5366 l_tc_appr_style_id hxc_data_app_rule_usages.approval_style_id%type;
5367 l_day_bb_id number;
5368 l_day_ovn number;
5369 l_time_recipient varchar2(150);
5370 l_tc_start_time date;
5371 l_tc_stop_time date;
5372 l_data_appr_rule_id hxc_time_entry_rules.time_entry_rule_id%type;
5373 --
5374 l_cnt number;
5375 -- l_item_key wf_items.item_key%type;
5376 l_current_rule varchar2(1000);
5377 l_all_rules varchar2(1000);
5378 --
5379 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_appr_rules';
5380 --
5381 BEGIN
5382 g_debug:=hr_utility.debug_enabled;
5383 --
5384 if g_debug then
5385 hr_utility.set_location(l_proc, 10);
5386 end if;
5387 --
5388 l_app_bld_blk_id := wf_engine.GetItemAttrNumber
5389 (itemtype => itemtype,
5390 itemkey => itemkey ,
5391 aname => 'APP_BB_ID');
5392 --
5393 if g_debug then
5394 hr_utility.set_location(l_proc, 20);
5395 end if;
5396 --
5397 l_app_ovn := wf_engine.GetItemAttrNumber
5398 (itemtype => itemtype,
5399 itemkey => itemkey ,
5400 aname => 'APP_BB_OVN');
5401 --
5402 if g_debug then
5403 hr_utility.set_location(l_proc, 30);
5404 end if;
5405 --
5406 l_time_recipient := wf_engine.GetItemAttrText
5407 (itemtype => itemtype,
5408 itemkey => itemkey,
5409 aname => 'TIME_RECIPIENT_ID');
5410 --
5411 if g_debug then
5412 hr_utility.set_location(l_proc, 40);
5413 --
5414 hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
5415 hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
5416 hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
5417 --
5418
5419 hr_utility.set_location(l_proc, 70);
5420 --
5421 end if;
5422 l_tc_appr_style_id := wf_engine.GetItemAttrNumber
5423 (itemtype => itemtype,
5424 itemkey => itemkey,
5425 aname => 'APPROVAL_STYLE_ID');
5426 --
5427 if g_debug then
5428 hr_utility.trace('l_tc_appr_style_id is : ' || to_char(l_tc_appr_style_id));
5429 end if;
5430 --
5431 open csr_get_appr_rule_id(l_tc_appr_style_id,
5432 l_time_recipient);
5433 fetch csr_get_appr_rule_id into l_data_appr_rule_id;
5434 --
5435 if g_debug then
5436 hr_utility.set_location(l_proc, 80);
5437 --
5438 hr_utility.trace('l_data_appr_rule_id is : ' || to_char(l_data_appr_rule_id));
5439 --
5440 end if;
5441 IF csr_get_appr_rule_id%NOTFOUND THEN
5442
5443 CLOSE csr_get_appr_rule_id;
5444 --
5445 result := 'COMPLETE:N';
5446 l_all_rules := 'NO_RULES';
5447 --
5448 if g_debug then
5449 hr_utility.set_location(l_proc, 90);
5450 --
5451 -- hr_utility.trace('Setting Status to Approved');
5452 --
5453 end if;
5454 -- wf_engine.SetItemAttrText(itemtype => itemtype,
5455 -- itemkey => itemkey,
5456 -- aname => 'APPROVAL_STATUS',
5457 -- avalue => 'APPROVED');
5458 --
5459 return;
5460 --
5461 ELSE
5462 --
5463 result := 'COMPLETE:Y';
5464 --
5465 if g_debug then
5466 hr_utility.set_location(l_proc, 100);
5467 end if;
5468 --
5469 l_cnt := 1;
5470 --
5471 LOOP
5472 --
5473 IF l_cnt = 1 THEN
5474 l_all_rules := to_char(l_data_appr_rule_id) || '|';
5475 ELSE
5476 l_all_rules := l_all_rules || to_char(l_data_appr_rule_id) || '|';
5477 END IF;
5478 --
5479 l_cnt := l_cnt + 1;
5480 --
5481 fetch csr_get_appr_rule_id into l_data_appr_rule_id;
5482 exit when csr_get_appr_rule_id%NOTFOUND;
5483 --
5484 END LOOP;
5485
5486 CLOSE csr_get_appr_rule_id;
5487 --
5488 if g_debug then
5489 hr_utility.set_location(l_proc, 110);
5490 --
5491 hr_utility.trace('l_all_rules is : ' || l_all_rules);
5492 --
5493 end if;
5494 END IF;
5495 --
5496 IF l_all_rules <> 'NO_RULES' THEN
5497 --
5498 if g_debug then
5499 hr_utility.set_location(l_proc, 120);
5500 end if;
5501 --
5502 wf_engine.SetItemAttrText(itemtype => itemtype,
5503 itemkey => itemkey,
5504 aname => 'ALL_RULES',
5505 avalue => l_all_rules);
5506 --
5507 if g_debug then
5508 hr_utility.trace('ALL_RULES Attribute is : ' || l_all_rules);
5509 end if;
5510 --
5511 END IF;
5512 --
5513 return;
5514 --
5515 exception
5516 when others then
5517 -- The line below records this function call in the error system
5518 -- in the case of an exception.
5519 --
5520 if g_debug then
5521 hr_utility.set_location(l_proc, 999);
5522 --
5523 hr_utility.trace('IN EXCEPTION IN chk_appr_rules');
5524 --
5525 end if;
5526 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.chk_appr_rules',
5527 itemtype, itemkey, to_char(actid), funcmode);
5528 raise;
5529 result := '';
5530 return;
5531 --
5532 --
5533 END chk_appr_rules;
5534 --
5535 --
5536 ------------------------ find_approval_rule ----------------------------
5537 --
5538 PROCEDURE find_approval_rule(itemtype IN varchar2,
5539 itemkey IN varchar2,
5540 actid IN number,
5541 funcmode IN varchar2,
5542 result IN OUT NOCOPY varchar2) is
5543 --
5544 l_current_rule varchar2(1000);
5545 l_all_rules varchar2(1000);
5546 --
5547 l_app_bld_blk_id number;
5548 l_app_ovn number;
5549 l_cnt number;
5550 -- l_item_key wf_items.item_key%type;
5551 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.find_approval_rule';
5552 --
5553 BEGIN
5554 g_debug:=hr_utility.debug_enabled;
5555 --
5556 if g_debug then
5557 hr_utility.set_location(l_proc, 10);
5558 end if;
5559 --
5560 l_all_rules := wf_engine.GetItemAttrText(itemtype => itemtype,
5561 itemkey => itemkey ,
5562 aname => 'ALL_RULES');
5563 --
5564 if g_debug then
5565 hr_utility.set_location(l_proc, 20);
5566 --
5567 hr_utility.trace('ALL_RULES is : ' || l_all_rules);
5568 --
5569 end if;
5570 l_cnt := instr(l_all_rules, '|');
5571 --
5572 IF l_cnt <> 0 THEN
5573 --
5574 if g_debug then
5575 hr_utility.set_location(l_proc, 30);
5576 end if;
5577 --
5578 l_current_rule := substr(l_all_rules, 1, l_cnt - 1);
5579 l_all_rules := replace(l_all_rules, l_current_rule || '|');
5580 --
5581 if g_debug then
5582 hr_utility.trace('l_current_rule is : ' || l_current_rule);
5583 end if;
5584 --
5585 result := 'COMPLETE:Y';
5586 --
5587 ELSE
5588 --
5589 if g_debug then
5590 hr_utility.set_location(l_proc, 40);
5591 end if;
5592 --
5593 result := 'COMPLETE:N';
5594 --
5595 if g_debug then
5596 hr_utility.trace('No More Rules - Setting status to APPROVED');
5597 end if;
5598 --
5599 wf_engine.SetItemAttrText(itemtype => itemtype,
5600 itemkey => itemkey,
5601 aname => 'APPROVAL_STATUS',
5602 avalue => 'APPROVED');
5603
5604
5605 wf_engine.SetItemAttrText(itemtype => itemtype,
5606 itemkey => itemkey,
5607 aname => 'APR_REJ_REASON',
5608 avalue => 'AUTO_APPROVE');
5609 --OIT Enhancement.
5610 --FYI Notification to WORKER on timecard AUTO APPROVE
5611 hxc_approval_wf_helper.set_notif_attribute_values
5612 (itemtype,
5613 itemkey,
5614 hxc_app_comp_notifications_api.c_action_auto_approve,
5615 hxc_app_comp_notifications_api.c_recipient_worker
5616 );
5617
5618
5619 return;
5620 --
5621 END IF;
5622 --
5623 wf_engine.SetItemAttrText(itemtype => itemtype,
5624 itemkey => itemkey,
5625 aname => 'CURRENT_RULE',
5626 avalue => l_current_rule);
5627 --
5628 if g_debug then
5629 hr_utility.set_location(l_proc, 60);
5630 --
5631 hr_utility.trace('CURRENT_RULE is : ' || l_current_rule);
5632 --
5633 end if;
5634 wf_engine.SetItemAttrText(itemtype => itemtype,
5635 itemkey => itemkey,
5636 aname => 'ALL_RULES',
5637 avalue => l_all_rules);
5638 --
5639 if g_debug then
5640 hr_utility.set_location(l_proc, 60);
5641 --
5642 hr_utility.trace('ALL_RULES is : ' || l_all_rules);
5643 --
5644 end if;
5645 return;
5646 --
5647 exception
5648 when others then
5649 -- The line below records this function call in the error system
5650 -- in the case of an exception.
5651 --
5652 if g_debug then
5653 hr_utility.set_location(l_proc, 999);
5654 --
5655 hr_utility.trace('IN EXCEPTION IN find_approval_rule');
5656 --
5657 end if;
5658 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.find_approval_rule',
5659 itemtype, itemkey, to_char(actid), funcmode);
5660 raise;
5661 result := '';
5662 return;
5663 --
5664 --
5665 END find_approval_rule;
5666 --
5667 --
5668 FUNCTION was_approved(
5669 p_app_period_id IN hxc_time_building_blocks.time_building_block_id%TYPE
5670 ,p_app_period_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5671 )
5672 RETURN BOOLEAN
5673 IS
5674 CURSOR c_was_approved(
5675 p_app_period_id IN hxc_time_building_blocks.time_building_block_id%TYPE
5676 ,p_app_period_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5677 )
5678 IS
5679 SELECT 'Y'
5680 FROM hxc_time_building_blocks
5681 WHERE time_building_block_id = p_app_period_id
5682 AND object_version_number < p_app_period_ovn
5683 AND approval_status = 'APPROVED';
5684
5685 l_was_approved VARCHAR2(1) := 'N';
5686 BEGIN
5687 OPEN c_was_approved(p_app_period_id, p_app_period_ovn);
5688 FETCH c_was_approved INTO l_was_approved;
5689 CLOSE c_was_approved;
5690
5691 IF l_was_approved = 'Y'
5692 THEN
5693 RETURN TRUE;
5694 END IF;
5695
5696 RETURN FALSE;
5697
5698 END was_approved;
5699
5700 FUNCTION same_no_blocks(
5701 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
5702 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5703 )
5704 RETURN BOOLEAN
5705 IS
5706 CURSOR c_no_blocks(
5707 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
5708 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5709 )
5710 IS
5711 SELECT COUNT(*)
5712 FROM hxc_time_building_blocks
5713 START WITH time_building_block_id = p_timecard_id
5714 AND object_version_number = p_timecard_ovn
5715 CONNECT by prior time_building_block_id =
5716 parent_building_block_id
5717 and prior object_version_number =
5718 parent_building_block_ovn;
5719
5720 CURSOR c_old_tc(
5721 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
5722 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5723 )
5724 IS
5725 SELECT object_version_number
5726 FROM hxc_time_building_blocks
5727 WHERE time_building_block_id = p_timecard_id
5728 AND approval_status = 'SUBMITTED'
5729 AND object_version_number < p_timecard_ovn
5730 ORDER BY object_version_number desc;
5731
5732 l_current_tc_count NUMBER;
5733 l_old_tc_count NUMBER;
5734 l_previous_tc_ovn hxc_time_building_blocks.object_version_number%TYPE := NULL;
5735 BEGIN
5736 OPEN c_old_tc(p_timecard_id, p_timecard_ovn);
5737 FETCH c_old_tc INTO l_previous_tc_ovn;
5738 CLOSE c_old_tc;
5739
5740 IF l_previous_tc_ovn IS NULL
5741 THEN
5742 RETURN FALSE;
5743 END IF;
5744
5745
5746 OPEN c_no_blocks(p_timecard_id, p_timecard_ovn);
5747 FETCH c_no_blocks INTO l_current_tc_count;
5748 CLOSE c_no_blocks;
5749
5750 OPEN c_no_blocks(p_timecard_id, l_previous_tc_ovn);
5751 FETCH c_no_blocks INTO l_old_tc_count;
5752 CLOSE c_no_blocks;
5753
5754 IF l_current_tc_count = l_old_tc_count
5755 THEN
5756 RETURN TRUE;
5757 END IF;
5758
5759 RETURN FALSE;
5760 END same_no_blocks;
5761
5762
5763
5764 ------------------------ execute_appr_rule ----------------------------
5765 --
5766 PROCEDURE execute_appr_rule(itemtype IN varchar2,
5767 itemkey IN varchar2,
5768 actid IN number,
5769 funcmode IN varchar2,
5770 result IN OUT NOCOPY varchar2) is
5771 --
5772 cursor csr_get_appr_rule_info(p_data_appr_rule_id number,
5773 p_end_date date) is
5774 select dar.name
5775 ,NVL( dar.description, dar.name ) ter_message_name
5776 ,dar.rule_usage
5777 ,dar.formula_id
5778 ,dar.mapping_id
5779 ,dar.attribute1
5780 ,dar.attribute2
5781 ,dar.attribute3
5782 ,dar.attribute4
5783 ,dar.attribute5
5784 ,dar.attribute6
5785 ,dar.attribute7
5786 ,dar.attribute8
5787 ,dar.attribute9
5788 ,dar.attribute10
5789 ,dar.attribute11
5790 ,dar.attribute12
5791 ,dar.attribute13
5792 ,dar.attribute14
5793 ,dar.attribute15
5794 ,ff.formula_name
5795 ,''
5796 from ff_formulas_f ff
5797 ,hxc_time_entry_rules dar
5798 where dar.time_entry_rule_id = p_data_appr_rule_id
5799 and p_end_date between dar.start_date and dar.end_date
5800 and ff.formula_id(+) = dar.formula_id
5801 and dar.start_date BETWEEN ff.effective_start_date(+)
5802 AND ff.effective_end_date(+)
5803 order by dar.start_date;
5804
5805 CURSOR csr_get_tc_dates ( p_bb_id NUMBER, p_bb_ovn NUMBER )IS
5806 SELECT start_time, stop_time
5807 FROM hxc_time_building_blocks
5808 WHERE time_building_block_id = p_bb_id
5809 AND object_version_number = p_bb_ovn;
5810 --
5811 l_data_appr_rule_id hxc_time_entry_rules.time_entry_rule_id%type;
5812 l_rule_usage hxc_time_entry_rules.rule_usage%type;
5813 l_formula_id hxc_time_entry_rules.formula_id%type;
5814 l_tc_start_date date;
5815 l_tc_end_date date;
5816 --
5817 l_app_start_date date;
5818 l_app_end_date date;
5819 l_tc_resource_id number;
5820 l_tc_bld_blk_id number;
5821 l_tc_ovn number;
5822 l_current_rule varchar2(1000);
5823 l_all_rules varchar2(1000);
5824 --
5825 l_rule_rec hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype;
5826 l_outputs ff_exec.outputs_t;
5827 l_result varchar2(1000);
5828 --
5829 l_error_table hxc_self_service_time_deposit.message_table;
5830 l_mapping_changed boolean;
5831 l_bld_blk_changed boolean;
5832 l_changed varchar2(10);
5833 l_cnt number;
5834 l_resubmit varchar2(10);
5835 l_bb_new varchar2(10);
5836 l_appl_period_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
5837 l_appl_period_bb_ovn hxc_time_building_blocks.object_version_number%TYPE;
5838 -- l_item_key wf_items.item_key%type;
5839 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.execute_appr_rule';
5840 --
5841 BEGIN
5842 g_debug:=hr_utility.debug_enabled;
5843 --
5844 if g_debug then
5845 hr_utility.set_location(l_proc, 10);
5846 end if;
5847 --
5848
5849
5850 l_current_rule := wf_engine.GetItemAttrText(itemtype => itemtype,
5851 itemkey => itemkey,
5852 aname => 'CURRENT_RULE');
5853 --
5854 if g_debug then
5855 hr_utility.set_location(l_proc, 20);
5856 end if;
5857 --
5858 l_app_start_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
5859 itemkey => itemkey,
5860 aname => 'APP_START_DATE');
5861 --
5862 if g_debug then
5863 hr_utility.set_location(l_proc, 25);
5864 end if;
5865 --
5866 l_app_end_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
5867 itemkey => itemkey,
5868 aname => 'APP_END_DATE');
5869 --
5870 if g_debug then
5871 hr_utility.set_location(l_proc, 30);
5872 end if;
5873 --
5874 l_tc_resource_id := wf_engine.GetItemAttrNumber(
5875 itemtype => itemtype,
5876 itemkey => itemkey,
5877 aname => 'RESOURCE_ID');
5878 --
5879 if g_debug then
5880 hr_utility.set_location(l_proc, 40);
5881 end if;
5882 --
5883 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
5884 (itemtype => itemtype,
5885 itemkey => itemkey,
5886 aname => 'TC_BLD_BLK_ID');
5887 --
5888 if g_debug then
5889 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
5890 end if;
5891 --
5892 l_tc_ovn := wf_engine.GetItemAttrNumber
5893 (itemtype => itemtype,
5894 itemkey => itemkey,
5895 aname => 'TC_BLD_BLK_OVN');
5896 --
5897 if g_debug then
5898 hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
5899 end if;
5900 --
5901 -- Set up l_resubmit to indicate whether this is a resubmission or not.
5902 -- (YES means it is a resubmission; NO means it is a submission).
5903 --
5904 l_resubmit := wf_engine.GetItemAttrText(itemtype => itemtype,
5905 itemkey => itemkey,
5906 aname => 'TC_RESUBMITTED');
5907 --
5908 if g_debug then
5909 hr_utility.set_location(l_proc, 50);
5910 end if;
5911 --
5912 l_bb_new := wf_engine.GetItemAttrText(itemtype => itemtype,
5913 itemkey => itemkey,
5914 aname => 'BB_NEW');
5915 --
5916 if g_debug then
5917 hr_utility.set_location(l_proc, 52);
5918 end if;
5919 --
5920 l_data_appr_rule_id := to_number(l_current_rule);
5921 --
5922 if g_debug then
5923 hr_utility.trace('l_current_rule is : ' || l_current_rule);
5924 hr_utility.trace('l_app_end_date is : ' ||
5925 to_char(l_app_end_date, 'DD-MM-YYYY'));
5926 hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5927 hr_utility.trace('l_resubmit is : ' || l_resubmit);
5928 end if;
5929 --
5930 open csr_get_appr_rule_info(l_current_rule, l_app_end_date);
5931 fetch csr_get_appr_rule_info into l_rule_rec;
5932 close csr_get_appr_rule_info;
5933 --
5934 if g_debug then
5935 hr_utility.set_location(l_proc, 60);
5936 end if;
5937 --
5938 IF (l_resubmit = 'YES' AND l_rule_rec.rule_usage <> 'SUBMISSION') OR
5939 (l_resubmit = 'NO' AND l_rule_rec.rule_usage <> 'RESUBMISSION') THEN
5940 --
5941 if g_debug then
5942 hr_utility.set_location(l_proc, 70);
5943 end if;
5944 --
5945 -- Apply rule
5946 --
5947 IF l_rule_rec.mapping_id IS NOT NULL THEN
5948 --
5949 -- Mapping needs to be checked. If any of the fields in the
5950 -- mapping have changed, and it is a resubmission
5951 -- then need to approve this timecard. Set l_changed to YES
5952 -- or NO accordingly, and use it to decide whether the formula,
5953 -- if there is one, needs to be applied.
5954 -- Then, check for formula, since it is possible to have both
5955 -- a mapping and a formula.
5956 --
5957 if g_debug then
5958 hr_utility.set_location(l_proc, 80);
5959 end if;
5960 --
5961 IF l_resubmit = 'YES' THEN
5962 --
5963 if g_debug then
5964 hr_utility.set_location(l_proc, 90);
5965 end if;
5966 l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
5967 itemtype => itemtype,
5968 itemkey => itemkey,
5969 aname => 'APP_BB_ID');
5970
5971 if g_debug then
5972 hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
5973 end if;
5974 l_appl_period_bb_ovn := wf_engine.GetItemAttrNumber(
5975 itemtype => itemtype,
5976 itemkey => itemkey,
5977 aname => 'APP_BB_OVN');
5978
5979 IF NOT was_approved(l_appl_period_bb_id, l_appl_period_bb_ovn)
5980 THEN
5981 wf_engine.SetItemAttrText(itemtype => itemtype,
5982 itemkey => itemkey,
5983 aname => 'TO_APPROVE',
5984 avalue => 'YES');
5985
5986 result := '';
5987 RETURN;
5988 END IF;
5989
5990 --
5991 -- Check to see if any values in hxc_time_building_blocks have
5992 -- changed for the submitted timecard.
5993 --
5994 IF l_bb_new = 'YES' THEN
5995 l_bld_blk_changed := TRUE;
5996 ELSE
5997 l_bld_blk_changed := same_no_blocks(l_tc_bld_blk_id, l_tc_ovn);
5998
5999 IF NOT l_bld_blk_changed
6000 THEN
6001 l_bld_blk_changed := hxc_mapping_utilities.chk_bld_blk_changed (
6002 p_timecard_bb_id => l_tc_bld_blk_id
6003 ,p_timecard_ovn => l_tc_ovn
6004 ,p_start_date => l_app_start_date
6005 ,p_end_date => l_app_end_date
6006 ,p_last_status => 'SUBMITTED'
6007 ,p_time_bld_blks => g_time_building_blocks);
6008 END IF;
6009 END IF;
6010 --
6011 -- Check to see if the mappings have changed for the submitted
6012 -- timecard, if nothing in hxc_time_building_blocks has changed.
6013 --
6014 IF l_bld_blk_changed THEN
6015 --
6016 if g_debug then
6017 hr_utility.set_location(l_proc, 130);
6018 end if;
6019 --
6020 l_changed := 'YES';
6021 --
6022 ELSE
6023 --
6024 if g_debug then
6025 hr_utility.set_location(l_proc, 140);
6026 end if;
6027 --
6028 l_mapping_changed := hxc_mapping_utilities.chk_mapping_changed(
6029 p_mapping_id => l_rule_rec.mapping_id
6030 ,p_timecard_bb_id => l_tc_bld_blk_id
6031 ,p_timecard_ovn => l_tc_ovn
6032 ,p_start_date => l_app_start_date
6033 ,p_end_date => l_app_end_date
6034 ,p_last_status => 'SUBMITTED'
6035 ,p_time_building_blocks => g_time_building_blocks
6036 ,p_time_attributes => g_time_attributes);
6037 --
6038 if g_debug then
6039 hr_utility.set_location(l_proc, 120);
6040 end if;
6041 --
6042 -- If there are differences, set l_changed to YES; else set to NO.
6043 --
6044 IF l_mapping_changed THEN
6045 --
6046 if g_debug then
6047 hr_utility.set_location(l_proc, 130);
6048 end if;
6049 --
6050 l_changed := 'YES';
6051 --
6052 ELSE
6053 --
6054 if g_debug then
6055 hr_utility.set_location(l_proc, 140);
6056 end if;
6057 --
6058 l_changed := 'NO';
6059 --
6060 END IF;
6061 --
6062 END IF;
6063 --
6064 ELSE -- not a resubmission, so fields in mapping are new.
6065 --
6066 -- Set l_changed to YES
6067 --
6068 if g_debug then
6069 hr_utility.set_location(l_proc, 150);
6070 end if;
6071 --
6072 l_changed := 'YES';
6073 --
6074 END IF;
6075 --
6076 ELSE
6077 --
6078 -- No Mapping ID, but there might still be a formula so set
6079 -- l_changed to YES
6080 --
6081 if g_debug then
6082 hr_utility.set_location(l_proc, 160);
6083 end if;
6084 --
6085 l_changed := 'NO'; -- GPM v115.48 WWB 2724576
6086 --
6087 END IF;
6088 --
6089 if g_debug then
6090 hr_utility.set_location(l_proc, 170);
6091 end if;
6092 --
6093 -- Check to see is a formula needs to be applied.
6094 --
6095 IF (l_rule_rec.formula_id IS NOT NULL AND l_changed = 'NO') -- GPM v115.48 WWB 2724576
6096 THEN
6097 --
6098 if g_debug then
6099 hr_utility.set_location(l_proc, 180);
6100 --
6101 hr_utility.trace('l_formula_name is : ' || l_rule_rec.formula_name);
6102 --
6103 hr_utility.set_location(l_proc, 190);
6104 end if;
6105 --
6106 -- call execute approval formula
6107 --
6108
6109 -- get tc period start and stop times
6110
6111 OPEN csr_get_tc_dates ( l_tc_bld_blk_id, l_tc_ovn );
6112 FETCH csr_get_tc_dates INTO l_tc_start_date, l_tc_end_date;
6113 CLOSE csr_get_tc_dates;
6114
6115 l_error_table.delete;
6116 --
6117 -- GPM v115.21
6118 l_result := hxc_ff_dict.execute_approval_formula(
6119 p_resource_id => l_tc_resource_id
6120 ,p_period_start_date => l_app_start_date
6121 ,p_period_end_date => l_app_end_date
6122 ,p_tc_period_start_date => l_tc_start_date
6123 ,p_tc_period_end_date => l_tc_end_date
6124 ,p_rule_rec => l_rule_rec
6125 ,p_message_table => l_error_table);
6126 --
6127 IF upper(l_result) = 'Y' THEN
6128 l_result := 'YES';
6129 END IF;
6130 --
6131 IF upper(l_result) = 'N' THEN
6132 l_result := 'NO';
6133 END IF;
6134 --
6135 IF upper(l_result) <> 'YES' AND upper(l_result) <> 'NO' THEN
6136 hr_utility.raise_error;
6137 END IF;
6138 --
6139 IF l_error_table.count > 0 THEN
6140 hr_utility.set_message(809, l_error_table(1).message_name);
6141 hr_utility.raise_error;
6142 END IF;
6143 --
6144 wf_engine.SetItemAttrText(itemtype => itemtype,
6145 itemkey => itemkey,
6146 aname => 'TO_APPROVE',
6147 avalue => upper(l_result));
6148 --
6149 if g_debug then
6150 hr_utility.trace('TO_APPROVE is : ' || l_result);
6151 end if;
6152 --
6153 ELSE
6154 --
6155 -- Set TO_APPROVE to l_changed.
6156 --
6157 if g_debug then
6158 hr_utility.set_location(l_proc, 230);
6159 end if;
6160 --
6161 wf_engine.SetItemAttrText(itemtype => itemtype,
6162 itemkey => itemkey,
6163 aname => 'TO_APPROVE',
6164 avalue => l_changed);
6165 --
6166 if g_debug then
6167 hr_utility.trace('TO_APPROVE is : ' || l_changed);
6168 end if;
6169 --
6170 END IF;
6171 --
6172 ELSE
6173 --
6174 -- Set TO_APPROVE attribute to YES since the rule does not apply.
6175 --
6176 if g_debug then
6177 hr_utility.set_location(l_proc, 240);
6178 end if;
6179 --
6180 -- Modifying the 'TO_APPROVE' value from 'NO' to 'YES' and Commenting out the following code for bug#3497011.
6181
6182 /* wf_engine.SetItemAttrText(itemtype => itemtype,
6183 itemkey => itemkey,
6184 aname => 'TO_APPROVE',
6185 avalue => 'NO');
6186 --
6187 if g_debug then
6188 hr_utility.trace('TO_APPROVE is : NO');
6189 end if; */
6190
6191 wf_engine.SetItemAttrText(itemtype => itemtype,
6192 itemkey => itemkey,
6193 aname => 'TO_APPROVE',
6194 avalue => 'YES');
6195 --
6196 if g_debug then
6197 hr_utility.trace('TO_APPROVE is : YES');
6198 end if;--
6199 END IF;
6200 --
6201 if g_debug then
6202 hr_utility.set_location(l_proc, 250);
6203 end if;
6204 --
6205 --
6206 result := '';
6207 return;
6208 --
6209 exception
6210 when others then
6211 -- The line below records this function call in the error system
6212 -- in the case of an exception.
6213 if g_debug then
6214 hr_utility.set_location(l_proc, 999);
6215 --
6216 hr_utility.trace('IN EXCEPTION IN execute_appr_rule');
6217 end if;
6218 --
6219 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.execute_appr_rule',
6220 itemtype, itemkey, to_char(actid), funcmode);
6221 raise;
6222 result := '';
6223 return;
6224 --
6225 --
6226 END execute_appr_rule;
6227 --
6228 --
6229 ------------------------ chk_approval_req ----------------------------
6230 --
6231 PROCEDURE chk_approval_req(itemtype IN varchar2,
6232 itemkey IN varchar2,
6233 actid IN number,
6234 funcmode IN varchar2,
6235 result IN OUT NOCOPY varchar2) is
6236 --
6237 l_current_rule varchar2(1000);
6238 l_all_rules varchar2(1000);
6239 l_to_approve varchar2(1000);
6240 --
6241 l_cnt number;
6242 -- l_item_key wf_items.item_key%type;
6243 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_approval_req';
6244 --
6245 BEGIN
6246 g_debug:=hr_utility.debug_enabled;
6247 --
6248 if g_debug then
6249 hr_utility.set_location(l_proc, 10);
6250 end if;
6251 --
6252 l_to_approve := wf_engine.GetItemAttrText(itemtype => itemtype,
6253 itemkey => itemkey ,
6254 aname => 'TO_APPROVE');
6255 --
6256 if g_debug then
6257 hr_utility.set_location(l_proc, 20);
6258 end if;
6259 --
6260 if g_debug then
6261 hr_utility.trace('l_to_approve is : ' || l_to_approve);
6262 end if;
6263 --
6264 IF l_to_approve = 'YES' THEN
6265 --
6266 if g_debug then
6267 hr_utility.set_location(l_proc, 30);
6268 end if;
6269 --
6270 result := 'COMPLETE:Y';
6271 --
6272 return;
6273 --
6274 ELSE
6275 --
6276 if g_debug then
6277 hr_utility.set_location(l_proc, 40);
6278 end if;
6279 --
6280 result := 'COMPLETE:N';
6281 --
6282 return;
6283 --
6284 END IF;
6285 --
6286 exception
6287 when others then
6288 -- The line below records this function call in the error system
6289 -- in the case of an exception.
6290 --
6291 if g_debug then
6292 hr_utility.set_location(l_proc, 999);
6293 --
6294 hr_utility.trace('IN EXCEPTION IN chk_approval_req');
6295 end if;
6296 --
6297 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.chk_approval_req',
6298 itemtype, itemkey, to_char(actid), funcmode);
6299 raise;
6300 --
6301 result := '';
6302 return;
6303 --
6304 --
6305 END chk_approval_req;
6306 --
6307 --------------------------- get_override -------------------------------
6308 --
6309 FUNCTION get_override(p_timecard_bb_id NUMBER
6310 ,p_timecard_ovn NUMBER) RETURN NUMBER IS
6311 --
6312 l_return hxc_time_building_blocks.resource_id%TYPE;
6313 --
6314 cursor csr_get_override_id is
6315 select to_number(ta.attribute10)
6316 from hxc_time_attributes ta,
6317 hxc_time_attribute_usages tau,
6318 hxc_time_building_blocks tbb
6319 where tbb.time_building_block_id = p_timecard_bb_id
6320 and tbb.object_version_number = p_timecard_ovn
6321 and tbb.time_building_block_id = tau.time_building_block_id
6322 and tbb.object_version_number = tau.time_building_block_ovn
6323 and ta.time_attribute_id = tau.time_attribute_id
6324 and ta.attribute_category = 'APPROVAL';
6325 --
6326 --
6327 BEGIN
6328 --
6329 OPEN csr_get_override_id;
6330 FETCH csr_get_override_id INTO l_return;
6331 CLOSE csr_get_override_id;
6332 --
6333 RETURN l_return;
6334 --
6335 end get_override;
6336
6337 PROCEDURE is_different_time_category (itemtype IN varchar2,
6338 itemkey IN varchar2,
6339 actid IN number,
6340 funcmode IN varchar2,
6341 result IN OUT NOCOPY varchar2) is
6342
6343 l_is_blank varchar2(1);
6344 l_total_hours number;
6345 l_app_bb_id number;
6346 l_approval_mechansim varchar2(20);
6347
6348 begin
6349
6350 l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
6351 itemkey => itemkey ,
6352 aname => 'IS_DIFF_TC',
6353 ignore_notfound => true
6354 );
6355
6356 l_app_bb_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
6357 itemkey => itemkey ,
6358 aname => 'APP_BB_ID');
6359
6360 hr_utility.trace('is_different_time_category l_is_blank ::'||l_is_blank);
6361
6362 hr_utility.trace('is_different_time_category l_app_bb_id ::'||l_app_bb_id);
6363
6364 if l_is_blank = 'Y' then
6365
6366 l_total_hours:= HXC_FIND_NOTIFY_APRS_PKG.category_timecard_hrs(l_app_bb_id,'');
6367
6368 wf_engine.SetItemAttrNumber(
6369 itemtype => itemtype,
6370 itemkey => itemkey,
6371 aname => 'TOTAL_TC_HOURS',
6372 avalue => l_total_hours);
6373
6374 hr_utility.trace('is_different_time_category COMPLETE:Y ');
6375 result := 'COMPLETE:Y';
6376 else
6377 hr_utility.trace('is_different_time_category COMPLETE:N ');
6378 result := 'COMPLETE:N';
6379
6380 end if;
6381
6382 end is_different_time_category;
6383 --
6384 --
6385 end hxc_approval_wf_pkg;