1 PACKAGE BODY AP_WEB_DISC_PKG AS
2 /* $Header: apwdiscb.pls 120.25.12010000.2 2008/08/06 10:15:06 rveliche ship $ */
3 --
4 -- Data section
5 --
6
7 -- This is to store information for a segment
8 TYPE segmentPrompt IS RECORD
9 (
10 --chiho:1305717:change the data type:
11 exptype AP_WEB_DB_EXPLINE_PKG.expLines_expendType,
12
13 segment_num number,
14 default_value fnd_descr_flex_col_usage_vl.default_value%TYPE,
15
16 --chiho:1305717:change the data type:
17 prompt fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE,
18
19 found BOOLEAN,
20 webEnabled BOOLEAN
21 );
22
23 TYPE segmentPrompt_table IS TABLE OF segmentPrompt
24 INDEX BY BINARY_INTEGER;
25
26 TYPE expIndex_table IS TABLE OF number
27 INDEX BY BINARY_INTEGER;
28
29
30 C_Grp_Size CONSTANT INTEGER := 80;
31 C_Just_Size CONSTANT INTEGER := 240;
32 C_Purpose_Size CONSTANT INTEGER := 240;
33 C_CostCenter_Size CONSTANT INTEGER := 30;
34 C_XTemplate_Size CONSTANT INTEGER := 25;
35 C_Approver_Size CONSTANT INTEGER := 240;
36 C_MiniString_Size CONSTANT INTEGER := 80;
37
38 /* The prompt index are relative to AP_WEB_EXP_VIEW_REC */
39 C_Date1_Prompt CONSTANT varchar2(3) := '6';
40 C_Date2_Prompt CONSTANT varchar2(3) := '7';
41 C_Days_Prompt CONSTANT varchar2(3) := '8';
42 C_DAmount_Prompt CONSTANT varchar2(3) := '9';
43 C_Amount_Prompt CONSTANT varchar2(3) := '23';
44 C_Exptype_Prompt CONSTANT varchar2(3) := '11';
45 C_Just_Prompt CONSTANT varchar2(3) := '12';
46 C_Grp_Prompt CONSTANT varchar2(3) := '24';
47 C_Missing_Prompt CONSTANT varchar2(3) := '15';
48 C_RecAmt_Prompt CONSTANT varchar2(3) := '10';
49 C_Rate_Prompt CONSTANT varchar2(3) := '22';
50 C_RecCurr_Prompt CONSTANT varchar2(3) := '21';
51 C_GLOBAL CONSTANT varchar2(6):='GLOBAL';
52
53 -- Constants passed as P_GenErrorOrWarning argument to GenReceiptErrorTable
54 C_GenErrorOnly CONSTANT NUMBER := 1;
55 C_GenWarningOnly CONSTANT NUMBER := 2;
56
57 procedure GetTill(p_exp in out nocopy long,
58 p_item out nocopy long,
59 p_sep in varchar2);
60
61 PROCEDURE GetLine(p_exp in out nocopy long,
62 p_line out nocopy long,
63 p_max in number default 2000);
64
65 PROCEDURE GetField(p_line in out nocopy long,
66 p_field out nocopy long,
67 p_max in number default 300);
68
69 PROCEDURE GetNonEmptyFld(p_line in out nocopy long,
70 p_field out nocopy long,
71 p_max in number default 300);
72
73 function SwapPrompts(p_table in out nocopy disc_prompts_table,
74 p_from in number,
75 P_to in number) return boolean;
76
77 function ReportTotal(Amount_Array in AP_WEB_PARENT_PKG.MiniString_Array)
78 return number;
79
80 function ReportTotal2(ExpLine_Array in AP_WEB_DFLEX_PKG.ExpReportLines_A)
81 return number;
82
83 function isValidCurrency (p_currency_code IN AP_WEB_DB_AP_INT_PKG.apSetUp_baseCurrencyCode)
84 return boolean;
85
86
87 procedure getFlexFieldGlobalSegments(p_user_id in NUMBER, -- Bug 2242176
88 p_segments in out nocopy FND_DFLEX.SEGMENTS_DR);
89
90 procedure getAllSegmentPrompts(p_user_id in NUMBER, -- Bug 2242176
91 p_report_header_info in AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
92 p_segmentPromptTable in out nocopy segmentPrompt_table);
93
94 procedure checkValidFlexFieldPrompt(p_fld in varchar2,
95 p_segmentPromptTable in segmentPrompt_table,
96 p_expIndexTable in expIndex_table,
97 p_number in out nocopy number);
98
99 procedure initIndexAndDflexTable(p_segmentPromptTable in segmentPrompt_table ,
100 p_dflexTable in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
101 p_expIndexTable in out nocopy expIndex_table);
102
103 procedure setupIndexAndDflexTable(p_expType in varchar2,
104 p_segmentPromptTable in segmentPrompt_table ,
105 p_dflexTable in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
106 p_expIndexTable in out nocopy expIndex_table);
107
108 --------------------------------
109 /* Fills the disc_prompts_table with the prompts retrieved from p_reg_code
110 region. The prompt_text field in the zeroth element contains the element count. */
111
112 PROCEDURE Disc_GetPrompts(
113 p_reg_code in AK_REGION_ITEMS_VL.region_code%TYPE,
114 p_table in out nocopy DISC_PROMPTS_TABLE
115 ) IS
116 ---------------------------------
117 l_count number;
118 l_error varchar2(500) := '';
119 l_prompt_cursor PromptsCursor;
120
121 BEGIN
122
123 l_count := 1;
124
125 IF ( GetAKRegionPromptsCursor(p_reg_code,
126 l_prompt_cursor) = TRUE ) THEN
127 loop
128 fetch l_prompt_cursor into
129 p_table(l_count).prompt_text,
130 p_table(l_count).prompt_code;
131
132 exit when l_prompt_cursor%NOTFOUND;
133
134 l_count := l_count + 1;
135
136 end loop;
137
138 close l_prompt_cursor;
139 END IF;
140
141 EXCEPTION
142 when OTHERS then
143 begin
144 l_count := p_table.COUNT;
145 LOOP
146 l_error := p_table(l_count).prompt_code || ' ' || l_error;
147 if (l_count = 1) then
148 exit;
149 end if;
150 l_count := l_count - 1;
151 END LOOP;
152 l_error := p_table.COUNT || '#' || l_error;
153
154 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
155 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
156 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'Disc_GetPrompts');
157 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','');
158 FND_MESSAGE.SET_TOKEN('PARAMETERS', l_error);
159 APP_EXCEPTION.RAISE_EXCEPTION;
160 end; /* when OTHERS */
161
162 END Disc_GetPrompts;
163
164 -----------------------------------
165 Function Disc_GetPrompt(p_prompt_text in varchar2,
166 p_table in disc_prompts_table) return varchar2 IS
167 -----------------------------------
168
169 l_count number := 1;
170
171 BEGIN
172
173 if (p_table.COUNT = 0) then
174 return null;
175 end if;
176 loop
177 if (p_table(l_count).prompt_text = p_prompt_text) then
178 return(p_table(l_count).prompt_code);
179 end if;
180 if (l_count = p_table.COUNT) then
181 return null;
182 end if;
183 l_count := l_count + 1;
184 end loop;
185
186 END Disc_GetPrompt;
187
188 ------------------------------------
189 Function Disc_GetPromptIndex(p_prompt_text in varchar2,
190 p_table in disc_prompts_table) return number IS
191 ------------------------------------
192
193 l_count number := 1;
194
195 BEGIN
196
197 if (p_table.COUNT = 0) then
198 return 0;
199 end if;
200 loop
201 if (p_table(l_count).prompt_text = p_prompt_text) then
202 return(l_count);
203 end if;
204 if (l_count = p_table.COUNT) then
205 return 0;
206 end if;
207 l_count := l_count + 1;
208 end loop;
209
210 END Disc_GetPromptIndex;
211
212
213 /* Call disc_getprompts to populate p_table with prompt_text and
214 prompt_code from AP_WEB_DISC_EXP region. Then fill in the proper
215 value for required and duplicate flags in p_table.
216 Possible value for the required flag:
217 'H' - required in the Header,
218 'R' - required in the receipts section,
219 'NH' - in header, but not required,
220 'NR' - in receipts, but not required,
221 'F' - found.
222 A note on 'amount' fields: initially only recamt is set to 'R' as required in
223 receipts section. Later on after parsing the header columns, if recamt is
224 not found, but dailyamt is there, that's ok.
225 Quan: Add the flexfield global prompts to p_table.
226 */
227
228 PROCEDURE AP_WEB_INIT_PROMPTS_ARRAY(p_user_id in number, -- bug 2242176
229 p_table in out nocopy DISC_PROMPTS_TABLE,
230 p_format_errors in out nocopy setup_error_stack)
231 IS
232
233 l_region_code varchar2(100) := 'AP_WEB_DISC_EXP';
234 l_count number := 1;
235 l_prompt_code varchar2(200) := '';
236 l_AllowOverriderFlag varchar2(1) := 'N';
237 l_ApprReqCC varchar2(1) := 'N';
238 l_OverriderReq varchar2(1) := 'N';
239 zero_prompts_found varchar2(200) := 'No prompts found';
240 l_segments FND_DFLEX.SEGMENTS_DR; -- For Flexfield global segments
241
242 BEGIN
243
244 Disc_GetPrompts(l_region_code, p_table);
245 if (p_table.COUNT = 0) then
246 /* Maybe the region doesn't even exist, or it's empty. Can't continue ... */
247 p_format_errors(p_format_errors.COUNT+1) := zero_prompts_found;
248 return;
249 end if;
250
251 -- Use VALUE_SPECIFIC instead of GET - bug 2242176
252 l_AllowOverriderFlag := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
253 p_name => 'AP_WEB_ALLOW_OVERRIDE_APPROVER',
254 p_user_id => p_user_id,
255 p_resp_id => null,
256 p_apps_id => null);
257
258 -- Overrider required if costcenter is different from your own?
259 l_ApprReqCC := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
260 p_name => 'AP_WEB_APPROVER_REQ_CC',
261 p_user_id => p_user_id,
262 p_resp_id => null,
263 p_apps_id => null);
264
265 -- Override Approver is required?
266 l_OverriderReq := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
267 p_name => 'AP_WEB_OVERRIDE_APPR_REQ',
268 p_user_id => p_user_id,
269 p_resp_id => null,
270 p_apps_id => null);
271
272 LOOP
273 if (l_count = p_table.COUNT+1) then
274 exit;
275 end if;
276 p_table(l_count).global_flag := false;
277 l_prompt_code := p_table(l_count).prompt_code;
278
279 if ((l_prompt_code = 'AP_WEB_COSTCTR') OR
280 (l_prompt_code = 'AP_WEB_PURPOSE') OR
281 (l_prompt_code = 'AP_WEB_EXP_TEMP')) then
282 p_table(l_count).required := 'H';
283 elsif (l_prompt_code = 'AP_WEB_OVERRIDE_APPROVER') then
284 if ((l_AllowOverriderFlag = 'Y') AND ((l_ApprReqCC = 'Y')
285 OR (l_OverriderReq = 'Y'))) then
286 p_table(l_count).required := 'H';
287 else
288 p_table(l_count).required := 'NH';
289 end if;
290 elsif ((l_prompt_code = 'AP_WEB_LINE') OR
291 (l_prompt_code = 'AP_WEB_STARTDATE') OR
292 (l_prompt_code = 'AP_WEB_RECPT_MISSING') OR
293 (l_prompt_code = 'AP_WEB_JUST')) then
294 p_table(l_count).required := 'R';
295 else
296 p_table(l_count).required := 'NR';
297 end if;
298 l_count := l_count + 1;
299 END LOOP;
300
301 -- Add the flexfield global prompts to p_table
302 getFlexFieldGlobalSegments(p_user_id, -- Bug 2242176 employee fnd user id
303 l_segments);
304
305
306 FOR i in 1..l_segments.nsegments LOOP
307 if (l_segments.is_enabled(i)) then
308 p_table(l_count).prompt_code := l_segments.segment_name(i);
309 p_table(l_count).prompt_text := l_segments.row_prompt(i);
310 p_table(l_count).required := 'NR';
311 p_table(l_count).duplicate := 'N';
312 p_table(l_count).global_flag := true;
313 l_count := l_count+1;
314 end if;
315 END LOOP;
316
317 END AP_WEB_INIT_PROMPTS_ARRAY;
318
319 --
320 -- Purpose: To identify zero, regardless of the number format
321 --
322 -- MODIFICATION HISTORY
323 -- Person Date Comments
324 -- --------- ------ -------------------------------------------
325 -- kwidjaja 4/26/02 Bug 2228748
326
327 FUNCTION isAllZeroes
328 ( p_numString IN varchar2)
329 RETURN boolean IS
330
331 v_length binary_integer;
332 v_index binary_integer;
333 v_currChar VARCHAR2(1);
334 v_return boolean := true;
335
336 BEGIN
337 v_length := length(p_numString);
338
339 FOR v_index in 1..v_length LOOP
340 v_currChar := substr(p_numString, v_index, 1);
341 IF v_currChar not in ('0', ',', '.') THEN
342 v_return := false;
343 EXIT;
344 END IF;
345 END LOOP;
346
347 RETURN v_return;
348 EXCEPTION
349 WHEN OTHERS THEN
350 RETURN false;
351 END isAllZeroes; -- Function ISALLZEROES
352
353 PROCEDURE parseExpReportHeader
354 (p_user_id IN NUMBER, -- Bug 2242176, fnd user id
355 p_text IN OUT NOCOPY LONG,
356 p_table IN OUT NOCOPY disc_prompts_table,
357 p_def_costcenter IN VARCHAR2,
358 p_header IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
359 p_errors OUT NOCOPY AP_WEB_UTILITIES_PKG.expError) IS
360 l_label_text VARCHAR2(300) := '';
361 l_label_code VARCHAR2(100) := '';
362 l_label_index NUMBER;
363 l_value VARCHAR2(300) := ''; -- even purpose only takes 240 chars.
364 l_line VARCHAR2(2000) := '';
365 l_appr VARCHAR2(240);
366 l_apprid NUMBER;
367 pp_required VARCHAR2(1) := 'N';
368 l_cost_center_result VARCHAR2(50) := '';
369 l_allow_overrider VARCHAR2(1) := 'N';
370 l_require_overrider VARCHAR2(1) := 'N';
371 l_overrider_CC VARCHAR2(1) := 'N';
372 l_unexp_err LONG := '';
373 format_error VARCHAR2(200) := 'Spreadsheet Format Error.';
374 overrider_required VARCHAR2(200):= 'Override approver required.';
375 overrider_required_cc VARCHAR2(200) := 'You have changed cost center. Override approver required.';
376 line_not_found VARCHAR2(200) := 'Header Line is not found';
377 purpose_too_long VARCHAR2(200) := 'Purpose too long. Truncate to 240 character.';
378 costcenter_invalid VARCHAR2(255) := '';
379 l_xtemplate_id AP_WEB_DB_EXPTEMPLATE_PKG.expTypes_reportID;
380
381 debug_info VARCHAR2(100) := '';
382 purpose_prompt VARCHAR2(50);
383 template_prompt VARCHAR2(50);
384 approver_prompt VARCHAR2(50);
385
386 l_employee_num HR_EMPLOYEES.employee_num%TYPE := 100;
387 l_employee_name HR_EMPLOYEES.full_name%TYPE := '';
388 l_cost_center VARCHAR2(150);
389 l_OverriderReq VARCHAR2(10);
390 l_approver_id HR_EMPLOYEES.employee_num%TYPE := 100;
391 l_approver_name HR_EMPLOYEES.full_name%TYPE := '';
392
393
394 BEGIN
395
396 -- Changed all occurances of FND_MESSAGE.get() with FND_MESSAGE.get_encoded() AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DISC_PKG', 'start parseExpReportHeader');
397
398 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_EMPTY_REPORT');
399 if (p_text is null) then
400 p_errors(1).text := FND_MESSAGE.Get_encoded();
401 p_errors(1).type := AP_WEB_UTILITIES_PKG.C_WarningMessageType;
402 return;
403 end if;
404
405 FND_MESSAGE.SET_NAME('SQLAP','AP_WEB_COST_CENTER_INVALID');
406 costcenter_invalid := FND_MESSAGE.Get;
407
408 debug_info := 'Getting Header fields';
409 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
410 LOOP
411 BEGIN
412 debug_info := 'Get Line';
413 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
414 BEGIN
415 GetLine(p_text, l_line);
416 EXCEPTION
417 when VALUE_ERROR then
418 debug_info := 'Get Line: truncate to max length';
419 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
420 END;
421
422 /* Here we are assuming one line per field/value pair. And they don't span
423 over multiple cells */
424 debug_info := 'Get Non Empty Field - label';
425 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
426 BEGIN
427 GetNonEmptyFld(l_line, l_label_text);
428 l_label_text := rtrim(l_label_text);
429 EXCEPTION
430 when VALUE_ERROR then
431 debug_info := 'Get Non Empty Field - label: truncate';
432 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
433 END;
434 l_label_index := Disc_GetPromptIndex(l_label_text, p_table);
435
436 /* Ignore if l_label_index = 0 -- we don't recognize this prompt. */
437 if (l_label_index <> 0) then
438 l_label_code := p_table(l_label_index).prompt_code;
439
440 /* If it's 'Line' field, then we've reached the receipts section.
441 Put the line back into p_text since we don't want to process it here. */
442 if (l_label_code = 'AP_WEB_LINE') then
443 p_text := l_label_text || fnd_global.local_chr(9) || l_line || fnd_global.local_chr(13) || '
444 ' || p_text;
445 exit;
446 end if;
447
448 if ((p_table(l_label_index).required='H') OR
449 (p_table(l_label_index).required='NH')) then
450 p_table(l_label_index).required := 'F';
451 elsif (p_table(l_label_index).required = 'F') then
452 p_table(l_label_index).duplicate := 'Y';
453 end if;
454
455 -- Get the value
456 debug_info := 'Get Non Empty Field - value';
457 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
458 BEGIN
459 GetNonEmptyFld(l_line,l_value);
460 l_value := rtrim(l_value);
461 EXCEPTION
462 when VALUE_ERROR then
463 debug_info := 'Get Non Empty Field - value: truncate';
464 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
465 END;
466 if (l_label_code = 'AP_WEB_COSTCTR') then
467 if (length(l_value) > C_CostCenter_Size) then
468 p_header.cost_center := substr(l_value, 1, C_CostCenter_Size);
469 else
470 p_header.cost_center := l_value;
471 end if;
472 --chiho:1310737:change the label code:
473 elsif (l_label_code = 'AP_WEB_REIMBCURR') then
474 p_header.reimbursement_currency_code := l_value;
475 -- Check whether the reimbursement currency is valid: bug 1871756
476 if (p_header.reimbursement_currency_code IS NOT NULL) then
477 if (NOT isValidCurrency (p_header.reimbursement_currency_code)) then
478 fnd_message.set_name('SQLAP','AP_WEB_DISCON_INVALID_CURR');
479 fnd_message.set_token('curr', p_header.reimbursement_currency_code);
480
481 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
482 fnd_message.get_encoded(),
483 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
484 end if;
485 end if;
486
487 elsif (l_label_code = 'AP_WEB_PURPOSE') then
488 if (length(l_value) > C_Purpose_Size) then
489 fnd_message.set_name('SQLAP', 'AP_WEB_MAXLEN_WARNING');
490 fnd_message.set_token('MAXLEN', to_char(C_Purpose_Size));
491 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
492 fnd_message.get_encoded(),
493 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
494 p_header.purpose := substr(l_value, 1, C_Purpose_Size);
495 else
496 p_header.purpose := l_value;
497 end if;
498 purpose_prompt := l_label_text;
499 elsif (l_label_code = 'AP_WEB_EXP_TEMP') then
500 BEGIN
501 p_header.template_name := l_value;
502 EXCEPTION
503 WHEN VALUE_ERROR then
504 p_header.template_name := substr(l_value, 1, C_Xtemplate_Size);
505 fnd_message.set_name('SQLAP', 'AP_WEB_MAXLEN_WARNING');
506 fnd_message.set_token('MAXLEN', to_char(C_Xtemplate_Size));
507 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
508 fnd_message.get_encoded(),
509 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
510 'TEMPLATE');
511 END;
512 template_prompt := l_label_text;
513 elsif (l_label_code = 'AP_WEB_OVERRIDE_APPROVER') then
514 BEGIN
515 l_appr := l_value;
516 EXCEPTION
517 when VALUE_ERROR then
518 l_appr := substr(l_value, 1, C_Approver_Size);
519 fnd_message.set_name('SQLAP', 'AP_WEB_MAXLEN_WARNING');
520 fnd_message.set_token('MAXLEN', to_char(C_Approver_Size));
521 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
522 fnd_message.get_encoded(),
523 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
524 END;
525 approver_prompt := l_label_text;
526 p_header.override_approver_name := l_appr;
527
528 end if;
529 end if; /* l_label_index <> 0 */
530 if (p_text is null) then
531 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_LINE_NOT_FOUND');
532 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
533 fnd_message.get_encoded(),
534 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
535 exit;
536 end if;
537 EXCEPTION
538 when OTHERS then
539 begin
540 --JMARY Calling the AP_DEBUG error message instead of the concatenated string that was passed.
541
542 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
543 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
544 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',format_error);
545 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
546 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
547
548 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,fnd_message.get_encoded(),
549 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
550 end;
551 END;
552 END LOOP;
553
554 debug_info := 'Validate Cost Center';
555 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
556 IF (p_header.cost_center IS NULL) THEN
557 /* default to the employee's own cost center */
558 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(l_employee_name,
559 l_employee_num,
560 l_cost_center,
561 p_header.employee_id);
562
563 /* Bug Fix 1903969. Removed the to_number function when setting the below values
564 for p_header.cost_center. All variables concerned are varchar2, and having a
565 to_number around either the p_def_costcenter or l_cost_center results in
566 ORA-20001: ORA-06502: PL/SQL: numeric or value error: character to number
567 conversion, when the cost center is alpha-numeric.
568 */
569
570 if l_cost_center is null then
571 p_header.cost_center := p_def_costcenter;
572 else
573 p_header.cost_center := l_cost_center;
574 end if;
575 END IF;
576
577 debug_info := 'Validate Purpose';
578 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
579 pp_required := AP_WEB_UTILITIES_PKG.value_specific(
580 p_name => 'AP_WEB_PURPOSE_REQUIRED',
581 p_user_id => p_user_id,
582 p_resp_id => null,
583 p_apps_id => null);
584 if (pp_required = 'Y') then
585 if (p_header.purpose is null) then
586 -- 4087170 : msg for purpose req is shown twice, each is different
587 fnd_message.set_name('SQLAP', 'AP_WEB_PURPOSE_REQUIRED');
588 -- fnd_message.set_name('SQLAP', 'AP_WEB_SEL_BLANK_WRN');
589 -- fnd_message.set_token('FIELD_NAME', purpose_prompt);
590 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
591 fnd_message.get_encoded(),
592 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
593 end if;
594 end if;
595
596 --Bug 3786831: Validating approver, and set the approver name, when profile option,
597 --IE:Approver Required = "Yes with Default" and approver name is not provided
598 --in the upload SpreadSheet data.
599 debug_info := 'Validate Approver';
600 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
601
602 l_OverriderReq := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
603 p_name => 'AP_WEB_OVERRIDE_APPR_REQ',
604 p_user_id => p_user_id,
605 p_resp_id => null,
606 p_apps_id => null);
607
608
609 IF p_header.override_approver_name is NULL AND l_OverriderReq = 'D' THEN
610
611 AP_WEB_UTILITIES_PKG.GetOverrideApproverDetail(p_header.employee_id,l_OverriderReq, l_approver_id, l_approver_name);
612
613 --Don't set the approver id here, set only the approver's name
614 p_header.override_approver_name := l_approver_name;
615
616
617 END IF; --p_header.override_approver_name is NULL
618
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 IF (SQLCODE <> -20001) THEN
623 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
624 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
625 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','parseExpReportHeader');
626 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
627 FND_MESSAGE.SET_TOKEN('PARAMETERS', '');
628 APP_EXCEPTION.RAISE_EXCEPTION;
629 ELSE
630 -- Do not need to set the token since it has been done in the
631 -- child process
632 RAISE;
633 END IF;
634
635 END parseExpReportHeader;
636
637
638 PROCEDURE AP_WEB_DISC_GetExpType(
639 p_table in out nocopy disc_prompts_table,
640 xtypecomp_array out nocopy AP_WEB_PARENT_PKG.MiniString_Array,
641 p_xtemplateid in AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_reportID
642 ) IS
643 /* xtypecomp_array is used later for populating xtypeindex_array. */
644 l_count number;
645 l_icount number := 1;
646 l_exp_type_cursor AP_WEB_DB_EXPTEMPLATE_PKG.ExpTypesOfTemplateCursor;
647
648 BEGIN
649
650 IF ( AP_WEB_DB_EXPTEMPLATE_PKG.GetExpTypesOfTemplateCursor(
651 p_xtemplateid,
652 l_exp_type_cursor ) = TRUE ) THEN
653 l_count := p_table.COUNT+1;
654
655 LOOP
656 fetch l_exp_type_cursor into
657 p_table(l_count).prompt_text,
658 p_table(l_count).prompt_code;
659
660 exit when l_exp_type_cursor%NOTFOUND;
661
662 p_table(l_count).required := 'NR';
663 xtypecomp_array(l_icount) := p_table(l_count).prompt_code;
664 l_count := l_count + 1;
665 l_icount := l_icount + 1;
666 END LOOP;
667 Close l_exp_type_cursor;
668 END IF;
669
670 END AP_WEB_DISC_GetExpType;
671
672 ----------------------------------------
673 -- Validate receipt headers
674 ----------------------------------------
675 PROCEDURE AP_WEB_DISC_Val_Rec_Headers(p_exp in out nocopy long,
676 p_table in out nocopy disc_prompts_table,
677 p_format_errors in out nocopy setup_error_stack)
678 IS
679
680 l_line VARCHAR2(2000) := '';
681 l_fld VARCHAR2(300) := '';
682 l_count2 number; /* format error count */
683 l_index number; /* index of the label in the prompts table */
684 l_code VARCHAR2(50);
685 l_pos number := 1; /* current cell position on the spreadsheet */
686 line_too_long VARCHAR2(300) := 'Header line > 2000 characters';
687 fld_too_long VARCHAR2(300) := 'Field > 300 characters';
688 debug_info VARCHAR2(300) := '';
689 current_calling_sequence VARCHAR2(255) := 'AP_WEB_DISC_Val_Rec_Headers';
690 BEGIN
691
692 debug_info := 'Get Line';
693 BEGIN
694 GetLine(p_exp, l_line);
695 Exception
696 when VALUE_ERROR then
697 debug_info := 'Get Line: truncated';
698 END;
699
700 debug_info := 'Get Field';
701 LOOP
702 BEGIN
703 GetField(l_line, l_fld);
704 l_fld := rtrim(ltrim(l_fld));
705 Exception
706 when VALUE_ERROR then
707 debug_info := 'Get Field: truncated';
708 END;
709 l_index := Disc_GetPromptIndex(l_fld, p_table);
710 if (l_index <> 0) then
711 l_code := p_table(l_index).prompt_code;
712 --
713 -- If the prompt hasn't been found, and set the status to 'F' means
714 -- found.
715 --
716 if ((p_table(l_index).required = 'R') OR
717 (p_table(l_index).required = 'NR')) then
718 p_table(l_index).required := 'F';
719 --
720 -- Match the position of the prompt in the prompt_table with its
721 -- actual position on the spreadsheet. This is just a simple trick
722 -- for ease of matching up the receipt columns with the header when
723 -- processing the receipts later.
724 --
725 if (not SwapPrompts(p_table, l_index, l_pos)) then
726 exit; -- really shouldn't happen
727 end if;
728 elsif (p_table(l_index).required = 'F') then
729 -- Prompt is already found, so it's a duplicate.
730 -- I don't distinguish where it is found. So if a label appeared in
731 -- header before, and then in receipts section again, I count it as
732 -- duplicate. Can make it more fine-grain if there is demand.
733 p_table(l_index).duplicate := 'Y';
734 end if;
735 else
736 if (l_pos > p_table.COUNT) then
737 p_table(l_pos).prompt_code := '';
738 end if;
739 end if;
740 if (l_line is null) then
741 exit;
742 end if;
743 l_pos := l_pos + 1;
744 END LOOP;
745
746 EXCEPTION
747 when OTHERS then
748 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
749 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
750 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
751 current_calling_sequence);
752 FND_MESSAGE.SET_TOKEN('PARAMETERS',
753 'l_line = ' || l_line);
754 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
755 APP_EXCEPTION.RAISE_EXCEPTION;
756 -- AP_WEB_DISC_UNEXP_ERR(SQLCODE || '@@' || l_line);
757
758 END AP_WEB_DISC_Val_Rec_Headers;
759
760 PROCEDURE AP_WEB_DISC_DISPLAY_FORMAT_ERR
761 (p_format_errors in out nocopy setup_error_stack) IS
762
763 i number;
764 l_title VARCHAR2(200) := 'Format Errors';
765
766
767 BEGIN
768 htp.p('<BASE HREF="'||FND_WEB_CONFIG.WEB_SERVER||'">');
769 htp.p('<HTML DIR= "'|| AP_WEB_INFRASTRUCTURE_PKG.GetDirectionAttribute || '">');
770 htp.headOpen;
771 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_FORMAT_ERR');
772 htp.title(fnd_message.get);
773
774 icx_util.copyright;
775 js.scriptOpen;
776
777 AP_WEB_UTILITIES_PKG.CancelExpenseReport;
778 AP_WEB_UTILITIES_PKG.GoBack;
779
780 js.scriptClose;
781 htp.headClose;
782 htp.p('
783 <style>
784 <!--
785 FONT.PROMPTBLACK
786 {FONT-FAMILY: ARIAL, SANS-SERIF;
787 COLOR: BLACK;
788 FONT-SIZE: 10PT;}
789 FONT.DATABLACK
790 {FONT-FAMILY: ARIAL, SANS-SERIF;
791 COLOR: BLACK;
792 FONT-WEIGHT: BOLD;
793 FONT-SIZE: 10PT;}
794 FONT.BUTTON
795 {FONT-FAMILY: ARIAL, SANS-SERIF;
796 COLOR: BLACK;
797 TEXT-DECORATION: NONE;
798 FONT-SIZE: 10PT;}
799 -->
800 </style>
801 ');
802
803 htp.p('<BODY bgcolor=#cccccc>');
804 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_CANNOT_READ');
805 htp.p('<basefont size=+1><B><FONT class=promptblack>' || fnd_message.get ||'</FONT></B></basefont><br>');
806 htp.ulistOpen;
807 for i in 1 .. p_format_errors.COUNT loop
808 htp.p('<FONT class=promptblack>');
809 htp.listItem(p_format_errors(i));
810 htp.p('</FONT>');
811 end loop;
812 htp.ulistClose;
813
814 htp.bodyClose;
815 htp.htmlClose;
816
817 END ap_web_disc_display_format_err;
818
819 ---------------------------------
820 /* Identify the rest of the format errors by going through the
821 prompt_table. */
822
823 PROCEDURE AP_WEB_DISC_FORMAT_ERROR(p_format_errors in out nocopy setup_error_stack,
824 p_table in out nocopy disc_prompts_table,
825 p_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError)
826 IS
827
828 i number := 1;
829 j number; /* p_format_errors count */
830 l_exptype number;
831 l_title varchar2(200) := 'Expense Template Format Errors';
832
833 exptype_found boolean := false; /* at least one exp type found, or Expense
834 Type column found. */
835 amt_found boolean := false; /* either daily amt or receipt amt is found */
836 recCurr_found boolean := false;
837 rate_found boolean := false;
838 rate_index number;
839
840 required_fld_missing varchar2(300) := 'Required field missing';
841 dup_fld_found varchar2(300) := 'Duplicate label found';
842 no_exptype_found varchar2(300) := 'At least one expense type required';
843 no_amt_found varchar2(300) := 'At least one amount field is required';
844 l_unexp_error varchar2(300) := '';
845 l_encd_mesg varchar2(1000); -- Used in the new tech stack for error messages
846
847 BEGIN
848 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DISC_PKG', 'start ap_web_disc_format_error');
849 FOR i in 1 .. p_table.COUNT LOOP
850 --
851 -- Either Daily Amount or Receipt Amount field found is ok
852 --
853 if ((p_table(i).required='F') AND
854 (p_table(i).prompt_code = 'AP_WEB_RECAMT')) then
855 amt_found := true;
856 elsif ((p_table(i).required='F') AND
857 (p_table(i).prompt_code = 'AP_WEB_DAILYAMT')) then
858 amt_found := true;
859 --
860 -- If currency is found, then rate has to exist.
861 --
862 elsif ((p_table(i).required='F') AND
863 (p_table(i).prompt_code = 'AP_WEB_RECCURR')) then
864 recCurr_found := true;
865 elsif ((p_table(i).required='F') AND
866 (p_table(i).prompt_code = 'AP_WEB_CONVRATE')) then
867 rate_found := true;
868 rate_index := i;
869 --
870 -- Expense Type as header found. This is newer than the flattened out
871 -- expense types implementation, but I want to keep it backward compatible.
872 --
873 elsif ((p_table(i).required='F') AND
874 (p_table(i).prompt_code = 'AP_WEB_EXPTYPE')) then
875 exptype_found := true;
876 --
877 -- Required field but not found
878 --
879 elsif ((p_table(i).required = 'H') OR (p_table(i).required = 'R')) then
880 j := p_format_errors.COUNT+1;
881 fnd_message.set_name('SQLAP', 'AP_WEB_SEL_BLANK_WRN');
882 fnd_message.set_token('FIELD_NAME',p_table(i).prompt_text);
883 l_encd_mesg := fnd_message.get_encoded();
884 fnd_message.set_encoded(l_encd_mesg);
885 p_format_errors(j) := fnd_message.get;
886 fnd_message.set_encoded(l_encd_mesg);
887 fnd_msg_pub.add();
888 -- Added for bug 2132994
889 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
890 l_encd_mesg,
891 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
892 /* p_format_errors(j) := p_table(i).prompt_code || ':'
893 || required_fld_missing; */
894 --
895 -- Duplicate field
896 --
897 elsif (p_table(i).duplicate = 'Y') then
898 j := p_format_errors.COUNT+1;
899 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_DUP_FLD');
900 fnd_message.set_token('FIELD_NAME', p_table(i).prompt_text);
901 l_encd_mesg := fnd_message.get_encoded();
902 fnd_message.set_encoded(l_encd_mesg);
903 p_format_errors(j) := fnd_message.get;
904 fnd_message.set_encoded(l_encd_mesg);
905 fnd_msg_pub.add();
906 -- Added for bug 2132994
907 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
908 l_encd_mesg,
909 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
910 /*p_format_errors(j) := p_table(i).prompt_code || ':'
911 || dup_fld_found;*/
912 else /* is it an expense type? */
913 begin
914 l_exptype := to_number(p_table(i).prompt_code);
915 if (l_exptype >= 0) then
916 if (p_table(i).required = 'F') then
917 exptype_found := true;
918 end if;
919 end if;
920 exception
921 when OTHERS then
922 l_exptype := 0; /* not doing anything */
923 end;
924 end if;
925 END LOOP;
926
927 --
928 -- Special cases:
929 -- Expense type
930 --
931 if (exptype_found = false) then
932 j:=p_format_errors.COUNT+1;
933 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_XTYPE_NOT_FOUND');
934 l_encd_mesg := fnd_message.get_encoded();
935 fnd_message.set_encoded(l_encd_mesg);
936 p_format_errors(j) := fnd_message.get;
937 fnd_message.set_encoded(l_encd_mesg);
938 fnd_msg_pub.add();
939 -- Added for bug 2132994
940 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
941 l_encd_mesg,
942 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
943 -- p_format_errors(j) := no_exptype_found;
944 -- RecAmount or DailyAmount
945 elsif (amt_found = false) then
946 j:=p_format_errors.COUNT+1;
947 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_AMT_NOT_FOUND');
948 l_encd_mesg := fnd_message.get_encoded();
949 fnd_message.set_encoded(l_encd_mesg);
950 p_format_errors(j) := fnd_message.get;
951 fnd_message.set_encoded(l_encd_mesg);
952 fnd_msg_pub.add();
953 -- Added for bug 2132994
954 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
955 l_encd_mesg,
956 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
957 -- p_format_errors(j) := no_amt_found;
958 -- Currency and Rate
959 elsif ((recCurr_found = true) AND (rate_found = false)) then
960 j:=p_format_errors.COUNT+1;
961 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_RATE_NOT_FOUND');
962 l_encd_mesg := fnd_message.get_encoded();
963 fnd_message.set_encoded(l_encd_mesg);
964 p_format_errors(j) := fnd_message.get;
965 fnd_message.set_encoded(l_encd_mesg);
966 fnd_msg_pub.add();
967 -- Added for bug 2132994
968 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
969 l_encd_mesg,
970 AP_WEB_UTILITIES_PKG.C_ErrorMessageType);
971 -- If only rate is found, but not currency, set the status to 'NR' so it
972 -- will be ignored later.
973 elsif ((recCurr_found = false) AND (rate_found = true)) then
974 p_table(rate_index).required := 'NR';
975 end if;
976 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DISC_PKG', 'end ap_web_disc_format_error');
977
978 EXCEPTION
979 WHEN OTHERS THEN
980 --JMARY Replaced AP_WEB_DISC_UNEXP_ERR with APP_EXCEPTION.RAISE_EXCEPTION
981 -- Using the AP_DEBUG instaed of the error concatenation.
982
983 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
984 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
985 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'ap_web_disc_format_error');
986 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','');
987 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
988 APP_EXCEPTION.RAISE_EXCEPTION;
989
990 END ap_web_disc_format_error;
991
992 PROCEDURE InitMiniStringArray(p_array OUT NOCOPY AP_WEB_PARENT_PKG.MiniString_Array,
993 p_size IN number,
994 p_val IN varchar2 default '') IS
995
996 i number;
997
998 BEGIN
999
1000 for i in 1 .. p_size loop
1001 p_array(i) := p_val;
1002 end loop;
1003
1004 END InitMiniStringArray;
1005
1006 PROCEDURE InitMedStringArray(p_array OUT NOCOPY AP_WEB_PARENT_PKG.MedString_Array,
1007 p_size IN number,
1008 p_val IN varchar2 default '') IS
1009
1010 i number;
1011
1012 BEGIN
1013
1014 for i in 1 .. p_size loop
1015 p_array(i) := p_val;
1016 end loop;
1017
1018 END InitMedStringArray;
1019
1020
1021 Function CheckPosNum(p_num in number,
1022 p_receipt_errors in out nocopy AP_WEB_UTILITIES_PKG.receipt_error_stack,
1023 p_index in number,
1024 p_prompt in varchar2) Return Boolean IS
1025 BEGIN
1026 if (p_num < 0) then
1027 fnd_message.set_name('SQLAP', 'AP_WEB_NOT_POS_NUM');
1028 fnd_message.set_token('VALUE', to_char(p_num));
1029 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1030 P_index,
1031 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1032 fnd_message.get_encoded(),
1033 p_prompt);
1034 return false;
1035 end if; /* p_num < 0 */
1036 return true;
1037 END CheckPosNum;
1038
1039
1040 PROCEDURE CheckStringSize(p_fld in out nocopy varchar2,
1041 p_size in number,
1042 p_receipt_errors in out nocopy AP_WEB_UTILITIES_PKG.receipt_error_stack,
1043 p_rec_count in number,
1044 p_prompt_index in varchar2) IS
1045 BEGIN
1046
1047 if (length(p_fld) > p_size) then
1048 /* The text is truncated and an error is entered. This should
1049 really be a warning but now we don't distinguish between
1050 errors and warnings. This is going to be displayed once in
1051 the error table to alert the user. Since the text is already
1052 truncated, and the client-size Javascript also guarantee the
1053 size of the string, user should never see it the 2nd time.
1054 */
1055 p_fld := substr(p_fld, 1, p_size);
1056 fnd_message.set_name('SQLAP', 'AP_WEB_MAXLEN_WARNING');
1057 fnd_message.set_token('MAXLEN',to_char(p_size));
1058 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1059 p_rec_count,
1060 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1061 fnd_message.get_encoded(),
1062 p_prompt_index);
1063
1064 end if;
1065
1066 END CheckStringSize;
1067
1068
1069 PROCEDURE PopulateXtypeIndex(xtypecomp_Array in AP_WEB_PARENT_PKG.MiniString_Array,
1070 xtype_array in AP_WEB_PARENT_PKG.MiniString_Array,
1071 xtypeindex_array out nocopy AP_WEB_PARENT_PKG.MiniString_Array) IS
1072 i number;
1073 V_ReceiptCount number;
1074
1075 BEGIN
1076
1077 for V_ReceiptCount in 1 .. xtype_array.COUNT LOOP
1078 if (xtype_array(V_ReceiptCount) is not null) then
1079 i := 1;
1080 LOOP
1081 if (xtype_array(V_ReceiptCount) = xtypecomp_Array(i)) then
1082 xtypeindex_Array(V_ReceiptCount) := to_char(i);
1083 exit;
1084 end if;
1085 if (i = xtypecomp_Array.COUNT) then
1086 exit;
1087 end if;
1088 i := i + 1;
1089 END LOOP;
1090 else
1091 xtypeindex_array(V_ReceiptCount) := '';
1092 end if;
1093 end loop;
1094
1095 END PopulateXtypeIndex;
1096
1097 ---------------------------------------------------
1098 PROCEDURE SerializeHeader(
1099 p_header in AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
1100 p_index in varchar2,
1101 p_xtype in varchar2,
1102 p_recCurr in varchar2,
1103 p_rate in varchar2,
1104 p_trans in varchar2,
1105 p_multiCurr in varchar2,
1106 p_amtDueEmployee in number,
1107 p_amtDueccCompany in number,
1108 ReportLine out nocopy long) IS
1109 l_delim varchar2(6) := '@att@';
1110
1111 l_delim_line varchar2(6) := '@line@';
1112 BEGIN
1113 ReportLine :=
1114 to_char(p_header.report_header_id) || l_delim
1115 || nvl(p_header.summary_start_date,'') || l_delim
1116 || p_header.override_approver_id || l_delim
1117 || p_header.override_approver_name || l_delim
1118 || p_header.cost_center || l_delim
1119 || p_header.employee_id || l_delim
1120 || p_header.template_id || l_delim
1121 || p_header.template_name || l_delim
1122 || nvl(p_header.last_receipt_date,'') || l_delim
1123 || p_header.reimbursement_currency_code || l_delim
1124 || p_header.reimbursement_currency_name || l_delim
1125 || p_multiCurr || l_delim
1126 || p_header.purpose || l_delim
1127 || to_char(p_header.number_Max_FlexField) || l_delim
1128 || to_char(p_amtDueEmployee) || l_delim
1129 || to_char(p_amtDueccCompany) || l_delim
1130 || l_delim_line;
1131 END SerializeHeader;
1132
1133 ------------------------------------------
1134 PROCEDURE parseExpReportReceipts(p_user_id IN NUMBER, -- Bug 2242176, fnd user id
1135 p_exp IN OUT NOCOPY LONG,
1136 p_table IN disc_prompts_table,
1137 P_IsSessionProjectEnabled IN VARCHAR2,
1138 p_report_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
1139 p_receipts OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
1140 Custom1_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1141 Custom2_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1142 Custom3_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1143 Custom4_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1144 Custom5_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1145 Custom6_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1146 Custom7_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1147 Custom8_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1148 Custom9_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1149 Custom10_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1150 Custom11_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1151 Custom12_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1152 Custom13_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1153 Custom14_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1154 Custom15_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1155 p_receipt_errors OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack,
1156 p_errors OUT NOCOPY AP_WEB_UTILITIES_PKG.expError, /*Bug No: 3075093*/
1157 p_error_type IN OUT NOCOPY VARCHAR2) IS
1158 i NUMBER;
1159 j NUMBER;
1160 k NUMBER;
1161 l_line VARCHAR2(2000);
1162 l_fld VARCHAR2(300);
1163 l_prompt_code VARCHAR2(100);
1164 l_prompt_text fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE :='';
1165 pos NUMBER; /* current position in the row */
1166 rec_count NUMBER := 1; /* receipt count */
1167 debug_info VARCHAR2(400) := ''; /* Bug 2530727 - Increased the length */
1168 current_calling_sequence VARCHAR2(255) := 'parseExpReportReceipts';
1169 l_number NUMBER; /* a temp holder for a number */
1170 l_date_str date; /* sysdate, used for comparison */
1171 l_date_str_var varchar2(100);
1172 multi_exptype_error VARCHAR2(200) := 'More than one expense type chosen';
1173
1174 /* just be there since ValidateReportLines is generic. */
1175 l_date_temp VARCHAR2(30) := '';
1176
1177 -- store justif flag fetched from just_required
1178 l_justreq_array AP_WEB_PARENT_PKG.Number_Array;
1179
1180 -- store currency code fetched from Currencies
1181 l_base_curr AP_WEB_DB_AP_INT_PKG.apSetUp_baseCurrencyCode;
1182 curr_found BOOLEAN := false;
1183
1184 l_xtype_text VARCHAR2(80) := '';
1185 l_xtype_code VARCHAR2(25) := '';
1186
1187 l_count NUMBER := 0;
1188 l_binNumber BINARY_INTEGER;
1189 l_dflex_enabled BOOLEAN;
1190 l_tempChar VARCHAR2(1);
1191 l_tempDate Date;
1192 l_project_name PA_PROJECTS_EXPEND_V.project_name%TYPE;
1193 l_task_name PA_TASKS_EXPEND_V.task_name%TYPE;
1194 l_segmentPromptTable segmentPrompt_table; /*to keep segment data for ALL segments*/
1195 l_expIndexTable expIndex_table; /*index to l_segmentPromptTable for a given Expense type*/
1196
1197 /*to store FlexField data for a receipt by the 'correct order'*/
1198 l_dflexTable AP_WEB_DFLEX_PKG.CustomFields_A;
1199 ---
1200 --- Used to determine if the receipt currency user provided is a recognized
1201 --- one. Took away the where clause since receipt can be in any currency,
1202 --- whereas reimbursement currency should be restricted.
1203 ---
1204 l_curr_code_cursor AP_WEB_DB_COUNTRY_PKG.CurrencyCodeCursor;
1205
1206 l_prompt varchar2(40); --Bug 2758267
1207
1208 l_label_code VARCHAR2(100) := '';/*Bug No: 3075093 - prompt code*/
1209 l_label_index NUMBER;/*Bug No: 3075093 - index of prompt in prompt table*/
1210 l_multiple_copy_check BOOLEAN := false;/*Bug No: 3075093 - set to true when import is successful*/
1211
1212 BEGIN
1213
1214 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DISC_PKG', 'start parseExpReportReceipts');
1215 -- check whether descriptive flexfield is enabled under profile option
1216 debug_info := 'Check profile option: descriptive flexfield';
1217 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1218 FND_PROFILE.GET('AP_WEB_DESC_FLEX_NAME', l_tempChar);
1219 -- Bug 3985122 Y-Lines Only, B - Both Header and Lines
1220 --l_dflex_enabled := (NVL(l_tempChar, 'N') = 'Y');
1221 l_dflex_enabled := ((NVL(l_tempChar, 'N') = 'Y') OR (NVL(l_tempChar, 'N') = 'B'));
1222
1223 -- Get today date
1224 IF ( AP_WEB_DB_UTIL_PKG.GetFormattedSysDate(
1225 AP_WEB_INFRASTRUCTURE_PKG.getDateFormat,
1226 l_date_str_var ) <> TRUE ) THEN
1227 l_date_str := NULL;
1228 ELSE
1229 l_date_str := to_date(l_date_str_var,AP_WEB_INFRASTRUCTURE_PKG.getDateFormat);
1230 END IF;
1231
1232 -- Get base currency code
1233 IF ( AP_WEB_DB_AP_INT_PKG.GetBaseCurrInfo( l_base_curr ) <> TRUE ) THEN
1234 l_base_curr := NULL;
1235 END IF;
1236
1237 -- Set up the l_segmentPromptTable array
1238 if (l_dflex_enabled) then
1239 getAllSegmentPrompts(p_user_id, p_report_header_info, l_segmentPromptTable);
1240 end if;
1241
1242 --
1243 -- Looping through spreadsheet lines
1244 --
1245 LOOP
1246 -- Check if reached the end of input.
1247 if (p_exp is null) then
1248 exit;
1249 end if;
1250 /*Bug No: 3075093 - Check if spreadsheet data pasted multiple times */
1251 if (l_multiple_copy_check = true) then
1252 p_error_type := C_setupError;
1253 fnd_message.set_name('SQLAP','OIE_DISC_MULTIPLE_COPY');
1254 AP_WEB_UTILITIES_PKG.AddExpError(p_errors,
1255 fnd_message.get_encoded(),
1256 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1257 null,
1258 0,
1259 AP_WEB_UTILITIES_PKG.C_OtherMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
1260 exit;
1261 end if;
1262
1263 --
1264 -- Initialize receipt error stack
1265 --
1266 p_receipt_errors(rec_count).error_text := '';
1267
1268 --
1269 -- Get line. If length of line > 2000, exception and warning.
1270 --
1271 BEGIN
1272 debug_info :='Get receipt line';
1273 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1274 GetLine(p_exp, l_line);
1275 EXCEPTION
1276 when VALUE_ERROR then
1277 fnd_message.set_name('SQLAP', 'AP_WEB_MAXLEN_WARNING');
1278 fnd_message.set_token('MAXLEN','2000');
1279 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1280 rec_count,
1281 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1282 fnd_message.get_encoded(),
1283 C_RecCurr_Prompt);
1284
1285 END;
1286 --
1287 -- initialize position pointer, arrays
1288 --
1289 debug_info := 'Initializing arrays';
1290 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1291 pos := 1;
1292 p_receipts(rec_count).parameter_id := '';
1293 p_receipts(rec_count).expense_type := '';
1294 p_receipts(rec_count).amount := '';
1295 p_receipts(rec_count).daily_amount := '';
1296 p_receipts(rec_count).start_date:= null;
1297 p_receipts(rec_count).end_date := null;
1298 p_receipts(rec_count).days := '';
1299 p_receipts(rec_count).justification := '';
1300 -- New for multi-curr support
1301 p_receipts(rec_count).receipt_amount := '';
1302 p_receipts(rec_count).currency_code := '';
1303 p_receipts(rec_count).rate := '';
1304
1305 -- Project Accounting support
1306 p_receipts(rec_count).project_number := '';
1307 p_receipts(rec_count).project_id := '';
1308 p_receipts(rec_count).task_number := '';
1309 p_receipts(rec_count).task_id := '';
1310 p_receipts(rec_count).expenditure_type := '';
1311 -- Grants Integration
1312 p_receipts(rec_count).award_number := '';
1313 p_receipts(rec_count).award_id := '';
1314
1315 -- Initialize the l_dflexTable and l_expIndexTable arrays
1316 initIndexAndDflexTable(l_segmentPromptTable, l_dflexTable, l_expIndexTable);
1317
1318 -- initialize found field to false
1319 FOR i in 1..l_segmentPromptTable.count LOOP
1320 l_segmentPromptTable(i).found := false;
1321 END LOOP;
1322 --
1323 -- Loop through items in this line
1324 --
1325 LOOP
1326 debug_info := 'Get receipt item';
1327 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1328 -- If field length > 300, exception and warning
1329 BEGIN
1330 GetField(l_line, l_fld);
1331 l_fld := rtrim(l_fld);
1332 EXCEPTION
1333 when VALUE_ERROR then
1334 fnd_message.set_name('SQLAP', 'AP_WEB_MAXLEN_WARNING');
1335 fnd_message.set_token('MAXLEN','300');
1336 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1337 rec_count,
1338 AP_WEB_UTILITIES_PKG.C_WarningMessageType,
1339 fnd_message.get_encoded());
1340 END;
1341 --
1342 -- pos 1 (Line column) has to be a number and can't be NULL.
1343 --
1344 if (pos = 1) then
1345 /* Bug No: 3075093 - Check whether Import Successfull prompt is reached*/
1346 l_label_index := Disc_GetPromptIndex(l_fld, p_table);
1347 if (l_label_index <> 0) then
1348 l_label_code := p_table(l_label_index).prompt_code;
1349 if( l_label_code = 'AP_WEB_UPLOAD_SUCC') THEN
1350 l_multiple_copy_check := true;
1351 end if;
1352 end if;
1353
1354 if (l_fld IS NULL) then
1355 exit; /* not counting this line */
1356 else
1357 begin
1358 l_number := to_number(l_fld);
1359 exception
1360 when OTHERS then
1361 exit; /* not counting this line */
1362 end;
1363 end if;
1364 else
1365 begin
1366 --
1367 -- See if the current column is an identified column
1368 -- If not, ignore the column
1369 --
1370 if (p_table(pos).required = 'F') then
1371 l_prompt_code := p_table(pos).prompt_code;
1372 else
1373 l_prompt_code := '';
1374 end if;
1375
1376 IF (l_prompt_code = 'AP_WEB_STARTDATE' OR l_prompt_code = 'AP_WEB_ENDDATE') THEN
1377 --FlexDate
1378 debug_info := 'Assigning start date';
1379 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1380 l_date_temp := substr( l_fld, 1, C_MiniString_Size );
1381 l_prompt_text := p_table(pos).prompt_text;
1382 BEGIN
1383 debug_info := 'converting to date';
1384 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1385 BEGIN
1386 /* Bug No # 4315875 : Replaced AP_WEB_INFRASTRUCTURE_PKG.getDateFormat with icx_sec.g_date_format
1387 so that preference changes made by user would be reflected without logging out
1388 */
1389 l_tempDate := to_date( l_date_temp,nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT')) );
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 -- chiho: handle the date value not in default date format:
1393 IF ( SQLCODE = -1843 ) THEN
1394 l_tempDate := to_date( l_date_temp, 'mm/dd/yyyy' );
1395 ELSE
1396 RAISE;
1397 END IF;
1398 END;
1399
1400 IF ( l_prompt_code = 'AP_WEB_STARTDATE' ) THEN
1401 /*p_receipts(rec_count).start_date := to_char( l_tempDate,
1402 nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'))); */
1403 --Bug 5029676
1404 p_receipts(rec_count).start_date := l_tempDate;
1405
1406 ELSIF ( l_prompt_code = 'AP_WEB_ENDDATE' ) THEN
1407 /*p_receipts(rec_count).end_date := to_char( l_tempDate,
1408 nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT')));*/
1409 --Bug 5029676
1410 p_receipts(rec_count).end_date := l_tempDate;
1411 END IF;
1412
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 IF ( SQLCODE = -1841 ) THEN
1416 fnd_message.set_name( 'SQLAP', 'AP_WEB_DISCON_INVALID_FULLYEAR');
1417 fnd_message.set_token ( 'DATE', l_prompt_text);
1418 ELSE
1419 fnd_message.set_name('SQLAP','AP_WEB_INVALID_DATE');
1420 fnd_message.set_token( 'format', nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT')));
1421 END IF;
1422
1423 IF ( l_prompt_code = 'AP_WEB_START_DATE' ) THEN
1424 AP_WEB_UTILITIES_PKG.AddMessage(
1425 P_Receipt_Errors,
1426 rec_count,
1427 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1428 fnd_message.get_encoded(),
1429 C_Date1_Prompt);
1430 ELSE
1431 AP_WEB_UTILITIES_PKG.AddMessage(
1432 P_Receipt_Errors,
1433 rec_count,
1434 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1435 fnd_message.get_encoded(),
1436 C_Date2_Prompt);
1437 END IF;
1438
1439 END;
1440
1441 elsif (l_prompt_code = 'AP_WEB_DAYS') then
1442 debug_info := 'Filling Days_Array';
1443 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1444 p_receipts(rec_count).days := substr(l_fld, 1, C_MiniString_Size);
1445 elsif (l_prompt_code = 'AP_WEB_DAILYAMT') then
1446 debug_info := 'Filling DAmount_Array';
1447 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1448 p_receipts(rec_count).daily_amount := substr(l_fld, 1, C_MiniString_Size);
1449 elsif (l_prompt_code = 'AP_WEB_RECAMT') then
1450 debug_info := 'Filling RecAmount_Array';
1451 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1452 p_receipts(rec_count).receipt_amount := substr(l_fld, 1, C_MiniString_Size);
1453 elsif (l_prompt_code = 'AP_WEB_GRP') then
1454 debug_info := 'Filling Group_Array';
1455 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1456 CheckStringSize(l_fld, C_Grp_Size, p_receipt_errors,rec_count,
1457 C_Grp_Prompt);
1458 p_receipts(rec_count).group_value := l_fld;
1459 elsif (l_prompt_code = 'AP_WEB_JUST') then
1460 debug_info := 'Filling Justification_Array - pre-CheckStringSize';
1461 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1462 CheckStringSize(l_fld, C_Just_Size, p_receipt_errors,rec_count,
1463 C_Just_Prompt);
1464 debug_info := 'Filling Justification_Array: ' || l_fld;
1465 p_receipts(rec_count).justification := l_fld;
1466 debug_info := 'After Filling Justification_Array';
1467 elsif (l_prompt_code = 'AP_WEB_RECPT_MISSING') then
1468 debug_info := 'Filling Receipt_Missing_Array';
1469 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1470 if (l_fld is not null) then
1471 p_receipts(rec_count).receipt_missing_flag := 'Y';
1472 else
1473 p_receipts(rec_count).receipt_missing_flag := '';
1474 end if;
1475 elsif (l_prompt_code = 'AP_WEB_RECCURR') then
1476 debug_info := 'Filling Currency_Array';
1477 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1478 p_receipts(rec_count).currency_code := substr(l_fld, 1, C_MiniString_Size);
1479 --
1480 -- I only want to check the currency once, when I know that currency
1481 -- field exists.
1482 --
1483 -- Check if the currency entered is a valid one.
1484 -- Do this here because in non-discon case, this check is done
1485 -- on client-side by Javascript, so don't want to put it in
1486 -- generic validation routine.
1487 --
1488 if (p_receipts(rec_count).currency_code is not NULL) then
1489 curr_found := isValidCurrency (p_receipts(rec_count).currency_code);
1490 if (p_receipts(rec_count).currency_code = 'OTHER') then
1491 curr_found := true;
1492 end if;
1493 -- Currency not valid
1494 if (NOT curr_found) then
1495 fnd_message.set_name('SQLAP','AP_WEB_DISCON_INVALID_CURR');
1496 fnd_message.set_token('curr', p_receipts(rec_count).currency_code);
1497
1498 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1499 rec_count,
1500 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1501 fnd_message.get_encoded(),
1502 C_RecCurr_Prompt);
1503 p_error_type := C_setupError;
1504 end if;
1505 else
1506 -- Currency is empty. Set to base currency.
1507 p_receipts(rec_count).currency_code := l_base_curr;
1508 end if;
1509 elsif (l_prompt_code = 'AP_WEB_CONVRATE') then
1510 debug_info := 'Filling Rate_Array';
1511 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1512 p_receipts(rec_count).rate := substr(l_fld, 1, C_MiniString_Size);
1513
1514 -- MS Excel passes 0 as .
1515 -- We need to convert it to 0 or TO_NUMBER will choke.
1516 if (p_receipts(rec_count).rate = '.') then
1517 p_receipts(rec_count).rate := '0';
1518 end if;
1519 elsif (l_prompt_code = 'AP_WEB_AMT') then
1520 debug_info := 'Filling Amount_Array';
1521 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1522 p_receipts(rec_count).amount := substr(l_fld, 1, C_MiniString_Size);
1523 elsif (l_prompt_code = 'AP_WEB_EXPTYPE') then
1524 --------------------------------------------
1525 -- The pop list style of expense type entry.
1526 --------------------------------------------
1527 debug_info := 'Filling Exptype array ' || l_fld;
1528 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1529 l_xtype_text := substr(l_fld, 1, C_MiniString_Size);
1530 if (l_xtype_text is not null) then
1531 ------------------------------------------
1532 -- Get parameter id from prompt tabel
1533 ------------------------------------------
1534 l_xtype_code := Disc_GetPrompt(l_xtype_text, p_table);
1535 if (p_receipts(rec_count).parameter_id is not null) then
1536 ------------------------------------------
1537 -- Exptype is already selected.
1538 -- But don't want to complain if they just entered the same
1539 -- thing twice.
1540 ------------------------------------------
1541 if (p_receipts(rec_count).parameter_id <> l_xtype_code) then
1542 ------------------------------------------
1543 -- Record multiple exptype error.
1544 ------------------------------------------
1545 debug_info := 'reporting multi expense type error';
1546 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1547 fnd_message.set_name('SQLAP','AP_WEB_DISCON_MULTI_EXPTYPE');
1548 fnd_message.set_token('exptype', l_xtype_text);
1549 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1550 rec_count,
1551 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1552 fnd_message.get_encoded(),
1553 C_Exptype_Prompt);
1554 p_error_type := C_setupError;
1555
1556 end if;
1557 else -- (p_receipts(rec_count).parameter_id is not null)
1558 ------------------------------------------
1559 -- Try to assign parameter id to xtype_array.
1560 ------------------------------------------
1561 debug_info := 'Get expense type id';
1562 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1563 if (l_xtype_code is null) then
1564 -- This will make to_number to fail, so one exception handles
1565 -- both null and non-null cases.
1566 l_xtype_code := 'not a number';
1567 end if; /* l_xtype_code is null */
1568 BEGIN
1569 l_number := to_number(l_xtype_code);
1570 p_receipts(rec_count).parameter_id := l_xtype_code;
1571 p_receipts(rec_count).expense_type := l_xtype_text;
1572 if (l_dflex_enabled) then
1573 -- Set up l_expIndexTable and dflexTable
1574
1575 /*Bug 2758267:Passed correct prompt instead of Web Friendly Prompt
1576 to function so that correct DFF values will be retreived.
1577 */
1578
1579 IF (AP_WEB_DB_EXPTEMPLATE_PKG.GetExpTypePrompt(
1580 to_number(p_receipts(rec_count).parameter_id), l_prompt)) THEN
1581 p_receipts(rec_count).expense_type := l_prompt;
1582 END IF;
1583
1584 setupIndexAndDflexTable(p_receipts(rec_count).expense_type,
1585 l_segmentPromptTable,
1586 l_dflexTable, l_expIndexTable);
1587 end if;
1588 EXCEPTION
1589 WHEN OTHERS THEN
1590 p_receipts(rec_count).parameter_id := null;
1591 fnd_message.set_name('SQLAP','AP_WEB_DISCON_INVALID_EXPTYPE');
1592 fnd_message.set_token('exptype', l_xtype_text);
1593 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1594 rec_count,
1595 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1596 fnd_message.get_encoded(),
1597 C_Exptype_Prompt);
1598 p_error_type := C_setupError;
1599
1600 END;
1601 end if; -- (p_receipts(rec_count).parameter_id is not null)
1602 end if;
1603
1604 elsif (l_prompt_code = 'AP_WEB_PA_TASK_NUMBER') then
1605
1606 debug_info := 'Filling in PA Task Name';
1607 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1608 -- Fill in PA info only if enabled, calculation of ID done later
1609 -- Requires ProjectID
1610 p_receipts(rec_count).task_number :=
1611 substr(l_fld, 1, C_MiniString_Size);
1612
1613 elsif (l_prompt_code = 'AP_WEB_PA_PROJECT_NUMBER') then
1614
1615 debug_info := 'Filling in PA Project Name';
1616 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1617 -- Fill in PA info only if enabled, calculation of ID done later
1618 p_receipts(rec_count).project_number :=
1619 substr(l_fld, 1, C_MiniString_Size);
1620
1621 elsif (l_prompt_code = 'OIEAWARDNUM') then
1622
1623 debug_info := 'Filling in Award Number';
1624 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1625 p_receipts(rec_count).award_number :=
1626 substr(l_fld, 1, C_MiniString_Size);
1627
1628 elsif (p_table(pos).global_flag) then -- Global FlexField as a column
1629 -- Put the value to the correct position in dflextable
1630 checkValidFlexFieldPrompt(p_table(pos).prompt_text, l_segmentPromptTable, l_expIndexTable, l_number);
1631 if (l_number > 0) then /* valid prompt*/
1632 if (l_segmentPromptTable(l_expIndexTable(l_number)).webEnabled) then
1633 l_dflexTable(l_number).value := l_fld;
1634 l_segmentPromptTable(l_expIndexTable(l_number)).found := true;
1635 else -- use default value
1636 l_dflexTable(l_number).value := l_segmentPromptTable(l_expIndexTable(l_number)).default_value;
1637 end if;
1638 else -- ignore it
1639 null;
1640 end if;
1641 elsif (l_prompt_code = 'AP_WEB_ADDITIONAL_INFO') then -- Quan: for Descriptive FlexField info
1642 if ( p_receipts(rec_count).parameter_id is not null) then /*make sure that expense type is valid*/
1643 -- Read all the flexfield information
1644 WHILE (l_line is not null) LOOP
1645 /* Check if prompt is valid, if so return the 'correct position'*/
1646 if (l_dflex_enabled) then
1647 checkValidFlexFieldPrompt(l_fld, l_segmentPromptTable, l_expIndexTable, l_number);
1648 else
1649 l_number := -1; -- make it invalid
1650 end if;
1651 if (l_number > 0) then /* valid prompt*/
1652 if (NOT l_segmentPromptTable(l_expIndexTable(l_number)).found) then
1653 l_segmentPromptTable(l_expIndexTable(l_number)).found := true;
1654 l_dflexTable(l_number).user_prompt := l_fld;
1655 if (l_line is not null) then
1656 GetField(l_line, l_fld); /* get the value */
1657 -- check if this segment is web-enabled
1658 if (l_segmentPromptTable(l_expIndexTable(l_number)).webEnabled) then
1659 l_fld := rtrim(l_fld);
1660 l_dflexTable(l_number).value := l_fld;
1661 else -- use default value
1662 l_dflexTable(l_number).value := l_segmentPromptTable(l_expIndexTable(l_number)).default_value;
1663 end if;
1664 else -- done with this receipt
1665 null;
1666 end if;
1667 else -- duplicate prompt
1668 fnd_message.set_name('SQLAP','AP_WEB_DISCON_DUP_FLD');
1669 fnd_message.set_token('Field_name', l_fld);
1670 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1671 rec_count,
1672 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1673 fnd_message.get_encoded(),
1674 C_Exptype_Prompt);
1675 p_error_type := C_setupError;
1676 if (l_line is not null) then
1677 GetField(l_line, l_fld); /* get the value and ignore it*/
1678 end if;
1679 end if;
1680 else -- invalid prompt
1681 if (l_line is not null) then
1682 GetField(l_line, l_fld); /* get the value and ignore it*/
1683 end if;
1684 end if;
1685
1686 if (l_line is not null) then /* get the next prompt */
1687 GetField(l_line, l_fld);
1688 l_fld := rtrim(l_fld);
1689 else
1690 l_fld := null; -- finish the loop, done with this receipt.
1691 end if;
1692
1693 END LOOP;
1694 end if;
1695 else
1696 begin
1697 -------------------------------------------------------------
1698 -- Non-VB style Exptype: expense type prompts at table header
1699 -------------------------------------------------------------
1700 debug_info := 'Filling Exptype -- the old way';
1701 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1702 /* See if it is expense type. This should always be at the
1703 end. */
1704 if (p_table(pos).required = 'F') then
1705 l_number := to_number(l_prompt_code);
1706 if (l_fld is not null) then
1707 if ((p_receipts(rec_count).parameter_id is not null) AND
1708 (p_receipts(rec_count).parameter_id <> l_prompt_code)) then
1709 fnd_message.set_name('SQLAP','AP_WEB_DISCON_MULTI_EXPTYPE');
1710 fnd_message.set_token('exptype', p_table(pos).prompt_text);
1711 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1712 rec_count,
1713 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1714 fnd_message.get_encoded(),
1715 C_Exptype_Prompt);
1716 p_error_type := C_setupError;
1717 else
1718 p_receipts(rec_count).parameter_id := l_prompt_code;
1719 p_receipts(rec_count).expense_type := p_table(pos).prompt_text;
1720 end if;
1721 end if; /* l_fld is not null? */
1722 end if; /* required = 'F'? */
1723 exception
1724 when OTHERS then
1725 NULL;
1726 end;
1727 end if;
1728 end;
1729 end if;
1730 pos := pos + 1;
1731 if (l_line is null) then
1732 exit;
1733 end if;
1734 END LOOP;
1735
1736 -------------------------------------------------------
1737 -- if receipt curr is not given, set it to base currency.
1738 -------------------------------------------------------
1739 debug_info := 'Set receipt currency';
1740 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1741 if (p_receipts(rec_count).currency_code is null) then
1742 p_receipts(rec_count).currency_code := l_base_curr;
1743 end if;
1744
1745 -------------------------------------------------------
1746 -- Validate the Project and Task Name fields and fill in
1747 -- Project and Task ID and Expenditure Type fields
1748 -------------------------------------------------------
1749 debug_info := 'Derive PA information';
1750 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1751 AP_WEB_PROJECT_PKG.DerivePAInfoFromUserInput(
1752 P_IsSessionProjectEnabled,
1753 p_receipts(rec_count).project_number,
1754 p_receipts(rec_count).project_id,
1755 l_project_name,
1756 p_receipts(rec_count).task_number,
1757 p_receipts(rec_count).task_id,
1758 l_task_name,
1759 p_receipts(rec_count).expenditure_type,
1760 p_receipts(rec_count).parameter_id);
1761
1762 --------------------------------
1763 --- Bug: 6978992
1764 --- Derive Award Id
1765 --------------------------------
1766 BEGIN
1767 IF (p_receipts(rec_count).award_number is not null) THEN
1768 SELECT award_id
1769 INTO p_receipts(rec_count).award_id
1770 FROM GMS_AWARDS
1771 WHERE award_number = p_receipts(rec_count).award_number;
1772 END IF;
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 p_receipts(rec_count).award_id := null;
1776 END;
1777 -------------------------------------------------------
1778 -- determine if this receipt is worth keeping
1779 -------------------------------------------------------
1780 debug_info := 'Determine if this receipt is worth keeping';
1781 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
1782 BEGIN
1783 if (((to_date(p_receipts(rec_count).start_date,
1784 AP_WEB_INFRASTRUCTURE_PKG.getDateFormat) = l_date_str) OR
1785 (p_receipts(rec_count).start_date is null))
1786 AND
1787 (p_receipts(rec_count).end_date is null)
1788 AND
1789 ((p_receipts(rec_count).days = '0') OR
1790 (p_receipts(rec_count).days is null))
1791 AND
1792 ((p_receipts(rec_count).daily_amount is null) OR
1793 (isAllZeroes(p_receipts(rec_count).daily_amount)))
1794 AND
1795 ((p_receipts(rec_count).amount is null) OR
1796 (isAllZeroes(p_receipts(rec_count).amount)))
1797 ) then
1798 /* It's an empty receipt. Rewind. */
1799 /* Xtype is the only one in which whether or not there is value
1800 matters */
1801 p_receipts(rec_count).parameter_id := '';
1802 rec_count := rec_count - 1;
1803 else -- the receipt is good
1804 -- copy dflfexTable to the customfield array
1805 l_binNumber := rec_count; -- since Propogate... take BINARY_INTEGER
1806 AP_WEB_DFLEX_PKG.propogateReceiptCustFldsInfo(l_dflexTable,
1807 l_binNumber,
1808 Custom1_Array,
1809 Custom2_Array,
1810 Custom3_Array,
1811 Custom4_Array,
1812 Custom5_Array,
1813 Custom6_Array,
1814 Custom7_Array,
1815 Custom8_Array,
1816 Custom9_Array,
1817 Custom10_Array,
1818 Custom11_Array,
1819 Custom12_Array,
1820 Custom13_Array,
1821 Custom14_Array,
1822 Custom15_Array);
1823
1824 -- check if expense type is provided. The reason that we don't do this right after
1825 -- processing expense type is not efficient since all the blank lines would be processed!
1826 -- The best way is to rewrite this module!(Quan 1/26/99)
1827 if (l_xtype_text is null) then
1828 fnd_message.set_name('SQLAP', 'AP_WEB_EXPENSE_TYPE_REQUIRED');
1829 AP_WEB_UTILITIES_PKG.AddMessage(P_Receipt_Errors,
1830 rec_count,
1831 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
1832 fnd_message.get_encoded(),
1833 C_Exptype_Prompt);
1834 end if;
1835 end if;
1836 exception
1837 when OTHERS then
1838 /* the field that should have number doesn't. Not declaring error
1839 now, but don't count it as empty receipt either. Let
1840 validation procedure report error later. */
1841 NULL;
1842 END;
1843
1844 rec_count := rec_count + 1;
1845
1846 END LOOP;
1847
1848 p_report_header_info.receipt_count := TO_CHAR(rec_count - 1);
1849
1850 j := 0;
1851 FOR i IN 1..to_number(p_report_header_info.receipt_count) LOOP
1852 if (p_receipts(i).group_value IS NOT NULL) THEN
1853 return;
1854 end if;
1855 j := j + 1;
1856 END LOOP;
1857
1858 if (j = to_number(p_report_header_info.receipt_count)) then
1859 FOR i IN 1..to_number(p_report_header_info.receipt_count) LOOP
1860 p_receipts(i).group_value := '';
1861 END LOOP;
1862 end if;
1863
1864 EXCEPTION
1865 WHEN OTHERS THEN
1866 BEGIN
1867 -- JMARY Added a check for -20001
1868
1869 IF (SQLCODE <> -20001) THEN
1870 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1871 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1872 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1873 current_calling_sequence);
1874 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1875 'l_line = ' || l_line);
1876 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1877 APP_EXCEPTION.RAISE_EXCEPTION;
1878 ELSE
1879 -- Do not need to set the token since it has been done in the
1880 -- child process
1881 RAISE;
1882 END IF;
1883 END;
1884
1885 END parseExpReportReceipts;
1886
1887
1888 PROCEDURE discValidateExpLines(
1889 p_report_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
1890 p_report_lines_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
1891 p_custom1_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1892 p_custom2_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1893 p_custom3_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1894 p_custom4_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1895 p_custom5_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1896 p_custom6_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1897 p_custom7_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1898 p_custom8_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1899 p_custom9_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1900 p_custom10_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1901 p_custom11_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1902 p_custom12_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1903 p_custom13_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1904 p_custom14_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1905 p_custom15_array IN OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
1906 p_has_core_field_errors OUT NOCOPY BOOLEAN,
1907 p_has_custom_field_errors OUT NOCOPY BOOLEAN,
1908 p_receipts_errors OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack,
1909 p_receipts_with_errors_count OUT NOCOPY BINARY_INTEGER,
1910 p_IsSessionProjectEnabled IN VARCHAR2,
1911 p_calculate_receipt_index IN BINARY_INTEGER DEFAULT NULL,
1912 p_DataDefaultedUpdateable IN OUT NOCOPY BOOLEAN)
1913 IS
1914 l_receipts_with_errors_core BINARY_INTEGER;
1915 l_receipts_with_errors_custom BINARY_INTEGER;
1916 l_errors AP_WEB_UTILITIES_PKG.expError;
1917 l_errors_custom AP_WEB_UTILITIES_PKG.expError;
1918
1919 V_IsSessionTaxEnabled VARCHAR2(1);
1920
1921 l_unexp_err VARCHAR2(2000);
1922 l_debug_info VARCHAR2(2000);
1923
1924 l_receipt_count NUMBER;
1925 l_title AK_REGIONS_VL.NAME%TYPE;
1926 l_prompts AP_WEB_UTILITIES_PKG.prompts_table;
1927 l_recCount INTEGER;
1928
1929 l_allow_credit_lines_profile VARCHAR2(1) := 'N';
1930 l_allow_credit_lines BOOLEAN;
1931
1932 l_just_required_cursor AP_WEB_DB_EXPTEMPLATE_PKG.JustificationExpTypeCursor;
1933
1934 l_curr_precision_cursor AP_WEB_DB_COUNTRY_PKG.CurrencyPrecisionCursor;
1935 l_justreq_array AP_WEB_PARENT_PKG.Number_Array;
1936 l_reimbcurr_precision AP_WEB_DB_COUNTRY_PKG.curr_precision;
1937
1938 V_SysInfoRec AP_WEB_DB_AP_INT_PKG.APSysInfoRec; -- For PATC: Exchange rate type in AP and Functional currency
1939 V_EndExpenseDate DATE; -- For PATC: Latest receipt date
1940 V_DefaultExchangeRate NUMBER; -- For PATC: Exchange rate for func->reimb
1941 -- on latest receipt date
1942 V_DateTemp DATE; -- For PATC: Scratch variable
1943 V_DateFormat VARCHAR2(30);
1944
1945 I INTEGER;
1946 l_report_line_rec AP_WEB_DFLEX_PKG.ExpReportLineRec;
1947 l_temp_array OIE_PDM_NUMBER_T; -- bug 5358186
1948 BEGIN
1949
1950 -- Convert number of maximum number of flexfield segments used
1951 l_receipt_count := TO_NUMBER(p_report_header_info.receipt_count);
1952
1953 AP_WEB_DFLEX_PKG.IsSessionTaxEnabled(
1954 V_IsSessionTaxEnabled);
1955
1956
1957 -- For core case, do NOT assume that the error table is empty. Certain
1958 -- errors are checked while processing the uploaded report in the discon.
1959 -- case.
1960
1961 -- Whatever in p_receipts_errors should belong to core error stack.
1962 -- The only time it could be populated is during disconnected processing.
1963 -- Other times it should be given null since it'll be called after
1964 -- String2*. If not so, make sure to initialize it before calling this
1965 -- procedure.
1966 --
1967
1968 -- Clear p_receipts_errors_custom and p_receipts_errors
1969 AP_WEB_UTILITIES_PKG.InitMessages(l_receipt_count, p_receipts_errors);
1970
1971 -- htp.p('p_receipts_errors size = ' || to_char(p_receipts_errors.count));
1972 --AP_WEB_UTILITIES_PKG.CopyMessages(p_receipts_errors,
1973 -- p_receipts_errors_core);
1974 --AP_WEB_UTILITIES_PKG.ClearMessages(p_receipts_errors);
1975
1976 -- validate core lines fields
1977 l_debug_info := 'ValidateExpLinesCoreFields';
1978 getPrompts(601,'AP_WEB_EXP_VIEW_REC',l_title,l_prompts);
1979
1980 FND_PROFILE.GET('AP_WEB_ALLOW_CREDIT_LINES', l_allow_credit_lines_profile);
1981 if (l_allow_credit_lines_profile = 'Y') then
1982 l_allow_credit_lines := TRUE;
1983 else
1984 l_allow_credit_lines := FALSE;
1985 end if;
1986
1987 l_debug_info := 'Fill justification required array';
1988 IF (AP_WEB_DB_EXPTEMPLATE_PKG.GetJustifReqdExpTypesCursor(l_just_required_cursor)) THEN
1989 i := 1;
1990 LOOP
1991 FETCH l_just_required_cursor INTO l_justreq_array(i);
1992 EXIT when l_just_required_cursor%NOTFOUND;
1993 i := i + 1;
1994 END LOOP;
1995 END IF;
1996
1997 CLOSE l_just_required_cursor;
1998
1999 l_reimbcurr_precision := AP_WEB_DB_COUNTRY_PKG.GetCurrencyPrecision(
2000 p_report_header_info.reimbursement_currency_code);
2001
2002 For l_recCount IN 1..l_receipt_count LOOP
2003 l_report_line_rec := p_report_lines_info(l_recCount);
2004 AP_WEB_VALIDATE_UTIL.ValidateExpLineCoreFields(
2005 null, -- Bug 2242176, use preparer in blue gray
2006 p_report_header_info,
2007 l_report_line_rec,
2008 l_recCount,
2009 l_allow_credit_lines,
2010 l_justreq_array,
2011 l_reimbcurr_precision,
2012 p_calculate_receipt_index,
2013 l_errors);
2014
2015 p_report_lines_info(l_recCount):= l_report_line_rec ;
2016 end loop;
2017 l_receipts_with_errors_core := l_errors.COUNT;
2018
2019 /* p_report_header_info.summary_start_date := l_sdate;
2020 p_report_header_info.summary_end_date := nvl(l_edate, l_sdate);
2021 */
2022
2023 -- validate flexfields
2024 l_debug_info := 'ValidateExpLinesCustomFields';
2025 l_receipts_with_errors_custom := 0;
2026
2027 -- The following calcuations marked with "For PATC" were
2028 -- added for the R11i support for multicurrency in PA.
2029 -- We need to retrieve currency and exchange rate information
2030 -- before calling PATC.
2031
2032 -- For PATC: Used when doing projects verification
2033 V_DateFormat := nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'));
2034
2035 -- For PATC: Determine the time-wise last receipt to use as the
2036 -- exchange rate date
2037 -- Assumes has at least one receipt
2038 l_debug_info := 'Getting latest date in report'||V_DateFormat;
2039 V_EndExpenseDate := to_date(p_report_lines_info(1).start_date, V_DateFormat);
2040 FOR i IN 1 .. l_Receipt_Count LOOP
2041 V_DateTemp := to_date(p_report_lines_info(i).start_date, V_DateFormat);
2042 if (V_EndExpenseDate < V_DateTemp) then
2043 V_EndExpenseDate := V_DateTemp;
2044 end if;
2045
2046 if (p_report_lines_info(i).end_date IS NOT NULL) then
2047 l_debug_info := 'Getting end_date';
2048 V_DateTemp := to_date(p_report_lines_info(i).end_date, V_DateFormat);
2049 if (V_EndExpenseDate < V_DateTemp) then
2050 V_EndExpenseDate := V_DateTemp;
2051 end if;
2052 end if;
2053
2054 END LOOP;
2055
2056 -- For PATC: Get information about functional currency and exchange
2057 -- rate for the last receipt date. The last receipt date will be
2058 -- equal to sysdate.
2059 l_debug_info := 'Getting functional currency and exchange rate info';
2060
2061 IF (NOT AP_WEB_DB_AP_INT_PKG.GetAPSysCurrencySetupInfo(V_SysInfoRec)) THEN
2062 NULL;
2063 END IF;
2064
2065 -- For PATC: Get the default exchange rate for the V_EndExpenseDate
2066 -- reimbursement currency/functional currency
2067 -- We are only calling this once for all receipts
2068 V_DefaultExchangeRate := AP_UTILITIES_PKG.get_exchange_rate(
2069 V_SysInfoRec.base_currency,
2070 p_report_header_info.reimbursement_currency_code,
2071 V_SysInfoRec.default_exchange_rate_type,
2072 V_EndExpenseDate,
2073 'ValidatePATransaction');
2074
2075 l_receipts_with_errors_custom := 0;
2076 FOR l_recCount IN 1..l_receipt_count LOOP
2077 l_report_line_rec := p_report_lines_info(l_recCount);
2078 AP_WEB_VALIDATE_UTIL.ValidateExpLineCustomFields(
2079 null,
2080 p_report_header_info,
2081 l_report_line_rec,
2082 l_recCount,
2083 V_SysInfoRec,
2084 V_DefaultExchangeRate,
2085 V_EndExpenseDate,
2086 V_DateFormat,
2087 p_custom1_array,
2088 p_custom2_array,
2089 p_custom3_array,
2090 p_custom4_array,
2091 p_custom5_array,
2092 p_custom6_array,
2093 p_custom7_array,
2094 p_custom8_array,
2095 p_custom9_array,
2096 p_custom10_array,
2097 p_custom11_array,
2098 p_custom12_array,
2099 p_custom13_array,
2100 p_custom14_array,
2101 p_custom15_array,
2102 V_IsSessionTaxEnabled,
2103 p_IsSessionProjectEnabled,
2104 p_receipts_errors,
2105 p_calculate_receipt_index,
2106 l_errors_custom,
2107 l_receipts_with_errors_custom,
2108 p_DataDefaultedUpdateable,
2109 TRUE, -- Calling from disconnected
2110 p_cust_meals_amount => l_temp_array,
2111 p_cust_accommodation_amount => l_temp_array,
2112 p_cust_night_rate_amount => l_temp_array,
2113 p_cust_pdm_rate => l_temp_array);
2114
2115 -- delete reference to temp array as this is used for per diem only
2116 -- disconnected solution currently does not support per diem
2117 -- deleting prevents inadvertent data corruption
2118 l_temp_array.delete; -- bug 5358186
2119 p_report_lines_info(l_recCount):= l_report_line_rec ;
2120
2121 END LOOP;
2122
2123 -- determine whether there were errors in the custom field
2124 p_has_core_field_errors := (l_receipts_with_errors_core > 0);
2125 p_has_custom_field_errors := (l_receipts_with_errors_custom > 0);
2126
2127 l_debug_info := 'merge error stacks';
2128 AP_WEB_UTILITIES_PKG.MergeExpErrors(l_errors,
2129 l_errors_custom);
2130 AP_WEB_UTILITIES_PKG.MergeErrors(l_errors,
2131 p_receipts_errors);
2132
2133 p_receipts_with_errors_count :=
2134 AP_WEB_UTILITIES_PKG.NumOfReceiptWithError(p_receipts_errors);
2135
2136 EXCEPTION
2137 WHEN OTHERS THEN
2138 -- Using the AP_EBUG instead of the concatenated error text
2139
2140 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2141 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2142 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DiscValidateExpLines');
2143 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','');
2144 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
2145 APP_EXCEPTION.RAISE_EXCEPTION;
2146 END discValidateExpLines;
2147
2148 PROCEDURE Serialize(P_IsSessionProjectEnabled IN VARCHAR2,
2149 Custom1_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2150 Custom2_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2151 Custom3_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2152 Custom4_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2153 Custom5_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2154 Custom6_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2155 Custom7_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2156 Custom8_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2157 Custom9_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2158 Custom10_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2159 Custom11_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2160 Custom12_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2161 Custom13_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2162 Custom14_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2163 Custom15_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
2164 ReportHdrInfo IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
2165 ExpReportLineInfo IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
2166 ReportLine OUT NOCOPY LONG,
2167 p_receipt_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack) IS
2168
2169 l_index VARCHAR2(3) := '1';
2170 l_xtype VARCHAR2(1) := '';
2171 l_recCurr VARCHAR2(1) := '';
2172 l_rate VARCHAR2(1) := '';
2173 l_trans VARCHAR2(20) := 'multi';
2174 l_multiCurr VARCHAR2(1) := 'N';
2175 l_invRate VARCHAR2(1) := 'N';
2176 i NUMBER;
2177 j NUMBER;
2178
2179 l_num NUMBER;
2180
2181 l_line LONG := '';
2182 l_delim VARCHAR2(6) := '@att@';
2183 debug_info VARCHAR2(200) := '';
2184 current_calling_sequence VARCHAR2(100) := 'Serialize';
2185 l_unexp_err LONG := '';
2186 l_amtDueEmployee NUMBER := 0;
2187
2188 V_ErrorText LONG; -- Stores stringify version of error text
2189 V_ErrorField VARCHAR2(1000); -- Stores stringify version of error field
2190
2191 l_recCount NUMBER := 0;
2192 BEGIN
2193
2194 FND_PROFILE.GET('DISPLAY_INVERSE_RATE', l_invRate);
2195 debug_info := 'Header info';
2196
2197 l_amtDueEmployee := 0;
2198 l_recCount := to_number(ReportHdrInfo.receipt_count);
2199
2200 l_amtDueEmployee := ReportTotal2(ExpReportLineInfo);
2201
2202 SerializeHeader
2203 (ReportHdrInfo,
2204 l_index,
2205 l_xtype,
2206 l_recCurr,
2207 l_rate,
2208 l_trans,
2209 l_multiCurr,
2210 l_amtDueEmployee, -- p_amtDueEmployee
2211 0, -- p_amtDueccCompany
2212 l_line);
2213
2214 debug_info := 'Receipts';
2215 for i in 1 .. l_recCount loop
2216 l_line := l_line ||
2217 to_char(ExpReportLineInfo(i).start_date,nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'))) ||
2218 l_delim;
2219 if (ExpReportLineInfo(i).end_date IS NULL) then
2220 l_line := l_line || l_delim;
2221 else
2222 l_line := l_line ||
2223 to_char(ExpReportLineInfo(i).end_date, nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'))) ||
2224 l_delim;
2225 end if;
2226 l_line := l_line || ExpReportLineInfo(i).days || l_delim;
2227 if (ExpReportLineInfo(i).daily_amount IS NULL) then
2228 l_line := l_line || l_delim;
2229 else
2230 l_line := l_line || ExpReportLineInfo(i).daily_amount || l_delim;
2231 end if;
2232
2233 -- 5/29/97,jmiao, take away equation
2234 -- multi-curr support
2235 l_line := l_line || ExpReportLineInfo(i).receipt_amount || l_delim;
2236 l_line := l_line || ExpReportLineInfo(i).rate || l_delim;
2237 l_line := l_line || ExpReportLineInfo(i).amount || l_delim;
2238
2239 if (ExpReportLineInfo(i).group_value IS NULL) then
2240 l_line := l_line || l_delim;
2241 else
2242 l_line := l_line || ExpReportLineInfo(i).group_value || l_delim;
2243 end if;
2244
2245 debug_info := 'Justification';
2246 l_line := l_line || ExpReportLineInfo(i).justification || l_delim;
2247
2248 --chiho:1295832:"IS NULL" -> "IS NOT NULL":
2249 if (ExpReportLineInfo(i).receipt_missing_flag IS NOT NULL) then
2250 l_line := l_line || ExpReportLineInfo(i).receipt_missing_flag || l_delim;
2251 else
2252 l_line := l_line || l_delim;
2253 end if;
2254
2255 debug_info := 'Expense type';
2256 l_line := l_line || ExpReportLineInfo(i).parameter_id || l_delim;
2257 -- multi-curr support
2258 l_line := l_line || ExpReportLineInfo(i).currency_code || l_delim;
2259
2260 debug_info := 'ItemizeId';
2261 l_line := l_line || l_delim;
2262
2263 debug_info := 'cCardTrxnId';
2264 l_line := l_line || l_delim;
2265
2266 debug_info := 'merchant';
2267 l_line := l_line || l_delim;
2268
2269 debug_info := 'merchDocNum';
2270 l_line := l_line || l_delim;
2271
2272 debug_info := 'TaxRef';
2273 l_line := l_line || l_delim;
2274
2275 debug_info := 'TaxRegNum';
2276 l_line := l_line || l_delim;
2277
2278 debug_info := 'TaxPayerID';
2279 l_line := l_line || l_delim;
2280
2281 debug_info := 'SupCountry';
2282 l_line := l_line || l_delim;
2283
2284 debug_info := 'TaxCodeID';
2285 l_line := l_line || ExpReportLineInfo(i).taxId || l_delim;
2286
2287 debug_info := 'OverrideFlag';
2288 l_line := l_line || l_delim;
2289
2290 debug_info := 'AmountIncludeTax';
2291 l_line := l_line || ExpReportLineInfo(i).amount_includes_tax || l_delim;
2292
2293 debug_info := 'TaxCode';
2294 l_line := l_line || l_delim;
2295
2296 -- Get flexfield info
2297 debug_info := 'Dflex Arrays NumMaxFlexField = ' || ReportHdrInfo.number_max_flexfield;
2298 IF (ReportHdrInfo.number_max_flexfield >= 1) THEN
2299 debug_info := 'Dflex Array 1 at receipt' || i;
2300 IF ( Custom1_Array.COUNT > 0 ) THEN
2301 l_line := l_line || Custom1_Array(i).value || l_delim;
2302 ELSE
2303 l_line := l_line || l_delim;
2304 END IF;
2305
2306 END IF;
2307
2308 IF (ReportHdrInfo.number_max_flexfield >= 2) THEN
2309 IF ( Custom2_Array.COUNT > 0 ) THEN
2310 l_line := l_line || Custom2_Array(i).value || l_delim;
2311 ELSE
2312 l_line := l_line || l_delim;
2313 END IF;
2314
2315 END IF;
2316
2317 IF (ReportHdrInfo.number_max_flexfield >= 3) THEN
2318 IF ( Custom3_Array.COUNT > 0 ) THEN
2319 l_line := l_line || Custom3_Array(i).value || l_delim;
2320 ELSE
2321 l_line := l_line || l_delim;
2322 END IF;
2323
2324 END IF;
2325
2326 IF (ReportHdrInfo.number_max_flexfield >= 4) THEN
2327 IF ( Custom4_Array.COUNT > 0 ) THEN
2328 l_line := l_line || Custom4_Array(i).value || l_delim;
2329 ELSE
2330 l_line := l_line || l_delim;
2331 END IF;
2332
2333 END IF;
2334
2335 IF (ReportHdrInfo.number_max_flexfield >= 5) THEN
2336 IF ( Custom5_Array.COUNT > 0 ) THEN
2337 l_line := l_line || Custom5_Array(i).value || l_delim;
2338 ELSE
2339 l_line := l_line || l_delim;
2340 END IF;
2341
2342 END IF;
2343
2344 IF (ReportHdrInfo.number_max_flexfield >= 6) THEN
2345 IF ( Custom6_Array.COUNT > 0 ) THEN
2346 l_line := l_line || Custom6_Array(i).value || l_delim;
2347 ELSE
2348 l_line := l_line || l_delim;
2349 END IF;
2350
2351 END IF;
2352
2353 IF (ReportHdrInfo.number_max_flexfield >= 7) THEN
2354 IF ( Custom7_Array.COUNT > 0 ) THEN
2355 l_line := l_line || Custom7_Array(i).value || l_delim;
2356 ELSE
2357 l_line := l_line || l_delim;
2358 END IF;
2359
2360 END IF;
2361
2362 IF (ReportHdrInfo.number_max_flexfield >= 8) THEN
2363 IF ( Custom8_Array.COUNT > 0 ) THEN
2364 l_line := l_line || Custom8_Array(i).value || l_delim;
2365 ELSE
2366 l_line := l_line || l_delim;
2367 END IF;
2368
2369 END IF;
2370
2371 IF (ReportHdrInfo.number_max_flexfield >= 9) THEN
2372 IF ( Custom9_Array.COUNT > 0 ) THEN
2373 l_line := l_line || Custom9_Array(i).value || l_delim;
2374 ELSE
2375 l_line := l_line || l_delim;
2376 END IF;
2377 END IF;
2378
2379 IF (ReportHdrInfo.number_max_flexfield >= 10) THEN
2380 IF ( Custom10_Array.COUNT > 0 ) THEN
2381 l_line := l_line || Custom10_Array(i).value || l_delim;
2382 ELSE
2383 l_line := l_line || l_delim;
2384 END IF;
2385 END IF;
2386
2387 IF (ReportHdrInfo.number_max_flexfield >= 11) THEN
2388 IF ( Custom11_Array.COUNT > 0 ) THEN
2389 l_line := l_line || Custom11_Array(i).value || l_delim;
2390 ELSE
2391 l_line := l_line || l_delim;
2392 END IF;
2393
2394 END IF;
2395
2396 IF (ReportHdrInfo.number_max_flexfield >= 12) THEN
2397 IF ( Custom12_Array.COUNT > 0 ) THEN
2398 l_line := l_line || Custom12_Array(i).value || l_delim;
2399 ELSE
2400 l_line := l_line || l_delim;
2401 END IF;
2402
2403 END IF;
2404
2405 IF (ReportHdrInfo.number_max_flexfield >= 13) THEN
2406 IF ( Custom13_Array.COUNT > 0 ) THEN
2407 l_line := l_line || Custom13_Array(i).value || l_delim;
2408 ELSE
2409 l_line := l_line || l_delim;
2410 END IF;
2411
2412 END IF;
2413
2414 IF (ReportHdrInfo.number_max_flexfield >= 14) THEN
2415 IF ( Custom14_Array.COUNT > 0 ) THEN
2416 l_line := l_line || Custom14_Array(i).value || l_delim;
2417 ELSE
2418 l_line := l_line || l_delim;
2419 END IF;
2420 END IF;
2421
2422 IF (ReportHdrInfo.number_max_flexfield >= 15) THEN
2423 IF ( Custom15_Array.COUNT > 0 ) THEN
2424 l_line := l_line || Custom15_Array(i).value || l_delim;
2425 ELSE
2426 l_line := l_line || l_delim;
2427 END IF;
2428 END IF;
2429
2430
2431 debug_info := 'Project Enabled';
2432 IF (P_IsSessionProjectEnabled = 'Y') THEN
2433 l_line := l_line || ExpReportLineInfo(I).project_number || l_delim;
2434 l_line := l_line || ExpReportLineInfo(I).task_number || l_delim;
2435 END IF;
2436
2437 -- for receipt visited flag, will validate if set to 'Y'
2438 l_line := l_line || 'Y' || l_delim;
2439
2440 -- for receipt errors
2441 debug_info := 'Receipt errors';
2442
2443 --chiho:1330572:
2444 IF (p_receipt_errors.EXISTS( i )) THEN
2445 l_line := l_line || p_receipt_errors(i).Error_Text || l_delim
2446 || p_receipt_errors(i).Error_Fields || l_delim
2447 || p_receipt_errors(i).Warning_Text || l_delim
2448 || p_receipt_errors(i).Warning_Fields || l_delim;
2449 ELSE
2450 l_line := l_line || l_delim
2451 || l_delim
2452 || l_delim
2453 || l_delim;
2454 END IF;
2455
2456 l_line := l_line || '@line@';
2457 end loop;
2458
2459 ReportLine := l_line;
2460
2461 exception
2462 when OTHERS then
2463 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2464 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2465 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
2466 current_calling_sequence);
2467 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2468 'l_line = ' || l_line);
2469 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2470 APP_EXCEPTION.RAISE_EXCEPTION;
2471
2472 END Serialize;
2473
2474
2475
2476 ------------------------------------------
2477 -- Returns the sum of Amount_Array content.
2478 -- Raise INVALID_NUMBER exception if a value is not
2479 -- a number.
2480 ------------------------------------------
2481 Function ReportTotal(Amount_Array in AP_WEB_PARENT_PKG.MiniString_Array)
2482 Return NUMBER IS
2483 i NUMBER;
2484 l_total NUMBER := 0;
2485 l_num NUMBER;
2486
2487 BEGIN
2488 for i in 1 .. Amount_Array.COUNT loop
2489 begin
2490 l_num := to_number(Amount_Array(i));
2491 if (l_num is not null) then
2492 l_total := l_total + l_num;
2493 end if;
2494 exception
2495 when OTHERS then
2496 l_num:=0;
2497 end;
2498 end loop;
2499 return l_total;
2500 END ReportTotal;
2501
2502
2503 ------------------------------------------
2504 -- Returns the sum of ExpLines content.
2505 -- Raise INVALID_NUMBER exception if a value is not
2506 -- a number.
2507 ------------------------------------------
2508 Function ReportTotal2(ExpLine_Array in AP_WEB_DFLEX_PKG.ExpReportLines_A)
2509 Return NUMBER IS
2510 i NUMBER;
2511 l_total NUMBER := 0;
2512 l_num NUMBER;
2513
2514 BEGIN
2515 for i in 1 .. ExpLine_Array.COUNT loop
2516 begin
2517 l_num := to_number(ExpLine_Array(i).amount);
2518 l_total := l_total + nvl(l_num,0);
2519 exception
2520 when OTHERS then
2521 l_num:=0;
2522 end;
2523 end loop;
2524 return l_total;
2525 END ReportTotal2;
2526
2527
2528 ------------------------------------------
2529 -- Checks whether the given currency code
2530 -- is valid or not.
2531 ------------------------------------------
2532 function isValidCurrency (p_currency_code IN AP_WEB_DB_AP_INT_PKG.apSetUp_baseCurrencyCode)
2533 return boolean IS
2534 ---
2535 --- Used to determine if the receipt currency user provided is a recognized
2536 --- one. Took away the where clause since receipt can be in any currency,
2537 --- whereas reimbursement currency should be restricted.
2538 ---
2539 l_curr_code_cursor AP_WEB_DB_COUNTRY_PKG.CurrencyCodeCursor;
2540
2541 l_currency_array AP_WEB_PARENT_PKG.MiniString_Array;
2542 l_count NUMBER;
2543 l_curr_found BOOLEAN := false;
2544 j BINARY_INTEGER;
2545
2546 BEGIN
2547 IF ( AP_WEB_DB_COUNTRY_PKG.GetCurrCodeCursor(l_curr_code_cursor) ) THEN
2548
2549 l_count := 1;
2550 LOOP
2551 FETCH l_curr_code_cursor INTO l_currency_array(l_count);
2552 EXIT WHEN l_curr_code_cursor%NOTFOUND;
2553 l_count := l_count + 1;
2554 END LOOP;
2555 CLOSE l_curr_code_cursor;
2556 END IF;
2557
2558 --
2559 -- Check if the currency entered is a valid one.
2560 -- Do this here because in non-discon case, this check is done
2561 -- on client-side by Javascript, so don't want to put it in
2562 -- generic validation routine.
2563 --
2564 IF (p_currency_code is not NULL) THEN
2565 FOR j in 1 .. l_currency_array.COUNT LOOP
2566 IF (p_currency_code = l_currency_array(j)) THEN
2567 l_curr_found := true;
2568 EXIT;
2569 END IF;
2570 END LOOP;
2571 END IF;
2572 RETURN l_curr_found;
2573 END isValidCurrency;
2574
2575 PROCEDURE ParseExpReport(
2576 p_user_id IN NUMBER, -- Bug 2242176, fnd user id
2577 p_exp in LONG,
2578 p_table IN OUT NOCOPY disc_prompts_table,
2579 p_costcenter in VARCHAR2,
2580 P_IsSessionProjectEnabled IN VARCHAR2,
2581 p_report_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
2582 p_report_lines_info OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
2583 Custom1_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2584 Custom2_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2585 Custom3_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2586 Custom4_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2587 Custom5_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2588 Custom6_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2589 Custom7_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2590 Custom8_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2591 Custom9_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2592 Custom10_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2593 Custom11_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2594 Custom12_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2595 Custom13_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2596 Custom14_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2597 Custom15_Array OUT NOCOPY AP_WEB_DFLEX_PKG.CustomFields_A,
2598 P_DataDefaultedUpdateable OUT NOCOPY BOOLEAN,
2599 p_errors OUT NOCOPY AP_WEB_UTILITIES_PKG.expError,
2600 p_receipt_errors OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack,
2601 p_error_type OUT NOCOPY VARCHAR2, -- Setup/data/no errors encountered
2602 p_techstack IN VARCHAR2 DEFAULT C_OldStack -- Old or new tech stack
2603 ) IS
2604 l_exp LONG := p_exp;
2605 l_temp_errors Setup_error_stack;
2606
2607 l_receipt_count INTEGER;
2608
2609 l_currencyCode AP_WEB_DB_AP_INT_PKG.apSetUp_baseCurrencyCode;
2610 l_currencyName AP_WEB_DB_COUNTRY_PKG.curr_name;
2611
2612 l_dflex_enabled BOOLEAN;
2613 l_tax_enabled VARCHAR2(1);
2614 l_tempChar VARCHAR2(1);
2615
2616 V_NumMaxPseudoFlexField NUMBER;
2617
2618 debug_info VARCHAR2(2000);
2619
2620 l_apsys_info_rec AP_WEB_DB_AP_INT_PKG.APSysInfoRec;
2621 l_curr_format VARCHAR2(80); /* reimbCurr format */
2622 l_terror_exists BOOLEAN;
2623 EndDate date;
2624
2625 l_template_id AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_reportID;
2626 l_xtypecomp_array AP_WEB_PARENT_PKG.MiniString_Array;
2627 l_last_receipt_date varchar2(25);
2628 l_dateformat varchar2(100);
2629
2630 BEGIN
2631
2632 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DISC_PKG', 'start ParseExpReport');
2633
2634 -- check whether descriptive flexfield is enabled under profile option
2635 debug_info := 'Check profile option: descriptive flexfield';
2636 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2637 FND_PROFILE.GET('AP_WEB_DESC_FLEX_NAME', l_tempChar);
2638 -- Bug 3985122 Y-Lines Only, B - Both Header and Lines
2639 --l_dflex_enabled := (NVL(l_tempChar, 'N') = 'Y');
2640 l_dflex_enabled := ((NVL(l_tempChar, 'N') = 'Y') OR (NVL(l_tempChar, 'N') = 'B'));
2641
2642 -- Compute maximum number of flexfield segments
2643 p_report_header_info.number_max_flexfield := AP_WEB_DFLEX_PKG.GetMaxNumSegmentsUsed;
2644 V_NumMaxPseudoFlexField := AP_WEB_DFLEX_PKG.GetMaxNumPseudoSegmentsUsed(
2645 P_IsSessionProjectEnabled);
2646
2647 -------------------------------------------------------
2648 debug_info := 'parse exp report header';
2649 -------------------------------------------------------
2650 parseExpReportHeader(p_user_id,
2651 l_exp,
2652 p_table,
2653 p_costcenter,
2654 p_report_header_info,
2655 p_errors);
2656
2657 -------------------------------------------------------
2658 debug_info := 'validate the exp report header';
2659 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2660 -------------------------------------------------------
2661
2662 --JMARY : Replaced the call to ValidateReportHeader with ValidateHeaderNoValidSession
2663 -- ValidateHeaderNoValidSession does not have a call to the validatesession().
2664
2665 AP_WEB_VALIDATE_UTIL.ValidateHeaderNoValidSession(
2666 p_user_id => p_user_id,
2667 ExpReportHeaderInfo => p_report_header_info,
2668 p_error => p_errors,
2669 p_bFull_Approver_Validation => TRUE);
2670
2671 --
2672 -- Reach the end of the report already. Display error if any and exit.
2673 -- Checking for newtechstack and oldtechstack to reuse the same code for both the techstacks.
2674 --
2675 if (l_exp is null) then
2676 -- most likely no Line field found
2677 ap_web_disc_format_error(l_temp_errors, p_table, p_errors);
2678 if (l_temp_errors.COUNT > 0) then
2679 p_error_type := C_setupError;
2680 end if;
2681 if (p_techstack = C_oldstack) then
2682 ap_web_disc_display_format_err(l_temp_errors);
2683 end if;
2684 return;
2685 end if;
2686
2687 debug_info := 'Check reimbursement currency';
2688 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2689
2690 --chiho:1310737:initialize the l_currencyCode:
2691 l_currencyCode := p_report_header_info.reimbursement_currency_code;
2692
2693 begin
2694 if (l_currencyCode is null) then
2695 debug_info := 'Get base currency';
2696
2697 IF ( AP_WEB_DB_AP_INT_PKG.GetAPSysCurrencySetupInfo(l_apsys_info_rec) = TRUE ) THEN
2698 l_currencyCode := l_apsys_info_rec.base_currency;
2699 l_currencyName := l_apsys_info_rec.base_curr_name;
2700 END IF;
2701
2702 else
2703 IF ( AP_WEB_DB_AP_INT_PKG.GetCurrNameForCurrCode(l_currencyCode,
2704 l_currencyName) <> TRUE ) THEN
2705 l_currencyName := NULL;
2706 END IF;
2707 end if;
2708 exception
2709 When OTHERS then
2710 RAISE NO_DATA_FOUND;
2711 end;
2712
2713 l_curr_format := FND_CURRENCY.get_format_mask(l_currencyCode, 30);
2714
2715 --
2716 -- Can't get expense type info if template is invalid, so consider it a
2717 -- format error.
2718 --
2719 l_terror_exists := FALSE;
2720 FOR i in 1..p_errors.COUNT LOOP
2721 IF (p_errors(i).field = 'TEMPLATE') then
2722 l_terror_exists := TRUE;
2723 END IF;
2724 END LOOP;
2725
2726 /*Bug 1842255 : Return if the template itself is invalid. */
2727 IF (l_terror_exists = TRUE) then
2728 p_error_type := C_setupError;
2729 if (p_techstack = C_oldstack) then
2730 fnd_message.set_name('SQLAP', 'AP_WEB_DISCON_TEMP_INVALID');
2731 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
2732 end if;
2733 return;
2734 ELSE
2735 debug_info := 'get expense type';
2736 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2737 l_template_id := to_number(p_report_header_info.template_id);
2738
2739 ap_web_disc_GetExpType(p_table, l_xtypecomp_array,
2740 l_template_id);
2741 END IF;
2742
2743 -----------------------------------------------------
2744 debug_info := 'validate receipt headers';
2745 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2746 -----------------------------------------------------
2747 ap_web_disc_val_rec_headers(l_exp, p_table, l_temp_errors);
2748
2749 ------------------------------------------------------
2750 debug_info := 'check format error';
2751 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2752 ------------------------------------------------------
2753 ap_web_disc_format_error(l_temp_errors, p_table, p_errors);
2754
2755 ----------------------------------------------------
2756 debug_info := 'Display format error if any';
2757 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2758 -- Introduced a check to differentiate between oldUI and newUI
2759 -- Introduced p_errors.count <> 0 to set error types -bug 6988475
2760
2761 if (l_temp_errors.COUNT <> 0 OR p_errors.COUNT <> 0) then
2762 p_error_type := C_setupError;
2763 if (p_techstack = C_oldstack) then
2764 ap_web_disc_display_format_err(l_temp_errors);
2765 end if;
2766 return;
2767 end if;
2768
2769 --Populate the header record
2770 debug_info := 'Initialize the record used in flexfield validation';
2771 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2772 p_report_header_info.receipt_count := '0';
2773 p_report_header_info.transaction_currency_type := 'multi';
2774 p_report_header_info.reimbursement_currency_code := l_currencyCode;
2775 p_report_header_info.reimbursement_currency_name := l_currencyName;
2776 p_report_header_info.multi_currency_flag := 'N';
2777 p_report_header_info.inverse_rate_flag := 'N';
2778 p_report_header_info.last_receipt_date := EndDate;
2779
2780 ---------------------------------------------------
2781 debug_info := 'process receipts';
2782 ---------------------------------------------------
2783 ParseExpReportReceipts(p_user_id,
2784 l_exp,
2785 p_table,
2786 P_IsSessionProjectEnabled,
2787 p_report_header_info,
2788 p_report_lines_info,
2789 Custom1_Array,
2790 Custom2_Array,
2791 Custom3_Array,
2792 Custom4_Array,
2793 Custom5_Array,
2794 Custom6_Array,
2795 Custom7_Array,
2796 Custom8_Array,
2797 Custom9_Array,
2798 Custom10_Array,
2799 Custom11_Array,
2800 Custom12_Array,
2801 Custom13_Array,
2802 Custom14_Array,
2803 Custom15_Array,
2804 p_receipt_errors,
2805 p_errors, /*Bug No: 3075093*/
2806 p_error_type);
2807
2808 -- Introduced this check for the NewUI
2809 -- Removed check. This is now done in parseExpReportReceipts. Bug 1871544.
2810 -- Bug No: 3075093 - Included p_errors to add Header level errors in ParseExpReportReceipts
2811
2812 IF (p_techstack = C_Newstack) AND (p_error_type = C_SetupError) THEN
2813 RETURN;
2814 END IF;
2815
2816
2817 l_receipt_count := TO_NUMBER(p_report_header_info.receipt_count);
2818 IF (l_receipt_count > 0) THEN
2819 l_last_receipt_date := to_char(p_report_lines_info(1).start_date, nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT')));
2820 l_dateformat := nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'));
2821 debug_info := 'Populate core fields and empty custom fields';
2822 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2823 FOR I IN 1..l_receipt_count LOOP
2824
2825 p_report_lines_info(I).receipt_index := I;
2826 p_report_lines_info(I).validation_required := 'Y';
2827 p_report_lines_info(I).calculate_flag := NULL;
2828 p_report_lines_info(I).calculated_amount := NULL;
2829 p_report_lines_info(I).copy_calc_amt_into_receipt_amt := NULL;
2830 p_report_lines_info(I).amount_includes_tax := NULL;
2831 p_report_lines_info(I).Tax_Code := NULL;
2832 p_report_lines_info(I).TaxOverrideFlag:= 'N';
2833 p_report_lines_info(I).TaxId := NULL;
2834
2835 IF (to_date(l_last_receipt_date, l_dateformat) <
2836 to_date(p_report_lines_info(I).start_date,l_dateformat)) THEN
2837 l_last_receipt_date := p_report_lines_info(I).start_date;
2838 END IF;
2839
2840 /*
2841 AP_WEB_DFLEX_PKG.SetExpReportLineInfo(p_report_lines_info(I),
2842 I,
2843 Xdate1_Array(I),
2844 Xdate2_Array(I),
2845 Days_Array(I),
2846 DAmount_Array(I),
2847 RecAmount_Array(I),
2848 Rate_Array(I),
2849 Amount_Array(I),
2850 xtype_Array(I),
2851 XTypeName_Array(I),
2852 RecCurr_Array(I),
2853 Group_Array(I),
2854 Justif_Array(I),
2855 receipt_missing_Array(I),
2856 'Y',
2857 NULL,
2858 NULL,
2859 NULL,
2860 NULL,
2861 NULL,
2862 'N',
2863 NULL,
2864 PAProjectID_Array(I),
2865 PAProjectNumber_Array(I),
2866 PATaskID_Array(I),
2867 PATaskNumber_Array(I),
2868 PAExpenditureType_Array(I));
2869 */
2870 debug_info := 'Populate Custom dflex array';
2871 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2872 if (not(l_dflex_enabled)) then -- get default values for custom fields
2873 Custom1_Array(I).value := null;
2874 Custom2_Array(I).value := null;
2875 Custom3_Array(I).value := null;
2876 Custom4_Array(I).value := null;
2877 Custom5_Array(I).value := null;
2878 Custom6_Array(I).value := null;
2879 Custom7_Array(I).value := null;
2880 Custom8_Array(I).value := null;
2881 Custom9_Array(I).value := null;
2882 Custom10_Array(I).value := null;
2883 Custom11_Array(I).value := null;
2884 Custom12_Array(I).value := null;
2885 Custom13_Array(I).value := null;
2886 Custom14_Array(I).value := null;
2887 Custom15_Array(I).value := null;
2888
2889 end if;
2890 END LOOP;
2891
2892 p_report_header_info.last_receipt_date := l_last_receipt_date;
2893
2894 if (not(l_dflex_enabled)) then -- get default values for custom fields
2895 debug_info := 'Populate Custom values with defaults';
2896 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2897 AP_WEB_DFLEX_PKG.PopulateCustomDefaultValues( p_user_id,
2898 p_report_header_info,
2899 p_report_lines_info,
2900 l_receipt_count,
2901 Custom1_Array,
2902 Custom2_Array,
2903 Custom3_Array,
2904 Custom4_Array,
2905 Custom5_Array,
2906 Custom6_Array,
2907 Custom7_Array,
2908 Custom8_Array,
2909 Custom9_Array,
2910 Custom10_Array,
2911 Custom11_Array,
2912 Custom12_Array,
2913 Custom13_Array,
2914 Custom14_Array,
2915 Custom15_Array,
2916 p_report_header_info.number_max_FlexField,
2917 P_DataDefaultedUpdateable);
2918
2919 end if;
2920
2921 -- Bug 2242176, passing fnd user id
2922 -- Bug 2812175, always default tax, regardless of AP_WEB_TAX_ENABLE
2923 -- profile option.
2924 -- AP_WEB_DFLEX_PKG.IsSessionTaxEnabled(l_tax_enabled, p_user_id);
2925 --if (l_tax_enabled = 'Y') then -- get default values for tax fields
2926 debug_info := 'Populate Pseudo values with defaults';
2927 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_DISC_PKG', debug_info);
2928 AP_WEB_DFLEX_PKG.PopulatePseudoDefaultValues( p_report_header_info,
2929 p_report_lines_info,
2930 l_receipt_count,
2931 P_DataDefaultedUpdateable);
2932
2933 --end if;
2934
2935 end if;
2936
2937 EXCEPTION
2938 WHEN OTHERS THEN
2939 BEGIN
2940 -- JMARY Added the -20001 check
2941 -- Check for teckstack to support both the UI's
2942
2943 IF (SQLCODE <> -20001) THEN
2944 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2945 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2946 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
2947 'ParseExpReport');
2948 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2949 'None passed.');
2950 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2951 END IF;
2952 IF (p_techstack = C_oldstack) THEN
2953 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
2954 ELSE
2955 APP_EXCEPTION.RAISE_EXCEPTION;
2956 END IF;
2957 END;
2958 END ParseExpReport;
2959
2960
2961 procedure GetTill(p_exp in out nocopy long,
2962 p_item out nocopy long,
2963 p_sep in varchar2) IS
2964 pos number;
2965
2966 BEGIN
2967
2968 if (p_exp is null) then
2969 p_item := null;
2970 return;
2971 end if;
2972 pos := instr(p_exp, p_sep);
2973 if ((pos is null) OR (pos = 0)) then
2974 p_item := p_exp;
2975 p_exp := null;
2976 return;
2977 end if;
2978 p_item := substr(p_exp, 1, pos-1);
2979 p_exp := substr(p_exp, pos+length(p_sep));
2980
2981 END GetTill;
2982
2983
2984 PROCEDURE GetLine(p_exp in out nocopy long,
2985 p_line out nocopy long,
2986 p_max in number default 2000) IS
2987 sep varchar2(2) := /*fnd_global.local_chr(13)||*/'
2988 '; -- Removed fnd_global.local_chr(13) for R12, bug 5140868
2989 -- pos number;
2990 -- l_exp long := p_exp;
2991 l_line long := '';
2992
2993 BEGIN
2994
2995 GetTill(p_exp, l_line, sep);
2996 if (length(l_line) >= p_max) then
2997 p_line := substr(l_line, 1, p_max) || fnd_global.local_chr(9);
2998 raise VALUE_ERROR;
2999 else
3000 p_line := l_line || fnd_global.local_chr(9);
3001 end if;
3002
3003 END GetLine;
3004
3005 PROCEDURE GetField(p_line in out nocopy long,
3006 p_field out nocopy long,
3007 p_max in number default 300) IS
3008
3009 sep varchar2(1) := fnd_global.local_chr(9);
3010 l_fld long := '';
3011
3012 BEGIN
3013
3014 GetTill(p_line, l_fld, sep);
3015 if (length(l_fld) > p_max) then
3016 p_field := substr(l_fld, 1, p_max-1);
3017 raise VALUE_ERROR;
3018 else
3019 p_field := l_fld;
3020 end if;
3021
3022 END GetField;
3023
3024 PROCEDURE GetNonEmptyFld(p_line in out nocopy long,
3025 p_field out nocopy long,
3026 p_max in number default 300) IS
3027
3028 l_fld varchar2(1000) := '';
3029
3030 BEGIN
3031
3032 LOOP
3033 if (p_line is null) then
3034 p_field := null;
3035 return;
3036 end if;
3037 GetField(p_line, l_fld, p_max);
3038 if (l_fld is not null) then
3039 p_field := l_fld;
3040 return;
3041 end if;
3042 END LOOP;
3043 EXCEPTION
3044 when VALUE_ERROR then
3045 p_field := l_fld;
3046 raise VALUE_ERROR;
3047 END GetNonEmptyFld;
3048
3049 PROCEDURE GetRestFldsConcat(p_line in out nocopy long,
3050 p_field out nocopy varchar2) IS
3051
3052 l_fld varchar2(300) := '';
3053 l_fldc varchar2(2000):='';
3054
3055 BEGIN
3056
3057 LOOP
3058 if (p_line is null) then
3059 p_field := l_fldc;
3060 return;
3061 end if;
3062 GetField(p_line, l_fld);
3063 l_fldc := l_fldc || ' ' ||l_fld;
3064 END LOOP;
3065
3066 END GetRestFldsConcat;
3067
3068 function SwapPrompts(p_table in out nocopy disc_prompts_table,
3069 p_from in number,
3070 p_to in number) return boolean is
3071 l_temp PROMPT_REC;
3072 i number;
3073
3074 BEGIN
3075
3076 if ((p_from <= 0) OR (p_to <= 0)) then
3077 return false;
3078 elsif (p_from = p_to) then
3079 return true;
3080 else
3081 l_temp := p_table(p_from);
3082 if (p_to <= p_table.COUNT) then
3083 p_table(p_from) := p_table(p_to);
3084 else
3085 for i in p_table.COUNT+1 .. (p_to - 1) loop
3086 p_table(i).prompt_code := '';
3087 end loop;
3088 p_table(p_from).prompt_code := '';
3089 end if;
3090 p_table(p_to) := l_temp;
3091 end if;
3092 return true;
3093
3094 end SwapPrompts;
3095
3096
3097 /*
3098 Written by:
3099 Quan Le
3100 Purpose:
3101 To get the FlexField global segments.
3102 Input:
3103 None
3104 Output:
3105 None
3106 Input Output:
3107 l_segments (FND_DFLEX.SEGMENTS_DR): Segments data for the global flexfield
3108 Assumption:
3109 The application is WEB
3110 The flexfield is EXPENSE AP_EXPENSE_REPORT_LINES
3111 Date:
3112 11/25/98
3113 */
3114 procedure getFlexFieldGlobalSegments(p_user_id in NUMBER, -- 2242176
3115 p_segments in out nocopy FND_DFLEX.SEGMENTS_DR) is
3116 l_DFlexField FND_DFLEX.DFLEX_R;
3117 l_DFlexInfo FND_DFLEX.DFLEX_DR;
3118 l_Contexts FND_DFLEX.CONTEXTS_DR;
3119 l_Context FND_DFLEX.CONTEXT_R;
3120 l_IsFlexFieldUsed BOOLEAN;
3121 l_DebugInfo VARCHAR2(100);
3122
3123 begin
3124 p_segments.nsegments := 0;
3125 AP_WEB_DFLEX_PKG.GetExpenseLineDFlexInfo(p_user_id, -- 2242176
3126 l_DFlexField,
3127 l_DFlexInfo,
3128 l_Contexts,
3129 l_IsFlexFieldUsed);
3130
3131 if (l_IsFlexFieldUsed) then
3132 -- Get information about the global context
3133 l_DebugInfo := 'Get information about the global context';
3134 l_Context.flexfield := l_DFlexField;
3135 l_Context.context_code := l_Contexts.context_code(l_Contexts.global_context);
3136 FND_DFLEX.Get_Segments(l_Context, p_segments, TRUE);
3137 end if;
3138
3139 end getFlexFieldGlobalSegments;
3140
3141 /*
3142 Written by:
3143 Quan Le
3144 Purpose:
3145 To get all FlexField segment prompts. The order of the table is:
3146 1. All the global segments ordered by sequence number.
3147 2. All the context segments for context1 order by sequence number.
3148 3. All the context segments for context2 order by sequence number.
3149 4. ...(repeated for the rest of the contexts)
3150 Input:
3151 p_report_header_info : Header record information
3152 Output:
3153 None
3154 Input Output:
3155 p_segmentPromptTable (segmentPrompt_table): Segments data for the flexfield
3156 Assumption:
3157 The application is WEB.
3158 The flexfield is EXPENSE AP_EXPENSE_REPORT_LINES.
3159 Date:
3160 11/25/98
3161 */
3162 procedure getAllSegmentPrompts(p_user_id in NUMBER,
3163 p_report_header_info in AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
3164 p_segmentPromptTable in out nocopy segmentPrompt_table) is
3165 l_DFlexField FND_DFLEX.DFLEX_R;
3166 l_DFlexInfo FND_DFLEX.DFLEX_DR;
3167 l_Contexts FND_DFLEX.CONTEXTS_DR;
3168 l_Context FND_DFLEX.CONTEXT_R;
3169 l_IsFlexFieldUsed BOOLEAN;
3170 l_DebugInfo VARCHAR2(100);
3171
3172 l_segments FND_DFLEX.SEGMENTS_DR; -- For Flexfield global segments
3173 l_count number:=1;
3174 begin
3175 AP_WEB_DFLEX_PKG.GetExpenseLineDFlexInfo(p_user_id, l_DFlexField,
3176 l_DFlexInfo,
3177 l_Contexts,
3178 l_IsFlexFieldUsed);
3179
3180 if (l_IsFlexFieldUsed) then
3181 -- Get the flexfield global prompts first
3182 l_DebugInfo := 'Get information about the global context';
3183 l_Context.flexfield := l_DFlexField;
3184 l_Context.context_code := l_Contexts.context_code(l_Contexts.global_context);
3185 FND_DFLEX.Get_Segments(l_Context, l_segments, TRUE);
3186
3187
3188 FOR i in 1..l_segments.nsegments LOOP
3189 if (l_segments.is_enabled(i)) then
3190 p_segmentPromptTable(l_count).exptype := C_GLOBAL;
3191 p_segmentPromptTable(l_count).segment_num := l_segments.sequence(i);
3192 p_segmentPromptTable(l_count).prompt := l_segments.row_prompt(i);
3193 p_segmentPromptTable(l_count).default_value := AP_WEB_DFLEX_PKG.getSegmentDefault(
3194 l_Context.context_code,
3195 l_segments, i);
3196 p_segmentPromptTable(l_count).found := false;
3197 p_segmentPromptTable(l_count).webEnabled := AP_WEB_DFLEX_PKG.isSegmentWebEnabled(l_segments, i);
3198 l_count := l_count+1;
3199 end if;
3200 END LOOP;
3201
3202 -- Get the rest(context) segments
3203 FOR i in 1..l_contexts.ncontexts LOOP
3204 if (i <> l_Contexts.global_context AND l_Contexts.is_enabled(i)) then
3205 l_Context.context_code := l_Contexts.context_code(i);
3206 FND_DFLEX.Get_Segments(l_Context, l_segments, TRUE);
3207 FOR j in 1..l_segments.nsegments LOOP
3208 p_segmentPromptTable(l_count).exptype := l_Contexts.context_code(i);/*context_name?*/
3209 p_segmentPromptTable(l_count).segment_num := l_segments.sequence(j);
3210 p_segmentPromptTable(l_count).prompt := l_segments.row_prompt(j);
3211 p_segmentPromptTable(l_count).default_value := AP_WEB_DFLEX_PKG.getSegmentDefault(
3212 l_Context.context_code,
3213 l_segments, j);
3214
3215 p_segmentPromptTable(l_count).found := false;
3216 p_segmentPromptTable(l_count).webEnabled := AP_WEB_DFLEX_PKG.isSegmentWebEnabled(l_segments, j);
3217 l_count := l_count+1;
3218 END LOOP;
3219 end if;
3220 END LOOP;
3221 end if;
3222 end getAllSegmentPrompts;
3223
3224 /*
3225 Written by:
3226 Quan Le
3227 Purpose:
3228 To check the validity of a FlexField segment prompt. if the prompt is valid the position
3229 (which determines the customfield array to be used to store the segment data)
3230 of the segment is returned
3231 Input:
3232 p_fld : segment prompt to be checked.
3233 p_segmentPromptTable : Array of ALL the segments for a given Flexfield.
3234 p_expIndexTable: Array of indices to p_segmentPromptTable for a given expense type .
3235 Output:
3236 None
3237 Input Output:
3238 p_number: The position of the segment in the customfield array if the prompt is valid.
3239 Otherwise, it is set to 0.
3240 Assumption:
3241 The application is WEB.
3242 The flexfield is EXPENSE AP_EXPENSE_REPORT_LINES.
3243 p_segmentPromptTable, and p_expIndexTable are set up properly.
3244 Date:
3245 12/02/98
3246 */
3247 procedure checkValidFlexFieldPrompt(p_fld in varchar2,
3248 p_segmentPromptTable in segmentPrompt_table,
3249 p_expIndexTable in expIndex_table,
3250 p_number in out nocopy number) IS
3251 BEGIN
3252 FOR i in 1..p_expIndexTable.count LOOP
3253 if (p_expIndexTable(i)>0 AND p_fld = p_segmentPromptTable(p_expIndexTable(i)).prompt) then
3254 p_number := i;
3255 return;
3256 end if;
3257 END LOOP;
3258 -- Invalid prompt
3259 p_number := 0;
3260 END checkValidFlexFieldPrompt;
3261
3262
3263 /*
3264 Written by:
3265 Quan Le
3266 Purpose:
3267 To set up p_dflexTable and p_expIndextable arrays for a given expense type.
3268 Input:
3269 p_expType : Expense Type
3270 p_segmentPromptTable : Array of ALL the segments for a given Flexfield
3271 Output:
3272 None
3273 Input Output:
3274 p_dflexTable : Array of ALL the segments for a given expense type
3275 p_expIndexTable: Array of indices to p_segmentPromptTable for a given expense type
3276 Assumption:
3277 p_dflexTable was initialized by initDflexTable procedure. Therefore, this procedure
3278 takes care the context sensitive segments only.
3279 The application is WEB .
3280 The flexfield is EXPENSE AP_EXPENSE_REPORT_LINES.
3281 p_segmentPromptTable is set up properly by the following order:
3282 1. Globals segments ordered by segment_number.
3283 2. Context segments for each expense type by the order of segment_number(sequence)
3284 Date:
3285 12/02/98
3286 */
3287 procedure setupIndexAndDflexTable(p_expType in varchar2,
3288 p_segmentPromptTable in segmentPrompt_table ,
3289 p_dflexTable in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
3290 p_expIndexTable in out nocopy expIndex_table) IS
3291 l_count number;
3292 l_start number;
3293 BEGIN
3294 IF (p_segmentPromptTable is null OR p_segmentPromptTable.count = 0) THEN
3295 return;
3296 END IF;
3297
3298 l_count := 1;
3299 -- chiho:bug#825307:propagate the fix made on 11.0:
3300 WHILE ((l_count <= p_segmentPromptTable.count)
3301 AND (p_segmentPromptTable(l_count).exptype = C_GLOBAL)) LOOP
3302 l_count := l_count + 1;
3303 END LOOP;
3304
3305 /*Set up the context sensitive segments */
3306 l_start := l_count;
3307 FOR i in l_start.. p_segmentPromptTable.count LOOP
3308 if (p_segmentPromptTable(i).exptype = p_expType) then
3309 p_dflexTable(l_count).prompt := p_segmentPromptTable(i).prompt;
3310 p_dflexTable(l_count).value := p_segmentPromptTable(i).default_value;
3311 p_expIndexTable(l_count) := i;
3312 l_count := l_count + 1;
3313 end if;
3314 END LOOP;
3315 END;
3316
3317
3318 /*
3319 Written by:
3320 Quan Le
3321 Purpose:
3322 To initialize p_dflexTable array with the default values for global segments. Also
3323 initialize p_expIndexTable to index to p_segmentPromptTable.
3324 Input:
3325 p_segmentPromptTable : Array of ALL the segments for a given Flexfield.
3326 Output:
3327 None
3328 Input Output:
3329 p_dflexTable : Array of ALL the segments for a given expense type
3330 p_expIndexTable: Array of indexes to p_segmentPromptTable
3331 Assumption:
3332 The application is WEB.
3333 The flexfield is EXPENSE AP_EXPENSE_REPORT_LINES.
3334 p_segmentPromptTable is set up properly by the following order:
3335 1. Globals segments ordered by segment_number.
3336 2. Context segments for each expense type by the order of segment_number(sequence)
3337 Date:
3338 12/02/98
3339 */
3340 procedure initIndexAndDflexTable(p_segmentPromptTable in segmentPrompt_table ,
3341 p_dflexTable in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
3342 p_expIndexTable in out nocopy expIndex_table) IS
3343 l_count number;
3344 l_debug_info VARCHAR2(2000);
3345 BEGIN
3346 /* Initialize first */
3347 l_debug_info := 'Initialize dflex table';
3348
3349 FOR i in 1..AP_WEB_DFLEX_PKG.C_AbsoluteMaxFlexField LOOP
3350 p_dflexTable(i).prompt := null;
3351 p_dflexTable(i).user_prompt := null;
3352 p_dflexTable(i).value := null;
3353 p_expIndexTable(i) := 0;
3354 END LOOP;
3355 IF (p_segmentPromptTable is null OR p_segmentPromptTable.count = 0) THEN
3356 return;
3357 END IF;
3358
3359 /* Put the global segments in */
3360 l_debug_info := 'Put global segments in';
3361
3362 l_count := 1;
3363
3364 -- chiho:bug#825307:propagate the fix on 11.0:
3365 WHILE ((l_count <= p_segmentPromptTable.count)
3366 AND (p_segmentPromptTable(l_count).exptype = C_GLOBAL)) LOOP
3367 p_dflexTable(l_count).prompt := p_segmentPromptTable(l_count).prompt;
3368 p_dflexTable(l_count).value := p_segmentPromptTable(l_count).default_value;
3369 p_expIndexTable(l_count) := l_count;
3370 l_count := l_count + 1;
3371 END LOOP;
3372
3373 EXCEPTION
3374 WHEN OTHERS THEN
3375 -- JMARY Replaced AP_WEB_UTILITIES_PKG.DisplayException with APP_EXCEPTION.RAISE_EXCPETION
3376 IF (SQLCODE <> -20001) THEN
3377 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3378 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3379 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3380 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
3381 APP_EXCEPTION.RAISE_EXCEPTION;
3382 ELSE
3383 -- Do not need to set the token since it has been done in the
3384 -- child process
3385 RAISE;
3386 END IF;
3387
3388 END initIndexAndDflexTable;
3389
3390
3391 /*
3392 Written by:
3393 Kristian Widjaja
3394 Purpose:
3395 To inverse the rates if the profile option is set up to do so.
3396 Input:
3397 None
3398 Output:
3399 None
3400 Input Output:
3401 p_receipts: array of expense receipts with modified rates (if any)
3402 Assumption:
3403 The application is WEB.
3404 Rates have already passed validation, in which case they should
3405 contain a valid number or the number '1'.
3406 Date:
3407 24-Aug-2001
3408 */
3409 PROCEDURE InverseRates(p_receipts IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A) IS
3410
3411 l_inverse_rate_profile VARCHAR2(1);
3412
3413 temp_rate NUMBER; /* This holds temporary rate info. */
3414 rec_count NUMBER := 1; /* receipt count */
3415
3416 BEGIN
3417 --
3418 -- Get inverse rate profile option.
3419 --
3420 FND_PROFILE.GET('DISPLAY_INVERSE_RATE', l_inverse_rate_profile);
3421
3422 -- if inverse rate profile option is set to yes, we need to
3423 -- convert the rate to 1/rate.
3424 IF (l_inverse_rate_profile = 'Y') THEN
3425
3426 -- Loop through all receipts
3427 FOR rec_count IN p_receipts.FIRST..p_receipts.LAST LOOP
3428 temp_rate := TO_NUMBER(p_receipts(rec_count).rate);
3429
3430 -- if user enters 0 as rate, it will cause 1/temp_rate
3431 -- to fail. 0 is always invalid.
3432 IF (temp_rate <> 0) THEN
3433 p_receipts(rec_count).rate := SUBSTR(TO_CHAR(1/TEMP_RATE),1,25);
3434 END IF; /* if (temp_rate <> 0) */
3435 END LOOP;
3436 END IF; /* l_inverse_rate_profile = 'Y' */
3437 END InverseRates;
3438
3439
3440 /*
3441 Written by:
3442 Kristian Widjaja
3443 Purpose:
3444 To check whether foreign currencies have an exchange rate of 1 or null.
3445 Fix for bug 1966365
3446 Input:
3447 p_report_header_info: Expense Report Header Information
3448 p_report_lines_info: Expense Report Lines Information
3449 Output:
3450 None
3451 Input Output:
3452 p_receipts_errors: Receipt error stack
3453 Assumption:
3454 The application is WEB.
3455 Rates have already passed validation, in which case they should
3456 contain a valid number, null, or the number '1'.
3457 Date:
3458 30-Aug-2001
3459 */
3460 PROCEDURE ValidateForeignCurrencies(
3461 p_report_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
3462 p_report_lines_info IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
3463 p_receipts_errors IN OUT NOCOPY AP_WEB_UTILITIES_PKG.receipt_error_stack) IS
3464
3465 l_reimbursement_currency_code AP_WEB_DFLEX_PKG.expLines_currCode;
3466 l_errors AP_WEB_UTILITIES_PKG.expError;
3467 l_receipt_count BINARY_INTEGER;
3468 rec_count NUMBER := 1; /* receipt count */
3469 l_date_format VARCHAR2(30);
3470 l_start_date DATE;
3471 l_is_fixed_rate VARCHAR2(1);
3472
3473 --Bug 3068461
3474 l_bUserPrefResult BOOLEAN;
3475 l_userPrefs AP_WEB_DB_USER_PREF_PKG.UserPrefsInfoRec;
3476 l_policyRateOptions AP_WEB_OA_DISC_PKG.PolicyRateOptionsRec;
3477
3478 BEGIN
3479 -- Get reimbursement currency
3480 l_reimbursement_currency_code := p_report_header_info.reimbursement_currency_code;
3481 l_receipt_count := TO_NUMBER(p_report_header_info.receipt_count);
3482 l_date_format := nvl(icx_sec.g_date_format,icx_sec.getNLS_PARAMETER('NLS_DATE_FORMAT'));
3483
3484 --Bug 3068461
3485 -- Get policy rate options
3486 AP_WEB_OA_DISC_PKG.GetPolicyRateOptions(l_policyRateOptions);
3487
3488 -- Get user preferences
3489 l_bUserPrefResult := AP_WEB_DB_USER_PREF_PKG.GetUserPrefs(p_report_header_info.employee_id, l_userPrefs);
3490
3491 -- Check if exchange rate is 1 or NULL for different currencies
3492 -- Loop through all receipts
3493 FOR rec_count IN 1..l_receipt_count LOOP
3494 -- Check if currency codes are different
3495 IF (l_reimbursement_currency_code <>
3496 p_report_lines_info(rec_count).currency_code) THEN
3497
3498 -- Check whether value of rate is 1 or NULL
3499 IF (TO_NUMBER(p_report_lines_info(rec_count).rate)=1) THEN
3500
3501 -- Check if it is a fixed rate - bug 2004968
3502 -- Workaround NULL value for start date. GL' is_fixed_rate API
3503 -- does not handle NULL start dates well.
3504 l_start_date := to_date(p_report_lines_info(rec_count).start_date, l_date_format);
3505 IF ((l_start_date IS NULL) OR (p_report_lines_info(rec_count).currency_code = 'OTHER')) THEN
3506 l_is_fixed_rate := 'N';
3507 ELSE
3508 l_is_fixed_rate :=
3509 GL_CURRENCY_API.is_fixed_rate(p_report_lines_info(rec_count).currency_code, l_reimbursement_currency_code, l_start_date);
3510 END IF;
3511
3512 -- Output error message if it is not a fixed rate
3513 IF (l_is_fixed_rate = 'N') THEN
3514 fnd_message.set_name('SQLAP', 'OIE_FOREIGN_EXCH_RATE_ONE');
3515 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
3516 fnd_message.get_encoded(),
3517 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
3518 C_Rate_Prompt,
3519 rec_count);
3520 END IF;
3521 -- Bug 3068461 : Don't raise an error if in setup the exchange rate is
3522 -- either Yes or if its User Definable and exchange rate in Expenses
3523 -- preferences is Yes
3524 ELSIF ( p_report_lines_info(rec_count).rate IS NULL AND
3525 ((l_policyRateOptions.default_exchange_rates = 'N') OR
3526 (l_policyRateOptions.default_exchange_rates = 'U' AND
3527 l_userPrefs.default_exchange_rate_flag = 'N')) ) THEN
3528
3529 -- Output error message
3530 fnd_message.set_name('SQLAP', 'OIE_NEED_EXCH_RATE');
3531 AP_WEB_UTILITIES_PKG.AddExpError(l_errors,
3532 fnd_message.get_encoded(),
3533 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
3534 C_Rate_Prompt,
3535 rec_count);
3536 END IF; -- rate
3537 END IF; -- reimbursement currency <> receipt currency
3538 END LOOP;
3539
3540 -- Merge errors with receipt error stack
3541 AP_WEB_UTILITIES_PKG.MergeErrors(l_errors, p_receipts_errors);
3542
3543 END ValidateForeignCurrencies;
3544
3545 ------------------------------------------------------------------------------------------------
3546 FUNCTION GetAKRegionPromptsCursor(p_reg_code IN AK_REGION_ITEMS_VL.region_code%TYPE,
3547 p_prompts_cursor OUT NOCOPY PromptsCursor)
3548 RETURN BOOLEAN IS
3549 -----------------------------------------------------------------------------------------------
3550 l_error varchar2(500) := '';
3551 BEGIN
3552 OPEN p_prompts_cursor FOR
3553 select ATTRIBUTE_LABEL_LONG,ATTRIBUTE_CODE
3554 from AK_REGION_ITEMS_VL
3555 where REGION_CODE = p_reg_code
3556 order by DISPLAY_SEQUENCE;
3557
3558 return TRUE;
3559
3560 EXCEPTION
3561 WHEN NO_DATA_FOUND THEN
3562 return FALSE;
3563 WHEN OTHERS THEN
3564 AP_WEB_DB_UTIL_PKG.RaiseException('GetAkRegionPromptsCursor');
3565 APP_EXCEPTION.RAISE_EXCEPTION;
3566 return FALSE;
3567 END GetAKRegionPromptsCursor;
3568
3569 PROCEDURE getPrompts( c_region_application_id in number,
3570 c_region_code in varchar2,
3571 c_title out nocopy AK_REGIONS_VL.NAME%TYPE,
3572 c_prompts out nocopy AP_WEB_UTILITIES_PKG.prompts_table)
3573 IS
3574 l_title AK_REGIONS_VL.NAME%TYPE;
3575 l_prompts icx_util.g_prompts_table;
3576 i number;
3577 BEGIN
3578 --
3579 -- Populate Prompt tables for translation
3580 --
3581 icx_util.getPrompts(c_region_application_id,
3582 c_region_code,
3583 l_title,
3584 l_prompts);
3585
3586 c_prompts(0) := l_prompts(0);
3587
3588 FOR i in 1 .. to_number(l_prompts(0))
3589 LOOP
3590 c_prompts(i) := l_prompts(i);
3591 END LOOP;
3592
3593 END getPrompts;
3594
3595 END AP_WEB_DISC_PKG;
[Home] [Help]