1 package body hxc_timecard_summary_api as
2 /* $Header: hxctcsumapi.pkb 120.0.12010000.3 2008/08/05 12:06:35 ubhat 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 end if;
233 end if;
234
235 l_index := p_blocks.next(l_index);
236 End Loop;
237
238 End cleanup_timecards;
239
240 procedure timecard_deposit
241 (p_blocks in hxc_block_table_type
242 ,p_mode in varchar2 default hxc_timecard_summary_pkg.c_normal_mode
243 ,p_approval_item_type in varchar2
244 ,p_approval_process_name in varchar2
245 ,p_approval_item_key in varchar2
246 ,p_tk_audit_item_type in varchar2
247 ,p_tk_audit_process_name in varchar2
248 ,p_tk_audit_item_key in varchar2
249 ) IS
250
251 l_timecard_index number;
252
253 Begin
254 --
255 -- 1. Find the timecard index of the blocks
256 --
257 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
258 --
259 -- 2. Clean up if this is an existing timecard
260 --
261 cleanup_timecards(p_blocks);
262 --
263 -- 3. Create timecard summary info
264 --
265 hxc_timecard_summary_pkg.insert_summary_row
266 (p_timecard_id => p_blocks(l_timecard_index).time_building_block_id
267 ,p_mode => p_mode
268 ,p_approval_item_type => p_approval_item_type
269 ,p_approval_process_name => p_approval_process_name
270 ,p_approval_item_key => p_approval_item_key
271 ,p_tk_audit_item_type => p_tk_audit_item_type
272 ,p_tk_audit_process_name => p_tk_audit_process_name
273 ,p_tk_audit_item_key => p_tk_audit_item_key
274 );
275 --
276 -- 4. Create the link information if the
277 -- mode is migration
278 --
279 if(p_mode = hxc_timecard_summary_pkg.c_migration_mode) then
280 hxc_tc_ap_links_pkg.create_timecard_links
281 (p_timecard_id => p_blocks(l_timecard_index).time_building_block_id);
282 end if;
283 End timecard_deposit;
284
285 Procedure timecard_deposit
286 (p_timecard_id in hxc_timecard_summary.timecard_id%type
287 ,p_mode in varchar2 default hxc_timecard_summary_pkg.c_normal_mode
288 ,p_approval_item_type in varchar2
289 ,p_approval_process_name in varchar2
290 ,p_approval_item_key in varchar2
291 ,p_tk_audit_item_type in varchar2
292 ,p_tk_audit_process_name in varchar2
293 ,p_tk_audit_item_key in varchar2
294 ) is
295
296 Begin
297 --
298 -- 1. Clean up if this is an existing timecard
299 -- NOTE: This version is called on migration, and we
300 -- absolutely don't want to call cleanup_timecards
301 -- on migration.
302
303 delete_timecard(p_timecard_id, p_mode);
304 --
305 -- 3. Create timecard summary info
306 --
307 hxc_timecard_summary_pkg.insert_summary_row
308 (p_timecard_id => p_timecard_id
309 ,p_mode => p_mode
310 ,p_approval_item_type => p_approval_item_type
311 ,p_approval_process_name => p_approval_process_name
312 ,p_approval_item_key => p_approval_item_key
313 ,p_tk_audit_item_type => p_tk_audit_item_type
314 ,p_tk_audit_process_name => p_tk_audit_process_name
315 ,p_tk_audit_item_key => p_tk_audit_item_key
316 );
317
318 End timecard_deposit;
319
320 Procedure timecard_delete
321 (p_blocks in hxc_block_table_type) is
322
323 l_timecard_index number;
324
325 Begin
326
327 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
328
329 delete_timecard(p_blocks,l_timecard_index, hxc_timecard_summary_pkg.c_normal_mode);
330
331 End timecard_delete;
332
333 Procedure timecard_delete
334 (p_timecard_id in hxc_timecard_summary.timecard_id%type) is
335
336 Begin
337
338 delete_timecard(p_timecard_id, hxc_timecard_summary_pkg.c_normal_mode);
339
340 End timecard_delete;
341
342 procedure reject_timecards
343 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
344 --
345 -- In this case we have a rejection.
346 -- We should ensure all non-rejected timecards
347 -- that overlap with this application periods
348 -- are changed to rejected.
349 --
350
351 cursor c_timecards
352 (p_ap_id in hxc_tc_ap_links.application_period_id%type) is
353 select ts.timecard_id
354 from hxc_tc_ap_links lnk, hxc_timecard_summary ts
355 where application_period_id = p_ap_id
356 and ts.timecard_id = lnk.timecard_id
357 and ts.approval_status <> hxc_timecard.c_rejected_status;
358
359 Begin
360
361 for tc_rec in c_timecards(p_application_period_id) loop
362 hxc_timecard_summary_pkg.reject_timecard(tc_rec.timecard_id);
363 end loop;
364
365 End reject_timecards;
366
367 Procedure submit_timecards
368 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
369
370 cursor c_timecards_to_check
371 (p_id in hxc_app_period_summary.application_period_id%type) is
372 select tc1.timecard_id ,tc1.approval_status
373 from hxc_timecard_summary tc1, hxc_tc_ap_links lnk1
374 where lnk1.application_period_id = p_id
375 and lnk1.timecard_id = tc1.timecard_id
376 and tc1.approval_status <> hxc_timecard.c_submitted_status;
377
378 cursor c_any_reject_app_periods
379 (p_id in hxc_tc_ap_links.timecard_id%type) is
380 select asm.approval_status
381 from hxc_tc_ap_links lnk1, hxc_app_period_summary asm
382 where lnk1.timecard_id = p_id
383 and lnk1.application_period_id = asm.application_period_id
384 and asm.approval_status = hxc_timecard.c_rejected_status;
385
386 l_approval_status hxc_app_period_summary.approval_status%type;
387
388 Begin
389
390 for tc_check in c_timecards_to_check(p_application_period_id) loop
391 if(tc_check.approval_status = hxc_timecard.c_approved_status) then
392 --
393 -- It doesn't matter about the other approval periods
394 -- just set the timecards to submitted.
395 --
396 hxc_timecard_summary_pkg.submit_timecard(tc_check.timecard_id);
397 elsif(tc_check.approval_status = hxc_timecard.c_rejected_status) then
398 --
399 -- It must be rejected, therefore we should check to see if we
400 -- can upgrade to submitted status (since we only look for APPROVED
401 -- or REJECTED timecards - SUBMITTED timecards do not have to change)
402 --
403 open c_any_reject_app_periods(tc_check.timecard_id);
404 fetch c_any_reject_app_periods into l_approval_status;
405 if (c_any_reject_app_periods%notfound) then
406 --
407 -- There are no more REJECTED app periods update to
408 -- submitted.
409 hxc_timecard_summary_pkg.submit_timecard(tc_check.timecard_id);
410 end if;
411 close c_any_reject_app_periods;
412 else
413 -- Timecard status should not be changed if it is submitted or
414 -- working or error.
415 null;
416 end if;
417
418 end loop;
419
420 End submit_timecards;
421
422 Procedure approve_timecards
423 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
424
425 cursor c_timecards_to_check
426 (p_id in hxc_app_period_summary.application_period_id%type) is
427 select tc1.timecard_id
428 from hxc_timecard_summary tc1, hxc_tc_ap_links lnk1
429 where lnk1.application_period_id = p_id
430 and lnk1.timecard_id = tc1.timecard_id
431 and tc1.approval_status = hxc_timecard.c_submitted_status;
432
433 cursor c_any_non_approved_app_periods
434 (p_timecard_id in hxc_timecard_summary.timecard_id%type) is
435 select asm.approval_status
436 from hxc_app_period_summary asm, hxc_tc_ap_links lnk1
437 where lnk1.timecard_id = p_timecard_id
438 and lnk1.application_period_id = asm.application_period_id
439 and asm.approval_status <> hxc_timecard.c_approved_status
440 order by 1 asc;
441
442 l_dummy_approval hxc_app_period_summary.approval_status%type;
443
444 Begin
445
446 for tc_check in c_timecards_to_check(p_application_period_id) loop
447 --
448 -- Check for any non-approved application periods for this
449 -- application period to see if we can approve this timecard
450 --
451 open c_any_non_approved_app_periods(tc_check.timecard_id);
452 fetch c_any_non_approved_app_periods into l_dummy_approval;
453 if (c_any_non_approved_app_periods%notfound) then
454 close c_any_non_approved_app_periods;
455 --
456 -- We can appprove this timecard!
457 --
458 hxc_timecard_summary_pkg.approve_timecard(tc_check.timecard_id);
459 else
460 close c_any_non_approved_app_periods;
461 if(l_dummy_approval = hxc_timecard.c_rejected_status) then
462 hxc_timecard_summary_pkg.reject_timecard(tc_check.timecard_id);
463 end if;
464 end if;
465 end loop;
466
467 End approve_timecards;
468
469 Procedure reevaluate_timecard_statuses
470 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
471
472 cursor c_app_period_info
473 (p_ap_id in hxc_app_period_summary.application_period_id%type) is
474 select approval_status
475 from hxc_app_period_summary
476 where application_period_id = p_ap_id;
477
478 l_approval_status hxc_app_period_summary.approval_status%type;
479
480 Begin
481
482 open c_app_period_info(p_application_period_id);
483 fetch c_app_period_info into l_approval_status;
484 if (c_app_period_info%found) then
485
486 if(l_approval_status = hxc_timecard.c_rejected_status) then
487 --
488 -- It's easy, reject all timecards associated with this app period
489 --
490 reject_timecards(p_application_period_id);
491 elsif(l_approval_status = hxc_timecard.c_submitted_status) then
492 --
493 -- Check for submission status
494 --
495 submit_timecards(p_application_period_id);
496 elsif(l_approval_status = hxc_timecard.c_approved_status) then
497 --
498 -- See if we can approve some timecards!
499 --
500 approve_timecards(p_application_period_id);
501
502 end if;
503
504 end if;
505 close c_app_period_info;
506
507 End reevaluate_timecard_statuses;
508
509 end hxc_timecard_summary_api;
[Home] [Help]