eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ZX_TDS_UTILITIES_PKG

Source


1 package body ZX_TDS_UTILITIES_PKG as
2 /* $Header: zxdiutilitiespub.pls 120.50.12020000.3 2013/03/20 06:40:52 srajapar ship $ */
3 
4 g_current_runtime_level   NUMBER;
5 g_level_statement         CONSTANT NUMBER   := FND_LOG.LEVEL_STATEMENT;
6 g_level_unexpected        CONSTANT NUMBER   := FND_LOG.LEVEL_UNEXPECTED;
7 
8 ----------------------------------------------------------------------
9 --  PUBLIC FUNCTION
10 --  get_tax_status_index
11 --
12 --  DESCRIPTION
13 --
14 --  This function returns the hash table index from global tax
15 --  status cache structure
16 --
17 
18 FUNCTION get_tax_status_index(
19             p_tax               IN   ZX_TAXES_B.TAX%TYPE,
20             p_tax_regime_code   IN   ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
21             p_tax_status_code   IN   ZX_STATUS_B.TAX_STATUS_CODE%TYPE)
22 RETURN BINARY_INTEGER IS
23 
24   l_tbl_index      BINARY_INTEGER;
25 BEGIN
26 
27   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
28 
29   IF (g_level_statement >= g_current_runtime_level ) THEN
30     FND_LOG.STRING(g_level_statement,
31                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_status_index.BEGIN',
32                    'ZX_TDS_UTILITIES_PKG: get_tax_status_index(+)');
33   END IF;
34 
35   l_tbl_index := dbms_utility.get_hash_value(
36                 p_tax_regime_code||p_tax||p_tax_status_code,
37                 1,
38                 8192);
39 
40   IF (g_level_statement >= g_current_runtime_level ) THEN
41 
42     FND_LOG.STRING(g_level_statement,
43                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_status_index.END',
44                    'ZX_TDS_UTILITIES_PKG: get_tax_status_index(-)'||
45                     'tbl index = ' || to_char(l_tbl_index));
46 
47   END IF;
48 
49   return l_tbl_index;
50 
51 EXCEPTION
52   WHEN OTHERS THEN
53     IF (g_level_unexpected >= g_current_runtime_level ) THEN
54       FND_LOG.STRING(g_level_unexpected,
55                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_status_index',
56                       sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
57     END IF;
58     RAISE;
59 END get_tax_status_index;
60 
61 -----------------------------------------------------------------------
62 --  PUBLIC PROCEDURE
63 --  get_tax_status_cache_info
64 --
65 --  DESCRIPTION
66 --  This procedure gets tax status information from global cache structure
67 --  based on the hash index of Tax, Tax regime code and Tax status code if
68 --  exists, if not, obtain the Tax status information from the database
69 --
70 PROCEDURE  get_tax_status_cache_info(
71              p_tax                 IN     ZX_TAXES_B.TAX%TYPE,
72              p_tax_regime_code     IN     ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
73              p_tax_status_code     IN     ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
74              p_tax_determine_date  IN     ZX_LINES.TAX_DETERMINE_DATE%TYPE,
75              p_status_rec             OUT NOCOPY ZX_STATUS_INFO_REC,
76              p_return_status          OUT NOCOPY VARCHAR2,
77              p_error_buffer           OUT NOCOPY VARCHAR2)
78 
79 IS
80   l_index              BINARY_INTEGER;
81 
82   CURSOR get_status_info_csr
83     (c_tax_status_code      ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
84      c_tax                  ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
85      c_tax_regime_code      ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
86      c_tax_determine_date   ZX_LINES.TAX_DETERMINE_DATE%TYPE)
87   IS
88     SELECT tax_status_id,
89            tax_status_code,
90            tax,
91            tax_regime_code,
92            effective_from,
93            effective_to,
94            Rule_Based_Rate_Flag,
95            Allow_Rate_Override_Flag,
96 --           Allow_Adhoc_Tax_Rate_Flag, -- commented out for bug 3420310
97            Allow_Exemptions_Flag,
98            Allow_Exceptions_Flag
99       FROM  ZX_SCO_STATUS_B_V
100       WHERE TAX_STATUS_CODE = c_tax_status_code      AND
101             TAX             = c_tax                  AND
102             TAX_REGIME_CODE = c_tax_regime_code      AND
103             c_tax_determine_date >= EFFECTIVE_FROM   AND
104             (c_tax_determine_date <= EFFECTIVE_TO OR
105              EFFECTIVE_TO IS NULL)
106         -- AND rownum = 1;
107         ORDER BY subscription_level_code;
108 
109 BEGIN
110 
111   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
112 
113   IF (g_level_statement >= g_current_runtime_level ) THEN
114     FND_LOG.STRING(g_level_statement,
115                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info.BEGIN',
116                    'ZX_TDS_UTILITIES_PKG: get_tax_status_cache_info(+)'||
117                    ' regime code: ' || p_tax_regime_code||
118                    ' status code: ' || p_tax_status_code||
119                    ' tax: ' || p_tax);
120   END IF;
121 
122   p_return_status := FND_API.G_RET_STS_SUCCESS;
123 
124   l_index := get_tax_status_index(p_tax,
125                                   p_tax_regime_code,
126                                   p_tax_status_code);
127   --
128   -- first check if the status info is available from the cache
129   --
130   IF g_tax_status_info_tbl.EXISTS(l_index) AND
131      p_tax_determine_date >= g_tax_status_info_tbl(l_index).effective_from AND
132      p_tax_determine_date <= NVL(g_tax_status_info_tbl(l_index).effective_to,
133                                                           p_tax_determine_date)
134   THEN
135 
136     p_status_rec := g_tax_status_info_tbl(l_index);
137 
138   ELSE
139     --
140     -- status info does not exist in cache, get it from zx_status
141     --
142     OPEN get_status_info_csr(p_tax_status_code,
143                              p_tax,
144                              p_tax_regime_code,
145                              p_tax_determine_date);
146     FETCH get_status_info_csr  INTO
147        p_status_rec.tax_status_id,
148        p_status_rec.tax_status_code,
149        p_status_rec.tax,
150        p_status_rec.tax_regime_code,
151        p_status_rec.effective_from,
152        p_status_rec.effective_to,
153        p_status_rec.Rule_Based_Rate_Flag,
154        p_status_rec.Allow_Rate_Override_Flag,
155 --       p_status_rec.Allow_Adhoc_Tax_Rate_Flag,  -- commented out for bug 3420310
156        p_status_rec.Allow_Exemptions_Flag,
157        p_status_rec.Allow_Exceptions_Flag;
158 
159     IF get_status_info_csr%NOTFOUND THEN
160       p_return_status := FND_API.G_RET_STS_ERROR;
161       p_error_buffer  := 'No data found for the specified tax status code';
162 
163       FND_MESSAGE.SET_NAME('ZX','ZX_TAX_STATUS_INFO_NOT_FOUND');
164       FND_MESSAGE.SET_TOKEN('TAX_REGIME',p_tax_regime_code);
165       FND_MESSAGE.SET_TOKEN('TAX', p_tax );   -- 8568734
166       FND_MESSAGE.SET_TOKEN('TAX_STATUS', p_tax_status_code);
167 
168       -- FND_MSG_PUB.Add;
169       ZX_API_PUB.add_msg(ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
170 
171     END IF;
172 
173     CLOSE get_status_info_csr;
174 
175     -- update the global status cache structure
176     --
177     IF p_return_status =  FND_API.G_RET_STS_SUCCESS THEN
178       g_tax_status_info_tbl(l_index) := p_status_rec;
179     END IF;
180   END IF;
181 
182   IF (g_level_statement >= g_current_runtime_level ) THEN
183     FND_LOG.STRING(g_level_statement,
184                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info',
185                    'Return_status = ' || p_return_status||
186                    ' tax_status_id = ' ||
187                     to_char(p_status_rec.tax_status_id)||
188                    ' Rule_Based_Rate_Flag = ' ||
189                     p_status_rec.Rule_Based_Rate_Flag||
190                    ' Allow_Rate_Override_Flag = ' ||
191                     p_status_rec.Allow_Rate_Override_Flag||
192                    ' Allow_Exemptions_Flag = ' ||
193                     p_status_rec.Allow_Exemptions_Flag||
194                    ' Allow_Exceptions_Flag = ' ||
195                     p_status_rec.Allow_Exceptions_Flag);
196 
197     FND_LOG.STRING(g_level_statement,
198                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info.END',
199                    'ZX_TDS_UTILITIES_PKG: get_tax_status_cache_info(-)');
200   END IF;
201 
202 EXCEPTION
203   WHEN OTHERS THEN
204     p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205     p_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
206     IF get_status_info_csr%ISOPEN THEN
207       CLOSE get_status_info_csr;
208     END IF;
209     IF (g_level_unexpected >= g_current_runtime_level ) THEN
210       FND_LOG.STRING(g_level_unexpected,
211                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info',
212                       p_error_buffer);
213     END IF;
214 
215 END get_tax_status_cache_info;
216 
217 --------------------------------------------------------------------------
218 --  PUBLIC PROCEDURE
219 --  get_tax_regime_cache_info
220 --
221 --  DESCRIPTION
222 --  This procedure gets tax regime information from global cache
223 --  structure based on tax regime code, if regime exists in it.
224 --  If regime does not exist, get tax regime information from the database
225 --------------------------------------------------------------------------
226 PROCEDURE get_regime_cache_info (
227   p_tax_regime_code     IN          zx_regimes_b.tax_regime_code%TYPE,
228   p_tax_determine_date  IN          DATE,
229   p_tax_regime_rec      OUT NOCOPY  zx_global_structures_pkg.tax_regime_rec_type,
230   p_return_status       OUT NOCOPY  VARCHAR2,
231   p_error_buffer        OUT NOCOPY  VARCHAR2) IS
232 
233   CURSOR  get_regime_info_csr IS
234    SELECT regime_precedence,
235           tax_regime_id,
236           tax_regime_code,
237           parent_regime_code,
238           country_code,
239           geography_type,
240           geography_id,
241           effective_from,
242           effective_to,
243           country_or_group_code
244      FROM ZX_REGIMES_B_V
245     WHERE tax_regime_code = p_tax_regime_code
246       AND (( effective_from <= p_tax_determine_date) AND
247            ( effective_to   >= p_tax_determine_date OR effective_to IS NULL));
248 
249  l_in_cache_flg         BOOLEAN         := FALSE;
250  l_index                NUMBER;
251 
252 BEGIN
253 
254   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
255 
256   IF (g_level_statement >= g_current_runtime_level ) THEN
257     FND_LOG.STRING(g_level_statement,
258                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_regime_cache_info.BEGIN',
259                    'ZX_TDS_UTILITIES_PKG.get_regime_cache_info(+)'||
260                    ' regime code: ' || p_tax_regime_code);
261   END IF;
262 
263   p_return_status := FND_API.G_RET_STS_SUCCESS;
264 
265   -- check if this tax regime exists in the cache structure
266   --
267   l_index := ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.FIRST;
268   WHILE l_index IS NOT NULL LOOP
269 
270     IF(p_tax_regime_code =
271         ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_index).tax_regime_code AND
272        (p_tax_determine_date >=
273          ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_index).effective_from AND
274         (p_tax_determine_date <=
275           ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_index).effective_to  OR
276          ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_index).effective_to IS NULL)))
277     THEN
278 
279       p_tax_regime_rec := ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_index);
280 
281       l_in_cache_flg := TRUE;
282 
283       EXIT;
284     END IF;
285     l_index := ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl.NEXT(l_index);
286   END LOOP;
287 
288   IF NOT l_in_cache_flg  THEN
289 
290     OPEN  get_regime_info_csr;
291     FETCH get_regime_info_csr INTO
292       p_tax_regime_rec.tax_regime_precedence,
293       p_tax_regime_rec.tax_regime_id,
294       p_tax_regime_rec.tax_regime_code,
295       p_tax_regime_rec.parent_regime_code,
296       p_tax_regime_rec.country_code,
297       p_tax_regime_rec.geography_type,
298       p_tax_regime_rec.geography_id,
299       p_tax_regime_rec.effective_from,
300       p_tax_regime_rec.effective_to,
301       p_tax_regime_rec.country_or_group_code;
302 
303     IF get_regime_info_csr%NOTFOUND THEN
304 
305       p_return_status := FND_API.G_RET_STS_ERROR;
306       p_error_buffer  := 'No data found for the specified tax regime code';
307 
308       FND_MESSAGE.SET_NAME('ZX','ZX_TAX_REGIME_INFO_NOT_FOUND');
309       FND_MESSAGE.SET_TOKEN('TAX_REGIME',p_tax_regime_code);
310 
311       -- FND_MSG_PUB.Add;
312       ZX_API_PUB.add_msg(ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
313 
314     ELSE
315        -- populate the global cache structure for regime
316        --
317        ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(
318                        p_tax_regime_rec.tax_regime_id) := p_tax_regime_rec;
319 
320     END IF;
321 
322     CLOSE get_regime_info_csr;
323 
324   END IF;
325 
326   IF (g_level_statement >= g_current_runtime_level ) THEN
327 
328     FND_LOG.STRING(g_level_statement,
329                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_regime_cache_info.END',
330                    'ZX_TDS_UTILITIES_PKG.get_regime_cache_info(-)'||
331                    ' tax_regime_id = ' ||
332                     to_char(p_tax_regime_rec.tax_regime_id)||
333                     ' RETURN_STATUS = ' || p_return_status);
334   END IF;
335 
336 EXCEPTION
337   WHEN OTHERS THEN
338     IF get_regime_info_csr%ISOPEN THEN
339       CLOSE get_regime_info_csr;
340     END IF;
341     p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342     p_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
343     IF (g_level_unexpected >= g_current_runtime_level ) THEN
344       FND_LOG.STRING(g_level_unexpected,
345                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_regime_cache_info',
346                       p_error_buffer);
347       FND_LOG.STRING(g_level_unexpected,
348                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_regime_cache_info.END',
349                      'ZX_TDS_UTILITIES_PKG.get_regime_cache_info(-)');
350     END IF;
351 
352 END get_regime_cache_info;
353 
354 -------------------------------------------------------------------------
355 --  PUBLIC PROCEDURE
356 --  get_tax_cache_info
357 --
358 --  DESCRIPTION
359 --  This procedure gets tax information from global cache structure
360 --  based on tax regime code and tax, if tax exists in it.
361 --  If tax does not exist, get the tax information from database
362 -------------------------------------------------------------------------
363 PROCEDURE get_tax_cache_info (
364   p_tax_regime_code     IN          zx_regimes_b.tax_regime_code%TYPE,
365   p_tax                 IN          zx_taxes_b.tax%TYPE,
366   p_tax_determine_date  IN          DATE,
367   x_tax_rec             OUT NOCOPY  zx_tax_info_cache_rec,
368   p_return_status       OUT NOCOPY  VARCHAR2,
369   p_error_buffer        OUT NOCOPY  VARCHAR2) IS
370 
371   CURSOR  get_tax_info_csr IS
372    SELECT tax_id,
373           tax,
374           tax_regime_code,
375           tax_type_code,
376           tax_precision,
377           minimum_accountable_unit,
378           Rounding_Rule_Code,
379           Tax_Status_Rule_Flag,
380           Tax_Rate_Rule_Flag,
381           Place_Of_Supply_Rule_Flag,
382           Applicability_Rule_Flag,
383           Tax_Calc_Rule_Flag,
384           Taxable_Basis_Rule_Flag,
385           def_tax_calc_formula,
386           def_taxable_basis_formula,
387           Reporting_Only_Flag,
388           tax_currency_code,
389           Def_Place_Of_Supply_Type_Code,
390           Def_Registr_Party_Type_Code,
391           Registration_Type_Rule_Flag,
392           Direct_Rate_Rule_Flag,
393           Def_Inclusive_Tax_Flag,
394           effective_from,
395           effective_to,
396           compounding_precedence,
397           Has_Other_Jurisdictions_Flag,
398           Live_For_Processing_Flag,
399           Regn_Num_Same_As_Le_Flag,
400           applied_amt_handling_flag,
401           exchange_rate_type,
402           applicable_by_default_flag,
403           record_type_code,
404           tax_exmpt_cr_method_code,
405           tax_exmpt_source_tax,
406           legal_reporting_status_def_val,
407           def_rec_settlement_option_code,
408           zone_geography_type,
409           override_geography_type,
410           allow_rounding_override_flag,
411           tax_account_source_tax
412      FROM ZX_SCO_TAXES_B_V
413     WHERE tax = p_tax
414       AND tax_regime_code = p_tax_regime_code
415       AND (effective_from <= p_tax_determine_date AND
416             (effective_to >= p_tax_determine_date OR effective_to IS NULL))
417       AND live_for_processing_flag = 'Y'
418       AND (live_for_applicability_flag = 'Y' OR
419            (LIVE_FOR_APPLICABILITY_FLAG = 'N' AND
420             tax ='LOCATION' AND record_type_code = 'MIGRATED'
421            )
422           )
423       -- AND rownum = 1;
424     ORDER BY subscription_level_code;
425 
426 
427  l_in_cache_flg         BOOLEAN         := FALSE;
428  l_index                NUMBER;
429 
430 BEGIN
431 
432   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
433 
434   IF (g_level_statement >= g_current_runtime_level ) THEN
435     FND_LOG.STRING(g_level_statement,
436                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_cache_info.BEGIN',
437                    'ZX_TDS_UTILITIES_PKG.get_tax_cache_info(+)'||
438                    ' regime_code: ' || p_tax_regime_code||
439                    ' tax: ' || p_tax);
440   END IF;
441 
442   p_return_status := FND_API.G_RET_STS_SUCCESS;
443 
444   --
445   -- first check if tax exists in tax cache structure
446   --
447   l_index := g_tax_rec_tbl.LAST;
448   WHILE l_index IS NOT NULL LOOP
449 
450     IF (p_tax = g_tax_rec_tbl(l_index).tax AND
451         p_tax_regime_code = g_tax_rec_tbl(l_index).tax_regime_code)
452     THEN
453 
454       x_tax_rec := g_tax_rec_tbl(l_index);
455       l_in_cache_flg := TRUE;
456       EXIT;
457     END IF;
458     l_index := g_tax_rec_tbl.PRIOR(l_index);
459   END LOOP;
460 
461   -- if tax does not exist in cache, get tax info from zx_taxes_b
462   --
463   IF NOT l_in_cache_flg  THEN
464     --
465     -- fetching tax_info from zx_taxes_b
466     --
467     OPEN  get_tax_info_csr;
468     FETCH get_tax_info_csr INTO x_tax_rec;
469 
470     IF get_tax_info_csr%NOTFOUND THEN
471       p_return_status := FND_API.G_RET_STS_ERROR;
472       p_error_buffer  := 'No data found for the specified tax_regime_code and tax';
473 
474       FND_MESSAGE.SET_NAME('ZX','ZX_TAX_INFO_NOT_FOUND');
475       FND_MESSAGE.SET_TOKEN('TAX_REGIME',p_tax_regime_code);
476       FND_MESSAGE.SET_TOKEN('TAX',p_tax);
477 
478       -- FND_MSG_PUB.Add;
479       ZX_API_PUB.add_msg(ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
480 
481     ELSE
482 
483       -- populate tax cache
484       --
485       g_tax_rec_tbl(x_tax_rec.tax_id) :=  x_tax_rec;
486     END IF;
487 
488     CLOSE get_tax_info_csr;
489 
490   END IF;   -- tax not in cache
491 
492   IF (g_level_statement >= g_current_runtime_level ) THEN
493 
494     FND_LOG.STRING(g_level_statement,
495                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_cache_info.END',
496                    'ZX_TDS_UTILITIES_PKG.get_tax_cache_info(-)'||
497                    'return_status = ' || p_return_status);
498   END IF;
499 
500 EXCEPTION
501   WHEN OTHERS THEN
502     IF get_tax_info_csr%ISOPEN THEN
503         CLOSE get_tax_info_csr;
504      END IF;
505     p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506     p_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
507     IF (g_level_unexpected >= g_current_runtime_level ) THEN
508       FND_LOG.STRING(g_level_unexpected,
509                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_cache_info',
510                       p_error_buffer);
511       FND_LOG.STRING(g_level_unexpected,
512                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_cache_info.END',
513                      'ZX_TDS_UTILITIES_PKG.get_tax_cache_info(-)');
514     END IF;
515 
516 END get_tax_cache_info;
517 
518 -------------------------------------------------------------------------
519 --  PUBLIC PROCEDURE
520 --  get_jurisdiction_cache_info
521 --
522 --  DESCRIPTION
523 --  This procedure gets jurisdiction information from global cache structure
524 --  based on tax regime code ,tax and tax jurisdiction code, if tax exists in it.
525 --
526 -------------------------------------------------------------------------
527 PROCEDURE get_jurisdiction_cache_info (
528   p_tax_regime_code     IN          zx_regimes_b.tax_regime_code%TYPE,
529   p_tax                 IN          zx_taxes_b.tax%TYPE,
530   p_tax_jurisdiction_code IN        zx_jurisdictions_b.tax_jurisdiction_code%TYPE,
531   p_tax_determine_date  IN          DATE,
532   x_jurisdiction_rec    OUT NOCOPY  zx_jur_info_cache_rec_type,
533   p_return_status       OUT NOCOPY  VARCHAR2,
534   p_error_buffer        OUT NOCOPY  VARCHAR2) IS
535 
536   CURSOR  get_jur_info_csr IS
537    SELECT tax_jurisdiction_code,
538           tax_jurisdiction_id,
539           effective_from,
540           effective_to,
541           tax_regime_code,
542           tax
543      FROM ZX_JURISDICTIONS_B
544     WHERE tax_regime_code = p_tax_regime_code
545       AND tax = p_tax
546       AND tax_jurisdiction_code = p_tax_jurisdiction_code
547       AND (effective_from <= p_tax_determine_date AND
548              (effective_to  >= p_tax_determine_date OR effective_to IS NULL));
549 
550  l_in_cache_flg         BOOLEAN         := FALSE;
551  l_index                NUMBER;
552 
553 BEGIN
554 
555   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
556 
557   IF (g_level_statement >= g_current_runtime_level ) THEN
558     FND_LOG.STRING(g_level_statement,
559                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info.BEGIN',
560                    'ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info(+)'||
561                    ' regime_code = ' || p_tax_regime_code||
562                    ' tax = ' || p_tax||
563                    ' jurisdiciton_code = ' || p_tax_jurisdiction_code);
564 
565   END IF;
566 
567   p_return_status := FND_API.G_RET_STS_SUCCESS;
568 
569   --
570   -- first check if tax exists in tax cache structure
571   --
572   l_index := g_jur_info_tbl.FIRST;
573   WHILE l_index IS NOT NULL LOOP
574 
575     IF (p_tax = g_jur_info_tbl(l_index).tax AND
576         p_tax_regime_code = g_jur_info_tbl(l_index).tax_regime_code AND
577         p_tax_jurisdiction_code = g_jur_info_tbl(l_index).tax_jurisdiction_code)
578     THEN
579 
580       x_jurisdiction_rec := g_jur_info_tbl(l_index);
581       l_in_cache_flg := TRUE;
582       EXIT;
583     END IF;
584     l_index := g_jur_info_tbl.NEXT(l_index);
585   END LOOP;
586 
587   -- if tax does not exist in cache, get tax info from zx_taxes_b
588   --
589   IF NOT l_in_cache_flg  THEN
590     --
591     -- fetching tax_info from zx_taxes_b
592     --
593     OPEN  get_jur_info_csr;
594     FETCH get_jur_info_csr INTO
595           x_jurisdiction_rec.tax_jurisdiction_code,
596           x_jurisdiction_rec.tax_jurisdiction_id,
597           x_jurisdiction_rec.effective_from,
598           x_jurisdiction_rec.effective_to,
599           x_jurisdiction_rec.tax_regime_code,
600           x_jurisdiction_rec.tax;
601 
602     IF get_jur_info_csr%NOTFOUND THEN
603        p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604        p_error_buffer  := 'No data found for the specified tax_regime_code and tax';
605     ELSE
606 
607       -- populate tax cache
608       --
609       g_jur_info_tbl(x_jurisdiction_rec.tax_jurisdiction_id) :=  x_jurisdiction_rec;
610     END IF;
611 
612     CLOSE get_jur_info_csr;
613 
614   END IF;   -- jurisdiciton not in cache
615 
616   IF (g_level_statement >= g_current_runtime_level ) THEN
617 
618     FND_LOG.STRING(g_level_statement,
619                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_jurisdiciton_cache_info.END',
620                    'ZX_TDS_UTILITIES_PKG.get_jurisdiciton_cache_info(-)'||
621                    ' jurisdiction id = '||to_char(x_jurisdiction_rec.tax_jurisdiction_id) ||
622                    ' return_status = ' || p_return_status);
623   END IF;
624 
625 EXCEPTION
626   WHEN OTHERS THEN
627     IF get_jur_info_csr%ISOPEN THEN
628         CLOSE get_jur_info_csr;
629      END IF;
630     p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
631     p_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
632     IF (g_level_unexpected >= g_current_runtime_level ) THEN
633       FND_LOG.STRING(g_level_unexpected,
634                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info',
635                       p_error_buffer);
636       FND_LOG.STRING(g_level_unexpected,
637                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info.END',
638                      'ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info(-)');
639     END IF;
640 
641 END get_jurisdiction_cache_info;
642 
643 -----------------------------------------------------------------------
644 --  PUBLIC PROCEDURE
645 --  populate_tax_cache
646 --
647 --  DESCRIPTION
648 --  This procedure populate the tax global cache structure
649 --  g_tax_rec_tbl based on tax_id, if it does not exist.
650 -----------------------------------------------------------------------
651 PROCEDURE populate_tax_cache (
652   p_tax_id             IN           NUMBER,
653   p_return_status      OUT NOCOPY   VARCHAR2,
654   p_error_buffer       OUT NOCOPY   VARCHAR2) IS
655 
656   CURSOR  get_tax_info_csr IS
657    SELECT tax_id,
658           tax,
659           tax_regime_code,
660           tax_type_code,
661           tax_precision,
662           minimum_accountable_unit,
663           Rounding_Rule_Code,
664           Tax_Status_Rule_Flag,
665           Tax_Rate_Rule_Flag,
666           Place_Of_Supply_Rule_Flag,
667           Applicability_Rule_Flag,
668           Tax_Calc_Rule_Flag,
669           Taxable_Basis_Rule_Flag,
670           def_tax_calc_formula,
671           def_taxable_basis_formula,
672           Reporting_Only_Flag,
673           tax_currency_code,
674           Def_Place_Of_Supply_Type_Code,
675           Def_Registr_Party_Type_Code,
676           Registration_Type_Rule_Flag,
677           Direct_Rate_Rule_Flag,
678           Def_Inclusive_Tax_Flag,
679           effective_from,
680           effective_to,
681           compounding_precedence,
682           Has_Other_Jurisdictions_Flag,
683           Live_For_Processing_Flag,
684           Regn_Num_Same_As_Le_Flag,
685           applied_amt_handling_flag,
686           exchange_rate_type,
687           applicable_by_default_flag,
688           record_type_code,
689           tax_exmpt_cr_method_code,
690           tax_exmpt_source_tax,
691           legal_reporting_status_def_val,
692           def_rec_settlement_option_code,
693           zone_geography_type,
694           override_geography_type,
695           allow_rounding_override_flag,
696           tax_account_source_tax
697      FROM ZX_TAXES_B
698     WHERE tax_id = p_tax_id;
699 
700 BEGIN
701 
702   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
703 
704   IF (g_level_statement >= g_current_runtime_level ) THEN
705     FND_LOG.STRING(g_level_statement,
706                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_tax_cache.BEGIN',
707                    'ZX_TDS_UTILITIES_PKG.populate_tax_cache(+)'||
708                    'tax_id := ' || to_char(p_tax_id));
709   END IF;
710 
711   p_return_status := FND_API.G_RET_STS_SUCCESS;
712 
713   IF p_tax_id IS NULL THEN
714     IF (g_level_statement >= g_current_runtime_level ) THEN
715       FND_LOG.STRING(g_level_statement,
716                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_tax_cache',
717                      'tax_id cannot be null');
718     END IF;
719     p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;     -- bug 4893261
720     p_error_buffer := 'tax_id cannot be null';
721     RETURN;
722   END IF;
723 
724   -- if tax does not exist in the cache structure, get tax info from database
725   --
726   IF NOT g_tax_rec_tbl.EXISTS(p_tax_id)  THEN
727 
728     OPEN get_tax_info_csr;
729     FETCH get_tax_info_csr INTO g_tax_rec_tbl(p_tax_id);
730 
731     IF get_tax_info_csr%NOTFOUND THEN
732        p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;     -- bug 4893261
733        p_error_buffer  := 'No data found for the specified tax_id';
734     END IF;
735 
736     CLOSE get_tax_info_csr;
737   END IF;       -- tax_id not exist in g_tax_rec_tbl
738 
739   IF (g_level_statement >= g_current_runtime_level ) THEN
740 
741     FND_LOG.STRING(g_level_statement,
742                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_tax_cache.END',
743                    'ZX_TDS_UTILITIES_PKG.populate_tax_cache(-)'||
744                    ' RETURN_STATUS = ' || p_return_status||
745                    ' error buffer: '||p_error_buffer);
746   END IF;
747 
748  EXCEPTION
749    WHEN OTHERS THEN
750      IF get_tax_info_csr%ISOPEN THEN
751         CLOSE get_tax_info_csr;
752      END IF;
753      p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754      p_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
755      IF (g_level_unexpected >= g_current_runtime_level ) THEN
756        FND_LOG.STRING(g_level_unexpected,
757                       'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_tax_cache',
758                        p_error_buffer);
759        FND_LOG.STRING(g_level_unexpected,
760                       'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_tax_cache.END',
761                       'ZX_TDS_UTILITIES_PKG.populate_tax_cache(-)');
762      END IF;
763 
764 END populate_tax_cache;
765 
766 -----------------------------------------------------------------------
767 --  PUBLIC PROCEDURE
768 --  populate_currency_cache
769 --
770 --  DESCRIPTION
771 --  This procedure populates the currency cache structure
772 --  g_currency_rec_tbl based on ledger_id. If this ledger_id already
773 --  exists in the cache structure, the population process will be skipped.
774 -----------------------------------------------------------------------
775 PROCEDURE populate_currency_cache (
776  p_ledger_id              IN          gl_sets_of_books.set_of_books_id%TYPE,
777  p_return_status          OUT NOCOPY  VARCHAR2,
778  p_error_buffer           OUT NOCOPY  VARCHAR2) IS
779 
780  CURSOR  get_currency_info_csr IS
781   SELECT sob.set_of_books_id,
782          cur.currency_code,
783          NVL(cur.minimum_accountable_unit, power(10, (-1 * precision))),
784          precision
785     FROM fnd_currencies cur, gl_sets_of_books sob
786    WHERE sob.set_of_books_id = p_ledger_id
787      AND cur.currency_code = sob.currency_code;
788 
789 
790 BEGIN
791   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
792 
793   IF (g_level_statement >= g_current_runtime_level ) THEN
794     FND_LOG.STRING(g_level_statement,
795                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_currency_cache.BEGIN',
796                    'ZX_TDS_UTILITIES_PKG.populate_currency_cache(+)'||
797                    'ledger_id := ' || to_char(p_ledger_id));
798   END IF;
799 
800   p_return_status := FND_API.G_RET_STS_SUCCESS;
801 
802   IF p_ledger_id IS NULL THEN
803     IF (g_level_statement >= g_current_runtime_level ) THEN
804       FND_LOG.STRING(g_level_statement,
805                      'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_currency_cache',
806                      'ledger_id cannot be null');
807     END IF;
808     p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;     -- bug 4893261
809     p_error_buffer := 'ledger_id cannot be null';
810     RETURN;
811   END IF;
812 
813   -- if ledger_id does not exist in the cache structure,
814   -- get currency info from database
815   --
816   IF NOT g_currency_rec_tbl.EXISTS(p_ledger_id)  THEN
817 
818     OPEN  get_currency_info_csr;
819     FETCH get_currency_info_csr INTO
820             g_currency_rec_tbl(p_ledger_id).ledger_id,
821             g_currency_rec_tbl(p_ledger_id).currency_code,
822             g_currency_rec_tbl(p_ledger_id).minimum_accountable_unit,
823             g_currency_rec_tbl(p_ledger_id).precision;
824 
825     IF get_currency_info_csr%NOTFOUND THEN
826        p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
827        p_error_buffer  := 'No data found for the specified ledger_id';
828     END IF;
829 
830     CLOSE get_currency_info_csr;
831   END IF;
832 
833   IF (g_level_statement >= g_current_runtime_level ) THEN
834 
835     FND_LOG.STRING(g_level_statement,
836                    'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_currency_cache.END',
837                    'ZX_TDS_UTILITIES_PKG.populate_currency_cache(-)'||
838                    ' RETURN_STATUS = ' || p_return_status||
839                    ' error buffer: '||p_error_buffer);
840   END IF;
841 
842  EXCEPTION
843    WHEN OTHERS THEN
844      IF get_currency_info_csr%ISOPEN THEN
845         CLOSE get_currency_info_csr;
846      END IF;
847      p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
848      p_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
849      IF (g_level_unexpected >= g_current_runtime_level ) THEN
850        FND_LOG.STRING(g_level_unexpected,
851                       'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_currency_cache',
852                        p_error_buffer);
853        FND_LOG.STRING(g_level_unexpected,
854                       'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.populate_currency_cache.END',
855                       'ZX_TDS_UTILITIES_PKG.populate_currency_cache(-)');
856      END IF;
857 
858 END populate_currency_cache;
859 
860 ----------------------------------------------------------------------
861 --  PROCEDURE
862 --  get_tax_rate_info
863 --
864 --  DESCRIPTION
865 --
866 --  This procedure get tax_rate_id from zx_rates_b with given tax_regime_code,
867 --  tax, tax_status_code, tax_rate_code and tax_determine_date.
868 --
869 --  IN
870 --                 p_tax_regime_code       VARCHAR2
871 --                 p_tax                   VARCHAR2
872 --                 p_tax_status_code       VARCHAR2
873 --                 p_tax_rate_code         VARCHAR2
874 --                 p_tax_determine_date    DATE
875 --                 p_tax_class             VARCHAR2
876 --  OUT NOCOPY
877 --                  p_tax_rate_rec         zx_rate_info_rec_type
878 --                 p_return_status         VARCHAR2
879 --                 p_error_buffer          VARCHAR2
880 --
881 PROCEDURE  get_tax_rate_info (
882  p_tax_regime_code        IN          VARCHAR2,
883  p_tax                    IN          VARCHAR2,
884  p_tax_jurisdiction_code  IN          zx_jurisdictions_b.tax_jurisdiction_code%TYPE,
885  p_tax_status_code        IN          VARCHAR2,
886  p_tax_rate_code          IN          VARCHAR2,
887  p_tax_determine_date     IN          DATE,
888  p_tax_class              IN          VARCHAR2,
889  p_tax_rate_rec           OUT NOCOPY  zx_rate_info_rec_type,
890  p_return_status          OUT NOCOPY  VARCHAR2,
891  p_error_buffer           OUT NOCOPY  VARCHAR2) IS
892 
893  l_tax_jurisdiction_code zx_jurisdictions_b.tax_jurisdiction_code%TYPE; -- for  bug#5569426
894  CURSOR  fetch_tax_rate_info_csr_jur IS
895   SELECT tax_regime_code,
896          tax,
897          tax_status_code,
898          tax_rate_code,
899          tax_rate_id,
900          effective_from,
901          effective_to,
902          rate_type_code,
903          percentage_rate,
904          quantity_rate,
905          Allow_Adhoc_Tax_Rate_Flag,
906          uom_code,
907          tax_jurisdiction_code,
908          offset_tax,
909          offset_status_code,
910          offset_tax_rate_code,
911          allow_exemptions_flag,
912          allow_exceptions_flag,
913          NULL     tax_jurisdiction_id,
914          def_rec_settlement_option_code,
915          taxable_basis_formula_code,
916          adj_for_adhoc_amt_code,
917          inclusive_tax_flag,
918          tax_class
919    FROM  ZX_SCO_RATES_B_V
920   WHERE  tax_regime_code = p_tax_regime_code
921     AND  tax = p_tax
922     AND  tax_status_code = p_tax_status_code
923     AND  active_flag     = 'Y'
924     AND  (tax_jurisdiction_code = p_tax_jurisdiction_code )
925     AND  tax_rate_code = p_tax_rate_code
926     AND  (tax_class = p_tax_class or tax_class IS NULL)
927     AND  ( p_tax_determine_date >= effective_from AND
928           (p_tax_determine_date <= effective_to OR effective_to IS NULL))
929     ORDER BY tax_class NULLS LAST, subscription_level_code;
930 
931    CURSOR  fetch_tax_rate_info_csr_no_jur IS
932   SELECT tax_regime_code,
933          tax,
934          tax_status_code,
935          tax_rate_code,
936          tax_rate_id,
937          effective_from,
938          effective_to,
939          rate_type_code,
940          percentage_rate,
941          quantity_rate,
942          Allow_Adhoc_Tax_Rate_Flag,
943          uom_code,
944          tax_jurisdiction_code,
945          offset_tax,
946          offset_status_code,
947          offset_tax_rate_code,
948          allow_exemptions_flag,
949          allow_exceptions_flag,
950          NULL     tax_jurisdiction_id,
951          def_rec_settlement_option_code,
952          taxable_basis_formula_code,
953          adj_for_adhoc_amt_code,
954          inclusive_tax_flag,
955          tax_class
956    FROM  ZX_SCO_RATES_B_V
957   WHERE  tax_regime_code = p_tax_regime_code
958     AND  tax = p_tax
959     AND  tax_status_code = p_tax_status_code
960     AND  active_flag     = 'Y'
961     AND  (tax_jurisdiction_code is NULL)
962     AND  tax_rate_code = p_tax_rate_code
963     AND  (tax_class = p_tax_class or tax_class IS NULL)
964     AND  ( p_tax_determine_date >= effective_from AND
965           (p_tax_determine_date <= effective_to OR effective_to IS NULL))
966     ORDER BY tax_class NULLS LAST, subscription_level_code;
967 
968 BEGIN
969   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
970 
971   p_return_status := FND_API.G_RET_STS_SUCCESS;
972 
973   IF (g_level_statement >= g_current_runtime_level ) THEN
974     FND_LOG.STRING(g_level_statement,
975                   'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info.BEGIN',
976                   'ZX_TDS_UTILITIES_PKG.get_tax_rate_info(+)'||
977                   ' tax_regime_code = ' || p_tax_regime_code||
978                   ' tax = ' || p_tax||
979                   ' tax_status_code = ' || p_tax_status_code||
980                   ' tax_rate_code = ' || p_tax_rate_code||
981                   ' trax_class = ' || p_tax_class||
982                   ' tax determine date = ' || p_tax_determine_date);
983 
984   END IF;
985 
986   --start bug#5569426
987   IF  p_tax_jurisdiction_code is NULL  then
988      l_tax_jurisdiction_code :='NULL';
989   ELSE
990      l_tax_jurisdiction_code := p_tax_jurisdiction_code;
991   END IF;
992   --end bug#5569426
993 
994   IF p_tax_jurisdiction_code is NOT NULL then
995      OPEN  fetch_tax_rate_info_csr_JUR;
996      FETCH fetch_tax_rate_info_csr_JUR INTO  p_tax_rate_rec;
997 
998      IF fetch_tax_rate_info_csr_JUR%NOTFOUND THEN
999 
1000         OPEN  fetch_tax_rate_info_csr_no_JUR;
1001         FETCH fetch_tax_rate_info_csr_no_jur INTO  p_tax_rate_rec;
1002 
1003         IF fetch_tax_rate_info_csr_no_JUR%NOTFOUND THEN
1004              p_return_status := FND_API.G_RET_STS_ERROR;
1005              p_error_buffer := 'No tax_rate_id found for the specified ' ||
1006                                'tax_regime_code, tax, tax_status_code and tax_rate_code';
1007 
1008               FND_MESSAGE.SET_NAME('ZX','ZX_TAX_RATE_INFO_NOT_FOUND');
1009               FND_MESSAGE.SET_TOKEN('TAX_REGIME',p_tax_regime_code);
1010               FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax);
1011               FND_MESSAGE.SET_TOKEN('TAX_STATUS',p_tax_status_code);
1012               FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE',p_tax_rate_code);
1013         FND_MESSAGE.SET_TOKEN('TAX_JURISDICTION_CODE',l_tax_jurisdiction_code); --for bug#5569426
1014               -- FND_MSG_PUB.Add;
1015               ZX_API_PUB.add_msg(ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
1016          END IF;
1017          close fetch_tax_rate_info_csr_no_JUR;
1018 
1019      END IF;
1020 
1021      CLOSE fetch_tax_rate_info_csr_JUR;
1022 
1023   ELSE
1024 
1025        OPEN  fetch_tax_rate_info_csr_no_JUR;
1026        FETCH fetch_tax_rate_info_csr_no_jur INTO  p_tax_rate_rec;
1027 
1028        IF fetch_tax_rate_info_csr_no_JUR%NOTFOUND THEN
1029             p_return_status := FND_API.G_RET_STS_ERROR;
1030             p_error_buffer := 'No tax_rate_id found for the specified ' ||
1031                               'tax_regime_code, tax, tax_status_code and tax_rate_code';
1032 
1033              FND_MESSAGE.SET_NAME('ZX','ZX_TAX_RATE_INFO_NOT_FOUND');
1034              FND_MESSAGE.SET_TOKEN('TAX_REGIME',p_tax_regime_code);
1035              FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax);
1036              FND_MESSAGE.SET_TOKEN('TAX_STATUS',p_tax_status_code);
1037              FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE',p_tax_rate_code);
1038              FND_MESSAGE.SET_TOKEN('TAX_JURISDICTION_CODE',l_tax_jurisdiction_code);   --for bug#5569426
1039              -- FND_MSG_PUB.Add;
1040              ZX_API_PUB.add_msg(ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
1041         END IF;
1042         close fetch_tax_rate_info_csr_no_JUR;
1043 
1044   END IF;
1045 
1046   IF (g_level_statement >= g_current_runtime_level ) THEN
1047 
1048     FND_LOG.STRING(g_level_statement,
1049                   'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info.END',
1050                   'ZX_TDS_UTILITIES_PKG.get_tax_rate_info(-)'||
1051                   ' tax rate id: '||to_char(p_tax_rate_rec.tax_rate_id)||
1052                   ' RETURN_STATUS = ' || p_return_status||
1053                   ' error buffer: '||p_error_buffer);
1054   END IF;
1055 
1056 EXCEPTION
1057   WHEN OTHERS THEN
1058     p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1059     IF (g_level_unexpected >= g_current_runtime_level ) THEN
1060       FND_LOG.STRING(g_level_unexpected,
1061                     'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info',
1062                      sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1063       FND_LOG.STRING(g_level_unexpected,
1064                     'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info.END',
1065                     'ZX_TDS_UTILITIES_PKG.get_tax_rate_info(-)');
1066     END IF;
1067 
1068 END get_tax_rate_info;
1069 
1070 ----------------------------------------------------------------------
1071 --  FUNCTION
1072 --   get_tax_index
1073 --
1074 --  DESCRIPTION
1075 --
1076 --  This function check if a tax line is allicable in the current document.
1077 --  If it is applicable, return the line index in p_detail_tax_line_tbl.
1078 --
1079 --  IN             p_taxregime_code
1080 --                 p_tax
1081 --                 p_trx_line_id
1082 --                 p_trx_level_type
1083 --                 l_begin_index
1084 --                 l_begin_index
1085 --  OUT NOCOPY     x_return_status
1086 --
1087 FUNCTION get_tax_index (
1088  p_tax_regime_code        IN          zx_regimes_b.tax_regime_code%TYPE,
1089  p_tax                    IN          zx_taxes_b.tax%TYPE,
1090  p_trx_line_id            IN          NUMBER,
1091  p_trx_level_type         IN          VARCHAR2,
1092  p_begin_index            IN          BINARY_INTEGER,
1093  p_end_index              IN          BINARY_INTEGER,
1094  x_return_status          OUT NOCOPY  VARCHAR2)  RETURN NUMBER IS
1095 
1096 BEGIN
1097 
1098   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1099   x_return_status :=  FND_API.G_RET_STS_SUCCESS;
1100 
1101   IF (g_level_statement >= g_current_runtime_level ) THEN
1102     FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_index.BEGIN',
1103                   'ZX_TDS_UTILITIES_PKG.get_tax_index(+)'||
1104                   ' tax_regime_code : ' || p_tax_regime_code||
1105                   ' tax : ' || p_tax);
1106   END IF;
1107 
1108   -- Return NULL if p_begin_index IS NULL
1109   --
1110   IF (p_begin_index IS NULL) THEN
1111     IF (g_level_statement >= g_current_runtime_level ) THEN
1112       FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_index',
1113                     'Warning: p_begin_index is NULL');
1114       FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_index.END',
1115                     'ZX_TDS_UTILITIES_PKG.get_tax_index(-)');
1116     END IF;
1117     RETURN NULL;
1118   END IF;
1119 
1120   FOR i IN NVL(p_begin_index, 0) .. NVL(p_end_index, -1) LOOP
1121 
1122     IF(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_line_id =
1123                                                               p_trx_line_id  AND
1124        ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).trx_level_type =
1125                                                            p_trx_level_type  AND
1126        ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_regime_code =
1127                                                            p_tax_regime_code AND
1128        ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax = p_tax )
1129     THEN
1130       IF (g_level_statement >= g_current_runtime_level ) THEN
1131 
1132         FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_index.END',
1133                       'ZX_TDS_UTILITIES_PKG.get_tax_index(-) tax found in cache');
1134       END IF;
1135       RETURN i;
1136     END IF;
1137   END LOOP;
1138 
1139   -- Return NULL if tax does not exist
1140   --
1141   IF (g_level_statement >= g_current_runtime_level ) THEN
1142 
1143     FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_index.END',
1144                   'ZX_TDS_UTILITIES_PKG.get_tax_index(-) tax does not exist in cache');
1145   END IF;
1146 
1147   RETURN NULL;
1148 
1149 EXCEPTION
1150   WHEN OTHERS THEN
1151     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1152     IF (g_level_unexpected >= g_current_runtime_level ) THEN
1153       FND_LOG.STRING(g_level_unexpected, 'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_index',
1154                      sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1155       FND_LOG.STRING(g_level_unexpected, 'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_index.END',
1156                     'get_tax_index(-)');
1157     END IF;
1158 
1159 END get_tax_index;
1160 
1161 ----------------------------------------------------------------------
1162 --  PROCEDURE
1163 --  get_tax_rate_info
1164 --
1165 --  DESCRIPTION
1166 --
1167 --  This procedure get tax_rate_id from zx_rates_b with given tax_rate_id
1168 --
1169 --  IN
1170 --                 p_tax_id                NUMBER
1171 --  OUT NOCOPY
1172 --                 p_tax_rate_rec         zx_rate_info_rec_type
1173 --                 p_return_status         VARCHAR2
1174 --                 p_error_buffer          VARCHAR2
1175 --  HISTORY
1176 --
1177 --  Apr-05-2005  Ling Zhang  Created for bug fix 4277780
1178 --
1179 
1180 PROCEDURE  get_tax_rate_info (
1181  p_tax_rate_id            IN          NUMBER,
1182  p_tax_rate_rec           OUT NOCOPY  zx_rate_info_rec_type,
1183  p_return_status          OUT NOCOPY  VARCHAR2,
1184  p_error_buffer           OUT NOCOPY  VARCHAR2) IS
1185 
1186  CURSOR  fetch_tax_rate_info_csr IS
1187   SELECT tax_regime_code,
1188          tax,
1189          tax_status_code,
1190          tax_rate_code,
1191          tax_rate_id,
1192          effective_from,
1193          effective_to,
1194          rate_type_code,
1195          percentage_rate,
1196          quantity_rate,
1197          Allow_Adhoc_Tax_Rate_Flag,
1198          uom_code,
1199          tax_jurisdiction_code,
1200          offset_tax,
1201          offset_status_code,
1202          offset_tax_rate_code,
1203          allow_exemptions_flag,
1204          allow_exceptions_flag,
1205          NULL     tax_jurisdiction_id,
1206          def_rec_settlement_option_code,
1207          taxable_basis_formula_code,
1208          adj_for_adhoc_amt_code,
1209          inclusive_tax_flag,
1210          tax_class
1211    -- FROM  ZX_SCO_RATES -- Bug#5395227
1212       FROM  ZX_RATES_B
1213   WHERE  tax_rate_id = p_tax_rate_id
1214   AND    active_flag = 'Y';
1215 
1216   l_tbl_index BINARY_INTEGER;
1217 
1218 BEGIN
1219   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1220 
1221   p_return_status := FND_API.G_RET_STS_SUCCESS;
1222 
1223   IF (g_level_statement >= g_current_runtime_level ) THEN
1224     FND_LOG.STRING(g_level_statement,
1225                   'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info.BEGIN',
1226                   'ZX_TDS_UTILITIES_PKG.get_tax_rate_info(+)'||
1227                   'tax_rate_id = ' || p_tax_rate_id);
1228   END IF;
1229 
1230  IF g_tax_rate_info_tbl.exists(p_tax_rate_id) then
1231 
1232     IF (g_level_statement >= g_current_runtime_level ) THEN
1233 
1234        FND_LOG.STRING(g_level_statement,
1235                   'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info',
1236                   'Found rate info in cache. ');
1237     END IF;
1238 
1239     p_tax_rate_rec := g_tax_rate_info_tbl(p_tax_rate_id);
1240 
1241  ELSE
1242 
1243       OPEN  fetch_tax_rate_info_csr;
1244       FETCH fetch_tax_rate_info_csr INTO  p_tax_rate_rec;
1245 
1246       g_tax_rate_info_tbl(p_tax_rate_id) := p_tax_rate_rec;
1247 
1248       l_tbl_index := dbms_utility.get_hash_value(
1249                 p_tax_rate_rec.tax_regime_code||p_tax_rate_rec.tax||
1250                 p_tax_rate_rec.tax_status_code||p_tax_rate_rec.tax_rate_code,
1251                 1,
1252                 8192);
1253 
1254       g_tax_rate_info_ind_by_hash(l_tbl_index) := p_tax_rate_rec;
1255 
1256       IF fetch_tax_rate_info_csr%NOTFOUND THEN
1257          p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;     -- bug 4893261
1258          p_error_buffer := 'No tax rate info found for the specified tax_rate_id';
1259       END IF;
1260 
1261       CLOSE fetch_tax_rate_info_csr;
1262   END IF;
1263 
1264   IF (g_level_statement >= g_current_runtime_level ) THEN
1265 
1266     FND_LOG.STRING(g_level_statement,
1267                   'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info.END',
1268                   'ZX_TDS_UTILITIES_PKG.get_tax_rate_info(-)'||
1269                   ' RETURN_STATUS = ' || p_return_status||
1270                   ' error buffer: '||p_error_buffer);
1271   END IF;
1272 
1273 EXCEPTION
1274   WHEN OTHERS THEN
1275     p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1276     IF (g_level_unexpected >= g_current_runtime_level ) THEN
1277       FND_LOG.STRING(g_level_unexpected,
1278                     'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info',
1279                      sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1280       FND_LOG.STRING(g_level_unexpected,
1281                     'ZX.PLSQL.ZX_TDS_UTILITIES_PKG.get_tax_rate_info.END',
1282                     'ZX_TDS_UTILITIES_PKG.get_tax_rate_info(-)');
1283     END IF;
1284 
1285 END get_tax_rate_info;
1286 
1287 END  ZX_TDS_UTILITIES_PKG;
1288