[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_TAX_RATE_PKG
Source
1 PACKAGE BODY ZX_TCM_TAX_RATE_PKG AS
2 /* $Header: zxctaxratespkgb.pls 120.14.12010000.2 2008/10/30 02:03:55 tsen ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8 g_module_name CONSTANT VARCHAR2(50) :='ZX.PLSQL.ZX_TCM_TAX_RATE_PKG';
9
10 PROCEDURE get_tax_rate_by_jur_gt(
11 p_tax_class IN VARCHAR2,
12 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
13 p_tax IN ZX_RATES_B.tax%TYPE,
14 p_tax_date IN DATE,
15 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
16 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
17 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
18 x_return_status OUT NOCOPY VARCHAR2
19 );
20
21 PROCEDURE get_def_tax_rate_by_jur_gt(
22 p_tax_class IN VARCHAR2,
23 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
24 p_tax IN ZX_RATES_B.tax%TYPE,
25 p_tax_date IN DATE,
26 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
27 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
28 x_return_status OUT NOCOPY VARCHAR2
29 );
30
31 PROCEDURE get_tax_rate_by_jur_code(
32 p_tax_class IN VARCHAR2,
33 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
34 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
35 p_tax IN ZX_RATES_B.tax%TYPE,
36 p_tax_date IN DATE,
37 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
38 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
39 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
40 x_return_status OUT NOCOPY VARCHAR2
41 );
42
43 PROCEDURE get_tax_rate_no_jur_code(
44 p_tax_class IN VARCHAR2,
45 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
46 p_tax IN ZX_RATES_B.tax%TYPE,
47 p_tax_date IN DATE,
48 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
49 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
50 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
51 x_return_status OUT NOCOPY VARCHAR2
52 );
53 PROCEDURE get_def_tax_rate_by_jur_code(
54 p_tax_class IN VARCHAR2,
55 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
56 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
57 p_tax IN ZX_RATES_B.tax%TYPE,
58 p_tax_date IN DATE,
59 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
60 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
61 x_return_status OUT NOCOPY VARCHAR2
62 );
63
64 PROCEDURE get_def_tax_rate_no_jur_code(
65 p_tax_class IN VARCHAR2,
66 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
67 p_tax IN ZX_RATES_B.tax%TYPE,
68 p_tax_date IN DATE,
69 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
70 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
71 x_return_status OUT NOCOPY VARCHAR2
72 );
73
74 PROCEDURE get_tax_rate_internal(
75 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
76 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
77 p_tax IN ZX_RATES_B.tax%TYPE,
78 p_tax_date IN DATE,
79 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
80 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
81 p_place_of_supply_type_code IN ZX_LINES.place_of_supply_type_code%TYPE,
82 p_structure_index IN NUMBER,
83 p_multiple_jurisdictions_flag IN VARCHAR2,
84 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
85 x_return_status OUT NOCOPY VARCHAR2
86 );
87
88 PROCEDURE get_tax_rate_pvt(
89 p_tax_class IN VARCHAR2,
90 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
91 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
92 p_tax IN ZX_RATES_B.tax%TYPE,
93 p_tax_date IN DATE,
94 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
95 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
96 p_place_of_supply_type_code IN ZX_LINES.place_of_supply_type_code%TYPE,
97 p_structure_index IN NUMBER,
98 p_multiple_jurisdictions_flag IN VARCHAR2,
99 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
100 x_return_status OUT NOCOPY VARCHAR2
101 );
102
103 PROCEDURE get_def_tax_rate_pvt(
104 p_tax_class IN VARCHAR2,
105 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
106 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
107 p_tax IN ZX_RATES_B.tax%TYPE,
108 p_tax_date IN DATE,
109 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
110 p_place_of_supply_type_code IN ZX_LINES.place_of_supply_type_code%TYPE,
111 p_structure_index IN NUMBER,
112 p_multiple_jurisdictions_flag IN VARCHAR2,
113 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
114 x_return_status OUT NOCOPY VARCHAR2
115 );
116
117
118 ------------------------------------------------------------------------------
119 -- PRIVATE PROCEDURE
120 -- get_tax_rate_by_jur_gt
121 --
122 -- DESCRIPTION
123 -- This procedure find tax rate information match the passed in tax
124 -- information and the jurisdiction info in zx_jurisdictions_gt
125 ------------------------------------------------------------------------------
126 PROCEDURE get_tax_rate_by_jur_gt(
127 p_tax_class IN VARCHAR2,
128 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
129 p_tax IN ZX_RATES_B.tax%TYPE,
130 p_tax_date IN DATE,
131 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
132 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
133 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
134 x_return_status OUT NOCOPY VARCHAR2
135 ) IS
136
137 -- the condition of tax_class is added for migration_data compatibility
138 -- it is possible for migrated tax rates from AP and AR share the same tax
139 -- rate code, in this case, need the tax_class info(AP: INPUT, AR: OUTPUT)
140 -- to tell the tax rates apart.
141
142 CURSOR get_tax_rate_csr IS
143 SELECT rate.tax_regime_code,
144 rate.tax,
145 rate.tax_status_code,
146 rate.tax_rate_code,
147 rate.tax_rate_id,
148 rate.effective_from,
149 rate.effective_to,
150 rate.rate_type_code,
151 rate.percentage_rate,
152 rate.quantity_rate,
153 rate.allow_adhoc_tax_rate_flag,
154 rate.uom_code,
155 rate.tax_jurisdiction_code,
156 rate.offset_tax,
157 rate.offset_status_code,
158 rate.offset_tax_rate_code,
159 rate.allow_exemptions_flag,
160 rate.allow_exceptions_flag,
161 jur.tax_jurisdiction_id,
162 rate.def_rec_settlement_option_code,
163 rate.taxable_basis_formula_code,
164 rate.adj_for_adhoc_amt_code,
165 rate.inclusive_tax_flag,
166 rate.tax_class
167 FROM ZX_SCO_RATES_B_V rate, ZX_JURISDICTIONS_GT jur
168 WHERE rate.tax_jurisdiction_code = jur.tax_jurisdiction_code
169 AND rate.tax = jur.tax
170 AND rate.tax_regime_code = jur.tax_regime_code
171 AND rate.effective_from <= p_tax_date
172 AND (rate.effective_to >= p_tax_date OR rate.effective_to IS NULL )
173 AND rate.tax_rate_code = p_tax_rate_code
174 AND rate.tax_status_code = p_tax_status_code
175 AND rate.tax = p_tax
176 AND rate.tax_regime_code = p_tax_regime_code
177 AND rate.active_flag = 'Y'
178 AND rate.tax_class IS NULL
179 ORDER BY jur.precedence_level, rate.subscription_level_code;
180
181 CURSOR get_tax_rate_mig_csr IS
182 SELECT rate.tax_regime_code,
183 rate.tax,
184 rate.tax_status_code,
185 rate.tax_rate_code,
186 rate.tax_rate_id,
187 rate.effective_from,
188 rate.effective_to,
189 rate.rate_type_code,
190 rate.percentage_rate,
191 rate.quantity_rate,
192 rate.allow_adhoc_tax_rate_flag,
193 rate.uom_code,
194 rate.tax_jurisdiction_code,
195 rate.offset_tax,
196 rate.offset_status_code,
197 rate.offset_tax_rate_code,
198 rate.allow_exemptions_flag,
199 rate.allow_exceptions_flag,
200 jur.tax_jurisdiction_id,
201 def_rec_settlement_option_code,
202 taxable_basis_formula_code,
203 adj_for_adhoc_amt_code,
204 inclusive_tax_flag,
205 tax_class
206 FROM ZX_SCO_RATES_B_V rate, ZX_JURISDICTIONS_GT jur
207 WHERE rate.tax_jurisdiction_code = jur.tax_jurisdiction_code
208 AND rate.tax = jur.tax
209 AND rate.tax_regime_code = jur.tax_regime_code
210 AND rate.effective_from <= p_tax_date
211 AND (rate.effective_to >= p_tax_date OR rate.effective_to IS NULL )
212 AND rate.tax_rate_code = p_tax_rate_code
213 AND rate.tax_status_code = p_tax_status_code
214 AND rate.tax = p_tax
215 AND rate.tax_regime_code = p_tax_regime_code
216 AND rate.active_flag = 'Y'
217 AND rate.tax_class = p_tax_class
218 ORDER BY jur.precedence_level, rate.subscription_level_code;
219
220 l_procedure_name CONSTANT VARCHAR2(30) := 'get_tax_rate_by_jur_gt';
221 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
222 l_tax_rate_rec_tmp ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
223
224 BEGIN
225 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
226
227 IF (g_level_procedure >= g_current_runtime_level ) THEN
228 FND_LOG.STRING(g_level_procedure,
229 g_module_name||'.'||l_procedure_name||'.BEGIN',
230 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_gt(+) ');
231 END IF;
232
233 x_return_status:= FND_API.G_RET_STS_SUCCESS;
234
235 -- use p_tax_regime_code,
236 -- p_tax,
237 -- p_tax_status_code,
238 -- p_tax_rate_code,
239 -- p_tax_date
240 -- and jurisdiction info in zx_jurisdictions_gt to
241 -- get the tax rate information from zx_rates_b
242 --
243 OPEN get_tax_rate_csr;
244
245 -- the first rate(the one with smallest precedence_level value) from the
246 -- cursor get_tax_rate_csr is the one should be used as the tax rate.
247
248 FETCH get_tax_rate_csr INTO l_tax_rate_rec;
249
250 IF get_tax_rate_csr%FOUND THEN
251
252 /* Bug#5395227- don't do a 2nd fetch
253 FETCH get_tax_rate_csr INTO l_tax_rate_rec_tmp;
254
255 IF get_tax_rate_csr%FOUND
256 AND l_tax_rate_rec_tmp.tax_jurisdiction_code = l_tax_rate_rec.tax_jurisdiction_code
257 THEN
258 -- raise error for multiple rate retrieved for the same jurisdiction code
259 x_return_status:= FND_API.G_RET_STS_ERROR;
260
261 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTIPLE_RATES_FOUND');
262 FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE',p_tax_rate_code);
263 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
264 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
265 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
266
267 CLOSE get_tax_rate_csr;
268
269 IF (g_level_unexpected >= g_current_runtime_level ) THEN
270 FND_LOG.STRING(g_level_unexpected,
271 g_module_name||'.'||l_procedure_name,
272 'Too Many Tax Rate Rows Retrived. ');
273 FND_LOG.STRING(g_level_unexpected,
274 g_module_name||'.'||l_procedure_name||'.END',
275 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_gt(-) ');
276 END IF;
277
278 ELSE
279 ***** Bug#5395227 ****/
280
281 x_tax_rate_rec := l_tax_rate_rec;
282 CLOSE get_tax_rate_csr;
283
284 IF (g_level_statement >= g_current_runtime_level ) THEN
285 FND_LOG.STRING(g_level_statement,
286 g_module_name||'.'||l_procedure_name,
287 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
288 FND_LOG.STRING(g_level_statement,
289 g_module_name||'.'||l_procedure_name,
290 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
291 END IF;
292
293 -- END IF;
294 ELSE -- get_tax_rate_csr%NOTFOUND
295 CLOSE get_tax_rate_csr;
296
297 -- tax rate is not found with NULL tax class, get tax rate with p_tax_class
298 --
299 OPEN get_tax_rate_mig_csr;
300
301 -- the first rate(the one with smallest precedence_level value) from the
302 -- cursor get_tax_rate_csr is the one should be used as the tax rate.
303
304 FETCH get_tax_rate_mig_csr INTO l_tax_rate_rec;
305
306 IF get_tax_rate_mig_csr%FOUND THEN
307
308 /* Bug#5395227- don't do a 2nd fetch
309
310 FETCH get_tax_rate_mig_csr INTO l_tax_rate_rec_tmp;
311
312 IF get_tax_rate_mig_csr%FOUND
313 AND l_tax_rate_rec_tmp.tax_jurisdiction_code = l_tax_rate_rec.tax_jurisdiction_code
314 THEN
315 -- raise error for multiple rate retrieved for the same jurisdiction code
316 x_return_status:= FND_API.G_RET_STS_ERROR;
317
318 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTIPLE_RATES_FOUND');
319 FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE',p_tax_rate_code);
320 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
321 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
322 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
323
324 CLOSE get_tax_rate_mig_csr;
325
326 IF (g_level_unexpected >= g_current_runtime_level ) THEN
327 FND_LOG.STRING(g_level_unexpected,
328 g_module_name||'.'||l_procedure_name,
329 'Too Many Tax Rate Rows Retrived. ');
330 FND_LOG.STRING(g_level_unexpected,
331 g_module_name||'.'||l_procedure_name||'.END',
332 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_gt(-) ');
333 END IF;
334
335 ELSE
336 ***** Bug#5395227 ****/
337
338 x_tax_rate_rec := l_tax_rate_rec;
339 CLOSE get_tax_rate_mig_csr;
340
341 IF (g_level_statement >= g_current_runtime_level ) THEN
342 FND_LOG.STRING(g_level_statement,
343 g_module_name||'.'||l_procedure_name,
344 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
345 FND_LOG.STRING(g_level_statement,
346 g_module_name||'.'||l_procedure_name,
347 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
348 END IF;
349
350 -- END IF;
351 ELSE -- get_tax_rate_mig_csr%NOTFOUND
352 CLOSE get_tax_rate_mig_csr;
353 END IF;
354 END IF;
355
356 IF (g_level_procedure >= g_current_runtime_level ) THEN
357 FND_LOG.STRING(g_level_procedure,
358 g_module_name||'.'||l_procedure_name||'.END',
359 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_gt(-) ');
360 END IF;
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365 IF get_tax_rate_csr%ISOPEN THEN
366 CLOSE get_tax_rate_csr;
367 END IF;
368 IF (g_level_unexpected >= g_current_runtime_level ) THEN
369 FND_LOG.STRING(g_level_unexpected,
370 g_module_name||'.'||l_procedure_name,
371 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
372 FND_LOG.STRING(g_level_unexpected,
373 g_module_name||'.'||l_procedure_name||'.END',
374 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_gt(-) ');
375 END IF;
376
377 END get_tax_rate_by_jur_gt;
378
379 ------------------------------------------------------------------------------
380 -- PRIVATE PROCEDURE
381 -- get_def_tax_rate_by_jur_gt
382 --
383 -- DESCRIPTION
384 -- This procedure find tax rate information match the passed in tax
385 -- information and the jurisdiction info in zx_jurisdictions_gt
386 ------------------------------------------------------------------------------
387 PROCEDURE get_def_tax_rate_by_jur_gt(
388 p_tax_class IN VARCHAR2,
389 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
390 p_tax IN ZX_RATES_B.tax%TYPE,
391 p_tax_date IN DATE,
392 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
393 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
394 x_return_status OUT NOCOPY VARCHAR2
395 )IS
396
397 -- the condition of tax_class is added for migration_data compatibility
398 -- it is possible for migrated tax rates from AP and AR share the same tax
399 -- rate code, in this case, need the tax_class info(AP: INPUT, AR: OUTPUT)
400 -- to tell the tax rates apart.
401
402 CURSOR get_def_tax_rate_csr IS
403 SELECT rate.tax_regime_code,
404 rate.tax,
405 rate.tax_status_code,
406 rate.tax_rate_code,
407 rate.tax_rate_id,
408 rate.effective_from,
409 rate.effective_to,
410 rate.rate_type_code,
411 rate.percentage_rate,
412 rate.quantity_rate,
413 rate.allow_adhoc_tax_rate_flag,
414 rate.uom_code,
415 rate.tax_jurisdiction_code,
416 rate.offset_tax,
417 rate.offset_status_code,
418 rate.offset_tax_rate_code,
419 rate.allow_exemptions_flag,
420 rate.allow_exceptions_flag,
421 jur.tax_jurisdiction_id,
422 def_rec_settlement_option_code,
423 taxable_basis_formula_code,
424 adj_for_adhoc_amt_code,
425 inclusive_tax_flag,
426 tax_class
427 FROM ZX_SCO_RATES_B_V rate, ZX_JURISDICTIONS_GT jur
428 WHERE rate.tax_jurisdiction_code = jur.tax_jurisdiction_code
429 AND rate.tax = jur.tax
430 AND rate.tax_regime_code = jur.tax_regime_code
431 AND rate.tax_status_code = p_tax_status_code
432 AND rate.tax = p_tax
433 AND rate.tax_regime_code = p_tax_regime_code
434 AND rate.default_flg_effective_from <= p_tax_date
435 AND (rate.default_flg_effective_to >= p_tax_date
436 OR rate.default_flg_effective_to IS NULL)
437 AND rate.default_rate_flag = 'Y'
438 AND rate.active_flag = 'Y'
439 AND rate.tax_class IS NULL
440 ORDER BY jur.precedence_level, rate.subscription_level_code;
441
442 CURSOR get_def_tax_rate_mig_csr IS
443 SELECT rate.tax_regime_code,
444 rate.tax,
445 rate.tax_status_code,
446 rate.tax_rate_code,
447 rate.tax_rate_id,
448 rate.effective_from,
449 rate.effective_to,
450 rate.rate_type_code,
451 rate.percentage_rate,
452 rate.quantity_rate,
453 rate.allow_adhoc_tax_rate_flag,
454 rate.uom_code,
455 rate.tax_jurisdiction_code,
456 rate.offset_tax,
457 rate.offset_status_code,
458 rate.offset_tax_rate_code,
459 rate.allow_exemptions_flag,
460 rate.allow_exceptions_flag,
461 jur.tax_jurisdiction_id,
462 def_rec_settlement_option_code,
463 taxable_basis_formula_code,
464 adj_for_adhoc_amt_code,
465 inclusive_tax_flag,
466 tax_class
467 FROM ZX_SCO_RATES_B_V rate, ZX_JURISDICTIONS_GT jur
468 WHERE rate.tax_jurisdiction_code = jur.tax_jurisdiction_code
469 AND rate.tax = jur.tax
470 AND rate.tax_regime_code = jur.tax_regime_code
471 AND rate.tax_status_code = p_tax_status_code
472 AND rate.tax = p_tax
473 AND rate.tax_regime_code = p_tax_regime_code
474 AND rate.default_flg_effective_from <= p_tax_date
475 AND (rate.default_flg_effective_to >= p_tax_date
476 OR rate.default_flg_effective_to IS NULL)
477 AND rate.default_rate_flag = 'Y'
478 AND rate.active_flag = 'Y'
479 AND rate.tax_class = p_tax_class
480 ORDER BY jur.precedence_level, rate.subscription_level_code;
481
482 l_procedure_name CONSTANT VARCHAR2(30) := 'get_def_tax_rate_by_jur_gt';
483 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
484 --l_tax_rate_rec_tmp ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
485
486 BEGIN
487 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
488
489 IF (g_level_procedure >= g_current_runtime_level ) THEN
490 FND_LOG.STRING(g_level_procedure,
491 g_module_name||'.'||l_procedure_name||'.BEGIN',
492 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_gt(+) ');
493 END IF;
494
495 x_return_status:= FND_API.G_RET_STS_SUCCESS;
496
497 -- get default tax rate code
498 OPEN get_def_tax_rate_csr;
499
500 -- the first rate(the one with smallest precedence_level value) from the
501 -- cursor get_tax_rate_csr is the one should be used as the tax rate.
502 FETCH get_def_tax_rate_csr INTO l_tax_rate_rec;
503
504 IF get_def_tax_rate_csr%FOUND THEN
505
506 /* Bug#5395227- don't do a 2nd fetch
507
508 FETCH get_def_tax_rate_csr INTO l_tax_rate_rec_tmp;
509
510 IF get_def_tax_rate_csr%FOUND
511 AND l_tax_rate_rec_tmp.tax_jurisdiction_code = l_tax_rate_rec.tax_jurisdiction_code
512 THEN
513 -- raise error for multiple rate retrieved for the same jurisdiction code
514 x_return_status:= FND_API.G_RET_STS_ERROR;
515
516 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTI_DEFAULT_RATES_FOUND');
517 FND_MESSAGE.SET_TOKEN('RATE_REGIME_CODE', p_tax_regime_code);
518 FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax);
519 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
520 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
521 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
522
523 CLOSE get_def_tax_rate_csr;
524
525 IF (g_level_unexpected >= g_current_runtime_level ) THEN
526 FND_LOG.STRING(g_level_unexpected,
527 g_module_name||'.'||l_procedure_name,
528 'Too Many Tax Rate Rows Retrived. ');
529 FND_LOG.STRING(g_level_unexpected,
530 g_module_name||'.'||l_procedure_name||'.END',
531 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_gt(-) ');
532 END IF;
533 ELSE
534 ***** Bug#5395227 ****/
535
536 x_tax_rate_rec := l_tax_rate_rec;
537 CLOSE get_def_tax_rate_csr;
538
539 IF (g_level_statement >= g_current_runtime_level ) THEN
540 FND_LOG.STRING(g_level_statement,
541 g_module_name||'.'||l_procedure_name,
542 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
543 FND_LOG.STRING(g_level_statement,
544 g_module_name||'.'||l_procedure_name,
545 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
546 END IF;
547
548 -- END IF;
549 ELSE -- get_def_tax_rate_csr%NOTFOUND
550 CLOSE get_def_tax_rate_csr;
551
552 -- tax rate is not found with NULL tax class, get tax rate with p_tax_class
553 --
554 OPEN get_def_tax_rate_mig_csr;
555
556 -- the first rate(the one with smallest precedence_level value) from the
557 -- cursor get_tax_rate_csr is the one should be used as the tax rate.
558 FETCH get_def_tax_rate_mig_csr INTO l_tax_rate_rec;
559
560 IF get_def_tax_rate_mig_csr%FOUND THEN
561
562 /* Bug#5395227- don't do a 2nd fetch
563 FETCH get_def_tax_rate_mig_csr INTO l_tax_rate_rec_tmp;
564
565 IF get_def_tax_rate_mig_csr%FOUND
566 AND l_tax_rate_rec_tmp.tax_jurisdiction_code = l_tax_rate_rec.tax_jurisdiction_code
567 THEN
568 -- raise error for multiple rate retrieved for the same jurisdiction code
569 x_return_status:= FND_API.G_RET_STS_ERROR;
570
571 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTI_DEFAULT_RATES_FOUND');
572 FND_MESSAGE.SET_TOKEN('RATE_REGIME_CODE', p_tax_regime_code);
573 FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax);
574 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
575 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
576 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
577
578 CLOSE get_def_tax_rate_mig_csr;
579
580 IF (g_level_unexpected >= g_current_runtime_level ) THEN
581 FND_LOG.STRING(g_level_unexpected,
582 g_module_name||'.'||l_procedure_name,
583 'Too Many Tax Rate Rows Retrived. ');
584 FND_LOG.STRING(g_level_unexpected,
585 g_module_name||'.'||l_procedure_name||'.END',
586 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_gt(-) ');
587 END IF;
588 ELSE
589 ***** Bug#5395227 ****/
590
591 x_tax_rate_rec := l_tax_rate_rec;
592 CLOSE get_def_tax_rate_mig_csr;
593
594 IF (g_level_statement >= g_current_runtime_level ) THEN
595 FND_LOG.STRING(g_level_statement,
596 g_module_name||'.'||l_procedure_name,
597 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
598 FND_LOG.STRING(g_level_statement,
599 g_module_name||'.'||l_procedure_name,
600 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
601 END IF;
602
603 --END IF;
604 ELSE -- get_def_tax_rate_mig_csr%NOTFOUND
605 CLOSE get_def_tax_rate_mig_csr;
606 END IF;
607 END IF;
608
609 IF (g_level_procedure >= g_current_runtime_level ) THEN
610 FND_LOG.STRING(g_level_procedure,
611 g_module_name||'.'||l_procedure_name||'.END',
612 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_gt(-) ');
613 END IF;
614
615 EXCEPTION
616 WHEN OTHERS THEN
617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
618 IF get_def_tax_rate_csr%ISOPEN THEN
619 CLOSE get_def_tax_rate_csr;
620 END IF;
621 IF (g_level_unexpected >= g_current_runtime_level ) THEN
622 FND_LOG.STRING(g_level_unexpected,
623 g_module_name||'.'||l_procedure_name,
624 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
625 FND_LOG.STRING(g_level_unexpected,
626 g_module_name||'.'||l_procedure_name||'.END',
627 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_gt(-) ');
628 END IF;
629
630 END get_def_tax_rate_by_jur_gt;
631
632 ------------------------------------------------------------------------------
633 -- PRIVATE PROCEDURE
634 -- get_tax_rate_by_jur_code
635 --
636 -- DESCRIPTION
637 -- This procedure find tax rate information match the passed in tax
638 -- information
639 ------------------------------------------------------------------------------
640 PROCEDURE get_tax_rate_by_jur_code(
641 p_tax_class IN VARCHAR2,
642 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
643 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
644 p_tax IN ZX_RATES_B.tax%TYPE,
645 p_tax_date IN DATE,
646 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
647 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
648 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
649 x_return_status OUT NOCOPY VARCHAR2
650 ) IS
651
652 -- the condition of tax_class is added for migration_data compatibility
653 -- it is possible for migrated tax rates from AP and AR share the same tax
654 -- rate code, in this case, need the tax_class info(AP: INPUT, AR: OUTPUT)
655 -- to tell the tax rates apart.
656
657 CURSOR get_tax_rate_csr IS
658 SELECT tax_regime_code,
659 tax,
660 tax_status_code,
661 tax_rate_code,
662 tax_rate_id,
663 effective_from,
664 effective_to,
665 rate_type_code,
666 percentage_rate,
667 quantity_rate,
668 Allow_Adhoc_Tax_Rate_Flag,
669 uom_code,
670 tax_jurisdiction_code,
671 offset_tax,
672 offset_status_code,
673 offset_tax_rate_code,
674 allow_exemptions_flag,
675 allow_exceptions_flag,
676 NULL tax_jurisdiction_id,
677 def_rec_settlement_option_code,
678 taxable_basis_formula_code,
679 adj_for_adhoc_amt_code,
680 inclusive_tax_flag,
681 tax_class
682 FROM ZX_SCO_RATES_B_V -- Bug#5395227
683 WHERE tax_jurisdiction_code = p_tax_jurisdiction_code
684 AND effective_from <= p_tax_date
685 AND (effective_to >= p_tax_date OR effective_to IS NULL )
686 AND tax_rate_code = p_tax_rate_code
687 AND tax_status_code = p_tax_status_code
688 AND tax = p_tax
689 AND tax_regime_code = p_tax_regime_code
690 AND Active_Flag = 'Y'
691 AND tax_class IS NULL
692 ORDER BY subscription_level_code; -- Bug#5395227
693
694 CURSOR get_tax_rate_mig_csr IS
695 SELECT tax_regime_code,
696 tax,
697 tax_status_code,
698 tax_rate_code,
699 tax_rate_id,
700 effective_from,
701 effective_to,
702 rate_type_code,
703 percentage_rate,
704 quantity_rate,
705 Allow_Adhoc_Tax_Rate_Flag,
706 uom_code,
707 tax_jurisdiction_code,
708 offset_tax,
709 offset_status_code,
710 offset_tax_rate_code,
711 allow_exemptions_flag,
712 allow_exceptions_flag,
713 NULL tax_jurisdiction_id,
714 def_rec_settlement_option_code,
715 taxable_basis_formula_code,
716 adj_for_adhoc_amt_code,
717 inclusive_tax_flag,
718 tax_class
719 FROM ZX_SCO_RATES_B_V -- Bug#5395227
720 WHERE tax_jurisdiction_code = p_tax_jurisdiction_code
721 AND effective_from <= p_tax_date
722 AND (effective_to >= p_tax_date OR effective_to IS NULL )
723 AND tax_rate_code = p_tax_rate_code
724 AND tax_status_code = p_tax_status_code
725 AND tax = p_tax
726 AND tax_regime_code = p_tax_regime_code
727 AND Active_Flag = 'Y'
728 AND tax_class = p_tax_class
729 ORDER BY subscription_level_code; -- Bug#5395227
730
731 l_procedure_name CONSTANT VARCHAR2(30) := 'get_tax_rate_by_jur_code';
732 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
733 -- l_tax_rate_rec_tmp ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
734
735 BEGIN
736 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
737
738 IF (g_level_procedure >= g_current_runtime_level ) THEN
739 FND_LOG.STRING(g_level_procedure,
740 g_module_name||'.'||l_procedure_name||'.BEGIN',
741 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_code(+) ');
742 END IF;
743
744 x_return_status:= FND_API.G_RET_STS_SUCCESS;
745
746 -- use p_tax_jurisdiction_code,
747 -- p_tax_regime_code,
748 -- p_tax,
749 -- p_tax_status_code,
750 -- p_tax_rate_code,
751 -- p_tax_date to
752
753 -- get tax rate with NULL tax class first
754 --
755 OPEN get_tax_rate_csr;
756
757 FETCH get_tax_rate_csr INTO l_tax_rate_rec;
758
759 IF get_tax_rate_csr%FOUND THEN
760
761 /* Bug#5395227- don't do a 2nd fetch
762
763 FETCH get_tax_rate_csr INTO l_tax_rate_rec_tmp;
764
765 IF get_tax_rate_csr%FOUND THEN
766 -- raise error for multiple rate retrieved
767 x_return_status:= FND_API.G_RET_STS_ERROR;
768
769 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTIPLE_RATES_FOUND');
770 FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE',p_tax_rate_code);
771 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
772 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
773 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
774
775 CLOSE get_tax_rate_csr;
776
777 IF (g_level_unexpected >= g_current_runtime_level ) THEN
778 FND_LOG.STRING(g_level_unexpected,
779 g_module_name||'.'||l_procedure_name,
780 'Too Many Tax Rate Rows Retrived. ');
781 FND_LOG.STRING(g_level_unexpected,
782 g_module_name||'.'||l_procedure_name||'.END',
783 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_code(-) ');
784 END IF;
785
786 ELSE
787 ***** Bug#5395227 ****/
788
789 x_tax_rate_rec := l_tax_rate_rec;
790 CLOSE get_tax_rate_csr;
791
792 IF (g_level_statement >= g_current_runtime_level ) THEN
793 FND_LOG.STRING(g_level_statement,
794 g_module_name||'.'||l_procedure_name,
795 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
796 FND_LOG.STRING(g_level_statement,
797 g_module_name||'.'||l_procedure_name,
798 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
799 END IF;
800
801 -- END IF;
802 ELSE -- get_tax_rate_csr%NOTFOUND
803 CLOSE get_tax_rate_csr;
804
805 OPEN get_tax_rate_mig_csr;
806
807 FETCH get_tax_rate_mig_csr INTO l_tax_rate_rec;
808
809 IF get_tax_rate_mig_csr%FOUND THEN
810
811 /* Bug#5395227- don't do a 2nd fetch
812 FETCH get_tax_rate_mig_csr INTO l_tax_rate_rec_tmp;
813
814 IF get_tax_rate_mig_csr%FOUND THEN
815 -- raise error for multiple rate retrieved
816 x_return_status:= FND_API.G_RET_STS_ERROR;
817
818 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTIPLE_RATES_FOUND');
819 FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE',p_tax_rate_code);
820 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
821 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
822 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
823
824 CLOSE get_tax_rate_mig_csr;
825
826 IF (g_level_unexpected >= g_current_runtime_level ) THEN
827 FND_LOG.STRING(g_level_unexpected,
828 g_module_name||'.'||l_procedure_name,
829 'Too Many Tax Rate Rows Retrived. ');
830 FND_LOG.STRING(g_level_unexpected,
831 g_module_name||'.'||l_procedure_name||'.END',
832 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_code(-) ');
833 END IF;
834
835 ELSE
836 ***** Bug#5395227 ****/
837
838 x_tax_rate_rec := l_tax_rate_rec;
839 CLOSE get_tax_rate_mig_csr;
840
841 IF (g_level_statement >= g_current_runtime_level ) THEN
842 FND_LOG.STRING(g_level_statement,
843 g_module_name||'.'||l_procedure_name,
844 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
845 FND_LOG.STRING(g_level_statement,
846 g_module_name||'.'||l_procedure_name,
847 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
848 END IF;
849
850 -- END IF;
851 ELSE -- get_tax_rate_mig_csr%NOTFOUND
852 CLOSE get_tax_rate_mig_csr;
853 END IF;
854
855 END IF;
856
857 IF (g_level_procedure >= g_current_runtime_level ) THEN
858 FND_LOG.STRING(g_level_procedure,
859 g_module_name||'.'||l_procedure_name||'.END',
860 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_code(-) ');
861 END IF;
862
863 EXCEPTION
864 WHEN OTHERS THEN
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 IF get_tax_rate_csr%ISOPEN THEN
867 CLOSE get_tax_rate_csr;
868 END IF;
869 IF (g_level_unexpected >= g_current_runtime_level ) THEN
870 FND_LOG.STRING(g_level_unexpected,
871 g_module_name||'.'||l_procedure_name,
872 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
873 FND_LOG.STRING(g_level_unexpected,
874 g_module_name||'.'||l_procedure_name||'.END',
875 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_code(-) ');
876 END IF;
877
878 END get_tax_rate_by_jur_code;
879
880 ------------------------------------------------------------------------------
881 -- PRIVATE PROCEDURE
882 -- get_tax_rate_no_jur_code
883 --
884 -- DESCRIPTION
885 -- This procedure find tax rate information match the passed in tax
886 -- information without jurisdiction information
887 ------------------------------------------------------------------------------
888
889 PROCEDURE get_tax_rate_no_jur_code(
890 p_tax_class IN VARCHAR2,
891 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
892 p_tax IN ZX_RATES_B.tax%TYPE,
893 p_tax_date IN DATE,
894 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
895 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
896 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
897 x_return_status OUT NOCOPY VARCHAR2
898 ) IS
899
900 -- the condition of tax_class is added for migration_data compatibility
901 -- it is possible for migrated tax rates from AP and AR share the same tax
902 -- rate code, in this case, need the tax_class info(AP: INPUT, AR: OUTPUT)
903 -- to tell the tax rates apart.
904
905 CURSOR get_tax_rate_csr_no_jur IS
906 SELECT tax_regime_code,
907 tax,
908 tax_status_code,
909 tax_rate_code,
910 tax_rate_id,
911 effective_from,
912 effective_to,
913 rate_type_code,
914 percentage_rate,
915 quantity_rate,
916 Allow_Adhoc_Tax_Rate_Flag,
917 uom_code,
918 tax_jurisdiction_code,
919 offset_tax,
920 offset_status_code,
921 offset_tax_rate_code,
922 allow_exemptions_flag,
923 allow_exceptions_flag,
924 NULL tax_jurisdiction_id,
925 def_rec_settlement_option_code,
926 taxable_basis_formula_code,
927 adj_for_adhoc_amt_code,
928 inclusive_tax_flag,
929 tax_class
930 FROM ZX_SCO_RATES_B_V -- Bug#5395227
931 WHERE tax_jurisdiction_code IS NULL
932 AND effective_from <= p_tax_date
933 AND ( effective_to >= p_tax_date OR effective_to IS NULL)
934 AND tax_rate_code = p_tax_rate_code
935 AND tax_status_code = p_tax_status_code
936 AND tax = p_tax
937 AND tax_regime_code = p_tax_regime_code
938 AND Active_Flag = 'Y'
939 AND tax_class IS NULL
940 ORDER BY subscription_level_code; -- Bug#5395227
941
942 CURSOR get_tax_rate_csr_no_jur_mig IS
943 SELECT tax_regime_code,
944 tax,
945 tax_status_code,
946 tax_rate_code,
947 tax_rate_id,
948 effective_from,
949 effective_to,
950 rate_type_code,
951 percentage_rate,
952 quantity_rate,
953 Allow_Adhoc_Tax_Rate_Flag,
954 uom_code,
955 tax_jurisdiction_code,
956 offset_tax,
957 offset_status_code,
958 offset_tax_rate_code,
959 allow_exemptions_flag,
960 allow_exceptions_flag,
961 NULL tax_jurisdiction_id,
962 def_rec_settlement_option_code,
963 taxable_basis_formula_code,
964 adj_for_adhoc_amt_code,
965 inclusive_tax_flag,
966 tax_class
967 FROM ZX_SCO_RATES_B_V -- Bug#5395227
968 WHERE tax_jurisdiction_code IS NULL
969 AND effective_from <= p_tax_date
970 AND ( effective_to >= p_tax_date OR effective_to IS NULL)
971 AND tax_rate_code = p_tax_rate_code
972 AND tax_status_code = p_tax_status_code
973 AND tax = p_tax
974 AND tax_regime_code = p_tax_regime_code
975 AND Active_Flag = 'Y'
976 AND tax_class = p_tax_class
977 ORDER BY subscription_level_code; -- Bug#5395227
978
979 l_procedure_name CONSTANT VARCHAR2(30) := 'get_tax_rate_no_jur_code';
980 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
981 --l_tax_rate_rec_tmp ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
982
983 BEGIN
984 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
985
986 IF (g_level_procedure >= g_current_runtime_level ) THEN
987 FND_LOG.STRING(g_level_procedure,
988 g_module_name||'.'||l_procedure_name||'.BEGIN',
989 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_no_jur_code(+) ');
990 END IF;
991 x_return_status := FND_API.G_RET_STS_SUCCESS;
992
993 -- get tax rate with NULL tax class first
994 --
995 OPEN get_tax_rate_csr_no_jur;
996
997 FETCH get_tax_rate_csr_no_jur INTO l_tax_rate_rec;
998
999 IF get_tax_rate_csr_no_jur%FOUND THEN
1000
1001 /* Bug#5395227- don't do a 2nd fetch
1002 FETCH get_tax_rate_csr_no_jur INTO l_tax_rate_rec_tmp;
1003
1004 IF get_tax_rate_csr_no_jur%FOUND THEN
1005 -- raise error for multiple rate retrieved
1006 x_return_status:= FND_API.G_RET_STS_ERROR;
1007 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTIPLE_RATES_FOUND');
1008 FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE',p_tax_rate_code);
1009 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
1010 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
1011 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
1012
1013 CLOSE get_tax_rate_csr_no_jur;
1014
1015 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1016 FND_LOG.STRING(g_level_unexpected,
1017 g_module_name||'.'||l_procedure_name,
1018 'Too Many Tax Rate Rows Retrived. ');
1019 FND_LOG.STRING(g_level_unexpected,
1020 g_module_name||'.'||l_procedure_name||'.END',
1021 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_no_jur_code(-) ');
1022 END IF;
1023
1024 RETURN;
1025 ELSE
1026 ***** Bug#5395227 ****/
1027
1028 x_tax_rate_rec := l_tax_rate_rec;
1029 CLOSE get_tax_rate_csr_no_jur;
1030
1031 IF (g_level_statement >= g_current_runtime_level ) THEN
1032 FND_LOG.STRING(g_level_statement,
1033 g_module_name||'.'||l_procedure_name,
1034 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
1035 FND_LOG.STRING(g_level_statement,
1036 g_module_name||'.'||l_procedure_name,
1037 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
1038 END IF;
1039 -- END IF;
1040 ELSE -- get_tax_rate_csr_no_jur%NOTFOUND
1041 CLOSE get_tax_rate_csr_no_jur;
1042
1043 -- tax rate is not found with NULL tax class, get tax rate with p_tax_class
1044 --
1045 OPEN get_tax_rate_csr_no_jur_mig;
1046
1047 FETCH get_tax_rate_csr_no_jur_mig INTO l_tax_rate_rec;
1048
1049 IF get_tax_rate_csr_no_jur_mig%FOUND THEN
1050
1051 /* Bug#5395227- don't do a 2nd fetch
1052 FETCH get_tax_rate_csr_no_jur_mig INTO l_tax_rate_rec_tmp;
1053
1054 IF get_tax_rate_csr_no_jur_mig%FOUND THEN
1055 -- raise error for multiple rate retrieved
1056 x_return_status:= FND_API.G_RET_STS_ERROR;
1057 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTIPLE_RATES_FOUND');
1058 FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE',p_tax_rate_code);
1059 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
1060 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
1061 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
1062
1063 CLOSE get_tax_rate_csr_no_jur_mig;
1064
1065 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1066 FND_LOG.STRING(g_level_unexpected,
1067 g_module_name||'.'||l_procedure_name,
1068 'Too Many Tax Rate Rows Retrived. ');
1069 FND_LOG.STRING(g_level_unexpected,
1070 g_module_name||'.'||l_procedure_name||'.END',
1071 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_no_jur_code(-) ');
1072 END IF;
1073
1074 RETURN;
1075 ELSE
1076 ***** Bug#5395227 ****/
1077
1078 x_tax_rate_rec := l_tax_rate_rec;
1079 CLOSE get_tax_rate_csr_no_jur_mig;
1080
1081 IF (g_level_statement >= g_current_runtime_level ) THEN
1082 FND_LOG.STRING(g_level_statement,
1083 g_module_name||'.'||l_procedure_name,
1084 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
1085 FND_LOG.STRING(g_level_statement,
1086 g_module_name||'.'||l_procedure_name,
1087 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
1088 END IF;
1089
1090 --END IF;
1091 ELSE -- get_tax_rate_csr_no_jur_mig%NOTFOUND
1092 CLOSE get_tax_rate_csr_no_jur_mig;
1093 END IF;
1094
1095 END IF;
1096
1097 IF (g_level_procedure >= g_current_runtime_level ) THEN
1098 FND_LOG.STRING(g_level_procedure,
1099 g_module_name||'.'||l_procedure_name||'.END',
1100 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_no_jur_code(-) ');
1101 END IF;
1102
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1106 IF get_tax_rate_csr_no_jur%ISOPEN THEN
1107 CLOSE get_tax_rate_csr_no_jur;
1108 END IF;
1109 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1110 FND_LOG.STRING(g_level_unexpected,
1111 g_module_name||'.'||l_procedure_name,
1112 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1113 FND_LOG.STRING(g_level_unexpected,
1114 g_module_name||'.'||l_procedure_name||'.END',
1115 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_no_jur_code(-) ');
1116 END IF;
1117 END get_tax_rate_no_jur_code;
1118
1119 ------------------------------------------------------------------------------
1120 -- PRIVATE PROCEDURE
1121 -- get_def_tax_rate_by_jur_code
1122 --
1123 -- DESCRIPTION
1124 -- This procedure find default tax rate information match the passed in tax
1125 -- information
1126 ------------------------------------------------------------------------------
1127
1128 PROCEDURE get_def_tax_rate_by_jur_code(
1129 p_tax_class IN VARCHAR2,
1130 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
1131 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
1132 p_tax IN ZX_RATES_B.tax%TYPE,
1133 p_tax_date IN DATE,
1134 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
1135 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
1136 x_return_status OUT NOCOPY VARCHAR2
1137 ) IS
1138
1139 -- the condition of tax_class is added for migration_data compatibility
1140 -- it is possible for migrated tax rates from AP and AR share the same tax
1141 -- rate code, in this case, need the tax_class info(AP: INPUT, AR: OUTPUT)
1142 -- to tell the tax rates apart.
1143
1144 CURSOR get_def_tax_rate_csr IS
1145 SELECT tax_regime_code,
1146 tax,
1147 tax_status_code,
1148 tax_rate_code,
1149 tax_rate_id,
1150 effective_from,
1151 effective_to,
1152 rate_type_code,
1153 percentage_rate,
1154 quantity_rate,
1155 Allow_Adhoc_Tax_Rate_Flag,
1156 uom_code,
1157 tax_jurisdiction_code,
1158 offset_tax,
1159 offset_status_code,
1160 offset_tax_rate_code,
1161 allow_exemptions_flag,
1162 allow_exceptions_flag,
1163 NULL tax_jurisdiction_id,
1164 def_rec_settlement_option_code,
1165 taxable_basis_formula_code,
1166 adj_for_adhoc_amt_code,
1167 inclusive_tax_flag,
1168 tax_class
1169 FROM ZX_SCO_RATES_B_V -- Bug#5395227
1170 WHERE tax_status_code = p_tax_status_code
1171 AND tax = p_tax
1172 AND tax_regime_code = p_tax_regime_code
1173 AND tax_jurisdiction_code = p_tax_jurisdiction_code
1174 AND default_flg_effective_from <= p_tax_date
1175 AND (default_flg_effective_to >= p_tax_date
1176 OR default_flg_effective_to IS NULL)
1177 AND Default_Rate_Flag = 'Y'
1178 AND Active_Flag = 'Y'
1179 AND tax_class IS NULL
1180 ORDER BY subscription_level_code; -- Bug#5395227
1181
1182 CURSOR get_def_tax_rate_mig_csr IS
1183 SELECT tax_regime_code,
1184 tax,
1185 tax_status_code,
1186 tax_rate_code,
1187 tax_rate_id,
1188 effective_from,
1189 effective_to,
1190 rate_type_code,
1191 percentage_rate,
1192 quantity_rate,
1193 Allow_Adhoc_Tax_Rate_Flag,
1194 uom_code,
1195 tax_jurisdiction_code,
1196 offset_tax,
1197 offset_status_code,
1198 offset_tax_rate_code,
1199 allow_exemptions_flag,
1200 allow_exceptions_flag,
1201 NULL tax_jurisdiction_id,
1202 def_rec_settlement_option_code,
1203 taxable_basis_formula_code,
1204 adj_for_adhoc_amt_code,
1205 inclusive_tax_flag,
1206 tax_class
1207 FROM ZX_SCO_RATES_B_V -- Bug#5395227
1208 WHERE tax_status_code = p_tax_status_code
1209 AND tax = p_tax
1210 AND tax_regime_code = p_tax_regime_code
1211 AND tax_jurisdiction_code = p_tax_jurisdiction_code
1212 AND default_flg_effective_from <= p_tax_date
1213 AND (default_flg_effective_to >= p_tax_date
1214 OR default_flg_effective_to IS NULL)
1215 AND Default_Rate_Flag = 'Y'
1216 AND Active_Flag = 'Y'
1217 AND tax_class = p_tax_class
1218 ORDER BY subscription_level_code; -- Bug#5395227
1219
1220 l_procedure_name CONSTANT VARCHAR2(30) := 'get_def_tax_rate_by_jur_code';
1221 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
1222 l_tax_rate_rec_tmp ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
1223
1224 BEGIN
1225 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1226
1227 IF (g_level_procedure >= g_current_runtime_level ) THEN
1228 FND_LOG.STRING(g_level_procedure,
1229 g_module_name||'.'||l_procedure_name||'.BEGIN',
1230 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_code(+) ');
1231 END IF;
1232
1233 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1234
1235 -- get default tax rate code with NULL tax class first
1236 --
1237 OPEN get_def_tax_rate_csr;
1238
1239 FETCH get_def_tax_rate_csr INTO l_tax_rate_rec;
1240
1241 IF get_def_tax_rate_csr%FOUND THEN
1242
1243 /* Bug#5395227- don't do a 2nd fetch
1244 FETCH get_def_tax_rate_csr INTO l_tax_rate_rec_tmp;
1245
1246 IF get_def_tax_rate_csr%FOUND THEN
1247 -- raise error for multiple rate retrieved
1248 x_return_status:= FND_API.G_RET_STS_ERROR;
1249
1250 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTI_DEFAULT_RATES_FOUND');
1251 FND_MESSAGE.SET_TOKEN('RATE_REGIME_CODE', p_tax_regime_code);
1252 FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax);
1253 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
1254 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
1255 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
1256
1257 CLOSE get_def_tax_rate_csr;
1258
1259 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1260 FND_LOG.STRING(g_level_unexpected,
1261 g_module_name||'.'||l_procedure_name,
1262 'Too Many Tax Rate Rows Retrived. ');
1263 FND_LOG.STRING(g_level_unexpected,
1264 g_module_name||'.'||l_procedure_name||'.END',
1265 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_code(-) ');
1266 END IF;
1267 ELSE
1268 ***** Bug#5395227 ****/
1269
1270 x_tax_rate_rec := l_tax_rate_rec;
1271 CLOSE get_def_tax_rate_csr;
1272
1273 IF (g_level_statement >= g_current_runtime_level ) THEN
1274 FND_LOG.STRING(g_level_statement,
1275 g_module_name||'.'||l_procedure_name,
1276 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
1277 FND_LOG.STRING(g_level_statement,
1278 g_module_name||'.'||l_procedure_name,
1279 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
1280 END IF;
1281
1282 -- END IF;
1283 ELSE -- get_def_tax_rate_csr%NOTFOUND
1284 CLOSE get_def_tax_rate_csr;
1285
1286 -- tax rate is not found with NULL tax class, try to get it with p_tax_class
1287 --
1288 OPEN get_def_tax_rate_mig_csr;
1289
1290 FETCH get_def_tax_rate_mig_csr INTO l_tax_rate_rec;
1291
1292 IF get_def_tax_rate_mig_csr%FOUND THEN
1293
1294 /* Bug#5395227- don't do a 2nd fetch
1295 FETCH get_def_tax_rate_mig_csr INTO l_tax_rate_rec_tmp;
1296
1297 IF get_def_tax_rate_mig_csr%FOUND THEN
1298 -- raise error for multiple rate retrieved
1299 x_return_status:= FND_API.G_RET_STS_ERROR;
1300
1301 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTI_DEFAULT_RATES_FOUND');
1302 FND_MESSAGE.SET_TOKEN('RATE_REGIME_CODE', p_tax_regime_code);
1303 FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax);
1304 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
1305 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
1306 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
1307
1308 CLOSE get_def_tax_rate_mig_csr;
1309
1310 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1311 FND_LOG.STRING(g_level_unexpected,
1312 g_module_name||'.'||l_procedure_name,
1313 'Too Many Tax Rate Rows Retrived. ');
1314 FND_LOG.STRING(g_level_unexpected,
1315 g_module_name||'.'||l_procedure_name||'.END',
1316 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_code(-) ');
1317 END IF;
1318 ELSE
1319 ***** Bug#5395227 ****/
1320
1321 x_tax_rate_rec := l_tax_rate_rec;
1322 CLOSE get_def_tax_rate_mig_csr;
1323
1324 IF (g_level_statement >= g_current_runtime_level ) THEN
1325 FND_LOG.STRING(g_level_statement,
1326 g_module_name||'.'||l_procedure_name,
1327 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
1328 FND_LOG.STRING(g_level_statement,
1329 g_module_name||'.'||l_procedure_name,
1330 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
1331 END IF;
1332
1333 -- END IF;
1334 ELSE -- get_def_tax_rate_mig_csr%NOTFOUND
1335 CLOSE get_def_tax_rate_mig_csr;
1336 END IF;
1337
1338 END IF;
1339
1340 IF (g_level_procedure >= g_current_runtime_level ) THEN
1341 FND_LOG.STRING(g_level_procedure,
1342 g_module_name||'.'||l_procedure_name||'.END',
1343 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_code(-) ');
1344 END IF;
1345
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1349 IF get_def_tax_rate_csr%ISOPEN THEN
1350 CLOSE get_def_tax_rate_csr;
1351 END IF;
1352 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1353 FND_LOG.STRING(g_level_unexpected,
1354 g_module_name||'.'||l_procedure_name,
1355 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1356 FND_LOG.STRING(g_level_unexpected,
1357 g_module_name||'.'||l_procedure_name||'.END',
1358 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_by_jur_code(-) ');
1359 END IF;
1360 END get_def_tax_rate_by_jur_code;
1361
1362 ------------------------------------------------------------------------------
1363 -- PRIVATE PROCEDURE
1364 -- get_def_tax_rate_no_jur_code
1365 --
1366 -- DESCRIPTION
1367 -- This procedure find default tax rate information match the passed in tax
1368 -- information without jurisdiction information
1369 ------------------------------------------------------------------------------
1370
1371 PROCEDURE get_def_tax_rate_no_jur_code(
1372 p_tax_class IN VARCHAR2,
1373 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
1374 p_tax IN ZX_RATES_B.tax%TYPE,
1375 p_tax_date IN DATE,
1376 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
1377 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
1378 x_return_status OUT NOCOPY VARCHAR2
1379 ) IS
1380
1381 -- the condition of tax_class is added for migration_data compatibility
1382 -- it is possible for migrated tax rates from AP and AR share the same tax
1383 -- rate code, in this case, need the tax_class info(AP: INPUT, AR: OUTPUT)
1384 -- to tell the tax rates apart.
1385
1386 CURSOR get_def_tax_rate_no_jur_csr IS
1387 SELECT tax_regime_code,
1388 tax,
1389 tax_status_code,
1390 tax_rate_code,
1391 tax_rate_id,
1392 effective_from,
1393 effective_to,
1394 rate_type_code,
1395 percentage_rate,
1396 quantity_rate,
1397 Allow_Adhoc_Tax_Rate_Flag,
1398 uom_code,
1399 tax_jurisdiction_code,
1400 offset_tax,
1401 offset_status_code,
1402 offset_tax_rate_code,
1403 allow_exemptions_flag,
1404 allow_exceptions_flag,
1405 NULL tax_jurisdiction_id,
1406 def_rec_settlement_option_code,
1407 taxable_basis_formula_code,
1408 adj_for_adhoc_amt_code,
1409 inclusive_tax_flag,
1410 tax_class
1411 FROM ZX_SCO_RATES_B_V -- Bug#5395227
1412 WHERE tax_status_code = p_tax_status_code
1413 AND tax = p_tax
1414 AND tax_regime_code = p_tax_regime_code
1415 AND tax_jurisdiction_code IS NULL
1416 AND default_flg_effective_from <= p_tax_date
1417 AND (default_flg_effective_to >= p_tax_date
1418 OR default_flg_effective_to IS NULL)
1419 AND Default_Rate_Flag = 'Y'
1420 AND Active_Flag = 'Y'
1421 AND tax_class IS NULL
1422 ORDER BY subscription_level_code; -- Bug#5395227
1423
1424 CURSOR get_def_tax_rate_no_jur_mi_csr IS
1425 SELECT tax_regime_code,
1426 tax,
1427 tax_status_code,
1428 tax_rate_code,
1429 tax_rate_id,
1430 effective_from,
1431 effective_to,
1432 rate_type_code,
1433 percentage_rate,
1434 quantity_rate,
1435 Allow_Adhoc_Tax_Rate_Flag,
1436 uom_code,
1437 tax_jurisdiction_code,
1438 offset_tax,
1439 offset_status_code,
1440 offset_tax_rate_code,
1441 allow_exemptions_flag,
1442 allow_exceptions_flag,
1443 NULL tax_jurisdiction_id,
1444 def_rec_settlement_option_code,
1445 taxable_basis_formula_code,
1446 adj_for_adhoc_amt_code,
1447 inclusive_tax_flag,
1448 tax_class
1449 FROM ZX_SCO_RATES_B_V -- Bug#5395227
1450 WHERE tax_status_code = p_tax_status_code
1451 AND tax = p_tax
1452 AND tax_regime_code = p_tax_regime_code
1453 AND tax_jurisdiction_code IS NULL
1454 AND default_flg_effective_from <= p_tax_date
1455 AND (default_flg_effective_to >= p_tax_date
1456 OR default_flg_effective_to IS NULL)
1457 AND Default_Rate_Flag = 'Y'
1458 AND Active_Flag = 'Y'
1459 AND tax_class = p_tax_class
1460 ORDER BY subscription_level_code; -- Bug#5395227
1461
1462 l_procedure_name CONSTANT VARCHAR2(30) := 'get_def_tax_rate_no_jur_code';
1463 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
1464 --l_tax_rate_rec_tmp ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
1465
1466 BEGIN
1467 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1468
1469 IF (g_level_procedure >= g_current_runtime_level ) THEN
1470 FND_LOG.STRING(g_level_procedure,
1471 g_module_name||'.'||l_procedure_name||'.BEGIN',
1472 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_no_jur_code(+) ');
1473 END IF;
1474 x_return_status := FND_API.G_RET_STS_SUCCESS;
1475
1476 -- Try to get tax rate with NULL tax class first
1477 --
1478 OPEN get_def_tax_rate_no_jur_csr;
1479
1480 FETCH get_def_tax_rate_no_jur_csr INTO l_tax_rate_rec;
1481 IF get_def_tax_rate_no_jur_csr%FOUND THEN
1482
1483 /* Bug#5395227- don't do a 2nd fetch
1484 FETCH get_def_tax_rate_no_jur_csr INTO l_tax_rate_rec_tmp;
1485
1486 IF get_def_tax_rate_no_jur_csr%FOUND THEN
1487 -- raise error for multiple rate found
1488 x_return_status:= FND_API.G_RET_STS_ERROR;
1489
1490 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTI_DEFAULT_RATES_FOUND');
1491 FND_MESSAGE.SET_TOKEN('RATE_REGIME_CODE', p_tax_regime_code);
1492 FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax);
1493 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
1494 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
1495 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
1496
1497 CLOSE get_def_tax_rate_no_jur_csr;
1498 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1499 FND_LOG.STRING(g_level_unexpected,
1500 g_module_name||'.'||l_procedure_name,
1501 'Too Many Tax Rate Rows Retrived. ');
1502 FND_LOG.STRING(g_level_unexpected,
1503 g_module_name||'.'||l_procedure_name||'.END',
1504 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_no_jur_code(-) ');
1505 END IF;
1506 RETURN;
1507 ELSE
1508 ***** Bug#5395227 ****/
1509
1510 x_tax_rate_rec := l_tax_rate_rec;
1511 CLOSE get_def_tax_rate_no_jur_csr;
1512
1513 IF (g_level_statement >= g_current_runtime_level ) THEN
1514 FND_LOG.STRING(g_level_statement,
1515 g_module_name||'.'||l_procedure_name,
1516 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
1517 FND_LOG.STRING(g_level_statement,
1518 g_module_name||'.'||l_procedure_name,
1519 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
1520 END IF;
1521
1522 -- END IF;
1523 ELSE -- get_def_tax_rate_no_jur_csr%NOTFOUND
1524 CLOSE get_def_tax_rate_no_jur_csr;
1525
1526 -- tax rate is not found for NULL tax_class, try to get it with p_tax_class
1527 --
1528 IF (g_level_statement >= g_current_runtime_level ) THEN
1529 FND_LOG.STRING(g_level_statement,
1530 g_module_name||'.'||l_procedure_name,
1531 'tax_class = ' || p_tax_class);
1532 END IF;
1533
1534 OPEN get_def_tax_rate_no_jur_mi_csr;
1535
1536 FETCH get_def_tax_rate_no_jur_mi_csr INTO l_tax_rate_rec;
1537 IF get_def_tax_rate_no_jur_mi_csr%FOUND THEN
1538
1539 /* Bug#5395227- don't do a 2nd fetch
1540 FETCH get_def_tax_rate_no_jur_mi_csr INTO l_tax_rate_rec_tmp;
1541
1542 IF get_def_tax_rate_no_jur_mi_csr%FOUND THEN
1543 -- raise error for multiple rate found
1544 x_return_status:= FND_API.G_RET_STS_ERROR;
1545
1546 FND_MESSAGE.SET_NAME ('ZX','ZX_MULTI_DEFAULT_RATES_FOUND');
1547 FND_MESSAGE.SET_TOKEN('RATE_REGIME_CODE', p_tax_regime_code);
1548 FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax);
1549 FND_MESSAGE.SET_TOKEN('TAX_DET_DATE',p_tax_date);
1550 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
1551 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
1552
1553 CLOSE get_def_tax_rate_no_jur_mi_csr;
1554 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1555 FND_LOG.STRING(g_level_unexpected,
1556 g_module_name||'.'||l_procedure_name,
1557 'Too Many Tax Rate Rows Retrived. ');
1558 FND_LOG.STRING(g_level_unexpected,
1559 g_module_name||'.'||l_procedure_name||'.END',
1560 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_no_jur_code(-) ');
1561 END IF;
1562 RETURN;
1563 ELSE
1564 ***** Bug#5395227 ****/
1565
1566 x_tax_rate_rec := l_tax_rate_rec;
1567 CLOSE get_def_tax_rate_no_jur_mi_csr;
1568
1569 IF (g_level_statement >= g_current_runtime_level ) THEN
1570 FND_LOG.STRING(g_level_statement,
1571 g_module_name||'.'||l_procedure_name,
1572 'Found Tax rate: tax_rate_id: ' || x_tax_rate_rec.tax_rate_id );
1573 FND_LOG.STRING(g_level_statement,
1574 g_module_name||'.'||l_procedure_name,
1575 'tax_rate_code: ' || x_tax_rate_rec.tax_rate_code );
1576 END IF;
1577
1578 -- END IF;
1579 ELSE -- get_def_tax_rate_no_jur_mi_csr%NOTFOUND
1580 CLOSE get_def_tax_rate_no_jur_mi_csr;
1581 END IF;
1582
1583 END IF;
1584
1585 IF (g_level_procedure >= g_current_runtime_level ) THEN
1586 FND_LOG.STRING(g_level_procedure,
1587 g_module_name||'.'||l_procedure_name||'.END',
1588 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_no_jur_code(-) ');
1589 END IF;
1590
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1594 IF get_def_tax_rate_no_jur_csr%ISOPEN THEN
1595 CLOSE get_def_tax_rate_no_jur_csr;
1596 END IF;
1597 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1598 FND_LOG.STRING(g_level_unexpected,
1599 g_module_name||'.'||l_procedure_name,
1600 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1601 FND_LOG.STRING(g_level_unexpected,
1602 g_module_name||'.'||l_procedure_name||'.END',
1603 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_no_jur_code(-) ');
1604 END IF;
1605 END get_def_tax_rate_no_jur_code;
1606
1607 ------------------------------------------------------------------------------
1608 -- PRIVATE PROCEDURE
1609 -- get_def_tax_rate_internal
1610 --
1611 -- DESCRIPTION
1612 -- This procedure find tax rate information match the passed in tax info by
1613 -- first getting the tax jurisdiction hierarchy
1614 ------------------------------------------------------------------------------
1615
1616 PROCEDURE get_tax_rate_internal(
1617 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
1618 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
1619 p_tax IN ZX_RATES_B.tax%TYPE,
1620 p_tax_date IN DATE,
1621 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
1622 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
1623 p_place_of_supply_type_code IN ZX_LINES.place_of_supply_type_code%TYPE,
1624 p_structure_index IN NUMBER,
1625 p_multiple_jurisdictions_flag IN VARCHAR2,
1626 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
1627 x_return_status OUT NOCOPY VARCHAR2
1628 ) IS
1629
1630 l_procedure_name CONSTANT VARCHAR2(30) := 'get_tax_rate_internal';
1631 l_structure_name VARCHAR2(30);
1632 l_location_id NUMBER;
1633 l_tax_jurisdiction_code ZX_RATES_B.tax_jurisdiction_code%TYPE;
1634 l_tax_param_code VARCHAR2(30);
1635 l_jurisdictions_found VARCHAR2(1);
1636 l_tax_jurisdiction_rec ZX_TCM_GEO_JUR_PKG.tax_jurisdiction_rec_type;
1637 l_multiple_jurisdictions_flag VARCHAR2(1);
1638 BEGIN
1639 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1640
1641 IF (g_level_procedure >= g_current_runtime_level ) THEN
1642 FND_LOG.STRING(g_level_procedure,
1643 g_module_name||'.'||l_procedure_name||'.BEGIN',
1644 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_internal(+) ');
1645 END IF;
1646
1647 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1648
1649 -- get location_id
1650 l_structure_name := 'TRX_LINE_DIST_TBL';
1651
1652 IF (g_level_statement >= g_current_runtime_level ) THEN
1653 FND_LOG.STRING(g_level_statement,
1654 g_module_name||'.'||l_procedure_name,
1655 'Calling ZX_GET_TAX_PARAM_DRIVER_PKG.get_driver_value...');
1656 END IF;
1657
1658 l_tax_param_code := ZX_TDS_APPLICABILITY_DETM_PKG.get_pos_parameter_name(
1659 p_place_of_supply_type_code,
1660 x_return_status);
1661
1662 IF NVL(x_return_status, FND_API.G_RET_STS_ERROR) <> FND_API.G_RET_STS_SUCCESS
1663 THEN
1664 -- TCM procedure called in get_pos_parameter_name will set the error msg
1665 -- here we just need to return to the calling point which will populate
1666 -- the context information.
1667 IF (g_level_statement >= g_current_runtime_level ) THEN
1668 FND_LOG.STRING(g_level_statement,
1669 g_module_name||'.'||l_procedure_name,
1670 'Incorrect return_status after calling ' ||
1671 'ZX_TDS_APPLICABILITY_DETM_PKG.get_pos_parameter_name');
1672 FND_LOG.STRING(g_level_statement,
1673 g_module_name||'.'||l_procedure_name,
1674 'RETURN_STATUS = ' || x_return_status);
1675 FND_LOG.STRING(g_level_statement,
1676 g_module_name||'.'||l_procedure_name||'.END',
1677 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_internal(-) ');
1678 END IF;
1679 RETURN;
1680 END IF;
1681
1682 ZX_GET_TAX_PARAM_DRIVER_PKG.get_driver_value(
1683 p_struct_name => l_structure_name,
1684 p_struct_index => p_structure_index,
1685 p_tax_param_code => l_tax_param_code,
1686 x_tax_param_value => l_location_id,
1687 x_return_status => x_return_status );
1688
1689 IF NVL(x_return_status, FND_API.G_RET_STS_ERROR) <> FND_API.G_RET_STS_SUCCESS
1690 THEN
1691 IF (g_level_statement >= g_current_runtime_level ) THEN
1692 FND_LOG.STRING(g_level_statement,
1693 g_module_name||'.'||l_procedure_name,
1694 'Incorrect return_status after calling ' ||
1695 'ZX_GET_TAX_PARAM_DRIVER_PKG.get_driver_value');
1696 FND_LOG.STRING(g_level_statement,
1697 g_module_name||'.'||l_procedure_name,
1698 'RETURN_STATUS = ' || x_return_status);
1699 FND_LOG.STRING(g_level_statement,
1700 g_module_name||'.'||l_procedure_name||'.END',
1701 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_internal(-)');
1702 END IF;
1703 RETURN;
1704 END IF;
1705
1706 -- get tax jurisdiction hierarchy
1707 IF l_location_id IS NOT NULL THEN
1708 -- get the jurisdiction
1709 --
1710 ZX_TCM_GEO_JUR_PKG.get_tax_jurisdictions (
1711 p_location_id => l_location_id,
1712 p_location_type => p_place_of_supply_type_code,
1713 p_tax => p_tax,
1714 p_tax_regime_code => p_tax_regime_code,
1715 p_trx_date => p_tax_date,
1716 x_tax_jurisdiction_rec => l_tax_jurisdiction_rec,
1717 x_jurisdictions_found => l_jurisdictions_found,
1718 x_return_status => x_return_status);
1719
1720 IF NVL(x_return_status, FND_API.G_RET_STS_ERROR) <> FND_API.G_RET_STS_SUCCESS
1721 THEN
1722 IF (g_level_statement >= g_current_runtime_level ) THEN
1723 FND_LOG.STRING(g_level_statement,
1724 g_module_name||'.'||l_procedure_name,
1725 'Incorrect return_status after calling ' ||
1726 'ZX_TCM_GEO_JUR_PKG.get_tax_jurisdiction');
1727 FND_LOG.STRING(g_level_statement,
1728 g_module_name||'.'||l_procedure_name,
1729 'RETURN_STATUS = ' || x_return_status);
1730 FND_LOG.STRING(g_level_statement,
1731 g_module_name||'.'||l_procedure_name||'.END',
1732 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_internal(-)');
1733 END IF;
1734 RETURN;
1735 END IF;
1736 END IF;
1737
1738 -- get tax rate loop through the jurisdiction hierarchy
1739 IF l_jurisdictions_found = 'Y' THEN
1740 IF l_tax_jurisdiction_rec.tax_jurisdiction_code IS NULL THEN
1741 l_multiple_jurisdictions_flag := 'Y';
1742 ELSE
1743 l_multiple_jurisdictions_flag := 'N';
1744 END IF;
1745
1746 get_tax_rate(
1747 p_event_class_rec => p_event_class_rec,
1748 p_tax_regime_code => p_tax_regime_code,
1749 p_tax_jurisdiction_code => l_tax_jurisdiction_rec.tax_jurisdiction_code,
1750 p_tax => p_tax,
1751 p_tax_date => p_tax_date,
1752 p_tax_status_code => p_tax_status_code,
1753 p_tax_rate_code => p_tax_rate_code,
1754 p_place_of_supply_type_code => p_place_of_supply_type_code,
1755 p_structure_index => p_structure_index,
1756 p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
1757 x_tax_rate_rec => x_tax_rate_rec,
1758 x_return_status => x_return_status
1759 );
1760 END IF;
1761
1762 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1763 IF (g_level_statement >= g_current_runtime_level ) THEN
1764 FND_LOG.STRING(g_level_statement,
1765 g_module_name||'.'||l_procedure_name,
1766 'Incorrect return_status after calling ' ||
1767 'ZX_TCM_TAX_RATE_PKG.get_tax_rate');
1768 FND_LOG.STRING(g_level_statement,
1769 g_module_name||'.'||l_procedure_name,
1770 'RETURN_STATUS = ' || x_return_status);
1771 FND_LOG.STRING(g_level_statement,
1772 g_module_name||'.'||l_procedure_name||'.END',
1773 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_internal(-)');
1774 END IF;
1775 RETURN;
1776 END IF;
1777
1778 IF (g_level_procedure >= g_current_runtime_level ) THEN
1779 FND_LOG.STRING(g_level_procedure,
1780 g_module_name||'.'||l_procedure_name||'.END',
1781 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_internal(-) ');
1782 END IF;
1783
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1787 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1788 FND_LOG.STRING(g_level_unexpected,
1789 g_module_name||'.'||l_procedure_name,
1790 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1791 FND_LOG.STRING(g_level_unexpected,
1792 g_module_name||'.'||l_procedure_name||'.END',
1793 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_internal(-) ');
1794 END IF;
1795
1796 END get_tax_rate_internal;
1797
1798 ------------------------------------------------------------------------------
1799 -- PRIVATE PROCEDURE
1800 -- get_tax_rate_pvt
1801 --
1802 -- DESCRIPTION
1803 -- This procedure find tax rate information match the passed in tax info
1804 ------------------------------------------------------------------------------
1805
1806 PROCEDURE get_tax_rate_pvt(
1807 p_tax_class IN VARCHAR2,
1808 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
1809 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
1810 p_tax IN ZX_RATES_B.tax%TYPE,
1811 p_tax_date IN DATE,
1812 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
1813 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
1814 p_place_of_supply_type_code IN ZX_LINES.place_of_supply_type_code%TYPE,
1815 p_structure_index IN NUMBER,
1816 p_multiple_jurisdictions_flag IN VARCHAR2,
1817 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
1818 x_return_status OUT NOCOPY VARCHAR2
1819 ) IS
1820
1821 l_procedure_name CONSTANT VARCHAR2(30) := 'get_tax_rate_pvt';
1822 l_tax_jurisdiction_code ZX_RATES_B.tax_jurisdiction_code%TYPE;
1823 l_ind_rec NUMBER;
1824 BEGIN
1825
1826 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1827
1828 IF (g_level_procedure >= g_current_runtime_level ) THEN
1829 FND_LOG.STRING(g_level_procedure,
1830 g_module_name||'.'||l_procedure_name||'.BEGIN',
1831 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt(+) ');
1832 END IF;
1833
1834 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1835
1836 IF (g_level_statement >= g_current_runtime_level ) THEN
1837 FND_LOG.STRING(g_level_statement,
1838 g_module_name||'.'||l_procedure_name,
1839 'p_multiple_jurisdictions_flag: '
1840 || p_multiple_jurisdictions_flag);
1841 END IF;
1842
1843 IF p_multiple_jurisdictions_flag = 'Y' THEN
1844
1845 get_tax_rate_by_jur_gt(
1846 p_tax_class => p_tax_class,
1847 p_tax_regime_code => p_tax_regime_code,
1848 p_tax => p_tax,
1849 p_tax_date => p_tax_date,
1850 p_tax_status_code => p_tax_status_code,
1851 p_tax_rate_code => p_tax_rate_code,
1852 x_tax_rate_rec => x_tax_rate_rec,
1853 x_return_status => x_return_status
1854 );
1855
1856 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1857 IF (g_level_statement >= g_current_runtime_level ) THEN
1858 FND_LOG.STRING(g_level_statement,
1859 g_module_name||'.'||l_procedure_name,
1860 'Incorrect return_status after calling ' ||
1861 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_code');
1862 FND_LOG.STRING(g_level_statement,
1863 g_module_name||'.'||l_procedure_name,
1864 'RETURN_STATUS = ' || x_return_status);
1865 FND_LOG.STRING(g_level_statement,
1866 g_module_name||'.'||l_procedure_name||'.END',
1867 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt(-)');
1868 END IF;
1869 RETURN;
1870 END IF;
1871
1872 -- if valid tax rate info found, then set the jurisdiction
1873 -- and return to the calling side.
1874 IF x_tax_rate_rec.tax_rate_id IS NOT NULL THEN
1875 IF (g_level_procedure >= g_current_runtime_level ) THEN
1876 FND_LOG.STRING(g_level_procedure,
1877 g_module_name||'.'||l_procedure_name||'.END',
1878 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt(-) ');
1879 END IF;
1880 RETURN;
1881 END IF;
1882 END IF;
1883
1884 IF p_tax_jurisdiction_code IS NOT NULL AND x_tax_rate_rec.tax_rate_id IS NULL
1885 THEN -- p_multiple_jurisdictions_flag = 'N'
1886 -- single jurisdiction case
1887 get_tax_rate_by_jur_code(
1888 p_tax_class => p_tax_class,
1889 p_tax_regime_code => p_tax_regime_code,
1890 p_tax_jurisdiction_code => p_tax_jurisdiction_code,
1891 p_tax => p_tax,
1892 p_tax_date => p_tax_date,
1893 p_tax_status_code => p_tax_status_code,
1894 p_tax_rate_code => p_tax_rate_code,
1895 x_tax_rate_rec => x_tax_rate_rec,
1896 x_return_status => x_return_status
1897 );
1898
1899 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1900 IF (g_level_statement >= g_current_runtime_level ) THEN
1901 FND_LOG.STRING(g_level_statement,
1902 g_module_name||'.'||l_procedure_name,
1903 'Incorrect return_status after calling ' ||
1904 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_code');
1905 FND_LOG.STRING(g_level_statement,
1906 g_module_name||'.'||l_procedure_name,
1907 'RETURN_STATUS = ' || x_return_status);
1908 FND_LOG.STRING(g_level_statement,
1909 g_module_name||'.'||l_procedure_name||'.END',
1910 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt(-)');
1911 END IF;
1912 RETURN;
1913 END IF;
1914
1915 -- if valid tax rate info found, then set the jurisdiction
1916 -- and return to the calling side.
1917 IF x_tax_rate_rec.tax_rate_id IS NOT NULL THEN
1918 IF (g_level_procedure >= g_current_runtime_level ) THEN
1919 FND_LOG.STRING(g_level_procedure,
1920 g_module_name||'.'||l_procedure_name||'.END',
1921 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt(-) ');
1922 END IF;
1923 RETURN;
1924 END IF;
1925 END IF;
1926
1927 -- if no tax_jurisdiction_code on the detail tax line or if no tax rate %
1928 -- found for the jurisdiction code on the detail tax line, go to find
1929 -- tax rate without jurisdiction code
1930 IF x_tax_rate_rec.tax_rate_id IS NULL THEN
1931 get_tax_rate_no_jur_code(
1932 p_tax_class => p_tax_class,
1933 p_tax_regime_code => p_tax_regime_code,
1934 p_tax => p_tax,
1935 p_tax_date => p_tax_date,
1936 p_tax_status_code => p_tax_status_code,
1937 p_tax_rate_code => p_tax_rate_code,
1938 x_tax_rate_rec => x_tax_rate_rec,
1939 x_return_status => x_return_status
1940 );
1941 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1942 IF (g_level_statement >= g_current_runtime_level ) THEN
1943 FND_LOG.STRING(g_level_statement,
1944 g_module_name||'.'||l_procedure_name,
1945 'Incorrect return_status after calling ' ||
1946 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_no_jur_code');
1947 FND_LOG.STRING(g_level_statement,
1948 g_module_name||'.'||l_procedure_name,
1949 'RETURN_STATUS = ' || x_return_status);
1950 FND_LOG.STRING(g_level_statement,
1951 g_module_name||'.'||l_procedure_name||'.END',
1952 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt(-)');
1953 END IF;
1954 RETURN;
1955 END IF;
1956 END IF;
1957
1958 -- raise error when no tax rate found finally.
1959 IF x_tax_rate_rec.tax_rate_id IS NULL THEN
1960 x_return_status:= FND_API.G_RET_STS_ERROR;
1961 FND_MESSAGE.SET_NAME('ZX','ZX_RATE_NOT_FOUND');
1962 FND_MESSAGE.SET_TOKEN('TAX_STATUS',p_tax_status_code);
1963 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
1964 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
1965
1966 IF (g_level_statement >= g_current_runtime_level) THEN
1967 FND_LOG.STRING(g_level_statement,
1968 g_module_name||'.'||l_procedure_name,
1969 'No rate found for tax rate code: ' ||p_tax_rate_code);
1970 END IF;
1971 END IF;
1972
1973 IF (g_level_procedure >= g_current_runtime_level ) THEN
1974 FND_LOG.STRING(g_level_procedure,
1975 g_module_name||'.'||l_procedure_name||'.END',
1976 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt(-) ');
1977 END IF;
1978
1979 EXCEPTION
1980 WHEN OTHERS THEN
1981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1982 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1983 FND_LOG.STRING(g_level_unexpected,
1984 g_module_name||'.'||l_procedure_name,
1985 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1986 FND_LOG.STRING(g_level_unexpected,
1987 g_module_name||'.'||l_procedure_name||'.END',
1988 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt(-) ');
1989 END IF;
1990 END get_tax_rate_pvt;
1991
1992 ------------------------------------------------------------------------------
1993 -- PRIVATE PROCEDURE
1994 -- get_def_tax_rate_pvt
1995 --
1996 -- DESCRIPTION
1997 -- This procedure find tax rate information match the passed in tax info
1998 ------------------------------------------------------------------------------
1999
2000 PROCEDURE get_def_tax_rate_pvt(
2001 p_tax_class IN VARCHAR2,
2002 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
2003 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
2004 p_tax IN ZX_RATES_B.tax%TYPE,
2005 p_tax_date IN DATE,
2006 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
2007 p_place_of_supply_type_code IN ZX_LINES.place_of_supply_type_code%TYPE,
2008 p_structure_index IN NUMBER,
2009 p_multiple_jurisdictions_flag IN VARCHAR2,
2010 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
2011 x_return_status OUT NOCOPY VARCHAR2
2012 ) IS
2013
2014 l_procedure_name CONSTANT VARCHAR2(30) := 'get_def_tax_rate_pvt';
2015
2016 BEGIN
2017
2018 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2019
2020 IF (g_level_procedure >= g_current_runtime_level ) THEN
2021 FND_LOG.STRING(g_level_procedure,
2022 g_module_name||'.'||l_procedure_name||'.BEGIN',
2023 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt(+) ');
2024 END IF;
2025
2026 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2027
2028 IF (g_level_statement >= g_current_runtime_level ) THEN
2029 FND_LOG.STRING(g_level_statement,
2030 g_module_name||'.'||l_procedure_name,
2031 'p_multiple_jurisdictions_flag: '
2032 || p_multiple_jurisdictions_flag);
2033 END IF;
2034
2035 -- for p_multiple_jurisdictions_flag
2036 IF p_multiple_jurisdictions_flag = 'Y' THEN
2037
2038 get_def_tax_rate_by_jur_gt(
2039 p_tax_class => p_tax_class,
2040 p_tax_regime_code => p_tax_regime_code,
2041 p_tax => p_tax,
2042 p_tax_date => p_tax_date,
2043 p_tax_status_code => p_tax_status_code,
2044 x_tax_rate_rec => x_tax_rate_rec,
2045 x_return_status => x_return_status
2046 );
2047
2048 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2049 IF (g_level_statement >= g_current_runtime_level ) THEN
2050 FND_LOG.STRING(g_level_statement,
2051 g_module_name||'.'||l_procedure_name,
2052 'Incorrect return_status after calling ' ||
2053 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_gt');
2054 FND_LOG.STRING(g_level_statement,
2055 g_module_name||'.'||l_procedure_name,
2056 'RETURN_STATUS = ' || x_return_status);
2057 FND_LOG.STRING(g_level_statement,
2058 g_module_name||'.'||l_procedure_name||'.END',
2059 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt(-)');
2060 END IF;
2061 RETURN;
2062 END IF;
2063
2064 -- if valid tax rate info found, return to the calling side.
2065 IF x_tax_rate_rec.tax_rate_id IS NOT NULL THEN
2066 IF (g_level_procedure >= g_current_runtime_level ) THEN
2067 FND_LOG.STRING(g_level_procedure,
2068 g_module_name||'.'||l_procedure_name||'.END',
2069 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt(-) ');
2070 END IF;
2071 RETURN;
2072 END IF;
2073
2074 END IF;
2075
2076 IF p_tax_jurisdiction_code IS NOT NULL AND x_tax_rate_rec.tax_rate_id IS NULL
2077 THEN --p_multiple_jurisdictions_flag = 'N'
2078
2079 -- single jurisdiction case
2080 get_def_tax_rate_by_jur_code(
2081 p_tax_class => p_tax_class,
2082 p_tax_regime_code => p_tax_regime_code,
2083 p_tax_jurisdiction_code => p_tax_jurisdiction_code,
2084 p_tax => p_tax,
2085 p_tax_date => p_tax_date,
2086 p_tax_status_code => p_tax_status_code,
2087 x_tax_rate_rec => x_tax_rate_rec,
2088 x_return_status => x_return_status
2089 );
2090
2091 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2092 IF (g_level_statement >= g_current_runtime_level ) THEN
2093 FND_LOG.STRING(g_level_statement,
2094 g_module_name||'.'||l_procedure_name,
2095 'Incorrect return_status after calling ' ||
2096 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_by_jur_code');
2097 FND_LOG.STRING(g_level_statement,
2098 g_module_name||'.'||l_procedure_name,
2099 'RETURN_STATUS = ' || x_return_status);
2100 FND_LOG.STRING(g_level_statement,
2101 g_module_name||'.'||l_procedure_name||'.END',
2102 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt(-)');
2103 END IF;
2104 RETURN;
2105 END IF;
2106
2107 -- if valid tax rate info found, return to the calling side.
2108 IF x_tax_rate_rec.tax_rate_id IS NOT NULL THEN
2109 IF (g_level_procedure >= g_current_runtime_level ) THEN
2110 FND_LOG.STRING(g_level_procedure,
2111 g_module_name||'.'||l_procedure_name||'.END',
2112 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt(-) ');
2113 END IF;
2114 RETURN;
2115 END IF;
2116
2117 END IF;
2118
2119 -- if no tax rate found, go to find tax rate without jurisdiction code
2120 IF x_tax_rate_rec.tax_rate_id IS NULL THEN
2121 get_def_tax_rate_no_jur_code(
2122 p_tax_class => p_tax_class,
2123 p_tax_regime_code => p_tax_regime_code,
2124 p_tax => p_tax,
2125 p_tax_date => p_tax_date,
2126 p_tax_status_code => p_tax_status_code,
2127 x_tax_rate_rec => x_tax_rate_rec,
2128 x_return_status => x_return_status
2129 );
2130 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2131 IF (g_level_statement >= g_current_runtime_level ) THEN
2132 FND_LOG.STRING(g_level_statement,
2133 g_module_name||'.'||l_procedure_name,
2134 'Incorrect return_status after calling ' ||
2135 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_no_jur_code');
2136 FND_LOG.STRING(g_level_statement,
2137 g_module_name||'.'||l_procedure_name,
2138 'RETURN_STATUS = ' || x_return_status);
2139 FND_LOG.STRING(g_level_statement,
2140 g_module_name||'.'||l_procedure_name||'.END',
2141 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt(-)');
2142 END IF;
2143 RETURN;
2144 END IF;
2145 END IF;
2146
2147
2148 -- raise error when no tax rate found finally.
2149 IF x_tax_rate_rec.tax_rate_id IS NULL THEN
2150 x_return_status:= FND_API.G_RET_STS_ERROR;
2151
2152 FND_MESSAGE.SET_NAME('ZX','ZX_DEFAULT_RATE_NOT_FOUND');
2153 FND_MESSAGE.SET_TOKEN('TAX_STATUS',p_tax_status_code);
2154 FND_MESSAGE.SET_TOKEN('TAX',p_tax);
2155 FND_MESSAGE.SET_TOKEN('DATE',p_tax_date);
2156
2157 IF (g_level_statement >= g_current_runtime_level) THEN
2158 FND_LOG.STRING(g_level_statement,
2159 g_module_name||'.'||l_procedure_name,
2160 'No default rate found for tax rate code');
2161 END IF;
2162 END IF;
2163
2164 IF (g_level_procedure >= g_current_runtime_level ) THEN
2165 FND_LOG.STRING(g_level_procedure,
2166 g_module_name||'.'||l_procedure_name||'.END',
2167 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt(-) ');
2168 END IF;
2169
2170 EXCEPTION
2171 WHEN OTHERS THEN
2172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2173 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2174 FND_LOG.STRING(g_level_unexpected,
2175 g_module_name||'.'||l_procedure_name,
2176 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2177 FND_LOG.STRING(g_level_unexpected,
2178 g_module_name||'.'||l_procedure_name||'.END',
2179 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt(-) ');
2180 END IF;
2181
2182 END get_def_tax_rate_pvt;
2183
2184 ------------------------------------------------------------------------------
2185 -- PUBLIC PROCEDURE
2186 -- get_tax_rate
2187 --
2188 -- DESCRIPTION
2189 -- This procedure find tax rate information match the passed in tax info
2190 ------------------------------------------------------------------------------
2191
2192 PROCEDURE get_tax_rate(
2193 p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
2194 p_tax_regime_code IN ZX_RATES_B.tax_regime_code%TYPE,
2195 p_tax_jurisdiction_code IN ZX_RATES_B.tax_jurisdiction_code%TYPE,
2196 p_tax IN ZX_RATES_B.tax%TYPE,
2197 p_tax_date IN DATE,
2198 p_tax_status_code IN ZX_RATES_B.tax_status_code%TYPE,
2199 p_tax_rate_code IN ZX_RATES_B.tax_rate_code%TYPE,
2200 p_place_of_supply_type_code IN ZX_LINES.place_of_supply_type_code%TYPE,
2201 p_structure_index IN NUMBER,
2202 p_multiple_jurisdictions_flag IN VARCHAR2,
2203 x_tax_rate_rec OUT NOCOPY ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type,
2204 x_return_status OUT NOCOPY VARCHAR2
2205 ) IS
2206 l_procedure_name CONSTANT VARCHAR2(30) := 'get_tax_rate';
2207 l_count_jur NUMBER;
2208 l_tax_class VARCHAR2(30);
2209 l_tbl_index BINARY_INTEGER;
2210 BEGIN
2211 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2212
2213 IF (g_level_procedure >= g_current_runtime_level ) THEN
2214 FND_LOG.STRING(g_level_procedure,
2215 g_module_name||'.'||l_procedure_name||'.BEGIN',
2216 'ZX_TCM_TAX_RATE_PKG.get_tax_rate(+) ');
2217 END IF;
2218
2219 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2220
2221 IF (g_level_statement >= g_current_runtime_level ) THEN
2222 FND_LOG.STRING(g_level_statement,
2223 g_module_name||'.'||l_procedure_name,
2224 'p_tax = ' || p_tax);
2225 FND_LOG.STRING(g_level_statement,
2226 g_module_name||'.'||l_procedure_name,
2227 'p_tax_status_code = ' || p_tax_status_code);
2228 FND_LOG.STRING(g_level_statement,
2229 g_module_name||'.'||l_procedure_name,
2230 'p_tax_regime_code = ' || p_tax_regime_code);
2231 FND_LOG.STRING(g_level_statement,
2232 g_module_name||'.'||l_procedure_name,
2233 'p_tax_jurisdiction_code = ' || p_tax_jurisdiction_code);
2234 FND_LOG.STRING(g_level_statement,
2235 g_module_name||'.'||l_procedure_name,
2236 'p_tax_date = ' || p_tax_date);
2237 END IF;
2238
2239
2240 -- for general create and update case
2241 -- the ZX_JURISDICTIONS_GT will be populated if multiple jurisdictions found
2242 -- for jurisdiction code overriden case
2243 -- the p_multiple_jurisdictions_flag will be set to 'N' from the UI
2244 -- for other override case. eg. status / rate code override.
2245 -- global jurisdiction table: ZX_JURISDICTIONS_GT is empty need to call tcm API
2246 -- to retrieve the jurisdiction info again
2247
2248 IF p_multiple_jurisdictions_flag ='Y' THEN
2249 SELECT COUNT(*) into l_count_jur
2250 FROM ZX_JURISDICTIONS_GT;
2251
2252 IF l_count_jur = 0 THEN
2253 get_tax_rate_internal(
2254 p_event_class_rec => p_event_class_rec,
2255 p_tax_regime_code => p_tax_regime_code,
2256 p_tax => p_tax,
2257 p_tax_date => p_tax_date,
2258 p_tax_status_code => p_tax_status_code,
2259 p_tax_rate_code => NULL,
2260 p_place_of_supply_type_code => p_place_of_supply_type_code,
2261 p_structure_index => p_structure_index,
2262 p_multiple_jurisdictions_flag => p_multiple_jurisdictions_flag,
2263 x_tax_rate_rec => x_tax_rate_rec,
2264 x_return_status => x_return_status
2265 );
2266 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2267 IF (g_level_statement >= g_current_runtime_level ) THEN
2268 FND_LOG.STRING(g_level_statement,
2269 g_module_name||'.'||l_procedure_name,
2270 'Incorrect return_status after calling ' ||
2271 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_internal');
2272 FND_LOG.STRING(g_level_statement,
2273 g_module_name||'.'||l_procedure_name,
2274 'RETURN_STATUS = ' || x_return_status);
2275 FND_LOG.STRING(g_level_statement,
2276 g_module_name||'.'||l_procedure_name||'.END',
2277 'ZX_TCM_TAX_RATE_PKG.get_tax_rate(-)');
2278 END IF;
2279 RETURN;
2280 END IF;
2281 END IF;
2282 END IF;
2283
2284 IF p_event_class_rec.prod_family_grp_code = 'O2C' THEN
2285 l_tax_class := 'OUTPUT';
2286 ELSIF p_event_class_rec.prod_family_grp_code = 'P2P' THEN
2287 l_tax_class := 'INPUT';
2288 END IF;
2289
2290 IF p_tax_rate_code IS NOT NULL THEN
2291 -- l_tax_rate_code is available on tax line OR
2292 -- Rule base engine returns l_tax_rate_code
2293 --
2294 get_tax_rate_pvt(
2295 p_tax_class => l_tax_class,
2296 p_tax_regime_code => p_tax_regime_code,
2297 p_tax_jurisdiction_code => p_tax_jurisdiction_code,
2298 p_tax => p_tax,
2299 p_tax_date => p_tax_date,
2300 p_tax_status_code => p_tax_status_code,
2301 p_tax_rate_code => p_tax_rate_code,
2302 p_place_of_supply_type_code => p_place_of_supply_type_code,
2303 p_structure_index => p_structure_index,
2304 p_multiple_jurisdictions_flag => p_multiple_jurisdictions_flag,
2305 x_tax_rate_rec => x_tax_rate_rec,
2306 x_return_status => x_return_status
2307 );
2308
2309 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2310 IF (g_level_statement >= g_current_runtime_level ) THEN
2311 FND_LOG.STRING(g_level_statement,
2312 g_module_name||'.'||l_procedure_name,
2313 'Incorrect return_status after calling ' ||
2314 'ZX_TCM_TAX_RATE_PKG.get_tax_rate_pvt');
2315 FND_LOG.STRING(g_level_statement,
2316 g_module_name||'.'||l_procedure_name,
2317 'RETURN_STATUS = ' || x_return_status);
2318 FND_LOG.STRING(g_level_statement,
2319 g_module_name||'.'||l_procedure_name||'.END',
2320 'ZX_TCM_TAX_RATE_PKG.get_tax_rate(-)');
2321 END IF;
2322 -- lower level should assign the fnd msg stack
2323 RETURN;
2324 END IF;
2325 END IF; ---- p_tax_rate_code is not null
2326
2327 -- IF p_tax_rate_code is null or no tax_rate_code returned from rule engine.
2328 IF p_tax_rate_code IS NULL
2329 OR x_tax_rate_rec.tax_rate_id IS NULL
2330 THEN
2331 -- l_tax_rate_code is not available on tax line and rule engine
2332 -- does not return tax rate code. Hence get the Default tax rate code
2333 --
2334 -- get default tax rate code
2335 get_def_tax_rate_pvt(
2336 p_tax_class => l_tax_class,
2337 p_tax_regime_code => p_tax_regime_code,
2338 p_tax_jurisdiction_code => p_tax_jurisdiction_code,
2339 p_tax => p_tax,
2340 p_tax_date => p_tax_date,
2341 p_tax_status_code => p_tax_status_code,
2342 p_place_of_supply_type_code => p_place_of_supply_type_code,
2343 p_structure_index => p_structure_index,
2344 p_multiple_jurisdictions_flag => p_multiple_jurisdictions_flag,
2345 x_tax_rate_rec => x_tax_rate_rec,
2346 x_return_status => x_return_status
2347 );
2348
2349 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2350 IF (g_level_statement >= g_current_runtime_level ) THEN
2351 FND_LOG.STRING(g_level_statement,
2352 g_module_name||'.'||l_procedure_name,
2353 'Incorrect return_status after calling ' ||
2354 'ZX_TCM_TAX_RATE_PKG.get_def_tax_rate_pvt');
2355 FND_LOG.STRING(g_level_statement,
2356 g_module_name||'.'||l_procedure_name,
2357 'RETURN_STATUS = ' || x_return_status);
2358 FND_LOG.STRING(g_level_statement,
2359 g_module_name||'.'||l_procedure_name||'.END',
2360 'ZX_TCM_TAX_RATE_PKG.get_tax_rate(-)');
2361 END IF;
2362 -- lower level should have set the fnd msg stack
2363 RETURN;
2364 END IF;
2365
2366 END IF; -- p_tax_rate_code is null or no tax_rate info
2367 -- found for the tax_rate_code returned from rule engine.
2368
2369 IF x_tax_rate_rec.tax_rate_id IS NOT NULL THEN
2370 IF (g_level_statement >= g_current_runtime_level ) THEN
2371 FND_LOG.STRING(g_level_statement,
2372 g_module_name||'.'||l_procedure_name,
2373 'tax_rate_code = ' || x_tax_rate_rec.tax_rate_code);
2374 FND_LOG.STRING(g_level_statement,
2375 g_module_name||'.'||l_procedure_name,
2376 'tax_rate_id = ' || x_tax_rate_rec.tax_rate_id);
2377 FND_LOG.STRING(g_level_statement,
2378 g_module_name||'.'||l_procedure_name,
2379 'rate_type = ' || x_tax_rate_rec.rate_type_code);
2380 FND_LOG.STRING(g_level_statement,
2381 g_module_name||'.'||l_procedure_name,
2382 'percentage_rate = ' || x_tax_rate_rec.percentage_rate);
2383 FND_LOG.STRING(g_level_statement,
2384 g_module_name||'.'||l_procedure_name,
2385 'quantity_rate = ' || x_tax_rate_rec.quantity_rate);
2386 FND_LOG.STRING(g_level_statement,
2387 g_module_name||'.'||l_procedure_name,
2388 'allow_adhoc_tax_rate_flag = ' || x_tax_rate_rec.allow_adhoc_tax_rate_flag);
2389 FND_LOG.STRING(g_level_statement,
2390 g_module_name||'.'||l_procedure_name,
2391 'uom_code = ' || x_tax_rate_rec.uom_code);
2392 FND_LOG.STRING(g_level_statement,
2393 g_module_name||'.'||l_procedure_name,
2394 'offset_tax_rate_code = ' || x_tax_rate_rec.offset_tax_rate_code);
2395 END IF;
2396 END IF;
2397
2398 -- populate the cached structure ZX_TDS_UTILITIES_PKG.g_tax_rate_info_ind_by_hash
2399 -- for reference by ZX_TDS_APPLICABILITY_DETM_PKG.get_process_Results for batch calculation calls
2400 -- since this procedure only checks the existence of a rate record having the same tax class
2401 -- it should be ok to refecene this record.
2402
2403 l_tbl_index := dbms_utility.get_hash_value(
2404 x_tax_rate_rec.tax_regime_code||x_tax_rate_rec.tax||
2405 x_tax_rate_rec.tax_status_code||x_tax_rate_rec.tax_rate_code,
2406 1,
2407 8192);
2408
2409 ZX_TDS_UTILITIES_PKG.g_tax_rate_info_ind_by_hash(l_tbl_index) := x_tax_rate_rec;
2410
2411 IF (g_level_procedure >= g_current_runtime_level ) THEN
2412 FND_LOG.STRING(g_level_procedure,
2413 g_module_name||'.'||l_procedure_name||'.END',
2414 'ZX_TCM_TAX_RATE_PKG.get_tax_rate(-) ');
2415 END IF;
2416
2417 EXCEPTION
2418 WHEN OTHERS THEN
2419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2420 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2421 FND_LOG.STRING(g_level_unexpected,
2422 g_module_name||'.'||l_procedure_name,
2423 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2424 FND_LOG.STRING(g_level_unexpected,
2425 g_module_name||'.'||l_procedure_name||'.END',
2426 'ZX_TCM_TAX_RATE_PKG.get_tax_rate(-) ');
2427 END IF;
2428
2429 END get_tax_rate;
2430
2431 END ZX_TCM_TAX_RATE_PKG;
2432