[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
484: ** REGIME NAME : Country Code || '-SALES-TAX-' || Qualifier1 ||'-'|| Qualifier2..
485: ** TAX (for qualifier) : Location segument qualifiers (ie. CITY, COUNTY..)
486: ** TAX (for location tax code) : 'LOCATION'
487: ** STATUS CODE : STANDARD
488: ** RATE (for location tax code) : Derived from ar_vat_tax_all_b.tax_code
489: **
490: ** i.e.
491: **
492: ** REGIME TAX STATUS RATES
1066: -- ****** DEBUG ******
1067:
1068: /*SELECT min(start_date)
1069: INTO l_min_start_date
1070: FROM ar_vat_tax_all_b
1071: WHERE tax_type = 'LOCATION';*/
1072:
1073: l_min_start_date := to_date('01-01-1952','DD-MM-YYYY'); --Bug 5475175
1074:
1965: )
1966: */
1967: SELECT
1968: decode(avt.leasing_flag,'Y',avt.tax_code,'LOCATION') L_TAX, --Bug fix 5147333
1969: (select min(start_date) from ar_vat_tax_all_b where tax_type = 'LOCATION') L_EFFECTIVE_FROM,
1970: NULL L_EFFECTIVE_TO,
1971: zrb.tax_regime_code L_TAX_REGIME_CODE,
1972: 'LOCATION' L_TAX_TYPE_CODE,
1973: --5713986, Update allow_manual_entry_flag, allow_tax_override_flag as 'Y' instead of 'N'
2092: 'CREATE_EXEMPTIONS' L_TAX_EXMPT_CR_MTHD_CD , --Bug 4204464
2093: NULL L_TAX_EXMPT_SOURCE_TAX,
2094: 'N' L_APPLICABLE_BY_DEFAULT_FLAG --Bug 4905771
2095: FROM
2096: ar_vat_tax_all_b avt,
2097: zx_party_tax_profile ptp,
2098: ar_system_parameters_all asp,
2099: gl_sets_of_books gsob,
2100: zx_regimes_b zrb
2379: )
2380: */
2381: SELECT
2382: decode(avt.leasing_flag,'Y',avt.tax_code,'LOCATION') L_TAX, --Bug fix 5147333
2383: (select min(start_date) from ar_vat_tax_all_b where tax_type = 'LOCATION') L_EFFECTIVE_FROM,
2384: NULL L_EFFECTIVE_TO,
2385: zrb.tax_regime_code L_TAX_REGIME_CODE,
2386: 'LOCATION' L_TAX_TYPE_CODE,
2387: --5713986, Update allow_manual_entry_flag,allow_tax_override_flag as 'Y' instead of 'N
2506: 'CREATE_EXEMPTIONS' L_TAX_EXMPT_CR_MTHD_CD , --Bug 4204464
2507: NULL L_TAX_EXMPT_SOURCE_TAX,
2508: 'N' L_APPLICABLE_BY_DEFAULT_FLAG --Bug 4905771
2509: FROM
2510: ar_vat_tax_all_b avt,
2511: zx_party_tax_profile ptp,
2512: ar_system_parameters_all asp,
2513: gl_sets_of_books gsob,
2514: zx_regimes_b zrb
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-%'
3016: AND status.tax = 'LOCATION'
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-%'
3351: AND status.tax = 'LOCATION'
3513: THEN
3514: SELECT min(start_date)
3515: INTO l_min_start_date
3516: FROM ar_system_parameters_all asp,
3517: ar_vat_tax_all_b avtb
3518: WHERE tax_database_view_set = '_A'
3519: AND asp.org_id = avtb.org_id;
3520: ELSE
3521: SELECT min(start_date)
3520: ELSE
3521: SELECT min(start_date)
3522: INTO l_min_start_date
3523: FROM ar_system_parameters_all asp,
3524: ar_vat_tax_all_b avtb
3525: WHERE tax_database_view_set = '_A'
3526: AND asp.org_id = l_org_id
3527: AND asp.org_id = avtb.org_id;
3528:
3675:
3676: SELECT min(start_date)
3677: INTO l_min_start_date
3678: FROM ar_system_parameters_all asp,
3679: ar_vat_tax_all_b avtb
3680: WHERE tax_database_view_set = '_V'
3681: AND asp.org_id = avtb.org_id;
3682:
3683: ELSE
3684:
3685: SELECT min(start_date)
3686: INTO l_min_start_date
3687: FROM ar_system_parameters_all asp,
3688: ar_vat_tax_all_b avtb
3689: WHERE tax_database_view_set = '_V'
3690: AND asp.org_id = l_org_id
3691: AND asp.org_id = avtb.org_id ;
3692:
4105: -- Populate Rates : Taxware and Vertex : Phase I
4106: --
4107: -- Creating tax rate code for each content owners
4108: -- using Tax Code created for vendor integration
4109: -- in AR_VAT_TAX_ALL_B
4110: --
4111: BEGIN
4112: IF L_MULTI_ORG_FLAG = 'Y'
4113: THEN
4417: fnd_global.conc_login_id L_PROGRAM_LOGIN_ID,
4418: DECODE(avt.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
4419: avt.DESCRIPTION DESCRIPTION -- Bug 4705196
4420: FROM
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
4749: fnd_global.conc_login_id L_PROGRAM_LOGIN_ID,
4750: DECODE(avt.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
4751: avt.DESCRIPTION DESCRIPTION -- Bug 4705196
4752: FROM
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
4785: --
4786: -- Populate Rates : Taxware and Vertex : Phase II
4787: --
4788: -- Creating tax rate code for each content owners
4789: -- In this phase we're not using Tax Code in AR_VAT_TAX_ALL_B
4790: -- as Tax Codes are not defined in 11i.
4791: --
4792:
4793: IF L_MULTI_ORG_FLAG = 'Y'
4831: END IF;
4832:
4833: /* --Commenting this out as part of bug 5209436
4834: AND NOT EXISTS (SELECT 1
4835: FROM ar_vat_tax_all_b avt
4836: WHERE avt.org_id = asp.org_id);*/
4837:
4838: IF content_owner_id_rec.count > 0 THEN
4839: FOR k IN 1..content_owner_id_rec.count LOOP
4890: | PROCEDURE
4891: | migrate_ar_vat_tax
4892: |
4893: | IN
4894: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
4895: | AR Tax Codes form for synchronization.
4896: |
4897: | OUT
4898: |
5237: nvl(asp.TAX_USE_CUSTOMER_EXEMPT_FLAG,'N') ALLOW_EXEMPTIONS_FLAG,
5238: 'N' ALLOW_EXCEPTIONS_FLAG --Bug 5505935
5239: FROM
5240: zx_update_criteria_results results,
5241: ar_vat_tax_all_b codes,
5242: zx_party_tax_profile ptp,
5243: ar_system_parameters_all asp --Bug 3985196: Tax Vendor Handling
5244: WHERE
5245: results.tax_code_id = codes.vat_tax_id
5584: nvl(asp.TAX_USE_CUSTOMER_EXEMPT_FLAG,'N') ALLOW_EXEMPTIONS_FLAG,
5585: 'N' ALLOW_EXCEPTIONS_FLAG --Bug 5505935
5586: FROM
5587: zx_update_criteria_results results,
5588: ar_vat_tax_all_b codes,
5589: zx_party_tax_profile ptp,
5590: ar_system_parameters_all asp --Bug 3985196: Tax Vendor Handling
5591: WHERE
5592: results.tax_code_id = codes.vat_tax_id
5935: DECODE(codes.tax_class, 'I', 'INPUT', 'O', 'OUTPUT') L_TAX_CLASS,
5936: DECODE (codes.TAXABLE_BASIS, 'AFTER_EPD', 'STANDARD_TB_DISCOUNT','STANDARD_TB') L_TAXABLE_BASIS_FORMULA,
5937: codes.DESCRIPTION DESCRIPTION -- Bug 4705196
5938: FROM
5939: ar_vat_tax_all_b codes,
5940: zx_party_tax_profile ptp,
5941: ar_system_parameters_all params,
5942: jl_zz_ar_tx_categ_all categs
5943: WHERE
6020: | FUNCTION
6021: | is_update_needed_for_loc_tax (p_tax_id NUMBER) RETURN BOOLEAN
6022: |
6023: | IN
6024: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
6025: | AR Tax Codes form for synchronization.
6026: | OUT
6027: | TRUE : When eBTax entities are needed for data updation.
6028: | FALSE : When eBTax entities are not needed for data updation.
6058: ) RETURN BOOLEAN
6059: AS
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
6072: asp.org_id
6073: INTO l_location_str_id,
6074: l_org_id
6075: FROM ar_system_parameters_all asp,
6076: ar_vat_tax_all_b avt
6077: where avt.org_id = asp.org_id
6078: AND avt.vat_tax_id = nvl(p_tax_id, avt.vat_tax_id)
6079: AND avt.tax_type = 'LOCATION';
6080: ELSE
6082: asp.org_id
6083: INTO l_location_str_id,
6084: l_org_id
6085: FROM ar_system_parameters_all asp,
6086: ar_vat_tax_all_b avt
6087: where avt.org_id = asp.org_id
6088: AND avt.org_id = l_org_id
6089: AND avt.vat_tax_id = nvl(p_tax_id, avt.vat_tax_id)
6090: AND avt.tax_type = 'LOCATION';
6551: | PROCEDURE
6552: | create_zx_status
6553: |
6554: | IN
6555: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
6556: | AR Tax Codes form for synchronization.
6557: |
6558: | OUT
6559: |
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'
6687: AND codes.vat_tax_id = rates.tax_rate_id
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'
6813: AND codes.vat_tax_id = rates.tax_rate_id
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
6945: rates.record_type_code = 'MIGRATED'
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
7089: AND rates.record_type_code = 'MIGRATED'
7123: MINUS
7124: SELECT
7125: global_attribute1
7126: FROM
7127: ar_vat_tax_all_b
7128: WHERE
7129: global_attribute_category = 'JA.TW.ARXSUVAT.VAT_TAX'
7130: )LOOKUPS; --
7131: --YK:8/31/2004: selects unused TW GDF.
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
7239: AND rates.record_type_code = 'MIGRATED'
7274: MINUS
7275: SELECT
7276: global_attribute1
7277: FROM
7278: ar_vat_tax_all_b
7279: WHERE
7280: global_attribute_category = 'JA.TW.ARXSUVAT.VAT_TAX'
7281: )LOOKUPS; --
7282: --YK:8/31/2004: selects unused TW GDF.
7333: | PROCEDURE
7334: | create_zx_taxes
7335: |
7336: | IN
7337: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
7338: | AR Tax Codes form for synchronization.
7339: |
7340: | OUT
7341: |
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
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
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',
8119: 'JL.BR.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
8178: | PROCEDURE
8179: | create_zx_regimes
8180: |
8181: | IN
8182: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
8183: | AR Tax Codes form for synchronization.
8184: |
8185: | OUT
8186: |
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)
8431: 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)
8465: |
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: |
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
8858: | DESCRIPTION
8859: | Updates the following tables with information on tax regime code,
8860: | tax, and status code. They will help us to migrate data in these
8861: | entities in future eBTax migration.
8862: | - AR_VAT_TAX_ALL_B
8863: | - RA_TAX_EXEMPTIONS_ALL (Handled in exemption migration)
8864: | - GL_TAX_OPTIONS (Handled in GL Tax Option migration)
8865: | - RA_ITEM_EXCEPTIONS (Handled in exception migration)
8866: |
8891: |
8892: | MODIFICATION HISTORY
8893: | 09/17/2004 Yoshimichi Konishi Created.
8894: | 05/27/2005 Yoshimichi Konishi Bug 4216592. Build dependency with
8895: | ar_vat_tax_all_b has been resolved.
8896: +==========================================================================*/
8897: PROCEDURE backward_updation AS
8898: type num_type is table of number(15);
8899: type code_type is table of varchar2(50);
8914: l_tax_status_code varchar2(30);
8915: l_co_id number;
8916:
8917: BEGIN
8918: -- backward updation: ar_vat_tax_all_b
8919: SELECT rates.tax_regime_code,
8920: rates.tax,
8921: rates.tax_status_code,
8922: rates.tax_rate_id
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
8933: IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
8933: IN ('JL.AR.ARXSUVAT.AR_VAT_TAX',
8934: 'JL.BR.ARXSUVAT.Tax Information', -- Bug 4868971
8935: 'JL.CO.ARXSUVAT.AR_VAT_TAX');
8936:
8937: arp_util_tax.debug('sel ar_vat_tax_all_b');
8938:
8939: forall i in 1..lc_rate_code_id.last
8940: update ar_vat_tax_all_b
8941: set tax_regime_code = lc_tax_regime_code(i),
8936:
8937: arp_util_tax.debug('sel ar_vat_tax_all_b');
8938:
8939: forall i in 1..lc_rate_code_id.last
8940: update ar_vat_tax_all_b
8941: set tax_regime_code = lc_tax_regime_code(i),
8942: tax = lc_tax(i),
8943: tax_status_code = lc_status_code(i)
8944: where vat_tax_id = lc_rate_code_id(i);
8983:
8984: for cursor_rec in
8985: (
8986: select DISTINCT code.tax_code
8987: FROM AR_VAT_TAX_ALL_B CODE,
8988: AR_SYSTEM_PARAMETERS_ALL PARAM
8989: WHERE code.org_id = param.org_id
8990: -- Bug 4905771
8991: -- Eliminating AR Tax Codes created for tax vendor
9081: ELSE
9082: NULL
9083: END TAG, --Bug 4562058
9084: codes.description DESCRIPTION,
9085: (SELECT MIN(codes.start_date) FROM AR_VAT_TAX_ALL_B WHERE tax_code = cursor_rec.tax_code) START_DATE_ACTIVE
9086: FROM
9087: ar_vat_tax_all_b codes
9088: WHERE
9089: -- Bug 4626074 : Create tax classif code for location
9083: END TAG, --Bug 4562058
9084: codes.description DESCRIPTION,
9085: (SELECT MIN(codes.start_date) FROM AR_VAT_TAX_ALL_B WHERE tax_code = cursor_rec.tax_code) START_DATE_ACTIVE
9086: FROM
9087: ar_vat_tax_all_b codes
9088: WHERE
9089: -- Bug 4626074 : Create tax classif code for location
9090: -- codes.tax_type <> 'LOCATION'
9091: codes.vat_tax_id = NVL(p_tax_id, codes.vat_tax_id)
9158: fnd_global.conc_login_id , -- PROGRAM_LOGIN_ID
9159: codes.set_of_books_id , -- SET_OF_BOOKS_ID
9160: codes.enabled_flag
9161: FROM
9162: ar_vat_tax_all_b codes
9163: WHERE
9164: NOT EXISTS
9165: (SELECT 1
9166: FROM zx_id_tcc_mapping_all zitm
9177: | FUNCTION
9178: | is_update_needed_for_vnd_tax (p_tax_id NUMBER) RETURN BOOLEAN
9179: |
9180: | IN
9181: | p_tax_id : ar_vat_tax_all_b.vat_tax_id is passed when it is called from
9182: | AR Tax Codes form for synchronization.
9183: | OUT
9184: | TRUE : When eBTax entities are needed for data updation.
9185: | FALSE : When eBTax entities are not needed for data updation.
9217: -- ****** CURSORS ******
9218: CURSOR vnd_tax_code_cur (p_tax_id NUMBER) IS
9219: SELECT avt.tax_code
9220: FROM ar_system_parameters_all asp,
9221: ar_vat_tax_all_b avt
9222: 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)
9223: AND avt.vat_tax_id = nvl(p_tax_id, avt.vat_tax_id)
9224: AND asp.default_country = 'US'
9225: AND asp.tax_database_view_set IN ('_A', '_V');
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
9368: AND taxes.TAX_REGIME_CODE = rates.TAX_REGIME_CODE
9369: AND taxes.TAX = rates.TAX
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
9416: AND taxes.TAX_REGIME_CODE = rates.TAX_REGIME_CODE
9417: AND taxes.TAX = rates.TAX
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
9463: AND taxes.TAX_REGIME_CODE = rates.TAX_REGIME_CODE
9464: AND taxes.TAX = rates.TAX
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')
9514: AND B.RECORD_TYPE_CODE = 'MIGRATED'
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')
9563: AND B.RECORD_TYPE_CODE = 'MIGRATED'
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')
9612: AND B.RECORD_TYPE_CODE = 'MIGRATED'
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',
9726: 'JL.BR.ARXSUVAT.AR_VAT_TAX',
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
9782: and rates.tax_status_code = status.tax_status_code
9863: avtt.language ,
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
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
10189: l.installed_flag in ('I', 'B')
10190: AND avtb.vat_tax_id = b.tax_rate_id
10191: AND b.record_type_code = 'MIGRATED'
10356: /* Bug 5248597*/
10357: /*BEGIN
10358: SELECT 'Y' INTO L_LTE_USED FROM DUAL
10359: WHERE EXISTS (select 1
10360: from ar_vat_tax_all_b
10361: where global_attribute_category in ('JL.AR.ARXSUVAT.AR_VAT_TAX',
10362: 'JL.BR.ARXSUVAT.AR_VAT_TAX',
10363: 'JL.CO.ARXSUVAT.AR_VAT_TAX')
10364: );
10372: -- Check if JATW is used
10373: BEGIN
10374: SELECT 'Y' INTO L_JATW_USED FROM DUAL
10375: WHERE EXISTS (select 1
10376: from ar_vat_tax_all_b
10377: where global_attribute_category = 'JA.TW.ARXSUVAT.VAT_TAX'); --YK:B:10/08/2004: Modified.
10378: EXCEPTION
10379: WHEN no_data_found THEN
10380: NULL;
10385: -- Check MIN START DATE
10386: BEGIN
10387: SELECT min(start_date)
10388: INTO L_MIN_START_DATE
10389: FROM ar_vat_tax_all_b;
10390: EXCEPTION
10391: WHEN OTHERS THEN
10392: arp_util_tax.debug('e:pkg_const:l_min_start_date:'||sqlcode || ' : ' || sqlerrm);
10393: END;