[Home] [Help]
PACKAGE BODY: APPS.ZX_GL_TAX_OPTIONS_PKG
Source
1 PACKAGE BODY zx_gl_tax_options_pkg AS
2 /* $Header: zxgltaxoptionb.pls 120.23 2011/07/14 06:57:02 ssanka 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 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 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,
379
376 G_MODULE_NAME || l_procedure_name,
377 'Fetching default tax include flag');
378 END IF;
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
506 | 04/08/2005 Yoshimichi Konishi Created.
503 |
504 |
505 | MODIFICATION HISTORY
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 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_return_status VARCHAR2(1);
544 l_msg_out VARCHAR2(30);
545
546 -- Logging Infra
547 l_api_name CONSTANT VARCHAR2(30) := 'GET_TAX_RATE_AND_ACCOUNT';
548 l_api_version CONSTANT NUMBER := 1.0;
549 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_TAX_RATE_AND_ACCOUNT';
550 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
551 -- Logging Infra
552
553 BEGIN
554 -- Logging Infra: Setting up runtime level
555 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
556
557 -- Logging Infra: Procedure level
558 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
559 FND_LOG.STRING(G_LEVEL_PROCEDURE,
560 G_MODULE_NAME || l_procedure_name,
561 'get_tax_rate_and_account(+)');
562 END IF;
563 -- Logging Infra: Break point input parameters
564 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
565 l_log_msg := 'B: IN: api_version=' || p_api_version ||
566 ', tax_type_code=' || p_tax_type_code ||
567 ', tax_rate_id=' || p_tax_rate_id;
568 FND_LOG.STRING(G_LEVEL_STATEMENT,
569 G_MODULE_NAME || l_procedure_name,
570 l_log_msg);
571 END IF;
572
573 OPEN get_pct_rate_ccid_cur (p_tax_rate_id, p_org_id, p_ledger_id);
574
575 FETCH get_pct_rate_ccid_cur INTO l_pct_rate,
576 l_tax_account_ccid;
577 IF get_pct_rate_ccid_cur%FOUND THEN
578 l_return_status := FND_API.G_RET_STS_SUCCESS;
579 ELSE
580 l_return_status := FND_API.G_RET_STS_ERROR;
581 x_msg_out := 'ZX_GL_OUT_RATE_CCID_NOTFOUND';
582 END IF;
583
584 CLOSE get_pct_rate_ccid_cur;
585
586 -- Logging Infra: Break point get_pct_rate_ccid_cur
587 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
588 l_log_msg := 'B: CUR: get_pct_rate_ccid_cur: pct_rate=' || l_pct_rate ||
589 ', tax_account_ccid=' || l_tax_account_ccid;
590 FND_LOG.STRING(G_LEVEL_STATEMENT,
591 G_MODULE_NAME || l_procedure_name,
592 l_log_msg);
593 END IF;
594
595 x_tax_rate_pct := l_pct_rate;
596 x_tax_account_ccid := l_tax_account_ccid;
597 x_return_status := l_return_status;
598 x_msg_out := l_msg_out;
599
600 -- Logging Infra: Break point output parameters
601 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
602 l_log_msg := 'B: OUT: tax_rate_pct=' || l_pct_rate ||
603 ', tax_account_ccid=' || l_tax_account_ccid ||
604 ', return_status=' || l_return_status;
605 FND_LOG.STRING(G_LEVEL_STATEMENT,
606 G_MODULE_NAME || l_procedure_name,
607 l_log_msg);
608 END IF;
609 -- Logging Infra: Procedure level
610 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
611 FND_LOG.STRING(G_LEVEL_PROCEDURE,
612 G_MODULE_NAME || l_procedure_name,
613 'get_tax_rate_and_account(-)');
614 END IF;
615
616 EXCEPTION
617 WHEN OTHERS THEN
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
619 x_msg_out := TO_CHAR(SQLCODE);
620 -- Logging Infra:
621 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
622 FND_LOG.STRING(G_LEVEL_STATEMENT,
623 G_MODULE_NAME || l_procedure_name,
624 TO_CHAR(SQLCODE) || ': ' || SQLERRM);
625 END IF;
626 app_exception.raise_exception;
627 END get_tax_rate_and_account;
628
629
630 /*===========================================================================+
634 | IN
631 | PROCEDURE
632 | get_tax_ccid
633 |
635 | p_tax_rate_id : Tax Rate ID
636 | p_org_id : Organization ID
637 | p_ledger_id : Ledger ID
638 |
639 | OUT
640 | x_tax_account_ccid : Tax Account CCID
641 | x_return_status : Return Status. See get_default_value for details.
642 | x_msg_out : Output Message. See get_default_value for
643 | details.
644 |
645 | DESCRIPTION
646 | This routine returns tax accounting CCID for a particular tax_rate_id.
647 |
648 |
649 | SCOPE - PUBLIC
650 |
651 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
652 |
653 | CALLED FROM
654 |
655 | NOTES
656 |
657 |
658 | MODIFICATION HISTORY
659 | 04/08/2005 Yoshimichi Konishi Created.
660 |
661 +==========================================================================*/
662 PROCEDURE get_tax_ccid
663 ( p_api_version IN NUMBER,
664 p_tax_rate_id IN NUMBER,
665 p_org_id IN NUMBER,
666 p_ledger_id IN NUMBER,
667 x_tax_account_ccid OUT NOCOPY NUMBER,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_msg_out OUT NOCOPY VARCHAR2
670 ) IS
671
672 -- ***** CURSORS *****
673 CURSOR tax_acct_cur (p_tax_rate_id NUMBER,
674 p_ledger_id NUMBER,
675 p_org_id NUMBER)
676 IS
677 SELECT NVL(tax_account_ccid, non_rec_account_ccid)
678 -- Bug 4766614
679 -- Added NVL so that when tax_account_ccid is null
680 -- the API returns non_rec_account_ccid
681 FROM zx_accounts za, zx_rates_b rates
682 WHERE rates.tax_rate_id = p_tax_rate_id
683 AND za.tax_account_entity_id(+) = rates.tax_rate_id
684 AND za.tax_account_entity_code(+) = 'RATES'
685 AND za.ledger_id(+) = p_ledger_id
686 AND za.internal_organization_id(+) = p_org_id;
687
688 -- ***** VARIABLES *****
689
690 -- Logging Infra
691 l_api_name CONSTANT VARCHAR2(30) := 'GET_TAX_CCID';
692 l_api_version CONSTANT NUMBER := 1.0;
693 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_TAX_CCID';
694 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
695 -- Logging Infra
696
697 BEGIN
698 -- Logging Infra: Setting up runtime level
699 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
700
701 -- Logging Infra: Procedure level
702 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
703 FND_LOG.STRING(G_LEVEL_PROCEDURE,
704 G_MODULE_NAME || l_procedure_name,
705 'get_tax_ccid(+)');
706 END IF;
707
708 -- Logging Infra: Break point input parameters
709 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
710 l_log_msg := 'B: IN: api_version=' || p_api_version ||
711 ', tax_rate_id=' || p_tax_rate_id ||
712 ', org_id=' || p_org_id ||
713 ', ledger_id=' || p_ledger_id;
714 FND_LOG.STRING(G_LEVEL_STATEMENT,
715 G_MODULE_NAME || l_procedure_name,
716 l_log_msg);
717 END IF;
718
719 OPEN tax_acct_cur (p_tax_rate_id,
720 p_ledger_id,
721 p_org_id);
722
723 FETCH tax_acct_cur INTO x_tax_account_ccid;
724
725 IF tax_acct_cur%FOUND THEN
726 x_return_status := FND_API.G_RET_STS_SUCCESS;
727 ELSE
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 x_msg_out := 'ZX_GL_TAXCCID_NOT_FOUND';
730 END IF;
731
732 CLOSE tax_acct_cur;
733
734 -- Logging Infra: Break point output parameters
735 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
736 l_log_msg := 'B: OUT: tax_account_ccid=' || x_tax_account_ccid ||
737 ', return_status=' || x_return_status;
738 FND_LOG.STRING(G_LEVEL_STATEMENT,
739 G_MODULE_NAME || l_procedure_name,
740 l_log_msg);
741 END IF;
742
743 -- Logging Infra: Procedure level
744 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
745 FND_LOG.STRING(G_LEVEL_PROCEDURE,
746 G_MODULE_NAME || l_procedure_name,
747 'get_tax_ccid(-)');
748 END IF;
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753 x_msg_out := TO_CHAR(SQLCODE);
754 -- Logging Infra:
755 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
756 FND_LOG.STRING(G_LEVEL_STATEMENT,
757 G_MODULE_NAME || l_procedure_name,
758 TO_CHAR(SQLCODE) || ': ' || SQLERRM);
759 END IF;
760 app_exception.raise_exception;
761 END get_tax_ccid;
762
763
764 /*===========================================================================+
765 | PROCEDURE
766 | get_tax_rate_id
767 |
768 | IN
769 | p_org_id : Organization ID
770 | p_le_id : Legal Entity ID
771 | p_tax_rate_code : Tax Rate Code
775 | 'T' for newly created Tax Rate Codes
772 | p_trx_date : Transaction Date
773 | p_tax_type_code : 'I' for Input Tax Rate Codes migrated from AP
774 | 'O' for Output Tax Rate Codes migrated from AR
776 |
777 | OUT
778 | p_tax_type_code : 'I' for Input Tax Rate Codes migrated from AP
779 | 'O' for Output Tax Rate Codes migrated from AR
780 | 'T' for newly created Tax Rate Codes
781 | x_tax_rate_id : Tax Rate ID
782 | x_return_status : Return Status. See get_default_value for details.
783 | x_msg_out : Output Message. See get_default_value for details.
784 |
785 | DESCRIPTION
786 | This routine returns tax rate ID for active tax rate code at a particular
787 | point in time.
788 |
789 | SCOPE - PUBLIC
790 |
791 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
792 |
793 | CALLED FROM
794 |
795 | NOTES
796 |
797 |
798 | MODIFICATION HISTORY
799 | 04/08/2005 Yoshimichi Konishi Created.
800 |
801 +==========================================================================*/
802 PROCEDURE get_tax_rate_id
803 ( p_api_version IN NUMBER DEFAULT NULL,
804 p_org_id IN NUMBER,
805 p_le_id IN NUMBER,
806 p_tax_rate_code IN VARCHAR2,
807 p_trx_date IN DATE,
808 p_tax_type_code IN OUT NOCOPY VARCHAR2,
809 x_tax_rate_id OUT NOCOPY NUMBER,
810 x_return_status OUT NOCOPY VARCHAR2,
811 x_msg_out OUT NOCOPY VARCHAR2
812 ) IS
813
814 -- ***** CURSORS *****
815
816 --
817 -- IF tax_type_code IS 'T'
818 -- THEN tax_class is NULL
819 --
820 -- This is the case for GL Tax Options records newly created after migration.
821 --
822 --
823 --
824 CURSOR rate_id_for_null_type_cur (p_tax_rate_code VARCHAR2,
825 p_trx_date DATE)
826 IS
827 SELECT rates.tax_rate_id,
828 rates.tax_class
829 FROM zx_sco_rates rates,
830 zx_taxes_b taxes
831 WHERE rates.tax_rate_code = p_tax_rate_code
832 AND p_trx_date >= rates.effective_from
833 AND p_trx_date <= NVL(rates.effective_to, p_trx_date)
834 AND nvl(rates.active_flag,'Y') = 'Y'
835 AND rates.rate_type_code = 'PERCENTAGE'
836 AND taxes.tax_regime_code = rates.tax_regime_code
837 AND taxes.tax = rates.tax
838 AND taxes.source_tax_flag = 'Y'
839 AND nvl(taxes.offset_tax_flag, 'N') <> 'Y'
840 AND rates.tax_jurisdiction_code IS NULL;
841
842
843 --
844 -- Assumption: IF tax_type_code IS NOT NULL
845 -- THEN tax_class IS NOT NULL
846 --
847 -- This is the case for migrated GL Tax Options records.
848 --
849 CURSOR rate_id_for_mig_cur (p_tax_rate_code VARCHAR2,
850 p_tax_class VARCHAR2,
851 p_trx_date DATE)
852 IS
853 SELECT rates.tax_rate_id
854 FROM zx_sco_rates rates,
855 zx_taxes_b taxes
856 WHERE rates.tax_rate_code = p_tax_rate_code
857 AND p_trx_date >= rates.effective_from
858 AND p_trx_date <= NVL(rates.effective_to, p_trx_date)
859 AND nvl(rates.active_flag,'Y') = 'Y'
863 AND taxes.source_tax_flag = 'Y'
860 AND rates.rate_type_code = 'PERCENTAGE'
861 AND taxes.tax_regime_code = rates.tax_regime_code
862 AND taxes.tax = rates.tax
864 AND nvl(taxes.offset_tax_flag, 'N') <> 'Y'
865 AND rates.tax_jurisdiction_code IS NULL
866 AND rates.tax_class = p_tax_class;
867
868 -- ***** VARIABLES *****
869 l_tax_type_code VARCHAR2(1);
870 l_tax_class VARCHAR2(30);
871 l_tax_rate_id NUMBER(15);
872 l_return_status VARCHAR2(1);
873
874 -- Logging Infra
875 l_api_name CONSTANT VARCHAR2(30) := 'GET_TAX_RATE_ID';
876 l_api_version CONSTANT NUMBER := 1.0;
877 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_TAX_RATE_ID';
878 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
879 -- Logging Infra
880 l_set_security_context_flag VARCHAR2(1);
881
882 BEGIN
883 -- Logging Infra: Setting up runtime level
884 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
885
886 -- Logging Infra: Procedure level
887 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
888 FND_LOG.STRING(G_LEVEL_PROCEDURE,
889 G_MODULE_NAME || l_procedure_name,
890 'get_tax_rate_id(+)');
891 END IF;
892 -- Logging Infra: Break point input parameters
893 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
894 l_log_msg := 'B: IN: api_version=' || p_api_version ||
895 ', org_id=' || p_org_id ||
896 ', tax_rate_code=' || p_tax_rate_code ||
897 ', trx_date=' || p_trx_date ||
898 ', tax_type_code=' || p_tax_type_code;
899 FND_LOG.STRING(G_LEVEL_STATEMENT,
900 G_MODULE_NAME || l_procedure_name,
901 l_log_msg);
902 END IF;
903
904
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
906
907 IF p_tax_rate_code = 'STD_AR_INPUT' THEN
908 x_return_status := FND_API.G_RET_STS_ERROR;
909 x_msg_out := 'ZX_GL_INVALID_TAX_RATE_CODE';
910 -- Logging Infra:
911 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
912 FND_LOG.STRING(G_LEVEL_STATEMENT,
913 G_MODULE_NAME || l_procedure_name,
914 'p_tax_rate_code is STD_AR_INPUT');
915 END IF;
916 Return;
917 END IF;
918
919
920 IF ZX_SECURITY.g_first_party_org_id is NULL THEN
921 l_set_security_context_flag := 'Y';
922 ELSE
923 l_set_security_context_flag := 'N';
924 END IF;
925
926 IF l_set_security_context_flag = 'Y' Then
927
928 -- Logging Infra: Procedure level
929 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
930 FND_LOG.STRING(G_LEVEL_PROCEDURE,
931 G_MODULE_NAME || l_procedure_name,
932 'Setting Security Context');
933 END IF;
934
935 ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
936
937 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
938 -- Logging Infra: Statement level
939 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
940 FND_LOG.STRING(G_LEVEL_STATEMENT,
941 G_MODULE_NAME || l_procedure_name,
942 'Error Setting Security Context');
943 END IF;
944 Return;
945 END IF;
946
947 END IF; -- l_set_security_context_flag
948
949 -- Logging Infra: Procedure level
950 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
951 FND_LOG.STRING(G_LEVEL_PROCEDURE,
952 G_MODULE_NAME || l_procedure_name,
953 'Fetching Tax Rate Id');
954 END IF;
955
956 IF p_tax_type_code IS NULL OR p_tax_type_code = 'T' THEN
957 OPEN rate_id_for_null_type_cur (p_tax_rate_code,
958 p_trx_date);
959 FETCH rate_id_for_null_type_cur INTO l_tax_rate_id,
960 l_tax_class;
961 CLOSE rate_id_for_null_type_cur;
962
963 IF l_tax_class IS NULL THEN
964 p_tax_type_code := 'T';
965 ELSIF l_tax_class = 'INPUT' THEN
966 p_tax_type_code := 'I';
967 ELSIF l_tax_class = 'OUTPUT' THEN
968 p_tax_type_code := 'O';
969 END IF;
970
971 -- Logging Infra: Break point rate_id_for_null_type_cur
972 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
973 l_log_msg := 'B: CUR: rate_id_for_null_type: tax_rate_id=' || l_tax_rate_id ||
974 ', tax_type_code=' || p_tax_type_code;
975 FND_LOG.STRING(G_LEVEL_STATEMENT,
976 G_MODULE_NAME || l_procedure_name,
977 l_log_msg);
978 END IF;
979 ELSIF p_tax_type_code IN ('I', 'O') THEN
980
981 IF p_tax_type_code = 'I' THEN
982 l_tax_class := 'INPUT';
983 ELSIF p_tax_type_code = 'O' THEN
984 l_tax_class := 'OUTPUT';
985 END IF;
986
987 OPEN rate_id_for_mig_cur (p_tax_rate_code,
988 l_tax_class,
989 p_trx_date);
990 FETCH rate_id_for_mig_cur INTO l_tax_rate_id;
991 CLOSE rate_id_for_mig_cur;
992
996 ', tax_type_code=' || p_tax_type_code;
993 -- Logging Infra: Break point rate_id_for_mig_cur
994 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
995 l_log_msg := 'B: CUR: rate_id_for_mig_cur: tax_rate_id=' || l_tax_rate_id ||
997 FND_LOG.STRING(G_LEVEL_STATEMENT,
998 G_MODULE_NAME || l_procedure_name,
999 l_log_msg);
1000 END IF;
1001
1002 END IF;
1003
1004 x_tax_rate_id := l_tax_rate_id;
1005
1006 -- Logging Infra: Break point output parameters
1007 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1008 l_log_msg := 'B: OUT: tax_type_code=' || l_tax_type_code ||
1009 ', tax_rate_id=' || l_tax_rate_id ||
1010 ', return_status=' || x_return_status;
1011 FND_LOG.STRING(G_LEVEL_STATEMENT,
1012 G_MODULE_NAME || l_procedure_name,
1013 l_log_msg);
1014 END IF;
1015
1016 -- Logging Infra: Procedure level
1017 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1018 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1019 G_MODULE_NAME || l_procedure_name,
1020 'Resetting First Party Org context to NULL');
1021 END IF;
1022
1023 IF l_set_security_context_flag = 'Y' Then
1024 ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1025 --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1026 ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1027 END IF;
1028
1029 -- Logging Infra: Procedure level
1030 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1031 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1032 G_MODULE_NAME || l_procedure_name,
1033 'get_tax_rate_id(-)');
1034 END IF;
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1038 x_msg_out := TO_CHAR(SQLCODE);
1039 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1040 FND_LOG.STRING(G_LEVEL_STATEMENT,
1041 G_MODULE_NAME || l_procedure_name,
1042 TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1043 END IF;
1044 app_exception.raise_exception;
1045 END get_tax_rate_id;
1046
1047
1048
1049 /*===========================================================================+
1050 | PROCEDURE
1051 | get_tax_code
1052 |
1053 | IN
1054 |
1055 | OUT
1056 |
1057 | DESCRIPTION
1058 | Will be obsolete.
1059 |
1060 | SCOPE - PUBLIC
1061 |
1062 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1063 |
1064 | CALLED FROM
1065 |
1066 | NOTES
1067 |
1068 |
1069 | MODIFICATION HISTORY
1070 | 04/08/2005 Yoshimichi Konishi Created.
1071 |
1072 +==========================================================================*/
1073 PROCEDURE get_tax_code
1074 ( p_api_version IN NUMBER DEFAULT NULL,
1075 p_org_id IN NUMBER,
1076 p_tax_type_code IN VARCHAR2,
1077 p_tax_rate_id IN NUMBER,
1078 x_tax_rate_code OUT NOCOPY VARCHAR2,
1079 x_return_status OUT NOCOPY VARCHAR2,
1080 x_msg_out OUT NOCOPY VARCHAR2
1081 ) IS
1082 BEGIN
1083 NULL;
1084 END get_tax_code;
1085
1086
1087 /*===========================================================================+
1088 | PROCEDURE
1089 | get_tax_rate_code
1090 |
1091 | IN
1092 | p_tax_type_code : 'I' for Input Tax Rate Codes migrated from AP
1093 | 'O' for Output Tax Rate Codes migrated from AR
1094 | 'T' for newly created Tax Rate Codes
1095 |
1096 | p_tax_rate_id : Tax Rate ID
1097 |
1098 | OUT
1099 | x_tax_rate_code : Tax Rate Code
1100 |
1101 | DESCRIPTION
1102 | This routine returns tax rate code for a particular tax_rate_id.
1103 |
1104 | SCOPE - PUBLIC
1105 |
1106 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1107 |
1108 | CALLED FROM
1109 |
1110 | NOTES
1111 |
1112 |
1113 | MODIFICATION HISTORY
1114 | 04/08/2005 Yoshimichi Konishi Created.
1115 |
1116 +==========================================================================*/
1117 PROCEDURE get_tax_rate_code
1118 ( p_api_version IN NUMBER DEFAULT NULL,
1119 p_tax_type_code IN VARCHAR2,
1120 p_tax_rate_id IN NUMBER,
1121 x_tax_rate_code OUT NOCOPY VARCHAR2,
1122 x_return_status OUT NOCOPY VARCHAR2,
1123 x_msg_out OUT NOCOPY VARCHAR2
1124 ) IS
1125
1126 -- ***** CURSORS *****
1127 CURSOR get_tax_rate_code_cur (p_tax_rate_id NUMBER)
1128 IS
1129 SELECT zrb.tax_rate_code
1130 FROM zx_rates_b zrb
1131 WHERE zrb.tax_rate_id = p_tax_rate_id;
1132
1133 CURSOR get_source_rate_code_cur (p_tax_rate_id NUMBER)
1134 IS
1135 SELECT zrb.tax_rate_code
1136 FROM zx_rates_b zrb
1137 WHERE zrb.source_id = p_tax_rate_id;
1138
1139 -- ***** VARIABLES *****
1140 l_tax_rate_code VARCHAR2(50);
1141 l_tax_rate_id NUMBER(15);
1142 l_return_status VARCHAR2(1);
1143 l_source_indicator VARCHAR2(1);
1144
1145 -- Logging Infra
1146 l_api_name CONSTANT VARCHAR2(30) := 'GET_TAX_RATE_CODE';
1147 l_api_version CONSTANT NUMBER := 1.0;
1151
1148 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_TAX_RATE_CODE';
1149 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1150 -- Logging Infra
1152 BEGIN
1153 -- Logging Infra: Setting up runtime level
1154 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1155
1156 -- Logging Infra: Procedure level
1157 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1158 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1159 G_MODULE_NAME || l_procedure_name,
1160 'get_tax_rate_code(+)');
1161 END IF;
1162 -- Logging Infra: Break point input parameters
1163 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1164 l_log_msg := 'B: IN: api_version=' || p_api_version ||
1165 ', tax_type_code=' || p_tax_type_code ||
1166 ', tax_rate_id=' || p_tax_rate_id;
1167 FND_LOG.STRING(G_LEVEL_STATEMENT,
1168 G_MODULE_NAME || l_procedure_name,
1169 l_log_msg);
1170 END IF;
1171
1172 l_return_status := FND_API.G_RET_STS_SUCCESS;
1173
1174 IF p_tax_type_code = 'I' THEN
1175
1176 OPEN get_source_rate_code_cur (p_tax_rate_id);
1177 FETCH get_source_rate_code_cur INTO l_tax_rate_code;
1178 IF get_source_rate_code_cur%FOUND THEN
1179 l_source_indicator := 'Y';
1180 ELSE
1181 l_source_indicator := 'N';
1182 END IF;
1183 CLOSE get_source_rate_code_cur;
1184
1185 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1186 l_log_msg := 'B: CUR: get_source_rate_code_cur: tax_rate_code=' ||
1187 l_tax_rate_code;
1188 FND_LOG.STRING(G_LEVEL_STATEMENT,
1189 G_MODULE_NAME || l_procedure_name,
1190 l_log_msg);
1191 FND_LOG.STRING(G_LEVEL_STATEMENT,
1192 G_MODULE_NAME || l_procedure_name,
1193 'l_source_indicator : ' || l_source_indicator);
1194
1195 END IF;
1196
1197 ELSIF p_tax_type_code = 'O' or p_tax_type_code = 'T' THEN
1198
1199 l_source_indicator := 'N';
1200
1201 ELSE
1202
1203 l_return_status := FND_API.G_RET_STS_ERROR;
1204 x_msg_out := 'ZX_GL_INVALID_PARAM';
1205 -- Logging Infra:
1206 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1207 l_log_msg := 'B: Unexpected tax_type_code';
1208 FND_LOG.STRING(G_LEVEL_STATEMENT,
1209 G_MODULE_NAME || l_procedure_name,
1210 l_log_msg);
1211 END IF;
1212 Return;
1213
1214 END IF;
1215
1216 IF l_source_indicator = 'N' THEN
1217
1218 OPEN get_tax_rate_code_cur (p_tax_rate_id);
1219 FETCH get_tax_rate_code_cur INTO l_tax_rate_code;
1220 CLOSE get_tax_rate_code_cur;
1221
1222 END IF;
1223
1224 -- Logging Infra: Break point get_tax_rate_code_cur
1225 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1226 l_log_msg := 'B: CUR: get_tax_rate_code_cur: tax_rate_code=' || l_tax_rate_code;
1227 FND_LOG.STRING(G_LEVEL_STATEMENT,
1228 G_MODULE_NAME || l_procedure_name,
1229 l_log_msg);
1230
1231 END IF;
1232
1233 x_tax_rate_code := l_tax_rate_code;
1234 x_return_status := l_return_status;
1235
1236 -- Logging Infra: Break point output parameters
1237 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1238 l_log_msg := 'B: OUT: tax_rate_code=' || l_tax_rate_code ||
1239 ', return_status=' || l_return_status;
1240 FND_LOG.STRING(G_LEVEL_STATEMENT,
1244
1241 G_MODULE_NAME || l_procedure_name,
1242 l_log_msg);
1243 END IF;
1245 -- Logging Infra: Procedure level
1246 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1247 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1248 G_MODULE_NAME || l_procedure_name,
1249 'get_tax_rate_and_account(-)');
1250 END IF;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1255 x_msg_out := TO_CHAR(SQLCODE);
1256 -- Logging Infra:
1257 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1258 FND_LOG.STRING(G_LEVEL_STATEMENT,
1259 G_MODULE_NAME || l_procedure_name,
1260 TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1261 END IF;
1262 app_exception.raise_exception;
1263 END get_tax_rate_code;
1264
1265 /*===========================================================================+
1266 | PROCEDURE
1267 | get_rouding_rule_code
1268 |
1269 | IN
1270 | p_api_version : API Version
1271 | p_ledger_id : Ledger ID
1272 | p_org_id : Org ID
1273 | p_le_id : Legal Entity ID
1274 | p_tax_class : Tax Class/Tax Type
1275 |
1276 | OUT
1277 | x_rouding_rule_code : Tax Rate Code
1278 | x_return_status : Return Status
1279 |
1280 | DESCRIPTION
1281 | This routine returns rounding_rule_code defined for a ledger.
1282 |
1283 | SCOPE - PUBLIC
1284 |
1285 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1286 |
1287 | CALLED FROM
1288 |
1289 | NOTES
1290 |
1291 |
1292 | MODIFICATION HISTORY
1293 | 06/30/2005 Yoshimichi Konishi Created.
1294 |
1295 +==========================================================================*/
1296
1297 PROCEDURE get_rounding_rule_code
1298 ( p_api_version IN NUMBER DEFAULT NULL,
1299 p_ledger_id IN NUMBER,
1300 p_org_id IN NUMBER,
1301 p_le_id IN NUMBER,
1302 p_tax_class IN VARCHAR2,
1303 x_rounding_rule_code OUT NOCOPY VARCHAR2,
1304 x_return_status OUT NOCOPY VARCHAR2,
1305 x_msg_out OUT NOCOPY VARCHAR2
1306 )
1307 IS
1308
1309 l_return_status VARCHAR2(1);
1310 l_tax_class VARCHAR2(30);
1311
1312 CURSOR rounding_rule_cur (p_ledger_id NUMBER,
1313 p_tax_class VARCHAR) IS
1314 SELECT rounding_rule_code
1315 FROM zx_account_rates
1316 WHERE ledger_id = p_ledger_id
1317 AND account_segment_value IS NULL
1318 AND (tax_class = p_tax_class
1319 OR
1320 tax_class IS NULL)
1321 AND content_owner_id = ZX_SECURITY.g_first_party_org_id;
1322
1323 -- Logging Infrastructure
1324 l_api_name CONSTANT VARCHAR2(30) := 'GET_ROUNDING_RULE_CODE';
1325 l_api_version CONSTANT NUMBER := 1.0;
1326 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_ROUNDING_RULE_CODE';
1327 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1328 -- Logging Infrastructure
1329 l_set_security_context_flag VARCHAR2(1);
1330
1331 BEGIN
1332
1333 -- Logging Infra: Setting up runtime level
1334 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1335
1336 -- Logging Infra: Procedure level
1337 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1338 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1339 G_MODULE_NAME || l_procedure_name,
1340 'get_rounding_rule_code(+)');
1341 END IF;
1342
1343 -- Logging Infra: Break point input parameters
1344 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1345 l_log_msg := 'B: IN: api_version=' || p_api_version ||
1346 ', ledger_id=' || p_ledger_id ||
1347 ', org_id=' || p_org_id ||
1348 ', le_id=' || p_le_id ||
1349 ', tax_class=' || p_tax_class;
1350 FND_LOG.STRING(G_LEVEL_STATEMENT,
1351 G_MODULE_NAME || l_procedure_name,
1352 l_log_msg);
1353 END IF;
1354
1355 x_return_status := FND_API.G_RET_STS_SUCCESS;
1356
1357 IF ZX_SECURITY.g_first_party_org_id is NULL then
1358 l_set_security_context_flag := 'Y';
1359 ELSE
1360 l_set_security_context_flag := 'N';
1361 END IF;
1362
1363 IF l_set_security_context_flag = 'Y' Then
1364
1365 -- Logging Infra: Procedure level
1366 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1367 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1368 G_MODULE_NAME || l_procedure_name,
1369 'Setting Security Context');
1370 END IF;
1371
1372 ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
1373
1374 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1375 -- Logging Infra: Statement level
1376 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1377 FND_LOG.STRING(G_LEVEL_STATEMENT,
1378 G_MODULE_NAME || l_procedure_name,
1379 'Error Setting Security Context');
1380 END IF;
1381 Return;
1382 END IF;
1383 END IF; -- l_set_security_context_flag
1384
1388 G_MODULE_NAME || l_procedure_name,
1385 -- Logging Infra: Procedure level
1386 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1387 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1389 'Fetching rounding rule code');
1390 END IF;
1391
1392
1393 IF p_tax_class = 'I' THEN
1394 l_tax_class := 'INPUT';
1395 END IF;
1396
1397 IF p_tax_class = 'O' THEN
1398 l_tax_class := 'OUTPUT';
1399 END IF;
1400
1401 OPEN rounding_rule_cur (p_ledger_id, l_tax_class);
1402 FETCH rounding_rule_cur INTO x_rounding_rule_code;
1403
1404 IF rounding_rule_cur%NOTFOUND THEN
1405 x_rounding_rule_code := NULL;
1406 END IF;
1407
1408 CLOSE rounding_rule_cur;
1409
1410
1411 IF l_set_security_context_flag = 'Y' Then
1412
1413 -- Logging Infra: Procedure level
1414 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1415 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1416 G_MODULE_NAME || l_procedure_name,
1417 'Resetting First Party Org context to NULL');
1418 END IF;
1419
1420 ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1421 --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1422 ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1423 END IF;
1424
1425 -- Logging Infra: Procedure level
1426 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1427 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1428 G_MODULE_NAME || l_procedure_name,
1429 'get_rounding_rule_code(-)');
1430 END IF;
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1435 x_msg_out := TO_CHAR(SQLCODE);
1436 -- Logging Infra:
1437 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1438 FND_LOG.STRING(G_LEVEL_STATEMENT,
1439 G_MODULE_NAME || l_procedure_name,
1440 TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1441 END IF;
1442 app_exception.raise_exception;
1443
1444 END get_rounding_rule_code;
1445
1446
1447 /*===========================================================================+
1448 | PROCEDURE
1449 | get_precision_mau
1450 |
1451 | IN
1452 | p_ledger_id : Ledger ID
1453 | p_org_id : Org ID
1454 | p_le_id : Legal Entity ID
1455 |
1456 | OUT
1457 | x_precision : Precision
1458 | x_mau : Minimum accountable unit
1459 |
1460 | DESCRIPTION
1461 | This routine returns rounding_rule_code defined for a ledger.
1462 |
1463 | SCOPE - PUBLIC
1464 |
1465 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1466 |
1467 | CALLED FROM
1468 |
1469 | NOTES
1470 |
1471 |
1472 | MODIFICATION HISTORY
1473 | 06/30/2005 Yoshimichi Konishi Created.
1474 |
1475 +==========================================================================*/
1476 PROCEDURE get_precision_mau
1477 ( p_api_version IN NUMBER DEFAULT NULL,
1478 p_ledger_id IN NUMBER,
1479 p_org_id IN NUMBER,
1480 p_le_id IN NUMBER,
1481 x_precision OUT NOCOPY NUMBER,
1482 x_mau OUT NOCOPY NUMBER,
1483 x_return_status OUT NOCOPY VARCHAR2,
1484 x_msg_out OUT NOCOPY VARCHAR2
1485 ) AS
1486
1487
1488 l_return_status VARCHAR2(1);
1489 l_first_pty_org_id NUMBER;
1490
1491
1492 CURSOR precision_mau_cur (p_ledger_id NUMBER)
1493 IS
1494 SELECT tax_precision,
1495 tax_mau
1496 FROM zx_account_rates
1497 WHERE ledger_id = p_ledger_id
1498 AND account_segment_value IS NULL
1499 AND rownum = 1;
1500
1501 -- Logging Infrastructure
1502 l_api_name CONSTANT VARCHAR2(30) := 'GET_PRECISION_MAU';
1503 l_api_version CONSTANT NUMBER := 1.0;
1504 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_PRECISION_MAU';
1505 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1506 -- Logging Infrastructure
1507 l_set_security_context_flag VARCHAR2(1);
1508
1509 BEGIN
1510
1511 -- Logging Infra: Setting up runtime level
1512 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1513
1514 -- Logging Infra: Procedure level
1515 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1516 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1517 G_MODULE_NAME || l_procedure_name,
1518 'get_precision_mau(+)');
1519 END IF;
1520
1521 -- Logging Infra: Break point input parameters
1522 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1523 l_log_msg := 'B: IN: api_version=' || p_api_version ||
1524 ', ledger_id=' || p_ledger_id ||
1525 ', org_id=' || p_org_id ||
1526 ', le_id=' || p_le_id;
1527 FND_LOG.STRING(G_LEVEL_STATEMENT,
1528 G_MODULE_NAME || l_procedure_name,
1529 l_log_msg);
1530 END IF;
1531
1532 x_return_status := FND_API.G_RET_STS_SUCCESS;
1533
1534 -- Logging Infra: Procedure level
1535 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1536 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1537 G_MODULE_NAME || l_procedure_name,
1538 'Setting Security Context');
1539 END IF;
1540
1541 IF ZX_SECURITY.g_first_party_org_id is NULL then
1542 l_set_security_context_flag := 'Y';
1546
1543 ELSE
1544 l_set_security_context_flag := 'N';
1545 END IF;
1547 IF l_set_security_context_flag = 'Y' Then
1548
1549 -- l_first_pty_org_id := ZX_SECURITY.G_FIRST_PARTY_ORG_ID;
1550 --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1551 --ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1552
1553 ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
1554
1555 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1556 -- Logging Infra: Statement level
1557 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1558 FND_LOG.STRING(G_LEVEL_STATEMENT,
1559 G_MODULE_NAME || l_procedure_name,
1560 'Error Setting Security Context');
1561 END IF;
1562 Return;
1563 END IF;
1564 END IF; -- l_set_security_context_flag
1565
1566 -- Logging Infra: Procedure level
1567 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1568 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1569 G_MODULE_NAME || l_procedure_name,
1570 'Fetching precision and mau');
1571 END IF;
1572
1573 OPEN precision_mau_cur (p_ledger_id);
1574
1575 FETCH precision_mau_cur INTO x_precision, x_mau;
1576
1577
1578 IF precision_mau_cur%NOTFOUND THEN
1579 x_precision := NULL;
1580 x_mau := NULL;
1581 END IF;
1582
1583 CLOSE precision_mau_cur;
1584
1585 -- Logging Infra: Procedure level
1586 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1587 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1588 G_MODULE_NAME || l_procedure_name,
1589 'Resetting First Party Org context to NULL');
1590 END IF;
1591
1592 -- This API gets called from the Journal Entry form also. So, resetting
1593 -- the security context to what was set by the form before thsi API
1594 -- got called.
1595
1596
1597 IF l_set_security_context_flag = 'Y' Then
1598
1599 ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1600 --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1601 ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1602 END IF;
1603
1604 -- Logging Infra: Procedure level
1605 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1606 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1607 G_MODULE_NAME || l_procedure_name,
1608 'get_precision_mau(-)');
1609 END IF;
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1614 x_msg_out := TO_CHAR(SQLCODE);
1615 -- Logging Infra:
1616 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1617 FND_LOG.STRING(G_LEVEL_STATEMENT,
1618 G_MODULE_NAME || l_procedure_name,
1619 TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1620 END IF;
1621 app_exception.raise_exception;
1622
1623 END get_precision_mau;
1624
1625
1626 PROCEDURE get_default_tax_include_flag
1627 (
1628 p_api_version IN NUMBER DEFAULT NULL,
1629 p_ledger_id IN NUMBER,
1630 p_org_id IN NUMBER,
1631 p_le_id IN NUMBER,
1632 p_account_value IN VARCHAR2,
1633 p_tax_type_code IN VARCHAR2,
1637 ) IS
1634 x_include_tax_flag OUT NOCOPY VARCHAR2,
1635 x_return_status OUT NOCOPY VARCHAR2,
1636 x_msg_out OUT NOCOPY VARCHAR2
1638
1639 l_include_tax_flag VARCHAR2(1);
1640 l_return_status VARCHAR2(1);
1641 l_msg_out VARCHAR2(30);
1642 l_tax_class VARCHAR2(30);
1643
1644 CURSOR rate_level_cur (p_ledger_id NUMBER,
1645 p_account_segment_value VARCHAR2,
1646 p_tax_class VARCHAR2) IS
1647 SELECT amt_incl_tax_flag
1648 FROM zx_account_rates
1649 WHERE account_segment_value = p_account_segment_value
1650 AND ledger_id = p_ledger_id
1651 AND (tax_class = p_tax_class
1652 OR tax_class IS NULL);
1653
1654
1655 CURSOR ledger_level_cur (p_ledger_id NUMBER,
1656 p_tax_class VARCHAR2) IS
1657 SELECT amt_incl_tax_flag
1658 FROM zx_account_rates
1659 WHERE ledger_id = p_ledger_id
1660 AND (tax_class = p_tax_class
1661 OR tax_class IS NULL)
1662 AND account_segment_value IS NULL;
1663
1664 -- Logging Infrastructure
1665 l_api_name CONSTANT VARCHAR2(30) := 'GET_DEFAULT_TAX_INCLUDE_FLAG';
1666 l_api_version CONSTANT NUMBER := 1.0;
1667 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_DEFAULT_TAX_INCLUDE_FLAG';
1668 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1669 -- Logging Infrastructure
1670 l_set_security_context_flag VARCHAR2(1);
1671
1672 BEGIN
1673
1674 -- Logging Infra: Setting up runtime level
1675 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1676
1677 -- Logging Infra: Procedure level
1678 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1679 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1680 G_MODULE_NAME || l_procedure_name,
1681 'get_default_tax_include_flag(+)');
1682 END IF;
1683
1684 -- Logging Infra: Break point input parameters
1685 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1686 l_log_msg := 'B: IN: api_version=' || p_api_version ||
1687 ', ledger_id=' || p_ledger_id ||
1688 ', org_id=' || p_org_id ||
1689 ', le_id=' || p_le_id ||
1690 ', account_value=' || p_account_value ||
1691 ', tax_type_code=' || p_tax_type_code;
1692 FND_LOG.STRING(G_LEVEL_STATEMENT,
1693 G_MODULE_NAME || l_procedure_name,
1694 l_log_msg);
1695 END IF;
1696
1697 l_return_status := FND_API.G_RET_STS_SUCCESS;
1698
1699 IF ZX_SECURITY.g_first_party_org_id is NULL then
1700 l_set_security_context_flag := 'Y';
1701 ELSE
1702 l_set_security_context_flag := 'N';
1703 END IF;
1704
1705 IF l_set_security_context_flag = 'Y' Then
1706
1707 -- Logging Infra: Procedure level
1708 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1709 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1710 G_MODULE_NAME || l_procedure_name,
1711 'Setting Security Context');
1712 END IF;
1713
1714 ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
1715
1716 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1717 -- Logging Infra: Statement level
1718 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1719 FND_LOG.STRING(G_LEVEL_STATEMENT,
1720 G_MODULE_NAME || l_procedure_name,
1721 'Error Setting Security Context');
1722 END IF;
1723 Return;
1724 END IF;
1725 END IF; -- l_set_security_context_flag
1726
1727 -- Logging Infra: Procedure level
1728 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1729 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1730 G_MODULE_NAME || l_procedure_name,
1731 'Fetching amount includes tax flag');
1732 END IF;
1733
1734 IF p_tax_type_code = 'I' THEN
1735 l_tax_class := 'INPUT';
1736 END IF;
1737
1738 IF p_tax_type_code = 'O' THEN
1739 l_tax_class := 'OUTPUT';
1740 END IF;
1741
1742 OPEN rate_level_cur (p_ledger_id,
1743 p_account_value,
1744 l_tax_class);
1745 FETCH rate_level_cur INTO l_include_tax_flag;
1746
1747 IF rate_level_cur%NOTFOUND THEN
1748 IF ledger_level_cur%ISOPEN THEN
1749 CLOSE ledger_level_cur;
1750 END IF;
1751
1752 OPEN ledger_level_cur (p_ledger_id,
1753 l_tax_class);
1754 FETCH ledger_level_cur INTO l_include_tax_flag;
1755
1756 IF ledger_level_cur%FOUND THEN
1757 l_return_status := FND_API.G_RET_STS_SUCCESS;
1758 ELSE
1759 l_return_status := FND_API.G_RET_STS_ERROR;
1760 l_msg_out := 'ZX_GL_DEF_INCL_TAX_NOTFOUND';
1761 END IF;
1762
1763 CLOSE ledger_level_cur;
1764
1765 END IF;
1766
1767 CLOSE rate_level_cur;
1768
1769 x_include_tax_flag := l_include_tax_flag;
1770 x_return_status := l_return_status;
1771 x_msg_out := l_msg_out;
1772
1773
1774 IF l_set_security_context_flag = 'Y' Then
1775
1776 -- Logging Infra: Procedure level
1777 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1781 END IF;
1778 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1779 G_MODULE_NAME || l_procedure_name,
1780 'Resetting First Party Org context to NULL');
1782
1783 ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1784 --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1785 ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1786 END IF;
1787
1788 -- Logging Infra: Procedure level
1789 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1790 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1791 G_MODULE_NAME || l_procedure_name,
1792 'get_default_tax_include_flag(-)');
1793 END IF;
1794
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1798 x_msg_out := TO_CHAR(SQLCODE);
1799 -- Logging Infra:
1800 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1801 FND_LOG.STRING(G_LEVEL_STATEMENT,
1802 G_MODULE_NAME || l_procedure_name,
1803 TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1804 END IF;
1805 app_exception.raise_exception;
1806 END;
1807
1808 PROCEDURE get_ledger_controls
1809 ( p_api_version IN NUMBER DEFAULT NULL,
1810 p_ledger_id IN NUMBER,
1811 p_org_id IN NUMBER,
1812 p_le_id IN NUMBER,
1813 x_calculation_level_code OUT NOCOPY VARCHAR2,
1814 x_tax_mau OUT NOCOPY NUMBER,
1815 x_return_status OUT NOCOPY VARCHAR2,
1816 x_msg_out OUT NOCOPY VARCHAR2
1817 ) IS
1818
1819 -- Logging Infrastructure
1820 l_api_name CONSTANT VARCHAR2(30) := 'GET_LEDGER_CONTROLS';
1821 l_api_version CONSTANT NUMBER := 1.0;
1822 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_LEDGER_CONTROLS';
1823 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1824 -- Logging Infrastructure
1825 l_set_security_context_flag VARCHAR2(1);
1826
1827 BEGIN
1828
1829 -- Logging Infra: Setting up runtime level
1830 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1831
1832 -- Logging Infra: Procedure level
1833 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1834 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1835 G_MODULE_NAME || l_procedure_name,
1836 'get_ledger_controls(+)');
1837 END IF;
1838
1839 -- Logging Infra: Break point input parameters
1840 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1841 l_log_msg := 'B: IN: api_version=' || p_api_version ||
1842 ', ledger_id=' || p_ledger_id ||
1843 ', org_id=' || p_org_id ||
1844 ', le_id=' || p_le_id;
1845 FND_LOG.STRING(G_LEVEL_STATEMENT,
1846 G_MODULE_NAME || l_procedure_name,
1847 l_log_msg);
1848 END IF;
1849
1850 x_return_status := FND_API.G_RET_STS_SUCCESS;
1851
1852 IF ZX_SECURITY.g_first_party_org_id is NULL then
1853 l_set_security_context_flag := 'Y';
1854 ELSE
1855 l_set_security_context_flag := 'N';
1856 END IF;
1857
1858 IF l_set_security_context_flag = 'Y' Then
1859
1860 -- Logging Infra: Procedure level
1861 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1862 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1863 G_MODULE_NAME || l_procedure_name,
1864 'Setting Security Context');
1865 END IF;
1866
1867 ZX_SECURITY.set_security_context(p_le_id, p_org_id, NULL, x_return_status);
1868
1869 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1870 -- Logging Infra: Statement level
1871 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1872 FND_LOG.STRING(G_LEVEL_STATEMENT,
1873 G_MODULE_NAME || l_procedure_name,
1874 'Error Setting Security Context');
1875 END IF;
1876 Return;
1877 END IF;
1878 END IF; -- l_set_security_context_flag
1879
1880 -- Logging Infra: Procedure level
1881 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1882 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1883 G_MODULE_NAME || l_procedure_name,
1884 'Fetching calc level code and tax mau');
1885 END IF;
1886
1887 SELECT calculation_level_code,
1888 decode(tax_mau, NULL, power(10,-1*tax_precision), tax_mau)
1889 INTO x_calculation_level_code,
1890 x_tax_mau
1891 FROM zx_account_rates
1892 WHERE ledger_id = p_ledger_id
1893 AND account_segment_value IS NULL
1894 AND rownum = 1;
1895
1896 IF l_set_security_context_flag = 'Y' Then
1897 -- Logging Infra: Procedure level
1898 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1902 END IF;
1899 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1900 G_MODULE_NAME || l_procedure_name,
1901 'Resetting First Party Org context to NULL');
1903
1904 ZX_SECURITY.G_FIRST_PARTY_ORG_ID := NULL;
1905 --dbms_session.set_context('my_ctx','FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1906 ZX_SECURITY.name_value('FIRSTPTYORGID',to_char(ZX_SECURITY.G_FIRST_PARTY_ORG_ID));
1907 END IF;
1908
1909 -- Logging Infra: Procedure level
1910 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1911 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1912 G_MODULE_NAME || l_procedure_name,
1913 'get_ledger_controls(-)');
1914 END IF;
1915
1916 EXCEPTION
1917 WHEN NO_DATA_FOUND THEN
1918 x_return_status := FND_API.G_RET_STS_ERROR;
1919 x_msg_out := 'ZX_GL_LEDGER_CONTROLS_NOTFOUND';
1920 -- Logging Infra:
1921 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1922 FND_LOG.STRING(G_LEVEL_STATEMENT,
1923 G_MODULE_NAME || l_procedure_name,
1924 'NO_DATA_FOUND');
1925 END IF;
1926 WHEN OTHERS THEN
1927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1928 x_msg_out := TO_CHAR(SQLCODE);
1929 -- Logging Infra:
1930 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1931 FND_LOG.STRING(G_LEVEL_STATEMENT,
1932 G_MODULE_NAME || l_procedure_name,
1933 TO_CHAR(SQLCODE) || ': ' || SQLERRM);
1934 END IF;
1935 app_exception.raise_exception;
1936
1937 END;
1938
1939
1940 END zx_gl_tax_options_pkg;