1. Tables and Views for Financials
  2. FLA_ACCRUED_SCHEDULES_V

FLA_ACCRUED_SCHEDULES_V

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

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_ACCRUED_STL_AMOUNT

IFRS_ACCRUED_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_ACCRUED_FLAG

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_ACCRUED_STL_AMOUNT

ASC_ACCRUED_LTL_AMOUNT

ASC_OPER_LEASE_REVENUE

ASC_OPER_NON_LEASE_REVENUE

ASC_OPER_UER_AMOUNT

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_FIN_AMORTIZATION_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_ACCRUED_STL_AMOUNT

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

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(DECODE(s.regime_code, 'IFRS16', NVL(s.accrued_stl_amount, 0), 0)) ifrs_accrued_stl_amount,

SUM(DECODE(s.regime_code, 'IFRS16', NVL(s.accrued_ltl_amount, 0), 0)) ifrs_accrued_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,

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

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(DECODE(s.regime_code, 'ASC842', NVL(s.accrued_stl_amount, 0), 0)) asc_accrued_stl_amount,

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

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_fin_amortization_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(DECODE(s.regime_code, 'JGAAP', NVL(s.accrued_stl_amount, 0), 0)) jgap_accrued_stl_amount,

SUM(DECODE(s.regime_code, 'JGAAP', NVL(s.accrued_ltl_amount, 0), 0)) jgap_accrued_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,

sh.primary_flag,

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

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,

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,

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,

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

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