1 PACKAGE BODY AP_WEB_CUST_DFLEX_PKG AS
2 /* $Header: apwdfcfb.pls 120.10.12010000.3 2008/09/15 09:15:56 rveliche ship $ */
3
4
5 ----------------------------------------------------------------------------
6 ----------------------------------------------------------------------------
7 -- CUSTOMPOPULATEPOPLIST: This procedure provides a way to populate
8 -- the values for custom fields rendered as poplists. This is pertinent
9 -- only for segments which have value sets which have independent and table
10 -- validation types. If longlist enabled is checked for the segment, the
11 -- segment is rendered as a text item, and this routine is not called.
12 --
13 -- For segments with the independent validation type:
14 -- (1) If CustomPopulatePoplist returns values, those values are used
15 -- in the poplist.
16 -- (2) Otherwise, the values defined for the independent value set is
17 -- is used.
18 -- (3) Otherwise, the segment is rendered as a text item.
19 --
20 -- For segments with table validation type:
21 -- (1) If CustomPopulatePoplist returns values, those values are used
22 -- in the poplist. Since this release does not execute the query
23 -- statement, you will have to reenter the query here.
24 -- (2) Otherwise, the segment is rendered as a text item.
25 --
26 -- PARAMETERS:
27 --
28 -- P_ExpReportHeaderInfo - Contains header information about the
29 -- expense report.
30 -- P_ExpenseTypeName - Expense type string, corresponds to the Context
31 -- Field Value Name field on the Descriptive
32 -- Flexfield Segments 10SC form.
33 -- P_CustomFieldName - Custom field name, corresponds to the name of the
34 -- segment on the Segment Summary 10SC form.
35 -- P_NumOfPoplistElem - Number of elements returned in the P_PoplistArray
36 -- P_PoplistArray - Array containing the values to be put into the poplist.
37 -- For each element, specify an InternalValue which will
38 -- actually be validated and saved, and a DisplayText
39 -- which will be displayed in the poplist.
40 -- Array is 1-based (array index should should start with 1)
41 --
42 ----------------------------------------------------------------------------
43 ----------------------------------------------------------------------------
44 PROCEDURE CustomPopulatePoplist(
45 P_ExpenseTypeName IN VARCHAR2,
46 P_CustomFieldName IN VARCHAR2,
47 P_NumOfPoplistElem OUT NOCOPY NUMBER,
48 P_PoplistArray OUT NOCOPY AP_WEB_DFLEX_PKG.PoplistValues_A)
49 ------------------------------------------------------------------------
50 IS
51 V_CurrentCallingSequence VARCHAR2(240);
52 V_DebugInfo VARCHAR2(240);
53 BEGIN
54
55 V_CurrentCallingSequence := 'AP_WEB_CUST_DFLEX_PKG.CustomPopulatePoplist';
56
57 V_DebugInfo := 'No poplist values provided.';
58 P_NumOfPoplistElem := 0;
59
60 --
61 -- Insert logic to populate poplist elements here
62 -- Example:
63 --
64 -- IF (UPPER(P_ExpenseTypeName) = 'CAR RENTAL') THEN
65 --
66 -- IF (UPPER(P_CustomFieldName) = 'RENTAL AGENCY') THEN
67 --
68 -- P_PoplistArray(1).InternalValue := 'Hertz';
69 -- P_PoplistArray(1).DisplayText := 'Hertz Custom Test';
70 --
71 -- P_PoplistArray(2).InternalValue := 'Avis';
72 -- P_PoplistArray(2).DisplayText := 'Avis Custom Test';
73 --
74 -- P_NumOfPoplistElem := 2;
75 --
76 -- END IF;
77 --
78 -- ELSIF (UPPER(P_ExpenseTypeName) = 'GLOBAL DATA ELEMENTS') THEN
79 --
80 -- IF (UPPER(P_CustomFieldName) = 'TEST PROMPT') THEN
81 --
82 -- ...
83 --
84 -- END IF;
85 --
86 -- END IF;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
91 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
92 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
93 V_CurrentCallingSequence);
94 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',V_DebugInfo);
95 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
96 END CustomPopulatePoplist;
97
98 ----------------------------------------------------------------------------
99 ----------------------------------------------------------------------------
100 -- CUSTOMPOPULATEDEFAULT: This procedure provides a way to
101 -- implement complex defaulting logic for the descriptive flexfield
102 -- custom fields you have defined other than what you have provided
103 -- for the descriptive flexfield definition. If no default is returned
104 -- by this routine, then the default value taken from the default
105 -- value field in the Segment 10SC form.
106 --
107 -- It is not possible to customize segments in the "Global Data Elements",
108 -- only the ones in the custom context values (expense types).
109 --
110 -- For checkboxes, specify 'Y' for check and 'N' to leave the box
111 -- unchecked.
112 --
113 -- PARAMETERS:
114 --
115 -- P_ExpReportHeaderInfo - Contains header information about the
116 -- expense report.
117 -- P_ExpenseTypeName - Expense type string, corresponds to the Context
118 -- Field Value Name field on the Descriptive
119 -- Flexfield Segments 10SC form.
120 -- P_CustomFieldName - Custom field name, corresponds to the name of the
121 -- segment on the Segment Summary 10SC form.
122 -- P_DefaultValue - Custom default value.
123 --
124 ----------------------------------------------------------------------------
125 ----------------------------------------------------------------------------
126 PROCEDURE CustomPopulateDefault(
127 P_ExpenseTypeName IN VARCHAR2,
128 P_CustomFieldName IN VARCHAR2,
129 P_DefaultValue OUT NOCOPY VARCHAR2)
130 ------------------------------------------------------------------
131 IS
132 V_CurrentCallingSequence VARCHAR2(240);
133 V_DebugInfo VARCHAR2(240);
134 BEGIN
135
136 V_CurrentCallingSequence := 'AP_WEB_CUST_DFLEX_PKG.CustomPopulateDefault';
137
138 V_DebugInfo := 'No default values provided.';
139 P_DefaultValue := '';
140
141 --
142 -- Insert logic to calculate the default
143 -- Example:
144 --
145 -- IF (UPPER(P_ExpenseTypeName) = 'GLOBAL DATA ELEMENTS') THEN
146 --
147 -- IF (UPPER(P_CustomFieldName) = 'TEST PROMPT') THEN
148 -- P_DefaultValue := 'Custom Test';
149 -- END IF;
150 --
151 -- ELSIF (UPPER(P_ExpenseTypeName) = 'AIRFARE') THEN
152 --
153 -- IF (UPPER(P_CustomFieldName) = 'AIRLINE') THEN
154 -- P_DefaultValue := 'United';
155 -- END IF;
156 --
157 -- ELSIF (UPPER(P_ExpenseTypeName) = 'HOTEL') THEN
158 --
159 -- IF (UPPER(P_CustomFieldName) = 'NEGOTIATED RATE USED?') THEN
160 --
161 -- P_DefaultValue := 'Y';
162 -- END IF;
163 --
164 -- ELSIF (UPPER(P_ExpenseTypeName) = 'CAR RENTAL') THEN
165 --
166 -- IF (UPPER(P_CustomFieldName) = 'RENTAL AGENCY') THEN
167 --
168 -- P_DefaultValue := 'Hertz';
169 -- END IF;
170 --
171 -- END IF;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
176 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
177 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
178 V_CurrentCallingSequence);
179 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',V_DebugInfo);
180 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
181 END CustomPopulateDefault;
182
183
184 -----------------------------------------------------------------------------
185 -----------------------------------------------------------------------------
186 -- CUSTOMVALIDATEDFLEXVALUES: This procedure provides you a way to
187 -- implement any special custom validation logic besides the basic
188 -- descriptive flexfield value set validation you wish to perform
189 -- on the descriptive flexfield custom flexfields you have defined.
190 -- Both individual custom field value validation logic as well as
191 -- cross custom field value validation, i.e. custom fields that are
192 -- context-sensitive to values of other custom fields, belong here.
193 -- We have also provided a function GetCustomFieldValue for which you
194 -- provide the name of the custom field and the custom_fields_array and
195 -- the custom field value is returned. This function makes accessing
196 -- the values of custom fields other than the current custom field being
197 -- processed possible. The following is that function spec:
198 --
199 -- FUNCTION GetCustomFieldValue(p_prompt IN VARCHAR2,
200 -- p_custom_fields_array IN CustomFields_A) RETURN VARCHAR2);
201 --
202 --
203 --
204 -- PARAMETERS:
205 --
206 -- p_exp_header_info - record that contains expense report header
207 -- information. The record contains the following
208 -- components:
209 -- report_header_id NUMBER,
210 -- employee_id VARCHAR2(25),
211 -- cost_center VARCHAR2(30),
212 -- template_id VARCHAR2(25),
213 -- template_name VARCHAR2(100),
214 -- purpose VARCHAR2(240),
215 -- summary_start_date VARCHAR2(25),
216 -- summary_end_date VARCHAR2(25),
217 -- summary_xtype VARCHAR2(25),
218 -- receipt_index NUMBER,
219 -- last_receipt_date VARCHAR2(25),
220 -- last_update_date VARCHAR2(25),
221 -- receipt_count VARCHAR2(25),
222 -- transaction_currency_type VARCHAR2(25),
223 -- reimbursement_currency_code VARCHAR2(25),
224 -- reimbursement_currency_name VARCHAR2(80),
225 -- multi_currency_flag VARCHAR2(1),
226 -- inverse_rate_flag VARCHAR2(1),
227 -- override_approver_id VARCHAR2(25),
228 -- override_approver_name VARCHAR2(80)
229 -- expenditure_organization_id NUMBER(15),
230 -- number_max_flexfield NUMBER,
231 -- amt_due_employee NUMBER,
232 -- amt_due_ccCompany NUMBER);
233 --
234 -- Usage: p_exp_header_info.multi_currency_flag
235 --
236 -- p_exp_line_info - record that contains expense report line
237 -- information. The record contains the following
238 -- components:
239 -- receipt_index NUMBER, -- index of receipt starting from 1
240 -- start_date DATE,
241 -- end_date DATE,
242 -- days VARCHAR2(25),
243 -- daily_amount VARCHAR2(25),
244 -- receipt_amount VARCHAR2(50),
245 -- rate VARCHAR2(25),
246 -- amount VARCHAR2(50),
247 -- parameter_id VARCHAR2(25),
248 -- expense_type VARCHAR2(80),
249 -- currency_code VARCHAR2(25),
250 -- cCardTrxnId NUMBER,
251 -- category VARCHAR2(30),
252 -- group_value VARCHAR2(80),
253 -- justification VARCHAR2(240),
254 -- receipt_missing_flag VARCHAR2(1),
255 -- validation_required VARCHAR2(1),
256 -- calculate_flag VARCHAR2(1),
257 -- calculated_amount VARCHAR2(50),
258 -- copy_calc_amt_into_receipt_amt VARCHAR2(1)
259 -- amount_includes_tax VARCHAR2(1),
260 -- tax_code VARCHAR2(15),
261 -- taxOverrideFlag VARCHAR2(1),
262 -- taxId VARCHAR2(15),
263 -- project_id VARCHAR2(15),
264 -- project_number VARCHAR2(25),
265 -- task_id VARCHAR2(15),
266 -- task_number VARCHAR2(25),
267 -- expenditure_type VARCHAR2(30)
268 --
269 -- Usage: p_exp_line_info.expense_type
270 --
271 -- p_custom_fields_array - array of custom fields record required by
272 -- GetCustomFieldValue function as a parameter
273 -- Needed for inter-dependent custom field
274 -- validation
275 --
276 -- p_custom_field_record - record containing custom field information
277 -- of the custom field being validated.
278 --
279 -- p_validation_level - validation level at which this api is being
280 -- called.
281 --
282 -- p_resulting_message - available for you to assign any validation error
286 -- if p_resulting_message is an error message,
283 -- or warning message to be displayed to the user.
284 --
285 -- p_message_type - (used in the future, not currently supported)
287 -- then set p_message_type = 'ERROR'
288 -- if p_resulting_message is a warning message,
289 -- then set p_message_type = 'WARNING'
290 -- You can refer to the following predefined constants
291 -- in your code:
292 -- AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeNone
293 -- AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError
294 -- AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeWarning
295 --
296 ---------------------------------------------------------------------------
297 ---------------------------------------------------------------------------
298 PROCEDURE CustomValidateDFlexValues(
299 p_exp_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
300 p_exp_line_info IN AP_WEB_DFLEX_PKG.ExpReportLineRec,
301 p_custom_fields_array IN AP_WEB_DFLEX_PKG.CustomFields_A,
302 p_custom_field_record IN AP_WEB_DFLEX_PKG.CustomFieldRec,
303 p_validation_level IN VARCHAR2,
304 p_result_message IN OUT NOCOPY VARCHAR2,
305 p_message_type IN OUT NOCOPY VARCHAR2,
306 p_receipt_index IN BINARY_INTEGER)
307 ---------------------------------------------------------------------------
308 IS
309 l_debug_info VARCHAR2(2000);
310 l_curr_calling_sequence VARCHAR2(200) := 'CustomValidateDFlexValues';
311 BEGIN
312
313 IF (p_validation_level = 'HEADER') THEN
314
315 ------------------------------------------------------------------
316 -- Expense Report Header Level Validation Logic Goes Here
317 -- Currently not supported. Feature to be implemented later on.
318 ------------------------------------------------------------------
319 NULL;
320
321 ELSIF (p_validation_level = 'LINE') THEN
322
323 --------------------------------------------------------
324 -- Expense Report Lines Level Validation Logic Goes Here
325 --------------------------------------------------------
326 NULL;
327
328 ELSIF (p_validation_level = 'FIELD') THEN
329
330 -------------------------------------------------------------------
331 -- Expense Report Type-Related Level Validation Logic Goes Here
332 -- EXAMPLE CODE:
333 -- IF (upper(p_custom_field_record.prompt) = 'RATE PER MILE') THEN
334 --
335 -- IF (p_custom_field_record.value > 5) THEN
336 --
337 -- p_result_message := 'Rate per mile cannot exceed 5';
338 -- p_message_type := 'ERROR';
339 -- return;
340 -- END IF;
341 -- ELSE IF (upper(p_custom_field_record.prompt) = '...') THEN
342 --
343 -- ELSE IF ... THEN
344 --
345 -- END IF;
346 -------------------------------------------------------------------
347
348 NULL;
349
350 END IF;
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
355 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
356 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
357 l_curr_calling_sequence);
358 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
359 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
360 END CustomValidateDFlexValues;
361
362
363 ----------------------------------------------------------------------------
364 ----------------------------------------------------------------------------
365 -- CUSTOMCALCULATEAMOUNT: This procedure is here to implement the
366 -- calculate amount feature to provide you a mechanism to perform
367 -- custom expense line calculation based off of expense report header,
368 -- line and custom field values.
369 --
370 -- PARAMETERS:
371 --
372 -- p_exp_header_info - record that contains expense report header
373 -- information. The record contains the following
374 -- components:
375 -- employee_id VARCHAR2(25),
376 -- cost_center VARCHAR2(30),
377 -- template_id VARCHAR2(25),
378 -- template_name VARCHAR2(100),
379 -- purpose VARCHAR2(240),
380 -- last_receipt_date VARCHAR2(25),
381 -- receipt_count VARCHAR2(25),
382 -- transaction_currency_type VARCHAR2(25),
383 -- reimbursement_currency_code VARCHAR2(25),
384 -- reimbursement_currency_name VARCHAR2(80),
385 -- multi_currency_flag VARCHAR2(1),
386 -- inverse_rate_flag VARCHAR2(1),
387 -- override_approver_id VARCHAR2(25),
388 -- override_approver_name VARCHAR2(80)
389 --
390 -- Usage: p_exp_header_info.multi_currency_flag
391 --
392 -- p_exp_line_info - record that contains expense report line
393 -- information. The record contains the following
394 -- components:
395 -- start_date date,
396 -- end_date date,
397 -- days VARCHAR2(25),
398 -- daily_amount VARCHAR2(25),
399 -- receipt_amount VARCHAR2(50),
400 -- rate VARCHAR2(25),
401 -- amount VARCHAR2(50),
402 -- parameter_id VARCHAR2(25),
403 -- expense_type VARCHAR2(80),
404 -- currency_code VARCHAR2(25),
405 -- group_value VARCHAR2(80),
406 -- justification VARCHAR2(240),
407 -- receipt_missing_flag VARCHAR2(1),
411 -- copy_calc_amt_into_receipt_amt VARCHAR2(1)
408 -- validation_required VARCHAR2(1),
409 -- calculate_flag VARCHAR2(1),
410 -- calculated_amount VARCHAR2(50),
412 --
413 -- Usage: p_exp_line_info.expense_type
414 --
415 -- All the components of p_exp_line_info are reference information
416 -- except for the last 2 components:
417 --
418 -- The result of the calculated_amount needs to be assigned into
419 -- p_exp_line_info.calculated_amount
420 --
421 -- For foreign currency, you can also specify if you want the result
422 -- copied into the Receipt Amount instead of Total. Total is the
423 -- expense line amount in the reimbursement currency while
424 -- Receipt Amount is without the foreing currency conversion. If
425 -- you want to let the end-user specify the currency rate, set
426 -- p_exp_line_info.copy_calc_amt_into_receipt_amt := 'Y'.
427 --
428 -- p_custom_fields_array - array of custom fields record required by
429 -- the GetCustomFieldValue function as a parameter
430 -- Needed for inter-dependent custom field
431 -- validation
432 --
433 -- Currently we only support propogating the calculated result back
434 -- to either the 'Total' field or the 'Receipt Amount' field in the
435 -- Enter Receipts page. By default, it is the 'Total' field, but for
436 -- the case where the reimbursement currency is different from the receipt
437 -- currency, if you want to allow the user to specify the conversion
438 -- rate from receipt currency to reimbursement currency, you can
439 -- specify that the amount calculated is to be copied into the
440 -- 'Receipt Amount' field by setting:
441 -- p_exp_line_info.copy_calc_amt_into_receipt_amt := 'Y';
442 -- otherwise it will be copied into the 'Total' field. Setting this
443 -- flag only works when reimbursement currency is different from
444 -- receipt currency.
445 ----------------------------------------------------------------------------
446 ----------------------------------------------------------------------------
447 PROCEDURE CustomCalculateAmount(
448 p_exp_header_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportHeaderRec, -- epxense report header details
449 p_exp_line_info IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLineRec, -- expense report line detail
450 p_custom_fields_array IN AP_WEB_DFLEX_PKG.CustomFields_A, -- custom field details
451 -- p_addon_rates used for mileage category only
452 p_addon_rates IN OIE_ADDON_RATES_T, -- array of additional rate types
453 p_report_line_id IN NUMBER DEFAULT NULL, -- report line id
454 -- below fields are used for per diem category only
455 p_daily_breakup_id IN OIE_PDM_NUMBER_T DEFAULT NULL, -- array of unique identifer for daily breakups
456 p_start_date IN OIE_PDM_DATE_T DEFAULT NULL, -- array of start date
457 p_end_date IN OIE_PDM_DATE_T DEFAULT NULL,-- array of end date
458 p_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,-- array of amount
459 p_number_of_meals IN OIE_PDM_NUMBER_T DEFAULT NULL,-- array of number of meals
460 p_meals_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,-- array of meals amount
461 p_breakfast_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,-- array of breakfast flag
462 p_lunch_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL, -- array of lunch flag
463 p_dinner_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL, -- array of dinner flag
464 p_accommodation_amount IN OIE_PDM_NUMBER_T DEFAULT NULL, -- array of accommodation amount
465 p_accommodation_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL, -- array of accommodation flag
466 p_hotel_name IN OIE_PDM_VARCHAR_80_T DEFAULT NULL, -- array of hotel name
467 p_night_rate_Type IN OIE_PDM_VARCHAR_80_T DEFAULT NULL, -- array of night rate type
468 p_night_rate_amount IN OIE_PDM_NUMBER_T DEFAULT NULL, -- array of night rate amount
469 p_pdm_rate IN OIE_PDM_NUMBER_T DEFAULT NULL, -- array of pdm rate
470 p_rate_Type_code IN OIE_PDM_VARCHAR_80_T DEFAULT NULL, -- array of rate type code
471 p_pdm_breakup_dest_id IN OIE_PDM_NUMBER_T DEFAULT NULL, -- array of unique identified for multiple destinations
472 p_pdm_destination_id IN OIE_PDM_NUMBER_T DEFAULT NULL, -- array of locations for each breakup period
473 p_dest_start_date IN OIE_PDM_DATE_T DEFAULT NULL, -- array of start date for each location
474 p_dest_end_date IN OIE_PDM_DATE_T DEFAULT NULL,-- array of end date for each location
475 p_location_id IN OIE_PDM_NUMBER_T DEFAULT NULL, -- array of locations
476 -- bug 5358186
477 p_cust_meals_amount IN OUT NOCOPY OIE_PDM_NUMBER_T, -- array of modified meals amount
478 p_cust_accommodation_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,-- array of modified accommodation amount
479 p_cust_night_rate_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,-- array of modified night rate amount
480 p_cust_pdm_rate IN OUT NOCOPY OIE_PDM_NUMBER_T-- array of modified pdm rate
481 )
482 ---------------------------------------------------------------------------
483 IS
484 l_miles NUMBER;
485 l_rate_per_mile NUMBER;
486 l_debug_info VARCHAR2(2000);
490 l_tot_amount number := 0;
487 l_curr_calling_sequence VARCHAR2(200) := 'CustomCalculateAmount';
488 i number ;
489 l_hours number ;
491 l_amount number := 0;
492 l_date1 date := null;
493 l_date2 date := null;
494 -- l_addon_array OIE_ADDON_RATES_T;
495
496 BEGIN
497 ------------------------------------------------------------------------
498 l_debug_info := 'Expense type ='|| p_exp_line_info.expense_type;
499 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_CUST_DFLEX_PKG', l_debug_info);
500 ------------------------------------------------------------------------
501 ----------------------------------------------------------------------------
502 -- Example code: Mileage
503 --
504 -- IF (upper(p_exp_line_info.expense_type) = 'MILEAGE') THEN
505 --
506 -- l_miles := AP_WEB_DFLEX_PKG.GetCustomFieldValue('MILES', p_custom_fields_array);
507 -- l_rate_per_mile := AP_WEB_DFLEX_PKG.GetCustomFieldValue('RATE PER MILE', p_custom_fields_array);
508 -- p_exp_line_info.calculated_amount := l_miles * l_rate_per_mile;
509 -- p_exp_line_info.copy_calc_amt_into_receipt_amt := 'Y';
510 --
511 -- ELSE IF (upper(p_exp_line_info.expense_type) = 'PER DIEM') THEN
512 --
513 -- NULL;
514 --
515 -- ELSE IF ...
516 --
517 -- END IF;
518 ----------------------------------------------------------------------------
519 IF (upper(p_exp_line_info.expense_type) = 'MILEAGE') THEN
520 NULL;
521 END IF;
522
523 /* Below sample code is part of a white paper which is to be published on client extension.*/
524 /*
525 -- sample code for expense type 'Mileage Client Extension Test'
526 -- to limit additional reimbursement amount if more than 5 passengers
527 IF (upper(p_exp_line_info.expense_type) = 'MILEAGE CLIENT EXTENSION') THEN
528 IF (p_exp_line_info.numberPassengers > 5) THEN
529 -- subtract the additional passenger rates from total amount
530 p_exp_line_info.calculated_amount :=
531 round(p_exp_line_info.mileageRate *p_exp_line_info.tripDistance
532 - (p_exp_line_info.numberPassengers - 5)*
533 p_exp_line_info.passengerRateUsed *p_exp_line_info.tripDistance, 2) ;
534 p_exp_line_info.copy_calc_amt_into_receipt_amt := 'Y';
535 END IF;
536 END IF;
537
538 -- change the expense type as applicable
539 IF (upper(p_exp_line_info.expense_type) = 'PERDIEM CLIENT EXTENSION') THEN
540 i := p_end_date.count;
541 -- if it is the employee hasn't traveled 3 hours within 16:00 and 07:00, reset the amount to zero.
542 FOR j IN 1..i LOOP
543 l_amount := p_amount(j);
544 l_hours := (p_end_date(j) - p_start_date(j)) *24;
545 IF ((l_hours > 6) AND (l_hours <= 8)) THEN
546 l_date1 := to_date(to_char(trunc(p_start_date(j)), 'YY-MON-DD')||':16:00', 'YY-MON-DD:HH24:MI') ;
547 l_date2 := to_date(to_char(trunc(p_start_date(j)+1), 'YY-MON-DD')||':07:00', 'YY-MON-DD:HH24:MI') ;
548 IF (p_start_date(j) < to_date(to_char(trunc(p_start_date(j)), 'YY-MON-DD')||':07:00', 'YY-MON-DD:HH24:MI')) THEN
549 l_date1 := to_date(to_char(trunc(p_start_date(j)-1), 'YY-MON-DD')||':16:00', 'YY-MON-DD:HH24:MI');
550 l_date2 := to_date(to_char(trunc(p_start_date(j)), 'YY-MON-DD')||':07:00', 'YY-MON-DD:HH24:MI');
551 END IF;
552 -- if l_date1 >= l_start_date then l_end_date - l_date1 < 3 then set amount to zero
553 -- else l_end_date - l_start_date < 3 then amount is set to zero
554 IF (l_date1 >= p_start_date(j)) THEN
555 IF (round(to_char(p_end_date(j) - l_date1)*24) < 3) THEN
556 l_amount := 0;
557 p_cust_pdm_rate(j) := 0;
558 p_cust_meals_amount(j) := 0;
559 p_cust_accommodation_amount(j) := 0;
560 END IF;
561 ELSIF (round(to_char(p_end_date(j) - p_start_date(j))*24) < 3) THEN
562 l_amount := 0;
563 p_cust_pdm_rate(j) := 0;
564 p_cust_meals_amount(j) := 0;
565 p_cust_accommodation_amount(j) := 0;
566 END IF;
567 END IF;
568 l_tot_amount := l_tot_amount + l_amount;
569 END LOOP;
570 p_exp_line_info.calculated_amount := l_tot_amount ;
571 p_exp_line_info.copy_calc_amt_into_receipt_amt := 'Y';
572 END IF;
573 */
574 ------------------------------------------------------------------------
575 l_debug_info := 'calculated_amount,copy_calc_amt_into_receipt_amt '|| p_exp_line_info.calculated_amount || ' ' || p_exp_line_info.copy_calc_amt_into_receipt_amt;
576 AP_WEB_UTILITIES_PKG.logStatement('AP_WEB_CUST_DFLEX_PKG', l_debug_info);
577 ------------------------------------------------------------------------
578
579
580 EXCEPTION
581 WHEN OTHERS THEN
582 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
583 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
584 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
585 l_curr_calling_sequence);
586 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
587 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
588 END CustomCalculateAmount;
589
590 ----------------------------------------------------------------------------
591 -- CUSTOMVALIDATECOSTCENTER:
592 -- Called by AP_WEB_VALIDATE_UTIL.ValidateCostCenter();
593 -- API provides a means of bypassing native cost center segment validation
597 -- enabled, or FALSE if native validation should be used. By default,
594 -- and using custom code to validate cost center value.
595 --
596 -- Function returns TRUE if custom cost center segment validation is
598 -- we assume that native validation is used.
599 --
600 -- PARAMETERS:
601 --
602 -- p_cs_error - Set this variable with your custom error message.
603 -- If left blank, standard error message will be used.
604 -- p_CostCenterValue - The cost center entered by the user
605 -- p_CostCenterValid - TRUE if cost center is valid, otherwise FALSE;
606 --
607 ----------------------------------------------------------------------------
608 ----------------------------------------------------------------------------
609 FUNCTION CustomValidateCostCenter(
610 p_cs_error OUT NOCOPY VARCHAR2,
611 p_CostCenterValue IN AP_EXPENSE_FEED_DISTS.cost_center%TYPE,
612 p_CostCenterValid IN OUT NOCOPY BOOLEAN,
613 p_employee_id IN NUMBER DEFAULT null) return BOOLEAN IS
614 ----------------------------------------------------------------------------
615 BEGIN
616 --
617 -- Assume cost center is valid
618 --
619 p_CostCenterValid := TRUE;
620
621 return(FALSE); -- return TRUE if using this extension to perform validation
622
623 -- Note: If any error occurred and p_cs_error needs to be set by getting
624 -- a FND message, make sure to use the following syntax:
625 --
626 -- p_CostCenterValid := FALSE;
627 --
628 -- FND_MESSAGE.SET_NAME('SQLAP', '<MESSAGE NAME>');
629 -- p_cs_error := FND_MESSAGE.GET_ENCODED();
630 --
631 -- return(TRUE);
632
633 END CustomValidateCostCenter;
634
635 ----------------------------------------------------------------------------
636 -- CUSTOMDEFAULTCOSTCENTER:
637 -- Called by AP_WEB_VALIDATE_UTIL.GetEmployeeInfo();
638 -- API provides a means of bypassing native cost center segment default
639 -- and using custom code to default cost center value.
640 --
641 -- Use employee_id to determing cost center within this custom
642 -- function and return the cost center segment value.
643 ----------------------------------------------------------------------------
644 ----------------------------------------------------------------------------
645 FUNCTION CustomDefaultCostCenter(
646 p_employee_id IN NUMBER) return VARCHAR2 IS
647 ----------------------------------------------------------------------------
648 BEGIN
649 --
650 --
651 --
652
653 return(null); -- return Cost center value here
654
655 END CustomDefaultCostCenter;
656
657 ----------------------------------------------------------------------------
658 -- CUSTOMVALIDATELINE:
659 -- Called by AP_WEB_VALIDATE_UTIL.ValidateExpLineCustomFields().
660 -- This API provides additional validation by adding errors to
661 -- the error stack.
662 -- Use AP_WEB_UTILITIES_PKG.AddExpErrorNotEncoded to add error messages.
663 ----------------------------------------------------------------------------
664 ----------------------------------------------------------------------------
665 PROCEDURE CustomValidateLine(
666 p_exp_header_info IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec,
667 p_exp_line_info IN AP_WEB_DFLEX_PKG.ExpReportLineRec,
668 p_custom_fields_array IN AP_WEB_DFLEX_PKG.CustomFields_A,
669 p_message_array IN OUT NOCOPY AP_WEB_UTILITIES_PKG.expError)
670 ----------------------------------------------------------------------------
671 IS
672 l_error_msg VARCHAR2(2000);
673 l_start_date Date;
674 l_end_date Date;
675 l_dff_start_date Date;
676 l_dff_end_date Date;
677
678 BEGIN
679 ----------------------------------------------------------------------------
680 -- Example code: Custmize Validation and add to error stack
681 --
682 --IF (to_number(p_exp_line_info.receipt_amount) > 2000) THEN
683 -- l_error_msg := 'Receipt Amount needs to be greater than 2000';
684 -- AP_WEB_UTILITIES_PKG.AddExpErrorNotEncoded(p_message_array,
685 -- l_error_msg,
686 -- AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
687 -- 'ReceiptAmount', -- Prompt
688 -- p_exp_line_info.receipt_index);
689 --ELSIF (...) THEN
690 --END IF;
691
692 -- Example code: To check if expense dates fall between start and end date
693 -- you should know the attributes associated with start and end date segments
694 -- defined in header DFF(Title : Expense Report)
695 /*
696 l_start_date := p_exp_line_info.start_date ;
697 l_end_date := nvl(p_exp_line_info.end_date,p_exp_line_info.start_date);
698 l_dff_start_date := fnd_date.canonical_to_date(p_exp_header_info.attribute2 );
699 l_dff_end_date := fnd_date.canonical_to_date(p_exp_header_info.attribute3 );
700
701 IF ((l_start_date is not null) AND (l_dff_start_date is not null) AND
702 (l_dff_end_date is not null)) THEN
703 IF (l_start_date < l_dff_start_date or l_start_date > l_dff_end_date or
704 l_end_date < l_dff_start_date or l_end_date > l_dff_end_date) THEN
705
706 l_error_msg := 'Date should be between ' || l_dff_start_date || ' - ' || l_dff_end_date;
707 AP_WEB_UTILITIES_PKG.AddExpErrorNotEncoded(p_message_array,
708 l_error_msg,
709 AP_WEB_UTILITIES_PKG.C_ErrorMessageType,
710 'Date', -- Prompt
711 p_exp_line_info.receipt_index);
712 --ELSIF (...) THEN
713 END IF;
714 END IF;
715 */
716 NULL;
717
718 END CustomValidateLine;
719
720 -------------------------------------------------------------------------------
721 -- Bug: 7365109
722 -- CustomGetCountyProvince:
723 -- Called by AP_WEB_DB_EXPLINE_PKG.GetCountyProvince;
724 -- API provides a means of bypassing native address style validation
725 --
726 -- Function returns TRUE if the validation for p_addressstyle passed in needs
727 -- to be skipped.
728 --
729 -- PARAMETERS:
730 --
731 -- p_addressstyle - The address style currently being used for validations
732 -- p_region - INOUT parameter, has a value when thic funtion is called
733 -- can be overridden.
734 -------------------------------------------------------------------------------
735 -------------------------------------------------------------------------------
736 FUNCTION CustomGetCountyProvince(
737 p_addressstyle IN per_addresses.style%TYPE,
738 p_region IN OUT NOCOPY per_addresses.region_1%TYPE) return BOOLEAN IS
739 -------------------------------------------------------------------------------
740 BEGIN
741
742 ------------------------------------------------------------------------------
743 -- Example
744 -- IF p_addressstyle = 'CL_GLB' OR p_addressstyle = 'GT_GLB' THEN
745 -- RETURN TRUE;
746 -- ELSE
747 -- RETURN FALSE;
748 -- END IF;
749 ------------------------------------------------------------------------------
750 RETURN FALSE;
751 END CustomGetCountyProvince;
752
753 END AP_WEB_CUST_DFLEX_PKG;
754
[Home] [Help]