FLA_SCHEDULE_SUMMARY

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID VERSION_NUM PAYMENT_NUMBER SCHEDULE_TYPE_CODE REVENUE_METHOD_CODE CURRENCY_CODE LIABILITY_FLAG ROU_ASSET_FLAG PERIOD_NAME SCHEDULE_DATE SCHEDULE_START_DATE SCHEDULE_END_DATE SCHEDULE_DAYS IFRS_ADJUSTED_LIABILITY_AMOUNT IFRS_OPENING_LIABILITY_AMOUNT IFRS_PAYMENT_AMOUNT IFRS_INTEREST_AMOUNT IFRS_CLOSING_LIABILITY_AMOUNT IFRS_PRESENT_VALUE IFRS_INCREASE_AMOUNT IFRS_ADJ_FIN_ROU_AMOUNT IFRS_OPENING_ROU_AMOUNT IFRS_AMORTIZATION IFRS_CLOSING_ROU_AMOUNT IFRS_ADJ_OPER_ROU_AMOUNT IFRS_OPENING_OPER_ROU_AMOUNT IFRS_OPER_AMORTIZATION IFRS_CLOSING_OPER_ROU_AMOUNT IFRS_ACCRUED_INTEREST_AMOUNT IFRS_OPENING_STL_AMOUNT IFRS_RECLASS_STL_AMOUNT IFRS_CLOSING_STL_AMOUNT IFRS_OPENING_LTL_AMOUNT IFRS_RECLASS_LTL_AMOUNT IFRS_CLOSING_LTL_AMOUNT IFRS_OPER_LEASE_REVENUE IFRS_OPER_NON_LEASE_REVENUE IFRS_OPER_UER_AMOUNT IFRS_ACCRUED_FLAG ASC_ADJUSTED_LIABILITY_AMOUNT ASC_OPENING_LIABILITY_AMOUNT ASC_PAYMENT_AMOUNT ASC_INTEREST_AMOUNT ASC_CLOSING_LIABILITY_AMOUNT ASC_PRESENT_VALUE ASC_INCREASE_AMOUNT ASC_CONTINGENT_AMOUNT ASC_ADJ_FIN_ROU_AMOUNT ASC_OPENING_FIN_ROU_AMOUNT ASC_FIN_AMORTIZATION ASC_CLOSING_FIN_ROU_AMOUNT ASC_ADJ_OPER_ROU_AMOUNT ASC_OPENING_OPER_ROU_AMOUNT ASC_OPER_AMORTIZATION ASC_CLOSING_OPER_ROU_AMOUNT ASC_LEASE_EXPENSE ASC_ACCRUED_INTEREST_AMOUNT ASC_OPENING_STL_AMOUNT ASC_RECLASS_STL_AMOUNT ASC_CLOSING_STL_AMOUNT ASC_OPENING_LTL_AMOUNT ASC_RECLASS_LTL_AMOUNT ASC_CLOSING_LTL_AMOUNT ASC_OPER_LEASE_REVENUE ASC_OPER_NON_LEASE_REVENUE ASC_OPER_UER_AMOUNT ASC_ACCRUED_FLAG INTEREST_RATE ADDER_RATE DAILY_COMPOUNDING_RATE JGAP_ADJUSTED_LIABILITY_AMOUNT JGAP_OPENING_LIABILITY_AMOUNT JGAP_PAYMENT_AMOUNT JGAP_INTEREST_AMOUNT JGAP_CLOSING_LIABILITY_AMOUNT JGAP_PRESENT_VALUE JGAP_INCREASE_AMOUNT JGAP_ADJ_FIN_ROU_AMOUNT JGAP_OPENING_FIN_ROU_AMOUNT JGAP_FIN_AMORTIZATION JGAP_CLOSING_FIN_ROU_AMOUNT JGAP_ACCRUED_INTEREST_AMOUNT JGAP_ADJ_OPER_ROU_AMOUNT JGAP_OPENING_OPER_ROU_AMOUNT JGAP_OPER_AMORTIZATION JGAP_CLOSING_OPER_ROU_AMOUNT JGAP_OPENING_STL_AMOUNT JGAP_RECLASS_STL_AMOUNT JGAP_CLOSING_STL_AMOUNT JGAP_OPENING_LTL_AMOUNT JGAP_RECLASS_LTL_AMOUNT JGAP_CLOSING_LTL_AMOUNT JGAP_OPER_LEASE_REVENUE JGAP_OPER_NON_LEASE_REVENUE JGAP_OPER_UER_AMOUNT JGAP_ACCRUED_FLAG

Name

LEASE_ID

LEASE_DETAIL_ID

VERSION_NUM

PAYMENT_NUMBER

SCHEDULE_TYPE_CODE

REVENUE_METHOD_CODE

CURRENCY_CODE

LIABILITY_FLAG

ROU_ASSET_FLAG

PERIOD_NAME

SCHEDULE_DATE

SCHEDULE_START_DATE

SCHEDULE_END_DATE

SCHEDULE_DAYS

IFRS_ADJUSTED_LIABILITY_AMOUNT

IFRS_OPENING_LIABILITY_AMOUNT

IFRS_PAYMENT_AMOUNT

IFRS_INTEREST_AMOUNT

IFRS_CLOSING_LIABILITY_AMOUNT

IFRS_PRESENT_VALUE

IFRS_INCREASE_AMOUNT

IFRS_ADJ_FIN_ROU_AMOUNT

IFRS_OPENING_ROU_AMOUNT

IFRS_AMORTIZATION

IFRS_CLOSING_ROU_AMOUNT

