FLA_INDEX_ASSESSMENTS

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID VERSION_TYPE_CODE VERSION_NUM COMMENCEMENT_DATE AMEND_FOR_INDEX_FLAG PROCESS_REQUEST_ID PROCESS_REQUEST_CODE AGREEMENT_NUMBER INCREMENT_INDEX_HEADER_ID INDEX_SHORT_NAME ASSESSMENT_DATE INCREASE_CALC_METHOD_CODE INDEX_TYPE_CODE FIXED_INCREASE_PERCENT MINIMUM_INCREASE_PERCENT MAXIMUM_INCREASE_PERCENT UNUSED_INCREASE_TREATMENT_CODE ACTIVATION_DERIVATION_CODE ASSESSMENT_DERIVATION_CODE FINAL_ASSESSED_FLAG SCHEDULES_IMPACT_FLAG ASSESSMENT_ACTIVATED_FLAG REQUIRED_INDEX_STATUS_CODE PREV_INDEX_BEGIN_DATE PREV_INDEX_END_DATE CURR_INDEX_BEGIN_DATE CURR_INDEX_END_DATE PREV_INDEX_DATE PREV_INDEX_VALUE CURR_INDEX_DATE CURR_INDEX_VALUE

Name

LEASE_ID

LEASE_DETAIL_ID

VERSION_TYPE_CODE

VERSION_NUM

COMMENCEMENT_DATE

AMEND_FOR_INDEX_FLAG

PROCESS_REQUEST_ID

PROCESS_REQUEST_CODE

AGREEMENT_NUMBER

INCREMENT_INDEX_HEADER_ID

INDEX_SHORT_NAME

ASSESSMENT_DATE

INCREASE_CALC_METHOD_CODE

INDEX_TYPE_CODE

FIXED_INCREASE_PERCENT

MINIMUM_INCREASE_PERCENT

MAXIMUM_INCREASE_PERCENT

UNUSED_INCREASE_TREATMENT_CODE

ACTIVATION_DERIVATION_CODE

ASSESSMENT_DERIVATION_CODE

FINAL_ASSESSED_FLAG

SCHEDULES_IMPACT_FLAG

ASSESSMENT_ACTIVATED_FLAG

REQUIRED_INDEX_STATUS_CODE

PREV_INDEX_BEGIN_DATE

PREV_INDEX_END_DATE

CURR_INDEX_BEGIN_DATE

CURR_INDEX_END_DATE

PREV_INDEX_DATE

PREV_INDEX_VALUE

CURR_INDEX_DATE

CURR_INDEX_VALUE

Query

