[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.50 2010/08/24 12:07:37 ssanka 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
284 CASE WHEN
281 offset.GLOBAL_ATTRIBUTE1||'-OFFST'
282 END
283 END,
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,
360 last_update_date,
361 last_update_login
362 )
363 SELECT
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,
490 offset.tax_type),1,24))
491 ) ||'-OFFST' tax,
492 DECODE(offset.global_attribute_category,
493 'JA.TW.APXTADTC.TAX_CODES',
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
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
630 asp.global_attribute13 || '-' || codes.tax_type
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,
689 NULL recovery_type_code,
686 'O', 'STANDARD',
687 'I', 'STANDARD-AR-INPUT',
688 'STANDARD')) tax_status_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,
743 last_update_login
744 )
745 SELECT
746 codes.vat_tax_id tax_code_id,
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 -- bug#8817419
850 -- Commented the call to the problem procedure.
851 --update_tax_status;
852
853 END load_results_for_ar;
854
855 /*===========================================================================+
856 | PROCEDURE
857 | load_tax_comp_results_for_ar
858 |
859 | DESCRIPTION
860 | 1. Populates data into zx_update_criteria_results table based on AR data in
861 | zx_tax_relations_t .
862 |
863 | ASSUMPTION:
864 | Since only AR related tax codes get migrated into zx_tax_priorities_t we do
865 | not have a load_tax_results_for_ap procedure.
866 |
867 |
868 |
869 | MODIFICATION HISTORY
870 | 04/22/2005 Arnab Sengupta
871 |
872 +==========================================================================*/
873
874 PROCEDURE load_tax_comp_results_for_ar (p_tax_id NUMBER) AS
875 BEGIN
876
877 /*Include this call to populate zx_tax_priorities_t before loading the results table
878 Bug 5691957 */
879 BEGIN
880 zx_tcm_compound_pkg.main;
881 EXCEPTION WHEN OTHERS THEN
882 NULL;
883 END;
884
885 IF L_MULTI_ORG_FLAG = 'Y'
886 THEN
887 INSERT
888 INTO zx_update_criteria_results
889 (
890 tax_code_id,
891 org_id,
892 tax_code,
893 tax_class,
894 tax_regime_code,
895 tax,
896 tax_precedence,
897 regime_precedence,
898 tax_status_code,
899 recovery_type_code,
900 frozen,
901 country_code,
902 effective_from,
903 effective_to,
904 created_by,
905 creation_date,
906 last_updated_by,
907 last_update_date,
908 last_update_login
909 )
910 SELECT
911 codes.vat_tax_id tax_code_id,
912 codes.org_id org_id,
913 codes.tax_code tax_code,
914 'OUTPUT' tax_class,
915 CASE WHEN codes.tax_type <> 'SALES_TAX' then
916 Zx_Migrate_Util.Get_Country(Codes.Org_Id)||'-Tax'
917 ELSE
918
919 Zx_Migrate_Util.GET_TAX_REGIME(
920 codes.tax_type,
921 codes.org_id)
922 END tax_regime_code,
923 NVL(CASE WHEN codes.global_attribute_category IN ('JE.CZ.ARXSUVAT.TAX_ORIGIN',
924 'JE.HU.ARXSUVAT.TAX_ORIGIN',
925 'JE.PL.ARXSUVAT.TAX_ORIGIN')
926 THEN CASE WHEN LENGTHB(codes.global_attribute1) > 30 THEN
927 RTRIM(SUBSTRB(codes.global_attribute1,1,24))
928 ELSE codes.global_attribute1
929 END
930 WHEN codes.global_attribute_category IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
931 'JL.BR.ARXSUVAT.Tax Information',
932 'JL.CO.ARXSUVAT.AR_VAT_TAX')
933 THEN (select tax_category
934 from jl_zz_ar_tx_categ_all
935 where TO_CHAR(tax_category_id) = codes.global_attribute1
936 and org_id = codes.org_id)
937 ELSE
938 NULL
939 END,
940 CASE WHEN codes.tax_type = Zx_Migrate_Util.GET_TAX(
941 codes.tax_code,
942 codes.tax_type)
943 THEN
944 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
945 codes.tax_code,
946 codes.tax_type),1,30))
947 ELSE
948 CASE WHEN LENGTHB(Zx_Migrate_Util.GET_TAX(
949 codes.tax_code,
950 codes.tax_type)||'-'||codes.tax_type) > 30
951 THEN
952 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
953 codes.tax_code,
954 codes.tax_type)||'-'||codes.tax_type,1,30))
955 ELSE
956 Zx_Migrate_Util.GET_TAX(
957 codes.tax_code,
958 codes.tax_type)||'-'||codes.tax_type
959 END
960 END
961 ) tax,
962 --zxpt.tax_code tax,
963 zxpt.tax_precedence tax_precedence,
964 zxpt.regime_precedence regime_precedence,
965 DECODE(codes.global_attribute_category,
966 'JA.TW.ARXSUVAT.VAT_TAX', nvl(codes.global_attribute1,'STANDARD'),
967 DECODE(codes.tax_class,
968 'O', 'STANDARD',
969 'I', 'STANDARD-AR-INPUT',
970 'STANDARD')) tax_status_code,
971 NULL recovery_type_code,
972 'N' frozen,
973 zx_migrate_util.get_country(codes.org_id) country_code,
974 codes.start_date effective_from,
975 codes.end_date effective_to,
976 fnd_global.user_id created_by,
977 sysdate creation_date,
978 fnd_global.user_id last_updated_by,
979 sysdate last_updated_date,
980 fnd_global.conc_login_id last_update_login
981 FROM ar_vat_tax_all_b codes,
982 ar_system_parameters_all asp,
983 zx_tax_priorities_t zxpt
984
985 WHERE
986 asp.org_id = codes.org_id
987 AND codes.vat_tax_id = zxpt.tax_id
988 -- Eliminate Tax Vendor Tax Codes
989 AND asp.tax_database_view_set = 'O'
990 -- Eliminate LTE tax codes
991 AND (codes.global_attribute_category is null OR
992 codes.global_attribute_category NOT IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
993 'JL.BR.ARXSUVAT.AR_VAT_TAX',
994 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
995 -- Eliminate tax_type = 'LOCATION'
996 AND codes.tax_type <> 'LOCATION'
997 --Added following conditions for Sync process
998 AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
999 --Rerunability
1000 AND NOT EXISTS (SELECT 1
1001 FROM zx_update_criteria_results zucr
1002 WHERE zucr.tax_code_id = nvl(p_tax_id,codes.vat_tax_id)
1003 AND zucr.tax_class = 'OUTPUT'
1004 );
1005 ELSE
1006
1007 INSERT
1008 INTO zx_update_criteria_results
1009 (
1010 tax_code_id,
1011 org_id,
1012 tax_code,
1013 tax_class,
1014 tax_regime_code,
1015 tax,
1016 tax_precedence,
1017 regime_precedence,
1018 tax_status_code,
1019 recovery_type_code,
1020 frozen,
1021 country_code,
1022 effective_from,
1023 effective_to,
1024 created_by,
1025 creation_date,
1026 last_updated_by,
1027 last_update_date,
1028 last_update_login
1029 )
1030 SELECT
1031 codes.vat_tax_id tax_code_id,
1032 codes.org_id org_id,
1033 codes.tax_code tax_code,
1034 'OUTPUT' tax_class,
1035 CASE WHEN codes.tax_type <> 'SALES_TAX' then
1036 Zx_Migrate_Util.Get_Country(Codes.Org_Id)||'-Tax'
1037 ELSE
1038 Zx_Migrate_Util.GET_TAX_REGIME(
1039 codes.tax_type,
1040 codes.org_id)
1041 END tax_regime_code,
1042 NVL(CASE WHEN codes.global_attribute_category IN ('JE.CZ.ARXSUVAT.TAX_ORIGIN',
1043 'JE.HU.ARXSUVAT.TAX_ORIGIN',
1044 'JE.PL.ARXSUVAT.TAX_ORIGIN')
1045 THEN CASE WHEN LENGTHB(codes.global_attribute1) > 30 THEN
1046 RTRIM(SUBSTRB(codes.global_attribute1,1,24))
1047 ELSE codes.global_attribute1
1048 END
1049 WHEN codes.global_attribute_category IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
1050 'JL.BR.ARXSUVAT.Tax Information',
1051 'JL.CO.ARXSUVAT.AR_VAT_TAX')
1052 THEN (select tax_category
1053 from jl_zz_ar_tx_categ_all
1054 where TO_CHAR(tax_category_id) = codes.global_attribute1
1055 and org_id = codes.org_id)
1056 ELSE
1057 NULL
1058 END,
1059 CASE WHEN codes.tax_type = Zx_Migrate_Util.GET_TAX(
1060 codes.tax_code,
1061 codes.tax_type)
1062 THEN
1063 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
1064 codes.tax_code,
1065 codes.tax_type),1,30))
1066 ELSE
1067 CASE WHEN LENGTHB(Zx_Migrate_Util.GET_TAX(
1068 codes.tax_code,
1069 codes.tax_type)||'-'||codes.tax_type) > 30
1070 THEN
1071 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
1072 codes.tax_code,
1073 codes.tax_type)||'-'||codes.tax_type,1,30))
1074 ELSE
1075 Zx_Migrate_Util.GET_TAX(
1076 codes.tax_code,
1077 codes.tax_type)||'-'||codes.tax_type
1078 END
1079 END
1080 ) tax,
1081 --zxpt.tax_code tax,
1082 zxpt.tax_precedence tax_precedence,
1083 zxpt.regime_precedence regime_precedence,
1084 DECODE(codes.global_attribute_category,
1085 'JA.TW.ARXSUVAT.VAT_TAX', nvl(codes.global_attribute1,'STANDARD'),
1086 DECODE(codes.tax_class,
1087 'O', 'STANDARD',
1088 'I', 'STANDARD-AR-INPUT',
1089 'STANDARD')) tax_status_code,
1090 NULL recovery_type_code,
1091 'N' frozen,
1092 zx_migrate_util.get_country(codes.org_id) country_code,
1093 codes.start_date effective_from,
1094 codes.end_date effective_to,
1095 fnd_global.user_id created_by,
1096 sysdate creation_date,
1097 fnd_global.user_id last_updated_by,
1098 sysdate last_updated_date,
1099 fnd_global.conc_login_id last_update_login
1100 FROM ar_vat_tax_all_b codes,
1101 ar_system_parameters_all asp,
1102 zx_tax_priorities_t zxpt
1103
1104 WHERE
1105 asp.org_id = codes.org_id
1106 AND codes.org_id = l_org_id
1107 AND codes.vat_tax_id = zxpt.tax_id
1108 -- Eliminate Tax Vendor Tax Codes
1109 AND asp.tax_database_view_set = 'O'
1110 -- Eliminate LTE tax codes
1111 AND (codes.global_attribute_category is null OR
1112 codes.global_attribute_category NOT IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
1113 'JL.BR.ARXSUVAT.AR_VAT_TAX',
1114 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
1115 -- Eliminate tax_type = 'LOCATION'
1116 AND codes.tax_type <> 'LOCATION'
1117 --Added following conditions for Sync process
1118 AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
1119 --Rerunability
1120 AND NOT EXISTS (SELECT 1
1121 FROM zx_update_criteria_results zucr
1122 WHERE zucr.tax_code_id = nvl(p_tax_id,codes.vat_tax_id)
1123 AND zucr.tax_class = 'OUTPUT'
1124 );
1125 END IF;
1126 END load_tax_comp_results_for_ar;
1127
1128
1129 /*===========================================================================+
1130 | PROCEDURE
1131 | load_results_for_intercomp_ap
1132 |
1133 | DESCRIPTION
1134 | Populates data into zx_update_criteria_results table for AP Tax Codes
1135 | that is used in intercompany transaction.
1136 |
1137 | MTL_INTERCOMPANY_PARAMTERS table stores information about two OUs that
1138 | are used for intercompany transactions. The customer related information is
1139 | used by the shipping organization (SHIP_ORGANIZATION_ID) for AR invoicing
1140 | purposes. The supplier related information is used by the selling organization
1141 | (SELL_ORGANIZATION_ID) for AP invoicing purposes.
1142 |
1143 | Tax Regime Code derived from AP Tax Code (used to create AP invoice) is
1144 | overriden by that of AR Tax Code (used to create AR invoice).
1145 |
1146 | Set zx_criteria_results.intercompany_flag to 'Y' for AP Tax Codes/AR Tax Codes
1147 | that are used for intercompany transactions.
1148 |
1149 |
1150 | MODIFICATION HISTORY
1151 | 04/29/2005 Yoshimichi Konishi Created
1152 |
1153 +==========================================================================*/
1154 PROCEDURE load_results_for_intercomp_ap (p_tax_id NUMBER) AS
1155 BEGIN
1156 INSERT
1157 INTO zx_update_criteria_results
1158 (
1159 tax_code_id,
1160 org_id,
1161 tax_code,
1162 tax_class,
1163 tax_regime_code,
1164 tax,
1165 tax_status_code,
1166 recovery_type_code,
1167 frozen,
1168 country_code,
1169 created_by,
1170 creation_date,
1171 last_updated_by,
1172 last_update_date,
1173 last_update_login,
1174 effective_from,
1175 effective_to,
1176 intercompany_flag
1177 )
1178 SELECT
1179 DISTINCT
1180 ap_codes.tax_id tax_code_id,
1181 ap_codes.org_id org_id,
1182 ap_codes.name tax_code,
1183 'INPUT' tax_class,
1184 Zx_Migrate_Util.GET_TAX_REGIME(
1185 ap_codes.tax_type,
1186 ap_codes.org_id) tax_regime_code,
1187 DECODE(ap_codes.global_attribute_category,
1188 'JE.CZ.ARXSUVAT.TAX_ORIGIN',
1189 CASE WHEN LENGTHB(ap_codes.global_attribute1) > 30 THEN
1190 RTRIM(SUBSTRB(ap_codes.global_attribute1,1,24))
1191 ELSE ap_codes.global_attribute1 END,
1192 'JE.HU.ARXSUVAT.TAX_ORIGIN',
1193 CASE WHEN LENGTHB(ap_codes.global_attribute1) > 30 THEN
1194 RTRIM(SUBSTRB(ap_codes.global_attribute1,1,24))
1195 ELSE ap_codes.global_attribute1 END,
1196 'JE.PL.ARXSUVAT.TAX_ORIGIN',
1197 CASE WHEN LENGTHB(ap_codes.global_attribute1) > 30 THEN
1198 RTRIM(SUBSTRB(ap_codes.global_attribute1,1,24))
1199 ELSE ap_codes.global_attribute1 END,
1200 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
1201 ap_codes.name,
1202 ap_codes.tax_type),1,30))
1203 ) tax,
1204 DECODE(ap_codes.global_attribute_category,
1205 'JA.TW.ARXSUVAT.VAT_TAX',
1206 nvl(ap_codes.global_attribute1,'STANDARD'),
1207 'STANDARD') tax_status_code,
1208 'STANDARD' recovery_type_code,
1209 'N' frozen,
1210 zx_migrate_util.get_country(ap_codes.org_id) country_code,
1211 fnd_global.user_id created_by,
1212 sysdate creation_date,
1213 fnd_global.user_id last_updated_by,
1214 sysdate last_updated_date,
1215 fnd_global.conc_login_id last_update_login,
1216 ap_codes.start_date effective_from,
1217 ap_codes.inactive_date effective_to,
1218 'Y' intercompany_flag
1219 FROM ap_tax_codes_all ap_codes,
1220 ar_vat_tax_all_b ar_codes,
1221 financials_system_params_all fsp,
1222 mtl_intercompany_parameters intcomp
1223 WHERE ap_codes.tax_type NOT IN ('AWT','TAX_GROUP')
1224 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)
1225 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)
1226 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)
1227 AND ap_codes.name = ar_codes.tax_code
1228 AND intcomp.flow_type = 2 -- Bug 4697235 : Specify flow_type=2 which is procurement
1229 -- Sync process
1230 AND ap_codes.tax_id = nvl(p_tax_id,ap_codes.tax_id)
1231 -- Rerunability
1232 AND NOT EXISTS (SELECT 1
1233 FROM zx_update_criteria_results zucr
1234 WHERE zucr.tax_code_id = nvl(p_tax_id,ap_codes.tax_id)
1235 AND zucr.tax_class = 'INPUT'
1236 );
1237
1238 END load_results_for_intercomp_ap;
1239
1240
1241
1242
1243 /*===========================================================================+
1244 | PROCEDURE
1245 | load_results_for_intercomp_ar
1246 |
1247 | DESCRIPTION
1248 | Populates data into zx_update_criteria_results table for AR Tax Codes
1249 | that is used in intercompany transaction.
1250 |
1251 | MTL_INTERCOMPANY_PARAMTERS table stores information about two OUs that
1252 | are used for intercompany transactions. The customer related information is
1253 | used by the shipping organization (SHIP_ORGANIZATION_ID) for AR invoicing
1254 | purposes. The supplier related information is used by the selling organization
1255 | (SELL_ORGANIZATION_ID) for AP invoicing purposes.
1256 |
1257 | Tax Regime Code derived from AP Tax Code (used to create AP invoice) is
1258 | overriden by that of AR Tax Code (used to create AR invoice).
1259 |
1260 | Set zx_criteria_results.intercompany_flag to 'Y' for AP Tax Codes/AR Tax Codes
1261 | that are used for intercompany transactions.
1262 |
1263 |
1264 | MODIFICATION HISTORY
1265 | 04/29/2005 Yoshimichi Konishi Created
1266 |
1267 +==========================================================================*/
1268 PROCEDURE load_results_for_intercomp_ar (p_tax_id NUMBER) AS
1269 BEGIN
1270
1271 INSERT
1272 INTO zx_update_criteria_results
1273 (
1274 tax_code_id,
1275 org_id,
1276 tax_code,
1277 tax_class,
1278 tax_regime_code,
1279 tax,
1280 tax_status_code,
1281 recovery_type_code,
1282 frozen,
1283 country_code,
1284 created_by,
1285 creation_date,
1286 last_updated_by,
1287 last_update_date,
1288 last_update_login,
1289 effective_from,
1290 effective_to,
1291 intercompany_flag
1292 )
1293 SELECT
1294 DISTINCT
1295 ar_codes.vat_tax_id tax_code_id,
1296 decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id) org_id,
1297 ar_codes.tax_code tax_code,
1298 'OUTPUT' tax_class,
1299 Zx_Migrate_Util.GET_TAX_REGIME(
1300 ar_codes.tax_type,
1301 decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id)) tax_regime_code,
1302 -- YK:02/09/2005:Needs substrb
1303 NVL(CASE WHEN ar_codes.global_attribute_category IN ('JE.CZ.ARXSUVAT.TAX_ORIGIN',
1304 'JE.HU.ARXSUVAT.TAX_ORIGIN',
1305 'JE.PL.ARXSUVAT.TAX_ORIGIN')
1306 THEN CASE WHEN ar_codes.global_attribute1 > 30 THEN
1307 RTRIM(SUBSTRB(ar_codes.global_attribute1,1,24))
1308 ELSE ar_codes.global_attribute1
1309 END
1310 WHEN ar_codes.global_attribute_category IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
1311 'JL.BR.ARXSUVAT.Tax Information',
1312 'JL.CO.ARXSUVAT.AR_VAT_TAX')
1313 THEN (select tax_category
1314 from jl_zz_ar_tx_categ_all
1315 where TO_CHAR(tax_category_id) = ar_codes.global_attribute1
1316 and org_id = ar_codes.org_id)
1317 ELSE
1318 NULL
1319 END,
1320 RTRIM(SUBSTRB(Zx_Migrate_Util.GET_TAX(
1321 ar_codes.tax_code,
1322 ar_codes.tax_type),1,30))) tax,
1323 DECODE(ar_codes.global_attribute_category,
1324 'JA.TW.ARXSUVAT.VAT_TAX', nvl(ar_codes.global_attribute1,'STANDARD'),
1325 DECODE(ar_codes.tax_class,
1326 'O', 'STANDARD',
1327 'I', 'STANDARD-AR-INPUT',
1328 'STANDARD')) tax_status_code,
1329 NULL recovery_type_code,
1330 'N' frozen,
1331 zx_migrate_util.get_country(decode(l_multi_org_flag,'N',l_org_id,ar_codes.org_id)) country_code,
1332 fnd_global.user_id created_by,
1333 sysdate creation_date,
1334 fnd_global.user_id last_updated_by,
1335 sysdate last_updated_date,
1336 fnd_global.conc_login_id last_update_login,
1337 ar_codes.start_date effective_from,
1338 ar_codes.end_date effective_to,
1339 'Y' intercompany_flag
1340 FROM ar_vat_tax_all_b ar_codes,
1341 ap_tax_codes_all ap_codes,
1342 ar_system_parameters_all asp,
1343 mtl_intercompany_parameters intcomp
1344 WHERE ar_codes.tax_type <> 'TAX_GROUP'
1345 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)
1346 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)
1347 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)
1348 AND ap_codes.name = ar_codes.tax_code
1349 AND intcomp.flow_type = 2 -- Bug 4697235 : Specify flow_type=2 which is for procurement
1350 -- Eliminate Tax Vendor Tax Codes
1351 AND asp.tax_database_view_set = 'O'
1352 -- Eliminate LTE tax codes
1353 -- Bug 4688151 : Do not eliminate LTE tax codes
1354 -- AND (ar_codes.global_attribute_category is null OR
1355 -- ar_codes.global_attribute_category NOT IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
1356 -- 'JL.BR.ARXSUVAT.AR_VAT_TAX',
1357 -- 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
1358 -- Eliminate tax_type = 'LOCATION'
1359 AND ar_codes.tax_type <> 'LOCATION'
1360 --Added following conditions for Sync process
1361 AND ar_codes.vat_tax_id = nvl(p_tax_id, ar_codes.vat_tax_id)
1362 --Rerunability
1363 AND NOT EXISTS (SELECT 1
1364 FROM zx_update_criteria_results zucr
1365 WHERE zucr.tax_code_id = nvl(p_tax_id,ar_codes.vat_tax_id)
1366 AND zucr.tax_class = 'OUTPUT'
1367 );
1368 END load_results_for_intercomp_ar;
1369
1370
1371 /*===========================================================================+
1372 | PROCEDURE
1373 | load_regimes
1374 |
1375 | DESCRIPTION
1376 | 1. Populates data into zx_regimes_b table based on data in
1377 | zx_update_criteria_results table for normal tax codes.
1378 | 2. Populates data into zx_regimes_b for Brazilian IPI
1379 | 3. Populates data into zx_regimes_b for Brazilian ISS
1380 | 4. Populates data into zx_regimes_b for GTE US Sales Tax Regimes
1381 | 5. Populates data into zx_regimes_b for Tax Vendor Regimes
1382 | 6. Populates data into zx_regimes_tl
1383 |
1384 |
1385 | NOTES
1386 | 1. Select distinct of tax_regime_code and country_code. Update Criteria UI
1387 | makes sure that this combination is unique.
1388 | 2. Tax Regime Code for unassigned offset tax codes handling. It is County
1389 | Code '-' OFFSET by default. User could override it through Criteria UI.
1390 |
1391 | MODIFICATION HISTORY
1392 | 02/15/2005 Yoshimichi Konishi Created.
1393 |
1394 +==========================================================================*/
1395 PROCEDURE load_regimes AS
1396 -- ****** TYPES ******
1397 TYPE denorm_tbl_type IS TABLE OF zx_migrate_tax_def_common.loc_str_rec_type INDEX BY BINARY_INTEGER;
1398
1399 -- ****** VARIABLES ******
1400 null_loc_str_rec loc_str_rec_type;
1401 denorm_tbl denorm_tbl_type;
1402 denorm_err_tbl denorm_tbl_type;
1403 cnt PLS_INTEGER;
1404 i PLS_INTEGER;
1405 d PLS_INTEGER;
1406 k PLS_INTEGER;
1407 l_temp_id_flex_num NUMBER; --fnd_id_flex_segments.id_flex_num%TYPE
1408 l_temp_seg_num NUMBER(15); --fnd_id_flex_segments.segment_num%TYPE
1409 l_temp_seg_att_type VARCHAR2(30); --fnd_segment_attribute_values.segment_attribute_type%TYPE
1410 l_temp_tax_currency_code VARCHAR2(15); --ar_system_parameters_all.tax_currency_code%TYPE
1411 l_temp_tax_precision NUMBER(1); --ar_system_parameters_all.tax_precision%TYPE
1412 l_temp_tax_mau NUMBER; --ar_system_parameters_all.tax_minimum_accountable_unit%TYPE
1413 l_temp_country_code VARCHAR2(60); --ar_system_parameters_all.default_country%TYPE
1414 l_temp_rounding_rule_code VARCHAR2(30); --ar_system_parameters_all.tax_rounding_rule%TYPE
1415 l_temp_tax_invoice_print VARCHAR2(30); --ar_system_parameters_all.tax_invoice_print%TYPE
1416 l_temp_allow_rounding_override VARCHAR2(30); --ar_system_parameters_all.tax_rounding_allow_override%TYPE
1417 l_temp_org_id NUMBER(15); --ar_system_parameters_all.org_id%TYPE
1418 l_tax_regime_name VARCHAR2(80); --zx_regimes_tl.tax_regime_name%TYPE
1419 l_tax_regime_code VARCHAR2(30); --zx_regimes_b.tax_regime_code%TYPE
1420
1421
1422 -- ****** CURSORS ******
1423 CURSOR loc_str_cur IS
1424 SELECT DISTINCT
1425 segment.id_flex_num id_flex_num,
1426 asp.default_country default_country,
1427 segment.segment_num seg_num,
1428 qual.segment_attribute_type seg_att_type,
1429 decode(l_multi_org_flag,'N',l_org_id,asp.org_id) org_id,
1430 NVL(asp.tax_currency_code, gsob.currency_code)
1431 tax_currency_code,
1432 asp.tax_precision tax_precision,
1433 asp.tax_minimum_accountable_unit tax_mau,
1434 asp.tax_rounding_rule rounding_rule_code,
1435 asp.tax_rounding_allow_override allow_rounding_override
1436 FROM fnd_id_flex_structures str,
1437 fnd_id_flex_segments segment,
1438 fnd_segment_attribute_values qual,
1439 ar_system_parameters_all asp,
1440 ar_vat_tax_all_b avt,
1441 gl_sets_of_books gsob
1442 WHERE str.id_flex_code = 'RLOC'
1443 AND str.application_id = 222
1444 AND str.application_id = segment.application_id
1445 AND str.id_flex_num = segment.id_flex_num
1446 AND str.id_flex_code = segment.id_flex_code
1447 AND segment.application_id = 222
1448 AND segment.id_flex_code = 'RLOC'
1449 AND segment.application_id= qual.application_id
1450 AND segment.id_flex_code = qual.id_flex_code
1451 AND segment.id_flex_num = qual.id_flex_num
1452 AND segment.application_column_name = qual.application_column_name
1453 AND segment.enabled_flag = 'Y'
1454 AND qual.attribute_value = 'Y'
1455 AND qual.segment_attribute_type NOT IN ('EXEMPT_LEVEL', 'TAX_ACCOUNT')
1456 AND asp.location_structure_id = str.id_flex_num
1457 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)
1458 AND avt.tax_type = 'LOCATION'
1459 AND asp.tax_database_view_set IN ('O', '_V', '_A') -- Bug 4880905
1460 AND asp.set_of_books_id = gsob.set_of_books_id
1461 ORDER BY 1,2,3,4,5;
1462
1463 BEGIN
1464 /*--------------------------------------------------------------------------
1465 |
1466 | Populating zx_regimes_b from zx_update_criteria_results
1467 |
1468 +---------------------------------------------------------------------------*/
1469
1470 INSERT INTO ZX_REGIMES_B
1471 (
1472 TAX_REGIME_CODE ,
1473 PARENT_REGIME_CODE ,
1474 REGIME_PRECEDENCE ,
1475 HAS_SUB_REGIME_FLAG ,
1476 COUNTRY_OR_GROUP_CODE ,
1477 COUNTRY_CODE ,
1478 GEOGRAPHY_TYPE ,
1479 EFFECTIVE_FROM ,
1480 EFFECTIVE_TO ,
1481 EXCHANGE_RATE_TYPE ,
1482 TAX_CURRENCY_CODE ,
1483 THRSHLD_GROUPING_LVL_CODE ,
1484 ROUNDING_RULE_CODE ,
1485 TAX_PRECISION ,
1486 MINIMUM_ACCOUNTABLE_UNIT ,
1487 TAX_STATUS_RULE_FLAG ,
1488 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
1489 APPLICABILITY_RULE_FLAG ,
1490 PLACE_OF_SUPPLY_RULE_FLAG ,
1491 TAX_CALC_RULE_FLAG ,
1492 TAXABLE_BASIS_THRSHLD_FLAG ,
1493 TAX_RATE_THRSHLD_FLAG ,
1494 TAX_AMT_THRSHLD_FLAG ,
1495 TAX_RATE_RULE_FLAG ,
1496 TAXABLE_BASIS_RULE_FLAG ,
1497 DEF_INCLUSIVE_TAX_FLAG ,
1498 HAS_OTHER_JURISDICTIONS_FLAG ,
1499 ALLOW_ROUNDING_OVERRIDE_FLAG ,
1500 ALLOW_EXEMPTIONS_FLAG ,
1501 ALLOW_EXCEPTIONS_FLAG ,
1502 ALLOW_RECOVERABILITY_FLAG ,
1503 --RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
1504 AUTO_PRVN_FLAG ,
1505 HAS_TAX_DET_DATE_RULE_FLAG ,
1506 HAS_EXCH_RATE_DATE_RULE_FLAG ,
1507 HAS_TAX_POINT_DATE_RULE_FLAG ,
1508 USE_LEGAL_MSG_FLAG ,
1509 REGN_NUM_SAME_AS_LE_FLAG ,
1510 DEF_REC_SETTLEMENT_OPTION_CODE ,
1511 RECORD_TYPE_CODE ,
1512 ATTRIBUTE1 ,
1513 ATTRIBUTE2 ,
1514 ATTRIBUTE3 ,
1515 ATTRIBUTE4 ,
1516 ATTRIBUTE5 ,
1517 ATTRIBUTE6 ,
1518 ATTRIBUTE7 ,
1519 ATTRIBUTE8 ,
1520 ATTRIBUTE9 ,
1521 ATTRIBUTE10 ,
1522 ATTRIBUTE11 ,
1523 ATTRIBUTE12 ,
1524 ATTRIBUTE13 ,
1525 ATTRIBUTE14 ,
1526 ATTRIBUTE15 ,
1527 ATTRIBUTE_CATEGORY ,
1528 DEF_REGISTR_PARTY_TYPE_CODE ,
1529 REGISTRATION_TYPE_RULE_FLAG ,
1530 TAX_INCLUSIVE_OVERRIDE_FLAG ,
1531 CROSS_REGIME_COMPOUNDING_FLAG ,
1532 TAX_REGIME_ID ,
1533 GEOGRAPHY_ID ,
1534 THRSHLD_CHK_TMPLT_CODE ,
1535 PERIOD_SET_NAME ,
1536 REP_TAX_AUTHORITY_ID ,
1537 COLL_TAX_AUTHORITY_ID ,
1538 CREATED_BY ,
1539 CREATION_DATE ,
1540 LAST_UPDATED_BY ,
1541 LAST_UPDATE_DATE ,
1542 LAST_UPDATE_LOGIN ,
1543 REQUEST_ID ,
1544 PROGRAM_APPLICATION_ID ,
1545 PROGRAM_ID ,
1546 PROGRAM_LOGIN_ID ,
1547 OBJECT_VERSION_NUMBER
1548 )
1549 SELECT
1550 L_TAX_REGIME_CODE ,
1551 NULL ,--PARENT_REGIME_CODE
1552 L_REGIME_PRECEDENCE ,--REGIME_ PRECEDENCE
1553 'N' ,--HAS_SUB_REGIME_FLAG
1554 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
1555 L_COUNTRY_CODE ,--COUNTRY_CODE
1556 NULL ,--GEOGRAPHY_TYPE
1557 l_min_start_date ,--EFFECTIVE_FROM
1558 NULL ,--EFFECTIVE_TO
1559 NULL ,--EXCHANGE_RATE_TYPE
1560 NULL ,--TAX_CURRENCY_CODE
1561 NULL ,--THRSHLD_GROUPING_LVL_CODE
1562 NULL ,--ROUNDING_RULE_CODE
1563 NULL ,--TAX_PRECISION
1564 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
1565 'N' ,--TAX_STATUS_RULE_FLAG
1566 'SHIP_FROM' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
1567 'N' ,--APPLICABILITY_RULE_FLAG
1568 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
1569 'N' ,--TAX_CALC_RULE_FLAG
1570 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
1571 'N' ,--TAX_RATE_THRSHLD_FLAG
1572 'N' ,--TAX_AMT_THRSHLD_FLAG
1573 'N' ,--TAX_RATE_RULE_FLAG
1574 'N' ,--TAXABLE_BASIS_RULE_FLAG
1575 'N' ,--DEF_INCLUSIVE_TAX_FLAG
1576 'N' ,--HAS_OTHER_JURISDICTIONS_FLAG
1577 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
1578 'N' ,--ALLOW_EXEMPTIONS_FLAG Bug 4204464 Bug 5204559
1579 'N' ,--ALLOW_EXCEPTIONS_FLAG Bug 4204464 Bug 5204559
1580 'N' ,--ALLOW_RECOVERABILITY_FLAG
1581 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
1582 'N' ,--AUTO_PRVN_FLAG
1583 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
1584 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
1585 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
1586 'N' ,--USE_LEGAL_MSG_FLAG
1587 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
1588 NULL ,--DEF_REC_SETTLE_OPTION_CODE
1589 'MIGRATED' ,--RECORD_TYPE_CODE
1590 NULL ,
1591 NULL ,
1592 NULL ,
1593 NULL ,
1594 NULL ,
1595 NULL ,
1596 NULL ,
1597 NULL ,
1598 NULL ,
1599 NULL ,
1600 NULL ,
1601 NULL ,
1602 NULL ,
1603 NULL ,
1604 NULL ,
1605 NULL ,
1606 'SHIP_FROM_PARTY' ,--DEF_REGISTR_PARTY_TYPE_CODE
1607 'N' ,--REGISTRATION_TYPE_RULE_FLAG
1608 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG
1609 DECODE(L_REGIME_PRECEDENCE,NULL,'N','Y') ,--CROSS_REGIME_COMPOUNDING_FLAG
1610 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
1611 NULL ,--GEOGRAPHY_ID
1612 NULL ,--THRSHLD_CHK_TMPLT_CODE
1613 NULL ,--PERIOD_SET_NAME
1614 NULL ,--REP_TAX_AUTHORITY_ID
1615 NULL ,--COLL_TAX_AUTHORITY_ID
1616 fnd_global.user_id ,
1617 SYSDATE ,
1618 fnd_global.user_id ,
1619 SYSDATE ,
1620 fnd_global.conc_login_id ,
1621 fnd_global.conc_request_id ,--Request Id
1622 fnd_global.prog_appl_id ,--Program Application ID
1623 fnd_global.conc_program_id ,--Program Id
1624 fnd_global.conc_login_id ,--Program Login ID
1625 1
1626 FROM
1627 (
1628 SELECT DISTINCT
1629 zucr.tax_regime_code l_tax_regime_code,
1630 zucr.country_code l_country_code,
1631 zucr.regime_precedence l_regime_precedence
1632 FROM zx_update_criteria_results zucr
1633 WHERE NOT EXISTS (SELECT 1
1634 FROM zx_regimes_b zrb
1635 WHERE zrb.tax_regime_code = zucr.tax_regime_code
1636 )
1637
1638 );
1639
1640
1641 IF zx_migrate_util.is_installed('AP') = 'Y' THEN
1642 /*------------------------------------------------------------------------------------
1643 |
1644 | For Brazilian Regimes : BR-IPI when BR-ICMS Regime exists
1645 |
1646 | YK:02/15/2005: The following sql in P2P tax def migration code is splitted into
1647 | two to avoid dynamic sql call to fetch sequence.
1648 |
1649 +-------------------------------------------------------------------------------------*/
1650 INSERT INTO
1651 ZX_REGIMES_B
1652 (
1653 TAX_REGIME_CODE ,
1654 PARENT_REGIME_CODE ,
1655 HAS_SUB_REGIME_FLAG ,
1656 COUNTRY_OR_GROUP_CODE ,
1657 COUNTRY_CODE ,
1658 GEOGRAPHY_TYPE ,
1659 EFFECTIVE_FROM ,
1660 EFFECTIVE_TO ,
1661 EXCHANGE_RATE_TYPE ,
1662 TAX_CURRENCY_CODE ,
1663 THRSHLD_GROUPING_LVL_CODE ,
1664 ROUNDING_RULE_CODE ,
1665 TAX_PRECISION ,
1666 MINIMUM_ACCOUNTABLE_UNIT ,
1667 TAX_STATUS_RULE_FLAG ,
1668 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
1669 APPLICABILITY_RULE_FLAG ,
1670 PLACE_OF_SUPPLY_RULE_FLAG ,
1671 TAX_CALC_RULE_FLAG ,
1672 TAXABLE_BASIS_THRSHLD_FLAG ,
1673 TAX_RATE_THRSHLD_FLAG ,
1674 TAX_AMT_THRSHLD_FLAG ,
1675 TAX_RATE_RULE_FLAG ,
1676 TAXABLE_BASIS_RULE_FLAG ,
1677 DEF_INCLUSIVE_TAX_FLAG ,
1678 HAS_OTHER_JURISDICTIONS_FLAG ,
1679 ALLOW_ROUNDING_OVERRIDE_FLAG ,
1680 ALLOW_EXEMPTIONS_FLAG ,
1681 ALLOW_EXCEPTIONS_FLAG ,
1682 ALLOW_RECOVERABILITY_FLAG ,
1683 -- RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
1684 AUTO_PRVN_FLAG ,
1685 HAS_TAX_DET_DATE_RULE_FLAG ,
1686 HAS_EXCH_RATE_DATE_RULE_FLAG ,
1687 HAS_TAX_POINT_DATE_RULE_FLAG ,
1688 USE_LEGAL_MSG_FLAG ,
1689 REGN_NUM_SAME_AS_LE_FLAG ,
1690 DEF_REC_SETTLEMENT_OPTION_CODE ,
1691 RECORD_TYPE_CODE ,
1692 ATTRIBUTE1 ,
1693 ATTRIBUTE2 ,
1694 ATTRIBUTE3 ,
1695 ATTRIBUTE4 ,
1696 ATTRIBUTE5 ,
1697 ATTRIBUTE6 ,
1698 ATTRIBUTE7 ,
1699 ATTRIBUTE8 ,
1700 ATTRIBUTE9 ,
1701 ATTRIBUTE10 ,
1702 ATTRIBUTE11 ,
1703 ATTRIBUTE12 ,
1704 ATTRIBUTE13 ,
1705 ATTRIBUTE14 ,
1706 ATTRIBUTE15 ,
1707 ATTRIBUTE_CATEGORY ,
1708 DEF_REGISTR_PARTY_TYPE_CODE ,
1709 REGISTRATION_TYPE_RULE_FLAG ,
1710 TAX_INCLUSIVE_OVERRIDE_FLAG ,
1711 REGIME_PRECEDENCE ,
1712 CROSS_REGIME_COMPOUNDING_FLAG ,
1713 TAX_REGIME_ID ,
1714 GEOGRAPHY_ID ,
1715 THRSHLD_CHK_TMPLT_CODE ,
1716 PERIOD_SET_NAME ,
1717 REP_TAX_AUTHORITY_ID ,
1718 COLL_TAX_AUTHORITY_ID ,
1719 CREATED_BY ,
1720 CREATION_DATE ,
1721 LAST_UPDATED_BY ,
1722 LAST_UPDATE_DATE ,
1723 LAST_UPDATE_LOGIN ,
1724 REQUEST_ID ,
1725 PROGRAM_APPLICATION_ID ,
1726 PROGRAM_ID ,
1727 PROGRAM_LOGIN_ID ,
1728 OBJECT_VERSION_NUMBER
1729 )
1730 SELECT
1731 'BR-IPI' ,--TAX_REGIME_CODE
1732 NULL ,--PARENT_REGIME_CODE
1733 'N' ,--HAS_SUB_REGIME_FLAG
1734 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
1735 'BR' ,--COUNTRY_CODE
1736 NULL ,--GEOGRAPHY_TYPE
1737 l_min_start_date ,--EFFECTIVE_FROM
1738 NULL ,--EFFECTIVE_TO
1739 NULL ,--EXCHANGE_RATE_TYPE
1740 NULL ,--TAX_CURRENCY_CODE
1741 NULL ,--THRSHLD_GROUPING_LVL_CODE
1742 NULL ,--ROUNDING_RULE_CODE
1743 NULL ,--TAX_PRECISION
1744 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
1745 'N' ,--TAX_STATUS_RULE_FLAG
1746 'SHIP_FROM' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
1747 'N' ,--APPLICABILITY_RULE_FLAG
1748 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
1749 'N' ,--TAX_CALC_RULE_FLAG
1750 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
1751 'N' ,--TAX_RATE_THRSHLD_FLAG
1752 'N' ,--TAX_AMT_THRSHLD_FLAG
1753 'N' ,--TAX_RATE_RULE_FLAG
1754 'N' ,--TAXABLE_BASIS_RULE_FLAG
1755 'N' ,--DEF_INCLUSIVE_TAX_FLAG
1756 'N' ,--HAS_OTHER_JURISDICTIONS_FLAG
1757 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
1758 'Y' ,--ALLOW_EXEMPTIONS_FLAG
1759 'Y' ,--ALLOW_EXCEPTIONS_FLAG
1760 'N' ,--ALLOW_RECOVERABILITY_FLAG
1761 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
1762 'N' ,--AUTO_PRVN_FLAG
1763 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
1764 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
1765 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
1766 'N' ,--USE_LEGAL_MSG_FLAG
1767 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
1768 NULL ,--DEF_REC_SETTLEMENT_OPTION_CODE
1769 'MIGRATED' ,--RECORD_TYPE_CODE
1770 NULL ,
1771 NULL ,
1772 NULL ,
1773 NULL ,
1774 NULL ,
1775 NULL ,
1776 NULL ,
1777 NULL ,
1778 NULL ,
1779 NULL ,
1780 NULL ,
1781 NULL ,
1782 NULL ,
1783 NULL ,
1784 NULL ,
1785 NULL ,
1786 'SHIP_FROM_PARTY' ,--DEF_REGISTR_PARTY_TYPE_CODE
1787 'N' ,--REGISTRATION_TYPE_RULE_FLAG
1788 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG
1789 NULL ,--REGIME_PRECEDENCE
1790 'N' ,--CROSS_REGIME_COMPOUNDING_FLAG
1791 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
1792 NULL ,--GEOGRAPHY_ID
1793 NULL ,--THRSHLD_CHK_TMPLT_CODE
1794 NULL ,--PERIOD_SET_NAME
1795 NULL ,--REP_TAX_AUTHORITY_ID
1796 NULL ,--COLL_TAX_AUTHORITY_ID
1797 fnd_global.user_id ,
1798 SYSDATE ,
1799 fnd_global.user_id ,
1800 SYSDATE ,
1801 fnd_global.conc_login_id ,
1802 fnd_global.conc_request_id ,--Request Id
1803 fnd_global.prog_appl_id ,--Program Application ID
1804 fnd_global.conc_program_id ,--Program Id
1805 fnd_global.conc_login_id ,--Program Login ID
1806 1
1807 FROM zx_regimes_b
1808 WHERE tax_regime_code = 'BR-ICMS'
1809 AND country_code = 'BR'
1810 AND NOT EXISTS (SELECT 1
1811 FROM zx_regimes_b
1812 WHERE tax_regime_code = 'BR-IPI');
1813
1814 /*--------------------------------------------------------------------------
1815 |
1816 | For Brazilian Regimes : BR-ISS when BR-IPI Regime exists
1817 |
1818 | YK:02/15/2005: The following sql in P2P tax def migration code is splitted into
1819 | two to avoid dynamic sql call to fetch sequence.
1820 |
1821 +---------------------------------------------------------------------------*/
1822 INSERT INTO
1823 ZX_REGIMES_B
1824 (
1825 TAX_REGIME_CODE ,
1826 PARENT_REGIME_CODE ,
1827 HAS_SUB_REGIME_FLAG ,
1828 COUNTRY_OR_GROUP_CODE ,
1829 COUNTRY_CODE ,
1830 GEOGRAPHY_TYPE ,
1831 EFFECTIVE_FROM ,
1832 EFFECTIVE_TO ,
1833 EXCHANGE_RATE_TYPE ,
1834 TAX_CURRENCY_CODE ,
1835 THRSHLD_GROUPING_LVL_CODE ,
1836 ROUNDING_RULE_CODE ,
1837 TAX_PRECISION ,
1838 MINIMUM_ACCOUNTABLE_UNIT ,
1839 TAX_STATUS_RULE_FLAG ,
1840 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
1841 APPLICABILITY_RULE_FLAG ,
1842 PLACE_OF_SUPPLY_RULE_FLAG ,
1843 TAX_CALC_RULE_FLAG ,
1844 TAXABLE_BASIS_THRSHLD_FLAG ,
1845 TAX_RATE_THRSHLD_FLAG ,
1846 TAX_AMT_THRSHLD_FLAG ,
1847 TAX_RATE_RULE_FLAG ,
1848 TAXABLE_BASIS_RULE_FLAG ,
1849 DEF_INCLUSIVE_TAX_FLAG ,
1850 HAS_OTHER_JURISDICTIONS_FLAG ,
1851 ALLOW_ROUNDING_OVERRIDE_FLAG ,
1852 ALLOW_EXEMPTIONS_FLAG ,
1853 ALLOW_EXCEPTIONS_FLAG ,
1854 ALLOW_RECOVERABILITY_FLAG ,
1855 -- RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
1856 AUTO_PRVN_FLAG ,
1857 HAS_TAX_DET_DATE_RULE_FLAG ,
1858 HAS_EXCH_RATE_DATE_RULE_FLAG ,
1859 HAS_TAX_POINT_DATE_RULE_FLAG ,
1860 USE_LEGAL_MSG_FLAG ,
1861 REGN_NUM_SAME_AS_LE_FLAG ,
1862 DEF_REC_SETTLEMENT_OPTION_CODE ,
1863 RECORD_TYPE_CODE ,
1864 ATTRIBUTE1 ,
1865 ATTRIBUTE2 ,
1866 ATTRIBUTE3 ,
1867 ATTRIBUTE4 ,
1868 ATTRIBUTE5 ,
1869 ATTRIBUTE6 ,
1870 ATTRIBUTE7 ,
1871 ATTRIBUTE8 ,
1872 ATTRIBUTE9 ,
1873 ATTRIBUTE10 ,
1874 ATTRIBUTE11 ,
1875 ATTRIBUTE12 ,
1876 ATTRIBUTE13 ,
1877 ATTRIBUTE14 ,
1878 ATTRIBUTE15 ,
1879 ATTRIBUTE_CATEGORY ,
1880 DEF_REGISTR_PARTY_TYPE_CODE ,
1881 REGISTRATION_TYPE_RULE_FLAG ,
1882 TAX_INCLUSIVE_OVERRIDE_FLAG ,
1883 REGIME_PRECEDENCE ,
1884 CROSS_REGIME_COMPOUNDING_FLAG ,
1885 TAX_REGIME_ID ,
1886 GEOGRAPHY_ID ,
1887 THRSHLD_CHK_TMPLT_CODE ,
1888 PERIOD_SET_NAME ,
1889 REP_TAX_AUTHORITY_ID ,
1890 COLL_TAX_AUTHORITY_ID ,
1891 CREATED_BY ,
1892 CREATION_DATE ,
1893 LAST_UPDATED_BY ,
1894 LAST_UPDATE_DATE ,
1895 LAST_UPDATE_LOGIN ,
1896 REQUEST_ID ,
1897 PROGRAM_APPLICATION_ID ,
1898 PROGRAM_ID ,
1899 PROGRAM_LOGIN_ID ,
1900 OBJECT_VERSION_NUMBER
1901 )
1902 SELECT
1903 'BR-ISS' ,--TAX_REGIME_CODE
1904 NULL ,--PARENT_REGIME_CODE
1905 'N' ,--HAS_SUB_REGIME_FLAG
1906 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
1907 'BR' ,--COUNTRY_CODE
1908 NULL ,--GEOGRAPHY_TYPE
1909 l_min_start_date ,--EFFECTIVE_FROM
1910 NULL ,--EFFECTIVE_TO
1911 NULL ,--EXCHANGE_RATE_TYPE
1912 NULL ,--TAX_CURRENCY_CODE
1913 NULL ,--THRSHLD_GROUPING_LVL_CODE
1914 NULL ,--ROUNDING_RULE_CODE
1915 NULL ,--TAX_PRECISION
1916 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
1917 'N' ,--TAX_STATUS_RULE_FLAG
1918 'SHIP_FROM' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
1919 'N' ,--APPLICABILITY_RULE_FLAG
1920 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
1921 'N' ,--TAX_CALC_RULE_FLAG
1922 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
1923 'N' ,--TAX_RATE_THRSHLD_FLAG
1924 'N' ,--TAX_AMT_THRSHLD_FLAG
1925 'N' ,--TAX_RATE_RULE_FLAG
1926 'N' ,--TAXABLE_BASIS_RULE_FLAG
1927 'N' ,--DEF_INCLUSIVE_TAX_FLAG
1928 'N' ,--HAS_OTHER_JURISDICTIONS_FLAG
1929 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
1930 'Y' ,--ALLOW_EXEMPTIONS_FLAG
1931 'Y' ,--ALLOW_EXCEPTIONS_FLAG
1932 'N' ,--ALLOW_RECOVERABILITY_FLAG
1933 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
1934 'N' ,--AUTO_PRVN_FLAG
1935 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
1936 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
1937 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
1938 'N' ,--USE_LEGAL_MSG_FLAG
1939 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
1940 NULL ,--DEF_REC_SETTLEMENT_OPTION_CODE
1941 'MIGRATED' ,--RECORD_TYPE_CODE
1942 NULL ,
1943 NULL ,
1944 NULL ,
1945 NULL ,
1946 NULL ,
1947 NULL ,
1948 NULL ,
1949 NULL ,
1950 NULL ,
1951 NULL ,
1952 NULL ,
1953 NULL ,
1954 NULL ,
1955 NULL ,
1956 NULL ,
1957 NULL ,
1958 'SHIP_FROM_PARTY' ,--DEF_REGISTR_PARTY_TYPE_CODE
1959 'N' ,--REGISTRATION_TYPE_RULE_FLAG
1960 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG
1961 NULL ,--REGIME_PRECEDENCE
1962 'N' ,--CROSS_REGIME_COMPOUNDING_FLAG
1963 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
1964 NULL ,--GEOGRAPHY_ID
1965 NULL ,--THRSHLD_CHK_TMPLT_CODE
1966 NULL ,--PERIOD_SET_NAME
1967 NULL ,--REP_TAX_AUTHORITY_ID
1968 NULL ,--COLL_TAX_AUTHORITY_ID
1969 fnd_global.user_id ,
1970 SYSDATE ,
1971 fnd_global.user_id ,
1972 SYSDATE ,
1973 fnd_global.conc_login_id ,
1974 fnd_global.conc_request_id ,--Request Id
1975 fnd_global.prog_appl_id ,--Program Application ID
1976 fnd_global.conc_program_id ,--Program Id
1977 fnd_global.conc_login_id ,--Program Login ID
1978 1
1979 FROM zx_regimes_b
1980 WHERE tax_regime_code = 'BR-IPI'
1981 AND country_code = 'BR'
1982 AND NOT EXISTS (SELECT 1
1983 FROM zx_regimes_b
1984 WHERE tax_regime_code = 'BR-ISS');
1985
1986 END IF;
1987
1988 IF zx_migrate_util.is_installed('AR') = 'Y' THEN
1989 /*-------------------------------------------------------------------------
1990 |
1991 | For GTE US Sales Tax Regimes
1992 | It also inserts zx_regimes_tl.
1993 |
1994 | Regime Code :
1995 | 1. Country Code || '-SALES-TAX-' || location structure id
1996 |
1997 | Regime Name :
1998 | 1. Country Code || '-SALES-TAX-' || Qualifier1 ||'-'|| Qualifier2..
1999 |
2000 +--------------------------------------------------------------------------*/
2001 -- ****** Building PL/SQL Table ******
2002 i := 1;
2003 d := 1;
2004 FOR loc_str_cur_rec IN loc_str_cur LOOP
2005 IF loc_str_cur%ROWCOUNT = 1 THEN
2006 loc_str_rec.country_code := loc_str_cur_rec.default_country;
2007 loc_str_rec.id_flex_num := loc_str_cur_rec.id_flex_num;
2008 loc_str_rec.seg_att_type1 := loc_str_cur_rec.seg_att_type;
2009 loc_str_rec.tax_currency_code := loc_str_cur_rec.tax_currency_code;
2010 loc_str_rec.tax_precision := loc_str_cur_rec.tax_precision;
2011 loc_str_rec.tax_mau := loc_str_cur_rec.tax_mau;
2012 loc_str_rec.rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
2013 loc_str_rec.allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
2014 loc_str_rec.org_id := loc_str_cur_rec.org_id;
2015
2016 l_temp_id_flex_num := loc_str_cur_rec.id_flex_num;
2017 l_temp_country_code := loc_str_cur_rec.default_country;
2018 l_temp_org_id := loc_str_cur_rec.org_id;
2022 l_temp_tax_precision := loc_str_cur_rec.tax_precision;
2019 l_temp_seg_num := loc_str_cur_rec.seg_num;
2020 l_temp_seg_att_type := loc_str_cur_rec.seg_att_type;
2021 l_temp_tax_currency_code := loc_str_cur_rec.tax_currency_code;
2023 l_temp_tax_mau := loc_str_cur_rec.tax_mau;
2024 l_temp_rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
2025 l_temp_allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
2026 l_temp_org_id := loc_str_cur_rec.org_id;
2027
2028 cnt := 1; --Counter for seg_att_type
2029 ELSE
2030 IF l_temp_id_flex_num = loc_str_cur_rec.id_flex_num AND
2031 l_temp_country_code = loc_str_cur_rec.default_country THEN
2032 IF l_temp_seg_num <> loc_str_cur_rec.seg_num THEN
2033 cnt := cnt + 1;
2034 IF cnt = 2 THEN
2035 loc_str_rec.seg_att_type2 := loc_str_cur_rec.seg_att_type;
2036 ELSIF cnt = 3 THEN
2037 loc_str_rec.seg_att_type3 := loc_str_cur_rec.seg_att_type;
2038 ELSIF cnt = 4 THEN
2039 loc_str_rec.seg_att_type4 := loc_str_cur_rec.seg_att_type;
2040 ELSIF cnt = 5 THEN
2041 loc_str_rec.seg_att_type5 := loc_str_cur_rec.seg_att_type;
2042 ELSIF cnt = 6 THEN
2043 loc_str_rec.seg_att_type6 := loc_str_cur_rec.seg_att_type;
2044 ELSIF cnt = 7 THEN
2045 loc_str_rec.seg_att_type7 := loc_str_cur_rec.seg_att_type;
2046 ELSIF cnt = 8 THEN
2047 loc_str_rec.seg_att_type8 := loc_str_cur_rec.seg_att_type;
2048 ELSIF cnt = 9 THEN
2049 loc_str_rec.seg_att_type9 := loc_str_cur_rec.seg_att_type;
2050 ELSIF cnt = 10 THEN
2051 loc_str_rec.seg_att_type10 := loc_str_cur_rec.seg_att_type;
2052 END IF;
2053 ELSIF l_temp_seg_num = loc_str_cur_rec.seg_num THEN
2054 IF l_temp_org_id <> loc_str_cur_rec.org_id THEN
2055 -- ORGANIZATION MERGE HAPPEND --
2056 loc_str_rec := null_loc_str_rec;
2057 loc_str_rec.country_code := loc_str_cur_rec.default_country;
2058 loc_str_rec.id_flex_num := loc_str_cur_rec.id_flex_num;
2059 loc_str_rec.tax_currency_code := loc_str_cur_rec.tax_currency_code;
2060 loc_str_rec.tax_precision := loc_str_cur_rec.tax_precision;
2061 loc_str_rec.tax_mau := loc_str_cur_rec.tax_mau;
2062 loc_str_rec.rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
2063 loc_str_rec.allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
2064 loc_str_rec.org_id := loc_str_cur_rec.org_id;
2065 --loc_str_rec.tax_account_id := loc_str_cur_rec.tax_account_id;
2066 denorm_err_tbl(d) := loc_str_rec;
2067 d := d + 1;
2068 END IF;
2069 END IF;
2070 ELSE
2071 denorm_tbl(i) := loc_str_rec;
2072 loc_str_rec := null_loc_str_rec;
2073 i := i + 1;
2074
2075 loc_str_rec.country_code := loc_str_cur_rec.default_country;
2076 loc_str_rec.id_flex_num := loc_str_cur_rec.id_flex_num;
2077 loc_str_rec.seg_att_type1 := loc_str_cur_rec.seg_att_type;
2078 loc_str_rec.tax_currency_code := loc_str_cur_rec.tax_currency_code;
2079 loc_str_rec.tax_precision := loc_str_cur_rec.tax_precision;
2080 loc_str_rec.tax_mau := loc_str_cur_rec.tax_mau;
2081 loc_str_rec.rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
2082 loc_str_rec.allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
2083 loc_str_rec.org_id := loc_str_cur_rec.org_id;
2084
2085 l_temp_id_flex_num := loc_str_cur_rec.id_flex_num;
2086 l_temp_country_code := loc_str_cur_rec.default_country;
2087 l_temp_org_id := loc_str_cur_rec.org_id;
2088 l_temp_seg_num := loc_str_cur_rec.seg_num;
2089 l_temp_seg_att_type := loc_str_cur_rec.seg_att_type;
2090 l_temp_tax_currency_code := loc_str_cur_rec.tax_currency_code;
2091 l_temp_tax_precision := loc_str_cur_rec.tax_precision;
2092 l_temp_tax_mau := loc_str_cur_rec.tax_mau;
2093 l_temp_rounding_rule_code := loc_str_cur_rec.rounding_rule_code;
2094 l_temp_allow_rounding_override := loc_str_cur_rec.allow_rounding_override;
2095 l_temp_org_id := loc_str_cur_rec.org_id;
2096 cnt := 1;
2097 END IF;
2098 END IF;
2099 END LOOP;
2100 denorm_tbl(i) := loc_str_rec;
2101
2102 -- ****** DEBUG ******
2103 FOR k in 1..denorm_tbl.count LOOP
2104 arp_util_tax.debug('***');
2105 arp_util_tax.debug('Country Code: '|| denorm_tbl(k).country_code);
2106 arp_util_tax.debug('ID Flex Num : '|| denorm_tbl(k).id_flex_num);
2107 arp_util_tax.debug('Attr1 : '|| denorm_tbl(k).seg_att_type1);
2108 arp_util_tax.debug('Attr2 : '|| denorm_tbl(k).seg_att_type2);
2109 arp_util_tax.debug('Attr3 : '|| denorm_tbl(k).seg_att_type3);
2110 arp_util_tax.debug('Attr4 : '|| denorm_tbl(k).seg_att_type4);
2111 arp_util_tax.debug('Attr5 : '|| denorm_tbl(k).seg_att_type5);
2112 arp_util_tax.debug('Attr6 : '|| denorm_tbl(k).seg_att_type6);
2113 arp_util_tax.debug('Attr7 : '|| denorm_tbl(k).seg_att_type7);
2114 arp_util_tax.debug('Attr8 : '|| denorm_tbl(k).seg_att_type8);
2115 arp_util_tax.debug('Attr9 : '|| denorm_tbl(k).seg_att_type9);
2116 arp_util_tax.debug('Attr10 : '|| denorm_tbl(k).seg_att_type10);
2117 arp_util_tax.debug('Currency : '|| denorm_tbl(k).tax_currency_code);
2118 arp_util_tax.debug('Precision : '|| denorm_tbl(k).tax_precision);
2119 arp_util_tax.debug('MAU : '|| denorm_tbl(k).tax_mau);
2120 arp_util_tax.debug('Rounding : '|| denorm_tbl(k).rounding_rule_code);
2121 arp_util_tax.debug('Rounding Ovr: '|| denorm_tbl(k).allow_rounding_override);
2122 arp_util_tax.debug('Org ID : '|| denorm_tbl(k).org_id);
2123 END LOOP;
2124 arp_util_tax.debug(' ');
2125 IF denorm_err_tbl.count > 0 THEN
2126 arp_util_tax.debug('*** ORGANZATION MERGED RECORDS ***');
2127 FOR k in 1..denorm_err_tbl.count LOOP
2128 arp_util_tax.debug(denorm_err_tbl(k).country_code);
2129 arp_util_tax.debug(denorm_err_tbl(k).id_flex_num);
2130 arp_util_tax.debug(denorm_err_tbl(k).tax_currency_code);
2131 arp_util_tax.debug(denorm_err_tbl(k).tax_precision);
2132 arp_util_tax.debug(denorm_err_tbl(k).tax_mau);
2133 arp_util_tax.debug(denorm_err_tbl(k).rounding_rule_code);
2134 arp_util_tax.debug(denorm_err_tbl(k).allow_rounding_override);
2135 arp_util_tax.debug(denorm_err_tbl(k).org_id);
2136 --arp_util_tax.debug(denorm_tbl(k).tax_account_id);
2137 END LOOP;
2138 ELSE
2139 arp_util_tax.debug('*** NO ORGANZATION MERGED RECORDS ***');
2140 END IF;
2141 -- ****** DEBUG ******
2142
2143
2144
2145
2146 -- ****** Insert into zx_regimes_b/tl ******
2147 FOR k in 1..denorm_tbl.count LOOP
2148 if denorm_tbl(k).country_code is not null
2149 then
2150 l_tax_regime_name := denorm_tbl(k).country_code || '-SALES-TAX' ||
2151 '-' || denorm_tbl(k).seg_att_type1 ||
2152 '-' || denorm_tbl(k).seg_att_type2 ||
2153 '-' || denorm_tbl(k).seg_att_type3 ||
2154 '-' || denorm_tbl(k).seg_att_type4 ||
2155 '-' || denorm_tbl(k).seg_att_type5 ||
2156 '-' || denorm_tbl(k).seg_att_type6 ||
2157 '-' || denorm_tbl(k).seg_att_type7 ||
2158 '-' || denorm_tbl(k).seg_att_type8 ||
2159 '-' || denorm_tbl(k).seg_att_type9 ||
2160 '-' || denorm_tbl(k).seg_att_type10;
2161 l_tax_regime_name := RTRIM(l_tax_regime_name, '-');
2162 l_tax_regime_code := denorm_tbl(k).country_code || '-SALES-TAX-' || denorm_tbl(k).id_flex_num;
2163
2164 INSERT ALL
2165 WHEN (NOT EXISTS (SELECT 1
2166 FROM ZX_REGIMES_B
2167 WHERE TAX_REGIME_CODE = l_tax_regime_code
2168 )
2169 ) THEN
2170 INTO ZX_REGIMES_B
2171 (
2172 TAX_REGIME_CODE ,
2173 PARENT_REGIME_CODE ,
2174 HAS_SUB_REGIME_FLAG ,
2175 COUNTRY_OR_GROUP_CODE ,
2176 COUNTRY_CODE ,
2177 GEOGRAPHY_TYPE ,
2178 EFFECTIVE_FROM ,
2179 EFFECTIVE_TO ,
2180 EXCHANGE_RATE_TYPE ,
2181 TAX_CURRENCY_CODE ,
2182 THRSHLD_GROUPING_LVL_CODE ,
2183 ROUNDING_RULE_CODE ,
2184 TAX_PRECISION ,
2185 MINIMUM_ACCOUNTABLE_UNIT ,
2186 TAX_STATUS_RULE_FLAG ,
2187 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
2188 APPLICABILITY_RULE_FLAG ,
2189 PLACE_OF_SUPPLY_RULE_FLAG ,
2190 TAX_CALC_RULE_FLAG ,
2191 TAXABLE_BASIS_THRSHLD_FLAG ,
2192 TAX_RATE_THRSHLD_FLAG ,
2193 TAX_AMT_THRSHLD_FLAG ,
2194 TAX_RATE_RULE_FLAG ,
2195 TAXABLE_BASIS_RULE_FLAG ,
2196 DEF_INCLUSIVE_TAX_FLAG ,
2197 HAS_OTHER_JURISDICTIONS_FLAG ,
2198 ALLOW_ROUNDING_OVERRIDE_FLAG ,
2199 ALLOW_EXEMPTIONS_FLAG ,
2200 ALLOW_EXCEPTIONS_FLAG ,
2201 ALLOW_RECOVERABILITY_FLAG ,
2202 -- RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
2203 AUTO_PRVN_FLAG ,
2204 HAS_TAX_DET_DATE_RULE_FLAG ,
2205 HAS_EXCH_RATE_DATE_RULE_FLAG ,
2206 HAS_TAX_POINT_DATE_RULE_FLAG ,
2207 USE_LEGAL_MSG_FLAG ,
2208 REGN_NUM_SAME_AS_LE_FLAG ,
2209 DEF_REC_SETTLEMENT_OPTION_CODE ,
2210 RECORD_TYPE_CODE ,
2211 ATTRIBUTE1 ,
2212 ATTRIBUTE2 ,
2213 ATTRIBUTE3 ,
2214 ATTRIBUTE4 ,
2215 ATTRIBUTE5 ,
2216 ATTRIBUTE6 ,
2217 ATTRIBUTE7 ,
2218 ATTRIBUTE8 ,
2219 ATTRIBUTE9 ,
2220 ATTRIBUTE10 ,
2221 ATTRIBUTE11 ,
2222 ATTRIBUTE12 ,
2223 ATTRIBUTE13 ,
2224 ATTRIBUTE14 ,
2225 ATTRIBUTE15 ,
2226 ATTRIBUTE_CATEGORY ,
2227 DEF_REGISTR_PARTY_TYPE_CODE ,
2228 REGISTRATION_TYPE_RULE_FLAG ,
2229 TAX_INCLUSIVE_OVERRIDE_FLAG ,
2230 REGIME_PRECEDENCE ,
2231 CROSS_REGIME_COMPOUNDING_FLAG ,
2232 TAX_REGIME_ID ,
2233 GEOGRAPHY_ID ,
2234 THRSHLD_CHK_TMPLT_CODE ,
2235 PERIOD_SET_NAME ,
2236 REP_TAX_AUTHORITY_ID ,
2237 COLL_TAX_AUTHORITY_ID ,
2238 CREATED_BY ,
2239 CREATION_DATE ,
2240 LAST_UPDATED_BY ,
2241 LAST_UPDATE_DATE ,
2242 LAST_UPDATE_LOGIN ,
2243 REQUEST_ID ,
2244 PROGRAM_APPLICATION_ID ,
2245 PROGRAM_ID ,
2246 PROGRAM_LOGIN_ID ,
2247 OBJECT_VERSION_NUMBER
2248 )
2249 VALUES
2250 (
2251 l_tax_regime_code , --TAX_REGIME_CODE
2252 NULL ,--PARENT_REGIME_CODE
2253 'N' ,--HAS_SUB_REGIME_FLAG
2254 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
2255 denorm_tbl(k).country_code ,--COUNTRY_CODE
2256 NULL ,--GEOGRAPHY_TYPE
2257 l_min_start_date ,--EFFECTIVE_FROM
2258 NULL ,--EFFECTIVE_TO
2259 NULL ,--EXCHANGE_RATE_TYPE
2260 NULL ,--TAX_CURRENCY_CODE ***** ATTENTION
2261 NULL ,--THRSHLD_GROUPING_LVL_CODE
2262 NULL ,--ROUNDING_RULE_CODE
2263 NULL ,--TAX_PRECISION ***** ATTENTION
2264 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
2265 'N' ,--TAX_STATUS_RULE_FLAG
2266 'SHIP_TO' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
2267 'N' ,--APPLICABILITY_RULE_FLAG
2268 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
2269 'N' ,--TAX_CALC_RULE_FLAG
2270 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
2271 'N' ,--TAX_RATE_THRSHLD_FLAG
2272 'N' ,--TAX_AMT_THRSHLD_FLAG
2273 'N' ,--TAX_RATE_RULE_FLAG
2274 'N' ,--TAXABLE_BASIS_RULE_FLAG
2275 'N' ,--DEF_INCLUSIVE_TAX_FLAG
2276 'Y' ,--HAS_OTHER_JURISDICTIONS_FLAG : 4610550
2277 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
2278 'Y' ,--ALLOW_EXEMPTIONS_FLAG
2279 'Y' ,--ALLOW_EXCEPTIONS_FLAG
2280 'N' ,--ALLOW_RECOVERABILITY_FLAG
2281 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
2282 'N' ,--AUTO_PRVN_FLAG
2283 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
2284 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
2285 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
2286 'N' ,--USE_LEGAL_MSG_FLAG
2287 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
2288 'N' ,--DEF_REC_SETTLE_OPTION_CODE
2289 'MIGRATED' ,--RECORD_TYPE_CODE
2290 NULL ,
2291 NULL ,
2292 NULL ,
2293 NULL ,
2294 NULL ,
2295 NULL ,
2296 NULL ,
2297 NULL ,
2298 NULL ,
2299 NULL ,
2300 NULL ,
2301 NULL ,
2302 NULL ,
2303 NULL ,
2304 NULL ,
2305 NULL ,
2306 'SHIP_TO_SITE' ,--DEF_REGISTR_PARTY_TYPE_CODE
2307 'N' ,--REGISTRATION_TYPE_RULE_FLAG
2308 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG /** Set it to Y. Need P2P Change. **/
2309 NULL ,--REGIME_PRECEDENCE /** Can be updated for compounding migration **/
2310 'N' ,--CROSS_REGIME_COMPOUNDING_FLAG
2311 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
2312 NULL ,--GEOGRAPHY_ID
2313 NULL ,--THRSHLD_CHK_TMPLT_CODE
2314 NULL ,--PERIOD_SET_NAME
2315 NULL ,--REP_TAX_AUTHORITY_ID
2316 NULL ,--COLL_TAX_AUTHORITY_ID
2317 fnd_global.user_id ,
2318 SYSDATE ,
2319 fnd_global.user_id ,
2320 SYSDATE ,
2321 fnd_global.conc_login_id ,
2322 fnd_global.conc_request_id ,--Request Id
2323 fnd_global.prog_appl_id ,--Program Application ID
2324 fnd_global.conc_program_id ,--Program Id
2325 fnd_global.conc_login_id ,--Program Login ID
2326 1
2327 )
2328 WHEN (NOT EXISTS (SELECT 1
2329 FROM ZX_REGIMES_B
2330 WHERE TAX_REGIME_CODE = l_tax_regime_code
2331 )
2332 ) THEN
2333 -- Need to insert _TL table for current language as l_tax_regime_name is
2334 -- derived using the following logic :
2335 -- Country Code '-SALES-TAX-' Seg Att1 '-' Seg Att2 '-' ...
2336 INTO ZX_REGIMES_TL
2337 (
2338 LANGUAGE ,
2339 SOURCE_LANG ,
2340 TAX_REGIME_NAME ,
2341 CREATION_DATE ,
2342 CREATED_BY ,
2343 LAST_UPDATE_DATE ,
2344 LAST_UPDATED_BY ,
2345 LAST_UPDATE_LOGIN ,
2346 TAX_REGIME_ID
2347 )
2348 VALUES
2349 (
2350 userenv('LANG'),
2351 userenv('LANG'),
2352 CASE WHEN l_tax_regime_name = UPPER(l_tax_regime_name)
2353 THEN Initcap(l_tax_regime_name)
2354 ELSE
2355 l_tax_regime_name
2356 END,
2357 SYSDATE,
2358 fnd_global.user_id ,
2359 SYSDATE ,
2360 fnd_global.user_id ,
2361 fnd_global.conc_login_id ,
2362 ZX_REGIMES_B_S.NEXTVAL
2363 )
2364 SELECT 1 FROM DUAL;
2365 END IF;
2366 END LOOP;
2367
2368
2369 /*-------------------------------------------------------------------------
2370 |
2371 | For Tax Vendor Regimes
2372 |
2373 | Regime Code :
2374 | 1. 'US-SALES-TAX-TAXWARE' if TAXWARE is installed in one of the OUs.
2375 | 2. 'US-SALES-TAX-VERTEX' if VERTEX is installed in one of the OUs.
2376 |
2377 +--------------------------------------------------------------------------*/
2378 /*
2379 INSERT ALL
2380 INTO zx_regimes_b
2381 (
2382 TAX_REGIME_CODE ,
2383 PARENT_REGIME_CODE ,
2384 HAS_SUB_REGIME_FLAG ,
2385 COUNTRY_OR_GROUP_CODE ,
2386 COUNTRY_CODE ,
2387 GEOGRAPHY_TYPE ,
2388 EFFECTIVE_FROM ,
2389 EFFECTIVE_TO ,
2390 EXCHANGE_RATE_TYPE ,
2391 TAX_CURRENCY_CODE ,
2392 THRSHLD_GROUPING_LVL_CODE ,
2393 ROUNDING_RULE_CODE ,
2394 TAX_PRECISION ,
2395 MINIMUM_ACCOUNTABLE_UNIT ,
2396 TAX_STATUS_RULE_FLAG ,
2397 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
2398 APPLICABILITY_RULE_FLAG ,
2399 PLACE_OF_SUPPLY_RULE_FLAG ,
2400 TAX_CALC_RULE_FLAG ,
2401 TAXABLE_BASIS_THRSHLD_FLAG ,
2402 TAX_RATE_THRSHLD_FLAG ,
2403 TAX_AMT_THRSHLD_FLAG ,
2404 TAX_RATE_RULE_FLAG ,
2405 TAXABLE_BASIS_RULE_FLAG ,
2406 DEF_INCLUSIVE_TAX_FLAG ,
2407 HAS_OTHER_JURISDICTIONS_FLAG ,
2408 ALLOW_ROUNDING_OVERRIDE_FLAG ,
2409 ALLOW_EXEMPTIONS_FLAG ,
2410 ALLOW_EXCEPTIONS_FLAG ,
2411 ALLOW_RECOVERABILITY_FLAG ,
2412 -- RECOVERABILITY_OVERRIDE_FLAG , Bug 3766372
2413 AUTO_PRVN_FLAG ,
2414 HAS_TAX_DET_DATE_RULE_FLAG ,
2415 HAS_EXCH_RATE_DATE_RULE_FLAG ,
2416 HAS_TAX_POINT_DATE_RULE_FLAG ,
2417 USE_LEGAL_MSG_FLAG ,
2418 REGN_NUM_SAME_AS_LE_FLAG ,
2419 DEF_REC_SETTLEMENT_OPTION_CODE ,
2420 RECORD_TYPE_CODE ,
2421 ATTRIBUTE1 ,
2422 ATTRIBUTE2 ,
2423 ATTRIBUTE3 ,
2424 ATTRIBUTE4 ,
2425 ATTRIBUTE5 ,
2426 ATTRIBUTE6 ,
2427 ATTRIBUTE7 ,
2428 ATTRIBUTE8 ,
2429 ATTRIBUTE9 ,
2430 ATTRIBUTE10 ,
2431 ATTRIBUTE11 ,
2432 ATTRIBUTE12 ,
2433 ATTRIBUTE13 ,
2434 ATTRIBUTE14 ,
2435 ATTRIBUTE15 ,
2436 ATTRIBUTE_CATEGORY ,
2437 DEF_REGISTR_PARTY_TYPE_CODE ,
2438 REGISTRATION_TYPE_RULE_FLAG ,
2439 TAX_INCLUSIVE_OVERRIDE_FLAG ,
2440 REGIME_PRECEDENCE ,
2441 CROSS_REGIME_COMPOUNDING_FLAG ,
2442 TAX_REGIME_ID ,
2443 GEOGRAPHY_ID ,
2444 THRSHLD_CHK_TMPLT_CODE ,
2445 PERIOD_SET_NAME ,
2446 REP_TAX_AUTHORITY_ID ,
2447 COLL_TAX_AUTHORITY_ID ,
2448 CREATED_BY ,
2449 CREATION_DATE ,
2450 LAST_UPDATED_BY ,
2451 LAST_UPDATE_DATE ,
2452 LAST_UPDATE_LOGIN ,
2453 REQUEST_ID ,
2454 PROGRAM_APPLICATION_ID ,
2455 PROGRAM_ID ,
2456 PROGRAM_LOGIN_ID ,
2457 OBJECT_VERSION_NUMBER
2458 )
2459 VALUES
2460 (
2461 l_tax_regime_code , --TAX_REGIME_CODE
2462 NULL ,--PARENT_REGIME_CODE
2463 'N' ,--HAS_SUB_REGIME_FLAG
2464 'COUNTRY' ,--COUNTRY_OR_GROUP_CODE
2465 'US' ,--COUNTRY_CODE
2466 NULL ,--GEOGRAPHY_TYPE
2467 l_min_start_date ,--EFFECTIVE_FROM
2468 NULL ,--EFFECTIVE_TO
2469 NULL ,--EXCHANGE_RATE_TYPE
2470 NULL ,--TAX_CURRENCY_CODE ***** ATTENTION
2471 NULL ,--THRSHLD_GROUPING_LVL_CODE
2472 NULL ,--ROUNDING_RULE_CODE
2473 NULL ,--TAX_PRECISION ***** ATTENTION
2474 NULL ,--MINIMUM_ACCOUNTABLE_UNIT
2475 'N' ,--TAX_STATUS_RULE_FLAG
2476 'SHIP_TO' ,--DEF_PLACE_OF_SUPPLY_TYPE_CODE
2477 'N' ,--APPLICABILITY_RULE_FLAG
2478 'N' ,--PLACE_OF_SUPPLY_RULE_FLAG
2479 'N' ,--TAX_CALC_RULE_FLAG
2480 'N' ,--TAXABLE_BASIS_THRSHLD_FLAG
2481 'N' ,--TAX_RATE_THRSHLD_FLAG
2482 'N' ,--TAX_AMT_THRSHLD_FLAG
2483 'N' ,--TAX_RATE_RULE_FLAG
2484 'N' ,--TAXABLE_BASIS_RULE_FLAG
2485 'N' ,--DEF_INCLUSIVE_TAX_FLAG
2486 'N' ,--HAS_OTHER_JURISDICTIONS_FLAG
2487 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
2488 'Y' ,--ALLOW_EXEMPTIONS_FLAG
2489 'Y' ,--ALLOW_EXCEPTIONS_FLAG
2490 'N' ,--ALLOW_RECOVERABILITY_FLAG
2491 -- 'N' ,--RECOVERABILITY_OVERRIDE_FLAG : Bug 3766372
2492 'N' ,--AUTO_PRVN_FLAG
2493 'N' ,--HAS_TAX_DET_DATE_RULE_FLAG
2494 'N' ,--HAS_EXCH_RATE_DATE_RULE_FLAG
2495 'N' ,--HAS_TAX_POINT_DATE_RULE_FLAG
2496 'N' ,--USE_LEGAL_MSG_FLAG
2497 'N' ,--REGN_NUM_SAME_AS_LE_FLAG
2498 'N' ,--DEF_REC_SETTLE_OPTION_CODE
2499 'MIGRATED' ,--RECORD_TYPE_CODE
2500 NULL ,
2501 NULL ,
2502 NULL ,
2503 NULL ,
2504 NULL ,
2505 NULL ,
2506 NULL ,
2507 NULL ,
2508 NULL ,
2509 NULL ,
2510 NULL ,
2511 NULL ,
2512 NULL ,
2513 NULL ,
2514 NULL ,
2515 NULL ,
2516 'SHIP_TO_SITE' ,--DEF_REGISTR_PARTY_TYPE_CODE
2517 'N' ,--REGISTRATION_TYPE_RULE_FLAG
2518 'Y' ,--TAX_INCLUSIVE_OVERRIDE_FLAG
2519 NULL ,--REGIME_PRECEDENCE
2520 'N' ,--CROSS_REGIME_COMPOUNDING_FLAG
2521 ZX_REGIMES_B_S.NEXTVAL ,--TAX_REGIME_ID
2522 NULL ,--GEOGRAPHY_ID
2523 NULL ,--THRSHLD_CHK_TMPLT_CODE
2524 NULL ,--PERIOD_SET_NAME
2525 NULL ,--REP_TAX_AUTHORITY_ID
2526 NULL ,--COLL_TAX_AUTHORITY_ID
2527 fnd_global.user_id ,
2528 SYSDATE ,
2529 fnd_global.user_id ,
2530 SYSDATE ,
2531 fnd_global.conc_login_id ,
2532 fnd_global.conc_request_id ,--Request Id
2533 fnd_global.prog_appl_id ,--Program Application ID
2534 fnd_global.conc_program_id ,--Program Id
2535 fnd_global.conc_login_id ,--Program Login ID
2536 1
2537 )
2538 SELECT distinct
2539 CASE
2540 WHEN asp.tax_database_view_set = '_A' THEN
2541 'US-SALES-TAX-TAXWARE'
2542 WHEN asp.tax_database_view_set = '_V' THEN
2543 'US-SALES-TAX-VERTEX'
2544 END l_tax_regime_code
2545 FROM ar_system_parameters_all asp
2546 WHERE asp.tax_database_view_set IN ('_A', '_V')
2547 AND asp.default_country = 'US'
2548 AND NOT EXISTS (SELECT 1
2549 FROM zx_regimes_b
2550 WHERE tax_regime_code IN ('US-SALES-TAX-TAXWARE', 'US-SALES-TAX-VERTEX')
2551 );
2552 */
2553
2554 END IF;
2555
2556
2557 /*-------------------------------------------------------------------------
2558 |
2559 | Populates data into zx_regimes_tl table
2560 |
2561 +--------------------------------------------------------------------------*/
2562 -- Bug 4688151 : LTE Tax Codes will derive tax_regime_name from
2563 IF L_MULTI_ORG_FLAG = 'Y'
2564 THEN
2565
2566 INSERT INTO ZX_REGIMES_TL
2567 (
2568 LANGUAGE ,
2569 SOURCE_LANG ,
2570 TAX_REGIME_NAME ,
2571 CREATION_DATE ,
2572 CREATED_BY ,
2573 LAST_UPDATE_DATE ,
2574 LAST_UPDATED_BY ,
2575 LAST_UPDATE_LOGIN ,
2576 TAX_REGIME_ID
2577
2578 )
2579 SELECT
2580 L.LANGUAGE_CODE ,
2581 userenv('LANG') ,
2582 CASE WHEN decode(d.global_attribute_category,
2583 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2584 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2585 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2586 B.TAX_REGIME_CODE)
2587 =
2588 UPPER(decode(d.global_attribute_category,
2589 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2590 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2591 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2592 B.TAX_REGIME_CODE))
2593 THEN
2594 Initcap(decode(d.global_attribute_category,
2595 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2596 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2597 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2598 B.TAX_REGIME_CODE))
2599 ELSE
2600 decode(d.global_attribute_category,
2601 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2602 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2603 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2604 B.TAX_REGIME_CODE)
2605 END ,
2606 SYSDATE ,
2607 fnd_global.user_id ,
2608 SYSDATE ,
2609 fnd_global.user_id ,
2610 fnd_global.conc_login_id ,
2614 ZX_REGIMES_B B,
2611 B.TAX_REGIME_ID
2612 FROM
2613 FND_LANGUAGES L,
2615 (select rates.tax_regime_code tax_regime_code,
2616 lkups.meaning meaning,
2617 params.global_attribute_category global_attribute_category
2618 from zx_rates_b rates,
2619 ar_vat_tax_all_b codes,
2620 ar_system_parameters_all params,
2621 fnd_lookups lkups
2622 where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
2623 AND codes.org_id = params.org_id
2624 and params.global_attribute13 = lkups.lookup_code
2625 and params.global_attribute_category in ('JL.AR.ARXSYSPA.SYS_PARAMETERS',
2626 'JL.BR.ARXSYSPA.Additional Info',
2627 'JL.CO.ARXSYSPA.SYS_PARAMETERS')
2628 and lkups.lookup_type = 'JLZZ_AR_TX_RULE_SET'
2629 group by rates.tax_regime_code,
2630 lkups.meaning,
2631 params.global_attribute_category
2632 ) D
2633 WHERE
2634 L.INSTALLED_FLAG in ('I', 'B')
2635 AND B.RECORD_TYPE_CODE = 'MIGRATED'
2636 --
2637 AND b.tax_regime_code = d.tax_regime_code (+)
2638 AND not exists
2639 (select NULL
2640 from ZX_REGIMES_TL T
2641 where T.TAX_REGIME_ID = B.TAX_REGIME_ID
2642 and T.LANGUAGE = L.LANGUAGE_CODE);
2643 ELSE
2644
2645 INSERT INTO ZX_REGIMES_TL
2646 (
2647 LANGUAGE ,
2648 SOURCE_LANG ,
2649 TAX_REGIME_NAME ,
2650 CREATION_DATE ,
2651 CREATED_BY ,
2652 LAST_UPDATE_DATE ,
2653 LAST_UPDATED_BY ,
2654 LAST_UPDATE_LOGIN ,
2655 TAX_REGIME_ID
2656
2657 )
2658 SELECT
2659 L.LANGUAGE_CODE ,
2660 userenv('LANG') ,
2661 case when
2662 decode(d.global_attribute_category,
2663 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2664 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2665 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2666 B.TAX_REGIME_CODE)
2667 =
2668 UPPER(decode(d.global_attribute_category,
2669 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2670 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2671 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2672 B.TAX_REGIME_CODE))
2673 then
2674 Initcap(decode(d.global_attribute_category,
2675 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2676 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2677 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2678 B.TAX_REGIME_CODE))
2679 else
2680 decode(d.global_attribute_category,
2681 'JL.AR.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2682 'JL.BR.ARXSYSPA.Additional Info', d.meaning,
2683 'JL.CO.ARXSYSPA.SYS_PARAMETERS', d.meaning,
2684 B.TAX_REGIME_CODE)
2685 end ,
2686 SYSDATE ,
2687 fnd_global.user_id ,
2688 SYSDATE ,
2689 fnd_global.user_id ,
2690 fnd_global.conc_login_id ,
2691 B.TAX_REGIME_ID
2692 FROM
2693 FND_LANGUAGES L,
2694 ZX_REGIMES_B B,
2695 (select rates.tax_regime_code tax_regime_code,
2696 lkups.meaning meaning,
2697 params.global_attribute_category global_attribute_category
2698 from zx_rates_b rates,
2699 ar_vat_tax_all_b codes,
2700 ar_system_parameters_all params,
2701 fnd_lookups lkups
2702 where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
2703 AND codes.org_id = params.org_id
2704 AND codes.org_id = l_org_id
2705 and params.global_attribute13 = lkups.lookup_code
2706 and params.global_attribute_category in ('JL.AR.ARXSYSPA.SYS_PARAMETERS',
2707 'JL.BR.ARXSYSPA.Additional Info',
2708 'JL.CO.ARXSYSPA.SYS_PARAMETERS')
2709 and lkups.lookup_type = 'JLZZ_AR_TX_RULE_SET'
2710 group by rates.tax_regime_code,
2711 lkups.meaning,
2712 params.global_attribute_category
2713 ) D
2714 WHERE
2715 L.INSTALLED_FLAG in ('I', 'B')
2716 AND B.RECORD_TYPE_CODE = 'MIGRATED'
2717 --
2718 AND b.tax_regime_code = d.tax_regime_code (+)
2719 AND not exists
2720 (select NULL
2721 from ZX_REGIMES_TL T
2722 where T.TAX_REGIME_ID = B.TAX_REGIME_ID
2723 and T.LANGUAGE = L.LANGUAGE_CODE);
2724
2725
2726 END IF;
2727
2728 END load_regimes;
2729
2730
2731 PROCEDURE update_tax_status AS
2732 BEGIN
2733 BEGIN
2734 FOR cursor_rec IN
2735 (
2736 SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'INPUT'
2737 INTERSECT
2738 SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'OUTPUT'
2739 AND tax_status_code <> 'STANDARD-AR-INPUT')
2740 LOOP
2741 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
2742 tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'INPUT';
2743
2744 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
2745 tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'OUTPUT';
2746
2747 END LOOP;
2748 EXCEPTION WHEN OTHERS THEN
2749 NULL;
2750 END;
2751
2752 END;
2753
2754
2755
2756 -- ****** CONSTRUCTOR ******
2757 BEGIN
2758 -- ****** Determine min(start_date) ******
2759 BEGIN
2760 SELECT min(start_date)
2761 INTO l_ap_min_start_date
2762 FROM ap_tax_codes_all;
2763 EXCEPTION
2764 WHEN NO_DATA_FOUND THEN
2765 l_ap_min_start_date := sysdate;
2766 END;
2767
2768 BEGIN
2769 SELECT min(start_date)
2770 INTO l_ar_min_start_date
2771 FROM ar_vat_tax_all_b;
2772 EXCEPTION
2773 WHEN NO_DATA_FOUND THEN
2774 l_ar_min_start_date := sysdate;
2775 END;
2776
2777 BEGIN
2778 SELECT count(*)
2779 INTO l_ap_count
2780 FROM ap_tax_codes_all
2781 WHERE tax_type <> 'AWT';
2782 --Bug 9415223
2783 END;
2784
2785 BEGIN
2786 SELECT count(*)
2787 INTO l_ar_count
2788 FROM ar_vat_tax_all_b;
2789 END;
2790
2791 IF l_ap_count = 0 THEN
2792 l_ap_min_start_date := sysdate;
2793 ELSIF l_ar_count = 0 THEN
2794 l_ar_min_start_date := sysdate;
2795 END IF;
2796
2797 IF l_ap_min_start_date >= l_ar_min_start_date THEN
2798 l_min_start_date := l_ar_min_start_date;
2799 ELSE
2800 l_min_start_date := l_ap_min_start_date;
2801 END IF;
2802
2803 BEGIN
2804 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
2805 FND_PRODUCT_GROUPS;
2806
2807 IF L_MULTI_ORG_FLAG = 'N' THEN
2808
2809 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
2810
2811 IF L_ORG_ID IS NULL THEN
2812 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
2813 END IF;
2814 ELSE
2815 L_ORG_ID := NULL;
2816 END IF;
2817
2818
2819 EXCEPTION
2820 WHEN OTHERS THEN
2821 arp_util_tax.debug('Exception in Common Migrate Tax Definition Constructor : '||sqlerrm);
2822
2823 END;
2824
2825 END;