[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_EXPTEMPLATE_PKG
Source
1 PACKAGE BODY AP_WEB_DB_EXPTEMPLATE_PKG AS
2 /* $Header: apwdbetb.pls 120.8 2005/05/25 22:18:24 qle ship $ */
3 --------------------------------------------------------------------------------
4 FUNCTION GetWebEnabledTemplatesCursor(p_cursor OUT NOCOPY TemplateCursor)
5 RETURN BOOLEAN IS
6 --------------------------------------------------------------------------------
7 BEGIN
8 OPEN p_cursor FOR
9 SELECT expense_report_id, report_type
10 FROM ap_expense_reports
11 WHERE web_enabled_flag = 'Y'
12 AND trunc(sysdate) <= trunc(nvl(inactive_date, sysdate))
13 AND AP_WEB_DB_EXPTEMPLATE_PKG.IsExpTemplateWebEnabled(expense_report_id) = 'Y'
14 ORDER BY UPPER(report_type);
15
16 return TRUE;
17 EXCEPTION
18 WHEN NO_DATA_FOUND THEN
19 return FALSE;
20 WHEN OTHERS THEN
21 AP_WEB_DB_UTIL_PKG.RaiseException('GetWebEnabledTemplatesCursor');
22 APP_EXCEPTION.RAISE_EXCEPTION;
23 return FALSE;
24 END GetWebEnabledTemplatesCursor;
25
26 --------------------------------------------------------------------------------
27 FUNCTION GetExpTypesOfTemplateCursor(p_xtemplateid IN expTypes_reportID,
28 p_cursor OUT NOCOPY ExpTypesOfTemplateCursor)
29 RETURN BOOLEAN IS
30 --------------------------------------------------------------------------------
31 BEGIN
32 OPEN P_cursor FOR
33 SELECT nvl(erp.web_friendly_prompt, erp.prompt),
34 to_char(erp.parameter_id)
35 FROM ap_lookup_codes lc,
36 ap_expense_report_params erp,
37 ap_expense_reports er
38 WHERE (erp.expense_report_id = p_xtemplateid
39 OR ( erp.web_enabled_flag = 'Y'
40 AND trunc(sysdate) <= trunc(nvl(er.inactive_date, sysdate))
41 )
42 )
43 AND lc.lookup_type = 'INVOICE DISTRIBUTION TYPE'
44 AND erp.expense_report_id = er.expense_report_id
45 AND erp.line_type_lookup_code = lc.lookup_code
46 AND trunc(sysdate) <= trunc(nvl(erp.end_date, sysdate))
47 ORDER BY nvl(web_friendly_prompt, prompt);
48
49 return TRUE;
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 return FALSE;
53 WHEN OTHERS THEN
54 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpTypesOfTemplateCursor');
55 APP_EXCEPTION.RAISE_EXCEPTION;
56 return FALSE;
57 END GetExpTypesOfTemplateCursor;
58
59 --------------------------------------------------------------------------------
60 FUNCTION GetAllExpenseTypesCursor(p_cursor OUT NOCOPY AllExpenseTypesCursor)
61 RETURN BOOLEAN IS
62 --------------------------------------------------------------------------------
63 BEGIN
64 OPEN p_cursor FOR
65 SELECT nvl(web_friendly_prompt, prompt) web_prompt,
66 erp.parameter_id,
67 erp.justification_required_flag,
68 erp.require_receipt_amount
69 FROM ap_expense_report_params erp;
70
71 return TRUE;
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 return FALSE;
75 WHEN OTHERS THEN
76 AP_WEB_DB_UTIL_PKG.RaiseException('GetAllExpenseTypesCursor');
77 APP_EXCEPTION.RAISE_EXCEPTION;
78 return FALSE;
79 END GetAllExpenseTypesCursor;
80
81 --------------------------------------------------------------------------------
82 FUNCTION GetExpTypesCursor(p_report_id IN expTempl_reportID,
83 p_cursor OUT NOCOPY ExpenseTypesCursor)
84 RETURN BOOLEAN IS
85 --------------------------------------------------------------------------------
86 BEGIN
87 OPEN p_cursor FOR
88
89 SELECT distinct erp.parameter_id,
90 nvl(erp.web_friendly_prompt,erp.prompt) web_prompt,
91 erp.require_receipt_amount,
92 erp.card_exp_type_lookup_code,
93 erp.amount_includes_tax_flag,
94 nvl(erp.justification_required_flag,'V') justif_req
95 FROM ap_expense_report_params erp,
96 ap_expense_reports er
97 WHERE erp.expense_report_id = p_report_id
98 OR (erp.web_enabled_flag = 'Y'
99 AND er.web_enabled_flag = 'Y'
100 AND trunc(sysdate) <= trunc(nvl(inactive_date, sysdate))
101 AND erp.expense_report_id = er.expense_report_id)
102 AND trunc(sysdate) <= trunc(nvl(erp.end_date, sysdate))
103 ORDER BY web_prompt;
104
105
106 return TRUE;
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 return FALSE;
110 WHEN OTHERS THEN
111 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpTypesCursor');
112 APP_EXCEPTION.RAISE_EXCEPTION;
113 return FALSE;
114 END GetExpTypesCursor;
115
116 --------------------------------------------------------------------------------
117 FUNCTION GetWebExpTypesCursor(p_cursor OUT NOCOPY WebExpenseTypesCursor)
118 RETURN BOOLEAN IS
119 --------------------------------------------------------------------------------
120 BEGIN
121 OPEN p_cursor FOR
122 SELECT erp.parameter_id,
123 nvl(erp.web_friendly_prompt,erp.prompt) web_prompt,
124 erp.prompt,
125 erp.require_receipt_amount,
126 card_exp_type_lookup_code,
127 nvl(erp.justification_required_flag,'V') justif_req,
128 calculate_amount_flag,
129 erp.amount_includes_tax_flag,
130 erp.pa_expenditure_type
131 FROM ap_expense_report_params erp,
132 ap_expense_reports er
133 WHERE erp.expense_report_id = er.expense_report_id
134 AND trunc(sysdate) <= trunc(nvl(er.inactive_date, sysdate))
135 AND trunc(sysdate) <= trunc(nvl(erp.end_date, sysdate))
136 AND er.web_enabled_flag = 'Y';
137
138 return TRUE;
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 return FALSE;
142 WHEN OTHERS THEN
143 AP_WEB_DB_UTIL_PKG.RaiseException('GetWebExpTypesCursor');
144 APP_EXCEPTION.RAISE_EXCEPTION;
145 return FALSE;
146 END GetWebExpTypesCursor;
147
148
149 --------------------------------------------------------------------------------
150 FUNCTION GetJustifReqdExpTypesCursor(p_cursor OUT NOCOPY JustificationExpTypeCursor)
151 RETURN BOOLEAN IS
152 --------------------------------------------------------------------------------
153 BEGIN
154 OPEN p_cursor FOR
155 SELECT erp.parameter_id
156 FROM ap_expense_report_params erp
157 WHERE nvl(erp.justification_required_flag,'V') = 'Y'
158 ORDER BY nvl(web_friendly_prompt, prompt);
159
160 return TRUE;
161 EXCEPTION
162 WHEN NO_DATA_FOUND THEN
163 return FALSE;
164 WHEN OTHERS THEN
165 AP_WEB_DB_UTIL_PKG.RaiseException('GetJustifReqdExpTypesCursor');
166 APP_EXCEPTION.RAISE_EXCEPTION;
167 return FALSE;
168 END GetJustifReqdExpTypesCursor;
169
170
171 --------------------------------------------------------------------------------
172 FUNCTION GetTemplateName(P_TemplateID IN expTypes_reportID,
173 P_TemplateName OUT NOCOPY expTypes_reportType)
174
175 RETURN BOOLEAN IS
176 --------------------------------------------------------------------------------
177 l_debugInfo varchar2(240);
178 BEGIN
179
180 -------------------------------------------------------
181 l_debugInfo := 'getTemplateName';
182 -------------------------------------------------------
183 SELECT report_type
184 INTO P_TemplateName
185 FROM ap_expense_reports WHERE web_enabled_flag = 'Y'
186 AND expense_report_id = P_TemplateID;
187
188 return TRUE;
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 return FALSE;
192 WHEN OTHERS THEN
193 AP_WEB_DB_UTIL_PKG.RaiseException('GetTemplateName');
194 APP_EXCEPTION.RAISE_EXCEPTION;
195 return FALSE;
196 END GetTemplateName;
197
198
199 --------------------------------------------------------------------------------
200 FUNCTION GetExpTemplateId(
201 p_report_type IN expTypes_reportType,
202 p_exp_temp_id OUT NOCOPY expTypes_reportID)
203 RETURN BOOLEAN IS
204 --------------------------------------------------------------------------------
205 BEGIN
206 -- Fix 1472710, Added check to verify if the template is valid.
207 SELECT expense_report_id
208 INTO p_exp_temp_id
209 FROM ap_expense_reports
210 WHERE web_enabled_flag = 'Y'
211 AND trunc(sysdate) <= trunc(nvl(inactive_date, sysdate))
212 AND report_type = p_report_type;
213
214 return TRUE;
215
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 return FALSE;
219 WHEN OTHERS THEN
220 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpTemplateId');
221 APP_EXCEPTION.RAISE_EXCEPTION;
222 return FALSE;
223 END GetExpTemplateId;
224
225 -------------------------------------------------------------------
226 FUNCTION GetExpTypePrompt(p_parameter_id IN expTempl_paramID,
227 p_exp_prompt OUT NOCOPY expTempl_prompt)
228 RETURN BOOLEAN IS
229 -------------------------------------------------------------------
230 BEGIN
231 SELECT prompt
232 INTO p_exp_prompt
233 FROM ap_expense_report_params
234 WHERE parameter_id = p_parameter_id;
235
236 return TRUE;
237
238 EXCEPTION
239 WHEN NO_DATA_FOUND THEN
240 return FALSE;
241 WHEN OTHERS THEN
242 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpTypePrompt');
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 return FALSE;
245 END GetExpTypePrompt;
246
247 -------------------------------------------------------------------
248 FUNCTION GetPersonalParamID(p_parameter_id OUT NOCOPY expTempl_paramID)
249 RETURN BOOLEAN IS
250 -------------------------------------------------------------------
251 BEGIN
252 SELECT parameter_id
253 INTO p_parameter_id
254 FROM ap_expense_report_params
255 WHERE expense_type_code = 'PERSONAL';
256
257 return TRUE;
258
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 return FALSE;
262 WHEN OTHERS THEN
263 AP_WEB_DB_UTIL_PKG.RaiseException('GetPersonalParamID');
264 APP_EXCEPTION.RAISE_EXCEPTION;
265 return FALSE;
266 END GetPersonalParamID;
267
268 -------------------------------------------------------------------
269 FUNCTION GetExpTypeInfo(P_ExpTypeID IN expTempl_paramID,
270 P_ExpTypeRec IN OUT NOCOPY ExpTypeInfoRec
271 ) RETURN BOOLEAN IS
272 -------------------------------------------------------------------
273 l_debug_info VARCHAR2(100);
274 l_curr_calling_sequence VARCHAR2(100) := 'GetExpTypeInfo';
275 BEGIN
276 -----------------------------------------------------------------
277 l_debug_info := 'Retrieving line_type_lookup_code';
278 -------------------------------------------------------------------
279 SELECT FLEX_CONCACTENATED,
280 VAT_CODE,
281 AMOUNT_INCLUDES_TAX_FLAG,
282 LINE_TYPE_LOOKUP_CODE,
283 PA_EXPENDITURE_TYPE
284 INTO P_ExpTypeRec.flex_concat,
285 P_ExpTypeRec.vat_code,
286 P_ExpTypeRec.amt_incl_tax,
287 P_ExpTypeRec.line_type,
288 p_ExpTypeRec.pa_exp_type
289 FROM AP_EXPENSE_REPORT_PARAMS
290 WHERE PARAMETER_ID = P_ExpTypeID;
291
292 return TRUE;
293 EXCEPTION
294 WHEN NO_DATA_FOUND THEN
295 return FALSE;
296 WHEN OTHERS THEN
297 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpTypeInfo', l_debug_info);
298 APP_EXCEPTION.RAISE_EXCEPTION;
299 return FALSE;
300 END GetExpTypeInfo;
301
302
303 -------------------------------------------------------------------
304 FUNCTION IsExpTemplateWebEnabled(p_expense_report_id IN ap_expense_reports.expense_report_id%TYPE)
305 RETURN VARCHAR2 IS
306 -------------------------------------------------------------------
307
308 l_exp_types_cursor AP_WEB_DB_EXPTEMPLATE_PKG.ExpenseTypesCursor;
309 l_parameter_id AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
310 l_web_FriendlyPrompt AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_webFriendlyPrompt;
311 l_require_receipt_amount AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_requireReceiptAmt;
312 l_card_exp_type_lookup_code AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_cardExpTypeLookupCode;
313 l_amount_includes_tax_flag AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_amtInclTaxFlag;
314 l_justif_req AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_justificationReqdFlag;
315
316 BEGIN
317
318 IF (AP_WEB_DB_EXPTEMPLATE_PKG.GetExpTypesCursor(to_number(p_expense_report_id), l_exp_types_cursor)) THEN
319 LOOP
320 FETCH l_exp_types_cursor
321 INTO l_parameter_id,
322 l_web_FriendlyPrompt,
323 l_require_receipt_amount,
324 l_card_exp_type_lookup_code,
325 l_amount_includes_tax_flag,
326 l_justif_req;
327 EXIT WHEN l_exp_types_cursor%NOTFOUND;
328
329 if (AP_WEB_OA_DISC_PKG.AreMPDRateSchedulesAssigned(l_parameter_id)
330 or AP_WEB_OA_DISC_PKG.ArePCRateSchedulesAssigned(l_parameter_id)
331 or AP_WEB_OA_DISC_PKG.AreExpenseFieldsRequired(l_parameter_id)
332 or AP_WEB_OA_DISC_PKG.AreExpenseFieldsEnabled(l_parameter_id)
333 or AP_WEB_OA_DISC_PKG.IsItemizationRequired(l_parameter_id)
334 ) then
335 CLOSE l_exp_types_cursor;
336 return 'N';
337 end if;
338
339 END LOOP;
340 CLOSE l_exp_types_cursor;
341
342 return 'Y';
343
344 END IF;
345
346 return 'N';
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 return 'N';
351 WHEN OTHERS THEN
352 AP_WEB_DB_UTIL_PKG.RaiseException('IsExpTemplateWebEnabled');
353 APP_EXCEPTION.RAISE_EXCEPTION;
354 return 'N';
355 END IsExpTemplateWebEnabled;
356
357 -------------------------------------------------------------------
358 FUNCTION GetNumWebEnabledExpTemplates(p_count OUT NOCOPY NUMBER)
359 RETURN BOOLEAN IS
360 -------------------------------------------------------------------
361 BEGIN
362 SELECT COUNT(*)
363 INTO p_count
364 FROM ap_expense_reports
365 WHERE web_enabled_flag = 'Y'
366 AND trunc(sysdate) <= trunc(nvl(inactive_date, sysdate))
367 AND AP_WEB_DB_EXPTEMPLATE_PKG.IsExpTemplateWebEnabled(expense_report_id) = 'Y';
368
369 return TRUE;
370 EXCEPTION
371 WHEN NO_DATA_FOUND THEN
372 p_count := 0;
373 return FALSE;
374 WHEN OTHERS THEN
375 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumWebEnabledExpTemplates');
376 APP_EXCEPTION.RAISE_EXCEPTION;
377 return FALSE;
378 END GetNumWebEnabledExpTemplates;
379
380
381 ----------------------------------------------------------------------------
382 FUNCTION Get_ItemDesc_LookupCode(
383 p_xtype IN VARCHAR2,
384 p_item_description OUT NOCOPY expTempl_prompt,
385 p_line_type_lookup_code OUT NOCOPY expTempl_lineTypeLookupCode,
386 p_require_receipt_amount OUT NOCOPY expTempl_requireReceiptAmt
387 ) RETURN BOOLEAN IS
388 ----------------------------------------------------------------------------
389 BEGIN
390 IF (p_xtype is NULL) THEN
391 p_item_description := 'ITEM';
392 p_line_type_lookup_code := 'ITEM';
393 p_require_receipt_amount := 0;
394 ELSE
395 SELECT nvl(prompt,'ITEM'),
396 nvl(line_type_lookup_code,'ITEM'),
397 nvl(require_receipt_amount,-1)
398 INTO p_item_description,
399 p_line_type_lookup_code,
400 p_require_receipt_amount
401 FROM AP_EXPENSE_REPORT_PARAMS
402 WHERE parameter_id = p_xtype;
403 END IF;
404
405 RETURN TRUE;
406
407 EXCEPTION
408 WHEN NO_DATA_FOUND THEN
409 RETURN FALSE;
410 WHEN OTHERS THEN
411 AP_WEB_DB_UTIL_PKG.RaiseException('Get_ItemDesc_LookupCode');
412 APP_EXCEPTION.RAISE_EXCEPTION;
413 return FALSE;
414
415 END Get_ItemDesc_LookupCode;
416
420 )
417 -------------------------------------------------------------------
418 FUNCTION GetDefaultTemplateId(
419 p_default_template_id OUT NOCOPY AP_SYSTEM_PARAMETERS.expense_report_id%TYPE
421 RETURN BOOLEAN IS
422 -------------------------------------------------------------------
423 l_base_curr_code AP_WEB_DB_AP_INT_PKG.apSetup_baseCurrencyCode;
424 l_set_of_books_id AP_WEB_DB_AP_INT_PKG.apSetup_setOfBooksID;
425 l_default_exch_rate_type AP_WEB_DB_AP_INT_PKG.apSetUp_defaultExchRateType;
426 BEGIN
427 return AP_WEB_DB_AP_INT_PKG.get_ap_system_params(p_base_curr_code => l_base_curr_code,
428 p_set_of_books_id => l_set_of_books_id,
429 p_expense_report_id => p_default_template_id,
430 p_default_exch_rate_type => l_default_exch_rate_type);
431
432 EXCEPTION
433 WHEN NO_DATA_FOUND THEN
434 p_default_template_id := null;
435 return FALSE;
436 WHEN OTHERS THEN
437 AP_WEB_DB_UTIL_PKG.RaiseException('GetDefaultTemplateId');
438 APP_EXCEPTION.RAISE_EXCEPTION;
439 return FALSE;
440 END GetDefaultTemplateId;
441
442
443 FUNCTION IsCustomCalculateEnabled(p_template_id IN VARCHAR2,
444 p_parameter_id IN VARCHAR2)
445 RETURN BOOLEAN IS
446
447 l_calculate_amount_enabled AP_EXPENSE_REPORT_PARAMS.calculate_amount_flag%TYPE;
448 l_debug_info VARCHAR2(2000);
449
450 BEGIN
451
452 ------------------------------------------------------------------------
453 l_debug_info := 'Retrieving Calculate Amount Flag for the expense type';
454 ------------------------------------------------------------------------
455
456 SELECT erp.calculate_amount_flag
457 INTO l_calculate_amount_enabled
458 FROM ap_lookup_codes lc,
459 ap_expense_report_params erp
460 WHERE (erp.expense_report_id = to_number(p_template_id)
461 AND erp.parameter_id = to_number(p_parameter_id))
462 AND erp.line_type_lookup_code = lc.lookup_code
463 AND lc.lookup_type = 'INVOICE DISTRIBUTION TYPE';
464
465 if (SQL%NOTFOUND) then
466 Raise NO_DATA_FOUND;
467 end if;
468
469 IF l_calculate_amount_enabled = 'Y' THEN
470 return TRUE;
471 ELSE
472 return FALSE;
473 END IF;
474
475 EXCEPTION
476 WHEN NO_DATA_FOUND THEN return FALSE;
477 WHEN OTHERS THEN
478 BEGIN
479 IF (SQLCODE <> -20001) THEN
480 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
481 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
482 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
483 'IsCustomCalculateEnabled');
484 FND_MESSAGE.SET_TOKEN('PARAMETERS',
485 'None passed.');
486 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
487 APP_EXCEPTION.RAISE_EXCEPTION;
488 ELSE
489 -- Do not need to set the token since it has been done in the
490 -- child process
491 RAISE;
492 END IF;
493 END;
494 END IsCustomCalculateEnabled;
495
496
497 -------------------------------------------------------------------
498 -- This function was added for bug 2771545
499 FUNCTION GetRequireReceiptAmt(P_ExpTypeID IN expTempl_paramID,
500 p_require_receipt_amount OUT NOCOPY expTempl_requireReceiptAmt
501 ) RETURN BOOLEAN IS
502 -------------------------------------------------------------------
503 l_debug_info VARCHAR2(100);
504 l_curr_calling_sequence VARCHAR2(100) := 'GetRequireReceiptAmt';
505
506 BEGIN
507 -----------------------------------------------------------------
508 l_debug_info := 'Retrieving require_receipt_amount';
509 -------------------------------------------------------------------
510 SELECT require_receipt_amount
511 INTO p_require_receipt_amount
512 FROM AP_EXPENSE_REPORT_PARAMS
513 WHERE PARAMETER_ID = P_ExpTypeID;
514
515 return TRUE;
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 return FALSE;
519 WHEN OTHERS THEN
520 IF (SQLCODE <> -20001) THEN
521 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
522 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
523 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'GetRequireReceiptAmt');
524 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
525 APP_EXCEPTION.RAISE_EXCEPTION;
526 ELSE
527 -- Do not need to set the token since it has been done in the
528 -- child process
529 RAISE;
530 END IF;
531 END GetRequireReceiptAmt;
532
533 /* jrautiai ADJ Fix Start */
534 FUNCTION GetRoundingParamID(p_parameter_id OUT NOCOPY expTempl_paramID)
535 RETURN BOOLEAN IS
536 BEGIN
537 SELECT parameter_id
538 INTO p_parameter_id
539 FROM ap_expense_report_params
540 WHERE expense_type_code = 'ROUNDING';
541
542 return TRUE;
543
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 return FALSE;
547 WHEN OTHERS THEN
548 AP_WEB_DB_UTIL_PKG.RaiseException('GetRoundingParamID');
549 APP_EXCEPTION.RAISE_EXCEPTION;
550 return FALSE;
551 END GetRoundingParamID;
552 /* jrautiai ADJ Fix End */
553
554 END AP_WEB_DB_EXPTEMPLATE_PKG;