SQL_Statement
SELECT ap.lease_id, ap.lease_detail_id, ap.version_type_code, ap.version_num, ap.commencement_date, ap.amend_for_index_flag, ap.process_request_id, ap.process_request_code, ap.agreement_number, ap.increment_index_header_id, ap.index_short_name, ap.assessment_date, ap.increase_calc_method_code, ap.index_type_code, ap.fixed_increase_percent, ap.minimum_increase_percent, ap.maximum_increase_percent, ap.unused_increase_treatment_code, ap.activation_derivation_code, ap.assessment_derivation_code, ap.final_assessed_flag, ap.schedules_impact_flag, ap.assessment_activated_flag, ap.required_index_status_code, ap.prev_index_begin_date, ap.prev_index_end_date, ap.curr_index_begin_date, ap.curr_index_end_date, MAX(ipl.index_date) prev_index_date, DECODE(ap.index_type_code, 'FIXED', ap.base_index_value, MAX(ipl.index_value+NVL(ipl.index_adjustment_factor,0)) KEEP(DENSE_RANK FIRST ORDER BY ipl.index_date DESC)) prev_index_value, MAX(icl.index_date) curr_index_date, MAX(icl.index_value+NVL(icl.index_adjustment_factor,0)) KEEP(DENSE_RANK FIRST ORDER BY icl.index_date DESC) curr_index_value FROM ( SELECT ld.lease_id, ld.lease_detail_id, ld.version_type_code, ld.version_num, ld.commencement_date, NVL(ld.amend_for_index_flag, 'N') amend_for_index_flag, ld.process_request_id, ld.process_request_code, ih.agreement_number, ih.increment_index_header_id, iih.index_short_name, ip.assessment_date, ih.increase_calc_method_code, iih.index_type_code, ip.derived_fixed_increase_percent fixed_increase_percent, ih.minimum_increase_percent, ih.maximum_increase_percent, ih.unused_increase_treatment_code, ih.activation_derivation_code, ip.assessment_derivation_code, ip.final_assessed_flag, ip.schedules_impact_flag, ih.base_index_value, DECODE(iih.index_type_code, 'FIXED', NULL, ADD_MONTHS(ip.assessment_date, (DECODE(ih.assessment_frequency_code, 'ANNUAL', 12, 6)-1) + nvl(ip.index_finder_months,0))) prev_index_begin_date, DECODE(iih.index_type_code, 'FIXED', NULL, ADD_MONTHS(ip.assessment_date, DECODE(ih.assessment_frequency_code, 'ANNUAL', 12, 6)-1)) prev_index_end_date, ADD_MONTHS(ip.assessment_date, nvl(ip.index_finder_months,0)) curr_index_begin_date, ip.assessment_date curr_index_end_date, DECODE(fip.assessment_date, NULL, 'N', 'Y') assessment_activated_flag, CASE WHEN ld.amend_for_index_flag = 'Y' AND ip.final_assessed_flag = 'I' THEN 'FINAL' WHEN ld.commencement_date > ip.assessment_date THEN 'FINAL' ELSE TO_CHAR(NULL) END required_index_status_code FROM fla_lease_details ld, fla_increment_headers ih, fla_increment_periods ip, fla_increment_index_hdrs_all iih, fla_increment_periods fip WHERE ld.lease_detail_id = ih.lease_detail_id AND ld.lease_id = ih.lease_id AND ih.lease_detail_id = ip.lease_detail_id AND ih.lease_id = ip.lease_id AND ih.agreement_number = ip.agreement_number AND ih.increment_index_header_id = iih.increment_index_header_id AND ip.increment_period_id = fip.parent_record_id(+) AND ip.lease_id = fip.lease_id(+) AND ip.agreement_number = fip.agreement_number(+) AND ip.assessment_date = fip.assessment_date(+) AND ld.version_type_code = 'D' AND ih.schedules_impact_flag = 'Y' AND ih.change_type_code <> 'DELETE' AND ip.change_type_code <> 'DELETE' ) ap, fla_increment_index_lines ipl, fla_increment_index_lines icl WHERE ap.increment_index_header_id = ipl.increment_index_header_id(+) AND ap.increment_index_header_id = icl.increment_index_header_id(+) AND ipl.index_date(+) BETWEEN ap.prev_index_begin_date AND ap.prev_index_end_date AND ipl.line_status_code(+) = NVL(ap.required_index_status_code, ipl.line_status_code(+)) AND icl.index_date(+) BETWEEN ap.curr_index_begin_date AND ap.curr_index_end_date AND icl.line_status_code(+) = NVL(ap.required_index_status_code, icl.line_status_code(+)) AND ap.final_assessed_flag <> 'Y' GROUP BY ap.lease_id, ap.lease_detail_id, ap.version_type_code, ap.version_num, ap.commencement_date, ap.amend_for_index_flag, ap.process_request_id, ap.process_request_code, ap.agreement_number, ap.increment_index_header_id, ap.index_short_name, ap.assessment_date, ap.increase_calc_method_code, ap.index_type_code, ap.fixed_increase_percent, ap.minimum_increase_percent, ap.maximum_increase_percent, ap.unused_increase_treatment_code, ap.activation_derivation_code, ap.assessment_derivation_code, ap.final_assessed_flag, ap.schedules_impact_flag, ap.assessment_activated_flag, ap.required_index_status_code, ap.base_index_value, ap.prev_index_begin_date, ap.prev_index_end_date, ap.curr_index_begin_date, ap.curr_index_end_date

SQL_Statement

SELECT ap.lease_id,

ap.lease_detail_id,

ap.version_type_code,

ap.version_num,

ap.commencement_date,

ap.amend_for_index_flag,

ap.process_request_id,

ap.process_request_code,

ap.agreement_number,

ap.increment_index_header_id,

ap.index_short_name,

ap.assessment_date,

ap.increase_calc_method_code,

ap.index_type_code,

ap.fixed_increase_percent,

ap.minimum_increase_percent,

ap.maximum_increase_percent,

ap.unused_increase_treatment_code,

ap.activation_derivation_code,

ap.assessment_derivation_code,

