[Home] [Help]
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,
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
124: );
125: ELSE
126:
127: INSERT
128: INTO zx_update_criteria_results
129: (
130: tax_code_id,
131: org_id,
132: tax_code,
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:
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
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,
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
339: ELSE
340:
341:
342: INSERT
343: INTO zx_update_criteria_results
344: (
345: tax_code_id,
346: org_id,
347: tax_code,
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:
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
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,
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
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,
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:
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,
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
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,
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:
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
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,
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
1004: );
1005: ELSE
1006:
1007: INSERT
1008: INTO zx_update_criteria_results
1009: (
1010: tax_code_id,
1011: org_id,
1012: tax_code,
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;
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
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,
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:
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
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,
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;
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
1462:
1463: BEGIN
1464: /*--------------------------------------------------------------------------
1465: |
1466: | Populating zx_regimes_b from zx_update_criteria_results
1467: |
1468: +---------------------------------------------------------------------------*/
1469:
1470: INSERT INTO ZX_REGIMES_B
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: )
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
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';
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';
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