FLA_SCHEDULE_IMPACTS

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID VERSION_TYPE_CODE VERSION_NUM APPROVAL_STATUS_CODE TRANSACTION_TYPE_CODE TRANSACTION_SUBTYPE_CODE PAYMENT_HEADER_ID PAYMENT_NUMBER ASSET_NUMBER CHANGE_TYPE_CODE CHANGED_IN_VERSION_NUM SCHEDULES_IMPACT_FLAG PAYMENT_ITEMS_IMPACT_FLAG LIABILITY_FLAG ROU_ASSET_FLAG REGIME_CODE DISCOUNT_RATE_INDEX_ID DV_LEASE_CLASSIFICATION_CODE DV_AMORT_PRORATION_CODE DV_AMORTIZATION_START_DATE DV_AMORTIZATION_END_DATE DV_INTEREST_RATE DV_ADDER_RATE DRAFT_SCHEDULES_EXISTS_FLAG DRAFT_SCHEDULE_HEADER_ID DRAFT_PARENT_RECORD_ID DSH_LEASE_CLASSIFICATION_CODE DSH_AMORTIZATION_START_DATE DSH_AMORTIZATION_END_DATE DV_AMORT_DATES_CHANGED_FLAG DSH_INTEREST_RATE DSH_ADDER_RATE DV_INTEREST_CHANGED_FLAG DSH_AMORT_PRORATION_CODE DV_PRORATION_CODE_CHANGED_FLAG FINAL_SCHEDULES_EXISTS_FLAG FINAL_SCHEDULE_HEADER_ID FSH_LEASE_CLASSIFICATION_CODE FSH_AMORTIZATION_START_DATE FSH_AMORTIZATION_END_DATE AMEND_AMORT_DATES_CHANGED_FLAG AMEND_AMORT_DATES_CODE FSH_INTEREST_RATE FSH_ADDER_RATE AMEND_INTEREST_CHANGED_FLAG FSH_AMORT_PRORATION_CODE DV_PERCENTAGE_OF_REDUCTION DSH_PERCENTAGE_OF_REDUCTION FSH_PERCENTAGE_OF_REDUCTION SCOPE_REDUCED_FLAG PERCENTAGE_CHANGED_FLAG SCHEDULES_REFRESH_NEEDED_FLAG COMPLIANCE_CHANGED_FLAG FINAL_LEASE_DETAIL_ID CLOSED_FLAG OPTION_FLAG PAYMENT_STATUS_CODE OPTION_STATUS_CODE ASSET_STATUS_CODE ASSET_TERMINATION_FLAG LEASE_TERMINATION_FLAG CONTINGENT_ONLY_IMPACT_FLAG MIGRATION_SCHEDULE_FLAG MIGRATED_FINANCE_ROU_AMOUNT MIGRATED_OPERATING_ROU_AMOUNT MIGRATION_IMPACT_FLAG IMPAIRMENT_IMPACT_FLAG IMPAIRMENT_INPROGRESS_FLAG ASSET_IMPAIRED_FLAG VENDOR_SITE_ID CURRENCY_CODE UNITS_CONSUMED UNIT_OF_MEASURE DV_SCOPE_CHANGE_BASIS_CODE UNITS_CHANGE_DISCARDED_FLAG

Name

LEASE_ID

LEASE_DETAIL_ID

VERSION_TYPE_CODE

VERSION_NUM

APPROVAL_STATUS_CODE

TRANSACTION_TYPE_CODE

TRANSACTION_SUBTYPE_CODE

PAYMENT_HEADER_ID

PAYMENT_NUMBER

ASSET_NUMBER

CHANGE_TYPE_CODE

CHANGED_IN_VERSION_NUM

SCHEDULES_IMPACT_FLAG

PAYMENT_ITEMS_IMPACT_FLAG

LIABILITY_FLAG

ROU_ASSET_FLAG

REGIME_CODE

DISCOUNT_RATE_INDEX_ID

DV_LEASE_CLASSIFICATION_CODE

DV_AMORT_PRORATION_CODE

DV_AMORTIZATION_START_DATE

DV_AMORTIZATION_END_DATE

DV_INTEREST_RATE

DV_ADDER_RATE

DRAFT_SCHEDULES_EXISTS_FLAG

DRAFT_SCHEDULE_HEADER_ID

DRAFT_PARENT_RECORD_ID

DSH_LEASE_CLASSIFICATION_CODE

DSH_AMORTIZATION_START_DATE

DSH_AMORTIZATION_END_DATE

DV_AMORT_DATES_CHANGED_FLAG

DSH_INTEREST_RATE

DSH_ADDER_RATE

DV_INTEREST_CHANGED_FLAG

DSH_AMORT_PRORATION_CODE

DV_PRORATION_CODE_CHANGED_FLAG

FINAL_SCHEDULES_EXISTS_FLAG

FINAL_SCHEDULE_HEADER_ID

FSH_LEASE_CLASSIFICATION_CODE

FSH_AMORTIZATION_START_DATE

FSH_AMORTIZATION_END_DATE

AMEND_AMORT_DATES_CHANGED_FLAG

AMEND_AMORT_DATES_CODE

FSH_INTEREST_RATE

FSH_ADDER_RATE

AMEND_INTEREST_CHANGED_FLAG

FSH_AMORT_PRORATION_CODE

DV_PERCENTAGE_OF_REDUCTION

DSH_PERCENTAGE_OF_REDUCTION

FSH_PERCENTAGE_OF_REDUCTION

SCOPE_REDUCED_FLAG

PERCENTAGE_CHANGED_FLAG

SCHEDULES_REFRESH_NEEDED_FLAG

COMPLIANCE_CHANGED_FLAG

