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

Query

SQL_Statement

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, pv.actual_amount*power((1+pv.daily_compounding_rate),(-1*pv.no_of_days)) 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 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, 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) 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) 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 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 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