19: PROCEDURE CREATE_ZX_REP_TYPE_CODES_AR(p_tax_id IN NUMBER);
20:
21: /* Common procedure used to create Reporting Types for AR*/
22: PROCEDURE CREATE_REPORTING_TYPE_AR(
23: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
24: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
25: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
26: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
27: p_tax_id IN NUMBER
20:
21: /* Common procedure used to create Reporting Types for AR*/
22: PROCEDURE CREATE_REPORTING_TYPE_AR(
23: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
24: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
25: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
26: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
27: p_tax_id IN NUMBER
28: );
21: /* Common procedure used to create Reporting Types for AR*/
22: PROCEDURE CREATE_REPORTING_TYPE_AR(
23: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
24: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
25: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
26: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
27: p_tax_id IN NUMBER
28: );
29:
28: );
29:
30: /* Common procedure used to create Reporting Types for AP*/
31: PROCEDURE CREATE_REPORTING_TYPE_AP(
32: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
33: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
34: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
35: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
36: p_tax_id IN NUMBER
29:
30: /* Common procedure used to create Reporting Types for AP*/
31: PROCEDURE CREATE_REPORTING_TYPE_AP(
32: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
33: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
34: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
35: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
36: p_tax_id IN NUMBER
37: );
30: /* Common procedure used to create Reporting Types for AP*/
31: PROCEDURE CREATE_REPORTING_TYPE_AP(
32: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
33: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
34: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
35: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
36: p_tax_id IN NUMBER
37: );
38:
37: );
38:
39: /* Common procedure used to create Reporting Codes */
40: PROCEDURE CREATE_REPORTING_CODES (
41: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
42: P_lookup_type IN FND_LOOKUPS.LOOKUP_TYPE%TYPE
43: );
44:
45: /*Common procedure used to create Reporting types for bug fix 3722296*/
44:
45: /*Common procedure used to create Reporting types for bug fix 3722296*/
46: PROCEDURE CREATE_REPORT_TYPE_PTP
47: (
48: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
49: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
50: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
51: );
52:
45: /*Common procedure used to create Reporting types for bug fix 3722296*/
46: PROCEDURE CREATE_REPORT_TYPE_PTP
47: (
48: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
49: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
50: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
51: );
52:
53: PROCEDURE CREATE_REPORT_TYPE_SEED
46: PROCEDURE CREATE_REPORT_TYPE_PTP
47: (
48: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
49: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
50: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
51: );
52:
53: PROCEDURE CREATE_REPORT_TYPE_SEED
54: (
51: );
52:
53: PROCEDURE CREATE_REPORT_TYPE_SEED
54: (
55: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
56: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
57: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
58: );
59:
52:
53: PROCEDURE CREATE_REPORT_TYPE_SEED
54: (
55: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
56: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
57: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
58: );
59:
60: PROCEDURE CREATE_REPORTING_CODES_SEED (
53: PROCEDURE CREATE_REPORT_TYPE_SEED
54: (
55: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
56: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
57: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
58: );
59:
60: PROCEDURE CREATE_REPORTING_CODES_SEED (
61: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
57: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
58: );
59:
60: PROCEDURE CREATE_REPORTING_CODES_SEED (
61: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
62: P_lookup_type IN FND_LOOKUPS.LOOKUP_TYPE%TYPE
63: );
64:
65: PROCEDURE CREATE_REPORTTYPE_USAGES_SEED;
104: PROCEDURE CREATE_ZX_REPORTING_ASSOC(p_tax_id IN NUMBER) IS
105: l_status fnd_module_installations.status%TYPE;
106: l_db_status fnd_module_installations.DB_STATUS%TYPE;
107:
108: l_reporting_type_id apps.zx_reporting_types_b.reporting_type_id%TYPE ;
109:
110: BEGIN
111: arp_util_tax.debug('CREATE_ZX_REPORTING_ASSOC(+)');
112:
122:
123: BEGIN
124: SELECT reporting_type_id
125: INTO l_reporting_type_id
126: FROM zx_reporting_types_b
127: WHERE reporting_type_code = 'REPORTING_STATUS_TRACKING';
128:
129: INSERT
130: INTO ZX_REPORT_CODES_ASSOC(
190: | |
191: | DESCRIPTION |
192: | This routine inserts data into following zx reporting entities based |
193: | on data in AP_TAX_CODES_ALL GDF attributes. |
194: | ZX_REPORTING_TYPES_B |
195: | ZX_REPORTING_TYPES_TL |
196: | ZX_REPORTING_CODES_B |
197: | ZX_REPORTING_CODES_TL |
198: | ZX_REPORT_TYPES_USAGES |
281: NULL,
282: report_types.REPORTING_TYPE_ID,
283: 1
284: FROM
285: ZX_REPORTING_TYPES_B report_types
286: WHERE
287: report_types.REPORTING_TYPE_CODE = 'PT_LOCATION'
288: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
289: AND NOT EXISTS
296:
297: -- Creation of FISCAL PRINTER Reporting Type and Reporting Usage
298: -- Bug # 3587896
299: INSERT ALL
300: WHEN ( NOT EXISTS (select 1 from zx_reporting_types_b
301: where reporting_type_code='FISCAL PRINTER'
302: and tax_regime_code = l_tax_regime_code)
303: )
304: THEN
301: where reporting_type_code='FISCAL PRINTER'
302: and tax_regime_code = l_tax_regime_code)
303: )
304: THEN
305: INTO ZX_REPORTING_TYPES_B(
306: REPORTING_TYPE_ID ,
307: REPORTING_TYPE_CODE ,
308: REPORTING_TYPE_DATATYPE_CODE ,
309: TAX_REGIME_CODE ,
327: PROGRAM_LOGIN_ID ,
328: OBJECT_VERSION_NUMBER
329: )
330: VALUES(
331: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
332: 'FISCAL PRINTER' ,--REPORTING_TYPE_CODE
333: 'YES_NO' ,--REPORTING_TYPE_DATATYPE
334: l_tax_regime_code ,--TAX_REGIME_CODE
335: NULL ,--TAX
353: 1
354: )
355: -- Creation of CAI NUMBER Reporting Type and Usage
356:
357: WHEN ( NOT EXISTS (select 1 from zx_reporting_types_b
358: where reporting_type_code='CAI NUMBER'
359: and tax_regime_code = l_tax_regime_code)
360: )
361: THEN
358: where reporting_type_code='CAI NUMBER'
359: and tax_regime_code = l_tax_regime_code)
360: )
361: THEN
362: INTO ZX_REPORTING_TYPES_B(
363: REPORTING_TYPE_ID ,
364: REPORTING_TYPE_CODE ,
365: REPORTING_TYPE_DATATYPE_CODE ,
366: TAX_REGIME_CODE ,
384: PROGRAM_LOGIN_ID ,
385: OBJECT_VERSION_NUMBER
386: )
387: VALUES(
388: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
389: 'CAI NUMBER' ,--REPORTING_TYPE_CODE
390: --'NUMBER' ,--REPORTING_TYPE_DATATYPE
391: 'TEXT' ,--REPORTING_TYPE_DATATYPE (Bug6430516)
392: l_tax_regime_code ,--TAX_REGIME_CODE
412: )
413:
414: --Bug# 3587896
415: SELECT effective_from, tax_regime_code l_tax_regime_code
416: FROM ZX_REPORTING_TYPES_B
417: WHERE REPORTING_TYPE_CODE = 'AR_DGI_TRX_CODE'; --YK:
418:
419: -----Creating reporting types for bug fix 3722296-------
420:
450: reporting_type_id ,--REPORTING_TYPE_ID
451: 1
452:
453: FROM
454: zx_reporting_types_b rep_type,
455: fnd_lookups
456:
457: WHERE
458: reporting_type_code = 'FISCAL PRINTER'
488: fnd_global.conc_login_id,--LAST_UPDATE_LOGIN
489: reporting_type_id ,--REPORTING_TYPE_ID
490: 1
491: FROM
492: zx_reporting_types_b rep_type,
493: fnd_lookups
494:
495: WHERE
496: reporting_type_code = 'CAI NUMBER'
526: SYSDATE ,
527: fnd_global.conc_login_id,
528: 1
529: FROM
530: zx_reporting_types_b types,
531: fnd_lookups lookups
532:
533: WHERE
534: types.reporting_type_code IN('PT_LOCATION' ,
605: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
606: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
607: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
608: FROM
609: ZX_REPORTING_TYPES_B report_types,
610: JA_LOOKUPS lookups
611: WHERE
612: report_types.REPORTING_TYPE_CODE = 'TW_GOVERNMENT_TAX_TYPE'
613: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
646: fnd_global.user_id ,
647: sysdate ,
648: fnd_global.conc_login_id
649: FROM
650: ZX_REPORTING_TYPES_B TYPES,
651: ZX_REPORTING_CODES_B CODES,
652: FND_LOOKUP_VALUES lookups,
653: FND_LANGUAGES L
654: WHERE
731:
732: ELSE types.REPORTING_TYPE_CODE END REPORTING_TYPE_NAME -- Bug 4886324
733:
734: FROM
735: ZX_REPORTING_TYPES_B TYPES,
736: FND_LANGUAGES L
737: WHERE
738: TYPES.RECORD_TYPE_CODE = 'MIGRATED'
739: AND L.INSTALLED_FLAG in ('I', 'B')
770: fnd_global.user_id ,
771: sysdate ,
772: fnd_global.conc_login_id
773: FROM
774: ZX_REPORTING_TYPES_B TYPES,
775: ZX_REPORTING_CODES_B CODES,
776: FND_LOOKUP_VALUES lookups,
777: FND_LANGUAGES L
778: WHERE
859:
860: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING TYPES FOR AR*/
861:
862: PROCEDURE CREATE_REPORTING_TYPE_AR(
863: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
864: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
865: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
866: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
867: p_tax_id IN NUMBER
860: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING TYPES FOR AR*/
861:
862: PROCEDURE CREATE_REPORTING_TYPE_AR(
863: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
864: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
865: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
866: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
867: p_tax_id IN NUMBER
868: )
861:
862: PROCEDURE CREATE_REPORTING_TYPE_AR(
863: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
864: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
865: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
866: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
867: p_tax_id IN NUMBER
868: )
869: IS
872:
873: arp_util_tax.debug('CREATE_REPORTING_TYPE_AR(+)');
874: arp_util_tax.debug('p_reporting_type_code = '||p_reporting_type_code);
875: INSERT ALL
876: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
877: where reporting_type_code= p_reporting_type_code
878: and tax_regime_code = l_tax_regime_code)
879: ) THEN
880: INTO ZX_REPORTING_TYPES_B(
876: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
877: where reporting_type_code= p_reporting_type_code
878: and tax_regime_code = l_tax_regime_code)
879: ) THEN
880: INTO ZX_REPORTING_TYPES_B(
881: REPORTING_TYPE_ID ,
882: REPORTING_TYPE_CODE ,
883: REPORTING_TYPE_DATATYPE_CODE ,
884: TAX_REGIME_CODE ,
902: PROGRAM_LOGIN_ID ,
903: OBJECT_VERSION_NUMBER
904: )
905: VALUES(
906: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
907: p_reporting_type_code ,--REPORTING_TYPE_CODE
908: p_datatype , --REPORTING_TYPE_DATATYPE
909: l_tax_regime_code ,
910: NULL ,--TAX
948:
949: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING TYPES FOR AP*/
950:
951: PROCEDURE CREATE_REPORTING_TYPE_AP(
952: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
953: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
954: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
955: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
956: p_tax_id IN NUMBER
949: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING TYPES FOR AP*/
950:
951: PROCEDURE CREATE_REPORTING_TYPE_AP(
952: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
953: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
954: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
955: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
956: p_tax_id IN NUMBER
957: )
950:
951: PROCEDURE CREATE_REPORTING_TYPE_AP(
952: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
953: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
954: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
955: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
956: p_tax_id IN NUMBER
957: )
958: IS
961:
962: arp_util_tax.debug('CREATE_REPORTING_TYPE_AP(+)');
963: arp_util_tax.debug('p_reporting_type_code = '||p_reporting_type_code);
964: INSERT ALL
965: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
966: where reporting_type_code= p_reporting_type_code
967: and tax_regime_code = l_tax_regime_code)
968: ) THEN
969: INTO ZX_REPORTING_TYPES_B(
965: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
966: where reporting_type_code= p_reporting_type_code
967: and tax_regime_code = l_tax_regime_code)
968: ) THEN
969: INTO ZX_REPORTING_TYPES_B(
970: REPORTING_TYPE_ID ,
971: REPORTING_TYPE_CODE ,
972: REPORTING_TYPE_DATATYPE_CODE ,
973: TAX_REGIME_CODE ,
991: PROGRAM_LOGIN_ID ,
992: OBJECT_VERSION_NUMBER
993: )
994: VALUES(
995: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
996: p_reporting_type_code ,--REPORTING_TYPE_CODE
997: p_datatype , --REPORTING_TYPE_DATATYPE
998: l_tax_regime_code ,
999: NULL ,--TAX
1038:
1039: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING CODES */
1040:
1041: PROCEDURE CREATE_REPORTING_CODES (
1042: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
1043: P_lookup_type IN FND_LOOKUPS.LOOKUP_TYPE%TYPE
1044: ) IS
1045:
1046: BEGIN
1097: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
1098: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
1099: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
1100: FROM
1101: ZX_REPORTING_TYPES_B report_types,
1102: FND_LOOKUPS lookups
1103: WHERE
1104: report_types.REPORTING_TYPE_CODE = p_reporting_type_code
1105: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
1138: fnd_global.user_id ,
1139: sysdate ,
1140: fnd_global.conc_login_id
1141: FROM
1142: ZX_REPORTING_TYPES_B TYPES,
1143: ZX_REPORTING_CODES_B CODES,
1144: FND_LOOKUP_VALUES lookups,
1145: FND_LANGUAGES L
1146: WHERE
1229: sysdate,
1230: fnd_global.conc_login_id,
1231: 1
1232: FROM
1233: zx_reporting_types_b types,
1234: ap_supplier_sites pvs,
1235: zx_party_tax_profile ptp
1236:
1237: WHERE
1279: sysdate,
1280: fnd_global.conc_login_id,
1281: 1
1282: FROM
1283: zx_reporting_types_b types,
1284: ap_supplier_sites pvs ,
1285: zx_party_tax_profile ptp
1286:
1287: WHERE
1411: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1412: FROM
1413: AP_TAX_CODES_ALL codes,
1414: ZX_RATES_B rates,
1415: ZX_REPORTING_TYPES_B reporting_types,
1416: ZX_REPORTING_CODES_B report_codes
1417: WHERE
1418: codes.tax_id = rates.tax_rate_id
1419: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1443: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1444: FROM
1445: AP_TAX_CODES_ALL codes,
1446: ZX_RATES_B rates,
1447: ZX_REPORTING_TYPES_B reporting_types,
1448: ZX_REPORTING_CODES_B report_codes
1449: WHERE
1450: codes.tax_id = rates.tax_rate_id
1451: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1474: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1475: FROM
1476: AP_TAX_CODES_ALL codes,
1477: ZX_RATES_B rates,
1478: ZX_REPORTING_TYPES_B reporting_types,
1479: ZX_REPORTING_CODES_B report_codes
1480: WHERE
1481: codes.tax_id = rates.tax_rate_id
1482: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1505: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1506: FROM
1507: AP_TAX_CODES_ALL codes,
1508: ZX_RATES_B rates,
1509: ZX_REPORTING_TYPES_B reporting_types,
1510: ZX_REPORTING_CODES_B report_codes
1511: WHERE
1512: codes.tax_id = rates.tax_rate_id
1513: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1536: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1537: FROM
1538: AP_TAX_CODES_ALL codes,
1539: ZX_RATES_B rates,
1540: ZX_REPORTING_TYPES_B reporting_types,
1541: ZX_REPORTING_CODES_B report_codes
1542: WHERE
1543: codes.tax_id = rates.tax_rate_id
1544: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1567: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1568: FROM
1569: AP_TAX_CODES_ALL codes,
1570: ZX_RATES_B rates,
1571: ZX_REPORTING_TYPES_B reporting_types,
1572: ZX_REPORTING_CODES_B report_codes
1573: WHERE
1574: codes.tax_id = rates.tax_rate_id
1575: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1601: NULL REPORTING_CODE_NUM_VALUE
1602: FROM
1603: AP_TAX_CODES_ALL codes,
1604: ZX_RATES_B rates,
1605: ZX_REPORTING_TYPES_B reporting_types
1606: -- ZX_REPORTING_CODES_B report_codes
1607: WHERE
1608: codes.tax_id = rates.tax_rate_id
1609: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1631: NULL REPORTING_CODE_NUM_VALUE
1632: FROM
1633: AP_TAX_CODES_ALL codes,
1634: ZX_RATES_B rates,
1635: ZX_REPORTING_TYPES_B reporting_types
1636: -- ZX_REPORTING_CODES_B report_codes
1637: WHERE
1638: codes.tax_id = rates.tax_rate_id
1639: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1662: NULL REPORTING_CODE_NUM_VALUE
1663: FROM
1664: AP_TAX_CODES_ALL codes,
1665: ZX_RATES_B rates,
1666: ZX_REPORTING_TYPES_B reporting_types
1667: -- ZX_REPORTING_CODES_B report_codes
1668: WHERE
1669: codes.tax_id = rates.tax_rate_id
1670: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1693: NULL REPORTING_CODE_NUM_VALUE
1694: FROM
1695: AP_TAX_CODES_ALL codes,
1696: ZX_RATES_B rates,
1697: ZX_REPORTING_TYPES_B reporting_types
1698: -- ZX_REPORTING_CODES_B report_codes
1699: WHERE
1700: codes.tax_id = rates.tax_rate_id
1701: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1724: NULL REPORTING_CODE_NUM_VALUE
1725: FROM
1726: AP_TAX_CODES_ALL codes,
1727: ZX_RATES_B rates,
1728: ZX_REPORTING_TYPES_B reporting_types
1729: -- ZX_REPORTING_CODES_B report_codes
1730: WHERE
1731:
1732: codes.tax_id = rates.tax_rate_id
1758:
1759: FROM
1760: AP_TAX_CODES_ALL codes,
1761: ZX_RATES_B rates,
1762: ZX_REPORTING_TYPES_B reporting_types
1763: -- ZX_REPORTING_CODES_B report_codes
1764: WHERE
1765: codes.tax_id = rates.tax_rate_id
1766: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1813: fnd_global.conc_login_id,
1814: 1 -- Bug 5031787
1815: FROM
1816: financials_system_params_all fin_sys_param,
1817: zx_reporting_types_b types,
1818: zx_reporting_codes_b codes,
1819: xle_etb_profiles etb,
1820: zx_party_tax_profile ptp
1821:
1863: fnd_global.conc_login_id,
1864: 1 -- Bug 5031787
1865: FROM
1866: financials_system_params_all fin_sys_param,
1867: zx_reporting_types_b types,
1868: zx_reporting_codes_b codes,
1869: xle_etb_profiles etb,
1870: zx_party_tax_profile ptp
1871:
1926: report_codes.REPORTING_CODE_NUM_VALUE , --REPORTING_CODE_NUM_VALUE
1927: 1
1928: FROM
1929: AP_TAX_CODES_ALL codes,
1930: ZX_REPORTING_TYPES_B reporting_types,
1931: ZX_REPORTING_CODES_B report_codes,
1932: ZX_RATES_B rates
1933:
1934:
2017: FROM
2018:
2019: AR_VAT_TAX_ALL_B codes,
2020: ZX_RATES_B rates,
2021: ZX_REPORTING_TYPES_B report_types,
2022: ZX_REPORTING_CODES_B report_codes
2023:
2024: WHERE
2025:
2055: FROM
2056:
2057: AR_VAT_TAX_ALL_B codes,
2058: ZX_RATES_B rates,
2059: ZX_REPORTING_TYPES_B report_types,
2060: ZX_REPORTING_CODES_B report_codes
2061:
2062: WHERE
2063:
2090: FROM
2091:
2092: AR_VAT_TAX_ALL_B codes,
2093: ZX_RATES_B rates,
2094: ZX_REPORTING_TYPES_B report_types,
2095: ZX_REPORTING_CODES_B report_codes
2096:
2097: WHERE
2098:
2125: FROM
2126:
2127: AR_VAT_TAX_ALL_B codes,
2128: ZX_RATES_B rates,
2129: ZX_REPORTING_TYPES_B report_types,
2130: ZX_REPORTING_CODES_B report_codes
2131:
2132: WHERE
2133:
2160: FROM
2161:
2162: AR_VAT_TAX_ALL_B codes,
2163: ZX_RATES_B rates,
2164: ZX_REPORTING_TYPES_B report_types,
2165: ZX_REPORTING_CODES_B report_codes
2166:
2167: WHERE
2168:
2195:
2196: FROM
2197: AR_VAT_TAX_ALL_B codes,
2198: ZX_RATES_B rates,
2199: ZX_REPORTING_TYPES_B report_types
2200: WHERE
2201: codes.vat_tax_id = rates.tax_rate_id
2202: AND codes.vat_tax_id = nvl(p_tax_id,codes.vat_tax_id)
2203: AND codes.global_attribute_category = 'JL.CL.ARXSUVAT.VAT_TAX'
2229: FROM
2230:
2231: AR_VAT_TAX_ALL_B codes,
2232: ZX_RATES_B rates,
2233: ZX_REPORTING_TYPES_B report_types
2234: -- ZX_REPORTING_CODES_B report_codes
2235:
2236: WHERE
2237:
2264: FROM
2265:
2266: AR_VAT_TAX_ALL_B codes,
2267: ZX_RATES_B rates,
2268: ZX_REPORTING_TYPES_B report_types
2269: -- ZX_REPORTING_CODES_B report_codes
2270:
2271: WHERE
2272:
2299: FROM
2300:
2301: AR_VAT_TAX_ALL_B codes,
2302: ZX_RATES_B rates,
2303: ZX_REPORTING_TYPES_B report_types
2304: -- ZX_REPORTING_CODES_B report_codes
2305:
2306: WHERE
2307:
2336: FROM
2337:
2338: AR_VAT_TAX_ALL_B codes,
2339: ZX_RATES_B rates,
2340: ZX_REPORTING_TYPES_B report_types
2341: -- ZX_REPORTING_CODES_B report_codes
2342:
2343: WHERE
2344:
2371: FROM
2372:
2373: AR_VAT_TAX_ALL_B codes,
2374: ZX_RATES_B rates,
2375: ZX_REPORTING_TYPES_B report_types
2376: -- ZX_REPORTING_CODES_B report_codes
2377:
2378: WHERE
2379:
2407: FROM
2408:
2409: AR_VAT_TAX_ALL_B codes,
2410: ZX_RATES_B rates,
2411: ZX_REPORTING_TYPES_B report_types
2412: -- ZX_REPORTING_CODES_B report_codes
2413:
2414: WHERE
2415:
2443: FROM
2444:
2445: AR_VAT_TAX_ALL_B codes,
2446: ZX_RATES_B rates,
2447: ZX_REPORTING_TYPES_B report_types,
2448: ZX_REPORTING_CODES_B report_codes
2449:
2450: WHERE
2451:
2480: FROM
2481:
2482: AR_VAT_TAX_ALL_B codes,
2483: ZX_RATES_B rates,
2484: ZX_REPORTING_TYPES_B report_types,
2485: ZX_REPORTING_CODES_B report_codes
2486:
2487: WHERE
2488:
2517: FROM
2518:
2519: AR_VAT_TAX_ALL_B codes,
2520: ZX_RATES_B rates,
2521: ZX_REPORTING_TYPES_B report_types,
2522: ZX_REPORTING_CODES_B report_codes
2523:
2524: WHERE
2525:
2554: FROM
2555:
2556: AR_VAT_TAX_ALL_B codes,
2557: ZX_RATES_B rates,
2558: ZX_REPORTING_TYPES_B report_types,
2559: ZX_REPORTING_CODES_B report_codes
2560:
2561: WHERE
2562:
2591: FROM
2592:
2593: AR_VAT_TAX_ALL_B codes,
2594: ZX_RATES_B rates,
2595: ZX_REPORTING_TYPES_B report_types,
2596: ZX_REPORTING_CODES_B report_codes
2597:
2598: WHERE
2599:
2628: report_codes.REPORTING_CODE_NUM_VALUE
2629: FROM
2630: AR_VAT_TAX_ALL_TL codes,
2631: ZX_RATES_B rates,
2632: ZX_REPORTING_TYPES_B report_types,
2633: ZX_REPORTING_CODES_B report_codes
2634:
2635: WHERE
2636:
2699: REPORTING_TYPE_ID,
2700: rates.EFFECTIVE_FROM,
2701: codes.global_attribute6 REPORTING_CODE_CHAR_VALUE
2702: FROM
2703: ZX_REPORTING_TYPES_B report_types,
2704: AR_VAT_TAX_ALL_B codes,
2705: ZX_RATES_B rates
2706:
2707: WHERE
2726: REPORTING_TYPE_ID,
2727: rates.EFFECTIVE_FROM,
2728: codes.global_attribute6 REPORTING_CODE_CHAR_VALUE
2729: FROM
2730: ZX_REPORTING_TYPES_B report_types,
2731: AR_VAT_TAX_ALL_B codes,
2732: ZX_RATES_B rates
2733:
2734: WHERE
2754: REPORTING_TYPE_ID,
2755: rates.EFFECTIVE_FROM,
2756: codes.global_attribute2 REPORTING_CODE_CHAR_VALUE
2757: FROM
2758: ZX_REPORTING_TYPES_B report_types,
2759: AR_VAT_TAX_ALL_B codes,
2760: ZX_RATES_B rates
2761:
2762: WHERE
2782: REPORTING_TYPE_ID,
2783: rates.EFFECTIVE_FROM,
2784: codes.global_attribute2 REPORTING_CODE_CHAR_VALUE
2785: FROM
2786: ZX_REPORTING_TYPES_B report_types,
2787: AR_VAT_TAX_ALL_B codes,
2788: ZX_RATES_B rates
2789:
2790: WHERE
2811: REPORTING_TYPE_ID,
2812: rates.EFFECTIVE_FROM,
2813: codes.global_attribute2 REPORTING_CODE_CHAR_VALUE
2814: FROM
2815: ZX_REPORTING_TYPES_B report_types,
2816: AR_VAT_TAX_ALL_B codes,
2817: ZX_RATES_B rates
2818:
2819: WHERE
2912: arp_util_tax.debug('CREATE_ZX_REP_TYPE_CODES_AP(+)');
2913: END IF;
2914:
2915: INSERT ALL
2916: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
2917: where reporting_type_code='PT_LOCATION'
2918: and tax_regime_code = l_tax_regime_code)
2919: ) THEN
2920: INTO ZX_REPORTING_TYPES_B(
2916: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
2917: where reporting_type_code='PT_LOCATION'
2918: and tax_regime_code = l_tax_regime_code)
2919: ) THEN
2920: INTO ZX_REPORTING_TYPES_B(
2921: REPORTING_TYPE_ID ,
2922: REPORTING_TYPE_CODE ,
2923: REPORTING_TYPE_DATATYPE_CODE ,
2924: TAX_REGIME_CODE ,
2943: PROGRAM_LOGIN_ID ,
2944: OBJECT_VERSION_NUMBER
2945: )
2946: VALUES(
2947: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
2948: 'PT_LOCATION' ,--REPORTING_TYPE_CODE
2949: 'TEXT' ,--REPORTING_TYPE_DATATYPE
2950: l_tax_regime_code ,
2951: NULL ,--TAX
2967: fnd_global.conc_program_id , -- Program Id
2968: fnd_global.conc_login_id , -- Program Login ID
2969: 1
2970: )
2971: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
2972: where reporting_type_code='PT_PRD_TAXABLE_BOX'
2973: and tax_regime_code = l_tax_regime_code)
2974: ) THEN
2975: INTO ZX_REPORTING_TYPES_B(
2971: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
2972: where reporting_type_code='PT_PRD_TAXABLE_BOX'
2973: and tax_regime_code = l_tax_regime_code)
2974: ) THEN
2975: INTO ZX_REPORTING_TYPES_B(
2976: REPORTING_TYPE_ID ,
2977: REPORTING_TYPE_CODE ,
2978: REPORTING_TYPE_DATATYPE_CODE ,
2979: TAX_REGIME_CODE ,
2998: PROGRAM_LOGIN_ID ,
2999: OBJECT_VERSION_NUMBER
3000: )
3001: VALUES(
3002: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
3003: 'PT_PRD_TAXABLE_BOX' ,--REPORTING_TYPE_CODE
3004: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3005: l_tax_regime_code ,
3006: NULL ,--TAX
3022: fnd_global.conc_program_id , -- Program Id
3023: fnd_global.conc_login_id ,-- Program Login ID
3024: 1
3025: )
3026: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3027: where reporting_type_code='PT_PRD_REC_TAX_BOX'
3028: and tax_regime_code = l_tax_regime_code)
3029: ) THEN
3030: INTO ZX_REPORTING_TYPES_B(
3026: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3027: where reporting_type_code='PT_PRD_REC_TAX_BOX'
3028: and tax_regime_code = l_tax_regime_code)
3029: ) THEN
3030: INTO ZX_REPORTING_TYPES_B(
3031: REPORTING_TYPE_ID ,
3032: REPORTING_TYPE_CODE ,
3033: REPORTING_TYPE_DATATYPE_CODE ,
3034: TAX_REGIME_CODE ,
3053: PROGRAM_LOGIN_ID ,
3054: OBJECT_VERSION_NUMBER
3055: )
3056: VALUES(
3057: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3058: 'PT_PRD_REC_TAX_BOX' ,--REPORTING_TYPE_CODE
3059: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3060: l_tax_regime_code ,
3061: NULL ,--TAX
3077: fnd_global.conc_program_id , -- Program Id
3078: fnd_global.conc_login_id , -- Program Login ID
3079: 1
3080: )
3081: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3082: where reporting_type_code='PT_ANL_TTL_TAXABLE_BOX'
3083: and tax_regime_code = l_tax_regime_code)
3084: ) THEN
3085: INTO ZX_REPORTING_TYPES_B(
3081: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3082: where reporting_type_code='PT_ANL_TTL_TAXABLE_BOX'
3083: and tax_regime_code = l_tax_regime_code)
3084: ) THEN
3085: INTO ZX_REPORTING_TYPES_B(
3086: REPORTING_TYPE_ID ,
3087: REPORTING_TYPE_CODE ,
3088: REPORTING_TYPE_DATATYPE_CODE ,
3089: TAX_REGIME_CODE ,
3108: PROGRAM_LOGIN_ID ,
3109: OBJECT_VERSION_NUMBER
3110: )
3111: VALUES(
3112: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3113: 'PT_ANL_TTL_TAXABLE_BOX' ,--REPORTING_TYPE_CODE
3114: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3115: l_tax_regime_code ,
3116: NULL ,--TAX
3132: fnd_global.conc_program_id , -- Program Id
3133: fnd_global.conc_login_id , -- Program Login ID
3134: 1
3135: )
3136: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3137: where reporting_type_code='PT_ANL_REC_TAXABLE'
3138: and tax_regime_code = l_tax_regime_code)
3139: ) THEN
3140: INTO ZX_REPORTING_TYPES_B(
3136: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3137: where reporting_type_code='PT_ANL_REC_TAXABLE'
3138: and tax_regime_code = l_tax_regime_code)
3139: ) THEN
3140: INTO ZX_REPORTING_TYPES_B(
3141: REPORTING_TYPE_ID ,
3142: REPORTING_TYPE_CODE ,
3143: REPORTING_TYPE_DATATYPE_CODE ,
3144: TAX_REGIME_CODE ,
3163: PROGRAM_LOGIN_ID ,
3164: OBJECT_VERSION_NUMBER
3165: )
3166: VALUES(
3167: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3168: 'PT_ANL_REC_TAXABLE' ,--REPORTING_TYPE_CODE
3169: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3170: l_tax_regime_code ,
3171: NULL ,--TAX
3187: fnd_global.conc_program_id , -- Program Id
3188: fnd_global.conc_login_id , -- Program Login ID
3189: 1
3190: )
3191: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3192: where reporting_type_code='PT_ANL_NON_REC_TAXABLE'
3193: and tax_regime_code = l_tax_regime_code)
3194: ) THEN
3195: INTO ZX_REPORTING_TYPES_B(
3191: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3192: where reporting_type_code='PT_ANL_NON_REC_TAXABLE'
3193: and tax_regime_code = l_tax_regime_code)
3194: ) THEN
3195: INTO ZX_REPORTING_TYPES_B(
3196: REPORTING_TYPE_ID ,
3197: REPORTING_TYPE_CODE ,
3198: REPORTING_TYPE_DATATYPE_CODE ,
3199: TAX_REGIME_CODE ,
3218: PROGRAM_LOGIN_ID ,
3219: OBJECT_VERSION_NUMBER
3220: )
3221: VALUES(
3222: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3223: 'PT_ANL_NON_REC_TAXABLE' ,--REPORTING_TYPE_CODE
3224: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3225: l_tax_regime_code ,
3226: NULL ,--TAX
3242: fnd_global.conc_program_id , -- Program Id
3243: fnd_global.conc_login_id , -- Program Login ID
3244: 1
3245: )
3246: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3247: where reporting_type_code='PT_ANL_REC_TAX_BOX'
3248: and tax_regime_code = l_tax_regime_code)
3249: ) THEN
3250: INTO ZX_REPORTING_TYPES_B(
3246: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3247: where reporting_type_code='PT_ANL_REC_TAX_BOX'
3248: and tax_regime_code = l_tax_regime_code)
3249: ) THEN
3250: INTO ZX_REPORTING_TYPES_B(
3251: REPORTING_TYPE_ID ,
3252: REPORTING_TYPE_CODE ,
3253: REPORTING_TYPE_DATATYPE_CODE ,
3254: TAX_REGIME_CODE ,
3273: PROGRAM_LOGIN_ID ,
3274: OBJECT_VERSION_NUMBER
3275: )
3276: VALUES(
3277: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3278: 'PT_ANL_REC_TAX_BOX' ,--REPORTING_TYPE_CODE
3279: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3280: l_tax_regime_code ,
3281: NULL ,--TAX
3318: CREATE_REPORTING_TYPE_AP('AR_DGI_TRX_CODE','TEXT','Y', 'JL.AR.APXTADTC.VAT', p_tax_id);
3319:
3320: /*
3321: INSERT ALL
3322: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3323: where reporting_type_code='AR_DGI_TRX_CODE'
3324: and tax_regime_code = l_tax_regime_code)
3325: ) THEN
3326: INTO ZX_REPORTING_TYPES_B(
3322: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3323: where reporting_type_code='AR_DGI_TRX_CODE'
3324: and tax_regime_code = l_tax_regime_code)
3325: ) THEN
3326: INTO ZX_REPORTING_TYPES_B(
3327: REPORTING_TYPE_ID ,
3328: REPORTING_TYPE_CODE ,
3329: REPORTING_TYPE_DATATYPE_CODE ,
3330: TAX_REGIME_CODE ,
3348: PROGRAM_LOGIN_ID ,
3349: OBJECT_VERSION_NUMBER
3350: )
3351: VALUES(
3352: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
3353: 'AR_DGI_TRX_CODE' ,--REPORTING_TYPE_CODE
3354: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3355: l_tax_regime_code ,
3356: NULL ,--TAX
3393:
3394: --Bug Fix : 4928369
3395: /*
3396: INSERT ALL
3397: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3398: where reporting_type_code='CL_TAX_CODE_CLASSIF'
3399: and tax_regime_code = l_tax_regime_code)
3400: ) THEN
3401: INTO ZX_REPORTING_TYPES_B(
3397: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3398: where reporting_type_code='CL_TAX_CODE_CLASSIF'
3399: and tax_regime_code = l_tax_regime_code)
3400: ) THEN
3401: INTO ZX_REPORTING_TYPES_B(
3402: REPORTING_TYPE_ID ,
3403: REPORTING_TYPE_CODE ,
3404: REPORTING_TYPE_DATATYPE_CODE ,
3405: TAX_REGIME_CODE ,
3423: PROGRAM_LOGIN_ID ,
3424: OBJECT_VERSION_NUMBER
3425: )
3426: VALUES(
3427: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
3428: 'CL_TAX_CODE_CLASSIF' ,--REPORTING_TYPE_CODE
3429: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3430: l_tax_regime_code ,
3431: NULL ,--TAX
3530: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID
3531: FROM
3532:
3533: FINANCIALS_SYSTEM_PARAMS_ALL fin_sys_param,
3534: ZX_REPORTING_TYPES_B report_types
3535:
3536: WHERE
3537:
3538: report_types.REPORTING_TYPE_CODE = 'MEMBER STATE'
3605: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3606:
3607: FROM
3608:
3609: ZX_REPORTING_TYPES_B report_types,
3610: FND_LOOKUPS lookups
3611:
3612: WHERE
3613:
3678: types.effective_from effective_from,
3679: types.reporting_type_id reporting_type_id,
3680: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3681: FROM
3682: zx_reporting_types_b types,
3683: ap_tax_codes_all codes
3684:
3685: WHERE
3686: types.reporting_type_code = 'PT_PRD_TAXABLE_BOX'
3704: types.reporting_type_id reporting_type_id,
3705: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3706:
3707: FROM
3708: zx_reporting_types_b types,
3709: ap_tax_codes_all codes
3710:
3711: WHERE
3712: types.reporting_type_code = 'PT_PRD_REC_TAX_BOX'
3730: types.effective_from effective_from,
3731: types.reporting_type_id reporting_type_id,
3732: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3733: FROM
3734: zx_reporting_types_b types,
3735: ap_tax_codes_all codes
3736:
3737: WHERE
3738: types.reporting_type_code = 'PT_ANL_TTL_TAXABLE_BOX'
3755: types.effective_from effective_from,
3756: types.reporting_type_id reporting_type_id,
3757: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3758: FROM
3759: zx_reporting_types_b types,
3760: ap_tax_codes_all codes
3761:
3762: WHERE
3763: types.reporting_type_code = 'PT_ANL_REC_TAXABLE'
3780: types.effective_from effective_from,
3781: types.reporting_type_id reporting_type_id,
3782: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3783: FROM
3784: zx_reporting_types_b types,
3785: ap_tax_codes_all codes
3786:
3787: WHERE
3788: types.reporting_type_code = 'PT_ANL_NON_REC_TAXABLE'
3806: types.effective_from effective_from,
3807: types.reporting_type_id reporting_type_id,
3808: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3809: FROM
3810: zx_reporting_types_b types,
3811: ap_tax_codes_all codes
3812:
3813: WHERE
3814: types.reporting_type_code = 'PT_ANL_REC_TAX_BOX'
3830: -- Bug Fix 4671652
3831: -- Creation of reporting types for KR_BUSINESS_LOCATIONS
3832:
3833: INSERT ALL
3834: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3835: where reporting_type_code= 'KR_BUSINESS_LOCATIONS'
3836: and tax_regime_code = l_tax_regime_code
3837: )
3838: ) THEN
3835: where reporting_type_code= 'KR_BUSINESS_LOCATIONS'
3836: and tax_regime_code = l_tax_regime_code
3837: )
3838: ) THEN
3839: INTO ZX_REPORTING_TYPES_B(
3840: REPORTING_TYPE_ID ,
3841: REPORTING_TYPE_CODE ,
3842: REPORTING_TYPE_DATATYPE_CODE ,
3843: TAX_REGIME_CODE ,
3861: PROGRAM_LOGIN_ID ,
3862: OBJECT_VERSION_NUMBER
3863: )
3864: VALUES(
3865: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
3866: 'KR_BUSINESS_LOCATIONS' ,--REPORTING_TYPE_CODE
3867: --'VARCHAR' ,--REPORTING_TYPE_DATATYPE
3868: 'TEXT' ,--REPORTING_TYPE_DATATYPE (Bug6430516)
3869: l_tax_regime_code ,--TAX_REGIME_CODE
3946: fnd_global.conc_login_id , -- Program Login ID
3947: l_location_code,
3948: NULL,
3949: NULL,
3950: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
3951: and reporting_type_code = 'KR_BUSINESS_LOCATIONS'),
3952: 1)
3953:
3954: select
3968: and tax_id_values_and_codes.tax_id = nvl(p_tax_id,tax_id_values_and_codes.tax_id)
3969: and tax_id_values_and_codes.location_code is not null
3970: and not exists
3971: ( select 1 from zx_reporting_codes_b where reporting_type_id =
3972: (select reporting_type_id FROM zx_reporting_types_b
3973: WHERE tax_regime_code = zxrb.tax_regime_code
3974: AND reporting_type_code = 'KR_BUSINESS_LOCATIONS')
3975: AND reporting_code_char_value = tax_id_values_and_codes.location_code);
3976:
4030: FROM
4031: AP_TAX_CODES_ALL codes,
4032: HR_LOCATIONS hrloc,
4033: ZX_RATES_B rates,
4034: ZX_REPORTING_TYPES_B reporting_types,
4035: ZX_REPORTING_CODES_B report_codes
4036: WHERE
4037: codes.tax_id = rates.tax_rate_id
4038: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
4098: reporting types. But in the below insert all we are using same context for all the reporting types.
4099: Hence we are using bulk insert only for the same context case. */
4100:
4101: INSERT ALL
4102: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4103: where reporting_type_code='PT_LOCATION'
4104: and tax_regime_code = l_tax_regime_code)
4105: ) THEN
4106: INTO
4103: where reporting_type_code='PT_LOCATION'
4104: and tax_regime_code = l_tax_regime_code)
4105: ) THEN
4106: INTO
4107: ZX_REPORTING_TYPES_B(
4108: REPORTING_TYPE_ID ,
4109: REPORTING_TYPE_CODE ,
4110: REPORTING_TYPE_DATATYPE_CODE ,
4111: TAX_REGIME_CODE ,
4130: PROGRAM_LOGIN_ID ,
4131: OBJECT_VERSION_NUMBER
4132: )
4133: VALUES(
4134: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
4135: 'PT_LOCATION' ,--REPORTING_TYPE_CODE
4136: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4137: l_tax_regime_code ,
4138: NULL ,--TAX
4154: fnd_global.conc_program_id , -- Program Id
4155: fnd_global.conc_login_id , -- Program Login ID
4156: 1
4157: )
4158: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4159: where reporting_type_code='PT_PRD_TAXABLE_BOX'
4160: and tax_regime_code = l_tax_regime_code)
4161: ) THEN
4162: INTO ZX_REPORTING_TYPES_B(
4158: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4159: where reporting_type_code='PT_PRD_TAXABLE_BOX'
4160: and tax_regime_code = l_tax_regime_code)
4161: ) THEN
4162: INTO ZX_REPORTING_TYPES_B(
4163: REPORTING_TYPE_ID ,
4164: REPORTING_TYPE_CODE ,
4165: REPORTING_TYPE_DATATYPE_CODE ,
4166: TAX_REGIME_CODE ,
4185: PROGRAM_LOGIN_ID ,
4186: OBJECT_VERSION_NUMBER
4187: )
4188: VALUES(
4189: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4190: 'PT_PRD_TAXABLE_BOX' ,--REPORTING_TYPE_CODE
4191: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4192: l_tax_regime_code ,
4193: NULL ,--TAX
4209: fnd_global.conc_program_id , -- Program Id
4210: fnd_global.conc_login_id , -- Program Login ID
4211: 1
4212: )
4213: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4214: where reporting_type_code='PT_PRD_REC_TAX_BOX'
4215: and tax_regime_code = l_tax_regime_code)
4216: ) THEN
4217: INTO ZX_REPORTING_TYPES_B(
4213: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4214: where reporting_type_code='PT_PRD_REC_TAX_BOX'
4215: and tax_regime_code = l_tax_regime_code)
4216: ) THEN
4217: INTO ZX_REPORTING_TYPES_B(
4218: REPORTING_TYPE_ID ,
4219: REPORTING_TYPE_CODE ,
4220: REPORTING_TYPE_DATATYPE_CODE ,
4221: TAX_REGIME_CODE ,
4240: PROGRAM_LOGIN_ID ,
4241: OBJECT_VERSION_NUMBER
4242: )
4243: VALUES(
4244: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4245: 'PT_PRD_REC_TAX_BOX' ,--REPORTING_TYPE_CODE
4246: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4247: l_tax_regime_code ,
4248: NULL ,--TAX
4264: fnd_global.conc_program_id , -- Program Id
4265: fnd_global.conc_login_id , -- Program Login ID
4266: 1
4267: )
4268: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4269: where reporting_type_code='PT_ANL_TTL_TAXABLE_BOX'
4270: and tax_regime_code = l_tax_regime_code)
4271: ) THEN
4272: INTO ZX_REPORTING_TYPES_B(
4268: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4269: where reporting_type_code='PT_ANL_TTL_TAXABLE_BOX'
4270: and tax_regime_code = l_tax_regime_code)
4271: ) THEN
4272: INTO ZX_REPORTING_TYPES_B(
4273: REPORTING_TYPE_ID ,
4274: REPORTING_TYPE_CODE ,
4275: REPORTING_TYPE_DATATYPE_CODE ,
4276: TAX_REGIME_CODE ,
4295: PROGRAM_LOGIN_ID ,
4296: OBJECT_VERSION_NUMBER
4297: )
4298: VALUES(
4299: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4300: 'PT_ANL_TTL_TAXABLE_BOX' ,--REPORTING_TYPE_CODE
4301: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4302: l_tax_regime_code ,
4303: NULL ,--TAX
4319: fnd_global.conc_program_id , -- Program Id
4320: fnd_global.conc_login_id , -- Program Login ID
4321: 1
4322: )
4323: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4324: where reporting_type_code='PT_ANL_REC_TAXABLE' -- Bug 5031787
4325: and tax_regime_code = l_tax_regime_code)
4326: ) THEN
4327: INTO ZX_REPORTING_TYPES_B(
4323: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4324: where reporting_type_code='PT_ANL_REC_TAXABLE' -- Bug 5031787
4325: and tax_regime_code = l_tax_regime_code)
4326: ) THEN
4327: INTO ZX_REPORTING_TYPES_B(
4328: REPORTING_TYPE_ID ,
4329: REPORTING_TYPE_CODE ,
4330: REPORTING_TYPE_DATATYPE_CODE ,
4331: TAX_REGIME_CODE ,
4350: PROGRAM_LOGIN_ID ,
4351: OBJECT_VERSION_NUMBER
4352: )
4353: VALUES(
4354: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4355: 'PT_ANL_REC_TAXABLE' ,--REPORTING_TYPE_CODE
4356: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4357: l_tax_regime_code ,
4358: NULL ,--TAX
4374: fnd_global.conc_program_id , -- Program Id
4375: fnd_global.conc_login_id , -- Program Login ID
4376: 1
4377: )
4378: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4379: where reporting_type_code='PT_ANL_NON_REC_TAXABLE'
4380: and tax_regime_code = l_tax_regime_code)
4381: ) THEN
4382: INTO ZX_REPORTING_TYPES_B(
4378: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4379: where reporting_type_code='PT_ANL_NON_REC_TAXABLE'
4380: and tax_regime_code = l_tax_regime_code)
4381: ) THEN
4382: INTO ZX_REPORTING_TYPES_B(
4383: REPORTING_TYPE_ID ,
4384: REPORTING_TYPE_CODE ,
4385: REPORTING_TYPE_DATATYPE_CODE ,
4386: TAX_REGIME_CODE ,
4405: PROGRAM_LOGIN_ID ,
4406: OBJECT_VERSION_NUMBER
4407: )
4408: VALUES(
4409: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4410: 'PT_ANL_NON_REC_TAXABLE' ,--REPORTING_TYPE_CODE
4411: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4412: l_tax_regime_code ,
4413: NULL ,--TAX
4429: fnd_global.conc_program_id , -- Program Id
4430: fnd_global.conc_login_id , -- Program Login ID
4431: 1
4432: )
4433: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4434: where reporting_type_code='PT_ANL_REC_TAX_BOX' -- Bug 5031787
4435: and tax_regime_code = l_tax_regime_code)
4436: ) THEN
4437: INTO ZX_REPORTING_TYPES_B(
4433: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4434: where reporting_type_code='PT_ANL_REC_TAX_BOX' -- Bug 5031787
4435: and tax_regime_code = l_tax_regime_code)
4436: ) THEN
4437: INTO ZX_REPORTING_TYPES_B(
4438: REPORTING_TYPE_ID ,
4439: REPORTING_TYPE_CODE ,
4440: REPORTING_TYPE_DATATYPE_CODE ,
4441: TAX_REGIME_CODE ,
4460: PROGRAM_LOGIN_ID ,
4461: OBJECT_VERSION_NUMBER
4462: )
4463: VALUES(
4464: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4465: 'PT_ANL_REC_TAX_BOX' ,--REPORTING_TYPE_CODE
4466: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4467: l_tax_regime_code ,
4468: NULL ,--TAX
4502: zx_rates.tax_regime_code;
4503:
4504:
4505: INSERT ALL
4506: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4507: where reporting_type_code='AR_DGI_TRX_CODE'
4508: and tax_regime_code = l_tax_regime_code)
4509: ) THEN
4510: INTO ZX_REPORTING_TYPES_B(
4506: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4507: where reporting_type_code='AR_DGI_TRX_CODE'
4508: and tax_regime_code = l_tax_regime_code)
4509: ) THEN
4510: INTO ZX_REPORTING_TYPES_B(
4511: REPORTING_TYPE_ID ,
4512: REPORTING_TYPE_CODE ,
4513: REPORTING_TYPE_DATATYPE_CODE ,
4514: TAX_REGIME_CODE ,
4532: PROGRAM_LOGIN_ID ,
4533: OBJECT_VERSION_NUMBER
4534: )
4535: VALUES(
4536: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4537: 'AR_DGI_TRX_CODE' ,--REPORTING_TYPE_CODE
4538: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4539: l_tax_regime_code ,
4540: NULL ,--TAX
4557: fnd_global.conc_login_id , -- Program Login ID
4558: 1
4559: )
4560:
4561: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4562: where reporting_type_code='AR_MUNICIPAL_JUR'
4563: and tax_regime_code = l_tax_regime_code)
4564: ) THEN
4565: INTO ZX_REPORTING_TYPES_B(
4561: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4562: where reporting_type_code='AR_MUNICIPAL_JUR'
4563: and tax_regime_code = l_tax_regime_code)
4564: ) THEN
4565: INTO ZX_REPORTING_TYPES_B(
4566: REPORTING_TYPE_ID ,
4567: REPORTING_TYPE_CODE ,
4568: REPORTING_TYPE_DATATYPE_CODE ,
4569: TAX_REGIME_CODE ,
4587: PROGRAM_LOGIN_ID ,
4588: OBJECT_VERSION_NUMBER
4589: )
4590: VALUES(
4591: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4592: 'AR_MUNICIPAL_JUR' ,--REPORTING_TYPE_CODE
4593: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4594: l_tax_regime_code ,
4595: NULL ,--TAX
4612: fnd_global.conc_login_id , -- Program Login ID
4613: 1
4614: )
4615:
4616: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4617: where reporting_type_code='AR_TURN_OVER_JUR_CODE'
4618: and tax_regime_code = l_tax_regime_code)
4619: ) THEN
4620: INTO ZX_REPORTING_TYPES_B(
4616: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4617: where reporting_type_code='AR_TURN_OVER_JUR_CODE'
4618: and tax_regime_code = l_tax_regime_code)
4619: ) THEN
4620: INTO ZX_REPORTING_TYPES_B(
4621: REPORTING_TYPE_ID ,
4622: REPORTING_TYPE_CODE ,
4623: REPORTING_TYPE_DATATYPE_CODE ,
4624: TAX_REGIME_CODE ,
4642: PROGRAM_LOGIN_ID ,
4643: OBJECT_VERSION_NUMBER
4644: )
4645: VALUES(
4646: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4647: 'AR_TURN_OVER_JUR_CODE' ,--REPORTING_TYPE_CODE
4648: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4649: l_tax_regime_code ,
4650: NULL ,--TAX
4684: zx_rates.tax_regime_code;
4685: --Bug 4705196
4686: /*
4687: --Bug 4422813
4688: --The creation of zx_reporting_types_b , zx_reporting_codes_b and zx_reporting_codes_tl has been put together for
4689: --easy understanding
4690:
4691: INSERT ALL
4692: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4688: --The creation of zx_reporting_types_b , zx_reporting_codes_b and zx_reporting_codes_tl has been put together for
4689: --easy understanding
4690:
4691: INSERT ALL
4692: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4693: where reporting_type_code= 'PRINTED_TAX_RATE_NAME'
4694: and tax_regime_code = l_tax_regime_code
4695: )
4696: ) THEN
4693: where reporting_type_code= 'PRINTED_TAX_RATE_NAME'
4694: and tax_regime_code = l_tax_regime_code
4695: )
4696: ) THEN
4697: INTO ZX_REPORTING_TYPES_B(
4698: REPORTING_TYPE_ID ,
4699: REPORTING_TYPE_CODE ,
4700: REPORTING_TYPE_DATATYPE_CODE ,
4701: TAX_REGIME_CODE ,
4719: PROGRAM_LOGIN_ID ,
4720: OBJECT_VERSION_NUMBER
4721: )
4722: VALUES(
4723: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
4724: 'PRINTED_TAX_RATE_NAME' ,--REPORTING_TYPE_CODE
4725: 'VARCHAR' , --REPORTING_TYPE_DATATYPE
4726: l_tax_regime_code ,
4727: NULL ,--TAX
4815: fnd_global.conc_login_id , -- Program Login ID
4816: printed_tax_name,
4817: NULL,
4818: NULL,
4819: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
4820: and reporting_type_code = 'PRINTED_TAX_RATE_NAME'),
4821: 1)
4822:
4823: (select
4831: and language = l_language_code
4832: and printed_tax_name is not null
4833: and not exists
4834: ( select 1 from zx_reporting_codes_b where reporting_type_id =
4835: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = zxrb.tax_regime_code
4836: and reporting_type_code = 'PRINTED_TAX_RATE_NAME')
4837: and reporting_code_char_value = printed_tax_name)
4838: );
4839:
4903:
4904: --Need to Delete Seeded records if there is a source for reporting type to get created using 11i Migrated Regimes.
4905: IF ( l_exists_cnt > 0 ) THEN
4906: DELETE FROM ZX_REPORTING_TYPES_TL
4907: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4908: WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED' );
4909:
4910: DELETE FROM ZX_REPORT_TYPES_USAGES
4911: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4907: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4908: WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED' );
4909:
4910: DELETE FROM ZX_REPORT_TYPES_USAGES
4911: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4912: WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED' );
4913:
4914: DELETE FROM ZX_REPORTING_TYPES_B WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED';
4915: END IF ;
4910: DELETE FROM ZX_REPORT_TYPES_USAGES
4911: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4912: WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED' );
4913:
4914: DELETE FROM ZX_REPORTING_TYPES_B WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED';
4915: END IF ;
4916:
4917: INSERT ALL
4918: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4914: DELETE FROM ZX_REPORTING_TYPES_B WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED';
4915: END IF ;
4916:
4917: INSERT ALL
4918: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4919: where reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE'
4920: and tax_regime_code = l_tax_regime_code
4921: )
4922: ) THEN
4919: where reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE'
4920: and tax_regime_code = l_tax_regime_code
4921: )
4922: ) THEN
4923: INTO ZX_REPORTING_TYPES_B(
4924: REPORTING_TYPE_ID ,
4925: REPORTING_TYPE_CODE ,
4926: REPORTING_TYPE_DATATYPE_CODE ,
4927: TAX_REGIME_CODE ,
4945: PROGRAM_LOGIN_ID ,
4946: OBJECT_VERSION_NUMBER
4947: )
4948: VALUES(
4949: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
4950: 'ZX_ADJ_TAX_CLASSIF_CODE' ,--REPORTING_TYPE_CODE
4951: --'VARCHAR' ,--REPORTING_TYPE_DATATYPE
4952: 'TEXT' ,--REPORTING_TYPE_DATATYPE (bug6430516)
4953: l_tax_regime_code ,--TAX_REGIME_CODE
5031: fnd_global.conc_login_id , -- Program Login ID
5032: adjustment_tax_code,
5033: NULL,
5034: NULL,
5035: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
5036: and reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE'),
5037: 1)
5038:
5039: select
5045: where zxrb.tax_rate_id = avtb.vat_tax_id
5046: and adjustment_tax_code is not null
5047: and not exists
5048: ( select 1 from zx_reporting_codes_b where reporting_type_id =
5049: (select reporting_type_id FROM zx_reporting_types_b
5050: WHERE tax_regime_code = zxrb.tax_regime_code
5051: AND reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE')
5052: AND reporting_code_char_value = adjustment_tax_code);
5053:
5095: fnd_global.conc_login_id , -- Program Login ID
5096: global_attribute5,
5097: NULL,
5098: NULL,
5099: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
5100: and reporting_type_code = 'CL_DEBIT_ACCOUNT'),
5101: 1)
5102:
5103: select
5110: and avtb.global_attribute_category ='JL.CL.ARXSUVAT.VAT_TAX'
5111: and avtb.global_attribute5 is not null
5112: and not exists
5113: ( select 1 from zx_reporting_codes_b where reporting_type_id =
5114: (select reporting_type_id FROM zx_reporting_types_b
5115: WHERE tax_regime_code = zxrb.tax_regime_code
5116: AND reporting_type_code = 'CL_DEBIT_ACCOUNT')
5117: AND reporting_code_char_value = avtb.global_attribute5);*/
5118:
5147: fnd_global.conc_login_id,--LAST_UPDATE_LOGIN
5148: reporting_type_id ,--REPORTING_TYPE_ID
5149: 1
5150: FROM
5151: zx_reporting_types_b rep_type,
5152: fnd_lookups lookups
5153: WHERE
5154: reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE'
5155: AND record_type_code = 'MIGRATED'
5221: WHEN types.REPORTING_TYPE_CODE = 'KR_BUSINESS_LOCATIONS' --Bug 4671652
5222: THEN 'Korean Business Locations'
5223: ELSE types.REPORTING_TYPE_CODE END REPORTING_TYPE_NAME -- Bug 4886324
5224: FROM
5225: ZX_REPORTING_TYPES_B TYPES,
5226: FND_LANGUAGES L
5227: WHERE
5228:
5229: TYPES.RECORD_TYPE_CODE = 'MIGRATED'
5263: SYSDATE ,
5264: fnd_global.conc_login_id,
5265: 1
5266: FROM
5267: zx_reporting_types_b types,
5268: fnd_lookups lookups
5269:
5270: WHERE types.reporting_type_code IN(
5271: 'AR_MUNICIPAL_JUR',
5334: NULL,
5335: report_types.REPORTING_TYPE_ID,
5336: 1
5337: FROM
5338: ZX_REPORTING_TYPES_B report_types
5339: WHERE
5340: report_types.REPORTING_TYPE_CODE = 'PT_LOCATION'
5341: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
5342: AND NOT EXISTS
5374: fnd_global.user_id ,
5375: sysdate ,
5376: fnd_global.conc_login_id
5377: FROM
5378: ZX_REPORTING_TYPES_B TYPES,
5379: ZX_REPORTING_CODES_B CODES,
5380: FND_LOOKUP_VALUES lookups,
5381: FND_LANGUAGES L
5382: WHERE
5422: fnd_global.user_id ,
5423: sysdate ,
5424: fnd_global.conc_login_id
5425: FROM
5426: ZX_REPORTING_TYPES_B TYPES,
5427: ZX_REPORTING_CODES_B CODES,
5428: FND_LOOKUP_VALUES lookups,
5429: FND_LANGUAGES L
5430: WHERE
5494: locations.location_code global_attribute,
5495: types.reporting_type_id reporting_type_id,
5496: types.effective_from effective_from
5497: FROM
5498: zx_reporting_types_b types,
5499: hr_locations locations
5500: WHERE
5501: types.reporting_type_code = 'KR_LOCATION'
5502: AND types.record_type_code = 'MIGRATED'
5536: fnd_global.user_id ,
5537: sysdate ,
5538: fnd_global.conc_login_id
5539: FROM
5540: ZX_REPORTING_TYPES_B TYPES,
5541: ZX_REPORTING_CODES_B CODES,
5542: FND_LOOKUP_VALUES lookups,
5543: FND_LANGUAGES L
5544: WHERE
5563: --Bug Fix 4671652
5564: --Creation of Reporting Types for KR_BUSINESS_LOCATIONS
5565:
5566: INSERT ALL
5567: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
5568: where reporting_type_code= 'KR_BUSINESS_LOCATIONS'
5569: and tax_regime_code = l_tax_regime_code
5570: )
5571: ) THEN
5568: where reporting_type_code= 'KR_BUSINESS_LOCATIONS'
5569: and tax_regime_code = l_tax_regime_code
5570: )
5571: ) THEN
5572: INTO ZX_REPORTING_TYPES_B(
5573: REPORTING_TYPE_ID ,
5574: REPORTING_TYPE_CODE ,
5575: REPORTING_TYPE_DATATYPE_CODE ,
5576: TAX_REGIME_CODE ,
5594: PROGRAM_LOGIN_ID ,
5595: OBJECT_VERSION_NUMBER
5596: )
5597: VALUES(
5598: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
5599: 'KR_BUSINESS_LOCATIONS' ,--REPORTING_TYPE_CODE
5600: --'VARCHAR' ,--REPORTING_TYPE_DATATYPE
5601: 'TEXT' ,--REPORTING_TYPE_DATATYPE (Bug6430516)
5602: l_tax_regime_code ,--TAX_REGIME_CODE
5680: fnd_global.conc_login_id , -- Program Login ID
5681: l_location_code,
5682: NULL,
5683: NULL,
5684: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
5685: and reporting_type_code = 'KR_BUSINESS_LOCATIONS'),
5686: 1)
5687:
5688: select
5702: and tax_id_values_and_codes.vat_tax_id = nvl(p_tax_id,tax_id_values_and_codes.vat_tax_id)
5703: and tax_id_values_and_codes.location_code is not null
5704: and not exists
5705: ( select 1 from zx_reporting_codes_b where reporting_type_id =
5706: (select reporting_type_id FROM zx_reporting_types_b
5707: WHERE tax_regime_code = zxrb.tax_regime_code
5708: AND reporting_type_code = 'KR_BUSINESS_LOCATIONS')
5709: AND reporting_code_char_value = tax_id_values_and_codes.location_code);
5710:
5763: FROM
5764: AR_VAT_TAX_ALL_B codes,
5765: HR_LOCATIONS hrloc,
5766: ZX_RATES_B rates,
5767: ZX_REPORTING_TYPES_B reporting_types,
5768: ZX_REPORTING_CODES_B report_codes
5769: WHERE
5770: codes.vat_tax_id = rates.tax_rate_id
5771: AND codes.vat_tax_id = nvl(p_tax_id,codes.vat_tax_id)
5815: reporting_type_id ,--REPORTING_TYPE_ID
5816: 1
5817:
5818: FROM
5819: zx_reporting_types_b rep_type
5820:
5821: WHERE
5822: reporting_type_code = 'KR_BUSINESS_LOCATIONS'
5823: AND NOT EXISTS ( select 1 from zx_report_types_usages
5866: (
5867:
5868: p_rep_type_info varchar2,
5869: p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
5870: p_reporting_type_code zx_reporting_types_b.reporting_type_code%type
5871: )
5872:
5873: IS
5874:
5906: sysdate,
5907: fnd_global.conc_login_id,
5908: 1
5909:
5910: FROM ZX_REPORTING_TYPES_B
5911: WHERE REPORTING_TYPE_CODE=p_reporting_type_code
5912:
5913: AND NOT EXISTS (SELECT 1 FROM
5914: ZX_REPORTING_TYPES_B RTB,
5910: FROM ZX_REPORTING_TYPES_B
5911: WHERE REPORTING_TYPE_CODE=p_reporting_type_code
5912:
5913: AND NOT EXISTS (SELECT 1 FROM
5914: ZX_REPORTING_TYPES_B RTB,
5915: ZX_REPORT_CODES_ASSOC RCA
5916: WHERE
5917:
5918: RTB.REPORTING_TYPE_ID = RCA.REPORTING_TYPE_ID
5950: | |
5951: |=========================================================================*/
5952: PROCEDURE CREATE_REPORT_TYPE_PTP
5953: (
5954: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
5955: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
5956: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
5957: )
5958: IS
5951: |=========================================================================*/
5952: PROCEDURE CREATE_REPORT_TYPE_PTP
5953: (
5954: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
5955: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
5956: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
5957: )
5958: IS
5959:
5952: PROCEDURE CREATE_REPORT_TYPE_PTP
5953: (
5954: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
5955: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
5956: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
5957: )
5958: IS
5959:
5960: BEGIN
5962: arp_util_tax.debug('CREATE_REPORT_TYPE_PTP(+)');
5963: arp_util_tax.debug('p_reporting_type_code='||p_reporting_type_code);
5964:
5965: INSERT
5966: INTO ZX_REPORTING_TYPES_B(
5967: REPORTING_TYPE_ID ,
5968: REPORTING_TYPE_CODE ,
5969: REPORTING_TYPE_DATATYPE_CODE ,
5970: TAX_REGIME_CODE ,
5988: PROGRAM_LOGIN_ID ,
5989: OBJECT_VERSION_NUMBER
5990: )
5991: SELECT
5992: zx_reporting_types_b_s.nextval, --REPORTING_TYPE_ID
5993: p_reporting_type_code, --REPORTING_TYPE_CODE
5994: p_datatype, --REPORTING_TYPE_DATATYPE
5995: NULL, --TAX REGIME CODE
5996: NULL, --TAX
6014: 1
6015:
6016: FROM DUAL
6017:
6018: WHERE NOT EXISTS (select 1 from zx_reporting_types_b
6019: where reporting_type_code= p_reporting_type_code
6020: and tax_regime_code IS NULL);
6021:
6022:
6048: | |
6049: |=========================================================================*/
6050: PROCEDURE CREATE_REPORT_TYPE_SEED
6051: (
6052: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
6053: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
6054: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
6055: )
6056: IS
6049: |=========================================================================*/
6050: PROCEDURE CREATE_REPORT_TYPE_SEED
6051: (
6052: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
6053: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
6054: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
6055: )
6056: IS
6057:
6050: PROCEDURE CREATE_REPORT_TYPE_SEED
6051: (
6052: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
6053: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
6054: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
6055: )
6056: IS
6057:
6058: BEGIN
6060: arp_util_tax.debug('CREATE_REPORT_TYPE_PTP(+)');
6061: arp_util_tax.debug('p_reporting_type_code='||p_reporting_type_code);
6062:
6063: INSERT
6064: INTO ZX_REPORTING_TYPES_B(
6065: REPORTING_TYPE_ID ,
6066: REPORTING_TYPE_CODE ,
6067: REPORTING_TYPE_DATATYPE_CODE ,
6068: TAX_REGIME_CODE ,
6086: PROGRAM_LOGIN_ID ,
6087: OBJECT_VERSION_NUMBER
6088: )
6089: SELECT
6090: zx_reporting_types_b_s.nextval, --REPORTING_TYPE_ID
6091: p_reporting_type_code, --REPORTING_TYPE_CODE
6092: p_datatype, --REPORTING_TYPE_DATATYPE
6093: NULL, --TAX REGIME CODE
6094: NULL, --TAX
6110: fnd_global.conc_program_id , -- Program Id
6111: fnd_global.conc_login_id , -- Program Login ID
6112: 1
6113: FROM DUAL
6114: WHERE NOT EXISTS (select 1 from zx_reporting_types_b
6115: where reporting_type_code= p_reporting_type_code
6116: );
6117:
6118: -- Insert into ZX_REPORTING_TYPES_TL
6194: THEN 'Korean Business Locations'
6195: ELSE types.REPORTING_TYPE_CODE END REPORTING_TYPE_NAME
6196:
6197: FROM
6198: ZX_REPORTING_TYPES_B TYPES,
6199: FND_LANGUAGES L
6200: WHERE
6201: TYPES.RECORD_TYPE_CODE = 'SEEDED'
6202: AND L.INSTALLED_FLAG in ('I', 'B')
6236: | 25-May-2006 Ashwin Gurram Created. |
6237: | |
6238: |=========================================================================*/
6239: PROCEDURE CREATE_REPORTING_CODES_SEED (
6240: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
6241: P_lookup_type IN FND_LOOKUPS.LOOKUP_TYPE%TYPE
6242: ) IS
6243:
6244: BEGIN
6295: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
6296: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
6297: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
6298: FROM
6299: ZX_REPORTING_TYPES_B report_types,
6300: FND_LOOKUPS lookups
6301: WHERE
6302: report_types.REPORTING_TYPE_CODE = p_reporting_type_code
6303: AND report_types.RECORD_TYPE_CODE = 'SEEDED'
6336: 120 ,
6337: sysdate ,
6338: 0
6339: FROM
6340: ZX_REPORTING_TYPES_B TYPES,
6341: ZX_REPORTING_CODES_B CODES,
6342: FND_LOOKUP_VALUES lookups,
6343: FND_LANGUAGES L
6344: WHERE
6413: 0,--LAST_UPDATE_LOGIN
6414: reporting_type_id ,--REPORTING_TYPE_ID
6415: 1
6416: FROM
6417: zx_reporting_types_b rep_type
6418: WHERE
6419: reporting_type_code IN
6420: (
6421: 'PT_LOCATION','PT_PRD_TAXABLE_BOX','PT_PRD_REC_TAX_BOX','PT_ANL_TTL_TAXABLE_BOX',
6451: 0,--LAST_UPDATE_LOGIN
6452: reporting_type_id ,--REPORTING_TYPE_ID
6453: 1
6454: FROM
6455: zx_reporting_types_b rep_type
6456: WHERE
6457: reporting_type_code IN ('FISCAL PRINTER','CAI NUMBER')
6458: AND record_type_code = 'SEEDED'
6459: AND NOT EXISTS ( select 1 from zx_report_types_usages
6554: NULL,
6555: report_types.REPORTING_TYPE_ID,
6556: 1
6557: FROM
6558: ZX_REPORTING_TYPES_B report_types
6559: WHERE
6560: report_types.REPORTING_TYPE_CODE = 'PT_LOCATION'
6561: AND report_types.RECORD_TYPE_CODE = 'SEEDED'
6562: AND NOT EXISTS
6605: ZX_REPORTING_CODES_B CODES,
6606: FND_LANGUAGES L
6607: WHERE
6608: CODES.RECORD_TYPE_CODE = 'SEEDED'
6609: AND codes.reporting_type_id = ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
6610: WHERE REPORTING_TYPE_CODE = 'PT_LOCATION' AND record_type_code = 'SEEDED' )
6611: AND L.INSTALLED_FLAG in ('I', 'B')
6612: AND not exists
6613: (select NULL
6715: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
6716: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
6717: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
6718: FROM
6719: ZX_REPORTING_TYPES_B report_types,
6720: JA_LOOKUPS lookups
6721: WHERE
6722: report_types.REPORTING_TYPE_CODE = 'TW_GOVERNMENT_TAX_TYPE'
6723: AND report_types.RECORD_TYPE_CODE = 'SEEDED'
6756: 120 ,
6757: sysdate ,
6758: 0
6759: FROM
6760: ZX_REPORTING_TYPES_B TYPES,
6761: ZX_REPORTING_CODES_B CODES,
6762: FND_LOOKUP_VALUES lookups,
6763: FND_LANGUAGES L
6764: WHERE
6852: SYSDATE ,
6853: fnd_global.conc_login_id,
6854: 1
6855: FROM
6856: zx_reporting_types_b types,
6857: fnd_lookups lookups
6858:
6859: WHERE
6860: types.reporting_type_code IN('PL_TAX_ORIGIN',
6897: SYSDATE ,
6898: fnd_global.conc_login_id,
6899: 1
6900: FROM
6901: zx_reporting_types_b types,
6902: fnd_lookups lookups
6903:
6904: WHERE
6905: types.reporting_type_code IN('MEMBER STATE',
6947: BEGIN
6948: arp_util_tax.debug('CREATE_REPORTING_TYPE_EMEA(+)');
6949: arp_util_tax.debug('p_reporting_type_code = '||'EMEA_VAT_REPORTING_TYPE');
6950: INSERT
6951: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
6952: where reporting_type_code= 'EMEA_VAT_REPORTING_TYPE'
6953: and tax_regime_code is NULL)
6954: ) THEN
6955: INTO ZX_REPORTING_TYPES_B(
6951: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
6952: where reporting_type_code= 'EMEA_VAT_REPORTING_TYPE'
6953: and tax_regime_code is NULL)
6954: ) THEN
6955: INTO ZX_REPORTING_TYPES_B(
6956: REPORTING_TYPE_ID ,
6957: REPORTING_TYPE_CODE ,
6958: REPORTING_TYPE_DATATYPE_CODE ,
6959: TAX_REGIME_CODE ,
6977: PROGRAM_LOGIN_ID ,
6978: OBJECT_VERSION_NUMBER
6979: )
6980: VALUES(
6981: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
6982: 'EMEA_VAT_REPORTING_TYPE' ,--REPORTING_TYPE_CODE
6983: --'VARCHAR' ,--REPORTING_TYPE_DATATYPE
6984: 'TEXT' ,--REPORTING_TYPE_DATATYPE (Bug6430516)
6985: NULL ,--TAX_REGIME_CODE
7039: SELECT
7040: TYPES.REPORTING_TYPE_ID ,
7041: L.LANGUAGE_CODE
7042: FROM
7043: ZX_REPORTING_TYPES_B TYPES,
7044: FND_LANGUAGES L
7045: WHERE TYPES.RECORD_TYPE_CODE = 'MIGRATED'
7046: AND TYPES.REPORTING_TYPE_CODE = 'EMEA_VAT_REPORTING_TYPE'
7047: AND L.INSTALLED_FLAG in ('I', 'B')
7099: SYSDATE ,--LAST_UPDATE_DATE
7100: fnd_global.conc_login_id ,--LAST_UPDATE_LOGIN
7101: 1 --OBJECT_VERSION_NUMBER
7102: FROM
7103: zx_reporting_types_b types
7104: WHERE types.reporting_type_code IN('EMEA_VAT_REPORTING_TYPE')
7105: --AND types.record_type_code = 'MIGRATED'
7106: AND NOT EXISTS ( select 1 from zx_report_types_usages
7107: where reporting_type_id = types.reporting_type_id
7186: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
7187: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
7188: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
7189: FROM
7190: ZX_REPORTING_TYPES_B report_types,
7191: FND_LOOKUPS lookups
7192: WHERE
7193: report_types.REPORTING_TYPE_CODE = 'EMEA_VAT_REPORTING_TYPE'
7194: --AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
7238: fnd_global.user_id ,
7239: sysdate ,
7240: fnd_global.conc_login_id
7241: FROM
7242: ZX_REPORTING_TYPES_B TYPES,
7243: ZX_REPORTING_CODES_B CODES,
7244: FND_LOOKUP_VALUES lookups,
7245: FND_LANGUAGES L
7246: WHERE
7332: report_codes.REPORTING_CODE_NUM_VALUE , --REPORTING_CODE_NUM_VALUE
7333: 1
7334: FROM
7335: AP_TAX_CODES_ALL codes,
7336: ZX_REPORTING_TYPES_B reporting_types,
7337: ZX_REPORTING_CODES_B report_codes,
7338: ZX_RATES_B rates,
7339: -- Bug 7620818
7340: ZX_REGIMES_B reg
7404: report_codes.REPORTING_CODE_NUM_VALUE , --REPORTING_CODE_NUM_VALUE
7405: 1
7406: FROM
7407: AR_VAT_TAX_ALL codes,
7408: ZX_REPORTING_TYPES_B reporting_types,
7409: ZX_REPORTING_CODES_B report_codes,
7410: ZX_RATES_B rates,
7411: -- Bug 7620818
7412: ZX_REGIMES_B reg
7454: BEGIN
7455: arp_util_tax.debug('CREATE_REP_ENT_REVERSE_VAT(+)');
7456: arp_util_tax.debug('p_reporting_type_code = '||'REVERSE_CHARGE_VAT');
7457:
7458: arp_util_tax.debug('Inserting into ZX_REPORTING_TYPES_B table...');
7459:
7460: INSERT WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
7461: where reporting_type_code = l_reporting_type_code
7462: and tax_regime_code = l_tax_regime_code
7456: arp_util_tax.debug('p_reporting_type_code = '||'REVERSE_CHARGE_VAT');
7457:
7458: arp_util_tax.debug('Inserting into ZX_REPORTING_TYPES_B table...');
7459:
7460: INSERT WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
7461: where reporting_type_code = l_reporting_type_code
7462: and tax_regime_code = l_tax_regime_code
7463: and tax = l_tax)
7464: ) THEN
7461: where reporting_type_code = l_reporting_type_code
7462: and tax_regime_code = l_tax_regime_code
7463: and tax = l_tax)
7464: ) THEN
7465: INTO ZX_REPORTING_TYPES_B(
7466: REPORTING_TYPE_ID ,
7467: REPORTING_TYPE_CODE ,
7468: REPORTING_TYPE_DATATYPE_CODE ,
7469: TAX_REGIME_CODE ,
7487: PROGRAM_LOGIN_ID ,
7488: OBJECT_VERSION_NUMBER
7489: )
7490: VALUES(
7491: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
7492: l_reporting_type_code ,--REPORTING_TYPE_CODE
7493: 'YES_NO' ,--REPORTING_TYPE_DATATYPE
7494: l_tax_regime_code ,--TAX_REGIME_CODE
7495: l_tax ,--TAX
7552: SELECT
7553: TYPES.REPORTING_TYPE_ID ,
7554: L.LANGUAGE_CODE
7555: FROM
7556: ZX_REPORTING_TYPES_B TYPES,
7557: FND_LANGUAGES L
7558: WHERE TYPES.RECORD_TYPE_CODE = 'MIGRATED'
7559: AND TYPES.REPORTING_TYPE_CODE = 'REVERSE_CHARGE_VAT'
7560: AND L.INSTALLED_FLAG in ('I', 'B')
7590: SYSDATE ,--LAST_UPDATE_DATE
7591: fnd_global.conc_login_id ,--LAST_UPDATE_LOGIN
7592: 1 --OBJECT_VERSION_NUMBER
7593: FROM
7594: zx_reporting_types_b types
7595: WHERE types.reporting_type_code = 'REVERSE_CHARGE_VAT'
7596: --AND types.record_type_code = 'MIGRATED'
7597: AND NOT EXISTS ( select 1 from zx_report_types_usages
7598: where reporting_type_id = types.reporting_type_id
7650: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
7651: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
7652: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
7653: FROM
7654: ZX_REPORTING_TYPES_B report_types,
7655: FND_LOOKUPS lookups
7656: WHERE
7657: report_types.REPORTING_TYPE_CODE = 'REVERSE_CHARGE_VAT'
7658: --AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
7694: fnd_global.user_id ,
7695: sysdate ,
7696: fnd_global.conc_login_id
7697: FROM
7698: ZX_REPORTING_TYPES_B TYPES,
7699: ZX_REPORTING_CODES_B CODES,
7700: FND_LOOKUP_VALUES lookups,
7701: FND_LANGUAGES L
7702: WHERE
7757: report_codes.REPORTING_CODE_NUM_VALUE , --REPORTING_CODE_NUM_VALUE
7758: 1
7759: FROM
7760: AR_VAT_TAX_ALL codes,
7761: ZX_REPORTING_TYPES_B reporting_types,
7762: ZX_REPORTING_CODES_B report_codes,
7763: ZX_RATES_B rates
7764: WHERE
7765: codes.vat_tax_id = rates.tax_rate_id