FINAL_LEASE_DETAIL_ID

CLOSED_FLAG

OPTION_FLAG

PAYMENT_STATUS_CODE

OPTION_STATUS_CODE

ASSET_STATUS_CODE

ASSET_TERMINATION_FLAG

LEASE_TERMINATION_FLAG

CONTINGENT_ONLY_IMPACT_FLAG

MIGRATION_SCHEDULE_FLAG

MIGRATED_FINANCE_ROU_AMOUNT

MIGRATED_OPERATING_ROU_AMOUNT

MIGRATION_IMPACT_FLAG

IMPAIRMENT_IMPACT_FLAG

IMPAIRMENT_INPROGRESS_FLAG

ASSET_IMPAIRED_FLAG

VENDOR_SITE_ID

CURRENCY_CODE

UNITS_CONSUMED

UNIT_OF_MEASURE

DV_SCOPE_CHANGE_BASIS_CODE

UNITS_CHANGE_DISCARDED_FLAG

Query

SQL_Statement
SELECT /+ PUSH_PRED(pay) PUSH_PRED(dsh) PUSH_PRED(fsh)/ pay.lease_id, pay.lease_detail_id, pay.version_type_code, pay.version_num, pay.approval_status_code, pay.transaction_type_code, pay.transaction_subtype_code, pay.payment_header_id, pay.payment_number, pay.asset_number, pay.change_type_code, pay.changed_in_version_num, pay.schedules_impact_flag, pay.payment_items_impact_flag, pay.liability_flag, pay.rou_asset_flag, pay.regime_code, pay.discount_rate_index_id, pay.dv_lease_classification_code, pay.dv_amort_proration_code, pay.dv_amortization_start_date, pay.dv_amortization_end_date, pay.dv_interest_rate, pay.dv_adder_rate, NVL2(dsh.schedule_header_id, 'Y', 'N') draft_schedules_exists_flag, dsh.schedule_header_id draft_schedule_header_id, dsh.parent_record_id draft_parent_record_id, dsh.dsh_lease_classification_code, dsh.dsh_amortization_start_date, dsh.dsh_amortization_end_date, CASE WHEN pay.dv_amortization_start_date = dsh.dsh_amortization_start_date AND pay.dv_amortization_end_date = dsh.dsh_amortization_end_date THEN 'N' ELSE NVL2(dsh.schedule_header_id, 'Y', NULL) END dv_amort_dates_changed_flag, dsh.dsh_interest_rate, dsh.dsh_adder_rate, CASE WHEN pay.dv_lease_classification_code = 'EXEMPT' THEN 'N' WHEN NVL(pay.dv_interest_rate, 0) = NVL(dsh.dsh_interest_rate, 0) AND NVL(pay.dv_adder_rate, 0) = NVL(dsh.dsh_adder_rate, 0) THEN 'N' ELSE NVL2(dsh.schedule_header_id, 'Y', NULL) END dv_interest_changed_flag, dsh.dsh_amort_proration_code, CASE WHEN dsh.schedule_header_id IS NULL THEN NULL WHEN NVL(pay.dv_amort_proration_code, '~') = NVL(dsh.dsh_amort_proration_code, '~') THEN 'N' ELSE 'Y' END dv_proration_code_changed_flag, NVL2(fsh.schedule_header_id, 'Y', 'N') final_schedules_exists_flag, fsh.schedule_header_id final_schedule_header_id, fsh.fsh_lease_classification_code, fsh.fsh_amortization_start_date, fsh.fsh_amortization_end_date, CASE WHEN pay.dv_amortization_end_date = fsh.fsh_amortization_end_date THEN 'N' ELSE NVL2(fsh.schedule_header_id, 'Y', NULL) END amend_amort_dates_changed_flag, CASE WHEN pay.dv_amortization_end_date > fsh.fsh_amortization_end_date THEN 'EXPAND' WHEN pay.dv_amortization_end_date < fsh.fsh_amortization_end_date THEN 'CONTRACT' WHEN pay.dv_amortization_end_date = fsh.fsh_amortization_end_date THEN 'NOCHANGE' ELSE NULL END amend_amort_dates_code, fsh.fsh_interest_rate, fsh.fsh_adder_rate, CASE WHEN pay.dv_lease_classification_code = 'EXEMPT' THEN 'N' WHEN NVL(pay.dv_interest_rate, 0) = NVL(fsh.fsh_interest_rate, 0) AND NVL(pay.dv_adder_rate, 0) = NVL(fsh.fsh_adder_rate, 0) THEN 'N' ELSE NVL2(fsh.schedule_header_id, 'Y', NULL) END amend_interest_changed_flag, fsh.fsh_amort_proration_code, DECODE(pay.dv_lease_classification_code, 'EXEMPT', 0, pay.dv_percentage_of_reduction) dv_percentage_of_reduction, dsh.dsh_percentage_of_reduction, fsh.fsh_percentage_of_reduction, CASE WHEN (pay.rou_asset_flag = 'Y' OR pay.liability_flag = 'Y') AND pay.dv_percentage_of_reduction > 0 THEN 'Y' ELSE 'N' END scope_reduced_flag, CASE WHEN pay.dv_lease_classification_code = 'EXEMPT' THEN 'N' WHEN NVL(pay.dv_percentage_of_reduction,0) = NVL(dsh.dsh_percentage_of_reduction,0) THEN 'N' ELSE 'Y' END percentage_changed_flag, pay.schedules_refresh_needed_flag, CASE WHEN fsh.schedule_header_id IS NULL AND pay.dv_lease_classification_code <> NVL(dsh.dsh_lease_classification_code, pay.dv_lease_classification_code) THEN 'Y' WHEN pay.dv_lease_classification_code = 'EXEMPT' THEN 'N' WHEN pay.rou_asset_flag = 'Y' AND fsh.schedule_header_id IS NULL AND (dsh.dsh_forecasted_fin_rou_amount = 0 AND dsh.dsh_forecasted_oper_rou_amount = 0) THEN 'Y' WHEN pay.rou_asset_flag = 'N' AND fsh.schedule_header_id IS NULL AND (dsh.dsh_forecasted_fin_rou_amount <> 0 OR dsh.dsh_forecasted_oper_rou_amount <> 0) THEN 'Y' WHEN pay.liability_flag = 'Y' AND fsh.schedule_header_id IS NULL AND dsh.dsh_forecasted_liability_amt = 0 THEN 'Y' WHEN pay.liability_flag = 'N' AND fsh.schedule_header_id IS NULL AND dsh.dsh_forecasted_liability_amt <> 0 THEN 'Y' ELSE 'N' END compliance_changed_flag, fsh.final_lease_detail_id, pay.closed_flag closed_flag, pay.option_flag, pay.payment_status_code, pay.option_status_code, pay.asset_status_code, pay.asset_termination_flag, pay.lease_termination_flag, pay.contingent_only_impact_flag, pay.migration_schedule_flag, pay.migrated_finance_rou_amount, pay.migrated_operating_rou_amount, CASE WHEN pay.migration_schedule_flag = 'Y' AND (NVL(pay.migrated_finance_rou_amount, 0) <> NVL(dsh.dsh_forecasted_fin_rou_amount, 0) OR NVL(pay.migrated_operating_rou_amount, 0) <> NVL(dsh.dsh_forecasted_oper_rou_amount, 0)) THEN 'Y' ELSE 'N' END migration_impact_flag, DECODE(pay.dv_lease_classification_code, 'EXEMPT', 'N', pay.impairment_impact_flag) impairment_impact_flag, pay.impairment_inprogress_flag, pay.asset_impaired_flag, pay.vendor_site_id, pay.currency_code, pay.units_consumed, pay.unit_of_measure, CASE WHEN fsh.schedule_header_id IS NULL OR pay.dv_lease_classification_code = 'EXEMPT' THEN TO_CHAR(NULL) WHEN pay.units_decreased_flag = 'N' OR pay.closed_flag <> 'N' THEN TO_CHAR(NULL) WHEN dsh.scope_change_basis_code IS NULL OR dsh.scope_change_basis_code <> pay.scope_change_basis_code THEN pay.scope_change_basis_code WHEN dsh.units_consumed IS NULL OR dsh.units_consumed <> pay.units_consumed THEN pay.scope_change_basis_code ELSE TO_CHAR(NULL) END dv_scope_change_basis_code, CASE WHEN pay.units_decreased_flag = 'N' AND dsh.scope_change_basis_code IS NOT NULL THEN 'Y' ELSE 'N' END units_change_discarded_flag FROM ( SELECT ph.lease_id, ph.lease_detail_id, ld.version_type_code, ld.version_num, ld.approval_status_code, CASE WHEN NVL(ph.closed_flag,'N') <> 'N' THEN 'TERMINATION' WHEN NVL(ld.transaction_type_code, 'BOOKING') = 'BOOKING' THEN 'BOOKING' WHEN ld.transaction_type_code = 'IMPAIRMENT' THEN 'IMPAIRMENT' ELSE 'REVISION' END transaction_type_code, ld.transaction_subtype_code transaction_subtype_code, ph.payment_header_id, ph.payment_number, ph.asset_number, ph.change_type_code, ph.changed_in_version_num, ph.schedules_impact_flag, NVL(ph.payment_items_impact_flag, 'Y') payment_items_impact_flag, CASE WHEN r.primary_flag = 'Y' AND ld.lease_classification_code = 'EXEMPT' THEN 'N' WHEN r.primary_flag = 'N' AND NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) = 'EXEMPT' THEN 'N' ELSE ph.liability_flag END liability_flag, CASE WHEN r.primary_flag = 'Y' AND ld.lease_classification_code = 'EXEMPT' THEN 'N' WHEN r.primary_flag = 'N' AND NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) = 'EXEMPT' THEN 'N' ELSE ph.rou_asset_flag END rou_asset_flag, CASE WHEN ph.liability_flag = 'N' AND ph.rou_asset_flag = 'N' THEN ph.amortize_exempt_payment_flag WHEN r.primary_flag = 'Y' AND ld.lease_classification_code = 'EXEMPT' THEN amortize_exempt_payment_flag WHEN r.primary_flag = 'N' AND NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) = 'EXEMPT' THEN amortize_exempt_payment_flag ELSE 'N' END amortize_exempt_payment_flag, r.regime_code regime_code, DECODE(r.primary_flag, 'Y', NVL(ph.prim_regime_disc_rate_index_id,ld.prim_regime_disc_rate_index_id), NVL(ph.secd_regime_disc_rate_index_id,ld.secd_regime_disc_rate_index_id)) discount_rate_index_id, CASE WHEN ph.amortize_exempt_payment_flag = 'N' AND ph.liability_flag = 'N' AND ph.rou_asset_flag = 'N' THEN 'NONE' WHEN ph.amortize_exempt_payment_flag = 'N' AND r.primary_flag = 'Y' AND ld.lease_classification_code = 'EXEMPT' THEN 'NONE' WHEN ph.amortize_exempt_payment_flag = 'N' AND r.primary_flag = 'N' AND NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) = 'EXEMPT' THEN 'NONE' WHEN ph.amortize_exempt_payment_flag = 'Y' AND ph.liability_flag = 'N' AND ph.rou_asset_flag = 'N' THEN 'EXEMPT' WHEN r.primary_flag = 'Y' THEN ld.lease_classification_code ELSE NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) END dv_lease_classification_code, la.amortization_proration_code dv_amort_proration_code, NVL(DECODE(ld.transaction_type_code, 'IMPAIRMENT', ld.commencement_date + 1, ld.commencement_date), la.amortization_start_date) dv_amortization_start_date, DECODE(ph.closed_flag,'I', NVL(ld.commencement_date, la.amortization_start_date), NVL(pas.amortization_end_date,ld.amortization_end_date)) dv_amortization_end_date, NVL(DECODE(r.primary_flag, 'Y', NVL(ph.prim_regime_interest_rate,ld.prim_regime_interest_rate), NVL(ph.secd_regime_interest_rate,ld.secd_regime_interest_rate)), 0) dv_interest_rate, NVL(DECODE(r.primary_flag, 'Y', NVL(ph.prim_regime_adder_rate,ld.prim_regime_adder_rate), NVL(ph.secd_regime_adder_rate,ld.secd_regime_adder_rate)), 0) dv_adder_rate, NVL(ph.percentage_of_reduction, DECODE(ph.asset_number, NULL, ld.percentage_of_reduction, pas.percentage_of_reduction)) dv_percentage_of_reduction, ld.schedules_refresh_needed_flag, NVL(ph.closed_flag, 'N') closed_flag, ph.option_flag, ph.payment_status_code, ph.option_status_code, pas.asset_status_code, CASE WHEN pas.asset_status_code IN ('TER_IN_PROGRESS', 'EXP_IN_PROGRESS', 'ACQ_IN_PROGRESS') THEN 'Y' ELSE 'N' END asset_termination_flag, NVL(ld.lease_termination_flag,'N') lease_termination_flag, NVL(ph.contingent_only_impact_flag,'N') contingent_only_impact_flag, ph.impairment_impact_flag, pas.impairment_inprogress_flag, pas.asset_impaired_flag, CASE WHEN ph.migrated_flag = 'Y' AND ph.rou_asset_flag = 'Y' AND (NVL(ph.finance_rou_balance_amount, 0) <> 0 OR NVL(ph.operating_rou_balance_amount, 0) <> 0) AND NVL(ld.transaction_type_code, 'BOOKING') = 'BOOKING' THEN 'Y' ELSE 'N' END migration_schedule_flag, CASE WHEN r.primary_flag = 'Y' AND r.regime_code IN ('IFRS16', 'JGAAP') THEN NVL(ph.finance_rou_balance_amount, ph.operating_rou_balance_amount) WHEN r.primary_flag = 'Y' AND r.regime_code = 'ASC842' THEN ph.finance_rou_balance_amount WHEN r.primary_flag = 'N' AND r.regime_code IN ('IFRS16', 'JGAAP') THEN NVL(ph.finance_rou_balance_amount, ph.operating_rou_balance_amount) WHEN r.primary_flag = 'N' AND r.regime_code = 'ASC842' THEN ph.finance_rou_balance_amount ELSE NULL END migrated_finance_rou_amount, CASE WHEN r.primary_flag = 'Y' AND r.regime_code IN ('IFRS16', 'JGAAP') THEN NVL(ph.finance_rou_balance_amount, ph.operating_rou_balance_amount) WHEN r.primary_flag = 'Y' AND r.regime_code = 'ASC842' THEN ph.operating_rou_balance_amount WHEN r.primary_flag = 'N' AND r.regime_code IN ('IFRS16', 'JGAAP') THEN NVL(ph.finance_rou_balance_amount, ph.operating_rou_balance_amount) WHEN r.primary_flag = 'N' AND r.regime_code = 'ASC842' THEN ph.operating_rou_balance_amount ELSE NULL END migrated_operating_rou_amount, ph.currency_code, ph.vendor_site_id, pas.units_consumed, pas.unit_of_measure, CASE WHEN fas.asset_number IS NULL OR fas.units_consumed IS NULL OR pas.units_consumed IS NULL THEN 'N' WHEN NVL(ld.transaction_type_code, '~') <> 'REVISION' THEN 'N' WHEN NVL(ld.transaction_subtype_code, '~') <> 'SCOPE_DECREASE' THEN 'N' WHEN pas.units_consumed < fas.units_consumed THEN 'Y' ELSE 'N' END units_decreased_flag, CASE WHEN r.primary_flag = 'Y' AND la.lease_asset_type_code = 'PROPERTY' THEN NVL(ld.prim_regime_scope_change_code, r.prop_scope_change_code) WHEN r.primary_flag = 'Y' AND la.lease_asset_type_code = 'EQUIPMENT' THEN NVL(ld.prim_regime_scope_change_code, r.equip_scope_change_code) WHEN r.primary_flag = 'N' AND la.lease_asset_type_code = 'PROPERTY' THEN NVL(ld.secd_regime_scope_change_code, r.prop_scope_change_code) WHEN r.primary_flag = 'N' AND la.lease_asset_type_code = 'EQUIPMENT' THEN NVL(ld.secd_regime_scope_change_code, r.equip_scope_change_code) ELSE TO_CHAR(NULL) END scope_change_basis_code FROM fla_leases_all la, fla_regime_details_v r, fla_lease_details ld, fla_lease_payment_headers ph, fla_lease_assets pas, fla_lease_assets fas WHERE la.org_id = r.org_id AND la.lease_id = ld.lease_id AND ld.version_type_code = 'D' AND ld.approval_status_code != 'FINALIZED' AND ld.lease_detail_id = ph.lease_detail_id AND ld.lease_id = ph.lease_id AND ph.lease_detail_id = pas.lease_detail_id(+) AND ph.asset_number = pas.asset_number(+) AND ph.schedule_type_code = 'PAYMENT' AND pas.lease_id = fas.lease_id(+) AND pas.asset_number = fas.asset_number(+) AND pas.lease_asset_id = fas.parent_record_id(+) ) pay, ( SELECT sh.lease_id, sh.schedule_header_id, sh.parent_record_id, sh.payment_number, sh.regime_code, sh.amortization_start_date dsh_amortization_start_date, sh.amortization_end_date dsh_amortization_end_date, sh.interest_rate dsh_interest_rate, sh.adder_rate dsh_adder_rate, sh.lease_classification_code dsh_lease_classification_code, sh.amortization_proration_code dsh_amort_proration_code, sh.percentage_of_reduction dsh_percentage_of_reduction, sh.forecasted_payment_amount dsh_forecasted_payment_amount, sh.forecasted_liability_amount dsh_forecasted_liability_amt, sh.forecasted_fin_rou_amount dsh_forecasted_fin_rou_amount, sh.forecasted_oper_rou_amount dsh_forecasted_oper_rou_amount, sh.scope_change_basis_code scope_change_basis_code, sh.units_consumed units_consumed, sh.unit_of_measure_code unit_of_measure_code FROM fla_schedule_headers sh WHERE sh.active_flag = 'Y' AND EXISTS( SELECT 1 FROM fla_lease_details WHERE lease_detail_id = sh.lease_detail_id AND version_type_code = 'D') ) dsh, ( SELECT sh.lease_id, sh.lease_detail_id final_lease_detail_id, sh.schedule_header_id, sh.payment_number, sh.regime_code, sh.amortization_start_date fsh_amortization_start_date, sh.amortization_end_date fsh_amortization_end_date, sh.interest_rate fsh_interest_rate, sh.adder_rate fsh_adder_rate, sh.lease_classification_code fsh_lease_classification_code, sh.amortization_proration_code fsh_amort_proration_code, sh.percentage_of_reduction fsh_percentage_of_reduction, sh.forecasted_payment_amount fsh_forecasted_payment_amount, sh.forecasted_liability_amount fsh_forecasted_liability_amt, sh.forecasted_fin_rou_amount fsh_forecasted_fin_rou_amount, sh.forecasted_oper_rou_amount fsh_forecasted_oper_rou_amount FROM fla_schedule_headers sh WHERE sh.active_flag = 'Y' AND EXISTS( SELECT 1 FROM fla_lease_details WHERE lease_detail_id = sh.lease_detail_id AND version_type_code = 'F') ) fsh WHERE pay.payment_number = dsh.payment_number(+) AND pay.regime_code = dsh.regime_code(+) AND pay.lease_id = dsh.lease_id(+) AND pay.payment_number = fsh.payment_number(+) AND pay.regime_code = fsh.regime_code(+) AND pay.lease_id = fsh.lease_id(+)

