[Home] [Help]
2540:
2541: END IF;
2542: -- ****** Inserting status for taxes (created for qualifier and location tax code) ******
2543: INSERT ALL
2544: INTO ZX_STATUS_B_TMP
2545: (
2546: TAX_STATUS_ID,
2547: TAX_STATUS_CODE,
2548: CONTENT_OWNER_ID,
2584: OBJECT_VERSION_NUMBER
2585: )
2586: VALUES
2587: (
2588: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
2589: l_tax_status_code, --TAX_STATUS_CODE
2590: l_content_owner_id, --CONTENT_OWNER_ID
2591: l_effective_from, --EFFECTIVE_FROM
2592: l_effective_to, --EFFECTIVE_TO
2639: LAST_UPDATE_LOGIN
2640: )
2641: VALUES
2642: (
2643: ZX_STATUS_B_S.NEXTVAL,
2644: USERENV('LANG'),
2645: USERENV('LANG'),
2646: l_tax_status_code,
2647: l_created_by,
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
2696: AND tax.tax = status.tax
2697: AND tax.content_owner_id = status.content_owner_id --Bug 4563007
2698: AND status.tax_status_code = 'STANDARD');
3007: fnd_global.conc_login_id L_PROGRAM_LOGIN_ID,
3008: DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
3009: codes.DESCRIPTION DESCRIPTION -- Bug 4705196
3010: FROM
3011: zx_status_b status,
3012: ar_vat_tax_all_b codes,
3013: zx_party_tax_profile ptp,
3014: ar_system_parameters_all asp
3015: WHERE status.tax_regime_code like '%-SALES-TAX-%'
3342: fnd_global.conc_login_id L_PROGRAM_LOGIN_ID,
3343: DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
3344: codes.DESCRIPTION DESCRIPTION -- Bug 4705196
3345: FROM
3346: zx_status_b status,
3347: ar_vat_tax_all_b codes,
3348: zx_party_tax_profile ptp,
3349: ar_system_parameters_all asp
3350: WHERE status.tax_regime_code like '%-SALES-TAX-%'
3824: -- Populate Status : Taxware and Vertex
3825: IF L_MULTI_ORG_FLAG = 'Y'
3826: THEN
3827: INSERT ALL
3828: INTO ZX_STATUS_B_TMP
3829: (
3830: TAX_STATUS_ID,
3831: TAX_STATUS_CODE,
3832: CONTENT_OWNER_ID,
3868: OBJECT_VERSION_NUMBER
3869: )
3870: VALUES
3871: (
3872: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
3873: l_tax_status_code, --TAX_STATUS_CODE
3874: l_content_owner_id, --CONTENT_OWNER_ID
3875: l_effective_from, --EFFECTIVE_FROM
3876: l_effective_to, --EFFECTIVE_TO
3953: AND TO_CHAR(asp.location_structure_id) = LTRIM(tax_regime_code, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ-_')
3954: AND asp.tax_database_view_set IN ('_A', '_V')
3955: AND asp.org_id = asp.org_id
3956: AND NOT EXISTS (SELECT 1
3957: FROM zx_status_b zsb
3958: WHERE zsb.tax_regime_code = ztb.tax_regime_code
3959: AND zsb.tax = ztb.tax
3960: AND zsb.content_owner_id = -99
3961: AND zsb.tax_status_code = 'STANDARD');
3961: AND zsb.tax_status_code = 'STANDARD');
3962: ELSE
3963:
3964: INSERT ALL
3965: INTO ZX_STATUS_B_TMP
3966: (
3967: TAX_STATUS_ID,
3968: TAX_STATUS_CODE,
3969: CONTENT_OWNER_ID,
4005: OBJECT_VERSION_NUMBER
4006: )
4007: VALUES
4008: (
4009: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
4010: l_tax_status_code, --TAX_STATUS_CODE
4011: l_content_owner_id, --CONTENT_OWNER_ID
4012: l_effective_from, --EFFECTIVE_FROM
4013: l_effective_to, --EFFECTIVE_TO
4091: AND asp.tax_database_view_set IN ('_A', '_V')
4092: AND asp.org_id = l_org_id
4093: AND asp.org_id = asp.org_id
4094: AND NOT EXISTS (SELECT 1
4095: FROM zx_status_b zsb
4096: WHERE zsb.tax_regime_code = ztb.tax_regime_code
4097: AND zsb.tax = ztb.tax
4098: AND zsb.content_owner_id = -99
4099: AND zsb.tax_status_code = 'STANDARD');
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: |
6580:
6581: -- Status creation excluding LTE related status code
6582: IF L_MULTI_ORG_FLAG = 'Y'
6583: THEN
6584: INSERT INTO ZX_STATUS_B_TMP
6585: (
6586: TAX_STATUS_ID,
6587: TAX_STATUS_CODE,
6588: CONTENT_OWNER_ID,
6625: REQUEST_ID,
6626: OBJECT_VERSION_NUMBER
6627: )
6628: SELECT
6629: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
6630: TAX_STATUS_CODE, --TAX_STATUS_CODE
6631: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
6632: EFFECTIVE_FROM, --EFFECTIVE_FROM
6633: NULL, --EFFECTIVE_TO
6687: AND codes.vat_tax_id = rates.tax_rate_id
6688: AND codes.org_id = ar_sys_op.org_id
6689: -- Eliminating LTE related status codes
6690: AND codes.tax_type NOT IN ('TAX_GROUP', 'LOCATION')
6691: AND not exists (select 1 from zx_status_b
6692: where tax_regime_code = rates.tax_regime_code
6693: and tax = rates.tax
6694: and tax_status_code = rates.tax_status_code
6695: and content_owner_id = rates.content_owner_id
6706: END
6707: ) STATUS;
6708: ELSE
6709:
6710: INSERT INTO ZX_STATUS_B_TMP
6711: (
6712: TAX_STATUS_ID,
6713: TAX_STATUS_CODE,
6714: CONTENT_OWNER_ID,
6751: REQUEST_ID,
6752: OBJECT_VERSION_NUMBER
6753: )
6754: SELECT
6755: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
6756: TAX_STATUS_CODE, --TAX_STATUS_CODE
6757: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
6758: EFFECTIVE_FROM, --EFFECTIVE_FROM
6759: NULL, --EFFECTIVE_TO
6814: AND codes.org_id = l_org_id
6815: AND codes.org_id = ar_sys_op.org_id
6816: -- Eliminating LTE related status codes
6817: AND codes.tax_type NOT IN ('TAX_GROUP', 'LOCATION')
6818: AND not exists (select 1 from zx_status_b
6819: where tax_regime_code = rates.tax_regime_code
6820: and tax = rates.tax
6821: and tax_status_code = rates.tax_status_code
6822: and content_owner_id = rates.content_owner_id
6841: --arp_util_tax.debug('1:');
6842:
6843: /*
6844: -- LTE handling
6845: INSERT INTO ZX_STATUS_B_TMP
6846: (
6847: TAX_STATUS_ID,
6848: TAX_STATUS_CODE,
6849: CONTENT_OWNER_ID,
6886: REQUEST_ID,
6887: OBJECT_VERSION_NUMBER
6888: )
6889: SELECT
6890: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
6891: TAX_STATUS_CODE, --TAX_STATUS_CODE
6892: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
6893: EFFECTIVE_FROM, --EFFECTIVE_FROM
6894: NULL, --EFFECTIVE_TO
6959: --Added following conditions for Sync process
6960: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
6961:
6962: --Rerunability
6963: AND not exists (select 1 from zx_status_b
6964: where tax_regime_code = rates.tax_regime_code
6965: and tax = rates.tax
6966: and tax_status_code = rates.tax_status_code
6967: and content_owner_id = rates.content_owner_id
6981: -- Unused JATW_GOVERNMENT_TAX_TYPE global flexfield atttribute handling
6982: IF L_JATW_USED = 'Y' THEN
6983: IF L_MULTI_ORG_FLAG = 'Y' THEN
6984:
6985: INSERT INTO ZX_STATUS_B_TMP
6986: (
6987: TAX_STATUS_ID,
6988: TAX_STATUS_CODE,
6989: CONTENT_OWNER_ID,
7026: REQUEST_ID,
7027: OBJECT_VERSION_NUMBER
7028: )
7029: SELECT
7030: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
7031: lookups.lookup_code, --TAX_STATUS_CODE
7032: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
7033: EFFECTIVE_FROM, --EFFECTIVE_FROM
7034: NULL, --EFFECTIVE_TO
7093: --Added following conditions for Sync process
7094: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
7095:
7096: --Rerunability
7097: AND not exists (select 1 from zx_status_b
7098: where tax_regime_code = rates.tax_regime_code
7099: and tax = rates.tax
7100: and tax_status_code = rates.tax_status_code
7101: and content_owner_id = rates.content_owner_id
7131: --YK:8/31/2004: selects unused TW GDF.
7132: --Does cartesian join to create all combination for regime, tax, content_onwer_id
7133: --status_code fetched in STATUSES.
7134: ELSE
7135: INSERT INTO ZX_STATUS_B_TMP
7136: (
7137: TAX_STATUS_ID,
7138: TAX_STATUS_CODE,
7139: CONTENT_OWNER_ID,
7176: REQUEST_ID,
7177: OBJECT_VERSION_NUMBER
7178: )
7179: SELECT
7180: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
7181: lookups.lookup_code, --TAX_STATUS_CODE
7182: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
7183: EFFECTIVE_FROM, --EFFECTIVE_FROM
7184: NULL, --EFFECTIVE_TO
7244: --Added following conditions for Sync process
7245: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
7246:
7247: --Rerunability
7248: AND not exists (select 1 from zx_status_b
7249: where tax_regime_code = rates.tax_regime_code
7250: and tax = rates.tax
7251: and tax_status_code = rates.tax_status_code
7252: and content_owner_id = rates.content_owner_id
7313: L.LANGUAGE_CODE,
7314: userenv('LANG')
7315: FROM
7316: FND_LANGUAGES L,
7317: ZX_STATUS_B B -- cartesian join
7318: WHERE
7319: L.INSTALLED_FLAG in ('I', 'B')
7320: AND RECORD_TYPE_CODE = 'MIGRATED'
7321: AND not exists
8824: ||l_org_id(i)||sqlerrm);
8825: END;
8826:
8827: BEGIN
8828: update zx_status_b_tmp
8829: set allow_rate_override_flag = 'N',
8830: object_version_number = object_version_number + 1
8831: where content_owner_id = (select party_tax_profile_id from zx_party_tax_profile
8832: where party_id= to_number(l_org_id(i))
8833: and party_type_code='OU')
8834: and record_type_code='MIGRATED';
8835:
8836: EXCEPTION WHEN OTHERS THEN
8837: arp_util_tax.debug('Failed to update zx_status_b due to exception in migrate_profile for org:'
8838: ||l_org_id(i)||sqlerrm);
8839: END;
8840:
8841: END LOOP; -- end l_org_id.count LOOP
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
9781: and rates.tax = status.tax
9823: L.LANGUAGE_CODE,
9824: userenv('LANG')
9825: FROM
9826: FND_LANGUAGES L,
9827: ZX_STATUS_B B -- cartesian join
9828: WHERE
9829: L.INSTALLED_FLAG in ('I', 'B')
9830: AND B.RECORD_TYPE_CODE = 'MIGRATED'
9831: AND not exists
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';
10337:
10338: UPDATE zx_status_b_tmp
10339: SET allow_exemptions_flag = l_allow_exemptions_flag,
10340: allow_exceptions_flag = l_allow_exceptions_flag
10341: WHERE content_owner_id = cursor_rec.party_tax_profile_id
10342: AND record_type_code = 'MIGRATED';