[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cat.tax_code,
NULL,
NULL
FROM jl_zz_ar_tx_cat_dtl cat
WHERE cat.tax_category_id = p_tax_category_id
AND p_trx_date <= cat.end_date_active
AND p_trx_date >= NVL(cat.start_date_active,p_trx_date)
UNION
SELECT cat.tax_code,
NULL,
NULL
FROM jl_zz_ar_tx_categ cat
WHERE cat.tax_category_id = p_tax_category_id
AND p_trx_date <= cat.end_date_active
AND p_trx_date >= NVL(cat.start_date_active,p_trx_date);
SELECT si.tax_code,
NULL,
si.inventory_item_id
FROM mtl_system_items si
WHERE si.inventory_item_id = p_inventory_item_id
AND si.organization_id = p_inventory_organization_id
AND exists (select 1
from ar_vat_tax vt
WHERE vt.tax_code = si.tax_code
AND vt.tax_type = 'VAT'
AND decode(ltrim(vt.global_attribute1, '0123456789'),
null, to_number(vt.global_attribute1), null) =
p_tax_category_id
AND nvl(vt.enabled_flag,'Y') = 'Y'
AND nvl(vt.tax_class,'O') = 'O'
AND p_trx_date >= vt.start_date
AND p_trx_date <= nvl(vt.end_date,p_trx_date))
UNION
SELECT tg.tax_code,
NULL,
si.inventory_item_id
FROM mtl_system_items si
,ar_vat_tax vt
,jl_zz_ar_tx_groups tg
,ar_vat_tax vt1
WHERE si.inventory_item_id = p_inventory_item_id
AND si.organization_id = p_inventory_organization_id
AND si.tax_code = vt.tax_code
AND vt.tax_type = 'TAX_GROUP'
AND p_trx_date >= vt1.start_date
AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
AND tg.group_tax_id = vt.vat_tax_id
AND nvl(vt1.enabled_flag,'Y') = 'Y'
AND nvl(vt1.tax_class,'O') = 'O'
AND tg.tax_category_id = p_tax_category_id
AND tg.contributor_type = p_contributor_type
AND tg.establishment_type = p_establishment_type
AND tg.transaction_nature = p_transaction_nature
AND tg.tax_code = vt1.tax_code
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
SELECT c.tax_code,
NULL,
c.cust_account_id
FROM hz_cust_accounts c
WHERE c.cust_account_id = NVL(p_ship_to_customer_id,
p_bill_to_customer_id)
AND exists (select 1
from ar_vat_tax vt
WHERE c.tax_code = vt.tax_code
AND vt.tax_type = 'VAT'
AND decode(ltrim(vt.global_attribute1,
'0123456789'),
null,
to_number(vt.global_attribute1),null) =
p_tax_category_id
AND p_trx_date >= vt.start_date
AND p_trx_date <= nvl(vt.end_date,p_trx_date)
AND nvl(vt.enabled_flag,'Y') = 'Y'
AND nvl(vt.tax_class,'O') = 'O')
UNION
SELECT tg.tax_code,
NULL,
c.cust_account_id
FROM hz_cust_accounts c
,ar_vat_tax vt
,jl_zz_ar_tx_groups tg
,ar_vat_tax vt1
WHERE c.cust_account_id = NVL(p_ship_to_customer_id,
p_bill_to_customer_id)
AND c.tax_code = vt.tax_code
AND vt.tax_type = 'TAX_GROUP'
AND tg.tax_category_id = p_tax_category_id
AND tg.tax_code = vt1.tax_code
AND p_trx_date >= vt1.start_date
AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
AND nvl(vt1.enabled_flag,'Y') = 'Y'
AND nvl(vt1.tax_class,'O') = 'O'
AND tg.group_tax_id = vt.vat_tax_id
AND tg.contributor_type = p_contributor_type
AND tg.transaction_nature = p_transaction_nature
AND tg.establishment_type = p_establishment_type
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
SELECT su.tax_code,
NULL,
su.site_use_id
FROM hz_cust_site_uses su
WHERE su.site_use_id = p_bill_to_site_use_id
AND exists (select 1
from ar_vat_tax vt
WHERE su.tax_code = vt.tax_code
AND vt.tax_type = 'VAT'
AND decode(ltrim(vt.global_attribute1,
'0123456789'),
null,
to_number(vt.global_attribute1),null) = p_tax_category_id
AND p_trx_date >= vt.start_date
AND p_trx_date <= nvl(vt.end_date,p_trx_date)
AND nvl(vt.enabled_flag,'Y') = 'Y'
AND nvl(vt.tax_class,'O') = 'O')
UNION
SELECT tg.tax_code,
NULL,
su.site_use_id
FROM hz_cust_site_uses su
,ar_vat_tax vt
,jl_zz_ar_tx_groups tg
,ar_vat_tax vt1
WHERE su.site_use_id = p_bill_to_site_use_id
AND su.tax_code = vt.tax_code
AND vt.tax_type = 'TAX_GROUP'
AND tg.group_tax_id = vt.vat_tax_id
AND tg.tax_category_id = p_tax_category_id
AND tg.contributor_type = p_contributor_type
AND tg.transaction_nature = p_transaction_nature
AND tg.establishment_type = p_establishment_type
AND tg.tax_code = vt1.tax_code
AND p_trx_date >= vt1.start_date
AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
AND nvl(vt1.enabled_flag,'Y') = 'Y'
AND nvl(vt1.tax_class,'O') = 'O'
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
SELECT su.tax_code,
NULL,
su.site_use_id
FROM hz_cust_site_uses su
WHERE su.site_use_id = p_ship_to_site_use_id
AND exists (select 1
from ar_vat_tax vt
WHERE su.tax_code = vt.tax_code
AND vt.tax_type = 'VAT'
AND decode(ltrim(vt.global_attribute1,
'0123456789'),null,
to_number(vt.global_attribute1),null)
= p_tax_category_id
AND p_trx_date >= vt.start_date
AND p_trx_date <= nvl(vt.end_date,p_trx_date)
AND nvl(vt.enabled_flag,'Y') = 'Y'
AND nvl(vt.tax_class,'O') = 'O' )
UNION
SELECT tg.tax_code,
NULL,
su.site_use_id
FROM hz_cust_site_uses su
,ar_vat_tax vt
,jl_zz_ar_tx_groups tg
,ar_vat_tax vt1
WHERE su.site_use_id = p_ship_to_site_use_id
AND su.tax_code = vt.tax_code
AND vt.tax_type = 'TAX_GROUP'
AND tg.group_tax_id = vt.vat_tax_id
AND tg.tax_category_id = p_tax_category_id
AND tg.contributor_type = p_contributor_type
AND tg.transaction_nature = p_transaction_nature
AND tg.establishment_type = p_establishment_type
AND tg.tax_code = vt1.tax_code
AND p_trx_date >= vt1.start_date
AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
AND nvl(vt1.enabled_flag,'Y') = 'Y'
AND nvl(vt1.tax_class,'O') = 'O'
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
SELECT hrl.global_attribute6 tax_code,
NULL,
p_ship_from_warehouse_id
FROM hr_locations_all hrl
WHERE hrl.location_id = p_warehouse_location_id
AND exists (select 1
from ar_vat_tax vt
WHERE hrl.global_attribute6 = vt.tax_code
AND vt.tax_type = 'VAT'
AND decode(ltrim(vt.global_attribute1,'0123456789'),
null, to_number(vt.global_attribute1),
null) = p_tax_category_id
AND p_trx_date >= vt.start_date
AND p_trx_date <= nvl(vt.end_date,p_trx_date)
AND nvl(vt.enabled_flag,'Y') = 'Y'
AND nvl(vt.tax_class,'O') = 'O')
UNION
SELECT tg.tax_code tax_code,
NULL,
p_ship_from_warehouse_id
FROM jl_zz_ar_tx_groups tg
WHERE tg.tax_category_id = p_tax_category_id
AND tg.contributor_type = p_contributor_type
AND tg.transaction_nature = p_transaction_nature
AND tg.establishment_type = p_establishment_type
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
AND exists (select 1
from ar_vat_tax vt1,
ar_vat_tax vt,
hr_locations_all hrl
WHERE hrl.location_id = p_warehouse_location_id
AND hrl.global_attribute6 = vt.tax_code
AND vt.tax_type = 'TAX_GROUP'
AND tg.group_tax_id = vt.vat_tax_id
AND tg.tax_code = vt1.tax_code
AND nvl(vt1.enabled_flag,'Y') = 'Y'
AND nvl(vt1.tax_class,'O') = 'O'
AND p_trx_date >= vt1.start_date
AND p_trx_date <= NVL(vt1.end_date, p_trx_date));
SELECT sp.tax_classification_code,
NULL,
NULL
FROM zx_product_options sp
,ar_vat_tax vt
WHERE sp.application_id = 222
AND sp.org_id = vt.org_id
AND sp.tax_classification_code = vt.tax_code
AND vt.tax_type = 'VAT'
AND decode(ltrim(vt.global_attribute1, '0123456789'), null,
to_number(vt.global_attribute1), null) = p_tax_category_id
AND p_trx_date >= vt.start_date
AND p_trx_date <= nvl(vt.end_date,p_trx_date)
AND nvl(vt.enabled_flag,'Y') = 'Y'
AND nvl(vt.tax_class,'O') = 'O'
UNION
SELECT tg.tax_code,
NULL,
NULL
FROM jl_zz_ar_tx_groups tg
WHERE tg.tax_category_id = p_tax_category_id
AND tg.contributor_type = p_contributor_type
AND tg.transaction_nature = p_transaction_nature
AND tg.establishment_type = p_establishment_type
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
AND exists (select 1
from ar_vat_tax vt1
,ar_vat_tax vt
,zx_product_options sp
WHERE sp.tax_classification_code = vt.tax_code
AND sp.application_id = 222
AND sp.org_id = vt.org_id
AND vt.tax_type = 'TAX_GROUP'
AND tg.group_tax_id = vt.vat_tax_id
AND tg.tax_code = vt1.tax_code
AND nvl(vt1.enabled_flag,'Y') = 'Y'
AND nvl(vt1.tax_class,'O') = 'O'
AND p_trx_date >= vt1.start_date
AND p_trx_date <= NVL(vt1.end_date,p_trx_date));
SELECT ml.tax_code,
NULL,
ml.memo_line_id
FROM ar_memo_lines ml
WHERE ml.memo_line_id = p_memo_line_id
AND exists (select 1
from ar_vat_tax vt
WHERE ml.tax_code = vt.tax_code
AND vt.tax_type = 'VAT'
AND decode(ltrim(vt.global_attribute1,
'0123456789'), null,
to_number(vt.global_attribute1), null)
= p_tax_category_id
AND p_trx_date >= vt.start_date
AND p_trx_date <= nvl(vt.end_date,p_trx_date)
AND nvl(vt.enabled_flag,'Y') = 'Y'
AND nvl(vt.tax_class,'O') = 'O')
UNION
SELECT tg.tax_code,
NULL,
ml.memo_line_id
FROM ar_memo_lines ml
,ar_vat_tax vt
,jl_zz_ar_tx_groups tg
,ar_vat_tax vt1
WHERE memo_line_id = p_memo_line_id
AND ml.tax_code = vt.tax_code
AND vt.tax_type = 'TAX_GROUP'
AND tg.group_tax_id = vt.vat_tax_id
AND tg.tax_category_id = p_tax_category_id
AND tg.contributor_type = p_contributor_type
AND tg.transaction_nature = p_transaction_nature
AND tg.establishment_type = p_establishment_type
AND tg.tax_code = vt1.tax_code
AND p_trx_date >= vt1.start_date
AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
AND nvl(vt1.enabled_flag,'Y') = 'Y'
AND nvl(vt1.tax_class,'O') = 'O'
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
SELECT tax_code,
NULL,
NULL
FROM jl_zz_ar_tx_schedules
WHERE tax_category_id = p_tax_category_id
AND p_base_amount BETWEEN min_taxable_basis
AND max_taxable_basis
AND p_trx_date <= end_date_active
AND p_trx_date >= NVL(start_date_active,p_trx_date);
SELECT fc.tax_code,
fc.base_rate,
fc.fsc_cls_id
FROM jl_zz_ar_tx_fsc_cls fc
WHERE fc.fiscal_classification_code = p_fiscal_classification_code
AND fc.tax_category_id = p_tax_category_id
AND fc.enabled_flag = 'Y'
AND p_trx_date <= fc.end_date_active
AND p_trx_date >= NVL(fc.start_date_active,p_trx_date);
SELECT loc.tax_code,
loc.base_rate,
loc.locn_id
FROM jl_zz_ar_tx_locn loc
WHERE loc.tax_category_id = p_tax_category_id
AND p_trx_date <= loc.end_date_active
AND p_trx_date >= NVL(loc.start_date_active, p_trx_date)
AND exists (select 1
from hz_geographies lv
,hz_cust_acct_sites ad
,hz_cust_site_uses su
,hz_party_sites p
,hz_locations lc
,ar_system_parameters sp
,hr_locations_all hrl
WHERE hrl.location_id = p_warehouse_location_id
AND loc.ship_from_code = hrl.REGION_2
AND loc.ship_to_segment_id = lv.geography_id
AND lv.geography_type = sp.global_attribute9
AND UPPER(lv.geography_name) =
UPPER(decode(sp.global_attribute9,'STATE',lc.state,lc.province))
AND su.cust_acct_site_id = ad.cust_acct_site_id
AND ad.party_site_id =p.party_site_id
AND p.location_id =lc.location_id
AND su.site_use_id = NVL(p_ship_to_site_use_id,
p_bill_to_site_use_id));
SELECT exc.tax_code,
exc.base_rate,
exc.exc_fsc_id
FROM jl_zz_ar_tx_exc_fsc exc
WHERE exc.fiscal_classification_code = p_fiscal_classification_code
AND exc.tax_category_id = p_tax_category_id
AND p_trx_date <= exc.end_date_active
AND p_trx_date >= NVL(exc.start_date_active, p_trx_date)
AND exists (select 1
from hz_geographies lv
,hz_cust_acct_sites ad
,hz_cust_site_uses su
,hz_party_sites p
,hz_locations loc
,ar_system_parameters sp
,hr_locations_all hrl
WHERE hrl.location_id = p_warehouse_location_id
AND exc.ship_from_code = hrl.REGION_2
AND exc.ship_to_segment_id = lv.geography_id
AND lv.geography_type = sp.global_attribute9
AND ad.party_site_id=p.party_site_id
AND p.location_id=loc.location_id
AND UPPER(lv.geography_name) = UPPER(decode(sp.global_attribute9,
'STATE', loc.state, loc.province))
AND su.cust_acct_site_id = ad.cust_acct_site_id
AND su.site_use_id = NVL(p_ship_to_site_use_id, p_bill_to_site_use_id));
SELECT exi.tax_code,
exi.base_rate,
exi.exc_itm_id
FROM jl_zz_ar_tx_exc_itm exi
WHERE exi.inventory_item_id = p_inventory_item_id
AND exi.organization_id = p_ship_from_warehouse_id
AND exi.tax_category_id = p_tax_category_id
AND p_trx_date <= exi.end_date_active
AND p_trx_date >= NVL(exi.start_date_active, p_trx_date)
AND exists (select 1
from hz_geographies lv
,hz_cust_acct_sites ad
,hz_cust_site_uses su
,hz_party_sites p
,hz_locations loc
,ar_system_parameters sp
,hr_locations_all hrl
WHERE hrl.location_id = p_warehouse_location_id
AND exi.ship_from_code = hrl.REGION_2
AND exi.ship_to_segment_id = lv.geography_id
AND lv.geography_type = sp.global_attribute9
AND ad.party_site_id =p.party_site_id
AND p.location_id=loc.location_id
AND UPPER(lv.geography_name) = decode(sp.global_attribute9,
'STATE', loc.state,loc.province)
AND su.cust_acct_site_id = ad.cust_acct_site_id
AND su.site_use_id = NVL(p_ship_to_site_use_id,p_bill_to_site_use_id));
SELECT gt.tax_code,
gt.base_rate,
gt.tax_group_record_id
FROM jl_zz_ar_tx_groups gt
WHERE gt.group_tax_id = p_group_tax_id
AND gt.tax_category_id = p_tax_category_id
AND gt.contributor_type = p_contributor_type
AND gt.transaction_nature = p_transaction_nature
AND gt.establishment_type = p_establishment_type
AND p_trx_date <= gt.end_date_active
AND p_trx_date >= NVL(gt.start_date_active, p_trx_date);
SELECT tnr.tax_code,
tnr.base_rate,
tnr.txn_nature_id
FROM jl_zz_ar_tx_nat_rat tnr,
jl_zz_ar_tx_att_val tcav,
jl_zz_ar_tx_categ tc,
jl_zz_ar_tx_cat_att tca,
jl_zz_ar_tx_att_cls tcac
WHERE tcac.tax_attr_class_code = p_transaction_nature_class
AND tcac.tax_category_id = p_tax_category_id
AND tcac.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tcac.enabled_flag = 'Y'
AND tca.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
AND tca.tax_attribute_name = tcac.tax_attribute_name
AND tc.tax_category_id = tcac.tax_category_id
AND p_trx_date <= tc.end_date_active
AND p_trx_date >= NVL(tc.start_date_active,p_trx_date)
AND tcav.tax_category_id = tc.tax_category_id
AND tcav.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
AND tcav.tax_attribute_name = tcac.tax_attribute_name
AND tcav.tax_attribute_value = tcac.tax_attribute_value
AND tnr.tax_categ_attr_val_id = tcav.tax_categ_attr_val_id
AND p_trx_date <= tnr.end_date_active
AND p_trx_date >= NVL(tnr.start_date_active,p_trx_date)
ORDER BY tca.priority_number;
SELECT tec.tax_code,
tec.base_rate,
tec.exc_cus_id
FROM jl_zz_ar_tx_exc_cus tec,
hz_cust_site_uses su
WHERE tec.tax_category_id = p_tax_category_id
AND p_trx_date <= tec.end_date_active
AND p_trx_date >= NVL(tec.start_date_active,p_trx_date)
AND su.site_use_id = NVL(p_ship_to_site_use_id,p_bill_to_site_use_id)
AND su.cust_acct_site_id = tec.address_id;
SELECT tr.rule,
tr.rule_id rule_id
FROM jl_zz_ar_tx_rules tr
WHERE tr.tax_rule_level = p_level
AND tr.tax_category_id = p_tax_category_id
AND nvl(tr.contributor_type,'~') = p_contributor_type
AND tr.cust_trx_type_id = p_cust_trx_type_id
ORDER BY tr.priority,
tr.rule;
SELECT tr.rule rule,
tr.rule_id rule_id
FROM jl_zz_ar_tx_rules tr,
ar_system_parameters sp
WHERE tr.tax_rule_level = p_level
AND tr.tax_category_id = p_tax_category_id
AND tr.contributor_type = 'DEFAULT'
AND tr.cust_trx_type_id =
decode(ltrim(sp.global_attribute15, '0123456789'),
null, to_number(sp.global_attribute15), null)
ORDER BY tr.priority,
tr.rule;
SELECT tr.rule,
tr.rule_id rule_id
FROM jl_zz_ar_tx_rules tr
WHERE tr.tax_rule_level = p_level
AND tr.tax_category_id = p_tax_category_id
AND nvl(tr.contributor_type,'~') = p_contributor_type
AND tr.cust_trx_type_id = p_cust_trx_type_id
ORDER BY tr.priority,
tr.rule;
SELECT tr.rule rule,
tr.rule_id rule_id
FROM jl_zz_ar_tx_rules tr,
ar_system_parameters sp
WHERE tr.tax_rule_level = p_level
AND tr.tax_category_id = p_tax_category_id
AND tr.contributor_type = 'DEFAULT'
AND tr.cust_trx_type_id =
decode(ltrim(sp.global_attribute15, '0123456789'), null,
to_number(sp.global_attribute15), null)
ORDER BY tr.priority,
tr.rule;
SELECT tr.rule,
tr.rule_id rule_id
FROM jl_zz_ar_tx_rules tr
WHERE tr.tax_rule_level = p_level
AND tr.tax_category_id = p_tax_category_id
AND NVL(tr.contributor_type,'~') = p_contributor_type
AND tr.cust_trx_type_id = p_cust_trx_type_id
ORDER BY tr.priority,
tr.rule;
SELECT tr.rule rule,
tr.rule_id rule_id
FROM jl_zz_ar_tx_rules tr,
ar_system_parameters sp
WHERE tr.tax_rule_level = p_level
AND tr.tax_category_id = p_tax_category_id
AND tr.contributor_type = 'DEFAULT'
AND tr.cust_trx_type_id =
decode(ltrim(sp.global_attribute15, '0123456789'),
null, to_number(sp.global_attribute15), null)
ORDER BY tr.priority,
tr.rule;
SELECT ac.tax_attribute_value
INTO l_curr_nature_class_value
FROM jl_zz_ar_tx_att_cls ac
WHERE ac.tax_attr_class_type = 'TRANSACTION_CLASS'
AND ac.tax_attr_class_code = p_transaction_nature_class
AND ac.tax_category_id = p_tax_category_id
AND ac.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
AND ac.enabled_flag = 'Y'
AND exists (select 1
from jl_zz_ar_tx_cat_att ca
WHERE ca.tax_category_id = ac.tax_category_id
AND ca.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
AND ca.tax_attribute_name = ac.tax_attribute_name);
select ota.tax_attribute_value,
cta.tax_attribute_value
into l_establishment_type,
l_contributor_type
FROM jl_zz_ar_tx_cus_cls cta,
hz_cust_site_uses rsu,
jl_zz_ar_tx_att_cls ota,
jl_zz_ar_tx_categ tc
WHERE tc.tax_category_id = p_tax_category_id
AND tc.threshold_check_grp_by = 'DOCUMENT'
AND ota.tax_attr_class_type = 'ORGANIZATION_CLASS'
AND ota.tax_category_id = tc.tax_category_id
AND ota.tax_attribute_type = 'ORGANIZATION_ATTRIBUTE'
AND ota.tax_attribute_name = tc.org_tax_attribute
AND ota.tax_attr_class_code = p_organization_class
AND rsu.site_use_id =
decode(tc.tax_category_id, null, 0, p_site_use_id)
AND cta.address_id = rsu.cust_acct_site_id
AND cta.tax_category_id = tc.tax_category_id
AND cta.tax_attribute_name = tc.cus_tax_attribute
AND cta.tax_attr_class_code = p_contributor_class
AND cta.enabled_flag = 'Y';
select ota.tax_attribute_value,
cta.tax_attribute_value
into l_establishment_type,
l_contributor_type
FROM jl_zz_ar_tx_att_cls cta,
jl_zz_ar_tx_att_cls ota,
jl_zz_ar_tx_categ tc
WHERE tc.tax_category_id = p_tax_category_id
AND tc.threshold_check_grp_by = 'DOCUMENT'
AND ota.tax_attr_class_type = 'ORGANIZATION_CLASS'
AND ota.tax_category_id = tc.tax_category_id
AND ota.tax_attribute_type = 'ORGANIZATION_ATTRIBUTE'
AND ota.tax_attribute_name = tc.org_tax_attribute
AND ota.tax_attr_class_code = p_organization_class
AND cta.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
AND cta.tax_category_id = tc.tax_category_id
AND cta.tax_attribute_type = 'CONTRIBUTOR_ATTRIBUTE'
AND cta.tax_attribute_name = tc.cus_tax_attribute
AND cta.tax_attr_class_code = p_contributor_class
AND cta.enabled_flag = 'Y';
SELECT 1
INTO l_dummy
FROM jl_zz_ar_tx_groups tg
WHERE tg.group_tax_id = p_tax_group
AND tg.tax_category_id = p_tax_category_id
AND tg.transaction_nature = l_curr_nature_class_value
AND tg.establishment_type = l_establishment_type
AND tg.contributor_type = l_contributor_type
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
SELECT tcav.tax_categ_attr_val_id tax_categ_attr_val_id
FROM jl_zz_ar_tx_att_val tcav,
jl_zz_ar_tx_cat_att tca,
jl_zz_ar_tx_att_cls tcac
WHERE tcav.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
AND tcav.tax_category_id = p_tax_category_id
AND tcac.tax_attr_class_code = p_transaction_nature_class
AND tcac.tax_category_id = tcav.tax_category_id
AND tcac.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tcac.enabled_flag = 'Y'
AND tca.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
AND tca.tax_attribute_name = tcac.tax_attribute_name
AND tca.tax_attribute_name = tcav.tax_attribute_name
AND tcac.tax_attribute_value = tcav.tax_attribute_value
ORDER BY tca.priority_number;
SELECT min_percentage,
min_amount,
min_taxable_basis,
use_tx_categ_thresholds
INTO p_min_tax_rate,
p_min_tax_amount,
p_min_taxable_basis,
l_use_tx_categ_thresholds
FROM jl_zz_ar_tx_groups
WHERE group_tax_id = p_tax_group_id
AND tax_category_id = p_tax_category_id
AND establishment_type = p_establishment_type
AND contributor_type = p_contributor_type
AND transaction_nature = p_transaction_nature
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT min_percentage
INTO p_min_tax_rate
FROM jl_zz_ar_tx_cat_dtl
WHERE tax_category_id = p_tax_category_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT min_percentage
INTO p_min_tax_rate
FROM jl_zz_ar_tx_categ
WHERE tax_category_id = p_tax_category_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT min_amount
INTO p_min_tax_amount
FROM jl_zz_ar_tx_cat_dtl
WHERE tax_category_id = p_tax_category_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT min_amount
INTO p_min_tax_amount
FROM jl_zz_ar_tx_categ
WHERE tax_category_id = p_tax_category_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT min_taxable_basis
INTO p_min_taxable_basis
FROM jl_zz_ar_tx_cat_dtl
WHERE tax_category_id = p_tax_category_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT min_taxable_basis
INTO p_min_taxable_basis
FROM jl_zz_ar_tx_categ
WHERE tax_category_id = p_tax_category_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT min_percentage,
min_amount,
min_taxable_basis
INTO l_nat_min_tax_rate,
l_nat_min_tax_amount,
l_nat_min_taxable_base
FROM jl_zz_ar_tx_nat_rat
WHERE tax_categ_attr_val_id = trx_nature_rec.tax_categ_attr_val_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.global_attribute11,0) appl_prior_base
,nvl(rlt1.global_attribute19,0) charged_tax
,nvl(rlt1.global_attribute20,0) calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r1.customer_trx_id = rl1.customer_trx_id
AND r1.customer_trx_id = p_prev_cust_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
AND p_trx_date <= nvl(v1.end_date, p_trx_date)
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
UNION --Related transactions of main transaction for current credit
--Transaction
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.global_attribute11,0) appl_prior_base
,nvl(rlt1.global_attribute19,0) charged_tax
,nvl(rlt1.global_attribute20,0) calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r1.customer_trx_id = rl1.customer_trx_id
AND r1.related_customer_trx_id = p_prev_cust_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
AND p_trx_date <= nvl(v1.end_date, p_trx_date)
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
UNION --Credit transactions of main transaction for current credit
--Transaction
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.global_attribute11,0) appl_prior_base
,nvl(rlt1.global_attribute19,0) charged_tax
,nvl(rlt1.global_attribute20,0) calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r1.customer_trx_id = rl1.customer_trx_id
AND r1.previous_customer_trx_id = p_prev_cust_trx_id
AND r1.customer_trx_id <> p_curr_cust_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
AND p_trx_date <= nvl(v1.end_date, p_trx_date)
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
UNION --Credit transactions of related transactions
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.global_attribute11,0) appl_prior_base
,nvl(rlt1.global_attribute19,0) charged_tax
,nvl(rlt1.global_attribute20,0) calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx r2
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r2.related_customer_trx_id = p_prev_cust_trx_id
AND r2.customer_trx_id = r1.previous_customer_trx_id
AND r1.customer_trx_id = rl1.customer_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
AND p_trx_date <= nvl(v1.end_date, p_trx_date)
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
ORDER BY 7, 6, 1;
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.taxable_amount,0) appl_prior_base
,fnd_number.canonical_to_number(nvl(rlt1.global_attribute19,0))
charged_tax
,fnd_number.canonical_to_number(nvl(rlt1.global_attribute20,0))
calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r1.customer_trx_id = rl1.customer_trx_id
AND r1.customer_trx_id = p_rel_cust_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
UNION --Related Transactions of main transaction of
--current related transaction
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.taxable_amount,0) appl_prior_base
,fnd_number.canonical_to_number(nvl(rlt1.global_attribute19,0))
charged_tax
,fnd_number.canonical_to_number(nvl(rlt1.global_attribute20,0))
calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r1.customer_trx_id = rl1.customer_trx_id
AND r1.related_customer_trx_id = p_rel_cust_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
UNION --Credit Transactions of main transaction for
-- current related transaction
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.taxable_amount,0) appl_prior_base
,fnd_number.canonical_to_number(nvl(rlt1.global_attribute19,0))
charged_tax
,fnd_number.canonical_to_number(nvl(rlt1.global_attribute20,0))
calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r1.customer_trx_id = rl1.customer_trx_id
AND r1.previous_customer_trx_id = p_rel_cust_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
UNION -- Credit Transactions of other related transaction for
-- current related transactions
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.taxable_amount,0) appl_prior_base
,fnd_number.canonical_to_number(nvl(rlt1.global_attribute19,0))
charged_tax
,fnd_number.canonical_to_number(nvl(rlt1.global_attribute20,0))
calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx r2
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r2.related_customer_trx_id = p_rel_cust_trx_id
AND r2.customer_trx_id = r1.previous_customer_trx_id
AND r1.customer_trx_id = rl1.customer_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
ORDER BY 7, 6, 1;
SELECT tc1.tax_category_id tax_category_id
,tc1.threshold_check_grp_by grp_attr_name
,tcl1.tax_attribute_value grp_attr_value
,nvl(rlt1.global_attribute11,0) appl_prior_base
,nvl(rlt1.global_attribute19,0) charged_tax
,nvl(rlt1.global_attribute20,0) calculated_tax
,rl1.customer_trx_line_id inv_line_number
,r1.customer_trx_id header_trx_id
FROM ra_customer_trx r1
,ra_customer_trx_lines rl1
,ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_att_cls tcl1
,jl_zz_ar_tx_categ tc1
WHERE r1.customer_trx_id = rl1.customer_trx_id
AND r1.customer_trx_id = p_curr_cust_trx_id
AND rl1.line_type = 'LINE'
AND r1.customer_trx_id = rlt1.customer_trx_id
AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
AND p_trx_date <= nvl(v1.end_date,p_trx_date)
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND rl1.global_attribute3 = tcl1.tax_attr_class_code
AND tcl1.tax_category_id = tc1.tax_category_id
AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
AND p_trx_date <= tc1.end_date_active
AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
ORDER BY 7, 6, 1;
SELECT tc1.tax_category_id tax_category_id
FROM ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_categ tc1
WHERE rlt1.customer_trx_id = p_rel_cust_trx_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND tc1.threshold_check_grp_by = 'DOCUMENT';
SELECT tc1.tax_category_id tax_category_id
FROM ra_customer_trx_lines rlt1
,ar_vat_tax v1
,jl_zz_ar_tx_categ tc1
WHERE rlt1.customer_trx_id = p_curr_cust_trx_id
AND rlt1.line_type= 'TAX'
AND rlt1.vat_tax_id = v1.vat_tax_id
AND v1.tax_type = 'VAT'
AND v1.global_attribute1 = tc1.tax_category_id
AND tc1.threshold_check_grp_by = 'DOCUMENT';
g_rel_tax_line_amounts.DELETE;
g_rel_trx_categ.DELETE;
SELECT tax_code,
min_taxable_basis,
max_taxable_basis
FROM jl_zz_ar_tx_schedules
WHERE tax_category_id = p_tax_category_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT rule
INTO l_rule_code
FROM jl_zz_ar_tx_rules
WHERE rule_id = p_rule_id;
SELECT threshold_check_grp_by,
threshold_check_level
INTO l_grp_attr_name,
l_operation_level
FROM jl_zz_ar_tx_categ
WHERE tax_category_id = p_tax_category_id
AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
AND end_date_active;
SELECT tax_attribute_value
INTO l_grp_attr_val
FROM jl_zz_ar_tx_att_cls ac
WHERE tax_attr_class_type = 'TRANSACTION_CLASS'
AND tax_attr_class_code = p_transaction_nature_class
AND enabled_flag = 'Y'
AND tax_category_id = p_tax_category_id
AND tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
AND tax_attribute_name = l_grp_attr_name;
SELECT tax_rate
INTO l_sch_tax_rate
FROM ar_vat_tax
WHERE tax_code = tax_schedule_rec.tax_code
AND set_of_books_id = p_set_of_books_id
AND start_date <= p_trx_date
AND NVL(end_date,p_trx_date) >= p_trx_date
AND nvl(enabled_flag,'Y') = 'Y'
AND nvl(tax_class,'O') = 'O';
FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Insert PLSQL rel tax line amounts table entry: '||
to_char(l_rel_tax_line_amt_ctr));
SELECT tc.tributary_substitution,
tc.tax_categ_to_reduce_id,
sp.location_structure_id,
decode(ltrim(sp.global_attribute10, '0123456789'),
null, to_number(sp.global_attribute10), null),
sp.set_of_books_id
INTO v_tributary_substitution,
v_tax_category_to_reduce_id,
v_location_structure_id,
v_location_segment_num,
v_set_of_books_id
FROM jl_zz_ar_tx_categ tc,
ar_system_parameters_all sp
WHERE tc.tax_category_id = p_tax_category_id
AND p_trx_date <= tc.end_date_active
AND p_trx_date >= NVL(tc.start_date_active, p_trx_date)
AND nvl(tc.org_id,-99) = nvl(sp.org_id,-99)
AND tc.org_id = zx_product_integration_pkg.sysinfo.sysparam.org_id;
SELECT tg.tributary_substitution,
tg.tax_category_to_reduce_id,
sp.location_structure_id,
decode(ltrim(sp.global_attribute10, '0123456789'),
null, to_number(sp.global_attribute10), null),
sp.set_of_books_id
INTO v_tributary_substitution,
v_tax_category_to_reduce_id,
v_location_structure_id,
v_location_segment_num,
v_set_of_books_id
FROM jl_zz_ar_tx_groups tg,
ar_system_parameters_all sp
WHERE tg.tax_category_id = p_tax_category_id
AND tg.group_tax_id = p_group_tax_id
AND tg.contributor_type = p_contributor_type
AND tg.transaction_nature = p_transaction_nature
AND tg.establishment_type = p_establishment_type
AND p_trx_date <= tg.end_date_active
AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
AND nvl(tg.org_id,-99) = nvl(sp.org_id,-99)
AND tg.org_id = zx_product_integration_pkg.sysinfo.sysparam.org_id;
select ac.tax_attribute_value
into v_aux_establishment_type
from jl_zz_ar_tx_att_cls ac
where ac.tax_attr_class_code = p_organization_class
and ac.tax_category_id = v_tax_category_to_reduce_id
and ac.tax_attribute_type = 'ORGANIZATION_ATTRIBUTE'
and ac.tax_attr_class_type = 'ORGANIZATION_CLASS'
and ac.enabled_flag = 'Y'
and exists (select 1
from jl_zz_ar_tx_categ cat
where cat.tax_category_id = ac.tax_category_id
and ac.tax_attribute_name = cat.org_tax_attribute
and p_trx_date <= cat.end_date_active
and p_trx_date >= NVL(cat.start_date_active, p_trx_date));
select ac.tax_attribute_value
into v_aux_transaction_nature
from jl_zz_ar_tx_att_cls ac
where ac.tax_attr_class_code = p_transaction_nature_class
and ac.tax_category_id = v_tax_category_to_reduce_id
and ac.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
and ac.tax_attr_class_type = 'TRANSACTION_CLASS'
and ac.enabled_flag = 'Y'
and exists (select 1
from jl_zz_ar_tx_categ cat
where cat.tax_category_id = ac.tax_category_id
and ac.tax_attribute_name = cat.txn_tax_attribute
and p_trx_date <= cat.end_date_active
and p_trx_date >= NVL(cat.start_date_active, p_trx_date));
select ac.tax_attribute_value
into v_aux_contributor_type
from jl_zz_ar_tx_cus_cls ac,
hz_cust_site_uses su,
jl_zz_ar_tx_categ cat
where ac.tax_attr_class_code = p_contributor_class
and ac.tax_category_id = v_tax_category_to_reduce_id
and ac.enabled_flag = 'Y'
and cat.tax_category_id = ac.tax_category_id
and ac.tax_attribute_name = cat.cus_tax_attribute
and su.cust_acct_site_id = ac.address_id
and su.site_use_id =
NVL(p_ship_to_site_use_id, p_bill_to_site_use_id)
and p_trx_date <= cat.end_date_active
and p_trx_date >= NVL(cat.start_date_active, p_trx_date);
select ac.tax_attribute_value
into v_aux_contributor_type
from jl_zz_ar_tx_att_cls ac,
jl_zz_ar_tx_categ cat
where ac.tax_attr_class_code = p_contributor_class
and ac.tax_category_id = v_tax_category_to_reduce_id
and ac.enabled_flag = 'Y'
and ac.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
and cat.tax_category_id = ac.tax_category_id
and ac.tax_attribute_type = 'CONTRIBUTOR_ATTRIBUTE'
and ac.tax_attribute_name = cat.cus_tax_attribute
and p_trx_date <= cat.end_date_active
and p_trx_date >= NVL(cat.start_date_active, p_trx_date);
SELECT vt.tax_rate
INTO v_aux_tax_rate
FROM ar_vat_tax vt
WHERE vt.tax_code = v_aux_tax_code
AND vt.set_of_books_id = v_set_of_books_id
AND vt.start_date <= trunc(p_trx_date)
AND nvl(vt.end_date, trunc(p_trx_date)) >= trunc(p_trx_date)
AND nvl(vt.enabled_flag,'Y') = 'Y'
AND nvl(vt.tax_class,'O') = 'O';
SELECT cust_trx_type_id
FROM RA_CUSTOMER_TRX_ALL
WHERE org_id = c_org_id
AND customer_trx_id = c_customer_trx_id ;
SELECT NVL(vt.amount_includes_tax_flag,'N'),
sp.global_attribute14,
vt.global_attribute3,
decode(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code,
ZX_PRODUCT_INTEGRATION_PKG.sysinfo.base_currency_code, 1,
decode(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_exchange_rate,
NULL, 1,
0, 1,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_exchange_rate))
INTO ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.amount_includes_tax_flag,
v_use_legal_message,
v_legal_message_exception,
l_exchange_rate
FROM ar_vat_tax vt,
ar_system_parameters_all sp
WHERE vt.vat_tax_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id
AND nvl(vt.org_id,-99) = nvl(sp.org_id,-99);
SELECT NVL(global_attribute1,'DEFAULT')
INTO l_organization_class
FROM hr_locations_all
where location_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4;
SELECT vat_tax_id
INTO l_vat_tax_id
FROM ar_vat_tax
WHERE tax_code = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code
AND set_of_books_id = ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id
AND tax_type = 'VAT'
AND to_date(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6,'YYYYMMDD') >= start_date
AND to_date(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6,'YYYYMMDD') <=
nvl(end_date,to_date(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6,'YYYYMMDD'))
AND nvl(enabled_flag,'Y') = 'Y'
AND nvl(tax_class,'O') = 'O';
SELECT rate.tax_rate_id,
rate.tax_rate_code,
regime.tax_regime_id,
regime.tax_regime_code,
tax.tax_id,
tax.tax,
status.tax_status_id,
status.tax_status_code
INTO ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_id,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_code,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_id,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_code,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_id,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_status_id,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_status_code
FROM zx_rates_b rate,
zx_regimes_b regime,
zx_taxes_b tax,
zx_status_b status
WHERE rate.tax_rate_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id
and rate.tax_regime_code = regime.tax_regime_code
and rate.tax = tax.tax
and tax.tax_regime_code = rate.tax_regime_code
and tax.content_owner_id = rate.content_owner_id
and rate.tax_status_code = status.tax_status_code
and status.tax_regime_code = rate.tax_regime_code
and status.tax = rate.tax
and status.content_owner_id = rate.content_owner_id;
SELECT rate.tax_rate_id,
rate.tax_rate_code,
rate.tax_regime_code,
rate.tax,
rate.tax_status_code
INTO ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_id,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_code,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_code,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_status_code
FROM zx_rates_b rate
WHERE rate.tax_rate_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id;
SELECT r.rule
INTO v_rule
FROM jl_zz_ar_tx_rules r
WHERE r.rule_id = p_rule_id;
SELECT substr(st.text,1,150)
INTO v_message_text
FROM jl_zz_ar_tx_lgl_msg lm,
ar_standard_text_vl st
WHERE st.standard_text_id = lm.message_id
AND lm.rule_data_id = p_rule_data_id
AND lm.inventory_organization_id = p_ship_from_warehouse_id
AND lm.rule_id = p_rule_id
AND lm.exception_code = p_legal_message_exception;
SELECT substr(st.text,1,150)
INTO v_message_text
FROM jl_zz_ar_tx_lgl_msg lm,
ar_standard_text_vl st
WHERE st.standard_text_id = lm.message_id
AND lm.rule_data_id = p_rule_data_id
AND lm.rule_id = p_rule_id
AND lm.exception_code = p_legal_message_exception;