SQL_Statement

SELECT /*+ PUSH_PRED(pay) PUSH_PRED(dsh) PUSH_PRED(fsh)*/

pay.lease_id,

pay.lease_detail_id,

pay.version_type_code,

pay.version_num,

pay.approval_status_code,

pay.transaction_type_code,

pay.transaction_subtype_code,

pay.payment_header_id,

pay.payment_number,

pay.asset_number,

pay.change_type_code,

pay.changed_in_version_num,

pay.schedules_impact_flag,

pay.payment_items_impact_flag,

pay.liability_flag,

pay.rou_asset_flag,

pay.regime_code,

pay.discount_rate_index_id,

pay.dv_lease_classification_code,

pay.dv_amort_proration_code,

pay.dv_amortization_start_date,

pay.dv_amortization_end_date,

pay.dv_interest_rate,

pay.dv_adder_rate,

NVL2(dsh.schedule_header_id, 'Y', 'N') draft_schedules_exists_flag,

dsh.schedule_header_id draft_schedule_header_id,

dsh.parent_record_id draft_parent_record_id,

dsh.dsh_lease_classification_code,

dsh.dsh_amortization_start_date,

dsh.dsh_amortization_end_date,

CASE

WHEN pay.dv_amortization_start_date = dsh.dsh_amortization_start_date AND pay.dv_amortization_end_date = dsh.dsh_amortization_end_date THEN 'N'

