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_GL_TAX_OPTIONS_PKG

Source


1 PACKAGE BODY zx_gl_tax_options_pkg AS
2 /* $Header: zxgltaxoptionb.pls 120.22 2006/09/22 17:00:53 nipatel ship $ */
3 
4 
5   -- Logging Infra
6   G_PKG_NAME                   CONSTANT VARCHAR2(30) := 'ZX_GL_TAX_OPTIONS_PKG';
7   G_CURRENT_RUNTIME_LEVEL      NUMBER;
8   G_LEVEL_UNEXPECTED           CONSTANT NUMBER       := FND_LOG.LEVEL_UNEXPECTED;
9   G_LEVEL_ERROR                CONSTANT NUMBER       := FND_LOG.LEVEL_ERROR;
10   G_LEVEL_EXCEPTION            CONSTANT NUMBER       := FND_LOG.LEVEL_EXCEPTION;
11   G_LEVEL_EVENT                CONSTANT NUMBER       := FND_LOG.LEVEL_EVENT;
12   G_LEVEL_PROCEDURE            CONSTANT NUMBER       := FND_LOG.LEVEL_PROCEDURE;
13   G_LEVEL_STATEMENT            CONSTANT NUMBER       := FND_LOG.LEVEL_STATEMENT;
14   G_MODULE_NAME                CONSTANT VARCHAR2(60) := 'ZX.PLSQL.ZX_GL_TAX_OPTIONS_PKG.';
15 
16 -- ***** PUBLIC PROCEDURES *****
17 /*===========================================================================+
18  | PROCEDURE
19  |    get_default_values
20  |
21  | IN
22  |    p_ledger_id          : Ledger ID
23  |    p_org_id             : Organization ID
24  |    p_le_id              : Legal Entity ID
25  |    p_account_segment    : Account Segment Number
26  |    p_account_type       : 'I' for Input Tax Rate Codes migrated from AP
27  |                           'O' for Output Tax Rate Codes migrated from AR
28  |                           'T'  for newly created Tax Rate Codes
29  |    p_trx_date           : Transaction Date
30  |
31  | OUT
32  |    x_default_regime_code     : Tax Regime Code
33  |    x_default_tax             : Tax
34  |    x_default_tax_status_code : Tax Status Code
35  |    x_default_tax_rate_code   : Tax Rate Code
36  |    x_default_tax_rate_id     : Tax Rate ID
37  |    x_default_rounding_code   : Rounding Code
38  |    x_default_incl_tax_flag   : Inclusive Tax Flag
39  |    x_return_status           : Either 'S' (Success), 'E' (Known Error),
40  |                                'U' (Unexpected Error/Exception)
41  |    x_msg_out                 : Output Message
42  |                                When x_return_status is 'E' x_msg_out returns
43  |                                message code.
44  |                                When x_return_status is 'U' x_msg_out returns
45  |                                SQLCODE.
46  |                                When x_return_status is 'S' x_msg_out returns
47  |                                NULL.
48  |
49  | DESCRIPTION
50  |     This routine returns Regime to Rate tax information for a particular
51  |     account segment value or a ledger.
52  |
53  | SCOPE - PUBLIC
54  |
55  | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
56  |
57  | CALLED FROM
58  |
59  | NOTES
60  | 1. Mapping between tax_type_code at journal line and tax_class in setup
61  |
62  |    TAX_TYPE_CODE    TAX_CLASS
63  |    -------------    --------------
64  |    'I'              'INPUT'
65  |    'O'              'OUTPUT'
66  |    'T'              NULL
67  |    NULL             NON_TAXABLE
68  |
69  |
70  | MODIFICATION HISTORY
71  | 04/08/2005   Yoshimichi Konishi   Created.
72  |
73  +==========================================================================*/
74 PROCEDURE get_default_values
75 (   p_api_version      IN   NUMBER  DEFAULT NULL,
76     p_ledger_id        IN   NUMBER,
77     p_org_id           IN   NUMBER,
78     p_le_id            IN   NUMBER,
79     p_account_segment  IN   VARCHAR2,
80     p_account_type     IN   VARCHAR2,
81     p_trx_date         IN   DATE,
82     x_default_regime_code       OUT   NOCOPY  VARCHAR2,
83     x_default_tax               OUT   NOCOPY  VARCHAR2,
84     x_default_tax_status_code   OUT   NOCOPY  VARCHAR2,
85     x_default_tax_rate_code     OUT   NOCOPY  VARCHAR2,
86     x_default_tax_rate_id       OUT   NOCOPY  NUMBER,
87     x_default_rounding_code     OUT   NOCOPY  VARCHAR2,
88     x_default_incl_tax_flag     OUT   NOCOPY  VARCHAR2,
89     x_return_status             OUT   NOCOPY  VARCHAR2,
90     x_msg_out                   OUT   NOCOPY  VARCHAR2
91 ) IS
92 
93   -- ***** CURSORS *****
94   CURSOR acct_rate_cur (p_tax_class         VARCHAR2,
95                         p_ledger_id         NUMBER,
96                         p_account_segment   VARCHAR2)
97   IS
98   SELECT  acr.tax_regime_code,
99           acr.tax,
100           acr.tax_status_code,
101           acr.tax_rate_code,
102           nvl(rates.source_id, rates.tax_rate_id),
103           acr.tax_class
104   FROM    zx_account_rates       acr,
105           zx_sco_rates           rates
106   WHERE   rates.tax_regime_code = acr.tax_regime_code
107   AND     rates.tax = acr.tax
108   AND     rates.tax_status_code = acr.tax_status_code
109   AND     rates.tax_rate_code = acr.tax_rate_code
110   AND     rates.active_flag = 'Y'
111   AND     nvl(acr.tax_class, p_tax_class) = p_tax_class
112   AND     acr.ledger_id = p_ledger_id
113   AND     acr.account_segment_value = p_account_segment
114   AND     p_trx_date >= rates.effective_from and
115           (p_trx_date <= rates.effective_to OR rates.effective_to IS NULL)
116   AND exists ( select 1 from zx_sco_taxes taxes
117       where taxes.tax_regime_code = rates.tax_regime_code
118         AND taxes.tax = rates.tax
119         AND taxes.live_for_applicability_flag = 'Y'
120         AND taxes.live_for_processing_flag = 'Y'
121         AND nvl(taxes.offset_tax_flag,'N') <> 'Y');
122 
123 
124   CURSOR acct_rate_sob_cur (p_tax_class         VARCHAR2,
125                             p_ledger_id         NUMBER)
126   IS
127   SELECT  acr.tax_regime_code,
128           acr.tax,
129           acr.tax_status_code,
130           acr.tax_rate_code,
131           nvl(rates.source_id, rates.tax_rate_id),
132           acr.tax_class
133   FROM    zx_account_rates       acr,
134           zx_sco_rates           rates
135   WHERE   rates.tax_regime_code = acr.tax_regime_code
136   AND     rates.tax = acr.tax
137   AND     rates.tax_status_code = acr.tax_status_code
138   AND     rates.tax_rate_code = acr.tax_rate_code
139   AND     rates.active_flag = 'Y'
140   AND     nvl(acr.tax_class, p_tax_class) = p_tax_class
141   AND     acr.ledger_id = p_ledger_id
142   AND     acr.account_segment_value IS NULL
143   AND     p_trx_date >= rates.effective_from and
144           (p_trx_date <= rates.effective_to OR rates.effective_to IS NULL)
145   AND exists ( select 1 from zx_sco_taxes taxes
146       where taxes.tax_regime_code = rates.tax_regime_code
147         AND taxes.tax = rates.tax
148         AND taxes.live_for_applicability_flag = 'Y'
149         AND taxes.live_for_processing_flag = 'Y'
150         AND nvl(taxes.offset_tax_flag,'N') <> 'Y');
151 
152   -- ***** VARIABLES *****
153   l_tax_type_code          VARCHAR2(30);
154   l_tax_class              VARCHAR2(30);
155   l_tax_class_tmp          VARCHAR2(30);
156   l_tax_regime_code        VARCHAR2(30);
157   l_tax                    VARCHAR2(50);
158   l_tax_status_code        VARCHAR2(30);
159   l_tax_rate_code          VARCHAR2(50);
160   l_tax_rate_id            NUMBER(15);
161   l_content_owner_id       NUMBER(15);
162   l_rounding_rule_code     VARCHAR2(30);
163   l_incl_tax_flag          VARCHAR2(1);
164   l_ledger_flag            VARCHAR2(1);
165   l_return_status          VARCHAR2(1);
166   l_msg_out                VARCHAR2(30);
167 
168    -- Logging Infra
169    l_api_name       CONSTANT   VARCHAR2(30) := 'GET_DEFAULT_VALUES';
170    l_api_version    CONSTANT   NUMBER := 1.0;
171    l_procedure_name CONSTANT VARCHAR2(30) := 'GET_DEFAULT_VALUES';
172    l_log_msg   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
173    -- Logging Infra
174    l_set_security_context_flag VARCHAR2(1);
175 
176 BEGIN
177 
178   -- Logging Infra: Setting up runtime level
179   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
180 
181   -- Logging Infra: Procedure level
182   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
183      FND_LOG.STRING(G_LEVEL_PROCEDURE,
184                     G_MODULE_NAME || l_procedure_name,
185                     'get_default_values(+)');
186   END IF;
187   -- Logging Infra: Break point input parameters
188   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
189       l_log_msg := 'B: IN: api_version=' || p_api_version ||
190                    ', ledger_id=' || p_ledger_id ||
191                    ', org_id=' || p_org_id ||
192                    ', account_segment=' || p_account_segment ||
193                    ', account_type=' || p_account_type ||
194                    ', trx_date=' || p_trx_date;
195       FND_LOG.STRING(G_LEVEL_STATEMENT,
196                     G_MODULE_NAME || l_procedure_name,
197                     l_log_msg);
198   END IF;
199 
200   x_return_status := FND_API.G_RET_STS_SUCCESS;
201 
202   IF p_account_type IN ('E', 'I') THEN
203      l_tax_class_tmp := 'INPUT';
204   ELSIF p_account_type IN ('R' , 'O') THEN
205      l_tax_class_tmp := 'OUTPUT';
206   ELSIF p_account_type = 'T' THEN
207      l_tax_class_tmp := p_account_type;
208   END IF;
209 
210   IF  ZX_SECURITY.g_first_party_org_id is NULL THEN
211      l_set_security_context_flag := 'Y';
212   ELSE
213      l_set_security_context_flag := 'N';
214   END IF;
215 
216   IF l_set_security_context_flag = 'Y' Then
217     -- Logging Infra: Procedure level
218     IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
219        FND_LOG.STRING(G_LEVEL_PROCEDURE,
220                       G_MODULE_NAME || l_procedure_name,
221                       'Setting Security Context');
222     END IF;
223 
224     ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
225 
226     IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
227      -- Logging Infra: Statement level
228       IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
229          FND_LOG.STRING(G_LEVEL_STATEMENT,
230                         G_MODULE_NAME || l_procedure_name,
231                         'Error Setting Security Context');
232       END IF;
233       Return;
234     END IF;
235   END IF;
236 
237   -- Logging Infra: Procedure level
238   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
239      FND_LOG.STRING(G_LEVEL_PROCEDURE,
240                     G_MODULE_NAME || l_procedure_name,
241                     'Fetching default tax rate info');
242   END IF;
243 
244   -- Account Level
245     -- Logging Infra: Break point acct_rate_cur
246     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
247         l_log_msg := 'B: CUR: acct_rate_cur: tax_class=' || l_tax_class_tmp ||
248                      ', ledger_id=' || p_ledger_id ||
249                      ', account_segment=' || p_account_segment;
250         FND_LOG.STRING(G_LEVEL_STATEMENT,
251                       G_MODULE_NAME || l_procedure_name,
252                       l_log_msg);
253     END IF;
254 
255     OPEN acct_rate_cur (l_tax_class_tmp,
256                         p_ledger_id,
257                         p_account_segment);
258     FETCH acct_rate_cur INTO x_default_regime_code,
259                              x_default_tax,
260                              x_default_tax_status_code,
261                              x_default_tax_rate_code,
262                              x_default_tax_rate_id,
263                              l_tax_class;
264     IF acct_rate_cur%FOUND THEN
265      l_ledger_flag := 'N';
266     ELSE
267      l_ledger_flag := 'Y';
268     END IF;
269 
270     CLOSE acct_rate_cur;
271 
272     -- Logging Infra: Break point acct_rate_cur
273     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
274         l_log_msg := 'B: CUR: acct_rate_cur: tax_regime_code=' || x_default_regime_code ||
275                      ', tax=' || x_default_tax ||
276                      ', tax_status_code=' || x_default_tax_status_code ||
277                      ', tax_rate_code=' || x_default_tax_rate_code ||
278                      ', tax_rate_id=' || x_default_tax_rate_id ||
279                      ', tax_class=' || l_tax_class;
280         FND_LOG.STRING(G_LEVEL_STATEMENT,
281                        G_MODULE_NAME || l_procedure_name,
282                        l_log_msg);
283     END IF;
284 
285   -- Ledger / Set Of Books Level
286   IF l_ledger_flag = 'Y' THEN
287 
288     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
289         l_log_msg := 'B: CUR: acct_rate_sob_cur: tax_class=' || l_tax_class_tmp ||
290                      ', ledger_id=' || p_ledger_id;
291         FND_LOG.STRING(G_LEVEL_STATEMENT,
292                       G_MODULE_NAME || l_procedure_name,
293                       l_log_msg);
294     END IF;
295 
296     OPEN acct_rate_sob_cur (l_tax_class_tmp,
297                             p_ledger_id);
298 
299     FETCH acct_rate_sob_cur INTO x_default_regime_code,
300                                  x_default_tax,
301                                  x_default_tax_status_code,
302                                  x_default_tax_rate_code,
303                                  x_default_tax_rate_id,
304                                  l_tax_class;
305     CLOSE acct_rate_sob_cur;
306 
307     -- Logging Infra: Break point acct_rate_cur
308     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
309         l_log_msg := 'B: CUR: acct_rate_sob_cur: tax_regime_code=' || x_default_regime_code ||
310                      ', tax=' || x_default_tax ||
311                      ', tax_status_code=' || x_default_tax_status_code ||
312                      ', tax_rate_code=' || x_default_tax_rate_code ||
313                      ', tax_rate_id=' || x_default_tax_rate_id ||
314                      ', tax_class=' || l_tax_class;
315         FND_LOG.STRING(G_LEVEL_STATEMENT,
316                       G_MODULE_NAME || l_procedure_name,
317                       l_log_msg);
318     END IF;
319   END IF;
320 
321 
322   IF l_set_security_context_flag = 'Y' Then
323 
324     -- Logging Infra: Procedure level
325     IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
326        FND_LOG.STRING(G_LEVEL_PROCEDURE,
327                       G_MODULE_NAME || l_procedure_name,
328                       'Resetting First Party Org context to NULL');
329     END IF;
330 
331     ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
332      --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
333      ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
334   END IF;
335 
336   -- Setting l_tax_type_code to pass to APIs that default rounding
337   -- rule and default tax include flag
338 
339   IF l_tax_class IS NULL THEN
340      l_tax_type_code := 'T';
341   ELSIF l_tax_class = 'INPUT' THEN
342      l_tax_type_code := 'I';
343   ELSIF l_tax_class = 'OUTPUT' THEN
344      l_tax_type_code := 'O';
345   END IF;
346 
347   -- Logging Infra: Procedure level
348   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
349      FND_LOG.STRING(G_LEVEL_PROCEDURE,
350                     G_MODULE_NAME || l_procedure_name,
351                     'Fetching default rounding rule');
352   END IF;
353 
354   GET_ROUNDING_RULE_CODE(1.0,
355                          p_ledger_id,
356                          p_org_id,
357                          p_le_id,
358                          l_tax_type_code,
359                          x_default_rounding_code,
360                          x_return_status,
361                          x_msg_out);
362 
363   IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
364    -- Logging Infra: Statement level
365     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
366        FND_LOG.STRING(G_LEVEL_STATEMENT,
367                       G_MODULE_NAME || l_procedure_name,
368                       'Error from API to default rounding rule');
369     END IF;
370     Return;
371   END IF;
372 
373   -- Logging Infra: Procedure level
374   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
375      FND_LOG.STRING(G_LEVEL_PROCEDURE,
376                     G_MODULE_NAME || l_procedure_name,
377                     'Fetching default tax include flag');
378   END IF;
379 
380   GET_DEFAULT_TAX_INCLUDE_FLAG(1.0,
381                                p_ledger_id,
382                                p_org_id,
383                                p_le_id,
384                                p_account_segment,
385                                l_tax_type_code,
386                                x_default_incl_tax_flag,
387                                x_return_status,
388                                x_msg_out);
389 
390   IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
391    -- Logging Infra: Statement level
392     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
393        FND_LOG.STRING(G_LEVEL_STATEMENT,
394                       G_MODULE_NAME || l_procedure_name,
395                       'Error from API to default tax include flag');
396     END IF;
397     Return;
398   END IF;
399 
400   IF x_default_regime_code IS NULL THEN
401     IF x_default_rounding_code IS NULL THEN
402       -- Logging Infra: Break point acct_rate_sob_cur
403       IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
404 	  l_log_msg := 'B: tax_regime_code/rounding_rule_code IS NULL';
405 	  FND_LOG.STRING(G_LEVEL_STATEMENT,
406 			G_MODULE_NAME || l_procedure_name,
407 			l_log_msg);
408       END IF;
409       l_return_status := FND_API.G_RET_STS_ERROR;
410       l_msg_out := 'ZX_GL_ROUNDING_CODE_NULL';
411       Return;
412     ELSE
413       -- ledger level information (rounding_rule_code) has been derived
414       IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
415 	  l_log_msg := 'B: tax_regime_code IS NULL but rounding_rule_code IS NOT NULL';
416 	  FND_LOG.STRING(G_LEVEL_STATEMENT,
417 			G_MODULE_NAME || l_procedure_name,
418 			l_log_msg);
419       END IF;
420     END IF;
421   END IF;
422 
423   /* comment out as the user should be able to continue to enter journal even if
424      default value is not found. This is consistent with 11i behaviour.
425   IF x_default_tax_rate_id IS NULL THEN
426       x_return_status := FND_API.G_RET_STS_ERROR;
427       x_msg_out := 'ZX_GL_RATE_ID_NULL';
428      -- Logging Infra: Statement level
429       IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
430          FND_LOG.STRING(G_LEVEL_STATEMENT,
431                         G_MODULE_NAME || l_procedure_name,
432                         x_msg_out);
433       END IF;
434       Return;
435   END IF;
436   */
437 
438    -- Logging Infra: Break point output parameters
439    IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
440        l_log_msg := 'B: OUT: return_status=' || x_return_status ||
441                     ', tax_regime_code=' || x_default_regime_code ||
442                     ', tax=' || x_default_tax ||
443                     ', tax_status_code=' || x_default_tax_status_code ||
444                     ', tax_rate_code=' || x_default_tax_rate_code ||
445                     ', rounding_rule_code=' || x_default_rounding_code ||
446                     ', incl_tax_flag=' || x_default_incl_tax_flag ||
447                     ', tax_rate_id=' || x_default_tax_rate_id;
448        FND_LOG.STRING(G_LEVEL_STATEMENT,
449                      G_MODULE_NAME || l_procedure_name,
450                      l_log_msg);
451    END IF;
452    -- Logging Infra: Procedure level
453    IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
454       FND_LOG.STRING(G_LEVEL_PROCEDURE,
455                      G_MODULE_NAME,
456                      'get_default_value(-)');
457    END IF;
458 
459 EXCEPTION
460   WHEN OTHERS THEN
461     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462     x_msg_out := TO_CHAR(SQLCODE);
463     -- Logging Infra:
464     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
465        FND_LOG.STRING(G_LEVEL_STATEMENT,
466                       G_MODULE_NAME || l_procedure_name,
467                       TO_CHAR(SQLCODE) || ': ' || SQLERRM);
468     END IF;
469     app_exception.raise_exception;
470 END get_default_values;
471 
472 
473 /*===========================================================================+
474  | PROCEDURE
475  |    get_tax_rate_and_account
476  |
477  | IN
478  |    p_ledger_id          : Ledger ID
479  |    p_org_id             : Organization ID
480  |    p_tax_type_code      : 'I' for Input Tax Rate Codes migrated from AP
481  |                           'O' for Output Tax Rate Codes migrated from AR
482  |                           'T' for newly created Tax Rate Codes
483  |    p_tax_rate_id        : Tax Rate ID
484  |
485  | OUT
486  |    x_tax_rate_pct       : Tax Percentage Rate
487  |    x_tax_account_ccid   : Tax Account CCID
488  |    x_return_status      : Return Status. See get_default_value for details.
489  |    x_msg_out            : Output Message. See get_default_value for
490  |                           details.
491  |
492  | DESCRIPTION
493  |     This routine returns tax percentage rate and its accounting CCID for
494  |     a particular tax_rate_id.
495  |
496  | SCOPE - PUBLIC
497  |
498  | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
499  |
500  | CALLED FROM
501  |
502  | NOTES
503  |
504  |
505  | MODIFICATION HISTORY
506  | 04/08/2005   Yoshimichi Konishi   Created.
507  |
508  +==========================================================================*/
509 PROCEDURE get_tax_rate_and_account
510 (   p_api_version       IN   NUMBER  DEFAULT NULL,
511     p_ledger_id         IN   NUMBER,
512     p_org_id            IN   NUMBER,
513     p_tax_type_code     IN   VARCHAR2,
514     p_tax_rate_id       IN   NUMBER,
515     x_tax_rate_pct      OUT  NOCOPY   NUMBER,
516     x_tax_account_ccid  OUT  NOCOPY   NUMBER,
517     x_return_status     OUT  NOCOPY   VARCHAR2,
518     x_msg_out           OUT  NOCOPY   VARCHAR2
519 ) IS
520 
521   -- ***** CURSORS *****
522   CURSOR get_pct_rate_ccid_cur (p_tax_rate_id  NUMBER,
523                                 p_org_id       NUMBER,
524                                 p_ledger_id    NUMBER)
525   IS
526   SELECT  zrb.percentage_rate,
527           NVL(za.tax_account_ccid, za.non_rec_account_ccid)
528           -- Bug 4766614
529           -- Added NVL so that when tax_account_ccid is null
530           -- the API returns non_rec_account_ccid
531   FROM    zx_rates_b zrb,
532           zx_accounts  za
533   WHERE   nvl(zrb.source_id, zrb.tax_rate_id) = p_tax_rate_id
534   AND     za.internal_organization_id(+) = p_org_id
535   AND     za.ledger_id(+) = p_ledger_id
536   AND     za.tax_account_entity_id(+) = zrb.tax_rate_id
537   AND     za.tax_account_entity_code(+) = 'RATES';
538 
539   -- ***** VARIABLES *****
540   l_pct_rate            NUMBER;
541   l_tax_account_ccid    NUMBER;
542   l_tax_rate_id         NUMBER(15);
543   l_source_id           NUMBER(15);
544   l_return_status       VARCHAR2(1);
545   l_msg_out             VARCHAR2(30);
546 
547   -- Logging Infra
548   l_api_name       CONSTANT   VARCHAR2(30) := 'GET_TAX_RATE_AND_ACCOUNT';
549   l_api_version    CONSTANT   NUMBER := 1.0;
550   l_procedure_name CONSTANT VARCHAR2(30) := 'GET_TAX_RATE_AND_ACCOUNT';
551   l_log_msg   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
552   -- Logging Infra
553 
554 BEGIN
555   -- Logging Infra: Setting up runtime level
556   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
557 
558   -- Logging Infra: Procedure level
559   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
560      FND_LOG.STRING(G_LEVEL_PROCEDURE,
561                     G_MODULE_NAME || l_procedure_name,
562                     'get_tax_rate_and_account(+)');
563   END IF;
564   -- Logging Infra: Break point input parameters
565   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
566       l_log_msg := 'B: IN: api_version=' || p_api_version ||
567                    ', tax_type_code=' || p_tax_type_code ||
568                    ', tax_rate_id=' || p_tax_rate_id;
572   END IF;
569       FND_LOG.STRING(G_LEVEL_STATEMENT,
570                     G_MODULE_NAME || l_procedure_name,
571                     l_log_msg);
573 
574   OPEN get_pct_rate_ccid_cur (p_tax_rate_id, p_org_id, p_ledger_id);
575 
576   FETCH get_pct_rate_ccid_cur INTO l_pct_rate,
577                                    l_tax_account_ccid;
578   IF get_pct_rate_ccid_cur%FOUND THEN
579     l_return_status := FND_API.G_RET_STS_SUCCESS;
580   ELSE
581     l_return_status := FND_API.G_RET_STS_ERROR;
582     x_msg_out := 'ZX_GL_OUT_RATE_CCID_NOTFOUND';
583   END IF;
584 
585   CLOSE get_pct_rate_ccid_cur;
586 
587   -- Logging Infra: Break point get_pct_rate_ccid_cur
588   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
589       l_log_msg := 'B: CUR: get_pct_rate_ccid_cur: pct_rate=' || l_pct_rate ||
590                    ', tax_account_ccid=' || l_tax_account_ccid;
591       FND_LOG.STRING(G_LEVEL_STATEMENT,
592                     G_MODULE_NAME || l_procedure_name,
593                     l_log_msg);
594   END IF;
595 
596   x_tax_rate_pct       := l_pct_rate;
597   x_tax_account_ccid   := l_tax_account_ccid;
598   x_return_status      := l_return_status;
599   x_msg_out            := l_msg_out;
600 
601   -- Logging Infra: Break point output parameters
602   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
603       l_log_msg := 'B: OUT: tax_rate_pct=' || l_pct_rate ||
604                    ', tax_account_ccid=' || l_tax_account_ccid ||
605                    ', return_status=' || l_return_status;
606       FND_LOG.STRING(G_LEVEL_STATEMENT,
607                     G_MODULE_NAME || l_procedure_name,
608                     l_log_msg);
609   END IF;
610   -- Logging Infra: Procedure level
611   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
612      FND_LOG.STRING(G_LEVEL_PROCEDURE,
613                     G_MODULE_NAME || l_procedure_name,
614                     'get_tax_rate_and_account(-)');
615   END IF;
616 
617 EXCEPTION
618   WHEN OTHERS THEN
619     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620     x_msg_out := TO_CHAR(SQLCODE);
621     -- Logging Infra:
622     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
623        FND_LOG.STRING(G_LEVEL_STATEMENT,
624                       G_MODULE_NAME || l_procedure_name,
625                       TO_CHAR(SQLCODE) || ': ' || SQLERRM);
626     END IF;
627     app_exception.raise_exception;
628 END get_tax_rate_and_account;
629 
630 
631 /*===========================================================================+
632  | PROCEDURE
633  |    get_tax_ccid
634  |
635  | IN
636  |    p_tax_rate_id        : Tax Rate ID
637  |    p_org_id             : Organization ID
638  |    p_ledger_id          : Ledger ID
639  |
640  | OUT
644  |                           details.
641  |    x_tax_account_ccid   : Tax Account CCID
642  |    x_return_status      : Return Status. See get_default_value for details.
643  |    x_msg_out            : Output Message. See get_default_value for
645  |
646  | DESCRIPTION
647  |     This routine returns tax accounting CCID for a particular tax_rate_id.
648  |
649  |
650  | SCOPE - PUBLIC
651  |
652  | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
653  |
654  | CALLED FROM
655  |
656  | NOTES
657  |
658  |
659  | MODIFICATION HISTORY
660  | 04/08/2005   Yoshimichi Konishi   Created.
661  |
662  +==========================================================================*/
663 PROCEDURE get_tax_ccid
664 (  p_api_version        IN   NUMBER,
665    p_tax_rate_id        IN   NUMBER,
666    p_org_id             IN   NUMBER,
667    p_ledger_id          IN   NUMBER,
668    x_tax_account_ccid   OUT  NOCOPY   NUMBER,
669    x_return_status      OUT  NOCOPY   VARCHAR2,
670    x_msg_out            OUT  NOCOPY   VARCHAR2
671 ) IS
672 
673   -- ***** CURSORS *****
674   CURSOR tax_acct_cur (p_tax_rate_id   NUMBER,
675                        p_ledger_id     NUMBER,
676                        p_org_id        NUMBER)
677   IS
678   SELECT  NVL(tax_account_ccid, non_rec_account_ccid)
679           -- Bug 4766614
680           -- Added NVL so that when tax_account_ccid is null
681           -- the API returns non_rec_account_ccid
682   FROM    zx_accounts   za, zx_rates_b rates
683   WHERE   nvl(rates.source_id, rates.tax_rate_id) = p_tax_rate_id
684   AND     za.tax_account_entity_id(+) = rates.tax_rate_id
685   AND     za.tax_account_entity_code(+) = 'RATES'
686   AND     za.ledger_id(+) = p_ledger_id
687   AND     za.internal_organization_id(+) = p_org_id;
688 
689   -- ***** VARIABLES *****
690 
691   -- Logging Infra
692   l_api_name       CONSTANT   VARCHAR2(30) := 'GET_TAX_CCID';
693   l_api_version    CONSTANT   NUMBER := 1.0;
694   l_procedure_name CONSTANT VARCHAR2(30) := 'GET_TAX_CCID';
695   l_log_msg   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
696   -- Logging Infra
697 
698 BEGIN
699   -- Logging Infra: Setting up runtime level
700   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
701 
702   -- Logging Infra: Procedure level
703   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
704      FND_LOG.STRING(G_LEVEL_PROCEDURE,
705                     G_MODULE_NAME || l_procedure_name,
706                     'get_tax_ccid(+)');
707   END IF;
708 
709   -- Logging Infra: Break point input parameters
710   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
711       l_log_msg := 'B: IN: api_version=' || p_api_version ||
712                    ', tax_rate_id=' || p_tax_rate_id ||
713                    ', org_id=' || p_org_id ||
714                    ', ledger_id=' || p_ledger_id;
715       FND_LOG.STRING(G_LEVEL_STATEMENT,
716                     G_MODULE_NAME || l_procedure_name,
717                     l_log_msg);
718   END IF;
719 
720   OPEN tax_acct_cur (p_tax_rate_id,
721                      p_ledger_id,
722                      p_org_id);
723 
724   FETCH tax_acct_cur INTO x_tax_account_ccid;
725 
726   IF tax_acct_cur%FOUND THEN
727     x_return_status := FND_API.G_RET_STS_SUCCESS;
728   ELSE
729     x_return_status := FND_API.G_RET_STS_ERROR;
730     x_msg_out := 'ZX_GL_TAXCCID_NOT_FOUND';
731   END IF;
732 
733   CLOSE tax_acct_cur;
734 
735   -- Logging Infra: Break point output parameters
736   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
737       l_log_msg := 'B: OUT: tax_account_ccid=' || x_tax_account_ccid ||
738                    ', return_status=' || x_return_status;
739       FND_LOG.STRING(G_LEVEL_STATEMENT,
740                     G_MODULE_NAME || l_procedure_name,
741                     l_log_msg);
742   END IF;
743 
744   -- Logging Infra: Procedure level
745   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
746      FND_LOG.STRING(G_LEVEL_PROCEDURE,
747                     G_MODULE_NAME || l_procedure_name,
748                     'get_tax_ccid(-)');
749   END IF;
750 
751 EXCEPTION
752   WHEN OTHERS THEN
753     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754     x_msg_out := TO_CHAR(SQLCODE);
755     -- Logging Infra:
756     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
757        FND_LOG.STRING(G_LEVEL_STATEMENT,
758                       G_MODULE_NAME || l_procedure_name,
759                       TO_CHAR(SQLCODE) || ': ' || SQLERRM);
760     END IF;
761     app_exception.raise_exception;
762 END get_tax_ccid;
763 
764 
765 /*===========================================================================+
766  | PROCEDURE
767  |    get_tax_rate_id
768  |
769  | IN
770  |    p_org_id             : Organization ID
771  |    p_le_id              : Legal Entity ID
772  |    p_tax_rate_code      : Tax Rate Code
773  |    p_trx_date           : Transaction Date
774  |    p_tax_type_code      : 'I' for Input Tax Rate Codes migrated from AP
775  |                           'O' for Output Tax Rate Codes migrated from AR
776  |                           'T' for newly created Tax Rate Codes
777  |
778  | OUT
779  |    p_tax_type_code      : 'I' for Input Tax Rate Codes migrated from AP
780  |                           'O' for Output Tax Rate Codes migrated from AR
781  |                           'T' for newly created Tax Rate Codes
782  |    x_tax_rate_id        : Tax Rate ID
783  |    x_return_status      : Return Status. See get_default_value for details.
784  |    x_msg_out            : Output Message. See get_default_value for details.
785  |
786  | DESCRIPTION
787  |     This routine returns tax rate ID for active tax rate code at a particular
788  |     point in time.
789  |
790  | SCOPE - PUBLIC
791  |
792  | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
793  |
794  | CALLED FROM
795  |
796  | NOTES
797  |
798  |
799  | MODIFICATION HISTORY
800  | 04/08/2005   Yoshimichi Konishi   Created.
801  |
802  +==========================================================================*/
803 PROCEDURE get_tax_rate_id
804 (   p_api_version       IN   NUMBER DEFAULT NULL,
805     p_org_id            IN   NUMBER,
806     p_le_id             IN   NUMBER,
807     p_tax_rate_code     IN   VARCHAR2,
808     p_trx_date          IN   DATE,
809     p_tax_type_code     IN OUT NOCOPY   VARCHAR2,
810     x_tax_rate_id       OUT    NOCOPY   NUMBER,
811     x_return_status     OUT    NOCOPY   VARCHAR2,
812     x_msg_out           OUT    NOCOPY   VARCHAR2
813 ) IS
814 
815   -- ***** CURSORS *****
816 
817   --
818   --  IF tax_type_code IS 'T'
819   --             THEN tax_class is NULL
820   --
821   -- This is the case for GL Tax Options records newly created after migration.
822   --
823   --
824   --
825   CURSOR rate_id_for_null_type_cur (p_tax_rate_code   VARCHAR2,
826                                     p_trx_date        DATE)
827   IS
828   SELECT   rates.tax_rate_id,
829            rates.tax_class
830   FROM     zx_sco_rates rates,
831            zx_taxes_b   taxes
832   WHERE    rates.tax_rate_code = p_tax_rate_code
833   AND      p_trx_date >= rates.effective_from
834   AND      p_trx_date <= NVL(rates.effective_to, p_trx_date)
835   AND      nvl(rates.active_flag,'Y') = 'Y'
836   AND      rates.rate_type_code = 'PERCENTAGE'
837   AND      taxes.tax_regime_code = rates.tax_regime_code
838   AND      taxes.tax = rates.tax
839   AND      taxes.source_tax_flag = 'Y'
840   AND      nvl(taxes.offset_tax_flag, 'N') <> 'Y'
841   AND      rates.tax_jurisdiction_code IS NULL;
842 
843 
844   --
845   -- Assumption: IF tax_type_code IS NOT NULL
846   --             THEN tax_class IS NOT NULL
847   --
848   -- This is the case for migrated GL Tax Options records.
849   --
850   CURSOR rate_id_for_mig_cur (p_tax_rate_code   VARCHAR2,
851                               p_tax_class       VARCHAR2,
852                               p_trx_date        DATE)
853   IS
854   SELECT  rates.tax_rate_id
855   FROM    zx_sco_rates rates,
856           zx_taxes_b   taxes
857   WHERE    rates.tax_rate_code = p_tax_rate_code
858   AND      p_trx_date >= rates.effective_from
859   AND      p_trx_date <= NVL(rates.effective_to, p_trx_date)
860   AND      nvl(rates.active_flag,'Y') = 'Y'
861   AND      rates.rate_type_code = 'PERCENTAGE'
862   AND      taxes.tax_regime_code = rates.tax_regime_code
863   AND      taxes.tax = rates.tax
864   AND      taxes.source_tax_flag = 'Y'
865   AND      nvl(taxes.offset_tax_flag, 'N') <> 'Y'
866   AND      rates.tax_jurisdiction_code IS NULL
867   AND      rates.tax_class = p_tax_class;
868 
869   -- ***** VARIABLES *****
870   l_tax_type_code   VARCHAR2(1);
871   l_tax_class       VARCHAR2(30);
872   l_tax_rate_id     NUMBER(15);
873   l_return_status   VARCHAR2(1);
874 
875   -- Logging Infra
876   l_api_name       CONSTANT   VARCHAR2(30) := 'GET_TAX_RATE_ID';
877   l_api_version    CONSTANT   NUMBER := 1.0;
878   l_procedure_name CONSTANT VARCHAR2(30) := 'GET_TAX_RATE_ID';
879   l_log_msg   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
880   -- Logging Infra
881   l_set_security_context_flag VARCHAR2(1);
882 
883 BEGIN
884   -- Logging Infra: Setting up runtime level
885   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
886 
887   -- Logging Infra: Procedure level
888   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
889      FND_LOG.STRING(G_LEVEL_PROCEDURE,
890                     G_MODULE_NAME || l_procedure_name,
891                     'get_tax_rate_id(+)');
892   END IF;
893   -- Logging Infra: Break point input parameters
894   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
895       l_log_msg := 'B: IN: api_version=' || p_api_version ||
896                    ', org_id=' || p_org_id ||
897                    ', tax_rate_code=' || p_tax_rate_code ||
898                    ', trx_date=' || p_trx_date ||
899                    ', tax_type_code=' || p_tax_type_code;
900       FND_LOG.STRING(G_LEVEL_STATEMENT,
901                     G_MODULE_NAME || l_procedure_name,
902                     l_log_msg);
903   END IF;
904 
905 
906   x_return_status := FND_API.G_RET_STS_SUCCESS;
907 
908   IF p_tax_rate_code = 'STD_AR_INPUT' THEN
909       x_return_status := FND_API.G_RET_STS_ERROR;
910       x_msg_out := 'ZX_GL_INVALID_TAX_RATE_CODE';
911     -- Logging Infra:
912     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
913        FND_LOG.STRING(G_LEVEL_STATEMENT,
914                       G_MODULE_NAME || l_procedure_name,
915                       'p_tax_rate_code is STD_AR_INPUT');
916     END IF;
917     Return;
918   END IF;
919 
920 
921   IF  ZX_SECURITY.g_first_party_org_id is NULL THEN
922      l_set_security_context_flag := 'Y';
923   ELSE
924      l_set_security_context_flag := 'N';
925   END IF;
926 
927   IF l_set_security_context_flag = 'Y' Then
928 
929       -- Logging Infra: Procedure level
930       IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
931          FND_LOG.STRING(G_LEVEL_PROCEDURE,
932                         G_MODULE_NAME || l_procedure_name,
933                         'Setting Security Context');
934       END IF;
935 
936       ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
937 
938       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
939         -- Logging Infra: Statement level
940         IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
941            FND_LOG.STRING(G_LEVEL_STATEMENT,
942                           G_MODULE_NAME || l_procedure_name,
943                           'Error Setting Security Context');
944         END IF;
945         Return;
946       END IF;
947 
948   END IF; -- l_set_security_context_flag
949 
950   -- Logging Infra: Procedure level
951   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
952      FND_LOG.STRING(G_LEVEL_PROCEDURE,
953                     G_MODULE_NAME || l_procedure_name,
954                     'Fetching Tax Rate Id');
955   END IF;
956 
957   IF p_tax_type_code IS NULL OR p_tax_type_code = 'T' THEN
958     OPEN rate_id_for_null_type_cur (p_tax_rate_code,
959                                     p_trx_date);
960     FETCH rate_id_for_null_type_cur INTO l_tax_rate_id,
961                                          l_tax_class;
962     CLOSE rate_id_for_null_type_cur;
963 
964     IF l_tax_class IS NULL THEN
965       p_tax_type_code := 'T';
966     ELSIF l_tax_class = 'INPUT' THEN
967       p_tax_type_code := 'I';
968     ELSIF l_tax_class = 'OUTPUT' THEN
969       p_tax_type_code := 'O';
970     END IF;
971 
972     -- Logging Infra: Break point rate_id_for_null_type_cur
973     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
974         l_log_msg := 'B: CUR: rate_id_for_null_type: tax_rate_id=' || l_tax_rate_id ||
975                      ', tax_type_code=' || p_tax_type_code;
976         FND_LOG.STRING(G_LEVEL_STATEMENT,
977                       G_MODULE_NAME || l_procedure_name,
978                       l_log_msg);
979     END IF;
980   ELSIF p_tax_type_code IN ('I', 'O') THEN
981 
982     IF p_tax_type_code = 'I' THEN
983       l_tax_class := 'INPUT';
984     ELSIF p_tax_type_code = 'O' THEN
985       l_tax_class := 'OUTPUT';
986     END IF;
987 
988     OPEN rate_id_for_mig_cur (p_tax_rate_code,
989                               l_tax_class,
990                               p_trx_date);
991     FETCH rate_id_for_mig_cur INTO l_tax_rate_id;
992     CLOSE rate_id_for_mig_cur;
993 
994     -- Logging Infra: Break point rate_id_for_mig_cur
995     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
996         l_log_msg := 'B: CUR: rate_id_for_mig_cur: tax_rate_id=' || l_tax_rate_id ||
997                      ', tax_type_code=' || p_tax_type_code;
998         FND_LOG.STRING(G_LEVEL_STATEMENT,
999                       G_MODULE_NAME || l_procedure_name,
1000                       l_log_msg);
1001     END IF;
1002 
1003   END IF;
1004 
1005   x_tax_rate_id   := l_tax_rate_id;
1006 
1007   -- Logging Infra: Break point output parameters
1008   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1009       l_log_msg := 'B: OUT: tax_type_code=' || l_tax_type_code ||
1010                    ', tax_rate_id=' || l_tax_rate_id ||
1011                    ', return_status=' || x_return_status;
1012       FND_LOG.STRING(G_LEVEL_STATEMENT,
1013                     G_MODULE_NAME || l_procedure_name,
1014                     l_log_msg);
1015   END IF;
1016 
1017   -- Logging Infra: Procedure level
1018   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1019      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1020                     G_MODULE_NAME || l_procedure_name,
1021                     'Resetting First Party Org context to NULL');
1022   END IF;
1023 
1024   IF l_set_security_context_flag = 'Y' Then
1025     ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1026      --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1027      ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1028   END IF;
1029 
1030   -- Logging Infra: Procedure level
1034                     'get_tax_rate_id(-)');
1031   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1032      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1033                     G_MODULE_NAME || l_procedure_name,
1035   END IF;
1036 EXCEPTION
1037   WHEN OTHERS THEN
1038     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1039     x_msg_out := TO_CHAR(SQLCODE);
1040     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1041        FND_LOG.STRING(G_LEVEL_STATEMENT,
1042                       G_MODULE_NAME || l_procedure_name,
1043                       TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1044     END IF;
1045     app_exception.raise_exception;
1046 END get_tax_rate_id;
1047 
1048 
1049 
1050 /*===========================================================================+
1051  | PROCEDURE
1052  |    get_tax_code
1053  |
1054  | IN
1055  |
1056  | OUT
1057  |
1058  | DESCRIPTION
1059  |    Will be obsolete.
1060  |
1061  | SCOPE - PUBLIC
1062  |
1063  | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1064  |
1065  | CALLED FROM
1066  |
1067  | NOTES
1068  |
1069  |
1070  | MODIFICATION HISTORY
1071  | 04/08/2005   Yoshimichi Konishi   Created.
1072  |
1073  +==========================================================================*/
1074 PROCEDURE get_tax_code
1075 (   p_api_version       IN   NUMBER  DEFAULT NULL,
1076     p_org_id            IN   NUMBER,
1077     p_tax_type_code     IN   VARCHAR2,
1078     p_tax_rate_id       IN   NUMBER,
1079     x_tax_rate_code     OUT  NOCOPY   VARCHAR2,
1080     x_return_status     OUT  NOCOPY   VARCHAR2,
1081     x_msg_out           OUT  NOCOPY   VARCHAR2
1082 ) IS
1083 BEGIN
1084   NULL;
1085 END  get_tax_code;
1086 
1087 
1088 /*===========================================================================+
1089  | PROCEDURE
1090  |    get_tax_rate_code
1091  |
1092  | IN
1093  |    p_tax_type_code  :  'I' for Input Tax Rate Codes migrated from AP
1094  |                        'O' for Output Tax Rate Codes migrated from AR
1095  |                        'T' for newly created Tax Rate Codes
1096  |
1097  |    p_tax_rate_id    :  Tax Rate ID
1098  |
1099  | OUT
1100  |    x_tax_rate_code  :  Tax Rate Code
1101  |
1102  | DESCRIPTION
1103  |    This routine returns tax rate code for a particular tax_rate_id.
1104  |
1105  | SCOPE - PUBLIC
1106  |
1107  | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1108  |
1109  | CALLED FROM
1110  |
1111  | NOTES
1112  |
1113  |
1114  | MODIFICATION HISTORY
1115  | 04/08/2005   Yoshimichi Konishi   Created.
1116  |
1117  +==========================================================================*/
1118 PROCEDURE get_tax_rate_code
1119 (   p_api_version       IN   NUMBER  DEFAULT NULL,
1120     p_tax_type_code     IN   VARCHAR2,
1121     p_tax_rate_id       IN   NUMBER,
1122     x_tax_rate_code     OUT  NOCOPY   VARCHAR2,
1123     x_return_status     OUT  NOCOPY   VARCHAR2,
1124     x_msg_out           OUT  NOCOPY   VARCHAR2
1125 ) IS
1126 
1127   -- ***** CURSORS *****
1128   CURSOR get_tax_rate_code_cur (p_tax_rate_id  NUMBER)
1129   IS
1130   SELECT  zrb.tax_rate_code
1131   FROM    zx_rates_b zrb
1132   WHERE   zrb.tax_rate_id = p_tax_rate_id;
1133 
1134   CURSOR get_source_rate_code_cur (p_tax_rate_id  NUMBER)
1135   IS
1136   SELECT  zrb.tax_rate_code
1137   FROM    zx_rates_b zrb
1138   WHERE   zrb.source_id = p_tax_rate_id;
1139 
1140   -- ***** VARIABLES *****
1141   l_tax_rate_code       VARCHAR2(50);
1142   l_tax_rate_id         NUMBER(15);
1143   l_source_id           NUMBER(15);
1144   l_return_status       VARCHAR2(1);
1145   l_source_indicator    VARCHAR2(1);
1146 
1147   -- Logging Infra
1148   l_api_name       CONSTANT   VARCHAR2(30) := 'GET_TAX_RATE_CODE';
1149   l_api_version    CONSTANT   NUMBER := 1.0;
1150   l_procedure_name CONSTANT   VARCHAR2(30) := 'GET_TAX_RATE_CODE';
1151   l_log_msg                   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1152   -- Logging Infra
1153 
1154 BEGIN
1155   -- Logging Infra: Setting up runtime level
1156   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1157 
1158   -- Logging Infra: Procedure level
1159   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1160      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1161                     G_MODULE_NAME || l_procedure_name,
1162                     'get_tax_rate_code(+)');
1163   END IF;
1164   -- Logging Infra: Break point input parameters
1165   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1166       l_log_msg := 'B: IN: api_version=' || p_api_version ||
1167                    ', tax_type_code=' || p_tax_type_code ||
1168                    ', tax_rate_id=' || p_tax_rate_id;
1169       FND_LOG.STRING(G_LEVEL_STATEMENT,
1170                     G_MODULE_NAME || l_procedure_name,
1171                     l_log_msg);
1172   END IF;
1173 
1174   l_return_status := FND_API.G_RET_STS_SUCCESS;
1175 
1176   IF p_tax_type_code = 'I' THEN
1177 
1178     OPEN get_source_rate_code_cur (p_tax_rate_id);
1179     FETCH get_source_rate_code_cur INTO l_tax_rate_code;
1180     IF get_source_rate_code_cur%FOUND THEN
1181        l_source_indicator := 'Y';
1182     ELSE
1183        l_source_indicator := 'N';
1184     END IF;
1185     CLOSE get_source_rate_code_cur;
1186 
1187     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1188       l_log_msg := 'B: CUR: get_source_rate_code_cur: tax_rate_code=' ||
1189                    l_tax_rate_code;
1190       FND_LOG.STRING(G_LEVEL_STATEMENT,
1191                     G_MODULE_NAME || l_procedure_name,
1192                     l_log_msg);
1193       FND_LOG.STRING(G_LEVEL_STATEMENT,
1197     END IF;
1194                     G_MODULE_NAME || l_procedure_name,
1195                     'l_source_indicator : ' || l_source_indicator);
1196 
1198 
1199   ELSIF p_tax_type_code = 'O' or p_tax_type_code = 'T' THEN
1200 
1201     l_source_indicator := 'N';
1202 
1203   ELSE
1204 
1205     l_return_status := FND_API.G_RET_STS_ERROR;
1206     x_msg_out := 'ZX_GL_INVALID_PARAM';
1207     -- Logging Infra:
1208     IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1209         l_log_msg := 'B: Unexpected tax_type_code';
1210         FND_LOG.STRING(G_LEVEL_STATEMENT,
1211                       G_MODULE_NAME || l_procedure_name,
1212                       l_log_msg);
1213     END IF;
1214     Return;
1215 
1216   END IF;
1217 
1218   IF l_source_indicator = 'N' THEN
1219 
1220     OPEN get_tax_rate_code_cur (p_tax_rate_id);
1221     FETCH get_tax_rate_code_cur INTO l_tax_rate_code;
1222     CLOSE get_tax_rate_code_cur;
1223 
1224   END IF;
1225 
1226   -- Logging Infra: Break point get_tax_rate_code_cur
1227   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1228       l_log_msg := 'B: CUR: get_tax_rate_code_cur: tax_rate_code=' || l_tax_rate_code;
1229       FND_LOG.STRING(G_LEVEL_STATEMENT,
1230                     G_MODULE_NAME || l_procedure_name,
1231                     l_log_msg);
1232 
1233   END IF;
1234 
1235   x_tax_rate_code      := l_tax_rate_code;
1236   x_return_status      := l_return_status;
1237 
1238   -- Logging Infra: Break point output parameters
1239   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1240       l_log_msg := 'B: OUT: tax_rate_code=' || l_tax_rate_code ||
1241                    ', return_status=' || l_return_status;
1242       FND_LOG.STRING(G_LEVEL_STATEMENT,
1243                     G_MODULE_NAME || l_procedure_name,
1244                     l_log_msg);
1245   END IF;
1246 
1247  -- Logging Infra: Procedure level
1248   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1249      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1250                     G_MODULE_NAME || l_procedure_name,
1251                     'get_tax_rate_and_account(-)');
1252   END IF;
1253 
1254 EXCEPTION
1255   WHEN OTHERS THEN
1256     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1257     x_msg_out := TO_CHAR(SQLCODE);
1258     -- Logging Infra:
1259     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1260        FND_LOG.STRING(G_LEVEL_STATEMENT,
1261                       G_MODULE_NAME || l_procedure_name,
1262                       TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1263     END IF;
1264     app_exception.raise_exception;
1265 END get_tax_rate_code;
1266 
1267 /*===========================================================================+
1268  | PROCEDURE
1269  |    get_rouding_rule_code
1270  |
1271  | IN
1272  |    p_api_version    : API Version
1273  |    p_ledger_id      : Ledger ID
1274  |    p_org_id         : Org ID
1275  |    p_le_id          : Legal Entity ID
1276  |    p_tax_class      : Tax Class/Tax Type
1277  |
1278  | OUT
1279  |    x_rouding_rule_code  :  Tax Rate Code
1280  |    x_return_status      :  Return Status
1281  |
1282  | DESCRIPTION
1283  |    This routine returns rounding_rule_code defined for a ledger.
1284  |
1285  | SCOPE - PUBLIC
1286  |
1287  | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1288  |
1289  | CALLED FROM
1290  |
1291  | NOTES
1292  |
1293  |
1294  | MODIFICATION HISTORY
1295  | 06/30/2005   Yoshimichi Konishi   Created.
1296  |
1297  +==========================================================================*/
1298 
1299 PROCEDURE get_rounding_rule_code
1300 ( p_api_version         IN  NUMBER DEFAULT NULL,
1301   p_ledger_id           IN  NUMBER,
1302   p_org_id              IN  NUMBER,
1303   p_le_id               IN  NUMBER,
1304   p_tax_class           IN  VARCHAR2,
1305   x_rounding_rule_code  OUT NOCOPY VARCHAR2,
1306   x_return_status       OUT NOCOPY VARCHAR2,
1307   x_msg_out             OUT NOCOPY VARCHAR2
1308 )
1309 IS
1310 
1311 l_return_status VARCHAR2(1);
1312 l_tax_class     VARCHAR2(30);
1313 
1314 CURSOR rounding_rule_cur (p_ledger_id   NUMBER,
1315                           p_tax_class   VARCHAR) IS
1316 SELECT rounding_rule_code
1317 FROM   zx_account_rates
1318 WHERE  ledger_id = p_ledger_id
1319 AND    account_segment_value IS NULL
1320 AND    (tax_class = p_tax_class
1321         OR
1322         tax_class IS NULL);
1323 
1324   -- Logging Infrastructure
1325   l_api_name       CONSTANT   VARCHAR2(30) := 'GET_ROUNDING_RULE_CODE';
1326   l_api_version    CONSTANT   NUMBER := 1.0;
1327   l_procedure_name CONSTANT   VARCHAR2(30) := 'GET_ROUNDING_RULE_CODE';
1328   l_log_msg                   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1329   -- Logging Infrastructure
1330   l_set_security_context_flag VARCHAR2(1);
1331 
1332 BEGIN
1333 
1334   -- Logging Infra: Setting up runtime level
1335   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1336 
1337   -- Logging Infra: Procedure level
1338   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1339      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1340                     G_MODULE_NAME || l_procedure_name,
1341                     'get_rounding_rule_code(+)');
1342   END IF;
1343 
1344   -- Logging Infra: Break point input parameters
1345   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1346       l_log_msg := 'B: IN: api_version=' || p_api_version ||
1347                    ', ledger_id=' || p_ledger_id ||
1348                    ', org_id=' || p_org_id ||
1349                    ', le_id=' || p_le_id ||
1350                    ', tax_class=' || p_tax_class;
1351       FND_LOG.STRING(G_LEVEL_STATEMENT,
1355 
1352                     G_MODULE_NAME || l_procedure_name,
1353                     l_log_msg);
1354   END IF;
1356     x_return_status := FND_API.G_RET_STS_SUCCESS;
1357 
1358   IF  ZX_SECURITY.g_first_party_org_id is NULL then
1359      l_set_security_context_flag := 'Y';
1360   ELSE
1361      l_set_security_context_flag := 'N';
1362   END IF;
1363 
1364   IF l_set_security_context_flag = 'Y' Then
1365 
1366      -- Logging Infra: Procedure level
1367      IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1368         FND_LOG.STRING(G_LEVEL_PROCEDURE,
1369                        G_MODULE_NAME || l_procedure_name,
1370                        'Setting Security Context');
1371      END IF;
1372 
1373        ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
1374 
1375      IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1376        -- Logging Infra: Statement level
1377        IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1378           FND_LOG.STRING(G_LEVEL_STATEMENT,
1379                          G_MODULE_NAME || l_procedure_name,
1380                          'Error Setting Security Context');
1381        END IF;
1382        Return;
1383      END IF;
1384   END IF; -- l_set_security_context_flag
1385 
1386   -- Logging Infra: Procedure level
1387   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1388      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1389                     G_MODULE_NAME || l_procedure_name,
1390                     'Fetching rounding rule code');
1391   END IF;
1392 
1393 
1394   IF p_tax_class = 'I' THEN
1395     l_tax_class := 'INPUT';
1396   END IF;
1397 
1398   IF p_tax_class = 'O' THEN
1399     l_tax_class := 'OUTPUT';
1400   END IF;
1401 
1402 OPEN rounding_rule_cur (p_ledger_id, l_tax_class);
1403 FETCH rounding_rule_cur INTO x_rounding_rule_code;
1404 
1405 IF rounding_rule_cur%NOTFOUND THEN
1406   x_rounding_rule_code := NULL;
1407 END IF;
1408 
1409 CLOSE rounding_rule_cur;
1410 
1411 
1412   IF l_set_security_context_flag = 'Y' Then
1413 
1414     -- Logging Infra: Procedure level
1415     IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1416        FND_LOG.STRING(G_LEVEL_PROCEDURE,
1417                       G_MODULE_NAME || l_procedure_name,
1418                       'Resetting First Party Org context to NULL');
1419     END IF;
1420 
1421     ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1422      --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1423      ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1424   END IF;
1425 
1426   -- Logging Infra: Procedure level
1427   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1428      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1429                     G_MODULE_NAME || l_procedure_name,
1430                     'get_rounding_rule_code(-)');
1431   END IF;
1432 
1433 EXCEPTION
1434   WHEN OTHERS THEN
1435     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436     x_msg_out := TO_CHAR(SQLCODE);
1437     -- Logging Infra:
1438     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1439        FND_LOG.STRING(G_LEVEL_STATEMENT,
1440                       G_MODULE_NAME || l_procedure_name,
1441                       TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1442     END IF;
1443     app_exception.raise_exception;
1444 
1445 END get_rounding_rule_code;
1446 
1447 
1448 /*===========================================================================+
1449  | PROCEDURE
1450  |    get_precision_mau
1451  |
1452  | IN
1453  |    p_ledger_id      : Ledger ID
1454  |    p_org_id         : Org ID
1455  |    p_le_id          : Legal Entity ID
1456  |
1457  | OUT
1458  |    x_precision  :  Precision
1459  |    x_mau        :  Minimum accountable unit
1460  |
1461  | DESCRIPTION
1462  |    This routine returns rounding_rule_code defined for a ledger.
1463  |
1464  | SCOPE - PUBLIC
1465  |
1466  | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1467  |
1468  | CALLED FROM
1469  |
1470  | NOTES
1471  |
1472  |
1473  | MODIFICATION HISTORY
1474  | 06/30/2005   Yoshimichi Konishi   Created.
1475  |
1476  +==========================================================================*/
1477 PROCEDURE get_precision_mau
1478 (  p_api_version IN NUMBER DEFAULT NULL,
1479    p_ledger_id   IN  NUMBER,
1480    p_org_id      IN  NUMBER,
1481    p_le_id       IN  NUMBER,
1482    x_precision   OUT NOCOPY  NUMBER,
1483    x_mau         OUT NOCOPY  NUMBER,
1484    x_return_status OUT NOCOPY VARCHAR2,
1485    x_msg_out       OUT NOCOPY VARCHAR2
1486 ) AS
1487 
1488 
1489 l_return_status VARCHAR2(1);
1490 l_first_pty_org_id NUMBER;
1491 
1492 
1493 CURSOR precision_mau_cur (p_ledger_id  NUMBER)
1494 IS
1495 SELECT tax_precision,
1496        tax_mau
1497 FROM   zx_account_rates
1498 WHERE  ledger_id = p_ledger_id
1499 AND    account_segment_value IS NULL
1500 AND    rownum = 1;
1501 
1502   -- Logging Infrastructure
1503   l_api_name       CONSTANT   VARCHAR2(30) := 'GET_PRECISION_MAU';
1504   l_api_version    CONSTANT   NUMBER := 1.0;
1505   l_procedure_name CONSTANT   VARCHAR2(30) := 'GET_PRECISION_MAU';
1506   l_log_msg                   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1507   -- Logging Infrastructure
1508   l_set_security_context_flag VARCHAR2(1);
1509 
1510 BEGIN
1511 
1512   -- Logging Infra: Setting up runtime level
1513   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1514 
1515   -- Logging Infra: Procedure level
1516   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1517      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1521 
1518                     G_MODULE_NAME || l_procedure_name,
1519                     'get_precision_mau(+)');
1520   END IF;
1522   -- Logging Infra: Break point input parameters
1523   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1524       l_log_msg := 'B: IN: api_version=' || p_api_version ||
1525                    ', ledger_id=' || p_ledger_id ||
1526                    ', org_id=' || p_org_id ||
1527                    ', le_id=' || p_le_id;
1528       FND_LOG.STRING(G_LEVEL_STATEMENT,
1529                     G_MODULE_NAME || l_procedure_name,
1530                     l_log_msg);
1531   END IF;
1532 
1533     x_return_status := FND_API.G_RET_STS_SUCCESS;
1534 
1535   -- Logging Infra: Procedure level
1536   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1537      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1538                     G_MODULE_NAME || l_procedure_name,
1539                     'Setting Security Context');
1540   END IF;
1541 
1542   IF  ZX_SECURITY.g_first_party_org_id is NULL then
1543      l_set_security_context_flag := 'Y';
1544   ELSE
1545      l_set_security_context_flag := 'N';
1546   END IF;
1547 
1548   IF l_set_security_context_flag = 'Y' Then
1549 
1550      -- l_first_pty_org_id  := ZX_SECURITY.G_FIRST_PARTY_ORG_ID;
1551      --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1552      --ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1553 
1554     ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
1555 
1556     IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1557       -- Logging Infra: Statement level
1558       IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1559          FND_LOG.STRING(G_LEVEL_STATEMENT,
1560                         G_MODULE_NAME || l_procedure_name,
1561                         'Error Setting Security Context');
1562       END IF;
1563       Return;
1564     END IF;
1565   END IF; -- l_set_security_context_flag
1566 
1567   -- Logging Infra: Procedure level
1568   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1569      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1570                     G_MODULE_NAME || l_procedure_name,
1571                     'Fetching precision and mau');
1572   END IF;
1573 
1574   OPEN precision_mau_cur (p_ledger_id);
1575 
1576   FETCH precision_mau_cur INTO x_precision, x_mau;
1577 
1578 
1579   IF precision_mau_cur%NOTFOUND THEN
1580    x_precision := NULL;
1581    x_mau := NULL;
1582   END IF;
1583 
1584   CLOSE precision_mau_cur;
1585 
1586   -- Logging Infra: Procedure level
1587   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1588      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1589                     G_MODULE_NAME || l_procedure_name,
1590                     'Resetting First Party Org context to NULL');
1591   END IF;
1592 
1593 -- This API gets called from the Journal Entry form also. So, resetting
1594 -- the security context to what was set by the form before thsi API
1595 -- got called.
1596 
1597 
1598    IF l_set_security_context_flag = 'Y' Then
1599 
1600       ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1601       --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1602       ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1603    END IF;
1604 
1605   -- Logging Infra: Procedure level
1606   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1607      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1608                     G_MODULE_NAME || l_procedure_name,
1609                     'get_precision_mau(-)');
1610   END IF;
1611 
1612   EXCEPTION
1613     WHEN OTHERS THEN
1614       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1615       x_msg_out := TO_CHAR(SQLCODE);
1616       -- Logging Infra:
1617       IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1618          FND_LOG.STRING(G_LEVEL_STATEMENT,
1619                         G_MODULE_NAME || l_procedure_name,
1620                         TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1621       END IF;
1622       app_exception.raise_exception;
1623 
1624 END get_precision_mau;
1625 
1626 
1627 PROCEDURE get_default_tax_include_flag
1628 (
1629    p_api_version        IN NUMBER  DEFAULT NULL,
1630    p_ledger_id          IN NUMBER,
1631    p_org_id             IN NUMBER,
1632    p_le_id              IN NUMBER,
1633    p_account_value      IN VARCHAR2,
1634    p_tax_type_code      IN VARCHAR2,
1635    x_include_tax_flag       OUT NOCOPY  VARCHAR2,
1636    x_return_status      OUT NOCOPY  VARCHAR2,
1637    x_msg_out            OUT NOCOPY  VARCHAR2
1638 ) IS
1639 
1640 l_include_tax_flag  VARCHAR2(1);
1641 l_return_status     VARCHAR2(1);
1642 l_msg_out           VARCHAR2(30);
1643 l_tax_class         VARCHAR2(30);
1644 
1645 CURSOR rate_level_cur (p_ledger_id              NUMBER,
1646                        p_account_segment_value  VARCHAR2,
1647                        p_tax_class              VARCHAR2) IS
1648   SELECT amt_incl_tax_flag
1649   FROM   zx_account_rates
1650   WHERE  account_segment_value = p_account_segment_value
1651   AND    ledger_id = p_ledger_id
1652   AND    (tax_class = p_tax_class
1653           OR tax_class IS NULL);
1654 
1655 
1656 CURSOR ledger_level_cur (p_ledger_id   NUMBER,
1657                          p_tax_class   VARCHAR2) IS
1658   SELECT amt_incl_tax_flag
1659   FROM   zx_account_rates
1660   WHERE  ledger_id = p_ledger_id
1661   AND    (tax_class = p_tax_class
1662           OR tax_class IS NULL)
1663   AND    account_segment_value IS NULL;
1664 
1665   -- Logging Infrastructure
1666   l_api_name       CONSTANT   VARCHAR2(30) := 'GET_DEFAULT_TAX_INCLUDE_FLAG';
1670   -- Logging Infrastructure
1667   l_api_version    CONSTANT   NUMBER := 1.0;
1668   l_procedure_name CONSTANT   VARCHAR2(30) := 'GET_DEFAULT_TAX_INCLUDE_FLAG';
1669   l_log_msg                   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1671   l_set_security_context_flag VARCHAR2(1);
1672 
1673 BEGIN
1674 
1675   -- Logging Infra: Setting up runtime level
1676   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1677 
1678   -- Logging Infra: Procedure level
1679   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1680      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1681                     G_MODULE_NAME || l_procedure_name,
1682                     'get_default_tax_include_flag(+)');
1683   END IF;
1684 
1685   -- Logging Infra: Break point input parameters
1686   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1687       l_log_msg := 'B: IN: api_version=' || p_api_version ||
1688                    ', ledger_id=' || p_ledger_id ||
1689                    ', org_id=' || p_org_id ||
1690                    ', le_id=' || p_le_id ||
1691                    ', account_value=' || p_account_value ||
1692                    ', tax_type_code=' || p_tax_type_code;
1693       FND_LOG.STRING(G_LEVEL_STATEMENT,
1694                     G_MODULE_NAME || l_procedure_name,
1695                     l_log_msg);
1696   END IF;
1697 
1698   l_return_status := FND_API.G_RET_STS_SUCCESS;
1699 
1700   IF  ZX_SECURITY.g_first_party_org_id is NULL then
1701      l_set_security_context_flag := 'Y';
1702   ELSE
1703      l_set_security_context_flag := 'N';
1704   END IF;
1705 
1706   IF l_set_security_context_flag = 'Y' Then
1707 
1708     -- Logging Infra: Procedure level
1709     IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1710        FND_LOG.STRING(G_LEVEL_PROCEDURE,
1711                       G_MODULE_NAME || l_procedure_name,
1712                       'Setting Security Context');
1713     END IF;
1714 
1715     ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
1716 
1717     IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1718       -- Logging Infra: Statement level
1719       IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1720          FND_LOG.STRING(G_LEVEL_STATEMENT,
1721                         G_MODULE_NAME || l_procedure_name,
1722                         'Error Setting Security Context');
1723       END IF;
1724       Return;
1725     END IF;
1726   END IF; -- l_set_security_context_flag
1727 
1728   -- Logging Infra: Procedure level
1729   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1730      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1731                     G_MODULE_NAME || l_procedure_name,
1732                     'Fetching amount includes tax flag');
1733   END IF;
1734 
1735   IF p_tax_type_code = 'I' THEN
1736      l_tax_class := 'INPUT';
1737   END IF;
1738 
1739   IF p_tax_type_code = 'O' THEN
1740      l_tax_class := 'OUTPUT';
1741   END IF;
1742 
1743   OPEN rate_level_cur (p_ledger_id,
1744                        p_account_value,
1745                        l_tax_class);
1746   FETCH rate_level_cur INTO l_include_tax_flag;
1747 
1748   IF rate_level_cur%NOTFOUND THEN
1749     IF ledger_level_cur%ISOPEN THEN
1750       CLOSE ledger_level_cur;
1751     END IF;
1752 
1753     OPEN ledger_level_cur (p_ledger_id,
1754                            l_tax_class);
1755     FETCH ledger_level_cur INTO l_include_tax_flag;
1756 
1757     IF ledger_level_cur%FOUND THEN
1758       l_return_status := FND_API.G_RET_STS_SUCCESS;
1759     ELSE
1760       l_return_status := FND_API.G_RET_STS_ERROR;
1761       l_msg_out := 'ZX_GL_DEF_INCL_TAX_NOTFOUND';
1762     END IF;
1763 
1764     CLOSE ledger_level_cur;
1765 
1766   END IF;
1767 
1768   CLOSE rate_level_cur;
1769 
1770   x_include_tax_flag := l_include_tax_flag;
1771   x_return_status := l_return_status;
1772   x_msg_out := l_msg_out;
1773 
1774 
1775   IF l_set_security_context_flag = 'Y' Then
1776 
1777     -- Logging Infra: Procedure level
1778     IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1779        FND_LOG.STRING(G_LEVEL_PROCEDURE,
1780                       G_MODULE_NAME || l_procedure_name,
1781                       'Resetting First Party Org context to NULL');
1782     END IF;
1783 
1784     ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1785     --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1786      ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1787   END IF;
1788 
1789   -- Logging Infra: Procedure level
1790   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1791      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1792                     G_MODULE_NAME || l_procedure_name,
1793                     'get_default_tax_include_flag(-)');
1794   END IF;
1795 
1796 EXCEPTION
1797   WHEN OTHERS THEN
1798     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1799     x_msg_out := TO_CHAR(SQLCODE);
1800     -- Logging Infra:
1801     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1802        FND_LOG.STRING(G_LEVEL_STATEMENT,
1803                       G_MODULE_NAME || l_procedure_name,
1804                       TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1805     END IF;
1806     app_exception.raise_exception;
1807 END;
1808 
1809 PROCEDURE get_ledger_controls
1810 (  p_api_version IN  NUMBER DEFAULT NULL,
1811    p_ledger_id   IN  NUMBER,
1812    p_org_id      IN  NUMBER,
1813    p_le_id       IN  NUMBER,
1814    x_calculation_level_code   OUT NOCOPY  VARCHAR2,
1815    x_tax_mau                  OUT NOCOPY  NUMBER,
1816    x_return_status            OUT NOCOPY VARCHAR2,
1817    x_msg_out                  OUT NOCOPY VARCHAR2
1818 ) IS
1819 
1823   l_procedure_name CONSTANT   VARCHAR2(30) := 'GET_LEDGER_CONTROLS';
1820   -- Logging Infrastructure
1821   l_api_name       CONSTANT   VARCHAR2(30) := 'GET_LEDGER_CONTROLS';
1822   l_api_version    CONSTANT   NUMBER := 1.0;
1824   l_log_msg                   FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1825   -- Logging Infrastructure
1826   l_set_security_context_flag VARCHAR2(1);
1827 
1828 BEGIN
1829 
1830   -- Logging Infra: Setting up runtime level
1831   G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1832 
1833   -- Logging Infra: Procedure level
1834   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1835      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1836                     G_MODULE_NAME || l_procedure_name,
1837                     'get_ledger_controls(+)');
1838   END IF;
1839 
1840   -- Logging Infra: Break point input parameters
1841   IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1842       l_log_msg := 'B: IN: api_version=' || p_api_version ||
1843                    ', ledger_id=' || p_ledger_id ||
1844                    ', org_id=' || p_org_id ||
1845                    ', le_id=' || p_le_id;
1846       FND_LOG.STRING(G_LEVEL_STATEMENT,
1847                     G_MODULE_NAME || l_procedure_name,
1848                     l_log_msg);
1849   END IF;
1850 
1851   x_return_status := FND_API.G_RET_STS_SUCCESS;
1852 
1853   IF  ZX_SECURITY.g_first_party_org_id is NULL then
1854      l_set_security_context_flag := 'Y';
1855   ELSE
1856      l_set_security_context_flag := 'N';
1857   END IF;
1858 
1859   IF l_set_security_context_flag = 'Y' Then
1860 
1861      -- Logging Infra: Procedure level
1862      IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1863         FND_LOG.STRING(G_LEVEL_PROCEDURE,
1864                        G_MODULE_NAME || l_procedure_name,
1865                        'Setting Security Context');
1866      END IF;
1867 
1868      ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
1869 
1870      IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1871        -- Logging Infra: Statement level
1872        IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1873           FND_LOG.STRING(G_LEVEL_STATEMENT,
1874                          G_MODULE_NAME || l_procedure_name,
1875                          'Error Setting Security Context');
1876        END IF;
1877        Return;
1878      END IF;
1879   END IF; -- l_set_security_context_flag
1880 
1881   -- Logging Infra: Procedure level
1882   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1883      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1884                     G_MODULE_NAME || l_procedure_name,
1885                     'Fetching calc level code and tax mau');
1886   END IF;
1887 
1888     SELECT calculation_level_code,
1889            decode(tax_mau, NULL, power(10,-1*tax_precision), tax_mau)
1890     INTO x_calculation_level_code,
1891          x_tax_mau
1892     FROM zx_account_rates
1893     WHERE ledger_id = p_ledger_id
1894     AND   account_segment_value IS NULL
1895     AND   rownum = 1;
1896 
1897   IF l_set_security_context_flag = 'Y' Then
1898     -- Logging Infra: Procedure level
1899     IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1900        FND_LOG.STRING(G_LEVEL_PROCEDURE,
1901                       G_MODULE_NAME || l_procedure_name,
1902                       'Resetting First Party Org context to NULL');
1903     END IF;
1904 
1905     ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1906     --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1907      ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1908   END IF;
1909 
1910   -- Logging Infra: Procedure level
1911   IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1912      FND_LOG.STRING(G_LEVEL_PROCEDURE,
1913                     G_MODULE_NAME || l_procedure_name,
1914                     'get_ledger_controls(-)');
1915   END IF;
1916 
1917 EXCEPTION
1918   WHEN NO_DATA_FOUND THEN
1919       x_return_status := FND_API.G_RET_STS_ERROR;
1920       x_msg_out := 'ZX_GL_LEDGER_CONTROLS_NOTFOUND';
1921     -- Logging Infra:
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                       'NO_DATA_FOUND');
1926     END IF;
1927   WHEN OTHERS THEN
1928     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1929     x_msg_out := TO_CHAR(SQLCODE);
1930     -- Logging Infra:
1931     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1932        FND_LOG.STRING(G_LEVEL_STATEMENT,
1933                       G_MODULE_NAME || l_procedure_name,
1934                       TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1935     END IF;
1936     app_exception.raise_exception;
1937 
1938 END;
1939 
1940 
1941 END zx_gl_tax_options_pkg;