[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
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)
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,
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
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;
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;
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;
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
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;