1 package body ZX_AR_TAX_CLASSIFICATN_DEF_PKG as
2 /* $Header: zxartxclsdefpkgb.pls 120.33 2011/01/19 19:13:25 ssanka ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8
9 -- global variable to hold org_id of PA or AR
10 g_org_id NUMBER;
11
12 PG_DEBUG VARCHAR2(1);
13 TAX_CODE_DEFAULT_EXTENSION VARCHAR2(1) := NULL;
14 c INTEGER;
15 rows INTEGER;
16 statement VARCHAR2(2000);
17 dummy VARCHAR2(25);
18 pg_tax_rate_passed ar_vat_tax.tax_rate%TYPE;
19 pg_adhoc_tax_code VARCHAR2(1);
20 curridx INTEGER := 1;
21 buf VARCHAR2(2160) := NULL;
22
23 type tab_ids is table of number index by binary_integer;
24 type tab_errors is table of varchar2(2000) index by binary_integer;
25 pg_error_id_tab tab_ids;
26 pg_error_msg_tab tab_errors;
27 pg_err_ins_ind binary_integer := 0;
28 pg_err_get_ind binary_integer := 0;
29
30 type tab_num_type is table of number index by binary_integer;
31 type tab_code_type is table of varchar2(15) index by binary_integer;
32 pg_max_p_mau_index INTEGER := 0;
33 pg_currency_code_tab tab_code_type;
34 pg_precision_tab tab_num_type;
35 pg_min_acct_unit_tab tab_num_type;
36 pg_batch_tax_rate_rule ra_batch_sources.invalid_tax_rate_rule%TYPE;
37
38 --
39 -- Forward declarations
40 --
41
42 FUNCTION get_site_tax(
43 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
44 p_trx_date IN ra_customer_trx.trx_date%TYPE)
45 RETURN VARCHAR2;
46
47 FUNCTION get_customer_tax(
48 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
49 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
50 p_trx_date IN ra_customer_trx.trx_date%TYPE )
51 RETURN VARCHAR2;
52
53 FUNCTION get_item_tax(
54 p_item_id IN mtl_system_items.inventory_item_id%TYPE,
55 p_organization_id IN mtl_system_items.organization_id%TYPE,
56 p_trx_date IN DATE,
57 p_memo_line_id IN ar_memo_lines.memo_line_id%TYPE default null)
58 RETURN VARCHAR2;
59 /*******
60 -- Bug#3945805
61 FUNCTION get_location_tax(
62 p_product IN VARCHAR2,
63 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
64 p_party_flag IN VARCHAR2,
65 p_party_location_id IN hz_locations.location_id%type default null)
66 RETURN VARCHAR2;
67
68 ******/
69 FUNCTION get_natural_acct_tax (
70 p_ccid IN NUMBER,
71 p_internal_organization_id IN NUMBER,
72 p_set_of_books_id IN ar_system_parameters.set_of_books_id%TYPE,
73 p_trx_date IN ra_customer_trx.trx_date%TYPE,
74 p_check_override_only IN VARCHAR2)
75 RETURN VARCHAR2;
76
77
78 PROCEDURE pop_pa_tax_info(p_internal_organization_id IN NUMBER,
79 p_application_id IN NUMBER,
80 p_return_status OUT NOCOPY VARCHAR2);
81
82 PROCEDURE pop_ar_tax_info(p_internal_organization_id IN NUMBER,
83 p_application_id IN NUMBER,
84 p_return_status OUT NOCOPY VARCHAR2);
85
86 PROCEDURE pop_ar_system_param_info(p_internal_organization_id IN NUMBER,
87 p_return_status OUT NOCOPY VARCHAR2);
88
89 /*----------------------------------------------------------------------------*
90 |Public Procedure |
91 | get_project_tax |
92 | |
93 |Description |
94 | get tax code associated with a project. |
95 | |
96 |Called From |
97 | get_pa_default_classification |
98 | |
99 |History |
100 | 28-OCT-98 TKOSHIO CREATED |
101 | 22-Jun-04 Sudhir Sekuri Bugfix 3611046 |
102 *----------------------------------------------------------------------------*/
103
104 FUNCTION get_project_tax(p_project_id IN NUMBER,
105 p_trx_date IN DATE,
106 p_retention_flag IN BOOLEAN DEFAULT FALSE) return VARCHAR2 IS
107 -- Bug 2355866
108 l_retention_flag varchar2(10) := NULL ;
109
110 CURSOR tax_csr (c_project_id NUMBER,
111 c_retention_flag VARCHAR2,
112 c_trx_date DATE,
113 c_org_id NUMBER) IS
114 SELECT p.output_tax_code,
115 p.retention_tax_code
116 -- FROM fnd_lookups l, pa_projects p bug#4574838
117 FROM zx_output_classifications_v l,
118 pa_projects p
119 WHERE p.project_id = c_project_id
120 AND l.lookup_code = decode(c_retention_flag,'TRUE',p.retention_tax_code,p.output_tax_code)
121 AND l.org_id IN (c_org_id, -99)
122 AND l.enabled_flag = 'Y'
123 AND (l.start_date_active <= c_trx_date OR
124 l.start_date_active is null)
125 AND (l.end_date_active >= c_trx_date OR
126 l.end_date_active is null)
127 AND rownum = 1
128 ORDER BY l.org_id desc;
129 -- AND l.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS';
130
131 l_tax_csr_rec tax_csr%rowtype;
132
133
134 --l_tax_classification_code varchar2(30) := NULL;
135 -- Bug#4574838
136 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
137
138 BEGIN
139
140 IF (g_level_statement >= g_current_runtime_level ) THEN
141 FND_LOG.STRING(g_level_statement,
142 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_project_tax.BEGIN',
143 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_project_tax(+)');
144 END IF;
145
146 l_tax_classification_code := NULL;
147
148 if p_retention_flag then
149 l_retention_flag := 'TRUE';
150 else
151 l_retention_flag := 'FALSE';
152 end if;
153
154 l_tax_csr_rec.output_tax_code := NULL;
155 l_tax_csr_rec.retention_tax_code := NULL;
156
157 --
158 -- Bug#5331994- add trx_date and org_id
159 --
160 open tax_csr(p_project_id,
161 l_retention_flag,
162 p_trx_date,
163 g_org_id);
164 fetch tax_csr into l_tax_csr_rec;
165 close tax_csr;
166
167 if p_retention_flag then
168 l_tax_classification_code := l_tax_csr_rec.retention_tax_code;
169 else
170 l_tax_classification_code := l_tax_csr_rec.output_tax_code;
171 end if;
172
173 IF (g_level_statement >= g_current_runtime_level ) THEN
174 FND_LOG.STRING(g_level_statement,
175 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_project_tax',
176 'tax_classificaton_code => '||l_tax_classification_code);
177 FND_LOG.STRING(g_level_statement,
178 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_project_tax.END',
179 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_project_tax(-)');
180 END IF;
181
182
183 return l_tax_classification_code;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
188 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_project_tax- '||
189 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
190 FND_MSG_PUB.Add;
191
192 IF (g_level_unexpected >= g_current_runtime_level ) THEN
193 FND_LOG.STRING(g_level_unexpected,
194 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_project_tax',
195 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
196 END IF;
197 if tax_csr%isopen then
198 close tax_csr;
199 end if;
200 RAISE;
201
202
203 END get_project_tax;
204
205 /*----------------------------------------------------------------------------*
206 |Public Procedure |
207 | get_expenditure_tax |
208 | |
209 |Description |
210 | get tax code associated with a expenditure. |
211 | |
212 |Called From |
213 | get_pa_default_classification |
214 | |
215 |History |
216 | 28-OCT-98 TKOSHIO CREATED |
217 | 22-Jun-04 Sudhir Sekuri Bugfix 3611046 |
218 *----------------------------------------------------------------------------*/
219
220 FUNCTION get_expenditure_tax(
221 p_expenditure_item_id IN NUMBER,
222 p_trx_date IN DATE) return VARCHAR2 IS
223
224 -- NOTE: OUTPUT_VAT_TAX_ID column in PA_EXPENDITURE_TYPE_OUS should be replaced
225 -- with tax classification code and the following query needs
226 -- to replace output_vat_tax_id with the replaced column
227
228 --
229 -- Bug#4520804
230 --
231 CURSOR tax_csr
232 (c_expenditure_item_id NUMBER,
233 c_org_id NUMBER,
234 c_trx_date DATE) IS
235 SELECT l.lookup_code
236 FROM zx_output_classifications_v l,
237 -- fnd_lookups l, bug#4574838
238 pa_expenditure_type_ous_all type,
239 pa_expenditure_items_all item
240 WHERE item.expenditure_item_id = c_expenditure_item_id
241 AND item.expenditure_type = type.expenditure_type
242 AND item.org_id = c_org_id
243 AND item.org_id = type.org_id
244 AND l.lookup_code = type.output_tax_classification_code
245 AND l.org_id IN (c_org_id, -99)
246 AND l.enabled_flag = 'Y'
247 AND (l.start_date_active <= c_trx_date OR
248 l.start_date_active is null)
249 AND (l.end_date_active >= c_trx_date OR
250 l.end_date_active is null)
251 AND rownum = 1
252 ORDER BY l.org_id desc
253 ;
254
255 l_output_tax_code zx_lines_det_factors.output_tax_classification_code%TYPE;
256
257 BEGIN
258
259 IF (g_level_statement >= g_current_runtime_level ) THEN
260 FND_LOG.STRING(g_level_statement,
261 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_expenditure_tax.BEGIN',
262 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_expenditure_tax(+)');
263 END IF;
264
265 --
266 -- Bug#5331994- add trx_date
267 --
268 open tax_csr(p_expenditure_item_id,
269 sysinfo.pa_product_options_rec.org_id,
270 p_trx_date);
271 fetch tax_csr into l_output_tax_code;
272 close tax_csr;
273
274 IF (g_level_statement >= g_current_runtime_level ) THEN
275 FND_LOG.STRING(g_level_statement,
276 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_expenditure_tax',
277 'tax_code => '||l_output_tax_code);
278 FND_LOG.STRING(g_level_statement,
279 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_expenditure_tax.END',
280 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_expenditure_tax(-)');
281 END IF;
282
283
284 return l_output_tax_code;
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
289 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_expenditure_tax- '||
290 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
291 FND_MSG_PUB.Add;
292
293 IF (g_level_unexpected >= g_current_runtime_level ) THEN
294 FND_LOG.STRING(g_level_unexpected,
295 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_expenditure_tax',
296 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
297 END IF;
298 if tax_csr%isopen then
299 close tax_csr;
300 end if;
301 RAISE;
302
303 END get_expenditure_tax;
304
305 /*----------------------------------------------------------------------------*
306 |Public Procedure |
307 | get_event_tax |
308 | |
309 |Description |
310 | get tax code associated with a project. |
311 | |
312 |Called From |
313 | get_pa_default_classification |
314 | |
315 |History |
316 | 28-OCT-98 TKOSHIO CREATED |
317 *----------------------------------------------------------------------------*/
318
319 FUNCTION get_event_tax(p_event_id IN NUMBER) return VARCHAR2 IS
320
321 CURSOR tax_csr (c_event_id NUMBER) IS
322 SELECT ev.tax_code
323 FROM pa_event_output_tax ev
324 WHERE ev.event_id = c_event_id;
325
326
327 -- l_output_tax_code varchar2(50) := NULL; bug#4574838
328
329 l_output_tax_code zx_lines_det_factors.output_tax_classification_code%TYPE;
330 BEGIN
331
332 IF (g_level_statement >= g_current_runtime_level ) THEN
333 FND_LOG.STRING(g_level_statement,
334 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_event_tax.BEGIN',
335 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_event_tax(+)');
336 END IF;
337
338 l_output_tax_code := NULL;
339
340 open tax_csr(p_event_id);
341 fetch tax_csr into l_output_tax_code;
342 close tax_csr;
343
344 IF (g_level_statement >= g_current_runtime_level ) THEN
345 FND_LOG.STRING(g_level_statement,
346 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_event_tax',
347 'tax_code => '||l_output_tax_code);
348 FND_LOG.STRING(g_level_statement,
349 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_event_tax.END',
350 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_event_tax(-)');
351 END IF;
352
353
354 return l_output_tax_code;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
359 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_event_tax- '||
360 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
361 FND_MSG_PUB.Add;
362
363 IF (g_level_unexpected >= g_current_runtime_level ) THEN
364 FND_LOG.STRING(g_level_unexpected,
365 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_event_tax',
366 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
367 END IF;
368 if tax_csr%isopen then
369 close tax_csr;
370 end if;
371 RAISE;
372
373
374 END get_event_tax;
375
376 /*----------------------------------------------------------------------------*
377 | PUBLIC PROCEDURE |
378 | get_pa_default_classification |
379 | |
380 | DESCRIPTION |
381 | Returns default tax code for Project Accounting's Draft invoice. |
382 | |
383 | SCOPE: Public |
384 | |
385 | CALLED FROM: |
386 | Project Accounting's Tax Defaulting api |
387 | |
388 | HISTORY |
389 | 28-NOV-98 TKOSHIO Created |
390 | 12-JUL-01 PLA Bugfix# 1810878- if no tax code specified at |
391 | the ship to site, use tax code at the bill |
392 | to site |
393 | 12-FEB-2003 Octavio Pedregal Additional call to get_pa_default_tax_code |
394 | for Tax engine changes due to customer |
395 | relationship support in pa. Bugfix 2759960 |
396 | 22-Jun-04 Sudhir Sekuri Bugfix 3611046 |
397 *----------------------------------------------------------------------------*/
398
399 PROCEDURE get_pa_default_classification
400 (p_project_id IN NUMBER
401 ,p_customer_id IN NUMBER
402 ,p_ship_to_site_use_id IN NUMBER
403 ,p_bill_to_site_use_id IN NUMBER
404 ,p_set_of_books_id IN NUMBER
405 ,p_event_id IN NUMBER
406 ,p_expenditure_item_id IN NUMBER
407 ,p_line_type IN VARCHAR2
408 ,p_request_id IN NUMBER
409 ,p_user_id IN NUMBER
410 ,p_trx_date IN DATE
411 ,p_tax_classification_code OUT NOCOPY VARCHAR2
412 ,p_application_id IN NUMBER
413 ,p_internal_organization_id IN NUMBER) IS
414
415 BEGIN
416
417 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
418
419 IF (g_level_statement >= g_current_runtime_level ) THEN
420 FND_LOG.STRING(g_level_statement,
421 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification.BEGIN',
422 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_pa_default_classification(+)');
423 END IF;
424
425 get_pa_default_classification
426 (p_project_id => p_project_id
427 ,p_project_customer_id => p_customer_id
428 ,p_ship_to_site_use_id => p_ship_to_site_use_id
429 ,p_bill_to_site_use_id => p_bill_to_site_use_id
430 ,p_set_of_books_id => p_set_of_books_id
431 ,p_event_id => p_event_id
432 ,p_expenditure_item_id => p_expenditure_item_id
433 ,p_line_type => p_line_type
434 ,p_request_id => p_request_id
435 ,p_user_id => p_user_id
436 ,p_trx_date => p_trx_date
437 ,p_tax_classification_code => p_tax_classification_code
438 ,p_ship_to_customer_id => NULL
439 ,p_bill_to_customer_id => NULL
440 ,p_application_id => p_application_id
441 ,p_internal_organization_id => p_internal_organization_id);
442
443 IF (g_level_statement >= g_current_runtime_level ) THEN
444 FND_LOG.STRING(g_level_statement,
445 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification.END',
446 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_pa_default_classification(-)');
447 END IF;
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
452 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_pa_default_classification- '||
453 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
454 FND_MSG_PUB.Add;
455
456 IF (g_level_unexpected >= g_current_runtime_level ) THEN
457 FND_LOG.STRING(g_level_unexpected,
458 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification',
459 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
460 END IF;
461 RAISE ;
462 END get_pa_default_classification;
463
464 /*----------------------------------------------------------------------------*
465 | PUBLIC PROCEDURE |
466 | get_pa_default_classification |
467 | |
468 | DESCRIPTION |
469 | Returns default tax classification code for Project Accounting's Draft |
470 | invoice. |
471 | |
472 | SCOPE: Public |
473 | |
474 | CALLED FROM: |
475 | Project Accounting's Tax Defaulting api |
476 | |
477 | HISTORY |
478 | 22-Jun094 Sudhir Sekuri Created. |
479 | |
480 *----------------------------------------------------------------------------*/
481
482 PROCEDURE get_pa_default_classification (
483 p_project_id IN NUMBER,
484 p_project_customer_id IN NUMBER,
485 p_ship_to_site_use_id IN NUMBER,
486 p_bill_to_site_use_id IN NUMBER,
487 p_set_of_books_id IN NUMBER,
488 p_event_id IN NUMBER,
489 p_expenditure_item_id IN NUMBER,
490 p_line_type IN VARCHAR2,
491 p_request_id IN NUMBER,
492 p_user_id IN NUMBER,
493 p_trx_date IN DATE,
494 p_tax_classification_code OUT NOCOPY VARCHAR2,
495 p_ship_to_customer_id IN NUMBER,
496 p_bill_to_customer_id IN NUMBER,
497 p_application_id IN NUMBER,
498 p_internal_organization_id IN NUMBER) IS
499
500 l_site_use_id NUMBER;
501 l_default_level VARCHAR2(30);
502 --l_tax_classification_code VARCHAR2(30);
503 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
504
505 l_vat_tax_id NUMBER;
506 l_count NUMBER;
507 l_party_flag VARCHAR2(1);
508 l_return_status VARCHAR2(80);
509 l_product VARCHAR2(2);
510
511 BEGIN
512 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
513
514 IF (g_level_statement >= g_current_runtime_level ) THEN
515 FND_LOG.STRING(g_level_statement,
516 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification.BEGIN',
517 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_pa_default_classification(+)');
518 END IF;
519
520 l_party_flag := 'N';
521 l_site_use_id := nvl(p_ship_to_site_use_id, p_bill_to_site_use_id);
522 l_product := 'PA';
523
524 IF (sysinfo.pa_product_options_rec.ORG_ID is NULL OR
525 sysinfo.pa_product_options_rec.ORG_ID <> p_internal_organization_id ) THEN
526 pop_pa_tax_info(p_internal_organization_id,
527 p_application_id,
528 l_return_status );
529 g_org_id := sysinfo.pa_product_options_rec.org_id;
530
531 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
532 RETURN;
533 END IF;
534 END IF;
535
536 IF (sysinfo.pa_product_options_rec.def_option_hier_1_code IS NOT NULL
537 OR sysinfo.pa_product_options_rec.def_option_hier_2_code IS NOT NULL
538 OR sysinfo.pa_product_options_rec.def_option_hier_3_code IS NOT NULL
539 OR sysinfo.pa_product_options_rec.def_option_hier_4_code IS NOT NULL
540 OR sysinfo.pa_product_options_rec.def_option_hier_5_code IS NOT NULL
541 OR sysinfo.pa_product_options_rec.def_option_hier_6_code IS NOT NULL
542 OR sysinfo.pa_product_options_rec.def_option_hier_7_code IS NOT NULL)
543 AND NVL(sysinfo.pa_product_options_rec.use_tax_classification_flag,'N') = 'Y' THEN
544
545 IF (g_level_statement >= g_current_runtime_level ) THEN
546 FND_LOG.STRING(g_level_statement,
547 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification',
548
549 'Initializing PA defaulting information');
550 END IF;
551
552 sysinfo.search_pa_hierarchy_tab(1) := sysinfo.pa_product_options_rec.def_option_hier_1_code;
553 sysinfo.search_pa_hierarchy_tab(2) := sysinfo.pa_product_options_rec.def_option_hier_2_code;
554 sysinfo.search_pa_hierarchy_tab(3) := sysinfo.pa_product_options_rec.def_option_hier_3_code;
555 sysinfo.search_pa_hierarchy_tab(4) := sysinfo.pa_product_options_rec.def_option_hier_4_code;
556 sysinfo.search_pa_hierarchy_tab(5) := sysinfo.pa_product_options_rec.def_option_hier_5_code;
557 sysinfo.search_pa_hierarchy_tab(6) := sysinfo.pa_product_options_rec.def_option_hier_6_code;
558 sysinfo.search_pa_hierarchy_tab(7) := sysinfo.pa_product_options_rec.def_option_hier_7_code;
559 ELSE
560 IF (g_level_statement >= g_current_runtime_level ) THEN
561 FND_LOG.STRING(g_level_statement,
562 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification',
563 'Defaulting of Tax Classification is not enabled or defaulting options are not set');
564 FND_LOG.STRING(g_level_statement,
565 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification.END',
566 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_pa_default_classification()-' );
567 END IF;
568 return;
569 END IF;
570
571 l_count := sysinfo.search_pa_hierarchy_tab.COUNT;
572
573 FOR i IN 1..l_count
574 Loop
575 IF (sysinfo.search_pa_hierarchy_tab(i) IS NULL) Then
576 --
577 -- default hierachy options from 1 to 7 can not
578 -- have gap, if the current one is NULL, the
579 -- rest would be NULL, there is no need to
580 -- continue looping
581 --
582 EXIT;
583 ELSE
584 -- sysinfo.search_pa_hierarchy_tab(i) IS NOT NULL
585
586 l_default_level := rtrim(sysinfo.search_pa_hierarchy_tab(i));
587 IF (g_level_statement >= g_current_runtime_level ) THEN
588 FND_LOG.STRING(g_level_statement,
589 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification',
590 '-- Search level = '||l_default_level);
591 END IF;
592
593 IF (l_default_level = TAX_DEFAULT_CUSTOMER) THEN
594 --
595 -- Get Customer level tax code
596 --
597 -- Bill_to_site_use_id
598 IF (g_level_statement >= g_current_runtime_level ) THEN
599 FND_LOG.STRING(g_level_statement,
600 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification',
601 'get the customer level tax code using ship to information ...');
602 END IF;
603
604 l_tax_classification_code := get_customer_tax(p_ship_to_site_use_id,
605 p_ship_to_customer_id,
606 p_trx_date);
607 -- Ship_to_site_use_id
608 IF l_tax_classification_code IS NULL THEN
609 IF (g_level_statement >= g_current_runtime_level ) THEN
610 FND_LOG.STRING(g_level_statement,
611 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification',
612 'Cannot find tax code using ship to information. '||
613 'Using bill to information instead');
614 END IF;
615
616 l_tax_classification_code := get_customer_tax(
617 p_bill_to_site_use_id,
618 p_bill_to_customer_id,
619 p_trx_date);
620 END IF;
621
622 ELSIF (l_default_level = TAX_DEFAULT_SITE) THEN
623 --
624 -- Get Customer Site level tax code
625 --
626 l_tax_classification_code := get_site_tax (l_site_use_id,
627 p_trx_date);
628
629 --
630 -- Bug# 1810878
631 -- if tax_code is null in ship to site
632 -- then get tax_code from bill to site
633 --
634 IF l_tax_classification_code is NULL and p_ship_to_site_use_id is NOT NULL THEN
635 l_tax_classification_code := get_site_tax (
636 p_bill_to_site_use_id,
637 p_trx_date);
638 END IF;
639
640 ELSIF (l_default_level = TAX_DEFAULT_PROJECT) THEN
641 --
642 -- Get Customer Project level tax code
643 --
644 l_tax_classification_code := get_project_tax (
645 p_project_id => p_project_id,
646 p_trx_date => p_trx_date);
647
648 ELSIF (l_default_level = TAX_DEFAULT_EXP_EV) THEN
649 --
650 -- Get Customer Project level tax code
651 --
652 IF ltrim(rtrim(p_line_type)) = 'EXPENDITURE' then
653 --
654 -- Get Expenditure Level Tax Code
655 --
656 l_tax_classification_code :=
657 get_expenditure_tax (
658 p_expenditure_item_id => p_expenditure_item_id,
659 p_trx_date => p_trx_date);
660
661 ELSIF ltrim(rtrim(p_line_type)) = 'EVENT' then
662 --
663 -- Get Event Level Tax Code
664 --
665 l_tax_classification_code := get_event_tax(
666 p_event_id => p_event_id);
667
668 ELSIF ltrim(rtrim(p_line_type)) = 'RETENTION' then
669 --
670 -- Get Project Level Retention Tax Code
671 --
672 l_tax_classification_code := get_project_tax(
673 p_project_id => p_project_id,
674 p_trx_date => p_trx_date,
675 p_retention_flag => TRUE);
676 END IF;
677
678 ELSIF (l_default_level = TAX_DEFAULT_EXTENSION) THEN
679 --
680 -- Get Client Extention Tax Code
681 --
682
683 -- NOTE: X_VAT_TAX_ID parameter should be changed to return and hold
684 -- Tax Classification Code.
685 -- This API internally calls PA_CLIENT_EXTN_OUTPUT_TAX and it
686 -- needs to be changed for this parameter
687
688 IF (g_level_statement >= g_current_runtime_level ) THEN
689 FND_LOG.STRING(g_level_statement,
690 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification',
691 'Calling PA_TAX_CLIENT_EXTN_DRV.get_tax_code');
692 END IF;
693
694 PA_TAX_CLIENT_EXTN_DRV.get_tax_code(
695 p_project_id => p_project_id,
696 p_customer_id => p_project_customer_id,
697 p_bill_to_site_use_id => p_bill_to_site_use_id,
698 p_ship_to_site_use_id => p_ship_to_site_use_id,
699 p_set_of_books_id => p_set_of_books_id,
700 p_expenditure_item_id => p_expenditure_item_id,
701 p_event_id => p_event_id,
702 p_line_type => p_line_type,
703 p_request_id => p_request_id,
704 p_user_id => p_user_id,
705 X_output_Tax_code => l_tax_classification_code);
706 -- bug#4480976
707 --x_vat_tax_id => l_vat_tax_id);
708
709
710 ELSIF (l_default_level = TAX_DEFAULT_AR_PARAM) THEN
711 --
712 -- Get AR System option level tax code
713 --
714 /* Bug 2214337: Get location based tax only if system level default tax code is null*/
715 /*************************
716 -- Bug#3945805
717 IF (
718 NVL(sysinfo.pa_product_options_rec.home_country_default_flag,'N') = 'Y') THEN
719 --
720 -- Look for tax code of type 'LOCATION'
721 --
722 l_tax_classification_code := get_location_tax(l_product,
723 l_site_use_id,
724 l_party_flag);
725
726 ELSE
727 ****************************/
728
729 l_tax_classification_code := sysinfo.pa_product_options_rec.tax_classification_code;
730 -- END IF;
731
732 END IF;
733
734 /* Bug Fix 2101493 Exit when tax code or tax id is found (when the
735 level is TAX_DEFAULT_EXTENSION tax id is returned,unlike other levels where
736 tax code is returned. Therefore if tax code or tax id is not null then stop
737 looping through the hierarchy */
738
739 IF (l_tax_classification_code IS NOT NULL) THEN
740 p_tax_classification_code := l_tax_classification_code;
741 EXIT; -- Exit search when tax code found.
742 END IF;
743 END IF;
744 END LOOP;
745
746 IF (g_level_statement >= g_current_runtime_level ) THEN
747 FND_LOG.STRING(g_level_statement,
748 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification.END',
749 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_pa_default_classification(-)');
750 END IF;
751
752 EXCEPTION
753 WHEN OTHERS THEN
754 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
755 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_pa_default_classification- '||
756 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
757 FND_MSG_PUB.Add;
758
759 IF (g_level_unexpected >= g_current_runtime_level ) THEN
760 FND_LOG.STRING(g_level_unexpected,
761 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_pa_default_classification',
762 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
763 END IF;
764 RAISE ;
765 END get_pa_default_classification;
766
767
768 /*----------------------------------------------------------------------------*
769 | PUBLIC PROCEDURE
770 | get_default_tax_classification
771 |
772 | DESCRIPTION
773 | In Release 11i, the defaulting hierarchy was used as a means to define
774 | Tax applicability. In order to support backward compatible behaviour of
775 | the defaulting hierarchy, the defaulted tax code will be passed to eTax
776 | as 'TAX_CLASSIFICATION_CODE', and in eTax, there will be rules created
777 | based on 'TAX_CLASSIFICATION_CODE' - for 'Direct Rate Determination'
778 | process, which will provide Tax, Tax Regime, Tax Status and Tax Rate
779 | code as a result.
780 |
781 | For example, if a tax code of 'VAT10' represents Tax Regime 'UK VAT',
782 | tax 'VAT', Tax Status Code 'STANDARD', and tax rate code of 'VAT10',
783 | and if defaulting hierarchy is designed to default this tax code on the
784 | transaction, then after defaulting, 'VAT10' would be passed to eTax as
785 | Tax Classification Code, and there will be a rule for Direct Rate
786 | Determination in eTax as follows:
787 |
788 | IF TAX_CLASSICATION_CODE = 'VAT10' THEN
789 | The result of Direct Rate Determination process is:
790 | Tax Regime Code 'UKVAT', Tax: 'VAT', Tax Status Code: 'STANDARD'
791 | and Tax Rate Code 'VAT10' are applicable'
792 |
793 | In order to pass the Tax classification Code to eTax, there should be a
794 | new API: get_default_Tax_classification, which will look at the Event
795 | Class defaulting options for Receivables and based on the defaulting
796 | hierarchy in event class mapping for AR, will provide default tax
797 | classification - from customer, customer site, item, product or system,
798 | and returns the tax classification.
799 |
800 | Bug 3517888 - For supporting customized views (The user may be currently
801 | using customized views to pass tax code to GTE), we will provide user
802 | definable PL/SQL function to default tax classification, which will be
803 | called from this new defaulting API. This pl/sql function name needs to
804 | be defined in TSRM at the same place where we are planning to define
805 | defaulting hierarchy options. We will call this function during defaulting
806 | api of the tax classification field, considering it as one of the
807 | defaulting hierarchy option. This API will also use the user-defined
808 | functions, defined in eTax Event Class Mappings to default Tax
809 | Classification Code (which is to support custom views that the user
810 | can define in AR today. For details, please refer bug 3517888).
811 |
812 | Need to find out the event class options being introduced through bug
813 | 3525184. Unless this bug is scoped in / resolved, we cannot code the
814 | new API - get_Default_Tax_Classification
815 |
816 | This API needs to be called inside validate n default API when Tax
817 | Classification Code is NULL.
818 |
819 | If the Tax Extension Service: TAX_CODE_DEFAULT has been implemented
820 | any call to this stored procedure will be implemented by a callout to
821 | A PL/SQL User Exit which a site can implement.
822 |
823 | Tax Code search hierarchy: Search ends when a tax code is found.
824 |
825 | PARAMETERS
826 | ship_to_site_use_id NUMBER
827 | bill_to_site_use_id NUMBER
828 | inventory_item_id NUMBER
829 | organization_id NUMBER
830 | -- warehouse_id NUMBER
831 | set_of_books_id NUMBER
832 | trx_date DATE
833 | trx_type_id NUMBER -- GL tax/Latin America
834 | memo_line_id NUMBER default null
835 | customer_id NUMBER default null
836 | cust_trx_id NUMBER default null -- GL tax for AR
837 | cust_trx_line_id NUMBER default null -- GL tax for AR
838 |
839 | APPL_SHORT_NAME VARCHAR2 default 'SO'
840 | Valid values:
841 | ------------
842 | OE - Order Entry; Tax code will not be defaulted from GL.
843 | AR - Receivables; GL Natural accounts for Revenue will also be used
844 | to default tax code.
845 | FUNC_SHORT_NAME VARCHAR2 default 'OE'
846 | Valid values:
847 | ------------
848 | OE - Future use.
849 | ACCT_RULES - Use Autoaccounting rules to default tax code from
850 | GL (E.g. Trx line Insert)
851 | ACCT_DIST - Use Accounting Distribution lines to default tax
852 | code from GL. (E.g. Trx Line Update)
853 | GL_ACCT_FIXUP - If tax code should be enforced from Natural Account
854 | Ignore hierarchy and default tax code from GL only
855 | using Revenue Account distributions that do NOT
856 | allow override of tax code.
857 | (E.g. Tax code fixup on Transaction completion)
858 | GL_ACCT_FIRST - If tax code should be enforced from Natural Account
859 | FIRST default tax code from GL using Revenue
860 | Account distributions that do NOT allow override
861 | of tax code.
862 | If not found, default thru the hierarchy using
863 | Accounting distributions.
864 | (E.g. Autoinvoice and Recurring Invoice)
865 |
866 | RETURNS
867 | tax_code - if there is a valid active one
868 | vat_tax_id - Used by AR
869 | amount_includes_tax_flag - Used by AR Trx Workbench
870 | amount_includes_tax_override - Used by AR Trx Workbench
871 | exception NO_DATA_FOUND when no tax code found
872 |
873 | EXAMPLE PL/SQL BLOCK
874 | Calling get_default_tax_classification() the procedure will return tax
875 | classification code or an exception
876 |
877 | HISTORY
878 | 21-Jun-04 Sudhir Sekuri Created.
879 |
880 *----------------------------------------------------------------------------*/
881 --
882 -- OE/OSM/AR Tax code defaulting API
883 --
884 PROCEDURE get_default_tax_classification (
885 p_ship_to_site_use_id IN NUMBER,
886 p_bill_to_site_use_id IN NUMBER,
887 p_inventory_item_id IN NUMBER,
888 p_organization_id IN NUMBER,
889 -- p_warehouse_id IN NUMBER,
890 p_set_of_books_id IN NUMBER,
891 p_trx_date IN DATE,
892 p_trx_type_id IN NUMBER,
893 p_tax_classification_code OUT NOCOPY VARCHAR2 ,
894 -- p_vat_tax_id OUT NOCOPY NUMBER,
895 -- p_amt_incl_tax_flag OUT NOCOPY VARCHAR2,
896 -- p_amt_incl_tax_override OUT NOCOPY VARCHAR2,
897 p_cust_trx_id IN NUMBER default null,
898 p_cust_trx_line_id IN NUMBER default null,
899 p_customer_id IN NUMBER default null,
900 p_memo_line_id IN NUMBER default null,
901 APPL_SHORT_NAME IN VARCHAR2 default null,
902 FUNC_SHORT_NAME IN VARCHAR2 default null,
903 p_party_flag IN VARCHAR2 default null,
904 p_party_location_id IN VARCHAR2 default null,
905 p_entity_code IN VARCHAR2,
906 p_event_class_code IN VARCHAR2,
907 p_application_id IN NUMBER,
908 p_internal_organization_id IN NUMBER,
909 p_ccid IN NUMBER default null
910 ) IS
911
912 -- v_tax_classification_code VARCHAR2(50) := NULL;
913 v_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
914 l_use_acct_line_flag BOOLEAN;
915
916 l_default_level VARCHAR2(30);
917 l_site_use_id NUMBER;
918 l_first_pty_org_id NUMBER;
919 l_count NUMBER;
920 l_return_status VARCHAR2(80);
921 l_product VARCHAR2(2);
922
923 CURSOR sel_tax_code_info (c_tax_code VARCHAR2,
924 c_set_of_books_id NUMBER,
925 c_trx_date DATE) IS
926 SELECT t.tax_code,
927 t.vat_tax_id,
928 amount_includes_tax_flag,
929 amount_includes_tax_override
930 FROM AR_VAT_TAX T
931 WHERE t.tax_code = c_tax_code
932 AND t.set_of_books_id = c_set_of_books_id
933 AND c_trx_date between t.start_date and
934 nvl(t.end_date, c_trx_date)
935 AND nvl(t.enabled_flag, 'Y') = 'Y'
936 AND nvl(t.tax_class, 'O') = 'O';
937
938 CURSOR c_evnt_cls_options (c_org_id NUMBER,
939 c_application_id NUMBER,
940 c_entity_code VARCHAR2,
941 c_event_class_code VARCHAR2,
942 c_trx_date DATE) IS
943 select enforce_tax_from_acct_flag
944 from zx_evnt_cls_options
945 where application_id = c_application_id
946 and entity_code = c_entity_code
947 and event_class_code = c_event_class_code
948 and first_pty_org_id = (Select party_tax_profile_id
949 From zx_party_tax_profile
950 where party_id = c_org_id
951 and party_type_code = 'OU')
952 and c_trx_date >= effective_from
953 and c_trx_date <= nvl(effective_to,c_trx_date)
954 and enabled_flag = 'Y';
955
956 cursor c_chk_tax_classif_code( c_tax_code in VARCHAR2,
957 c_org_id in NUMBER,
958 c_trx_date DATE) is
959 select lookup_code
960 from zx_output_classifications_v
961 where lookup_code = c_tax_code
962 AND org_id IN (c_org_id, -99)
963 AND enabled_flag = 'Y'
964 AND (start_date_active <= c_trx_date OR
965 start_date_active is null)
966 AND (end_date_active >= c_trx_date OR
967 end_date_active is null)
968 AND rownum = 1
969 ORDER BY org_id desc;
970
971
972
973
974
975 l_tax_enforce_account_flag zx_evnt_cls_options.enforce_tax_from_acct_flag%type;
976 l_index BINARY_INTEGER;
977 BEGIN
978 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
979
980 IF (g_level_statement >= g_current_runtime_level ) THEN
981 FND_LOG.STRING(g_level_statement,
982 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.BEGIN',
983 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification(+)');
984 FND_LOG.STRING(g_level_statement,
985 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
986 'p_ship_to_site_use_id: '||to_char(p_ship_to_site_use_id));
987 FND_LOG.STRING(g_level_statement,
988 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
989 'p_bill_to_site_use_id: '||to_char(p_bill_to_site_use_id));
990 FND_LOG.STRING(g_level_statement,
991 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
992 'p_inventory_item_id: '||to_char(p_inventory_item_id));
993 FND_LOG.STRING(g_level_statement,
994 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
995 'p_organization_id: '||to_char(p_organization_id));
996 FND_LOG.STRING(g_level_statement,
997 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
998 'p_set_of_books_id: '||to_char(p_set_of_books_id));
999 FND_LOG.STRING(g_level_statement,
1000 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1001 'p_trx_date: '||to_char(p_trx_date,'DD-MON-YYYY'));
1002 FND_LOG.STRING(g_level_statement,
1003 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1004 'p_trx_type_id: '||to_char(p_trx_type_id));
1005 FND_LOG.STRING(g_level_statement,
1006 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1007 'p_cust_trx_id: '||to_char(p_cust_trx_id));
1008 FND_LOG.STRING(g_level_statement,
1009 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1010 'p_cust_trx_line_id: '||to_char(p_cust_trx_line_id));
1011 FND_LOG.STRING(g_level_statement,
1012 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1013 'p_customer_id: '||to_char(p_customer_id));
1014 FND_LOG.STRING(g_level_statement,
1015 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1016 'p_memo_line_id: '||to_char(p_memo_line_id));
1017 FND_LOG.STRING(g_level_statement,
1018 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1019 'APPL_SHORT_NAME: '||APPL_SHORT_NAME);
1020 FND_LOG.STRING(g_level_statement,
1021 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1022 'FUNC_SHORT_NAME: '||FUNC_SHORT_NAME );
1023 FND_LOG.STRING(g_level_statement,
1024 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1025 'p_party_flag: '||p_party_flag);
1026 FND_LOG.STRING(g_level_statement,
1027 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1028 'p_party_location_id: '||p_party_location_id );
1029 FND_LOG.STRING(g_level_statement,
1030 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1031 'p_application_id == >'||TO_CHAR(p_application_id));
1032 FND_LOG.STRING(g_level_statement,
1033 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1034 'p_internal_organization_id == >'||TO_CHAR(p_internal_organization_id));
1035 END IF;
1036
1037 v_tax_classification_code := NULL;
1038 l_product := 'AR';
1039
1040 IF (sysinfo.ar_product_options_rec.ORG_ID is NULL OR
1041 sysinfo.ar_product_options_rec.ORG_ID <> p_internal_organization_id) THEN
1042 pop_ar_tax_info(p_internal_organization_id,
1043 p_application_id,
1044 l_return_status );
1045 g_org_id := sysinfo.ar_product_options_rec.ORG_ID;
1046
1047 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1048 RETURN;
1049 END IF;
1050 END IF;
1051
1052 IF profinfo.so_organization_id is NULL then
1053 profinfo.so_organization_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', g_org_id);
1054 END IF;
1055
1056 /*************************************************************************/
1057 /* If installed, the Tax Vendor Extension will be called to determine if */
1058 /* the Tax Code Defaulting from Order Entry has been implemented */
1059 /*************************************************************************/
1060 IF sysinfo.sysparam.tax_method = MTHD_LATIN THEN
1061 v_tax_classification_code := JG_ZZ_TAX.get_default_tax_code(
1062 p_set_of_books_id,
1063 p_trx_date,
1064 p_trx_type_id);
1065
1066 IF (g_level_statement >= g_current_runtime_level ) THEN
1067 FND_LOG.STRING(g_level_statement,
1068 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1069 'Tax_method is LATIN and Tax code is '|| v_tax_classification_code);
1070 FND_LOG.STRING(g_level_statement,
1071 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
1072 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification()-' );
1073 END IF;
1074 --
1075 -- Bug#5024478- return output to caller
1076 --
1077 p_tax_classification_code := v_tax_classification_code;
1078 RETURN;
1079 END IF;
1080
1081 -- Get Receivables default tax code.
1082 -- Search: Site, Customer, Product, Account, Tax type LOCATION and
1083 -- System option
1084 -- The search ends when a tax code is found.
1085 --
1086
1087 IF NVL(sysinfo.ar_product_options_rec.use_tax_classification_flag,'N') = 'N' THEN
1088 --
1089 -- if use_tax_classification_flag is no, no need to
1090 -- search the default hierachy
1091 --
1092 p_tax_classification_code := NULL;
1093 RETURN;
1094 END IF;
1095
1096 -- Fetch Tax Enforce Account Flag
1097 -- Bug#4090842- use org_id passed in
1098 -- OPEN c_evnt_cls_options (to_number(substrb(userenv('CLIENT_INFO'),1,10)),
1099
1100 IF sysinfo.tax_enforce_account_flag is NULL then
1101 BEGIN
1102 OPEN c_evnt_cls_options (
1103 p_internal_organization_id,
1104 222,
1105 p_entity_code,
1106 p_event_class_code,
1107 p_trx_date);
1108 FETCH c_evnt_cls_options into l_tax_enforce_account_flag;
1109
1110 if c_evnt_cls_options%NOTFOUND then
1111 IF (g_level_statement >= g_current_runtime_level ) THEN
1112 FND_LOG.STRING(g_level_statement,
1113 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1114 'c_evnt_cls_options cursor not found !! Setting sysinfo.tax_enforce_account_flag to N');
1115 END IF;
1116
1117 sysinfo.tax_enforce_account_flag := 'N';
1118 end if;
1119
1120 IF (g_level_statement >= g_current_runtime_level ) THEN
1121 FND_LOG.STRING(g_level_statement,
1122 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1123 'c_evnt_cls_options.tax_enforce_account_flag = '||l_tax_enforce_account_flag);
1124 END IF;
1125
1126 sysinfo.tax_enforce_account_flag := l_tax_enforce_account_flag;
1127
1128 if c_evnt_cls_options%ISOPEN then
1129 close c_evnt_cls_options;
1130 end if;
1131
1132 EXCEPTION
1133 when others then
1134
1135 IF (g_level_statement >= g_current_runtime_level ) THEN
1136 FND_LOG.STRING(g_level_statement,
1137 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1138 'Exception: '||SQLCODE||' ; '||SQLERRM||' Setting sysinfo.tax_enforce_account_flag to N');
1139 END IF;
1140 sysinfo.tax_enforce_account_flag := 'N';
1141 if c_evnt_cls_options%ISOPEN then
1142 close c_evnt_cls_options;
1143 end if;
1144 END;
1145 END IF;
1146
1147 -- If function called to fixup GL Acct Tax Code, Get Override
1148 -- protected Natural Acct tax code using Revenue account lines and
1149 -- exit.
1150 -- E.g.: Trx Workbench - Invoice Completion
1151 --
1152 IF ( NVL(appl_short_name, 'SO') = 'AR' AND
1153 NVL(func_short_name, 'OE') = 'GL_ACCT_FIXUP' AND
1154 nvl(sysinfo.tax_enforce_account_flag,'N') = 'Y' ) THEN
1155
1156 v_tax_classification_code := get_natural_acct_tax(
1157 p_ccid => p_ccid,
1158 p_internal_organization_id => p_internal_organization_id,
1159 p_set_of_books_id=>p_set_of_books_id,
1160 p_trx_date=>p_trx_date,
1161 p_check_override_only=>'Y');
1162
1163 IF v_tax_classification_code IS NOT NULL THEN
1164 IF (g_level_statement >= g_current_runtime_level ) THEN
1165 FND_LOG.STRING(g_level_statement,
1166 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1167 'Tax Classification code is '|| v_tax_classification_code);
1168 FND_LOG.STRING(g_level_statement,
1169 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
1170 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification()-' );
1171 END IF;
1172
1173 p_tax_classification_code := v_tax_classification_code;
1174 RETURN;
1175 ELSE
1176 RAISE NO_DATA_FOUND;
1177 END IF;
1178 END IF;
1179
1180 -- If function called to look for tax codes at Natural Account first
1181 -- Get Override protected natural Account tax code using Revenue
1182 -- account lines. If tax code not found, Search thru the hierarchy.
1183 -- E.g.: Autoinvoice and Recurring tax code defaulting.
1184 --
1185 IF ( NVL(func_short_name, 'OE') = 'GL_ACCT_FIRST' AND
1186 nvl(sysinfo.tax_enforce_account_flag,'N') = 'Y' ) THEN
1187
1188 v_tax_classification_code := get_natural_acct_tax(
1189 p_ccid => p_ccid,
1190 p_internal_organization_id => p_internal_organization_id,
1191 p_set_of_books_id=>p_set_of_books_id,
1192 p_trx_date=>p_trx_date,
1193 p_check_override_only=>'Y');
1194 IF v_tax_classification_code IS NOT NULL THEN
1195 IF (g_level_statement >= g_current_runtime_level ) THEN
1196 FND_LOG.STRING(g_level_statement,
1197 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1198 'Tax Classification code is '|| v_tax_classification_code);
1199 FND_LOG.STRING(g_level_statement,
1200 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
1201 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification()-' );
1202 END IF;
1203
1204 p_tax_classification_code := v_tax_classification_code;
1205 RETURN;
1206 END IF;
1207
1208 END IF;
1209
1210 /*----------------------------------------------------------------*/
1211 /* Defaulting Hierarchy: */
1212 /* Site, Customer, Item/Memo, Revenue Account, System Options */
1213 /*----------------------------------------------------------------*/
1214 l_site_use_id := nvl(p_ship_to_site_use_id, p_bill_to_site_use_id);
1215
1216 IF (sysinfo.ar_product_options_rec.def_option_hier_1_code IS NOT NULL
1217 OR sysinfo.ar_product_options_rec.def_option_hier_2_code IS NOT NULL
1218 OR sysinfo.ar_product_options_rec.def_option_hier_3_code IS NOT NULL
1219 OR sysinfo.ar_product_options_rec.def_option_hier_4_code IS NOT NULL
1220 OR sysinfo.ar_product_options_rec.def_option_hier_5_code IS NOT NULL
1221 OR sysinfo.ar_product_options_rec.def_option_hier_6_code IS NOT NULL
1222 OR sysinfo.ar_product_options_rec.def_option_hier_7_code IS NOT NULL) THEN
1223
1224 IF (g_level_statement >= g_current_runtime_level ) THEN
1225 FND_LOG.STRING(g_level_statement,
1226 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1227 'Initializing AR defaulting information');
1228 END IF;
1229
1230 sysinfo.search_hierarchy_tab(1) := sysinfo.ar_product_options_rec.def_option_hier_1_code;
1231 sysinfo.search_hierarchy_tab(2) := sysinfo.ar_product_options_rec.def_option_hier_2_code;
1232 sysinfo.search_hierarchy_tab(3) := sysinfo.ar_product_options_rec.def_option_hier_3_code;
1233 sysinfo.search_hierarchy_tab(4) := sysinfo.ar_product_options_rec.def_option_hier_4_code;
1234 sysinfo.search_hierarchy_tab(5) := sysinfo.ar_product_options_rec.def_option_hier_5_code;
1235 sysinfo.search_hierarchy_tab(6) := sysinfo.ar_product_options_rec.def_option_hier_6_code;
1236 sysinfo.search_hierarchy_tab(7) := sysinfo.ar_product_options_rec.def_option_hier_7_code;
1237 ELSE
1238 IF (g_level_statement >= g_current_runtime_level ) THEN
1239 FND_LOG.STRING(g_level_statement,
1240 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1241 'Defaulting of Tax Classification is not enabled or defaulting options are not set');
1242 FND_LOG.STRING(g_level_statement,
1243 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
1244 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification()-' );
1245 END IF;
1246 return;
1247 END IF;
1248
1249 l_count := sysinfo.search_hierarchy_tab.COUNT;
1250
1251 FOR i IN 1..l_count
1252 Loop
1253 IF (sysinfo.search_hierarchy_tab(i) IS NULL) Then
1254 --
1255 -- default hierachy options from 1 to 7 can not
1256 -- have gap, if the current one is NULL, the
1257 -- rest would be NULL, there is no need to
1258 -- continue looping
1259 --
1260 EXIT;
1261 ELSE
1262 -- sysinfo.search_hierarchy_tab(i) IS NOT NULL
1263 IF ( v_tax_classification_code IS NOT NULL ) THEN
1264 EXIT;
1265 END IF;
1266
1267 l_default_level := rtrim(sysinfo.search_hierarchy_tab(i));
1268 IF (g_level_statement >= g_current_runtime_level ) THEN
1269 FND_LOG.STRING(g_level_statement,
1270 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1271 '-- Search level = '||l_default_level);
1272 END IF;
1273 --crm
1274 IF (nvl(p_party_flag, 'N') = 'N') AND
1275 ( l_default_level = TAX_DEFAULT_SITE ) THEN
1276 --
1277 -- Get Customer Site level tax code
1278 --
1279 v_tax_classification_code := get_site_tax(l_site_use_id,
1280 p_trx_date);
1281 -- Bug# 1810878
1282 -- if tax_classification_code is null in ship to site
1283 -- then get tax_classification_code from bill to site
1284 --
1285 IF v_tax_classification_code is NULL and p_ship_to_site_use_id is NOT NULL THEN
1286 v_tax_classification_code := get_site_tax(p_bill_to_site_use_id,
1287 p_trx_date);
1288 END IF;
1289
1290 END IF;
1291 --crm
1292 IF ( nvl(p_party_flag, 'N') = 'N') AND
1293 ( l_default_level = TAX_DEFAULT_CUSTOMER ) THEN
1294 --
1295 -- Get Customer level tax code
1296 --
1297
1298 IF p_customer_id is NOT NULL AND l_site_use_id is NOT NULL THEN -- Bug 8201987
1299
1300 v_tax_classification_code := get_customer_tax(l_site_use_id,
1301 p_customer_id,
1302 p_trx_date);
1303
1304 IF v_tax_classification_code is NULL and
1305 p_ship_to_site_use_id is NOT NULL THEN
1306 v_tax_classification_code := get_customer_tax(
1307 p_bill_to_site_use_id,
1308 p_customer_id,
1309 p_trx_date);
1310 END IF;
1311 END IF; -- Bug 8201987
1312 END IF;
1313
1314 IF ( l_default_level = TAX_DEFAULT_PRODUCT ) THEN
1315 --
1316 -- Get item level tax code
1317 --
1318
1319 -- ER #1683780. Call get_item_tax using warehouse_id first
1320 IF p_organization_id is not NULL then
1321 v_tax_classification_code :=
1322 get_item_tax(p_inventory_item_id,
1323 p_organization_id,
1324 p_trx_date,
1325 p_memo_line_id);
1326 END IF;
1327
1328 -- If warehouse_id is NULL or tax classification code is not found using warehouse_id
1329 -- then use item validation organization
1330 IF v_tax_classification_code is NULL then
1331
1332 v_tax_classification_code :=
1333 get_item_tax(p_inventory_item_id,
1334 profinfo.so_organization_id,
1335 p_trx_date,
1336 p_memo_line_id);
1337 END IF;
1338
1339 END IF;
1340
1341 --
1342 -- If Application is AR, Look at Natural account
1343 --
1344 IF ( NVL(appl_short_name, 'SO' ) = 'AR' AND
1345 l_default_level = TAX_DEFAULT_ACCOUNT ) THEN
1346
1347 IF NVL(func_short_name, 'OE') IN ('ACCT_DIST', 'GL_ACCT_FIRST') THEN
1348 l_use_acct_line_flag := TRUE; -- Use Revenue account lines
1349 ELSE
1350 l_use_acct_line_flag := FALSE; -- Use AutoAccounting rules
1351 END IF;
1352
1353 v_tax_classification_code := get_natural_acct_tax(
1354 p_ccid => p_ccid,
1355 p_internal_organization_id => p_internal_organization_id,
1356 p_set_of_books_id=>p_set_of_books_id,
1357 p_trx_date=>p_trx_date,
1358 p_check_override_only=>'N');
1359 END IF;
1360
1361
1362 IF ( l_default_level = TAX_DEFAULT_SYSTEM ) THEN
1363
1364 /* Bugfix 558633: System Option level always enabled for Sales Tax */
1365 /* Bugfix 1139131: Only if tax code is null, use the location based tax */
1366 /* Bugfix 3711248: Only if home country default flag is enabled */
1367 /************************
1368 |-- Bug#3945805
1369 | IF (
1370 | NVL(sysinfo.ar_product_options_rec.home_country_default_flag,'N') = 'Y') THEN
1371 | --
1372 | -- Look for tax code of type 'LOCATION'
1373 | --
1374 | v_tax_classification_code := get_location_tax(
1375 | l_product,
1376 | site_use_id,
1377 | p_party_flag,
1378 | p_party_location_id);
1379 | ELSE
1380 ***************************/
1381
1382 IF sysinfo.ar_product_options_rec.tax_classification_code is NOT NULL then
1383 l_index := dbms_utility.get_hash_value(p_internal_organization_id||sysinfo.ar_product_options_rec.tax_classification_code
1384 ||to_char(trunc(p_trx_date),'MMDDRRRR'),1,8192);
1385 IF tax_code_tbl.exists(l_index)
1386 AND tax_code_tbl(l_index).org_id = p_internal_organization_id
1387 AND tax_code_tbl(l_index).lookup_code = sysinfo.ar_product_options_rec.tax_classification_code
1388 AND tax_code_tbl(l_index).det_date = trunc(p_trx_date) THEN
1389
1390 IF (g_level_statement >= g_current_runtime_level ) THEN
1391 FND_LOG.STRING(g_level_statement,'ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1392 'Tax Code Exists in Cache');
1393 END IF;
1394 v_tax_classification_code := tax_code_tbl(l_index).tax_code;
1395 ELSE
1396 IF (g_level_statement >= g_current_runtime_level ) THEN
1397 FND_LOG.STRING(g_level_statement,
1398 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1399 'defaulting tax classification from system ');
1400 END IF;
1401
1402 -- check if this tax calssification code is valid for the current
1403 -- transaction date
1404
1405 open c_chk_tax_classif_code(sysinfo.ar_product_options_rec.tax_classification_code,
1406 p_internal_organization_id,
1407 p_trx_date);
1408 fetch c_chk_tax_classif_code
1409 into v_tax_classification_code;
1410 close c_chk_tax_classif_code;
1411
1412 IF (g_level_statement >= g_current_runtime_level ) THEN
1413 FND_LOG.STRING(g_level_statement,
1414 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1415 'Tax classification defaulted from system is: v_tax_classification_code');
1416 END IF;
1417 tax_code_tbl(l_index).org_id := p_internal_organization_id;
1418 tax_code_tbl(l_index).lookup_code := sysinfo.ar_product_options_rec.tax_classification_code;
1419 tax_code_tbl(l_index).det_date := trunc(p_trx_date);
1420 tax_code_tbl(l_index).tax_code := v_tax_classification_code;
1421 END IF; -- Caching IF
1422 END IF;
1423
1424
1425 -- END IF;
1426 END IF;
1427 END IF;
1428 END LOOP; -- Search tax defaulting hierarchy
1429
1430 /*****************
1431 IF ( v_tax_classification_code IS NULL ) THEN
1432 --
1433 -- Look for tax code of type 'LOCATION'
1434 --
1435 v_tax_classification_code := get_location_tax(site_use_id,
1436 p_party_flag,
1437 p_party_location_id);
1438 END IF;
1439 ***************/
1440
1441 p_tax_classification_code := v_tax_classification_code;
1442
1443 IF (g_level_statement >= g_current_runtime_level ) THEN
1444 FND_LOG.STRING(g_level_statement,
1445 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1446 'default_tax_classification: '||v_tax_classification_code);
1447 END IF;
1448
1449 /* Bug#4406011
1450 --
1451 -- If tax classification code is not found and use tax_classification_flag is enabled,
1452 -- raise NO_DATA_FOUND error
1453 --
1454 IF (v_tax_classification_code IS NULL) THEN
1455 RAISE NO_DATA_FOUND;
1456 END IF;
1457 */
1458
1459 IF (g_level_statement >= g_current_runtime_level ) THEN
1460 FND_LOG.STRING(g_level_statement,
1461 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
1462 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification()-' );
1463 END IF;
1464
1465 EXCEPTION
1466 WHEN NO_DATA_FOUND THEN
1467 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1468 FND_LOG.STRING(g_level_unexpected,
1469 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1470 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1471 END IF;
1472 RAISE;
1473 WHEN OTHERS THEN
1474 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1475 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_default_tax_classification- '||
1476 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1477 FND_MSG_PUB.Add;
1478 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1479 FND_LOG.STRING(g_level_unexpected,
1480 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1481 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1482 END IF;
1483 RAISE ;
1484 END get_default_tax_classification;
1485
1486
1487 /*----------------------------------------------------------------------------*
1488 | PRIVATE FUNCTION |
1489 | get_site_tax |
1490 | |
1491 | DESCRIPTION |
1492 | This function will look for any tax code that is specified at the site |
1493 | level if the system options allow use of tax codes at site level. It |
1494 | will return the tax code if one is found for the Site id. |
1495 | |
1496 | PARAMETERS |
1497 | site_use_id in NUMBER |
1498 | |
1499 | RETURNS |
1500 | tax code if one is found at the site level and valid for the trx date.|
1501 | null if a valid tax classification is not found. |
1502 | |
1503 | CALLED FROM |
1504 | get_default_tax_classification() |
1505 | |
1506 | HISTORY |
1507 | 27-NOV-95 Mahesh Sabapathy Created. |
1508 | 06-Jan-98 Mahesh Sabapathy Bugfix 604453: Exclude members of Tax Group|
1509 | 21-Jun-04 Sudhir Sekuri Bug 3611046 |
1510 *----------------------------------------------------------------------------*/
1511
1512 FUNCTION get_site_tax (
1513 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1514 p_trx_date IN ra_customer_trx.trx_date%TYPE)
1515 RETURN VARCHAR2 IS
1516
1517 l_cust_acct_site_id HZ_CUST_ACCT_SITES.CUST_ACCT_SITE_ID%TYPE;
1518 l_party_tax_profile_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
1519 l_zx_registration_rec ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC;
1520 l_tax_classification_code HZ_CUST_SITE_USES.tax_code%TYPE;
1521 l_ret_record_level VARCHAR2(30);
1522 l_return_status VARCHAR2(80);
1523 l_error_buffer VARCHAR2(100);
1524
1525 l_parent_ptp_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
1526 l_cust_account_id HZ_CUST_ACCT_SITES.CUST_ACCOUNT_ID%TYPE;
1527
1528 CURSOR get_site_tax_info
1529 (c_site_use_id hz_cust_site_uses.site_use_id%TYPE,
1530 c_org_id NUMBER,
1531 c_trx_date date)
1532 IS
1533 SELECT su.tax_code
1534 FROM HZ_CUST_SITE_USES_ALL su, ZX_OUTPUT_CLASSIFICATIONS_V l
1535 WHERE su.site_use_id = c_site_use_id
1536 AND su.org_id = c_org_id
1537 AND l.lookup_code = su.tax_code
1538 AND l.org_id IN (c_org_id, -99)
1539 AND l.enabled_flag = 'Y'
1540 AND (l.start_date_active <= c_trx_date OR
1541 l.start_date_active is null)
1542 AND (l.end_date_active >= c_trx_date OR
1543 l.end_date_active is null)
1544 AND rownum = 1
1545 ORDER BY l.org_id desc;
1546 -- rownum is added because there could be two potnetial rows returned, one for org_id -99
1547 -- and one for c_org_id
1548
1549
1550
1551
1552 BEGIN
1553
1554 IF (g_level_procedure >= g_current_runtime_level ) THEN
1555 FND_LOG.STRING(g_level_procedure,
1556 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax.BEGIN',
1557 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_site_tax(+)');
1558 END IF;
1559
1560 l_tax_classification_code := NULL;
1561
1562 -- IF ( sysinfo.sysparam.tax_use_site_exc_rate_flag = 'Y' ) THEN
1563
1564
1565 OPEN get_site_tax_info(p_site_use_id,
1566 g_org_id,
1567 p_trx_date);
1568 FETCH get_site_tax_info INTO l_tax_classification_code;
1569 CLOSE get_site_tax_info;
1570
1571
1572 IF (g_level_procedure >= g_current_runtime_level ) THEN
1573 FND_LOG.STRING(g_level_procedure,
1574 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1575 'tax_classification_code = ' ||
1576 l_tax_classification_code);
1577 FND_LOG.STRING(g_level_procedure,
1578 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1579 'l_return_status = ' || l_return_status);
1580 FND_LOG.STRING(g_level_procedure,
1581 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax.END',
1582 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_site_tax(-)');
1583 END IF;
1584
1585 RETURN l_tax_classification_code;
1586
1587 EXCEPTION
1588 WHEN OTHERS THEN
1589 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1590 l_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1591 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1592 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_site_tax- '|| l_error_buffer);
1593 FND_MSG_PUB.Add;
1594
1595 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1596 FND_LOG.STRING(g_level_unexpected,
1597 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1598 l_error_buffer);
1599 END IF;
1600
1601
1602 RAISE ;
1603
1604 END get_site_tax;
1605
1606 /*----------------------------------------------------------------------------*
1607 | PRIVATE FUNCTION |
1608 | get_customer_tax |
1609 | |
1610 | DESCRIPTION |
1611 | This function will look for any tax code that is specified at the |
1612 | customer level if the system options allow use of tax codes at the |
1613 | customer level. The function returns the tax code if one is found for |
1614 | the Customer id. It returns null if a default tax code is not found. |
1615 | |
1616 | PARAMETERS |
1617 | site_use_id in NUMBER |
1618 | customer_id in NUMBER default null |
1619 | |
1620 | RETURNS |
1621 | tax code - if one is found at the Customer level,valid for the |
1622 | trx date |
1623 | null - if a valid tax code is not found. |
1624 | |
1625 | CALLED FROM |
1626 | get_default_tax_classification() |
1627 | |
1628 | HISTORY |
1629 | 27-NOV-95 Mahesh Sabapathy Created. |
1630 | 06-Jan-98 Mahesh Sabapathy Bugfix 604453: Exclude members of Tax Group|
1631 | 29-Feb-2000 Wei Feng, Bugfix 1205682: by changing the order of the FROM |
1632 | clause to have RA_CUSTORMER preceding AR_VAT_TAX. |
1633 *----------------------------------------------------------------------------*/
1634
1635 FUNCTION get_customer_tax (
1636 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1637 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
1638 p_trx_date IN ra_customer_trx.trx_date%TYPE)
1639 RETURN VARCHAR2 IS
1640
1641 l_customer_id hz_cust_accounts.cust_account_id%TYPE;
1642 l_tax_classification_code HZ_CUST_ACCOUNTS.tax_code%TYPE;
1643
1644 l_ret_record_level VARCHAR2(30);
1645 l_return_status VARCHAR2(80);
1646 l_error_buffer VARCHAR2(100);
1647
1648
1649 CURSOR get_customer_id
1650 (c_site_use_id HZ_CUST_SITE_USES.site_use_id%TYPE)
1651 IS
1652 SELECT CUST_ACCT.cust_account_id
1653 FROM HZ_CUST_ACCOUNTS CUST_ACCT,
1654 HZ_CUST_ACCT_SITES CUST_ACCT_SITES,
1655 HZ_CUST_SITE_USES CUST_SITE_USES
1656 WHERE CUST_ACCT.cust_account_id = CUST_ACCT_SITES.cust_account_id
1657 AND CUST_ACCT_SITES.cust_acct_site_id = CUST_SITE_USES.cust_acct_site_id
1658 AND CUST_SITE_USES.site_use_id = c_site_use_id;
1659
1660
1661 CURSOR sel_customer_tax
1662 (c_customer_id HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
1663 c_org_id NUMBER,
1664 c_trx_date DATE)
1665 IS
1666 SELECT c.tax_code
1667 FROM HZ_CUST_ACCOUNTS_ALL c, ZX_OUTPUT_CLASSIFICATIONS_V l
1668 WHERE c.cust_account_id = c_customer_id
1669 AND c.org_id = c_org_id
1670 AND l.lookup_code = c.tax_code
1671 AND l.org_id IN (c_org_id, -99)
1672 AND l.enabled_flag = 'Y'
1673 AND (l.start_date_active <= c_trx_date OR
1674 l.start_date_active is null)
1675 AND (l.end_date_active >= c_trx_date OR
1676 l.end_date_active is null)
1677 AND rownum = 1
1678 ORDER BY l.org_id desc;
1679
1680
1681 BEGIN
1682
1683 IF (g_level_procedure >= g_current_runtime_level ) THEN
1684 FND_LOG.STRING(g_level_procedure,
1685 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax.BEGIN',
1686 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_customer_tax(+)');
1687 END IF;
1688
1689 l_tax_classification_code := NULL;
1690
1691 -- IF ( sysinfo.sysparam.tax_use_cust_exc_rate_flag = 'Y' ) THEN
1692 --
1693 -- If customer_id is not passed, then get customer_id using site_use_id
1694 --
1695 IF ( p_customer_id IS NOT NULL ) THEN
1696 l_customer_id := p_customer_id;
1697 ELSE
1698 --
1699 -- Get customer_id
1700 --
1701 OPEN get_customer_id(p_site_use_id);
1702 FETCH get_customer_id INTO l_customer_id;
1703 CLOSE get_customer_id;
1704
1705 END IF; -- Customer_id passed?
1706
1707 IF l_customer_id IS NOT NULL THEN
1708 OPEN sel_customer_tax(l_customer_id,
1709 g_org_id,
1710 p_trx_date);
1711 FETCH sel_customer_tax INTO l_tax_classification_code;
1712 CLOSE sel_customer_tax;
1713 END IF;
1714
1715 IF (g_level_procedure >= g_current_runtime_level ) THEN
1716 FND_LOG.STRING(g_level_procedure,
1717 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax',
1718 'tax_classification_code = ' ||
1719 l_tax_classification_code);
1720 FND_LOG.STRING(g_level_procedure,
1721 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax',
1722 'l_return_status = ' || l_return_status);
1723 FND_LOG.STRING(g_level_procedure,
1724 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax.END',
1725 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_customer_tax(-)');
1726 END IF;
1727
1728 RETURN l_tax_classification_code;
1729
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1733 l_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1734 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1735 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_customer_tax- '|| l_error_buffer);
1736 FND_MSG_PUB.Add;
1737
1738 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1739 FND_LOG.STRING(g_level_unexpected,
1740 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax',
1741 l_error_buffer);
1742 END IF;
1743
1744 RAISE ;
1745
1746 END get_customer_tax;
1747
1748 /*----------------------------------------------------------------------------*
1749 | PRIVATE FUNCTION |
1750 | get_item_tax |
1751 | |
1752 | DESCRIPTION |
1753 | This function will look for any tax code that is specified at the |
1754 | item level if the system options allow use of tax codes at the item |
1755 | level. The function first looks at memo lines if a memo line id is |
1756 | passed and will look at items if a tax code was not found for memo lines|
1757 | The function returns the tax code if one is valid and returns null if |
1758 | one is not found. |
1759 | |
1760 | PARAMETERS |
1761 | organization_id in NUMBER |
1762 | item_id in NUMBER |
1763 | memo_line_id in NUMBER |
1764 | |
1765 | |
1766 | RETURNS |
1767 | tax code - if one is found at the Item or Memo line level,valid for |
1768 | the trx date |
1769 | null - if a valid tax code is not found. |
1770 | |
1771 | CALLED FROM |
1772 | get_default_tax_classification() |
1773 | |
1774 | HISTORY |
1775 | 27-NOV-95 Mahesh Sabapathy Created. |
1776 | 06-Jan-98 Mahesh Sabapathy Bugfix 604453: Exclude members of Tax Group |
1777 | 23-Jun-04 Sudhir Sekuri Bugfix 3611046 |
1778 *----------------------------------------------------------------------------*/
1779
1780 FUNCTION get_item_tax (
1781 p_item_id IN mtl_system_items.inventory_item_id%TYPE,
1782 p_organization_id IN mtl_system_items.organization_id%TYPE,
1783 p_trx_date IN DATE,
1784 p_memo_line_id IN ar_memo_lines.memo_line_id%TYPE default null
1785 ) RETURN VARCHAR2 IS
1786
1787 -- l_tax_classification_code varchar2(30);
1788 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
1789
1790 CURSOR sel_memo_line_tax(
1791 c_memo_line_id AR_MEMO_LINES.memo_line_id%type,
1792 c_trx_date DATE,
1793 c_org_id NUMBER)
1794 IS
1795 SELECT m.tax_code
1796 -- FROM fnd_lookups l, AR_MEMO_LINES M bug#4574838
1797 FROM zx_output_classifications_v l, AR_MEMO_LINES m
1798 WHERE m.memo_line_id = c_memo_line_id
1799 AND l.lookup_code = m.tax_code
1800 AND l.org_id IN (c_org_id, -99)
1801 AND l.enabled_flag = 'Y'
1802 AND (l.start_date_active <= c_trx_date OR
1803 l.start_date_active is null)
1804 AND (l.end_date_active >= c_trx_date OR
1805 l.end_date_active is null)
1806 --AND l.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
1807 AND rownum = 1
1808 ORDER BY l.org_id desc;
1809
1810 CURSOR sel_item_tax
1811 (c_item_id MTL_SYSTEM_ITEMS.inventory_item_id%type,
1812 c_organization_id MTL_SYSTEM_ITEMS.organization_id%type,
1813 c_trx_date DATE,
1814 c_org_id NUMBER)
1815 IS
1816 SELECT i.tax_code
1817 -- FROM fnd_lookups l, MTL_SYSTEM_ITEMS bug#4574838
1818 FROM zx_output_classifications_v l, MTL_SYSTEM_ITEMS i
1819 WHERE i.inventory_item_id = c_item_id
1820 AND i.organization_id = c_organization_id
1821 AND l.lookup_code = i.tax_code
1822 AND l.org_id IN (c_org_id, -99)
1823 AND l.enabled_flag = 'Y'
1824 AND (l.start_date_active <= c_trx_date OR
1825 l.start_date_active is null)
1826 AND (l.end_date_active >= c_trx_date OR
1827 l.end_date_active is null)
1828 -- AND l.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
1829 AND rownum = 1
1830 ORDER BY l.org_id desc;
1831
1832 BEGIN
1833
1834 IF (g_level_statement >= g_current_runtime_level ) THEN
1835 FND_LOG.STRING(g_level_statement,
1836 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_item_tax.BEGIN',
1837 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_item_tax()+' );
1838 END IF;
1839
1840 -- IF ( sysinfo.sysparam.tax_use_prod_exc_rate_flag = 'Y' ) THEN
1841 --
1842 -- If Memo line id passed, look for memo line tax code and if notfound
1843 -- then look for item tax code.
1844 --
1845 IF (p_memo_line_id IS NOT NULL) THEN
1846 --
1847 -- Bug#5331994- add trx_date and org_id
1848 --
1849 OPEN sel_memo_line_tax(
1850 p_memo_line_id,
1851 p_trx_date,
1852 g_org_id);
1853 FETCH sel_memo_line_tax INTO l_tax_classification_code;
1854 CLOSE sel_memo_line_tax;
1855 END IF; -- Memo line info passed?
1856
1857 IF (l_tax_classification_code IS NULL AND
1858 p_item_id IS NOT NULL) THEN
1859 --
1860 -- Couldn't find tax code for Memo lines, look for Item tax code
1861 --
1862 --
1863 -- Bug#5331994- add trx_date and org_id
1864 --
1865 OPEN sel_item_tax(
1866 p_item_id,
1867 p_organization_id,
1868 p_trx_date,
1869 g_org_id);
1870
1871 FETCH sel_item_tax INTO l_tax_classification_code;
1872 CLOSE sel_item_tax;
1873 END IF; -- Tax code not found and item_id passed?
1874 -- END IF;
1875
1876 IF (g_level_statement >= g_current_runtime_level ) THEN
1877 FND_LOG.STRING(g_level_statement,
1878 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_item_tax',
1879 '>>> O : Tax_classification_code = '||l_tax_classification_code);
1880 FND_LOG.STRING(g_level_statement,
1881 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_item_tax.END',
1882 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_item_tax()-' );
1883 END IF;
1884
1885 RETURN (l_tax_classification_code);
1886
1887 EXCEPTION
1888 WHEN OTHERS THEN
1889 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1890 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_item_tax- '||
1891 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1892 FND_MSG_PUB.Add;
1893
1894 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1895 FND_LOG.STRING(g_level_unexpected,
1896 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_item_tax',
1897 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1898 END IF;
1899 IF (sel_memo_line_tax%ISOPEN) THEN
1900 CLOSE sel_memo_line_tax;
1901 END IF;
1902 IF (sel_item_tax%ISOPEN) THEN
1903 CLOSE sel_item_tax;
1904 END IF;
1905 RAISE ;
1906
1907 END get_item_tax;
1908
1909 /*----------------------------------------------------------------------------*
1910 | PRIVATE FUNCTION |
1911 | get_location_tax |
1912 | |
1913 | DESCRIPTION |
1914 | This function will look for a valid tax code of type 'LOCATION' when |
1915 | the tax method is of type 'SALES TAX' |
1916 | The function returns the tax code if one is valid and returns null if |
1917 | one is not found. |
1918 | |
1919 | PARAMETERS |
1920 | set_of_books_id in NUMBER |
1921 | |
1922 | RETURNS |
1923 | tax code - if a tax code of type 'LOCATION' valid for the trx date |
1924 | is found. |
1925 | null - if a valid tax code of type 'LOCATION' is not found. |
1926 | |
1927 | CALLED FROM |
1928 | get_default_tax_classification() |
1929 | |
1930 | HISTORY |
1931 | 27-NOV-95 Mahesh Sabapathy Created. |
1932 | 23-Jun-04 Sudhir Sekuri Bugfix 3611046 |
1933 | 22-Sep-05 Phong La Bugfix 4625479: pass in p_product |
1934 | 30-Sep-05 Phong La Bugfix 3945805: do not this function |
1935 *----------------------------------------------------------------------------*/
1936 -- Bug#3945805
1937 /******************************
1938 FUNCTION get_location_tax (
1939 p_product IN VARCHAR2,
1940 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1941 p_party_flag IN VARCHAR2,
1942 p_party_location_id IN hz_locations.location_id%type) RETURN VARCHAR2 IS
1943
1944 l_country hz_locations.country%TYPE := null;
1945 -- l_tax_classification_code ar_vat_tax.tax_code%TYPE := null;
1946
1947 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
1948
1949 CURSOR sel_addr_country(
1950 c_site_use_id HZ_CUST_SITE_USES.site_use_id%TYPE)
1951 IS
1952 SELECT loc.country
1953 FROM HZ_CUST_ACCT_SITES acct_site,
1954 HZ_PARTY_SITES party_site,
1955 HZ_LOCATIONS loc,
1956 HZ_CUST_SITE_USES site_uses
1957 WHERE site_uses.site_use_id = c_site_use_id
1958 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
1959 AND acct_site.party_site_id = party_site.party_site_id
1960 AND loc.location_id = party_site.location_id;
1961
1962 --crm
1963 CURSOR sel_loc_country (
1964 c_party_location_id HZ_LOCATIONS.location_id%TYPE)
1965 IS
1966 SELECT country
1967 FROM HZ_LOCATIONS
1968 WHERE location_id = c_party_location_id;
1969
1970 BEGIN
1971
1972 IF (g_level_statement >= g_current_runtime_level ) THEN
1973 FND_LOG.STRING(g_level_statement,
1974 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax.BEGIN',
1975 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_location_tax()+' );
1976 FND_LOG.STRING(g_level_statement,
1977 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1978 'p_site_use_id: '||to_char(p_site_use_id));
1979 FND_LOG.STRING(g_level_statement,
1980 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1981 'p_party_flag: '||p_party_flag);
1982 FND_LOG.STRING(g_level_statement,
1983 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1984 'p_party_location_id: '||p_party_location_id);
1985 FND_LOG.STRING(g_level_statement,
1986 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1987 'p_product: '||p_product);
1988 END IF;
1989
1990 l_tax_classification_code := NULL;
1991
1992 --crm
1993 IF (nvl(p_party_flag, 'N') = 'Y') THEN
1994 --
1995 -- Get Country code for party site location
1996 --
1997 OPEN sel_loc_country(p_party_location_id);
1998 FETCH sel_loc_country INTO l_country;
1999 CLOSE sel_loc_country;
2000 ELSE
2001 --
2002 -- Get Country code for the site
2003 --
2004 OPEN sel_addr_country(p_site_use_id);
2005 FETCH sel_addr_country INTO l_country;
2006 CLOSE sel_addr_country;
2007 END IF;
2008
2009 IF (g_level_statement >= g_current_runtime_level ) THEN
2010 FND_LOG.STRING(g_level_statement,
2011 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
2012 'Country code is : '||l_country);
2013 END IF;
2014
2015 --
2016 -- If tax method = 'Sales Tax' and Address is in the Home Country, then look
2017 -- for a valid tax code of type 'LOCATION'.
2018 --
2019 IF ( sysinfo.sysparam.default_country = l_country ) THEN
2020 IF p_product = 'AR' THEN
2021 l_tax_classification_code := sysinfo.ar_product_options_rec.tax_classification_code;
2022 ELSIF p_product = 'PA' THEN
2023 l_tax_classification_code := sysinfo.pa_product_options_rec.tax_classification_code;
2024 END IF;
2025
2026 END IF; -- Tax method is 'Sales Tax'?
2027
2028 IF (g_level_statement >= g_current_runtime_level ) THEN
2029 FND_LOG.STRING(g_level_statement,
2030 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
2031 '>>> O : Tax_classification_code = '||l_tax_classification_code);
2032 FND_LOG.STRING(g_level_statement,
2033 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax.END',
2034 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_location_tax()-' );
2035 END IF;
2036
2037 RETURN (l_tax_classification_code);
2038
2039 EXCEPTION
2040 WHEN OTHERS THEN
2041 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2042 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_location_tax- '||
2043 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2044 FND_MSG_PUB.Add;
2045
2046 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2047 FND_LOG.STRING(g_level_unexpected,
2048 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
2049 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2050 END IF;
2051
2052 RAISE ;
2053
2054 END get_location_tax;
2055 ***************************/
2056
2057 /*----------------------------------------------------------------------------*
2058 | PRIVATE FUNCTION |
2059 | get_natural_acct_tax |
2060 | |
2061 | DESCRIPTION |
2062 | This function will look for any tax code that is specified at the |
2063 | natural account segment of the Revenue account of a transaction line. |
2064 | The tax code, If specified for the natural account must be valid for |
2065 | the trx date and set of books id. The function will return a valid |
2066 | tax code if one is found. |
2067 | |
2068 | If multiple revenue lines exist for the transaction line, All the |
2069 | Revenue account lines must have the same tax code(if any). The |
2070 | function will NOT return a tax code if multiple tax codes are found |
2071 | for the revenue lines. |
2072 | |
2073 | PARAMETERS |
2074 | customer_trx_line_id in NUMBER |
2075 | set_of_books_id in NUMBER |
2076 | trx_date in DATE |
2077 | |
2078 | |
2079 | RETURNS |
2080 | tax code if one is found at the natural account level and is valid for|
2081 | the trx date and set of books id. |
2082 | null if a valid tax code is not found. |
2083 | |
2084 | CALLED FROM |
2085 | get_default_tax_classification() |
2086 | |
2087 | HISTORY |
2088 | 25-Jul-97 Mahesh Sabapathy Created. |
2089 *----------------------------------------------------------------------------*/
2090
2091 FUNCTION get_natural_acct_tax (
2092 p_ccid IN NUMBER
2093 ,p_internal_organization_id IN NUMBER
2094 ,p_set_of_books_id IN ar_system_parameters.set_of_books_id%TYPE
2095 ,p_trx_date IN ra_customer_trx.trx_date%TYPE
2096 ,p_check_override_only IN VARCHAR2 ) RETURN VARCHAR2 IS
2097
2098
2099 -- l_tax_classification_code varchar2(30);
2100 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
2101
2102 l_dummy CHAR;
2103
2104 BEGIN
2105 IF (g_level_statement >= g_current_runtime_level ) THEN
2106 FND_LOG.STRING(g_level_statement,
2107 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax.BEGIN',
2108 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_natural_acct_tax(+)');
2109 END IF;
2110
2111
2112 -- Get tax code from GL
2113 get_gl_tax_info ( p_ccid => p_ccid
2114 ,p_internal_organization_id => p_internal_organization_id
2115 ,p_trx_date => p_trx_date
2116 ,p_set_of_books_id => p_set_of_books_id
2117 ,p_check_override_only => p_check_override_only
2118 ,p_tax_classification_code => l_tax_classification_code
2119 ,p_override_flag => l_dummy );
2120
2121 IF (g_level_statement >= g_current_runtime_level ) THEN
2122 FND_LOG.STRING(g_level_statement,
2123 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax',
2124 '>>> O : Tax_classification_code = '||l_tax_classification_code);
2125 FND_LOG.STRING(g_level_statement,
2126 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax',
2127 '>>> O : Override_flag = '||l_dummy);
2128 FND_LOG.STRING(g_level_statement,
2129 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax.END',
2130 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_natural_acct_tax()-' );
2131 END IF;
2132
2133 RETURN l_tax_classification_code;
2134
2135 EXCEPTION
2136 WHEN OTHERS THEN
2137 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2138 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_natural_acct_tax- '||
2139 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2140 FND_MSG_PUB.Add;
2141
2142 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2143 FND_LOG.STRING(g_level_unexpected,
2144 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax',
2145 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2146 END IF;
2147 RAISE ;
2148
2149 END get_natural_acct_tax;
2150
2151 /*----------------------------------------------------------------------------*
2152 | PUBLIC FUNCTION |
2153 | get_gl_tax_info |
2154 | |
2155 | DESCRIPTION |
2156 | Given a table of Revenue Account CCIDs, Set_of_books_id and trx_date |
2157 | A distinct tax code if specified for the natural account of the |
2158 | Revenue accounts is found and is valid for the set_of_books_id and the |
2159 | trx_date, This function will return the tax_code and a status stating |
2160 | if the tax code is overrideable. |
2161 | |
2162 | If multiple revenue lines exist for the transaction line, All the |
2163 | Revenue account lines must have the same tax code(if any). The |
2164 | function will NOT return a tax code if multiple tax codes are found |
2165 | for the revenue lines. |
2166 | |
2167 | PARAMETERS |
2168 | CCID_table in NUMBER |
2169 | set_of_books_id in NUMBER |
2170 | trx_date in DATE |
2171 | check_override_only in DATE |
2172 | |
2173 | |
2174 | RETURNS |
2175 | Tax_Code: If a distinct tax code is found for the natural account and |
2176 | and is valid for the set_of_books_id and trx_date. |
2177 | Override_flag: Y, If the GL setup allows override of tax code, else N.|
2178 | |
2179 | CALLED FROM |
2180 | ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax() |
2181 | ARP_PROCESS_TAX.Validate_tax_info() |
2182 | |
2183 | HISTORY |
2184 | 25-Jul-97 Mahesh Sabapathy Created. |
2185 | 06-Jan-98 Mahesh Sabapathy Bugfix 604453: Exclude members of Tax Group |
2186 *----------------------------------------------------------------------------*/
2187
2188
2189 PROCEDURE get_gl_tax_info (
2190 p_ccid IN NUMBER
2191 ,p_internal_organization_id IN NUMBER
2192 ,p_trx_date IN DATE
2193 ,p_set_of_books_id IN NUMBER
2194 ,p_check_override_only IN CHAR
2195 ,p_tax_classification_code OUT NOCOPY VARCHAR2
2196 ,p_override_flag OUT NOCOPY CHAR
2197 ,p_validate_tax_code_flag IN BOOLEAN default TRUE) IS
2198
2199 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
2200
2201 l_override_flag CHAR;
2202 statement varchar2(2000);
2203
2204 BEGIN
2205
2206 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2207
2208 IF (g_level_statement >= g_current_runtime_level ) THEN
2209 FND_LOG.STRING(g_level_statement,
2210 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info.BEGIN',
2211 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_gl_tax_info()+' );
2212 END IF;
2213
2214 l_override_flag := NULL;
2215 l_tax_classification_code := NULL;
2216
2217 -- bug fix 3783241 begin
2218 /*----------------------------------------------------------------------+
2219 | Build IN clause for the Revenue Account(s) CCIDs. E.g.:( 1000, 1001 )|
2220 +----------------------------------------------------------------------*/
2221
2222 statement :=
2223 'Declare
2224 b_tax_code VARCHAR2(50);
2225 b_override_flag CHAR;
2226 Begin
2227 :b_tax_code := NULL;
2228 :b_override_flag := NULL;
2229 Begin
2230 -- See if accounts with Override tax code flag N have
2231 -- distinct tax codes.
2232 Select distinct tax_classification_code into :b_tax_code
2233 from gl_code_combinations gcc,
2234 zx_acct_tx_cls_defs_all gtoa
2235 where code_combination_id = :l_ccid ' ||
2236 ' and gcc.'||tax_gbl_rec.natural_acct_column||
2237 ' = gtoa.account_segment_value
2238 and gtoa.ledger_id = :l_set_of_books_id
2239 and gtoa.org_id = :l_org_id
2240 and gtoa.tax_class = ''OUTPUT''
2241 and nvl(gtoa.allow_tax_code_override_flag, ''Y'') = ''N'';
2242 :b_override_flag := ''N''; -- Override protected tax code found
2243 Exception
2244 When TOO_MANY_ROWS then
2245 :b_override_flag := ''N''; -- Override protected distinct tax code
2246 -- NOT found
2247 When NO_DATA_FOUND then
2248 :b_override_flag := ''Y''; -- Override protected accounts not found
2249 End;
2250
2251 --
2252 -- Distinct tax code with override flag N NOT found.
2253 --
2254 If ( :b_tax_code IS NULL and :b_check_override_only = ''N'' ) Then
2255 Begin
2256 Select distinct tax_classification_code into :b_tax_code
2257 from gl_code_combinations gcc,
2258 zx_acct_tx_cls_defs_all gtoa
2259 where code_combination_id = :l_ccid '||
2260 'and gcc.'||tax_gbl_rec.natural_acct_column||
2261 ' = gtoa.account_segment_value
2262 and gtoa.ledger_id = :l_set_of_books_id
2263 and gtoa.org_id = :l_org_id
2264 and gtoa.tax_class = ''OUTPUT'';
2265 Exception
2266 When TOO_MANY_ROWS OR NO_DATA_FOUND Then
2267 null; -- Distinct Tax code not found
2268 End;
2269 End If;
2270 End;';
2271
2272 IF (g_level_statement >= g_current_runtime_level ) THEN
2273 FND_LOG.STRING(g_level_statement,
2274 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2275 '-- statement = '||statement);
2276 END IF;
2277
2278 -- BugFix 936377
2279 -- Bug Fix 3254621 add in p_set_of_books_id, nvl(sysinfo.sysparam.org_id, -1)
2280 EXECUTE IMMEDIATE statement USING IN OUT l_tax_classification_code,
2281 IN OUT l_override_flag,
2282 p_ccid,
2283 p_set_of_books_id,
2284 p_internal_organization_id,
2285 p_check_override_only ;
2286
2287 IF (g_level_statement >= g_current_runtime_level ) THEN
2288 FND_LOG.STRING(g_level_statement,
2289 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2290 'l_tax_classification_code '||l_tax_classification_code);
2291 END IF;
2292
2293 p_tax_classification_code := l_tax_classification_code;
2294 p_override_flag := l_override_flag;
2295
2296 IF (g_level_statement >= g_current_runtime_level ) THEN
2297 FND_LOG.STRING(g_level_statement,
2298 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2299 '>>> O : Tax_classification_code = '||l_tax_classification_code);
2300 FND_LOG.STRING(g_level_statement,
2301 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2302 '>>> O : Override_flag = '||l_override_flag);
2303 FND_LOG.STRING(g_level_statement,
2304 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info.END',
2305 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_gl_tax_info()-' );
2306 END IF;
2307
2308 EXCEPTION
2309 WHEN OTHERS THEN
2310 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2311 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_gl_tax_info- '||
2312 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2313 FND_MSG_PUB.Add;
2314
2315 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2316 FND_LOG.STRING(g_level_unexpected,
2317 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2318 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2319 END IF;
2320 RAISE;
2321
2322 END get_gl_tax_info;
2323
2324 -- Bug#4090842- new procedure
2325 /*----------------------------------------------------------------------------*
2326 | PROCEDURE pop_ar_tax_info |
2327 | |
2328 | DESCRIPTION |
2329 | This procedure populates AR tax default option hierachies from |
2330 | zx_product_options |
2331 | |
2332 | RETURNS |
2333 | |
2334 | HISTORY |
2335 | |
2336 *----------------------------------------------------------------------------*/
2337
2338
2339 PROCEDURE pop_ar_tax_info(p_internal_organization_id IN NUMBER,
2340 p_application_id IN NUMBER,
2341 p_return_status OUT NOCOPY VARCHAR2)
2342 IS
2343 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
2344 l_functional_currency gl_sets_of_books.currency_code%type;
2345 l_base_precision fnd_currencies.precision%type;
2346 l_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
2347 l_master_org_id oe_system_parameters_all.master_organization_id%type;
2348 l_sob_test gl_sets_of_books.set_of_books_id%type;
2349
2350 CURSOR c_ar_product_options (c_org_id NUMBER,
2351 c_application_id NUMBER) IS
2352 SELECT org_id,
2353 def_option_hier_1_code,
2354 def_option_hier_2_code,
2355 def_option_hier_3_code,
2356 def_option_hier_4_code,
2357 def_option_hier_5_code,
2358 def_option_hier_6_code,
2359 def_option_hier_7_code,
2360 home_country_default_flag,
2361 tax_classification_code,
2362 tax_method_code,
2363 inclusive_tax_used_flag,
2364 tax_use_customer_exempt_flag,
2365 tax_use_product_exempt_flag,
2366 tax_use_loc_exc_rate_flag,
2367 tax_allow_compound_flag,
2368 tax_rounding_rule,
2369 tax_precision,
2370 tax_minimum_accountable_unit,
2371 use_tax_classification_flag,
2372 allow_tax_rounding_ovrd_flag
2373 FROM zx_product_options_all
2374 WHERE org_id = c_org_id
2375 AND application_id = c_application_id
2376 AND event_class_mapping_id IS NULL;
2377
2378 BEGIN
2379
2380 --
2381 -- Get tax default Info
2382 --
2383 IF (g_level_statement >= g_current_runtime_level ) THEN
2384 FND_LOG.STRING(g_level_statement,
2385 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_tax_info.BEGIN',
2386 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_ar_tax_info()+');
2387 END IF;
2388
2389 -- init return status
2390 p_return_status := FND_API.G_RET_STS_SUCCESS;
2391
2392 --
2393 -- Fetch AR Application Product Options
2394 -- OPEN c_product_options (to_number(substrb(userenv('CLIENT_INFO'),1,10)), 222);
2395
2396 OPEN c_ar_product_options (p_internal_organization_id, 222);
2397 FETCH c_ar_product_options
2398 INTO sysinfo.ar_product_options_rec.org_id,
2399 sysinfo.ar_product_options_rec.def_option_hier_1_code,
2400 sysinfo.ar_product_options_rec.def_option_hier_2_code,
2401 sysinfo.ar_product_options_rec.def_option_hier_3_code,
2402 sysinfo.ar_product_options_rec.def_option_hier_4_code,
2403 sysinfo.ar_product_options_rec.def_option_hier_5_code,
2404 sysinfo.ar_product_options_rec.def_option_hier_6_code,
2405 sysinfo.ar_product_options_rec.def_option_hier_7_code,
2406 sysinfo.ar_product_options_rec.home_country_default_flag,
2407 sysinfo.ar_product_options_rec.tax_classification_code,
2408 sysinfo.ar_product_options_rec.tax_method_code,
2409 sysinfo.ar_product_options_rec.inclusive_tax_used_flag,
2410 sysinfo.ar_product_options_rec.tax_use_customer_exempt_flag,
2411 sysinfo.ar_product_options_rec.tax_use_product_exempt_flag,
2412 sysinfo.ar_product_options_rec.tax_use_loc_exc_rate_flag,
2413 sysinfo.ar_product_options_rec.tax_allow_compound_flag,
2414 sysinfo.ar_product_options_rec.tax_rounding_rule,
2415 sysinfo.ar_product_options_rec.tax_precision,
2416 sysinfo.ar_product_options_rec.tax_minimum_accountable_unit,
2417 sysinfo.ar_product_options_rec.use_tax_classification_flag,
2418 sysinfo.ar_product_options_rec.allow_tax_rounding_ovrd_flag;
2419 CLOSE c_ar_product_options;
2420
2421
2422 sysinfo.sysparam.TAX_METHOD
2423 :=sysinfo.ar_product_options_rec.TAX_METHOD_CODE ;
2424 sysinfo.sysparam.ORG_ID
2425 :=sysinfo.ar_product_options_rec.ORG_ID ;
2426 sysinfo.sysparam.INCLUSIVE_TAX_USED
2427 :=sysinfo.ar_product_options_rec.INCLUSIVE_TAX_USED_FLAG ;
2428 sysinfo.sysparam.TAX_USE_CUSTOMER_EXEMPT_FLAG
2429 :=sysinfo.ar_product_options_rec.TAX_USE_CUSTOMER_EXEMPT_FLAG ;
2430 sysinfo.sysparam.TAX_USE_PRODUCT_EXEMPT_FLAG
2431 :=sysinfo.ar_product_options_rec.TAX_USE_PRODUCT_EXEMPT_FLAG ;
2432 sysinfo.sysparam.TAX_USE_LOC_EXC_RATE_FLAG
2433 :=sysinfo.ar_product_options_rec.TAX_USE_LOC_EXC_RATE_FLAG ;
2434 sysinfo.sysparam.TAX_ALLOW_COMPOUND_FLAG
2435 :=sysinfo.ar_product_options_rec.TAX_ALLOW_COMPOUND_FLAG ;
2436 sysinfo.sysparam.TAX_ROUNDING_RULE
2437 :=sysinfo.ar_product_options_rec.TAX_ROUNDING_RULE ;
2438 sysinfo.sysparam.TAX_MINIMUM_ACCOUNTABLE_UNIT
2439 :=sysinfo.ar_product_options_rec.TAX_MINIMUM_ACCOUNTABLE_UNIT ;
2440 sysinfo.sysparam.TAX_PRECISION
2441 :=sysinfo.ar_product_options_rec.TAX_PRECISION ;
2442 sysinfo.sysparam.TAX_ROUNDING_ALLOW_OVERRIDE
2443 := sysinfo.ar_product_options_rec.allow_tax_rounding_ovrd_flag;
2444
2445 --
2446 -- Bug#4625479- get default country code from ar_system_parameters
2447 --
2448 pop_ar_system_param_info(p_internal_organization_id,
2449 p_return_status);
2450
2451 IF (g_level_statement >= g_current_runtime_level ) THEN
2452 FND_LOG.STRING(g_level_statement,
2453 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_tax_info.END',
2454 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_ar_tax_info()-');
2455 END IF;
2456
2457 EXCEPTION
2458 WHEN OTHERS THEN
2459 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2460 IF c_ar_product_options%ISOPEN THEN
2461 CLOSE c_ar_product_options;
2462 END IF;
2463 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2464 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_ar_tax_info- '||
2465 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2466 FND_MSG_PUB.Add;
2467
2468 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2469 FND_LOG.STRING(g_level_unexpected,
2470 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_tax_info',
2471 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2472 END IF;
2473 END pop_ar_tax_info;
2474
2475 -- Bug#4090842- new procedure
2476 /*----------------------------------------------------------------------------*
2477 | PROCEDURE pop_pa_tax_info |
2478 | |
2479 | DESCRIPTION |
2480 | This procedure populates PA tax default option hierachies from |
2481 | zx_product_options |
2482 | |
2483 | RETURNS |
2484 | |
2485 | HISTORY |
2486 | |
2487 *----------------------------------------------------------------------------*/
2488
2489 PROCEDURE pop_pa_tax_info(p_internal_organization_id IN NUMBER,
2490 p_application_id IN NUMBER,
2491 p_return_status OUT NOCOPY VARCHAR2)
2492 IS
2493 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
2494 l_functional_currency gl_sets_of_books.currency_code%type;
2495 l_base_precision fnd_currencies.precision%type;
2496 l_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
2497 l_master_org_id oe_system_parameters_all.master_organization_id%type;
2498 l_sob_test gl_sets_of_books.set_of_books_id%type;
2499
2500 CURSOR c_pa_product_options (c_org_id NUMBER,
2501 c_application_id NUMBER) IS
2502 SELECT org_id,
2503 def_option_hier_1_code,
2504 def_option_hier_2_code,
2505 def_option_hier_3_code,
2506 def_option_hier_4_code,
2507 def_option_hier_5_code,
2508 def_option_hier_6_code,
2509 def_option_hier_7_code,
2510 home_country_default_flag,
2511 tax_classification_code,
2512 tax_method_code,
2513 inclusive_tax_used_flag,
2514 tax_use_customer_exempt_flag,
2515 tax_use_product_exempt_flag,
2516 tax_use_loc_exc_rate_flag,
2517 tax_allow_compound_flag,
2518 tax_rounding_rule,
2519 tax_precision,
2520 tax_minimum_accountable_unit,
2521 use_tax_classification_flag,
2522 allow_tax_rounding_ovrd_flag
2523 FROM zx_product_options_all
2524 WHERE org_id = c_org_id
2525 AND application_id = c_application_id
2526 AND event_class_mapping_id IS NULL;
2527
2528 BEGIN
2529
2530 --
2531 -- Get System Info
2532 --
2533
2534 IF (g_level_statement >= g_current_runtime_level ) THEN
2535 FND_LOG.STRING(g_level_statement,
2536 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_pa_tax_info.BEGIN',
2537 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_pa_tax_info()+');
2538 END IF;
2539
2540 -- init return status
2541 p_return_status := FND_API.G_RET_STS_SUCCESS;
2542
2543 --
2544 -- Fetch AR Application Product Options
2545 -- OPEN c_product_options (to_number(substrb(userenv('CLIENT_INFO'),1,10)), 275);
2546
2547 OPEN c_pa_product_options (p_internal_organization_id, 275);
2548 FETCH c_pa_product_options
2549 INTO sysinfo.pa_product_options_rec.org_id,
2550 sysinfo.pa_product_options_rec.def_option_hier_1_code,
2551 sysinfo.pa_product_options_rec.def_option_hier_2_code,
2552 sysinfo.pa_product_options_rec.def_option_hier_3_code,
2553 sysinfo.pa_product_options_rec.def_option_hier_4_code,
2554 sysinfo.pa_product_options_rec.def_option_hier_5_code,
2555 sysinfo.pa_product_options_rec.def_option_hier_6_code,
2556 sysinfo.pa_product_options_rec.def_option_hier_7_code,
2557 sysinfo.pa_product_options_rec.home_country_default_flag,
2558 sysinfo.pa_product_options_rec.tax_classification_code,
2559 sysinfo.pa_product_options_rec.tax_method_code,
2560 sysinfo.pa_product_options_rec.inclusive_tax_used_flag,
2561 sysinfo.pa_product_options_rec.tax_use_customer_exempt_flag,
2562 sysinfo.pa_product_options_rec.tax_use_product_exempt_flag,
2563 sysinfo.pa_product_options_rec.tax_use_loc_exc_rate_flag,
2564 sysinfo.pa_product_options_rec.tax_allow_compound_flag,
2565 sysinfo.pa_product_options_rec.tax_rounding_rule,
2566 sysinfo.pa_product_options_rec.tax_precision,
2567 sysinfo.pa_product_options_rec.tax_minimum_accountable_unit,
2568 sysinfo.pa_product_options_rec.use_tax_classification_flag,
2569 sysinfo.pa_product_options_rec.allow_tax_rounding_ovrd_flag;
2570 CLOSE c_pa_product_options;
2571
2572
2573
2574 sysinfo.sysparam.TAX_METHOD
2575 :=sysinfo.pa_product_options_rec.TAX_METHOD_CODE ;
2576 sysinfo.sysparam.ORG_ID
2577 :=sysinfo.pa_product_options_rec.ORG_ID ;
2578 sysinfo.sysparam.INCLUSIVE_TAX_USED
2579 :=sysinfo.pa_product_options_rec.INCLUSIVE_TAX_USED_FLAG ;
2580 sysinfo.sysparam.TAX_USE_CUSTOMER_EXEMPT_FLAG
2581 :=sysinfo.pa_product_options_rec.TAX_USE_CUSTOMER_EXEMPT_FLAG ;
2582 sysinfo.sysparam.TAX_USE_PRODUCT_EXEMPT_FLAG
2583 :=sysinfo.pa_product_options_rec.TAX_USE_PRODUCT_EXEMPT_FLAG ;
2584 sysinfo.sysparam.TAX_USE_LOC_EXC_RATE_FLAG
2585 :=sysinfo.pa_product_options_rec.TAX_USE_LOC_EXC_RATE_FLAG ;
2586 sysinfo.sysparam.TAX_ALLOW_COMPOUND_FLAG
2587 :=sysinfo.pa_product_options_rec.TAX_ALLOW_COMPOUND_FLAG ;
2588 sysinfo.sysparam.TAX_ROUNDING_RULE
2589 :=sysinfo.pa_product_options_rec.TAX_ROUNDING_RULE ;
2590 sysinfo.sysparam.TAX_MINIMUM_ACCOUNTABLE_UNIT
2591 :=sysinfo.pa_product_options_rec.TAX_MINIMUM_ACCOUNTABLE_UNIT ;
2592 sysinfo.sysparam.TAX_PRECISION
2593 :=sysinfo.pa_product_options_rec.TAX_PRECISION ;
2594 sysinfo.sysparam.TAX_ROUNDING_ALLOW_OVERRIDE
2595 := sysinfo.pa_product_options_rec.allow_tax_rounding_ovrd_flag;
2596
2597 --
2598 -- Bug#4625479- get default country code from ar_system_parameters
2599 --
2600 pop_ar_system_param_info(p_internal_organization_id,
2601 p_return_status);
2602
2603 IF (g_level_statement >= g_current_runtime_level ) THEN
2604 FND_LOG.STRING(g_level_statement,
2605 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_pa_tax_info.END',
2606 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_pa_tax_info()-');
2607 END IF;
2608
2609 EXCEPTION
2610 WHEN OTHERS THEN
2611 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2612 IF c_pa_product_options%ISOPEN THEN
2613 CLOSE c_pa_product_options;
2614 END IF;
2615 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2616 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_pa_tax_info- '||
2617 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2618 FND_MSG_PUB.Add;
2619
2620 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2621 FND_LOG.STRING(g_level_unexpected,
2622 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_pa_tax_info',
2623 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2624 END IF;
2625 END pop_pa_tax_info;
2626
2627
2628
2629
2630 -- Bug#4625479- new procedure
2631 /*----------------------------------------------------------------------------*
2632 | PROCEDURE pop_ar_system_param_info |
2633 | |
2634 | DESCRIPTION |
2635 | This procedure populates default country from ar_system_parameters |
2636 | |
2637 | RETURNS |
2638 | |
2639 | HISTORY |
2640 | |
2641 *----------------------------------------------------------------------------*/
2642
2643
2644 PROCEDURE pop_ar_system_param_info(p_internal_organization_id IN NUMBER,
2645 p_return_status OUT NOCOPY VARCHAR2)
2646 IS
2647 CURSOR c_ar_system_param(c_org_id NUMBER)
2648 IS
2649 SELECT default_country
2650 FROM ar_system_parameters_all
2651 WHERE org_id = c_org_id;
2652
2653 BEGIN
2654
2655 --
2656 -- Get default country Info
2657 --
2658 IF (g_level_statement >= g_current_runtime_level ) THEN
2659 FND_LOG.STRING(g_level_statement,
2660 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_system_param_info.BEGIN',
2661 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_ar_system_param_info()+');
2662 END IF;
2663
2664 -- init return status
2665 p_return_status := FND_API.G_RET_STS_SUCCESS;
2666
2667 --
2668 -- Fetch AR system parameters
2669 --
2670 OPEN c_ar_system_param(p_internal_organization_id);
2671 FETCH c_ar_system_param
2672 INTO sysinfo.sysparam.default_country;
2673 CLOSE c_ar_system_param;
2674
2675 IF (g_level_statement >= g_current_runtime_level ) THEN
2676 FND_LOG.STRING(g_level_statement,
2677 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_system_param_info',
2678 'default country: ' || sysinfo.sysparam.default_country);
2679 FND_LOG.STRING(g_level_statement,
2680 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_system_param_info.END',
2681 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_ar_system_param_info()-');
2682 END IF;
2683
2684 EXCEPTION
2685 WHEN OTHERS THEN
2686 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2687 IF c_ar_system_param%ISOPEN THEN
2688 CLOSE c_ar_system_param;
2689 END IF;
2690 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2691 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_ar_tax_info- '||
2692 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2693 FND_MSG_PUB.Add;
2694
2695 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2696 FND_LOG.STRING(g_level_unexpected,
2697 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_system_param_info',
2698 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2699 END IF;
2700 END pop_ar_system_param_info;
2701
2702
2703
2704
2705 -- Bug#4090842- change and split initialize to pop_ar_tax_info
2706 -- and pop_pa_tax_info
2707 /*----------------------------------------------------------------------------*
2708 | PROCEDURE INITIALIZE |
2709 | |
2710 | DESCRIPTION |
2711 | The Initialize will set System and Profile options required by the |
2712 | Tax Entity Handler and other functions in the global records sysinfo |
2713 | and profinfo. It will also the Tax Account Qualifier Segment and the |
2714 | Location tax code count in the global record tax_gbl_rec. |
2715 | |
2716 | RETURNS |
2717 | |
2718 | HISTORY |
2719 | |
2720 *----------------------------------------------------------------------------*/
2721
2722
2723 PROCEDURE initialize is
2724
2725 l_master_org_id oe_system_parameters_all.master_organization_id%type;
2726 l_sob_test gl_sets_of_books.set_of_books_id%type;
2727
2728 BEGIN
2729
2730 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2731
2732 IF (g_level_statement >= g_current_runtime_level ) THEN
2733 FND_LOG.STRING(g_level_statement,
2734 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.Initialize.BEGIN',
2735 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: Initialize()+');
2736 END IF;
2737
2738 sysinfo.pa_product_options_rec.ORG_ID := NULL;
2739 sysinfo.ar_product_options_rec.ORG_ID := NULL;
2740
2741 --
2742 -- Get Profile Info
2743 --
2744 -- bug 5120920 - use oe_sys_parameters.value();
2745
2746 g_org_id := mo_global.get_current_org_id;
2747
2748 IF g_org_id is not NULL then
2749
2750 l_master_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', g_org_id);
2751
2752 if l_master_org_id is NULL then
2753 IF (g_level_procedure >= g_current_runtime_level ) THEN
2754 FND_LOG.STRING(g_level_procedure,
2755 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize',
2756 'Error Getting OE MASTER_ORGANIZATION ID using mo_global.get_current_org_id');
2757 END IF;
2758 FND_MESSAGE.set_name('AR','AR_NO_OM_MASTER_ORG'); -- Bug 3151551
2759 APP_EXCEPTION.raise_exception;
2760 end if;
2761 profinfo.so_organization_id := l_master_org_id;
2762
2763 END IF;
2764
2765 --
2766 -- GL Natural Account info
2767 --
2768 BEGIN
2769 tax_gbl_rec.natural_acct_column := arp_flex.expand(arp_flex.gl,
2770 'GL_ACCOUNT', ',', '%COLUMN%');
2771 EXCEPTION
2772 WHEN OTHERS THEN
2773 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2774 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
2775 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize- '||
2776 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2777 FND_MSG_PUB.Add;
2778
2779 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2780 FND_LOG.STRING(g_level_unexpected,
2781 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize', 'Error Getting GL Natural Account Segment');
2782 FND_LOG.STRING(g_level_unexpected,
2783 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize',
2784 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2785 END IF;
2786
2787 /******* Bug#4655710
2788 WHEN OTHERS THEN
2789 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2790 FND_LOG.STRING(g_level_unexpected,
2791 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize',
2792 'Error Getting GL Natural Account Segment');
2793 END IF;
2794
2795 RAISE;
2796
2797 **********/
2798 END;
2799
2800 IF (g_level_statement >= g_current_runtime_level ) THEN
2801 FND_LOG.STRING(g_level_statement,
2802 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize.END',
2803 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: Initialize()-');
2804 END IF;
2805
2806 END initialize;
2807
2808 /*----------------------------------------------------------------------------*
2809 | PACKAGE CONSTRUCTOR |
2810 | |
2811 | DESCRIPTION |
2812 | The constructor will set System and Profile options required by the |
2813 | Tax Entity Handler and other functions in the global records sysinfo |
2814 | and profinfo. It will also the Tax Account Qualifier Segment and the |
2815 | Location tax code count in the global record tax_gbl_rec. |
2816 | |
2817 | RETURNS |
2818 | |
2819 | HISTORY |
2820 | |
2821 *----------------------------------------------------------------------------*/
2822 --
2823 -- Constructor code
2824 --
2825 BEGIN
2826
2827 initialize;
2828
2829 END ZX_AR_TAX_CLASSIFICATN_DEF_PKG;
2830
[Home] [Help]