FLA_INCREMENT_LINE_IMPACTS

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID VERSION_TYPE_CODE VERSION_NUM AGREEMENT_NUMBER AGREEMENT_NAME AGREEMENT_START_DATE AGREEMENT_END_DATE AGR_FIRST_ASSESSMENT_DATE AGREEMENT_TERMINATION_DATE VENDOR_ID PAYMENT_CURRENCY_CODE ASSESSMENT_FREQUENCY_CODE ASSESS_IN_YEARS_NUM ASSESSMENT_MONTHS BASIS_TYPE_CODE BASIS_COMPOUNDING_CODE SCHEDULES_IMPACT_FLAG ASSESSMENT_DATE FINAL_ASSESSED_FLAG INCREASE_CALC_METHOD_CODE INCREASE_PERCENT FIXED_INCREASE_AMOUNT BASIS_FACTOR_PERCENT INCREASE_APPLICABLE_FLAG PAYMENT_NUMBER PAYMENT_START_DATE PAYMENT_END_DATE FIRST_ASSESSMENT_DATE LAST_ASSESSMENT_DATE PAYMENT_BASIS_START_DATE PAYMENT_BASIS_END_DATE CLOSED_PAYMENT_FLAG

Name

LEASE_ID

LEASE_DETAIL_ID

VERSION_TYPE_CODE

VERSION_NUM

AGREEMENT_NUMBER

AGREEMENT_NAME

AGREEMENT_START_DATE

AGREEMENT_END_DATE

AGR_FIRST_ASSESSMENT_DATE

AGREEMENT_TERMINATION_DATE

VENDOR_ID

PAYMENT_CURRENCY_CODE

ASSESSMENT_FREQUENCY_CODE

ASSESS_IN_YEARS_NUM

ASSESSMENT_MONTHS

BASIS_TYPE_CODE

BASIS_COMPOUNDING_CODE

SCHEDULES_IMPACT_FLAG

ASSESSMENT_DATE

FINAL_ASSESSED_FLAG

INCREASE_CALC_METHOD_CODE

INCREASE_PERCENT

FIXED_INCREASE_AMOUNT

BASIS_FACTOR_PERCENT

INCREASE_APPLICABLE_FLAG

PAYMENT_NUMBER

PAYMENT_START_DATE

PAYMENT_END_DATE

FIRST_ASSESSMENT_DATE

LAST_ASSESSMENT_DATE

PAYMENT_BASIS_START_DATE

PAYMENT_BASIS_END_DATE

CLOSED_PAYMENT_FLAG

Query

SQL_Statement
SELECT ld.lease_id, ld.lease_detail_id, ld.version_type_code, ld.version_num, ih.agreement_number, ih.agreement_name, ih.agreement_start_date, ih.agreement_end_date, ih.first_assessment_date agr_first_assessment_date, ih.agreement_termination_date, ih.vendor_id, ih.payment_currency_code, ih.assessment_frequency_code, ih.assess_in_years_num, DECODE(ih.assessment_frequency_code, 'ANNUAL', 12ih.assess_in_years_num, 6) assessment_months, ih.basis_type_code, ih.basis_compounding_code, ih.schedules_impact_flag, ap.assessment_date, ap.final_assessed_flag, ih.increase_calc_method_code, ap.increase_percent, ap.fixed_increase_amount, ap.basis_factor_percent, ap.increase_applicable_flag, ip.payment_number, ph.payment_start_date, ph.payment_end_date, ip.first_assessment_date, ip.last_assessment_date, DECODE(ih.basis_type_code, 'PREVIOUS', ADD_MONTHS(ap.assessment_date,-1 DECODE(ih.assessment_frequency_code,'ANNUAL',12,6)), ap.assessment_date) payment_basis_start_date, DECODE(ih.basis_type_code, 'PREVIOUS', ap.assessment_date - 1, ADD_MONTHS(ap.assessment_date, DECODE(ih.assessment_frequency_code,'ANNUAL',12,6))) payment_basis_end_date, ph.closed_payment_flag FROM fla_lease_details ld, fla_increment_headers ih, fla_increment_periods ap, fla_increment_payments ip, ( SELECT lph.lease_id, lph.lease_detail_id, lph.payment_number, lph.payment_start_date, NVL(lph.termination_date,MAX(lpl.payment_line_end_date)) payment_end_date, NVL(lph.closed_flag, 'N') closed_payment_flag FROM fla_lease_payment_headers lph, fla_lease_payment_lines lpl WHERE lph.lease_detail_id = lpl.lease_detail_id AND lph.payment_number = lpl.payment_number AND lph.lease_id = lpl.lease_id AND lph.change_type_code <> 'DELETE' AND lpl.change_type_code <> 'DELETE' GROUP BY lph.lease_id, lph.lease_detail_id, lph.payment_number, lph.payment_start_date, lph.termination_date, NVL(lph.closed_flag, 'N') ) ph WHERE ld.lease_detail_id = ih.lease_detail_id AND ld.lease_id = ih.lease_id AND ld.version_type_code = 'D' AND ih.lease_detail_id = ap.lease_detail_id AND ih.agreement_number = ap.agreement_number AND ih.lease_id = ap.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 ip.payment_number = ph.payment_number AND ip.lease_detail_id = ph.lease_detail_id AND ip.lease_id = ph.lease_id AND ap.assessment_date BETWEEN ph.payment_start_date AND ph.payment_end_date AND ap.change_type_code <> 'DELETE' AND ip.change_type_code <> 'DELETE' AND ih.change_type_code <> 'DELETE' AND ih.schedules_impact_flag = 'Y'

