[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* (p_event_class_rec.tax_event_type_code = 'UPDATE' AND
* p_tax_line_rec.line_level_action = 'CREATE') OR
* (p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX' AND
* p_tax_line_rec.Manually_Entered_Flag = 'Y') ) THEN
* populate_orig_columns(p_tax_line_rec);
SELECT ZX_LINES_S.nextval
INTO p_tax_line_rec.tax_line_id
FROM dual;
p_tax_line_rec.last_updated_by := fnd_global.user_id;
p_tax_line_rec.last_update_login := fnd_global.login_id;
p_tax_line_rec.last_update_date := sysdate;
SELECT ZX_LINES_S.nextval
INTO ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).tax_line_id
FROM dual;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_updated_by :=
fnd_global.user_id;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_update_login :=
fnd_global.login_id;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(i).last_update_date :=
sysdate;
INSERT INTO zx_detail_tax_lines_gt
( tax_line_id,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_line_id,
trx_level_type,
trx_line_number,
doc_event_status,
-- line_event_status,
tax_event_class_code,
tax_event_type_code,
tax_line_number,
content_owner_id,
tax_regime_id,
tax_regime_code,
tax_id,
tax,
tax_status_id,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
tax_apportionment_line_number,
trx_id_level2,
trx_id_level3,
trx_id_level4,
trx_id_level5,
trx_id_level6,
trx_user_key_level1,
trx_user_key_level2,
trx_user_key_level3,
trx_user_key_level4,
trx_user_key_level5,
trx_user_key_level6,
mrc_tax_line_flag,
ledger_id,
establishment_id,
legal_entity_id,
legal_entity_tax_reg_number,
hq_estb_reg_number,
hq_estb_party_tax_prof_id,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
tax_currency_conversion_date,
tax_currency_conversion_type,
tax_currency_conversion_rate,
trx_currency_code,
minimum_accountable_unit,
precision,
trx_number,
trx_date,
unit_price,
line_amt,
trx_line_quantity,
tax_base_modifier_rate,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_line_quantity,
other_doc_line_amt,
other_doc_line_tax_amt,
other_doc_line_taxable_amt,
unrounded_taxable_amt,
unrounded_tax_amt,
related_doc_application_id,
related_doc_entity_code,
related_doc_event_class_code,
related_doc_trx_id,
related_doc_number,
related_doc_date,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
applied_from_trx_level_type,
applied_from_trx_number,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
adjusted_doc_line_id,
adjusted_doc_trx_level_type,
adjusted_doc_number,
adjusted_doc_date,
applied_to_application_id,
applied_to_event_class_code,
applied_to_entity_code,
applied_to_trx_id,
applied_to_line_id,
applied_to_trx_number,
summary_tax_line_id,
offset_link_to_tax_line_id,
offset_flag,
process_for_recovery_flag,
tax_jurisdiction_id,
tax_jurisdiction_code,
place_of_supply,
place_of_supply_type_code,
place_of_supply_result_id,
tax_date_rule_id,
tax_date,
tax_determine_date,
tax_point_date,
trx_line_date,
tax_type_code,
tax_code,
tax_registration_id,
tax_registration_number,
registration_party_type,
rounding_level_code,
rounding_rule_code,
rounding_lvl_party_tax_prof_id,
rounding_lvl_party_type,
compounding_tax_flag,
orig_tax_status_id,
orig_tax_status_code,
orig_tax_rate_id,
orig_tax_rate_code,
orig_tax_rate,
orig_tax_jurisdiction_id,
orig_tax_jurisdiction_code,
orig_tax_amt_included_flag,
orig_self_assessed_flag,
tax_currency_code,
tax_amt,
tax_amt_tax_curr,
tax_amt_funcl_curr,
taxable_amt,
taxable_amt_tax_curr,
taxable_amt_funcl_curr,
orig_taxable_amt,
orig_taxable_amt_tax_curr,
cal_tax_amt,
cal_tax_amt_tax_curr,
cal_tax_amt_funcl_curr,
orig_tax_amt,
orig_tax_amt_tax_curr,
rec_tax_amt,
rec_tax_amt_tax_curr,
rec_tax_amt_funcl_curr,
nrec_tax_amt,
nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
tax_apportionment_flag,
historical_flag,
taxable_basis_formula,
tax_calculation_formula,
cancel_flag,
purge_flag,
delete_flag,
tax_amt_included_flag,
self_assessed_flag,
overridden_flag,
manually_entered_flag,
freeze_until_overridden_flag,
copied_from_other_doc_flag,
recalc_required_flag,
settlement_flag,
item_dist_changed_flag,
associated_child_frozen_flag,
tax_only_line_flag,
compounding_dep_tax_flag,
last_manual_entry,
tax_provider_id,
record_type_code,
reporting_period_id,
legal_message_appl_2,
legal_message_status,
legal_message_rate,
legal_message_basis,
legal_message_calc,
legal_message_threshold,
legal_message_pos,
legal_message_trn,
legal_message_exmpt,
legal_message_excpt,
tax_regime_template_id,
tax_applicability_result_id,
direct_rate_result_id,
status_result_id,
rate_result_id,
basis_result_id,
thresh_result_id,
calc_result_id,
tax_reg_num_det_result_id,
eval_exmpt_result_id,
eval_excpt_result_id,
enforce_from_natural_acct_flag,
tax_hold_code,
tax_hold_released_code,
prd_total_tax_amt,
prd_total_tax_amt_tax_curr,
prd_total_tax_amt_funcl_curr,
internal_org_location_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
numeric1,
numeric2,
numeric3,
numeric4,
numeric5,
numeric6,
numeric7,
numeric8,
numeric9,
numeric10,
char1,
char2,
char3,
char4,
char5,
char6,
char7,
char8,
char9,
char10,
date1,
date2,
date3,
date4,
date5,
date6,
date7,
date8,
date9,
date10,
tax_rate_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
line_assessable_value,
legal_justification_text1,
legal_justification_text2,
legal_justification_text3,
reporting_currency_code,
trx_line_index,
offset_tax_rate_code,
proration_code,
other_doc_source,
reporting_only_flag,
ctrl_total_line_tx_amt,
sync_with_prvdr_flag,
interface_entity_code,
interface_tax_line_id,
taxing_juris_geography_id,
adjusted_doc_tax_line_id,
object_version_number,
legal_reporting_status,
account_source_tax_rate_id
)
(SELECT /*+ leading(G) index(l ZX_LINES_u1 ) */
L.tax_line_id,
L.internal_organization_id,
L.application_id,
L.entity_code,
L.event_class_code,
L.event_type_code,
L.trx_id,
L.trx_line_id,
L.trx_level_type,
L.trx_line_number,
L.doc_event_status,
-- L.line_event_status,
L.tax_event_class_code,
L.tax_event_type_code,
L.tax_line_number,
L.content_owner_id,
L.tax_regime_id,
L.tax_regime_code,
L.tax_id,
L.tax,
L.tax_status_id,
L.tax_status_code,
L.tax_rate_id,
L.tax_rate_code,
L.tax_rate,
L.tax_apportionment_line_number,
L.trx_id_level2,
L.trx_id_level3,
L.trx_id_level4,
L.trx_id_level5,
L.trx_id_level6,
L.trx_user_key_level1,
L.trx_user_key_level2,
L.trx_user_key_level3,
L.trx_user_key_level4,
L.trx_user_key_level5,
L.trx_user_key_level6,
L.mrc_tax_line_flag,
G.ledger_id,
G.establishment_id,
G.legal_entity_id,
L.legal_entity_tax_reg_number,
L.hq_estb_reg_number,
L.hq_estb_party_tax_prof_id,
G.currency_conversion_date,
G.currency_conversion_type,
G.currency_conversion_rate,
L.tax_currency_conversion_date,
L.tax_currency_conversion_type,
L.tax_currency_conversion_rate,
L.trx_currency_code,
L.minimum_accountable_unit,
L.precision,
G.trx_number,
G.trx_date,
L.unit_price,
L.line_amt,
L.trx_line_quantity,
L.tax_base_modifier_rate,
L.ref_doc_application_id,
L.ref_doc_entity_code,
L.ref_doc_event_class_code,
L.ref_doc_trx_id,
L.ref_doc_line_id,
L.ref_doc_line_quantity,
L.other_doc_line_amt,
L.other_doc_line_tax_amt,
L.other_doc_line_taxable_amt,
0,
0, -- L.unrounded_tax_amt,
L.related_doc_application_id,
L.related_doc_entity_code,
L.related_doc_event_class_code,
L.related_doc_trx_id,
L.related_doc_number,
L.related_doc_date,
L.applied_from_application_id,
L.applied_from_event_class_code,
L.applied_from_entity_code,
L.applied_from_trx_id,
L.applied_from_line_id,
L.applied_from_trx_level_type,
L.applied_from_trx_number,
L.adjusted_doc_application_id,
L.adjusted_doc_entity_code,
L.adjusted_doc_event_class_code,
L.adjusted_doc_trx_id,
L.adjusted_doc_line_id,
L.adjusted_doc_trx_level_type,
L.adjusted_doc_number,
L.adjusted_doc_date,
L.applied_to_application_id,
L.applied_to_event_class_code,
L.applied_to_entity_code,
L.applied_to_trx_id,
L.applied_to_line_id,
L.applied_to_trx_number,
L.summary_tax_line_id,
L.offset_link_to_tax_line_id,
L.offset_flag,
DECODE(L.historical_flag,'Y','Y',L.process_for_recovery_flag),
L.tax_jurisdiction_id,
L.tax_jurisdiction_code,
L.place_of_supply,
L.place_of_supply_type_code,
L.place_of_supply_result_id,
L.tax_date_rule_id,
L.tax_date,
L.tax_determine_date,
L.tax_point_date,
L.trx_line_date,
L.tax_type_code,
L.tax_code,
L.tax_registration_id,
L.tax_registration_number,
L.registration_party_type,
L.rounding_level_code,
L.rounding_rule_code,
L.rounding_lvl_party_tax_prof_id,
L.rounding_lvl_party_type,
L.compounding_tax_flag,
L.orig_tax_status_id,
L.orig_tax_status_code,
L.orig_tax_rate_id,
L.orig_tax_rate_code,
L.orig_tax_rate,
L.orig_tax_jurisdiction_id,
L.orig_tax_jurisdiction_code,
L.orig_tax_amt_included_flag,
L.orig_self_assessed_flag,
L.tax_currency_code,
0, -- L.tax_amt,
0, -- L.tax_amt_tax_curr,
0, -- L.tax_amt_funcl_curr,
0, -- L.taxable_amt
0, -- L.taxable_amt_tax_curr,
0, -- L.taxable_amt_funcl_curr,
DECODE(L.orig_taxable_amt,NULL,L.taxable_amt,L.orig_taxable_amt),
--orig_taxable_amt
DECODE(L.orig_taxable_amt_tax_curr,NULL,L.taxable_amt_tax_curr,L.orig_taxable_amt_tax_curr),
-- orig_taxable_amt_tax_curr
0 , -- L.cal_tax_amt,
0 , -- L.cal_tax_amt_tax_curr,
0 , -- L.cal_tax_amt_funcl_curr,
DECODE(L.orig_tax_amt,NULL,L.tax_amt,L.orig_tax_amt),
--L.orig_tax_amt
DECODE(L.orig_tax_amt_tax_curr,NULL, L.tax_amt_tax_curr,
L.orig_tax_amt_tax_curr), -- L.orig_tax_amt_tax_curr
0, --L.rec_tax_amt,
0, --L.rec_tax_amt_tax_curr,
0, --L.rec_tax_amt_funcl_curr,
0, -- L.nrec_tax_amt,
0, --L.nrec_tax_amt_tax_curr,
0, -- L.nrec_tax_amt_funcl_curr,
L.tax_exemption_id,
L.tax_rate_before_exemption,
L.tax_rate_name_before_exemption,
L.exempt_rate_modifier,
L.exempt_certificate_number,
L.exempt_reason,
L.exempt_reason_code,
L.tax_exception_id,
L.tax_rate_before_exception,
L.tax_rate_name_before_exception,
L.exception_rate,
L.tax_apportionment_flag,
L.historical_flag,
L.taxable_basis_formula,
L.tax_calculation_formula,
L.cancel_flag,
L.purge_flag,
L.delete_flag,
L.tax_amt_included_flag,
L.self_assessed_flag,
L.overridden_flag,
L.manually_entered_flag,
L.freeze_until_overridden_flag,
L.copied_from_other_doc_flag,
L.recalc_required_flag,
L.settlement_flag,
L.item_dist_changed_flag,
L.associated_child_frozen_flag,
L.tax_only_line_flag,
L.compounding_dep_tax_flag,
L.last_manual_entry,
L.tax_provider_id,
L.record_type_code,
L.reporting_period_id,
L.legal_message_appl_2,
L.legal_message_status,
L.legal_message_rate,
L.legal_message_basis,
L.legal_message_calc,
L.legal_message_threshold,
L.legal_message_pos,
L.legal_message_trn,
L.legal_message_exmpt,
L.legal_message_excpt,
L.tax_regime_template_id,
L.tax_applicability_result_id,
L.direct_rate_result_id,
L.status_result_id,
L.rate_result_id,
L.basis_result_id,
L.thresh_result_id,
L.calc_result_id,
L.tax_reg_num_det_result_id,
L.eval_exmpt_result_id,
L.eval_excpt_result_id,
L.enforce_from_natural_acct_flag,
NULL, --L.tax_hold_code,
NULL, -- L.tax_hold_released_code,
NULL, --L.prd_total_tax_amt,
NULL, --L.prd_total_tax_amt_tax_curr,
NULL, --L.prd_total_tax_amt_funcl_curr,
L.internal_org_location_id,
L.attribute_category,
L.attribute1,
L.attribute2,
L.attribute3,
L.attribute4,
L.attribute5,
L.attribute6,
L.attribute7,
L.attribute8,
L.attribute9,
L.attribute10,
L.attribute11,
L.attribute12,
L.attribute13,
L.attribute14,
L.attribute15,
L.global_attribute_category,
L.global_attribute1,
L.global_attribute2,
L.global_attribute3,
L.global_attribute4,
L.global_attribute5,
L.global_attribute6,
L.global_attribute7,
L.global_attribute8,
L.global_attribute9,
L.global_attribute10,
L.global_attribute11,
L.global_attribute12,
L.global_attribute13,
L.global_attribute14,
L.global_attribute15,
L.numeric1,
L.numeric2,
L.numeric3,
L.numeric4,
L.numeric5,
L.numeric6,
L.numeric7,
L.numeric8,
L.numeric9,
L.numeric10,
L.char1,
L.char2,
L.char3,
L.char4,
L.char5,
L.char6,
L.char7,
L.char8,
L.char9,
L.char10,
L.date1,
L.date2,
L.date3,
L.date4,
L.date5,
L.date6,
L.date7,
L.date8,
L.date9,
L.date10,
L.tax_rate_type,
L.created_by,
L.creation_date,
L.last_updated_by,
L.last_update_date,
L.last_update_login,
L.line_assessable_value,
L.legal_justification_text1,
L.legal_justification_text2,
L.legal_justification_text3,
L.reporting_currency_code,
NULL, --L.trx_line_index,
L.offset_tax_rate_code,
NULL, -- L.proration_code,
NULL, --L.other_doc_source,
L.reporting_only_flag,
L.ctrl_total_line_tx_amt,
L.sync_with_prvdr_flag,
L.interface_entity_code,
L.interface_tax_line_id,
L.taxing_juris_geography_id,
L.adjusted_doc_tax_line_id,
1,
L.legal_reporting_status,
L.account_source_tax_rate_id
FROM ZX_LINES L,
zx_lines_det_factors G
WHERE G.application_id = p_event_class_rec.application_id AND
G.entity_code = p_event_class_rec.entity_code AND
G.event_class_code = p_event_class_rec.event_class_code AND
-- G.trx_id = p_event_class_rec.trx_id AND
G.event_id = p_event_class_rec.event_id AND
L.trx_id = G.trx_id AND
L.trx_line_id = G.trx_line_id AND
L.trx_level_type = G.trx_level_type AND
L.event_class_code = G.event_class_code
AND L.entity_code = G.entity_code
AND L.application_id = G.application_id
-- AND L.subscriber_id = G.subscriber_id
AND G.line_level_action NOT IN ('SYNCHRONIZE', 'CANCEL', 'NO_CHANGE','DISCARD')
AND L.Cancel_Flag = 'Y'
-- AND (L.tax_provider_id IS NOT NULL OR L.Cancel_Flag = 'Y')
AND NOT EXISTS
(SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
1
FROM zx_detail_tax_lines_gt gt
WHERE gt.application_id = L.application_id
AND gt.entity_code = L.entity_code
AND gt.event_class_code = L.event_class_code
AND gt.trx_id = L.trx_id
AND gt.trx_line_id = L.trx_line_id
AND gt.trx_level_type = L.trx_level_type
AND gt.tax_regime_code = L.tax_regime_code
AND gt.tax = L.tax
AND NVL(gt.tax_apportionment_line_number, -999999) =
NVL(L.tax_apportionment_line_number, -999999)
)
);
'Number of Rows Inserted: ' || SQL%ROWCOUNT);
INSERT INTO zx_detail_tax_lines_gt
( tax_line_id,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_line_id,
trx_level_type,
trx_line_number,
doc_event_status,
tax_event_class_code,
tax_event_type_code,
tax_line_number,
content_owner_id,
tax_regime_id,
tax_regime_code,
tax_id,
tax,
tax_status_id,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
tax_apportionment_line_number,
trx_id_level2,
trx_id_level3,
trx_id_level4,
trx_id_level5,
trx_id_level6,
trx_user_key_level1,
trx_user_key_level2,
trx_user_key_level3,
trx_user_key_level4,
trx_user_key_level5,
trx_user_key_level6,
mrc_tax_line_flag,
ledger_id,
establishment_id,
legal_entity_id,
legal_entity_tax_reg_number,
hq_estb_reg_number,
hq_estb_party_tax_prof_id,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
tax_currency_conversion_date,
tax_currency_conversion_type,
tax_currency_conversion_rate,
trx_currency_code,
minimum_accountable_unit,
precision,
trx_number,
trx_date,
unit_price,
line_amt,
trx_line_quantity,
tax_base_modifier_rate,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_line_quantity,
other_doc_line_amt,
other_doc_line_tax_amt,
other_doc_line_taxable_amt,
unrounded_taxable_amt,
unrounded_tax_amt,
related_doc_application_id,
related_doc_entity_code,
related_doc_event_class_code,
related_doc_trx_id,
related_doc_number,
related_doc_date,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
applied_from_trx_level_type,
applied_from_trx_number,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
adjusted_doc_line_id,
adjusted_doc_trx_level_type,
adjusted_doc_number,
adjusted_doc_date,
applied_to_application_id,
applied_to_event_class_code,
applied_to_entity_code,
applied_to_trx_id,
applied_to_line_id,
applied_to_trx_number,
summary_tax_line_id,
offset_link_to_tax_line_id,
offset_flag,
process_for_recovery_flag,
tax_jurisdiction_id,
tax_jurisdiction_code,
place_of_supply,
place_of_supply_type_code,
place_of_supply_result_id,
tax_date_rule_id,
tax_date,
tax_determine_date,
tax_point_date,
trx_line_date,
tax_type_code,
tax_code,
tax_registration_id,
tax_registration_number,
registration_party_type,
rounding_level_code,
rounding_rule_code,
rounding_lvl_party_tax_prof_id,
rounding_lvl_party_type,
compounding_tax_flag,
orig_tax_status_id,
orig_tax_status_code,
orig_tax_rate_id,
orig_tax_rate_code,
orig_tax_rate,
orig_tax_jurisdiction_id,
orig_tax_jurisdiction_code,
orig_tax_amt_included_flag,
orig_self_assessed_flag,
tax_currency_code,
tax_amt,
tax_amt_tax_curr,
tax_amt_funcl_curr,
taxable_amt,
taxable_amt_tax_curr,
taxable_amt_funcl_curr,
orig_taxable_amt,
orig_taxable_amt_tax_curr,
cal_tax_amt,
cal_tax_amt_tax_curr,
cal_tax_amt_funcl_curr,
orig_tax_amt,
orig_tax_amt_tax_curr,
rec_tax_amt,
rec_tax_amt_tax_curr,
rec_tax_amt_funcl_curr,
nrec_tax_amt,
nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
tax_apportionment_flag,
historical_flag,
taxable_basis_formula,
tax_calculation_formula,
cancel_flag,
purge_flag,
delete_flag,
tax_amt_included_flag,
self_assessed_flag,
overridden_flag,
manually_entered_flag,
freeze_until_overridden_flag,
copied_from_other_doc_flag,
recalc_required_flag,
settlement_flag,
item_dist_changed_flag,
associated_child_frozen_flag,
tax_only_line_flag,
compounding_dep_tax_flag,
last_manual_entry,
tax_provider_id,
record_type_code,
reporting_period_id,
legal_message_appl_2,
legal_message_status,
legal_message_rate,
legal_message_basis,
legal_message_calc,
legal_message_threshold,
legal_message_pos,
legal_message_trn,
legal_message_exmpt,
legal_message_excpt,
tax_regime_template_id,
tax_applicability_result_id,
direct_rate_result_id,
status_result_id,
rate_result_id,
basis_result_id,
thresh_result_id,
calc_result_id,
tax_reg_num_det_result_id,
eval_exmpt_result_id,
eval_excpt_result_id,
enforce_from_natural_acct_flag,
tax_hold_code,
tax_hold_released_code,
prd_total_tax_amt,
prd_total_tax_amt_tax_curr,
prd_total_tax_amt_funcl_curr,
internal_org_location_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
numeric1,
numeric2,
numeric3,
numeric4,
numeric5,
numeric6,
numeric7,
numeric8,
numeric9,
numeric10,
char1,
char2,
char3,
char4,
char5,
char6,
char7,
char8,
char9,
char10,
date1,
date2,
date3,
date4,
date5,
date6,
date7,
date8,
date9,
date10,
tax_rate_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
line_assessable_value,
legal_justification_text1,
legal_justification_text2,
legal_justification_text3,
reporting_currency_code,
trx_line_index,
offset_tax_rate_code,
proration_code,
other_doc_source,
reporting_only_flag,
ctrl_total_line_tx_amt,
sync_with_prvdr_flag,
interface_entity_code,
interface_tax_line_id,
taxing_juris_geography_id,
adjusted_doc_tax_line_id,
object_version_number,
legal_reporting_status,
account_source_tax_rate_id
)
(SELECT /*+ leading(G) */
L.tax_line_id,
L.internal_organization_id,
L.application_id,
L.entity_code,
L.event_class_code,
L.event_type_code,
L.trx_id,
L.trx_line_id,
L.trx_level_type,
L.trx_line_number,
L.doc_event_status,
L.tax_event_class_code,
L.tax_event_type_code,
L.tax_line_number,
L.content_owner_id,
L.tax_regime_id,
L.tax_regime_code,
L.tax_id,
L.tax,
L.tax_status_id,
L.tax_status_code,
L.tax_rate_id,
L.tax_rate_code,
L.tax_rate,
L.tax_apportionment_line_number,
L.trx_id_level2,
L.trx_id_level3,
L.trx_id_level4,
L.trx_id_level5,
L.trx_id_level6,
L.trx_user_key_level1,
L.trx_user_key_level2,
L.trx_user_key_level3,
L.trx_user_key_level4,
L.trx_user_key_level5,
L.trx_user_key_level6,
L.mrc_tax_line_flag,
L.ledger_id,
L.establishment_id,
L.legal_entity_id,
L.legal_entity_tax_reg_number,
L.hq_estb_reg_number,
L.hq_estb_party_tax_prof_id,
L.currency_conversion_date,
L.currency_conversion_type,
L.currency_conversion_rate,
L.tax_currency_conversion_date,
L.tax_currency_conversion_type,
L.tax_currency_conversion_rate,
L.trx_currency_code,
L.minimum_accountable_unit,
L.precision,
G.trx_number,
L.trx_date,
L.unit_price,
L.line_amt,
L.trx_line_quantity,
L.tax_base_modifier_rate,
L.ref_doc_application_id,
L.ref_doc_entity_code,
L.ref_doc_event_class_code,
L.ref_doc_trx_id,
L.ref_doc_line_id,
L.ref_doc_line_quantity,
L.other_doc_line_amt,
L.other_doc_line_tax_amt,
L.other_doc_line_taxable_amt,
0, -- L.unrounded_taxable_amt,
0, -- L.unrounded_tax_amt,
L.related_doc_application_id,
L.related_doc_entity_code,
L.related_doc_event_class_code,
L.related_doc_trx_id,
L.related_doc_number,
L.related_doc_date,
L.applied_from_application_id,
L.applied_from_event_class_code,
L.applied_from_entity_code,
L.applied_from_trx_id,
L.applied_from_line_id,
L.applied_from_trx_level_type,
L.applied_from_trx_number,
L.adjusted_doc_application_id,
L.adjusted_doc_entity_code,
L.adjusted_doc_event_class_code,
L.adjusted_doc_trx_id,
L.adjusted_doc_line_id,
L.adjusted_doc_trx_level_type,
L.adjusted_doc_number,
L.adjusted_doc_date,
L.applied_to_application_id,
L.applied_to_event_class_code,
L.applied_to_entity_code,
L.applied_to_trx_id,
L.applied_to_line_id,
L.applied_to_trx_number,
L.summary_tax_line_id,
L.offset_link_to_tax_line_id,
L.offset_flag,
DECODE(L.Reporting_Only_Flag, 'N', 'Y', 'N'), -- L.Process_For_Recovery_Flag,
L.tax_jurisdiction_id,
L.tax_jurisdiction_code,
L.place_of_supply,
L.place_of_supply_type_code,
L.place_of_supply_result_id,
L.tax_date_rule_id,
L.tax_date,
L.tax_determine_date,
L.tax_point_date,
L.trx_line_date,
L.tax_type_code,
L.tax_code,
L.tax_registration_id,
L.tax_registration_number,
L.registration_party_type,
L.rounding_level_code,
L.rounding_rule_code,
L.rounding_lvl_party_tax_prof_id,
L.rounding_lvl_party_type,
L.compounding_tax_flag,
L.orig_tax_status_id,
L.orig_tax_status_code,
L.orig_tax_rate_id,
L.orig_tax_rate_code,
L.orig_tax_rate,
L.orig_tax_jurisdiction_id,
L.orig_tax_jurisdiction_code,
L.orig_tax_amt_included_flag,
L.orig_self_assessed_flag,
L.tax_currency_code,
0, -- L.tax_amt,
0, -- L.tax_amt_tax_curr,
0, -- L.tax_amt_funcl_curr,
0, -- L.taxable_amt,
0, -- L.taxable_amt_tax_curr,
0, -- L.taxable_amt_funcl_curr,
DECODE(L.orig_taxable_amt, NULL,L.taxable_amt,
L.orig_taxable_amt), -- L.orig_taxable_amt
DECODE(L.orig_taxable_amt_tax_curr, NULL, L.taxable_amt_funcl_curr,
L.orig_taxable_amt_tax_curr), -- L.orig_taxable_amt_tax_curr
0, -- L.cal_tax_amt,
0, -- L.cal_tax_amt_tax_curr,
0, -- L.cal_tax_amt_funcl_curr,
DECODE(L.orig_tax_amt, NULL, L.tax_amt,
L.orig_tax_amt), -- L.orig_tax_amt
DECODE(L.orig_tax_amt_tax_curr, NULL, L.tax_amt,
L.orig_tax_amt_tax_curr), -- L.orig_tax_amt_tax_curr
0, -- L.rec_tax_amt,
0, -- L.rec_tax_amt_tax_curr,
0, -- L.rec_tax_amt_funcl_curr,
0, -- L.nrec_tax_amt,
0, -- L.nrec_tax_amt_tax_curr,
0, -- L.nrec_tax_amt_funcl_curr,
L.tax_exemption_id,
L.tax_rate_before_exemption,
L.tax_rate_name_before_exemption,
L.exempt_rate_modifier,
L.exempt_certificate_number,
L.exempt_reason,
L.exempt_reason_code,
L.tax_exception_id,
L.tax_rate_before_exception,
L.tax_rate_name_before_exception,
L.exception_rate,
L.tax_apportionment_flag,
L.historical_flag,
L.taxable_basis_formula,
L.tax_calculation_formula,
'Y', -- L.cancel_flag
L.purge_flag,
L.delete_flag,
L.tax_amt_included_flag,
L.self_assessed_flag,
L.overridden_flag,
L.manually_entered_flag,
L.freeze_until_overridden_flag,
L.copied_from_other_doc_flag,
L.recalc_required_flag,
L.settlement_flag,
L.item_dist_changed_flag,
L.associated_child_frozen_flag,
L.tax_only_line_flag,
L.compounding_dep_tax_flag,
L.last_manual_entry,
L.tax_provider_id,
L.record_type_code,
L.reporting_period_id,
L.legal_message_appl_2,
L.legal_message_status,
L.legal_message_rate,
L.legal_message_basis,
L.legal_message_calc,
L.legal_message_threshold,
L.legal_message_pos,
L.legal_message_trn,
L.legal_message_exmpt,
L.legal_message_excpt,
L.tax_regime_template_id,
L.tax_applicability_result_id,
L.direct_rate_result_id,
L.status_result_id,
L.rate_result_id,
L.basis_result_id,
L.thresh_result_id,
L.calc_result_id,
L.tax_reg_num_det_result_id,
L.eval_exmpt_result_id,
L.eval_excpt_result_id,
L.enforce_from_natural_acct_flag,
NULL, -- L.tax_hold_code,
NULL, -- L.tax_hold_released_code,
NULL, -- L.prd_total_tax_amt,
NULL, -- L.prd_total_tax_amt_tax_curr,
NULL, -- L.prd_total_tax_amt_funcl_curr,
L.internal_org_location_id,
L.attribute_category,
L.attribute1,
L.attribute2,
L.attribute3,
L.attribute4,
L.attribute5,
L.attribute6,
L.attribute7,
L.attribute8,
L.attribute9,
L.attribute10,
L.attribute11,
L.attribute12,
L.attribute13,
L.attribute14,
L.attribute15,
L.global_attribute_category,
L.global_attribute1,
L.global_attribute2,
L.global_attribute3,
L.global_attribute4,
L.global_attribute5,
L.global_attribute6,
L.global_attribute7,
L.global_attribute8,
L.global_attribute9,
L.global_attribute10,
L.global_attribute11,
L.global_attribute12,
L.global_attribute13,
L.global_attribute14,
L.global_attribute15,
L.numeric1,
L.numeric2,
L.numeric3,
L.numeric4,
L.numeric5,
L.numeric6,
L.numeric7,
L.numeric8,
L.numeric9,
L.numeric10,
L.char1,
L.char2,
L.char3,
L.char4,
L.char5,
L.char6,
L.char7,
L.char8,
L.char9,
L.char10,
L.date1,
L.date2,
L.date3,
L.date4,
L.date5,
L.date6,
L.date7,
L.date8,
L.date9,
L.date10,
L.tax_rate_type,
L.created_by,
L.creation_date,
L.last_updated_by,
L.last_update_date,
L.last_update_login,
L.line_assessable_value,
L.legal_justification_text1,
L.legal_justification_text2,
L.legal_justification_text3,
L.reporting_currency_code,
NULL, -- L.trx_line_index
NULL, -- L.offset_tax_rate_code
NULL, -- L.proration_code
NULL, -- L.other_doc_source
L.reporting_only_flag,
L.ctrl_total_line_tx_amt,
DECODE(L.tax_provider_id, NULL, L.sync_with_prvdr_flag, 'Y'),
L.interface_entity_code,
L.interface_tax_line_id,
L.taxing_juris_geography_id,
L.adjusted_doc_tax_line_id,
1,
legal_reporting_status,
L.account_source_tax_rate_id
FROM zx_lines L,
zx_lines_det_factors G
WHERE G.application_id = p_event_class_rec.application_id
AND G.entity_code = p_event_class_rec.entity_code
AND G.event_class_code = p_event_class_rec.event_class_code
AND G.trx_id = p_event_class_rec.trx_id
AND G.event_id = p_event_class_rec.event_id
AND L.trx_id = G.trx_id
AND L.trx_line_id = G.trx_line_id
AND L.trx_level_type = G.trx_level_type
AND L.event_class_code = G.event_class_code
AND L.entity_code = G.entity_code
AND L.application_id = G.application_id
AND L.associated_child_frozen_flag = 'Y'
AND L.cancel_flag = 'Y'
AND L.tax_apportionment_line_number < 0
AND NOT EXISTS ( SELECT 1
FROM zx_detail_tax_lines_gt T
WHERE T.tax_line_id = L.tax_line_id
-- bug 8470599 these columns are redundant and are doing index range scan on ZX_DETAIL_TAX_LINES_GT_U1
-- AND T.trx_id = L.trx_id
-- AND T.trx_line_id = L.trx_line_id
-- AND T.event_class_code = L.event_class_code
-- AND T.entity_code = L.entity_code
-- AND T.application_id = L.application_id
-- AND T.trx_level_type = L.trx_level_type
)
);
UPDATE zx_detail_tax_lines_gt zlgt
SET (tax_line_id, associated_child_frozen_flag, summary_tax_line_id,offset_link_to_tax_line_id) =
( SELECT /*+ INDEX(zl ZX_LINES_U1) */
tax_line_id, associated_child_frozen_flag, summary_tax_line_id,offset_link_to_tax_line_id
FROM zx_lines zl
WHERE zl.application_id = zlgt.application_id
AND zl.entity_code = zlgt.entity_code
AND zl.event_class_code = zlgt.event_class_code
AND zl.trx_id = zlgt.trx_id
and zl.trx_line_id = zlgt.trx_line_id
AND zl.trx_level_type = zlgt.trx_level_type
AND zl.internal_organization_id = zlgt.internal_organization_id
AND NVL(zl.applied_from_trx_level_type, 'x') = NVL(zlgt.applied_from_trx_level_type, 'x')
AND NVL(zl.adjusted_doc_trx_level_type, 'x') = NVL(zlgt.adjusted_doc_trx_level_type, 'x')
AND NVL(zl.applied_from_application_id, 0) = NVL(zlgt.applied_from_application_id, 0)
AND NVL(zl.applied_from_event_class_code, 'x') = NVL(zlgt.applied_from_event_class_code, 'x')
AND NVL(zl.applied_from_entity_code, 'x') = NVL(zlgt.applied_from_entity_code, 'x')
AND NVL(zl.applied_from_trx_id, 0) = NVL(zlgt.applied_from_trx_id, 0)
AND NVL(zl.applied_from_line_id, 0) = NVL(zlgt.applied_from_line_id, 0)
AND NVL(zl.adjusted_doc_application_id, 0) = NVL(zlgt.adjusted_doc_application_id, 0)
AND NVL(zl.adjusted_doc_entity_code, 'x') = NVL(zlgt.adjusted_doc_entity_code, 'x')
AND NVL(zl.adjusted_doc_event_class_code, 'x') = NVL(zlgt.adjusted_doc_event_class_code, 'x')
AND NVL(zl.adjusted_doc_trx_id, 0) = NVL(zlgt.adjusted_doc_trx_id, 0)
AND NVL(zl.tax_exemption_id, -999) = NVL(zlgt.tax_exemption_id, -999)
--AND NVL(zl.tax_rate_before_exemption, -999) = NVL(zlgt.tax_rate_before_exemption, -999)
--AND NVL(zl.tax_rate_name_before_exemption, 'x') = NVL(zlgt.tax_rate_name_before_exemption, 'x')
--AND NVL(zl.exempt_rate_modifier, -999) = NVL(zlgt.exempt_rate_modifier, -999)
AND NVL(zl.exempt_certificate_number, 'x') = NVL(zlgt.exempt_certificate_number, 'x')
--AND NVL(zl.exempt_reason, 'x') = NVL(zlgt.exempt_reason, 'x')
AND NVL(zl.exempt_reason_code, 'x') = NVL(zlgt.exempt_reason_code, 'x')
AND NVL(zl.tax_exception_id, -999) = NVL(zlgt.tax_exception_id, -999)
--AND NVL(zl.tax_rate_before_exception, -999) = NVL(zlgt.tax_rate_before_exception, -999)
--AND NVL(zl.tax_rate_name_before_exception, 'x') = NVL(zlgt.tax_rate_name_before_exception, 'x')
--AND NVL(zl.exception_rate, -999) = NVL(zlgt.exception_rate, -999)
AND NVL(zl.content_owner_id, 0) = NVL(zlgt.content_owner_id, 0)
AND zl.tax_regime_code = zlgt.tax_regime_code
AND zl.tax = zlgt.tax
AND NVL(zl.tax_status_code, 'x') = NVL(zlgt.tax_status_code, 'x')
AND NVL(zl.tax_rate_id, 0) = NVL(zlgt.tax_rate_id, 0)
AND NVL(zl.tax_rate_code, 'x') = NVL(zlgt.tax_rate_code, 'x')
AND NVL(zl.tax_rate, -99) = NVL(zlgt.tax_rate, -99)
AND NVL(zl.tax_jurisdiction_code, 'x') = NVL(zlgt.tax_jurisdiction_code, 'x')
AND NVL(zl.ledger_id, 0) = NVL(zlgt.ledger_id, 0)
AND NVL(zl.legal_entity_id, 0) = NVL(zlgt.legal_entity_id, 0)
AND NVL(zl.establishment_id, 0) = NVL(zlgt.establishment_id, 0)
AND NVL(TRUNC(zl.currency_conversion_date), SYSDATE) = NVL(TRUNC(zlgt.currency_conversion_date), SYSDATE)
AND NVL(zl.currency_conversion_type,'x') = NVL(zlgt.currency_conversion_type,'x')
AND NVL(zl.currency_conversion_rate, 1) = NVL(zlgt.currency_conversion_rate, 1)
AND NVL(zl.taxable_basis_formula,'x') = NVL(zlgt.taxable_basis_formula,'x')
AND NVL(zl.tax_calculation_formula,'x') = NVL(zlgt.tax_calculation_formula,'x')
AND zl.tax_amt_included_flag = zlgt.tax_amt_included_flag
AND zl.compounding_tax_flag = zlgt.compounding_tax_flag
AND zl.self_assessed_flag = zlgt.self_assessed_flag
AND zl.reporting_only_flag = zlgt.reporting_only_flag
AND zl.copied_from_other_doc_flag = zlgt.copied_from_other_doc_flag
AND NVL(zl.record_type_code,'x') = NVL(zlgt.record_type_code,'x')
AND NVL(zl.tax_provider_id, 0) = NVL(zlgt.tax_provider_id, 0)
AND zl.overridden_flag = zlgt.overridden_flag
AND zl.manually_entered_flag = zlgt.manually_entered_flag
AND zl.tax_only_line_flag = zlgt.tax_only_line_flag
AND zl.mrc_tax_line_flag = zlgt.mrc_tax_line_flag
AND zl.historical_flag = zlgt.historical_flag
AND NVL(zl.tax_apportionment_line_number, 1) = NVL(zlgt.tax_apportionment_line_number, 1)
AND zl.tax_line_id <> zlgt.tax_line_id
AND zl.cancel_flag <> 'Y'
AND ROWNUM = 1
)
WHERE NVL(associated_child_frozen_flag, 'N') ='N'
-- Bug 8348107
AND (summary_tax_line_id IS NULL OR nvl(historical_flag,'N') = 'Y')
AND NVL(tax_only_line_flag, 'N') = 'N'
AND EXISTS (SELECT /*+ INDEX(zl ZX_LINES_U1) */
tax_line_id, associated_child_frozen_flag
FROM zx_lines zl
WHERE zl.application_id = zlgt.application_id
AND zl.entity_code = zlgt.entity_code
AND zl.event_class_code = zlgt.event_class_code
AND zl.trx_id = zlgt.trx_id
and zl.trx_line_id = zlgt.trx_line_id
AND zl.trx_level_type = zlgt.trx_level_type
AND zl.internal_organization_id = zlgt.internal_organization_id
AND NVL(zl.applied_from_trx_level_type, 'x') = NVL(zlgt.applied_from_trx_level_type, 'x')
AND NVL(zl.adjusted_doc_trx_level_type, 'x') = NVL(zlgt.adjusted_doc_trx_level_type, 'x')
AND NVL(zl.applied_from_application_id, 0) = NVL(zlgt.applied_from_application_id, 0)
AND NVL(zl.applied_from_event_class_code, 'x') = NVL(zlgt.applied_from_event_class_code, 'x')
AND NVL(zl.applied_from_entity_code, 'x') = NVL(zlgt.applied_from_entity_code, 'x')
AND NVL(zl.applied_from_trx_id, 0) = NVL(zlgt.applied_from_trx_id, 0)
AND NVL(zl.applied_from_line_id, 0) = NVL(zlgt.applied_from_line_id, 0)
AND NVL(zl.adjusted_doc_application_id, 0) = NVL(zlgt.adjusted_doc_application_id, 0)
AND NVL(zl.adjusted_doc_entity_code, 'x') = NVL(zlgt.adjusted_doc_entity_code, 'x')
AND NVL(zl.adjusted_doc_event_class_code, 'x') = NVL(zlgt.adjusted_doc_event_class_code, 'x')
AND NVL(zl.adjusted_doc_trx_id, 0) = NVL(zlgt.adjusted_doc_trx_id, 0)
AND NVL(zl.tax_exemption_id, -999) = NVL(zlgt.tax_exemption_id, -999)
-- AND NVL(zl.tax_rate_before_exemption, -999) = NVL(zlgt.tax_rate_before_exemption, -999)
-- AND NVL(zl.tax_rate_name_before_exemption, 'x') = NVL(zlgt.tax_rate_name_before_exemption, 'x')
-- AND NVL(zl.exempt_rate_modifier, -999) = NVL(zlgt.exempt_rate_modifier, -999)
AND NVL(zl.exempt_certificate_number, 'x') = NVL(zlgt.exempt_certificate_number, 'x')
-- AND NVL(zl.exempt_reason, 'x') = NVL(zlgt.exempt_reason, 'x')
AND NVL(zl.exempt_reason_code, 'x') = NVL(zlgt.exempt_reason_code, 'x')
AND NVL(zl.tax_exception_id, -999) = NVL(zlgt.tax_exception_id, -999)
-- AND NVL(zl.tax_rate_before_exception, -999) = NVL(zlgt.tax_rate_before_exception, -999)
-- AND NVL(zl.tax_rate_name_before_exception, 'x') = NVL(zlgt.tax_rate_name_before_exception, 'x')
-- AND NVL(zl.exception_rate, -999) = NVL(zlgt.exception_rate, -999)
AND NVL(zl.content_owner_id, 0) = NVL(zlgt.content_owner_id, 0)
AND zl.tax_regime_code = zlgt.tax_regime_code
AND zl.tax = zlgt.tax
AND NVL(zl.tax_status_code, 'x') = NVL(zlgt.tax_status_code, 'x')
AND NVL(zl.tax_rate_id, 0) = NVL(zlgt.tax_rate_id, 0)
AND NVL(zl.tax_rate_code, 'x') = NVL(zlgt.tax_rate_code, 'x')
AND NVL(zl.tax_rate, -99) = NVL(zlgt.tax_rate, -99)
AND NVL(zl.tax_jurisdiction_code, 'x') = NVL(zlgt.tax_jurisdiction_code, 'x')
AND NVL(zl.ledger_id, 0) = NVL(zlgt.ledger_id, 0)
AND NVL(zl.legal_entity_id, 0) = NVL(zlgt.legal_entity_id, 0)
AND NVL(zl.establishment_id, 0) = NVL(zlgt.establishment_id, 0)
AND NVL(TRUNC(zl.currency_conversion_date), SYSDATE) = NVL(TRUNC(zlgt.currency_conversion_date), SYSDATE)
AND NVL(zl.currency_conversion_type,'x') = NVL(zlgt.currency_conversion_type,'x')
AND NVL(zl.currency_conversion_rate, 1) = NVL(zlgt.currency_conversion_rate, 1)
AND NVL(zl.taxable_basis_formula,'x') = NVL(zlgt.taxable_basis_formula,'x')
AND NVL(zl.tax_calculation_formula,'x') = NVL(zlgt.tax_calculation_formula,'x')
AND zl.tax_amt_included_flag = zlgt.tax_amt_included_flag
AND zl.compounding_tax_flag = zlgt.compounding_tax_flag
AND zl.self_assessed_flag = zlgt.self_assessed_flag
AND zl.reporting_only_flag = zlgt.reporting_only_flag
AND zl.copied_from_other_doc_flag = zlgt.copied_from_other_doc_flag
AND NVL(zl.record_type_code,'x') = NVL(zlgt.record_type_code,'x')
AND NVL(zl.tax_provider_id, 0) = NVL(zlgt.tax_provider_id, 0)
AND zl.overridden_flag = zlgt.overridden_flag
AND zl.manually_entered_flag = zlgt.manually_entered_flag
AND zl.tax_only_line_flag = zlgt.tax_only_line_flag
AND zl.mrc_tax_line_flag = zlgt.mrc_tax_line_flag
AND zl.historical_flag = zlgt.historical_flag
AND NVL(zl.tax_apportionment_line_number, 1) = NVL(zlgt.tax_apportionment_line_number, 1)
AND zl.tax_line_id <> zlgt.tax_line_id
AND zl.cancel_flag <> 'Y'
);
'Number of Rows Updated: ' || SQL%ROWCOUNT);
INSERT INTO zx_detail_tax_lines_gt
( tax_line_id,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_line_id,
trx_level_type,
trx_line_number,
doc_event_status,
tax_event_class_code,
tax_event_type_code,
tax_line_number,
content_owner_id,
tax_regime_id,
tax_regime_code,
tax_id,
tax,
tax_status_id,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
tax_apportionment_line_number,
trx_id_level2,
trx_id_level3,
trx_id_level4,
trx_id_level5,
trx_id_level6,
trx_user_key_level1,
trx_user_key_level2,
trx_user_key_level3,
trx_user_key_level4,
trx_user_key_level5,
trx_user_key_level6,
mrc_tax_line_flag,
ledger_id,
establishment_id,
legal_entity_id,
legal_entity_tax_reg_number,
hq_estb_reg_number,
hq_estb_party_tax_prof_id,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
tax_currency_conversion_date,
tax_currency_conversion_type,
tax_currency_conversion_rate,
trx_currency_code,
minimum_accountable_unit,
precision,
trx_number,
trx_date,
unit_price,
line_amt,
trx_line_quantity,
tax_base_modifier_rate,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_line_quantity,
other_doc_line_amt,
other_doc_line_tax_amt,
other_doc_line_taxable_amt,
unrounded_taxable_amt,
unrounded_tax_amt,
related_doc_application_id,
related_doc_entity_code,
related_doc_event_class_code,
related_doc_trx_id,
related_doc_number,
related_doc_date,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
applied_from_trx_level_type,
applied_from_trx_number,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
adjusted_doc_line_id,
adjusted_doc_trx_level_type,
adjusted_doc_number,
adjusted_doc_date,
applied_to_application_id,
applied_to_event_class_code,
applied_to_entity_code,
applied_to_trx_id,
applied_to_line_id,
applied_to_trx_number,
summary_tax_line_id,
offset_link_to_tax_line_id,
offset_flag,
process_for_recovery_flag,
tax_jurisdiction_id,
tax_jurisdiction_code,
place_of_supply,
place_of_supply_type_code,
place_of_supply_result_id,
tax_date_rule_id,
tax_date,
tax_determine_date,
tax_point_date,
trx_line_date,
tax_type_code,
tax_code,
tax_registration_id,
tax_registration_number,
registration_party_type,
rounding_level_code,
rounding_rule_code,
rounding_lvl_party_tax_prof_id,
rounding_lvl_party_type,
compounding_tax_flag,
orig_tax_status_id,
orig_tax_status_code,
orig_tax_rate_id,
orig_tax_rate_code,
orig_tax_rate,
orig_tax_jurisdiction_id,
orig_tax_jurisdiction_code,
orig_tax_amt_included_flag,
orig_self_assessed_flag,
tax_currency_code,
tax_amt,
tax_amt_tax_curr,
tax_amt_funcl_curr,
taxable_amt,
taxable_amt_tax_curr,
taxable_amt_funcl_curr,
orig_taxable_amt,
orig_taxable_amt_tax_curr,
cal_tax_amt,
cal_tax_amt_tax_curr,
cal_tax_amt_funcl_curr,
orig_tax_amt,
orig_tax_amt_tax_curr,
rec_tax_amt,
rec_tax_amt_tax_curr,
rec_tax_amt_funcl_curr,
nrec_tax_amt,
nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
tax_apportionment_flag,
historical_flag,
taxable_basis_formula,
tax_calculation_formula,
cancel_flag,
purge_flag,
delete_flag,
tax_amt_included_flag,
self_assessed_flag,
overridden_flag,
manually_entered_flag,
freeze_until_overridden_flag,
copied_from_other_doc_flag,
recalc_required_flag,
settlement_flag,
item_dist_changed_flag,
associated_child_frozen_flag,
tax_only_line_flag,
compounding_dep_tax_flag,
last_manual_entry,
tax_provider_id,
record_type_code,
reporting_period_id,
legal_message_appl_2,
legal_message_status,
legal_message_rate,
legal_message_basis,
legal_message_calc,
legal_message_threshold,
legal_message_pos,
legal_message_trn,
legal_message_exmpt,
legal_message_excpt,
tax_regime_template_id,
tax_applicability_result_id,
direct_rate_result_id,
status_result_id,
rate_result_id,
basis_result_id,
thresh_result_id,
calc_result_id,
tax_reg_num_det_result_id,
eval_exmpt_result_id,
eval_excpt_result_id,
enforce_from_natural_acct_flag,
tax_hold_code,
tax_hold_released_code,
prd_total_tax_amt,
prd_total_tax_amt_tax_curr,
prd_total_tax_amt_funcl_curr,
internal_org_location_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
numeric1,
numeric2,
numeric3,
numeric4,
numeric5,
numeric6,
numeric7,
numeric8,
numeric9,
numeric10,
char1,
char2,
char3,
char4,
char5,
char6,
char7,
char8,
char9,
char10,
date1,
date2,
date3,
date4,
date5,
date6,
date7,
date8,
date9,
date10,
tax_rate_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
line_assessable_value,
legal_justification_text1,
legal_justification_text2,
legal_justification_text3,
reporting_currency_code,
trx_line_index,
offset_tax_rate_code,
proration_code,
other_doc_source,
reporting_only_flag,
ctrl_total_line_tx_amt,
sync_with_prvdr_flag,
interface_entity_code,
interface_tax_line_id,
taxing_juris_geography_id,
adjusted_doc_tax_line_id,
object_version_number,
legal_reporting_status,
account_source_tax_rate_id
)
(SELECT /*+ leading(G) index(l ZX_LINES_u1 ) */
L.tax_line_id,
L.internal_organization_id,
L.application_id,
L.entity_code,
L.event_class_code,
L.event_type_code,
L.trx_id,
L.trx_line_id,
L.trx_level_type,
L.trx_line_number,
L.doc_event_status,
L.tax_event_class_code,
L.tax_event_type_code,
L.tax_line_number,
L.content_owner_id,
L.tax_regime_id,
L.tax_regime_code,
L.tax_id,
L.tax,
L.tax_status_id,
L.tax_status_code,
L.tax_rate_id,
L.tax_rate_code,
L.tax_rate,
-- bug 6906427
NVL(-((SELECT max(ABS(tax_apportionment_line_number))
FROM zx_detail_tax_lines_gt gt1
WHERE gt1.application_id = L.application_id
AND gt1.entity_code = L.entity_code
AND gt1.event_class_code = L.event_class_code
AND gt1.trx_id = L.trx_id
AND gt1.trx_line_id = L.trx_line_id
AND gt1.trx_level_type = L.trx_level_type
AND gt1.tax_regime_code = L.tax_regime_code
AND gt1.tax = L.tax
-- ) + 1 -- Commented as a fix for Bug#7340317
) + L.tax_apportionment_line_number -- Added as a fix for Bug#7340317
),
-L.tax_apportionment_line_number
),
L.trx_id_level2,
L.trx_id_level3,
L.trx_id_level4,
L.trx_id_level5,
L.trx_id_level6,
L.trx_user_key_level1,
L.trx_user_key_level2,
L.trx_user_key_level3,
L.trx_user_key_level4,
L.trx_user_key_level5,
L.trx_user_key_level6,
L.mrc_tax_line_flag,
L.ledger_id,
L.establishment_id,
L.legal_entity_id,
L.legal_entity_tax_reg_number,
L.hq_estb_reg_number,
L.hq_estb_party_tax_prof_id,
L.currency_conversion_date,
L.currency_conversion_type,
L.currency_conversion_rate,
L.tax_currency_conversion_date,
L.tax_currency_conversion_type,
L.tax_currency_conversion_rate,
L.trx_currency_code,
L.minimum_accountable_unit,
L.precision,
G.trx_number,
L.trx_date,
L.unit_price,
L.line_amt,
L.trx_line_quantity,
L.tax_base_modifier_rate,
L.ref_doc_application_id,
L.ref_doc_entity_code,
L.ref_doc_event_class_code,
L.ref_doc_trx_id,
L.ref_doc_line_id,
L.ref_doc_line_quantity,
L.other_doc_line_amt,
L.other_doc_line_tax_amt,
L.other_doc_line_taxable_amt,
0, -- L.unrounded_taxable_amt,
0, -- L.unrounded_tax_amt,
L.related_doc_application_id,
L.related_doc_entity_code,
L.related_doc_event_class_code,
L.related_doc_trx_id,
L.related_doc_number,
L.related_doc_date,
L.applied_from_application_id,
L.applied_from_event_class_code,
L.applied_from_entity_code,
L.applied_from_trx_id,
L.applied_from_line_id,
L.applied_from_trx_level_type,
L.applied_from_trx_number,
L.adjusted_doc_application_id,
L.adjusted_doc_entity_code,
L.adjusted_doc_event_class_code,
L.adjusted_doc_trx_id,
L.adjusted_doc_line_id,
L.adjusted_doc_trx_level_type,
L.adjusted_doc_number,
L.adjusted_doc_date,
L.applied_to_application_id,
L.applied_to_event_class_code,
L.applied_to_entity_code,
L.applied_to_trx_id,
L.applied_to_line_id,
L.applied_to_trx_number,
L.summary_tax_line_id,
L.offset_link_to_tax_line_id,
L.offset_flag,
DECODE(L.Reporting_Only_Flag, 'N', 'Y', 'N'), -- L.Process_For_Recovery_Flag,
L.tax_jurisdiction_id,
L.tax_jurisdiction_code,
L.place_of_supply,
L.place_of_supply_type_code,
L.place_of_supply_result_id,
L.tax_date_rule_id,
L.tax_date,
L.tax_determine_date,
L.tax_point_date,
L.trx_line_date,
L.tax_type_code,
L.tax_code,
L.tax_registration_id,
L.tax_registration_number,
L.registration_party_type,
L.rounding_level_code,
L.rounding_rule_code,
L.rounding_lvl_party_tax_prof_id,
L.rounding_lvl_party_type,
L.compounding_tax_flag,
L.orig_tax_status_id,
L.orig_tax_status_code,
L.orig_tax_rate_id,
L.orig_tax_rate_code,
L.orig_tax_rate,
L.orig_tax_jurisdiction_id,
L.orig_tax_jurisdiction_code,
L.orig_tax_amt_included_flag,
L.orig_self_assessed_flag,
L.tax_currency_code,
0, -- L.tax_amt,
0, -- L.tax_amt_tax_curr,
0, -- L.tax_amt_funcl_curr,
0, -- L.taxable_amt,
0, -- L.taxable_amt_tax_curr,
0, -- L.taxable_amt_funcl_curr,
DECODE(L.orig_taxable_amt, NULL,L.taxable_amt,
L.orig_taxable_amt), -- L.orig_taxable_amt
DECODE(L.orig_taxable_amt_tax_curr, NULL, L.taxable_amt_funcl_curr,
L.orig_taxable_amt_tax_curr), -- L.orig_taxable_amt_tax_curr
0, -- L.cal_tax_amt,
0, -- L.cal_tax_amt_tax_curr,
0, -- L.cal_tax_amt_funcl_curr,
DECODE(L.orig_tax_amt, NULL, L.tax_amt,
L.orig_tax_amt), -- L.orig_tax_amt
DECODE(L.orig_tax_amt_tax_curr, NULL, L.tax_amt,
L.orig_tax_amt_tax_curr), -- L.orig_tax_amt_tax_curr
0, -- L.rec_tax_amt,
0, -- L.rec_tax_amt_tax_curr,
0, -- L.rec_tax_amt_funcl_curr,
0, -- L.nrec_tax_amt,
0, -- L.nrec_tax_amt_tax_curr,
0, -- L.nrec_tax_amt_funcl_curr,
L.tax_exemption_id,
L.tax_rate_before_exemption,
L.tax_rate_name_before_exemption,
L.exempt_rate_modifier,
L.exempt_certificate_number,
L.exempt_reason,
L.exempt_reason_code,
L.tax_exception_id,
L.tax_rate_before_exception,
L.tax_rate_name_before_exception,
L.exception_rate,
L.tax_apportionment_flag,
L.historical_flag,
L.taxable_basis_formula,
L.tax_calculation_formula,
'Y', -- L.cancel_flag
L.purge_flag,
L.delete_flag,
L.tax_amt_included_flag,
L.self_assessed_flag,
L.overridden_flag,
L.manually_entered_flag,
L.freeze_until_overridden_flag,
L.copied_from_other_doc_flag,
L.recalc_required_flag,
L.settlement_flag,
L.item_dist_changed_flag,
L.associated_child_frozen_flag,
L.tax_only_line_flag,
L.compounding_dep_tax_flag,
L.last_manual_entry,
L.tax_provider_id,
L.record_type_code,
L.reporting_period_id,
L.legal_message_appl_2,
L.legal_message_status,
L.legal_message_rate,
L.legal_message_basis,
L.legal_message_calc,
L.legal_message_threshold,
L.legal_message_pos,
L.legal_message_trn,
L.legal_message_exmpt,
L.legal_message_excpt,
L.tax_regime_template_id,
L.tax_applicability_result_id,
L.direct_rate_result_id,
L.status_result_id,
L.rate_result_id,
L.basis_result_id,
L.thresh_result_id,
L.calc_result_id,
L.tax_reg_num_det_result_id,
L.eval_exmpt_result_id,
L.eval_excpt_result_id,
L.enforce_from_natural_acct_flag,
NULL, -- L.tax_hold_code,
NULL, -- L.tax_hold_released_code,
NULL, -- L.prd_total_tax_amt,
NULL, -- L.prd_total_tax_amt_tax_curr,
NULL, -- L.prd_total_tax_amt_funcl_curr,
L.internal_org_location_id,
L.attribute_category,
L.attribute1,
L.attribute2,
L.attribute3,
L.attribute4,
L.attribute5,
L.attribute6,
L.attribute7,
L.attribute8,
L.attribute9,
L.attribute10,
L.attribute11,
L.attribute12,
L.attribute13,
L.attribute14,
L.attribute15,
L.global_attribute_category,
L.global_attribute1,
L.global_attribute2,
L.global_attribute3,
L.global_attribute4,
L.global_attribute5,
L.global_attribute6,
L.global_attribute7,
L.global_attribute8,
L.global_attribute9,
L.global_attribute10,
L.global_attribute11,
L.global_attribute12,
L.global_attribute13,
L.global_attribute14,
L.global_attribute15,
L.numeric1,
L.numeric2,
L.numeric3,
L.numeric4,
L.numeric5,
L.numeric6,
L.numeric7,
L.numeric8,
L.numeric9,
L.numeric10,
L.char1,
L.char2,
L.char3,
L.char4,
L.char5,
L.char6,
L.char7,
L.char8,
L.char9,
L.char10,
L.date1,
L.date2,
L.date3,
L.date4,
L.date5,
L.date6,
L.date7,
L.date8,
L.date9,
L.date10,
L.tax_rate_type,
L.created_by,
L.creation_date,
L.last_updated_by,
L.last_update_date,
L.last_update_login,
L.line_assessable_value,
L.legal_justification_text1,
L.legal_justification_text2,
L.legal_justification_text3,
L.reporting_currency_code,
NULL, -- L.trx_line_index
NULL, -- L.offset_tax_rate_code
NULL, -- L.proration_code
NULL, -- L.other_doc_source
L.reporting_only_flag,
L.ctrl_total_line_tx_amt,
DECODE(L.tax_provider_id, NULL, L.sync_with_prvdr_flag, 'Y'),
L.interface_entity_code,
L.interface_tax_line_id,
L.taxing_juris_geography_id,
L.adjusted_doc_tax_line_id,
1,
DECODE(L.legal_reporting_status, '111111111111111',
'000000000000000', L.legal_reporting_status),
L.account_source_tax_rate_id
FROM zx_lines L,
zx_lines_det_factors G
WHERE G.application_id = p_event_class_rec.application_id
AND G.entity_code = p_event_class_rec.entity_code
AND G.event_class_code = p_event_class_rec.event_class_code
-- AND G.trx_id = p_event_class_rec.trx_id
AND G.event_id = p_event_class_rec.event_id
AND L.trx_id = G.trx_id
AND L.trx_line_id = G.trx_line_id
AND L.trx_level_type = G.trx_level_type
AND L.event_class_code = G.event_class_code
AND L.entity_code = G.entity_code
AND L.application_id = G.application_id
AND L.associated_child_frozen_flag = 'Y'
AND L.cancel_flag <> 'Y'
-- bug 6906427
AND G.line_level_action = 'UPDATE'
AND NOT EXISTS ( SELECT 1
FROM zx_detail_tax_lines_gt T
WHERE T.tax_line_id = L.tax_line_id
-- bug 8470599 these columns are redundant and are doing index range scan on ZX_DETAIL_TAX_LINES_GT_U1
-- AND T.trx_id = L.trx_id
-- AND T.trx_line_id = L.trx_line_id
-- AND T.event_class_code = L.event_class_code
-- AND T.entity_code = L.entity_code
-- AND T.application_id = L.application_id
-- AND T.trx_level_type = L.trx_level_type
-- AND T.tax = L.tax
-- AND T.tax_regime_code = L.tax_regime_code
-- AND T.tax_status_code = L.tax_status_code
-- AND T.tax_line_number = L.tax_line_number
)
);
'Number of Rows Inserted: ' || SQL%ROWCOUNT);
INSERT INTO zx_detail_tax_lines_gt
( tax_line_id,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_line_id,
trx_level_type,
trx_line_number,
doc_event_status,
-- line_event_status,
tax_event_class_code,
tax_event_type_code,
tax_line_number,
content_owner_id,
tax_regime_id,
tax_regime_code,
tax_id,
tax,
tax_status_id,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
tax_apportionment_line_number,
trx_id_level2,
trx_id_level3,
trx_id_level4,
trx_id_level5,
trx_id_level6,
trx_user_key_level1,
trx_user_key_level2,
trx_user_key_level3,
trx_user_key_level4,
trx_user_key_level5,
trx_user_key_level6,
mrc_tax_line_flag,
ledger_id,
establishment_id,
legal_entity_id,
legal_entity_tax_reg_number,
hq_estb_reg_number,
hq_estb_party_tax_prof_id,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
tax_currency_conversion_date,
tax_currency_conversion_type,
tax_currency_conversion_rate,
trx_currency_code,
minimum_accountable_unit,
precision,
trx_number,
trx_date,
unit_price,
line_amt,
trx_line_quantity,
tax_base_modifier_rate,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_line_quantity,
other_doc_line_amt,
other_doc_line_tax_amt,
other_doc_line_taxable_amt,
unrounded_taxable_amt,
unrounded_tax_amt,
related_doc_application_id,
related_doc_entity_code,
related_doc_event_class_code,
related_doc_trx_id,
related_doc_number,
related_doc_date,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
applied_from_trx_level_type,
applied_from_trx_number,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
adjusted_doc_line_id,
adjusted_doc_trx_level_type,
adjusted_doc_number,
adjusted_doc_date,
applied_to_application_id,
applied_to_event_class_code,
applied_to_entity_code,
applied_to_trx_id,
applied_to_line_id,
applied_to_trx_level_type,
applied_to_trx_number,
summary_tax_line_id,
offset_link_to_tax_line_id,
offset_flag,
process_for_recovery_flag,
tax_jurisdiction_id,
tax_jurisdiction_code,
place_of_supply,
place_of_supply_type_code,
place_of_supply_result_id,
tax_date_rule_id,
tax_date,
tax_determine_date,
tax_point_date,
trx_line_date,
tax_type_code,
tax_code,
tax_registration_id,
tax_registration_number,
registration_party_type,
rounding_level_code,
rounding_rule_code,
rounding_lvl_party_tax_prof_id,
rounding_lvl_party_type,
compounding_tax_flag,
orig_tax_status_id,
orig_tax_status_code,
orig_tax_rate_id,
orig_tax_rate_code,
orig_tax_rate,
orig_tax_jurisdiction_id,
orig_tax_jurisdiction_code,
orig_tax_amt_included_flag,
orig_self_assessed_flag,
tax_currency_code,
tax_amt,
tax_amt_tax_curr,
tax_amt_funcl_curr,
taxable_amt,
taxable_amt_tax_curr,
taxable_amt_funcl_curr,
orig_taxable_amt,
orig_taxable_amt_tax_curr,
cal_tax_amt,
cal_tax_amt_tax_curr,
cal_tax_amt_funcl_curr,
orig_tax_amt,
orig_tax_amt_tax_curr,
rec_tax_amt,
rec_tax_amt_tax_curr,
rec_tax_amt_funcl_curr,
nrec_tax_amt,
nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
tax_apportionment_flag,
historical_flag,
taxable_basis_formula,
tax_calculation_formula,
cancel_flag,
purge_flag,
delete_flag,
tax_amt_included_flag,
self_assessed_flag,
overridden_flag,
manually_entered_flag,
freeze_until_overridden_flag,
copied_from_other_doc_flag,
recalc_required_flag,
settlement_flag,
item_dist_changed_flag,
associated_child_frozen_flag,
tax_only_line_flag,
compounding_dep_tax_flag,
last_manual_entry,
tax_provider_id,
record_type_code,
reporting_period_id,
legal_message_appl_2,
legal_message_status,
legal_message_rate,
legal_message_basis,
legal_message_calc,
legal_message_threshold,
legal_message_pos,
legal_message_trn,
legal_message_exmpt,
legal_message_excpt,
tax_regime_template_id,
tax_applicability_result_id,
direct_rate_result_id,
status_result_id,
rate_result_id,
basis_result_id,
thresh_result_id,
calc_result_id,
tax_reg_num_det_result_id,
eval_exmpt_result_id,
eval_excpt_result_id,
enforce_from_natural_acct_flag,
tax_hold_code,
tax_hold_released_code,
prd_total_tax_amt,
prd_total_tax_amt_tax_curr,
prd_total_tax_amt_funcl_curr,
internal_org_location_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
numeric1,
numeric2,
numeric3,
numeric4,
numeric5,
numeric6,
numeric7,
numeric8,
numeric9,
numeric10,
char1,
char2,
char3,
char4,
char5,
char6,
char7,
char8,
char9,
char10,
date1,
date2,
date3,
date4,
date5,
date6,
date7,
date8,
date9,
date10,
tax_rate_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
line_assessable_value,
legal_justification_text1,
legal_justification_text2,
legal_justification_text3,
reporting_currency_code,
trx_line_index,
offset_tax_rate_code,
proration_code,
other_doc_source,
reporting_only_flag,
ctrl_total_line_tx_amt,
sync_with_prvdr_flag,
interface_entity_code,
interface_tax_line_id,
taxing_juris_geography_id,
adjusted_doc_tax_line_id,
object_version_number,
legal_reporting_status,
account_source_tax_rate_id
)
(SELECT /*+ leading(G) */
L.tax_line_id,
L.internal_organization_id,
L.application_id,
L.entity_code,
L.event_class_code,
L.event_type_code,
L.trx_id,
L.trx_line_id,
L.trx_level_type,
L.trx_line_number,
L.doc_event_status,
-- L.line_event_status,
L.tax_event_class_code,
L.tax_event_type_code,
L.tax_line_number,
L.content_owner_id,
L.tax_regime_id,
L.tax_regime_code,
L.tax_id,
L.tax,
L.tax_status_id,
L.tax_status_code,
L.tax_rate_id,
L.tax_rate_code,
L.tax_rate,
L.tax_apportionment_line_number,
L.trx_id_level2,
L.trx_id_level3,
L.trx_id_level4,
L.trx_id_level5,
L.trx_id_level6,
L.trx_user_key_level1,
L.trx_user_key_level2,
L.trx_user_key_level3,
L.trx_user_key_level4,
L.trx_user_key_level5,
L.trx_user_key_level6,
L.mrc_tax_line_flag,
G.ledger_id,
G.establishment_id,
G.legal_entity_id,
L.legal_entity_tax_reg_number,
L.hq_estb_reg_number,
L.hq_estb_party_tax_prof_id,
G.currency_conversion_date,
G.currency_conversion_type,
G.currency_conversion_rate,
L.tax_currency_conversion_date,
L.tax_currency_conversion_type,
L.tax_currency_conversion_rate,
L.trx_currency_code,
L.minimum_accountable_unit,
L.precision,
G.trx_number,
G.trx_date,
L.unit_price,
L.line_amt,
L.trx_line_quantity,
L.tax_base_modifier_rate,
L.ref_doc_application_id,
L.ref_doc_entity_code,
L.ref_doc_event_class_code,
L.ref_doc_trx_id,
L.ref_doc_line_id,
L.ref_doc_line_quantity,
L.other_doc_line_amt,
L.other_doc_line_tax_amt,
L.other_doc_line_taxable_amt,
0, -- L.unrounded_taxable_amt
0, -- L.unrounded_tax_amt
L.related_doc_application_id,
L.related_doc_entity_code,
L.related_doc_event_class_code,
L.related_doc_trx_id,
L.related_doc_number,
L.related_doc_date,
L.applied_from_application_id,
L.applied_from_event_class_code,
L.applied_from_entity_code,
L.applied_from_trx_id,
L.applied_from_line_id,
L.applied_from_trx_level_type,
L.applied_from_trx_number,
L.adjusted_doc_application_id,
L.adjusted_doc_entity_code,
L.adjusted_doc_event_class_code,
L.adjusted_doc_trx_id,
L.adjusted_doc_line_id,
L.adjusted_doc_trx_level_type,
L.adjusted_doc_number,
L.adjusted_doc_date,
L.applied_to_application_id,
L.applied_to_event_class_code,
L.applied_to_entity_code,
L.applied_to_trx_id,
L.applied_to_line_id,
L.applied_to_trx_level_type,
L.applied_to_trx_number,
L.summary_tax_line_id,
L.offset_link_to_tax_line_id,
L.offset_flag,
DECODE(L.historical_flag,'Y','Y','N'), -- L.Process_For_Recovery_Flag
L.tax_jurisdiction_id,
L.tax_jurisdiction_code,
L.place_of_supply,
L.place_of_supply_type_code,
L.place_of_supply_result_id,
L.tax_date_rule_id,
L.tax_date,
L.tax_determine_date,
L.tax_point_date,
L.trx_line_date,
L.tax_type_code,
L.tax_code,
L.tax_registration_id,
L.tax_registration_number,
L.registration_party_type,
L.rounding_level_code,
L.rounding_rule_code,
L.rounding_lvl_party_tax_prof_id,
L.rounding_lvl_party_type,
L.compounding_tax_flag,
L.orig_tax_status_id,
L.orig_tax_status_code,
L.orig_tax_rate_id,
L.orig_tax_rate_code,
L.orig_tax_rate,
L.orig_tax_jurisdiction_id,
L.orig_tax_jurisdiction_code,
L.orig_tax_amt_included_flag,
L.orig_self_assessed_flag,
L.tax_currency_code,
0, -- L.tax_amt
0, -- L.tax_amt_tax_curr
0, -- L.tax_amt_funcl_curr ??
0, -- taxable_amt
0, -- taxable_amt_tax_curr
0, -- L.taxable_amt_funcl_curr ??
DECODE(L.orig_taxable_amt, NULL, L.taxable_amt,
L.orig_taxable_amt), -- orig_taxable_amt
DECODE(L.orig_taxable_amt_tax_curr, NULL, L.taxable_amt_tax_curr,
L.orig_taxable_amt_tax_curr), -- orig_taxable_amt_tax_curr
0, -- L.cal_tax_amt
0, -- L.cal_tax_amt_tax_curr
0, -- L.cal_tax_amt_funcl_curr ??
DECODE(L.orig_tax_amt, NULL, L.tax_amt,
L.orig_tax_amt), -- L.orig_tax_amt,
DECODE(L.orig_tax_amt_tax_curr, NULL, L.tax_amt_tax_curr,
L.orig_tax_amt_tax_curr), -- L.orig_tax_amt_tax_curr
0, -- L.rec_tax_amt
0, -- L.rec_tax_amt_tax_curr
0, -- L.rec_tax_amt_funcl_curr ??
0, -- L.nrec_tax_amt
0, -- L.nrec_tax_amt_tax_curr
0, -- L.nrec_tax_amt_funcl_curr ??
L.tax_exemption_id,
L.tax_rate_before_exemption,
L.tax_rate_name_before_exemption,
L.exempt_rate_modifier,
L.exempt_certificate_number,
L.exempt_reason,
L.exempt_reason_code,
L.tax_exception_id,
L.tax_rate_before_exception,
L.tax_rate_name_before_exception,
L.exception_rate,
L.tax_apportionment_flag,
L.historical_flag,
L.taxable_basis_formula,
L.tax_calculation_formula,
'Y', -- DECODE(L.historical_flag, 'Y', 'Y', L.cancel_flag),
L.purge_flag,
L.delete_flag,
L.tax_amt_included_flag,
L.self_assessed_flag,
L.overridden_flag,
L.manually_entered_flag,
L.freeze_until_overridden_flag,
L.copied_from_other_doc_flag,
L.recalc_required_flag,
L.settlement_flag,
L.item_dist_changed_flag,
L.associated_child_frozen_flag,
L.tax_only_line_flag,
L.compounding_dep_tax_flag,
L.last_manual_entry,
L.tax_provider_id,
L.record_type_code,
L.reporting_period_id,
L.legal_message_appl_2,
L.legal_message_status,
L.legal_message_rate,
L.legal_message_basis,
L.legal_message_calc,
L.legal_message_threshold,
L.legal_message_pos,
L.legal_message_trn,
L.legal_message_exmpt,
L.legal_message_excpt,
L.tax_regime_template_id,
L.tax_applicability_result_id,
L.direct_rate_result_id,
L.status_result_id,
L.rate_result_id,
L.basis_result_id,
L.thresh_result_id,
L.calc_result_id,
L.tax_reg_num_det_result_id,
L.eval_exmpt_result_id,
L.eval_excpt_result_id,
L.enforce_from_natural_acct_flag,
NULL, --L.tax_hold_code,
NULL, --L.tax_hold_released_code,
NULL, -- L.prd_total_tax_amt
NULL, -- L.prd_total_tax_amt_tax_curr
NULL, -- L.prd_total_tax_amt_funcl_curr
L.internal_org_location_id,
L.attribute_category,
L.attribute1,
L.attribute2,
L.attribute3,
L.attribute4,
L.attribute5,
L.attribute6,
L.attribute7,
L.attribute8,
L.attribute9,
L.attribute10,
L.attribute11,
L.attribute12,
L.attribute13,
L.attribute14,
L.attribute15,
L.global_attribute_category,
L.global_attribute1,
L.global_attribute2,
L.global_attribute3,
L.global_attribute4,
L.global_attribute5,
L.global_attribute6,
L.global_attribute7,
L.global_attribute8,
L.global_attribute9,
L.global_attribute10,
L.global_attribute11,
L.global_attribute12,
L.global_attribute13,
L.global_attribute14,
L.global_attribute15,
L.numeric1,
L.numeric2,
L.numeric3,
L.numeric4,
L.numeric5,
L.numeric6,
L.numeric7,
L.numeric8,
L.numeric9,
L.numeric10,
L.char1,
L.char2,
L.char3,
L.char4,
L.char5,
L.char6,
L.char7,
L.char8,
L.char9,
L.char10,
L.date1,
L.date2,
L.date3,
L.date4,
L.date5,
L.date6,
L.date7,
L.date8,
L.date9,
L.date10,
L.tax_rate_type,
L.created_by,
L.creation_date,
fnd_global.user_id, --L.last_updated_by,
SYSDATE, --L.last_update_date,
fnd_global.login_id,--L.last_update_login,
L.line_assessable_value,
L.legal_justification_text1,
L.legal_justification_text2,
L.legal_justification_text3,
L.reporting_currency_code,
NULL, -- L.trx_line_index
NULL, -- L.offset_tax_rate_code
NULL, -- L.proration_code
NULL, -- L.other_doc_source
L.reporting_only_flag,
L.ctrl_total_line_tx_amt,
L.sync_with_prvdr_flag,
L.interface_entity_code,
L.interface_tax_line_id,
L.taxing_juris_geography_id,
L.adjusted_doc_tax_line_id,
1,
decode(L.legal_reporting_status,'111111111111111',
'000000000000000',L.legal_reporting_status),
L.account_source_tax_rate_id
FROM ZX_LINES L,
zx_lines_det_factors G
WHERE G.application_id = p_event_class_rec.application_id AND
G.entity_code = p_event_class_rec.entity_code AND
G.event_class_code = p_event_class_rec.event_class_code AND
-- G.trx_id = p_event_class_rec.trx_id AND
G.event_id = p_event_class_rec.event_id AND
L.trx_id = G.trx_id AND
L.trx_line_id = G.trx_line_id AND
L.trx_level_type = G.trx_level_type AND
L.event_class_code = G.event_class_code AND
-- L.event_type_code = G.event_type_code AND
L.entity_code = G.entity_code AND
L.application_id = G.application_id AND
-- L.subscriber_id = G.subscriber_id AND
G.line_level_action IN ('DISCARD', 'UNAPPLY_FROM') AND
L.tax_provider_id IS NULL
AND NOT EXISTS
(SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
1
FROM zx_detail_tax_lines_gt gt
WHERE gt.application_id = L.application_id
AND gt.entity_code = L.entity_code
AND gt.event_class_code = L.event_class_code
AND gt.trx_id = L.trx_id
AND gt.trx_line_id = L.trx_line_id
AND gt.trx_level_type = L.trx_level_type
AND gt.tax_regime_code = L.tax_regime_code
AND gt.tax = L.tax
AND NVL(gt.tax_apportionment_line_number, -999999) =
NVL(L.tax_apportionment_line_number, -999999)
)
);
'Number of Rows Inserted: ' || SQL%ROWCOUNT);
INSERT INTO zx_detail_tax_lines_gt
( tax_line_id,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_line_id,
trx_level_type,
trx_line_number,
doc_event_status,
tax_event_class_code,
tax_event_type_code,
tax_line_number,
content_owner_id,
tax_regime_id,
tax_regime_code,
tax_id,
tax,
tax_status_id,
tax_status_code,
tax_rate_id,
tax_rate_code,
tax_rate,
tax_apportionment_line_number,
trx_id_level2,
trx_id_level3,
trx_id_level4,
trx_id_level5,
trx_id_level6,
trx_user_key_level1,
trx_user_key_level2,
trx_user_key_level3,
trx_user_key_level4,
trx_user_key_level5,
trx_user_key_level6,
mrc_tax_line_flag,
ledger_id,
establishment_id,
legal_entity_id,
legal_entity_tax_reg_number,
hq_estb_reg_number,
hq_estb_party_tax_prof_id,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
tax_currency_conversion_date,
tax_currency_conversion_type,
tax_currency_conversion_rate,
trx_currency_code,
minimum_accountable_unit,
precision,
trx_number,
trx_date,
unit_price,
line_amt,
trx_line_quantity,
tax_base_modifier_rate,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_line_quantity,
other_doc_line_amt,
other_doc_line_tax_amt,
other_doc_line_taxable_amt,
unrounded_taxable_amt,
unrounded_tax_amt,
related_doc_application_id,
related_doc_entity_code,
related_doc_event_class_code,
related_doc_trx_id,
related_doc_number,
related_doc_date,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
applied_from_trx_level_type,
applied_from_trx_number,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
adjusted_doc_line_id,
adjusted_doc_trx_level_type,
adjusted_doc_number,
adjusted_doc_date,
applied_to_application_id,
applied_to_event_class_code,
applied_to_entity_code,
applied_to_trx_id,
applied_to_line_id,
applied_to_trx_number,
summary_tax_line_id,
offset_link_to_tax_line_id,
offset_flag,
process_for_recovery_flag,
tax_jurisdiction_id,
tax_jurisdiction_code,
place_of_supply,
place_of_supply_type_code,
place_of_supply_result_id,
tax_date_rule_id,
tax_date,
tax_determine_date,
tax_point_date,
trx_line_date,
tax_type_code,
tax_code,
tax_registration_id,
tax_registration_number,
registration_party_type,
rounding_level_code,
rounding_rule_code,
rounding_lvl_party_tax_prof_id,
rounding_lvl_party_type,
compounding_tax_flag,
orig_tax_status_id,
orig_tax_status_code,
orig_tax_rate_id,
orig_tax_rate_code,
orig_tax_rate,
orig_tax_jurisdiction_id,
orig_tax_jurisdiction_code,
orig_tax_amt_included_flag,
orig_self_assessed_flag,
tax_currency_code,
tax_amt,
tax_amt_tax_curr,
tax_amt_funcl_curr,
taxable_amt,
taxable_amt_tax_curr,
taxable_amt_funcl_curr,
orig_taxable_amt,
orig_taxable_amt_tax_curr,
cal_tax_amt,
cal_tax_amt_tax_curr,
cal_tax_amt_funcl_curr,
orig_tax_amt,
orig_tax_amt_tax_curr,
rec_tax_amt,
rec_tax_amt_tax_curr,
rec_tax_amt_funcl_curr,
nrec_tax_amt,
nrec_tax_amt_tax_curr,
nrec_tax_amt_funcl_curr,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
tax_apportionment_flag,
historical_flag,
taxable_basis_formula,
tax_calculation_formula,
cancel_flag,
purge_flag,
delete_flag,
tax_amt_included_flag,
self_assessed_flag,
overridden_flag,
manually_entered_flag,
freeze_until_overridden_flag,
copied_from_other_doc_flag,
recalc_required_flag,
settlement_flag,
item_dist_changed_flag,
associated_child_frozen_flag,
tax_only_line_flag,
compounding_dep_tax_flag,
last_manual_entry,
tax_provider_id,
record_type_code,
reporting_period_id,
legal_message_appl_2,
legal_message_status,
legal_message_rate,
legal_message_basis,
legal_message_calc,
legal_message_threshold,
legal_message_pos,
legal_message_trn,
legal_message_exmpt,
legal_message_excpt,
tax_regime_template_id,
tax_applicability_result_id,
direct_rate_result_id,
status_result_id,
rate_result_id,
basis_result_id,
thresh_result_id,
calc_result_id,
tax_reg_num_det_result_id,
eval_exmpt_result_id,
eval_excpt_result_id,
enforce_from_natural_acct_flag,
tax_hold_code,
tax_hold_released_code,
prd_total_tax_amt,
prd_total_tax_amt_tax_curr,
prd_total_tax_amt_funcl_curr,
internal_org_location_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
numeric1,
numeric2,
numeric3,
numeric4,
numeric5,
numeric6,
numeric7,
numeric8,
numeric9,
numeric10,
char1,
char2,
char3,
char4,
char5,
char6,
char7,
char8,
char9,
char10,
date1,
date2,
date3,
date4,
date5,
date6,
date7,
date8,
date9,
date10,
tax_rate_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
line_assessable_value,
legal_justification_text1,
legal_justification_text2,
legal_justification_text3,
reporting_currency_code,
trx_line_index,
offset_tax_rate_code,
proration_code,
other_doc_source,
reporting_only_flag,
ctrl_total_line_tx_amt,
sync_with_prvdr_flag,
interface_entity_code,
interface_tax_line_id,
taxing_juris_geography_id,
adjusted_doc_tax_line_id,
object_version_number,
legal_reporting_status,
account_source_tax_rate_id
)
(SELECT /*+ leading(G) */
L.tax_line_id,
L.internal_organization_id,
L.application_id,
L.entity_code,
L.event_class_code,
L.event_type_code,
L.trx_id,
L.trx_line_id,
L.trx_level_type,
L.trx_line_number,
L.doc_event_status,
L.tax_event_class_code,
L.tax_event_type_code,
L.tax_line_number,
L.content_owner_id,
L.tax_regime_id,
L.tax_regime_code,
L.tax_id,
L.tax,
L.tax_status_id,
L.tax_status_code,
L.tax_rate_id,
L.tax_rate_code,
L.tax_rate,
L.tax_apportionment_line_number,
L.trx_id_level2,
L.trx_id_level3,
L.trx_id_level4,
L.trx_id_level5,
L.trx_id_level6,
L.trx_user_key_level1,
L.trx_user_key_level2,
L.trx_user_key_level3,
L.trx_user_key_level4,
L.trx_user_key_level5,
L.trx_user_key_level6,
L.mrc_tax_line_flag,
L.ledger_id,
L.establishment_id,
L.legal_entity_id,
L.legal_entity_tax_reg_number,
L.hq_estb_reg_number,
L.hq_estb_party_tax_prof_id,
L.currency_conversion_date,
L.currency_conversion_type,
L.currency_conversion_rate,
L.tax_currency_conversion_date,
L.tax_currency_conversion_type,
L.tax_currency_conversion_rate,
L.trx_currency_code,
L.minimum_accountable_unit,
L.precision,
G.trx_number,
L.trx_date,
L.unit_price,
L.line_amt,
L.trx_line_quantity,
L.tax_base_modifier_rate,
L.ref_doc_application_id,
L.ref_doc_entity_code,
L.ref_doc_event_class_code,
L.ref_doc_trx_id,
L.ref_doc_line_id,
L.ref_doc_line_quantity,
L.other_doc_line_amt,
L.other_doc_line_tax_amt,
L.other_doc_line_taxable_amt,
L.unrounded_taxable_amt,
0, -- L.unrounded_tax_amt,
L.related_doc_application_id,
L.related_doc_entity_code,
L.related_doc_event_class_code,
L.related_doc_trx_id,
L.related_doc_number,
L.related_doc_date,
L.applied_from_application_id,
L.applied_from_event_class_code,
L.applied_from_entity_code,
L.applied_from_trx_id,
L.applied_from_line_id,
L.applied_from_trx_level_type,
L.applied_from_trx_number,
L.adjusted_doc_application_id,
L.adjusted_doc_entity_code,
L.adjusted_doc_event_class_code,
L.adjusted_doc_trx_id,
L.adjusted_doc_line_id,
L.adjusted_doc_trx_level_type,
L.adjusted_doc_number,
L.adjusted_doc_date,
L.applied_to_application_id,
L.applied_to_event_class_code,
L.applied_to_entity_code,
L.applied_to_trx_id,
L.applied_to_line_id,
L.applied_to_trx_number,
L.summary_tax_line_id,
L.offset_link_to_tax_line_id,
L.offset_flag,
DECODE(L.reporting_only_flag, 'N', 'Y', 'N'), -- L.process_for_recovery_flag,
L.tax_jurisdiction_id,
L.tax_jurisdiction_code,
L.place_of_supply,
L.place_of_supply_type_code,
L.place_of_supply_result_id,
L.tax_date_rule_id,
L.tax_date,
L.tax_determine_date,
L.tax_point_date,
L.trx_line_date,
L.tax_type_code,
L.tax_code,
L.tax_registration_id,
L.tax_registration_number,
L.registration_party_type,
L.rounding_level_code,
L.rounding_rule_code,
L.rounding_lvl_party_tax_prof_id,
L.rounding_lvl_party_type,
L.compounding_tax_flag,
L.orig_tax_status_id,
L.orig_tax_status_code,
L.orig_tax_rate_id,
L.orig_tax_rate_code,
L.orig_tax_rate,
L.orig_tax_jurisdiction_id,
L.orig_tax_jurisdiction_code,
L.orig_tax_amt_included_flag,
L.orig_self_assessed_flag,
L.tax_currency_code,
0, -- L.tax_amt,
0, -- L.tax_amt_tax_curr,
0, -- L.tax_amt_funcl_curr,
taxable_amt,
taxable_amt_tax_curr,
L.taxable_amt_funcl_curr,
L.orig_taxable_amt,
L.orig_taxable_amt_tax_curr,
L.cal_tax_amt,
L.cal_tax_amt_tax_curr,
L.cal_tax_amt_funcl_curr,
L.orig_tax_amt,
L.orig_tax_amt_tax_curr,
L.rec_tax_amt,
L.rec_tax_amt_tax_curr,
L.rec_tax_amt_funcl_curr,
L.nrec_tax_amt,
L.nrec_tax_amt_tax_curr,
L.nrec_tax_amt_funcl_curr,
L.tax_exemption_id,
L.tax_rate_before_exemption,
L.tax_rate_name_before_exemption,
L.exempt_rate_modifier,
L.exempt_certificate_number,
L.exempt_reason,
L.exempt_reason_code,
L.tax_exception_id,
L.tax_rate_before_exception,
L.tax_rate_name_before_exception,
L.exception_rate,
L.tax_apportionment_flag,
L.historical_flag,
L.taxable_basis_formula,
L.tax_calculation_formula,
'Y', -- L.cancel_flag,
L.purge_flag,
L.delete_flag,
L.tax_amt_included_flag,
L.self_assessed_flag,
L.overridden_flag,
L.manually_entered_flag,
L.freeze_until_overridden_flag,
L.copied_from_other_doc_flag,
L.recalc_required_flag,
L.settlement_flag,
L.item_dist_changed_flag,
L.associated_child_frozen_flag,
L.tax_only_line_flag,
L.compounding_dep_tax_flag,
L.last_manual_entry,
L.tax_provider_id,
L.record_type_code,
L.reporting_period_id,
L.legal_message_appl_2,
L.legal_message_status,
L.legal_message_rate,
L.legal_message_basis,
L.legal_message_calc,
L.legal_message_threshold,
L.legal_message_pos,
L.legal_message_trn,
L.legal_message_exmpt,
L.legal_message_excpt,
L.tax_regime_template_id,
L.tax_applicability_result_id,
L.direct_rate_result_id,
L.status_result_id,
L.rate_result_id,
L.basis_result_id,
L.thresh_result_id,
L.calc_result_id,
L.tax_reg_num_det_result_id,
L.eval_exmpt_result_id,
L.eval_excpt_result_id,
L.enforce_from_natural_acct_flag,
NULL, -- L.tax_hold_code,
NULL, -- L.tax_hold_released_code,
NULL, -- L.prd_total_tax_amt,
NULL, -- L.prd_total_tax_amt_tax_curr,
NULL, -- L.prd_total_tax_amt_funcl_curr,
L.internal_org_location_id,
L.attribute_category,
L.attribute1,
L.attribute2,
L.attribute3,
L.attribute4,
L.attribute5,
L.attribute6,
L.attribute7,
L.attribute8,
L.attribute9,
L.attribute10,
L.attribute11,
L.attribute12,
L.attribute13,
L.attribute14,
L.attribute15,
L.global_attribute_category,
L.global_attribute1,
L.global_attribute2,
L.global_attribute3,
L.global_attribute4,
L.global_attribute5,
L.global_attribute6,
L.global_attribute7,
L.global_attribute8,
L.global_attribute9,
L.global_attribute10,
L.global_attribute11,
L.global_attribute12,
L.global_attribute13,
L.global_attribute14,
L.global_attribute15,
L.numeric1,
L.numeric2,
L.numeric3,
L.numeric4,
L.numeric5,
L.numeric6,
L.numeric7,
L.numeric8,
L.numeric9,
L.numeric10,
L.char1,
L.char2,
L.char3,
L.char4,
L.char5,
L.char6,
L.char7,
L.char8,
L.char9,
L.char10,
L.date1,
L.date2,
L.date3,
L.date4,
L.date5,
L.date6,
L.date7,
L.date8,
L.date9,
L.date10,
L.tax_rate_type,
L.created_by,
L.creation_date,
L.last_updated_by,
L.last_update_date,
L.last_update_login,
L.line_assessable_value,
L.legal_justification_text1,
L.legal_justification_text2,
L.legal_justification_text3,
L.reporting_currency_code,
L.trx_line_index,
L.offset_tax_rate_code,
L.proration_code,
L.other_doc_source,
L.reporting_only_flag,
L.ctrl_total_line_tx_amt,
L.sync_with_prvdr_flag,
L.interface_entity_code,
L.interface_tax_line_id,
L.taxing_juris_geography_id,
L.adjusted_doc_tax_line_id,
1,
decode(L.legal_reporting_status, '111111111111111',
'000000000000000', L.legal_reporting_status),
L.account_source_tax_rate_id
FROM ZX_LINES L,
zx_lines_det_factors G
WHERE G.application_id = p_event_class_rec.application_id AND
G.entity_code = p_event_class_rec.entity_code AND
G.event_class_code = p_event_class_rec.event_class_code AND
-- G.trx_id = p_event_class_rec.trx_id AND
G.event_id = p_event_class_rec.event_id AND
L.trx_id = G.trx_id AND
L.trx_line_id = G.trx_line_id AND
L.trx_level_type = G.trx_level_type AND
L.event_class_code = G.event_class_code AND
-- L.event_type_code = G.event_type_code AND
L.entity_code = G.entity_code AND
L.application_id = G.application_id AND
-- L.subscriber_id = G.subscriber_id AND
G.line_level_action = 'CANCEL' AND
L.tax_provider_id IS NULL
);
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
tax_line_id,
orig_tax_amt_tax_curr,
tax_amt_tax_curr,
tax_id,
tax_hold_released_code,
Rounding_Rule_Code
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 tax_event_type_code = 'OVERRIDE_TAX'
AND offset_link_to_tax_line_id IS NULL
AND orig_tax_amt_tax_curr IS NOT NULL
AND mrc_tax_line_flag = 'N';
SELECT /*+ INDEX(tax_line ZX_DETAIL_TAX_LINES_GT_U1) */
tax_line.tax_line_id,
tax_line.orig_tax_amt_tax_curr,
tax_line.tax_amt_tax_curr,
tax_line.tax_id,
tax_line.tax_hold_released_code,
tax_line.Rounding_Rule_Code
FROM ZX_DETAIL_TAX_LINES_GT tax_line,
zx_lines_det_factors trx_line
WHERE tax_line.application_id = p_event_class_rec.application_id
AND tax_line.entity_code = p_event_class_rec.entity_code
AND tax_line.event_class_code = p_event_class_rec.event_class_code
AND tax_line.trx_id = p_event_class_rec.trx_id
AND 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
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.offset_link_to_tax_line_id IS NULL
AND tax_line.orig_tax_amt IS NOT NULL
AND tax_line.mrc_tax_line_flag = 'N';
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
tax_line_id,
orig_tax_amt_tax_curr,
tax_amt_tax_curr,
tax_id,
tax_hold_released_code,
Rounding_Rule_Code
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 ctrl_total_line_tx_amt IS NOT NULL
AND offset_link_to_tax_line_id IS NULL
AND orig_tax_amt IS NOT NULL
AND mrc_tax_line_flag = 'N';
SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
tax_line_id,
orig_tax_amt_tax_curr,
tax_amt_tax_curr,
tax_id,
tax_hold_released_code,
Rounding_Rule_Code
FROM zx_detail_tax_lines_gt gt
WHERE gt.application_id = p_event_class_rec.application_id
AND gt.entity_code = p_event_class_rec.entity_code
AND gt.event_class_code = p_event_class_rec.event_class_code
AND (gt.tax_event_type_code = 'UPDATE' AND
gt.last_manual_entry IS NOT NULL AND
NVL(gt.manually_entered_flag, 'N') <> 'Y'
)
AND gt.offset_link_to_tax_line_id IS NULL
AND gt.orig_tax_amt_tax_curr IS NOT NULL
AND gt.mrc_tax_line_flag = 'N'
AND EXISTS
(SELECT /*+ index(LINE ZX_LINES_DET_FACTORS_U1) */ 1
FROM zx_lines_det_factors line
WHERE line.application_id = p_event_class_rec.application_id
AND line.entity_code = p_event_class_rec.entity_code
AND line.event_class_code = p_event_class_rec.event_class_code
AND line.event_id = p_event_class_rec.event_id
AND line.trx_id = gt.trx_id
AND line.trx_line_id = gt.trx_line_id
AND line.trx_level_type = gt.trx_level_type
);
SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
tax_line_id,
orig_tax_amt_tax_curr,
tax_amt_tax_curr,
tax_id,
tax_hold_released_code,
Rounding_Rule_Code
FROM zx_detail_tax_lines_gt gt
WHERE gt.application_id = p_event_class_rec.application_id
AND gt.entity_code = p_event_class_rec.entity_code
AND gt.event_class_code = p_event_class_rec.event_class_code
AND (gt.tax_event_type_code = 'CREATE' AND
gt.last_manual_entry = 'TAX_AMOUNT' AND
NVL(gt.manually_entered_flag, 'N') = 'Y'
)
AND gt.offset_link_to_tax_line_id IS NULL
AND gt.orig_tax_amt_tax_curr IS NOT NULL
AND gt.mrc_tax_line_flag = 'N'
AND EXISTS
(SELECT 1
FROM zx_transaction_lines_gt lines_gt
WHERE lines_gt.application_id = p_event_class_rec.application_id
AND lines_gt.entity_code = p_event_class_rec.entity_code
AND lines_gt.event_class_code = p_event_class_rec.event_class_code
AND lines_gt.trx_id = gt.trx_id
AND lines_gt.trx_line_id = gt.trx_line_id
AND lines_gt.trx_level_type = gt.trx_level_type
AND lines_gt.line_level_action IN ('CREATE_WITH_TAX', 'LINE_INFO_TAX_ONLY')
);
'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - OVERRIDE_TAX');
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
ZX_DETAIL_TAX_LINES_GT
SET tax_hold_code = nvl(l_tax_hold_code_tbl(i),tax_hold_code),
tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
WHERE tax_line_id = l_tax_line_id_tbl(i);
'Number of Rows Updated: ' || SQL%ROWCOUNT);
'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - Control Line Amount');
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
ZX_DETAIL_TAX_LINES_GT
SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
WHERE tax_line_id = l_tax_line_id_tbl(i);
'Number of Rows Updated: ' || SQL%ROWCOUNT);
'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - Control Header Amount');
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
ZX_DETAIL_TAX_LINES_GT
SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
WHERE tax_line_id = l_tax_line_id_tbl(i);
'Number of Rows Updated: ' || SQL%ROWCOUNT);
IF (p_event_class_rec.tax_event_type_code = 'UPDATE' OR (ZX_TDS_CALC_SERVICES_PUB_PKG.g_ln_action_update_exist_flg = 'Y'))
THEN
OPEN get_tax_tolerance_upd_csr;
'Number of rows fetched for UPDATE = ' || to_char(l_count));
'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - UPDATE event');
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
ZX_DETAIL_TAX_LINES_GT
SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
WHERE tax_line_id = l_tax_line_id_tbl(i);
'Number of Rows Updated: ' || SQL%ROWCOUNT);
'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - CREATE event');
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
ZX_DETAIL_TAX_LINES_GT
SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
WHERE tax_line_id = l_tax_line_id_tbl(i);
'Number of Rows Updated: ' || SQL%ROWCOUNT);