IFRS_ADJ_OPER_ROU_AMOUNT

IFRS_OPENING_OPER_ROU_AMOUNT

IFRS_OPER_AMORTIZATION

IFRS_CLOSING_OPER_ROU_AMOUNT

IFRS_ACCRUED_INTEREST_AMOUNT

IFRS_OPENING_STL_AMOUNT

IFRS_RECLASS_STL_AMOUNT

IFRS_CLOSING_STL_AMOUNT

IFRS_OPENING_LTL_AMOUNT

IFRS_RECLASS_LTL_AMOUNT

IFRS_CLOSING_LTL_AMOUNT

IFRS_OPER_LEASE_REVENUE

IFRS_OPER_NON_LEASE_REVENUE

IFRS_OPER_UER_AMOUNT

IFRS_ACCRUED_FLAG

ASC_ADJUSTED_LIABILITY_AMOUNT

ASC_OPENING_LIABILITY_AMOUNT

ASC_PAYMENT_AMOUNT

ASC_INTEREST_AMOUNT

ASC_CLOSING_LIABILITY_AMOUNT

ASC_PRESENT_VALUE

ASC_INCREASE_AMOUNT

ASC_CONTINGENT_AMOUNT

ASC_ADJ_FIN_ROU_AMOUNT

ASC_OPENING_FIN_ROU_AMOUNT

ASC_FIN_AMORTIZATION

ASC_CLOSING_FIN_ROU_AMOUNT

ASC_ADJ_OPER_ROU_AMOUNT

ASC_OPENING_OPER_ROU_AMOUNT

ASC_OPER_AMORTIZATION

ASC_CLOSING_OPER_ROU_AMOUNT

ASC_LEASE_EXPENSE

ASC_ACCRUED_INTEREST_AMOUNT

ASC_OPENING_STL_AMOUNT

ASC_RECLASS_STL_AMOUNT

ASC_CLOSING_STL_AMOUNT

ASC_OPENING_LTL_AMOUNT

ASC_RECLASS_LTL_AMOUNT

ASC_CLOSING_LTL_AMOUNT

ASC_OPER_LEASE_REVENUE

ASC_OPER_NON_LEASE_REVENUE

ASC_OPER_UER_AMOUNT

ASC_ACCRUED_FLAG

INTEREST_RATE

ADDER_RATE

DAILY_COMPOUNDING_RATE

JGAP_ADJUSTED_LIABILITY_AMOUNT

JGAP_OPENING_LIABILITY_AMOUNT

JGAP_PAYMENT_AMOUNT

JGAP_INTEREST_AMOUNT

JGAP_CLOSING_LIABILITY_AMOUNT

JGAP_PRESENT_VALUE

JGAP_INCREASE_AMOUNT

JGAP_ADJ_FIN_ROU_AMOUNT

JGAP_OPENING_FIN_ROU_AMOUNT

JGAP_FIN_AMORTIZATION

JGAP_CLOSING_FIN_ROU_AMOUNT

JGAP_ACCRUED_INTEREST_AMOUNT

JGAP_ADJ_OPER_ROU_AMOUNT

JGAP_OPENING_OPER_ROU_AMOUNT

JGAP_OPER_AMORTIZATION

JGAP_CLOSING_OPER_ROU_AMOUNT

JGAP_OPENING_STL_AMOUNT

JGAP_RECLASS_STL_AMOUNT

JGAP_CLOSING_STL_AMOUNT

JGAP_OPENING_LTL_AMOUNT

JGAP_RECLASS_LTL_AMOUNT

JGAP_CLOSING_LTL_AMOUNT

JGAP_OPER_LEASE_REVENUE

JGAP_OPER_NON_LEASE_REVENUE

JGAP_OPER_UER_AMOUNT

JGAP_ACCRUED_FLAG

Query

