FLA_ON_DEMAND_SCH_SUMMARY

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID BU_ID LEASE_TYPE_CODE LEASE_NUMBER LEASE_NAME LEASE_DETAIL_ID DATA_DESC VERSION_NUM ASSET_NUMBER PAYMENT_OPTION PAYMENT_NUMBER SCHEDULE_TYPE_CODE REVENUE_METHOD_CODE PAYMENT_PURPOSE PAYMENT_TYPE OPTION_TYPE 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_BASE_PAYMENT_AMOUNT IFRS_INCREASE_AMOUNT IFRS_FINAL_PAYMENT_AMOUNT IFRS_INTEREST_EXPENSE_AMOUNT IFRS_CLOSING_LIABILITY_AMOUNT IFRS_PRESENT_VALUE IFRS_ADJUSTED_ROU_AMOUNT IFRS_OPENING_ROU_AMOUNT IFRS_AMORTIZATION_AMOUNT IFRS_CLOSING_ROU_AMOUNT IFRS_ADJUSTED_OPER_ROU_AMOUNT IFRS_OPENING_OPER_ROU_AMOUNT IFRS_OPER_AMORTIZATION_AMOUNT IFRS_CLOSING_OPER_ROU_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_BASE_PAYMENT_AMOUNT ASC_INCREASE_AMOUNT ASC_CONTINGENT_AMOUNT ASC_FINAL_PAYMENT_AMOUNT ASC_INTEREST_EXPENSE_AMOUNT ASC_CLOSING_LIABILITY_AMOUNT ASC_PRESENT_VALUE ASC_ADJUSTED_FIN_ROU_AMOUNT ASC_OPENING_FIN_ROU_AMOUNT ASC_FIN_AMORTIZATION_AMOUNT ASC_CLOSING_FIN_ROU_AMOUNT ASC_ADJUSTED_OPER_ROU_AMOUNT ASC_OPENING_OPER_ROU_AMOUNT ASC_OPER_AMORTIZATION_AMOUNT ASC_OPER_LEASE_EXPENSE_AMOUNT ASC_CLOSING_OPER_ROU_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 JGAP_ADJUSTED_LIABILITY_AMOUNT JGAP_OPENING_LIABILITY_AMOUNT JGAP_BASE_PAYMENT_AMOUNT JGAP_INCREASE_AMOUNT JGAP_FINAL_PAYMENT_AMOUNT JGAP_INTEREST_EXPENSE_AMOUNT JGAP_CLOSING_LIABILITY_AMOUNT JGAP_PRESENT_VALUE JGAP_ADJUSTED_FIN_ROU_AMOUNT JGAP_OPENING_FIN_ROU_AMOUNT JGAP_AMORTIZATION_FIN_AMOUNT JGAP_CLOSING_FIN_ROU_AMOUNT JGAP_ADJUSTED_OPER_ROU_AMOUNT JGAP_OPENING_OPER_ROU_AMOUNT JGAP_OPER_AMORTIZATION_AMOUNT 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

BU_ID

LEASE_TYPE_CODE

LEASE_NUMBER

LEASE_NAME

LEASE_DETAIL_ID

DATA_DESC

VERSION_NUM

ASSET_NUMBER

PAYMENT_OPTION

PAYMENT_NUMBER

SCHEDULE_TYPE_CODE

REVENUE_METHOD_CODE

PAYMENT_PURPOSE

PAYMENT_TYPE

OPTION_TYPE

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_BASE_PAYMENT_AMOUNT

IFRS_INCREASE_AMOUNT

IFRS_FINAL_PAYMENT_AMOUNT

IFRS_INTEREST_EXPENSE_AMOUNT

IFRS_CLOSING_LIABILITY_AMOUNT

IFRS_PRESENT_VALUE

IFRS_ADJUSTED_ROU_AMOUNT

IFRS_OPENING_ROU_AMOUNT

IFRS_AMORTIZATION_AMOUNT

IFRS_CLOSING_ROU_AMOUNT

IFRS_ADJUSTED_OPER_ROU_AMOUNT

IFRS_OPENING_OPER_ROU_AMOUNT

IFRS_OPER_AMORTIZATION_AMOUNT

IFRS_CLOSING_OPER_ROU_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_BASE_PAYMENT_AMOUNT

ASC_INCREASE_AMOUNT

ASC_CONTINGENT_AMOUNT

ASC_FINAL_PAYMENT_AMOUNT

ASC_INTEREST_EXPENSE_AMOUNT

ASC_CLOSING_LIABILITY_AMOUNT

ASC_PRESENT_VALUE

ASC_ADJUSTED_FIN_ROU_AMOUNT

ASC_OPENING_FIN_ROU_AMOUNT

ASC_FIN_AMORTIZATION_AMOUNT

ASC_CLOSING_FIN_ROU_AMOUNT

ASC_ADJUSTED_OPER_ROU_AMOUNT

ASC_OPENING_OPER_ROU_AMOUNT

ASC_OPER_AMORTIZATION_AMOUNT

ASC_OPER_LEASE_EXPENSE_AMOUNT

ASC_CLOSING_OPER_ROU_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

JGAP_ADJUSTED_LIABILITY_AMOUNT

JGAP_OPENING_LIABILITY_AMOUNT

JGAP_BASE_PAYMENT_AMOUNT

JGAP_INCREASE_AMOUNT

JGAP_FINAL_PAYMENT_AMOUNT

JGAP_INTEREST_EXPENSE_AMOUNT

JGAP_CLOSING_LIABILITY_AMOUNT

JGAP_PRESENT_VALUE

JGAP_ADJUSTED_FIN_ROU_AMOUNT

JGAP_OPENING_FIN_ROU_AMOUNT

JGAP_AMORTIZATION_FIN_AMOUNT

JGAP_CLOSING_FIN_ROU_AMOUNT

JGAP_ADJUSTED_OPER_ROU_AMOUNT

JGAP_OPENING_OPER_ROU_AMOUNT

