[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_EXPRPT_PKG
Source
1 PACKAGE BODY AP_WEB_DB_EXPRPT_PKG AS
2 /* $Header: apwdberb.pls 120.52.12000000.3 2007/10/11 09:50:24 stalasil ship $ */
3
4 ----------------------------------------------------------------------------------------
5 -- Name: GetEmployeeIdFromBothPayParent
6 -- Desc: get employee_id from both pay parent report
7 -- Input: p_bothpay_parent_id - parent report header id
8 -- Output: p_employee_id - employee_id from both pay parent report
9 ---------------------------------------------------------------------------------------
10 PROCEDURE GetEmployeeIdFromBothPayParent(
11 p_bothpay_parent_id IN NUMBER,
12 p_employee_id OUT NOCOPY NUMBER)
13 IS
14 BEGIN
15 SELECT employee_id
16 INTO p_employee_id
17 FROM AP_EXPENSE_REPORT_HEADERS
18 WHERE report_header_id = p_bothpay_parent_id;
19
20 EXCEPTION
21 WHEN OTHERS THEN
22 AP_WEB_DB_UTIL_PKG.RaiseException('GetEmployeeIdFromBothPayParent');
23 APP_EXCEPTION.RAISE_EXCEPTION;
24 END GetEmployeeIdFromBothPayParent;
25
26
27
28 ----------------------------------------------------------------------
29 FUNCTION GetRestorableReportsCursor(P_WebUserID IN AK_WEB_USER_SEC_ATTR_VALUES.web_user_id%TYPE,
30 p_cursor OUT NOCOPY RestorableReportsCursor)
31 RETURN BOOLEAN IS
32 ----------------------------------------------------------------------
33 BEGIN
34 -- Returns reports which can be modified by the web user
35 -- 3176205: hr_empcur includes all workers.
36 -- Note: Instead of PER_WORKFORCE_X, PER_PEOPLE_X is used for the
37 -- following reasons:
38 -- o Query is driven off AP_EXPENSE_REPORT_HEADERS
39 -- o Better performance using PER_PEOPLE_X
40 -- o PER_WORKFORCE_X can return more than one row for a person
41 -- o The only column selected from PER_PEOPLE_X is full_name
42 OPEN p_cursor FOR
43 SELECT ap_erh.REPORT_HEADER_ID,
44 ap_erh.INVOICE_NUM,
45 hr_empcur.FULL_NAME,
46 ap_erh.WEEK_END_DATE,
47 ap_erh.DEFAULT_CURRENCY_CODE,
48 ap_erh.DESCRIPTION,
49 ap_erh.TOTAL,
50 fndl.MEANING
51 FROM AP_EXPENSE_REPORT_HEADERS ap_erh,
52 PER_PEOPLE_X hr_empcur,
53 FND_LOOKUPS fndl
54 WHERE (ap_erh.EMPLOYEE_ID IN
55 (SELECT NUMBER_VALUE
56 FROM AK_WEB_USER_SEC_ATTR_VALUES
57 WHERE ATTRIBUTE_CODE = C_UserAttributeCode AND
58 WEB_USER_ID = P_WebUserID))
59 AND ap_erh.SOURCE = C_RestorableReportSource
60 AND ap_erh.WORKFLOW_APPROVED_FLAG IN
61 (C_WORKFLOW_APPROVED_SAVED, C_WORKFLOW_APPROVED_REJECTED,
62 --ER 1552747 - withdraw expense report
63 C_WORKFLOW_APPROVED_RETURNED, C_WORKFLOW_APPROVED_WITHDRAW)
64 AND ap_erh.EMPLOYEE_ID = hr_empcur.PERSON_ID
65 AND fndl.LOOKUP_TYPE = 'YES_NO'
66 AND DECODE(WORKFLOW_APPROVED_FLAG, C_WORKFLOW_APPROVED_REJECTED, 'Y', 'N') = fndl.LOOKUP_CODE
67 ORDER BY hr_empcur.FULL_NAME, ap_erh.REPORT_HEADER_ID;
68
69 return TRUE;
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 return FALSE;
73 WHEN OTHERS THEN
74 AP_WEB_DB_UTIL_PKG.RaiseException('GetRestorableReportsCursor');
75 APP_EXCEPTION.RAISE_EXCEPTION;
76 return FALSE;
77 END GetRestorableReportsCursor;
78
79
80 -------------------------------------------------------------------
81 FUNCTION GetExpWorkflowInfo(
82 P_ReportID IN expHdr_headerID,
83 P_WorkflowRec OUT NOCOPY ExpWorkflowRec)
84 RETURN BOOLEAN IS
85 -------------------------------------------------------------------
86 l_debug_info VARCHAR2(1000);
87 l_curr_calling_sequence VARCHAR2(100) := 'GetExpWorkflowInfo';
88 BEGIN
89 l_debug_info := 'Retrieve Invoice Number and Workflow Approved Flag for expense report';
90 SELECT INVOICE_NUM,
91 WORKFLOW_APPROVED_FLAG
92 INTO P_WorkflowRec.doc_num,
93 P_WorkflowRec.workflow_flag
94 FROM AP_EXPENSE_REPORT_HEADERS
95 WHERE REPORT_HEADER_ID = P_ReportID;
96
97 return TRUE;
98 EXCEPTION
99 WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
100 return FALSE;
101 WHEN OTHERS THEN
102 AP_WEB_DB_UTIL_PKG.RaiseException(l_curr_calling_sequence, l_debug_info);
103 APP_EXCEPTION.RAISE_EXCEPTION;
104 return FALSE;
105
106 END GetExpWorkflowInfo;
107
108
109 -------------------------------------------------------------------
110 FUNCTION GetReportInfo(
111 p_expenseReportId IN expHdr_headerID,
112 p_exp_info_rec OUT NOCOPY ExpInfoRec
113 ) RETURN BOOLEAN IS
114 -------------------------------------------------------------------
115 BEGIN
116 SELECT employee_id,
117 default_currency_code,
118 invoice_num,
119 total,
120 payment_currency_code,
121 week_end_date
122 INTO p_exp_info_rec.emp_id,
123 p_exp_info_rec.default_curr_code,
124 p_exp_info_rec.doc_num,
125 p_exp_info_rec.total,
126 p_exp_info_rec.payment_curr_code,
127 p_exp_info_rec.week_end_date
128 FROM ap_expense_report_headers
129 WHERE report_header_id = p_expenseReportId;
130
131 return TRUE;
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134 return FALSE;
135 WHEN OTHERS THEN
136 AP_WEB_DB_UTIL_PKG.RaiseException('GetReportInfo');
137 APP_EXCEPTION.RAISE_EXCEPTION;
138 return FALSE;
139
140 END GetReportInfo;
141
142 -------------------------------------------------------------------
143 FUNCTION GetOverrideApproverID(p_report_header_id IN expHdr_headerID,
144 p_id OUT NOCOPY expHdr_overrideApprID)
145 RETURN BOOLEAN IS
146 -------------------------------------------------------------------
147 BEGIN
148 SELECT override_approver_id
149 INTO p_id
150 FROM ap_expense_report_headers
151 WHERE report_header_id = p_report_header_id;
152
153 return TRUE;
154
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 return FALSE;
158 WHEN OTHERS THEN
159 AP_WEB_DB_UTIL_PKG.RaiseException('GetOverrideApproverID');
160 APP_EXCEPTION.RAISE_EXCEPTION;
161 return FALSE;
162
163 END GetOverrideApproverID;
164
165
166 --------------------------------------------------------------------------------
167 FUNCTION GetOrgIdByReportHeaderId(
168 p_header_id IN expHdr_headerID,
169 p_org_id OUT NOCOPY expHdr_orgID) RETURN BOOLEAN IS
170 --------------------------------------------------------------------------------
171 BEGIN
172 SELECT org_id
173 INTO p_org_id
174 FROM ap_expense_report_headers_all
175 WHERE report_header_id = p_header_id;
176
177 return TRUE;
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 return FALSE;
181 WHEN OTHERS THEN
182 AP_WEB_DB_UTIL_PKG.RaiseException('GetOrgIdByReportHeaderId');
183 APP_EXCEPTION.RAISE_EXCEPTION;
184 return FALSE;
185 END GetOrgIdByReportHeaderId;
186
187 -------------------------------------------------------------------
188 FUNCTION GetReportHeaderAttributes(p_report_header_id IN expHdr_headerID,
189 p_default_comb_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
190 p_emp_id OUT NOCOPY expHdr_employeeID,
191 p_flex_concat OUT NOCOPY expHdr_flexConcat,
192 p_attr_category OUT NOCOPY expHdr_attrCategory,
193 p_attr1 OUT NOCOPY expHdr_attr1,
194 p_attr2 OUT NOCOPY expHdr_attr2,
195 p_attr3 OUT NOCOPY expHdr_attr3,
196 p_attr4 OUT NOCOPY expHdr_attr4,
197 p_attr5 OUT NOCOPY expHdr_attr5,
198 p_attr6 OUT NOCOPY expHdr_attr6,
199 p_attr7 OUT NOCOPY expHdr_attr7,
200 p_attr8 OUT NOCOPY expHdr_attr8,
201 p_attr9 OUT NOCOPY expHdr_attr9,
202 p_attr10 OUT NOCOPY expHdr_attr10,
203 p_attr11 OUT NOCOPY expHdr_attr11,
204 p_attr12 OUT NOCOPY expHdr_attr12,
205 p_attr13 OUT NOCOPY expHdr_attr13,
206 p_attr14 OUT NOCOPY expHdr_attr14,
207 p_attr15 OUT NOCOPY expHdr_attr15)
208 RETURN BOOLEAN IS
209 -------------------------------------------------------------------
210 l_default_emp_ccid expHdr_employeeCCID;
211 BEGIN
212 -- 3176205: This query includes all workers except for terminated
213 -- contingent workers and terminated employees who are active contingent
214 -- workers.
215 -- Note: PER_PEOPLE_X would make more sense than PER_WORKFORCE_X
216 -- but we cannot use that because default_code_combination_id
217 -- is selected.
218 -- Therefore we need to limit the query so that at most one
219 -- row will be returned.
220 SELECT exp.employee_id,
221 exp.flex_concatenated,
222 exp.attribute_category,
223 exp.attribute1,
224 exp.attribute2,
225 exp.attribute3,
226 exp.attribute4,
227 exp.attribute5,
228 exp.attribute6,
229 exp.attribute7,
230 exp.attribute8,
231 exp.attribute9,
232 exp.attribute10,
233 exp.attribute11,
234 exp.attribute12,
235 exp.attribute13,
236 exp.attribute14,
237 exp.attribute15
238 INTO p_emp_id,
239 p_flex_concat,
240 p_attr_category,
241 p_attr1,
242 p_attr2,
243 p_attr3,
244 p_attr4,
245 p_attr5,
246 p_attr6,
247 p_attr7,
248 p_attr8,
249 p_attr9,
250 p_attr10,
251 p_attr11,
252 p_attr12,
253 p_attr13,
254 p_attr14,
255 p_attr15
256 FROM ap_expense_report_headers exp
257 WHERE exp.report_header_id = p_report_header_id;
258
259 IF GetDefaultEmpCCID(p_emp_id, l_default_emp_ccid) THEN
260 p_default_comb_id := l_default_emp_ccid;
261 END IF;
262
263 return TRUE;
264 EXCEPTION
265 WHEN NO_DATA_FOUND THEN
266 return FALSE;
267 WHEN OTHERS THEN
268 AP_WEB_DB_UTIL_PKG.RaiseException('GetReportHeaderAttributes');
269 APP_EXCEPTION.RAISE_EXCEPTION;
270 return FALSE;
271 END GetReportHeaderAttributes;
272
273
274 -------------------------------------------------------------------
275 FUNCTION GetReportHeaderInfo(P_ReportID IN expHdr_headerID,
276 P_ExpHdrRec OUT NOCOPY ExpHeaderRec)
277 RETURN BOOLEAN IS
278 -------------------------------------------------------------------
279
280 l_debugInfo VARCHAR2(240);
281 l_DCDName VARCHAR2(240);
282 l_HeaderText VARCHAR2(240);
283 l_MessageText VARCHAR2(240);
284 l_LinkText VARCHAR2(240);
285 BEGIN
286
287 -------------------------------------------------------
288 l_debugInfo := 'GetReportHeaderInfo';
289 -------------------------------------------------------
290 SELECT TO_CHAR(expense_report_id),
291 TO_CHAR(week_end_date),
292 description,
293 default_currency_code,
294 flex_concatenated,
295 TO_CHAR(override_approver_id),
296 override_approver_name,
297 employee_id,
298 TO_CHAR(last_update_date,AP_WEB_DB_UTIL_PKG.C_DetailedDateFormat)
299 INTO P_ExpHdrRec.template_id,
300 P_ExpHdrRec.last_receipt_date,
301 P_ExpHdrRec.description,
302 P_ExpHdrRec.default_curr_code,
303 P_ExpHdrRec.flex_concat,
304 P_ExpHdrRec.override_appr_id,
305 P_ExpHdrRec.override_appr_name,
306 P_ExpHdrRec.emp_id,
307 P_ExpHdrRec.last_update_date
308 FROM AP_EXPENSE_REPORT_HEADERS
309 WHERE REPORT_HEADER_ID = P_ReportID
310 AND SOURCE = C_RestorableReportSource;
311
312 return TRUE;
313
314 EXCEPTION
315 WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
316 return FALSE;
317 WHEN OTHERS THEN
318 AP_WEB_DB_UTIL_PKG.RaiseException('GetReportHeaderInfo');
319 APP_EXCEPTION.RAISE_EXCEPTION;
320 return FALSE;
321 END GetReportHeaderInfo;
322
323
324 -------------------------------------------------------------------
325 FUNCTION GetExpReportExchCurrInfo(p_report_id IN expHdr_headerID,
326 p_exch_rate OUT NOCOPY expHdr_defaultExchRate,
327 p_reimb_precision OUT NOCOPY FND_CURRENCIES_VL.PRECISION%TYPE
328 ) RETURN BOOLEAN IS
329 -------------------------------------------------------------------
330 BEGIN
331 SELECT nvl(default_exchange_rate,1),
332 nvl(precision,0)
333 INTO p_exch_rate,
334 p_reimb_precision
335 FROM fnd_currencies_vl,
336 ap_expense_report_headers
337 WHERE report_header_id = p_report_id
338 AND currency_code = default_currency_code;
339
340 return TRUE;
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 return FALSE;
344 WHEN OTHERS THEN
345 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpReportExchCurrInfo');
346 APP_EXCEPTION.RAISE_EXCEPTION;
347 return FALSE;
348 END GetExpReportExchCurrInfo;
349
350
351 -------------------------------------------------------------------
352 FUNCTION GetNextExpReportID(p_new_report_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
353 -------------------------------------------------------------------
354 l_debug_info VARCHAR2(1000);
355 BEGIN
356 l_debug_info := 'Getting the next report header id';
357
358 SELECT ap_expense_report_headers_s.nextval
359 INTO p_new_report_id
360 FROM sys.dual;
361
362 return TRUE;
363
364 EXCEPTION
365 WHEN NO_DATA_FOUND THEN
366 return FALSE;
367 WHEN OTHERS THEN
368 AP_WEB_DB_UTIL_PKG.RaiseException('GetNextExpReportID', l_debug_info);
369 APP_EXCEPTION.RAISE_EXCEPTION;
370 return FALSE;
371 END GetNextExpReportID;
372
373 -------------------------------------------------------------------
374 FUNCTION GetNextRptHdrID(p_new_report_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
375 -------------------------------------------------------------------
376 l_debug_info VARCHAR2(1000);
377 BEGIN
378 l_debug_info := 'Getting the next report header id';
379
380 SELECT ap_expense_report_headers_s.nextval
381 INTO p_new_report_id
382 FROM dual;
383
384 return TRUE;
385
386 EXCEPTION
387 WHEN NO_DATA_FOUND THEN
388 return FALSE;
389 WHEN OTHERS THEN
390 AP_WEB_DB_UTIL_PKG.RaiseException('GetNextRptHdrID', l_debug_info);
391 APP_EXCEPTION.RAISE_EXCEPTION;
392 return FALSE;
393 END GetNextRptHdrID;
394
395 -------------------------------------------------------------------
396 FUNCTION GetAccountingInfo(
397 p_report_header_id IN expHdr_headerID,
398 p_sys_apply_advances_default OUT NOCOPY apSetUp_applyAdvDefault,
399 p_sys_allow_awt_flag OUT NOCOPY apSetUp_allowAWTFlag,
400 p_sys_default_xrate_type OUT NOCOPY apSetUp_defaultExchRateType,
401 p_sys_make_rate_mandatory OUT NOCOPY apSetUp_makeMandatoryFlag,
402 p_exp_check_address_flag OUT NOCOPY finSysParams_checkAddrFlag,
403 p_default_currency_code OUT NOCOPY expHdr_defaultCurrCode,
404 p_week_end_date OUT NOCOPY expHdr_weekEndDate,
405 p_flex_concatenated OUT NOCOPY expHdr_flexConcat,
406 p_employee_id OUT NOCOPY expHdr_employeeID)
407 RETURN BOOLEAN IS
408 -------------------------------------------------------------------
409
410 /*Bug 2699333:Removed FIN table since expense_check_address_flag can
411 be got from hr_employees_current_v view and there is no
412 join with FIN table.
413
414 Added GS.set_of_books_id=nvl(HR.set_of_books_id,
415 GS.set_of_books_id)
416 to avoid Merge join cartesians.
417 */
418 l_emp_set_of_books_id gl_sets_of_books.set_of_books_id%type;
419 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
420 l_exp_check_address_flag per_employees_x.expense_check_address_flag%type;
421 l_fin_exp_check_address_flag financials_system_parameters.expense_check_address_flag%type;
422 BEGIN
423 -- 3176205: This query includes all workers except for terminated
424 -- contingent workers and terminated employees who are active contingent
425 -- workers.
426 SELECT nvl(S.apply_advances_default, 'N'),
427 nvl(S.allow_awt_flag, 'N'),
428 decode(S.base_currency_code, RH.default_currency_code, null,
429 S.default_exchange_rate_type),
430 nvl(S.make_rate_mandatory_flag, 'N'),
431 RH.default_currency_code,
432 week_end_date,
433 flex_concatenated,
434 RH.employee_id
435 INTO p_sys_apply_advances_default,
436 p_sys_allow_awt_flag,
437 p_sys_default_xrate_type,
438 p_sys_make_rate_mandatory,
439 p_default_currency_code,
440 p_week_end_date,
441 p_flex_concatenated,
442 p_employee_id
443 FROM ap_system_parameters S,
444 ap_expense_report_headers RH
445 WHERE RH.report_header_id = p_report_header_id;
446
447 SELECT expense_check_address_flag
448 INTO l_exp_check_address_flag
449 FROM (
450 SELECT emp.expense_check_address_flag
451 FROM per_employees_x emp
452 WHERE emp.employee_id = p_employee_id
453 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
454 UNION ALL
455 SELECT emp.expense_check_address_flag
456 FROM per_cont_workers_current_x emp
457 WHERE emp.person_id = p_employee_id
458 );
459
460 SELECT expense_check_address_flag
461 INTO l_fin_exp_check_address_flag
462 FROM financials_system_parameters;
463
464 p_exp_check_address_flag := nvl(l_exp_check_address_flag, l_fin_exp_check_address_flag);
465
466 return TRUE;
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 return FALSE;
470 WHEN OTHERS THEN
471 AP_WEB_DB_UTIL_PKG.RaiseException('GetAccountingInfo');
472 APP_EXCEPTION.RAISE_EXCEPTION;
473 return FALSE;
474 END GetAccountingInfo;
475
476
477 -------------------------------------------------------------------
478 FUNCTION GetExpReportInfo(
479 p_report_header_id IN expHdr_headerID,
480 p_description OUT NOCOPY VARCHAR2,
481 p_ccard_amt OUT NOCOPY expHdr_amtDueCCardCompany,
482 p_total OUT NOCOPY expHdr_total
483 ) RETURN BOOLEAN IS
484 -------------------------------------------------------------------
485 l_exp_rpt_purpose VARCHAR2(240);
486 l_emp_masked_cc_number VARCHAR2(320);
487 l_emp_full_name VARCHAR2(240);
488 l_description VARCHAR2(1000); -- bug3303390
489 BEGIN
490
491 SELECT DISTINCT icc.masked_cc_number || '/' || emp.full_name,
492 erh.description,
493 emp.full_name,
494 erh.amt_due_ccard_company,
495 erh.total
496 INTO l_emp_masked_cc_number,
497 l_exp_rpt_purpose,
498 l_emp_full_name,
499 p_ccard_amt,
500 p_total
501 FROM ap_expense_report_headers erh,
502 ap_credit_card_trxns cc,
503 /* hr_employees emp Bug 3006221 */
504 per_people_f emp,
505 ap_cards aca,
506 iby_creditcard icc
507 WHERE cc.card_id = aca.card_id
508 AND aca.card_reference_id = icc.instrid
509 AND erh.report_header_id = p_report_header_id
510 AND cc.report_header_id = p_report_header_id
511 AND erh.employee_id = emp.person_id
512 AND TRUNC(sysdate) BETWEEN emp.effective_start_date
513 AND emp.effective_end_date /* Bug 3111161 */
514 AND cc.category='BUSINESS';
515
516 -- Bug 2786500:By default message will have 'EMP_CARD_NUM - EXP_RPT_PURPOSE'
517 -- Message can be modified to have one or combination of the following
518 -- EMP_CARD_NUM, EXP_RPT_PURPOSE, EMP_FULL_NAME
519 FND_MESSAGE.SET_NAME('SQLAP','OIE_INVOICE_DESC');
520 l_description := FND_MESSAGE.GET;
521 l_description := replace(l_description,'EMP_FULL_NAME',l_emp_full_name);
522 l_description := replace(l_description,'EMP_CARD_NUM',l_emp_masked_cc_number);
523 l_description := replace(l_description,'EXP_RPT_PURPOSE',l_exp_rpt_purpose);
524 p_description := substrb(l_description,1,240); --2227571
525 p_description := rtrim(p_description); --2227571
526 IF substr(p_description, -1) = '-' THEN
527 p_description := substr(p_description,1, length(p_description) -2);
528 END IF;
529
530 return TRUE;
531
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 return FALSE;
535 WHEN OTHERS THEN
536 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpReportInfo');
537 APP_EXCEPTION.RAISE_EXCEPTION;
538 return FALSE;
539 END GetExpReportInfo;
540
541
542 -------------------------------------------------------------------
543 FUNCTION ExpReportShortpaid(P_ReportID IN expHdr_headerID,
544 P_Shortpaid OUT NOCOPY BOOLEAN)
545 RETURN BOOLEAN IS
546 -------------------------------------------------------------------
547 l_shortpay_id expHdr_shortpayParentID := NULL;
548 BEGIN
549 SELECT shortpay_parent_id
550 INTO l_shortpay_id
551 FROM ap_expense_report_headers
552 WHERE report_header_id = P_ReportID;
553
554 IF (l_shortpay_id IS NULL) THEN
555 P_Shortpaid := FALSE;
556 ELSE
557 P_Shortpaid := TRUE;
558 END IF;
559
560 return TRUE;
561
562 EXCEPTION
563 WHEN NO_DATA_FOUND THEN
564 return FALSE;
565 WHEN OTHERS THEN
566 AP_WEB_DB_UTIL_PKG.RaiseException('ExpReportShortpaid');
567 APP_EXCEPTION.RAISE_EXCEPTION;
568 return FALSE;
569 END ExpReportShortpaid;
570
571 -------------------------------------------------------------------
572 FUNCTION InsertReportHeader(p_xpense_rec IN XpenseInfoRec,
573 p_ExpReportHeaderInfo IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec
574 ) RETURN BOOLEAN IS
575 -------------------------------------------------------------------
576 l_curr_calling_sequence VARCHAR2(100) := 'AddReportHeader';
577 l_debug_info VARCHAR2(100);
578 BEGIN
579
580 l_debug_info := 'Add Report Header';
581
582 INSERT INTO AP_EXPENSE_REPORT_HEADERS
583 (report_header_id,
584 employee_id,
585 override_approver_id,
586 override_approver_name,
587 week_end_date,
588 vouchno,
589 total,
590 invoice_num,
591 expense_report_id,
592 set_of_books_id,
593 source,
594 description,
595 flex_concatenated,
596 default_currency_code,
597 payment_currency_code, --1396360
598 creation_date,
599 created_by,
600 last_update_date,
601 last_updated_by,
602 workflow_approved_flag,
603 amt_due_employee,
604 amt_due_ccard_company,
605 org_id)
606 VALUES
607 (p_xpense_rec.report_header_id,
608 p_ExpReportHeaderInfo.employee_id,
609 p_xpense_rec.approver_id,
610 p_ExpReportHeaderInfo.override_approver_name,
611 p_xpense_rec.week_end_date,
612 p_xpense_rec.vouchno,
613 p_xpense_rec.total,
614 p_xpense_rec.document_number,
615 to_number(p_ExpReportHeaderInfo.template_id),
616 p_xpense_rec.set_of_books_id,
617 p_xpense_rec.source,
618 AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(p_ExpReportHeaderInfo.purpose),
619 p_ExpReportHeaderInfo.cost_center,
620 p_ExpReportHeaderInfo.reimbursement_currency_code,
621 p_ExpReportHeaderInfo.reimbursement_currency_code,
622 sysdate,
623 icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
624 sysdate,
625 icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
626 p_xpense_rec.workflow_flag,
627 p_ExpReportHeaderInfo.amt_due_employee,
628 p_ExpReportHeaderInfo.amt_due_ccCompany,
629 nvl( p_xpense_rec.org_id, mo_global.get_current_org_id() ) );
630
631 return TRUE;
632 EXCEPTION -- Block which encapsulates the delete and insert code
633 WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
634 AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeader',l_debug_info,
635 'AP_WEB_SAVESUB_DELETE_FAILED',
636 'V_ReportHeaderID = ' || p_xpense_rec.report_header_id
637 ||', Invoice Num = '|| p_xpense_rec.document_number);
638 APP_EXCEPTION.RAISE_EXCEPTION;
639 return FALSE;
640 WHEN OTHERS THEN
641 IF (SQLCODE = -00054) THEN
642 -- Tried to obtain lock when deleting on an already locked row
643 -- Get invoice prefix profile option, and trim if it is too long
644 -- Get message stating delete failed
645 AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeader',l_debug_info,
646 'AP_WEB_SAVESUB_LOCK_FAILED',
647 'V_ReportHeaderID = ' || p_xpense_rec.report_header_id
648 );
649 APP_EXCEPTION.RAISE_EXCEPTION;
650 END IF;
651 return FALSE;
652 END InsertReportHeader; -- Block which encapsulates the delete and insert code
653
654 -------------------------------------------------------------------
655 FUNCTION InsertReportHeaderLikeExisting(p_orig_report_header_id IN expHdr_headerID,
656 p_xpense_rec IN XpenseInfoRec
657 ) RETURN BOOLEAN IS
658 -------------------------------------------------------------------
659 BEGIN
660 INSERT INTO ap_expense_report_headers
661 (report_header_id,
662 employee_id,
663 week_end_date,
664 creation_date,
665 created_by,
666 last_update_date,
667 last_updated_by,
668 vouchno,
669 total,
670 vendor_id,
671 vendor_site_id,
672 expense_check_address_flag,
673 reference_1,
674 reference_2,
675 invoice_num,
676 expense_report_id,
677 accts_pay_code_combination_id,
678 set_of_books_id,
679 source,
680 expense_status_code,
681 purgeable_flag,
682 accounting_date,
683 maximum_amount_to_apply,
684 advance_invoice_to_apply,
685 apply_advances_default,
686 employee_ccid,
687 reject_code,
688 hold_lookup_code,
689 attribute_category,
690 attribute1,
691 attribute2,
692 attribute3,
693 attribute4,
694 attribute5,
695 attribute6,
696 attribute7,
697 attribute8,
698 attribute9,
699 attribute10,
700 attribute11,
701 attribute12,
702 attribute13,
703 attribute14,
704 attribute15,
705 default_currency_code,
706 default_exchange_rate_type,
707 default_exchange_rate,
708 default_exchange_date,
709 last_update_login,
710 voucher_num,
711 doc_category_code,
712 awt_group_id,
713 org_id,
714 workflow_approved_flag,
715 flex_concatenated,
716 global_attribute_category,
717 global_attribute1,
718 global_attribute2,
719 global_attribute3,
720 global_attribute4,
721 global_attribute5,
722 global_attribute6,
723 global_attribute7,
724 global_attribute8,
725 global_attribute9,
726 global_attribute10,
727 global_attribute11,
728 global_attribute12,
729 global_attribute13,
730 global_attribute14,
731 global_attribute15,
732 global_attribute16,
733 global_attribute17,
734 global_attribute18,
735 global_attribute19,
736 global_attribute20,
737 override_approver_id,
738 payment_cross_rate_type,
739 payment_cross_rate_date,
740 payment_cross_rate,
741 payment_currency_code,
742 core_wf_status_flag,
743 amt_due_employee,
744 amt_due_ccard_company,
745 description,
746 bothpay_parent_id,
747 shortpay_parent_id,
748 paid_on_behalf_employee_id,
749 report_submitted_date, -- 2646985
750 receipts_received_date, -- jrautiai 3008468
751 last_audited_by, -- jrautiai 2987037
752 audit_code, -- jrautiai 3255738
753 report_filing_number
754 )
755 SELECT p_xpense_rec.report_header_id,
756 erh.employee_id, --2446559
757 week_end_date,
758 sysdate,
759 -- Bug 2473070 NVL(icx_sec.getID(icx_sec.PV_USER_ID), erh.created_by),
760 NVL(p_xpense_rec.preparer_id, erh.created_by),
761 sysdate,
762 -- Bug 2473070 NVL(icx_sec.getID(icx_sec.PV_USER_ID), erh.last_updated_by),
763 NVL(p_xpense_rec.last_updated_by, erh.last_updated_by),
764 p_xpense_rec.vouchno,
765 decode(p_xpense_rec.total,-1,erh.amt_due_ccard_company, p_xpense_rec.total), --result of combining the apis
766 NVL(p_xpense_rec.vendor_id, erh.vendor_id),
767 NVL(p_xpense_rec.vendor_site_id, erh.vendor_site_id),
768 NVL(p_xpense_rec.expense_check_address_flag, erh.expense_check_address_flag),
769 reference_1,
770 reference_2,
771 p_xpense_rec.document_number, --invoice_num
772 expense_report_id,
773 NVL(p_xpense_rec.accts_pay_comb_id,erh.accts_pay_code_combination_id),
774 set_of_books_id,
775 NVL(p_xpense_rec.source,erh.source),
776 p_xpense_rec.expense_status_code,
777 purgeable_flag,
778 accounting_date,
779 '', -- Bug 3654956
780 '', -- 4001778 advance_invoice_to_apply,
781 '', -- 4001778 apply_advances_default,
782 employee_ccid,
783 reject_code,
784 hold_lookup_code,
785 attribute_category,
786 attribute1,
787 attribute2,
788 attribute3,
789 attribute4,
790 attribute5,
791 attribute6,
792 attribute7,
793 attribute8,
794 attribute9,
795 attribute10,
796 attribute11,
797 attribute12,
798 attribute13,
799 attribute14,
800 attribute15,
801 default_currency_code,
802 default_exchange_rate_type,
803 default_exchange_rate,
804 default_exchange_date,
805 NVL(p_xpense_rec.last_update_login, erh.last_update_login),
806 voucher_num,
807 doc_category_code,
808 awt_group_id,
809 NVL(p_xpense_rec.org_id, erh.org_id),
810 decode(p_xpense_rec.workflow_flag, NULL,erh.workflow_approved_flag, decode(p_xpense_rec.workflow_flag,'POLICY','M',null)),
811 flex_concatenated,
812 global_attribute_category,
813 global_attribute1,
814 global_attribute2,
815 global_attribute3,
816 global_attribute4,
817 global_attribute5,
818 global_attribute6,
819 global_attribute7,
820 global_attribute8,
821 global_attribute9,
822 global_attribute10,
823 global_attribute11,
824 global_attribute12,
825 global_attribute13,
826 global_attribute14,
827 global_attribute15,
828 global_attribute16,
829 global_attribute17,
830 global_attribute18,
831 global_attribute19,
832 global_attribute20,
833 override_approver_id,
834 payment_cross_rate_type,
835 payment_cross_rate_date,
836 payment_cross_rate,
837 payment_currency_code,
838 core_wf_status_flag,
839 nvl(p_xpense_rec.amt_due_employee,0), --amt_due_employee
840 decode(p_xpense_rec.amt_due_ccard, NULL, nvl(erh.amt_due_ccard_company,0), p_xpense_rec.amt_due_ccard), --amt_due_ccard_company
841 NVL(p_xpense_rec.description,erh.description), --description
842 p_xpense_rec.bothpay_report_header_id, --bothpay_parent_id
843 NVL(p_xpense_rec.shortpay_parent_id, erh.shortpay_parent_id),
844 decode(p_xpense_rec.behalf_employee_id, -1, erh.employee_id, erh.paid_on_behalf_employee_id), --paid_on_behalf_employee_id
845 report_submitted_date, -- 2646985
846 receipts_received_date, -- jrautiai 3008468
847 last_audited_by, -- jrautiai 2987037
848 audit_code, -- jrautiai 3255738
849 report_filing_number
850 FROM ap_expense_report_headers erh
851 WHERE report_header_id = p_orig_report_header_id;
852
853 return TRUE;
854 EXCEPTION
855 WHEN NO_DATA_FOUND THEN
856 return FALSE;
857 WHEN OTHERS THEN
858 AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeaderLikeExisting');
859 APP_EXCEPTION.RAISE_EXCEPTION;
860 return FALSE;
861 END InsertReportHeaderLikeExisting;
862
863 FUNCTION SetDefaultExchRateType(p_report_header_id IN expHdr_headerID,
864 p_xrate_type IN expHdr_defaultXchRateType)
865 RETURN BOOLEAN IS
866 BEGIN
867 UPDATE ap_expense_report_headers
868 SET default_exchange_rate_type = p_xrate_type
869 WHERE report_header_id = p_report_header_id;
870
871 return TRUE;
872 EXCEPTION
873 WHEN NO_DATA_FOUND THEN
874 return FALSE;
875 WHEN OTHERS THEN
876 AP_WEB_DB_UTIL_PKG.RaiseException('SetDefaultExchRateType');
877 APP_EXCEPTION.RAISE_EXCEPTION;
878 return FALSE;
879 END SetDefaultExchRateType;
880
881 ------------------------------------------------------------------
882 FUNCTION SetExpenseHeaderInfo(
883 p_report_header_id IN expHdr_headerID,
884 p_exp_check_address_flag IN expHdr_expCheckAddrFlag,
885 p_source IN expHdr_source,
886 p_workflow_approve_flag IN expHdr_wkflApprvdFlag,
887 p_sys_apply_advances_default IN expHdr_applyAdvDefault,
888 p_available_prepays IN NUMBER,
889 p_sys_allow_awt_flag IN AP_SYSTEM_PARAMETERS.allow_awt_flag%TYPE,
890 p_ven_allow_awt_flag IN AP_SUPPLIERS.allow_awt_flag%TYPE,
891 p_ven_awt_group_id IN expHdr_awtGroupID,
892 p_sys_default_xrate_type IN expHdr_defaultXchRateType,
893 p_week_end_date IN expHdr_defaultExchDate,
894 p_default_exchange_rate IN expHdr_defaultExchRate,
895 p_employee_ccid IN expHdr_employeeCCID
896 ) RETURN BOOLEAN IS
897 -------------------------------------------------------------------
898 BEGIN
899 UPDATE ap_expense_report_headers RH
900 SET expense_check_address_flag = p_exp_check_address_flag,
901 source = p_source,
902 workflow_approved_flag = p_workflow_approve_flag,
903 apply_advances_default = decode(apply_advances_default,'Y','Y',decode(p_sys_apply_advances_default, 'Y',
904 decode(sign(p_available_prepays), 1, 'Y', 'N'), 'N')),
905 awt_group_id = decode(p_sys_allow_awt_flag, 'Y',
906 decode(p_ven_allow_awt_flag, 'Y', p_ven_awt_group_id,
907 null), null),
908 default_exchange_rate_type = p_sys_default_xrate_type,
909 default_exchange_date = decode(p_sys_default_xrate_type, null, null,
910 p_week_end_date),
911 default_exchange_rate = p_default_exchange_rate,
912 employee_ccid = p_employee_ccid
913 WHERE report_header_id = p_report_header_id;
914
915 return TRUE;
916
917 EXCEPTION
918 WHEN NO_DATA_FOUND THEN
919 return FALSE;
920 WHEN OTHERS THEN
921 AP_WEB_DB_UTIL_PKG.RaiseException('SetExpenseHeaderInfo');
922 APP_EXCEPTION.RAISE_EXCEPTION;
923 return FALSE;
924 END SetExpenseHeaderInfo;
925
926
927 -------------------------------------------------------------------
928 FUNCTION SetAmtDuesAndTotal(
929 p_report_header_id IN expHdr_headerID,
930 p_amt_due_ccard_company IN expHdr_amtDueCCardCompany,
931 p_amt_due_employee IN expHdr_amtDueEmployee,
932 p_total IN expHdr_total
933 ) RETURN BOOLEAN IS
934 -------------------------------------------------------------------
935 l_max_amt_to_apply expHdr_maxAmountApplied := NULL;--Bug#6400678
936 l_amt_due_employee expHdr_amtDueEmployee := 0;--Bug#6400678
937 BEGIN
938 l_amt_due_employee := p_amt_due_employee;
939
940 SELECT decode (maximum_amount_to_apply, NULL, NULL,least( p_total, maximum_amount_to_apply))-- Bug 3654956
941 INTO l_max_amt_to_apply
942 from ap_expense_report_headers
943 where report_header_id = p_report_header_id;
944
945 --Bug#6400678 : Calculate amount due to employee taking advance applied into consideration
946
947 IF(l_max_amt_to_apply IS NOT NULL AND l_max_amt_to_apply <> 0) THEN
948 l_amt_due_employee := l_amt_due_employee - l_max_amt_to_apply;
949 END IF;
950
951 UPDATE ap_expense_report_headers
952 SET amt_due_ccard_company = p_amt_due_ccard_company,
953 amt_due_employee = l_amt_due_employee,
954 total = p_total,
955 maximum_amount_to_apply = l_max_amt_to_apply
956 WHERE report_header_id = p_report_header_id;
957
958 return TRUE;
959
960 EXCEPTION
961 WHEN NO_DATA_FOUND THEN
962 return FALSE;
963 WHEN OTHERS THEN
964 AP_WEB_DB_UTIL_PKG.RaiseException('SetAmtDuesAndTotal');
965 APP_EXCEPTION.RAISE_EXCEPTION;
966 return FALSE;
967 END SetAmtDuesAndTotal;
968
969
970 -------------------------------------------------------------------
971 FUNCTION SetBothpayReportHeader(
972 p_report_header_id IN expHdr_headerID,
973 p_sub_total IN NUMBER,
974 p_vendor_id IN expHdr_vendorID,
975 p_vendor_site_id IN expHdr_vendorSiteID,
976 p_bothpay_id IN expHdr_bothpayParentID,
977 p_paid_on_behalf_id IN expHdr_paidOnBehalfEmpID,
978 p_total IN expHdr_total,
979 p_amt_due_ccard_company IN expHdr_amtDueCCardCompany,
980 p_employee_id IN expHdr_employeeID,
981 p_description IN expHdr_description,
982 p_source IN expHdr_source,
983 p_accts_comb_id IN expHdr_acctsPayCodeCombID
984 ) RETURN BOOLEAN IS
985 -------------------------------------------------------------------
986 BEGIN
987 /* Bug 3654956 : Seeting the value of maximum_amount_to_apply*/
988 UPDATE ap_expense_report_headers
989 SET total = decode(p_total, NULL, total, total - p_sub_total),
990 vendor_id = p_vendor_id,
991 vendor_site_id = p_vendor_site_id,
992 amt_due_ccard_company = NVL(p_amt_due_ccard_company, amt_due_ccard_company),
993 bothpay_parent_id = p_bothpay_id,
994 paid_on_behalf_employee_id = DECODE(p_paid_on_behalf_id, -1, employee_id, p_paid_on_behalf_id),
995 employee_id = NVL(p_employee_id, employee_id),
996 description = NVL(p_description, description),
997 source = NVL(p_source, source),
998 accts_pay_code_combination_id = NVL(p_accts_comb_id, accts_pay_code_combination_id),
999 maximum_amount_to_apply = decode (maximum_amount_to_apply, NULL, NULL,
1000 decode(p_total, NULL,NULL,
1001 least( total - p_sub_total, maximum_amount_to_apply)))
1002 WHERE report_header_id = p_report_header_id;
1003
1004 return TRUE;
1005
1006 EXCEPTION
1007 WHEN NO_DATA_FOUND THEN
1008 return FALSE;
1009 WHEN OTHERS THEN
1010 AP_WEB_DB_UTIL_PKG.RaiseException('SetBothpayReportHeader');
1011 APP_EXCEPTION.RAISE_EXCEPTION;
1012 return FALSE;
1013 END SetBothpayReportHeader;
1014
1015 -------------------------------------------------------------------
1016 FUNCTION SetWkflApprvdFlagAndSource(
1017 p_report_header_id IN expHdr_headerID,
1018 p_flag IN expHdr_wkflApprvdFlag,
1019 p_source IN expHdr_source
1020 ) RETURN BOOLEAN IS
1021 -------------------------------------------------------------------
1022 BEGIN
1023
1024 UPDATE ap_expense_report_headers erh
1025 SET workflow_approved_flag = NVL(p_flag, decode(erh.workflow_approved_flag, 'M', 'A', erh.workflow_approved_flag)),
1026 source = NVL(p_source, erh.source)
1027 WHERE report_header_id = p_report_header_id;
1028
1029 return TRUE;
1030
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 return FALSE;
1034 WHEN OTHERS THEN
1035 AP_WEB_DB_UTIL_PKG.RaiseException('SetWkflApprvdFlagAndSource');
1036 APP_EXCEPTION.RAISE_EXCEPTION;
1037 return FALSE;
1038 END SetWkflApprvdFlagAndSource;
1039
1040
1041 /* This is a combination of two FUNCTIONs. The other one is
1042 commented out nocopy below */
1043 -------------------------------------------------------------------
1044 FUNCTION SetWkflApprvdFlag(p_report_header_id IN expHdr_headerID)
1045 RETURN BOOLEAN IS
1046 -------------------------------------------------------------------
1047 BEGIN
1048
1049 UPDATE ap_expense_report_headers
1050 SET workflow_approved_flag = decode(workflow_approved_flag,
1051 'P','Y',
1052 C_WORKFLOW_APPROVED_REQUEST, C_WORKFLOW_APPROVED_REQUEST, -- AP already rejected
1053 C_WORKFLOW_APPROVED_REJECTED, C_WORKFLOW_APPROVED_REJECTED, -- AP already requests more info
1054 'M')
1055 WHERE report_header_id = p_report_header_id;
1056
1057 return TRUE;
1058
1059 EXCEPTION
1060 WHEN NO_DATA_FOUND THEN
1061 return FALSE;
1062 WHEN OTHERS THEN
1063 AP_WEB_DB_UTIL_PKG.RaiseException('SetWkflApprvdFlag');
1064 APP_EXCEPTION.RAISE_EXCEPTION;
1065 return FALSE;
1066 END SetWkflApprvdFlag;
1067
1068 -------------------------------------------------------------------
1069 FUNCTION SetWkflApprvdFlag2(p_report_header_id IN expHdr_headerID)
1070 RETURN BOOLEAN IS
1071 -------------------------------------------------------------------
1072 BEGIN
1073
1074 UPDATE ap_expense_report_headers
1075 SET workflow_approved_flag = decode(workflow_approved_flag,
1076 'P', 'Y',
1077 'Y', 'Y',
1078 'M')
1079 WHERE report_header_id = p_report_header_id;
1080
1081 return TRUE;
1082
1083 EXCEPTION
1084 WHEN NO_DATA_FOUND THEN
1085 return FALSE;
1086 WHEN OTHERS THEN
1087 AP_WEB_DB_UTIL_PKG.RaiseException('SetWkflApprvdFlag2');
1088 APP_EXCEPTION.RAISE_EXCEPTION;
1089 return FALSE;
1090 END SetWkflApprvdFlag2;
1091
1092 -------------------------------------------------------------------
1093 FUNCTION DeleteReportHeaderAtDate(
1094 P_ReportID IN expHdr_headerID,
1095 P_LastUpdateDate IN expHdr_lastUpdateDate)
1096 RETURN BOOLEAN IS
1097 -------------------------------------------------------------------
1098 l_TempReportHeaderID NUMBER;
1099
1100 BEGIN
1101
1102 -- Selects report headers to delete. The actual value being selected does
1103 -- not matter. For some reason the compiler complains when the OF
1104 -- column-name in the FOR UPDATE is missing and NOWAIT is used, so the OF
1105 -- REPORT_HEADER_ID is used as a place holder.
1106 SELECT REPORT_HEADER_ID
1107 INTO l_TempReportHeaderID
1108 FROM AP_EXPENSE_REPORT_HEADERS
1109 WHERE REPORT_HEADER_ID = P_ReportID
1110 AND SOURCE = C_RestorableReportSource
1111 AND LAST_UPDATE_DATE = NVL(P_LastUpdateDate, LAST_UPDATE_DATE)
1112 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
1113
1114 -- Delete matching line
1115 if (DeleteExpenseReport(P_ReportID)) then null; end if;
1116
1117 return TRUE;
1118
1119 EXCEPTION
1120 WHEN NO_DATA_FOUND THEN
1121 return FALSE;
1122 WHEN OTHERS THEN
1123 AP_WEB_DB_UTIL_PKG.RaiseException('DeleteReportHeaderAtDate');
1124 APP_EXCEPTION.RAISE_EXCEPTION;
1125 return FALSE;
1126 END DeleteReportHeaderAtDate;
1127
1128
1129 -------------------------------------------------------------------
1130 FUNCTION DeleteExpenseReport(p_report_header_id IN expHdr_headerID)
1131 RETURN BOOLEAN IS
1132 -------------------------------------------------------------------
1133 l_curr_calling_sequence VARCHAR2(100) := 'DeleteExpenseReport';
1134
1135 BEGIN
1136 DELETE FROM ap_expense_report_headers
1137 WHERE report_header_id = p_report_header_id;
1138
1139 /* Delete All Notes associated with Expense Report */
1140 AP_WEB_NOTES_PKG.DeleteERNotes (
1141 p_src_report_header_id => p_report_header_id
1142 );
1143
1144 /* Delete attachments assocated with the header */
1145 fnd_attached_documents2_pkg.delete_attachments(
1146 X_entity_name => 'OIE_HEADER_ATTACHMENTS',
1147 X_pk1_value => p_report_header_id,
1148 X_delete_document_flag => 'Y'
1149 );
1150
1151 return TRUE;
1152
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN
1155 return FALSE;
1156 WHEN OTHERS THEN
1157 AP_WEB_DB_UTIL_PKG.RaiseException('DeleteExpenseReport');
1158 APP_EXCEPTION.RAISE_EXCEPTION;
1159 return FALSE;
1160 END DeleteExpenseReport;
1161
1162 -------------------------------------------------------------------
1163 FUNCTION ResubmitExpenseReport(p_workflow_approved_flag IN VARCHAR2)
1164 RETURN BOOLEAN IS
1165 -------------------------------------------------------------------
1166 BEGIN
1167 /* Bug 2636718 - Please note that this piece of the code does not
1168 have C_WORKFLOW_WITHDRAWN because this API is used to determine
1169 if its going to create a new workflow process or re start from
1170 a block activity. This API will return if workflow is restarted
1171 from a blocked activity. Withdraw needs to create a new workflow
1172 process
1173 */
1174 if ((p_workflow_approved_flag IS NOT NULL) AND (p_workflow_approved_flag = C_WORKFLOW_APPROVED_REJECTED OR p_workflow_approved_flag = C_WORKFLOW_APPROVED_RETURNED)) then
1175 return TRUE;
1176 else
1177 return FALSE;
1178 end if;
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 AP_WEB_DB_UTIL_PKG.RaiseException('ResubmitExpenseReport');
1183 APP_EXCEPTION.RAISE_EXCEPTION;
1184 return FALSE;
1185 END ResubmitExpenseReport;
1186
1187
1188
1189 -------------------------------------------------------------------
1190 -- Name: DuplicateHeader
1191 -- Desc: duplicates an Expense Report Header
1192 -- Input: p_source_report_header_id - source expense report header id
1193 -- Returns: p_target_report_header_id - target expense report header id
1194 -------------------------------------------------------------------
1195 PROCEDURE DuplicateHeader(
1196 p_user_id IN NUMBER,
1197 p_source_report_header_id IN expHdr_headerID,
1198 p_target_report_header_id IN OUT NOCOPY expHdr_headerID) IS
1199
1200 l_invoice_num varchar2(50);
1201 l_employee_id number;
1202
1203 BEGIN
1204
1205 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPRPT_PKG',
1206 'start DuplicateHeader');
1207
1208 select EMPLOYEE_ID into l_employee_id
1209 from AP_EXPENSE_REPORT_HEADERS
1210 where REPORT_HEADER_ID = p_source_report_header_id;
1211 l_invoice_num := AP_WEB_OA_CUSTOM_PKG.GetNewExpenseReportInvoice(l_employee_id, p_user_id, p_target_report_header_id);
1212
1213 insert into AP_EXPENSE_REPORT_HEADERS
1214 (
1215 REPORT_HEADER_ID,
1216 WEEK_END_DATE,
1217 EMPLOYEE_ID,
1218 VOUCHNO,
1219 TOTAL,
1220 EXPENSE_REPORT_ID,
1221 SET_OF_BOOKS_ID,
1222 SOURCE,
1223 DESCRIPTION,
1224 ATTRIBUTE_CATEGORY,
1225 ATTRIBUTE1,
1226 ATTRIBUTE2,
1227 ATTRIBUTE3,
1228 ATTRIBUTE4,
1229 ATTRIBUTE5,
1230 ATTRIBUTE6,
1231 ATTRIBUTE7,
1232 ATTRIBUTE8,
1233 ATTRIBUTE9,
1234 ATTRIBUTE10,
1235 ATTRIBUTE11,
1236 ATTRIBUTE12,
1237 ATTRIBUTE13,
1238 ATTRIBUTE14,
1239 ATTRIBUTE15,
1240 DEFAULT_CURRENCY_CODE,
1241 DEFAULT_EXCHANGE_RATE_TYPE,
1242 DEFAULT_EXCHANGE_RATE,
1243 DEFAULT_EXCHANGE_DATE,
1244 ORG_ID,
1245 GLOBAL_ATTRIBUTE_CATEGORY,
1246 GLOBAL_ATTRIBUTE1,
1247 GLOBAL_ATTRIBUTE2,
1248 GLOBAL_ATTRIBUTE3,
1249 GLOBAL_ATTRIBUTE4,
1250 GLOBAL_ATTRIBUTE5,
1251 GLOBAL_ATTRIBUTE6,
1252 GLOBAL_ATTRIBUTE7,
1253 GLOBAL_ATTRIBUTE8,
1254 GLOBAL_ATTRIBUTE9,
1255 GLOBAL_ATTRIBUTE10,
1256 GLOBAL_ATTRIBUTE11,
1257 GLOBAL_ATTRIBUTE12,
1258 GLOBAL_ATTRIBUTE13,
1259 GLOBAL_ATTRIBUTE14,
1260 GLOBAL_ATTRIBUTE15,
1261 GLOBAL_ATTRIBUTE16,
1262 GLOBAL_ATTRIBUTE17,
1263 GLOBAL_ATTRIBUTE18,
1264 GLOBAL_ATTRIBUTE19,
1265 GLOBAL_ATTRIBUTE20,
1266 FLEX_CONCATENATED,
1267 OVERRIDE_APPROVER_ID,
1268 PAYMENT_CURRENCY_CODE,
1269 OVERRIDE_APPROVER_NAME,
1270 DEFAULT_RECEIPT_CURRENCY_CODE,
1271 MULTIPLE_CURRENCIES_FLAG,
1272 CREATION_DATE,
1273 CREATED_BY,
1274 LAST_UPDATE_DATE,
1275 LAST_UPDATED_BY,
1276 INVOICE_NUM
1277 )
1278 select
1279 p_target_report_header_id AS REPORT_HEADER_ID,
1280 WEEK_END_DATE,
1281 EMPLOYEE_ID,
1282 0 AS VOUCHNO,
1283 0 AS TOTAL,
1284 EXPENSE_REPORT_ID,
1285 SET_OF_BOOKS_ID,
1286 C_RestorableReportSource AS SOURCE,
1287 DESCRIPTION,
1288 ATTRIBUTE_CATEGORY,
1289 ATTRIBUTE1,
1290 ATTRIBUTE2,
1291 ATTRIBUTE3,
1292 ATTRIBUTE4,
1293 ATTRIBUTE5,
1294 ATTRIBUTE6,
1295 ATTRIBUTE7,
1296 ATTRIBUTE8,
1297 ATTRIBUTE9,
1298 ATTRIBUTE10,
1299 ATTRIBUTE11,
1300 ATTRIBUTE12,
1301 ATTRIBUTE13,
1302 ATTRIBUTE14,
1303 ATTRIBUTE15,
1304 DEFAULT_CURRENCY_CODE,
1305 DEFAULT_EXCHANGE_RATE_TYPE,
1306 DEFAULT_EXCHANGE_RATE,
1307 DEFAULT_EXCHANGE_DATE,
1308 ORG_ID,
1309 GLOBAL_ATTRIBUTE_CATEGORY,
1310 GLOBAL_ATTRIBUTE1,
1311 GLOBAL_ATTRIBUTE2,
1312 GLOBAL_ATTRIBUTE3,
1313 GLOBAL_ATTRIBUTE4,
1314 GLOBAL_ATTRIBUTE5,
1315 GLOBAL_ATTRIBUTE6,
1316 GLOBAL_ATTRIBUTE7,
1317 GLOBAL_ATTRIBUTE8,
1318 GLOBAL_ATTRIBUTE9,
1319 GLOBAL_ATTRIBUTE10,
1320 GLOBAL_ATTRIBUTE11,
1321 GLOBAL_ATTRIBUTE12,
1322 GLOBAL_ATTRIBUTE13,
1323 GLOBAL_ATTRIBUTE14,
1324 GLOBAL_ATTRIBUTE15,
1325 GLOBAL_ATTRIBUTE16,
1326 GLOBAL_ATTRIBUTE17,
1327 GLOBAL_ATTRIBUTE18,
1328 GLOBAL_ATTRIBUTE19,
1329 GLOBAL_ATTRIBUTE20,
1330 FLEX_CONCATENATED,
1331 OVERRIDE_APPROVER_ID,
1332 PAYMENT_CURRENCY_CODE,
1333 OVERRIDE_APPROVER_NAME,
1334 DEFAULT_RECEIPT_CURRENCY_CODE,
1335 MULTIPLE_CURRENCIES_FLAG,
1336 sysdate AS CREATION_DATE,
1337 p_user_id AS CREATED_BY,
1338 sysdate AS LAST_UPDATE_DATE,
1339 p_user_id AS LAST_UPDATED_BY,
1340 l_invoice_num as INVOICE_NUM
1341 from AP_EXPENSE_REPORT_HEADERS
1342 where REPORT_HEADER_ID = p_source_report_header_id;
1343
1344 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPRPT_PKG',
1345 'end DuplicateHeader');
1346
1347 END DuplicateHeader;
1348 --------------------------------------------------------------------------------
1349
1350 FUNCTION UpdateHeaderTotal(
1351 p_report_header_id IN expHdr_headerID
1352 ) RETURN BOOLEAN IS
1353 --------------------------------------------------------------------------------
1354 l_total number;
1355 l_diff number;
1356 BEGIN
1357
1358 select sum(amount)
1359 into l_total
1360 from ap_expense_report_lines
1361 where report_header_id = p_report_header_id;
1362
1363 select(l_total - total)
1364 into l_diff
1365 from ap_expense_report_headers
1366 where report_header_id = p_report_header_id;
1367
1368 update ap_expense_report_headers
1369 set total = l_total,
1370 amt_due_employee = amt_due_employee + l_diff
1371 where report_header_id = p_report_header_id;
1372
1373 return TRUE;
1374
1375 EXCEPTION
1376 WHEN NO_DATA_FOUND THEN
1377 return FALSE;
1378 WHEN OTHERS THEN
1379 AP_WEB_DB_UTIL_PKG.RaiseException('UpdateHeaderTotal');
1380 APP_EXCEPTION.RAISE_EXCEPTION;
1381 return FALSE;
1382 END UpdateHeaderTotal;
1383
1384 --------------------------------------------------------------------------------
1385
1386
1387 FUNCTION GetReimbCurr(
1388 p_expenseReportId IN expHdr_headerID,
1389 p_payment_curr_code OUT NOCOPY expHdr_payemntCurrCode
1390 ) RETURN BOOLEAN IS
1391 -------------------------------------------------------------------
1392 BEGIN
1393 SELECT payment_currency_code
1394 INTO p_payment_curr_code
1395 FROM ap_expense_report_headers
1396 WHERE report_header_id = p_expenseReportId;
1397
1398 return TRUE;
1399 EXCEPTION
1400 WHEN NO_DATA_FOUND THEN
1401 return FALSE;
1402 WHEN OTHERS THEN
1403 AP_WEB_DB_UTIL_PKG.RaiseException('GetReimbCurr');
1404 APP_EXCEPTION.RAISE_EXCEPTION;
1405 return FALSE;
1406
1407 END GetReimbCurr;
1408 -------------------------------------------------------------------
1409 FUNCTION GetHeaderTotal(p_report_header_id IN expHdr_headerID,
1410 p_total OUT NOCOPY NUMBER)
1411 RETURN BOOLEAN IS
1412 -------------------------------------------------------------------
1413 BEGIN
1414 SELECT total
1415 INTO p_total
1416 FROM ap_expense_report_headers
1417 WHERE report_header_id = p_report_header_id;
1418
1419 RETURN true;
1420 EXCEPTION
1421 WHEN NO_DATA_FOUND THEN
1422 return FALSE;
1423 WHEN OTHERS THEN
1424 AP_WEB_DB_UTIL_PKG.RaiseException('GetHeaderTotal');
1425 APP_EXCEPTION.RAISE_EXCEPTION;
1426 return FALSE;
1427 END GetHeaderTotal ;
1428 -------------------------------------------------------------------
1429
1430 /*Written By :Amulya Mishra
1431 Purpose :Returns the PAYMENT_DUE_FROM_CODE from
1432 AP_CREDIT_CARD_TRXNS_ALL table based upon
1433 report_header_id.
1434 */
1435 FUNCTION getPaymentDueFromReport(
1436 p_report_header_id IN expHdr_headerID,
1437 p_paymentDueFromCode OUT NOCOPY VARCHAR2)
1438 RETURN BOOLEAN IS
1439
1440 p_payment_due_from_code VARCHAR2(30);
1441 -----------------------------------------------------------------------------
1442 BEGIN
1443 p_paymentDueFromCode := NULL;
1444 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_CCARD_PKG', 'start getPaymentDueFromReport');
1445
1446 SELECT payment_due_from_code
1447 INTO p_paymentDueFromCode
1448 FROM ap_credit_card_trxns_all trx
1449 WHERE trx.report_header_id = p_report_header_id
1450 AND rownum = 1; --Data Corruption might give two Distinct Pay Methods.
1451
1452 return TRUE;
1453
1454 EXCEPTION
1455 WHEN NO_DATA_FOUND THEN
1456 RETURN FALSE;
1457
1458 WHEN OTHERS THEN
1459 AP_WEB_DB_UTIL_PKG.RaiseException( 'getPaymentDueFromReport');
1460 APP_EXCEPTION.RAISE_EXCEPTION;
1461
1462 END getPaymentDueFromReport;
1463 -----------------------------------------------------------------------------
1464
1465 -----------------------------------------------------------------------------
1466 /*Written By :Ron Langi
1467 Purpose :Returns the Audit Return Reason and Instruction
1468 using the report_header_id.
1469 */
1470 -----------------------------------------------------------------------------
1471 FUNCTION getAuditReturnReasonInstr(
1472 p_report_header_id IN expHdr_headerID,
1473 p_return_reason OUT NOCOPY VARCHAR2,
1474 p_return_instruction OUT NOCOPY VARCHAR2)
1475 RETURN BOOLEAN IS
1476
1477 BEGIN
1478 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_EXPRPT_PKG', 'start getAuditReturnReasonInstr');
1479
1480 SELECT AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUD_RETURN_REASONS',aerh.return_reason_code),
1481 AP_WEB_POLICY_UTILS.get_lookup_description('OIE_AUD_RETURN_REASONS',aerh.return_reason_code)||' '||aerh.return_instruction
1482 INTO p_return_reason,
1483 p_return_instruction
1484 FROM ap_expense_report_headers aerh
1485 WHERE aerh.report_header_id = p_report_header_id;
1486
1487 return true;
1488
1489 EXCEPTION
1490 WHEN NO_DATA_FOUND THEN
1491 RETURN false;
1492 WHEN OTHERS THEN
1493 AP_WEB_DB_UTIL_PKG.RaiseException('getAuditReturnReasonInstr');
1494 APP_EXCEPTION.RAISE_EXCEPTION;
1495 END getAuditReturnReasonInstr;
1496
1497 -----------------------------------------------------------------------------
1498 /*Written By :Ron Langi
1499 Purpose :Clears the Audit Return Reason and Instruction
1500 using the report_header_id.
1501 */
1502 -----------------------------------------------------------------------------
1503 PROCEDURE clearAuditReturnReasonInstr(
1504 p_report_header_id IN expHdr_headerID)
1505 IS
1506
1507 BEGIN
1508 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_EXPRPT_PKG', 'start clearAuditReturnReasonInstr');
1509
1510 UPDATE ap_expense_report_headers aerh
1511 SET aerh.return_reason_code = '',
1512 aerh.return_instruction = ''
1513 WHERE aerh.report_header_id = p_report_header_id;
1514
1515 EXCEPTION
1516 WHEN NO_DATA_FOUND THEN
1517 RETURN ;
1518 WHEN OTHERS THEN
1519 AP_WEB_DB_UTIL_PKG.RaiseException('clearAuditReturnReasonInstr');
1520 APP_EXCEPTION.RAISE_EXCEPTION;
1521 END clearAuditReturnReasonInstr;
1522
1523 --------------------------------------------------------------------------------
1524 FUNCTION GetDefaultEmpCCID(
1525 p_employee_id IN NUMBER,
1526 p_default_emp_ccid OUT NOCOPY expHdr_employeeCCID)
1527
1528 RETURN BOOLEAN IS
1529
1530 --------------------------------------------------------------------------------
1531 l_debugInfo varchar2(240);
1532 BEGIN
1533 l_debugInfo := 'Get default employee CCID';
1534
1535 SELECT default_code_combination_id
1536 INTO p_default_emp_ccid
1537 FROM (
1538 SELECT emp.default_code_combination_id
1539 FROM per_employees_x emp
1540 WHERE emp.employee_id = p_employee_id
1541 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
1542 UNION ALL
1543 SELECT emp.default_code_combination_id
1544 FROM per_cont_workers_current_x emp
1545 WHERE emp.person_id = p_employee_id
1546 );
1547
1548
1549 RETURN TRUE;
1550
1551 EXCEPTION
1552 WHEN NO_DATA_FOUND THEN
1553 return FALSE;
1554 WHEN OTHERS THEN
1555 AP_WEB_DB_UTIL_PKG.RaiseException('GetDefaultEmpCCID',
1556 l_debugInfo);
1557 APP_EXCEPTION.RAISE_EXCEPTION;
1558 return FALSE;
1559 END GetDefaultEmpCCID;
1560
1561 --------------------------------------------------------------------------------
1562 FUNCTION GetChartOfAccountsID(
1563 p_employee_id IN NUMBER,
1564 p_chart_of_accounts_id OUT NOCOPY glsob_chartOfAccountsID)
1565
1566 RETURN BOOLEAN IS
1567
1568 --------------------------------------------------------------------------------
1569 l_debugInfo varchar2(240);
1570 l_emp_set_of_books_id gl_sets_of_books.set_of_books_id%type;
1571 BEGIN
1572 l_debugInfo := 'Get Chart of Accounts ID';
1573
1574 SELECT set_of_books_id
1575 INTO l_emp_set_of_books_id
1576 FROM (
1577 SELECT emp.set_of_books_id
1578 FROM per_employees_x emp
1579 WHERE emp.employee_id = p_employee_id
1580 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
1581 UNION ALL
1582 SELECT emp.set_of_books_id
1583 FROM per_cont_workers_current_x emp
1584 WHERE emp.person_id = p_employee_id
1585 );
1586
1587 IF (l_emp_set_of_books_id IS NOT NULL) THEN
1588 SELECT GS.chart_of_accounts_id
1589 INTO p_chart_of_accounts_id
1590 FROM gl_sets_of_books GS
1591 WHERE GS.set_of_books_id=l_emp_set_of_books_id;
1592 ELSE
1593 RETURN FALSE;
1594 END IF;
1595
1596 RETURN TRUE;
1597
1598 EXCEPTION
1599 WHEN NO_DATA_FOUND THEN
1600 return FALSE;
1601 WHEN OTHERS THEN
1602 AP_WEB_DB_UTIL_PKG.RaiseException('GetChartOfAccountsID',
1603 l_debugInfo);
1604 APP_EXCEPTION.RAISE_EXCEPTION;
1605 return FALSE;
1606 END GetChartOfAccountsID;
1607
1608
1609 --------------------------------------------------------------------------------
1610 FUNCTION GetFlexConcactenated(p_parameter_id IN ap_expense_report_params.parameter_id%TYPE,
1611 p_FlexConcactenated OUT NOCOPY ap_expense_report_params.FLEX_CONCACTENATED%TYPE)
1612 RETURN BOOLEAN IS
1613
1614 --------------------------------------------------------------------------------
1615 l_debugInfo varchar2(240);
1616 BEGIN
1617 l_debugInfo := 'Get FlexConcactenated';
1618
1619 SELECT Flex_Concactenated
1620 INTO p_FlexConcactenated
1621 FROM AP_EXPENSE_REPORT_PARAMS
1622 WHERE parameter_id = p_parameter_id;
1623
1624
1625 RETURN TRUE;
1626
1627 EXCEPTION
1628 WHEN NO_DATA_FOUND THEN
1629 return FALSE;
1630 WHEN OTHERS THEN
1631 AP_WEB_DB_UTIL_PKG.RaiseException('GetFlexConcactenated',
1632 l_debugInfo);
1633 APP_EXCEPTION.RAISE_EXCEPTION;
1634 return FALSE;
1635 END GetFlexConcactenated;
1636
1637 /*Written By :Maulik Vadera
1638 Purpose :Wrapper function over getPaymentDueFromReport(p_report_header_id,p_paymentDueFromCode)
1639 */
1640 --------------------------------------------------------------------------------
1641 FUNCTION getPaymentDueFromReport(p_report_header_id IN expHdr_headerID)
1642 RETURN VARCHAR2 IS
1643 --------------------------------------------------------------------------------
1644 l_payment_due_from_code VARCHAR2(30);
1645 l_temp_bool BOOLEAN;
1646
1647 BEGIN
1648
1649 l_temp_bool := getPaymentDueFromReport(p_report_header_id,l_payment_due_from_code);
1650
1651 RETURN l_payment_due_from_code;
1652
1653 END getPaymentDueFromReport;
1654
1655 ------------------------------------------------------------------------
1656 -- FUNCTION GetERInvoiceNumber
1657 -- Returns the invoice number of an expense report
1658 -- 03/22/2005 - Kristian Widjaja
1659 ------------------------------------------------------------------------
1660 FUNCTION GetERInvoiceNumber(p_report_header_id IN NUMBER)
1661 RETURN VARCHAR2 IS
1662
1663 l_invoice_num AP_EXPENSE_REPORT_HEADERS_ALL.INVOICE_NUM%TYPE;
1664
1665 BEGIN
1666 SELECT invoice_num
1667 INTO l_invoice_num
1668 FROM ap_expense_report_headers_all
1669 WHERE report_header_id = p_report_header_id;
1670
1671 return l_invoice_num;
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 AP_WEB_DB_UTIL_PKG.RaiseException('GetERInvoiceNumber');
1675 APP_EXCEPTION.RAISE_EXCEPTION;
1676 END GetERInvoiceNumber;
1677
1678 ------------------------------------------------------------------------
1679 -- FUNCTION GetERWorkflowApproved
1680 -- Returns the workflow approved flag of an expense report
1681 -- 03/22/2005 - Kristian Widjaja
1682 ------------------------------------------------------------------------
1683 FUNCTION GetERWorkflowApprovedFlag(p_report_header_id IN NUMBER)
1684 RETURN VARCHAR2 IS
1685 l_workflow_approved_flag AP_EXPENSE_REPORT_HEADERS_ALL.WORKFLOW_APPROVED_FLAG%TYPE;
1686
1687 BEGIN
1688 SELECT workflow_approved_flag
1689 INTO l_workflow_approved_flag
1690 FROM ap_expense_report_headers_all
1691 WHERE report_header_id = p_report_header_id;
1692
1693 return l_workflow_approved_flag;
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 AP_WEB_DB_UTIL_PKG.RaiseException('GetERWorkflowApprovedFlag');
1697 APP_EXCEPTION.RAISE_EXCEPTION;
1698 END GetERWorkflowApprovedFlag;
1699
1700 FUNCTION GetERLastUpdateDate(p_report_header_id IN NUMBER)
1701 RETURN VARCHAR2 IS
1702 l_last_update_date VARCHAR2(30);
1703
1704 BEGIN
1705 SELECT to_char(last_update_date, 'DD-MON-RRRR HH:MI:SS', 'NLS_DATE_LANGUAGE = ENGLISH')
1706 INTO l_last_update_date
1707 FROM ap_expense_report_headers_all
1708 WHERE report_header_id = p_report_header_id;
1709
1710 return l_last_update_date;
1711 EXCEPTION
1712 WHEN OTHERS THEN
1713 AP_WEB_DB_UTIL_PKG.RaiseException('GetERLastUpdateDate');
1714 APP_EXCEPTION.RAISE_EXCEPTION;
1715 END GetERLastUpdateDate;
1716
1717 END AP_WEB_DB_EXPRPT_PKG;