<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: WangYu  
    $Date: 2014-4-25 上午09:30:21  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select

                    (SELECT m.extra_nam
                     FROM hls_bp_master m
                     WHERE m.bp_id = ct.bp_id_agent_level1)                                                                       agent_extra_name,
                    --代理店简称
                    (SELECT m.bp_name
                     FROM hls_bp_master m
                     WHERE m.bp_id =
                           ct.bp_id_tenant)        AS                                                                             bp_tenant_name,
                    --承租人名称
                    ct.contract_number,
                    --合同编号
                    to_char(ct.lease_start_date,
                            'yyyy-mm-dd')                                                                                         lease_start_date,
                    --租赁期开始日
                    (SELECT ccli.modelcd
                     FROM con_contract_lease_item ccli
                     WHERE ccli.contract_id =
                           ct.contract_id)                                                                                        modelcd,
                    --机型
                    (SELECT i.machine_number
                     FROM con_contract_lease_item i
                     WHERE i.contract_id = ct.contract_id
                           AND i.equipment_type =
                               'MAIN')                                                                                            machine_number,
                    --机号
                    (NVL(ct.int_rate_display, 0) * 100 ||
                     '%')                                                                                                         int_rate_display,
                    --利率
                    ct.lease_times,
                    --租赁期间
                    (NVL(ct.down_payment_ratio, 0) * 100 ||
                     '%')                                                                                                         down_payment_ratio,
                    --首付比例
                    ct.lease_item_amount           as                                                                             lease_item_amount,
                    --设备款
                    NVL(ct.residual_value,
                        0)                                                                                                        residual_amount,
                    --留购金
                    NVL(ct.total_rental, 0) + NVL(ct.down_payment, 0) +
                    NVL(ct.residual_value,
                        0)                                                                                                        contract_amount,
                    --合同金额
                    NVL(ct.down_payment,
                        0)                                                                                                        down_payment,
                    --首付款
                    (case when ct.contract_status = 'TERMINATE'
                        then
                            0
                     else
                         ((select sum(ccw.due_amount)
                           from con_contract_cashflow ccw
                           where ccw.contract_id = ct.contract_id
                                 and ccw.cf_item in (1, 200)) -
                          nvl((select sum(nvl(cw.write_off_due_amount, 0))
                               from csh_transaction cn, csh_write_off cw
                               where cw.csh_transaction_id = cn.transaction_id
                                     and cw.cf_item in (1, 200)
                                     and cw.contract_id = ct.contract_id
                                     and NVL(cw.reversed_flag, 'N') = 'N'
                                     and trunc(cn.transaction_date) <=
                                         trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                              0))
                     end)                                                                                                         month_due_amount,
                    --当月末应收款余额（合计）
                    (case when ct.contract_status = 'TERMINATE'
                        then
                            0
                     else
                         ((select sum(ccw.principal)
                           from con_contract_cashflow ccw
                           where ccw.contract_id = ct.contract_id
                                 and ccw.cf_item in (1, 200)) -
                          nvl((select sum(nvl(cw.write_off_principal, 0))
                               from csh_transaction cn, csh_write_off cw
                               where cw.csh_transaction_id = cn.transaction_id
                                     and cw.cf_item in (1, 200)
                                     and cw.contract_id = ct.contract_id
                                     and NVL(cw.reversed_flag, 'N') = 'N'
                                     and trunc(cn.transaction_date) <=
                                         trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                              0))
                     end)                                                                                                         month_due_principal,
                    --当月末本金余额（合计）
                    (case when ct.contract_status = 'TERMINATE'
                        then
                            0
                     else
                         ((select sum(ccw.interest)
                           from con_contract_cashflow ccw
                           where ccw.contract_id = ct.contract_id
                                 and ccw.cf_item in (1, 200)) -
                          nvl((select sum(nvl(cw.write_off_interest, 0))
                               from csh_transaction cn, csh_write_off cw
                               where cw.csh_transaction_id = cn.transaction_id
                                     and cw.cf_item in (1, 200)
                                     and cw.contract_id = ct.contract_id
                                     and NVL(cw.reversed_flag, 'N') = 'N'
                                     and trunc(cn.transaction_date) <=
                                         trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                              0)) end)                                                                                            month_interest,
                    --当月末利息余额（合计）
                    (case when ct.contract_status = 'TERMINATE'
                        then
                            0
                     else
                         ((select sum(ccw.due_amount)
                           from con_contract_cashflow ccw
                           where ccw.contract_id = ct.contract_id
                                 and ccw.cf_item in (8)) -
                          nvl((select sum(nvl(cw.write_off_due_amount, 0))
                               from csh_transaction cn, csh_write_off cw
                               where cw.csh_transaction_id = cn.transaction_id
                                     and cw.cf_item in (8)
                                     and cw.contract_id = ct.contract_id
                                     and NVL(cw.reversed_flag, 'N') = 'N'
                                     and trunc(cn.transaction_date) <=
                                         trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                              0)) end)                                                                                            month_residual,
                    --当月末留购金余额
                    (case when ct.contract_status = 'TERMINATE'
                        then
                            0
                     else
                         con_contract_pkg.get_business_times(p_contract_id => ct.contract_id,
                                                             p_calc_date   => to_date(${@cur_end}, 'yyyy-mm-dd'),
                                                             p_contract_inception_date        => ct.contract_inception_date) end) business_due_times,
                    --逾期期数
                    (case when ct.contract_status = 'TERMINATE'
                        then
                            0
                     else
                         (nvl((select sum(ccw.due_amount) - sum(nvl((SELECT SUM(nvl(cwo.write_off_due_amount, 0))
                                                                     FROM csh_write_off cwo, csh_transaction cn
                                                                     WHERE nvl(cwo.reversed_flag, 'N') = 'N'
                                                                           AND cwo.contract_id = ccw.contract_id
                                                                           AND cwo.times = ccw.times
                                                                           and
                                                                           cwo.csh_transaction_id = cn.transaction_id
                                                                           AND cwo.cf_item = ccw.cf_item
                                                                           and trunc(cn.transaction_date) <=
                                                                               trunc(to_date(${@cur_end},
                                                                                             'yyyy-mm-dd'))),
                                                                    0))
                               from con_contract_cashflow ccw
                               where ccw.contract_id = ct.contract_id
                                     and ccw.cf_item in (1, 200, 8)
                                     and trunc(ccw.due_date) <= trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))), 0)
                          - nvl((select sum((nvl(tt.transaction_amount, 0) - nvl(tt.write_off_amount, 0) -
                                             nvl(tt.returned_amount, 0)))
                                 from csh_transaction tt
                                 where tt.transaction_type = 'DEPOSIT'
                                       AND tt.deposit_trans_type = 'rent_deposit'
                                       and tt.ref_contract_id = ct.contract_id),
                                0)) end)                                                                                          business_due_amount,
                    --当月末逾期金额（合计）

                    (select sum((nvl(tt.transaction_amount, 0) - nvl(tt.write_off_amount, 0) -
                                 nvl(tt.returned_amount, 0)))
                     from csh_transaction tt
                     where tt.transaction_type = 'DEPOSIT'
                           AND tt.deposit_trans_type = 'rent_deposit'
                           and tt.ref_contract_id =
                               ct.contract_id)                                                                                    deposit_amount,
                    --保证金金额
                    (SELECT v.code_value_name
                     FROM sys_code_values_v v
                     WHERE v.code = 'CON500_CONTRACT_STATUS'
                           AND v.code_value =
                               ct.contract_status) AS                                                                             contract_status_n,
                    --合同金额
                    (select to_char(due_date, 'yyyy-mm-dd')
                     from con_contract_cashflow
                     where contract_id = ct.contract_id
                       and cf_status='RELEASE'
                           and cf_item =
                               8)                                                                                                 residual_date,
                    -- 合同终了
                    to_char(ct.et_date,
                            'yyyy-mm-dd')                                                                                         terminate_date,
                    ct.is_buyout,
                    (Select v.code_value_name
                     From sys_code_values_v v
                     Where v.code = 'YES_OR_NO'
                           And v.code_enabled_flag = 'Y'
                           And v.code_value_enabled_flag = 'Y'
                           and v.CODE_VALUE = ct.is_buyout)                                                                       is_buyout_n,
                  (select sh.large_balance from prj_quotation sh where sh.DOCUMENT_ID = ct.project_id) large_balance,
                  (select v.code_value_name
                    from sys_code_values_v v
                   where v.code = 'SECONDARY_LEASE'
                     and v.code_value = (select sh.large_balance from prj_quotation sh where sh.DOCUMENT_ID = ct.project_id)) large_balance_n,
                     ct.branch_code,
                   (Select v.code_value_name
                  From sys_code_values_v v
                 Where v.code = 'BRANCH_CODE_SZMP'
                   And v.code_enabled_flag = 'Y'
                   And v.code_value_enabled_flag = 'Y'
                   And v.code_value = ct.branch_code) branch_code_n
                from con_contract ct
                where ct.contract_status not in ('CANCEL')
                      and ct.data_class = 'NORMAL'
                order by ct.bp_id_agent_level1
                ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                begin
                    rpt5012_rental_balance_pkg.rental_balance_insert(
                            p_month   =>${@month},
                            p_user_id      =>${/session/@user_id},
                            p_batch_id       =>${@batch_id}
                    );
                end;
                ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
                    rpt5012_rental_balance_pkg.get_month_date(
                            p_month   =>${@month},
                            p_cur_begin      =>${@cur_begin},
                            p_cur_end      =>${@cur_end},
                            p_before_begin      =>${@before_begin},
                            p_before_end       =>${@before_end}
                    );
                end;
                ]]></bm:update-sql>
            <bm:parameters>
                <bm:parameter name="cur_begin" dataType="date" input="true" output="true"
                              outputPath="/parameter/@cur_begin"/>
                <bm:parameter name="cur_end" dataType="date" input="true" output="true"
                              outputPath="/parameter/@cur_end"/>
                <bm:parameter name="before_begin" dataType="date" input="true" output="true"
                              outputPath="/parameter/@before_begin"/>
                <bm:parameter name="before_end" dataType="date" input="true" output="true"
                              outputPath="/parameter/@before_end"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="agent_extra_name"/>
        <bm:field name="bp_tenant_name"/>
        <bm:field name="contract_number"/>
        <bm:field name="lease_start_date"/>
        <bm:field name="modelcd"/>
        <bm:field name="machine_number"/>
        <bm:field name="int_rate_display"/>
        <bm:field name="lease_times"/>
        <bm:field name="down_payment_ratio"/>
        <bm:field name="lease_item_amount"/>
        <bm:field name="residual_amount"/>
        <bm:field name="contract_amount"/>
        <bm:field name="down_payment"/>
        <bm:field name="month_due_amount"/>
        <bm:field name="month_due_principal"/>
        <bm:field name="month_interest"/>
        <bm:field name="month_residual"/>
        <bm:field name="business_due_times"/>
        <bm:field name="business_due_amount"/>
        <bm:field name="deposit_amount"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="residual_date"/>
        <bm:field name="terminate_date"/>
        <bm:field name="is_buyout_n"/>

    </bm:fields>
    <!--    <bm:query-fields>-->
    <!--        <bm:query-field field="bp_id_tenant_n" queryExpression="t1.bp_id_tenant_n = ${@bp_id_tenant_n}"/>-->
    <!--        <bm:query-field field="account_name" queryExpression="t1.account_name = ${@account_name}"/>-->
    <!--        <bm:query-field field="account_id_n" queryExpression="t1.account_id_n = ${@account_id_n}"/>-->
    <!--    </bm:query-fields>-->
</bm:model>
