[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_HOLDS_WF
Source
1 PACKAGE BODY AP_WEB_HOLDS_WF AS
2 /* $Header: apwholdsb.pls 120.14 2011/07/15 10:16:20 rveliche ship $ */
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;
417 l_numNameArr(iNum) := 'HOLDING_EXPENSE_REPORT_ID';
418 l_numValArr(iNum) := l_holding_report_header_id;
419
420 --------------------------------------------------------
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 -----------------------------------------------------
546 l_debug_info := 'Set all text item attributes';
547 -----------------------------------------------------
548 WF_ENGINE.SetItemAttrTextArray(p_item_type, p_item_key, l_textNameArr, l_textValArr);
549
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);
689 l_hold_code AP_AUD_RULE_SETS.hold_code%type;
690
691 BEGIN
692
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 OR l_hold_code = C_HOLD_EACH_CODE) then
711 return C_HOLD_ALL;
712 else
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
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;
832
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 l_org_id ap_expense_params_all.org_id%type;
839
840 BEGIN
841
842 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start StoreNote');
843
844 IF (p_funmode = 'RUN') THEN
845
846 -------------------------------------------------------------------
847 l_debug_info := 'Need to generate Note based on language setup';
848 -------------------------------------------------------------------
849
850 -------------------------------------------------------------------
851 l_debug_info := 'Save original language';
852 -------------------------------------------------------------------
853 l_orig_language_code := userenv('LANG');
854 select nls_language
855 into l_orig_language
856 from fnd_languages
857 where language_code = l_orig_language_code;
858
859 -------------------------------------------------------------------
860 l_debug_info := 'Check AP_EXPENSE_PARAMS.NOTE_LANGUAGE_CODE';
861 -------------------------------------------------------------------
862 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
863 p_item_key,
864 'ORG_ID');
865 begin
866 select note_language_code
867 into l_new_language_code
868 from ap_expense_params_all
869 where org_id = l_org_id;
870
871 exception
872 when no_data_found then
873 null;
874 end;
875
876 -------------------------------------------------------------------
877 l_debug_info := 'Else use instance base language';
878 -------------------------------------------------------------------
879 if (l_new_language_code is null) then
880 select language_code
881 into l_new_language_code
882 from fnd_languages
883 where installed_flag in ('B');
884 end if;
885
886 -------------------------------------------------------------------
887 l_debug_info := 'Set nls context to new language';
888 -------------------------------------------------------------------
889 select nls_language
890 into l_new_language
891 from fnd_languages
892 where language_code = l_new_language_code;
893
894 fnd_global.set_nls_context(p_nls_language => l_new_language);
895
896 ------------------------------------------------------------
897 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
898 ------------------------------------------------------------
899 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
900 p_item_key,
901 'EXPENSE_REPORT_ID');
902
903 -------------------------------------------------------------------
904 l_debug_info := 'Retrieve Activity Attr FND Message';
905 -------------------------------------------------------------------
906 l_fnd_message := WF_ENGINE.GetActivityAttrText(p_item_type,
907 p_item_key,
908 p_actid,
909 'FND_MESSAGE');
910
911 if (l_fnd_message is not null) then
912
913 FND_MESSAGE.SET_NAME('SQLAP', l_fnd_message);
914
915 if (l_fnd_message = 'APWRECPT_HELD_ALL_NOTE') then
916
917 l_holding_invoice_num := WF_ENGINE.GetItemAttrText(p_item_type,
918 p_item_key,
919 'HOLDING_EXPENSE_REPORT');
920
921 FND_MESSAGE.SET_TOKEN('HOLDING_EXPENSE_REPORT', l_holding_invoice_num);
922
923 end if; -- l_fnd_message is not null
924
925 l_note_text := FND_MESSAGE.GET;
926
927 ------------------------------------------------------------
928 l_debug_info := 'store the fnd message as a note';
929 ------------------------------------------------------------
930 AP_WEB_NOTES_PKG.CreateERPrepToAudNote (
931 p_report_header_id => l_report_header_id,
932 p_note => l_note_text,
933 p_lang => l_new_language_code
934 );
935
936 end if; -- l_fnd_message is not null
937
938 -------------------------------------------------------------------
939 l_debug_info := 'Restore nls context to original language';
940 -------------------------------------------------------------------
941 fnd_global.set_nls_context(p_nls_language => l_orig_language);
942
943 p_result := 'COMPLETE';
944
945 END IF; -- p_funmode = 'RUN'
946
947 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end StoreNote');
948
949 EXCEPTION
950 WHEN OTHERS THEN
951 Wf_Core.Context('AP_WEB_HOLDS_WF', 'StoreNote',
952 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
953 raise;
954 END StoreNote;
955
956
957 ----------------------------------------------------------------------
958 PROCEDURE CallbackFunction( p_item_type IN VARCHAR2,
959 p_item_key IN VARCHAR2,
960 p_actid IN NUMBER,
961 p_funmode IN VARCHAR2,
962 p_result OUT NOCOPY VARCHAR2) IS
963 ----------------------------------------------------------------------
964 l_debug_info VARCHAR2(200);
965
966 l_org_id number;
967 l_expense_report_id number;
968
969 BEGIN
970
971 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start CallbackFunction');
972
973 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
974 p_item_key,
975 'ORG_ID');
976
977 if (l_org_id is null) then
978 -- EXPENSE_REPORT_ID item attribute should exist
979 l_expense_report_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
980 p_item_key,
981 'EXPENSE_REPORT_ID');
982
983 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(l_expense_report_id, l_org_id) <> TRUE) THEN
984 l_org_id := NULL;
985 END IF;
986
987 WF_ENGINE.SetItemAttrNumber(p_item_type,
988 p_item_key,
989 'ORG_ID',
990 l_org_id);
991 end if;
992
993 /*
994 if ( p_funmode = 'RUN' ) then
995 --<your RUN executable statements>
996
997 p_result := 'TRUE';
998
999 return;
1000 end if;
1001 */
1002
1003 if ( p_funmode = 'SET_CTX' ) then
1004 --<your executable statements for establishing context information>
1005
1006 if (l_org_id is not null) then
1007 mo_global.set_policy_context(p_access_mode => 'S',
1008 p_org_id => l_org_id);
1009 end if;
1010
1011 p_result := 'TRUE';
1012
1013 return;
1014 end if;
1015
1016 if ( p_funmode = 'TEST_CTX' and l_org_id is not null) then
1017 --<your executable statements for testing the validity of the current context information>
1018
1019 IF ((nvl(mo_global.get_access_mode, 'NULL') <> 'S') OR
1020 (nvl(mo_global.get_current_org_id, -99) <> nvl(l_org_id, -99)) ) THEN
1021 p_result := 'FALSE';
1022 ELSE
1023 p_result := 'TRUE';
1024 END IF;
1025
1026 return;
1027 end if;
1028
1029 /*
1030 if ( p_funmode = '<other command>' ) then
1031 p_result := ' ';
1032
1033 return;
1034 end if;
1035 */
1036
1037 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'end CallbackFunction');
1038
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 Wf_Core.Context('AP_WEB_HOLDS_WF', 'CallbackFunction',
1042 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1043 raise;
1044 END CallbackFunction;
1045
1046
1047 ------------------------------------------------------------------------
1048 PROCEDURE ExpenseHolds IS
1049 ------------------------------------------------------------------------
1050
1051 l_debug_info VARCHAR2(200);
1052
1053 BEGIN
1054
1055 ------------------------------------------------------------
1056 l_debug_info := 'Process Hold Each Scenario';
1057 fnd_file.put_line(fnd_file.log, l_debug_info);
1058 ------------------------------------------------------------
1059 HoldEach;
1060
1061 ------------------------------------------------------------
1062 l_debug_info := 'Process Hold All Scenario';
1063 fnd_file.put_line(fnd_file.log, l_debug_info);
1064 ------------------------------------------------------------
1065 HoldAll;
1066
1067 ------------------------------------------------------------
1068 l_debug_info := 'Process Hold BothPay Scenario';
1069 fnd_file.put_line(fnd_file.log, l_debug_info);
1070 ------------------------------------------------------------
1071 HoldBothPay;
1072
1073 ------------------------------------------------------------
1074 l_debug_info := 'Process Obsolete Holds';
1075 fnd_file.put_line(fnd_file.log, l_debug_info);
1076 ------------------------------------------------------------
1077 ObsoleteHold;
1078
1079 EXCEPTION
1080 WHEN OTHERS THEN
1081 BEGIN
1082 IF ( SQLCODE <> -20001 )
1083 THEN
1084 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1085 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1086 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'ExpenseHolds' );
1087 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1088 APP_EXCEPTION.RAISE_EXCEPTION;
1089 ELSE
1090 -- Do not need to set the token since it has been done in the
1091 -- child process
1092 RAISE;
1093 END IF;
1094 END;
1095
1096 END ExpenseHolds;
1097
1098
1099 ------------------------------------------------------------------------
1100 PROCEDURE UpdateExpenseStatus(
1101 p_report_header_id IN NUMBER,
1102 p_expense_status_code IN VARCHAR2,
1103 p_holding_report_header_id IN NUMBER) IS
1104 ------------------------------------------------------------------------
1105 l_debug_info VARCHAR2(200);
1106
1107 l_expense_status_code ap_expense_report_headers.expense_status_code%type;
1108
1109 BEGIN
1110
1111 ------------------------------------------------------------
1112 l_debug_info := 'Lock current Expense Status for: '||p_report_header_id;
1113 fnd_file.put_line(fnd_file.log, l_debug_info);
1114 ------------------------------------------------------------
1115 select expense_status_code
1116 into l_expense_status_code
1117 from ap_expense_report_headers
1118 where report_header_id = p_report_header_id
1119 for update of expense_status_code nowait;
1120
1121 ------------------------------------------------------------
1122 l_debug_info := 'Update current Expense Status to: '||p_expense_status_code||' for: '||p_report_header_id;
1123 fnd_file.put_line(fnd_file.log, l_debug_info);
1124 ------------------------------------------------------------
1125 update ap_expense_report_headers
1126 set expense_status_code = p_expense_status_code,
1127 holding_report_header_id = p_holding_report_header_id,
1128 expense_last_status_date = sysdate,
1129 request_id = fnd_global.conc_request_id
1130 where report_header_id = p_report_header_id;
1131
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 BEGIN
1135 IF ( SQLCODE <> -20001 )
1136 THEN
1137 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1138 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1139 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'UpdateExpenseStatus' );
1140 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1141 APP_EXCEPTION.RAISE_EXCEPTION;
1142 ELSE
1143 -- Do not need to set the token since it has been done in the
1144 -- child process
1145 RAISE;
1146 END IF;
1147 END;
1148
1149 END UpdateExpenseStatus;
1150
1151
1152 ------------------------------------------------------------------------
1153 PROCEDURE ReleaseHold(
1154 p_report_header_id IN NUMBER) IS
1155 ------------------------------------------------------------------------
1156 l_debug_info VARCHAR2(200);
1157
1158 BEGIN
1159
1160 ------------------------------------------------------------
1161 l_debug_info := 'Release Hold for: '||p_report_header_id;
1162 fnd_file.put_line(fnd_file.log, l_debug_info);
1163 ------------------------------------------------------------
1164 UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS, null);
1165
1166 EXCEPTION
1167 WHEN OTHERS THEN
1168 BEGIN
1169 IF ( SQLCODE <> -20001 )
1170 THEN
1171 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1172 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1173 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'ReleaseHold' );
1174 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1175 APP_EXCEPTION.RAISE_EXCEPTION;
1176 ELSE
1177 -- Do not need to set the token since it has been done in the
1178 -- child process
1179 RAISE;
1180 END IF;
1181 END;
1182
1183 END ReleaseHold;
1184
1185
1186 ------------------------------------------------------------------------
1187 PROCEDURE PlaceHold(
1188 p_report_header_id IN NUMBER,
1189 p_holding_report_header_id IN NUMBER) IS
1190 ------------------------------------------------------------------------
1191 l_debug_info VARCHAR2(200);
1192
1193 BEGIN
1194
1195 ------------------------------------------------------------
1196 l_debug_info := 'Place Hold on: '||p_report_header_id||' because of: '||p_holding_report_header_id;
1197 fnd_file.put_line(fnd_file.log, l_debug_info);
1198 ------------------------------------------------------------
1199 UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, p_holding_report_header_id);
1200
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 BEGIN
1204 IF ( SQLCODE <> -20001 )
1205 THEN
1206 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1207 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1208 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'PlaceHold' );
1209 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1210 APP_EXCEPTION.RAISE_EXCEPTION;
1211 ELSE
1212 -- Do not need to set the token since it has been done in the
1213 -- child process
1214 RAISE;
1215 END IF;
1216 END;
1217
1218 END PlaceHold;
1219
1220
1221 ------------------------------------------------------------------------
1222 PROCEDURE ReadyForPayment(
1223 p_report_header_id IN NUMBER) IS
1224 ------------------------------------------------------------------------
1225 l_debug_info VARCHAR2(200);
1226
1227 BEGIN
1228
1229 ------------------------------------------------------------
1230 l_debug_info := 'Ready for Payment for: '||p_report_header_id;
1231 fnd_file.put_line(fnd_file.log, l_debug_info);
1232 ------------------------------------------------------------
1233 UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_INVOICED, null);
1234
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 BEGIN
1238 IF ( SQLCODE <> -20001 )
1239 THEN
1240 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1241 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1242 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'ReadyForPayment' );
1243 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1244 APP_EXCEPTION.RAISE_EXCEPTION;
1245 ELSE
1246 -- Do not need to set the token since it has been done in the
1247 -- child process
1248 RAISE;
1249 END IF;
1250 END;
1251
1252 END ReadyForPayment;
1253
1254
1255 /*
1256 Written by:
1257 Ron Langi
1258
1259 Purpose:
1260 The purpose of this is to hold the current expense report until the receipt package is received for required receipts.
1261
1262 The following is the PL/SQL logic invoked by the Invoice Import program for the Hold Each scenario:
1263
1264 1. For each expense report that is 'Payment Held' or 'Pending Holds Clearance':
1265
1266 If the receipts are not required or have been received/waived:
1267 a. Mark as 'Ready for Payment'
1268 b. Clear the HOLDING_REPORT_HEADER_ID
1269
1270 If it is pending holds or it has been held for another report (scenario change):
1271 a. Mark as 'Payment Held'
1272 b. Set the HOLDING_REPORT_HEADER_ID to the current expense report
1273
1274
1275 Input:
1276
1277 Output:
1278
1279 Input/Output:
1280
1281 Assumption:
1282
1283 */
1284 ----------------------------------------------------------------------
1285 PROCEDURE HoldEach IS
1286 ----------------------------------------------------------------------
1287
1288 l_debug_info VARCHAR2(200);
1289 l_receipts_required VARCHAR2(1);
1290
1291 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1292 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
1293 l_image_receipts_status ap_expense_report_headers.image_receipts_status%TYPE;
1294 l_source ap_expense_report_headers.source%TYPE;
1295 l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1296 l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1297 l_report_submitted_date ap_expense_report_headers.report_submitted_date%TYPE;
1298 l_org_id ap_expense_report_headers.org_id%TYPE;
1299
1300
1301 /*
1302 Criteria for this cursor is:
1303 - all payment held or pending holds clearance
1304 - excludes bothpay child reports
1305 - submitted within Hold Each scenario
1306 */
1307 ------------------------------------------------------------
1308 -- cursor for Hold Each scenario
1309 ------------------------------------------------------------
1310 CURSOR c_hold_each IS
1311 select aerh.report_header_id,
1312 aerh.receipts_status,
1313 aerh.image_receipts_status,
1314 aerh.source,
1315 aerh.expense_status_code,
1316 aerh.holding_report_header_id,
1317 aerh.report_submitted_date,
1318 aerh.org_id
1319 from AP_EXPENSE_REPORT_HEADERS aerh,
1320 AP_AUD_RULE_SETS rs,
1321 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1322 where aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
1323 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1324 and aerh.bothpay_parent_id is null
1325 and rsa.org_id = aerh.org_id
1326 and rsa.rule_set_id = rs.rule_set_id
1327 and rs.rule_set_type = C_HOLD_RULE
1328 and TRUNC(aerh.report_submitted_date)
1329 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
1330 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
1331 and rs.HOLD_CODE = C_HOLD_EACH_CODE;
1332
1333
1334 BEGIN
1335
1336 ------------------------------------------------------------
1337 l_debug_info := 'Determine whether to place Holds';
1338 fnd_file.put_line(fnd_file.log, l_debug_info);
1339 ------------------------------------------------------------
1340 open c_hold_each;
1341 loop
1342
1343 fetch c_hold_each into l_report_header_id,
1344 l_receipts_status,
1345 l_image_receipts_status,
1346 l_source,
1347 l_expense_status_code,
1348 l_holding_report_header_id,
1349 l_report_submitted_date,
1350 l_org_id;
1351 exit when c_hold_each%NOTFOUND;
1352
1353 l_receipts_required := GetReceiptsRequired(l_report_submitted_date, l_org_id);
1354
1355 if ((l_receipts_required in ('B', 'O', 'N') AND
1356 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)) OR
1357 (l_receipts_required = 'I' AND (nvl(l_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED) OR
1358 nvl(l_image_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
1359
1360 ------------------------------------------------------------
1361 l_debug_info := 'Receipts not required or have been received/waived';
1362 fnd_file.put_line(fnd_file.log, l_debug_info);
1363 ------------------------------------------------------------
1364 ------------------------------------------------------------
1365 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
1366 fnd_file.put_line(fnd_file.log, l_debug_info);
1367 ------------------------------------------------------------
1368 ReadyForPayment(l_report_header_id);
1369
1370 ------------------------------------------------------------
1371 l_debug_info := 'If previously Held, then raise Released event';
1372 ------------------------------------------------------------
1373 if (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
1374
1375 ------------------------------------------------------------
1376 l_debug_info := 'Raise hold released event for: '||l_report_header_id;
1377 ------------------------------------------------------------
1378 RaiseReleasedEvent(l_report_header_id);
1379
1380 end if;
1381
1382 /*elsif (nvl(l_image_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
1383
1384 ------------------------------------------------------------
1385 l_debug_info := 'Image Receipts not required or have been received/waived';
1386 fnd_file.put_line(fnd_file.log, l_debug_info);
1387 ------------------------------------------------------------
1388 ------------------------------------------------------------
1389 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
1390 fnd_file.put_line(fnd_file.log, l_debug_info);
1391 ------------------------------------------------------------
1392 ReadyForPayment(l_report_header_id);*/
1393
1394 elsif (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS or
1395 (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD and l_holding_report_header_id <> l_report_header_id)) then
1396
1397 ------------------------------------------------------------
1398 l_debug_info := 'Set status Payment Held and set holding_report_header_id to current report_header_id for: '||l_report_header_id;
1399 fnd_file.put_line(fnd_file.log, l_debug_info);
1400 ------------------------------------------------------------
1401 PlaceHold(l_report_header_id, l_report_header_id);
1402
1403 ------------------------------------------------------------
1404 l_debug_info := 'Raise hold placed event for: '||l_report_header_id;
1405 fnd_file.put_line(fnd_file.log, l_debug_info);
1406 ------------------------------------------------------------
1407 RaiseHeldEvent(l_report_header_id);
1408
1409 elsif (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD and
1410 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
1411
1412 ------------------------------------------------------------
1413 l_debug_info := 'Payment Held status untouched for: '||l_report_header_id;
1414 fnd_file.put_line(fnd_file.log, l_debug_info);
1415 ------------------------------------------------------------
1416
1417 else
1418
1419 ------------------------------------------------------------
1420 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
1421 fnd_file.put_line(fnd_file.log, l_debug_info);
1422 ------------------------------------------------------------
1423 ReadyForPayment(l_report_header_id);
1424
1425 end if;
1426
1427 end loop;
1428 close c_hold_each;
1429
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432 BEGIN
1433 IF ( SQLCODE <> -20001 )
1434 THEN
1435 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1436 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1437 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'HoldEach' );
1438 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1439 APP_EXCEPTION.RAISE_EXCEPTION;
1440 ELSE
1441 -- Do not need to set the token since it has been done in the
1442 -- child process
1443 RAISE;
1444 END IF;
1445 END;
1446
1447 END HoldEach;
1448
1449
1450 ------------------------------------------------------------------------
1451 FUNCTION GetOldestOverdueReceipts(
1452 p_employee_id IN NUMBER,
1453 p_hold_rct_overdue_days IN NUMBER) RETURN NUMBER IS
1454 ------------------------------------------------------------------------
1455 l_debug_info VARCHAR2(200);
1456
1457 l_overdue_report_header_id number;
1458
1459 /*
1460 Criteria for this cursor is:
1461 - all submitted reports
1462 - excludes bothpay child reports
1463 - receipts are grossly overdue
1464 */
1465 ------------------------------------------------------------
1466 -- cursor for oldest overdue receipts
1467 -- NOTE: need index on aerh.receipts_status/employee_id
1468 ------------------------------------------------------------
1469 CURSOR c_oldest_overdue_receipts IS
1470 select aerh.report_header_id
1471 from AP_EXPENSE_REPORT_HEADERS aerh
1472 where (aerh.source <> 'NonValidatedWebExpense' or aerh.workflow_approved_flag is null)
1473 and aerh.receipts_status is not null
1474 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)
1475 -- and aerh.expense_status_code not in (AP_WEB_RECEIPTS_WF.C_INVOICED, AP_WEB_RECEIPTS_WF.C_PARPAID, AP_WEB_RECEIPTS_WF.C_PAID)
1476 and aerh.bothpay_parent_id is null
1477 and trunc(sysdate) - (trunc(aerh.report_submitted_date) + p_hold_rct_overdue_days) > 0
1478 and aerh.employee_id = p_employee_id
1479 order by aerh.report_submitted_date asc;
1480
1481 BEGIN
1482
1483 ------------------------------------------------------------
1484 l_debug_info := 'Get Oldest Overdue Receipts';
1485 fnd_file.put_line(fnd_file.log, l_debug_info);
1486 ------------------------------------------------------------
1487 open c_oldest_overdue_receipts;
1488 fetch c_oldest_overdue_receipts into l_overdue_report_header_id;
1489 close c_oldest_overdue_receipts;
1490
1491 return l_overdue_report_header_id;
1492
1493 EXCEPTION
1494 WHEN NO_DATA_FOUND THEN
1495 return null;
1496 WHEN OTHERS THEN
1497 BEGIN
1498 IF ( SQLCODE <> -20001 )
1499 THEN
1500 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1501 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1502 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'GetOldestOverdueReceipts' );
1503 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1504 APP_EXCEPTION.RAISE_EXCEPTION;
1505 ELSE
1506 -- Do not need to set the token since it has been done in the
1507 -- child process
1508 RAISE;
1509 END IF;
1510 END;
1511
1512 END GetOldestOverdueReceipts;
1513
1514
1515 ------------------------------------------------------------------------
1516 FUNCTION GetOldestImgOverdueReceipts(
1517 p_employee_id IN NUMBER,
1518 p_hold_rct_overdue_days IN NUMBER) RETURN NUMBER IS
1519 ------------------------------------------------------------------------
1520 l_debug_info VARCHAR2(200);
1521
1522 l_overdue_report_header_id number;
1523
1524 /*
1525 Criteria for this cursor is:
1526 - all submitted reports
1527 - excludes bothpay child reports
1528 - receipts are grossly overdue
1529 */
1530 ------------------------------------------------------------
1531 -- cursor for oldest overdue receipts
1532 -- NOTE: need index on aerh.receipts_status/employee_id
1533 ------------------------------------------------------------
1534 CURSOR c_oldest_overdue_receipts IS
1535 select aerh.report_header_id
1536 from AP_EXPENSE_REPORT_HEADERS aerh
1537 where (aerh.source <> 'NonValidatedWebExpense' or aerh.workflow_approved_flag is null)
1538 and aerh.image_receipts_status is not null
1539 and aerh.image_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)
1540 -- and aerh.expense_status_code not in (AP_WEB_RECEIPTS_WF.C_INVOICED, AP_WEB_RECEIPTS_WF.C_PARPAID, AP_WEB_RECEIPTS_WF.C_PAID)
1541 and aerh.receipts_status is not null
1542 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)
1543 and aerh.bothpay_parent_id is null
1544 and trunc(sysdate) - (trunc(aerh.report_submitted_date) + p_hold_rct_overdue_days) > 0
1545 and aerh.employee_id = p_employee_id
1546 order by aerh.report_submitted_date asc;
1547
1548 BEGIN
1549
1550 ------------------------------------------------------------
1551 l_debug_info := 'Get Oldest Overdue Receipts';
1552 fnd_file.put_line(fnd_file.log, l_debug_info);
1553 ------------------------------------------------------------
1554 open c_oldest_overdue_receipts;
1555 fetch c_oldest_overdue_receipts into l_overdue_report_header_id;
1556 close c_oldest_overdue_receipts;
1557
1558 return l_overdue_report_header_id;
1559
1560 EXCEPTION
1561 WHEN NO_DATA_FOUND THEN
1562 return null;
1563 WHEN OTHERS THEN
1564 BEGIN
1565 IF ( SQLCODE <> -20001 )
1566 THEN
1567 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1568 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1569 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'GetOldestImgOverdueReceipts' );
1570 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1571 APP_EXCEPTION.RAISE_EXCEPTION;
1572 ELSE
1573 -- Do not need to set the token since it has been done in the
1574 -- child process
1575 RAISE;
1576 END IF;
1577 END;
1578
1579 END GetOldestImgOverdueReceipts;
1580
1581
1582
1583 /*
1584 Written by:
1585 Ron Langi
1586
1587 Purpose:
1588 The purpose of this is to hold the current expense report if the employee has any grossly overdue receipts
1589
1590 The following is the PL/SQL logic invoked by the Invoice Import program for the Hold All scenario:
1591
1592 1. For each expense report that is 'Payment Held' or 'Pending Holds Clearance':
1593
1594 If there is an expense report with grossly overdue receipts and it is currently not being held for it:
1595 a. Mark as 'Payment Held'
1596 b. Set the HOLDING_REPORT_HEADER_ID to the expense report with grossly overdue receipts
1597 c. Raise the Expense Held event
1598
1599 If there is no expense report with grossly overdue receipts:
1600 a. Mark as 'Ready for Payment'
1601 b. Raise the Expense Released event if previously held
1602
1603
1604 Input:
1605
1606 Output:
1607
1608 Input/Output:
1609
1610 Assumption:
1611
1612 */
1613 ----------------------------------------------------------------------
1614 PROCEDURE HoldAll IS
1615 ----------------------------------------------------------------------
1616
1617 l_debug_info VARCHAR2(200);
1618
1619 l_overdue_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1620 l_overdue_img_report_hdr_id ap_expense_report_headers.report_header_id%TYPE;
1621 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1622 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
1623 l_source ap_expense_report_headers.source%TYPE;
1624 l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1625 l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1626 l_employee_id ap_expense_report_headers.employee_id%TYPE;
1627 l_hold_rct_overdue_days ap_aud_rule_sets.hold_rct_overdue_days%TYPE;
1628
1629
1630 /*
1631 Criteria for this cursor is:
1632 - all payment held or pending holds clearance
1633 - excludes bothpay child reports
1634 - submitted within Hold All scenario
1635 */
1636 ------------------------------------------------------------
1637 -- cursor for Hold All scenario
1638 ------------------------------------------------------------
1639 CURSOR c_hold_all IS
1640 select aerh.report_header_id,
1641 aerh.receipts_status,
1642 aerh.source,
1643 aerh.expense_status_code,
1644 aerh.holding_report_header_id,
1645 aerh.employee_id,
1646 rs.hold_rct_overdue_days
1647 from AP_EXPENSE_REPORT_HEADERS aerh,
1648 AP_AUD_RULE_SETS rs,
1649 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1650 where aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
1651 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1652 and aerh.bothpay_parent_id is null
1653 and rsa.org_id = aerh.org_id
1654 and rsa.rule_set_id = rs.rule_set_id
1655 and rs.rule_set_type = C_HOLD_RULE
1656 and TRUNC(aerh.report_submitted_date)
1657 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
1658 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
1659 and rs.HOLD_CODE = C_HOLD_ALL_CODE;
1660
1661 BEGIN
1662
1663 ------------------------------------------------------------
1664 l_debug_info := 'Determine whether to place Holds';
1665 fnd_file.put_line(fnd_file.log, l_debug_info);
1666 ------------------------------------------------------------
1667 open c_hold_all;
1668 loop
1669
1670 fetch c_hold_all into l_report_header_id,
1671 l_receipts_status,
1672 l_source,
1673 l_expense_status_code,
1674 l_holding_report_header_id,
1675 l_employee_id,
1676 l_hold_rct_overdue_days;
1677 exit when c_hold_all%NOTFOUND;
1678
1679 ------------------------------------------------------------
1680 l_debug_info := 'Get oldest overdue receipts for employee: '||to_char(l_employee_id);
1681 fnd_file.put_line(fnd_file.log, l_debug_info);
1682 ------------------------------------------------------------
1683 l_overdue_report_header_id := GetOldestOverdueReceipts(l_employee_id, l_hold_rct_overdue_days);
1684
1685 l_overdue_img_report_hdr_id := GetOldestImgOverdueReceipts(l_employee_id, l_hold_rct_overdue_days);
1686
1687 ------------------------------------------------------------
1688 l_debug_info := 'Oldest overdue receipts is: '||to_char(l_overdue_report_header_id);
1689 fnd_file.put_line(fnd_file.log, l_debug_info);
1690 ------------------------------------------------------------
1691
1692 ------------------------------------------------------------
1693 l_debug_info := 'Oldest overdue image receipts is: '||to_char(l_overdue_img_report_hdr_id);
1694 fnd_file.put_line(fnd_file.log, l_debug_info);
1695 ------------------------------------------------------------
1696
1697 if (l_overdue_report_header_id is not null) then
1698
1699 ------------------------------------------------------------
1700 l_debug_info := 'Current holding report is: '||to_char(l_holding_report_header_id);
1701 fnd_file.put_line(fnd_file.log, l_debug_info);
1702 ------------------------------------------------------------
1703
1704 if (l_overdue_report_header_id <> nvl(l_holding_report_header_id, 0)) then
1705
1706 ------------------------------------------------------------
1707 l_debug_info := 'Place Hold';
1708 fnd_file.put_line(fnd_file.log, l_debug_info);
1709 ------------------------------------------------------------
1710
1711 ------------------------------------------------------------
1712 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;
1713 fnd_file.put_line(fnd_file.log, l_debug_info);
1714 ------------------------------------------------------------
1715 PlaceHold(l_report_header_id, l_overdue_report_header_id);
1716
1717 ------------------------------------------------------------
1718 l_debug_info := 'Raise hold placed event for: '||l_report_header_id;
1719 fnd_file.put_line(fnd_file.log, l_debug_info);
1720 ------------------------------------------------------------
1721 RaiseHeldEvent(l_report_header_id);
1722
1723 end if;
1724 elsif (l_overdue_img_report_hdr_id is not null) then
1725
1726 ------------------------------------------------------------
1727 l_debug_info := 'Current holding report is: '||to_char(l_holding_report_header_id);
1728 fnd_file.put_line(fnd_file.log, l_debug_info);
1729 ------------------------------------------------------------
1730
1731 if (l_overdue_img_report_hdr_id <> nvl(l_holding_report_header_id, 0)) then
1732
1733 ------------------------------------------------------------
1734 l_debug_info := 'Place Hold';
1735 fnd_file.put_line(fnd_file.log, l_debug_info);
1736 ------------------------------------------------------------
1737
1738 ------------------------------------------------------------
1739 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_img_report_hdr_id;
1740 fnd_file.put_line(fnd_file.log, l_debug_info);
1741 ------------------------------------------------------------
1742 PlaceHold(l_report_header_id, l_overdue_img_report_hdr_id);
1743
1744 ------------------------------------------------------------
1745 l_debug_info := 'Raise hold placed event for: '||l_report_header_id;
1746 fnd_file.put_line(fnd_file.log, l_debug_info);
1747 ------------------------------------------------------------
1748 RaiseHeldEvent(l_report_header_id);
1749
1750 end if;
1751
1752 else
1753
1754 ------------------------------------------------------------
1755 l_debug_info := 'Mark Ready for Payment';
1756 fnd_file.put_line(fnd_file.log, l_debug_info);
1757 ------------------------------------------------------------
1758
1759 ------------------------------------------------------------
1760 l_debug_info := 'Set status Ready for Payment and clear holding_report_header_id for: '||l_report_header_id;
1761 fnd_file.put_line(fnd_file.log, l_debug_info);
1762 ------------------------------------------------------------
1763 ReadyForPayment(l_report_header_id);
1764
1765 ------------------------------------------------------------
1766 l_debug_info := 'If previously Held, then raise Released event';
1767 ------------------------------------------------------------
1768 if (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
1769
1770 ------------------------------------------------------------
1771 l_debug_info := 'Raise hold released event for: '||l_report_header_id;
1772 fnd_file.put_line(fnd_file.log, l_debug_info);
1773 ------------------------------------------------------------
1774 RaiseReleasedEvent(l_report_header_id);
1775
1776 end if;
1777
1778 end if;
1779
1780 end loop;
1781 close c_hold_all;
1782
1783 EXCEPTION
1784 WHEN OTHERS THEN
1785 BEGIN
1786 IF ( SQLCODE <> -20001 )
1787 THEN
1788 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1789 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1790 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'HoldAll' );
1791 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1792 APP_EXCEPTION.RAISE_EXCEPTION;
1793 ELSE
1794 -- Do not need to set the token since it has been done in the
1795 -- child process
1796 RAISE;
1797 END IF;
1798 END;
1799
1800
1801 END HoldAll;
1802
1803
1804 ------------------------------------------------------------------------
1805 FUNCTION IsCCReceiptsRequired(
1806 p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
1807 ------------------------------------------------------------------------
1808 l_debug_info VARCHAR2(200);
1809
1810 l_is_cc_receipts_required VARCHAR2(1);
1811
1812 BEGIN
1813
1814 ------------------------------------------------------------
1815 l_debug_info := 'Check if Credit Card Receipts are required for: '||p_report_header_id;
1816 fnd_file.put_line(fnd_file.log, l_debug_info);
1817 ------------------------------------------------------------
1818 select 'Y'
1819 into l_is_cc_receipts_required
1820 from AP_EXPENSE_REPORT_HEADERS aerh
1821 where aerh.report_header_id = p_report_header_id
1822 and
1823 exists
1824 (select 1
1825 from ap_expense_report_lines aerl
1826 where aerl.report_header_id = aerh.report_header_id
1827 and aerl.credit_card_trx_id is not null
1828 and nvl(aerl.receipt_required_flag, 'N') = 'Y'
1829 and rownum = 1
1830 )
1831 and rownum = 1;
1832
1833 return l_is_cc_receipts_required;
1834
1835 EXCEPTION
1836 WHEN NO_DATA_FOUND THEN
1837 return 'N';
1838 WHEN OTHERS THEN
1839 BEGIN
1840 IF ( SQLCODE <> -20001 )
1841 THEN
1842 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1843 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1844 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'IsCCReceiptsRequired' );
1845 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1846 APP_EXCEPTION.RAISE_EXCEPTION;
1847 ELSE
1848 -- Do not need to set the token since it has been done in the
1849 -- child process
1850 RAISE;
1851 END IF;
1852 END;
1853
1854 END IsCCReceiptsRequired;
1855
1856
1857 ------------------------------------------------------------------------
1858 FUNCTION IsCCImgReceiptsRequired(
1859 p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
1860 ------------------------------------------------------------------------
1861 l_debug_info VARCHAR2(200);
1862
1863 l_is_cc_receipts_required VARCHAR2(1);
1864
1865 BEGIN
1866
1867 ------------------------------------------------------------
1868 l_debug_info := 'Check if Credit Card Receipts are required for: '||p_report_header_id;
1869 fnd_file.put_line(fnd_file.log, l_debug_info);
1870 ------------------------------------------------------------
1871 select 'Y'
1872 into l_is_cc_receipts_required
1873 from AP_EXPENSE_REPORT_HEADERS aerh
1874 where aerh.report_header_id = p_report_header_id
1875 and
1876 exists
1877 (select 1
1878 from ap_expense_report_lines aerl
1879 where aerl.report_header_id = aerh.report_header_id
1880 and aerl.credit_card_trx_id is not null
1881 and nvl(aerl.image_receipt_required_flag, 'N') = 'Y'
1882 and rownum = 1
1883 )
1884 and rownum = 1;
1885
1886 return l_is_cc_receipts_required;
1887
1888 EXCEPTION
1889 WHEN NO_DATA_FOUND THEN
1890 return 'N';
1891 WHEN OTHERS THEN
1892 BEGIN
1893 IF ( SQLCODE <> -20001 )
1894 THEN
1895 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
1896 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
1897 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'IsCCReceiptsRequired' );
1898 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
1899 APP_EXCEPTION.RAISE_EXCEPTION;
1900 ELSE
1901 -- Do not need to set the token since it has been done in the
1902 -- child process
1903 RAISE;
1904 END IF;
1905 END;
1906
1907 END IsCCImgReceiptsRequired;
1908
1909
1910 /*
1911 Written by:
1912 Ron Langi
1913
1914 Purpose:
1915 The purpose of this is to hold the Both Pay Credit Card Expenses, if setup that way.
1916
1917 The following shows the PL/SQL logic invoked by the Invoice Import program for the Both Pay Credit Card Expenses.
1918
1919 1. For each expense report that is 'Payment Held' or 'Pending Holds Clearance':
1920
1921 If the receipts are not required or have been received/waived and rule is not set to 'Always'
1922 a. Mark as 'Ready for Payment'
1923
1924 If the rule is set to 'Never':
1925 a. Mark as 'Ready for Payment'
1926
1927 If the rule is set to 'If Receipts Required' and the credit card receipts are not required or have been received/waived:
1928 a. Mark as 'Ready for Payment'
1929
1930 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:
1931 a. Mark as 'Payment Held'
1932 b. Set the HOLDING_REPORT_HEADER_ID to the parent expense report
1933
1934 If the rule is set to 'Always' and the parent expense report's status is invoiced
1935 a. Mark as 'Ready for Payment'
1936
1937 If the rule is set to 'Always' and the parent expense report's status is payment held
1938 a. Mark as 'Payment Held'
1939 b. Set the HOLDING_REPORT_HEADER_ID to the parent expense report
1940
1941
1942 Input:
1943
1944 Output:
1945
1946 Input/Output:
1947
1948 Assumption:
1949
1950 */
1951 ------------------------------------------------------------------------
1952 PROCEDURE HoldBothPay IS
1953 ------------------------------------------------------------------------
1954
1955 l_debug_info VARCHAR2(200);
1956
1957 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
1958 l_bothpay_parent_id ap_expense_report_headers.bothpay_parent_id%TYPE;
1959 l_receipts_status ap_expense_report_headers.receipts_status%TYPE;
1960 l_image_receipts_status ap_expense_report_headers.receipts_status%TYPE;
1961 l_parent_status ap_expense_report_headers.expense_status_code%TYPE;
1962 l_source ap_expense_report_headers.source%TYPE;
1963 l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1964 l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1965 l_hold_rct_overdue_bp_cc_code ap_aud_rule_sets.hold_rct_overdue_bp_cc_code%TYPE;
1966
1967 /*
1968 Criteria for this cursor is:
1969 - source is 'SelfService' or 'Both Pay'
1970 - all payment held or pending holds clearance
1971 - only bothpay child reports
1972 - submitted within Hold Each or All scenario
1973 */
1974 ------------------------------------------------------------
1975 -- cursor for holds in Both Pay scenario
1976 ------------------------------------------------------------
1977 CURSOR c_hold_bothpay IS
1978 select aerh.report_header_id,
1979 aerh.bothpay_parent_id,
1980 aerh2.receipts_status,
1981 aerh2.image_receipts_status,
1982 aerh2.expense_status_code,
1983 aerh.source,
1984 aerh.expense_status_code,
1985 aerh.holding_report_header_id,
1986 rs.hold_rct_overdue_bp_cc_code
1987 from AP_EXPENSE_REPORT_HEADERS aerh,
1988 AP_EXPENSE_REPORT_HEADERS aerh2,
1989 AP_AUD_RULE_SETS rs,
1990 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1991 where aerh.source in (AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE, AP_WEB_RECEIPTS_WF.C_BOTHPAY)
1992 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1993 and aerh.bothpay_parent_id is not null
1994 and aerh2.report_header_id = aerh.bothpay_parent_id
1995 and rsa.org_id = aerh2.org_id
1996 and rsa.rule_set_id = rs.rule_set_id
1997 and rs.rule_set_type = C_HOLD_RULE
1998 and TRUNC(aerh2.report_submitted_date)
1999 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh2.report_submitted_date))
2000 AND TRUNC(NVL(rsa.END_DATE, aerh2.report_submitted_date))
2001 and rs.HOLD_CODE in (C_HOLD_EACH_CODE, C_HOLD_ALL_CODE);
2002
2003
2004 BEGIN
2005
2006 ------------------------------------------------------------
2007 l_debug_info := 'Determine whether to place Holds';
2008 fnd_file.put_line(fnd_file.log, l_debug_info);
2009 ------------------------------------------------------------
2010 open c_hold_bothpay;
2011 loop
2012
2013 fetch c_hold_bothpay into l_report_header_id,
2014 l_bothpay_parent_id,
2015 l_receipts_status,
2016 l_image_receipts_status,
2017 l_parent_status,
2018 l_source,
2019 l_expense_status_code,
2020 l_holding_report_header_id,
2021 l_hold_rct_overdue_bp_cc_code;
2022 exit when c_hold_bothpay%NOTFOUND;
2023
2024 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
2025 nvl(l_image_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
2026 l_hold_rct_overdue_bp_cc_code <> C_HOLD_BP_ALWAYS) then
2027 ------------------------------------------------------------
2028 l_debug_info := 'Receipts not required or have been received/waived and not Hold Always';
2029 fnd_file.put_line(fnd_file.log, l_debug_info);
2030 ------------------------------------------------------------
2031
2032 ------------------------------------------------------------
2033 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
2034 fnd_file.put_line(fnd_file.log, l_debug_info);
2035 ------------------------------------------------------------
2036 ReadyForPayment(l_report_header_id);
2037
2038 /*elsif (nvl(l_image_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
2039 l_hold_rct_overdue_bp_cc_code <> C_HOLD_BP_ALWAYS) then
2040 ------------------------------------------------------------
2041 l_debug_info := 'Receipts not required or have been received/waived and not Hold Always';
2042 fnd_file.put_line(fnd_file.log, l_debug_info);
2043 ------------------------------------------------------------
2044
2045 ------------------------------------------------------------
2046 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
2047 fnd_file.put_line(fnd_file.log, l_debug_info);
2048 ------------------------------------------------------------
2049 ReadyForPayment(l_report_header_id);*/
2050
2051 elsif (l_hold_rct_overdue_bp_cc_code = C_HOLD_BP_NEVER) then
2052 ------------------------------------------------------------
2053 l_debug_info := 'Never Hold BothPay';
2054 fnd_file.put_line(fnd_file.log, l_debug_info);
2055 ------------------------------------------------------------
2056
2057 ------------------------------------------------------------
2058 l_debug_info := 'Set status Ready for Payment for: '||l_report_header_id;
2059 fnd_file.put_line(fnd_file.log, l_debug_info);
2060 ------------------------------------------------------------
2061 ReadyForPayment(l_report_header_id);
2062
2063 elsif (l_hold_rct_overdue_bp_cc_code = C_HOLD_BP_REQUIRED) then
2064 ------------------------------------------------------------
2065 l_debug_info := 'Hold BothPay If Required';
2066 fnd_file.put_line(fnd_file.log, l_debug_info);
2067 ------------------------------------------------------------
2068
2069 if (IsCCReceiptsRequired(l_bothpay_parent_id) = 'Y' and
2070 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
2071
2072 if (l_expense_status_code <> AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
2073
2074 ------------------------------------------------------------
2075 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;
2076 fnd_file.put_line(fnd_file.log, l_debug_info);
2077 ------------------------------------------------------------
2078 PlaceHold(l_report_header_id, l_bothpay_parent_id);
2079
2080 end if;
2081
2082 elsif (IsCCImgReceiptsRequired(l_bothpay_parent_id) = 'Y' and
2083 nvl(l_image_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
2084
2085 if (l_expense_status_code <> AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
2086
2087 ------------------------------------------------------------
2088 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;
2089 fnd_file.put_line(fnd_file.log, l_debug_info);
2090 ------------------------------------------------------------
2091 PlaceHold(l_report_header_id, l_bothpay_parent_id);
2092
2093 end if;
2094
2095 else
2096
2097 ------------------------------------------------------------
2098 l_debug_info := 'Set status Ready for Payment Held for: '||l_report_header_id;
2099 fnd_file.put_line(fnd_file.log, l_debug_info);
2100 ------------------------------------------------------------
2101 ReadyForPayment(l_report_header_id);
2102
2103 end if; -- Hold BothPay If Required
2104
2105 elsif (l_hold_rct_overdue_bp_cc_code = C_HOLD_BP_ALWAYS) then
2106 ------------------------------------------------------------
2107 l_debug_info := 'Hold BothPay Always';
2108 fnd_file.put_line(fnd_file.log, l_debug_info);
2109 ------------------------------------------------------------
2110
2111 if (l_parent_status = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
2112
2113 ------------------------------------------------------------
2114 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;
2115 fnd_file.put_line(fnd_file.log, l_debug_info);
2116 ------------------------------------------------------------
2117 PlaceHold(l_report_header_id, l_bothpay_parent_id);
2118
2119 else
2120
2121 ------------------------------------------------------------
2122 l_debug_info := 'Set status Ready for Payment Held for: '||l_report_header_id;
2123 fnd_file.put_line(fnd_file.log, l_debug_info);
2124 ------------------------------------------------------------
2125 ReadyForPayment(l_report_header_id);
2126
2127 end if; -- Hold BothPay Always
2128
2129 else
2130
2131 ------------------------------------------------------------
2132 l_debug_info := 'I do not understand hold scenario: '||l_hold_rct_overdue_bp_cc_code;
2133 fnd_file.put_line(fnd_file.log, l_debug_info);
2134 ------------------------------------------------------------
2135
2136 end if;
2137
2138 end loop;
2139 close c_hold_bothpay;
2140
2141
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 BEGIN
2145 IF ( SQLCODE <> -20001 )
2146 THEN
2147 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
2148 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
2149 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'HoldBothPay' );
2150 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
2151 APP_EXCEPTION.RAISE_EXCEPTION;
2152 ELSE
2153 -- Do not need to set the token since it has been done in the
2154 -- child process
2155 RAISE;
2156 END IF;
2157 END;
2158
2159 END HoldBothPay;
2160
2161
2162 /*
2163 Written by:
2164 Ron Langi
2165
2166 Purpose:
2167 The purpose of this is to release any holds on expense reports where:
2168 1. hold rules no longer apply
2169 2. hold was performed on an original report containing only Both Pay credit card trxns whose
2170 Both Pay report is invoiced.
2171
2172 The following is the PL/SQL logic invoked by the Invoice Import program for non-existent holds :
2173 a. Mark as 'Ready for Payment'
2174 b. Clear the HOLDING_REPORT_HEADER_ID
2175 c. Raise the Expense Released event if previously held
2176
2177
2178
2179 Input:
2180
2181 Output:
2182
2183 Input/Output:
2184
2185 Assumption:
2186
2187 */
2188 ------------------------------------------------------------------------
2189 PROCEDURE ObsoleteHold 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_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
2197 l_source ap_expense_report_headers.source%TYPE;
2198 l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
2199
2200
2201 /*
2202 Criteria for this cursor is:
2203 - source is 'SelfService' or 'Both Pay'
2204 - all payment held or pending holds clearance
2205 - not submitted within Hold Each or All scenario
2206 */
2207 ------------------------------------------------------------
2208 -- cursor for obsolete Holds
2209 ------------------------------------------------------------
2210 CURSOR c_obsolete_holds IS
2211 select aerh.report_header_id,
2212 aerh.source,
2213 aerh.expense_status_code
2214 from AP_EXPENSE_REPORT_HEADERS aerh
2215 where aerh.source in (AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE, AP_WEB_RECEIPTS_WF.C_BOTHPAY)
2216 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
2217 and
2218 not exists
2219 (select 1
2220 from AP_AUD_RULE_SETS rs,
2221 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
2222 where rsa.org_id = aerh.org_id
2223 and rsa.rule_set_id = rs.rule_set_id
2224 and rs.rule_set_type = C_HOLD_RULE
2225 and TRUNC(aerh.report_submitted_date)
2226 BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
2227 AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
2228 and rownum = 1
2229 );
2230
2231 /*
2232 Criteria for this cursor is:
2233 - source is 'SelfService'
2234 - all payment held or pending holds clearance
2235 - original reports containing only Both Pay credit card trxns
2236 */
2237 ------------------------------------------------------------
2238 -- cursor for obsolete Both Pay Holds
2239 ------------------------------------------------------------
2240 CURSOR c_obsolete_bothpay_holds IS
2241 select aerh.report_header_id,
2242 aerh.expense_status_code
2243 from AP_EXPENSE_REPORT_HEADERS aerh
2244 where aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
2245 and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
2246 and aerh.bothpay_parent_id is null
2247 and
2248 not exists
2249 (select 1
2250 from ap_expense_report_lines aerl
2251 where aerl.report_header_id = aerh.report_header_id
2252 and aerl.credit_card_trx_id IS NULL
2253 and rownum = 1)
2254 and
2255 exists
2256 (select 1
2257 from ap_expense_report_headers aerh2
2258 where aerh2.bothpay_parent_id = aerh.report_header_id
2259 and aerh2.expense_status_code = AP_WEB_RECEIPTS_WF.C_INVOICED
2260 and rownum = 1);
2261
2262
2263 BEGIN
2264
2265 ------------------------------------------------------------
2266 l_debug_info := 'Obsolete Holds';
2267 fnd_file.put_line(fnd_file.log, l_debug_info);
2268 ------------------------------------------------------------
2269 open c_obsolete_holds;
2270 loop
2271
2272 fetch c_obsolete_holds into l_report_header_id,
2273 l_source,
2274 l_expense_status_code;
2275 exit when c_obsolete_holds%NOTFOUND;
2276
2277 ------------------------------------------------------------
2278 l_debug_info := 'Mark Ready for Payment';
2279 fnd_file.put_line(fnd_file.log, l_debug_info);
2280 ------------------------------------------------------------
2281
2282 ------------------------------------------------------------
2283 l_debug_info := 'Set status Ready for Payment and clear holding_report_header_id for: '||l_report_header_id;
2284 fnd_file.put_line(fnd_file.log, l_debug_info);
2285 ------------------------------------------------------------
2286 ReadyForPayment(l_report_header_id);
2287
2288 ------------------------------------------------------------
2289 l_debug_info := 'If not Both Pay child and previously Held, then raise Released event';
2290 ------------------------------------------------------------
2291 if (l_source <> AP_WEB_RECEIPTS_WF.C_BOTHPAY and
2292 l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
2293
2294 ------------------------------------------------------------
2295 l_debug_info := 'Raise hold released event for: '||l_report_header_id;
2296 fnd_file.put_line(fnd_file.log, l_debug_info);
2297 ------------------------------------------------------------
2298 RaiseReleasedEvent(l_report_header_id);
2299
2300 end if;
2301
2302 end loop;
2303 close c_obsolete_holds;
2304
2305
2306 ------------------------------------------------------------
2307 l_debug_info := 'Obsolete Both Pay Holds';
2308 fnd_file.put_line(fnd_file.log, l_debug_info);
2309 ------------------------------------------------------------
2310 open c_obsolete_bothpay_holds;
2311 loop
2312
2313 fetch c_obsolete_bothpay_holds into l_report_header_id,
2314 l_expense_status_code;
2315 exit when c_obsolete_bothpay_holds%NOTFOUND;
2316
2317 ------------------------------------------------------------
2318 l_debug_info := 'Mark Ready for Payment';
2319 fnd_file.put_line(fnd_file.log, l_debug_info);
2320 ------------------------------------------------------------
2321
2322 ------------------------------------------------------------
2323 l_debug_info := 'Set status Ready for Payment and clear holding_report_header_id for: '||l_report_header_id;
2324 fnd_file.put_line(fnd_file.log, l_debug_info);
2325 ------------------------------------------------------------
2326 ReadyForPayment(l_report_header_id);
2327
2328 ------------------------------------------------------------
2329 l_debug_info := 'If previously Held, then raise Released event';
2330 ------------------------------------------------------------
2331 if (l_expense_status_code = AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD) then
2332
2333 ------------------------------------------------------------
2334 l_debug_info := 'Raise hold released event for: '||l_report_header_id;
2335 fnd_file.put_line(fnd_file.log, l_debug_info);
2336 ------------------------------------------------------------
2337 RaiseReleasedEvent(l_report_header_id);
2338
2339 end if;
2340
2341 end loop;
2342 close c_obsolete_bothpay_holds;
2343
2344
2345 EXCEPTION
2346 WHEN OTHERS THEN
2347 BEGIN
2348 IF ( SQLCODE <> -20001 )
2349 THEN
2350 FND_MESSAGE.SET_NAME( 'SQLAP', 'AP_DEBUG' );
2351 FND_MESSAGE.SET_TOKEN( 'ERROR', SQLERRM );
2352 FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'ObsoleteHold' );
2353 FND_MESSAGE.SET_TOKEN( 'DEBUG_INFO', l_debug_info );
2354 APP_EXCEPTION.RAISE_EXCEPTION;
2355 ELSE
2356 -- Do not need to set the token since it has been done in the
2357 -- child process
2358 RAISE;
2359 END IF;
2360 END;
2361
2362 END ObsoleteHold;
2363
2364
2365 FUNCTION GetReceiptsRequired(p_report_date IN DATE, p_org_id IN NUMBER) RETURN VARCHAR2 IS
2366
2367 CURSOR ReceiptRuleCur IS
2368 SELECT rs.*
2369 FROM AP_AUD_RULE_SETS rs,
2370 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
2371 WHERE rsa.org_id = p_org_id
2372 AND rsa.rule_set_id = rs.rule_set_id
2373 AND rs.rule_set_type = 'RECEIPT'
2374 AND TRUNC(p_report_date) BETWEEN TRUNC(NVL(rsa.start_date,p_report_date)) AND TRUNC(NVL(rsa.end_date,p_report_date));
2375
2376 ReceiptRuleRec ReceiptRuleCur%ROWTYPE;
2377 BEGIN
2378
2379 IF (p_report_date IS NULL OR p_org_id IS NULL) THEN
2380 RETURN 'N';
2381 END IF;
2382
2383 OPEN ReceiptRuleCur;
2384 FETCH ReceiptRuleCur INTO ReceiptRuleRec;
2385
2386 IF ReceiptRuleCur%FOUND THEN
2387 IF (ReceiptRuleRec.ORIG_RECEIPT_REQ = 'Y' AND ReceiptRuleRec.IMAGE_RECEIPT_REQ = 'Y') THEN
2388 RETURN 'B';
2389 ELSIF (ReceiptRuleRec.ORIG_RECEIPT_REQ = 'Y') THEN
2390 RETURN 'O';
2391 ELSIF (ReceiptRuleRec.IMAGE_RECEIPT_REQ = 'Y') THEN
2392 RETURN 'I';
2393 ELSE
2394 RETURN 'N';
2395 END IF;
2396 END IF;
2397
2398 CLOSE ReceiptRuleCur;
2399
2400 RETURN 'N';
2401
2402 END GetReceiptsRequired;
2403
2404
2405 END AP_WEB_HOLDS_WF;