[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_HOLDS_WF
Source
1 PACKAGE BODY AP_WEB_HOLDS_WF AS
2 /* $Header: apwholdsb.pls 120.4 2005/11/18 01:17:48 rlangi noship $ */
3
4 ------------------------
5 -- Events
6 ------------------------
7 -- Event key is used for item key and result code when aborting the track process
8 -- Event name is the true event name
9 -- Item/Event key is in the form of: '<Expense Report Id>:<Event key>:<DD-MON-RRRR HH:MI:SS>'
10 C_HELD_EVENT_KEY CONSTANT VARCHAR2(30) := 'held';
11 C_HELD_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.held';
12 C_RELEASED_EVENT_KEY CONSTANT VARCHAR2(30) := 'released';
13 C_RELEASED_EVENT_NAME CONSTANT VARCHAR2(80) := 'oracle.apps.ap.oie.expenseReport.released';
14
15 -- Item Key Delimeter
16 C_ITEM_KEY_DELIM CONSTANT VARCHAR2(1) := ':';
17
18
19 ------------------------------------------------------------------------
20 FUNCTION ParseItemKey(
21 p_item_type IN VARCHAR2,
22 p_item_key IN VARCHAR2) RETURN VARCHAR2 IS
23 ------------------------------------------------------------------------
24 l_debug_info VARCHAR2(200);
25
26 l_item_key wf_items.item_key%TYPE;
27
28 BEGIN
29
30 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start ParseItemKey');
31
32 ----------------------------------------------------------
33 l_debug_info := 'Parse the item key for the Expense Report Id';
34 ----------------------------------------------------------
35 return substrb(p_item_key, 1, instrb(p_item_key, C_ITEM_KEY_DELIM)-1);
36
37 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end ParseItemKey');
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 Wf_Core.Context('AP_WEB_HOLDS_WF', 'ParseItemKey',
42 p_item_type, p_item_key, l_debug_info);
43 raise;
44 END ParseItemKey;
45
46
47 ------------------------------------------------------------------------
48 FUNCTION IsHoldsRuleSetup(
49 p_org_id IN NUMBER,
50 p_report_submitted_date IN DATE) RETURN VARCHAR2 IS
51 ------------------------------------------------------------------------
52 l_debug_info VARCHAR2(200);
53
54 l_is_holds_rule_setup VARCHAR2(1);
55
56 BEGIN
57
58 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start IsHoldsRuleSetup');
59
60 ------------------------------------------------------------
61 l_debug_info := 'Check if Holds Rule Setup';
62 ------------------------------------------------------------
63 select 'Y'
64 into l_is_holds_rule_setup
65 from AP_AUD_RULE_SETS rs,
66 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
67 where rsa.org_id = p_org_id
68 and rsa.rule_set_id = rs.rule_set_id
69 and rs.rule_set_type = C_HOLD_RULE
70 and TRUNC(p_report_submitted_date)
71 BETWEEN TRUNC(NVL(rsa.START_DATE, p_report_submitted_date))
72 AND TRUNC(NVL(rsa.END_DATE, p_report_submitted_date))
73 and rownum = 1;
74
75 return l_is_holds_rule_setup;
76
77 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end IsHoldsRuleSetup');
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN
81 return 'N';
82 WHEN OTHERS THEN
83 Wf_Core.Context('AP_WEB_HOLDS_WF', 'IsHoldsRuleSetup',
84 to_char(p_org_id), to_char(p_report_submitted_date), l_debug_info);
85 raise;
86 END IsHoldsRuleSetup;
87
88
89 ------------------------------------------------------------------------
90 PROCEDURE IsHoldsRuleSetup(
91 p_item_type IN VARCHAR2,
92 p_item_key IN VARCHAR2,
93 p_actid IN NUMBER,
94 p_funmode IN VARCHAR2,
95 p_result OUT NOCOPY VARCHAR2) IS
96 ------------------------------------------------------------------------
97 l_debug_info VARCHAR2(200);
98
99 l_org_id number;
100 l_expense_report_id number;
101 l_report_submitted_date AP_EXPENSE_REPORT_HEADERS.report_submitted_date%type;
102
103 l_is_notif_rule_setup VARCHAR2(1);
104
105 BEGIN
106
107 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start IsHoldsRuleSetup');
108
109 IF (p_funmode = 'RUN') THEN
110
111 ----------------------------------------------------------
112 l_debug_info := 'Parse the item key for the Expense Report Id';
113 ----------------------------------------------------------
114 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
115
116 if (l_expense_report_id is null) then
117 Wf_Core.Raise('InvalidExpenseReportId');
118 end if;
119
120 ----------------------------------------------------------
121 l_debug_info := 'Get the Expense Report Org Id';
122 ----------------------------------------------------------
123 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
124 l_org_id := NULL;
125 END IF;
126
127 ----------------------------------------------------------
128 l_debug_info := 'Get Expense Report data';
129 ----------------------------------------------------------
130 select report_submitted_date
131 into l_report_submitted_date
132 from ap_expense_report_headers
133 where report_header_id = l_expense_report_id;
134
135 ------------------------------------------------------------
136 l_debug_info := 'Check if Holds Rule Setup';
137 ------------------------------------------------------------
138 l_is_notif_rule_setup := IsHoldsRuleSetup(l_org_id, l_report_submitted_date);
139
140 p_result := 'COMPLETE:'||l_is_notif_rule_setup;
141
142 ELSIF (p_funmode = 'CANCEL') THEN
143
144 p_result := 'COMPLETE';
145
146 END IF; -- p_funmode = 'RUN'
147
148 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end IsHoldsRuleSetup');
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 Wf_Core.Context('AP_WEB_HOLDS_WF', 'IsHoldsRuleSetup',
153 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
154 raise;
155 END IsHoldsRuleSetup;
156
157
158 ------------------------------------------------------------------------
159 FUNCTION GenerateEventKey(
160 p_expense_report_id IN NUMBER,
161 p_event_key IN VARCHAR2) RETURN VARCHAR2 IS
162 ------------------------------------------------------------------------
163
164 l_timestamp varchar2(30);
165
166 BEGIN
167
168 select to_char(sysdate, 'DD-MON-RRRR HH:MI:SS')
169 into l_timestamp
170 from dual;
171
172 return p_expense_report_id||C_ITEM_KEY_DELIM||p_event_key||C_ITEM_KEY_DELIM||l_timestamp;
173
174 END GenerateEventKey;
175
176
177 ------------------------------------------------------------------------
178 FUNCTION EventKeyExists(
179 p_event_key IN VARCHAR2) RETURN BOOLEAN IS
180 ------------------------------------------------------------------------
181
182 l_event_key_exists varchar2(1) := 'N';
183
184 BEGIN
185
186 select 'Y'
187 into l_event_key_exists
188 from wf_items
189 where item_type = C_APWHOLDS
190 and item_key = p_event_key
191 and rownum = 1;
192
193 return true;
194
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 return false;
198
199 END EventKeyExists;
200
201
202 ------------------------------------------------------------------------
203 PROCEDURE RaiseHeldEvent(
204 p_expense_report_id IN NUMBER) IS
205 ------------------------------------------------------------------------
206 l_debug_info VARCHAR2(200);
207
208 l_event_key wf_items.item_key%type;
209
210 BEGIN
211
212 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start RaiseHeldEvent');
213
214 ----------------------------------------------------------
215 l_debug_info := 'Generate Event Key';
216 ----------------------------------------------------------
217 l_event_key := GenerateEventKey(p_expense_report_id, C_HELD_EVENT_KEY);
218
219 ----------------------------------------------------------
220 l_debug_info := 'Check Event Key';
221 ----------------------------------------------------------
222 if (NOT EventKeyExists(l_event_key)) then
223
224 ----------------------------------------------------------
225 l_debug_info := 'Raise Held Event';
226 ----------------------------------------------------------
227 wf_event.raise(p_event_name => C_HELD_EVENT_NAME,
228 p_event_key => l_event_key);
229 --p_parameters => l_parameter_list);
230
231 end if;
232
233 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end RaiseHeldEvent');
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 Wf_Core.Context('AP_WEB_HOLDS_WF', 'RaiseHeldEvent',
238 p_expense_report_id, l_debug_info);
239 raise;
240 END RaiseHeldEvent;
241
242
243 ------------------------------------------------------------------------
244 PROCEDURE RaiseReleasedEvent(
245 p_expense_report_id IN NUMBER) IS
246 ------------------------------------------------------------------------
247 l_debug_info VARCHAR2(200);
248
249 l_event_key wf_items.item_key%type;
250
251 BEGIN
252
253 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start RaiseReleasedEvent');
254
255 ----------------------------------------------------------
256 l_debug_info := 'Generate Event Key';
257 ----------------------------------------------------------
258 l_event_key := GenerateEventKey(p_expense_report_id, C_RELEASED_EVENT_KEY);
259
260 ----------------------------------------------------------
261 l_debug_info := 'Check Event Key';
262 ----------------------------------------------------------
263 if (NOT EventKeyExists(l_event_key)) then
264
265 ----------------------------------------------------------
266 l_debug_info := 'Raise Released Event';
267 ----------------------------------------------------------
268 wf_event.raise(p_event_name => C_RELEASED_EVENT_NAME,
269 p_event_key => l_event_key);
270 --p_parameters => l_parameter_list);
271
272 end if;
273
274 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end RaiseReleasedEvent');
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 Wf_Core.Context('AP_WEB_HOLDS_WF', 'RaiseReleasedEvent',
279 p_expense_report_id, l_debug_info);
280 raise;
281 END RaiseReleasedEvent;
282
283
284 ------------------------------------------------------------------------
285 PROCEDURE RaiseReleasedEvent(
286 p_item_type IN VARCHAR2,
287 p_item_key IN VARCHAR2,
288 p_actid IN NUMBER,
289 p_funmode IN VARCHAR2,
290 p_result OUT NOCOPY VARCHAR2) IS
291 ------------------------------------------------------------------------
292 l_debug_info VARCHAR2(200);
293
294 BEGIN
295
296 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start RaiseReleasedEvent');
297
298 IF (p_funmode = 'RUN') THEN
299
300 ----------------------------------------------------------
301 l_debug_info := 'Raise Released event';
302 ----------------------------------------------------------
303 RaiseReleasedEvent(WF_ENGINE.GetItemAttrNumber(p_item_type,
304 p_item_key,
305 'EXPENSE_REPORT_ID'));
306
307 p_result := 'COMPLETE';
308
309 END IF; -- p_funmode = 'RUN'
310
311 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end RaiseReleasedEvent');
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 Wf_Core.Context('AP_WEB_HOLDS_WF', 'RaiseReleasedEvent',
316 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
317 raise;
318 END RaiseReleasedEvent;
319
320
321 ------------------------------------------------------------------------
322 PROCEDURE Init(
323 p_item_type IN VARCHAR2,
324 p_item_key IN VARCHAR2) IS
325 ------------------------------------------------------------------------
326 l_debug_info VARCHAR2(200);
327
328 l_textNameArr Wf_Engine.NameTabTyp;
329 l_numNameArr Wf_Engine.NameTabTyp;
330 l_textValArr Wf_Engine.TextTabTyp;
331 l_numValArr Wf_Engine.NumTabTyp;
332 iText NUMBER :=0;
333 iNum NUMBER :=0;
334
335 l_org_id number;
336 l_expense_report_id number;
337 l_created_by number;
338 l_preparer_id number;
339 l_preparer_name wf_users.name%type;
340 l_preparer_display_name wf_users.display_name%type;
341 l_employee_id number;
342 l_employee_name wf_users.name%type;
343 l_employee_display_name wf_users.display_name%type;
344 l_invoice_num AP_EXPENSE_REPORT_HEADERS.invoice_num%type;
345 l_cost_center AP_EXPENSE_REPORT_HEADERS.flex_concatenated%type;
346 l_total varchar2(80);
347 l_purpose AP_EXPENSE_REPORT_HEADERS.description%type;
348 l_report_submitted_date AP_EXPENSE_REPORT_HEADERS.report_submitted_date%type;
349 l_holding_report_header_id AP_EXPENSE_REPORT_HEADERS.holding_report_header_id%type;
350 l_holding_invoice_num AP_EXPENSE_REPORT_HEADERS.invoice_num%type;
351
352 BEGIN
353
354 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start Init');
355
356 ----------------------------------------------------------
357 l_debug_info := 'Parse the item key for the Expense Report Id';
358 ----------------------------------------------------------
359 l_expense_report_id := ParseItemKey(p_item_type, p_item_key);
360
361 if (l_expense_report_id is null) then
362 Wf_Core.Raise('InvalidExpenseReportId');
363 end if;
364
365 ----------------------------------------------------------
366 l_debug_info := 'Get the Expense Report Org Id';
367 ----------------------------------------------------------
368 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
369 l_org_id := NULL;
370 END IF;
371
372
373 ----------------------------------------------------------
374 l_debug_info := 'Get Expense Report data';
375 -- Note: was thinking of getting data from APEXP WF but we cannot
376 -- assume that Expenses WF still exists (may be purged)
377 ----------------------------------------------------------
378 select created_by,
379 employee_id,
380 invoice_num,
381 flex_concatenated,
382 to_char(nvl(AMT_DUE_CCARD_COMPANY,0)+nvl(AMT_DUE_EMPLOYEE,0),
383 FND_CURRENCY.Get_Format_Mask(default_currency_code,22))||' '||default_currency_code,
384 description,
385 report_submitted_date,
386 holding_report_header_id
387 into l_created_by,
388 l_employee_id,
389 l_invoice_num,
390 l_cost_center,
391 l_total,
392 l_purpose,
393 l_report_submitted_date,
394 l_holding_report_header_id
395 from ap_expense_report_headers
396 where report_header_id = l_expense_report_id;
397
398 ----------------------------------------------------------
399 l_debug_info := 'Get Holding Expense Report data';
400 ----------------------------------------------------------
401 if (l_holding_report_header_id is not null) then
402 begin
403 select invoice_num
404 into l_holding_invoice_num
405 from ap_expense_report_headers
406 where report_header_id = l_holding_report_header_id;
407
408 exception
409 when no_data_found then
410 null;
411 end;
412
413 ----------------------------------------------------------
414 l_debug_info := 'Set HOLDING_EXPENSE_REPORT_ID Item Attribute';
415 ----------------------------------------------------------
416 iNum := iNum + 1;
420 --------------------------------------------------------
417 l_numNameArr(iNum) := 'HOLDING_EXPENSE_REPORT_ID';
418 l_numValArr(iNum) := l_holding_report_header_id;
419
421 l_debug_info := 'Set HOLDING_EXPENSE_REPORT Item Attribute';
422 --------------------------------------------------------
423 iText := iText + 1;
424 l_textNameArr(iText) := 'HOLDING_EXPENSE_REPORT';
425 l_textValArr(iText) := l_holding_invoice_num;
426
427 else
428
429 --------------------------------------------------------
430 l_debug_info := 'Unset HOLDING_REPORT_DETAILS_URL Item Attribute';
431 --------------------------------------------------------
432 iText := iText + 1;
433 l_textNameArr(iText) := 'HOLDING_REPORT_DETAILS_URL';
434 l_textValArr(iText) := '';
435
436
437 end if;
438
439 ----------------------------------------------------------
440 l_debug_info := 'Get Preparer Id using Created By';
441 ----------------------------------------------------------
442 if (AP_WEB_DB_HR_INT_PKG.GetEmpIdForUser(l_created_by, l_preparer_id)) then
443 null;
444 end if;
445
446 ------------------------------------------------------------
447 l_debug_info := 'Get Name Info Associated With Preparer Id';
448 ------------------------------------------------------------
449 WF_DIRECTORY.GetUserName('PER',
450 l_preparer_id,
451 l_preparer_name,
452 l_preparer_display_name);
453
454 if (l_preparer_name is null) then
455 Wf_Core.Raise('InvalidOwner');
456 end if;
457
458 ----------------------------------------------------------
459 l_debug_info := 'Set the Preparer as the Owner of Receipts Mgmt Workflow Process.';
460 ----------------------------------------------------------
461 WF_ENGINE.SetItemOwner(p_item_type, p_item_key, l_preparer_name);
462
463 ----------------------------------------------------------
464 l_debug_info := 'Set Item User Key to Invoice Number for easier query ';
465 ----------------------------------------------------------
466 WF_ENGINE.SetItemUserKey(p_item_type,
467 p_item_key,
468 l_invoice_num);
469
470 --------------------------------------------------------
471 l_debug_info := 'Set EXPENSE_REPORT Item Attribute';
472 --------------------------------------------------------
473 iText := iText + 1;
474 l_textNameArr(iText) := 'EXPENSE_REPORT';
475 l_textValArr(iText) := l_invoice_num;
476
477 ------------------------------------------------------------
478 l_debug_info := 'Get Name Info Associated With Employee_Id';
479 ------------------------------------------------------------
480 WF_DIRECTORY.GetUserName('PER',
481 l_employee_id,
482 l_employee_name,
483 l_employee_display_name);
484
485 ----------------------------------------------------------
486 l_debug_info := 'Set ORG_ID Item Attribute';
487 ----------------------------------------------------------
488 iNum := iNum + 1;
489 l_numNameArr(iNum) := 'ORG_ID';
490 l_numValArr(iNum) := l_org_id;
491
492 ----------------------------------------------------------
493 l_debug_info := 'Set EXPENSE_REPORT_ID Item Attribute';
494 ----------------------------------------------------------
495 iNum := iNum + 1;
496 l_numNameArr(iNum) := 'EXPENSE_REPORT_ID';
497 l_numValArr(iNum) := l_expense_report_id;
498
499 ----------------------------------------------------------
500 l_debug_info := 'Set EXPENSE_REPORT_FOR Item Attribute';
501 ----------------------------------------------------------
502 iText := iText + 1;
503 l_textNameArr(iText) := 'EXPENSE_REPORT_FOR';
504 l_textValArr(iText) := l_employee_display_name;
505
506 ----------------------------------------------------------
507 l_debug_info := 'Set EXPENSE_REPORT_COST_CENTER Item Attribute';
508 ----------------------------------------------------------
509 iText := iText + 1;
510 l_textNameArr(iText) := 'EXPENSE_REPORT_COST_CENTER';
511 l_textValArr(iText) := l_cost_center;
512
513 ----------------------------------------------------------
514 l_debug_info := 'Set EXPENSE_REPORT_TOTAL Item Attribute';
515 ----------------------------------------------------------
516 iText := iText + 1;
517 l_textNameArr(iText) := 'EXPENSE_REPORT_TOTAL';
518 l_textValArr(iText) := l_total;
519
520 ----------------------------------------------------------
521 l_debug_info := 'Set EXPENSE_REPORT_PURPOSE Item Attribute';
522 ----------------------------------------------------------
523 iText := iText + 1;
524 l_textNameArr(iText) := 'EXPENSE_REPORT_PURPOSE';
525 l_textValArr(iText) := l_purpose;
526
527 ----------------------------------------------------------
528 l_debug_info := 'Set EXPENSE_REPORT_SUBMIT_DATE Item Attribute';
529 ----------------------------------------------------------
530 WF_ENGINE.SetItemAttrDate(p_item_type, p_item_key, 'EXPENSE_REPORT_SUBMIT_DATE', l_report_submitted_date);
531
532 ----------------------------------------------------------
533 l_debug_info := 'Set PREPARER_ROLE Item Attribute';
534 ----------------------------------------------------------
535 iText := iText + 1;
536 l_textNameArr(iText) := 'PREPARER_ROLE';
537 l_textValArr(iText) := l_preparer_name;
538
539
540 -----------------------------------------------------
541 l_debug_info := 'Set all number item attributes';
542 -----------------------------------------------------
543 WF_ENGINE.SetItemAttrNumberArray(p_item_type, p_item_key, l_numNameArr, l_numValArr);
544
545 -----------------------------------------------------
549
546 l_debug_info := 'Set all text item attributes';
547 -----------------------------------------------------
548 WF_ENGINE.SetItemAttrTextArray(p_item_type, p_item_key, l_textNameArr, l_textValArr);
550
551 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end Init');
552
553 EXCEPTION
554 WHEN OTHERS THEN
555 Wf_Core.Context('AP_WEB_HOLDS_WF', 'Init',
556 p_item_type, p_item_key, l_expense_report_id, l_preparer_name, l_debug_info);
557 raise;
558 END Init;
559
560
561 ------------------------------------------------------------------------
562 PROCEDURE InitHeld(
563 p_item_type IN VARCHAR2,
564 p_item_key IN VARCHAR2,
565 p_actid IN NUMBER,
566 p_funmode IN VARCHAR2,
567 p_result OUT NOCOPY VARCHAR2) IS
568 ------------------------------------------------------------------------
569 l_debug_info VARCHAR2(200);
570
571 BEGIN
572
573 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start InitHeld');
574
575 IF (p_funmode = 'RUN') THEN
576
577 ----------------------------------------------------------
578 l_debug_info := 'Initialize common event data';
579 ----------------------------------------------------------
580 Init(p_item_type, p_item_key);
581
582 p_result := 'COMPLETE';
583
584 END IF; -- p_funmode = 'RUN'
585
586 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end InitHeld');
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 Wf_Core.Context('AP_WEB_HOLDS_WF', 'InitHeld',
591 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
592 raise;
593 END InitHeld;
594
595
596 ------------------------------------------------------------------------
597 PROCEDURE InitReleased(
598 p_item_type IN VARCHAR2,
599 p_item_key IN VARCHAR2,
600 p_actid IN NUMBER,
601 p_funmode IN VARCHAR2,
602 p_result OUT NOCOPY VARCHAR2) IS
603 ------------------------------------------------------------------------
604 l_debug_info VARCHAR2(200);
605
606 BEGIN
607
608 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start InitReleased');
609
610 IF (p_funmode = 'RUN') THEN
611
612 ----------------------------------------------------------
613 l_debug_info := 'Initialize common event data';
614 ----------------------------------------------------------
615 Init(p_item_type, p_item_key);
616
617 p_result := 'COMPLETE';
618
619 END IF; -- p_funmode = 'RUN'
620
621 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end InitReleased');
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 Wf_Core.Context('AP_WEB_HOLDS_WF', 'InitReleased',
626 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
627 raise;
628 END InitReleased;
629
630
631 ------------------------------------------------------------------------
632 PROCEDURE AnyHoldsPending(
633 p_item_type IN VARCHAR2,
634 p_item_key IN VARCHAR2,
635 p_actid IN NUMBER,
636 p_funmode IN VARCHAR2,
637 p_result OUT NOCOPY VARCHAR2) IS
638 ------------------------------------------------------------------------
639 l_debug_info VARCHAR2(200);
640
641 l_report_header_id number;
642 l_any_holds_pending varchar2(1) := 'N';
643
644 BEGIN
645
646 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start AnyHoldsPending');
647
648 IF (p_funmode = 'RUN') THEN
649
650 ------------------------------------------------------------
651 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
652 ------------------------------------------------------------
653 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
654 p_item_key,
655 'EXPENSE_REPORT_ID');
656
657 ----------------------------------------------------------
658 l_debug_info := 'Any Holds Pending?';
659 ----------------------------------------------------------
660 select 'Y'
661 into l_any_holds_pending
662 from ap_expense_report_headers
663 where report_header_id <> holding_report_header_id
664 and holding_report_header_id = l_report_header_id
665 and rownum = 1;
666
667 p_result := 'COMPLETE:'||l_any_holds_pending;
668
669 END IF; -- p_funmode = 'RUN'
670
671 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end AnyHoldsPending');
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 Wf_Core.Context('AP_WEB_HOLDS_WF', 'AnyHoldsPending',
676 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
677 raise;
678 END AnyHoldsPending;
679
680
681 ------------------------------------------------------------------------
682 FUNCTION GetHoldsScenario(
683 p_org_id IN NUMBER,
684 p_report_submitted_date IN DATE) RETURN VARCHAR2 IS
685 ------------------------------------------------------------------------
686 l_debug_info VARCHAR2(200);
687
688 l_is_holds_rule_setup VARCHAR2(1);
692
689 l_hold_code AP_AUD_RULE_SETS.hold_code%type;
690
691 BEGIN
693 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start GetHoldsScenario');
694
695 ------------------------------------------------------------
696 l_debug_info := 'Check if Holds Rule Setup';
697 ------------------------------------------------------------
698 select rs.hold_code
699 into l_hold_code
700 from AP_AUD_RULE_SETS rs,
701 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
702 where rsa.org_id = p_org_id
703 and rsa.rule_set_id = rs.rule_set_id
704 and rs.rule_set_type = C_HOLD_RULE
705 and TRUNC(p_report_submitted_date)
706 BETWEEN TRUNC(NVL(rsa.START_DATE, p_report_submitted_date))
707 AND TRUNC(NVL(rsa.END_DATE, p_report_submitted_date))
708 and rownum = 1;
709
710 if (l_hold_code = C_HOLD_ALL_CODE) then
711 return C_HOLD_ALL;
712 elsif (l_hold_code = C_HOLD_EACH_CODE) then
713 return C_HOLD_EACH;
714 end if;
715
716 return null;
717
718 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end GetHoldsScenario');
719
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 return null;
723 WHEN OTHERS THEN
724 Wf_Core.Context('AP_WEB_HOLDS_WF', 'GetHoldsScenario',
725 to_char(p_org_id), to_char(p_report_submitted_date), l_debug_info);
726 raise;
727 END GetHoldsScenario;
728
729
730 ------------------------------------------------------------------------
731 PROCEDURE GetHoldsScenario(
732 p_item_type IN VARCHAR2,
733 p_item_key IN VARCHAR2,
734 p_actid IN NUMBER,
735 p_funmode IN VARCHAR2,
736 p_result OUT NOCOPY VARCHAR2) IS
737 ------------------------------------------------------------------------
738 l_debug_info VARCHAR2(200);
739
740 l_org_id number;
741 l_report_submitted_date date;
742
743 l_holds_scenario AP_AUD_RULE_SETS.hold_code%type;
744
745 BEGIN
746
747 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start GetHoldsScenario');
748
749 IF (p_funmode = 'RUN') THEN
750
751 ------------------------------------------------------------
752 l_debug_info := 'Retrieve ORG_ID Item Attribute';
753 ------------------------------------------------------------
754 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
755 p_item_key,
756 'ORG_ID');
757
758 ------------------------------------------------------------
759 l_debug_info := 'Retrieve EXPENSE_REPORT_SUBMIT_DATE Item Attribute';
760 ------------------------------------------------------------
761 l_report_submitted_date := WF_ENGINE.GetItemAttrDate(p_item_type,
762 p_item_key,
763 'EXPENSE_REPORT_SUBMIT_DATE');
764
765 ----------------------------------------------------------
766 l_debug_info := 'Get Holds Scenario';
767 ----------------------------------------------------------
768 l_holds_scenario := GetHoldsScenario(l_org_id, l_report_submitted_date);
769
770 p_result := 'COMPLETE:'||l_holds_scenario;
771
772 END IF; -- p_funmode = 'RUN'
773
774 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end GetHoldsScenario');
775
776 EXCEPTION
777 WHEN OTHERS THEN
778 Wf_Core.Context('AP_WEB_HOLDS_WF', 'GetHoldsScenario',
779 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
780 raise;
781 END GetHoldsScenario;
782
783
784 ------------------------------------------------------------------------
785 PROCEDURE ReleaseHold(
786 p_item_type IN VARCHAR2,
787 p_item_key IN VARCHAR2,
788 p_actid IN NUMBER,
789 p_funmode IN VARCHAR2,
790 p_result OUT NOCOPY VARCHAR2) IS
791 ------------------------------------------------------------------------
792 l_debug_info VARCHAR2(200);
793
794 BEGIN
795
796 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start ReleaseHold');
797
798 IF (p_funmode = 'RUN') THEN
799
800 ----------------------------------------------------------
801 l_debug_info := 'Release Hold';
802 ----------------------------------------------------------
803
804 p_result := 'COMPLETE';
805
806 END IF; -- p_funmode = 'RUN'
807
808 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end ReleaseHold');
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 Wf_Core.Context('AP_WEB_HOLDS_WF', 'ReleaseHold',
813 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
814 raise;
815 END ReleaseHold;
816
817
818 ------------------------------------------------------------------------
819 PROCEDURE StoreNote(
820 p_item_type IN VARCHAR2,
821 p_item_key IN VARCHAR2,
822 p_actid IN NUMBER,
823 p_funmode IN VARCHAR2,
824 p_result OUT NOCOPY VARCHAR2) IS
825 ------------------------------------------------------------------------
826 l_report_header_id number;
827 l_debug_info VARCHAR2(200);
828
832
829 l_fnd_message fnd_new_messages.message_name%type;
830 l_note_text varchar2(2000);
831 l_holding_invoice_num ap_expense_report_headers.invoice_num%type;
833 l_orig_language_code ap_expense_params.note_language_code%type := null;
834 l_orig_language fnd_languages.nls_language%type := null;
835 l_new_language_code ap_expense_params.note_language_code%type := null;
836 l_new_language fnd_languages.nls_language%type := null;
837
838 BEGIN
839
840 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start StoreNote');
841
842 IF (p_funmode = 'RUN') THEN
843
844 -------------------------------------------------------------------
845 l_debug_info := 'Need to generate Note based on language setup';
846 -------------------------------------------------------------------
847
848 -------------------------------------------------------------------
849 l_debug_info := 'Save original language';
850 -------------------------------------------------------------------
851 l_orig_language_code := userenv('LANG');
852 select nls_language
853 into l_orig_language
854 from fnd_languages
855 where language_code = l_orig_language_code;
856
857 -------------------------------------------------------------------
858 l_debug_info := 'Check AP_EXPENSE_PARAMS.NOTE_LANGUAGE_CODE';
859 -------------------------------------------------------------------
860 begin
861 select note_language_code
862 into l_new_language_code
863 from ap_expense_params;
864
865 exception
866 when no_data_found then
867 null;
868 end;
869
870 -------------------------------------------------------------------
871 l_debug_info := 'Else use instance base language';
872 -------------------------------------------------------------------
873 if (l_new_language_code is null) then
874 select language_code
875 into l_new_language_code
876 from fnd_languages
877 where installed_flag in ('B');
878 end if;
879
880 -------------------------------------------------------------------
881 l_debug_info := 'Set nls context to new language';
882 -------------------------------------------------------------------
883 select nls_language
884 into l_new_language
885 from fnd_languages
886 where language_code = l_new_language_code;
887
888 fnd_global.set_nls_context(p_nls_language => l_new_language);
889
890 ------------------------------------------------------------
891 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
892 ------------------------------------------------------------
893 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
894 p_item_key,
895 'EXPENSE_REPORT_ID');
896
897 -------------------------------------------------------------------
898 l_debug_info := 'Retrieve Activity Attr FND Message';
899 -------------------------------------------------------------------
900 l_fnd_message := WF_ENGINE.GetActivityAttrText(p_item_type,
901 p_item_key,
902 p_actid,
903 'FND_MESSAGE');
904
905 if (l_fnd_message is not null) then
906
907 FND_MESSAGE.SET_NAME('SQLAP', l_fnd_message);
908
909 if (l_fnd_message = 'APWRECPT_HELD_ALL_NOTE') then
910
911 l_holding_invoice_num := WF_ENGINE.GetItemAttrText(p_item_type,
912 p_item_key,
913 'HOLDING_EXPENSE_REPORT');
914
915 FND_MESSAGE.SET_TOKEN('HOLDING_EXPENSE_REPORT', l_holding_invoice_num);
916
917 end if; -- l_fnd_message is not null
918
919 l_note_text := FND_MESSAGE.GET;
920
921 ------------------------------------------------------------
922 l_debug_info := 'store the fnd message as a note';
923 ------------------------------------------------------------
924 AP_WEB_NOTES_PKG.CreateERPrepToAudNote (
925 p_report_header_id => l_report_header_id,
926 p_note => l_note_text,
927 p_lang => l_new_language_code
928 );
929
930 end if; -- l_fnd_message is not null
931
932 -------------------------------------------------------------------
933 l_debug_info := 'Restore nls context to original language';
934 -------------------------------------------------------------------
935 fnd_global.set_nls_context(p_nls_language => l_orig_language);
936
937 p_result := 'COMPLETE';
938
939 END IF; -- p_funmode = 'RUN'
940
941 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end StoreNote');
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 Wf_Core.Context('AP_WEB_HOLDS_WF', 'StoreNote',
946 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
947 raise;
948 END StoreNote;
949
950
951 ----------------------------------------------------------------------
952 PROCEDURE CallbackFunction( p_item_type IN VARCHAR2,
953 p_item_key IN VARCHAR2,
954 p_actid IN NUMBER,
955 p_funmode IN VARCHAR2,
956 p_result OUT NOCOPY VARCHAR2) IS
957 ----------------------------------------------------------------------
958 l_debug_info VARCHAR2(200);
959
960 l_org_id number;
961 l_expense_report_id number;
965 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start CallbackFunction');
962
963 BEGIN
964
966
967 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
968 p_item_key,
969 'ORG_ID');
970
971 if (l_org_id is null) then
972 -- EXPENSE_REPORT_ID item attribute should exist
973 l_expense_report_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
974 p_item_key,
975 'EXPENSE_REPORT_ID');
976
977 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
978 l_org_id := NULL;
979 END IF;
980
981 WF_ENGINE.SetItemAttrNumber(p_item_type,
982 p_item_key,
983 'ORG_ID',
984 l_org_id);
985 end if;
986
987 /*
988 if ( p_funmode = 'RUN' ) then
989 --<your RUN executable statements>
990
991 p_result := 'TRUE';
992
993 return;
994 end if;
995 */
996
997 if ( p_funmode = 'SET_CTX' ) then
998 --<your executable statements for establishing context information>
999
1000 if (l_org_id is not null) then
1001 mo_global.set_policy_context(p_access_mode => 'S',
1002 p_org_id => l_org_id);
1003 end if;
1004
1005 p_result := 'TRUE';
1006
1007 return;
1008 end if;
1009
1010 if ( p_funmode = 'TEST_CTX' and l_org_id is not null) then
1011 --<your executable statements for testing the validity of the current context information>
1012
1013 IF ((nvl(mo_global.get_access_mode, 'NULL') <> 'S') OR
1014 (nvl(mo_global.get_current_org_id, -99) <> nvl(l_org_id, -99)) ) THEN
1015 p_result := 'FALSE';
1016 ELSE
1017 p_result := 'TRUE';
1018 END IF;
1019
1020 return;
1021 end if;
1022
1023 /*
1024 if ( p_funmode = '<other command>' ) then
1025 p_result := ' ';
1026
1027 return;
1028 end if;
1029 */
1030
1031 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end CallbackFunction');
1032
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 Wf_Core.Context('AP_WEB_HOLDS_WF', 'CallbackFunction',
1036 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1037 raise;
1038 END CallbackFunction;
1039
1040
1041 ------------------------------------------------------------------------
1042 PROCEDURE ExpenseHolds IS
1043 ------------------------------------------------------------------------
1044
1045 l_debug_info VARCHAR2(200);
1046
1047 BEGIN
1048
1049 ------------------------------------------------------------
1050 l_debug_info := 'Process Hold Each Scenario';
1051 fnd_file.put_line(fnd_file.log, l_debug_info);
1052 ------------------------------------------------------------
1053 HoldEach;
1054
1055 ------------------------------------------------------------
1056 l_debug_info := 'Process Hold All Scenario';
1057 fnd_file.put_line(fnd_file.log, l_debug_info);
1058 ------------------------------------------------------------
1059 HoldAll;
1060
1061 ------------------------------------------------------------
1062 l_debug_info := 'Process Hold BothPay Scenario';
1063 fnd_file.put_line(fnd_file.log, l_debug_info);
1064 ------------------------------------------------------------
1065 HoldBothPay;
1066
1067 ------------------------------------------------------------
1068 l_debug_info := 'Process Obsolete Holds';
1069 fnd_file.put_line(fnd_file.log, l_debug_info);
1070 ------------------------------------------------------------
1071 ObsoleteHold;
1072
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 BEGIN
1076 IF ( SQLCODE <> -20001 )
1077 THEN
1078 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1079 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1080 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'ExpenseHolds' );
1081 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1082 APP_EXCEPTION.RAISE_EXCEPTION;
1083 ELSE
1084 -- Do not need to set the token since it has been done in the
1085 -- child process
1086 RAISE;
1087 END IF;
1088 END;
1089
1090 END ExpenseHolds;
1091
1092
1093 ------------------------------------------------------------------------
1094 PROCEDURE UpdateExpenseStatus(
1095 p_report_header_id IN NUMBER,
1096 p_expense_status_code IN VARCHAR2,
1097 p_holding_report_header_id IN NUMBER) IS
1098 ------------------------------------------------------------------------
1099 l_debug_info VARCHAR2(200);
1100
1101 l_expense_status_code ap_expense_report_headers.expense_status_code%type;
1102
1103 BEGIN
1104
1105 ------------------------------------------------------------
1106 l_debug_info := 'Lock current Expense Status for: '||p_report_header_id;
1107 fnd_file.put_line(fnd_file.log, l_debug_info);
1108 ------------------------------------------------------------
1109 select expense_status_code
1110 into l_expense_status_code
1111 from ap_expense_report_headers
1112 where report_header_id = p_report_header_id
1113 for update of expense_status_code nowait;
1114
1118 ------------------------------------------------------------
1115 ------------------------------------------------------------
1116 l_debug_info := 'Update current Expense Status to: '||p_expense_status_code||' for: '||p_report_header_id;
1117 fnd_file.put_line(fnd_file.log, l_debug_info);
1119 update ap_expense_report_headers
1120 set expense_status_code = p_expense_status_code,
1121 holding_report_header_id = p_holding_report_header_id,
1122 expense_last_status_date = sysdate,
1123 request_id = fnd_global.conc_request_id
1124 where report_header_id = p_report_header_id;
1125
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 BEGIN
1129 IF ( SQLCODE <> -20001 )
1130 THEN
1131 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1132 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1133 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'UpdateExpenseStatus' );
1134 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1135 APP_EXCEPTION.RAISE_EXCEPTION;
1136 ELSE
1137 -- Do not need to set the token since it has been done in the
1138 -- child process
1139 RAISE;
1140 END IF;
1141 END;
1142
1143 END UpdateExpenseStatus;
1144
1145
1146 ------------------------------------------------------------------------
1147 PROCEDURE ReleaseHold(
1148 p_report_header_id IN NUMBER) IS
1149 ------------------------------------------------------------------------
1150 l_debug_info VARCHAR2(200);
1151
1152 BEGIN
1153
1154 ------------------------------------------------------------
1155 l_debug_info := 'Release Hold for: '||p_report_header_id;
1156 fnd_file.put_line(fnd_file.log, l_debug_info);
1157 ------------------------------------------------------------
1158 UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS, null);
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 BEGIN
1163 IF ( SQLCODE <> -20001 )
1164 THEN
1165 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1166 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1167 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'ReleaseHold' );
1168 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1169 APP_EXCEPTION.RAISE_EXCEPTION;
1170 ELSE
1171 -- Do not need to set the token since it has been done in the
1172 -- child process
1173 RAISE;
1174 END IF;
1175 END;
1176
1177 END ReleaseHold;
1178
1179
1180 ------------------------------------------------------------------------
1181 PROCEDURE PlaceHold(
1182 p_report_header_id IN NUMBER,
1183 p_holding_report_header_id IN NUMBER) IS
1184 ------------------------------------------------------------------------
1185 l_debug_info VARCHAR2(200);
1186
1187 BEGIN
1188
1189 ------------------------------------------------------------
1190 l_debug_info := 'Place Hold on: '||p_report_header_id||' because of: '||p_holding_report_header_id;
1191 fnd_file.put_line(fnd_file.log, l_debug_info);
1192 ------------------------------------------------------------
1193 UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, p_holding_report_header_id);
1194
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 BEGIN
1198 IF ( SQLCODE <> -20001 )
1199 THEN
1200 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1201 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1202 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'PlaceHold' );
1203 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1204 APP_EXCEPTION.RAISE_EXCEPTION;
1205 ELSE
1206 -- Do not need to set the token since it has been done in the
1207 -- child process
1208 RAISE;
1209 END IF;
1210 END;
1211
1212 END PlaceHold;
1213
1214
1215 ------------------------------------------------------------------------
1216 PROCEDURE ReadyForPayment(
1217 p_report_header_id IN NUMBER) IS
1218 ------------------------------------------------------------------------
1219 l_debug_info VARCHAR2(200);
1220
1221 BEGIN
1222
1223 ------------------------------------------------------------
1224 l_debug_info := 'Ready for Payment for: '||p_report_header_id;
1225 fnd_file.put_line(fnd_file.log, l_debug_info);
1226 ------------------------------------------------------------
1227 UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_INVOICED, null);
1228
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 BEGIN
1232 IF ( SQLCODE <> -20001 )
1233 THEN
1234 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1235 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1236 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'ReadyForPayment' );
1237 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1238 APP_EXCEPTION.RAISE_EXCEPTION;
1239 ELSE
1240 -- Do not need to set the token since it has been done in the
1241 -- child process
1242 RAISE;
1243 END IF;
1244 END;
1245
1246 END ReadyForPayment;
1247
1248
1249 /*
1250 Written by:
1251 Ron Langi
1255
1252
1253 Purpose:
1254 The purpose of this is to hold the current expense report until the receipt package is received for required receipts.
1256 The following is the PL/SQL logic invoked by the Invoice Import program for the Hold Each scenario:
1257
1258 1. For each expense report that is 'Payment Held' or 'Pending Holds Clearance':
1259
1260 If the receipts are not required or have been received/waived:
1261 a. Mark as 'Ready for Payment'
1262 b. Clear the HOLDING_REPORT_HEADER_ID
1263
1264 If it is pending holds or it has been held for another report (scenario change):
1265 a. Mark as 'Payment Held'
1266 b. Set the HOLDING_REPORT_HEADER_ID to the current expense report
1267
1268
1269 Input:
1270
1271 Output:
1272
1273 Input/Output:
1274
1275 Assumption:
1276
1277 */
1278 ----------------------------------------------------------------------
1279 PROCEDURE HoldEach IS
1280 ----------------------------------------------------------------------
1281
1282 l_debug_info VARCHAR2(200);
1283
1284 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1285 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
1286 l_source ap_expense_report_headers.source%TYPE;
1287 l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1288 l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1289
1290
1291 /*
1292 Criteria for this cursor is:
1293 - all payment held or pending holds clearance
1294 - excludes bothpay child reports
1295 - submitted within Hold Each scenario
1296 */
1297 ------------------------------------------------------------
1298 -- cursor for Hold Each scenario
1299 ------------------------------------------------------------
1300 CURSOR c_hold_each IS
1301 select aerh.report_header_id,
1302 aerh.receipts_status,
1303 aerh.source,
1304 aerh.expense_status_code,
1305 aerh.holding_report_header_id
1306 from AP_EXPENSE_REPORT_HEADERS aerh,
1307 AP_AUD_RULE_SETS rs,
1308 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1309 where aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
1310 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1311 and aerh.bothpay_parent_id is null
1312 and rsa.org_id = aerh.org_id
1313 and rsa.rule_set_id = rs.rule_set_id
1314 and rs.rule_set_type = C_HOLD_RULE
1315 and TRUNC(aerh.report_submitted_date)
1316 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
1317 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
1318 and rs.HOLD_CODE = C_HOLD_EACH_CODE;
1319
1320
1321 BEGIN
1322
1323 ------------------------------------------------------------
1324 l_debug_info := 'Determine whether to place Holds';
1325 fnd_file.put_line(fnd_file.log, l_debug_info);
1326 ------------------------------------------------------------
1327 open c_hold_each;
1328 loop
1329
1330 fetch c_hold_each into l_report_header_id,
1331 l_receipts_status,
1332 l_source,
1333 l_expense_status_code,
1334 l_holding_report_header_id;
1335 exit when c_hold_each%NOTFOUND;
1336
1337 if (nvl(l_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) in (AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED, AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED)) then
1338
1339 ------------------------------------------------------------
1340 l_debug_info := 'Receipts not required or have been received/waived';
1341 fnd_file.put_line(fnd_file.log, l_debug_info);
1342 ------------------------------------------------------------
1343 ------------------------------------------------------------
1344 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
1345 fnd_file.put_line(fnd_file.log, l_debug_info);
1346 ------------------------------------------------------------
1347 ReadyForPayment(l_report_header_id);
1348
1349 elsif (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS or
1350 (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD and l_holding_report_header_id <> l_report_header_id)) then
1351
1352 ------------------------------------------------------------
1353 l_debug_info := 'Set status Payment Held and set holding_report_header_id to current report_header_id for: '||l_report_header_id;
1354 fnd_file.put_line(fnd_file.log, l_debug_info);
1355 ------------------------------------------------------------
1356 PlaceHold(l_report_header_id, l_report_header_id);
1357
1358 elsif (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD and
1359 nvl(l_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED, AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED)) then
1360
1361 ------------------------------------------------------------
1362 l_debug_info := 'Payment Held status untouched for: '||l_report_header_id;
1363 fnd_file.put_line(fnd_file.log, l_debug_info);
1364 ------------------------------------------------------------
1365
1366 else
1367
1368 ------------------------------------------------------------
1369 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
1370 fnd_file.put_line(fnd_file.log, l_debug_info);
1371 ------------------------------------------------------------
1372 ReadyForPayment(l_report_header_id);
1373
1374 end if;
1375
1376 end loop;
1377 close c_hold_each;
1381 BEGIN
1378
1379 EXCEPTION
1380 WHEN OTHERS THEN
1382 IF ( SQLCODE <> -20001 )
1383 THEN
1384 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1385 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1386 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'HoldEach' );
1387 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1388 APP_EXCEPTION.RAISE_EXCEPTION;
1389 ELSE
1390 -- Do not need to set the token since it has been done in the
1391 -- child process
1392 RAISE;
1393 END IF;
1394 END;
1395
1396 END HoldEach;
1397
1398
1399 ------------------------------------------------------------------------
1400 FUNCTION GetOldestOverdueReceipts(
1401 p_employee_id IN NUMBER,
1402 p_hold_rct_overdue_days IN NUMBER) RETURN NUMBER IS
1403 ------------------------------------------------------------------------
1404 l_debug_info VARCHAR2(200);
1405
1406 l_overdue_report_header_id number;
1407
1408 /*
1409 Criteria for this cursor is:
1410 - all submitted reports
1411 - excludes bothpay child reports
1412 - receipts are grossly overdue
1413 */
1414 ------------------------------------------------------------
1415 -- cursor for oldest overdue receipts
1416 -- NOTE: need index on aerh.receipts_status/employee_id
1417 ------------------------------------------------------------
1418 CURSOR c_oldest_overdue_receipts IS
1419 select aerh.report_header_id
1420 from AP_EXPENSE_REPORT_HEADERS aerh
1421 where (aerh.source <> 'NonValidatedWebExpense' or aerh.workflow_approved_flag is null)
1422 and aerh.receipts_status is not null
1423 and aerh.receipts_status in (AP_WEB_RECEIPTS_WF.C_REQUIRED, AP_WEB_RECEIPTS_WF.C_MISSING, AP_WEB_RECEIPTS_WF.C_OVERDUE, AP_WEB_RECEIPTS_WF.C_IN_TRANSIT, AP_WEB_RECEIPTS_WF.C_RESOLUTN)
1424 and aerh.bothpay_parent_id is null
1425 and trunc(sysdate) - (trunc(aerh.report_submitted_date) + p_hold_rct_overdue_days) > 0
1426 and aerh.employee_id = p_employee_id
1427 order by aerh.report_submitted_date asc;
1428
1429 BEGIN
1430
1431 ------------------------------------------------------------
1432 l_debug_info := 'Get Oldest Overdue Receipts';
1433 fnd_file.put_line(fnd_file.log, l_debug_info);
1434 ------------------------------------------------------------
1435 open c_oldest_overdue_receipts;
1436 fetch c_oldest_overdue_receipts into l_overdue_report_header_id;
1437 close c_oldest_overdue_receipts;
1438
1439 return l_overdue_report_header_id;
1440
1441 EXCEPTION
1442 WHEN NO_DATA_FOUND THEN
1443 return null;
1444 WHEN OTHERS THEN
1445 BEGIN
1446 IF ( SQLCODE <> -20001 )
1447 THEN
1448 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1449 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1450 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'GetOldestOverdueReceipts' );
1451 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1452 APP_EXCEPTION.RAISE_EXCEPTION;
1453 ELSE
1454 -- Do not need to set the token since it has been done in the
1455 -- child process
1456 RAISE;
1457 END IF;
1458 END;
1459
1460 END GetOldestOverdueReceipts;
1461
1462
1463 /*
1464 Written by:
1465 Ron Langi
1466
1467 Purpose:
1468 The purpose of this is to hold the current expense report if the employee has any grossly overdue receipts
1469
1470 The following is the PL/SQL logic invoked by the Invoice Import program for the Hold All scenario:
1471
1472 1. For each expense report that is 'Payment Held' or 'Pending Holds Clearance':
1473
1474 If there is an expense report with grossly overdue receipts and it is currently not being held for it:
1475 a. Mark as 'Payment Held'
1476 b. Set the HOLDING_REPORT_HEADER_ID to the expense report with grossly overdue receipts
1477 c. Raise the Expense Held event
1478
1479 If there is no expense report with grossly overdue receipts:
1480 a. Mark as 'Ready for Payment'
1481 b. Raise the Expense Released event if previously held
1482
1483
1484 Input:
1485
1486 Output:
1487
1488 Input/Output:
1489
1490 Assumption:
1491
1492 */
1493 ----------------------------------------------------------------------
1494 PROCEDURE HoldAll IS
1495 ----------------------------------------------------------------------
1496
1497 l_debug_info VARCHAR2(200);
1498
1499 l_overdue_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1500 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1501 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
1502 l_source ap_expense_report_headers.source%TYPE;
1503 l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1504 l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1505 l_employee_id ap_expense_report_headers.employee_id%TYPE;
1506 l_hold_rct_overdue_days ap_aud_rule_sets.hold_rct_overdue_days%TYPE;
1507
1508
1509 /*
1510 Criteria for this cursor is:
1511 - all payment held or pending holds clearance
1512 - excludes bothpay child reports
1513 - submitted within Hold All scenario
1514 */
1515 ------------------------------------------------------------
1516 -- cursor for Hold All scenario
1517 ------------------------------------------------------------
1521 aerh.source,
1518 CURSOR c_hold_all IS
1519 select aerh.report_header_id,
1520 aerh.receipts_status,
1522 aerh.expense_status_code,
1523 aerh.holding_report_header_id,
1524 aerh.employee_id,
1525 rs.hold_rct_overdue_days
1526 from AP_EXPENSE_REPORT_HEADERS aerh,
1527 AP_AUD_RULE_SETS rs,
1528 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1529 where aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
1530 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1531 and aerh.bothpay_parent_id is null
1532 and rsa.org_id = aerh.org_id
1533 and rsa.rule_set_id = rs.rule_set_id
1534 and rs.rule_set_type = C_HOLD_RULE
1535 and TRUNC(aerh.report_submitted_date)
1536 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
1537 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
1538 and rs.HOLD_CODE = C_HOLD_ALL_CODE;
1539
1540 BEGIN
1541
1542 ------------------------------------------------------------
1543 l_debug_info := 'Determine whether to place Holds';
1544 fnd_file.put_line(fnd_file.log, l_debug_info);
1545 ------------------------------------------------------------
1546 open c_hold_all;
1547 loop
1548
1549 fetch c_hold_all into l_report_header_id,
1550 l_receipts_status,
1551 l_source,
1552 l_expense_status_code,
1553 l_holding_report_header_id,
1554 l_employee_id,
1555 l_hold_rct_overdue_days;
1556 exit when c_hold_all%NOTFOUND;
1557
1558 ------------------------------------------------------------
1559 l_debug_info := 'Get oldest overdue receipts for employee: '||to_char(l_employee_id);
1560 fnd_file.put_line(fnd_file.log, l_debug_info);
1561 ------------------------------------------------------------
1562 l_overdue_report_header_id := GetOldestOverdueReceipts(l_employee_id, l_hold_rct_overdue_days);
1563
1564 ------------------------------------------------------------
1565 l_debug_info := 'Oldest overdue receipts is: '||to_char(l_overdue_report_header_id);
1566 fnd_file.put_line(fnd_file.log, l_debug_info);
1567 ------------------------------------------------------------
1568
1569 if (l_overdue_report_header_id is not null) then
1570
1571 ------------------------------------------------------------
1572 l_debug_info := 'Current holding report is: '||to_char(l_holding_report_header_id);
1573 fnd_file.put_line(fnd_file.log, l_debug_info);
1574 ------------------------------------------------------------
1575
1576 if (l_overdue_report_header_id <> nvl(l_holding_report_header_id, 0)) then
1577
1578 ------------------------------------------------------------
1579 l_debug_info := 'Place Hold';
1580 fnd_file.put_line(fnd_file.log, l_debug_info);
1581 ------------------------------------------------------------
1582
1583 ------------------------------------------------------------
1584 l_debug_info := 'Set status Payment Held for: '||l_report_header_id||' and set holding_report_header_id to overdue report_header_id: '||l_overdue_report_header_id;
1585 fnd_file.put_line(fnd_file.log, l_debug_info);
1586 ------------------------------------------------------------
1587 PlaceHold(l_report_header_id, l_overdue_report_header_id);
1588
1589 ------------------------------------------------------------
1590 l_debug_info := 'Raise hold placed event for: '||l_report_header_id;
1591 fnd_file.put_line(fnd_file.log, l_debug_info);
1592 ------------------------------------------------------------
1593 RaiseHeldEvent(l_report_header_id);
1594
1595 end if;
1596
1597 else
1598
1599 ------------------------------------------------------------
1600 l_debug_info := 'Mark Ready for Payment';
1601 fnd_file.put_line(fnd_file.log, l_debug_info);
1602 ------------------------------------------------------------
1603
1604 ------------------------------------------------------------
1605 l_debug_info := 'Set status Ready for Payment and clear holding_report_header_id for: '||l_report_header_id;
1606 fnd_file.put_line(fnd_file.log, l_debug_info);
1607 ------------------------------------------------------------
1608 ReadyForPayment(l_report_header_id);
1609
1610 ------------------------------------------------------------
1611 l_debug_info := 'If previously Held, then raise Released event';
1612 ------------------------------------------------------------
1613 if (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
1614
1615 ------------------------------------------------------------
1616 l_debug_info := 'Raise hold released event for: '||l_report_header_id;
1617 fnd_file.put_line(fnd_file.log, l_debug_info);
1618 ------------------------------------------------------------
1619 RaiseReleasedEvent(l_report_header_id);
1620
1621 end if;
1622
1623 end if;
1624
1625 end loop;
1626 close c_hold_all;
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 BEGIN
1631 IF ( SQLCODE <> -20001 )
1632 THEN
1633 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1634 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1635 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'HoldAll' );
1636 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1637 APP_EXCEPTION.RAISE_EXCEPTION;
1638 ELSE
1639 -- Do not need to set the token since it has been done in the
1643 END;
1640 -- child process
1641 RAISE;
1642 END IF;
1644
1645
1646 END HoldAll;
1647
1648
1649 ------------------------------------------------------------------------
1650 FUNCTION IsCCReceiptsRequired(
1651 p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
1652 ------------------------------------------------------------------------
1653 l_debug_info VARCHAR2(200);
1654
1655 l_is_cc_receipts_required VARCHAR2(1);
1656
1657 BEGIN
1658
1659 ------------------------------------------------------------
1660 l_debug_info := 'Check if Credit Card Receipts are required for: '||p_report_header_id;
1661 fnd_file.put_line(fnd_file.log, l_debug_info);
1662 ------------------------------------------------------------
1663 select 'Y'
1664 into l_is_cc_receipts_required
1665 from AP_EXPENSE_REPORT_HEADERS aerh
1666 where aerh.report_header_id = p_report_header_id
1667 and
1668 exists
1669 (select 1
1670 from ap_expense_report_lines aerl
1671 where aerl.report_header_id = aerh.report_header_id
1672 and aerl.credit_card_trx_id is not null
1673 and nvl(aerl.receipt_required_flag, 'N') = 'Y'
1674 and rownum = 1
1675 )
1676 and rownum = 1;
1677
1678 return l_is_cc_receipts_required;
1679
1680 EXCEPTION
1681 WHEN NO_DATA_FOUND THEN
1682 return 'N';
1683 WHEN OTHERS THEN
1684 BEGIN
1685 IF ( SQLCODE <> -20001 )
1686 THEN
1687 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1688 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1689 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'IsCCReceiptsRequired' );
1690 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1691 APP_EXCEPTION.RAISE_EXCEPTION;
1692 ELSE
1693 -- Do not need to set the token since it has been done in the
1694 -- child process
1695 RAISE;
1696 END IF;
1697 END;
1698
1699 END IsCCReceiptsRequired;
1700
1701
1702 /*
1703 Written by:
1704 Ron Langi
1705
1706 Purpose:
1707 The purpose of this is to hold the Both Pay Credit Card Expenses, if setup that way.
1708
1709 The following shows the PL/SQL logic invoked by the Invoice Import program for the Both Pay Credit Card Expenses.
1710
1711 1. For each expense report that is 'Payment Held' or 'Pending Holds Clearance':
1712
1713 If the receipts are not required or have been received/waived and rule is not set to 'Always'
1714 a. Mark as 'Ready for Payment'
1715
1716 If the rule is set to 'Never':
1717 a. Mark as 'Ready for Payment'
1718
1719 If the rule is set to 'If Receipts Required' and the credit card receipts are not required or have been received/waived:
1720 a. Mark as 'Ready for Payment'
1721
1722 If the rule is set to 'If Receipts Required' and the credit card receipts are required and have not been received/waived and the report is not already held:
1723 a. Mark as 'Payment Held'
1724 b. Set the HOLDING_REPORT_HEADER_ID to the parent expense report
1725
1726 If the rule is set to 'Always' and the parent expense report's status is invoiced
1727 a. Mark as 'Ready for Payment'
1728
1729 If the rule is set to 'Always' and the parent expense report's status is payment held
1730 a. Mark as 'Payment Held'
1731 b. Set the HOLDING_REPORT_HEADER_ID to the parent expense report
1732
1733
1734 Input:
1735
1736 Output:
1737
1738 Input/Output:
1739
1740 Assumption:
1741
1742 */
1743 ------------------------------------------------------------------------
1744 PROCEDURE HoldBothPay IS
1745 ------------------------------------------------------------------------
1746
1747 l_debug_info VARCHAR2(200);
1748
1749 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1750 l_bothpay_parent_id ap_expense_report_headers.bothpay_parent_id%TYPE;
1751 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
1752 l_parent_status ap_expense_report_headers.expense_status_code%TYPE;
1753 l_source ap_expense_report_headers.source%TYPE;
1754 l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1755 l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1756 l_hold_rct_overdue_bp_cc_code ap_aud_rule_sets.hold_rct_overdue_bp_cc_code%TYPE;
1757
1758 /*
1759 Criteria for this cursor is:
1760 - source is 'SelfService' or 'Both Pay'
1761 - all payment held or pending holds clearance
1762 - only bothpay child reports
1763 - submitted within Hold Each or All scenario
1764 */
1765 ------------------------------------------------------------
1766 -- cursor for holds in Both Pay scenario
1767 ------------------------------------------------------------
1768 CURSOR c_hold_bothpay IS
1769 select aerh.report_header_id,
1770 aerh.bothpay_parent_id,
1771 aerh2.receipts_status,
1772 aerh2.expense_status_code,
1773 aerh.source,
1774 aerh.expense_status_code,
1775 aerh.holding_report_header_id,
1776 rs.hold_rct_overdue_bp_cc_code
1777 from AP_EXPENSE_REPORT_HEADERS aerh,
1778 AP_EXPENSE_REPORT_HEADERS aerh2,
1779 AP_AUD_RULE_SETS rs,
1780 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1781 where aerh.source in (AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE, AP_WEB_RECEIPTS_WF.C_BOTHPAY)
1782 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1786 and rsa.rule_set_id = rs.rule_set_id
1783 and aerh.bothpay_parent_id is not null
1784 and aerh2.report_header_id = aerh.bothpay_parent_id
1785 and rsa.org_id = aerh2.org_id
1787 and rs.rule_set_type = C_HOLD_RULE
1788 and TRUNC(aerh2.report_submitted_date)
1789 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh2.report_submitted_date))
1790 AND TRUNC(NVL(rsa.END_DATE, aerh2.report_submitted_date))
1791 and rs.HOLD_CODE in (C_HOLD_EACH_CODE, C_HOLD_ALL_CODE);
1792
1793
1794 BEGIN
1795
1796 ------------------------------------------------------------
1797 l_debug_info := 'Determine whether to place Holds';
1798 fnd_file.put_line(fnd_file.log, l_debug_info);
1799 ------------------------------------------------------------
1800 open c_hold_bothpay;
1801 loop
1802
1803 fetch c_hold_bothpay into l_report_header_id,
1804 l_bothpay_parent_id,
1805 l_receipts_status,
1806 l_parent_status,
1807 l_source,
1808 l_expense_status_code,
1809 l_holding_report_header_id,
1810 l_hold_rct_overdue_bp_cc_code;
1811 exit when c_hold_bothpay%NOTFOUND;
1812
1813 if (nvl(l_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) in (AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED, AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED) and
1814 l_hold_rct_overdue_bp_cc_code <> C_HOLD_BP_ALWAYS) then
1815 ------------------------------------------------------------
1816 l_debug_info := 'Receipts not required or have been received/waived and not Hold Always';
1817 fnd_file.put_line(fnd_file.log, l_debug_info);
1818 ------------------------------------------------------------
1819
1820 ------------------------------------------------------------
1821 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
1822 fnd_file.put_line(fnd_file.log, l_debug_info);
1823 ------------------------------------------------------------
1824 ReadyForPayment(l_report_header_id);
1825
1826 elsif (l_hold_rct_overdue_bp_cc_code = C_HOLD_BP_NEVER) then
1827 ------------------------------------------------------------
1828 l_debug_info := 'Never Hold BothPay';
1829 fnd_file.put_line(fnd_file.log, l_debug_info);
1830 ------------------------------------------------------------
1831
1832 ------------------------------------------------------------
1833 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
1834 fnd_file.put_line(fnd_file.log, l_debug_info);
1835 ------------------------------------------------------------
1836 ReadyForPayment(l_report_header_id);
1837
1838 elsif (l_hold_rct_overdue_bp_cc_code = C_HOLD_BP_REQUIRED) then
1839 ------------------------------------------------------------
1840 l_debug_info := 'Hold BothPay If Required';
1841 fnd_file.put_line(fnd_file.log, l_debug_info);
1842 ------------------------------------------------------------
1843
1844 if (IsCCReceiptsRequired(l_bothpay_parent_id) = 'Y' and
1845 nvl(l_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED, AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED)) then
1846
1847 if (l_expense_status_code <> AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
1848
1849 ------------------------------------------------------------
1850 l_debug_info := 'Set status Payment Held for: '||l_report_header_id||' and set holding_report_header_id to parent report_header_id: '||l_bothpay_parent_id;
1851 fnd_file.put_line(fnd_file.log, l_debug_info);
1852 ------------------------------------------------------------
1853 PlaceHold(l_report_header_id, l_bothpay_parent_id);
1854
1855 end if;
1856
1857 else
1858
1859 ------------------------------------------------------------
1860 l_debug_info := 'Set status Ready for Payment Held for: '||l_report_header_id;
1861 fnd_file.put_line(fnd_file.log, l_debug_info);
1862 ------------------------------------------------------------
1863 ReadyForPayment(l_report_header_id);
1864
1865 end if; -- Hold BothPay If Required
1866
1867 elsif (l_hold_rct_overdue_bp_cc_code = C_HOLD_BP_ALWAYS) then
1868 ------------------------------------------------------------
1869 l_debug_info := 'Hold BothPay Always';
1870 fnd_file.put_line(fnd_file.log, l_debug_info);
1871 ------------------------------------------------------------
1872
1873 if (l_parent_status = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
1874
1875 ------------------------------------------------------------
1876 l_debug_info := 'Set status Payment Held for: '||l_report_header_id||' and set holding_report_header_id to parent report_header_id: '||l_bothpay_parent_id;
1877 fnd_file.put_line(fnd_file.log, l_debug_info);
1878 ------------------------------------------------------------
1879 PlaceHold(l_report_header_id, l_bothpay_parent_id);
1880
1881 else
1882
1883 ------------------------------------------------------------
1884 l_debug_info := 'Set status Ready for Payment Held for: '||l_report_header_id;
1885 fnd_file.put_line(fnd_file.log, l_debug_info);
1886 ------------------------------------------------------------
1887 ReadyForPayment(l_report_header_id);
1888
1889 end if; -- Hold BothPay Always
1890
1891 else
1892
1893 ------------------------------------------------------------
1897
1894 l_debug_info := 'I do not understand hold scenario: '||l_hold_rct_overdue_bp_cc_code;
1895 fnd_file.put_line(fnd_file.log, l_debug_info);
1896 ------------------------------------------------------------
1898 end if;
1899
1900 end loop;
1901 close c_hold_bothpay;
1902
1903
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 BEGIN
1907 IF ( SQLCODE <> -20001 )
1908 THEN
1909 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1910 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1911 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'HoldBothPay' );
1912 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1913 APP_EXCEPTION.RAISE_EXCEPTION;
1914 ELSE
1915 -- Do not need to set the token since it has been done in the
1916 -- child process
1917 RAISE;
1918 END IF;
1919 END;
1920
1921 END HoldBothPay;
1922
1923
1924 /*
1925 Written by:
1926 Ron Langi
1927
1928 Purpose:
1929 The purpose of this is to release any holds on expense reports where:
1930 1. hold rules no longer apply
1931 2. hold was performed on an original report containing only Both Pay credit card trxns whose
1932 Both Pay report is invoiced.
1933
1934 The following is the PL/SQL logic invoked by the Invoice Import program for non-existent holds :
1935 a. Mark as 'Ready for Payment'
1936 b. Clear the HOLDING_REPORT_HEADER_ID
1937 c. Raise the Expense Released event if previously held
1938
1939
1940
1941 Input:
1942
1943 Output:
1944
1945 Input/Output:
1946
1947 Assumption:
1948
1949 */
1950 ------------------------------------------------------------------------
1951 PROCEDURE ObsoleteHold IS
1952 ------------------------------------------------------------------------
1953
1954 l_debug_info VARCHAR2(200);
1955
1956 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1957 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
1958 l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1959 l_source ap_expense_report_headers.source%TYPE;
1960 l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1961
1962
1963 /*
1964 Criteria for this cursor is:
1965 - source is 'SelfService' or 'Both Pay'
1966 - all payment held or pending holds clearance
1967 - not submitted within Hold Each or All scenario
1968 */
1969 ------------------------------------------------------------
1970 -- cursor for obsolete Holds
1971 ------------------------------------------------------------
1972 CURSOR c_obsolete_holds IS
1973 select aerh.report_header_id,
1974 aerh.source,
1975 aerh.expense_status_code
1976 from AP_EXPENSE_REPORT_HEADERS aerh
1977 where aerh.source in (AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE, AP_WEB_RECEIPTS_WF.C_BOTHPAY)
1978 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1979 and
1980 not exists
1981 (select 1
1982 from AP_AUD_RULE_SETS rs,
1983 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1984 where rsa.org_id = aerh.org_id
1985 and rsa.rule_set_id = rs.rule_set_id
1986 and rs.rule_set_type = C_HOLD_RULE
1987 and TRUNC(aerh.report_submitted_date)
1988 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
1989 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
1990 and rownum = 1
1991 );
1992
1993 /*
1994 Criteria for this cursor is:
1995 - source is 'SelfService'
1996 - all payment held or pending holds clearance
1997 - original reports containing only Both Pay credit card trxns
1998 */
1999 ------------------------------------------------------------
2000 -- cursor for obsolete Both Pay Holds
2001 ------------------------------------------------------------
2002 CURSOR c_obsolete_bothpay_holds IS
2003 select aerh.report_header_id,
2004 aerh.expense_status_code
2005 from AP_EXPENSE_REPORT_HEADERS aerh
2006 where aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
2007 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
2008 and aerh.bothpay_parent_id is null
2009 and
2010 not exists
2011 (select 1
2012 from ap_expense_report_lines aerl
2013 where aerl.report_header_id = aerh.report_header_id
2014 and aerl.credit_card_trx_id IS NULL
2015 and rownum = 1)
2016 and
2017 exists
2018 (select 1
2019 from ap_expense_report_headers aerh2
2020 where aerh2.bothpay_parent_id = aerh.report_header_id
2021 and aerh2.expense_status_code = AP_WEB_RECEIPTS_WF.C_INVOICED
2022 and rownum = 1);
2023
2024
2025 BEGIN
2026
2027 ------------------------------------------------------------
2028 l_debug_info := 'Obsolete Holds';
2029 fnd_file.put_line(fnd_file.log, l_debug_info);
2030 ------------------------------------------------------------
2031 open c_obsolete_holds;
2032 loop
2033
2034 fetch c_obsolete_holds into l_report_header_id,
2035 l_source,
2036 l_expense_status_code;
2037 exit when c_obsolete_holds%NOTFOUND;
2038
2039 ------------------------------------------------------------
2040 l_debug_info := 'Mark Ready for Payment';
2041 fnd_file.put_line(fnd_file.log, l_debug_info);
2042 ------------------------------------------------------------
2043
2047 ------------------------------------------------------------
2044 ------------------------------------------------------------
2045 l_debug_info := 'Set status Ready for Payment and clear holding_report_header_id for: '||l_report_header_id;
2046 fnd_file.put_line(fnd_file.log, l_debug_info);
2048 ReadyForPayment(l_report_header_id);
2049
2050 ------------------------------------------------------------
2051 l_debug_info := 'If not Both Pay child and previously Held, then raise Released event';
2052 ------------------------------------------------------------
2053 if (l_source <> AP_WEB_RECEIPTS_WF.C_BOTHPAY and
2054 l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
2055
2056 ------------------------------------------------------------
2057 l_debug_info := 'Raise hold released event for: '||l_report_header_id;
2058 fnd_file.put_line(fnd_file.log, l_debug_info);
2059 ------------------------------------------------------------
2060 RaiseReleasedEvent(l_report_header_id);
2061
2062 end if;
2063
2064 end loop;
2065 close c_obsolete_holds;
2066
2067
2068 ------------------------------------------------------------
2069 l_debug_info := 'Obsolete Both Pay Holds';
2070 fnd_file.put_line(fnd_file.log, l_debug_info);
2071 ------------------------------------------------------------
2072 open c_obsolete_bothpay_holds;
2073 loop
2074
2075 fetch c_obsolete_bothpay_holds into l_report_header_id,
2076 l_expense_status_code;
2077 exit when c_obsolete_bothpay_holds%NOTFOUND;
2078
2079 ------------------------------------------------------------
2080 l_debug_info := 'Mark Ready for Payment';
2081 fnd_file.put_line(fnd_file.log, l_debug_info);
2082 ------------------------------------------------------------
2083
2084 ------------------------------------------------------------
2085 l_debug_info := 'Set status Ready for Payment and clear holding_report_header_id for: '||l_report_header_id;
2086 fnd_file.put_line(fnd_file.log, l_debug_info);
2087 ------------------------------------------------------------
2088 ReadyForPayment(l_report_header_id);
2089
2090 ------------------------------------------------------------
2091 l_debug_info := 'If previously Held, then raise Released event';
2092 ------------------------------------------------------------
2093 if (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
2094
2095 ------------------------------------------------------------
2096 l_debug_info := 'Raise hold released event for: '||l_report_header_id;
2097 fnd_file.put_line(fnd_file.log, l_debug_info);
2098 ------------------------------------------------------------
2099 RaiseReleasedEvent(l_report_header_id);
2100
2101 end if;
2102
2103 end loop;
2104 close c_obsolete_bothpay_holds;
2105
2106
2107 EXCEPTION
2108 WHEN OTHERS THEN
2109 BEGIN
2110 IF ( SQLCODE <> -20001 )
2111 THEN
2112 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
2113 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
2114 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'ObsoleteHold' );
2115 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
2116 APP_EXCEPTION.RAISE_EXCEPTION;
2117 ELSE
2118 -- Do not need to set the token since it has been done in the
2119 -- child process
2120 RAISE;
2121 END IF;
2122 END;
2123
2124 END ObsoleteHold;
2125
2126
2127
2128
2129
2130 END AP_WEB_HOLDS_WF;