JGAP_OPER_AMORTIZATION_AMOUNT

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 s.lease_id, s.org_id bu_id, s.lease_type_code, s.lease_number, s.lease_name, s.lease_detail_id, DECODE(s.stage_request_id, -999, 'ondemand_schedules', 'main_schedules') data_desc, s.version_num, s.asset_number, DECODE(s.option_flag,'Y','OPTION','PAYMENT') payment_option, s.payment_number, s.schedule_type_code, s.revenue_method_code, pp.meaning payment_purpose, pt.meaning payment_type, ot.meaning option_type, s.currency_code, s.liability_flag, s.rou_asset_flag, s.period_name, s.schedule_date, s.schedule_start_date, s.schedule_end_date, s.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) - NVL(s.increase_amount, 0), 0)) ifrs_base_payment_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.increase_amount, 0), 0)) ifrs_increase_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.payment_item_amount, 0), 0)) ifrs_final_payment_amount, SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.interest_amount, 0), 0)) ifrs_interest_expense_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.adjusted_fin_rou_amount, 0), 0)) ifrs_adjusted_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_amount, 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_adjusted_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_amount, 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.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)) 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) - NVL(s.increase_amount, 0) + NVL(s.contingent_amount, 0), 0)) asc_base_payment_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.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.payment_item_amount, 0), 0)) asc_final_payment_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.interest_amount, 0), 0)) asc_interest_expense_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.adjusted_fin_rou_amount, 0), 0)) asc_adjusted_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_amount, 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_adjusted_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_amount, SUM(DECODE(s.regime_code, 'ASC842', NVL(s.lease_expense, 0), 0)) asc_oper_lease_expense_amount, 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.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)) asc_accrued_flag, 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) - NVL(s.increase_amount, 0), 0)) jgap_base_payment_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.increase_amount, 0), 0)) jgap_increase_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.payment_item_amount, 0), 0)) jgap_final_payment_amount, SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.interest_amount, 0), 0)) jgap_interest_expense_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.adjusted_fin_rou_amount, 0), 0)) jgap_adjusted_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_amortization_fin_amount, 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.adjusted_oper_rou_amount, 0), 0)) jgap_adjusted_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_amount, 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)) jgap_accrued_flag FROM ( SELECT sh.lease_id, sh.org_id, sh.lease_detail_id, sh.lease_number, sh.lease_name, sh.version_num, sh.payment_number, sh.regime_code, sh.currency_code, sh.option_flag, sh.payment_type_code, sh.payment_purpose_code, sh.option_type_code, sh.asset_number, sh.liability_flag, sh.rou_asset_flag, sh.schedule_type_code, sh.revenue_method_code, sh.lease_type_code, ass.stage_request_id, ass.number_of_accrual_days schedule_days, NVL(ass.accrued_flag, 'N') accrued_flag, ass.schedule_date, ass.period_name, ass.accrual_start_date schedule_start_date, ass.schedule_date schedule_end_date, 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(NVL(lss.present_value_amount, 0)) 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, 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 la.lease_id, la.org_id, la.lease_number, la.lease_name, sh.lease_detail_id, MIN(sh.schedule_header_id) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) schedule_header_id, MIN(sh.version_num) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) version_num, sh.payment_number, sh.regime_code, sh.amortization_start_date, ph.currency_code, ph.option_flag, ph.payment_type_code, ph.payment_purpose_code, ph.option_type_code, ph.asset_number, ph.liability_flag, ph.rou_asset_flag, ph.schedule_type_code, ph.revenue_method_code, la.lease_type_code, 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 FROM fla_leases_all la, fla_lease_details ld, fla_lease_payment_headers ph, fla_schedule_headers sh WHERE la.lease_id = ld.lease_id AND ld.version_type_code = 'F' AND ld.lease_id = ph.lease_id AND ld.lease_detail_id = ph.lease_detail_id AND ph.lease_id = sh.lease_id AND ph.lease_detail_id = sh.lease_detail_id AND ph.payment_number = sh.payment_number AND NVL(sh.reversal_flag, 'N') = 'N' GROUP BY la.lease_id, la.org_id, la.lease_number, la.lease_name, sh.lease_detail_id, sh.payment_number, sh.regime_code, sh.amortization_start_date, ph.currency_code, ph.option_flag, ph.payment_type_code, ph.payment_purpose_code, ph.option_type_code, ph.asset_number, ph.liability_flag, ph.rou_asset_flag, ph.schedule_type_code, ph.revenue_method_code, la.lease_type_code ) sh, ( SELECT accrual_schedule_id, schedule_header_id, accrual_start_date, schedule_date, period_name, number_of_accrual_days, interest_amount, schedule1_amount fin_amortization, schedule2_amount oper_amortization, schedule3_amount lease_expense, opening_fin_rou_amount, closing_fin_rou_amount, opening_oper_rou_amount, closing_oper_rou_amount, accrued_flag, stage_request_id FROM fla_accrual_schedules_stage ass WHERE stage_request_id = -999 AND NOT EXISTS( SELECT 1 FROM fla_accrual_schedules WHERE schedule_header_id = ass.schedule_header_id AND schedule_date = ass.schedule_date) UNION ALL SELECT accrual_schedule_id, schedule_header_id, accrual_start_date, schedule_date, period_name, number_of_accrual_days, interest_amount, schedule1_amount fin_amortization, schedule2_amount oper_amortization, schedule3_amount lease_expense, opening_fin_rou_amount, closing_fin_rou_amount, opening_oper_rou_amount, closing_oper_rou_amount, accrued_flag, 100 stage_request_id FROM fla_accrual_schedules WHERE accrual_reversal_flag = 'N' ) ass, ( SELECT schedule_header_id, liability_schedule_id, liability_start_date, liability_end_date, period_name, schedule_date, payment_item_amount, number_of_liability_days, cumulative_number_of_days, present_value_amount, opening_liability_amount, closing_liability_amount, interest_amount, interest_remainder_amount, NVL(increase_amount,0) increase_amount, NVL(contingent_amount,0) contingent_amount, NVL(cumulative_increase_amount,0) cumulative_increase_amount, NVL(cumulative_contingent_amount,0) cumulative_contingent_amount, NVL(increase_amount,0) + NVL(cumulative_increase_amount,0) total_increase_amount, NVL(contingent_amount,0) + NVL(cumulative_contingent_amount,0) total_contingent_amount, stage_request_id, NVL(opening_short_term_liab_amount, 0) opening_short_term_liab_amount, NVL(reclass_short_term_liab_amount, 0) reclass_short_term_liab_amount, NVL(closing_short_term_liab_amount, 0) closing_short_term_liab_amount, NVL(opening_short_term_liab_amount, 0) opening_long_term_liab_amount, NVL(reclass_long_term_liab_amount, 0) reclass_long_term_liab_amount, NVL(closing_long_term_liab_amount, 0) closing_long_term_liab_amount FROM fla_liability_schedules_stage stg WHERE stage_request_id = -999 AND NOT EXISTS( SELECT 1 FROM fla_liability_schedules lsh WHERE schedule_header_id = stg.schedule_header_id AND schedule_date = stg.schedule_date) UNION ALL SELECT ls.schedule_header_id, ls.liability_schedule_id, ls.liability_start_date, ls.liability_end_date, ls.period_name, 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, ls.interest_remainder_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, NVL(opening_short_term_liab_amount, 0) opening_short_term_liab_amount, NVL(reclass_short_term_liab_amount, 0) reclass_short_term_liab_amount, NVL(closing_short_term_liab_amount, 0) closing_short_term_liab_amount, NVL(opening_long_term_liab_amount, 0) opening_long_term_liab_amount, NVL(reclass_long_term_liab_amount, 0) reclass_long_term_liab_amount, NVL(closing_long_term_liab_amount, 0) closing_long_term_liab_amount FROM fla_liability_schedules ls ) lss WHERE 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.lease_id, sh.org_id, sh.lease_detail_id, sh.lease_number, sh.lease_name, sh.version_num, sh.payment_number, sh.regime_code, sh.currency_code, sh.option_flag, sh.payment_type_code, sh.payment_purpose_code, sh.option_type_code, sh.asset_number, sh.liability_flag, sh.rou_asset_flag, sh.schedule_type_code, sh.revenue_method_code, sh.lease_type_code, ass.stage_request_id, ass.number_of_accrual_days, NVL(ass.accrued_flag, 'N'), ass.schedule_date, ass.period_name, ass.accrual_start_date, ass.schedule_date, 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 ) s, fnd_lookups pt, fnd_lookups ot, fnd_lookups pp WHERE s.payment_type_code = pt.lookup_code(+) AND s.option_type_code = ot.lookup_code(+) AND s.payment_purpose_code = pp.lookup_code(+) AND pt.lookup_type(+) = 'ORA_FLA_PAYMENT_TYPE' AND ot.lookup_type(+) = 'ORA_FLA_LEASE_OPTION_TYPE' AND pp.lookup_type(+) = 'ORA_FLA_PAYMENT_PURPOSE' GROUP BY s.lease_id, s.org_id, s.lease_number, s.lease_name, s.lease_detail_id, s.version_num, s.asset_number, DECODE(s.option_flag,'Y','OPTION','PAYMENT'), s.payment_number, pp.meaning, pt.meaning, ot.meaning, s.currency_code, s.liability_flag, s.rou_asset_flag, s.schedule_type_code, s.revenue_method_code, s.lease_type_code, s.period_name, s.schedule_date, s.schedule_start_date, s.schedule_end_date, s.schedule_days, DECODE(s.stage_request_id, -999, 'ondemand_schedules', 'main_schedules')

