1 package body hxc_timecard_summary_api as
2 /* $Header: hxctcsumapi.pkb 120.2.12020000.2 2012/07/04 09:36:11 amnaraya ship $ */
3 Procedure delete_timecard
4 (p_blocks in hxc_block_table_type
5 ,p_timecard_id in number
6 ,p_mode in varchar2 default hxc_timecard_summary_pkg.c_normal_mode
7 ) is
8
9 l_index number;
10 l_previous_status hxc_timecard_summary.approval_status%type;
11
12 l_item_key hxc_timecard_summary.approval_item_key%type;
13 l_dummy varchar2(1);
14
15 cursor c_previously_submitted(
16 p_timecard_id in hxc_timecard_summary.timecard_id%type) is
17 select ts.approval_status
18 from hxc_timecard_summary ts
19 where timecard_id = p_timecard_id
20 and exists(
21 select 1
22 from hxc_tc_ap_links tcl,
23 hxc_app_period_summary aps
24 where aps.application_period_id = tcl.application_period_id
25 and tcl.timecard_id = ts.timecard_id
26 and aps.approval_status = hxc_timecard.c_submitted_status
27 );
28
29
30 cursor c_get_item_key(p_timecard_id in number)
31 is
32 select approval_item_key
33 from hxc_timecard_summary
34 where timecard_id = p_timecard_id;
35
36 cursor c_is_wf_deferred(p_item_key in hxc_timecard_summary.approval_item_key%type)
37 is
38 select 'Y'
39 from wf_item_activity_statuses wias
40 where item_type = 'HXCEMP'
41 and item_key = l_item_key
42 and activity_status = 'DEFERRED';
43
44 Begin
45
46 --
47 -- 0. Close all nofications associated with this timecard, only
48 -- if we're not migrating.
49 -- if we are not migrating, and the timecard is submitted,
50 -- indicating that there might still be an open notification
51 -- that requires cancellation.
52
53 --
54 if(p_mode = hxc_timecard_summary_pkg.c_normal_mode) then
55 open c_previously_submitted(p_timecard_id);
56 fetch c_previously_submitted into l_previous_status;
57 if (c_previously_submitted%found) then
58 /*
59 --Cancel notifications for TK Audit
60
61 hxc_timekeeper_wf_pkg.cancel_previous_notifications
62 ( p_tk_audit_item_type => l_previous_tk_item_type
63 ,p_tk_audit_item_key => l_previous_tk_item_key
64 );
65 */
66 --Cancel notifications for Approval
67
68 hxc_find_notify_aprs_pkg.cancel_previous_notifications
69 (p_timecard_id => p_timecard_id);
70
71 end if;
72 close c_previously_submitted;
73 end if;
74
75 -- 1. Remove all the links between the timecard and the application
76 -- periods
77
78 hxc_tc_ap_links_pkg.remove_timecard_links
79 (p_timecard_id => p_timecard_id);
80
81
82 open c_get_item_key(p_timecard_id);
83 fetch c_get_item_key into l_item_key;
84 close c_get_item_key;
85
86 If l_item_key is not null then
87 open c_is_wf_deferred(l_item_key);
88 fetch c_is_wf_deferred into l_dummy;
89 close c_is_wf_deferred;
90
91 If l_dummy = 'Y' then
92 wf_engine.AbortProcess(itemkey => l_item_key,
93 itemtype => 'HXCEMP');
94 end if;
95 end if;
96
97
98 -- 2. Delete the existing timecard information in the summary
99 --
100
101 hxc_timecard_summary_pkg.delete_summary_row
102 (p_timecard_id => p_timecard_id);
103
104 End delete_timecard;
105
106 Procedure delete_timecard
107 (p_timecard_id in hxc_timecard_summary.timecard_id%type
108 ,p_mode in varchar2 default hxc_timecard_summary_pkg.c_normal_mode
109 ) is
110
111 l_index number;
112
113 l_dummy varchar(1);
114
115 l_item_key hxc_timecard_summary.approval_item_key%type;
116 l_dummy_ik varchar2(1);
117
118 cursor c_previously_submitted(
119 p_timecard_id in hxc_timecard_summary.timecard_id%type) is
120 select '1'
121 from hxc_timecard_summary
122 where timecard_id = p_timecard_id;
123
124
125 cursor c_get_item_key(p_timecard_id in number)
126 is
127 select approval_item_key
128 from hxc_timecard_summary
129 where timecard_id = p_timecard_id;
130
131 cursor c_is_wf_deferred(p_item_key in hxc_timecard_summary.approval_item_key%type)
132 is
133 select 'Y'
134 from wf_item_activity_statuses wias
135 where item_type = 'HXCEMP'
136 and item_key = l_item_key
137 and activity_status = 'DEFERRED';
138
139
140 Begin
141 --
142 -- 0. Close all nofications associated with this timecard, only
143 -- if we're not migrating.
144 --
145 if(p_mode = hxc_timecard_summary_pkg.c_normal_mode) then
146
147
148 open c_previously_submitted(p_timecard_id);
149 fetch c_previously_submitted into l_dummy;
150 if (c_previously_submitted%found) then
151 /*
152 --Cancel notifications for TK Audit
153
154 hxc_timekeeper_wf_pkg.cancel_previous_notifications
155 ( p_tk_audit_item_type => l_previous_tk_item_type
156 ,p_tk_audit_item_key => l_previous_tk_item_key
157 );
158 */
159 --Cancel notifications for Approval
160
161 hxc_find_notify_aprs_pkg.cancel_previous_notifications
162 (p_timecard_id => p_timecard_id);
163 end if;
164 close c_previously_submitted;
165
166 end if;
167
168
169 -- 1. Remove all the links between the timecard and the application
170 -- periods
171
172 hxc_tc_ap_links_pkg.remove_timecard_links
173 (p_timecard_id => p_timecard_id);
174
175 open c_get_item_key(p_timecard_id);
176 fetch c_get_item_key into l_item_key;
177 close c_get_item_key;
178
179 If l_item_key is not null then
180 open c_is_wf_deferred(l_item_key);
181 fetch c_is_wf_deferred into l_dummy_ik;
182 close c_is_wf_deferred;
183
184 If l_dummy_ik = 'Y' then
185 wf_engine.AbortProcess(itemkey => l_item_key,
186 itemtype => 'HXCEMP');
187 end if;
188 end if;
189
190 -- 2. Delete the existing timecard information in the summary
191
192 hxc_timecard_summary_pkg.delete_summary_row
193 (p_timecard_id => p_timecard_id);
194
195 End delete_timecard;
196
197
198 Function timecard_present
199 (p_timecard_id in hxc_timecard_summary.timecard_id%type) return boolean is
200
201 l_dummy hxc_timecard_summary.timecard_id%type;
202
203 begin
204
205 select timecard_id
206 into l_dummy
207 from hxc_timecard_summary
208 where timecard_id = p_timecard_id;
209
210 return true;
211
212 exception
213 when others then
214 return false;
215 end timecard_present;
216
217 Procedure cleanup_timecards
218 (p_blocks in hxc_block_table_type) is
219
220 l_index number;
221
222 Begin
223
224 l_index := p_blocks.first;
225
226 Loop
227 Exit when not p_blocks.exists(l_index);
228
229 if(p_blocks(l_index).scope = hxc_timecard.c_timecard_scope) then
230 if(timecard_present(p_blocks(l_index).time_building_block_id)) then
231 delete_timecard(p_blocks,p_blocks(l_index).time_building_block_id);
232 -- Bug 12919783
233 ELSE
234 INSERT INTO hxc_rollback_timecards_all
235 (timecard_id,
236 timecard_type)
237 VALUES
238 ( p_blocks(l_index).time_building_block_id,
239 'HXC_TIMECARD_SUMMARY');
240
241 end if;
242 end if;
243
244 l_index := p_blocks.next(l_index);
245 End Loop;
246
247 End cleanup_timecards;
248
249 procedure timecard_deposit
250 (p_blocks in hxc_block_table_type
251 ,p_mode in varchar2 default hxc_timecard_summary_pkg.c_normal_mode
252 ,p_approval_item_type in varchar2
253 ,p_approval_process_name in varchar2
254 ,p_approval_item_key in varchar2
255 ,p_tk_audit_item_type in varchar2
256 ,p_tk_audit_process_name in varchar2
257 ,p_tk_audit_item_key in varchar2
258 ) IS
259
260 l_timecard_index number;
261
262 Begin
263 --
264 -- 1. Find the timecard index of the blocks
265 --
266 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
267 --
268 -- 2. Clean up if this is an existing timecard
269 --
270 cleanup_timecards(p_blocks);
271 --
272 -- 3. Create timecard summary info
273 --
274 hxc_timecard_summary_pkg.insert_summary_row
275 (p_timecard_id => p_blocks(l_timecard_index).time_building_block_id
276 ,p_mode => p_mode
277 ,p_approval_item_type => p_approval_item_type
278 ,p_approval_process_name => p_approval_process_name
279 ,p_approval_item_key => p_approval_item_key
280 ,p_tk_audit_item_type => p_tk_audit_item_type
281 ,p_tk_audit_process_name => p_tk_audit_process_name
282 ,p_tk_audit_item_key => p_tk_audit_item_key
283 );
284 --
285 -- 4. Create the link information if the
286 -- mode is migration
287 --
288 if(p_mode = hxc_timecard_summary_pkg.c_migration_mode) then
289 hxc_tc_ap_links_pkg.create_timecard_links
290 (p_timecard_id => p_blocks(l_timecard_index).time_building_block_id);
291 end if;
292 End timecard_deposit;
293
294 Procedure timecard_deposit
295 (p_timecard_id in hxc_timecard_summary.timecard_id%type
296 ,p_mode in varchar2 default hxc_timecard_summary_pkg.c_normal_mode
297 ,p_approval_item_type in varchar2
298 ,p_approval_process_name in varchar2
299 ,p_approval_item_key in varchar2
300 ,p_tk_audit_item_type in varchar2
301 ,p_tk_audit_process_name in varchar2
302 ,p_tk_audit_item_key in varchar2
303 ) is
304
305 Begin
306 --
307 -- 1. Clean up if this is an existing timecard
308 -- NOTE: This version is called on migration, and we
309 -- absolutely don't want to call cleanup_timecards
310 -- on migration.
311
312 delete_timecard(p_timecard_id, p_mode);
313 --
314 -- 3. Create timecard summary info
315 --
316 hxc_timecard_summary_pkg.insert_summary_row
317 (p_timecard_id => p_timecard_id
318 ,p_mode => p_mode
319 ,p_approval_item_type => p_approval_item_type
320 ,p_approval_process_name => p_approval_process_name
321 ,p_approval_item_key => p_approval_item_key
322 ,p_tk_audit_item_type => p_tk_audit_item_type
323 ,p_tk_audit_process_name => p_tk_audit_process_name
324 ,p_tk_audit_item_key => p_tk_audit_item_key
325 );
326
327 End timecard_deposit;
328
329 Procedure timecard_delete
330 (p_blocks in hxc_block_table_type) is
331
332 l_timecard_index number;
333
334 Begin
335
336 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
337
338 delete_timecard(p_blocks,l_timecard_index, hxc_timecard_summary_pkg.c_normal_mode);
339
340 End timecard_delete;
341
342 Procedure timecard_delete
343 (p_timecard_id in hxc_timecard_summary.timecard_id%type) is
344
345 Begin
346
347 delete_timecard(p_timecard_id, hxc_timecard_summary_pkg.c_normal_mode);
348
349 End timecard_delete;
350
351 procedure reject_timecards
352 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
353 --
354 -- In this case we have a rejection.
355 -- We should ensure all non-rejected timecards
356 -- that overlap with this application periods
357 -- are changed to rejected.
358 --
359
360 cursor c_timecards
361 (p_ap_id in hxc_tc_ap_links.application_period_id%type) is
362 select ts.timecard_id
363 from hxc_tc_ap_links lnk, hxc_timecard_summary ts
364 where application_period_id = p_ap_id
365 and ts.timecard_id = lnk.timecard_id
366 and ts.approval_status <> hxc_timecard.c_rejected_status;
367
368 Begin
369
370 for tc_rec in c_timecards(p_application_period_id) loop
371 hxc_timecard_summary_pkg.reject_timecard(tc_rec.timecard_id);
372 end loop;
373
374 End reject_timecards;
375
376 Procedure submit_timecards
377 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
378
379 cursor c_timecards_to_check
380 (p_id in hxc_app_period_summary.application_period_id%type) is
381 select tc1.timecard_id ,tc1.approval_status
382 from hxc_timecard_summary tc1, hxc_tc_ap_links lnk1
383 where lnk1.application_period_id = p_id
384 and lnk1.timecard_id = tc1.timecard_id
385 and tc1.approval_status <> hxc_timecard.c_submitted_status;
386
387 cursor c_any_reject_app_periods
388 (p_id in hxc_tc_ap_links.timecard_id%type) is
389 select asm.approval_status
390 from hxc_tc_ap_links lnk1, hxc_app_period_summary asm
391 where lnk1.timecard_id = p_id
392 and lnk1.application_period_id = asm.application_period_id
393 and asm.approval_status = hxc_timecard.c_rejected_status;
394
395 l_approval_status hxc_app_period_summary.approval_status%type;
396
397 Begin
398
399 for tc_check in c_timecards_to_check(p_application_period_id) loop
400 if(tc_check.approval_status = hxc_timecard.c_approved_status) then
401 --
402 -- It doesn't matter about the other approval periods
403 -- just set the timecards to submitted.
404 --
405 hxc_timecard_summary_pkg.submit_timecard(tc_check.timecard_id);
406 elsif(tc_check.approval_status = hxc_timecard.c_rejected_status) then
407 --
408 -- It must be rejected, therefore we should check to see if we
409 -- can upgrade to submitted status (since we only look for APPROVED
410 -- or REJECTED timecards - SUBMITTED timecards do not have to change)
411 --
412 open c_any_reject_app_periods(tc_check.timecard_id);
413 fetch c_any_reject_app_periods into l_approval_status;
414 if (c_any_reject_app_periods%notfound) then
415 --
416 -- There are no more REJECTED app periods update to
417 -- submitted.
418 hxc_timecard_summary_pkg.submit_timecard(tc_check.timecard_id);
419 end if;
420 close c_any_reject_app_periods;
421 else
422 -- Timecard status should not be changed if it is submitted or
423 -- working or error.
424 null;
425 end if;
426
427 end loop;
428
429 End submit_timecards;
430
431 Procedure approve_timecards
432 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
433
434 cursor c_timecards_to_check
435 (p_id in hxc_app_period_summary.application_period_id%type) is
436 select tc1.timecard_id
437 from hxc_timecard_summary tc1, hxc_tc_ap_links lnk1
438 where lnk1.application_period_id = p_id
439 and lnk1.timecard_id = tc1.timecard_id
440 and tc1.approval_status = hxc_timecard.c_submitted_status;
441
442 cursor c_any_non_approved_app_periods
443 (p_timecard_id in hxc_timecard_summary.timecard_id%type) is
444 select asm.approval_status
445 from hxc_app_period_summary asm, hxc_tc_ap_links lnk1
446 where lnk1.timecard_id = p_timecard_id
447 and lnk1.application_period_id = asm.application_period_id
448 and asm.approval_status <> hxc_timecard.c_approved_status
449 order by 1 asc;
450
451 l_dummy_approval hxc_app_period_summary.approval_status%type;
452
453 Begin
454
455 for tc_check in c_timecards_to_check(p_application_period_id) loop
456 --
457 -- Check for any non-approved application periods for this
458 -- application period to see if we can approve this timecard
459 --
460 open c_any_non_approved_app_periods(tc_check.timecard_id);
461 fetch c_any_non_approved_app_periods into l_dummy_approval;
462 if (c_any_non_approved_app_periods%notfound) then
463 close c_any_non_approved_app_periods;
464 --
465 -- We can appprove this timecard!
466 --
467 hxc_timecard_summary_pkg.approve_timecard(tc_check.timecard_id);
468 else
469 close c_any_non_approved_app_periods;
470 if(l_dummy_approval = hxc_timecard.c_rejected_status) then
471 hxc_timecard_summary_pkg.reject_timecard(tc_check.timecard_id);
472 end if;
473 end if;
474 end loop;
475
476 End approve_timecards;
477
478 Procedure reevaluate_timecard_statuses
479 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
480
481 cursor c_app_period_info
482 (p_ap_id in hxc_app_period_summary.application_period_id%type) is
483 select approval_status
484 from hxc_app_period_summary
485 where application_period_id = p_ap_id;
486
487 l_approval_status hxc_app_period_summary.approval_status%type;
488
489 Begin
490
491 open c_app_period_info(p_application_period_id);
492 fetch c_app_period_info into l_approval_status;
493 if (c_app_period_info%found) then
494
495 if(l_approval_status = hxc_timecard.c_rejected_status) then
496 --
497 -- It's easy, reject all timecards associated with this app period
498 --
499 reject_timecards(p_application_period_id);
500 elsif(l_approval_status = hxc_timecard.c_submitted_status) then
501 --
502 -- Check for submission status
503 --
504 submit_timecards(p_application_period_id);
505 elsif(l_approval_status = hxc_timecard.c_approved_status) then
506 --
507 -- See if we can approve some timecards!
508 --
509 approve_timecards(p_application_period_id);
510
511 end if;
512
513 end if;
514 close c_app_period_info;
515
516 End reevaluate_timecard_statuses;
517
518 end hxc_timecard_summary_api;
[Home] [Help]