FLA_ACCRUAL_SCHEDULES

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
SCHEDULE_HEADER_ID PAYMENT_NUMBER LEASE_ID LEASE_DETAIL_ID VERSION_NUM REGIME_CODE AMORTIZATION_START_DATE AMORTIZATION_END_DATE ACTIVE_FLAG PARENT_RECORD_ID LAST_ACCRUED_DATE ADJUSTMENT_START_DATE TRANSACTION_TYPE_CODE ACCRUAL_START_DATE SCHEDULE_DATE PERIOD_NAME NUMBER_OF_ACCRUAL_DAYS INTEREST_AMOUNT FIN_AMORTIZATION OPENING_FIN_ROU_AMOUNT CLOSING_FIN_ROU_AMOUNT OPER_AMORTIZATION LEASE_EXPENSE OPENING_OPER_ROU_AMOUNT CLOSING_OPER_ROU_AMOUNT CATCHUP_FLAG CURRENCY_CODE ROU_ASSET_FLAG LIABILITY_FLAG STAGE_REQUEST_ID ADJUSTED_FIN_ROU_AMOUNT ADJUSTED_OPER_ROU_AMOUNT ADJUSTED_RESERVE_AMOUNT PERIOD_REMAINDER_AMOUNT SCHEDULE1_AMOUNT SCHEDULE1_REMAINDER_AMOUNT SCHEDULE2_AMOUNT SCHEDULE2_REMAINDER_AMOUNT SCHEDULE3_AMOUNT SCHEDULE3_REMAINDER_AMOUNT SCHEDULE4_AMOUNT SCHEDULE4_REMAINDER_AMOUNT SCHEDULE5_AMOUNT SCHEDULE5_REMAINDER_AMOUNT GENERATED_FROM_AMEND_FLAG ACCRUED_FLAG ACCRUAL_REVERSAL_FLAG EXPIRED_ROU_FLAG

Name

SCHEDULE_HEADER_ID

PAYMENT_NUMBER

LEASE_ID

LEASE_DETAIL_ID

VERSION_NUM

REGIME_CODE

AMORTIZATION_START_DATE

AMORTIZATION_END_DATE

ACTIVE_FLAG

PARENT_RECORD_ID

LAST_ACCRUED_DATE

ADJUSTMENT_START_DATE

TRANSACTION_TYPE_CODE

ACCRUAL_START_DATE

SCHEDULE_DATE

PERIOD_NAME

NUMBER_OF_ACCRUAL_DAYS

INTEREST_AMOUNT

FIN_AMORTIZATION

OPENING_FIN_ROU_AMOUNT

CLOSING_FIN_ROU_AMOUNT

OPER_AMORTIZATION

LEASE_EXPENSE

OPENING_OPER_ROU_AMOUNT

CLOSING_OPER_ROU_AMOUNT

CATCHUP_FLAG

CURRENCY_CODE

ROU_ASSET_FLAG

LIABILITY_FLAG

STAGE_REQUEST_ID

ADJUSTED_FIN_ROU_AMOUNT

ADJUSTED_OPER_ROU_AMOUNT

ADJUSTED_RESERVE_AMOUNT

PERIOD_REMAINDER_AMOUNT

SCHEDULE1_AMOUNT

SCHEDULE1_REMAINDER_AMOUNT

SCHEDULE2_AMOUNT

SCHEDULE2_REMAINDER_AMOUNT

SCHEDULE3_AMOUNT

SCHEDULE3_REMAINDER_AMOUNT

SCHEDULE4_AMOUNT

SCHEDULE4_REMAINDER_AMOUNT

SCHEDULE5_AMOUNT

SCHEDULE5_REMAINDER_AMOUNT

GENERATED_FROM_AMEND_FLAG

ACCRUED_FLAG

ACCRUAL_REVERSAL_FLAG

EXPIRED_ROU_FLAG

Query

