1. Tables and Views for Financials
  2. FLA_PRESENT_VALUES_V

FLA_PRESENT_VALUES_V

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

TAX_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,

a.tax_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,

pv.tax_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,

sh.currency_code,

sh.due_date,

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

CASE

WHEN sh.amortization_proration_code = '999' THEN(sh.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(sh.due_date, 'MM') THEN

CASE

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

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

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

ELSE TO_NUMBER(TO_CHAR(sh.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(sh.due_date, 'MM') THEN

0

ELSE

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

END) +

(CASE

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

0

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

30

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

29

ELSE TO_NUMBER(TO_CHAR(sh.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(sh.due_date, 'DD')) < 31 THEN

1

ELSE 0

END)

ELSE

(CASE

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

DECODE(LAST_DAY(sh.due_date), sh.due_date, 30, TO_NUMBER(TO_CHAR(sh.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(sh.due_date, 'MM') THEN

0

ELSE

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

END) +

(CASE

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

0

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

30

ELSE

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

END) - 1

END no_of_days,

sh.amend_purpose_flag,

NVL(sh.increase_amount,0) increase_amount,

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

NVL(sh.cumulative_increase_amount,0) cumulative_increase_amount,

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

NVL(sh.total_increase_amount,0) total_increase_amount,

CASE WHEN sh.regime_code = 'ASC842' AND sh.lease_classification_code <> 'EXEMPT' THEN NVL(sh.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,

NVL(sh.tax_amount,0) tax_amount

FROM (

SELECT ash.lease_id,

ash.lease_detail_id,

ash.version_type_code,

ash.version_num,

ash.transaction_type_code,

ash.schedule_header_id,

ash.regime_code,

ash.payment_number,

ash.active_flag,

ash.lease_classification_code,

ash.amortization_proration_code,

ash.amortization_start_date,

ash.amortization_end_date,

ash.number_of_amortization_days,

ash.interest_rate,

ash.adder_rate,

ash.daily_compounding_rate,

ash.last_payment_date,

ash.end_of_day_payment_flag,

ash.interest_compounding_days,

ash.simple_interest_rate,

ash.interest_compounding_code,

ash.scheduling_version_num,

ash.amend_purpose_flag,

ash.forecasted_liability_amount,

ash.tot_forecast_liability_amount,

ash.last_accrued_date,

pi.currency_code,

pi.due_date,

SUM(NVL(pi.actual_amount,0)) actual_amount,

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

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

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

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

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

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

SUM(CASE WHEN pi.tax_transaction_id IS NOT NULL THEN NVL(pi.actual_amount,0) ELSE 0 END) tax_amount

FROM fla_active_schedule_headers_v ash,

fla_payment_items pi

WHERE ash.payment_lease_detail_id = pi.lease_detail_id

AND ash.lease_id = pi.lease_id

AND ash.payment_number = pi.payment_number

AND ash.amortization_start_date <= pi.due_date

AND ash.amortization_end_date >= pi.due_date

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

AND pi.parent_payment_item_id IS NULL

AND NVL(pi.payment_item_type_code,'~') <> 'RECOVERABLE'

GROUP BY ash.lease_id,

ash.lease_detail_id,

ash.version_type_code,

ash.version_num,

ash.transaction_type_code,

ash.schedule_header_id,

ash.regime_code,

ash.payment_number,

ash.active_flag,

ash.lease_classification_code,

ash.amortization_proration_code,

ash.amortization_start_date,

ash.amortization_end_date,

ash.number_of_amortization_days,

ash.interest_rate,

ash.adder_rate,

ash.daily_compounding_rate,

ash.last_payment_date,

ash.end_of_day_payment_flag,

ash.interest_compounding_days,

ash.simple_interest_rate,

ash.interest_compounding_code,

ash.scheduling_version_num,

ash.amend_purpose_flag,

ash.forecasted_liability_amount,

ash.tot_forecast_liability_amount,

ash.last_accrued_date,

pi.currency_code,

pi.due_date

) sh

) pv

)a