FLA_PRESENT_VALUES

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID VERSION_TYPE_CODE VERSION_NUM TRANSACTION_TYPE_CODE SCHEDULE_HEADER_ID REGIME_CODE PAYMENT_NUMBER ACTIVE_FLAG LEASE_CLASSIFICATION_CODE AMORTIZATION_PRORATION_CODE AMORTIZATION_START_DATE AMORTIZATION_END_DATE NUMBER_OF_AMORTIZATION_DAYS INTEREST_RATE ADDER_RATE MONTHLY_COMPOUNDING_RATE DAILY_COMPOUNDING_RATE CURRENCY_CODE DUE_DATE ACTUAL_AMOUNT NO_OF_DAYS PRESENT_VALUE AMEND_PURPOSE_FLAG INCREASE_AMOUNT CONTINGENT_AMOUNT CUMULATIVE_INCREASE_AMOUNT CUMULATIVE_CONTINGENT_AMOUNT TOTAL_INCREASE_AMOUNT TOTAL_CONTINGENT_AMOUNT NEXT_ACCRUAL_START_DATE LAST_PAYMENT_DATE END_OF_DAY_PAYMENT_FLAG INTEREST_COMPOUNDING_DAYS SIMPLE_INTEREST_RATE INTEREST_COMPOUNDING_CODE FORECASTED_LIABILITY_AMOUNT TOT_FORECAST_LIABILITY_AMOUNT

Name

LEASE_ID

LEASE_DETAIL_ID

VERSION_TYPE_CODE

VERSION_NUM

TRANSACTION_TYPE_CODE

SCHEDULE_HEADER_ID

REGIME_CODE

PAYMENT_NUMBER

ACTIVE_FLAG

LEASE_CLASSIFICATION_CODE

AMORTIZATION_PRORATION_CODE

AMORTIZATION_START_DATE

AMORTIZATION_END_DATE

NUMBER_OF_AMORTIZATION_DAYS

INTEREST_RATE

ADDER_RATE

MONTHLY_COMPOUNDING_RATE

DAILY_COMPOUNDING_RATE

CURRENCY_CODE

DUE_DATE

ACTUAL_AMOUNT

NO_OF_DAYS

PRESENT_VALUE

AMEND_PURPOSE_FLAG

INCREASE_AMOUNT

CONTINGENT_AMOUNT

CUMULATIVE_INCREASE_AMOUNT

CUMULATIVE_CONTINGENT_AMOUNT

TOTAL_INCREASE_AMOUNT

TOTAL_CONTINGENT_AMOUNT

NEXT_ACCRUAL_START_DATE

LAST_PAYMENT_DATE

END_OF_DAY_PAYMENT_FLAG

INTEREST_COMPOUNDING_DAYS

SIMPLE_INTEREST_RATE

INTEREST_COMPOUNDING_CODE

FORECASTED_LIABILITY_AMOUNT

TOT_FORECAST_LIABILITY_AMOUNT

Query

