[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_OA_DISC_PKG
Source
1 PACKAGE BODY AP_WEB_OA_DISC_PKG AS
2 /* $Header: apwoadib.pls 120.19.12010000.3 2008/10/22 09:34:50 meesubra ship $ */
3
4 /* Constants */
5 C_Yes CONSTANT VARCHAR2(1) := 'Y';
6 C_No CONSTANT VARCHAR2(1) := 'N';
7
8 C_REQUIRED CONSTANT VARCHAR2(25) := 'REQUIRED';
9 C_ENABLED CONSTANT VARCHAR2(25) := 'ENABLED';
10
11 PROCEDURE AddLineNumbersToErrors (p_header_errors IN AP_WEB_UTILITIES_PKG.expError,
12 p_receipt_errors IN AP_WEB_UTILITIES_PKG.receipt_error_stack);
13
14 PROCEDURE CheckForReceiptWarnings (p_receipt_errors IN AP_WEB_UTILITIES_PKG.receipt_error_stack);
15
16 Procedure OAInsertTempData(
17 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
18 p_report_lines_info IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
19 p_receipt_errors IN AP_WEB_UTILITIES_PKG.receipt_error_stack,
20 Custom1_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
21 Custom2_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
22 Custom3_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
23 Custom4_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
24 Custom5_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
25 Custom6_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
26 Custom7_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
27 Custom8_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
28 Custom9_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
29 Custom10_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
30 Custom11_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
31 Custom12_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
32 Custom13_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
33 Custom14_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
34 Custom15_Array IN AP_WEB_DFLEX_PKG.CustomFields_A);
35
36 PROCEDURE ValidateRequiredProjectTask(
37 p_employee_id IN NUMBER, -- bug 2242176, employee's id
38 p_user_id IN NUMBER, -- bug 2242176, employee's user id
39 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
40 p_report_lines_info IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
41 p_receipts_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack);
42
43 PROCEDURE CheckForMileagePerDiemPolicy(
44 p_user_id IN NUMBER,
45 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
46 p_report_lines_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
47 p_header_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError,
48 p_receipts_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack);
49
50 PROCEDURE ValidateHeaderDFF(
51 p_user_id IN NUMBER,
52 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
53 p_header_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError);
54
55
56 /*
57 Written By
58 JMARY
59 Purpose
60 Procedure called by Import Page to display the Setup Errors and Data Validation Errors.
61 Input
62 p_exp
63 p_empid
64 Output
65 p_error_type
66 p_return_status
67 p_msg_count
68 p_msg_data
69 InputOutput
70 None
71 Assumptions
72 The application is WEB
73 Date
74 01-DEC-2000
75 */
76
77 Procedure OAExpReport(
78 p_exp IN VARCHAR2,
79 p_empid IN VARCHAR2,
80 p_receipt_count OUT NOCOPY NUMBER,
81 p_receipt_with_error OUT NOCOPY NUMBER,
82 p_error_type OUT NOCOPY VARCHAR2,
83 p_return_status OUT NOCOPY VARCHAR2,
84 p_msg_count OUT NOCOPY NUMBER,
85 p_msg_data OUT NOCOPY VARCHAR2
86 ) IS
87
88 l_table AP_WEB_DISC_PKG.disc_prompts_table;
89
90 l_receipt_with_error NUMBER;
91 l_receipt_with_warning NUMBER;
92 l_debug_info VARCHAR2(300) := '';
93 l_current_calling_sequence VARCHAR2(255) := 'OAExpReport';
94 l_receipt_count NUMBER;
95 l_employee_name PER_WORKFORCE_X.full_name%TYPE;
96 l_targ_emp_id number;
97 l_employee_num PER_WORKFORCE_X.employee_number%TYPE ;
98 l_cost_center VARCHAR2(150);
99
100 l_IsSessionProjectEnabled VARCHAR2(1);
101 Custom1_Array AP_WEB_DFLEX_PKG.CustomFields_A;
102 Custom2_Array AP_WEB_DFLEX_PKG.CustomFields_A;
103 Custom3_Array AP_WEB_DFLEX_PKG.CustomFields_A;
104 Custom4_Array AP_WEB_DFLEX_PKG.CustomFields_A;
105 Custom5_Array AP_WEB_DFLEX_PKG.CustomFields_A;
106 Custom6_Array AP_WEB_DFLEX_PKG.CustomFields_A;
107 Custom7_Array AP_WEB_DFLEX_PKG.CustomFields_A;
108 Custom8_Array AP_WEB_DFLEX_PKG.CustomFields_A;
109 Custom9_Array AP_WEB_DFLEX_PKG.CustomFields_A;
110 Custom10_Array AP_WEB_DFLEX_PKG.CustomFields_A;
111 Custom11_Array AP_WEB_DFLEX_PKG.CustomFields_A;
112 Custom12_Array AP_WEB_DFLEX_PKG.CustomFields_A;
113 Custom13_Array AP_WEB_DFLEX_PKG.CustomFields_A;
114 Custom14_Array AP_WEB_DFLEX_PKG.CustomFields_A;
115 Custom15_Array AP_WEB_DFLEX_PKG.CustomFields_A;
116 l_has_core_field_errors BOOLEAN;
117 l_has_custom_field_errors BOOLEAN;
118 l_report_header_info AP_WEB_DFLEX_PKG.ExpReportHeaderRec;
119 l_report_lines_info AP_WEB_DFLEX_PKG.ExpReportLines_A;
120 l_temp_errors AP_WEB_DISC_PKG.Setup_error_stack;
121
122 l_DataDefaultedUpdateable BOOLEAN;
123 l_emp_id AP_WEB_DB_HR_INT_PKG.empCurrent_employeeID;
124
125 l_receipt_line_errors AP_WEB_UTILITIES_PKG.receipt_error_stack;
126 l_validate_receipt_errors AP_WEB_UTILITIES_PKG.receipt_error_stack;
127 l_parse_header_errors AP_WEB_UTILITIES_PKG.expError;
128 l_parse_receipt_errors AP_WEB_UTILITIES_PKG.receipt_error_stack;
129 l_errortype VARCHAR2(1) ;
130 l_techstack VARCHAR2(1) := AP_WEB_DISC_PKG.C_NewStack;
131 l_userId VARCHAR2(20):= null;
132 l_temp_array OIE_PDM_NUMBER_T; -- bug 5358186
133
134 BEGIN
135 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_DISC_PKG', 'start OAExpReport');
136
137 ------------------------------------------------------
138 l_debug_info := 'Initalize global message table';
139 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
140 ------------------------------------------------------
141 fnd_msg_pub.initialize;
142
143 ------------------------------------------------------
144 l_debug_info := 'Retrieve employee information';
145 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
146 ------------------------------------------------------
147 -- Retrieve internal employee id
148 l_targ_emp_id := p_empid;
149
150 -- Retrieve employee information
151 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(l_employee_name,
152 l_employee_num,
153 l_cost_center,
154 l_targ_emp_id);
155
156 -------------------------------------------------
157 l_debug_info := 'get userid';
158 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
159 --------------------------------------------------
160 AP_WEB_OA_MAINFLOW_PKG.GetUserID(p_empid, l_userId);
161
162 ------------------------------------------------------
163 l_debug_info := 'Initalize prompt array';
164 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
165 ------------------------------------------------------
166 AP_WEB_DISC_PKG.ap_web_init_prompts_array(TO_NUMBER(l_userId), l_table, l_temp_errors);
167
168 ------------------------------------------------------
169 l_debug_info := 'Check for prompt array errors';
170 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
171 ------------------------------------------------------
172 IF l_temp_errors.count > 0 THEN
173 fnd_message.set_name('SQLAP','AP_WEB_DISC_ZERO_PROMPTS');
174 fnd_msg_pub.add();
175 p_error_type := AP_WEB_DISC_PKG.C_SetupError;
176 return;
177 END IF;
178
179 ------------------------------------------------------
180 l_debug_info := 'Get more employee information';
181 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
182 ------------------------------------------------------
183 -- Determine whether project enabled
184
185 AP_WEB_PROJECT_PKG.IsSessionProjectEnabled(
186 p_empid, l_userId,
187 l_IsSessionProjectEnabled);
188
189 -- Get organization ID for employee if project enabled
190 IF ( l_IsSessionProjectEnabled = C_Yes ) THEN
191 l_emp_id := TO_NUMBER(p_empid);
192 IF ( AP_WEB_DB_HR_INT_PKG.GetEmpOrgId(
193 l_emp_id,
194 l_report_header_info.expenditure_organization_id) <> TRUE ) THEN
195 l_report_header_info.expenditure_organization_id := NULL;
196 END IF;
197 END IF;
198
199 -------------------------------------------------------
200 l_debug_info := 'parse exp report';
201 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
202 -------------------------------------------------------
203 l_report_header_info.employee_id := p_empid;
204 AP_WEB_DISC_PKG.ParseExpReport(
205 TO_NUMBER(l_userId), -- Bug 2242176, Employee FND user id
206 p_exp,
207 l_table,
208 l_cost_center,
209 l_IsSessionProjectEnabled,
210 l_report_header_info,
211 l_report_lines_info,
212 Custom1_Array,
213 Custom2_Array,
214 Custom3_Array,
215 Custom4_Array,
216 Custom5_Array,
217 Custom6_Array,
218 Custom7_Array,
219 Custom8_Array,
220 Custom9_Array,
221 Custom10_Array,
222 Custom11_Array,
223 Custom12_Array,
224 Custom13_Array,
225 Custom14_Array,
226 Custom15_Array,
227 l_DataDefaultedUpdateable,
228 l_parse_header_errors,
229 l_parse_receipt_errors,
230 l_errortype,
231 l_techstack
232 );
233
234 -------------------------------------------------------
235 l_debug_info := 'add line numbers to parse errors';
236 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
237 -------------------------------------------------------
238 AddLineNumbersToErrors (l_parse_header_errors, l_parse_receipt_errors);
239
240 -- fnd_msg_pub.count_and_get() returns the number of error messages in the table.
241 IF (l_errortype = AP_WEB_DISC_PKG.C_SetupError) THEN
242 fnd_msg_pub.count_and_get(p_count => p_msg_count,
243 p_data => p_msg_data);
244 p_error_type := l_errortype;
245 return;
246 END IF;
247
248 -- Bug 4064985 - Need to prevent the user to skip to review if there are any required
249 -- segments in Header level descriptive flexfield
250 AP_WEB_OA_DISC_PKG.ValidateHeaderDFF(
251 TO_NUMBER(l_userId),
252 l_report_header_info,
253 l_parse_header_errors);
254
255 -------------------------------------------------
256 l_debug_info := 'validate receipt lines';
257 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
258 --------------------------------------------------
259 l_receipt_count := TO_NUMBER(l_report_header_info.receipt_count);
260 p_receipt_count := l_receipt_count;
261 IF (l_receipt_count > 0 ) THEN
262
263 AP_WEB_VALIDATE_UTIL.ValidateExpLines(
264 TO_NUMBER(l_userId), -- bug 2242176, employee fnd user id
265 l_report_header_info,
266 l_report_lines_info,
267 Custom1_Array,
268 Custom2_Array,
269 Custom3_Array,
270 Custom4_Array,
271 Custom5_Array,
272 Custom6_Array,
273 Custom7_Array,
274 Custom8_Array,
275 Custom9_Array,
276 Custom10_Array,
277 Custom11_Array,
278 Custom12_Array,
279 Custom13_Array,
280 Custom14_Array,
281 Custom15_Array,
282 l_has_core_field_errors,
283 l_has_custom_field_errors,
284 l_validate_receipt_errors,
285 p_receipt_with_error,
286 l_IsSessionProjectEnabled,
287 NULL,
288 TRUE,
289 p_cust_meals_amount => l_temp_array,
290 p_cust_accommodation_amount => l_temp_array,
291 p_cust_night_rate_amount => l_temp_array,
292 p_cust_pdm_rate => l_temp_array );
293
294 -- delete reference to temp array as this is used for per diem only
295 -- disconnected solution currently doe snot support per diem
296 -- deleting prevents inadvertent data corruption
297
298 If l_temp_array IS NOT NULL THEN
299
300 l_temp_array.delete; -- bug 5358186
301
302 END IF;
303
304 -------------------------------------------------------
305 l_debug_info := 'Validate Foreign Currencies';
306 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
307 -------------------------------------------------------
308 -- 1966365: Checks whether foreign currencies have a rate of 1 or null
309 AP_WEB_DISC_PKG.ValidateForeignCurrencies(l_report_header_info,
310 l_report_lines_info, l_validate_receipt_errors);
311
312 -------------------------------------------------------
313 l_debug_info := 'Validate Required Fields';
314 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
315 -------------------------------------------------------
316 -- 2159879: Checks whether Required fields are present or not
317 AP_WEB_OA_DISC_PKG.ValidateRequiredProjectTask(TO_NUMBER(p_empid),
318 TO_NUMBER(l_userId),
319 l_report_header_info,
320 l_report_lines_info, l_validate_receipt_errors);
321
322 -------------------------------------------------------
323 l_debug_info := 'Check for Mileage / Per Diem / Policy';
324 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
325 -------------------------------------------------------
326 AP_WEB_OA_DISC_PKG.CheckForMileagePerDiemPolicy(
327 TO_NUMBER(l_userId),
328 l_report_header_info,
329 l_report_lines_info, l_parse_header_errors, l_validate_receipt_errors);
330
331 -------------------------------------------------------
332 l_debug_info := 'Merge parse errors and validate errors';
333 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
334 -------------------------------------------------------
335 AP_WEB_UTILITIES_PKG.MergeErrorStacks(l_receipt_count, l_parse_receipt_errors,
336 l_validate_receipt_errors, l_validate_receipt_errors);
337
338 -------------------------------------------------------
339 l_debug_info := 'calculate number of receipts with errors';
340 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
341 -------------------------------------------------------
342 --calculate the correct number of receipts with errors:
343 p_receipt_with_error := AP_WEB_UTILITIES_PKG.NumOfReceiptWithError(
344 l_validate_receipt_errors );
345
346 -------------------------------------------------------
347 l_debug_info := 'add line numbers to validation errors';
348 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
349 -------------------------------------------------------
350 AddLineNumbersToErrors (l_parse_header_errors, l_validate_receipt_errors);
351 END IF;
352
353 -----------------------------------------
354 l_debug_info := 'Set number of errors and error type';
355 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
356 ------------------------------------------
357 fnd_msg_pub.count_and_get(p_count => p_msg_count,
358 p_data => p_msg_data);
359
360 IF p_msg_count > 0 THEN
361 p_error_type := AP_WEB_DISC_PKG.C_DataError;
362 ELSE
363
364 -----------------------------------------
365 l_debug_info := 'Check for warnings';
366 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
367 ------------------------------------------
368 CheckForReceiptWarnings (l_validate_receipt_errors);
369
370 -----------------------------------------
371 l_debug_info := 'Add warnings to message stack';
372 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
373 ------------------------------------------
374 fnd_msg_pub.count_and_get(p_count => p_msg_count,
375 p_data => p_msg_data);
376
377 IF p_msg_count > 0 THEN
378 p_error_type := AP_WEB_DISC_PKG.C_Warning;
379 ELSE
380 p_error_type := AP_WEB_DISC_PKG.C_NoError;
381 END IF;
382 END IF;
383
384 -----------------------------------------
385 l_debug_info := 'Inserts into the Temporary Table';
386 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
387 ------------------------------------------
388 OAInsertTempData(
389 l_report_header_info,
390 l_report_lines_info,
391 l_validate_receipt_errors,
392 Custom1_Array,
393 Custom2_Array,
394 Custom3_Array,
395 Custom4_Array,
396 Custom5_Array,
397 Custom6_Array,
398 Custom7_Array,
399 Custom8_Array,
400 Custom9_Array,
401 Custom10_Array,
402 Custom11_Array,
403 Custom12_Array,
404 Custom13_Array,
405 Custom14_Array,
406 Custom15_Array);
407 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_DISC_PKG', 'end OAExpReport');
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 IF (SQLCODE <> -20001) THEN
412 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
413 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
414 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','OAExpReport');
415 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
416 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
417 END IF;
418 FND_MSG_PUB.ADD();
419 p_error_type := AP_WEB_DISC_PKG.C_SetupError;
420 FND_MSG_PUB.COUNT_AND_GET(p_count => p_msg_count,
421 p_data => p_msg_data);
422
423 End OAExpReport;
424
425 /*
426 Written By
427 JMARY
428 Purpose
429 Procedure called by OAExpReport to insert into the Temporary Tables .
430 Input
431 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
432 p_report_lines_info IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
433 p_receipt_errors IN AP_WEB_UTILITIES_PKG.receipt_error_stack,
434 Custom1_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
435 Custom2_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
436 Custom3_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
437 Custom4_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
438 Custom5_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
439 Custom6_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
440 Custom7_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
441 Custom8_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
442 Custom9_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
443 Custom10_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
444 Custom11_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
445 Custom12_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
446 Custom13_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
447 Custom14_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
448 Custom15_Array IN AP_WEB_DFLEX_PKG.CustomFields_A
449 Output
450 p_error_type
451 p_return_status
452 p_msg_count
453 p_msg_data
454 InputOutput
455 None
456 Assumptions
457 None
458 Date
459 01-DEC-2000
460 */
461
462 Procedure OAInsertTempData(
463 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
464 p_report_lines_info IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
465 p_receipt_errors IN AP_WEB_UTILITIES_PKG.receipt_error_stack,
466 Custom1_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
467 Custom2_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
468 Custom3_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
469 Custom4_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
470 Custom5_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
471 Custom6_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
472 Custom7_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
473 Custom8_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
474 Custom9_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
475 Custom10_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
476 Custom11_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
477 Custom12_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
478 Custom13_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
479 Custom14_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
480 Custom15_Array IN AP_WEB_DFLEX_PKG.CustomFields_A) IS
481
482 l_debug_info varchar2(1000);
483 P_AttributeCol AP_WEB_PARENT_PKG.BigString_Array;
484 l_item_description AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_prompt;
485 l_line_type_lookup_code AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_lineTypeLookupCode;
486 l_require_receipt_amount NUMBER;
487 BEGIN
488 -----------------------------------------
489 l_debug_info := 'Delete existing data in temporary files';
490 ------------------------------------------
491 DELETE FROM ap_web_disc_headers_gt;
492 DELETE FROM ap_web_disc_lines_gt;
493
494 -----------------------------------------
495 l_debug_info := 'Insert header into temporary table';
496 ------------------------------------------
497 INSERT INTO ap_web_disc_headers_gt (EMPLOYEE_ID,
498 COST_CENTER,
499 TEMPLATE_ID,
500 PURPOSE,
501 REIMBURSEMENT_CURRENCY_CODE,
502 OVERRIDE_APPROVER_NAME
503 ) VALUES(
504 to_number(p_report_header_info.employee_id),
505 p_report_header_info.cost_center,
506 to_number(p_report_header_info.template_id),
507 p_report_header_info.purpose,
508 p_report_header_info.reimbursement_currency_code,
509 p_report_header_info.override_approver_name);
510 -----------------------------------------
511 l_debug_info := 'Insert line info into temporary table';
512 ------------------------------------------
513
514 FOR i IN 1 .. (p_report_lines_info.COUNT-1) LOOP
515
516 IF ( NOT AP_WEB_DB_EXPTEMPLATE_PKG.Get_ItemDesc_LookupCode(p_report_lines_info(i).parameter_id,
517 l_item_description,l_line_type_lookup_code,l_require_receipt_amount) ) THEN
518 EXIT;
519 END IF;
520
521 AP_WEB_PARENT_PKG.MapCustomArrayToColumn(i,
522 p_report_header_info,
523 p_report_lines_info,
524 Custom1_Array,
525 Custom2_Array,
526 Custom3_Array,
527 Custom4_Array,
528 Custom5_Array,
529 Custom6_Array,
530 Custom7_Array,
531 Custom8_Array,
532 Custom9_Array,
533 Custom10_Array,
534 Custom11_Array,
535 Custom12_Array,
536 Custom13_Array,
537 Custom14_Array,
538 Custom15_Array,
539 P_AttributeCol);
540
541 INSERT INTO ap_web_disc_lines_gt (
542 START_DATE,
543 END_DATE,
544 DAILY_AMOUNT,
545 RECEIPT_AMOUNT,
546 RATE,
547 PARAMETER_ID,
548 CURRENCY_CODE,
549 GROUP_VALUE,
550 JUSTIFICATION,
551 RECEIPT_MISSING_FLAG,
552 PROJECT_NUMBER,
553 TASK_NUMBER,
554 AWARD_NUMBER,
555 AMOUNT_INCLUDES_TAX_FLAG,
556 TAX_CODE_ID,
557 VAT_CODE,
558 RECEIPT_ERRORS_FLAG,
559 ATTRIBUTE_CATEGORY,
560 ATTRIBUTE1,
561 ATTRIBUTE2,
562 ATTRIBUTE3,
563 ATTRIBUTE4,
564 ATTRIBUTE5,
565 ATTRIBUTE6,
566 ATTRIBUTE7,
567 ATTRIBUTE8,
568 ATTRIBUTE9,
569 ATTRIBUTE10,
570 ATTRIBUTE11,
571 ATTRIBUTE12,
572 ATTRIBUTE13,
573 ATTRIBUTE14,
574 ATTRIBUTE15)
575 VALUES(
576 to_date(p_report_lines_info(i).start_date, AP_WEB_INFRASTRUCTURE_PKG.getDateFormat),
577 to_date(p_report_lines_info(i).end_date, AP_WEB_INFRASTRUCTURE_PKG.getDateFormat),
578 AP_WEB_DB_UTIL_PKG.CharToNumber(p_report_lines_info(i).daily_amount),
579 AP_WEB_DB_UTIL_PKG.CharToNumber(p_report_lines_info(i).receipt_amount),
580 to_number(p_report_lines_info(i).rate),
581 to_number(p_report_lines_info(i).parameter_id),
582 p_report_lines_info(i).currency_code,
583 p_report_lines_info(i).group_value,
584 p_report_lines_info(i).justification,
585 p_report_lines_info(i).receipt_missing_flag,
586 p_report_lines_info(i).project_number,
587 p_report_lines_info(i).task_number,
588 p_report_lines_info(i).award_number,
589 p_report_lines_info(i).amount_includes_tax,
590 to_number(p_report_lines_info(i).taxId),
591 p_report_lines_info(i).tax_code,
592 decode(p_receipt_errors(i).error_text, null, C_No, C_Yes),
593 l_item_description,
594 P_AttributeCol(1),
595 P_AttributeCol(2),
596 P_AttributeCol(3),
597 P_AttributeCol(4),
598 P_AttributeCol(5),
599 P_AttributeCol(6),
600 P_AttributeCol(7),
601 P_AttributeCol(8),
602 P_AttributeCol(9),
603 P_AttributeCol(10),
604 P_AttributeCol(11),
605 P_AttributeCol(12),
606 P_AttributeCol(13),
607 P_AttributeCol(14),
608 P_AttributeCol(15));
609
610
611 END LOOP;
612
613 COMMIT;
614 EXCEPTION
615 WHEN OTHERS THEN
616 DELETE FROM ap_web_disc_headers_gt;
617 DELETE FROM ap_web_disc_lines_gt;
618 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
619 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
620 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','OAExpReport');
621 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
622 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
623 APP_EXCEPTION.RAISE_EXCEPTION;
624 END OAInsertTempData;
625
626
627 /*
628 Written By
629 KWIDJAJA
630 Purpose
631 Procedure called by OAExpReport to add receipt line numbers
632 to Import errors.
633 Input
634 p_header_errors
635 p_receipt_errors
636 Output
637 None
638 InputOutput
639 None
640 Assumptions
641 The application is WEB
642 Date
643 04-OCT-2001
644 */
645 PROCEDURE AddLineNumbersToErrors (p_header_errors IN AP_WEB_UTILITIES_PKG.expError,
646 p_receipt_errors IN AP_WEB_UTILITIES_PKG.receipt_error_stack) IS
647
648 v_index BINARY_INTEGER;
649 v_array_index BINARY_INTEGER;
650 v_error_text AP_WEB_UTILITIES_PKG.MSG_TEXT_TYPE%TYPE;
651 v_error_text_array AP_WEB_UTILITIES_PKG.LongString_Array;
652
653 BEGIN
654 -- Clear current global message stack
655 FND_MSG_PUB.Delete_Msg;
656
657 -- Repopulate header level errors
658 IF (p_header_errors.COUNT > 0) THEN
659 -- Loop through header errors
660 v_index := p_header_errors.FIRST;
661 LOOP
662 --Check if there are any errors.
663 IF (p_header_errors(v_index).text IS NOT NULL) THEN
664 fnd_message.set_name('SQLAP', 'OIE_GENERIC_MESSAGE');
665 fnd_message.set_token ('MESSAGE', p_header_errors(v_index).text);
666 fnd_msg_pub.add();
667 END IF;
668
669 EXIT WHEN v_index = p_header_errors.LAST;
670 v_index := p_header_errors.NEXT(v_index);
671 END LOOP;
672 END IF;
673
674 -- Repopulate receipt line errors
675 IF (p_receipt_errors.COUNT > 0) THEN
676 -- Loop through error stack
677 v_index := p_receipt_errors.FIRST;
678 LOOP
679 --Check if there are any errors.
680 IF (p_receipt_errors(v_index).error_text IS NOT NULL) THEN
681 -- Parse error text into array
682 AP_WEB_UTILITIES_PKG.ArrayifyText(p_receipt_errors(v_index).error_text, v_error_text_array);
683 v_array_index := v_error_text_array.FIRST;
684 LOOP
685 IF (v_error_text_array(v_array_index) IS NOT NULL) THEN
686 v_error_text := v_error_text_array(v_array_index);
687
688 -- Build line with error message
689 fnd_message.set_name('SQLAP', 'OIE_RECEIPT_LINE_ERROR');
690 fnd_message.set_token('LINE', to_char(v_index));
691 fnd_message.set_token('ERROR_MESSAGE', v_error_text);
692
693 fnd_msg_pub.add();
694 END IF;
695
696 EXIT WHEN v_array_index = v_error_text_array.LAST;
697 v_array_index := v_error_text_array.NEXT(v_array_index);
698 END LOOP;
699 END IF;
700
701 EXIT WHEN v_index = p_receipt_errors.LAST;
702 v_index := p_receipt_errors.NEXT(v_index);
703 END LOOP;
704 END IF;
705 END AddLineNumbersToErrors;
706
707
708 /*
709 Written By
710 KWIDJAJA
711 Purpose
712 Procedure called by OAExpReport to add receipt line numbers
713 to Import errors.
714 Input
715 p_header_errors
716 p_receipt_errors
717 Output
718 None
719 InputOutput
720 None
721 Assumptions
722 The application is WEB
723 Date
724 04-OCT-2001
725 */
726 PROCEDURE CheckForReceiptWarnings (p_receipt_errors IN AP_WEB_UTILITIES_PKG.receipt_error_stack) IS
727
728 v_index BINARY_INTEGER;
729 v_array_index BINARY_INTEGER;
730 v_warning_text AP_WEB_UTILITIES_PKG.MSG_TEXT_TYPE%TYPE;
731 v_warning_text_array AP_WEB_UTILITIES_PKG.LongString_Array;
732
733 BEGIN
734 -- Clear current global message stack
735 FND_MSG_PUB.Delete_Msg;
736
737 -- Repopulate receipt line warnings
738 IF (p_receipt_errors.COUNT > 0) THEN
739 -- Loop through error stack
740 v_index := p_receipt_errors.FIRST;
741 LOOP
742 --Check if there are any warnings.
743 IF (p_receipt_errors(v_index).warning_text IS NOT NULL) THEN
744 -- Parse warning text into array
745 AP_WEB_UTILITIES_PKG.ArrayifyText(p_receipt_errors(v_index).warning_text, v_warning_text_array);
746 v_array_index := v_warning_text_array.FIRST;
747 LOOP
748 IF (v_warning_text_array(v_array_index) IS NOT NULL) THEN
749 v_warning_text := v_warning_text_array(v_array_index);
750
751 -- Build line with error message
752 fnd_message.set_name('SQLAP', 'OIE_GENERIC_MESSAGE');
753 fnd_message.set_token('MESSAGE', v_warning_text);
754
755 fnd_msg_pub.add();
756 END IF;
757
758 EXIT WHEN v_array_index = v_warning_text_array.LAST;
759 v_array_index := v_warning_text_array.NEXT(v_array_index);
760 END LOOP;
761 END IF;
762
763 EXIT WHEN v_index = p_receipt_errors.LAST;
764 v_index := p_receipt_errors.NEXT(v_index);
765 END LOOP;
766 END IF;
767 END CheckForReceiptWarnings;
768
769 -- Bug: 6619166, Project and Task Not Validated in Disconnected Expense Entry Skip to Review.
770 PROCEDURE ValidateProjectTransaction(
771 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
772 p_report_lines_info IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
773 p_base_curr_code IN AP_WEB_DB_AP_INT_PKG.apSetUp_baseCurrencyCode,
774 p_acct_raw_cost IN NUMBER,
775 p_def_exchng_rate_type IN VARCHAR2,
776 p_default_exchange_rate IN NUMBER,
777 p_vendor_id IN NUMBER,
778 p_ei_date IN DATE,
779 p_rec_count IN NUMBER,
780 p_msg_type OUT NOCOPY VARCHAR2,
781 p_return_error_message OUT NOCOPY VARCHAR2,
782 p_procedure_billable_flag OUT NOCOPY VARCHAR2
783 ) IS
784 BEGIN
785 AP_WEB_PROJECT_PKG.ValidatePATransaction(p_project_id => p_report_lines_info(p_rec_count).project_id,
786 p_task_id => p_report_lines_info(p_rec_count).task_id,
787 p_ei_date => p_ei_date,
788 p_expenditure_type => p_report_lines_info(p_rec_count).expenditure_type,
789 p_non_labor_resource => NULL,
790 p_person_id => p_report_header_info.employee_id,
791 p_quantity => NULL,
792 p_denom_currency_code=> p_report_header_info.reimbursement_currency_code,
793 p_acct_currency_code => p_base_curr_code,
794 p_denom_raw_cost => p_report_lines_info(p_rec_count).amount,
795 p_acct_raw_cost => p_acct_raw_cost,
796 p_acct_rate_type => p_def_exchng_rate_type,
797 p_acct_rate_date => p_report_lines_info(p_rec_count).end_date,
798 p_acct_exchange_rate => p_default_exchange_rate,
799 p_transfer_ei => NULL,
800 p_incurred_by_org_id => p_report_header_info.expenditure_organization_id,
801 p_nl_resource_org_id => NULL,
802 p_transaction_source => NULL,
803 p_calling_module => 'SelfService',
804 p_vendor_id => p_vendor_id,
805 p_entered_by_user_id => NULL,
806 p_attribute_category => NULL,
807 p_attribute1 => NULL,
808 p_attribute2 => NULL,
809 p_attribute3 => NULL,
810 p_attribute4 => NULL,
811 p_attribute5 => NULL,
812 p_attribute6 => NULL,
813 p_attribute7 => NULL,
814 p_attribute8 => NULL,
815 p_attribute9 => NULL,
816 p_attribute10 => NULL,
817 p_attribute11 => NULL,
818 p_attribute12 => NULL,
819 p_attribute13 => NULL,
820 p_attribute14 => NULL,
821 p_attribute15 => NULL,
822 p_msg_type => p_msg_type,
823 p_msg_data => p_return_error_message,
824 p_billable_flag => p_procedure_billable_flag);
825
826
827 END ValidateProjectTransaction;
828 /*
829 Written by:
830 Kristian Widjaja
831 Purpose:
832 To check whether fields such as project and task are required for these receipts
833 Fix for bug 2159879
834 Input:
835 p_report_header_info: Expense Report Header Information
836 p_report_lines_info: Expense Report Lines Information
837 Output:
838 None
839 Input Output:
840 p_receipts_errors: Receipt error stack
841 Assumption:
842 The application is WEB.
843 Receipts have already passed general parsing and validation.
844 Date:
845 18-Jan-2002
846 */
847 PROCEDURE ValidateRequiredProjectTask(
848 p_employee_id IN NUMBER,
849 p_user_id IN NUMBER,
850 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
851 p_report_lines_info IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
852 p_receipts_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack) IS
853
854 l_errors AP_WEB_UTILITIES_PKG.expError;
855 l_receipt_count BINARY_INTEGER;
856 l_debug_info VARCHAR2(300) := '';
857 rec_count NUMBER := 1; /* receipt count */
858
859 l_pa_expenditure_type AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paExpendituretype;
860 l_is_project_required VARCHAR2(2);--Bug#6852373 - changed size to 2.
861 l_IsSessionProjectEnabled VARCHAR2(1);
862 l_return_error_message VARCHAR2(5000);
863 l_msg_count NUMBER;
864 l_msg_data VARCHAR2(5000);
865 l_base_curr_code AP_WEB_DB_AP_INT_PKG.apSetUp_baseCurrencyCode;
866 l_ret BOOLEAN;
867 l_def_exchng_rate_type VARCHAR2(100);
868 ln_default_exchange_rate NUMBER;
869 ln_acct_raw_cost NUMBER;
870 ln_vendor_id NUMBER;
871 lv_msg_type VARCHAR2(2000);
872 lv_procedure_billable_flag VARCHAR2(200);
873
874 BEGIN
875 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_DISC_PKG', 'start ValidateRequiredProjectTask');
876 ------------------------------------------------------
877 l_debug_info := 'Verify that the user is a projects user';
878 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
879 ------------------------------------------------------
880 AP_WEB_PROJECT_PKG.IsSessionProjectEnabled(
881 p_employee_id, p_user_id,
882 l_IsSessionProjectEnabled);
883
884 IF ( l_IsSessionProjectEnabled = C_No ) THEN
885 RETURN;
886 END IF;
887
888 ------------------------------------------------------
889 l_debug_info := 'Verify that the OIE:Enable Projects profile option is set to Required';
890 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
891 ------------------------------------------------------
892 l_is_project_required := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
893 p_name => 'AP_WEB_ENABLE_PROJECT_ACCOUNTING',
894 p_user_id => p_user_id,
895 p_resp_id => null,
896 p_apps_id => null);
897
898 l_receipt_count := TO_NUMBER(p_report_header_info.receipt_count);
899 -- Loop through all receipts
900 FOR rec_count IN 1..l_receipt_count LOOP
901 ------------------------------------------------------
902 l_debug_info := 'Get the expenditure type for receipt '|| to_char(rec_count);
903 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
904 ------------------------------------------------------
905 AP_WEB_PROJECT_PKG.GetExpenditureTypeMapping(p_report_lines_info(rec_count).parameter_id,
906 l_pa_expenditure_type);
907
908 -- If the expenditure type is not null
909 IF (l_pa_expenditure_type IS NOT NULL) THEN
910 ------------------------------------------------------
911 l_debug_info := 'Check whether project number and task number are null';
912 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
913 ------------------------------------------------------
914
915 -- Output error message if project number and task number is null
916 -- AP_WEB_ENABLE_PROJECT_ACCOUNTING can take Y, N, RA, YA, R
917 -- Either Project or Task Can be Null.
918 -- Bug: 6978992, validate Award.
919 IF ((l_is_project_required = 'R' OR l_is_project_required = 'RA') AND
920 (p_report_lines_info(rec_count).project_number IS NULL OR
921 p_report_lines_info(rec_count).task_number IS NULL
922 OR (IsGrantsEnabled() AND p_report_lines_info(rec_count).award_number IS NULL))) THEN
923
924 IF (p_report_lines_info(rec_count).project_number IS NULL OR
925 p_report_lines_info(rec_count).task_number IS NULL) THEN
926 fnd_message.set_name('SQLAP', 'AP_WEB_PA_PROJTASK_REQUIRED');
927 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
928 fnd_message.get_encoded(),
929 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
930 null,
931 rec_count);
932 END IF;
933 -- Award is required.
934 IF (IsGrantsEnabled() AND p_report_lines_info(rec_count).award_number IS NULL) THEN
935 fnd_message.set_name('SQLAP', 'OIE_AWARD_REQUIRED');
936 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
937 fnd_message.get_encoded(),
938 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
939 null,
940 rec_count);
941 END IF;
942 ELSE
943 -- Either Project or task is missing, but not both.
944 IF ((p_report_lines_info(rec_count).project_number IS NOT NULL AND
945 p_report_lines_info(rec_count).task_number IS NULL) OR
946 (p_report_lines_info(rec_count).task_number IS NOT NULL AND
947 p_report_lines_info(rec_count).project_number IS NULL) OR
948 (IsGrantsEnabled() AND p_report_lines_info(rec_count).award_number is not null AND
949 (p_report_lines_info(rec_count).project_number IS NULL OR p_report_lines_info(rec_count).task_number IS NULL))) THEN
950
951
952 fnd_message.set_name('SQLAP', 'AP_WEB_PA_PROJTASK_REQUIRED');
953 l_return_error_message := fnd_message.get;
954 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
955 l_return_error_message,
956 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
957 'SQLAP',
958 rec_count,
959 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
960
961 ELSE
962 -- 6619166, Projects and Tasks not validated in disconnected expense entry.
963 -- Need to validate only when Project or Task are entered.
964 IF (p_report_lines_info(rec_count).project_number IS NOT NULL
965 OR p_report_lines_info(rec_count).task_number IS NOT NULL) THEN
966
967 -- Bug: 7176464
968 IF (AP_WEB_CUS_ACCTG_PKG.CustomValidateProjectDist(
969 p_report_lines_info(rec_count).report_line_id,
970 p_report_lines_info(rec_count).parameter_id,
971 p_report_lines_info(rec_count).project_id,
972 p_report_lines_info(rec_count).task_id,
973 p_report_lines_info(rec_count).award_id,
974 p_report_header_info.expenditure_organization_id,
975 p_report_lines_info(rec_count).amount,
976 l_return_error_message)) THEN
977 -- Custom Validate Project Allocations
978 IF (l_return_error_message is not null) THEN
979
980 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
981 l_return_error_message,
982 AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError,
983 'PATC',
984 rec_count,
985 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
986 AP_WEB_UTILITIES_PKG.MergeErrors(l_errors, p_receipts_errors);
987 -- Bug 7497991: commenting return to continue validations.
988 -- return;
989 END IF;
990 END IF;
991 IF (NOT AP_WEB_DB_AP_INT_PKG.GetVendorID(p_report_header_info.employee_id, ln_vendor_id)) THEN
992 ln_vendor_id := NULL;
993 END IF; /* GetVendorID */
994
995 l_ret := AP_WEB_DB_AP_INT_PKG.GetBaseCurrInfo(l_base_curr_code);
996 AP_WEB_DB_AP_INT_PKG.GetDefaultExchange(l_def_exchng_rate_type);
997 ln_default_exchange_rate := AP_UTILITIES_PKG.get_exchange_rate(l_base_curr_code,
998 p_report_header_info.reimbursement_currency_code,
999 l_def_exchng_rate_type,
1000 p_report_lines_info(rec_count).end_date,
1001 'ValidatePATransaction');
1002 -- Calculate the receipt amount in the functional currency
1003 ln_acct_raw_cost := NULL;
1004 IF ln_default_exchange_rate IS NOT NULL AND ln_default_exchange_rate <> 0 THEN
1005 ln_acct_raw_cost := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(p_report_lines_info(rec_count).amount/ln_default_exchange_rate, l_base_curr_code);
1006 END IF;
1007
1008 lv_msg_type := null;
1009 l_return_error_message := null;
1010 lv_procedure_billable_flag := null;
1011
1012 ValidateProjectTransaction(p_report_header_info,
1013 p_report_lines_info,
1014 l_base_curr_code,
1015 ln_acct_raw_cost,
1016 l_def_exchng_rate_type,
1017 ln_default_exchange_rate,
1018 ln_vendor_id,
1019 p_report_lines_info(rec_count).start_date,
1020 rec_count,
1021 lv_msg_type,
1022 l_return_error_message,
1023 lv_procedure_billable_flag);
1024
1025 if (l_return_error_message IS NOT NULL AND lv_msg_type = AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError) then
1026 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1027 l_return_error_message,
1028 lv_msg_type,
1029 'PATC',
1030 rec_count,
1031 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1032 else
1033 IF (IsGrantsEnabled() AND p_report_lines_info(rec_count).award_number is not null) THEN
1034 -- 6978992, AwardFundingProject call below is a workaround, GMS API fails with
1035 -- Exact Fetch returns more numberof rows error, when the award is not enabled for a project.
1036 IF(not GMS_OIE_INT_PKG.AwardFundingProject(
1037 p_report_lines_info(rec_count).award_id,
1038 p_report_lines_info(rec_count).project_id,
1039 p_report_lines_info(rec_count).task_id)) THEN
1040
1041 fnd_message.set_name('GMS', 'GMS_INVALID_AWARD');
1042 l_return_error_message := fnd_message.get;
1043 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1044 l_return_error_message,
1045 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1046 'GMS',
1047 rec_count,
1048 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1049
1050 -- Validate for the start expense date
1051 ELSIF(not GMS_OIE_INT_PKG.DoGrantsValidation(p_project_id => p_report_lines_info(rec_count).project_id,
1052 p_task_id => p_report_lines_info(rec_count).task_id,
1053 p_award_id => p_report_lines_info(rec_count).award_id,
1054 p_award_number => p_report_lines_info(rec_count).award_number,
1055 p_expenditure_type => p_report_lines_info(rec_count).expenditure_type,
1056 p_expenditure_item_date => p_report_lines_info(rec_count).start_date,
1057 p_calling_module => 'SelfService',
1058 p_err_msg => l_return_error_message)) then
1059 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1060 l_return_error_message,
1061 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1062 'GMS',
1063 rec_count,
1064 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1065 ELSE
1066 l_return_error_message := null;
1067 lv_msg_type := null;
1068 END IF;
1069 END IF;
1070 end if;
1071 if (l_return_error_message is null and p_report_lines_info(rec_count).end_date is not null) then
1072
1073 ValidateProjectTransaction(p_report_header_info,
1074 p_report_lines_info,
1075 l_base_curr_code,
1076 ln_acct_raw_cost,
1077 l_def_exchng_rate_type,
1078 ln_default_exchange_rate,
1079 ln_vendor_id,
1080 p_report_lines_info(rec_count).end_date,
1081 rec_count,
1082 lv_msg_type,
1083 l_return_error_message,
1084 lv_procedure_billable_flag);
1085
1086 if (l_return_error_message IS NOT NULL AND lv_msg_type = AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError) then
1087 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1088 l_return_error_message,
1089 lv_msg_type,
1090 'PATC',
1091 rec_count,
1092 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1093 else
1094 IF (IsGrantsEnabled() AND p_report_lines_info(rec_count).award_number is not null) THEN
1095 IF(not GMS_OIE_INT_PKG.AwardFundingProject(
1096 p_report_lines_info(rec_count).award_id,
1097 p_report_lines_info(rec_count).project_id,
1098 p_report_lines_info(rec_count).task_id)) THEN
1099
1100 fnd_message.set_name('GMS', 'GMS_INVALID_AWARD');
1101 l_return_error_message := fnd_message.get;
1102 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1103 l_return_error_message,
1104 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1105 'GMS',
1106 rec_count,
1107 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1108 -- Validate for the start expense date
1109 ELSIF(not GMS_OIE_INT_PKG.DoGrantsValidation(p_project_id => p_report_lines_info(rec_count).project_id,
1110 p_task_id => p_report_lines_info(rec_count).task_id,
1111 p_award_id => p_report_lines_info(rec_count).award_id,
1112 p_award_number => p_report_lines_info(rec_count).award_number,
1113 p_expenditure_type => p_report_lines_info(rec_count).expenditure_type,
1114 p_expenditure_item_date => p_report_lines_info(rec_count).end_date,
1115 p_calling_module => 'SelfService',
1116 p_err_msg => l_return_error_message)) then
1117 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1118 l_return_error_message,
1119 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1120 'GMS',
1121 1,
1122 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1123 ELSE
1124 l_return_error_message := null;
1125 lv_msg_type := null;
1126 END IF;
1127 END IF;
1128 end if;
1129 end if;
1130 END IF;
1131 END IF; -- Check for missing Project or Task
1132 END IF;
1133 ELSE
1134 IF (p_report_lines_info(rec_count).project_number IS NOT NULL OR
1135 p_report_lines_info(rec_count).task_number IS NOT NULL OR
1136 p_report_lines_info(rec_count).award_number IS NOT NULL) THEN
1137 fnd_message.set_name('SQLAP', 'OIE_NON_PROJ_EXP');
1138 l_return_error_message := fnd_message.get;
1139 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
1140 l_return_error_message,
1141 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1142 'SQLAP',
1143 rec_count,
1144 AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1145 END IF;
1146 END IF; -- Check expenditure type
1147 END LOOP;
1148
1149 -- Merge errors with receipt error stack
1150 AP_WEB_UTILITIES_PKG.MergeErrors(l_errors, p_receipts_errors);
1151
1152 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_DISC_PKG', 'end ValidateRequiredProjectTask');
1153 END ValidateRequiredProjectTask;
1154
1155
1156 /*
1157 Written by:
1158 Kristian Widjaja
1159 Purpose:
1160 To check whether a type has a rate Mileage or Per Diem schedule assigned to it.
1161 Input:
1162 p_parameter_id: Expense Type
1163 Output:
1164 Boolean
1165 Input Output:
1166 Assumption:
1167 Date:
1168 12-Jul-2002
1169 */
1170 Function AreMPDRateSchedulesAssigned (p_parameter_id IN ap_expense_report_params.parameter_id%TYPE)
1171 RETURN boolean IS
1172
1173 v_numRows NUMBER := 0;
1174 BEGIN
1175 -- This query returns rows if there exist a company policy id
1176 -- of category Mileage or Per Diem for the given parameter ID
1177 SELECT 1
1178 INTO v_numRows
1179 FROM dual
1180 WHERE exists
1181 (SELECT 1
1182 FROM ap_expense_report_params expTypes
1183 WHERE expTypes.company_policy_id IS NOT NULL
1184 AND expTypes.category_code in ('MILEAGE', 'PER_DIEM')
1185 AND trunc(sysdate) <= trunc(NVL(expTypes.end_date, sysdate))
1186 AND expTypes.parameter_id = p_parameter_id);
1187
1188 -- Return true if there were rows, return false otherwise.
1189 IF v_numRows = 1 THEN
1190 RETURN TRUE;
1191 ELSE
1192 RETURN FALSE;
1193 END IF;
1194
1195 EXCEPTION
1196 WHEN no_data_found THEN
1197 return(false);
1198 WHEN OTHERS THEN
1199 raise;
1200
1201 END AreMPDRateSchedulesAssigned;
1202
1203 /*
1204 Written by:
1205 Kristian Widjaja
1206 Purpose:
1207 To check whether a type has a rate Policy Compliance schedule assigned to it.
1208 Input:
1209 p_parameter_id: Expense Type
1210 Output:
1211 Boolean
1212 Input Output:
1213 Assumption:
1214 Date:
1215 12-Jul-2002
1216 */
1217 Function ArePCRateSchedulesAssigned (p_parameter_id IN ap_expense_report_params.parameter_id%TYPE)
1218 RETURN boolean IS
1219
1220 v_numRows NUMBER := 0;
1221 BEGIN
1222 -- This query returns rows if there exist any schedule options
1223 -- of category Accommodations, Airfare, Car Rental, or Meals for the given parameter ID
1224 SELECT 1
1225 INTO v_numRows
1226 FROM dual
1227 WHERE EXISTS
1228 (SELECT 1
1229 FROM ap_expense_report_params expTypes
1230 WHERE expTypes.company_policy_id IS NOT NULL
1231 AND expTypes.category_code in ('ACCOMMODATIONS', 'AIRFARE', 'CAR_RENTAL', 'MEALS')
1232 AND trunc(sysdate) <= trunc(NVL(expTypes.end_date, sysdate))
1233 AND expTypes.parameter_id = p_parameter_id);
1234
1235 -- Return true if there were rows, return false otherwise.
1236 IF v_numRows = 1 THEN
1237 RETURN TRUE;
1238 ELSE
1239 RETURN FALSE;
1240 END IF;
1241
1242 EXCEPTION
1243 WHEN no_data_found THEN
1244 return(false);
1245 WHEN OTHERS THEN
1246 raise;
1247 END ArePCRateSchedulesAssigned;
1248
1249 /*
1250 Written by:
1251 Kristian Widjaja
1252 Purpose:
1253 To check whether a type has a schedules with required or enabled expense fields
1254 Input:
1255 p_parameter_id: Expense Type
1256 Output:
1257 Boolean
1258 Input Output:
1259 Assumption:
1260 Date:
1261 12-Jul-2002
1262 */
1263 Function CheckExpenseFields (p_parameter_id IN ap_expense_report_params.parameter_id%TYPE, p_reqd_enabled IN VARCHAR2)
1264 RETURN boolean IS
1265
1266 v_numRows NUMBER := 0;
1267 BEGIN
1268 -- This query returns rows if there exist any required expense fields
1269 -- of category Accommodations, Airfare, Car Rental, or Meals for the given parameter ID
1270 SELECT 1
1271 INTO v_numRows
1272 FROM dual
1273 WHERE EXISTS
1274 (SELECT 1
1275 FROM ap_expense_report_params expTypes,
1276 ap_pol_cat_options catOptions
1277 WHERE
1278 ((catOptions.category_code = 'ACCOMMODATIONS'
1279 AND (END_DATE_FIELD = p_reqd_enabled
1280 OR MERCHANT_FIELD = p_reqd_enabled))
1281 OR
1282 (catOptions.category_code = 'AIRFARE'
1283 AND (MERCHANT_FIELD = p_reqd_enabled
1284 OR TICKET_CLASS_FIELD = p_reqd_enabled
1285 OR TICKET_NUMBER_FIELD = p_reqd_enabled
1286 OR LOCATION_FROM_FIELD = p_reqd_enabled
1287 OR LOCATION_TO_FIELD = p_reqd_enabled))
1288 OR
1289 (catOptions.category_code = 'CAR_RENTAL'
1290 AND (MERCHANT_FIELD = p_reqd_enabled))
1291 OR
1292 (catOptions.category_code = 'MEALS'
1293 AND (ATTENDEES_FIELD = p_reqd_enabled
1294 OR ATTENDEES_NUMBER_FIELD = p_reqd_enabled))
1295 OR
1296 (catOptions.category_code = 'MILEAGE'
1297 AND (DESTINATION_FIELD = p_reqd_enabled
1298 OR LICENSE_PLATE_FIELD = p_reqd_enabled))
1299 )
1300 AND expTypes.category_code = catOptions.category_code
1301 AND trunc(sysdate) <= trunc(NVL(expTypes.end_date, sysdate))
1302 AND expTypes.parameter_id = p_parameter_id);
1303
1304 -- Return true if there were rows, return false otherwise.
1305 IF v_numRows = 1 THEN
1306 RETURN TRUE;
1307 ELSE
1308 RETURN FALSE;
1309 END IF;
1310
1311 EXCEPTION
1312 WHEN no_data_found THEN
1313 return(false);
1314 WHEN OTHERS THEN
1315 raise;
1316 END CheckExpenseFields;
1317
1318 Function AreExpenseFieldsRequired (p_parameter_id IN ap_expense_report_params.parameter_id%TYPE)
1319 RETURN boolean IS
1320
1321 BEGIN
1322 return CheckExpenseFields(p_parameter_id, C_REQUIRED);
1323 END AreExpenseFieldsRequired;
1324
1325 Function AreExpenseFieldsEnabled (p_parameter_id IN ap_expense_report_params.parameter_id%TYPE)
1326 RETURN boolean IS
1327
1328 BEGIN
1329 return CheckExpenseFields(p_parameter_id, C_ENABLED);
1330 END AreExpenseFieldsEnabled;
1331
1332
1333
1334 /*
1335 Written by:
1336 Kristian Widjaja
1337 Purpose:
1338 To check whether a type requires Itemization
1339 Input:
1340 p_parameter_id: Expense Type
1341 Output:
1342 Boolean
1343 Input Output:
1344 Assumption:
1345 Date:
1346 12-Jul-2002
1347 */
1348 Function IsItemizationRequired (p_parameter_id IN ap_expense_report_params.parameter_id%TYPE)
1349 RETURN boolean IS
1350
1351 v_numRows NUMBER := 0;
1352 BEGIN
1353 -- This query returns rows if itemizations are required for the given parameter ID
1354 SELECT 1
1355 INTO v_numRows
1356 FROM dual
1357 WHERE EXISTS
1358 (SELECT 1
1359 FROM ap_expense_report_params expTypes
1360 WHERE expTypes.itemization_required_flag = C_Yes
1361 AND trunc(sysdate) <= trunc(NVL(expTypes.end_date, sysdate))
1362 AND expTypes.parameter_id = p_parameter_id);
1363
1364 -- Return true if there were rows, return false otherwise.
1365 IF v_numRows = 1 THEN
1366 RETURN TRUE;
1367 ELSE
1368 RETURN FALSE;
1369 END IF;
1370
1371 EXCEPTION
1372 WHEN no_data_found THEN
1373 return(false);
1374 WHEN OTHERS THEN
1375 raise;
1376 END IsItemizationRequired;
1377
1378 /*
1379 Written by:
1380 Kristian Widjaja
1381 Purpose:
1382 To check whether any Merchant fields are required per VAT setup
1383 Input:
1384 Output:
1385 Boolean
1386 Input Output:
1387 Assumption:
1388 Date:
1389 12-Jul-2002
1390 */
1391 Function AreMerchantFieldsRequired
1392 RETURN boolean IS
1393
1394 l_debug_info VARCHAR2(300) := '';
1395 l_is_tax_enabled VARCHAR2(1);
1396 v_numRows NUMBER := 0;
1397 BEGIN
1398 ------------------------------------------------------
1399 l_debug_info := 'Verify that the OIE:Enable Tax profile option is set to Yes';
1400 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
1401 ------------------------------------------------------
1402 AP_WEB_DFLEX_PKG.IsSessionTaxEnabled(l_is_tax_enabled);
1403 IF (l_is_tax_enabled = C_Yes) THEN
1404 -- This query returns rows if any VAT merchant fields are required in this org
1405 SELECT 1
1406 INTO v_numRows
1407 FROM dual
1408 WHERE EXISTS
1409 (SELECT 1
1410 FROM ap_web_vat_setup
1411 WHERE
1412 ENABLED_CODE = C_Yes
1413 AND
1414 (ENABLE_MERCHANT_NAME_CODE = C_REQUIRED
1415 OR ENABLE_MERCHANT_RECEIPT_CODE = C_REQUIRED
1416 OR ENABLE_MERCHANT_TAX_REG_CODE = C_REQUIRED
1417 OR ENABLE_MERCHANT_TAXPAYER_CODE = C_REQUIRED
1418 OR ENABLE_MERCHANT_REFERENCE_CODE = C_REQUIRED));
1419
1420 -- Return true if there were rows, return false otherwise
1421 IF v_numRows = 1 THEN
1422 RETURN TRUE;
1423 ELSE
1424 RETURN FALSE;
1425 END IF;
1426 ELSE
1427 RETURN FALSE;
1428 END IF;
1429
1430 EXCEPTION
1431 WHEN no_data_found THEN
1432 return(false);
1433 WHEN OTHERS THEN
1434 raise;
1435 END AreMerchantFieldsRequired;
1436
1437 /*
1438 Written by:
1439 Kristian Widjaja
1440 Purpose:
1441 To check whether exchange rates are set up
1442 Input:
1443 Output:
1444 Boolean
1445 Input Output:
1446 Assumption:
1447 Date:
1448 12-Jul-2002
1449 */
1450 FUNCTION IsExchangeRateSetup
1451 RETURN boolean IS
1452
1453 v_numRows NUMBER := 0;
1454 BEGIN
1455 -- This query returns rows if there exist any exchange rate validation in this org
1456 SELECT 1
1457 INTO v_numRows
1458 FROM dual
1459 WHERE EXISTS
1460 (SELECT 1
1461 FROM AP_POL_EXRATE_OPTIONS WHERE ENABLED = 'Y');
1462
1463 -- Return true if there were rows, return false otherwise
1464 IF v_numRows = 1 THEN
1465 RETURN TRUE;
1466 ELSE
1467 RETURN FALSE;
1468 END IF;
1469
1470 EXCEPTION
1471 WHEN no_data_found THEN
1472 return(false);
1473 WHEN OTHERS THEN
1474 raise;
1475 END IsExchangeRateSetup;
1476
1477 /*
1478 Written by:
1479 Ron Langi
1480 Purpose:
1481 To check whether Grants is enabled
1482 Input:
1483 Output:
1484 Boolean
1485 Input Output:
1486 Assumption:
1487 Date:
1488 12-Jul-2002
1489 */
1490 FUNCTION IsGrantsEnabled
1491 RETURN boolean IS
1492
1493 BEGIN
1494 return GMS_OIE_INT_PKG.IsGrantsEnabled();
1495 END IsGrantsEnabled;
1496
1497 /*
1498 Written by:
1499 Ron Langi
1500 Purpose:
1501 To check whether Line Level Accounting is enabled
1502 Input:
1503 Output:
1504 Boolean
1505 Input Output:
1506 Assumption:
1507 Date:
1508 12-Jul-2002
1509 */
1510 FUNCTION IsLineLevelAcctingEnabled
1511 RETURN boolean IS
1512
1513 BEGIN
1514 return (NVL(fnd_profile.value('OIE_ENABLE_LINE_LEVEL_ACCOUNTING'),'N') = 'Y');
1515 END IsLineLevelAcctingEnabled;
1516
1517 /*
1518 Written by:
1519 Kristian Widjaja
1520 Purpose:
1521 To check whether a report has any Per Diem, Mileage, or Policy related items
1522 Input:
1523 Output:
1524 Boolean
1525 Input Output:
1526 Assumption:
1527 Date:
1528 12-Jul-2002
1529 */
1530
1531 /*Bug 2686210: Dont display the policy related error messages
1532 in the Import Page after clicking Skip To Review
1533 Button. Allow user to go to Review Page if the Expense
1534 report contains only Policy Violated items. Display the
1535 policy warning in Review Page.
1536 Deleted all occurances of l_PC_present.
1537 Have a diff with earlier version for knowing the occurance of the
1538 policy violations checking.
1539 */
1540
1541 PROCEDURE CheckForMileagePerDiemPolicy(
1542 p_user_id IN NUMBER,
1543 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
1544 p_report_lines_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
1545 p_header_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError,
1546 p_receipts_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack) IS
1547
1548 l_receipt_count BINARY_INTEGER;
1549 l_debug_info VARCHAR2(300) := '';
1550 l_errors AP_WEB_UTILITIES_PKG.expError;
1551 rec_count NUMBER := 1; /* receipt count */
1552
1553 l_MPD_present BOOLEAN := false; -- Keeps track whether the report contains Mileage / Per Diem header exceptions
1554 l_foreign_currency_present BOOLEAN := false; -- Keeps track whether foreign currencies exist.
1555 l_reimbursement_currency_code AP_WEB_DFLEX_PKG.expLines_currCode;
1556 l_receipt_required_amount AP_EXPENSE_REPORT_PARAMS.require_receipt_amount%TYPE;
1557 l_base_currency_code AP_WEB_DB_AP_INT_PKG.apSetUp_baseCurrencyCode;
1558
1559 BEGIN
1560 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_DISC_PKG', 'start CheckForPerDiemMileagePolicy');
1561 -- Get reimbursement currency
1562 l_reimbursement_currency_code := p_report_header_info.reimbursement_currency_code;
1563
1564 ------------------------------------------------------
1565 l_debug_info := 'Check whether there are any required VAT merchant fields';
1566 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
1567 ------------------------------------------------------
1568
1569 ------------------------------------------------------
1570 l_debug_info := 'Go through all lines and check whether there are any Mileage, Per Diem, or Policy items';
1571 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
1572 ------------------------------------------------------
1573
1574 l_receipt_count := TO_NUMBER(p_report_header_info.receipt_count);
1575
1576 -- Loop through all receipts
1577 FOR rec_count IN 1..l_receipt_count LOOP
1578 ------------------------------------------------------
1579 l_debug_info := 'Check Mileage/Per Diem for receipt '|| to_char(rec_count);
1580 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
1581 ------------------------------------------------------
1582 -- Only check if one or more monetary values is not null
1583 IF p_report_lines_info(rec_count).daily_amount IS NOT null
1584 OR p_report_lines_info(rec_count).receipt_amount IS NOT null
1585 OR p_report_lines_info(rec_count).amount IS NOT null THEN
1586
1587 IF AreMPDRateSchedulesAssigned(p_report_lines_info(rec_count).parameter_id) THEN
1588 ------------------------------------------------------
1589 l_debug_info := 'Blank out monetary values.';
1590 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
1591 ------------------------------------------------------
1592 p_report_lines_info(rec_count).daily_amount := null;
1593 p_report_lines_info(rec_count).receipt_amount := null;
1594 p_report_lines_info(rec_count).amount := null;
1595
1596 l_MPD_present := TRUE;
1597 END IF;
1598 END IF;
1599
1600 -- Do not perform this check anymore if a foreign currency has been found.
1601 IF (NOT l_foreign_currency_present
1602 AND (l_reimbursement_currency_code <>
1603 p_report_lines_info(rec_count).currency_code)) THEN
1604 ------------------------------------------------------
1605 l_debug_info := 'Found foreign currency in receipt '|| to_char(rec_count);
1606 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_OA_DISC_PKG', l_debug_info);
1607 ------------------------------------------------------
1608 l_foreign_currency_present := TRUE;
1609 END IF;
1610 END LOOP;
1611
1612
1613 -- Display Milage / Per Diem error message, if applicable
1614 IF l_MPD_present THEN
1615 fnd_message.set_name('SQLAP','OIE_DISC_PDM_REMOVE_VALUES');
1616 AP_WEB_UTILITIES_PKG.AddExpError(p_header_errors,
1617 fnd_message.get_encoded(),
1618 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
1619 END IF;
1620
1621 -- Merge errors with receipt error stack
1622 AP_WEB_UTILITIES_PKG.MergeErrors(l_errors, p_receipts_errors);
1623
1624 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_OA_DISC_PKG', 'end CheckForPerDiemMileagePolicy');
1625 END CheckForMileagePerDiemPolicy;
1626
1627 /*
1628 Written by:
1629 skoukunt
1630 Purpose:
1631 To check wh/ether there are any required segments in Header level descriptive flexfield
1632 Input:
1633 Output:
1634 Input Output:
1635 Assumption:
1636 Date:
1637 20-Dec-2004
1638 */
1639
1640 PROCEDURE ValidateHeaderDFF(
1641 p_user_id IN NUMBER,
1642 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
1643 p_header_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError) IS
1644
1645 l_DFFEnabled VARCHAR2(1);
1646
1647 BEGIN
1648
1649 l_DFFEnabled := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
1650 p_name => 'AP_WEB_DESC_FLEX_NAME',
1651 p_user_id => p_user_id,
1652 p_resp_id => FND_PROFILE.VALUE('RESP_ID'),
1653 p_apps_id => FND_PROFILE.VALUE('RESP_APPL_ID') );
1654
1655 IF (nvl(l_DFFEnabled,'N') in ('B','H')) THEN
1656
1657 FND_FLEX_DESCVAL.Set_Context_Value(p_report_header_info.template_name);
1658 FND_FLEX_DESCVAL.Set_Column_Value('Attribute1','');
1659 FND_FLEX_DESCVAL.Set_Column_Value('Attribute2','');
1660 FND_FLEX_DESCVAL.Set_Column_Value('Attribute3','');
1661 FND_FLEX_DESCVAL.Set_Column_Value('Attribute4','');
1662 FND_FLEX_DESCVAL.Set_Column_Value('Attribute5','');
1663 FND_FLEX_DESCVAL.Set_Column_Value('Attribute6','');
1664 FND_FLEX_DESCVAL.Set_Column_Value('Attribute7','');
1665 FND_FLEX_DESCVAL.Set_Column_Value('Attribute8','');
1666 FND_FLEX_DESCVAL.Set_Column_Value('Attribute9','');
1667 FND_FLEX_DESCVAL.Set_Column_Value('Attribute10','');
1668 FND_FLEX_DESCVAL.Set_Column_Value('Attribute11','');
1669 FND_FLEX_DESCVAL.Set_Column_Value('Attribute12','');
1670 FND_FLEX_DESCVAL.Set_Column_Value('Attribute13','');
1671 FND_FLEX_DESCVAL.Set_Column_Value('Attribute14','');
1672 FND_FLEX_DESCVAL.Set_Column_Value('Attribute15','');
1673
1674 IF (NOT FND_FLEX_DESCVAL.Validate_Desccols('SQLAP',
1675 'AP_EXPENSE_REPORT_HEADERS',
1676 'I',
1677 sysdate,
1678 TRUE)) THEN
1679
1680 fnd_message.set_name('SQLAP','OIE_ADDITIONAL_INFO_REQUIRED');
1681 --fnd_message.set_token ('MESSAGE', FND_FLEX_DESCVAL.error_message);
1682 AP_WEB_UTILITIES_PKG.AddExpError(p_header_errors,
1683 fnd_message.get_encoded(),
1684 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
1685
1686 END IF;
1687
1688 END IF;
1689
1690 EXCEPTION
1691 WHEN OTHERS THEN
1692 AP_WEB_DB_UTIL_PKG.RaiseException('ValidateHeaderDFF');
1693 APP_EXCEPTION.RAISE_EXCEPTION;
1694 END ValidateHeaderDFF;
1695
1696 /*========================================================================
1697 | PUBLIC PROCEDURE GetPolicyRateOptions
1698 |
1699 | DESCRIPTION
1700 | Get the default_exchange_rates from ap_pol_exrate_options
1701 | can add other fields to the record PolicyRateOptionsRec and select
1702 | the select statement to get values for other fields
1703 |
1704 | PARAMETERS
1705 | None
1706 |
1707 | MODIFICATION HISTORY
1708 | Date Author Description of Changes
1709 | 28-JUL-2003 Srihari Koukuntla Created
1710 |
1711 *=======================================================================*/
1712 PROCEDURE GetPolicyRateOptions(p_policyRateOptions OUT NOCOPY PolicyRateOptionsRec)
1713 IS
1714 BEGIN
1715
1716 SELECT nvl(default_exchange_rates,'N')
1717 INTO p_policyRateOptions.default_exchange_rates
1718 FROM ap_pol_exrate_options WHERE enabled = 'Y';
1719
1720 EXCEPTION
1721 WHEN NO_DATA_FOUND THEN
1722 p_policyRateOptions.default_exchange_rates := 'N';
1723
1724 WHEN OTHERS THEN
1725 AP_WEB_DB_UTIL_PKG.RaiseException('GetPolicyRateOptions');
1726 APP_EXCEPTION.RAISE_EXCEPTION;
1727 END GetPolicyRateOptions;
1728
1729 END AP_WEB_OA_DISC_PKG;