1 PACKAGE BODY ZX_TDS_CALC_PKG AS
2 /* $Header: zxdicalctaxspkgb.pls 120.33.12010000.2 2008/09/11 18:16:25 appradha ship $ */
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_ERROR;
106
107 IF (g_level_statement >= g_current_runtime_level ) THEN
108
109 FND_LOG.STRING(g_level_statement,
110 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT.END',
111 'ZX_TDS_CALC_PKG.GET_TAX_AMOUNT (-)'||' begin index or end index is null');
112 END IF;
113 RETURN;
114
115 END IF;
116
117 FOR i IN p_begin_index..p_end_index
118 LOOP
119
120 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'REFERENCE' AND
121 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt = 0 AND
122 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt = 0 AND
123 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).manually_entered_flag = 'Y' AND
124 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).freeze_until_overridden_flag ='Y'
125 THEN
126
127 NULL;
128
129 ELSE
130
131 l_formula_code:= NULL;
132 l_formula_id:= NULL;
133 p_return_status:= FND_API.G_RET_STS_SUCCESS;
134
135 l_tax_id :=ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_id;
136 l_tax_date :=ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_date;
137
138 l_tax_determine_date := ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date;
139
140 l_Tax_Calc_Rule_Flag:=
141 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).Tax_Calc_Rule_Flag;
142 l_def_formula:=
143 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).DEF_TAX_CALC_FORMULA;
144
145 -- Do not calculate taxes for those lines which have last_manual_entry =
146 -- 'TAX_AMOUNT'. Tax on these lines has been overridden by the user and we
147 -- should not change the user overridden amts
148
149 -- Do not calculate taxes for those lines where tax_provider_id is not null
150 -- Provider services will calculate taxes for these lines
151
152 -- Do not calculate tax for those lines which are copied from reference
153 -- document and which also have Freeze_Until_Overridden_Flag = Y. These are
154 -- the manual tax lines on reference document not found applicable on the
155 -- current document.
156
157 CASE
158 WHEN NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry,'N') = 'TAX_AMOUNT'
159 THEN
160 NULL;
161
162 -- bug 5531168: call rule engine when other_doc_source is 'REFERENCE'
163 WHEN NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).COPIED_FROM_OTHER_DOC_FLAG,'N') <> 'Y' OR
164 (NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).COPIED_FROM_OTHER_DOC_FLAG,'N') = 'Y' AND
165 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source, 'X') = 'REFERENCE') OR
166 (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'APPLIED_FROM' AND
167 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag = 'R')
168 THEN
169
170 -- If the tax uses tax calculation rules, call rule based engine
171 -- to determine tax calculation formula.
172
173 IF l_Tax_Calc_Rule_Flag = 'Y' THEN
174
175 ZX_TDS_RULE_BASE_DETM_PVT.rule_base_process(
176 'CALCULATE_TAX_AMOUNTS',
177 p_structure_name,
178 p_structure_index,
179 p_event_class_rec,
180 l_tax_id,
181 NULL,
182 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date,
183 NULL,
184 NULL,
185 l_zx_result_rec,
186 p_return_status,
187 p_error_buffer);
188
189 -- If rule based engine is not successful,use the default
190 -- tax calculation formula for that tax
191
192 if l_zx_result_rec.alphanumeric_result is not null then
193 l_formula_code:= l_zx_result_rec.alphanumeric_result;
194 IF (g_level_statement >= g_current_runtime_level ) THEN
195
196 FND_LOG.STRING(g_level_statement,
197 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
198 'formula code from rule ' || l_formula_code);
199 END IF;
200 else
201 l_formula_code:= l_def_formula;
202 IF (g_level_statement >= g_current_runtime_level ) THEN
203
204 FND_LOG.STRING(g_level_statement,
205 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
206 'default formula code ' || l_formula_code);
207 END IF;
208 end if;
209
210 l_formula_id:= l_zx_result_rec.numeric_result;
211 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).calc_result_id:=
212 l_zx_result_rec.result_id;
213
214 ELSIF l_def_formula is not null and l_def_formula <> 'STANDARD_TC' THEN
215
216 l_formula_code:= l_def_formula;
217 IF (g_level_statement >= g_current_runtime_level ) THEN
218
219 FND_LOG.STRING(g_level_statement,
220 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
221 'default formula code ' || l_formula_code);
222 END IF;
223
224 open getFormulaId(l_formula_code);
225 fetch getFormulaId into l_formula_id;
226 close getFormulaId;
227
228 ELSE
229 -- The tax calculation formula STANDARD_TC is seeded and in this case,
230 -- the tax calculation is done as: Taxablable amt * Tax Rate
231 l_formula_code:= 'STANDARD_TC';
232
233
234 END IF;
235
236 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_calculation_formula:= l_formula_code;
237
238 IF l_formula_code <> 'STANDARD_TC' AND l_formula_id is not NULL THEN
239
240 IF (g_level_statement >= g_current_runtime_level ) THEN
241 FND_LOG.STRING(g_level_statement,
242 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
243 'Get compounding information...');
244 END IF;
245
246 open getFormulaInfoD(l_formula_id);
247 loop
248
249 fetch getFormulaInfoD into l_compounding_tax,
250 l_compounding_tax_regime_code,
251 l_Compounding_Type_Code;
252
253 exit when getFormulaInfoD%notfound;
254
255 -- Bug#5395227- use cache structure
256 -- open getTaxId(l_compounding_tax,l_compounding_tax_regime_code);
257 -- fetch getTaxId into l_d_tax_id;
258 -- close getTaxId;
259
260 -- Bug#5395227- replace getTaxId by the code below
261 --
262 -- init tax record for each new tax regime and tax
263 --
264 l_tax_rec := NULL;
265 l_d_tax_id := NULL;
266
267 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
268 l_compounding_tax_regime_code,
269 l_compounding_tax,
270 l_tax_determine_date,
271 l_tax_rec,
272 p_return_status,
273 p_error_buffer);
274
275 if p_return_status = FND_API.G_RET_STS_SUCCESS then
276 l_d_tax_id := l_tax_rec.tax_id;
277 end if;
278
279 if tax_amt_tbl.exists(l_d_tax_id) then
280
281 if nvl(l_Compounding_Type_Code,'ADD') = 'ADD' then
282
283 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt:=
284 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt +
285 tax_amt_tbl(l_d_tax_id);
286
287 else
288 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt:=
289 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt -
290 tax_amt_tbl(l_d_tax_id);
291
292 end if;
293
294 else
295
296 p_return_status:= FND_API.G_RET_STS_ERROR;
297
298 IF (g_level_statement >= g_current_runtime_level ) THEN
299
300 FND_LOG.STRING(g_level_statement,
301 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
302 'Tax amount is not calculated for tax_id ' || l_d_tax_id );
303 END IF;
304 EXIT;
305
306 end if;
307
308 end loop;
309
310 close getFormulaInfoD;
311
312 END IF; -- l_formula_id
313
314 WHEN ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).OTHER_DOC_SOURCE = 'APPLIED_FROM' AND
315 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag = 'P'
316 THEN
317
318 -- Proration Scenarios:
319 -- 1. XML Invoices with control total (for all taxes within the document):
320 -- Wrapper/Applicability process calculates tax amount.
321 --
322 -- 2. Transaction line with Adjusted to/Applied from information:
323 -- Tax calculation service calculate the tax amount
324 -- eg. payables credit memo, adjusted to a payables invoice
325 -- payables invoice, applied from a prepayment
326
327 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_amt <> 0 THEN
328
329 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt:=
330 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_tax_amt *
331 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_amt /
332 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_amt );
333
334 ELSE -- other_doc_line_amt = 0 OR IS NULL
335 -- copy unrounded_tax_amt from reference document,
336 --
337 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt :=
338 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_tax_amt;
339
340 END IF; -- other_doc_line_amt <> 0
341
342 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_amt:= NULL;
343
344 ELSE
345 IF (g_level_statement >= g_current_runtime_level ) THEN
346 FND_LOG.STRING(g_level_statement,
347 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
348 'ELSE CASE ' );
349 END IF;
350 NULL;
351
352 END CASE; -- Delete_Flag ...
353
354 -- In other cases such as when formula_code is STANDARD_TC, tax is
355 -- Taxable_amt multiplied by tax rate, which is already populated
356 -- into ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl during the taxable basis determination
357 -- process.
358
359 tax_amt_tbl(l_tax_id):=
360 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt;
361
362
363 IF (g_level_statement >= g_current_runtime_level ) THEN
364 FND_LOG.STRING(g_level_statement,
365 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
366 ' Unrounded Tax amt for tax ' || l_tax_id || ' is '||
367 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt);
368 END IF;
369
370 END IF;
371
372 END LOOP;
373
374 IF (g_level_procedure >= g_current_runtime_level ) THEN
375 FND_LOG.STRING(g_level_procedure,
376 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT.END',
377 'ZX_TDS_CALC_PKG: GET_TAX_AMOUNT (-)');
378 END IF;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
384
385 IF (g_level_unexpected >= g_current_runtime_level ) THEN
386 FND_LOG.STRING(g_level_unexpected,
387 'ZX.PLSQL.ZX_TDS_CALC_PKG.GET_TAX_AMOUNT',
388 p_error_buffer);
389 END IF;
390
391 END GET_TAX_AMOUNT;
392
393 END ZX_TDS_CALC_PKG;
394
395
[Home] [Help]