FLA_LIABILITY_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 DAILY_COMPOUNDING_RATE LIABILITY_SCHEDULE_ID LIABILITY_START_DATE LIABILITY_END_DATE PERIOD_NAME SCHEDULE_DATE PAYMENT_ITEM_AMOUNT NUMBER_OF_LIABILITY_DAYS CUMULATIVE_NUMBER_OF_DAYS PRESENT_VALUE_AMOUNT OPENING_LIABILITY_AMOUNT CLOSING_LIABILITY_AMOUNT INTEREST_AMOUNT INTEREST_REMAINDER_AMOUNT CATCHUP_FLAG CURRENCY_CODE ROU_ASSET_FLAG LIABILITY_FLAG ORG_PRIMARY_REGIME_CODE ORG_SECONDARY_REGIME_CODE ORG_ID LEDGER_ID SECONDARY_LEDGER_ID STAGE_REQUEST_ID ADJUSTED_LIABILITY_AMOUNT PRIMARY_REGIME_FLAG INCREASE_AMOUNT CONTINGENT_AMOUNT CUMULATIVE_INCREASE_AMOUNT CUMULATIVE_CONTINGENT_AMOUNT TOTAL_INCREASE_AMOUNT TOTAL_CONTINGENT_AMOUNT SCHEDULE_START_DATE SCHEDULE_END_DATE SCHEDULE_DAYS CUMULATIVE_SCHEDULE_DAYS UNROUNDED_PRESENT_VALUE_AMOUNT PRECISION EXTENDED_PRECISION OPENING_SHORT_TERM_LIAB_AMOUNT RECLASS_SHORT_TERM_LIAB_AMOUNT CLOSING_SHORT_TERM_LIAB_AMOUNT OPENING_LONG_TERM_LIAB_AMOUNT RECLASS_LONG_TERM_LIAB_AMOUNT CLOSING_LONG_TERM_LIAB_AMOUNT PAYMENT_TYPE_CODE PAYMENT_PURPOSE_CODE OPTION_TYPE_CODE OPTION_FLAG BASE_PAYMENT_AMOUNT PAYMENT_ITEM_DATE UPFRONT_TAX_AMOUNT

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

DAILY_COMPOUNDING_RATE

LIABILITY_SCHEDULE_ID

LIABILITY_START_DATE

LIABILITY_END_DATE

PERIOD_NAME

SCHEDULE_DATE

PAYMENT_ITEM_AMOUNT

NUMBER_OF_LIABILITY_DAYS

CUMULATIVE_NUMBER_OF_DAYS

PRESENT_VALUE_AMOUNT

OPENING_LIABILITY_AMOUNT

CLOSING_LIABILITY_AMOUNT

INTEREST_AMOUNT

INTEREST_REMAINDER_AMOUNT

CATCHUP_FLAG

CURRENCY_CODE

ROU_ASSET_FLAG

LIABILITY_FLAG

ORG_PRIMARY_REGIME_CODE

ORG_SECONDARY_REGIME_CODE

ORG_ID

LEDGER_ID

SECONDARY_LEDGER_ID

STAGE_REQUEST_ID

ADJUSTED_LIABILITY_AMOUNT

PRIMARY_REGIME_FLAG

INCREASE_AMOUNT

CONTINGENT_AMOUNT

CUMULATIVE_INCREASE_AMOUNT

CUMULATIVE_CONTINGENT_AMOUNT

TOTAL_INCREASE_AMOUNT

TOTAL_CONTINGENT_AMOUNT

SCHEDULE_START_DATE

SCHEDULE_END_DATE

SCHEDULE_DAYS

CUMULATIVE_SCHEDULE_DAYS

UNROUNDED_PRESENT_VALUE_AMOUNT

PRECISION

EXTENDED_PRECISION

OPENING_SHORT_TERM_LIAB_AMOUNT

RECLASS_SHORT_TERM_LIAB_AMOUNT

CLOSING_SHORT_TERM_LIAB_AMOUNT

OPENING_LONG_TERM_LIAB_AMOUNT

RECLASS_LONG_TERM_LIAB_AMOUNT

