FLA_EQP_ACCRUED_TRANSACTIONS

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID ORG_ID LEASE_TYPE_CODE LEASE_NUMBER LEASE_NAME LEASE_DETAIL_ID VERSION_NUM ASSET_NUMBER PAYMENT_NUMBER REGIME_CODE PRIMARY_FLAG SCHEDULE_TYPE_CODE PAYMENT_CATEGORY_CODE PAYMENT_PHASE_CODE PAYMENT_TYPE_CODE PAYMENT_PURPOSE_CODE CURRENCY_CODE LEASE_CLASSIFICATION_CODE PERIOD_NAME SCHEDULE_DATE SCHEDULE_START_DATE SCHEDULE_END_DATE SCHEDULE_DAYS OPENING_LIABILITY_AMOUNT INTEREST_AMOUNT CLOSING_LIABILITY_AMOUNT PRESENT_VALUE OPENING_FIN_ROU_AMOUNT FIN_AMORTIZATION_AMOUNT CLOSING_FIN_ROU_AMOUNT OPENING_OPER_ROU_AMOUNT OPER_AMORTIZATION_AMOUNT CLOSING_OPER_ROU_AMOUNT RENTAL_PAYMENT_AMOUNT OTHER_PAYMENT_AMOUNT RENTAL_LEASE_REVENUE OTHER_LEASE_REVENUE RENTAL_UER_AMOUNT OTHER_UER_AMOUNT ACCRUED_FLAG

Name

LEASE_ID

ORG_ID

LEASE_TYPE_CODE

LEASE_NUMBER

LEASE_NAME

LEASE_DETAIL_ID

VERSION_NUM

ASSET_NUMBER

PAYMENT_NUMBER

REGIME_CODE

PRIMARY_FLAG

SCHEDULE_TYPE_CODE

PAYMENT_CATEGORY_CODE

PAYMENT_PHASE_CODE

PAYMENT_TYPE_CODE

PAYMENT_PURPOSE_CODE

CURRENCY_CODE

LEASE_CLASSIFICATION_CODE

PERIOD_NAME

SCHEDULE_DATE

SCHEDULE_START_DATE

SCHEDULE_END_DATE

SCHEDULE_DAYS

OPENING_LIABILITY_AMOUNT

INTEREST_AMOUNT

CLOSING_LIABILITY_AMOUNT

PRESENT_VALUE

OPENING_FIN_ROU_AMOUNT

FIN_AMORTIZATION_AMOUNT

CLOSING_FIN_ROU_AMOUNT

OPENING_OPER_ROU_AMOUNT

OPER_AMORTIZATION_AMOUNT

CLOSING_OPER_ROU_AMOUNT

RENTAL_PAYMENT_AMOUNT

OTHER_PAYMENT_AMOUNT

RENTAL_LEASE_REVENUE

OTHER_LEASE_REVENUE

RENTAL_UER_AMOUNT

OTHER_UER_AMOUNT

ACCRUED_FLAG

Query

