[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'
OR ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(
p_trx_line_index) = 'CREATE_WITH_TAX') -- Bug 8205359
))) AND --bug#8534499
(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(
p_trx_line_index) <> 'INTERCOMPANY_TRX' OR
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(
p_trx_line_index) IS NULL) 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);
SELECT count(*) into l_count
FROM zx_lines_det_factors
WHERE application_id = l_upg_trx_info_rec.application_id
AND entity_code = l_upg_trx_info_rec.entity_code
AND trx_id = l_upg_trx_info_rec.trx_id
AND trx_line_id = l_upg_trx_info_rec.trx_line_id
AND trx_level_type = l_upg_trx_info_rec.trx_level_type;
SELECT count(*) into l_count
FROM zx_lines_det_factors
WHERE application_id = l_upg_trx_info_rec.application_id
AND entity_code = l_upg_trx_info_rec.entity_code
AND trx_id = l_upg_trx_info_rec.trx_id;
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) */
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,
ROUND(SUM(tax_line.unrounded_tax_amt), 20)
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) 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 tax_line.offset_tax_rate_code 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;
SELECT /*+ INDEX(tax_line ZX_DETAIL_TAX_LINES_GT_U1) */
-- Added following columns for Bug#13093258 --
tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
tax_line.trx_line_id,
ROUND(SUM(tax_line.unrounded_tax_amt), 20) total_line_tx_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 tax_line.offset_tax_rate_code 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(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
zx_detail_tax_lines_gt
SET tax_hold_code = ZX_TDS_CALC_SERVICES_PUB_PKG.g_tax_variance_hold_val,
tax_hold_released_code = BITAND(ZX_TDS_CALC_SERVICES_PUB_PKG.g_tax_variance_hold_val,
NVL(tax_hold_released_code,0))
WHERE application_id = l_application_id_tbl(i)
AND entity_code = l_entity_code_tbl(i)
AND event_class_code = l_event_class_code_tbl(i)
AND trx_id = l_trx_id_tbl(i)
AND trx_line_id = l_trx_line_id_tbl(i);
l_application_id_tbl.DELETE;
l_event_class_code_tbl.DELETE;
l_entity_code_tbl.DELETE;
l_trx_id_tbl.DELETE;
l_trx_line_id_tbl.DELETE;
l_total_line_tx_amt_tbl.DELETE;
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_ctrl_total_line_tx_amt_tbl(i)/l_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'), bug 14395492
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,
ROUND((line.unrounded_tax_amt * (l_ctrl_total_line_tx_amt_tbl(i)/l_total_line_tx_amt_tbl(i)))/line.tax_rate*100 , 20)
),
'Y', decode ( NVL(rate.ADJ_FOR_ADHOC_AMT_CODE, 'TAXABLE_BASIS'),
'TAXABLE_BASIS', decode(line.tax_rate, 0, line.unrounded_taxable_amt,
ROUND((line.unrounded_tax_amt * (l_ctrl_total_line_tx_amt_tbl(i)/l_total_line_tx_amt_tbl(i)))/line.tax_rate*100, 20)
),
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,
ROUND((line.unrounded_tax_amt * (l_ctrl_total_line_tx_amt_tbl(i)/l_total_line_tx_amt_tbl(i)))/line.unrounded_taxable_amt*100 , 20)
),
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 line.offset_tax_rate_code IS NULL
AND NVL(cancel_flag,'N') <> 'Y';
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
trx_line.ctrl_total_hdr_tx_amt,
ROUND(SUM(tax_line.unrounded_tax_amt), 20)
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 tax_line.offset_tax_rate_code 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;
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
-- Added following columns for Bug#13093258 --
tax_line.application_id,
tax_line.event_class_code,
tax_line.entity_code,
tax_line.trx_id,
ROUND(SUM(tax_line.unrounded_tax_amt), 20) total_trx_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 tax_line.offset_tax_rate_code 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(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
zx_detail_tax_lines_gt
SET tax_hold_code = ZX_TDS_CALC_SERVICES_PUB_PKG.g_tax_variance_hold_val,
tax_hold_released_code = BITAND(ZX_TDS_CALC_SERVICES_PUB_PKG.g_tax_variance_hold_val,
NVL(tax_hold_released_code,0))
WHERE application_id = l_application_id_tbl(i)
AND entity_code = l_entity_code_tbl(i)
AND event_class_code = l_event_class_code_tbl(i)
AND trx_id = l_trx_id_tbl(i);
l_application_id_tbl.DELETE;
l_event_class_code_tbl.DELETE;
l_entity_code_tbl.DELETE;
l_trx_id_tbl.DELETE;
l_total_trx_tax_amt_tbl.DELETE;
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_ctrl_total_hdr_tx_amt_tbl(i)/l_total_trx_tax_amt_tbl(i)))),
--line.sync_with_prvdr_flag = DECODE(tax_provider_id, NULL, 'N', 'Y'), bug 14395492
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,
ROUND((unrounded_tax_amt * (l_ctrl_total_hdr_tx_amt_tbl(i)/l_total_trx_tax_amt_tbl(i)))/line.tax_rate*100, 20))
),
'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,
ROUND((line.unrounded_tax_amt*(l_ctrl_total_hdr_tx_amt_tbl(i)/l_total_trx_tax_amt_tbl(i)))/line.tax_rate*100, 20))
),
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,
Round((line.unrounded_tax_amt*(l_ctrl_total_hdr_tx_amt_tbl(i)/l_total_trx_tax_amt_tbl(i)))/line.unrounded_taxable_amt*100, 20)
)
),
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_tax_rate_code 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),
SUM(tax_amt_funcl_curr),
SUM(tax_amt_tax_curr),
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 offset_tax_rate_code 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 AND
SUM(tax_amt) <> 0;
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)
,tax_amt_funcl_curr = Decode(tax_amt_funcl_curr * currency_conversion_rate,
NULL, NULL,
(tax_amt_funcl_curr
+ round_tax_curr_xml_inv (
l_ctrl_total_line_tx_amt_tbl(i) * currency_conversion_rate,
ledger_id,
tax_id,
tax_currency_code,
Rounding_Rule_Code,
'FUNCL_CURR',
precision,
minimum_accountable_unit
)
- NVL(l_total_fun_tax_amt_tbl(i),
round_tax_curr_xml_inv (
l_total_line_tx_amt_tbl(i) * currency_conversion_rate,
ledger_id,
tax_id,
tax_currency_code,
Rounding_Rule_Code,
'FUNCL_CURR',
precision,
minimum_accountable_unit
)
)
)
)
,tax_amt_tax_curr = Decode(tax_amt_tax_curr * tax_currency_conversion_rate,
NULL, NULL,
(tax_amt_tax_curr
+ round_tax_curr_xml_inv (
l_ctrl_total_line_tx_amt_tbl(i) * tax_currency_conversion_rate,
ledger_id,
tax_id,
tax_currency_code,
Rounding_Rule_Code,
'TAX_CURR',
precision,
minimum_accountable_unit
)
- NVL(l_total_tax_cur_amt_tbl(i),
round_tax_curr_xml_inv (
l_total_line_tx_amt_tbl(i) * tax_currency_conversion_rate,
ledger_id,
tax_id,
tax_currency_code,
Rounding_Rule_Code,
'TAX_CURR',
precision,
minimum_accountable_unit
)
)
)
)
--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_tax_rate_code 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),
SUM(tax_line.tax_amt_funcl_curr),
SUM(tax_line.tax_amt_tax_curr),
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.offset_tax_rate_code 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 AND
SUM(tax_line.tax_amt) <> 0;
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)
,tax_amt_funcl_curr = Decode(tax_amt_funcl_curr * currency_conversion_rate,
NULL, NULL,
(tax_amt_funcl_curr
+ round_tax_curr_xml_inv (
l_ctrl_total_hdr_tx_amt_tbl(i) * currency_conversion_rate,
ledger_id,
tax_id,
tax_currency_code,
Rounding_Rule_Code,
'FUNCL_CURR',
precision,
minimum_accountable_unit
)
- NVL(l_total_fun_tax_amt_tbl(i),
round_tax_curr_xml_inv (
l_total_trx_tax_amt_tbl(i) * currency_conversion_rate,
ledger_id,
tax_id,
tax_currency_code,
Rounding_Rule_Code,
'FUNCL_CURR',
precision,
minimum_accountable_unit
)
)
)
)
,tax_amt_tax_curr = Decode(tax_amt_tax_curr * tax_currency_conversion_rate,
NULL, NULL,
(tax_amt_tax_curr
+ round_tax_curr_xml_inv (
l_ctrl_total_hdr_tx_amt_tbl(i) * tax_currency_conversion_rate,
ledger_id,
tax_id,
tax_currency_code,
Rounding_Rule_Code,
'TAX_CURR',
precision,
minimum_accountable_unit
)
- NVL(l_total_tax_cur_amt_tbl(i),
round_tax_curr_xml_inv (
l_total_trx_tax_amt_tbl(i) * tax_currency_conversion_rate,
ledger_id,
tax_id,
tax_currency_code,
Rounding_Rule_Code,
'TAX_CURR',
precision,
minimum_accountable_unit
)
)
)
)
--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_tax_rate_code 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_TDS_CALC_SERVICES_PUB_PKG.g_ln_action_update_exist_flg := 'N';
ZX_GLOBAL_STRUCTURES_PKG.g_update_event_process_flag := 'N';
ZX_GLOBAL_STRUCTURES_PKG.ptnr_tax_regime_tbl.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.lte_trx_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_TPI_SERVICES_PKG.tax_regime_tmp_tbl.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.g_hz_zone_tbl.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL.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;
SELECT assoc.reporting_code_id
INTO l_reporting_code_id
FROM zx_reporting_types_b types,
zx_report_codes_assoc assoc
WHERE types.legal_message_flag = 'Y'
AND assoc.entity_code = 'ZX_PROCESS_RESULTS'
AND assoc.entity_id = p_result_id
AND assoc.reporting_type_id = types.reporting_type_id
AND l_date BETWEEN assoc.effective_from AND NVL(assoc.effective_to, l_date);
SELECT NVL(cur.minimum_accountable_unit, power(10, (-1 * precision))),precision
INTO l_mau,l_precision
FROM fnd_currencies cur, gl_sets_of_books sob
WHERE sob.set_of_books_id = p_ledger_id
AND cur.currency_code = sob.currency_code;