4 /*=============================================================================
1 PACKAGE BODY AP_ETAX_PKG AS
2 /* $Header: apetaxpb.pls 120.15.12010000.3 2009/12/30 14:01:32 ppodhiya ship $ */
3
5 | FUNCTION - Calling_eTax()
6 |
7 | DESCRIPTION
8 | Public function that will call the requested AP_ETAX_SERVICES_PKG function
9 | This API is called from different points throughout AP.
10 | This API assumes the calling code controls the commit cycle.
11 | This function returns TRUE if the call to the service is successful.
12 | Otherwise, FALSE.
13 |
14 | PARAMETERS
15 | P_Invoice_Id - invoice id
16 | P_Line_Number - This parameter will be used to allow this API to
17 | calculate tax only for the line specified in this
18 | parameter. Additionally, this parameter will be used
19 | to determine the PREPAY line created for prepayment
20 | unapplications.
21 | P_Calling_Mode - calling mode. Identifies which service to call
22 | P_Override_Status - only for calling_mode OVERRIDE TAX
23 | P_Line_Number_To_Delete - required when calling mode is MARK TAX LINES DELETED
24 | P_Interface_Invoice_Id - invoice id in the ap_invoices_interface table.
25 | this parameter is used only while calling the
26 | API to calculate tax from the import program
27 | P_All_Error_Messages - Determine if the calling point wants the returning
28 | of all or only 1 error message. Calling point will
29 | pass Y in the case they want to handle the return of
30 | more than one message from the FND message pile. N
31 | if they just want 1 message to be returned.
32 | P_error_code - Error code to be returned
33 | P_calling_sequence - Calling sequence
34 |
35 | MODIFICATION HISTORY
36 | DATE Author Action
37 | 07-OCT-2003 SYIDNER Created
38 | 05-NOV-2003 SYIDNER Included new P_Line_Number_To_Delete and
39 | P_All_Error_Messages parameters
40 | 05-MAR-2004 SYIDNER Included P_Line_Number as a new parameter
41 |
42 *============================================================================*/
43
44 FUNCTION Calling_eTax(
45 P_Invoice_id IN NUMBER,
46 P_Line_Number IN NUMBER DEFAULT NULL,
47 P_Calling_Mode IN VARCHAR2,
48 P_Override_Status IN VARCHAR2 DEFAULT NULL,
49 P_Line_Number_To_Delete IN NUMBER DEFAULT NULL,
50 P_Interface_Invoice_Id IN NUMBER DEFAULT NULL,
51 P_Event_Id IN NUMBER DEFAULT NULL,
52 P_All_Error_Messages IN VARCHAR2,
53 P_error_code OUT NOCOPY VARCHAR2,
54 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
55
56 IS
57 l_debug_info VARCHAR2(240);
58 l_curr_calling_sequence VARCHAR2(4000);
59
60 l_return_status BOOLEAN := TRUE;
61 l_assessable_value NUMBER; --8911181
62 l_invoice_amount NUMBER; --8911181
63 l_invoice_currency_code AP_INVOICES_ALL.INVOICE_CURRENCY_CODE%TYPE; --8911181
64
65 BEGIN
66
67 l_curr_calling_sequence := 'AP_ETAX_PKG.Calling_eTax<-' ||
68 P_calling_sequence;
69
70 ------------------------------------------------------------------
71 l_debug_info := 'Step 1: P_Calling_Mode is:'||P_Calling_Mode;
72 ------------------------------------------------------------------
73
74 -- Bug 9034372. Added new calling mode : 'QUICK CANCEL'
75
76 IF (P_Calling_Mode IN ('CALCULATE', 'APPLY PREPAY', 'QUICK CANCEL')) THEN
77 -------------------------------------------------------------------
78 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Calculate';
79 -------------------------------------------------------------------
80 -- BUG 8911181 START : Updating Invoice Lines Assessable Value --
81
82 SELECT to_number(fnd_profile.value('AP_JEA_TAX_ASSESSABLE_VALUE'))
83 INTO l_assessable_value
84 FROM dual;
85
86 IF l_assessable_value IS NOT NULL THEN
87
88 SELECT invoice_currency_code
89 INTO l_invoice_currency_code
90 FROM ap_invoices_all
91 WHERE invoice_id = P_Invoice_Id;
92
93 SELECT SUM(amount)
94 INTO l_invoice_amount
95 FROM ap_invoice_lines_all
96 WHERE invoice_id = P_Invoice_Id
97 AND line_type_lookup_code IN ('ITEM', 'FREIGHT');
98
99 IF l_invoice_amount > l_assessable_value THEN
100
101 UPDATE ap_invoice_lines_all
102 SET assessable_value = AP_Utilities_Pkg.AP_Round_Currency(
103 amount*l_assessable_value/
104 decode(l_invoice_amount,0,
105 decode(l_assessable_value,0,1,l_assessable_value),
106 l_invoice_amount)
107 ,l_invoice_currency_code)
108 WHERE Invoice_id = p_invoice_id
109 AND line_type_lookup_code NOT IN ('TAX', 'AWT');
110
111 END IF; -- l_invoice_amount > l_assessable_value
112
113 END IF; -- l_assessable_value IS NOT NULL
117 IF NOT (AP_ETAX_SERVICES_PKG.Calculate(
114 -- BUG 8911181 ENDS
115
116
118 P_Invoice_id => P_Invoice_Id,
119 P_Line_Number => P_Line_Number,
120 P_Calling_Mode => P_Calling_Mode,
121 P_All_Error_Messages => P_All_Error_Messages,
122 P_Error_Code => P_Error_Code,
123 P_Calling_Sequence => l_curr_calling_sequence)) THEN
124
125 l_return_status := FALSE;
126 END IF;
127
128 ELSIF (P_Calling_Mode = 'CALCULATE IMPORT') THEN
129 -------------------------------------------------------------------
130 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Calculate_Import';
131 -------------------------------------------------------------------
132
133 IF NOT (AP_ETAX_SERVICES_PKG.Calculate_Import(
134 P_Invoice_id => P_Invoice_Id,
135 P_Calling_Mode => P_Calling_Mode,
136 P_Interface_Invoice_Id => P_Interface_Invoice_Id,
137 P_All_Error_Messages => P_All_Error_Messages,
138 P_Error_Code => P_Error_Code,
139 P_Calling_Sequence => l_curr_calling_sequence)) THEN
140
141 l_return_status := FALSE;
142 END IF;
143
144
145 ELSIF (P_Calling_Mode IN ('DISTRIBUTE', 'DISTRIBUTE RECOUP')) THEN
146 -------------------------------------------------------------------
147 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Distribute';
148 -------------------------------------------------------------------
149
150 IF NOT (AP_ETAX_SERVICES_PKG.Distribute(
151 P_Invoice_id => P_Invoice_Id,
152 P_Line_Number => P_Line_Number,
153 P_Calling_Mode => P_Calling_Mode,
154 P_All_Error_Messages => P_All_Error_Messages,
155 P_Error_Code => P_Error_Code,
156 P_Calling_Sequence => l_curr_calling_sequence)) THEN
157
158 l_return_status := FALSE;
159 END IF;
160
161 ELSIF (P_Calling_Mode = 'DISTRIBUTE IMPORT') THEN
162 -------------------------------------------------------------------
163 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Distribute_Import';
164 -------------------------------------------------------------------
165
166 IF NOT (AP_ETAX_SERVICES_PKG.Distribute_Import(
167 P_Invoice_id => P_Invoice_Id,
168 P_Calling_Mode => P_Calling_Mode,
169 P_All_Error_Messages => P_All_Error_Messages,
170 P_Error_Code => P_Error_Code,
171 P_Calling_Sequence => l_curr_calling_sequence)) THEN
172
173 l_return_status := FALSE;
174 END IF;
175
176 ELSIF (P_Calling_Mode = 'IMPORT INTERFACE') THEN
177 -------------------------------------------------------------------
178 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Import_Interface';
179 -------------------------------------------------------------------
180
181 IF NOT (AP_ETAX_SERVICES_PKG.Import_Interface(
182 P_Invoice_id => P_Invoice_Id,
183 P_Calling_Mode => P_Calling_Mode,
184 P_Interface_Invoice_Id => P_Interface_Invoice_Id,
185 P_All_Error_Messages => P_All_Error_Messages,
186 P_Error_Code => P_Error_Code,
187 P_Calling_Sequence => l_curr_calling_sequence)) THEN
188
189 l_return_status := FALSE;
190 END IF;
191
192 ELSIF (P_Calling_Mode = 'REVERSE INVOICE') THEN
193 -------------------------------------------------------------------
194 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Reverse_Invoice';
195 -------------------------------------------------------------------
196
197 IF NOT (AP_ETAX_SERVICES_PKG.Reverse_Invoice(
198 P_Invoice_id => P_Invoice_Id,
199 P_Calling_Mode => P_Calling_Mode,
200 P_All_Error_Messages => P_All_Error_Messages,
201 P_Error_Code => P_Error_Code,
202 P_Calling_Sequence => l_curr_calling_sequence)) THEN
203
204 l_return_status := FALSE;
205 END IF;
206
207 ELSIF (P_Calling_Mode = 'OVERRIDE TAX') THEN
208 -------------------------------------------------------------------
209 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Override_Tax';
210 -------------------------------------------------------------------
211
212 IF NOT (AP_ETAX_SERVICES_PKG.Override_Tax(
213 P_Invoice_id => P_Invoice_Id,
214 P_Calling_Mode => P_Calling_Mode,
215 P_Override_Status => P_Override_Status,
216 P_Event_Id => P_Event_Id,
217 P_All_Error_Messages => P_All_Error_Messages,
218 P_Error_Code => P_Error_Code,
222 END IF;
219 P_Calling_Sequence => l_curr_calling_sequence)) THEN
220
221 l_return_status := FALSE;
223
224 ELSIF (P_Calling_Mode = 'OVERRIDE RECOVERY') THEN
225 -------------------------------------------------------------------
226 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Override_Recovery';
227 -------------------------------------------------------------------
228
229 IF NOT (AP_ETAX_SERVICES_PKG.Override_Recovery(
230 P_Invoice_id => P_Invoice_Id,
231 P_Calling_Mode => P_Calling_Mode,
232 P_All_Error_Messages => P_All_Error_Messages,
233 P_Error_Code => P_Error_Code,
234 P_Calling_Sequence => l_curr_calling_sequence)) THEN
235
236 l_return_status := FALSE;
237 END IF;
238
239 ELSIF (P_Calling_Mode IN ('CANCEL INVOICE',
240 'FREEZE INVOICE',
241 'UNFREEZE INVOICE',
242 'DISCARD LINE',
243 'UNAPPLY PREPAY')) THEN --bugfix:5697764
244 -------------------------------------------------------------------
245 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Global_Document_Update';
246 -------------------------------------------------------------------
247
248 IF P_Calling_Mode IN ('DISCARD LINE', 'UNAPPLY PREPAY') THEN
249 IF NOT (AP_ETAX_SERVICES_PKG.Global_Document_Update(
250 P_Invoice_id => P_Invoice_Id,
251 P_Line_Number => P_Line_Number,
252 P_Calling_Mode => P_Calling_Mode,
253 P_All_Error_Messages => P_All_Error_Messages,
254 P_Error_Code => P_Error_Code,
255 P_Calling_Sequence => l_curr_calling_sequence)) THEN
256
257 l_return_status := FALSE;
258 END IF;
259 ELSE
260 IF NOT (AP_ETAX_SERVICES_PKG.Global_Document_Update(
261 P_Invoice_id => P_Invoice_Id,
262 P_Calling_Mode => P_Calling_Mode,
263 P_All_Error_Messages => P_All_Error_Messages,
264 P_Error_Code => P_Error_Code,
265 P_Calling_Sequence => l_curr_calling_sequence)) THEN
266
267 l_return_status := FALSE;
268 END IF;
269 END IF;
270
271 ELSIF (P_Calling_Mode = 'MARK TAX LINES DELETED') THEN
272 -------------------------------------------------------------------
273 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Mark_Tax_Lines_Deleted';
274 -------------------------------------------------------------------
275
276 IF NOT (AP_ETAX_SERVICES_PKG.Mark_Tax_Lines_Deleted(
277 P_Invoice_id => P_Invoice_Id,
278 P_Calling_Mode => P_Calling_Mode,
279 P_Line_Number_To_Delete => P_Line_Number_To_Delete,
280 P_All_Error_Messages => P_All_Error_Messages,
281 P_Error_Code => P_Error_Code,
282 P_Calling_Sequence => l_curr_calling_sequence)) THEN
283
284 l_return_status := FALSE;
285 END IF;
286
287
288 ELSIF (P_Calling_Mode = 'VALIDATE') THEN
289 -------------------------------------------------------------------
290 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Validate_Invoice';
291 -------------------------------------------------------------------
292
293 IF NOT (AP_ETAX_SERVICES_PKG.Validate_Invoice(
294 P_Invoice_id => P_Invoice_Id,
295 P_Calling_Mode => P_Calling_Mode,
296 P_All_Error_Messages => P_All_Error_Messages,
297 P_Error_Code => P_Error_Code,
298 P_Calling_Sequence => l_curr_calling_sequence)) THEN
299
300 l_return_status := FALSE;
301 END IF;
302
303 ELSIF (P_Calling_Mode = 'RECOUPMENT') THEN
304 -------------------------------------------------------------------
308 IF NOT (AP_ETAX_SERVICES_PKG.Generate_Recouped_Tax(
305 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Generate_Recouped_Tax';
306 -------------------------------------------------------------------
307
309 P_Invoice_id => P_Invoice_Id,
310 P_Invoice_Line_Number => P_Line_Number,
311 P_Calling_Mode => P_Calling_Mode,
312 P_All_Error_Messages => P_All_Error_Messages,
313 P_Error_Code => P_Error_Code,
314 P_Calling_Sequence => l_curr_calling_sequence)) THEN
315
316 l_return_status := FALSE;
317 END IF;
318
319 ELSIF (P_Calling_Mode = 'DELETE_TAX_DIST') THEN
320 -------------------------------------------------------------------
321 l_debug_info := 'Step 2: Calling AP_ETAX_SERVICES_PKG.Delete_Tax_Distributions';
322 -------------------------------------------------------------------
323
324 IF NOT (AP_ETAX_SERVICES_PKG.Delete_Tax_Distributions
325 (p_invoice_id => P_Invoice_Id,
326 p_calling_mode => P_Calling_Mode,
327 p_all_error_messages => P_All_Error_Messages,
328 p_error_code => P_Error_Code,
329 p_calling_sequence => l_curr_calling_sequence)) THEN
330
331 l_return_status := FALSE;
332 END IF;
333
334 END IF;
335
336 RETURN l_return_status;
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 IF (SQLCODE <> -20001) THEN
341 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
342 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
343 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
344 FND_MESSAGE.SET_TOKEN('PARAMETERS',
345 ' P_Invoice_Id = '||P_Invoice_Id||
346 ' P_Calling_Mode = '||P_Calling_Mode||
347 ' P_Override_Status = '||P_Override_Status||
348 ' P_Line_Number_To_Delete = '||P_Line_Number_To_Delete||
349 ' P_All_Error_Messages = '||P_All_Error_Messages||
350 ' P_Error_Code = '||P_Error_Code||
351 ' P_Calling_Sequence = '||P_Calling_Sequence);
352 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
353
354 END IF;
355 APP_EXCEPTION.RAISE_EXCEPTION;
356
357 END Calling_eTax;
358
359
360 /*=============================================================================
361 | FUNCTION - Calculate_Quote ()
362 |
363 | DESCRIPTION
364 | This function will return the tax amount and indicate if it is inclusive.
365 | This will be called from the recurring invoices form. This is a special
366 | case, as the invoices for which the tax is to be calculated are not yet
367 | saved to the database and eBTax global temporary tables are populated
368 | based on the parameters. A psuedo-line is inserted into the GTT and
369 | removed after the tax amount is calculated.
370 |
371 | PARAMETERS
372 | P_Invoice_header_rec - Invoice header info
373 | P_Invoice_Lines_Rec - Invoice lines info
374 | P_Calling_Mode - Calling mode. (CALCULATE_QUOTE)
375 | P_All_Error_Messages - Should API return 1 error message or allow
376 | calling point to get them from message stack
377 | P_error_code - Error code to be returned
378 | P_calling_sequence - Calling sequence
379 |
380 | MODIFICATION HISTORY
381 | DATE Author Action
382 | 13-AUG-2003 Sanjay Created
383 *============================================================================*/
384 FUNCTION Calculate_Quote(
385 P_Calling_Mode IN VARCHAR2,
386 P_All_Error_Messages IN VARCHAR2,
387 P_Invoice_Header_Rec IN ap_invoices_all%ROWTYPE DEFAULT NULL,
388 P_Invoice_Lines_Rec IN ap_invoice_lines_all%ROWTYPE DEFAULT NULL,
389 P_Tax_Amount OUT NOCOPY NUMBER,
390 P_Tax_Amt_Included OUT NOCOPY VARCHAR2,
391 P_error_code OUT NOCOPY VARCHAR2,
392 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN IS
393
394 l_curr_calling_sequence VARCHAR2(4000);
395 l_return_status BOOLEAN := TRUE;
396
397 BEGIN
398
399 l_curr_calling_sequence := 'AP_ETAX_PKG.Calculate_Quote<-' || P_calling_sequence;
400
401
402 IF P_Calling_Mode = 'CALCULATE QUOTE' THEN
403
404 IF NOT (AP_ETAX_SERVICES_PKG.CALCULATE_QUOTE(
405 P_Invoice_Header_Rec => p_invoice_header_rec,
406 P_Invoice_Lines_Rec => p_invoice_lines_rec,
407 P_Calling_Mode => p_calling_mode,
408 P_Tax_Amount => p_tax_amount,
409 P_Tax_Amt_Included => P_Tax_Amt_Included,
410 P_Error_Code => p_error_code,
411 P_Calling_Sequence => p_calling_sequence )) THEN
412
413 l_return_status := FALSE;
414 END IF;
415 END IF;
416
417 RETURN l_return_status;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 IF (SQLCODE <> -20001) THEN
422 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
423 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
424 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
425
426 END IF;
427 APP_EXCEPTION.RAISE_EXCEPTION;
428
429 RETURN l_return_status;
430
434 | FUNCTION - callETax()
431 END Calculate_Quote;
432
433 /*=============================================================================
435 |
436 | DESCRIPTION
437 | Public function that will call the requested AP_ETAX_SERVICES_PKG function
438 |
439 | This API is a wrapper of the calling_etax() api, mainly used by JDBC calls
440 | from OA based applications.
441 | This API will commit at the end.
442 | This function returns 0 if the call to the service is successful.
443 | Otherwise, 1.
444 |
445 | PARAMETERS
446 | x_Invoice_Id - invoice id
447 | x_Line_Number - This parameter will be used to allow this API to
448 | calculate tax only for the line specified in this
449 | parameter. Additionally, this parameter will be used
450 | to determine the PREPAY line created for prepayment
451 | unapplications.
452 | x_Calling_Mode - calling mode. Identifies which service to call
453 | x_Override_Status - only for calling_mode OVERRIDE TAX
454 | x_Line_Number_To_Delete - required when calling mode is MARK TAX LINES DELETED
455 | x_Interface_Invoice_Id - invoice id in the ap_invoices_interface table.
456 | this parameter is used only while calling the
457 | API to calculate tax from the import program
458 | x_All_Error_Messages - Determine if the calling point wants the returning
459 | of all or only 1 error message. Calling point will
460 | pass Y in the case they want to handle the return of
461 | more than one message from the FND message pile. N
462 | if they just want 1 message to be returned.
463 | x_event_id - used by OVERRIDE TAX
464 | x_error_code - Error code to be returned
465 | x_calling_sequence - Calling sequence
466 |
467 *============================================================================*/
468
469 FUNCTION callETax(
470 x_Invoice_id IN NUMBER,
471 x_Line_Number IN NUMBER DEFAULT NULL,
472 x_Calling_Mode IN VARCHAR2,
473 x_Override_Status IN VARCHAR2 DEFAULT NULL,
474 x_Line_Number_To_Delete IN NUMBER DEFAULT NULL,
475 x_Interface_Invoice_Id IN NUMBER DEFAULT NULL,
476 x_Event_Id IN NUMBER DEFAULT NULL,
477 x_All_Error_Messages IN VARCHAR2,
478 x_error_code OUT NOCOPY VARCHAR2,
479 x_Calling_Sequence IN VARCHAR2) RETURN NUMBER
480
481 IS
482 l_debug_info VARCHAR2(240);
483 l_curr_calling_sequence VARCHAR2(4000);
484
485 BEGIN
486
487 l_curr_calling_sequence := 'AP_ETAX_PKG.callETax<-' ||
488 x_calling_sequence;
489
490 ------------------------------------------------------------------
491 l_debug_info := 'calling the calling_etax() api';
492 ------------------------------------------------------------------
493 IF NOT (ap_etax_pkg.calling_etax(
494 p_invoice_id => x_invoice_id,
495 p_line_number => x_line_number,
496 p_calling_mode => x_calling_mode,
497 p_override_status => x_override_status,
498 p_line_number_to_delete => x_line_number_to_delete,
499 p_Interface_Invoice_Id => x_interface_invoice_id,
500 P_Event_Id => x_event_id,
501 p_all_error_messages => x_all_error_messages,
502 p_error_code => x_error_code,
503 p_calling_sequence => l_curr_calling_sequence)) THEN
504 RETURN 1; -- failed
505 ELSE
506 RETURN 0; -- successful
507 END IF;
508
509 -- make sure we commit here
510 commit;
511
512 EXCEPTION
513 WHEN OTHERS THEN
514 /*
515 IF (SQLCODE <> -20001) THEN
516 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
517 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
518 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
519 FND_MESSAGE.SET_TOKEN('PARAMETERS',
520 ' x_Invoice_Id = '||x_Invoice_Id||
521 ' x_Calling_Mode = '||x_Calling_Mode||
522 ' x_Override_Status = '||x_Override_Status||
523 ' x_Line_Number_To_Delete = '||x_Line_Number_To_Delete||
524 ' x_All_Error_Messages = '||x_All_Error_Messages||
525 ' x_Error_Code = '||x_Error_Code||
526 ' x_Calling_Sequence = '||x_Calling_Sequence);
527 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
528
529 END IF;
530 APP_EXCEPTION.RAISE_EXCEPTION;
531 */
532
533 RETURN 1;
534
535 END callETax;
536
537 END AP_ETAX_PKG;
[Home] [Help]