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