1 PACKAGE BODY AP_WEB_OA_ACTIVE_PKG AS
2 /* $Header: apwoaacb.pls 120.21.12020000.2 2012/09/20 07:13:32 meesubra ship $ */
3
4 -- Cache for AP Approver and Expenses Administrator
5 g_ApApprover ap_lookup_codes.displayed_field%TYPE := NULL;
6 g_ExpensesAdmin ap_lookup_codes.displayed_field%TYPE := NULL;
7
8 -- Cache for GetReportStatusCode
9 grsc_old_report_header_id NUMBER := NULL;
10 grsc_old_status_code ap_lookup_codes.lookup_code%TYPE := NULL;
11
12 -- Cache for GetApproverName
13 gan_old_report_header_id NUMBER := NULL;
14 gan_old_approver_name WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE := NULL;
15 gan_old_attribute_name WF_ITEM_ATTRIBUTE_VALUES.NAME%TYPE := NULL;
16
17 -- Global variable for session language code.
18 g_langCode language_code := NULL;
19
20 --
21 -- GetApproverName
22 -- Purpose: To get the approver name from the workflow
23 --
24 -- Input: p_report_header_id
25 -- p_attribute_name: to get either approver, preparer, or
26 -- employee name
27 --
28 -- Output: The approver name for this report with this attribute
29 -- NULL, if no approver is found.
30 --
31
32 FUNCTION GetApproverName (p_report_header_id IN NUMBER,
33 p_attribute_name IN VARCHAR2) RETURN VARCHAR2
34 IS
35 BEGIN
36 -- Check cache first
37 IF p_report_header_id = gan_old_report_header_id
38 AND p_attribute_name = gan_old_attribute_name THEN
39 RETURN gan_old_approver_name;
40 END IF;
41
42 -- update Cache
43 gan_old_approver_name :=
44 WF_ENGINE.GetItemAttrText(C_APEXP, TO_CHAR(p_report_header_id), p_attribute_name);
45 gan_old_report_header_id := p_report_header_id;
46 gan_old_attribute_name := p_attribute_name;
47
48 --Bug 12565659: Fetch the approver name directly from wf_roles, to display it correctly
49 IF p_attribute_name = C_APPROVER_DISPLAY_NAME THEN
50 SELECT wfr.description INTO gan_old_approver_name
51 FROM ap_expense_report_headers_all aerh, wf_roles wfr
52 WHERE aerh.report_header_id = p_report_header_id
53 AND wfr.orig_system = aerh.approval_type
54 AND wfr.orig_system_id = aerh.expense_current_approver_id;
55 END IF;
56
57 RETURN gan_old_approver_name;
58 EXCEPTION
59 WHEN OTHERS THEN
60 -- This attribute was not found, return NULL
61 gan_old_report_header_id := p_report_header_id;
62 gan_old_attribute_name := p_attribute_name;
63 gan_old_approver_name := NULL;
64 RETURN NULL;
65 END GetApproverName;
66
67
68 --
69 -- GetApApprover
70 -- Purpose: To get the AP Approver string
71 --
72 -- Input: None. Assumes that the AP Approver has been
73 -- set up properly in AP Lookups
74 --
75 -- Output: The AP approver name
76 -- NULL, if the AP approver was not set up.
77 --
78
79 FUNCTION GetApApprover RETURN VARCHAR2
80 IS
81 v_ApApprover ap_lookup_codes.displayed_field%TYPE := NULL;
82 v_langCode language_code;
83 BEGIN
84 v_langCode := userenv('LANG');
85
86 -- Check cache first
87 IF g_ApApprover IS NOT NULL AND g_langCode = v_langCode THEN
88 RETURN g_ApApprover;
89 ELSE
90 g_langCode := v_langCode;
91
92 SELECT DISPLAYED_FIELD
93 INTO v_ApApprover
94 FROM ap_lookup_codes
95 WHERE LOOKUP_TYPE = C_EXPENSE_REPORT_APPROVER
96 AND LOOKUP_CODE = C_AP;
97
98 -- Update cache
99 g_ApApprover := v_ApApprover;
100
101 RETURN v_ApApprover;
102 END IF;
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 -- Update cache. Not using NULL, so that cache will
106 -- still be used.
107 g_ApApprover := ' ';
108
109 RETURN g_ApApprover;
110 END GetApApprover;
111
112
113 --
114 -- GetExpensesAdmin
115 -- Purpose: To get the Expenses Administrator string
116 --
117 -- Input: None. Assumes that the Expenses Admin has been
118 -- set up properly in AP Lookups
119 --
120 -- Output: The Expenses Administrator name
121 -- NULL, if the Expenses Administrator was not set up.
122 --
123
124 FUNCTION GetExpensesAdmin RETURN VARCHAR2
125 IS
126 v_ExpensesAdmin ap_lookup_codes.displayed_field%TYPE := NULL;
127 v_langCode language_code;
128 BEGIN
129 v_langCode := userenv('LANG');
130
131 -- Check cache first
132 IF g_ExpensesAdmin IS NOT NULL AND g_langCode = v_langCode THEN
133 RETURN g_ExpensesAdmin;
134 ELSE
135 g_langCode := v_langCode;
136
137 SELECT DISPLAYED_FIELD
138 INTO v_ExpensesAdmin
139 FROM ap_lookup_codes
140 WHERE LOOKUP_TYPE = C_EXPENSE_REPORT_APPROVER
141 AND LOOKUP_CODE = C_EXPADMIN;
142
143 -- Update cache
144 g_ExpensesAdmin := v_ExpensesAdmin;
145
146 RETURN v_ExpensesAdmin;
147 END IF;
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 -- Update cache. Not using NULL, so that cache will
151 -- still be used.
152 g_ExpensesAdmin := ' ';
153
154 RETURN g_ExpensesAdmin;
155 END GetExpensesAdmin;
156
157
158 --
159 -- GetWFLastNotificationActivity
160 -- Purpose: To get the activity label of the last notification
161 -- that was sent for this item
162 --
163 -- Input: p_report_header_id. Assumes that only 1 activity has status
164 -- 'NOTIFIED' per process. (It would show COMPLETED otherwise)
165 --
166 -- Output: The last notification activity for this report's process
167 -- NULL, if no oustanding notifications are present for this report
168 --
169
170 FUNCTION GetWFLastNotificationActivity (p_report_header_id IN NUMBER) RETURN VARCHAR2
171 IS
172 v_activity_label wf_item_activity_statuses_v.activity_label%TYPE := NULL;
173
174 BEGIN
175 -- Find activity with last notification id
176 /*Bug 2790208: Added rownum = 1 with an order by clause
177 to avoid any potential JBO error in Track
178 Expense Reports table.Now it ensures a single
179 row to be returned which is the latest notified.
180 */
181 SELECT INSTANCE_LABEL
182 INTO v_activity_label
183 FROM (
184 SELECT PA.INSTANCE_LABEL
185 FROM WF_ITEM_ACTIVITY_STATUSES IAS,
186 WF_PROCESS_ACTIVITIES PA
187 WHERE
188 IAS.ITEM_TYPE = C_APEXP
189 AND IAS.ACTIVITY_STATUS = C_NOTIFIED
190 AND IAS.ITEM_KEY = TO_CHAR(p_report_header_id)
191 AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
192 AND IAS.NOTIFICATION_ID IS NOT NULL
193 ORDER BY IAS.BEGIN_DATE desc
194 )
195 WHERE ROWNUM =1;
196
197 RETURN v_activity_label;
198
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 RETURN NULL;
202 END GetWFLastNotificationActivity;
203
204
205 --
206 -- GetReportStatusCode
207 -- Purpose: To return the status code of an expense report
208 --
209 -- Input: p_source,
210 -- p_workflow_approved_flag,
211 -- p_report_header_id.
212 --
213 -- Output: The report status code which also matches with
214 -- the codes in the lookup type
215 -- EXPENSE REPORT STATUS.
216 --
217
218 FUNCTION GetReportStatusCode(p_source IN VARCHAR2,
219 p_workflow_approved_flag IN VARCHAR2,
220 p_report_header_id IN NUMBER,
221 p_cache IN VARCHAR2,
222 p_query_wf_activities IN VARCHAR2) RETURN VARCHAR2
223 IS
224 v_status_code ap_lookup_codes.lookup_code%TYPE := NULL;
225 v_expense_status_code ap_expense_report_headers_all.expense_status_code%TYPE := NULL;
226
227 --Bug 3612002:Changed datatype of the variable so that length
228 -- will become 4000.
229
230 v_approver WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE := NULL;
231 v_activity_label wf_item_activity_statuses_v.activity_label%TYPE := NULL;
232
233 BEGIN
234 -- Check cache
235 IF ((p_report_header_id = grsc_old_report_header_id) AND (p_cache = 'Y'))THEN
236 RETURN grsc_old_status_code;
237 END IF;
238
239 -- Look at the source first
240 SELECT expense_status_code INTO v_expense_status_code FROM ap_expense_report_headers_all
241 WHERE report_header_id = p_report_header_id;
242
243 -- Source = SelfService or CREDIT CARD
244 IF p_source IN (C_SelfService, C_CREDIT_CARD, C_BOTH_PAY) THEN
245 v_status_code := C_INVOICED;
246 IF v_expense_status_code = 'PAID' THEN
247 v_status_code := 'PAID';
248 END IF;
249
250 -- Source = XpenseXpress
251 ELSIF p_source = C_XpenseXpress THEN
252 IF p_workflow_approved_flag IN (C_WFAutoApproved, C_WFMgrPayablesApproved) THEN
253 v_status_code := C_INVOICED;
254 ELSE
255 v_status_code := C_PENDMGR;
256 END IF;
257
258 -- Source = NonValidatedWebExpense
259 ELSIF p_source = C_NonValidatedWebExpense THEN
260 IF p_workflow_approved_flag = C_WFSaved THEN
261 v_status_code := C_SAVED;
262 ELSIF p_workflow_approved_flag = C_WFRejected THEN
263 v_status_code := C_REJECTED;
264 ELSIF p_workflow_approved_flag = C_WFReturned THEN
265 v_status_code := C_RETURNED;
266 -- ER 1552747 - withdraw expense report
267 ELSIF p_workflow_approved_flag = C_WFWithdrawn THEN
268 v_status_code := C_WITHDRAWN;
269 ELSIF p_workflow_approved_flag = C_WFInProgress THEN
270 v_status_code := C_INPROGRESS;
271 ELSE
272 v_status_code := C_ERROR;
273 END IF;
274
275 -- Source = WebExpense
276 ELSIF p_source = C_WebExpense THEN
277
278 -- Bug 3478073
279 -- Conditionally query WF activities.
280
281 IF p_query_wf_activities = 'Y' THEN
282 -- Check last notification activity
283 v_activity_label := GetWFLastNotificationActivity(p_report_header_id);
284
285 IF p_workflow_approved_flag = C_WFManagerApproved THEN
286 --Bug 2315312:Check for SHORTPAY here as SHORTPAY receipt's
287 -- workflow_approved_flag is only Management Approval,
288 -- NOT Payables Approval.
289 -- Also moved GetWFLastNotificationActivity call to
290 -- above.
291
292 IF(v_activity_label like '%SHORTPAY%') THEN
293 v_status_code := C_RESOLUTN;
294 ELSE
295 v_status_code := C_MGRAPPR;
296 END IF;
297 ELSIF p_workflow_approved_flag = C_WFPayablesApproved THEN
298 v_status_code := C_PENDMGR;
299
300 ELSIF p_workflow_approved_flag = 'Q' THEN --To take care of the scenario where manager approved and auditor rejected ( Bug 6628290)
301 v_status_code := C_RESOLUTN;
302
303 -- workflow approved flag is null or Not Approved
304 ELSIF p_workflow_approved_flag IS NULL
305 OR p_workflow_approved_flag = C_WFMgrPayablesApproved
306 OR p_workflow_approved_flag = C_WFNotApproved THEN
307
308
309 -- Check for third party notification
310 IF v_activity_label = C_REQUEST_EMPLOYEE_APPROVAL THEN
311 v_status_code := C_EMPAPPR;
312
313 -- Check for no manager response or shortpay
314 ELSIF v_activity_label = C_INFORM_PREP_NO_MANAGER_RESP
315 OR v_activity_label = C_INFORM_PREPARER_SHORTPAY
316 OR v_activity_label = C_POLICY_SHORTPAY_NOTICE
317 OR v_activity_label like '%SHORTPAY%' THEN
318 v_status_code := C_RESOLUTN;
319
320 -- Check for System administrator action
321 ELSIF v_activity_label = C_INFORM_SYSADM_AP_VALID_FAIL
322 OR v_activity_label = C_INFORM_CUSTOM_VALIDATE_ERROR
323 OR v_activity_label = C_INFORM_NO_APPROVER
324 OR v_activity_label = C_INFORM_SYSADM_NO_APPROVER THEN
325 v_status_code := C_ERROR;
326
327 -- Check if the report is ready for invoicing
328 ELSIF p_workflow_approved_flag = C_WFMgrPayablesApproved THEN
329 v_status_code := C_INVOICED;
330
331 ELSE
332 -- check approver name
333 v_approver := GetApproverName(p_report_header_id, C_APPROVER_DISPLAY_NAME);
334 IF v_approver IS NULL THEN
335 -- Bug# 8937372: Report status should be shown correctly when the expense report is having multiple approvers.
336 IF v_expense_status_code = 'PENDMGR' THEN
337 v_status_code := C_PENDMGR;
338 ELSE
339 v_status_code := C_ERROR;
340 END IF;
341 ELSE
342 v_status_code := C_PENDMGR;
343 END IF;
344 END IF;
345 END IF;
346 END IF; -- Query wf activities
347 END IF; -- Source
348
349 -- Update cache
350 grsc_old_status_code := v_status_code;
351 grsc_old_report_header_id := p_report_header_id;
352
353 RETURN v_status_code;
354 END GetReportStatusCode;
355
356
357 --
358 -- GetReportStatus
359 -- Purpose: To return the report status using the
360 -- the report status code
361 --
362 -- Input: p_source,
363 -- p_workflow_approved_flag,
364 -- p_report_header_id
365 --
366 -- Output: The report status according to EXPENSE REPORT STATUS
367 -- The report status CODE if the lookup value is not found.
368 --
369
370 FUNCTION GetReportStatus(p_source IN VARCHAR2,
371 p_workflow_approved_flag IN VARCHAR2,
372 p_report_header_id IN NUMBER
373 ) RETURN VARCHAR2
374 IS
375 v_status_code ap_lookup_codes.lookup_code%TYPE := NULL;
376 v_status ap_lookup_codes.displayed_field%TYPE := NULL;
377
378 BEGIN
379 -- Determine the status code
380 v_status_code := GetReportStatusCode(p_source,
381 p_workflow_approved_flag,
382 p_report_header_id);
383
384 -- Using status code, retrieve displayed value.
385 SELECT DISPLAYED_FIELD
386 INTO v_status
387 FROM ap_lookup_codes
388 WHERE LOOKUP_TYPE = C_EXPENSE_REPORT_STATUS
389 AND LOOKUP_CODE = v_status_code;
390
391 RETURN v_status;
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 RETURN v_status_code;
395 END GetReportStatus;
396
397
398 --
399 -- Purpose: To get the current approver of the given expense report
400 --
401 -- Input: p_source,
402 -- p_workflow_approved_flag,
403 -- p_report_header_id
404 -- p_status_code
405 --
406 -- Output: The current approver
407 --
408
409 FUNCTION GetCurrentApprover(p_source IN VARCHAR2,
410 p_workflow_approved_flag IN VARCHAR2,
411 p_report_header_id IN NUMBER,
412 p_status_code IN VARCHAR2)
413 RETURN VARCHAR2
414 IS
415 v_current_approver WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE := NULL;
416 v_status_code ap_lookup_codes.lookup_code%TYPE := NULL;
417
418 BEGIN
419
420 -- Use p_status_code if given.
421 IF p_status_code IS NOT NULL THEN
422 v_status_code := p_status_code;
423 ELSE
424 -- Otherwise, determine the status code
425 -- This is needed for reports submitted before bug fix 2290269
426 v_status_code := GetReportStatusCode(p_source,
427 p_workflow_approved_flag,
428 p_report_header_id,'Y','N');
429 END IF;
430
431 -- Using status code, determine Current Approver
432
433 -- status code = Pending Payables Approval
434 IF v_status_code = C_MGRAPPR THEN
435 v_current_approver := getApApprover;
436
437 -- status code = Pending Expenses Administrator resolution
438 ELSIF v_status_code = C_ERROR THEN
439 v_current_approver := getExpensesAdmin;
440
441 -- status code = Pending Manager Approval
442 ELSIF v_status_code = C_PENDMGR THEN
443 v_current_approver := GetApproverName(p_report_header_id, C_APPROVER_DISPLAY_NAME);
444
445 -- status code = Pending Employee approval
446 ELSIF v_status_code = C_EMPAPPR THEN
447 v_current_approver := GetApproverName(p_report_header_id, C_EMPLOYEE_DISPLAY_NAME);
448
449 -- status code = Pending Your Resolution
450 ELSIF v_status_code = C_RESOLUTN THEN
451 v_current_approver := GetApproverName(p_report_header_id, C_PREPARER_DISPLAY_NAME);
452 END IF; -- status code
453
454 RETURN v_current_approver;
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 RETURN v_current_approver;
458 END GetCurrentApprover;
459
460
461 FUNCTION GetIncludeNotification(p_category IN VARCHAR2,
462 p_trx_id IN NUMBER)
463 RETURN VARCHAR2 IS
464 l_status ap_lookup_codes.lookup_code%TYPE ;
465 l_source ap_expense_report_headers.source%TYPE;
466 l_workflow_approved_flag ap_expense_report_headers.workflow_approved_flag%TYPE;
467 l_report_header_id ap_expense_report_headers.report_header_id%TYPE;
468
469 BEGIN
470
471 IF p_category = 'PERSONAL' then
472
473 SELECT erh.report_header_id, source, workflow_approved_flag
474 INTO l_report_header_id , l_source , l_workflow_approved_flaG
475 FROM ap_credit_card_trxns cct, ap_expense_report_headers erh
476 WHERE trx_id = p_trx_id
477 AND cct.report_header_id = erh.report_header_id;
478
479 l_status := AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(l_source,
480 l_workflow_approved_flag,
481 l_report_header_id);
482
483 IF l_status not in ('UNUSED','SAVED', 'EMPAPPR', 'REJECTED', 'RESOLUTN','WITHDRAWN',
484 'RETURNED') THEN
485 RETURN 'FALSE';
486 ELSE
487 RETURN 'TRUE';
488 END IF;
489
490 ELSE
491 RETURN 'TRUE';
492 END IF;
493
494 END GetIncludeNotification;
495
496 --
497 -- GetBothPayStatusCode
498 -- Purpose: To return the both report status code by considering
499 -- The status of original report and credit card invoice
500 --
501 -- Input: p_status_code,
502 -- p_report_header_id
503 -- p_amt_due_ccard_company
504 -- p_amt_due_employee
505
506 -- Output: The report status code
507 --
508
509 FUNCTION GetBothPayStatusCode(p_report_header_id IN NUMBER,
510 p_status_code IN VARCHAR2,
511 p_amt_due_ccard_company IN NUMBER,
512 p_amt_due_employee IN NUMBER) RETURN VARCHAR2
513 IS
514 l_cc_report_status_code VARCHAR2(30);
515 l_org_cc_report_status_code VARCHAR2(30);
516 l_final_status_code VARCHAR2(30);
517 l_cc_vouch_no NUMBER(15);
518
519
520 BEGIN
521
522 l_final_status_code := p_status_code;
523
524 --Checked if status code is null
525 IF l_final_status_code IS NULL THEN
526
527 RETURN l_final_status_code;
528
529 END IF;
530
531 --If there are not any credit card transactions
532 IF p_amt_due_ccard_company IS NULL OR p_amt_due_ccard_company = 0 THEN
533
534 RETURN l_final_status_code;
535
536 END IF;
537
538
539 --Check if report is split or not, if it is not split it will throw the exception
540 SELECT AERH.VOUCHNO, AERH.EXPENSE_STATUS_CODE
541 INTO l_cc_vouch_no, l_org_cc_report_status_code
542 FROM AP_EXPENSE_REPORT_HEADERS_ALL AERH
543 WHERE AERH.BOTHPAY_PARENT_ID=p_report_header_id;
544
545
546 --Report is split
547
548 --If there are only buisness credit card transactions
549 IF p_amt_due_employee IS NULL OR p_amt_due_employee = 0 THEN
550
551
552 --Check if report is imported or not
553 IF l_cc_vouch_no IS NULL OR l_cc_vouch_no = 0 THEN
554
555 --Report is not imported
556 RETURN l_org_cc_report_status_code;
557
558 ELSE
559 --Report is imported
560
561 SELECT DECODE(AI.CANCELLED_DATE,NULL,
562 DECODE(APS.GROSS_AMOUNT ,0,'PAID',
563 DECODE(AI.PAYMENT_STATUS_FLAG,'Y','PAID',
564 'N','INVOICED',
565 'P','PARPAID',NULL)),
566 'CANCELLED')
567 INTO l_final_status_code
568 FROM
569 AP_INVOICES_ALL AI,
570 AP_EXPENSE_REPORT_HEADERS_ALL AERH,
571 AP_PAYMENT_SCHEDULES_ALL APS
572 WHERE AERH.VOUCHNO = AI.INVOICE_ID
573 AND AERH.BOTHPAY_PARENT_ID = p_report_header_id
574 AND AI.INVOICE_TYPE_LOOKUP_CODE = 'MIXED'
575 AND AI.INVOICE_ID= APS.INVOICE_ID
576 AND AI.VENDOR_ID = AERH.VENDOR_ID;
577
578
579 RETURN l_final_status_code;
580
581 END IF;
582
583
584 END IF;
585
586 --There are both credit card exepnses and cash and other expenses
587
588 SELECT DECODE(AI.CANCELLED_DATE,NULL,
589 DECODE(APS.GROSS_AMOUNT ,0,'PAID',
590 DECODE(AI.PAYMENT_STATUS_FLAG,'Y','PAID',
591 'N','INVOICED',
592 'P','PARPAID',NULL)),
593 'CANCELLED')
594 INTO l_cc_report_status_code
595 FROM
596 AP_INVOICES_ALL AI,
597 AP_EXPENSE_REPORT_HEADERS_ALL AERH,
598 AP_PAYMENT_SCHEDULES_ALL APS
599 WHERE AERH.VOUCHNO = AI.INVOICE_ID
600 AND AERH.BOTHPAY_PARENT_ID = p_report_header_id
601 AND AI.INVOICE_TYPE_LOOKUP_CODE = 'MIXED'
602 AND AI.INVOICE_ID= APS.INVOICE_ID
603 AND AI.VENDOR_ID = AERH.VENDOR_ID;
604
605
606 IF l_cc_report_status_code IS NOT NULL
607 AND p_status_code = 'PAID'
608 AND l_cc_report_status_code = 'PAID' THEN
609
610 l_final_status_code := 'PAID';
611
612 ELSIF l_cc_report_status_code IS NOT NULL
613 AND ((p_status_code = 'INVOICED' AND l_cc_report_status_code = 'PAID') OR
614 (p_status_code = 'PAID' AND l_cc_report_status_code = 'INVOICED')) THEN
615
616 l_final_status_code := 'PARPAID';
617
618 END IF;
619
620 RETURN l_final_status_code;
621
622 EXCEPTION
623 WHEN OTHERS THEN
624
625 RETURN l_final_status_code;
626
627 END GetBothPayStatusCode;
628
629 END AP_WEB_OA_ACTIVE_PKG;
[Home] [Help]