[Home] [Help]
113:
114: /* Bug 4710118 : To Ensure that atleast one rate code gets created with default_rate_flag = 'Y' for a particular
115: Combination of regime , tax , status and Content Owner */
116:
117: update zx_rates_b_tmp rates
118: set rates.default_rate_flag = 'Y' ,
119: rates.default_flg_effective_from = rates.effective_from, --Bug 5104891
120: rates.default_flg_effective_to = rates.effective_to -- Bug 6680676
121: where rates.tax_rate_code in ( select rates1.tax_rate_code from zx_rates_b rates1
117: update zx_rates_b_tmp rates
118: set rates.default_rate_flag = 'Y' ,
119: rates.default_flg_effective_from = rates.effective_from, --Bug 5104891
120: rates.default_flg_effective_to = rates.effective_to -- Bug 6680676
121: where rates.tax_rate_code in ( select rates1.tax_rate_code from zx_rates_b rates1
122: where rates.tax_regime_code = rates1.tax_regime_code
123: and rates.tax = rates1.tax
124: and rates.tax_status_code = rates1.tax_status_code
125: and rates.content_owner_id = rates1.content_owner_id
130: and rownum = 1)
131: /* Not Exists is to prevent the default_rate_flag to be updated to 'Y' for 2 rates under the same combination of
132: regime,tax,status and Content owner */
133:
134: and not exists (select 1 from zx_rates_b rates2
135: where rates2.tax_regime_code = rates.tax_regime_code
136: and rates2.tax = rates.tax
137: and rates2.tax_status_code = rates.tax_status_code
138: and rates2.content_owner_id = rates.content_owner_id
244: INTO p_tax_regime_code,
245: p_tax,
246: p_tax_status_code,
247: p_tax_rate_code
248: FROM zx_rates_b zrb,
249: zx_party_tax_profile zptp
250: WHERE
251: zptp.party_type_code = 'OU'
252: AND zptp.party_tax_profile_id = zrb.content_owner_id
2703: -- zx_rates_tl is created by procedure populate_mls_tables
2704: IF L_MULTI_ORG_FLAG = 'Y'
2705: THEN
2706: INSERT ALL
2707: INTO zx_rates_b_tmp
2708: (
2709: TAX_RATE_ID ,
2710: TAX_RATE_CODE ,
2711: CONTENT_OWNER_ID ,
3029: 'JL.BR.ARXSUVAT.AR_VAT_TAX',
3030: 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
3031: AND (asp.tax_database_view_set = 'O' AND codes.tax_type = 'LOCATION')
3032: AND NOT EXISTS (SELECT 1
3033: FROM zx_rates_b rates
3034: WHERE status.tax_regime_code = rates.tax_regime_code
3035: AND status.tax = rates.tax
3036: AND status.tax_status_code = rates.tax_status_code
3037: AND status.content_owner_id = rates.content_owner_id
3038: AND rates.tax_rate_code = codes.tax_code
3039: );
3040: ELSE
3041: INSERT ALL
3042: INTO zx_rates_b_tmp
3043: (
3044: TAX_RATE_ID ,
3045: TAX_RATE_CODE ,
3046: CONTENT_OWNER_ID ,
3366: 'JL.CO.ARXSUVAT.AR_VAT_TAX'))
3367: AND (asp.tax_database_view_set = 'O' AND codes.tax_type = 'LOCATION')
3368:
3369: AND NOT EXISTS (SELECT 1
3370: FROM zx_rates_b rates
3371: WHERE status.tax_regime_code = rates.tax_regime_code
3372: AND status.tax = rates.tax
3373: AND status.tax_status_code = rates.tax_status_code
3374: AND status.content_owner_id = rates.content_owner_id
4113: THEN
4114:
4115:
4116: INSERT ALL
4117: INTO zx_rates_b_tmp
4118: (
4119: TAX_RATE_ID ,
4120: TAX_RATE_CODE ,
4121: CONTENT_OWNER_ID ,
4438: AND ((avt.tax_code = ztb.tax and avt.tax_type = 'SALES_TAX' ) or (avt.tax_type = 'LOCATION' and ztb.tax='LOCATION'))
4439: --bug#7343019
4440: AND ptp.party_tax_profile_id = ztb.content_owner_id
4441: AND NOT EXISTS (SELECT 1
4442: FROM zx_rates_b rates
4443: WHERE rates.tax_rate_id = avt.vat_tax_id
4444: );
4445: ELSE
4446:
4445: ELSE
4446:
4447:
4448: INSERT ALL
4449: INTO zx_rates_b_tmp
4450: (
4451: TAX_RATE_ID ,
4452: TAX_RATE_CODE ,
4453: CONTENT_OWNER_ID ,
4771: AND ((avt.tax_code = ztb.tax and avt.tax_type = 'SALES_TAX' ) or (avt.tax_type = 'LOCATION' and ztb.tax='LOCATION'))
4772: --bug#7343019
4773: AND ptp.party_tax_profile_id = ztb.content_owner_id
4774: AND NOT EXISTS (SELECT 1
4775: FROM zx_rates_b rates
4776: WHERE rates.tax_rate_id = avt.vat_tax_id
4777: );
4778:
4779: END IF;
4926: IF L_MULTI_ORG_FLAG = 'Y'
4927: THEN
4928:
4929: INSERT ALL
4930: INTO zx_rates_b_tmp
4931: (
4932: TAX_RATE_ID ,
4933: TAX_RATE_CODE ,
4934: CONTENT_OWNER_ID ,
5267: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
5268:
5269: --Rerunability/ID CLASH
5270: AND not exists (select 1
5271: from zx_rates_b rates
5272: where rates.tax_rate_id = nvl(p_tax_id, codes.vat_tax_id)
5273: );
5274: ELSE
5275:
5273: );
5274: ELSE
5275:
5276: INSERT ALL
5277: INTO zx_rates_b_tmp
5278: (
5279: TAX_RATE_ID ,
5280: TAX_RATE_CODE ,
5281: CONTENT_OWNER_ID ,
5615: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
5616:
5617: --Rerunability/ID CLASH
5618: AND not exists (select 1
5619: from zx_rates_b rates
5620: where rates.tax_rate_id = nvl(p_tax_id, codes.vat_tax_id)
5621: );
5622:
5623:
5627:
5628: /* Bug 4708982
5629: IF L_LTE_USED = 'Y' THEN
5630: INSERT ALL
5631: INTO zx_rates_b_tmp
5632: (
5633: TAX_RATE_ID ,
5634: TAX_RATE_CODE ,
5635: CONTENT_OWNER_ID ,
5959:
5960: -- Rerunability/ID CLASH
5961: --BugFix 3605729 added nvl(source_id, in the following condition.
5962: AND not exists (select 1
5963: from zx_rates_b rates
5964: where rates.tax_rate_id = nvl(p_tax_id,codes.vat_tax_id)
5965: );
5966: END IF;
5967: */
5992: avtt.language ,
5993: avtt.source_lang
5994:
5995: FROM ar_vat_tax_all_tl avtt,
5996: zx_rates_b zrb
5997: WHERE avtt.vat_tax_id = zrb.tax_rate_id
5998: AND NOT EXISTS (SELECT 1
5999: FROM zx_rates_tl zrt
6000: WHERE zrt.tax_rate_id = avtt.vat_tax_id
6557: |
6558: | OUT
6559: |
6560: | DESCRIPTION
6561: | This routine creates records in zx_status_b/tl grouping by zx_rates_b
6562: |
6563: |
6564: | SCOPE - PRIVATE
6565: |
6678: NVL(ar_sys_op.TAX_USE_PRODUCT_EXEMPT_FLAG,'N') = 'N')
6679: THEN 'N'
6680: ELSE 'Y'
6681: END ALLOW_EXCEPTIONS_FLAG
6682: FROM ZX_RATES_B rates,
6683: AR_VAT_TAX_ALL_B codes,
6684: AR_SYSTEM_PARAMETERS_ALL ar_sys_op
6685: WHERE
6686: rates.record_type_code = 'MIGRATED'
6804: NVL(ar_sys_op.TAX_USE_PRODUCT_EXEMPT_FLAG,'N') = 'N')
6805: THEN 'N'
6806: ELSE 'Y'
6807: END ALLOW_EXCEPTIONS_FLAG
6808: FROM ZX_RATES_B rates,
6809: AR_VAT_TAX_ALL_B codes,
6810: AR_SYSTEM_PARAMETERS_ALL ar_sys_op
6811: WHERE
6812: rates.record_type_code = 'MIGRATED'
6936: min(categs.start_date_active) EFFECTIVE_FROM, --Effecive is derived from jl_zz_ar_tx_categ
6937: AR_SYS_OP.TAX_USE_PRODUCT_EXEMPT_FLAG tax_use_product_exempt_flag,
6938: AR_SYS_OP.TAX_USE_CUSTOMER_EXEMPT_FLAG tax_use_customer_exempt_flag,
6939: AR_SYS_OP.TAX_USE_LOC_EXC_RATE_FLAG tax_use_loc_exc_rate_flag
6940: FROM ZX_RATES_B rates,
6941: AR_VAT_TAX_ALL_B codes,
6942: AR_SYSTEM_PARAMETERS_ALL ar_sys_op,
6943: jl_zz_ar_tx_categ_all categs
6944: WHERE
7080: AND NVL(ar_sys_op.TAX_USE_PRODUCT_EXEMPT_FLAG,'N') = 'N')
7081: THEN 'N'
7082: ELSE 'Y'
7083: END ALLOW_EXCEPTIONS_FLAG
7084: FROM ZX_RATES_B rates,
7085: AR_VAT_TAX_ALL_B codes,
7086: AR_SYSTEM_PARAMETERS_ALL ar_sys_op
7087: WHERE codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id) --YK:8/30/2004: ID Clash handling
7088: AND codes.global_attribute_category = 'JATW.ARXSUVAT.VAT_TAX' --RP:9/7/2004: Category is different for P2P
7230: AND NVL(ar_sys_op.TAX_USE_PRODUCT_EXEMPT_FLAG,'N') = 'N')
7231: THEN 'N'
7232: ELSE 'Y'
7233: END ALLOW_EXCEPTIONS_FLAG
7234: FROM ZX_RATES_B rates,
7235: AR_VAT_TAX_ALL_B codes,
7236: AR_SYSTEM_PARAMETERS_ALL ar_sys_op
7237: WHERE codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id) --YK:8/30/2004: ID Clash handling
7238: AND codes.global_attribute_category = 'JATW.ARXSUVAT.VAT_TAX' --RP:9/7/2004: Category is different for P2P
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: |
7493: (select codes.tax_type tax_type_code,
7494: rates.tax_regime_code tax_regime_code,
7495: rates.tax tax,
7496: rates.content_owner_id content_owner_id
7497: from zx_rates_b rates, ar_vat_tax_all_b codes
7498: where nvl(rates.source_id, rates.tax_rate_id) = codes.vat_tax_id --ID Clash
7499: AND rates.record_type_code = 'MIGRATED'
7500: AND rates.tax_class = DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT')
7501: group by rates.tax_regime_code, rates.tax, rates.content_owner_id, tax_type
7559: 'N' ,--REGISTRATION_TYPE_RULE_FLAG
7560: 'N' ,--REPORTING_ONLY_FLAG
7561: 'N' ,--AUTO_PRVN_FLAG
7562: CASE WHEN
7563: EXISTS (select 1 from zx_rates_b active_rate
7564: where active_rate.TAX = l_TAX
7565: and active_rate.TAX_REGIME_CODE = l_TAX_REGIME_CODE
7566: and sysdate between active_rate.effective_from
7567: and nvl(active_rate.effective_to,sysdate)) --Could add active_flag in the condition
7591: 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
7592: --BugFix 3493419
7593: DECODE(L_CONTENT_OWNER_ID,
7594: (select min(CONTENT_OWNER_ID)
7595: from zx_rates_b
7596: where tax = l_TAX
7597: and tax_regime_code = l_TAX_REGIME_CODE
7598: and RECORD_TYPE_CODE = 'MIGRATED'),
7599: 'Y',
7661: -- Bug 6006519/5654551. 'User' is not a valid exchange rate type
7662: -- decode(ARP.CROSS_CURRENCY_RATE_TYPE,'User','Corporate',ARP.CROSS_CURRENCY_RATE_TYPE) CROSS_CURRENCY_RATE_TYPE
7663: NULL CROSS_CURRENCY_RATE_TYPE
7664: FROM
7665: ZX_RATES_B RATES,
7666: ZX_UPDATE_CRITERIA_RESULTS results,
7667: AR_SYSTEM_PARAMETERS_ALL ARP,
7668: GL_SETS_OF_BOOKS SOB,
7669: ZX_PARTY_TAX_PROFILE PTP
7846: (select codes.tax_type tax_type_code,
7847: rates.tax_regime_code tax_regime_code,
7848: rates.tax tax,
7849: rates.content_owner_id content_owner_id
7850: from zx_rates_b rates, ar_vat_tax_all_b codes
7851: where nvl(rates.source_id, rates.tax_rate_id) = codes.vat_tax_id --ID Clash
7852: AND rates.record_type_code = 'MIGRATED'
7853: AND rates.tax_class = DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT')
7854: group by rates.tax_regime_code, rates.tax, rates.content_owner_id, tax_type
7912: 'N' ,--REGISTRATION_TYPE_RULE_FLAG
7913: 'N' ,--REPORTING_ONLY_FLAG
7914: 'N' ,--AUTO_PRVN_FLAG
7915: CASE WHEN
7916: EXISTS (select 1 from zx_rates_b active_rate
7917: where active_rate.TAX = l_TAX
7918: and active_rate.TAX_REGIME_CODE = l_TAX_REGIME_CODE
7919: and sysdate between active_rate.effective_from
7920: and nvl(active_rate.effective_to,sysdate)) --Could add active_flag in the condition
7944: 'N' ,--ALLOW_ROUNDING_OVERRIDE_FLAG
7945: --BugFix 3493419
7946: DECODE(L_CONTENT_OWNER_ID,
7947: (select min(CONTENT_OWNER_ID)
7948: from zx_rates_b
7949: where tax = l_TAX
7950: and tax_regime_code = l_TAX_REGIME_CODE
7951: and RECORD_TYPE_CODE = 'MIGRATED'),
7952: 'Y',
8014: -- Bug 6006519/5654551, 'User' is not a valid exchange rate type
8015: -- decode(ARP.CROSS_CURRENCY_RATE_TYPE,'User','Corporate',ARP.CROSS_CURRENCY_RATE_TYPE) CROSS_CURRENCY_RATE_TYPE
8016: NULL CROSS_CURRENCY_RATE_TYPE
8017: FROM
8018: ZX_RATES_B RATES,
8019: ZX_UPDATE_CRITERIA_RESULTS results,
8020: AR_SYSTEM_PARAMETERS_ALL ARP,
8021: GL_SETS_OF_BOOKS SOB,
8022: ZX_PARTY_TAX_PROFILE PTP
8110: rates.tax tax,
8111: rates.content_owner_id content_owner_id,
8112: categs.description description,
8113: codes.global_attribute_category global_attribute_category
8114: from zx_rates_b rates,
8115: ar_vat_tax_all_b codes,
8116: jl_zz_ar_tx_categ_all categs
8117: where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
8118: and codes.global_attribute_category in ('JL.AR.ARXSUVAT.AR_VAT_TAX',
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
8168: AND rates.content_owner_id = tax.content_owner_id
8169: AND rates.tax_rate_id = codes.vat_tax_id
8370: (
8371: SELECT RATES.TAX_REGIME_CODE TAX_REGIME_CODE,
8372: min(RATES.EFFECTIVE_FROM) EFFECTIVE_FROM
8373: FROM
8374: ZX_RATES_B RATES
8375: WHERE
8376: RATES.RECORD_TYPE_CODE = 'MIGRATED'
8377:
8378: --Added following conditions for Sync process
8422: ZX_REGIMES_B B,
8423: (select rates.tax_regime_code tax_regime_code,
8424: lkups.meaning meaning,
8425: codes.global_attribute_category global_attribute_category
8426: from zx_rates_b rates,
8427: ar_vat_tax_all_b codes,
8428: ar_system_parameters_all params,
8429: fnd_lookups lkups
8430: where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
8924: lc_tax_regime_code,
8925: lc_tax,
8926: lc_status_code,
8927: lc_rate_code_id
8928: FROM zx_rates_b rates,
8929: ar_vat_tax_all_b tax_code
8930: WHERE tax_code.vat_tax_id = rates.tax_rate_id
8931: AND rates.record_type_code = 'MIGRATED'
8932: AND tax_code.global_attribute_category
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
9367: taxes.CONTENT_OWNER_ID = rates.CONTENT_OWNER_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
9415: taxes.CONTENT_OWNER_ID = rates.CONTENT_OWNER_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
9462: taxes.CONTENT_OWNER_ID = rates.CONTENT_OWNER_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
9513: L.INSTALLED_FLAG in ('I', 'B')
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
9562: L.INSTALLED_FLAG in ('I', 'B')
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
9611: L.INSTALLED_FLAG in ('I', 'B')
9717: rates.tax tax,
9718: rates.content_owner_id content_owner_id,
9719: categs.description description,
9720: codes.global_attribute_category global_attribute_category
9721: from zx_rates_b rates,
9722: ar_vat_tax_all_b codes,
9723: jl_zz_ar_tx_categ_all categs
9724: where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
9725: and codes.global_attribute_category in ('JL.AR.ARXSUVAT.AR_VAT_TAX',
9772: fnd_global.user_id,
9773: fnd_global.conc_login_id,
9774: flv.language,
9775: userenv('LANG')
9776: from zx_rates_b rates,
9777: zx_status_b status,
9778: ar_vat_tax_all_b codes,
9779: fnd_lookup_values flv
9780: where rates.tax_regime_code = status.tax_regime_code
9864: avtt.source_lang ,
9865: avtb.description
9866: FROM ar_vat_tax_all_tl avtt,
9867: ar_vat_tax_all_b avtb, --Bug 4636694
9868: zx_rates_b zrb
9869: WHERE avtt.vat_tax_id = zrb.tax_rate_id
9870: AND avtt.vat_tax_id = avtb.vat_tax_id
9871: AND NOT EXISTS (SELECT 1
9872: FROM zx_rates_tl zrt
9890: |
9891: | OUT
9892: |
9893: | DESCRIPTION
9894: | This routine inserts a record into zx_rates_b.
9895: |
9896: | SCOPE - PRIVATE
9897: |
9898: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
9920: IS
9921: BEGIN
9922: INSERT ALL
9923: WHEN (NOT EXISTS (SELECT 1
9924: FROM zx_rates_b
9925: WHERE tax_rate_code = p_tax_rate_code
9926: AND content_owner_id = p_content_owner_id
9927: AND active_flag = p_active_flag
9928: AND TRUNC(effective_from) = p_effective_from
9927: AND active_flag = p_active_flag
9928: AND TRUNC(effective_from) = p_effective_from
9929: )
9930: ) THEN
9931: INTO zx_rates_b_tmp
9932: (
9933: TAX_RATE_ID ,
9934: TAX_RATE_CODE ,
9935: CONTENT_OWNER_ID ,
9995: ALLOW_EXCEPTIONS_FLAG
9996: )
9997: VALUES
9998: (
9999: zx_rates_b_s.nextval , --TAX_RATE_ID
10000: p_tax_rate_code , --tax_rate_code
10001: p_content_owner_id , --content_owner_id
10002: p_effective_from , --effective_from
10003: NULL , --effective_to
10108: )
10109: VALUES
10110: (
10111: ZX_ACCOUNTS_S.nextval ,--TAX_ACCOUNT_ID
10112: zx_rates_b_s.nextval ,--TAX_RATE_ID
10113: 'RATES' ,--TAX_ACCOUNT_ENTITY_CODE
10114: p_ledger_id ,--LEDGER_ID
10115: p_org_id ,--ORG_ID
10116: p_tax_account_ccid ,--TAX_ACCOUNT_CCID
10180: userenv('LANG'),
10181: avtb.description
10182: FROM
10183: fnd_languages l,
10184: zx_rates_b b,
10185: ar_system_parameters_all asp,
10186: zx_party_tax_profile ptp,
10187: ar_vat_tax_all_b avtb
10188: WHERE
10306: FOR cursor_rec IN (SELECT party_id,
10307: party_tax_profile_id
10308: FROM zx_party_tax_profile
10309: WHERE party_tax_profile_id IN (SELECT content_owner_id
10310: FROM zx_rates_b
10311: WHERE tax_class = 'OUTPUT'
10312: INTERSECT
10313: SELECT content_owner_id
10314: FROM zx_rates_b
10310: FROM zx_rates_b
10311: WHERE tax_class = 'OUTPUT'
10312: INTERSECT
10313: SELECT content_owner_id
10314: FROM zx_rates_b
10315: WHERE tax_class = 'INPUT')
10316: AND party_type_code = 'OU'
10317: AND record_type_code = 'MIGRATED')
10318: LOOP