[Home] [Help]
273: |
274: | OUT
275: |
276: | DESCRIPTION
277: | This routine creates records in zx_taxes_b/tl for tax code with tax
278: | type = 'LOCATION' and tax code used by tax vendors.
279: | It creates regime, tax, status.
280: |
281: | SCOPE - PUBLIC
517: ** 101 US CITY COUNTY STATE 200 USD
518: ** 200 US CITY COUNTY STATE 300 USD
519: **
520: **
521: ** Records in denorm_tbl is used to insert records in zx_regimes_b and zx_taxes_b.
522: ** The program creates one regime record for each location structure.
523: ** The program creates as many tax records s as the number of segments in a location structure.
524: **
525: ** If same structure is shared among different OUs
1709: -- Inserting tax for LOCATION based AR Tax Codes (partition by content_owner_id)
1710: IF L_MULTI_ORG_FLAG = 'Y'
1711: THEN
1712: INSERT ALL
1713: INTO zx_taxes_b_tmp
1714: (
1715: TAX ,
1716: EFFECTIVE_FROM ,
1717: EFFECTIVE_TO ,
1913: L_PARENT_GEOGRAPHY_TYPE ,
1914: L_PARENT_GEOGRAPHY_ID ,
1915: L_ALLOW_MASS_CREATE_FLAG ,
1916: L_APPLIED_AMT_HANDLING_FLAG ,
1917: ZX_TAXES_B_S.NEXTVAL ,
1918: L_CONTENT_OWNER_ID ,
1919: L_REP_TAX_AUTHORITY_ID ,
1920: L_COLL_TAX_AUTHORITY_ID ,
1921: L_THRSHLD_CHK_TMPLT_CODE ,
1952: LAST_UPDATE_LOGIN
1953: )
1954: VALUES
1955: (
1956: ZX_TAXES_B_S.NEXTVAL ,
1957: USERENV('LANG') ,
1958: USERENV('LANG') ,
1959: 'LOCATION' ,
1960: L_CREATED_BY ,
2070: WHERE country_code = asp.default_country
2071: AND rownum = 1) L_PARENT_GEOGRAPHY_ID,
2072: 'N' L_ALLOW_MASS_CREATE_FLAG,
2073: 'P' L_APPLIED_AMT_HANDLING_FLAG,
2074: --ZX_TAXES_B_S.NEXTVAL L_TAX_ID,
2075: ptp.party_tax_profile_id L_CONTENT_OWNER_ID,
2076: NULL L_REP_TAX_AUTHORITY_ID,
2077: NULL L_COLL_TAX_AUTHORITY_ID,
2078: NULL L_THRSHLD_CHK_TMPLT_CODE,
2116: ),'-999'
2117: )
2118: )
2119: AND NOT EXISTS (SELECT 1
2120: FROM zx_taxes_b ztb
2121: WHERE ztb.tax_regime_code = zrb.tax_regime_code
2122: AND (ztb.tax = 'LOCATION' or ztb.tax = avt.tax_code)
2123: AND ztb.content_owner_id = ptp.party_tax_profile_id);
2124: ELSE
2123: AND ztb.content_owner_id = ptp.party_tax_profile_id);
2124: ELSE
2125:
2126: INSERT ALL
2127: INTO zx_taxes_b_tmp
2128: (
2129: TAX ,
2130: EFFECTIVE_FROM ,
2131: EFFECTIVE_TO ,
2327: L_PARENT_GEOGRAPHY_TYPE ,
2328: L_PARENT_GEOGRAPHY_ID ,
2329: L_ALLOW_MASS_CREATE_FLAG ,
2330: L_APPLIED_AMT_HANDLING_FLAG ,
2331: ZX_TAXES_B_S.NEXTVAL ,
2332: L_CONTENT_OWNER_ID ,
2333: L_REP_TAX_AUTHORITY_ID ,
2334: L_COLL_TAX_AUTHORITY_ID ,
2335: L_THRSHLD_CHK_TMPLT_CODE ,
2366: LAST_UPDATE_LOGIN
2367: )
2368: VALUES
2369: (
2370: ZX_TAXES_B_S.NEXTVAL ,
2371: USERENV('LANG') ,
2372: USERENV('LANG') ,
2373: 'LOCATION' ,
2374: L_CREATED_BY ,
2484: WHERE country_code = asp.default_country
2485: AND rownum = 1) L_PARENT_GEOGRAPHY_ID,
2486: 'N' L_ALLOW_MASS_CREATE_FLAG,
2487: 'P' L_APPLIED_AMT_HANDLING_FLAG,
2488: --ZX_TAXES_B_S.NEXTVAL L_TAX_ID,
2489: ptp.party_tax_profile_id L_CONTENT_OWNER_ID,
2490: NULL L_REP_TAX_AUTHORITY_ID,
2491: NULL L_COLL_TAX_AUTHORITY_ID,
2492: NULL L_THRSHLD_CHK_TMPLT_CODE,
2531: ),'-999'
2532: )
2533: )
2534: AND NOT EXISTS (SELECT 1
2535: FROM zx_taxes_b ztb
2536: WHERE ztb.tax_regime_code = zrb.tax_regime_code
2537: AND (ztb.tax = 'LOCATION' or ztb.tax = avt.tax_code)
2538: AND ztb.content_owner_id = ptp.party_tax_profile_id);
2539:
2687: SYSDATE l_last_update_date,
2688: fnd_global.user_id l_last_updated_by,
2689: fnd_global.conc_login_id l_last_update_login,
2690: fnd_global.conc_request_id l_request_id
2691: FROM zx_taxes_b tax
2692: WHERE tax.tax_regime_code like '%-SALES-TAX-%'
2693: AND NOT EXISTS (SELECT 1
2694: FROM zx_status_b status
2695: WHERE tax.tax_regime_code = status.tax_regime_code
3784: --bug6336567
3785: BEGIN
3786: SELECT tax_account_source_tax
3787: INTO l_tax_acct_source_tax
3788: FROM zx_taxes_b
3789: WHERE tax_regime_code = tax_regime_code_rec(k)
3790: AND tax = 'CITY'
3791: AND record_type_code = 'MIGRATED';
3792: EXCEPTION
3946: SYSDATE l_last_update_date,
3947: fnd_global.user_id l_last_updated_by,
3948: fnd_global.conc_login_id l_last_update_login,
3949: fnd_global.conc_request_id l_request_id
3950: FROM zx_taxes_b ztb,
3951: ar_system_parameters_all asp
3952: WHERE ztb.tax_regime_code LIKE 'US-SALES-TAX-%'
3953: AND TO_CHAR(asp.location_structure_id) = LTRIM(tax_regime_code, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ-_')
3954: AND asp.tax_database_view_set IN ('_A', '_V')
4083: SYSDATE l_last_update_date,
4084: fnd_global.user_id l_last_updated_by,
4085: fnd_global.conc_login_id l_last_update_login,
4086: fnd_global.conc_request_id l_request_id
4087: FROM zx_taxes_b ztb,
4088: ar_system_parameters_all asp
4089: WHERE ztb.tax_regime_code LIKE 'US-SALES-TAX-%'
4090: AND TO_CHAR(asp.location_structure_id) = LTRIM(tax_regime_code, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ-_')
4091: AND asp.tax_database_view_set IN ('_A', '_V')
4421: ar_vat_tax_all_b avt,
4422: zx_party_tax_profile ptp,
4423: ar_system_parameters_all asp,
4424: zx_regimes_b zrb,
4425: zx_taxes_b ztb
4426: WHERE ptp.party_type_code = 'OU'
4427: AND ptp.party_id = avt.org_id
4428: AND avt.org_id = asp.org_id
4429: AND asp.tax_database_view_set IN ('_A', '_V')
4753: ar_vat_tax_all_b avt,
4754: zx_party_tax_profile ptp,
4755: ar_system_parameters_all asp,
4756: zx_regimes_b zrb,
4757: zx_taxes_b ztb
4758: WHERE ptp.party_type_code = 'OU'
4759: AND ptp.party_id = l_org_id
4760: AND ptp.party_id = avt.org_id
4761: AND avt.org_id = asp.org_id
6060: -- ****** VARIABLES ******
6061: l_location_str_id ar_system_parameters_all.location_structure_id%TYPE;
6062: l_org_id ar_vat_tax_all_b.org_id%TYPE;
6063: l_tax_regime_code zx_regimes_b.tax_regime_code%TYPE;
6064: l_tax zx_taxes_b.tax%TYPE;
6065:
6066: BEGIN
6067: IF p_tax_id IS NOT NULL AND p_tax_type = 'LOCATION' THEN
6068: BEGIN
6118: IF l_tax_regime_code IS NOT NULL THEN
6119: BEGIN
6120: SELECT ztb.tax
6121: INTO l_tax
6122: FROM zx_taxes_b ztb,
6123: zx_party_tax_profile zptp
6124: WHERE ztb.content_owner_id = zptp.party_tax_profile_id
6125: AND zptp.party_type_code = 'OU'
6126: AND zptp.party_id = l_org_id
6197: |
6198: | OUT
6199: |
6200: | DESCRIPTION
6201: | This routine is used to insert data into zx_taxes_b for location
6202: | segment qualifier. Mainly created for code readability.
6203: |
6204: | SCOPE - PRIVATE
6205: |
6246: AND country_code = p_country_code;
6247:
6248: INSERT ALL
6249: WHEN (NOT EXISTS (SELECT 1
6250: FROM zx_taxes_b
6251: WHERE tax_regime_code = p_tax_regime_code
6252: AND tax = p_seg_att_type
6253: AND content_owner_id = -99
6254: )
6252: AND tax = p_seg_att_type
6253: AND content_owner_id = -99
6254: )
6255: ) THEN
6256: INTO ZX_TAXES_B_TMP
6257: (
6258: TAX ,
6259: EFFECTIVE_FROM ,
6260: EFFECTIVE_TO ,
6477: NULL , -- PARENT_GEOGRAPHY_TYPE
6478: NULL , -- PARENT_GEOGRAPHY_ID
6479: 'Y' , -- ALLOW_MASS_CREATE_FLAG /*Bug fix 552412*/
6480: 'P' , -- APPLIED_AMT_HANDLING_FLAG
6481: zx_taxes_b_s.nextval , -- TAX_ID
6482: -99 , -- CONTENT_OWNER_ID
6483: NULL , -- REP_TAX_AUTHORITY_ID
6484: NULL , -- COLL_TAX_AUTHORITY_ID
6485: NULL , -- THRSHLD_CHK_TMPLT_CODE
6510: )
6511: /*
6512:
6513: WHEN (NOT EXISTS (SELECT 1
6514: FROM zx_taxes_b
6515: WHERE tax_regime_code = p_tax_regime_code
6516: AND tax = p_seg_att_type
6517: AND content_owner_id = -99
6518: )
6538: fnd_global.user_id,
6539: SYSDATE,
6540: fnd_global.user_id,
6541: fnd_global.conc_login_id,
6542: ZX_TAXES_B_S.NEXTVAL
6543: )*/
6544: SELECT 1 FROM DUAL;
6545:
6546: EXCEPTION
7339: |
7340: | OUT
7341: |
7342: | DESCRIPTION
7343: | This routine creates records in zx_taxes_b/tl grouping by zx_rates_b
7344: |
7345: |
7346: | SCOPE - PRIVATE
7347: |
7366:
7367: IF L_MULTI_ORG_FLAG = 'Y'
7368: THEN
7369:
7370: INSERT INTO ZX_TAXES_B_TMP
7371: (
7372: TAX ,
7373: EFFECTIVE_FROM ,
7374: EFFECTIVE_TO ,
7618: NULL ,--PARENT_GEOGRAPHY_TYPE --YK:8/30/2004: Can be populated for US Sales Tax
7619: NULL ,--PARENT_GEOGRAPHY_ID --YK:8/30/2004: Can be populated for US Sales Tax
7620: 'N' ,--ALLOW_MASS_CREATE_FLAG
7621: 'P' ,--APPLIED_AMT_HANDLING_FLAG --Bug 5232502
7622: ZX_TAXES_B_S.NEXTVAL ,--TAX_ID
7623: L_CONTENT_OWNER_ID ,
7624: NULL ,--REP_TAX_AUTHORITY_ID
7625: NULL ,--COLL_TAX_AUTHORITY_ID
7626: NULL ,--THRSHLD_CHK_TMPLT_CODE
7681: --Added following conditions for Sync process
7682: AND results.tax_code_id = nvl(p_tax_id, results.tax_code_id)
7683:
7684: --Rerunability
7685: AND not exists (select 1 from zx_taxes_b
7686: where tax_regime_code = rates.tax_regime_code
7687: and tax = rates.tax
7688: and content_owner_id= rates.content_owner_id
7689: )
7710: ) TAX
7711: --YK:B:10/07/2004: Added the following condition.
7712: WHERE NOT EXISTS
7713: (select 1
7714: from zx_taxes_b tax1
7715: where tax.l_tax_regime_code = tax1.tax_regime_code
7716: and tax.l_tax = tax1.tax
7717: and tax.l_content_owner_id = tax1.content_owner_id
7718:
7719: )
7720: ;
7721: ELSE
7722:
7723: INSERT INTO ZX_TAXES_B_TMP
7724: (
7725: TAX ,
7726: EFFECTIVE_FROM ,
7727: EFFECTIVE_TO ,
7971: NULL ,--PARENT_GEOGRAPHY_TYPE --YK:8/30/2004: Can be populated for US Sales Tax
7972: NULL ,--PARENT_GEOGRAPHY_ID --YK:8/30/2004: Can be populated for US Sales Tax
7973: 'N' ,--ALLOW_MASS_CREATE_FLAG
7974: 'P' ,--APPLIED_AMT_HANDLING_FLAG --Bug 5232502
7975: ZX_TAXES_B_S.NEXTVAL ,--TAX_ID
7976: L_CONTENT_OWNER_ID ,
7977: NULL ,--REP_TAX_AUTHORITY_ID
7978: NULL ,--COLL_TAX_AUTHORITY_ID
7979: NULL ,--THRSHLD_CHK_TMPLT_CODE
8035: --Added following conditions for Sync process
8036: AND results.tax_code_id = nvl(p_tax_id, results.tax_code_id)
8037:
8038: --Rerunability
8039: AND not exists (select 1 from zx_taxes_b
8040: where tax_regime_code = rates.tax_regime_code
8041: and tax = rates.tax
8042: and content_owner_id= rates.content_owner_id
8043: )
8064: ) TAX
8065: --YK:B:10/07/2004: Added the following condition.
8066: WHERE NOT EXISTS
8067: (select 1
8068: from zx_taxes_b tax1
8069: where tax.l_tax_regime_code = tax1.tax_regime_code
8070: and tax.l_tax = tax1.tax
8071: and tax.l_content_owner_id = tax1.content_owner_id
8072:
8104: fnd_global.conc_login_id ,
8105: B.TAX_ID
8106: FROM
8107: FND_LANGUAGES L,
8108: ZX_TAXES_B B,
8109: (select rates.tax_regime_code tax_regime_code,
8110: rates.tax tax,
8111: rates.content_owner_id content_owner_id,
8112: categs.description description,
8134: from ZX_TAXES_TL T
8135: where T.TAX_ID = B.TAX_ID
8136: and T.LANGUAGE = L.LANGUAGE_CODE);*/
8137:
8138: --Bug : 5092560 : This is to populate the ZX_TAXES_B.legal_reporting_status_def_val with '000000000000000'
8139:
8140: update zx_taxes_b_tmp
8141: set legal_reporting_status_def_val = '000000000000000'
8142: WHERE record_type_code = 'MIGRATED'
8136: and T.LANGUAGE = L.LANGUAGE_CODE);*/
8137:
8138: --Bug : 5092560 : This is to populate the ZX_TAXES_B.legal_reporting_status_def_val with '000000000000000'
8139:
8140: update zx_taxes_b_tmp
8141: set legal_reporting_status_def_val = '000000000000000'
8142: WHERE record_type_code = 'MIGRATED'
8143: AND tax_regime_code in (
8144: select distinct tax_regime_code from zx_regimes_b
8159: 'SK')
8160: ) ;
8161:
8162: --Bug Fix 5691957
8163: UPDATE zx_taxes_b_tmp tax SET taxable_basis_rule_flag = 'Y' WHERE
8164: EXISTS
8165: (SELECT 1 FROM zx_rates_b rates,ar_vat_tax_all_b codes
8166: WHERE rates.tax_regime_code = tax.tax_regime_code
8167: AND rates.tax = tax.tax
8466: | DESCRIPTION
8467: | This routine processes AR Tax_setup related fnd_lookups and creates
8468: | appropriate ZX lookups in FND_LOOKUPS.
8469: | AR_VAT_TAX_ALL_B.VAT_TRANSACTION_TYPE --> ZX_RATES.VAT_TRANSACTION_TYPE
8470: | AR_VAT_TAX_ALL_B.TAX_TYPE --> ZX_TAXES_B.TAX_TYPE_CODE
8471: |
8472: | SCOPE - PRIVATE
8473: |
8474: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
8809: if l_org_id.count <> 0 then
8810: for i in 1..l_org_id.count LOOP
8811: BEGIN
8812:
8813: update zx_taxes_b_tmp
8814: set allow_manual_entry_flag = 'N',
8815: allow_tax_override_flag = 'N',
8816: object_version_number = object_version_number + 1
8817: where content_owner_id = (select party_tax_profile_id from zx_party_tax_profile
8819: and party_type_code='OU')
8820: and record_type_code='MIGRATED';
8821:
8822: EXCEPTION WHEN OTHERS THEN
8823: arp_util_tax.debug('Failed to update zx_taxes_b due to exception in migrate_profile for org:'
8824: ||l_org_id(i)||sqlerrm);
8825: END;
8826:
8827: BEGIN
9072: FROM
9073: (
9074: SELECT
9075: CASE WHEN LENGTHB(codes.tax_code) > 30
9076: THEN SUBSTRB(codes.tax_code, 1, 24) ||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_TAXES_B_S')
9077: ELSE codes.tax_code
9078: END LOOKUP_CODE,
9079: CASE WHEN LENGTHB(codes.tax_code) > 30
9080: THEN codes.tax_code
9358: fnd_global.user_id ,
9359: fnd_global.conc_login_id ,
9360: taxes.tax_id
9361: FROM
9362: zx_taxes_b taxes,
9363: zx_rates_b rates,
9364: fnd_lookup_values flv,
9365: ar_vat_tax_all_b ar_code
9366: WHERE
9406: fnd_global.user_id ,
9407: fnd_global.conc_login_id ,
9408: taxes.tax_id
9409: FROM
9410: zx_taxes_b taxes,
9411: zx_rates_b rates,
9412: fnd_lookup_values flv,
9413: ar_vat_tax_all_b ar_code
9414: WHERE
9453: fnd_global.user_id ,
9454: fnd_global.conc_login_id ,
9455: taxes.tax_id
9456: FROM
9457: zx_taxes_b taxes,
9458: zx_rates_b rates,
9459: fnd_lookup_values flv,
9460: ar_vat_tax_all_b ar_code
9461: WHERE
9504: fnd_global.conc_login_id ,
9505: B.TAX_ID
9506: FROM
9507: FND_LANGUAGES L,
9508: ZX_TAXES_B B,
9509: zx_rates_b rates,
9510: ar_vat_tax_all_b codes,
9511: jl_zz_ar_tx_categ_all categs
9512: WHERE
9553: fnd_global.conc_login_id ,
9554: B.TAX_ID
9555: FROM
9556: FND_LANGUAGES L,
9557: ZX_TAXES_B B,
9558: zx_rates_b rates,
9559: ar_vat_tax_all_b codes,
9560: jl_zz_ar_tx_categ_all categs
9561: WHERE
9602: fnd_global.conc_login_id ,
9603: B.TAX_ID
9604: FROM
9605: FND_LANGUAGES L,
9606: ZX_TAXES_B B,
9607: zx_rates_b rates,
9608: ar_vat_tax_all_b codes,
9609: jl_zz_ar_tx_categ_all categs
9610: WHERE
9651: L.LANGUAGE_CODE,
9652: userenv('LANG')
9653: FROM
9654: FND_LANGUAGES L,
9655: ZX_TAXES_B B -- cartesian join
9656: WHERE
9657: L.INSTALLED_FLAG in ('I', 'B')
9658: AND B.RECORD_TYPE_CODE = 'MIGRATED'
9659: AND not exists
9711: fnd_global.conc_login_id ,
9712: B.TAX_ID
9713: FROM
9714: FND_LANGUAGES L,
9715: ZX_TAXES_B B,
9716: (select rates.tax_regime_code tax_regime_code,
9717: rates.tax tax,
9718: rates.content_owner_id content_owner_id,
9719: categs.description description,
10230: | QCTVQ 204
10231: |
10232: | While createing taxes (create_zx_taxes), tax_precedence is a part
10233: | of grouping criteria. If there're records described above,
10234: | create_zx_taxes failes with unique index zx_taxes_b_u2 violation.
10235: |
10236: |
10237: | SCOPE - PRIVATE
10238: |
10328: l_allow_exceptions_flag
10329: FROM ar_system_parameters_all
10330: WHERE org_id = cursor_rec.party_id;
10331:
10332: UPDATE zx_taxes_b_tmp
10333: SET allow_exemptions_flag = l_allow_exemptions_flag,
10334: allow_exceptions_flag = l_allow_exceptions_flag
10335: WHERE content_owner_id = cursor_rec.party_tax_profile_id
10336: AND record_type_code = 'MIGRATED';