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