1 PACKAGE BODY AP_WEB_AUDIT_LIST_PVT AS
2 /* $Header: apwvallb.pls 115.2 2004/07/02 08:56:06 jrautiai noship $ */
3
4 /*========================================================================
5 | PUBLIC FUNCTION get_date_range
6 |
7 | DESCRIPTION
8 | This function returns a date range type populated with the values
9 | passed in as parameters.
10 |
11 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
12 | audit list API
13 |
14 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
15 |
16 | RETURNS
17 | Date range type populated with the values passed as parameters.
18 |
19 | PARAMETERS
20 | p_date1 IN Start date of the range.
21 | p_date2 IN End date of the range.
22 | p_audit_reason_code OUT Audit reason for the date range
23 |
24 | MODIFICATION HISTORY
25 | Date Author Description of Changes
26 | 28-Jun-2004 J Rautiainen Created
27 |
28 *=======================================================================*/
29 FUNCTION get_date_range(p_date1 IN DATE,
30 p_date2 IN DATE,
31 p_audit_reason_code IN VARCHAR2) RETURN Date_Range_Type IS
32 result Date_Range_Type;
33 BEGIN
34 result.start_date := trunc(NVL(p_date1,c_min_date));
35 result.end_date := trunc(NVL(p_date2,c_max_date));
36 result.audit_reason_code := p_audit_reason_code;
37 return result;
38 END get_date_range;
39
40 /*========================================================================
41 | PUBLIC FUNCTION get_date_range
42 |
43 | DESCRIPTION
44 | This function returns a date range type populated with the values
45 | passed in as parameters.
46 |
47 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
48 | audit list API
49 |
50 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
51 |
52 | RETURNS
53 | Date range type populated with the values passed as parameters.
54 |
55 | PARAMETERS
56 | p_audit_rec IN Audit record the API was called with.
57 |
58 | MODIFICATION HISTORY
59 | Date Author Description of Changes
60 | 28-Jun-2004 J Rautiainen Created
61 |
62 *=======================================================================*/
63 FUNCTION get_date_range(p_audit_rec IN AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type) RETURN Date_Range_Type IS
64 BEGIN
65 return get_date_range(p_audit_rec.start_date, p_audit_rec.end_date, p_audit_rec.audit_reason_code);
66 END get_date_range;
67
68 /*========================================================================
69 | PUBLIC FUNCTION includes
70 |
71 | DESCRIPTION
72 | This function detects whether a date range includes a specific date.
73 | Equal dates are considered to be included.
74 |
75 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
76 | audit list API
77 |
78 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
79 |
80 | RETURNS
81 | Boolean indicating whether the date range includes the date.
82 |
83 | PARAMETERS
84 | p_date1_rec IN Date range which is checked.
85 | p_date2 IN Date which is checked.
86 |
87 | MODIFICATION HISTORY
88 | Date Author Description of Changes
89 | 28-Jun-2004 J Rautiainen Created
90 |
91 *=======================================================================*/
92 FUNCTION includes(p_date1_rec IN Date_Range_Type,
93 p_date2 IN DATE) RETURN BOOLEAN IS
94 BEGIN
95 IF ( before(p_date1_rec.start_date,p_date2)
96 AND after(p_date1_rec.end_date,p_date2))
97 OR p_date1_rec.start_date = p_date2
98 OR p_date1_rec.end_date = p_date2 THEN
99 RETURN TRUE;
100 ELSE
101 RETURN FALSE;
102 END IF;
103 END includes;
104
105 /*========================================================================
106 | PUBLIC FUNCTION includes
107 |
108 | DESCRIPTION
109 | This function detects whether a date range includes another date range.
110 | Equal dates are considered to be included.
111 |
112 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
113 | audit list API
114 |
115 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
116 |
117 | RETURNS
118 | Boolean indicating whether the date range includes the other date range.
119 |
120 | PARAMETERS
121 | p_date1_rec IN Date range which is checked whether includes.
122 | p_date2_rec IN Date range which is checked whether included.
123 |
124 | MODIFICATION HISTORY
125 | Date Author Description of Changes
126 | 28-Jun-2004 J Rautiainen Created
127 |
128 *=======================================================================*/
129 FUNCTION includes(p_date1_rec IN Date_Range_Type,
130 p_date2_rec IN Date_Range_Type) RETURN BOOLEAN IS
131 BEGIN
132 IF includes(p_date1_rec,p_date2_rec.start_date)
133 AND includes(p_date1_rec,p_date2_rec.end_date) THEN
134 RETURN TRUE;
135 ELSE
136 RETURN FALSE;
137 END IF;
138 END includes;
139
140 /*========================================================================
141 | PUBLIC FUNCTION continuous
142 |
143 | DESCRIPTION
144 | This function detects whether two date ranges are continuous eg.
145 | whether the later date range continues immediately after the other.
146 |
147 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
148 | audit list API
149 |
150 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
151 |
152 | RETURNS
153 | Boolean indicating whether the date ranges are continuous.
154 |
155 | PARAMETERS
156 | p_date1_rec IN Date range which is checked.
157 | p_date2_rec IN Date range which is checked.
158 |
159 | MODIFICATION HISTORY
160 | Date Author Description of Changes
161 | 28-Jun-2004 J Rautiainen Created
162 |
163 *=======================================================================*/
164 FUNCTION continuous(p_date1_rec IN Date_Range_Type,
165 p_date2_rec IN Date_Range_Type) RETURN BOOLEAN IS
166 BEGIN
167 IF NOT overlap(p_date1_rec,p_date2_rec) THEN
168 IF ( before(p_date1_rec.start_date,p_date2_rec.start_date)
169 AND p_date1_rec.end_date+1 = p_date2_rec.start_date)
170 OR
171 ( after(p_date1_rec.start_date,p_date2_rec.start_date)
172 AND p_date2_rec.end_date+1 = p_date1_rec.start_date) THEN
173 RETURN TRUE;
174 ELSE
175 RETURN FALSE;
176 END IF;
177 ELSE
178 RETURN FALSE;
179 END IF;
180 END continuous;
181
182 /*========================================================================
183 | PUBLIC FUNCTION open_date
184 |
185 | DESCRIPTION
186 | This function detects whether a date is a open date eg. NULL which is
187 | considered as infinite.
188 |
189 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
190 | audit list API
191 |
192 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
193 |
194 | RETURNS
195 | Boolean indicating whether the date is a open date.
196 |
197 | PARAMETERS
198 | p_date1 IN Date to be checked.
199 |
200 | MODIFICATION HISTORY
201 | Date Author Description of Changes
202 | 28-Jun-2004 J Rautiainen Created
203 |
204 *=======================================================================*/
205 FUNCTION open_date(p_date IN DATE) RETURN BOOLEAN IS
206 BEGIN
207 IF p_date = c_min_date
208 OR p_date = c_max_date
209 OR p_date IS NULL THEN
210 RETURN TRUE;
211 ELSE
212 RETURN FALSE;
213 END IF;
214 END open_date;
215
216 /*========================================================================
217 | PUBLIC FUNCTION open_end
218 |
219 | DESCRIPTION
220 | This function detects whether range has a open end date.
221 |
222 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
223 | audit list API
224 |
225 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
226 |
227 | RETURNS
228 | Boolean indicating whether the range has a open end date.
229 |
230 | PARAMETERS
231 | p_date_rec IN Date range to be checked.
232 |
233 | MODIFICATION HISTORY
234 | Date Author Description of Changes
235 | 28-Jun-2004 J Rautiainen Created
236 |
237 *=======================================================================*/
238 FUNCTION open_end(p_date_rec IN Date_Range_Type) RETURN BOOLEAN IS
239 BEGIN
240 RETURN open_date(p_date_rec.end_date);
241 END open_end;
242
243 /*========================================================================
244 | PUBLIC FUNCTION open_start
245 |
246 | DESCRIPTION
247 | This function detects whether range has a open start date.
248 |
249 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
250 | audit list API
251 |
252 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
253 |
254 | RETURNS
255 | Boolean indicating whether the range has a open start date.
256 |
257 | PARAMETERS
258 | p_date_rec IN Date range to be checked.
259 |
260 | MODIFICATION HISTORY
261 | Date Author Description of Changes
262 | 28-Jun-2004 J Rautiainen Created
263 |
264 *=======================================================================*/
265 FUNCTION open_start(p_date_rec IN Date_Range_Type) RETURN BOOLEAN IS
266 BEGIN
267 RETURN open_date(p_date_rec.start_date);
268 END open_start;
269
270 /*========================================================================
271 | PUBLIC FUNCTION overlap
272 |
273 | DESCRIPTION
274 | This function detects whether two ranges overlap each other.
275 |
276 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
277 | audit list API
278 |
279 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
280 |
281 | RETURNS
282 | Boolean indicating whether the two ranges overlap each other.
283 |
284 | PARAMETERS
285 | p_date1_rec IN Date range to be checked.
286 | p_date2_rec IN Date range to be checked.
287 |
288 | MODIFICATION HISTORY
289 | Date Author Description of Changes
290 | 28-Jun-2004 J Rautiainen Created
291 |
292 *=======================================================================*/
293 FUNCTION overlap(p_date1_rec IN Date_Range_Type,
294 p_date2_rec IN Date_Range_Type) RETURN BOOLEAN IS
295 BEGIN
296 IF ( includes(p_date1_rec,p_date2_rec) = TRUE
297 OR includes(p_date1_rec,p_date2_rec.start_date) = TRUE
298 OR includes(p_date1_rec,p_date2_rec.end_date) = TRUE
299 OR includes(p_date2_rec,p_date1_rec) = TRUE
300 OR includes(p_date2_rec,p_date1_rec.start_date) = TRUE
301 OR includes(p_date2_rec,p_date1_rec.end_date) = TRUE) THEN
302 RETURN TRUE;
303 ELSE
304 RETURN FALSE;
305 END IF;
306 END overlap;
307
308 /*========================================================================
309 | PUBLIC FUNCTION gap_between_start_dates
310 |
311 | DESCRIPTION
312 | This function returns the gap between the start dates of two ranges.
313 | Note this is only detected if the first record includes the second.
314 |
315 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
316 | audit list API
317 |
318 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
319 |
320 | RETURNS
321 | Date range for the gap between the start dates.
322 |
323 | PARAMETERS
324 | p_date1_rec IN Date range to be checked.
325 | p_date2_rec IN Date range to be checked.
326 |
327 | MODIFICATION HISTORY
328 | Date Author Description of Changes
329 | 28-Jun-2004 J Rautiainen Created
330 |
331 *=======================================================================*/
332 FUNCTION gap_between_start_dates(p_date1_rec IN Date_Range_Type,
333 p_date2_rec IN Date_Range_Type) RETURN Date_Range_Type IS
334 result Date_Range_Type;
335 BEGIN
336 result.start_date := NULL;
337 result.end_date := NULL;
338 IF NOT includes(p_date1_rec, p_date2_rec)
339 OR p_date1_rec.start_date = p_date2_rec.start_date THEN
340 RETURN result;
341 ELSE
342 result.start_date := p_date1_rec.start_date;
343 result.end_date := p_date2_rec.start_date-1;
344 END IF;
345 return result;
346 END gap_between_start_dates;
347
348 /*========================================================================
349 | PUBLIC FUNCTION empty
350 |
351 | DESCRIPTION
352 | This function detects whether a range record is empty.
353 |
354 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
355 | audit list API
356 |
357 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
358 |
359 | RETURNS
360 | Boolean indicating whether the range is empty.
361 |
362 | PARAMETERS
363 | p_date_rec IN Date range to be checked.
364 |
365 | MODIFICATION HISTORY
366 | Date Author Description of Changes
367 | 28-Jun-2004 J Rautiainen Created
368 |
369 *=======================================================================*/
370 FUNCTION empty(p_date_rec IN Date_Range_Type) RETURN BOOLEAN IS
371 BEGIN
372 IF p_date_rec.start_date IS NULL AND p_date_rec.end_date IS NULL THEN
373 RETURN TRUE;
374 ELSE
375 RETURN FALSE;
376 END IF;
377 END empty;
378
379 /*========================================================================
380 | PUBLIC FUNCTION equals
381 |
382 | DESCRIPTION
383 | This function detects whether two ranges are equal.
384 |
385 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
386 | audit list API
387 |
388 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
389 |
390 | RETURNS
391 | Boolean indicating whether the ranges are equal.
392 |
393 | PARAMETERS
394 | p_date1_rec IN Date range to be checked.
395 | p_date2_rec IN Date range to be checked.
396 |
397 | MODIFICATION HISTORY
398 | Date Author Description of Changes
399 | 28-Jun-2004 J Rautiainen Created
400 |
401 *=======================================================================*/
402 FUNCTION equals(p_date1_rec IN Date_Range_Type,
403 p_date2_rec IN Date_Range_Type) RETURN BOOLEAN IS
404 BEGIN
405 IF NVL(p_date1_rec.start_date, c_min_date) = NVL(p_date2_rec.start_date, c_min_date)
406 AND NVL(p_date1_rec.end_date, c_max_date) = NVL(p_date2_rec.end_date, c_max_date) THEN
407 RETURN TRUE;
408 ELSE
409 RETURN FALSE;
410 END IF;
411 END equals;
412
413 /*========================================================================
414 | PUBLIC FUNCTION before
415 |
416 | DESCRIPTION
417 | This function detects whether a date is before than another date.
418 |
419 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
420 | audit list API
421 |
422 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
423 |
424 | RETURNS
425 | Boolean indicating whether the first date is before the second.
426 |
427 | PARAMETERS
428 | p_date1 IN Date to be checked.
429 | p_date2 IN Date to be checked.
430 |
434 |
431 | MODIFICATION HISTORY
432 | Date Author Description of Changes
433 | 28-Jun-2004 J Rautiainen Created
435 *=======================================================================*/
436 FUNCTION before(p_date1 IN DATE,
437 p_date2 IN DATE) RETURN BOOLEAN IS
438 BEGIN
439 IF (p_date1 < p_date2) THEN
440 RETURN TRUE;
441 ELSE
442 RETURN FALSE;
443 END IF;
444 END before;
445
446 /*========================================================================
447 | PUBLIC FUNCTION before
448 |
449 | DESCRIPTION
450 | This function detects whether a date range is before than another
451 | date range.
452 |
453 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
454 | audit list API
455 |
456 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
457 |
458 | RETURNS
459 | Boolean indicating whether the first date range is before the second.
460 |
461 | PARAMETERS
462 | p_date1_rec IN Date range to be checked.
463 | p_date2_rec IN Date range to be checked.
464 |
465 | MODIFICATION HISTORY
466 | Date Author Description of Changes
467 | 28-Jun-2004 J Rautiainen Created
468 |
469 *=======================================================================*/
470 FUNCTION before(p_date1_rec IN Date_Range_Type,
471 p_date2_rec IN Date_Range_Type) RETURN BOOLEAN IS
472 BEGIN
473 IF overlap(p_date1_rec, p_date2_rec) THEN
474 RETURN FALSE;
475 ELSE
476 IF before(p_date1_rec.end_date, p_date2_rec.start_date) THEN
477 RETURN TRUE;
478 ELSE
479 RETURN FALSE;
480 END IF;
481 END IF;
482 END before;
483
484 /*========================================================================
485 | PUBLIC FUNCTION after
486 |
487 | DESCRIPTION
488 | This function detects whether a date is after than another date.
489 |
490 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
491 | audit list API
492 |
493 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
494 |
495 | RETURNS
496 | Boolean indicating whether the first date is after the second.
497 |
498 | PARAMETERS
499 | p_date1 IN Date to be checked.
500 | p_date2 IN Date to be checked.
501 |
502 | MODIFICATION HISTORY
503 | Date Author Description of Changes
504 | 28-Jun-2004 J Rautiainen Created
505 |
506 *=======================================================================*/
507 FUNCTION after(p_date1 IN DATE,
508 p_date2 IN DATE) RETURN BOOLEAN IS
509 BEGIN
510 IF (p_date1 > p_date2) THEN
511 RETURN TRUE;
512 ELSE
513 RETURN FALSE;
514 END IF;
515 END after;
516
517 /*========================================================================
518 | PUBLIC FUNCTION after
519 |
520 | DESCRIPTION
521 | This function detects whether a date range is after than another
522 | date range.
523 |
524 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
525 | audit list API
526 |
527 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
528 |
529 | RETURNS
530 | Boolean indicating whether the first date range is after the second.
531 |
532 | PARAMETERS
533 | p_date1_rec IN Date range to be checked.
534 | p_date2_rec IN Date range to be checked.
535 |
536 | MODIFICATION HISTORY
537 | Date Author Description of Changes
538 | 28-Jun-2004 J Rautiainen Created
539 |
540 *=======================================================================*/
541 FUNCTION after(p_date1_rec IN Date_Range_Type,
542 p_date2_rec IN Date_Range_Type) RETURN BOOLEAN IS
543 BEGIN
544 IF overlap(p_date1_rec, p_date2_rec) THEN
545 RETURN FALSE;
546 ELSE
547 IF after(p_date1_rec.start_date, p_date2_rec.end_date) THEN
548 RETURN TRUE;
549 ELSE
550 RETURN FALSE;
551 END IF;
552 END IF;
553 END after;
554
555 /*========================================================================
556 | PUBLIC PROCEDURE insert_to_audit_list
557 |
558 | DESCRIPTION
559 | This procedure inserts date ranges included in a array into the
560 | database.
561 |
562 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
563 | audit list API
564 |
565 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
566 |
567 | RETURNS
568 | None.
569 |
570 | PARAMETERS
571 | p_person_id IN Person to be added.
572 | p_range_table IN Array containing the entries to be created.
573 | p_auto_audit_id OUT Identifier of the new record created, if multiple created returns -1.
574 |
575 | MODIFICATION HISTORY
576 | Date Author Description of Changes
577 | 28-Jun-2004 J Rautiainen Created
578 |
579 *=======================================================================*/
580 PROCEDURE insert_to_audit_list(p_person_id IN NUMBER,
581 p_range_table IN range_table,
582 p_auto_audit_id OUT NOCOPY NUMBER) IS
583 l_auto_audit_id NUMBER;
584
585 BEGIN
586 p_auto_audit_id := null;
587
588 IF p_range_table.COUNT = 0 THEN
589 RETURN;
593 select AP_AUD_AUTO_AUDITS_S.nextval INTO l_auto_audit_id from sys.DUAL;
590 END IF;
591
592 FOR i IN p_range_table.FIRST..p_range_table.LAST LOOP
594
595 INSERT INTO AP_AUD_AUTO_AUDITS(
596 AUTO_AUDIT_ID,
597 EMPLOYEE_ID,
598 AUDIT_REASON_CODE,
599 START_DATE,
600 END_DATE,
601 CREATION_DATE,
602 CREATED_BY,
603 LAST_UPDATE_LOGIN,
604 LAST_UPDATE_DATE,
605 LAST_UPDATED_BY)
606 VALUES (
607 l_auto_audit_id,
608 p_person_id,
609 p_range_table(i).audit_reason_code,
610 decode(p_range_table(i).start_date,
611 c_min_date, SYSDATE,
612 p_range_table(i).start_date),
613 decode(p_range_table(i).end_date,
614 c_max_date, NULL,
615 p_range_table(i).end_date),
616 SYSDATE,
617 nvl(fnd_global.user_id, -1),
618 fnd_global.conc_login_id,
619 SYSDATE,
620 nvl(fnd_global.user_id, -1));
621
622 END LOOP;
623
624 IF p_range_table.COUNT = 1 THEN
625 p_auto_audit_id := l_auto_audit_id;
626 ELSE
627 p_auto_audit_id := -1;
628 END IF;
629 END insert_to_audit_list;
630
631 /*========================================================================
632 | PUBLIC PROCEDURE update_audit_list_entry_dates
633 |
634 | DESCRIPTION
635 | This procedure updates a audit list entry data.
636 |
637 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
638 | audit list API
639 |
640 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
641 |
642 | RETURNS
643 | None.
644 |
645 | PARAMETERS
646 | p_auto_audit_id IN Identifier of the record to be updated.
647 | p_start_date IN Start date for the record.
648 | p_end_date IN End date for the record.
649 |
650 | MODIFICATION HISTORY
651 | Date Author Description of Changes
652 | 28-Jun-2004 J Rautiainen Created
653 |
654 *=======================================================================*/
655 PROCEDURE update_audit_list_entry_dates(p_auto_audit_id IN NUMBER,
656 p_start_date IN DATE,
657 p_end_date IN DATE) IS
658 BEGIN
659 IF before(p_end_date,p_start_date) THEN
660 delete_audit_list_entry(p_auto_audit_id);
661 END IF;
662
663 UPDATE AP_AUD_AUTO_AUDITS
664 SET START_DATE = p_start_date,
665 END_DATE = decode(p_end_date,
666 c_max_date, NULL,
667 p_end_date),
668 LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
669 LAST_UPDATE_DATE = SYSDATE,
670 LAST_UPDATED_BY = nvl(fnd_global.user_id, -1)
671 WHERE AUTO_AUDIT_ID = p_auto_audit_id;
672
673 END update_audit_list_entry_dates;
674
675 /*========================================================================
676 | PUBLIC PROCEDURE move_existing_entry
677 |
678 | DESCRIPTION
679 | This procedure moves an audit list entry so that it does not overlap
680 | with the new entry.
681 |
682 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
683 | audit list API
684 |
685 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
686 |
687 | RETURNS
688 | None.
689 |
690 | PARAMETERS
691 | p_auto_audit_id IN Identifier of the record to be updated.
692 | p_new_date_range IN The new entry date range.
693 | p_old_date_range IN The old entry date range.
694 |
695 | MODIFICATION HISTORY
696 | Date Author Description of Changes
697 | 28-Jun-2004 J Rautiainen Created
698 |
699 *=======================================================================*/
700 PROCEDURE move_existing_entry(p_auto_audit_id IN NUMBER,
701 p_new_date_range IN Date_Range_Type,
702 p_old_date_range IN Date_Range_Type) IS
703 BEGIN
704 IF before(p_new_date_range.start_date, p_old_date_range.start_date)
705 OR p_new_date_range.start_date = p_old_date_range.start_date THEN
706 update_audit_list_entry_dates(p_auto_audit_id,
707 p_new_date_range.end_date + 1,
708 p_old_date_range.end_date);
709 ELSE
710
711 update_audit_list_entry_dates(p_auto_audit_id,
712 p_old_date_range.start_date,
713 p_new_date_range.start_date - 1);
714 END IF;
715
716 END move_existing_entry;
717
718 /*========================================================================
719 | PUBLIC PROCEDURE move_new_entry
720 |
721 | DESCRIPTION
722 | This procedure moves the new entry so that it does not overlap
723 | with the audit list entries.
724 |
725 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
726 | audit list API
727 |
728 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
729 |
730 | RETURNS
731 | None.
732 |
733 | PARAMETERS
734 | p_new_date_range IN The new entry date range.
735 | p_old_date_range IN The old entry date range.
736 |
737 | MODIFICATION HISTORY
738 | Date Author Description of Changes
739 | 28-Jun-2004 J Rautiainen Created
740 |
741 *=======================================================================*/
745 IF before(p_new_date_range.start_date, p_old_date_range.start_date) THEN
742 PROCEDURE move_new_entry(p_new_date_range IN OUT NOCOPY Date_Range_Type,
743 p_old_date_range IN Date_Range_Type) IS
744 BEGIN
746 p_new_date_range.end_date := p_old_date_range.start_date - 1;
747 ELSE
748 IF open_end(p_old_date_range) THEN
749 /*====================================================================================*
750 | The existing entry is stronger than the new one and it does not have a end date, |
751 | this means that there is no point processing further. Here we set the date range |
752 | of the new record so that when inserting the new record it is disregarded. |
753 | The dates are set to the limits on purpose (start to latest and end to earliest). |
754 *====================================================================================*/
755 p_new_date_range.start_date := c_max_date;
756 p_new_date_range.end_date := c_min_date;
757 ELSE
758 p_new_date_range.start_date := p_old_date_range.end_date + 1;
759 END IF;
760 END IF;
761
762 END move_new_entry;
763
764 /*========================================================================
765 | PUBLIC PROCEDURE split_existing_entry
766 |
767 | DESCRIPTION
768 | This procedure adds the new entry in the middle of an existing audit
769 | list entry.
770 |
771 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
772 | audit list API
773 |
774 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
775 |
776 | RETURNS
777 | None.
778 |
779 | PARAMETERS
780 | p_overlap_rec IN The overlapping record.
781 | p_new_date_range IN The new entry date range.
782 | p_old_date_range IN The old entry date range.
783 |
784 | MODIFICATION HISTORY
785 | Date Author Description of Changes
786 | 28-Jun-2004 J Rautiainen Created
787 |
788 *=======================================================================*/
789 PROCEDURE split_existing_entry(p_overlap_rec IN ap_aud_auto_audits%ROWTYPE,
790 p_new_date_range IN Date_Range_Type,
791 p_old_date_range IN Date_Range_Type) IS
792 temp_range Date_Range_Type;
793 insert_ranges range_table;
794 counter NUMBER := 0;
795 l_auto_audit_id NUMBER;
796 BEGIN
797 IF NOT includes(p_old_date_range, p_new_date_range) THEN
798 RETURN;
799 END IF;
800
801 update_audit_list_entry_dates(p_overlap_rec.auto_audit_id,
802 p_old_date_range.start_date,
803 p_new_date_range.start_date - 1);
804
805 IF (NOT open_end(p_new_date_range)) THEN
806 temp_range.start_date := p_new_date_range.end_date + 1;
807 temp_range.end_date := p_old_date_range.end_date;
808 temp_range.audit_reason_code := p_overlap_rec.audit_reason_code;
809
810 add_range_to_be_inserted(temp_range, insert_ranges, counter);
811
812 insert_to_audit_list(p_overlap_rec.employee_id, insert_ranges, l_auto_audit_id);
813 END IF;
814
815 END split_existing_entry;
816
817 /*========================================================================
818 | PUBLIC PROCEDURE split_new_entry
819 |
820 | DESCRIPTION
821 | This procedure splits the new entry so that an existing audit list
822 | entry remains in the middle of it.
823 |
824 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
825 | audit list API
826 |
827 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
828 |
829 | RETURNS
830 | None.
831 |
832 | PARAMETERS
833 | p_new_date_range IN The new entry date range.
834 | p_old_date_range IN The old entry date range.
835 | p_range_table IN OUT Array containing the new entries to be created.
836 | p_counter IN OUT Counter storing the count of lines in the array.
837 |
838 | MODIFICATION HISTORY
839 | Date Author Description of Changes
840 | 28-Jun-2004 J Rautiainen Created
841 |
842 *=======================================================================*/
843 PROCEDURE split_new_entry(p_new_date_range IN OUT NOCOPY Date_Range_Type,
844 p_old_date_range IN Date_Range_Type,
845 p_range_table IN OUT NOCOPY range_table,
846 p_counter IN OUT NOCOPY NUMBER) IS
847 temp_range Date_Range_Type;
848 BEGIN
849
850 temp_range := gap_between_start_dates(p_new_date_range, p_old_date_range);
851 temp_range.audit_reason_code := p_new_date_range.audit_reason_code;
852 add_range_to_be_inserted(temp_range, p_range_table, p_counter);
853
854 IF open_end(p_old_date_range) THEN
855 -- if old record is open ended do not do anything
856 p_new_date_range.start_date := c_max_date;
857 p_new_date_range.end_date := c_min_date;
858 ELSE
859 p_new_date_range.start_date := p_old_date_range.end_date + 1;
860 END IF;
861
862 END split_new_entry;
863
864 /*========================================================================
865 | PUBLIC PROCEDURE delete_audit_list_entry
866 |
867 | DESCRIPTION
868 | This procedure deletes a given audit list entry.
869 |
870 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
871 | audit list API
872 |
873 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
874 |
875 | RETURNS
876 | None.
877 |
881 | MODIFICATION HISTORY
878 | PARAMETERS
879 | p_auto_audit_id IN Identifier of the record to be deleted.
880 |
882 | Date Author Description of Changes
883 | 28-Jun-2004 J Rautiainen Created
884 |
885 *=======================================================================*/
886 PROCEDURE delete_audit_list_entry(p_auto_audit_id NUMBER) IS
887 BEGIN
888
889 DELETE AP_AUD_AUTO_AUDITS
890 WHERE AUTO_AUDIT_ID = p_auto_audit_id;
891
892 END delete_audit_list_entry;
893
894 /*========================================================================
895 | PUBLIC PROCEDURE end_date_open_entry
896 |
897 | DESCRIPTION
898 | This procedure end dates a existing open end dated record.
899 |
900 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
901 | audit list API
902 |
903 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
904 |
905 | RETURNS
906 | None.
907 |
908 | PARAMETERS
909 | p_emp_rec IN Record containing employee information.
910 | p_audit_rec IN Record containing the new audit list entry information.
911 |
912 | MODIFICATION HISTORY
913 | Date Author Description of Changes
914 | 28-Jun-2004 J Rautiainen Created
915 |
916 *=======================================================================*/
917 PROCEDURE end_date_open_entry(p_emp_rec IN AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
918 p_audit_rec IN AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type) IS
919 CURSOR target_cur IS
920 SELECT auto_audit_id, start_date, end_date
921 FROM ap_aud_auto_audits
922 WHERE employee_id = p_emp_rec.person_id
923 AND audit_reason_code = p_audit_rec.audit_reason_code
924 AND end_date is NULL;
925
926 target_rec target_cur%ROWTYPE;
927
928 BEGIN
929 OPEN target_cur;
930 FETCH target_cur INTO target_rec;
931 IF target_cur%FOUND THEN
932 update_audit_list_entry_dates(target_rec.auto_audit_id,
933 target_rec.start_date,
934 p_audit_rec.end_date);
935 END IF;
936 CLOSE target_cur;
937 END end_date_open_entry;
938
939 /*========================================================================
940 | PUBLIC PROCEDURE process_entry
941 |
942 | DESCRIPTION
943 | This procedure processes new audit list entry.
944 |
945 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
946 | audit list API
947 |
948 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
949 |
950 | RETURNS
951 | None.
952 |
953 | PARAMETERS
954 | p_emp_rec IN Record containing employee information.
955 | p_audit_rec IN Record containing the new audit list entry information.
956 | p_return_status OUT Status whether the action was succesful or not
957 | p_auto_audit_id OUT Identifier of the new record created, if multiple created returns -1.
958 |
959 | MODIFICATION HISTORY
960 | Date Author Description of Changes
961 | 28-Jun-2004 J Rautiainen Created
962 |
963 *=======================================================================*/
964 PROCEDURE process_entry(p_emp_rec IN AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
965 p_audit_rec IN AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type,
966 p_return_status OUT NOCOPY VARCHAR2,
967 p_auto_audit_id OUT NOCOPY NUMBER) IS
968
969 /*=====================================================================*
970 | Cursor containing all records that a date range overlaps. |
971 | Note to detect continuous records the dates passed in as parameters |
972 | are start_date-1 and end_date+1 |
973 *====================================================================*/
974 CURSOR overlap_cur(p_start_date DATE, p_end_date DATE) IS
975 SELECT *
976 FROM ap_aud_auto_audits
977 WHERE employee_id = p_emp_rec.person_id
978 AND ( ( trunc(start_date) between p_start_date
979 and p_end_date
980 OR
981 trunc(NVL(end_date,c_max_date)) between p_start_date
982 and p_end_date
983
984 )
985 OR
986 ( p_start_date between trunc(start_date)
987 and trunc(NVL(end_date, c_max_date))
988 OR
989 p_end_date between trunc(start_date)
990 and trunc(NVL(end_date, c_max_date))
991
992 )
993 )
994 order by start_date;
995
996 overlap_rec ap_aud_auto_audits%ROWTYPE;
997 b_termination BOOLEAN := p_audit_rec.audit_reason_code = c_termination;
998 b_loa BOOLEAN := p_audit_rec.audit_reason_code = c_loa;
999 b_regular BOOLEAN := p_audit_rec.audit_reason_code NOT IN (c_termination,c_loa);
1000 new_date_range Date_Range_Type;
1001 old_date_range Date_Range_Type;
1002 temp_range Date_Range_Type;
1003 temp_date DATE;
1004 insert_ranges range_table;
1005 counter NUMBER := 0;
1006
1007 BEGIN
1008 p_return_status := FND_API.G_RET_STS_SUCCESS;
1009
1010 /*================================*
1011 | Check for overlapping records. |
1012 *================================*/
1013 new_date_range := get_date_range(p_audit_rec);
1014 IF (open_start(new_date_range)) THEN
1018 *======================================================================*/
1015 /*======================================================================*
1016 | If the new record is open start dated, then a record with open end |
1017 | date must exist and that record is end dated. |
1019 end_date_open_entry(p_emp_rec,p_audit_rec);
1020 ELSE
1021 /*=====================================================================*
1022 | To detect continuous records the dates passed in as parameters |
1023 | are start_date-1 and end_date+1 |
1024 *====================================================================*/
1025 IF new_date_range.end_date = c_max_date THEN
1026 temp_date := c_max_date;
1027 ELSE
1028 temp_date := new_date_range.end_date+1;
1029 END IF;
1030
1031 FOR overlap_rec IN overlap_cur(new_date_range.start_date-1, temp_date) LOOP
1032 old_date_range := get_date_range(overlap_rec.start_date, overlap_rec.end_date, overlap_rec.audit_reason_code);
1033
1034 IF overlap_rec.audit_reason_code = p_audit_rec.audit_reason_code
1035 AND ( overlap(new_date_range, old_date_range) OR continuous(new_date_range, old_date_range)) THEN
1036 /*====================================================================================*
1037 | When the new record and any overlapping or continuing record has the same |
1038 | audit reason, the records are merged. |
1039 *====================================================================================*/
1040 new_date_range.start_date := least(new_date_range.start_date, old_date_range.start_date);
1041 new_date_range.end_date := greatest(new_date_range.end_date, old_date_range.end_date);
1042 delete_audit_list_entry(overlap_rec.auto_audit_id);
1043 ELSIF b_termination THEN
1044 /*====================================================================================*
1045 | When creating termination any old entry included in the new date range is deleted. |
1046 | In overlap case with any other status than termination, the overlap is removed with|
1047 | termination taking precedence. |
1048 *====================================================================================*/
1049 IF includes(new_date_range, old_date_range) THEN
1050 delete_audit_list_entry(overlap_rec.auto_audit_id);
1051 ELSIF overlap(new_date_range, old_date_range) THEN
1052 IF includes(old_date_range, new_date_range) THEN
1053 split_existing_entry(overlap_rec, new_date_range, old_date_range);
1054 ELSE
1055 move_existing_entry(overlap_rec.auto_audit_id, new_date_range,old_date_range);
1056 END IF;
1057 END IF;
1058 ELSIF b_loa THEN
1059 /*====================================================================================*
1060 | When creating loa any old entry included in the new date range is deleted, unless |
1061 | the old record is termination, in which case the termination takes precedence and |
1062 | the new loa record is updated so that there is no overlap. |
1063 | If the there is overlap and the old record is not termination the overlap is |
1064 | removed with loa taking precedence. |
1065 *====================================================================================*/
1066 IF includes(new_date_range, old_date_range) THEN
1067 IF overlap_rec.audit_reason_code = c_termination THEN
1068 split_new_entry(new_date_range, old_date_range,insert_ranges, counter);
1069 ELSE
1070 delete_audit_list_entry(overlap_rec.auto_audit_id);
1071 END IF;
1072 ELSIF overlap(new_date_range, old_date_range) THEN
1073 IF overlap_rec.audit_reason_code = c_termination THEN
1074 move_new_entry(new_date_range,old_date_range);
1075 ELSIF includes(old_date_range, new_date_range) THEN
1076 split_existing_entry(overlap_rec, new_date_range, old_date_range);
1077 ELSE
1078 move_existing_entry(overlap_rec.auto_audit_id, new_date_range,old_date_range);
1079 END IF;
1080 END IF;
1081 ELSE -- b_regular
1082 /*====================================================================================*
1083 | For all other statuses the new record takes precedence of any other record than |
1084 | termination and loa. |
1085 *====================================================================================*/
1086 IF includes(new_date_range, old_date_range) THEN
1087 IF overlap_rec.audit_reason_code IN (c_termination, c_loa) THEN
1088 split_new_entry(new_date_range, old_date_range,insert_ranges, counter);
1089 ELSE
1090 delete_audit_list_entry(overlap_rec.auto_audit_id);
1091 END IF;
1092 ELSIF overlap(new_date_range, old_date_range) THEN
1093 IF overlap_rec.audit_reason_code IN (c_termination, c_loa) THEN
1094 move_new_entry(new_date_range,old_date_range);
1095 ELSIF includes(old_date_range, new_date_range) THEN
1096 split_existing_entry(overlap_rec, new_date_range, old_date_range);
1097 ELSE
1098 move_existing_entry(overlap_rec.auto_audit_id, new_date_range,old_date_range);
1099 END IF;
1100 END IF;
1101 END IF; -- b_termination - b_loa - b_regular ...
1102 END LOOP;
1103
1104 /*====================================================================================*
1105 | Once all the existing records are processed create add the remaining date range of |
1109 temp_range.end_date := new_date_range.end_date;
1106 | the new record in to the array od records to be created. |
1107 *====================================================================================*/
1108 temp_range.start_date := new_date_range.start_date;
1110 temp_range.audit_reason_code := p_audit_rec.audit_reason_code;
1111 add_range_to_be_inserted(temp_range, insert_ranges, counter);
1112
1113 insert_to_audit_list(p_emp_rec.person_id, insert_ranges, p_auto_audit_id);
1114
1115 END IF; -- open_start
1116 EXCEPTION
1117 WHEN others THEN
1118 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1119 END process_entry;
1120
1121 /*========================================================================
1122 | PUBLIC PROCEDURE add_range_to_be_inserted
1123 |
1124 | DESCRIPTION
1125 | This procedure adds a range to the array used to store the ranges to
1126 | be inserted as audit list entries.
1127 |
1128 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1129 | audit list API
1130 |
1131 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1132 |
1133 | RETURNS
1134 | None.
1135 |
1136 | PARAMETERS
1137 | p_date_range IN The entry date range.
1138 | p_range_table IN OUT Array containing the new entries to be created.
1139 | p_counter IN OUT Counter storing the count of lines in the array.
1140 |
1141 | MODIFICATION HISTORY
1142 | Date Author Description of Changes
1143 | 28-Jun-2004 J Rautiainen Created
1144 |
1145 *=======================================================================*/
1146 PROCEDURE add_range_to_be_inserted(p_range IN Date_Range_Type,
1147 p_range_table IN OUT NOCOPY range_table,
1148 counter IN OUT NOCOPY NUMBER) IS
1149 BEGIN
1150 IF empty(p_range)
1151 OR before(p_range.end_date,p_range.start_date) THEN
1152 RETURN;
1153 END IF;
1154
1155 p_range_table(counter).start_date := p_range.start_date;
1156 p_range_table(counter).end_date := p_range.end_date;
1157 p_range_table(counter).audit_reason_code := p_range.audit_reason_code;
1158 counter := counter + 1;
1159
1160 END add_range_to_be_inserted;
1161
1162 /*========================================================================
1163 | PUBLIC PROCEDURE remove_entries
1164 |
1165 | DESCRIPTION
1166 | This procedure processes audit list entry removal.
1167 |
1168 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1169 | audit list API
1170 |
1171 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1172 |
1173 | RETURNS
1174 | None.
1175 |
1176 | PARAMETERS
1177 | p_emp_rec IN Record containing employee information.
1178 | p_date_range_rec IN Record containg date range
1179 | p_return_status OUT Status whether the action was succesful or not
1180 |
1181 | MODIFICATION HISTORY
1182 | Date Author Description of Changes
1183 | 28-Jun-2004 J Rautiainen Created
1184 |
1185 *=======================================================================*/
1186 PROCEDURE remove_entries(p_emp_rec IN AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
1187 p_date_range_rec IN AP_WEB_AUDIT_LIST_PUB.Date_Range_Type,
1188 p_return_status OUT NOCOPY VARCHAR2) IS
1189
1190 /*=====================================================================*
1191 | Cursor containing all records that a date range overlaps. |
1192 *====================================================================*/
1193 CURSOR overlap_cur(p_start_date DATE, p_end_date DATE) IS
1194 SELECT *
1195 FROM ap_aud_auto_audits
1196 WHERE employee_id = p_emp_rec.person_id
1197 AND ( ( trunc(start_date) between p_start_date
1198 and p_end_date
1199 OR
1200 trunc(NVL(end_date,c_max_date)) between p_start_date
1201 and p_end_date
1202
1203 )
1204 OR
1205 ( p_start_date between trunc(start_date)
1206 and trunc(NVL(end_date, c_max_date))
1207 OR
1208 p_end_date between trunc(start_date)
1209 and trunc(NVL(end_date, c_max_date))
1210
1211 )
1212 )
1213 order by start_date;
1214
1215 new_date_range Date_Range_Type;
1216 old_date_range Date_Range_Type;
1217 BEGIN
1218 p_return_status := FND_API.G_RET_STS_SUCCESS;
1219 new_date_range := get_date_range(p_date_range_rec.start_date, p_date_range_rec.end_date, NULL);
1220
1221 FOR overlap_rec IN overlap_cur(new_date_range.start_date, new_date_range.end_date) LOOP
1222 old_date_range := get_date_range(overlap_rec.start_date, overlap_rec.end_date, overlap_rec.audit_reason_code);
1223
1224 /*====================================================================================*
1225 | When deleting a data range any old entry included in the date range is deleted. |
1226 | In overlap case the overlap is removed. |
1227 *====================================================================================*/
1228 IF includes(new_date_range, old_date_range) THEN
1229 delete_audit_list_entry(overlap_rec.auto_audit_id);
1230 ELSIF overlap(new_date_range, old_date_range) THEN
1231 IF includes(old_date_range, new_date_range) THEN
1232 split_existing_entry(overlap_rec, new_date_range, old_date_range);
1233 ELSE
1234 move_existing_entry(overlap_rec.auto_audit_id, new_date_range,old_date_range);
1235 END IF;
1236 END IF;
1237 END LOOP;
1238
1239 EXCEPTION
1240 WHEN others THEN
1241 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1242 END remove_entries;
1243
1244 END AP_WEB_AUDIT_LIST_PVT;
[Home] [Help]