ap.final_assessed_flag,

ap.schedules_impact_flag,

ap.assessment_activated_flag,

ap.required_index_status_code,

ap.prev_index_begin_date,

ap.prev_index_end_date,

ap.curr_index_begin_date,

ap.curr_index_end_date,

MAX(ipl.index_date) prev_index_date,

DECODE(ap.index_type_code, 'FIXED', ap.base_index_value, MAX(ipl.index_value+NVL(ipl.index_adjustment_factor,0)) KEEP(DENSE_RANK FIRST ORDER BY ipl.index_date DESC)) prev_index_value,

MAX(icl.index_date) curr_index_date,

MAX(icl.index_value+NVL(icl.index_adjustment_factor,0)) KEEP(DENSE_RANK FIRST ORDER BY icl.index_date DESC) curr_index_value

FROM (

SELECT ld.lease_id,

ld.lease_detail_id,

ld.version_type_code,

ld.version_num,

ld.commencement_date,

NVL(ld.amend_for_index_flag, 'N') amend_for_index_flag,

ld.process_request_id,

ld.process_request_code,

ih.agreement_number,

ih.increment_index_header_id,

iih.index_short_name,

ip.assessment_date,

ih.increase_calc_method_code,

iih.index_type_code,

ip.derived_fixed_increase_percent fixed_increase_percent,

ih.minimum_increase_percent,

ih.maximum_increase_percent,

ih.unused_increase_treatment_code,

ih.activation_derivation_code,

ip.assessment_derivation_code,

ip.final_assessed_flag,

ip.schedules_impact_flag,

ih.base_index_value,

DECODE(iih.index_type_code, 'FIXED', NULL, ADD_MONTHS(ip.assessment_date, (DECODE(ih.assessment_frequency_code, 'ANNUAL', 12, 6)*-1) + nvl(ip.index_finder_months,0))) prev_index_begin_date,

DECODE(iih.index_type_code, 'FIXED', NULL, ADD_MONTHS(ip.assessment_date, DECODE(ih.assessment_frequency_code, 'ANNUAL', 12, 6)*-1)) prev_index_end_date,

ADD_MONTHS(ip.assessment_date, nvl(ip.index_finder_months,0)) curr_index_begin_date,

ip.assessment_date curr_index_end_date,

DECODE(fip.assessment_date, NULL, 'N', 'Y') assessment_activated_flag,

CASE WHEN ld.amend_for_index_flag = 'Y' AND ip.final_assessed_flag = 'I' THEN 'FINAL'

WHEN ld.commencement_date > ip.assessment_date THEN 'FINAL'

ELSE TO_CHAR(NULL)

END required_index_status_code

FROM fla_lease_details ld,

fla_increment_headers ih,

fla_increment_periods ip,

fla_increment_index_hdrs_all iih,

fla_increment_periods fip

WHERE ld.lease_detail_id = ih.lease_detail_id

AND ld.lease_id = ih.lease_id

AND ih.lease_detail_id = ip.lease_detail_id

AND ih.lease_id = ip.lease_id

AND ih.agreement_number = ip.agreement_number

AND ih.increment_index_header_id = iih.increment_index_header_id

AND ip.increment_period_id = fip.parent_record_id(+)

AND ip.lease_id = fip.lease_id(+)

AND ip.agreement_number = fip.agreement_number(+)

AND ip.assessment_date = fip.assessment_date(+)

AND ld.version_type_code = 'D'

AND ih.schedules_impact_flag = 'Y'

AND ih.change_type_code <> 'DELETE'

AND ip.change_type_code <> 'DELETE'

) ap,

fla_increment_index_lines ipl,

fla_increment_index_lines icl

WHERE ap.increment_index_header_id = ipl.increment_index_header_id(+)

AND ap.increment_index_header_id = icl.increment_index_header_id(+)

AND ipl.index_date(+) BETWEEN ap.prev_index_begin_date AND ap.prev_index_end_date

AND ipl.line_status_code(+) = NVL(ap.required_index_status_code, ipl.line_status_code(+))

AND icl.index_date(+) BETWEEN ap.curr_index_begin_date AND ap.curr_index_end_date

AND icl.line_status_code(+) = NVL(ap.required_index_status_code, icl.line_status_code(+))

AND ap.final_assessed_flag <> 'Y'

GROUP BY ap.lease_id,