SQL_Statement
SELECT a.lease_id, a.lease_detail_id, a.version_type_code, a.version_num, a.transaction_type_code, a.schedule_header_id, a.regime_code, a.payment_number, a.active_flag, a.lease_classification_code, a.amortization_proration_code, a.amortization_start_date, a.amortization_end_date, a.number_of_amortization_days, a.interest_rate, a.adder_rate, CASE WHEN a.amortization_start_date = a.due_date THEN a.interest_rate + a.adder_rate ELSE ROUND(12 * (POWER(a.actual_amount/NULLIF(a.present_value, 0), 30/NULLIF(a.no_of_days, 0)) - 1)100, 2) END monthly_compounding_rate, a.daily_compounding_rate, a.currency_code, a.due_date, a.actual_amount, a.no_of_days, a.present_value, a.amend_purpose_flag, a.increase_amount, a.contingent_amount, a.cumulative_increase_amount, a.cumulative_contingent_amount, a.total_increase_amount, a.total_contingent_amount, a.next_accrual_start_date, a.last_payment_date, a.end_of_day_payment_flag, a.interest_compounding_days, a.simple_interest_rate, a.interest_compounding_code, a.forecasted_liability_amount, a.tot_forecast_liability_amount FROM ( SELECT pv.lease_id, pv.lease_detail_id, pv.version_type_code, pv.version_num, pv.transaction_type_code, pv.schedule_header_id, pv.regime_code, pv.payment_number, pv.active_flag, pv.lease_classification_code, pv.amortization_proration_code, pv.amortization_start_date, pv.amortization_end_date, pv.number_of_amortization_days, pv.interest_rate, pv.adder_rate, pv.daily_compounding_rate, pv.currency_code, pv.due_date, pv.actual_amount, pv.no_of_days, CASE WHEN pv.interest_compounding_code = 'DAILY' THEN pv.actual_amountpower((1+pv.daily_compounding_rate),(-1pv.no_of_days)) ELSE pv.actual_amount/power((1+pv.daily_compounding_rate), (pv.no_of_days/pv.interest_compounding_days)) END present_value, amend_purpose_flag, pv.increase_amount, pv.contingent_amount, pv.cumulative_increase_amount, pv.cumulative_contingent_amount, pv.total_increase_amount, pv.total_contingent_amount, pv.next_accrual_start_date, pv.last_payment_date, pv.end_of_day_payment_flag, pv.interest_compounding_days, pv.simple_interest_rate, pv.interest_compounding_code, pv.forecasted_liability_amount, pv.tot_forecast_liability_amount FROM (SELECT sh.lease_id, sh.lease_detail_id, sh.version_type_code, sh.version_num, sh.transaction_type_code, sh.schedule_header_id, sh.regime_code, sh.payment_number, sh.active_flag, sh.lease_classification_code, sh.amortization_proration_code, sh.amortization_start_date, sh.amortization_end_date, sh.number_of_amortization_days, sh.interest_rate, sh.adder_rate, sh.daily_compounding_rate, sh.last_payment_date, sh.end_of_day_payment_flag, sh.interest_compounding_days, sh.simple_interest_rate, sh.interest_compounding_code, pi.currency_code, pi.due_date, CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN (NVL(pi.actual_amount,0) - NVL(pi.total_contingent_amount,0)) ELSE NVL(pi.actual_amount,0) END actual_amount, CASE WHEN sh.amortization_proration_code = '999' THEN(pi.due_date - sh.amortization_start_date) + DECODE(sh.end_of_day_payment_flag, 'Y', 1, 0) WHEN sh.scheduling_version_num >= 3 THEN (CASE WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN CASE WHEN TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) = 31 THEN 0 WHEN TO_NUMBER(TO_CHAR(pi.due_date, 'DD')) = 31 THEN 31 - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) WHEN LAST_DAY(pi.due_date) = pi.due_date THEN 30 - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) ELSE TO_NUMBER(TO_CHAR(pi.due_date, 'DD')) - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) END WHEN TRUNC(sh.amortization_start_date, 'MM') = sh.amortization_start_date THEN 30 ELSE 31 - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) END) + (CASE WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN 0 ELSE MONTHS_BETWEEN(TRUNC(pi.due_date, 'MM'), LAST_DAY(sh.amortization_start_date) + 1) 30 END) + (CASE WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN 0 WHEN TO_NUMBER(TO_CHAR(pi.due_date, 'DD')) = 31 THEN 30 WHEN LAST_DAY(pi.due_date) = pi.due_date THEN 29 ELSE TO_NUMBER(TO_CHAR(pi.due_date, 'DD')-1) END) + /Below case ment for end of day pv enhancement/ (CASE WHEN sh.end_of_day_payment_flag = 'Y' AND TO_NUMBER(TO_CHAR(pi.due_date, 'DD')) < 31 THEN 1 ELSE 0 END) ELSE (CASE WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN DECODE(LAST_DAY(pi.due_date), pi.due_date, 30, TO_NUMBER(TO_CHAR(pi.due_date, 'DD'))) - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) + 1 WHEN TRUNC(sh.amortization_start_date, 'MM') = sh.amortization_start_date THEN 30 ELSE 31 - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) END) + (CASE WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN 0 ELSE MONTHS_BETWEEN(TRUNC(pi.due_date, 'MM'), LAST_DAY(sh.amortization_start_date) + 1) * 30 END) + (CASE WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN 0 WHEN LAST_DAY(pi.due_date) = pi.due_date THEN 30 ELSE DECODE(LAST_DAY(pi.due_date), pi.due_date, 30, TO_NUMBER(TO_CHAR(pi.due_date, 'DD'))) END) - 1 END no_of_days, sh.amend_purpose_flag, NVL(pi.increase_amount,0) increase_amount, CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN NVL(pi.contingent_amount,0) ELSE 0 END contingent_amount, NVL(pi.cumulative_increase_amount,0) cumulative_increase_amount, CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN NVL(pi.cumulative_contingent_amount,0) ELSE 0 END cumulative_contingent_amount, NVL(pi.total_increase_amount,0) total_increase_amount, CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN NVL(pi.total_contingent_amount,0) ELSE 0 END total_contingent_amount, NVL(sh.last_accrued_date+1,sh.amortization_start_date) next_accrual_start_date, sh.forecasted_liability_amount, sh.tot_forecast_liability_amount FROM fla_active_schedule_headers_v sh, (SELECT lease_detail_id, lease_id, payment_number, currency_code, due_date, SUM(NVL(actual_amount,0)) actual_amount, SUM(CASE WHEN payment_increase_flag = 'Y' AND cumulative_increase_flag = 'N' THEN NVL(actual_amount,0) ELSE 0 END) increase_amount, SUM(CASE WHEN payment_increase_flag = 'Y' AND cumulative_increase_flag = 'N' THEN NVL(contingent_amount,0) ELSE 0 END) contingent_amount, SUM(CASE WHEN payment_increase_flag = 'Y' AND cumulative_increase_flag = 'Y' THEN NVL(actual_amount,0) ELSE 0 END) cumulative_increase_amount, SUM(CASE WHEN payment_increase_flag = 'Y' AND cumulative_increase_flag = 'Y' THEN NVL(contingent_amount,0) ELSE 0 END) cumulative_contingent_amount, SUM(CASE WHEN payment_increase_flag = 'Y' THEN NVL(actual_amount,0) ELSE 0 END) total_increase_amount, SUM(CASE WHEN payment_increase_flag = 'Y' THEN NVL(contingent_amount,0) ELSE 0 END) total_contingent_amount FROM fla_lease_payment_items_v payitm WHERE NVL(reversal_flag,'N')= 'N' AND parent_payment_item_id IS NULL AND include_in_rou_flag = 'Y' GROUP BY lease_detail_id, lease_id, payment_number, currency_code, due_date )pi WHERE sh.act_lease_detail_id = pi.lease_detail_id AND sh.lease_id = pi.lease_id AND sh.payment_number = pi.payment_number AND sh.amortization_start_date <= pi.due_date AND sh.amortization_end_date >= pi.due_date ) pv )a