CLOSING_LONG_TERM_LIAB_AMOUNT

PAYMENT_TYPE_CODE

PAYMENT_PURPOSE_CODE

OPTION_TYPE_CODE

OPTION_FLAG

BASE_PAYMENT_AMOUNT

PAYMENT_ITEM_DATE

UPFRONT_TAX_AMOUNT

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, sh.daily_compounding_rate, lss.liability_schedule_id, lss.liability_start_date, lss.liability_end_date, lss.period_name, lss.schedule_date, lss.payment_item_amount, lss.number_of_liability_days, lss.cumulative_number_of_days, ROUND(lss.present_value_amount, fc.extended_precision) present_value_amount, lss.opening_liability_amount, lss.closing_liability_amount, lss.interest_amount, lss.interest_remainder_amount, lss.catchup_flag, ph.currency_code, ph.rou_asset_flag, ph.liability_flag, so.primary_regime_code org_primary_regime_code, so.secondary_regime_code org_secondary_regime_code, so.org_id, so.ledger_id, so.secondary_ledger_id, lss.stage_request_id, DECODE(sh.amortization_start_date,lss.liability_start_date,sh.adjusted_liability_amount,0) adjusted_liability_amount, DECODE(so.primary_regime_code, sh.regime_code, 'Y', 'N') primary_regime_flag, lss.increase_amount, CASE WHEN sh.regime_code = 'ASC842' THEN NVL(lss.total_contingent_amount, 0) ELSE 0 END contingent_amount, lss.cumulative_increase_amount, lss.cumulative_contingent_amount, lss.total_increase_amount, lss.total_contingent_amount, MIN(lss.liability_start_date) OVER (PARTITION BY sh.schedule_header_id, lss.schedule_date) schedule_start_date, MAX(lss.liability_end_date) OVER (PARTITION BY sh.schedule_header_id, lss.schedule_date) schedule_end_date, SUM(lss.number_of_liability_days) OVER (PARTITION BY sh.schedule_header_id, lss.schedule_date) schedule_days, MAX(lss.cumulative_number_of_days) OVER (PARTITION BY sh.schedule_header_id, lss.schedule_date) cumulative_schedule_days, lss.present_value_amount unrounded_present_value_amount, fc.precision, fc.extended_precision, NVL(lss.opening_short_term_liab_amount, 0) opening_short_term_liab_amount, NVL(lss.reclass_short_term_liab_amount, 0) reclass_short_term_liab_amount, NVL(lss.closing_short_term_liab_amount, 0) closing_short_term_liab_amount, NVL(lss.opening_long_term_liab_amount, 0) opening_long_term_liab_amount, NVL(lss.reclass_long_term_liab_amount, 0) reclass_long_term_liab_amount, NVL(lss.closing_long_term_liab_amount, 0) closing_long_term_liab_amount, ph.payment_type_code, ph.payment_purpose_code, ph.option_type_code, ph.option_flag, NVL(lss.payment_item_amount, 0) - NVL(lss.total_increase_amount, 0) + CASE WHEN sh.regime_code = 'ASC842' THEN NVL(lss.total_contingent_amount, 0) ELSE 0 END base_payment_amount, lss.payment_item_date, lss.upfront_tax_amount 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, sh.daily_compounding_rate, SUM(NVL(psh.adjusted_liability_amount,0) + (-1 * NVL(psh.reduced_liability_amount,0))) adjusted_liability_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.lease_classification_code <> 'EXEMPT' 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.daily_compounding_rate) sh, (SELECT schedule_header_id, liability_schedule_id, liability_start_date, liability_end_date, period_name, schedule_date, payment_item_amount, number_of_liability_days, cumulative_number_of_days, present_value_amount, opening_liability_amount, closing_liability_amount, interest_amount, interest_remainder_amount, NVL(increase_amount,0) increase_amount, NVL(contingent_amount,0) contingent_amount, NVL(cumulative_increase_amount,0) cumulative_increase_amount, NVL(cumulative_contingent_amount,0) cumulative_contingent_amount, NVL(increase_amount,0) + NVL(cumulative_increase_amount,0) total_increase_amount, NVL(contingent_amount,0) + NVL(cumulative_contingent_amount,0) total_contingent_amount, catchup_flag, payment_item_date, upfront_tax_amount, stage_request_id, opening_short_term_liab_amount, reclass_short_term_liab_amount, closing_short_term_liab_amount, opening_long_term_liab_amount, reclass_long_term_liab_amount, closing_long_term_liab_amount FROM fla_liability_schedules_stage stg WHERE stage_request_id = -99 UNION ALL SELECT ls.schedule_header_id, ls.liability_schedule_id, ls.liability_start_date, ls.liability_end_date, ls.period_name, 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, ls.interest_remainder_amount, NVL(ls.increase_amount,0) increase_amount, NVL(ls.contingent_amount,0) contingent_amount, NVL(ls.cumulative_increase_amount,0) cumulative_increase_amount, NVL(ls.cumulative_contingent_amount,0) cumulative_contingent_amount, NVL(ls.increase_amount,0) + NVL(ls.cumulative_increase_amount,0) total_increase_amount, NVL(ls.contingent_amount,0) + NVL(ls.cumulative_contingent_amount,0) total_contingent_amount, ls.catchup_flag, ls.payment_item_date, ls.upfront_tax_amount, 100 stage_request_id, opening_short_term_liab_amount, reclass_short_term_liab_amount, closing_short_term_liab_amount, opening_long_term_liab_amount, reclass_long_term_liab_amount, closing_long_term_liab_amount 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 sh2.regime_code = sh1.regime_code AND sh2.lease_id = sh1.lease_id AND lst.stage_request_id = -99),ls.schedule_date+1) ) lss, fla_lease_payment_headers ph, fla_leases_all fl, fla_system_options_all so, fla_currencies_v fc WHERE fl.lease_id = ph.lease_id AND ph.lease_id = sh.lease_id AND ph.lease_detail_id = sh.lease_detail_id AND ph.payment_number = sh.payment_number and sh.schedule_header_id = lss.schedule_header_id AND fl.org_id = so.org_id AND ph.currency_code = fc.currency_code AND ph.liability_flag = 'Y'

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,

