1 PACKAGE BODY ZX_TDS_RATE_DETM_PKG AS
2 /* $Header: zxditxratedtpkgb.pls 120.77.12010000.4 2008/12/11 04:24:23 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_error CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 ------------------------------------------------------------------------------
11 -- PRIVATE PROCEDURE
12 ------------------------------------------------------------------------------
13
14 PROCEDURE validate_offset_tax (
15 p_tax_regime_code IN zx_regimes_b.tax_regime_code%TYPE,
16 p_tax IN zx_taxes_b.tax%TYPE,
17 p_tax_determine_date IN DATE,
18 p_tax_status_code IN zx_status_b.tax_status_code%TYPE,
19 p_tax_jurisdiction_code IN zx_jurisdictions_b.tax_jurisdiction_code%TYPE,
20 p_tax_class IN zx_rates_b.tax_class%TYPE,
21 p_tax_rate_code IN zx_rates_b.tax_rate_code%TYPE,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_error_buffer OUT NOCOPY VARCHAR2
24 );
25
26 ------------------------------------------------------------------------------
27 -- PUBLIC PROCEDURE
28 -- get_tax_rate
29 --
30 -- DESCRIPTION
31 -- This is the main procedure in this package.This procedure is used to
32 -- determine the tax rate for all tax lines belonging to a transaction line
33 -- (indicated by p_begin_index and p_end_index)
34 ------------------------------------------------------------------------------
35
36
37 PROCEDURE GET_TAX_RATE(
38 p_begin_index IN NUMBER,
39 p_end_index IN NUMBER,
40 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
41 p_structure_name IN VARCHAR2,
42 p_structure_index IN BINARY_INTEGER,
43 p_return_status OUT NOCOPY VARCHAR2,
44 p_error_buffer OUT NOCOPY VARCHAR2)
45 IS
46
47 l_tax_id NUMBER;
48 l_def_tax_rate_code zx_rates_b.tax_rate_code%TYPE;
49 l_Tax_Rate_Rule_Flag varchar2(1);
50 l_tax ZX_TAXES_B.tax%TYPE;
51 l_tax_status_code ZX_STATUS_B.tax_status_code%TYPE;
52 l_tax_regime_code ZX_STATUS_B.tax_regime_code%TYPE;
53 l_tax_rate NUMBER;
54 l_tax_rate_id INTEGER;
55 l_def_tax_rate_id INTEGER;
56 l_tax_jurisdiction_code zx_rates_b.tax_jurisdiction_code%TYPE;
57 l_tax_jurisdiction_id zx_lines.tax_jurisdiction_id%TYPE;
58 l_Rate_Type_Code zx_rates_b.Rate_Type_Code%TYPE;
59 l_def_rate_type zx_rates_b.Rate_Type_Code%TYPE;
60 l_percentage_rate zx_rates_b.percentage_rate%TYPE;
61 l_def_percentage_rate zx_rates_b.percentage_rate%TYPE;
62 l_quantity_rate zx_rates_b.quantity_rate%TYPE;
63 l_def_quantity_rate zx_rates_b.quantity_rate%TYPE;
64 l_tax_rate_code zx_rates_b.tax_rate_code%TYPE;
65 l_tax_date date;
66 l_zx_result_rec zx_process_results%ROWTYPE;
67 l_status_index NUMBER;
68 l_effective_from DATE;
69 l_effective_to DATE;
70 l_adhoc_tax_rate_flg VARCHAR2(1);
71 l_uom_code zx_rates_b.uom_code%TYPE;
72 l_offset_tax_rate_code zx_rates_b.offset_tax_rate_code%TYPE;
73 l_offset_status_code zx_rates_b.offset_status_code%TYPE;
74 l_offset_tax zx_rates_b.offset_tax%TYPE;
75 l_offset_tax_appl VARCHAR2(1);
76 l_ptp_id NUMBER;
77 l_reg_party_type VARCHAR2(80);
78 l_numeric_result zx_process_results.numeric_result%TYPE;
79
80 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
81 l_exemption_rec ZX_TCM_GET_EXEMPT_PKG.exemption_rec_type;
82 l_exception_rec ZX_TCM_GET_EXCEPT_PKG.exception_rec_type;
83 l_allow_exemptions_flag zx_rates_b.allow_exemptions_flag%TYPE;
84 l_allow_exceptions_flag zx_rates_b.allow_exceptions_flag%TYPE;
85
86 l_multiple_jurisdictions_flag VARCHAR2(1);
87
88 l_tax_class zx_rates_b.tax_class%TYPE;
89 l_inventory_org_id NUMBER;
90
91 TYPE exemption_info_type IS RECORD (
92 tax_regime_code zx_taxes_b.tax_regime_code%TYPE,
93 tax zx_taxes_b.tax%TYPE,
94 exemption_id NUMBER,
95 percent_exempt NUMBER,
96 discount_special_rate VARCHAR2(30)
97 );
98
99 TYPE exemption_info_tbl_type IS TABLE OF exemption_info_type
100 INDEX by BINARY_INTEGER;
101
102 l_exempt_info_tbl exemption_info_tbl_type;
103 l_ind NUMBER;
104
105 CURSOR get_ptp_cur(c_ptp_id zx_party_tax_profile.party_tax_profile_id%TYPE) IS
106 SELECT Allow_Offset_Tax_Flag
107 FROM zx_party_tax_profile
108 WHERE party_tax_profile_id = c_ptp_id;
109
110 CURSOR get_numeric_value_csr(c_result_id zx_process_results.result_id%TYPE) IS
111 SELECT numeric_result
112 FROM zx_process_results
113 WHERE result_id = c_result_id;
114
115 -- bug 7208618
116 --
117 CURSOR get_ap_supplier_csr(
118 c_account_id ap_supplier_sites_all.vendor_id%TYPE) IS
119 SELECT offset_tax_flag
120 FROM ap_suppliers
121 WHERE vendor_id = c_account_id ;
122
123 CURSOR get_ap_supplier_site_csr(
124 c_account_id ap_supplier_sites_all.vendor_id%TYPE,
125 c_account_site_id ap_supplier_sites_all.vendor_site_id%TYPE) IS
126 SELECT offset_tax_flag
127 FROM ap_supplier_sites_all
128 WHERE vendor_id = c_account_id
129 AND vendor_site_id = c_account_site_id;
130
131 l_account_id hz_cust_accounts.cust_account_id%TYPE;
132 l_account_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
133 l_account_type_code VARCHAR2(30);
134 l_first_party_flag BOOLEAN;
135 --
136 -- End Bug 7208618
137
138
139 BEGIN
140
141 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
142
143 IF (g_level_procedure >= g_current_runtime_level ) THEN
144 FND_LOG.STRING(g_level_procedure,
145 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.GET_TAX_RATE.BEGIN',
146 'ZX_TDS_RATE_DETM_PKG: GET_TAX_RATE(+)');
147 END IF;
148
149 p_return_status:= FND_API.G_RET_STS_SUCCESS;
150
151 -- begin to get tax rate name, if available
152
153 IF p_begin_index IS NULL or p_end_index IS NULL THEN
154 p_return_status:= FND_API.G_RET_STS_ERROR;
155 IF (g_level_error >= g_current_runtime_level ) THEN
156
157 FND_LOG.STRING(g_level_error,
158 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
159 ' unexpected error:' || 'p_begin_index or p_end_index IS NULL...');
160 END IF;
161 RETURN;
162 END IF;
163
164 l_exempt_info_tbl.DELETE;
165
166 -- Bug#5417753- determine tax_class value
167 IF p_event_class_rec.prod_family_grp_code = 'O2C' THEN
168 l_tax_class := 'OUTPUT';
169 ELSIF p_event_class_rec.prod_family_grp_code = 'P2P' THEN
170 l_tax_class := 'INPUT';
171 END IF;
172
173 FOR i in p_begin_index..p_end_index LOOP
174
175
176 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate IS NOT NULL AND
177 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.source_event_class_code(p_structure_index) = 'INTERCOMPANY_TRX' THEN
178 IF (g_level_statement >= g_current_runtime_level ) THEN
179 FND_LOG.STRING(g_level_statement,
180 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
181 ' Intercompany Transaction : Tax rate : ' || ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate);
182 END IF;
183 ELSE
184
185 l_tax_id := ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_id;
186 l_tax := ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax;
187 l_tax_status_code :=
188 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_status_code;
189 l_tax_regime_code :=
190 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_regime_code;
191
192 l_tax_jurisdiction_code :=
193 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_jurisdiction_code;
194 l_tax_jurisdiction_id :=
195 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_jurisdiction_id;
196
197 --- Bug 7499374
198 l_tax_date := NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_date,
199 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date);
200
201 IF (g_level_statement >= g_current_runtime_level ) THEN
202 FND_LOG.STRING(g_level_statement,
203 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
204 ' Tax Date ' || l_tax_date);
205 END IF;
206
207 l_multiple_jurisdictions_flag :=
208 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).multiple_jurisdictions_flag, 'N');
209
210 l_offset_tax_appl := NULL;
211
212 -- Do not perform rate det. if rate is already there in g_detail_tax_lines_tbl
213 -- or delete_flag on the tax line is Y or tax is calculated by providers
214 -- rate will be already available if it is a manual tax line or an override
215 -- tax event with last_manual_entry = 'Tax Rate' or 'Tax Amount'
216
217 -- tax rate determination should not be done for offset taxes. However, the
218 -- offset tax lines will not be there in detail tax lines structure for
219 -- create or override case. Hence logic to exclude offset taxes is not included
220
221
222 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry =
223 'TAX_AMOUNT' AND
224 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate IS NULL
225 THEN
226 p_return_status:= FND_API.G_RET_STS_ERROR;
227 p_error_buffer :=
228 'Tax Rate must be entered when last manual entry is Tax Amount';
229 IF (g_level_error >= g_current_runtime_level ) THEN
230 FND_LOG.STRING(g_level_error,
231 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
232 'p_error_buffer: '|| p_error_buffer);
233 END IF;
234 RETURN;
235 END IF;
236
237 CASE
238 WHEN
239 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX'
240 AND
241 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Recalc_Required_Flag IS NULL OR
242 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Recalc_Required_Flag = 'N'
243 )
244 THEN
245
246 -- RECALC_REQUIRED_FLAG will be populated by tax lines Determination
247 -- table handler when the user overrides one or more tax lines. (When the
248 -- line being overridden is inclusive or used to compound other taxes,
249 -- then this flag will be set to 'Y' for all the tax lines belonging to
250 -- the current transaction line) If the value of RACALC_REQUIRED_FLAG = 'N'
251 -- then skip the process and only perform population of relevant Tax Rate
252 -- Determination columns into detail tax lines structure.
253 NULL;
254
255 WHEN
256 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry in
257 ('TAX_RATE', 'TAX_AMOUNT','STATUSTORATE' ) -- and -- bug fix 5237144
258 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX'
259 THEN
260 -- Check the last_manual_entry flag and if it is tax_rate or tax_amount or
261 -- status_to_rate, then do not perform this process.
262 /****************changed code for 6903249***************************/
263 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'REFERENCE' AND
264 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_tax_amt = 0 AND
265 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).unrounded_taxable_amt = 0 AND
266 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).manually_entered_flag = 'Y' AND
267 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).freeze_until_overridden_flag ='Y'
268 THEN
269
270 NULL;
271
272 ELSE
273
274 l_tax_regime_code :=
275 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_regime_code;
276 l_tax :=
277 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax;
278 l_tax_jurisdiction_code :=
279 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_jurisdiction_code;
280 l_tax_status_code :=
281 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_status_codE;
282 l_tax_rate_code :=
283 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code;
284 zx_tds_utilities_pkg.get_tax_rate_info
285 ( l_tax_regime_code,
286 l_tax,
287 l_tax_jurisdiction_code,
288 l_tax_status_code,
289 l_tax_rate_code,
290 l_tax_date,
291 l_tax_class,
292 l_tax_rate_rec,
293 p_return_status,
294 p_error_buffer);
295
296 l_tax_rate_id := l_tax_rate_rec.tax_rate_id;
297 l_tax_rate_code := l_tax_rate_rec.tax_rate_code;
298 l_rate_type_code := l_tax_rate_rec.rate_type_code;
299 l_percentage_rate := l_tax_rate_rec.percentage_rate; l_quantity_rate := l_tax_rate_rec.quantity_rate;
300 l_uom_code := l_tax_rate_rec.uom_code;
301 l_offset_tax_rate_code := l_tax_rate_rec.offset_tax_rate_code;
302 l_offset_status_code := l_tax_rate_rec.offset_status_code;
303 l_offset_tax := l_tax_rate_rec.offset_tax;
304 l_adhoc_tax_rate_flg := l_tax_rate_rec.allow_adhoc_tax_rate_flag;
305 l_allow_exemptions_flag := l_tax_rate_rec.allow_exemptions_flag;
306 l_allow_exceptions_flag := l_tax_rate_rec.allow_exceptions_flag;
307
308 IF (l_offset_tax_rate_code is NOT NULL) THEN
309 validate_offset_tax (
310 p_tax_regime_code => l_tax_regime_code,
311 p_tax => l_offset_tax,
312 p_tax_determine_date => l_tax_date,
313 p_tax_status_code => l_offset_status_code,
314 p_tax_jurisdiction_code => l_tax_jurisdiction_code, --? does offset tax rate has jurisdiction info
315 p_tax_class => l_tax_class,
316 p_tax_rate_code => l_offset_tax_rate_code,
317 x_return_status => p_return_status,
318 x_error_buffer => p_error_buffer
319 );
320 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
321 p_error_buffer := 'Need to create Offset Tax. But offset tax is not
322 valid'; -- will be replaced with coded message
323
324 IF g_level_error >= g_current_runtime_level THEN
325 FND_LOG.STRING(g_level_error,
326 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
327 'Need to create Offset Tax. But offset tax is not valid');
328 FND_LOG.STRING(g_level_error,
329 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
330 'RETURN_STATUS = ' || p_return_status);
331 FND_LOG.STRING(g_level_error,
332 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
333 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-)');
334 END IF;
335 RETURN;
336 ELSE
337 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).offset_tax_rate_code
338 := l_offset_tax_rate_code;
339 END IF;
340 END IF;
341 END IF;
342
343 /**********end of changed code for 6903249 ************************/
344
345
346 WHEN
347 (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).OTHER_DOC_SOURCE = 'ADJUSTED' OR
348 (ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'APPLIED_FROM' AND
349 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag <> 'R')
350 ) AND
351 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code <> 'OVERRIDE_TAX'
352 THEN
353 -- In case when the tax line is copied from Applied From (applied_amt_handling_flag on
354 -- g_tax_rec_tbl(l_tax_id) is NOT 'R' ) or Adjusted Document,
355 -- Applicability process will copy Tax Regime, Tax, Status, Rate, Place of Supply,
356 -- Reg. Number, Offset tax columns from original document. So no need to perform
357 -- tax rate determination in this case. However, when the user overrides the calcuated
358 -- tax line, tax rate determination needs to be performed.
359
360 NULL;
361
362 /* bug 3330127 : Included in the next case
363 WHEN
364 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Copied_From_Other_Doc_Flag = 'Y' AND
365 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Manually_Entered_Flag = 'Y'
366 THEN
367 -- When a manual tax line is copied from reference document, the Tax Regime, Tax, Status, Rate,
368 -- and other columns are copied from manual tax line in reference document as well. So
369 -- no need to perform tax rate determination in this case.
370
371 NULL;
372 */
373 WHEN
374 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Freeze_Until_Overridden_Flag = 'Y'
375
376 /* -- Bug 3330127: When user override tax line information, Freeze_Until_Overridden_Flag will
377 -- be set to 'N'
378 AND ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Overridden_Flag <> 'Y'
379 OR
380 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Copied_From_Other_Doc_Flag = 'Y' )
381 */
382 THEN
383
384 -- When a manual tax line is copied from reference document, the Tax Regime, Tax, Status, Rate,
385 -- and other columns are copied from manual tax line in reference document as well. So
386 -- no need to perform tax rate determination in this case.
387
388 -- When the transaction is matched to a reference document, and if a tax
389 -- that was applicable on the reference document is not found applicable
390 -- during applicability process, the tax line from the reference document
391 -- is copied, but the tax rate, status, amounts are populated as zero,
392 -- until the user views that tax line and overrides it. So skip tax rate determination
393 -- in this case.
394
395 NULL;
396
397 /* -- bug 4673667: do not need to check delete_flag
398 WHEN
399 -- bug 3330127
400 -- Tax lines calculated by provider will not be pulled in for processing
401 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_provider_id is NOT NULL
402 OR
403
404 UPPER(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Delete_Flag) = 'Y'
405 THEN
406 -- Do not perform rate determination for tax lines calculated by providers
407 -- or for tax lines marked for deletion.
408
409 NULL;
410 */
411 WHEN
412 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code is null
413 /* -- Bug 3330127: already handled in the above cases
414 AND
415 nvl(upper(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Delete_Flag),'N') <> 'Y' AND
416 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_provider_id is NULL
417 */
418 ) OR
419 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry IN
420 ('TAX_STATUS', 'TAX_RATE_CODE') -- AND --bug 5237144
421 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_event_type_code = 'OVERRIDE_TAX'
422 ) OR
423 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).other_doc_source = 'APPLIED_FROM' AND
424 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).applied_amt_handling_flag = 'R'
425 ) OR
426 -- Bug 5176149: need to populate tax rate id and tax rate % for matched invoice
427 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).OTHER_DOC_SOURCE = 'REFERENCE'
428 ) OR
429 ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).direct_rate_result_id IS NOT NULL
430 ) OR
431 ( p_event_class_rec.template_usage_code = 'TAX_RULES' AND
432 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code IS NOT NULL
433 )
434 THEN
435 -- This is the normal processing case where rate determination needs to be performed
436 -- When tax is not calculated by provider service and rate is not already
437 -- available in detail tax lines structure and delete flag is not 'Y'
438 -- or an override case with last_manual_entry as 'TAX_STATUS', then determine
439 -- the tax rate to be applied to the tax line.
440
441 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).process_for_recovery_flag:= 'Y';
442
443 -- IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code IS NOT NULL
444 -- bug 3330127: if last_manual_entry = 'TAX_STATUS', UI should set tax_rate_code to NULL
445 -- AND nvl(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry,'N') <> 'TAX_STATUS'
446 -- THEN
447 --
448 -- tax rate is already available on tax line. Use this tax rate
449 --
450 -- l_tax_rate_code:= ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code;
451 --
452 -- ELSE -- get rate code
453
454 -- If tax rate code is available on tax line. Use this tax rate.
455 -- Otherwise, need to determine tax rate code
456 --
457 l_tax_rate_code := ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code;
458
459 IF l_tax_rate_code IS NULL THEN
460
461 -- select the tax rate rule flag from tax status cache
462 -- get the hash value using tax regime code, tax, tax status code
463
464 l_status_index := ZX_TDS_UTILITIES_PKG.get_tax_status_index(
465 l_tax,
466 l_tax_regime_code,
467 l_tax_status_code);
468
469 IF l_status_index IS NULL THEN
470
471 -- Tax Status Determination must always populate the Tax Status Information
472 -- in the cache; Even when the user enters a manual tax line with status info.
473 -- the tax status determination will populate the Tax Status cache for that Status
474 -- hence if tax status info is not found in the cache, it is an error.
475
476 p_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
477 p_error_buffer := 'The Tax Status information could not be located in cache.';
478
479 IF (g_level_error >= g_current_runtime_level ) THEN
480 FND_LOG.STRING(g_level_error,
481 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
482 'Could not locate the status record in cache....');
483 END IF;
484
485 -- Set appropriate message and return
486 RETURN;
487 ELSE
488
489 l_tax_rate_rule_flag :=
490 ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl(l_status_index).rule_based_rate_flag;
491 l_effective_from :=
492 ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl(l_status_index).effective_from;
493 l_effective_to :=
494 ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl(l_status_index).effective_to;
495
496 END IF; -- l_status_index is NULL
497
498 IF l_tax_rate_rule_flag = 'Y' THEN
499 -- call rule base detm process to determine tax rate;
500 --
501 ZX_TDS_RULE_BASE_DETM_PVT.rule_base_process(
502 'DET_TAX_RATE',
503 p_structure_name,
504 p_structure_index,
505 p_event_class_rec,
506 l_tax_id,
507 l_tax_status_code,
508 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date,
509 NULL,
510 NULL,
511 l_zx_result_rec,
512 p_return_status,
513 p_error_buffer);
514
515 IF p_return_status IN ( FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR)
516 THEN
517 -- error is raised in rule based evaluation. Abort processing.
518 IF (g_level_error >= g_current_runtime_level ) THEN
519 FND_LOG.STRING(g_level_error,
520 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
521 'Rule Based engine returned error. Aborting... ');
522 END IF;
523 RETURN;
524 END IF;
525
526 l_tax_rate_code:= l_zx_result_rec.alphanumeric_result;
527 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
528 i).rate_result_id := l_zx_result_rec.result_id;
529
530 IF (g_level_statement >= g_current_runtime_level ) THEN
531 FND_LOG.STRING(g_level_statement,
532 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
533 'l_tax_rate_code returned by rule base process is '||
534 l_zx_result_rec.alphanumeric_result);
535
536 END IF;
537 END IF; -- l_Tax_Rate_Rule_Flag = 'Y'
538 END IF; -- get rate code
539
540 l_tax_rate_rec := NULL;
541
542 ZX_TCM_TAX_RATE_PKG.get_tax_rate(
543 p_event_class_rec => p_event_class_rec,
544 p_tax_regime_code => l_tax_regime_code,
545 p_tax_jurisdiction_code => l_tax_jurisdiction_code,
546 p_tax => l_tax,
547 p_tax_date => l_tax_date,
548 p_tax_status_code => l_tax_status_code,
549 p_tax_rate_code => l_tax_rate_code,
550 p_place_of_supply_type_code => ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
551 i).place_of_supply_type_code,
552 p_structure_index => p_structure_index,
553 p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
554 x_tax_rate_rec => l_tax_rate_rec,
555 x_return_status => p_return_status
556 );
557
558 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
559 IF (g_level_error >= g_current_runtime_level ) THEN
560 FND_LOG.STRING(g_level_error,
561 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
562 'Incorrect return_status after calling ' ||
563 'ZX_TCM_TAX_RATE_PKG.get_tax_rate');
564 FND_LOG.STRING(g_level_error,
565 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
566 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-)'||p_return_status);
567 END IF;
568
569 -- in TCM, the error msg already saved on fnd msg stack
570 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
571 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id;
572 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
573 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type;
574
575 ZX_API_PUB.add_msg(
576 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
577
578 RETURN;
579 END IF;
580
581 l_tax_rate_id := l_tax_rate_rec.tax_rate_id;
582 l_tax_rate_code := l_tax_rate_rec.tax_rate_code;
583 l_rate_type_code := l_tax_rate_rec.rate_type_code;
584 l_percentage_rate := l_tax_rate_rec.percentage_rate;
585 l_quantity_rate := l_tax_rate_rec.quantity_rate;
586 l_uom_code := l_tax_rate_rec.uom_code;
587 l_offset_tax_rate_code := l_tax_rate_rec.offset_tax_rate_code;
588 l_offset_status_code := l_tax_rate_rec.offset_status_code;
589 l_offset_tax := l_tax_rate_rec.offset_tax;
590 l_adhoc_tax_rate_flg := l_tax_rate_rec.allow_adhoc_tax_rate_flag;
591 l_allow_exemptions_flag := l_tax_rate_rec.allow_exemptions_flag;
592 l_allow_exceptions_flag := l_tax_rate_rec.allow_exceptions_flag;
593
594 -- if the rate found is not for the most inner jurisdiction, which stamped
595 -- on the tax line in applicability process, then restamp the
596 -- jurisdiction_code and id for which the rate is found on the
597 -- tax line. Here the tax_jurisdition_id is got from he jurisdiction gtt.
598 -- NOTE: multiple_jurisdictions_flag won't change during override event
599
600 IF l_tax_rate_rec.tax_jurisdiction_code IS NOT NULL
601 AND ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_jurisdiction_code
602 <> l_tax_rate_rec.tax_jurisdiction_code
603 THEN
604 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_jurisdiction_code
605 := l_tax_rate_rec.tax_jurisdiction_code;
606
607 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_jurisdiction_id
608 := l_tax_rate_rec.tax_jurisdiction_id;
609
610 END IF; --l_tax_rate_rec.tax_jurisdiction_code IS NOT NULL
611
612
613 IF NOT ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl.EXISTS(l_tax_rate_id) THEN
614 ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl(l_tax_rate_id) := l_tax_rate_rec;
615 END IF;
616
617 IF l_tax_rate_code IS NOT NULL THEN
618
619 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate_code:=
620 l_tax_rate_code;
621 IF l_Rate_Type_Code = 'SLABBED' THEN
622
623 -- slabbed rate not supported in phase 1a;
624 -- UI need to take care of not to allow slabbed tax defined.
625 p_return_status:= FND_API.G_RET_STS_ERROR;
626 p_error_buffer := 'Slabbed rates are not supported in phase 1a ';
627 IF (g_level_error >= g_current_runtime_level ) THEN
628 FND_LOG.STRING(g_level_error,
629 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
630 'p_error_buffer: '|| p_error_buffer);
631 END IF;
632 RETURN;
633
634 elsif l_Rate_Type_Code = 'PERCENTAGE' THEN
635
636 l_tax_rate:= l_percentage_rate;
637
638 else -- quantity
639
640 l_tax_rate:= l_quantity_rate;
641
642 -- For quantity based rates, if the UOM_CODE on the transaction does
643 -- not match the UOM_CODE on the rate, then raise error.
644
645 -- IF l_uom_code <> ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).uom_code THEN
646 IF l_uom_code <>
647 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.uom_code(p_structure_index)
648 THEN
649 -- Raise error;
650 p_return_status:= FND_API.G_RET_STS_ERROR;
651 p_error_buffer := 'UOM_CODE on the transaction does not match the '||
652 ' UOM_CODE on the rate ';
653
654 IF (g_level_error >= g_current_runtime_level ) THEN
655 FND_LOG.STRING(g_level_error,
656 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
657 'The UOM '||l_uom_code||' for quantity rate '||l_tax_rate_code ||
658 ' is not the same as the UOM '||
659 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.uom_code(
660 p_structure_index)
661 ||' on the transaction line. Please fix one to match the other.');
662 END IF;
663
664 FND_MESSAGE.SET_NAME('ZX','ZX_UOM_NOT_MATCH');
665 FND_MESSAGE.SET_TOKEN('UOM_RATE',l_uom_code);
666 FND_MESSAGE.SET_TOKEN('RATE_CODE', l_tax_rate_code );
667 FND_MESSAGE.SET_TOKEN('UOM_TRX',
668 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.uom_code(p_structure_index) );
669
670 -- FND_MSG_PUB.Add;
671 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
672 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id;
673 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
674 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type;
675
676 ZX_API_PUB.add_msg(
677 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
678
679 RETURN;
680 END IF;
681
682 END IF;
683
684 IF (g_level_procedure >= g_current_runtime_level ) THEN
685 FND_LOG.STRING(g_level_procedure,
686 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
687 'tax rate code: ' ||l_tax_rate_code ||' tax_rate = ' || l_tax_rate);
688 END IF;
689
690 ELSE -- if l_tax_rate_code is NULL
691 p_return_status:= FND_API.G_RET_STS_ERROR; -- error
692
693 IF (g_level_error>= g_current_runtime_level ) THEN
694 FND_LOG.STRING(g_level_error,
695 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
696 'Tax_rate_code not found for tax: '|| l_tax ||
697 'tax_status: '||l_tax_status_code);
698 END IF;
699
700 FND_MESSAGE.SET_NAME('ZX','ZX_RATE_NOT_FOUND');
701 FND_MESSAGE.SET_TOKEN('TAX',l_tax);
702 FND_MESSAGE.SET_TOKEN('TAX_STATUS',l_tax_status_code);
703 FND_MESSAGE.SET_TOKEN('DATE',l_tax_date);
704 --FND_MSG_PUB.Add;
705
706 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
707 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id;
708 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
709 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type;
710
711 ZX_API_PUB.add_msg(
712 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
713
714 RETURN;
715
716 END IF; -- l_tax_rate_code is NOT NULL
717
718 IF l_tax_rate IS NULL THEN
719 -- rate_code found from the zx_rates_b table, but no
720 -- rate associated with the rate_code
721 p_return_status:= FND_API.G_RET_STS_ERROR; -- error
722 p_error_buffer := 'Error: No tax rate found';
723 IF (g_level_error >= g_current_runtime_level ) THEN
724 FND_LOG.STRING(g_level_error,
725 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
726 p_error_buffer);
727 END IF;
728 RETURN;
729 END IF;
730
731 -- Bug 4277751: for INTERCOMPANY CREATE and
732 -- offset_flag = 'Y', populate the offset_tax_rate_code and skip
733 -- offset applicability process
734 --
735 IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
736 p_structure_index) IN ('CREATE', 'UPDATE')
737 AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(
738 p_structure_index) = 'INTERCOMPANY_TRX'
739 AND ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Offset_Flag = 'Y'
740 THEN
741
742 -- for intercompany the Offset_Flag on detail tax line marked as 'Y'
743 -- only when the offset_tax_rate_code is not null, so no need to check
744 -- whether the offset_tax_rate_code is null in this case.
745
746 validate_offset_tax (
747 p_tax_regime_code => l_tax_regime_code,
748 p_tax => l_offset_tax,
749 p_tax_determine_date => l_tax_date,
750 p_tax_status_code => l_offset_status_code,
751 p_tax_jurisdiction_code => l_tax_jurisdiction_code, --? does offset tax rate has jurisdiction info
752 p_tax_class => l_tax_class,
753 p_tax_rate_code => l_offset_tax_rate_code,
754 x_return_status => p_return_status,
755 x_error_buffer => p_error_buffer
756 );
757
758 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
759 p_error_buffer := 'Need to create Offset Tax. But offset tax is not valid'; -- will be replaced with coded message
760
761 IF g_level_error >= g_current_runtime_level THEN
762 FND_LOG.STRING(g_level_error,
763 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
764 'Need to create Offset Tax. But offset tax is not valid');
765 FND_LOG.STRING(g_level_error,
766 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
767 'RETURN_STATUS = ' || p_return_status);
768 FND_LOG.STRING(g_level_error,
769 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
770 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-)');
771 END IF;
772 RETURN;
773 ELSE
774 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
775 i).offset_tax_rate_code := l_offset_tax_rate_code;
776 END IF;
777
778 ELSIF p_event_class_rec.allow_offset_tax_calc_flag = 'Y' THEN
779
780 -- Check the value of p_event_class_rec.allow_offset_tax_calc_flag to
781 -- determine if it is necessary to perform offset tax applicability process
782 --
783 -- perform applicability process for offset tax;
784 --
785 IF l_offset_tax_rate_code IS NOT NULL THEN
786
787 validate_offset_tax (
788 p_tax_regime_code => l_tax_regime_code,
789 p_tax => l_offset_tax,
790 p_tax_determine_date => l_tax_date,
791 p_tax_status_code => l_offset_status_code,
792 p_tax_jurisdiction_code => l_tax_jurisdiction_code,
793 p_tax_class => l_tax_class,
794 p_tax_rate_code => l_offset_tax_rate_code,
795 x_return_status => p_return_status,
796 x_error_buffer => p_error_buffer
797 );
798
799 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
800
801 IF (g_level_statement >= g_current_runtime_level ) THEN
802 FND_LOG.STRING(g_level_statement,
803 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
804 'Offset tax not found, reset the return status and continue the process');
805 END IF;
806 p_return_status := FND_API.G_RET_STS_SUCCESS;
807
808 ELSE -- p_return_status = FND_API.G_RET_STS_SUCCESS
809
810 l_reg_party_type:= p_event_class_rec.Offset_Tax_Basis_Code || '_TAX_PROF_ID';
811
812 -- Bug 7208618
813 --
814 l_account_type_code := p_event_class_rec.sup_cust_acct_type;
815 l_first_party_flag := ZX_TDS_RULE_BASE_DETM_PVT.evaluate_if_first_party(l_reg_party_type);
816
817 IF (NOT l_first_party_flag) AND l_account_type_code = 'SUPPLIER' THEN
818
819 IF SUBSTR(l_reg_party_type, 1, 14) IN ('SHIP_FROM_SITE', 'BILL_FROM_SITE') OR
820 SUBSTR(l_reg_party_type, 1, 12) IN ('SHIP_TO_SITE', 'BILL_TO_SITE')
821 THEN
822
823 -- get l_account_site_id
824 --
825 ZX_TDS_RULE_BASE_DETM_PVT.get_tsrm_num_value(
826 p_structure_name,
827 p_structure_index,
828 SUBSTR(l_reg_party_type,1,5) || 'THIRD_PTY_ACCT_SITE_ID',
829 l_account_site_id,
830 p_return_status,
831 p_error_buffer);
832
833 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
834 IF (g_level_error >= g_current_runtime_level ) THEN
835 FND_LOG.STRING(g_level_error,
836 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
837 'Incorrect return_status after calling ' ||
838 'ZX_TDS_RULE_BASE_DETM_PVT.get_tsrm_num_value');
839 FND_LOG.STRING(g_level_error,
840 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
841 'l_account_site_id = ' || l_account_site_id);
842 FND_LOG.STRING(g_level_error,
843 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
844 'RETURN_STATUS = ' || p_return_status);
845 FND_LOG.STRING(g_level_error,
846 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
847 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-)');
848 END IF;
849 RETURN;
850 END IF;
851
852 -- get l_account_id
853 --
854 ZX_TDS_RULE_BASE_DETM_PVT.get_tsrm_num_value(
855 p_structure_name,
856 p_structure_index,
857 SUBSTR(l_reg_party_type,1,5) || 'THIRD_PTY_ACCT_ID',
858 l_account_id,
859 p_return_status,
860 p_error_buffer);
861
862 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
863 IF (g_level_error >= g_current_runtime_level ) THEN
864 FND_LOG.STRING(g_level_error,
865 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
866 'Incorrect return_status after calling ' ||
867 'ZX_TDS_RULE_BASE_DETM_PVT.get_tsrm_num_value');
868 FND_LOG.STRING(g_level_error,
869 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
870 'l_account_id = ' || l_account_id);
871 FND_LOG.STRING(g_level_error,
872 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
873 'RETURN_STATUS = ' || p_return_status);
874 FND_LOG.STRING(g_level_error,
875 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
876 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-)');
877 END IF;
878 RETURN;
879 END IF;
880
881
882 OPEN get_ap_supplier_site_csr(l_account_id, l_account_site_id);
883 FETCH get_ap_supplier_site_csr INTO l_offset_tax_appl;
884 CLOSE get_ap_supplier_site_csr;
885
886 IF (g_level_error >= g_current_runtime_level ) THEN
887 FND_LOG.STRING(g_level_error,
888 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
889 'l_account_site_id = '||l_account_site_id);
890 FND_LOG.STRING(g_level_error,
891 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
892 'l_offset_tax_appl = '||l_offset_tax_appl);
893 END IF;
894
895 ELSIF SUBSTR(l_reg_party_type, 1,15) IN ('SHIP_FROM_PARTY', 'BILL_FROM_PARTY') OR
896 SUBSTR(l_reg_party_type, 1,13) IN ('SHIP_TO_PARTY', 'BILL_TO_PARTY')
897 THEN
898
899 -- get l_account_id
900 --
901 ZX_TDS_RULE_BASE_DETM_PVT.get_tsrm_num_value(
902 p_structure_name,
903 p_structure_index,
904 SUBSTR(l_reg_party_type,1,5) || 'THIRD_PTY_ACCT_ID',
905 l_account_id,
906 p_return_status,
907 p_error_buffer);
908
909 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
910 IF (g_level_error >= g_current_runtime_level ) THEN
911 FND_LOG.STRING(g_level_error,
912 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
913 'Incorrect return_status after calling ' ||
914 'ZX_TDS_RULE_BASE_DETM_PVT.get_tsrm_num_value');
915 FND_LOG.STRING(g_level_error,
916 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
917 'l_account_id = ' || l_account_id);
918 FND_LOG.STRING(g_level_error,
919 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
920 'RETURN_STATUS = ' || p_return_status);
921 FND_LOG.STRING(g_level_error,
922 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
923 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-)');
924 END IF;
925 RETURN;
926 END IF;
927
928 OPEN get_ap_supplier_csr(l_account_id);
929 FETCH get_ap_supplier_csr INTO l_offset_tax_appl;
930 CLOSE get_ap_supplier_csr;
931
932 IF (g_level_error >= g_current_runtime_level ) THEN
933 FND_LOG.STRING(g_level_error,
934 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
935 'l_account_id = '||l_account_id);
936 FND_LOG.STRING(g_level_error,
937 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
938 'l_offset_tax_appl= '||l_offset_tax_appl);
939 END IF;
940
941 END IF; -- l_reg_party_type = 'PARTY' OR 'SITE'
942 END IF; -- l_account_type_code = 'SUPPLIER'
943 -- Bug 7208618 ends
944
945 IF l_offset_tax_appl IS NULL THEN
946
947 ZX_GET_TAX_PARAM_DRIVER_PKG.get_driver_value(
948 p_structure_name,
949 p_structure_index,
950 l_reg_party_type,
951 l_ptp_id,
952 p_return_status );
953
954 OPEN get_ptp_cur(l_ptp_id);
955 FETCH get_ptp_cur into l_offset_tax_appl;
956 CLOSE get_ptp_cur;
957
958 IF (g_level_error >= g_current_runtime_level ) THEN
959 FND_LOG.STRING(g_level_error,
960 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
961 'l_offset_tax_appl(from PTP) = '||l_offset_tax_appl);
962 END IF;
963
964 END IF;
965
966 --ZX_TDS_RULE_BASE_DETM_PVT.rule_base_process(
967 -- 'DET_OFFSET_TAX',
968 -- p_structure_name,
969 -- p_structure_index,
970 -- p_event_class_rec,
971 -- l_tax_id,
972 -- l_tax_status_code,
973 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date,
974 -- l_offset_rule_code,
975 -- null,
976 -- l_zx_result_rec,
977 -- p_return_status,
978 -- p_error_buffer);
979 --
980 --IF p_return_status in (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR)
981 --THEN
982 -- -- Error is raised in rule based evaluation. Abort processing.
983 -- --
984 -- IF (g_level_statement >= g_current_runtime_level ) THEN
985 -- FND_LOG.STRING(g_level_statement,
986 -- 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
987 -- 'Rule Based engine returned error. Aborting... ');
988 -- END IF;
989 -- p_return_status:= FND_API.G_RET_STS_ERROR;
990 -- p_error_buffer := 'Rule Based engine returned error during offset tax determination ';
991 --
992 -- IF (g_level_statement >= g_current_runtime_level ) THEN
993 -- FND_LOG.STRING(g_level_statement,
994 -- 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
995 -- 'p_error_buffer: '|| p_error_buffer);
996 -- END IF;
997 -- RETURN;
998 --ELSE
999 -- l_offset_tax_appl:= l_zx_result_rec.alphanumeric_result;
1000
1001 IF nvl(l_offset_tax_appl,'N') = 'Y' THEN
1002
1003 IF (g_level_procedure >= g_current_runtime_level ) THEN
1004
1005 FND_LOG.STRING(g_level_procedure,
1006 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1007 'Offset tax applicable...');
1008 END IF;
1009
1010 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Offset_Flag := 'Y';
1011
1012
1013 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1014 i).offset_tax_rate_code := l_offset_tax_rate_code;
1015 --ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1016 -- i).offset_tax_line_number :=
1017 -- NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_max_tax_line_number,0)+ 1;
1018 ZX_TDS_CALC_SERVICES_PUB_PKG.g_max_tax_line_number :=
1019 NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_max_tax_line_number,0)+ 1;
1020
1021 --IF (g_level_statement >= g_current_runtime_level ) THEN
1022 -- FND_LOG.STRING(g_level_statement,
1023 -- 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1024 -- 'offset_tax_line_number = '||
1025 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1026 -- i).offset_tax_line_number);
1027 --END IF;
1028 ELSE
1029 IF (g_level_statement >= g_current_runtime_level ) THEN
1030 FND_LOG.STRING(g_level_statement,
1031 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1032 'Offset tax not applicable...');
1033 END IF;
1034
1035 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Offset_Flag := 'N';
1036 END IF; -- l_offset_tax_appl
1037 --END IF; -- p_return_status
1038 END IF; -- p_return_status after validate_offset_tax
1039 END IF; -- l_offset_tax_rate_code
1040 END IF; -- p_event_class_rec.allow_offset_tax_calc_flag = 'Y'
1041
1042 -- When tax_event_type in the Event Class structure is 'OVERRIDE_TAX', tax rate
1043 -- determination will only be done when last_manual_entry = 'TAX_STATUS'.
1044 -- After tax rate is determined for the line whose last_manual_entry =
1045 -- 'TAX_STATUS', last_manual_entry will be updated to 'STATUSTORATE'.
1046
1047
1048 IF nvl(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry,'N') = 'TAX_STATUS'
1049 THEN
1050 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry := 'STATUSTORATE';
1051
1052 END IF;
1053
1054 -- Bug 3973763: If direc_rate_result_id IS NOT NULL, check if
1055 -- numeric_result is populated. If yes, check if tax is adhoc. If yes,
1056 -- populate this value to tax_rate field, if not, check if
1057 -- l_tax_rate = l_numeric_result. If not, raise error.
1058 --
1059 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).direct_rate_result_id IS NOT NULL
1060 THEN
1061
1062 OPEN get_numeric_value_csr(
1063 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).direct_rate_result_id);
1064 FETCH get_numeric_value_csr INTO l_numeric_result;
1065 CLOSE get_numeric_value_csr;
1066
1067 IF l_numeric_result IS NOT NULL THEN
1068
1069 IF l_adhoc_tax_rate_flg = 'Y' THEN
1070
1071 l_tax_rate := l_numeric_result;
1072
1073 ELSE
1074 IF l_tax_rate <> l_numeric_result THEN
1075
1076 -- Raise error
1077 --
1078 p_return_status:= FND_API.G_RET_STS_ERROR;
1079 p_error_buffer := 'Different Exception rate is specified for non-adhoc tax.';
1080
1081 IF (g_level_error >= g_current_runtime_level) THEN
1082 FND_LOG.STRING(g_level_error,
1083 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1084 p_error_buffer);
1085 FND_LOG.STRING(g_level_error,
1086 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1087 'l_tax_rate = ' ||l_tax_rate);
1088 FND_LOG.STRING(g_level_error,
1089 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1090 'l_numeric_result = ' || l_numeric_result);
1091 FND_LOG.STRING(g_level_error,
1092 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
1093 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-) ');
1094 END IF;
1095 RETURN;
1096 END IF;
1097 END IF;
1098 END IF;
1099 END IF;
1100
1101 --bug6604498
1102
1103 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).exception_rate IS NOT NULL THEN
1104 l_tax_rate := ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).exception_rate;
1105 END IF;
1106
1107 -- Process Tax Exceptions
1108 IF l_allow_exceptions_flag ='Y' THEN
1109 IF p_event_class_rec.prod_family_grp_code = 'O2C' AND
1110 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_from_party_id.EXISTS(p_structure_index) THEN
1111 l_inventory_org_id := nvl(ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_from_party_id(p_structure_index),
1112 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(p_structure_index));
1113 ELSIF p_event_class_rec.prod_family_grp_code = 'P2P' AND
1114 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_to_party_id.EXISTS(p_structure_index) THEN
1115 l_inventory_org_id := nvl(ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_to_party_id(p_structure_index),
1116 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(p_structure_index));
1117 ELSE
1118 l_inventory_org_id := ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(p_structure_index);
1119 END IF;
1120
1121 ZX_TCM_GET_EXCEPT_PKG.get_tax_exceptions(
1122 p_inventory_item_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_id(p_structure_index),
1123 p_inventory_organization_id => l_inventory_org_id,
1124 p_product_category => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_category(p_structure_index),
1125 p_tax_regime_code => l_tax_regime_code,
1126 p_tax => l_tax,
1127 p_tax_status_code => l_tax_status_code,
1128 p_tax_rate_code => l_tax_rate_code,
1129 p_trx_date => l_tax_date,
1130 p_tax_jurisdiction_id => l_tax_jurisdiction_id,
1131 p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
1132 x_exception_rec => l_exception_rec,
1133 x_return_status => p_return_status
1134 );
1135
1136 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1137 IF (g_level_error >= g_current_runtime_level ) THEN
1138 FND_LOG.STRING(g_level_error,
1139 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1140 'Incorrect return_status after calling ' ||
1141 'ZX_TCM_GET_EXCEPT_PKG.get_tax_exceptions');
1142 FND_LOG.STRING(g_level_error,
1143 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
1144 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-)'||p_return_status);
1145 END IF;
1146
1147 RETURN;
1148 END IF;
1149
1150 IF l_exception_rec.tax_exception_id IS NOT NULL THEN
1151 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1152 i).TAX_EXCEPTION_ID := l_exception_rec.tax_exception_id;
1153
1154 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1155 i).EXCEPTION_RATE := l_exception_rec.exception_rate;
1156
1157 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1158 i).TAX_RATE_BEFORE_EXCEPTION := l_tax_rate;
1159
1160 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1161 i).TAX_RATE_NAME_BEFORE_EXCEPTION := l_tax_rate_code;
1162
1163 IF l_exception_rec.exception_type_code = 'SPECIAL_RATE' THEN
1164 l_tax_rate := l_exception_rec.exception_rate;
1165 ELSE -- l_exception_rec.exception_type_code = 'DISCOUNT' THEN
1166 l_tax_rate := l_tax_rate*(100 - l_exception_rec.exception_rate)/100;
1167 END IF;
1168 END IF;
1169
1170 END IF;
1171
1172 -- Process Tax Exemptions
1173 IF p_event_class_rec.allow_exemptions_flag ='Y'
1174 AND l_allow_exemptions_flag ='Y'
1175 THEN
1176
1177 l_exemption_rec := NULL;
1178
1179 IF ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(
1180 l_tax_id).tax_exmpt_source_tax IS NOT NULL
1181 THEN
1182 IF (g_level_statement >= g_current_runtime_level ) THEN
1183 FND_LOG.STRING(g_level_statement,
1184 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1185 'Current tax has a source tax: ' ||
1186 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).tax_exmpt_source_tax);
1187
1188 END IF;
1189
1190 FOR i IN REVERSE NVL(l_exempt_info_tbl.FIRST, 0)..NVL(l_exempt_info_tbl.LAST, -1) LOOP
1191
1192
1193 IF l_exempt_info_tbl(i).tax_regime_code = l_tax_regime_code AND
1194 l_exempt_info_tbl(i).tax = ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(
1195 l_tax_id).tax_exmpt_source_tax
1196 THEN
1197
1198 l_exemption_rec.exemption_id := l_exempt_info_tbl(i).exemption_id;
1199 l_exemption_rec.percent_exempt := l_exempt_info_tbl(i).percent_exempt;
1200 l_exemption_rec.discount_special_rate := l_exempt_info_tbl(i).discount_special_rate;
1201 EXIT;
1202 END IF;
1203 END LOOP;
1204 END IF;
1205
1206 IF l_exemption_rec.exemption_id IS NULL THEN
1207 IF (g_level_statement >= g_current_runtime_level ) THEN
1208 FND_LOG.STRING(g_level_statement,
1209 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1210 'There is no source tax or no source tax exemptions info '||
1211 'calling TCM exemption API to get exemption info.');
1212 END IF;
1213
1214 /* Bug Number: 6328797 - According to 11i , The exemptions should work at
1215 -> Ship_To For Customer Site level.
1216 -> Bill_to For Customer Level
1217 So we need to pass ship_to_site_tax_prof_id for finding the exemptions.
1218 But previously we were passing Bill_to_site_tax_prof_id to find the exemptions.
1219 So, chaged passing variable to ship_to_site_tax_prof_id.
1220
1221 But to avoid the structure changes, we are not changing the Naming Convention.
1222 If we change the Structure Chages we need to do in lot of packages.
1223 So we are keeping the name as bill_to only but changing the passing value.
1224 */
1225
1226 IF p_event_class_rec.prod_family_grp_code = 'O2C' AND
1227 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_from_party_id.EXISTS(p_structure_index) THEN
1228 l_inventory_org_id := nvl(ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_from_party_id(p_structure_index),
1229 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(p_structure_index));
1230 ELSIF p_event_class_rec.prod_family_grp_code = 'P2P' AND
1231 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_to_party_id.EXISTS(p_structure_index) THEN
1232 l_inventory_org_id := nvl(ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_to_party_id(p_structure_index),
1233 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(p_structure_index));
1234 ELSE
1235 l_inventory_org_id := ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(p_structure_index);
1236 END IF;
1237
1238 ZX_TCM_GET_EXEMPT_PKG.get_tax_exemptions(
1239 p_bill_to_cust_site_use_id => NVL(ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_to_cust_acct_site_use_id(p_structure_index),
1240 ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_cust_acct_site_use_id(p_structure_index)), -- 7625597
1241 p_bill_to_cust_acct_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_third_pty_acct_id(p_structure_index),
1242 p_bill_to_party_site_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.ship_to_site_tax_prof_id(p_structure_index),
1243 p_bill_to_party_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_party_tax_prof_id(p_structure_index),
1244 p_sold_to_party_site_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trading_hq_site_tax_prof_id(p_structure_index),
1245 p_sold_to_party_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trading_hq_party_tax_prof_id(p_structure_index),
1246 p_inventory_org_id => l_inventory_org_id,
1247 p_inventory_item_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_id(p_structure_index),
1248 p_exempt_certificate_number => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_certificate_number(p_structure_index),
1249 p_reason_code => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_reason_code(p_structure_index),
1250 p_exempt_control_flag => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exemption_control_flag(p_structure_index),
1251 p_tax_date => l_tax_date,
1252 p_tax_regime_code => l_tax_regime_code,
1253 p_tax => l_tax,
1254 p_tax_status_code => l_tax_status_code,
1255 p_tax_rate_code => l_tax_rate_code,
1256 p_tax_jurisdiction_id => l_tax_jurisdiction_id,
1257 p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
1258 p_event_class_rec => p_event_class_rec,
1259 x_return_status => p_return_status,
1260 x_exemption_rec => l_exemption_rec);
1261
1262 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1263 IF (g_level_error >= g_current_runtime_level ) THEN
1264 FND_LOG.STRING(g_level_error,
1265 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1266 'Incorrect return_status after calling ' ||
1267 ' ZX_TCM_GET_EXEMPT_PKG.get_tax_exemptions');
1268 FND_LOG.STRING(g_level_error,
1269 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
1270 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-)'||p_return_status);
1271 END IF;
1272
1273 -- in TCM, the error msg already saved on fnd msg stack
1274 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
1275 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id;
1276 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
1277 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type;
1278
1279 ZX_API_PUB.add_msg(
1280 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
1281
1282 RETURN;
1283 END IF;
1284
1285 IF l_exemption_rec.apply_to_lower_levels_flag = 'Y' THEN
1286
1287 l_ind := NVL(l_exempt_info_tbl.LAST, 0) + 1;
1288 l_exempt_info_tbl(l_ind).tax_regime_code := l_tax_regime_code;
1289 l_exempt_info_tbl(l_ind).tax := l_tax;
1290 l_exempt_info_tbl(l_ind).exemption_id := l_exemption_rec.exemption_id;
1291 l_exempt_info_tbl(l_ind).percent_exempt := l_exemption_rec.percent_exempt;
1292 l_exempt_info_tbl(l_ind).discount_special_rate := l_exemption_rec.discount_special_rate;
1293 END IF;
1294
1295 END IF;
1296
1297 IF l_exemption_rec.exemption_id IS NOT NULL THEN
1298
1299 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1300 i).tax_exemption_id := l_exemption_rec.exemption_id;
1301
1302 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1303 i).tax_rate_before_exemption := l_tax_rate;
1304
1305 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1306 i).tax_rate_name_before_exemption := l_tax_rate_code;
1307
1308 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1309 -- i).exempt_certificate_number
1310 -- := ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_certificate_number(
1311 -- p_structure_index);
1312
1313 --ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1314 -- i).exempt_reason
1315 -- := ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_reason(
1316 -- p_structure_index);
1317
1318 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
1319 -- i).exempt_reason_code
1320 -- := ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_reason_code(
1321 -- p_structure_index);
1322 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).exempt_certificate_number
1323 := l_exemption_rec.exempt_certificate_number;
1324
1325 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).exempt_reason
1326 := l_exemption_rec.exempt_reason;
1327
1328 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).exempt_reason_code
1329 := l_exemption_rec.exempt_reason_code;
1330
1331
1332 IF l_exemption_rec.discount_special_rate = 'SPECIAL_RATE' THEN
1333
1334 IF l_tax_rate <> 0 THEN
1335 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).exempt_rate_modifier
1336 := l_exemption_rec.percent_exempt / l_tax_rate;
1337 l_tax_rate := l_exemption_rec.percent_exempt;
1338 ELSE
1339 IF (g_level_statement >= g_current_runtime_level ) THEN
1340 FND_LOG.STRING(g_level_statement,
1341 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1342 'tax_rate is Zero, no exemption needed. ' );
1343 END IF;
1344 END IF;
1345
1346 ELSE -- l_exemption_rec.discount_special_rate = 'DISCOUNT' THEN
1347 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).exempt_rate_modifier
1348 := (100 - l_exemption_rec.percent_exempt)/100;
1349
1350 l_tax_rate := l_tax_rate*(100 - l_exemption_rec.percent_exempt)/100;
1351
1352 END IF;
1353
1354 END IF; -- l_exemption_rec.exemption_id IS NOT NULL
1355
1356 END IF; -- p_event_class_rec.allow_exemptions_flag ='Y'
1357
1358 update_tax_rate(-- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl,
1359 i,
1360 l_tax_rate_code,
1361 l_tax_rate,
1362 l_tax_rate_id,
1363 l_Rate_Type_Code);
1364
1365
1366 ELSE -- default case of WHEN statement
1367
1368 -- ***** If the rate is available through tax group expansion, then
1369 -- ***** should the validation of rate be done ?
1370
1371 -- If the rate is available then validate whether the tax rate can be used
1372 -- for the transaction
1373 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_rate is NOT NULL
1374 -- AND ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_provider_id is NULL
1375 -- AND ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Delete_Flag <> 'Y'
1376 THEN
1377 -- check whether the tax rate is ad-hoc
1378
1379 /* commented out for bug 3420310, the following check should have been done on the UI
1380 l_status_index := ZX_TDS_UTILITIES_PKG.get_tax_status_index(
1381 l_tax,
1382 l_tax_regime_code,
1383 l_tax_status_code);
1384
1385
1386 IF l_status_index is NULL THEN
1387
1388 -- Tax Status Determination must always populate the Tax Status Information
1389 -- in the cache; Hence if tax status info is not found in the cache,
1390 -- it is an error.
1391
1392 p_return_status:= FND_API.G_RET_STS_ERROR;
1393 p_error_buffer := 'The Tax Status information could not be located in cache.';
1394 IF (g_level_error >= g_current_runtime_level ) THEN
1395 FND_LOG.STRING(g_level_error,
1396 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1397 'Could not locate the status record in cache....');
1398 END IF;
1399 -- Set appropriate message and return
1400 RETURN;
1401 ELSE
1402 -- bug fix 3420310
1403 -- Allow_Adhoc_Tax_Rate_Flag moved to zx_rates_b table, g_tax_status_info_tbl changed accordingly
1404 -- l_adhoc_tax_rate_flg :=
1405 -- ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl(l_status_index).Allow_Adhoc_Tax_Rate_Flag;
1406 l_adhoc_tax_rate_flg :=
1407 ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl(l_status_index).Allow_Adhoc_Tax_Rate_Flag;
1408
1409 END IF; -- l_status_index is NULL
1410
1411 -- If the tax code is not adhoc then the user cannot specify tax rate on
1412 -- the tax line. if this was an override case, and if last_manual_entry
1413 -- is Tax Amount, then the rate should not be available on the Tax Line.
1414
1415 IF l_adhoc_tax_rate_flg <> 'Y'
1416 AND ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_manual_entry = 'TAX_RATE' THEN
1417
1418 p_return_status:= FND_API.G_RET_STS_ERROR;
1419 p_error_buffer := 'You cannot specify a rate percentage on a tax which '||
1420 ' is not ad-hoc';
1421 IF (g_level_error >= g_current_runtime_level ) THEN
1422 FND_LOG.STRING(g_level_error,
1423 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1424 'A tax rate cannot be specified on a tax status which '||
1425 'does not allow ad-hoc rates');
1426 END IF;
1427 RETURN;
1428
1429 END IF;
1430 end commented out for bug 3420310 */
1431
1432 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Copied_From_Other_Doc_Flag = 'Y'
1433 AND ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).Manually_Entered_Flag <> 'Y'
1434 THEN
1435 -- The tax was prorated based on reference document; validate that
1436 -- the tax rate is valid for the transaction date.
1437
1438 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date >=
1439 ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl(l_status_index).effective_from
1440 and ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date <=
1441 ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl(l_status_index).effective_to
1442 THEN
1443 NULL;
1444
1445 else
1446 p_return_status:= FND_API.G_RET_STS_ERROR;
1447 p_error_buffer := 'The rate is not valid for the tax determination date'||
1448 to_char(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_determine_date );
1449 IF (g_level_error >= g_current_runtime_level ) THEN
1450 FND_LOG.STRING(g_level_error,
1451 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate',
1452 'p_error_buffer: '|| p_error_buffer);
1453 END IF;
1454 END IF;
1455
1456 END IF;
1457 END IF;
1458
1459
1460 END CASE; -- rate determination
1461
1462 END IF; -- Intercompany Transaction check
1463
1464 end loop; -- for tax i.
1465
1466 IF (g_level_procedure >= g_current_runtime_level ) THEN
1467 FND_LOG.STRING(g_level_procedure,
1468 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.get_tax_rate.END',
1469 'ZX_TDS_RATE_DETM_PKG.get_tax_rate(-) ');
1470 END IF;
1471
1472 EXCEPTION
1473 WHEN OTHERS THEN
1474 p_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1475 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1476
1477 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1478 FND_LOG.STRING(g_level_unexpected,
1479 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.GET_TAX_RATE',
1480 p_error_buffer);
1481 END IF;
1482
1483
1484 END GET_TAX_RATE;
1485
1486
1487
1488 PROCEDURE UPDATE_TAX_RATE(
1489 -- ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl in out nocopy zx_api_pub.detail_tax_line_tbl_type,
1490 p_tax_line_index in number,
1491 p_tax_rate_code in zx_lines.tax_rate_code%TYPE,
1492 p_tax_rate in zx_lines.tax_rate%TYPE,
1493 p_tax_rate_id in number,
1494 p_Rate_Type_Code in zx_rates_b.Rate_Type_Code%TYPE) IS
1495
1496 begin
1497
1498 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1499
1500 IF (g_level_procedure >= g_current_runtime_level ) THEN
1501 FND_LOG.STRING(g_level_procedure,
1502 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.update_tax_rate.BEGIN',
1503 'ZX_TDS_RATE_DETM_PKG.update_tax_rate(+) ');
1504 END IF;
1505
1506 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_tax_line_index).tax_rate_code:= p_tax_rate_code;
1507 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_tax_line_index).tax_rate:= p_tax_rate;
1508 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_tax_line_index).legal_message_rate := NULL;
1509 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_tax_line_index).tax_rate_id := p_tax_rate_id ;
1510 ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_tax_line_index).tax_rate_type := p_Rate_Type_Code;
1511
1512 IF (g_level_procedure >= g_current_runtime_level ) THEN
1513 FND_LOG.STRING(g_level_procedure,
1514 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.update_tax_rate.END',
1515 'ZX_TDS_RATE_DETM_PKG.update_tax_rate(-) ');
1516 END IF;
1517
1518 EXCEPTION
1519 WHEN OTHERS THEN
1520
1521 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1522 FND_LOG.STRING(g_level_unexpected,
1523 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.update_tax_rate',
1524 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1525 END IF;
1526
1527 end update_tax_rate;
1528 ------------------------------------------------------------------------------
1529 -- PRIVATE PROCEDURE
1530 -- validate_offset_tax
1531 --
1532 -- DESCRIPTION
1533 -- This procedure calls utility pkg to validate the tax, tax_status and tax_rate.
1534 -- If validation succeed, utility pkg will store the tax, status, rate info in
1535 -- the global cache structure.
1536 --
1537 -- Created by lxzhang for bug fix 5118526
1538 ------------------------------------------------------------------------------
1539
1540 PROCEDURE validate_offset_tax (
1541 p_tax_regime_code IN zx_regimes_b.tax_regime_code%TYPE,
1542 p_tax IN zx_taxes_b.tax%TYPE,
1543 p_tax_determine_date IN DATE,
1544 p_tax_status_code IN zx_status_b.tax_status_code%TYPE,
1545 p_tax_jurisdiction_code IN zx_jurisdictions_b.tax_jurisdiction_code%TYPE,
1546 p_tax_class IN zx_rates_b.tax_class%TYPE,
1547 p_tax_rate_code IN zx_rates_b.tax_rate_code%TYPE,
1548 x_return_status OUT NOCOPY VARCHAR2,
1549 x_error_buffer OUT NOCOPY VARCHAR2
1550 ) IS
1551
1552 /* Bug#5417753- use cache structure
1553 CURSOR get_offset_info_csr
1554 (c_tax_rate_code ZX_RATES_B.TAX_RATE_CODE%TYPE,
1555 c_tax ZX_TAXES_B.TAX%TYPE,
1556 c_tax_regime_code ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
1557 c_tax_status_code ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
1558 c_tax_determine_date ZX_DETAIL_TAX_LINES_GT.TAX_DETERMINE_DATE%TYPE)
1559 IS
1560 SELECT tax_rate_id
1561 FROM ZX_SCO_RATES_B_V -- Bug#5395227
1562 WHERE tax_rate_code = c_tax_rate_code AND
1563 tax = c_tax AND
1564 tax_regime_code = c_tax_regime_code AND
1565 tax_status_code = c_tax_status_code AND
1566 active_flag = 'Y' AND
1567 c_tax_determine_date >= effective_from AND
1568 (c_tax_determine_date <= effective_to OR
1569 effective_to IS NULL)
1570 ORDER BY subscription_level_code; -- Bug#5395227
1571 */
1572
1573 l_tax_rate_id NUMBER;
1574 l_offset_tax_rec ZX_TDS_UTILITIES_PKG.zx_tax_info_cache_rec;
1575 l_offet_tax_status_rec ZX_TDS_UTILITIES_PKG.zx_status_info_rec;
1576 l_offset_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
1577 BEGIN
1578 IF (g_level_procedure >= g_current_runtime_level ) THEN
1579 FND_LOG.STRING(g_level_procedure,
1580 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax.BEGIN',
1581 'ZX_TDS_RATE_DETM_PKG: validate_offset_tax(+)');
1582 END IF;
1583
1584 x_return_status := FND_API.G_RET_STS_SUCCESS;
1585
1586 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
1587 p_tax_regime_code,
1588 p_tax,
1589 p_tax_determine_date,
1590 l_offset_tax_rec,
1591 x_return_status,
1592 x_error_buffer);
1593
1594 IF NVL(x_return_status, FND_API.G_RET_STS_UNEXP_ERROR) <> FND_API.G_RET_STS_SUCCESS THEN
1595 IF (g_level_error >= g_current_runtime_level ) THEN
1596 FND_LOG.STRING(g_level_error,
1597 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax',
1598 'Incorrect return status after calling ZX_TDS_UTILITIES_PKG.get_tax_cache_info');
1599 FND_LOG.STRING(g_level_error,
1600 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax.END',
1601 'ZX_TDS_RATE_DETM_PKG: validate_offset_tax(-)');
1602 END IF;
1603 RETURN;
1604 END IF;
1605
1606 ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info(
1607 p_tax,
1608 p_tax_regime_code,
1609 p_tax_status_code,
1610 p_tax_determine_date,
1611 l_offet_tax_status_rec,
1612 x_return_status,
1613 x_error_buffer);
1614
1615 IF NVL(x_return_status, FND_API.G_RET_STS_UNEXP_ERROR) <> FND_API.G_RET_STS_SUCCESS THEN
1616 IF (g_level_error >= g_current_runtime_level ) THEN
1617 FND_LOG.STRING(g_level_error,
1618 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax',
1619 'Incorrect return status after calling ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info');
1620 FND_LOG.STRING(g_level_error,
1621 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax.END',
1622 'ZX_TDS_RATE_DETM_PKG: validate_offset_tax(-)');
1623 END IF;
1624 RETURN;
1625 END IF;
1626
1627 /* Bug#5417753- use cache structure
1628 OPEN get_offset_info_csr(p_tax_rate_code,
1629 p_tax,
1630 p_tax_regime_code,
1631 p_tax_status_code,
1632 p_tax_determine_date);
1633
1634 FETCH get_offset_info_csr INTO l_tax_rate_id;
1635 IF get_offset_info_csr%NOTFOUND THEN
1636 x_return_status := FND_API.G_RET_STS_ERROR;
1637 x_error_buffer := 'No data found for the specified tax rate '; -- will replace with coded message
1638 IF (g_level_error >= g_current_runtime_level ) THEN
1639 FND_LOG.STRING(g_level_error,
1640 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax',
1641 'No data found for the specified offset tax rate');
1642 END IF;
1643 END IF;
1644 CLOSE get_offset_info_csr;
1645 */
1646
1647 ZX_TDS_UTILITIES_PKG.get_tax_rate_info(
1648 p_tax_regime_code,
1649 p_tax,
1650 p_tax_jurisdiction_code,
1651 p_tax_status_code,
1652 p_tax_rate_code,
1653 p_tax_determine_date,
1654 p_tax_class,
1655 l_offset_tax_rate_rec,
1656 x_return_status,
1657 x_error_buffer);
1658
1659 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1660 l_tax_rate_id := l_offset_tax_rate_rec.tax_rate_id;
1661
1662 ELSE
1663 IF (g_level_error >= g_current_runtime_level ) THEN
1664 FND_LOG.STRING(g_level_error,
1665 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax',
1666 'Incorrect return status after calling ZX_TDS_UTILITIES_PKG.get_tax_rate_info');
1667 FND_LOG.STRING(g_level_error,
1668 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax.END',
1669 'ZX_TDS_RATE_DETM_PKG: validate_offset_tax(-)');
1670 END IF;
1671 RETURN;
1672
1673 END IF;
1674
1675 IF (g_level_procedure >= g_current_runtime_level ) THEN
1676 FND_LOG.STRING(g_level_procedure,
1677 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax.END',
1678 'ZX_TDS_RATE_DETM_PKG: validate_offset_tax(-)');
1679 END IF;
1680 EXCEPTION
1681 WHEN OTHERS THEN
1682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1683
1684 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1685 FND_LOG.STRING(g_level_unexpected,
1686 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax',
1687 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1688 FND_LOG.STRING(g_level_unexpected,
1689 'ZX.PLSQL.ZX_TDS_RATE_DETM_PKG.validate_offset_tax.END',
1690 'ZX_TDS_RATE_DETM_PKG: validate_offset_tax(-)');
1691 END IF;
1692
1693 END validate_offset_tax;
1694
1695 END ZX_TDS_RATE_DETM_PKG;
1696
[Home] [Help]