SQL_Statement
SELECT lease_id, lease_detail_id, version_num, payment_number, schedule_type_code, revenue_method_code, currency_code, liability_flag, rou_asset_flag, period_name, schedule_date, schedule_start_date, schedule_end_date, schedule_days, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.adjusted_liability_amount, 0), 0)) ifrs_adjusted_liability_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_liability_amount, 0), 0)) ifrs_opening_liability_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.payment_item_amount, 0), 0)) ifrs_payment_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.interest_amount, 0), 0)) ifrs_interest_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_liability_amount, 0), 0)) ifrs_closing_liability_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.present_value_amount, 0), 0)) ifrs_present_value, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.total_increase_amount, 0), 0)) ifrs_increase_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.adjusted_fin_rou_amount, 0), 0)) ifrs_adj_fin_rou_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_fin_rou_amount, 0), 0)) ifrs_opening_rou_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.fin_amortization, 0), 0)) ifrs_amortization, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_fin_rou_amount, 0), 0)) ifrs_closing_rou_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.adjusted_oper_rou_amount, 0), 0)) ifrs_adj_oper_rou_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_oper_rou_amount, 0), 0)) ifrs_opening_oper_rou_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.oper_amortization, 0), 0)) ifrs_oper_amortization, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_oper_rou_amount, 0), 0)) ifrs_closing_oper_rou_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.accrued_interest_amount, 0), 0)) ifrs_accrued_interest_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_stl_amount, 0), 0)) ifrs_opening_stl_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.reclass_stl_amount, 0), 0)) ifrs_reclass_stl_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_stl_amount, 0), 0)) ifrs_closing_stl_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_ltl_amount, 0), 0)) ifrs_opening_ltl_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.reclass_ltl_amount, 0), 0)) ifrs_reclass_ltl_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_ltl_amount, 0), 0)) ifrs_closing_ltl_amount, SUM(CASE WHEN s.regime_code = 'IFRS16' AND s.revenue_method_code = 'LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) ifrs_oper_lease_revenue, SUM(CASE WHEN s.regime_code = 'IFRS16' AND s.revenue_method_code = 'NON_LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) ifrs_oper_non_lease_revenue, SUM(CASE WHEN s.regime_code = 'IFRS16' AND s.schedule_type_code = 'BILLING' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END) ifrs_oper_uer_amount, MAX(DECODE(s.regime_code, 'IFRS16', s.accrued_flag, 'N')) ifrs_accrued_flag, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.adjusted_liability_amount, 0), 0)) asc_adjusted_liability_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_liability_amount, 0), 0)) asc_opening_liability_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.payment_item_amount, 0), 0)) asc_payment_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.interest_amount, 0), 0)) asc_interest_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_liability_amount, 0), 0)) asc_closing_liability_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.present_value_amount, 0), 0)) asc_present_value, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.total_increase_amount, 0), 0)) asc_increase_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.contingent_amount, 0), 0)) asc_contingent_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.adjusted_fin_rou_amount, 0), 0)) asc_adj_fin_rou_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_fin_rou_amount, 0), 0)) asc_opening_fin_rou_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.fin_amortization, 0), 0)) asc_fin_amortization, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_fin_rou_amount, 0), 0)) asc_closing_fin_rou_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.adjusted_oper_rou_amount, 0), 0)) asc_adj_oper_rou_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_oper_rou_amount, 0), 0)) asc_opening_oper_rou_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.oper_amortization, 0), 0)) asc_oper_amortization, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_oper_rou_amount, 0), 0)) asc_closing_oper_rou_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.lease_expense, 0), 0)) asc_lease_expense, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.accrued_interest_amount, 0), 0)) asc_accrued_interest_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_stl_amount, 0), 0)) asc_opening_stl_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.reclass_stl_amount, 0), 0)) asc_reclass_stl_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_stl_amount, 0), 0)) asc_closing_stl_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_ltl_amount, 0), 0)) asc_opening_ltl_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.reclass_ltl_amount, 0), 0)) asc_reclass_ltl_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_ltl_amount, 0), 0)) asc_closing_ltl_amount, SUM(CASE WHEN s.regime_code = 'ASC842' AND s.revenue_method_code = 'LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) asc_oper_lease_revenue, SUM(CASE WHEN s.regime_code = 'ASC842' AND s.revenue_method_code = 'NON_LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) asc_oper_non_lease_revenue, SUM(CASE WHEN s.regime_code = 'ASC842' AND s.schedule_type_code = 'BILLING' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END) asc_oper_uer_amount, MAX(DECODE(s.regime_code, 'ASC842', s.accrued_flag, 'N')) asc_accrued_flag, MAX(s.interest_rate) interest_rate, MAX(s.adder_rate) adder_rate, MAX(s.daily_compounding_rate) daily_compounding_rate, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.adjusted_liability_amount, 0), 0)) jgap_adjusted_liability_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_liability_amount, 0), 0)) jgap_opening_liability_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.payment_item_amount, 0), 0)) jgap_payment_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.interest_amount, 0), 0)) jgap_interest_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_liability_amount, 0), 0)) jgap_closing_liability_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.present_value_amount, 0), 0)) jgap_present_value, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.total_increase_amount, 0), 0)) jgap_increase_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.adjusted_fin_rou_amount, 0), 0)) jgap_adj_fin_rou_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_fin_rou_amount, 0), 0)) jgap_opening_fin_rou_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.fin_amortization, 0), 0)) jgap_fin_amortization, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_fin_rou_amount, 0), 0)) jgap_closing_fin_rou_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.accrued_interest_amount, 0), 0)) jgap_accrued_interest_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.adjusted_oper_rou_amount, 0), 0)) jgap_adj_oper_rou_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_oper_rou_amount, 0), 0)) jgap_opening_oper_rou_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.oper_amortization, 0), 0)) jgap_oper_amortization, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_oper_rou_amount, 0), 0)) jgap_closing_oper_rou_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_stl_amount, 0), 0)) jgap_opening_stl_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.reclass_stl_amount, 0), 0)) jgap_reclass_stl_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_stl_amount, 0), 0)) jgap_closing_stl_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_ltl_amount, 0), 0)) jgap_opening_ltl_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.reclass_ltl_amount, 0), 0)) jgap_reclass_ltl_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_ltl_amount, 0), 0)) jgap_closing_ltl_amount, SUM(CASE WHEN s.regime_code = 'JGAAP' AND s.revenue_method_code = 'LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) jgap_oper_lease_revenue, SUM(CASE WHEN s.regime_code = 'JGAAP' AND s.revenue_method_code = 'NON_LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) jgap_oper_non_lease_revenue, SUM(CASE WHEN s.regime_code = 'JGAAP' AND s.schedule_type_code = 'BILLING' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END) jgap_oper_uer_amount, MAX(DECODE(s.regime_code, 'JGAAP', s.accrued_flag, 'N')) jgap_accrued_flag FROM ( SELECT sh.payment_number, sh.lease_id, sh.lease_detail_id, sh.version_num, sh.regime_code, sh.interest_rate, sh.adder_rate, sh.daily_compounding_rate, ph.currency_code, ph.rou_asset_flag, ph.liability_flag, ph.schedule_type_code, ph.revenue_method_code, ass.period_name, ass.schedule_date, ass.accrual_start_date schedule_start_date, ass.schedule_date schedule_end_date, ass.number_of_accrual_days schedule_days, ass.accrued_flag, MIN(DECODE(sh.amortization_start_date, NVL(ass.accrual_start_date, sh.amortization_start_date), NVL(sh.adjusted_liability_amount,0), 0)) adjusted_liability_amount, MIN(NVL(lss.opening_liability_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_start_date) opening_liability_amount, SUM(NVL(lss.payment_item_amount, 0)) payment_item_amount, SUM(NVL(lss.interest_amount, 0)) interest_amount, MIN(NVL(lss.closing_liability_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_end_date DESC) closing_liability_amount, SUM(ROUND(NVL(lss.present_value_amount, 0), fc.extended_precision)) present_value_amount, SUM(NVL(lss.increase_amount, 0)) increase_amount, SUM(NVL(lss.contingent_amount, 0)) contingent_amount, SUM(NVL(lss.cumulative_increase_amount, 0)) cumulative_increase_amount, SUM(NVL(lss.cumulative_contingent_amount, 0)) cumulative_contingent_amount, SUM(NVL(lss.total_increase_amount, 0)) total_increase_amount, SUM(NVL(total_contingent_amount, 0)) total_contingent_amount, MIN(DECODE(sh.amortization_start_date, NVL(ass.accrual_start_date, sh.amortization_start_date), NVL(sh.adjusted_fin_rou_amount, 0), 0)) adjusted_fin_rou_amount, ass.opening_fin_rou_amount, ass.fin_amortization, ass.closing_fin_rou_amount, MIN(DECODE(sh.amortization_start_date, NVL(ass.accrual_start_date, sh.amortization_start_date), NVL(sh.adjusted_oper_rou_amount, 0), 0)) adjusted_oper_rou_amount, ass.opening_oper_rou_amount, ass.oper_amortization, ass.closing_oper_rou_amount, ass.lease_expense, ass.interest_amount accrued_interest_amount, MIN(NVL(lss.opening_short_term_liab_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_start_date) opening_stl_amount, SUM(NVL(lss.reclass_short_term_liab_amount, 0)) reclass_stl_amount, MIN(NVL(lss.closing_short_term_liab_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_end_date DESC) closing_stl_amount, MIN(NVL(lss.opening_long_term_liab_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_start_date) opening_ltl_amount, SUM(NVL(lss.reclass_long_term_liab_amount, 0)) reclass_ltl_amount, MIN(NVL(lss.closing_long_term_liab_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_end_date DESC) closing_ltl_amount FROM ( SELECT sh.lease_id, sh.payment_number, sh.regime_code, sh.amortization_start_date, MIN(sh.schedule_header_id) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) schedule_header_id, MIN(sh.lease_detail_id) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) lease_detail_id, MIN(sh.version_num) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) version_num, SUM(NVL(sh.adjusted_liability_amount,0) + (-1 * NVL(sh.reduced_liability_amount,0))) adjusted_liability_amount, SUM(NVL(sh.adjusted_fin_rou_amount,0) + (-1 * NVL(sh.reduced_fin_rou_amount,0))) adjusted_fin_rou_amount, SUM(NVL(sh.adjusted_oper_rou_amount,0) + (-1 * NVL(sh.reduced_oper_rou_amount,0))) adjusted_oper_rou_amount, MIN(sh.interest_rate) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) interest_rate, MIN(sh.adder_rate) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) adder_rate, MIN(sh.daily_compounding_rate) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) daily_compounding_rate FROM fla_schedule_headers sh WHERE NVL(sh.reversal_flag,'N') = 'N' GROUP BY sh.lease_id, sh.payment_number, sh.regime_code, sh.amortization_start_date ) sh, fla_lease_payment_headers ph, fla_currencies_v fc, ( SELECT ass.schedule_header_id, ass.accrual_start_date, ass.schedule_date, ass.period_name, ass.number_of_accrual_days, ass.interest_amount, ass.schedule1_amount fin_amortization, ass.schedule2_amount oper_amortization, ass.schedule3_amount lease_expense, ass.opening_fin_rou_amount, ass.closing_fin_rou_amount, ass.opening_oper_rou_amount, ass.closing_oper_rou_amount, ass.accrued_flag, ass.stage_request_id, 'N' expired_rou_flag FROM fla_accrual_schedules_stage ass WHERE ass.stage_request_id = -99 UNION ALL SELECT ass.schedule_header_id, ass.accrual_start_date, ass.schedule_date, ass.period_name, ass.number_of_accrual_days, ass.interest_amount, ass.schedule1_amount fin_amortization, ass.schedule2_amount oper_amortization, ass.schedule3_amount lease_expense, ass.opening_fin_rou_amount, ass.closing_fin_rou_amount, ass.opening_oper_rou_amount, ass.closing_oper_rou_amount, ass.accrued_flag, 100 stage_request_id, NVL(expired_rou_flag, 'N') expired_rou_flag FROM fla_accrual_schedules ass, fla_schedule_headers sh1 WHERE ass.schedule_header_id = sh1.schedule_header_id AND ass.accrual_reversal_flag = 'N' AND ass.schedule_date < NVL(( SELECT min(ass2.schedule_date) FROM fla_accrual_schedules_stage ass2, fla_schedule_headers sh2 WHERE ass2.schedule_header_id = sh2.schedule_header_id AND sh2.payment_number = sh1.payment_number AND sh2.regime_code = sh1.regime_code AND sh2.lease_id = sh1.lease_id AND ass2.stage_request_id = -99) ,ass.schedule_date+1) ) ass, ( SELECT stg.schedule_header_id, stg.liability_start_date, stg.liability_end_date, stg.schedule_date, stg.payment_item_amount, stg.number_of_liability_days, stg.cumulative_number_of_days, stg.present_value_amount, stg.opening_liability_amount, stg.closing_liability_amount, stg.interest_amount, NVL(stg.increase_amount,0) increase_amount, NVL(stg.contingent_amount,0) contingent_amount, NVL(stg.cumulative_increase_amount,0) cumulative_increase_amount, NVL(stg.cumulative_contingent_amount,0) cumulative_contingent_amount, NVL(stg.increase_amount,0) + NVL(stg.cumulative_increase_amount,0) total_increase_amount, NVL(stg.contingent_amount,0) + NVL(stg.cumulative_contingent_amount,0) total_contingent_amount, stg.stage_request_id, stg.opening_short_term_liab_amount, stg.reclass_short_term_liab_amount, stg.closing_short_term_liab_amount, stg.opening_long_term_liab_amount, stg.reclass_long_term_liab_amount, stg.closing_long_term_liab_amount FROM fla_liability_schedules_stage stg WHERE stg.stage_request_id = -99 UNION ALL SELECT ls.schedule_header_id, ls.liability_start_date, ls.liability_end_date, ls.schedule_date, ls.payment_item_amount, ls.number_of_liability_days, ls.cumulative_number_of_days, ls.present_value_amount, ls.opening_liability_amount, ls.closing_liability_amount, ls.interest_amount, NVL(ls.increase_amount,0) increase_amount, NVL(ls.contingent_amount,0) contingent_amount, NVL(ls.cumulative_increase_amount,0) cumulative_increase_amount, NVL(ls.cumulative_contingent_amount,0) cumulative_contingent_amount, NVL(ls.increase_amount,0) + NVL(ls.cumulative_increase_amount,0) total_increase_amount, NVL(ls.contingent_amount,0) + NVL(ls.cumulative_contingent_amount,0) total_contingent_amount, 100 stage_request_id, ls.opening_short_term_liab_amount, ls.reclass_short_term_liab_amount, ls.closing_short_term_liab_amount, ls.opening_long_term_liab_amount, ls.reclass_long_term_liab_amount, ls.closing_long_term_liab_amount FROM fla_liability_schedules ls, fla_schedule_headers sh1 WHERE ls.schedule_header_id = sh1.schedule_header_id AND ls.schedule_date < NVL(( SELECT MIN(lst.schedule_date) FROM fla_liability_schedules_stage lst, fla_schedule_headers sh2 WHERE lst.schedule_header_id = sh2.schedule_header_id AND sh2.payment_number = sh1.payment_number AND sh2.regime_code = sh1.regime_code AND sh2.lease_id = sh1.lease_id AND lst.stage_request_id = -99) ,ls.schedule_date+1) ) lss WHERE sh.lease_id = ph.lease_id AND sh.lease_detail_id = ph.lease_detail_id AND sh.payment_number = ph.payment_number AND ph.currency_code = fc.currency_code AND sh.schedule_header_id = ass.schedule_header_id AND sh.schedule_header_id = lss.schedule_header_id(+) AND ass.schedule_date = lss.schedule_date(+) GROUP BY sh.payment_number, sh.lease_id, sh.lease_detail_id, sh.version_num, sh.regime_code, sh.interest_rate, sh.adder_rate, sh.daily_compounding_rate, ph.currency_code, ph.rou_asset_flag, ph.liability_flag, ph.schedule_type_code, ph.revenue_method_code, ass.period_name, ass.schedule_date, ass.accrual_start_date, ass.schedule_date, ass.number_of_accrual_days, ass.accrued_flag, ass.opening_fin_rou_amount, ass.fin_amortization, ass.closing_fin_rou_amount, ass.opening_oper_rou_amount, ass.oper_amortization, ass.closing_oper_rou_amount, ass.lease_expense, ass.interest_amount ) s GROUP BY s.lease_id, s.lease_detail_id, s.version_num, s.payment_number, s.currency_code, s.liability_flag, s.rou_asset_flag, s.schedule_type_code, s.revenue_method_code, s.period_name, s.schedule_date, s.schedule_start_date, s.schedule_end_date, s.schedule_days