SQL_Statement
SELECT sh.schedule_header_id , sh.payment_number , sh.lease_id , sh.lease_detail_id , sh.version_num , sh.regime_code , sh.amortization_start_date , sh.amortization_end_date , sh.active_flag , sh.parent_record_id , sh.last_accrued_date , sh.adjustment_start_date , sh.transaction_type_code , lss.accrual_start_date , lss.schedule_date , lss.period_name , lss.number_of_accrual_days , lss.interest_amount , lss.schedule1_amount fin_amortization , lss.opening_fin_rou_amount , lss.closing_fin_rou_amount , lss.schedule2_amount oper_amortization , lss.schedule3_amount lease_expense , lss.opening_oper_rou_amount , lss.closing_oper_rou_amount , lss.catchup_flag , ph.currency_code , ph.rou_asset_flag , ph.liability_flag , lss.stage_request_id, DECODE(sh.amortization_start_date,lss.accrual_start_date,sh.adjusted_fin_rou_amount,0) adjusted_fin_rou_amount, DECODE(sh.amortization_start_date,lss.accrual_start_date,sh.adjusted_oper_rou_amount,0) adjusted_oper_rou_amount, DECODE(sh.amortization_start_date,lss.accrual_start_date,sh.adjusted_reserve_amount,0) adjusted_reserve_amount, period_remainder_amount, schedule1_amount, schedule1_remainder_amount, schedule2_amount, schedule2_remainder_amount, schedule3_amount, schedule3_remainder_amount, schedule4_amount, schedule4_remainder_amount, schedule5_amount, schedule5_remainder_amount, generated_from_amend_flag, accrued_flag, accrual_reversal_flag, expired_rou_flag FROM ( SELECT sh.schedule_header_id, sh.payment_number , sh.lease_id , sh.lease_detail_id , sh.version_num , sh.regime_code , sh.amortization_start_date , sh.amortization_end_date , sh.active_flag , sh.parent_record_id , sh.last_accrued_date , sh.adjustment_start_date , sh.transaction_type_code , SUM(NVL(psh.adjusted_fin_rou_amount,0) + (-1 * NVL(psh.reduced_fin_rou_amount,0))) adjusted_fin_rou_amount, SUM(NVL(psh.adjusted_oper_rou_amount,0) + (-1 * NVL(psh.reduced_oper_rou_amount,0))) adjusted_oper_rou_amount, SUM(NVL(psh.adjusted_reserve_amount,0)) adjusted_reserve_amount FROM fla_schedule_headers sh, fla_schedule_headers psh WHERE sh.lease_id = psh.lease_id AND sh.payment_number = psh.payment_number AND sh.regime_code = psh.regime_code AND sh.amortization_start_date = psh.amortization_start_date AND NVL(psh.reversal_flag,'N') = 'N' GROUP BY sh.schedule_header_id , sh.payment_number , sh.lease_id , sh.lease_detail_id , sh.version_num , sh.regime_code , sh.amortization_start_date , sh.amortization_end_date , sh.active_flag , sh.parent_record_id , sh.last_accrued_date , sh.adjustment_start_date , sh.transaction_type_code ) sh , ( SELECT accrual_schedule_id, schedule_header_id, accrual_start_date, schedule_date, period_name, number_of_accrual_days, period_remainder_amount, interest_amount, schedule1_amount, schedule1_remainder_amount, schedule2_amount, schedule2_remainder_amount, schedule3_amount, schedule3_remainder_amount, schedule4_amount, schedule4_remainder_amount, schedule5_amount, schedule5_remainder_amount, opening_fin_rou_amount, closing_fin_rou_amount, opening_oper_rou_amount, closing_oper_rou_amount, catchup_flag , generated_from_amend_flag, accrued_flag, accrual_reversal_flag, stage_request_id, 'N' expired_rou_flag FROM fla_accrual_schedules_stage ass WHERE stage_request_id = -99 UNION ALL SELECT ass.accrual_schedule_id, ass.schedule_header_id, ass.accrual_start_date, ass.schedule_date, ass.period_name, ass.number_of_accrual_days, ass.period_remainder_amount, ass.interest_amount, ass.schedule1_amount, ass.schedule1_remainder_amount, ass.schedule2_amount, ass.schedule2_remainder_amount, ass.schedule3_amount, ass.schedule3_remainder_amount, ass.schedule4_amount, ass.schedule4_remainder_amount, ass.schedule5_amount, ass.schedule5_remainder_amount, ass.opening_fin_rou_amount, ass.closing_fin_rou_amount, ass.opening_oper_rou_amount, ass.closing_oper_rou_amount, ass.catchup_flag , ass.generated_from_amend_flag, ass.accrued_flag, ass.accrual_reversal_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) ) lss , fla_lease_payment_headers ph WHERE sh.schedule_header_id = lss.schedule_header_id and sh.payment_number = ph.payment_number AND sh.lease_detail_id = ph.lease_detail_id

SQL_Statement

SELECT sh.schedule_header_id ,

sh.payment_number ,

sh.lease_id ,

sh.lease_detail_id ,

sh.version_num ,

sh.regime_code ,

sh.amortization_start_date ,

sh.amortization_end_date ,

sh.active_flag ,

sh.parent_record_id ,

sh.last_accrued_date ,

sh.adjustment_start_date ,

sh.transaction_type_code ,

lss.accrual_start_date ,

lss.schedule_date ,

lss.period_name ,

lss.number_of_accrual_days ,

lss.interest_amount ,

lss.schedule1_amount fin_amortization ,

lss.opening_fin_rou_amount ,

