1 PACKAGE BODY AP_WEB_AUDIT_PROCESS AS
2 /* $Header: apwaudpb.pls 120.22.12010000.4 2008/08/28 06:49:27 stalasil ship $ */
3
4 -- jrautiai ADJ Fix Start
5 C_CompanyPay CONSTANT VARCHAR2(10) := 'COMPANY';
6 -- jrautiai ADJ End
7
8 /*========================================================================
9 | Prototype Declarations Procedures
10 *=======================================================================*/
11 PROCEDURE process_policy_violations(p_report_header_id IN NUMBER, p_audit_report OUT NOCOPY BOOLEAN);
12 PROCEDURE process_audit_list_member(p_report_header_id IN NUMBER, p_employee_id IN NUMBER, p_audit_report OUT NOCOPY BOOLEAN);
13 PROCEDURE process_old_receipts(p_report_header_id IN NUMBER, p_age_limit IN NUMBER, p_audit_report OUT NOCOPY BOOLEAN);
14 PROCEDURE process_receipts_required(p_report_header_id IN NUMBER, p_audit_report OUT NOCOPY BOOLEAN);
15 PROCEDURE process_justification_required(p_report_header_id IN NUMBER, p_audit_report OUT NOCOPY BOOLEAN);
16 PROCEDURE process_inactive(p_report_header_id IN NUMBER, p_audit_report OUT NOCOPY BOOLEAN);
17 PROCEDURE process_unused_advance(p_report_header_id IN NUMBER, p_audit_report OUT NOCOPY BOOLEAN);
18 PROCEDURE process_amount(p_report_header_id IN NUMBER, p_audit_all_amount_limit IN NUMBER, p_audit_all_amount_currency IN VARCHAR2, p_audit_report OUT NOCOPY BOOLEAN);
19 PROCEDURE process_random_audit(p_report_header_id IN NUMBER,
20 p_random_audit_percentage IN NUMBER,
21 p_ignore_credit_only_flag IN VARCHAR2,
22 p_ignore_rj_not_req_only_flag IN VARCHAR2,
23 p_audit_report OUT NOCOPY BOOLEAN);
24 PROCEDURE process_custom_audit(p_report_header_id IN NUMBER, p_audit_report OUT NOCOPY BOOLEAN, p_override_default_processing OUT NOCOPY BOOLEAN);
25 PROCEDURE update_audit_code(p_report_header_id IN NUMBER, p_audit_code IN VARCHAR2);
26 PROCEDURE process_paperless_audit(p_report_header_id IN NUMBER,
27 p_pl_audit_cc_only_flag IN VARCHAR2,
28 p_pl_audit_violation_flag IN VARCHAR2,
29 p_pl_audit_pdm_only_flag IN VARCHAR2,
30 p_assign_auditor_stage_code IN VARCHAR2,
31 p_audit_report OUT NOCOPY BOOLEAN);
32
33 PROCEDURE process_audit_list(p_report_header_id IN NUMBER);
34 PROCEDURE insert_audit_reason(p_report_header_id IN NUMBER, p_audit_reason_code IN VARCHAR2);
35 PROCEDURE update_audit_reason(p_report_header_id IN NUMBER, p_audit_reason_code IN VARCHAR2);
36 FUNCTION get_report_violation_count(p_report_header_id IN NUMBER) RETURN NUMBER;
37 FUNCTION get_employee_violation_count(p_employee_id IN NUMBER, p_months IN NUMBER) RETURN NUMBER;
38
39 /*========================================================================
40 | Prototype Declarations Functions
41 *=======================================================================*/
42 FUNCTION construct_random_percentage(pn_random_number NUMBER) RETURN NUMBER;
43 FUNCTION get_random_percentage RETURN NUMBER;
44 FUNCTION get_shortpaid_audit_code(p_report_header_id IN NUMBER) RETURN VARCHAR2;
45
46 /*========================================================================
47 | PUBLIC FUNCTION get_shortpaid_audit_code
48 |
49 | DESCRIPTION
50 | This function returns the audit code of parent report for a shortpaid expense report.
51 |
52 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
53 |
54 | RETURNS
55 | audit_code VARCHAR2
56 |
57 | PARAMETERS
58 | p_report_header_id IN Expense Report identifier
59 |
60 | MODIFICATION HISTORY
61 | Date Author Description of Changes
62 | 20-Dec-2005 SaiKumar Talasila Created
63 *=======================================================================*/
64
65 FUNCTION get_shortpaid_audit_code(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
66 l_audit_code VARCHAR2(30);
67 l_parent_report_id NUMBER;
68 BEGIN
69
70 SELECT shortpay_parent_id INTO l_parent_report_id
71 FROM ap_expense_report_headers_all
72 WHERE report_header_id = p_report_header_id;
73
74 IF l_parent_report_id is NOT NULL THEN
75 SELECT audit_code into l_audit_code
76 FROM ap_expense_report_headers_all
77 WHERE report_header_id = l_parent_report_id;
78
79 RETURN l_audit_code;
80 ELSE
81 RETURN NULL;
82 END IF;
83
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 RETURN NULL;
87 WHEN OTHERS THEN
88 AP_WEB_DB_UTIL_PKG.RaiseException('get_shortpaid_audit_code');
89 APP_EXCEPTION.RAISE_EXCEPTION;
90
91 END get_shortpaid_audit_code;
92
93 /*========================================================================
94 | PUBLIC FUNCTION process_expense_report
95 |
96 | DESCRIPTION
97 | This function does audit processing for a given expense report.
98 |
99 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
100 | Called from BC4J.
101 |
102 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
103 |
104 | RETURNS
105 | Tag containing auditing information as VARCHAR2.
106 |
107 | PARAMETERS
108 | p_report_header_id IN Expense Report identifier
109 |
110 | MODIFICATION HISTORY
111 | Date Author Description of Changes
112 | 13-Oct-2002 J Rautiainen Created
113 | 11-Mar-2005 Maulik Vadera Bug 4192680: Modified code to set the audit code
114 | AUTO_APPROVE for the reports those contain
115 | only both pay personal expense.
116 *=======================================================================*/
117 FUNCTION process_expense_report(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
118 PRAGMA AUTONOMOUS_TRANSACTION;
119
120 CURSOR process_cur IS
121 select rs.rule_set_id,
122 rs.rule_set_type,
123 rs.description,
124 rs.start_date,
125 rs.end_date,
126 rs.paperless_audit_cc_only_flag,
127 rs.paperless_audit_violation_flag,
128 rs.paperless_audit_pdm_only_flag,
129 rs.assign_auditor_stage_code,
130 rs.audit_all_violations_flag,
131 rs.audit_all_from_audit_list_flag,
132 rs.audit_all_old_receipts_flag,
133 rs.audit_all_receipt_days_limit,
134 rs.random_audit_flag,
135 rs.random_audit_percentage,
136 rs.ignore_credit_only_flag,
137 rs.ignore_rj_not_req_only_flag,
138 rs.auto_approval_tag,
139 rs.requires_audit_tag,
140 rs.paperless_audit_tag,
141 aerh.employee_id,
142 aerh.audit_code,
143 rs.rule_set_name,
144 aerh.org_id,
145 rs.audit_all_amount_code,
146 rs.audit_all_amount_limit,
147 rs.audit_all_amount_currency_code,
148 rs.audit_all_receipts_code,
149 rs.audit_all_justification_code,
150 rs.audit_all_inactive_code,
151 rs.audit_all_unused_advances
152 from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
153 AP_AUD_RULE_SETS rs,
154 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
155 where aerh.report_header_id = p_report_header_id
156 and aerh.org_id = rsa.org_id
157 and rsa.rule_set_id = rs.rule_set_id
158 and rs.rule_set_type = 'RULE'
159 and TRUNC(SYSDATE)
160 BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
161 AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
162
163 process_rec process_cur%ROWTYPE;
164 audit_report boolean := false;
165 audit_report_tmp boolean := false;
166 override_default_processing boolean := false;
167 l_bpay_personal_cc_only boolean := false;
168 l_shortpaid_report boolean := false;
169 audit_code VARCHAR2(30);
170 l_old_audit_code ap_expense_report_headers_all.audit_code%type := null;
171 l_old_audit_reason_code ap_aud_audit_reasons.audit_reason_code%type := null;
172 BEGIN
173
174 IF p_report_header_id is null THEN
175 return null;
176 END IF;
177
178 OPEN process_cur;
179 FETCH process_cur INTO process_rec;
180
181 IF process_cur%NOTFOUND THEN
182 CLOSE process_cur;
183 return null;
184 END IF;
185
186 CLOSE process_cur;
187
188 --Bug#6632506 : AuditCode should be copied form parent report for a shortpaid report
189 l_shortpaid_report := ap_web_audit_queue_utils.report_shortpaid(p_report_header_id);
190
191 IF l_shortpaid_report THEN
192 audit_code := get_shortpaid_audit_code(p_report_header_id);
193
194 IF audit_code is NOT NULL THEN
195
196 update_audit_code(p_report_header_id, audit_code);
197 COMMIT;
198
199 IF audit_code = 'PAPERLESS_AUDIT' THEN
200 IF (NVL(process_rec.assign_auditor_stage_code,'MANAGER_APPROVAL') = 'SUBMISSION') THEN
201 AP_WEB_AUDIT_QUEUE_UTILS.enqueue_for_audit(p_report_header_id);
202 COMMIT;
203 END IF;
204 END IF;
205
206 IF audit_code = 'PAPERLESS_AUDIT' THEN
207 RETURN process_rec.paperless_audit_tag;
208 ELSIF audit_code = 'AUTO_APPROVE' THEN
209 RETURN process_rec.auto_approval_tag;
210 ELSE
211 RETURN process_rec.requires_audit_tag;
212 END IF;
213 END IF;
214
215 END IF;
216
217 l_bpay_personal_cc_only := bothpay_personal_cc_only(p_report_header_id);
218
219 IF l_bpay_personal_cc_only THEN
220
221 audit_code := 'AUTO_APPROVE';
222 update_audit_code(p_report_header_id, audit_code);
223 COMMIT;
224
225 RETURN process_rec.auto_approval_tag;
226
227 END IF;
228
229 process_audit_list(p_report_header_id);
230
231 IF process_rec.audit_code IS NULL OR process_rec.audit_code = 'AUTO_APPROVE' THEN
232 process_custom_audit(p_report_header_id, audit_report_tmp, override_default_processing);
233
234 audit_report := audit_report_tmp;
235
236 IF (NVL(override_default_processing, FALSE) = TRUE) THEN
237 null;
238 ELSE
239
240 IF NVL(process_rec.audit_all_violations_flag, 'N') = 'Y' THEN
241 process_policy_violations(p_report_header_id, audit_report_tmp);
242
243 IF audit_report = false THEN
244 audit_report := audit_report_tmp;
245 END IF;
246 END IF;
247
248 IF NVL(process_rec.audit_all_from_audit_list_flag, 'N') = 'Y' THEN
249 process_audit_list_member(p_report_header_id, process_rec.employee_id, audit_report_tmp);
250
251 IF audit_report = false THEN
252 audit_report := audit_report_tmp;
253 END IF;
254 END IF;
255
256 IF NVL(process_rec.audit_all_old_receipts_flag, 'N') = 'Y' THEN
257 process_old_receipts(p_report_header_id, process_rec.audit_all_receipt_days_limit, audit_report_tmp);
258
259 IF audit_report = false THEN
260 audit_report := audit_report_tmp;
261 END IF;
262 END IF;
263
264 /* Bug 4019412 : If the expense report was previously randomly chosen for
265 * audit, it should be audited even if it is withdrawn and resubmitted.
266 */
267 BEGIN
268 select audit_reason_code, audit_code
269 into l_old_audit_reason_code, l_old_audit_code
270 from
271 (
272 select audit_code, audit_reason_code
273 from ap_expense_report_headers_all aerh,
274 ap_aud_audit_reasons aud
275 where aerh.report_header_id = p_report_header_id
276 and aerh.report_header_id = aud.report_header_id
277 and aud.audit_reason_code = 'RANDOM'
278 )
279 where rownum=1;
280 EXCEPTION WHEN NO_DATA_FOUND THEN
281 NULL;
282 END;
283
284 IF l_old_audit_code is NULL and l_old_audit_reason_code = 'RANDOM' then
285 update_audit_reason(p_report_header_id, 'RANDOM');
286 audit_report := TRUE;
287 ELSIF NVL(process_rec.random_audit_flag, 'N') = 'Y' THEN
288 process_random_audit(p_report_header_id,
289 process_rec.random_audit_percentage,
290 process_rec.ignore_credit_only_flag,
291 process_rec.ignore_rj_not_req_only_flag,
292 audit_report_tmp);
293
294 IF audit_report = false THEN
295 audit_report := audit_report_tmp;
296 END IF;
297
298 END IF;
299
300 IF NVL(process_rec.audit_all_amount_code, 'NOT_ACTIVE') = 'GREATER_THAN' THEN
301 process_amount(p_report_header_id,
302 process_rec.audit_all_amount_limit,
303 process_rec.audit_all_amount_currency_code,
304 audit_report_tmp);
305
306 IF audit_report = false THEN
307 audit_report := audit_report_tmp;
308 END IF;
309 END IF;
310
311 IF NVL(process_rec.audit_all_receipts_code, 'NOT_ACTIVE') = 'REQUIRED' THEN
312 process_receipts_required(p_report_header_id, audit_report_tmp);
313
314 IF audit_report = false THEN
315 audit_report := audit_report_tmp;
316 END IF;
317 END IF;
318
319 IF NVL(process_rec.audit_all_justification_code, 'NOT_ACTIVE') = 'REQUIRED' THEN
320 process_justification_required(p_report_header_id, audit_report_tmp);
321
322 IF audit_report = false THEN
323 audit_report := audit_report_tmp;
324 END IF;
325 END IF;
326
327 IF NVL(process_rec.audit_all_inactive_code, 'NOT_ACTIVE') = 'INACTIVE' THEN
328 process_inactive(p_report_header_id, audit_report_tmp);
329
330 IF audit_report = false THEN
331 audit_report := audit_report_tmp;
332 END IF;
333 END IF;
334
335 IF NVL(process_rec.audit_all_unused_advances, 'N') = 'Y' THEN
336 process_unused_advance(p_report_header_id, audit_report_tmp);
337
338 IF audit_report = false THEN
339 audit_report := audit_report_tmp;
340 END IF;
341 END IF;
342
343 END IF; -- override_default_processing = TRUE
344
345
346 audit_code := 'AUDIT';
347 IF (audit_report = false) THEN
348 audit_code := 'AUTO_APPROVE';
349
350 ELSIF ( NVL(process_rec.paperless_audit_cc_only_flag, 'N') = 'Y'
351 OR NVL(process_rec.paperless_audit_violation_flag, 'N') = 'Y'
352 OR NVL(process_rec.paperless_audit_pdm_only_flag, 'N') = 'Y') THEN
353
354 process_paperless_audit(p_report_header_id,
355 NVL(process_rec.paperless_audit_cc_only_flag, 'N'),
356 NVL(process_rec.paperless_audit_violation_flag, 'N'),
357 NVL(process_rec.paperless_audit_pdm_only_flag, 'N'),
361 IF audit_report_tmp = true THEN
358 NVL(process_rec.assign_auditor_stage_code,'MANAGER_APPROVAL'),
359 audit_report_tmp);
360
362 audit_code := 'PAPERLESS_AUDIT';
363 END IF;
364 END IF;
365
366 update_audit_code(p_report_header_id, audit_code);
367
368 COMMIT;
369 ELSE
370 audit_code := process_rec.audit_code;
371
372 IF audit_code = 'PAPERLESS_AUDIT' THEN
373 IF (NVL(process_rec.assign_auditor_stage_code,'MANAGER_APPROVAL') = 'SUBMISSION') THEN
374 AP_WEB_AUDIT_QUEUE_UTILS.enqueue_for_audit(p_report_header_id);
375 COMMIT;
376 END IF;
377 END IF;
378 END IF; --process_rec.audit_code IS NULL
379
380 IF audit_code = 'PAPERLESS_AUDIT' THEN
381 RETURN process_rec.paperless_audit_tag;
382 ELSIF audit_code = 'AUTO_APPROVE' THEN
383 RETURN process_rec.auto_approval_tag;
384 ELSE
385 RETURN process_rec.requires_audit_tag;
386 END IF;
387
388 END process_expense_report;
389
390 /*========================================================================
391 | PRIVATE PROCEDURE process_policy_violations
392 |
393 | DESCRIPTION
394 | This procedure does policy violation processing for a given expense report.
395 |
396 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
397 | Called from BC4J.
398 |
399 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
400 |
401 | RETURNS
402 |
403 | PARAMETERS
404 | p_report_header_id IN Expense Report identifier
405 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
406 |
407 | MODIFICATION HISTORY
408 | Date Author Description of Changes
409 | 13-Oct-2002 J Rautiainen Created
410 |
411 *=======================================================================*/
412 PROCEDURE process_policy_violations(p_report_header_id IN NUMBER,
413 p_audit_report OUT NOCOPY BOOLEAN) IS
414 BEGIN
415 p_audit_report := FALSE;
416
417 IF get_report_violation_count(p_report_header_id) > 0 THEN
418 insert_audit_reason(p_report_header_id, 'POLICY_VIOLATION');
419 p_audit_report := TRUE;
420 END IF;
421
422 END process_policy_violations;
423
424 /*========================================================================
425 | PRIVATE PROCEDURE process_audit_list_member
426 |
427 | DESCRIPTION
428 | This procedure does audit list member processing for a given expense report.
429 |
430 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
431 | Called from BC4J.
432 |
433 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
434 |
435 | RETURNS
436 |
437 | PARAMETERS
438 | p_report_header_id IN Expense Report identifier
439 | p_employee_id IN Employee identifier
440 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
441 |
442 | MODIFICATION HISTORY
443 | Date Author Description of Changes
444 | 13-Oct-2002 J Rautiainen Created
445 |
446 *=======================================================================*/
447 PROCEDURE process_audit_list_member(p_report_header_id IN NUMBER,
448 p_employee_id IN NUMBER,
449 p_audit_report OUT NOCOPY BOOLEAN) IS
450
451 CURSOR audit_list_cur IS
452 select auto_audit_id
453 from AP_AUD_AUTO_AUDITS
454 where employee_id = p_employee_id
455 and TRUNC(SYSDATE)
456 BETWEEN TRUNC(NVL(START_DATE,SYSDATE))
457 AND TRUNC(NVL(END_DATE,SYSDATE));
458
459 audit_list_rec audit_list_cur%ROWTYPE;
460
461 BEGIN
462 p_audit_report := FALSE;
463
464 OPEN audit_list_cur;
465 FETCH audit_list_cur INTO audit_list_rec;
466
467 IF audit_list_cur%FOUND THEN
468 insert_audit_reason(p_report_header_id, 'AUDIT_LIST');
469 p_audit_report := TRUE;
470 END IF;
471
472 CLOSE audit_list_cur;
473
474 END process_audit_list_member;
475
476 /*========================================================================
477 | PRIVATE PROCEDURE process_old_receipts
478 |
479 | DESCRIPTION
480 | This procedure does old receipt processing for a given expense report.
481 |
482 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
483 | Called from BC4J.
484 |
485 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
486 |
487 | RETURNS
488 |
489 | PARAMETERS
490 | p_report_header_id IN Expense Report identifier
491 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
492 |
493 | MODIFICATION HISTORY
494 | Date Author Description of Changes
495 | 13-Oct-2002 J Rautiainen Created
496 |
497 *=======================================================================*/
498 PROCEDURE process_old_receipts(p_report_header_id IN NUMBER,
499 p_age_limit IN NUMBER,
503 select min(start_expense_date) oldest_receipt_date
500 p_audit_report OUT NOCOPY BOOLEAN) IS
501
502 CURSOR receipt_cur IS
504 from AP_EXPENSE_REPORT_LINES_ALL
505 where report_header_id = p_report_header_id;
506
507 receipt_rec receipt_cur%ROWTYPE;
508
509 BEGIN
510 p_audit_report := FALSE;
511
512 OPEN receipt_cur;
513 FETCH receipt_cur INTO receipt_rec;
514
515 IF receipt_cur%FOUND THEN
516 IF trunc(receipt_rec.oldest_receipt_date) < (trunc(SYSDATE) - p_age_limit) THEN
517 insert_audit_reason(p_report_header_id, 'OVERDUE_RECEIPTS');
518 p_audit_report := TRUE;
519 END IF;
520 END IF;
521
522 CLOSE receipt_cur;
523
524 END process_old_receipts;
525
526 /*========================================================================
527 | PRIVATE PROCEDURE process_receipts_required
528 |
529 | DESCRIPTION
530 | This procedure does receipts required rule processing for a given
531 | expense report.
532 |
533 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
534 | Called from BC4J.
535 |
536 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
537 |
538 | RETURNS
539 |
540 | PARAMETERS
541 | p_report_header_id IN Expense Report identifier
542 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
543 |
544 | MODIFICATION HISTORY
545 | Date Author Description of Changes
546 | 16-Dec-2004 J Rautiainen Created
547 |
548 *=======================================================================*/
549 PROCEDURE process_receipts_required(p_report_header_id IN NUMBER,
550 p_audit_report OUT NOCOPY BOOLEAN) IS
551
552 CURSOR receipt_cur IS
553 select count(1) required_count
554 from AP_EXPENSE_REPORT_LINES_ALL
555 where report_header_id = p_report_header_id
556 and NVL(receipt_required_flag,'N') = 'Y';
557
558 receipt_rec receipt_cur%ROWTYPE;
559
560 BEGIN
561 p_audit_report := FALSE;
562
563 OPEN receipt_cur;
564 FETCH receipt_cur INTO receipt_rec;
565 IF receipt_rec.required_count > 0 THEN
566 insert_audit_reason(p_report_header_id, 'RECEIPT_REQUIRED');
567 p_audit_report := TRUE;
568 END IF;
569
570 CLOSE receipt_cur;
571
572 END process_receipts_required;
573
574 /*========================================================================
575 | PRIVATE PROCEDURE process_justification_required
576 |
577 | DESCRIPTION
578 | This procedure does justification required rule processing for a given
579 | expense report.
580 |
581 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
582 | Called from BC4J.
583 |
584 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
585 |
586 | RETURNS
587 |
588 | PARAMETERS
589 | p_report_header_id IN Expense Report identifier
590 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
591 |
592 | MODIFICATION HISTORY
593 | Date Author Description of Changes
594 | 16-Dec-2004 J Rautiainen Created
595 |
596 *=======================================================================*/
597 PROCEDURE process_justification_required(p_report_header_id IN NUMBER,
598 p_audit_report OUT NOCOPY BOOLEAN) IS
599
600 CURSOR line_cur IS
601 select count(aerl.report_header_id) required_count
602 from AP_EXPENSE_REPORT_LINES_ALL aerl
603 where aerl.report_header_id = p_report_header_id
604 and ( NVL(aerl.justification_required_flag,'N') = 'Y'
605 OR (
606 NVL(aerl.justification_required_flag,'N') = 'V'
607 AND EXISTS(select 1
608 from ap_pol_violations_all pv
609 where pv.report_header_id = aerl.report_header_id
610 and pv.distribution_line_number = aerl.distribution_line_number
611 )
612 )
613 );
614
615 line_rec line_cur%ROWTYPE;
616
617 BEGIN
618 p_audit_report := FALSE;
619
620 OPEN line_cur;
621 FETCH line_cur INTO line_rec;
622 IF line_rec.required_count > 0 THEN
623 insert_audit_reason(p_report_header_id, 'REQUIRED_JUSTIFICATION');
624 p_audit_report := TRUE;
625 END IF;
626
627 CLOSE line_cur;
628
629 END process_justification_required;
630
631 /*========================================================================
632 | PRIVATE PROCEDURE process_inactive
633 |
634 | DESCRIPTION
635 | This procedure does inactive employee rule processing for a given
636 | expense report.
637 |
638 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
639 | Called from BC4J.
640 |
641 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
642 |
643 | RETURNS
644 |
645 | PARAMETERS
646 | p_report_header_id IN Expense Report identifier
647 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
648 |
652 |
649 | MODIFICATION HISTORY
650 | Date Author Description of Changes
651 | 16-Dec-2004 J Rautiainen Created
653 *=======================================================================*/
654 PROCEDURE process_inactive(p_report_header_id IN NUMBER,
655 p_audit_report OUT NOCOPY BOOLEAN) IS
656
657 CURSOR report_cur IS
658 select AP_WEB_AUDIT_UTILS.is_employee_active(employee_id, org_id) active
659 from AP_EXPENSE_REPORT_HEADERS_ALL
660 where report_header_id = p_report_header_id;
661
662 report_rec report_cur%ROWTYPE;
663
664 BEGIN
665 p_audit_report := FALSE;
666
667 OPEN report_cur;
668 FETCH report_cur INTO report_rec;
669 IF report_cur%FOUND
670 AND report_rec.active = 'N' THEN
671 insert_audit_reason(p_report_header_id, 'INACTIVE_EMPLOYEE');
672 p_audit_report := TRUE;
673 END IF;
674
675 CLOSE report_cur;
676
677 END process_inactive;
678
679 /*========================================================================
680 | PRIVATE PROCEDURE process_unused_advance
681 |
682 | DESCRIPTION
683 | This procedure does unused advance rule processing for a given
684 | expense report.
685 |
686 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
687 | Called from BC4J.
688 |
689 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
690 |
691 | RETURNS
692 |
693 | PARAMETERS
694 | p_report_header_id IN Expense Report identifier
695 | p_audit_report OUT NOCOPY TRUE if report needs to be audited |
696 |
697 *=======================================================================*/
698 PROCEDURE process_unused_advance(p_report_header_id IN NUMBER,
699 p_audit_report OUT NOCOPY BOOLEAN) IS
700
701 adv_not_applied_flag VARCHAR2(1) := 'N';
702
703 BEGIN
704 p_audit_report := FALSE;
705
706 BEGIN
707 SELECT 'Y'
708 INTO adv_not_applied_flag
709 FROM dual
710 WHERE EXISTS(
711 SELECT 'Y'
712 FROM ap_expense_report_headers_all XH,
713 ap_invoices_all AI,
714 po_vendors PV
715 WHERE XH.report_header_id = p_report_header_id
716 AND XH.prepay_num IS NULL
717 AND XH.advance_invoice_to_apply IS NULL
718 AND Nvl(XH.maximum_amount_to_apply,0) = 0
719 AND PV.employee_id = XH.employee_id
720 AND AI.vendor_id = PV.vendor_id
721 AND AI.invoice_type_lookup_code = 'PREPAYMENT'
722 AND AI.earliest_settlement_date <= sysdate
723 AND AI.payment_status_flag = 'Y'
724 AND AI.invoice_currency_code = XH.payment_currency_code
725 AND AP_WEB_PAYMENTS_PKG.get_prepay_amount_remaining(AI.invoice_id,AI.invoice_num,PV.employee_id,XH.default_currency_code,null,null,200) > 0
726 );
727 EXCEPTION
728 WHEN NO_DATA_FOUND THEN
729 adv_not_applied_flag := 'N';
730 END;
731
732 IF ( adv_not_applied_flag = 'Y' ) THEN
733 insert_audit_reason(p_report_header_id, 'UNAPPLIED_ADVANCE');
734 p_audit_report := TRUE;
735 END IF;
736
737 END process_unused_advance;
738
739
740 /*========================================================================
741 | PRIVATE PROCEDURE process_amount
742 |
743 | DESCRIPTION
744 | This procedure does amount rule processing for a given
745 | expense report.
746 |
747 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
748 | Called from BC4J.
749 |
750 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
751 |
752 | RETURNS
753 |
754 | PARAMETERS
755 | p_report_header_id IN Expense Report identifier
756 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
757 |
758 | MODIFICATION HISTORY
759 | Date Author Description of Changes
760 | 16-Dec-2004 J Rautiainen Created
761 |
762 *=======================================================================*/
763 PROCEDURE process_amount(p_report_header_id IN NUMBER,
764 p_audit_all_amount_limit IN NUMBER,
765 p_audit_all_amount_currency IN VARCHAR2,
766 p_audit_report OUT NOCOPY BOOLEAN) IS
767
768 CURSOR report_cur IS
769 select aerh.default_currency_code,
770 aerh.total,
771 sp.default_exchange_rate_type
772 from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
773 ap_system_parameters_all sp
774 where aerh.report_header_id = p_report_header_id
775 and sp.org_id = aerh.org_id;
776
777 report_rec report_cur%ROWTYPE;
778 l_total NUMBER := to_number(null);
779
780 BEGIN
781 p_audit_report := FALSE;
782
783 OPEN report_cur;
784 FETCH report_cur INTO report_rec;
785 IF report_cur%FOUND THEN
786
787 IF report_rec.default_currency_code = p_audit_all_amount_currency THEN
788 l_total := report_rec.total;
789 ELSE
790 BEGIN
794 report_rec.default_exchange_rate_type,
791 l_total := gl_currency_api.convert_amount(report_rec.default_currency_code,
792 p_audit_all_amount_currency,
793 SYSDATE,
795 report_rec.total);
796 EXCEPTION
797 WHEN OTHERS THEN
798 /* If amount cannot be converted, then the rule is ignored as per the
799 * functional design */
800 l_total := null;
801 END;
802 END IF;
803
804 IF l_total IS NOT NULL AND l_total > p_audit_all_amount_limit THEN
805 insert_audit_reason(p_report_header_id, 'AMOUNT');
806 p_audit_report := TRUE;
807 END IF;
808 END IF;
809
810 CLOSE report_cur;
811
812 END process_amount;
813
814 /*========================================================================
815 | PRIVATE PROCEDURE process_random_audit
816 |
817 | DESCRIPTION
818 | This procedure randomly selects expense reports for audit.
819 |
820 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
821 | Called from BC4J.
822 |
823 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
824 |
825 | RETURNS
826 |
827 | PARAMETERS
828 | p_report_header_id IN Expense Report identifier
829 | p_random_audit_percentage IN Probability this report will be audited
830 | p_ignore_credit_only_flag IN Ignore reports which contain only credit lines
831 | p_ignore_rj_not_req_only_flag IN Ignore reports which contain only receipts where
832 | receipt and justification is not required.
833 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
834 |
835 | MODIFICATION HISTORY
836 | Date Author Description of Changes
837 | 13-Oct-2002 J Rautiainen Created
838 |
839 *=======================================================================*/
840 PROCEDURE process_random_audit(p_report_header_id IN NUMBER,
841 p_random_audit_percentage IN NUMBER,
842 p_ignore_credit_only_flag IN VARCHAR2,
843 p_ignore_rj_not_req_only_flag IN VARCHAR2,
844 p_audit_report OUT NOCOPY BOOLEAN) IS
845
846 CURSOR debit_cur IS
847 select count(1) debit_line_count
848 from ap_expense_report_lines_all aerl
849 where aerl.report_header_id = p_report_header_id
850 and amount > 0;
851
852 debit_rec debit_cur%ROWTYPE;
853 ln_random_percentage NUMBER;
854 ln_num_rct_req_receipts NUMBER;
855 ln_num_just_req_receipts NUMBER;
856 lb_ignore BOOLEAN := FALSE;
857 BEGIN
858 p_audit_report := FALSE;
859
860 IF p_random_audit_percentage = 0 THEN
861 null;
862 ELSE
863 IF NVL(p_ignore_credit_only_flag, 'N') = 'Y' THEN
864
865 OPEN debit_cur;
866 FETCH debit_cur INTO debit_rec;
867 CLOSE debit_cur;
868
869 IF (debit_rec.debit_line_count = 0) THEN
870 lb_ignore := TRUE;
871 END IF;
872 END IF;
873
874 IF lb_ignore = false AND NVL(p_ignore_rj_not_req_only_flag, 'N') = 'Y' THEN
875
876 IF (NOT AP_WEB_DB_EXPLINE_PKG.GetNumReceiptRequiredLines(p_report_header_id, ln_num_rct_req_receipts)) THEN
877 ln_num_rct_req_receipts := 0;
878 END IF;
879
880 IF (NOT AP_WEB_DB_EXPLINE_PKG.GetNumJustReqdLines(p_report_header_id, ln_num_just_req_receipts)) THEN
881 ln_num_just_req_receipts := 0;
882 END IF;
883
884 IF ( ln_num_rct_req_receipts = 0
885 AND ln_num_just_req_receipts = 0) THEN
886 lb_ignore := TRUE;
887 END IF;
888 END IF;
889
890 IF p_random_audit_percentage = 100
891 AND lb_ignore = FALSE THEN
892
893 insert_audit_reason(p_report_header_id, 'RANDOM');
894 p_audit_report := TRUE;
895
896 ELSIF lb_ignore = FALSE THEN
897
898 ln_random_percentage := AP_WEB_AUDIT_PROCESS.get_random_percentage();
899
900 IF ln_random_percentage < p_random_audit_percentage THEN
901 insert_audit_reason(p_report_header_id, 'RANDOM');
902 p_audit_report := TRUE;
903 END IF;
904
905 END IF;
906 END IF;
907 END process_random_audit;
908
909 /*========================================================================
910 | PRIVATE PROCEDURE process_custom_audit
911 |
912 | DESCRIPTION
913 | This procedure provides customization hook to audit expense reports.
914 |
915 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
916 | Called from BC4J.
917 |
918 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
919 |
920 | RETURNS
921 |
922 | PARAMETERS
923 | p_report_header_id IN Expense Report identifier
924 | p_audit_report OUT NOCOPY TRUE if report needs to be audited
925 | p_override_default_processing OUT NOCOPY TRUE if default audit logic is overridden
926 |
927 | MODIFICATION HISTORY
931 *=======================================================================*/
928 | Date Author Description of Changes
929 | 13-Oct-2002 J Rautiainen Created
930 |
932 PROCEDURE process_custom_audit(p_report_header_id IN NUMBER,
933 p_audit_report OUT NOCOPY BOOLEAN,
934 p_override_default_processing OUT NOCOPY BOOLEAN) IS
935
936 p_audit_reason_code VARCHAR2(30) := null;
937 BEGIN
938 p_audit_report := FALSE;
939
940 AP_WEB_AUDIT_HOOK.audit_expense_report(p_report_header_id,
941 p_audit_reason_code,
942 p_audit_report,
943 p_override_default_processing);
944
945 IF (p_audit_report = TRUE AND p_audit_reason_code IS NOT NULL) THEN
946 insert_audit_reason(p_report_header_id, p_audit_reason_code);
947 END IF;
948
949 END process_custom_audit;
950
951 /*========================================================================
952 | PRIVATE PROCEDURE update_audit_code
953 |
954 | DESCRIPTION
955 | This procedure update expense report header with the audit code.
956 |
957 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
958 | Called from BC4J.
959 |
960 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
961 |
962 | RETURNS
963 |
964 | PARAMETERS
965 | p_report_header_id IN Expense Report identifier
966 | p_audit_code IN Audit code, one of the following:
967 | AUTO_APPROVE
968 | PAPERLESS_AUDIT
969 | AUDIT
970 |
971 | MODIFICATION HISTORY
972 | Date Author Description of Changes
973 | 13-Oct-2002 J Rautiainen Created
974 |
975 *=======================================================================*/
976 PROCEDURE update_audit_code(p_report_header_id IN NUMBER,
977 p_audit_code IN VARCHAR2) IS
978
979 BEGIN
980
981 IF ( p_report_header_id IS NOT NULL
982 AND p_audit_code IS NOT NULL) THEN
983
984 UPDATE ap_expense_report_headers_all erh
985 SET audit_code = p_audit_code
986 WHERE report_header_id = p_report_header_id;
987 END IF;
988
989 EXCEPTION
990 WHEN OTHERS THEN
991 null;
992
993 END update_audit_code;
994
995 /*========================================================================
996 | PRIVATE PROCEDURE process_paperless_audit
997 |
998 | DESCRIPTION
999 | This procedure processes expense report paperless audit.
1000 |
1001 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1002 | Called from BC4J.
1003 |
1004 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1005 |
1006 | RETURNS
1007 |
1008 | PARAMETERS
1009 | p_report_header_id IN Expense Report identifier
1010 |
1011 | MODIFICATION HISTORY
1012 | Date Author Description of Changes
1013 | 13-Oct-2002 J Rautiainen Created
1014 |
1015 *=======================================================================*/
1016 PROCEDURE process_paperless_audit(p_report_header_id IN NUMBER,
1017 p_pl_audit_cc_only_flag IN VARCHAR2,
1018 p_pl_audit_violation_flag IN VARCHAR2,
1019 p_pl_audit_pdm_only_flag IN VARCHAR2,
1020 p_assign_auditor_stage_code IN VARCHAR2,
1021 p_audit_report OUT NOCOPY BOOLEAN) IS
1022
1023 CURSOR cc_cur IS
1024 select count(1) non_cc_line_count
1025 from ap_expense_report_lines_all aerl
1026 where aerl.report_header_id = p_report_header_id
1027 and ( CREDIT_CARD_TRX_ID is null
1028 OR ( CREDIT_CARD_TRX_ID is not null
1029 AND NVL(receipt_required_flag,'N') = 'Y'
1030 )
1031 );
1032
1033 CURSOR pdm_cur IS
1034 select count(1) non_pdm_line_count
1035 from ap_expense_report_lines_all aerl
1036 where aerl.report_header_id = p_report_header_id
1037 and NVL(aerl.category_code,'NONE') not in ('PER_DIEM','MILEAGE');
1038
1039 CURSOR rr_cur IS
1040 select count(1) rr_line_count
1041 from ap_expense_report_lines_all aerl
1042 where aerl.report_header_id = p_report_header_id
1043 and nvl(aerl.receipt_required_flag, 'N') = 'Y';
1044
1045 pdm_rec pdm_cur%ROWTYPE;
1046 cc_rec cc_cur%ROWTYPE;
1047 rr_rec rr_cur%ROWTYPE;
1048 paperless_audit BOOLEAN := FALSE;
1049
1050 BEGIN
1051 p_audit_report := FALSE;
1052 IF (p_pl_audit_cc_only_flag = 'Y') THEN
1053
1054 OPEN cc_cur;
1055 FETCH cc_cur INTO cc_rec;
1056 CLOSE cc_cur;
1057
1058 IF (cc_rec.non_cc_line_count = 0) THEN
1059 paperless_audit := TRUE;
1060 END IF;
1061 END IF;
1062
1063 IF (paperless_audit = FALSE AND p_pl_audit_violation_flag = 'Y') THEN
1064
1068
1065 IF (get_report_violation_count(p_report_header_id) > 0) THEN
1066 paperless_audit := TRUE;
1067 END IF;
1069 END IF;
1070
1071 IF (paperless_audit = FALSE AND p_pl_audit_pdm_only_flag = 'Y') THEN
1072
1073 OPEN pdm_cur;
1074 FETCH pdm_cur INTO pdm_rec;
1075 CLOSE pdm_cur;
1076
1077 IF (pdm_rec.non_pdm_line_count = 0) THEN
1078 paperless_audit := TRUE;
1079 ELSE
1080 OPEN rr_cur;
1081 FETCH rr_cur INTO rr_rec;
1082 CLOSE rr_cur;
1083
1084 IF (rr_rec.rr_line_count = 0) THEN
1085 paperless_audit := TRUE;
1086 END IF;
1087 END IF;
1088
1089 END IF;
1090
1091 IF (paperless_audit = TRUE) THEN
1092 IF (p_assign_auditor_stage_code = 'SUBMISSION') THEN
1093 AP_WEB_AUDIT_QUEUE_UTILS.enqueue_for_audit(p_report_header_id);
1094 END IF;
1095
1096 p_audit_report := TRUE;
1097 END IF;
1098
1099 END process_paperless_audit;
1100
1101 /*========================================================================
1102 | PRIVATE PROCEDURE process_audit_list
1103 |
1104 | DESCRIPTION
1105 | This procedure adds user to automatic audit list if needed.
1106 |
1107 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1108 | Called from BC4J.
1109 |
1110 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1111 |
1112 | RETURNS
1113 |
1114 | PARAMETERS
1115 | p_report_header_id IN Expense Report identifier
1116 |
1117 | MODIFICATION HISTORY
1118 | Date Author Description of Changes
1119 | 13-Oct-2002 J Rautiainen Created
1120 | 11-Mar-2005 Maulik Vadera Bug 4192680: Added extra condition
1121 | to not include both pay personal credit
1122 | card reports in the calculation of
1123 | expense_report_count
1124 *=======================================================================*/
1125 PROCEDURE process_audit_list(p_report_header_id IN NUMBER) IS
1126 CURSOR list_cur IS
1127 select rs.receipt_delay_rule_flag,
1128 rs.receipt_delay_days,
1129 rs.monthly_total_rule_flag,
1130 rs.monthly_total_allowed,
1131 rs.monthly_violations_rule_flag,
1132 rs.monthly_violations_allowed,
1133 rs.monthly_reports_rule_flag,
1134 rs.monthly_reports_allowed,
1135 rs.audit_term_duration_days,
1136 aerh.employee_id,
1137 rs.rule_set_name,
1138 aerh.org_id,
1139 rs.rule_set_type,
1140 rs.start_date,
1141 rs.end_date,
1142 rs.rule_set_id
1143 from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
1144 AP_AUD_RULE_SETS rs,
1145 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1146 where aerh.report_header_id = p_report_header_id
1147 and aerh.org_id = rsa.org_id
1148 and rsa.rule_set_id = rs.rule_set_id
1149 and rs.rule_set_type = 'AUDIT_LIST'
1150 and TRUNC(SYSDATE)
1151 BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
1152 AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
1153
1154 --Bug 4192680: Changed the cursor definition to exclude thee both pay personal credit
1155 --card Report from report count.
1156 CURSOR reports_cur(p_employee_id IN NUMBER, p_org_id IN NUMBER) IS
1157 select sum(NVL(aerh.total,0)*NVL(aerh.default_exchange_rate,1)) expense_report_total,
1158 count(aerh.expense_report_id) expense_report_count
1159 from AP_EXPENSE_REPORT_HEADERS_ALL aerh
1160 where employee_id = p_employee_id
1161 and aerh.org_id = p_org_id
1162 and aerh.report_submitted_date > add_months(sysdate,-1)
1163 and ( aerh.source in ('WebExpense', 'SelfService')
1164 or aerh.report_header_id = p_report_header_id)
1165 and exists(select 'Y'
1166 from AP_EXPENSE_REPORT_LINES_ALL aerl
1167 where aerh.report_header_id = aerl.report_header_id);
1168
1169
1170 list_rec list_cur%ROWTYPE;
1171 reports_rec reports_cur%ROWTYPE;
1172 lb_added boolean := false;
1173
1174 BEGIN
1175 OPEN list_cur;
1176 FETCH list_cur INTO list_rec;
1177
1178 IF list_cur%FOUND THEN
1179
1180 OPEN reports_cur(list_rec.employee_id, list_rec.org_id);
1181 FETCH reports_cur INTO reports_rec;
1182 CLOSE reports_cur;
1183
1184 IF NVL(list_rec.monthly_total_rule_flag, 'N') = 'Y' AND lb_added = false THEN
1185
1186 IF list_rec.monthly_total_allowed < reports_rec.expense_report_total THEN
1187 add_to_audit_list(list_rec.employee_id, list_rec.audit_term_duration_days, 'EXPENSE_TOTAL');
1188 lb_added := true;
1189 END IF;
1190 END IF;
1191
1192 IF NVL(list_rec.monthly_violations_rule_flag, 'N') = 'Y' AND lb_added = false THEN
1193
1194 IF list_rec.monthly_violations_allowed < get_employee_violation_count(list_rec.employee_id, 1) THEN
1195 add_to_audit_list(list_rec.employee_id, list_rec.audit_term_duration_days, 'POLICY_VIOLATION');
1196 lb_added := true;
1197 END IF;
1198 END IF;
1199
1200 IF NVL(list_rec.monthly_reports_rule_flag, 'N') = 'Y' AND lb_added = false THEN
1204 END IF;
1201 IF list_rec.monthly_reports_allowed < reports_rec.expense_report_count THEN
1202 add_to_audit_list(list_rec.employee_id, list_rec.audit_term_duration_days, 'EXPENSE_COUNT');
1203 lb_added := true;
1205 END IF;
1206 END IF; -- list_cur FOUND
1207
1208 CLOSE list_cur;
1209
1210 END process_audit_list;
1211
1212 /*========================================================================
1213 | PUBLIC PROCEDURE add_to_audit_list
1214 |
1215 | DESCRIPTION
1216 | This procedure inserts given employee to audit list.
1217 |
1218 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1219 | Called from BC4J.
1220 |
1221 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1222 |
1223 | RETURNS
1224 |
1225 | PARAMETERS
1226 | p_employee_id IN Employee identifier
1227 | p_reason_code IN Reason code
1228 |
1229 | MODIFICATION HISTORY
1230 | Date Author Description of Changes
1231 | 13-Oct-2002 J Rautiainen Created
1232 |
1233 *=======================================================================*/
1234 PROCEDURE add_to_audit_list(p_employee_id IN NUMBER,
1235 p_duration IN NUMBER,
1236 p_reason_code IN VARCHAR2) IS
1237 PRAGMA AUTONOMOUS_TRANSACTION;
1238
1239 CURSOR audit_cur IS
1240 SELECT auto_audit_id,
1241 employee_id,
1242 audit_reason_code,
1243 start_date,
1244 end_date
1245 FROM ap_aud_auto_audits
1246 WHERE employee_id = p_employee_id
1247 AND trunc(sysdate) between trunc(start_date) and trunc(NVL(end_date, sysdate))
1248 order by end_date desc;
1249
1250 audit_rec audit_cur%ROWTYPE;
1251 record_exists boolean := false;
1252 open_dated boolean := false;
1253 create_record boolean := true;
1254 ld_start_date DATE;
1255 ld_end_date DATE;
1256 BEGIN
1257 IF (p_employee_id IS NOT NULL AND p_reason_code IS NOT NULL) THEN
1258
1259 OPEN audit_cur;
1260 FETCH audit_cur INTO audit_rec;
1261
1262 IF audit_cur%FOUND THEN
1263 record_exists := true;
1264
1265 ld_start_date := audit_rec.start_date;
1266 ld_end_date := SYSDATE-1;
1267 IF ld_start_date > ld_end_date THEN
1268 ld_start_date := ld_end_date;
1269 END IF;
1270
1271 IF audit_rec.end_date is null THEN
1272 open_dated := true;
1273 END IF;
1274 END IF;
1275
1276 CLOSE audit_cur;
1277
1278 IF record_exists = true THEN
1279
1280 IF ( audit_rec.audit_reason_code = 'TERMINATION'
1281 OR audit_rec.audit_reason_code = 'LEAVE_OF_ABSENCE'
1282 OR open_dated = true
1283 ) THEN
1284 create_record := false;
1285 ELSE
1286 UPDATE AP_AUD_AUTO_AUDITS
1287 SET END_DATE = ld_end_date, START_DATE = ld_start_date
1288 WHERE AUTO_AUDIT_ID = audit_rec.auto_audit_id;
1289 END IF;
1290 END IF; -- record_exists = true
1291
1292 IF create_record = true THEN
1293
1294 INSERT INTO AP_AUD_AUTO_AUDITS(
1295 AUTO_AUDIT_ID,
1296 EMPLOYEE_ID,
1297 AUDIT_REASON_CODE,
1298 START_DATE,
1299 END_DATE,
1300 CREATION_DATE,
1301 CREATED_BY,
1302 LAST_UPDATE_LOGIN,
1303 LAST_UPDATE_DATE,
1304 LAST_UPDATED_BY)
1305 VALUES (
1306 AP_AUD_AUTO_AUDITS_S.nextval,
1307 p_employee_id,
1308 p_reason_code,
1309 SYSDATE,
1310 DECODE(p_duration,
1311 null, null,
1312 SYSDATE+p_duration),
1313 SYSDATE,
1314 nvl(fnd_global.user_id, -1),
1315 fnd_global.conc_login_id,
1316 SYSDATE,
1317 nvl(fnd_global.user_id, -1));
1318 END IF; --create_record = true
1319
1320 commit;
1321 END IF;
1322
1323 END add_to_audit_list;
1324
1325 /*========================================================================
1326 | PRIVATE PROCEDURE insert_audit_reason
1327 |
1328 | DESCRIPTION
1329 | This procedure inserts given audit reason code for a expense report.
1330 |
1331 | Changes to this procedure may require changes to update_audit_reason
1332 |
1333 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1334 | Called from BC4J.
1335 |
1336 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1337 |
1338 | RETURNS
1339 |
1340 | PARAMETERS
1341 | p_report_header_id IN Expense Report identifier
1342 | p_audit_reson_code IN Audit reason code
1343 |
1344 | MODIFICATION HISTORY
1345 | Date Author Description of Changes
1346 | 13-Oct-2002 J Rautiainen Created
1347 |
1348 *=======================================================================*/
1349 PROCEDURE insert_audit_reason(p_report_header_id IN NUMBER,
1350 p_audit_reason_code IN VARCHAR2) IS
1351 --PRAGMA AUTONOMOUS_TRANSACTION;
1352
1353 BEGIN
1354 IF (p_report_header_id IS NOT NULL AND p_audit_reason_code IS NOT NULL) THEN
1358 AUDIT_REASON_CODE,
1355 INSERT INTO AP_AUD_AUDIT_REASONS(
1356 AUDIT_REASON_ID,
1357 REPORT_HEADER_ID,
1359 CREATION_DATE,
1360 CREATED_BY,
1361 LAST_UPDATE_LOGIN,
1362 LAST_UPDATE_DATE,
1363 LAST_UPDATED_BY)
1364 VALUES (
1365 AP_AUD_AUDIT_REASONS_S.nextval,
1366 p_report_header_id,
1367 p_audit_reason_code,
1368 SYSDATE,
1369 nvl(fnd_global.user_id, -1),
1370 fnd_global.conc_login_id,
1371 SYSDATE,
1372 nvl(fnd_global.user_id, -1));
1373 commit;
1374 END IF;
1375
1376 END insert_audit_reason;
1377
1378 /*========================================================================
1379 | PRIVATE PROCEDURE update_audit_reason
1380 |
1381 | DESCRIPTION
1382 | This procedure updates the given audit reason code entry
1383 | in AP_AUD_AUDIT_REASONS table for an expense report .
1384 |
1385 | Changes to this procedure may require changes to insert_audit_reason
1386 |
1387 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1388 | Called from BC4J.
1389 |
1390 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1391 |
1392 | RETURNS
1393 |
1394 | PARAMETERS
1395 | p_report_header_id IN Expense Report identifier
1396 | p_audit_reson_code IN Audit reason code
1397 |
1398 | MODIFICATION HISTORY
1399 | Date Author Description of Changes
1400 | 01-May-2008 STALASIL Created
1401 |
1402 *=======================================================================*/
1403 PROCEDURE update_audit_reason(p_report_header_id IN NUMBER,
1404 p_audit_reason_code IN VARCHAR2) IS
1405
1406 BEGIN
1407
1408 IF (p_report_header_id IS NOT NULL AND p_audit_reason_code IS NOT NULL) THEN
1409 UPDATE AP_AUD_AUDIT_REASONS
1410 SET LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
1411 LAST_UPDATE_DATE = SYSDATE,
1412 LAST_UPDATED_BY = nvl(fnd_global.user_id, -1)
1413 WHERE report_header_id = p_report_header_id
1414 AND AUDIT_REASON_CODE = p_audit_reason_code ;
1415 commit;
1416 END IF;
1417
1418 END update_audit_reason;
1419
1420 /*========================================================================
1421 | PUBLIC function get_random_percentage
1422 |
1423 | DESCRIPTION
1424 | Returns a random 2 digit percentage used to decide whether
1425 | expense report is going to be randomly picked
1426 |
1427 | PSEUDO CODE/LOGIC
1428 |
1429 | PARAMETERS
1430 |
1431 | RETURNS
1432 | NUMBER 2 digit number between 0-99
1433 | KNOWN ISSUES
1434 |
1435 | NOTES
1436 |
1437 | MODIFICATION HISTORY
1438 | Date Author Description of Changes
1439 | 13-Oct-2002 J Rautiainen Created
1440 *=======================================================================*/
1441 FUNCTION get_random_percentage RETURN NUMBER IS
1442
1443 ln_random_number NUMBER;
1444 lv_percentage NUMBER;
1445
1446 BEGIN
1447
1448 /*------------------------+
1449 | Get the random number |
1450 +------------------------*/
1451 ln_random_number := FND_CRYPTO.RANDOMNUMBER;
1452
1453
1454 /*-------------------------------------------------------------+
1455 | Construct the random percentage based on the random number. |
1456 +-------------------------------------------------------------*/
1457 lv_percentage := construct_random_percentage(ln_random_number);
1458
1459 return lv_percentage;
1460
1461 END get_random_percentage;
1462
1463 /*========================================================================
1464 | PUBLIC function construct_random_percentage
1465 |
1466 | DESCRIPTION
1467 | Creates a 2 digit random percentage from a random number.
1468 |
1469 | PSEUDO CODE/LOGIC
1470 |
1471 | PARAMETERS
1472 | pn_random_number random integer
1473 |
1474 | RETURNS
1475 | NUMBER 2 digit random percentage
1476 | KNOWN ISSUES
1477 |
1478 | NOTES
1479 |
1480 | MODIFICATION HISTORY
1481 | Date Author Description of Changes
1482 | 14-Oct-2002 J Rautiainen Created
1483 *=======================================================================*/
1484 FUNCTION construct_random_percentage(pn_random_number NUMBER) RETURN NUMBER IS
1485
1486 /*-----------------------------------------------------------+
1487 | Array containing digits 0-9. This is used to map the |
1488 | random number into percentafe digits |
1489 +-----------------------------------------------------------*/
1490 TYPE t_number IS VARRAY(10) OF VARCHAR2(1);
1491 v_numberList t_number := t_number('1','2','3','4','5','6','7','8','9','0');
1492
1493 lv_result VARCHAR2(100) := '';
1494 lv_temp VARCHAR2(100) := '';
1495 lv_random_number VARCHAR2(100);
1496
1497 BEGIN
1498
1499 /*------------------------------------------------------------+
1500 | Convert random number in to a string, we use the string to |
1501 | process the random number 2 characters at time. |
1505 /*-----------------------------------------------------------+
1502 +------------------------------------------------------------*/
1503 lv_random_number := to_char(abs(pn_random_number));
1504
1506 | Loop twice to get the 2 digits for the random percentage. |
1507 +-----------------------------------------------------------*/
1508
1509 FOR i IN 1..2 LOOP
1510
1511 /*--------------------------------------------------------+
1512 | Take 2 character block from the remaining random number|
1513 +--------------------------------------------------------*/
1514 lv_temp := SUBSTR(lv_random_number,1,2);
1515
1516 /*--------------------------------------------------------------+
1517 | Module 10 is used to get the index for the entry in |
1518 | the array. Without this we would get array index out nocopy of |
1519 | bounds error. 1 is added to deal with the fact that the |
1520 | array has indexes from 1 to 10 NOT 0 to 9. |
1521 +--------------------------------------------------------------*/
1522 lv_result := lv_result || v_numberList(mod(to_number(lv_temp),10)+1);
1523
1524 /*---------------------------------------------------------------+
1525 | Remove the processed 2 character block from the random number |
1526 +---------------------------------------------------------------*/
1527 lv_random_number := SUBSTR(lv_random_number,3,length(lv_random_number));
1528
1529 END LOOP;
1530
1531 /*-----------------------------------+
1532 | Return the constructed percentage |
1533 +-----------------------------------*/
1534 return to_number(lv_result);
1535
1536 END construct_random_percentage;
1537
1538 /*========================================================================
1539 | PRIVATE FUNCTION get_report_violation_count
1540 |
1541 | DESCRIPTION
1542 | This function returns the number of policy violation for a given expense report.
1543 |
1544 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1545 | Called from BC4J.
1546 |
1547 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1548 |
1549 | RETURNS
1550 | Count of violations on a given expense report
1551 |
1552 | PARAMETERS
1553 | p_report_header_id IN Expense Report identifier
1554 |
1555 | MODIFICATION HISTORY
1556 | Date Author Description of Changes
1557 | 13-Oct-2002 J Rautiainen Created
1558 |
1559 *=======================================================================*/
1560 FUNCTION get_report_violation_count(p_report_header_id IN NUMBER) RETURN NUMBER IS
1561
1562 CURSOR violation_cur IS
1563 select count(1) violation_count
1564 from AP_POL_VIOLATIONS_ALL
1565 where report_header_id = p_report_header_id;
1566
1567 violation_rec violation_cur%ROWTYPE;
1568
1569 BEGIN
1570 OPEN violation_cur;
1571 FETCH violation_cur INTO violation_rec;
1572 CLOSE violation_cur;
1573
1574 RETURN NVL(violation_rec.violation_count,0);
1575
1576 END get_report_violation_count;
1577
1578 /*========================================================================
1579 | PRIVATE FUNCTION get_report_violation_count
1580 |
1581 | DESCRIPTION
1582 | This function returns the number of policy violation for a given employee
1583 | during last given months.
1584 |
1585 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1586 | Called from BC4J.
1587 |
1588 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1589 |
1590 | RETURNS
1591 | Count of violations for a given employee
1592 |
1593 | PARAMETERS
1594 | p_report_header_id IN Expense Report identifier
1595 | p_months IN Time in months for which reports are examined
1596 |
1597 | MODIFICATION HISTORY
1598 | Date Author Description of Changes
1599 | 13-Oct-2002 J Rautiainen Created
1600 |
1601 *=======================================================================*/
1602 FUNCTION get_employee_violation_count(p_employee_id IN NUMBER,
1603 p_months IN NUMBER) RETURN NUMBER IS
1604
1605 CURSOR violation_cur IS
1606 select count(1) violation_count
1607 from AP_POL_VIOLATIONS_ALL viol,
1608 AP_EXPENSE_REPORT_HEADERS_ALL aerh
1609 where aerh.employee_id = p_employee_id
1610 and viol.report_header_id = aerh.report_header_id
1611 and aerh.week_end_date > add_months(sysdate,-p_months);
1612
1613 violation_rec violation_cur%ROWTYPE;
1614
1615 BEGIN
1616 OPEN violation_cur;
1617 FETCH violation_cur INTO violation_rec;
1618 CLOSE violation_cur;
1619
1620 RETURN NVL(violation_rec.violation_count,0);
1621
1622 END get_employee_violation_count;
1623
1624 /**
1625 * jrautiai ADJ Fix end
1626 */
1627
1628 /*========================================================================
1629 | PUBLIC PROCEDURE process_audit_actions
1630 |
1631 | DESCRIPTION
1632 | This procedure deals with auditor adjustments. This logic is called
1633 | when audit is completed and it deals with adjustments in reimbursable
1634 | amount and shortpayments.
1635 |
1639 |
1636 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1637 | Called from expense report form and HTML UI when auditor
1638 | completes audit.
1640 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1641 |
1642 | RETURNS
1643 | None
1644 |
1645 | PARAMETERS
1646 | p_report_header_id IN Expense report to processed
1647 |
1648 | MODIFICATION HISTORY
1649 | Date Author Description of Changes
1650 | 15-Jul-2003 J Rautiainen Created
1651 |
1652 *=======================================================================*/
1653 PROCEDURE process_audit_actions(p_report_header_id IN NUMBER) IS
1654
1655 CURSOR report_lines_c IS
1656 SELECT AERL.*
1657 FROM AP_EXPENSE_REPORT_LINES_ALL AERL
1658 WHERE REPORT_HEADER_ID = p_report_header_id
1659 AND (itemization_parent_id is null OR itemization_parent_id <> -1)
1660 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
1661
1662 report_lines_rec report_lines_c%ROWTYPE;
1663
1664 ln_next_itemization_id NUMBER;
1665 ln_personal_expense_id NUMBER;
1666 l_payment_due VARCHAR2(10);
1667
1668 BEGIN
1669
1670 OPEN report_lines_c;
1671
1672 LOOP
1673 FETCH report_lines_c into report_lines_rec;
1674 EXIT WHEN report_lines_c%NOTFOUND;
1675
1676 /**
1677 * Check whether a line was adjusted
1678 */
1679 IF report_lines_rec.AMOUNT <> NVL(report_lines_rec.SUBMITTED_AMOUNT, report_lines_rec.AMOUNT) THEN
1680
1681 /**
1682 * Line was adjusted, Update the CC transaction with the adjustment for credit card line
1683 */
1684 IF report_lines_rec.CREDIT_CARD_TRX_ID IS NOT NULL THEN
1685 update_cc_transaction(report_lines_rec);
1686 END IF; -- report_lines_rec.CREDIT_CARD_TRX_ID is null
1687
1688 END IF; -- if line was adjusted
1689
1690 END LOOP;
1691
1692 CLOSE report_lines_c;
1693
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 AP_WEB_DB_UTIL_PKG.RaiseException('process_audit_actions');
1697 APP_EXCEPTION.RAISE_EXCEPTION;
1698 END process_audit_actions;
1699
1700 /*========================================================================
1701 | PUBLIC PROCEDURE update_cc_transaction
1702 |
1703 | DESCRIPTION
1704 | This procedure updates the CC transaction amounts to match the
1705 | amounts on the expense line.
1706 |
1707 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1708 | process_audit_actions
1709 |
1710 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1711 |
1712 | RETURNS
1713 | None
1714 | PARAMETERS
1715 | Expense line record containing the data on the modified expense line
1716 |
1717 | MODIFICATION HISTORY
1718 | Date Author Description of Changes
1719 | 21-Jul-2003 J Rautiainen Created
1720 |
1721 *=======================================================================*/
1722 PROCEDURE update_cc_transaction(expense_line_rec IN AP_EXPENSE_REPORT_LINES_ALL%ROWTYPE) IS
1723
1724 CURSOR trx_c(p_trx_id IN NUMBER) IS
1725 SELECT cct.*
1726 FROM AP_CREDIT_CARD_TRXNS_ALL cct
1727 WHERE cct.trx_id = p_trx_id
1728 FOR UPDATE OF TRX_ID NOWAIT;
1729
1730 CURSOR total_c(p_report_header_id IN NUMBER, p_trx_id IN NUMBER, p_personal_expense_id IN NUMBER) IS
1731 SELECT sum(amount) total_amount
1732 FROM AP_EXPENSE_REPORT_LINES_ALL
1733 WHERE report_header_id = p_report_header_id
1734 AND web_parameter_id <> p_personal_expense_id
1735 AND credit_card_trx_id = p_trx_id
1736 AND (itemization_parent_id is null OR itemization_parent_id <> -1);
1737
1738 trx_rec trx_c%ROWTYPE;
1739 total_rec total_c%ROWTYPE;
1740 ln_personal_expense_id NUMBER;
1741
1742 BEGIN
1743 IF expense_line_rec.credit_card_trx_id IS NOT NULL THEN
1744
1745 ln_personal_expense_id := AP_WEB_AUDIT_UTILS.get_personal_expense_id;
1746
1747 OPEN total_c(expense_line_rec.report_header_id, expense_line_rec.credit_card_trx_id, ln_personal_expense_id);
1748 FETCH total_c into total_rec;
1749 CLOSE total_c;
1750
1751 OPEN trx_c(expense_line_rec.credit_card_trx_id);
1752 FETCH trx_c into trx_rec;
1753
1754 UPDATE AP_CREDIT_CARD_TRXNS_ALL
1755 SET expensed_amount = total_rec.total_amount
1756 WHERE CURRENT OF trx_c;
1757
1758 CLOSE trx_c;
1759
1760 IF (total_rec.total_amount=0) THEN -- Bug 6628290 (sodash) when the Expensed Amount is zero, set the category as personal for the Credit Card Trxn.
1761
1762 UPDATE AP_CREDIT_CARD_TRXNS_ALL
1763 SET category = 'PERSONAL'
1764 WHERE trx_id = expense_line_rec.credit_card_trx_id;
1765
1766 END IF;
1767
1768
1769 END IF;
1770 END update_cc_transaction;
1771
1772
1773 /*========================================================================
1774 | PUBLIC PROCEDURE process_shortpays
1775 |
1776 | DESCRIPTION
1777 | This procedure processes shortpayments on a line, namely if one of
1778 | itemized lines is shortpaid, then all the itemized lines are
1782 | process_audit_actions
1779 | shortpaid as well.
1780 |
1781 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1783 |
1784 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1785 |
1786 | RETURNS
1787 | None
1788 | PARAMETERS
1789 | Expense line record containing the data on the modified expense line
1790 |
1791 | MODIFICATION HISTORY
1792 | Date Author Description of Changes
1793 | 21-Jul-2003 J Rautiainen Created
1794 |
1795 *=======================================================================*/
1796 PROCEDURE process_shortpays(expense_line_rec IN AP_EXPENSE_REPORT_LINES_ALL%ROWTYPE) IS
1797 BEGIN
1798 IF (expense_line_rec.report_header_id IS NOT NULL
1799 AND expense_line_rec.itemization_parent_id IS NOT NULL) THEN
1800
1801 /*
1802 * When a itemized line is shortpaid, all the other itemized lines will be shortpaid also.
1803 * In addition for company pay there might exist a personal row, which is not itemized,
1804 * with the CC cc transaction, but has to also be shortpaid when any of the other
1805 * itemization is shortpaid. So the query has OR statement which will deal with
1806 * cash and CC lines separately. For cash the itemized lines are shortpaid, for
1807 * CC transactions all lines belonging to the same transactions are shortpaid
1808 */
1809 UPDATE AP_EXPENSE_REPORT_LINES_ALL
1810 SET policy_shortpay_flag = DECODE(expense_line_rec.policy_shortpay_flag,
1811 'Y','Y',
1812 policy_shortpay_flag),
1813 receipt_verified_flag = DECODE(NVL(expense_line_rec.receipt_verified_flag,'N'),
1814 'N','N',
1815 receipt_verified_flag)
1816 WHERE report_header_id = expense_line_rec.report_header_id
1817 AND (itemization_parent_id = expense_line_rec.itemization_parent_id
1818 OR
1819 /* Deal with itemized personal CC line */
1820 ( expense_line_rec.credit_card_trx_id IS NOT NULL
1821 AND credit_card_trx_id = expense_line_rec.credit_card_trx_id)
1822 );
1823 END IF;
1824 END process_shortpays;
1825
1826
1827 /*========================================================================
1828 | PUBLIC PROCEDURE process_rate_rounding
1829 |
1830 | DESCRIPTION
1831 | This procedure calculates and creates any rounding lines needed due
1832 | to rounding issues.
1833 |
1834 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1835 | process_audit_actions
1836 |
1837 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1838 |
1839 | RETURNS
1840 | None
1841 |
1842 | PARAMETERS
1843 | Expense report header identifier to be processed
1844 |
1845 | MODIFICATION HISTORY
1846 | Date Author Description of Changes
1847 | 21-Jul-2003 J Rautiainen Created
1848 |
1849 *=======================================================================*/
1850 PROCEDURE process_rate_rounding(p_report_header_id IN NUMBER) IS
1851
1852 CURSOR totals_c(p_personal_expense_id IN NUMBER, p_rounding_expense_id IN NUMBER) IS
1853 SELECT itemization_parent_id,
1854 credit_card_trx_id,
1855 sum(amount) total_amount,
1856 sum(daily_amount
1857 * NVL((end_expense_date - start_expense_date)+1,1)
1858 * NVL(receipt_conversion_rate,1)) total_daily_amount,
1859 currency_code
1860 FROM ap_expense_report_lines_all
1861 WHERE report_header_id = p_report_header_id
1862 AND currency_code <> NVL(receipt_currency_code,currency_code)
1863 AND web_parameter_id <> p_personal_expense_id
1864 AND web_parameter_id <> p_rounding_expense_id
1865 AND itemization_parent_id is not null
1866 AND itemization_parent_id <> -1 -- do not include parent line
1867 GROUP BY itemization_parent_id, credit_card_trx_id, currency_code;
1868
1869 CURSOR rounding_line_c(p_itemization_parent_id IN NUMBER, p_trx_id IN NUMBER, p_rounding_expense_id IN NUMBER) IS
1870 SELECT AERL.*
1871 FROM AP_EXPENSE_REPORT_LINES_ALL AERL
1872 WHERE REPORT_HEADER_ID = p_report_header_id
1873 AND WEB_PARAMETER_ID = p_rounding_expense_id
1874 AND ITEMIZATION_PARENT_ID = p_itemization_parent_id
1875 AND ( (p_trx_id IS NULL AND credit_card_trx_id is NULL)
1876 OR
1877 (p_trx_id IS NOT NULL AND credit_card_trx_id IS NOT NULL)
1878 )
1879 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
1880
1881 CURSOR lines_c(p_itemization_parent_id IN NUMBER, p_trx_id IN NUMBER, p_personal_expense_id IN NUMBER) IS
1882 SELECT AERL.*
1883 FROM AP_EXPENSE_REPORT_LINES_ALL AERL
1884 WHERE REPORT_HEADER_ID = p_report_header_id
1885 AND WEB_PARAMETER_ID <> p_personal_expense_id
1886 AND ITEMIZATION_PARENT_ID = p_itemization_parent_id
1887 AND ( (p_trx_id IS NULL AND credit_card_trx_id is NULL)
1888 OR
1889 (p_trx_id IS NOT NULL AND credit_card_trx_id IS NOT NULL)
1890 )
1891 ORDER BY distribution_line_number;
1892
1893 CURSOR rounding_expense_c(p_parameter_id IN NUMBER) IS
1894 SELECT parameter_id,
1895 LINE_TYPE_LOOKUP_CODE,
1896 category_code, prompt
1900 CURSOR distribution_c(p_report_line_id IN NUMBER) IS
1897 FROM ap_expense_report_params_all
1898 WHERE parameter_id = p_parameter_id;
1899
1901 SELECT REPORT_HEADER_ID
1902 FROM AP_EXP_REPORT_DISTS
1903 WHERE (REPORT_LINE_ID = p_report_line_id)
1904 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
1905
1906 rounding_line_rec rounding_line_c%ROWTYPE;
1907 rounding_expense_rec rounding_expense_c%ROWTYPE;
1908 lines_rec lines_c%ROWTYPE;
1909 new_report_line_rec AP_EXPENSE_REPORT_LINES_ALL%ROWTYPE;
1910 distribution_rec distribution_c%ROWTYPE;
1911 l_exp_line_ccid AP_WEB_DB_EXPLINE_PKG.expLines_codeCombID;
1912
1913 ln_total_amount NUMBER;
1914 ln_total_daily_amount NUMBER;
1915 ln_rounding_amount NUMBER;
1916
1917 ln_personal_expense_id NUMBER;
1918 ln_rounding_expense_id NUMBER;
1919 ln_next_dist_line_number NUMBER;
1920
1921 l_precision NUMBER := 0;
1922 l_extended_precision NUMBER := 0;
1923 l_min_acct_unit NUMBER := 0;
1924
1925 BEGIN
1926 ln_personal_expense_id := AP_WEB_AUDIT_UTILS.get_seeded_expense_id('PERSONAL');
1927 ln_rounding_expense_id := AP_WEB_AUDIT_UTILS.get_seeded_expense_id('ROUNDING');
1928
1929 FOR totals_rec IN totals_c(ln_personal_expense_id, ln_rounding_expense_id) LOOP
1930
1931 fnd_currency.get_info(totals_rec.CURRENCY_CODE,l_precision,l_extended_precision,l_min_acct_unit);
1932 ln_total_amount := ROUND(totals_rec.total_amount, l_precision);
1933 ln_total_daily_amount := ROUND(totals_rec.total_daily_amount, l_precision);
1934
1935 IF ln_total_amount <> ln_total_daily_amount THEN
1936
1937 /**
1938 * Find the a existing line for the itemization to copy the data from
1939 * Since the information we use are identical on all itemized lines the
1940 * cursor is ordered by distribution_line_number, so we are taking the
1941 * information from the line first created within the itemization.
1942 */
1943 OPEN lines_c(totals_rec.itemization_parent_id, totals_rec.credit_card_trx_id, ln_personal_expense_id);
1944 FETCH lines_c into lines_rec;
1945 CLOSE lines_c;
1946
1947 ln_rounding_amount := ln_total_daily_amount-ln_total_amount;
1948
1949 OPEN rounding_line_c(totals_rec.itemization_parent_id, totals_rec.credit_card_trx_id, ln_rounding_expense_id);
1950 FETCH rounding_line_c into rounding_line_rec;
1951
1952 IF rounding_line_c%FOUND THEN
1953
1954 UPDATE AP_EXPENSE_REPORT_LINES_ALL
1955 SET amount = ln_rounding_amount
1956 WHERE CURRENT OF rounding_line_c;
1957
1958 CLOSE rounding_line_c;
1959 ELSE
1960 CLOSE rounding_line_c;
1961
1962 /**
1963 * Itemized rounding line does not exists, we need
1964 * to create a new rounding line for the adjusted amount.
1965 */
1966
1967 /**
1968 * Find the expense type information for rounding expenses.
1969 */
1970 OPEN rounding_expense_c(ln_rounding_expense_id);
1971 FETCH rounding_expense_c into rounding_expense_rec;
1972 CLOSE rounding_expense_c;
1973
1974 /**
1975 * Fetch the rounding error account to which the rounding amount is accounted.
1976 */
1977 l_exp_line_ccid := AP_WEB_AUDIT_UTILS.get_rounding_error_ccid(lines_rec.org_id);
1978
1979 ln_next_dist_line_number := AP_WEB_AUDIT_UTILS.get_next_distribution_line_id(p_report_header_id);
1980
1981 /**
1982 * Populate the new rounding row with data from the original row and overwriting
1983 * the following data.
1984 */
1985 new_report_line_rec.report_header_id := lines_rec.report_header_id;
1986 new_report_line_rec.distribution_line_number := ln_next_dist_line_number;
1987 new_report_line_rec.itemization_parent_id := lines_rec.itemization_parent_id;
1988 new_report_line_rec.amount := ln_rounding_amount;
1989 new_report_line_rec.web_parameter_id := rounding_expense_rec.parameter_id;
1990 -- new_report_line_rec.code_combination_id := l_exp_line_ccid;
1991 new_report_line_rec.item_description := rounding_expense_rec.line_type_lookup_code;
1992 new_report_line_rec.line_type_lookup_code := rounding_expense_rec.line_type_lookup_code;
1993 new_report_line_rec.start_expense_date := lines_rec.start_expense_date;
1994 new_report_line_rec.currency_code := lines_rec.currency_code;
1995 new_report_line_rec.receipt_currency_code := lines_rec.receipt_currency_code;
1996 new_report_line_rec.set_of_books_id := lines_rec.set_of_books_id;
1997 new_report_line_rec.org_id := lines_rec.org_id;
1998 new_report_line_rec.last_update_login := FND_GLOBAL.login_id;
1999 new_report_line_rec.last_updated_by := FND_GLOBAL.user_id;
2000 new_report_line_rec.last_update_date := SYSDATE;
2001 new_report_line_rec.created_by := FND_GLOBAL.user_id;
2002 new_report_line_rec.creation_date := SYSDATE;
2003 new_report_line_rec.category_code := 'ROUNDING';
2004 new_report_line_rec.justification_required_flag := 'N';
2005
2006 AP_WEB_DB_EXPLINE_PKG.InsertLine(new_report_line_rec);
2007
2008 OPEN rounding_line_c(totals_rec.itemization_parent_id, totals_rec.credit_card_trx_id, ln_rounding_expense_id);
2009 FETCH rounding_line_c into rounding_line_rec;
2010 CLOSE rounding_line_c;
2011 /* Create distribution line */
2012 AP_WEB_DB_EXPDIST_PKG.updateAccountValues (
2013 p_report_header_id => rounding_line_rec.report_header_id,
2014 p_report_line_id => rounding_line_rec.report_line_id,
2015 p_report_distribution_id => null,
2016 p_ccid => l_exp_line_ccid);
2017
2018 END IF; -- rounding_line_c%FOUND
2019 ELSE -- ln_total_amount = ln_total_daily_amount
2020
2021 OPEN rounding_line_c(totals_rec.itemization_parent_id, totals_rec.credit_card_trx_id, ln_rounding_expense_id);
2022 FETCH rounding_line_c into rounding_line_rec;
2023
2024 IF rounding_line_c%FOUND THEN
2025 DELETE AP_EXPENSE_REPORT_LINES_ALL
2026 WHERE CURRENT OF rounding_line_c;
2027 END IF;
2028 CLOSE rounding_line_c;
2029
2030
2031 OPEN distribution_c(rounding_line_rec.report_line_id);
2032 FETCH distribution_c into distribution_rec;
2033 IF distribution_c%FOUND THEN
2034 DELETE AP_EXP_REPORT_DISTS_ALL
2035 WHERE CURRENT OF distribution_c;
2036 END IF;
2037 CLOSE distribution_c;
2038
2039 END IF; -- ln_total_amount <> ln_total_daily_amount
2040 END LOOP;
2041
2042 END process_rate_rounding;
2043
2044 /*========================================================================
2045 | PUBLIC FUNCTION bothpay_personal_cc_only
2046 |
2047 | DESCRIPTION
2048 | This function checks if the report has only bothpay personal credit card expenses
2049 |
2050 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2051 | Called from function process_expense_report(p_report_header_id IN NUMBER)
2052 |
2053 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2054 |
2055 | RETURNS
2056 | TRUE in case the report has only bothpay personal credit card expenses,
2057 | otherwise FALSE
2058 |
2059 | PARAMETERS
2060 | p_report_header_id IN Expense Report identifier
2061 |
2062 | MODIFICATION HISTORY
2063 | Date Author Description of Changes
2064 | 28-Feb-2005 Maulik Vadera Created
2065 |
2066 *=======================================================================*/
2067
2068 FUNCTION bothpay_personal_cc_only(p_report_header_id IN NUMBER) RETURN BOOLEAN
2069 IS
2070 line_count NUMBER(15);
2071 BEGIN
2072
2073 SELECT COUNT(REPORT_LINE_ID) into line_count
2074 FROM AP_EXPENSE_REPORT_LINES_ALL
2075 WHERE REPORT_HEADER_ID = p_report_header_id;
2076
2077 IF line_count = 0 THEN
2078 RETURN TRUE;
2079 ELSE
2080 RETURN FALSE;
2081 END IF;
2082
2083 END bothpay_personal_cc_only;
2084
2085
2086 /**
2087 * jrautiai ADJ Fix end
2088 */
2089 END AP_WEB_AUDIT_PROCESS;
[Home] [Help]