[Home] [Help]
PACKAGE BODY: APPS.ZX_MIGRATE_TAX_DEF_COMMON
Source
1 PACKAGE BODY zx_migrate_tax_def_common AS
2 /* $Header: zxstaxdefmigb.pls 120.44.12010000.3 2008/09/24 06:46:52 tsen ship $ */
3
4 -- ****** GLOBAL VARIABLES ******
5 l_min_start_date DATE;
6 l_ap_min_start_date DATE;
7 l_ar_min_start_date DATE;
8 l_ap_count NUMBER;
9 l_ar_count NUMBER;
10 L_MULTI_ORG_FLAG FND_PRODUCT_GROUPS.MULTI_ORG_FLAG%TYPE;
11 L_ORG_ID NUMBER(15);
12
13 -- ****** PROCEDURES ******
14 PROCEDURE update_tax_status ;
15 PROCEDURE load_results_for_ap (p_tax_id NUMBER) AS
16 BEGIN
17
18 IF L_MULTI_ORG_FLAG = 'Y'
19 THEN
20
21 INSERT
22 INTO zx_update_criteria_results
23 (
24 tax_code_id,
25 org_id,
26 tax_code,
27 tax_class,
28 tax_regime_code,
29 tax,
30 tax_status_code,
31 recovery_type_code,
32 frozen,
33 country_code,
34 effective_from,
35 effective_to,
36 created_by,
37 creation_date,
38 last_updated_by,
39 last_update_date,
40 last_update_login
41 )
42 SELECT
43 codes.tax_id tax_code_id,
44 codes.org_id org_id,
45 codes.name tax_code,
46 'INPUT' tax_class,
47 case when codes.tax_type = 'USE'
48 then
49 Zx_Migrate_Util.GET_TAX_REGIME(
50 codes.tax_type,
51 codes.org_id)
52 else
53 Zx_Migrate_Util.Get_Country(codes.Org_Id)||'-Tax'
54 end tax_regime_code,
55 Nvl(CASE WHEN codes.global_attribute_category
56 IN ('JE.CZ.APXTADTC.TAX_ORIGIN','JE.HU.APXTADTC.TAX_ORIGIN','JE.PL.APXTADTC.TAX_ORIGIN','JE.CH.APXTADTC.TAX_INFO'
57 )
58 THEN
59 CASE WHEN lengthb (codes.global_attribute1) > 30
60 THEN
61 rtrim(substrb(CODES.GLOBAL_ATTRIBUTE1,1,24))||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_TAXES_B_S')
62 ELSE
63 CODES.GLOBAL_ATTRIBUTE1
64 END
65 END
66 ,
67 CASE WHEN codes.tax_type ='USE'
68 THEN
69 RTRIM(substrb(Zx_Migrate_Util.GET_TAX(
70 codes.name,
71 codes.tax_type),1,30))
72 ELSE
73 CASE WHEN
74 Zx_Migrate_Util.GET_TAX(
75 codes.name,
76 codes.tax_type) <> codes.tax_type
77 THEN
78 CASE WHEN
79 Lengthb(Zx_Migrate_Util.GET_TAX(
80 codes.name,
81 codes.tax_type)||'-'||codes.tax_type) > 30
82 THEN
83 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
84 codes.name,
85 codes.tax_type)||'-'||codes.tax_type,1,30))
86 ELSE
87 Zx_Migrate_Util.GET_TAX(
88 codes.name,
89 codes.tax_type)||'-'||codes.tax_type
90 END
91 ELSE
92 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
93 codes.name,
94 codes.tax_type),1,30))
95 END
96
97 END
98 ) tax,
99 DECODE(codes.global_attribute_category,
100 'JA.TW.APXTADTC.TAX_CODES',
101 nvl(codes.global_attribute1,'STANDARD'),
102 'STANDARD') tax_status_code,
103 NULL recovery_type_code, --Bug Fix 5028009
104 'N' frozen,
105 zx_migrate_util.get_country(codes.org_id) country_code,
106 codes.start_date effective_from,
107 codes.inactive_date effective_to,
108 fnd_global.user_id created_by,
109 sysdate creation_date,
110 fnd_global.user_id last_updated_by,
111 sysdate last_updated_date,
112 fnd_global.conc_login_id last_update_login
113 FROM ap_tax_codes_all codes,
114 financials_system_params_all fsp
115 WHERE codes.tax_type NOT IN ('AWT','TAX_GROUP','OFFSET')
116 AND codes.org_id = fsp.org_id
117 -- Sync process
118 AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
119 -- Rerunability
120 AND NOT EXISTS (SELECT 1
121 FROM zx_update_criteria_results zucr
122 WHERE zucr.tax_code_id = nvl(p_tax_id,codes.tax_id)
123 AND zucr.tax_class = 'INPUT'
124 );
125 ELSE
126
127 INSERT
128 INTO zx_update_criteria_results
129 (
130 tax_code_id,
131 org_id,
132 tax_code,
133 tax_class,
134 tax_regime_code,
135 tax,
136 tax_status_code,
137 recovery_type_code,
138 frozen,
139 country_code,
140 effective_from,
141 effective_to,
142 created_by,
143 creation_date,
144 last_updated_by,
145 last_update_date,
146 last_update_login
147 )
148 SELECT
149 codes.tax_id tax_code_id,
150 codes.org_id org_id,
151 codes.name tax_code,
152 'INPUT' tax_class,
153 case when codes.tax_type = 'USE'
154 then
155 Zx_Migrate_Util.GET_TAX_REGIME(
156 codes.tax_type,
157 codes.org_id)
158 else
159 Zx_Migrate_Util.Get_Country(codes.Org_Id)||'-Tax'
160 end tax_regime_code,
161 Nvl(CASE WHEN codes.global_attribute_category
162 IN ('JE.CZ.APXTADTC.TAX_ORIGIN','JE.HU.APXTADTC.TAX_ORIGIN','JE.PL.APXTADTC.TAX_ORIGIN','JE.CH.APXTADTC.TAX_INFO'
163 )
164 THEN
165 CASE WHEN lengthb (codes.global_attribute1) > 30
166 THEN
167 rtrim(substrb(CODES.GLOBAL_ATTRIBUTE1,1,24))||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_TAXES_B_S')
168 ELSE
169 CODES.GLOBAL_ATTRIBUTE1
170 END
171 END
172 ,
173 CASE WHEN codes.tax_type ='USE'
174 THEN
175 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
176 codes.name,
177 codes.tax_type),1,30))
178 ELSE
179 CASE WHEN
180 Zx_Migrate_Util.GET_TAX(
181 codes.name,
182 codes.tax_type) <> codes.tax_type
183 THEN
184 CASE WHEN
185 lengthb(Zx_Migrate_Util.GET_TAX(
186 codes.name,
187 codes.tax_type)||'-'||codes.tax_type) > 30
188 THEN
189 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
190 codes.name,
191 codes.tax_type)||'-'||codes.tax_type,1,30))
192 ELSE
193 Zx_Migrate_Util.GET_TAX(
194 codes.name,
195 codes.tax_type)||'-'||codes.tax_type
196 END
197 ELSE
198 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
199 codes.name,
200 codes.tax_type),1,30))
201 END
202
203 END
204 ) tax,
205 DECODE(codes.global_attribute_category,
206 'JA.TW.APXTADTC.TAX_CODES',
207 nvl(codes.global_attribute1,'STANDARD'),
208 'STANDARD') tax_status_code,
209 NULL recovery_type_code, --Bug Fix 5028009
210 'N' frozen,
211 zx_migrate_util.get_country(codes.org_id) country_code,
212 codes.start_date effective_from,
213 codes.inactive_date effective_to,
214 fnd_global.user_id created_by,
215 sysdate creation_date,
216 fnd_global.user_id last_updated_by,
217 sysdate last_updated_date,
218 fnd_global.conc_login_id last_update_login
219 FROM ap_tax_codes_all codes,
220 financials_system_params_all fsp
221 WHERE codes.tax_type NOT IN ('AWT','TAX_GROUP','OFFSET')
222 AND codes.org_id = fsp.org_id
223 AND codes.org_id = l_org_id
224 -- Sync process
225 AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
226 -- Rerunability
227 AND NOT EXISTS (SELECT 1
228 FROM zx_update_criteria_results zucr
229 WHERE zucr.tax_code_id = nvl(p_tax_id,codes.tax_id)
230 AND zucr.tax_class = 'INPUT'
231 );
232
233 END IF;
234 /*Insert rows for assigned offset tax codes into zx_update_criteria_results*/
235
236 IF L_MULTI_ORG_FLAG = 'Y'
237 THEN
238 INSERT
239 INTO zx_update_criteria_results
240 (
241 tax_code_id,
242 org_id,
243 tax_code,
244 tax_class,
245 tax_regime_code,
246 tax,
247 tax_status_code,
248 recovery_type_code,
249 frozen,
250 country_code,
251 effective_from,
252 effective_to,
253 created_by,
254 creation_date,
255 last_updated_by,
256 last_update_date,
257 last_update_login
258 )
259 SELECT
260 DISTINCT -->Bug 5868851
261 offset.tax_id tax_code_id,
262 offset.org_id org_id,
263 offset.name tax_code,
264 'INPUT' tax_class,
265 case when codes.tax_type = 'USE'
266 then
267 Zx_Migrate_Util.GET_TAX_REGIME(
268 codes.tax_type,
269 codes.org_id)
270 else
271 Zx_Migrate_Util.Get_Country(codes.Org_Id)||'-Tax'
272 end tax_regime_code,
273 NVL(CASE WHEN offset.global_attribute_category
274 IN ('JE.CZ.APXTADTC.TAX_ORIGIN','JE.HU.APXTADTC.TAX_ORIGIN','JE.PL.APXTADTC.TAX_ORIGIN','JE.CH.APXTADTC.TAX_INFO'
275 )
276 THEN
277 CASE WHEN lengthb (offset.global_attribute1) > 24
278 THEN
279 rtrim(substrb(offset.GLOBAL_ATTRIBUTE1,1,24))||'-OFFST'
280 ELSE
281 offset.GLOBAL_ATTRIBUTE1||'-OFFST'
282 END
283 END,
284 CASE WHEN
285 Zx_Migrate_Util.GET_TAX(
286 offset.name,
287 offset.tax_type)
288 <> offset.tax_type
289 THEN CASE WHEN LENGTHB(Zx_Migrate_Util.GET_TAX(
290 offset.name,
291 offset.tax_type)
292 ||'-OFFSET-'||offset.tax_type) > 30 THEN
293 RTRIM(SUBSTRB(
294 Zx_Migrate_Util.GET_TAX(
295 offset.name,
296 offset.tax_type)
297 ||'-OFFSET-'||offset.tax_type,1,30))
298 ELSE
299 Zx_Migrate_Util.GET_TAX(
300 offset.name,
301 offset.tax_type)
302 ||'-OFFSET-'||offset.tax_type
303 END
304 ELSE
305 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
306 offset.name,
307 offset.tax_type),1,24))
308 ||'-OFFST'
309 END )tax,
310 DECODE(offset.global_attribute_category,
311 'JA.TW.APXTADTC.TAX_CODES',
312 nvl(offset.global_attribute1,'STANDARD'),
313 'STANDARD') tax_status_code,
314 NULL recovery_type_code, --Bug Fix 5028009
315 'N' frozen,
316 zx_migrate_util.get_country(offset.org_id) country_code,
317 offset.start_date effective_from,
318 offset.inactive_date effective_to,
319 fnd_global.user_id created_by,
320 sysdate creation_date,
321 fnd_global.user_id last_updated_by,
322 sysdate last_updated_date,
323 fnd_global.conc_login_id last_update_login
324 FROM ap_tax_codes_all codes,
325 ap_tax_codes_all offset,
326 financials_system_params_all fsp
327 WHERE offset.tax_type = 'OFFSET'
328 AND offset.tax_id = codes.offset_tax_code_id
329 AND codes.offset_tax_code_id IS NOT NULL
330 AND codes.org_id = fsp.org_id
331 -- Sync process
332 AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
333 -- Rerunability
334 AND NOT EXISTS (SELECT 1
335 FROM zx_update_criteria_results zucr
336 WHERE zucr.tax_code_id = nvl(p_tax_id,offset.tax_id)
337 AND zucr.tax_class = 'INPUT'
338 );
339 ELSE
340
341
342 INSERT
343 INTO zx_update_criteria_results
344 (
345 tax_code_id,
346 org_id,
347 tax_code,
348 tax_class,
349 tax_regime_code,
350 tax,
351 tax_status_code,
352 recovery_type_code,
353 frozen,
354 country_code,
355 effective_from,
356 effective_to,
357 created_by,
358 creation_date,
359 last_updated_by,
363 SELECT
360 last_update_date,
361 last_update_login
362 )
364 DISTINCT -->Bug 5868851
365 offset.tax_id tax_code_id,
366 offset.org_id org_id,
367 offset.name tax_code,
368 'INPUT' tax_class,
369 case when codes.tax_type = 'USE'
370 then
371 Zx_Migrate_Util.GET_TAX_REGIME(
372 codes.tax_type,
373 codes.org_id)
374 else
375 Zx_Migrate_Util.Get_Country(codes.Org_Id)||'-Tax'
376 end tax_regime_code,
377 NVL(CASE WHEN offset.global_attribute_category
378 IN ('JE.CZ.APXTADTC.TAX_ORIGIN','JE.HU.APXTADTC.TAX_ORIGIN','JE.PL.APXTADTC.TAX_ORIGIN','JE.CH.APXTADTC.TAX_INFO'
379 )
380 THEN
381 CASE WHEN lengthb (offset.global_attribute1) > 24
382 THEN
383 rtrim(substrb(offset.GLOBAL_ATTRIBUTE1,1,24))||'-OFFST'
384 ELSE
385 offset.GLOBAL_ATTRIBUTE1||'-OFFST'
386 END
387 END,
388 CASE WHEN
389 Zx_Migrate_Util.GET_TAX(
390 offset.name,
391 offset.tax_type)
392 <> offset.tax_type
393 THEN CASE WHEN LENGTHB(
394 Zx_Migrate_Util.GET_TAX(
395 offset.name,
396 offset.tax_type)
397 ||'-OFFSET-'||offset.tax_type) > 30 THEN
398 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
399 offset.name,
400 offset.tax_type)
401 ||'-OFFSET-'||offset.tax_type,1,30))
402 ELSE
403 Zx_Migrate_Util.GET_TAX(
404 offset.name,
405 offset.tax_type)
406 ||'-OFFSET-'||offset.tax_type
407 END
408 ELSE
409 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
410 offset.name,
411 offset.tax_type),1,24))
412 ||'-OFFST'
413 END ) tax,
414 DECODE(offset.global_attribute_category,
415 'JA.TW.APXTADTC.TAX_CODES',
416 nvl(offset.global_attribute1,'STANDARD'),
417 'STANDARD') tax_status_code,
418 NULL recovery_type_code, --Bug Fix 5028009
419 'N' frozen,
420 zx_migrate_util.get_country(offset.org_id) country_code,
421 offset.start_date effective_from,
422 offset.inactive_date effective_to,
423 fnd_global.user_id created_by,
424 sysdate creation_date,
425 fnd_global.user_id last_updated_by,
426 sysdate last_updated_date,
427 fnd_global.conc_login_id last_update_login
428 FROM ap_tax_codes_all codes,
429 ap_tax_codes_all offset,
430 financials_system_params_all fsp
431 WHERE offset.tax_type = 'OFFSET'
432 AND offset.tax_id = codes.offset_tax_code_id
433 AND codes.offset_tax_code_id IS NOT NULL
434 AND codes.org_id = fsp.org_id
435 AND codes.org_id = l_org_id
436 -- Sync process
437 AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
438 -- Rerunability
439 AND NOT EXISTS (SELECT 1
440 FROM zx_update_criteria_results zucr
441 WHERE zucr.tax_code_id = nvl(p_tax_id,offset.tax_id)
442 AND zucr.tax_class = 'INPUT'
443 );
444
445 END IF;
446
447 /*Insert rows for un-assigned offset tax codes into zx_update_criteria_results*/
448 IF L_MULTI_ORG_FLAG = 'Y'
449 THEN
450 INSERT
451 INTO zx_update_criteria_results
452 (
453 tax_code_id,
454 org_id,
455 tax_code,
456 tax_class,
457 tax_regime_code,
458 tax,
459 tax_status_code,
460 recovery_type_code,
461 frozen,
462 country_code,
463 effective_from,
464 effective_to,
465 created_by,
466 creation_date,
467 last_updated_by,
468 last_update_date,
469 last_update_login
470 )
471 SELECT
472 offset.tax_id tax_code_id,
473 offset.org_id org_id,
474 offset.name tax_code,
475 'INPUT' tax_class,
476 Zx_Migrate_Util.Get_Country(Offset.Org_Id)||'-Tax' tax_regime_code,
477 NVL(CASE WHEN offset.global_attribute_category
478 IN ('JE.CZ.APXTADTC.TAX_ORIGIN','JE.HU.APXTADTC.TAX_ORIGIN','JE.PL.APXTADTC.TAX_ORIGIN','JE.CH.APXTADTC.TAX_INFO'
479 )
480 THEN
481 CASE WHEN lengthb (offset.global_attribute1) > 30
482 THEN
483 rtrim(substrb(offset.GLOBAL_ATTRIBUTE1,1,24))
484 ELSE
485 offset.GLOBAL_ATTRIBUTE1
486 END
487 END,
488 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
489 offset.name,
493 'JA.TW.APXTADTC.TAX_CODES',
490 offset.tax_type),1,24))
491 ) ||'-OFFST' tax,
492 DECODE(offset.global_attribute_category,
494 nvl(offset.global_attribute1,'STANDARD'),
495 'STANDARD') tax_status_code,
496 NULL recovery_type_code, --Bug Fix 5028009
497 'N' frozen,
498 zx_migrate_util.get_country(offset.org_id) country_code,
499 offset.start_date effective_from,
500 offset.inactive_date effective_to,
501 fnd_global.user_id created_by,
502 sysdate creation_date,
503 fnd_global.user_id last_updated_by,
504 sysdate last_updated_date,
505 fnd_global.conc_login_id last_update_login
506 FROM
507 ap_tax_codes_all offset,
508 financials_system_params_all fsp
509 WHERE offset.tax_type = 'OFFSET'
510 AND offset.org_id = fsp.org_id
511 AND not exists (select 1 from ap_tax_codes_all where
512 offset_tax_code_id = offset.tax_id)
513 -- Rerunability
514 AND NOT EXISTS (SELECT 1
515 FROM zx_update_criteria_results zucr
516 WHERE zucr.tax_code_id = nvl(p_tax_id,offset.tax_id)
517 AND zucr.tax_class = 'INPUT'
518 );
519 ELSE
520 INSERT
521 INTO zx_update_criteria_results
522 (
523 tax_code_id,
524 org_id,
525 tax_code,
526 tax_class,
527 tax_regime_code,
528 tax,
529 tax_status_code,
530 recovery_type_code,
531 frozen,
532 country_code,
533 effective_from,
534 effective_to,
535 created_by,
536 creation_date,
537 last_updated_by,
538 last_update_date,
539 last_update_login
540 )
541 SELECT
542 offset.tax_id tax_code_id,
543 offset.org_id org_id,
544 offset.name tax_code,
545 'INPUT' tax_class,
546 Zx_Migrate_Util.Get_Country(Offset.Org_Id)||'-Tax' tax_regime_code,
547 NVL(CASE WHEN offset.global_attribute_category
548 IN ('JE.CZ.APXTADTC.TAX_ORIGIN','JE.HU.APXTADTC.TAX_ORIGIN','JE.PL.APXTADTC.TAX_ORIGIN','JE.CH.APXTADTC.TAX_INFO'
549 )
550 THEN
551 CASE WHEN lengthb (offset.global_attribute1) > 30
552 THEN
553 rtrim(substrb(offset.GLOBAL_ATTRIBUTE1,1,24))
554 ELSE
555 offset.GLOBAL_ATTRIBUTE1
556 END
557 END,
558 rtrim(substrb(Zx_Migrate_Util.GET_TAX(
559 offset.name,
560 offset.tax_type),1,24))
561 ) ||'-OFFST' tax,
562 DECODE(offset.global_attribute_category,
563 'JA.TW.APXTADTC.TAX_CODES',
564 nvl(offset.global_attribute1,'STANDARD'),
565 'STANDARD') tax_status_code,
566 NULL recovery_type_code, --Bug Fix 5028009
567 'N' frozen,
568 zx_migrate_util.get_country(offset.org_id) country_code,
569 offset.start_date effective_from,
570 offset.inactive_date effective_to,
571 fnd_global.user_id created_by,
572 sysdate creation_date,
573 fnd_global.user_id last_updated_by,
574 sysdate last_updated_date,
575 fnd_global.conc_login_id last_update_login
576 FROM
577 ap_tax_codes_all offset,
578 financials_system_params_all fsp
579 WHERE offset.tax_type = 'OFFSET'
580 AND offset.org_id = fsp.org_id
581 AND offset.org_id = l_org_id
582 AND not exists (select 1 from ap_tax_codes_all where
583 offset_tax_code_id = offset.tax_id)
584 -- Rerunability
585 AND NOT EXISTS (SELECT 1
586 FROM zx_update_criteria_results zucr
587 WHERE zucr.tax_code_id = nvl(p_tax_id,offset.tax_id)
588 AND zucr.tax_class = 'INPUT'
589 );
590
591
592 END IF;
593 END load_results_for_ap;
594
595 PROCEDURE load_results_for_ar (p_tax_id NUMBER) AS
596 BEGIN
597
598 IF L_MULTI_ORG_FLAG = 'Y'
599 THEN
600 INSERT
601 INTO zx_update_criteria_results
602 (
603 tax_code_id,
604 org_id,
605 tax_code,
606 tax_class,
607 tax_regime_code,
608 tax,
609 tax_status_code,
610 recovery_type_code,
611 frozen,
612 country_code,
613 effective_from,
614 effective_to,
615 created_by,
616 creation_date,
617 last_updated_by,
618 last_update_date,
619 last_update_login
620 )
621 SELECT
622 codes.vat_tax_id tax_code_id,
623 codes.org_id org_id,
624 codes.tax_code tax_code,
625 'OUTPUT' tax_class,
626 -- Bug 4688151 : Populate LTE Tax Regimes
630 asp.global_attribute13 || '-' || codes.tax_type
627 CASE WHEN asp.global_attribute_category IN ('JL.AR.ARXSYSPA.SYS_PARAMETERS',
628 'JL.BR.ARXSYSPA.Additional Info',
629 'JL.CO.ARXSYSPA.SYS_PARAMETERS') THEN
631 ELSE
632 CASE WHEN codes.tax_type <> 'SALES_TAX' then
633 Zx_Migrate_Util.Get_Country(Codes.Org_Id)||'-Tax'
634 ELSE
635
636 Zx_Migrate_Util.GET_TAX_REGIME(
637 codes.tax_type,
638 codes.org_id)
639 END
640 END tax_regime_code,
641 -- YK:02/09/2005:Needs substrb
642 NVL(CASE WHEN codes.global_attribute_category IN ('JE.CZ.ARXSUVAT.TAX_ORIGIN',
643 'JE.HU.ARXSUVAT.TAX_ORIGIN',
644 'JE.PL.ARXSUVAT.TAX_ORIGIN')
645 THEN
646 CASE WHEN LENGTHB(codes.global_attribute1) > 30 THEN
647 RTRIM(SUBSTRB(codes.global_attribute1,1,24))
648 ELSE codes.global_attribute1
649 END
650 WHEN codes.global_attribute_category IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
651 'JL.BR.ARXSUVAT.Tax Information',
652 'JL.CO.ARXSUVAT.AR_VAT_TAX')
653 THEN (select tax_category
654 from jl_zz_ar_tx_categ_all
655 where TO_CHAR(tax_category_id) = codes.global_attribute1
656 and org_id = codes.org_id)
657 ELSE
658 NULL
659 END,
660 CASE WHEN codes.tax_type = Zx_Migrate_Util.GET_TAX(
661 codes.tax_code,
662 codes.tax_type)
663 THEN
664 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
665 codes.tax_code,
666 codes.tax_type),1,30))
667 ELSE
668 CASE WHEN LENGTHB(Zx_Migrate_Util.GET_TAX(
669 codes.tax_code,
670 codes.tax_type)||'-'||codes.tax_type) > 30
671 THEN
672 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
673 codes.tax_code,
674 codes.tax_type)||'-'||codes.tax_type,1,30))
675 ELSE
676 Zx_Migrate_Util.GET_TAX(
677 codes.tax_code,
678 codes.tax_type)||'-'||codes.tax_type
679 END
680 END
681
682 ) tax,
683 DECODE(codes.global_attribute_category,
684 'JA.TW.ARXSUVAT.VAT_TAX', nvl(codes.global_attribute1,'STANDARD'),
685 DECODE(codes.tax_class,
686 'O', 'STANDARD',
687 'I', 'STANDARD-AR-INPUT',
688 'STANDARD')) tax_status_code,
689 NULL recovery_type_code,
690 'N' frozen,
691 zx_migrate_util.get_country(codes.org_id) country_code,
692 codes.start_date effective_from,
693 codes.end_date effective_to,
694 fnd_global.user_id created_by,
695 sysdate creation_date,
696 fnd_global.user_id last_updated_by,
697 sysdate last_updated_date,
698 fnd_global.conc_login_id last_update_login
699 FROM ar_vat_tax_all_b codes,
700 ar_system_parameters_all asp
701 WHERE codes.tax_type not in ('TAX_GROUP', 'LOCATION')
702 AND asp.org_id = codes.org_id
703 -- Eliminate Tax Vendor Tax Codes
704 -- Bug 4880975 : Vendor tax codes other than tax type location
705 -- should also be loaded into results table.
706 -- AND asp.tax_database_view_set not in ('_A', '_V')
707 -- Eliminate LTE tax codes
708 -- Bug 4688151 : Do not eliminate LTE tax codes
709 -- For LTE Tax Codes regime name should come from JL tax category
710 -- AND (codes.global_attribute_category is null OR
711 -- codes.global_attribute_category NOT IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
712 -- 'JL.BR.ARXSUVAT.AR_VAT_TAX',
713 -- 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
714 -- Eliminate tax_type = 'LOCATION'
715 --Added following conditions for Sync process
716 AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
717 --Rerunability
718 AND NOT EXISTS (SELECT 1
719 FROM zx_update_criteria_results zucr
720 WHERE zucr.tax_code_id = nvl(p_tax_id,codes.vat_tax_id)
721 AND zucr.tax_class = 'OUTPUT'
722 );
723 ELSE
724 INSERT
725 INTO zx_update_criteria_results
726 (
727 tax_code_id,
728 org_id,
729 tax_code,
730 tax_class,
731 tax_regime_code,
732 tax,
733 tax_status_code,
734 recovery_type_code,
735 frozen,
736 country_code,
737 effective_from,
738 effective_to,
739 created_by,
740 creation_date,
741 last_updated_by,
742 last_update_date,
746 codes.vat_tax_id tax_code_id,
743 last_update_login
744 )
745 SELECT
747 codes.org_id org_id,
748 codes.tax_code tax_code,
749 'OUTPUT' tax_class,
750 -- Bug 4688151 : Populate LTE Tax Regimes
751 CASE WHEN asp.global_attribute_category IN ('JL.AR.ARXSYSPA.SYS_PARAMETERS',
752 'JL.BR.ARXSYSPA.Additional Info',
753 'JL.CO.ARXSYSPA.SYS_PARAMETERS') THEN
754 asp.global_attribute13 || '-' || codes.tax_type
755 ELSE
756 CASE WHEN codes.tax_type <> 'SALES_TAX' then
757 Zx_Migrate_Util.Get_Country(Codes.Org_Id)||'-Tax'
758 ELSE
759
760 Zx_Migrate_Util.GET_TAX_REGIME(
761 codes.tax_type,
762 codes.org_id)
763 END
764 END tax_regime_code,
765 -- YK:02/09/2005:Needs substrb
766 NVL(CASE WHEN codes.global_attribute_category IN ('JE.CZ.ARXSUVAT.TAX_ORIGIN',
767 'JE.HU.ARXSUVAT.TAX_ORIGIN',
768 'JE.PL.ARXSUVAT.TAX_ORIGIN')
769 THEN CASE WHEN LENGTHB(codes.global_attribute1) > 30 THEN
770 RTRIM(SUBSTRB(codes.global_attribute1,1,24))
771 ELSE codes.global_attribute1
772 END
773 WHEN codes.global_attribute_category IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
774 'JL.BR.ARXSUVAT.Tax Information',
775 'JL.CO.ARXSUVAT.AR_VAT_TAX')
776 THEN (select tax_category
777 from jl_zz_ar_tx_categ_all
778 where TO_CHAR(tax_category_id) = codes.global_attribute1
779 and org_id = codes.org_id)
780 ELSE
781 NULL
782 END,
783 CASE WHEN codes.tax_type = Zx_Migrate_Util.GET_TAX(
784 codes.tax_code,
785 codes.tax_type)
786 THEN
787 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
788 codes.tax_code,
789 codes.tax_type),1,30))
790 ELSE
791 CASE WHEN LENGTHB(Zx_Migrate_Util.GET_TAX(
792 codes.tax_code,
793 codes.tax_type)||'-'||codes.tax_type) > 30
794 THEN
795 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
796 codes.tax_code,
797 codes.tax_type)||'-'||codes.tax_type,1,30))
798 ELSE
799 Zx_Migrate_Util.GET_TAX(
800 codes.tax_code,
801 codes.tax_type)||'-'||codes.tax_type
802 END
803
804 END) tax,
805 DECODE(codes.global_attribute_category,
806 'JA.TW.ARXSUVAT.VAT_TAX', nvl(codes.global_attribute1,'STANDARD'),
807 DECODE(codes.tax_class,
808 'O', 'STANDARD',
809 'I', 'STANDARD-AR-INPUT',
810 'STANDARD')) tax_status_code,
811 NULL recovery_type_code,
812 'N' frozen,
813 zx_migrate_util.get_country(codes.org_id) country_code,
814 codes.start_date effective_from,
815 codes.end_date effective_to,
816 fnd_global.user_id created_by,
817 sysdate creation_date,
818 fnd_global.user_id last_updated_by,
819 sysdate last_updated_date,
820 fnd_global.conc_login_id last_update_login
821 FROM ar_vat_tax_all_b codes,
822 ar_system_parameters_all asp
823 WHERE codes.tax_type not in ('TAX_GROUP', 'LOCATION')
824 AND asp.org_id = codes.org_id
825 AND asp.org_id = l_org_id
826 -- Eliminate Tax Vendor Tax Codes
827 -- Bug 4880975 : Vendor tax codes other than tax type location
828 -- should also be loaded into results table.
829 -- AND asp.tax_database_view_set not in ('_A', '_V')
830 -- Eliminate LTE tax codes
831 -- Bug 4688151 : Do not eliminate LTE tax codes
832 -- For LTE Tax Codes regime name should come from JL tax category
833 -- AND (codes.global_attribute_category is null OR
834 -- codes.global_attribute_category NOT IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
835 -- 'JL.BR.ARXSUVAT.AR_VAT_TAX',
836 -- 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
837 -- Eliminate tax_type = 'LOCATION'
838 --Added following conditions for Sync process
839 AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
840 --Rerunability
841 AND NOT EXISTS (SELECT 1
842 FROM zx_update_criteria_results zucr
843 WHERE zucr.tax_code_id = nvl(p_tax_id,codes.vat_tax_id)
844 AND zucr.tax_class = 'OUTPUT'
845 );
846
847 END IF;
848
849 update_tax_status;
850
851 END load_results_for_ar;
852
853 /*===========================================================================+
854 | PROCEDURE
855 | load_tax_comp_results_for_ar
856 |
857 | DESCRIPTION
861 | ASSUMPTION:
858 | 1. Populates data into zx_update_criteria_results table based on AR data in
859 | zx_tax_relations_t .
860 |
862 | Since only AR related tax codes get migrated into zx_tax_priorities_t we do
863 | not have a load_tax_results_for_ap procedure.
864 |
865 |
866 |
867 | MODIFICATION HISTORY
868 | 04/22/2005 Arnab Sengupta
869 |
870 +==========================================================================*/
871
872 PROCEDURE load_tax_comp_results_for_ar (p_tax_id NUMBER) AS
873 BEGIN
874
875 /*Include this call to populate zx_tax_priorities_t before loading the results table
876 Bug 5691957 */
877 BEGIN
878 zx_tcm_compound_pkg.main;
879 EXCEPTION WHEN OTHERS THEN
880 NULL;
881 END;
882
883 IF L_MULTI_ORG_FLAG = 'Y'
884 THEN
885 INSERT
886 INTO zx_update_criteria_results
887 (
888 tax_code_id,
889 org_id,
890 tax_code,
891 tax_class,
892 tax_regime_code,
893 tax,
894 tax_precedence,
895 regime_precedence,
896 tax_status_code,
897 recovery_type_code,
898 frozen,
899 country_code,
900 effective_from,
901 effective_to,
902 created_by,
903 creation_date,
904 last_updated_by,
905 last_update_date,
906 last_update_login
907 )
908 SELECT
909 codes.vat_tax_id tax_code_id,
910 codes.org_id org_id,
911 codes.tax_code tax_code,
912 'OUTPUT' tax_class,
913 CASE WHEN codes.tax_type = 'VAT' then
914 Zx_Migrate_Util.Get_Country(Codes.Org_Id)||'-Tax'
915 ELSE
916
917 Zx_Migrate_Util.GET_TAX_REGIME(
918 codes.tax_type,
919 codes.org_id)
920 END
921 tax_regime_code,
922 zxpt.tax_code tax,
923 zxpt.tax_precedence tax_precedence,
924 zxpt.regime_precedence regime_precedence,
925 DECODE(codes.global_attribute_category,
926 'JA.TW.ARXSUVAT.VAT_TAX', nvl(codes.global_attribute1,'STANDARD'),
927 DECODE(codes.tax_class,
928 'O', 'STANDARD',
929 'I', 'STANDARD-AR-INPUT',
930 'STANDARD')) tax_status_code,
931 NULL recovery_type_code,
932 'N' frozen,
933 zx_migrate_util.get_country(codes.org_id) country_code,
934 codes.start_date effective_from,
935 codes.end_date effective_to,
936 fnd_global.user_id created_by,
937 sysdate creation_date,
938 fnd_global.user_id last_updated_by,
939 sysdate last_updated_date,
940 fnd_global.conc_login_id last_update_login
941 FROM ar_vat_tax_all_b codes,
942 ar_system_parameters_all asp,
943 zx_tax_priorities_t zxpt
944
945 WHERE
946 asp.org_id = codes.org_id
947 AND codes.vat_tax_id = zxpt.tax_id
948 -- Eliminate Tax Vendor Tax Codes
949 AND asp.tax_database_view_set = 'O'
950 -- Eliminate LTE tax codes
951 AND (codes.global_attribute_category is null OR
952 codes.global_attribute_category NOT IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
953 'JL.BR.ARXSUVAT.AR_VAT_TAX',
954 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
955 -- Eliminate tax_type = 'LOCATION'
956 AND codes.tax_type <> 'LOCATION'
957 --Added following conditions for Sync process
958 AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
959 --Rerunability
960 AND NOT EXISTS (SELECT 1
961 FROM zx_update_criteria_results zucr
962 WHERE zucr.tax_code_id = nvl(p_tax_id,codes.vat_tax_id)
963 AND zucr.tax_class = 'OUTPUT'
964 );
965 ELSE
966
967 INSERT
968 INTO zx_update_criteria_results
969 (
970 tax_code_id,
971 org_id,
972 tax_code,
973 tax_class,
974 tax_regime_code,
975 tax,
976 tax_precedence,
977 regime_precedence,
978 tax_status_code,
979 recovery_type_code,
980 frozen,
981 country_code,
982 effective_from,
983 effective_to,
984 created_by,
985 creation_date,
986 last_updated_by,
987 last_update_date,
988 last_update_login
989 )
990 SELECT
991 codes.vat_tax_id tax_code_id,
992 codes.org_id org_id,
993 codes.tax_code tax_code,
994 'OUTPUT' tax_class,
995 CASE WHEN codes.tax_type = 'VAT' then
996 Zx_Migrate_Util.Get_Country(Codes.Org_Id)||'-Tax'
997 ELSE
998
999 Zx_Migrate_Util.GET_TAX_REGIME(
1000 codes.tax_type,
1001 codes.org_id)
1002 END
1003 tax_regime_code,
1004 zxpt.tax_code tax,
1008 'JA.TW.ARXSUVAT.VAT_TAX', nvl(codes.global_attribute1,'STANDARD'),
1005 zxpt.tax_precedence tax_precedence,
1006 zxpt.regime_precedence regime_precedence,
1007 DECODE(codes.global_attribute_category,
1009 DECODE(codes.tax_class,
1010 'O', 'STANDARD',
1011 'I', 'STANDARD-AR-INPUT',
1012 'STANDARD')) tax_status_code,
1013 NULL recovery_type_code,
1014 'N' frozen,
1015 zx_migrate_util.get_country(codes.org_id) country_code,
1016 codes.start_date effective_from,
1017 codes.end_date effective_to,
1018 fnd_global.user_id created_by,
1019 sysdate creation_date,
1020 fnd_global.user_id last_updated_by,
1021 sysdate last_updated_date,
1022 fnd_global.conc_login_id last_update_login
1023 FROM ar_vat_tax_all_b codes,
1024 ar_system_parameters_all asp,
1025 zx_tax_priorities_t zxpt
1026
1027 WHERE
1028 asp.org_id = codes.org_id
1029 AND codes.org_id = l_org_id
1030 AND codes.vat_tax_id = zxpt.tax_id
1031 -- Eliminate Tax Vendor Tax Codes
1032 AND asp.tax_database_view_set = 'O'
1033 -- Eliminate LTE tax codes
1034 AND (codes.global_attribute_category is null OR
1035 codes.global_attribute_category NOT IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
1036 'JL.BR.ARXSUVAT.AR_VAT_TAX',
1037 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
1038 -- Eliminate tax_type = 'LOCATION'
1039 AND codes.tax_type <> 'LOCATION'
1040 --Added following conditions for Sync process
1041 AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
1042 --Rerunability
1043 AND NOT EXISTS (SELECT 1
1044 FROM zx_update_criteria_results zucr
1045 WHERE zucr.tax_code_id = nvl(p_tax_id,codes.vat_tax_id)
1046 AND zucr.tax_class = 'OUTPUT'
1047 );
1048 END IF;
1049 END load_tax_comp_results_for_ar;
1050
1051
1052 /*===========================================================================+
1053 | PROCEDURE
1054 | load_results_for_intercomp_ap
1055 |
1056 | DESCRIPTION
1057 | Populates data into zx_update_criteria_results table for AP Tax Codes
1058 | that is used in intercompany transaction.
1059 |
1060 | MTL_INTERCOMPANY_PARAMTERS table stores information about two OUs that
1061 | are used for intercompany transactions. The customer related information is
1062 | used by the shipping organization (SHIP_ORGANIZATION_ID) for AR invoicing
1063 | purposes. The supplier related information is used by the selling organization
1064 | (SELL_ORGANIZATION_ID) for AP invoicing purposes.
1065 |
1066 | Tax Regime Code derived from AP Tax Code (used to create AP invoice) is
1067 | overriden by that of AR Tax Code (used to create AR invoice).
1068 |
1069 | Set zx_criteria_results.intercompany_flag to 'Y' for AP Tax Codes/AR Tax Codes
1070 | that are used for intercompany transactions.
1071 |
1072 |
1073 | MODIFICATION HISTORY
1074 | 04/29/2005 Yoshimichi Konishi Created
1075 |
1076 +==========================================================================*/
1077 PROCEDURE load_results_for_intercomp_ap (p_tax_id NUMBER) AS
1078 BEGIN
1079 INSERT
1080 INTO zx_update_criteria_results
1081 (
1082 tax_code_id,
1083 org_id,
1084 tax_code,
1085 tax_class,
1086 tax_regime_code,
1087 tax,
1088 tax_status_code,
1089 recovery_type_code,
1090 frozen,
1091 country_code,
1092 created_by,
1093 creation_date,
1094 last_updated_by,
1095 last_update_date,
1096 last_update_login,
1097 effective_from,
1098 effective_to,
1099 intercompany_flag
1100 )
1101 SELECT
1102 DISTINCT
1103 ap_codes.tax_id tax_code_id,
1104 ap_codes.org_id org_id,
1105 ap_codes.name tax_code,
1106 'INPUT' tax_class,
1107 Zx_Migrate_Util.GET_TAX_REGIME(
1108 ap_codes.tax_type,
1109 ap_codes.org_id) tax_regime_code,
1110 DECODE(ap_codes.global_attribute_category,
1111 'JE.CZ.ARXSUVAT.TAX_ORIGIN',
1112 CASE WHEN LENGTHB(ap_codes.global_attribute1) > 30 THEN
1113 RTRIM(SUBSTRB(ap_codes.global_attribute1,1,24))
1114 ELSE ap_codes.global_attribute1 END,
1115 'JE.HU.ARXSUVAT.TAX_ORIGIN',
1116 CASE WHEN LENGTHB(ap_codes.global_attribute1) > 30 THEN
1117 RTRIM(SUBSTRB(ap_codes.global_attribute1,1,24))
1118 ELSE ap_codes.global_attribute1 END,
1119 'JE.PL.ARXSUVAT.TAX_ORIGIN',
1120 CASE WHEN LENGTHB(ap_codes.global_attribute1) > 30 THEN
1121 RTRIM(SUBSTRB(ap_codes.global_attribute1,1,24))
1122 ELSE ap_codes.global_attribute1 END,
1123 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
1124 ap_codes.name,
1125 ap_codes.tax_type),1,30))
1126 ) tax,
1127 DECODE(ap_codes.global_attribute_category,
1128 'JA.TW.ARXSUVAT.VAT_TAX',
1129 nvl(ap_codes.global_attribute1,'STANDARD'),
1130 'STANDARD') tax_status_code,
1134 fnd_global.user_id created_by,
1131 'STANDARD' recovery_type_code,
1132 'N' frozen,
1133 zx_migrate_util.get_country(ap_codes.org_id) country_code,
1135 sysdate creation_date,
1136 fnd_global.user_id last_updated_by,
1137 sysdate last_updated_date,
1138 fnd_global.conc_login_id last_update_login,
1139 ap_codes.start_date effective_from,
1140 ap_codes.inactive_date effective_to,
1141 'Y' intercompany_flag
1142 FROM ap_tax_codes_all ap_codes,
1143 ar_vat_tax_all_b ar_codes,
1144 financials_system_params_all fsp,
1145 mtl_intercompany_parameters intcomp
1146 WHERE ap_codes.tax_type NOT IN ('AWT','TAX_GROUP')
1147 AND decode(l_multi_org_flag,'N',l_org_id,ap_codes.org_id) = decode(l_multi_org_flag,'N',l_org_id,fsp.org_id)
1148 AND decode(l_multi_org_flag,'N',l_org_id,ap_codes.org_id) = decode(l_multi_org_flag,'N',l_org_id,intcomp.sell_organization_id)
1149 AND decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id) = decode(l_multi_org_flag,'N',l_org_id,intcomp.ship_organization_id)
1150 AND ap_codes.name = ar_codes.tax_code
1151 AND intcomp.flow_type = 2 -- Bug 4697235 : Specify flow_type=2 which is procurement
1152 -- Sync process
1153 AND ap_codes.tax_id = nvl(p_tax_id,ap_codes.tax_id)
1154 -- Rerunability
1155 AND NOT EXISTS (SELECT 1
1156 FROM zx_update_criteria_results zucr
1157 WHERE zucr.tax_code_id = nvl(p_tax_id,ap_codes.tax_id)
1158 AND zucr.tax_class = 'INPUT'
1159 );
1160
1161 END load_results_for_intercomp_ap;
1162
1163
1164
1165
1166 /*===========================================================================+
1167 | PROCEDURE
1168 | load_results_for_intercomp_ar
1169 |
1170 | DESCRIPTION
1171 | Populates data into zx_update_criteria_results table for AR Tax Codes
1172 | that is used in intercompany transaction.
1173 |
1174 | MTL_INTERCOMPANY_PARAMTERS table stores information about two OUs that
1175 | are used for intercompany transactions. The customer related information is
1176 | used by the shipping organization (SHIP_ORGANIZATION_ID) for AR invoicing
1177 | purposes. The supplier related information is used by the selling organization
1178 | (SELL_ORGANIZATION_ID) for AP invoicing purposes.
1179 |
1180 | Tax Regime Code derived from AP Tax Code (used to create AP invoice) is
1181 | overriden by that of AR Tax Code (used to create AR invoice).
1182 |
1183 | Set zx_criteria_results.intercompany_flag to 'Y' for AP Tax Codes/AR Tax Codes
1184 | that are used for intercompany transactions.
1185 |
1186 |
1187 | MODIFICATION HISTORY
1188 | 04/29/2005 Yoshimichi Konishi Created
1189 |
1190 +==========================================================================*/
1191 PROCEDURE load_results_for_intercomp_ar (p_tax_id NUMBER) AS
1192 BEGIN
1193
1194 INSERT
1195 INTO zx_update_criteria_results
1196 (
1197 tax_code_id,
1198 org_id,
1199 tax_code,
1200 tax_class,
1201 tax_regime_code,
1202 tax,
1203 tax_status_code,
1204 recovery_type_code,
1205 frozen,
1206 country_code,
1207 created_by,
1208 creation_date,
1209 last_updated_by,
1210 last_update_date,
1211 last_update_login,
1212 effective_from,
1213 effective_to,
1214 intercompany_flag
1215 )
1216 SELECT
1217 DISTINCT
1218 ar_codes.vat_tax_id tax_code_id,
1219 decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id) org_id,
1220 ar_codes.tax_code tax_code,
1221 'OUTPUT' tax_class,
1222 Zx_Migrate_Util.GET_TAX_REGIME(
1223 ar_codes.tax_type,
1224 decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id)) tax_regime_code,
1225 -- YK:02/09/2005:Needs substrb
1226 NVL(CASE WHEN ar_codes.global_attribute_category IN ('JE.CZ.ARXSUVAT.TAX_ORIGIN',
1227 'JE.HU.ARXSUVAT.TAX_ORIGIN',
1228 'JE.PL.ARXSUVAT.TAX_ORIGIN')
1229 THEN CASE WHEN ar_codes.global_attribute1 > 30 THEN
1230 RTRIM(SUBSTRB(ar_codes.global_attribute1,1,24))
1231 ELSE ar_codes.global_attribute1
1232 END
1233 WHEN ar_codes.global_attribute_category IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
1234 'JL.BR.ARXSUVAT.Tax Information',
1235 'JL.CO.ARXSUVAT.AR_VAT_TAX')
1236 THEN (select tax_category
1237 from jl_zz_ar_tx_categ_all
1238 where TO_CHAR(tax_category_id) = ar_codes.global_attribute1
1239 and org_id = ar_codes.org_id)
1240 ELSE
1241 NULL
1242 END,
1243 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
1244 ar_codes.tax_code,
1245 ar_codes.tax_type),1,30))) tax,
1246 DECODE(ar_codes.global_attribute_category,
1250 'I', 'STANDARD-AR-INPUT',
1247 'JA.TW.ARXSUVAT.VAT_TAX', nvl(ar_codes.global_attribute1,'STANDARD'),
1248 DECODE(ar_codes.tax_class,
1249 'O', 'STANDARD',
1251 'STANDARD')) tax_status_code,
1252 NULL recovery_type_code,
1253 'N' frozen,
1254 zx_migrate_util.get_country(decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id)) country_code,
1255 fnd_global.user_id created_by,
1256 sysdate creation_date,
1257 fnd_global.user_id last_updated_by,
1258 sysdate last_updated_date,
1259 fnd_global.conc_login_id last_update_login,
1260 ar_codes.start_date effective_from,
1261 ar_codes.end_date effective_to,
1262 'Y' intercompany_flag
1263 FROM ar_vat_tax_all_b ar_codes,
1264 ap_tax_codes_all ap_codes,
1265 ar_system_parameters_all asp,
1266 mtl_intercompany_parameters intcomp
1267 WHERE ar_codes.tax_type <> 'TAX_GROUP'
1268 AND decode(l_multi_org_flag,'N',l_org_id,asp.org_id) = decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id)
1269 AND decode(l_multi_org_flag,'N',l_org_id,ap_codes.org_id) = decode(l_multi_org_flag,'N',l_org_id,intcomp.sell_organization_id)
1270 AND decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id) = decode(l_multi_org_flag,'N',l_org_id,intcomp.ship_organization_id)
1271 AND ap_codes.name = ar_codes.tax_code
1272 AND intcomp.flow_type = 2 -- Bug 4697235 : Specify flow_type=2 which is for procurement
1273 -- Eliminate Tax Vendor Tax Codes
1274 AND asp.tax_database_view_set = 'O'
1275 -- Eliminate LTE tax codes
1276 -- Bug 4688151 : Do not eliminate LTE tax codes
1277 -- AND (ar_codes.global_attribute_category is null OR
1278 -- ar_codes.global_attribute_category NOT IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
1279 -- 'JL.BR.ARXSUVAT.AR_VAT_TAX',
1280 -- 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
1281 -- Eliminate tax_type = 'LOCATION'
1282 AND ar_codes.tax_type <> 'LOCATION'
1283 --Added following conditions for Sync process
1284 AND ar_codes.vat_tax_id = nvl(p_tax_id, ar_codes.vat_tax_id)
1285 --Rerunability
1286 AND NOT EXISTS (SELECT 1
1287 FROM zx_update_criteria_results zucr
1288 WHERE zucr.tax_code_id = nvl(p_tax_id,ar_codes.vat_tax_id)
1289 AND zucr.tax_class = 'OUTPUT'
1290 );
1291 END load_results_for_intercomp_ar;
1292
1293
1294 /*===========================================================================+
1295 | PROCEDURE
1296 | load_regimes
1297 |
1298 | DESCRIPTION
1299 | 1. Populates data into zx_regimes_b table based on data in
1300 | zx_update_criteria_results table for normal tax codes.
1301 | 2. Populates data into zx_regimes_b for Brazilian IPI
1302 | 3. Populates data into zx_regimes_b for Brazilian ISS
1303 | 4. Populates data into zx_regimes_b for GTE US Sales Tax Regimes
1304 | 5. Populates data into zx_regimes_b for Tax Vendor Regimes
1305 | 6. Populates data into zx_regimes_tl
1306 |
1307 |
1308 | NOTES
1309 | 1. Select distinct of tax_regime_code and country_code. Update Criteria UI
1310 | makes sure that this combination is unique.
1311 | 2. Tax Regime Code for unassigned offset tax codes handling. It is County
1312 | Code '-' OFFSET by default. User could override it through Criteria UI.
1313 |
1314 | MODIFICATION HISTORY
1315 | 02/15/2005 Yoshimichi Konishi Created.
1316 |
1317 +==========================================================================*/
1318 PROCEDURE load_regimes AS
1319 -- ****** TYPES ******
1320 TYPE denorm_tbl_type IS TABLE OF zx_migrate_tax_def_common.loc_str_rec_type INDEX BY BINARY_INTEGER;
1321
1322 -- ****** VARIABLES ******
1323 null_loc_str_rec loc_str_rec_type;
1324 denorm_tbl denorm_tbl_type;
1325 denorm_err_tbl denorm_tbl_type;
1326 cnt PLS_INTEGER;
1327 i PLS_INTEGER;
1328 d PLS_INTEGER;
1329 k PLS_INTEGER;
1330 l_temp_id_flex_num NUMBER; --fnd_id_flex_segments.id_flex_num%TYPE
1331 l_temp_seg_num NUMBER(15); --fnd_id_flex_segments.segment_num%TYPE
1332 l_temp_seg_att_type VARCHAR2(30); --fnd_segment_attribute_values.segment_attribute_type%TYPE
1333 l_temp_tax_currency_code VARCHAR2(15); --ar_system_parameters_all.tax_currency_code%TYPE
1334 l_temp_tax_precision NUMBER(1); --ar_system_parameters_all.tax_precision%TYPE
1335 l_temp_tax_mau NUMBER; --ar_system_parameters_all.tax_minimum_accountable_unit%TYPE
1336 l_temp_country_code VARCHAR2(60); --ar_system_parameters_all.default_country%TYPE
1337 l_temp_rounding_rule_code VARCHAR2(30); --ar_system_parameters_all.tax_rounding_rule%TYPE
1338 l_temp_tax_invoice_print VARCHAR2(30); --ar_system_parameters_all.tax_invoice_print%TYPE
1339 l_temp_allow_rounding_override VARCHAR2(30); --ar_system_parameters_all.tax_rounding_allow_override%TYPE
1340 l_temp_org_id NUMBER(15); --ar_system_parameters_all.org_id%TYPE
1341 l_tax_regime_name VARCHAR2(80); --zx_regimes_tl.tax_regime_name%TYPE
1342 l_tax_regime_code VARCHAR2(30); --zx_regimes_b.tax_regime_code%TYPE
1343
1344
1345 -- ****** CURSORS ******
1346 CURSOR loc_str_cur IS
1347 SELECT DISTINCT
1351 qual.segment_attribute_type seg_att_type,
1348 segment.id_flex_num id_flex_num,
1349 asp.default_country default_country,
1350 segment.segment_num seg_num,
1352 decode(l_multi_org_flag,'N',l_org_id,asp.org_id) org_id,
1353 NVL(asp.tax_currency_code, gsob.currency_code)
1354 tax_currency_code,
1355 asp.tax_precision tax_precision,
1356 asp.tax_minimum_accountable_unit tax_mau,
1357 asp.tax_rounding_rule rounding_rule_code,
1358 asp.tax_rounding_allow_override allow_rounding_override
1359 FROM fnd_id_flex_structures str,
1360 fnd_id_flex_segments segment,
1361 fnd_segment_attribute_values qual,
1362 ar_system_parameters_all asp,
1363 ar_vat_tax_all_b avt,
1364 gl_sets_of_books gsob
1365 WHERE str.id_flex_code = 'RLOC'
1366 AND str.application_id = 222
1367 AND str.application_id = segment.application_id
1368 AND str.id_flex_num = segment.id_flex_num
1369 AND str.id_flex_code = segment.id_flex_code
1370 AND segment.application_id = 222
1371 AND segment.id_flex_code = 'RLOC'
1372 AND segment.application_id= qual.application_id
1373 AND segment.id_flex_code = qual.id_flex_code
1374 AND segment.id_flex_num = qual.id_flex_num
1375 AND segment.application_column_name = qual.application_column_name
1376 AND segment.enabled_flag = 'Y'
1377 AND qual.attribute_value = 'Y'
1378 AND qual.segment_attribute_type NOT IN ('EXEMPT_LEVEL', 'TAX_ACCOUNT')
1379 AND asp.location_structure_id = str.id_flex_num
1380 AND decode(l_multi_org_flag,'N',l_org_id,asp.org_id) = decode(l_multi_org_flag,'N',l_org_id,avt.org_id)
1381 AND avt.tax_type = 'LOCATION'
1382 AND asp.tax_database_view_set IN ('O', '_V', '_A') -- Bug 4880905
1383 AND asp.set_of_books_id = gsob.set_of_books_id
1384 ORDER BY 1,2,3,4,5;
1385
1386 BEGIN
1387 /*--------------------------------------------------------------------------
1388 |
1389 | Populating zx_regimes_b from zx_update_criteria_results
1390 |
1391 +---------------------------------------------------------------------------*/
1392
1393 INSERT INTO ZX_REGIMES_B
1394 (
1395 TAX_REGIME_CODE ,
1396 PARENT_REGIME_CODE ,
1397 REGIME_PRECEDENCE ,
1398 HAS_SUB_REGIME_FLAG ,
1399 COUNTRY_OR_GROUP_CODE ,
1400 COUNTRY_CODE ,
1401 GEOGRAPHY_TYPE ,
1402 EFFECTIVE_FROM ,
1403 EFFECTIVE_TO ,
1404 EXCHANGE_RATE_TYPE ,
1405 TAX_CURRENCY_CODE ,
1406 THRSHLD_GROUPING_LVL_CODE ,
1407 ROUNDING_RULE_CODE ,
1408 TAX_PRECISION ,
1409 MINIMUM_ACCOUNTABLE_UNIT ,
1410 TAX_STATUS_RULE_FLAG ,
1411 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
1412 APPLICABILITY_RULE_FLAG ,
1413 PLACE_OF_SUPPLY_RULE_FLAG ,
1414 TAX_CALC_RULE_FLAG ,
1415 TAXABLE_BASIS_THRSHLD_FLAG ,
1416 TAX_RATE_THRSHLD_FLAG ,
1417 TAX_AMT_THRSHLD_FLAG ,
1418 TAX_RATE_RULE_FLAG ,
1419 TAXABLE_BASIS_RULE_FLAG ,
1420 DEF_INCLUSIVE_TAX_FLAG ,
1421 HAS_OTHER_JURISDICTIONS_FLAG ,
1422 ALLOW_ROUNDING_OVERRIDE_FLAG ,
1423 ALLOW_EXEMPTIONS_FLAG ,
1424 ALLOW_EXCEPTIONS_FLAG ,
1425 ALLOW_RECOVERABILITY_FLAG ,
1426 --RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
1427 AUTO_PRVN_FLAG ,
1428 HAS_TAX_DET_DATE_RULE_FLAG ,
1429 HAS_EXCH_RATE_DATE_RULE_FLAG ,
1430 HAS_TAX_POINT_DATE_RULE_FLAG ,
1431 USE_LEGAL_MSG_FLAG ,
1432 REGN_NUM_SAME_AS_LE_FLAG ,
1433 DEF_REC_SETTLEMENT_OPTION_CODE ,
1434 RECORD_TYPE_CODE ,
1435 ATTRIBUTE1 ,
1436 ATTRIBUTE2 ,
1437 ATTRIBUTE3 ,
1438 ATTRIBUTE4 ,
1439 ATTRIBUTE5 ,
1440 ATTRIBUTE6 ,
1441 ATTRIBUTE7 ,
1442 ATTRIBUTE8 ,
1443 ATTRIBUTE9 ,
1444 ATTRIBUTE10 ,
1445 ATTRIBUTE11 ,
1446 ATTRIBUTE12 ,
1447 ATTRIBUTE13 ,
1448 ATTRIBUTE14 ,
1449 ATTRIBUTE15 ,
1450 ATTRIBUTE_CATEGORY ,
1451 DEF_REGISTR_PARTY_TYPE_CODE ,
1452 REGISTRATION_TYPE_RULE_FLAG ,
1453 TAX_INCLUSIVE_OVERRIDE_FLAG ,
1454 CROSS_REGIME_COMPOUNDING_FLAG ,
1455 TAX_REGIME_ID ,
1456 GEOGRAPHY_ID ,
1457 THRSHLD_CHK_TMPLT_CODE ,
1458 PERIOD_SET_NAME ,
1459 REP_TAX_AUTHORITY_ID ,
1460 COLL_TAX_AUTHORITY_ID ,
1461 CREATED_BY ,
1462 CREATION_DATE ,
1466 REQUEST_ID ,
1463 LAST_UPDATED_BY ,
1464 LAST_UPDATE_DATE ,
1465 LAST_UPDATE_LOGIN ,
1467 PROGRAM_APPLICATION_ID ,
1468 PROGRAM_ID ,
1469 PROGRAM_LOGIN_ID ,
1470 OBJECT_VERSION_NUMBER
1471 )
1472 SELECT
1473 L_TAX_REGIME_CODE ,
1474 NULL ,--PARENT_REGIME_CODE
1475 L_REGIME_PRECEDENCE ,--REGIME_ PRECEDENCE
1476 'N' ,--HAS_SUB_REGIME_FLAG
1477 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
1478 L_COUNTRY_CODE ,--COUNTRY_CODE
1479 NULL ,--GEOGRAPHY_TYPE
1480 l_min_start_date ,--EFFECTIVE_FROM
1481 NULL ,--EFFECTIVE_TO
1482 NULL ,--EXCHANGE_RATE_TYPE
1483 NULL ,--TAX_CURRENCY_CODE
1484 NULL ,--THRSHLD_GROUPING_LVL_CODE
1485 NULL ,--ROUNDING_RULE_CODE
1486 NULL ,--TAX_PRECISION
1487 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
1488 'N' ,--TAX_STATUS_RULE_FLAG
1489 'SHIP_FROM' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
1490 'N' ,--APPLICABILITY_RULE_FLAG
1491 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
1492 'N' ,--TAX_CALC_RULE_FLAG
1493 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
1494 'N' ,--TAX_RATE_THRSHLD_FLAG
1495 'N' ,--TAX_AMT_THRSHLD_FLAG
1496 'N' ,--TAX_RATE_RULE_FLAG
1497 'N' ,--TAXABLE_BASIS_RULE_FLAG
1498 'N' ,--DEF_INCLUSIVE_TAX_FLAG
1499 'N' ,--HAS_OTHER_JURISDICTIONS_FLAG
1500 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
1501 'N' ,--ALLOW_EXEMPTIONS_FLAG Bug 4204464 Bug 5204559
1502 'N' ,--ALLOW_EXCEPTIONS_FLAG Bug 4204464 Bug 5204559
1503 'N' ,--ALLOW_RECOVERABILITY_FLAG
1504 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
1505 'N' ,--AUTO_PRVN_FLAG
1506 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
1507 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
1508 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
1509 'N' ,--USE_LEGAL_MSG_FLAG
1510 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
1511 NULL ,--DEF_REC_SETTLE_OPTION_CODE
1512 'MIGRATED' ,--RECORD_TYPE_CODE
1513 NULL ,
1514 NULL ,
1515 NULL ,
1516 NULL ,
1517 NULL ,
1518 NULL ,
1519 NULL ,
1520 NULL ,
1521 NULL ,
1522 NULL ,
1523 NULL ,
1524 NULL ,
1525 NULL ,
1526 NULL ,
1527 NULL ,
1528 NULL ,
1529 'SHIP_FROM_PARTY' ,--DEF_REGISTR_PARTY_TYPE_CODE
1530 'N' ,--REGISTRATION_TYPE_RULE_FLAG
1531 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG
1532 DECODE(L_REGIME_PRECEDENCE,NULL,'N','Y') ,--CROSS_REGIME_COMPOUNDING_FLAG
1533 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
1534 NULL ,--GEOGRAPHY_ID
1535 NULL ,--THRSHLD_CHK_TMPLT_CODE
1536 NULL ,--PERIOD_SET_NAME
1537 NULL ,--REP_TAX_AUTHORITY_ID
1538 NULL ,--COLL_TAX_AUTHORITY_ID
1539 fnd_global.user_id ,
1540 SYSDATE ,
1541 fnd_global.user_id ,
1542 SYSDATE ,
1543 fnd_global.conc_login_id ,
1544 fnd_global.conc_request_id ,--Request Id
1545 fnd_global.prog_appl_id ,--Program Application ID
1546 fnd_global.conc_program_id ,--Program Id
1547 fnd_global.conc_login_id ,--Program Login ID
1548 1
1549 FROM
1550 (
1551 SELECT DISTINCT
1552 zucr.tax_regime_code l_tax_regime_code,
1553 zucr.country_code l_country_code,
1554 zucr.regime_precedence l_regime_precedence
1555 FROM zx_update_criteria_results zucr
1556 WHERE NOT EXISTS (SELECT 1
1557 FROM zx_regimes_b zrb
1558 WHERE zrb.tax_regime_code = zucr.tax_regime_code
1559 )
1560
1561 );
1562
1563
1564 IF zx_migrate_util.is_installed('AP') = 'Y' THEN
1565 /*------------------------------------------------------------------------------------
1566 |
1567 | For Brazilian Regimes : BR-IPI when BR-ICMS Regime exists
1568 |
1569 | YK:02/15/2005: The following sql in P2P tax def migration code is splitted into
1570 | two to avoid dynamic sql call to fetch sequence.
1571 |
1575 (
1572 +-------------------------------------------------------------------------------------*/
1573 INSERT INTO
1574 ZX_REGIMES_B
1576 TAX_REGIME_CODE ,
1577 PARENT_REGIME_CODE ,
1578 HAS_SUB_REGIME_FLAG ,
1579 COUNTRY_OR_GROUP_CODE ,
1580 COUNTRY_CODE ,
1581 GEOGRAPHY_TYPE ,
1582 EFFECTIVE_FROM ,
1583 EFFECTIVE_TO ,
1584 EXCHANGE_RATE_TYPE ,
1585 TAX_CURRENCY_CODE ,
1586 THRSHLD_GROUPING_LVL_CODE ,
1587 ROUNDING_RULE_CODE ,
1588 TAX_PRECISION ,
1589 MINIMUM_ACCOUNTABLE_UNIT ,
1590 TAX_STATUS_RULE_FLAG ,
1591 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
1592 APPLICABILITY_RULE_FLAG ,
1593 PLACE_OF_SUPPLY_RULE_FLAG ,
1594 TAX_CALC_RULE_FLAG ,
1595 TAXABLE_BASIS_THRSHLD_FLAG ,
1596 TAX_RATE_THRSHLD_FLAG ,
1597 TAX_AMT_THRSHLD_FLAG ,
1598 TAX_RATE_RULE_FLAG ,
1599 TAXABLE_BASIS_RULE_FLAG ,
1600 DEF_INCLUSIVE_TAX_FLAG ,
1601 HAS_OTHER_JURISDICTIONS_FLAG ,
1602 ALLOW_ROUNDING_OVERRIDE_FLAG ,
1603 ALLOW_EXEMPTIONS_FLAG ,
1604 ALLOW_EXCEPTIONS_FLAG ,
1605 ALLOW_RECOVERABILITY_FLAG ,
1606 -- RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
1607 AUTO_PRVN_FLAG ,
1608 HAS_TAX_DET_DATE_RULE_FLAG ,
1609 HAS_EXCH_RATE_DATE_RULE_FLAG ,
1610 HAS_TAX_POINT_DATE_RULE_FLAG ,
1611 USE_LEGAL_MSG_FLAG ,
1612 REGN_NUM_SAME_AS_LE_FLAG ,
1613 DEF_REC_SETTLEMENT_OPTION_CODE ,
1614 RECORD_TYPE_CODE ,
1615 ATTRIBUTE1 ,
1616 ATTRIBUTE2 ,
1617 ATTRIBUTE3 ,
1618 ATTRIBUTE4 ,
1619 ATTRIBUTE5 ,
1620 ATTRIBUTE6 ,
1621 ATTRIBUTE7 ,
1622 ATTRIBUTE8 ,
1623 ATTRIBUTE9 ,
1624 ATTRIBUTE10 ,
1625 ATTRIBUTE11 ,
1626 ATTRIBUTE12 ,
1627 ATTRIBUTE13 ,
1628 ATTRIBUTE14 ,
1629 ATTRIBUTE15 ,
1630 ATTRIBUTE_CATEGORY ,
1631 DEF_REGISTR_PARTY_TYPE_CODE ,
1632 REGISTRATION_TYPE_RULE_FLAG ,
1633 TAX_INCLUSIVE_OVERRIDE_FLAG ,
1634 REGIME_PRECEDENCE ,
1635 CROSS_REGIME_COMPOUNDING_FLAG ,
1636 TAX_REGIME_ID ,
1637 GEOGRAPHY_ID ,
1638 THRSHLD_CHK_TMPLT_CODE ,
1639 PERIOD_SET_NAME ,
1640 REP_TAX_AUTHORITY_ID ,
1641 COLL_TAX_AUTHORITY_ID ,
1642 CREATED_BY ,
1643 CREATION_DATE ,
1644 LAST_UPDATED_BY ,
1645 LAST_UPDATE_DATE ,
1646 LAST_UPDATE_LOGIN ,
1647 REQUEST_ID ,
1648 PROGRAM_APPLICATION_ID ,
1649 PROGRAM_ID ,
1650 PROGRAM_LOGIN_ID ,
1651 OBJECT_VERSION_NUMBER
1652 )
1653 SELECT
1654 'BR-IPI' ,--TAX_REGIME_CODE
1655 NULL ,--PARENT_REGIME_CODE
1656 'N' ,--HAS_SUB_REGIME_FLAG
1657 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
1658 'BR' ,--COUNTRY_CODE
1659 NULL ,--GEOGRAPHY_TYPE
1660 l_min_start_date ,--EFFECTIVE_FROM
1661 NULL ,--EFFECTIVE_TO
1662 NULL ,--EXCHANGE_RATE_TYPE
1663 NULL ,--TAX_CURRENCY_CODE
1664 NULL ,--THRSHLD_GROUPING_LVL_CODE
1665 NULL ,--ROUNDING_RULE_CODE
1666 NULL ,--TAX_PRECISION
1667 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
1668 'N' ,--TAX_STATUS_RULE_FLAG
1669 'SHIP_FROM' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
1670 'N' ,--APPLICABILITY_RULE_FLAG
1671 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
1672 'N' ,--TAX_CALC_RULE_FLAG
1673 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
1674 'N' ,--TAX_RATE_THRSHLD_FLAG
1675 'N' ,--TAX_AMT_THRSHLD_FLAG
1676 'N' ,--TAX_RATE_RULE_FLAG
1677 'N' ,--TAXABLE_BASIS_RULE_FLAG
1678 'N' ,--DEF_INCLUSIVE_TAX_FLAG
1679 'N' ,--HAS_OTHER_JURISDICTIONS_FLAG
1680 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
1681 'Y' ,--ALLOW_EXEMPTIONS_FLAG
1685 'N' ,--AUTO_PRVN_FLAG
1682 'Y' ,--ALLOW_EXCEPTIONS_FLAG
1683 'N' ,--ALLOW_RECOVERABILITY_FLAG
1684 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
1686 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
1687 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
1688 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
1689 'N' ,--USE_LEGAL_MSG_FLAG
1690 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
1691 NULL ,--DEF_REC_SETTLEMENT_OPTION_CODE
1692 'MIGRATED' ,--RECORD_TYPE_CODE
1693 NULL ,
1694 NULL ,
1695 NULL ,
1696 NULL ,
1697 NULL ,
1698 NULL ,
1699 NULL ,
1700 NULL ,
1701 NULL ,
1702 NULL ,
1703 NULL ,
1704 NULL ,
1705 NULL ,
1706 NULL ,
1707 NULL ,
1708 NULL ,
1709 'SHIP_FROM_PARTY' ,--DEF_REGISTR_PARTY_TYPE_CODE
1710 'N' ,--REGISTRATION_TYPE_RULE_FLAG
1711 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG
1712 NULL ,--REGIME_PRECEDENCE
1713 'N' ,--CROSS_REGIME_COMPOUNDING_FLAG
1714 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
1715 NULL ,--GEOGRAPHY_ID
1716 NULL ,--THRSHLD_CHK_TMPLT_CODE
1717 NULL ,--PERIOD_SET_NAME
1718 NULL ,--REP_TAX_AUTHORITY_ID
1719 NULL ,--COLL_TAX_AUTHORITY_ID
1720 fnd_global.user_id ,
1721 SYSDATE ,
1722 fnd_global.user_id ,
1723 SYSDATE ,
1724 fnd_global.conc_login_id ,
1725 fnd_global.conc_request_id ,--Request Id
1726 fnd_global.prog_appl_id ,--Program Application ID
1727 fnd_global.conc_program_id ,--Program Id
1728 fnd_global.conc_login_id ,--Program Login ID
1729 1
1730 FROM zx_regimes_b
1731 WHERE tax_regime_code = 'BR-ICMS'
1732 AND country_code = 'BR'
1733 AND NOT EXISTS (SELECT 1
1734 FROM zx_regimes_b
1735 WHERE tax_regime_code = 'BR-IPI');
1736
1737 /*--------------------------------------------------------------------------
1738 |
1739 | For Brazilian Regimes : BR-ISS when BR-IPI Regime exists
1740 |
1741 | YK:02/15/2005: The following sql in P2P tax def migration code is splitted into
1742 | two to avoid dynamic sql call to fetch sequence.
1743 |
1744 +---------------------------------------------------------------------------*/
1745 INSERT INTO
1746 ZX_REGIMES_B
1747 (
1748 TAX_REGIME_CODE ,
1749 PARENT_REGIME_CODE ,
1750 HAS_SUB_REGIME_FLAG ,
1751 COUNTRY_OR_GROUP_CODE ,
1752 COUNTRY_CODE ,
1753 GEOGRAPHY_TYPE ,
1754 EFFECTIVE_FROM ,
1755 EFFECTIVE_TO ,
1756 EXCHANGE_RATE_TYPE ,
1757 TAX_CURRENCY_CODE ,
1758 THRSHLD_GROUPING_LVL_CODE ,
1759 ROUNDING_RULE_CODE ,
1760 TAX_PRECISION ,
1761 MINIMUM_ACCOUNTABLE_UNIT ,
1762 TAX_STATUS_RULE_FLAG ,
1763 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
1764 APPLICABILITY_RULE_FLAG ,
1765 PLACE_OF_SUPPLY_RULE_FLAG ,
1766 TAX_CALC_RULE_FLAG ,
1767 TAXABLE_BASIS_THRSHLD_FLAG ,
1768 TAX_RATE_THRSHLD_FLAG ,
1769 TAX_AMT_THRSHLD_FLAG ,
1770 TAX_RATE_RULE_FLAG ,
1771 TAXABLE_BASIS_RULE_FLAG ,
1772 DEF_INCLUSIVE_TAX_FLAG ,
1773 HAS_OTHER_JURISDICTIONS_FLAG ,
1774 ALLOW_ROUNDING_OVERRIDE_FLAG ,
1775 ALLOW_EXEMPTIONS_FLAG ,
1776 ALLOW_EXCEPTIONS_FLAG ,
1777 ALLOW_RECOVERABILITY_FLAG ,
1778 -- RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
1779 AUTO_PRVN_FLAG ,
1780 HAS_TAX_DET_DATE_RULE_FLAG ,
1781 HAS_EXCH_RATE_DATE_RULE_FLAG ,
1782 HAS_TAX_POINT_DATE_RULE_FLAG ,
1783 USE_LEGAL_MSG_FLAG ,
1784 REGN_NUM_SAME_AS_LE_FLAG ,
1785 DEF_REC_SETTLEMENT_OPTION_CODE ,
1786 RECORD_TYPE_CODE ,
1787 ATTRIBUTE1 ,
1788 ATTRIBUTE2 ,
1789 ATTRIBUTE3 ,
1790 ATTRIBUTE4 ,
1791 ATTRIBUTE5 ,
1792 ATTRIBUTE6 ,
1793 ATTRIBUTE7 ,
1794 ATTRIBUTE8 ,
1795 ATTRIBUTE9 ,
1796 ATTRIBUTE10 ,
1797 ATTRIBUTE11 ,
1798 ATTRIBUTE12 ,
1799 ATTRIBUTE13 ,
1800 ATTRIBUTE14 ,
1804 REGISTRATION_TYPE_RULE_FLAG ,
1801 ATTRIBUTE15 ,
1802 ATTRIBUTE_CATEGORY ,
1803 DEF_REGISTR_PARTY_TYPE_CODE ,
1805 TAX_INCLUSIVE_OVERRIDE_FLAG ,
1806 REGIME_PRECEDENCE ,
1807 CROSS_REGIME_COMPOUNDING_FLAG ,
1808 TAX_REGIME_ID ,
1809 GEOGRAPHY_ID ,
1810 THRSHLD_CHK_TMPLT_CODE ,
1811 PERIOD_SET_NAME ,
1812 REP_TAX_AUTHORITY_ID ,
1813 COLL_TAX_AUTHORITY_ID ,
1814 CREATED_BY ,
1815 CREATION_DATE ,
1816 LAST_UPDATED_BY ,
1817 LAST_UPDATE_DATE ,
1818 LAST_UPDATE_LOGIN ,
1819 REQUEST_ID ,
1820 PROGRAM_APPLICATION_ID ,
1821 PROGRAM_ID ,
1822 PROGRAM_LOGIN_ID ,
1823 OBJECT_VERSION_NUMBER
1824 )
1825 SELECT
1826 'BR-ISS' ,--TAX_REGIME_CODE
1827 NULL ,--PARENT_REGIME_CODE
1828 'N' ,--HAS_SUB_REGIME_FLAG
1829 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
1830 'BR' ,--COUNTRY_CODE
1831 NULL ,--GEOGRAPHY_TYPE
1832 l_min_start_date ,--EFFECTIVE_FROM
1833 NULL ,--EFFECTIVE_TO
1834 NULL ,--EXCHANGE_RATE_TYPE
1835 NULL ,--TAX_CURRENCY_CODE
1836 NULL ,--THRSHLD_GROUPING_LVL_CODE
1837 NULL ,--ROUNDING_RULE_CODE
1838 NULL ,--TAX_PRECISION
1839 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
1840 'N' ,--TAX_STATUS_RULE_FLAG
1841 'SHIP_FROM' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
1842 'N' ,--APPLICABILITY_RULE_FLAG
1843 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
1844 'N' ,--TAX_CALC_RULE_FLAG
1845 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
1846 'N' ,--TAX_RATE_THRSHLD_FLAG
1847 'N' ,--TAX_AMT_THRSHLD_FLAG
1848 'N' ,--TAX_RATE_RULE_FLAG
1849 'N' ,--TAXABLE_BASIS_RULE_FLAG
1850 'N' ,--DEF_INCLUSIVE_TAX_FLAG
1851 'N' ,--HAS_OTHER_JURISDICTIONS_FLAG
1852 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
1853 'Y' ,--ALLOW_EXEMPTIONS_FLAG
1854 'Y' ,--ALLOW_EXCEPTIONS_FLAG
1855 'N' ,--ALLOW_RECOVERABILITY_FLAG
1856 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
1857 'N' ,--AUTO_PRVN_FLAG
1858 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
1859 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
1860 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
1861 'N' ,--USE_LEGAL_MSG_FLAG
1862 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
1863 NULL ,--DEF_REC_SETTLEMENT_OPTION_CODE
1864 'MIGRATED' ,--RECORD_TYPE_CODE
1865 NULL ,
1866 NULL ,
1867 NULL ,
1868 NULL ,
1869 NULL ,
1870 NULL ,
1871 NULL ,
1872 NULL ,
1873 NULL ,
1874 NULL ,
1875 NULL ,
1876 NULL ,
1877 NULL ,
1878 NULL ,
1879 NULL ,
1880 NULL ,
1881 'SHIP_FROM_PARTY' ,--DEF_REGISTR_PARTY_TYPE_CODE
1882 'N' ,--REGISTRATION_TYPE_RULE_FLAG
1883 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG
1884 NULL ,--REGIME_PRECEDENCE
1885 'N' ,--CROSS_REGIME_COMPOUNDING_FLAG
1886 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
1887 NULL ,--GEOGRAPHY_ID
1888 NULL ,--THRSHLD_CHK_TMPLT_CODE
1889 NULL ,--PERIOD_SET_NAME
1890 NULL ,--REP_TAX_AUTHORITY_ID
1891 NULL ,--COLL_TAX_AUTHORITY_ID
1892 fnd_global.user_id ,
1893 SYSDATE ,
1894 fnd_global.user_id ,
1895 SYSDATE ,
1896 fnd_global.conc_login_id ,
1897 fnd_global.conc_request_id ,--Request Id
1898 fnd_global.prog_appl_id ,--Program Application ID
1899 fnd_global.conc_program_id ,--Program Id
1900 fnd_global.conc_login_id ,--Program Login ID
1901 1
1902 FROM zx_regimes_b
1903 WHERE tax_regime_code = 'BR-IPI'
1904 AND country_code = 'BR'
1905 AND NOT EXISTS (SELECT 1
1906 FROM zx_regimes_b
1907 WHERE tax_regime_code = 'BR-ISS');
1911 IF zx_migrate_util.is_installed('AR') = 'Y' THEN
1908
1909 END IF;
1910
1912 /*-------------------------------------------------------------------------
1913 |
1914 | For GTE US Sales Tax Regimes
1915 | It also inserts zx_regimes_tl.
1916 |
1917 | Regime Code :
1918 | 1. Country Code || '-SALES-TAX-' || location structure id
1919 |
1920 | Regime Name :
1921 | 1. Country Code || '-SALES-TAX-' || Qualifier1 ||'-'|| Qualifier2..
1922 |
1923 +--------------------------------------------------------------------------*/
1924 -- ****** Building PL/SQL Table ******
1925 i := 1;
1926 d := 1;
1927 FOR loc_str_cur_rec IN loc_str_cur LOOP
1928 IF loc_str_cur%ROWCOUNT = 1 THEN
1929 loc_str_rec.country_code := loc_str_cur_rec.default_country;
1930 loc_str_rec.id_flex_num := loc_str_cur_rec.id_flex_num;
1931 loc_str_rec.seg_att_type1 := loc_str_cur_rec.seg_att_type;
1932 loc_str_rec.tax_currency_code := loc_str_cur_rec.tax_currency_code;
1933 loc_str_rec.tax_precision := loc_str_cur_rec.tax_precision;
1934 loc_str_rec.tax_mau := loc_str_cur_rec.tax_mau;
1935 loc_str_rec.rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
1936 loc_str_rec.allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
1937 loc_str_rec.org_id := loc_str_cur_rec.org_id;
1938
1939 l_temp_id_flex_num := loc_str_cur_rec.id_flex_num;
1940 l_temp_country_code := loc_str_cur_rec.default_country;
1941 l_temp_org_id := loc_str_cur_rec.org_id;
1942 l_temp_seg_num := loc_str_cur_rec.seg_num;
1943 l_temp_seg_att_type := loc_str_cur_rec.seg_att_type;
1944 l_temp_tax_currency_code := loc_str_cur_rec.tax_currency_code;
1945 l_temp_tax_precision := loc_str_cur_rec.tax_precision;
1946 l_temp_tax_mau := loc_str_cur_rec.tax_mau;
1947 l_temp_rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
1948 l_temp_allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
1949 l_temp_org_id := loc_str_cur_rec.org_id;
1950
1951 cnt := 1; --Counter for seg_att_type
1952 ELSE
1953 IF l_temp_id_flex_num = loc_str_cur_rec.id_flex_num AND
1954 l_temp_country_code = loc_str_cur_rec.default_country THEN
1955 IF l_temp_seg_num <> loc_str_cur_rec.seg_num THEN
1956 cnt := cnt + 1;
1957 IF cnt = 2 THEN
1958 loc_str_rec.seg_att_type2 := loc_str_cur_rec.seg_att_type;
1959 ELSIF cnt = 3 THEN
1960 loc_str_rec.seg_att_type3 := loc_str_cur_rec.seg_att_type;
1961 ELSIF cnt = 4 THEN
1962 loc_str_rec.seg_att_type4 := loc_str_cur_rec.seg_att_type;
1963 ELSIF cnt = 5 THEN
1964 loc_str_rec.seg_att_type5 := loc_str_cur_rec.seg_att_type;
1965 ELSIF cnt = 6 THEN
1966 loc_str_rec.seg_att_type6 := loc_str_cur_rec.seg_att_type;
1967 ELSIF cnt = 7 THEN
1968 loc_str_rec.seg_att_type7 := loc_str_cur_rec.seg_att_type;
1969 ELSIF cnt = 8 THEN
1970 loc_str_rec.seg_att_type8 := loc_str_cur_rec.seg_att_type;
1971 ELSIF cnt = 9 THEN
1972 loc_str_rec.seg_att_type9 := loc_str_cur_rec.seg_att_type;
1973 ELSIF cnt = 10 THEN
1974 loc_str_rec.seg_att_type10 := loc_str_cur_rec.seg_att_type;
1975 END IF;
1976 ELSIF l_temp_seg_num = loc_str_cur_rec.seg_num THEN
1977 IF l_temp_org_id <> loc_str_cur_rec.org_id THEN
1978 -- ORGANIZATION MERGE HAPPEND --
1979 loc_str_rec := null_loc_str_rec;
1980 loc_str_rec.country_code := loc_str_cur_rec.default_country;
1981 loc_str_rec.id_flex_num := loc_str_cur_rec.id_flex_num;
1982 loc_str_rec.tax_currency_code := loc_str_cur_rec.tax_currency_code;
1983 loc_str_rec.tax_precision := loc_str_cur_rec.tax_precision;
1984 loc_str_rec.tax_mau := loc_str_cur_rec.tax_mau;
1985 loc_str_rec.rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
1986 loc_str_rec.allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
1987 loc_str_rec.org_id := loc_str_cur_rec.org_id;
1988 --loc_str_rec.tax_account_id := loc_str_cur_rec.tax_account_id;
1989 denorm_err_tbl(d) := loc_str_rec;
1990 d := d + 1;
1991 END IF;
1992 END IF;
1993 ELSE
1994 denorm_tbl(i) := loc_str_rec;
1995 loc_str_rec := null_loc_str_rec;
1996 i := i + 1;
1997
1998 loc_str_rec.country_code := loc_str_cur_rec.default_country;
1999 loc_str_rec.id_flex_num := loc_str_cur_rec.id_flex_num;
2000 loc_str_rec.seg_att_type1 := loc_str_cur_rec.seg_att_type;
2001 loc_str_rec.tax_currency_code := loc_str_cur_rec.tax_currency_code;
2002 loc_str_rec.tax_precision := loc_str_cur_rec.tax_precision;
2003 loc_str_rec.tax_mau := loc_str_cur_rec.tax_mau;
2004 loc_str_rec.rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
2005 loc_str_rec.allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
2006 loc_str_rec.org_id := loc_str_cur_rec.org_id;
2007
2008 l_temp_id_flex_num := loc_str_cur_rec.id_flex_num;
2009 l_temp_country_code := loc_str_cur_rec.default_country;
2010 l_temp_org_id := loc_str_cur_rec.org_id;
2011 l_temp_seg_num := loc_str_cur_rec.seg_num;
2012 l_temp_seg_att_type := loc_str_cur_rec.seg_att_type;
2016 l_temp_rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
2013 l_temp_tax_currency_code := loc_str_cur_rec.tax_currency_code;
2014 l_temp_tax_precision := loc_str_cur_rec.tax_precision;
2015 l_temp_tax_mau := loc_str_cur_rec.tax_mau;
2017 l_temp_allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
2018 l_temp_org_id := loc_str_cur_rec.org_id;
2019 cnt := 1;
2020 END IF;
2021 END IF;
2022 END LOOP;
2023 denorm_tbl(i) := loc_str_rec;
2024
2025 -- ****** DEBUG ******
2026 FOR k in 1..denorm_tbl.count LOOP
2027 arp_util_tax.debug('***');
2028 arp_util_tax.debug('Country Code: '|| denorm_tbl(k).country_code);
2029 arp_util_tax.debug('ID Flex Num : '|| denorm_tbl(k).id_flex_num);
2030 arp_util_tax.debug('Attr1 : '|| denorm_tbl(k).seg_att_type1);
2031 arp_util_tax.debug('Attr2 : '|| denorm_tbl(k).seg_att_type2);
2032 arp_util_tax.debug('Attr3 : '|| denorm_tbl(k).seg_att_type3);
2033 arp_util_tax.debug('Attr4 : '|| denorm_tbl(k).seg_att_type4);
2034 arp_util_tax.debug('Attr5 : '|| denorm_tbl(k).seg_att_type5);
2035 arp_util_tax.debug('Attr6 : '|| denorm_tbl(k).seg_att_type6);
2036 arp_util_tax.debug('Attr7 : '|| denorm_tbl(k).seg_att_type7);
2037 arp_util_tax.debug('Attr8 : '|| denorm_tbl(k).seg_att_type8);
2038 arp_util_tax.debug('Attr9 : '|| denorm_tbl(k).seg_att_type9);
2039 arp_util_tax.debug('Attr10 : '|| denorm_tbl(k).seg_att_type10);
2040 arp_util_tax.debug('Currency : '|| denorm_tbl(k).tax_currency_code);
2041 arp_util_tax.debug('Precision : '|| denorm_tbl(k).tax_precision);
2042 arp_util_tax.debug('MAU : '|| denorm_tbl(k).tax_mau);
2043 arp_util_tax.debug('Rounding : '|| denorm_tbl(k).rounding_rule_code);
2044 arp_util_tax.debug('Rounding Ovr: '|| denorm_tbl(k).allow_rounding_override);
2045 arp_util_tax.debug('Org ID : '|| denorm_tbl(k).org_id);
2046 END LOOP;
2047 arp_util_tax.debug(' ');
2048 IF denorm_err_tbl.count > 0 THEN
2049 arp_util_tax.debug('*** ORGANZATION MERGED RECORDS ***');
2050 FOR k in 1..denorm_err_tbl.count LOOP
2051 arp_util_tax.debug(denorm_err_tbl(k).country_code);
2052 arp_util_tax.debug(denorm_err_tbl(k).id_flex_num);
2053 arp_util_tax.debug(denorm_err_tbl(k).tax_currency_code);
2054 arp_util_tax.debug(denorm_err_tbl(k).tax_precision);
2055 arp_util_tax.debug(denorm_err_tbl(k).tax_mau);
2056 arp_util_tax.debug(denorm_err_tbl(k).rounding_rule_code);
2057 arp_util_tax.debug(denorm_err_tbl(k).allow_rounding_override);
2058 arp_util_tax.debug(denorm_err_tbl(k).org_id);
2059 --arp_util_tax.debug(denorm_tbl(k).tax_account_id);
2060 END LOOP;
2061 ELSE
2062 arp_util_tax.debug('*** NO ORGANZATION MERGED RECORDS ***');
2063 END IF;
2064 -- ****** DEBUG ******
2065
2066
2067
2068
2069 -- ****** Insert into zx_regimes_b/tl ******
2070 FOR k in 1..denorm_tbl.count LOOP
2071 if denorm_tbl(k).country_code is not null
2072 then
2073 l_tax_regime_name := denorm_tbl(k).country_code || '-SALES-TAX' ||
2074 '-' || denorm_tbl(k).seg_att_type1 ||
2075 '-' || denorm_tbl(k).seg_att_type2 ||
2076 '-' || denorm_tbl(k).seg_att_type3 ||
2077 '-' || denorm_tbl(k).seg_att_type4 ||
2078 '-' || denorm_tbl(k).seg_att_type5 ||
2079 '-' || denorm_tbl(k).seg_att_type6 ||
2080 '-' || denorm_tbl(k).seg_att_type7 ||
2081 '-' || denorm_tbl(k).seg_att_type8 ||
2082 '-' || denorm_tbl(k).seg_att_type9 ||
2083 '-' || denorm_tbl(k).seg_att_type10;
2084 l_tax_regime_name := RTRIM(l_tax_regime_name, '-');
2085 l_tax_regime_code := denorm_tbl(k).country_code || '-SALES-TAX-' || denorm_tbl(k).id_flex_num;
2086
2087 INSERT ALL
2088 WHEN (NOT EXISTS (SELECT 1
2089 FROM ZX_REGIMES_B
2090 WHERE TAX_REGIME_CODE = l_tax_regime_code
2091 )
2092 ) THEN
2093 INTO ZX_REGIMES_B
2094 (
2095 TAX_REGIME_CODE ,
2096 PARENT_REGIME_CODE ,
2097 HAS_SUB_REGIME_FLAG ,
2098 COUNTRY_OR_GROUP_CODE ,
2099 COUNTRY_CODE ,
2100 GEOGRAPHY_TYPE ,
2101 EFFECTIVE_FROM ,
2102 EFFECTIVE_TO ,
2103 EXCHANGE_RATE_TYPE ,
2104 TAX_CURRENCY_CODE ,
2105 THRSHLD_GROUPING_LVL_CODE ,
2106 ROUNDING_RULE_CODE ,
2107 TAX_PRECISION ,
2108 MINIMUM_ACCOUNTABLE_UNIT ,
2109 TAX_STATUS_RULE_FLAG ,
2110 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
2111 APPLICABILITY_RULE_FLAG ,
2112 PLACE_OF_SUPPLY_RULE_FLAG ,
2113 TAX_CALC_RULE_FLAG ,
2114 TAXABLE_BASIS_THRSHLD_FLAG ,
2115 TAX_RATE_THRSHLD_FLAG ,
2116 TAX_AMT_THRSHLD_FLAG ,
2117 TAX_RATE_RULE_FLAG ,
2118 TAXABLE_BASIS_RULE_FLAG ,
2119 DEF_INCLUSIVE_TAX_FLAG ,
2120 HAS_OTHER_JURISDICTIONS_FLAG ,
2121 ALLOW_ROUNDING_OVERRIDE_FLAG ,
2122 ALLOW_EXEMPTIONS_FLAG ,
2126 AUTO_PRVN_FLAG ,
2123 ALLOW_EXCEPTIONS_FLAG ,
2124 ALLOW_RECOVERABILITY_FLAG ,
2125 -- RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
2127 HAS_TAX_DET_DATE_RULE_FLAG ,
2128 HAS_EXCH_RATE_DATE_RULE_FLAG ,
2129 HAS_TAX_POINT_DATE_RULE_FLAG ,
2130 USE_LEGAL_MSG_FLAG ,
2131 REGN_NUM_SAME_AS_LE_FLAG ,
2132 DEF_REC_SETTLEMENT_OPTION_CODE ,
2133 RECORD_TYPE_CODE ,
2134 ATTRIBUTE1 ,
2135 ATTRIBUTE2 ,
2136 ATTRIBUTE3 ,
2137 ATTRIBUTE4 ,
2138 ATTRIBUTE5 ,
2139 ATTRIBUTE6 ,
2140 ATTRIBUTE7 ,
2141 ATTRIBUTE8 ,
2142 ATTRIBUTE9 ,
2143 ATTRIBUTE10 ,
2144 ATTRIBUTE11 ,
2145 ATTRIBUTE12 ,
2146 ATTRIBUTE13 ,
2147 ATTRIBUTE14 ,
2148 ATTRIBUTE15 ,
2149 ATTRIBUTE_CATEGORY ,
2150 DEF_REGISTR_PARTY_TYPE_CODE ,
2151 REGISTRATION_TYPE_RULE_FLAG ,
2152 TAX_INCLUSIVE_OVERRIDE_FLAG ,
2153 REGIME_PRECEDENCE ,
2154 CROSS_REGIME_COMPOUNDING_FLAG ,
2155 TAX_REGIME_ID ,
2156 GEOGRAPHY_ID ,
2157 THRSHLD_CHK_TMPLT_CODE ,
2158 PERIOD_SET_NAME ,
2159 REP_TAX_AUTHORITY_ID ,
2160 COLL_TAX_AUTHORITY_ID ,
2161 CREATED_BY ,
2162 CREATION_DATE ,
2163 LAST_UPDATED_BY ,
2164 LAST_UPDATE_DATE ,
2165 LAST_UPDATE_LOGIN ,
2166 REQUEST_ID ,
2167 PROGRAM_APPLICATION_ID ,
2168 PROGRAM_ID ,
2169 PROGRAM_LOGIN_ID ,
2170 OBJECT_VERSION_NUMBER
2171 )
2172 VALUES
2173 (
2174 l_tax_regime_code , --TAX_REGIME_CODE
2175 NULL ,--PARENT_REGIME_CODE
2176 'N' ,--HAS_SUB_REGIME_FLAG
2177 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
2178 denorm_tbl(k).country_code ,--COUNTRY_CODE
2179 NULL ,--GEOGRAPHY_TYPE
2180 l_min_start_date ,--EFFECTIVE_FROM
2181 NULL ,--EFFECTIVE_TO
2182 NULL ,--EXCHANGE_RATE_TYPE
2183 NULL ,--TAX_CURRENCY_CODE ***** ATTENTION
2184 NULL ,--THRSHLD_GROUPING_LVL_CODE
2185 NULL ,--ROUNDING_RULE_CODE
2186 NULL ,--TAX_PRECISION ***** ATTENTION
2187 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
2188 'N' ,--TAX_STATUS_RULE_FLAG
2189 'SHIP_TO' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
2190 'N' ,--APPLICABILITY_RULE_FLAG
2191 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
2192 'N' ,--TAX_CALC_RULE_FLAG
2193 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
2194 'N' ,--TAX_RATE_THRSHLD_FLAG
2195 'N' ,--TAX_AMT_THRSHLD_FLAG
2196 'N' ,--TAX_RATE_RULE_FLAG
2197 'N' ,--TAXABLE_BASIS_RULE_FLAG
2198 'N' ,--DEF_INCLUSIVE_TAX_FLAG
2199 'Y' ,--HAS_OTHER_JURISDICTIONS_FLAG : 4610550
2200 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
2201 'Y' ,--ALLOW_EXEMPTIONS_FLAG
2202 'Y' ,--ALLOW_EXCEPTIONS_FLAG
2203 'N' ,--ALLOW_RECOVERABILITY_FLAG
2204 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
2205 'N' ,--AUTO_PRVN_FLAG
2206 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
2207 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
2208 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
2209 'N' ,--USE_LEGAL_MSG_FLAG
2210 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
2211 'N' ,--DEF_REC_SETTLE_OPTION_CODE
2212 'MIGRATED' ,--RECORD_TYPE_CODE
2213 NULL ,
2214 NULL ,
2215 NULL ,
2216 NULL ,
2217 NULL ,
2218 NULL ,
2219 NULL ,
2220 NULL ,
2221 NULL ,
2222 NULL ,
2223 NULL ,
2224 NULL ,
2225 NULL ,
2226 NULL ,
2227 NULL ,
2228 NULL ,
2229 'SHIP_TO_SITE' ,--DEF_REGISTR_PARTY_TYPE_CODE
2233 'N' ,--CROSS_REGIME_COMPOUNDING_FLAG
2230 'N' ,--REGISTRATION_TYPE_RULE_FLAG
2231 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG /** Set it to Y. Need P2P Change. **/
2232 NULL ,--REGIME_PRECEDENCE /** Can be updated for compounding migration **/
2234 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
2235 NULL ,--GEOGRAPHY_ID
2236 NULL ,--THRSHLD_CHK_TMPLT_CODE
2237 NULL ,--PERIOD_SET_NAME
2238 NULL ,--REP_TAX_AUTHORITY_ID
2239 NULL ,--COLL_TAX_AUTHORITY_ID
2240 fnd_global.user_id ,
2241 SYSDATE ,
2242 fnd_global.user_id ,
2243 SYSDATE ,
2244 fnd_global.conc_login_id ,
2245 fnd_global.conc_request_id ,--Request Id
2246 fnd_global.prog_appl_id ,--Program Application ID
2247 fnd_global.conc_program_id ,--Program Id
2248 fnd_global.conc_login_id ,--Program Login ID
2249 1
2250 )
2251 WHEN (NOT EXISTS (SELECT 1
2252 FROM ZX_REGIMES_B
2253 WHERE TAX_REGIME_CODE = l_tax_regime_code
2254 )
2255 ) THEN
2256 -- Need to insert _TL table for current language as l_tax_regime_name is
2257 -- derived using the following logic :
2258 -- Country Code '-SALES-TAX-' Seg Att1 '-' Seg Att2 '-' ...
2259 INTO ZX_REGIMES_TL
2260 (
2261 LANGUAGE ,
2262 SOURCE_LANG ,
2263 TAX_REGIME_NAME ,
2264 CREATION_DATE ,
2265 CREATED_BY ,
2266 LAST_UPDATE_DATE ,
2267 LAST_UPDATED_BY ,
2268 LAST_UPDATE_LOGIN ,
2269 TAX_REGIME_ID
2270 )
2271 VALUES
2272 (
2273 userenv('LANG'),
2274 userenv('LANG'),
2275 CASE WHEN l_tax_regime_name = UPPER(l_tax_regime_name)
2276 THEN Initcap(l_tax_regime_name)
2277 ELSE
2278 l_tax_regime_name
2279 END,
2280 SYSDATE,
2281 fnd_global.user_id ,
2282 SYSDATE ,
2283 fnd_global.user_id ,
2284 fnd_global.conc_login_id ,
2285 ZX_REGIMES_B_S.NEXTVAL
2286 )
2287 SELECT 1 FROM DUAL;
2288 END IF;
2289 END LOOP;
2290
2291
2292 /*-------------------------------------------------------------------------
2293 |
2294 | For Tax Vendor Regimes
2295 |
2296 | Regime Code :
2297 | 1. 'US-SALES-TAX-TAXWARE' if TAXWARE is installed in one of the OUs.
2298 | 2. 'US-SALES-TAX-VERTEX' if VERTEX is installed in one of the OUs.
2299 |
2300 +--------------------------------------------------------------------------*/
2301 /*
2302 INSERT ALL
2303 INTO zx_regimes_b
2304 (
2305 TAX_REGIME_CODE ,
2306 PARENT_REGIME_CODE ,
2307 HAS_SUB_REGIME_FLAG ,
2308 COUNTRY_OR_GROUP_CODE ,
2309 COUNTRY_CODE ,
2310 GEOGRAPHY_TYPE ,
2311 EFFECTIVE_FROM ,
2312 EFFECTIVE_TO ,
2313 EXCHANGE_RATE_TYPE ,
2314 TAX_CURRENCY_CODE ,
2315 THRSHLD_GROUPING_LVL_CODE ,
2316 ROUNDING_RULE_CODE ,
2317 TAX_PRECISION ,
2318 MINIMUM_ACCOUNTABLE_UNIT ,
2319 TAX_STATUS_RULE_FLAG ,
2320 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
2321 APPLICABILITY_RULE_FLAG ,
2322 PLACE_OF_SUPPLY_RULE_FLAG ,
2323 TAX_CALC_RULE_FLAG ,
2324 TAXABLE_BASIS_THRSHLD_FLAG ,
2325 TAX_RATE_THRSHLD_FLAG ,
2326 TAX_AMT_THRSHLD_FLAG ,
2327 TAX_RATE_RULE_FLAG ,
2328 TAXABLE_BASIS_RULE_FLAG ,
2329 DEF_INCLUSIVE_TAX_FLAG ,
2330 HAS_OTHER_JURISDICTIONS_FLAG ,
2331 ALLOW_ROUNDING_OVERRIDE_FLAG ,
2332 ALLOW_EXEMPTIONS_FLAG ,
2333 ALLOW_EXCEPTIONS_FLAG ,
2334 ALLOW_RECOVERABILITY_FLAG ,
2335 -- RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
2336 AUTO_PRVN_FLAG ,
2337 HAS_TAX_DET_DATE_RULE_FLAG ,
2338 HAS_EXCH_RATE_DATE_RULE_FLAG ,
2339 HAS_TAX_POINT_DATE_RULE_FLAG ,
2340 USE_LEGAL_MSG_FLAG ,
2341 REGN_NUM_SAME_AS_LE_FLAG ,
2342 DEF_REC_SETTLEMENT_OPTION_CODE ,
2343 RECORD_TYPE_CODE ,
2344 ATTRIBUTE1 ,
2345 ATTRIBUTE2 ,
2346 ATTRIBUTE3 ,
2347 ATTRIBUTE4 ,
2348 ATTRIBUTE5 ,
2349 ATTRIBUTE6 ,
2350 ATTRIBUTE7 ,
2351 ATTRIBUTE8 ,
2352 ATTRIBUTE9 ,
2356 ATTRIBUTE13 ,
2353 ATTRIBUTE10 ,
2354 ATTRIBUTE11 ,
2355 ATTRIBUTE12 ,
2357 ATTRIBUTE14 ,
2358 ATTRIBUTE15 ,
2359 ATTRIBUTE_CATEGORY ,
2360 DEF_REGISTR_PARTY_TYPE_CODE ,
2361 REGISTRATION_TYPE_RULE_FLAG ,
2362 TAX_INCLUSIVE_OVERRIDE_FLAG ,
2363 REGIME_PRECEDENCE ,
2364 CROSS_REGIME_COMPOUNDING_FLAG ,
2365 TAX_REGIME_ID ,
2366 GEOGRAPHY_ID ,
2367 THRSHLD_CHK_TMPLT_CODE ,
2368 PERIOD_SET_NAME ,
2369 REP_TAX_AUTHORITY_ID ,
2370 COLL_TAX_AUTHORITY_ID ,
2371 CREATED_BY ,
2372 CREATION_DATE ,
2373 LAST_UPDATED_BY ,
2374 LAST_UPDATE_DATE ,
2375 LAST_UPDATE_LOGIN ,
2376 REQUEST_ID ,
2377 PROGRAM_APPLICATION_ID ,
2378 PROGRAM_ID ,
2379 PROGRAM_LOGIN_ID ,
2380 OBJECT_VERSION_NUMBER
2381 )
2382 VALUES
2383 (
2384 l_tax_regime_code , --TAX_REGIME_CODE
2385 NULL ,--PARENT_REGIME_CODE
2386 'N' ,--HAS_SUB_REGIME_FLAG
2387 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
2388 'US' ,--COUNTRY_CODE
2389 NULL ,--GEOGRAPHY_TYPE
2390 l_min_start_date ,--EFFECTIVE_FROM
2391 NULL ,--EFFECTIVE_TO
2392 NULL ,--EXCHANGE_RATE_TYPE
2393 NULL ,--TAX_CURRENCY_CODE ***** ATTENTION
2394 NULL ,--THRSHLD_GROUPING_LVL_CODE
2395 NULL ,--ROUNDING_RULE_CODE
2396 NULL ,--TAX_PRECISION ***** ATTENTION
2397 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
2398 'N' ,--TAX_STATUS_RULE_FLAG
2399 'SHIP_TO' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
2400 'N' ,--APPLICABILITY_RULE_FLAG
2401 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
2402 'N' ,--TAX_CALC_RULE_FLAG
2403 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
2404 'N' ,--TAX_RATE_THRSHLD_FLAG
2405 'N' ,--TAX_AMT_THRSHLD_FLAG
2406 'N' ,--TAX_RATE_RULE_FLAG
2407 'N' ,--TAXABLE_BASIS_RULE_FLAG
2408 'N' ,--DEF_INCLUSIVE_TAX_FLAG
2409 'N' ,--HAS_OTHER_JURISDICTIONS_FLAG
2410 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
2411 'Y' ,--ALLOW_EXEMPTIONS_FLAG
2412 'Y' ,--ALLOW_EXCEPTIONS_FLAG
2413 'N' ,--ALLOW_RECOVERABILITY_FLAG
2414 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
2415 'N' ,--AUTO_PRVN_FLAG
2416 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
2417 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
2418 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
2419 'N' ,--USE_LEGAL_MSG_FLAG
2420 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
2421 'N' ,--DEF_REC_SETTLE_OPTION_CODE
2422 'MIGRATED' ,--RECORD_TYPE_CODE
2423 NULL ,
2424 NULL ,
2425 NULL ,
2426 NULL ,
2427 NULL ,
2428 NULL ,
2429 NULL ,
2430 NULL ,
2431 NULL ,
2432 NULL ,
2433 NULL ,
2434 NULL ,
2435 NULL ,
2436 NULL ,
2437 NULL ,
2438 NULL ,
2439 'SHIP_TO_SITE' ,--DEF_REGISTR_PARTY_TYPE_CODE
2440 'N' ,--REGISTRATION_TYPE_RULE_FLAG
2441 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG
2442 NULL ,--REGIME_PRECEDENCE
2443 'N' ,--CROSS_REGIME_COMPOUNDING_FLAG
2444 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
2445 NULL ,--GEOGRAPHY_ID
2446 NULL ,--THRSHLD_CHK_TMPLT_CODE
2447 NULL ,--PERIOD_SET_NAME
2448 NULL ,--REP_TAX_AUTHORITY_ID
2449 NULL ,--COLL_TAX_AUTHORITY_ID
2450 fnd_global.user_id ,
2451 SYSDATE ,
2452 fnd_global.user_id ,
2453 SYSDATE ,
2454 fnd_global.conc_login_id ,
2455 fnd_global.conc_request_id ,--Request Id
2456 fnd_global.prog_appl_id ,--Program Application ID
2460 )
2457 fnd_global.conc_program_id ,--Program Id
2458 fnd_global.conc_login_id ,--Program Login ID
2459 1
2461 SELECT distinct
2462 CASE
2463 WHEN asp.tax_database_view_set = '_A' THEN
2464 'US-SALES-TAX-TAXWARE'
2465 WHEN asp.tax_database_view_set = '_V' THEN
2466 'US-SALES-TAX-VERTEX'
2467 END l_tax_regime_code
2468 FROM ar_system_parameters_all asp
2469 WHERE asp.tax_database_view_set IN ('_A', '_V')
2470 AND asp.default_country = 'US'
2471 AND NOT EXISTS (SELECT 1
2472 FROM zx_regimes_b
2473 WHERE tax_regime_code IN ('US-SALES-TAX-TAXWARE', 'US-SALES-TAX-VERTEX')
2474 );
2475 */
2476
2477 END IF;
2478
2479
2480 /*-------------------------------------------------------------------------
2481 |
2482 | Populates data into zx_regimes_tl table
2483 |
2484 +--------------------------------------------------------------------------*/
2485 -- Bug 4688151 : LTE Tax Codes will derive tax_regime_name from
2486 IF L_MULTI_ORG_FLAG = 'Y'
2487 THEN
2488
2489 INSERT INTO ZX_REGIMES_TL
2490 (
2491 LANGUAGE ,
2492 SOURCE_LANG ,
2493 TAX_REGIME_NAME ,
2494 CREATION_DATE ,
2495 CREATED_BY ,
2496 LAST_UPDATE_DATE ,
2497 LAST_UPDATED_BY ,
2498 LAST_UPDATE_LOGIN ,
2499 TAX_REGIME_ID
2500
2501 )
2502 SELECT
2503 L.LANGUAGE_CODE ,
2504 userenv('LANG') ,
2505 CASE WHEN decode(d.global_attribute_category,
2506 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2507 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2508 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2509 B.TAX_REGIME_CODE)
2510 =
2511 UPPER(decode(d.global_attribute_category,
2512 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2513 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2514 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2515 B.TAX_REGIME_CODE))
2516 THEN
2517 Initcap(decode(d.global_attribute_category,
2518 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2519 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2520 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2521 B.TAX_REGIME_CODE))
2522 ELSE
2523 decode(d.global_attribute_category,
2524 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2525 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2526 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2527 B.TAX_REGIME_CODE)
2528 END ,
2529 SYSDATE ,
2530 fnd_global.user_id ,
2531 SYSDATE ,
2532 fnd_global.user_id ,
2533 fnd_global.conc_login_id ,
2534 B.TAX_REGIME_ID
2535 FROM
2536 FND_LANGUAGES L,
2537 ZX_REGIMES_B B,
2538 (select rates.tax_regime_code tax_regime_code,
2539 lkups.meaning meaning,
2540 params.global_attribute_category global_attribute_category
2541 from zx_rates_b rates,
2542 ar_vat_tax_all_b codes,
2543 ar_system_parameters_all params,
2544 fnd_lookups lkups
2545 where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
2546 AND codes.org_id = params.org_id
2547 and params.global_attribute13 = lkups.lookup_code
2548 and params.global_attribute_category in ('JL.AR.ARXSYSPA.SYS_PARAMETERS',
2549 'JL.BR.ARXSYSPA.Additional Info',
2550 'JL.CO.ARXSYSPA.SYS_PARAMETERS')
2551 and lkups.lookup_type = 'JLZZ_AR_TX_RULE_SET'
2552 group by rates.tax_regime_code,
2553 lkups.meaning,
2554 params.global_attribute_category
2555 ) D
2556 WHERE
2557 L.INSTALLED_FLAG in ('I', 'B')
2558 AND B.RECORD_TYPE_CODE = 'MIGRATED'
2559 --
2560 AND b.tax_regime_code = d.tax_regime_code (+)
2561 AND not exists
2562 (select NULL
2563 from ZX_REGIMES_TL T
2564 where T.TAX_REGIME_ID = B.TAX_REGIME_ID
2565 and T.LANGUAGE = L.LANGUAGE_CODE);
2566 ELSE
2567
2568 INSERT INTO ZX_REGIMES_TL
2569 (
2570 LANGUAGE ,
2571 SOURCE_LANG ,
2572 TAX_REGIME_NAME ,
2573 CREATION_DATE ,
2574 CREATED_BY ,
2575 LAST_UPDATE_DATE ,
2576 LAST_UPDATED_BY ,
2577 LAST_UPDATE_LOGIN ,
2578 TAX_REGIME_ID
2579
2580 )
2581 SELECT
2582 L.LANGUAGE_CODE ,
2583 userenv('LANG') ,
2584 case when
2585 decode(d.global_attribute_category,
2586 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2587 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2588 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2589 B.TAX_REGIME_CODE)
2590 =
2591 UPPER(decode(d.global_attribute_category,
2592 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2593 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2597 Initcap(decode(d.global_attribute_category,
2594 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2595 B.TAX_REGIME_CODE))
2596 then
2598 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2599 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2600 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2601 B.TAX_REGIME_CODE))
2602 else
2603 decode(d.global_attribute_category,
2604 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2605 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2606 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2607 B.TAX_REGIME_CODE)
2608 end ,
2609 SYSDATE ,
2610 fnd_global.user_id ,
2611 SYSDATE ,
2612 fnd_global.user_id ,
2613 fnd_global.conc_login_id ,
2614 B.TAX_REGIME_ID
2615 FROM
2616 FND_LANGUAGES L,
2617 ZX_REGIMES_B B,
2618 (select rates.tax_regime_code tax_regime_code,
2619 lkups.meaning meaning,
2620 params.global_attribute_category global_attribute_category
2621 from zx_rates_b rates,
2622 ar_vat_tax_all_b codes,
2623 ar_system_parameters_all params,
2624 fnd_lookups lkups
2625 where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
2626 AND codes.org_id = params.org_id
2627 AND codes.org_id = l_org_id
2628 and params.global_attribute13 = lkups.lookup_code
2629 and params.global_attribute_category in ('JL.AR.ARXSYSPA.SYS_PARAMETERS',
2630 'JL.BR.ARXSYSPA.Additional Info',
2631 'JL.CO.ARXSYSPA.SYS_PARAMETERS')
2632 and lkups.lookup_type = 'JLZZ_AR_TX_RULE_SET'
2633 group by rates.tax_regime_code,
2634 lkups.meaning,
2635 params.global_attribute_category
2636 ) D
2637 WHERE
2638 L.INSTALLED_FLAG in ('I', 'B')
2639 AND B.RECORD_TYPE_CODE = 'MIGRATED'
2640 --
2641 AND b.tax_regime_code = d.tax_regime_code (+)
2642 AND not exists
2643 (select NULL
2644 from ZX_REGIMES_TL T
2645 where T.TAX_REGIME_ID = B.TAX_REGIME_ID
2646 and T.LANGUAGE = L.LANGUAGE_CODE);
2647
2648
2649 END IF;
2650
2651 END load_regimes;
2652
2653
2654 PROCEDURE update_tax_status AS
2655 BEGIN
2656 BEGIN
2657 FOR cursor_rec IN
2658 (
2659 SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'INPUT'
2660 INTERSECT
2661 SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'OUTPUT'
2662 AND tax_status_code <> 'STANDARD-AR-INPUT')
2663 LOOP
2664 UPDATE zx_update_criteria_results SET tax_status_code = 'STANDARD-INPUT' WHERE tax_regime_code = cursor_rec.tax_regime_code AND tax = cursor_rec.tax AND
2665 tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'INPUT';
2666
2667 UPDATE zx_update_criteria_results SET tax_status_code = 'STANDARD-OUTPUT' WHERE tax_regime_code = cursor_rec.tax_regime_code AND tax = cursor_rec.tax AND
2668 tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'OUTPUT';
2669
2670 END LOOP;
2671 EXCEPTION WHEN OTHERS THEN
2672 NULL;
2673 END;
2674
2675 END;
2676
2677
2678
2679 -- ****** CONSTRUCTOR ******
2680 BEGIN
2681 -- ****** Determine min(start_date) ******
2682 BEGIN
2683 SELECT min(start_date)
2684 INTO l_ap_min_start_date
2685 FROM ap_tax_codes_all;
2686 EXCEPTION
2687 WHEN NO_DATA_FOUND THEN
2688 l_ap_min_start_date := sysdate;
2689 END;
2690
2691 BEGIN
2692 SELECT min(start_date)
2693 INTO l_ar_min_start_date
2694 FROM ar_vat_tax_all_b;
2695 EXCEPTION
2696 WHEN NO_DATA_FOUND THEN
2697 l_ar_min_start_date := sysdate;
2698 END;
2699
2700 BEGIN
2701 SELECT count(*)
2702 INTO l_ap_count
2703 FROM ap_tax_codes_all;
2704 END;
2705
2706 BEGIN
2707 SELECT count(*)
2708 INTO l_ar_count
2709 FROM ar_vat_tax_all_b;
2710 END;
2711
2712 IF l_ap_count = 0 THEN
2713 l_ap_min_start_date := sysdate;
2714 ELSIF l_ar_count = 0 THEN
2715 l_ar_min_start_date := sysdate;
2716 END IF;
2717
2718 IF l_ap_min_start_date >= l_ar_min_start_date THEN
2719 l_min_start_date := l_ar_min_start_date;
2720 ELSE
2721 l_min_start_date := l_ap_min_start_date;
2722 END IF;
2723
2724 BEGIN
2725 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
2726 FND_PRODUCT_GROUPS;
2727
2728 IF L_MULTI_ORG_FLAG = 'N' THEN
2729
2730 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
2731
2732 IF L_ORG_ID IS NULL THEN
2733 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
2734 END IF;
2735 ELSE
2736 L_ORG_ID := NULL;
2737 END IF;
2738
2739
2740 EXCEPTION
2741 WHEN OTHERS THEN
2742 arp_util_tax.debug('Exception in Common Migrate Tax Definition Constructor : '||sqlerrm);
2743
2744 END;
2745
2746 END;