[Home] [Help]
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.
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,
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
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)
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,
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
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;
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;
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;
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
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;