[Home] [Help]
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:
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:
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,
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: ------------------------------------------------------------------------
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
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;
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');
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
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
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;
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
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
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(+)
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
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
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
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
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;
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);
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
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
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;
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:
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;
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);
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
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
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
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
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: