[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE handle_update_scenarios(
p_trx_line_index IN BINARY_INTEGER,
p_event_class_rec IN zx_api_pub.event_class_rec_type,
p_row_num IN NUMBER,
p_tax_regime_code IN zx_regimes_b.tax_regime_code%TYPE,
p_tax IN zx_taxes_b.tax%TYPE,
p_tax_date IN DATE,
p_tax_determine_date IN DATE,
p_tax_point_date IN DATE,
x_self_assessed_flag OUT NOCOPY zx_lines.self_assessed_flag%TYPE,
x_tax_amt_included_flag OUT NOCOPY zx_lines.tax_amt_included_flag%TYPE,
x_tax_jurisdiction_id OUT NOCOPY zx_lines.tax_jurisdiction_id%TYPE,
x_tax_jurisdiction_code OUT NOCOPY zx_lines.tax_jurisdiction_code%TYPE,
x_return_status OUT NOCOPY VARCHAR2);
SELECT zxdtd.determining_factor_code,
zxdtd.determining_factor_class_code,
zxdtd.tax_regime_det_level_code
FROM zx_det_factor_templ_b zxdt,
zx_det_factor_templ_dtl zxdtd
WHERE zxdtd.det_factor_templ_id = zxdt.det_factor_templ_id
AND zxdt.det_factor_templ_code = p_reg_template_code;
SELECT tax_regime_id,
regime_precedence,
tax_regime_code,
parent_regime_code,
country_code,
geography_type,
geography_id,
effective_from,
effective_to
FROM ZX_REGIMES_B_V r
WHERE country_code = p_country_code
AND (p_tax_determine_date >= effective_from AND
(p_tax_determine_date <= effective_to OR effective_to IS NULL))
AND EXISTS (SELECT /*+ no_unnest */ 1
FROM ZX_SCO_TAXES_B_V t
WHERE t.tax_regime_code = r.tax_regime_code
AND t.live_for_processing_flag = 'Y'
AND t.live_for_applicability_flag = 'Y'
AND (p_tax_determine_date >= t.effective_from AND
(p_tax_determine_date <= t.effective_to OR t.effective_to IS NULL)))
ORDER BY regime_precedence;
SELECT tax_regime_id,
regime_precedence,
tax_regime_code,
parent_regime_code,
country_code,
geography_type,
geography_id,
effective_from,
effective_to
FROM ZX_REGIMES_B_V regime,
hz_relationships relation
WHERE relation.object_id = c_geography_id
AND relation.object_type = 'COUNTRY'
AND relation.subject_id = regime.geography_id
AND relation.subject_type = regime.geography_type
AND c_date >= relation.start_date
AND (c_date <= relation.end_date OR relation.end_date IS NULL)
AND (c_date >= regime.effective_from AND
(c_date <= regime.effective_to OR regime.effective_to IS NULL))
AND EXISTS (SELECT /*+ no_unnest */ 1
FROM ZX_SCO_TAXES_B_V tax
WHERE tax.tax_regime_code = regime.tax_regime_code
AND tax.live_for_processing_flag = 'Y'
AND tax.live_for_applicability_flag = 'Y'
AND (c_date >= tax.effective_from AND
(c_date <= tax.effective_to OR tax.effective_to IS NULL)))
ORDER BY regime.regime_precedence;
SELECT * FROM zx_lines
WHERE trx_id = p_event_class_rec.trx_id
AND application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND manually_entered_flag = 'Y'
AND cancel_flag <> 'Y'
AND mrc_tax_line_flag = 'N'
AND tax_provider_id IS NULL;
SELECT * FROM zx_lines
WHERE trx_id = p_event_class_rec.trx_id
AND application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND tax_provider_id IS NULL
AND cancel_flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N'
AND recalc_required_flag = 'Y'
ORDER BY manually_entered_flag DESC;
SELECT * FROM zx_lines
WHERE trx_id = p_event_class_rec.trx_id
AND application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND tax_provider_id IS NULL
AND cancel_flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N'
AND (recalc_required_flag = 'Y' OR tax_amt_included_flag = 'Y')
ORDER BY manually_entered_flag DESC;
SELECT * FROM zx_lines
WHERE trx_id = p_event_class_rec.trx_id
AND application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND tax_provider_id IS NULL
AND cancel_flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N'
AND (recalc_required_flag = 'Y' OR compounding_tax_flag = 'Y')
ORDER BY manually_entered_flag DESC, compounding_tax_flag DESC,
compounding_dep_tax_flag;
SELECT * FROM zx_lines
WHERE trx_id = p_event_class_rec.trx_id
AND application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND tax_provider_id IS NULL
AND cancel_flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N'
AND (recalc_required_flag = 'Y' OR compounding_tax_flag = 'Y'
OR tax_amt_included_flag = 'Y')
ORDER BY manually_entered_flag DESC, compounding_tax_flag DESC,
compounding_dep_tax_flag;
SELECT * FROM zx_lines
WHERE trx_id = p_event_class_rec.trx_id
AND application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND tax_provider_id IS NULL
AND cancel_flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N'
ORDER BY tax_regime_code, tax, tax_apportionment_line_number;
p_trx_line_index) = 'UPDATE')
OR
(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_event_type_code(
p_trx_line_index) ='UPDATE' AND
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
p_trx_line_index) = 'LINE_INFO_TAX_ONLY')) THEN -- Bug 5291394
-- For Update
--
FOR tax_line_rec IN get_manual_tax_lines LOOP
l_rownum := l_rownum + 1;
/* Bug4959835: Moved to handle_update_scenarios
l_unrounded_taxable_amt zx_lines.unrounded_taxable_amt%TYPE;
SELECT tax_jurisdiction_id,
tax_jurisdiction_code,
tax_regime_code,
tax,
precedence_level
FROM zx_jurisdictions_gt
WHERE tax_regime_code = c_tax_regime_code
AND tax = c_tax
ORDER BY precedence_level;
SELECT tax_id,
tax,
tax_regime_code,
tax_type_code,
tax_precision,
minimum_accountable_unit,
Rounding_Rule_Code,
Tax_Status_Rule_Flag,
Tax_Rate_Rule_Flag,
Place_Of_Supply_Rule_Flag,
Applicability_Rule_Flag,
Tax_Calc_Rule_Flag,
Taxable_Basis_Rule_Flag,
def_tax_calc_formula,
def_taxable_basis_formula,
Reporting_Only_Flag,
tax_currency_code,
Def_Place_Of_Supply_Type_Code,
Def_Registr_Party_Type_Code,
Registration_Type_Rule_Flag,
Direct_Rate_Rule_Flag,
Def_Inclusive_Tax_Flag,
effective_from,
effective_to,
compounding_precedence,
Has_Other_Jurisdictions_Flag,
Live_For_Processing_Flag,
Regn_Num_Same_As_Le_Flag,
applied_amt_handling_flag,
exchange_rate_type,
applicable_by_default_flag,
record_type_code,
tax_exmpt_cr_method_code,
tax_exmpt_source_tax,
legal_reporting_status_def_val,
def_rec_settlement_option_code,
zone_geography_type,
override_geography_type,
allow_rounding_override_flag,
tax_account_source_tax
FROM ZX_SCO_TAXES zxt
WHERE zxt.tax_regime_code = p_tax_regime_code
AND live_for_processing_flag = 'Y'
AND live_for_applicability_flag = 'Y'
AND ( p_tax_determine_date >= effective_from AND
(p_tax_determine_date <= effective_to OR effective_to IS NULL))
AND zxt.offset_tax_flag = 'N'
ORDER BY compounding_precedence;
/* Bug 4959835: Moved to procedure handle_update_scenarios
CURSOR get_key_columns_cur(p_tax zx_lines.tax%TYPE) IS
SELECT tax_line_id,
last_manual_entry,
tax_status_code,
orig_tax_status_id,
orig_tax_status_code,
tax_rate_code,
tax_rate,
orig_tax_rate_id,
orig_tax_rate_code,
orig_tax_rate,
tax_amt,
orig_tax_amt,
taxable_amt,
orig_taxable_amt,
line_amt,
self_assessed_flag,
tax_amt_included_flag,
tax_jurisdiction_id,
tax_jurisdiction_code,
orig_self_assessed_flag,
orig_tax_amt_included_flag,
orig_tax_jurisdiction_id,
orig_tax_jurisdiction_code,
unrounded_taxable_amt,
unrounded_tax_amt,
cal_tax_amt,
associated_child_frozen_flag
FROM zx_lines
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND tax_regime_code = p_tax_regime_code
AND tax = p_tax
AND mrc_tax_line_flag = 'N';
SELECT tax_status_code,
tax_rate_code,
line_amt,
tax_amt,
taxable_amt
FROM zx_lines
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_application_id(p_trx_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_event_class_code(p_trx_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_entity_code(p_trx_line_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_trx_id(p_trx_line_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_trx_level_type(p_trx_line_index)
AND tax_regime_code = c_tax_regime_code
AND tax = c_tax
AND cancel_flag <> 'Y'
AND mrc_tax_line_flag = 'N';
SELECT 1
FROM ZX_SCO_RATES_B_V
WHERE effective_from <= p_tax_determine_date
AND (effective_to >= p_tax_determine_date OR effective_to IS NULL )
AND tax_rate_code = c_tax_rate_code
AND tax_status_code = c_tax_status_code
AND tax = c_tax
AND tax_regime_code = c_tax_regime_code
AND Active_Flag = 'Y'
AND (tax_class = c_tax_class or tax_class IS NULL)
AND ROWNUM=1;
p_trx_line_index) = 'UPDATE' OR
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
p_trx_line_index) = 'CREATE_TAX_ONLY' OR
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
p_trx_line_index) = 'COPY_AND_CREATE'
THEN
FOR l_tax_rec IN get_all_taxes_for_regime_cur LOOP
-- init the local indicator of whether the tax is applicable.
l_tax_applicable := FALSE;
/* Following are commented out since the select column in the cursor
get_all_taxes_for_regime_cur has the same set of column as the definition
of ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl and in the same order. Changed to
assignment as above. If later new columns added to get_all_taxes_for_regime_cur,
but not need to cache, we need to uncomment this part and add the assignment
individually.
ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(
l_tax_rec.tax_id).tax_id := l_tax_rec.tax_id;
p_trx_line_index) = 'UPDATE' OR
(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
p_trx_line_index) IN ('CREATE', 'UPDATE')
AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(
p_trx_line_index) = 'INTERCOMPANY_TRX')
THEN
-- Check if this tax line exists in the new created applicable tax lines
--
l_tax_tbl_subscript := ZX_TDS_UTILITIES_PKG.get_tax_index(
l_tax_rec.tax_regime_code,
l_tax_rec.tax,
l_trx_line_id,
l_trx_level_type,
x_begin_index,
x_end_index,
x_return_status);
p_trx_line_index)='UPDATE') THEN
-- Bug 4959835. Moved the code to a private procedure.
handle_update_scenarios( p_trx_line_index,
p_event_class_rec,
l_new_row_num,
l_tax_rec.tax_regime_code,
l_tax_rec.tax,
p_tax_date,
p_tax_determine_date,
p_tax_point_date,
l_self_assessed_flag,
l_tax_amt_included_flag,
l_tax_jurisdiction_id,
l_tax_jurisdiction_code,
x_return_status);
* SELECT zx_lines_s.NEXTVAL
* INTO ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
* l_new_row_num).tax_line_id
* FROM dual;
p_trx_line_index) IN ('CREATE', 'UPDATE') AND
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(
p_trx_line_index) = 'INTERCOMPANY_TRX'
THEN
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
l_new_row_num).self_assessed_flag := 'Y';
END IF; -- line_level_action = 'CREATE', 'UPDATE', 'CREATE_TAX_ONLY'
SELECT * FROM zx_lines
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index)
AND tax_provider_id IS NULL
AND Cancel_Flag<> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N';
* SELECT zx_lines_s.NEXTVAL
* INTO ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
* l_new_row_num).tax_line_id from dual;
SELECT applied_amt_handling_flag INTO l_applied_amt_handling_flag
FROM zx_taxes_b_tmp
WHERE tax_id = tax_line_rec.tax_id;
* l_new_row_num).LAST_UPDATED_BY := fnd_global.user_id;
* l_new_row_num).LAST_UPDATE_DATE := sysdate;
* l_new_row_num).last_update_login := fnd_global.login_id;
SELECT * FROM zx_lines
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id( p_trx_line_index)
AND trx_line_id =
NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(
p_trx_line_index), trx_line_id)
AND trx_level_type =
NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(
p_trx_line_index), trx_level_type)
/* Bug 5131206:
For partner integration, when the line_level_action is 'ALLOCATE_TAX_ONLY_ADJUSTMENT',
eBTax needs to create prorated tax lines.
In other cases, partner tax lines should be excluded.
*/
-- AND tax_provider_id IS NULL
AND (tax_provider_id IS NULL
OR ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(p_trx_line_index) = 'ALLOCATE_TAX_ONLY_ADJUSTMENT')
AND Cancel_Flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N';
SELECT * FROM zx_lines
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND tax_regime_code = c_tax_regime_code
AND tax = c_tax
AND tax_apportionment_line_number = c_apportionment_line_number
AND mrc_tax_line_flag = 'N';
* SELECT zx_lines_s.NEXTVAL
* INTO ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
* l_new_row_num).tax_line_id from dual;
IF p_event_class_rec.tax_event_type_code = 'UPDATE' AND
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(p_trx_line_index) = 'UPDATE'
THEN
OPEN get_key_columns_cur(tax_line_rec.tax_regime_code, tax_line_rec.tax,tax_line_rec.tax_apportionment_line_number); -- Bug7597449
* l_new_row_num).LAST_UPDATED_BY := fnd_global.user_id;
* l_new_row_num).last_update_date := sysdate;
* l_new_row_num).last_update_login := fnd_global.login_id;
SELECT tax_jurisdiction_code
INTO l_jurisdiction_code
FROM zx_jurisdictions_b jur
WHERE jur.tax_regime_code = p_tax_regime_code
AND jur.tax = p_tax
AND jur.default_jurisdiction_flag = 'Y'
AND p_tax_determine_date between jur.default_flg_effective_from
and nvl(jur.default_flg_effective_to, p_tax_determine_date);
SELECT def_registr_party_type_code,
registration_type_rule_flag
FROM ZX_TAXES_B
WHERE tax_id = p_tax_id;
SELECT chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ledger_id(p_trx_line_index);
SELECT tax_jurisdiction_id,
tax_jurisdiction_code
FROM zx_jurisdictions_b
WHERE tax_regime_code = c_tax_regime_code
AND tax = c_tax
AND default_jurisdiction_flag = 'Y'
AND (p_tax_determine_date >= default_flg_effective_from AND
(p_tax_determine_date <= default_flg_effective_to OR default_flg_effective_to IS NULL));
SELECT zxtr.tax_regime_code,
zxtr.tax,
zxtr.tax_status_code,
zxtr.tax_rate_code,
zxtr.amt_incl_tax_flag,
zxtr.allow_rate_override_flag,
zxtr.tax_class,
zxt.tax_id,
zxt.tax_type_code,
zxt.tax_precision,
zxt.minimum_accountable_unit,
zxt.rounding_rule_code,
zxt.tax_status_rule_flag,
zxt.tax_rate_rule_flag,
zxt.place_of_supply_rule_flag,
zxt.applicability_rule_flag,
zxt.tax_calc_rule_flag,
zxt.taxable_basis_rule_flag,
zxt.def_tax_calc_formula,
zxt.def_taxable_basis_formula,
zxt.reporting_only_flag,
zxt.tax_currency_code,
zxt.def_place_of_supply_type_code,
zxt.def_registr_party_type_code,
zxt.registration_type_rule_flag,
zxt.direct_rate_rule_flag,
zxt.def_inclusive_tax_flag,
zxt.effective_from,
zxt.effective_to,
zxt.compounding_precedence,
zxt.has_other_jurisdictions_flag,
zxt.live_for_processing_flag,
zxt.regn_num_same_as_le_flag,
zxt.applied_amt_handling_flag,
zxt.exchange_rate_type,
zxt.applicable_by_default_flag,
zxt.record_type_code,
zxt.tax_exmpt_cr_method_code,
zxt.tax_exmpt_source_tax,
zxt.legal_reporting_status_def_val,
zxt.def_rec_settlement_option_code,
zxt.zone_geography_type,
zxt.override_geography_type,
zxt.allow_rounding_override_flag,
zxt.tax_account_source_tax
FROM zx_sco_account_rates zxtr,
zx_sco_taxes zxt,
fnd_lookups lc
WHERE zxtr.content_owner_id = p_event_class_rec.first_pty_org_id
AND zxtr.ledger_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ledger_id(p_trx_line_index)
AND zxtr.account_segment_value= c_account_seg_value
AND zxt.tax_regime_code = zxtr.tax_regime_code
AND zxt.tax = zxtr.tax
AND zxt.tax_type_code = lc.lookup_code
AND lc.lookup_type = 'ZX_TAX_TYPE_CATEGORY'
AND NVL(zxt.tax_type_code, 'X') <> 'OFFSET'
AND zxt.live_for_processing_flag = 'Y'
AND (p_tax_determine_date >= zxt.effective_from AND
(p_tax_determine_date <= zxt.effective_to OR zxt.effective_to IS NULL))
ORDER BY zxt.compounding_precedence;
l_sql_statement := 'SELECT ' || l_app_column_name ||
' FROM gl_code_combinations cc ' ||
' WHERE cc.code_combination_id = ' || l_account_ccid;
SELECT zxl.*
FROM zx_lines zxl, zx_evnt_cls_mappings map
WHERE map.application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(p_trx_line_index)
AND map.entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.entity_code(p_trx_line_index)
AND map.event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.event_class_code(p_trx_line_index)
AND zxl.application_id = map.intrcmp_src_appln_id
AND zxl.entity_code = map.intrcmp_src_entity_code
AND zxl.event_class_code = decode(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(p_trx_line_index),
'INTERCOMPANY_TRX',
decode(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_class(p_trx_line_index),
'AP_CREDIT_MEMO', 'CREDIT_MEMO',
'AP_DEBIT_MEMO', 'DEBIT_MEMO',
map.intrcmp_src_evnt_cls_code), map.intrcmp_src_evnt_cls_code)
AND zxl.trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(p_trx_line_index)
AND zxl.trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_line_id(p_trx_line_index)
AND zxl.trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_level_type(p_trx_line_index)
AND zxl.tax_provider_id IS NULL
AND zxl.cancel_flag <> 'Y'
AND zxl.mrc_tax_line_flag = 'N';
SELECT tax_regime_code,
tax,
tax_status_code,
record_type_code,
offset_tax_rate_code,
tax_jurisdiction_code
FROM zx_rates_b
WHERE tax_rate_id = c_tax_rate_id
AND active_flag = 'Y';
SELECT tax_regime_code,
tax,
tax_status_code,
tax_jurisdiction_code
FROM zx_sco_rates_b_v
WHERE tax_rate_code = c_tax_rate_code
AND effective_from <= p_tax_determine_date
AND (effective_to >= p_tax_determine_date OR effective_to IS NULL)
AND tax_class = 'INPUT'
AND active_flag = 'Y'
ORDER BY subscription_level_code;
SELECT tax_regime_code,
tax,
tax_status_code,
tax_jurisdiction_code
FROM zx_sco_rates_b_v
WHERE tax_rate_code = c_tax_rate_code
AND effective_from <= p_tax_determine_date
AND (effective_to >= p_tax_determine_date OR effective_to IS NULL)
AND tax_class IS NULL
AND active_flag = 'Y'
ORDER BY subscription_level_code;
SELECT tax_regime_code,
tax,
tax_status_code,
tax_jurisdiction_code
FROM zx_sco_rates_b_v
WHERE tax_rate_code = c_tax_rate_code
AND effective_from <= p_tax_determine_date
AND (effective_to >= p_tax_determine_date OR effective_to IS NULL)
AND active_flag = 'Y'
AND tax_class <> 'INPUT'
ORDER BY subscription_level_code;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl.delete(l_rownum);
SELECT cg.constraint_id
FROM zx_condition_groups_b cg,
ar_tax_conditions_all tc
WHERE cg.condition_group_code like c_tax_classification_code||'~'||'%'
AND cg.enabled_flag = 'Y'
AND cg.constraint_id IS NOT NULL
AND tc.tax_condition_id = cg.constraint_id
AND tc.org_id = c_internal_organization_id;
SELECT /*+ leading(PR) use_nl(RL) */
rl.tax_regime_code,
rl.tax,
pr.status_result,
pr.rate_result,
pr.condition_set_id,
pr.exception_set_id,
pr.result_id,
1 query_num
FROM ZX_PROCESS_RESULTS pr,
ZX_SCO_RULES_B_V rl
--ZX_SCO_RATES_B_V rt
WHERE (pr.CONDITION_GROUP_CODE = c_condition_group_code or
pr.CONDITION_GROUP_CODE like c_condition_group_code ||'~'||'%')
AND rl.effective_from <= c_tax_determine_date ---Bug 5691957
AND (rl.effective_to >= c_tax_determine_date OR rl.effective_to IS NULL ) ---Bug 5691957
AND pr.enabled_flag = 'Y'
AND rl.tax_rule_id = pr.tax_rule_id
AND rl.service_type_code = 'DET_DIRECT_RATE'
AND EXISTS
(SELECT /*+ no_unnest */ 1
FROM ZX_SCO_RATES_B_V rt
WHERE rt.tax_regime_code = rl.tax_regime_code -- bug 6680676
AND rt.tax = rl.tax
AND rt.tax_status_code = pr.status_result
AND rt.tax_rate_code = pr.rate_result
AND rt.effective_from <= c_tax_determine_date
AND (rt.effective_to >= c_tax_determine_date OR rt.effective_to IS NULL )
AND rt.Active_Flag = 'Y'
AND (rt.tax_class = c_tax_class OR rt.tax_class IS NULL))
UNION ALL
SELECT /*+ leading(RT.a) use_nl(RT.sd) */
DISTINCT rt.tax_regime_code,
rt.tax,
rt.tax_status_code,
rt.tax_rate_code,
NULL condition_set_id,
NULL exception_set_id,
NULL result_id,
2 query_num
FROM ZX_SCO_RATES_B_V rt
WHERE rt.tax_rate_code = c_tax_rate_code
AND rt.rate_type_code <> 'RECOVERY'
AND rt.effective_from <= c_tax_determine_date
AND (rt.effective_to >= c_tax_determine_date OR rt.effective_to IS NULL )
AND rt.Active_Flag = 'Y'
AND (rt.tax_class = c_tax_class or rt.tax_class IS NULL)
-- Bug 5481559: Though jurisdiction code is NULL for migrated tax classification
-- codes, it can be entered for newly created tax rates, in which
-- case,w e should consider the tax rate if jurisdiction matches. i-- Jurisdiction match will be checked when place of supply is
-- validated.
-- AND rt.tax_jurisdiction_code is NULL
AND EXISTS (SELECT 1
FROM ZX_SCO_TAXES_B_V tax
WHERE tax.tax_regime_code = rt.tax_regime_code
AND tax.tax = rt.tax
AND tax.live_for_processing_flag = 'Y'
AND tax.live_for_applicability_flag = 'Y');
SELECT 1
FROM ZX_SCO_RATES_B_V
WHERE tax_regime_code = c_tax_regime_code
AND tax = c_tax
AND tax_status_code = c_tax_status_code
AND tax_rate_code = c_tax_rate_code
AND effective_from <= c_tax_determine_date
AND (effective_to >= c_tax_determine_date OR effective_to IS NULL )
AND Active_Flag = 'Y'
AND (tax_class = c_tax_class or tax_class IS NULL)
AND rownum=1;
SELECT tax_jurisdiction_id,
tax_jurisdiction_code,
tax_regime_code,
tax,
precedence_level
FROM zx_jurisdictions_gt
WHERE tax_regime_code = c_tax_regime_code
AND tax = c_tax
ORDER BY precedence_level;
SELECT tax_id,
tax,
tax_regime_code,
tax_type_code,
tax_precision,
minimum_accountable_unit,
Rounding_Rule_Code,
Tax_Status_Rule_Flag,
Tax_Rate_Rule_Flag,
Place_Of_Supply_Rule_Flag,
Applicability_Rule_Flag,
Tax_Calc_Rule_Flag,
Taxable_Basis_Rule_Flag,
def_tax_calc_formula,
def_taxable_basis_formula,
Reporting_Only_Flag,
tax_currency_code,
Def_Place_Of_Supply_Type_Code,
Def_Registr_Party_Type_Code,
Registration_Type_Rule_Flag,
Direct_Rate_Rule_Flag,
Def_Inclusive_Tax_Flag,
effective_from,
effective_to,
compounding_precedence,
Has_Other_Jurisdictions_Flag,
Live_For_Processing_Flag,
Regn_Num_Same_As_Le_Flag,
applied_amt_handling_flag,
exchange_rate_type,
applicable_by_default_flag,
record_type_code,
tax_exmpt_cr_method_code,
tax_exmpt_source_tax,
legal_reporting_status_def_val,
def_rec_settlement_option_code,
zone_geography_type,
override_geography_type,
allow_rounding_override_flag,
tax_account_source_tax
FROM ZX_SCO_TAXES_B_V
WHERE tax = c_tax
AND tax_regime_code = c_tax_regime_code
AND (effective_from <= c_tax_determine_date AND
(effective_to >= c_tax_determine_date OR effective_to IS NULL))
AND live_for_processing_flag = 'Y'
AND live_for_applicability_flag = 'Y'
-- AND rownum = 1;
p_trx_line_index) = 'UPDATE' OR
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
p_trx_line_index) = 'CREATE_TAX_ONLY' OR
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
p_trx_line_index) = 'COPY_AND_CREATE'
THEN
IF p_event_class_rec.prod_family_grp_code = 'O2C' THEN
OPEN get_unique_constraint_csr(l_tax_classification_code,
l_internal_organization_id);
l_action_rec_tbl.delete;
SELECT DISTINCT rt.tax_status_code,
rt.tax_rate_code,
2
INTO l_status_result_tbl(j),
l_rate_result_tbl(j),
l_query_num_tbl(j)
FROM ZX_SCO_RATES_B_V rt
WHERE rt.tax_rate_code = l_action_rec_tbl(i).action_value
AND tax_regime_code = l_tax_regime_code_tbl(j)
AND tax = l_tax_tbl(j)
AND rt.effective_from <= p_tax_determine_date
AND (rt.effective_to >= p_tax_determine_date
OR rt.effective_to IS NULL )
AND rt.Active_Flag = 'Y'
AND (rt.tax_class = l_tax_class or rt.tax_class IS NULL)
AND EXISTS (SELECT 1
FROM ZX_SCO_TAXES_B_V tax
WHERE tax.tax_regime_code = rt.tax_regime_code
AND tax.tax = rt.tax
AND tax.live_for_processing_flag = 'Y'
AND tax.live_for_applicability_flag = 'Y');
Open select_tax_status_rate_code (p_tax_regime_code, p_tax, l_override_tax_rate_code,
p_tax_determine_date);
fetch select_tax_status_rate_code into l_tax_status_code, l_tax_rate_code;
If select_tax_status_rate_code%NOTFOUND then
--A record does not exist with that tax rate code for the given tax.
--Raise error;
IF select_tax_status_rate_code%isopen then
Close select_tax_status_rate_code;
Close select_tax_status_rate_code;
p_trx_line_index)='UPDATE') THEN
handle_update_scenarios(p_trx_line_index,
p_event_class_rec,
l_rownum,
l_tax_rec.tax_regime_code,
l_tax_rec.tax,
p_tax_date,
p_tax_determine_date,
p_tax_point_date,
l_self_assessed_flag,
l_tax_amt_included_flag,
l_tax_jurisdiction_id,
l_tax_jurisdiction_code,
x_return_status);
p_trx_line_index) IN ('CREATE', 'UPDATE') AND
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(
p_trx_line_index) = 'INTERCOMPANY_TRX'
THEN
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
l_rownum).self_assessed_flag := 'Y';
END IF; -- line_level_action = 'CREATE', 'UPDATE', 'CREATE_TAX_ONLY'
PROCEDURE handle_update_scenarios(
p_trx_line_index IN BINARY_INTEGER,
p_event_class_rec IN zx_api_pub.event_class_rec_type,
p_row_num IN NUMBER,
p_tax_regime_code IN zx_regimes_b.tax_regime_code%TYPE,
p_tax IN zx_taxes_b.tax%TYPE,
p_tax_date IN DATE,
p_tax_determine_date IN DATE,
p_tax_point_date IN DATE,
x_self_assessed_flag OUT NOCOPY zx_lines.self_assessed_flag%TYPE,
x_tax_amt_included_flag OUT NOCOPY zx_lines.tax_amt_included_flag%TYPE,
x_tax_jurisdiction_id OUT NOCOPY zx_lines.tax_jurisdiction_id%TYPE,
x_tax_jurisdiction_code OUT NOCOPY zx_lines.tax_jurisdiction_code%TYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
-- Local variables
l_current_line_amt zx_lines.line_amt%TYPE;
SELECT * FROM zx_lines
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_trx_line_index)
AND tax_regime_code = p_tax_regime_code
AND tax = p_tax
AND mrc_tax_line_flag = 'N'
AND tax_apportionment_line_number > 0
ORDER BY tax_apportionment_line_number;
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.BEGIN',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(+)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.BEGIN',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'Value for TAX_RATE_TYPE '||ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
p_row_num).TAX_RATE_TYPE);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'Incorrect return_status after calling '||
'ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info.');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.END',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'Incorrect return_status after calling '||
'ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info.');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.END',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'Incorrect return_status after calling '||
'ZX_TDS_UTILITIES_PKG.get_tax_rate_info.');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.END',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'Incorrect return_status after calling '||
'ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info.');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.END',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'Incorrect return_status after calling '||
'ZX_TDS_UTILITIES_PKG.get_tax_rate_info.');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.END',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'Incorrect return_status after calling '||
'ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info.');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.END',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'Incorrect return_status after calling '||
'ZX_TDS_UTILITIES_PKG.get_tax_rate_info.');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.END',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
* SELECT zx_lines_s.NEXTVAL
* INTO ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(
* p_row_num).tax_line_id
* FROM dual;
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
'get_key_columns_cur NOTFOUND..');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.BEGIN',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios',
l_error_buffer);
'ZX.PLSQL.ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios.END',
'ZX_TDS_APPLICABILITY_DETM_PKG.handle_update_scenarios(-)');
END handle_update_scenarios;
SELECT tax_status_code,
tax_rate_code,
line_amt,
tax_amt,
taxable_amt,
-- nipatel bug 6648042
tax_apportionment_line_number
-- nipatel bug 6648042
FROM zx_lines
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_application_id(p_trx_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_event_class_code(p_trx_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_entity_code(p_trx_line_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_trx_id(p_trx_line_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_line_id(p_trx_line_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_trx_level_type(p_trx_line_index)
AND tax_regime_code = c_tax_regime_code
AND tax = c_tax
AND cancel_flag <> 'Y'
AND mrc_tax_line_flag = 'N';