1 PACKAGE BODY AP_WEB_AUDIT_QUEUE_UTILS AS
2 /* $Header: apwaudqb.pls 120.10 2010/08/01 12:48:36 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 IF l_next_auditor_id <> p_auditor_id THEN
293 transfer_report(p_auditor_id, l_next_auditor_id, l_expense_report_id);
294 END IF;
295 ELSE
296 transfer_report(p_auditor_id, l_next_auditor_id, l_expense_report_id);
297 END IF;
298
299 END LOOP;
300 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end rebalance_queue');
301 END rebalance_queue;
302
303
304 /*========================================================================
305 | PUBLIC PROCEDURE open_load_cur
306 |
307 | DESCRIPTION
308 | This procedure opens cursor containing the open cursor for the
309 | current entry in ap_aud_workloads for auditor_id. It should
310 | return at most 1 row.
311 |
312 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
313 |
314 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
315 |
316 | PARAMETERS
317 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
318 | p_open_load_cur IN GenRefCursor
319 |
320 | MODIFICATION HISTORY
321 | Date Author Description of Changes
322 | 05-Sep-2002 Mohammad Shoaib Jamall Created
323 |
324 *=======================================================================*/
325 PROCEDURE open_load_cur(p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
326 p_open_load_cur OUT NOCOPY GenRefCursor)
327 IS
328
329 BEGIN
330 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start open_load_cur:' || p_auditor_id);
331 OPEN p_open_load_cur FOR
332 SELECT s.current_total_workload,
333 w.workload_percent
334 FROM
335 ap_aud_auditors a,
336 ap_aud_queue_summaries_v s,
337 ap_aud_workloads w
338 WHERE a.auditor_id = p_auditor_id
339 AND a.auditor_id = s.auditor_id(+)
340 AND a.auditor_id = w.auditor_id
341 AND w.start_date <= sysdate
342 AND sysdate < nvl(w.end_date,sysdate+1);
343
344
345 END open_load_cur;
346
347 /*========================================================================
348 | PUBLIC PROCEDURE open_auditors_cur_w
349 |
350 | DESCRIPTION
351 | This procedure opens cursor containing auditor info for auditors
352 | who can audit for the report header id
353 |
354 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
355 |
356 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
357 |
358 | PARAMETERS
359 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
360 | p_cust_acct_cur IN GenRefCursor
361 |
362 | MODIFICATION HISTORY
363 | Date Author Description of Changes
364 | 05-Sep-2002 Mohammad Shoaib Jamall Created
365 |
366 *=======================================================================*/
367 PROCEDURE open_auditors_cur_w(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
368 p_auditor_info_cur OUT NOCOPY GenRefCursor)
369 IS
370 l_org_id AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE;
371 BEGIN
372 SELECT ORG_ID INTO l_org_id FROM ap_expense_report_headers_all
373 WHERE report_header_id = p_report_header_id;
374 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);
375 END open_auditors_cur_w;
376
377 /*========================================================================
378 | PUBLIC PROCEDURE open_auditors_info_cursor
379 |
380 | DESCRIPTION
381 | This procedure opens cursor containing auditor info for auditors
382 | who can audit for the org_id. Customers can customize this for
383 | their own business logic.
384 |
385 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
386 |
387 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
388 |
389 | PARAMETERS
390 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
391 | p_org_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
392 | p_cust_acct_cur IN GenRefCursor
393 |
394 | MODIFICATION HISTORY
395 | Date Author Description of Changes
396 | 05-Sep-2002 Mohammad Shoaib Jamall Created
397 |
398 *=======================================================================*/
399 PROCEDURE open_auditors_info_cursor(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
400 p_org_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE,
401 p_auditor_info_cur OUT NOCOPY GenRefCursor)
402 IS
403
404 BEGIN
405 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start open_auditors_info_cursor:' || p_report_header_id || ' p_org_id:' || p_org_id);
406 OPEN p_auditor_info_cur FOR
407 SELECT a.auditor_id
408 FROM ap_aud_auditors a,
409 per_organization_list per,
410 hr_organization_information oi,
411 fnd_user u,
412 ap_expense_report_headers_all exp
413 WHERE a.security_profile_id = per.security_profile_id
414 AND a.auditor_id <> -1
415 AND per.organization_id = oi.organization_id
416 AND oi.org_information_context = 'CLASS'
417 AND oi.org_information1 = 'OPERATING_UNIT'
418 AND oi.organization_id = p_org_id
419 -- making sure auditor does not audit expense report owned by her
420 AND exp.report_header_id = p_report_header_id
421 AND a.auditor_id = u.user_id
422 AND nvl(u.employee_id , -1) <> nvl(exp.employee_id , -2)
423 -- making sure auditor does not audit expense report filed by her
424 AND nvl(u.user_id , -1) <> nvl(exp.created_by , -2)
425 UNION
426 SELECT a.auditor_id
427 FROM ap_aud_auditors a,
428 per_security_profiles sec,
429 fnd_user u,
430 ap_expense_report_headers_all exp
431 WHERE a.security_profile_id = sec.security_profile_id
432 AND sec.view_all_organizations_flag = 'Y'
433 AND a.auditor_id <> -1
434 -- making sure auditor does not audit expense report owned by her
435 AND exp.report_header_id = p_report_header_id
436 AND a.auditor_id = u.user_id
437 AND nvl(u.employee_id , -1) <> nvl(exp.employee_id , -2)
438 -- making sure auditor does not audit expense report filed by her
439 AND nvl(u.user_id , -1) <> nvl(exp.created_by , -2);
440
441 END open_auditors_info_cursor;
442
443 /*========================================================================
444 | PUBLIC PROCEDURE open_orphaned_reports_cursor
445 |
446 | DESCRIPTION
447 | This procedure opens cursor containing (expense report ids, auditor ids) in
448 | AP_AUD_QUEUES that have been orphaned. Meaning they belong to an auditor
449 | who is not going to be able to view them because of his security_profile
450 | setting.
451 |
452 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
453 |
454 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
455 |
456 | PARAMETERS
457 | p_org_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
458 | p_cust_acct_cur IN GenRefCursor
459 |
460 | MODIFICATION HISTORY
461 | Date Author Description of Changes
462 | 05-Sep-2002 Mohammad Shoaib Jamall Created
463 |
464 *=======================================================================*/
465 PROCEDURE open_orphaned_reports_cursor(p_expense_report_cur OUT NOCOPY GenRefCursor)
466 IS
467 BEGIN
468
469 OPEN p_expense_report_cur FOR
470 SELECT expense_report_id, auditor_id
471 FROM ap_aud_queues WHERE
472 expense_report_id NOT IN
473 ( SELECT q.expense_report_id
474 FROM ap_aud_queues q,
475 ap_expense_report_headers_all e,
476 ap_aud_auditors a,
477 per_organization_list per,
478 hr_organization_information oi
479 WHERE q.auditor_id = a.auditor_id
480 AND q.expense_report_id = e.report_header_id
481 AND a.security_profile_id = per.security_profile_id
482 AND per.organization_id = oi.organization_id
483 AND oi.org_information_context = 'CLASS'
484 AND oi.org_information1 = 'OPERATING_UNIT'
485 AND e.org_id = oi.organization_id);
486
487 END open_orphaned_reports_cursor;
488
489 /*========================================================================
490 | PUBLIC PROCEDURE find_enqueue_auditor
491 |
492 | DESCRIPTION
493 | This function finds an auditor to enqueue an expense report
494 |
495 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
496 |
497 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
498 |
499 | PARAMETERS
500 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
501 |
502 | MODIFICATION HISTORY
503 | Date Author Description of Changes
504 | 05-Sep-2002 Mohammad Shoaib Jamall Created
505 |
506 *=======================================================================*/
507 FUNCTION find_enqueue_auditor(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
508 RETURN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
509 IS
510 -- preseeded auditor with no association with FND_USER
511 l_assignee NUMBER := -1;
512 -- an extremely large number
513 l_assignee_adjusted_workload NUMBER := 10E124;
514 l_auditor_info_cur GenRefCursor;
515 l_open_load_cur GenRefCursor;
516
517 l_current_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
518 l_current_workload NUMBER;
519 l_current_adjusted_workload NUMBER;
520 l_current_workload_percent AP_AUD_WORKLOADS.WORKLOAD_PERCENT%TYPE;
521 -- temporary scratchpad variable
522 l_num NUMBER;
523 l_parent_auditor_id NUMBER;
524 BEGIN
525 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start find_enqueue_auditor');
526 l_parent_auditor_id := NULL;
527
528 -- if report is shortpaid(or has been last audited) and there is a last auditor -> if last
529 -- auditor of parent isn't 0 workload and auditor still has access to the org for this auditor,
530 -- then assign to that auditor; else assign to fallback auditor
531 IF report_last_audited(p_report_id => p_report_header_id) THEN
532 SELECT h.last_audited_by INTO l_parent_auditor_id
533 FROM AP_EXPENSE_REPORT_HEADERS_ALL h
534 WHERE h.report_header_id = p_report_header_id;
535 IF l_parent_auditor_id IS NOT NULL THEN
536 open_load_cur(p_auditor_id => l_parent_auditor_id,
537 p_open_load_cur => l_open_load_cur);
538 FETCH l_open_load_cur INTO l_current_workload, l_current_workload_percent;
539 IF (auditor_has_access(p_auditor_id => l_parent_auditor_id,
540 p_report_header_id => p_report_header_id) AND
541 l_current_workload_percent > 0) THEN
542 l_assignee := l_parent_auditor_id;
543 END IF;
544 IF (l_assignee <> -1) THEN
545 RETURN l_assignee;
546 END IF;
547 END IF;
548 END IF;
549
550
551 open_auditors_cur_w( p_report_header_id => p_report_header_id,
552 p_auditor_info_cur => l_auditor_info_cur);
553 LOOP
554 FETCH l_auditor_info_cur INTO l_current_auditor_id;
555 EXIT WHEN l_auditor_info_cur%NOTFOUND;
556 IF (l_current_auditor_id <> -1) THEN
557
558 open_load_cur(p_auditor_id => l_current_auditor_id,
559 p_open_load_cur => l_open_load_cur);
560
561 FETCH l_open_load_cur INTO l_current_workload, l_current_workload_percent;
562 IF l_open_load_cur%FOUND THEN
563 EXIT WHEN l_assignee_adjusted_workload = 0;
564 IF (l_current_workload_percent IS NOT NULL) AND (l_current_workload_percent <> 0) THEN
565
566 IF (l_current_workload IS NULL) THEN
567 l_current_workload := 0;
568 ELSE
569 -- if current auditor is already assigned this report then subtract report weight from l_current_workload
570 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;
571 IF (l_num > 0) THEN
572 SELECT report_weight INTO l_num FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;
573 l_current_workload := l_current_workload - l_num;
574 END IF;
575 END IF;
576 l_current_adjusted_workload := l_current_workload / l_current_workload_percent;
577 IF (l_current_adjusted_workload < l_assignee_adjusted_workload) THEN
578 l_assignee := l_current_auditor_id;
579 l_assignee_adjusted_workload := l_current_adjusted_workload;
580 END IF;
581
582 END IF;
583 END IF;
584
585 END IF;
586
587 END LOOP;
588
589 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end find_enqueue_auditor');
590
591 RETURN l_assignee;
592
593 END find_enqueue_auditor;
594
595 /*========================================================================
596 | PUBLIC PROCEDURE report_shortpaid
597 |
598 | DESCRIPTION
599 | is report shorpaid
600 |
601 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
602 |
603 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
604 |
605 | PARAMETERS
606 | p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
607 |
608 | MODIFICATION HISTORY
609 | Date Author Description of Changes
610 | 05-Sep-2002 Mohammad Shoaib Jamall Created
611 |
612 *=======================================================================*/
613 FUNCTION report_shortpaid(p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
614 RETURN BOOLEAN IS
615 l_shortpay_id AP_EXPENSE_REPORT_HEADERS_ALL.SHORTPAY_PARENT_ID%TYPE := NULL;
616 BEGIN
617 SELECT shortpay_parent_id
618 INTO l_shortpay_id
619 FROM ap_expense_report_headers_all
620 WHERE report_header_id = p_report_id;
621 IF (l_shortpay_id IS NULL) THEN
622 return FALSE;
623 ELSE
624 return TRUE;
625 END IF;
626 END report_shortpaid;
627
628 /*========================================================================
629 | PUBLIC PROCEDURE report_last_audited
630 |
631 | DESCRIPTION
632 | has report been last audited by someone
633 |
634 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
635 |
636 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
637 |
638 | PARAMETERS
639 | p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
640 |
641 | MODIFICATION HISTORY
642 | Date Author Description of Changes
643 | 05-Sep-2002 Mohammad Shoaib Jamall Created
644 |
645 *=======================================================================*/
646 FUNCTION report_last_audited(p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
647 RETURN BOOLEAN IS
648 l_last_audited_by AP_EXPENSE_REPORT_HEADERS_ALL.LAST_AUDITED_BY%TYPE := NULL;
649 BEGIN
650 SELECT last_audited_by
651 INTO l_last_audited_by
652 FROM ap_expense_report_headers_all
653 WHERE report_header_id = p_report_id;
654 IF (l_last_audited_by IS NULL) THEN
655 return FALSE;
656 ELSE
657 return TRUE;
658 END IF;
659 END report_last_audited;
660
661 /*========================================================================
662 | PUBLIC PROCEDURE auditor_has_access
663 |
664 | DESCRIPTION
665 | This function finds if an auditor can access a report
666 |
667 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
668 |
669 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
670 |
671 | PARAMETERS
672 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
673 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
674 |
675 | MODIFICATION HISTORY
676 | Date Author Description of Changes
677 | 05-Sep-2002 Mohammad Shoaib Jamall Created
678 |
679 *=======================================================================*/
680 FUNCTION auditor_has_access(
681 p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
682 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
683 RETURN BOOLEAN
684 IS
685 l_ret_val BOOLEAN := FALSE;
686 l_auditor_info_cur GenRefCursor;
687 l_current_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
688 BEGIN
689 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start auditor_has_access');
690 open_auditors_cur_w( p_report_header_id => p_report_header_id,
691 p_auditor_info_cur => l_auditor_info_cur);
692
693 LOOP
694 FETCH l_auditor_info_cur INTO l_current_auditor_id;
695 EXIT WHEN l_auditor_info_cur%NOTFOUND;
696 IF l_current_auditor_id = p_auditor_id THEN
697 l_ret_val := TRUE;
698 EXIT;
699 END IF;
700 END LOOP;
701
702 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'end auditor_has_access');
703 RETURN l_ret_val;
704
705 END auditor_has_access;
706
707 /*========================================================================
708 | PUBLIC PROCEDURE transfer_report
709 |
710 | DESCRIPTION
711 | This procedure transfers between auditors.
712 |
713 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
714 |
715 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
716 |
717 | PARAMETERS
718 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
719 | p_next_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
720 | p_expense_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
721 |
722 | MODIFICATION HISTORY
723 | Date Author Description of Changes
724 | 05-Sep-2002 Mohammad Shoaib Jamall Created
725 |
726 *=======================================================================*/
727 PROCEDURE transfer_report(p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
728 p_next_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
729 p_expense_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
730 IS
731 PRAGMA AUTONOMOUS_TRANSACTION;
732
733 BEGIN
734 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start transfer_report');
735
736 IF (p_auditor_id <> p_next_auditor_id) THEN
737
738 UPDATE ap_aud_queues SET auditor_id = p_next_auditor_id,
739 last_update_login = null,
740 last_update_date = sysdate,
741 last_updated_by = FND_GLOBAL.USER_ID
742 WHERE auditor_id = p_auditor_id AND expense_report_id = p_expense_report_id;
743 COMMIT;
744
745 END IF;
746
747 AP_WEB_UTILITIES_PKG.LogProcedure(G_PKG_NAME, 'start transfer_report');
748
749 END transfer_report;
750
751 /*========================================================================
752 | PUBLIC FUNCTION report_weight
753 |
754 | DESCRIPTION
755 | Wrapper around FND_FUNCTION.IS_FUNCTION_ON_MENU() which returns 'Y' or
756 | 'N' instead of true/false
757 |
758 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
759 |
760 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
761 |
762 | PARAMETERS
763 | p_menu_id - menu to check
764 | p_function_id - function to look for
765 | p_check_grant_flag - if TRUE, then we won't return TRUE unless
766 | GRANT_FLAG = 'Y'. Generally pass FALSE
767 | for Data Security and TRUE for Func Sec.
768 |
769 | MODIFICATION HISTORY
770 | Date Author Description of Changes
771 | 04-Sep-2002 Mohammad Shoaib Jamall Created
772 |
773 *=======================================================================*/
774 FUNCTION is_function_on_menu(p_menu_id IN NUMBER,
775 p_function_id IN NUMBER,
776 p_check_grant_flag IN VARCHAR2) RETURN VARCHAR2
777 IS
778 l_ret_val BOOLEAN;
779 l_check_grant_flag BOOLEAN;
780 BEGIN
781 IF p_check_grant_flag IS NULL THEN
782 l_ret_val := fnd_function.is_function_on_menu(p_menu_id, p_function_id);
783 ELSE
784 IF p_check_grant_flag = 'Y' THEN
785 l_check_grant_flag := true;
786 ELSE
787 l_check_grant_flag := false;
788 END IF;
789 l_ret_val := fnd_function.is_function_on_menu(p_menu_id, p_function_id, l_check_grant_flag);
790 END IF;
791 IF l_ret_val THEN
792 RETURN 'Y';
793 ELSE
794 RETURN 'N';
795 END IF;
796 END is_function_on_menu;
797
798 /*========================================================================
799 | PUBLIC PROCEDURE remove_from_queue
800 |
801 | DESCRIPTION
802 | removes expense report from audit queues if it is in the queue.
803 | if it isn't in the queue then simply return.
804 |
805 |
806 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
807 |
808 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
809 |
810 | PARAMETERS
811 | p_report_header_id - report to remove
812 |
813 | MODIFICATION HISTORY
814 | Date Author Description of Changes
815 | 16-Oct-2002 Mohammad Shoaib Jamall Created
816 |
817 *=======================================================================*/
818 PROCEDURE remove_from_queue(
819 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
820 IS
821 BEGIN
822 DELETE FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;
823 END remove_from_queue;
824
825 /*========================================================================
826 | PUBLIC PROCEDURE auditor_for_report
827 |
828 | DESCRIPTION
829 | returns auditor_id to which report is assigned as p_auditor_id.
830 | if report is assigned to the fallback auditor returns -1.
831 | if it isn't in the queue then returns null.
832 |
833 |
834 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
835 |
836 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
837 |
838 | PARAMETERS
839 | p_report_header_id - report to remove
840 | p_auditor_id - auditor id returned
841 |
842 | MODIFICATION HISTORY
843 | Date Author Description of Changes
844 | 16-Oct-2002 Mohammad Shoaib Jamall Created
845 |
846 *=======================================================================*/
847 PROCEDURE auditor_for_report(
848 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
849 p_auditor_id OUT NOCOPY AP_AUD_AUDITORS.AUDITOR_ID%TYPE)
850 IS
851 BEGIN
852 p_auditor_id := null;
853 SELECT auditor_id INTO p_auditor_id FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;
854 EXCEPTION
855 WHEN OTHERS THEN
856 p_auditor_id := null;
857 END auditor_for_report;
858
859 /*========================================================================
860 | PUBLIC PROCEDURE reassign_orphaned_reports
861 |
862 | DESCRIPTION
863 | the security_profile_id assigned to an auditor_id can be changed
864 | OR orgs may be removed from the security_profile_id. In these
865 | cases reports may be assigned to an auditor and whereas she doesn't have
866 | access to the org_id to view the report. This procedure runs in a
867 | concurrent program and reassigns orphaned reports.
868 |
869 |
870 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
871 |
872 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
873 |
874 | PARAMETERS
875 | errbug - standard Concurrent Program error output param
876 | retcode - standard Concurrent Program return code output param
877 |
878 | MODIFICATION HISTORY
879 | Date Author Description of Changes
880 | 10-Oct-2002 Mohammad Shoaib Jamall Created
881 |
882 *=======================================================================*/
883 PROCEDURE reassign_orphaned_reports(errbuf OUT NOCOPY VARCHAR2,
884 retcode OUT NOCOPY VARCHAR2)
885 IS
886 l_expense_report_cur GenRefCursor;
887 l_expense_report_id NUMBER;
888 l_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
889 l_next_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
890
891 l_sqlerrm VARCHAR2(2000);
892 l_subject VARCHAR2(200);
893 BEGIN
894 fnd_file.put_line(fnd_file.log, 'starting AP_WEB_AUDIT_QUEUE_UTILS.reassign_orphaned_reports()');
895
896 validate_auditors();
897
898 open_orphaned_reports_cursor(p_expense_report_cur => l_expense_report_cur);
899 LOOP
900 FETCH l_expense_report_cur INTO l_expense_report_id, l_auditor_id;
901 EXIT WHEN l_expense_report_cur%NOTFOUND;
902
903 l_next_auditor_id := find_enqueue_auditor(l_expense_report_id);
904 transfer_report(l_auditor_id, l_next_auditor_id, l_expense_report_id);
905
906 END LOOP;
907 retcode := 0; -- SUCCESS
908 fnd_file.put_line(fnd_file.log, 'ending AP_WEB_AUDIT_QUEUE_UTILS.reassign_orphaned_reports()');
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 l_sqlerrm := sqlerrm(sqlcode) || fnd_global.newline ||
913 'Location: AP_WEB_AUDIT_QUEUE_UTILS.reassign_orphaned_reports()'||fnd_global.newline||
914 'Time: '||to_char(sysdate, 'DD-MON-RRRR HH:MI:SS');
915 retcode := 2; -- ERROR
916 fnd_message.set_name('SQLAP','OIE_AUD_REASSIGN_ORPH_ERROR');
917 fnd_message.set_token('REQUEST_ID', fnd_global.conc_request_id);
918 l_subject := fnd_message.get;
919 fnd_file.put_line(fnd_file.log, 'AP_WEB_AUDIT_QUEUE_UTILS.reassign_orphaned_reports()EXCEPTION:'||l_sqlerrm);
920
921 END reassign_orphaned_reports;
922
923 PROCEDURE validate_auditors IS
924 l_auditor_id AP_AUD_AUDITORS.AUDITOR_ID%TYPE;
925 l_security_profile_id AP_AUD_AUDITORS.SECURITY_PROFILE_ID%TYPE;
926 -- scratch variable
927 l_num NUMBER;
928 CURSOR c1 IS SELECT auditor_id, security_profile_id
929 FROM ap_aud_auditors
930 WHERE auditor_id <> -1 AND security_profile_id IS NOT NULL;
931 BEGIN
932
933 FOR rec IN c1 LOOP
934 l_auditor_id := rec.auditor_id;
935 l_security_profile_id := rec.security_profile_id;
936
937 SELECT count(1) INTO l_num FROM
938 (SELECT
939 u.user_id,
940 FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', u.user_id, r.responsibility_id, 200/*SQLAP*/) security_profile_id
941 FROM
942 FND_USER u,
943 FND_USER_RESP_GROUPS g,
944 FND_RESPONSIBILITY r,
945 FND_FORM_FUNCTIONS f
946 WHERE
947 u.user_id = l_auditor_id
948 AND u.user_id = g.user_id
949 AND g.responsibility_id = r.responsibility_id
950 AND AP_WEB_AUDIT_QUEUE_UTILS.IS_FUNCTION_ON_MENU(r.menu_id, f.function_id, 'Y') = 'Y'
951 AND f.function_name = 'OIE_AUD_AUDIT') sp
952 WHERE sp.security_profile_id = l_security_profile_id;
953
954 IF (l_num = 0) THEN
955 UPDATE ap_aud_auditors SET security_profile_id = null WHERE auditor_id = l_auditor_id;
956 END IF;
957
958 END LOOP;
959
960 END validate_auditors;
961
962
963 END AP_WEB_AUDIT_QUEUE_UTILS;
[Home] [Help]