SQL_Statement

SELECT a.lease_id,

a.lease_detail_id,

a.version_type_code,

a.version_num,

a.transaction_type_code,

a.schedule_header_id,

a.regime_code,

a.payment_number,

a.active_flag,

a.lease_classification_code,

a.amortization_proration_code,

a.amortization_start_date,

a.amortization_end_date,

a.number_of_amortization_days,

a.interest_rate,

a.adder_rate,

CASE

WHEN a.amortization_start_date = a.due_date THEN a.interest_rate + a.adder_rate

ELSE ROUND(12 * (POWER(a.actual_amount/NULLIF(a.present_value, 0), 30/NULLIF(a.no_of_days, 0)) - 1)*100, 2)

END monthly_compounding_rate,

a.daily_compounding_rate,

a.currency_code,

a.due_date,

a.actual_amount,

a.no_of_days,

a.present_value,

a.amend_purpose_flag,

a.increase_amount,

a.contingent_amount,

a.cumulative_increase_amount,

a.cumulative_contingent_amount,

a.total_increase_amount,

a.total_contingent_amount,

a.next_accrual_start_date,

a.last_payment_date,

a.end_of_day_payment_flag,

a.interest_compounding_days,

a.simple_interest_rate,

a.interest_compounding_code,

a.forecasted_liability_amount,

