[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_RECEIPTS_WF
Source
1 PACKAGE BODY AP_WEB_RECEIPTS_WF AS
2 /* $Header: apwrecptb.pls 120.6.12010000.3 2008/11/21 09:01:23 sodash 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_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.receipts.overdue';
17 C_MISSING_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.missing';
18 C_MISSING_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.receipts.missing';
19 C_RECEIVED_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.received';
20 C_RECEIVED_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.receipts.received';
21 C_ABORTED_EVENT_KEY CONSTANT VARCHAR2(30) := 'receipts.aborted';
22 C_ABORTED_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.receipts.aborted';
23
24 -- Item Key Delimeter
25 C_ITEM_KEY_DELIM CONSTANT VARCHAR2(1) := ':';
26
27
28 ------------------------------------------------------------------------
29 FUNCTION ParseItemKey(
30 p_item_type IN VARCHAR2,
31 p_item_key IN VARCHAR2) RETURN VARCHAR2 IS
32 ------------------------------------------------------------------------
33 l_debug_info VARCHAR2(200);
34
35 l_item_key wf_items.item_key%TYPE;
36
37 BEGIN
38
39
40 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start ParseItemKey');
41
42 ----------------------------------------------------------
43 l_debug_info := 'Parse the item key for the Expense Report Id';
44 ----------------------------------------------------------
45 return substrb(p_item_key, 1, instrb(p_item_key, C_ITEM_KEY_DELIM)-1);
46
47 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end ParseItemKey');
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'ParseItemKey',
52 p_item_type, p_item_key, l_debug_info);
53 raise;
54 END ParseItemKey;
55
56
57 ------------------------------------------------------------------------
58 FUNCTION IsNotifRuleSetup(
59 p_org_id IN NUMBER,
60 p_report_submitted_date IN DATE) RETURN VARCHAR2 IS
61 ------------------------------------------------------------------------
62 l_debug_info VARCHAR2(200);
63
64 l_is_notif_rule_setup varchar2(1);
65
66 BEGIN
67
68 ------------------------------------------------------------
69 l_debug_info := 'Check if Notif Rules Setup';
70 ------------------------------------------------------------
71 select 'Y'
72 into l_is_notif_rule_setup
73 from AP_AUD_RULE_SETS rs,
74 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
75 where rsa.org_id = p_org_id
76 and rsa.rule_set_id = rs.rule_set_id
77 and rs.rule_set_type = C_NOTIFY_RULE
78 and TRUNC(p_report_submitted_date)
79 BETWEEN TRUNC(NVL(rsa.START_DATE, p_report_submitted_date))
80 AND TRUNC(NVL(rsa.END_DATE, p_report_submitted_date))
81 and rownum = 1;
82
83 return 'Y';
84
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 return 'N';
88 WHEN OTHERS THEN
89 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsNotifRuleSetup',
90 to_char(p_org_id), to_char(p_report_submitted_date), l_debug_info);
91 raise;
92 END IsNotifRuleSetup;
93
94
95 ------------------------------------------------------------------------
96 PROCEDURE IsNotifRuleSetup(
97 p_item_type IN VARCHAR2,
98 p_item_key IN VARCHAR2,
99 p_actid IN NUMBER,
100 p_funmode IN VARCHAR2,
101 p_result OUT NOCOPY VARCHAR2) IS
102 ------------------------------------------------------------------------
103 l_debug_info VARCHAR2(200);
104
105 l_org_id number;
106 l_expense_report_id number;
107 l_report_submitted_date AP_EXPENSE_REPORT_HEADERS.report_submitted_date%type;
108
109 l_is_notif_rule_setup VARCHAR2(1);
110
111 BEGIN
112
113 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsNotifRuleSetup');
114
115 IF (p_funmode = 'RUN') THEN
116
117 ----------------------------------------------------------
118 l_debug_info := 'Parse the item key for the Expense Report Id';
119 ----------------------------------------------------------
120 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
121
122 if (l_expense_report_id is null) then
123 Wf_Core.Raise('InvalidExpenseReportId');
124 end if;
125
126 ----------------------------------------------------------
127 l_debug_info := 'Get the Expense Report Org Id';
128 ----------------------------------------------------------
129 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
130 l_org_id := NULL;
131 END IF;
132
133 ----------------------------------------------------------
134 l_debug_info := 'Get Expense Report data';
135 ----------------------------------------------------------
136 select report_submitted_date
137 into l_report_submitted_date
138 from ap_expense_report_headers_all
139 where report_header_id = l_expense_report_id;
140
141 ------------------------------------------------------------
142 l_debug_info := 'Check if Notif Rules Setup';
143 ------------------------------------------------------------
144 l_is_notif_rule_setup := IsNotifRuleSetup(l_org_id, l_report_submitted_date);
145
146 p_result := 'COMPLETE:'||l_is_notif_rule_setup;
147
148 ELSIF (p_funmode = 'CANCEL') THEN
149
150 p_result := 'COMPLETE';
151
152 END IF; -- p_funmode = 'RUN'
153
154 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsNotifRuleSetup');
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsNotifRuleSetup',
159 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
160 raise;
161 END IsNotifRuleSetup;
162
163
164 ------------------------------------------------------------------------
165 FUNCTION GenerateEventKey(
166 p_expense_report_id IN NUMBER,
167 p_event_key IN VARCHAR2) RETURN VARCHAR2 IS
168 ------------------------------------------------------------------------
169
170 l_timestamp varchar2(30);
171
172 BEGIN
173
174 select to_char(sysdate, 'DD-MON-RRRR HH:MI:SS')
175 into l_timestamp
176 from dual;
177
178 return p_expense_report_id||C_ITEM_KEY_DELIM||p_event_key||C_ITEM_KEY_DELIM||l_timestamp;
179
180 END GenerateEventKey;
181
182
183 ------------------------------------------------------------------------
184 FUNCTION EventKeyExists(
185 p_event_key IN VARCHAR2) RETURN BOOLEAN IS
186 ------------------------------------------------------------------------
187
188 l_event_key_exists varchar2(1) := 'N';
189
190 BEGIN
191
192 select 'Y'
193 into l_event_key_exists
194 from wf_items
195 where item_type = C_APWRECPT
196 and item_key = p_event_key
197 and rownum = 1;
198
199 return true;
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 return false;
204
205 END EventKeyExists;
206
207
208 ------------------------------------------------------------------------
209 PROCEDURE RaiseOverdueEvent(
210 p_expense_report_id IN NUMBER) IS
211 ------------------------------------------------------------------------
212 l_debug_info VARCHAR2(200);
213
214 l_event_key wf_items.item_key%type;
215
216 BEGIN
217
218 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseOverdueEvent');
219
220 ----------------------------------------------------------
221 l_debug_info := 'Generate Event Key';
222 ----------------------------------------------------------
223 l_event_key := GenerateEventKey(p_expense_report_id, C_OVERDUE_EVENT_KEY);
224
225 ----------------------------------------------------------
226 l_debug_info := 'Check Event Key';
227 ----------------------------------------------------------
228 if (NOT EventKeyExists(l_event_key)) then
229
230 ----------------------------------------------------------
231 l_debug_info := 'Raise Overdue Event';
232 ----------------------------------------------------------
233 wf_event.raise(p_event_name => C_OVERDUE_EVENT_NAME,
234 p_event_key => l_event_key);
235 --p_parameters => l_parameter_list);
236
237 end if;
238
239 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseOverdueEvent');
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseOverdueEvent',
244 p_expense_report_id, l_debug_info);
245 raise;
246 END RaiseOverdueEvent;
247
248
249 ------------------------------------------------------------------------
250 PROCEDURE RaiseMissingEvent(
251 p_expense_report_id IN NUMBER) IS
252 ------------------------------------------------------------------------
253 l_debug_info VARCHAR2(200);
254
255 l_event_key wf_items.item_key%type;
256
257 BEGIN
258
259 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseMissingEvent');
260
261 ----------------------------------------------------------
262 l_debug_info := 'Generate Event Key';
263 ----------------------------------------------------------
264 l_event_key := GenerateEventKey(p_expense_report_id, C_MISSING_EVENT_KEY);
265
266 ----------------------------------------------------------
267 l_debug_info := 'Check Event Key';
268 ----------------------------------------------------------
269 if (NOT EventKeyExists(l_event_key)) then
270
271 ----------------------------------------------------------
272 l_debug_info := 'Raise Missing Event';
273 ----------------------------------------------------------
274 wf_event.raise(p_event_name => C_MISSING_EVENT_NAME,
275 p_event_key => l_event_key);
276 --p_parameters => l_parameter_list);
277
278 end if;
279
280 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseMissingEvent');
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseMissingEvent',
285 p_expense_report_id, l_debug_info);
286 raise;
287 END RaiseMissingEvent;
288
289
290 ------------------------------------------------------------------------
291 PROCEDURE RaiseReceivedEvent(
292 p_expense_report_id IN NUMBER) IS
293 ------------------------------------------------------------------------
294 l_debug_info VARCHAR2(200);
295
296 l_event_key wf_items.item_key%type;
297
298 BEGIN
299
300 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseReceivedEvent');
301
302 ----------------------------------------------------------
303 l_debug_info := 'Generate Event Key';
304 ----------------------------------------------------------
305 l_event_key := GenerateEventKey(p_expense_report_id, C_RECEIVED_EVENT_KEY);
306
307 ----------------------------------------------------------
308 l_debug_info := 'Check Event Key';
309 ----------------------------------------------------------
310 if (NOT EventKeyExists(l_event_key)) then
311
312 ----------------------------------------------------------
313 l_debug_info := 'Raise Received Event';
314 ----------------------------------------------------------
315 wf_event.raise(p_event_name => C_RECEIVED_EVENT_NAME,
316 p_event_key => l_event_key);
317 --p_parameters => l_parameter_list);
318
319 end if;
320
321 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseReceivedEvent');
322
323 EXCEPTION
324 WHEN OTHERS THEN
325 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseReceivedEvent',
326 p_expense_report_id, l_debug_info);
327 raise;
328 END RaiseReceivedEvent;
329
330
331 ------------------------------------------------------------------------
332 PROCEDURE RaiseAbortedEvent(
333 p_expense_report_id IN NUMBER) IS
334 ------------------------------------------------------------------------
335 l_debug_info VARCHAR2(200);
336
337 l_event_key wf_items.item_key%type;
338
339 BEGIN
340
341 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseAbortedEvent');
342
343 ----------------------------------------------------------
344 l_debug_info := 'Generate Event Key';
345 ----------------------------------------------------------
346 l_event_key := GenerateEventKey(p_expense_report_id, C_ABORTED_EVENT_KEY);
347
348 ----------------------------------------------------------
349 l_debug_info := 'Check Event Key';
350 ----------------------------------------------------------
351 if (NOT EventKeyExists(l_event_key)) then
352
353 ----------------------------------------------------------
354 l_debug_info := 'Update Receipts Status if not Received or Waived';
355 ----------------------------------------------------------
356 begin
357 update ap_expense_report_headers_all
358 set receipts_status = ''
359 where report_header_id = p_expense_report_id
360 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)
361 and receipts_received_date is null;
362 exception
363 when others then null;
364 end;
365
366 ----------------------------------------------------------
367 l_debug_info := 'Reset Held Reports';
368 ----------------------------------------------------------
369 -- Bug 4075804
370 begin
371 update ap_expense_report_headers_all
372 set expense_status_code = C_PENDING_HOLDS,
373 holding_report_header_id = null,
374 expense_last_status_date = sysdate
375 where holding_report_header_id = p_expense_report_id
376 and expense_status_code = C_PAYMENT_HELD;
377 exception
378 when others then null;
379 end;
380
381 ----------------------------------------------------------
382 l_debug_info := 'Raise Aborted Event';
383 ----------------------------------------------------------
384 wf_event.raise(p_event_name => C_ABORTED_EVENT_NAME,
385 p_event_key => l_event_key);
386 --p_parameters => l_parameter_list);
387
388 end if;
389
390 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseAbortedEvent');
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseAbortedEvent',
395 p_expense_report_id, l_debug_info);
396 raise;
397 END RaiseAbortedEvent;
398
399
400 ------------------------------------------------------------------------
401 PROCEDURE RaiseAbortedEvent(
402 p_item_type IN VARCHAR2,
403 p_item_key IN VARCHAR2,
404 p_actid IN NUMBER,
405 p_funmode IN VARCHAR2,
406 p_result OUT NOCOPY VARCHAR2) IS
407 ------------------------------------------------------------------------
408 l_debug_info VARCHAR2(200);
409
410 BEGIN
411
412 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start RaiseAbortedEvent');
413
414 IF (p_funmode = 'RUN') THEN
415
416 ----------------------------------------------------------
417 l_debug_info := 'Raise Aborted event';
418 ----------------------------------------------------------
419 RaiseAbortedEvent(WF_ENGINE.GetItemAttrNumber(p_item_type,
420 p_item_key,
421 'EXPENSE_REPORT_ID'));
422
423 p_result := 'COMPLETE';
424
425 END IF; -- p_funmode = 'RUN'
426
427 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end RaiseAbortedEvent');
428
429 EXCEPTION
430 WHEN OTHERS THEN
431 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'RaiseAbortedEvent',
432 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
433 raise;
434 END RaiseAbortedEvent;
435
436
437 ------------------------------------------------------------------------
438 PROCEDURE Init(
439 p_item_type IN VARCHAR2,
440 p_item_key IN VARCHAR2) IS
441 ------------------------------------------------------------------------
442 l_debug_info VARCHAR2(200);
443
444 l_textNameArr Wf_Engine.NameTabTyp;
445 l_numNameArr Wf_Engine.NameTabTyp;
446 l_textValArr Wf_Engine.TextTabTyp;
447 l_numValArr Wf_Engine.NumTabTyp;
448 iText NUMBER :=0;
449 iNum NUMBER :=0;
450
451 l_org_id number;
452 l_expense_report_id number;
453 l_created_by number;
454 l_preparer_id number;
455 l_preparer_name wf_users.name%type;
456 l_preparer_display_name wf_users.display_name%type;
457 l_employee_id number;
458 l_employee_name wf_users.name%type;
459 l_employee_display_name wf_users.display_name%type;
460 l_invoice_num AP_EXPENSE_REPORT_HEADERS.invoice_num%type;
461 l_cost_center AP_EXPENSE_REPORT_HEADERS.flex_concatenated%type;
462 l_total varchar2(80);
463 l_purpose AP_EXPENSE_REPORT_HEADERS.description%type;
464 l_report_submitted_date AP_EXPENSE_REPORT_HEADERS.report_submitted_date%type;
465
466 l_notif_rule AP_AUD_RULE_SETS%ROWTYPE;
467 l_is_notif_rule_setup varchar2(1) := 'N';
468
469 BEGIN
470
471 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start Init');
472
473 ----------------------------------------------------------
474 l_debug_info := 'Parse the item key for the Expense Report Id';
475 ----------------------------------------------------------
476 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
477
478 if (l_expense_report_id is null) then
479 Wf_Core.Raise('InvalidExpenseReportId');
480 end if;
481
482 ----------------------------------------------------------
483 l_debug_info := 'Get the Expense Report Org Id';
484 ----------------------------------------------------------
485 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
486 l_org_id := NULL;
487 END IF;
488
489
490 ----------------------------------------------------------
491 l_debug_info := 'Get Expense Report data';
492 -- Note: was thinking of getting data from APEXP WF but we cannot
493 -- assume that Expenses WF still exists (may be purged)
494 ----------------------------------------------------------
495 select created_by,
496 employee_id,
497 invoice_num,
498 flex_concatenated,
499 to_char(nvl(AMT_DUE_CCARD_COMPANY,0)+nvl(AMT_DUE_EMPLOYEE,0),
500 FND_CURRENCY.Get_Format_Mask(default_currency_code,22))||' '||default_currency_code,
501 description,
502 report_submitted_date
503 into l_created_by,
504 l_employee_id,
505 l_invoice_num,
506 l_cost_center,
507 l_total,
508 l_purpose,
509 l_report_submitted_date
510 from ap_expense_report_headers_all
511 where report_header_id = l_expense_report_id;
512
513 ----------------------------------------------------------
514 l_debug_info := 'Get Preparer Id using Created By';
515 ----------------------------------------------------------
516 if (AP_WEB_DB_HR_INT_PKG.GetEmpIdForUser(l_created_by, l_preparer_id)) then
517 null;
518 end if;
519
520 ------------------------------------------------------------
521 l_debug_info := 'Get Name Info Associated With Preparer Id';
522 ------------------------------------------------------------
523 WF_DIRECTORY.GetUserName('PER',
524 l_preparer_id,
525 l_preparer_name,
526 l_preparer_display_name);
527
528 if (l_preparer_name is null) then
529 Wf_Core.Raise('InvalidOwner');
530 end if;
531
532 ----------------------------------------------------------
533 l_debug_info := 'Set the Preparer as the Owner of Receipts Mgmt Workflow Process.';
534 ----------------------------------------------------------
535 WF_ENGINE.SetItemOwner(p_item_type, p_item_key, l_preparer_name);
536
537 ----------------------------------------------------------
538 l_debug_info := 'Set Item User Key to Invoice Number for easier query ';
539 ----------------------------------------------------------
540 WF_ENGINE.SetItemUserKey(p_item_type,
541 p_item_key,
542 l_invoice_num);
543
544 --------------------------------------------------------
545 l_debug_info := 'Set EXPENSE_REPORT Item Attribute';
546 --------------------------------------------------------
547 iText := iText + 1;
548 l_textNameArr(iText) := 'EXPENSE_REPORT';
549 l_textValArr(iText) := l_invoice_num;
550
551 ------------------------------------------------------------
552 l_debug_info := 'Get Name Info Associated With Employee_Id';
553 ------------------------------------------------------------
554 WF_DIRECTORY.GetUserName('PER',
555 l_employee_id,
556 l_employee_name,
557 l_employee_display_name);
558
559 ----------------------------------------------------------
560 l_debug_info := 'Set ORG_ID Item Attribute';
561 ----------------------------------------------------------
562 iNum := iNum + 1;
563 l_numNameArr(iNum) := 'ORG_ID';
564 l_numValArr(iNum) := l_org_id;
565
566 ----------------------------------------------------------
567 l_debug_info := 'Set EXPENSE_REPORT_ID Item Attribute';
568 ----------------------------------------------------------
569 iNum := iNum + 1;
570 l_numNameArr(iNum) := 'EXPENSE_REPORT_ID';
571 l_numValArr(iNum) := l_expense_report_id;
572
573 ----------------------------------------------------------
574 l_debug_info := 'Set EXPENSE_REPORT_FOR Item Attribute';
575 ----------------------------------------------------------
576 iText := iText + 1;
577 l_textNameArr(iText) := 'EXPENSE_REPORT_FOR';
578 l_textValArr(iText) := l_employee_display_name;
579
580 ----------------------------------------------------------
581 l_debug_info := 'Set EXPENSE_REPORT_COST_CENTER Item Attribute';
582 ----------------------------------------------------------
583 iText := iText + 1;
584 l_textNameArr(iText) := 'EXPENSE_REPORT_COST_CENTER';
585 l_textValArr(iText) := l_cost_center;
586
587 ----------------------------------------------------------
588 l_debug_info := 'Set EXPENSE_REPORT_TOTAL Item Attribute';
589 ----------------------------------------------------------
590 iText := iText + 1;
591 l_textNameArr(iText) := 'EXPENSE_REPORT_TOTAL';
592 l_textValArr(iText) := l_total;
593
594 ----------------------------------------------------------
595 l_debug_info := 'Set EXPENSE_REPORT_PURPOSE Item Attribute';
596 ----------------------------------------------------------
597 iText := iText + 1;
598 l_textNameArr(iText) := 'EXPENSE_REPORT_PURPOSE';
599 l_textValArr(iText) := l_purpose;
600
601 ----------------------------------------------------------
602 l_debug_info := 'Set EXPENSE_REPORT_SUBMIT_DATE Item Attribute';
603 ----------------------------------------------------------
604 WF_ENGINE.SetItemAttrDate(p_item_type, p_item_key, 'EXPENSE_REPORT_SUBMIT_DATE', l_report_submitted_date);
605
606 ----------------------------------------------------------
607 l_debug_info := 'Check Notification Rule';
608 ----------------------------------------------------------
609 l_is_notif_rule_setup := IsNotifRuleSetup(l_org_id, l_report_submitted_date);
610
611 if (l_is_notif_rule_setup = 'Y') then
612
613 ----------------------------------------------------------
614 l_debug_info := 'Get the Notification Rule';
615 ----------------------------------------------------------
616 AP_WEB_AUDIT_UTILS.get_rule(l_org_id, l_report_submitted_date, C_NOTIFY_RULE, l_notif_rule);
617
618 ----------------------------------------------------------
619 l_debug_info := 'Set NOTIF_RULE_DAYS_OVERDUE Item Attribute';
620 ----------------------------------------------------------
621 /*
622 NOTIF_RULE_DAYS_OVERDUE is the rule for determining
623 what's considered overdue
624 process date - report submission date
625 */
626 iNum := iNum + 1;
627 l_numNameArr(iNum) := 'NOTIF_RULE_DAYS_OVERDUE';
628 l_numValArr(iNum) := l_notif_rule.NOTIFY_RCT_OVERDUE_DAYS; -- relative time in days
629 --l_numValArr(iNum) := 60; -- relative time in days
630
631 ----------------------------------------------------------
632 l_debug_info := 'Set NOTIF_RULE_TIMEOUT Item Attribute';
633 ----------------------------------------------------------
634 /*
635 NOTIF_RULE_TIMEOUT is the rule for determining
636 how long to wait for a response from the preparer
637 */
638 iNum := iNum + 1;
639 l_numNameArr(iNum) := 'NOTIF_RULE_TIMEOUT';
640 l_numValArr(iNum) := l_notif_rule.NOTIFY_ACTION_REQUIRED_DAYS * C_DAY_TO_MINUTES; -- relative time in minutes
641 --l_numValArr(iNum) := 1440; -- relative time in minutes
642
643 ----------------------------------------------------------
644 l_debug_info := 'Set NOTIF_RULE_WAIT Item Attribute';
645 ----------------------------------------------------------
646 /*
647 NOTIF_RULE_WAIT is the rule for determining
648 how long to wait after a response from the preparer
649 */
650 iNum := iNum + 1;
651 l_numNameArr(iNum) := 'NOTIF_RULE_WAIT';
652 l_numValArr(iNum) := l_notif_rule.NOTIFY_RESPONSE_OVERDUE_DAYS; -- relative time in days
653 --l_numValArr(iNum) := 0; -- relative time in days
654
655 ----------------------------------------------------------
656 l_debug_info := 'Set NOTIF_RULE_MISSING_DECL_REQD Item Attribute';
657 ----------------------------------------------------------
658 /*
659 NOTIF_RULE_MISSING_DECL_REQD is the rule for determining
660 whether a missing receipt declaration is required or not
661 */
662 iText := iText + 1;
663 l_textNameArr(iText) := 'NOTIF_RULE_MISSING_DECL_REQD';
664 l_textValArr(iText) := l_notif_rule.NOTIFY_DOCUMENT_REQUIRED_CODE;
665 --l_textValArr(iText) := C_REQUIRED;
666
667 ----------------------------------------------------------
668 l_debug_info := 'Set NOTIF_RULE_NOTIF_RECEIVED Item Attribute';
669 ----------------------------------------------------------
670 /*
671 NOTIF_RULE_NOTIF_RECEIVED is the rule for determining
672 whether to notify the preparer when the receipts pkg is received
673 */
674 iText := iText + 1;
675 l_textNameArr(iText) := 'NOTIF_RULE_NOTIF_RECEIVED';
676 l_textValArr(iText) := l_notif_rule.NOTIFY_RCT_RECEIVED_CODE;
677 --l_textValArr(iText) := C_RECEIPTS_RECEIVED;
678
679 end if; -- (l_is_notif_rule_setup = 'Y')
680
681 ----------------------------------------------------------
682 l_debug_info := 'Set PREPARER_ROLE Item Attribute';
683 ----------------------------------------------------------
684 iText := iText + 1;
685 l_textNameArr(iText) := 'PREPARER_ROLE';
686 l_textValArr(iText) := l_preparer_name;
687
688
689 -----------------------------------------------------
690 l_debug_info := 'Set all number item attributes';
691 -----------------------------------------------------
692 WF_ENGINE.SetItemAttrNumberArray(p_item_type, p_item_key, l_numNameArr, l_numValArr);
693
694 -----------------------------------------------------
695 l_debug_info := 'Set all text item attributes';
696 -----------------------------------------------------
697 WF_ENGINE.SetItemAttrTextArray(p_item_type, p_item_key, l_textNameArr, l_textValArr);
698
699
700 if (l_is_notif_rule_setup = 'Y') then
701
702 ----------------------------------------------------------
703 l_debug_info := 'Set DAYS_OVERDUE Item Attribute';
704 ----------------------------------------------------------
705 /*
706 DAYS_OVERDUE is the diff between notif sent date and date the
707 the expense report receipts package became overdue
708 notif sent date - (report submission date + NOTIF_RULE_DAYS_OVERDUE)
709 iNum := iNum + 1;
710 l_numNameArr(iNum) := 'DAYS_OVERDUE';
711 l_numValArr(iNum) := 60; -- relative time in days
712 */
713 SetDaysOverdue(p_item_type, p_item_key);
714
715 end if; -- (l_is_notif_rule_setup = 'Y')
716
717
718 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end Init');
719
720 EXCEPTION
721 WHEN OTHERS THEN
722 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'Init',
723 p_item_type, p_item_key, l_expense_report_id, l_preparer_name, l_debug_info);
724 raise;
725 END Init;
726
727
728 ------------------------------------------------------------------------
729 PROCEDURE InitOverdue(
730 p_item_type IN VARCHAR2,
731 p_item_key IN VARCHAR2,
732 p_actid IN NUMBER,
733 p_funmode IN VARCHAR2,
734 p_result OUT NOCOPY VARCHAR2) IS
735 ------------------------------------------------------------------------
736 l_debug_info VARCHAR2(200);
737
738 BEGIN
739
740 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start InitOverdue');
741
742 IF (p_funmode = 'RUN') THEN
743
744 ----------------------------------------------------------
745 l_debug_info := 'Initialize common event data';
746 ----------------------------------------------------------
747 Init(p_item_type, p_item_key);
748
749 p_result := 'COMPLETE';
750
751 END IF; -- p_funmode = 'RUN'
752
753 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end InitOverdue');
754
755 EXCEPTION
756 WHEN OTHERS THEN
757 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'InitOverdue',
758 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
759 raise;
760 END InitOverdue;
761
762
763 ------------------------------------------------------------------------
764 PROCEDURE InitMissing(
765 p_item_type IN VARCHAR2,
766 p_item_key IN VARCHAR2,
767 p_actid IN NUMBER,
768 p_funmode IN VARCHAR2,
769 p_result OUT NOCOPY VARCHAR2) IS
770 ------------------------------------------------------------------------
771 l_debug_info VARCHAR2(200);
772
773 BEGIN
774
775 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start InitMissing');
776
777 IF (p_funmode = 'RUN') THEN
778
779 ----------------------------------------------------------
780 l_debug_info := 'Initialize common event data';
781 ----------------------------------------------------------
782 Init(p_item_type, p_item_key);
783
784 p_result := 'COMPLETE';
785
786 END IF; -- p_funmode = 'RUN'
787
788 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end InitMissing');
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'InitMissing',
793 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
794 raise;
795 END InitMissing;
796
797
798 ------------------------------------------------------------------------
799 PROCEDURE CheckOverdueExists(
800 p_item_type IN VARCHAR2,
801 p_item_key IN VARCHAR2,
802 p_actid IN NUMBER,
803 p_funmode IN VARCHAR2,
804 p_result OUT NOCOPY VARCHAR2) IS
805 ------------------------------------------------------------------------
806 l_debug_info VARCHAR2(200);
807
808 l_status wf_item_activity_statuses.ACTIVITY_STATUS%TYPE;
809 l_result wf_item_activity_statuses.ACTIVITY_RESULT_CODE%TYPE;
810
811 l_item_key wf_items.item_key%TYPE;
812 l_found_item_key wf_items.item_key%TYPE;
813
814 BEGIN
815
816 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckOverdueExists');
817
818 IF (p_funmode = 'RUN') THEN
819
820 begin
821
822 ----------------------------------------------------------
823 l_debug_info := 'Getting item key of current process';
824 ----------------------------------------------------------
825 l_item_key := ParseItemKey(p_item_type, p_item_key);
826
827 ----------------------------------------------------------
828 l_debug_info := 'Encode item key for Overdue process';
829 ----------------------------------------------------------
830 l_item_key := l_item_key||C_ITEM_KEY_DELIM||C_OVERDUE_EVENT_KEY||'%';
831
832 ----------------------------------------------------------
833 l_debug_info := 'Check for at least one Overdue process';
834 ----------------------------------------------------------
835 select item_key
836 into l_found_item_key
837 from wf_items
838 where item_type = p_item_type
839 and item_key like l_item_key
840 and end_date is null
841 and rownum = 1;
842
843 p_result := 'COMPLETE:Y';
844
845 exception
846 when no_data_found then
847 p_result := 'COMPLETE:N';
848 when others then
849 p_result := 'COMPLETE:N';
850
851 end;
852
853 ELSIF (p_funmode = 'CANCEL') THEN
854 p_result := 'COMPLETE';
855 END IF; -- p_funmode = 'RUN'
856
857 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckOverdueExists');
858
859 EXCEPTION
860 WHEN OTHERS THEN
861 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckOverdueExists',
862 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
863 raise;
864 END CheckOverdueExists;
865
866
867 ------------------------------------------------------------------------
868 PROCEDURE CheckMissingExists(
869 p_item_type IN VARCHAR2,
870 p_item_key IN VARCHAR2,
871 p_actid IN NUMBER,
872 p_funmode IN VARCHAR2,
873 p_result OUT NOCOPY VARCHAR2) IS
874 ------------------------------------------------------------------------
875 l_debug_info VARCHAR2(200);
876
877 l_status wf_item_activity_statuses.ACTIVITY_STATUS%TYPE;
878 l_result wf_item_activity_statuses.ACTIVITY_RESULT_CODE%TYPE;
879
880 l_item_key wf_items.item_key%TYPE;
881 l_found_item_key wf_items.item_key%TYPE;
882
883 BEGIN
884
885 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckMissingExists');
886
887 IF (p_funmode = 'RUN') THEN
888
889 begin
890
891 ----------------------------------------------------------
892 l_debug_info := 'Getting item key of current process';
893 ----------------------------------------------------------
894 l_item_key := ParseItemKey(p_item_type, p_item_key);
895
896 ----------------------------------------------------------
897 l_debug_info := 'Encode item key for Missing process';
898 ----------------------------------------------------------
899 l_item_key := l_item_key||C_ITEM_KEY_DELIM||C_MISSING_EVENT_KEY||'%';
900
901 ----------------------------------------------------------
902 l_debug_info := 'Check for at least one Missing process';
903 ----------------------------------------------------------
904 select item_key
905 into l_found_item_key
906 from wf_items
907 where item_type = p_item_type
908 and item_key like l_item_key
909 and end_date is null
910 and rownum = 1;
911
912 p_result := 'COMPLETE:Y';
913
914 exception
915 when no_data_found then
916 p_result := 'COMPLETE:N';
917 when others then
918 p_result := 'COMPLETE:N';
919
920 end;
921
922 ELSIF (p_funmode = 'CANCEL') THEN
923 p_result := 'COMPLETE';
924 END IF; -- p_funmode = 'RUN'
925
926 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckMissingExists');
927
928 EXCEPTION
929 WHEN OTHERS THEN
930 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckMissingExists',
931 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
932 raise;
933 END CheckMissingExists;
934
935
936 ------------------------------------------------------------------------
937 PROCEDURE AbortOverdue(
938 p_item_type IN VARCHAR2,
939 p_item_key IN VARCHAR2,
940 p_actid IN NUMBER,
941 p_funmode IN VARCHAR2,
942 p_result OUT NOCOPY VARCHAR2) IS
943 ------------------------------------------------------------------------
944 l_debug_info VARCHAR2(200);
945
946 BEGIN
947
948 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start AbortOverdue');
949
950 IF (p_funmode = 'RUN') THEN
951
952 AbortProcess(p_item_type, p_item_key, C_OVERDUE_EVENT_KEY);
953
954 p_result := 'COMPLETE';
955
956 END IF; -- p_funmode = 'RUN'
957
958 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end AbortOverdue');
959
960 EXCEPTION
961 WHEN OTHERS THEN
962 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'AbortOverdue',
963 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
964 raise;
965 END AbortOverdue;
966
967
968 ------------------------------------------------------------------------
969 PROCEDURE AbortMissing(
970 p_item_type IN VARCHAR2,
971 p_item_key IN VARCHAR2,
972 p_actid IN NUMBER,
973 p_funmode IN VARCHAR2,
974 p_result OUT NOCOPY VARCHAR2) IS
975 ------------------------------------------------------------------------
976 l_debug_info VARCHAR2(200);
977
978 l_item_key wf_items.item_key%type;
979
980 BEGIN
981
982 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start AbortMissing');
983
984 IF (p_funmode = 'RUN') THEN
985
986 AbortProcess(p_item_type, p_item_key, C_MISSING_EVENT_KEY);
987
988 p_result := 'COMPLETE';
989
990 END IF; -- p_funmode = 'RUN'
991
992 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end AbortMissing');
993
994 EXCEPTION
995 WHEN OTHERS THEN
996 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'AbortMissing',
997 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
998 raise;
999 END AbortMissing;
1000
1001
1002 ------------------------------------------------------------------------
1003 PROCEDURE AbortProcess(
1004 p_item_type IN VARCHAR2,
1005 p_item_key IN VARCHAR2,
1006 p_event_key IN VARCHAR2) IS
1007 ------------------------------------------------------------------------
1008 l_debug_info VARCHAR2(200);
1009
1010 l_item_key wf_items.item_key%type;
1011 l_found_item_key wf_items.item_key%type;
1012
1013 l_status wf_item_activity_statuses.ACTIVITY_STATUS%TYPE;
1014 l_result wf_item_activity_statuses.ACTIVITY_RESULT_CODE%TYPE;
1015
1016 -- cursor for receipt events
1017 CURSOR c_receipt_events is
1018 select item_key
1019 from wf_items
1020 where item_type = p_item_type
1021 and item_key like l_item_key
1022 and end_date is null;
1023
1024 BEGIN
1025
1026 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start AbortProcess');
1027
1028 ----------------------------------------------------------
1029 l_debug_info := 'Getting item key of current process';
1030 ----------------------------------------------------------
1031 l_item_key := ParseItemKey(p_item_type, p_item_key);
1032
1033 ----------------------------------------------------------
1034 l_debug_info := 'Encode item key for event process';
1035 ----------------------------------------------------------
1036 l_item_key := l_item_key||C_ITEM_KEY_DELIM||p_event_key||'%';
1037
1038 open c_receipt_events;
1039 loop
1040
1041 fetch c_receipt_events into l_found_item_key;
1042 exit when c_receipt_events%NOTFOUND;
1043
1044 ----------------------------------------------------------
1045 l_debug_info := 'Abort event process and use the item key as the result';
1046 ----------------------------------------------------------
1047 begin
1048
1049 WF_ENGINE.AbortProcess(p_item_type,
1050 l_found_item_key,
1051 null,
1052 wf_engine.eng_force);
1053
1054 exception
1055 when others then null;
1056 end;
1057
1058 end loop;
1059 close c_receipt_events;
1060
1061 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end AbortProcess');
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'AbortProcess',
1066 p_item_type, p_item_key, p_event_key, l_debug_info);
1067 raise;
1068 END AbortProcess;
1069
1070
1071 ------------------------------------------------------------------------
1072 PROCEDURE InitReceived(
1073 p_item_type IN VARCHAR2,
1074 p_item_key IN VARCHAR2,
1075 p_actid IN NUMBER,
1076 p_funmode IN VARCHAR2,
1077 p_result OUT NOCOPY VARCHAR2) IS
1078 ------------------------------------------------------------------------
1079 l_debug_info VARCHAR2(200);
1080
1081 BEGIN
1082
1083 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start InitReceived');
1084
1085 IF (p_funmode = 'RUN') THEN
1086
1087 ----------------------------------------------------------
1088 l_debug_info := 'Initialize common event data';
1089 ----------------------------------------------------------
1090 Init(p_item_type, p_item_key);
1091
1092 p_result := 'COMPLETE';
1093
1094 END IF; -- p_funmode = 'RUN'
1095
1096 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end InitReceived');
1097
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'InitReceived',
1101 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1102 raise;
1103 END InitReceived;
1104
1105
1106 ------------------------------------------------------------------------
1107 PROCEDURE InitAborted(
1108 p_item_type IN VARCHAR2,
1109 p_item_key IN VARCHAR2,
1110 p_actid IN NUMBER,
1111 p_funmode IN VARCHAR2,
1112 p_result OUT NOCOPY VARCHAR2) IS
1113 ------------------------------------------------------------------------
1114 l_debug_info VARCHAR2(200);
1115
1116 BEGIN
1117
1118 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start InitAborted');
1119
1120 IF (p_funmode = 'RUN') THEN
1121
1122 ----------------------------------------------------------
1123 l_debug_info := 'Initialize common event data';
1124 ----------------------------------------------------------
1125 Init(p_item_type, p_item_key);
1126
1127 p_result := 'COMPLETE';
1128
1129 END IF; -- p_funmode = 'RUN'
1130
1131 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end InitAborted');
1132
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'InitAborted',
1136 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1137 raise;
1138 END InitAborted;
1139
1140 ------------------------------------------------------------------------
1141 FUNCTION GetReceiptsStatus(
1142 p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
1143 ------------------------------------------------------------------------
1144 l_debug_info VARCHAR2(200);
1145
1146 l_receipts_status varchar2(30);
1147
1148 BEGIN
1149
1150 ------------------------------------------------------------
1151 l_debug_info := 'Retrieve current Receipt Status';
1152 ------------------------------------------------------------
1153 select receipts_status
1154 into l_receipts_status
1155 from ap_expense_report_headers_all
1156 where report_header_id = p_report_header_id;
1157
1158 return l_receipts_status;
1159
1160 EXCEPTION
1161 WHEN NO_DATA_FOUND THEN
1162 return null;
1163 WHEN OTHERS THEN
1164 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'GetReceiptsStatus',
1165 to_char(p_report_header_id), l_debug_info);
1166 raise;
1167 END GetReceiptsStatus;
1168
1169
1170 ------------------------------------------------------------------------
1171 PROCEDURE GetReceiptsStatus(
1172 p_item_type IN VARCHAR2,
1173 p_item_key IN VARCHAR2,
1174 p_actid IN NUMBER,
1175 p_funmode IN VARCHAR2,
1176 p_result OUT NOCOPY VARCHAR2) IS
1177 ------------------------------------------------------------------------
1178 l_debug_info VARCHAR2(200);
1179
1180 l_receipts_status varchar2(30);
1181 l_report_header_id number;
1182
1183 BEGIN
1184
1185 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start GetReceiptsStatus');
1186
1187 IF (p_funmode = 'RUN') THEN
1188
1189 ------------------------------------------------------------
1190 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1191 ------------------------------------------------------------
1192 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1193 p_item_key,
1194 'EXPENSE_REPORT_ID');
1195
1196 ------------------------------------------------------------
1197 l_debug_info := 'Retrieve current Receipt Status';
1198 ------------------------------------------------------------
1199 l_receipts_status := GetReceiptsStatus(l_report_header_id);
1200
1201 p_result := 'COMPLETE:'||l_receipts_status;
1202
1203 END IF; -- p_funmode = 'RUN'
1204
1205 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end GetReceiptsStatus');
1206
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'GetReceiptsStatus',
1210 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1211 raise;
1212 END GetReceiptsStatus;
1213
1214
1215 ------------------------------------------------------------------------
1216 PROCEDURE SetReceiptsStatus(
1217 p_report_header_id IN NUMBER,
1218 p_receipts_status IN VARCHAR2) IS
1219 ------------------------------------------------------------------------
1220 l_debug_info VARCHAR2(200);
1221
1222 l_orig_receipts_status varchar2(30);
1223
1224 BEGIN
1225
1226 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetReceiptsStatus');
1227
1228 ------------------------------------------------------------
1229 l_debug_info := 'Lock current Receipt Status';
1230 ------------------------------------------------------------
1231 select receipts_status
1232 into l_orig_receipts_status
1233 from ap_expense_report_headers_all
1234 where report_header_id = p_report_header_id
1235 for update of receipts_status nowait;
1236
1237 ------------------------------------------------------------
1238 l_debug_info := 'Update current Receipt Status';
1239 ------------------------------------------------------------
1240 update ap_expense_report_headers_all
1241 set receipts_status = p_receipts_status
1242 where report_header_id = p_report_header_id;
1243
1244 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetReceiptsStatus');
1245
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetReceiptsStatus',
1249 to_char(p_report_header_id), p_receipts_status, l_debug_info);
1250 raise;
1251 END SetReceiptsStatus;
1252
1253
1254 ------------------------------------------------------------------------
1255 PROCEDURE SetReceiptsStatus(
1256 p_item_type IN VARCHAR2,
1257 p_item_key IN VARCHAR2,
1258 p_actid IN NUMBER,
1259 p_funmode IN VARCHAR2,
1260 p_result OUT NOCOPY VARCHAR2) IS
1261 ------------------------------------------------------------------------
1262 l_debug_info VARCHAR2(200);
1263
1264 l_orig_receipts_status varchar2(30);
1265 l_receipts_status varchar2(30);
1266 l_report_header_id number;
1267
1268 BEGIN
1269
1270 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetReceiptsStatus');
1271
1272 IF (p_funmode = 'RUN') THEN
1273
1274 -------------------------------------------------------------------
1275 l_debug_info := 'Retrieve Activity Attr Receipts Status';
1276 -------------------------------------------------------------------
1277 l_receipts_status := WF_ENGINE.GetActivityAttrText(p_item_type,
1278 p_item_key,
1279 p_actid,
1280 'RECEIPTS_STATUS');
1281
1282 ------------------------------------------------------------
1283 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1284 ------------------------------------------------------------
1285 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1286 p_item_key,
1287 'EXPENSE_REPORT_ID');
1288
1289 ------------------------------------------------------------
1290 l_debug_info := 'Update current Receipt Status';
1291 ------------------------------------------------------------
1292 SetReceiptsStatus(l_report_header_id, l_receipts_status);
1293
1294 p_result := 'COMPLETE';
1295
1296 END IF; -- p_funmode = 'RUN'
1297
1298 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetReceiptsStatus');
1299
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetReceiptsStatus',
1303 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1304 raise;
1305 END SetReceiptsStatus;
1306
1307
1308 ------------------------------------------------------------------------
1309 PROCEDURE SetDaysOverdue(
1310 p_item_type IN VARCHAR2,
1311 p_item_key IN VARCHAR2) IS
1312 ------------------------------------------------------------------------
1313 l_debug_info VARCHAR2(200);
1314
1315 l_days_overdue number;
1316 l_report_submitted_date date;
1317 l_notif_rule_days_overdue number;
1318 l_receipts_status varchar2(30);
1319 l_report_header_id number;
1320
1321 BEGIN
1322
1323 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetDaysOverdue');
1324
1325 /*
1326 DAYS_OVERDUE is the diff between notif sent date and date the
1327 the expense report receipts package became overdue
1328 notif sent date - (report submission date + NOTIF_RULE_DAYS_OVERDUE)
1329 */
1330 l_report_submitted_date := WF_ENGINE.GetItemAttrDate(p_item_type,
1331 p_item_key,
1332 'EXPENSE_REPORT_SUBMIT_DATE');
1333
1334 l_notif_rule_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
1335 p_item_key,
1336 'NOTIF_RULE_DAYS_OVERDUE');
1337
1338 l_days_overdue := trunc(sysdate) - (trunc(l_report_submitted_date) + l_notif_rule_days_overdue);
1339
1340 ----------------------------------------------------------
1341 l_debug_info := 'Set DAYS_OVERDUE Item Attribute';
1342 ----------------------------------------------------------
1343 WF_ENGINE.SetItemAttrNumber(p_item_type, p_item_key, 'DAYS_OVERDUE', l_days_overdue);
1344
1345 ------------------------------------------------------------
1346 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1347 ------------------------------------------------------------
1348 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1349 p_item_key,
1350 'EXPENSE_REPORT_ID');
1351
1352 ------------------------------------------------------------
1353 l_debug_info := 'Retrieve current Receipt Status';
1354 ------------------------------------------------------------
1355 l_receipts_status := GetReceiptsStatus(l_report_header_id);
1356
1357 if (l_receipts_status = C_IN_TRANSIT) then
1358 ------------------------------------------------------------
1359 l_debug_info := 'Update current Receipt Status to Overdue if In Transit';
1360 ------------------------------------------------------------
1361 SetReceiptsStatus(l_report_header_id, C_OVERDUE);
1362 end if;
1363
1364 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetDaysOverdue');
1365
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetDaysOverdue',
1369 p_item_type, p_item_key, l_debug_info);
1370 raise;
1371 END SetDaysOverdue;
1372
1373
1374 ------------------------------------------------------------------------
1375 PROCEDURE SetDaysOverdue(
1376 p_item_type IN VARCHAR2,
1377 p_item_key IN VARCHAR2,
1378 p_actid IN NUMBER,
1379 p_funmode IN VARCHAR2,
1380 p_result OUT NOCOPY VARCHAR2) IS
1381 ------------------------------------------------------------------------
1382 l_debug_info VARCHAR2(200);
1383
1384 l_days_overdue number;
1385 l_report_submitted_date date;
1386 l_notif_rule_days_overdue number;
1387
1388 BEGIN
1389
1390 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start SetDaysOverdue');
1391
1392 IF (p_funmode = 'RUN') THEN
1393
1394 SetDaysOverdue(p_item_type, p_item_key);
1395
1396 p_result := 'COMPLETE';
1397
1398 END IF; -- p_funmode = 'RUN'
1399
1400 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end SetDaysOverdue');
1401
1402 EXCEPTION
1403 WHEN OTHERS THEN
1404 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'SetDaysOverdue',
1405 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1406 raise;
1407 END SetDaysOverdue;
1408
1409
1410 ------------------------------------------------------------------------
1411 PROCEDURE CheckMissingDeclRequired(
1412 p_item_type IN VARCHAR2,
1413 p_item_key IN VARCHAR2,
1414 p_actid IN NUMBER,
1415 p_funmode IN VARCHAR2,
1416 p_result OUT NOCOPY VARCHAR2) IS
1417 ------------------------------------------------------------------------
1418 l_debug_info VARCHAR2(200);
1419
1420 l_missing_decl_reqd fnd_lookups.lookup_code%TYPE;
1421
1422 BEGIN
1423
1424 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckMissingDeclRequired');
1425
1426 IF (p_funmode = 'RUN') THEN
1427
1428 ----------------------------------------------------------
1429 l_debug_info := 'Check if Missing Declaration is required';
1430 ----------------------------------------------------------
1431 l_missing_decl_reqd := WF_ENGINE.GetItemAttrText(p_item_type,
1432 p_item_key,
1433 'NOTIF_RULE_MISSING_DECL_REQD');
1434
1435 if (nvl(l_missing_decl_reqd, C_NOT_REQUIRED) = C_REQUIRED) then
1436 p_result := 'COMPLETE:Y';
1437 else
1438 p_result := 'COMPLETE:N';
1439 end if;
1440
1441 END IF; -- p_funmode = 'RUN'
1442
1443 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckMissingDeclRequired');
1444
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckMissingDeclRequired',
1448 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1449 raise;
1450 END CheckMissingDeclRequired;
1451
1452
1453
1454 ------------------------------------------------------------------------
1455 PROCEDURE CheckNotifyReceived(
1456 p_item_type IN VARCHAR2,
1457 p_item_key IN VARCHAR2,
1458 p_actid IN NUMBER,
1459 p_funmode IN VARCHAR2,
1460 p_result OUT NOCOPY VARCHAR2) IS
1461 ------------------------------------------------------------------------
1462 l_debug_info VARCHAR2(200);
1463
1464 l_notif_received fnd_lookups.lookup_code%TYPE;
1465 l_days_overdue number := 0;
1466
1467 BEGIN
1468
1469 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CheckNotifyReceived');
1470
1471 IF (p_funmode = 'RUN') THEN
1472
1473 ----------------------------------------------------------
1474 l_debug_info := 'Check if Notify Receipts Received is enabled';
1475 ----------------------------------------------------------
1476 l_notif_received := WF_ENGINE.GetItemAttrText(p_item_type,
1477 p_item_key,
1478 'NOTIF_RULE_NOTIF_RECEIVED');
1479
1480 ----------------------------------------------------------
1481 l_debug_info := 'Check if Days Overdue';
1482 ----------------------------------------------------------
1483 l_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
1484 p_item_key,
1485 'DAYS_OVERDUE');
1486
1487
1488 if ((nvl(l_notif_received, C_NEVER) = C_RECEIPTS_RECEIVED) or
1489 (nvl(l_notif_received, C_NEVER) = C_RECEIPTS_OVERDUE and nvl(l_days_overdue, 0) > 0)) then
1490 p_result := 'COMPLETE:Y';
1491 else
1492 p_result := 'COMPLETE:N';
1493 end if;
1494
1495 END IF; -- p_funmode = 'RUN'
1496
1497 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CheckNotifyReceived');
1498
1499 EXCEPTION
1500 WHEN OTHERS THEN
1501 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CheckNotifyReceived',
1502 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1503 raise;
1504 END CheckNotifyReceived;
1505
1506
1507 ------------------------------------------------------------------------
1508 PROCEDURE IsReceivedWaived(
1509 p_item_type IN VARCHAR2,
1510 p_item_key IN VARCHAR2,
1511 p_actid IN NUMBER,
1512 p_funmode IN VARCHAR2,
1513 p_result OUT NOCOPY VARCHAR2) IS
1514 ------------------------------------------------------------------------
1515 l_debug_info VARCHAR2(200);
1516
1517 l_receipts_status varchar2(30);
1518 l_report_header_id number;
1519
1520 BEGIN
1521
1522 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsReceivedWaived');
1523
1524 IF (p_funmode = 'RUN') THEN
1525
1526 ------------------------------------------------------------
1527 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1528 ------------------------------------------------------------
1529 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1530 p_item_key,
1531 'EXPENSE_REPORT_ID');
1532
1533 ------------------------------------------------------------
1534 l_debug_info := 'Retrieve current Receipt Status';
1535 ------------------------------------------------------------
1536 l_receipts_status := GetReceiptsStatus(l_report_header_id);
1537
1538 if (l_receipts_status in (C_RECEIVED, C_RECEIVED_RESUBMITTED, C_WAIVED)) then
1539 p_result := 'COMPLETE:Y';
1540 else
1541 p_result := 'COMPLETE:N';
1542 end if;
1543
1544 END IF; -- p_funmode = 'RUN'
1545
1546 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsReceivedWaived');
1547
1548 EXCEPTION
1549 WHEN OTHERS THEN
1550 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsReceivedWaived',
1551 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1552 raise;
1553 END IsReceivedWaived;
1554
1555
1556 ------------------------------------------------------------------------
1557 FUNCTION IsShortpay(
1558 p_item_type IN VARCHAR2,
1559 p_item_key IN VARCHAR2,
1560 p_shortpay_type IN VARCHAR2) RETURN VARCHAR2 IS
1561 ------------------------------------------------------------------------
1562 l_debug_info VARCHAR2(200);
1563
1564 l_report_header_id number;
1565 l_is_shortpay varchar2(1) := 'N';
1566
1567 BEGIN
1568
1569 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsShortpay');
1570
1571 ------------------------------------------------------------
1572 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1573 ------------------------------------------------------------
1574 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1575 p_item_key,
1576 'EXPENSE_REPORT_ID');
1577
1578 ------------------------------------------------------------
1579 l_debug_info := 'Check if Missing or Policy Shortpay';
1580 ------------------------------------------------------------
1581 select 'Y'
1582 into l_is_shortpay
1583 from ap_expense_report_headers_all aerh,
1584 wf_items wf
1585 where aerh.report_header_id = l_report_header_id
1586 and aerh.shortpay_parent_id is not null
1587 and wf.item_type = C_APEXP
1588 and wf.item_key = to_char(aerh.report_header_id) -- Bug 6841589 (sodash) to solve the invalid number exception
1589 and wf.end_date is null
1590 and wf.root_activity = p_shortpay_type
1591 and rownum = 1;
1592
1593 return l_is_shortpay;
1594
1595 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsShortpay');
1596
1597 EXCEPTION
1598 WHEN NO_DATA_FOUND THEN
1599 return 'N';
1600 WHEN OTHERS THEN
1601 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsShortpay',
1602 p_item_type, p_item_key, l_debug_info);
1603 raise;
1604 END IsShortpay;
1605
1606
1607 ------------------------------------------------------------------------
1608 PROCEDURE IsMissingShortpay(
1609 p_item_type IN VARCHAR2,
1610 p_item_key IN VARCHAR2,
1611 p_actid IN NUMBER,
1612 p_funmode IN VARCHAR2,
1613 p_result OUT NOCOPY VARCHAR2) IS
1614 ------------------------------------------------------------------------
1615 l_debug_info VARCHAR2(200);
1616
1617 l_is_shortpay VARCHAR2(1) := 'N';
1618
1619 BEGIN
1620
1621 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsMissingShortpay');
1622
1623 IF (p_funmode = 'RUN') THEN
1624
1625 ------------------------------------------------------------
1626 l_debug_info := 'Check if Missing Receipts Shortpay';
1627 ------------------------------------------------------------
1628 l_is_shortpay := IsShortpay(p_item_type,
1629 p_item_key,
1630 C_NO_RECEIPTS_SHORTPAY_PROCESS);
1631
1632 p_result := 'COMPLETE:'||l_is_shortpay;
1633
1634 END IF; -- p_funmode = 'RUN'
1635
1636 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsMissingShortpay');
1637
1638 EXCEPTION
1639 WHEN NO_DATA_FOUND THEN
1640 p_result := 'COMPLETE:N';
1641 WHEN OTHERS THEN
1642 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsMissingShortpay',
1643 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1644 raise;
1645 END IsMissingShortpay;
1646
1647
1648 ------------------------------------------------------------------------
1649 PROCEDURE IsPolicyShortpay(
1650 p_item_type IN VARCHAR2,
1651 p_item_key IN VARCHAR2,
1652 p_actid IN NUMBER,
1653 p_funmode IN VARCHAR2,
1654 p_result OUT NOCOPY VARCHAR2) IS
1655 ------------------------------------------------------------------------
1656 l_debug_info VARCHAR2(200);
1657
1658 l_is_shortpay VARCHAR2(1) := 'N';
1659
1660 BEGIN
1661
1662 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start IsPolicyShortpay');
1663
1664 IF (p_funmode = 'RUN') THEN
1665
1666 ------------------------------------------------------------
1667 l_debug_info := 'Check if Policy Violation Shortpay';
1668 ------------------------------------------------------------
1669 l_is_shortpay := IsShortpay(p_item_type,
1670 p_item_key,
1671 C_POLICY_VIOLATION_PROCESS);
1672
1673 p_result := 'COMPLETE:'||l_is_shortpay;
1674
1675 END IF; -- p_funmode = 'RUN'
1676
1677 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end IsPolicyShortpay');
1678
1679 EXCEPTION
1680 WHEN NO_DATA_FOUND THEN
1681 p_result := 'COMPLETE:N';
1682 WHEN OTHERS THEN
1683 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'IsPolicyShortpay',
1684 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1685 raise;
1686 END IsPolicyShortpay;
1687
1688
1689 ------------------------------------------------------------------------
1690 PROCEDURE CompleteShortpay(
1691 p_item_type IN VARCHAR2,
1692 p_item_key IN VARCHAR2,
1693 p_activity IN VARCHAR2,
1694 p_result IN VARCHAR2) IS
1695 ------------------------------------------------------------------------
1696 l_debug_info VARCHAR2(200);
1697
1698 l_report_header_id number;
1699
1700 BEGIN
1701
1702 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CompleteShortpay');
1703
1704 ------------------------------------------------------------
1705 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1706 ------------------------------------------------------------
1707 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1708 p_item_key,
1709 'EXPENSE_REPORT_ID');
1710
1711 ----------------------------------------------------------
1712 l_debug_info := 'Complete Missing or Policy Shortpay Process';
1713 ----------------------------------------------------------
1714 begin
1715
1716 WF_ENGINE.CompleteActivityInternalName(C_APEXP,
1717 l_report_header_id,
1718 p_activity,
1719 p_result);
1720
1721 exception
1722 when others then null;
1723 end;
1724
1725 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CompleteShortpay');
1726
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CompleteShortpay',
1730 p_item_type, p_item_key, p_activity, p_result, l_debug_info);
1731 raise;
1732 END CompleteShortpay;
1733
1734
1735 ------------------------------------------------------------------------
1736 PROCEDURE CompleteMissingShortpay(
1737 p_item_type IN VARCHAR2,
1738 p_item_key IN VARCHAR2,
1739 p_actid IN NUMBER,
1740 p_funmode IN VARCHAR2,
1741 p_result OUT NOCOPY VARCHAR2) IS
1742 ------------------------------------------------------------------------
1743 l_debug_info VARCHAR2(200);
1744
1745 BEGIN
1746
1747 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CompleteMissingShortpay');
1748
1749 IF (p_funmode = 'RUN') THEN
1750
1751 ----------------------------------------------------------
1752 l_debug_info := 'Complete Missing Shortpay Process';
1753 ----------------------------------------------------------
1754 begin
1755
1756 CompleteShortpay(p_item_type,
1757 p_item_key,
1758 C_INFORM_PREPARER_SHORTPAY,
1759 C_AP_WILL_SUBMIT);
1760
1761 exception
1762 when others then null;
1763 end;
1764
1765 end if;
1766
1767 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CompleteMissingShortpay');
1768
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CompleteMissingShortpay',
1772 p_item_type, p_item_key, to_char(p_actid), p_result, l_debug_info);
1773 raise;
1774 END CompleteMissingShortpay;
1775
1776
1777 ------------------------------------------------------------------------
1778 PROCEDURE CompletePolicyShortpay(
1779 p_item_type IN VARCHAR2,
1780 p_item_key IN VARCHAR2,
1781 p_actid IN NUMBER,
1782 p_funmode IN VARCHAR2,
1783 p_result OUT NOCOPY VARCHAR2) IS
1784 ------------------------------------------------------------------------
1785 l_debug_info VARCHAR2(200);
1786
1787 BEGIN
1788
1789 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CompletePolicyShortpay');
1790
1791 IF (p_funmode = 'RUN') THEN
1792
1793 ----------------------------------------------------------
1794 l_debug_info := 'Complete Policy Shortpay Process';
1795 ----------------------------------------------------------
1796 begin
1797
1798 CompleteShortpay(p_item_type,
1799 p_item_key,
1800 C_POLICY_SHORTPAY_NOTICE,
1801 C_AP_PROVIDE_MISSING_INFO);
1802
1803 exception
1804 when others then null;
1805 end;
1806
1807 end if;
1808
1809 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CompletePolicyShortpay');
1810
1811 EXCEPTION
1812 WHEN OTHERS THEN
1813 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CompletePolicyShortpay',
1814 p_item_type, p_item_key, to_char(p_actid), p_result, l_debug_info);
1815 raise;
1816 END CompletePolicyShortpay;
1817
1818
1819 /*
1820 Written by:
1821 Ron Langi
1822 Purpose:
1823 This stores a Preparer-Auditor note based on the Preparer
1824 action/response from a notification activity.
1825
1826 The following is gathered from the WF:
1827 - RESULT_TYPE contains the lookup type for the result of the Notification.
1828 - RESULT_CODE contains the lookup code for the result of the Notification.
1829 - RESPONSE contains the respond attr for the Notification.
1830 - FND_MESSAGE contains the specific FND message to store
1831
1832 The Preparer-Auditor note is stored in the form of:
1833 <Preparer Action>: <Preparer Response>
1834 */
1835 ----------------------------------------------------------------------
1836 PROCEDURE StoreNote(
1837 p_item_type IN VARCHAR2,
1838 p_item_key IN VARCHAR2,
1839 p_actid IN NUMBER,
1840 p_funmode IN VARCHAR2,
1841 p_result OUT NOCOPY VARCHAR2) IS
1842 ----------------------------------------------------------------------
1843 l_report_header_id number;
1844 l_debug_info VARCHAR2(200);
1845
1846 l_fnd_message fnd_new_messages.message_name%type;
1847 l_note_text varchar2(2000);
1848 l_days_overdue number;
1849
1850 l_message_name fnd_new_messages.message_name%type;
1851 l_result_type Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
1852 l_result_code Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
1853 l_response Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
1854 l_type_display_name varchar2(240);
1855 l_code_display_name varchar2(240);
1856 l_note_prefix varchar2(2000);
1857
1858 l_orig_language_code ap_expense_params.note_language_code%type := null;
1859 l_orig_language fnd_languages.nls_language%type := null;
1860 l_new_language_code ap_expense_params.note_language_code%type := null;
1861 l_new_language fnd_languages.nls_language%type := null;
1862
1863 l_created_by number;
1864 l_preparer_id number;
1865
1866 l_org_id ap_expense_params_all.org_id%type;
1867 BEGIN
1868
1869 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start StoreNote');
1870
1871 IF (p_funmode = 'RUN') THEN
1872
1873 -------------------------------------------------------------------
1874 l_debug_info := 'Need to generate Note based on language setup';
1875 -------------------------------------------------------------------
1876
1877 -------------------------------------------------------------------
1878 l_debug_info := 'Save original language';
1879 -------------------------------------------------------------------
1880 l_orig_language_code := userenv('LANG');
1881 select nls_language
1882 into l_orig_language
1883 from fnd_languages
1884 where language_code = l_orig_language_code;
1885
1886 -------------------------------------------------------------------
1887 l_debug_info := 'Check AP_EXPENSE_PARAMS.NOTE_LANGUAGE_CODE';
1888 -------------------------------------------------------------------
1889 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1890 p_item_key,
1891 'ORG_ID');
1892 begin
1893 select note_language_code
1894 into l_new_language_code
1895 from ap_expense_params_all
1896 where org_id = l_org_id;
1897
1898 exception
1899 when no_data_found then
1900 null;
1901 end;
1902
1903 -------------------------------------------------------------------
1904 l_debug_info := 'Else use instance base language';
1905 -------------------------------------------------------------------
1906 if (l_new_language_code is null) then
1907 select language_code
1908 into l_new_language_code
1909 from fnd_languages
1910 where installed_flag in ('B');
1911 end if;
1912
1913 -------------------------------------------------------------------
1914 l_debug_info := 'Set nls context to new language';
1915 -------------------------------------------------------------------
1916 select nls_language
1917 into l_new_language
1918 from fnd_languages
1919 where language_code = l_new_language_code;
1920
1921 fnd_global.set_nls_context(p_nls_language => l_new_language);
1922
1923 ------------------------------------------------------------
1924 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
1925 ------------------------------------------------------------
1926 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1927 p_item_key,
1928 'EXPENSE_REPORT_ID');
1929
1930 -------------------------------------------------------------------
1931 l_debug_info := 'Retrieve Activity Attr Result Type';
1932 -------------------------------------------------------------------
1933 l_result_type := WF_ENGINE.GetActivityAttrText(p_item_type,
1934 p_item_key,
1935 p_actid,
1936 'RESULT_TYPE');
1937 -- bug 6361555
1938 begin
1939 select created_by
1940 into l_created_by
1941 from ap_expense_report_headers_all
1942 where report_header_id = l_report_header_id;
1943 exception
1944 when others then
1945 null;
1946 end;
1947
1948 ----------------------------------------------------------
1949 l_debug_info := 'Get Preparer Id using Created By';
1950 ----------------------------------------------------------
1951 if (AP_WEB_DB_HR_INT_PKG.GetEmpIdForUser(l_created_by, l_preparer_id)) then
1952 null;
1953 end if;
1954
1955 if (l_result_type is not null) then
1956
1957 -------------------------------------------------------------------
1958 l_debug_info := 'Retrieve Note prefix';
1959 -------------------------------------------------------------------
1960 l_message_name := 'OIE_NOTES_PREPARER_RESPONSE';
1961
1962 begin
1963 -------------------------------------------------------------------
1964 -- fnd_global.set_nls_context() seems to work for WF but not FND_MESSAGES
1965 -------------------------------------------------------------------
1966 select message_text
1967 into l_note_prefix
1968 from fnd_new_messages
1969 where application_id = 200
1970 and message_name = l_message_name
1971 and language_code = l_new_language_code;
1972
1973 exception
1974 when no_data_found then
1975 FND_MESSAGE.SET_NAME('SQLAP', l_message_name);
1976 l_note_prefix := FND_MESSAGE.GET;
1977 end;
1978
1979 -------------------------------------------------------------------
1980 l_debug_info := 'Retrieve Activity Attr Result Code';
1981 -------------------------------------------------------------------
1982 l_result_code := WF_ENGINE.GetActivityAttrText(p_item_type,
1983 p_item_key,
1984 p_actid,
1985 'RESULT_CODE');
1986
1987 -------------------------------------------------------------------
1988 l_debug_info := 'Retrieve Activity Attr Response';
1989 -------------------------------------------------------------------
1990 l_response := WF_ENGINE.GetActivityAttrText(p_item_type,
1991 p_item_key,
1992 p_actid,
1993 'RESPONSE');
1994
1995 ------------------------------------------------------------
1996 l_debug_info := 'Retrieve lookup display name';
1997 ------------------------------------------------------------
1998 WF_LOOKUP_TYPES_PUB.fetch_lookup_display(l_result_type,
1999 l_result_code,
2000 l_type_display_name,
2001 l_code_display_name);
2002
2003 ------------------------------------------------------------
2004 l_debug_info := 'store the result and response as a note';
2005 ------------------------------------------------------------
2006 AP_WEB_NOTES_PKG.CreateERPrepToAudNote (
2007 p_report_header_id => l_report_header_id,
2008 p_note => l_note_prefix||' '||l_code_display_name||'
2009 '||l_response,
2010 p_lang => l_new_language_code,
2011 p_entered_by => nvl(l_preparer_id,fnd_global.user_id) -- bug 6361555
2012 );
2013
2014 ----------------------------------------------------------
2015 l_debug_info := 'clear Item Attribute PREPARER_RESPONSE';
2016 -- this assumes preparer response, if we need to change this
2017 -- later then change Activity Attr RESPONSE to pass item attr
2018 ---------------------------------------------------------
2019 WF_ENGINE.SetItemAttrText(p_item_type,
2020 p_item_key,
2021 'PREPARER_RESPONSE',
2022 '');
2023
2024 else
2025
2026 -------------------------------------------------------------------
2027 l_debug_info := 'Retrieve Activity Attr FND Message';
2028 -------------------------------------------------------------------
2029 l_fnd_message := WF_ENGINE.GetActivityAttrText(p_item_type,
2030 p_item_key,
2031 p_actid,
2032 'FND_MESSAGE');
2033
2034 if (l_fnd_message in ('APWRECPT_OVERDUE_SENT','APWRECPT_MISSING_SENT')) then
2035
2036 l_days_overdue := WF_ENGINE.GetItemAttrNumber(p_item_type,
2037 p_item_key,
2038 'DAYS_OVERDUE');
2039
2040 FND_MESSAGE.SET_NAME('SQLAP', l_fnd_message);
2041 FND_MESSAGE.SET_TOKEN('DAYS_OVERDUE', to_char(l_days_overdue));
2042 l_note_text := FND_MESSAGE.GET;
2043
2044 ------------------------------------------------------------
2045 l_debug_info := 'store the fnd message as a note';
2046 ------------------------------------------------------------
2047 AP_WEB_NOTES_PKG.CreateERPrepToAudNote (
2048 p_report_header_id => l_report_header_id,
2049 p_note => l_note_text,
2050 p_lang => l_new_language_code
2051 );
2052
2053 end if; -- l_fnd_message is not null
2054
2055 end if; -- l_result_type is not null
2056
2057 -------------------------------------------------------------------
2058 l_debug_info := 'Restore nls context to original language';
2059 -------------------------------------------------------------------
2060 fnd_global.set_nls_context(p_nls_language => l_orig_language);
2061
2062 p_result := 'COMPLETE:Y';
2063
2064 END IF;
2065
2066 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end StoreNote');
2067
2068 EXCEPTION
2069 WHEN OTHERS THEN
2070 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'StoreNote',
2071 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
2072 raise;
2073 END StoreNote;
2074
2075
2076 ----------------------------------------------------------------------
2077 PROCEDURE CallbackFunction( p_item_type IN VARCHAR2,
2078 p_item_key IN VARCHAR2,
2079 p_actid IN NUMBER,
2080 p_funmode IN VARCHAR2,
2081 p_result OUT NOCOPY VARCHAR2) IS
2082 ----------------------------------------------------------------------
2083 l_debug_info VARCHAR2(200);
2084
2085 l_org_id number;
2086 l_expense_report_id number;
2087
2088 BEGIN
2089
2090 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'start CallbackFunction');
2091
2092 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2093 p_item_key,
2094 'ORG_ID');
2095
2096 if (l_org_id is null) then
2097 -- EXPENSE_REPORT_ID item attribute should exist
2098 l_expense_report_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2099 p_item_key,
2100 'EXPENSE_REPORT_ID');
2101
2102 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
2103 l_org_id := NULL;
2104 END IF;
2105
2106 WF_ENGINE.SetItemAttrNumber(p_item_type,
2107 p_item_key,
2108 'ORG_ID',
2109 l_org_id);
2110 end if;
2111
2112 /*
2113 if ( p_funmode = 'RUN' ) then
2114 --<your RUN executable statements>
2115
2116 p_result := 'TRUE';
2117
2118 return;
2119 end if;
2120 */
2121
2122 if ( p_funmode = 'SET_CTX' ) then
2123 --<your executable statements for establishing context information>
2124
2125 if (l_org_id is not null) then
2126 mo_global.set_policy_context(p_access_mode => 'S',
2127 p_org_id => l_org_id);
2128 end if;
2129
2130 p_result := 'TRUE';
2131
2132 return;
2133 end if;
2134
2135 if ( p_funmode = 'TEST_CTX' and l_org_id is not null) then
2136 --<your executable statements for testing the validity of the current context information>
2137
2138 IF ((nvl(mo_global.get_access_mode, 'NULL') <> 'S') OR
2139 (nvl(mo_global.get_current_org_id, -99) <> nvl(l_org_id, -99)) ) THEN
2140 p_result := 'FALSE';
2141 ELSE
2142 p_result := 'TRUE';
2143 END IF;
2144
2145 return;
2146 end if;
2147
2148 /*
2149 if ( p_funmode = '<other command>' ) then
2150 p_result := ' ';
2151
2152 return;
2153 end if;
2154 */
2155
2156 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_RECEIPTS_WF', 'end CallbackFunction');
2157
2158 EXCEPTION
2159 WHEN OTHERS THEN
2160 Wf_Core.Context('AP_WEB_RECEIPTS_WF', 'CallbackFunction',
2161 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
2162 raise;
2163 END CallbackFunction;
2164
2165
2166 /*
2167 Written by:
2168 Ron Langi
2169
2170 Purpose:
2171 Tracks Overdue Receipt Packages
2172
2173 Input:
2174 p_org_id - Org Id (optional)
2175
2176 Output:
2177 errbuf - contains error message; required by Concurrent Manager
2178 retcode - contains return code; required by Concurrent Manager
2179
2180 Input/Output:
2181
2182 Assumption:
2183
2184 */
2185 ----------------------------------------------------------------------
2186 PROCEDURE TrackOverdue(
2187 errbuf out nocopy varchar2,
2188 retcode out nocopy number,
2189 p_org_id in number) IS
2190 ----------------------------------------------------------------------
2191
2192 l_debug_info VARCHAR2(200);
2193
2194 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
2195 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
2196 l_orig_receipts_status ap_expense_report_headers.receipts_status%TYPE;
2197
2198 l_employee_id ap_expense_report_headers.employee_id%TYPE;
2199 l_business_group_id hr_organization_units.business_group_id%TYPE;
2200 l_duration AP_AUD_RULE_SETS.audit_term_duration_days%TYPE;
2201 l_emp_rec AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type;
2202 l_audit_rec AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type;
2203 l_auto_audit_id NUMBER;
2204 l_x_return_status VARCHAR2(1);
2205 l_x_msg_count NUMBER;
2206 l_x_msg_data VARCHAR2(2000);
2207
2208
2209
2210 /*
2211 Criteria for this cursor is:
2212 - receipts status is REQUIRED or MISSING
2213 - original reports (excludes bothpay child reports)
2214 - reports that have effective notification rules
2215 - no Overdue/Missing Receipts WF exists
2216 - REQUIRED receipts are overdue
2217 or
2218 MISSING receipts are overdue and phys doc is reqd
2219 */
2220 -- cursor for overdue required/missing receipts
2221 CURSOR c_overdue_receipts IS
2222 select aerh.report_header_id,
2223 aerh.receipts_status
2224 from AP_EXPENSE_REPORT_HEADERS aerh,
2225 AP_AUD_RULE_SETS rs,
2226 AP_AUD_RULE_ASSIGNMENTS rsa
2227 where aerh.receipts_status in (C_REQUIRED, C_MISSING)
2228 and aerh.bothpay_parent_id is null
2229 and rsa.org_id = nvl(p_org_id, rsa.org_id)
2230 and rsa.org_id = aerh.org_id
2231 and rsa.rule_set_id = rs.rule_set_id
2232 and rs.rule_set_type = C_NOTIFY_RULE
2233 and TRUNC(aerh.report_submitted_date)
2234 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
2235 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
2236 and
2237 (
2238 (aerh.RECEIPTS_STATUS = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
2239 or
2240 (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)
2241 )
2242 and not exists
2243 (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)
2244 and not exists
2245 (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);
2246
2247
2248 /*
2249 Criteria for this cursor is:
2250 - list employee's with late receipts who are not on the audit list yet
2251 - late receipts are required and overdue
2252 */
2253 -- cursor for employees to be audited
2254 CURSOR c_audit_list_receipts IS
2255 select aerh.employee_id,
2256 hr.business_group_id,
2257 max(rs.audit_term_duration_days)
2258 from AP_EXPENSE_REPORT_HEADERS aerh,
2259 hr_organization_units hr,
2260 AP_AUD_RULE_SETS rs,
2261 AP_AUD_RULE_ASSIGNMENTS rsa
2262 where aerh.org_id = nvl(p_org_id, aerh.org_id)
2263 and aerh.bothpay_parent_id is null
2264 and aerh.report_submitted_date is not null
2265 and hr.organization_id = aerh.org_id
2266 and rsa.org_id = aerh.org_id
2267 and rsa.rule_set_id = rs.rule_set_id
2268 and rs.rule_set_type = 'AUDIT_LIST'
2269 and rs.receipt_delay_rule_flag = 'Y'
2270 and TRUNC(aerh.report_submitted_date)
2271 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
2272 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
2273 and aerh.receipts_status in ('REQUIRED', 'MISSING', 'OVERDUE', 'IN_TRANSIT', 'RESOLUTN')
2274 and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.receipt_delay_days) > 0
2275 group by employee_id, business_group_id;
2276
2277
2278
2279
2280 BEGIN
2281
2282 fnd_file.put_line(fnd_file.log, 'p_org_id = '|| p_org_id);
2283
2284 ------------------------------------------------------------
2285 l_debug_info := 'Process Overdue/Missing Receipts';
2286 ------------------------------------------------------------
2287 fnd_file.put_line(fnd_file.log, l_debug_info);
2288
2289 open c_overdue_receipts;
2290 loop
2291
2292 fetch c_overdue_receipts into l_report_header_id, l_receipts_status;
2293 exit when c_overdue_receipts%NOTFOUND;
2294
2295 if (l_receipts_status = C_REQUIRED) then
2296
2297 ------------------------------------------------------------
2298 l_debug_info := 'Update current Receipt Status';
2299 ------------------------------------------------------------
2300 SetReceiptsStatus(l_report_header_id, C_OVERDUE);
2301
2302 ------------------------------------------------------------
2303 l_debug_info := 'Raise Overdue Event: '||l_report_header_id;
2304 ------------------------------------------------------------
2305 fnd_file.put_line(fnd_file.log, l_debug_info);
2306 RaiseOverdueEvent(l_report_header_id);
2307
2308 elsif (l_receipts_status = C_MISSING) then
2309
2310 ------------------------------------------------------------
2311 l_debug_info := 'Raise Missing Event: '||l_report_header_id;
2312 ------------------------------------------------------------
2313 fnd_file.put_line(fnd_file.log, l_debug_info);
2314 RaiseMissingEvent(l_report_header_id);
2315
2316 end if;
2317
2318 end loop;
2319 close c_overdue_receipts;
2320
2321 ------------------------------------------------------------
2322 l_debug_info := 'Commit Events for Overdue/Missing Receipts';
2323 ------------------------------------------------------------
2324 fnd_file.put_line(fnd_file.log, l_debug_info);
2325 COMMIT;
2326
2327 ------------------------------------------------------------
2328 l_debug_info := 'Audit Overdue/Missing Receipts';
2329 ------------------------------------------------------------
2330 fnd_file.put_line(fnd_file.log, l_debug_info);
2331
2332 open c_audit_list_receipts;
2333 loop
2334
2335 fetch c_audit_list_receipts into l_employee_id, l_business_group_id, l_duration;
2336 exit when c_audit_list_receipts%NOTFOUND;
2337
2338 ------------------------------------------------------------
2339 l_debug_info := 'Adding to Audit List employee: '||l_employee_id||' business group id: '||l_business_group_id||' duration: '||l_duration;
2340 ------------------------------------------------------------
2341 fnd_file.put_line(fnd_file.log, l_debug_info);
2342
2343 l_emp_rec.business_group_id := l_business_group_id;
2344 l_emp_rec.person_id := l_employee_id;
2345 l_audit_rec.audit_reason_code := 'RECEIPTS_LATE';
2346 l_audit_rec.start_date := sysdate;
2347 l_audit_rec.end_date := sysdate + l_duration;
2348
2349 AP_WEB_AUDIT_LIST_PUB.Audit_Employee(1.0,
2350 FND_API.G_FALSE, --p_init_msg_list
2351 FND_API.G_FALSE, --p_commit
2352 FND_API.G_VALID_LEVEL_FULL, --p_validation_level
2353 l_x_return_status,
2354 l_x_msg_count,
2355 l_x_msg_data,
2356 l_emp_rec,
2357 l_audit_rec,
2358 l_auto_audit_id);
2359
2360 end loop;
2361 close c_audit_list_receipts;
2362
2363 ------------------------------------------------------------
2364 l_debug_info := 'Commit Audit for Overdue/Missing Receipts';
2365 ------------------------------------------------------------
2366 fnd_file.put_line(fnd_file.log, l_debug_info);
2367 COMMIT;
2368
2369 EXCEPTION
2370 WHEN OTHERS THEN
2371 raise;
2372
2373 END TrackOverdue;
2374
2375
2376 END AP_WEB_RECEIPTS_WF;