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