[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_OA_MAINFLOW_PKG
Source
1 PACKAGE BODY AP_WEB_OA_MAINFLOW_PKG AS
2 /* $Header: apwoamfb.pls 120.60.12020000.2 2012/11/15 09:37:53 preshukl ship $ */
3
4
5 PROCEDURE DeleteExpenseReport(
6 ReportID IN expHdr_headerID)
7 IS
8
9 BEGIN
10
11 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
12 'start DeleteExpenseReport');
13
14 DeleteReport(ReportID);
15
16 --reset any remaining personal credit card lines
17 IF (AP_WEB_DB_CCARD_PKG.ResetCCLines(ReportID)) THEN
18 COMMIT;
19 END IF;
20
21 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
22 'end DeleteExpenseReport');
23 EXCEPTION
24 WHEN OTHERS THEN
25 APP_EXCEPTION.RAISE_EXCEPTION;
26
27 END DeleteExpenseReport;
28
29 PROCEDURE GetEmployeeIdFromBothPayParent(p_bothpay_parent_id IN NUMBER,
30 p_employee_id OUT NOCOPY NUMBER)
31 IS
32 BEGIN
33 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
34 'start GetEmployeeIdFromBothPayParent');
35
36 AP_WEB_DB_EXPRPT_PKG.GetEmployeeIdFromBothPayParent(p_bothpay_parent_id,
37 p_employee_id);
38
39 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
40 'end GetEmployeeIdFromBothPayParent');
41 END GetEmployeeIdFromBothPayParent;
42
43 PROCEDURE GetGeneralInfo(
44 p_preparer_id IN NUMBER,
45 p_default_expense_template_id OUT NOCOPY NUMBER,
46 p_default_approver_name OUT NOCOPY VARCHAR2,
47 p_default_purpose OUT NOCOPY VARCHAR2,
48 p_default_validate_detail_page OUT NOCOPY VARCHAR2,
49 p_default_skip_cc_if_no_trxn OUT NOCOPY VARCHAR2,
50 p_default_foreign_curr_flag OUT NOCOPY VARCHAR2,
51 p_set_of_books_id OUT NOCOPY NUMBER,
52 p_is_grants_enabled OUT NOCOPY VARCHAR2
53 )
54 IS
55 -- Return values from DB layer
56 l_bSOBResult BOOLEAN;
57 l_bUserPrefResult BOOLEAN;
58
59 -- Default expense template ID in Payables
60 l_defaultAPTemplateId AP_SYSTEM_PARAMETERS.expense_report_id%TYPE;
61 l_default_template_name AP_WEB_DB_EXPTEMPLATE_PKG.expTypes_reportType;
62
63 -- Preparer's user preference
64 l_userPrefs AP_WEB_DB_USER_PREF_PKG.UserPrefsInfoRec;
65
66 -- Approver's employee info
67 l_approver_info_rec AP_WEB_DB_HR_INT_PKG.EmployeeInfoRec;
68
69 BEGIN
70
71 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
72 'start GetGeneralInfo');
73
74 -- Initialize arguments
75 p_default_expense_template_id := NULL;
76 p_default_approver_name := NULL;
77
78 -- Get user preferences
79 l_bUserPrefResult := AP_WEB_DB_USER_PREF_PKG.GetUserPrefs(p_preparer_id, l_userPrefs);
80
81 -- Get default expense template ID from Payables Options
82 IF (AP_WEB_DB_EXPTEMPLATE_PKG.GetDefaultTemplateId(l_defaultAPTemplateId)) THEN
83 p_default_expense_template_id := l_defaultAPTemplateId;
84 END IF;
85
86 -- Default expense template ID
87 IF (l_userPrefs.default_expense_template_id IS NOT NULL) THEN
88 -- Want to be sure that the template ID is valid for this org
89 IF (AP_WEB_DB_EXPTEMPLATE_PKG.GetTemplateName(l_userPrefs.default_expense_template_id,
90 l_default_template_name)) THEN
91 p_default_expense_template_id := l_userPrefs.default_expense_template_id;
92 ELSE
93 p_default_expense_template_id := NULL;
94 END IF;
95 END IF;
96
97 -- Default approver name
98 IF (l_userPrefs.default_approver_id IS NOT NULL) THEN
99 IF (AP_WEB_DB_HR_INT_PKG.GetEmployeeInfo(l_userPrefs.default_approver_id, l_approver_info_rec)) THEN
100 p_default_approver_name := l_approver_info_rec.employee_name;
101 END IF;
102 END IF;
103
104 -- Default purpose
105 p_default_purpose := l_userPrefs.default_purpose;
106
107 -- Default validate detail page
108 p_default_validate_detail_page := l_userPrefs.validate_details_flag;
109
110 -- Default foreign currency flag
111 p_default_foreign_curr_flag := l_userPrefs.default_foreign_curr_flag;
112
113 -- Set of books
114 l_bSOBResult := AP_WEB_DB_AP_INT_PKG.GetSOB(p_set_of_books_id);
115
116 IF (GMS_OIE_INT_PKG.IsGrantsEnabled()) THEN
117 p_is_grants_enabled := 'Y';
118 ELSE
119 p_is_grants_enabled := 'N';
120 END IF;
121
122 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
123 'end GetGeneralInfo');
124
125 END GetGeneralInfo;
126
127 FUNCTION IsGrantsEnabled RETURN VARCHAR2
128 IS
129 BEGIN
130 IF (GMS_OIE_INT_PKG.IsGrantsEnabled()) THEN
131 RETURN 'Y';
132 ELSE
133 RETURN 'N';
134 END IF;
135 END IsGrantsEnabled;
136
137
138 /*----------------------------------------------------------------------------*
139 | Procedure |
140 | ValidateReportHeader |
141 | |
142 | DESCRIPTION |
143 | Server-side validation for report header |
144 | |
145 | PARAMETERS |
146 | |
147 | RETURNS |
148 | none |
149 *----------------------------------------------------------------------------*/
150 -------------------------------------------------------------------
151 PROCEDURE ValidateReportHeader(
152 report_header_id IN VARCHAR2,
153 employee_id IN VARCHAR2,
154 cost_center IN VARCHAR2,
155 template_id IN VARCHAR2,
156 template_name IN VARCHAR2,
157 purpose IN VARCHAR2,
158 summary_start_date IN VARCHAR2,
159 last_receipt_date IN VARCHAR2,
160 reimbursement_currency_code IN VARCHAR2,
161 reimbursement_currency_name IN VARCHAR2,
162 multi_currency_flag IN VARCHAR2,
163 override_approver_id IN OUT NOCOPY VARCHAR2,
164 override_approver_name IN OUT NOCOPY VARCHAR2,
165 number_max_flexfield IN VARCHAR2,
166 amt_due_employee IN VARCHAR2,
167 amt_due_ccCompany IN VARCHAR2,
168 p_IsSessionProjectEnabled IN VARCHAR2,
169 p_return_status OUT NOCOPY VARCHAR2,
170 p_msg_count OUT NOCOPY NUMBER,
171 p_msg_data OUT NOCOPY VARCHAR2
172 ) IS
173 -------------------------------------------------------------------
174 ExpReportHeaderInfo AP_WEB_DFLEX_PKG.ExpReportHeaderRec;
175 l_Error AP_WEB_UTILITIES_PKG.expError ;
176 l_org_id AP_WEB_DB_HR_INT_PKG.empCurrent_orgID;
177 l_user_id varchar2(20); -- 2242176, fnd user id
178 l_debug_info varchar2(200);
179 current_calling_sequence varchar2(100) := 'ValidateReportHeader';
180
181 BEGIN
182 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
183 'start ValidateReportHeader');
184 p_return_status := '';
185 l_debug_info := 'setting ExpReportHeaderInfo values';
186 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
187 ExpReportHeaderInfo.report_header_id := report_header_id;
188 ExpReportHeaderInfo.employee_id := employee_id;
189 ExpReportHeaderInfo.cost_center := cost_center;
190 ExpReportHeaderInfo.template_id := template_id;
191 ExpReportHeaderInfo.template_name := template_name;
192 ExpReportHeaderInfo.purpose := purpose;
193 ExpReportHeaderInfo.summary_start_date := summary_start_date;
194 ExpReportHeaderInfo.last_receipt_date := last_receipt_date;
195 ExpReportHeaderInfo.reimbursement_currency_code := reimbursement_currency_code;
196 ExpReportHeaderInfo.reimbursement_currency_name := reimbursement_currency_name;
197 ExpReportHeaderInfo.multi_currency_flag := multi_currency_flag;
198 ExpReportHeaderInfo.override_approver_id := override_approver_id;
199 ExpReportHeaderInfo.override_approver_name := override_approver_name;
200 ExpReportHeaderInfo.number_max_flexfield := number_max_flexfield;
201 ExpReportHeaderInfo.amt_due_employee := amt_due_employee;
202 ExpReportHeaderInfo.amt_due_ccCompany := amt_due_ccCompany;
203
204 IF P_IsSessionProjectEnabled = 'Y' THEN
205 IF (AP_WEB_DB_HR_INT_PKG.GetEmpOrgId(ExpReportHeaderInfo.employee_id, l_org_id)) THEN
206 ExpReportHeaderInfo.expenditure_organization_id := l_org_id;
207 END IF;
208 END IF;
209
210 l_debug_info := 'Getting employee user ID';
211 GetUserID(employee_id, l_user_id);
212
213 l_debug_info := 'calling ValidateReportHeader';
214 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
215 --Bug 2829307:Pass session_id for validation.
216 AP_WEB_VALIDATE_UTIL.ValidateHeaderNoValidSession(
217 p_user_id => TO_NUMBER(l_user_id),
218 ExpReportHeaderInfo => ExpReportHeaderInfo ,
219 p_error => l_Error,
220 p_bFull_Approver_Validation => FALSE);
221 if (ExpReportHeaderInfo.override_approver_id IS NOT NULL) THEN
222 override_approver_id := ExpReportHeaderInfo.override_approver_id;
223 override_approver_name := ExpReportHeaderInfo.override_approver_name;
224 end if;
225
226 fnd_msg_pub.count_and_get(p_count => p_msg_count,
227 p_data => p_msg_data);
228
229 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
230 'end ValidateReportHeader');
231 exception
232 when others then
233 BEGIN
234 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
235 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
236 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
237 current_calling_sequence);
238 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
239 APP_EXCEPTION.RAISE_EXCEPTION;
240 END;
241 END ValidateReportHeader;
242
243
244 /*----------------------------------------------------------------------------*
245 | Procedure |
246 | ValidateReceiptLine |
247 | |
248 | DESCRIPTION |
249 | Server-side validation for one receipt line |
250 | |
251 | PARAMETERS |
252 | |
253 | RETURNS |
254 | none |
255 *----------------------------------------------------------------------------*/
256 -----------------------------------------------------------------
257 PROCEDURE ValidateReceiptLine(
258 employee_id IN VARCHAR2,
259 template_id IN VARCHAR2,
260 summary_start_date IN VARCHAR2,
261 reimbursement_currency_code IN VARCHAR2,
262 reimbursement_currency_name IN VARCHAR2,
263 multi_currency_flag IN VARCHAR2,
264 override_approver_name IN VARCHAR2,
265 number_max_flexfield IN VARCHAR2,
266 start_date IN DATE,
267 end_date IN DATE,
268 days IN VARCHAR2,
269 daily_amount IN NUMBER,
270 receipt_amount IN NUMBER,
271 rate IN VARCHAR2,
272 amount IN NUMBER,
273 parameter_id IN VARCHAR2,
274 currency_code IN VARCHAR2,
275 merchant IN VARCHAR2,
276 merchantDoc IN VARCHAR2,
277 taxReference IN VARCHAR2,
278 taxRegNumber IN VARCHAR2,
279 taxPayerId IN VARCHAR2,
280 supplyCountry IN VARCHAR2,
281 itemizeId IN VARCHAR2,
282 cCardTrxnId IN VARCHAR2,
283 group_value IN VARCHAR2,
284 justification IN OUT NOCOPY VARCHAR2,
285 receipt_missing_flag IN VARCHAR2,
286 validation_required IN VARCHAR2,
287 taxOverrideFlag IN VARCHAR2,
288 project_number IN VARCHAR2,
289 task_number IN VARCHAR2,
290 project_name OUT NOCOPY VARCHAR2,
291 task_name OUT NOCOPY VARCHAR2,
292 attribute1 IN VARCHAR2,
293 attribute2 IN VARCHAR2,
294 attribute3 IN VARCHAR2,
295 attribute4 IN VARCHAR2,
296 attribute5 IN VARCHAR2,
297 attribute6 IN VARCHAR2,
298 attribute7 IN VARCHAR2,
299 attribute8 IN VARCHAR2,
300 attribute9 IN VARCHAR2,
301 attribute10 IN VARCHAR2,
302 attribute11 IN VARCHAR2,
303 attribute12 IN VARCHAR2,
304 attribute13 IN VARCHAR2,
305 attribute14 IN VARCHAR2,
306 attribute15 IN VARCHAR2,
307 p_IsSessionTaxEnabled IN VARCHAR2,
308 p_IsSessionProjectEnabled IN VARCHAR2,
309 p_calculate_amt_index IN INTEGER,
310 p_calculated_receipt_amount OUT NOCOPY VARCHAR2,
311 p_return_status OUT NOCOPY VARCHAR2,
312 p_msg_count OUT NOCOPY NUMBER,
313 p_msg_data OUT NOCOPY VARCHAR2,
314 p_userId IN VARCHAR2,
315 award_number IN VARCHAR2,
316 p_cost_center IN VARCHAR2,
317 p_template_name IN VARCHAR2,--Bug 2510993
318 p_purpose IN VARCHAR2,--Bug 2510993
319 p_override_approver_id IN NUMBER, --Bug 2510993
320 p_last_update_date IN DATE, --Bug 2510993
321
322 receipt_cost_center IN VARCHAR2 DEFAULT NULL,
323 -- skaneshi: temporarily put default null so does not cause plsql error
324 p_transaction_currency_type IN VARCHAR2,
325 p_inverse_rate_flag IN VARCHAR2,
326 p_report_header_id IN NUMBER,
327 p_category_code IN VARCHAR2, --Bug 2292854
328 -- Per Diem data
329 p_nFreeBreakfasts1 IN NUMBER,
330 p_nFreeBreakfasts2 IN NUMBER,
331 p_nFreeBreakfasts3 IN NUMBER,
332 p_nFreeLunches1 IN NUMBER,
333 p_nFreeLunches2 IN NUMBER,
334 p_nFreeLunches3 IN NUMBER,
335 p_nFreeDinners1 IN NUMBER,
336 p_nFreeDinners2 IN NUMBER,
337 p_nFreeDinners3 IN NUMBER,
338 p_nFreeAccommodations1 IN NUMBER,
339 p_nFreeAccommodations2 IN NUMBER,
340 p_nFreeAccommodations3 IN NUMBER,
341 p_location IN VARCHAR2,
342 -- Mileage data
343 p_dailyDistance IN NUMBER,
344 p_tripDistance IN NUMBER,
345 p_mileageRate IN NUMBER,
346 p_vehicleCategory IN VARCHAR2,
347 p_vehicleType IN VARCHAR2,
348 p_fuelType IN VARCHAR2,
349 p_numberPassengers IN NUMBER,
350 p_default_currency_code IN VARCHAR2,
351 p_default_exchange_rate_type IN VARCHAR2,
352 p_header_attribute_category IN VARCHAR2,
353 p_header_attribute1 IN VARCHAR2,
354 p_header_attribute2 IN VARCHAR2,
355 p_header_attribute3 IN VARCHAR2,
356 p_header_attribute4 IN VARCHAR2,
357 p_header_attribute5 IN VARCHAR2,
358 p_header_attribute6 IN VARCHAR2,
359 p_header_attribute7 IN VARCHAR2,
360 p_header_attribute8 IN VARCHAR2,
361 p_header_attribute9 IN VARCHAR2,
362 p_header_attribute10 IN VARCHAR2,
363 p_header_attribute11 IN VARCHAR2,
364 p_header_attribute12 IN VARCHAR2,
365 p_header_attribute13 IN VARCHAR2,
366 p_header_attribute14 IN VARCHAR2,
367 p_header_attribute15 IN VARCHAR2,
368 p_receipt_index IN NUMBER,
369 p_passenger_rate_used IN NUMBER,
370 p_license_plate_number IN VARCHAR2,
371 p_destination_from IN VARCHAR2,
372 p_destination_to IN VARCHAR2,
373 p_distance_unit_code IN VARCHAR2,
374 p_addon_rates IN OIE_ADDON_RATES_T DEFAULT NULL,
375 p_report_line_id IN NUMBER,
376 p_itemization_parent_id IN NUMBER,
377 p_daily_breakup_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
378 p_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
379 p_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
380 p_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
381 p_number_of_meals IN OIE_PDM_NUMBER_T DEFAULT NULL,
382 p_meals_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
383 p_breakfast_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
384 p_lunch_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
385 p_dinner_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
386 p_accommodation_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
387 p_accommodation_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
388 p_hotel_name IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
389 p_night_rate_Type IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
390 p_night_rate_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
391 p_pdm_rate IN OIE_PDM_NUMBER_T DEFAULT NULL,
392 p_rate_Type_code IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
393 p_pdm_breakup_dest_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
394 p_pdm_destination_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
395 p_dest_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
396 p_dest_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
397 p_location_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
398 p_cust_meals_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
399 p_cust_accommodation_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
400 p_cust_night_rate_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
401 p_cust_pdm_rate IN OUT NOCOPY OIE_PDM_NUMBER_T,
402 p_vat_code IN VARCHAR2 DEFAULT NULL, -- Bug: 6719467
403 p_emp_attendee_count IN NUMBER DEFAULT NULL, -- Bug 6919132
404 p_nonemp_attendee_count IN NUMBER DEFAULT NULL, -- Bug 6919132
405 p_receipt_location_id IN NUMBER DEFAULT NULL -- Bug 14769423
406 ) IS
407 -------------------------------------------------------------------
408
409 l_debug_info VARCHAR2(1000);
410 ExpReportHeaderInfo AP_WEB_DFLEX_PKG.ExpReportHeaderRec;
411 ExpReportLinesInfo AP_WEB_DFLEX_PKG.ExpReportLineRec;
412 Custom1_Array AP_WEB_DFLEX_PKG.CustomFields_A;
413 Custom2_Array AP_WEB_DFLEX_PKG.CustomFields_A;
414 Custom3_Array AP_WEB_DFLEX_PKG.CustomFields_A;
415 Custom4_Array AP_WEB_DFLEX_PKG.CustomFields_A;
416 Custom5_Array AP_WEB_DFLEX_PKG.CustomFields_A;
417 Custom6_Array AP_WEB_DFLEX_PKG.CustomFields_A;
418 Custom7_Array AP_WEB_DFLEX_PKG.CustomFields_A;
419 Custom8_Array AP_WEB_DFLEX_PKG.CustomFields_A;
420 Custom9_Array AP_WEB_DFLEX_PKG.CustomFields_A;
421 Custom10_Array AP_WEB_DFLEX_PKG.CustomFields_A;
422 Custom11_Array AP_WEB_DFLEX_PKG.CustomFields_A;
423 Custom12_Array AP_WEB_DFLEX_PKG.CustomFields_A;
424 Custom13_Array AP_WEB_DFLEX_PKG.CustomFields_A;
425 Custom14_Array AP_WEB_DFLEX_PKG.CustomFields_A;
426 Custom15_Array AP_WEB_DFLEX_PKG.CustomFields_A;
427 Receipts_With_Errors_Count BINARY_INTEGER;
428 Receipt_Error_Array AP_WEB_UTILITIES_PKG.receipt_error_stack;
429 l_has_core_field_errors BOOLEAN;
430 l_has_custom_field_errors BOOLEAN;
431 l_receipt_line_errors AP_WEB_UTILITIES_PKG.receipt_error_stack;
432 l_validate_receipt_errors AP_WEB_UTILITIES_PKG.receipt_error_stack;
433 l_receipt_with_error NUMBER;
434 current_calling_sequence varchar2(100) := 'ValidateReceiptLine';
435 l_expenditure_type AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paExpenditureType := NULL;
436
437 ExpReportLinesInfo_A AP_WEB_DFLEX_PKG.ExpReportLines_A;
438 CustomValuesArray AP_WEB_PARENT_PKG.BigString_Array;
439 l_calculate_amt_index INTEGER := p_calculate_amt_index;
440
441 BEGIN
442 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
443 'start ValidateReceiptLines');
444
445 ExpReportHeaderInfo.employee_id := employee_id;
446 ExpReportHeaderInfo.template_id := template_id;
447 ExpReportHeaderInfo.summary_start_date := summary_start_date;
448 ExpReportHeaderInfo.reimbursement_currency_code := reimbursement_currency_code;
449 ExpReportHeaderInfo.reimbursement_currency_name := reimbursement_currency_name;
450 ExpReportHeaderInfo.multi_currency_flag := multi_currency_flag;
451 ExpReportHeaderInfo.override_approver_name := override_approver_name;
452 ExpReportHeaderInfo.number_max_flexfield := number_max_flexfield;
453 ExpReportHeaderInfo.receipt_count := 1; -- validate one receipt at a time
454 ExpReportHeaderInfo.cost_center := p_cost_center;
455 ExpReportHeaderInfo.report_header_id := p_report_header_id;
456
457
458 --Bug 2510993:
459 ExpReportHeaderInfo.template_name := p_template_name;
460 ExpReportHeaderInfo.purpose := p_purpose;
461 ExpReportHeaderInfo.override_approver_id := p_override_approver_id;
462 ExpReportHeaderInfo.last_update_date := p_last_update_date;
463 ExpReportHeaderInfo.transaction_currency_type := p_transaction_currency_type;
464 ExpReportHeaderInfo.inverse_rate_flag := p_inverse_rate_flag;
465 ExpReportHeaderInfo.default_currency_code := p_default_currency_code;
466 ExpReportHeaderInfo.default_exchange_rate_type := p_default_exchange_rate_type;
467
468 ExpReportHeaderInfo.attribute_category := p_header_attribute_category;
469 ExpReportHeaderInfo.attribute1 := p_header_attribute1;
470 ExpReportHeaderInfo.attribute2 := p_header_attribute2;
471 ExpReportHeaderInfo.attribute3 := p_header_attribute3;
472 ExpReportHeaderInfo.attribute4 := p_header_attribute4;
473 ExpReportHeaderInfo.attribute5 := p_header_attribute5;
474 ExpReportHeaderInfo.attribute6 := p_header_attribute6;
475 ExpReportHeaderInfo.attribute7 := p_header_attribute7;
476 ExpReportHeaderInfo.attribute8 := p_header_attribute8;
477 ExpReportHeaderInfo.attribute9 := p_header_attribute9;
478 ExpReportHeaderInfo.attribute10 := p_header_attribute10;
479 ExpReportHeaderInfo.attribute11 := p_header_attribute11;
480 ExpReportHeaderInfo.attribute12 := p_header_attribute12;
481 ExpReportHeaderInfo.attribute13 := p_header_attribute13;
482 ExpReportHeaderInfo.attribute14 := p_header_attribute14;
483 ExpReportHeaderInfo.attribute15 := p_header_attribute15;
484
485 ExpReportLinesInfo.start_date := start_date;
486 ExpReportLinesInfo.end_date := end_date;
487 ExpReportLinesInfo.days := days;
488 ExpReportLinesInfo.daily_amount := to_char(daily_amount);
489 ExpReportLinesInfo.receipt_amount := to_char(receipt_amount);
490 ExpReportLinesInfo.rate := rate;
491 ExpReportLinesInfo.amount := to_char(amount);
492 ExpReportLinesInfo.parameter_id := parameter_id;
493 ExpReportLinesInfo.currency_code := currency_code;
494 ExpReportLinesInfo.merchant := merchant;
495 ExpReportLinesInfo.merchantDoc := merchantDoc;
496 ExpReportLinesInfo.taxReference := taxReference;
497 ExpReportLinesInfo.taxRegNumber := taxRegNumber;
498 ExpReportLinesInfo.taxPayerId := taxPayerId;
499 ExpReportLinesInfo.supplyCountry := supplyCountry;
500 ExpReportLinesInfo.itemizeId := itemizeId;
501 ExpReportLinesInfo.cCardTrxnId := cCardTrxnId;
502 ExpReportLinesInfo.group_value := group_value;
503 ExpReportLinesInfo.justification := justification;
504 ExpReportLinesInfo.receipt_missing_flag := receipt_missing_flag;
505 ExpReportLinesInfo.validation_required := validation_required;
506 ExpReportLinesInfo.tax_code := p_vat_code; -- bug: 6719467
507 ExpReportLinesInfo.taxOverrideFlag := taxOverrideFlag;
508 --BUg 2292854
509 ExpReportLinesInfo.category_code := p_category_code;
510 ExpReportLinesInfo.emp_attendee_count := p_emp_attendee_count; -- Bug 6919132
511 ExpReportLinesInfo.nonemp_attendee_count := p_nonemp_attendee_count; -- Bug 6919132
512 -- per diem data
513 ExpReportLinesInfo.nFreeBreakfasts1 := p_nFreeBreakfasts1;
514 ExpReportLinesInfo.nFreeBreakfasts2 := p_nFreeBreakfasts2;
515 ExpReportLinesInfo.nFreeBreakfasts3 := p_nFreeBreakfasts3;
516 ExpReportLinesInfo.nFreeLunches1 := p_nFreeLunches1;
517 ExpReportLinesInfo.nFreeLunches2 := p_nFreeLunches2;
518 ExpReportLinesInfo.nFreeLunches3 := p_nFreeLunches3;
519 ExpReportLinesInfo.nFreeDinners1 := p_nFreeDinners1;
520 ExpReportLinesInfo.nFreeDinners2 := p_nFreeDinners2;
521 ExpReportLinesInfo.nFreeDinners3 := p_nFreeDinners3;
522 ExpReportLinesInfo.nFreeAccommodations1 := p_nFreeAccommodations1;
523 ExpReportLinesInfo.nFreeAccommodations2 := p_nFreeAccommodations2;
524 ExpReportLinesInfo.nFreeAccommodations3 := p_nFreeAccommodations3;
525 ExpReportLinesInfo.location := p_location;
526 -- Bug 3600198
527 ExpReportLinesInfo.startTime := to_char(start_date, 'HH24:MI');
528 ExpReportLinesInfo.endTime := to_char(end_date, 'HH24:MI');
529 -- mileage data
530 ExpReportLinesInfo.dailyDistance := p_dailyDistance;
531 ExpReportLinesInfo.tripDistance := p_tripDistance;
532 ExpReportLinesInfo.mileageRate := p_mileageRate;
533 ExpReportLinesInfo.vehicleCategory := p_vehicleCategory;
534 ExpReportLinesInfo.vehicleType := p_vehicleType;
535 ExpReportLinesInfo.fuelType := p_fuelType;
536 ExpReportLinesInfo.numberPassengers := p_numberPassengers;
537 ExpReportLinesInfo.receipt_index := p_receipt_index;
538 ExpReportLinesInfo.licensePlateNumber := p_license_plate_number;
539 ExpReportLinesInfo.passengerRateUsed := p_passenger_rate_used;
540 ExpReportLinesInfo.destinationFrom := p_destination_from;
541 ExpReportLinesInfo.destinationTo := p_destination_to;
542 ExpReportLinesInfo.distanceUnitCode := p_distance_unit_code;
543 ExpReportLinesInfo.report_line_id := p_report_line_id;
544 ExpReportLinesInfo.location_id := p_receipt_location_id; -- Bug: 14769423
545 if (p_itemization_parent_id = 0) then
546 ExpReportLinesInfo.itemization_parent_id := null;
547 else
548 ExpReportLinesInfo.itemization_parent_id := p_itemization_parent_id;
549 end if;
550
551 Custom1_Array(1).value := attribute1;
552 Custom2_Array(1).value := attribute2;
553 Custom3_Array(1).value := attribute3;
554 Custom4_Array(1).value := attribute4;
555 Custom5_Array(1).value := attribute5;
556 Custom6_Array(1).value := attribute6;
557 Custom7_Array(1).value := attribute7;
558 Custom8_Array(1).value := attribute8;
559 Custom9_Array(1).value := attribute9;
560 Custom10_Array(1).value := attribute10;
561 Custom11_Array(1).value := attribute11;
562 Custom12_Array(1).value := attribute12;
563 Custom13_Array(1).value := attribute13;
564 Custom14_Array(1).value := attribute14;
565 Custom15_Array(1).value := attribute15;
566
567 ------------------------------------------------------------------------
568 l_debug_info := 'Convert to Arrays to pass into MapColumnToCustomFields';
569 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
570 ------------------------------------------------------------------------
571 ExpReportLinesInfo_A(1) := ExpReportLinesInfo;
572 CustomValuesArray(1) := Custom1_Array(1).value;
573 CustomValuesArray(2) := Custom2_Array(1).value;
574 CustomValuesArray(3) := Custom3_Array(1).value;
575 CustomValuesArray(4) := Custom4_Array(1).value;
576 CustomValuesArray(5) := Custom5_Array(1).value;
577 CustomValuesArray(6) := Custom6_Array(1).value;
578 CustomValuesArray(7) := Custom7_Array(1).value;
579 CustomValuesArray(8) := Custom8_Array(1).value;
580 CustomValuesArray(9) := Custom9_Array(1).value;
581 CustomValuesArray(10) := Custom10_Array(1).value;
582 CustomValuesArray(11) := Custom11_Array(1).value;
583 CustomValuesArray(12) := Custom12_Array(1).value;
584 CustomValuesArray(13) := Custom13_Array(1).value;
585 CustomValuesArray(14) := Custom14_Array(1).value;
586 CustomValuesArray(15) := Custom15_Array(1).value;
587
588 -----------------------------------------------------
589 l_debug_info := 'Calling MapColumnToCustomFields';
590 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
591 -----------------------------------------------------
592 AP_WEB_VALIDATE_UTIL.MapColumnToCustomFields(p_userId,
593 1, --P_ReceiptIndex
594 CustomValuesArray,
595 ExpReportLinesInfo_A,
596 Custom1_Array,
597 Custom2_Array,
598 Custom3_Array,
599 Custom4_Array,
600 Custom5_Array,
601 Custom6_Array,
602 Custom7_Array,
603 Custom8_Array,
604 Custom9_Array,
605 Custom10_Array,
606 Custom11_Array,
607 Custom12_Array,
608 Custom13_Array,
609 Custom14_Array,
610 Custom15_Array);
611
612 ----------------------------------------------------------------------------------------------------------
613 l_debug_info := 'Assiging l_calculate_amt_index to null so that Calculate Amount will not be called';
614 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
615 ----------------------------------------------------------------------------------------------------------
616 IF (p_calculate_amt_index = -1) THEN
617 l_calculate_amt_index := NULL;
618 END IF;
619
620
621 -----------------------------------------------------
622 l_debug_info := 'Calling ValidateExpLine';
623 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
624 -----------------------------------------------------
625 --Bug 2829307:Pass session_id for validation.
626 AP_WEB_VALIDATE_UTIL.ValidateExpLine(
627 to_number(p_userId),
628 ExpReportHeaderInfo,
629 ExpReportLinesInfo,
630 Custom1_Array,
631 Custom2_Array,
632 Custom3_Array,
633 Custom4_Array,
634 Custom5_Array,
635 Custom6_Array,
636 Custom7_Array,
637 Custom8_Array,
638 Custom9_Array,
639 Custom10_Array,
640 Custom11_Array,
641 Custom12_Array,
642 Custom13_Array,
643 Custom14_Array,
644 Custom15_Array,
645 l_has_core_field_errors,
646 l_has_custom_field_errors,
647 l_validate_receipt_errors,
648 l_receipt_with_error,
649 p_IsSessionProjectEnabled,
650 1,
651 l_calculate_amt_index,
652 FALSE,
653 p_addon_rates,
654 p_report_line_id,
655 p_daily_breakup_id,
656 p_start_date,
657 p_end_date,
658 p_amount,
659 p_number_of_meals,
660 p_meals_amount,
661 p_breakfast_flag,
662 p_lunch_flag,
663 p_dinner_flag,
664 p_accommodation_amount,
665 p_accommodation_flag,
666 p_hotel_name,
667 p_night_rate_Type,
668 p_night_rate_amount,
669 p_pdm_rate,
670 p_rate_Type_code,
671 p_pdm_breakup_dest_id,
672 p_pdm_destination_id,
673 p_dest_start_date,
674 p_dest_end_date,
675 p_location_id,
676 p_cust_meals_amount,
677 p_cust_accommodation_amount,
678 p_cust_night_rate_amount,
679 p_cust_pdm_rate
680 );
681
682 ------------------------------------------------------------------------------------------
683 l_debug_info := 'Assigning p_calculated_receipt_amount, which is NULL if not calculated';
684 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
685 ------------------------------------------------------------------------------------------
686 --Return Calculated Receipt Amount
687 p_calculated_receipt_amount := ExpReportLinesInfo.calculated_amount;
688 justification := ExpReportLinesInfo.justification;
689 -----------------------------------------------------
690 l_debug_info := 'Calling fnd_msg_pub.count_and_get';
691 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
692 -----------------------------------------------------
693 fnd_msg_pub.count_and_get(p_count => p_msg_count,
694 p_data => p_msg_data);
695
696 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
697 'end ValidateReceiptLines');
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 BEGIN
702 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
703 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
704 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
705 current_calling_sequence);
706 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
707 APP_EXCEPTION.RAISE_EXCEPTION;
708 END;
709 END ValidateReceiptLine;
710
711
712 /*----------------------------------------------------------------------------*
713 | Procedure |
714 | GetItemDescLookupCode |
715 | |
716 | DESCRIPTION |
717 | Wrapper for calling AP_WEB_DB_EXPTEMPLATE_PKG.Get_ItemDesc_LookupCode |
718 | |
719 | PARAMETERS |
720 | INPUT |
721 | p_parameter_id VARCHAR2 -- web parameter id |
722 | |
723 | OUTPUT |
724 | p_item_description VARCHAR2 -- item descption |
725 | p_line_type_lookup_code VARCHAR2 -- line type lookup code |
726 | |
727 | RETURNS |
728 | none |
729 *----------------------------------------------------------------------------*/
730 ---------------------------------------------------------------------
731 PROCEDURE GetItemDescLookupCode(p_parameter_id IN VARCHAR2,
732 p_item_description OUT NOCOPY VARCHAR2,
733 p_line_type_lookup_code OUT NOCOPY VARCHAR2)
734 ----------------------------------------------------------------------
735 IS
736 bResult BOOLEAN;
737 l_require_receipt_amount NUMBER;
738 BEGIN
739
740 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
741 'start GetItemDescLookupCode');
742
743 bResult := AP_WEB_DB_EXPTEMPLATE_PKG.Get_ItemDesc_LookupCode(
744 p_parameter_id,
745 p_item_description,
746 p_line_type_lookup_code,
747 l_require_receipt_amount);
748
749 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
750 'end GetItemDescLookupCode');
751
752 END GetItemDescLookupCode;
753
754 /*----------------------------------------------------------------------------*
755 | Procedure |
756 | OASubmitWorkflow |
757 | |
758 | DESCRIPTION |
759 | |
760 | PARAMETERS |
761 | INPUT |
762 | p_report_header_id Number -- Report Header Id |
763 | p_preparer_id Number -- Preparer Id |
764 | p_employee_id Number -- Employee Id |
765 | p_invoice_number Varchar -- Invoice Number |
766 | p_reim_curr Varchar -- Reimbursable Currency Code |
767 | p_cost_center Varchar -- Cost Center |
768 | p_purpose Varchar -- Purpose of receipt |
769 | p_approver_id Number -- Override Approver Id |
770 | p_week_end_date Date -- Week ending date of receipt |
771 | p_workflow_appr_flag Varchar -- Status of workflow; null on submit|
772 | |
773 | RETURNS |
774 | none |
775 *----------------------------------------------------------------------------*/
776 ---------------------------------------------------------------------------
777 PROCEDURE OASubmitWorkflow (p_report_header_id IN VARCHAR2,
778 p_preparer_id IN VARCHAR2,
779 p_employee_id IN VARCHAR2,
780 p_invoice_number IN VARCHAR2,
781 p_reimb_curr IN VARCHAR2,
782 p_cost_center IN VARCHAR2,
783 p_purpose IN VARCHAR2,
784 p_approver_id IN VARCHAR2,
785 p_week_end_date IN DATE, --Bug 3322390
786 p_workflow_appr_flag IN VARCHAR2,
787 p_msg_count OUT NOCOPY NUMBER)
788 ---------------------------------------------------------------------------
789 IS
790 l_debug_info VARCHAR2(300) := '';
791 l_neg_pos_total NUMBER := 0;
792 l_pos_total NUMBER := 0;
793 l_msg_data VARCHAR2(1000);
794 l_errors AP_WEB_UTILITIES_PKG.expError;
795 l_ResubmitReport BOOLEAN := FALSE;
796
797 BEGIN
798 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
799 'start OASubmitWorkflow');
800
801
802 -------------------------------------------------------------------
803 -- Check to see if this is resubmitting a rejected/returned report
804 -- If so then restart existing WF else raise a Submit event
805 -------------------------------------------------------------------
806 l_ResubmitReport := AP_WEB_DB_EXPRPT_PKG.ResubmitExpenseReport(
807 p_workflow_appr_flag);
808
809 IF (TRUE) THEN
810
811 -------------------------------------------------------------------
812 l_debug_info := 'Get the Total of the negative and positive amounts';
813 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
814 -------------------------------------------------------------------
815 BEGIN
816 SELECT
817 SUM(amount),
818 SUM(DECODE(SIGN(amount),-1,0,amount))
819 INTO
820 l_neg_pos_total,
821 l_pos_total
822 FROM AP_EXPENSE_REPORT_LINES_ALL
823 WHERE REPORT_HEADER_ID = p_report_header_id
824 AND (itemization_parent_id is null OR itemization_parent_id <> -1);
825 EXCEPTION
826 WHEN OTHERS THEN
827 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
828 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
829 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'OASubmitWorkflow');
830 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
831 APP_EXCEPTION.RAISE_EXCEPTION;
832 END;
833
834 ------------------------------------------------------------
835 l_debug_info := 'Starting workflow process';
836 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
837 ------------------------------------------------------------
838 AP_WEB_EXPENSE_WF.StartExpenseReportProcess(to_number(p_report_header_id),
839 to_number(p_preparer_id),
840 to_number(p_employee_id),
841 p_invoice_number,
842 to_number(l_neg_pos_total),
843 to_number(l_pos_total),
844 p_reimb_curr,
845 p_cost_center,
846 p_purpose,
847 to_number(p_approver_id),
848 p_week_end_date, -- Bug 3322390
849 p_workflow_appr_flag,
850 p_submit_from_oie => AP_WEB_EXPENSE_WF.C_SUBMIT_FROM_OIE,
851 p_event_raised => 'N');
852 l_debug_info := 'End of workflow process';
853 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
854 -- Do not remove this commit otherwise wf process will not be created.
855 COMMIT;
856 ELSE -- Not a ReSubmit
857
858 ------------------------------------------------------------
859 l_debug_info := 'Starting Expenses WF process via Business Event';
860 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
861 ------------------------------------------------------------
862 AP_WEB_EXPENSE_WF.RaiseSubmitEvent(to_number(p_report_header_id),
863 p_workflow_appr_flag);
864
865 -- Do not remove this commit otherwise wf process will not be created.
866 COMMIT;
867 END IF;
868
869 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
870 'end OASubmitWorkflow');
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 IF (SQLCODE <> -20001) THEN
875 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
876 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
877 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'OASubmitWorkflow');
878 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
879 APP_EXCEPTION.RAISE_EXCEPTION;
880 ELSE
881 -- bug 2203689: caught exception from StartExpenseReprotProcess
882
883 FND_MESSAGE.SET_NAME('SQLAP', 'OIE_WORKFLOW_ERROR');
884 FND_MESSAGE.SET_TOKEN('ERROR_NAME', wf_core.error_name);
885 FND_MESSAGE.SET_TOKEN('ERROR_NUMBER', wf_core.error_number);
886 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE', wf_core.error_message);
887 FND_MESSAGE.SET_TOKEN('ERROR_STACK', wf_core.error_stack);
888
889 -- APP_EXCEPTION.RAISE_EXCEPTION can only display error message with less than
890 -- 512 characters. In order to display the compelte workflow information for bug
891 -- 2203689, call addExpError and checkErrors to get message from error stack
892
893 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
894 fnd_message.get_encoded(),
895 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
896
897 fnd_msg_pub.count_and_get(p_count => p_msg_count,
898 p_data => l_msg_data);
899 END IF;
900
901 END OASubmitWorkflow;
902
903
904 ------------------------------------------------------------------
905 PROCEDURE GetEmployeeInfo(
906 p_employee_id IN NUMBER,
907 p_employee_name OUT NOCOPY VARCHAR2,
908 p_employee_num OUT NOCOPY VARCHAR2,
909 p_cost_center OUT NOCOPY VARCHAR2,
910 p_is_project_enabled OUT NOCOPY VARCHAR2,
911 p_default_reimb_currency_code OUT NOCOPY VARCHAR2,
912 p_is_cc_enabled OUT NOCOPY VARCHAR2,
913 p_max_num_segments OUT NOCOPY NUMBER,
914 p_userId OUT NOCOPY VARCHAR2
915 ) IS
916 -------------------------------------------------------------------
917 l_debug_info VARCHAR2(1000);
918 l_vendor_id AP_WEB_DB_AP_INT_PKG.vendors_vendorID;
919 l_vend_pay_curr AP_WEB_DB_AP_INT_PKG.vendors_paymentCurrCode;
920 l_vend_pay_curr_name AP_WEB_DB_COUNTRY_PKG.curr_name;
921 l_SysInfoRec AP_WEB_DB_AP_INT_PKG.APSysInfoRec;
922 l_base_currency AP_WEB_DB_AP_INT_PKG.apSetUp_baseCurrencyCode;
923 l_sys_multi_curr_flag AP_WEB_DB_AP_INT_PKG.apSetUp_multiCurrencyFlag;
924 l_base_curr_name AP_WEB_DB_COUNTRY_PKG.curr_name;
925 l_has VARCHAR2(1);
926 l_cCardEnabled VARCHAR2(1);
927 l_userId NUMBER;
928 l_reimb_currency_code AP_EXPENSE_REPORT_HEADERS.default_currency_code%TYPE; -- Bug: 5696596
929 l_nonBasePayAllowed VARCHAR2(1);
930
931 Begin
932 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
933 'start GetEmployeeInfo');
934
935 l_debug_info := 'Getting employee information';
936 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
937 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(
938 p_employee_name,
939 p_employee_num,
940 p_cost_center,
941 p_employee_id);
942
943
944 l_debug_info := 'Getting employee user ID';
945 GetUserID(p_employee_id, p_userId);
946 l_userId := to_number(p_userId);
947
948
949 l_debug_info := 'Check if user is project enabled';
950 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
951 AP_WEB_PROJECT_PKG.IsSessionProjectEnabled(p_employee_id,
952 FND_PROFILE.VALUE('USER_ID'),
953 p_is_project_enabled);
954
955 IF p_is_project_enabled = 'Y' THEN
956 -- for bug 2029630
957 -- AP_WEB_PROJECT_PKG.IsSessionProjectEnabled only returns 'Y' or 'N"
958 -- If profile option OIE:Enable Projects equals to Required then
959 -- we need to make sure project information is entered in the
960 -- middle-tier validation
961 p_is_project_enabled := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
962 p_name => 'AP_WEB_ENABLE_PROJECT_ACCOUNTING',
963 p_user_id => l_userId,
964 p_resp_id => null,
965 p_apps_id => null);
966 END IF;
967
968
969 l_debug_info := 'vendor id';
970 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
971 IF (NOT AP_WEB_DB_AP_INT_PKG.GetVendorInfoOfEmp(p_employee_id,
972 l_vendor_id,
973 l_vend_pay_curr,
974 l_vend_pay_curr_name
975 )) THEN
976 l_vendor_id := NULL;
977 l_vend_pay_curr := NULL;
978 l_vend_pay_curr_name := NULL;
979 END IF;
980
981 l_debug_info := 'Select currency information';
982 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
983 IF (AP_WEB_DB_AP_INT_PKG.GetAPSysCurrencySetupInfo(l_SysInfoRec)) THEN
984 l_base_currency := l_SysInfoRec.base_currency;
985 l_sys_multi_curr_flag := l_SysInfoRec.sys_multi_curr_flag;
986 l_base_curr_name := l_SysInfoRec.base_curr_name;
987 END IF;
988
989 p_default_reimb_currency_code := nvl(l_vend_pay_curr, l_base_currency);
990
991 --Bug: 5696596, pickup the default currency from the preferences if there is one.
992 l_nonBasePayAllowed := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
993 p_name => 'AP_WEB_ALLOW_NON_BASE_REIMB',
994 p_user_id => l_userId,
995 p_resp_id => null,
996 p_apps_id => null);
997 IF (l_nonBasePayAllowed = 'Y') THEN
998 -- Cannot use AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC as user can change preferences without logout.
999 l_reimb_currency_code := FND_PROFILE.VALUE_SPECIFIC(
1000 NAME => 'ICX_PREFERRED_CURRENCY',
1001 USER_ID => l_userId,
1002 RESPONSIBILITY_ID => null,
1003 APPLICATION_ID => null);
1004
1005 p_default_reimb_currency_code := nvl(l_reimb_currency_code, p_default_reimb_currency_code);
1006 END IF;
1007
1008
1009 l_debug_info := 'Check if user is credit card enabled';
1010 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
1011 l_cCardEnabled := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
1012 p_name => 'SSE_ENABLE_CREDIT_CARD',
1013 p_user_id => l_userId,
1014 p_resp_id => null,
1015 p_apps_id => null);
1016
1017
1018 IF (AP_WEB_DB_CCARD_PKG.UserHasCreditCard(p_employee_id, l_has) AND
1019 AP_WEB_DB_HR_INT_PKG.IsPersonCwk(p_employee_id) = 'N' AND
1020 l_has = 'Y' AND l_cCardEnabled = 'Y') THEN
1021 p_is_cc_enabled := 'Y';
1022 ELSE
1023 p_is_cc_enabled := 'N';
1024 END IF;
1025
1026 l_debug_info := 'Get Maximum number of flexfield segments';
1027 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_MAINFLOW_PKG', l_debug_info);
1028 p_max_num_segments := AP_WEB_DFLEX_PKG.GetMaxNumSegmentsUsed(TO_NUMBER(p_userId));
1029
1030 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
1031 'end GetEmployeeInfo');
1032 EXCEPTION
1033 WHEN NO_DATA_FOUND THEN
1034 NULL;
1035 WHEN OTHERS THEN
1036 AP_WEB_DB_UTIL_PKG.RaiseException('GetEmloyeeInfo', l_debug_info);
1037 END GetEmployeeInfo;
1038
1039
1040
1041 /*----------------------------------------------------------------------------*
1042 | Procedure |
1043 | GetUserID |
1044 | |
1045 | DESCRIPTION |
1046 | Get the user id base on the passed-in employee id |
1047 | PARAMETERS |
1048 | INPUT |
1049 | p_employee_id VARCHAR2 -- Employee Id |
1050 | p_user_id VARCHAR2 -- User Id that maps to the employee|
1051 | RETURNS |
1052 | none |
1053 *----------------------------------------------------------------------------*/
1054 PROCEDURE GetUserID(p_employee_id IN VARCHAR2,
1055 p_user_id OUT NOCOPY VARCHAR2)
1056 IS
1057 l_FNDUserID AP_WEB_DB_HR_INT_PKG.fndUser_userID;
1058 l_userIdCursor AP_WEB_DB_HR_INT_PKG.UserIdRefCursor;
1059
1060 BEGIN
1061 IF ( AP_WEB_DB_HR_INT_PKG.GetUserIdForEmpCursor(
1062 p_Employee_id,
1063 l_userIdCursor) = TRUE ) THEN
1064 LOOP
1065 FETCH l_userIdCursor INTO
1066 p_user_id;
1067 -- only fetch the first row from the cursor
1068 -- this selected user id will be used to get profile option
1069 -- values.
1070 EXIT;
1071
1072 END LOOP;
1073 CLOSE l_userIdCursor;
1074 END IF;
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 BEGIN
1078 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1079 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1080 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'GetUserID');
1081 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
1082 END;
1083
1084
1085 END GetUserID;
1086
1087 /*----------------------------------------------------------------------------*
1088 | Procedure |
1089 | WithdrawExpenseReport |
1090 | |
1091 | DESCRIPTION |
1092 | Withdraw the expense report from workflow approval |
1093 | bug1552747 |
1094 | PARAMETERS |
1095 | INPUT |
1096 | p_report_header_id NUMBER -- Expense Report Header ID |
1097 | RETURNS |
1098 | none |
1099 *----------------------------------------------------------------------------*/
1100
1101 PROCEDURE WithdrawExpenseReport(
1102 p_report_header_id IN expHdr_headerID)
1103 IS
1104 l_debug_info varchar2(200);
1105 BEGIN
1106
1107 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
1108 'start WithdrawExpenseReport');
1109 l_debug_info := 'Calling WithdrawExpenseRep';
1110 AP_WEB_EXPENSE_WF.WithdrawExpenseRep(p_report_header_id);
1111
1112 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
1113 'end WithdrawExpenseReport');
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 IF (SQLCODE <> -20001) THEN
1117 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1118 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1119 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'WithdrawExpenseReport');
1120 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1121 APP_EXCEPTION.RAISE_EXCEPTION;
1122 ELSE
1123 -- Do not need to set the token since it has been done in the
1124 -- child process
1125 RAISE;
1126 END IF;
1127 END WithdrawExpenseReport;
1128
1129
1130 /*----------------------------------------------------------------------------*
1131 | Procedure |
1132 | GetFunctionalCurrencyInfo |
1133 | |
1134 | DESCRIPTION |
1135 | Get functional currency code and type |
1136 | PARAMETERS |
1137 | INPUT |
1138 | OUTPUT |
1139 | p_currencyCode out nocopy varchar2, |
1140 | p_currencyType out nocopy varchar2 |
1141 *----------------------------------------------------------------------------*/
1142 PROCEDURE GetFunctionalCurrencyInfo(p_currencyCode out nocopy varchar2,
1143 p_currencyType out nocopy varchar2)
1144 IS
1145 l_SysInfoRec AP_WEB_DB_AP_INT_PKG.APSysInfoRec;
1146 BEGIN
1147
1148 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
1149 'start GetFunctionalCurrencyInfo');
1150 p_currencyCode := null;
1151 p_currencyType := null;
1152
1153 IF (AP_WEB_DB_AP_INT_PKG.GetAPSysCurrencySetupInfo(l_SysInfoRec)) THEN
1154 p_currencyType := l_SysInfoRec.default_exchange_rate_type;
1155 p_currencyCode := l_SysInfoRec.base_currency;
1156 END IF; /* GetAPSysCurrencySetupInfo */
1157
1158 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
1159 'end GetFunctionalCurrencyInfo ');
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 APP_EXCEPTION.RAISE_EXCEPTION;
1163
1164 END GetFunctionalCurrencyInfo;
1165
1166
1167 -------------------------------------------------------------------
1168 -- Name: DuplicateExpenseReport
1169 -- Desc: duplicates an Expense Report
1170 -- Input: p_source_report_header_id - source expense report header id
1171 -- Returns: p_target_report_header_id - target expense report header id
1172 -------------------------------------------------------------------
1173 PROCEDURE DuplicateExpenseReport(
1174 p_user_id IN NUMBER,
1175 p_source_report_header_id IN expHdr_headerID,
1176 p_target_report_header_id IN OUT NOCOPY expHdr_headerID) IS
1177
1178 BEGIN
1179
1180 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
1181 'start DuplicateExpenseReport');
1182
1183 select AP_EXPENSE_REPORT_HEADERS_S.NEXTVAL
1184 into p_target_report_header_id
1185 from sys.dual;
1186
1187 AP_WEB_DB_EXPRPT_PKG.DuplicateHeader(p_user_id, p_source_report_header_id, p_target_report_header_id);
1188 AP_WEB_DB_EXPLINE_PKG.DuplicateLines(p_user_id, p_source_report_header_id, p_target_report_header_id);
1189
1190 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
1191 'end DuplicateExpenseReport');
1192
1193 END DuplicateExpenseReport;
1194
1195 /*------------------------------------------------------------+
1196 Created By: Amulya Mishra
1197 Bug 2751642:A wrapper function to get the org_id value from
1198 AP_WEB_DB_HR_INT_PKG.GetEmpOrgId.
1199 Since AP_WEB_DB_HR_INT_PKG.GetEmpOrgId return boolean
1200 it cannot be called directly from java files.
1201 +-------------------------------------------------------------*/
1202 -----------------------------------------------------------------
1203
1204 FUNCTION GetOrgIDFromHR(p_employee_id IN NUMBER,
1205 p_effective_date IN Date)
1206 RETURN NUMBER IS
1207 l_org_id HR_EMPLOYEES_CURRENT_V.ORGANIZATION_ID%TYPE;
1208 BEGIN
1209 IF ( AP_WEB_DB_HR_INT_PKG.GetEmpOrgId(P_Employee_id, p_effective_date, l_org_id) = TRUE ) THEN
1210 return l_org_id;
1211 ELSE
1212 return NULL;
1213 END IF;
1214
1215 END;
1216
1217 -----------------------------------------------------------------------------
1218
1219 ----------------------------------------------------------------------
1220 PROCEDURE GetDefaultAcctgSegValues(
1221 P_REPORT_HEADER_ID IN NUMBER,
1222 P_REPORT_LINE_ID IN NUMBER,
1223 P_OLD_EMPLOYEE_ID IN NUMBER,
1224 P_OLD_HEADER_COST_CENTER IN AP_EXPENSE_REPORT_HEADERS.flex_concatenated%TYPE,
1225 P_OLD_PARAMETER_ID IN NUMBER,
1226 P_NEW_EMPLOYEE_ID IN NUMBER,
1227 P_NEW_HEADER_COST_CENTER IN AP_EXPENSE_REPORT_HEADERS.flex_concatenated%TYPE,
1228 P_NEW_PARAMETER_ID IN NUMBER,
1229 P_SEGMENTS IN AP_OIE_KFF_SEGMENTS_T,
1230 X_SEGMENTS OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
1231 x_combination_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
1232 X_MSG_COUNT OUT NOCOPY NUMBER,
1233 X_MSG_DATA OUT NOCOPY VARCHAR2,
1234 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
1235 ----------------------------------------------------------------------
1236 -- Bug: 7039477, sync error message length with fnd_flex_keyval.err_text
1237 l_return_error_message VARCHAR2(2000);
1238 l_debug_info varchar2(200);
1239 l_old_segments AP_OIE_KFF_SEGMENTS_T;
1240 l_segments AP_OIE_KFF_SEGMENTS_T;
1241 l_ccid NUMBER;
1242
1243 BEGIN
1244
1245 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_MAINFLOW_PKG', 'Start GetDefaultAcctgSegValues');
1246
1247 -- Initialize API return status to success
1248 x_return_status := FND_API.G_RET_STS_SUCCESS;
1249
1250 -- Create local copy of input segments
1251 l_segments := p_segments;
1252
1253 -- Bug 4997339:
1254 -- If old employee id, old header cost center, and old parameter id
1255 -- exist, get old default values to compare with new default values.
1256 IF (
1257 p_old_employee_id IS NOT NULL AND
1258 p_old_header_cost_center IS NOT NULL AND
1259 p_old_parameter_id IS NOT NULL
1260 ) THEN
1261
1262 l_debug_info := 'Call build account to get previously defaulted segments';
1263
1264 AP_WEB_ACCTG_PKG.BuildAccount(
1265 p_report_header_id => p_report_header_id,
1266 p_report_line_id => p_report_line_id,
1267 p_employee_id => p_old_employee_id,
1268 p_cost_center => p_old_header_cost_center,
1269 p_line_cost_center => null,
1270 p_exp_type_parameter_id => p_old_parameter_id,
1271 p_segments => null,
1272 p_ccid => null,
1273 p_build_mode => AP_WEB_ACCTG_PKG.C_DEFAULT,
1274 p_new_segments => l_old_segments,
1275 p_new_ccid => l_ccid,
1276 p_return_error_message => l_return_error_message);
1277
1278 l_debug_info := 'Null out those segments that have not been user over-written';
1279 -- Loop through segments
1280 FOR i IN 1..l_segments.COUNT LOOP
1281 IF l_segments(i) = l_old_segments(i) THEN
1282 l_segments(i) := NULL;
1283 END IF;
1284 END LOOP;
1285 END IF;
1286
1287 l_debug_info := 'Call build account to get new segments';
1288 AP_WEB_ACCTG_PKG.BuildAccount(
1289 p_report_header_id => p_report_header_id,
1290 p_report_line_id => p_report_line_id,
1291 p_employee_id => p_new_employee_id,
1292 p_cost_center => p_new_header_cost_center,
1293 p_line_cost_center => null,
1294 p_exp_type_parameter_id => p_new_parameter_id,
1295 p_segments => l_segments,
1296 p_ccid => null,
1297 p_build_mode => AP_WEB_ACCTG_PKG.C_DEFAULT,
1298 p_new_segments => x_segments,
1299 p_new_ccid => x_combination_id,
1300 p_return_error_message => l_return_error_message);
1301
1302
1303 if (l_return_error_message is not null) then
1304 raise G_EXC_ERROR;
1305 end if;
1306
1307 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_MAINFLOW_PKG', 'end GetDefaultAcctgSegValues');
1308
1309 EXCEPTION
1310 WHEN G_EXC_ERROR THEN
1311 x_return_status := FND_API.G_RET_STS_ERROR;
1312 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1313 p_data => x_msg_data);
1314
1315 WHEN OTHERS THEN
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1318 p_data => x_msg_data);
1319 END GetDefaultAcctgSegValues;
1320
1321 -----------------------------------------------------------------------------
1322 PROCEDURE validateAccountSegments(
1323 P_REPORT_HEADER_ID IN NUMBER,
1324 P_REPORT_LINE_ID IN NUMBER,
1325 P_EMPLOYEE_ID IN NUMBER,
1326 P_SEGMENTS IN AP_OIE_KFF_SEGMENTS_T,
1327 X_SEGMENTS OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
1328 x_combination_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
1329 X_MSG_COUNT OUT NOCOPY NUMBER,
1330 X_MSG_DATA OUT NOCOPY VARCHAR2,
1331 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
1332 ----------------------------------------------------------------------
1333
1334 -- Bug: 7039477, sync error message length with fnd_flex_keyval.err_text
1335 l_return_error_message varchar2(2000);
1336
1337 BEGIN
1338 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_MAINFLOW_PKG', 'Start validateAccountSegments');
1339
1340 -- Initialize API return status to success
1341 x_return_status := FND_API.G_RET_STS_SUCCESS;
1342
1343 -- Initialize message stack
1344 FND_MSG_PUB.initialize;
1345
1346 AP_WEB_ACCTG_PKG.BuildAccount(
1347 p_report_header_id => p_report_header_id,
1348 p_report_line_id => p_report_line_id,
1349 p_employee_id => p_employee_id,
1350 p_cost_center => null,
1351 p_line_cost_center => null,
1352 p_exp_type_parameter_id => null,
1353 p_segments => p_segments,
1354 p_ccid => null,
1355 p_build_mode => AP_WEB_ACCTG_PKG.C_VALIDATE,
1356 p_new_segments => x_segments,
1357 p_new_ccid => x_combination_id,
1358 p_return_error_message => l_return_error_message);
1359
1360 if (l_return_error_message is not null) then
1361 raise G_EXC_ERROR;
1362 end if;
1363
1364 EXCEPTION
1365 WHEN G_EXC_ERROR THEN
1366 x_return_status := FND_API.G_RET_STS_ERROR;
1367 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1368 p_data => x_msg_data);
1369
1370 WHEN OTHERS THEN
1371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1372 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1373 p_data => x_msg_data);
1374
1375 END validateAccountSegments;
1376
1377 -----------------------------------------------------------------------------
1378 PROCEDURE rebuildAccountSegments(
1379 P_REPORT_HEADER_ID IN NUMBER,
1380 P_REPORT_LINE_ID IN NUMBER,
1381 P_EMPLOYEE_ID IN NUMBER,
1382 P_SEGMENTS IN AP_OIE_KFF_SEGMENTS_T,
1383 X_SEGMENTS OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
1384 x_combination_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
1385 X_MSG_COUNT OUT NOCOPY NUMBER,
1386 X_MSG_DATA OUT NOCOPY VARCHAR2,
1387 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
1388 ----------------------------------------------------------------------
1389
1390 -- Bug: 7039477, sync error message length with fnd_flex_keyval.err_text
1391 l_return_error_message varchar2(2000);
1392
1393 BEGIN
1394 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_MAINFLOW_PKG', 'Start rebuildAccountSegments');
1395
1396 -- Initialize API return status to success
1397 x_return_status := FND_API.G_RET_STS_SUCCESS;
1398
1399 AP_WEB_ACCTG_PKG.BuildAccount(
1400 p_report_header_id => p_report_header_id,
1401 p_report_line_id => p_report_line_id,
1402 p_employee_id => p_employee_id,
1403 p_cost_center => null,
1404 p_line_cost_center => null,
1405 p_exp_type_parameter_id => null,
1406 p_segments => p_segments,
1407 p_ccid => null,
1408 p_build_mode => AP_WEB_ACCTG_PKG.C_CUSTOM_BUILD_ONLY,
1409 p_new_segments => x_segments,
1410 p_new_ccid => x_combination_id,
1411 p_return_error_message => l_return_error_message);
1412
1413 if (l_return_error_message is not null) then
1414 raise G_EXC_ERROR;
1415 end if;
1416
1417 EXCEPTION
1418 WHEN G_EXC_ERROR THEN
1419 x_return_status := FND_API.G_RET_STS_ERROR;
1420 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1421 p_data => x_msg_data);
1422
1423 WHEN OTHERS THEN
1424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1425 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1426 p_data => x_msg_data);
1427
1428 END rebuildAccountSegments;
1429
1430
1431 PROCEDURE updateExpensedAmount(
1432 p_trxIds IN AP_WEB_PARENT_PKG.Number_Array,
1433 p_expensedAmt IN AP_WEB_PARENT_PKG.Number_Array,
1434 p_reportId IN AP_CREDIT_CARD_TRXNS.report_header_id%TYPE
1435 ) IS
1436 l_numCharges number := 0;
1437 l_idArray AP_WEB_PARENT_PKG.Number_Array;
1438 l_amtArray AP_WEB_PARENT_PKG.number_array;
1439 l_foundArray AP_WEB_PARENT_PKG.boolean_array;
1440 l_debugInfo varchar2(240);
1441 l_exp_amount AP_WEB_DB_CCARD_PKG.ccTrxn_expensedAmt;
1442 l_trxn_id AP_WEB_DB_CCARD_PKG.ccTrxn_trxID;
1443
1444 BEGIN
1445 l_debugInfo := 'Combine all receipts of the same charge';
1446 for i in 1..p_trxIds.count loop
1447 l_foundArray(i) := false;
1448 end loop;
1449
1450 for i in 1..p_trxIds.count loop
1451 if (not(l_foundArray(i)) and p_trxIds(i) is not null) then
1452 l_numCharges := l_numCharges + 1;
1453 l_idArray(l_numCharges) := p_trxIds(i);
1454 l_amtArray(l_numCharges) := p_expensedAmt(i);
1455 -- look for same charge
1456 for j in (i+1)..p_trxIds.count loop
1457 if (not(l_foundArray(j)) AND (p_trxIds(j) = l_idArray(l_numCharges))) then
1458 l_amtArray(l_numCharges) := l_amtArray(l_numCharges) + p_expensedAmt(j);
1459 l_foundArray(j) := true;
1460 end if;
1461 end loop;
1462 end if;
1463 end loop;
1464
1465 l_debugInfo := 'Update the credit card interface table';
1466 for i in 1..l_idArray.count loop
1467 l_trxn_id := l_idArray(i);
1468 l_exp_amount := l_amtArray(i);
1469
1470 IF ( NOT AP_WEB_DB_CCARD_PKG.UpdateExpensedAmount(
1471 l_trxn_id,
1472 p_reportId,
1473 l_exp_amount) ) THEN
1474 NULL;
1475 END IF;
1476 end loop;
1477 commit;
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 BEGIN
1482 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1483 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1484 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'updateExpensedAmount');
1485 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debugInfo);
1486 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
1487 END;
1488 END updateExpensedAmount;
1489
1490 /*
1491 Written by:
1492 Quan Le
1493 Purpose:
1494 To update charges used in an Expense Report that is about to be deleted. Specifically,
1495 it will credit the receipt amount back to the Expensed_Amount field of the corresponding
1496 charge.
1497 Input:
1498 p_id_Array : table of credit card transaction ids
1499 p_amtArray: table of expensed amount corresponding to p_idArray
1500 Output:
1501 None
1502 Input Output:
1503 None
1504 Assumption:
1505 None
1506 Date:
1507 11/19/99
1508 */
1509 PROCEDURE updChargesFromDeletedReport(p_idArray in AP_WEB_PARENT_PKG.number_Array,
1510 p_amtArray in AP_WEB_PARENT_PKG.number_Array)
1511 IS
1512 l_idArray AP_WEB_PARENT_PKG.number_Array;
1513 l_amtArray AP_WEB_PARENT_PKG.number_Array;
1514 l_foundArray AP_WEB_PARENT_PKG.boolean_Array;
1515 l_id number;
1516 l_numCharges number := 0;
1517 l_temp number;
1518 l_debugInfo varchar2(240);
1519
1520 BEGIN
1521 l_debugInfo := 'Combine all receipts of the same charge';
1522 for i in 1..p_idArray.count loop
1523 l_foundArray(i) := false;
1524 end loop;
1525
1526
1527 for i in 1..p_idArray.count loop
1528 if (not(l_foundArray(i)) and p_idArray(i) is not null) then
1529 l_numCharges := l_numCharges + 1;
1530 l_idArray(l_numCharges) := p_idArray(i);
1531 l_amtArray(l_numCharges) := p_amtArray(i);
1532 -- look for same charge
1533 for j in (i+1)..p_idArray.count loop
1534 if (not(l_foundArray(j)) AND (p_idArray(j) = l_idArray(l_numCharges))) then
1535 l_amtArray(l_numCharges) := l_amtArray(l_numCharges) + p_amtArray(j);
1536 l_foundArray(j) := true;
1537 end if;
1538 end loop;
1539 end if;
1540 end loop;
1541
1542 for i in 1.. l_idArray.count loop
1543 l_debugInfo := 'Get the existing expensed amount';
1544 if (NOT AP_WEB_DB_CCARD_PKG.GetExpensedAmountForTrxnId(l_idArray(i), l_temp)) then
1545 raise NO_DATA_FOUND;
1546 end if;
1547 l_amtArray(i) := l_temp - l_amtArray(i);
1548 end loop;
1549
1550 updateExpensedAmount(l_idArray, l_amtArray, null);
1551
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 BEGIN
1555 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1556 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1557 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'updChargesFromDeletedReport');
1558 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debugInfo);
1559 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
1560 END;
1561 END updChargesFromDeletedReport;
1562
1563 PROCEDURE DeleteReport(
1564 ReportID IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE)
1565 IS
1566 l_DebugInfo VARCHAR2(200);
1567 l_count NUMBER;
1568 l_idArray AP_WEB_PARENT_PKG.number_Array;
1569 l_amtArray AP_WEB_PARENT_PKG.number_Array;
1570 l_ReportLines AP_WEB_DB_EXPLINE_PKG.CCTrxnCursor;
1571 l_WkflRec AP_WEB_DB_EXPRPT_PKG.ExpWorkflowRec;
1572 BEGIN
1573 l_DebugInfo := 'Get all the ids and amounts of credit card receipts';
1574
1575 IF (AP_WEB_DB_EXPLINE_PKG.GetTrxIdsAndAmtsCursor(ReportID, l_ReportLines)) THEN
1576 l_count := 1;
1577 LOOP
1578 FETCH l_ReportLines INTO l_idArray(l_count), l_amtArray(l_count);
1579 EXIT WHEN l_ReportLines%NOTFOUND;
1580 l_count := l_count+1;
1581 END LOOP;
1582 END IF;
1583 CLOSE l_ReportLines;
1584
1585 -- Abort workflow process if report has been previously rejected
1586 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetExpWorkflowInfo(ReportID,l_WkflRec)) THEN
1587 l_WkflRec.workflow_flag := NULL;
1588 END IF;
1589
1590 IF AP_WEB_DB_EXPRPT_PKG.ResubmitExpenseReport(l_WkflRec.workflow_flag) THEN
1591 begin
1592 WF_ENGINE.AbortProcess('APEXP', ReportID);
1593 exception
1594 when others then null;
1595 end;
1596 END IF;
1597
1598
1599 l_DebugInfo := 'Delete report header';
1600 IF (NOT AP_WEB_DB_EXPRPT_PKG.DeleteReportHeaderAtDate(ReportID)) THEN
1601 raise NO_DATA_FOUND;
1602 END IF;
1603
1604 l_DebugInfo := 'Delete report lines';
1605 IF (NOT AP_WEB_DB_EXPLINE_PKG.DeleteReportLines(ReportID))THEN
1606 raise NO_DATA_FOUND;
1607 END IF;
1608
1609 l_DebugInfo := 'update credit card charges';
1610 if (l_idArray.count > 0) then
1611 updChargesFromDeletedReport(l_idArray, l_amtArray);
1612 end if;
1613
1614 l_DebugInfo := 'Delete violations';
1615 AP_WEB_DB_VIOLATIONS_PKG.deleteViolationEntry(ReportID);
1616
1617 -- Commit deletion
1618 -- We will have a lock on all rows in the cursors from the time the cursor
1619 -- is opened until the commit.
1620 COMMIT;
1621 EXCEPTION
1622 WHEN OTHERS THEN
1623 ROLLBACK;
1624 APP_EXCEPTION.RAISE_EXCEPTION;
1625 END DeleteReport;
1626
1627
1628
1629 END AP_WEB_OA_MAINFLOW_PKG;