ELSE NVL2(dsh.schedule_header_id, 'Y', NULL)

END dv_amort_dates_changed_flag,

dsh.dsh_interest_rate,

dsh.dsh_adder_rate,

CASE

WHEN pay.dv_lease_classification_code = 'EXEMPT' THEN 'N'

WHEN NVL(pay.dv_interest_rate, 0) = NVL(dsh.dsh_interest_rate, 0) AND NVL(pay.dv_adder_rate, 0) = NVL(dsh.dsh_adder_rate, 0) THEN 'N'

ELSE NVL2(dsh.schedule_header_id, 'Y', NULL)

END dv_interest_changed_flag,

dsh.dsh_amort_proration_code,

CASE

WHEN dsh.schedule_header_id IS NULL THEN NULL

WHEN NVL(pay.dv_amort_proration_code, '~') = NVL(dsh.dsh_amort_proration_code, '~') THEN 'N'

ELSE 'Y'

END dv_proration_code_changed_flag,

NVL2(fsh.schedule_header_id, 'Y', 'N') final_schedules_exists_flag,

fsh.schedule_header_id final_schedule_header_id,

fsh.fsh_lease_classification_code,

fsh.fsh_amortization_start_date,

fsh.fsh_amortization_end_date,

CASE

WHEN pay.dv_amortization_end_date = fsh.fsh_amortization_end_date THEN 'N'

