[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM zx_rec_nrec_dist
WHERE NVL(reverse_flag,'N') = 'N'
AND tax_line_id = p_detail_tax_line_tbl(p_tax_line_index).tax_line_id
AND trx_line_dist_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_dist_id(
p_trx_line_dist_index)
ORDER BY rec_nrec_tax_dist_id, recoverable_flag;
SELECT max(rec_nrec_tax_dist_number)
FROM zx_rec_nrec_dist
WHERE tax_line_id = p_detail_tax_line_tbl(p_tax_line_index).tax_line_id
AND trx_line_dist_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_dist_id(
p_trx_line_dist_index);
SELECT zx_rec_nrec_dist_s.nextval INTO
p_rec_nrec_dist_tbl(i).rec_nrec_tax_dist_id FROM DUAL;
SELECT zx_rec_nrec_dist_s.nextval INTO l_tax_dist_id from dual;
SELECT zx_rec_nrec_dist_s.nextval INTO l_tax_dist_id from dual;
SELECT max(rec_nrec_tax_dist_number)
FROM zx_rec_nrec_dist
WHERE tax_line_id = c_tax_line_id
AND trx_line_dist_id = c_trx_line_dist_id;
SELECT * FROM zx_rec_nrec_dist
WHERE trx_id = p_event_class_rec.trx_id
AND 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 tax_line_id = p_detail_tax_line_tbl(p_tax_line_index).tax_line_id
AND NVL(reverse_flag ,'N') = 'N'
AND freeze_flag = 'Y'
ORDER BY trx_line_dist_id;
SELECT zx_rec_nrec_dist_s.NEXTVAL INTO p_rec_nrec_dist_tbl(i).rec_nrec_tax_dist_id FROM DUAL;
SELECT allow_recoverability_flag,
primary_recovery_type_code,
primary_rec_type_rule_flag,
secondary_recovery_type_code,
secondary_rec_type_rule_flag,
primary_rec_rate_det_rule_flag,
sec_rec_rate_det_rule_flag,
def_primary_rec_rate_code,
def_secondary_rec_rate_code,
effective_from,
effective_to,
def_rec_settlement_option_code,
tax_account_source_tax
FROM ZX_TAXES_B
WHERE tax_id = c_tax_id;
SELECT max(rec_nrec_tax_dist_number)
FROM zx_rec_nrec_dist
WHERE tax_line_id = p_detail_tax_line_tbl(p_tax_line_index).tax_line_id
AND trx_line_dist_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_dist_id(
p_trx_line_dist_index);
SELECT recovery_rule_code
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 active_flag = 'Y'
AND rate_type_code <> 'RECOVERY'
AND effective_from <= c_tax_date
AND (effective_to >= c_tax_date OR effective_to IS NULL)
--AND rownum = 1
ORDER BY subscription_level_code;
SELECT recovery_rule_code,
default_rec_rate_code,
def_rec_settlement_option_code
FROM ZX_RATES_B
WHERE tax_rate_id = c_tax_rate_id;
SELECT tax_rate_id,
percentage_rate,
allow_adhoc_tax_rate_flag
FROM ZX_SCO_RATES_B_V
WHERE tax_regime_code = c_tax_regime_code
AND tax = c_tax
AND tax_rate_code = c_tax_rate_code
AND rate_type_code = 'RECOVERY'
AND recovery_type_code = c_recovery_type_code
AND active_flag = 'Y'
AND (tax_class = c_tax_class or tax_class IS NULL)
AND effective_from <= c_tax_date
AND (effective_to >= c_tax_date OR effective_to IS NULL)
--AND rownum = 1
ORDER BY tax_class NULLS LAST, subscription_level_code;
SELECT rate.tax_rate_id,
rate.tax_rate_code,
rate.percentage_rate,
rate.allow_adhoc_tax_rate_flag
FROM ZX_SCO_RATES_B_V rate
WHERE tax = c_tax
AND tax_regime_code = c_tax_regime_code
AND active_flag = 'Y'
-- AND tax_jurisdiction_code(+) = c_tax_jurisdiction_code
AND default_flg_effective_from <= c_tax_date
AND (default_flg_effective_to >= c_tax_date OR
default_flg_effective_to IS NULL)
AND rate_type_code = 'RECOVERY'
AND rate.recovery_type_code = c_recovery_type_code
AND (rate.tax_class = c_tax_class or rate.tax_class IS NULL)
AND default_rate_flag = 'Y'
--AND rownum = 1
ORDER BY rate.tax_class NULLS LAST, rate.subscription_level_code;
SELECT default_rec_rate_code
FROM ZX_SCO_RATES_B_V rate
WHERE rate.effective_from <= c_tax_date
AND (rate.effective_to >= c_tax_date OR rate.effective_to IS NULL)
AND rate.tax_rate_code = c_tax_rate_code
AND rate.tax_status_code = c_tax_status_code
AND rate.tax = c_tax
AND rate.active_flag = 'Y'
AND rate_type_code <> 'RECOVERY'
AND rate.default_rec_type_code = c_recovery_type_code
AND rate.tax_regime_code = c_tax_regime_code
--AND rownum = 1
ORDER BY rate.subscription_level_code;
SELECT allow_recoverability_flag,
primary_recovery_type_code,
primary_rec_type_rule_flag,
secondary_recovery_type_code,
secondary_rec_type_rule_flag,
primary_rec_rate_det_rule_flag,
sec_rec_rate_det_rule_flag,
def_primary_rec_rate_code,
def_secondary_rec_rate_code,
effective_from,
effective_to,
def_rec_settlement_option_code,
tax_account_source_tax
FROM ZX_TAXES_B
WHERE tax_id = c_tax_id;
SELECT tax_account_entity_id
FROM zx_accounts
WHERE tax_account_entity_id = c_recovery_rate_id
AND tax_account_entity_code = 'RATES'
AND ledger_id = c_ledger_id
AND internal_organization_id = c_internal_org_id;
SELECT zx_rec_nrec_dist_s.nextval INTO
p_rec_nrec_dist_tbl(p_rnd_end_index).rec_nrec_tax_dist_id from dual;
l_non_zero_rec_tax_dists_tbl.DELETE;
l_non_zero_nrec_tax_dists_tbl.DELETE;
SELECT recoverable_flag,
recovery_type_code,
rec_type_rule_flag,
rec_rate_det_rule_flag,
recovery_rate_id,
recovery_rate_code,
rec_nrec_rate,
rec_nrec_tax_dist_id,
rec_nrec_tax_amt,
rec_nrec_tax_amt_tax_curr,
rec_nrec_tax_amt_funcl_curr,
trx_line_dist_amt,
def_rec_settlement_option_code,
account_source_tax_rate_id
FROM zx_rec_nrec_dist
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(
p_trx_line_dist_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(
p_trx_line_dist_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(
p_trx_line_dist_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(
p_trx_line_dist_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(
p_trx_line_dist_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(
p_trx_line_dist_index)
AND trx_line_dist_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_dist_id(
p_trx_line_dist_index)
AND tax_id = p_tax_id
AND NVL(reverse_flag, 'N') <> 'Y'
ORDER BY rec_nrec_tax_dist_id, recoverable_flag;
SELECT recoverable_flag,
recovery_type_code,
rec_type_rule_flag,
rec_rate_det_rule_flag,
recovery_rate_id,
recovery_rate_code,
rec_nrec_rate,
rec_nrec_tax_dist_id,
def_rec_settlement_option_code,
account_source_tax_rate_id
FROM zx_rec_nrec_dist
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(
p_trx_line_dist_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(
p_trx_line_dist_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(
p_trx_line_dist_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(
p_trx_line_dist_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(
p_trx_line_dist_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(
p_trx_line_dist_index)
AND trx_line_dist_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_dist_id(
p_trx_line_dist_index)
AND tax_id = p_tax_id
AND NVL(reverse_flag, 'N') <> 'Y'
ORDER BY rec_nrec_tax_dist_id, recoverable_flag;
SELECT recoverable_flag,
recovery_type_code,
rec_type_rule_flag,
rec_rate_det_rule_flag,
recovery_rate_code,
rec_nrec_tax_dist_id,
rec_nrec_rate,
orig_rec_nrec_rate,
rec_rate_result_id
FROM zx_rec_nrec_dist
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_application_id(
p_trx_line_dist_index)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_entity_code(
p_trx_line_dist_index)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_event_class_code(
p_trx_line_dist_index)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_trx_id(
p_trx_line_dist_index)
AND trx_line_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_line_id(
p_trx_line_dist_index)
AND trx_level_type =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_trx_level_type(
p_trx_line_dist_index)
AND trx_line_dist_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ref_doc_dist_id(
p_trx_line_dist_index)
AND tax = p_detail_tax_line_tbl(p_tax_line_index).tax
AND tax_regime_code = p_detail_tax_line_tbl(p_tax_line_index).tax_regime_code
AND NVL(reverse_flag, 'N') <> 'Y'
ORDER BY rec_nrec_tax_dist_id, recoverable_flag;
SELECT max(rec_nrec_tax_dist_number)
FROM zx_rec_nrec_dist
WHERE tax_line_id = p_detail_tax_line_tbl(p_tax_line_index).tax_line_id
AND trx_line_dist_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_dist_id(p_trx_line_dist_index);
UPDATE zx_rec_nrec_dist_gt gt
SET per_trx_curr_unit_nr_amt =
-- Bugfix 5218651: for amount based matching, when dist qty is 0 or null, return rec_nrec_tax_amt as per unit nr amount
DECODE(gt.trx_line_dist_qty*gt.unit_price,0, gt.rec_nrec_tax_amt,
gt.rec_nrec_tax_amt/(gt.trx_line_dist_qty*gt.unit_price)),
per_unit_nrec_tax_amt =
DECODE(gt.trx_line_dist_qty, 0, gt.rec_nrec_tax_amt,
gt.rec_nrec_tax_amt/gt.trx_line_dist_qty),
(ref_doc_tax_dist_id,
ref_doc_unit_price,
ref_per_trx_curr_unit_nr_amt,
ref_doc_per_unit_nrec_tax_amt)=
(SELECT
rec_nrec_tax_dist_id,
unit_price,
DECODE(gt.ref_doc_trx_line_dist_qty,
NULL, DECODE(dist.unit_price, 0, dist.rec_nrec_tax_amt, dist.rec_nrec_tax_amt/dist.unit_price),
DECODE(gt.ref_doc_trx_line_dist_qty*dist.unit_price, 0, dist.rec_nrec_tax_amt,
dist.rec_nrec_tax_amt/(gt.ref_doc_trx_line_dist_qty*dist.unit_price))),
DECODE(gt.ref_doc_trx_line_dist_qty, 0, dist.rec_nrec_tax_amt,
dist.rec_nrec_tax_amt/gt.ref_doc_trx_line_dist_qty)
FROM zx_rec_nrec_dist dist
WHERE dist.application_id = gt.ref_doc_application_id
AND dist.entity_code = gt.ref_doc_entity_code
AND dist.event_class_code = gt.ref_doc_event_class_code
AND dist.trx_id = gt.ref_doc_trx_id
AND dist.trx_line_id = gt.ref_doc_line_id
AND dist.trx_level_type = gt.ref_doc_trx_level_type
AND (dist.trx_line_dist_id = gt.ref_doc_dist_id OR
gt.tax_only_line_flag = 'Y')
AND dist.tax_regime_code = gt.tax_regime_code
AND dist.tax = gt.tax
AND dist.recoverable_flag = 'N'
AND NVL(dist.reverse_flag,'N') = 'N'
AND dist.mrc_tax_dist_flag = 'N'
)
WHERE ref_doc_application_id IS NOT NULL
AND recoverable_flag = 'N'
AND NVL(reverse_flag,'N')='N'
AND mrc_tax_dist_flag = 'N';
UPDATE zx_rec_nrec_dist_gt gt
SET ref_doc_unit_price =
(SELECT unit_price
FROM zx_lines_det_factors line
WHERE line.application_id = gt.ref_doc_application_id
AND line.entity_code = gt.ref_doc_entity_code
AND line.event_class_code = gt.ref_doc_event_class_code
AND line.trx_id = gt.ref_doc_trx_id
AND line.trx_line_id = gt.ref_doc_line_id
AND line.trx_level_type = gt.ref_doc_trx_level_type
)
WHERE ref_doc_application_id IS NOT NULL
AND recoverable_flag = 'N'
AND NVL(reverse_flag,'N')='N'
AND mrc_tax_dist_flag = 'N'
AND ref_doc_tax_dist_id IS NULL;
SELECT ledger_id,
reporting_currency_code,
currency_conversion_rate,
currency_conversion_date,
currency_conversion_type,
minimum_accountable_unit,
precision,
tax_line_id,
summary_tax_line_id,
tax_amt
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 = c_trx_line_id
AND trx_level_type = c_trx_level_type
AND tax_line_number = c_tax_line_number
AND mrc_tax_line_flag = 'Y';
SELECT zx_rec_nrec_dist_s.NEXTVAL INTO
p_rec_nrec_dist_tbl(p_rnd_end_index).rec_nrec_tax_dist_id FROM DUAL;