[Home] [Help]
78: | PROCEDURE
79: | migrate_ar_tax_code_setup
80: |
81: | IN
82: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
83: | AR Tax Codes form for synchronization.
84: |
85: | OUT
86: |
199: /*===========================================================================+
200: | PROCEDURE
201: | get_r2r_for_ar_taxcode
202: | IN
203: | p_tax_code: varchar2: AR Tax Code (ar_vat_tax_all_b.tax_code)
204: | p_org_id : number : Org ID for AR Tax Code (ar_vat_tax_all_b.org_id)
205: | OUT
206: | p_tax_regime_code : varchar2: Tax Regime Code derived for AR Tax Code
207: | p_tax : varchar2: Tax derived for AR Tax Code
200: | PROCEDURE
201: | get_r2r_for_ar_taxcode
202: | IN
203: | p_tax_code: varchar2: AR Tax Code (ar_vat_tax_all_b.tax_code)
204: | p_org_id : number : Org ID for AR Tax Code (ar_vat_tax_all_b.org_id)
205: | OUT
206: | p_tax_regime_code : varchar2: Tax Regime Code derived for AR Tax Code
207: | p_tax : varchar2: Tax derived for AR Tax Code
208: | p_tax_status_code : varchar2: Tax Status Code derived for AR Tax Code
399: l_temp_org_id ar_system_parameters_all.org_id%TYPE; --NUMBER(15)
400: l_temp_rounding_rule_code ar_system_parameters_all.tax_rounding_rule%TYPE; --VARCHAR2(30)
401: l_temp_tax_invoice_print ar_system_parameters_all.tax_invoice_print%TYPE; --VARCHAR2(30)
402: l_temp_allow_rounding_override ar_system_parameters_all.tax_rounding_allow_override%TYPE; --VARCHAR2(30)
403: --l_temp_tax_account_id ar_vat_tax_all_b.tax_account_id%TYPE; --NUMBER(15)
404: l_temp_seg_name VARCHAR2(30); --Bug 4204464
405: l_temp_appl_col_name VARCHAR2(30);
406:
407: l_min_start_date DATE;
441: FROM fnd_id_flex_structures str,
442: fnd_id_flex_segments segment,
443: fnd_segment_attribute_values qual,
444: ar_system_parameters_all asp,
445: ar_vat_tax_all_b avt,
446: gl_sets_of_books gsob
447: WHERE str.id_flex_code = 'RLOC'
448: AND str.application_id = 222
449: AND str.application_id = segment.application_id
502: ** REGIME NAME : Country Code || '-SALES-TAX-' || Qualifier1 ||'-'|| Qualifier2..
503: ** TAX (for qualifier) : Location segument qualifiers (ie. CITY, COUNTY..)
504: ** TAX (for location tax code) : 'LOCATION'
505: ** STATUS CODE : STANDARD
506: ** RATE (for location tax code) : Derived from ar_vat_tax_all_b.tax_code
507: **
508: ** i.e.
509: **
510: ** REGIME TAX STATUS RATES
1152: -- ****** DEBUG ******
1153:
1154: /*SELECT min(start_date)
1155: INTO l_min_start_date
1156: FROM ar_vat_tax_all_b
1157: WHERE tax_type = 'LOCATION';*/
1158:
1159: l_min_start_date := to_date('01-01-1952','DD-MM-YYYY'); --Bug 5475175
1160:
2051: )
2052: */
2053: SELECT DISTINCT
2054: decode(avt.leasing_flag,'Y',avt.tax_code,'LOCATION') L_TAX, --Bug fix 5147333
2055: (select min(start_date) from ar_vat_tax_all_b where tax_type = 'LOCATION') L_EFFECTIVE_FROM,
2056: NULL L_EFFECTIVE_TO,
2057: zrb.tax_regime_code L_TAX_REGIME_CODE,
2058: 'LOCATION' L_TAX_TYPE_CODE,
2059: --5713986, Update allow_manual_entry_flag, allow_tax_override_flag as 'Y' instead of 'N'
2178: 'CREATE_EXEMPTIONS' L_TAX_EXMPT_CR_MTHD_CD , --Bug 4204464
2179: NULL L_TAX_EXMPT_SOURCE_TAX,
2180: 'N' L_APPLICABLE_BY_DEFAULT_FLAG --Bug 4905771
2181: FROM
2182: ar_vat_tax_all_b avt,
2183: zx_party_tax_profile ptp,
2184: ar_system_parameters_all asp,
2185: gl_sets_of_books gsob,
2186: zx_regimes_b zrb
2466: )
2467: */
2468: SELECT DISTINCT
2469: decode(avt.leasing_flag,'Y',avt.tax_code,'LOCATION') L_TAX, --Bug fix 5147333
2470: (select min(start_date) from ar_vat_tax_all_b where tax_type = 'LOCATION') L_EFFECTIVE_FROM,
2471: NULL L_EFFECTIVE_TO,
2472: zrb.tax_regime_code L_TAX_REGIME_CODE,
2473: 'LOCATION' L_TAX_TYPE_CODE,
2474: --5713986, Update allow_manual_entry_flag,allow_tax_override_flag as 'Y' instead of 'N
2593: 'CREATE_EXEMPTIONS' L_TAX_EXMPT_CR_MTHD_CD , --Bug 4204464
2594: NULL L_TAX_EXMPT_SOURCE_TAX,
2595: 'N' L_APPLICABLE_BY_DEFAULT_FLAG --Bug 4905771
2596: FROM
2597: ar_vat_tax_all_b avt,
2598: zx_party_tax_profile ptp,
2599: ar_system_parameters_all asp,
2600: gl_sets_of_books gsob,
2601: zx_regimes_b zrb
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-%'
3104: AND status.tax = 'LOCATION'
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-%'
3439: AND status.tax = 'LOCATION'
3636: THEN
3637: SELECT min(start_date)
3638: INTO l_min_start_date
3639: FROM ar_system_parameters_all asp,
3640: ar_vat_tax_all_b avtb
3641: WHERE tax_database_view_set = '_A'
3642: AND asp.org_id = avtb.org_id;
3643: ELSE
3644: SELECT min(start_date)
3643: ELSE
3644: SELECT min(start_date)
3645: INTO l_min_start_date
3646: FROM ar_system_parameters_all asp,
3647: ar_vat_tax_all_b avtb
3648: WHERE tax_database_view_set = '_A'
3649: AND asp.org_id = l_org_id
3650: AND asp.org_id = avtb.org_id;
3651:
3798:
3799: SELECT min(start_date)
3800: INTO l_min_start_date
3801: FROM ar_system_parameters_all asp,
3802: ar_vat_tax_all_b avtb
3803: WHERE tax_database_view_set = '_V'
3804: AND asp.org_id = avtb.org_id;
3805:
3806: ELSE
3807:
3808: SELECT min(start_date)
3809: INTO l_min_start_date
3810: FROM ar_system_parameters_all asp,
3811: ar_vat_tax_all_b avtb
3812: WHERE tax_database_view_set = '_V'
3813: AND asp.org_id = l_org_id
3814: AND asp.org_id = avtb.org_id ;
3815:
4082: -- Populate Rates : Taxware and Vertex : Phase I
4083: --
4084: -- Creating tax rate code for each content owners
4085: -- using Tax Code created for vendor integration
4086: -- in AR_VAT_TAX_ALL_B
4087: --
4088: BEGIN
4089: IF L_MULTI_ORG_FLAG = 'Y'
4090: THEN
4394: fnd_global.conc_login_id L_PROGRAM_LOGIN_ID,
4395: DECODE(avt.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
4396: avt.DESCRIPTION DESCRIPTION -- Bug 4705196
4397: FROM
4398: ar_vat_tax_all_b avt,
4399: zx_party_tax_profile ptp,
4400: ar_system_parameters_all asp,
4401: zx_regimes_b zrb,
4402: zx_taxes_b ztb
4726: fnd_global.conc_login_id L_PROGRAM_LOGIN_ID,
4727: DECODE(avt.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
4728: avt.DESCRIPTION DESCRIPTION -- Bug 4705196
4729: FROM
4730: ar_vat_tax_all_b avt,
4731: zx_party_tax_profile ptp,
4732: ar_system_parameters_all asp,
4733: zx_regimes_b zrb,
4734: zx_taxes_b ztb
4762: --
4763: -- Populate Rates : Taxware and Vertex : Phase II
4764: --
4765: -- Creating tax rate code for each content owners
4766: -- In this phase we're not using Tax Code in AR_VAT_TAX_ALL_B
4767: -- as Tax Codes are not defined in 11i.
4768: --
4769:
4770: -- Commenting the code for 12847663
4810: -- END IF;
4811:
4812: /* --Commenting this out as part of bug 5209436
4813: AND NOT EXISTS (SELECT 1
4814: FROM ar_vat_tax_all_b avt
4815: WHERE avt.org_id = asp.org_id);*/
4816:
4817: -- IF content_owner_id_rec.count > 0 THEN
4818: -- FOR k IN 1..content_owner_id_rec.count LOOP
4886: | PROCEDURE
4887: | migrate_ar_vat_tax
4888: |
4889: | IN
4890: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
4891: | AR Tax Codes form for synchronization.
4892: |
4893: | OUT
4894: |
5241: nvl(asp.TAX_USE_CUSTOMER_EXEMPT_FLAG,'N') ALLOW_EXEMPTIONS_FLAG,
5242: 'N' ALLOW_EXCEPTIONS_FLAG --Bug 5505935
5243: FROM
5244: zx_update_criteria_results results,
5245: ar_vat_tax_all_b codes,
5246: zx_party_tax_profile ptp,
5247: ar_system_parameters_all asp --Bug 3985196: Tax Vendor Handling
5248: WHERE
5249: results.tax_code_id = codes.vat_tax_id
5596: nvl(asp.TAX_USE_CUSTOMER_EXEMPT_FLAG,'N') ALLOW_EXEMPTIONS_FLAG,
5597: 'N' ALLOW_EXCEPTIONS_FLAG --Bug 5505935
5598: FROM
5599: zx_update_criteria_results results,
5600: ar_vat_tax_all_b codes,
5601: zx_party_tax_profile ptp,
5602: ar_system_parameters_all asp --Bug 3985196: Tax Vendor Handling
5603: WHERE
5604: results.tax_code_id = codes.vat_tax_id
5947: DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
5948: DECODE (codes.TAXABLE_BASIS, 'AFTER_EPD', 'STANDARD_TB_DISCOUNT','STANDARD_TB') L_TAXABLE_BASIS_FORMULA,
5949: codes.DESCRIPTION DESCRIPTION -- Bug 4705196
5950: FROM
5951: ar_vat_tax_all_b codes,
5952: zx_party_tax_profile ptp,
5953: ar_system_parameters_all params,
5954: jl_zz_ar_tx_categ_all categs
5955: WHERE
6032: | FUNCTION
6033: | is_update_needed_for_loc_tax (p_tax_id NUMBER) RETURN BOOLEAN
6034: |
6035: | IN
6036: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
6037: | AR Tax Codes form for synchronization.
6038: | OUT
6039: | TRUE : When eBTax entities are needed for data updation.
6040: | FALSE : When eBTax entities are not needed for data updation.
6070: ) RETURN BOOLEAN
6071: AS
6072: -- ****** VARIABLES ******
6073: l_location_str_id ar_system_parameters_all.location_structure_id%TYPE;
6074: l_org_id ar_vat_tax_all_b.org_id%TYPE;
6075: l_tax_regime_code zx_regimes_b.tax_regime_code%TYPE;
6076: l_tax zx_taxes_b.tax%TYPE;
6077:
6078: BEGIN
6084: asp.org_id
6085: INTO l_location_str_id,
6086: l_org_id
6087: FROM ar_system_parameters_all asp,
6088: ar_vat_tax_all_b avt
6089: where avt.org_id = asp.org_id
6090: AND avt.vat_tax_id = nvl(p_tax_id, avt.vat_tax_id)
6091: AND avt.tax_type = 'LOCATION';
6092: ELSE
6094: asp.org_id
6095: INTO l_location_str_id,
6096: l_org_id
6097: FROM ar_system_parameters_all asp,
6098: ar_vat_tax_all_b avt
6099: where avt.org_id = asp.org_id
6100: AND avt.org_id = l_org_id
6101: AND avt.vat_tax_id = nvl(p_tax_id, avt.vat_tax_id)
6102: AND avt.tax_type = 'LOCATION';
6563: | PROCEDURE
6564: | create_zx_status
6565: |
6566: | IN
6567: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
6568: | AR Tax Codes form for synchronization.
6569: |
6570: | OUT
6571: |
6691: THEN 'N'
6692: ELSE 'Y'
6693: END ALLOW_EXCEPTIONS_FLAG
6694: FROM ZX_RATES_B rates,
6695: AR_VAT_TAX_ALL_B codes,
6696: AR_SYSTEM_PARAMETERS_ALL ar_sys_op
6697: WHERE
6698: rates.record_type_code = 'MIGRATED'
6699: AND codes.vat_tax_id = rates.tax_rate_id
6817: THEN 'N'
6818: ELSE 'Y'
6819: END ALLOW_EXCEPTIONS_FLAG
6820: FROM ZX_RATES_B rates,
6821: AR_VAT_TAX_ALL_B codes,
6822: AR_SYSTEM_PARAMETERS_ALL ar_sys_op
6823: WHERE
6824: rates.record_type_code = 'MIGRATED'
6825: AND codes.vat_tax_id = rates.tax_rate_id
6949: AR_SYS_OP.TAX_USE_PRODUCT_EXEMPT_FLAG tax_use_product_exempt_flag,
6950: AR_SYS_OP.TAX_USE_CUSTOMER_EXEMPT_FLAG tax_use_customer_exempt_flag,
6951: AR_SYS_OP.TAX_USE_LOC_EXC_RATE_FLAG tax_use_loc_exc_rate_flag
6952: FROM ZX_RATES_B rates,
6953: AR_VAT_TAX_ALL_B codes,
6954: AR_SYSTEM_PARAMETERS_ALL ar_sys_op,
6955: jl_zz_ar_tx_categ_all categs
6956: WHERE
6957: rates.record_type_code = 'MIGRATED'
7093: THEN 'N'
7094: ELSE 'Y'
7095: END ALLOW_EXCEPTIONS_FLAG
7096: FROM ZX_RATES_B rates,
7097: AR_VAT_TAX_ALL_B codes,
7098: AR_SYSTEM_PARAMETERS_ALL ar_sys_op
7099: WHERE codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id) --YK:8/30/2004: ID Clash handling
7100: AND codes.global_attribute_category = 'JATW.ARXSUVAT.VAT_TAX' --RP:9/7/2004: Category is different for P2P
7101: AND rates.record_type_code = 'MIGRATED'
7135: MINUS
7136: SELECT
7137: global_attribute1
7138: FROM
7139: ar_vat_tax_all_b
7140: WHERE
7141: global_attribute_category = 'JA.TW.ARXSUVAT.VAT_TAX'
7142: )LOOKUPS; --
7143: --YK:8/31/2004: selects unused TW GDF.
7243: THEN 'N'
7244: ELSE 'Y'
7245: END ALLOW_EXCEPTIONS_FLAG
7246: FROM ZX_RATES_B rates,
7247: AR_VAT_TAX_ALL_B codes,
7248: AR_SYSTEM_PARAMETERS_ALL ar_sys_op
7249: WHERE codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id) --YK:8/30/2004: ID Clash handling
7250: AND codes.global_attribute_category = 'JATW.ARXSUVAT.VAT_TAX' --RP:9/7/2004: Category is different for P2P
7251: AND rates.record_type_code = 'MIGRATED'
7286: MINUS
7287: SELECT
7288: global_attribute1
7289: FROM
7290: ar_vat_tax_all_b
7291: WHERE
7292: global_attribute_category = 'JA.TW.ARXSUVAT.VAT_TAX'
7293: )LOOKUPS; --
7294: --YK:8/31/2004: selects unused TW GDF.
7345: | PROCEDURE
7346: | create_zx_taxes
7347: |
7348: | IN
7349: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
7350: | AR Tax Codes form for synchronization.
7351: |
7352: | OUT
7353: |
7505: (select codes.tax_type tax_type_code,
7506: rates.tax_regime_code tax_regime_code,
7507: rates.tax tax,
7508: rates.content_owner_id content_owner_id
7509: from zx_rates_b rates, ar_vat_tax_all_b codes
7510: where nvl(rates.source_id, rates.tax_rate_id) = codes.vat_tax_id --ID Clash
7511: AND rates.record_type_code = 'MIGRATED'
7512: AND rates.tax_class = DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT')
7513: group by rates.tax_regime_code, rates.tax, rates.content_owner_id, tax_type
7858: (select codes.tax_type tax_type_code,
7859: rates.tax_regime_code tax_regime_code,
7860: rates.tax tax,
7861: rates.content_owner_id content_owner_id
7862: from zx_rates_b rates, ar_vat_tax_all_b codes
7863: where nvl(rates.source_id, rates.tax_rate_id) = codes.vat_tax_id --ID Clash
7864: AND rates.record_type_code = 'MIGRATED'
7865: AND rates.tax_class = DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT')
7866: group by rates.tax_regime_code, rates.tax, rates.content_owner_id, tax_type
8123: rates.content_owner_id content_owner_id,
8124: categs.description description,
8125: codes.global_attribute_category global_attribute_category
8126: from zx_rates_b rates,
8127: ar_vat_tax_all_b codes,
8128: jl_zz_ar_tx_categ_all categs
8129: where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
8130: and codes.global_attribute_category in ('JL.AR.ARXSUVAT.AR_VAT_TAX',
8131: 'JL.BR.ARXSUVAT.AR_VAT_TAX',
8173:
8174: --Bug Fix 5691957
8175: UPDATE zx_taxes_b_tmp tax SET taxable_basis_rule_flag = 'Y' WHERE
8176: EXISTS
8177: (SELECT 1 FROM zx_rates_b rates,ar_vat_tax_all_b codes
8178: WHERE rates.tax_regime_code = tax.tax_regime_code
8179: AND rates.tax = tax.tax
8180: AND rates.content_owner_id = tax.content_owner_id
8181: AND rates.tax_rate_id = codes.vat_tax_id
8190: | PROCEDURE
8191: | create_zx_regimes
8192: |
8193: | IN
8194: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
8195: | AR Tax Codes form for synchronization.
8196: |
8197: | OUT
8198: |
8435: (select rates.tax_regime_code tax_regime_code,
8436: lkups.meaning meaning,
8437: codes.global_attribute_category global_attribute_category
8438: from zx_rates_b rates,
8439: ar_vat_tax_all_b codes,
8440: ar_system_parameters_all params,
8441: fnd_lookups lkups
8442: where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
8443: AND decode(l_multi_org_flag,'N',l_org_id,codes.org_id) = decode(l_multi_org_flag,'N',l_org_id,params.org_id)
8477: |
8478: | DESCRIPTION
8479: | This routine processes AR Tax_setup related fnd_lookups and creates
8480: | appropriate ZX lookups in FND_LOOKUPS.
8481: | AR_VAT_TAX_ALL_B.VAT_TRANSACTION_TYPE --> ZX_RATES.VAT_TRANSACTION_TYPE
8482: | AR_VAT_TAX_ALL_B.TAX_TYPE --> ZX_TAXES_B.TAX_TYPE_CODE
8483: |
8484: | SCOPE - PRIVATE
8485: |
8478: | DESCRIPTION
8479: | This routine processes AR Tax_setup related fnd_lookups and creates
8480: | appropriate ZX lookups in FND_LOOKUPS.
8481: | AR_VAT_TAX_ALL_B.VAT_TRANSACTION_TYPE --> ZX_RATES.VAT_TRANSACTION_TYPE
8482: | AR_VAT_TAX_ALL_B.TAX_TYPE --> ZX_TAXES_B.TAX_TYPE_CODE
8483: |
8484: | SCOPE - PRIVATE
8485: |
8486: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
8870: | DESCRIPTION
8871: | Updates the following tables with information on tax regime code,
8872: | tax, and status code. They will help us to migrate data in these
8873: | entities in future eBTax migration.
8874: | - AR_VAT_TAX_ALL_B
8875: | - RA_TAX_EXEMPTIONS_ALL (Handled in exemption migration)
8876: | - GL_TAX_OPTIONS (Handled in GL Tax Option migration)
8877: | - RA_ITEM_EXCEPTIONS (Handled in exception migration)
8878: |
8903: |
8904: | MODIFICATION HISTORY
8905: | 09/17/2004 Yoshimichi Konishi Created.
8906: | 05/27/2005 Yoshimichi Konishi Bug 4216592. Build dependency with
8907: | ar_vat_tax_all_b has been resolved.
8908: +==========================================================================*/
8909: PROCEDURE backward_updation AS
8910: type num_type is table of number(15);
8911: type code_type is table of varchar2(50);
8926: l_tax_status_code varchar2(30);
8927: l_co_id number;
8928:
8929: BEGIN
8930: -- backward updation: ar_vat_tax_all_b
8931: SELECT rates.tax_regime_code,
8932: rates.tax,
8933: rates.tax_status_code,
8934: rates.tax_rate_id
8937: lc_tax,
8938: lc_status_code,
8939: lc_rate_code_id
8940: FROM zx_rates_b rates,
8941: ar_vat_tax_all_b tax_code
8942: WHERE tax_code.vat_tax_id = rates.tax_rate_id
8943: AND rates.record_type_code = 'MIGRATED'
8944: AND tax_code.global_attribute_category
8945: IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
8945: IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
8946: 'JL.BR.ARXSUVAT.Tax Information', -- Bug 4868971
8947: 'JL.CO.ARXSUVAT.AR_VAT_TAX');
8948:
8949: arp_util_tax.debug('sel ar_vat_tax_all_b');
8950:
8951: forall i in 1..lc_rate_code_id.last
8952: update ar_vat_tax_all_b
8953: set tax_regime_code = lc_tax_regime_code(i),
8948:
8949: arp_util_tax.debug('sel ar_vat_tax_all_b');
8950:
8951: forall i in 1..lc_rate_code_id.last
8952: update ar_vat_tax_all_b
8953: set tax_regime_code = lc_tax_regime_code(i),
8954: tax = lc_tax(i),
8955: tax_status_code = lc_status_code(i)
8956: where vat_tax_id = lc_rate_code_id(i);
8995:
8996: for cursor_rec in
8997: (
8998: select DISTINCT code.tax_code
8999: FROM AR_VAT_TAX_ALL_B CODE,
9000: AR_SYSTEM_PARAMETERS_ALL PARAM
9001: WHERE code.org_id = param.org_id
9002: -- Bug 4905771
9003: -- Eliminating AR Tax Codes created for tax vendor
9093: ELSE
9094: NULL
9095: END TAG, --Bug 4562058
9096: codes.description DESCRIPTION,
9097: (SELECT MIN(codes.start_date) FROM AR_VAT_TAX_ALL_B WHERE tax_code = cursor_rec.tax_code) START_DATE_ACTIVE
9098: FROM
9099: ar_vat_tax_all_b codes
9100: WHERE
9101: -- Bug 4626074 : Create tax classif code for location
9095: END TAG, --Bug 4562058
9096: codes.description DESCRIPTION,
9097: (SELECT MIN(codes.start_date) FROM AR_VAT_TAX_ALL_B WHERE tax_code = cursor_rec.tax_code) START_DATE_ACTIVE
9098: FROM
9099: ar_vat_tax_all_b codes
9100: WHERE
9101: -- Bug 4626074 : Create tax classif code for location
9102: -- codes.tax_type <> 'LOCATION'
9103: codes.vat_tax_id = NVL(p_tax_id, codes.vat_tax_id)
9170: fnd_global.conc_login_id , -- PROGRAM_LOGIN_ID
9171: codes.set_of_books_id , -- SET_OF_BOOKS_ID
9172: codes.enabled_flag
9173: FROM
9174: ar_vat_tax_all_b codes
9175: WHERE
9176: NOT EXISTS
9177: (SELECT 1
9178: FROM zx_id_tcc_mapping_all zitm
9189: | FUNCTION
9190: | is_update_needed_for_vnd_tax (p_tax_id NUMBER) RETURN BOOLEAN
9191: |
9192: | IN
9193: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
9194: | AR Tax Codes form for synchronization.
9195: | OUT
9196: | TRUE : When eBTax entities are needed for data updation.
9197: | FALSE : When eBTax entities are not needed for data updation.
9229: -- ****** CURSORS ******
9230: CURSOR vnd_tax_code_cur (p_tax_id NUMBER) IS
9231: SELECT avt.tax_code
9232: FROM ar_system_parameters_all asp,
9233: ar_vat_tax_all_b avt
9234: WHERE decode(l_multi_org_flag,'N',l_org_id,avt.org_id) = decode(l_multi_org_flag,'N',l_org_id,asp.org_id)
9235: AND avt.vat_tax_id = nvl(p_tax_id, avt.vat_tax_id)
9236: AND asp.default_country = 'US'
9237: AND asp.tax_database_view_set IN ('_A', '_V');
9373: FROM
9374: zx_taxes_b taxes,
9375: zx_rates_b rates,
9376: fnd_lookup_values flv,
9377: ar_vat_tax_all_b ar_code
9378: WHERE
9379: taxes.CONTENT_OWNER_ID = rates.CONTENT_OWNER_ID
9380: AND taxes.TAX_REGIME_CODE = rates.TAX_REGIME_CODE
9381: AND taxes.TAX = rates.TAX
9421: FROM
9422: zx_taxes_b taxes,
9423: zx_rates_b rates,
9424: fnd_lookup_values flv,
9425: ar_vat_tax_all_b ar_code
9426: WHERE
9427: taxes.CONTENT_OWNER_ID = rates.CONTENT_OWNER_ID
9428: AND taxes.TAX_REGIME_CODE = rates.TAX_REGIME_CODE
9429: AND taxes.TAX = rates.TAX
9468: FROM
9469: zx_taxes_b taxes,
9470: zx_rates_b rates,
9471: fnd_lookup_values flv,
9472: ar_vat_tax_all_b ar_code
9473: WHERE
9474: taxes.CONTENT_OWNER_ID = rates.CONTENT_OWNER_ID
9475: AND taxes.TAX_REGIME_CODE = rates.TAX_REGIME_CODE
9476: AND taxes.TAX = rates.TAX
9518: FROM
9519: FND_LANGUAGES L,
9520: ZX_TAXES_B B,
9521: zx_rates_b rates,
9522: ar_vat_tax_all_b codes,
9523: jl_zz_ar_tx_categ_all categs
9524: WHERE
9525: L.INSTALLED_FLAG in ('I', 'B')
9526: AND B.RECORD_TYPE_CODE = 'MIGRATED'
9567: FROM
9568: FND_LANGUAGES L,
9569: ZX_TAXES_B B,
9570: zx_rates_b rates,
9571: ar_vat_tax_all_b codes,
9572: jl_zz_ar_tx_categ_all categs
9573: WHERE
9574: L.INSTALLED_FLAG in ('I', 'B')
9575: AND B.RECORD_TYPE_CODE = 'MIGRATED'
9616: FROM
9617: FND_LANGUAGES L,
9618: ZX_TAXES_B B,
9619: zx_rates_b rates,
9620: ar_vat_tax_all_b codes,
9621: jl_zz_ar_tx_categ_all categs
9622: WHERE
9623: L.INSTALLED_FLAG in ('I', 'B')
9624: AND B.RECORD_TYPE_CODE = 'MIGRATED'
9730: rates.content_owner_id content_owner_id,
9731: categs.description description,
9732: codes.global_attribute_category global_attribute_category
9733: from zx_rates_b rates,
9734: ar_vat_tax_all_b codes,
9735: jl_zz_ar_tx_categ_all categs
9736: where codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
9737: and codes.global_attribute_category in ('JL.AR.ARXSUVAT.AR_VAT_TAX',
9738: 'JL.BR.ARXSUVAT.AR_VAT_TAX',
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
9794: and rates.tax_status_code = status.tax_status_code
9877: avtt.language ,
9878: avtt.source_lang ,
9879: avtb.description
9880: FROM zx_rates_b b,
9881: ar_vat_tax_all_b avtb,
9882: ar_vat_tax_all_tl avtt
9883: WHERE avtb.vat_tax_id = b.tax_rate_id
9884: AND avtb.vat_tax_id = avtt.vat_tax_id
9885: AND b.record_type_code = 'MIGRATED'
10365: /* Bug 5248597*/
10366: /*BEGIN
10367: SELECT 'Y' INTO L_LTE_USED FROM DUAL
10368: WHERE EXISTS (select 1
10369: from ar_vat_tax_all_b
10370: where global_attribute_category in ('JL.AR.ARXSUVAT.AR_VAT_TAX',
10371: 'JL.BR.ARXSUVAT.AR_VAT_TAX',
10372: 'JL.CO.ARXSUVAT.AR_VAT_TAX')
10373: );
10381: -- Check if JATW is used
10382: BEGIN
10383: SELECT 'Y' INTO L_JATW_USED FROM DUAL
10384: WHERE EXISTS (select 1
10385: from ar_vat_tax_all_b
10386: where global_attribute_category = 'JA.TW.ARXSUVAT.VAT_TAX'); --YK:B:10/08/2004: Modified.
10387: EXCEPTION
10388: WHEN no_data_found THEN
10389: NULL;
10394: -- Check MIN START DATE
10395: BEGIN
10396: SELECT min(start_date)
10397: INTO L_MIN_START_DATE
10398: FROM ar_vat_tax_all_b;
10399: EXCEPTION
10400: WHEN OTHERS THEN
10401: arp_util_tax.debug('e:pkg_const:l_min_start_date:'||sqlcode || ' : ' || sqlerrm);
10402: END;