ELSE NVL2(fsh.schedule_header_id, 'Y', NULL)

END amend_amort_dates_changed_flag,

CASE

WHEN pay.dv_amortization_end_date > fsh.fsh_amortization_end_date THEN 'EXPAND'

WHEN pay.dv_amortization_end_date < fsh.fsh_amortization_end_date THEN 'CONTRACT'

WHEN pay.dv_amortization_end_date = fsh.fsh_amortization_end_date THEN 'NOCHANGE'

ELSE NULL

END amend_amort_dates_code,

fsh.fsh_interest_rate,

fsh.fsh_adder_rate,

CASE

WHEN pay.dv_lease_classification_code = 'EXEMPT' THEN 'N'

WHEN NVL(pay.dv_interest_rate, 0) = NVL(fsh.fsh_interest_rate, 0) AND NVL(pay.dv_adder_rate, 0) = NVL(fsh.fsh_adder_rate, 0) THEN 'N'

ELSE NVL2(fsh.schedule_header_id, 'Y', NULL)

END amend_interest_changed_flag,

fsh.fsh_amort_proration_code,

DECODE(pay.dv_lease_classification_code, 'EXEMPT', 0, pay.dv_percentage_of_reduction) dv_percentage_of_reduction,

dsh.dsh_percentage_of_reduction,