SQL_Statement
SELECT s.lease_id, s.org_id, s.lease_type_code, s.lease_number, s.lease_name, s.lease_detail_id, s.version_num, s.asset_number, s.payment_number, s.regime_code, s.primary_flag, s.schedule_type_code, s.payment_category_code, s.payment_phase_code, s.payment_type_code, s.payment_purpose_code, s.currency_code, s.lease_classification_code, s.period_name, s.schedule_date, s.schedule_start_date, s.schedule_end_date, s.schedule_days, 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, NVL(s.opening_fin_rou_amount, 0) opening_fin_rou_amount, NVL(s.fin_amortization, 0) fin_amortization_amount, 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_amount, 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, 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.payment_category_code, sh.payment_phase_code, sh.payment_type_code, sh.payment_purpose_code, sh.asset_number, sh.lease_classification_code, sh.schedule_type_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(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, NVL(ass.interest_amount, 0) interest_amount, ass.opening_fin_rou_amount, ass.schedule1_amount fin_amortization, ass.closing_fin_rou_amount, ass.opening_oper_rou_amount, ass.schedule2_amount oper_amortization, ass.closing_oper_rou_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.lease_classification_code) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) lease_classification_code, sh.regime_code, sh.amortization_start_date, sh.currency_code, DECODE(sh.payment_number, '00000', 'RENT', ph.payment_category_code) payment_category_code, ph.payment_phase_code, ph.payment_type_code, ph.payment_purpose_code, DECODE(sh.payment_number, '00000', sh.asset_number, ph.asset_number) asset_number, la.lease_type_code, sh.schedule_type_code, DECODE(sh.regime_code, so.primary_regime_code, 'Y', 'N') primary_flag 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 = sh.lease_id AND ld.lease_detail_id = sh.lease_detail_id AND NVL(sh.reversal_flag, 'N') = 'N' AND sh.lease_id = ph.lease_id (+) AND sh.lease_detail_id = ph.lease_detail_id (+) AND sh.payment_number = ph.payment_number (+) AND la.lease_type_code = 'REVENUE' AND la.lease_asset_type_code = 'EQUIPMENT' 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, sh.currency_code, DECODE(sh.payment_number, '00000', 'RENT', ph.payment_category_code), ph.payment_phase_code, ph.payment_type_code, ph.payment_purpose_code, DECODE(sh.payment_number, '00000', sh.asset_number, ph.asset_number), la.lease_type_code, sh.schedule_type_code, DECODE(sh.regime_code, so.primary_regime_code, 'Y', 'N') ) sh, fla_accrual_schedules ass, fla_liability_schedules lss 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(+) 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.payment_category_code, sh.payment_phase_code, sh.payment_type_code, sh.payment_purpose_code, sh.asset_number, sh.schedule_type_code, sh.lease_type_code, sh.primary_flag, 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 ) s

SQL_Statement

SELECT s.lease_id,

s.org_id,

s.lease_type_code,

s.lease_number,

s.lease_name,

s.lease_detail_id,

s.version_num,

s.asset_number,

s.payment_number,

s.regime_code,

s.primary_flag,

s.schedule_type_code,

s.payment_category_code,

s.payment_phase_code,

s.payment_type_code,

s.payment_purpose_code,

s.currency_code,

s.lease_classification_code,

s.period_name,

s.schedule_date,

s.schedule_start_date,

s.schedule_end_date,

s.schedule_days,

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,

NVL(s.opening_fin_rou_amount, 0) opening_fin_rou_amount,

NVL(s.fin_amortization, 0) fin_amortization_amount,

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

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,

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

sh.payment_phase_code,

sh.payment_type_code,

sh.payment_purpose_code,

sh.asset_number,

sh.lease_classification_code,

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

NVL(ass.interest_amount, 0) interest_amount,

ass.opening_fin_rou_amount,

ass.schedule1_amount fin_amortization,

ass.closing_fin_rou_amount,

ass.opening_oper_rou_amount,

ass.schedule2_amount oper_amortization,

ass.closing_oper_rou_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.lease_classification_code) KEEP(DENSE_RANK FIRST ORDER BY sh.version_num DESC) lease_classification_code,

sh.regime_code,

sh.amortization_start_date,

sh.currency_code,

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

ph.payment_phase_code,

ph.payment_type_code,

ph.payment_purpose_code,

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

la.lease_type_code,

sh.schedule_type_code,

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

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 = sh.lease_id

AND ld.lease_detail_id = sh.lease_detail_id

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

AND sh.lease_id = ph.lease_id (+)

AND sh.lease_detail_id = ph.lease_detail_id (+)

AND sh.payment_number = ph.payment_number (+)

AND la.lease_type_code = 'REVENUE'

AND la.lease_asset_type_code = 'EQUIPMENT'

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,

sh.currency_code,

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

ph.payment_phase_code,

ph.payment_type_code,

ph.payment_purpose_code,

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

la.lease_type_code,

sh.schedule_type_code,

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

) sh,

fla_accrual_schedules ass,

fla_liability_schedules lss

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(+)

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

sh.payment_phase_code,

sh.payment_type_code,

sh.payment_purpose_code,

sh.asset_number,

sh.schedule_type_code,

sh.lease_type_code,

sh.primary_flag,

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

) s