sh.daily_compounding_rate,

lss.liability_schedule_id,

lss.liability_start_date,

lss.liability_end_date,

lss.period_name,

lss.schedule_date,

lss.payment_item_amount,

lss.number_of_liability_days,

lss.cumulative_number_of_days,

ROUND(lss.present_value_amount, fc.extended_precision) present_value_amount,

lss.opening_liability_amount,

lss.closing_liability_amount,

lss.interest_amount,

lss.interest_remainder_amount,

lss.catchup_flag,

ph.currency_code,

ph.rou_asset_flag,

ph.liability_flag,

so.primary_regime_code org_primary_regime_code,

so.secondary_regime_code org_secondary_regime_code,

so.org_id,

so.ledger_id,

so.secondary_ledger_id,

lss.stage_request_id,

DECODE(sh.amortization_start_date,lss.liability_start_date,sh.adjusted_liability_amount,0) adjusted_liability_amount,

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

lss.increase_amount,

CASE WHEN sh.regime_code = 'ASC842' THEN NVL(lss.total_contingent_amount, 0) ELSE 0 END contingent_amount,

lss.cumulative_increase_amount,

lss.cumulative_contingent_amount,

lss.total_increase_amount,

lss.total_contingent_amount,

MIN(lss.liability_start_date) OVER (PARTITION BY sh.schedule_header_id, lss.schedule_date) schedule_start_date,

MAX(lss.liability_end_date) OVER (PARTITION BY sh.schedule_header_id, lss.schedule_date) schedule_end_date,

SUM(lss.number_of_liability_days) OVER (PARTITION BY sh.schedule_header_id, lss.schedule_date) schedule_days,

MAX(lss.cumulative_number_of_days) OVER (PARTITION BY sh.schedule_header_id, lss.schedule_date) cumulative_schedule_days,

lss.present_value_amount unrounded_present_value_amount,

fc.precision,

fc.extended_precision,

NVL(lss.opening_short_term_liab_amount, 0) opening_short_term_liab_amount,