a.tot_forecast_liability_amount

FROM (

SELECT pv.lease_id,

pv.lease_detail_id,

pv.version_type_code,

pv.version_num,

pv.transaction_type_code,

pv.schedule_header_id,

pv.regime_code,

pv.payment_number,

pv.active_flag,

pv.lease_classification_code,

pv.amortization_proration_code,

pv.amortization_start_date,

pv.amortization_end_date,

pv.number_of_amortization_days,

pv.interest_rate,

pv.adder_rate,

pv.daily_compounding_rate,

pv.currency_code,

pv.due_date,

pv.actual_amount,

pv.no_of_days,

CASE

WHEN pv.interest_compounding_code = 'DAILY' THEN

pv.actual_amount*power((1+pv.daily_compounding_rate),(-1*pv.no_of_days))

ELSE

pv.actual_amount/power((1+pv.daily_compounding_rate), (pv.no_of_days/pv.interest_compounding_days))

END present_value,

amend_purpose_flag,

pv.increase_amount,

pv.contingent_amount,

pv.cumulative_increase_amount,

pv.cumulative_contingent_amount,

pv.total_increase_amount,

pv.total_contingent_amount,

pv.next_accrual_start_date,

pv.last_payment_date,

pv.end_of_day_payment_flag,

pv.interest_compounding_days,

pv.simple_interest_rate,

pv.interest_compounding_code,

pv.forecasted_liability_amount,

pv.tot_forecast_liability_amount

FROM

(SELECT sh.lease_id,

sh.lease_detail_id,

sh.version_type_code,

sh.version_num,

sh.transaction_type_code,

sh.schedule_header_id,

sh.regime_code,

sh.payment_number,

sh.active_flag,

sh.lease_classification_code,

sh.amortization_proration_code,

sh.amortization_start_date,

sh.amortization_end_date,

sh.number_of_amortization_days,

sh.interest_rate,

sh.adder_rate,

sh.daily_compounding_rate,

sh.last_payment_date,

sh.end_of_day_payment_flag,

sh.interest_compounding_days,

sh.simple_interest_rate,

sh.interest_compounding_code,

pi.currency_code,

pi.due_date,

CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN (NVL(pi.actual_amount,0) - NVL(pi.total_contingent_amount,0)) ELSE NVL(pi.actual_amount,0) END actual_amount,

CASE

WHEN sh.amortization_proration_code = '999' THEN(pi.due_date - sh.amortization_start_date) + DECODE(sh.end_of_day_payment_flag, 'Y', 1, 0)

WHEN sh.scheduling_version_num >= 3 THEN

(CASE

WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN

CASE

WHEN TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) = 31 THEN 0

WHEN TO_NUMBER(TO_CHAR(pi.due_date, 'DD')) = 31 THEN 31 - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD'))

WHEN LAST_DAY(pi.due_date) = pi.due_date THEN 30 - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD'))

ELSE TO_NUMBER(TO_CHAR(pi.due_date, 'DD')) - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD'))

END

WHEN TRUNC(sh.amortization_start_date, 'MM') = sh.amortization_start_date THEN

30

ELSE

31 - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD'))

END) +

(CASE

WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN

0

ELSE

MONTHS_BETWEEN(TRUNC(pi.due_date, 'MM'), LAST_DAY(sh.amortization_start_date) + 1) * 30

END) +

(CASE

WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN

0

WHEN TO_NUMBER(TO_CHAR(pi.due_date, 'DD')) = 31 THEN

30

WHEN LAST_DAY(pi.due_date) = pi.due_date THEN

29

ELSE TO_NUMBER(TO_CHAR(pi.due_date, 'DD')-1)

END) + /*Below case ment for end of day pv enhancement*/

(CASE

WHEN sh.end_of_day_payment_flag = 'Y' AND TO_NUMBER(TO_CHAR(pi.due_date, 'DD')) < 31 THEN

1

ELSE 0

END)

ELSE

(CASE

WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN

DECODE(LAST_DAY(pi.due_date), pi.due_date, 30, TO_NUMBER(TO_CHAR(pi.due_date, 'DD'))) - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD')) + 1

WHEN TRUNC(sh.amortization_start_date, 'MM') = sh.amortization_start_date THEN

30

ELSE

31 - TO_NUMBER(TO_CHAR(sh.amortization_start_date, 'DD'))

END) +

(CASE

WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN

0

ELSE

MONTHS_BETWEEN(TRUNC(pi.due_date, 'MM'), LAST_DAY(sh.amortization_start_date) + 1) * 30

END) +

(CASE

WHEN TRUNC(sh.amortization_start_date, 'MM') = TRUNC(pi.due_date, 'MM') THEN

0

WHEN LAST_DAY(pi.due_date) = pi.due_date THEN

30

ELSE

DECODE(LAST_DAY(pi.due_date), pi.due_date, 30, TO_NUMBER(TO_CHAR(pi.due_date, 'DD')))

END) - 1

END no_of_days,

sh.amend_purpose_flag,

NVL(pi.increase_amount,0) increase_amount,

CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN NVL(pi.contingent_amount,0) ELSE 0 END contingent_amount,

NVL(pi.cumulative_increase_amount,0) cumulative_increase_amount,

CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN NVL(pi.cumulative_contingent_amount,0) ELSE 0 END cumulative_contingent_amount,

NVL(pi.total_increase_amount,0) total_increase_amount,

CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN NVL(pi.total_contingent_amount,0) ELSE 0 END total_contingent_amount,

NVL(sh.last_accrued_date+1,sh.amortization_start_date) next_accrual_start_date,

sh.forecasted_liability_amount,

sh.tot_forecast_liability_amount

FROM fla_active_schedule_headers_v sh,

(SELECT lease_detail_id,

lease_id,

payment_number,

currency_code,

due_date,

SUM(NVL(actual_amount,0)) actual_amount,

SUM(CASE WHEN payment_increase_flag = 'Y' AND cumulative_increase_flag = 'N' THEN NVL(actual_amount,0) ELSE 0 END) increase_amount,

SUM(CASE WHEN payment_increase_flag = 'Y' AND cumulative_increase_flag = 'N' THEN NVL(contingent_amount,0) ELSE 0 END) contingent_amount,

SUM(CASE WHEN payment_increase_flag = 'Y' AND cumulative_increase_flag = 'Y' THEN NVL(actual_amount,0) ELSE 0 END) cumulative_increase_amount,

SUM(CASE WHEN payment_increase_flag = 'Y' AND cumulative_increase_flag = 'Y' THEN NVL(contingent_amount,0) ELSE 0 END) cumulative_contingent_amount,

SUM(CASE WHEN payment_increase_flag = 'Y' THEN NVL(actual_amount,0) ELSE 0 END) total_increase_amount,

SUM(CASE WHEN payment_increase_flag = 'Y' THEN NVL(contingent_amount,0) ELSE 0 END) total_contingent_amount

FROM fla_lease_payment_items_v payitm

WHERE NVL(reversal_flag,'N')= 'N'

AND parent_payment_item_id IS NULL

AND include_in_rou_flag = 'Y'

GROUP BY lease_detail_id,

lease_id,

payment_number,

currency_code,

due_date

)pi

WHERE sh.act_lease_detail_id = pi.lease_detail_id

AND sh.lease_id = pi.lease_id

AND sh.payment_number = pi.payment_number

AND sh.amortization_start_date <= pi.due_date

AND sh.amortization_end_date >= pi.due_date

) pv

)a