FLA_DRAFT_SCHEDULES

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID ORG_ID LEASE_DETAIL_ID LEASE_TYPE_CODE LEASE_NUMBER LEASE_NAME VERSION_NUM PAYMENT_NUMBER PAYMENT_TYPE_CODE PAYMENT_PURPOSE_CODE OPTION_TYPE_CODE ASSET_NUMBER REGIME_CODE LEASE_CLASSIFICATION_CODE PRIMARY_REGIME_FLAG 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 INTEREST_RATE ADDER_RATE DAILY_COMPOUNDING_RATE ADJUSTED_LIABILITY_AMOUNT OPENING_LIABILITY_AMOUNT BASE_PAYMENT_AMOUNT INCREASE_AMOUNT CONTINGENT_AMOUNT FINAL_PAYMENT_AMOUNT INTEREST_AMOUNT CLOSING_LIABILITY_AMOUNT PRESENT_VALUE_AMOUNT ADJUSTED_FIN_ROU_AMOUNT OPENING_FIN_ROU_AMOUNT FIN_AMORTIZATION CLOSING_FIN_ROU_AMOUNT ADJUSTED_OPER_ROU_AMOUNT OPENING_OPER_ROU_AMOUNT OPER_AMORTIZATION LEASE_EXPENSE CLOSING_OPER_ROU_AMOUNT OPENING_STL_AMOUNT RECLASS_STL_AMOUNT CLOSING_STL_AMOUNT OPENING_LTL_AMOUNT RECLASS_LTL_AMOUNT CLOSING_LTL_AMOUNT OPER_LEASE_REVENUE OPER_NON_LEASE_REVENUE OPER_UER_AMOUNT OPENING_EXEMPT_AMORT_AMOUNT EXEMPT_AMORT_AMOUNT CLOSING_EXEMPT_AMORT_AMOUNT AMORTIZE_EXEMPT_PAYMENT_FLAG ACCRUED_FLAG

Name

LEASE_ID

ORG_ID

LEASE_DETAIL_ID

LEASE_TYPE_CODE

LEASE_NUMBER

LEASE_NAME

VERSION_NUM

PAYMENT_NUMBER

PAYMENT_TYPE_CODE

PAYMENT_PURPOSE_CODE

OPTION_TYPE_CODE

ASSET_NUMBER

REGIME_CODE

LEASE_CLASSIFICATION_CODE

PRIMARY_REGIME_FLAG

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

INTEREST_RATE

ADDER_RATE

DAILY_COMPOUNDING_RATE

ADJUSTED_LIABILITY_AMOUNT

OPENING_LIABILITY_AMOUNT

BASE_PAYMENT_AMOUNT

INCREASE_AMOUNT

CONTINGENT_AMOUNT

FINAL_PAYMENT_AMOUNT

INTEREST_AMOUNT

CLOSING_LIABILITY_AMOUNT

PRESENT_VALUE_AMOUNT

ADJUSTED_FIN_ROU_AMOUNT

OPENING_FIN_ROU_AMOUNT

FIN_AMORTIZATION

CLOSING_FIN_ROU_AMOUNT

ADJUSTED_OPER_ROU_AMOUNT

OPENING_OPER_ROU_AMOUNT

OPER_AMORTIZATION

LEASE_EXPENSE

CLOSING_OPER_ROU_AMOUNT

OPENING_STL_AMOUNT

RECLASS_STL_AMOUNT

CLOSING_STL_AMOUNT

OPENING_LTL_AMOUNT

RECLASS_LTL_AMOUNT

CLOSING_LTL_AMOUNT

OPER_LEASE_REVENUE

OPER_NON_LEASE_REVENUE

OPER_UER_AMOUNT

OPENING_EXEMPT_AMORT_AMOUNT

EXEMPT_AMORT_AMOUNT

CLOSING_EXEMPT_AMORT_AMOUNT

AMORTIZE_EXEMPT_PAYMENT_FLAG

ACCRUED_FLAG

Query