SQL_Statement

SELECT lease_id,

lease_detail_id,

version_num,

payment_number,

schedule_type_code,

revenue_method_code,

currency_code,

liability_flag,

rou_asset_flag,

period_name,

schedule_date,

schedule_start_date,

schedule_end_date,

schedule_days,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.adjusted_liability_amount, 0), 0)) ifrs_adjusted_liability_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_liability_amount, 0), 0)) ifrs_opening_liability_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.payment_item_amount, 0), 0)) ifrs_payment_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.interest_amount, 0), 0)) ifrs_interest_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_liability_amount, 0), 0)) ifrs_closing_liability_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.present_value_amount, 0), 0)) ifrs_present_value,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.total_increase_amount, 0), 0)) ifrs_increase_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.adjusted_fin_rou_amount, 0), 0)) ifrs_adj_fin_rou_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_fin_rou_amount, 0), 0)) ifrs_opening_rou_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.fin_amortization, 0), 0)) ifrs_amortization,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_fin_rou_amount, 0), 0)) ifrs_closing_rou_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.adjusted_oper_rou_amount, 0), 0)) ifrs_adj_oper_rou_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_oper_rou_amount, 0), 0)) ifrs_opening_oper_rou_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.oper_amortization, 0), 0)) ifrs_oper_amortization,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_oper_rou_amount, 0), 0)) ifrs_closing_oper_rou_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.accrued_interest_amount, 0), 0)) ifrs_accrued_interest_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_stl_amount, 0), 0)) ifrs_opening_stl_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.reclass_stl_amount, 0), 0)) ifrs_reclass_stl_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_stl_amount, 0), 0)) ifrs_closing_stl_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.opening_ltl_amount, 0), 0)) ifrs_opening_ltl_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.reclass_ltl_amount, 0), 0)) ifrs_reclass_ltl_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.closing_ltl_amount, 0), 0)) ifrs_closing_ltl_amount,