lss.closing_fin_rou_amount ,

lss.schedule2_amount oper_amortization ,

lss.schedule3_amount lease_expense ,

lss.opening_oper_rou_amount ,

lss.closing_oper_rou_amount ,

lss.catchup_flag ,

ph.currency_code ,

ph.rou_asset_flag ,

ph.liability_flag ,

lss.stage_request_id,

DECODE(sh.amortization_start_date,lss.accrual_start_date,sh.adjusted_fin_rou_amount,0) adjusted_fin_rou_amount,

DECODE(sh.amortization_start_date,lss.accrual_start_date,sh.adjusted_oper_rou_amount,0) adjusted_oper_rou_amount,

DECODE(sh.amortization_start_date,lss.accrual_start_date,sh.adjusted_reserve_amount,0) adjusted_reserve_amount,

period_remainder_amount,

schedule1_amount,

schedule1_remainder_amount,

schedule2_amount,

schedule2_remainder_amount,

schedule3_amount,

schedule3_remainder_amount,

schedule4_amount,

schedule4_remainder_amount,

schedule5_amount,

schedule5_remainder_amount,

generated_from_amend_flag,

accrued_flag,

accrual_reversal_flag,

expired_rou_flag

FROM

(

SELECT sh.schedule_header_id,

sh.payment_number ,

sh.lease_id ,

sh.lease_detail_id ,

sh.version_num ,

sh.regime_code ,

sh.amortization_start_date ,

sh.amortization_end_date ,

sh.active_flag ,

sh.parent_record_id ,

sh.last_accrued_date ,

sh.adjustment_start_date ,

sh.transaction_type_code ,

SUM(NVL(psh.adjusted_fin_rou_amount,0) + (-1 * NVL(psh.reduced_fin_rou_amount,0))) adjusted_fin_rou_amount,

SUM(NVL(psh.adjusted_oper_rou_amount,0) + (-1 * NVL(psh.reduced_oper_rou_amount,0))) adjusted_oper_rou_amount,

SUM(NVL(psh.adjusted_reserve_amount,0)) adjusted_reserve_amount

FROM fla_schedule_headers sh,

fla_schedule_headers psh

WHERE sh.lease_id = psh.lease_id

AND sh.payment_number = psh.payment_number

AND sh.regime_code = psh.regime_code

AND sh.amortization_start_date = psh.amortization_start_date

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

GROUP BY sh.schedule_header_id ,

sh.payment_number ,

sh.lease_id ,

sh.lease_detail_id ,

sh.version_num ,

sh.regime_code ,

sh.amortization_start_date ,

sh.amortization_end_date ,

sh.active_flag ,

sh.parent_record_id ,

sh.last_accrued_date ,

sh.adjustment_start_date ,

sh.transaction_type_code

) sh ,

(

SELECT accrual_schedule_id,

schedule_header_id,

accrual_start_date,

schedule_date,

period_name,

number_of_accrual_days,

period_remainder_amount,

interest_amount,

schedule1_amount,

schedule1_remainder_amount,

schedule2_amount,

schedule2_remainder_amount,

schedule3_amount,

schedule3_remainder_amount,

schedule4_amount,

schedule4_remainder_amount,

schedule5_amount,

schedule5_remainder_amount,

opening_fin_rou_amount,

closing_fin_rou_amount,

opening_oper_rou_amount,

closing_oper_rou_amount,

catchup_flag ,

generated_from_amend_flag,

accrued_flag,

accrual_reversal_flag,

stage_request_id,

'N' expired_rou_flag

FROM fla_accrual_schedules_stage ass

WHERE stage_request_id = -99

UNION ALL

SELECT ass.accrual_schedule_id,

ass.schedule_header_id,

ass.accrual_start_date,

ass.schedule_date,

ass.period_name,

ass.number_of_accrual_days,

ass.period_remainder_amount,

ass.interest_amount,

ass.schedule1_amount,

ass.schedule1_remainder_amount,

ass.schedule2_amount,

ass.schedule2_remainder_amount,

ass.schedule3_amount,

ass.schedule3_remainder_amount,

ass.schedule4_amount,

ass.schedule4_remainder_amount,

ass.schedule5_amount,

ass.schedule5_remainder_amount,

ass.opening_fin_rou_amount,

ass.closing_fin_rou_amount,

ass.opening_oper_rou_amount,

ass.closing_oper_rou_amount,

ass.catchup_flag ,

ass.generated_from_amend_flag,

ass.accrued_flag,

ass.accrual_reversal_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)

) lss ,

fla_lease_payment_headers ph

WHERE sh.schedule_header_id = lss.schedule_header_id

and sh.payment_number = ph.payment_number

AND sh.lease_detail_id = ph.lease_detail_id