[Home] [Help]
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;
119: PROCEDURE CREATE_ZX_REPORTING_ASSOC(p_tax_id IN NUMBER) IS
120: l_status fnd_module_installations.status%TYPE;
121: l_db_status fnd_module_installations.DB_STATUS%TYPE;
122:
123: l_reporting_type_id zx_reporting_types_b.reporting_type_id%TYPE ;
124:
125: BEGIN
126: arp_util_tax.debug('CREATE_ZX_REPORTING_ASSOC(+)');
127:
137:
138: BEGIN
139: SELECT reporting_type_id
140: INTO l_reporting_type_id
141: FROM zx_reporting_types_b
142: WHERE reporting_type_code = 'REPORTING_STATUS_TRACKING';
143:
144: INSERT
145: INTO ZX_REPORT_CODES_ASSOC(
205: | |
206: | DESCRIPTION |
207: | This routine inserts data into following zx reporting entities based |
208: | on data in AP_TAX_CODES_ALL GDF attributes. |
209: | ZX_REPORTING_TYPES_B |
210: | ZX_REPORTING_TYPES_TL |
211: | ZX_REPORTING_CODES_B |
212: | ZX_REPORTING_CODES_TL |
213: | ZX_REPORT_TYPES_USAGES |
296: NULL,
297: report_types.REPORTING_TYPE_ID,
298: 1
299: FROM
300: ZX_REPORTING_TYPES_B report_types
301: WHERE
302: report_types.REPORTING_TYPE_CODE = 'PT_LOCATION'
303: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
304: AND NOT EXISTS
311:
312: -- Creation of FISCAL PRINTER Reporting Type and Reporting Usage
313: -- Bug # 3587896
314: INSERT ALL
315: WHEN ( NOT EXISTS (select 1 from zx_reporting_types_b
316: where reporting_type_code='FISCAL PRINTER'
317: and tax_regime_code = l_tax_regime_code)
318: )
319: THEN
316: where reporting_type_code='FISCAL PRINTER'
317: and tax_regime_code = l_tax_regime_code)
318: )
319: THEN
320: INTO ZX_REPORTING_TYPES_B(
321: REPORTING_TYPE_ID ,
322: REPORTING_TYPE_CODE ,
323: REPORTING_TYPE_DATATYPE_CODE ,
324: TAX_REGIME_CODE ,
342: PROGRAM_LOGIN_ID ,
343: OBJECT_VERSION_NUMBER
344: )
345: VALUES(
346: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
347: 'FISCAL PRINTER' ,--REPORTING_TYPE_CODE
348: 'YES_NO' ,--REPORTING_TYPE_DATATYPE
349: l_tax_regime_code ,--TAX_REGIME_CODE
350: NULL ,--TAX
368: 1
369: )
370: -- Creation of CAI NUMBER Reporting Type and Usage
371:
372: WHEN ( NOT EXISTS (select 1 from zx_reporting_types_b
373: where reporting_type_code='CAI NUMBER'
374: and tax_regime_code = l_tax_regime_code)
375: )
376: THEN
373: where reporting_type_code='CAI NUMBER'
374: and tax_regime_code = l_tax_regime_code)
375: )
376: THEN
377: INTO ZX_REPORTING_TYPES_B(
378: REPORTING_TYPE_ID ,
379: REPORTING_TYPE_CODE ,
380: REPORTING_TYPE_DATATYPE_CODE ,
381: TAX_REGIME_CODE ,
399: PROGRAM_LOGIN_ID ,
400: OBJECT_VERSION_NUMBER
401: )
402: VALUES(
403: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
404: 'CAI NUMBER' ,--REPORTING_TYPE_CODE
405: --'NUMBER' ,--REPORTING_TYPE_DATATYPE
406: 'TEXT' ,--REPORTING_TYPE_DATATYPE (Bug6430516)
407: l_tax_regime_code ,--TAX_REGIME_CODE
427: )
428:
429: --Bug# 3587896
430: SELECT effective_from, tax_regime_code l_tax_regime_code
431: FROM ZX_REPORTING_TYPES_B
432: WHERE REPORTING_TYPE_CODE = 'AR_DGI_TRX_CODE'; --YK:
433:
434: -----Creating reporting types for bug fix 3722296-------
435:
465: reporting_type_id ,--REPORTING_TYPE_ID
466: 1
467:
468: FROM
469: zx_reporting_types_b rep_type,
470: fnd_lookups
471:
472: WHERE
473: reporting_type_code = 'FISCAL PRINTER'
503: fnd_global.conc_login_id,--LAST_UPDATE_LOGIN
504: reporting_type_id ,--REPORTING_TYPE_ID
505: 1
506: FROM
507: zx_reporting_types_b rep_type,
508: fnd_lookups
509:
510: WHERE
511: reporting_type_code = 'CAI NUMBER'
541: SYSDATE ,
542: fnd_global.conc_login_id,
543: 1
544: FROM
545: zx_reporting_types_b types,
546: fnd_lookups lookups
547:
548: WHERE
549: types.reporting_type_code IN('PT_LOCATION' ,
620: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
621: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
622: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
623: FROM
624: ZX_REPORTING_TYPES_B report_types,
625: JA_LOOKUPS lookups
626: WHERE
627: report_types.REPORTING_TYPE_CODE = 'TW_GOVERNMENT_TAX_TYPE'
628: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
661: fnd_global.user_id ,
662: sysdate ,
663: fnd_global.conc_login_id
664: FROM
665: ZX_REPORTING_TYPES_B TYPES,
666: ZX_REPORTING_CODES_B CODES,
667: FND_LOOKUP_VALUES lookups,
668: FND_LANGUAGES L
669: WHERE
746:
747: ELSE types.REPORTING_TYPE_CODE END REPORTING_TYPE_NAME -- Bug 4886324
748:
749: FROM
750: ZX_REPORTING_TYPES_B TYPES,
751: FND_LANGUAGES L
752: WHERE
753: TYPES.RECORD_TYPE_CODE = 'MIGRATED'
754: AND L.INSTALLED_FLAG in ('I', 'B')
785: fnd_global.user_id ,
786: sysdate ,
787: fnd_global.conc_login_id
788: FROM
789: ZX_REPORTING_TYPES_B TYPES,
790: ZX_REPORTING_CODES_B CODES,
791: FND_LOOKUP_VALUES lookups,
792: FND_LANGUAGES L
793: WHERE
874:
875: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING TYPES FOR AR*/
876:
877: PROCEDURE CREATE_REPORTING_TYPE_AR(
878: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
879: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
880: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
881: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
882: p_tax_id IN NUMBER
875: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING TYPES FOR AR*/
876:
877: PROCEDURE CREATE_REPORTING_TYPE_AR(
878: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
879: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
880: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
881: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
882: p_tax_id IN NUMBER
883: )
876:
877: PROCEDURE CREATE_REPORTING_TYPE_AR(
878: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
879: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
880: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
881: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
882: p_tax_id IN NUMBER
883: )
884: IS
887:
888: arp_util_tax.debug('CREATE_REPORTING_TYPE_AR(+)');
889: arp_util_tax.debug('p_reporting_type_code = '||p_reporting_type_code);
890: INSERT ALL
891: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
892: where reporting_type_code= p_reporting_type_code
893: and tax_regime_code = l_tax_regime_code)
894: ) THEN
895: INTO ZX_REPORTING_TYPES_B(
891: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
892: where reporting_type_code= p_reporting_type_code
893: and tax_regime_code = l_tax_regime_code)
894: ) THEN
895: INTO ZX_REPORTING_TYPES_B(
896: REPORTING_TYPE_ID ,
897: REPORTING_TYPE_CODE ,
898: REPORTING_TYPE_DATATYPE_CODE ,
899: TAX_REGIME_CODE ,
917: PROGRAM_LOGIN_ID ,
918: OBJECT_VERSION_NUMBER
919: )
920: VALUES(
921: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
922: p_reporting_type_code ,--REPORTING_TYPE_CODE
923: p_datatype , --REPORTING_TYPE_DATATYPE
924: l_tax_regime_code ,
925: NULL ,--TAX
963:
964: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING TYPES FOR AP*/
965:
966: PROCEDURE CREATE_REPORTING_TYPE_AP(
967: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
968: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
969: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
970: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
971: p_tax_id IN NUMBER
964: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING TYPES FOR AP*/
965:
966: PROCEDURE CREATE_REPORTING_TYPE_AP(
967: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
968: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
969: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
970: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
971: p_tax_id IN NUMBER
972: )
965:
966: PROCEDURE CREATE_REPORTING_TYPE_AP(
967: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
968: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
969: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE,
970: P_category IN AR_VAT_TAX_ALL_B.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
971: p_tax_id IN NUMBER
972: )
973: IS
976:
977: arp_util_tax.debug('CREATE_REPORTING_TYPE_AP(+)');
978: arp_util_tax.debug('p_reporting_type_code = '||p_reporting_type_code);
979: INSERT ALL
980: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
981: where reporting_type_code= p_reporting_type_code
982: and tax_regime_code = l_tax_regime_code)
983: ) THEN
984: INTO ZX_REPORTING_TYPES_B(
980: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
981: where reporting_type_code= p_reporting_type_code
982: and tax_regime_code = l_tax_regime_code)
983: ) THEN
984: INTO ZX_REPORTING_TYPES_B(
985: REPORTING_TYPE_ID ,
986: REPORTING_TYPE_CODE ,
987: REPORTING_TYPE_DATATYPE_CODE ,
988: TAX_REGIME_CODE ,
1006: PROGRAM_LOGIN_ID ,
1007: OBJECT_VERSION_NUMBER
1008: )
1009: VALUES(
1010: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
1011: p_reporting_type_code ,--REPORTING_TYPE_CODE
1012: p_datatype , --REPORTING_TYPE_DATATYPE
1013: l_tax_regime_code ,
1014: NULL ,--TAX
1053:
1054: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE REPORTING CODES */
1055:
1056: PROCEDURE CREATE_REPORTING_CODES (
1057: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
1058: P_lookup_type IN FND_LOOKUPS.LOOKUP_TYPE%TYPE
1059: ) IS
1060:
1061: BEGIN
1112: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
1113: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
1114: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
1115: FROM
1116: ZX_REPORTING_TYPES_B report_types,
1117: FND_LOOKUPS lookups
1118: WHERE
1119: report_types.REPORTING_TYPE_CODE = p_reporting_type_code
1120: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
1153: fnd_global.user_id ,
1154: sysdate ,
1155: fnd_global.conc_login_id
1156: FROM
1157: ZX_REPORTING_TYPES_B TYPES,
1158: ZX_REPORTING_CODES_B CODES,
1159: FND_LOOKUP_VALUES lookups,
1160: FND_LANGUAGES L
1161: WHERE
1244: sysdate,
1245: fnd_global.conc_login_id,
1246: 1
1247: FROM
1248: zx_reporting_types_b types,
1249: ap_supplier_sites pvs,
1250: zx_party_tax_profile ptp
1251:
1252: WHERE
1294: sysdate,
1295: fnd_global.conc_login_id,
1296: 1
1297: FROM
1298: zx_reporting_types_b types,
1299: ap_supplier_sites pvs ,
1300: zx_party_tax_profile ptp
1301:
1302: WHERE
1426: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1427: FROM
1428: AP_TAX_CODES_ALL codes,
1429: ZX_RATES_B rates,
1430: ZX_REPORTING_TYPES_B reporting_types,
1431: ZX_REPORTING_CODES_B report_codes
1432: WHERE
1433: codes.tax_id = rates.tax_rate_id
1434: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1458: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1459: FROM
1460: AP_TAX_CODES_ALL codes,
1461: ZX_RATES_B rates,
1462: ZX_REPORTING_TYPES_B reporting_types,
1463: ZX_REPORTING_CODES_B report_codes
1464: WHERE
1465: codes.tax_id = rates.tax_rate_id
1466: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1489: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1490: FROM
1491: AP_TAX_CODES_ALL codes,
1492: ZX_RATES_B rates,
1493: ZX_REPORTING_TYPES_B reporting_types,
1494: ZX_REPORTING_CODES_B report_codes
1495: WHERE
1496: codes.tax_id = rates.tax_rate_id
1497: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1520: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1521: FROM
1522: AP_TAX_CODES_ALL codes,
1523: ZX_RATES_B rates,
1524: ZX_REPORTING_TYPES_B reporting_types,
1525: ZX_REPORTING_CODES_B report_codes
1526: WHERE
1527: codes.tax_id = rates.tax_rate_id
1528: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1551: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1552: FROM
1553: AP_TAX_CODES_ALL codes,
1554: ZX_RATES_B rates,
1555: ZX_REPORTING_TYPES_B reporting_types,
1556: ZX_REPORTING_CODES_B report_codes
1557: WHERE
1558: codes.tax_id = rates.tax_rate_id
1559: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1582: report_codes.REPORTING_CODE_NUM_VALUE REPORTING_CODE_NUM_VALUE
1583: FROM
1584: AP_TAX_CODES_ALL codes,
1585: ZX_RATES_B rates,
1586: ZX_REPORTING_TYPES_B reporting_types,
1587: ZX_REPORTING_CODES_B report_codes
1588: WHERE
1589: codes.tax_id = rates.tax_rate_id
1590: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1616: NULL REPORTING_CODE_NUM_VALUE
1617: FROM
1618: AP_TAX_CODES_ALL codes,
1619: ZX_RATES_B rates,
1620: ZX_REPORTING_TYPES_B reporting_types
1621: -- ZX_REPORTING_CODES_B report_codes
1622: WHERE
1623: codes.tax_id = rates.tax_rate_id
1624: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1646: NULL REPORTING_CODE_NUM_VALUE
1647: FROM
1648: AP_TAX_CODES_ALL codes,
1649: ZX_RATES_B rates,
1650: ZX_REPORTING_TYPES_B reporting_types
1651: -- ZX_REPORTING_CODES_B report_codes
1652: WHERE
1653: codes.tax_id = rates.tax_rate_id
1654: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1677: NULL REPORTING_CODE_NUM_VALUE
1678: FROM
1679: AP_TAX_CODES_ALL codes,
1680: ZX_RATES_B rates,
1681: ZX_REPORTING_TYPES_B reporting_types
1682: -- ZX_REPORTING_CODES_B report_codes
1683: WHERE
1684: codes.tax_id = rates.tax_rate_id
1685: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1708: NULL REPORTING_CODE_NUM_VALUE
1709: FROM
1710: AP_TAX_CODES_ALL codes,
1711: ZX_RATES_B rates,
1712: ZX_REPORTING_TYPES_B reporting_types
1713: -- ZX_REPORTING_CODES_B report_codes
1714: WHERE
1715: codes.tax_id = rates.tax_rate_id
1716: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1739: NULL REPORTING_CODE_NUM_VALUE
1740: FROM
1741: AP_TAX_CODES_ALL codes,
1742: ZX_RATES_B rates,
1743: ZX_REPORTING_TYPES_B reporting_types
1744: -- ZX_REPORTING_CODES_B report_codes
1745: WHERE
1746:
1747: codes.tax_id = rates.tax_rate_id
1773:
1774: FROM
1775: AP_TAX_CODES_ALL codes,
1776: ZX_RATES_B rates,
1777: ZX_REPORTING_TYPES_B reporting_types
1778: -- ZX_REPORTING_CODES_B report_codes
1779: WHERE
1780: codes.tax_id = rates.tax_rate_id
1781: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
1834: fnd_global.conc_login_id,
1835: 1 -- Bug 5031787
1836: FROM
1837: financials_system_params_all fin_sys_param,
1838: zx_reporting_types_b types,
1839: zx_reporting_codes_b codes,
1840: xle_etb_profiles etb,
1841: zx_party_tax_profile ptp
1842:
1890: fnd_global.conc_login_id,
1891: 1 -- Bug 5031787
1892: FROM
1893: financials_system_params_all fin_sys_param,
1894: zx_reporting_types_b types,
1895: zx_reporting_codes_b codes,
1896: xle_etb_profiles etb,
1897: zx_party_tax_profile ptp
1898:
1953: report_codes.REPORTING_CODE_NUM_VALUE , --REPORTING_CODE_NUM_VALUE
1954: 1
1955: FROM
1956: AP_TAX_CODES_ALL codes,
1957: ZX_REPORTING_TYPES_B reporting_types,
1958: ZX_REPORTING_CODES_B report_codes,
1959: ZX_RATES_B rates
1960:
1961:
2044: FROM
2045:
2046: AR_VAT_TAX_ALL_B codes,
2047: ZX_RATES_B rates,
2048: ZX_REPORTING_TYPES_B report_types,
2049: ZX_REPORTING_CODES_B report_codes
2050:
2051: WHERE
2052:
2082: FROM
2083:
2084: AR_VAT_TAX_ALL_B codes,
2085: ZX_RATES_B rates,
2086: ZX_REPORTING_TYPES_B report_types,
2087: ZX_REPORTING_CODES_B report_codes
2088:
2089: WHERE
2090:
2117: FROM
2118:
2119: AR_VAT_TAX_ALL_B codes,
2120: ZX_RATES_B rates,
2121: ZX_REPORTING_TYPES_B report_types,
2122: ZX_REPORTING_CODES_B report_codes
2123:
2124: WHERE
2125:
2152: FROM
2153:
2154: AR_VAT_TAX_ALL_B codes,
2155: ZX_RATES_B rates,
2156: ZX_REPORTING_TYPES_B report_types,
2157: ZX_REPORTING_CODES_B report_codes
2158:
2159: WHERE
2160:
2187: FROM
2188:
2189: AR_VAT_TAX_ALL_B codes,
2190: ZX_RATES_B rates,
2191: ZX_REPORTING_TYPES_B report_types,
2192: ZX_REPORTING_CODES_B report_codes
2193:
2194: WHERE
2195:
2222:
2223: FROM
2224: AR_VAT_TAX_ALL_B codes,
2225: ZX_RATES_B rates,
2226: ZX_REPORTING_TYPES_B report_types
2227: WHERE
2228: codes.vat_tax_id = rates.tax_rate_id
2229: AND codes.vat_tax_id = nvl(p_tax_id,codes.vat_tax_id)
2230: AND codes.global_attribute_category = 'JL.CL.ARXSUVAT.VAT_TAX'
2256: FROM
2257:
2258: AR_VAT_TAX_ALL_B codes,
2259: ZX_RATES_B rates,
2260: ZX_REPORTING_TYPES_B report_types
2261: -- ZX_REPORTING_CODES_B report_codes
2262:
2263: WHERE
2264:
2291: FROM
2292:
2293: AR_VAT_TAX_ALL_B codes,
2294: ZX_RATES_B rates,
2295: ZX_REPORTING_TYPES_B report_types
2296: -- ZX_REPORTING_CODES_B report_codes
2297:
2298: WHERE
2299:
2326: FROM
2327:
2328: AR_VAT_TAX_ALL_B codes,
2329: ZX_RATES_B rates,
2330: ZX_REPORTING_TYPES_B report_types
2331: -- ZX_REPORTING_CODES_B report_codes
2332:
2333: WHERE
2334:
2363: FROM
2364:
2365: AR_VAT_TAX_ALL_B codes,
2366: ZX_RATES_B rates,
2367: ZX_REPORTING_TYPES_B report_types
2368: -- ZX_REPORTING_CODES_B report_codes
2369:
2370: WHERE
2371:
2398: FROM
2399:
2400: AR_VAT_TAX_ALL_B codes,
2401: ZX_RATES_B rates,
2402: ZX_REPORTING_TYPES_B report_types
2403: -- ZX_REPORTING_CODES_B report_codes
2404:
2405: WHERE
2406:
2434: FROM
2435:
2436: AR_VAT_TAX_ALL_B codes,
2437: ZX_RATES_B rates,
2438: ZX_REPORTING_TYPES_B report_types
2439: -- ZX_REPORTING_CODES_B report_codes
2440:
2441: WHERE
2442:
2470: FROM
2471:
2472: AR_VAT_TAX_ALL_B codes,
2473: ZX_RATES_B rates,
2474: ZX_REPORTING_TYPES_B report_types,
2475: ZX_REPORTING_CODES_B report_codes
2476:
2477: WHERE
2478:
2507: FROM
2508:
2509: AR_VAT_TAX_ALL_B codes,
2510: ZX_RATES_B rates,
2511: ZX_REPORTING_TYPES_B report_types,
2512: ZX_REPORTING_CODES_B report_codes
2513:
2514: WHERE
2515:
2544: FROM
2545:
2546: AR_VAT_TAX_ALL_B codes,
2547: ZX_RATES_B rates,
2548: ZX_REPORTING_TYPES_B report_types,
2549: ZX_REPORTING_CODES_B report_codes
2550:
2551: WHERE
2552:
2581: FROM
2582:
2583: AR_VAT_TAX_ALL_B codes,
2584: ZX_RATES_B rates,
2585: ZX_REPORTING_TYPES_B report_types,
2586: ZX_REPORTING_CODES_B report_codes
2587:
2588: WHERE
2589:
2618: FROM
2619:
2620: AR_VAT_TAX_ALL_B codes,
2621: ZX_RATES_B rates,
2622: ZX_REPORTING_TYPES_B report_types,
2623: ZX_REPORTING_CODES_B report_codes
2624:
2625: WHERE
2626:
2655: report_codes.REPORTING_CODE_NUM_VALUE
2656: FROM
2657: AR_VAT_TAX_ALL_TL codes,
2658: ZX_RATES_B rates,
2659: ZX_REPORTING_TYPES_B report_types,
2660: ZX_REPORTING_CODES_B report_codes
2661:
2662: WHERE
2663:
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
2753: REPORTING_TYPE_ID,
2754: rates.EFFECTIVE_FROM,
2755: codes.global_attribute6 REPORTING_CODE_CHAR_VALUE
2756: FROM
2757: ZX_REPORTING_TYPES_B report_types,
2758: AR_VAT_TAX_ALL_B codes,
2759: ZX_RATES_B rates
2760:
2761: WHERE
2781: REPORTING_TYPE_ID,
2782: rates.EFFECTIVE_FROM,
2783: codes.global_attribute2 REPORTING_CODE_CHAR_VALUE
2784: FROM
2785: ZX_REPORTING_TYPES_B report_types,
2786: AR_VAT_TAX_ALL_B codes,
2787: ZX_RATES_B rates
2788:
2789: WHERE
2809: REPORTING_TYPE_ID,
2810: rates.EFFECTIVE_FROM,
2811: codes.global_attribute2 REPORTING_CODE_CHAR_VALUE
2812: FROM
2813: ZX_REPORTING_TYPES_B report_types,
2814: AR_VAT_TAX_ALL_B codes,
2815: ZX_RATES_B rates
2816:
2817: WHERE
2838: REPORTING_TYPE_ID,
2839: rates.EFFECTIVE_FROM,
2840: codes.global_attribute2 REPORTING_CODE_CHAR_VALUE
2841: FROM
2842: ZX_REPORTING_TYPES_B report_types,
2843: AR_VAT_TAX_ALL_B codes,
2844: ZX_RATES_B rates
2845:
2846: WHERE
2939: arp_util_tax.debug('CREATE_ZX_REP_TYPE_CODES_AP(+)');
2940: END IF;
2941:
2942: INSERT ALL
2943: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
2944: where reporting_type_code='PT_LOCATION'
2945: and tax_regime_code = l_tax_regime_code)
2946: ) THEN
2947: INTO ZX_REPORTING_TYPES_B(
2943: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
2944: where reporting_type_code='PT_LOCATION'
2945: and tax_regime_code = l_tax_regime_code)
2946: ) THEN
2947: INTO ZX_REPORTING_TYPES_B(
2948: REPORTING_TYPE_ID ,
2949: REPORTING_TYPE_CODE ,
2950: REPORTING_TYPE_DATATYPE_CODE ,
2951: TAX_REGIME_CODE ,
2970: PROGRAM_LOGIN_ID ,
2971: OBJECT_VERSION_NUMBER
2972: )
2973: VALUES(
2974: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
2975: 'PT_LOCATION' ,--REPORTING_TYPE_CODE
2976: 'TEXT' ,--REPORTING_TYPE_DATATYPE
2977: l_tax_regime_code ,
2978: NULL ,--TAX
2994: fnd_global.conc_program_id , -- Program Id
2995: fnd_global.conc_login_id , -- Program Login ID
2996: 1
2997: )
2998: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
2999: where reporting_type_code='PT_PRD_TAXABLE_BOX'
3000: and tax_regime_code = l_tax_regime_code)
3001: ) THEN
3002: INTO ZX_REPORTING_TYPES_B(
2998: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
2999: where reporting_type_code='PT_PRD_TAXABLE_BOX'
3000: and tax_regime_code = l_tax_regime_code)
3001: ) THEN
3002: INTO ZX_REPORTING_TYPES_B(
3003: REPORTING_TYPE_ID ,
3004: REPORTING_TYPE_CODE ,
3005: REPORTING_TYPE_DATATYPE_CODE ,
3006: TAX_REGIME_CODE ,
3025: PROGRAM_LOGIN_ID ,
3026: OBJECT_VERSION_NUMBER
3027: )
3028: VALUES(
3029: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
3030: 'PT_PRD_TAXABLE_BOX' ,--REPORTING_TYPE_CODE
3031: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3032: l_tax_regime_code ,
3033: NULL ,--TAX
3049: fnd_global.conc_program_id , -- Program Id
3050: fnd_global.conc_login_id ,-- Program Login ID
3051: 1
3052: )
3053: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3054: where reporting_type_code='PT_PRD_REC_TAX_BOX'
3055: and tax_regime_code = l_tax_regime_code)
3056: ) THEN
3057: INTO ZX_REPORTING_TYPES_B(
3053: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3054: where reporting_type_code='PT_PRD_REC_TAX_BOX'
3055: and tax_regime_code = l_tax_regime_code)
3056: ) THEN
3057: INTO ZX_REPORTING_TYPES_B(
3058: REPORTING_TYPE_ID ,
3059: REPORTING_TYPE_CODE ,
3060: REPORTING_TYPE_DATATYPE_CODE ,
3061: TAX_REGIME_CODE ,
3080: PROGRAM_LOGIN_ID ,
3081: OBJECT_VERSION_NUMBER
3082: )
3083: VALUES(
3084: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3085: 'PT_PRD_REC_TAX_BOX' ,--REPORTING_TYPE_CODE
3086: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3087: l_tax_regime_code ,
3088: NULL ,--TAX
3104: fnd_global.conc_program_id , -- Program Id
3105: fnd_global.conc_login_id , -- Program Login ID
3106: 1
3107: )
3108: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3109: where reporting_type_code='PT_ANL_TTL_TAXABLE_BOX'
3110: and tax_regime_code = l_tax_regime_code)
3111: ) THEN
3112: INTO ZX_REPORTING_TYPES_B(
3108: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3109: where reporting_type_code='PT_ANL_TTL_TAXABLE_BOX'
3110: and tax_regime_code = l_tax_regime_code)
3111: ) THEN
3112: INTO ZX_REPORTING_TYPES_B(
3113: REPORTING_TYPE_ID ,
3114: REPORTING_TYPE_CODE ,
3115: REPORTING_TYPE_DATATYPE_CODE ,
3116: TAX_REGIME_CODE ,
3135: PROGRAM_LOGIN_ID ,
3136: OBJECT_VERSION_NUMBER
3137: )
3138: VALUES(
3139: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3140: 'PT_ANL_TTL_TAXABLE_BOX' ,--REPORTING_TYPE_CODE
3141: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3142: l_tax_regime_code ,
3143: NULL ,--TAX
3159: fnd_global.conc_program_id , -- Program Id
3160: fnd_global.conc_login_id , -- Program Login ID
3161: 1
3162: )
3163: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3164: where reporting_type_code='PT_ANL_REC_TAXABLE'
3165: and tax_regime_code = l_tax_regime_code)
3166: ) THEN
3167: INTO ZX_REPORTING_TYPES_B(
3163: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3164: where reporting_type_code='PT_ANL_REC_TAXABLE'
3165: and tax_regime_code = l_tax_regime_code)
3166: ) THEN
3167: INTO ZX_REPORTING_TYPES_B(
3168: REPORTING_TYPE_ID ,
3169: REPORTING_TYPE_CODE ,
3170: REPORTING_TYPE_DATATYPE_CODE ,
3171: TAX_REGIME_CODE ,
3190: PROGRAM_LOGIN_ID ,
3191: OBJECT_VERSION_NUMBER
3192: )
3193: VALUES(
3194: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3195: 'PT_ANL_REC_TAXABLE' ,--REPORTING_TYPE_CODE
3196: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3197: l_tax_regime_code ,
3198: NULL ,--TAX
3214: fnd_global.conc_program_id , -- Program Id
3215: fnd_global.conc_login_id , -- Program Login ID
3216: 1
3217: )
3218: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3219: where reporting_type_code='PT_ANL_NON_REC_TAXABLE'
3220: and tax_regime_code = l_tax_regime_code)
3221: ) THEN
3222: INTO ZX_REPORTING_TYPES_B(
3218: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3219: where reporting_type_code='PT_ANL_NON_REC_TAXABLE'
3220: and tax_regime_code = l_tax_regime_code)
3221: ) THEN
3222: INTO ZX_REPORTING_TYPES_B(
3223: REPORTING_TYPE_ID ,
3224: REPORTING_TYPE_CODE ,
3225: REPORTING_TYPE_DATATYPE_CODE ,
3226: TAX_REGIME_CODE ,
3245: PROGRAM_LOGIN_ID ,
3246: OBJECT_VERSION_NUMBER
3247: )
3248: VALUES(
3249: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3250: 'PT_ANL_NON_REC_TAXABLE' ,--REPORTING_TYPE_CODE
3251: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3252: l_tax_regime_code ,
3253: NULL ,--TAX
3269: fnd_global.conc_program_id , -- Program Id
3270: fnd_global.conc_login_id , -- Program Login ID
3271: 1
3272: )
3273: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3274: where reporting_type_code='PT_ANL_REC_TAX_BOX'
3275: and tax_regime_code = l_tax_regime_code)
3276: ) THEN
3277: INTO ZX_REPORTING_TYPES_B(
3273: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3274: where reporting_type_code='PT_ANL_REC_TAX_BOX'
3275: and tax_regime_code = l_tax_regime_code)
3276: ) THEN
3277: INTO ZX_REPORTING_TYPES_B(
3278: REPORTING_TYPE_ID ,
3279: REPORTING_TYPE_CODE ,
3280: REPORTING_TYPE_DATATYPE_CODE ,
3281: TAX_REGIME_CODE ,
3300: PROGRAM_LOGIN_ID ,
3301: OBJECT_VERSION_NUMBER
3302: )
3303: VALUES(
3304: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
3305: 'PT_ANL_REC_TAX_BOX' ,--REPORTING_TYPE_CODE
3306: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3307: l_tax_regime_code ,
3308: NULL ,--TAX
3345: CREATE_REPORTING_TYPE_AP('AR_DGI_TRX_CODE','TEXT','Y', 'JL.AR.APXTADTC.VAT', p_tax_id);
3346:
3347: /*
3348: INSERT ALL
3349: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3350: where reporting_type_code='AR_DGI_TRX_CODE'
3351: and tax_regime_code = l_tax_regime_code)
3352: ) THEN
3353: INTO ZX_REPORTING_TYPES_B(
3349: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3350: where reporting_type_code='AR_DGI_TRX_CODE'
3351: and tax_regime_code = l_tax_regime_code)
3352: ) THEN
3353: INTO ZX_REPORTING_TYPES_B(
3354: REPORTING_TYPE_ID ,
3355: REPORTING_TYPE_CODE ,
3356: REPORTING_TYPE_DATATYPE_CODE ,
3357: TAX_REGIME_CODE ,
3375: PROGRAM_LOGIN_ID ,
3376: OBJECT_VERSION_NUMBER
3377: )
3378: VALUES(
3379: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
3380: 'AR_DGI_TRX_CODE' ,--REPORTING_TYPE_CODE
3381: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3382: l_tax_regime_code ,
3383: NULL ,--TAX
3420:
3421: --Bug Fix : 4928369
3422: /*
3423: INSERT ALL
3424: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3425: where reporting_type_code='CL_TAX_CODE_CLASSIF'
3426: and tax_regime_code = l_tax_regime_code)
3427: ) THEN
3428: INTO ZX_REPORTING_TYPES_B(
3424: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3425: where reporting_type_code='CL_TAX_CODE_CLASSIF'
3426: and tax_regime_code = l_tax_regime_code)
3427: ) THEN
3428: INTO ZX_REPORTING_TYPES_B(
3429: REPORTING_TYPE_ID ,
3430: REPORTING_TYPE_CODE ,
3431: REPORTING_TYPE_DATATYPE_CODE ,
3432: TAX_REGIME_CODE ,
3450: PROGRAM_LOGIN_ID ,
3451: OBJECT_VERSION_NUMBER
3452: )
3453: VALUES(
3454: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
3455: 'CL_TAX_CODE_CLASSIF' ,--REPORTING_TYPE_CODE
3456: 'TEXT' ,--REPORTING_TYPE_DATATYPE
3457: l_tax_regime_code ,
3458: NULL ,--TAX
3557: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID
3558: FROM
3559:
3560: FINANCIALS_SYSTEM_PARAMS_ALL fin_sys_param,
3561: ZX_REPORTING_TYPES_B report_types
3562:
3563: WHERE
3564:
3565: report_types.REPORTING_TYPE_CODE = 'MEMBER STATE'
3632: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3633:
3634: FROM
3635:
3636: ZX_REPORTING_TYPES_B report_types,
3637: FND_LOOKUPS lookups
3638:
3639: WHERE
3640:
3705: types.effective_from effective_from,
3706: types.reporting_type_id reporting_type_id,
3707: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3708: FROM
3709: zx_reporting_types_b types,
3710: ap_tax_codes_all codes
3711:
3712: WHERE
3713: types.reporting_type_code = 'PT_PRD_TAXABLE_BOX'
3731: types.reporting_type_id reporting_type_id,
3732: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3733:
3734: FROM
3735: zx_reporting_types_b types,
3736: ap_tax_codes_all codes
3737:
3738: WHERE
3739: types.reporting_type_code = 'PT_PRD_REC_TAX_BOX'
3757: types.effective_from effective_from,
3758: types.reporting_type_id reporting_type_id,
3759: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3760: FROM
3761: zx_reporting_types_b types,
3762: ap_tax_codes_all codes
3763:
3764: WHERE
3765: types.reporting_type_code = 'PT_ANL_TTL_TAXABLE_BOX'
3782: types.effective_from effective_from,
3783: types.reporting_type_id reporting_type_id,
3784: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3785: FROM
3786: zx_reporting_types_b types,
3787: ap_tax_codes_all codes
3788:
3789: WHERE
3790: types.reporting_type_code = 'PT_ANL_REC_TAXABLE'
3807: types.effective_from effective_from,
3808: types.reporting_type_id reporting_type_id,
3809: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3810: FROM
3811: zx_reporting_types_b types,
3812: ap_tax_codes_all codes
3813:
3814: WHERE
3815: types.reporting_type_code = 'PT_ANL_NON_REC_TAXABLE'
3833: types.effective_from effective_from,
3834: types.reporting_type_id reporting_type_id,
3835: types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
3836: FROM
3837: zx_reporting_types_b types,
3838: ap_tax_codes_all codes
3839:
3840: WHERE
3841: types.reporting_type_code = 'PT_ANL_REC_TAX_BOX'
3857: -- Bug Fix 4671652
3858: -- Creation of reporting types for KR_BUSINESS_LOCATIONS
3859:
3860: INSERT ALL
3861: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
3862: where reporting_type_code= 'KR_BUSINESS_LOCATIONS'
3863: and tax_regime_code = l_tax_regime_code
3864: )
3865: ) THEN
3862: where reporting_type_code= 'KR_BUSINESS_LOCATIONS'
3863: and tax_regime_code = l_tax_regime_code
3864: )
3865: ) THEN
3866: INTO ZX_REPORTING_TYPES_B(
3867: REPORTING_TYPE_ID ,
3868: REPORTING_TYPE_CODE ,
3869: REPORTING_TYPE_DATATYPE_CODE ,
3870: TAX_REGIME_CODE ,
3888: PROGRAM_LOGIN_ID ,
3889: OBJECT_VERSION_NUMBER
3890: )
3891: VALUES(
3892: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
3893: 'KR_BUSINESS_LOCATIONS' ,--REPORTING_TYPE_CODE
3894: --'VARCHAR' ,--REPORTING_TYPE_DATATYPE
3895: 'TEXT' ,--REPORTING_TYPE_DATATYPE (Bug6430516)
3896: l_tax_regime_code ,--TAX_REGIME_CODE
3973: fnd_global.conc_login_id , -- Program Login ID
3974: l_location_code,
3975: NULL,
3976: NULL,
3977: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
3978: and reporting_type_code = 'KR_BUSINESS_LOCATIONS'),
3979: 1)
3980:
3981: select
3995: and tax_id_values_and_codes.tax_id = nvl(p_tax_id,tax_id_values_and_codes.tax_id)
3996: and tax_id_values_and_codes.location_code is not null
3997: and not exists
3998: ( select 1 from zx_reporting_codes_b where reporting_type_id =
3999: (select reporting_type_id FROM zx_reporting_types_b
4000: WHERE tax_regime_code = zxrb.tax_regime_code
4001: AND reporting_type_code = 'KR_BUSINESS_LOCATIONS')
4002: AND reporting_code_char_value = tax_id_values_and_codes.location_code);
4003:
4057: FROM
4058: AP_TAX_CODES_ALL codes,
4059: HR_LOCATIONS hrloc,
4060: ZX_RATES_B rates,
4061: ZX_REPORTING_TYPES_B reporting_types,
4062: ZX_REPORTING_CODES_B report_codes
4063: WHERE
4064: codes.tax_id = rates.tax_rate_id
4065: AND codes.tax_id = nvl(p_tax_id,codes.tax_id)
4125: reporting types. But in the below insert all we are using same context for all the reporting types.
4126: Hence we are using bulk insert only for the same context case. */
4127:
4128: INSERT ALL
4129: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4130: where reporting_type_code='PT_LOCATION'
4131: and tax_regime_code = l_tax_regime_code)
4132: ) THEN
4133: INTO
4130: where reporting_type_code='PT_LOCATION'
4131: and tax_regime_code = l_tax_regime_code)
4132: ) THEN
4133: INTO
4134: ZX_REPORTING_TYPES_B(
4135: REPORTING_TYPE_ID ,
4136: REPORTING_TYPE_CODE ,
4137: REPORTING_TYPE_DATATYPE_CODE ,
4138: TAX_REGIME_CODE ,
4157: PROGRAM_LOGIN_ID ,
4158: OBJECT_VERSION_NUMBER
4159: )
4160: VALUES(
4161: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s'),--REPORTING_TYPE_ID
4162: 'PT_LOCATION' ,--REPORTING_TYPE_CODE
4163: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4164: l_tax_regime_code ,
4165: NULL ,--TAX
4181: fnd_global.conc_program_id , -- Program Id
4182: fnd_global.conc_login_id , -- Program Login ID
4183: 1
4184: )
4185: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4186: where reporting_type_code='PT_PRD_TAXABLE_BOX'
4187: and tax_regime_code = l_tax_regime_code)
4188: ) THEN
4189: INTO ZX_REPORTING_TYPES_B(
4185: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4186: where reporting_type_code='PT_PRD_TAXABLE_BOX'
4187: and tax_regime_code = l_tax_regime_code)
4188: ) THEN
4189: INTO ZX_REPORTING_TYPES_B(
4190: REPORTING_TYPE_ID ,
4191: REPORTING_TYPE_CODE ,
4192: REPORTING_TYPE_DATATYPE_CODE ,
4193: TAX_REGIME_CODE ,
4212: PROGRAM_LOGIN_ID ,
4213: OBJECT_VERSION_NUMBER
4214: )
4215: VALUES(
4216: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4217: 'PT_PRD_TAXABLE_BOX' ,--REPORTING_TYPE_CODE
4218: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4219: l_tax_regime_code ,
4220: NULL ,--TAX
4236: fnd_global.conc_program_id , -- Program Id
4237: fnd_global.conc_login_id , -- Program Login ID
4238: 1
4239: )
4240: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4241: where reporting_type_code='PT_PRD_REC_TAX_BOX'
4242: and tax_regime_code = l_tax_regime_code)
4243: ) THEN
4244: INTO ZX_REPORTING_TYPES_B(
4240: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4241: where reporting_type_code='PT_PRD_REC_TAX_BOX'
4242: and tax_regime_code = l_tax_regime_code)
4243: ) THEN
4244: INTO ZX_REPORTING_TYPES_B(
4245: REPORTING_TYPE_ID ,
4246: REPORTING_TYPE_CODE ,
4247: REPORTING_TYPE_DATATYPE_CODE ,
4248: TAX_REGIME_CODE ,
4267: PROGRAM_LOGIN_ID ,
4268: OBJECT_VERSION_NUMBER
4269: )
4270: VALUES(
4271: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4272: 'PT_PRD_REC_TAX_BOX' ,--REPORTING_TYPE_CODE
4273: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4274: l_tax_regime_code ,
4275: NULL ,--TAX
4291: fnd_global.conc_program_id , -- Program Id
4292: fnd_global.conc_login_id , -- Program Login ID
4293: 1
4294: )
4295: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4296: where reporting_type_code='PT_ANL_TTL_TAXABLE_BOX'
4297: and tax_regime_code = l_tax_regime_code)
4298: ) THEN
4299: INTO ZX_REPORTING_TYPES_B(
4295: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4296: where reporting_type_code='PT_ANL_TTL_TAXABLE_BOX'
4297: and tax_regime_code = l_tax_regime_code)
4298: ) THEN
4299: INTO ZX_REPORTING_TYPES_B(
4300: REPORTING_TYPE_ID ,
4301: REPORTING_TYPE_CODE ,
4302: REPORTING_TYPE_DATATYPE_CODE ,
4303: TAX_REGIME_CODE ,
4322: PROGRAM_LOGIN_ID ,
4323: OBJECT_VERSION_NUMBER
4324: )
4325: VALUES(
4326: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4327: 'PT_ANL_TTL_TAXABLE_BOX' ,--REPORTING_TYPE_CODE
4328: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4329: l_tax_regime_code ,
4330: NULL ,--TAX
4346: fnd_global.conc_program_id , -- Program Id
4347: fnd_global.conc_login_id , -- Program Login ID
4348: 1
4349: )
4350: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4351: where reporting_type_code='PT_ANL_REC_TAXABLE' -- Bug 5031787
4352: and tax_regime_code = l_tax_regime_code)
4353: ) THEN
4354: INTO ZX_REPORTING_TYPES_B(
4350: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4351: where reporting_type_code='PT_ANL_REC_TAXABLE' -- Bug 5031787
4352: and tax_regime_code = l_tax_regime_code)
4353: ) THEN
4354: INTO ZX_REPORTING_TYPES_B(
4355: REPORTING_TYPE_ID ,
4356: REPORTING_TYPE_CODE ,
4357: REPORTING_TYPE_DATATYPE_CODE ,
4358: TAX_REGIME_CODE ,
4377: PROGRAM_LOGIN_ID ,
4378: OBJECT_VERSION_NUMBER
4379: )
4380: VALUES(
4381: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4382: 'PT_ANL_REC_TAXABLE' ,--REPORTING_TYPE_CODE
4383: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4384: l_tax_regime_code ,
4385: NULL ,--TAX
4401: fnd_global.conc_program_id , -- Program Id
4402: fnd_global.conc_login_id , -- Program Login ID
4403: 1
4404: )
4405: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4406: where reporting_type_code='PT_ANL_NON_REC_TAXABLE'
4407: and tax_regime_code = l_tax_regime_code)
4408: ) THEN
4409: INTO ZX_REPORTING_TYPES_B(
4405: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4406: where reporting_type_code='PT_ANL_NON_REC_TAXABLE'
4407: and tax_regime_code = l_tax_regime_code)
4408: ) THEN
4409: INTO ZX_REPORTING_TYPES_B(
4410: REPORTING_TYPE_ID ,
4411: REPORTING_TYPE_CODE ,
4412: REPORTING_TYPE_DATATYPE_CODE ,
4413: TAX_REGIME_CODE ,
4432: PROGRAM_LOGIN_ID ,
4433: OBJECT_VERSION_NUMBER
4434: )
4435: VALUES(
4436: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4437: 'PT_ANL_NON_REC_TAXABLE' ,--REPORTING_TYPE_CODE
4438: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4439: l_tax_regime_code ,
4440: NULL ,--TAX
4456: fnd_global.conc_program_id , -- Program Id
4457: fnd_global.conc_login_id , -- Program Login ID
4458: 1
4459: )
4460: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4461: where reporting_type_code='PT_ANL_REC_TAX_BOX' -- Bug 5031787
4462: and tax_regime_code = l_tax_regime_code)
4463: ) THEN
4464: INTO ZX_REPORTING_TYPES_B(
4460: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4461: where reporting_type_code='PT_ANL_REC_TAX_BOX' -- Bug 5031787
4462: and tax_regime_code = l_tax_regime_code)
4463: ) THEN
4464: INTO ZX_REPORTING_TYPES_B(
4465: REPORTING_TYPE_ID ,
4466: REPORTING_TYPE_CODE ,
4467: REPORTING_TYPE_DATATYPE_CODE ,
4468: TAX_REGIME_CODE ,
4487: PROGRAM_LOGIN_ID ,
4488: OBJECT_VERSION_NUMBER
4489: )
4490: VALUES(
4491: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4492: 'PT_ANL_REC_TAX_BOX' ,--REPORTING_TYPE_CODE
4493: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4494: l_tax_regime_code ,
4495: NULL ,--TAX
4529: zx_rates.tax_regime_code;
4530:
4531:
4532: INSERT ALL
4533: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4534: where reporting_type_code='AR_DGI_TRX_CODE'
4535: and tax_regime_code = l_tax_regime_code)
4536: ) THEN
4537: INTO ZX_REPORTING_TYPES_B(
4533: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4534: where reporting_type_code='AR_DGI_TRX_CODE'
4535: and tax_regime_code = l_tax_regime_code)
4536: ) THEN
4537: INTO ZX_REPORTING_TYPES_B(
4538: REPORTING_TYPE_ID ,
4539: REPORTING_TYPE_CODE ,
4540: REPORTING_TYPE_DATATYPE_CODE ,
4541: TAX_REGIME_CODE ,
4559: PROGRAM_LOGIN_ID ,
4560: OBJECT_VERSION_NUMBER
4561: )
4562: VALUES(
4563: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4564: 'AR_DGI_TRX_CODE' ,--REPORTING_TYPE_CODE
4565: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4566: l_tax_regime_code ,
4567: NULL ,--TAX
4584: fnd_global.conc_login_id , -- Program Login ID
4585: 1
4586: )
4587:
4588: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4589: where reporting_type_code='AR_MUNICIPAL_JUR'
4590: and tax_regime_code = l_tax_regime_code)
4591: ) THEN
4592: INTO ZX_REPORTING_TYPES_B(
4588: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4589: where reporting_type_code='AR_MUNICIPAL_JUR'
4590: and tax_regime_code = l_tax_regime_code)
4591: ) THEN
4592: INTO ZX_REPORTING_TYPES_B(
4593: REPORTING_TYPE_ID ,
4594: REPORTING_TYPE_CODE ,
4595: REPORTING_TYPE_DATATYPE_CODE ,
4596: TAX_REGIME_CODE ,
4614: PROGRAM_LOGIN_ID ,
4615: OBJECT_VERSION_NUMBER
4616: )
4617: VALUES(
4618: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4619: 'AR_MUNICIPAL_JUR' ,--REPORTING_TYPE_CODE
4620: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4621: l_tax_regime_code ,
4622: NULL ,--TAX
4639: fnd_global.conc_login_id , -- Program Login ID
4640: 1
4641: )
4642:
4643: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4644: where reporting_type_code='AR_TURN_OVER_JUR_CODE'
4645: and tax_regime_code = l_tax_regime_code)
4646: ) THEN
4647: INTO ZX_REPORTING_TYPES_B(
4643: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4644: where reporting_type_code='AR_TURN_OVER_JUR_CODE'
4645: and tax_regime_code = l_tax_regime_code)
4646: ) THEN
4647: INTO ZX_REPORTING_TYPES_B(
4648: REPORTING_TYPE_ID ,
4649: REPORTING_TYPE_CODE ,
4650: REPORTING_TYPE_DATATYPE_CODE ,
4651: TAX_REGIME_CODE ,
4669: PROGRAM_LOGIN_ID ,
4670: OBJECT_VERSION_NUMBER
4671: )
4672: VALUES(
4673: zx_migrate_util.get_next_seqid('zx_reporting_types_b_s') ,--REPORTING_TYPE_ID
4674: 'AR_TURN_OVER_JUR_CODE' ,--REPORTING_TYPE_CODE
4675: 'TEXT' ,--REPORTING_TYPE_DATATYPE
4676: l_tax_regime_code ,
4677: NULL ,--TAX
4711: zx_rates.tax_regime_code;
4712: --Bug 4705196
4713: /*
4714: --Bug 4422813
4715: --The creation of zx_reporting_types_b , zx_reporting_codes_b and zx_reporting_codes_tl has been put together for
4716: --easy understanding
4717:
4718: INSERT ALL
4719: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4715: --The creation of zx_reporting_types_b , zx_reporting_codes_b and zx_reporting_codes_tl has been put together for
4716: --easy understanding
4717:
4718: INSERT ALL
4719: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4720: where reporting_type_code= 'PRINTED_TAX_RATE_NAME'
4721: and tax_regime_code = l_tax_regime_code
4722: )
4723: ) THEN
4720: where reporting_type_code= 'PRINTED_TAX_RATE_NAME'
4721: and tax_regime_code = l_tax_regime_code
4722: )
4723: ) THEN
4724: INTO ZX_REPORTING_TYPES_B(
4725: REPORTING_TYPE_ID ,
4726: REPORTING_TYPE_CODE ,
4727: REPORTING_TYPE_DATATYPE_CODE ,
4728: TAX_REGIME_CODE ,
4746: PROGRAM_LOGIN_ID ,
4747: OBJECT_VERSION_NUMBER
4748: )
4749: VALUES(
4750: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
4751: 'PRINTED_TAX_RATE_NAME' ,--REPORTING_TYPE_CODE
4752: 'VARCHAR' , --REPORTING_TYPE_DATATYPE
4753: l_tax_regime_code ,
4754: NULL ,--TAX
4842: fnd_global.conc_login_id , -- Program Login ID
4843: printed_tax_name,
4844: NULL,
4845: NULL,
4846: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
4847: and reporting_type_code = 'PRINTED_TAX_RATE_NAME'),
4848: 1)
4849:
4850: (select
4858: and language = l_language_code
4859: and printed_tax_name is not null
4860: and not exists
4861: ( select 1 from zx_reporting_codes_b where reporting_type_id =
4862: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = zxrb.tax_regime_code
4863: and reporting_type_code = 'PRINTED_TAX_RATE_NAME')
4864: and reporting_code_char_value = printed_tax_name)
4865: );
4866:
4930:
4931: --Need to Delete Seeded records if there is a source for reporting type to get created using 11i Migrated Regimes.
4932: IF ( l_exists_cnt > 0 ) THEN
4933: DELETE FROM ZX_REPORTING_TYPES_TL
4934: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4935: WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED' );
4936:
4937: DELETE FROM ZX_REPORT_TYPES_USAGES
4938: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4934: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4935: WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED' );
4936:
4937: DELETE FROM ZX_REPORT_TYPES_USAGES
4938: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4939: WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED' );
4940:
4941: DELETE FROM ZX_REPORTING_TYPES_B WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED';
4942: END IF ;
4937: DELETE FROM ZX_REPORT_TYPES_USAGES
4938: WHERE reporting_type_id IN ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
4939: WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED' );
4940:
4941: DELETE FROM ZX_REPORTING_TYPES_B WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED';
4942: END IF ;
4943:
4944: INSERT ALL
4945: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4941: DELETE FROM ZX_REPORTING_TYPES_B WHERE reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE' AND record_type_code = 'SEEDED';
4942: END IF ;
4943:
4944: INSERT ALL
4945: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
4946: where reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE'
4947: and tax_regime_code = l_tax_regime_code
4948: )
4949: ) THEN
4946: where reporting_type_code= 'ZX_ADJ_TAX_CLASSIF_CODE'
4947: and tax_regime_code = l_tax_regime_code
4948: )
4949: ) THEN
4950: INTO ZX_REPORTING_TYPES_B(
4951: REPORTING_TYPE_ID ,
4952: REPORTING_TYPE_CODE ,
4953: REPORTING_TYPE_DATATYPE_CODE ,
4954: TAX_REGIME_CODE ,
4972: PROGRAM_LOGIN_ID ,
4973: OBJECT_VERSION_NUMBER
4974: )
4975: VALUES(
4976: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
4977: 'ZX_ADJ_TAX_CLASSIF_CODE' ,--REPORTING_TYPE_CODE
4978: --'VARCHAR' ,--REPORTING_TYPE_DATATYPE
4979: 'TEXT' ,--REPORTING_TYPE_DATATYPE (bug6430516)
4980: l_tax_regime_code ,--TAX_REGIME_CODE
5058: fnd_global.conc_login_id , -- Program Login ID
5059: adjustment_tax_code,
5060: NULL,
5061: NULL,
5062: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
5063: and reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE'),
5064: 1)
5065:
5066: select
5072: where zxrb.tax_rate_id = avtb.vat_tax_id
5073: and adjustment_tax_code is not null
5074: and not exists
5075: ( select 1 from zx_reporting_codes_b where reporting_type_id =
5076: (select reporting_type_id FROM zx_reporting_types_b
5077: WHERE tax_regime_code = zxrb.tax_regime_code
5078: AND reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE')
5079: AND reporting_code_char_value = adjustment_tax_code)
5080: GROUP BY adjustment_tax_code,zxrb.tax_regime_code;
5124: fnd_global.conc_login_id , -- Program Login ID
5125: global_attribute5,
5126: NULL,
5127: NULL,
5128: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
5129: and reporting_type_code = 'CL_DEBIT_ACCOUNT'),
5130: 1)
5131:
5132: select
5139: and avtb.global_attribute_category ='JL.CL.ARXSUVAT.VAT_TAX'
5140: and avtb.global_attribute5 is not null
5141: and not exists
5142: ( select 1 from zx_reporting_codes_b where reporting_type_id =
5143: (select reporting_type_id FROM zx_reporting_types_b
5144: WHERE tax_regime_code = zxrb.tax_regime_code
5145: AND reporting_type_code = 'CL_DEBIT_ACCOUNT')
5146: AND reporting_code_char_value = avtb.global_attribute5);*/
5147:
5176: fnd_global.conc_login_id,--LAST_UPDATE_LOGIN
5177: reporting_type_id ,--REPORTING_TYPE_ID
5178: 1
5179: FROM
5180: zx_reporting_types_b rep_type,
5181: fnd_lookups lookups
5182: WHERE
5183: reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE'
5184: AND record_type_code = 'MIGRATED'
5250: WHEN types.REPORTING_TYPE_CODE = 'KR_BUSINESS_LOCATIONS' --Bug 4671652
5251: THEN 'Korean Business Locations'
5252: ELSE types.REPORTING_TYPE_CODE END REPORTING_TYPE_NAME -- Bug 4886324
5253: FROM
5254: ZX_REPORTING_TYPES_B TYPES,
5255: FND_LANGUAGES L
5256: WHERE
5257:
5258: TYPES.RECORD_TYPE_CODE = 'MIGRATED'
5292: SYSDATE ,
5293: fnd_global.conc_login_id,
5294: 1
5295: FROM
5296: zx_reporting_types_b types,
5297: fnd_lookups lookups
5298:
5299: WHERE types.reporting_type_code IN(
5300: 'AR_MUNICIPAL_JUR',
5363: NULL,
5364: report_types.REPORTING_TYPE_ID,
5365: 1
5366: FROM
5367: ZX_REPORTING_TYPES_B report_types
5368: WHERE
5369: report_types.REPORTING_TYPE_CODE = 'PT_LOCATION'
5370: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
5371: AND NOT EXISTS
5403: fnd_global.user_id ,
5404: sysdate ,
5405: fnd_global.conc_login_id
5406: FROM
5407: ZX_REPORTING_TYPES_B TYPES,
5408: ZX_REPORTING_CODES_B CODES,
5409: FND_LOOKUP_VALUES lookups,
5410: FND_LANGUAGES L
5411: WHERE
5451: fnd_global.user_id ,
5452: sysdate ,
5453: fnd_global.conc_login_id
5454: FROM
5455: ZX_REPORTING_TYPES_B TYPES,
5456: ZX_REPORTING_CODES_B CODES,
5457: FND_LOOKUP_VALUES lookups,
5458: FND_LANGUAGES L
5459: WHERE
5523: locations.location_code global_attribute,
5524: types.reporting_type_id reporting_type_id,
5525: types.effective_from effective_from
5526: FROM
5527: zx_reporting_types_b types,
5528: hr_locations locations
5529: WHERE
5530: types.reporting_type_code = 'KR_LOCATION'
5531: AND types.record_type_code = 'MIGRATED'
5565: fnd_global.user_id ,
5566: sysdate ,
5567: fnd_global.conc_login_id
5568: FROM
5569: ZX_REPORTING_TYPES_B TYPES,
5570: ZX_REPORTING_CODES_B CODES,
5571: FND_LOOKUP_VALUES lookups,
5572: FND_LANGUAGES L
5573: WHERE
5592: --Bug Fix 4671652
5593: --Creation of Reporting Types for KR_BUSINESS_LOCATIONS
5594:
5595: INSERT ALL
5596: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
5597: where reporting_type_code= 'KR_BUSINESS_LOCATIONS'
5598: and tax_regime_code = l_tax_regime_code
5599: )
5600: ) THEN
5597: where reporting_type_code= 'KR_BUSINESS_LOCATIONS'
5598: and tax_regime_code = l_tax_regime_code
5599: )
5600: ) THEN
5601: INTO ZX_REPORTING_TYPES_B(
5602: REPORTING_TYPE_ID ,
5603: REPORTING_TYPE_CODE ,
5604: REPORTING_TYPE_DATATYPE_CODE ,
5605: TAX_REGIME_CODE ,
5623: PROGRAM_LOGIN_ID ,
5624: OBJECT_VERSION_NUMBER
5625: )
5626: VALUES(
5627: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
5628: 'KR_BUSINESS_LOCATIONS' ,--REPORTING_TYPE_CODE
5629: --'VARCHAR' ,--REPORTING_TYPE_DATATYPE
5630: 'TEXT' ,--REPORTING_TYPE_DATATYPE (Bug6430516)
5631: l_tax_regime_code ,--TAX_REGIME_CODE
5709: fnd_global.conc_login_id , -- Program Login ID
5710: l_location_code,
5711: NULL,
5712: NULL,
5713: (select reporting_type_id FROM zx_reporting_types_b where tax_regime_code = l_tax_regime_code
5714: and reporting_type_code = 'KR_BUSINESS_LOCATIONS'),
5715: 1)
5716:
5717: select
5731: and tax_id_values_and_codes.vat_tax_id = nvl(p_tax_id,tax_id_values_and_codes.vat_tax_id)
5732: and tax_id_values_and_codes.location_code is not null
5733: and not exists
5734: ( select 1 from zx_reporting_codes_b where reporting_type_id =
5735: (select reporting_type_id FROM zx_reporting_types_b
5736: WHERE tax_regime_code = zxrb.tax_regime_code
5737: AND reporting_type_code = 'KR_BUSINESS_LOCATIONS')
5738: AND reporting_code_char_value = tax_id_values_and_codes.location_code);
5739:
5792: FROM
5793: AR_VAT_TAX_ALL_B codes,
5794: HR_LOCATIONS hrloc,
5795: ZX_RATES_B rates,
5796: ZX_REPORTING_TYPES_B reporting_types,
5797: ZX_REPORTING_CODES_B report_codes
5798: WHERE
5799: codes.vat_tax_id = rates.tax_rate_id
5800: AND codes.vat_tax_id = nvl(p_tax_id,codes.vat_tax_id)
5844: reporting_type_id ,--REPORTING_TYPE_ID
5845: 1
5846:
5847: FROM
5848: zx_reporting_types_b rep_type
5849:
5850: WHERE
5851: reporting_type_code = 'KR_BUSINESS_LOCATIONS'
5852: AND NOT EXISTS ( select 1 from zx_report_types_usages
5895: (
5896:
5897: p_rep_type_info varchar2,
5898: p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
5899: p_reporting_type_code zx_reporting_types_b.reporting_type_code%type
5900: )
5901:
5902: IS
5903:
5935: sysdate,
5936: fnd_global.conc_login_id,
5937: 1
5938:
5939: FROM ZX_REPORTING_TYPES_B
5940: WHERE REPORTING_TYPE_CODE=p_reporting_type_code
5941:
5942: AND NOT EXISTS (SELECT 1 FROM
5943: ZX_REPORTING_TYPES_B RTB,
5939: FROM ZX_REPORTING_TYPES_B
5940: WHERE REPORTING_TYPE_CODE=p_reporting_type_code
5941:
5942: AND NOT EXISTS (SELECT 1 FROM
5943: ZX_REPORTING_TYPES_B RTB,
5944: ZX_REPORT_CODES_ASSOC RCA
5945: WHERE
5946:
5947: RTB.REPORTING_TYPE_ID = RCA.REPORTING_TYPE_ID
5979: | |
5980: |=========================================================================*/
5981: PROCEDURE CREATE_REPORT_TYPE_PTP
5982: (
5983: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
5984: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
5985: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
5986: )
5987: IS
5980: |=========================================================================*/
5981: PROCEDURE CREATE_REPORT_TYPE_PTP
5982: (
5983: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
5984: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
5985: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
5986: )
5987: IS
5988:
5981: PROCEDURE CREATE_REPORT_TYPE_PTP
5982: (
5983: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
5984: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
5985: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
5986: )
5987: IS
5988:
5989: BEGIN
5991: arp_util_tax.debug('CREATE_REPORT_TYPE_PTP(+)');
5992: arp_util_tax.debug('p_reporting_type_code='||p_reporting_type_code);
5993:
5994: INSERT
5995: INTO ZX_REPORTING_TYPES_B(
5996: REPORTING_TYPE_ID ,
5997: REPORTING_TYPE_CODE ,
5998: REPORTING_TYPE_DATATYPE_CODE ,
5999: TAX_REGIME_CODE ,
6017: PROGRAM_LOGIN_ID ,
6018: OBJECT_VERSION_NUMBER
6019: )
6020: SELECT
6021: zx_reporting_types_b_s.nextval, --REPORTING_TYPE_ID
6022: p_reporting_type_code, --REPORTING_TYPE_CODE
6023: p_datatype, --REPORTING_TYPE_DATATYPE
6024: NULL, --TAX REGIME CODE
6025: NULL, --TAX
6043: 1
6044:
6045: FROM DUAL
6046:
6047: WHERE NOT EXISTS (select 1 from zx_reporting_types_b
6048: where reporting_type_code= p_reporting_type_code
6049: and tax_regime_code IS NULL);
6050:
6051:
6077: | |
6078: |=========================================================================*/
6079: PROCEDURE CREATE_REPORT_TYPE_SEED
6080: (
6081: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
6082: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
6083: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
6084: )
6085: IS
6078: |=========================================================================*/
6079: PROCEDURE CREATE_REPORT_TYPE_SEED
6080: (
6081: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
6082: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
6083: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
6084: )
6085: IS
6086:
6079: PROCEDURE CREATE_REPORT_TYPE_SEED
6080: (
6081: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
6082: p_datatype IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_DATATYPE_CODE%TYPE,
6083: p_has_rep_code IN ZX_REPORTING_TYPES_B.HAS_REPORTING_CODES_FLAG%TYPE
6084: )
6085: IS
6086:
6087: BEGIN
6089: arp_util_tax.debug('CREATE_REPORT_TYPE_PTP(+)');
6090: arp_util_tax.debug('p_reporting_type_code='||p_reporting_type_code);
6091:
6092: INSERT
6093: INTO ZX_REPORTING_TYPES_B(
6094: REPORTING_TYPE_ID ,
6095: REPORTING_TYPE_CODE ,
6096: REPORTING_TYPE_DATATYPE_CODE ,
6097: TAX_REGIME_CODE ,
6115: PROGRAM_LOGIN_ID ,
6116: OBJECT_VERSION_NUMBER
6117: )
6118: SELECT
6119: zx_reporting_types_b_s.nextval, --REPORTING_TYPE_ID
6120: p_reporting_type_code, --REPORTING_TYPE_CODE
6121: p_datatype, --REPORTING_TYPE_DATATYPE
6122: NULL, --TAX REGIME CODE
6123: NULL, --TAX
6139: fnd_global.conc_program_id , -- Program Id
6140: fnd_global.conc_login_id , -- Program Login ID
6141: 1
6142: FROM DUAL
6143: WHERE NOT EXISTS (select 1 from zx_reporting_types_b
6144: where reporting_type_code= p_reporting_type_code
6145: );
6146:
6147: -- Insert into ZX_REPORTING_TYPES_TL
6223: THEN 'Korean Business Locations'
6224: ELSE types.REPORTING_TYPE_CODE END REPORTING_TYPE_NAME
6225:
6226: FROM
6227: ZX_REPORTING_TYPES_B TYPES,
6228: FND_LANGUAGES L
6229: WHERE
6230: TYPES.RECORD_TYPE_CODE = 'SEEDED'
6231: AND L.INSTALLED_FLAG in ('I', 'B')
6265: | 25-May-2006 Ashwin Gurram Created. |
6266: | |
6267: |=========================================================================*/
6268: PROCEDURE CREATE_REPORTING_CODES_SEED (
6269: p_reporting_type_code IN ZX_REPORTING_TYPES_B.REPORTING_TYPE_CODE %TYPE,
6270: P_lookup_type IN FND_LOOKUPS.LOOKUP_TYPE%TYPE
6271: ) IS
6272:
6273: BEGIN
6324: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
6325: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
6326: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
6327: FROM
6328: ZX_REPORTING_TYPES_B report_types,
6329: FND_LOOKUPS lookups
6330: WHERE
6331: report_types.REPORTING_TYPE_CODE = p_reporting_type_code
6332: AND report_types.RECORD_TYPE_CODE = 'SEEDED'
6365: 120 ,
6366: sysdate ,
6367: 0
6368: FROM
6369: ZX_REPORTING_TYPES_B TYPES,
6370: ZX_REPORTING_CODES_B CODES,
6371: FND_LOOKUP_VALUES lookups,
6372: FND_LANGUAGES L
6373: WHERE
6442: 0,--LAST_UPDATE_LOGIN
6443: reporting_type_id ,--REPORTING_TYPE_ID
6444: 1
6445: FROM
6446: zx_reporting_types_b rep_type
6447: WHERE
6448: reporting_type_code IN
6449: (
6450: 'PT_LOCATION','PT_PRD_TAXABLE_BOX','PT_PRD_REC_TAX_BOX','PT_ANL_TTL_TAXABLE_BOX',
6480: 0,--LAST_UPDATE_LOGIN
6481: reporting_type_id ,--REPORTING_TYPE_ID
6482: 1
6483: FROM
6484: zx_reporting_types_b rep_type
6485: WHERE
6486: reporting_type_code IN ('FISCAL PRINTER','CAI NUMBER')
6487: AND record_type_code = 'SEEDED'
6488: AND NOT EXISTS ( select 1 from zx_report_types_usages
6583: NULL,
6584: report_types.REPORTING_TYPE_ID,
6585: 1
6586: FROM
6587: ZX_REPORTING_TYPES_B report_types
6588: WHERE
6589: report_types.REPORTING_TYPE_CODE = 'PT_LOCATION'
6590: AND report_types.RECORD_TYPE_CODE = 'SEEDED'
6591: AND NOT EXISTS
6634: ZX_REPORTING_CODES_B CODES,
6635: FND_LANGUAGES L
6636: WHERE
6637: CODES.RECORD_TYPE_CODE = 'SEEDED'
6638: AND codes.reporting_type_id = ( SELECT reporting_type_id FROM ZX_REPORTING_TYPES_B
6639: WHERE REPORTING_TYPE_CODE = 'PT_LOCATION' AND record_type_code = 'SEEDED' )
6640: AND L.INSTALLED_FLAG in ('I', 'B')
6641: AND not exists
6642: (select NULL
6744: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
6745: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
6746: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
6747: FROM
6748: ZX_REPORTING_TYPES_B report_types,
6749: JA_LOOKUPS lookups
6750: WHERE
6751: report_types.REPORTING_TYPE_CODE = 'TW_GOVERNMENT_TAX_TYPE'
6752: AND report_types.RECORD_TYPE_CODE = 'SEEDED'
6785: 120 ,
6786: sysdate ,
6787: 0
6788: FROM
6789: ZX_REPORTING_TYPES_B TYPES,
6790: ZX_REPORTING_CODES_B CODES,
6791: FND_LOOKUP_VALUES lookups,
6792: FND_LANGUAGES L
6793: WHERE
6881: SYSDATE ,
6882: fnd_global.conc_login_id,
6883: 1
6884: FROM
6885: zx_reporting_types_b types,
6886: fnd_lookups lookups
6887:
6888: WHERE
6889: types.reporting_type_code IN('PL_TAX_ORIGIN',
6926: SYSDATE ,
6927: fnd_global.conc_login_id,
6928: 1
6929: FROM
6930: zx_reporting_types_b types,
6931: fnd_lookups lookups
6932:
6933: WHERE
6934: types.reporting_type_code IN('MEMBER STATE',
6976: BEGIN
6977: arp_util_tax.debug('CREATE_REPORTING_TYPE_EMEA(+)');
6978: arp_util_tax.debug('p_reporting_type_code = '||'EMEA_VAT_REPORTING_TYPE');
6979: INSERT
6980: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
6981: where reporting_type_code= 'EMEA_VAT_REPORTING_TYPE'
6982: and tax_regime_code is NULL)
6983: ) THEN
6984: INTO ZX_REPORTING_TYPES_B(
6980: WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
6981: where reporting_type_code= 'EMEA_VAT_REPORTING_TYPE'
6982: and tax_regime_code is NULL)
6983: ) THEN
6984: INTO ZX_REPORTING_TYPES_B(
6985: REPORTING_TYPE_ID ,
6986: REPORTING_TYPE_CODE ,
6987: REPORTING_TYPE_DATATYPE_CODE ,
6988: TAX_REGIME_CODE ,
7006: PROGRAM_LOGIN_ID ,
7007: OBJECT_VERSION_NUMBER
7008: )
7009: VALUES(
7010: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
7011: 'EMEA_VAT_REPORTING_TYPE' ,--REPORTING_TYPE_CODE
7012: --'VARCHAR' ,--REPORTING_TYPE_DATATYPE
7013: 'TEXT' ,--REPORTING_TYPE_DATATYPE (Bug6430516)
7014: NULL ,--TAX_REGIME_CODE
7068: SELECT
7069: TYPES.REPORTING_TYPE_ID ,
7070: L.LANGUAGE_CODE
7071: FROM
7072: ZX_REPORTING_TYPES_B TYPES,
7073: FND_LANGUAGES L
7074: WHERE TYPES.RECORD_TYPE_CODE = 'MIGRATED'
7075: AND TYPES.REPORTING_TYPE_CODE = 'EMEA_VAT_REPORTING_TYPE'
7076: AND L.INSTALLED_FLAG in ('I', 'B')
7128: SYSDATE ,--LAST_UPDATE_DATE
7129: fnd_global.conc_login_id ,--LAST_UPDATE_LOGIN
7130: 1 --OBJECT_VERSION_NUMBER
7131: FROM
7132: zx_reporting_types_b types
7133: WHERE types.reporting_type_code IN('EMEA_VAT_REPORTING_TYPE')
7134: --AND types.record_type_code = 'MIGRATED'
7135: AND NOT EXISTS ( select 1 from zx_report_types_usages
7136: where reporting_type_id = types.reporting_type_id
7215: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
7216: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
7217: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
7218: FROM
7219: ZX_REPORTING_TYPES_B report_types,
7220: FND_LOOKUPS lookups
7221: WHERE
7222: report_types.REPORTING_TYPE_CODE = 'EMEA_VAT_REPORTING_TYPE'
7223: --AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
7268: fnd_global.user_id ,
7269: sysdate ,
7270: fnd_global.conc_login_id
7271: FROM
7272: ZX_REPORTING_TYPES_B TYPES,
7273: ZX_REPORTING_CODES_B CODES,
7274: FND_LOOKUP_VALUES lookups,
7275: FND_LANGUAGES L
7276: WHERE
7362: report_codes.REPORTING_CODE_NUM_VALUE , --REPORTING_CODE_NUM_VALUE
7363: 1
7364: FROM
7365: AP_TAX_CODES_ALL codes,
7366: ZX_REPORTING_TYPES_B reporting_types,
7367: ZX_REPORTING_CODES_B report_codes,
7368: ZX_RATES_B rates,
7369: -- Bug 7620818
7370: ZX_REGIMES_B reg
7434: report_codes.REPORTING_CODE_NUM_VALUE , --REPORTING_CODE_NUM_VALUE
7435: 1
7436: FROM
7437: AR_VAT_TAX_ALL codes,
7438: ZX_REPORTING_TYPES_B reporting_types,
7439: ZX_REPORTING_CODES_B report_codes,
7440: ZX_RATES_B rates,
7441: -- Bug 7620818
7442: ZX_REGIMES_B reg
7484: BEGIN
7485: arp_util_tax.debug('CREATE_REP_ENT_REVERSE_VAT(+)');
7486: arp_util_tax.debug('p_reporting_type_code = '||'REVERSE_CHARGE_VAT');
7487:
7488: arp_util_tax.debug('Inserting into ZX_REPORTING_TYPES_B table...');
7489:
7490: INSERT WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
7491: where reporting_type_code = l_reporting_type_code
7492: and tax_regime_code = l_tax_regime_code
7486: arp_util_tax.debug('p_reporting_type_code = '||'REVERSE_CHARGE_VAT');
7487:
7488: arp_util_tax.debug('Inserting into ZX_REPORTING_TYPES_B table...');
7489:
7490: INSERT WHEN (NOT EXISTS (select 1 from zx_reporting_types_b
7491: where reporting_type_code = l_reporting_type_code
7492: and tax_regime_code = l_tax_regime_code
7493: and tax = l_tax)
7494: ) THEN
7491: where reporting_type_code = l_reporting_type_code
7492: and tax_regime_code = l_tax_regime_code
7493: and tax = l_tax)
7494: ) THEN
7495: INTO ZX_REPORTING_TYPES_B(
7496: REPORTING_TYPE_ID ,
7497: REPORTING_TYPE_CODE ,
7498: REPORTING_TYPE_DATATYPE_CODE ,
7499: TAX_REGIME_CODE ,
7517: PROGRAM_LOGIN_ID ,
7518: OBJECT_VERSION_NUMBER
7519: )
7520: VALUES(
7521: zx_reporting_types_b_s.nextval ,--REPORTING_TYPE_ID
7522: l_reporting_type_code ,--REPORTING_TYPE_CODE
7523: 'YES_NO' ,--REPORTING_TYPE_DATATYPE
7524: l_tax_regime_code ,--TAX_REGIME_CODE
7525: l_tax ,--TAX
7582: SELECT
7583: TYPES.REPORTING_TYPE_ID ,
7584: L.LANGUAGE_CODE
7585: FROM
7586: ZX_REPORTING_TYPES_B TYPES,
7587: FND_LANGUAGES L
7588: WHERE TYPES.RECORD_TYPE_CODE = 'MIGRATED'
7589: AND TYPES.REPORTING_TYPE_CODE = 'REVERSE_CHARGE_VAT'
7590: AND L.INSTALLED_FLAG in ('I', 'B')
7620: SYSDATE ,--LAST_UPDATE_DATE
7621: fnd_global.conc_login_id ,--LAST_UPDATE_LOGIN
7622: 1 --OBJECT_VERSION_NUMBER
7623: FROM
7624: zx_reporting_types_b types
7625: WHERE types.reporting_type_code = 'REVERSE_CHARGE_VAT'
7626: --AND types.record_type_code = 'MIGRATED'
7627: AND NOT EXISTS ( select 1 from zx_report_types_usages
7628: where reporting_type_id = types.reporting_type_id
7680: report_types.EFFECTIVE_FROM EFFECTIVE_FROM,
7681: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID,
7682: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
7683: FROM
7684: ZX_REPORTING_TYPES_B report_types,
7685: FND_LOOKUPS lookups
7686: WHERE
7687: report_types.REPORTING_TYPE_CODE = 'REVERSE_CHARGE_VAT'
7688: --AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
7724: fnd_global.user_id ,
7725: sysdate ,
7726: fnd_global.conc_login_id
7727: FROM
7728: ZX_REPORTING_TYPES_B TYPES,
7729: ZX_REPORTING_CODES_B CODES,
7730: FND_LOOKUP_VALUES lookups,
7731: FND_LANGUAGES L
7732: WHERE
7787: report_codes.REPORTING_CODE_NUM_VALUE , --REPORTING_CODE_NUM_VALUE
7788: 1
7789: FROM
7790: AR_VAT_TAX_ALL codes,
7791: ZX_REPORTING_TYPES_B reporting_types,
7792: ZX_REPORTING_CODES_B report_codes,
7793: ZX_RATES_B rates
7794: WHERE
7795: codes.vat_tax_id = rates.tax_rate_id
7844:
7845: dyn_sql_stmt:=
7846: 'INSERT WHEN
7847: (NOT EXISTS
7848: (SELECT 1 FROM zx_reporting_types_b
7849: WHERE reporting_type_code = ''ZX_INTRA_EU_TRX_TYPE''
7850: AND tax_regime_code IS NULL)
7851: ) THEN
7852: INTO ZX_REPORTING_TYPES_B
7848: (SELECT 1 FROM zx_reporting_types_b
7849: WHERE reporting_type_code = ''ZX_INTRA_EU_TRX_TYPE''
7850: AND tax_regime_code IS NULL)
7851: ) THEN
7852: INTO ZX_REPORTING_TYPES_B
7853: (REPORTING_TYPE_ID,
7854: REPORTING_TYPE_CODE,
7855: REPORTING_TYPE_DATATYPE_CODE,
7856: TAX_REGIME_CODE,
7874: PROGRAM_LOGIN_ID,
7875: OBJECT_VERSION_NUMBER
7876: )
7877: VALUES
7878: (zx_reporting_types_b_s.nextval,
7879: ''ZX_INTRA_EU_TRX_TYPE'',
7880: ''TEXT'',
7881: NULL,
7882: NULL,
7912:
7913: dyn_sql_stmt:=
7914: 'INSERT WHEN
7915: (NOT EXISTS
7916: (SELECT 1 FROM zx_reporting_types_b
7917: WHERE reporting_type_code = ''ZX_LGL_JUSTIFICATN_MSG_TYPE''
7918: AND tax_regime_code IS NULL)
7919: ) THEN
7920: INTO ZX_REPORTING_TYPES_B
7916: (SELECT 1 FROM zx_reporting_types_b
7917: WHERE reporting_type_code = ''ZX_LGL_JUSTIFICATN_MSG_TYPE''
7918: AND tax_regime_code IS NULL)
7919: ) THEN
7920: INTO ZX_REPORTING_TYPES_B
7921: (REPORTING_TYPE_ID,
7922: REPORTING_TYPE_CODE,
7923: REPORTING_TYPE_DATATYPE_CODE,
7924: TAX_REGIME_CODE,
7942: PROGRAM_LOGIN_ID,
7943: OBJECT_VERSION_NUMBER
7944: )
7945: VALUES
7946: (zx_reporting_types_b_s.nextval,
7947: ''ZX_LGL_JUSTIFICATN_MSG_TYPE'',
7948: ''TEXT'',
7949: NULL,
7950: NULL,
8004: fnd_global.conc_login_id
8005: FROM ( SELECT TYPES.REPORTING_TYPE_ID ,
8006: L.LANGUAGE_CODE,
8007: TYPES.REPORTING_TYPE_CODE
8008: FROM ZX_REPORTING_TYPES_B TYPES,
8009: FND_LANGUAGES L
8010: WHERE TYPES.RECORD_TYPE_CODE = 'MIGRATED'
8011: AND TYPES.REPORTING_TYPE_CODE in ('ZX_INTRA_EU_TRX_TYPE',
8012: 'ZX_LGL_JUSTIFICATN_MSG_TYPE')
8073: SYSDATE , --LAST_UPDATE_DATE
8074: fnd_global.conc_login_id , --LAST_UPDATE_LOGIN
8075: reporting_type_id , --REPORTING_TYPE_ID
8076: 1 --OBJECT_VERSION_NUMBER
8077: FROM zx_reporting_types_b rep_type,
8078: fnd_lookups lookups
8079: WHERE reporting_type_code in ('ZX_INTRA_EU_TRX_TYPE',
8080: 'ZX_LGL_JUSTIFICATN_MSG_TYPE')
8081: AND record_type_code = 'MIGRATED'
8154: FROM ( SELECT UPPER(lookup_code) REPORTING_CODE_CHAR_VALUE,
8155: report_types.EFFECTIVE_FROM EFFECTIVE_FROM ,
8156: report_types.REPORTING_TYPE_ID REPORTING_TYPE_ID ,
8157: report_types.REPORTING_TYPE_DATATYPE_CODE DATATYPE
8158: FROM ZX_REPORTING_TYPES_B report_types,
8159: AR_LOOKUPS lookups
8160: WHERE report_types.REPORTING_TYPE_CODE in ('ZX_INTRA_EU_TRX_TYPE',
8161: 'ZX_LGL_JUSTIFICATN_MSG_TYPE')
8162: AND report_types.RECORD_TYPE_CODE = 'MIGRATED'
8198: SYSDATE ,
8199: fnd_global.user_id ,
8200: SYSDATE ,
8201: fnd_global.conc_login_id
8202: FROM ZX_REPORTING_TYPES_B TYPES,
8203: ZX_REPORTING_CODES_B CODES,
8204: FND_LOOKUP_VALUES lookups ,
8205: FND_LANGUAGES L
8206: WHERE TYPES.REPORTING_TYPE_ID = CODES.REPORTING_TYPE_ID
8294: report_codes.REPORTING_CODE_NUM_VALUE
8295: FROM
8296: AR_VAT_TAX_ALL_B codes,
8297: ZX_RATES_B rates,
8298: ZX_REPORTING_TYPES_B report_types,
8299: ZX_REPORTING_CODES_B report_codes
8300: WHERE
8301: codes.vat_tax_id = rates.tax_rate_id AND
8302: Upper(codes.LGL_JUSTIFICATION_MSG_CODE) = report_codes.REPORTING_CODE_CHAR_VALUE AND
8362: report_codes.REPORTING_CODE_NUM_VALUE
8363: FROM
8364: AR_VAT_TAX_ALL_B codes,
8365: ZX_RATES_B rates,
8366: ZX_REPORTING_TYPES_B report_types,
8367: ZX_REPORTING_CODES_B report_codes
8368: WHERE
8369: codes.vat_tax_id = rates.tax_rate_id AND
8370: Upper(codes.INTRA_EU_TRX_TYPE_CODE) = report_codes.REPORTING_CODE_CHAR_VALUE AND