SUM(CASE WHEN s.regime_code = 'IFRS16' AND s.revenue_method_code = 'LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) ifrs_oper_lease_revenue,

SUM(CASE WHEN s.regime_code = 'IFRS16' AND s.revenue_method_code = 'NON_LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) ifrs_oper_non_lease_revenue,

SUM(CASE WHEN s.regime_code = 'IFRS16' AND s.schedule_type_code = 'BILLING' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END) ifrs_oper_uer_amount,

MAX(DECODE(s.regime_code, 'IFRS16', s.accrued_flag, 'N')) ifrs_accrued_flag,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.adjusted_liability_amount, 0), 0)) asc_adjusted_liability_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_liability_amount, 0), 0)) asc_opening_liability_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.payment_item_amount, 0), 0)) asc_payment_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.interest_amount, 0), 0)) asc_interest_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_liability_amount, 0), 0)) asc_closing_liability_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.present_value_amount, 0), 0)) asc_present_value,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.total_increase_amount, 0), 0)) asc_increase_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.contingent_amount, 0), 0)) asc_contingent_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.adjusted_fin_rou_amount, 0), 0)) asc_adj_fin_rou_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_fin_rou_amount, 0), 0)) asc_opening_fin_rou_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.fin_amortization, 0), 0)) asc_fin_amortization,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_fin_rou_amount, 0), 0)) asc_closing_fin_rou_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.adjusted_oper_rou_amount, 0), 0)) asc_adj_oper_rou_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_oper_rou_amount, 0), 0)) asc_opening_oper_rou_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.oper_amortization, 0), 0)) asc_oper_amortization,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_oper_rou_amount, 0), 0)) asc_closing_oper_rou_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.lease_expense, 0), 0)) asc_lease_expense,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.accrued_interest_amount, 0), 0)) asc_accrued_interest_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_stl_amount, 0), 0)) asc_opening_stl_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.reclass_stl_amount, 0), 0)) asc_reclass_stl_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_stl_amount, 0), 0)) asc_closing_stl_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.opening_ltl_amount, 0), 0)) asc_opening_ltl_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.reclass_ltl_amount, 0), 0)) asc_reclass_ltl_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.closing_ltl_amount, 0), 0)) asc_closing_ltl_amount,

