1 package body ZX_AR_TAX_CLASSIFICATN_DEF_PKG as
2 /* $Header: zxartxclsdefpkgb.pls 120.28.12010000.2 2009/02/02 12:55:31 rajessub 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
379 | |
376 /*----------------------------------------------------------------------------*
377 | PUBLIC PROCEDURE |
378 | get_pa_default_classification |
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 | |
475 | Project Accounting's Tax Defaulting api |
472 | SCOPE: Public |
473 | |
474 | CALLED FROM: |
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
576 --
573 FOR i IN 1..l_count
574 Loop
575 IF (sysinfo.search_pa_hierarchy_tab(i) IS NULL) Then
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',
694 PA_TAX_CLIENT_EXTN_DRV.get_tax_code(
691 'Calling PA_TAX_CLIENT_EXTN_DRV.get_tax_code');
692 END IF;
693
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
807 | defaulting hierarchy option. This API will also use the user-defined
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
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
915
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;
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
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',
1022 'FUNC_SHORT_NAME: '||FUNC_SHORT_NAME );
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',
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
1138 'Exception: '||SQLCODE||' ; '||SQLERRM||' Setting sysinfo.tax_enforce_account_flag to N');
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',
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;
1239 FND_LOG.STRING(g_level_statement,
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
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');
1362 IF ( l_default_level = TAX_DEFAULT_SYSTEM ) THEN
1359 END IF;
1360
1361
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
1384
1385 IF (g_level_statement >= g_current_runtime_level ) THEN
1386 FND_LOG.STRING(g_level_statement,
1387 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1388 'defaulting tax classification from system ');
1389 END IF;
1390
1391 -- check if this tax calssification code is valid for the current
1392 -- transaction date
1393
1394 open c_chk_tax_classif_code(sysinfo.ar_product_options_rec.tax_classification_code,
1395 p_internal_organization_id,
1396 p_trx_date);
1397 fetch c_chk_tax_classif_code
1398 into v_tax_classification_code;
1399 close c_chk_tax_classif_code;
1400
1401 IF (g_level_statement >= g_current_runtime_level ) THEN
1402 FND_LOG.STRING(g_level_statement,
1403 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1404 'Tax classification defaulted from system is: v_tax_classification_code');
1405 END IF;
1406
1407
1408 END IF;
1409
1410
1411 -- END IF;
1412 END IF;
1413 END IF;
1414 END LOOP; -- Search tax defaulting hierarchy
1415
1416 /*****************
1417 IF ( v_tax_classification_code IS NULL ) THEN
1418 --
1419 -- Look for tax code of type 'LOCATION'
1420 --
1421 v_tax_classification_code := get_location_tax(site_use_id,
1422 p_party_flag,
1423 p_party_location_id);
1424 END IF;
1425 ***************/
1426
1427 p_tax_classification_code := v_tax_classification_code;
1428
1429 IF (g_level_statement >= g_current_runtime_level ) THEN
1430 FND_LOG.STRING(g_level_statement,
1431 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1432 'default_tax_classification: '||v_tax_classification_code);
1433 END IF;
1434
1435 /* Bug#4406011
1436 --
1437 -- If tax classification code is not found and use tax_classification_flag is enabled,
1438 -- raise NO_DATA_FOUND error
1439 --
1440 IF (v_tax_classification_code IS NULL) THEN
1441 RAISE NO_DATA_FOUND;
1442 END IF;
1443 */
1444
1445 IF (g_level_statement >= g_current_runtime_level ) THEN
1446 FND_LOG.STRING(g_level_statement,
1447 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification.END',
1448 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_default_tax_classification()-' );
1449 END IF;
1450
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN
1453 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1454 FND_LOG.STRING(g_level_unexpected,
1455 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1456 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1457 END IF;
1458 RAISE;
1459 WHEN OTHERS THEN
1460 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1461 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_default_tax_classification- '||
1462 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1463 FND_MSG_PUB.Add;
1464 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1465 FND_LOG.STRING(g_level_unexpected,
1466 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification',
1467 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1468 END IF;
1469 RAISE ;
1470 END get_default_tax_classification;
1471
1472
1473 /*----------------------------------------------------------------------------*
1474 | PRIVATE FUNCTION |
1475 | get_site_tax |
1476 | |
1477 | DESCRIPTION |
1478 | This function will look for any tax code that is specified at the site |
1479 | level if the system options allow use of tax codes at site level. It |
1483 | site_use_id in NUMBER |
1480 | will return the tax code if one is found for the Site id. |
1481 | |
1482 | PARAMETERS |
1484 | |
1485 | RETURNS |
1486 | tax code if one is found at the site level and valid for the trx date.|
1487 | null if a valid tax classification is not found. |
1488 | |
1489 | CALLED FROM |
1490 | get_default_tax_classification() |
1491 | |
1492 | HISTORY |
1493 | 27-NOV-95 Mahesh Sabapathy Created. |
1494 | 06-Jan-98 Mahesh Sabapathy Bugfix 604453: Exclude members of Tax Group|
1495 | 21-Jun-04 Sudhir Sekuri Bug 3611046 |
1496 *----------------------------------------------------------------------------*/
1497
1498 FUNCTION get_site_tax (
1499 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1500 p_trx_date IN ra_customer_trx.trx_date%TYPE)
1501 RETURN VARCHAR2 IS
1502
1503 l_cust_acct_site_id HZ_CUST_ACCT_SITES.CUST_ACCT_SITE_ID%TYPE;
1504 l_party_tax_profile_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
1505 l_zx_registration_rec ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC;
1506 l_tax_classification_code HZ_CUST_SITE_USES.tax_code%TYPE;
1507 l_ret_record_level VARCHAR2(30);
1508 l_return_status VARCHAR2(80);
1509 l_error_buffer VARCHAR2(100);
1510
1511 l_parent_ptp_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
1512 l_cust_account_id HZ_CUST_ACCT_SITES.CUST_ACCOUNT_ID%TYPE;
1513
1514 CURSOR get_site_tax_info
1515 (c_site_use_id hz_cust_site_uses.site_use_id%TYPE,
1516 c_org_id NUMBER,
1517 c_trx_date date)
1518 IS
1519 SELECT su.tax_code
1520 FROM HZ_CUST_SITE_USES_ALL su, ZX_OUTPUT_CLASSIFICATIONS_V l
1521 WHERE su.site_use_id = c_site_use_id
1522 AND su.org_id = c_org_id
1523 AND l.lookup_code = su.tax_code
1524 AND l.org_id IN (c_org_id, -99)
1525 AND l.enabled_flag = 'Y'
1526 AND (l.start_date_active <= c_trx_date OR
1527 l.start_date_active is null)
1528 AND (l.end_date_active >= c_trx_date OR
1529 l.end_date_active is null)
1530 AND rownum = 1
1531 ORDER BY l.org_id desc;
1532 -- rownum is added because there could be two potnetial rows returned, one for org_id -99
1533 -- and one for c_org_id
1534
1535
1536
1537
1538 BEGIN
1539
1540 IF (g_level_procedure >= g_current_runtime_level ) THEN
1541 FND_LOG.STRING(g_level_procedure,
1542 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax.BEGIN',
1543 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_site_tax(+)');
1544 END IF;
1545
1546 l_tax_classification_code := NULL;
1547
1548 -- IF ( sysinfo.sysparam.tax_use_site_exc_rate_flag = 'Y' ) THEN
1549
1550
1551 OPEN get_site_tax_info(p_site_use_id,
1552 g_org_id,
1553 p_trx_date);
1554 FETCH get_site_tax_info INTO l_tax_classification_code;
1555 CLOSE get_site_tax_info;
1556
1557
1558 IF (g_level_procedure >= g_current_runtime_level ) THEN
1559 FND_LOG.STRING(g_level_procedure,
1560 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1561 'tax_classification_code = ' ||
1562 l_tax_classification_code);
1563 FND_LOG.STRING(g_level_procedure,
1564 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1565 'l_return_status = ' || l_return_status);
1566 FND_LOG.STRING(g_level_procedure,
1567 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax.END',
1568 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_site_tax(-)');
1569 END IF;
1570
1571 RETURN l_tax_classification_code;
1572
1573 EXCEPTION
1574 WHEN OTHERS THEN
1575 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1576 l_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1577 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1578 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_site_tax- '|| l_error_buffer);
1579 FND_MSG_PUB.Add;
1580
1581 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1582 FND_LOG.STRING(g_level_unexpected,
1583 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_site_tax',
1584 l_error_buffer);
1585 END IF;
1586
1587
1588 RAISE ;
1589
1590 END get_site_tax;
1591
1592 /*----------------------------------------------------------------------------*
1593 | PRIVATE FUNCTION |
1594 | get_customer_tax |
1595 | |
1596 | DESCRIPTION |
1600 | the Customer id. It returns null if a default tax code is not found. |
1597 | This function will look for any tax code that is specified at the |
1598 | customer level if the system options allow use of tax codes at the |
1599 | customer level. The function returns the tax code if one is found for |
1601 | |
1602 | PARAMETERS |
1603 | site_use_id in NUMBER |
1604 | customer_id in NUMBER default null |
1605 | |
1606 | RETURNS |
1607 | tax code - if one is found at the Customer level,valid for the |
1608 | trx date |
1609 | null - if a valid tax code is not found. |
1610 | |
1611 | CALLED FROM |
1612 | get_default_tax_classification() |
1613 | |
1614 | HISTORY |
1615 | 27-NOV-95 Mahesh Sabapathy Created. |
1616 | 06-Jan-98 Mahesh Sabapathy Bugfix 604453: Exclude members of Tax Group|
1617 | 29-Feb-2000 Wei Feng, Bugfix 1205682: by changing the order of the FROM |
1618 | clause to have RA_CUSTORMER preceding AR_VAT_TAX. |
1619 *----------------------------------------------------------------------------*/
1620
1621 FUNCTION get_customer_tax (
1622 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1623 p_customer_id IN hz_cust_accounts.cust_account_id%TYPE,
1624 p_trx_date IN ra_customer_trx.trx_date%TYPE)
1625 RETURN VARCHAR2 IS
1626
1627 l_customer_id hz_cust_accounts.cust_account_id%TYPE;
1628 l_tax_classification_code HZ_CUST_ACCOUNTS.tax_code%TYPE;
1629
1630 l_ret_record_level VARCHAR2(30);
1631 l_return_status VARCHAR2(80);
1632 l_error_buffer VARCHAR2(100);
1633
1634
1635 CURSOR get_customer_id
1636 (c_site_use_id HZ_CUST_SITE_USES.site_use_id%TYPE)
1637 IS
1638 SELECT CUST_ACCT.cust_account_id
1639 FROM HZ_CUST_ACCOUNTS CUST_ACCT,
1640 HZ_CUST_ACCT_SITES CUST_ACCT_SITES,
1641 HZ_CUST_SITE_USES CUST_SITE_USES
1642 WHERE CUST_ACCT.cust_account_id = CUST_ACCT_SITES.cust_account_id
1643 AND CUST_ACCT_SITES.cust_acct_site_id = CUST_SITE_USES.cust_acct_site_id
1644 AND CUST_SITE_USES.site_use_id = c_site_use_id;
1645
1646
1647 CURSOR sel_customer_tax
1648 (c_customer_id HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
1649 c_org_id NUMBER,
1650 c_trx_date DATE)
1651 IS
1652 SELECT c.tax_code
1653 FROM HZ_CUST_ACCOUNTS_ALL c, ZX_OUTPUT_CLASSIFICATIONS_V l
1654 WHERE c.cust_account_id = c_customer_id
1655 AND c.org_id = c_org_id
1656 AND l.lookup_code = c.tax_code
1657 AND l.org_id IN (c_org_id, -99)
1658 AND l.enabled_flag = 'Y'
1659 AND (l.start_date_active <= c_trx_date OR
1660 l.start_date_active is null)
1661 AND (l.end_date_active >= c_trx_date OR
1662 l.end_date_active is null)
1663 AND rownum = 1
1664 ORDER BY l.org_id desc;
1665
1666
1667 BEGIN
1668
1669 IF (g_level_procedure >= g_current_runtime_level ) THEN
1670 FND_LOG.STRING(g_level_procedure,
1671 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax.BEGIN',
1672 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_customer_tax(+)');
1673 END IF;
1674
1675 l_tax_classification_code := NULL;
1676
1677 -- IF ( sysinfo.sysparam.tax_use_cust_exc_rate_flag = 'Y' ) THEN
1678 --
1679 -- If customer_id is not passed, then get customer_id using site_use_id
1680 --
1681 IF ( p_customer_id IS NOT NULL ) THEN
1682 l_customer_id := p_customer_id;
1683 ELSE
1684 --
1685 -- Get customer_id
1686 --
1687 OPEN get_customer_id(p_site_use_id);
1688 FETCH get_customer_id INTO l_customer_id;
1689 CLOSE get_customer_id;
1690
1691 END IF; -- Customer_id passed?
1692
1693 IF l_customer_id IS NOT NULL THEN
1694 OPEN sel_customer_tax(l_customer_id,
1695 g_org_id,
1696 p_trx_date);
1697 FETCH sel_customer_tax INTO l_tax_classification_code;
1698 CLOSE sel_customer_tax;
1699 END IF;
1700
1701 IF (g_level_procedure >= g_current_runtime_level ) THEN
1702 FND_LOG.STRING(g_level_procedure,
1703 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax',
1704 'tax_classification_code = ' ||
1705 l_tax_classification_code);
1706 FND_LOG.STRING(g_level_procedure,
1707 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax',
1708 'l_return_status = ' || l_return_status);
1709 FND_LOG.STRING(g_level_procedure,
1710 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax.END',
1711 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_customer_tax(-)');
1712 END IF;
1713
1717 WHEN OTHERS THEN
1714 RETURN l_tax_classification_code;
1715
1716 EXCEPTION
1718 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1719 l_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1720 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1721 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_customer_tax- '|| l_error_buffer);
1722 FND_MSG_PUB.Add;
1723
1724 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1725 FND_LOG.STRING(g_level_unexpected,
1726 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_customer_tax',
1727 l_error_buffer);
1728 END IF;
1729
1730 RAISE ;
1731
1732 END get_customer_tax;
1733
1734 /*----------------------------------------------------------------------------*
1735 | PRIVATE FUNCTION |
1736 | get_item_tax |
1737 | |
1738 | DESCRIPTION |
1739 | This function will look for any tax code that is specified at the |
1740 | item level if the system options allow use of tax codes at the item |
1741 | level. The function first looks at memo lines if a memo line id is |
1742 | passed and will look at items if a tax code was not found for memo lines|
1743 | The function returns the tax code if one is valid and returns null if |
1744 | one is not found. |
1745 | |
1746 | PARAMETERS |
1747 | organization_id in NUMBER |
1748 | item_id in NUMBER |
1749 | memo_line_id in NUMBER |
1750 | |
1751 | |
1752 | RETURNS |
1753 | tax code - if one is found at the Item or Memo line level,valid for |
1754 | the trx date |
1755 | null - if a valid tax code is not found. |
1756 | |
1757 | CALLED FROM |
1758 | get_default_tax_classification() |
1759 | |
1760 | HISTORY |
1761 | 27-NOV-95 Mahesh Sabapathy Created. |
1762 | 06-Jan-98 Mahesh Sabapathy Bugfix 604453: Exclude members of Tax Group |
1763 | 23-Jun-04 Sudhir Sekuri Bugfix 3611046 |
1764 *----------------------------------------------------------------------------*/
1765
1766 FUNCTION get_item_tax (
1767 p_item_id IN mtl_system_items.inventory_item_id%TYPE,
1768 p_organization_id IN mtl_system_items.organization_id%TYPE,
1769 p_trx_date IN DATE,
1770 p_memo_line_id IN ar_memo_lines.memo_line_id%TYPE default null
1771 ) RETURN VARCHAR2 IS
1772
1773 -- l_tax_classification_code varchar2(30);
1774 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
1775
1776 CURSOR sel_memo_line_tax(
1777 c_memo_line_id AR_MEMO_LINES.memo_line_id%type,
1778 c_trx_date DATE,
1779 c_org_id NUMBER)
1780 IS
1781 SELECT m.tax_code
1782 -- FROM fnd_lookups l, AR_MEMO_LINES M bug#4574838
1783 FROM zx_output_classifications_v l, AR_MEMO_LINES m
1784 WHERE m.memo_line_id = c_memo_line_id
1785 AND l.lookup_code = m.tax_code
1786 AND l.org_id IN (c_org_id, -99)
1787 AND l.enabled_flag = 'Y'
1788 AND (l.start_date_active <= c_trx_date OR
1789 l.start_date_active is null)
1790 AND (l.end_date_active >= c_trx_date OR
1791 l.end_date_active is null)
1792 --AND l.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
1793 AND rownum = 1
1794 ORDER BY l.org_id desc;
1795
1796 CURSOR sel_item_tax
1797 (c_item_id MTL_SYSTEM_ITEMS.inventory_item_id%type,
1798 c_organization_id MTL_SYSTEM_ITEMS.organization_id%type,
1799 c_trx_date DATE,
1800 c_org_id NUMBER)
1801 IS
1802 SELECT i.tax_code
1803 -- FROM fnd_lookups l, MTL_SYSTEM_ITEMS bug#4574838
1804 FROM zx_output_classifications_v l, MTL_SYSTEM_ITEMS i
1805 WHERE i.inventory_item_id = c_item_id
1806 AND i.organization_id = c_organization_id
1807 AND l.lookup_code = i.tax_code
1808 AND l.org_id IN (c_org_id, -99)
1809 AND l.enabled_flag = 'Y'
1810 AND (l.start_date_active <= c_trx_date OR
1811 l.start_date_active is null)
1812 AND (l.end_date_active >= c_trx_date OR
1813 l.end_date_active is null)
1814 -- AND l.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
1815 AND rownum = 1
1816 ORDER BY l.org_id desc;
1817
1818 BEGIN
1819
1820 IF (g_level_statement >= g_current_runtime_level ) THEN
1821 FND_LOG.STRING(g_level_statement,
1822 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_item_tax.BEGIN',
1823 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_item_tax()+' );
1824 END IF;
1825
1826 -- IF ( sysinfo.sysparam.tax_use_prod_exc_rate_flag = 'Y' ) THEN
1830 --
1827 --
1828 -- If Memo line id passed, look for memo line tax code and if notfound
1829 -- then look for item tax code.
1831 IF (p_memo_line_id IS NOT NULL) THEN
1832 --
1833 -- Bug#5331994- add trx_date and org_id
1834 --
1835 OPEN sel_memo_line_tax(
1836 p_memo_line_id,
1837 p_trx_date,
1838 g_org_id);
1839 FETCH sel_memo_line_tax INTO l_tax_classification_code;
1840 CLOSE sel_memo_line_tax;
1841 END IF; -- Memo line info passed?
1842
1843 IF (l_tax_classification_code IS NULL AND
1844 p_item_id IS NOT NULL) THEN
1845 --
1846 -- Couldn't find tax code for Memo lines, look for Item tax code
1847 --
1848 --
1849 -- Bug#5331994- add trx_date and org_id
1850 --
1851 OPEN sel_item_tax(
1852 p_item_id,
1853 p_organization_id,
1854 p_trx_date,
1855 g_org_id);
1856
1857 FETCH sel_item_tax INTO l_tax_classification_code;
1858 CLOSE sel_item_tax;
1859 END IF; -- Tax code not found and item_id passed?
1860 -- END IF;
1861
1862 IF (g_level_statement >= g_current_runtime_level ) THEN
1863 FND_LOG.STRING(g_level_statement,
1864 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_item_tax',
1865 '>>> O : Tax_classification_code = '||l_tax_classification_code);
1866 FND_LOG.STRING(g_level_statement,
1867 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_item_tax.END',
1868 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_item_tax()-' );
1869 END IF;
1870
1871 RETURN (l_tax_classification_code);
1872
1873 EXCEPTION
1874 WHEN OTHERS THEN
1875 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1876 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_item_tax- '||
1877 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1878 FND_MSG_PUB.Add;
1879
1880 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1881 FND_LOG.STRING(g_level_unexpected,
1882 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_item_tax',
1883 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1884 END IF;
1885 IF (sel_memo_line_tax%ISOPEN) THEN
1886 CLOSE sel_memo_line_tax;
1887 END IF;
1888 IF (sel_item_tax%ISOPEN) THEN
1889 CLOSE sel_item_tax;
1890 END IF;
1891 RAISE ;
1892
1893 END get_item_tax;
1894
1895 /*----------------------------------------------------------------------------*
1896 | PRIVATE FUNCTION |
1897 | get_location_tax |
1898 | |
1899 | DESCRIPTION |
1900 | This function will look for a valid tax code of type 'LOCATION' when |
1901 | the tax method is of type 'SALES TAX' |
1902 | The function returns the tax code if one is valid and returns null if |
1903 | one is not found. |
1904 | |
1905 | PARAMETERS |
1906 | set_of_books_id in NUMBER |
1907 | |
1908 | RETURNS |
1909 | tax code - if a tax code of type 'LOCATION' valid for the trx date |
1910 | is found. |
1911 | null - if a valid tax code of type 'LOCATION' is not found. |
1912 | |
1913 | CALLED FROM |
1914 | get_default_tax_classification() |
1915 | |
1916 | HISTORY |
1917 | 27-NOV-95 Mahesh Sabapathy Created. |
1918 | 23-Jun-04 Sudhir Sekuri Bugfix 3611046 |
1919 | 22-Sep-05 Phong La Bugfix 4625479: pass in p_product |
1920 | 30-Sep-05 Phong La Bugfix 3945805: do not this function |
1921 *----------------------------------------------------------------------------*/
1922 -- Bug#3945805
1923 /******************************
1924 FUNCTION get_location_tax (
1925 p_product IN VARCHAR2,
1926 p_site_use_id IN hz_cust_site_uses.site_use_id%TYPE,
1927 p_party_flag IN VARCHAR2,
1928 p_party_location_id IN hz_locations.location_id%type) RETURN VARCHAR2 IS
1929
1930 l_country hz_locations.country%TYPE := null;
1931 -- l_tax_classification_code ar_vat_tax.tax_code%TYPE := null;
1932
1933 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
1934
1935 CURSOR sel_addr_country(
1936 c_site_use_id HZ_CUST_SITE_USES.site_use_id%TYPE)
1937 IS
1938 SELECT loc.country
1939 FROM HZ_CUST_ACCT_SITES acct_site,
1940 HZ_PARTY_SITES party_site,
1941 HZ_LOCATIONS loc,
1942 HZ_CUST_SITE_USES site_uses
1943 WHERE site_uses.site_use_id = c_site_use_id
1944 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
1945 AND acct_site.party_site_id = party_site.party_site_id
1949 CURSOR sel_loc_country (
1946 AND loc.location_id = party_site.location_id;
1947
1948 --crm
1950 c_party_location_id HZ_LOCATIONS.location_id%TYPE)
1951 IS
1952 SELECT country
1953 FROM HZ_LOCATIONS
1954 WHERE location_id = c_party_location_id;
1955
1956 BEGIN
1957
1958 IF (g_level_statement >= g_current_runtime_level ) THEN
1959 FND_LOG.STRING(g_level_statement,
1960 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax.BEGIN',
1961 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_location_tax()+' );
1962 FND_LOG.STRING(g_level_statement,
1963 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1964 'p_site_use_id: '||to_char(p_site_use_id));
1965 FND_LOG.STRING(g_level_statement,
1966 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1967 'p_party_flag: '||p_party_flag);
1968 FND_LOG.STRING(g_level_statement,
1969 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1970 'p_party_location_id: '||p_party_location_id);
1971 FND_LOG.STRING(g_level_statement,
1972 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1973 'p_product: '||p_product);
1974 END IF;
1975
1976 l_tax_classification_code := NULL;
1977
1978 --crm
1979 IF (nvl(p_party_flag, 'N') = 'Y') THEN
1980 --
1981 -- Get Country code for party site location
1982 --
1983 OPEN sel_loc_country(p_party_location_id);
1984 FETCH sel_loc_country INTO l_country;
1985 CLOSE sel_loc_country;
1986 ELSE
1987 --
1988 -- Get Country code for the site
1989 --
1990 OPEN sel_addr_country(p_site_use_id);
1991 FETCH sel_addr_country INTO l_country;
1992 CLOSE sel_addr_country;
1993 END IF;
1994
1995 IF (g_level_statement >= g_current_runtime_level ) THEN
1996 FND_LOG.STRING(g_level_statement,
1997 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
1998 'Country code is : '||l_country);
1999 END IF;
2000
2001 --
2002 -- If tax method = 'Sales Tax' and Address is in the Home Country, then look
2003 -- for a valid tax code of type 'LOCATION'.
2004 --
2005 IF ( sysinfo.sysparam.default_country = l_country ) THEN
2006 IF p_product = 'AR' THEN
2007 l_tax_classification_code := sysinfo.ar_product_options_rec.tax_classification_code;
2008 ELSIF p_product = 'PA' THEN
2009 l_tax_classification_code := sysinfo.pa_product_options_rec.tax_classification_code;
2010 END IF;
2011
2012 END IF; -- Tax method is 'Sales Tax'?
2013
2014 IF (g_level_statement >= g_current_runtime_level ) THEN
2015 FND_LOG.STRING(g_level_statement,
2016 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
2017 '>>> O : Tax_classification_code = '||l_tax_classification_code);
2018 FND_LOG.STRING(g_level_statement,
2019 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax.END',
2020 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_location_tax()-' );
2021 END IF;
2022
2023 RETURN (l_tax_classification_code);
2024
2025 EXCEPTION
2026 WHEN OTHERS THEN
2027 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2028 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_location_tax- '||
2029 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2030 FND_MSG_PUB.Add;
2031
2032 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2033 FND_LOG.STRING(g_level_unexpected,
2034 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_location_tax',
2035 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2036 END IF;
2037
2038 RAISE ;
2039
2040 END get_location_tax;
2041 ***************************/
2042
2043 /*----------------------------------------------------------------------------*
2044 | PRIVATE FUNCTION |
2045 | get_natural_acct_tax |
2046 | |
2047 | DESCRIPTION |
2048 | This function will look for any tax code that is specified at the |
2049 | natural account segment of the Revenue account of a transaction line. |
2050 | The tax code, If specified for the natural account must be valid for |
2051 | the trx date and set of books id. The function will return a valid |
2052 | tax code if one is found. |
2053 | |
2054 | If multiple revenue lines exist for the transaction line, All the |
2055 | Revenue account lines must have the same tax code(if any). The |
2056 | function will NOT return a tax code if multiple tax codes are found |
2057 | for the revenue lines. |
2058 | |
2059 | PARAMETERS |
2060 | customer_trx_line_id in NUMBER |
2061 | set_of_books_id in NUMBER |
2062 | trx_date in DATE |
2063 | |
2067 | the trx date and set of books id. |
2064 | |
2065 | RETURNS |
2066 | tax code if one is found at the natural account level and is valid for|
2068 | null if a valid tax code is not found. |
2069 | |
2070 | CALLED FROM |
2071 | get_default_tax_classification() |
2072 | |
2073 | HISTORY |
2074 | 25-Jul-97 Mahesh Sabapathy Created. |
2075 *----------------------------------------------------------------------------*/
2076
2077 FUNCTION get_natural_acct_tax (
2078 p_ccid IN NUMBER
2079 ,p_internal_organization_id IN NUMBER
2080 ,p_set_of_books_id IN ar_system_parameters.set_of_books_id%TYPE
2081 ,p_trx_date IN ra_customer_trx.trx_date%TYPE
2082 ,p_check_override_only IN VARCHAR2 ) RETURN VARCHAR2 IS
2083
2084
2085 -- l_tax_classification_code varchar2(30);
2086 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
2087
2088 l_dummy CHAR;
2089
2090 BEGIN
2091 IF (g_level_statement >= g_current_runtime_level ) THEN
2092 FND_LOG.STRING(g_level_statement,
2093 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax.BEGIN',
2094 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_natural_acct_tax(+)');
2095 END IF;
2096
2097
2098 -- Get tax code from GL
2099 get_gl_tax_info ( p_ccid => p_ccid
2100 ,p_internal_organization_id => p_internal_organization_id
2101 ,p_trx_date => p_trx_date
2102 ,p_set_of_books_id => p_set_of_books_id
2103 ,p_check_override_only => p_check_override_only
2104 ,p_tax_classification_code => l_tax_classification_code
2105 ,p_override_flag => l_dummy );
2106
2107 IF (g_level_statement >= g_current_runtime_level ) THEN
2108 FND_LOG.STRING(g_level_statement,
2109 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax',
2110 '>>> O : Tax_classification_code = '||l_tax_classification_code);
2111 FND_LOG.STRING(g_level_statement,
2112 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax',
2113 '>>> O : Override_flag = '||l_dummy);
2114 FND_LOG.STRING(g_level_statement,
2115 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax.END',
2116 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_natural_acct_tax()-' );
2117 END IF;
2118
2119 RETURN l_tax_classification_code;
2120
2121 EXCEPTION
2122 WHEN OTHERS THEN
2123 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2124 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_natural_acct_tax- '||
2125 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2126 FND_MSG_PUB.Add;
2127
2128 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2129 FND_LOG.STRING(g_level_unexpected,
2130 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax',
2131 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2132 END IF;
2133 RAISE ;
2134
2135 END get_natural_acct_tax;
2136
2137 /*----------------------------------------------------------------------------*
2138 | PUBLIC FUNCTION |
2139 | get_gl_tax_info |
2140 | |
2141 | DESCRIPTION |
2142 | Given a table of Revenue Account CCIDs, Set_of_books_id and trx_date |
2143 | A distinct tax code if specified for the natural account of the |
2144 | Revenue accounts is found and is valid for the set_of_books_id and the |
2145 | trx_date, This function will return the tax_code and a status stating |
2146 | if the tax code is overrideable. |
2147 | |
2148 | If multiple revenue lines exist for the transaction line, All the |
2149 | Revenue account lines must have the same tax code(if any). The |
2150 | function will NOT return a tax code if multiple tax codes are found |
2151 | for the revenue lines. |
2152 | |
2153 | PARAMETERS |
2154 | CCID_table in NUMBER |
2155 | set_of_books_id in NUMBER |
2156 | trx_date in DATE |
2157 | check_override_only in DATE |
2158 | |
2159 | |
2160 | RETURNS |
2161 | Tax_Code: If a distinct tax code is found for the natural account and |
2162 | and is valid for the set_of_books_id and trx_date. |
2166 | ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_natural_acct_tax() |
2163 | Override_flag: Y, If the GL setup allows override of tax code, else N.|
2164 | |
2165 | CALLED FROM |
2167 | ARP_PROCESS_TAX.Validate_tax_info() |
2168 | |
2169 | HISTORY |
2170 | 25-Jul-97 Mahesh Sabapathy Created. |
2171 | 06-Jan-98 Mahesh Sabapathy Bugfix 604453: Exclude members of Tax Group |
2172 *----------------------------------------------------------------------------*/
2173
2174
2175 PROCEDURE get_gl_tax_info (
2176 p_ccid IN NUMBER
2177 ,p_internal_organization_id IN NUMBER
2178 ,p_trx_date IN DATE
2179 ,p_set_of_books_id IN NUMBER
2180 ,p_check_override_only IN CHAR
2181 ,p_tax_classification_code OUT NOCOPY VARCHAR2
2182 ,p_override_flag OUT NOCOPY CHAR
2183 ,p_validate_tax_code_flag IN BOOLEAN default TRUE) IS
2184
2185 l_tax_classification_code zx_lines_det_factors.output_tax_classification_code%TYPE;
2186
2187 l_override_flag CHAR;
2188 statement varchar2(2000);
2189
2190 BEGIN
2191
2192 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2193
2194 IF (g_level_statement >= g_current_runtime_level ) THEN
2195 FND_LOG.STRING(g_level_statement,
2196 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info.BEGIN',
2197 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_gl_tax_info()+' );
2198 END IF;
2199
2200 l_override_flag := NULL;
2201 l_tax_classification_code := NULL;
2202
2203 -- bug fix 3783241 begin
2204 /*----------------------------------------------------------------------+
2205 | Build IN clause for the Revenue Account(s) CCIDs. E.g.:( 1000, 1001 )|
2206 +----------------------------------------------------------------------*/
2207
2208 statement :=
2209 'Declare
2210 b_tax_code VARCHAR2(50);
2211 b_override_flag CHAR;
2212 Begin
2213 :b_tax_code := NULL;
2214 :b_override_flag := NULL;
2215 Begin
2216 -- See if accounts with Override tax code flag N have
2217 -- distinct tax codes.
2218 Select distinct tax_classification_code into :b_tax_code
2219 from gl_code_combinations gcc,
2220 zx_acct_tx_cls_defs_all gtoa
2221 where code_combination_id = :l_ccid ' ||
2222 ' and gcc.'||tax_gbl_rec.natural_acct_column||
2223 ' = gtoa.account_segment_value
2224 and gtoa.ledger_id = :l_set_of_books_id
2225 and gtoa.org_id = :l_org_id
2226 and gtoa.tax_class = ''O''
2227 and nvl(gtoa.allow_tax_code_override_flag, ''Y'') = ''N'';
2228 :b_override_flag := ''N''; -- Override protected tax code found
2229 Exception
2230 When TOO_MANY_ROWS then
2231 :b_override_flag := ''N''; -- Override protected distinct tax code
2232 -- NOT found
2233 When NO_DATA_FOUND then
2234 :b_override_flag := ''Y''; -- Override protected accounts not found
2235 End;
2236
2237 --
2238 -- Distinct tax code with override flag N NOT found.
2239 --
2240 If ( :b_tax_code IS NULL and :b_check_override_only = ''N'' ) Then
2241 Begin
2242 Select distinct tax_classification_code into :b_tax_code
2243 from gl_code_combinations gcc,
2244 zx_acct_tx_cls_defs_all gtoa
2245 where code_combination_id = :l_ccid '||
2246 'and gcc.'||tax_gbl_rec.natural_acct_column||
2247 ' = gtoa.account_segment_value
2248 and gtoa.ledger_id = :l_set_of_books_id
2249 and gtoa.org_id = :l_org_id
2250 and gtoa.tax_class = ''O'';
2251 Exception
2252 When TOO_MANY_ROWS OR NO_DATA_FOUND Then
2253 null; -- Distinct Tax code not found
2254 End;
2255 End If;
2256 End;';
2257
2258 IF (g_level_statement >= g_current_runtime_level ) THEN
2259 FND_LOG.STRING(g_level_statement,
2260 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2261 '-- statement = '||statement);
2262 END IF;
2263
2264 -- BugFix 936377
2265 -- Bug Fix 3254621 add in p_set_of_books_id, nvl(sysinfo.sysparam.org_id, -1)
2266 EXECUTE IMMEDIATE statement USING IN OUT l_tax_classification_code,
2267 IN OUT l_override_flag,
2268 p_ccid,
2269 p_set_of_books_id,
2270 p_internal_organization_id,
2271 p_check_override_only ;
2272
2273 IF (g_level_statement >= g_current_runtime_level ) THEN
2274 FND_LOG.STRING(g_level_statement,
2275 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2276 'l_tax_classification_code '||l_tax_classification_code);
2277 END IF;
2278
2279 p_tax_classification_code := l_tax_classification_code;
2280 p_override_flag := l_override_flag;
2281
2282 IF (g_level_statement >= g_current_runtime_level ) THEN
2283 FND_LOG.STRING(g_level_statement,
2284 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2288 '>>> O : Override_flag = '||l_override_flag);
2285 '>>> O : Tax_classification_code = '||l_tax_classification_code);
2286 FND_LOG.STRING(g_level_statement,
2287 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2289 FND_LOG.STRING(g_level_statement,
2290 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info.END',
2291 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: get_gl_tax_info()-' );
2292 END IF;
2293
2294 EXCEPTION
2295 WHEN OTHERS THEN
2296 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2297 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_gl_tax_info- '||
2298 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2299 FND_MSG_PUB.Add;
2300
2301 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2302 FND_LOG.STRING(g_level_unexpected,
2303 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_gl_tax_info',
2304 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2305 END IF;
2306 RAISE;
2307
2308 END get_gl_tax_info;
2309
2310 -- Bug#4090842- new procedure
2311 /*----------------------------------------------------------------------------*
2312 | PROCEDURE pop_ar_tax_info |
2313 | |
2314 | DESCRIPTION |
2315 | This procedure populates AR tax default option hierachies from |
2316 | zx_product_options |
2317 | |
2318 | RETURNS |
2319 | |
2320 | HISTORY |
2321 | |
2322 *----------------------------------------------------------------------------*/
2323
2324
2325 PROCEDURE pop_ar_tax_info(p_internal_organization_id IN NUMBER,
2326 p_application_id IN NUMBER,
2327 p_return_status OUT NOCOPY VARCHAR2)
2328 IS
2329 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
2330 l_functional_currency gl_sets_of_books.currency_code%type;
2331 l_base_precision fnd_currencies.precision%type;
2332 l_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
2333 l_master_org_id oe_system_parameters_all.master_organization_id%type;
2334 l_sob_test gl_sets_of_books.set_of_books_id%type;
2335
2336 CURSOR c_ar_product_options (c_org_id NUMBER,
2337 c_application_id NUMBER) IS
2338 SELECT org_id,
2339 def_option_hier_1_code,
2340 def_option_hier_2_code,
2341 def_option_hier_3_code,
2342 def_option_hier_4_code,
2343 def_option_hier_5_code,
2344 def_option_hier_6_code,
2345 def_option_hier_7_code,
2346 home_country_default_flag,
2347 tax_classification_code,
2348 tax_method_code,
2349 inclusive_tax_used_flag,
2350 tax_use_customer_exempt_flag,
2351 tax_use_product_exempt_flag,
2352 tax_use_loc_exc_rate_flag,
2353 tax_allow_compound_flag,
2354 tax_rounding_rule,
2355 tax_precision,
2356 tax_minimum_accountable_unit,
2357 use_tax_classification_flag,
2358 allow_tax_rounding_ovrd_flag
2359 FROM zx_product_options_all
2360 WHERE org_id = c_org_id
2361 AND application_id = c_application_id
2362 AND event_class_mapping_id IS NULL;
2363
2364 BEGIN
2365
2366 --
2367 -- Get tax default Info
2368 --
2369 IF (g_level_statement >= g_current_runtime_level ) THEN
2370 FND_LOG.STRING(g_level_statement,
2371 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_tax_info.BEGIN',
2372 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_ar_tax_info()+');
2373 END IF;
2374
2375 -- init return status
2376 p_return_status := FND_API.G_RET_STS_SUCCESS;
2377
2378 --
2379 -- Fetch AR Application Product Options
2380 -- OPEN c_product_options (to_number(substrb(userenv('CLIENT_INFO'),1,10)), 222);
2381
2382 OPEN c_ar_product_options (p_internal_organization_id, 222);
2383 FETCH c_ar_product_options
2384 INTO sysinfo.ar_product_options_rec.org_id,
2385 sysinfo.ar_product_options_rec.def_option_hier_1_code,
2386 sysinfo.ar_product_options_rec.def_option_hier_2_code,
2387 sysinfo.ar_product_options_rec.def_option_hier_3_code,
2388 sysinfo.ar_product_options_rec.def_option_hier_4_code,
2389 sysinfo.ar_product_options_rec.def_option_hier_5_code,
2390 sysinfo.ar_product_options_rec.def_option_hier_6_code,
2391 sysinfo.ar_product_options_rec.def_option_hier_7_code,
2392 sysinfo.ar_product_options_rec.home_country_default_flag,
2393 sysinfo.ar_product_options_rec.tax_classification_code,
2394 sysinfo.ar_product_options_rec.tax_method_code,
2395 sysinfo.ar_product_options_rec.inclusive_tax_used_flag,
2396 sysinfo.ar_product_options_rec.tax_use_customer_exempt_flag,
2397 sysinfo.ar_product_options_rec.tax_use_product_exempt_flag,
2398 sysinfo.ar_product_options_rec.tax_use_loc_exc_rate_flag,
2402 sysinfo.ar_product_options_rec.tax_minimum_accountable_unit,
2399 sysinfo.ar_product_options_rec.tax_allow_compound_flag,
2400 sysinfo.ar_product_options_rec.tax_rounding_rule,
2401 sysinfo.ar_product_options_rec.tax_precision,
2403 sysinfo.ar_product_options_rec.use_tax_classification_flag,
2404 sysinfo.ar_product_options_rec.allow_tax_rounding_ovrd_flag;
2405 CLOSE c_ar_product_options;
2406
2407
2408 sysinfo.sysparam.TAX_METHOD
2409 :=sysinfo.ar_product_options_rec.TAX_METHOD_CODE ;
2410 sysinfo.sysparam.ORG_ID
2411 :=sysinfo.ar_product_options_rec.ORG_ID ;
2412 sysinfo.sysparam.INCLUSIVE_TAX_USED
2413 :=sysinfo.ar_product_options_rec.INCLUSIVE_TAX_USED_FLAG ;
2414 sysinfo.sysparam.TAX_USE_CUSTOMER_EXEMPT_FLAG
2415 :=sysinfo.ar_product_options_rec.TAX_USE_CUSTOMER_EXEMPT_FLAG ;
2416 sysinfo.sysparam.TAX_USE_PRODUCT_EXEMPT_FLAG
2417 :=sysinfo.ar_product_options_rec.TAX_USE_PRODUCT_EXEMPT_FLAG ;
2418 sysinfo.sysparam.TAX_USE_LOC_EXC_RATE_FLAG
2419 :=sysinfo.ar_product_options_rec.TAX_USE_LOC_EXC_RATE_FLAG ;
2420 sysinfo.sysparam.TAX_ALLOW_COMPOUND_FLAG
2421 :=sysinfo.ar_product_options_rec.TAX_ALLOW_COMPOUND_FLAG ;
2422 sysinfo.sysparam.TAX_ROUNDING_RULE
2423 :=sysinfo.ar_product_options_rec.TAX_ROUNDING_RULE ;
2424 sysinfo.sysparam.TAX_MINIMUM_ACCOUNTABLE_UNIT
2425 :=sysinfo.ar_product_options_rec.TAX_MINIMUM_ACCOUNTABLE_UNIT ;
2426 sysinfo.sysparam.TAX_PRECISION
2427 :=sysinfo.ar_product_options_rec.TAX_PRECISION ;
2428 sysinfo.sysparam.TAX_ROUNDING_ALLOW_OVERRIDE
2429 := sysinfo.ar_product_options_rec.allow_tax_rounding_ovrd_flag;
2430
2431 --
2432 -- Bug#4625479- get default country code from ar_system_parameters
2433 --
2434 pop_ar_system_param_info(p_internal_organization_id,
2435 p_return_status);
2436
2437 IF (g_level_statement >= g_current_runtime_level ) THEN
2438 FND_LOG.STRING(g_level_statement,
2439 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_tax_info.END',
2440 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_ar_tax_info()-');
2441 END IF;
2442
2443 EXCEPTION
2444 WHEN OTHERS THEN
2445 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2446 IF c_ar_product_options%ISOPEN THEN
2447 CLOSE c_ar_product_options;
2448 END IF;
2449 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2450 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_ar_tax_info- '||
2451 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2452 FND_MSG_PUB.Add;
2453
2454 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2455 FND_LOG.STRING(g_level_unexpected,
2456 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_tax_info',
2457 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2458 END IF;
2459 END pop_ar_tax_info;
2460
2461 -- Bug#4090842- new procedure
2462 /*----------------------------------------------------------------------------*
2463 | PROCEDURE pop_pa_tax_info |
2464 | |
2465 | DESCRIPTION |
2466 | This procedure populates PA tax default option hierachies from |
2467 | zx_product_options |
2468 | |
2469 | RETURNS |
2470 | |
2471 | HISTORY |
2472 | |
2473 *----------------------------------------------------------------------------*/
2474
2475 PROCEDURE pop_pa_tax_info(p_internal_organization_id IN NUMBER,
2476 p_application_id IN NUMBER,
2477 p_return_status OUT NOCOPY VARCHAR2)
2478 IS
2479 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type;
2480 l_functional_currency gl_sets_of_books.currency_code%type;
2481 l_base_precision fnd_currencies.precision%type;
2482 l_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
2483 l_master_org_id oe_system_parameters_all.master_organization_id%type;
2484 l_sob_test gl_sets_of_books.set_of_books_id%type;
2485
2486 CURSOR c_pa_product_options (c_org_id NUMBER,
2487 c_application_id NUMBER) IS
2488 SELECT org_id,
2489 def_option_hier_1_code,
2490 def_option_hier_2_code,
2491 def_option_hier_3_code,
2492 def_option_hier_4_code,
2493 def_option_hier_5_code,
2494 def_option_hier_6_code,
2495 def_option_hier_7_code,
2496 home_country_default_flag,
2497 tax_classification_code,
2498 tax_method_code,
2499 inclusive_tax_used_flag,
2500 tax_use_customer_exempt_flag,
2501 tax_use_product_exempt_flag,
2502 tax_use_loc_exc_rate_flag,
2503 tax_allow_compound_flag,
2504 tax_rounding_rule,
2505 tax_precision,
2506 tax_minimum_accountable_unit,
2510 WHERE org_id = c_org_id
2507 use_tax_classification_flag,
2508 allow_tax_rounding_ovrd_flag
2509 FROM zx_product_options_all
2511 AND application_id = c_application_id
2512 AND event_class_mapping_id IS NULL;
2513
2514 BEGIN
2515
2516 --
2517 -- Get System Info
2518 --
2519
2520 IF (g_level_statement >= g_current_runtime_level ) THEN
2521 FND_LOG.STRING(g_level_statement,
2522 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_pa_tax_info.BEGIN',
2523 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_pa_tax_info()+');
2524 END IF;
2525
2526 -- init return status
2527 p_return_status := FND_API.G_RET_STS_SUCCESS;
2528
2529 --
2530 -- Fetch AR Application Product Options
2531 -- OPEN c_product_options (to_number(substrb(userenv('CLIENT_INFO'),1,10)), 275);
2532
2533 OPEN c_pa_product_options (p_internal_organization_id, 275);
2534 FETCH c_pa_product_options
2535 INTO sysinfo.pa_product_options_rec.org_id,
2536 sysinfo.pa_product_options_rec.def_option_hier_1_code,
2537 sysinfo.pa_product_options_rec.def_option_hier_2_code,
2538 sysinfo.pa_product_options_rec.def_option_hier_3_code,
2539 sysinfo.pa_product_options_rec.def_option_hier_4_code,
2540 sysinfo.pa_product_options_rec.def_option_hier_5_code,
2541 sysinfo.pa_product_options_rec.def_option_hier_6_code,
2542 sysinfo.pa_product_options_rec.def_option_hier_7_code,
2543 sysinfo.pa_product_options_rec.home_country_default_flag,
2544 sysinfo.pa_product_options_rec.tax_classification_code,
2545 sysinfo.pa_product_options_rec.tax_method_code,
2546 sysinfo.pa_product_options_rec.inclusive_tax_used_flag,
2547 sysinfo.pa_product_options_rec.tax_use_customer_exempt_flag,
2548 sysinfo.pa_product_options_rec.tax_use_product_exempt_flag,
2549 sysinfo.pa_product_options_rec.tax_use_loc_exc_rate_flag,
2550 sysinfo.pa_product_options_rec.tax_allow_compound_flag,
2551 sysinfo.pa_product_options_rec.tax_rounding_rule,
2552 sysinfo.pa_product_options_rec.tax_precision,
2553 sysinfo.pa_product_options_rec.tax_minimum_accountable_unit,
2554 sysinfo.pa_product_options_rec.use_tax_classification_flag,
2555 sysinfo.pa_product_options_rec.allow_tax_rounding_ovrd_flag;
2556 CLOSE c_pa_product_options;
2557
2558
2559
2560 sysinfo.sysparam.TAX_METHOD
2561 :=sysinfo.pa_product_options_rec.TAX_METHOD_CODE ;
2562 sysinfo.sysparam.ORG_ID
2563 :=sysinfo.pa_product_options_rec.ORG_ID ;
2564 sysinfo.sysparam.INCLUSIVE_TAX_USED
2565 :=sysinfo.pa_product_options_rec.INCLUSIVE_TAX_USED_FLAG ;
2566 sysinfo.sysparam.TAX_USE_CUSTOMER_EXEMPT_FLAG
2567 :=sysinfo.pa_product_options_rec.TAX_USE_CUSTOMER_EXEMPT_FLAG ;
2568 sysinfo.sysparam.TAX_USE_PRODUCT_EXEMPT_FLAG
2569 :=sysinfo.pa_product_options_rec.TAX_USE_PRODUCT_EXEMPT_FLAG ;
2570 sysinfo.sysparam.TAX_USE_LOC_EXC_RATE_FLAG
2571 :=sysinfo.pa_product_options_rec.TAX_USE_LOC_EXC_RATE_FLAG ;
2572 sysinfo.sysparam.TAX_ALLOW_COMPOUND_FLAG
2573 :=sysinfo.pa_product_options_rec.TAX_ALLOW_COMPOUND_FLAG ;
2574 sysinfo.sysparam.TAX_ROUNDING_RULE
2575 :=sysinfo.pa_product_options_rec.TAX_ROUNDING_RULE ;
2576 sysinfo.sysparam.TAX_MINIMUM_ACCOUNTABLE_UNIT
2577 :=sysinfo.pa_product_options_rec.TAX_MINIMUM_ACCOUNTABLE_UNIT ;
2578 sysinfo.sysparam.TAX_PRECISION
2579 :=sysinfo.pa_product_options_rec.TAX_PRECISION ;
2580 sysinfo.sysparam.TAX_ROUNDING_ALLOW_OVERRIDE
2581 := sysinfo.pa_product_options_rec.allow_tax_rounding_ovrd_flag;
2582
2583 --
2584 -- Bug#4625479- get default country code from ar_system_parameters
2585 --
2586 pop_ar_system_param_info(p_internal_organization_id,
2587 p_return_status);
2588
2589 IF (g_level_statement >= g_current_runtime_level ) THEN
2590 FND_LOG.STRING(g_level_statement,
2591 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_pa_tax_info.END',
2592 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_pa_tax_info()-');
2593 END IF;
2594
2595 EXCEPTION
2596 WHEN OTHERS THEN
2597 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2598 IF c_pa_product_options%ISOPEN THEN
2599 CLOSE c_pa_product_options;
2600 END IF;
2601 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2602 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_pa_tax_info- '||
2603 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2604 FND_MSG_PUB.Add;
2605
2606 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2607 FND_LOG.STRING(g_level_unexpected,
2608 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_pa_tax_info',
2609 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2610 END IF;
2611 END pop_pa_tax_info;
2612
2613
2614
2615
2616 -- Bug#4625479- new procedure
2617 /*----------------------------------------------------------------------------*
2618 | PROCEDURE pop_ar_system_param_info |
2619 | |
2620 | DESCRIPTION |
2624 | |
2621 | This procedure populates default country from ar_system_parameters |
2622 | |
2623 | RETURNS |
2625 | HISTORY |
2626 | |
2627 *----------------------------------------------------------------------------*/
2628
2629
2630 PROCEDURE pop_ar_system_param_info(p_internal_organization_id IN NUMBER,
2631 p_return_status OUT NOCOPY VARCHAR2)
2632 IS
2633 CURSOR c_ar_system_param(c_org_id NUMBER)
2634 IS
2635 SELECT default_country
2636 FROM ar_system_parameters_all
2637 WHERE org_id = c_org_id;
2638
2639 BEGIN
2640
2641 --
2642 -- Get default country Info
2643 --
2644 IF (g_level_statement >= g_current_runtime_level ) THEN
2645 FND_LOG.STRING(g_level_statement,
2646 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_system_param_info.BEGIN',
2647 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_ar_system_param_info()+');
2648 END IF;
2649
2650 -- init return status
2651 p_return_status := FND_API.G_RET_STS_SUCCESS;
2652
2653 --
2654 -- Fetch AR system parameters
2655 --
2656 OPEN c_ar_system_param(p_internal_organization_id);
2657 FETCH c_ar_system_param
2658 INTO sysinfo.sysparam.default_country;
2659 CLOSE c_ar_system_param;
2660
2661 IF (g_level_statement >= g_current_runtime_level ) THEN
2662 FND_LOG.STRING(g_level_statement,
2663 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_system_param_info',
2664 'default country: ' || sysinfo.sysparam.default_country);
2665 FND_LOG.STRING(g_level_statement,
2666 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_system_param_info.END',
2667 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: pop_ar_system_param_info()-');
2668 END IF;
2669
2670 EXCEPTION
2671 WHEN OTHERS THEN
2672 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2673 IF c_ar_system_param%ISOPEN THEN
2674 CLOSE c_ar_system_param;
2675 END IF;
2676 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2677 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', 'pop_ar_tax_info- '||
2678 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2679 FND_MSG_PUB.Add;
2680
2681 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2682 FND_LOG.STRING(g_level_unexpected,
2683 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.pop_ar_system_param_info',
2684 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2685 END IF;
2686 END pop_ar_system_param_info;
2687
2688
2689
2690
2691 -- Bug#4090842- change and split initialize to pop_ar_tax_info
2692 -- and pop_pa_tax_info
2693 /*----------------------------------------------------------------------------*
2694 | PROCEDURE INITIALIZE |
2695 | |
2696 | DESCRIPTION |
2697 | The Initialize will set System and Profile options required by the |
2698 | Tax Entity Handler and other functions in the global records sysinfo |
2699 | and profinfo. It will also the Tax Account Qualifier Segment and the |
2700 | Location tax code count in the global record tax_gbl_rec. |
2701 | |
2702 | RETURNS |
2703 | |
2704 | HISTORY |
2705 | |
2706 *----------------------------------------------------------------------------*/
2707
2708
2709 PROCEDURE initialize is
2710
2711 l_master_org_id oe_system_parameters_all.master_organization_id%type;
2712 l_sob_test gl_sets_of_books.set_of_books_id%type;
2713
2714 BEGIN
2715
2716 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2717
2718 IF (g_level_statement >= g_current_runtime_level ) THEN
2719 FND_LOG.STRING(g_level_statement,
2720 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.Initialize.BEGIN',
2721 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: Initialize()+');
2722 END IF;
2723
2724 sysinfo.pa_product_options_rec.ORG_ID := NULL;
2725 sysinfo.ar_product_options_rec.ORG_ID := NULL;
2726
2727 --
2728 -- Get Profile Info
2729 --
2730 -- bug 5120920 - use oe_sys_parameters.value();
2731
2732 g_org_id := mo_global.get_current_org_id;
2733
2734 IF g_org_id is not NULL then
2735
2736 l_master_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', g_org_id);
2737
2738 if l_master_org_id is NULL then
2739 IF (g_level_procedure >= g_current_runtime_level ) THEN
2740 FND_LOG.STRING(g_level_procedure,
2741 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize',
2742 'Error Getting OE MASTER_ORGANIZATION ID using mo_global.get_current_org_id');
2743 END IF;
2744 FND_MESSAGE.set_name('AR','AR_NO_OM_MASTER_ORG'); -- Bug 3151551
2745 APP_EXCEPTION.raise_exception;
2746 end if;
2747 profinfo.so_organization_id := l_master_org_id;
2748
2749 END IF;
2750
2751 --
2752 -- GL Natural Account info
2753 --
2754 BEGIN
2755 tax_gbl_rec.natural_acct_column := arp_flex.expand(arp_flex.gl,
2756 'GL_ACCOUNT', ',', '%COLUMN%');
2757 EXCEPTION
2758 WHEN OTHERS THEN
2759 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2760 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
2761 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize- '||
2762 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2763 FND_MSG_PUB.Add;
2764
2765 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2766 FND_LOG.STRING(g_level_unexpected,
2767 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize', 'Error Getting GL Natural Account Segment');
2768 FND_LOG.STRING(g_level_unexpected,
2769 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize',
2773 /******* Bug#4655710
2770 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
2771 END IF;
2772
2774 WHEN OTHERS THEN
2775 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2776 FND_LOG.STRING(g_level_unexpected,
2777 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize',
2778 'Error Getting GL Natural Account Segment');
2779 END IF;
2780
2781 RAISE;
2782
2783 **********/
2784 END;
2785
2786 IF (g_level_statement >= g_current_runtime_level ) THEN
2787 FND_LOG.STRING(g_level_statement,
2788 'ZX.PLSQL.ZX_AR_TAX_CLASSIFICATN_DEF_PKG.initialize.END',
2789 'ZX_AR_TAX_CLASSIFICATN_DEF_PKG: Initialize()-');
2790 END IF;
2791
2792 END initialize;
2793
2794 /*----------------------------------------------------------------------------*
2795 | PACKAGE CONSTRUCTOR |
2796 | |
2797 | DESCRIPTION |
2798 | The constructor will set System and Profile options required by the |
2799 | Tax Entity Handler and other functions in the global records sysinfo |
2800 | and profinfo. It will also the Tax Account Qualifier Segment and the |
2801 | Location tax code count in the global record tax_gbl_rec. |
2802 | |
2803 | RETURNS |
2804 | |
2805 | HISTORY |
2806 | |
2807 *----------------------------------------------------------------------------*/
2808 --
2809 -- Constructor code
2810 --
2811 BEGIN
2812
2813 initialize;
2814
2815 END ZX_AR_TAX_CLASSIFICATN_DEF_PKG;
2816
[Home] [Help]