SQL_Statement
SELECT s.lease_id, s.org_id, s.lease_detail_id, s.lease_type_code, s.lease_number, s.lease_name, s.version_num, s.payment_number, s.payment_type_code, s.payment_purpose_code, s.option_type_code, s.asset_number, s.regime_code, s.lease_classification_code, s.primary_regime_flag, s.schedule_type_code, s.revenue_method_code, s.currency_code, DECODE(s.schedule_type_code, 'EXEMPT', 'N', s.liability_flag) liability_flag, DECODE(s.schedule_type_code, 'EXEMPT', 'N', s.rou_asset_flag) rou_asset_flag, s.period_name, s.schedule_date, s.schedule_start_date, s.schedule_end_date, s.schedule_days, s.interest_rate, s.adder_rate, s.daily_compounding_rate, NVL(s.adjusted_liability_amount, 0) adjusted_liability_amount, NVL(s.opening_liability_amount, 0) opening_liability_amount, NVL(s.payment_item_amount, 0) - NVL(s.total_increase_amount, 0) + CASE WHEN s.regime_code = 'ASC842' AND s.lease_classification_code <> 'EXEMPT' THEN NVL(s.total_contingent_amount, 0) ELSE 0 END base_payment_amount, NVL(s.total_increase_amount, 0) increase_amount, CASE WHEN s.regime_code = 'ASC842' AND s.lease_classification_code <> 'EXEMPT' THEN NVL(s.total_contingent_amount, 0) ELSE 0 END contingent_amount, NVL(s.payment_item_amount, 0) final_payment_amount, NVL(s.interest_amount, 0) interest_amount, NVL(s.closing_liability_amount, 0) closing_liability_amount, NVL(s.present_value_amount, 0) present_value_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.adjusted_fin_rou_amount, 0) END adjusted_fin_rou_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.opening_fin_rou_amount, 0) END opening_fin_rou_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.fin_amortization, 0) END fin_amortization, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.closing_fin_rou_amount, 0) END closing_fin_rou_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.adjusted_oper_rou_amount, 0) END adjusted_oper_rou_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.opening_oper_rou_amount, 0) END opening_oper_rou_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.oper_amortization, 0) END oper_amortization, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.lease_expense, 0) END lease_expense, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.closing_oper_rou_amount, 0) END closing_oper_rou_amount, NVL(s.opening_stl_amount, 0) opening_stl_amount, NVL(s.reclass_stl_amount, 0) reclass_stl_amount, NVL(s.closing_stl_amount, 0) closing_stl_amount, NVL(s.opening_ltl_amount, 0) opening_ltl_amount, NVL(s.reclass_ltl_amount, 0) reclass_ltl_amount, NVL(s.closing_ltl_amount, 0) closing_ltl_amount, CASE WHEN s.revenue_method_code = 'LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END oper_lease_revenue, CASE WHEN s.revenue_method_code = 'NON_LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END oper_non_lease_revenue, CASE WHEN s.schedule_type_code = 'BILLING' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END oper_uer_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN NVL(s.opening_oper_rou_amount, 0) ELSE 0 END opening_exempt_amort_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN NVL(s.oper_amortization, 0) ELSE 0 END exempt_amort_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END closing_exempt_amort_amount, DECODE(s.lease_classification_code, 'EXEMPT', 'Y', 'N') amortize_exempt_payment_flag, s.accrued_flag FROM ( SELECT sh.payment_number, sh.lease_id, sh.org_id, sh.lease_number, sh.lease_name, sh.lease_type_code, sh.lease_detail_id, sh.version_num, sh.regime_code, sh.primary_regime_flag, sh.interest_rate, sh.adder_rate, sh.daily_compounding_rate, sh.lease_classification_code, ph.payment_type_code, ph.payment_purpose_code, ph.option_type_code, ph.asset_number, 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, la.org_id, la.lease_number, la.lease_name, la.lease_type_code, DECODE(sh.regime_code, so.primary_regime_code, 'Y', 'N') primary_regime_flag, 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, MIN(sh.lease_classification_code) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) lease_classification_code FROM fla_schedule_headers sh, fla_leases_all la, fla_system_options_all so WHERE sh.lease_id = la.lease_id AND la.org_id = so.org_id AND NVL(sh.reversal_flag,'N') = 'N' GROUP BY sh.lease_id, sh.payment_number, sh.regime_code, sh.amortization_start_date, la.org_id, la.lease_number, la.lease_name, la.lease_type_code, DECODE(sh.regime_code, so.primary_regime_code, 'Y', 'N') ) 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.org_id, sh.lease_number, sh.lease_name, sh.lease_type_code, sh.lease_detail_id, sh.version_num, sh.regime_code, sh.primary_regime_flag, sh.interest_rate, sh.adder_rate, sh.daily_compounding_rate, sh.lease_classification_code, ph.payment_type_code, ph.payment_purpose_code, ph.option_type_code, ph.asset_number, 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

SQL_Statement

SELECT s.lease_id,

s.org_id,

s.lease_detail_id,

s.lease_type_code,

s.lease_number,

s.lease_name,

s.version_num,

s.payment_number,

s.payment_type_code,

s.payment_purpose_code,

s.option_type_code,

s.asset_number,

s.regime_code,

s.lease_classification_code,

s.primary_regime_flag,

s.schedule_type_code,

s.revenue_method_code,

s.currency_code,

DECODE(s.schedule_type_code, 'EXEMPT', 'N', s.liability_flag) liability_flag,

