FLA_ACCRUED_TRANSACTIONS

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 VERSION_NUM ASSET_NUMBER PAYMENT_OPTION PAYMENT_NUMBER REGIME_CODE SCHEDULE_TYPE_CODE REVENUE_METHOD_CODE PAYMENT_PURPOSE PAYMENT_TYPE OPTION_TYPE CURRENCY_CODE LIABILITY_FLAG ROU_ASSET_FLAG LEASE_CLASSIFICATION_CODE INTEREST_RATE ADDER_RATE DAILY_COMPOUNDING_RATE PERIOD_NAME SCHEDULE_DATE SCHEDULE_START_DATE SCHEDULE_END_DATE SCHEDULE_DAYS ADJUSTED_LIABILITY_AMOUNT OPENING_LIABILITY_AMOUNT BASE_PAYMENT_AMOUNT INCREASE_AMOUNT CONTINGENT_AMOUNT FINAL_PAYMENT_AMOUNT INTEREST_AMOUNT CLOSING_LIABILITY_AMOUNT PRESENT_VALUE OPENING_STL_AMOUNT RECLASS_STL_AMOUNT CLOSING_STL_AMOUNT OPENING_LTL_AMOUNT RECLASS_LTL_AMOUNT CLOSING_LTL_AMOUNT ACCRUED_STL_AMOUNT ACCRUED_LTL_AMOUNT ADJUSTED_FIN_ROU_AMOUNT OPENING_FIN_ROU_AMOUNT FIN_AMORTIZATION_AMOUNT CLOSING_FIN_ROU_AMOUNT ADJUSTED_OPER_ROU_AMOUNT OPENING_OPER_ROU_AMOUNT OPER_AMORTIZATION_AMOUNT OPER_LEASE_EXPENSE_AMOUNT CLOSING_OPER_ROU_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

BU_ID

LEASE_TYPE_CODE

LEASE_NUMBER

LEASE_NAME

LEASE_DETAIL_ID

VERSION_NUM

ASSET_NUMBER

PAYMENT_OPTION

PAYMENT_NUMBER

REGIME_CODE

SCHEDULE_TYPE_CODE

REVENUE_METHOD_CODE

PAYMENT_PURPOSE

PAYMENT_TYPE

OPTION_TYPE

CURRENCY_CODE

LIABILITY_FLAG

ROU_ASSET_FLAG

LEASE_CLASSIFICATION_CODE

INTEREST_RATE

ADDER_RATE

DAILY_COMPOUNDING_RATE

PERIOD_NAME

SCHEDULE_DATE

SCHEDULE_START_DATE

SCHEDULE_END_DATE

SCHEDULE_DAYS

ADJUSTED_LIABILITY_AMOUNT

OPENING_LIABILITY_AMOUNT

BASE_PAYMENT_AMOUNT

INCREASE_AMOUNT

CONTINGENT_AMOUNT

FINAL_PAYMENT_AMOUNT

INTEREST_AMOUNT

CLOSING_LIABILITY_AMOUNT

PRESENT_VALUE

OPENING_STL_AMOUNT

RECLASS_STL_AMOUNT

CLOSING_STL_AMOUNT

OPENING_LTL_AMOUNT

RECLASS_LTL_AMOUNT

CLOSING_LTL_AMOUNT

ACCRUED_STL_AMOUNT

ACCRUED_LTL_AMOUNT

ADJUSTED_FIN_ROU_AMOUNT

OPENING_FIN_ROU_AMOUNT

FIN_AMORTIZATION_AMOUNT

CLOSING_FIN_ROU_AMOUNT

ADJUSTED_OPER_ROU_AMOUNT

OPENING_OPER_ROU_AMOUNT

OPER_AMORTIZATION_AMOUNT

OPER_LEASE_EXPENSE_AMOUNT

