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