1 PACKAGE BODY AP_WEB_RECEIPT_MANAGEMENT_UTIL AS
2 /* $Header: apwrmutb.pls 120.2 2008/03/18 04:35:59 sodash ship $ */
3
4 FUNCTION get_line_receipt_status(p_report_header_id IN NUMBER) RETURN VARCHAR2;
5
6 /*========================================================================
7 | PUBLIC FUNCTION get_receipt_status
8 |
9 | DESCRIPTION
10 | This function returns the receipt status for a expense report. If no
11 | event is passed in as parameter, then the status deducted from the
12 | current status and values on line columns. If an event is passed in
13 | then it is also taken into consideration when deducting the receipt
14 | status.
15 |
16 | Note if this logic is called from BC4J, then the changes on the page
17 | need to be posted in order for this function to be able to see them.
18 | To do that, call this function from the OAViewObjectImpl.beforeCommit.
19 |
20 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
21 | Called from BC4J and workflow logic.
22 |
23 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
24 |
25 | RETURNS
26 | Receipt status as VARCHAR2.
27 |
28 | PARAMETERS
29 | p_report_header_id IN Expense report identifier
30 | p_event IN event taken on the report one of the following:
31 | C_EVENT_WAIVE_RECEIPTS
32 | C_EVENT_WAIVE_COMPLETE
33 | C_EVENT_RECEIVE_RECEIPTS
34 | C_EVENT_RECEIPTS_NOT_RECEIVED
35 | C_EVENT_RECEIPTS_IN_TRANSIT
36 | C_EVENT_MR_SHORTPAY
37 | C_EVENT_PV_SHORTPAY
38 | C_EVENT_SHORTPAY
39 | C_EVENT_NONE
40 | C_EVENT_REJECT
41 | C_EVENT_REQUEST_INFO
42 | C_EVENT_RELEASE_HOLD
43 | C_EVENT_COMPLETE_AUDIT
44 | The value is defaulted to C_EVENT_NONE
45 |
46 | MODIFICATION HISTORY
47 | Date Author Description of Changes
48 | 16-Aug-2004 J Rautiainen Created
49 |
50 *=======================================================================*/
51 FUNCTION get_receipt_status(p_report_header_id IN NUMBER,
52 p_event IN VARCHAR2 DEFAULT C_EVENT_NONE) RETURN VARCHAR2 IS
53
54 CURSOR header_cur IS
55 select aerh.receipts_status,
56 aerh.receipts_received_date,
57 aerh2.receipts_status parent_receipts_status
58 from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
59 AP_EXPENSE_REPORT_HEADERS_ALL aerh2
60 where aerh.report_header_id = p_report_header_id
61 and aerh2.report_header_id(+) = aerh.shortpay_parent_id;
62
63 header_rec header_cur%ROWTYPE;
64 l_rm_id NUMBER;
65 l_pv_id NUMBER;
66 l_line_status VARCHAR2(30);
67 BEGIN
68 IF ( p_report_header_id IS NULL
69 OR p_event NOT IN ( C_EVENT_WAIVE_RECEIPTS,
70 C_EVENT_WAIVE_COMPLETE,
71 C_EVENT_RECEIVE_RECEIPTS,
72 C_EVENT_RECEIPTS_NOT_RECEIVED,
73 C_EVENT_RECEIPTS_IN_TRANSIT,
74 C_EVENT_MR_SHORTPAY,
75 C_EVENT_PV_SHORTPAY,
76 C_EVENT_SHORTPAY,
77 C_EVENT_NONE,
78 C_EVENT_REJECT,
79 C_EVENT_REQUEST_INFO,
80 C_EVENT_RELEASE_HOLD,
81 C_EVENT_COMPLETE_AUDIT)
82 ) THEN
83 return null;
84 END IF;
85
86 OPEN header_cur;
87 FETCH header_cur INTO header_rec;
88 IF header_cur%NOTFOUND THEN
89 CLOSE header_cur;
90 return null;
91 END IF;
92 CLOSE header_cur;
93
94 IF p_event = C_EVENT_RECEIPTS_IN_TRANSIT THEN
95 return C_STATUS_IN_TRANSIT;
96 ELSIF p_event = C_EVENT_WAIVE_RECEIPTS OR p_event = C_EVENT_WAIVE_COMPLETE THEN
97 return C_STATUS_WAIVED;
98 ELSIF p_event = C_EVENT_RECEIVE_RECEIPTS THEN
99 return C_STATUS_RECEIVED;
100 ELSIF p_event = C_EVENT_REJECT THEN
101 IF header_rec.receipts_received_date IS NULL THEN
102 return null;
103 ELSE
104 return header_rec.receipts_status;
105 END IF;
106 ELSIF p_event in (C_EVENT_REQUEST_INFO, C_EVENT_RELEASE_HOLD, C_EVENT_COMPLETE_AUDIT) THEN
107 return header_rec.receipts_status;
108 END IF;
109
110 l_line_status := get_line_receipt_status(p_report_header_id);
111
112 IF p_event = C_EVENT_MR_SHORTPAY THEN
113 return C_STATUS_RESOLUTN;
114 ELSIF p_event = C_EVENT_PV_SHORTPAY THEN
115 IF header_rec.parent_receipts_status = C_STATUS_WAIVED THEN
116 return C_STATUS_WAIVED;
117 END IF;
118 ELSIF p_event = C_EVENT_SHORTPAY THEN
119 IF l_line_status = C_STATUS_NOT_REQUIRED THEN
120 return C_STATUS_NOT_REQUIRED;
121 END IF;
122 END IF;
123
124 IF header_rec.receipts_received_date IS NOT NULL THEN
125 return C_STATUS_RECEIVED;
126 ELSIF header_rec.receipts_status = C_STATUS_WAIVED THEN
127 return C_STATUS_WAIVED;
128 ELSE
129 return l_line_status;
130 END IF;
131
132 END get_receipt_status;
133
134 /*========================================================================
135 | PUBLIC FUNCTION get_line_receipt_status
136 |
137 | DESCRIPTION
138 | This function returns the receipt status for a expense report
139 | deducted from the values on line columns. This does NOT necessarily
140 | represent the correct value for the whole expense report, rather
141 | indicates what the status would be if only lines are evaluated.
142 |
143 | Note if this logic is called from BC4J, then the changes on the page
144 | need to be posted in order for this function to be able to see them.
145 | To do that, call this function from the OAViewObjectImpl.beforeCommit.
146 |
147 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
148 | PLSQL logic.
149 |
150 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
151 |
152 | RETURNS
153 | Receipt status as VARCHAR2.
154 |
155 | PARAMETERS
156 | p_report_header_id IN Expense report identifier
157 |
158 | MODIFICATION HISTORY
159 | Date Author Description of Changes
160 | 16-Aug-2004 J Rautiainen Created
161 |
162 *=======================================================================*/
163 FUNCTION get_line_receipt_status(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
164
165 CURSOR line_cur IS
166 select aerl.receipt_required_flag,
167 aerl.receipt_missing_flag
168 from AP_EXPENSE_REPORT_LINES_ALL aerl,
169 AP_EXPENSE_REPORT_PARAMS_ALL erp
170 where aerl.report_header_id = p_report_header_id
171 and erp.parameter_id = aerl.web_parameter_id
172 and NVL(erp.expense_type_code,'NOT_DEFINED') not in ('PERSONAL','ROUNDING');
173
174 line_rec line_cur%ROWTYPE;
175
176 required_count NUMBER := 0;
177 missing_count NUMBER := 0;
178
179 BEGIN
180
181 FOR line_rec IN line_cur LOOP
182
183 IF NVL(line_rec.receipt_required_flag,'N') = 'Y' THEN
184 IF NVL(line_rec.receipt_missing_flag,'N') = 'Y' THEN
185 missing_count := missing_count + 1;
186 ELSE
187 required_count := required_count + 1;
188 END IF;
189 END IF;
190
191 END LOOP;
192
193 IF required_count > 0 THEN
194 return C_STATUS_REQUIRED;
195 ELSIF missing_count > 0 THEN
196 return C_STATUS_MISSING;
197 ELSE
198 return C_STATUS_NOT_REQUIRED;
199 END IF;
200
201 END get_line_receipt_status;
202
203 /*========================================================================
204 | PUBLIC PROCEDURE handle_event
205 |
206 | DESCRIPTION
207 | This procedure handles a receipt management related event.
208 |
209 | Note if this logic is called from BC4J, then the changes on the page
210 | need to be posted in order for this function to be able to see them.
211 | To do that, call this function from the OAViewObjectImpl.beforeCommit.
212 |
213 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
214 | Called from BC4J and workflow logic.
215 |
216 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
217 |
218 | RETURNS
219 | Receipt status as VARCHAR2.
220 |
221 | PARAMETERS
222 | p_report_header_id IN Expense report identifier
223 | p_event IN event taken on the report one of the following:
224 | C_EVENT_WAIVE_RECEIPTS
225 | C_EVENT_WAIVE_COMPLETE
226 | C_EVENT_RECEIVE_RECEIPTS
227 | C_EVENT_RECEIPTS_NOT_RECEIVED
228 | C_EVENT_MR_SHORTPAY
229 | C_EVENT_PV_SHORTPAY
230 | C_EVENT_SHORTPAY
231 | C_EVENT_REJECT
232 | C_EVENT_REQUEST_INFO
233 | C_EVENT_RELEASE_HOLD
234 | C_EVENT_COMPLETE_AUDIT
235 |
236 | MODIFICATION HISTORY
237 | Date Author Description of Changes
238 | 16-Aug-2004 J Rautiainen Created
239 |
240 *=======================================================================*/
241 PROCEDURE handle_event(p_report_header_id IN NUMBER,
242 p_event IN VARCHAR2) IS
243
244 CURSOR header_cur IS
245 select aerh.*
246 from AP_EXPENSE_REPORT_HEADERS_ALL aerh
247 where aerh.report_header_id = p_report_header_id
248 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
249
250 CURSOR split_cc_cur IS
251 select aerh.report_header_id
252 from AP_EXPENSE_REPORT_HEADERS_ALL aerh
253 where aerh.BOTHPAY_PARENT_ID = p_report_header_id
254 and holding_report_header_id is not null;
255
256 CURSOR orig_cc_cur(l_report_header_id IN NUMBER) IS
257 select aerh.report_header_id
258 from AP_EXPENSE_REPORT_HEADERS_ALL aerh
259 where aerh.report_header_id = l_report_header_id
260 and holding_report_header_id is not null
261 and NOT EXISTS (select 1
262 from ap_expense_report_lines_all aerl
263 where aerl.report_header_id = aerh.report_header_id
264 and aerl.credit_card_trx_id IS NULL);
265
266 header_rec header_cur%ROWTYPE;
267 split_cc_rec split_cc_cur%ROWTYPE;
268 l_new_status VARCHAR2(30);
269 BEGIN
270 IF ( p_report_header_id IS NULL
271 OR p_event IS NULL
272 OR p_event NOT IN (C_EVENT_WAIVE_RECEIPTS,
273 C_EVENT_WAIVE_COMPLETE,
274 C_EVENT_RECEIVE_RECEIPTS,
275 C_EVENT_RECEIPTS_NOT_RECEIVED,
276 C_EVENT_MR_SHORTPAY,
277 C_EVENT_PV_SHORTPAY,
278 C_EVENT_SHORTPAY,
279 C_EVENT_REJECT,
280 C_EVENT_REQUEST_INFO,
281 C_EVENT_RELEASE_HOLD,
282 C_EVENT_COMPLETE_AUDIT )
283 ) THEN
284 return;
285 END IF;
286
287 OPEN header_cur;
288 FETCH header_cur INTO header_rec;
289
290 l_new_status := get_receipt_status(p_report_header_id, p_event);
291
292 IF (header_rec.report_header_id IS NOT NULL) THEN
293 UPDATE ap_expense_report_headers_all
294 SET receipts_status = l_new_status
295 WHERE CURRENT OF header_cur;
296 END IF;
297
298 IF (l_new_status IS NOT NULL AND header_rec.receipts_status IS NOT NULL)
299 AND (l_new_status = C_STATUS_NOT_REQUIRED AND header_rec.receipts_status <> C_STATUS_NOT_REQUIRED) THEN
300 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
301 END IF;
302
303 IF (p_event = C_EVENT_WAIVE_RECEIPTS OR p_event = C_EVENT_WAIVE_COMPLETE) THEN
304 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
305 ELSIF (p_event = C_EVENT_RECEIVE_RECEIPTS) THEN
306 AP_WEB_RECEIPTS_WF.RaiseReceivedEvent(p_report_header_id);
307 ELSIF (p_event = C_EVENT_RECEIPTS_NOT_RECEIVED) THEN
308 -- currently no additional processing for this event
309 null;
310 ELSIF (p_event = C_EVENT_REJECT) THEN
311 /* Aborted event is raised in Expenses WF SetRejectStatusAndResetAttr(),
312 * which is shared by manager rejection. However since the auditor can
313 * reject prior to manager approval, raising the event here */
314 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
315
316 ELSIF (p_event = C_EVENT_REQUEST_INFO) THEN
317 -- currently no additional processing for this event
318 null;
319 ELSIF (p_event = C_EVENT_RELEASE_HOLD) THEN
320 AP_WEB_HOLDS_WF.ReadyForPayment(p_report_header_id);
321
322 IF header_rec.source = 'Both Pay' THEN
323 /* For both pay reports the status is reset on the header only if the original report contains only
324 * credit card transaction lines */
325 FOR orig_cc_rec IN orig_cc_cur(header_rec.BOTHPAY_PARENT_ID) LOOP
326 AP_WEB_HOLDS_WF.ReadyForPayment(orig_cc_rec.report_header_id);
327 AP_WEB_HOLDS_WF.RaiseReleasedEvent(orig_cc_rec.report_header_id);
328 END LOOP;
329 ELSE
330 AP_WEB_HOLDS_WF.RaiseReleasedEvent(p_report_header_id);
331
332 /* For reports from which cc transactions were split into separate .1 report, release hold also releases
333 * the hold on the split report */
334 FOR split_cc_rec IN split_cc_cur LOOP
335 AP_WEB_HOLDS_WF.ReadyForPayment(split_cc_rec.report_header_id);
336 END LOOP;
337 END IF;
338 ELSIF (p_event = C_EVENT_SHORTPAY) THEN
339 IF l_new_status = C_STATUS_NOT_REQUIRED THEN
340 UPDATE AP_EXPENSE_REPORT_HEADERS_ALL
341 SET receipts_received_date = null
342 WHERE CURRENT OF header_cur;
343 END IF;
344
345 ELSIF (p_event = C_EVENT_MR_SHORTPAY) THEN
346
347 UPDATE AP_EXPENSE_REPORT_HEADERS_ALL
348 SET receipts_received_date = null, report_filing_number = null
349 WHERE CURRENT OF header_cur;
350
351 ELSIF (p_event = C_EVENT_PV_SHORTPAY) THEN
352 -- currently no additional processing for this event
353 null;
354 ELSIF (p_event = C_EVENT_COMPLETE_AUDIT) THEN
355 IF header_rec.shortpay_parent_id is not null
356 AND header_rec.receipts_status = C_STATUS_RECEIVED
357 AND 'Y' = AP_WEB_RECEIPT_MANAGEMENT_UTIL.is_shortpaid_report(p_report_header_id, AP_WEB_RECEIPTS_WF.C_POLICY_VIOLATION_PROCESS) THEN
358 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
359 END IF;
360 END IF;
361
362 CLOSE header_cur;
363 END handle_event;
364
365 /*========================================================================
366 | PUBLIC FUNCTION is_shortpaid_report
367 |
368 | DESCRIPTION
369 | This function detects whether a report is a shortpaid report.
370 |
371 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
372 | Called from BC4J and workflow logic.
373 |
374 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
375 |
376 | RETURNS
377 | Y or N depending whether the report is shortpaid of the particualr type.
378 |
379 | PARAMETERS
380 | p_report_header_id IN Expense report identifier
381 | p_shortpay_type IN type of the shortpay:
382 | AP_WEB_RECEIPTS_WF.C_NO_RECEIPTS_SHORTPAY_PROCESS
383 | AP_WEB_RECEIPTS_WF.C_POLICY_VIOLATION_PROCESS
384 |
385 | MODIFICATION HISTORY
386 | Date Author Description of Changes
387 | 31-Dec-2004 J Rautiainen Created
388 |
389 *=======================================================================*/
390 FUNCTION is_shortpaid_report(p_report_header_id IN NUMBER,
391 p_shortpay_type IN VARCHAR2) RETURN VARCHAR2 IS
392
393 CURSOR wf_cur IS
394 select 1
395 from wf_items wf
396 where wf.item_type = 'APEXP'
397 and wf.item_key = to_char(p_report_header_id) -- Bug 6841589 (sodash) to solve the invalid number exception
398 and wf.end_date is null
399 and wf.root_activity = p_shortpay_type
400 and rownum = 1;
401
402 wf_rec wf_cur%ROWTYPE;
403 BEGIN
404 IF p_report_header_id IS NULL
405 OR p_shortpay_type IS NULL
406 OR p_shortpay_type not in (AP_WEB_RECEIPTS_WF.C_NO_RECEIPTS_SHORTPAY_PROCESS,
407 AP_WEB_RECEIPTS_WF.C_POLICY_VIOLATION_PROCESS) THEN
408 return 'N';
409 END IF;
410
411 OPEN wf_cur;
412 FETCH wf_cur INTO wf_rec;
413 IF wf_cur%FOUND THEN
414 CLOSE wf_cur;
415 return 'Y';
416 ELSE
417 CLOSE wf_cur;
418 return 'N';
419 END IF;
420
421 END is_shortpaid_report;
422
423 END AP_WEB_RECEIPT_MANAGEMENT_UTIL;
[Home] [Help]