SQL_Statement

SELECT s.lease_id,

s.org_id bu_id,

s.lease_type_code,

s.lease_number,

s.lease_name,

s.lease_detail_id,

DECODE(s.stage_request_id, -999, 'ondemand_schedules', 'main_schedules') data_desc,

s.version_num,

s.asset_number,

DECODE(s.option_flag,'Y','OPTION','PAYMENT') payment_option,

s.payment_number,

s.schedule_type_code,

s.revenue_method_code,

pp.meaning payment_purpose,

pt.meaning payment_type,

ot.meaning option_type,

s.currency_code,

s.liability_flag,

s.rou_asset_flag,

s.period_name,

s.schedule_date,

s.schedule_start_date,

s.schedule_end_date,

s.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) - NVL(s.increase_amount, 0), 0)) ifrs_base_payment_amount,

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

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

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.interest_amount, 0), 0)) ifrs_interest_expense_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.adjusted_fin_rou_amount, 0), 0)) ifrs_adjusted_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_amount,

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_adjusted_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_amount,

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.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)) 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) - NVL(s.increase_amount, 0) + NVL(s.contingent_amount, 0), 0)) asc_base_payment_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.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.payment_item_amount, 0), 0)) asc_final_payment_amount,

SUM(DECODE(s.regime_code, 'ASC842', NVL(s.interest_amount, 0), 0)) asc_interest_expense_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.adjusted_fin_rou_amount, 0), 0)) asc_adjusted_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_amount,

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_adjusted_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_amount,

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

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.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)) asc_accrued_flag,

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) - NVL(s.increase_amount, 0), 0)) jgap_base_payment_amount,

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

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

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.interest_amount, 0), 0)) jgap_interest_expense_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.adjusted_fin_rou_amount, 0), 0)) jgap_adjusted_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_amortization_fin_amount,

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.adjusted_oper_rou_amount, 0), 0)) jgap_adjusted_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_amount,

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)) jgap_accrued_flag