fsh.fsh_percentage_of_reduction,

CASE

WHEN (pay.rou_asset_flag = 'Y' OR pay.liability_flag = 'Y') AND pay.dv_percentage_of_reduction > 0 THEN 'Y'

ELSE 'N'

END scope_reduced_flag,

CASE

WHEN pay.dv_lease_classification_code = 'EXEMPT' THEN 'N'

WHEN NVL(pay.dv_percentage_of_reduction,0) = NVL(dsh.dsh_percentage_of_reduction,0) THEN 'N'

ELSE 'Y'

END percentage_changed_flag,

pay.schedules_refresh_needed_flag,

CASE

WHEN fsh.schedule_header_id IS NULL AND pay.dv_lease_classification_code <> NVL(dsh.dsh_lease_classification_code, pay.dv_lease_classification_code) THEN 'Y'

WHEN pay.dv_lease_classification_code = 'EXEMPT' THEN 'N'

WHEN pay.rou_asset_flag = 'Y' AND fsh.schedule_header_id IS NULL AND (dsh.dsh_forecasted_fin_rou_amount = 0 AND dsh.dsh_forecasted_oper_rou_amount = 0) THEN 'Y'

WHEN pay.rou_asset_flag = 'N' AND fsh.schedule_header_id IS NULL AND (dsh.dsh_forecasted_fin_rou_amount <> 0 OR dsh.dsh_forecasted_oper_rou_amount <> 0) THEN 'Y'

WHEN pay.liability_flag = 'Y' AND fsh.schedule_header_id IS NULL AND dsh.dsh_forecasted_liability_amt = 0 THEN 'Y'

WHEN pay.liability_flag = 'N' AND fsh.schedule_header_id IS NULL AND dsh.dsh_forecasted_liability_amt <> 0 THEN 'Y'

ELSE 'N'

END compliance_changed_flag,

fsh.final_lease_detail_id,

pay.closed_flag closed_flag,

pay.option_flag,

pay.payment_status_code,

pay.option_status_code,

pay.asset_status_code,

pay.asset_termination_flag,

pay.lease_termination_flag,

pay.contingent_only_impact_flag,

pay.migration_schedule_flag,

pay.migrated_finance_rou_amount,

pay.migrated_operating_rou_amount,

CASE

WHEN pay.migration_schedule_flag = 'Y'

AND (NVL(pay.migrated_finance_rou_amount, 0) <> NVL(dsh.dsh_forecasted_fin_rou_amount, 0)

OR NVL(pay.migrated_operating_rou_amount, 0) <> NVL(dsh.dsh_forecasted_oper_rou_amount, 0))

THEN 'Y'

ELSE 'N'

END migration_impact_flag,

DECODE(pay.dv_lease_classification_code, 'EXEMPT', 'N', pay.impairment_impact_flag) impairment_impact_flag,

pay.impairment_inprogress_flag,

pay.asset_impaired_flag,

pay.vendor_site_id,

pay.currency_code,

pay.units_consumed,

pay.unit_of_measure,

CASE

WHEN fsh.schedule_header_id IS NULL OR pay.dv_lease_classification_code = 'EXEMPT' THEN TO_CHAR(NULL)

WHEN pay.units_decreased_flag = 'N' OR pay.closed_flag <> 'N' THEN TO_CHAR(NULL)

WHEN dsh.scope_change_basis_code IS NULL OR dsh.scope_change_basis_code <> pay.scope_change_basis_code THEN pay.scope_change_basis_code

WHEN dsh.units_consumed IS NULL OR dsh.units_consumed <> pay.units_consumed THEN pay.scope_change_basis_code

ELSE TO_CHAR(NULL)

END dv_scope_change_basis_code,

CASE WHEN pay.units_decreased_flag = 'N' AND dsh.scope_change_basis_code IS NOT NULL THEN 'Y' ELSE 'N' END units_change_discarded_flag

FROM (

SELECT ph.lease_id,

ph.lease_detail_id,

ld.version_type_code,

ld.version_num,

ld.approval_status_code,

CASE

WHEN NVL(ph.closed_flag,'N') <> 'N' THEN 'TERMINATION'

WHEN NVL(ld.transaction_type_code, 'BOOKING') = 'BOOKING' THEN 'BOOKING'

WHEN ld.transaction_type_code = 'IMPAIRMENT' THEN 'IMPAIRMENT'

ELSE 'REVISION'

END transaction_type_code,

ld.transaction_subtype_code transaction_subtype_code,

ph.payment_header_id,

ph.payment_number,

ph.asset_number,

ph.change_type_code,

ph.changed_in_version_num,

ph.schedules_impact_flag,

NVL(ph.payment_items_impact_flag, 'Y') payment_items_impact_flag,

CASE

WHEN r.primary_flag = 'Y' AND ld.lease_classification_code = 'EXEMPT' THEN 'N'

WHEN r.primary_flag = 'N' AND NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) = 'EXEMPT' THEN 'N'

