[Home] [Help]
PACKAGE BODY: APPS.HXC_TIME_APPROVAL_INFO
Source
1 Package body hxc_time_approval_info AS
2 /* $Header: hxctcapinfo.pkb 115.2 2004/07/05 02:36:47 dragarwa noship $ */
3
4 g_timecard_info timecard_info;
5 g_application_info application_info_table;
6
7 procedure clearCache is
8 begin
9
10 g_timecard_info.timecard_id := null;
11 g_timecard_info.approval_status := null;
12 g_timecard_info.submission_date := null;
13 g_timecard_info.audit_data_exists := null;
14 g_timecard_info.recorded_hours := null;
15 g_application_info.delete;
16
17 end clearCache;
18
19 procedure createApplicationCache
20 (p_timecard_id in hxc_timecard_summary.timecard_id%type) is
21
22 cursor c_ap_cache_info
23 (p_tc_id in hxc_tc_ap_links.timecard_id%type) is
24 select aps.application_period_id,
25 aps.approval_status,
26 aps.creation_date,
27 aps.notification_status,
28 aps.approver_id,
29 aps.time_recipient_id
30 from hxc_tc_ap_links tcl,
31 hxc_app_period_summary aps
32 where tcl.timecard_id = p_tc_id
33 and tcl.application_period_id = aps.application_period_id;
34
35 l_dummy_name varchar2(360);
36
37 begin
38
39 for app_rec in c_ap_cache_info(p_timecard_id) loop
40 g_application_info(app_rec.application_period_id).time_recipient_id :=
41 app_rec.time_recipient_id;
42 g_application_info(app_rec.application_period_id).approval_status :=
43 app_rec.approval_status;
44 g_application_info(app_rec.application_period_id).creation_date :=
45 app_rec.creation_date;
46 g_application_info(app_rec.application_period_id).notification_status :=
47 app_rec.notification_status;
48 wf_directory.getusername
49 (p_orig_system => 'PER',
50 p_orig_system_id => app_rec.approver_id,
51 p_name => l_dummy_name,
52 p_display_name => g_application_info(app_rec.application_period_id).approver
53 );
54 end loop;
55
56 end createApplicationCache;
57
58 function findApprovalDate
59 (p_timecard_id in hxc_timecard_summary.timecard_id%type)
60 return hxc_timecard_summary.submission_date%type is
61
62 cursor c_approval_date
63 (p_timecard_id in hxc_timecard_summary.timecard_id%type) is
64 select max(aps.creation_date)
65 from hxc_tc_ap_links tcl, hxc_app_period_summary aps
66 where tcl.timecard_id = p_timecard_id
67 and aps.approval_status = 'APPROVED'
68 and aps.application_period_id = tcl.application_period_id;
69
70 l_approval_date date;
71
72 begin
73 open c_approval_date(p_timecard_id);
74 fetch c_approval_date into l_approval_date;
75 if(c_approval_date%notfound) then
76 l_approval_date := null;
77 end if;
78 close c_approval_date;
79
80 return l_approval_date;
81 end findApprovalDate;
82
83 procedure createTimecardCache
84 (p_timecard_id in hxc_timecard_summary.timecard_id%type) is
85
86 cursor c_tc_cache_info
87 (p_tc_id in hxc_timecard_summary.timecard_id%type) is
88 select ts.timecard_id,
89 ts.approval_status,
90 ts.recorded_hours,
91 ts.has_reasons,
92 ts.submission_date
93 from hxc_timecard_summary ts
94 where ts.timecard_id = p_tc_id;
95
96 begin
97
98 open c_tc_cache_info(p_timecard_id);
99 fetch c_tc_cache_info into
100 g_timecard_info.timecard_id,
101 g_timecard_info.approval_status,
102 g_timecard_info.recorded_hours,
103 g_timecard_info.audit_data_exists,
104 g_timecard_info.submission_date;
105 close c_tc_cache_info;
106 if(g_timecard_info.approval_status = 'APPROVED') then
107 g_timecard_info.approval_date :=
108 findApprovalDate(p_timecard_id);
109 else
110 g_timecard_info.approval_date := null;
111 end if;
112 end createTimecardCache;
113
114 function findTimeCategoryId
115 (p_time_category_name in hxc_time_categories.time_category_name%type)
116 return hxc_time_categories.time_category_id%type is
117
118 cursor c_tc_id
119 (p_name in hxc_time_categories.time_category_name%type) is
120 select time_category_id
121 from hxc_time_categories
122 where time_category_name = p_name;
123
124 l_tc_id hxc_time_categories.time_category_id%type;
125
126 begin
127
128 if(p_time_category_name is not null) then
129 open c_tc_id(p_time_category_name);
130 fetch c_tc_id into l_tc_id;
131 if(c_tc_id%notfound) then
132 close c_tc_id;
133 fnd_message.set_name('HXC','HXC_NO_TIME_CATEGORY');
134 fnd_message.set_token('NAME',p_time_category_name);
135 fnd_message.raise_error;
136 else
137 close c_tc_id;
138 end if;
139 else
140 l_tc_id := null;
141 end if;
142
143 return l_tc_id;
144
145 end findTimeCategoryId;
146
147 function findTimeRecipientId
148 (p_time_recipient_name in hxc_time_recipients.name%type)
149 return hxc_time_recipients.time_recipient_id%type is
150
151 cursor c_find_tr_id
152 (p_name in hxc_time_recipients.name%type) is
153 select time_recipient_id
154 from hxc_time_recipients
155 where name = p_name;
156
157 l_tr_id hxc_time_recipients.time_recipient_id%type;
158
159 begin
160
161 open c_find_tr_id(p_time_recipient_name);
162 fetch c_find_tr_id into l_tr_id;
163 if(c_find_tr_id%notfound) then
164 close c_find_tr_id;
165 fnd_message.set_name('HXC','HXC_NO_TIME_RECIPIENT');
166 fnd_message.set_token('NAME',p_time_recipient_name);
167 fnd_message.raise_error;
168 else
169 close c_find_tr_id;
170 end if;
171 return l_tr_id;
172
173 end findTimeRecipientId;
174
175 function findAppPeriodId
176 (p_resource_id in hxc_app_period_summary.resource_id%type,
177 p_start_time in hxc_app_period_summary.start_time%type,
178 p_stop_time in hxc_app_period_summary.stop_time%type,
179 p_application_name in hxc_time_recipients.name%type,
180 p_time_category_name in hxc_time_categories.time_category_name%type)
181 return hxc_app_period_summary.application_period_id%type is
182
183 cursor c_find_app_period
184 (p_resource_id in hxc_app_period_summary.resource_id%type,
185 p_start_time in hxc_app_period_summary.start_time%type,
186 p_stop_time in hxc_app_period_summary.stop_time%type,
187 p_time_recipient_id in hxc_app_period_summary.time_recipient_id%type,
188 p_time_category_id in hxc_app_period_summary.time_category_id%type) is
189 select application_period_id
190 from hxc_app_period_summary
191 where resource_id = p_resource_id
192 and trunc(start_time) = trunc(p_start_time)
193 and trunc(stop_time) = trunc(p_stop_time)
194 and time_recipient_id = p_time_recipient_id
195 and nvl(time_category_id,-1) = nvl(p_time_category_id,-1);
196
197 l_time_recipient_id hxc_time_recipients.time_recipient_id%type;
198 l_time_category_id hxc_time_categories.time_category_id%type;
199 l_application_period_id hxc_app_period_summary.application_period_id%type;
200
201 begin
202
203 l_time_recipient_id := findTimeRecipientId(p_application_name);
204 l_time_category_id := findTimeCategoryId(p_time_category_name);
205
206 open c_find_app_period
207 (p_resource_id,
208 p_start_time,
209 p_stop_time,
210 l_time_recipient_id,
211 l_time_category_id);
212 fetch c_find_app_period into l_application_period_id;
213 if(c_find_app_period%notfound) then
214 close c_find_app_period;
215 fnd_message.set_name('HXC','HXC_NO_APP_PERIOD');
216 fnd_message.raise_error;
217 else
218 close c_find_app_period;
219 end if;
220 return l_application_period_id;
221
222 end findAppPeriodId;
223
224 function findTimecardId
225 (p_application_period_id in hxc_app_period_summary.application_period_id%type)
226 return hxc_timecard_summary.timecard_id%type is
227
228 cursor c_find_timecard_id
229 (p_app_period_id in hxc_app_period_summary.application_period_id%type) is
230 select timecard_id
231 from hxc_tc_ap_links
232 where application_period_id = p_app_period_id;
233
234 l_timecard_id hxc_timecard_summary.timecard_id%type;
235
236 begin
237
238 open c_find_timecard_id(p_application_period_id);
239 fetch c_find_timecard_id into l_timecard_id;
240 if(c_find_timecard_id%notfound) then
241 close c_find_timecard_id;
242 fnd_message.set_name('HXC','HXC_NO_TIMECARD_ID');
243 fnd_message.raise_error;
244 else
245 close c_find_timecard_id;
246 end if;
247 return l_timecard_id;
248
249 end findTimecardId;
250
251 function findTimecardId
252 (p_resource_id in hxc_timecard_summary.resource_id%type,
253 p_start_time in hxc_timecard_summary.start_time%type,
254 p_stop_time in hxc_timecard_summary.stop_time%type)
255 return hxc_timecard_summary.timecard_id%type is
256
257 cursor c_find_timecard_id
258 (p_resource_id in hxc_timecard_summary.resource_id%type,
259 p_start_time in hxc_timecard_summary.start_time%type,
260 p_stop_time in hxc_timecard_summary.stop_time%type) is
261 select timecard_id
262 from hxc_timecard_summary
263 where resource_id = p_resource_id
264 and trunc(start_time) = trunc(p_start_time)
265 and trunc(stop_time) = trunc(p_stop_time);
266
267 l_timecard_id hxc_timecard_summary.timecard_id%type;
268
269 begin
270
271 open c_find_timecard_id(p_resource_id,p_start_time,p_stop_time);
272 fetch c_find_timecard_id into l_timecard_id;
273 if(c_find_timecard_id%notfound) then
274 close c_find_timecard_id;
275 fnd_message.set_name('HXC','HXC_NO_TIMECARD_ID');
276 fnd_message.raise_error;
277 else
278 close c_find_timecard_id;
279 end if;
280 return l_timecard_id;
281
282 end findTimecardId;
283
284
285 function verifyCache
286 (p_timecard_id in hxc_timecard_summary.timecard_id%type)
287 return boolean is
288
289 begin
290
291 if(g_timecard_info.timecard_id = p_timecard_id) then
292 return true;
293 else
294 return false;
295 end if;
296
297 end verifyCache;
298
299 procedure verifyOrCreateCache
300 (p_timecard_id in hxc_timecard_summary.timecard_id%type,
301 p_application_period_id in hxc_app_period_summary.application_period_id%type) is
302
303 l_timecard_id hxc_timecard_summary.timecard_id%type;
304
305 begin
306
307 l_timecard_id := p_timecard_id;
308
309 if((p_timecard_id is null) and (p_application_period_id is not null)) then
310 l_timecard_id := findTimecardId(p_application_period_id);
311 end if;
312
313 if (NOT verifyCache(p_timecard_id)) then
314
315 clearCache;
316 createTimecardCache(p_timecard_id);
317 if(p_application_period_id is not null) then
318 createApplicationCache(p_timecard_id);
319 end if;
320
321 end if;
322
323 end verifyOrCreateCache;
324
325 --
326 -- Public interfaces below
327 --
328
329 function get_timecard_approval_status
330 (p_timecard_id in hxc_timecard_summary.timecard_id%type)
331 return hxc_timecard_summary.approval_status%type is
332
333 begin
334 verifyOrCreateCache(p_timecard_id,null);
335 return g_timecard_info.approval_status;
336 end get_timecard_approval_status;
337
338 function get_timecard_approval_status
339 (p_resource_id in hxc_timecard_summary.resource_id%type,
340 p_start_time in hxc_timecard_summary.start_time%type,
341 p_stop_time in hxc_timecard_summary.stop_time%type)
342 return hxc_timecard_summary.approval_status%type is
343
344 begin
345 verifyOrCreateCache
346 (findTimecardId
347 (p_resource_id,
348 p_start_time,
349 p_stop_time
350 ),
351 null
352 );
353 return g_timecard_info.approval_status;
354 end get_timecard_approval_status;
355
356 function get_timecard_approval_date
357 (p_timecard_id in hxc_timecard_summary.timecard_id%type)
358 return hxc_timecard_summary.submission_date%type is
359
360 begin
361 verifyOrCreateCache(p_timecard_id,null);
362 return g_timecard_info.approval_date;
363 end get_timecard_approval_date;
364
365 function get_timecard_approval_date
366 (p_resource_id in hxc_timecard_summary.resource_id%type,
367 p_start_time in hxc_timecard_summary.start_time%type,
368 p_stop_time in hxc_timecard_summary.stop_time%type)
369 return hxc_timecard_summary.submission_date%type is
370
371 begin
372 verifyOrCreateCache
373 (findTimecardId
374 (p_resource_id,
375 p_start_time,
376 p_stop_time
377 ),
378 null
379 );
380 return g_timecard_info.approval_date;
381 end get_timecard_approval_date;
382
383 function get_timecard_recorded_hours
384 (p_timecard_id in hxc_timecard_summary.timecard_id%type)
385 return hxc_timecard_summary.recorded_hours%type is
386
387 begin
388 verifyOrCreateCache(p_timecard_id,null);
389 return g_timecard_info.recorded_hours;
390 end get_timecard_recorded_hours;
391
392 function get_timecard_recorded_hours
393 (p_resource_id in hxc_timecard_summary.resource_id%type,
394 p_start_time in hxc_timecard_summary.start_time%type,
395 p_stop_time in hxc_timecard_summary.stop_time%type)
396 return hxc_timecard_summary.recorded_hours%type is
397
398 begin
399 verifyOrCreateCache
400 (findTimecardId
401 (p_resource_id,
402 p_start_time,
403 p_stop_time
404 ),
405 null
406 );
407 return g_timecard_info.recorded_hours;
408 end get_timecard_recorded_hours;
409
410 function get_timecard_audit_data_exists
411 (p_timecard_id in hxc_timecard_summary.timecard_id%type)
412 return hxc_timecard_summary.has_reasons%type is
413
414 begin
415 verifyOrCreateCache(p_timecard_id, null);
416 return g_timecard_info.audit_data_exists;
417 end get_timecard_audit_data_exists;
418
419 function get_timecard_audit_data_exists
420 (p_resource_id in hxc_timecard_summary.resource_id%type,
421 p_start_time in hxc_timecard_summary.start_time%type,
422 p_stop_time in hxc_timecard_summary.stop_time%type)
423 return hxc_timecard_summary.has_reasons%type is
424
428 (p_resource_id,
425 begin
426 verifyOrCreateCache
427 (findTimecardId
429 p_start_time,
430 p_stop_time
431 ),
432 null
433 );
434 return g_timecard_info.audit_data_exists;
435 end get_timecard_audit_data_exists;
436
437 function get_timecard_submission_date
438 (p_timecard_id in hxc_timecard_summary.timecard_id%type)
439 return hxc_timecard_summary.submission_date%type is
440
441 begin
442 verifyOrCreateCache(p_timecard_id,null);
443 return g_timecard_info.submission_date;
444 end get_timecard_submission_date;
445
446 function get_timecard_submission_date
447 (p_resource_id in hxc_timecard_summary.resource_id%type,
448 p_start_time in hxc_timecard_summary.start_time%type,
449 p_stop_time in hxc_timecard_summary.stop_time%type)
450 return hxc_timecard_summary.submission_date%type is
451
452 begin
453 verifyOrCreateCache
454 (findTimecardId
455 (p_resource_id,
456 p_start_time,
457 p_stop_time
458 ),
459 null
460 );
461 return g_timecard_info.submission_date;
462 end get_timecard_submission_date;
463
464 function get_app_approval_status
465 (p_application_period_id in hxc_app_period_summary.application_period_id%type)
466 return hxc_app_period_summary.approval_status%type is
467
468 begin
469 verifyOrCreateCache(findTimecardId(p_application_period_id),p_application_period_id);
470 return g_application_info(p_application_period_id).approval_status;
471 end get_app_approval_status;
472
473 function get_app_approval_status
474 (p_resource_id in hxc_app_period_summary.resource_id%type,
475 p_start_time in hxc_app_period_summary.start_time%type,
476 p_stop_time in hxc_app_period_summary.stop_time%type,
477 p_application_name in hxc_time_recipients.name%type
478 )
479 return hxc_app_period_summary.approval_status%type is
480
481 begin
482 return get_app_approval_status(p_resource_id,p_start_time,p_stop_time,p_application_name,null);
483 end get_app_approval_status;
484
485 function get_app_approval_status
486 (p_resource_id in hxc_app_period_summary.resource_id%type,
487 p_start_time in hxc_app_period_summary.start_time%type,
488 p_stop_time in hxc_app_period_summary.stop_time%type,
489 p_application_name in hxc_time_recipients.name%type,
490 p_time_category_name in hxc_time_categories.time_category_name%type
491 )
492 return hxc_app_period_summary.approval_status%type is
493
494 l_application_period_id hxc_app_period_summary.application_period_id%type;
495
496 begin
497 l_application_period_id := findAppPeriodId
498 (p_resource_id,
499 p_start_time,
500 p_stop_time,
501 p_application_name,
502 p_time_category_name);
503 verifyOrCreateCache(findTimecardId(l_application_period_id),l_application_period_id);
504 return g_application_info(l_application_period_id).approval_status;
505 end get_app_approval_status;
506
507 function get_app_creation_date
508 (p_application_period_id in hxc_app_period_summary.application_period_id%type)
509 return hxc_app_period_summary.creation_date%type is
510
511 begin
512 verifyOrCreateCache(findTimecardId(p_application_period_id), p_application_period_id);
513 return g_application_info(p_application_period_id).creation_date;
514 end get_app_creation_date;
515
516 function get_app_creation_date
517 (p_resource_id in hxc_app_period_summary.resource_id%type,
518 p_start_time in hxc_app_period_summary.start_time%type,
519 p_stop_time in hxc_app_period_summary.stop_time%type,
520 p_application_name in hxc_time_recipients.name%type
521 )
522 return hxc_app_period_summary.creation_date%type is
523
524 begin
525 return get_app_creation_date
526 (p_resource_id,
527 p_start_time,
528 p_stop_time,
529 p_application_name,
530 null
531 );
532 end get_app_creation_date;
533
534 function get_app_creation_date
535 (p_resource_id in hxc_app_period_summary.resource_id%type,
536 p_start_time in hxc_app_period_summary.start_time%type,
537 p_stop_time in hxc_app_period_summary.stop_time%type,
538 p_application_name in hxc_time_recipients.name%type,
539 p_time_category_name in hxc_time_categories.time_category_name%type
540 )
541 return hxc_app_period_summary.creation_date%type is
542 l_application_period_id hxc_app_period_summary.application_period_id%type;
543 begin
544 l_application_period_id := findAppPeriodId
545 (p_resource_id,
546 p_start_time,
547 p_stop_time,
548 p_application_name,
549 p_time_category_name);
550 verifyOrCreateCache(findTimecardId(l_application_period_id),l_application_period_id);
551 return g_application_info(l_application_period_id).creation_date;
552 end get_app_creation_date;
553
554 function get_app_approver
555 (p_application_period_id in hxc_app_period_summary.application_period_id%type)
556 return varchar2 is
557 begin
558 verifyOrCreateCache(findTimecardId(p_application_period_id),p_application_period_id);
559 return g_application_info(p_application_period_id).approver;
560 end get_app_approver;
561
562 function get_app_approver
563 (p_resource_id in hxc_app_period_summary.resource_id%type,
564 p_start_time in hxc_app_period_summary.start_time%type,
565 p_stop_time in hxc_app_period_summary.stop_time%type,
566 p_application_name in hxc_time_recipients.name%type
567 )
568 return varchar2 is
569
570 begin
571 return get_app_approver
572 (p_resource_id,
573 p_start_time,
574 p_stop_time,
578 end get_app_approver;
575 p_application_name,
576 null
577 );
579
580 function get_app_approver
581 (p_resource_id in hxc_app_period_summary.resource_id%type,
582 p_start_time in hxc_app_period_summary.start_time%type,
583 p_stop_time in hxc_app_period_summary.stop_time%type,
584 p_application_name in hxc_time_recipients.name%type,
585 p_time_category_name in hxc_time_categories.time_category_name%type
586 )
587 return varchar2 is
588 l_application_period_id hxc_app_period_summary.application_period_id%type;
589 begin
590 l_application_period_id := findAppPeriodId
591 (p_resource_id,
592 p_start_time,
593 p_stop_time,
594 p_application_name,
595 p_time_category_name);
596 verifyOrCreateCache(findTimecardId(l_application_period_id),l_application_period_id);
597 return g_application_info(l_application_period_id).approver;
598 end get_app_approver;
599
600 END hxc_time_approval_info;