SQL_Statement

SELECT ld.lease_id,

ld.lease_detail_id,

ld.version_type_code,

ld.version_num,

ih.agreement_number,

ih.agreement_name,

ih.agreement_start_date,

ih.agreement_end_date,

ih.first_assessment_date agr_first_assessment_date,

ih.agreement_termination_date,

ih.vendor_id,

ih.payment_currency_code,

ih.assessment_frequency_code,

ih.assess_in_years_num,

DECODE(ih.assessment_frequency_code, 'ANNUAL', 12*ih.assess_in_years_num, 6) assessment_months,

ih.basis_type_code,

ih.basis_compounding_code,

ih.schedules_impact_flag,

ap.assessment_date,

ap.final_assessed_flag,

ih.increase_calc_method_code,

ap.increase_percent,

ap.fixed_increase_amount,

ap.basis_factor_percent,

ap.increase_applicable_flag,

ip.payment_number,

ph.payment_start_date,

ph.payment_end_date,

ip.first_assessment_date,

ip.last_assessment_date,

DECODE(ih.basis_type_code, 'PREVIOUS', ADD_MONTHS(ap.assessment_date,-1 * DECODE(ih.assessment_frequency_code,'ANNUAL',12,6)), ap.assessment_date) payment_basis_start_date,

DECODE(ih.basis_type_code, 'PREVIOUS', ap.assessment_date - 1, ADD_MONTHS(ap.assessment_date, DECODE(ih.assessment_frequency_code,'ANNUAL',12,6))) payment_basis_end_date,

ph.closed_payment_flag

FROM fla_lease_details ld,

fla_increment_headers ih,

fla_increment_periods ap,

fla_increment_payments ip,

(

SELECT lph.lease_id,

lph.lease_detail_id,

lph.payment_number,

lph.payment_start_date,

NVL(lph.termination_date,MAX(lpl.payment_line_end_date)) payment_end_date,

NVL(lph.closed_flag, 'N') closed_payment_flag

FROM fla_lease_payment_headers lph,

fla_lease_payment_lines lpl

WHERE lph.lease_detail_id = lpl.lease_detail_id

AND lph.payment_number = lpl.payment_number

AND lph.lease_id = lpl.lease_id

AND lph.change_type_code <> 'DELETE'

AND lpl.change_type_code <> 'DELETE'

GROUP BY lph.lease_id, lph.lease_detail_id, lph.payment_number, lph.payment_start_date, lph.termination_date, NVL(lph.closed_flag, 'N')

) ph

WHERE ld.lease_detail_id = ih.lease_detail_id

AND ld.lease_id = ih.lease_id

AND ld.version_type_code = 'D'

AND ih.lease_detail_id = ap.lease_detail_id

AND ih.agreement_number = ap.agreement_number

AND ih.lease_id = ap.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 ip.payment_number = ph.payment_number

AND ip.lease_detail_id = ph.lease_detail_id

AND ip.lease_id = ph.lease_id

AND ap.assessment_date BETWEEN ph.payment_start_date AND ph.payment_end_date

AND ap.change_type_code <> 'DELETE'

AND ip.change_type_code <> 'DELETE'

AND ih.change_type_code <> 'DELETE'

AND ih.schedules_impact_flag = 'Y'