ELSE ph.liability_flag

END liability_flag,

CASE

WHEN r.primary_flag = 'Y' AND ld.lease_classification_code = 'EXEMPT' THEN 'N'

WHEN r.primary_flag = 'N' AND NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) = 'EXEMPT' THEN 'N'

ELSE ph.rou_asset_flag

END rou_asset_flag,

CASE

WHEN ph.liability_flag = 'N' AND ph.rou_asset_flag = 'N' THEN ph.amortize_exempt_payment_flag

WHEN r.primary_flag = 'Y' AND ld.lease_classification_code = 'EXEMPT' THEN amortize_exempt_payment_flag

WHEN r.primary_flag = 'N' AND NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) = 'EXEMPT' THEN amortize_exempt_payment_flag

ELSE 'N'

END amortize_exempt_payment_flag,

r.regime_code regime_code,

DECODE(r.primary_flag, 'Y', NVL(ph.prim_regime_disc_rate_index_id,ld.prim_regime_disc_rate_index_id), NVL(ph.secd_regime_disc_rate_index_id,ld.secd_regime_disc_rate_index_id)) discount_rate_index_id,

CASE

WHEN ph.amortize_exempt_payment_flag = 'N' AND ph.liability_flag = 'N' AND ph.rou_asset_flag = 'N' THEN 'NONE'

WHEN ph.amortize_exempt_payment_flag = 'N' AND r.primary_flag = 'Y' AND ld.lease_classification_code = 'EXEMPT' THEN 'NONE'

WHEN ph.amortize_exempt_payment_flag = 'N' AND r.primary_flag = 'N' AND NVL(ld.lease_secondary_classif_code, ld.lease_classification_code) = 'EXEMPT' THEN 'NONE'

WHEN ph.amortize_exempt_payment_flag = 'Y' AND ph.liability_flag = 'N' AND ph.rou_asset_flag = 'N' THEN 'EXEMPT'

WHEN r.primary_flag = 'Y' THEN ld.lease_classification_code

ELSE NVL(ld.lease_secondary_classif_code, ld.lease_classification_code)

END dv_lease_classification_code,

la.amortization_proration_code dv_amort_proration_code,

NVL(DECODE(ld.transaction_type_code, 'IMPAIRMENT', ld.commencement_date + 1, ld.commencement_date), la.amortization_start_date) dv_amortization_start_date,

DECODE(ph.closed_flag,'I', NVL(ld.commencement_date, la.amortization_start_date), NVL(pas.amortization_end_date,ld.amortization_end_date)) dv_amortization_end_date,

NVL(DECODE(r.primary_flag, 'Y', NVL(ph.prim_regime_interest_rate,ld.prim_regime_interest_rate), NVL(ph.secd_regime_interest_rate,ld.secd_regime_interest_rate)), 0) dv_interest_rate,

NVL(DECODE(r.primary_flag, 'Y', NVL(ph.prim_regime_adder_rate,ld.prim_regime_adder_rate), NVL(ph.secd_regime_adder_rate,ld.secd_regime_adder_rate)), 0) dv_adder_rate,

NVL(ph.percentage_of_reduction, DECODE(ph.asset_number, NULL, ld.percentage_of_reduction, pas.percentage_of_reduction)) dv_percentage_of_reduction,

ld.schedules_refresh_needed_flag,

NVL(ph.closed_flag, 'N') closed_flag,

ph.option_flag,

ph.payment_status_code,

ph.option_status_code,

pas.asset_status_code,

CASE WHEN pas.asset_status_code IN ('TER_IN_PROGRESS', 'EXP_IN_PROGRESS', 'ACQ_IN_PROGRESS') THEN 'Y' ELSE 'N' END asset_termination_flag,

NVL(ld.lease_termination_flag,'N') lease_termination_flag,

NVL(ph.contingent_only_impact_flag,'N') contingent_only_impact_flag,

ph.impairment_impact_flag,

pas.impairment_inprogress_flag,

pas.asset_impaired_flag,

CASE

WHEN ph.migrated_flag = 'Y' AND ph.rou_asset_flag = 'Y'

AND (NVL(ph.finance_rou_balance_amount, 0) <> 0 OR NVL(ph.operating_rou_balance_amount, 0) <> 0)

AND NVL(ld.transaction_type_code, 'BOOKING') = 'BOOKING'

THEN 'Y'

ELSE 'N'

END migration_schedule_flag,

CASE

WHEN r.primary_flag = 'Y' AND r.regime_code IN ('IFRS16', 'JGAAP') THEN NVL(ph.finance_rou_balance_amount, ph.operating_rou_balance_amount)

WHEN r.primary_flag = 'Y' AND r.regime_code = 'ASC842' THEN ph.finance_rou_balance_amount

WHEN r.primary_flag = 'N' AND r.regime_code IN ('IFRS16', 'JGAAP') THEN NVL(ph.finance_rou_balance_amount, ph.operating_rou_balance_amount)

WHEN r.primary_flag = 'N' AND r.regime_code = 'ASC842' THEN ph.finance_rou_balance_amount

ELSE NULL

END migrated_finance_rou_amount,

CASE

