[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT template_usage_code
FROM zx_det_factor_templ_b
WHERE DET_FACTOR_TEMPL_CODE = p_event_class_rec.DET_FACTOR_TEMPL_CODE;
SELECT intrcmp_src_appln_id,
intrcmp_src_entity_code,
intrcmp_src_evnt_cls_code
FROM zx_evnt_cls_mappings
WHERE application_id = c_application_id
AND entity_code = c_entity_code
AND event_class_code = c_event_class_code;
p_trx_line_index) = 'UPDATE'
THEN
ZX_GLOBAL_STRUCTURES_PKG.g_update_event_process_flag := 'Y';
p_trx_line_index) = 'UPDATE' AND
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
p_trx_line_index) IN ('CANCEL', 'SYNCHRONIZE', 'DISCARD', 'NO_CHANGE',
'UNAPPLY_FROM')
THEN
IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.historical_flag(p_trx_line_index) = 'Y' AND
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(p_trx_line_index) IN
('CANCEL', 'DISCARD', 'NO_CHANGE', 'UNAPPLY_FROM')
THEN
l_upg_trx_info_rec.application_id
:= ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(p_trx_line_index);
g_check_cond_grp_tbl.DELETE;
g_tsrm_num_value_tbl.DELETE;
g_tsrm_alphanum_value_tbl.DELETE;
g_trx_alphanum_value_tbl.DELETE;
p_trx_line_index) ='UPDATE' OR
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_event_type_code(
p_trx_line_index) = 'OVERRIDE_TAX') THEN
-- IF it is a migrated transaction,
-- fetch all tax lines from current document, skip applicability process
-- Only do tax amount calculation and taxable basis
-- OR IF tax_event_type_code = 'OVERRIDE_TAX',
-- fetch all tax lines from current document, skip applicability process
-- ELSIF applied_from_application_id IS NOT NULL,
-- call get_det_tax_lines_from_applied. Skip applicability process
-- ELSIF adjusted_doc_application_id IS NOT NULL,
-- call get_det_tax_lines_from_adjusted. Skip applicability process
-- ELSE perform applicability process.
--
-- Bug 5688340: Rearranged the order of conditions in IF statement
-- (ie. pulled adjusted_doc is NOT NULL condition before
-- applied_from).
-- The receipt application in AR causes a tax adjustment to be
-- created in eBTax, if an earned discount is recognized.
-- In this case, AR passes invoice info in adjusted doc columns
-- and cash receipt info in applied from columns.
-- In this case, tax calculation must be done using invoice
-- (ie. adjusted doc info).
IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.historical_flag(p_trx_line_index)
= 'Y' OR
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_event_type_code(
p_trx_line_index) = 'OVERRIDE_TAX'
THEN
ZX_TDS_APPLICABILITY_DETM_PKG.fetch_tax_lines(
p_event_class_rec,
p_trx_line_index,
NULL,
NULL,
NULL,
l_begin_index,
l_end_index,
x_return_status);
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
ZX_TDS_APPLICABILITY_DETM_PKG.fetch_tax_lines(
p_event_class_rec,
p_trx_line_index,
l_tax_date,
l_tax_determine_date,
l_tax_point_date,
l_begin_index,
l_end_index,
x_return_status);
END IF; -- -- line_level_action = 'UPDATE'
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'
AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(
p_trx_line_index) IS NOT NULL
THEN
ZX_TDS_APPLICABILITY_DETM_PKG.get_taxes_for_intercomp_trx(
p_event_class_rec,
p_trx_line_index,
l_tax_date,
l_tax_determine_date,
l_tax_point_date,
l_begin_index,
l_end_index,
x_return_status);
ELSE -- tax_event_type_code other than 'CREATE','UPDATE','OVERRIDE_TAX'
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- bug 4893261
END IF; -- tax_event_type_code 'CREATE','UPDATE','OVERRIDE_TAX'
(p_event_class_rec.tax_event_type_code = 'UPDATE' AND
ZX_TDS_CALC_SERVICES_PUB_PKG.g_overridden_tax_ln_exist_flg = 'Y'
)
)
THEN
ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance(
p_event_class_rec,
x_return_status,
l_error_buffer);
PROCEDURE update_exchange_rate (
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
p_ledger_id IN NUMBER,
p_currency_conversion_rate IN NUMBER,
p_currency_conversion_type IN VARCHAR2,
p_currency_conversion_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2 ) IS
l_error_buffer VARCHAR2(240);
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate.BEGIN',
'ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate(+)');
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate',
'Incorrect return_status after calling ' ||
'ZX_TDS_TAX_ROUNDING_PKG.convert_and_round_curr()');
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate.END',
'ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate(-)');
ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate (
p_event_class_rec => p_event_class_rec,
p_ledger_id => p_ledger_id,
p_currency_conversion_rate => p_currency_conversion_rate,
p_currency_conversion_type => p_currency_conversion_type,
p_currency_conversion_date => p_currency_conversion_date,
x_return_status => x_return_status);
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate',
'Incorrect return_status after calling ' ||
'ZX_TRD_INTERNAL_SERVICES_PVT.update_exchange_rate()');
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate.END',
'ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate(-)');
ZX_TRL_PUB_PKG.update_exchange_rate (
p_event_class_rec => p_event_class_rec,
x_return_status => x_return_status);
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate',
'Incorrect return_status after calling ' ||
'ZX_TRL_PUB_PKG.update_exchange_rate()');
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate',
'RETURN_STATUS = ' || x_return_status);
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate.END',
'ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate(-)');
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate.END',
'ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate(-)'||x_return_status);
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate.END',
'ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate(-)');
END update_exchange_rate;
p_detail_tax_lines_rec.delete_flag := NVL( p_detail_tax_lines_rec.delete_flag, 'N' );
| Procedure dump_detail_tax_lines_into_gt is called to insert detail |
| tax lines into the global temporary table zx_detail_tax_lines_gt |
| when the number of tax lines in the g_detail_tax_line_tbl |
| reaches 1000 |
* =====================================================================*/
PROCEDURE dump_detail_tax_lines_into_gt (
p_detail_tax_lines_tbl IN OUT NOCOPY detail_tax_lines_tbl_type,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
INSERT INTO zx_detail_tax_lines_gt
VALUES p_detail_tax_lines_tbl(tax_line_index);
| Procedure dump_detail_tax_lines_into_gt is called to insert detail |
| tax lines into the global temporary table zx_detail_tax_lines_gt |
* =====================================================================*/
PROCEDURE dump_detail_tax_lines_into_gt (
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl.DELETE;
g_detail_tax_lines_tbl.DELETE;
g_fsc_tbl.DELETE;
ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl.DELETE;
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */ *
FROM zx_detail_tax_lines_gt
WHERE trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_id(p_line_index)
AND application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.application_id(p_line_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.entity_code(p_line_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.event_class_code(p_line_index);
g_detail_tax_lines_tbl.delete;
SELECT /*+ INDEX(tax_line ZX_DETAIL_TAX_LINES_GT_U1) */
SUM(tax_line.unrounded_tax_amt),
tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
tax_line.trx_line_id,
tax_line.trx_level_type,
tax_line.ctrl_total_line_tx_amt
FROM zx_detail_tax_lines_gt tax_line
WHERE
-- commented out for bug fix 5417887
-- tax_line.application_id = p_event_class_rec.application_id
-- AND tax_line.event_class_code = p_event_class_rec.event_class_code
-- AND tax_line.entity_code = p_event_class_rec.entity_code
-- AND tax_line.trx_id = p_event_class_rec.trx_id
-- AND
nvl(tax_line.ctrl_total_line_tx_amt,0) <> 0
AND tax_line.self_assessed_flag <> 'Y'
AND tax_line.offset_flag <> 'Y'
AND tax_line.offset_link_to_tax_line_id IS NULL
AND NVL(cancel_flag,'N') <> 'Y'
GROUP BY tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
tax_line.trx_line_id,
tax_line.trx_level_type,
tax_line.ctrl_total_line_tx_amt;
SELECT /*+ INDEX(tax_line ZX_DETAIL_TAX_LINES_GT_U1) */
SUM(tax_line.unrounded_tax_amt)
FROM zx_detail_tax_lines_gt tax_line
WHERE tax_line.ctrl_total_line_tx_amt <> 0
AND tax_line.self_assessed_flag <> 'Y'
AND tax_line.offset_flag <> 'Y'
AND tax_line.offset_link_to_tax_line_id IS NULL
AND NVL(cancel_flag,'N') <> 'Y'
GROUP BY tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
tax_line.trx_line_id,
tax_line.trx_level_type,
tax_line.ctrl_total_line_tx_amt
HAVING NVL(SUM(tax_line.unrounded_tax_amt),0) = 0;
UPDATE /*+ INDEX(line ZX_DETAIL_TAX_LINES_GT_U1) */
zx_detail_tax_lines_gt line
SET line.orig_tax_amt = line.unrounded_tax_amt,
line.orig_taxable_amt = line.unrounded_taxable_amt,
line.orig_tax_rate = line.tax_rate,
line.tax_amt = NULL,
line.unrounded_tax_amt = line.unrounded_tax_amt/l_total_line_tx_amt_tbl(i) * l_ctrl_total_line_tx_amt_tbl(i),
line.ctrl_total_line_tx_amt = l_ctrl_total_line_tx_amt_tbl(i),
line.sync_with_prvdr_flag = DECODE(line.tax_provider_id, NULL, 'N', 'Y'),
line.overridden_flag = 'Y',
line.last_manual_entry = 'TAX_AMOUNT',
line.taxable_amt = NULL,
(line.unrounded_taxable_amt,
line.tax_rate,
line.taxable_basis_formula)
= (select decode ( NVL(rate.ALLOW_ADHOC_TAX_RATE_FLAG, 'N'),
'N', decode ( line.tax_rate, 0, line.unrounded_taxable_amt,
(line.unrounded_tax_amt/l_total_line_tx_amt_tbl(i) * l_ctrl_total_line_tx_amt_tbl(i))/line.tax_rate*100 ),
'Y', decode ( NVL(rate.ADJ_FOR_ADHOC_AMT_CODE, 'TAXABLE_BASIS'),
'TAXABLE_BASIS', decode(line.tax_rate, 0, line.unrounded_taxable_amt,
(line.unrounded_tax_amt/l_total_line_tx_amt_tbl(i) * l_ctrl_total_line_tx_amt_tbl(i))/line.tax_rate*100 ),
line.unrounded_taxable_amt),
line.unrounded_taxable_amt ) unrounded_taxable_amt,
decode ( NVL(rate.ALLOW_ADHOC_TAX_RATE_FLAG, 'N'),
'Y', decode ( NVL(rate.ADJ_FOR_ADHOC_AMT_CODE, 'TAXABLE_BASIS'),
'TAX_RATE', decode(line.unrounded_taxable_amt, 0, line.tax_rate,
(line.unrounded_tax_amt/l_total_line_tx_amt_tbl(i) * l_ctrl_total_line_tx_amt_tbl(i))/line.unrounded_taxable_amt*100 ),
line.tax_rate),
line.tax_rate ) tax_rate,
decode ( NVL(rate.ALLOW_ADHOC_TAX_RATE_FLAG, 'N'),
'N', decode ( line.tax_rate, 0, line.taxable_basis_formula, 'PRORATED_TB' ),
'Y', decode ( NVL(rate.ADJ_FOR_ADHOC_AMT_CODE, 'TAXABLE_BASIS'),
'TAXABLE_BASIS', decode(line.tax_rate, 0, line.taxable_basis_formula, 'PRORATED_TB'),
line.taxable_basis_formula),
line.taxable_basis_formula ) taxable_basis_formula
from zx_rates_b rate
where line.tax_rate_id = rate.tax_rate_id
)
WHERE line.application_id = l_application_id_tbl(i)
AND line.event_class_code = l_event_class_code_tbl(i)
AND line.entity_code = l_entity_code_tbl(i)
AND line.trx_id = l_trx_id_tbl(i)
AND line.trx_line_id = l_trx_line_id_tbl(i)
AND line.trx_level_type = l_trx_level_type_tbl(i)
AND nvl(line.ctrl_total_line_tx_amt,0) <> 0 -- change for this bug 7000903
AND line.self_assessed_flag <> 'Y'
AND line.offset_flag <> 'Y'
AND line.offset_link_to_tax_line_id IS NULL
AND NVL(cancel_flag,'N') <> 'Y';
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
SUM(tax_line.unrounded_tax_amt),
tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
trx_line.ctrl_total_hdr_tx_amt
FROM zx_detail_tax_lines_gt tax_line,
zx_lines_det_factors trx_line
WHERE tax_line.application_id = trx_line.application_id
AND tax_line.event_class_code = trx_line.event_class_code
AND tax_line.entity_code = trx_line.entity_code
AND tax_line.trx_id = trx_line.trx_id
-- bugfix 5599951
AND tax_line.trx_line_id = trx_line.trx_line_id
AND tax_line.trx_level_type = trx_line.trx_level_type
AND trx_line.ctrl_total_hdr_tx_amt IS NOT NULL
AND tax_line.self_assessed_flag <> 'Y'
AND tax_line.offset_flag <> 'Y'
AND tax_line.offset_link_to_tax_line_id IS NULL
AND NVL(cancel_flag,'N') <> 'Y'
GROUP BY tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
trx_line.ctrl_total_hdr_tx_amt;
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
SUM(tax_line.unrounded_tax_amt)
FROM zx_detail_tax_lines_gt tax_line,
zx_lines_det_factors trx_line
WHERE tax_line.application_id = trx_line.application_id
AND tax_line.event_class_code = trx_line.event_class_code
AND tax_line.entity_code = trx_line.entity_code
AND tax_line.trx_id = trx_line.trx_id
-- bugfix 5599951
AND tax_line.trx_line_id = trx_line.trx_line_id
AND tax_line.trx_level_type = trx_line.trx_level_type
AND trx_line.ctrl_total_hdr_tx_amt <> 0
AND tax_line.self_assessed_flag <> 'Y'
AND tax_line.offset_flag <> 'Y'
AND tax_line.offset_link_to_tax_line_id IS NULL
AND NVL(cancel_flag,'N') <> 'Y'
GROUP BY tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
trx_line.ctrl_total_hdr_tx_amt
HAVING NVL(SUM(tax_line.unrounded_tax_amt), 0) = 0;
UPDATE /*+ INDEX(line ZX_DETAIL_TAX_LINES_GT_U1) */
zx_detail_tax_lines_gt line
SET line.orig_tax_amt = line.unrounded_tax_amt,
line.orig_taxable_amt = line.unrounded_taxable_amt,
line.orig_tax_rate = line.tax_rate,
line.tax_amt = NULL,
line.unrounded_tax_amt = DECODE(l_total_trx_tax_amt_tbl(i),
0, 0, (line.unrounded_tax_amt/l_total_trx_tax_amt_tbl(i)) * l_ctrl_total_hdr_tx_amt_tbl(i)),
line.sync_with_prvdr_flag = DECODE(tax_provider_id, NULL, 'N', 'Y'),
line.overridden_flag = 'Y',
line.last_manual_entry = 'TAX_AMOUNT',
line.taxable_amt = NULL,
(line.unrounded_taxable_amt,
line.tax_rate,
line.taxable_basis_formula)
= (select decode ( NVL(rate.ALLOW_ADHOC_TAX_RATE_FLAG, 'N'),
'N', decode(line.tax_rate, 0, line.unrounded_taxable_amt,
DECODE(l_total_trx_tax_amt_tbl(i), 0, line.unrounded_taxable_amt,
(unrounded_tax_amt/l_total_trx_tax_amt_tbl(i) * l_ctrl_total_hdr_tx_amt_tbl(i))/line.tax_rate*100 )
),
'Y', decode ( NVL(rate.ADJ_FOR_ADHOC_AMT_CODE, 'TAXABLE_BASIS'),
'TAXABLE_BASIS', decode(line.tax_rate, 0, line.unrounded_taxable_amt,
DECODE(l_total_trx_tax_amt_tbl(i), 0, line.unrounded_taxable_amt,
(line.unrounded_tax_amt/l_total_trx_tax_amt_tbl(i)*l_ctrl_total_hdr_tx_amt_tbl(i))/line.tax_rate*100)
),
line.unrounded_taxable_amt),
line.unrounded_taxable_amt ) unrounded_taxable_amt,
decode ( NVL(rate.ALLOW_ADHOC_TAX_RATE_FLAG, 'N'),
'Y', decode(NVL(rate.ADJ_FOR_ADHOC_AMT_CODE, 'TAXABLE_BASIS'),
'TAX_RATE',
decode(line.unrounded_taxable_amt,
0, line.tax_rate,
decode(l_total_trx_tax_amt_tbl(i),
0, line.tax_rate,
(line.unrounded_tax_amt/l_total_trx_tax_amt_tbl(i)*l_ctrl_total_hdr_tx_amt_tbl(i))/line.unrounded_taxable_amt*100
)
),
line.tax_rate),
line.tax_rate ) tax_rate,
decode ( NVL(rate.ALLOW_ADHOC_TAX_RATE_FLAG, 'N'),
'N', decode ( line.tax_rate, 0, line.taxable_basis_formula, 'PRORATED_TB' ),
'Y', decode ( NVL(rate.ADJ_FOR_ADHOC_AMT_CODE, 'TAXABLE_BASIS'),
'TAXABLE_BASIS', decode(line.tax_rate, 0, line.taxable_basis_formula, 'PRORATED_TB'),
line.taxable_basis_formula),
line.taxable_basis_formula ) taxable_basis_formula
from zx_rates_b rate
where line.tax_rate_id = rate.tax_rate_id
)
WHERE line.application_id = l_application_id_tbl(i)
AND line.event_class_code = l_event_class_code_tbl(i)
AND line.entity_code = l_entity_code_tbl(i)
AND line.trx_id = l_trx_id_tbl(i)
AND line.self_assessed_flag <> 'Y'
AND line.offset_link_to_tax_line_id IS NULL
AND line.offset_flag <> 'Y'
AND line.mrc_tax_line_flag = 'N'
AND NVL(cancel_flag,'N') <> 'Y';
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
SUM(tax_amt),
MAX(tax_amt),
application_id,
event_class_code,
entity_code,
trx_id,
trx_line_id,
trx_level_type,
ctrl_total_line_tx_amt
FROM zx_detail_tax_lines_gt
WHERE
-- commented out for bug fix 5417887
-- 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
ctrl_total_line_tx_amt IS NOT NULL
AND self_assessed_flag <> 'Y'
AND offset_flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N'
AND NVL(cancel_flag,'N') <> 'Y'
GROUP BY application_id,
event_class_code,
entity_code,
trx_id,
trx_line_id,
trx_level_type,
ctrl_total_line_tx_amt
HAVING SUM(tax_amt) <> ctrl_total_line_tx_amt;
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
zx_detail_tax_lines_gt
SET tax_amt = tax_amt + l_ctrl_total_line_tx_amt_tbl(i) - l_total_line_tx_amt_tbl(i)
--sync_with_prvdr_flag = DECODE(tax_provider_id, NULL, 'N', 'Y') -- this should have already been set during proration
WHERE application_id = l_application_id_tbl(i)
AND event_class_code = l_event_class_code_tbl(i)
AND entity_code = l_entity_code_tbl(i)
AND trx_id = l_trx_id_tbl(i)
AND trx_line_id = l_trx_line_id_tbl(i)
AND trx_level_type = l_trx_level_type_tbl(i)
AND ctrl_total_line_tx_amt IS NOT NULL
AND self_assessed_flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND offset_flag <> 'Y'
AND mrc_tax_line_flag = 'N'
AND tax_amt = l_max_tax_amt_tbl(i)
AND NVL(cancel_flag,'N') <> 'Y'
AND rownum = 1;
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
SUM(tax_line.tax_amt),
MAX(tax_line.tax_amt),
tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
trx_line.ctrl_total_hdr_tx_amt
FROM zx_detail_tax_lines_gt tax_line,
zx_lines_det_factors trx_line
WHERE tax_line.application_id = trx_line.application_id
AND tax_line.event_class_code = trx_line.event_class_code
AND tax_line.entity_code = trx_line.entity_code
AND tax_line.trx_id = trx_line.trx_id
-- bugfix 5599951
AND tax_line.trx_line_id = trx_line.trx_line_id
AND tax_line.trx_level_type = trx_line.trx_level_type
AND tax_line.self_assessed_flag <> 'Y'
AND tax_line.offset_flag <> 'Y'
AND tax_line.offset_link_to_tax_line_id IS NULL
AND tax_line.mrc_tax_line_flag = 'N'
AND trx_line.ctrl_total_hdr_tx_amt IS NOT NULL
AND NVL(cancel_flag,'N') <> 'Y'
GROUP BY tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
trx_line.ctrl_total_hdr_tx_amt
HAVING SUM(tax_line.tax_amt) <> trx_line.ctrl_total_hdr_tx_amt;
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
zx_detail_tax_lines_gt
SET tax_amt = tax_amt + l_ctrl_total_hdr_tx_amt_tbl(i) - l_total_trx_tax_amt_tbl(i)
--sync_with_prvdr_flag = DECODE(tax_provider_id, NULL, 'N', 'Y') -- this should have already been set during process proration
WHERE application_id = l_application_id_tbl(i)
AND event_class_code = l_event_class_code_tbl(i)
AND entity_code = l_entity_code_tbl(i)
AND trx_id = l_trx_id_tbl(i)
AND self_assessed_flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND offset_flag <> 'Y'
AND mrc_tax_line_flag = 'N'
AND tax_amt = l_max_tax_amt_tbl(i)
AND NVL(cancel_flag,'N') <> 'Y'
AND rownum = 1;
SELECT tax_amt, summary_tax_line_id
FROM zx_lines_summary
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 last_manual_entry = 'TAX_AMOUNT' -- manual or overridden sum tax line
AND adjust_tax_amt_flag = 'Y'
AND nvl(cancel_flag,'N') <> 'Y'
AND nvl(self_assessed_flag,'N') <> 'Y'
AND tax_provider_id IS NULL;
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
SUM(tax_amt), MAX(tax_amt)
FROM zx_detail_tax_lines_gt
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 summary_tax_line_id = p_summary_tax_line_id;
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
zx_detail_tax_lines_gt
SET tax_amt = tax_amt + l_rounding_diff_tbl(i)
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 summary_tax_line_id = l_summary_tax_line_id_tbl(i)
AND tax_amt = l_max_tax_amt_tbl(i)
AND rownum = 1;
DELETE FROM ZX_DETAIL_TAX_LINES_GT;
ZX_GLOBAL_STRUCTURES_PKG.g_update_event_process_flag := 'N';
ZX_GLOBAL_STRUCTURES_PKG.ptnr_tax_regime_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_check_cond_grp_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_fsc_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_tsrm_num_value_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_tsrm_alphanum_value_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_trx_alphanum_value_tbl.DELETE;
ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl.DELETE;
ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl.DELETE;
ZX_TDS_UTILITIES_PKG.g_tax_status_info_tbl.DELETE;
ZX_TDS_UTILITIES_PKG.g_currency_rec_tbl.DELETE;
ZX_TRD_INTERNAL_SERVICES_PVT.g_tax_recovery_info_tbl.DELETE;
ZX_TDS_TAX_ROUNDING_PKG.g_currency_tbl.DELETE;
ZX_TDS_TAX_ROUNDING_PKG.g_tax_curr_conv_rate_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_class_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_cq_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_data_type_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_code_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_tax_parameter_code_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_operator_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_numeric_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_date_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_alphanum_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_value_low_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_value_high_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_segment_array.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_check_cond_grp_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_tsrm_num_value_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_tsrm_alphanum_value_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_trx_alphanum_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_class_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_cq_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_data_type_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_code_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_tax_parameter_code_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_operator_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_numeric_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_date_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_alphanum_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_value_low_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_value_high_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_segment_array.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl.DELETE;
ZX_TRD_INTERNAL_SERVICES_PVT.g_tax_recovery_info_tbl.DELETE;
ZX_SRVC_TYP_PKG.l_line_level_tbl.DELETE;
delete from zx_jurisdictions_gt;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_check_cond_grp_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_tsrm_num_value_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_tsrm_alphanum_value_tbl.DELETE;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_trx_alphanum_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_class_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_cq_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_data_type_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_determining_factor_code_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_tax_parameter_code_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_operator_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_numeric_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_date_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_alphanum_value_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_value_low_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_value_high_tbl.DELETE;
ZX_TDS_RULE_BASE_DETM_PVT.g_segment_array.DELETE;
delete from zx_jurisdictions_gt;
ZX_TDS_RULE_BASE_DETM_PVT.g_segment_array.DELETE;
DELETE FROM ZX_REC_NREC_DIST_GT;
SELECT process_for_applicability_flag
FROM zx_party_tax_profile
WHERE party_tax_profile_id = p_tax_prof_id;