<?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.next_maturity_date - last_maturity_date) total_days,
               round(interest * guarantee_days /
                     (tt.next_maturity_date - last_maturity_date),
                     2) unearned_finance_income,
               round(interest * guarantee_days /
                     (tt.next_maturity_date - last_maturity_date) / 1.13,
                     2) net_unearned_finance_income,
               round(interest * guarantee_days /
                     (tt.next_maturity_date - last_maturity_date),
                     2) - round(interest * guarantee_days /
                                (tt.next_maturity_date - last_maturity_date) / 1.13,
                                2) vat_unearned_finance_income
          FROM (SELECT (SELECT m.bp_name
                          FROM hls_bp_master m
                         WHERE m.bp_id = lc.loan_con_bp) loan_con_bp_name,
                       t.withdrawal_number,
                       crp.rate,
                       t.widthdrawal_date widthdrawal_date,
                       
                       to_char(t.widthdrawal_date, 'yyyy-mm-dd') widthdrawal_date_n,
                       
                       (SELECT MAX(crp.maturity_date)
                          FROM tre_loan_con_repayment_plan crp
                         WHERE crp.withdrawal_id = t.widthdrawal_plan_id) last_date,
                       
                       to_char((SELECT MAX(crp.maturity_date)
                                 FROM tre_loan_con_repayment_plan crp
                                WHERE crp.withdrawal_id = t.widthdrawal_plan_id),
                               'yyyy-mm-dd') last_date_n,
                       
                       NVL(
                       
                       (select crp.interest_balance  from tre_loan_con_repayment_plan crp where crp.repayment_plan_id
                       =hls_monthend_pkg.get_last_overdue_id(p_internal_period_num => ${@internal_period_num},
                                                              p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                              p_cf_item             => 900))
                       
                       ,0) current_balance,
                       NVL(crp.interest_balance,0) next_balance,
                       
                       crp.times,
                       
                       hls_monthend_pkg.get_last_overdue_date(p_internal_period_num => ${@internal_period_num},
                                                              p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                              p_cf_item             => 900) AS last_maturity_date,
                       
                       to_char(hls_monthend_pkg.get_last_overdue_date(p_internal_period_num => ${@internal_period_num},
                                                                      p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                                      p_cf_item             => 900),
                               'yyyy-mm-dd') last_maturity_date_n,
                       
                       hls_monthend_pkg.get_first_overdue_date(p_internal_period_num =>${@internal_period_num},
                                                               p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                               p_cf_item             => 900) AS next_maturity_date,
                       
                       to_char(hls_monthend_pkg.get_first_overdue_date(p_internal_period_num =>${@internal_period_num},
                                                                       p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                                       p_cf_item             => 900),
                               'yyyy-mm-dd') next_maturity_date_n,
                       
                       hls_monthend_pkg.get_first_overdue_amount(p_internal_period_num =>${@internal_period_num},
                                                                 p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                                 p_cf_item             => 900) interest,
                       
                       '后付' AS pay_type,
                       
                       (add_months(to_date(${@internal_period_num},'yyyymm'),1)-1-
                       hls_monthend_pkg.get_last_overdue_date(p_internal_period_num =>${@internal_period_num},
                                                               p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                               p_cf_item             => 900) + 1) guarantee_days,

                       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 (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             => 900)) 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>