DECODE(s.schedule_type_code, 'EXEMPT', 'N', s.rou_asset_flag) rou_asset_flag,

s.period_name,

s.schedule_date,

s.schedule_start_date,

s.schedule_end_date,

s.schedule_days,

s.interest_rate,

s.adder_rate,

s.daily_compounding_rate,

NVL(s.adjusted_liability_amount, 0) adjusted_liability_amount,

NVL(s.opening_liability_amount, 0) opening_liability_amount,

NVL(s.payment_item_amount, 0) - NVL(s.total_increase_amount, 0) + CASE WHEN s.regime_code = 'ASC842' AND s.lease_classification_code <> 'EXEMPT' THEN NVL(s.total_contingent_amount, 0) ELSE 0 END base_payment_amount,

NVL(s.total_increase_amount, 0) increase_amount,

CASE WHEN s.regime_code = 'ASC842' AND s.lease_classification_code <> 'EXEMPT' THEN NVL(s.total_contingent_amount, 0) ELSE 0 END contingent_amount,

NVL(s.payment_item_amount, 0) final_payment_amount,

NVL(s.interest_amount, 0) interest_amount,

NVL(s.closing_liability_amount, 0) closing_liability_amount,

NVL(s.present_value_amount, 0) present_value_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.adjusted_fin_rou_amount, 0) END adjusted_fin_rou_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.opening_fin_rou_amount, 0) END opening_fin_rou_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.fin_amortization, 0) END fin_amortization,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.closing_fin_rou_amount, 0) END closing_fin_rou_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.adjusted_oper_rou_amount, 0) END adjusted_oper_rou_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.opening_oper_rou_amount, 0) END opening_oper_rou_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.oper_amortization, 0) END oper_amortization,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.lease_expense, 0) END lease_expense,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.closing_oper_rou_amount, 0) END closing_oper_rou_amount,

NVL(s.opening_stl_amount, 0) opening_stl_amount,

NVL(s.reclass_stl_amount, 0) reclass_stl_amount,

NVL(s.closing_stl_amount, 0) closing_stl_amount,

NVL(s.opening_ltl_amount, 0) opening_ltl_amount,

NVL(s.reclass_ltl_amount, 0) reclass_ltl_amount,

NVL(s.closing_ltl_amount, 0) closing_ltl_amount,

CASE WHEN s.revenue_method_code = 'LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END oper_lease_revenue,

CASE WHEN s.revenue_method_code = 'NON_LEASE_REVENUE' THEN NVL(s.oper_amortization, 0) ELSE 0 END oper_non_lease_revenue,

CASE WHEN s.schedule_type_code = 'BILLING' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END oper_uer_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN NVL(s.opening_oper_rou_amount, 0) ELSE 0 END opening_exempt_amort_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN NVL(s.oper_amortization, 0) ELSE 0 END exempt_amort_amount,

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN NVL(s.closing_oper_rou_amount, 0) ELSE 0 END closing_exempt_amort_amount,

DECODE(s.lease_classification_code, 'EXEMPT', 'Y', 'N') amortize_exempt_payment_flag,

s.accrued_flag

FROM (

SELECT sh.payment_number,

sh.lease_id,

sh.org_id,

sh.lease_number,

sh.lease_name,

sh.lease_type_code,

sh.lease_detail_id,

sh.version_num,

sh.regime_code,

sh.primary_regime_flag,

sh.interest_rate,

sh.adder_rate,

sh.daily_compounding_rate,

sh.lease_classification_code,

ph.payment_type_code,

ph.payment_purpose_code,

ph.option_type_code,

ph.asset_number,

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,

la.org_id,

la.lease_number,

la.lease_name,

la.lease_type_code,

DECODE(sh.regime_code, so.primary_regime_code, 'Y', 'N') primary_regime_flag,

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,

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

FROM fla_schedule_headers sh,

fla_leases_all la,

fla_system_options_all so

WHERE sh.lease_id = la.lease_id

AND la.org_id = so.org_id

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

GROUP BY sh.lease_id,

sh.payment_number,

sh.regime_code,

sh.amortization_start_date,

la.org_id,

la.lease_number,

la.lease_name,

la.lease_type_code,

DECODE(sh.regime_code, so.primary_regime_code, 'Y', 'N')

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

sh.lease_number,

sh.lease_name,

sh.lease_type_code,

sh.lease_detail_id,

sh.version_num,

sh.regime_code,

sh.primary_regime_flag,

sh.interest_rate,

sh.adder_rate,

sh.daily_compounding_rate,

sh.lease_classification_code,

ph.payment_type_code,

ph.payment_purpose_code,

ph.option_type_code,

ph.asset_number,

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