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