WHEN r.primary_flag = 'Y' AND r.regime_code IN ('IFRS16', 'JGAAP') THEN NVL(ph.finance_rou_balance_amount, ph.operating_rou_balance_amount)

WHEN r.primary_flag = 'Y' AND r.regime_code = 'ASC842' THEN ph.operating_rou_balance_amount

WHEN r.primary_flag = 'N' AND r.regime_code IN ('IFRS16', 'JGAAP') THEN NVL(ph.finance_rou_balance_amount, ph.operating_rou_balance_amount)

WHEN r.primary_flag = 'N' AND r.regime_code = 'ASC842' THEN ph.operating_rou_balance_amount

ELSE NULL

END migrated_operating_rou_amount,

ph.currency_code,

ph.vendor_site_id,

pas.units_consumed,

pas.unit_of_measure,

CASE

WHEN fas.asset_number IS NULL OR fas.units_consumed IS NULL OR pas.units_consumed IS NULL THEN 'N'

WHEN NVL(ld.transaction_type_code, '~') <> 'REVISION' THEN 'N'

WHEN NVL(ld.transaction_subtype_code, '~') <> 'SCOPE_DECREASE' THEN 'N'

WHEN pas.units_consumed < fas.units_consumed THEN 'Y'

ELSE 'N'

END units_decreased_flag,

CASE

WHEN r.primary_flag = 'Y' AND la.lease_asset_type_code = 'PROPERTY' THEN NVL(ld.prim_regime_scope_change_code, r.prop_scope_change_code)

WHEN r.primary_flag = 'Y' AND la.lease_asset_type_code = 'EQUIPMENT' THEN NVL(ld.prim_regime_scope_change_code, r.equip_scope_change_code)

WHEN r.primary_flag = 'N' AND la.lease_asset_type_code = 'PROPERTY' THEN NVL(ld.secd_regime_scope_change_code, r.prop_scope_change_code)

WHEN r.primary_flag = 'N' AND la.lease_asset_type_code = 'EQUIPMENT' THEN NVL(ld.secd_regime_scope_change_code, r.equip_scope_change_code)

ELSE TO_CHAR(NULL)

END scope_change_basis_code

FROM fla_leases_all la,

fla_regime_details_v r,

fla_lease_details ld,

fla_lease_payment_headers ph,

fla_lease_assets pas,

fla_lease_assets fas

WHERE la.org_id = r.org_id

AND la.lease_id = ld.lease_id

AND ld.version_type_code = 'D'

AND ld.approval_status_code != 'FINALIZED'

AND ld.lease_detail_id = ph.lease_detail_id

AND ld.lease_id = ph.lease_id

AND ph.lease_detail_id = pas.lease_detail_id(+)

AND ph.asset_number = pas.asset_number(+)

AND ph.schedule_type_code = 'PAYMENT'

AND pas.lease_id = fas.lease_id(+)

AND pas.asset_number = fas.asset_number(+)

AND pas.lease_asset_id = fas.parent_record_id(+)

) pay,

(

SELECT sh.lease_id,

sh.schedule_header_id,

sh.parent_record_id,

sh.payment_number,

sh.regime_code,

sh.amortization_start_date dsh_amortization_start_date,

sh.amortization_end_date dsh_amortization_end_date,

sh.interest_rate dsh_interest_rate,

sh.adder_rate dsh_adder_rate,

sh.lease_classification_code dsh_lease_classification_code,

sh.amortization_proration_code dsh_amort_proration_code,

sh.percentage_of_reduction dsh_percentage_of_reduction,

sh.forecasted_payment_amount dsh_forecasted_payment_amount,

sh.forecasted_liability_amount dsh_forecasted_liability_amt,

sh.forecasted_fin_rou_amount dsh_forecasted_fin_rou_amount,

sh.forecasted_oper_rou_amount dsh_forecasted_oper_rou_amount,

sh.scope_change_basis_code scope_change_basis_code,

sh.units_consumed units_consumed,

sh.unit_of_measure_code unit_of_measure_code

FROM fla_schedule_headers sh

WHERE sh.active_flag = 'Y'

AND EXISTS(

SELECT 1

FROM fla_lease_details

WHERE lease_detail_id = sh.lease_detail_id

AND version_type_code = 'D')

) dsh,

(

SELECT sh.lease_id,

sh.lease_detail_id final_lease_detail_id,

sh.schedule_header_id,

sh.payment_number,

sh.regime_code,

sh.amortization_start_date fsh_amortization_start_date,

sh.amortization_end_date fsh_amortization_end_date,

sh.interest_rate fsh_interest_rate,

sh.adder_rate fsh_adder_rate,

sh.lease_classification_code fsh_lease_classification_code,

sh.amortization_proration_code fsh_amort_proration_code,

sh.percentage_of_reduction fsh_percentage_of_reduction,

sh.forecasted_payment_amount fsh_forecasted_payment_amount,

sh.forecasted_liability_amount fsh_forecasted_liability_amt,

sh.forecasted_fin_rou_amount fsh_forecasted_fin_rou_amount,

sh.forecasted_oper_rou_amount fsh_forecasted_oper_rou_amount

FROM fla_schedule_headers sh

WHERE sh.active_flag = 'Y'

AND EXISTS(

SELECT 1

FROM fla_lease_details

WHERE lease_detail_id = sh.lease_detail_id

AND version_type_code = 'F')

) fsh

WHERE pay.payment_number = dsh.payment_number(+)

AND pay.regime_code = dsh.regime_code(+)

AND pay.lease_id = dsh.lease_id(+)

AND pay.payment_number = fsh.payment_number(+)

AND pay.regime_code = fsh.regime_code(+)

AND pay.lease_id = fsh.lease_id(+)