SUM(CASE WHEN s.regime_code = 'ASC842' AND s.revenue_method_code = 'LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) asc_oper_lease_revenue,

SUM(CASE WHEN s.regime_code = 'ASC842' AND s.revenue_method_code = 'NON_LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) asc_oper_non_lease_revenue,

SUM(CASE WHEN s.regime_code = 'ASC842' AND s.schedule_type_code = 'BILLING' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END) asc_oper_uer_amount,

MAX(DECODE(s.regime_code, 'ASC842', s.accrued_flag, 'N')) asc_accrued_flag,

MAX(s.interest_rate) interest_rate,

MAX(s.adder_rate) adder_rate,

MAX(s.daily_compounding_rate) daily_compounding_rate,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.adjusted_liability_amount, 0), 0)) jgap_adjusted_liability_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_liability_amount, 0), 0)) jgap_opening_liability_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.payment_item_amount, 0), 0)) jgap_payment_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.interest_amount, 0), 0)) jgap_interest_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_liability_amount, 0), 0)) jgap_closing_liability_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.present_value_amount, 0), 0)) jgap_present_value,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.total_increase_amount, 0), 0)) jgap_increase_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.adjusted_fin_rou_amount, 0), 0)) jgap_adj_fin_rou_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_fin_rou_amount, 0), 0)) jgap_opening_fin_rou_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.fin_amortization, 0), 0)) jgap_fin_amortization,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_fin_rou_amount, 0), 0)) jgap_closing_fin_rou_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.accrued_interest_amount, 0), 0)) jgap_accrued_interest_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.adjusted_oper_rou_amount, 0), 0)) jgap_adj_oper_rou_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_oper_rou_amount, 0), 0)) jgap_opening_oper_rou_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.oper_amortization, 0), 0)) jgap_oper_amortization,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_oper_rou_amount, 0), 0)) jgap_closing_oper_rou_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_stl_amount, 0), 0)) jgap_opening_stl_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.reclass_stl_amount, 0), 0)) jgap_reclass_stl_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_stl_amount, 0), 0)) jgap_closing_stl_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.opening_ltl_amount, 0), 0)) jgap_opening_ltl_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.reclass_ltl_amount, 0), 0)) jgap_reclass_ltl_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.closing_ltl_amount, 0), 0)) jgap_closing_ltl_amount,