CLOSING_OPER_ROU_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 bu_id, s.lease_type_code, s.lease_number, s.lease_name, s.lease_detail_id, s.version_num, s.asset_number, DECODE(s.option_flag,'Y','OPTION','PAYMENT') payment_option, s.payment_number, s.regime_code, s.schedule_type_code, s.revenue_method_code, pp.meaning payment_purpose, pt.meaning payment_type, ot.meaning option_type, 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.lease_classification_code, s.interest_rate, s.adder_rate, s.daily_compounding_rate, s.period_name, s.schedule_date, s.schedule_start_date, s.schedule_end_date, s.schedule_days, 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, 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, NVL(s.accrued_stl_amount, 0) accrued_stl_amount, NVL(s.accrued_ltl_amount, 0) accrued_ltl_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_amount, 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_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.lease_expense, 0) END oper_lease_expense_amount, CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.closing_oper_rou_amount, 0) END closing_oper_rou_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.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.lease_classification_code, sh.schedule_type_code, sh.revenue_method_code, sh.lease_type_code, sh.primary_flag, sh.interest_rate, sh.adder_rate, sh.daily_compounding_rate, 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, 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.increase_amount,0) + NVL(lss.cumulative_increase_amount,0)) total_increase_amount, SUM(NVL(lss.contingent_amount,0) + NVL(lss.cumulative_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, NVL(ass.interest_amount, 0) interest_amount, ass.opening_fin_rou_amount, ass.schedule1_amount 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.schedule2_amount oper_amortization, ass.closing_oper_rou_amount, ass.schedule2_amount 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, fat.short_term_liability_amount accrued_stl_amount, fat.long_term_liability_amount accrued_ltl_amount FROM ( SELECT la.lease_id, la.org_id, la.lease_number, la.lease_name, sh.lease_detail_id, sh.payment_number, 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, 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, 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, la.lease_type_code, ph.schedule_type_code, ph.revenue_method_code, DECODE(sh.regime_code, so.primary_regime_code, 'Y', 'N') primary_flag, 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_system_options_all so, fla_lease_details ld, fla_lease_payment_headers ph, fla_schedule_headers sh WHERE la.org_id = so.org_id AND 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, la.lease_type_code, ph.schedule_type_code, ph.revenue_method_code, DECODE(sh.regime_code, so.primary_regime_code, 'Y', 'N') ) sh, fla_accrual_schedules ass, fla_liability_schedules lss, fla_accounting_transactions fat WHERE sh.schedule_header_id = ass.schedule_header_id AND ass.accrual_reversal_flag = 'N' AND sh.schedule_header_id = lss.schedule_header_id(+) AND ass.schedule_date = lss.schedule_date(+) AND ass.schedule_header_id = fat.schedule_header_id(+) AND ass.schedule_date = fat.schedule_date(+) AND fat.transaction_type_code(+) = 'LIABILITY_RECLASS' AND fat.accounted_flag(+) = 'Y' AND fat.reversal_flag(+) = 'N' 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, sh.primary_flag, sh.interest_rate, sh.adder_rate, sh.daily_compounding_rate, sh.lease_classification_code, ass.number_of_accrual_days, NVL(ass.accrued_flag, 'N'), ass.schedule_date, ass.period_name, ass.accrual_start_date, ass.schedule_date, ass.interest_amount, ass.opening_fin_rou_amount, ass.schedule1_amount, ass.closing_fin_rou_amount, ass.opening_oper_rou_amount, ass.schedule2_amount, ass.closing_oper_rou_amount, ass.schedule3_amount, fat.short_term_liability_amount, fat.long_term_liability_amount ) 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'

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,

s.version_num,

s.asset_number,

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

s.payment_number,

s.regime_code,

s.schedule_type_code,

s.revenue_method_code,

pp.meaning payment_purpose,

pt.meaning payment_type,

ot.meaning option_type,

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.lease_classification_code,

s.interest_rate,

s.adder_rate,

s.daily_compounding_rate,

s.period_name,

s.schedule_date,

s.schedule_start_date,

s.schedule_end_date,

s.schedule_days,

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,

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,

NVL(s.accrued_stl_amount, 0) accrued_stl_amount,

NVL(s.accrued_ltl_amount, 0) accrued_ltl_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_amount,

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

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

CASE WHEN s.lease_classification_code = 'EXEMPT' THEN 0 ELSE NVL(s.closing_oper_rou_amount, 0) END closing_oper_rou_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.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.lease_classification_code,

sh.schedule_type_code,

sh.revenue_method_code,

sh.lease_type_code,

sh.primary_flag,

sh.interest_rate,

sh.adder_rate,

sh.daily_compounding_rate,

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,

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.increase_amount,0) + NVL(lss.cumulative_increase_amount,0)) total_increase_amount,

SUM(NVL(lss.contingent_amount,0) + NVL(lss.cumulative_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,

NVL(ass.interest_amount, 0) interest_amount,

ass.opening_fin_rou_amount,

ass.schedule1_amount 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.schedule2_amount oper_amortization,

ass.closing_oper_rou_amount,

ass.schedule2_amount 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,

fat.short_term_liability_amount accrued_stl_amount,

fat.long_term_liability_amount accrued_ltl_amount

FROM (

SELECT la.lease_id,

la.org_id,

la.lease_number,

la.lease_name,

sh.lease_detail_id,

sh.payment_number,

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,

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,

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,

la.lease_type_code,

ph.schedule_type_code,

ph.revenue_method_code,

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

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_system_options_all so,

fla_lease_details ld,

fla_lease_payment_headers ph,

fla_schedule_headers sh

WHERE la.org_id = so.org_id

AND 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,

la.lease_type_code,

ph.schedule_type_code,

ph.revenue_method_code,

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

) sh,

fla_accrual_schedules ass,

fla_liability_schedules lss,

fla_accounting_transactions fat

WHERE sh.schedule_header_id = ass.schedule_header_id

AND ass.accrual_reversal_flag = 'N'

AND sh.schedule_header_id = lss.schedule_header_id(+)

AND ass.schedule_date = lss.schedule_date(+)

AND ass.schedule_header_id = fat.schedule_header_id(+)

AND ass.schedule_date = fat.schedule_date(+)

AND fat.transaction_type_code(+) = 'LIABILITY_RECLASS'

AND fat.accounted_flag(+) = 'Y'

AND fat.reversal_flag(+) = 'N'

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,

sh.primary_flag,

sh.interest_rate,

sh.adder_rate,

sh.daily_compounding_rate,

sh.lease_classification_code,

ass.number_of_accrual_days,

NVL(ass.accrued_flag, 'N'),

ass.schedule_date,

ass.period_name,

ass.accrual_start_date,

ass.schedule_date,

ass.interest_amount,

ass.opening_fin_rou_amount,

ass.schedule1_amount,

ass.closing_fin_rou_amount,

ass.opening_oper_rou_amount,

ass.schedule2_amount,

ass.closing_oper_rou_amount,

ass.schedule3_amount,

fat.short_term_liability_amount,

fat.long_term_liability_amount

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