1 PACKAGE BODY AP_WEB_RECEIPT_MANAGEMENT_UTIL AS
2 /* $Header: apwrmutb.pls 120.16 2011/07/18 12:49:15 dsadipir ship $ */
3
4 FUNCTION get_line_receipt_status(p_report_header_id IN NUMBER) RETURN VARCHAR2;
5
6 FUNCTION get_image_receipt_status(p_report_header_id IN NUMBER,
7 p_event IN VARCHAR2 DEFAULT C_EVENT_NONE) RETURN VARCHAR2;
8
9 /*========================================================================
10 | PUBLIC FUNCTION get_receipt_status
11 |
12 | DESCRIPTION
13 | This function returns the receipt status for a expense report. If no
14 | event is passed in as parameter, then the status deducted from the
15 | current status and values on line columns. If an event is passed in
16 | then it is also taken into consideration when deducting the receipt
17 | status.
18 |
19 | Note if this logic is called from BC4J, then the changes on the page
20 | need to be posted in order for this function to be able to see them.
21 | To do that, call this function from the OAViewObjectImpl.beforeCommit.
22 |
23 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
24 | Called from BC4J and workflow logic.
25 |
26 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
27 |
28 | RETURNS
29 | Receipt status as VARCHAR2.
30 |
31 | PARAMETERS
32 | p_report_header_id IN Expense report identifier
33 | p_event IN event taken on the report one of the following:
34 | C_EVENT_WAIVE_RECEIPTS
35 | C_EVENT_WAIVE_COMPLETE
36 | C_EVENT_RECEIVE_RECEIPTS
37 | C_EVENT_RECEIPTS_NOT_RECEIVED
38 | C_EVENT_RECEIPTS_IN_TRANSIT
39 | C_EVENT_MR_SHORTPAY
40 | C_EVENT_PV_SHORTPAY
41 | C_EVENT_SHORTPAY
42 | C_EVENT_NONE
43 | C_EVENT_REJECT
44 | C_EVENT_REQUEST_INFO
45 | C_EVENT_RELEASE_HOLD
46 | C_EVENT_COMPLETE_AUDIT
47 | The value is defaulted to C_EVENT_NONE
48 |
49 | MODIFICATION HISTORY
50 | Date Author Description of Changes
51 | 16-Aug-2004 J Rautiainen Created
52 |
53 *=======================================================================*/
54 FUNCTION get_receipt_status(p_report_header_id IN NUMBER,
55 p_event IN VARCHAR2 DEFAULT C_EVENT_NONE) RETURN VARCHAR2 IS
56
57 CURSOR header_cur IS
58 select aerh.receipts_status,
59 aerh.receipts_received_date,
60 aerh2.receipts_status parent_receipts_status
61 from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
62 AP_EXPENSE_REPORT_HEADERS_ALL aerh2
63 where aerh.report_header_id = p_report_header_id
64 and aerh2.report_header_id(+) = aerh.shortpay_parent_id;
65
66 header_rec header_cur%ROWTYPE;
67 l_rm_id NUMBER;
68 l_pv_id NUMBER;
69 l_line_status VARCHAR2(30);
70 BEGIN
71 IF ( p_report_header_id IS NULL
72 OR p_event NOT IN ( C_EVENT_WAIVE_RECEIPTS,
73 C_EVENT_WAIVE_COMPLETE,
74 C_EVENT_RECEIVE_RECEIPTS,
75 C_EVENT_RECEIPTS_NOT_RECEIVED,
76 C_EVENT_RECEIPTS_IN_TRANSIT,
77 C_EVENT_MR_SHORTPAY,
78 C_EVENT_MIR_SHORTPAY,
79 C_EVENT_MBR_SHORTPAY,
80 C_EVENT_PV_SHORTPAY,
81 C_EVENT_SHORTPAY,
82 C_EVENT_NONE,
83 C_EVENT_REJECT,
84 C_EVENT_REQUEST_INFO,
85 C_EVENT_RELEASE_HOLD,
86 C_EVENT_COMPLETE_AUDIT)
87 ) THEN
88 return null;
89 END IF;
90
91 OPEN header_cur;
92 FETCH header_cur INTO header_rec;
93 IF header_cur%NOTFOUND THEN
94 CLOSE header_cur;
95 return null;
96 END IF;
97 CLOSE header_cur;
98
99 l_line_status := get_line_receipt_status(p_report_header_id);
100
101 IF p_event = C_EVENT_MR_SHORTPAY THEN
102 return C_STATUS_RESOLUTN;
103 ELSIF p_event = C_EVENT_MIR_SHORTPAY THEN
104 return C_STATUS_RESOLUTN;
105 ELSIF p_event = C_EVENT_MBR_SHORTPAY THEN
106 return C_STATUS_RESOLUTN;
107 ELSIF p_event = C_EVENT_PV_SHORTPAY THEN
108 IF header_rec.parent_receipts_status = C_STATUS_WAIVED THEN
109 return C_STATUS_WAIVED;
110 END IF;
111 ELSIF p_event = C_EVENT_SHORTPAY THEN
112 IF l_line_status = C_STATUS_NOT_REQUIRED THEN
113 IF header_rec.receipts_received_date IS NOT NULL THEN
114 return C_STATUS_RECEIVED;
115 ELSE
116 return C_STATUS_NOT_REQUIRED;
117 END IF;
118 ELSE
119 return header_rec.receipts_status;
120 END IF;
121 END IF;
122
123
124 IF header_rec.receipts_status = C_STATUS_NOT_REQUIRED THEN
125 IF header_rec.receipts_received_date IS NOT NULL THEN
126 return C_STATUS_RECEIVED;
127 ELSE
128 return C_STATUS_NOT_REQUIRED;
129 END IF;
130 END IF;
131
132 IF p_event = C_EVENT_RECEIPTS_IN_TRANSIT THEN
133 return C_STATUS_IN_TRANSIT;
134 ELSIF p_event = C_EVENT_WAIVE_RECEIPTS OR p_event = C_EVENT_WAIVE_COMPLETE THEN
135 return C_STATUS_WAIVED;
136 ELSIF p_event = C_EVENT_RECEIVE_RECEIPTS THEN
137 return C_STATUS_RECEIVED;
138 ELSIF p_event = C_EVENT_REJECT THEN
139 IF header_rec.receipts_received_date IS NULL THEN
140 return null;
141 ELSE
142 return header_rec.receipts_status;
143 END IF;
144 ELSIF p_event in (C_EVENT_REQUEST_INFO, C_EVENT_RELEASE_HOLD, C_EVENT_COMPLETE_AUDIT) THEN
145 return header_rec.receipts_status;
146 END IF;
147
148 /*l_line_status := get_line_receipt_status(p_report_header_id);
149
150 IF p_event = C_EVENT_MR_SHORTPAY THEN
151 return C_STATUS_RESOLUTN;
152 ELSIF p_event = C_EVENT_MIR_SHORTPAY THEN
153 return C_STATUS_RESOLUTN;
154 ELSIF p_event = C_EVENT_MBR_SHORTPAY THEN
155 return C_STATUS_RESOLUTN;
156 ELSIF p_event = C_EVENT_PV_SHORTPAY THEN
157 IF header_rec.parent_receipts_status = C_STATUS_WAIVED THEN
158 return C_STATUS_WAIVED;
159 END IF;
160 ELSIF p_event = C_EVENT_SHORTPAY THEN
161 IF l_line_status = C_STATUS_NOT_REQUIRED THEN
162 return C_STATUS_NOT_REQUIRED;
163 END IF;
164 END IF;*/
165
166 IF header_rec.receipts_received_date IS NOT NULL THEN
167 return C_STATUS_RECEIVED;
168 ELSIF header_rec.receipts_status = C_STATUS_WAIVED THEN
169 return C_STATUS_WAIVED;
170 ELSE
171 return l_line_status;
172 END IF;
173
174 END get_receipt_status;
175
176 /*========================================================================
177 | PUBLIC FUNCTION get_line_receipt_status
178 |
179 | DESCRIPTION
180 | This function returns the receipt status for a expense report
181 | deducted from the values on line columns. This does NOT necessarily
182 | represent the correct value for the whole expense report, rather
183 | indicates what the status would be if only lines are evaluated.
184 |
185 | Note if this logic is called from BC4J, then the changes on the page
186 | need to be posted in order for this function to be able to see them.
187 | To do that, call this function from the OAViewObjectImpl.beforeCommit.
188 |
189 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
190 | PLSQL logic.
191 |
192 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
193 |
194 | RETURNS
195 | Receipt status as VARCHAR2.
196 |
197 | PARAMETERS
198 | p_report_header_id IN Expense report identifier
199 |
200 | MODIFICATION HISTORY
201 | Date Author Description of Changes
202 | 16-Aug-2004 J Rautiainen Created
203 |
204 *=======================================================================*/
205 FUNCTION get_line_receipt_status(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
206
207 CURSOR line_cur IS
208 select aerl.receipt_required_flag,
209 aerl.receipt_missing_flag
210 from AP_EXPENSE_REPORT_LINES_ALL aerl,
211 AP_EXPENSE_REPORT_PARAMS_ALL erp
212 where aerl.report_header_id = p_report_header_id
213 and erp.parameter_id = aerl.web_parameter_id
214 and NVL(erp.expense_type_code,'NOT_DEFINED') not in ('PERSONAL','ROUNDING');
215
216 line_rec line_cur%ROWTYPE;
217
218 required_count NUMBER := 0;
219 missing_count NUMBER := 0;
220 l_receipts_status VARCHAR2(30);
221
222 BEGIN
223
224 SELECT receipts_status INTO l_receipts_status
225 FROM ap_expense_report_headers_all WHERE report_header_id = p_report_header_id;
226
227 IF l_receipts_status = C_STATUS_RECEIVED THEN
228 RETURN C_STATUS_RECEIVED;
229 END IF;
230
231 FOR line_rec IN line_cur LOOP
232
233 IF NVL(line_rec.receipt_required_flag,'N') = 'Y' THEN
234 IF NVL(line_rec.receipt_missing_flag,'N') = 'Y' THEN
235 missing_count := missing_count + 1;
236 ELSE
237 required_count := required_count + 1;
238 END IF;
239 END IF;
240
241 END LOOP;
242
243 IF required_count > 0 THEN
244 return C_STATUS_REQUIRED;
245 ELSIF missing_count > 0 THEN
246 return C_STATUS_MISSING;
247 ELSE
248 return C_STATUS_NOT_REQUIRED;
249 END IF;
250
251 END get_line_receipt_status;
252
253 /*========================================================================
254 | PUBLIC PROCEDURE handle_event
255 |
256 | DESCRIPTION
257 | This procedure handles a receipt management related event.
258 |
259 | Note if this logic is called from BC4J, then the changes on the page
260 | need to be posted in order for this function to be able to see them.
261 | To do that, call this function from the OAViewObjectImpl.beforeCommit.
262 |
263 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
264 | Called from BC4J and workflow logic.
265 |
266 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
267 |
268 | RETURNS
269 | Receipt status as VARCHAR2.
270 |
271 | PARAMETERS
272 | p_report_header_id IN Expense report identifier
273 | p_event IN event taken on the report one of the following:
274 | C_EVENT_WAIVE_RECEIPTS
275 | C_EVENT_WAIVE_COMPLETE
276 | C_EVENT_RECEIVE_RECEIPTS
277 | C_EVENT_RECEIPTS_NOT_RECEIVED
278 | C_EVENT_MR_SHORTPAY
279 | C_EVENT_PV_SHORTPAY
280 | C_EVENT_SHORTPAY
281 | C_EVENT_REJECT
282 | C_EVENT_REQUEST_INFO
283 | C_EVENT_RELEASE_HOLD
284 | C_EVENT_COMPLETE_AUDIT
285 |
286 | MODIFICATION HISTORY
287 | Date Author Description of Changes
288 | 16-Aug-2004 J Rautiainen Created
289 |
290 *=======================================================================*/
291 PROCEDURE handle_event(p_report_header_id IN NUMBER,
292 p_event IN VARCHAR2) IS
293
294 CURSOR header_cur IS
295 select aerh.*
296 from AP_EXPENSE_REPORT_HEADERS_ALL aerh
297 where aerh.report_header_id = p_report_header_id
298 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
299
300 CURSOR split_cc_cur IS
301 select aerh.report_header_id
302 from AP_EXPENSE_REPORT_HEADERS_ALL aerh
303 where aerh.BOTHPAY_PARENT_ID = p_report_header_id
304 and holding_report_header_id is not null;
305
306 CURSOR orig_cc_cur(l_report_header_id IN NUMBER) IS
307 select aerh.report_header_id
308 from AP_EXPENSE_REPORT_HEADERS_ALL aerh
309 where aerh.report_header_id = l_report_header_id
310 and holding_report_header_id is not null
311 and NOT EXISTS (select 1
312 from ap_expense_report_lines_all aerl
313 where aerl.report_header_id = aerh.report_header_id
314 and aerl.credit_card_trx_id IS NULL);
315
316 header_rec header_cur%ROWTYPE;
317 split_cc_rec split_cc_cur%ROWTYPE;
318 l_new_status VARCHAR2(30);
319 l_line_status VARCHAR2(30);
320 l_temp_org_status VARCHAR2(20);
321 l_temp_img_status VARCHAR2(30);
322 BEGIN
323 IF ( p_report_header_id IS NULL
324 OR p_event IS NULL
325 OR p_event NOT IN (C_EVENT_WAIVE_RECEIPTS,
326 C_EVENT_WAIVE_COMPLETE,
327 C_EVENT_RECEIVE_RECEIPTS,
328 C_EVENT_RECEIPTS_NOT_RECEIVED,
329 C_EVENT_MR_SHORTPAY,
330 C_EVENT_MIR_SHORTPAY,
331 C_EVENT_MBR_SHORTPAY,
332 C_EVENT_PV_SHORTPAY,
333 C_EVENT_SHORTPAY,
334 C_EVENT_REJECT,
335 C_EVENT_REQUEST_INFO,
336 C_EVENT_RELEASE_HOLD,
337 C_EVENT_COMPLETE_AUDIT )
338 ) THEN
339 return;
340 END IF;
341
342 OPEN header_cur;
343 FETCH header_cur INTO header_rec;
344 l_new_status := get_receipt_status(p_report_header_id, p_event);
345
346 IF (header_rec.report_header_id IS NOT NULL) THEN
347 IF (p_event = C_EVENT_MIR_SHORTPAY) THEN
348 l_line_status := get_line_receipt_status(p_report_header_id);
349 UPDATE ap_expense_report_headers_all
350 SET image_receipts_status = l_new_status,
351 receipts_status = l_line_status
352 WHERE CURRENT OF header_cur;
353 ELSIF (p_event = C_EVENT_MBR_SHORTPAY) THEN
354 UPDATE ap_expense_report_headers_all
355 SET image_receipts_status = l_new_status,
356 receipts_status = l_new_status
357 WHERE CURRENT OF header_cur;
358 ELSE
359 l_line_status := get_image_receipt_status(p_report_header_id, p_event);
360 UPDATE ap_expense_report_headers_all
361 SET receipts_status = l_new_status,
362 image_receipts_status = l_line_status
363 WHERE CURRENT OF header_cur;
364 END IF;
365 END IF;
366 IF (l_new_status IS NOT NULL AND header_rec.receipts_status IS NOT NULL)
367 AND (l_new_status = C_STATUS_NOT_REQUIRED AND header_rec.receipts_status <> C_STATUS_NOT_REQUIRED) THEN
368 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
369 END IF;
370
371 IF (p_event = C_EVENT_WAIVE_RECEIPTS OR p_event = C_EVENT_WAIVE_COMPLETE) THEN
372 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
373 ELSIF (p_event = C_EVENT_RECEIVE_RECEIPTS) THEN
374 AP_WEB_RECEIPTS_WF.RaiseReceivedEvent(p_report_header_id);
375 ELSIF (p_event = C_EVENT_RECEIPTS_NOT_RECEIVED) THEN
376 -- currently no additional processing for this event
377 null;
378 ELSIF (p_event = C_EVENT_REJECT) THEN
379 /* Aborted event is raised in Expenses WF SetRejectStatusAndResetAttr(),
380 * which is shared by manager rejection. However since the auditor can
381 * reject prior to manager approval, raising the event here */
382 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
383
384 ELSIF (p_event = C_EVENT_REQUEST_INFO) THEN
385 -- Bug 12746833: Abort shortpay process (if exists), when the auditor requests more info
386 AbortShortPayProcess(p_report_header_id);
387 ELSIF (p_event = C_EVENT_RELEASE_HOLD) THEN
388 AP_WEB_HOLDS_WF.ReadyForPayment(p_report_header_id);
389
390 IF header_rec.source = 'Both Pay' THEN
391 /* For both pay reports the status is reset on the header only if the original report contains only
392 * credit card transaction lines */
393 FOR orig_cc_rec IN orig_cc_cur(header_rec.BOTHPAY_PARENT_ID) LOOP
394 AP_WEB_HOLDS_WF.ReadyForPayment(orig_cc_rec.report_header_id);
395 AP_WEB_HOLDS_WF.RaiseReleasedEvent(orig_cc_rec.report_header_id);
396 END LOOP;
397 ELSE
398 AP_WEB_HOLDS_WF.RaiseReleasedEvent(p_report_header_id);
399
400 /* For reports from which cc transactions were split into separate .1 report, release hold also releases
401 * the hold on the split report */
402 FOR split_cc_rec IN split_cc_cur LOOP
403 AP_WEB_HOLDS_WF.ReadyForPayment(split_cc_rec.report_header_id);
404 END LOOP;
405 END IF;
406 ELSIF (p_event = C_EVENT_SHORTPAY) THEN
407 IF l_new_status = C_STATUS_NOT_REQUIRED THEN
408 UPDATE AP_EXPENSE_REPORT_HEADERS_ALL
409 SET receipts_received_date = null
410 WHERE CURRENT OF header_cur;
411 END IF;
412
413 ELSIF (p_event = C_EVENT_MR_SHORTPAY) THEN
414
415 UPDATE AP_EXPENSE_REPORT_HEADERS_ALL
416 SET receipts_received_date = null, report_filing_number = null
417 WHERE CURRENT OF header_cur;
418
419 ELSIF (p_event = C_EVENT_MIR_SHORTPAY) THEN
420
421 UPDATE AP_EXPENSE_REPORT_HEADERS_ALL
422 SET image_receipts_received_date = null
423 WHERE CURRENT OF header_cur;
424
425 ELSIF (p_event = C_EVENT_MBR_SHORTPAY) THEN
426
427 UPDATE AP_EXPENSE_REPORT_HEADERS_ALL
428 SET image_receipts_received_date = null, receipts_received_date = null, report_filing_number = null
429 WHERE CURRENT OF header_cur;
430
431 ELSIF (p_event = C_EVENT_PV_SHORTPAY) THEN
432 -- currently no additional processing for this event
433 null;
434 ELSIF (p_event = C_EVENT_COMPLETE_AUDIT) THEN
435 IF header_rec.shortpay_parent_id is not null
436 AND header_rec.receipts_status = C_STATUS_RECEIVED
437 AND 'Y' = AP_WEB_RECEIPT_MANAGEMENT_UTIL.is_shortpaid_report(p_report_header_id, AP_WEB_RECEIPTS_WF.C_POLICY_VIOLATION_PROCESS) THEN
438 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
439
440 ELSIF ( header_rec.shortpay_parent_id is not null
441 AND (header_rec.receipts_status <> 'NOT_REQUIRED' OR header_rec.image_receipts_status <> 'NOT_REQUIRED')) THEN
442 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
443 l_temp_org_status := get_receipt_status(p_report_header_id, C_EVENT_NONE);
444 l_temp_img_status := get_image_receipt_status(p_report_header_id, C_EVENT_NONE);
445 UPDATE ap_expense_report_headers_all
446 SET receipts_status = l_temp_org_status,
447 image_receipts_status = l_temp_img_status
448 WHERE report_header_id = p_report_header_id;
449 ELSIF header_rec.receipts_status = 'RECEIVED' THEN
450 AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(p_report_header_id);
451 END IF;
452 END IF;
453
454 CLOSE header_cur;
455 END handle_event;
456
457 /*========================================================================
458 | PUBLIC FUNCTION is_shortpaid_report
459 |
460 | DESCRIPTION
461 | This function detects whether a report is a shortpaid report.
462 |
463 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
464 | Called from BC4J and workflow logic.
465 |
466 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
467 |
468 | RETURNS
469 | Y or N depending whether the report is shortpaid of the particualr type.
470 |
471 | PARAMETERS
472 | p_report_header_id IN Expense report identifier
473 | p_shortpay_type IN type of the shortpay:
474 | AP_WEB_RECEIPTS_WF.C_NO_RECEIPTS_SHORTPAY_PROCESS
475 | AP_WEB_RECEIPTS_WF.C_POLICY_VIOLATION_PROCESS
476 |
477 | MODIFICATION HISTORY
478 | Date Author Description of Changes
479 | 31-Dec-2004 J Rautiainen Created
480 |
481 *=======================================================================*/
482 FUNCTION is_shortpaid_report(p_report_header_id IN NUMBER,
483 p_shortpay_type IN VARCHAR2) RETURN VARCHAR2 IS
484
485 CURSOR wf_cur IS
486 select 1
487 from wf_items wf
488 where wf.item_type = 'APEXP'
489 and wf.item_key = to_char(p_report_header_id) -- Bug 6841589 (sodash) to solve the invalid number exception
490 and wf.end_date is null
491 and wf.root_activity = p_shortpay_type
492 and rownum = 1;
493
494 wf_rec wf_cur%ROWTYPE;
495 BEGIN
496 IF p_report_header_id IS NULL
497 OR p_shortpay_type IS NULL
498 OR p_shortpay_type not in (AP_WEB_RECEIPTS_WF.C_NO_RECEIPTS_SHORTPAY_PROCESS,
499 AP_WEB_RECEIPTS_WF.C_POLICY_VIOLATION_PROCESS) THEN
500 return 'N';
501 END IF;
502
503 OPEN wf_cur;
504 FETCH wf_cur INTO wf_rec;
505 IF wf_cur%FOUND THEN
506 CLOSE wf_cur;
507 return 'Y';
508 ELSE
509 CLOSE wf_cur;
510 return 'N';
511 END IF;
512
513 END is_shortpaid_report;
514
515
516 FUNCTION get_image_receipt_status(p_report_header_id IN NUMBER,
517 p_event IN VARCHAR2 DEFAULT C_EVENT_NONE) RETURN VARCHAR2 IS
518
519 l_line_status VARCHAR2(30);
520 l_header_status VARCHAR2(30);
521 l_receipts_status VARCHAR2(30);
522
523 BEGIN
524
525 SELECT receipts_status, image_receipts_status INTO l_receipts_status, l_header_status
526 FROM ap_expense_report_headers_all WHERE report_header_id = p_report_header_id;
527
528 IF l_header_status = C_STATUS_NOT_REQUIRED THEN
529 return C_STATUS_NOT_REQUIRED;
530 ELSIF l_header_status = C_STATUS_RECEIVED THEN
531 return C_STATUS_RECEIVED;
532 ELSIF p_event = C_EVENT_COMPLETE_AUDIT AND l_receipts_status = C_STATUS_RECEIVED THEN
533 return C_STATUS_WAIVED;
534 END IF;
535
536 l_line_status := AP_WEB_UTILITIES_PKG.GetImageAttachmentStatus(p_report_header_id);
537
538 IF l_line_status = C_STATUS_NOT_REQUIRED THEN
539 return C_STATUS_NOT_REQUIRED;
540 END IF;
541
542 IF p_event = C_EVENT_RECEIPTS_IN_TRANSIT THEN
543 return C_STATUS_IN_TRANSIT;
544 ELSIF p_event = C_EVENT_WAIVE_RECEIPTS OR p_event = C_EVENT_WAIVE_COMPLETE THEN
545 return C_STATUS_WAIVED;
546 ELSIF l_header_status = C_STATUS_MISSING OR l_header_status = C_STATUS_RECEIVED OR l_header_status = C_STATUS_OVERDUE
547 OR l_header_status = C_STATUS_WAIVED or l_header_status = C_STATUS_IN_TRANSIT THEN
548 return l_header_status;
549 ELSE
550 return l_line_status;
551 END IF;
552
553 END get_image_receipt_status;
554
555
556 PROCEDURE AbortShortPayProcess(p_report_header_id IN NUMBER) IS
557
558 l_debug_info VARCHAR2(1000);
559 l_is_missing_shortpay VARCHAR2(1) := 'N';
560 l_is_policy_shortpay VARCHAR2(1) := 'N';
561 l_curr_calling_sequence VARCHAR2(100) := 'AbortShortPayProcess';
562
563 BEGIN
564
565 ------------------------------------------------------------
566 l_debug_info := 'Check if Missing Receipts Shortpay';
567 ------------------------------------------------------------
568 l_is_missing_shortpay := IsShortpay(p_report_header_id, 'NO_RECEIPTS_SHORTPAY_PROCESS');
569
570 IF l_is_missing_shortpay = 'Y' THEN
571 ----------------------------------------------------------
572 l_debug_info := 'Complete Missing Shortpay Process';
573 ----------------------------------------------------------
574 BEGIN
575 CompleteShortpay(p_report_header_id,
576 'INFORM_PREPARER_SHORTPAY',
577 'AP_WILL_SUBMIT');
578 CompleteShortpay(p_report_header_id,
579 'INFORM_PREPARER_IMAGE_SHORTPAY',
580 'WILL_SEND');
581 CompleteShortpay(p_report_header_id,
582 'INFORM_PREPARER_IMAGE_SHRTP_M',
583 'WILL_SEND');
584 EXCEPTION
585 WHEN OTHERS THEN
586 NULL;
587 END;
588 ELSE
589 ------------------------------------------------------------
590 l_debug_info := 'Check if Policy Violation Shortpay';
591 ------------------------------------------------------------
592 l_is_policy_shortpay := IsShortpay(p_report_header_id, 'POLICY_VIOLATION_PROCESS');
593
594 IF l_is_policy_shortpay = 'Y' THEN
595 ----------------------------------------------------------
596 l_debug_info := 'Complete Policy Shortpay Process';
597 ----------------------------------------------------------
598 BEGIN
599 CompleteShortpay(p_report_header_id,
600 'POLICY_SHORTPAY_NOTICE',
601 'AP_PROVIDE MISSING_INFO');
602 EXCEPTION
603 WHEN OTHERS THEN
604 NULL;
605 END;
606 END IF;
607 END IF;
608
609 EXCEPTION
610 WHEN OTHERS THEN
611 AP_WEB_DB_UTIL_PKG.RaiseException(l_curr_calling_sequence, l_debug_info);
612 APP_EXCEPTION.RAISE_EXCEPTION;
613 END AbortShortPayProcess;
614
615
616 FUNCTION IsShortpay(p_report_header_id IN NUMBER,
617 p_shortpay_type IN VARCHAR2) RETURN VARCHAR2 IS
618
619 l_debug_info VARCHAR2(1000);
620 l_is_shortpay VARCHAR2(1) := 'N';
621 l_curr_calling_sequence VARCHAR2(100) := 'IsShortpay';
622
623 BEGIN
624
625 ------------------------------------------------------------
626 l_debug_info := 'Check if Missing or Policy Shortpay';
627 ------------------------------------------------------------
628 select 'Y'
629 into l_is_shortpay
630 from ap_expense_report_headers_all aerh,
631 wf_items wf
632 where aerh.report_header_id = p_report_header_id
633 and aerh.shortpay_parent_id is not null
634 and wf.item_type = 'APEXP'
635 and wf.item_key = to_char(aerh.report_header_id)
636 and wf.end_date is null
637 and wf.root_activity = p_shortpay_type
638 and rownum = 1;
639
640 return l_is_shortpay;
641
642 EXCEPTION
643 WHEN NO_DATA_FOUND THEN
644 RETURN 'N';
645 WHEN OTHERS THEN
646 AP_WEB_DB_UTIL_PKG.RaiseException(l_curr_calling_sequence, l_debug_info);
647 APP_EXCEPTION.RAISE_EXCEPTION;
648 END IsShortpay;
649
650
651 PROCEDURE CompleteShortpay(p_report_header_id IN NUMBER,
652 p_activity IN VARCHAR2,
653 p_result IN VARCHAR2) IS
654
655 l_debug_info VARCHAR2(1000);
656 l_curr_calling_sequence VARCHAR2(100) := 'CompleteShortpay';
657
658 BEGIN
659
660 ----------------------------------------------------------
661 l_debug_info := 'Complete Missing or Policy Shortpay Process';
662 ----------------------------------------------------------
663 BEGIN
664 WF_ENGINE.CompleteActivityInternalName('APEXP',
665 p_report_header_id,
666 p_activity,
667 p_result);
668 EXCEPTION
669 WHEN OTHERS THEN
670 NULL;
671 END;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 AP_WEB_DB_UTIL_PKG.RaiseException(l_curr_calling_sequence, l_debug_info);
676 APP_EXCEPTION.RAISE_EXCEPTION;
677 END CompleteShortpay;
678
679
680 END AP_WEB_RECEIPT_MANAGEMENT_UTIL;
[Home] [Help]