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