[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_RECEIPTS_WF
Source
1 PACKAGE BODY AP_WEB_RECEIPTS_WF AS
2 /* $Header: apwrecptb.pls 120.33.12020000.3 2012/10/17 09:45:56 dsadipir ship $ */
3
4 ------------------------
5 -- Day to minutes conversion 24*60
6 ------------------------
7 C_DAY_TO_MINUTES NUMBER := 1440;
8
9 ------------------------
10 -- Events
11 ------------------------
12 -- Event key is used for item key and result code when aborting the track process
13 -- Event name is the true event name
14 -- Item/Event key is in the form of: '<Expense Report Id>:<Event key>:<DD-MON-RRRR HH:MI:SS>'
15 C_OVERDUE_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.overdue';
16 C_OVERDUE_ORIG_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.overdue.original';
17 C_OVERDUE_IMG_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.overdue.imaged';
18 C_OVERDUE_BOTH_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.overdue.both';
19 C_OVERDUE_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.receipts.overdue';
20 C_MISSING_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.missing';
21 C_MISSING_ORIG_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.missing.original';
22 C_MISSING_IMG_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.missing.imaged';
23 C_MISSING_BOTH_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.missing.both';
24 C_MISSING_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.receipts.missing';
25 C_RECEIVED_IMG_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.received.imaged';
26 C_RECEIVED_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.received';
27 C_RECEIVED_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.receipts.received';
28 C_ABORTED_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.aborted';
29 C_ABORTED_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.receipts.aborted';
30
31 -- Item Key Delimeter
32 C_ITEM_KEY_DELIM CONSTANT VARCHAR2(1) := ':';
33
34
35 ------------------------------------------------------------------------
36 FUNCTION ParseItemKey(
37 p_item_type IN VARCHAR2,
38 p_item_key IN VARCHAR2) RETURN VARCHAR2 IS
39 ------------------------------------------------------------------------
40 l_debug_info VARCHAR2(200);
41
42 l_item_key wf_items.item_key%TYPE;
43
44 BEGIN
45
46
47 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start ParseItemKey');
48
49 ----------------------------------------------------------
50 l_debug_info := 'Parse the item key for the Expense Report Id';
51 ----------------------------------------------------------
52 return substrb(p_item_key, 1, instrb(p_item_key, C_ITEM_KEY_DELIM)-1);
53
54 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end ParseItemKey');
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'ParseItemKey',
59 p_item_type, p_item_key, l_debug_info);
60 raise;
61 END ParseItemKey;
62
63
64 ------------------------------------------------------------------------
65 FUNCTION IsNotifRuleSetup(
66 p_org_id IN NUMBER,
67 p_report_submitted_date IN DATE) RETURN VARCHAR2 IS
68 ------------------------------------------------------------------------
69 l_debug_info VARCHAR2(200);
70
71 l_is_notif_rule_setup varchar2(1);
72
73 BEGIN
74
75 ------------------------------------------------------------
76 l_debug_info := 'Check if Notif Rules Setup';
77 ------------------------------------------------------------
78 select 'Y'
79 into l_is_notif_rule_setup
80 from AP_AUD_RULE_SETS rs,
81 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
82 where rsa.org_id = p_org_id
83 and rsa.rule_set_id = rs.rule_set_id
84 and rs.rule_set_type = C_NOTIFY_RULE
85 and TRUNC(p_report_submitted_date)
86 BETWEEN TRUNC(NVL(rsa.START_DATE, p_report_submitted_date))
87 AND TRUNC(NVL(rsa.END_DATE, p_report_submitted_date))
88 and rownum = 1;
89
90 return 'Y';
91
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 return 'N';
95 WHEN OTHERS THEN
96 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsNotifRuleSetup',
97 to_char(p_org_id), to_char(p_report_submitted_date), l_debug_info);
98 raise;
99 END IsNotifRuleSetup;
100
101
102 ------------------------------------------------------------------------
103 PROCEDURE IsNotifRuleSetup(
104 p_item_type IN VARCHAR2,
105 p_item_key IN VARCHAR2,
106 p_actid IN NUMBER,
107 p_funmode IN VARCHAR2,
108 p_result OUT NOCOPY VARCHAR2) IS
109 ------------------------------------------------------------------------
110 l_debug_info VARCHAR2(200);
111
112 l_org_id number;
113 l_expense_report_id number;
114 l_report_submitted_date AP_EXPENSE_REPORT_HEADERS.report_submitted_date%type;
115
116 l_is_notif_rule_setup VARCHAR2(1);
117
118 BEGIN
119
120 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsNotifRuleSetup');
121
122 IF (p_funmode = 'RUN') THEN
123
124 ----------------------------------------------------------
125 l_debug_info := 'Parse the item key for the Expense Report Id';
126 ----------------------------------------------------------
127 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
128
129 if (l_expense_report_id is null) then
130 Wf_Core.Raise('InvalidExpenseReportId');
131 end if;
132
133 ----------------------------------------------------------
134 l_debug_info := 'Get the Expense Report Org Id';
135 ----------------------------------------------------------
136 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
137 l_org_id := NULL;
138 END IF;
139
140 ----------------------------------------------------------
141 l_debug_info := 'Get Expense Report data';
142 ----------------------------------------------------------
143 select report_submitted_date
144 into l_report_submitted_date
145 from ap_expense_report_headers_all
146 where report_header_id = l_expense_report_id;
147
148 ------------------------------------------------------------
149 l_debug_info := 'Check if Notif Rules Setup';
150 ------------------------------------------------------------
151 l_is_notif_rule_setup := IsNotifRuleSetup(l_org_id, l_report_submitted_date);
152
153 p_result := 'COMPLETE:'||l_is_notif_rule_setup;
154
155 ELSIF (p_funmode = 'CANCEL') THEN
156
157 p_result := 'COMPLETE';
158
159 END IF; -- p_funmode = 'RUN'
160
161 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsNotifRuleSetup');
162
163 EXCEPTION
164 WHEN OTHERS THEN
165 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsNotifRuleSetup',
166 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
167 raise;
168 END IsNotifRuleSetup;
169
170
171 ------------------------------------------------------------------------
172 FUNCTION GenerateEventKey(
173 p_expense_report_id IN NUMBER,
174 p_event_key IN VARCHAR2) RETURN VARCHAR2 IS
175 ------------------------------------------------------------------------
176
177 l_timestamp varchar2(30);
178
179 BEGIN
180
181 select to_char(sysdate, 'DD-MON-RRRR HH:MI:SS')
182 into l_timestamp
183 from dual;
184
185 return p_expense_report_id||C_ITEM_KEY_DELIM||p_event_key||C_ITEM_KEY_DELIM||l_timestamp;
186
187 END GenerateEventKey;
188
189
190 ------------------------------------------------------------------------
191 FUNCTION EventKeyExists(
192 p_event_key IN VARCHAR2) RETURN BOOLEAN IS
193 ------------------------------------------------------------------------
194
195 l_event_key_exists varchar2(1) := 'N';
196
197 BEGIN
198
199 select 'Y'
200 into l_event_key_exists
201 from wf_items
202 where item_type = C_APWRECPT
203 and item_key = p_event_key
204 and rownum = 1;
205
206 return true;
207
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 return false;
211
212 END EventKeyExists;
213
214
215 ------------------------------------------------------------------------
216 PROCEDURE RaiseOverdueEvent(
217 p_expense_report_id IN NUMBER) IS
218 ------------------------------------------------------------------------
219 l_debug_info VARCHAR2(200);
220
221 l_event_key wf_items.item_key%type;
222
223 BEGIN
224
225 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseOverdueEvent');
226
227 ----------------------------------------------------------
228 l_debug_info := 'Generate Event Key';
229 ----------------------------------------------------------
230 l_event_key := GenerateEventKey(p_expense_report_id, C_OVERDUE_EVENT_KEY);
231
232 ----------------------------------------------------------
233 l_debug_info := 'Check Event Key';
234 ----------------------------------------------------------
235 if (NOT EventKeyExists(l_event_key)) then
236
237 ----------------------------------------------------------
238 l_debug_info := 'Raise Overdue Event';
239 ----------------------------------------------------------
240 wf_event.raise(p_event_name => C_OVERDUE_EVENT_NAME,
241 p_event_key => l_event_key);
242 --p_parameters => l_parameter_list);
243
244 end if;
245
246 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseOverdueEvent');
247
248 EXCEPTION
249 WHEN OTHERS THEN
250 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseOverdueEvent',
251 p_expense_report_id, l_debug_info);
252 raise;
253 END RaiseOverdueEvent;
254
255
256 ------------------------------------------------------------------------
257 PROCEDURE RaiseMissingEvent(
258 p_expense_report_id IN NUMBER) IS
259 ------------------------------------------------------------------------
260 l_debug_info VARCHAR2(200);
261
262 l_event_key wf_items.item_key%type;
263
264 BEGIN
265
266 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseMissingEvent');
267
268 ----------------------------------------------------------
269 l_debug_info := 'Generate Event Key';
270 ----------------------------------------------------------
271 l_event_key := GenerateEventKey(p_expense_report_id, C_MISSING_EVENT_KEY);
272
273 ----------------------------------------------------------
274 l_debug_info := 'Check Event Key';
275 ----------------------------------------------------------
276 if (NOT EventKeyExists(l_event_key)) then
277
278 ----------------------------------------------------------
279 l_debug_info := 'Raise Missing Event';
280 ----------------------------------------------------------
281 wf_event.raise(p_event_name => C_MISSING_EVENT_NAME,
282 p_event_key => l_event_key);
283 --p_parameters => l_parameter_list);
284
285 end if;
286
287 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseMissingEvent');
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseMissingEvent',
292 p_expense_report_id, l_debug_info);
293 raise;
294 END RaiseMissingEvent;
295
296
297 ------------------------------------------------------------------------
298 PROCEDURE RaiseReceivedEvent(
299 p_expense_report_id IN NUMBER) IS
300 ------------------------------------------------------------------------
301 l_debug_info VARCHAR2(200);
302
303 l_event_key wf_items.item_key%type;
304
305 BEGIN
306
307 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseReceivedEvent');
308
309 ----------------------------------------------------------
310 l_debug_info := 'Generate Event Key';
311 ----------------------------------------------------------
312 l_event_key := GenerateEventKey(p_expense_report_id, C_RECEIVED_EVENT_KEY);
313
314 ----------------------------------------------------------
315 l_debug_info := 'Check Event Key';
316 ----------------------------------------------------------
317 if (NOT EventKeyExists(l_event_key)) then
318
319 ----------------------------------------------------------
320 l_debug_info := 'Raise Received Event';
321 ----------------------------------------------------------
322 wf_event.raise(p_event_name => C_RECEIVED_EVENT_NAME,
323 p_event_key => l_event_key);
324 --p_parameters => l_parameter_list);
325
326 end if;
327
328 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseReceivedEvent');
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseReceivedEvent',
333 p_expense_report_id, l_debug_info);
334 raise;
335 END RaiseReceivedEvent;
336
337
338 ------------------------------------------------------------------------
339 PROCEDURE RaiseAbortedEvent(
340 p_expense_report_id IN NUMBER) IS
341 ------------------------------------------------------------------------
342 l_debug_info VARCHAR2(200);
343
344 l_event_key wf_items.item_key%type;
345
346 BEGIN
347
348 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseAbortedEvent');
349
350 ----------------------------------------------------------
351 l_debug_info := 'Generate Event Key';
352 ----------------------------------------------------------
353 l_event_key := GenerateEventKey(p_expense_report_id, C_ABORTED_EVENT_KEY);
354
355 ----------------------------------------------------------
356 l_debug_info := 'Check Event Key';
357 ----------------------------------------------------------
358 if (NOT EventKeyExists(l_event_key)) then
359
360 ----------------------------------------------------------
361 l_debug_info := 'Update Receipts Status if not Received or Waived';
362 ----------------------------------------------------------
363 begin
364 update ap_expense_report_headers_all
365 set receipts_status = ''
366 where report_header_id = p_expense_report_id
367 and nvl(receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED)
368 and receipts_received_date is null;
369
370 update ap_expense_report_headers_all
371 set image_receipts_status = ''
372 where report_header_id = p_expense_report_id
373 and nvl(image_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED)
374 and image_receipts_received_date is null;
375 exception
376 when others then null;
377 end;
378
379 ----------------------------------------------------------
380 l_debug_info := 'Reset Held Reports';
381 ----------------------------------------------------------
382 -- Bug 4075804
383 begin
384 update ap_expense_report_headers_all
385 set expense_status_code = C_PENDING_HOLDS,
386 holding_report_header_id = null,
387 expense_last_status_date = sysdate
388 where holding_report_header_id = p_expense_report_id
389 and expense_status_code = C_PAYMENT_HELD;
390 exception
391 when others then null;
392 end;
393
394 ----------------------------------------------------------
395 l_debug_info := 'Raise Aborted Event';
396 ----------------------------------------------------------
397 wf_event.raise(p_event_name => C_ABORTED_EVENT_NAME,
398 p_event_key => l_event_key);
399 --p_parameters => l_parameter_list);
400
401 end if;
402
403 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseAbortedEvent');
404
405 EXCEPTION
406 WHEN OTHERS THEN
407 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseAbortedEvent',
408 p_expense_report_id, l_debug_info);
409 raise;
410 END RaiseAbortedEvent;
411
412
413 ------------------------------------------------------------------------
414 PROCEDURE RaiseAbortedEvent(
415 p_item_type IN VARCHAR2,
416 p_item_key IN VARCHAR2,
417 p_actid IN NUMBER,
418 p_funmode IN VARCHAR2,
419 p_result OUT NOCOPY VARCHAR2) IS
420 ------------------------------------------------------------------------
421 l_debug_info VARCHAR2(200);
422 l_receipt_type VARCHAR2(50);
423 l_event_key wf_items.item_key%type;
424 l_report_header_id NUMBER;
425
426 BEGIN
427
428 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseAbortedEvent');
429
430 IF (p_funmode = 'RUN') THEN
431
432 ----------------------------------------------------------
433 l_debug_info := 'Raise Aborted event';
434 ----------------------------------------------------------
435 /*RaiseAbortedEvent(WF_ENGINE.GetItemAttrNumber(p_item_type,
436 p_item_key,
437 'EXPENSE_REPORT_ID'));*/
438 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
439 p_item_key,
440 'EXPENSE_REPORT_ID');
441
442 l_event_key := GenerateEventKey(l_report_header_id, C_ABORTED_EVENT_KEY);
443
444 l_receipt_type := WF_ENGINE.GetActivityAttrText(p_item_type,
445 p_item_key,
446 p_actid,
447 'ABORT_RECEIPT_TYPE');
448 IF (NOT EventKeyExists(l_event_key)) THEN
449 IF (l_receipt_type = 'ORIGINAL') THEN
450 update ap_expense_report_headers_all
451 set receipts_status = ''
452 where report_header_id = l_report_header_id
453 and nvl(receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED)
454 and receipts_received_date is null;
455 ELSE
456 update ap_expense_report_headers_all
457 set image_receipts_status = ''
458 where report_header_id = l_report_header_id
459 and nvl(image_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED)
460 and image_receipts_received_date is null;
461 END IF;
462
463 begin
464 update ap_expense_report_headers_all
465 set expense_status_code = C_PENDING_HOLDS,
466 holding_report_header_id = null,
467 expense_last_status_date = sysdate
468 where holding_report_header_id = l_report_header_id
469 and expense_status_code = C_PAYMENT_HELD;
470 exception
471 when others then null;
472 end;
473
474 wf_event.raise(p_event_name => C_ABORTED_EVENT_NAME,
475 p_event_key => l_event_key);
476 --p_parameters => l_parameter_list);
477
478 END IF;
479
480 p_result := 'COMPLETE';
481
482 END IF; -- p_funmode = 'RUN'
483
484 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseAbortedEvent');
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseAbortedEvent',
489 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
490 raise;
491 END RaiseAbortedEvent;
492
493
494 ------------------------------------------------------------------------
495 PROCEDURE Init(
496 p_item_type IN VARCHAR2,
497 p_item_key IN VARCHAR2) IS
498 ------------------------------------------------------------------------
499 l_debug_info VARCHAR2(200);
500
501 l_textNameArr Wf_Engine.NameTabTyp;
502 l_numNameArr Wf_Engine.NameTabTyp;
503 l_textValArr Wf_Engine.TextTabTyp;
504 l_numValArr Wf_Engine.NumTabTyp;
505 iText NUMBER :=0;
506 iNum NUMBER :=0;
507
508 l_org_id number;
509 l_expense_report_id number;
510 l_created_by number;
511 l_preparer_id number;
512 l_preparer_name wf_users.name%type;
513 l_preparer_display_name wf_users.display_name%type;
514 l_employee_id number;
515 l_employee_name wf_users.name%type;
516 l_employee_display_name wf_users.display_name%type;
517 l_invoice_num AP_EXPENSE_REPORT_HEADERS.invoice_num%type;
518 l_cost_center AP_EXPENSE_REPORT_HEADERS.flex_concatenated%type;
519 l_total varchar2(80);
520 l_purpose AP_EXPENSE_REPORT_HEADERS.description%type;
521 l_report_submitted_date AP_EXPENSE_REPORT_HEADERS.report_submitted_date%type;
522 l_missing_img_just AP_EXPENSE_REPORT_HEADERS.missing_img_just%type;
523
524 l_notif_rule AP_AUD_RULE_SETS%ROWTYPE;
525 l_is_notif_rule_setup varchar2(1) := 'N';
526
527 BEGIN
528
529 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start Init');
530
531 ----------------------------------------------------------
532 l_debug_info := 'Parse the item key for the Expense Report Id';
533 ----------------------------------------------------------
534 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
535
536 if (l_expense_report_id is null) then
537 Wf_Core.Raise('InvalidExpenseReportId');
538 end if;
539
540 ----------------------------------------------------------
541 l_debug_info := 'Get the Expense Report Org Id';
542 ----------------------------------------------------------
543 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
544 l_org_id := NULL;
545 END IF;
546
547
548 ----------------------------------------------------------
549 l_debug_info := 'Get Expense Report data';
550 -- Note: was thinking of getting data from APEXP WF but we cannot
551 -- assume that Expenses WF still exists (may be purged)
552 ----------------------------------------------------------
553 select created_by,
554 employee_id,
555 invoice_num,
556 flex_concatenated,
557 to_char(nvl(AMT_DUE_CCARD_COMPANY,0)+nvl(AMT_DUE_EMPLOYEE,0),
558 FND_CURRENCY.Get_Format_Mask(default_currency_code,22))||' '||default_currency_code,
559 description,
560 report_submitted_date,
561 missing_img_just
562 into l_created_by,
563 l_employee_id,
564 l_invoice_num,
565 l_cost_center,
566 l_total,
567 l_purpose,
568 l_report_submitted_date,
569 l_missing_img_just
570 from ap_expense_report_headers_all
571 where report_header_id = l_expense_report_id;
572
573 ----------------------------------------------------------
574 l_debug_info := 'Get Preparer Id using Created By';
575 ----------------------------------------------------------
576 if (AP_WEB_DB_HR_INT_PKG.GetEmpIdForUser(l_created_by, l_preparer_id)) then
577 null;
578 end if;
579
580 ------------------------------------------------------------
581 l_debug_info := 'Get Name Info Associated With Preparer Id';
582 ------------------------------------------------------------
583 WF_DIRECTORY.GetUserName('PER',
584 l_preparer_id,
585 l_preparer_name,
586 l_preparer_display_name);
587
588 if (l_preparer_name is null) then
589 Wf_Core.Raise('InvalidOwner');
590 end if;
591
592 ----------------------------------------------------------
593 l_debug_info := 'Set the Preparer as the Owner of Receipts Mgmt Workflow Process.';
594 ----------------------------------------------------------
595 WF_ENGINE.SetItemOwner(p_item_type, p_item_key, l_preparer_name);
596
597 ----------------------------------------------------------
598 l_debug_info := 'Set Item User Key to Invoice Number for easier query ';
599 ----------------------------------------------------------
600 WF_ENGINE.SetItemUserKey(p_item_type,
601 p_item_key,
602 l_invoice_num);
603
604 --------------------------------------------------------
605 l_debug_info := 'Set EXPENSE_REPORT Item Attribute';
606 --------------------------------------------------------
607 iText := iText + 1;
608 l_textNameArr(iText) := 'EXPENSE_REPORT';
609 l_textValArr(iText) := l_invoice_num;
610
611 ------------------------------------------------------------
612 l_debug_info := 'Get Name Info Associated With Employee_Id';
613 ------------------------------------------------------------
614 WF_DIRECTORY.GetUserName('PER',
615 l_employee_id,
616 l_employee_name,
617 l_employee_display_name);
618
619 ----------------------------------------------------------
620 l_debug_info := 'Set ORG_ID Item Attribute';
621 ----------------------------------------------------------
622 iNum := iNum + 1;
623 l_numNameArr(iNum) := 'ORG_ID';
624 l_numValArr(iNum) := l_org_id;
625
626 ----------------------------------------------------------
627 l_debug_info := 'Set EXPENSE_REPORT_ID Item Attribute';
628 ----------------------------------------------------------
629 iNum := iNum + 1;
630 l_numNameArr(iNum) := 'EXPENSE_REPORT_ID';
631 l_numValArr(iNum) := l_expense_report_id;
632
633 ----------------------------------------------------------
634 l_debug_info := 'Set EXPENSE_REPORT_FOR Item Attribute';
635 ----------------------------------------------------------
636 iText := iText + 1;
637 l_textNameArr(iText) := 'EXPENSE_REPORT_FOR';
638 l_textValArr(iText) := l_employee_display_name;
639
640 ----------------------------------------------------------
641 l_debug_info := 'Set EXPENSE_REPORT_COST_CENTER Item Attribute';
642 ----------------------------------------------------------
643 iText := iText + 1;
644 l_textNameArr(iText) := 'EXPENSE_REPORT_COST_CENTER';
645 l_textValArr(iText) := l_cost_center;
646
647 ----------------------------------------------------------
648 l_debug_info := 'Set EXPENSE_REPORT_TOTAL Item Attribute';
649 ----------------------------------------------------------
650 iText := iText + 1;
651 l_textNameArr(iText) := 'EXPENSE_REPORT_TOTAL';
652 l_textValArr(iText) := l_total;
653
654 ----------------------------------------------------------
655 l_debug_info := 'Set EXPENSE_REPORT_PURPOSE Item Attribute';
656 ----------------------------------------------------------
657 iText := iText + 1;
658 l_textNameArr(iText) := 'EXPENSE_REPORT_PURPOSE';
659 l_textValArr(iText) := l_purpose;
660
661 ----------------------------------------------------------
662 l_debug_info := 'Set EXPENSE_REPORT_SUBMIT_DATE Item Attribute';
663 ----------------------------------------------------------
664 WF_ENGINE.SetItemAttrDate(p_item_type, p_item_key, 'EXPENSE_REPORT_SUBMIT_DATE', l_report_submitted_date);
665
666 ----------------------------------------------------------
667 l_debug_info := 'Check Notification Rule';
668 ----------------------------------------------------------
669 l_is_notif_rule_setup := IsNotifRuleSetup(l_org_id, l_report_submitted_date);
670
671 if (l_is_notif_rule_setup = 'Y') then
672
673 ----------------------------------------------------------
674 l_debug_info := 'Get the Notification Rule';
675 ----------------------------------------------------------
676 AP_WEB_AUDIT_UTILS.get_rule(l_org_id, l_report_submitted_date, C_NOTIFY_RULE, l_notif_rule);
677
678 ----------------------------------------------------------
679 l_debug_info := 'Set NOTIF_RULE_DAYS_OVERDUE Item Attribute';
680 ----------------------------------------------------------
681 /*
682 NOTIF_RULE_DAYS_OVERDUE is the rule for determining
683 what's considered overdue
684 process date - report submission date
685 */
686 iNum := iNum + 1;
687 l_numNameArr(iNum) := 'NOTIF_RULE_DAYS_OVERDUE';
688 l_numValArr(iNum) := l_notif_rule.NOTIFY_RCT_OVERDUE_DAYS; -- relative time in days
689 --l_numValArr(iNum) := 60; -- relative time in days
690
691 iNum := iNum + 1;
692 l_numNameArr(iNum) := 'NOTIF_IMAGE_DAYS_OVERDUE';
693 l_numValArr(iNum) := l_notif_rule.NOTIFY_IMG_RCT_OVERDUE_DAYS; -- relative time in days
694
695 ----------------------------------------------------------
696 l_debug_info := 'Set NOTIF_RULE_TIMEOUT Item Attribute';
697 ----------------------------------------------------------
698 /*
699 NOTIF_RULE_TIMEOUT is the rule for determining
700 how long to wait for a response from the preparer
701 */
702 iNum := iNum + 1;
703 l_numNameArr(iNum) := 'NOTIF_RULE_TIMEOUT';
704 l_numValArr(iNum) := l_notif_rule.NOTIFY_ACTION_REQUIRED_DAYS * C_DAY_TO_MINUTES; -- relative time in minutes
705 --l_numValArr(iNum) := 1440; -- relative time in minutes
706
707 iNum := iNum + 1;
708 l_numNameArr(iNum) := 'NOTIF_IMAGE_TIMEOUT';
709 l_numValArr(iNum) := l_notif_rule.NOTIFY_IMG_ACTION_REQ_DAYS * C_DAY_TO_MINUTES; -- relative time in minutes
710
711 ----------------------------------------------------------
712 l_debug_info := 'Set NOTIF_RULE_WAIT Item Attribute';
713 ----------------------------------------------------------
714 /*
715 NOTIF_RULE_WAIT is the rule for determining
716 how long to wait after a response from the preparer
717 */
718 iNum := iNum + 1;
719 l_numNameArr(iNum) := 'NOTIF_RULE_WAIT';
720 l_numValArr(iNum) := l_notif_rule.NOTIFY_RESPONSE_OVERDUE_DAYS; -- relative time in days
721 --l_numValArr(iNum) := 0; -- relative time in days
722
723 iNum := iNum + 1;
724 l_numNameArr(iNum) := 'NOTIF_IMAGE_WAIT';
725 l_numValArr(iNum) := l_notif_rule.NOTIFY_IMG_RESP_OVERDUE_DAYS; -- relative time in days
726
727 ----------------------------------------------------------
728 l_debug_info := 'Set NOTIF_RULE_MISSING_DECL_REQD Item Attribute';
729 ----------------------------------------------------------
730 /*
731 NOTIF_RULE_MISSING_DECL_REQD is the rule for determining
732 whether a missing receipt declaration is required or not
733 */
734 iText := iText + 1;
735 l_textNameArr(iText) := 'NOTIF_RULE_MISSING_DECL_REQD';
736 l_textValArr(iText) := l_notif_rule.NOTIFY_DOCUMENT_REQUIRED_CODE;
737 --l_textValArr(iText) := C_REQUIRED;
738
739 ----------------------------------------------------------
740 l_debug_info := 'Set NOTIF_RULE_NOTIF_RECEIVED Item Attribute';
741 ----------------------------------------------------------
742 /*
743 NOTIF_RULE_NOTIF_RECEIVED is the rule for determining
744 whether to notify the preparer when the receipts pkg is received
745 */
746 iText := iText + 1;
747 l_textNameArr(iText) := 'NOTIF_RULE_NOTIF_RECEIVED';
748 l_textValArr(iText) := l_notif_rule.NOTIFY_RCT_RECEIVED_CODE;
749 --l_textValArr(iText) := C_RECEIPTS_RECEIVED;
750
751 end if; -- (l_is_notif_rule_setup = 'Y')
752
753 ----------------------------------------------------------
754 l_debug_info := 'Set PREPARER_ROLE Item Attribute';
755 ----------------------------------------------------------
756 iText := iText + 1;
757 l_textNameArr(iText) := 'PREPARER_ROLE';
758 l_textValArr(iText) := l_preparer_name;
759
760 ----------------------------------------------------------
761 l_debug_info := 'Set MISSING_IMG_JUST Item Attribute';
762 ----------------------------------------------------------
763 iText := iText + 1;
764 l_textNameArr(iText) := 'MISSING_IMAGE_JUST';
765 l_textValArr(iText) := l_missing_img_just;
766
767 -----------------------------------------------------
768 l_debug_info := 'Set all number item attributes';
769 -----------------------------------------------------
770 WF_ENGINE.SetItemAttrNumberArray(p_item_type, p_item_key, l_numNameArr, l_numValArr);
771
772 -----------------------------------------------------
773 l_debug_info := 'Set all text item attributes';
774 -----------------------------------------------------
775 WF_ENGINE.SetItemAttrTextArray(p_item_type, p_item_key, l_textNameArr, l_textValArr);
776
777
778 if (l_is_notif_rule_setup = 'Y') then
779
780 ----------------------------------------------------------
781 l_debug_info := 'Set DAYS_OVERDUE Item Attribute';
782 ----------------------------------------------------------
783 /*
784 DAYS_OVERDUE is the diff between notif sent date and date the
785 the expense report receipts package became overdue
786 notif sent date - (report submission date + NOTIF_RULE_DAYS_OVERDUE)
787 iNum := iNum + 1;
788 l_numNameArr(iNum) := 'DAYS_OVERDUE';
789 l_numValArr(iNum) := 60; -- relative time in days
790 */
791 SetDaysOverdue(p_item_type, p_item_key);
792 SetImageOverdueDays(p_item_type, p_item_key);
793
794 end if; -- (l_is_notif_rule_setup = 'Y')
795
796
797 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end Init');
798
799 EXCEPTION
800 WHEN OTHERS THEN
801 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'Init',
802 p_item_type, p_item_key, l_expense_report_id, l_preparer_name, l_debug_info);
803 raise;
804 END Init;
805
806
807 ------------------------------------------------------------------------
808 PROCEDURE InitOverdue(
809 p_item_type IN VARCHAR2,
810 p_item_key IN VARCHAR2,
811 p_actid IN NUMBER,
812 p_funmode IN VARCHAR2,
813 p_result OUT NOCOPY VARCHAR2) IS
814 ------------------------------------------------------------------------
815 l_debug_info VARCHAR2(200);
816 l_item_key VARCHAR2(200) := lower(p_item_key);
817
818 BEGIN
819
820 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start InitOverdue');
821
822 IF (p_funmode = 'RUN') THEN
823
824 ----------------------------------------------------------
825 l_debug_info := 'Initialize common event data';
826 ----------------------------------------------------------
827 Init(p_item_type, p_item_key);
828
829 BEGIN
830 IF (INSTR(l_item_key,'both') <> 0) THEN
831 WF_ENGINE.SetItemAttrText(p_item_type,
832 p_item_key,
833 'OVERDUE_TYPE_REQUIRED',
834 'BOTH');
835 ELSIF (INSTR(l_item_key,'original') <> 0) THEN
836 WF_ENGINE.SetItemAttrText(p_item_type,
837 p_item_key,
838 'OVERDUE_TYPE_REQUIRED',
839 'ORIGINAL');
840 ELSIF (INSTR(l_item_key,'image') <> 0) THEN
841 WF_ENGINE.SetItemAttrText(p_item_type,
842 p_item_key,
843 'OVERDUE_TYPE_REQUIRED',
844 'IMAGE');
845 END IF;
846 EXCEPTION
847 WHEN OTHERS THEN
848 NULL;
849 END;
850
851
852 p_result := 'COMPLETE';
853
854 END IF; -- p_funmode = 'RUN'
855
856 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end InitOverdue');
857
858 EXCEPTION
859 WHEN OTHERS THEN
860 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'InitOverdue',
861 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
862 raise;
863 END InitOverdue;
864
865
866 ------------------------------------------------------------------------
867 PROCEDURE InitMissing(
868 p_item_type IN VARCHAR2,
869 p_item_key IN VARCHAR2,
870 p_actid IN NUMBER,
871 p_funmode IN VARCHAR2,
872 p_result OUT NOCOPY VARCHAR2) IS
873 ------------------------------------------------------------------------
874 l_debug_info VARCHAR2(200);
875 l_item_key VARCHAR2(200) := lower(p_item_key);
876
877 BEGIN
878
879 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start InitMissing');
880
881 IF (p_funmode = 'RUN') THEN
882
883 ----------------------------------------------------------
884 l_debug_info := 'Initialize common event data';
885 ----------------------------------------------------------
886 Init(p_item_type, p_item_key);
887
888 BEGIN
889 IF (INSTR(l_item_key,'both') <> 0) THEN
890 WF_ENGINE.SetItemAttrText(p_item_type,
891 p_item_key,
892 'MISSING_TYPE_REQUIRED',
893 'BOTH');
894 ELSIF (INSTR(l_item_key,'original') <> 0) THEN
895 WF_ENGINE.SetItemAttrText(p_item_type,
896 p_item_key,
897 'MISSING_TYPE_REQUIRED',
898 'ORIGINAL');
899 ELSIF (INSTR(l_item_key,'image') <> 0) THEN
900 WF_ENGINE.SetItemAttrText(p_item_type,
901 p_item_key,
902 'MISSING_TYPE_REQUIRED',
903 'IMAGE');
904 END IF;
905 EXCEPTION
906 WHEN OTHERS THEN
907 NULL;
908 END;
909
910 p_result := 'COMPLETE';
911
912 END IF; -- p_funmode = 'RUN'
913
914 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end InitMissing');
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'InitMissing',
919 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
920 raise;
921 END InitMissing;
922
923
924 ------------------------------------------------------------------------
925 PROCEDURE CheckOverdueExists(
926 p_item_type IN VARCHAR2,
927 p_item_key IN VARCHAR2,
928 p_actid IN NUMBER,
929 p_funmode IN VARCHAR2,
930 p_result OUT NOCOPY VARCHAR2) IS
931 ------------------------------------------------------------------------
932 l_debug_info VARCHAR2(200);
933
934 l_status wf_item_activity_statuses.ACTIVITY_STATUS%TYPE;
935 l_result wf_item_activity_statuses.ACTIVITY_RESULT_CODE%TYPE;
936
937 l_item_key wf_items.item_key%TYPE;
938 l_found_item_key wf_items.item_key%TYPE;
939
940 BEGIN
941
942 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckOverdueExists');
943
944 IF (p_funmode = 'RUN') THEN
945
946 begin
947
948 ----------------------------------------------------------
949 l_debug_info := 'Getting item key of current process';
950 ----------------------------------------------------------
951 l_item_key := ParseItemKey(p_item_type, p_item_key);
952
953 ----------------------------------------------------------
954 l_debug_info := 'Encode item key for Overdue process';
955 ----------------------------------------------------------
956 l_item_key := l_item_key||C_ITEM_KEY_DELIM||C_OVERDUE_EVENT_KEY||'%';
957
958 ----------------------------------------------------------
959 l_debug_info := 'Check for at least one Overdue process';
960 ----------------------------------------------------------
961 select item_key
962 into l_found_item_key
963 from wf_items
964 where item_type = p_item_type
965 and item_key like l_item_key
966 and end_date is null
967 and rownum = 1;
968
969 p_result := 'COMPLETE:Y';
970
971 exception
972 when no_data_found then
973 p_result := 'COMPLETE:N';
974 when others then
975 p_result := 'COMPLETE:N';
976
977 end;
978
979 ELSIF (p_funmode = 'CANCEL') THEN
980 p_result := 'COMPLETE';
981 END IF; -- p_funmode = 'RUN'
982
983 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckOverdueExists');
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckOverdueExists',
988 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
989 raise;
990 END CheckOverdueExists;
991
992
993 ------------------------------------------------------------------------
994 PROCEDURE CheckMissingExists(
995 p_item_type IN VARCHAR2,
996 p_item_key IN VARCHAR2,
997 p_actid IN NUMBER,
998 p_funmode IN VARCHAR2,
999 p_result OUT NOCOPY VARCHAR2) IS
1000 ------------------------------------------------------------------------
1001 l_debug_info VARCHAR2(200);
1002
1003 l_status wf_item_activity_statuses.ACTIVITY_STATUS%TYPE;
1004 l_result wf_item_activity_statuses.ACTIVITY_RESULT_CODE%TYPE;
1005
1006 l_item_key wf_items.item_key%TYPE;
1007 l_found_item_key wf_items.item_key%TYPE;
1008
1009 BEGIN
1010
1011 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckMissingExists');
1012
1013 IF (p_funmode = 'RUN') THEN
1014
1015 begin
1016
1017 ----------------------------------------------------------
1018 l_debug_info := 'Getting item key of current process';
1019 ----------------------------------------------------------
1020 l_item_key := ParseItemKey(p_item_type, p_item_key);
1021
1022 ----------------------------------------------------------
1023 l_debug_info := 'Encode item key for Missing process';
1024 ----------------------------------------------------------
1025 l_item_key := l_item_key||C_ITEM_KEY_DELIM||C_MISSING_EVENT_KEY||'%';
1026
1027 ----------------------------------------------------------
1028 l_debug_info := 'Check for at least one Missing process';
1029 ----------------------------------------------------------
1030 select item_key
1031 into l_found_item_key
1032 from wf_items
1033 where item_type = p_item_type
1034 and item_key like l_item_key
1035 and end_date is null
1036 and rownum = 1;
1037
1038 p_result := 'COMPLETE:Y';
1039
1040 exception
1041 when no_data_found then
1042 p_result := 'COMPLETE:N';
1043 when others then
1044 p_result := 'COMPLETE:N';
1045
1046 end;
1047
1048 ELSIF (p_funmode = 'CANCEL') THEN
1049 p_result := 'COMPLETE';
1050 END IF; -- p_funmode = 'RUN'
1051
1052 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckMissingExists');
1053
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckMissingExists',
1057 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1058 raise;
1059 END CheckMissingExists;
1060
1061
1062 ------------------------------------------------------------------------
1063 PROCEDURE AbortOverdue(
1064 p_item_type IN VARCHAR2,
1065 p_item_key IN VARCHAR2,
1066 p_actid IN NUMBER,
1067 p_funmode IN VARCHAR2,
1068 p_result OUT NOCOPY VARCHAR2) IS
1069 ------------------------------------------------------------------------
1070 l_debug_info VARCHAR2(200);
1071 l_expense_report_id NUMBER;
1072 l_receipts_status VARCHAR2(30);
1073 l_image_receipts_status VARCHAR2(30);
1074 l_expense_status_code VARCHAR2(30);
1075 l_workflow_flag VARCHAR2(1);
1076
1077 BEGIN
1078
1079 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start AbortOverdue');
1080
1081 IF (p_funmode = 'RUN') THEN
1082
1083 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
1084
1085 SELECT receipts_status, image_receipts_status, expense_status_code, workflow_approved_flag
1086 INTO l_receipts_status, l_image_receipts_status, l_expense_status_code, l_workflow_flag
1087 FROM ap_expense_report_headers_all WHERE report_header_id = l_expense_report_id;
1088
1089 IF (l_receipts_status = AP_WEB_RECEIPTS_WF.C_RECEIVED OR l_receipts_status = AP_WEB_RECEIPTS_WF.C_WAIVED OR
1090 l_receipts_status = AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED OR l_expense_status_code = 'REJECTED' OR l_expense_status_code = 'RETURNED' OR
1091 l_expense_status_code = 'WITHDRAWN') THEN
1092
1093 AbortProcess(p_item_type, p_item_key, C_OVERDUE_ORIG_EVENT_KEY);
1094
1095 END IF;
1096
1097 IF ((l_receipts_status = AP_WEB_RECEIPTS_WF.C_RECEIVED AND l_workflow_flag IN ('P', 'Y')) OR l_image_receipts_status = AP_WEB_RECEIPTS_WF.C_RECEIVED OR
1098 l_image_receipts_status = AP_WEB_RECEIPTS_WF.C_WAIVED OR l_image_receipts_status = AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED OR
1099 l_expense_status_code = 'REJECTED' OR l_expense_status_code = 'RETURNED' OR l_expense_status_code = 'WITHDRAWN') THEN
1100
1101 AbortProcess(p_item_type, p_item_key, C_OVERDUE_IMG_EVENT_KEY);
1102
1103 END IF;
1104
1105 p_result := 'COMPLETE';
1106
1107 END IF; -- p_funmode = 'RUN'
1108
1109 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end AbortOverdue');
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'AbortOverdue',
1114 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1115 raise;
1116 END AbortOverdue;
1117
1118
1119 ------------------------------------------------------------------------
1120 PROCEDURE AbortMissing(
1121 p_item_type IN VARCHAR2,
1122 p_item_key IN VARCHAR2,
1123 p_actid IN NUMBER,
1124 p_funmode IN VARCHAR2,
1125 p_result OUT NOCOPY VARCHAR2) IS
1126 ------------------------------------------------------------------------
1127 l_debug_info VARCHAR2(200);
1128
1129 l_item_key wf_items.item_key%type;
1130
1131 BEGIN
1132
1133 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start AbortMissing');
1134
1135 IF (p_funmode = 'RUN') THEN
1136
1137 AbortProcess(p_item_type, p_item_key, C_MISSING_EVENT_KEY);
1138
1139 p_result := 'COMPLETE';
1140
1141 END IF; -- p_funmode = 'RUN'
1142
1143 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end AbortMissing');
1144
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'AbortMissing',
1148 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1149 raise;
1150 END AbortMissing;
1151
1152
1153 ------------------------------------------------------------------------
1154 PROCEDURE AbortProcess(
1155 p_item_type IN VARCHAR2,
1156 p_item_key IN VARCHAR2,
1157 p_event_key IN VARCHAR2) IS
1158 ------------------------------------------------------------------------
1159 l_debug_info VARCHAR2(200);
1160
1161 l_item_key wf_items.item_key%type;
1162 l_found_item_key wf_items.item_key%type;
1163
1164 l_status wf_item_activity_statuses.ACTIVITY_STATUS%TYPE;
1165 l_result wf_item_activity_statuses.ACTIVITY_RESULT_CODE%TYPE;
1166
1167 -- cursor for receipt events
1168 CURSOR c_receipt_events is
1169 select item_key
1170 from wf_items
1171 where item_type = p_item_type
1172 and item_key like l_item_key
1173 and end_date is null;
1174
1175 BEGIN
1176
1177 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start AbortProcess');
1178
1179 ----------------------------------------------------------
1180 l_debug_info := 'Getting item key of current process';
1181 ----------------------------------------------------------
1182 l_item_key := ParseItemKey(p_item_type, p_item_key);
1183
1184 ----------------------------------------------------------
1185 l_debug_info := 'Encode item key for event process';
1186 ----------------------------------------------------------
1187 l_item_key := l_item_key||C_ITEM_KEY_DELIM||p_event_key||'%';
1188
1189 open c_receipt_events;
1190 loop
1191
1192 fetch c_receipt_events into l_found_item_key;
1193 exit when c_receipt_events%NOTFOUND;
1194
1195 ----------------------------------------------------------
1196 l_debug_info := 'Abort event process and use the item key as the result';
1197 ----------------------------------------------------------
1198 begin
1199
1200 WF_ENGINE.AbortProcess(p_item_type,
1201 l_found_item_key,
1202 null,
1203 wf_engine.eng_force);
1204
1205 exception
1206 when others then null;
1207 end;
1208
1209 end loop;
1210 close c_receipt_events;
1211
1212 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end AbortProcess');
1213
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'AbortProcess',
1217 p_item_type, p_item_key, p_event_key, l_debug_info);
1218 raise;
1219 END AbortProcess;
1220
1221
1222 ------------------------------------------------------------------------
1223 PROCEDURE InitReceived(
1224 p_item_type IN VARCHAR2,
1225 p_item_key IN VARCHAR2,
1226 p_actid IN NUMBER,
1227 p_funmode IN VARCHAR2,
1228 p_result OUT NOCOPY VARCHAR2) IS
1229 ------------------------------------------------------------------------
1230 l_debug_info VARCHAR2(200);
1231 l_item_key VARCHAR2(200) := lower(p_item_key);
1232
1233 BEGIN
1234
1235 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start InitReceived');
1236
1237 IF (p_funmode = 'RUN') THEN
1238
1239 ----------------------------------------------------------
1240 l_debug_info := 'Initialize common event data';
1241 ----------------------------------------------------------
1242 Init(p_item_type, p_item_key);
1243
1244 BEGIN
1245 IF (INSTR(l_item_key,'image') <> 0) THEN
1246 WF_ENGINE.SetItemAttrText(p_item_type,
1247 p_item_key,
1248 'RECEIVED_TYPE_REQUIRED',
1249 'IMAGE');
1250 ELSE
1251 WF_ENGINE.SetItemAttrText(p_item_type,
1252 p_item_key,
1253 'RECEIVED_TYPE_REQUIRED',
1254 'ORIGINAL');
1255 END IF;
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 NULL;
1259 END;
1260
1261
1262 p_result := 'COMPLETE';
1263
1264 END IF; -- p_funmode = 'RUN'
1265
1266 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end InitReceived');
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'InitReceived',
1271 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1272 raise;
1273 END InitReceived;
1274
1275
1276 ------------------------------------------------------------------------
1277 PROCEDURE InitAborted(
1278 p_item_type IN VARCHAR2,
1279 p_item_key IN VARCHAR2,
1280 p_actid IN NUMBER,
1281 p_funmode IN VARCHAR2,
1282 p_result OUT NOCOPY VARCHAR2) IS
1283 ------------------------------------------------------------------------
1284 l_debug_info VARCHAR2(200);
1285
1286 BEGIN
1287
1288 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start InitAborted');
1289
1290 IF (p_funmode = 'RUN') THEN
1291
1292 ----------------------------------------------------------
1293 l_debug_info := 'Initialize common event data';
1294 ----------------------------------------------------------
1295 Init(p_item_type, p_item_key);
1296
1297 p_result := 'COMPLETE';
1298
1299 END IF; -- p_funmode = 'RUN'
1300
1301 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end InitAborted');
1302
1303 EXCEPTION
1304 WHEN OTHERS THEN
1305 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'InitAborted',
1306 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1307 raise;
1308 END InitAborted;
1309
1310 ------------------------------------------------------------------------
1311 FUNCTION GetReceiptsStatus(
1312 p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
1313 ------------------------------------------------------------------------
1314 l_debug_info VARCHAR2(200);
1315
1316 l_receipts_status varchar2(30);
1317
1318 BEGIN
1319
1320 ------------------------------------------------------------
1321 l_debug_info := 'Retrieve current Receipt Status';
1322 ------------------------------------------------------------
1323 select receipts_status
1324 into l_receipts_status
1325 from ap_expense_report_headers_all
1326 where report_header_id = p_report_header_id;
1327
1328 return l_receipts_status;
1329
1330 EXCEPTION
1331 WHEN NO_DATA_FOUND THEN
1332 return null;
1333 WHEN OTHERS THEN
1334 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'GetReceiptsStatus',
1335 to_char(p_report_header_id), l_debug_info);
1336 raise;
1337 END GetReceiptsStatus;
1338
1339
1340 ------------------------------------------------------------------------
1341 PROCEDURE GetReceiptsStatus(
1342 p_item_type IN VARCHAR2,
1343 p_item_key IN VARCHAR2,
1344 p_actid IN NUMBER,
1345 p_funmode IN VARCHAR2,
1346 p_result OUT NOCOPY VARCHAR2) IS
1347 ------------------------------------------------------------------------
1348 l_debug_info VARCHAR2(200);
1349
1350 l_receipts_status varchar2(30);
1351 l_report_header_id number;
1352
1353 BEGIN
1354
1355 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start GetReceiptsStatus');
1356
1357 IF (p_funmode = 'RUN') THEN
1358
1359 ------------------------------------------------------------
1360 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1361 ------------------------------------------------------------
1362 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1363 p_item_key,
1364 'EXPENSE_REPORT_ID');
1365
1366 ------------------------------------------------------------
1367 l_debug_info := 'Retrieve current Receipt Status';
1368 ------------------------------------------------------------
1369 l_receipts_status := GetReceiptsStatus(l_report_header_id);
1370
1371 p_result := 'COMPLETE:'||l_receipts_status;
1372
1373 END IF; -- p_funmode = 'RUN'
1374
1375 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end GetReceiptsStatus');
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'GetReceiptsStatus',
1380 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1381 raise;
1382 END GetReceiptsStatus;
1383
1384
1385 ------------------------------------------------------------------------
1386 PROCEDURE SetReceiptsStatus(
1387 p_report_header_id IN NUMBER,
1388 p_receipts_status IN VARCHAR2) IS
1389 ------------------------------------------------------------------------
1390 l_debug_info VARCHAR2(200);
1391
1392 l_orig_receipts_status varchar2(30);
1393
1394 BEGIN
1395
1396 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetReceiptsStatus');
1397
1398 ------------------------------------------------------------
1399 l_debug_info := 'Lock current Receipt Status';
1400 ------------------------------------------------------------
1401 select receipts_status
1402 into l_orig_receipts_status
1403 from ap_expense_report_headers_all
1404 where report_header_id = p_report_header_id
1405 for update of receipts_status nowait;
1406
1407 ------------------------------------------------------------
1408 l_debug_info := 'Update current Receipt Status';
1409 ------------------------------------------------------------
1410 update ap_expense_report_headers_all
1411 set receipts_status = p_receipts_status
1412 where report_header_id = p_report_header_id;
1413
1414 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetReceiptsStatus');
1415
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetReceiptsStatus',
1419 to_char(p_report_header_id), p_receipts_status, l_debug_info);
1420 raise;
1421 END SetReceiptsStatus;
1422
1423
1424 ------------------------------------------------------------------------
1425 PROCEDURE SetReceiptsStatus(
1426 p_item_type IN VARCHAR2,
1427 p_item_key IN VARCHAR2,
1428 p_actid IN NUMBER,
1429 p_funmode IN VARCHAR2,
1430 p_result OUT NOCOPY VARCHAR2) IS
1431 ------------------------------------------------------------------------
1432 l_debug_info VARCHAR2(200);
1433
1434 l_orig_receipts_status varchar2(30);
1435 l_receipts_status varchar2(30);
1436 l_report_header_id number;
1437
1438 BEGIN
1439
1440 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetReceiptsStatus');
1441
1442 IF (p_funmode = 'RUN') THEN
1443
1444 -------------------------------------------------------------------
1445 l_debug_info := 'Retrieve Activity Attr Receipts Status';
1446 -------------------------------------------------------------------
1447 l_receipts_status := WF_ENGINE.GetActivityAttrText(p_item_type,
1448 p_item_key,
1449 p_actid,
1450 'RECEIPTS_STATUS');
1451
1452 ------------------------------------------------------------
1453 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1454 ------------------------------------------------------------
1455 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1456 p_item_key,
1457 'EXPENSE_REPORT_ID');
1458
1459 ------------------------------------------------------------
1460 l_debug_info := 'Update current Receipt Status';
1461 ------------------------------------------------------------
1462 SetReceiptsStatus(l_report_header_id, l_receipts_status);
1463
1464 p_result := 'COMPLETE';
1465
1466 END IF; -- p_funmode = 'RUN'
1467
1468 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetReceiptsStatus');
1469
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetReceiptsStatus',
1473 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1474 raise;
1475 END SetReceiptsStatus;
1476
1477
1478 ------------------------------------------------------------------------
1479 PROCEDURE SetDaysOverdue(
1480 p_item_type IN VARCHAR2,
1481 p_item_key IN VARCHAR2) IS
1482 ------------------------------------------------------------------------
1483 l_debug_info VARCHAR2(200);
1484
1485 l_days_overdue number;
1486 l_report_submitted_date date;
1487 l_notif_rule_days_overdue number;
1488 l_receipts_status varchar2(30);
1489 l_report_header_id number;
1490
1491 BEGIN
1492
1493 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetDaysOverdue');
1494
1495 /*
1496 DAYS_OVERDUE is the diff between notif sent date and date the
1497 the expense report receipts package became overdue
1498 notif sent date - (report submission date + NOTIF_RULE_DAYS_OVERDUE)
1499 */
1500 l_report_submitted_date := WF_ENGINE.GetItemAttrDate(p_item_type,
1501 p_item_key,
1502 'EXPENSE_REPORT_SUBMIT_DATE');
1503
1504 l_notif_rule_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
1505 p_item_key,
1506 'NOTIF_RULE_DAYS_OVERDUE');
1507
1508 l_days_overdue := trunc(sysdate) - (trunc(l_report_submitted_date));-- + l_notif_rule_days_overdue);
1509
1510 ----------------------------------------------------------
1511 l_debug_info := 'Set DAYS_OVERDUE Item Attribute';
1512 ----------------------------------------------------------
1513 WF_ENGINE.SetItemAttrNumber(p_item_type, p_item_key, 'DAYS_OVERDUE', l_days_overdue);
1514
1515 ------------------------------------------------------------
1516 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1517 ------------------------------------------------------------
1518 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1519 p_item_key,
1520 'EXPENSE_REPORT_ID');
1521
1522 ------------------------------------------------------------
1523 l_debug_info := 'Retrieve current Receipt Status';
1524 ------------------------------------------------------------
1525 l_receipts_status := GetReceiptsStatus(l_report_header_id);
1526
1527 if (l_receipts_status = C_IN_TRANSIT) then
1528 ------------------------------------------------------------
1529 l_debug_info := 'Update current Receipt Status to Overdue if In Transit';
1530 ------------------------------------------------------------
1531 SetReceiptsStatus(l_report_header_id, C_OVERDUE);
1532 end if;
1533
1534 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetDaysOverdue');
1535
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetDaysOverdue',
1539 p_item_type, p_item_key, l_debug_info);
1540 raise;
1541 END SetDaysOverdue;
1542
1543
1544
1545 ------------------------------------------------------------------------
1546 PROCEDURE SetDaysOverdue(
1547 p_item_type IN VARCHAR2,
1548 p_item_key IN VARCHAR2,
1549 p_actid IN NUMBER,
1550 p_funmode IN VARCHAR2,
1551 p_result OUT NOCOPY VARCHAR2) IS
1552 ------------------------------------------------------------------------
1553 l_debug_info VARCHAR2(200);
1554
1555 l_days_overdue number;
1556 l_report_submitted_date date;
1557 l_notif_rule_days_overdue number;
1558
1559 BEGIN
1560
1561 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetDaysOverdue');
1562
1563 IF (p_funmode = 'RUN') THEN
1564
1565 SetDaysOverdue(p_item_type, p_item_key);
1566
1567 p_result := 'COMPLETE';
1568
1569 END IF; -- p_funmode = 'RUN'
1570
1571 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetDaysOverdue');
1572
1573 EXCEPTION
1574 WHEN OTHERS THEN
1575 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetDaysOverdue',
1576 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1577 raise;
1578 END SetDaysOverdue;
1579
1580
1581 ------------------------------------------------------------------------
1582 PROCEDURE CheckMissingDeclRequired(
1583 p_item_type IN VARCHAR2,
1584 p_item_key IN VARCHAR2,
1585 p_actid IN NUMBER,
1586 p_funmode IN VARCHAR2,
1587 p_result OUT NOCOPY VARCHAR2) IS
1588 ------------------------------------------------------------------------
1589 l_debug_info VARCHAR2(200);
1590
1591 l_missing_decl_reqd fnd_lookups.lookup_code%TYPE;
1592
1593 BEGIN
1594
1595 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckMissingDeclRequired');
1596
1597 IF (p_funmode = 'RUN') THEN
1598
1599 ----------------------------------------------------------
1600 l_debug_info := 'Check if Missing Declaration is required';
1601 ----------------------------------------------------------
1602 l_missing_decl_reqd := WF_ENGINE.GetItemAttrText(p_item_type,
1603 p_item_key,
1604 'NOTIF_RULE_MISSING_DECL_REQD');
1605
1606 if (nvl(l_missing_decl_reqd, C_NOT_REQUIRED) = C_REQUIRED) then
1607 p_result := 'COMPLETE:Y';
1608 else
1609 p_result := 'COMPLETE:N';
1610 end if;
1611
1612 END IF; -- p_funmode = 'RUN'
1613
1614 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckMissingDeclRequired');
1615
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckMissingDeclRequired',
1619 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1620 raise;
1621 END CheckMissingDeclRequired;
1622
1623
1624
1625 ------------------------------------------------------------------------
1626 PROCEDURE CheckNotifyReceived(
1627 p_item_type IN VARCHAR2,
1628 p_item_key IN VARCHAR2,
1629 p_actid IN NUMBER,
1630 p_funmode IN VARCHAR2,
1631 p_result OUT NOCOPY VARCHAR2) IS
1632 ------------------------------------------------------------------------
1633 l_debug_info VARCHAR2(200);
1634
1635 l_notif_received fnd_lookups.lookup_code%TYPE;
1636 l_days_overdue number := 0;
1637 l_report_header_id number;
1638 l_receipts_status varchar2(30);
1639
1640 BEGIN
1641
1642 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckNotifyReceived');
1643
1644 IF (p_funmode = 'RUN') THEN
1645
1646 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1647 p_item_key,
1648 'EXPENSE_REPORT_ID');
1649
1650 ------------------------------------------------------------
1651 l_debug_info := 'Retrieve current Receipt Status';
1652 ------------------------------------------------------------
1653 l_receipts_status := GetReceiptsStatus(l_report_header_id);
1654
1655 ----------------------------------------------------------
1656 l_debug_info := 'Check if Notify Receipts Received is enabled';
1657 ----------------------------------------------------------
1658 l_notif_received := WF_ENGINE.GetItemAttrText(p_item_type,
1659 p_item_key,
1660 'NOTIF_RULE_NOTIF_RECEIVED');
1661
1662 ----------------------------------------------------------
1663 l_debug_info := 'Check if Days Overdue';
1664 ----------------------------------------------------------
1665 l_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
1666 p_item_key,
1667 'DAYS_OVERDUE');
1668
1669
1670 if ((l_receipts_status = C_RECEIVED) AND ((nvl(l_notif_received, C_NEVER) = C_RECEIPTS_RECEIVED) or
1671 (nvl(l_notif_received, C_NEVER) = C_RECEIPTS_OVERDUE and nvl(l_days_overdue, -1) >= 0))) then
1672 p_result := 'COMPLETE:Y';
1673 else
1674 p_result := 'COMPLETE:N';
1675 end if;
1676
1677 END IF; -- p_funmode = 'RUN'
1678
1679 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckNotifyReceived');
1680
1681 EXCEPTION
1682 WHEN OTHERS THEN
1683 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckNotifyReceived',
1684 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1685 raise;
1686 END CheckNotifyReceived;
1687
1688
1689 ------------------------------------------------------------------------
1690 PROCEDURE IsReceivedWaived(
1691 p_item_type IN VARCHAR2,
1692 p_item_key IN VARCHAR2,
1693 p_actid IN NUMBER,
1694 p_funmode IN VARCHAR2,
1695 p_result OUT NOCOPY VARCHAR2) IS
1696 ------------------------------------------------------------------------
1697 l_debug_info VARCHAR2(200);
1698
1699 l_receipts_status varchar2(30);
1700 l_image_receipts_status varchar2(30);
1701 l_report_header_id number;
1702
1703 BEGIN
1704
1705 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsReceivedWaived');
1706
1707 IF (p_funmode = 'RUN') THEN
1708
1709 ------------------------------------------------------------
1710 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1711 ------------------------------------------------------------
1712 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1713 p_item_key,
1714 'EXPENSE_REPORT_ID');
1715
1716 ------------------------------------------------------------
1717 l_debug_info := 'Retrieve current Receipt Status';
1718 ------------------------------------------------------------
1719 l_receipts_status := GetReceiptsStatus(l_report_header_id);
1720 l_image_receipts_status := GetImageReceiptsStatus(l_report_header_id);
1721
1722 if (l_receipts_status in (C_RECEIVED, C_RECEIVED_RESUBMITTED, C_WAIVED)
1723 OR l_image_receipts_status in (C_RECEIVED, C_WAIVED)) then
1724 p_result := 'COMPLETE:Y';
1725 else
1726 p_result := 'COMPLETE:N';
1727 end if;
1728
1729 END IF; -- p_funmode = 'RUN'
1730
1731 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsReceivedWaived');
1732
1733 EXCEPTION
1734 WHEN OTHERS THEN
1735 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsReceivedWaived',
1736 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1737 raise;
1738 END IsReceivedWaived;
1739
1740
1741 ------------------------------------------------------------------------
1742 FUNCTION IsShortpay(
1743 p_item_type IN VARCHAR2,
1744 p_item_key IN VARCHAR2,
1745 p_shortpay_type IN VARCHAR2) RETURN VARCHAR2 IS
1746 ------------------------------------------------------------------------
1747 l_debug_info VARCHAR2(200);
1748
1749 l_report_header_id number;
1750 l_is_shortpay varchar2(1) := 'N';
1751
1752 BEGIN
1753
1754 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsShortpay');
1755
1756 ------------------------------------------------------------
1757 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1758 ------------------------------------------------------------
1759 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1760 p_item_key,
1761 'EXPENSE_REPORT_ID');
1762
1763 ------------------------------------------------------------
1764 l_debug_info := 'Check if Missing or Policy Shortpay';
1765 ------------------------------------------------------------
1766 select 'Y'
1767 into l_is_shortpay
1768 from ap_expense_report_headers_all aerh,
1769 wf_items wf
1770 where aerh.report_header_id = l_report_header_id
1771 and aerh.shortpay_parent_id is not null
1772 and wf.item_type = C_APEXP
1773 and wf.item_key = to_char(aerh.report_header_id) -- Bug 6841589 (sodash) to solve the invalid number exception
1774 and wf.end_date is null
1775 and wf.root_activity = p_shortpay_type
1776 and rownum = 1;
1777
1778 return l_is_shortpay;
1779
1780 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsShortpay');
1781
1782 EXCEPTION
1783 WHEN NO_DATA_FOUND THEN
1784 return 'N';
1785 WHEN OTHERS THEN
1786 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsShortpay',
1787 p_item_type, p_item_key, l_debug_info);
1788 raise;
1789 END IsShortpay;
1790
1791
1792 ------------------------------------------------------------------------
1793 PROCEDURE IsMissingShortpay(
1794 p_item_type IN VARCHAR2,
1795 p_item_key IN VARCHAR2,
1796 p_actid IN NUMBER,
1797 p_funmode IN VARCHAR2,
1798 p_result OUT NOCOPY VARCHAR2) IS
1799 ------------------------------------------------------------------------
1800 l_debug_info VARCHAR2(200);
1801
1802 l_is_shortpay VARCHAR2(1) := 'N';
1803
1804 BEGIN
1805
1806 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsMissingShortpay');
1807
1808 IF (p_funmode = 'RUN') THEN
1809
1810 ------------------------------------------------------------
1811 l_debug_info := 'Check if Missing Receipts Shortpay';
1812 ------------------------------------------------------------
1813 l_is_shortpay := IsShortpay(p_item_type,
1814 p_item_key,
1815 C_NO_RECEIPTS_SHORTPAY_PROCESS);
1816
1817 p_result := 'COMPLETE:'||l_is_shortpay;
1818
1819 END IF; -- p_funmode = 'RUN'
1820
1821 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsMissingShortpay');
1822
1823 EXCEPTION
1824 WHEN NO_DATA_FOUND THEN
1825 p_result := 'COMPLETE:N';
1826 WHEN OTHERS THEN
1827 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsMissingShortpay',
1828 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1829 raise;
1830 END IsMissingShortpay;
1831
1832
1833 ------------------------------------------------------------------------
1834 PROCEDURE IsPolicyShortpay(
1835 p_item_type IN VARCHAR2,
1836 p_item_key IN VARCHAR2,
1837 p_actid IN NUMBER,
1838 p_funmode IN VARCHAR2,
1839 p_result OUT NOCOPY VARCHAR2) IS
1840 ------------------------------------------------------------------------
1841 l_debug_info VARCHAR2(200);
1842
1843 l_is_shortpay VARCHAR2(1) := 'N';
1844
1845 BEGIN
1846
1847 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsPolicyShortpay');
1848
1849 IF (p_funmode = 'RUN') THEN
1850
1851 ------------------------------------------------------------
1852 l_debug_info := 'Check if Policy Violation Shortpay';
1853 ------------------------------------------------------------
1854 l_is_shortpay := IsShortpay(p_item_type,
1855 p_item_key,
1856 C_POLICY_VIOLATION_PROCESS);
1857
1858 p_result := 'COMPLETE:'||l_is_shortpay;
1859
1860 END IF; -- p_funmode = 'RUN'
1861
1862 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsPolicyShortpay');
1863
1864 EXCEPTION
1865 WHEN NO_DATA_FOUND THEN
1866 p_result := 'COMPLETE:N';
1867 WHEN OTHERS THEN
1868 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsPolicyShortpay',
1869 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1870 raise;
1871 END IsPolicyShortpay;
1872
1873
1874 ------------------------------------------------------------------------
1875 PROCEDURE CompleteShortpay(
1876 p_item_type IN VARCHAR2,
1877 p_item_key IN VARCHAR2,
1878 p_activity IN VARCHAR2,
1879 p_result IN VARCHAR2) IS
1880 ------------------------------------------------------------------------
1881 l_debug_info VARCHAR2(200);
1882
1883 l_report_header_id number;
1884
1885 BEGIN
1886
1887 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CompleteShortpay');
1888
1889 ------------------------------------------------------------
1890 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1891 ------------------------------------------------------------
1892 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1893 p_item_key,
1894 'EXPENSE_REPORT_ID');
1895
1896 ----------------------------------------------------------
1897 l_debug_info := 'Complete Missing or Policy Shortpay Process';
1898 ----------------------------------------------------------
1899 begin
1900 WF_ENGINE.CompleteActivityInternalName(C_APEXP,
1901 l_report_header_id,
1902 p_activity,
1903 p_result);
1904
1905 exception
1906 when others then null;
1907 end;
1908
1909 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CompleteShortpay');
1910
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CompleteShortpay',
1914 p_item_type, p_item_key, p_activity, p_result, l_debug_info);
1915 raise;
1916 END CompleteShortpay;
1917
1918
1919 ------------------------------------------------------------------------
1920 PROCEDURE CompleteMissingShortpay(
1921 p_item_type IN VARCHAR2,
1922 p_item_key IN VARCHAR2,
1923 p_actid IN NUMBER,
1924 p_funmode IN VARCHAR2,
1925 p_result OUT NOCOPY VARCHAR2) IS
1926 ------------------------------------------------------------------------
1927 l_debug_info VARCHAR2(200);
1928
1929 BEGIN
1930
1931 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CompleteMissingShortpay');
1932
1933 IF (p_funmode = 'RUN') THEN
1934
1935 ----------------------------------------------------------
1936 l_debug_info := 'Complete Missing Shortpay Process';
1937 ----------------------------------------------------------
1938 begin
1939
1940 CompleteShortpay(p_item_type,
1941 p_item_key,
1942 C_INFORM_PREPARER_SHORTPAY,
1943 C_AP_WILL_SUBMIT);
1944 CompleteShortpay(p_item_type,
1945 p_item_key,
1946 'INFORM_PREPARER_IMAGE_SHORTPAY',
1947 'WILL_SEND');
1948 CompleteShortpay(p_item_type,
1949 p_item_key,
1950 'INFORM_PREPARER_IMAGE_SHRTP_M',
1951 'WILL_SEND');
1952
1953 exception
1954 when others then null;
1955 end;
1956
1957 end if;
1958
1959 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CompleteMissingShortpay');
1960
1961 EXCEPTION
1962 WHEN OTHERS THEN
1963 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CompleteMissingShortpay',
1964 p_item_type, p_item_key, to_char(p_actid), p_result, l_debug_info);
1965 raise;
1966 END CompleteMissingShortpay;
1967
1968
1969 ------------------------------------------------------------------------
1970 PROCEDURE CompletePolicyShortpay(
1971 p_item_type IN VARCHAR2,
1972 p_item_key IN VARCHAR2,
1973 p_actid IN NUMBER,
1974 p_funmode IN VARCHAR2,
1975 p_result OUT NOCOPY VARCHAR2) IS
1976 ------------------------------------------------------------------------
1977 l_debug_info VARCHAR2(200);
1978
1979 BEGIN
1980
1981 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CompletePolicyShortpay');
1982
1983 IF (p_funmode = 'RUN') THEN
1984
1985 ----------------------------------------------------------
1986 l_debug_info := 'Complete Policy Shortpay Process';
1987 ----------------------------------------------------------
1988 begin
1989
1990 CompleteShortpay(p_item_type,
1991 p_item_key,
1992 C_POLICY_SHORTPAY_NOTICE,
1993 C_AP_PROVIDE_MISSING_INFO);
1994
1995 exception
1996 when others then null;
1997 end;
1998
1999 end if;
2000
2001 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CompletePolicyShortpay');
2002
2003 EXCEPTION
2004 WHEN OTHERS THEN
2005 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CompletePolicyShortpay',
2006 p_item_type, p_item_key, to_char(p_actid), p_result, l_debug_info);
2007 raise;
2008 END CompletePolicyShortpay;
2009
2010
2011 /*
2012 Written by:
2013 Ron Langi
2014 Purpose:
2015 This stores a Preparer-Auditor note based on the Preparer
2016 action/response from a notification activity.
2017
2018 The following is gathered from the WF:
2019 - RESULT_TYPE contains the lookup type for the result of the Notification.
2020 - RESULT_CODE contains the lookup code for the result of the Notification.
2021 - RESPONSE contains the respond attr for the Notification.
2022 - FND_MESSAGE contains the specific FND message to store
2023
2024 The Preparer-Auditor note is stored in the form of:
2025 <Preparer Action>: <Preparer Response>
2026 */
2027 ----------------------------------------------------------------------
2028 PROCEDURE StoreNote(
2029 p_item_type IN VARCHAR2,
2030 p_item_key IN VARCHAR2,
2031 p_actid IN NUMBER,
2032 p_funmode IN VARCHAR2,
2033 p_result OUT NOCOPY VARCHAR2) IS
2034 ----------------------------------------------------------------------
2035 l_report_header_id number;
2036 l_debug_info VARCHAR2(200);
2037
2038 l_fnd_message fnd_new_messages.message_name%type;
2039 l_note_text varchar2(2000);
2040 l_days_overdue number;
2041
2042 l_message_name fnd_new_messages.message_name%type;
2043 l_result_type Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
2044 l_result_code Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
2045 l_response Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
2046 l_type_display_name varchar2(240);
2047 l_code_display_name varchar2(240);
2048 l_note_prefix varchar2(2000);
2049
2050 l_orig_language_code ap_expense_params.note_language_code%type := null;
2051 l_orig_language fnd_languages.nls_language%type := null;
2052 l_new_language_code ap_expense_params.note_language_code%type := null;
2053 l_new_language fnd_languages.nls_language%type := null;
2054
2055 l_created_by number;
2056 --l_preparer_id number;
2057
2058 l_org_id ap_expense_params_all.org_id%type;
2059 l_received_date ap_expense_report_headers_all.receipts_received_date%type;
2060 BEGIN
2061
2062 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start StoreNote');
2063
2064 IF (p_funmode = 'RUN') THEN
2065
2066 -------------------------------------------------------------------
2067 l_debug_info := 'Need to generate Note based on language setup';
2068 -------------------------------------------------------------------
2069
2070 -------------------------------------------------------------------
2071 l_debug_info := 'Save original language';
2072 -------------------------------------------------------------------
2073 l_orig_language_code := userenv('LANG');
2074 select nls_language
2075 into l_orig_language
2076 from fnd_languages
2077 where language_code = l_orig_language_code;
2078
2079 -------------------------------------------------------------------
2080 l_debug_info := 'Check AP_EXPENSE_PARAMS.NOTE_LANGUAGE_CODE';
2081 -------------------------------------------------------------------
2082 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2083 p_item_key,
2084 'ORG_ID');
2085 begin
2086 select note_language_code
2087 into l_new_language_code
2088 from ap_expense_params_all
2089 where org_id = l_org_id;
2090
2091 exception
2092 when no_data_found then
2093 null;
2094 end;
2095
2096 -------------------------------------------------------------------
2097 l_debug_info := 'Else use instance base language';
2098 -------------------------------------------------------------------
2099 if (l_new_language_code is null) then
2100 select language_code
2101 into l_new_language_code
2102 from fnd_languages
2103 where installed_flag in ('B');
2104 end if;
2105
2106 -------------------------------------------------------------------
2107 l_debug_info := 'Set nls context to new language';
2108 -------------------------------------------------------------------
2109 select nls_language
2110 into l_new_language
2111 from fnd_languages
2112 where language_code = l_new_language_code;
2113
2114 fnd_global.set_nls_context(p_nls_language => l_new_language);
2115
2116 ------------------------------------------------------------
2117 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
2118 ------------------------------------------------------------
2119 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2120 p_item_key,
2121 'EXPENSE_REPORT_ID');
2122
2123 -------------------------------------------------------------------
2124 l_debug_info := 'Retrieve Activity Attr Result Type';
2125 -------------------------------------------------------------------
2126 l_result_type := WF_ENGINE.GetActivityAttrText(p_item_type,
2127 p_item_key,
2128 p_actid,
2129 'RESULT_TYPE');
2130 -- bug 6361555
2131 begin
2132 select created_by
2133 into l_created_by
2134 from ap_expense_report_headers_all
2135 where report_header_id = l_report_header_id;
2136 exception
2137 when others then
2138 null;
2139 end;
2140
2141 ----------------------------------------------------------
2142 l_debug_info := 'Get Preparer Id using Created By';
2143 ----------------------------------------------------------
2144 /*if (AP_WEB_DB_HR_INT_PKG.GetEmpIdForUser(l_created_by, l_preparer_id)) then
2145 null;
2146 end if;*/
2147
2148 if (l_result_type is not null) then
2149
2150 -------------------------------------------------------------------
2151 l_debug_info := 'Retrieve Note prefix';
2152 -------------------------------------------------------------------
2153 l_message_name := 'OIE_NOTES_PREPARER_RESPONSE';
2154
2155 begin
2156 -------------------------------------------------------------------
2157 -- fnd_global.set_nls_context() seems to work for WF but not FND_MESSAGES
2158 -------------------------------------------------------------------
2159 select message_text
2160 into l_note_prefix
2161 from fnd_new_messages
2162 where application_id = 200
2163 and message_name = l_message_name
2164 and language_code = l_new_language_code;
2165
2166 exception
2167 when no_data_found then
2168 FND_MESSAGE.SET_NAME('SQLAP', l_message_name);
2169 l_note_prefix := FND_MESSAGE.GET;
2170 end;
2171
2172 BEGIN
2173 IF (l_result_type = 'APWRECPT_OVERDUE_RESPONSE') THEN
2174 l_message_name := 'OIE_NOTES_OVERDUE_RESPONSE';
2175 l_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
2176 p_item_key,
2177 'DAYS_OVERDUE');
2178
2179 FND_MESSAGE.SET_NAME('SQLAP', l_message_name);
2180 FND_MESSAGE.SET_TOKEN('DAYS_OVERDUE', to_char(l_days_overdue));
2181 l_note_prefix := FND_MESSAGE.GET;
2182 END IF;
2183
2184 IF (l_result_type = 'APWRECPT_IMG_OVERDUE_RESPONSE') THEN
2185 l_message_name := 'OIE_NOTES_IMG_OVERDUE_RESPONSE';
2186 l_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
2187 p_item_key,
2188 'IMAGE_DAYS_OVERDUE');
2189 FND_MESSAGE.SET_NAME('SQLAP', l_message_name);
2190 FND_MESSAGE.SET_TOKEN('DAYS_OVERDUE', to_char(l_days_overdue));
2191 l_note_prefix := FND_MESSAGE.GET;
2192 END IF;
2193 EXCEPTION
2194 WHEN OTHERS THEN
2195 NULL;
2196 END;
2197
2198
2199 -------------------------------------------------------------------
2200 l_debug_info := 'Retrieve Activity Attr Result Code';
2201 -------------------------------------------------------------------
2202 l_result_code := WF_ENGINE.GetActivityAttrText(p_item_type,
2203 p_item_key,
2204 p_actid,
2205 'RESULT_CODE');
2206
2207 -------------------------------------------------------------------
2208 l_debug_info := 'Retrieve Activity Attr Response';
2209 -------------------------------------------------------------------
2210 l_response := WF_ENGINE.GetActivityAttrText(p_item_type,
2211 p_item_key,
2212 p_actid,
2213 'RESPONSE');
2214
2215 ------------------------------------------------------------
2216 l_debug_info := 'Retrieve lookup display name';
2217 ------------------------------------------------------------
2218 WF_LOOKUP_TYPES_PUB.fetch_lookup_display(l_result_type,
2219 l_result_code,
2220 l_type_display_name,
2221 l_code_display_name);
2222
2223 ------------------------------------------------------------
2224 l_debug_info := 'store the result and response as a note';
2225 ------------------------------------------------------------
2226 AP_WEB_NOTES_PKG.CreateERPrepToAudNote (
2227 p_report_header_id => l_report_header_id,
2228 p_note => l_note_prefix||' '||l_code_display_name||'
2229 '||l_response,
2230 p_lang => l_new_language_code,
2231 p_entered_by => nvl(l_created_by,fnd_global.user_id) -- bug 6361555
2232 );
2233
2234 ----------------------------------------------------------
2235 l_debug_info := 'clear Item Attribute PREPARER_RESPONSE';
2236 -- this assumes preparer response, if we need to change this
2237 -- later then change Activity Attr RESPONSE to pass item attr
2238 ---------------------------------------------------------
2239 WF_ENGINE.SetItemAttrText(p_item_type,
2240 p_item_key,
2241 'PREPARER_RESPONSE',
2242 '');
2243
2244 else
2245
2246 -------------------------------------------------------------------
2247 l_debug_info := 'Retrieve Activity Attr FND Message';
2248 -------------------------------------------------------------------
2249 l_fnd_message := WF_ENGINE.GetActivityAttrText(p_item_type,
2250 p_item_key,
2251 p_actid,
2252 'FND_MESSAGE');
2253 if (l_fnd_message in ('APWRECPT_OVERDUE_SENT','APWRECPT_IMG_OVERDUE_SENT',
2254 'APWRECPT_MISSING_SENT','APWRECPT_IMG_MISSING_SENT',
2255 'APWRECPT_RECEIVED_SENT','APWRECPT_IMG_RECEIVED_SENT')) then
2256
2257 if (l_fnd_message in ('APWRECPT_OVERDUE_SENT','APWRECPT_MISSING_SENT')) then
2258
2259 l_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
2260 p_item_key,
2261 'DAYS_OVERDUE');
2262 FND_MESSAGE.SET_NAME('SQLAP', l_fnd_message);
2263 FND_MESSAGE.SET_TOKEN('DAYS_OVERDUE', to_char(l_days_overdue));
2264
2265 elsif (l_fnd_message in ('APWRECPT_IMG_OVERDUE_SENT','APWRECPT_IMG_MISSING_SENT')) then
2266
2267 l_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
2268 p_item_key,
2269 'IMAGE_DAYS_OVERDUE');
2270 FND_MESSAGE.SET_NAME('SQLAP', l_fnd_message);
2271 FND_MESSAGE.SET_TOKEN('DAYS_OVERDUE', to_char(l_days_overdue));
2272 elsif (l_fnd_message = 'APWRECPT_RECEIVED_SENT') THEN
2273 BEGIN
2274 FND_MESSAGE.SET_NAME('SQLAP', l_fnd_message);
2275 SELECT trunc(receipts_received_date) INTO l_received_date
2276 FROM ap_expense_report_headers_all WHERE report_header_id = l_report_header_id;
2277 FND_MESSAGE.SET_TOKEN('RECEIVED_DATE', to_char(l_received_date));
2278 EXCEPTION
2279 WHEN OTHERS THEN
2280 NULL;
2281 END;
2282 elsif (l_fnd_message = 'APWRECPT_IMG_RECEIVED_SENT') THEN
2283 BEGIN
2284 FND_MESSAGE.SET_NAME('SQLAP', l_fnd_message);
2285 SELECT trunc(image_receipts_received_date) INTO l_received_date
2286 FROM ap_expense_report_headers_all WHERE report_header_id = l_report_header_id;
2287 FND_MESSAGE.SET_TOKEN('RECEIVED_DATE', to_char(l_received_date));
2288 EXCEPTION
2289 WHEN OTHERS THEN
2290 NULL;
2291 END;
2292 end if;
2293
2294 l_note_text := FND_MESSAGE.GET;
2295
2296 ------------------------------------------------------------
2297 l_debug_info := 'store the fnd message as a note';
2298 ------------------------------------------------------------
2299 AP_WEB_NOTES_PKG.CreateERPrepToAudNote (
2300 p_report_header_id => l_report_header_id,
2301 p_note => l_note_text,
2302 p_lang => l_new_language_code
2303 );
2304
2305 end if; -- l_fnd_message is not null
2306
2307 end if; -- l_result_type is not null
2308
2309 -------------------------------------------------------------------
2310 l_debug_info := 'Restore nls context to original language';
2311 -------------------------------------------------------------------
2312 fnd_global.set_nls_context(p_nls_language => l_orig_language);
2313
2314 p_result := 'COMPLETE:Y';
2315
2316 END IF;
2317
2318 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end StoreNote');
2319
2320 EXCEPTION
2321 WHEN OTHERS THEN
2322 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'StoreNote',
2323 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
2324 raise;
2325 END StoreNote;
2326
2327
2328 ----------------------------------------------------------------------
2329 PROCEDURE CallbackFunction( p_item_type IN VARCHAR2,
2330 p_item_key IN VARCHAR2,
2331 p_actid IN NUMBER,
2332 p_funmode IN VARCHAR2,
2333 p_result OUT NOCOPY VARCHAR2) IS
2334 ----------------------------------------------------------------------
2335 l_debug_info VARCHAR2(200);
2336
2337 l_org_id number;
2338 l_expense_report_id number;
2339
2340 BEGIN
2341
2342 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CallbackFunction');
2343
2344 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2345 p_item_key,
2346 'ORG_ID');
2347
2348 if (l_org_id is null) then
2349 -- EXPENSE_REPORT_ID item attribute should exist
2350 l_expense_report_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2351 p_item_key,
2352 'EXPENSE_REPORT_ID');
2353
2354 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
2355 l_org_id := NULL;
2356 END IF;
2357
2358 WF_ENGINE.SetItemAttrNumber(p_item_type,
2359 p_item_key,
2360 'ORG_ID',
2361 l_org_id);
2362 end if;
2363
2364 /*
2365 if ( p_funmode = 'RUN' ) then
2366 --<your RUN executable statements>
2367
2368 p_result := 'TRUE';
2369
2370 return;
2371 end if;
2372 */
2373
2374 if ( p_funmode = 'SET_CTX' ) then
2375 --<your executable statements for establishing context information>
2376
2377 if (l_org_id is not null) then
2378 mo_global.set_policy_context(p_access_mode => 'S',
2379 p_org_id => l_org_id);
2380 end if;
2381
2382 p_result := 'TRUE';
2383
2384 return;
2385 end if;
2386
2387 if ( p_funmode = 'TEST_CTX' and l_org_id is not null) then
2388 --<your executable statements for testing the validity of the current context information>
2389
2390 IF ((nvl(mo_global.get_access_mode, 'NULL') <> 'S') OR
2391 (nvl(mo_global.get_current_org_id, -99) <> nvl(l_org_id, -99)) ) THEN
2392 p_result := 'FALSE';
2393 ELSE
2394 p_result := 'TRUE';
2395 END IF;
2396
2397 return;
2398 end if;
2399
2400 /*
2401 if ( p_funmode = '<other command>' ) then
2402 p_result := ' ';
2403
2404 return;
2405 end if;
2406 */
2407
2408 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CallbackFunction');
2409
2410 EXCEPTION
2411 WHEN OTHERS THEN
2412 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CallbackFunction',
2413 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
2414 raise;
2415 END CallbackFunction;
2416
2417
2418 ------------------------------------------------------------------------
2419 PROCEDURE RaiseOverdueEvent(
2420 p_expense_report_id IN NUMBER,
2421 p_overdue_event IN VARCHAR2) IS
2422 ------------------------------------------------------------------------
2423 l_debug_info VARCHAR2(200);
2424
2425 l_event_key wf_items.item_key%type;
2426
2427 BEGIN
2428
2429 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseOverdueEvent');
2430
2431 ----------------------------------------------------------
2432 l_debug_info := 'Generate Event Key';
2433 ----------------------------------------------------------
2434 l_event_key := GenerateEventKey(p_expense_report_id, p_overdue_event);
2435
2436 ----------------------------------------------------------
2437 l_debug_info := 'Check Event Key';
2438 ----------------------------------------------------------
2439 if (NOT EventKeyExists(l_event_key)) then
2440
2441 ----------------------------------------------------------
2442 l_debug_info := 'Raise Overdue Event';
2443 ----------------------------------------------------------
2444 wf_event.raise(p_event_name => C_OVERDUE_EVENT_NAME,
2445 p_event_key => l_event_key);
2446 --p_parameters => l_parameter_list);
2447
2448 end if;
2449
2450 -- Supervisor Notifications
2451 UPDATE AP_EXPENSE_REPORT_HEADERS_ALL
2452 SET OVERDUE_REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
2453 WHERE REPORT_HEADER_ID = p_expense_report_id;
2454
2455 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseOverdueEvent');
2456
2457 EXCEPTION
2458 WHEN OTHERS THEN
2459 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseOverdueEvent',
2460 p_expense_report_id, l_debug_info);
2461 raise;
2462 END RaiseOverdueEvent;
2463
2464 ------------------------------------------------------------------------
2465 PROCEDURE RaiseMissingEvent(
2466 p_expense_report_id IN NUMBER,
2467 p_missing_event IN VARCHAR2) IS
2468 ------------------------------------------------------------------------
2469 l_debug_info VARCHAR2(200);
2470
2471 l_event_key wf_items.item_key%type;
2472
2473 BEGIN
2474
2475 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseMissingEvent');
2476
2477 ----------------------------------------------------------
2478 l_debug_info := 'Generate Event Key';
2479 ----------------------------------------------------------
2480 l_event_key := GenerateEventKey(p_expense_report_id, p_missing_event);
2481
2482 ----------------------------------------------------------
2483 l_debug_info := 'Check Event Key';
2484 ----------------------------------------------------------
2485 if (NOT EventKeyExists(l_event_key)) then
2486
2487 ----------------------------------------------------------
2488 l_debug_info := 'Raise Missing Event';
2489 ----------------------------------------------------------
2490 wf_event.raise(p_event_name => C_MISSING_EVENT_NAME,
2491 p_event_key => l_event_key);
2492 --p_parameters => l_parameter_list);
2493
2494 end if;
2495
2496 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseMissingEvent');
2497
2498 EXCEPTION
2499 WHEN OTHERS THEN
2500 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseMissingEvent',
2501 p_expense_report_id, l_debug_info);
2502 raise;
2503 END RaiseMissingEvent;
2504
2505 FUNCTION CheckActiveWFExists(p_report_header_id IN NUMBER, p_item_key IN VARCHAR2) RETURN VARCHAR2 IS
2506 l_ret_value VARCHAR2(1);
2507 BEGIN
2508
2509 SELECT 'Y' INTO l_ret_value FROM wf_items
2510 WHERE item_key like to_char(p_report_header_id)||p_item_key
2511 AND end_date is null;
2512
2513 RETURN l_ret_value;
2514
2515 EXCEPTION
2516 WHEN OTHERS THEN
2517 RETURN 'N';
2518 END CheckActiveWFExists;
2519
2520 /*
2521 Written by:
2522 Ron Langi
2523
2524 Purpose:
2525 Tracks Overdue Receipt Packages
2526
2527 Input:
2528 p_org_id - Org Id (optional)
2529
2530 Output:
2531 errbuf - contains error message; required by Concurrent Manager
2532 retcode - contains return code; required by Concurrent Manager
2533
2534 Input/Output:
2535
2536 Assumption:
2537
2538 */
2539 ----------------------------------------------------------------------
2540 PROCEDURE TrackOverdue(
2541 errbuf out nocopy varchar2,
2542 retcode out nocopy number,
2543 p_org_id in number,
2544 p_mgr_notify in varchar2 DEFAULT 'N',
2545 p_dummy_notify in number,
2546 p_surrogate_mgr_id in number DEFAULT NULL) IS
2547 ----------------------------------------------------------------------
2548
2549 l_debug_info VARCHAR2(200);
2550
2551 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
2552 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
2553 l_orig_receipts_status ap_expense_report_headers.receipts_status%TYPE;
2554 l_report_submit_date ap_expense_report_headers.report_submitted_date%TYPE;
2555 l_rule_overdue_days AP_AUD_RULE_SETS.NOTIFY_RCT_OVERDUE_DAYS%TYPE;
2556 l_image_rule_overdue_days AP_AUD_RULE_SETS.NOTIFY_IMG_RCT_OVERDUE_DAYS%TYPE;
2557
2558 l_employee_id ap_expense_report_headers.employee_id%TYPE;
2559 l_business_group_id hr_organization_units.business_group_id%TYPE;
2560 l_duration AP_AUD_RULE_SETS.audit_term_duration_days%TYPE;
2561 l_emp_rec AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type;
2562 l_audit_rec AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type;
2563 l_auto_audit_id NUMBER;
2564 l_x_return_status VARCHAR2(1);
2565 l_x_msg_count NUMBER;
2566 l_x_msg_data VARCHAR2(2000);
2567 l_event_raised VARCHAR2(1) := 'N';
2568 l_receipt_type VARCHAR2(10);
2569 l_image_receipt_status ap_expense_report_headers.receipts_status%TYPE;
2570 l_original_overdue BOOLEAN := FALSE;
2571 l_image_overdue BOOLEAN := FALSE;
2572 l_original_missing BOOLEAN := FALSE;
2573 l_image_missing BOOLEAN := FALSE;
2574 l_item_key VARCHAR2(100) := to_char(FND_GLOBAL.CONC_REQUEST_ID);
2575 l_item_type VARCHAR2(10) := C_APWRECPT;
2576 l_supervisor_id ap_expense_report_headers.employee_id%TYPE;
2577 l_temp_emp_id ap_expense_report_headers.employee_id%TYPE;
2578 l_supName wf_users.name%type;
2579 l_supDisplayName wf_users.display_name%type;
2580
2581
2582 /*
2583 Criteria for this cursor is:
2584 - receipts status is REQUIRED or MISSING
2585 - original reports (excludes bothpay child reports)
2586 - reports that have effective notification rules
2587 - no Overdue/Missing Receipts WF exists
2588 - REQUIRED receipts are overdue
2589 or
2590 MISSING receipts are overdue and phys doc is reqd
2591 */
2592 -- cursor for overdue required/missing receipts
2593 CURSOR c_overdue_receipts IS
2594 select aerh.report_header_id,
2595 aerh.receipts_status,
2596 aerh.image_receipts_status,
2597 aerh.report_submitted_date,
2598 rs.NOTIFY_RCT_OVERDUE_DAYS,
2599 rs.NOTIFY_IMG_RCT_OVERDUE_DAYS
2600 from AP_EXPENSE_REPORT_HEADERS aerh,
2601 AP_AUD_RULE_SETS rs,
2602 AP_AUD_RULE_ASSIGNMENTS rsa
2603 where (aerh.receipts_status in (C_REQUIRED, C_MISSING)
2604 OR decode(aerh.image_receipts_status, 'PENDING_IMAGE_SUBMISSION', C_REQUIRED, aerh.image_receipts_status) in (C_REQUIRED, C_MISSING))
2605 and aerh.bothpay_parent_id is null
2606 and aerh.shortpay_parent_id is null
2607 and rsa.org_id = nvl(p_org_id, rsa.org_id)
2608 and rsa.org_id = aerh.org_id
2609 and rsa.rule_set_id = rs.rule_set_id
2610 and rs.rule_set_type = C_NOTIFY_RULE
2611 and TRUNC(aerh.report_submitted_date)
2612 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
2613 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
2614 and
2615 (
2616 ((aerh.RECEIPTS_STATUS = C_REQUIRED and sysdate - (aerh.report_submitted_date + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
2617 and not exists
2618 (select 1 from wf_items where aerh.RECEIPTS_STATUS = C_REQUIRED and item_type = 'APWRECPT'
2619 and (item_key like to_char(aerh.report_header_id)||':receipts.overdue.original%'
2620 OR item_key like to_char(aerh.report_header_id)||':receipts.overdue.both%')
2621 and end_date is null and rownum=1)
2622 )
2623 or
2624 ((aerh.RECEIPTS_STATUS = C_MISSING and nvl(aerh.IMAGE_RECEIPTS_STATUS, C_NOT_REQUIRED) in (C_NOT_REQUIRED, C_MISSING, C_WAIVED, C_RECEIVED)
2625 and sysdate - (aerh.report_submitted_date + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
2626 and not exists
2627 (select 1 from wf_items where aerh.RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT'
2628 and (item_key like to_char(aerh.report_header_id)||':receipts.missing.original%'
2629 OR item_key like to_char(aerh.report_header_id)||':receipts.missing.both%')
2630 and end_date is null and rownum=1)
2631 )
2632 or
2633 ((decode(aerh.IMAGE_RECEIPTS_STATUS, 'PENDING_IMAGE_SUBMISSION', C_REQUIRED, aerh.IMAGE_RECEIPTS_STATUS) = C_REQUIRED
2634 and sysdate - (aerh.report_submitted_date + rs.NOTIFY_IMG_RCT_OVERDUE_DAYS) > 0)
2635 and (aerh.RECEIPTS_STATUS <> AP_WEB_RECEIPTS_WF.C_RECEIVED and (nvl(aerh.workflow_approved_flag, 'N') <> 'P' OR aerh.expense_status_code <> 'INVOICED'))
2636 and not exists
2637 (select 1
2638 from wf_items
2639 where decode(aerh.IMAGE_RECEIPTS_STATUS, 'PENDING_IMAGE_SUBMISSION', C_REQUIRED, aerh.IMAGE_RECEIPTS_STATUS) = C_REQUIRED
2640 and item_type = 'APWRECPT'
2641 and (item_key like to_char(aerh.report_header_id)||':receipts.overdue.image%'
2642 OR item_key like to_char(aerh.report_header_id)||':receipts.overdue.both%')
2643 and end_date is null
2644 and rownum=1)
2645 )
2646 or
2647 ((aerh.IMAGE_RECEIPTS_STATUS = C_MISSING and nvl(aerh.RECEIPTS_STATUS, C_NOT_REQUIRED) in (C_NOT_REQUIRED, C_MISSING, C_WAIVED, C_RECEIVED)
2648 and sysdate - (aerh.report_submitted_date + rs.NOTIFY_IMG_RCT_OVERDUE_DAYS) > 0)
2649 and (aerh.RECEIPTS_STATUS <> AP_WEB_RECEIPTS_WF.C_RECEIVED and (nvl(aerh.workflow_approved_flag, 'N') <> 'P' OR aerh.expense_status_code <> 'INVOICED'))
2650 and not exists
2651 (select 1
2652 from wf_items
2653 where aerh.IMAGE_RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT'
2654 and (item_key like to_char(aerh.report_header_id)||':receipts.missing.image%'
2655 OR item_key like to_char(aerh.report_header_id)||':receipts.missing.both%')
2656 and end_date is null
2657 and rownum=1)
2658 )
2659 );
2660 /*
2661 select aerh.report_header_id,
2662 aerh.receipts_status,
2663 'ORIGINAL'
2664 from AP_EXPENSE_REPORT_HEADERS aerh,
2665 AP_AUD_RULE_SETS rs,
2666 AP_AUD_RULE_ASSIGNMENTS rsa
2667 where aerh.receipts_status in (C_REQUIRED, C_MISSING)
2668 and aerh.bothpay_parent_id is null
2669 and rsa.org_id = nvl(p_org_id, rsa.org_id)
2670 and rsa.org_id = aerh.org_id
2671 and rsa.rule_set_id = rs.rule_set_id
2672 and rs.rule_set_type = C_NOTIFY_RULE
2673 and TRUNC(aerh.report_submitted_date)
2674 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
2675 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
2676 and
2677 (
2678 (aerh.RECEIPTS_STATUS = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
2679 or
2680 (aerh.RECEIPTS_STATUS = C_MISSING and rs.NOTIFY_DOCUMENT_REQUIRED_CODE = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
2681 )
2682 and not exists
2683 (select 1 from wf_items where aerh.RECEIPTS_STATUS = C_REQUIRED and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.overdue%' and end_date is null and rownum=1)
2684 and not exists
2685 (select 1 from wf_items where aerh.RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.missing%' and end_date is null and rownum=1)
2686
2687 UNION
2688
2689 select aerh.report_header_id,
2690 aerh.image_receipts_status,
2691 'IMAGE'
2692 from AP_EXPENSE_REPORT_HEADERS aerh,
2693 AP_AUD_RULE_SETS rs,
2694 AP_AUD_RULE_ASSIGNMENTS rsa
2695 where aerh.image_receipts_status in (C_REQUIRED, C_MISSING)
2696 and aerh.bothpay_parent_id is null
2697 and rsa.org_id = nvl(p_org_id, rsa.org_id)
2698 and rsa.org_id = aerh.org_id
2699 and rsa.rule_set_id = rs.rule_set_id
2700 and rs.rule_set_type = C_NOTIFY_RULE
2701 and TRUNC(aerh.report_submitted_date)
2702 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
2703 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
2704 and
2705 (
2706 (aerh.IMAGE_RECEIPTS_STATUS = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_IMG_RCT_OVERDUE_DAYS) > 0)
2707 or
2708 (aerh.IMAGE_RECEIPTS_STATUS = C_MISSING and rs.NOTIFY_DOCUMENT_REQUIRED_CODE = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_IMG_RCT_OVERDUE_DAYS) > 0)
2709 )
2710 and not exists
2711 (select 1 from wf_items where aerh.IMAGE_RECEIPTS_STATUS = C_REQUIRED and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.overdue%' and end_date is null and rownum=1)
2712 and not exists
2713 (select 1 from wf_items where aerh.IMAGE_RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.missing%' and end_date is null and rownum=1);
2714
2715 */
2716 /*
2717 Criteria for this cursor is:
2718 - list employee's with late receipts who are not on the audit list yet
2719 - late receipts are required and overdue
2720 */
2721 -- cursor for employees to be audited
2722 CURSOR c_audit_list_receipts IS
2723 select aerh.employee_id,
2724 hr.business_group_id,
2725 max(rs.audit_term_duration_days)
2726 from AP_EXPENSE_REPORT_HEADERS aerh,
2727 hr_organization_units hr,
2728 AP_AUD_RULE_SETS rs,
2729 AP_AUD_RULE_ASSIGNMENTS rsa
2730 where aerh.org_id = nvl(p_org_id, aerh.org_id)
2731 and aerh.bothpay_parent_id is null
2732 and aerh.report_submitted_date is not null
2733 and hr.organization_id = aerh.org_id
2734 and rsa.org_id = aerh.org_id
2735 and rsa.rule_set_id = rs.rule_set_id
2736 and rs.rule_set_type = 'AUDIT_LIST'
2737 and rs.receipt_delay_rule_flag = 'Y'
2738 and TRUNC(aerh.report_submitted_date)
2739 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
2740 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
2741 and aerh.receipts_status in ('REQUIRED', 'MISSING', 'OVERDUE', 'IN_TRANSIT', 'RESOLUTN')
2742 and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.receipt_delay_days) > 0
2743 group by employee_id, business_group_id;
2744
2745
2746
2747 CURSOR c_supervisor_list IS
2748 SELECT DISTINCT supervisor_id FROM
2749 (SELECT emp.supervisor_id
2750 FROM per_employees_x emp
2751 WHERE emp.employee_id in (select distinct employee_id
2752 from ap_expense_report_headers_all
2753 where overdue_request_id = FND_GLOBAL.CONC_REQUEST_ID)
2754 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
2755 UNION ALL
2756 SELECT emp.supervisor_id
2757 FROM per_cont_workers_current_x emp
2758 WHERE emp.person_id in (select distinct employee_id
2759 from ap_expense_report_headers_all
2760 where overdue_request_id = FND_GLOBAL.CONC_REQUEST_ID)
2761 );
2762
2763
2764
2765 BEGIN
2766
2767 fnd_file.put_line(fnd_file.log, 'p_org_id = :'|| p_org_id || ':');
2768 fnd_file.put_line(fnd_file.log, 'p_mgr_notify = :'|| p_mgr_notify ||':');
2769
2770 ------------------------------------------------------------
2771 l_debug_info := 'Process Overdue/Missing Receipts';
2772 ------------------------------------------------------------
2773 fnd_file.put_line(fnd_file.log, l_debug_info);
2774 open c_overdue_receipts;
2775 loop
2776
2777 fetch c_overdue_receipts into l_report_header_id, l_receipts_status,l_image_receipt_status,l_report_submit_date,l_rule_overdue_days,l_image_rule_overdue_days;--, l_receipt_type;
2778 exit when c_overdue_receipts%NOTFOUND;
2779
2780 -- Reset all the boolean variables
2781 l_original_overdue := FALSE;
2782 l_image_overdue := FALSE;
2783 l_original_missing := FALSE;
2784 l_image_missing := FALSE;
2785
2786 if (l_receipts_status = C_REQUIRED OR l_image_receipt_status = C_REQUIRED) then
2787
2788 ------------------------------------------------------------
2789 l_debug_info := 'Update current Receipt Status';
2790 ------------------------------------------------------------
2791
2792 IF (l_receipts_status = 'REQUIRED' AND (sysdate - (l_report_submit_date + l_rule_overdue_days)) > 0) THEN
2793 l_original_overdue := TRUE;
2794
2795 END IF;
2796
2797 IF (l_image_receipt_status = 'REQUIRED' AND (sysdate - (l_report_submit_date + l_image_rule_overdue_days)) > 0) THEN
2798 l_image_overdue := TRUE;
2799
2800 END IF;
2801 IF(l_original_overdue = TRUE AND l_image_overdue = TRUE) THEN
2802 SetReceiptsStatus(l_report_header_id, C_OVERDUE);
2803 SetImageReceiptsStatus(l_report_header_id, C_OVERDUE);
2804 l_event_raised := 'Y';
2805 --RaiseOverdueEvent(l_report_header_id, C_OVERDUE_BOTH_EVENT_KEY);
2806 RaiseOverdueEvent(l_report_header_id, C_OVERDUE_ORIG_EVENT_KEY);
2807 RaiseOverdueEvent(l_report_header_id, C_OVERDUE_IMG_EVENT_KEY);
2808 ELSIF (l_original_overdue = TRUE) THEN
2809 SetReceiptsStatus(l_report_header_id, C_OVERDUE);
2810 l_event_raised := 'Y';
2811 RaiseOverdueEvent(l_report_header_id, C_OVERDUE_ORIG_EVENT_KEY);
2812 ELSIF (l_image_overdue = TRUE) THEN
2813 SetImageReceiptsStatus(l_report_header_id, C_OVERDUE);
2814 l_event_raised := 'Y';
2815 RaiseOverdueEvent(l_report_header_id, C_OVERDUE_IMG_EVENT_KEY);
2816 END IF;
2817 ------------------------------------------------------------
2818 l_debug_info := 'Raise Overdue Event: '||l_report_header_id;
2819 ------------------------------------------------------------
2820 fnd_file.put_line(fnd_file.log, l_debug_info);
2821
2822
2823 end if;
2824
2825 if (l_receipts_status = C_MISSING OR l_image_receipt_status = C_MISSING) then
2826
2827 ------------------------------------------------------------
2828 l_debug_info := 'Raise Missing Event: '||l_report_header_id;
2829 ------------------------------------------------------------
2830 IF (l_receipts_status = C_MISSING AND (sysdate - (l_report_submit_date + l_rule_overdue_days)) > 0) THEN
2831 l_original_missing := TRUE;
2832
2833 END IF;
2834
2835 IF (l_image_receipt_status = C_MISSING AND (sysdate - (l_report_submit_date + l_image_rule_overdue_days)) > 0) THEN
2836 l_image_missing := TRUE;
2837
2838 END IF;
2839 IF(l_original_missing = TRUE AND l_image_missing = TRUE) THEN
2840 l_event_raised := 'Y';
2841 --RaiseMissingEvent(l_report_header_id, C_MISSING_BOTH_EVENT_KEY);
2842 /*IF (CheckActiveWFExists(l_report_header_id, ':receipts.missing.original%') = 'N') THEN
2843 RaiseMissingEvent(l_report_header_id, C_MISSING_ORIG_EVENT_KEY);
2844 END IF;*/
2845 IF (CheckActiveWFExists(l_report_header_id, ':receipts.missing.image%') = 'N' AND
2846 CheckActiveWFExists(l_report_header_id, ':receipts.missing.original%') = 'N') THEN
2847 RaiseMissingEvent(l_report_header_id, C_MISSING_IMG_EVENT_KEY);
2848 END IF;
2849 ELSIF (l_original_missing = TRUE) THEN
2850 IF (CheckActiveWFExists(l_report_header_id, ':receipts.missing.original%') = 'N') THEN
2851 l_event_raised := 'Y';
2852 RaiseMissingEvent(l_report_header_id, C_MISSING_ORIG_EVENT_KEY);
2853 END IF;
2854 ELSIF (l_image_missing = TRUE) THEN
2855 IF (CheckActiveWFExists(l_report_header_id, ':receipts.missing.image%') = 'N') THEN
2856 l_event_raised := 'Y';
2857 RaiseMissingEvent(l_report_header_id, C_MISSING_IMG_EVENT_KEY);
2858 END IF;
2859 END IF;
2860 fnd_file.put_line(fnd_file.log, l_debug_info);
2861
2862 end if;
2863
2864 end loop;
2865 close c_overdue_receipts;
2866
2867 ------------------------------------------------------------
2868 l_debug_info := 'Commit Events for Overdue/Missing Receipts';
2869 ------------------------------------------------------------
2870 fnd_file.put_line(fnd_file.log, l_debug_info);
2871 COMMIT;
2872
2873 ------------------------------------------------------------
2874 l_debug_info := 'Audit Overdue/Missing Receipts';
2875 ------------------------------------------------------------
2876 fnd_file.put_line(fnd_file.log, l_debug_info);
2877
2878 open c_audit_list_receipts;
2879 loop
2880
2881 fetch c_audit_list_receipts into l_employee_id, l_business_group_id, l_duration;
2882 exit when c_audit_list_receipts%NOTFOUND;
2883
2884 ------------------------------------------------------------
2885 l_debug_info := 'Adding to Audit List employee: '||l_employee_id||' business group id: '||l_business_group_id||' duration: '||l_duration;
2886 ------------------------------------------------------------
2887 fnd_file.put_line(fnd_file.log, l_debug_info);
2888
2889 l_emp_rec.business_group_id := l_business_group_id;
2890 l_emp_rec.person_id := l_employee_id;
2891 l_audit_rec.audit_reason_code := 'RECEIPTS_LATE';
2892 l_audit_rec.start_date := sysdate;
2893 l_audit_rec.end_date := sysdate + l_duration;
2894
2895 AP_WEB_AUDIT_LIST_PUB.Audit_Employee(1.0,
2896 FND_API.G_FALSE, --p_init_msg_list
2897 FND_API.G_FALSE, --p_commit
2898 FND_API.G_VALID_LEVEL_FULL, --p_validation_level
2899 l_x_return_status,
2900 l_x_msg_count,
2901 l_x_msg_data,
2902 l_emp_rec,
2903 l_audit_rec,
2904 l_auto_audit_id);
2905
2906 end loop;
2907 close c_audit_list_receipts;
2908
2909 ------------------------------------------------------------
2910 l_debug_info := 'Commit Audit for Overdue/Missing Receipts';
2911 ------------------------------------------------------------
2912 fnd_file.put_line(fnd_file.log, l_debug_info);
2913 COMMIT;
2914
2915 -- Notification to Supervisor
2916 BEGIN
2917 IF (p_mgr_notify = 'Y') THEN
2918 fnd_file.put_line(fnd_file.log, 'Processing Notifications to the management');
2919 open c_supervisor_list;
2920 loop
2921 fetch c_supervisor_list into l_supervisor_id;
2922 exit when c_supervisor_list%NOTFOUND;
2923
2924 IF (l_supervisor_id IS NOT NULL) THEN
2925 l_item_key := l_item_key || '-' || l_supervisor_id;
2926 fnd_file.put_line(fnd_file.log, 'Create Process ' || l_item_key);
2927 WF_ENGINE.CreateProcess(l_item_type, l_item_key , 'INFORM_MANAGER_OVERDUE');
2928
2929
2930 IF (AP_WEB_EXPENSE_WF.CheckSurrogateManager(l_supervisor_id, l_supervisor_id, p_surrogate_mgr_id)) THEN
2931 WF_DIRECTORY.GetUserName('PER',
2932 p_surrogate_mgr_id,
2933 l_supName,
2934 l_supDisplayName);
2935 fnd_file.put_line(fnd_file.log, 'Sending Notifications to Surrogate Manager ' || l_supName);
2936 ELSE
2937 WF_DIRECTORY.GetUserName('PER',
2938 l_supervisor_id,
2939 l_supName,
2940 l_supDisplayName);
2941 fnd_file.put_line(fnd_file.log, 'Sending Notifications to Supervisor ' || l_supName);
2942 END IF;
2943 WF_ENGINE.SetItemAttrText(l_item_type,
2944 l_item_key,
2945 'SUPERVISOR',
2946 l_supName);
2947 WF_ENGINE.SetItemAttrNumber(l_item_type,
2948 l_item_key,
2949 'SUPERVISOR_ID',
2950 l_supervisor_id);
2951 WF_ENGINE.SetItemAttrNumber(l_item_type,
2952 l_item_key,
2953 'OVERDUE_REQUEST_ID',
2954 FND_GLOBAL.CONC_REQUEST_ID);
2955 WF_ENGINE.StartProcess(l_item_type, l_item_key);
2956 fnd_file.put_line(fnd_file.log, 'Start Process ' || l_item_key);
2957 END IF;
2958 end loop;
2959 fnd_file.put_line(fnd_file.log, 'Done Processing Notifications to the management');
2960 END IF;
2961 END;
2962
2963 EXCEPTION
2964 WHEN OTHERS THEN
2965 raise;
2966
2967 END TrackOverdue;
2968
2969 FUNCTION WFExistsForReport(p_report_header_id in varchar2) RETURN BOOLEAN IS
2970 l_event_key_exists varchar2(1);
2971 BEGIN
2972 select 'Y'
2973 into l_event_key_exists
2974 from wf_items
2975 where item_type = C_APWRECPT
2976 and item_key like p_report_header_id||'%'
2977 and rownum = 1;
2978
2979 IF(l_event_key_exists = 'Y') THEN
2980 return true;
2981 ELSE
2982 return false;
2983 END IF;
2984 END WFExistsForReport;
2985
2986 ------------------------------------------------------------------------
2987 PROCEDURE CheckReceiptType( p_item_type IN VARCHAR2,
2988 p_item_key IN VARCHAR2,
2989 p_actid IN NUMBER,
2990 p_funmode IN VARCHAR2,
2991 p_result OUT NOCOPY VARCHAR2) IS
2992 ------------------------------------------------------------------------
2993 l_org_id number;
2994 l_expense_report_id number;
2995 l_receipt_rule AP_AUD_RULE_SETS%ROWTYPE;
2996 l_report_submitted_date date;
2997 l_receipts_status VARCHAR2(50) := 'N';
2998 l_image_receipts_status VARCHAR2(50) := 'N';
2999 l_img_missing_event VARCHAR2(1) := 'N';
3000 l_result_type VARCHAR2(50);
3001 BEGIN
3002 IF (p_funmode = 'RUN') THEN
3003
3004 l_img_missing_event := WF_ENGINE.GetActivityAttrText(p_item_type,
3005 p_item_key,
3006 p_actid,
3007 'IMAGE_MISSING_EVENT');
3008 BEGIN
3009 IF (l_img_missing_event = 'Y') THEN
3010 l_result_type := WF_ENGINE.GetItemAttrText(p_item_type,
3011 p_item_key,
3012 'MISSING_TYPE_REQUIRED');
3013 ELSE
3014 l_result_type := WF_ENGINE.GetItemAttrText(p_item_type,
3015 p_item_key,
3016 'OVERDUE_TYPE_REQUIRED');
3017 END IF;
3018 EXCEPTION
3019 WHEN OTHERS THEN
3020 l_result_type := 'ORIGINAL';
3021 END;
3022 p_result := 'COMPLETE:'||l_result_type;
3023 /* l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
3024
3025 if (l_expense_report_id is null) then
3026 Wf_Core.Raise('InvalidExpenseReportId');
3027 end if;
3028
3029 select nvl(receipts_status,'N'), nvl(image_receipts_status,'N') into l_receipts_status, l_image_receipts_status
3030 from ap_expense_report_headers_all where report_header_id = l_expense_report_id;
3031
3032 IF (l_receipts_status = 'RECEIVED') THEN
3033 l_receipts_status := 'Y';
3034 ELSE
3035 l_receipts_status := 'N';
3036 END IF;
3037
3038 IF (l_image_receipts_status = 'RECEIVED') THEN
3039 l_image_receipts_status := 'Y';
3040 ELSIF (l_image_receipts_status = 'MISSING') THEN
3041 l_image_receipts_status := 'M';
3042 ELSE
3043 l_image_receipts_status := 'N';
3044 END IF;
3045
3046
3047 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
3048 l_org_id := NULL;
3049 END IF;
3050
3051 l_report_submitted_date := WF_ENGINE.GetItemAttrDate(p_item_type,
3052 p_item_key,
3053 'EXPENSE_REPORT_SUBMIT_DATE');
3054
3055 AP_WEB_AUDIT_UTILS.get_rule(l_org_id, l_report_submitted_date, C_RECEIPT_RULE, l_receipt_rule);
3056
3057 BEGIN
3058 l_img_missing_event := WF_ENGINE.GetActivityAttrText(p_item_type,
3059 p_item_key,
3060 p_actid,
3061 'IMAGE_MISSING_EVENT');
3062 EXCEPTION
3063 WHEN OTHERS THEN
3064 l_img_missing_event := 'N';
3065 END;
3066 IF(l_image_receipts_status = 'M' AND WFExistsForReport(l_expense_report_id)) THEN
3067 p_result := 'COMPLETE:ORIGINAL';
3068 ELSIF(l_receipt_rule.ORIG_RECEIPT_REQ = 'Y' AND l_receipt_rule.IMAGE_RECEIPT_REQ = 'Y'
3069 AND l_image_receipts_status = 'N' AND l_receipts_status = 'N') THEN
3070 p_result := 'COMPLETE:BOTH';
3071 ELSIF (l_receipt_rule.ORIG_RECEIPT_REQ = 'Y' AND l_receipts_status = 'N') THEN
3072 p_result := 'COMPLETE:ORIGINAL';
3073 ELSIF (l_receipt_rule.IMAGE_RECEIPT_REQ = 'Y' AND l_image_receipts_status = 'N') THEN
3074 IF (l_img_missing_event = 'Y') THEN
3075 IF(l_receipts_status = 'N') THEN
3076 p_result := 'COMPLETE:IMAGE';
3077 ELSE
3078 p_result := 'COMPLETE';
3079 END IF;
3080 ELSE
3081 p_result := 'COMPLETE:IMAGE';
3082 END IF;
3083 ELSE
3084 p_result := 'COMPLETE:ORIGINAL';
3085 END IF;
3086 */
3087 END IF;
3088 END CheckReceiptType;
3089
3090
3091 ------------------------------------------------------------------------
3092 FUNCTION GetImageReceiptsStatus(
3093 p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
3094 ------------------------------------------------------------------------
3095 l_debug_info VARCHAR2(200);
3096
3097 l_receipts_status varchar2(30);
3098
3099 BEGIN
3100
3101 ------------------------------------------------------------
3102 l_debug_info := 'Retrieve current Receipt Status';
3103 ------------------------------------------------------------
3104 select image_receipts_status
3105 into l_receipts_status
3106 from ap_expense_report_headers_all
3107 where report_header_id = p_report_header_id;
3108
3109 return l_receipts_status;
3110
3111 EXCEPTION
3112 WHEN NO_DATA_FOUND THEN
3113 return null;
3114 WHEN OTHERS THEN
3115 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'GetImageReceiptsStatus',
3116 to_char(p_report_header_id), l_debug_info);
3117 raise;
3118 END GetImageReceiptsStatus;
3119
3120
3121 ------------------------------------------------------------------------
3122 PROCEDURE SetImageReceiptsStatus(
3123 p_report_header_id IN NUMBER,
3124 p_receipts_status IN VARCHAR2) IS
3125 ------------------------------------------------------------------------
3126 l_debug_info VARCHAR2(200);
3127
3128 l_image_receipts_status varchar2(30);
3129
3130 BEGIN
3131
3132 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetImageReceiptsStatus');
3133
3134 ------------------------------------------------------------
3135 l_debug_info := 'Lock current Receipt Status';
3136 ------------------------------------------------------------
3137 select image_receipts_status
3138 into l_image_receipts_status
3139 from ap_expense_report_headers_all
3140 where report_header_id = p_report_header_id
3141 for update of receipts_status nowait;
3142
3143 ------------------------------------------------------------
3144 l_debug_info := 'Update current Receipt Status';
3145 ------------------------------------------------------------
3146 update ap_expense_report_headers_all
3147 set image_receipts_status = p_receipts_status
3148 where report_header_id = p_report_header_id;
3149
3150 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetImageReceiptsStatus');
3151
3152 EXCEPTION
3153 WHEN OTHERS THEN
3154 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetImageReceiptsStatus',
3155 to_char(p_report_header_id), p_receipts_status, l_debug_info);
3156 raise;
3157 END SetImageReceiptsStatus;
3158
3159
3160 ------------------------------------------------------------------------
3161 PROCEDURE CheckNotifyImageReceived(
3162 p_item_type IN VARCHAR2,
3163 p_item_key IN VARCHAR2,
3164 p_actid IN NUMBER,
3165 p_funmode IN VARCHAR2,
3166 p_result OUT NOCOPY VARCHAR2) IS
3167 ------------------------------------------------------------------------
3168 l_debug_info VARCHAR2(200);
3169
3170 l_notif_received fnd_lookups.lookup_code%TYPE;
3171 l_days_overdue number := 0;
3172
3173 l_report_header_id number;
3174 l_image_receipts_status varchar2(30);
3175
3176 BEGIN
3177
3178 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckNotifyImageReceived');
3179
3180 IF (p_funmode = 'RUN') THEN
3181
3182 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3183 p_item_key,
3184 'EXPENSE_REPORT_ID');
3185
3186 ------------------------------------------------------------
3187 l_debug_info := 'Retrieve current Image Receipt Status';
3188 ------------------------------------------------------------
3189 l_image_receipts_status := GetImageReceiptsStatus(l_report_header_id);
3190
3191 ----------------------------------------------------------
3192 l_debug_info := 'Check if Notify Receipts Received is enabled';
3193 ----------------------------------------------------------
3194 l_notif_received := WF_ENGINE.GetItemAttrText(p_item_type,
3195 p_item_key,
3196 'NOTIF_RULE_NOTIF_RECEIVED');
3197
3198 ----------------------------------------------------------
3199 l_debug_info := 'Check if Days Overdue';
3200 ----------------------------------------------------------
3201 l_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
3202 p_item_key,
3203 'IMAGE_DAYS_OVERDUE');
3204
3205
3206 if ((l_image_receipts_status = C_RECEIVED) AND ((nvl(l_notif_received, C_NEVER) = C_RECEIPTS_RECEIVED) or
3207 (nvl(l_notif_received, C_NEVER) = C_RECEIPTS_OVERDUE and nvl(l_days_overdue, -1) >= 0))) then
3208 p_result := 'COMPLETE:Y';
3209 else
3210 p_result := 'COMPLETE:N';
3211 end if;
3212
3213 END IF; -- p_funmode = 'RUN'
3214
3215 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckNotifyImageReceived');
3216
3217 EXCEPTION
3218 WHEN OTHERS THEN
3219 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckNotifyImageReceived',
3220 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
3221 raise;
3222 END CheckNotifyImageReceived;
3223
3224 ------------------------------------------------------------------------
3225 PROCEDURE SetImageReceiptsStatus(
3226 p_item_type IN VARCHAR2,
3227 p_item_key IN VARCHAR2,
3228 p_actid IN NUMBER,
3229 p_funmode IN VARCHAR2,
3230 p_result OUT NOCOPY VARCHAR2) IS
3231 ------------------------------------------------------------------------
3232 l_debug_info VARCHAR2(200);
3233
3234 l_orig_receipts_status varchar2(30);
3235 l_receipts_status varchar2(30);
3236 l_report_header_id number;
3237
3238 BEGIN
3239
3240 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetImageReceiptsStatus');
3241
3242 IF (p_funmode = 'RUN') THEN
3243
3244 -------------------------------------------------------------------
3245 l_debug_info := 'Retrieve Activity Attr Receipts Status';
3246 -------------------------------------------------------------------
3247 l_receipts_status := WF_ENGINE.GetActivityAttrText(p_item_type,
3248 p_item_key,
3249 p_actid,
3250 'IMAGE_RECEIPTS_STATUS');
3251
3252 ------------------------------------------------------------
3253 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
3254 ------------------------------------------------------------
3255 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3256 p_item_key,
3257 'EXPENSE_REPORT_ID');
3258
3259 ------------------------------------------------------------
3260 l_debug_info := 'Update current Receipt Status';
3261 ------------------------------------------------------------
3262 SetImageReceiptsStatus(l_report_header_id, l_receipts_status);
3263
3264 p_result := 'COMPLETE';
3265
3266 END IF; -- p_funmode = 'RUN'
3267
3268 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetImageReceiptsStatus');
3269
3270 EXCEPTION
3271 WHEN OTHERS THEN
3272 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetImageReceiptsStatus',
3273 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
3274 raise;
3275 END SetImageReceiptsStatus;
3276
3277 ------------------------------------------------------------------------
3278 PROCEDURE SetImageOverdueDays(
3279 p_item_type IN VARCHAR2,
3280 p_item_key IN VARCHAR2,
3281 p_actid IN NUMBER,
3282 p_funmode IN VARCHAR2,
3283 p_result OUT NOCOPY VARCHAR2) IS
3284 ------------------------------------------------------------------------
3285 l_debug_info VARCHAR2(200);
3286
3287 l_days_overdue number;
3288 l_report_submitted_date date;
3289 l_notif_rule_days_overdue number;
3290
3291 BEGIN
3292
3293 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetDaysOverdue');
3294
3295 IF (p_funmode = 'RUN') THEN
3296
3297 SetImageOverdueDays(p_item_type, p_item_key);
3298
3299 p_result := 'COMPLETE';
3300
3301 END IF; -- p_funmode = 'RUN'
3302
3303 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetDaysOverdue');
3304
3305 EXCEPTION
3306 WHEN OTHERS THEN
3307 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetDaysOverdue',
3308 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
3309 raise;
3310 END SetImageOverdueDays;
3311
3312 ------------------------------------------------------------------------
3313 PROCEDURE SetImageOverdueDays(
3314 p_item_type IN VARCHAR2,
3315 p_item_key IN VARCHAR2) IS
3316 ------------------------------------------------------------------------
3317 l_debug_info VARCHAR2(200);
3318
3319 l_img_days_overdue number;
3320 l_report_submitted_date date;
3321 l_notif_rule_days_overdue number;
3322 l_notif_image_days_overdue number;
3323 l_receipts_status varchar2(30);
3324 l_report_header_id number;
3325
3326 BEGIN
3327
3328 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetDaysOverdue');
3329
3330 /*
3331 DAYS_OVERDUE is the diff between notif sent date and date the
3332 the expense report receipts package became overdue
3333 notif sent date - (report submission date + NOTIF_RULE_DAYS_OVERDUE)
3334 */
3335 l_report_submitted_date := WF_ENGINE.GetItemAttrDate(p_item_type,
3336 p_item_key,
3337 'EXPENSE_REPORT_SUBMIT_DATE');
3338
3339
3340 l_notif_image_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
3341 p_item_key,
3342 'NOTIF_IMAGE_DAYS_OVERDUE');
3343
3344 l_img_days_overdue := trunc(sysdate) - (trunc(l_report_submitted_date) );--+ l_notif_image_days_overdue);
3345
3346 ----------------------------------------------------------
3347 l_debug_info := 'Set IMAGE_DAYS_OVERDUE Item Attribute';
3348 ----------------------------------------------------------
3349
3350 WF_ENGINE.SetItemAttrNumber(p_item_type, p_item_key, 'IMAGE_DAYS_OVERDUE', l_img_days_overdue);
3351
3352 ------------------------------------------------------------
3353 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
3354 ------------------------------------------------------------
3355 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3356 p_item_key,
3357 'EXPENSE_REPORT_ID');
3358
3359 l_receipts_status := GetImageReceiptsStatus(l_report_header_id);
3360
3361 if (l_receipts_status = C_IN_TRANSIT) then
3362 ------------------------------------------------------------
3363 l_debug_info := 'Update current Receipt Status to Overdue if In Transit';
3364 ------------------------------------------------------------
3365 SetImageReceiptsStatus(l_report_header_id, C_OVERDUE);
3366 end if;
3367
3368 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetDaysOverdue');
3369
3370 EXCEPTION
3371 WHEN OTHERS THEN
3372 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetDaysOverdue',
3373 p_item_type, p_item_key, l_debug_info);
3374 raise;
3375 END SetImageOverdueDays;
3376
3377 ------------------------------------------------------------------------
3378 PROCEDURE GetImageReceiptsStatus(
3379 p_item_type IN VARCHAR2,
3380 p_item_key IN VARCHAR2,
3381 p_actid IN NUMBER,
3382 p_funmode IN VARCHAR2,
3383 p_result OUT NOCOPY VARCHAR2) IS
3384 ------------------------------------------------------------------------
3385 l_debug_info VARCHAR2(200);
3386
3387 l_receipts_status varchar2(30);
3388 l_report_header_id number;
3389
3390 BEGIN
3391
3392 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start GetImageReceiptsStatus');
3393
3394 IF (p_funmode = 'RUN') THEN
3395
3396 ------------------------------------------------------------
3397 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
3398 ------------------------------------------------------------
3399 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3400 p_item_key,
3401 'EXPENSE_REPORT_ID');
3402
3403 ------------------------------------------------------------
3404 l_debug_info := 'Retrieve current Receipt Status';
3405 ------------------------------------------------------------
3406 l_receipts_status := GetImageReceiptsStatus(l_report_header_id);
3407
3408 p_result := 'COMPLETE:'||l_receipts_status;
3409
3410 END IF; -- p_funmode = 'RUN'
3411
3412 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end GetImageReceiptsStatus');
3413
3414 EXCEPTION
3415 WHEN OTHERS THEN
3416 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'GetReceiptsStatus',
3417 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
3418 raise;
3419 END GetImageReceiptsStatus;
3420
3421 ------------------------------------------------------------------------
3422 PROCEDURE UpdateOriginalInTransit(
3423 p_item_type IN VARCHAR2,
3424 p_item_key IN VARCHAR2,
3425 p_actid IN NUMBER,
3426 p_funmode IN VARCHAR2,
3427 p_result OUT NOCOPY VARCHAR2) IS
3428 ------------------------------------------------------------------------
3429 l_org_id number;
3430 l_expense_report_id number;
3431 l_receipt_rule AP_AUD_RULE_SETS%ROWTYPE;
3432 l_report_submitted_date date;
3433 l_receipts_status varchar2(30);
3434
3435 BEGIN
3436 IF (p_funmode = 'RUN') THEN
3437 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
3438
3439 if (l_expense_report_id is null) then
3440 Wf_Core.Raise('InvalidExpenseReportId');
3441 end if;
3442
3443 l_receipts_status := GetReceiptsStatus(l_expense_report_id);
3444
3445 IF (nvl(l_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) <> AP_WEB_RECEIPTS_WF.C_RECEIVED) THEN
3446 SetReceiptsStatus(l_expense_report_id, C_IN_TRANSIT);
3447 END IF;
3448 p_result := 'COMPLETE';
3449 END IF;
3450
3451
3452 END UpdateOriginalInTransit;
3453
3454 ------------------------------------------------------------------------
3455 PROCEDURE RaiseMissingEvent(
3456 p_item_type IN VARCHAR2,
3457 p_item_key IN VARCHAR2,
3458 p_actid IN NUMBER,
3459 p_funmode IN VARCHAR2,
3460 p_result OUT NOCOPY VARCHAR2) IS
3461 ------------------------------------------------------------------------
3462 l_expense_report_id number;
3463 BEGIN
3464 IF (p_funmode = 'RUN') THEN
3465 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
3466
3467 if (l_expense_report_id is null) then
3468 Wf_Core.Raise('InvalidExpenseReportId');
3469 end if;
3470 RaiseMissingEvent(l_expense_report_id);
3471 p_result := 'COMPLETE';
3472 END IF;
3473
3474 END RaiseMissingEvent;
3475
3476 ------------------------------------------------------------------------
3477 PROCEDURE AcceptMissingReceiptDecl(
3478 p_item_type IN VARCHAR2,
3479 p_item_key IN VARCHAR2,
3480 p_actid IN NUMBER,
3481 p_funmode IN VARCHAR2,
3482 p_result OUT NOCOPY VARCHAR2) IS
3483 ------------------------------------------------------------------------
3484 l_org_id number;
3485 l_expense_report_id number;
3486 l_receipt_rule AP_AUD_RULE_SETS%ROWTYPE;
3487 l_report_submitted_date date;
3488 l_AMEEnabled VARCHAR2(1);
3489 l_item_key wf_items.item_key%TYPE;
3490 C_WF_Version NUMBER := 0;
3491 l_wf_appr_flag VARCHAR2(1);
3492
3493
3494 BEGIN
3495 IF (p_funmode = 'RUN') THEN
3496
3497 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
3498
3499 if (l_expense_report_id is null) then
3500 Wf_Core.Raise('InvalidExpenseReportId');
3501 end if;
3502 l_item_key := to_char(l_expense_report_id);
3503
3504 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
3505 l_org_id := NULL;
3506 END IF;
3507
3508 l_report_submitted_date := WF_ENGINE.GetItemAttrDate(p_item_type,
3509 p_item_key,
3510 'EXPENSE_REPORT_SUBMIT_DATE');
3511
3512 AP_WEB_AUDIT_UTILS.get_rule(l_org_id, l_report_submitted_date, C_RECEIPT_RULE, l_receipt_rule);
3513
3514 IF (l_receipt_rule.rule_set_id is null OR nvl(l_receipt_rule.allow_recpt_decl,'N') = 'N') THEN
3515 p_result := 'COMPLETE:Y';
3516 ELSE
3517 select nvl(workflow_approved_flag, 1) into l_wf_appr_flag
3518 from ap_expense_report_headers_all
3519 where report_header_id = l_expense_report_id;
3520
3521 IF (l_wf_appr_flag IN ('A','P','Y')) THEN
3522 p_result := 'COMPLETE:Y';
3523 ELSE
3524 update ap_expense_report_headers_all set
3525 expense_status_code = 'RETURNED',
3526 report_submitted_date = null,last_update_date = sysdate
3527 where report_header_id = l_expense_report_id;
3528
3529
3530 IF (NOT AP_WEB_DB_EXPRPT_PKG.SetWkflApprvdFlagAndSource(l_expense_report_id,
3531 AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_RETURNED,
3532 'NonValidatedWebExpense')) THEN
3533 NULL;
3534 END IF;
3535 BEGIN
3536 l_AMEEnabled := WF_ENGINE.GetItemAttrText(C_APEXP,
3537 l_item_key,
3538 'AME_ENABLED');
3539 IF (l_AMEEnabled = 'Y') THEN
3540 --Bug 4425821: Uptake AME parallel approvers
3541 C_WF_Version := AP_WEB_EXPENSE_WF.GetFlowVersion(C_APEXP, l_item_key);
3542
3543 IF (C_WF_Version >= AP_WEB_EXPENSE_WF.C_R120_Version) THEN
3544 AP_WEB_EXPENSE_WF.AMEAbortRequestApprovals(l_expense_report_id);
3545 END IF;
3546
3547 AME_API2.clearAllApprovals(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
3548 transactionIdIn => l_item_key,
3549 transactionTypeIn => C_APEXP);
3550
3551 END IF;
3552 EXCEPTION
3553 WHEN OTHERS THEN
3554 NULL;
3555 END;
3556
3557
3558 AP_WEB_AUDIT_QUEUE_UTILS.remove_from_queue(l_expense_report_id);
3559 AP_WEB_AUDIT_UTILS.clear_audit_reason_codes(l_expense_report_id);
3560 AP_WEB_DB_EXPLINE_PKG.resetAPflags(l_expense_report_id);
3561 --AP_WEB_RECEIPTS_WF.RaiseAbortedEvent(l_expense_report_id);
3562
3563 BEGIN
3564 wf_engine.AbortProcess (itemtype => C_APEXP,
3565 itemkey => l_item_key,
3566 cascade => TRUE);
3567 wf_purge.Items(itemtype => C_APEXP,
3568 itemkey => l_item_key);
3569 wf_purge.TotalPerm(itemtype => C_APEXP,
3570 itemkey => l_item_key,
3571 runtimeonly => TRUE);
3572
3573
3574
3575 EXCEPTION
3576 WHEN OTHERS THEN
3577 NULL;
3578 END;
3579
3580 p_result := 'COMPLETE:N';
3581 END IF;
3582 END IF;
3583 END IF;
3584
3585 END AcceptMissingReceiptDecl;
3586
3587
3588 ------------------------------------------------------------------------
3589 PROCEDURE InitOriginalRecptTrack(
3590 p_item_type IN VARCHAR2,
3591 p_item_key IN VARCHAR2,
3592 p_actid IN NUMBER,
3593 p_funmode IN VARCHAR2,
3594 p_result OUT NOCOPY VARCHAR2) IS
3595 ------------------------------------------------------------------------
3596 l_org_id number;
3597 l_expense_report_id number;
3598 l_receipt_rule AP_AUD_RULE_SETS%ROWTYPE;
3599 l_report_submitted_date date;
3600 l_receipts_status VARCHAR2(30);
3601 l_image_receipts_status VARCHAR2(30);
3602 BEGIN
3603 IF (p_funmode = 'RUN') THEN
3604 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
3605
3606 if (l_expense_report_id is null) then
3607 Wf_Core.Raise('InvalidExpenseReportId');
3608 end if;
3609
3610 SELECT nvl(receipts_status, 'NOT_REQUIRED'), nvl(image_receipts_status, 'NOT_REQUIRED')
3611 INTO l_receipts_status, l_image_receipts_status
3612 FROM ap_expense_report_headers_all
3613 WHERE report_header_id = l_expense_report_id;
3614
3615 IF (l_image_receipts_status <> 'NOT_REQUIRED' AND l_receipts_status = 'NOT_REQUIRED') THEN
3616 SetReceiptsStatus(l_expense_report_id, 'REQUIRED');
3617 END IF;
3618
3619 /*
3620 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
3621 l_org_id := NULL;
3622 END IF;
3623
3624 l_report_submitted_date := WF_ENGINE.GetItemAttrDate(p_item_type,
3625 p_item_key,
3626 'EXPENSE_REPORT_SUBMIT_DATE');
3627
3628 AP_WEB_AUDIT_UTILS.get_rule(l_org_id, l_report_submitted_date, C_RECEIPT_RULE, l_receipt_rule);
3629 IF(l_receipt_rule.IMAGE_RECEIPT_REQ = 'Y' AND l_receipt_rule.ORIG_RECEIPT_REQ = 'N') THEN
3630 SetReceiptsStatus(l_expense_report_id, C_OVERDUE);
3631 RaiseOverdueEvent(l_expense_report_id, C_OVERDUE_ORIG_EVENT_KEY);
3632 END IF;
3633 */
3634 END IF;
3635 EXCEPTION
3636 WHEN OTHERS THEN
3637 NULL;
3638
3639 END InitOriginalRecptTrack;
3640
3641
3642 ------------------------------------------------------------------------
3643 PROCEDURE CheckRecvdRecptType(
3644 p_item_type IN VARCHAR2,
3645 p_item_key IN VARCHAR2,
3646 p_actid IN NUMBER,
3647 p_funmode IN VARCHAR2,
3648 p_result OUT NOCOPY VARCHAR2) IS
3649 ------------------------------------------------------------------------
3650 l_expense_report_id NUMBER;
3651 l_receipts_status VARCHAR2(30);
3652 l_image_receipts_status VARCHAR2(30);
3653 l_result VARCHAR2(30);
3654
3655 BEGIN
3656 IF (p_funmode = 'RUN') THEN
3657 BEGIN
3658 l_result := WF_ENGINE.GetItemAttrText(p_item_type,
3659 p_item_key,
3660 'RECEIVED_TYPE_REQUIRED');
3661 EXCEPTION
3662 WHEN OTHERS THEN
3663 l_result := 'ORIGINAL';
3664 END;
3665 p_result := 'COMPLETE:'||l_result;
3666
3667 /*l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
3668 SELECT receipts_status, image_receipts_status INTO l_receipts_status, l_image_receipts_status
3669 FROM ap_expense_report_headers_all WHERE report_header_id = l_expense_report_id;
3670
3671 IF (l_receipts_status = 'RECEIVED' AND l_image_receipts_status = 'RECEIVED') THEN
3672 p_result := 'COMPLETE:BOTH';
3673 ELSIF(l_receipts_status = 'RECEIVED') THEN
3674 p_result := 'COMPLETE:ORIGINAL';
3675 ELSIF(l_image_receipts_status = 'RECEIVED') THEN
3676 p_result := 'COMPLETE:IMAGE';
3677 END IF;
3678 */
3679 END IF;
3680
3681 EXCEPTION
3682 WHEN NO_DATA_FOUND THEN
3683 p_result := 'COMPLETE:NONE';
3684
3685 END CheckRecvdRecptType;
3686
3687 ------------------------------------------------------------------------
3688 PROCEDURE Check_Both_Required(
3689 p_item_type IN VARCHAR2,
3690 p_item_key IN VARCHAR2,
3691 p_actid IN NUMBER,
3692 p_funmode IN VARCHAR2,
3693 p_result OUT NOCOPY VARCHAR2) IS
3694 ------------------------------------------------------------------------
3695 l_result_type VARCHAR2(50);
3696 l_process_type VARCHAR2(30);
3697 l_result VARCHAR2(30);
3698
3699 BEGIN
3700 IF (p_funmode = 'RUN') THEN
3701 l_process_type := WF_ENGINE.GetActivityAttrText(p_item_type,
3702 p_item_key,
3703 p_actid,
3704 'PROCESS_TYPE');
3705
3706 IF (l_process_type = 'RECEIVED') THEN
3707 l_result_type := WF_ENGINE.GetItemAttrText(p_item_type,
3708 p_item_key,
3709 'RECEIVED_TYPE_REQUIRED');
3710 ELSIF(l_process_type = 'OVERDUE') THEN
3711 l_result_type := WF_ENGINE.GetItemAttrText(p_item_type,
3712 p_item_key,
3713 'OVERDUE_TYPE_REQUIRED');
3714 l_result_type := 'COMPLETE:'||l_result_type;
3715 ELSIF(l_process_type = 'MISSING') THEN
3716 l_result_type := WF_ENGINE.GetItemAttrText(p_item_type,
3717 p_item_key,
3718 'MISSING_TYPE_REQUIRED');
3719 l_result_type := 'COMPLETE:'||l_result_type;
3720 END IF;
3721
3722 IF(l_result_type = 'COMPLETE:BOTH') THEN
3723 p_result := 'COMPLETE:Y';
3724 ELSE
3725 p_result := 'COMPLETE:N';
3726 END IF;
3727
3728
3729 END IF;
3730
3731 EXCEPTION
3732 WHEN OTHERS THEN
3733 p_result := 'COMPLETE:N';
3734
3735 END Check_Both_Required;
3736
3737 PROCEDURE RaiseReceivedEvent(
3738 p_expense_report_id IN NUMBER,
3739 p_receipt_type IN VARCHAR2) IS
3740 l_debug_info VARCHAR2(200);
3741
3742 l_event_key wf_items.item_key%type;
3743 BEGIN
3744 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseReceivedEvent');
3745
3746 ----------------------------------------------------------
3747 l_debug_info := 'Generate Event Key';
3748 ----------------------------------------------------------
3749 IF (p_receipt_type = 'IMAGE') THEN
3750 l_event_key := GenerateEventKey(p_expense_report_id, C_RECEIVED_IMG_EVENT_KEY);
3751 ELSE
3752 l_event_key := GenerateEventKey(p_expense_report_id, C_RECEIVED_EVENT_KEY);
3753 END IF;
3754
3755 ----------------------------------------------------------
3756 l_debug_info := 'Check Event Key';
3757 ----------------------------------------------------------
3758 if (NOT EventKeyExists(l_event_key)) then
3759
3760 ----------------------------------------------------------
3761 l_debug_info := 'Raise Received Event';
3762 ----------------------------------------------------------
3763 wf_event.raise(p_event_name => C_RECEIVED_EVENT_NAME,
3764 p_event_key => l_event_key);
3765 --p_parameters => l_parameter_list);
3766
3767 end if;
3768
3769 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseReceivedEvent');
3770
3771 EXCEPTION
3772 WHEN OTHERS THEN
3773 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseReceivedEvent',
3774 p_expense_report_id, l_debug_info);
3775 raise;
3776 END RaiseReceivedEvent;
3777 PROCEDURE AbortOriginalOverdue(
3778 p_item_type IN VARCHAR2,
3779 p_item_key IN VARCHAR2,
3780 p_actid IN NUMBER,
3781 p_funmode IN VARCHAR2,
3782 p_result OUT NOCOPY VARCHAR2) IS
3783 l_receipts_status VARCHAR2(30);
3784 l_report_header_id NUMBER;
3785 l_event_key wf_items.item_key%type;
3786 BEGIN
3787 IF (p_funmode = 'RUN') THEN
3788 l_receipts_status := WF_ENGINE.GetActivityAttrText(p_item_type,
3789 p_item_key,
3790 p_actid,
3791 'ORIG_RECEIPT_STATUS');
3792
3793 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3794 p_item_key,
3795 'EXPENSE_REPORT_ID');
3796 IF (nvl(GetReceiptsStatus(l_report_header_id), 'NOT_REQUIRED') NOT IN ('NOT_REQUIRED', 'RECEIVED')) THEN
3797 SetReceiptsStatus(l_report_header_id, l_receipts_status);
3798
3799 AbortProcess(p_item_type, p_item_key, C_OVERDUE_ORIG_EVENT_KEY);
3800 END IF;
3801
3802 /*
3803 l_event_key := GenerateEventKey(l_report_header_id, C_ABORTED_EVENT_KEY);
3804
3805 if (NOT EventKeyExists(l_event_key)) then
3806 wf_event.raise(p_event_name => C_ABORTED_EVENT_NAME,
3807 p_event_key => l_event_key);
3808 --p_parameters => l_parameter_list);
3809 end if;*/
3810 p_result := 'COMPLETE:Y';
3811 END IF;
3812
3813 EXCEPTION
3814 WHEN OTHERS THEN
3815 p_result := 'COMPLETE:N';
3816
3817 END;
3818 PROCEDURE IsReportReturned(
3819 p_item_type IN VARCHAR2,
3820 p_item_key IN VARCHAR2,
3821 p_actid IN NUMBER,
3822 p_funmode IN VARCHAR2,
3823 p_result OUT NOCOPY VARCHAR2) IS
3824 l_wf_status_code VARCHAR2(1);
3825 l_report_header_id NUMBER;
3826 BEGIN
3827 IF (p_funmode = 'RUN') THEN
3828 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
3829 p_item_key,
3830 'EXPENSE_REPORT_ID');
3831 SELECT nvl(workflow_approved_flag, 1) INTO l_wf_status_code
3832 FROM ap_expense_report_headers_all
3833 WHERE report_header_id = l_report_header_id;
3834
3835 IF (l_wf_status_code = AP_WEB_DB_EXPRPT_PKG.C_WORKFLOW_APPROVED_RETURNED) THEN
3836 p_result := 'COMPLETE:Y';
3837 ELSE
3838 p_result := 'COMPLETE:N';
3839 END IF;
3840 END IF;
3841 EXCEPTION
3842 WHEN OTHERS THEN
3843 p_result := 'COMPLETE:N';
3844 END;
3845
3846
3847
3848 END AP_WEB_RECEIPTS_WF;