1. Tables and Views for Financials
  2. FLA_EQP_DRAFT_SCHEDULES_V

FLA_EQP_DRAFT_SCHEDULES_V

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_CATEGORY_CODE

PAYMENT_PHASE_CODE

ACCRUAL_METHOD_CODE

PAYMENT_TYPE_CODE

PAYMENT_PURPOSE_CODE

ASSET_NUMBER

REGIME_CODE

LEASE_CLASSIFICATION_CODE

PRIMARY_REGIME_FLAG

SCHEDULE_TYPE_CODE

CURRENCY_CODE

PERIOD_NAME

SCHEDULE_DATE

SCHEDULE_START_DATE

SCHEDULE_END_DATE

SCHEDULE_DAYS

ACCRUED_FLAG

OPENING_LIABILITY_AMOUNT

INTEREST_AMOUNT

CLOSING_LIABILITY_AMOUNT

PRESENT_VALUE_AMOUNT

OPENING_FIN_ROU_AMOUNT

FIN_AMORTIZATION

CLOSING_FIN_ROU_AMOUNT

OPENING_OPER_ROU_AMOUNT

OPER_AMORTIZATION

CLOSING_OPER_ROU_AMOUNT

RENTAL_PAYMENT_AMOUNT

OTHER_PAYMENT_AMOUNT

RENTAL_LEASE_REVENUE

OTHER_LEASE_REVENUE

RENTAL_UER_AMOUNT

OTHER_UER_AMOUNT

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

s.payment_phase_code,

s.accrual_method_code,

s.payment_type_code,

s.payment_purpose_code,

s.asset_number,

s.regime_code,

s.lease_classification_code,

s.primary_regime_flag,

s.schedule_type_code,

s.currency_code,

s.period_name,

s.schedule_date,

s.schedule_start_date,

s.schedule_end_date,

s.schedule_days,

s.accrued_flag,

NVL(s.opening_liability_amount, 0) opening_liability_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,

NVL(s.opening_fin_rou_amount, 0) opening_fin_rou_amount,

NVL(s.fin_amortization, 0) fin_amortization,

NVL(s.closing_fin_rou_amount, 0) closing_fin_rou_amount,

NVL(s.opening_oper_rou_amount, 0) opening_oper_rou_amount,

NVL(s.oper_amortization, 0) oper_amortization,

NVL(s.closing_oper_rou_amount, 0) closing_oper_rou_amount,

CASE WHEN s.payment_category_code = 'RENT' THEN NVL(s.payment_item_amount, 0) END rental_payment_amount,

CASE WHEN s.payment_category_code <> 'RENT' THEN NVL(s.payment_item_amount, 0) END other_payment_amount,

CASE WHEN s.payment_category_code <> 'RENT' THEN NULL

WHEN s.lease_classification_code = 'OPERATING' THEN NVL(s.oper_amortization, 0)

ELSE NVL(s.fin_amortization, 0)

END rental_lease_revenue,

CASE WHEN s.payment_category_code = 'RENT' THEN NULL

WHEN s.lease_classification_code = 'OPERATING' OR s.payment_category_code = 'OTHERS' THEN NVL(s.oper_amortization, 0)

ELSE NVL(s.fin_amortization, 0)

END other_lease_revenue,

CASE WHEN s.payment_category_code <> 'RENT' THEN NULL

WHEN s.lease_classification_code = 'OPERATING' THEN NVL(s.closing_oper_rou_amount, 0)

ELSE NVL(s.closing_fin_rou_amount, 0)

END rental_uer_amount,

CASE WHEN s.payment_category_code = 'RENT' THEN NULL

WHEN s.lease_classification_code = 'OPERATING' OR s.payment_category_code = 'OTHERS' THEN NVL(s.closing_oper_rou_amount, 0)

ELSE NVL(s.closing_fin_rou_amount, 0)

END other_uer_amount

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

DECODE(sh.payment_number, '00000', 'RENT', ph.payment_category_code) payment_category_code,

ph.payment_phase_code,

DECODE(sh.primary_regime_flag, 'Y', ph.primary_accrual_method_code, ph.secondary_accrual_method_code) accrual_method_code,

ph.payment_type_code,

ph.payment_purpose_code,

DECODE(sh.payment_number, '00000', sh.asset_number, ph.asset_number) asset_number,

sh.currency_code,

sh.schedule_type_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(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,

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.interest_amount accrued_interest_amount

FROM (

SELECT sh.lease_id,

sh.asset_number,

sh.payment_number,

sh.regime_code,

sh.amortization_start_date,

sh.schedule_type_code,

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,

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

MIN(sh.currency_code) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) currency_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'

AND la.lease_type_code = 'REVENUE'

AND la.lease_asset_type_code = 'EQUIPMENT'

GROUP BY sh.lease_id,

sh.asset_number,

sh.payment_number,

sh.regime_code,

sh.amortization_start_date,

sh.schedule_type_code,

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,

(

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

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

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 NVL(sh2.asset_number, 'XX') = NVL(sh1.asset_number, 'XX')

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,

stg.stage_request_id

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,

100 stage_request_id

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 NVL(sh2.asset_number, 'XX') = NVL(sh1.asset_number, 'XX')

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

DECODE(sh.payment_number, '00000', 'RENT', ph.payment_category_code),

ph.payment_phase_code,

DECODE(sh.primary_regime_flag, 'Y', ph.primary_accrual_method_code, ph.secondary_accrual_method_code),

ph.payment_type_code,

ph.payment_purpose_code,

DECODE(sh.payment_number, '00000', sh.asset_number, ph.asset_number),

sh.currency_code,

sh.schedule_type_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.interest_amount

) s