FLA_ACTIVE_SCHEDULE_HEADERS_V

Details

• Schema: FUSION

• Object owner: FUN

• Object type: VIEW

Columns

Name

LEASE_DETAIL_ID VERSION_NUM LEASE_ID VERSION_TYPE_CODE PAYMENT_NUMBER REGIME_CODE ACT_LEASE_DETAIL_ID SCHEDULE_HEADER_ID DRAFT_SCHEDULE_HEADER_ID SCHEDULE_VERSION_NUM AMORTIZATION_START_DATE AMORTIZATION_END_DATE ACT_AMORTIZATION_END_DATE NUMBER_OF_AMORTIZATION_DAYS INTEREST_RATE ADDER_RATE DAILY_COMPOUNDING_RATE LEASE_EXPENSE_RATE AMORTIZATION_RATE FORECASTED_LIABILITY_AMOUNT FORECASTED_FIN_ROU_AMOUNT FORECASTED_OPER_ROU_AMOUNT FORECASTED_RESERVE_AMOUNT FORECASTED_PAYMENT_AMOUNT FORECASTED_INTEREST_AMOUNT CLOSING_LIABILITY_AMOUNT CLOSING_FIN_ROU_AMOUNT CLOSING_OPER_ROU_AMOUNT CLOSING_PAYMENT_AMOUNT CLOSING_INTEREST_AMOUNT ADJUSTED_LIABILITY_AMOUNT ADJUSTED_FIN_ROU_AMOUNT ADJUSTED_OPER_ROU_AMOUNT ADJUSTED_RESERVE_AMOUNT GAIN_LOSS_AMOUNT TRANSACTION_TYPE_CODE LEASE_CLASSIFICATION_CODE ADJUSTMENT_START_DATE TRANSACTION_DATE ACCOUNTED_FLAG ACTIVE_FLAG REGENERATE_SCHEDULES_FLAG AMORTIZATION_PRORATION_CODE LAST_ACCRUED_DATE REQUEST_ID AMEND_PURPOSE_FLAG PARENT_RECORD_ID CURRENCY_CODE LIABILITY_FLAG ROU_ASSET_FLAG PAYMENT_FREQUENCY_CODE OPTION_FLAG SCHEDULE_TYPE TOT_FORECAST_LIABILITY_AMOUNT SCHEDULING_VERSION_NUM EXPIRATION_TRANSACTION_FLAG INTEREST_COMPOUNDING_CODE SIMPLE_INTEREST_RATE INTEREST_COMPOUNDING_DAYS END_OF_DAY_PAYMENT_FLAG LAST_PAYMENT_DATE

Query

SQL_Statement

