1 PACKAGE BODY ZX_TDS_CALC_PKG AS
2 /* $Header: zxdicalctaxspkgb.pls 120.37.12020000.2 2012/07/23 12:52:19 srajapar noship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 ------------------------------------------------------------------------------
11 -- PUBLIC PROCEDURE
12 -- GET_TAX_AMOUNT
13 --
14 -- DESCRIPTION
15 -- This is the main procedure in this package.
16 -- This procedure is used to calculate tax for for all tax lines
17 -- belonging to a transaction line (indicated by p_begin_index and p_end_index)
18 ------------------------------------------------------------------------------
19
20 PROCEDURE GET_TAX_AMOUNT (
21 p_begin_index IN number,
22 p_end_index IN number,
23 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
24 p_structure_name IN VARCHAR2,
25 p_structure_index IN BINARY_INTEGER,
26 p_return_status OUT NOCOPY VARCHAR2,
27 p_error_buffer OUT NOCOPY VARCHAR2)
28
29 IS
30
31 l_Tax_Calc_Rule_Flag zx_taxes_b.Tax_Calc_Rule_Flag%type;
32 l_def_formula varchar(30);
33 l_formula_code varchar2(30);
34 l_formula_id number;
35 l_counter number;
36 l_line_amt number;
37 l_discount_amt number;
38 i number;
39 l_sum_basiscoef number;
40 l_sum_constcoef number;
41 l_tax_id number;
42 l_d_tax_id number;
43 l_zx_result_rec ZX_PROCESS_RESULTS%ROWTYPE;
44 l_perc_discount number;
45 l_adjusted_line_amt number;
46 l_compounding_tax varchar2(30);
47 l_compounding_tax_regime_code varchar2(30);
48 l_Compounding_Type_Code varchar2(30);
49 l_compounding_factor number;
50 l_tax_date date;
51
52 l_tax_determine_date date;
53 l_tax_rec ZX_TDS_UTILITIES_PKG.ZX_TAX_INFO_CACHE_REC;
54
55 TYPE tax_amt_tbl_type IS TABLE OF ZX_LINES.TAX_amt%TYPE INDEX BY BINARY_INTEGER;
56
57 tax_amt_tbl tax_amt_tbl_type;
58
59 cursor getFormulaInfoD(c_formula_id in number ) is
60 select compounding_tax,
61 compounding_tax_regime_code,
62 Compounding_Type_Code
63 from zx_formula_details
64 where formula_id = c_formula_id;
65
66 /* Bug#5395227 -- use cache structure
67
68 cursor getTaxId(c_tax varchar2,
69 c_tax_regime_code varchar2) is
70 select tax_id from ZX_SCO_TAXES
71 where tax = c_tax
72 and tax_regime_code = c_tax_regime_code;
73 */
74
75 cursor getFormulaId(c_formula_code varchar2) is
76 select formula_id from ZX_SCO_FORMULA
77 where formula_code = c_formula_code;
78
79
80 BEGIN
81 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
82
83 IF (g_level_procedure >= g_current_runtime_level ) THEN
84 FND_LOG.STRING(g_level_procedure,
85 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT.BEGIN',
86 'ZX_TDS_CALC_PKG: GET_TAX_AMOUNT (+)');
87 END IF;
88
89 p_return_status:= FND_API.G_RET_STS_SUCCESS;
90 p_error_buffer := NULL;
91
92 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(
93 p_structure_index) IS NOT NULL THEN
94
95 IF (g_level_procedure >= g_current_runtime_level ) THEN
96
97 FND_LOG.STRING(g_level_procedure,
98 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT.END',
99 'ZX_TDS_CALC_PKG.GET_TAX_AMOUNT (-)'||' skip processing adjustment and credit memo');
100 END IF;
101 RETURN;
102 END IF;
103
104 IF p_begin_index IS NULL OR p_end_index IS NULL THEN
105 p_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
106
107 IF (g_level_statement >= g_current_runtime_level ) THEN
108 FND_LOG.STRING(g_level_statement,
109 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT.END',
110 'ZX_TDS_CALC_PKG.GET_TAX_AMOUNT (-)'||' begin index or end index is null');
111 END IF;
112 RETURN;
113 END IF;
114
115 FOR i IN p_begin_index..p_end_index
116 LOOP
117 IF (g_level_statement >= g_current_runtime_level) THEN
118 FND_LOG.STRING(g_level_statement,
119 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
120 'Calculating Tax for Tax Line '||TO_CHAR(i));
121 END IF;
122
123 IF NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).cancel_flag,'N') = 'Y'
124 THEN
125 IF (g_level_statement >= g_current_runtime_level) THEN
126 FND_LOG.STRING(g_level_statement,
127 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
128 'Canceled Tax Line. Unrounded_Tax_amt = '||
129 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt);
130 END IF;
131 NULL;
132
133 ELSIF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'REFERENCE' AND
134 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt = 0 AND
135 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt = 0 AND
136 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).manually_entered_flag = 'Y' AND
137 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).freeze_until_overridden_flag ='Y'
138 THEN
139
140 IF (g_level_statement >= g_current_runtime_level) THEN
141 FND_LOG.STRING(g_level_statement,
142 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
143 'Zero Amount Reference Tax Line. Unrounded_Tax_amt = '||
144 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt);
145 END IF;
146 NULL;
147
148 ELSE
149
150 l_formula_code:= NULL;
151 l_formula_id:= NULL;
152 p_return_status:= FND_API.G_RET_STS_SUCCESS;
153
154 l_tax_id :=ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_id;
155 l_tax_date :=ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_date;
156
157 l_tax_determine_date := ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date;
158
159 l_Tax_Calc_Rule_Flag:=
160 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).Tax_Calc_Rule_Flag;
161 l_def_formula:=
162 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).DEF_TAX_CALC_FORMULA;
163
164 -- Do not calculate taxes for those lines which have last_manual_entry =
165 -- 'TAX_AMOUNT'. Tax on these lines has been overridden by the user and we
166 -- should not change the user overridden amts
167
168 -- Do not calculate taxes for those lines where tax_provider_id is not null
169 -- Provider services will calculate taxes for these lines
170
171 -- Do not calculate tax for those lines which are copied from reference
172 -- document and which also have Freeze_Until_Overridden_Flag = Y. These are
173 -- the manual tax lines on reference document not found applicable on the
174 -- current document.
175
176 CASE
177 WHEN NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry,'N') = 'TAX_AMOUNT'
178 THEN
179 NULL;
180
181 -- bug 5531168: call rule engine when other_doc_source is 'REFERENCE'
182 WHEN NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).COPIED_FROM_OTHER_DOC_FLAG,'N') <> 'Y' OR
183 (NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).COPIED_FROM_OTHER_DOC_FLAG,'N') = 'Y' AND
184 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source, 'X') = 'REFERENCE') OR
185 (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'APPLIED_FROM' AND
186 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag = 'R')
187 THEN
188
189 -- If the tax uses tax calculation rules, call rule based engine
190 -- to determine tax calculation formula.
191
192 IF l_Tax_Calc_Rule_Flag = 'Y' THEN
193
194 ZX_TDS_RULE_BASE_DETM_PVT.rule_base_process(
195 'CALCULATE_TAX_AMOUNTS',
196 p_structure_name,
197 p_structure_index,
198 p_event_class_rec,
199 l_tax_id,
200 NULL,
201 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date,
202 NULL,
203 NULL,
204 l_zx_result_rec,
205 p_return_status,
206 p_error_buffer);
207
208 -- If rule based engine is not successful,use the default
209 -- tax calculation formula for that tax
210
211 if l_zx_result_rec.alphanumeric_result is not null then
212 l_formula_code:= l_zx_result_rec.alphanumeric_result;
213 IF (g_level_statement >= g_current_runtime_level ) THEN
214
215 FND_LOG.STRING(g_level_statement,
216 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
217 'formula code from rule ' || l_formula_code);
218 END IF;
219 else
220 l_formula_code:= l_def_formula;
221 IF (g_level_statement >= g_current_runtime_level ) THEN
222
223 FND_LOG.STRING(g_level_statement,
224 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
225 'default formula code ' || l_formula_code);
226 END IF;
227 end if;
228
229 l_formula_id:= l_zx_result_rec.numeric_result;
230 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).calc_result_id:=
231 l_zx_result_rec.result_id;
232 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).legal_message_calc:=
233 ZX_TDS_CALC_SERVICES_PUB_PKG.get_rep_code_id(l_zx_result_rec.result_id,
234 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_date);
235 ELSIF l_def_formula is not null and l_def_formula <> 'STANDARD_TC' THEN
236
237 l_formula_code:= l_def_formula;
238 IF (g_level_statement >= g_current_runtime_level ) THEN
239
240 FND_LOG.STRING(g_level_statement,
241 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
242 'default formula code ' || l_formula_code);
243 END IF;
244
245 open getFormulaId(l_formula_code);
246 fetch getFormulaId into l_formula_id;
247 close getFormulaId;
248
249 ELSE
250 -- The tax calculation formula STANDARD_TC is seeded and in this case,
251 -- the tax calculation is done as: Taxablable amt * Tax Rate
252 l_formula_code:= 'STANDARD_TC';
253
254
255 END IF;
256
257 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_calculation_formula:= l_formula_code;
258
259 IF l_formula_code <> 'STANDARD_TC' AND l_formula_id is not NULL THEN
260
261 IF (g_level_statement >= g_current_runtime_level ) THEN
262 FND_LOG.STRING(g_level_statement,
263 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
264 'Get compounding information...');
265 END IF;
266
267 open getFormulaInfoD(l_formula_id);
268 loop
269
270 fetch getFormulaInfoD into l_compounding_tax,
271 l_compounding_tax_regime_code,
272 l_Compounding_Type_Code;
273
274 exit when getFormulaInfoD%notfound;
275
276 -- Bug#5395227- use cache structure
277 -- open getTaxId(l_compounding_tax,l_compounding_tax_regime_code);
278 -- fetch getTaxId into l_d_tax_id;
279 -- close getTaxId;
280
281 -- Bug#5395227- replace getTaxId by the code below
282 --
283 -- init tax record for each new tax regime and tax
284 --
285 l_tax_rec := NULL;
286 l_d_tax_id := NULL;
287
288 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
289 l_compounding_tax_regime_code,
290 l_compounding_tax,
291 l_tax_determine_date,
292 l_tax_rec,
293 p_return_status,
294 p_error_buffer);
295
296 if p_return_status = FND_API.G_RET_STS_SUCCESS then
297 l_d_tax_id := l_tax_rec.tax_id;
298 end if;
299
300 if tax_amt_tbl.exists(l_d_tax_id) then
301
302 if nvl(l_Compounding_Type_Code,'ADD') = 'ADD' then
303
304 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt:=
305 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt +
306 tax_amt_tbl(l_d_tax_id);
307
308 else
309 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt:=
310 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt -
311 tax_amt_tbl(l_d_tax_id);
312
313 end if;
314
315 else
316
317 p_return_status:= FND_API.G_RET_STS_ERROR;
318
319 IF (g_level_statement >= g_current_runtime_level ) THEN
320
321 FND_LOG.STRING(g_level_statement,
322 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
323 'Tax amount is not calculated for tax_id ' || l_d_tax_id );
324 END IF;
325
326 FND_MESSAGE.SET_NAME('ZX','ZX_COMPND_TAX_NOT_FOUND');
327 FND_MESSAGE.SET_TOKEN('TAX',l_compounding_tax);
328 FND_MESSAGE.SET_TOKEN('FORMULA_CODE',l_formula_code);
329
330 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
331 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id;
332 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
333 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type;
334
335 ZX_API_PUB.add_msg(ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
336 EXIT;
337 end if;
338 end loop;
339
340 close getFormulaInfoD;
341
342 END IF; -- l_formula_id
343
344 WHEN ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).OTHER_DOC_SOURCE = 'APPLIED_FROM' AND
345 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag = 'P'
346 THEN
347
348 -- Proration Scenarios:
349 -- 1. XML Invoices with control total (for all taxes within the document):
350 -- Wrapper/Applicability process calculates tax amount.
351 --
352 -- 2. Transaction line with Adjusted to/Applied from information:
353 -- Tax calculation service calculate the tax amount
354 -- eg. payables credit memo, adjusted to a payables invoice
355 -- payables invoice, applied from a prepayment
356
357 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_amt <> 0 THEN
358
359 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt:=
360 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt,
361 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_tax_amt *
362 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_amt /
363 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_amt ));
364
365 ELSE -- other_doc_line_amt = 0 OR IS NULL
366 -- copy unrounded_tax_amt from reference document,
367 --
368 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt :=
369 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt,
370 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_tax_amt);
371
372 END IF; -- other_doc_line_amt <> 0
373
374 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_amt:= NULL;
375
376 ELSE
377 IF (g_level_statement >= g_current_runtime_level ) THEN
378 FND_LOG.STRING(g_level_statement,
379 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
380 'ELSE CASE ' );
381 END IF;
382 NULL;
383
384 END CASE; -- Delete_Flag ...
385
386 -- In other cases such as when formula_code is STANDARD_TC, tax is
387 -- Taxable_amt multiplied by tax rate, which is already populated
388 -- into ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl during the taxable basis determination
389 -- process.
390
391 tax_amt_tbl(l_tax_id):=
392 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt;
393
394
395 IF (g_level_statement >= g_current_runtime_level ) THEN
396 FND_LOG.STRING(g_level_statement,
397 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
398 ' Unrounded_Tax_Amt for Tax ' || l_tax_id || ' is '||
399 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt);
400 END IF;
401
402 END IF;
403
404 END LOOP;
405
406 IF (g_level_procedure >= g_current_runtime_level ) THEN
407 FND_LOG.STRING(g_level_procedure,
408 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT.END',
409 'ZX_TDS_CALC_PKG: GET_TAX_AMOUNT (-)');
410 END IF;
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
416
417 IF (g_level_unexpected >= g_current_runtime_level ) THEN
418 FND_LOG.STRING(g_level_unexpected,
419 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
420 p_error_buffer);
421 END IF;
422
423 END GET_TAX_AMOUNT;
424
425 END ZX_TDS_CALC_PKG;
[Home] [Help]