[Home] [Help]
2628:
2629: END IF;
2630: -- ****** Inserting status for taxes (created for qualifier and location tax code) ******
2631: INSERT ALL
2632: INTO ZX_STATUS_B_TMP
2633: (
2634: TAX_STATUS_ID,
2635: TAX_STATUS_CODE,
2636: CONTENT_OWNER_ID,
2672: OBJECT_VERSION_NUMBER
2673: )
2674: VALUES
2675: (
2676: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
2677: l_tax_status_code, --TAX_STATUS_CODE
2678: l_content_owner_id, --CONTENT_OWNER_ID
2679: l_effective_from, --EFFECTIVE_FROM
2680: l_effective_to, --EFFECTIVE_TO
2727: LAST_UPDATE_LOGIN
2728: )
2729: VALUES
2730: (
2731: ZX_STATUS_B_S.NEXTVAL,
2732: USERENV('LANG'),
2733: USERENV('LANG'),
2734: l_tax_status_code,
2735: l_created_by,
2778: fnd_global.conc_request_id l_request_id
2779: FROM zx_taxes_b tax
2780: WHERE tax.tax_regime_code like '%-SALES-TAX-%'
2781: AND NOT EXISTS (SELECT 1
2782: FROM zx_status_b status
2783: WHERE tax.tax_regime_code = status.tax_regime_code
2784: AND tax.tax = status.tax
2785: AND tax.content_owner_id = status.content_owner_id --Bug 4563007
2786: AND status.tax_status_code = 'STANDARD');
3095: fnd_global.conc_login_id L_PROGRAM_LOGIN_ID,
3096: DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
3097: codes.DESCRIPTION DESCRIPTION -- Bug 4705196
3098: FROM
3099: zx_status_b status,
3100: ar_vat_tax_all_b codes,
3101: zx_party_tax_profile ptp,
3102: ar_system_parameters_all asp
3103: WHERE status.tax_regime_code like '%-SALES-TAX-%'
3430: fnd_global.conc_login_id L_PROGRAM_LOGIN_ID,
3431: DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
3432: codes.DESCRIPTION DESCRIPTION -- Bug 4705196
3433: FROM
3434: zx_status_b status,
3435: ar_vat_tax_all_b codes,
3436: zx_party_tax_profile ptp,
3437: ar_system_parameters_all asp
3438: WHERE status.tax_regime_code like '%-SALES-TAX-%'
3946:
3947: -- Populate Status : Taxware and Vertex
3948:
3949: INSERT ALL
3950: INTO ZX_STATUS_B_TMP
3951: (
3952: TAX_STATUS_ID,
3953: TAX_STATUS_CODE,
3954: CONTENT_OWNER_ID,
3990: OBJECT_VERSION_NUMBER
3991: )
3992: VALUES
3993: (
3994: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
3995: l_tax_status_code, --TAX_STATUS_CODE
3996: l_content_owner_id, --CONTENT_OWNER_ID
3997: l_effective_from, --EFFECTIVE_FROM
3998: l_effective_to, --EFFECTIVE_TO
4071: fnd_global.conc_request_id l_request_id
4072: FROM zx_taxes_b ztb
4073: WHERE ztb.tax_regime_code LIKE 'US-SALES-TAX-%'
4074: AND NOT EXISTS (SELECT 1
4075: FROM zx_status_b zsb
4076: WHERE zsb.tax_regime_code = ztb.tax_regime_code
4077: AND zsb.tax = ztb.tax
4078: AND zsb.content_owner_id = ztb.content_owner_id
4079: AND zsb.tax_status_code = 'STANDARD');
6569: |
6570: | OUT
6571: |
6572: | DESCRIPTION
6573: | This routine creates records in zx_status_b/tl grouping by zx_rates_b
6574: |
6575: |
6576: | SCOPE - PRIVATE
6577: |
6592:
6593: -- Status creation excluding LTE related status code
6594: IF L_MULTI_ORG_FLAG = 'Y'
6595: THEN
6596: INSERT INTO ZX_STATUS_B_TMP
6597: (
6598: TAX_STATUS_ID,
6599: TAX_STATUS_CODE,
6600: CONTENT_OWNER_ID,
6637: REQUEST_ID,
6638: OBJECT_VERSION_NUMBER
6639: )
6640: SELECT
6641: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
6642: TAX_STATUS_CODE, --TAX_STATUS_CODE
6643: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
6644: EFFECTIVE_FROM, --EFFECTIVE_FROM
6645: NULL, --EFFECTIVE_TO
6699: AND codes.vat_tax_id = rates.tax_rate_id
6700: AND codes.org_id = ar_sys_op.org_id
6701: -- Eliminating LTE related status codes
6702: AND codes.tax_type NOT IN ('TAX_GROUP', 'LOCATION')
6703: AND not exists (select 1 from zx_status_b
6704: where tax_regime_code = rates.tax_regime_code
6705: and tax = rates.tax
6706: and tax_status_code = rates.tax_status_code
6707: and content_owner_id = rates.content_owner_id
6718: END
6719: ) STATUS;
6720: ELSE
6721:
6722: INSERT INTO ZX_STATUS_B_TMP
6723: (
6724: TAX_STATUS_ID,
6725: TAX_STATUS_CODE,
6726: CONTENT_OWNER_ID,
6763: REQUEST_ID,
6764: OBJECT_VERSION_NUMBER
6765: )
6766: SELECT
6767: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
6768: TAX_STATUS_CODE, --TAX_STATUS_CODE
6769: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
6770: EFFECTIVE_FROM, --EFFECTIVE_FROM
6771: NULL, --EFFECTIVE_TO
6826: AND codes.org_id = l_org_id
6827: AND codes.org_id = ar_sys_op.org_id
6828: -- Eliminating LTE related status codes
6829: AND codes.tax_type NOT IN ('TAX_GROUP', 'LOCATION')
6830: AND not exists (select 1 from zx_status_b
6831: where tax_regime_code = rates.tax_regime_code
6832: and tax = rates.tax
6833: and tax_status_code = rates.tax_status_code
6834: and content_owner_id = rates.content_owner_id
6853: --arp_util_tax.debug('1:');
6854:
6855: /*
6856: -- LTE handling
6857: INSERT INTO ZX_STATUS_B_TMP
6858: (
6859: TAX_STATUS_ID,
6860: TAX_STATUS_CODE,
6861: CONTENT_OWNER_ID,
6898: REQUEST_ID,
6899: OBJECT_VERSION_NUMBER
6900: )
6901: SELECT
6902: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
6903: TAX_STATUS_CODE, --TAX_STATUS_CODE
6904: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
6905: EFFECTIVE_FROM, --EFFECTIVE_FROM
6906: NULL, --EFFECTIVE_TO
6971: --Added following conditions for Sync process
6972: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
6973:
6974: --Rerunability
6975: AND not exists (select 1 from zx_status_b
6976: where tax_regime_code = rates.tax_regime_code
6977: and tax = rates.tax
6978: and tax_status_code = rates.tax_status_code
6979: and content_owner_id = rates.content_owner_id
6993: -- Unused JATW_GOVERNMENT_TAX_TYPE global flexfield atttribute handling
6994: IF L_JATW_USED = 'Y' THEN
6995: IF L_MULTI_ORG_FLAG = 'Y' THEN
6996:
6997: INSERT INTO ZX_STATUS_B_TMP
6998: (
6999: TAX_STATUS_ID,
7000: TAX_STATUS_CODE,
7001: CONTENT_OWNER_ID,
7038: REQUEST_ID,
7039: OBJECT_VERSION_NUMBER
7040: )
7041: SELECT
7042: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
7043: lookups.lookup_code, --TAX_STATUS_CODE
7044: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
7045: EFFECTIVE_FROM, --EFFECTIVE_FROM
7046: NULL, --EFFECTIVE_TO
7105: --Added following conditions for Sync process
7106: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
7107:
7108: --Rerunability
7109: AND not exists (select 1 from zx_status_b
7110: where tax_regime_code = rates.tax_regime_code
7111: and tax = rates.tax
7112: and tax_status_code = rates.tax_status_code
7113: and content_owner_id = rates.content_owner_id
7143: --YK:8/31/2004: selects unused TW GDF.
7144: --Does cartesian join to create all combination for regime, tax, content_onwer_id
7145: --status_code fetched in STATUSES.
7146: ELSE
7147: INSERT INTO ZX_STATUS_B_TMP
7148: (
7149: TAX_STATUS_ID,
7150: TAX_STATUS_CODE,
7151: CONTENT_OWNER_ID,
7188: REQUEST_ID,
7189: OBJECT_VERSION_NUMBER
7190: )
7191: SELECT
7192: ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
7193: lookups.lookup_code, --TAX_STATUS_CODE
7194: CONTENT_OWNER_ID, --CONTENT_OWNER_ID
7195: EFFECTIVE_FROM, --EFFECTIVE_FROM
7196: NULL, --EFFECTIVE_TO
7256: --Added following conditions for Sync process
7257: AND codes.vat_tax_id = nvl(p_tax_id, codes.vat_tax_id)
7258:
7259: --Rerunability
7260: AND not exists (select 1 from zx_status_b
7261: where tax_regime_code = rates.tax_regime_code
7262: and tax = rates.tax
7263: and tax_status_code = rates.tax_status_code
7264: and content_owner_id = rates.content_owner_id
7325: L.LANGUAGE_CODE,
7326: userenv('LANG')
7327: FROM
7328: FND_LANGUAGES L,
7329: ZX_STATUS_B B -- cartesian join
7330: WHERE
7331: L.INSTALLED_FLAG in ('I', 'B')
7332: AND RECORD_TYPE_CODE = 'MIGRATED'
7333: AND not exists
8836: ||l_org_id(i)||sqlerrm);
8837: END;
8838:
8839: BEGIN
8840: update zx_status_b_tmp
8841: set allow_rate_override_flag = 'N',
8842: object_version_number = object_version_number + 1
8843: where content_owner_id = (select party_tax_profile_id from zx_party_tax_profile
8844: where party_id= to_number(l_org_id(i))
8845: and party_type_code='OU')
8846: and record_type_code='MIGRATED';
8847:
8848: EXCEPTION WHEN OTHERS THEN
8849: arp_util_tax.debug('Failed to update zx_status_b due to exception in migrate_profile for org:'
8850: ||l_org_id(i)||sqlerrm);
8851: END;
8852:
8853: END LOOP; -- end l_org_id.count LOOP
9785: fnd_global.conc_login_id,
9786: flv.language,
9787: userenv('LANG')
9788: from zx_rates_b rates,
9789: zx_status_b status,
9790: ar_vat_tax_all_b codes,
9791: fnd_lookup_values flv
9792: where rates.tax_regime_code = status.tax_regime_code
9793: and rates.tax = status.tax
9835: L.LANGUAGE_CODE,
9836: userenv('LANG')
9837: FROM
9838: FND_LANGUAGES L,
9839: ZX_STATUS_B B -- cartesian join
9840: WHERE
9841: L.INSTALLED_FLAG in ('I', 'B')
9842: AND B.RECORD_TYPE_CODE = 'MIGRATED'
9843: AND not exists
10343: allow_exceptions_flag = l_allow_exceptions_flag
10344: WHERE content_owner_id = cursor_rec.party_tax_profile_id
10345: AND record_type_code = 'MIGRATED';
10346:
10347: UPDATE zx_status_b_tmp
10348: SET allow_exemptions_flag = l_allow_exemptions_flag,
10349: allow_exceptions_flag = l_allow_exceptions_flag
10350: WHERE content_owner_id = cursor_rec.party_tax_profile_id
10351: AND record_type_code = 'MIGRATED';