FROM (

SELECT sh.lease_id,

sh.org_id,

sh.lease_detail_id,

sh.lease_number,

sh.lease_name,

sh.version_num,

sh.payment_number,

sh.regime_code,

sh.currency_code,

sh.option_flag,

sh.payment_type_code,

sh.payment_purpose_code,

sh.option_type_code,

sh.asset_number,

sh.liability_flag,

sh.rou_asset_flag,

sh.schedule_type_code,

sh.revenue_method_code,

sh.lease_type_code,

ass.stage_request_id,

ass.number_of_accrual_days schedule_days,

NVL(ass.accrued_flag, 'N') accrued_flag,

ass.schedule_date,

ass.period_name,

ass.accrual_start_date schedule_start_date,

ass.schedule_date schedule_end_date,

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(NVL(lss.present_value_amount, 0)) 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,

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 la.lease_id,

la.org_id,

la.lease_number,

la.lease_name,

sh.lease_detail_id,

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

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

sh.payment_number,

sh.regime_code,

sh.amortization_start_date,

ph.currency_code,

ph.option_flag,

ph.payment_type_code,

ph.payment_purpose_code,

ph.option_type_code,

ph.asset_number,

ph.liability_flag,

ph.rou_asset_flag,

ph.schedule_type_code,

ph.revenue_method_code,

la.lease_type_code,

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

FROM fla_leases_all la,

fla_lease_details ld,

fla_lease_payment_headers ph,

fla_schedule_headers sh

WHERE la.lease_id = ld.lease_id

AND ld.version_type_code = 'F'

AND ld.lease_id = ph.lease_id

AND ld.lease_detail_id = ph.lease_detail_id

AND ph.lease_id = sh.lease_id

AND ph.lease_detail_id = sh.lease_detail_id

AND ph.payment_number = sh.payment_number

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

GROUP BY la.lease_id,

la.org_id,

la.lease_number,

la.lease_name,

sh.lease_detail_id,

sh.payment_number,

sh.regime_code,

sh.amortization_start_date,

ph.currency_code,

ph.option_flag,

ph.payment_type_code,

ph.payment_purpose_code,

ph.option_type_code,

ph.asset_number,

ph.liability_flag,

ph.rou_asset_flag,

ph.schedule_type_code,

ph.revenue_method_code,

la.lease_type_code

) sh,

(

SELECT accrual_schedule_id,

schedule_header_id,

accrual_start_date,

schedule_date,

period_name,

number_of_accrual_days,

interest_amount,

schedule1_amount fin_amortization,

schedule2_amount oper_amortization,

schedule3_amount lease_expense,

opening_fin_rou_amount,

closing_fin_rou_amount,

opening_oper_rou_amount,

closing_oper_rou_amount,

accrued_flag,

stage_request_id

FROM fla_accrual_schedules_stage ass

WHERE stage_request_id = -999

AND NOT EXISTS(

SELECT 1

FROM fla_accrual_schedules

WHERE schedule_header_id = ass.schedule_header_id

AND schedule_date = ass.schedule_date)

UNION ALL

SELECT accrual_schedule_id,

schedule_header_id,

accrual_start_date,

schedule_date,

period_name,

number_of_accrual_days,

interest_amount,

schedule1_amount fin_amortization,

schedule2_amount oper_amortization,

schedule3_amount lease_expense,

opening_fin_rou_amount,

closing_fin_rou_amount,

opening_oper_rou_amount,

closing_oper_rou_amount,

accrued_flag,

100 stage_request_id

FROM fla_accrual_schedules

WHERE accrual_reversal_flag = 'N'

) ass,

