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