[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:
854: | PROCEDURE
855: | load_tax_comp_results_for_ar
856: |
857: | DESCRIPTION
858: | 1. Populates data into zx_update_criteria_results table based on AR data in
859: | zx_tax_relations_t .
860: |
861: | ASSUMPTION:
862: | Since only AR related tax codes get migrated into zx_tax_priorities_t we do
882:
883: IF L_MULTI_ORG_FLAG = 'Y'
884: THEN
885: INSERT
886: INTO zx_update_criteria_results
887: (
888: tax_code_id,
889: org_id,
890: tax_code,
957: --Added following conditions for Sync process
958: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
959: --Rerunability
960: AND NOT EXISTS (SELECT 1
961: FROM zx_update_criteria_results zucr
962: WHERE zucr.tax_code_id = nvl(p_tax_id,codes.vat_tax_id)
963: AND zucr.tax_class = 'OUTPUT'
964: );
965: ELSE
964: );
965: ELSE
966:
967: INSERT
968: INTO zx_update_criteria_results
969: (
970: tax_code_id,
971: org_id,
972: tax_code,
1040: --Added following conditions for Sync process
1041: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
1042: --Rerunability
1043: AND NOT EXISTS (SELECT 1
1044: FROM zx_update_criteria_results zucr
1045: WHERE zucr.tax_code_id = nvl(p_tax_id,codes.vat_tax_id)
1046: AND zucr.tax_class = 'OUTPUT'
1047: );
1048: END IF;
1053: | PROCEDURE
1054: | load_results_for_intercomp_ap
1055: |
1056: | DESCRIPTION
1057: | Populates data into zx_update_criteria_results table for AP Tax Codes
1058: | that is used in intercompany transaction.
1059: |
1060: | MTL_INTERCOMPANY_PARAMTERS table stores information about two OUs that
1061: | are used for intercompany transactions. The customer related information is
1076: +==========================================================================*/
1077: PROCEDURE load_results_for_intercomp_ap (p_tax_id NUMBER) AS
1078: BEGIN
1079: INSERT
1080: INTO zx_update_criteria_results
1081: (
1082: tax_code_id,
1083: org_id,
1084: tax_code,
1152: -- Sync process
1153: AND ap_codes.tax_id = nvl(p_tax_id,ap_codes.tax_id)
1154: -- Rerunability
1155: AND NOT EXISTS (SELECT 1
1156: FROM zx_update_criteria_results zucr
1157: WHERE zucr.tax_code_id = nvl(p_tax_id,ap_codes.tax_id)
1158: AND zucr.tax_class = 'INPUT'
1159: );
1160:
1167: | PROCEDURE
1168: | load_results_for_intercomp_ar
1169: |
1170: | DESCRIPTION
1171: | Populates data into zx_update_criteria_results table for AR Tax Codes
1172: | that is used in intercompany transaction.
1173: |
1174: | MTL_INTERCOMPANY_PARAMTERS table stores information about two OUs that
1175: | are used for intercompany transactions. The customer related information is
1191: PROCEDURE load_results_for_intercomp_ar (p_tax_id NUMBER) AS
1192: BEGIN
1193:
1194: INSERT
1195: INTO zx_update_criteria_results
1196: (
1197: tax_code_id,
1198: org_id,
1199: tax_code,
1283: --Added following conditions for Sync process
1284: AND ar_codes.vat_tax_id = nvl(p_tax_id, ar_codes.vat_tax_id)
1285: --Rerunability
1286: AND NOT EXISTS (SELECT 1
1287: FROM zx_update_criteria_results zucr
1288: WHERE zucr.tax_code_id = nvl(p_tax_id,ar_codes.vat_tax_id)
1289: AND zucr.tax_class = 'OUTPUT'
1290: );
1291: END load_results_for_intercomp_ar;
1296: | load_regimes
1297: |
1298: | DESCRIPTION
1299: | 1. Populates data into zx_regimes_b table based on data in
1300: | zx_update_criteria_results table for normal tax codes.
1301: | 2. Populates data into zx_regimes_b for Brazilian IPI
1302: | 3. Populates data into zx_regimes_b for Brazilian ISS
1303: | 4. Populates data into zx_regimes_b for GTE US Sales Tax Regimes
1304: | 5. Populates data into zx_regimes_b for Tax Vendor Regimes
1385:
1386: BEGIN
1387: /*--------------------------------------------------------------------------
1388: |
1389: | Populating zx_regimes_b from zx_update_criteria_results
1390: |
1391: +---------------------------------------------------------------------------*/
1392:
1393: INSERT INTO ZX_REGIMES_B
1551: SELECT DISTINCT
1552: zucr.tax_regime_code l_tax_regime_code,
1553: zucr.country_code l_country_code,
1554: zucr.regime_precedence l_regime_precedence
1555: FROM zx_update_criteria_results zucr
1556: WHERE NOT EXISTS (SELECT 1
1557: FROM zx_regimes_b zrb
1558: WHERE zrb.tax_regime_code = zucr.tax_regime_code
1559: )
2655: BEGIN
2656: BEGIN
2657: FOR cursor_rec IN
2658: (
2659: SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'INPUT'
2660: INTERSECT
2661: SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'OUTPUT'
2662: AND tax_status_code <> 'STANDARD-AR-INPUT')
2663: LOOP
2657: FOR cursor_rec IN
2658: (
2659: SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'INPUT'
2660: INTERSECT
2661: SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'OUTPUT'
2662: AND tax_status_code <> 'STANDARD-AR-INPUT')
2663: LOOP
2664: UPDATE zx_update_criteria_results SET tax_status_code = 'STANDARD-INPUT' WHERE tax_regime_code = cursor_rec.tax_regime_code AND tax = cursor_rec.tax AND
2665: tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'INPUT';
2660: INTERSECT
2661: SELECT tax_regime_code,tax,tax_status_code,org_id ,tax_code,effective_from FROM zx_update_criteria_results WHERE tax_class = 'OUTPUT'
2662: AND tax_status_code <> 'STANDARD-AR-INPUT')
2663: LOOP
2664: UPDATE zx_update_criteria_results SET tax_status_code = 'STANDARD-INPUT' WHERE tax_regime_code = cursor_rec.tax_regime_code AND tax = cursor_rec.tax AND
2665: tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'INPUT';
2666:
2667: UPDATE zx_update_criteria_results SET tax_status_code = 'STANDARD-OUTPUT' WHERE tax_regime_code = cursor_rec.tax_regime_code AND tax = cursor_rec.tax AND
2668: tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'OUTPUT';
2663: LOOP
2664: UPDATE zx_update_criteria_results SET tax_status_code = 'STANDARD-INPUT' WHERE tax_regime_code = cursor_rec.tax_regime_code AND tax = cursor_rec.tax AND
2665: tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'INPUT';
2666:
2667: UPDATE zx_update_criteria_results SET tax_status_code = 'STANDARD-OUTPUT' WHERE tax_regime_code = cursor_rec.tax_regime_code AND tax = cursor_rec.tax AND
2668: tax_status_code = cursor_rec.tax_status_code AND org_id = cursor_rec.org_id AND tax_class = 'OUTPUT';
2669:
2670: END LOOP;
2671: EXCEPTION WHEN OTHERS THEN