[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.61 2012/02/09 14:56:58 dsadipir 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 l_default_exch_rate_type apSetUp_defaultExchRateType;
423 BEGIN
424 -- 3176205: This query includes all workers except for terminated
425 -- contingent workers and terminated employees who are active contingent
426 -- workers.
427 AP_WEB_DB_AP_INT_PKG.GetDefaultExchange(l_default_exch_rate_type);
428 SELECT nvl(S.apply_advances_default, 'N'),
429 nvl(S.allow_awt_flag, 'N'),
430 decode(S.base_currency_code, RH.default_currency_code, null,
431 l_default_exch_rate_type),
432 nvl(S.make_rate_mandatory_flag, 'N'),
433 RH.default_currency_code,
434 week_end_date,
435 flex_concatenated,
436 RH.employee_id
437 INTO p_sys_apply_advances_default,
438 p_sys_allow_awt_flag,
439 p_sys_default_xrate_type,
440 p_sys_make_rate_mandatory,
441 p_default_currency_code,
442 p_week_end_date,
443 p_flex_concatenated,
444 p_employee_id
445 FROM ap_system_parameters S,
446 ap_expense_report_headers RH
447 WHERE RH.report_header_id = p_report_header_id;
448
449 SELECT expense_check_address_flag
450 INTO l_exp_check_address_flag
451 FROM (
452 SELECT emp.expense_check_address_flag
453 FROM per_employees_x emp
454 WHERE emp.employee_id = p_employee_id
455 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
456 UNION ALL
457 SELECT emp.expense_check_address_flag
458 FROM per_cont_workers_current_x emp
459 WHERE emp.person_id = p_employee_id
460 );
461
462 SELECT expense_check_address_flag
463 INTO l_fin_exp_check_address_flag
464 FROM financials_system_parameters;
465
466 p_exp_check_address_flag := nvl(l_exp_check_address_flag, l_fin_exp_check_address_flag);
467
468 return TRUE;
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 return FALSE;
472 WHEN OTHERS THEN
473 AP_WEB_DB_UTIL_PKG.RaiseException('GetAccountingInfo');
474 APP_EXCEPTION.RAISE_EXCEPTION;
475 return FALSE;
476 END GetAccountingInfo;
477
478
479 -------------------------------------------------------------------
480 FUNCTION GetExpReportInfo(
481 p_report_header_id IN expHdr_headerID,
482 p_description OUT NOCOPY VARCHAR2,
483 p_ccard_amt OUT NOCOPY expHdr_amtDueCCardCompany,
484 p_total OUT NOCOPY expHdr_total
485 ) RETURN BOOLEAN IS
486 -------------------------------------------------------------------
487 l_exp_rpt_purpose VARCHAR2(240);
488 l_emp_masked_cc_number VARCHAR2(320);
489 l_emp_full_name VARCHAR2(240);
490 l_description VARCHAR2(1000); -- bug3303390
491 BEGIN
492
493 SELECT DISTINCT icc.masked_cc_number || '/' || emp.full_name,
494 erh.description,
495 emp.full_name,
496 erh.amt_due_ccard_company,
497 erh.total
498 INTO l_emp_masked_cc_number,
499 l_exp_rpt_purpose,
500 l_emp_full_name,
501 p_ccard_amt,
502 p_total
503 FROM ap_expense_report_headers erh,
504 ap_credit_card_trxns cc,
505 /* hr_employees emp Bug 3006221 */
506 per_people_f emp,
507 ap_cards aca,
508 iby_creditcard icc
509 WHERE cc.card_id = aca.card_id
510 AND aca.card_reference_id = icc.instrid
511 AND erh.report_header_id = p_report_header_id
512 AND cc.report_header_id = p_report_header_id
513 AND erh.employee_id = emp.person_id
514 AND TRUNC(sysdate) BETWEEN emp.effective_start_date
515 AND emp.effective_end_date /* Bug 3111161 */
516 AND cc.category='BUSINESS';
517
518 -- Bug 2786500:By default message will have 'EMP_CARD_NUM - EXP_RPT_PURPOSE'
519 -- Message can be modified to have one or combination of the following
520 -- EMP_CARD_NUM, EXP_RPT_PURPOSE, EMP_FULL_NAME
521 FND_MESSAGE.SET_NAME('SQLAP','OIE_INVOICE_DESC');
522 l_description := FND_MESSAGE.GET;
523 l_description := replace(l_description,'EMP_FULL_NAME',l_emp_full_name);
524 l_description := replace(l_description,'EMP_CARD_NUM',l_emp_masked_cc_number);
525 l_description := replace(l_description,'EXP_RPT_PURPOSE',l_exp_rpt_purpose);
526 p_description := substrb(l_description,1,240); --2227571
527 p_description := rtrim(p_description); --2227571
528 IF substr(p_description, -1) = '-' THEN
529 p_description := substr(p_description,1, length(p_description) -2);
530 END IF;
531
532 return TRUE;
533
534 EXCEPTION
535 WHEN NO_DATA_FOUND THEN
536 return FALSE;
537 WHEN OTHERS THEN
538 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpReportInfo');
539 APP_EXCEPTION.RAISE_EXCEPTION;
540 return FALSE;
541 END GetExpReportInfo;
542
543
544 -------------------------------------------------------------------
545 FUNCTION ExpReportShortpaid(P_ReportID IN expHdr_headerID,
546 P_Shortpaid OUT NOCOPY BOOLEAN)
547 RETURN BOOLEAN IS
548 -------------------------------------------------------------------
549 l_shortpay_id expHdr_shortpayParentID := NULL;
550 BEGIN
551 SELECT shortpay_parent_id
552 INTO l_shortpay_id
553 FROM ap_expense_report_headers
554 WHERE report_header_id = P_ReportID;
555
556 IF (l_shortpay_id IS NULL) THEN
557 P_Shortpaid := FALSE;
558 ELSE
559 P_Shortpaid := TRUE;
560 END IF;
561
562 return TRUE;
563
564 EXCEPTION
565 WHEN NO_DATA_FOUND THEN
566 return FALSE;
567 WHEN OTHERS THEN
568 AP_WEB_DB_UTIL_PKG.RaiseException('ExpReportShortpaid');
569 APP_EXCEPTION.RAISE_EXCEPTION;
570 return FALSE;
571 END ExpReportShortpaid;
572
573 -------------------------------------------------------------------
574 FUNCTION InsertReportHeader(p_xpense_rec IN XpenseInfoRec,
575 p_ExpReportHeaderInfo IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec
576 ) RETURN BOOLEAN IS
577 -------------------------------------------------------------------
578 l_curr_calling_sequence VARCHAR2(100) := 'AddReportHeader';
579 l_debug_info VARCHAR2(100);
580 BEGIN
581
582 l_debug_info := 'Add Report Header';
583
584 INSERT INTO AP_EXPENSE_REPORT_HEADERS
585 (report_header_id,
586 employee_id,
587 override_approver_id,
588 override_approver_name,
589 week_end_date,
590 vouchno,
591 total,
592 invoice_num,
593 expense_report_id,
594 set_of_books_id,
595 source,
596 description,
597 flex_concatenated,
598 default_currency_code,
599 payment_currency_code, --1396360
600 creation_date,
601 created_by,
602 last_update_date,
603 last_updated_by,
604 workflow_approved_flag,
605 amt_due_employee,
606 amt_due_ccard_company,
607 org_id)
608 VALUES
609 (p_xpense_rec.report_header_id,
610 p_ExpReportHeaderInfo.employee_id,
611 p_xpense_rec.approver_id,
612 p_ExpReportHeaderInfo.override_approver_name,
613 p_xpense_rec.week_end_date,
614 p_xpense_rec.vouchno,
615 p_xpense_rec.total,
616 p_xpense_rec.document_number,
617 to_number(p_ExpReportHeaderInfo.template_id),
618 p_xpense_rec.set_of_books_id,
619 p_xpense_rec.source,
620 AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(p_ExpReportHeaderInfo.purpose),
621 p_ExpReportHeaderInfo.cost_center,
622 p_ExpReportHeaderInfo.reimbursement_currency_code,
623 p_ExpReportHeaderInfo.reimbursement_currency_code,
624 sysdate,
625 icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
626 sysdate,
627 icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
628 p_xpense_rec.workflow_flag,
629 p_ExpReportHeaderInfo.amt_due_employee,
630 p_ExpReportHeaderInfo.amt_due_ccCompany,
631 nvl( p_xpense_rec.org_id, mo_global.get_current_org_id() ) );
632
633 return TRUE;
634 EXCEPTION -- Block which encapsulates the delete and insert code
635 WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
636 AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeader',l_debug_info,
637 'AP_WEB_SAVESUB_DELETE_FAILED',
638 'V_ReportHeaderID = ' || p_xpense_rec.report_header_id
639 ||', Invoice Num = '|| p_xpense_rec.document_number);
640 APP_EXCEPTION.RAISE_EXCEPTION;
641 return FALSE;
642 WHEN OTHERS THEN
643 IF (SQLCODE = -00054) THEN
644 -- Tried to obtain lock when deleting on an already locked row
645 -- Get invoice prefix profile option, and trim if it is too long
646 -- Get message stating delete failed
647 AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeader',l_debug_info,
648 'AP_WEB_SAVESUB_LOCK_FAILED',
649 'V_ReportHeaderID = ' || p_xpense_rec.report_header_id
650 );
651 APP_EXCEPTION.RAISE_EXCEPTION;
652 END IF;
653 return FALSE;
654 END InsertReportHeader; -- Block which encapsulates the delete and insert code
655
656 -------------------------------------------------------------------
657 FUNCTION InsertReportHeaderLikeExisting(p_orig_report_header_id IN expHdr_headerID,
658 p_xpense_rec IN XpenseInfoRec
659 ) RETURN BOOLEAN IS
660 -------------------------------------------------------------------
661 BEGIN
662 INSERT INTO ap_expense_report_headers
663 (report_header_id,
664 employee_id,
665 week_end_date,
666 creation_date,
667 created_by,
668 last_update_date,
669 last_updated_by,
670 vouchno,
671 total,
672 vendor_id,
673 vendor_site_id,
674 expense_check_address_flag,
675 reference_1,
676 reference_2,
677 invoice_num,
678 expense_report_id,
679 accts_pay_code_combination_id,
680 set_of_books_id,
681 source,
682 expense_status_code,
683 purgeable_flag,
684 accounting_date,
685 maximum_amount_to_apply,
686 advance_invoice_to_apply,
687 apply_advances_default,
688 employee_ccid,
689 reject_code,
690 hold_lookup_code,
691 attribute_category,
692 attribute1,
693 attribute2,
694 attribute3,
695 attribute4,
696 attribute5,
697 attribute6,
698 attribute7,
699 attribute8,
700 attribute9,
701 attribute10,
702 attribute11,
703 attribute12,
704 attribute13,
705 attribute14,
706 attribute15,
707 default_currency_code,
708 default_exchange_rate_type,
709 default_exchange_rate,
710 default_exchange_date,
711 last_update_login,
712 voucher_num,
713 doc_category_code,
714 awt_group_id,
715 org_id,
716 workflow_approved_flag,
717 flex_concatenated,
718 global_attribute_category,
719 global_attribute1,
720 global_attribute2,
721 global_attribute3,
722 global_attribute4,
723 global_attribute5,
724 global_attribute6,
725 global_attribute7,
726 global_attribute8,
727 global_attribute9,
728 global_attribute10,
729 global_attribute11,
730 global_attribute12,
731 global_attribute13,
732 global_attribute14,
733 global_attribute15,
734 global_attribute16,
735 global_attribute17,
736 global_attribute18,
737 global_attribute19,
738 global_attribute20,
739 override_approver_id,
740 payment_cross_rate_type,
741 payment_cross_rate_date,
742 payment_cross_rate,
743 payment_currency_code,
744 core_wf_status_flag,
745 amt_due_employee,
746 amt_due_ccard_company,
747 description,
748 bothpay_parent_id,
749 shortpay_parent_id,
750 paid_on_behalf_employee_id,
751 report_submitted_date, -- 2646985
752 receipts_received_date, -- jrautiai 3008468
753 last_audited_by, -- jrautiai 2987037
754 audit_code, -- jrautiai 3255738
755 report_filing_number,
756 receipts_status,
757 image_receipts_status,
758 image_receipts_received_date,
759 approval_type
760 )
761 SELECT p_xpense_rec.report_header_id,
762 erh.employee_id, --2446559
763 week_end_date,
764 sysdate,
765 -- Bug 2473070 NVL(icx_sec.getID(icx_sec.PV_USER_ID), erh.created_by),
766 NVL(p_xpense_rec.preparer_id, erh.created_by),
767 sysdate,
768 -- Bug 2473070 NVL(icx_sec.getID(icx_sec.PV_USER_ID), erh.last_updated_by),
769 NVL(p_xpense_rec.last_updated_by, erh.last_updated_by),
770 p_xpense_rec.vouchno,
771 decode(p_xpense_rec.total,-1,erh.amt_due_ccard_company, p_xpense_rec.total), --result of combining the apis
772 NVL(p_xpense_rec.vendor_id, erh.vendor_id),
773 NVL(p_xpense_rec.vendor_site_id, erh.vendor_site_id),
774 NVL(p_xpense_rec.expense_check_address_flag, erh.expense_check_address_flag),
775 reference_1,
776 reference_2,
777 p_xpense_rec.document_number, --invoice_num
778 expense_report_id,
779 NVL(p_xpense_rec.accts_pay_comb_id,erh.accts_pay_code_combination_id),
780 set_of_books_id,
781 NVL(p_xpense_rec.source,erh.source),
782 p_xpense_rec.expense_status_code,
783 purgeable_flag,
784 accounting_date,
785 '', -- Bug 3654956
786 '', -- 4001778 advance_invoice_to_apply,
787 '', -- 4001778 apply_advances_default,
788 employee_ccid,
789 reject_code,
790 hold_lookup_code,
791 attribute_category,
792 attribute1,
793 attribute2,
794 attribute3,
795 attribute4,
796 attribute5,
797 attribute6,
798 attribute7,
799 attribute8,
800 attribute9,
801 attribute10,
802 attribute11,
803 attribute12,
804 attribute13,
805 attribute14,
806 attribute15,
807 default_currency_code,
808 default_exchange_rate_type,
809 default_exchange_rate,
810 default_exchange_date,
811 NVL(p_xpense_rec.last_update_login, erh.last_update_login),
812 voucher_num,
813 doc_category_code,
814 awt_group_id,
815 NVL(p_xpense_rec.org_id, erh.org_id),
816 decode(p_xpense_rec.workflow_flag, NULL,erh.workflow_approved_flag, decode(p_xpense_rec.workflow_flag,'POLICY','M',null)),
817 flex_concatenated,
818 global_attribute_category,
819 global_attribute1,
820 global_attribute2,
821 global_attribute3,
822 global_attribute4,
823 global_attribute5,
824 global_attribute6,
825 global_attribute7,
826 global_attribute8,
827 global_attribute9,
828 global_attribute10,
829 global_attribute11,
830 global_attribute12,
831 global_attribute13,
832 global_attribute14,
833 global_attribute15,
834 global_attribute16,
835 global_attribute17,
836 global_attribute18,
837 global_attribute19,
838 global_attribute20,
839 override_approver_id,
840 payment_cross_rate_type,
841 payment_cross_rate_date,
842 payment_cross_rate,
843 payment_currency_code,
844 core_wf_status_flag,
845 nvl(p_xpense_rec.amt_due_employee,0), --amt_due_employee
846 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
847 NVL(p_xpense_rec.description,erh.description), --description
848 p_xpense_rec.bothpay_report_header_id, --bothpay_parent_id
849 NVL(p_xpense_rec.shortpay_parent_id, erh.shortpay_parent_id),
850 decode(p_xpense_rec.behalf_employee_id, -1, erh.employee_id, erh.paid_on_behalf_employee_id), --paid_on_behalf_employee_id
851 report_submitted_date, -- 2646985
852 p_xpense_rec.receipts_received_date, -- jrautiai 3008468
853 last_audited_by, -- jrautiai 2987037
854 audit_code, -- jrautiai 3255738
855 report_filing_number,
856 p_xpense_rec.receipts_status,
857 p_xpense_rec.image_receipts_status,
858 p_xpense_rec.image_receipts_received_date,
859 approval_type -- Bug 13696856
860 FROM ap_expense_report_headers erh
861 WHERE report_header_id = p_orig_report_header_id;
862
863 return TRUE;
864 EXCEPTION
865 WHEN NO_DATA_FOUND THEN
866 return FALSE;
867 WHEN OTHERS THEN
868 AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeaderLikeExisting');
869 APP_EXCEPTION.RAISE_EXCEPTION;
870 return FALSE;
871 END InsertReportHeaderLikeExisting;
872
873 FUNCTION SetDefaultExchRateType(p_report_header_id IN expHdr_headerID,
874 p_xrate_type IN expHdr_defaultXchRateType)
875 RETURN BOOLEAN IS
876 BEGIN
877 UPDATE ap_expense_report_headers
878 SET default_exchange_rate_type = p_xrate_type
879 WHERE report_header_id = p_report_header_id;
880
881 return TRUE;
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 return FALSE;
885 WHEN OTHERS THEN
886 AP_WEB_DB_UTIL_PKG.RaiseException('SetDefaultExchRateType');
887 APP_EXCEPTION.RAISE_EXCEPTION;
888 return FALSE;
889 END SetDefaultExchRateType;
890
891 ------------------------------------------------------------------
892 FUNCTION SetExpenseHeaderInfo(
893 p_report_header_id IN expHdr_headerID,
894 p_exp_check_address_flag IN expHdr_expCheckAddrFlag,
895 p_source IN expHdr_source,
896 p_workflow_approve_flag IN expHdr_wkflApprvdFlag,
897 p_sys_apply_advances_default IN expHdr_applyAdvDefault,
898 p_available_prepays IN NUMBER,
899 p_sys_allow_awt_flag IN AP_SYSTEM_PARAMETERS.allow_awt_flag%TYPE,
900 p_ven_allow_awt_flag IN AP_SUPPLIERS.allow_awt_flag%TYPE,
901 p_ven_awt_group_id IN expHdr_awtGroupID,
902 p_sys_default_xrate_type IN expHdr_defaultXchRateType,
903 p_week_end_date IN expHdr_defaultExchDate,
904 p_default_exchange_rate IN expHdr_defaultExchRate,
905 p_employee_ccid IN expHdr_employeeCCID
906 ) RETURN BOOLEAN IS
907 -------------------------------------------------------------------
908 BEGIN
909 UPDATE ap_expense_report_headers RH
910 SET expense_check_address_flag = p_exp_check_address_flag,
911 source = p_source,
912 workflow_approved_flag = p_workflow_approve_flag,
913 apply_advances_default = decode(apply_advances_default,'Y','Y',decode(p_sys_apply_advances_default, 'Y',
914 decode(sign(p_available_prepays), 1, 'Y', 'N'), 'N')),
915 awt_group_id = decode(p_sys_allow_awt_flag, 'Y',
916 decode(p_ven_allow_awt_flag, 'Y', p_ven_awt_group_id,
917 null), null),
918 default_exchange_rate_type = p_sys_default_xrate_type,
919 default_exchange_date = decode(p_sys_default_xrate_type, null, null,
920 p_week_end_date),
921 default_exchange_rate = p_default_exchange_rate,
922 employee_ccid = p_employee_ccid
923 WHERE report_header_id = p_report_header_id;
924
925 return TRUE;
926
927 EXCEPTION
928 WHEN NO_DATA_FOUND THEN
929 return FALSE;
930 WHEN OTHERS THEN
931 AP_WEB_DB_UTIL_PKG.RaiseException('SetExpenseHeaderInfo');
932 APP_EXCEPTION.RAISE_EXCEPTION;
933 return FALSE;
934 END SetExpenseHeaderInfo;
935
936
937 -------------------------------------------------------------------
938 FUNCTION SetAmtDuesAndTotal(
939 p_report_header_id IN expHdr_headerID,
940 p_amt_due_ccard_company IN expHdr_amtDueCCardCompany,
941 p_amt_due_employee IN expHdr_amtDueEmployee,
942 p_total IN expHdr_total
943 ) RETURN BOOLEAN IS
944 -------------------------------------------------------------------
945 l_max_amt_to_apply expHdr_maxAmountApplied := NULL;--Bug#6400678
946 l_amt_due_employee expHdr_amtDueEmployee := 0;--Bug#6400678
947 BEGIN
948 l_amt_due_employee := p_amt_due_employee;
949
950 SELECT decode (maximum_amount_to_apply, NULL, NULL,least( p_total, maximum_amount_to_apply))-- Bug 3654956
951 INTO l_max_amt_to_apply
952 from ap_expense_report_headers
953 where report_header_id = p_report_header_id;
954
955 --Bug#6400678 : Calculate amount due to employee taking advance applied into consideration
956
957 IF(l_max_amt_to_apply IS NOT NULL AND l_max_amt_to_apply <> 0) THEN
958 l_amt_due_employee := l_amt_due_employee - l_max_amt_to_apply;
959 END IF;
960
961 UPDATE ap_expense_report_headers
962 SET amt_due_ccard_company = p_amt_due_ccard_company,
963 amt_due_employee = l_amt_due_employee,
964 total = p_total,
965 maximum_amount_to_apply = l_max_amt_to_apply
966 WHERE report_header_id = p_report_header_id;
967
968 return TRUE;
969
970 EXCEPTION
971 WHEN NO_DATA_FOUND THEN
972 return FALSE;
973 WHEN OTHERS THEN
974 AP_WEB_DB_UTIL_PKG.RaiseException('SetAmtDuesAndTotal');
975 APP_EXCEPTION.RAISE_EXCEPTION;
976 return FALSE;
977 END SetAmtDuesAndTotal;
978
979
980 -------------------------------------------------------------------
981 FUNCTION SetBothpayReportHeader(
982 p_report_header_id IN expHdr_headerID,
983 p_sub_total IN NUMBER,
984 p_vendor_id IN expHdr_vendorID,
985 p_vendor_site_id IN expHdr_vendorSiteID,
986 p_bothpay_id IN expHdr_bothpayParentID,
987 p_paid_on_behalf_id IN expHdr_paidOnBehalfEmpID,
988 p_total IN expHdr_total,
989 p_amt_due_ccard_company IN expHdr_amtDueCCardCompany,
990 p_employee_id IN expHdr_employeeID,
991 p_description IN expHdr_description,
992 p_source IN expHdr_source,
993 p_accts_comb_id IN expHdr_acctsPayCodeCombID
994 ) RETURN BOOLEAN IS
995 -------------------------------------------------------------------
996 BEGIN
997 /* Bug 3654956 : Seeting the value of maximum_amount_to_apply*/
998 UPDATE ap_expense_report_headers
999 SET total = decode(p_total, NULL, total, total - p_sub_total),
1000 vendor_id = p_vendor_id,
1001 vendor_site_id = p_vendor_site_id,
1002 amt_due_ccard_company = NVL(p_amt_due_ccard_company, amt_due_ccard_company),
1003 bothpay_parent_id = p_bothpay_id,
1004 paid_on_behalf_employee_id = DECODE(p_paid_on_behalf_id, -1, employee_id, p_paid_on_behalf_id),
1005 employee_id = NVL(p_employee_id, employee_id),
1006 description = NVL(p_description, description),
1007 source = NVL(p_source, source),
1008 accts_pay_code_combination_id = NVL(p_accts_comb_id, accts_pay_code_combination_id),
1009 maximum_amount_to_apply = decode (maximum_amount_to_apply, NULL, NULL,
1010 decode(p_total, NULL,NULL,
1011 least( total - p_sub_total, maximum_amount_to_apply)))
1012 WHERE report_header_id = p_report_header_id;
1013
1014 return TRUE;
1015
1016 EXCEPTION
1017 WHEN NO_DATA_FOUND THEN
1018 return FALSE;
1019 WHEN OTHERS THEN
1020 AP_WEB_DB_UTIL_PKG.RaiseException('SetBothpayReportHeader');
1021 APP_EXCEPTION.RAISE_EXCEPTION;
1022 return FALSE;
1023 END SetBothpayReportHeader;
1024
1025 -------------------------------------------------------------------
1026 FUNCTION SetWkflApprvdFlagAndSource(
1027 p_report_header_id IN expHdr_headerID,
1028 p_flag IN expHdr_wkflApprvdFlag,
1029 p_source IN expHdr_source
1030 ) RETURN BOOLEAN IS
1031 -------------------------------------------------------------------
1032 BEGIN
1033
1034 UPDATE ap_expense_report_headers erh
1035 SET workflow_approved_flag = NVL(p_flag, decode(erh.workflow_approved_flag, 'M', 'A', erh.workflow_approved_flag)),
1036 source = NVL(p_source, erh.source),
1037 last_update_date = SYSDATE
1038 --last_updated_by = Decode(Nvl(fnd_global.user_id,-1),-1,last_updated_by,fnd_global.user_id)
1039 WHERE report_header_id = p_report_header_id;
1040
1041 return TRUE;
1042
1043 EXCEPTION
1044 WHEN NO_DATA_FOUND THEN
1045 return FALSE;
1046 WHEN OTHERS THEN
1047 AP_WEB_DB_UTIL_PKG.RaiseException('SetWkflApprvdFlagAndSource');
1048 APP_EXCEPTION.RAISE_EXCEPTION;
1049 return FALSE;
1050 END SetWkflApprvdFlagAndSource;
1051
1052
1053 /* This is a combination of two FUNCTIONs. The other one is
1054 commented out nocopy below */
1055 -------------------------------------------------------------------
1056 FUNCTION SetWkflApprvdFlag(p_report_header_id IN expHdr_headerID)
1057 RETURN BOOLEAN IS
1058 -------------------------------------------------------------------
1059 BEGIN
1060
1061 UPDATE ap_expense_report_headers
1062 SET workflow_approved_flag = decode(workflow_approved_flag,
1063 'P','Y',
1064 C_WORKFLOW_APPROVED_REQUEST, C_WORKFLOW_APPROVED_REQUEST, -- AP already rejected
1065 C_WORKFLOW_APPROVED_REJECTED, C_WORKFLOW_APPROVED_REJECTED, -- AP already requests more info
1066 'M')
1067 WHERE report_header_id = p_report_header_id;
1068
1069 return TRUE;
1070
1071 EXCEPTION
1072 WHEN NO_DATA_FOUND THEN
1073 return FALSE;
1074 WHEN OTHERS THEN
1075 AP_WEB_DB_UTIL_PKG.RaiseException('SetWkflApprvdFlag');
1076 APP_EXCEPTION.RAISE_EXCEPTION;
1077 return FALSE;
1078 END SetWkflApprvdFlag;
1079
1080 -------------------------------------------------------------------
1081 FUNCTION SetWkflApprvdFlag2(p_report_header_id IN expHdr_headerID)
1082 RETURN BOOLEAN IS
1083 -------------------------------------------------------------------
1084 BEGIN
1085
1086 UPDATE ap_expense_report_headers
1087 SET workflow_approved_flag = decode(workflow_approved_flag,
1088 'P', 'Y',
1089 'Y', 'Y',
1090 'M')
1091 WHERE report_header_id = p_report_header_id;
1092
1093 return TRUE;
1094
1095 EXCEPTION
1096 WHEN NO_DATA_FOUND THEN
1097 return FALSE;
1098 WHEN OTHERS THEN
1099 AP_WEB_DB_UTIL_PKG.RaiseException('SetWkflApprvdFlag2');
1100 APP_EXCEPTION.RAISE_EXCEPTION;
1101 return FALSE;
1102 END SetWkflApprvdFlag2;
1103
1104 -------------------------------------------------------------------
1105 FUNCTION DeleteReportHeaderAtDate(
1106 P_ReportID IN expHdr_headerID,
1107 P_LastUpdateDate IN expHdr_lastUpdateDate)
1108 RETURN BOOLEAN IS
1109 -------------------------------------------------------------------
1110 l_TempReportHeaderID NUMBER;
1111
1112 BEGIN
1113
1114 -- Selects report headers to delete. The actual value being selected does
1115 -- not matter. For some reason the compiler complains when the OF
1116 -- column-name in the FOR UPDATE is missing and NOWAIT is used, so the OF
1117 -- REPORT_HEADER_ID is used as a place holder.
1118 SELECT REPORT_HEADER_ID
1119 INTO l_TempReportHeaderID
1120 FROM AP_EXPENSE_REPORT_HEADERS
1121 WHERE REPORT_HEADER_ID = P_ReportID
1122 AND SOURCE = C_RestorableReportSource
1123 AND LAST_UPDATE_DATE = NVL(P_LastUpdateDate, LAST_UPDATE_DATE)
1124 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
1125
1126 -- Delete matching line
1127 if (DeleteExpenseReport(P_ReportID)) then null; end if;
1128
1129 return TRUE;
1130
1131 EXCEPTION
1132 WHEN NO_DATA_FOUND THEN
1133 return FALSE;
1134 WHEN OTHERS THEN
1135 AP_WEB_DB_UTIL_PKG.RaiseException('DeleteReportHeaderAtDate');
1136 APP_EXCEPTION.RAISE_EXCEPTION;
1137 return FALSE;
1138 END DeleteReportHeaderAtDate;
1139
1140
1141 -------------------------------------------------------------------
1142 FUNCTION DeleteExpenseReport(p_report_header_id IN expHdr_headerID)
1143 RETURN BOOLEAN IS
1144 -------------------------------------------------------------------
1145 l_curr_calling_sequence VARCHAR2(100) := 'DeleteExpenseReport';
1146
1147 BEGIN
1148 DELETE FROM ap_expense_report_headers
1149 WHERE report_header_id = p_report_header_id;
1150
1151 /* Delete All Notes associated with Expense Report */
1152 AP_WEB_NOTES_PKG.DeleteERNotes (
1153 p_src_report_header_id => p_report_header_id
1154 );
1155
1156 /* Delete attachments assocated with the header */
1157 fnd_attached_documents2_pkg.delete_attachments(
1158 X_entity_name => 'OIE_HEADER_ATTACHMENTS',
1159 X_pk1_value => p_report_header_id,
1160 X_delete_document_flag => 'Y'
1161 );
1162
1163 return TRUE;
1164
1165 EXCEPTION
1166 WHEN NO_DATA_FOUND THEN
1167 return FALSE;
1168 WHEN OTHERS THEN
1169 AP_WEB_DB_UTIL_PKG.RaiseException('DeleteExpenseReport');
1170 APP_EXCEPTION.RAISE_EXCEPTION;
1171 return FALSE;
1172 END DeleteExpenseReport;
1173
1174 -------------------------------------------------------------------
1175 FUNCTION ResubmitExpenseReport(p_workflow_approved_flag IN VARCHAR2)
1176 RETURN BOOLEAN IS
1177 -------------------------------------------------------------------
1178 BEGIN
1179 /* Bug 2636718 - Please note that this piece of the code does not
1180 have C_WORKFLOW_WITHDRAWN because this API is used to determine
1181 if its going to create a new workflow process or re start from
1182 a block activity. This API will return if workflow is restarted
1183 from a blocked activity. Withdraw needs to create a new workflow
1184 process
1185 */
1186 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
1187 return TRUE;
1188 else
1189 return FALSE;
1190 end if;
1191
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 AP_WEB_DB_UTIL_PKG.RaiseException('ResubmitExpenseReport');
1195 APP_EXCEPTION.RAISE_EXCEPTION;
1196 return FALSE;
1197 END ResubmitExpenseReport;
1198
1199
1200
1201 -------------------------------------------------------------------
1202 -- Name: DuplicateHeader
1203 -- Desc: duplicates an Expense Report Header
1204 -- Input: p_source_report_header_id - source expense report header id
1205 -- Returns: p_target_report_header_id - target expense report header id
1206 -------------------------------------------------------------------
1207 PROCEDURE DuplicateHeader(
1208 p_user_id IN NUMBER,
1209 p_source_report_header_id IN expHdr_headerID,
1210 p_target_report_header_id IN OUT NOCOPY expHdr_headerID) IS
1211
1212 l_invoice_num varchar2(50);
1213 l_employee_id number;
1214
1215 BEGIN
1216
1217 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPRPT_PKG',
1218 'start DuplicateHeader');
1219
1220 select EMPLOYEE_ID into l_employee_id
1221 from AP_EXPENSE_REPORT_HEADERS
1222 where REPORT_HEADER_ID = p_source_report_header_id;
1223 l_invoice_num := AP_WEB_OA_CUSTOM_PKG.GetNewExpenseReportInvoice(l_employee_id, p_user_id, p_target_report_header_id);
1224
1225 insert into AP_EXPENSE_REPORT_HEADERS
1226 (
1227 REPORT_HEADER_ID,
1228 WEEK_END_DATE,
1229 EMPLOYEE_ID,
1230 VOUCHNO,
1231 TOTAL,
1232 EXPENSE_REPORT_ID,
1233 SET_OF_BOOKS_ID,
1234 SOURCE,
1235 DESCRIPTION,
1236 ATTRIBUTE_CATEGORY,
1237 ATTRIBUTE1,
1238 ATTRIBUTE2,
1239 ATTRIBUTE3,
1240 ATTRIBUTE4,
1241 ATTRIBUTE5,
1242 ATTRIBUTE6,
1243 ATTRIBUTE7,
1244 ATTRIBUTE8,
1245 ATTRIBUTE9,
1246 ATTRIBUTE10,
1247 ATTRIBUTE11,
1248 ATTRIBUTE12,
1249 ATTRIBUTE13,
1250 ATTRIBUTE14,
1251 ATTRIBUTE15,
1252 DEFAULT_CURRENCY_CODE,
1253 DEFAULT_EXCHANGE_RATE_TYPE,
1254 DEFAULT_EXCHANGE_RATE,
1255 DEFAULT_EXCHANGE_DATE,
1256 ORG_ID,
1257 GLOBAL_ATTRIBUTE_CATEGORY,
1258 GLOBAL_ATTRIBUTE1,
1259 GLOBAL_ATTRIBUTE2,
1260 GLOBAL_ATTRIBUTE3,
1261 GLOBAL_ATTRIBUTE4,
1262 GLOBAL_ATTRIBUTE5,
1263 GLOBAL_ATTRIBUTE6,
1264 GLOBAL_ATTRIBUTE7,
1265 GLOBAL_ATTRIBUTE8,
1266 GLOBAL_ATTRIBUTE9,
1267 GLOBAL_ATTRIBUTE10,
1268 GLOBAL_ATTRIBUTE11,
1269 GLOBAL_ATTRIBUTE12,
1270 GLOBAL_ATTRIBUTE13,
1271 GLOBAL_ATTRIBUTE14,
1272 GLOBAL_ATTRIBUTE15,
1273 GLOBAL_ATTRIBUTE16,
1274 GLOBAL_ATTRIBUTE17,
1275 GLOBAL_ATTRIBUTE18,
1276 GLOBAL_ATTRIBUTE19,
1277 GLOBAL_ATTRIBUTE20,
1278 FLEX_CONCATENATED,
1279 OVERRIDE_APPROVER_ID,
1280 PAYMENT_CURRENCY_CODE,
1281 OVERRIDE_APPROVER_NAME,
1282 DEFAULT_RECEIPT_CURRENCY_CODE,
1283 MULTIPLE_CURRENCIES_FLAG,
1284 CREATION_DATE,
1285 CREATED_BY,
1286 LAST_UPDATE_DATE,
1287 LAST_UPDATED_BY,
1288 INVOICE_NUM
1289 )
1290 select
1291 p_target_report_header_id AS REPORT_HEADER_ID,
1292 WEEK_END_DATE,
1293 EMPLOYEE_ID,
1294 0 AS VOUCHNO,
1295 0 AS TOTAL,
1296 EXPENSE_REPORT_ID,
1297 SET_OF_BOOKS_ID,
1298 C_RestorableReportSource AS SOURCE,
1299 DESCRIPTION,
1300 ATTRIBUTE_CATEGORY,
1301 ATTRIBUTE1,
1302 ATTRIBUTE2,
1303 ATTRIBUTE3,
1304 ATTRIBUTE4,
1305 ATTRIBUTE5,
1306 ATTRIBUTE6,
1307 ATTRIBUTE7,
1308 ATTRIBUTE8,
1309 ATTRIBUTE9,
1310 ATTRIBUTE10,
1311 ATTRIBUTE11,
1312 ATTRIBUTE12,
1313 ATTRIBUTE13,
1314 ATTRIBUTE14,
1315 ATTRIBUTE15,
1316 DEFAULT_CURRENCY_CODE,
1317 DEFAULT_EXCHANGE_RATE_TYPE,
1318 DEFAULT_EXCHANGE_RATE,
1319 DEFAULT_EXCHANGE_DATE,
1320 ORG_ID,
1321 GLOBAL_ATTRIBUTE_CATEGORY,
1322 GLOBAL_ATTRIBUTE1,
1323 GLOBAL_ATTRIBUTE2,
1324 GLOBAL_ATTRIBUTE3,
1325 GLOBAL_ATTRIBUTE4,
1326 GLOBAL_ATTRIBUTE5,
1327 GLOBAL_ATTRIBUTE6,
1328 GLOBAL_ATTRIBUTE7,
1329 GLOBAL_ATTRIBUTE8,
1330 GLOBAL_ATTRIBUTE9,
1331 GLOBAL_ATTRIBUTE10,
1332 GLOBAL_ATTRIBUTE11,
1333 GLOBAL_ATTRIBUTE12,
1334 GLOBAL_ATTRIBUTE13,
1335 GLOBAL_ATTRIBUTE14,
1336 GLOBAL_ATTRIBUTE15,
1337 GLOBAL_ATTRIBUTE16,
1338 GLOBAL_ATTRIBUTE17,
1339 GLOBAL_ATTRIBUTE18,
1340 GLOBAL_ATTRIBUTE19,
1341 GLOBAL_ATTRIBUTE20,
1342 FLEX_CONCATENATED,
1343 OVERRIDE_APPROVER_ID,
1344 PAYMENT_CURRENCY_CODE,
1345 OVERRIDE_APPROVER_NAME,
1346 DEFAULT_RECEIPT_CURRENCY_CODE,
1347 MULTIPLE_CURRENCIES_FLAG,
1348 sysdate AS CREATION_DATE,
1349 p_user_id AS CREATED_BY,
1350 sysdate AS LAST_UPDATE_DATE,
1351 p_user_id AS LAST_UPDATED_BY,
1352 l_invoice_num as INVOICE_NUM
1353 from AP_EXPENSE_REPORT_HEADERS
1354 where REPORT_HEADER_ID = p_source_report_header_id;
1355
1356 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPRPT_PKG',
1357 'end DuplicateHeader');
1358
1359 END DuplicateHeader;
1360 --------------------------------------------------------------------------------
1361
1362 FUNCTION UpdateHeaderTotal(
1363 p_report_header_id IN expHdr_headerID
1364 ) RETURN BOOLEAN IS
1365 --------------------------------------------------------------------------------
1366 l_total number;
1367 l_diff number;
1368 BEGIN
1369
1370 -- Bug 9286884 - Itemized Parents shouldn't be included in total calc.
1371 select sum(amount)
1372 into l_total
1373 from ap_expense_report_lines
1374 where report_header_id = p_report_header_id
1375 and Nvl(itemization_parent_id,-200) <> -1;
1376
1377 select(l_total - total)
1378 into l_diff
1379 from ap_expense_report_headers
1380 where report_header_id = p_report_header_id;
1381
1382 update ap_expense_report_headers
1383 set total = l_total,
1384 amt_due_employee = amt_due_employee + l_diff
1385 where report_header_id = p_report_header_id;
1386
1387 return TRUE;
1388
1389 EXCEPTION
1390 WHEN NO_DATA_FOUND THEN
1391 return FALSE;
1392 WHEN OTHERS THEN
1393 AP_WEB_DB_UTIL_PKG.RaiseException('UpdateHeaderTotal');
1394 APP_EXCEPTION.RAISE_EXCEPTION;
1395 return FALSE;
1396 END UpdateHeaderTotal;
1397
1398 --------------------------------------------------------------------------------
1399
1400
1401 FUNCTION GetReimbCurr(
1402 p_expenseReportId IN expHdr_headerID,
1403 p_payment_curr_code OUT NOCOPY expHdr_payemntCurrCode
1404 ) RETURN BOOLEAN IS
1405 -------------------------------------------------------------------
1406 BEGIN
1407 SELECT payment_currency_code
1408 INTO p_payment_curr_code
1409 FROM ap_expense_report_headers
1410 WHERE report_header_id = p_expenseReportId;
1411
1412 return TRUE;
1413 EXCEPTION
1414 WHEN NO_DATA_FOUND THEN
1415 return FALSE;
1416 WHEN OTHERS THEN
1417 AP_WEB_DB_UTIL_PKG.RaiseException('GetReimbCurr');
1418 APP_EXCEPTION.RAISE_EXCEPTION;
1419 return FALSE;
1420
1421 END GetReimbCurr;
1422 -------------------------------------------------------------------
1423 FUNCTION GetHeaderTotal(p_report_header_id IN expHdr_headerID,
1424 p_total OUT NOCOPY NUMBER)
1425 RETURN BOOLEAN IS
1426 -------------------------------------------------------------------
1427 BEGIN
1428 SELECT total
1429 INTO p_total
1430 FROM ap_expense_report_headers
1431 WHERE report_header_id = p_report_header_id;
1432
1433 RETURN true;
1434 EXCEPTION
1435 WHEN NO_DATA_FOUND THEN
1436 return FALSE;
1437 WHEN OTHERS THEN
1438 AP_WEB_DB_UTIL_PKG.RaiseException('GetHeaderTotal');
1439 APP_EXCEPTION.RAISE_EXCEPTION;
1440 return FALSE;
1441 END GetHeaderTotal ;
1442 -------------------------------------------------------------------
1443
1444 /*Written By :Amulya Mishra
1445 Purpose :Returns the PAYMENT_DUE_FROM_CODE from
1446 AP_CREDIT_CARD_TRXNS_ALL table based upon
1447 report_header_id.
1448 */
1449 FUNCTION getPaymentDueFromReport(
1450 p_report_header_id IN expHdr_headerID,
1451 p_paymentDueFromCode OUT NOCOPY VARCHAR2)
1452 RETURN BOOLEAN IS
1453
1454 p_payment_due_from_code VARCHAR2(30);
1455 -----------------------------------------------------------------------------
1456 BEGIN
1457 p_paymentDueFromCode := NULL;
1458 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_CCARD_PKG', 'start getPaymentDueFromReport');
1459
1460 SELECT payment_due_from_code
1461 INTO p_paymentDueFromCode
1462 FROM ap_credit_card_trxns_all trx
1463 WHERE trx.report_header_id = p_report_header_id
1464 AND rownum = 1; --Data Corruption might give two Distinct Pay Methods.
1465
1466 return TRUE;
1467
1468 EXCEPTION
1469 WHEN NO_DATA_FOUND THEN
1470 RETURN FALSE;
1471
1472 WHEN OTHERS THEN
1473 AP_WEB_DB_UTIL_PKG.RaiseException( 'getPaymentDueFromReport');
1474 APP_EXCEPTION.RAISE_EXCEPTION;
1475
1476 END getPaymentDueFromReport;
1477 -----------------------------------------------------------------------------
1478
1479 -----------------------------------------------------------------------------
1480 /*Written By :Ron Langi
1481 Purpose :Returns the Audit Return Reason and Instruction
1482 using the report_header_id.
1483 */
1484 -----------------------------------------------------------------------------
1485 FUNCTION getAuditReturnReasonInstr(
1486 p_report_header_id IN expHdr_headerID,
1487 p_return_reason OUT NOCOPY VARCHAR2,
1488 p_return_instruction OUT NOCOPY VARCHAR2)
1489 RETURN BOOLEAN IS
1490
1491 BEGIN
1492 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_EXPRPT_PKG', 'start getAuditReturnReasonInstr');
1493
1494 SELECT AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUD_RETURN_REASONS',aerh.return_reason_code),
1495 AP_WEB_POLICY_UTILS.get_lookup_description('OIE_AUD_RETURN_REASONS',aerh.return_reason_code)||' '||aerh.return_instruction
1496 INTO p_return_reason,
1497 p_return_instruction
1498 FROM ap_expense_report_headers aerh
1499 WHERE aerh.report_header_id = p_report_header_id;
1500
1501 return true;
1502
1503 EXCEPTION
1504 WHEN NO_DATA_FOUND THEN
1505 RETURN false;
1506 WHEN OTHERS THEN
1507 AP_WEB_DB_UTIL_PKG.RaiseException('getAuditReturnReasonInstr');
1508 APP_EXCEPTION.RAISE_EXCEPTION;
1509 END getAuditReturnReasonInstr;
1510
1511 -----------------------------------------------------------------------------
1512 /*Written By :Ron Langi
1513 Purpose :Clears the Audit Return Reason and Instruction
1514 using the report_header_id.
1515 */
1516 -----------------------------------------------------------------------------
1517 PROCEDURE clearAuditReturnReasonInstr(
1518 p_report_header_id IN expHdr_headerID)
1519 IS
1520
1521 BEGIN
1522 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_EXPRPT_PKG', 'start clearAuditReturnReasonInstr');
1523
1524 UPDATE ap_expense_report_headers aerh
1525 SET aerh.return_reason_code = '',
1526 aerh.return_instruction = ''
1527 WHERE aerh.report_header_id = p_report_header_id;
1528
1529 EXCEPTION
1530 WHEN NO_DATA_FOUND THEN
1531 RETURN ;
1532 WHEN OTHERS THEN
1533 AP_WEB_DB_UTIL_PKG.RaiseException('clearAuditReturnReasonInstr');
1534 APP_EXCEPTION.RAISE_EXCEPTION;
1535 END clearAuditReturnReasonInstr;
1536
1537 --------------------------------------------------------------------------------
1538 FUNCTION GetDefaultEmpCCID(
1539 p_employee_id IN NUMBER,
1540 p_default_emp_ccid OUT NOCOPY expHdr_employeeCCID)
1541
1542 RETURN BOOLEAN IS
1543
1544 --------------------------------------------------------------------------------
1545 l_debugInfo varchar2(240);
1546 BEGIN
1547 l_debugInfo := 'Get default employee CCID';
1548
1549 SELECT default_code_combination_id
1550 INTO p_default_emp_ccid
1551 FROM (
1552 SELECT emp.default_code_combination_id
1553 FROM per_employees_x emp
1554 WHERE emp.employee_id = p_employee_id
1555 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
1556 UNION ALL
1557 SELECT emp.default_code_combination_id
1558 FROM per_cont_workers_current_x emp
1559 WHERE emp.person_id = p_employee_id
1560 );
1561
1562
1563 RETURN TRUE;
1564
1565 EXCEPTION
1566 WHEN NO_DATA_FOUND THEN
1567 return FALSE;
1568 WHEN OTHERS THEN
1569 AP_WEB_DB_UTIL_PKG.RaiseException('GetDefaultEmpCCID',
1570 l_debugInfo);
1571 APP_EXCEPTION.RAISE_EXCEPTION;
1572 return FALSE;
1573 END GetDefaultEmpCCID;
1574
1575 --------------------------------------------------------------------------------
1576 FUNCTION GetChartOfAccountsID(
1577 p_employee_id IN NUMBER,
1578 p_chart_of_accounts_id OUT NOCOPY glsob_chartOfAccountsID)
1579
1580 RETURN BOOLEAN IS
1581
1582 --------------------------------------------------------------------------------
1583 l_debugInfo varchar2(240);
1584 l_emp_set_of_books_id gl_sets_of_books.set_of_books_id%type;
1585 BEGIN
1586 l_debugInfo := 'Get Chart of Accounts ID';
1587
1588 SELECT set_of_books_id
1589 INTO l_emp_set_of_books_id
1590 FROM (
1591 SELECT emp.set_of_books_id
1592 FROM per_employees_x emp
1593 WHERE emp.employee_id = p_employee_id
1594 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
1595 UNION ALL
1596 SELECT emp.set_of_books_id
1597 FROM per_cont_workers_current_x emp
1598 WHERE emp.person_id = p_employee_id
1599 );
1600
1601 IF (l_emp_set_of_books_id IS NOT NULL) THEN
1602 SELECT GS.chart_of_accounts_id
1603 INTO p_chart_of_accounts_id
1604 FROM gl_sets_of_books GS
1605 WHERE GS.set_of_books_id=l_emp_set_of_books_id;
1606 ELSE
1607 RETURN FALSE;
1608 END IF;
1609
1610 RETURN TRUE;
1611
1612 EXCEPTION
1613 WHEN NO_DATA_FOUND THEN
1614 return FALSE;
1615 WHEN OTHERS THEN
1616 AP_WEB_DB_UTIL_PKG.RaiseException('GetChartOfAccountsID',
1617 l_debugInfo);
1618 APP_EXCEPTION.RAISE_EXCEPTION;
1619 return FALSE;
1620 END GetChartOfAccountsID;
1621
1622
1623 --------------------------------------------------------------------------------
1624 FUNCTION GetFlexConcactenated(p_parameter_id IN ap_expense_report_params.parameter_id%TYPE,
1625 p_FlexConcactenated OUT NOCOPY ap_expense_report_params.FLEX_CONCACTENATED%TYPE)
1626 RETURN BOOLEAN IS
1627
1628 --------------------------------------------------------------------------------
1629 l_debugInfo varchar2(240);
1630 BEGIN
1631 l_debugInfo := 'Get FlexConcactenated';
1632
1633 SELECT Flex_Concactenated
1634 INTO p_FlexConcactenated
1635 FROM AP_EXPENSE_REPORT_PARAMS
1636 WHERE parameter_id = p_parameter_id;
1637
1638
1639 RETURN TRUE;
1640
1641 EXCEPTION
1642 WHEN NO_DATA_FOUND THEN
1643 return FALSE;
1644 WHEN OTHERS THEN
1645 AP_WEB_DB_UTIL_PKG.RaiseException('GetFlexConcactenated',
1646 l_debugInfo);
1647 APP_EXCEPTION.RAISE_EXCEPTION;
1648 return FALSE;
1649 END GetFlexConcactenated;
1650
1651 /*Written By :Maulik Vadera
1652 Purpose :Wrapper function over getPaymentDueFromReport(p_report_header_id,p_paymentDueFromCode)
1653 */
1654 --------------------------------------------------------------------------------
1655 FUNCTION getPaymentDueFromReport(p_report_header_id IN expHdr_headerID)
1656 RETURN VARCHAR2 IS
1657 --------------------------------------------------------------------------------
1658 l_payment_due_from_code VARCHAR2(30);
1659 l_temp_bool BOOLEAN;
1660
1661 BEGIN
1662
1663 l_temp_bool := getPaymentDueFromReport(p_report_header_id,l_payment_due_from_code);
1664
1665 RETURN l_payment_due_from_code;
1666
1667 END getPaymentDueFromReport;
1668
1669 ------------------------------------------------------------------------
1670 -- FUNCTION GetERInvoiceNumber
1671 -- Returns the invoice number of an expense report
1672 -- 03/22/2005 - Kristian Widjaja
1673 ------------------------------------------------------------------------
1674 FUNCTION GetERInvoiceNumber(p_report_header_id IN NUMBER)
1675 RETURN VARCHAR2 IS
1676
1677 l_invoice_num AP_EXPENSE_REPORT_HEADERS_ALL.INVOICE_NUM%TYPE;
1678
1679 BEGIN
1680 SELECT invoice_num
1681 INTO l_invoice_num
1682 FROM ap_expense_report_headers_all
1683 WHERE report_header_id = p_report_header_id;
1684
1685 return l_invoice_num;
1686 EXCEPTION
1687 WHEN OTHERS THEN
1688 AP_WEB_DB_UTIL_PKG.RaiseException('GetERInvoiceNumber');
1689 APP_EXCEPTION.RAISE_EXCEPTION;
1690 END GetERInvoiceNumber;
1691
1692 ------------------------------------------------------------------------
1693 -- FUNCTION GetERWorkflowApproved
1694 -- Returns the workflow approved flag of an expense report
1695 -- 03/22/2005 - Kristian Widjaja
1696 ------------------------------------------------------------------------
1697 FUNCTION GetERWorkflowApprovedFlag(p_report_header_id IN NUMBER)
1698 RETURN VARCHAR2 IS
1699 l_workflow_approved_flag AP_EXPENSE_REPORT_HEADERS_ALL.WORKFLOW_APPROVED_FLAG%TYPE;
1700
1701 BEGIN
1702 SELECT workflow_approved_flag
1703 INTO l_workflow_approved_flag
1704 FROM ap_expense_report_headers_all
1705 WHERE report_header_id = p_report_header_id;
1706
1707 return l_workflow_approved_flag;
1708 EXCEPTION
1709 WHEN OTHERS THEN
1710 AP_WEB_DB_UTIL_PKG.RaiseException('GetERWorkflowApprovedFlag');
1711 APP_EXCEPTION.RAISE_EXCEPTION;
1712 END GetERWorkflowApprovedFlag;
1713
1714 FUNCTION GetERLastUpdateDate(p_report_header_id IN NUMBER)
1715 RETURN VARCHAR2 IS
1716 l_last_update_date VARCHAR2(30);
1717
1718 BEGIN
1719 SELECT to_char(last_update_date, 'DD-MON-RRRR HH:MI:SS', 'NLS_DATE_LANGUAGE = ENGLISH')
1720 INTO l_last_update_date
1721 FROM ap_expense_report_headers_all
1722 WHERE report_header_id = p_report_header_id;
1723
1724 return l_last_update_date;
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 AP_WEB_DB_UTIL_PKG.RaiseException('GetERLastUpdateDate');
1728 APP_EXCEPTION.RAISE_EXCEPTION;
1729 END GetERLastUpdateDate;
1730
1731 ------------------------------------------------------------------------
1732 -- FUNCTION CopyAttachments
1733 -- API to create the reference for the Attachments from source
1734 -- to target depending on the entity name
1735 -- Entity Name=OIE_HEADER_ATTACHMENTS - Header Level Attachments
1736 -- Entity Name=OIE_LINE_ATTACHMENTS - Line Level Attachments
1737 -- 10th Dec, 2009 - SaiKumar Talasila.
1738 ------------------------------------------------------------------------
1739 PROCEDURE CopyAttachments(p_source_id IN NUMBER,
1740 p_target_id IN NUMBER,
1741 p_entity_name IN VARCHAR2)
1742 IS
1743
1744 CURSOR expense_attachments_cur(l_report_header_id IN NUMBER) IS
1745 SELECT *
1746 FROM ( SELECT *
1747 FROM fnd_attached_documents
1748 WHERE entity_name = p_entity_name
1749 AND pk1_value = To_Char(l_report_header_id)
1750 );
1751
1752 CURSOR expense_documents_cur(l_document_id IN NUMBER) IS
1753 SELECT *
1754 FROM fnd_documents
1755 WHERE document_id = l_document_id;
1756
1757 CURSOR expense_documents_tl_cur(l_document_id IN NUMBER) IS
1758 SELECT *
1759 FROM fnd_documents_tl
1760 WHERE document_id = l_document_id
1761 AND rownum = 1;
1762
1763 AttachedDocTabRec expense_attachments_cur%ROWTYPE;
1764
1765 DocumentTabRec expense_documents_cur%ROWTYPE;
1766
1767 DocumentTLTabRec expense_documents_tl_cur%ROWTYPE;
1768
1769 l_rowid VARCHAR2(60) := null;
1770 l_media_id NUMBER := null;
1771
1772 BEGIN
1773
1774 IF(p_source_id IS NULL OR p_target_id IS NULL) THEN
1775 RETURN;
1776 END IF;
1777
1778
1779 OPEN expense_attachments_cur(p_source_id);
1780
1781 LOOP
1782
1783 FETCH expense_attachments_cur
1784 INTO AttachedDocTabRec;
1785
1786 EXIT WHEN expense_attachments_cur%NOTFOUND;
1787
1788 BEGIN
1789
1790 SELECT fnd_attached_documents_s.nextval
1791 INTO AttachedDocTabRec.attached_document_id
1792 FROM dual;
1793
1794 FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
1795 (x_rowid => l_rowid
1796 , x_attached_document_id => AttachedDocTabRec.attached_document_id
1797 , x_document_id => AttachedDocTabRec.document_id
1798 , x_seq_num => AttachedDocTabRec.seq_num
1799 , x_entity_name => AttachedDocTabRec.entity_name
1800 , x_pk1_value => To_Char(p_target_id)
1801 , x_pk2_value => null
1802 , x_pk3_value => null
1803 , x_pk4_value => null
1804 , x_pk5_value => null
1805 , x_automatically_added_flag => 'Y'
1806 , x_creation_date => sysdate
1807 , x_created_by => AttachedDocTabRec.created_by
1808 , x_last_update_date => sysdate
1809 , x_last_updated_by => to_number(fnd_global.user_id)
1810 , x_last_update_login => to_number(FND_GLOBAL.LOGIN_ID)
1811 , x_column1 => AttachedDocTabRec.column1
1812 , x_datatype_id => null
1813 , x_category_id => AttachedDocTabRec.category_id
1814 , x_security_type => null
1815 , X_security_id => null
1816 , X_publish_flag => null
1817 , X_image_type => null
1818 , X_storage_type => null
1819 , X_usage_type => null
1820 , X_language => null
1821 , X_description => null
1822 , X_file_name => null
1823 , X_media_id => l_media_id
1824 , X_doc_attribute_Category => null
1825 , X_doc_attribute1 => null
1826 , X_doc_attribute2 => null
1827 , X_doc_attribute3 => null
1828 , X_doc_attribute4 => null
1829 , X_doc_attribute5 => null
1830 , X_doc_attribute6 => null
1831 , X_doc_attribute7 => null
1832 , X_doc_attribute8 => null
1833 , X_doc_attribute9 => null
1834 , X_doc_attribute10 => null
1835 , X_doc_attribute11 => null
1836 , X_doc_attribute12 => null
1837 , X_doc_attribute13 => null
1838 , X_doc_attribute14 => null
1839 , X_doc_attribute15 => null
1840 );
1841
1842 /* Logic to update the document usage_type to "S" */
1843
1844 OPEN expense_documents_cur(AttachedDocTabRec.document_id);
1845
1846 FETCH expense_documents_cur
1847 INTO DocumentTabRec;
1848
1849 CLOSE expense_documents_cur;
1850
1851 OPEN expense_documents_tl_cur(AttachedDocTabRec.document_id);
1852
1853 FETCH expense_documents_tl_cur
1854 INTO DocumentTLTabRec;
1855
1856 CLOSE expense_documents_tl_cur;
1857
1858 FND_DOCUMENTS_PKG.Update_Row
1859 (X_document_id => DocumentTabRec.document_id
1860 ,X_last_update_date => sysdate
1861 ,X_last_updated_by => to_number(fnd_global.user_id)
1862 ,X_last_update_login => to_number(FND_GLOBAL.LOGIN_ID)
1863 ,X_datatype_id => DocumentTabRec.datatype_id
1864 ,X_category_id => DocumentTabRec.category_id
1865 ,X_security_type => DocumentTabRec.security_type
1866 ,X_security_id => DocumentTabRec.security_id
1867 ,X_publish_flag => DocumentTabRec.publish_flag
1868 ,X_image_type => DocumentTabRec.image_type
1869 ,X_storage_type => DocumentTabRec.storage_type
1870 ,X_usage_type => 'S'
1871 ,X_start_date_active => DocumentTabRec.start_date_active
1872 ,X_end_date_active => DocumentTabRec.end_date_active
1873 ,X_language => DocumentTLTabRec.language
1874 ,X_description => DocumentTLTabRec.description
1875 ,X_file_name => DocumentTabRec.file_name
1876 ,X_media_id => DocumentTabRec.media_id
1877 ,X_Attribute_Category => DocumentTLTabRec.doc_attribute_category
1878 ,X_Attribute1 => DocumentTLTabRec.doc_attribute1
1879 ,X_Attribute2 => DocumentTLTabRec.doc_attribute2
1880 ,X_Attribute3 => DocumentTLTabRec.doc_attribute3
1881 ,X_Attribute4 => DocumentTLTabRec.doc_attribute4
1882 ,X_Attribute5 => DocumentTLTabRec.doc_attribute5
1883 ,X_Attribute6 => DocumentTLTabRec.doc_attribute6
1884 ,X_Attribute7 => DocumentTLTabRec.doc_attribute7
1885 ,X_Attribute8 => DocumentTLTabRec.doc_attribute8
1886 ,X_Attribute9 => DocumentTLTabRec.doc_attribute9
1887 ,X_Attribute10 => DocumentTLTabRec.doc_attribute10
1888 ,X_Attribute11 => DocumentTLTabRec.doc_attribute11
1889 ,X_Attribute12 => DocumentTLTabRec.doc_attribute12
1890 ,X_Attribute13 => DocumentTLTabRec.doc_attribute13
1891 ,X_Attribute14 => DocumentTLTabRec.doc_attribute14
1892 ,X_Attribute15 => DocumentTLTabRec.doc_attribute15
1893 ,X_url => DocumentTabRec.url
1894 ,X_title => DocumentTLTabRec.title);
1895
1896 EXCEPTION
1897 WHEN OTHERS THEN
1898 --Error raised and ignored while Transferring the attachments.
1899 NULL;
1900 END;
1901
1902 END LOOP;
1903
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 AP_WEB_DB_UTIL_PKG.RaiseException('CopyAttachments');
1907 APP_EXCEPTION.RAISE_EXCEPTION;
1908 END CopyAttachments;
1909
1910 END AP_WEB_DB_EXPRPT_PKG;