[Home] [Help]
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;
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
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
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:
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;
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: /*========================================================================
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);
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: /*========================================================================
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
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,
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,
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;
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
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:
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,
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: /*========================================================================