(

SELECT schedule_header_id,

liability_schedule_id,

liability_start_date,

liability_end_date,

period_name,

schedule_date,

payment_item_amount,

number_of_liability_days,

cumulative_number_of_days,

present_value_amount,

opening_liability_amount,

closing_liability_amount,

interest_amount,

interest_remainder_amount,

NVL(increase_amount,0) increase_amount,

NVL(contingent_amount,0) contingent_amount,

NVL(cumulative_increase_amount,0) cumulative_increase_amount,

NVL(cumulative_contingent_amount,0) cumulative_contingent_amount,

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

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

stage_request_id,

NVL(opening_short_term_liab_amount, 0) opening_short_term_liab_amount,

NVL(reclass_short_term_liab_amount, 0) reclass_short_term_liab_amount,

NVL(closing_short_term_liab_amount, 0) closing_short_term_liab_amount,

NVL(opening_short_term_liab_amount, 0) opening_long_term_liab_amount,

NVL(reclass_long_term_liab_amount, 0) reclass_long_term_liab_amount,

NVL(closing_long_term_liab_amount, 0) closing_long_term_liab_amount

FROM fla_liability_schedules_stage stg

WHERE stage_request_id = -999

AND NOT EXISTS(

SELECT 1

FROM fla_liability_schedules lsh

WHERE schedule_header_id = stg.schedule_header_id

AND schedule_date = stg.schedule_date)

UNION ALL

SELECT ls.schedule_header_id,

ls.liability_schedule_id,

ls.liability_start_date,

ls.liability_end_date,

ls.period_name,

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,

ls.interest_remainder_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,

NVL(opening_short_term_liab_amount, 0) opening_short_term_liab_amount,

NVL(reclass_short_term_liab_amount, 0) reclass_short_term_liab_amount,

NVL(closing_short_term_liab_amount, 0) closing_short_term_liab_amount,

NVL(opening_long_term_liab_amount, 0) opening_long_term_liab_amount,

NVL(reclass_long_term_liab_amount, 0) reclass_long_term_liab_amount,

NVL(closing_long_term_liab_amount, 0) closing_long_term_liab_amount

FROM fla_liability_schedules ls

) lss

WHERE 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.lease_id,

sh.org_id,

sh.lease_detail_id,

sh.lease_number,

sh.lease_name,

sh.version_num,

sh.payment_number,

sh.regime_code,

sh.currency_code,

sh.option_flag,

sh.payment_type_code,

sh.payment_purpose_code,

sh.option_type_code,

sh.asset_number,

sh.liability_flag,

sh.rou_asset_flag,

sh.schedule_type_code,

sh.revenue_method_code,

sh.lease_type_code,

ass.stage_request_id,

ass.number_of_accrual_days,

NVL(ass.accrued_flag, 'N'),

ass.schedule_date,

ass.period_name,

ass.accrual_start_date,

ass.schedule_date,

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

) s,

fnd_lookups pt,

fnd_lookups ot,

fnd_lookups pp

WHERE s.payment_type_code = pt.lookup_code(+)

AND s.option_type_code = ot.lookup_code(+)

AND s.payment_purpose_code = pp.lookup_code(+)

AND pt.lookup_type(+) = 'ORA_FLA_PAYMENT_TYPE'

AND ot.lookup_type(+) = 'ORA_FLA_LEASE_OPTION_TYPE'

AND pp.lookup_type(+) = 'ORA_FLA_PAYMENT_PURPOSE'

GROUP BY s.lease_id,

s.org_id,

s.lease_number,

s.lease_name,

s.lease_detail_id,

s.version_num,

s.asset_number,

DECODE(s.option_flag,'Y','OPTION','PAYMENT'),

s.payment_number,

pp.meaning,

pt.meaning,

ot.meaning,

s.currency_code,

s.liability_flag,

s.rou_asset_flag,

s.schedule_type_code,

s.revenue_method_code,

s.lease_type_code,

s.period_name,

s.schedule_date,

s.schedule_start_date,

s.schedule_end_date,

s.schedule_days,

DECODE(s.stage_request_id, -999, 'ondemand_schedules', 'main_schedules')