[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,
default_rec_rate_code,
def_rec_settlement_option_code,
default_rec_type_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)
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 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'
ORDER BY rate.tax_class NULLS LAST, 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 tax_rate_id
FROM zx_rates_b
WHERE tax = c_tax
AND tax_regime_code = c_tax_regime_code
AND content_owner_id IN (c_content_owner_id,-99)
AND tax_rate_code = 'AD_HOC_RECOVERY'
AND rate_type_code = 'RECOVERY'
AND recovery_type_code = 'STANDARD'
AND record_type_code = 'MIGRATED'
AND active_flag = 'Y'
AND (tax_class = c_tax_class OR tax_class IS NULL)
ORDER BY tax_class NULLS LAST, content_owner_id DESC;
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
-- Modified the condition for Bug#16601146
AND NVL(tax_apportionment_line_number,p_tax_apportionment_line_num) = p_tax_apportionment_line_num
-- AND NVL(tax_apportionment_line_number,-99) =
-- DECODE(historical_flag,'Y',p_tax_apportionment_line_num,NVL(tax_apportionment_line_number,-99))
AND NVL(reverse_flag, 'N') <> 'Y'
ORDER BY rec_nrec_tax_dist_id, recoverable_flag;
UPDATE zx_rec_nrec_dist zd
SET tax_apportionment_line_number =
(SELECT tax_apportionment_line_number
FROM zx_lines zl
WHERE zl.tax_line_id = zd.tax_line_id)
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_apportionment_line_number IS NULL
AND NVL(reverse_flag, 'N') <> 'Y';
' Rows updated : '|| TO_CHAR(SQL%ROWCOUNT));
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
-- Modified the following condition for Bug#16601146
AND NVL(tax_apportionment_line_number,p_tax_apportionment_line_num) = p_tax_apportionment_line_num
-- AND NVL(tax_apportionment_line_number,-99) =
-- DECODE(historical_flag,'Y',p_tax_apportionment_line_num,NVL(tax_apportionment_line_number,-99))
AND NVL(reverse_flag, 'N') <> 'Y'
ORDER BY rec_nrec_tax_dist_id, recoverable_flag;
UPDATE zx_rec_nrec_dist zd
SET tax_apportionment_line_number =
(SELECT tax_apportionment_line_number
FROM zx_lines zl
WHERE zl.tax_line_id = zd.tax_line_id)
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_apportionment_line_number IS NULL
AND NVL(reverse_flag, 'N') <> 'Y';
' Rows updated : '|| TO_CHAR(SQL%ROWCOUNT));
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,
recovery_rate_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 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);
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, NVL(gt.unrounded_rec_nrec_tax_amt,gt.rec_nrec_tax_amt),
NVL(gt.unrounded_rec_nrec_tax_amt,gt.rec_nrec_tax_amt)/(gt.trx_line_dist_qty * gt.unit_price * (1 - (ZX_TRD_SERVICES_PUB_PKG.g_retained_value/100)))),
per_unit_nrec_tax_amt =
DECODE(gt.trx_line_dist_qty, 0, NVL(gt.unrounded_rec_nrec_tax_amt,gt.rec_nrec_tax_amt),
NVL(gt.unrounded_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,
--Bug 9470313
Nvl(unit_price,0),
DECODE(gt.ref_doc_trx_line_dist_qty,
NULL, DECODE(Nvl(dist.unit_price,0), 0, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/dist.trx_line_dist_amt, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/dist.unit_price),
DECODE(gt.ref_doc_trx_line_dist_qty*dist.unit_price, 0, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt),
NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/(gt.ref_doc_trx_line_dist_qty*dist.unit_price))),
DECODE(Nvl(gt.ref_doc_trx_line_dist_qty,0), 0, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/dist.trx_line_dist_amt,
NVL(dist.unrounded_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
),
ref_per_trx_curr_unit_nr_amt = 0,
ref_doc_per_unit_nrec_tax_amt = 0
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;
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, NVL(gt.unrounded_rec_nrec_tax_amt,gt.rec_nrec_tax_amt),
NVL(gt.unrounded_rec_nrec_tax_amt,gt.rec_nrec_tax_amt)/(gt.trx_line_dist_qty * gt.unit_price * (ZX_TRD_SERVICES_PUB_PKG.g_retained_value/100))),
per_unit_nrec_tax_amt =
DECODE(gt.trx_line_dist_qty, 0, NVL(gt.unrounded_rec_nrec_tax_amt,gt.rec_nrec_tax_amt),
NVL(gt.unrounded_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,
--Bug 9470313
Nvl(unit_price,0),
DECODE(gt.ref_doc_trx_line_dist_qty,
NULL, DECODE(Nvl(dist.unit_price,0), 0, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/dist.trx_line_dist_amt, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/dist.unit_price),
DECODE(gt.ref_doc_trx_line_dist_qty*dist.unit_price, 0, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt),
(NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt))*(ZX_TRD_SERVICES_PUB_PKG.g_retained_value/100)/(gt.ref_doc_trx_line_dist_qty*dist.unit_price*(ZX_TRD_SERVICES_PUB_PKG.g_retained_value/100)))),
DECODE(Nvl(gt.ref_doc_trx_line_dist_qty,0), 0, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/dist.trx_line_dist_amt,
NVL(dist.unrounded_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
),
ref_per_trx_curr_unit_nr_amt = 0,
ref_doc_per_unit_nrec_tax_amt = 0
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;
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, NVL(gt.unrounded_rec_nrec_tax_amt,gt.rec_nrec_tax_amt),
NVL(gt.unrounded_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, NVL(gt.unrounded_rec_nrec_tax_amt,gt.rec_nrec_tax_amt),
NVL(gt.unrounded_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,
--Bug 9470313
Nvl(unit_price,0),
DECODE(gt.ref_doc_trx_line_dist_qty,
NULL, DECODE(Nvl(dist.unit_price,0), 0, NVL(dist.unrounded_rec_nrec_tax_amt, dist.rec_nrec_tax_amt)/dist.trx_line_dist_amt, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/dist.unit_price),
DECODE(gt.ref_doc_trx_line_dist_qty*dist.unit_price, 0, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt),
NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/(gt.ref_doc_trx_line_dist_qty*dist.unit_price))),
DECODE(Nvl(gt.ref_doc_trx_line_dist_qty,0), 0, NVL(dist.unrounded_rec_nrec_tax_amt,dist.rec_nrec_tax_amt)/dist.trx_line_dist_amt,
NVL(dist.unrounded_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
),
ref_per_trx_curr_unit_nr_amt = 0,
ref_doc_per_unit_nrec_tax_amt = 0,
per_trx_curr_unit_nr_amt =
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)
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;