[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_VALIDATE_UTIL
Source
1 PACKAGE BODY AP_WEB_VALIDATE_UTIL AS
2 /* $Header: apwvutlb.pls 120.34.12010000.3 2009/05/12 06:57:18 meesubra ship $ */
3
4 /* The prompt index are relative to AP_WEB_EXP_VIEW_REC */
5 C_Date1_Prompt CONSTANT varchar2(3) := '6';
6 C_Date2_Prompt CONSTANT varchar2(3) := '7';
7 C_Days_Prompt CONSTANT varchar2(3) := '8';
8 C_DAmount_Prompt CONSTANT varchar2(3) := '9';
9 C_Amount_Prompt CONSTANT varchar2(3) := '23';
10 C_Exptype_Prompt CONSTANT varchar2(3) := '11';
11 C_Just_Prompt CONSTANT varchar2(3) := '12';
12 C_Grp_Prompt CONSTANT varchar2(3) := '24';
13 C_Missing_Prompt CONSTANT varchar2(3) := '30';
14 C_RecAmt_Prompt CONSTANT varchar2(3) := '10';
15 C_Rate_Prompt CONSTANT varchar2(3) := '22';
16 C_TaxName_Prompt CONSTANT varchar2(3) := '33';
17
18 C_RateFormat CONSTANT VARCHAR2(15) := '9999990D9999999';
19
20 -- Used in WithinTolerance to compare amounts
21 C_Tolerance CONSTANT NUMBER := .01;
22
23 C_Yes CONSTANT VARCHAR2(1) := 'Y';
24 C_No CONSTANT VARCHAR2(1) := 'N';
25
26 -- Indicates Valid dates after this date only
27 C_MinimumYear CONSTANT NUMBER := 1900;
28
29 -- Justification array: to be initialized when the package is instantiated
30 C_justreq_array AP_WEB_PARENT_PKG.Number_Array;
31
32 FUNCTION WithinTolerance(P_actual number,
33 P_target number) RETURN BOOLEAN;
34
35
36
37
38 Function CheckNum
39 (p_num in number,
40 p_errors in out nocopy AP_WEB_UTILITIES_PKG.expError,
41 p_index in number,
42 p_prompt in varchar2,
43 p_allow_negative in boolean default TRUE,
44 p_absolute in boolean default FALSE) Return Boolean IS
45
46 l_IsMobileApp boolean;
47
48 BEGIN
49
50 l_IsMobileApp := AP_WEB_UTILITIES_PKG.IsMobileApp;
51
52 if (p_allow_negative) then
53 return TRUE;
54 else
55 if (((p_num < 0) AND (NOT p_absolute)) OR
56 ((p_num <= 0) AND (p_absolute))) then
57 fnd_message.set_name('SQLAP', 'AP_WEB_NOT_POS_NUM');
58 fnd_message.set_token('VALUE', to_char(p_num));
59 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
60 fnd_message.get_encoded(),
61 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
62 p_prompt,
63 p_index,
64 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
65 return false;
66 end if; /* p_num < 0 */
67 end if;
68 return true;
69 END CheckNum;
70
71 Function CheckPosNum
72 (p_num in number,
73 p_receipt_errors in out nocopy AP_WEB_UTILITIES_PKG.receipt_error_stack,
74 p_index in number,
75 p_prompt in varchar2,
76 p_absolute in boolean default FALSE) Return Boolean IS
77 BEGIN
78 if (((p_num < 0) AND (NOT p_absolute)) OR
79 ((p_num <= 0) AND (p_absolute))) then
80 fnd_message.set_name('SQLAP', 'AP_WEB_NOT_POS_NUM');
81 fnd_message.set_token('VALUE', to_char(p_num));
82 AP_WEB_UTILITIES_PKG.AddMessage(p_receipt_errors,
83 p_index,
84 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
85 fnd_message.get_encoded(),
86 p_prompt);
87
88 return false;
89 end if; /* p_num < 0 */
90 return true;
91 END CheckPosNum;
92
93 /*--------------------------------------------------------------*
94 | Function |
95 | IsValidYear |
96 | |
97 | DESCRIPTION |
98 | Checks whether the given date has a valid year or not. |
99 | |
100 | ASSUMPTION |
101 | The given date is a valid date. |
102 | PARAMETERS |
103 | P_Date IN |
104 | RETURNS |
105 | Boolean indicating whether the year is valid or not. |
106 *--------------------------------------------------------------*/
107 FUNCTION IsValidYear(P_Date IN DATE) RETURN BOOLEAN
108 IS
109 V_Year NUMBER;
110 BEGIN
111 V_Year := to_number(to_char(P_Date, 'SYYYY'));
112
113 IF V_Year < C_MinimumYear THEN
114 return FALSE;
115 END IF;
116
117 return TRUE;
118 EXCEPTION
119 when others then
120 return FALSE;
121 END IsValidYear;
122
123
124 FUNCTION IsValidDate(P_DateStr IN VARCHAR2,
125 P_DateFormat IN VARCHAR2) RETURN BOOLEAN
126 IS
127 V_Temp DATE;
128 BEGIN
129 V_Temp := to_date(P_DateStr, P_DateFormat);
130 return TRUE;
131 EXCEPTION
132 when others then
133 return FALSE;
134 END IsValidDate;
135
136 /*----------------------------------------------------------------------------*
137 | Procedure |
138 | ValidateExpLinesCustomFields |
139 | |
140 | DESCRIPTION |
141 | Server-Side validation for multiple receipt lines custom fields |
142 | |
143 | ASSUMPTION |
144 | Currently this procedure only be used by AP_WEB_SUBMIT_PKG.SaveOrSubmit
145 | for Blue-Gray UI |
146 | PARAMETERS |
147 | |
148 | RETURNS |
149 | none |
150 *----------------------------------------------------------------------------*/
151 PROCEDURE ValidateExpLinesCustomFields(
152 p_report_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
153 p_report_lines_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
154 p_custom1_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
155 p_custom2_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
156 p_custom3_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
157 p_custom4_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
158 p_custom5_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
159 p_custom6_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
160 p_custom7_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
161 p_custom8_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
162 p_custom9_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
163 p_custom10_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
164 p_custom11_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
165 p_custom12_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
166 p_custom13_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
167 p_custom14_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
168 p_custom15_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
169 p_num_max_flex_field IN NUMBER,
170 P_IsSessionTaxEnabled IN VARCHAR2,
171 P_IsSessionProjectEnabled IN VARCHAR2,
172 p_receipts_count IN BINARY_INTEGER,
173 p_receipt_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack,
174 p_receipts_with_errors_count IN OUT NOCOPY BINARY_INTEGER,
175 p_calculate_receipt_index IN BINARY_INTEGER,
176 p_error IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError,
177 p_addon_rates IN OIE_ADDON_RATES_T DEFAULT NuLL,
178 p_report_line_id IN NUMBER DEFAULT NULL,
179 p_daily_breakup_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
180 p_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
181 p_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
182 p_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
183 p_number_of_meals IN OIE_PDM_NUMBER_T DEFAULT NULL,
184 p_meals_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
185 p_breakfast_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
186 p_lunch_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
187 p_dinner_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
188 p_accommodation_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
189 p_accommodation_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
190 p_hotel_name IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
191 p_night_rate_Type IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
192 p_night_rate_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
193 p_pdm_rate IN OIE_PDM_NUMBER_T DEFAULT NULL,
194 p_rate_Type_code IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
195 p_pdm_breakup_dest_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
196 p_pdm_destination_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
197 p_dest_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
198 p_dest_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
199 p_location_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
200 p_cust_meals_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
201 p_cust_accommodation_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
202 p_cust_night_rate_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
203 p_cust_pdm_rate IN OUT NOCOPY OIE_PDM_NUMBER_T
204 )
205 ----------------------------------------------------------------------------
206 IS
207
208 l_receipt_index BINARY_INTEGER := p_calculate_receipt_index;
209 l_debug_info VARCHAR2(2000);
210
211 i INTEGER;
212
213 V_SysInfoRec AP_WEB_DB_AP_INT_PKG.APSysInfoRec; -- For PATC: Exchange rate type in AP and Functional currency
214 V_EndExpenseDate DATE; -- For PATC: Latest receipt date
215 V_DefaultExchangeRate NUMBER; -- For PATC: Exchange rate for func->reimb
216 V_DateTemp DATE; -- For PATC: Scratch variable
217 V_DateFormat VARCHAR2(30);
218
219 l_IsMobileApp boolean;
220 l_DataDefaultedUpdateable BOOLEAN;
221 l_vendor_id AP_WEB_DB_AP_INT_PKG.vendors_vendorID;
222 l_vend_pay_curr AP_WEB_DB_AP_INT_PKG.vendors_paymentCurrCode;
223 l_vend_pay_curr_name AP_WEB_DB_COUNTRY_PKG.curr_name;
224
225 BEGIN
226
227 l_IsMobileApp := AP_WEB_UTILITIES_PKG.IsMobileApp;
228
229 IF (p_receipts_count = 0) THEN
230 RETURN;
231 END IF;
232 p_receipts_with_errors_count := 0;
233
234 -- The following calcuations marked with "For PATC" were
235 -- added for the R11i support for multicurrency in PA.
236 -- We need to retrieve currency and exchange rate information
237 -- before calling PATC.
238
239 -- For PATC: Used when doing projects verification
240 --Bug 3336823
241 V_DateFormat := nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'));
242
243
244 -- For PATC: Determine the time-wise last receipt to use as the
245 -- exchange rate date
246 -- Assumes has at least one receipt
247 l_debug_info := 'Getting latest date in report'||V_DateFormat;
248 V_EndExpenseDate := to_date(p_report_lines_info(1).start_date, V_DateFormat);
249
250 FOR i IN 1 .. P_Receipts_Count LOOP
251 V_DateTemp := to_date(p_report_lines_info(i).start_date, V_DateFormat);
252 if (V_EndExpenseDate < V_DateTemp) then
253 V_EndExpenseDate := V_DateTemp;
254 end if;
255
256 if (p_report_lines_info(i).end_date IS NOT NULL) then
257 l_debug_info := 'Getting end_date';
258 V_DateTemp := to_date(p_report_lines_info(i).end_date, V_DateFormat);
259 if (V_EndExpenseDate < V_DateTemp) then
260 V_EndExpenseDate := V_DateTemp;
261 end if;
262 end if;
263
264 END LOOP;
265
266 -- For PATC: Get information about functional currency and exchange
267 -- rate for the last receipt date. The last receipt date will be
268 -- equal to sysdate.
269 L_debug_info := 'Getting functional currency and exchange rate info';
270
271 IF (NOT AP_WEB_DB_AP_INT_PKG.GetAPSysCurrencySetupInfo(V_SysInfoRec)) THEN
272 NULL;
273 END IF;
274
275 IF (NOT AP_WEB_DB_AP_INT_PKG.GetVendorInfoOfEmp(p_report_header_info.employee_id,
276 l_vendor_id,
277 l_vend_pay_curr,
278 l_vend_pay_curr_name
279 )) THEN
280 l_vendor_id := NULL;
281 l_vend_pay_curr := NULL;
282 l_vend_pay_curr_name := NULL;
283 END IF;
284
285 -- For PATC: Get the default exchange rate for the V_EndExpenseDate
286 -- reimbursement currency/functional currency
287 -- We are only calling this once for all receipts
288 V_DefaultExchangeRate := AP_UTILITIES_PKG.get_exchange_rate(
289 nvl(l_vend_pay_curr, V_SysInfoRec.base_currency),
290 p_report_header_info.reimbursement_currency_code,
291 V_SysInfoRec.default_exchange_rate_type,
292 V_EndExpenseDate,
293 'ValidatePATransaction');
294
295 p_report_header_info.number_max_flexfield := p_num_max_flex_field;
296
297 l_debug_info := 'Validate custom flexfields';
298 -- Bug Fix 2280687 : Do not validate all lines when we select Calculate Amount
299 IF ((p_calculate_receipt_index is not null) and (p_calculate_receipt_index > 0)) THEN
300 p_report_lines_info(p_calculate_receipt_index).receipt_index := p_calculate_receipt_index;
301 ValidateExpLineCustomFields(
302 null,
303 p_report_header_info,
304 p_report_lines_info(p_calculate_receipt_index),
305 l_receipt_index,
306 V_SysInfoRec,
307 V_DefaultExchangeRate,
308 V_EndExpenseDate,
309 V_DateFormat,
310 p_custom1_array,
311 p_custom2_array,
312 p_custom3_array,
313 p_custom4_array,
314 p_custom5_array,
315 p_custom6_array,
316 p_custom7_array,
317 p_custom8_array,
318 p_custom9_array,
319 p_custom10_array,
320 p_custom11_array,
321 p_custom12_array,
322 p_custom13_array,
323 p_custom14_array,
324 p_custom15_array,
325 P_IsSessionTaxEnabled,
326 P_IsSessionProjectEnabled,
327 p_receipt_errors,
328 p_calculate_receipt_index,
329 p_error,
330 p_receipts_with_errors_count,
331 l_DataDefaultedUpdateable,
332 FALSE,
333 false,
334 p_addon_rates,
335 p_report_line_id,
336 p_daily_breakup_id,
337 p_start_date,
338 p_end_date,
339 p_amount,
340 p_number_of_meals,
341 p_meals_amount,
342 p_breakfast_flag,
343 p_lunch_flag,
344 p_dinner_flag,
345 p_accommodation_amount,
346 p_accommodation_flag,
347 p_hotel_name,
348 p_night_rate_Type,
349 p_night_rate_amount,
350 p_pdm_rate,
351 p_rate_Type_code,
352 p_pdm_breakup_dest_id,
353 p_pdm_destination_id,
354 p_dest_start_date,
355 p_dest_end_date,
356 p_location_id,
357 p_cust_meals_amount,
358 p_cust_accommodation_amount,
359 p_cust_night_rate_amount,
360 p_cust_pdm_rate
361 );
362 ELSE
363 FOR l_receipt_index in 1 .. p_receipts_count LOOP
364 p_report_lines_info(l_receipt_index).receipt_index := l_receipt_index;
365 ValidateExpLineCustomFields(
366 null,
367 p_report_header_info,
368 p_report_lines_info(l_receipt_index),
369 l_receipt_index,
370 V_SysInfoRec,
371 V_DefaultExchangeRate,
372 V_EndExpenseDate,
373 V_DateFormat,
374 p_custom1_array,
375 p_custom2_array,
376 p_custom3_array,
377 p_custom4_array,
378 p_custom5_array,
379 p_custom6_array,
380 p_custom7_array,
381 p_custom8_array,
382 p_custom9_array,
383 p_custom10_array,
384 p_custom11_array,
385 p_custom12_array,
386 p_custom13_array,
387 p_custom14_array,
388 p_custom15_array,
389 P_IsSessionTaxEnabled,
390 P_IsSessionProjectEnabled,
391 p_receipt_errors,
392 p_calculate_receipt_index,
393 p_error,
394 p_receipts_with_errors_count,
395 l_DataDefaultedUpdateable,
396 FALSE,
397 TRUE, -- for Blue Gray UI,
398 p_addon_rates,
399 p_report_line_id,
400 p_daily_breakup_id,
401 p_start_date,
402 p_end_date,
403 p_amount,
404 p_number_of_meals,
405 p_meals_amount,
406 p_breakfast_flag,
407 p_lunch_flag,
408 p_dinner_flag,
409 p_accommodation_amount,
410 p_accommodation_flag,
411 p_hotel_name,
412 p_night_rate_Type,
413 p_night_rate_amount,
414 p_pdm_rate,
415 p_rate_Type_code,
416 p_pdm_breakup_dest_id,
417 p_pdm_destination_id,
418 p_dest_start_date,
419 p_dest_end_date,
420 p_location_id,
421 p_cust_meals_amount,
422 p_cust_accommodation_amount,
423 p_cust_night_rate_amount,
424 p_cust_pdm_rate
425 );
426
427 END LOOP;
428 END IF;
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 BEGIN
433 IF (SQLCODE <> -20001) THEN
434 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
435 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
436 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
437 'ValidateExpLinesCustomFields');
438 FND_MESSAGE.SET_TOKEN('PARAMETERS',
439 'None passed.');
440 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
441 END IF;
442
443 APP_EXCEPTION.RAISE_EXCEPTION;
444 END;
445 END ValidateExpLinesCustomFields;
446
447 PROCEDURE ValidateApprover(p_employee_id in varchar2,
448 p_approverName in out nocopy varchar2,
449 p_approverID in out nocopy varchar2,
450 p_ap_error out nocopy varchar2
451 ) IS
452 l_approvercount NUMBER; -- Bug 709879
453 l_approverid AP_WEB_DB_HR_INT_PKG.empCurrent_employeeID;
454 l_approverfullname AP_WEB_DB_HR_INT_PKG.empCurrent_fullName; -- Bug 709879
455 l_upper_approver_name_exact AP_WEB_DB_HR_INT_PKG.empCurrent_fullName; -- Bug 709879
456 l_upper_approver_name_fuzzy AP_WEB_DB_HR_INT_PKG.empCurrent_fullName;
457 l_paren_position NUMBER;
458 debug_info VARCHAR2(2000);
459 l_approvers_cursor AP_WEB_DB_HR_INT_PKG.EmpInfoCursor;
460
461 BEGIN
462 p_approverName := ltrim(p_approverName);
463 -- If the p_approverID argument is not null, then we are assuming that the
464 -- approver name was (1) derived using an LOV or (2) restored from a report
465 -- where the approver ID was already determined.
466 -- In those scenarios, it is not necessary to derive the override approver name or ID.
467 IF (p_approverID IS NOT NULL) THEN
468
469 -- Store the current p_approverID
470 l_approverID := p_approverID;
471
472 ELSE
473
474 -- Approver ID and name is not yet known
475 l_approvercount := 0;
476 p_approverID := null;
477 p_ap_error := null;
478
479 --Bug 2502624. Removed the trimming of the approverName to the first occurance
480 --of '('.Terminating the employee name at the first '(' might result
481 -- in duplicate employees being found.
482
483 l_upper_approver_name_exact := UPPER(p_approverName);
484
485 -- Bug 1363739, Added the condition to prevent performing validation
486 -- if the value is null
487 IF l_upper_approver_name_exact is null THEN
488 return;
489 END IF;
490
491 l_upper_approver_name_fuzzy := l_upper_approver_name_exact || '%';
492
493 --
494 -- If 3rd party case, the person for whom the expense report is prepared
495 -- for AND the preparer cannot be the Overriding Approver
496 --
497
498 BEGIN
499 -- Bug 709879
500 -- Problem: exact search on approver fullname would always result
501 -- in too many approvers found if another approver's partial
502 -- fullname was like the exact one being searched on due to fuzzy
503 -- Solution: use a cursor for fuzzy search and check for exact match
504 -- Caveat: Does not handle case where 2+ employees have exact same
505 -- fullname; 1st employee found with exact fullname match wins
506 IF (AP_WEB_DB_HR_INT_PKG.GetEmployeeInfoCursor(p_approverName,
507 l_upper_approver_name_fuzzy, l_approvers_cursor)) THEN
508 FETCH l_approvers_cursor
509 INTO l_approverid, l_approverfullname;
510 WHILE l_approvers_cursor%FOUND LOOP
511 l_approvercount := l_approvercount + 1;
512
513 /* Removing this check because we want to alert the user that there
514 are other full names that roughly match.
515 -- Bug 1363739, Added upper as l_upper_approver_name_exact is
516 -- already in upper and the fetch would return what is in the DB
517 IF (UPPER(l_approverfullname) = l_upper_approver_name_exact) THEN
518 EXIT;
519 END IF;
520 */
521
522 -- Bug 1363739, If a partial value is entered for approver and
523 -- there are say 1000 matches found, then we need not loop for
524 -- all thousand instead exit if there are more than 2 matches
525 IF l_approvercount > 2 THEN
526 EXIT;
527 END IF;
528
529 FETCH l_approvers_cursor INTO l_approverid, l_approverfullname;
530 END LOOP;
531 END IF;
532 CLOSE l_approvers_cursor;
533
534 if (l_approvercount > 1) then
535 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_TOO_MANY_APPROVERS');
536 p_ap_error:= fnd_message.get_encoded();
537 RETURN;
538 elsif (l_approvercount = 0) then
539 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_FOUND_NO_APPROVERS');
540 p_ap_error := fnd_message.get_encoded();
541 RETURN;
542 end if;
543
544 EXCEPTION
545 when TOO_MANY_ROWS then
546 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_TOO_MANY_APPROVERS');
547 p_ap_error:= fnd_message.get_encoded();
548 RETURN;
549 when NO_DATA_FOUND then
550 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_FOUND_NO_APPROVERS');
551 p_ap_error := fnd_message.get_encoded();
552 RETURN;
553 WHEN OTHERS THEN
554 IF (SQLCODE <> -20001) THEN
555 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
556 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
557 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','ValidateApprover');
558 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
559 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
560 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get_encoded());
561 END IF;
562 END;
563 END IF; -- IF (p_approverID IS NOT NULL) THEN
564
565 -- Bug 711957/669360
566 -- preparer and employee cannot be the approver
567 -- and hr.employee_id != to_number(p_employee_id);
568 -- Bug 3198236 : corrected error messages
569 if (l_ApproverID = AP_WEB_DB_HR_INT_PKG.getEmployeeID) then
570 fnd_message.set_name('SQLAP', 'AP_WEB_PREP_CANNOT_APPROVE');
571 p_ap_error := fnd_message.get_encoded();
572 elsif (l_ApproverID = to_number(p_employee_id)) then
573 fnd_message.set_name('SQLAP', 'AP_WEB_EMP_CANNOT_APPROVE');
574 p_ap_error := fnd_message.get_encoded();
575 else
576 -- Assign the p_approverID only if there are no errors
577 p_approverID := to_char(l_approverid);
578 p_approverName := l_approverfullname;
579 end if;
580 END ValidateApprover;
581
582 PROCEDURE ValidateCostCenter(p_costcenter IN AP_EXPENSE_FEED_DISTS.cost_center%TYPE,
583 p_cs_error OUT NOCOPY varchar2,
584 p_employee_id IN NUMBER) IS
585 p_CostCenterValid boolean := FALSE;
586 l_customError varchar2(2000);
587
588 l_CostCenterValid BOOLEAN := FALSE;
589 l_IsMobileApp BOOLEAN := FALSE;
590 l_error_message VARCHAR2(2000);
591 l_default_emp_segments AP_OIE_KFF_SEGMENTS_T;
592 l_employee_ccid AP_WEB_DB_EXPRPT_PKG.expHdr_employeeCCID;
593
594
595 BEGIN
596
597
598 l_IsMobileApp := AP_WEB_UTILITIES_PKG.IsMobileApp;
599
600 --
601 -- Call custom cost center validation API
602 --
603 if (AP_WEB_CUST_DFLEX_PKG.CustomValidateCostCenter(
604 l_customError,
605 p_costcenter,
606 p_CostCenterValid,
607 p_employee_id)) then
608 --
609 -- Custom validation API returned TRUE; therefore custom validation
610 -- is used in lieu of native cost center validation
611 --
612 if (p_CostCenterValid) then
613 --
614 -- If custom validation succeeds, clear the error text
615 --
616 p_cs_error := null;
617 else
618 --
619 -- Custom validation failed; supply standard failure message if
620 -- custom error message is null
621 --
622 if (l_customError is null) then
623 if (not l_IsMobileApp) then
624 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_COST_CENTER_INVALID');
625 else
626 FND_MESSAGE.SET_NAME('SQLAP','AP_OME_COST_CENTER_ERROR');
627 end if;
628 p_cs_error:= fnd_message.get_encoded();
629 else
630 p_cs_error := l_customError;
631 end if;
632 end if;
633 else
634 --
635 -- Custom validation API returned FALSE; therefore we validate using
636 -- the cursor declared above.
637 --
638 IF (NOT AP_WEB_DB_AP_INT_PKG.CostCenterValid(p_costCenter,
639 l_CostCenterValid,
640 p_employee_id)) THEN
641 NULL;
642 END IF;
643
644 if (NOT l_CostCenterValid) then
645 --
646 -- Failed; set standard failure message.
647 --
648 if (not l_IsMobileApp) then
649 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_COST_CENTER_INVALID');
650 else
651 FND_MESSAGE.SET_NAME('SQLAP','AP_OME_COST_CENTER_ERROR');
652 end if;
653 p_cs_error:= fnd_message.get_encoded();
654 end if;
655 end if;
656
657 -- Bug: 5161664, Default Expense Account Validation.
658 -- Same code is called from General Information and Spreadsheet upload
659 IF (p_CostCenterValid OR l_CostCenterValid) THEN
660 AP_WEB_ACCTG_PKG.BuildAccount(
661 p_report_header_id => null,
662 p_report_line_id => null,
663 p_employee_id => p_employee_id,
664 p_cost_center => p_costCenter,
665 p_line_cost_center => null,
666 p_exp_type_parameter_id => null,
667 p_segments => null,
668 p_ccid => null,
669 p_build_mode => AP_WEB_ACCTG_PKG.C_DEFAULT_VALIDATE,
670 p_new_segments => l_default_emp_segments,
671 p_new_ccid => l_employee_ccid,
672 p_return_error_message => l_error_message);
673 IF (l_error_message IS NOT NULL) THEN
674 -- BuildAccount will in itself add to fnd message pub, initialize to avoid that
675 -- No side effects with initialize in Spread Sheet import, all error messages properly displayed.
676 fnd_msg_pub.initialize;
677 FND_MESSAGE.SET_NAME('SQLAP','OIE_DEA_VALIDATION_ERROR');
678 FND_MESSAGE.SET_TOKEN('VAL_ERROR', l_error_message);
679 p_cs_error:= fnd_message.get_encoded();
680 END IF;
681 END IF;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 AP_WEB_DB_UTIL_PKG.RaiseException('Validate Cost Center');
686 APP_EXCEPTION.RAISE_EXCEPTION;
687
688 END ValidateCostCenter;
689
690
691
692 FUNCTION IsReceiptRequired
693 (p_require_receipt_amount number,
694 p_amount varchar2,
695 p_reimb_curr varchar2,
696 p_base_currency varchar2) RETURN BOOLEAN IS
697 --
698 -- Returns true if the receipt is required, false otherwise.
699 -- The require_receipt_amount value in ap_expense_report_params is based
700 -- on base currency. Since we'll do currency exchange in a fairly late
701 -- state (in workflow), for any receipt that is in non-base currency,
702 -- if the require_receipt_amount is >= 0 for its expense type, we
703 -- set receipt required to true for this receipt. (7/15/97)
704 --
705
706 l_amt number;
707
708 BEGIN
709
710 l_amt := nvl(p_require_receipt_amount, -1);
711 if (l_amt >= 0) then
712 if (nvl(p_reimb_curr, p_base_currency) <> p_base_currency) then
713 -- receipt currency not equal to base currency
714 return true;
715 elsif (to_number(p_amount) > l_amt) then
716 -- receipt amount exceed threshold
717 return true;
718 else
719 return false;
720 end if;
721 else
722 return false;
723 end if;
724 return false;
725 END IsReceiptRequired;
726
727 FUNCTION WithinTolerance(P_actual number,
728 P_target number) RETURN BOOLEAN
729 --
730 -- Used in comparing what user entered with our calculation.
731 -- Depending on currency format, that could be some diff in rounding.
732 -- Don't want to be too restrictive, but the tolerance can be adjusted.
733 --
734 IS
735 BEGIN
736 RETURN ((P_actual < (P_target + C_Tolerance)) AND
737 (P_actual > (P_target - C_Tolerance)));
738 END;
739
740 ---------------------------------------------------------------
741 PROCEDURE ValidateReportHeader(
742 ExpReportHeaderInfo IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
743 p_Error IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError
744
745 ) IS
746 ---------------------------------------------------------------
747 l_FuncCode varchar2(50) := 'AP_WEB_EXPENSES';
748 BEGIN
749
750 IF ( AP_WEB_INFRASTRUCTURE_PKG.validatesession(l_FuncCode) ) THEN
751 ValidateHeaderNoValidSession(p_user_id => null, -- 2242176, use preparer in blue gray
752 ExpReportHeaderInfo => ExpReportHeaderInfo,
753 p_error => p_Error,
754 p_bFull_Approver_Validation => TRUE);
755 END IF;
756
757 END ValidateReportHeader;
758
759
760 /*----------------------------------------------------------------------------*
761 | Procedure |
762 | ValidateHeaderNoValidSession |
763 | |
764 | DESCRIPTION |
765 | Server-Side validation for report header without calling |
766 | validatesession |
767 | |
768 | ASSUMPTION |
769 | |
770 | PARAMETERS |
771 | |
772 | RETURNS |
773 | none |
774 *----------------------------------------------------------------------------*/
775 PROCEDURE ValidateHeaderNoValidSession(
776 p_user_id IN NUMBER, -- 2242176, fnd user id
777 ExpReportHeaderInfo IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
778 p_error IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError,
779 p_bFull_Approver_Validation IN BOOLEAN
780
781 ) IS
782 ---------------------------------------------------------------
783 l_last_receipt VARCHAR2(25);
784 l_last_receipt_date DATE;
785
786 l_employee_name AP_WEB_DB_HR_INT_PKG.empCurrent_fullName := 'McKee, Mr. David (Dave)';
787 l_employee_num AP_WEB_DB_HR_INT_PKG.empCurrent_empNum := 100;
788 l_default_cost_center VARCHAR2(80);
789 current_calling_sequence VARCHAR2(2000);
790 debug_info VARCHAR2(100);
791
792 l_allow_overrider VARCHAR2(1) := 'N';
793 l_require_overrider VARCHAR2(1) := 'N';
794 l_overrider_CC VARCHAR2(1) := 'N';
795 l_date_format VARCHAR2(30);
796 -- For displaying error table
797 l_cs_error varchar2(500) := '';
798 l_ap_error varchar2(500) := '';
799
800 l_IsMobileApp boolean;
801
802 -- For bug fix 1865355
803 l_exp_reimb_curr_profile VARCHAR2(1);
804 l_apsys_info_rec AP_WEB_DB_AP_INT_PKG.APSysInfoRec;
805 l_base_currency AP_SYSTEM_PARAMETERS.base_currency_code%TYPE;
806 l_vendor_id NUMBER;
807 l_vend_pay_curr VARCHAR2(15);
808 l_vend_pay_curr_name FND_CURRENCIES_VL.name%TYPE;
809 l_default_reimb_curr FND_CURRENCIES_VL.currency_code%TYPE;
810
811 BEGIN
812
813 l_IsMobileApp := AP_WEB_UTILITIES_PKG.IsMobileApp;
814
815
816 -- Get date mask
817 --Bug 3336823
818 l_date_format := nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'));
819
820 -- Update the calling sequence
821 --
822 IF (NOT AP_WEB_DB_UTIL_PKG.GetFormattedSysDate(l_date_format, l_last_receipt)) THEN
823 NULL;
824 END IF;
825
826 current_calling_sequence := 'AP_WEB_VALIDATE_UTIL.ValidateReportHeader';
827 -- Validate Approver Name
828 -- If override approver name is provided and no approver id
829 -- exists (non-wizard validated) then validate.
830 -- Overriding approver cannot be the submitting employee
831 --
832 debug_info := 'Validate Override Approver';
833
834 -- Bug 3525089 : Setting up l_allow_overrider, so that if p_bFull_Approver_Validation = false
835 -- then ValidateApprover gets called
836 l_allow_overrider := AP_WEB_UTILITIES_PKG.value_specific(
837 p_name => 'AP_WEB_ALLOW_OVERRIDE_APPROVER',
838 p_user_id => p_user_id,
839 p_resp_id => null,
840 p_apps_id => null);
841
842 IF (p_bFull_Approver_Validation) THEN
843
844 l_overrider_CC := AP_WEB_UTILITIES_PKG.value_specific(
845 p_name => 'AP_WEB_APPROVER_REQ_CC',
846 p_user_id => p_user_id,
847 p_resp_id => null,
848 p_apps_id => null);
849
850 IF ( l_allow_overrider = 'Y' ) THEN
851 -- get the default cost center of the filer:
852 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(
853 l_employee_name,
854 l_employee_num,
855 l_default_cost_center,
856 ExpReportHeaderInfo.employee_id );
857
858 l_require_overrider := AP_WEB_UTILITIES_PKG.value_specific(
859 p_name => 'AP_WEB_OVERRIDE_APPR_REQ',
860 p_user_id => p_user_id,
861 p_resp_id => null,
862 p_apps_id => null );
863
864 IF ( l_require_overrider = 'Y' OR l_require_overrider = 'D') THEN
865 IF ( ExpReportHeaderInfo.override_approver_name IS NULL ) THEN
866 fnd_message.set_name('SQLAP', 'AP_WEB_OVERRIDER_REQUIRED');
867 AP_WEB_UTILITIES_PKG.AddExpError(p_error,
868 fnd_message.get_encoded(),
869 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
870 null,
871 0,
872 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
873 ELSE
874 ValidateApprover(
875 ExpReportHeaderInfo.employee_id,
876 ExpReportHeaderInfo.override_approver_name,
877 ExpReportHeaderInfo.override_approver_id,
878 l_ap_error );
879 IF ( l_ap_error IS NOT NULL ) THEN
880 AP_WEB_UTILITIES_PKG.AddExpError(
881 p_error,
882 l_ap_error,
883 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
884 'AP_WEB_FULLNAME',
885 0,
886 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
887 END IF;
888
889 END IF;
890 ELSIF (l_require_overrider = 'N' AND ExpReportHeaderInfo.override_approver_name IS NOT NULL) THEN
891 ValidateApprover(
892 ExpReportHeaderInfo.employee_id,
893 ExpReportHeaderInfo.override_approver_name,
894 ExpReportHeaderInfo.override_approver_id,
895 l_ap_error );
896
897 IF ( l_ap_error IS NOT NULL ) THEN
898 AP_WEB_UTILITIES_PKG.AddExpError(
899 p_error,
900 l_ap_error,
901 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
902 'AP_WEB_FULLNAME',
903 0,
904 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
905 END IF;
906 ELSIF ( l_overrider_CC = 'Y' AND ExpReportHeaderInfo.cost_center <> l_default_cost_center ) THEN
907 IF ( ExpReportHeaderInfo.override_approver_name IS NULL ) THEN
908 fnd_message.set_name('SQLAP','AP_WEB_DISCON_OVERRIDER_CC');
909 AP_WEB_UTILITIES_PKG.AddExpError(
910 p_error,
911 fnd_message.get_encoded(),
912 AP_WEB_UTILITIES_PKG.C_ErrorMessageType ,
913 null,
914 0,
915 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
916 ELSE
917 ValidateApprover(
918 ExpReportHeaderInfo.employee_id,
919 ExpReportHeaderInfo.override_approver_name,
920 ExpReportHeaderInfo.override_approver_id,
921 l_ap_error );
922
923 IF ( l_ap_error IS NOT NULL ) THEN
924 AP_WEB_UTILITIES_PKG.AddExpError(
925 p_error,
926 l_ap_error,
927 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
928 'AP_WEB_FULLNAME',
929 0,
930 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
931 END IF;
932 END IF;
933 END IF;
934 END IF;
935 ELSE
936
937 IF ( l_allow_overrider = 'Y' ) THEN
938 IF ( ExpReportHeaderInfo.override_approver_name IS NOT NULL ) THEN
939 ValidateApprover(
940 ExpReportHeaderInfo.employee_id,
941 ExpReportHeaderInfo.override_approver_name,
942 ExpReportHeaderInfo.override_approver_id,
943 l_ap_error );
944
945 IF ( l_ap_error IS NOT NULL ) THEN
946 AP_WEB_UTILITIES_PKG.AddExpError(
947 p_error,
948 l_ap_error,
949 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
950 'AP_WEB_FULLNAME',
951 0,
952 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
953 END IF;
954 END IF;
955 END IF;
956 END IF;
957 -- Validate Cost Center
958 debug_info := 'Validate Cost Center';
959 IF ( ExpReportHeaderInfo.cost_center IS NULL ) THEN
960 if (not l_IsMobileApp) then
961 fnd_message.set_name( 'SQLAP', 'AP_WEB_COST_CENTER_INVALID' );
962 else
963 fnd_message.set_name( 'SQLAP', 'AP_OME_COST_CENTER_ERROR' );
964 end if;
965 AP_WEB_UTILITIES_PKG.AddExpError(
966 p_error,
967 fnd_message.get_encoded(),
968 AP_WEB_UTILITIES_PKG.C_errorMessageType,
969 'txtCostCenter',
970 0,
971 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
972 ELSE
973 AP_WEB_VALIDATE_UTIL.ValidateCostCenter(
974 ExpReportHeaderInfo.cost_center,
975 l_cs_error,
976 ExpReportHeaderInfo.employee_id );
977
978 IF ( l_cs_error IS NOT NULL ) THEN
979 AP_WEB_UTILITIES_PKG.AddExpError(
980 p_error,
981 l_cs_error,
982 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
983 'txtCostCenter',
984 0,
985 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory,
986 l_IsMobileApp);
987 END IF;
988 END IF;
989
990
991 debug_info := 'Validate Expense Template';
992 BEGIN
993 -- Fix bug 1472710, removed initcap for ExpReportHeaderInfo.template_name
994 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetExpTemplateId(
995 ExpReportHeaderInfo.template_name,
996 ExpReportHeaderInfo.template_id)) THEN
997 ExpReportHeaderInfo.template_id := NULL;
998 ExpReportHeaderInfo.template_name := NULL;
999 raise NO_DATA_FOUND;
1000 END IF;
1001
1002 EXCEPTION
1003 when OTHERS then
1004 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_TEMP_INVALID');
1005 AP_WEB_UTILITIES_PKG.AddExpError(p_error,
1006 fnd_message.get_encoded(),
1007 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1008 'TEMPLATE',
1009 0,
1010 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1011 END;
1012
1013 -- Validate Last Receipt Date
1014 debug_info := 'Validating Last Receipt Date';
1015 BEGIN
1016 debug_info := 'Store Last Receipt date string into the date format';
1017 l_last_receipt_date := to_date(l_last_receipt, l_date_format);
1018 IF (ExpReportHeaderInfo.last_receipt_date IS NOT NULL) THEN
1019 IF (l_last_receipt_date < to_date(ExpReportHeaderInfo.last_receipt_date, l_date_format)) THEN
1020 l_last_receipt_date := ExpReportHeaderInfo.last_receipt_date;
1021 END IF;
1022 END IF;
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 FND_MESSAGE.SET_NAME( 'SQLAP','AP_WEB_LAST_RECDATE_INVALID' );
1026 AP_WEB_UTILITIES_PKG.AddExpError(
1027 p_error,
1028 fnd_message.get_encoded(),
1029 AP_WEB_UTILITIES_PKG.C_errorMessageType,
1030 'popEmployeeID',
1031 0,
1032 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1033
1034 END;
1035
1036 -- For bug fix 1865355
1037 -- Validate reimbursement currency
1038 l_exp_reimb_curr_profile := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
1039 p_name => 'AP_WEB_ALLOW_NON_BASE_REIMB',
1040 p_user_id => p_user_id,
1041 p_resp_id => null,
1042 p_apps_id => null);
1043 IF ( nvl(l_exp_reimb_curr_profile,'Y') = 'N' ) THEN
1044 IF ( NOT AP_WEB_DB_AP_INT_PKG.GetVendorInfoOfEmp(ExpReportHeaderInfo.employee_id,
1045 l_vendor_id,l_vend_pay_curr,l_vend_pay_curr_name) ) THEN
1046 NULL;
1047 END IF;
1048
1049 IF ( AP_WEB_DB_AP_INT_PKG.GetAPSysCurrencySetupInfo(l_apsys_info_rec) = TRUE ) THEN
1050 l_base_currency := l_apsys_info_rec.base_currency;
1051 END IF;
1052
1053 l_default_reimb_curr := nvl(l_vend_pay_curr, l_base_currency);
1054 IF ( ExpReportHeaderInfo.reimbursement_currency_code <> l_default_reimb_curr ) THEN
1055 ExpReportHeaderInfo.reimbursement_currency_code := l_default_reimb_curr;
1056 FND_MESSAGE.SET_NAME('SQLAP', 'OIE_INVALID_REIMB_CURR');
1057 FND_MESSAGE.SET_TOKEN('FUNCTIONAL_CURRENCY',l_default_reimb_curr);
1058 AP_WEB_UTILITIES_PKG.AddExpError(p_error,
1059 fnd_message.get_encoded(),
1060 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
1061 END IF;
1062 END IF;
1063
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 -- JMARY have to handle this error for NEWUI
1067 BEGIN
1068 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1069 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1070 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1071 current_calling_sequence);
1072
1073 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1074
1075 APP_EXCEPTION.RAISE_EXCEPTION;
1076 END;
1077 END ValidateHeaderNoValidSession;
1078
1079 /*----------------------------------------------------------------------------*
1080 | Procedure |
1081 | ValidateExpLineCustomFields |
1082 | |
1083 | DESCRIPTION |
1084 | Server-Side validation for single receipt line custom fields |
1085 | |
1086 | ASSUMPTION |
1087 | p_report_header_info.number_max_flexfield has been set |
1088 | |
1089 | PARAMETERS |
1090 | |
1091 | RETURNS |
1092 | none |
1093 *----------------------------------------------------------------------------*/
1094 PROCEDURE ValidateExpLineCustomFields(
1095 p_userId IN NUMBER,
1096 p_report_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
1097 p_report_line_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLineRec,
1098 p_receipt_index IN INTEGER, -- for AddExpError
1099 p_SysInfoRec IN AP_WEB_DB_AP_INT_PKG.APSysInfoRec,
1100 p_DefaultExchangeRate IN NUMBER,
1101 p_EndExpenseDate IN DATE,
1102 p_DateFormat IN VARCHAR2,
1103 p_custom1_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1104 p_custom2_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1105 p_custom3_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1106 p_custom4_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1107 p_custom5_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1108 p_custom6_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1109 p_custom7_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1110 p_custom8_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1111 p_custom9_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1112 p_custom10_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1113 p_custom11_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1114 p_custom12_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1115 p_custom13_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1116 p_custom14_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1117 p_custom15_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1118 P_IsSessionTaxEnabled IN VARCHAR2,
1119 P_IsSessionProjectEnabled IN VARCHAR2,
1120 p_receipt_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack,
1121 p_calculate_receipt_index IN BINARY_INTEGER,
1122 p_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError,
1123 p_receipts_with_errors_count IN OUT NOCOPY BINARY_INTEGER,
1124 p_DataDefaultedUpdateable IN OUT NOCOPY BOOLEAN,
1125 p_bCalling_from_disconnected IN BOOLEAN,
1126 p_bForBlueGray IN BOOLEAN default FALSE,
1127 p_addon_rates IN OIE_ADDON_RATES_T DEFAULT NULL,
1128 p_report_line_id IN NUMBER DEFAULT NULL,
1129 p_daily_breakup_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
1130 p_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
1131 p_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
1132 p_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
1133 p_number_of_meals IN OIE_PDM_NUMBER_T DEFAULT NULL,
1134 p_meals_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
1135 p_breakfast_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
1136 p_lunch_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
1137 p_dinner_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
1138 p_accommodation_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
1139 p_accommodation_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
1140 p_hotel_name IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
1141 p_night_rate_Type IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
1142 p_night_rate_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
1143 p_pdm_rate IN OIE_PDM_NUMBER_T DEFAULT NULL,
1144 p_rate_Type_code IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
1145 p_pdm_breakup_dest_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
1146 p_pdm_destination_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
1147 p_dest_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
1148 p_dest_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
1149 p_location_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
1150 p_cust_meals_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
1151 p_cust_accommodation_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
1152 p_cust_night_rate_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
1153 p_cust_pdm_rate IN OUT NOCOPY OIE_PDM_NUMBER_T
1154 )
1155 ----------------------------------------------------------------------------
1156 IS
1157
1158 l_receipt_custom_fields_array AP_WEB_DFLEX_PKG.CustomFields_A;
1159
1160 l_curr_calling_sequence VARCHAR2(200) := 'ValidateExpLinesCustomFields';
1161 l_debug_info VARCHAR2(2000);
1162
1163 i INTEGER;
1164 AttributeCol_Array AP_WEB_PARENT_PKG.BigString_Array;
1165
1166 V_Field1 NUMBER;
1167 V_Field2 NUMBER;
1168
1169 V_AcctRawCost NUMBER; -- For PATC: Raw cost in functional currency
1170
1171 l_error1 AP_WEB_UTILITIES_PKG.expError;
1172 l_error2 AP_WEB_UTILITIES_PKG.expError;
1173 l_invRate VARCHAR2(1) := 'N';
1174 l_calc_amt_enabled_for_disc BOOLEAN := FALSE;
1175
1176 V_EnteredByUserID VARCHAR2(100);
1177
1178 l_IsMobileApp boolean;
1179
1180 l_report_lines_array AP_WEB_DFLEX_PKG.ExpReportLines_A;
1181
1182 V_GrantsResult VARCHAR2(2000); -- For Grants: Error/warning message
1183
1184 l_CostCenterErrorMsg VARCHAR2(2000) := NULL;
1185
1186 BEGIN
1187
1188 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', 'start ValidateExpLineCustomFields');
1189 l_IsMobileApp := AP_WEB_UTILITIES_PKG.IsMobileApp;
1190
1191
1192 -- Validate descriptive flexfields and call custom validation hook for both
1193 -- core and descriptive flexfields
1194 l_debug_info := 'Validate desc flexfields and custom validation hook';
1195 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
1196 IF p_report_header_info.number_max_flexfield > 0 THEN
1197
1198 -- validate only if an expense type is specified
1199 IF p_report_line_info.parameter_id IS NOT NULL THEN
1200
1201 -----------------------------------------------------
1202 l_debug_info := 'GetReceiptCustomFields';
1203 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
1204 -----------------------------------------------------
1205
1206 AP_WEB_DFLEX_PKG.GetReceiptCustomFields(l_receipt_custom_fields_array,
1207 p_receipt_index,
1208 p_custom1_array,
1209 p_custom2_array,
1210 p_custom3_array,
1211 p_custom4_array,
1212 p_custom5_array,
1213 p_custom6_array,
1214 p_custom7_array,
1215 p_custom8_array,
1216 p_custom9_array,
1217 p_custom10_array,
1218 p_custom11_array,
1219 p_custom12_array,
1220 p_custom13_array,
1221 p_custom14_array,
1222 p_custom15_array);
1223
1224 -----------------------------------------------------
1225 l_debug_info := 'ValidateReceiptCustomFields';
1226 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
1227 -----------------------------------------------------
1228 AP_WEB_DFLEX_PKG.ValidateReceiptCustomFields(p_userId,
1229 p_report_header_info,
1230 p_report_line_info,
1231 l_receipt_custom_fields_array,
1232 p_receipt_errors,
1233 p_receipt_index,
1234 l_error1);
1235 --chiho:1346208:
1236 p_receipts_with_errors_count := p_receipts_with_errors_count + l_error1.COUNT;
1237
1238 -- Call custom validation hook for both core and pseudo descriptive flexfields
1239 l_debug_info := 'Call custom validation hook for both core and pseudo descriptive flexfields';
1240 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
1241 AP_WEB_CUST_DFLEX_PKG.CustomValidateLine(p_report_header_info,
1242 p_report_line_info,
1243 l_receipt_custom_fields_array,
1244 l_error2);
1245
1246 -----------------------------------------------------
1247 l_debug_info := 'PropogateReceiptCustFldsInfo';
1248 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
1249 -----------------------------------------------------
1250 AP_WEB_DFLEX_PKG.PropogateReceiptCustFldsInfo(
1251 l_receipt_custom_fields_array,
1252 p_receipt_index,
1253 p_custom1_array,
1254 p_custom2_array,
1255 p_custom3_array,
1256 p_custom4_array,
1257 p_custom5_array,
1258 p_custom6_array,
1259 p_custom7_array,
1260 p_custom8_array,
1261 p_custom9_array,
1262 p_custom10_array,
1263 p_custom11_array,
1264 p_custom12_array,
1265 p_custom13_array,
1266 p_custom14_array,
1267 p_custom15_array);
1268
1269 END IF; -- (p_report_line_info.parameter_id IS NOT NULL)
1270 ELSE -- (p_report_header_info.number_max_flexfield > 0)
1271
1272 -- Call custom validation hook for both core and pseudo descriptive flexfields
1273 l_debug_info := 'Call custom validation hook';
1274 -- Bug: 6617094, Expense type is null in Custom Validation.
1275 AP_WEB_DFLEX_PKG.PopulateExpTypeInLineRec(p_report_line_info);
1276 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
1277 AP_WEB_CUST_DFLEX_PKG.CustomValidateLine(p_report_header_info,
1278 p_report_line_info,
1279 l_receipt_custom_fields_array,
1280 l_error2);
1281
1282 END IF; -- (p_report_header_info.number_max_flexfield > 0)
1283
1284 IF p_bCalling_from_disconnected THEN
1285 l_calc_amt_enabled_for_disc := AP_WEB_DB_EXPTEMPLATE_PKG.IsCustomCalculateEnabled(
1286 p_report_header_info.template_id,
1287 p_report_line_info.parameter_id);
1288
1289 END IF;
1290
1291 IF (p_receipts_with_errors_count = 0) THEN
1292
1293 /* If calculate amount is being called from online entry, then
1294 p_calculate_receipt_index is not null. If we are uploading
1295 the spreadsheet, p_calculate_receipt_index is null and we need
1296 to call customcalculateamount if calculate amount is enabled for
1297 this expense type. */
1298 IF ((p_calculate_receipt_index IS NOT NULL) OR (l_calc_amt_enabled_for_disc)) THEN
1299
1300 AP_WEB_CUST_DFLEX_PKG.CustomCalculateAmount(
1301 p_report_header_info,
1302 p_report_line_info,
1303 l_receipt_custom_fields_array,
1304 p_addon_rates,
1305 p_report_line_id,
1306 p_daily_breakup_id,
1307 p_start_date,
1308 p_end_date,
1309 p_amount,
1310 p_number_of_meals,
1311 p_meals_amount,
1312 p_breakfast_flag,
1313 p_lunch_flag,
1314 p_dinner_flag,
1315 p_accommodation_amount,
1316 p_accommodation_flag,
1317 p_hotel_name,
1318 p_night_rate_Type,
1319 p_night_rate_amount,
1320 p_pdm_rate,
1321 p_rate_Type_code,
1322 p_pdm_breakup_dest_id,
1323 p_pdm_destination_id,
1324 p_dest_start_date,
1325 p_dest_end_date,
1326 p_location_id,
1327 p_cust_meals_amount,
1328 p_cust_accommodation_amount,
1329 p_cust_night_rate_amount,
1330 p_cust_pdm_rate
1331 );
1332
1333 END IF;
1334
1335 IF (l_calc_amt_enabled_for_disc) THEN
1336
1337 ----------------------------------------------
1338 l_debug_info := 'Propagate calculated amount';
1339 ----------------------------------------------
1340 IF (to_number(substr(p_report_line_info.receipt_amount,1,80)) <>
1341 to_number(substr(p_report_line_info.calculated_amount,1,80)))THEN
1342 p_report_line_info.receipt_amount := p_report_line_info.calculated_amount;
1343 ----------------------------------------------
1344 l_debug_info := 'Set Datadefaulted to true';
1345 ----------------------------------------------
1346 p_DataDefaultedUpdateable := TRUE;
1347 ELSE
1348 p_DataDefaultedUpdateable := FALSE;
1349
1350 END IF;
1351
1352 ----------------------------------------------
1353 l_debug_info := 'Recalculate Daily Amount';
1354 ----------------------------------------------
1355 p_report_line_info.daily_amount := NULL;
1356
1357 ----------------------------------------------
1358 l_debug_info := 'Recalculate Amount';
1359 ----------------------------------------------
1360 l_invRate := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
1361 p_name => 'DISPLAY_INVERSE_RATE',
1362 p_user_id => p_userId,
1363 p_resp_id => null,
1364 p_apps_id => null);
1365
1366 IF (l_invRate = 'N') THEN
1367 p_report_line_info.amount := p_report_line_info.receipt_amount * p_report_line_info.rate;
1368 ELSE
1369 p_report_line_info.amount := p_report_line_info.receipt_amount / p_report_line_info.rate;
1370 END IF;
1371
1372
1373 ----------------------------------------------
1374 l_debug_info := 'Revalidate new values';
1375 ----------------------------------------------
1376 AP_WEB_DFLEX_PKG.ValidateReceiptCustomFields(p_userId,
1377 p_report_header_info,
1378 p_report_line_info,
1379 l_receipt_custom_fields_array,
1380 p_receipt_errors,
1381 p_receipt_index,
1382 l_error1);
1383 END IF;
1384
1385 END IF;
1386
1387
1388 --chiho:1346208:
1389 AP_WEB_UTILITIES_PKG.MergeExpErrors( p_errors, l_error1 );
1390
1391 AP_WEB_UTILITIES_PKG.MergeExpErrors(p_errors, l_error2);
1392
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 BEGIN
1396 IF (SQLCODE <> -20001) THEN
1397 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1398 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1399 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1400 'ValidateExpLineCustomFields');
1401 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1402 'None passed.');
1403 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1404 END IF;
1405 APP_EXCEPTION.RAISE_EXCEPTION;
1406 END;
1407 END ValidateExpLineCustomFields;
1408
1409
1410 /*----------------------------------------------------------------------------*
1411 | Procedure |
1412 | ValidateExpLineCoreFields |
1413 | |
1414 | DESCRIPTION |
1415 | Server-Side validation for single receipt line core fields |
1416 | |
1417 | ASSUMPTION |
1418 | |
1419 | PARAMETERS |
1420 | |
1421 | RETURNS |
1422 | none |
1423 *----------------------------------------------------------------------------*/
1424 PROCEDURE ValidateExpLineCoreFields
1425 (p_user_id IN NUMBER, -- 2242176, fnd user id
1426 p_report_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
1427 p_report_line_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLineRec,
1428 p_receiptcount IN NUMBER,
1429 p_allow_credit_lines IN BOOLEAN,
1430 p_justreq_array IN AP_WEB_PARENT_PKG.Number_Array,
1431 p_reimbcurr_precision IN FND_CURRENCIES_VL.PRECISION%TYPE,
1432 p_calculate_receipt_index IN BINARY_INTEGER,
1433 p_exp_error IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError)
1434 -----------------------------------------------------------------------------
1435 IS
1436
1437 /* Note: p_receipt_errors might not be empty -- Validation in disconnected
1438 might have filled in something.
1439 -Try to validate the receipt attributes in the same order as they'll appear
1440 in View Receipts table.
1441 -If the function is called from disconnected, since we don't support multi-curr
1442 initially, some of the arrays are just empty ones. So need to check if the
1443 array size is zero before proceeding to avoid an exception.
1444 */
1445
1446 l_DATE1_temp DATE;
1447 l_dates_ok BOOLEAN := true;
1448 l_date2_temp DATE;
1449 l_dailyAmount NUMBER;
1450 l_receiptAmount NUMBER;
1451 l_num NUMBER;
1452 l_orig_num NUMBER; --added for bug 1056403
1453 l_rate NUMBER;
1454 l_rate_string VARCHAR2(15);
1455 l_amount NUMBER;
1456 l_sdate DATE := sysdate;
1457 l_edate DATE;
1458 l_acdate DATE; -- date1 if date2 is null, otherwise date2.
1459 l_receipt_custom_fields_array AP_WEB_DFLEX_PKG.CustomFields_A;
1460 l_reimbcurr_format VARCHAR2(80);
1461 l_reccurr_format VARCHAR2(80);
1462 l_date_format VARCHAR2(30);
1463 i NUMBER;
1464 debug_info VARCHAR2(100) := '';
1465 l_inverse_rate_profile VARCHAR2(1);
1466
1467 l_is_fixed_rate VARCHAR2(1);
1468 l_fixed_rate_applied VARCHAR2(1) := 'N';
1469 l_fixed_msg VARCHAR2(2000);
1470 l_cal_amount_method VARCHAR2(1) := '*'; --added for bug 1056403
1471 l_time_based_entry_flag varchar2(1); -- Bug 6392916 (sodash)
1472 l_IsMobileApp boolean;
1473
1474 BEGIN
1475
1476 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_VALIDATE_UTIL', 'start ValidateExpLineCoreFields');
1477 l_IsMobileApp := AP_WEB_UTILITIES_PKG.IsMobileApp;
1478
1479 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_FIXED');
1480 l_fixed_msg := AP_WEB_DB_UTIL_PKG.jsPrepString(fnd_message.get_encoded(), TRUE);
1481
1482 l_reimbcurr_format := FND_CURRENCY.get_format_mask(p_report_header_info.reimbursement_currency_code, 30);
1483
1484 if (p_report_header_info.transaction_currency_type = 'reimbursement') then
1485 l_reccurr_format := l_reimbcurr_format;
1486 end if;
1487
1488 --Bug 3336823
1489 l_date_format := nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'));
1490
1491
1492 l_inverse_rate_profile := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
1493 p_name => 'DISPLAY_INVERSE_RATE',
1494 p_user_id => p_user_id,
1495 p_resp_id => null,
1496 p_apps_id => null);
1497
1498 l_fixed_rate_applied := 'N';
1499
1500 debug_info:='checking trans';
1501 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1502 l_dates_ok := true;
1503 if (p_report_header_info.transaction_currency_type = 'multi') then
1504 l_reccurr_format :=
1505 FND_CURRENCY.get_format_mask(p_report_line_info.currency_code, 30);
1506 end if;
1507 --
1508 -- Check if Valid Start Date was entered
1509 --
1510 debug_info:='Check Start Date';
1511 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1512 if (p_report_line_info.start_date is null) then
1513 l_dates_ok := false;
1514 fnd_message.set_name('SQLAP', 'AP_WEB_ST_DATE_FIRST');
1515 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1516 fnd_message.get_encoded(),
1517 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1518 C_Date1_Prompt,
1519 p_receiptcount,
1520 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1521 else
1522 BEGIN
1523 --Bug 6054643: Removing the to_date conversion
1524 l_date1_temp := trunc(p_report_line_info.start_date);
1525
1526 -- Check whether year is Valid
1527 if (NOT IsValidYear(l_date1_temp) AND (p_report_line_info.category_code <> 'PER_DIEM') )THEN
1528 l_dates_ok := false;
1529 fnd_message.set_name('SQLAP', 'OIE_INVALID_YEAR');
1530 fnd_message.set_token('MINIMUM_YEAR', to_char(C_MinimumYear));
1531 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1532 fnd_message.get_encoded(),
1533 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1534 C_Date1_Prompt,
1535 p_receiptcount,
1536 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1537 end if;
1538
1539 l_acdate := l_date1_temp;
1540
1541 if (l_edate is null) then
1542 l_edate := l_date1_temp;
1543 end if;
1544 if (l_date1_temp < l_sdate) then
1545 l_sdate := l_date1_temp;
1546 elsif (l_date1_temp > l_edate) then
1547 l_edate := l_date1_temp;
1548 end if;
1549 EXCEPTION
1550 when OTHERS then
1551 l_dates_ok := false;
1552 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_START_DATE_INVALID');
1553 FND_MESSAGE.SET_TOKEN('START_DATE', p_report_line_info.start_date);
1554 FND_MESSAGE.SET_TOKEN('PROPER_FORMAT', to_char(sysdate, l_date_format));
1555 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1556 fnd_message.get_encoded(),
1557 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1558 C_Date1_Prompt,
1559 p_receiptcount,
1560 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1561
1562 END;
1563 end if; /* date 1 is null */
1564
1565 --
1566 -- Check if Valid End Date was entered
1567 --
1568 debug_info := 'Check End Date';
1569 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1570 BEGIN
1571 --Bug 6054643: Removing the to_date conversion
1572 l_date2_temp := trunc(nvl(p_report_line_info.end_date, l_date1_temp));
1573
1574 -- Check whether year is Valid
1575
1576 /*Bug 2292854: Raise End Date invalid error only if expense category
1577 code is PER_DIEM. Dont raise for Receipt based and Mileage.
1578 */
1579 IF (p_report_line_info.category_code = 'PER_DIEM') THEN
1580 -- Bug 6392916 (sodash)
1581 -- Checking whether the start date is valid
1582 if (NOT IsValidYear(l_date1_temp)) THEN
1583 l_dates_ok := false;
1584 fnd_message.set_name('SQLAP', 'OIE_INVALID_YEAR_START_DATE');
1585 fnd_message.set_token('MINIMUM_YEAR', to_char(C_MinimumYear));
1586 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1587 fnd_message.get_encoded(),
1588 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1589 C_Date1_Prompt,
1590 p_receiptcount,
1591 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1592 end if;
1593 -- Bug 6392916 (sodash)
1594 -- Checking whether entering the end date is compulsory for the Per Diem Schedule
1595 select TIME_BASED_ENTRY_FLAG into l_time_based_entry_flag from AP_POL_HEADERS aph, AP_EXPENSE_REPORT_PARAMS_ALL param where aph.POLICY_ID = param.COMPANY_POLICY_ID and param.PARAMETER_ID = p_report_line_info.parameter_id;
1596 if (l_time_based_entry_flag='Y' AND
1597 p_report_line_info.end_date IS NOT NULL AND
1598 NOT IsValidYear(l_date2_temp)) THEN
1599 l_dates_ok := false;
1600 fnd_message.set_name('SQLAP', 'OIE_INVALID_YEAR_END_DATE');
1601 fnd_message.set_token('MINIMUM_YEAR', to_char(C_MinimumYear));
1602 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1603 fnd_message.get_encoded(),
1604 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1605 C_Date2_Prompt,
1606 p_receiptcount,
1607 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1608 end if;
1609 END IF;
1610
1611 l_acdate := l_date2_temp;
1612 if (l_date2_temp > l_edate) then
1613 l_edate := l_date2_temp;
1614 elsif (l_date2_temp < l_sdate) then
1615 l_sdate := l_date2_temp;
1616 end if;
1617 EXCEPTION
1618 when OTHERS then
1619 l_dates_ok := false;
1620 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_END_DATE_INVALID');
1621 FND_MESSAGE.SET_TOKEN('END_DATE', p_report_line_info.end_date);
1622 FND_MESSAGE.SET_TOKEN('PROPER_FORMAT', to_char(sysdate, l_date_format));
1623 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1624 fnd_message.get_encoded(),
1625 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1626 C_Date2_Prompt,
1627 p_receiptcount,
1628 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1629 END;
1630
1631 --
1632 -- Check if End Date falls on or after Start Date
1633 -- Only do it when both dates' format is fine.
1634 --
1635 debug_info:= 'Day1 < Day2?';
1636 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1637 if (l_dates_ok) then
1638 if (l_date1_temp > nvl(l_date2_temp, l_date1_temp)) then
1639 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_START_AFTER_END');
1640 FND_MESSAGE.SET_TOKEN('START_DATE', p_report_line_info.start_date);
1641 FND_MESSAGE.SET_TOKEN('END_DATE', p_report_line_info.end_date);
1642 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1643 fnd_message.get_encoded(),
1644 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1645 C_Date2_Prompt,
1646 p_receiptcount,
1647 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1648 end if;
1649 end if;
1650
1651 --
1652 -- Check if Days is a valid number. And if days entered equals to
1653 -- actual different between the two dates.
1654 --
1655 debug_info := 'Check days';
1656 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1657 BEGIN
1658 l_num := to_number(p_report_line_info.days);
1659 if (l_dates_ok) then
1660 if (p_report_line_info.days is null) then
1661 if (p_report_line_info.end_date is null) then
1662 -- bug 2190588: raise exception since we are not able to
1663 -- calcualte the date range. This case should only happen
1664 -- when upload spreadsheet
1665 APP_EXCEPTION.RAISE_EXCEPTION;
1666 else
1667 /* Calculate the date range */
1668 p_report_line_info.days :=
1669 to_char(l_date2_temp - l_date1_temp + 1);
1670 end if;
1671
1672 -- Raise exception if number is not an integer or less than 1
1673 elsif (l_num < 1 OR floor(l_num) <> ceil(l_num)) then
1674 APP_EXCEPTION.RAISE_EXCEPTION;
1675
1676 -- If no end date is given, obtain the end date
1677 -- by adding days to the start date
1678 -- Only check the range if both start and end date are given. Bug 1865586
1679 elsif (to_date(p_report_line_info.end_date, l_date_format) IS NULL) then
1680 l_date2_temp := l_date1_temp + l_num - 1;
1681 p_report_line_info.end_date := l_date2_temp;
1682
1683 -- Take away due to request from IES. 8/1/97
1684 -- Undo the Take away above. Bug # 688566
1685 -- We DO want to make sure the number of days matches with
1686 -- the real range of dates.
1687 elsif ((l_date2_temp - l_date1_temp + 1) <> l_num)
1688 then
1689 fnd_message.set_name('SQLAP', 'AP_WEB_WRONG_NUM_OF_DAYS');
1690 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1691 fnd_message.get_encoded(),
1692 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1693 C_Days_Prompt,
1694 p_receiptcount,
1695 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1696 end if;
1697 end if; /* l_dates_ok */
1698 EXCEPTION
1699 when OTHERS then
1700 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_DAYS_INVALID');
1701 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1702 fnd_message.get_encoded(),
1703 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1704 C_Days_Prompt,
1705 p_receiptcount,
1706 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1707 END;
1708
1709 debug_info := 'Check if the fields exist in the receipts array';
1710 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1711
1712 --
1713 -- Check if Daily Amount is a valid number.
1714 --
1715 debug_info := 'Daily Amount';
1716 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1717 l_dailyAmount := null; -- initialize it since it will be used later!
1718 if (p_report_line_info.daily_amount IS NOT NULL) then
1719 BEGIN
1720 l_dailyAmount := AP_WEB_DB_UTIL_PKG.CharToNumber(p_report_line_info.daily_amount);
1721 if CheckNum(l_dailyAmount, p_exp_error, p_receiptcount,
1722 C_DAmount_Prompt, p_allow_credit_lines, FALSE) then
1723 -- p_report_line_info.daily_amount := to_char(l_num);
1724 NULL;
1725 end if;
1726 EXCEPTION
1727 when OTHERS then
1728 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_DAILY_AMOUNT_INVALID');
1729 p_report_line_info.daily_amount := null;
1730 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1731 fnd_message.get_encoded(),
1732 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1733 C_DAmount_Prompt,
1734 p_receiptcount,
1735 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1736
1737 END;
1738 end if;
1739
1740 --
1741 -- Check if Receipt Amount is a valid number.
1742 --
1743 debug_info := 'Check Receipt Amount';
1744 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1745 l_receiptAmount := null; -- initialize it since it will be used later
1746 BEGIN
1747 if (p_report_line_info.receipt_amount is not NULL) then
1748 l_receiptAmount := AP_WEB_DB_UTIL_PKG.CharToNumber(p_report_line_info.receipt_amount);
1749 if CheckNum(l_receiptAmount, p_exp_error, p_receiptcount,
1750 C_RecAmt_Prompt, p_allow_credit_lines, FALSE) then
1751 --
1752 -- If l_dailyAmount and l_receiptAmount are both not null and zero then
1753 -- l_receiptAmount must be equal l_dailyAmount * Days.
1754 BEGIN
1755 if ((l_dailyAmount is not NULL) AND (l_dailyAmount <> 0)) then
1756 -- bug 2103589: we need to deal with difference due to formatting
1757 l_num := to_number(p_report_line_info.days);
1758 if (l_num = 1) then
1759 l_amount := l_receiptAmount;
1760 else
1761 l_amount := to_number(to_char(l_receiptAmount/l_num, l_reccurr_format));
1762 end if;
1763
1764 if ((l_receiptAmount <> 0) AND (NOT WithinTolerance(l_dailyAmount, l_amount))) then
1765 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_RECEIPT_AMT_INCORRECT');
1766 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1767 fnd_message.get_encoded(),
1768 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1769 C_RecAmt_Prompt,
1770 p_receiptcount,
1771 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1772 elsif (l_receiptAmount = 0) then
1773 if (l_dailyAmount <> 0) then -- dailyAmount is valid
1774 -- Could have been pre-seeded to 0 in spreadsheet. Null it
1775 -- out since don't want to mess with currency format.
1776 p_report_line_info.receipt_amount := null;
1777 end if;
1778 end if;
1779 end if;
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782 -- Either DAmount or Days invalid. But both should have been
1783 -- reported earlier.
1784 NULL;
1785 END; /* l_dailyAmount * Days equal to l_receiptAmount */
1786
1787 --
1788 -- Check whether the receipt amount equals the rounded
1789 -- currency amount
1790
1791 if (NOT WithinTolerance (l_receiptAmount,
1792 AP_WEB_UTILITIES_PKG.OIE_ROUND_CURRENCY(l_receiptAmount,
1793 p_report_line_info.currency_code))) then
1794 FND_MESSAGE.SET_NAME('SQLAP', 'OIE_CURRENCY_NO_DECIMALS');
1795 FND_MESSAGE.SET_TOKEN('RECEIPT_CURRENCY',
1796 p_report_line_info.currency_code);
1797 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1798 fnd_message.get_encoded(),
1799 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1800 C_RecAmt_Prompt,
1801 p_receiptcount,
1802 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1803 end if; /* Rounding */
1804
1805 end if; /* CheckNum */
1806 end if;
1807 EXCEPTION
1808 when OTHERS then
1809 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_RECEIPT_AMOUNT_INVALID');
1810 p_report_line_info.receipt_amount := null;
1811 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1812 fnd_message.get_encoded(),
1813 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1814 C_RecAmt_Prompt,
1815 p_receiptcount,
1816 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1817
1818 END;
1819 -- chiho:bug#825307:extend the exception handling to include GL_CURRENCY_API.is_fixed_rate:
1820 BEGIN
1821 --
1822 -- Check if Rate is a valid number.
1823 --
1824 debug_info := 'Check rate';
1825 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1826 IF ((p_report_line_info.currency_code =
1827 p_report_header_info.reimbursement_currency_code) OR
1828 (p_report_line_info.currency_code = 'OTHER')) THEN
1829 l_is_fixed_rate := 'N';
1830 ELSE
1831 -- This works around a GL is_fixed_rate bug. when disconnected
1832 -- upload has wrong date format, l_acdate is null. it shouldn't
1833 -- call is_fixed_rate to determine conversion.
1834 -- is_fixed_rate does not handle null date well.
1835 IF (l_acdate is NULL) THEN
1836 l_is_fixed_rate := 'N';
1837 ELSE
1838 l_is_fixed_rate := GL_CURRENCY_API.is_fixed_rate(
1839 p_report_line_info.currency_code, p_report_header_info.reimbursement_currency_code, l_acdate);
1840 END IF;
1841 END IF; -- IF (p_report_line_info.currency_code = p_report_header_info.reimbursement_currency_code)
1842
1843 if (p_report_line_info.rate IS NOT NULL) then
1844 IF (l_is_fixed_rate = 'Y') THEN
1845 l_num := 1;
1846 ELSE
1847 l_num := to_number(nvl(p_report_line_info.rate, '1'));
1848 END IF; -- IF (l_is_fixed_rate = 'Y')
1849
1850 -- abosulte flag is set to true since rate should be > 0.
1851 -- **** USED TO BE CheckPosNum
1852 if not CheckNum(l_num, p_exp_error,
1853 p_receiptcount, C_Rate_Prompt, TRUE, TRUE) then
1854 debug_info := 'rate is negative'; /* CheckPosNum is function */
1855 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1856 else
1857 IF (l_is_fixed_rate = 'Y') THEN
1858 p_report_line_info.rate:= '1'; -- Bug 1871739. Use '1' instead of l_fixed_msg
1859 ELSE
1860 p_report_line_info.rate:=ltrim(to_char(l_num));
1861 END IF;
1862 -- get rid of trailing zeros, and if last char is a decimal then get
1863 -- rid of it
1864 IF INSTRB(p_report_line_info.rate,'.') <> 0 THEN
1865 p_report_line_info.rate := RTRIM(p_report_line_info.rate,'0');
1866 p_report_line_info.rate := RTRIM(p_report_line_info.rate,'.');
1867 END IF;
1868
1869 --
1870 -- if RecCurr = p_report_header_info.reimbursement_currency_code, and rate <> 1, it's an error.
1871 --
1872 if (nvl(p_report_line_info.currency_code, p_report_header_info.reimbursement_currency_code) = p_report_header_info.reimbursement_currency_code) then
1873 if ((l_num <> 1) or (l_num <> null)) then
1874 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_SAMECURR_RATE');
1875 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1876 fnd_message.get_encoded(),
1877 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1878 C_Rate_Prompt,
1879 p_receiptcount,
1880 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1881 -- Default rate to '1' since currency is not foreign.
1882 -- and adjust reimbursable amount
1883 p_report_line_info.rate:= '1'; -- Bug 2177344
1884 p_report_line_info.amount := p_report_line_info.receipt_amount;
1885 end if;
1886 end if;
1887 end if; /* CheckNum */
1888 end if; /* if l_rate_exists */
1889
1890 EXCEPTION
1891 when OTHERS then
1892 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_RATE_INVALID');
1893 p_report_line_info.rate := null;
1894 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
1895 fnd_message.get_encoded(),
1896 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1897 C_Rate_Prompt,
1898 p_receiptcount,
1899 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
1900 END;
1901
1902 --
1903 -- Check if (Total) Amount is a valid number.
1904 --
1905 debug_info := 'Amount';
1906 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1907 -- Only do this check in non "Calculate Amount" custom field calculation
1908 -- case.
1909
1910 if (NOT(p_calculate_receipt_index > 0) OR
1911 (p_calculate_receipt_index is NULL)) then
1912 -- First calculate what we would get from the daily amount, days,
1913 -- receipt amount and rate info that we have so far.
1914
1915 BEGIN
1916 -- Bug 2103589: since the receipt amt is already validated above, we just use it
1917 if ((l_receiptAmount is not null) AND (l_receiptAmount <> 0)) then
1918 l_num := l_receiptAmount;
1919 elsif ((l_dailyAmount is not null) AND (l_dailyAmount <> 0)) then
1920 l_num := l_dailyAmount * to_number(p_report_line_info.days);
1921 else
1922 l_num := null;
1923 end if;
1924
1925 if (p_report_line_info.rate <> l_fixed_msg) THEN
1926 l_rate := to_number(nvl(p_report_line_info.rate, '1'));
1927 ELSE
1928 l_rate := 1;
1929 END IF;
1930
1931 if ((l_rate > 0) AND (l_num is not null)) then
1932 debug_info := 'before calc l_amount';
1933 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1934 IF (l_is_fixed_rate = 'Y') THEN
1935 l_amount := GL_CURRENCY_API.convert_amount(
1936 p_report_line_info.currency_code,
1937 p_report_header_info.reimbursement_currency_code,
1938 l_acdate,
1939 null,
1940 l_num);
1941 ELSE
1942 /* bugs 761336 and 1056403 */
1943 if (l_inverse_rate_profile = 'Y') then
1944 l_amount := ROUND(l_num/l_rate,p_reimbcurr_precision);
1945 else
1946 l_amount := ROUND(l_num * l_rate, p_reimbcurr_precision);
1947 end if; /* l_inverse_rate_profile = 'Y' */
1948 l_orig_num := l_num;
1949
1950 /*** Round l_amount here. ***/
1951 END IF; /* if (l_is_fixed_rate = 'Y') */
1952 else
1953 debug_info := 'l_amount is null';
1954 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', debug_info);
1955 l_amount := null;
1956 end if; /* l_rate > 0 */
1957
1958 EXCEPTION
1959 WHEN OTHERS then
1960 -- Error should have been reported ealier. Do nothing here.
1961 NULL;
1962 END;
1963 --
1964 -- If it's not null, check if it's a valid number >= 0
1965 --
1966 if (p_report_line_info.amount is not null and
1967 p_report_line_info.itemization_parent_id <> '-1') then
1968 BEGIN
1969 l_num := AP_WEB_DB_UTIL_PKG.CharToNumber(p_report_line_info.amount);
1970 if CheckNum(l_num, p_exp_error, p_receiptcount,
1971 C_Amount_Prompt, p_allow_credit_lines, FALSE) then
1972 --
1973 -- If Amount does not match value calculated from Daily amount,
1974 -- Days, receipt amount and rate, report error.
1975 -- Use format masks???
1976 --
1977 --
1978 -- Corner case: user enters total amount only and leaves other
1979 -- amount as zero. (bug# 572569)
1980 --
1981 if (l_amount is null) then -- note that l_amount can't be 0
1982 l_amount := l_num;
1983 end if;
1984
1985 -- For disconnected. When triangulation is involved.
1986 IF (l_is_fixed_rate = 'Y') THEN
1987 IF (NOT WithinTolerance(l_num, l_amount)) THEN
1988 l_num := l_amount;
1989 l_fixed_rate_applied := 'Y';
1990 p_report_line_info.amount := TO_CHAR(l_num);
1991 END IF;
1992 END IF;
1993
1994 -- bug 6075479 do not validate amount for itemized lines
1995 if ((l_num <> 0) AND (p_report_line_info.itemization_parent_id < 1)
1996 AND (NOT WithinTolerance(l_num, l_amount))) then
1997 --added for bug 1056403
1998 IF (l_inverse_rate_profile = 'Y') THEN
1999 l_cal_amount_method := '/';
2000 ELSE
2001 l_cal_amount_method := '*';
2002 END IF;
2003
2004 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_TOTAL_AMT_INCORRECT');
2005 FND_MESSAGE.SET_TOKEN('Total_Amount',
2006 p_report_line_info.amount);
2007 FND_MESSAGE.SET_TOKEN('receipt_amount',to_char(l_orig_num));
2008 FND_MESSAGE.SET_TOKEN('amount_method',l_cal_amount_method);
2009 FND_MESSAGE.SET_TOKEN('rate',to_char(l_rate));
2010 FND_MESSAGE.SET_TOKEN('result',to_char(l_amount));
2011
2012 -- JMARY Have to create a new message for this error
2013 AP_WEB_UTILITIES_PKG.AddExpError(
2014 p_exp_error,
2015 fnd_message.get_encoded(),
2016 -- || TO_CHAR(l_orig_num) || l_cal_amount_method || TO_CHAR(l_rate) || '=' ||TO_CHAR(l_amount) ,
2017 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
2018 C_Amount_Prompt,
2019 p_receiptcount,
2020 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
2021
2022 elsif (l_num = 0) then
2023 -- It could be that 0 was pre-seeded in the spreadsheet. But
2024 -- need to make sure that the calculated amount is not zero.
2025 if (l_amount is not null) then
2026 p_report_line_info.amount := to_char(l_amount);
2027 end if;
2028 end if;
2029
2030 /*
2031 IF (l_fixed_rate_applied = 'Y') THEN
2032 fnd_message.set_name('SQLAP','AP_WEB_FIXED_RATE_APPLIED');
2033 AP_WEB_UTILITIES_PKG.AddExpError(P_Exp_Error,
2034 fnd_message.get_encoded(),
2035 AP_WEB_UTILITIES_PKG.C_WarningMessageType,
2036 C_Rate_Prompt,
2037 p_receiptcount,
2038 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
2039
2040 END IF;
2041 */
2042 end if; /* CheckNum */
2043 EXCEPTION
2044 when OTHERS then
2045 FND_MESSAGE.SET_NAME('SQLAP', 'AP_WEB_TOTAL_AMOUNT_INVALID');
2046 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
2047 fnd_message.get_encoded(),
2048 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
2049 C_Amount_Prompt,
2050 p_receiptcount,
2051 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
2052 END;
2053 --
2054 -- If it's null, report error if daily amount and receipt amount are
2055 -- also null. Otherwise, try to calculate total amount.
2056 --
2057 else
2058 if ((p_report_line_info.amount is null) AND
2059 (p_report_line_info.receipt_amount is null)) then
2060 fnd_message.set_name('SQLAP', 'AP_WEB_TOTAL_REQUIRED');
2061 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
2062 fnd_message.get_encoded(),
2063 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
2064 C_Amount_Prompt,
2065 p_receiptcount,
2066 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
2067 else
2068 if (p_report_header_info.transaction_currency_type = 'reimbursement') then
2069 BEGIN
2070 l_num := to_number(p_report_line_info.amount);
2071 if (l_num > 0) then
2072 p_report_line_info.amount :=
2073 to_char(l_num * to_number(p_report_line_info.days));
2074 end if;
2075 EXCEPTION
2076 when OTHERS then
2077 NULL; /* checked DAmount and Days already, so sth useless. */
2078 END;
2079 else
2080 p_report_line_info.amount := to_char(l_amount);
2081 end if; /* trans */
2082 end if;
2083 end if; /* if Amount is not null */
2084 end if; /* if (NOT(p_calculate_receipt_index > 0)) */
2085
2086 --- Check if at least one expense type is selected. This should only
2087 --- be relavent in the disconnected case.
2088 --- Quan 1/22/99: Since this check is done in apwdiscb for disconnected report,
2089 --- we commented this out for bug 729876
2090 /*
2091 debug_info := 'Expense type';
2092 BEGIN
2093 if (xtype_array(p_receiptcount) is null) then
2094 debug_info := 'No expense type selected';
2095 fnd_message.set_name('SQLAP', 'AP_WEB_EXPENSE_TYPE_REQUIRED');
2096 AP_WEB_UTILITIES_PKG.AddExpError(p_exp_error,
2097 fnd_message.get_encoded(),
2098 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
2099 C_ExpType_Prompt,
2100 p_receiptcount,
2101 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
2102 end if;
2103 END;
2104 */
2105
2106 -------------------------------------------------------
2107 -- check if justification is required
2108 -------------------------------------------------------
2109 if (p_report_line_info.parameter_id is not null) then
2110 if (p_report_line_info.justification is null) then
2111 -- Is justification required?
2112 i := 1;
2113 loop
2114 if (i > p_justreq_array.COUNT) then
2115 exit;
2116 end if;
2117 if (to_number(p_report_line_info.parameter_id) = p_justreq_array(i)) then
2118
2119 fnd_message.set_name('SQLAP', 'AP_WEB_JUSTIFICATION_REQUIRED');
2120 AP_WEB_UTILITIES_PKG.AddExpError(P_Exp_Error,
2121 fnd_message.get_encoded(),
2122 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
2123 C_Just_Prompt,
2124 p_receiptcount,
2125 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, l_IsMobileApp);
2126 exit;
2127 end if;
2128 i := i + 1;
2129 end loop;
2130 end if; /* justif is null? */
2131 end if; -- xtype is not null
2132
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135 BEGIN
2136 IF (SQLCODE <> -20001) THEN
2137 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2138 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2139 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
2140 'ValidateExpLineCoreFields');
2141 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2142 'None passed.');
2143 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2144 END IF;
2145
2146 APP_EXCEPTION.RAISE_EXCEPTION;
2147 END;
2148 END validateExpLineCoreFields;
2149
2150 /*----------------------------------------------------------------------------*
2151 | Procedure |
2152 | ValidateExplines |
2153 | |
2154 | DESCRIPTION |
2155 | Server-Side validation for multiple receipt lines |
2156 | |
2157 | ASSUMPTION |
2158 | |
2159 | |
2160 | PARAMETERS |
2161 | |
2162 | RETURNS |
2163 | none |
2164 *----------------------------------------------------------------------------*/
2165
2166 PROCEDURE ValidateExpLines(
2167 p_userId IN NUMBER,
2168 p_report_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
2169 p_report_line_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
2170 p_custom1_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2171 p_custom2_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2172 p_custom3_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2173 p_custom4_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2174 p_custom5_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2175 p_custom6_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2176 p_custom7_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2177 p_custom8_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2178 p_custom9_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2179 p_custom10_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2180 p_custom11_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2181 p_custom12_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2182 p_custom13_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2183 p_custom14_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2184 p_custom15_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2185 p_has_core_field_errors OUT NOCOPY BOOLEAN,
2186 p_has_custom_field_errors OUT NOCOPY BOOLEAN,
2187 p_receipts_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack,
2188 p_receipts_with_errors_count IN OUT NOCOPY BINARY_INTEGER,
2189 p_IsSessionProjectEnabled IN VARCHAR2,
2190 p_calculate_receipt_index IN BINARY_INTEGER DEFAULT NULL,
2191 p_bCalling_from_disconnected IN BOOLEAN,
2192 p_addon_rates IN OIE_ADDON_RATES_T DEFAULT NULL,
2193 p_report_line_id IN NUMBER DEFAULT NULL,
2194 p_daily_breakup_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
2195 p_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
2196 p_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
2197 p_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
2198 p_number_of_meals IN OIE_PDM_NUMBER_T DEFAULT NULL,
2199 p_meals_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
2200 p_breakfast_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
2201 p_lunch_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
2202 p_dinner_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
2203 p_accommodation_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
2204 p_accommodation_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
2205 p_hotel_name IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
2206 p_night_rate_Type IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
2207 p_night_rate_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
2208 p_pdm_rate IN OIE_PDM_NUMBER_T DEFAULT NULL,
2209 p_rate_Type_code IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
2210 p_pdm_breakup_dest_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
2211 p_pdm_destination_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
2212 p_dest_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
2213 p_dest_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
2214 p_location_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
2215 p_cust_meals_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
2216 p_cust_accommodation_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
2217 p_cust_night_rate_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
2218 p_cust_pdm_rate IN OUT NOCOPY OIE_PDM_NUMBER_T
2219 )
2220 IS
2221 l_receipt_count NUMBER;
2222 l_report_line_rec AP_WEB_DFLEX_PKG.ExpReportLineRec;
2223 l_receipt_with_error NUMBER := 0;
2224
2225 BEGIN
2226
2227 l_receipt_count := TO_NUMBER(p_report_header_info.receipt_count);
2228
2229 -- For core case, do NOT assume that the error table is empty. Certain
2230 -- errors are checked while processing the uploaded report in the discon.
2231 -- case.
2232
2233 -- Whatever in p_receipts_errors should belong to core error stack.
2234 -- The only time it could be populated is during disconnected processing.
2235 -- Other times it should be given null since it'll be called after
2236 -- String2*. If not so, make sure to initialize it before calling this
2237 -- procedure.
2238 --
2239
2240 -- Clear p_receipts_errors_custom and p_receipts_errors
2241 AP_WEB_UTILITIES_PKG.InitMessages(l_receipt_count, p_receipts_errors);
2242
2243 FOR i IN 1 .. l_Receipt_Count LOOP
2244 l_report_line_rec := p_report_line_info(i);
2245
2246 AP_WEB_VALIDATE_UTIL.ValidateExpLine(
2247 p_userId,
2248 p_report_header_info => p_report_header_info,
2249 p_report_line_info => l_report_line_rec,
2250 p_custom1_array => p_Custom1_Array,
2251 p_custom2_array => p_Custom2_Array,
2252 p_custom3_array => p_Custom3_Array,
2253 p_custom4_array => p_Custom4_Array,
2254 p_custom5_array => p_Custom5_Array,
2255 p_custom6_array => p_Custom6_Array,
2256 p_custom7_array => p_Custom7_Array,
2257 p_custom8_array => p_Custom8_Array,
2258 p_custom9_array => p_Custom9_Array,
2259 p_custom10_array => p_Custom10_Array,
2260 p_custom11_array => p_Custom11_Array,
2261 p_custom12_array => p_Custom12_Array,
2262 p_custom13_array => p_Custom13_Array,
2263 p_custom14_array => p_Custom14_Array,
2264 p_custom15_array => p_Custom15_Array,
2265 p_has_core_field_errors => p_has_core_field_errors,
2266 p_has_custom_field_errors => p_has_custom_field_errors,
2267 p_receipts_errors => p_receipts_errors,
2268 p_receipts_with_errors_count => p_receipts_with_errors_count,
2269 p_IsSessionProjectEnabled => p_IsSessionProjectEnabled,
2270 p_receipt_index => i,
2271 p_calculate_receipt_index => NULL,
2272 p_bCalling_from_disconnected => p_bCalling_from_disconnected,
2273 p_addon_rates => p_addon_rates,
2274 p_report_line_id => p_report_line_id,
2275 p_daily_breakup_id => p_daily_breakup_id,
2276 p_start_date => p_start_date,
2277 p_end_date => p_end_date,
2278 p_amount => p_amount,
2279 p_number_of_meals => p_number_of_meals,
2280 p_meals_amount => p_meals_amount,
2281 p_breakfast_flag => p_breakfast_flag,
2282 p_lunch_flag => p_lunch_flag,
2283 p_dinner_flag => p_dinner_flag,
2284 p_accommodation_amount => p_accommodation_amount,
2285 p_accommodation_flag => p_accommodation_flag,
2286 p_hotel_name => p_hotel_name,
2287 p_night_rate_Type => p_night_rate_type,
2288 p_night_rate_amount => p_night_rate_amount,
2289 p_pdm_rate => p_pdm_rate,
2290 p_rate_Type_code => p_rate_type_code,
2291 p_pdm_breakup_dest_id => p_pdm_breakup_dest_id,
2292 p_pdm_destination_id => p_pdm_destination_id,
2293 p_dest_start_date => p_dest_start_date,
2294 p_dest_end_date => p_dest_end_date,
2295 p_location_id => p_location_id,
2296 p_cust_meals_amount => p_cust_meals_amount,
2297 p_cust_accommodation_amount => p_cust_accommodation_amount,
2298 p_cust_night_rate_amount => p_cust_night_rate_amount,
2299 p_cust_pdm_rate => p_cust_pdm_rate
2300 );
2301
2302 p_report_line_info(i):= l_report_line_rec ;
2303
2304 l_receipt_with_error := l_receipt_with_error +p_receipts_with_errors_count ;
2305
2306 END LOOP;
2307 p_receipts_with_errors_count := l_receipt_with_error;
2308 END ValidateExplines;
2309
2310 /*----------------------------------------------------------------------------*
2311 | Procedure |
2312 | ValidateExpline |
2313 | |
2314 | DESCRIPTION |
2315 | Server-Side validation for single receipt line |
2316 | |
2317 | ASSUMPTION |
2318 | p_report_header_info.number_max_flexfield has been set |
2319 | p_report_header_info.summary_start_date has been set |
2320 | p_report_header_info.summary_end_date has been set |
2321 | |
2322 | PARAMETERS |
2323 | |
2324 | RETURNS |
2325 | none |
2326 *----------------------------------------------------------------------------*/
2327
2328 PROCEDURE ValidateExpLine(
2329 p_userId IN NUMBER,
2330 p_report_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
2331 p_report_line_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLineRec,
2332 p_custom1_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2333 p_custom2_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2334 p_custom3_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2335 p_custom4_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2336 p_custom5_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2337 p_custom6_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2338 p_custom7_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2339 p_custom8_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2340 p_custom9_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2341 p_custom10_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2342 p_custom11_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2343 p_custom12_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2344 p_custom13_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2345 p_custom14_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2346 p_custom15_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2347 p_has_core_field_errors OUT NOCOPY BOOLEAN,
2348 p_has_custom_field_errors OUT NOCOPY BOOLEAN,
2349 p_receipts_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack,
2350 p_receipts_with_errors_count IN OUT NOCOPY BINARY_INTEGER,
2351 p_IsSessionProjectEnabled IN VARCHAR2,
2352 p_receipt_index IN BINARY_INTEGER, --needed to reference CustomN_Array
2353 p_calculate_receipt_index IN BINARY_INTEGER DEFAULT NULL,
2354 p_bCalling_from_disconnected IN BOOLEAN,
2355 p_addon_rates IN OIE_ADDON_RATES_T,
2356 p_report_line_id IN NUMBER DEFAULT NULL,
2357 p_daily_breakup_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
2358 p_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
2359 p_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
2360 p_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
2361 p_number_of_meals IN OIE_PDM_NUMBER_T DEFAULT NULL,
2362 p_meals_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
2363 p_breakfast_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
2364 p_lunch_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
2365 p_dinner_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
2366 p_accommodation_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
2367 p_accommodation_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
2368 p_hotel_name IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
2369 p_night_rate_Type IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
2370 p_night_rate_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
2371 p_pdm_rate IN OIE_PDM_NUMBER_T DEFAULT NULL,
2372 p_rate_Type_code IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
2373 p_pdm_breakup_dest_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
2374 p_pdm_destination_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
2375 p_dest_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
2376 p_dest_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
2377 p_location_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
2378 p_cust_meals_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
2379 p_cust_accommodation_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
2380 p_cust_night_rate_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
2381 p_cust_pdm_rate IN OUT NOCOPY OIE_PDM_NUMBER_T
2382 )
2383 ------------------------------------------------------------------------
2384 IS
2385 l_receipts_with_errors_core BINARY_INTEGER;
2386 l_receipts_with_errors_custom BINARY_INTEGER;
2387 l_errors AP_WEB_UTILITIES_PKG.expError;
2388 l_errors_custom AP_WEB_UTILITIES_PKG.expError;
2389
2390 V_IsSessionTaxEnabled VARCHAR2(1);
2391
2392 l_debug_info VARCHAR2(2000);
2393
2394 l_recCount INTEGER;
2395
2396 l_allow_credit_lines_profile VARCHAR2(1) := 'N';
2397 l_allow_credit_lines BOOLEAN;
2398
2399 l_curr_precision_cursor AP_WEB_DB_COUNTRY_PKG.CurrencyPrecisionCursor;
2400 l_reimbcurr_precision AP_WEB_DB_COUNTRY_PKG.curr_precision;
2401
2402 V_SysInfoRec AP_WEB_DB_AP_INT_PKG.APSysInfoRec; -- For PATC: Exchange rate type in AP and Functional currency
2403 V_EndExpenseDate DATE; -- For PATC: Latest receipt date
2404 V_DefaultExchangeRate NUMBER; -- For PATC: Exchange rate for func->reimb
2405 -- on latest receipt date
2406 V_DateFormat VARCHAR2(30);
2407
2408 I INTEGER;
2409 l_DataDefaultedUpdateable BOOLEAN;
2410
2411 BEGIN
2412
2413 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_VALIDATE_UTIL', 'start validateExpLine');
2414
2415 AP_WEB_DFLEX_PKG.IsSessionTaxEnabled(
2416 V_IsSessionTaxEnabled,
2417 p_userId); -- 2242176, fnd user id
2418
2419 -- validate core lines fields
2420 l_debug_info := 'ValidateExpLinesCoreFields';
2421 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
2422
2423 -- Bug 2204539: always allow credit line for credit card receipt
2424 if (p_report_line_info.cCardTrxnId is null) then
2425 l_allow_credit_lines_profile := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
2426 p_name => 'AP_WEB_ALLOW_CREDIT_LINES',
2427 p_user_id => p_userId,
2428 p_resp_id => null,
2429 p_apps_id => null);
2430 if (l_allow_credit_lines_profile = 'Y') then
2431 l_allow_credit_lines := TRUE;
2432 else
2433 l_allow_credit_lines := FALSE;
2434 end if;
2435 else
2436 l_allow_credit_lines := TRUE;
2437 end if;
2438
2439 l_reimbcurr_precision := AP_WEB_DB_COUNTRY_PKG.GetCurrencyPrecision(
2440 p_report_header_info.reimbursement_currency_code);
2441
2442
2443 AP_WEB_VALIDATE_UTIL.ValidateExpLineCoreFields(
2444 p_userId, -- 2242176
2445 p_report_header_info,
2446 p_report_line_info,
2447 p_receipt_index,
2448 l_allow_credit_lines,
2449 C_justreq_array,
2450 l_reimbcurr_precision,
2451 p_calculate_receipt_index,
2452 l_errors);
2453
2454 l_receipts_with_errors_core := l_errors.COUNT;
2455
2456
2457 -- validate flexfields
2458 l_debug_info := 'ValidateExpLinesCustomFields';
2459 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
2460 l_receipts_with_errors_custom := 0;
2461
2462 -- The following calcuations marked with "For PATC" were
2463 -- added for the R11i support for multicurrency in PA.
2464 -- We need to retrieve currency and exchange rate information
2465 -- before calling PATC.
2466
2467 -- For PATC: Used when doing projects verification
2468 --Bug 3336823
2469 V_DateFormat := nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'));
2470
2471
2472 -- For PATC: Get information about functional currency and exchange
2473 -- rate for the last receipt date. The last receipt date will be
2474 -- equal to sysdate.
2475 l_debug_info := 'Getting functional currency and exchange rate info';
2476 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
2477
2478 -- for bug 3452403, get default currency information from header
2479 V_SysInfoRec.base_currency := p_report_header_info.default_currency_code;
2480 V_SysInfoRec.default_exchange_rate_type := p_report_header_info.default_exchange_rate_type;
2481
2482 -- For PATC: Get the default exchange rate for the summary_end_date
2483 -- reimbursement currency/functional currency
2484 -- We are only calling this once for all receipts
2485 V_DefaultExchangeRate := AP_UTILITIES_PKG.get_exchange_rate(
2486 V_SysInfoRec.base_currency,
2487 p_report_header_info.reimbursement_currency_code,
2488 V_SysInfoRec.default_exchange_rate_type,
2489 p_report_header_info.summary_end_date,
2490 'ValidatePATransaction');
2491
2492 l_receipts_with_errors_custom := 0;
2493
2494 AP_WEB_VALIDATE_UTIL.ValidateExpLineCustomFields(
2495 p_userId,
2496 p_report_header_info,
2497 p_report_line_info,
2498 p_receipt_index,
2499 V_SysInfoRec,
2500 V_DefaultExchangeRate,
2501 p_report_header_info.summary_end_date,
2502 V_DateFormat,
2503 p_custom1_array,
2504 p_custom2_array,
2505 p_custom3_array,
2506 p_custom4_array,
2507 p_custom5_array,
2508 p_custom6_array,
2509 p_custom7_array,
2510 p_custom8_array,
2511 p_custom9_array,
2512 p_custom10_array,
2513 p_custom11_array,
2514 p_custom12_array,
2515 p_custom13_array,
2516 p_custom14_array,
2517 p_custom15_array,
2518 V_IsSessionTaxEnabled,
2519 p_IsSessionProjectEnabled,
2520 p_receipts_errors,
2521 p_calculate_receipt_index,
2522 l_errors_custom,
2523 l_receipts_with_errors_custom,
2524 l_DataDefaultedUpdateable,
2525 p_bCalling_from_disconnected,
2526 false,
2527 p_addon_rates,
2528 p_report_line_id,
2529 p_daily_breakup_id,
2530 p_start_date,
2531 p_end_date,
2532 p_amount,
2533 p_number_of_meals,
2534 p_meals_amount,
2535 p_breakfast_flag,
2536 p_lunch_flag,
2537 p_dinner_flag,
2538 p_accommodation_amount,
2539 p_accommodation_flag,
2540 p_hotel_name,
2541 p_night_rate_Type,
2542 p_night_rate_amount,
2543 p_pdm_rate,
2544 p_rate_Type_code,
2545 p_pdm_breakup_dest_id,
2546 p_pdm_destination_id,
2547 p_dest_start_date,
2548 p_dest_end_date,
2549 p_location_id,
2550 p_cust_meals_amount,
2551 p_cust_accommodation_amount,
2552 p_cust_night_rate_amount,
2553 p_cust_pdm_rate
2554 );
2555
2556 -- bug#2188075 - Updated the amount, display a warning to the user
2557 IF (l_DataDefaultedUpdateable) THEN
2558 fnd_message.set_name('SQLAP', 'OIE_DATA_CALCULATED_DIFFER');
2559 AP_WEB_UTILITIES_PKG.AddExpError(l_errors_custom,
2560 fnd_message.get_encoded(),
2561 AP_WEB_UTILITIES_PKG.C_WarningMessageType,
2562 'FlexField',
2563 p_receipt_index,
2564 AP_WEB_UTILITIES_PKG.C_DFFMessageCategory);
2565 END IF;
2566
2567
2568 -- determine whether there were errors in the custom field
2569 p_has_core_field_errors := (l_receipts_with_errors_core > 0);
2570 p_has_custom_field_errors := (l_receipts_with_errors_custom > 0);
2571
2572 l_debug_info := 'merge error stacks';
2573 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
2574
2575 AP_WEB_UTILITIES_PKG.MergeExpErrors(l_errors,
2576 l_errors_custom);
2577
2578 AP_WEB_UTILITIES_PKG.MergeErrors(l_errors,
2579 p_receipts_errors);
2580
2581 p_receipts_with_errors_count :=
2582 AP_WEB_UTILITIES_PKG.NumOfReceiptWithError(p_receipts_errors);
2583
2584
2585 EXCEPTION
2586 WHEN OTHERS THEN
2587 BEGIN
2588 IF (SQLCODE <> -20001) THEN
2589 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2590 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2591 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
2592 'ValidateExpLine');
2593 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2594 'None passed.');
2595 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2596 END IF;
2597 APP_EXCEPTION.RAISE_EXCEPTION;
2598 END;
2599 END ValidateExpLine;
2600
2601 PROCEDURE MapColumnToCustomFields(
2602 p_userId IN NUMBER,
2603 P_ReceiptIndex IN NUMBER,
2604 Attribute_Array IN AP_WEB_PARENT_PKG.BigString_Array,
2605 ExpReportLinesInfo IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
2606 Custom1_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2607 Custom2_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2608 Custom3_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2609 Custom4_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2610 Custom5_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2611 Custom6_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2612 Custom7_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2613 Custom8_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2614 Custom9_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2615 Custom10_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2616 Custom11_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2617 Custom12_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2618 Custom13_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2619 Custom14_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2620 Custom15_Array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A)
2621 IS
2622
2623 l_CustomFieldsForOneReceipt AP_WEB_DFLEX_PKG.CustomFields_A;
2624 l_CustomField AP_WEB_DFLEX_PKG.CustomFieldRec;
2625 I NUMBER;
2626 l_NumGlobalEnabledSegs NUMBER;
2627 l_NumContextEnabledSegs NUMBER;
2628 l_DFlexfield FND_DFLEX.DFLEX_R;
2629 l_DFlexinfo FND_DFLEX.DFLEX_DR;
2630 l_DFlexfieldContexts FND_DFLEX.CONTEXTS_DR;
2631 l_ContextIndex NUMBER;
2632
2633 -- Error
2634 l_CurrentCallingSequence VARCHAR2(240);
2635 l_DebugInfo VARCHAR2(240);
2636
2637 PROCEDURE MapColToField(
2638 Attribute_Array IN AP_WEB_PARENT_PKG.BigString_Array,
2639 P_ColumnMapping IN VARCHAR2,
2640 P_TargetValue OUT NOCOPY VARCHAR2)
2641 IS
2642 BEGIN
2643
2644 IF P_ColumnMapping = 'ATTRIBUTE1' THEN
2645 P_TargetValue := Attribute_Array(1);
2646 ELSIF P_ColumnMapping = 'ATTRIBUTE2' THEN
2647 P_TargetValue := Attribute_Array(2);
2648 ELSIF P_ColumnMapping = 'ATTRIBUTE3' THEN
2649 P_TargetValue := Attribute_Array(3);
2650 ELSIF P_ColumnMapping = 'ATTRIBUTE4' THEN
2651 P_TargetValue := Attribute_Array(4);
2652 ELSIF P_ColumnMapping = 'ATTRIBUTE5' THEN
2653 P_TargetValue := Attribute_Array(5);
2654 ELSIF P_ColumnMapping = 'ATTRIBUTE6' THEN
2655 P_TargetValue := Attribute_Array(6);
2656 ELSIF P_ColumnMapping = 'ATTRIBUTE7' THEN
2657 P_TargetValue := Attribute_Array(7);
2658 ELSIF P_ColumnMapping = 'ATTRIBUTE8' THEN
2659 P_TargetValue := Attribute_Array(8);
2660 ELSIF P_ColumnMapping = 'ATTRIBUTE9' THEN
2661 P_TargetValue := Attribute_Array(9);
2662 ELSIF P_ColumnMapping = 'ATTRIBUTE10' THEN
2663 P_TargetValue := Attribute_Array(10);
2664 ELSIF P_ColumnMapping = 'ATTRIBUTE11' THEN
2665 P_TargetValue := Attribute_Array(11);
2666 ELSIF P_ColumnMapping = 'ATTRIBUTE12' THEN
2667 P_TargetValue := Attribute_Array(12);
2668 ELSIF P_ColumnMapping = 'ATTRIBUTE13' THEN
2669 P_TargetValue := Attribute_Array(13);
2670 ELSIF P_ColumnMapping = 'ATTRIBUTE14' THEN
2671 P_TargetValue := Attribute_Array(14);
2672 ELSIF P_ColumnMapping = 'ATTRIBUTE15' THEN
2673 P_TargetValue := Attribute_Array(15);
2674 END IF;
2675
2676 END MapColToField;
2677
2678 BEGIN
2679
2680 l_CurrentCallingSequence := 'AP_WEB_VIEW_RECEIPTS_PKG';
2681
2682 -- Initialize l_CustomFieldsForOneReceipt
2683 l_DebugInfo := 'Initialize l_CustomFieldsForOneReceipt';
2684 FOR I IN 1..15 LOOP
2685 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(l_CustomFieldsForOneReceipt(I));
2686 END LOOP;
2687
2688 -- Get information about custom fields
2689 l_DebugInfo := 'Get information about custom fields';
2690 AP_WEB_DFLEX_PKG.PopulateCustomFieldsInfo(
2691 p_userId => p_userId,
2692 p_exp_line_info => ExpReportLinesInfo(P_ReceiptIndex),
2693 p_custom_fields_array => l_CustomFieldsForOneReceipt,
2694 p_num_global_enabled_segs => l_NumGlobalEnabledSegs,
2695 p_num_context_enabled_segs => l_NumContextEnabledSegs,
2696 p_dflexfield => l_DFlexField,
2697 p_dflexinfo => l_DFlexInfo,
2698 p_dflexfield_contexts => l_DFlexFieldContexts,
2699 p_context_index => l_ContextIndex);
2700
2701 -- Map the columns to custom value
2702 l_DebugInfo := 'Map the columns to custom value';
2703 FOR I IN 1..15 LOOP
2704
2705 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(l_CustomFieldsForOneReceipt(I)) THEN
2706
2707 MapColToField(
2708 Attribute_Array,
2709 l_CustomFieldsForOneReceipt(I).column_mapping,
2710 l_CustomFieldsForOneReceipt(I).value);
2711 END IF;
2712
2713 END LOOP;
2714
2715 -- Propagate receipt info into the custom array
2716 l_DebugInfo := 'Propagate receipt info into the custom array';
2717 AP_WEB_DFLEX_PKG.PropogateReceiptCustFldsInfo(
2718 p_receipt_custom_fields_array => l_CustomFieldsForOneReceipt,
2719 p_receipt_index => P_ReceiptIndex,
2720 p_custom1_array => Custom1_Array,
2721 p_custom2_array => Custom2_Array,
2722 p_custom3_array => Custom3_Array,
2723 p_custom4_array => Custom4_Array,
2724 p_custom5_array => Custom5_Array,
2725 p_custom6_array => Custom6_Array,
2726 p_custom7_array => Custom7_Array,
2727 p_custom8_array => Custom8_Array,
2728 p_custom9_array => Custom9_Array,
2729 p_custom10_array => Custom10_Array,
2730 p_custom11_array => Custom11_Array,
2731 p_custom12_array => Custom12_Array,
2732 p_custom13_array => Custom13_Array,
2733 p_custom14_array => Custom14_Array,
2734 p_custom15_array => Custom15_Array);
2735
2736 EXCEPTION
2737 WHEN OTHERS THEN
2738 BEGIN
2739 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2740 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2741 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_CurrentCallingSequence);
2742 FND_MESSAGE.SET_TOKEN('PARAMETERS', '');
2743 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_DebugInfo);
2744 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
2745 END;
2746
2747 END MapColumnToCustomFields;
2748
2749
2750 PROCEDURE initJustificationRequiredArray IS
2751 l_debug_info VARCHAR2(2000);
2752 l_just_required_cursor AP_WEB_DB_EXPTEMPLATE_PKG.JustificationExpTypeCursor;
2753 i INTEGER;
2754 BEGIN
2755 l_debug_info := 'Fill justification required array';
2756 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_VALIDATE_UTIL', l_debug_info);
2757 IF (AP_WEB_DB_EXPTEMPLATE_PKG.GetJustifReqdExpTypesCursor(l_just_required_cursor)) THEN
2758 i := 1;
2759 LOOP
2760 FETCH l_just_required_cursor INTO C_justreq_array(i);
2761 EXIT when l_just_required_cursor%NOTFOUND;
2762 i := i + 1;
2763 END LOOP;
2764 END IF;
2765
2766 CLOSE l_just_required_cursor;
2767 EXCEPTION
2768 WHEN OTHERS THEN
2769 BEGIN
2770 IF (SQLCODE <> -20001) THEN
2771 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2772 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2773 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
2774 'initJustificationRequiredArray');
2775 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2776 'None passed.');
2777 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2778 END IF;
2779 APP_EXCEPTION.RAISE_EXCEPTION;
2780 END;
2781 END initJustificationRequiredArray;
2782 -----------------------------------------------------------------------------
2783
2784
2785 BEGIN -- Package initialization
2786 initJustificationRequiredArray;
2787
2788 END AP_WEB_VALIDATE_UTIL;