SUM(CASE WHEN s.regime_code = 'JGAAP' AND s.revenue_method_code = 'LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) jgap_oper_lease_revenue,

SUM(CASE WHEN s.regime_code = 'JGAAP' AND s.revenue_method_code = 'NON_LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END) jgap_oper_non_lease_revenue,

SUM(CASE WHEN s.regime_code = 'JGAAP' AND s.schedule_type_code = 'BILLING' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END) jgap_oper_uer_amount,

MAX(DECODE(s.regime_code, 'JGAAP', s.accrued_flag, 'N')) jgap_accrued_flag

FROM (

SELECT sh.payment_number,

sh.lease_id,

sh.lease_detail_id,

sh.version_num,

sh.regime_code,

sh.interest_rate,

sh.adder_rate,

sh.daily_compounding_rate,

ph.currency_code,

ph.rou_asset_flag,

ph.liability_flag,

ph.schedule_type_code,

ph.revenue_method_code,

ass.period_name,

ass.schedule_date,

ass.accrual_start_date schedule_start_date,

ass.schedule_date schedule_end_date,

ass.number_of_accrual_days schedule_days,

ass.accrued_flag,

MIN(DECODE(sh.amortization_start_date, NVL(ass.accrual_start_date, sh.amortization_start_date), NVL(sh.adjusted_liability_amount,0), 0)) adjusted_liability_amount,

MIN(NVL(lss.opening_liability_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_start_date) opening_liability_amount,

SUM(NVL(lss.payment_item_amount, 0)) payment_item_amount,

SUM(NVL(lss.interest_amount, 0)) interest_amount,

MIN(NVL(lss.closing_liability_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_end_date DESC) closing_liability_amount,

SUM(ROUND(NVL(lss.present_value_amount, 0), fc.extended_precision)) present_value_amount,

SUM(NVL(lss.increase_amount, 0)) increase_amount,

SUM(NVL(lss.contingent_amount, 0)) contingent_amount,

SUM(NVL(lss.cumulative_increase_amount, 0)) cumulative_increase_amount,

SUM(NVL(lss.cumulative_contingent_amount, 0)) cumulative_contingent_amount,

SUM(NVL(lss.total_increase_amount, 0)) total_increase_amount,

SUM(NVL(total_contingent_amount, 0)) total_contingent_amount,

MIN(DECODE(sh.amortization_start_date, NVL(ass.accrual_start_date, sh.amortization_start_date), NVL(sh.adjusted_fin_rou_amount, 0), 0)) adjusted_fin_rou_amount,

ass.opening_fin_rou_amount,

ass.fin_amortization,

ass.closing_fin_rou_amount,

MIN(DECODE(sh.amortization_start_date, NVL(ass.accrual_start_date, sh.amortization_start_date), NVL(sh.adjusted_oper_rou_amount, 0), 0)) adjusted_oper_rou_amount,

ass.opening_oper_rou_amount,

ass.oper_amortization,

ass.closing_oper_rou_amount,

ass.lease_expense,

ass.interest_amount accrued_interest_amount,

MIN(NVL(lss.opening_short_term_liab_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_start_date) opening_stl_amount,

SUM(NVL(lss.reclass_short_term_liab_amount, 0)) reclass_stl_amount,

MIN(NVL(lss.closing_short_term_liab_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_end_date DESC) closing_stl_amount,

MIN(NVL(lss.opening_long_term_liab_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_start_date) opening_ltl_amount,

SUM(NVL(lss.reclass_long_term_liab_amount, 0)) reclass_ltl_amount,

MIN(NVL(lss.closing_long_term_liab_amount,0)) KEEP(DENSE_RANK FIRST ORDER BY lss.liability_end_date DESC) closing_ltl_amount

FROM (

SELECT sh.lease_id,

sh.payment_number,

sh.regime_code,

sh.amortization_start_date,

MIN(sh.schedule_header_id) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) schedule_header_id,

MIN(sh.lease_detail_id) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) lease_detail_id,

MIN(sh.version_num) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) version_num,

SUM(NVL(sh.adjusted_liability_amount,0) + (-1 * NVL(sh.reduced_liability_amount,0))) adjusted_liability_amount,

SUM(NVL(sh.adjusted_fin_rou_amount,0) + (-1 * NVL(sh.reduced_fin_rou_amount,0))) adjusted_fin_rou_amount,

SUM(NVL(sh.adjusted_oper_rou_amount,0) + (-1 * NVL(sh.reduced_oper_rou_amount,0))) adjusted_oper_rou_amount,

MIN(sh.interest_rate) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) interest_rate,

MIN(sh.adder_rate) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) adder_rate,

MIN(sh.daily_compounding_rate) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) daily_compounding_rate

FROM fla_schedule_headers sh

WHERE NVL(sh.reversal_flag,'N') = 'N'

GROUP BY sh.lease_id,

sh.payment_number,

sh.regime_code,

sh.amortization_start_date

) sh,

fla_lease_payment_headers ph,

fla_currencies_v fc,

(

SELECT ass.schedule_header_id,

ass.accrual_start_date,

ass.schedule_date,

ass.period_name,

ass.number_of_accrual_days,

ass.interest_amount,

ass.schedule1_amount fin_amortization,

ass.schedule2_amount oper_amortization,

ass.schedule3_amount lease_expense,

ass.opening_fin_rou_amount,

ass.closing_fin_rou_amount,

ass.opening_oper_rou_amount,

ass.closing_oper_rou_amount,

ass.accrued_flag,

ass.stage_request_id,

'N' expired_rou_flag

FROM fla_accrual_schedules_stage ass

WHERE ass.stage_request_id = -99

UNION ALL

SELECT ass.schedule_header_id,

ass.accrual_start_date,

ass.schedule_date,

ass.period_name,

ass.number_of_accrual_days,

ass.interest_amount,

ass.schedule1_amount fin_amortization,

ass.schedule2_amount oper_amortization,

ass.schedule3_amount lease_expense,

ass.opening_fin_rou_amount,

ass.closing_fin_rou_amount,

ass.opening_oper_rou_amount,

ass.closing_oper_rou_amount,

ass.accrued_flag,

100 stage_request_id,

NVL(expired_rou_flag, 'N') expired_rou_flag

FROM fla_accrual_schedules ass,

fla_schedule_headers sh1

WHERE ass.schedule_header_id = sh1.schedule_header_id

AND ass.accrual_reversal_flag = 'N'

AND ass.schedule_date < NVL((

SELECT min(ass2.schedule_date)

FROM fla_accrual_schedules_stage ass2,

fla_schedule_headers sh2

WHERE ass2.schedule_header_id = sh2.schedule_header_id

AND sh2.payment_number = sh1.payment_number

AND sh2.regime_code = sh1.regime_code

AND sh2.lease_id = sh1.lease_id

AND ass2.stage_request_id = -99)

,ass.schedule_date+1)

) ass,

(

SELECT stg.schedule_header_id,

stg.liability_start_date,

stg.liability_end_date,

stg.schedule_date,

stg.payment_item_amount,

stg.number_of_liability_days,

stg.cumulative_number_of_days,

stg.present_value_amount,

stg.opening_liability_amount,

stg.closing_liability_amount,

stg.interest_amount,

NVL(stg.increase_amount,0) increase_amount,

NVL(stg.contingent_amount,0) contingent_amount,

NVL(stg.cumulative_increase_amount,0) cumulative_increase_amount,

NVL(stg.cumulative_contingent_amount,0) cumulative_contingent_amount,

NVL(stg.increase_amount,0) + NVL(stg.cumulative_increase_amount,0) total_increase_amount,

NVL(stg.contingent_amount,0) + NVL(stg.cumulative_contingent_amount,0) total_contingent_amount,

stg.stage_request_id,

stg.opening_short_term_liab_amount,

stg.reclass_short_term_liab_amount,

stg.closing_short_term_liab_amount,

stg.opening_long_term_liab_amount,

stg.reclass_long_term_liab_amount,

stg.closing_long_term_liab_amount

FROM fla_liability_schedules_stage stg

WHERE stg.stage_request_id = -99

UNION ALL

SELECT ls.schedule_header_id,

ls.liability_start_date,

ls.liability_end_date,

ls.schedule_date,

ls.payment_item_amount,

ls.number_of_liability_days,

ls.cumulative_number_of_days,

ls.present_value_amount,

ls.opening_liability_amount,

ls.closing_liability_amount,

ls.interest_amount,

NVL(ls.increase_amount,0) increase_amount,

NVL(ls.contingent_amount,0) contingent_amount,

NVL(ls.cumulative_increase_amount,0) cumulative_increase_amount,

NVL(ls.cumulative_contingent_amount,0) cumulative_contingent_amount,

NVL(ls.increase_amount,0) + NVL(ls.cumulative_increase_amount,0) total_increase_amount,

NVL(ls.contingent_amount,0) + NVL(ls.cumulative_contingent_amount,0) total_contingent_amount,

100 stage_request_id,

ls.opening_short_term_liab_amount,

ls.reclass_short_term_liab_amount,

ls.closing_short_term_liab_amount,

ls.opening_long_term_liab_amount,

ls.reclass_long_term_liab_amount,

ls.closing_long_term_liab_amount

FROM fla_liability_schedules ls,

fla_schedule_headers sh1

WHERE ls.schedule_header_id = sh1.schedule_header_id

AND ls.schedule_date < NVL((

SELECT MIN(lst.schedule_date)

FROM fla_liability_schedules_stage lst,

fla_schedule_headers sh2

WHERE lst.schedule_header_id = sh2.schedule_header_id

AND sh2.payment_number = sh1.payment_number

AND sh2.regime_code = sh1.regime_code

AND sh2.lease_id = sh1.lease_id

AND lst.stage_request_id = -99)

,ls.schedule_date+1)

) lss

WHERE sh.lease_id = ph.lease_id

AND sh.lease_detail_id = ph.lease_detail_id

AND sh.payment_number = ph.payment_number

AND ph.currency_code = fc.currency_code

AND sh.schedule_header_id = ass.schedule_header_id

AND sh.schedule_header_id = lss.schedule_header_id(+)

AND ass.schedule_date = lss.schedule_date(+)

GROUP BY sh.payment_number,

sh.lease_id,

sh.lease_detail_id,

sh.version_num,

sh.regime_code,

sh.interest_rate,

sh.adder_rate,

sh.daily_compounding_rate,

ph.currency_code,

ph.rou_asset_flag,

ph.liability_flag,

ph.schedule_type_code,

ph.revenue_method_code,

ass.period_name,

ass.schedule_date,

ass.accrual_start_date,

ass.schedule_date,

ass.number_of_accrual_days,

ass.accrued_flag,

ass.opening_fin_rou_amount,

ass.fin_amortization,

ass.closing_fin_rou_amount,

ass.opening_oper_rou_amount,

ass.oper_amortization,

ass.closing_oper_rou_amount,

ass.lease_expense,

ass.interest_amount

) s

GROUP BY s.lease_id,

s.lease_detail_id,

s.version_num,

s.payment_number,

s.currency_code,

s.liability_flag,

s.rou_asset_flag,

s.schedule_type_code,

s.revenue_method_code,

s.period_name,

s.schedule_date,

s.schedule_start_date,

s.schedule_end_date,

s.schedule_days