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