[Home] [Help]
391: d PLS_INTEGER;
392: l_temp_id_flex_num fnd_id_flex_segments.id_flex_num%TYPE; --NUMBER;
393: l_temp_seg_num fnd_id_flex_segments.segment_num%TYPE; --NUMBER(15)
394: l_temp_seg_att_type fnd_segment_attribute_values.segment_attribute_type%TYPE; --VARCHAR2(30)
395: l_temp_tax_currency_code ar_system_parameters_all.tax_currency_code%TYPE; --VARCHAR2(15)
396: l_temp_tax_precision ar_system_parameters_all.tax_precision%TYPE; --NUMBER(1)
397: l_temp_tax_mau ar_system_parameters_all.tax_minimum_accountable_unit%TYPE; --NUMBER
398: l_temp_country_code ar_system_parameters_all.default_country%TYPE; --VARCHAR2(60)
399: l_temp_org_id ar_system_parameters_all.org_id%TYPE; --NUMBER(15)
392: l_temp_id_flex_num fnd_id_flex_segments.id_flex_num%TYPE; --NUMBER;
393: l_temp_seg_num fnd_id_flex_segments.segment_num%TYPE; --NUMBER(15)
394: l_temp_seg_att_type fnd_segment_attribute_values.segment_attribute_type%TYPE; --VARCHAR2(30)
395: l_temp_tax_currency_code ar_system_parameters_all.tax_currency_code%TYPE; --VARCHAR2(15)
396: l_temp_tax_precision ar_system_parameters_all.tax_precision%TYPE; --NUMBER(1)
397: l_temp_tax_mau ar_system_parameters_all.tax_minimum_accountable_unit%TYPE; --NUMBER
398: l_temp_country_code ar_system_parameters_all.default_country%TYPE; --VARCHAR2(60)
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)
393: l_temp_seg_num fnd_id_flex_segments.segment_num%TYPE; --NUMBER(15)
394: l_temp_seg_att_type fnd_segment_attribute_values.segment_attribute_type%TYPE; --VARCHAR2(30)
395: l_temp_tax_currency_code ar_system_parameters_all.tax_currency_code%TYPE; --VARCHAR2(15)
396: l_temp_tax_precision ar_system_parameters_all.tax_precision%TYPE; --NUMBER(1)
397: l_temp_tax_mau ar_system_parameters_all.tax_minimum_accountable_unit%TYPE; --NUMBER
398: l_temp_country_code ar_system_parameters_all.default_country%TYPE; --VARCHAR2(60)
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)
394: l_temp_seg_att_type fnd_segment_attribute_values.segment_attribute_type%TYPE; --VARCHAR2(30)
395: l_temp_tax_currency_code ar_system_parameters_all.tax_currency_code%TYPE; --VARCHAR2(15)
396: l_temp_tax_precision ar_system_parameters_all.tax_precision%TYPE; --NUMBER(1)
397: l_temp_tax_mau ar_system_parameters_all.tax_minimum_accountable_unit%TYPE; --NUMBER
398: l_temp_country_code ar_system_parameters_all.default_country%TYPE; --VARCHAR2(60)
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)
395: l_temp_tax_currency_code ar_system_parameters_all.tax_currency_code%TYPE; --VARCHAR2(15)
396: l_temp_tax_precision ar_system_parameters_all.tax_precision%TYPE; --NUMBER(1)
397: l_temp_tax_mau ar_system_parameters_all.tax_minimum_accountable_unit%TYPE; --NUMBER
398: l_temp_country_code ar_system_parameters_all.default_country%TYPE; --VARCHAR2(60)
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)
396: l_temp_tax_precision ar_system_parameters_all.tax_precision%TYPE; --NUMBER(1)
397: l_temp_tax_mau ar_system_parameters_all.tax_minimum_accountable_unit%TYPE; --NUMBER
398: l_temp_country_code ar_system_parameters_all.default_country%TYPE; --VARCHAR2(60)
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
397: l_temp_tax_mau ar_system_parameters_all.tax_minimum_accountable_unit%TYPE; --NUMBER
398: l_temp_country_code ar_system_parameters_all.default_country%TYPE; --VARCHAR2(60)
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);
398: l_temp_country_code ar_system_parameters_all.default_country%TYPE; --VARCHAR2(60)
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:
440: NULL cross_curr_rate_type
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
468: CURSOR loc_rate_csr IS
469: SELECT tax_regime_code,tax,party_tax_profile_id,
470: org_id,location_tax_account,set_of_books_id
471: FROM zx_party_tax_profile ptp,
472: ar_system_parameters_all asp,
473: zx_taxes_b tax
474: WHERE ptp.party_type_code = 'OU'
475: AND ptp.party_id = asp.org_id
476: AND asp.tax_database_view_set = 'O'
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
2187: WHERE avt.org_id = ptp.party_id
2188: AND ptp.party_type_code = 'OU'
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
2602: WHERE avt.org_id = ptp.party_id
2603: AND avt.org_id = l_org_id
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'
3105: AND status.tax_status_code = 'STANDARD'
3106: AND status.content_owner_id = ptp.party_tax_profile_id
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'
3440: AND status.tax_status_code = 'STANDARD'
3441: AND status.content_owner_id = ptp.party_tax_profile_id
3598: CURSOR loc_rate_csr IS
3599: SELECT tax_regime_code,tax,party_tax_profile_id,
3600: org_id,location_tax_account,set_of_books_id
3601: FROM zx_party_tax_profile ptp,
3602: ar_system_parameters_all asp,
3603: zx_taxes_b tax
3604: WHERE ptp.party_type_code = 'OU'
3605: AND ptp.party_id = asp.org_id
3606: AND asp.tax_database_view_set IN ('_A','_V')
3635: IF L_MULTI_ORG_FLAG = 'Y'
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
3642: AND asp.org_id = avtb.org_id;
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;
3657: THEN
3658: SELECT zrb.tax_regime_code
3659: BULK COLLECT INTO tax_regime_code_rec
3660: FROM zx_regimes_b zrb,
3661: ar_system_parameters_all asp
3662: WHERE tax_regime_code LIKE 'US-SALES-TAX-%'
3663: AND TO_CHAR(asp.location_structure_id) = LTRIM(tax_regime_code, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ-_')
3664: AND asp.tax_database_view_set = '_A';
3665: ELSE
3665: ELSE
3666: SELECT zrb.tax_regime_code
3667: BULK COLLECT INTO tax_regime_code_rec
3668: FROM zx_regimes_b zrb,
3669: ar_system_parameters_all asp
3670: WHERE tax_regime_code LIKE 'US-SALES-TAX-%'
3671: AND TO_CHAR(asp.location_structure_id) = LTRIM(tax_regime_code, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ-_')
3672: AND asp.tax_database_view_set = '_A'
3673: AND asp.org_id = l_org_id;
3797: THEN
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 ;
3820: THEN
3821: SELECT zrb.tax_regime_code
3822: BULK COLLECT INTO tax_regime_code_rec
3823: FROM zx_regimes_b zrb,
3824: ar_system_parameters_all asp
3825: WHERE tax_regime_code LIKE 'US-SALES-TAX-%'
3826: AND TO_CHAR(asp.location_structure_id) = LTRIM(tax_regime_code, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ-_')
3827: AND asp.tax_database_view_set = '_V';
3828: ELSE
3829:
3830: SELECT zrb.tax_regime_code
3831: BULK COLLECT INTO tax_regime_code_rec
3832: FROM zx_regimes_b zrb,
3833: ar_system_parameters_all asp
3834: WHERE tax_regime_code LIKE 'US-SALES-TAX-%'
3835: AND TO_CHAR(asp.location_structure_id) = LTRIM(tax_regime_code, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ-_')
3836: AND asp.tax_database_view_set = '_V'
3837: AND asp.org_id = l_org_id ;
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
4403: WHERE ptp.party_type_code = 'OU'
4404: AND ptp.party_id = avt.org_id
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
4735: WHERE ptp.party_type_code = 'OU'
4736: AND ptp.party_id = l_org_id
4781: -- org_id_rec,
4782: -- location_tax_account_rec,
4783: -- set_of_books_id_rec,
4784: -- location_structure_id_rec
4785: -- FROM ar_system_parameters_all asp,
4786: -- zx_party_tax_profile ptp
4787: -- WHERE ptp.party_type_code = 'OU'
4788: -- AND ptp.party_id = asp.org_id
4789: -- AND asp.tax_database_view_set IN ('_A', '_V');
4798: -- org_id_rec,
4799: -- location_tax_account_rec,
4800: -- set_of_books_id_rec,
4801: -- location_structure_id_rec
4802: -- FROM ar_system_parameters_all asp,
4803: -- zx_party_tax_profile ptp
4804: -- WHERE ptp.party_type_code = 'OU'
4805: -- AND ptp.party_id = asp.org_id
4806: -- AND asp.org_id = l_org_id
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
5250: AND results.tax_class = 'OUTPUT'
5251: AND codes.tax_type NOT IN ('TAX_GROUP', 'LOCATION')
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
5605: AND results.tax_class = 'OUTPUT'
5606: AND codes.tax_type NOT IN ('TAX_GROUP', 'LOCATION')
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
5956: codes.tax_type <> 'TAX_GROUP'
5957:
6069: p_tax_type VARCHAR2 DEFAULT NULL
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:
6083: SELECT asp.location_structure_id,
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';
6093: SELECT asp.location_structure_id,
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)
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
6700: AND codes.org_id = ar_sys_op.org_id
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
6826: AND codes.org_id = l_org_id
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'
6958: AND codes.vat_tax_id = rates.tax_rate_id
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'
7102: AND codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
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'
7252: AND codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id)
7675: NULL CROSS_CURRENCY_RATE_TYPE
7676: FROM
7677: ZX_RATES_B RATES,
7678: ZX_UPDATE_CRITERIA_RESULTS results,
7679: AR_SYSTEM_PARAMETERS_ALL ARP,
7680: GL_SETS_OF_BOOKS SOB,
7681: ZX_PARTY_TAX_PROFILE PTP
7682: WHERE
7683: -- Bug 4691005 : 1. removed nvl source_id
8028: NULL CROSS_CURRENCY_RATE_TYPE
8029: FROM
8030: ZX_RATES_B RATES,
8031: ZX_UPDATE_CRITERIA_RESULTS results,
8032: AR_SYSTEM_PARAMETERS_ALL ARP,
8033: GL_SETS_OF_BOOKS SOB,
8034: ZX_PARTY_TAX_PROFILE PTP
8035: WHERE
8036: -- Bug 4691005 : 1. removed nvl source_id
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)
8444: and params.global_attribute13 = lkups.lookup_code
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
9004: -- integration
9228: AS
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'
10334: ELSE 'Y'
10335: END
10336: INTO l_allow_exemptions_flag,
10337: l_allow_exceptions_flag
10338: FROM ar_system_parameters_all
10339: WHERE org_id = cursor_rec.party_id;
10340:
10341: UPDATE zx_taxes_b_tmp
10342: SET allow_exemptions_flag = l_allow_exemptions_flag,