NVL(lss.reclass_short_term_liab_amount, 0) reclass_short_term_liab_amount,

NVL(lss.closing_short_term_liab_amount, 0) closing_short_term_liab_amount,

NVL(lss.opening_long_term_liab_amount, 0) opening_long_term_liab_amount,

NVL(lss.reclass_long_term_liab_amount, 0) reclass_long_term_liab_amount,

NVL(lss.closing_long_term_liab_amount, 0) closing_long_term_liab_amount,

ph.payment_type_code,

ph.payment_purpose_code,

ph.option_type_code,

ph.option_flag,

NVL(lss.payment_item_amount, 0) - NVL(lss.total_increase_amount, 0) + CASE WHEN sh.regime_code = 'ASC842' THEN NVL(lss.total_contingent_amount, 0) ELSE 0 END base_payment_amount,

lss.payment_item_date,

lss.upfront_tax_amount

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,

sh.daily_compounding_rate,

SUM(NVL(psh.adjusted_liability_amount,0) + (-1 * NVL(psh.reduced_liability_amount,0))) adjusted_liability_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.lease_classification_code <> 'EXEMPT'

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.daily_compounding_rate) sh,

(SELECT schedule_header_id,

liability_schedule_id,

liability_start_date,

liability_end_date,

period_name,

schedule_date,

payment_item_amount,

number_of_liability_days,

cumulative_number_of_days,

present_value_amount,

opening_liability_amount,

closing_liability_amount,

interest_amount,

interest_remainder_amount,

NVL(increase_amount,0) increase_amount,

NVL(contingent_amount,0) contingent_amount,

NVL(cumulative_increase_amount,0) cumulative_increase_amount,

NVL(cumulative_contingent_amount,0) cumulative_contingent_amount,

NVL(increase_amount,0) + NVL(cumulative_increase_amount,0) total_increase_amount,

NVL(contingent_amount,0) + NVL(cumulative_contingent_amount,0) total_contingent_amount,

catchup_flag,

payment_item_date,

upfront_tax_amount,

stage_request_id,

opening_short_term_liab_amount,

reclass_short_term_liab_amount,

closing_short_term_liab_amount,

opening_long_term_liab_amount,

reclass_long_term_liab_amount,

closing_long_term_liab_amount

FROM fla_liability_schedules_stage stg

WHERE stage_request_id = -99

UNION ALL

SELECT ls.schedule_header_id,

ls.liability_schedule_id,

ls.liability_start_date,

ls.liability_end_date,

ls.period_name,

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,

ls.interest_remainder_amount,

NVL(ls.increase_amount,0) increase_amount,

NVL(ls.contingent_amount,0) contingent_amount,

NVL(ls.cumulative_increase_amount,0) cumulative_increase_amount,

NVL(ls.cumulative_contingent_amount,0) cumulative_contingent_amount,

NVL(ls.increase_amount,0) + NVL(ls.cumulative_increase_amount,0) total_increase_amount,

NVL(ls.contingent_amount,0) + NVL(ls.cumulative_contingent_amount,0) total_contingent_amount,

ls.catchup_flag,

ls.payment_item_date,

ls.upfront_tax_amount,

100 stage_request_id,

opening_short_term_liab_amount,

reclass_short_term_liab_amount,

closing_short_term_liab_amount,

opening_long_term_liab_amount,

reclass_long_term_liab_amount,

closing_long_term_liab_amount

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 sh2.regime_code = sh1.regime_code

AND sh2.lease_id = sh1.lease_id

AND lst.stage_request_id = -99),ls.schedule_date+1)

) lss,

fla_lease_payment_headers ph,

fla_leases_all fl,

fla_system_options_all so,

fla_currencies_v fc

WHERE fl.lease_id = ph.lease_id

AND ph.lease_id = sh.lease_id

AND ph.lease_detail_id = sh.lease_detail_id

AND ph.payment_number = sh.payment_number

and sh.schedule_header_id = lss.schedule_header_id

AND fl.org_id = so.org_id

AND ph.currency_code = fc.currency_code

AND ph.liability_flag = 'Y'