1 PACKAGE AP_WEB_AUDIT_QUEUE_UTILS AUTHID CURRENT_USER AS
2 /* $Header: apwaudqs.pls 120.3.12010000.2 2008/08/06 07:43:46 rveliche ship $ */
3
4 /*=======================================================================+
5 | Constants
6 +=======================================================================*/
7 G_OIE_AUD_INVALID_ASSIGNMENT CONSTANT VARCHAR2(30) := 'INVALID_ASSIGNMENT';
8 G_OIE_AUD_SUCCESS CONSTANT VARCHAR2(30) := 'SUCCESS';
9
10 /*=======================================================================+
11 | Types
12 +=======================================================================*/
13 TYPE GenRefCursor IS REF CURSOR;
14
15 /*=======================================================================+
16 | Procedures and Functions
17 +=======================================================================*/
18
19 /*========================================================================
20 | PUBLIC FUNCTION report_weight
21 |
22 | DESCRIPTION
23 | This function return high threshold for a given low value.
24 |
25 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
26 |
27 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
28 |
29 | PARAMETERS
30 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
31 |
32 | MODIFICATION HISTORY
33 | Date Author Description of Changes
34 | 04-Sep-2002 Mohammad Shoaib Jamall Created
35 |
36 *=======================================================================*/
37 FUNCTION report_weight(p_report_header_id IN
38 AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE) RETURN NUMBER;
39
40 /*========================================================================
41 | PUBLIC PROCEDURE assign_to_last_auditor
42 |
43 | DESCRIPTION
44 | This procedure assigns a returned Report back to the same Auditor
45 | who last Audited the report. Does not consider the Auditor Load
46 |
47 | CALLED FROM PROCEDURES/FUNCTIONS (workflow/local to this package body)
48 |
49 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
50 |
51 | PARAMETERS
52 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
53 |
54 | MODIFICATION HISTORY
55 | Date Author Description of Changes
56 | 22-Feb-2008 SaiKumar Talasila Created
57 |
58 *=======================================================================*/
59 PROCEDURE assign_to_last_auditor(p_report_header_id IN
60 AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE);
61
62 /*========================================================================
63 | PUBLIC PROCEDURE enqueue_for_audit
64 |
65 | DESCRIPTION
66 | This procedure enqueus a report_header_id for audit
67 |
68 | CALLED FROM PROCEDURES/FUNCTIONS (workflow/local to this package body)
69 |
70 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
71 |
72 | PARAMETERS
73 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
74 |
75 | MODIFICATION HISTORY
76 | Date Author Description of Changes
77 | 05-Sep-2002 Mohammad Shoaib Jamall Created
78 |
79 *=======================================================================*/
80 PROCEDURE enqueue_for_audit(p_report_header_id IN
81 AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE);
82
83 /*========================================================================
84 | PUBLIC PROCEDURE assign_report_to_auditor
85 |
86 | DESCRIPTION
87 | This procedure enqueus a report_header_id for audit:
88 | - Takes in report_header_id and auditor_id
89 | - If report already in the queue under this auditor do nothing
90 | - If report not in the queue, enqueue it for this auditor if this
91 | auditor is not owner of report.
92 | - If report already in the queue under another auditor transfer
93 | the report under this auditor if auditor is not owner of report.
94 | - p_retcode is returned with:
95 | 'INVALID_ASSIGNMENT' -> if the auditor is the owner of report and
96 | cannot be assigned the report.
97 | 'SUCCESS' -> if everything went ok.
98 |
99 | CALLED FROM PROCEDURES/FUNCTIONS (workflow/local to this package body)
100 |
101 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
102 |
103 | PARAMETERS
104 | p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
105 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
106 |
107 | MODIFICATION HISTORY
108 | Date Author Description of Changes
109 | 05-Sep-2002 Mohammad Shoaib Jamall Created
110 |
111 *=======================================================================*/
112 PROCEDURE assign_report_to_auditor(
113 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
114 p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE,
115 p_retcode OUT NOCOPY VARCHAR2);
116
117 /*========================================================================
118 | PUBLIC PROCEDURE rebalance_queue
119 |
120 | DESCRIPTION
121 | This procedure enqueus a report_header_id for audit
122 |
123 | CALLED FROM PROCEDURES/FUNCTIONS (workflow/local to this package body)
124 |
125 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
126 |
127 | PARAMETERS
128 | p_auditor_id IN AP_AUD_AUDITORS.AUDITOR_ID%TYPE
129 |
130 | MODIFICATION HISTORY
131 | Date Author Description of Changes
132 | 05-Sep-2002 Mohammad Shoaib Jamall Created
133 |
134 *=======================================================================*/
135 PROCEDURE rebalance_queue(p_auditor_id IN
136 AP_AUD_AUDITORS.AUDITOR_ID%TYPE);
137
138
139 /*========================================================================
140 | PUBLIC PROCEDURE open_auditors_info_cursor
141 |
142 | DESCRIPTION
143 | This procedure opens cursor containing auditor info for auditors
144 | who can audit for the org_id. Customers can customize this for
145 | their own business logic.
146 |
147 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
148 |
149 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
150 |
151 | PARAMETERS
152 | p_org_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE
153 | p_cust_acct_cur IN GenRefCursor
154 |
155 | MODIFICATION HISTORY
156 | Date Author Description of Changes
157 | 05-Sep-2002 Mohammad Shoaib Jamall Created
158 |
159 *=======================================================================*/
160 PROCEDURE open_auditors_info_cursor(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
161 p_org_id IN AP_EXPENSE_REPORT_HEADERS_ALL.ORG_ID%TYPE,
162 p_auditor_info_cur OUT NOCOPY GenRefCursor);
163
164 /*========================================================================
165 | PUBLIC FUNCTION is_function_on_menu
166 |
167 | DESCRIPTION
168 | Wrapper around FND_FUNCTION.IS_FUNCTION_ON_MENU() which returns 'Y' or
169 | 'N' instead of true/false
170 |
171 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
172 |
173 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
174 |
175 | PARAMETERS
176 | p_menu_id - menu to check
177 | p_function_id - function to look for
178 | p_check_grant_flag - if TRUE, then we won't return TRUE unless
179 | GRANT_FLAG = 'Y'. Generally pass FALSE
180 | for Data Security and TRUE for Func Sec.
181 |
182 | MODIFICATION HISTORY
183 | Date Author Description of Changes
184 | 04-Sep-2002 Mohammad Shoaib Jamall Created
185 |
186 *=======================================================================*/
187 FUNCTION is_function_on_menu(p_menu_id IN NUMBER,
188 p_function_id IN NUMBER,
189 p_check_grant_flag IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
190
191 /*========================================================================
192 | PUBLIC PROCEDURE report_shortpaid
193 |
194 | DESCRIPTION
195 | is report shorpaid
196 |
197 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
198 |
199 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
200 |
201 | PARAMETERS
202 | p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE
203 |
204 | MODIFICATION HISTORY
205 | Date Author Description of Changes
206 | 05-Sep-2002 Mohammad Shoaib Jamall Created
207 |
208 *=======================================================================*/
209
210 FUNCTION report_shortpaid(p_report_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE)
211 RETURN BOOLEAN;
212
213
214 /*========================================================================
215 | PUBLIC PROCEDURE remove_from_queue
216 |
217 | DESCRIPTION
218 | removes expense report from audit queues if it is in the queue.
219 | if it isn't in the queue then simply return.
220 |
221 |
222 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
223 |
224 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
225 |
226 | PARAMETERS
227 | p_report_header_id - report to remove
228 |
229 | MODIFICATION HISTORY
230 | Date Author Description of Changes
231 | 16-Oct-2002 Mohammad Shoaib Jamall Created
232 |
233 *=======================================================================*/
234 PROCEDURE remove_from_queue(
235 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE);
236
237 /*========================================================================
238 | PUBLIC PROCEDURE auditor_for_report
239 |
240 | DESCRIPTION
241 | returns auditor_id to which report is assigned as p_auditor_id.
242 | if report is assigned to the fallback auditor returns -1.
243 | if it isn't in the queue then returns null.
244 |
245 |
246 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
247 |
248 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
249 |
250 | PARAMETERS
251 | p_report_header_id - report to remove
252 | p_auditor_id - auditor id returned
253 |
254 | MODIFICATION HISTORY
255 | Date Author Description of Changes
256 | 16-Oct-2002 Mohammad Shoaib Jamall Created
257 |
258 *=======================================================================*/
259 PROCEDURE auditor_for_report(
260 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS_ALL.REPORT_HEADER_ID%TYPE,
261 p_auditor_id OUT NOCOPY AP_AUD_AUDITORS.AUDITOR_ID%TYPE);
262
263 /*========================================================================
264 | PUBLIC PROCEDURE reassign_orphaned_reports
265 |
266 | DESCRIPTION
267 | the security_profile_id assigned to an auditor_id can be changed
268 | OR orgs may be removed from the security_profile_id. In these
269 | cases reports may be assigned to an auditor and whereas she doesn't have
270 | access to the org_id to view the report. This procedure runs in a
271 | concurrent program and reassigns orphaned reports.
272 |
273 |
274 | CALLED FROM PROCEDURES/FUNCTIONS - Concurrent Program
275 |
279 | errbug - standard Concurrent Program error output param
276 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
277 |
278 | PARAMETERS
280 | retcode - standard Concurrent Program return code output param
281 |
282 | MODIFICATION HISTORY
283 | Date Author Description of Changes
284 | 10-Oct-2002 Mohammad Shoaib Jamall Created
285 |
286 *=======================================================================*/
287 PROCEDURE reassign_orphaned_reports(errbuf OUT NOCOPY VARCHAR2,
288 retcode OUT NOCOPY VARCHAR2);
289
290
291 END AP_WEB_AUDIT_QUEUE_UTILS;