SELECT ld.lease_detail_id, ld.version_num, ld.lease_id, ld.version_type_code, sh.payment_number payment_number, sh.regime_code regime_code, DECODE(r.column_value, 2, psh.lease_detail_id, sh.lease_detail_id) act_lease_detail_id, DECODE(r.column_value, 2, psh.schedule_header_id, sh.schedule_header_id) schedule_header_id, sh.schedule_header_id draft_schedule_header_id, DECODE(r.column_value, 2, psh.version_num, sh.version_num) schedule_version_num, DECODE(r.column_value, 2, psh.amortization_start_date, sh.amortization_start_date) amortization_start_date, DECODE(r.column_value, 2, LEAST(psh.amortization_end_date, sh.amortization_start_date-1), sh.amortization_end_date) amortization_end_date, DECODE(r.column_value, 2, psh.amortization_end_date, sh.amortization_end_date) act_amortization_end_date, DECODE(r.column_value, 2, psh.number_of_amortization_days, sh.number_of_amortization_days) number_of_amortization_days, DECODE(r.column_value, 2, psh.interest_rate, sh.interest_rate) interest_rate, DECODE(r.column_value, 2, psh.adder_rate, sh.adder_rate) adder_rate, DECODE(r.column_value, 2, psh.daily_compounding_rate, sh.daily_compounding_rate) daily_compounding_rate, DECODE(r.column_value, 2, psh.lease_expense_rate, sh.lease_expense_rate) lease_expense_rate, DECODE(r.column_value, 2, psh.amortization_rate, sh.amortization_rate) amortization_rate, DECODE(r.column_value, 2, psh.forecasted_liability_amount, sh.forecasted_liability_amount) forecasted_liability_amount, DECODE(r.column_value, 2, psh.forecasted_fin_rou_amount, sh.forecasted_fin_rou_amount) forecasted_fin_rou_amount, DECODE(r.column_value, 2, psh.forecasted_oper_rou_amount, sh.forecasted_oper_rou_amount) forecasted_oper_rou_amount, DECODE(r.column_value, 2, psh.forecasted_reserve_amount, sh.forecasted_reserve_amount) forecasted_reserve_amount, DECODE(r.column_value, 2, psh.forecasted_payment_amount, sh.forecasted_payment_amount) forecasted_payment_amount, DECODE(r.column_value, 2, psh.forecasted_interest_amount, sh.forecasted_interest_amount) forecasted_interest_amount, DECODE(r.column_value, 2, psh.closing_liability_amount, sh.closing_liability_amount) closing_liability_amount, DECODE(r.column_value, 2, psh.closing_fin_rou_amount, sh.closing_fin_rou_amount) closing_fin_rou_amount, DECODE(r.column_value, 2, psh.closing_oper_rou_amount, sh.closing_oper_rou_amount) closing_oper_rou_amount, DECODE(r.column_value, 2, psh.closing_payment_amount, sh.closing_payment_amount) closing_payment_amount, DECODE(r.column_value, 2, psh.closing_interest_amount, sh.closing_interest_amount) closing_interest_amount, DECODE(r.column_value, 2, psh.adjusted_liability_amount, sh.adjusted_liability_amount) adjusted_liability_amount, DECODE(r.column_value, 2, psh.adjusted_fin_rou_amount, sh.adjusted_fin_rou_amount) adjusted_fin_rou_amount, DECODE(r.column_value, 2, psh.adjusted_oper_rou_amount, sh.adjusted_oper_rou_amount) adjusted_oper_rou_amount, DECODE(r.column_value, 2, psh.adjusted_reserve_amount, sh.adjusted_reserve_amount) adjusted_reserve_amount, DECODE(r.column_value, 2, psh.gain_loss_amount, sh.gain_loss_amount) gain_loss_amount, DECODE(r.column_value, 2, psh.transaction_type_code, sh.transaction_type_code) transaction_type_code, DECODE(r.column_value, 2, psh.lease_classification_code, sh.lease_classification_code) lease_classification_code, DECODE(r.column_value, 2, psh.adjustment_start_date, sh.adjustment_start_date) adjustment_start_date, DECODE(r.column_value, 2, psh.transaction_date, sh.transaction_date) transaction_date, DECODE(r.column_value, 2, psh.accounted_flag, sh.accounted_flag) accounted_flag, sh.active_flag active_flag, sh.regenerate_schedules_flag regenerate_schedules_flag, DECODE(r.column_value, 2, psh.amortization_proration_code, sh.amortization_proration_code) amortization_proration_code, CASE WHEN r.column_value = 1 AND ld.version_type_code = 'F' THEN sh.last_accrued_date WHEN r.column_value = 1 AND sh.regenerate_schedules_flag = 'Y' THEN TO_DATE(NULL) WHEN r.column_value = 1 THEN (SELECT MAX(schedule_date) FROM fla_accrual_schedules_stage WHERE schedule_header_id = sh.schedule_header_id AND stage_request_id = -99) WHEN r.column_value = 2 AND sh.regenerate_schedules_flag = 'Y' AND sh.adjustment_start_date = psh.amortization_start_date THEN TO_DATE(NULL) WHEN r.column_value = 2 AND sh.regenerate_schedules_flag = 'Y' THEN sh.adjustment_start_date-1 WHEN r.column_value = 2 THEN (SELECT MAX(schedule_date) FROM fla_accrual_schedules_stage WHERE schedule_header_id = psh.schedule_header_id AND stage_request_id = -99) END last_accrued_date, DECODE(r.column_value, 2, psh.request_id, sh.request_id) request_id, DECODE(r.column_value, 2, 'Y', 'N') amend_purpose_flag, DECODE(r.column_value, 1, sh.parent_record_id, null) parent_record_id, ph.currency_code, ph.liability_flag, ph.rou_asset_flag, ph.payment_frequency_code, ph.option_flag, CASE WHEN r.column_value = 1 AND ld.version_type_code = 'D' THEN 'DRAFT' WHEN r.column_value = 2 AND ld.version_type_code = 'D' THEN 'AMEND' ELSE 'FINAL' END schedule_type, DECODE(r.column_value, 2, psh.tot_forecast_liability_amount, sh.tot_forecast_liability_amount) tot_forecast_liability_amount, DECODE(r.column_value, 2, psh.scheduling_version_num, sh.scheduling_version_num) scheduling_version_num, sh.expiration_transaction_flag, DECODE(r.column_value, 2, psh.interest_compounding_code, sh.interest_compounding_code) interest_compounding_code, DECODE(r.column_value, 2, psh.simple_interest_rate, sh.simple_interest_rate) simple_interest_rate, DECODE(r.column_value, 2, psh.interest_compounding_days, sh.interest_compounding_days) interest_compounding_days, DECODE(r.column_value, 2, psh.end_of_day_payment_flag, sh.end_of_day_payment_flag) end_of_day_payment_flag, DECODE(r.column_value, 2, psh.last_payment_date, sh.last_payment_date) last_payment_date FROM fla_lease_details ld, fla_schedule_headers sh, fla_lease_payment_headers ph, fla_schedule_headers psh, TABLE(fla_lease_utils_pkg.generate_rows(CASE WHEN ld.version_type_code = 'D' AND sh.parent_record_id IS NOT NULL THEN 2 ELSE 1 END)) r WHERE ld.lease_detail_id = sh.lease_detail_id AND ld.lease_id = sh.lease_id AND sh.active_flag = 'Y' 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 sh.parent_record_id = psh.schedule_header_id(+) AND sh.lease_id = psh.lease_id(+) AND sh.payment_number = psh.payment_number(+) AND sh.regime_code = psh.regime_code(+) UNION ALL SELECT ld.lease_detail_id, ld.version_num, ld.lease_id, ld.version_type_code, sh.payment_number payment_number, sh.regime_code regime_code, sh.lease_detail_id act_lease_detail_id, sh.schedule_header_id schedule_header_id, sh.schedule_header_id draft_schedule_header_id, sh.version_num schedule_version_num, sh.amortization_start_date amortization_start_date, sh.amortization_end_date amortization_end_date, sh.amortization_end_date act_amortization_end_date, sh.number_of_amortization_days number_of_amortization_days, sh.interest_rate interest_rate, sh.adder_rate adder_rate, sh.daily_compounding_rate daily_compounding_rate, sh.lease_expense_rate lease_expense_rate, sh.amortization_rate amortization_rate, sh.forecasted_liability_amount forecasted_liability_amount, sh.forecasted_fin_rou_amount forecasted_fin_rou_amount, sh.forecasted_oper_rou_amount forecasted_oper_rou_amount, sh.forecasted_reserve_amount forecasted_reserve_amount, sh.forecasted_payment_amount forecasted_payment_amount, sh.forecasted_interest_amount forecasted_interest_amount, sh.closing_liability_amount closing_liability_amount, sh.closing_fin_rou_amount closing_fin_rou_amount, sh.closing_oper_rou_amount closing_oper_rou_amount, sh.closing_payment_amount closing_payment_amount, sh.closing_interest_amount closing_interest_amount, sh.adjusted_liability_amount adjusted_liability_amount, sh.adjusted_fin_rou_amount adjusted_fin_rou_amount, sh.adjusted_oper_rou_amount adjusted_oper_rou_amount, sh.adjusted_reserve_amount adjusted_reserve_amount, sh.gain_loss_amount gain_loss_amount, sh.transaction_type_code transaction_type_code, sh.lease_classification_code lease_classification_code, sh.adjustment_start_date adjustment_start_date, sh.transaction_date transaction_date, sh.accounted_flag accounted_flag, sh.active_flag active_flag, 'Y' regenerate_schedules_flag, sh.amortization_proration_code amortization_proration_code, NVL((SELECT MAX(schedule_date) FROM fla_accrual_schedules_stage WHERE schedule_header_id = sh.schedule_header_id AND stage_request_id = -99), sh.last_accrued_date) last_accrued_date, sh.request_id request_id, 'N' amend_purpose_flag, sh.parent_record_id parent_record_id, ph.currency_code, ph.liability_flag, ph.rou_asset_flag, ph.payment_frequency_code, ph.option_flag, 'REPORTING' schedule_type, sh.tot_forecast_liability_amount, sh.scheduling_version_num, sh.expiration_transaction_flag, sh.interest_compounding_code, sh.simple_interest_rate, sh.interest_compounding_days, sh.end_of_day_payment_flag, sh.last_payment_date FROM fla_schedule_headers sh, fla_lease_payment_headers ph, fla_lease_details lf, fla_lease_details ld 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.lease_detail_id = lf.lease_detail_id AND lf.lease_id = ld.lease_id AND sh.active_flag = 'Y' AND lf.version_type_code = 'F' AND ld.version_type_code = 'D' AND ld.approval_status_code <> 'FINALIZED' AND NOT EXISTS( SELECT 1 FROM fla_schedule_headers dsh WHERE lease_detail_id = ld.lease_detail_id AND payment_number = sh.payment_number AND regime_code = sh.regime_code)