[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_EXPENSE_CUST_WF
Source
1 PACKAGE BODY AP_WEB_EXPENSE_CUST_WF AS
2 /* $Header: apwxwfcb.pls 120.17 2006/08/10 21:12:45 skoukunt ship $ */
3
4 ----------------------------------------------------------------------
5 PROCEDURE CustomValidateExpenseReport(p_item_type IN VARCHAR2,
6 p_item_key IN VARCHAR2,
7 p_actid IN NUMBER,
8 p_funmode IN VARCHAR2,
9 p_result OUT NOCOPY VARCHAR2) IS
10 ----------------------------------------------------------------------
11 l_return_error_message VARCHAR2(2000);
12 l_report_header_id NUMBER;
13 l_debug_info VARCHAR2(200);
14 BEGIN
15
16 IF (p_funmode = 'RUN') THEN
17
18 ------------------------------------------------------------
19 l_debug_info := 'Retrieve Expense_Report_ID Item Attribute';
20 ------------------------------------------------------------
21 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
22 p_item_key,
23 'EXPENSE_REPORT_ID');
24
25 ------------------------------------------
26 l_debug_info := 'Call DoCustomValidation';
27 ------------------------------------------
28 DoCustomValidation(l_report_header_id,
29 l_return_error_message);
30
31
32 IF (l_return_error_message IS NULL) THEN
33 p_result := 'COMPLETE:AP_PASS';
34 ELSE
35
36 WF_ENGINE.SetItemAttrText(p_item_type,
37 p_item_key,
38 'ERROR_MESSAGE',
39 l_return_error_message);
40
41 p_result := 'COMPLETE:AP_FAIL';
42 END IF;
43
44 ELSIF (p_funmode = 'CANCEL') THEN
45 NULL;
46 END IF;
47
48 EXCEPTION
49 WHEN OTHERS THEN
50 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'CustomValidateExpenseReport',
51 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
52 raise;
53 END CustomValidateExpenseReport;
54
55 ---------------------------------------------------------------------------
56 PROCEDURE DoCustomValidation(p_report_header_id IN NUMBER,
57 p_return_error_message IN OUT NOCOPY VARCHAR2) IS
58 ---------------------------------------------------------------------------
59 l_debug_info VARCHAR2(200);
60 BEGIN
61
62 p_return_error_message := NULL;
63
64 EXCEPTION
65 WHEN OTHERS THEN
66 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'DoCustomValidation',
67 null, null, null, l_debug_info);
68 raise;
69 END DoCustomValidation;
70
71
72 ---------------------------------------------------------------------------
73 PROCEDURE GetApprover(p_employee_id IN NUMBER,
74 p_emp_cost_center IN VARCHAR2,
75 p_doc_cost_center IN VARCHAR2,
76 p_approval_amount IN NUMBER,
77 p_item_key IN VARCHAR2,
78 p_item_type IN VARCHAR2,
79 p_curr_approver_id IN NUMBER,
80 p_override_approver_id IN NUMBER,
81 p_find_approver_method IN VARCHAR2,
82 p_next_approver_id IN OUT NOCOPY NUMBER,
83 p_error_message IN OUT NOCOPY VARCHAR2,
84 p_instructions OUT NOCOPY VARCHAR2,
85 p_special_instr OUT NOCOPY VARCHAR2
86 ) IS
87 ---------------------------------------------------------------------------
88 l_debug_info VARCHAR2(200);
89 -- bug 3257576
90 l_error_message fnd_new_messages.message_text%type;
91 l_curr_approver_name per_people_x.full_name%type;
92 l_next_approver_status per_assignment_status_types.per_system_status%type;
93 l_next_approver_name per_people_x.full_name%type;
94 BEGIN
95 --p_next_approver_id direct manager
96 --p_override_approver_id if overriding approver is entered
97 --p_curr_approver_id will have same value as p_override_approver_id
98 IF (p_find_approver_method = 'CHAIN') THEN
99
100 IF (p_next_approver_id IS NULL) THEN
101
102 -- This procedure is called only when p_override_approver_id is null
103 -- when l_find_approver_count = 0
104 -- p_curr_approver_id will have same value as p_override_approver_id
105 -- if p_curr_approver_id is null and manager does not exist or
106 -- terminated or suspended error is caught prior to this method
107 -- p_next_approver_id has value of direct manager when find_approver
108 -- _count is 0 it will be null when l_find_approver_count > 0
109 -- p_curr_approver_id will have the value of APPROVER_ID
110 -----------------------------------------------------------------
111 l_debug_info := 'Calling Get Manager with method equal CHAIN and
112 this is not the first approver being retrieved';
113 -----------------------------------------------------------------
114 -- bug 3257576 replace GetManager with GetManagerInfoAndCheckStatus
115 AP_WEB_EXPENSE_WF.GetManagerInfoAndCheckStatus(
116 p_curr_approver_id,
117 l_curr_approver_name,
118 p_next_approver_id,
119 l_next_approver_name,
120 l_next_approver_status,
121 p_error_message,
122 p_instructions,
123 p_special_instr);
124
125 END IF;
126
127 ELSIF (p_find_approver_method = 'DIRECT') THEN
128
129 ---------------------------------------------------------------------
130 l_debug_info := 'Calling Get Final Manager with method equal DIRECT';
131 ---------------------------------------------------------------------
132 AP_WEB_EXPENSE_WF.GetFinalApprover(p_employee_id,
133 p_override_approver_id,
134 p_emp_cost_center,
135 p_doc_cost_center,
136 p_approval_amount,
137 p_item_key,
138 p_item_type,
139 p_next_approver_id,
140 p_error_message,
141 p_instructions,
142 p_special_instr);
143
144
145 ELSIF (p_find_approver_method = 'ONE_STOP_DIRECT') THEN
146
147 IF (p_next_approver_id IS NULL) THEN
148
149 -------------------------------------------------------------------
150 l_debug_info := 'Calling Get Final Manager with method equal
151 ONE_STOP_DIRECT and this is not the first approver
152 being retrieved';
153 -------------------------------------------------------------------
154 AP_WEB_EXPENSE_WF.GetFinalApprover(p_curr_approver_id,
155 p_override_approver_id,
156 p_emp_cost_center,
157 p_doc_cost_center,
158 p_approval_amount,
159 p_item_key,
160 p_item_type,
161 p_next_approver_id,
162 p_error_message,
163 p_instructions,
164 p_special_instr);
165 END IF;
166
167 ELSE
168 FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_EXP_INVAL_FIND_APPROVER_METHOD');
169 p_error_message := FND_MESSAGE.Get;
170 return;
171
172 /* p_error_message := 'Invalid Find Approver Method';
173 return; */
174 END IF;
175
176 IF (p_next_approver_id IS NULL AND p_error_message IS NULL) THEN
177 ------------------------------------
178 l_debug_info := 'No approver found';
179 ------------------------------------
180 FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_NO_APPROVER_FOUND');
181 p_error_message := FND_MESSAGE.Get;
182 END IF;
183
184 IF (p_next_approver_id = p_employee_id) THEN
185 ---------------------------------------------
186 l_debug_info := 'Loop in Approval Hierarchy';
187 ---------------------------------------------
188 FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_APRVL_HIERARCHY_LOOP');
189 p_error_message := FND_MESSAGE.Get;
190 FND_MESSAGE.Set_Name('SQLAP', 'OIE_NO_APPROVER_INSTR6');
191 p_instructions := FND_MESSAGE.Get;
192 FND_MESSAGE.Set_Name('SQLAP', 'OIE_NO_APPROVER_SPL_INSTR');
193 p_special_instr := FND_MESSAGE.Get;
194 END IF;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GetApprover',
199 null, null, null, l_debug_info);
200 raise;
201 END GetApprover;
202
203 ----------------------------------------------------------------------
204 PROCEDURE FindApprover(p_item_type IN VARCHAR2,
205 p_item_key IN VARCHAR2,
206 p_actid IN NUMBER,
207 p_funmode IN VARCHAR2,
208 p_result OUT NOCOPY VARCHAR2) IS
209 ----------------------------------------------------------------------
210 l_employee_id NUMBER;
211 l_emp_cost_center VARCHAR2(240);
212 l_doc_cost_center VARCHAR2(240);
213 l_approval_amount NUMBER;
214 l_curr_approver_id NUMBER := NULL;
215 l_next_approver_id NUMBER := NULL;
216 l_dir_manager_id NUMBER := NULL;
217 l_override_approver_id NUMBER := NULL;
218 l_find_approver_method VARCHAR2(20);
219 l_find_approver_count NUMBER;
220 fixable_exception EXCEPTION;
221 l_error_message VARCHAR2(2000);
222 l_debug_info VARCHAR2(200);
223
224 C_CreditLineVersion CONSTANT NUMBER := 1;
225 C_WF_Version NUMBER := 0;
226
227 l_AMEEnabled VARCHAR2(1);
228 l_recNextApprover AME_UTIL.approverRecord;
229 l_adminApprover AME_UTIL.approverRecord;
230 l_ApprReqCC VARCHAR2(1);
231
232 -- bug 3257576
233 l_dir_manager_status per_assignment_status_types.per_system_status%type;
234 l_dir_manager_name per_people_x.full_name%type;
235 l_employee_name per_people_x.full_name%type;
236 l_instructions fnd_new_messages.message_text%type;
237 l_special_instr fnd_new_messages.message_text%type;
238 l_error fnd_new_messages.message_text%type;
239 BEGIN
240
241 IF (p_funmode = 'RUN') THEN
242
243 -----------------------------------------------------
244 l_debug_info := 'Get Workflow Version Number';
245 -----------------------------------------------------
246 C_WF_Version := AP_WEB_EXPENSE_WF.GetFlowVersion(p_item_type, p_item_key);
247
248
249 ------------------------------------------------------
250 l_debug_info := 'Retrieve Employee_ID Item Attribute';
251 -------------------------------------------------------
252 l_employee_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
253 p_item_key,
254 'EMPLOYEE_ID');
255
256 ------------------------------------------------------
257 l_debug_info := 'Retrieve Employee_ID Item Attribute';
258 -------------------------------------------------------
259 l_employee_name := WF_ENGINE.GetItemAttrNumber(p_item_type,
260 p_item_key,
261 'EMPLOYEE_DISPLAY_NAME');
262
263 ----------------------------------------------------------
264 l_debug_info := 'Retrieve Emp_Cost_Center Item Attribute';
265 -----------------------------------------------------------
266 l_emp_cost_center := WF_ENGINE.GetItemAttrText(p_item_type,
267 p_item_key,
268 'EMP_COST_CENTER');
269
270
271 ----------------------------------------------------------
272 l_debug_info := 'Retrieve Doc_Cost_Center Item Attribute';
273 -----------------------------------------------------------
274 l_doc_cost_center := WF_ENGINE.GetItemAttrText(p_item_type,
275 p_item_key,
276 'DOC_COST_CENTER');
277
278
279 -------------------------------------------------
280 l_debug_info := 'Retrieve Total Item Attribute';
281 --------------------------------------------------
282 /* Bug 3307845 : The total amount should be considered when verifying
283 the authority to approve */
284 /*
285 IF (C_WF_Version >= C_CreditLineVersion) THEN
286 l_approval_amount := WF_ENGINE.GetItemAttrNumber(p_item_type,
287 p_item_key,
288 'POS_NEW_EXPENSE_TOTAL');
289 ELSE
290 */
291 l_approval_amount := WF_ENGINE.GetItemAttrNumber(p_item_type,
292 p_item_key,
293 'TOTAL');
294 /* END IF; */
295
296 -------------------------------------------------------------------
297 l_debug_info := 'Retrieve Find_Approver_Count Activity Attribute';
298 -------------------------------------------------------------------
299 l_find_approver_count := WF_ENGINE.GetItemAttrNumber(p_item_type,
300 p_item_key,
301 'FIND_APPROVER_COUNT');
302
303 ----------------------------------------------------
304 l_debug_info := 'Retrieve profile option AME Enabled?';
305 ----------------------------------------------------
306 l_AMEEnabled := WF_ENGINE.GetItemAttrText(p_item_type,
307 p_item_key,
308 'AME_ENABLED');
309
310
311 ------------------------------------------------------
312 l_debug_info := 'Retrieve Approver_ID Item Attribute';
313 ------------------------------------------------------
314 /*Bug 2650108:Added an if condition to check if the
315 approver_id is NULL in AP_EXPENSE_REPORT_HEADERS
316 table for the first time .
317 */
318
319 /*BUg 2707624:Call the GetOverrideApproverID() procedure
320 only first time else always the notification
321 will go to the Alternate Approver assigned to the Report.
322 */
323
324 /*
325 For AME Line-Level Approvals project, if AME is enabled, Override
326 Approver ID will be used to set AME attribute Job Level Non Default
327 Starting Point Person ID hence don't need to get it here.
328 */
329 /* removed IF (l_AMEEnabled = 'N') to avoid error AP_WEB_EXP_COST_CTR_DIFF
330 when cost center is changed and override approver is entered
331 */
332 IF (l_find_approver_count = 0) THEN
333 IF (NOT (AP_WEB_DB_EXPRPT_PKG.GetOverrideApproverID(to_number(p_item_key), l_curr_approver_id))) THEN
334 l_curr_approver_id := NULL;
335 END IF;
336 END IF;
337
338
339 IF(l_curr_approver_id is NULL AND l_find_approver_count >0)
340 THEN
341 l_curr_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
342 p_item_key,
343 'APPROVER_ID');
344
345 END IF;
346
347 -------------------------------------------------------------------
348 l_debug_info := 'Retrieve Find_Approver_Method Activity Attribute';
349 -------------------------------------------------------------------
350 l_find_approver_method := WF_ENGINE.GetActivityAttrText(p_item_type,
351 p_item_key,
352 p_actid,
353 'FIND_APPROVER_METHOD');
354
355 IF (l_find_approver_count = 0) THEN
356
357 ----------------------------------------------------
358 l_debug_info := 'First Time calling Find Approver';
359 ----------------------------------------------------
360
361 -- bug 3257576
362 AP_WEB_EXPENSE_WF.GetManagerInfoAndCheckStatus(
363 l_employee_id,
364 l_employee_name,
365 l_dir_manager_id,
366 l_dir_manager_name,
367 l_dir_manager_status,
368 l_error_message,
369 l_instructions,
370 l_special_instr);
371
372 AP_WEB_EXPENSE_WF.SetPersonAs(l_dir_manager_id,
373 p_item_type,
374 p_item_key,
375 'MANAGER');
376
377 /* Bug 1811921 : Checking for the profile option
378 before raising an error */
379 FND_PROFILE.GET('AP_WEB_APPROVER_REQ_CC', l_ApprReqCC);
380
381 IF (l_AMEEnabled = 'N') THEN
382
383 IF (l_curr_approver_id IS NULL) THEN
384 IF (l_error_message IS NOT NULL) THEN
385 raise fixable_exception ;
386 ELSE
387 l_next_approver_id := l_dir_manager_id;
388 END IF;
389 END IF;
390
391 IF ((l_emp_cost_center <> l_doc_cost_center) AND
392 (l_curr_approver_id IS NULL) AND
393 (nvl(l_ApprReqCC,'N') = 'Y')) THEN
394
395 FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_EXP_COST_CTR_DIFF');
396 l_error_message := FND_MESSAGE.Get;
397 raise fixable_exception ;
398
399
400 /* l_error_message := 'The Expense Report Cost Center is different
401 from the Employee Cost Center, but No Override Approver
402 was provided';
403
404 raise fixable_exception; */
405
406 END IF;
407
408 IF (l_curr_approver_id IS NOT NULL) THEN
409 l_next_approver_id := l_curr_approver_id;
410 l_override_approver_id := l_curr_approver_id;
411 l_error_message := null; -- override approver is entered //Bug 4469689
412 END IF;
413
414 ELSE --AME Enabled
415
416 l_error_message := null;
417 IF (l_curr_approver_id IS NULL) AND
418 (l_emp_cost_center <> l_doc_cost_center) AND
419 (nvl(l_ApprReqCC,'N') = 'Y') THEN
420
421 FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_EXP_COST_CTR_DIFF');
422 l_error_message := FND_MESSAGE.Get;
423 raise fixable_exception ;
424 END IF; --IF (l_emp_cost_center <> l_doc_cost_center)
425
426 -------------------------------------------
427 l_debug_info := 'Clear AME approval chain';
428 -------------------------------------------
429 -- 3103400:remove the call to HaveAMEReCreateApprovalChain and
430 -- add clearAllApprovals
431 AME_API2.clearAllApprovals(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
432 transactionIdIn => p_item_key,
433 transactionTypeIn => p_item_type);
434
435 END IF; --if l_AMEEnabled
436
437 END IF; -- End of first time calling find approver
438
439 IF (l_AMEEnabled = 'Y') THEN
440
441 -- bug 3257576
442 AP_WEB_EXPENSE_WF.GetManagerInfoAndCheckStatus(
443 l_employee_id,
444 l_employee_name,
445 l_dir_manager_id,
446 l_dir_manager_name,
447 l_dir_manager_status,
448 l_error,
449 l_instructions,
450 l_special_instr);
451
452 ---------------------------------------
453 l_debug_info := 'Calling AME_API.getNextApprover. If seeing this debug info, there are some exceptions caused by AME API.';
454 ---------------------------------------
455
456 -- stub file ameeapin.pkb 115.0.1151.5 always returns null as l_recNextApprover
457 -- which will cause workflow failed
458 BEGIN
459 AME_API.getNextApprover(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
460 transactionIdIn => p_item_key,
461 transactionTypeIn => p_item_type,
462 nextApproverOut => l_recNextApprover);
463 EXCEPTION
464 when others then
465 FND_MESSAGE.Set_Name('SQLAP', 'OIE_GETNEXTAPPROVER_ERROR');
466 l_error_message := FND_MESSAGE.Get;
467 -- bug 3257576
468 FND_MESSAGE.Set_Name('SQLAP', 'OIE_NO_APPROVER_INSTR4');
469 l_instructions := FND_MESSAGE.Get;
470 raise fixable_exception ;
471 END;
472
473 /*
474 AME_API.getNextApprover will return administrator's id if exception
475 happened in AME. We need to confirm this by checking whether the employee's
476 manager happens to be the administrator.
477 */
478
479 ---------------------------------------
480 l_debug_info := 'Calling AME_API.getAdminApprover. If seeing this debug info, there are some exceptions caused by AME API.';
481 ---------------------------------------
482 BEGIN
483 AME_API.getAdminApprover(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
484 transactionTypeIn => p_item_type,
485 adminApproverOut => l_adminApprover);
486 EXCEPTION
487 when others then
488 FND_MESSAGE.Set_Name('SQLAP', 'OIE_GETNEXTAPPROVER_ERROR');
489 l_error_message := FND_MESSAGE.Get;
490 -- bug 3257576
491 FND_MESSAGE.Set_Name('SQLAP', 'OIE_NO_APPROVER_INSTR4');
492 l_instructions := FND_MESSAGE.Get;
493 raise fixable_exception ;
494 END;
495
496 if l_recNextApprover.person_id = l_adminApprover.person_id then
497 if l_adminApprover.person_id <> l_dir_manager_id then
498 FND_MESSAGE.Set_Name('SQLAP', 'OIE_GETNEXTAPPROVER_ERROR');
499 l_error_message := FND_MESSAGE.Get;
500 -- bug 3257576
501 FND_MESSAGE.Set_Name('SQLAP', 'OIE_NO_APPROVER_INSTR4');
502 l_instructions := FND_MESSAGE.Get;
503 raise fixable_exception ;
504 else
505 l_next_approver_id := l_recNextApprover.person_id;
506 end if;
507 else
508 l_next_approver_id := l_recNextApprover.person_id;
509 end if;
510
511 ELSIF ((l_override_approver_id IS NULL) OR
512 (l_find_approver_method = 'DIRECT')) THEN
513
514 ---------------------------------------
515 l_debug_info := 'Calling Get Approver';
516 ---------------------------------------
517 GetApprover(l_employee_id,
518 l_emp_cost_center,
519 l_doc_cost_center,
520 l_approval_amount,
521 p_item_key,
522 p_item_type,
523 l_curr_approver_id,
524 l_override_approver_id,
525 l_find_approver_method,
526 l_next_approver_id,
527 l_error_message,
528 l_instructions,
529 l_special_instr);
530
531 END IF;
532
533 IF ((l_next_approver_id IS NULL) OR (l_error_message IS NOT NULL)) THEN
534
535 -- bug 3257576
536 WF_ENGINE.SetItemAttrText(p_item_type,
537 p_item_key,
538 'NO_APPROVER_PROBLEM',
539 l_error_message);
540 WF_ENGINE.SetItemAttrText(p_item_type,
541 p_item_key,
542 'NO_APPROVER_INSTRUCTIONS',
543 l_instructions);
544 WF_ENGINE.SetItemAttrText(p_item_type,
545 p_item_key,
546 'NO_APPROVER_SPECIAL_INSTR',
547 l_special_instr);
548
549 p_result := 'COMPLETE:N';
550
551 ELSE
552
553 AP_WEB_EXPENSE_WF.SetPersonAs(l_next_approver_id,
554 p_item_type,
555 p_item_key,
556 'APPROVER');
557
558 WF_ENGINE.SetItemAttrNumber(p_item_type,
559 p_item_key,
560 'FIND_APPROVER_COUNT',
561 l_find_approver_count+1);
562
563 p_result := 'COMPLETE:Y';
564
565 END IF;
566
567 ELSIF (p_funmode = 'CANCEL') THEN
568 NULL;
569 END IF;
570
571 EXCEPTION
572 WHEN fixable_exception THEN
573 -- bug 3257576
574 WF_ENGINE.SetItemAttrText(p_item_type,
575 p_item_key,
576 'NO_APPROVER_PROBLEM',
577 l_error_message);
578 WF_ENGINE.SetItemAttrText(p_item_type,
579 p_item_key,
580 'NO_APPROVER_INSTRUCTIONS',
581 l_instructions);
582 WF_ENGINE.SetItemAttrText(p_item_type,
583 p_item_key,
584 'NO_APPROVER_SPECIAL_INSTR',
585 l_special_instr);
586
587 p_result := 'COMPLETE:N';
588 WHEN OTHERS THEN
589 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'FindApprover',
590 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
591 raise;
592 END FindApprover;
593
594
595 ---------------------------------------------------------------------
596 FUNCTION HasAuthority(p_approver_id IN NUMBER,
597 p_doc_cost_center IN VARCHAR2,
598 p_approval_amount IN NUMBER,
599 p_item_key IN VARCHAR2,
600 p_item_type IN VARCHAR2) RETURN BOOLEAN IS
601 ---------------------------------------------------------------------
602 l_has_authority BOOLEAN;
603 l_report_id AP_WEB_DB_EXPRPT_PKG.expHdr_headerID;
604 l_exch_rate AP_WEB_DB_EXPRPT_PKG.expHdr_defaultExchRate;
605 l_reimb_precision AP_WEB_DB_COUNTRY_PKG.curr_precision;
606 l_debug_info VARCHAR2(240);
607 l_exp_info_rec AP_WEB_DB_EXPRPT_PKG.ExpInfoRec;
608
609
610 BEGIN
611
612 IF (AP_WEB_DB_UTIL_PKG.AtLeastProd16) THEN
613
614 -----------------------------------------------------
615 l_debug_info := 'Retrieve Expense Report Number';
616 -----------------------------------------------------
617 l_report_id := WF_ENGINE.GetItemAttrText(p_item_type,
618 p_item_key,
619 'EXPENSE_REPORT_ID');
620
621 -----------------------------------------------------
622 l_debug_info := 'Get Expense Report Currency Info';
623 -----------------------------------------------------
624 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetReportInfo(l_report_id,l_exp_info_rec)) THEN
625 return false;
626 END IF;
627
628 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetExpReportExchCurrInfo(l_report_id, l_exch_rate, l_reimb_precision)) THEN
629 return false;
630 END IF;
631
632 -----------------------------------------------------
633 l_debug_info := 'Invoke ApproverHasAuthority ' || p_approver_id || ' ' || p_approval_amount || ' ' || l_exch_rate || ' ' || l_reimb_precision || ' ' || p_item_type;
634 -----------------------------------------------------
635 IF (AP_WEB_DB_AP_INT_PKG.ApproverHasAuthority(
636 p_approver_id, p_doc_cost_center,
637 p_approval_amount, l_reimb_precision, p_item_type,
638 l_exp_info_rec.payment_curr_code, l_exp_info_rec.week_end_date,
639 l_has_authority)) THEN
640
641 return l_has_authority;
642 ELSE
643 return FALSE;
644 END IF;
645
646 ELSE
647
648 return TRUE; -- always has authority if before prod16
649
650 END IF;
651
652 EXCEPTION
653 WHEN OTHERS THEN
654 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'HasAuthority',
655 p_item_type, null, null, l_debug_info);
656 raise;
657 END HasAuthority;
658
659
660 ----------------------------------------------------------------------
661 PROCEDURE VerifyAuthority(p_item_type IN VARCHAR2,
662 p_item_key IN VARCHAR2,
663 p_actid IN NUMBER,
664 p_funmode IN VARCHAR2,
665 p_result OUT NOCOPY VARCHAR2) IS
666 ----------------------------------------------------------------------
667 l_approver_id NUMBER;
668 l_preparer_id NUMBER;
669 l_doc_cost_center VARCHAR2(240);
670 l_approval_amount NUMBER;
671 l_debug_info VARCHAR2(200);
672 C_CreditLineVersion CONSTANT NUMBER := 1;
673 C_WF_Version NUMBER := 0;
674
675 l_AMEEnabled VARCHAR2(1);
676 l_bHasAuthority BOOLEAN;
677 l_recApprover AME_UTIL.approverRecord;
678 l_recNextApprover AME_UTIL.approverRecord;
679
680 BEGIN
681
682 IF (p_funmode = 'RUN') THEN
683
684
685 -----------------------------------------------------
686 l_debug_info := 'Get Workflow Version Number';
687 -----------------------------------------------------
688 C_WF_Version := AP_WEB_EXPENSE_WF.GetFlowVersion(p_item_type, p_item_key);
689
690
691 ------------------------------------------------------
692 l_debug_info := 'Retrieve Approver_ID Item Attribute';
693 -------------------------------------------------------
694 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
695 p_item_key,
696 'APPROVER_ID');
697
698
699 ------------------------------------------------------
700 l_debug_info := 'Retrieve Preparer_ID Item Attribute';
701 -------------------------------------------------------
702 l_preparer_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
703 p_item_key,
704 'PREPARER_ID');
705
706 ----------------------------------------------------------
707 l_debug_info := 'Retrieve Doc_Cost_Center Item Attribute';
708 -----------------------------------------------------------
709 l_doc_cost_center := WF_ENGINE.GetItemAttrText(p_item_type,
710 p_item_key,
711 'DOC_COST_CENTER');
712
713
714 -------------------------------------------------
715 l_debug_info := 'Retrieve Total Item Attribute';
716 -------------------------------------------------
717 /* Bug 3307845 : The total amount should be considered when verifying
718 the authority to approve */
719 /*
720 IF (C_WF_Version >= C_CreditLineVersion) THEN
721 l_approval_amount := WF_ENGINE.GetItemAttrNumber(p_item_type,
722 p_item_key,
723 'POS_NEW_EXPENSE_TOTAL');
724 ELSE
725 */
726 l_approval_amount := WF_ENGINE.GetItemAttrNumber(p_item_type,
727 p_item_key,
728 'TOTAL');
729 /* END IF; */
730
731
732 ----------------------------------------------------
733 l_debug_info := 'Retrieve profile option AME Enabled?';
734 ----------------------------------------------------
735 l_AMEEnabled := WF_ENGINE.GetItemAttrText(p_item_type,
736 p_item_key,
737 'AME_ENABLED');
738
739 IF (l_AMEEnabled = 'Y') THEN
740
741 /*Call AMEs UpdateApprovalStatus api to let AME know the expense report
742 is Approved by previous approver. */
743
744 l_recApprover.person_id := l_approver_id;
745 l_recApprover.approval_status := AME_UTIL.approvedStatus;
746
747 ------------------------------------------------------
748 l_debug_info := 'Call AMEs updateApprovalAtatus api';
749 ------------------------------------------------------
750 AME_API.updateApprovalStatus2(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
751 transactionIdIn => p_item_key,
752 approvalStatusIn => AME_UTIL.approvedStatus,
753 approverPersonIdIn => l_approver_id,
754 approverUserIdIn => NULL,
755 transactionTypeIn => p_item_type);
756
757 /* Set Has_Authority local variable to true if
758 AMEs getNextApprover returns a null; */
759 -------------------------------------------------
760 l_debug_info := 'Call AMEs getNextApprover api';
761 -------------------------------------------------
762 AME_API.getNextApprover(applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
763 transactionIdIn => p_item_key,
764 transactionTypeIn => p_item_type,
765 nextApproverOut => l_recNextApprover);
766
767 IF (l_recNextApprover.person_id IS NULL) THEN
768 l_bHasAuthority := TRUE;
769 ELSE
770 l_bHasAuthority := FALSE;
771 END IF;
772
773 ELSE -- AME not enabled
774
775 --Set Has_Authority local variable to call to our own HasAuthority api;
776 --------------------------------------------
777 l_debug_info := 'Call HasAuthority api';
778 --------------------------------------------
779 l_bHasAuthority := HasAuthority(l_approver_id,
780 l_doc_cost_center,
781 l_approval_amount,
782 p_item_key,
783 p_item_type);
784
785 -- bug 4112598/4281805
786 IF (l_preparer_id = l_approver_id) THEN
787 l_bHasAuthority := FALSE;
788 END IF;
789
790 END IF; -- if l_AMEEnabled
791
792 --------------------------------------------
793 l_debug_info := 'Set Result';
794 --------------------------------------------
795 -- bug 4112598/4281805
796 -- IF ((l_bHasAuthority) AND (l_preparer_id <> l_approver_id)) THEN
797 IF (l_bHasAuthority) THEN
798
799 p_result := 'COMPLETE:AP_PASS';
800
801 ELSE
802
803 p_result := 'COMPLETE:AP_FAIL';
804
805 END IF;
806
807 ELSIF (p_funmode = 'CANCEL') THEN
808 NULL;
809 END IF;
810
811 EXCEPTION
812 WHEN OTHERS THEN
813 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'VerifyAuthority',
814 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
815 raise;
816 END VerifyAuthority;
817
818 ----------------------------------------------------------------------
819 PROCEDURE CustomDataTransfer(p_item_type IN VARCHAR2,
820 p_item_key IN VARCHAR2) IS
821 ----------------------------------------------------------------------
822 l_report_header_id NUMBER;
823 l_debug_info VARCHAR2(200);
824 BEGIN
825
826 /* Place some custom code here, i.e. update statement */
827
828 null;
829
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'CustomDataTransfer',
834 p_item_type, p_item_key, null, l_debug_info);
835 raise;
836 END CustomDataTransfer;
837
838
839
840
841
842
843 ----------------------------------------------------------------------
844 PROCEDURE DetermineMgrInvolvement(p_item_type IN VARCHAR2,
845 p_item_key IN VARCHAR2,
846 p_actid IN NUMBER,
847 p_funmode IN VARCHAR2,
848 p_result OUT NOCOPY VARCHAR2) IS
849 ----------------------------------------------------------------------
850 l_new_expense_total NUMBER;
851 l_debug_info VARCHAR2(200);
852
853 l_employee_id NUMBER;
854 l_employee_name wf_users.name%type;
855 l_report_header_id NUMBER;
856 l_week_end_date DATE := NULL;
857 l_session_project_enabled VARCHAR2(1) := NULL;
858 l_contains_non_project_line VARCHAR2(1) := NULL;
859 l_contains_project_line VARCHAR2(1) := NULL;
860 l_non_proj_mgr_involvement VARCHAR2(20);
861 l_proj_mgr_involvement VARCHAR2(20);
862 l_auto_approved VARCHAR2(1000) := NULL;
863
864 l_payment VARCHAR2(20);--BUg 2944363
865 l_num_personal_lines NUMBER := 0; --Bug 2944363
866
867
868 C_WF_Version NUMBER := 0;
869
870 l_notification_only CONSTANT VARCHAR2(20) := 'NOTIFICATION_ONLY';
871 l_approval_required CONSTANT VARCHAR2(20) := 'APPROVAL_REQUIRED';
872 l_bypass_approval CONSTANT VARCHAR2(20) := 'BYPASS_APPROVAL';
873 l_no_auto_approve_notif CONSTANT VARCHAR2(20) := 'NO_NOTIFICATION';
874
875 /****** SAMPLE CODE ******
876 l_notify_only_amount NUMBER := 100;
877 l_approval_req_amount NUMBER := 500;
878 ****** SAMPLE CODE ******/
879
880 BEGIN
881
882 IF (p_funmode = 'RUN') THEN
883
884 ------------------------------------------------------------
885 -- If expense report contains a project-related receipt, then
886 -- determine whether manager approval is automatic.
887 ------------------------------------------------------------
888
889 ------------------------------------------------------------
890 l_debug_info := 'Retrieve workflow version';
891 ------------------------------------------------------------
892 C_WF_Version := AP_WEB_EXPENSE_WF.GetFlowVersion(p_item_type, p_item_key);
893
894 ------------------------------------------------------------
895 l_debug_info := 'Retrieve employee name and report ID Item Attribute';
896 ------------------------------------------------------------
897 l_employee_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
898 p_item_key,
899 'EMPLOYEE_ID');
900
901 l_employee_name := WF_ENGINE.GetItemAttrText(p_item_type,
902 p_item_key,
903 'EMPLOYEE_NAME');
904
905 l_report_header_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
906 p_item_key,
907 'EXPENSE_REPORT_ID');
908
909 IF (C_WF_Version >= AP_WEB_EXPENSE_WF.C_ProjectIntegrationVersion) THEN
910 l_week_end_date := WF_ENGINE.GetItemAttrDate(p_item_type,
911 p_item_key,
912 'WEEK_END_DATE');
913 END IF;
914
915 ------------------------------------------------------------
916 l_debug_info := 'Determine whether session is project enabled';
917 ------------------------------------------------------------
918 IF (C_WF_Version >= AP_WEB_EXPENSE_WF.C_11_0_3Version) THEN
919 l_session_project_enabled := WF_ENGINE.GetItemAttrText(p_item_type,
920 p_item_key,
921 'EMPLOYEE_PROJECT_ENABLED');
922
923 ELSE
924 l_session_project_enabled := 'Y';
925 END IF;
926
927 ------------------------------------------------------------
928 l_debug_info := 'Determine if proj-related and non-proj-related line in report';
929 ------------------------------------------------------------
930 l_contains_project_line := 'N';
931 l_contains_non_project_line := 'N';
932 IF (l_session_project_enabled = 'Y') THEN
933 IF ( AP_WEB_DB_EXPLINE_PKG.ContainsProjectRelatedLine(
934 l_report_header_id) ) THEN
935 l_contains_project_line := 'Y';
936 END IF;
937 END IF;
938
939 IF ( AP_WEB_DB_EXPLINE_PKG.ContainsNonProjectRelatedLine(
940 l_report_header_id) ) THEN
941 l_contains_non_project_line := 'Y';
942 END IF;
943
944 -----------------------------------------------------------------------
945 l_debug_info := 'Determine auto approval for project-related lines';
946 -----------------------------------------------------------------------
947 l_proj_mgr_involvement := l_bypass_approval;
948 IF (l_contains_project_line = 'Y') THEN
949
950 PA_CLIENT_EXTN_PTE.Get_Exp_AutoApproval(X_source => 'SELF_SERVICE',
951 X_exp_class_code => NULL,
952 X_txn_id => l_report_header_id,
953 X_exp_ending_date => l_week_end_date,
954 X_person_id => l_employee_id,
955 X_approved => l_auto_approved);
956
957 -- If automatically approved, then bypass approval
958 IF (l_auto_approved <> 'Y') THEN
959 l_proj_mgr_involvement := l_approval_required;
960 END IF;
961
962 END IF;
963
964 -----------------------------------------------------------------------
965 l_debug_info := 'Determine auto approval for non-project-related lines';
966 -----------------------------------------------------------------------
967 l_non_proj_mgr_involvement := l_bypass_approval;
968 IF (l_contains_non_project_line = 'Y') THEN
969
970 -------------------------------------------------------------
971 l_debug_info := 'Retrieve New Expense Total.';
972 -------------------------------------------------------------
973 l_new_expense_total := WF_ENGINE.GetItemAttrNumber(p_item_type,
974 p_item_key,
975 'POS_NEW_EXPENSE_TOTAL');
976
977 IF (l_new_expense_total <= 0) THEN
978 l_non_proj_mgr_involvement := l_notification_only;
979 ELSE
980
981 /****** SAMPLE CODE ******
982 For example: To bypass management approval for expense report below
983 a specific amount.
984
985 IF (l_new_expense_total >= l_approval_req_amount) THEN
986 l_non_proj_mgr_involvement := l_approval_required;
987 ELSIF (l_new_expense_total >= l_notify_only_amount) THEN
988 l_non_proj_mgr_involvement := l_notification_only;
989 ELSE
990 l_non_proj_mgr_involvement := l_bypass_approval;
991 END IF;
992
993 ****** SAMPLE CODE ******/
994
995 /****** Remove the line below if you are customizing this code. ******/
996 l_non_proj_mgr_involvement := l_approval_required;
997
998 END IF;
999 END IF; -- (l_contains_non_project_line = 'Y')
1000
1001 -----------------------------------------------------------------------
1002 l_debug_info := 'Determine strictest involvement';
1003 -----------------------------------------------------------------------
1004 if (l_non_proj_mgr_involvement = l_approval_required) or
1005 (l_proj_mgr_involvement = l_approval_required) then
1006 p_result := 'COMPLETE:' || l_approval_required;
1007 elsif (l_non_proj_mgr_involvement = l_notification_only) or
1008 (l_proj_mgr_involvement = l_notification_only) then
1009 p_result := 'COMPLETE:' || l_notification_only;
1010 elsif (l_non_proj_mgr_involvement = l_bypass_approval) or
1011 (l_proj_mgr_involvement = l_bypass_approval) then
1012 p_result := 'COMPLETE:' || l_bypass_approval;
1013 end if;
1014
1015 /*Bug 2944363 : If PAYMENT_DUE_FROM is BOTH and ER contains only
1016 personal CC trxns,
1017 Then send a notification to Approver .
1018 */
1019
1020 --AMMISHRA - Both Pay Personal Only Lines project.
1021
1022 if (AP_WEB_DB_EXPLINE_PKG.GetNoOfBothPayPersonalLines(l_report_header_id,l_num_personal_lines)) then null; end if;
1023 ----------------------------------------------------------------
1024 l_debug_info := 'Retrieve Profile Option Payment Due From';
1025 ----------------------------------------------------------------
1026 IF (NOT AP_WEB_DB_EXPRPT_PKG.getPaymentDueFromReport(l_report_header_id,l_payment)) THEN
1027 l_debug_info := 'Could not set workflow attribute Payment_Due_From';
1028 END IF;
1029
1030 IF (l_payment = 'BOTH' and l_num_personal_lines > 0 ) THEN
1031 p_result := 'COMPLETE:' || l_no_auto_approve_notif;
1032
1033 WF_ENGINE.SetItemAttrText(p_item_type,
1034 p_item_key,
1035 '#FROM_ROLE',
1036 WF_ENGINE.GetItemAttrText(p_item_type,
1037 p_item_key,
1038 'EMPLOYEE_NAME'));
1039
1040 END IF;
1041 --2944363 : Ends here
1042
1043
1044 ELSIF (p_funmode = 'CANCEL') THEN
1045 NULL;
1046 END IF;
1047
1048 EXCEPTION
1049
1050 WHEN OTHERS THEN
1051 WF_CORE.Context('AP_WEB_EXPENSE_WF', 'DetermineMgrInvolvement',
1052 p_item_type, p_item_key,
1053 to_char(p_actid), l_debug_info);
1054 raise;
1055 END DetermineMgrInvolvement;
1056
1057
1058
1059
1060 END AP_WEB_EXPENSE_CUST_WF;