<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm
    $Date: 2013-12-28 下午1:31:12
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1"
          baseTable="CON_UNEARNED_FINANCE_INCOME" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[

            SELECT tt.*,
               (tt.change_money - tt.unearned_finance_income_bl) AS unearned_finance_income_al
          FROM (SELECT t.withdrawal_number,
                       (SELECT crp.times
                          FROM tre_loan_con_repayment_plan crp
                         WHERE to_char(crp.maturity_date, 'yyyymm') = ${@internal_period_num}
                           AND crp.withdrawal_id = t.widthdrawal_plan_id
                           and rownum=1) times,
                       t.widthdrawal_date,
                       to_char(t.widthdrawal_date,'yyyy-mm-dd')widthdrawal_date_n,
                       (SELECT crp.maturity_date
                          FROM tre_loan_con_repayment_plan crp
                         WHERE to_char(crp.maturity_date, 'yyyymm') = ${@internal_period_num}
                           AND crp.withdrawal_id = t.widthdrawal_plan_id
                           and rownum=1) maturity_date,
                       to_char((SELECT crp.maturity_date
                          FROM tre_loan_con_repayment_plan crp
                         WHERE to_char(crp.maturity_date, 'yyyymm') = ${@internal_period_num}
                           AND crp.withdrawal_id = t.widthdrawal_plan_id
                           and rownum=1),'yyyy-mm-dd') maturity_date_n,

                       t.amount,
                       lc.change_money,
                       (SELECT SUM(i.unearned_finance_income)
                          FROM con_unearned_finance_income i,
                               tre_loan_con_repayment_plan rp
                         WHERE i.cashflow_id = rp.repayment_plan_id
                           AND i.contract_id = rp.withdrawal_id
                           AND rp.withdrawal_id = t.widthdrawal_plan_id
                           AND i.cf_item = 901
                           AND i.internal_period_num = ${@internal_period_num}) AS unearned_finance_income,

                       (SELECT SUM(i.unearned_finance_income)
                          FROM con_unearned_finance_income i,
                               tre_loan_con_repayment_plan rp
                         WHERE i.cashflow_id = rp.repayment_plan_id
                           AND i.contract_id = rp.withdrawal_id
                           AND rp.withdrawal_id = t.widthdrawal_plan_id
                           AND i.cf_item = 901
                           AND i.internal_period_num <= ${@internal_period_num}) AS unearned_finance_income_bl,
                      lc.loan_contract_id

                  FROM tre_loan_con_withdrawal_plan t, tre_loan_contract lc,
                           tre_loan_con_repayment_plan  crp
                 WHERE t.widthdrawal_wfl_status = 'CONFIRM'
                   AND t.loan_contract_id = lc.loan_contract_id
                   AND lc.change_money > 0
                   AND (EXISTS
                            (SELECT 1
                               FROM tre_loan_con_repayment_plan  rp,
                                    tre_loan_con_repayment_dtl   rd,
                                    tre_loan_con_withdrawal_plan wp
                              WHERE rp.repayment_plan_id = rd.repayment_plan_id
                                AND rp.rental - nvl(rd.repayment_amount, 0) > 0
                                AND wp.widthdrawal_plan_id = rp.withdrawal_id
                                AND to_char(wp.widthdrawal_date, 'yyyymm') <= ${@internal_period_num}
                                AND rp.withdrawal_id = t.widthdrawal_plan_id) OR EXISTS
                            (SELECT 1
                               FROM tre_loan_con_repayment_plan  rp,
                                    tre_loan_con_repayment_dtl   rd,
                                    tre_loan_con_withdrawal_plan wp
                              WHERE rp.repayment_plan_id = rd.repayment_plan_id
                                AND rp.rental - nvl(rd.repayment_amount, 0) = 0
                                AND wp.widthdrawal_plan_id = rp.withdrawal_id
                                AND to_char(wp.widthdrawal_date, 'yyyymm') = ${@internal_period_num}
                                AND rp.withdrawal_id = t.widthdrawal_plan_id) OR EXISTS
                            (SELECT 1
                               FROM tre_loan_con_repayment_plan  rp,
                                    tre_loan_con_withdrawal_plan wp
                              WHERE wp.widthdrawal_plan_id = rp.withdrawal_id
                                AND to_char(wp.widthdrawal_date, 'yyyymm') <= ${@internal_period_num}
                                AND rp.withdrawal_id = t.widthdrawal_plan_id))
                         AND crp.withdrawal_id = t.widthdrawal_plan_id
                         AND crp.repayment_plan_id =
                             hls_monthend_pkg.get_first_overdue_id(p_internal_period_num => ${@internal_period_num},
                                                                  p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                                  p_cf_item             => 901)

                            ) tt

                       #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="company_id" queryExpression="tt.company_id=${@company_id}"/>
        <bm:query-field name="withdrawal_number" queryExpression="tt.withdrawal_number=${@withdrawal_number}"/>
    </bm:query-fields>
</bm:model>
