The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT default_taxation_country
, document_sub_type
, trx_business_category
, line_intended_use
, product_fisc_classification
, product_category
, product_type
, user_defined_fisc_class
, assessable_value
, DECODE(APPLICATION_ID, 201, NVL(USER_UPD_DET_FACTORS_FLAG, 'N'), 'Y') user_override_tax_flag
INTO l_det_tax_attr_tbl(l_source_line_key).DEFAULT_TAXATION_COUNTRY
, l_det_tax_attr_tbl(l_source_line_key).DOCUMENT_SUB_TYPE
, l_det_tax_attr_tbl(l_source_line_key).TRX_BUSINESS_CATEGORY
, l_det_tax_attr_tbl(l_source_line_key).LINE_INTENDED_USE
, l_det_tax_attr_tbl(l_source_line_key).PRODUCT_FISC_CLASSIFICATION
, l_det_tax_attr_tbl(l_source_line_key).PRODUCT_CATEGORY
, l_det_tax_attr_tbl(l_source_line_key).PRODUCT_TYPE
, l_det_tax_attr_tbl(l_source_line_key).USER_DEFINED_FISC_CLASS
, l_det_tax_attr_tbl(l_source_line_key).ASSESSABLE_VALUE
, l_det_tax_attr_tbl(l_source_line_key).USER_OVERRIDE_TAX_FLAG
FROM zx_lines_det_factors
WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_application_id(p_trx_line_index)
AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_entity_code(p_trx_line_index)
AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(p_trx_line_index)
AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(p_trx_line_index)
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_line_id(p_trx_line_index)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_level_type(p_trx_line_index);
case 1 : In update mode, if taxation country is changed or
case 2 : When a new trx line gets inserted, if there are already other trx lines existing
in zx_lines_det_facotrs then default taxation country and document sub type values
will be passed from the existing trx lines.
In both cases re-default the tax attributes based on taxation country parameter
*/
-- If taxation country is passed then default the other tax attributes based on the passed value.
IF p_taxation_country IS NOT NULL AND (l_country_code IS NULL OR l_country_code <> p_taxation_country) THEN
l_country_code := p_taxation_country;
SELECT nvl(global_attribute8,'0') global_attribute8,
to_char(to_date(global_attribute9,'YYYY/MM/DD HH24:MI:SS'),
'RRRR-MON-DD') global_attribute9 INTO l_tax_invoice_number, l_tax_invoice_date
FROM RA_BATCH_SOURCES_ALL
WHERE batch_source_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BATCH_SOURCE_ID(p_trx_line_index)
AND NVL(org_id, -99) =
NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.internal_organization_id(p_trx_line_index), -99);
SELECT fc.classification_code
INTO x_prod_fisc_class_code
FROM zx_fc_product_fiscal_v fc,
mtl_item_categories mic
WHERE fc.country_code = p_country_code
AND mic.inventory_item_id = p_item_id
AND mic.organization_id = p_item_org_id
AND mic.category_id = fc.category_id
AND mic.category_set_id = fc.category_set_id
AND fc.structure_name = 'PRODUCT_FISCAL_CLASS'
AND fc.country_code in ('JL', 'BR', 'CO')
AND EXISTS
( SELECT 1
FROM jl_zz_ar_tx_fsc_cls_all
WHERE fiscal_classification_code = fc.classification_code
AND NVL(org_id, -99) = NVL(p_internal_org_id, -99)
AND enabled_flag = 'Y');
SELECT memo.global_attribute2 product_category
INTO x_prod_fisc_class_code
FROM ar_memo_lines_all_b Memo
WHERE memo_line_id = p_item_id
AND NVL(org_id, -99) = NVL(p_internal_org_id, -99);
SELECT tax_classification_code, effective_from, effective_to
INTO l_tax_classification_code, l_effective_from, l_effective_to
FROM zx_id_tcc_mapping
WHERE tax_rate_code_id = l_tax_code_id
AND source = 'AP';
SELECT global_attribute1
INTO l_temp_attribute1
FROM mtl_system_items_b
WHERE inventory_item_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_id(p_trx_line_index)
AND organization_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_org_id(p_trx_line_index);
SELECT ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_event_class_code( p_trx_line_index)
|| '.' || global_attribute2
INTO ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_business_category(p_trx_line_index)
FROM mtl_system_items_b
WHERE inventory_item_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_id(p_trx_line_index)
AND organization_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_org_id(p_trx_line_index);
SELECT decode(global_attribute13,'ARGENTINA', 'AR',
'COLOMBIA', 'CO',
'BRAZIL', 'BR',
NULL) INTO l_country_code FROM ar_system_parameters_all
WHERE NVL(org_id, -99) = NVL(p_internal_org_id, -99)
AND global_attribute_category like 'JL%';
SELECT le.country INTO l_country_code
FROM XLE_FIRSTPARTY_INFORMATION_V le
WHERE le.legal_entity_id = p_legal_entity_id;