1 PACKAGE BODY AP_WEB_AUDIT_QUEUE_UTILS AS
2 /* $Header: apwaudqb.pls 120.7.12010000.2 2008/08/06 07:42:41 rveliche ship $ */
3
4 /*=======================================================================+
5 | Global Constants
6 +=======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_WEB_AUDIT_QUEUE_UTILS';
9
10 ------------------------------------------------------------------------
11 -- Local Procedure Signature
12 ------------------------------------------------------------------------
13 PROCEDURE open_auditors_cur_w(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
14 p_auditor_info_cur OUT NOCOPY GenRefCursor);
15
16 PROCEDURE open_load_cur(p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
17 p_open_load_cur OUT NOCOPY GenRefCursor);
18
19 PROCEDURE validate_auditors;
20
21 PROCEDURE open_orphaned_reports_cursor(p_expense_report_cur OUT NOCOPY GenRefCursor);
22
23 FUNCTION find_enqueue_auditor(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
24 RETURN AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
25
26 FUNCTION report_last_audited(p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
27 RETURN BOOLEAN;
28
29 FUNCTION auditor_has_access(
30 p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
31 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
32 RETURN BOOLEAN;
33
34 PROCEDURE transfer_report(p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
35 p_next_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
36 p_expense_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE);
37
38 ------------------------------------------------------------------------
39 -- Procedure Result Codes
40 ------------------------------------------------------------------------
41
42 --------------------------------------------------------------------------
43 -- Registration Status Constants
44 --------------------------------------------------------------------------
45
46 --------------------------------------------------------------------------
47 -- Access Domain Type Constants
48 --------------------------------------------------------------------------
49
50 /*========================================================================
51 | PUBLIC FUNCTION report_weight
52 |
53 | DESCRIPTION
54 | This function return high threshold for a given low value. Currently
55 | this is simply a function of the number of lines in the report.
56 |
57 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
58 |
59 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
60 |
61 | PARAMETERS
62 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
63 |
64 | MODIFICATION HISTORY
65 | Date Author Description of Changes
66 | 04-Sep-2002 Mohammad Shoaib Jamall Created
67 |
68 *=======================================================================*/
69 FUNCTION report_weight(p_report_header_id IN
70 AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE) RETURN NUMBER
71 IS
72 l_report_weight NUMBER := 0;
73 BEGIN
74 SELECT count(1) INTO l_report_weight FROM ap_expense_report_lines_all
75 WHERE report_header_id = p_report_header_id
76 AND (itemization_parent_id is null OR itemization_parent_id <> -1);
77
78 RETURN(l_report_weight);
79 END report_weight;
80
81 /*========================================================================
82 | PUBLIC PROCEDURE assign_report_to_auditor
83 |
84 | DESCRIPTION
85 | This procedure enqueus a report_header_id for audit:
86 | - Takes in report_header_id and auditor_id
87 | - If report already in the queue under this auditor do nothing
88 | - If report not in the queue, enqueue it for this auditor if this
89 | auditor is not owner of report.
90 | - If report already in the queue under another auditor transfer
91 | the report under this auditor if auditor is not owner of report.
92 | - p_retcode is returned with:
93 | 'INVALID_ASSIGNMENT' -> if the auditor is the owner of report and
94 | cannot be assigned the report.
95 | 'SUCCESS' -> if everything went ok.
96 |
97 | CALLED FROM PROCEDURES/FUNCTIONS (workflow/local to this package body)
98 |
99 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
100 |
101 | PARAMETERS
102 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
103 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
104 |
105 | MODIFICATION HISTORY
106 | Date Author Description of Changes
107 | 05-Sep-2002 Mohammad Shoaib Jamall Created
108 |
109 *=======================================================================*/
110 PROCEDURE assign_report_to_auditor(
111 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
112 p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
113 p_retcode OUT NOCOPY VARCHAR2)
114 IS
115 l_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
116 l_aud_queue_cur GenRefCursor;
117 l_scratch NUMBER := 0;
118 BEGIN
119 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start assign_report_to_auditor');
120 IF (p_report_header_id IS NOT NULL) AND (p_auditor_id IS NOT NULL) THEN
121 OPEN l_aud_queue_cur FOR
122 SELECT auditor_id FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;
123 FETCH l_aud_queue_cur INTO l_auditor_id;
124
125 -- is p_auditor_id the creator or employee for p_report_header_id; if yes then we should
126 -- return with INVALID_ASSIGNMENT; else proceed normally.
127 SELECT count(1) INTO l_scratch
128 FROM fnd_user u,
129 ap_expense_report_headers_all exp
130 WHERE u.user_id <> -1
131 AND u.user_id = p_auditor_id
132 -- making sure auditor does not audit expense report owned by her OR filed by her
133 AND exp.report_header_id = p_report_header_id
134 AND (nvl(u.employee_id , -1) = nvl(exp.employee_id , -2) OR nvl(u.user_id , -1) = nvl(exp.created_by , -2));
135
136 IF (l_scratch > 0) THEN
137 p_retcode := G_OIE_AUD_INVALID_ASSIGNMENT;
138 return;
139 END IF;
140
141 IF l_aud_queue_cur%NOTFOUND THEN
142 -- report not in queue
143 --------------------------------------------------------------------------
144 INSERT INTO ap_aud_queues(expense_report_id,
145 auditor_id,
146 report_weight,
147 creation_date,
148 created_by,
149 last_update_login,
150 last_update_date,
151 last_updated_by)
152 VALUES ( p_report_header_id,
153 p_auditor_id,
154 report_weight(p_report_header_id),
155 sysdate,
156 FND_GLOBAL.USER_ID,
157 null,
158 sysdate,
159 FND_GLOBAL.USER_ID);
160 --------------------------------------------------------------------------
161 ELSIF (l_auditor_id <> p_auditor_id) THEN
162 -- report in queue but needs to be reassigned to p_auditor_id
163 transfer_report(l_auditor_id, p_auditor_id, p_report_header_id);
164 --------------------------------------------------------------------------
165 --------------------------------------------------------------------------
166 END IF;
167 END IF;
168
169 p_retcode := G_OIE_AUD_SUCCESS;
170 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end assign_report_to_auditor');
171 END assign_report_to_auditor;
172
173 /*========================================================================
174 | PUBLIC PROCEDURE assign_to_last_auditor
175 |
176 | DESCRIPTION
177 | This procedure assigns a returned Report back to the same Auditor
178 | who last Audited the report. Does not consider the Auditor Load
179 |
180 | CALLED FROM PROCEDURES/FUNCTIONS (workflow/local to this package body)
181 |
182 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
183 |
184 | PARAMETERS
185 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
186 |
187 | MODIFICATION HISTORY
188 | Date Author Description of Changes
189 | 22-Feb-2008 SaiKumar Talasila Created
190 |
191 *=======================================================================*/
192 PROCEDURE assign_to_last_auditor(p_report_header_id IN
193 AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
194 IS
195 l_assignee NUMBER;
196 l_retcode VARCHAR2(200);
197 invalid_assig_to_audit_q EXCEPTION;
198 BEGIN
199 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start assign_to_last_auditor');
200
201 l_assignee := NULL;
202 IF report_last_audited(p_report_id => p_report_header_id) THEN
203 SELECT h.last_audited_by INTO l_assignee
204 FROM AP_EXPENSE_REPORT_HEADERS_ALL h
205 WHERE h.report_header_id = p_report_header_id;
206
207 IF( nvl(l_assignee,-1) <> -1 AND
208 auditor_has_access(p_auditor_id => l_assignee,
209 p_report_header_id => p_report_header_id) ) THEN
210 assign_report_to_auditor(p_report_header_id, l_assignee, l_retcode);
211 IF (G_OIE_AUD_INVALID_ASSIGNMENT = l_retcode) THEN
212 raise invalid_assig_to_audit_q;
213 END IF;
214 END IF;
215 END IF;
216
217 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end assign_to_last_auditor');
218
219 END assign_to_last_auditor;
220
221
222 /*========================================================================
223 | PUBLIC PROCEDURE enqueue_for_audit
224 |
225 | DESCRIPTION
226 | This procedure enqueus a report_header_id for audit
227 |
228 | CALLED FROM PROCEDURES/FUNCTIONS (workflow/local to this package body)
229 |
230 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
231 |
232 | PARAMETERS
233 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
234 |
235 | MODIFICATION HISTORY
236 | Date Author Description of Changes
237 | 05-Sep-2002 Mohammad Shoaib Jamall Created
238 |
239 *=======================================================================*/
240 PROCEDURE enqueue_for_audit(p_report_header_id IN
241 AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
242 IS
243 l_assignee NUMBER;
244 l_retcode VARCHAR2(200);
245 invalid_assig_to_audit_q EXCEPTION;
246 BEGIN
247 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start enqueue_for_audit');
248 l_assignee := find_enqueue_auditor(p_report_header_id);
249 assign_report_to_auditor(p_report_header_id, l_assignee, l_retcode);
250 IF (G_OIE_AUD_INVALID_ASSIGNMENT = l_retcode) THEN
251 raise invalid_assig_to_audit_q;
252 END IF;
253 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end enqueue_for_audit');
254
255 END enqueue_for_audit;
256
257 /*========================================================================
258 | PUBLIC PROCEDURE rebalance_queue
259 |
260 | DESCRIPTION
261 | This procedure enqueus a report_header_id for audit
262 |
263 | CALLED FROM PROCEDURES/FUNCTIONS (workflow/local to this package body)
264 |
265 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
266 |
267 | PARAMETERS
268 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
269 |
270 | MODIFICATION HISTORY
271 | Date Author Description of Changes
272 | 05-Sep-2002 Mohammad Shoaib Jamall Created
273 |
274 *=======================================================================*/
275 PROCEDURE rebalance_queue(p_auditor_id IN
276 AP_AUD_AUDITORS.AUDITOR_ID%TYPE)
277 IS
278 l_next_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
279 l_expense_report_id NUMBER;
280 CURSOR c1 IS SELECT expense_report_id
281 FROM ap_aud_queues
282 WHERE auditor_id = p_auditor_id order by last_update_date desc;
283 BEGIN
284 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start rebalance_queue');
285 FOR rec IN c1 LOOP
286 l_expense_report_id := rec.expense_report_id;
287 EXIT WHEN l_expense_report_id IS NULL;
288 l_next_auditor_id := find_enqueue_auditor(l_expense_report_id);
289 -- If the report has not been audited previously, exit when the next
290 -- auditor found is the same as the current auditor we are rebalancing for.
291 IF NOT report_last_audited(p_report_id => l_expense_report_id) THEN
292 EXIT WHEN l_next_auditor_id = p_auditor_id;
293 END IF;
294 transfer_report(p_auditor_id, l_next_auditor_id, l_expense_report_id);
295
296 END LOOP;
297 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end rebalance_queue');
298 END rebalance_queue;
299
300
301 /*========================================================================
302 | PUBLIC PROCEDURE open_load_cur
303 |
304 | DESCRIPTION
305 | This procedure opens cursor containing the open cursor for the
306 | current entry in ap_aud_workloads for auditor_id. It should
307 | return at most 1 row.
308 |
309 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
310 |
311 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
312 |
313 | PARAMETERS
314 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
315 | p_open_load_cur IN GenRefCursor
316 |
317 | MODIFICATION HISTORY
318 | Date Author Description of Changes
319 | 05-Sep-2002 Mohammad Shoaib Jamall Created
320 |
321 *=======================================================================*/
322 PROCEDURE open_load_cur(p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
323 p_open_load_cur OUT NOCOPY GenRefCursor)
324 IS
325
326 BEGIN
327 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start open_load_cur:' || p_auditor_id);
328 OPEN p_open_load_cur FOR
329 SELECT s.current_total_workload,
330 w.workload_percent
331 FROM
332 ap_aud_auditors a,
333 ap_aud_queue_summaries_v s,
334 ap_aud_workloads w
335 WHERE a.auditor_id = p_auditor_id
336 AND a.auditor_id = s.auditor_id(+)
337 AND a.auditor_id = w.auditor_id
338 AND w.start_date <= sysdate
339 AND sysdate < nvl(w.end_date,sysdate+1);
340
341
342 END open_load_cur;
343
344 /*========================================================================
345 | PUBLIC PROCEDURE open_auditors_cur_w
346 |
347 | DESCRIPTION
348 | This procedure opens cursor containing auditor info for auditors
349 | who can audit for the report header id
350 |
351 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
352 |
353 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
354 |
355 | PARAMETERS
356 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
357 | p_cust_acct_cur IN GenRefCursor
358 |
359 | MODIFICATION HISTORY
360 | Date Author Description of Changes
361 | 05-Sep-2002 Mohammad Shoaib Jamall Created
362 |
363 *=======================================================================*/
364 PROCEDURE open_auditors_cur_w(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
365 p_auditor_info_cur OUT NOCOPY GenRefCursor)
366 IS
367 l_org_id AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE;
368 BEGIN
369 SELECT ORG_ID INTO l_org_id FROM ap_expense_report_headers_all
370 WHERE report_header_id = p_report_header_id;
371 open_auditors_info_cursor(p_report_header_id => p_report_header_id, p_org_id => l_org_id, p_auditor_info_cur => p_auditor_info_cur);
372 END open_auditors_cur_w;
373
377 | DESCRIPTION
374 /*========================================================================
375 | PUBLIC PROCEDURE open_auditors_info_cursor
376 |
378 | This procedure opens cursor containing auditor info for auditors
379 | who can audit for the org_id. Customers can customize this for
380 | their own business logic.
381 |
382 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
383 |
384 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
385 |
386 | PARAMETERS
387 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
388 | p_org_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
389 | p_cust_acct_cur IN GenRefCursor
390 |
391 | MODIFICATION HISTORY
392 | Date Author Description of Changes
393 | 05-Sep-2002 Mohammad Shoaib Jamall Created
394 |
395 *=======================================================================*/
396 PROCEDURE open_auditors_info_cursor(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
397 p_org_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE,
398 p_auditor_info_cur OUT NOCOPY GenRefCursor)
399 IS
400
401 BEGIN
402 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start open_auditors_info_cursor:' || p_report_header_id || ' p_org_id:' || p_org_id);
403 OPEN p_auditor_info_cur FOR
404 SELECT a.auditor_id
405 FROM ap_aud_auditors a,
406 per_organization_list per,
407 hr_organization_information oi,
408 fnd_user u,
409 ap_expense_report_headers_all exp
410 WHERE a.security_profile_id = per.security_profile_id
411 AND a.auditor_id <> -1
412 AND per.organization_id = oi.organization_id
413 AND oi.org_information_context = 'CLASS'
414 AND oi.org_information1 = 'OPERATING_UNIT'
415 AND oi.organization_id = p_org_id
416 -- making sure auditor does not audit expense report owned by her
417 AND exp.report_header_id = p_report_header_id
418 AND a.auditor_id = u.user_id
419 AND nvl(u.employee_id , -1) <> nvl(exp.employee_id , -2)
420 -- making sure auditor does not audit expense report filed by her
421 AND nvl(u.user_id , -1) <> nvl(exp.created_by , -2);
422
423 END open_auditors_info_cursor;
424
425 /*========================================================================
426 | PUBLIC PROCEDURE open_orphaned_reports_cursor
427 |
428 | DESCRIPTION
429 | This procedure opens cursor containing (expense report ids, auditor ids) in
430 | AP_AUD_QUEUES that have been orphaned. Meaning they belong to an auditor
431 | who is not going to be able to view them because of his security_profile
432 | setting.
433 |
434 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
435 |
436 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
437 |
438 | PARAMETERS
439 | p_org_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
440 | p_cust_acct_cur IN GenRefCursor
441 |
442 | MODIFICATION HISTORY
443 | Date Author Description of Changes
444 | 05-Sep-2002 Mohammad Shoaib Jamall Created
445 |
446 *=======================================================================*/
447 PROCEDURE open_orphaned_reports_cursor(p_expense_report_cur OUT NOCOPY GenRefCursor)
448 IS
449 BEGIN
450
451 OPEN p_expense_report_cur FOR
452 SELECT expense_report_id, auditor_id
453 FROM ap_aud_queues WHERE
454 expense_report_id NOT IN
455 ( SELECT q.expense_report_id
456 FROM ap_aud_queues q,
457 ap_expense_report_headers_all e,
458 ap_aud_auditors a,
459 per_organization_list per,
460 hr_organization_information oi
461 WHERE q.auditor_id = a.auditor_id
462 AND q.expense_report_id = e.report_header_id
463 AND a.security_profile_id = per.security_profile_id
464 AND per.organization_id = oi.organization_id
465 AND oi.org_information_context = 'CLASS'
466 AND oi.org_information1 = 'OPERATING_UNIT'
467 AND e.org_id = oi.organization_id);
468
469 END open_orphaned_reports_cursor;
470
471 /*========================================================================
472 | PUBLIC PROCEDURE find_enqueue_auditor
473 |
474 | DESCRIPTION
475 | This function finds an auditor to enqueue an expense report
476 |
477 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
478 |
479 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
480 |
481 | PARAMETERS
482 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
483 |
484 | MODIFICATION HISTORY
485 | Date Author Description of Changes
486 | 05-Sep-2002 Mohammad Shoaib Jamall Created
487 |
488 *=======================================================================*/
489 FUNCTION find_enqueue_auditor(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
490 RETURN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
491 IS
492 -- preseeded auditor with no association with FND_USER
493 l_assignee NUMBER := -1;
494 -- an extremely large number
495 l_assignee_adjusted_workload NUMBER := 10E124;
496 l_auditor_info_cur GenRefCursor;
500 l_current_workload NUMBER;
497 l_open_load_cur GenRefCursor;
498
499 l_current_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
501 l_current_adjusted_workload NUMBER;
502 l_current_workload_percent AP_AUD_WORKLOADS.WORKLOAD_PERCENT%TYPE;
503 -- temporary scratchpad variable
504 l_num NUMBER;
505 l_parent_auditor_id NUMBER;
506 BEGIN
507 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start find_enqueue_auditor');
508 l_parent_auditor_id := NULL;
509
510 -- if report is shortpaid(or has been last audited) and there is a last auditor -> if last
511 -- auditor of parent isn't 0 workload and auditor still has access to the org for this auditor,
512 -- then assign to that auditor; else assign to fallback auditor
513 IF report_last_audited(p_report_id => p_report_header_id) THEN
514 SELECT h.last_audited_by INTO l_parent_auditor_id
515 FROM AP_EXPENSE_REPORT_HEADERS_ALL h
516 WHERE h.report_header_id = p_report_header_id;
517 IF l_parent_auditor_id IS NOT NULL THEN
518 open_load_cur(p_auditor_id => l_parent_auditor_id,
519 p_open_load_cur => l_open_load_cur);
520 FETCH l_open_load_cur INTO l_current_workload, l_current_workload_percent;
521 IF (auditor_has_access(p_auditor_id => l_parent_auditor_id,
522 p_report_header_id => p_report_header_id) AND
523 l_current_workload_percent > 0) THEN
524 l_assignee := l_parent_auditor_id;
525 END IF;
526 IF (l_assignee <> -1) THEN
527 RETURN l_assignee;
528 END IF;
529 END IF;
530 END IF;
531
532
533 open_auditors_cur_w( p_report_header_id => p_report_header_id,
534 p_auditor_info_cur => l_auditor_info_cur);
535 LOOP
536 FETCH l_auditor_info_cur INTO l_current_auditor_id;
537 EXIT WHEN l_auditor_info_cur%NOTFOUND;
538
539 IF (l_current_auditor_id <> -1) THEN
540
541 open_load_cur(p_auditor_id => l_current_auditor_id,
542 p_open_load_cur => l_open_load_cur);
543
544 FETCH l_open_load_cur INTO l_current_workload, l_current_workload_percent;
545 IF l_open_load_cur%FOUND THEN
546 EXIT WHEN l_assignee_adjusted_workload = 0;
547 IF (l_current_workload_percent IS NOT NULL) AND (l_current_workload_percent <> 0) THEN
548
549 IF (l_current_workload IS NULL) THEN
550 l_current_workload := 0;
551 ELSE
552 -- if current auditor is already assigned this report then subtract report weight from l_current_workload
553 SELECT count(1) INTO l_num FROM ap_aud_queues WHERE expense_report_id = p_report_header_id AND auditor_id = l_current_auditor_id;
554 IF (l_num > 0) THEN
555 SELECT report_weight INTO l_num FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;
556 l_current_workload := l_current_workload - l_num;
557 END IF;
558 END IF;
559 l_current_adjusted_workload := l_current_workload / l_current_workload_percent;
560 IF (l_current_adjusted_workload < l_assignee_adjusted_workload) THEN
561 l_assignee := l_current_auditor_id;
562 l_assignee_adjusted_workload := l_current_adjusted_workload;
563 END IF;
564
565 END IF;
566 END IF;
567
568 END IF;
569
570 END LOOP;
571
572 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end find_enqueue_auditor');
573
574 RETURN l_assignee;
575
576 END find_enqueue_auditor;
577
578 /*========================================================================
579 | PUBLIC PROCEDURE report_shortpaid
580 |
581 | DESCRIPTION
582 | is report shorpaid
583 |
584 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
585 |
586 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
587 |
588 | PARAMETERS
589 | p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
590 |
591 | MODIFICATION HISTORY
592 | Date Author Description of Changes
593 | 05-Sep-2002 Mohammad Shoaib Jamall Created
594 |
595 *=======================================================================*/
596 FUNCTION report_shortpaid(p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
597 RETURN BOOLEAN IS
598 l_shortpay_id AP_EXPENSE_REPORT_HEADERS_ALL.SHORTPAY_PARENT_ID%TYPE := NULL;
599 BEGIN
600 SELECT shortpay_parent_id
601 INTO l_shortpay_id
602 FROM ap_expense_report_headers_all
603 WHERE report_header_id = p_report_id;
604 IF (l_shortpay_id IS NULL) THEN
605 return FALSE;
606 ELSE
607 return TRUE;
608 END IF;
609 END report_shortpaid;
610
611 /*========================================================================
612 | PUBLIC PROCEDURE report_last_audited
613 |
614 | DESCRIPTION
615 | has report been last audited by someone
616 |
617 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
618 |
619 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
620 |
621 | PARAMETERS
622 | p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
623 |
624 | MODIFICATION HISTORY
625 | Date Author Description of Changes
629 FUNCTION report_last_audited(p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
626 | 05-Sep-2002 Mohammad Shoaib Jamall Created
627 |
628 *=======================================================================*/
630 RETURN BOOLEAN IS
631 l_last_audited_by AP_EXPENSE_REPORT_HEADERS_ALL.LAST_AUDITED_BY%TYPE := NULL;
632 BEGIN
633 SELECT last_audited_by
634 INTO l_last_audited_by
635 FROM ap_expense_report_headers_all
636 WHERE report_header_id = p_report_id;
637 IF (l_last_audited_by IS NULL) THEN
638 return FALSE;
639 ELSE
640 return TRUE;
641 END IF;
642 END report_last_audited;
643
644 /*========================================================================
645 | PUBLIC PROCEDURE auditor_has_access
646 |
647 | DESCRIPTION
648 | This function finds if an auditor can access a report
649 |
650 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
651 |
652 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
653 |
654 | PARAMETERS
655 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
656 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
657 |
658 | MODIFICATION HISTORY
659 | Date Author Description of Changes
660 | 05-Sep-2002 Mohammad Shoaib Jamall Created
661 |
662 *=======================================================================*/
663 FUNCTION auditor_has_access(
664 p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
665 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
666 RETURN BOOLEAN
667 IS
668 l_ret_val BOOLEAN := FALSE;
669 l_auditor_info_cur GenRefCursor;
670 l_current_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
671 BEGIN
672 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start auditor_has_access');
673 open_auditors_cur_w( p_report_header_id => p_report_header_id,
674 p_auditor_info_cur => l_auditor_info_cur);
675
676 LOOP
677 FETCH l_auditor_info_cur INTO l_current_auditor_id;
678 EXIT WHEN l_auditor_info_cur%NOTFOUND;
679 IF l_current_auditor_id = p_auditor_id THEN
680 l_ret_val := TRUE;
681 EXIT;
682 END IF;
683 END LOOP;
684
685 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end auditor_has_access');
686 RETURN l_ret_val;
687
688 END auditor_has_access;
689
690 /*========================================================================
691 | PUBLIC PROCEDURE transfer_report
692 |
693 | DESCRIPTION
694 | This procedure transfers between auditors.
695 |
696 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
697 |
698 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
699 |
700 | PARAMETERS
701 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
702 | p_next_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
703 | p_expense_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
704 |
705 | MODIFICATION HISTORY
706 | Date Author Description of Changes
707 | 05-Sep-2002 Mohammad Shoaib Jamall Created
708 |
709 *=======================================================================*/
710 PROCEDURE transfer_report(p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
711 p_next_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
712 p_expense_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
713 IS
714 PRAGMA AUTONOMOUS_TRANSACTION;
715
716 BEGIN
717 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start transfer_report');
718
719 IF (p_auditor_id <> p_next_auditor_id) THEN
720
721 UPDATE ap_aud_queues SET auditor_id = p_next_auditor_id,
722 last_update_login = null,
723 last_update_date = sysdate,
724 last_updated_by = FND_GLOBAL.USER_ID
725 WHERE auditor_id = p_auditor_id AND expense_report_id = p_expense_report_id;
726 COMMIT;
727
728 END IF;
729
730 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start transfer_report');
731
732 END transfer_report;
733
734 /*========================================================================
735 | PUBLIC FUNCTION report_weight
736 |
737 | DESCRIPTION
738 | Wrapper around FND_FUNCTION.IS_FUNCTION_ON_MENU() which returns 'Y' or
739 | 'N' instead of true/false
740 |
741 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
742 |
743 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
744 |
745 | PARAMETERS
746 | p_menu_id - menu to check
747 | p_function_id - function to look for
748 | p_check_grant_flag - if TRUE, then we won't return TRUE unless
749 | GRANT_FLAG = 'Y'. Generally pass FALSE
750 | for Data Security and TRUE for Func Sec.
751 |
752 | MODIFICATION HISTORY
753 | Date Author Description of Changes
754 | 04-Sep-2002 Mohammad Shoaib Jamall Created
755 |
756 *=======================================================================*/
757 FUNCTION is_function_on_menu(p_menu_id IN NUMBER,
758 p_function_id IN NUMBER,
759 p_check_grant_flag IN VARCHAR2) RETURN VARCHAR2
760 IS
761 l_ret_val BOOLEAN;
762 l_check_grant_flag BOOLEAN;
763 BEGIN
764 IF p_check_grant_flag IS NULL THEN
765 l_ret_val := fnd_function.is_function_on_menu(p_menu_id, p_function_id);
766 ELSE
767 IF p_check_grant_flag = 'Y' THEN
768 l_check_grant_flag := true;
769 ELSE
770 l_check_grant_flag := false;
771 END IF;
772 l_ret_val := fnd_function.is_function_on_menu(p_menu_id, p_function_id, l_check_grant_flag);
773 END IF;
774 IF l_ret_val THEN
775 RETURN 'Y';
776 ELSE
777 RETURN 'N';
778 END IF;
779 END is_function_on_menu;
780
781 /*========================================================================
782 | PUBLIC PROCEDURE remove_from_queue
783 |
784 | DESCRIPTION
785 | removes expense report from audit queues if it is in the queue.
786 | if it isn't in the queue then simply return.
787 |
788 |
789 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
790 |
791 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
792 |
793 | PARAMETERS
794 | p_report_header_id - report to remove
795 |
796 | MODIFICATION HISTORY
797 | Date Author Description of Changes
798 | 16-Oct-2002 Mohammad Shoaib Jamall Created
799 |
800 *=======================================================================*/
801 PROCEDURE remove_from_queue(
802 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
803 IS
804 BEGIN
805 DELETE FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;
806 END remove_from_queue;
807
808 /*========================================================================
809 | PUBLIC PROCEDURE auditor_for_report
810 |
811 | DESCRIPTION
812 | returns auditor_id to which report is assigned as p_auditor_id.
813 | if report is assigned to the fallback auditor returns -1.
814 | if it isn't in the queue then returns null.
815 |
816 |
817 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
818 |
819 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
820 |
821 | PARAMETERS
822 | p_report_header_id - report to remove
823 | p_auditor_id - auditor id returned
824 |
825 | MODIFICATION HISTORY
826 | Date Author Description of Changes
827 | 16-Oct-2002 Mohammad Shoaib Jamall Created
828 |
829 *=======================================================================*/
830 PROCEDURE auditor_for_report(
831 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
832 p_auditor_id OUT NOCOPY AP_AUD_AUDITORS.AUDITOR_ID%TYPE)
833 IS
834 BEGIN
835 p_auditor_id := null;
836 SELECT auditor_id INTO p_auditor_id FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;
837 EXCEPTION
838 WHEN OTHERS THEN
839 p_auditor_id := null;
840 END auditor_for_report;
841
842 /*========================================================================
843 | PUBLIC PROCEDURE reassign_orphaned_reports
844 |
845 | DESCRIPTION
846 | the security_profile_id assigned to an auditor_id can be changed
847 | OR orgs may be removed from the security_profile_id. In these
848 | cases reports may be assigned to an auditor and whereas she doesn't have
849 | access to the org_id to view the report. This procedure runs in a
850 | concurrent program and reassigns orphaned reports.
851 |
852 |
853 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
854 |
855 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
856 |
857 | PARAMETERS
858 | errbug - standard Concurrent Program error output param
859 | retcode - standard Concurrent Program return code output param
860 |
861 | MODIFICATION HISTORY
862 | Date Author Description of Changes
863 | 10-Oct-2002 Mohammad Shoaib Jamall Created
864 |
865 *=======================================================================*/
866 PROCEDURE reassign_orphaned_reports(errbuf OUT NOCOPY VARCHAR2,
867 retcode OUT NOCOPY VARCHAR2)
868 IS
869 l_expense_report_cur GenRefCursor;
870 l_expense_report_id NUMBER;
871 l_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
872 l_next_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
873
874 l_sqlerrm VARCHAR2(2000);
875 l_subject VARCHAR2(200);
876 BEGIN
877 fnd_file.put_line(fnd_file.log, 'starting AP_WEB_AUDIT_QUEUE_UTILS.reassign_orphaned_reports()');
878
879 validate_auditors();
880
881 open_orphaned_reports_cursor(p_expense_report_cur => l_expense_report_cur);
882 LOOP
883 FETCH l_expense_report_cur INTO l_expense_report_id, l_auditor_id;
884 EXIT WHEN l_expense_report_cur%NOTFOUND;
885
886 l_next_auditor_id := find_enqueue_auditor(l_expense_report_id);
887 transfer_report(l_auditor_id, l_next_auditor_id, l_expense_report_id);
888
889 END LOOP;
890 retcode := 0; -- SUCCESS
891 fnd_file.put_line(fnd_file.log, 'ending AP_WEB_AUDIT_QUEUE_UTILS.reassign_orphaned_reports()');
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 l_sqlerrm := sqlerrm(sqlcode) || fnd_global.newline ||
896 'Location: AP_WEB_AUDIT_QUEUE_UTILS.reassign_orphaned_reports()'||fnd_global.newline||
897 'Time: '||to_char(sysdate, 'DD-MON-RRRR HH:MI:SS');
898 retcode := 2; -- ERROR
899 fnd_message.set_name('SQLAP','OIE_AUD_REASSIGN_ORPH_ERROR');
900 fnd_message.set_token('REQUEST_ID', fnd_global.conc_request_id);
901 l_subject := fnd_message.get;
902 fnd_file.put_line(fnd_file.log, 'AP_WEB_AUDIT_QUEUE_UTILS.reassign_orphaned_reports()EXCEPTION:'||l_sqlerrm);
903
904 END reassign_orphaned_reports;
905
906 PROCEDURE validate_auditors IS
907 l_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
908 l_security_profile_id AP_AUD_AUDITORS.SECURITY_PROFILE_ID%TYPE;
909 -- scratch variable
910 l_num NUMBER;
911 CURSOR c1 IS SELECT auditor_id, security_profile_id
912 FROM ap_aud_auditors
913 WHERE auditor_id <> -1 AND security_profile_id IS NOT NULL;
914 BEGIN
915
916 FOR rec IN c1 LOOP
917 l_auditor_id := rec.auditor_id;
918 l_security_profile_id := rec.security_profile_id;
919
920 SELECT count(1) INTO l_num FROM
921 (SELECT
922 u.user_id,
923 FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', u.user_id, r.responsibility_id, 200/*SQLAP*/) security_profile_id
924 FROM
925 FND_USER u,
926 FND_USER_RESP_GROUPS g,
927 FND_RESPONSIBILITY r,
928 FND_FORM_FUNCTIONS f
929 WHERE
930 u.user_id = l_auditor_id
931 AND u.user_id = g.user_id
932 AND g.responsibility_id = r.responsibility_id
933 AND AP_WEB_AUDIT_QUEUE_UTILS.IS_FUNCTION_ON_MENU(r.menu_id, f.function_id, 'Y') = 'Y'
934 AND f.function_name = 'OIE_AUD_AUDIT') sp
935 WHERE sp.security_profile_id = l_security_profile_id;
936
937 IF (l_num = 0) THEN
938 UPDATE ap_aud_auditors SET security_profile_id = null WHERE auditor_id = l_auditor_id;
939 END IF;
940
941 END LOOP;
942
943 END validate_auditors;
944
945
946 END AP_WEB_AUDIT_QUEUE_UTILS;
[Home] [Help]