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 PAYMENT_LEASE_DETAIL_ID ASSET_NUMBER

Query

SQL_Statement

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