1 PACKAGE BODY ZX_TDS_TAXABLE_BASIS_DETM_PKG AS
2 /* $Header: zxditxbsisdtpkgb.pls 120.80.12010000.3 2008/11/26 02:07:28 tsen 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_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8 g_level_error CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
9
10 PROCEDURE populate_inclusive_tax_flag (
11 p_tax_line_index IN NUMBER,
12 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
13 p_structure_name IN VARCHAR2,
14 p_structure_index IN BINARY_INTEGER,
15 p_return_status OUT NOCOPY VARCHAR2,
16 p_error_buffer OUT NOCOPY VARCHAR2);
17
18
19 ------------------------------------------------------------------------------
20 -- PUBLIC PROCEDURE
21 -- get_taxable_basis
22 --
23 -- DESCRIPTION
24 -- This is the main procedure in this package.
25 -- This procedure is used to calculate taxable basis for for all tax lines
26 -- belonging to a transaction line (indicated by p_begin_index and p_end_index)
27 ------------------------------------------------------------------------------
28
29 PROCEDURE Get_taxable_basis (
30 p_begin_index IN NUMBER,
31 p_end_index IN NUMBER,
32 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
33 p_structure_name IN VARCHAR2,
34 p_structure_index IN BINARY_INTEGER,
35 p_return_status OUT NOCOPY VARCHAR2,
36 p_error_buffer OUT NOCOPY VARCHAR2)
37
38 IS
39
40 l_Taxable_Basis_Rule_Flag ZX_TAXES_B.Taxable_Basis_Rule_Flag%type;
41 l_def_formula varchar(30);
42 l_formula_from_rate VARCHAR(30);
43 l_formula_code varchar2(30);
44 l_formula_id number;
45 l_line_amt number;
46 l_discount_amt number;
47 -- i number;
48 l_sum_basiscoef number;
49 l_sum_constcoef number;
50 l_tax_id number;
51 l_tax_rate_id zx_rates_b.tax_rate_id%TYPE;
52 l_compounding_tax_id number;
53 l_zx_result_rec ZX_PROCESS_RESULTS%ROWTYPE;
54 l_perc_discount number;
55 l_common_comp_base number;
56 l_compounding_tax varchar2(30);
57 l_cpdg_tax_regime_code varchar2(30);
58 l_Compounding_Type_Code varchar2(30);
59 l_compounding_factor number;
60 l_tax_date date;
61 l_Formula_Type_Code ZX_FORMULA_B.Formula_Type_Code%type;
62 l_Taxable_Basis_Type_Code ZX_FORMULA_B.Taxable_Basis_Type_Code%type;
63 l_base_rate_modifier ZX_FORMULA_B.base_rate_modifier%type;
64 l_Cash_Discount_Appl_Flag ZX_FORMULA_B.Cash_Discount_Appl_Flag%type;
65 l_Volume_Discount_Appl_Flag ZX_FORMULA_B.Volume_Discount_Appl_Flag%type;
66 l_Trading_Discount_Appl_Flag ZX_FORMULA_B.Trading_Discount_Appl_Flag%type;
67 l_Transfer_Charge_Appl_Flag ZX_FORMULA_B.Transfer_Charge_Appl_Flag%type;
68 l_TRANS_CHRG_APPL_FLG ZX_FORMULA_B.Transport_Charge_Appl_Flag%type;
69 l_Insurance_Charge_Appl_Flag ZX_FORMULA_B.Insurance_Charge_Appl_Flag%type;
70 l_Other_Charge_Appl_Flag ZX_FORMULA_B.Other_Charge_Appl_Flag%type;
71 l_enforce_compounding_flag ZX_FORMULA_DETAILS.ENFORCE_COMPOUNDING_FLAG%TYPE;
72
73 l_allow_adhoc_tax_rate_flag VARCHAR2(1);
74 l_adj_for_adhoc_amt_code ZX_RATES_B.ADJ_FOR_ADHOC_AMT_CODE%TYPE;
75
76 l_tax_determine_date date;
77 l_tax_rec ZX_TDS_UTILITIES_PKG.ZX_TAX_INFO_CACHE_REC;
78 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
79
80 TYPE parameter_rec IS RECORD (
81 tax_id number,
82 formula_code varchar(30),
83 incl_indicator number,
84 weird_indicator number,
85 composite_incl_indicator number,
86 base_rate_modifier number,
87 discount number,
88 tax_rate number,
89 tax_amt number,
90 taxable_amt number,
91 compounding_flg varchar(1),
92 compounding_dep_flg varchar(1),
93 basiscoef number,
94 constcoef number,
95 Taxable_Basis_Type_Code ZX_FORMULA_B.Taxable_Basis_Type_Code%type,
96 overrideconst number );
97
98 TYPE parameter_tbl_type IS TABLE OF parameter_rec INDEX BY BINARY_INTEGER;
99
100 parameter_tbl parameter_tbl_type;
101
102
103 cursor getFormulaInfoH(c_formula_code in varchar2,
104 c_tax_date in date ) is
105
106 select FORMULA_ID,
107 Formula_Type_Code,
108 Taxable_Basis_Type_Code,
109 BASE_RATE_MODIFIER,
110 Cash_Discount_Appl_Flag,
111 Volume_Discount_Appl_Flag,
112 Trading_Discount_Appl_Flag,
113 Transfer_Charge_Appl_Flag,
114 Transport_Charge_Appl_Flag,
115 Insurance_Charge_Appl_Flag,
116 Other_Charge_Appl_Flag
117 from ZX_SCO_FORMULA
118 where formula_code = c_formula_code
119 and effective_from <= c_tax_date
120 and ( effective_to >= c_tax_date or
121 effective_to is null )
122 and Enabled_Flag = 'Y';
123
124
125 cursor getFormulaInfoD(c_formula_id in number ) is
126 select compounding_tax,
127 compounding_tax_regime_code,
128 Compounding_Type_Code,
129 enforce_compounding_flag
130 from ZX_FORMULA_DETAILS
131 where formula_id = c_formula_id;
132
133 /* Bug#5395227 -- use cache structure
134 cursor getTaxId(c_tax varchar2,
135 c_tax_regime_code varchar2) is
136 select tax_id from ZX_SCO_TAXES
137 where tax = c_tax
138 and tax_regime_code = c_tax_regime_code;
139 */
140
141 cursor getAdhocInfo( c_tax_rate_id IN NUMBER) is
142 select NVL(ADJ_FOR_ADHOC_AMT_CODE, 'TAXABLE_BASIS'), NVL(ALLOW_ADHOC_TAX_RATE_FLAG, 'N')
143 from zx_rates_b
144 where TAX_RATE_ID = c_tax_rate_id;
145
146 CURSOR get_formula_code_from_rate_csr(
147 c_tax_rate_id zx_rates_b.tax_rate_id%TYPE) IS
148 SELECT taxable_basis_formula_code
149 FROM zx_rates_b
150 WHERE tax_rate_id = c_tax_rate_id;
151
152 BEGIN
153
154 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
155
156 IF (g_level_procedure >= g_current_runtime_level ) THEN
157 FND_LOG.STRING(g_level_procedure,
158 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.BEGIN',
159 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (+)');
160 END IF;
161
162 p_return_status:= FND_API.G_RET_STS_SUCCESS;
163
164 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(
165 p_structure_index) IS NOT NULL THEN
166
167 IF (g_level_procedure >= g_current_runtime_level ) THEN
168
169 FND_LOG.STRING(g_level_procedure,
170 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
171 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)'||' skip processing for credit memo');
172 END IF;
173 RETURN;
174 END IF;
175
176 l_sum_basiscoef:= 1;
177 l_sum_constcoef:= 0;
178
179 -- Bug#5520167- get line amt from trx line
180 --l_line_amt:= ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_begin_index).line_amt;
181
182 l_line_amt:= ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_amt( p_structure_index);
183
184
185 IF p_begin_index is null or p_end_index is null THEN
186
187 p_return_status:= FND_API.G_RET_STS_ERROR;
188 IF (g_level_procedure >= g_current_runtime_level ) THEN
189
190 FND_LOG.STRING(g_level_procedure,
191 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
192 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)'||' Error: begin index or end index is null');
193 END IF;
194 RETURN;
195
196 END IF;
197
198 For i IN p_begin_index..p_end_index
199 Loop
200
201 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'REFERENCE' AND
202 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt = 0 AND
203 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt = 0 AND
204 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).manually_entered_flag = 'Y' AND
205 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).freeze_until_overridden_flag ='Y'
206 THEN
207
208 NULL;
209
210 ELSE
211
212
213 l_tax_id :=ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_id;
214 l_tax_rate_id :=
215 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_id;
216
217
218 l_tax_date :=ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_date;
219
220 l_tax_determine_date := ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date;
221
222 l_Taxable_Basis_Rule_Flag:=
223 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).Taxable_Basis_Rule_Flag;
224 l_def_formula:=
225 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).def_taxable_basis_formula;
226
227 -- Do not re-determine tax_amt_included_flag if
228 -- bug 5391331: tax_amt_included_flag is overridden
229 -- bug 5391084: manual tax line
230 --
231 -- changed the following condition out for bug 5525890 and bug 5525816
232 -- Commented the code for Bug 7438875 to populate inclusive flag even if last_manual_entry is set to TAX_AMOUNT
233 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
234 i).orig_tax_amt_included_flag IS NULL AND
235 /*(
236 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
237 i).last_manual_entry, 'X') <> 'TAX_AMOUNT'
238 -- bugfix 5619762
239 OR
240 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ctrl_total_hdr_tx_amt(p_structure_index) is not null
241 OR
242 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ctrl_total_line_tx_amt(p_structure_index) is not null
243 ) AND*/
244 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
245 i).manually_entered_flag, 'N') ='N'
246 THEN
247 -- populate inclusive_tax_flag by calling populate_inclusive_tax_flag
248 --
249 populate_inclusive_tax_flag (
250 p_tax_line_index => i,
251 p_event_class_rec => p_event_class_rec,
252 p_structure_name => p_structure_name,
253 p_structure_index => p_structure_index,
254 p_return_status => p_return_status,
255 p_error_buffer => p_error_buffer);
256
257 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
258 IF (g_level_statement>= g_current_runtime_level ) THEN
259 FND_LOG.STRING(g_level_statement,
260 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
261 'Incorrect return_status after calling ' ||
262 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag');
263 FND_LOG.STRING(g_level_statement,
264 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
265 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)');
266 END IF;
267 RETURN;
268 END IF;
269 END IF;
270
271 -- Do not calculate taxes for those lines where tax_provider_id is not null
272 -- Provider services will calculate taxes for these lines
273
274 -- Do not calculate tax for those lines which are copied from reference
275 -- document and which also have Freeze_Until_Overridden_Flag = Y. These are
276 -- the manual tax lines on reference document not found applicable on the
277 -- current document. Hiwever, if Freeze_Until_Overridden_Flag is 'Y' and the
278 -- tax event type is OVERRIDE, then taxable amount should be calculated.
279
280 -- In case of override, only process those tax lines which need
281 -- to be recalculated. i.e.in case of inclusive or compounded taxes only,
282 -- we should recalculate all taxes, otherwise only recalculate taxes which
283 -- have recalculate_tax_flg ='Y'
284
285 CASE
286 WHEN (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).recalc_required_flag <> 'Y' AND
287 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX') OR
288 (NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry, 'X') = 'TAX_AMOUNT') OR
289 (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'APPLIED_FROM' AND
290 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag = 'P')
291 THEN
292
293 -- RECALC_REQUIRED_FLAG will be populated by tax lines Determination
294 -- table handler when the user overrides one or more tax lines. (When the
295 -- line being overridden is inclusive or used to compound other taxes,
296 -- then this flag will be set to 'Y' for all the tax lines belonging to
297 -- the current transaction line) If the value of RACALC_REQUIRED_FLAG = 'N'
298 -- then skip the process and only perform population of relevant Tax Rate
299 -- Determination columns into detail tax lines structure.
300
301 -- NULL;
302
303 -- Bug 3560223: populate parameter_tbl for compounding tax and
304 -- inclusive tax
305 --
306 parameter_tbl(l_tax_id).tax_id := l_tax_id;
307 parameter_tbl(l_tax_id).tax_rate :=
308 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate;
309 parameter_tbl(l_tax_id).formula_code :=
310 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).taxable_basis_formula;
311
312 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_amt_included_flag = 'S'
313 THEN
314 parameter_tbl(l_tax_id).weird_indicator:= 1;
315 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
316 i).tax_amt_included_flag := 'Y';
317 ELSE
318 parameter_tbl(l_tax_id).weird_indicator:= 0;
319 END IF;
320
321 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_amt_includes_tax_flag(
322 p_structure_index) = 'I' OR
323 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
324 i).tax_amt_included_flag = 'Y'
325 THEN
326 parameter_tbl(l_tax_id).incl_indicator:= 1;
327 ELSE
328 parameter_tbl(l_tax_id).incl_indicator:= 0;
329 END IF;
330
331
332 parameter_tbl(l_tax_id).composite_incl_indicator :=
333 parameter_tbl(l_tax_id).incl_indicator -
334 parameter_tbl(l_tax_id).weird_indicator;
335
336 parameter_tbl(l_tax_id).basiscoef:= 0;
337 parameter_tbl(l_tax_id).constcoef:= 0;
338 parameter_tbl(l_tax_id).overrideconst:=
339 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt;
340
341 -- l_sum_basiscoef:= l_sum_basiscoef + parameter_tbl(l_tax_id).composite_incl_indicator *
342 -- parameter_tbl(l_tax_id).basiscoef * parameter_tbl(l_tax_id).tax_rate / 100;
343
344 l_sum_constcoef:= l_sum_constcoef + parameter_tbl(l_tax_id).composite_incl_indicator *
345 parameter_tbl(l_tax_id).constcoef * parameter_tbl(l_tax_id).tax_rate /100
346 + parameter_tbl(l_tax_id).composite_incl_indicator *
347 parameter_tbl(l_tax_id).overrideconst;
348
349 IF (g_level_statement >= g_current_runtime_level ) THEN
350 FND_LOG.STRING(g_level_statement,
351 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
352 ' sum_basiscoef: ' || l_sum_basiscoef||
353 ' sum_constcoef: ' || l_sum_constcoef);
354 END IF;
355
356 WHEN ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_provider_id is NOT NULL OR
357 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Delete_Flag = 'Y'
358 THEN
359
360 -- Do not perform taxable basis determination for provider calculated lines. Taxes
361 -- calculated by providers cannot be compounded by taxes calculated by eTax and
362 -- vice versa. Also, do not process tax lines which are marked for deletion.
363
364 NULL;
365
366 WHEN
367 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).OTHER_DOC_SOURCE ='ADJUSTED' AND
368 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code <> 'OVERRIDE_TAX'
369 THEN
370 -- In case when the tax line is copied from 'Applied From' or 'Adjusted' Document,
371 -- Applicability process will copy Tax Regime, Tax, Status, Rate, Place of Supply,
372 -- Reg. Number, Offset tax columns from original document. in this case,
373 -- taxable amount will be a proration and no need to calculate multipliers for
374 -- this tax line.
375
376 NULL;
377
378 --WHEN
379 -- (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX' AND
380 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry = 'TAX_AMOUNT')
381 --THEN
382
383 -- In case of an override event on tax line, where the user has overridden tax amount,
384 -- we need not calculate multiplier for that tax line, the taxable amount will be
385 -- computed as tax amount / tax rate in the next loop.
386
387 --NULL;
388
389 WHEN
390 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).COPIED_FROM_OTHER_DOC_FLAG = 'Y' AND
391 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Manually_Entered_Flag = 'Y' AND
392 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).OTHER_DOC_SOURCE = 'REFERENCE'
393 THEN
394 -- When a manual tax line is copied from reference document, the Tax Regime, Tax, Status, Rate,
395 -- and other columns are copied from manual tax line in reference document as well. in this
396 -- case, the taxable amount will be computed as a percentage of taxable amount on reference
397 -- document. So no need to calculate multiplier for that tax line.
398
399 -- Condition Other_Doc_Source = 'REFERENCE' added as a fix of bug#6891479
400
401 NULL;
402 /* comment out for bug fix 3391186
403 WHEN
404 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Freeze_Until_Overridden_Flag = 'Y' AND
405 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Overridden_Flag <> 'Y'
406 OR
407 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).COPIED_FROM_OTHER_DOC_FLAG = 'Y' )
408 THEN
409 */
410 WHEN
411 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Freeze_Until_Overridden_Flag = 'Y' AND
412 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Overridden_Flag <> 'Y'
413 THEN
414 -- When the transaction is matched to a reference document, and if a tax
415 -- that was applicable on the reference document is not found applicable
416 -- during applicability process, the tax line from the reference document
417 -- is copied, but the tax rate, status, amounts are populated as zero,
418 -- until the user views that tax line and overrides it. So skip taxable basis determination
419 -- in this case.
420
421 NULL;
422
423 WHEN
424 (NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Freeze_Until_Overridden_Flag,'N') <> 'Y' OR
425 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).COPIED_FROM_OTHER_DOC_FLAG,'N') <> 'Y') OR
426 (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'APPLIED_FROM' AND
427 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag = 'R') OR
428 (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX' AND
429 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Recalc_Required_Flag = 'Y' AND
430 -- bug fix 5525890
431 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry,'X') <> 'TAX_AMOUNT' )
432 THEN
433
434 -- Initialize compounding_dep_tax_flag and compounding_tax_miss_flag
435 --
436 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).compounding_dep_tax_flag := 'N';
437 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).compounding_tax_miss_flag := 'N';
438
439 -- In case of manual tax lines or override with last manual entry
440 -- of tax_amount, calculate the taxable amount based on tax amount
441 -- and line amount and not using the evaluation loop.
442
443 -- In case of copied from reference document, prorate the taxable
444 -- amount based on the other_doc_tax_amt and other_doc_line_amt
445
446 -- hence the above cases are excluded from this loop
447
448 IF nvl(l_Taxable_Basis_Rule_Flag,'N') = 'Y' THEN
449
450 ZX_TDS_RULE_BASE_DETM_PVT.rule_base_process(
451 'DET_TAXABLE_BASIS',
452 p_structure_name,
453 p_structure_index,
454 p_event_class_rec,
455 l_tax_id,
456 NULL,
457 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date,
458 NULL,
459 NULL,
460 l_zx_result_rec,
461 p_return_status,
462 p_error_buffer);
463
464 if l_zx_result_rec.alphanumeric_result is not null then
465 l_formula_code:= l_zx_result_rec.alphanumeric_result;
466 IF (g_level_statement >= g_current_runtime_level ) THEN
467 FND_LOG.STRING(g_level_statement,
468 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
469 'Get Formula code from rule_base_process '|| l_formula_code);
470 END IF;
471
472 else
473 /* use cache
474 OPEN get_formula_code_from_rate_csr(l_tax_rate_id);
475 FETCH get_formula_code_from_rate_csr INTO l_formula_from_rate;
476 CLOSE get_formula_code_from_rate_csr;
477 */
478
479 ZX_TDS_UTILITIES_PKG.get_tax_rate_info (
480 p_tax_rate_id => l_tax_rate_id,
481 p_tax_rate_rec => l_tax_rate_rec,
482 p_return_status => p_return_status,
483 p_error_buffer => p_error_buffer);
484
485 l_formula_from_rate := l_tax_rate_rec.taxable_basis_formula_code;
486
487 IF l_formula_from_rate IS NOT NULL THEN
488 l_formula_code:= l_formula_from_rate;
489 IF (g_level_statement >= g_current_runtime_level ) THEN
490 FND_LOG.STRING(g_level_statement,
491 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
492 'Get Formula code from tax rate '||l_formula_code);
493 END IF;
494 ELSE
495 l_formula_code:= l_def_formula;
496 IF (g_level_statement >= g_current_runtime_level ) THEN
497 FND_LOG.STRING(g_level_statement,
498 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
499 'Get Default Formula code from tax '||l_formula_code);
500 END IF;
501 END IF;
502 end if;
503
504 l_formula_id:= l_zx_result_rec.numeric_result;
505 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).basis_result_id:= l_zx_result_rec.result_id;
506
507 ELSE
508
509 /* use cache
510 OPEN get_formula_code_from_rate_csr(l_tax_rate_id);
511 FETCH get_formula_code_from_rate_csr INTO l_formula_from_rate;
512 CLOSE get_formula_code_from_rate_csr;
513 */
514
515 ZX_TDS_UTILITIES_PKG.get_tax_rate_info (
516 p_tax_rate_id => l_tax_rate_id,
517 p_tax_rate_rec => l_tax_rate_rec,
518 p_return_status => p_return_status,
519 p_error_buffer => p_error_buffer);
520
521 l_formula_from_rate := l_tax_rate_rec.taxable_basis_formula_code;
522
523
524
525 IF l_formula_from_rate IS NOT NULL THEN
526 l_formula_code := l_formula_from_rate;
527 IF (g_level_statement >= g_current_runtime_level ) THEN
528 FND_LOG.STRING(g_level_statement,
529 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
530 'Get Formula code from tax rate '|| l_formula_code);
531 END IF;
532 ELSE
533 l_formula_code:= l_def_formula;
534 IF (g_level_statement >= g_current_runtime_level ) THEN
535 FND_LOG.STRING(g_level_statement,
536 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
537 'Get Default Formula code from tax '|| l_formula_code);
538 END IF;
539 END IF;
540 END IF;
541
542 IF l_formula_code IS NULL THEN
543 p_return_status := FND_API.G_RET_STS_ERROR;
544 IF (g_level_error >= g_current_runtime_level ) THEN
545
546 FND_LOG.STRING(g_level_error ,
547 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
548 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)'||' error: can not determine formula code');
549 END IF;
550 RETURN;
551 END IF;
552
553 parameter_tbl(l_tax_id).tax_id:= l_tax_id;
554 parameter_tbl(l_tax_id).tax_rate:= ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate;
555 parameter_tbl(l_tax_id).formula_code:= l_formula_code;
556
557
558 -- This is the end of getting formula code
559
560 if ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Tax_Amt_Included_Flag = 'S' then
561 parameter_tbl(l_tax_id).weird_indicator:= 1;
562 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
563 i).tax_amt_included_flag := 'Y';
564 else
565 parameter_tbl(l_tax_id).weird_indicator:= 0;
566 end if;
567
568 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_amt_includes_tax_flag(
569 p_structure_index) = 'I' OR
570 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
571 i).tax_amt_included_flag = 'Y'
572 THEN
573 parameter_tbl(l_tax_id).incl_indicator:= 1;
574 ELSE
575 parameter_tbl(l_tax_id).incl_indicator:= 0;
576 END IF;
577
578 IF (g_level_statement >= g_current_runtime_level ) THEN
579 FND_LOG.STRING(g_level_statement,
580 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
581 'parameter_tbl( ' || l_tax_id || ' ).incl_indicator:= ' ||
582 parameter_tbl(l_tax_id).incl_indicator||
583 'weird_indicator:= ' ||
584 parameter_tbl(l_tax_id).weird_indicator);
585 END IF;
586
587 parameter_tbl(l_tax_id).composite_incl_indicator:= parameter_tbl(l_tax_id).incl_indicator -
588 parameter_tbl(l_tax_id).weird_indicator;
589
590
591 parameter_tbl(l_tax_id).basiscoef:= 1;
592 parameter_tbl(l_tax_id).constcoef:= 0;
593 parameter_tbl(l_tax_id).overrideconst:= 0;
594
595 IF l_formula_code IN ('STANDARD_TB', 'STANDARD_QUANTITY') THEN
596
597 -- When formula code is STANDARD_TB, the rate type should be PERCENT.
598 -- When formula code is STANDARD_QUANTITY, the rate type should be QUANTITY. Otherwise
599 -- multiplying taxable basis by tax rate will give incorrect result.
600 IF ( l_formula_code = 'STANDARD_TB' AND
601 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_type <> 'PERCENTAGE' )
602 -- add following condition for bug fix 5481559
603 OR ( l_formula_code = 'STANDARD_QUANTITY' AND
604 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_type <> 'QUANTITY' )
605 THEN
606
607 p_return_status:= FND_API.G_RET_STS_ERROR;
608
609 FND_MESSAGE.SET_NAME('ZX','ZX_RATE_FORMULA_MISMATCH');
610 FND_MESSAGE.SET_TOKEN('RATE_TYPE',
611 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_type);
612 FND_MESSAGE.SET_TOKEN('TAX',ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax );
613 FND_MESSAGE.SET_TOKEN('TAXABLE_BASIS_TYPE', NVL(l_Taxable_Basis_Type_Code, 'PERCENTAGE') );
614 FND_MESSAGE.SET_TOKEN('FORMULA_CODE', l_formula_code );
615
616 -- FND_MSG_PUB.Add;
617 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
618 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id;
619 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
620 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type;
621
622 ZX_API_PUB.add_msg(
623 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
624
625 IF (g_level_error >= g_current_runtime_level ) THEN
626 FND_LOG.STRING(g_level_error ,
627 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
628 'Taxable basis type and tax Rate Type do not match ');
629 FND_LOG.STRING(g_level_error,
630 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
631 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)');
632 END IF;
633
634 RETURN;
635
636 END IF;
637
638 -- When formula code is STANDARD_TB then no discounts or charges are
639 -- applicable. Base rate modifier is set to 1
640 parameter_tbl(l_tax_id).discount:= 0;
641 parameter_tbl(l_tax_id).base_rate_modifier:= 1;
642
643 -- if ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry = 'TAX_AMOUNT'
644 -- and ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX' then
645 --
646 -- -- This is a case where user has overridden tax amt on the tax engine
647 -- -- calculated tax. Hence do not apply taxable basis formula again, but
648 -- -- only set the overrideconst to overriddne tax amt.
649 -- parameter_tbl(l_tax_id).basiscoef:= 0;
650 -- parameter_tbl(l_tax_id).constcoef:= 0;
651 -- parameter_tbl(l_tax_id).overrideconst:=
652 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt;
653 --
654 -- else
655 -- parameter_tbl(l_tax_id).basiscoef:= 1;
656 -- parameter_tbl(l_tax_id).constcoef:= 0;
657 -- parameter_tbl(l_tax_id).overrideconst:= 0;
658 -- end if;
659
660 ELSIF l_formula_code <> 'STANDARD_QUANTITY' THEN
661
662 open getFormulaInfoH(l_formula_code,l_tax_date);
663 fetch getFormulaInfoH
664 into l_FORMULA_ID,
665 l_Formula_Type_Code,
666 l_Taxable_Basis_Type_Code,
667 l_BASE_RATE_MODIFIER,
668 l_Cash_Discount_Appl_Flag,
669 l_Volume_Discount_Appl_Flag,
670 l_Trading_Discount_Appl_Flag,
671 l_Transfer_Charge_Appl_Flag,
672 L_TRANS_CHRG_APPL_FLG,
673 l_Insurance_Charge_Appl_Flag,
674 l_Other_Charge_Appl_Flag;
675
676 if getFormulaInfoH%notfound then
677
678 p_return_status:= FND_API.G_RET_STS_ERROR;
679 IF (g_level_error >= g_current_runtime_level ) THEN
680 FND_LOG.STRING(g_level_error,
681 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
682 'Formula Info not found for formula code ' || l_formula_code );
683 FND_LOG.STRING(g_level_error,
684 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
685 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)');
686 END IF;
687
688 end if;
689
690 close getFormulaInfoH;
691
692 IF (g_level_statement >= g_current_runtime_level ) THEN
693
694 FND_LOG.STRING(g_level_statement,
695 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
696 'Taxable Basis Type: '|| l_Taxable_Basis_Type_Code||' base rate modifier: '||l_BASE_RATE_MODIFIER||
697 'Cash_Discount_Appl_Flag: '||l_Cash_Discount_Appl_Flag||' Volume_Discount_Appl_Flag: '||l_Volume_Discount_Appl_Flag||
698 ' Trading_Discount_Appl_Flag: '||l_Trading_Discount_Appl_Flag||' Transfer_Charge_Appl_Flag: '||
699 l_Transfer_Charge_Appl_Flag||' TRANS_CHRG_APPL_FLG: '||L_TRANS_CHRG_APPL_FLG||
700 ' Insurance_Charge_Appl_Flag: '||l_Insurance_Charge_Appl_Flag||' Other_Charge_Appl_Flag: '||l_Other_Charge_Appl_Flag);
701 END IF;
702
703
704 parameter_tbl(l_tax_id).base_rate_modifier := 1 + nvl(l_BASE_RATE_MODIFIER,0)/100;
705 parameter_tbl(l_tax_id).Taxable_Basis_Type_Code := upper(l_Taxable_Basis_Type_Code);
706
707 if parameter_tbl(l_tax_id).Taxable_Basis_Type_Code <> 'ASSESSABLE_VALUE' then
708
709 -- When Taxable Basis Type is not QUANTITY but the rate type is QUANTITY
710 -- then error should be raised, otherwise the tax calculation result will be incorrect.
711 IF ( parameter_tbl(l_tax_id).Taxable_Basis_Type_Code <> 'QUANTITY' AND
712 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_type = 'QUANTITY')
713 OR
714 ( parameter_tbl(l_tax_id).Taxable_Basis_Type_Code = 'QUANTITY' AND
715 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_type <> 'QUANTITY')
716 THEN
717
718 p_return_status:= FND_API.G_RET_STS_ERROR;
719
720 FND_MESSAGE.SET_NAME('ZX','ZX_RATE_FORMULA_MISMATCH');
721 FND_MESSAGE.SET_TOKEN('RATE_TYPE',
722 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_type);
723 FND_MESSAGE.SET_TOKEN('TAX',ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax );
724 FND_MESSAGE.SET_TOKEN('TAXABLE_BASIS_TYPE', parameter_tbl(l_tax_id).Taxable_Basis_Type_Code);
725 FND_MESSAGE.SET_TOKEN('FORMULA_CODE', l_formula_code );
726
727 -- FND_MSG_PUB.Add;
728 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
729 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id;
730 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
731 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type;
732
733 ZX_API_PUB.add_msg(
734 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
735
736 IF (g_level_error >= g_current_runtime_level ) THEN
737 FND_LOG.STRING(g_level_error ,
738 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
739 'Taxable basis type for Formula Code '||l_formula_code||
740 ' and tax Rate Type QUANTITY do not match ');
741 FND_LOG.STRING(g_level_error ,
742 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
743 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)');
744 END IF;
745 RETURN;
746
747 END IF;
748
749
750 if parameter_tbl(l_tax_id).Taxable_Basis_Type_Code = 'PRIOR_TAX' then
751
752 parameter_tbl(l_tax_id).base_rate_modifier := 0;
753
754 end if;
755
756 l_discount_amt:= 0;
757
758
759 if l_Cash_Discount_Appl_Flag = 'Y' then
760 l_discount_amt:= l_discount_amt -
761 ABS(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.cash_discount(p_structure_index));
762
763 end if;
764
765 if l_Volume_Discount_Appl_Flag = 'Y' then
766 l_discount_amt:= l_discount_amt -
767 ABS(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.volume_discount(p_structure_index));
768
769 end if;
770
771 if l_Trading_Discount_Appl_Flag = 'Y' then
772 l_discount_amt:= l_discount_amt -
773 ABS(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trading_discount(p_structure_index));
774
775 end if;
776
777
778 if l_Transfer_Charge_Appl_Flag = 'Y' then
779 l_discount_amt:= l_discount_amt +
780 ABS(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.transfer_charge(p_structure_index));
781
782 end if;
783
784
785 if L_TRANS_CHRG_APPL_FLG = 'Y' then
786 l_discount_amt:= l_discount_amt +
787 ABS(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.transportation_charge(p_structure_index));
788
789 end if;
790
791
792 if l_Insurance_Charge_Appl_Flag = 'Y' then
793 l_discount_amt:= l_discount_amt +
794 ABS(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.insurance_charge(p_structure_index));
795
796 end if;
797
798 if l_Other_Charge_Appl_Flag = 'Y' then
799 l_discount_amt:= l_discount_amt +
800 ABS(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.other_charge(p_structure_index));
801
802 end if;
803
804 IF (g_level_statement >= g_current_runtime_level ) THEN
805 FND_LOG.STRING(g_level_statement,
806 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
807 'Total discount or charge: '||
808 nvl(l_discount_amt,0));
809 END IF;
810
811 parameter_tbl(l_tax_id).discount:= nvl(l_discount_amt,0);
812
813 -- end of discount / charge
814
815
816 -- if ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry = 'TAX_AMOUNT'
817 -- and ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX' then
818
819 -- -- This is a case where user has overridden tax amt on the tax engine
820 -- -- calculated tax. Hence do not apply taxable basis formula again, but
821 -- -- only set the overrideconst to overridden tax amt.
822 -- parameter_tbl(l_tax_id).basiscoef:= 0;
823 -- parameter_tbl(l_tax_id).constcoef:= 0;
824 -- parameter_tbl(l_tax_id).overrideconst:=
825 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt;
826
827 -- else
828
829 parameter_tbl(l_tax_id).basiscoef:=
830 parameter_tbl(l_tax_id).base_rate_modifier;
831
832 parameter_tbl(l_tax_id).constcoef:=
833 parameter_tbl(l_tax_id).discount;
834
835 parameter_tbl(l_tax_id).overrideconst:= 0;
836 IF (g_level_statement >= g_current_runtime_level ) THEN
837 FND_LOG.STRING(g_level_statement,
838 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
839 'basiscoef: ' ||parameter_tbl(l_tax_id).basiscoef ||' constcoef: ' ||
840 parameter_tbl(l_tax_id).constcoef);
841 END IF;
842
843 if l_formula_id is null then
844 p_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
845 IF (g_level_error >= g_current_runtime_level ) THEN
846 FND_LOG.STRING(g_level_error ,
847 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
848 'l_formula_id is null ');
849 FND_LOG.STRING(g_level_error ,
850 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
851 'p_return_status is ' || p_return_status);
852 FND_LOG.STRING(g_level_error ,
853 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
854 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)');
855 END IF;
856 RETURN;
857 end if;
858
859 open getFormulaInfoD(l_formula_id);
860 fetch getFormulaInfoD into l_compounding_tax,l_cpdg_tax_regime_code,
861 l_Compounding_Type_Code, l_enforce_compounding_flag;
862
863 while getFormulaInfoD%found loop
864
865 IF (g_level_statement >= g_current_runtime_level ) THEN
866 FND_LOG.STRING(g_level_statement,
867 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
868 ' Compounding tax: ' || l_compounding_tax||
869 ' Compounding tax regime code: ' || l_cpdg_tax_regime_code||
870 ' Compounding tax type: ' || l_Compounding_Type_Code);
871 END IF;
872
873 -- set the compounding_dep_tax_flag here
874 parameter_tbl(l_tax_id).compounding_dep_flg:= 'Y';
875
876 -- Bug#5395227- use cache structure
877 -- open getTaxId(l_compounding_tax, l_cpdg_tax_regime_code);
878 -- fetch getTaxId into l_compounding_tax_id;
879
880 -- Bug#5395227- replace getTaxId by the code below
881 --
882 -- init tax record for each new tax regime and tax
883 --
884 l_tax_rec := NULL;
885 l_compounding_tax_id := NULL;
886
887 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
888 l_cpdg_tax_regime_code,
889 l_compounding_tax,
890 l_tax_determine_date,
891 l_tax_rec,
892 p_return_status,
893 p_error_buffer);
894
895 if p_return_status = FND_API.G_RET_STS_SUCCESS then
896 l_compounding_tax_id := l_tax_rec.tax_id;
897 end if;
898
899 -- if getTaxID%notfound then
900 if l_compounding_tax_id IS NULL then
901 p_return_status:= FND_API.G_RET_STS_ERROR;
902
903 IF (g_level_error >= g_current_runtime_level ) THEN
904 FND_LOG.STRING(g_level_error ,
905 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
906 ' Compounding tax id does not exist');
907 FND_LOG.STRING(g_level_error ,
908 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
909 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)');
910 END IF;
911 RETURN;
912 end if;
913
914 IF (g_level_statement >= g_current_runtime_level ) THEN
915 FND_LOG.STRING(g_level_statement,
916 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
917 ' Compounding tax id is ' || l_compounding_tax_id);
918 END IF;
919 -- close getTaxId;
920
921 IF parameter_tbl.exists(l_compounding_tax_id) THEN
922
923 if l_Compounding_Type_Code = 'ADD' then
924 l_compounding_factor:= 1;
925 else
926 l_compounding_factor:= -1;
927 end if;
928
929 parameter_tbl(l_tax_id).basiscoef:= parameter_tbl(l_tax_id).basiscoef +
930 parameter_tbl(l_compounding_tax_id).basiscoef *
931 (parameter_tbl(l_compounding_tax_id).tax_rate /100) * l_compounding_factor;
932
933 parameter_tbl(l_tax_id).constcoef:= parameter_tbl(l_tax_id).constcoef +
934 parameter_tbl(l_compounding_tax_id).constcoef *
935 (parameter_tbl(l_compounding_tax_id).tax_rate/100) * l_compounding_factor +
936 parameter_tbl(l_compounding_tax_id).overrideconst * l_compounding_factor;
937
938 parameter_tbl(l_compounding_tax_id).compounding_flg:= 'Y';
939
940 ELSE --l_compounding_tax_id not exists
941
942 -- bug fix 3282007: add the following IF condition handling.
943 IF l_enforce_compounding_flag = 'Y' THEN
944
945 p_return_status:= FND_API.G_RET_STS_ERROR;
946
947 FND_MESSAGE.SET_NAME('ZX','ZX_COMPND_TAX_NOT_FOUND');
948 FND_MESSAGE.SET_TOKEN('TAX',l_compounding_tax);
949 FND_MESSAGE.SET_TOKEN('FORMULA_CODE',l_formula_code);
950 -- FND_MSG_PUB.Add;
951 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
952 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id;
953 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
954 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type;
955
956 ZX_API_PUB.add_msg(
957 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
958
959 IF (g_level_error >= g_current_runtime_level ) THEN
960 FND_LOG.STRING(g_level_error,
961 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
962 'Either Tax ' || l_compounding_tax_id ||
963 ' is not applicable or compounding precedence is wrong');
964 FND_LOG.STRING(g_level_error,
965 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis.END',
966 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)');
967 END IF;
968
969 RETURN;
970 ELSE
971 -- bug 3644541: set compounding_tax_miss_flag
972 --
973 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
974 i).compounding_tax_miss_flag := 'Y';
975 END IF;
976
977 END IF; -- parameter_tbl.exists
978
979 fetch getFormulaInfoD into l_compounding_tax,l_cpdg_tax_regime_code,
980 l_Compounding_Type_Code, l_enforce_compounding_flag;
981 end loop;
982
983 close getFormulaInfoD;
984
985 -- end if; -- last manual entry
986
987 end if; -- taxable basis type code
988
989 END IF; -- l_formula_code
990
991
992 l_sum_basiscoef:= l_sum_basiscoef + parameter_tbl(l_tax_id).composite_incl_indicator *
993 parameter_tbl(l_tax_id).basiscoef * parameter_tbl(l_tax_id).tax_rate / 100;
994
995 l_sum_constcoef:= l_sum_constcoef + parameter_tbl(l_tax_id).composite_incl_indicator *
996 parameter_tbl(l_tax_id).constcoef * parameter_tbl(l_tax_id).tax_rate /100
997 + parameter_tbl(l_tax_id).composite_incl_indicator *
998 parameter_tbl(l_tax_id).overrideconst;
999
1000 IF (g_level_statement >= g_current_runtime_level ) THEN
1001 FND_LOG.STRING(g_level_statement,
1002 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1003 ' sum_basiscoef: ' || l_sum_basiscoef||
1004 ' sum_constcoef: ' || l_sum_constcoef);
1005 END IF;
1006
1007 ELSE -- default case
1008
1009 NULL;
1010
1011 END CASE; -- Delete_Flag
1012
1013 IF p_return_status IN ( FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
1014 EXIT;
1015 END IF;
1016 END IF;
1017
1018 END LOOP;
1019
1020 IF (g_level_statement >= g_current_runtime_level ) THEN
1021 FND_LOG.STRING(g_level_statement,
1022 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1023 ' ');
1024 END IF;
1025
1026 IF nvl(p_return_status, 'SUCCESS') <> FND_API.G_RET_STS_ERROR THEN
1027
1028 l_common_comp_base := (l_line_amt - l_sum_constcoef) / l_sum_basiscoef;
1029
1030 IF (g_level_statement >= g_current_runtime_level ) THEN
1031 FND_LOG.STRING(g_level_statement,
1032 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1033 'l_sum_basiscoef is ' || l_sum_basiscoef);
1034 FND_LOG.STRING(g_level_statement,
1035 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1036 'l_sum_constcoef is ' || l_sum_constcoef);
1037 FND_LOG.STRING(g_level_statement,
1038 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1039 'l_common_comp_base is ' || l_common_comp_base);
1040
1041 --This complete the building of multipliers. Now update columns
1042
1043 FND_LOG.STRING(g_level_statement,
1044 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1045 'update columns');
1046 END IF;
1047
1048 for i in p_begin_index..p_end_index loop
1049
1050 l_tax_id := ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_id;
1051
1052
1053 -- Update the columns only for those lines which were not marked for
1054 -- deletion and which were not marked for claculation by providers
1055 -- and which are not manual tax lines copied from reference document.
1056
1057 -- Even if Freeze_Until_Overridden_Flag is 'Y', if the tax event type is
1058 -- OVERRIDE, then taxable amount should be calculated.
1059
1060 CASE
1061 WHEN ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_provider_id is NOT NULL OR
1062 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Delete_Flag = 'Y'
1063 THEN
1064
1065 NULL;
1066
1067 WHEN ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).OTHER_DOC_SOURCE = 'APPLIED_FROM' AND
1068 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag = 'P' AND
1069 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code <> 'OVERRIDE_TAX'
1070 THEN
1071 -- Proration Scenarios:
1072 -- In the following cases, taxable basis is not calculated using taxable
1073 -- basis formula, but proration is done based on reference / applied from
1074 -- adjusted to docs.
1075
1076 -- 1. XML Invoices with control total (for all taxes within the document):
1077 -- taxable basis determination prorates the taxable amount.
1078 -- This case is open and not yet finalized
1079 --
1080 -- 2. Transaction line with Adjusted to/Applied from information:
1081 -- Taxable basis Determination proartes the taxable amount
1082 -- eg. payables credit memo, adjusted to a payables invoice
1083 -- payables invoice, applied from a prepayment
1084 --
1085 -- 3. Imported summary tax lines with allocation link structure
1086 -- Taxable basis determination prorates the taxable amount
1087 -- (should we have tax event type called IMPORT to identify this ?
1088 --
1089 -- 4. Tax amount overridden in the summary tax line through the User Interface
1090 -- Taxable basis determination calcuates the taxable amount as
1091 -- tax amount / tax rate
1092
1093 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_amt <> 0 THEN
1094
1095 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt:=
1096 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_taxable_amt *
1097 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_amt /
1098 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_amt );
1099
1100 ELSE -- other_doc_line_amt = 0 OR IS NULL
1101 -- copy unrounded_taxable_amt from reference document,
1102 --
1103 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt :=
1104 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_line_taxable_amt;
1105
1106 END IF; -- other_doc_line_amt <> 0
1107
1108
1109 WHEN
1110 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Freeze_Until_Overridden_Flag = 'Y' AND
1111 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Overridden_Flag <> 'Y'
1112 THEN
1113 -- Taxable amounts should have been set to zero in applicability process.
1114 -- hence no processing required here.
1115
1116 NULL;
1117 WHEN nvl(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Delete_Flag,'N') <> 'Y' AND
1118 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_provider_id is NULL AND
1119 (nvl(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Freeze_Until_Overridden_Flag,'N') <> 'Y' OR
1120 nvl(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).COPIED_FROM_OTHER_DOC_FLAG,'N') <> 'Y') OR
1121 (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX' AND
1122 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Recalc_Required_Flag = 'Y') THEN
1123
1124 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).taxable_basis_formula:=
1125 parameter_tbl(l_tax_id).formula_code;
1126
1127 IF parameter_tbl(l_tax_id).formula_code = 'STANDARD_QUANTITY' THEN
1128 -- For quantity based taxes, the calculation is not performed using
1129 -- the regular calculation cycle. Hence calculate the taxable amt
1130 -- and tax amt for quantity based taxes.
1131 -- For quantity based taxes, taxable amt is set equal to quantity
1132 -- although the field name indicates it is amt.
1133
1134 parameter_tbl(l_tax_id).taxable_amt:=
1135 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_quantity;
1136
1137 parameter_tbl(l_tax_id).tax_amt:= parameter_tbl(l_tax_id).taxable_amt *
1138 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate;--Bug 5185614
1139
1140 ELSIF parameter_tbl(l_tax_id).Taxable_Basis_Type_Code = 'ASSESSABLE_VALUE' then
1141 -- For ASSESSABLE_VALUE formula, the taxable amt is the line assessable value.
1142 parameter_tbl(l_tax_id).taxable_amt:=
1143 nvl(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_assessable_value,
1144 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).line_amt);
1145
1146 parameter_tbl(l_tax_id).tax_amt:= parameter_tbl(l_tax_id).taxable_amt *
1147 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate / 100;
1148
1149 ELSE
1150
1151 parameter_tbl(l_tax_id).taxable_amt:= l_common_comp_base *
1152 parameter_tbl(l_tax_id).basiscoef
1153 + parameter_tbl(l_tax_id).constcoef;
1154
1155
1156 parameter_tbl(l_tax_id).tax_amt:= parameter_tbl(l_tax_id).taxable_amt *
1157 parameter_tbl(l_tax_id).tax_rate/100
1158 + parameter_tbl(l_tax_id).overrideconst;
1159
1160 END IF;
1161
1162 if ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry is null OR
1163 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry <> 'TAX_AMOUNT' then
1164
1165 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt:= parameter_tbl(l_tax_id).taxable_amt;
1166 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt:=
1167 parameter_tbl(l_tax_id).tax_amt;
1168
1169 elsif ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry = 'TAX_AMOUNT' THEN
1170 -- bug 5237144:
1171 -- and ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX' then
1172
1173 /* use cache
1174 OPEN getAdhocInfo(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_id);
1175 FETCH getAdhocInfo into l_adj_for_adhoc_amt_code, l_allow_adhoc_tax_rate_flag;
1176 IF getAdhocInfo%NOTFOUND THEN
1177 close getAdhocInfo;
1178 p_return_status := FND_API.G_RET_STS_ERROR;
1179 IF (g_level_error >= g_current_runtime_level ) THEN
1180 FND_LOG.STRING(g_level_error,
1181 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1182 'No Data Found for tax_rate_code: '||
1183 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code ||
1184 ' tax_rate_id: ' ||
1185 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_id );
1186 FND_LOG.STRING(g_level_error,
1187 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis.END',
1188 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis (-)');
1189 END IF;
1190 RETURN;
1191 END IF;
1192 CLOSE getAdhocInfo;
1193 */
1194
1195 ZX_TDS_UTILITIES_PKG.get_tax_rate_info (
1196 p_tax_rate_id => ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_id,
1197 p_tax_rate_rec => l_tax_rate_rec,
1198 p_return_status => p_return_status,
1199 p_error_buffer => p_error_buffer);
1200
1201
1202 -- bug#7344499- return error to user when rate is not found
1203
1204 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1205 -- no rate found for the specify tax_rate_id
1206 RETURN;
1207 END IF;
1208
1209 l_adj_for_adhoc_amt_code := nvl(l_tax_rate_rec.adj_for_adhoc_amt_code,'TAXABLE_BASIS');
1210 l_allow_adhoc_tax_rate_flag := nvl(l_tax_rate_rec.Allow_Adhoc_Tax_Rate_Flag,'N');
1211
1212 IF l_allow_adhoc_tax_rate_flag = 'N' OR
1213 (l_allow_adhoc_tax_rate_flag = 'Y' AND l_adj_for_adhoc_amt_code = 'TAXABLE_BASIS') OR
1214 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).manually_entered_flag, 'N') = 'Y'
1215 THEN
1216 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate <> 0 THEN
1217 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).orig_taxable_amt IS NULL THEN
1218 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).orig_taxable_amt :=
1219 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).taxable_amt;
1220 END IF;
1221 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt:=
1222 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt /
1223 --Start Bug 7310806
1224 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate * 100;
1225 --parameter_tbl(l_tax_id).tax_rate * 100;
1226 --End Bug 7310806
1227
1228 -- ensuring that PRORATED_TB is not set for overriden tax lines.
1229 IF NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).overridden_flag,'N') = 'N'
1230 OR NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).manually_entered_flag,'N') = 'Y' THEN
1231 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).taxable_basis_formula:= 'PRORATED_TB';
1232 END IF;
1233
1234 ELSE
1235
1236 -- when tax_rate = 0, unrounded_tax_amt = 0, do nothing.
1237 -- Error out when Tax_rate = 0, unrounded_tax_amt <> 0.
1238 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt <> 0 THEN
1239 p_return_status := FND_API.G_RET_STS_ERROR;
1240 IF (g_level_error >= g_current_runtime_level ) THEN
1241 FND_LOG.STRING(g_level_error,
1242 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis',
1243 'adj_for_adhoc_amt_code: ' ||l_adj_for_adhoc_amt_code );
1244 FND_LOG.STRING(g_level_error,
1245 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis',
1246 'Tax_rate = 0, unrounded_tax_amt <> 0. ' ||
1247 'Cannot calculate taxable basis amount.');
1248 FND_LOG.STRING(g_level_error,
1249 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1250 'p_return_status = ' || p_return_status);
1251 FND_LOG.STRING(g_level_error,
1252 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis.END',
1253 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis (-)');
1254 END IF;
1255 RETURN;
1256 END IF;
1257 END IF; -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate
1258
1259 ELSIF (l_allow_adhoc_tax_rate_flag = 'Y' AND l_adj_for_adhoc_amt_code = 'TAX_RATE')
1260 THEN
1261 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt <> 0 THEN
1262
1263 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).orig_tax_rate IS NULL THEN
1264 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).orig_tax_rate :=
1265 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate;
1266 END IF;
1267
1268 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate :=
1269 round(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt/
1270 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt*100, 6);
1271
1272 ELSE
1273 -- when Taxable_amt = 0, unrounded_tax_amt = 0, do nothing.
1274 -- Error out when Taxable_amt = 0, unrounded_tax_amt <> 0.
1275 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt <> 0 THEN
1276 p_return_status := FND_API.G_RET_STS_ERROR;
1277 IF (g_level_error >= g_current_runtime_level ) THEN
1278 FND_LOG.STRING(g_level_error,
1279 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis',
1280 'adj_for_adhoc_amt_code: ' ||l_adj_for_adhoc_amt_code );
1281 FND_LOG.STRING(g_level_error,
1282 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis',
1283 'Taxable_amt = 0, unrounded_tax_amt <> 0. ' ||
1284 'Cannot calculate tax rate.');
1285 FND_LOG.STRING(g_level_error,
1286 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.Get_taxable_basis',
1287 'p_return_status = ' || p_return_status);
1288 FND_LOG.STRING(g_level_error,
1289 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis.END',
1290 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis (-)');
1291 END IF;
1292 RETURN;
1293 END IF;
1294
1295 END IF; -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt
1296 END IF;
1297
1298 end if; -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry
1299
1300 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_base_modifier_rate:=
1301 parameter_tbl(l_tax_id).base_rate_modifier;
1302 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Compounding_Tax_Flag:=
1303 parameter_tbl(l_tax_id).compounding_flg;
1304 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Compounding_Dep_Tax_Flag:=
1305 parameter_tbl(l_tax_id).compounding_dep_flg;
1306
1307
1308 WHEN ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Freeze_Until_Overridden_Flag = 'Y' THEN
1309
1310 NULL;
1311
1312 ELSE -- default case
1313
1314 NULL;
1315
1316 END CASE; -- Delete_Flag
1317
1318 -- polpulate tax_amt_included_flag and compounding_tax_flag in
1319 -- ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl
1320 --
1321 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1322 i).tax_amt_included_flag = 'Y' THEN
1323
1324 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_amt_included_flag(
1325 p_structure_index) := 'Y';
1326 END IF;
1327
1328 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1329 i).compounding_dep_tax_flag = 'Y' THEN
1330
1331 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.compounding_tax_flag(
1332 p_structure_index) := 'Y';
1333 END IF;
1334
1335 end loop;
1336
1337 END IF;
1338
1339 IF (g_level_procedure >= g_current_runtime_level ) THEN
1340 FND_LOG.STRING(g_level_procedure,
1341 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.get_taxable_basis.END',
1342 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS (-)');
1343 END IF;
1344
1345 EXCEPTION
1346 WHEN OTHERS THEN
1347 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1349
1350 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1351 FND_LOG.STRING(g_level_unexpected,
1352 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.GET_TAXABLE_BASIS',
1353 p_error_buffer);
1354 END IF;
1355
1356 END get_taxable_basis;
1357
1358 PROCEDURE populate_inclusive_tax_flag (
1359 p_tax_line_index IN NUMBER,
1360 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
1361 p_structure_name IN VARCHAR2,
1362 p_structure_index IN BINARY_INTEGER,
1363 p_return_status OUT NOCOPY VARCHAR2,
1364 p_error_buffer OUT NOCOPY VARCHAR2) IS
1365
1366 l_inclusive_tax_flag VARCHAR2(1);
1367 l_reg_party_type zx_lines.registration_party_type%TYPE;
1368 l_ptp_id zx_party_tax_profile.party_tax_profile_id%TYPE;
1369 l_site_ptp_id zx_party_tax_profile.party_tax_profile_id%TYPE;
1370
1371 CURSOR get_inclusive_flag_from_rate(
1372 c_tax_rate_id zx_rates_b.tax_rate_id%TYPE) IS
1373 SELECT inclusive_tax_flag
1374 FROM zx_rates_b
1375 WHERE tax_rate_id = c_tax_rate_id;
1376
1377 CURSOR get_inclusive_flag_from_ptp(
1378 c_ptp_id zx_party_tax_profile.party_tax_profile_id%TYPE) IS
1379 SELECT inclusive_tax_flag
1380 FROM zx_party_tax_profile
1381 WHERE party_tax_profile_id = c_ptp_id;
1382 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
1383
1384 BEGIN
1385
1386 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1387
1388 IF (g_level_procedure >= g_current_runtime_level ) THEN
1389 FND_LOG.STRING(g_level_procedure,
1390 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag.BEGIN',
1391 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag (+)');
1392 END IF;
1393
1394 p_return_status:= FND_API.G_RET_STS_SUCCESS;
1395
1396
1397 -- IF line_amt_includes_tax_flag is 'A'/'N', tax_amt_included_flag
1398 -- is 'Y'/'N'. If line_amt_includes_tax_flag IN ('S', 'I'), need to
1399 -- determine tax_amt_included_flag from tax rate/tax registration/
1400 -- PTP/Tax.
1401 --
1402 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_amt_includes_tax_flag(
1403 p_structure_index) = 'A'
1404 THEN
1405 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1406 p_tax_line_index).tax_amt_included_flag := 'Y';
1407
1408
1409 ELSIF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_amt_includes_tax_flag(
1410 p_structure_index) = 'N'
1411 THEN
1412 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1413 p_tax_line_index).tax_amt_included_flag := 'N';
1414
1415 ELSIF ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.line_amt_includes_tax_flag(
1416 p_structure_index) IN ('S', 'I')
1417 THEN
1418
1419 -- Get tax_amt_included_flag from tax rate
1420 --
1421 /* use cache
1422 OPEN get_inclusive_flag_from_rate(
1423 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_tax_line_index).tax_rate_id);
1424 FETCH get_inclusive_flag_from_rate INTO l_inclusive_tax_flag;
1425 CLOSE get_inclusive_flag_from_rate;
1426 */
1427
1428 ZX_TDS_UTILITIES_PKG.get_tax_rate_info (
1429 p_tax_rate_id => ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1430 p_tax_line_index).tax_rate_id,
1431 p_tax_rate_rec => l_tax_rate_rec,
1432 p_return_status => p_return_status,
1433 p_error_buffer => p_error_buffer);
1434
1435 l_inclusive_tax_flag := l_tax_rate_rec.inclusive_tax_flag;
1436
1437 IF l_inclusive_tax_flag IS NOT NULL THEN
1438
1439 -- populate inclusive_tax_flag onto detail tax line
1440 --
1441 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1442 p_tax_line_index).tax_amt_included_flag := l_inclusive_tax_flag;
1443
1444 IF g_level_statement >= g_current_runtime_level THEN
1445 FND_LOG.STRING(g_level_statement,
1446 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1447 'Get Inclusive_tax_flag from Tax Rate: ' ||
1448 l_inclusive_tax_flag);
1449 END IF;
1450 ELSE
1451 -- Check inclusive_tax_flag returned from tax registration
1452 -- process(inclusive_tax_flag has already been stamped onto
1453 -- detail tax line). If it is not available, get
1454 -- inclusive_tax_flag from ptp table
1455 --
1456 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1457 p_tax_line_index).tax_amt_included_flag IS NOT NULL THEN
1458 IF g_level_statement >= g_current_runtime_level THEN
1459 FND_LOG.STRING(g_level_statement,
1460 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1461 'tax_amt_included_flag available from tax registration. ' ||
1462 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1463 p_tax_line_index).tax_amt_included_flag);
1464 END IF;
1465 ELSE
1466
1467 -- Bug 5335580: Query inclusive_tax_flag from PTP table with registration
1468 -- party site level PTP id first. If it is not found, query
1469 -- inclusive_tax_flag using registration party PTP id.
1470 --
1471 -- Get tax inclusive flag with l_site_ptp_id
1472 --
1473 l_reg_party_type :=
1474 REPLACE(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1475 p_tax_line_index).registration_party_type,
1476 'PARTY', 'SITE') || '_' || 'TAX_PROF_ID';
1477
1478 ZX_GET_TAX_PARAM_DRIVER_PKG.get_driver_value(
1479 p_structure_name,
1480 p_structure_index,
1481 l_reg_party_type,
1482 l_site_ptp_id,
1483 p_return_status );
1484
1485 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1486 IF (g_level_error >= g_current_runtime_level ) THEN
1487 FND_LOG.STRING(g_level_error ,
1488 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1489 'Incorrect return_status after call ZX_GET_TAX_PARAM_DRIVER_PKG.get_driver_value().');
1490
1491 FND_LOG.STRING(g_level_error,
1492 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1493 'return_status: '|| p_return_status);
1494 END IF;
1495 END IF;
1496
1497 OPEN get_inclusive_flag_from_ptp(l_site_ptp_id);
1498 FETCH get_inclusive_flag_from_ptp INTO l_inclusive_tax_flag;
1499 CLOSE get_inclusive_flag_from_ptp;
1500
1501 IF l_inclusive_tax_flag IS NOT NULL THEN
1502
1503 -- populate inclusive_tax_flag onto detail tax line
1504 --
1505 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1506 p_tax_line_index).tax_amt_included_flag := l_inclusive_tax_flag;
1507
1508 IF g_level_statement >= g_current_runtime_level THEN
1509 FND_LOG.STRING(g_level_statement,
1510 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1511 'Get tax_amt_included_flag from SITE PTP: ' ||
1512 l_inclusive_tax_flag);
1513 END IF;
1514
1515 ELSE -- l_inclusive_tax_flag is not found from SITE PTP
1516
1517 -- Get tax inclusive flag with l_ptp_id
1518 --
1519 l_reg_party_type:= ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1520 p_tax_line_index).registration_party_type || '_TAX_PROF_ID';
1521
1522 ZX_GET_TAX_PARAM_DRIVER_PKG.get_driver_value(
1523 p_structure_name,
1524 p_structure_index,
1525 l_reg_party_type,
1526 l_ptp_id,
1527 p_return_status );
1528
1529 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1530 IF (g_level_statement>= g_current_runtime_level ) THEN
1531 FND_LOG.STRING(g_level_statement,
1532 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1533 'Incorrect return_status after call ZX_GET_TAX_PARAM_DRIVER_PKG.get_driver_value().');
1534
1535 FND_LOG.STRING(g_level_statement,
1536 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1537 'return_status: '|| p_return_status);
1538 END IF;
1539 END IF;
1540
1541 OPEN get_inclusive_flag_from_ptp(l_ptp_id);
1542 FETCH get_inclusive_flag_from_ptp INTO l_inclusive_tax_flag;
1543 CLOSE get_inclusive_flag_from_ptp;
1544
1545 IF l_inclusive_tax_flag IS NOT NULL THEN
1546
1547 -- populate inclusive_tax_flag onto detail tax line
1548 --
1549 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1550 p_tax_line_index).tax_amt_included_flag := l_inclusive_tax_flag;
1551
1552 IF g_level_statement >= g_current_runtime_level THEN
1553 FND_LOG.STRING(g_level_statement,
1554 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1555 'Get tax_amt_included_flag from PARTY PTP: ' ||
1556 l_inclusive_tax_flag);
1557 END IF;
1558 ELSE
1559 -- Get Get tax inclusive flag from Tax
1560 --
1561 l_inclusive_tax_flag := ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(
1562 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1563 p_tax_line_index).tax_id).def_inclusive_tax_flag;
1564
1565 IF l_inclusive_tax_flag IS NOT NULL THEN
1566
1567 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1568 p_tax_line_index).tax_amt_included_flag := l_inclusive_tax_flag;
1569
1570 IF g_level_statement >= g_current_runtime_level THEN
1571 FND_LOG.STRING(g_level_statement,
1572 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1573 'Get tax_amt_included_flag from tax: ' ||
1574 l_inclusive_tax_flag);
1575 END IF;
1576
1577 ELSE
1578
1579 -- Bug 4778841: default l_inclusive_tax_flag to 'N' when it is NULL
1580 --
1581 l_inclusive_tax_flag := 'N';
1582 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1583 p_tax_line_index).tax_amt_included_flag := l_inclusive_tax_flag;
1584
1585 IF g_level_statement >= g_current_runtime_level THEN
1586 FND_LOG.STRING(g_level_statement,
1587 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1588 'Inclusive tax flag is defaulted to N. ');
1589 END IF;
1590
1591 ---- raise error because l_inclusive_tax_flag is not available
1592 ----
1593 --p_return_status := FND_API.G_RET_STS_ERROR;
1594 --p_error_buffer := 'Inclusive Tax Flag is not available.';
1595 --
1596 --FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1597 --FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
1598 -- 'populate_inclusive_tax_flag- '|| p_error_buffer);
1599 --FND_MSG_PUB.Add;
1600 --IF (g_level_unexpected >= g_current_runtime_level ) THEN
1601 -- FND_LOG.STRING(g_level_unexpected,
1602 -- 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1603 -- p_error_buffer);
1604 -- FND_LOG.STRING(g_level_unexpected,
1605 -- 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1606 -- 'Exception :ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag (-)');
1607 --END IF;
1608 --RETURN;
1609
1610 END IF; -- tax_amt_included_flag is avialbale from tax
1611 END IF; -- tax_amt_included_flag is avialbale from PARTY PTP
1612 END IF; -- tax_amt_included_flag is avialbale from SITE PTP
1613 END IF; -- tax_amt_included_flag is avialbale from tax registration
1614 END IF; -- tax_amt_included_flag is avialbale from tax rate
1615 END IF; -- line_amt_includes_tax_flag
1616
1617 IF (g_level_procedure >= g_current_runtime_level ) THEN
1618 FND_LOG.STRING(g_level_procedure,
1619 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag.END',
1620 'ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag (-)');
1621 END IF;
1622
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1626 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1627
1628 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1629 FND_LOG.STRING(g_level_unexpected,
1630 'ZX.PLSQL.ZX_TDS_TAXABLE_BASIS_DETM_PKG.populate_inclusive_tax_flag',
1631 p_error_buffer);
1632 END IF;
1633 END populate_inclusive_tax_flag;
1634
1635 END ZX_TDS_TAXABLE_BASIS_DETM_PKG;
1636
[Home] [Help]