[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_PARENT_PKG
Source
1 PACKAGE BODY AP_WEB_PARENT_PKG AS
2 /* $Header: apwxexpb.pls 120.4 2006/02/24 11:22:43 sbalaji ship $ */
3
4 /* The prompt index are relative to AP_WEB_EXP_VIEW_REC */
5 C_Date1_Prompt CONSTANT varchar2(3) := '6';
6 C_Date2_Prompt CONSTANT varchar2(3) := '7';
7 C_Days_Prompt CONSTANT varchar2(3) := '8';
8 C_DAmount_Prompt CONSTANT varchar2(3) := '9';
9 C_Amount_Prompt CONSTANT varchar2(3) := '23';
10 C_Exptype_Prompt CONSTANT varchar2(3) := '11';
11 C_Just_Prompt CONSTANT varchar2(3) := '12';
12 C_Grp_Prompt CONSTANT varchar2(3) := '24';
13 C_Missing_Prompt CONSTANT varchar2(3) := '15';
14 C_RecAmt_Prompt CONSTANT varchar2(3) := '10';
15 C_Rate_Prompt CONSTANT varchar2(3) := '22';
16
17
18 /*
19 Written by:
20 Quan Le
21 Purpose:
22 To retrieve the first string that is delimited @att@ from p-string. The return string and its delimiter
23 will be removed from p_string
24 The format of the string is:
25 <string>"@att@"<string>"@att@"...
26 Input:
27 p_delimiterSize: size of the delimiter
28 Output:
29 first string that is delimited @att@
30 Input Output:
31 p_string : string of the above format
32 Assumption:
33 Date:
34 11/10/99
35 */
36 function getNext(p_string in out nocopy varchar2,
37 p_delimiterSize in number) return varchar2
38 is
39 l_position number;
40 l_out varchar2(240);
41 begin
42 l_position := instrb(p_string, '@att@');
43 l_out := substrb(p_string, 1, l_position-1);
44 p_string := substrb(p_string, l_position + p_delimiterSize);
45 return l_out;
46 EXCEPTION
47 WHEN OTHERS THEN
48 BEGIN
49 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
50 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
51 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getNext');
52 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
53 END;
54
55 end getNext;
56
57 function getNextLong(p_string in out nocopy varchar2,
58 p_delimiterSize in number) return long
59 is
60 l_position number;
61 l_out long;
62 begin
63 l_position := instrb(p_string, '@att@');
64 l_out := substrb(p_string, 1, l_position-1);
65 p_string := substrb(p_string, l_position + p_delimiterSize);
66 return l_out;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 BEGIN
71 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
72 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
73 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getNextLong');
74 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
75 END;
76
77 end getNextLong;
78
79
80 -----------------------------------
81 -- Given the long report string, only fetch header info, and return
82 -- the rest of the string.
83 -----------------------------------
84 PROCEDURE String2PLSQL_Header(V_line in out nocopy long,
85 P_IsSessionProjectEnabled IN VARCHAR2,
86 ExpReportHeaderInfo out nocopy AP_WEB_DFLEX_PKG.ExpReportHeaderRec) IS
87
88 position number;
89 debug_info varchar2(200);
90 l_org_id AP_WEB_DB_HR_INT_PKG.empCurrent_orgID;
91 current_calling_sequence varchar2(100) := 'String2PLSQL_Header';
92
93 V_ExpenditureOrganizationID VARCHAR2(15);
94 V_IsSessionProjectEnabled varchar2(1);
95 BEGIN
96
97 V_line := replace(V_line, '
98 ', '');
99 -- bug 225419: Do not use CHR character function in PL/SQL
100 -- V_line := replace(V_line, chr(13) || '
101 -- ', '');
102
103
104 -- Get restored report header ID
105 position := instrb(V_line, '@att@');
106 ExpReportHeaderInfo.report_header_id := substrb(V_line, 1, position-1);
107 V_line := substrb(V_line, position+5);
108
109 -- Get position within array where user last was
110 debug_info := 'Getting Start Date';
111 position := instrb(V_line, '@att@');
112 ExpReportHeaderInfo.summary_start_date := substrb(V_line, 1, position-1);
113 V_line := substrb(V_line, position+5);
114
115 -- Get approver id
116 debug_info := 'Getting approver id';
117 position := instrb(V_line, '@att@');
118 ExpReportHeaderInfo.override_approver_id := substrb(V_line, 1, position-1);
119 V_line := substrb(V_line, position+5);
120
121 -- Get approver name
122 debug_info := 'Getting approver name';
123 position := instrb(V_line, '@att@');
124 ExpReportHeaderInfo.override_approver_name := substrb(V_line, 1, position-1);
125 V_line := substrb(V_line, position+5);
126
127
128 -- Get Cost Center
129 debug_info := 'Getting cost center';
130 position := instrb(V_line, '@att@');
131 ExpReportHeaderInfo.cost_center := substrb(V_line, 1, position-1);
132 V_line := substrb(V_line, position+5);
133
134
135
136 -- Get employee_id that ValidateReport has been called in
137 debug_info := 'Getting employee id';
138 position := instrb(V_line, '@att@');
139 ExpReportHeaderInfo.employee_id := substrb(V_line, 1, position-1);
140 V_line := substrb(V_line, position+5);
141
142
143 -- Get exp_report_id (PK of Expense Report Templates)
144 debug_info := 'Getting exp report id';
145 position := instrb(V_line, '@att@');
146 ExpReportHeaderInfo.template_id := substrb(V_line, 1, position-1);
147 V_line := substrb(V_line, position+5);
148
149
150 -- Get template name (PK of Expense Report Templates)
151 debug_info := 'Getting exp report id';
152 position := instrb(V_line, '@att@');
153 ExpReportHeaderInfo.template_name := substrb(V_line, 1, position-1);
154 V_line := substrb(V_line, position+5);
155
156
157
158 -- Get Last Receipt Date
159 debug_info := 'Getting last receipt date';
160 position := instrb(V_line, '@att@');
161 ExpReportHeaderInfo.last_receipt_date := substrb(V_line, 1, position-1);
162 V_line := substrb(V_line, position+5);
163
164 -- Get Reimbursement Currency
165 debug_info := 'Getting reimbCurr';
166 position := instrb(V_line, '@att@');
167 ExpReportHeaderInfo.reimbursement_currency_code := substrb(V_line, 1, position-1);
168 V_line := substrb(V_line, position+5);
169
170 -- Get Reimbursement Currency Name
171 debug_info := 'Getting reimbCurr name';
172 position := instrb(V_line, '@att@');
173 ExpReportHeaderInfo.reimbursement_currency_name := substrb(V_line, 1, position-1);
174 V_line := substrb(V_line, position+5);
175
176 -- Get Multi-Currency flag (Y, N)
177 debug_info := 'Getting multi curr flag';
178 position := instrb(V_line, '@att@');
179 ExpReportHeaderInfo.multi_currency_flag := substrb(V_line, 1, position-1);
180 V_line := substrb(V_line, position+5);
181
182
183 -- Get Purpose
184 debug_info := 'Getting purpose';
185 position := instrb(V_line, '@att@');
186 ExpReportHeaderInfo.purpose := substrb(V_line, 1, position-1);
187 V_line := substrb(V_line, position+5);
188
189 -- Get maximum number of flexfields used
190 debug_info := 'Getting maximum number of flex fields';
191 position := instrb(V_line, '@att@');
192 ExpReportHeaderInfo.number_max_flexfield := substrb(V_line, 1, position-1);
193 V_line := substrb(V_line, position+5);
194
195
196 -- Get amount due employee
197 debug_info := 'Getting amount due employee';
198 position := instrb(V_line, '@att@');
199 ExpReportHeaderInfo.amt_due_employee := substrb(V_line, 1, position-1);
200 V_line := substrb(V_line, position+5);
201
202 -- Get amount due cc company
203 debug_info := 'Getting amount due cc company';
204 position := instrb(V_line, '@att@');
205 ExpReportHeaderInfo.amt_due_ccCompany := substrb(V_line, 1, position-1);
206 V_line := substrb(V_line, position+5);
207
208
209 -- Go past '@line@'
210 debug_info := 'Past Line';
211 position := instrb(V_line, '@line@');
212 V_line := substrb(V_line, position+6);
213
214
215 -- Get organization ID for employee if project enabled
216 ExpReportHeaderInfo.expenditure_organization_id := NULL;
217
218 AP_WEB_PROJECT_PKG.IsSessionProjectEnabled(ExpReportHeaderInfo.employee_id,
219 ICX_SEC.getID(icx_sec.PV_WEB_USER_ID),
220 V_IsSessionProjectEnabled);
221
222 begin
223 if V_IsSessionProjectEnabled = 'Y' then
224 IF (AP_WEB_DB_HR_INT_PKG.GetEmpOrgId(ExpReportHeaderInfo.employee_id, l_org_id)) THEN
225 ExpReportHeaderInfo.expenditure_organization_id := l_org_id;
226 END IF;
227 end if;
228 exception
229 when others then
230 BEGIN
231 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
232 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
233 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
234 current_calling_sequence);
235 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
236 APP_EXCEPTION.RAISE_EXCEPTION;
237 END;
238 end;
239
240 END String2PLSQL_Header;
241
242 PROCEDURE String2PLSQL_Receipts(P_IsSessionTaxEnabled IN VARCHAR2,
243 P_IsSessionProjectEnabled IN VARCHAR2,
244 receipt_error_Array in out nocopy AP_WEB_UTILITIES_PKG.receipt_error_stack,
245 V_Line in out nocopy long,
246 ExpReportHeaderInfo in out nocopy AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
247 ExpReportLinesInfo out nocopy AP_WEB_DFLEX_PKG.ExpReportLines_A,
248 Custom1_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
249 Custom2_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
250 Custom3_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
251 Custom4_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
252 Custom5_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
253 Custom6_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
254 Custom7_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
255 Custom8_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
256 Custom9_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
257 Custom10_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
258 Custom11_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
259 Custom12_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
260 Custom13_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
261 Custom14_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
262 Custom15_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A)
263 IS
264
265 debug_info varchar2(255) := '';
266 current_calling_sequence varchar2(255) :=
267 'AP_WEB_PARENT_PKG.String2PLSQL_Receipts';
268 position number;
269 V_ReceiptVisited VARCHAR2(1);
270 I NUMBER;
271 l_temp VARCHAR2(30);
272 V_NumMaxPseudoFlexField NUMBER;
273 V_AmtInclTax VARCHAR2(1);
274 V_TaxName VARCHAR2(15);
275 V_TaxOverrideFlag VARCHAR2(2);
276 V_TaxId VARCHAR2(15);
277 V_PAProjectNumber pa_projects.segment1%type;
278 V_PATaskNumber pa_tasks.task_number%type;
279 l_PAProjectName PA_PROJECTS_EXPEND_V.project_name%TYPE;
280 l_PATaskName PA_TASKS_EXPEND_V.task_name%TYPE;
281 V_PAProjectID VARCHAR2(15) := NULL;
282 V_PATaskID VARCHAR2(15) := NULL;
283 V_PAExpenditureType AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paExpenditureType := NULL;
284
285 V_ErrorMessage LONG;
286 V_ErrorField VARCHAR2(100);
287 V_WarningMessage LONG;
288 V_WarningField VARCHAR2(100);
289 V_ReceiptCount Number := 0;
290
291 -- chiho:
292 l_exp_type VARCHAR2(30);
293 l_date_format VARCHAR2(30);
294 l_date DATE;
295 l_is_fixed_rate VARCHAR2(1);
296 l_euro_rate NUMBER;
297 l_inverse_rate_profile VARCHAR2(1);
298
299 l_tax_code VARCHAR2(15);
300
301 BEGIN
302
303 V_NumMaxPseudoFlexField := AP_WEB_DFLEX_PKG.GetMaxNumPseudoSegmentsUsed(
304 P_IsSessionProjectEnabled);
305
306
307 LOOP
308 if (nvl(length(V_line),0) < 13) then
309 Exit;
310 end if;
311
312
313 -- Increment Receipt Counter
314 V_ReceiptCount := V_ReceiptCount + 1;
315
316 -- Initialize custom fields array
317 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom1_Array(V_ReceiptCount));
318 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom2_Array(V_ReceiptCount));
319 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom3_Array(V_ReceiptCount));
320 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom4_Array(V_ReceiptCount));
321 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom5_Array(V_ReceiptCount));
322 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom6_Array(V_ReceiptCount));
323 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom7_Array(V_ReceiptCount));
324 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom8_Array(V_ReceiptCount));
325 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom9_Array(V_ReceiptCount));
326 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom10_Array(V_ReceiptCount));
327 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom11_Array(V_ReceiptCount));
328 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom12_Array(V_ReceiptCount));
329 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom13_Array(V_ReceiptCount));
330 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom14_Array(V_ReceiptCount));
331 AP_WEB_DFLEX_PKG.ClearCustomFieldRec(Custom15_Array(V_ReceiptCount));
332
333 debug_info := 'Parsing Receipt '||to_char(V_ReceiptCount);
334
335 -- Pull Detail-level values
336 --
337 -- Get Start Date of Receipt
338 ExpReportLinesInfo(V_ReceiptCount).start_date := getNext(V_line, 5);
339
340 -- Get End Date of Receipt
341 ExpReportLinesInfo(V_ReceiptCount).end_date := getNext(V_line, 5);
342
343 -- Get Span of Expense in Days
344 ExpReportLinesInfo(V_ReceiptCount).days := getNext(V_line, 5);
345
346 -- Get Daily Amount
347 ExpReportLinesInfo(V_ReceiptCount).daily_amount := getNext(V_line, 5);
348
349 -- Get Receipt Amount
350 ExpReportLinesInfo(V_ReceiptCount).receipt_amount := getNext(V_line, 5);
351
352 -- Get Conversion Rate
353 ExpReportLinesInfo(V_ReceiptCount).rate := substrb(getNext(V_line, 5),1,25);
354
355
356 -- Get Receipt Amount (in Reimbursement Currency)
357 ExpReportLinesInfo(V_ReceiptCount).amount := getNext(V_line, 5);
358
359 -- Get Group
360 ExpReportLinesInfo(V_ReceiptCount).group_value := getNext(V_line, 5);
361
362 -- Get Justification
363 -- bug 225419: Do not use CHR character function in PL/SQL
364 -- ExpReportLinesInfo(V_ReceiptCount).justification := replace(getNext(V_line, 5), chr(13)||'
365 -- ',' ');
366 ExpReportLinesInfo(V_ReceiptCount).justification := replace(getNext(V_line, 5), '
367 ',' ');
368
369 -- Get Receipt Missing flag
370 ExpReportLinesInfo(V_ReceiptCount).receipt_missing_flag := getNext(V_line, 5);
371
372 -- Get Expense Type
373 -- chiho:make the expense type NULL if no valid value was entered:
374 BEGIN
375 l_exp_type := getNext( V_line, 5 );
376 ExpReportLinesInfo(V_ReceiptCount).parameter_id := TO_NUMBER( l_exp_type );
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 ExpReportLinesInfo(V_ReceiptCount).parameter_id := NULL;
381 END;
382
383 -- Get Receipt Currency
384 ExpReportLinesInfo(V_ReceiptCount).currency_code := getNext(V_line, 5);
385
386 -- Determine if Euro currencies
387 debug_info := 'Euro';
388 l_date_format := ICX_SEC.getID(ICX_SEC.PV_DATE_FORMAT);
389 l_date := nvl(to_date(ExpReportLinesInfo(V_ReceiptCount).end_date,
390 l_date_format),
391 to_date(ExpReportLinesInfo(V_ReceiptCount).start_date,
392 l_date_format));
393 -- is_fixed_rate does not handle null date well.
394 IF ((l_date is NOT NULL) AND (ExpReportLinesInfo(V_ReceiptCount).currency_code <> 'OTHER')) THEN
395 l_is_fixed_rate := GL_CURRENCY_API.is_fixed_rate(
396 ExpReportLinesInfo(V_ReceiptCount).currency_code, ExpReportHeaderInfo.reimbursement_currency_code, l_date);
397 IF (l_is_fixed_rate = 'Y') THEN
398 l_euro_rate := GL_CURRENCY_API.get_rate(
399 ExpReportLinesInfo(V_ReceiptCount).currency_code,
400 ExpReportHeaderInfo.reimbursement_currency_code,
401 l_date,
402 null);
403 debug_info := 'Rate = ' || to_char(l_euro_rate);
404 -- Determine Inverse Rate Profile Option
405 FND_PROFILE.GET('DISPLAY_INVERSE_RATE',l_inverse_rate_profile);
406 IF (nvl(l_inverse_rate_profile,'N') = 'Y' AND nvl(l_euro_rate,0) <> 0) THEN
407 l_euro_rate := 1/l_euro_rate;
408 END IF;
409 ExpReportLinesInfo(V_ReceiptCount).rate := substrb(to_char(l_euro_rate), 1, 25);
410 END IF;
411 END IF;
412
413 -- Get ItemizeID
414 l_temp := getNext(V_line, 5);
415 if (l_temp = 'null') then ExpReportLinesInfo(V_ReceiptCount).itemizeId := null;
416 else ExpReportLinesInfo(V_ReceiptCount).itemizeId := l_temp;
417 end if;
418
419 -- Get CCTrxnID
420 l_temp := getNext(V_line, 5);
421 if (l_temp = 'null') then ExpReportLinesInfo(V_ReceiptCount).cCardTrxnId := null;
422 else ExpReportLinesInfo(V_ReceiptCount).cCardTrxnId := l_temp;
423 end if;
424
425 -- Get Merchant
426 ExpReportLinesInfo(V_ReceiptCount).merchant := getNext(V_line, 5);
427
428 -- Get MerchantDoc
429 ExpReportLinesInfo(V_ReceiptCount).merchantDoc := getNext(V_line, 5);
430
431 -- Get TaxReference
432 ExpReportLinesInfo(V_ReceiptCount).taxReference := getNext(V_line, 5);
433
434 -- Get TaxRegNumber
435 ExpReportLinesInfo(V_ReceiptCount).taxRegNumber := getNext(V_line, 5);
436
437 -- Get TaxPayerID
438 ExpReportLinesInfo(V_ReceiptCount).taxPayerId := getNext(V_line, 5);
439
440 -- Get SupplyCountry
441 ExpReportLinesInfo(V_ReceiptCount).supplyCountry := getNext(V_line, 5);
442
443 -- Get TaxCodeID
444 ExpReportLinesInfo(V_ReceiptCount).taxId := getNext(V_line, 5);
445
446 -- Get VatCode
447 IF (ExpReportLinesInfo(V_ReceiptCount).taxId is not null) THEN
448 IF ( NOT AP_WEB_DB_AP_INT_PKG.GetVatCode(ExpReportLinesInfo(V_ReceiptCount).taxId, ExpReportLinesInfo(V_ReceiptCount).tax_code) ) THEN
449 null;
450 END IF;
451 END IF;
452
453
454 -- Get OverrideFlag
455 ExpReportLinesInfo(V_ReceiptCount).taxOverrideFlag := getNext(V_line, 5);
456
457 -- Get AmtIncludesTax
458 ExpReportLinesInfo(V_ReceiptCount).amount_includes_tax := getNext(V_line, 5);
459
460 -- Get TaxCode
461 l_tax_code := getNext(V_line, 5);
462 IF nvl(P_IsSessionTaxEnabled,'N') <> 'Y' THEN
463 ExpReportLinesInfo(V_ReceiptCount).tax_code := l_tax_code;
464 END IF;
465
466 -- Get flexfield info
467 Custom1_Array(V_ReceiptCount).value := NULL;
468 IF (ExpReportHeaderInfo.number_max_flexfield >= 1) THEN
469 Custom1_Array(V_ReceiptCount).value := getNext(V_line, 5);
470 END IF;
471
472 Custom2_Array(V_ReceiptCount).value := NULL;
473 IF (ExpReportHeaderInfo.number_max_flexfield >= 2) THEN
474 Custom2_Array(V_ReceiptCount).value := getNext(V_line, 5);
475 END IF;
476
477 Custom3_Array(V_ReceiptCount).value := NULL;
478 IF (ExpReportHeaderInfo.number_max_flexfield >= 3) THEN
479 Custom3_Array(V_ReceiptCount).value := getNext(V_line, 5);
480 END IF;
481
482 Custom4_Array(V_ReceiptCount).value := NULL;
483 IF (ExpReportHeaderInfo.number_max_flexfield >= 4) THEN
484 Custom4_Array(V_ReceiptCount).value := getNext(V_line, 5);
485 END IF;
486
487 Custom5_Array(V_ReceiptCount).value := NULL;
488 IF (ExpReportHeaderInfo.number_max_flexfield >= 5) THEN
489 Custom5_Array(V_ReceiptCount).value := getNext(V_line, 5);
490 END IF;
491
492 Custom6_Array(V_ReceiptCount).value := NULL;
493 IF (ExpReportHeaderInfo.number_max_flexfield >= 6) THEN
494 Custom6_Array(V_ReceiptCount).value := getNext(V_line, 5);
495 END IF;
496
497 Custom7_Array(V_ReceiptCount).value := NULL;
498 IF (ExpReportHeaderInfo.number_max_flexfield >= 7) THEN
499 Custom7_Array(V_ReceiptCount).value := getNext(V_line, 5);
500 END IF;
501
502 Custom8_Array(V_ReceiptCount).value := NULL;
503 IF (ExpReportHeaderInfo.number_max_flexfield >= 8) THEN
504 Custom8_Array(V_ReceiptCount).value := getNext(V_line, 5);
505 END IF;
506
507 Custom9_Array(V_ReceiptCount).value := NULL;
508 IF (ExpReportHeaderInfo.number_max_flexfield >= 9) THEN
509 Custom9_Array(V_ReceiptCount).value := getNext(V_line, 5);
510 END IF;
511
512 Custom10_Array(V_ReceiptCount).value := NULL;
513 IF (ExpReportHeaderInfo.number_max_flexfield >= 10) THEN
514 Custom10_Array(V_ReceiptCount).value := getNext(V_line, 5);
515 END IF;
516
517 Custom11_Array(V_ReceiptCount).value := NULL;
518 IF (ExpReportHeaderInfo.number_max_flexfield >= 11) THEN
519 Custom11_Array(V_ReceiptCount).value := getNext(V_line, 5);
520 END IF;
521
522 Custom12_Array(V_ReceiptCount).value := NULL;
523 IF (ExpReportHeaderInfo.number_max_flexfield >= 12) THEN
524 Custom12_Array(V_ReceiptCount).value := getNext(V_line, 5);
525 END IF;
526
527 Custom13_Array(V_ReceiptCount).value := NULL;
528 IF (ExpReportHeaderInfo.number_max_flexfield >= 13) THEN
529 Custom13_Array(V_ReceiptCount).value := getNext(V_line, 5);
530 END IF;
531
532 Custom14_Array(V_ReceiptCount).value := NULL;
533 IF (ExpReportHeaderInfo.number_max_flexfield >= 14) THEN
534 Custom14_Array(V_ReceiptCount).value := getNext(V_line, 5);
535 END IF;
536
537 Custom15_Array(V_ReceiptCount).value := NULL;
538 IF (ExpReportHeaderInfo.number_max_flexfield >= 15) THEN
539 Custom15_Array(V_ReceiptCount).value := getNext(V_line, 5);
540 END IF;
541
542 -- Project Pseudo flexfields
543 V_PAProjectNumber := NULL;
544 V_PATaskNumber := NULL;
545 IF P_IsSessionProjectEnabled = 'Y' THEN
546 -- Project Number
547 debug_info := 'position 1';
548 V_PAProjectNumber := getNext(V_line, 5);
549 ExpReportLinesInfo(V_ReceiptCount).project_number := V_PAProjectNumber;
550
551 ExpReportLinesInfo(V_ReceiptCount).project_id := NULL;
552
553
554 -- Task Number
555 V_PATaskNumber := getNext(V_line, 5);
556
557 ExpReportLinesInfo(V_ReceiptCount).task_number := V_PATaskNumber;
558
559 ExpReportLinesInfo(V_ReceiptCount).task_id := NULL;
560
561 END IF;
562
563 -- Get whether receipt was visited
564 ExpReportLinesInfo(V_ReceiptCount).validation_required := getNext(V_line, 5);
565
566 -- Get Error Table
567 V_ErrorMessage := getNextLong(V_line, 5);
568
569 V_ErrorField := getNext(V_line, 5);
570 -- Convert the error message strings to MessageArray
571 receipt_error_Array(V_ReceiptCount).error_text := V_ErrorMessage;
572 receipt_error_Array(V_ReceiptCount).error_fields := V_ErrorField;
573
574 -- Get Warning Table
575 V_WarningMessage := getNextLong(V_line, 5);
576 V_WarningField := getNext(V_line, 11);
577
578 -- Convert the error message strings to MessageArray
579 receipt_error_Array(V_ReceiptCount).warning_text := V_WarningMessage;
580 receipt_error_Array(V_ReceiptCount).warning_fields := V_WarningField;
581
582
583 -- Fill in other project information
584
585 AP_WEB_PROJECT_PKG.DerivePAInfoFromUserInput(
586 P_IsSessionProjectEnabled,
587 V_PAProjectNumber,
588 V_PAProjectID,
589 l_PAProjectName,
590 V_PATaskNumber,
591 V_PATaskID,
592 l_PATaskName,
593 V_PAExpenditureType,
594 ExpReportLinesInfo(V_ReceiptCount).parameter_id);
595
596 if (V_PAExpenditureType IS NOT NULL) then
597 ExpReportLinesInfo(V_ReceiptCount).expenditure_type := V_PAExpenditureType;
598 end if;
599
600 if (V_PAProjectID IS NOT NULL) then ExpReportLinesInfo(V_ReceiptCount).project_id := V_PAProjectID;
601 end if;
602
603 if (V_PATaskID IS NOT NULL) then ExpReportLinesInfo(V_ReceiptCount).task_id := V_PATaskID;
604 end if;
605
606 END LOOP;
607
608 -- Set the new header data type
609 ExpReportHeaderInfo.receipt_count := V_ReceiptCount;
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 BEGIN
614 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
615 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
616 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
617 current_calling_sequence);
618 FND_MESSAGE.SET_TOKEN('PARAMETERS',
619 'V_line=' || V_line
620 );
621 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
622 APP_EXCEPTION.RAISE_EXCEPTION;
623 END;
624
625 END String2PLSQL_Receipts;
626
627
628 PROCEDURE String2PLSQL(P_IsSessionTaxEnabled IN VARCHAR2,
629 P_IsSessionProjectEnabled IN VARCHAR2,
630 receipt_error_Array in out nocopy AP_WEB_UTILITIES_PKG.receipt_error_stack,
631 ParseThis in long,
632 ExpReportHeaderInfo out nocopy AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
633 ExpReportLinesInfo out nocopy AP_WEB_DFLEX_PKG.ExpReportLines_A,
634 Custom1_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
635 Custom2_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
636 Custom3_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
637 Custom4_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
638 Custom5_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
639 Custom6_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
640 Custom7_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
641 Custom8_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
642 Custom9_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
643 Custom10_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
644 Custom11_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
645 Custom12_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
646 Custom13_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
647 Custom14_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A,
648 Custom15_Array in out nocopy AP_WEB_DFLEX_PKG.CustomFields_A)
649 IS
650 position number;
651 V_line long := ParseThis;
652 debug_info varchar2(200);
653 current_calling_sequence varchar2(100) := 'String2PLSQL';
654 V_IsSessionProjectEnabled varchar2(1);
655
656 BEGIN
657
658 -- Pull Header-level values
659 --
660 debug_info := 'Header level';
661 String2PLSQL_Header(V_line,
662 P_IsSessionProjectEnabled,
663 ExpReportHeaderInfo);
664
665 AP_WEB_PROJECT_PKG.IsSessionProjectEnabled(ExpReportHeaderInfo.employee_id,
666 ICX_SEC.getID(icx_sec.PV_WEB_USER_ID),
667 V_IsSessionProjectEnabled);
668
669 --
670 -- Get the receipts
671 --
672 String2PLSQL_Receipts(P_IsSessionTaxEnabled,
673 V_IsSessionProjectEnabled,
674 receipt_error_Array,
675 V_line,
676 ExpReportHeaderInfo,
677 ExpReportLinesInfo,
678 Custom1_Array,
679 Custom2_Array,
680 Custom3_Array,
681 Custom4_Array,
682 Custom5_Array,
683 Custom6_Array,
684 Custom7_Array,
685 Custom8_Array,
686 Custom9_Array,
687 Custom10_Array,
688 Custom11_Array,
689 Custom12_Array,
690 Custom13_Array,
691 Custom14_Array,
692 Custom15_Array);
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 BEGIN
697 IF (SQLCODE <> -20001) THEN
698 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
699 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
700 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
701 current_calling_sequence);
702 FND_MESSAGE.SET_TOKEN('PARAMETERS',
703 'V_line=' || V_line
704 );
705 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
706 END IF;
707 APP_EXCEPTION.RAISE_EXCEPTION;
708 END;
709
710 END String2PLSQL;
711
712
713 PROCEDURE MapCustomArrayToColumn(
714 P_Index IN NUMBER,
715 ExpReportHeaderInfo IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
716 ExpReportLinesInfo IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
717 Custom1_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
718 Custom2_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
719 Custom3_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
720 Custom4_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
721 Custom5_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
722 Custom6_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
723 Custom7_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
724 Custom8_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
725 Custom9_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
726 Custom10_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
727 Custom11_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
728 Custom12_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
729 Custom13_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
730 Custom14_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
731 Custom15_Array IN AP_WEB_DFLEX_PKG.CustomFields_A,
732 AttributeCol_Array IN OUT NOCOPY BigString_Array)
733 IS
734
735 V_CurrentCallingSequence VARCHAR2(2000);
736 V_DebugInfo VARCHAR2(2000);
737
738 PROCEDURE MapFlexFieldValueToColumn(
739 P_Value IN VARCHAR2,
740 P_ColumnMapping IN VARCHAR2,
741 AttributeCol_Array IN OUT NOCOPY AP_WEB_PARENT_PKG.BigString_Array)
742 IS
743 BEGIN
744
745 IF P_ColumnMapping = 'ATTRIBUTE1' THEN
746 AttributeCol_Array(1) := P_Value;
747 END IF;
748
749 IF P_ColumnMapping = 'ATTRIBUTE2' THEN
750 AttributeCol_Array(2) := P_Value;
751 END IF;
752
753 IF P_ColumnMapping = 'ATTRIBUTE3' THEN
754 AttributeCol_Array(3) := P_Value;
755 END IF;
756
757 IF P_ColumnMapping = 'ATTRIBUTE4' THEN
758 AttributeCol_Array(4) := P_Value;
759 END IF;
760
761 IF P_ColumnMapping = 'ATTRIBUTE5' THEN
762 AttributeCol_Array(5) := P_Value;
763 END IF;
764
765 IF P_ColumnMapping = 'ATTRIBUTE6' THEN
766 AttributeCol_Array(6) := P_Value;
767 END IF;
768
769 IF P_ColumnMapping = 'ATTRIBUTE7' THEN
770 AttributeCol_Array(7) := P_Value;
771 END IF;
772
773 IF P_ColumnMapping = 'ATTRIBUTE8' THEN
774 AttributeCol_Array(8) := P_Value;
775 END IF;
776
777 IF P_ColumnMapping = 'ATTRIBUTE9' THEN
778 AttributeCol_Array(9) := P_Value;
779 END IF;
780
781 IF P_ColumnMapping = 'ATTRIBUTE10' THEN
782 AttributeCol_Array(10) := P_Value;
783 END IF;
784
785 IF P_ColumnMapping = 'ATTRIBUTE11' THEN
786 AttributeCol_Array(11) := P_Value;
787 END IF;
788
789 IF P_ColumnMapping = 'ATTRIBUTE12' THEN
790 AttributeCol_Array(12) := P_Value;
791 END IF;
792
793 IF P_ColumnMapping = 'ATTRIBUTE13' THEN
794 AttributeCol_Array(13) := P_Value;
795 END IF;
796
797 IF P_ColumnMapping = 'ATTRIBUTE14' THEN
798 AttributeCol_Array(14) := P_Value;
799 END IF;
800
801 IF P_ColumnMapping = 'ATTRIBUTE15' THEN
802 AttributeCol_Array(15) := P_Value;
803 END IF;
804
805 END MapFlexFieldValueToColumn;
806
807 BEGIN
808
809 V_CurrentCallingSequence := 'AP_WEB_SUBMIT_PKG.MapCustomArrayToColumn';
810
811 -- Initialize attribute column values
812 FOR I IN 1..15 LOOP
813 AttributeCol_Array(I) := NULL;
814 END LOOP;
815
816 -- Map values for index-th receipt
817 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom1_Array(P_Index)) THEN
818
819 MapFlexFieldValueToColumn(Custom1_Array(P_Index).value,
820 Custom1_Array(P_Index).column_mapping,
821 AttributeCol_Array);
822
823 END IF;
824
825 -- Map values for index-th receipt
826 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom2_Array(P_Index)) THEN
827
828 MapFlexFieldValueToColumn(Custom2_Array(P_Index).value,
829 Custom2_Array(P_Index).column_mapping,
830 AttributeCol_Array);
831 END IF;
832
833 -- Map values for index-th receipt
834 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom3_Array(P_Index)) THEN
835
836 MapFlexFieldValueToColumn(Custom3_Array(P_Index).value,
837 Custom3_Array(P_Index).column_mapping,
838 AttributeCol_Array);
839
840 END IF;
841
842 -- Map values for index-th receipt
843 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom4_Array(P_Index)) THEN
844
845 MapFlexFieldValueToColumn(Custom4_Array(P_Index).value,
846 Custom4_Array(P_Index).column_mapping,
847 AttributeCol_Array);
848
849 END IF;
850
851 -- Map values for index-th receipt
852 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom5_Array(P_Index)) THEN
853
854 MapFlexFieldValueToColumn(Custom5_Array(P_Index).value,
855 Custom5_Array(P_Index).column_mapping,
856 AttributeCol_Array);
857 END IF;
858
859 -- Map values for index-th receipt
860 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom6_Array(P_Index)) THEN
861
862 MapFlexFieldValueToColumn(Custom6_Array(P_Index).value,
863 Custom6_Array(P_Index).column_mapping,
864 AttributeCol_Array);
865 END IF;
866
867 -- Map values for index-th receipt
868 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom7_Array(P_Index)) THEN
869
870 MapFlexFieldValueToColumn(Custom7_Array(P_Index).value,
871 Custom7_Array(P_Index).column_mapping,
872 AttributeCol_Array);
873 END IF;
874
875 -- Map values for index-th receipt
876 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom8_Array(P_Index)) THEN
877
878 MapFlexFieldValueToColumn(Custom8_Array(P_Index).value,
879 Custom8_Array(P_Index).column_mapping,
880 AttributeCol_Array);
881 END IF;
882
883 -- Map values for index-th receipt
884 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom9_Array(P_Index)) THEN
885
886 MapFlexFieldValueToColumn(Custom9_Array(P_Index).value,
887 Custom9_Array(P_Index).column_mapping,
888 AttributeCol_Array);
889 END IF;
890
891 -- Map values for index-th receipt
892 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom10_Array(P_Index)) THEN
893
894 MapFlexFieldValueToColumn(Custom10_Array(P_Index).value,
895 Custom10_Array(P_Index).column_mapping,
896 AttributeCol_Array);
897 END IF;
898
899 -- Map values for index-th receipt
900 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom11_Array(P_Index)) THEN
901
902 MapFlexFieldValueToColumn(Custom11_Array(P_Index).value,
903 Custom11_Array(P_Index).column_mapping,
904 AttributeCol_Array);
905 END IF;
906
907 -- Map values for index-th receipt
908 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom12_Array(P_Index)) THEN
909
910 MapFlexFieldValueToColumn(Custom12_Array(P_Index).value,
911 Custom12_Array(P_Index).column_mapping,
912 AttributeCol_Array);
913 END IF;
914
915 -- Map values for index-th receipt
916 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom13_Array(P_Index)) THEN
917
918 MapFlexFieldValueToColumn(Custom13_Array(P_Index).value,
919 Custom13_Array(P_Index).column_mapping,
920 AttributeCol_Array);
921 END IF;
922
923 -- Map values for index-th receipt
924 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom14_Array(P_Index)) THEN
925
926 MapFlexFieldValueToColumn(Custom14_Array(P_Index).value,
927 Custom14_Array(P_Index).column_mapping,
928 AttributeCol_Array);
929 END IF;
930
931 -- Map values for index-th receipt
932 IF AP_WEB_DFLEX_PKG.IsFlexFieldUsed(Custom15_Array(P_Index)) THEN
933
934 MapFlexFieldValueToColumn(Custom15_Array(P_Index).value,
935 Custom15_Array(P_Index).column_mapping,
936 AttributeCol_Array);
937 END IF;
938 EXCEPTION
939 WHEN OTHERS THEN
940 BEGIN
941 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
942 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
943 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', V_CurrentCallingSequence);
944 FND_MESSAGE.SET_TOKEN('PARAMETERS','');
945 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',V_DebugInfo);
946 APP_EXCEPTION.Raise_Exception;
947 END;
948
949 END MapCustomArrayToColumn;
950
951
952 END AP_WEB_PARENT_PKG;