<?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,
                --机号
                to_char(NVL(ct.int_rate_display, 0) * 100, 'FM99,999,999,990.00') || '%' int_rate_display,
                -- (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 ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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)<=0) then 0 else
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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)
                end) month_due_amount,
                --当月末应收款余额（合计）
                (CASE
                WHEN (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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) - 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
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END) <= 0 THEN
                0
                ELSE
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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) - 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
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
                END) month_due_amount_after,
                --当月末应收款余额（合计）保证金后
                (case when ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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) <=0) then 0 else
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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)
                end) month_due_principal,
                --当月末本金余额（合计）
                (CASE
                WHEN (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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) - 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
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END) <= 0 THEN
                0
                ELSE
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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) - 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
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
                END) month_due_principal_after,
                --当月末本金余额（合计）保证金后
                (case when ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                AND cw.contract_id = ct.contract_id
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END) <=0) then 0 else
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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)
                end) month_interest,
                --当月末利息余额（合计）
                (CASE
                WHEN ((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
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))) - ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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))) > 0 THEN
                (CASE
                WHEN ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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) - (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
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))) - (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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)) <= 0 THEN
                0
                ELSE
                ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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) -
                (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
                AND tt.transaction_date <= trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))) -
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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))
                END)
                ELSE
                (CASE
                WHEN (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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) <= 0 THEN
                0
                ELSE
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE') -
                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, 250)
                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)
                END)
                END) month_interest_after,
                --当月末利息余额（合计）保证金后
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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)
                and ccw.cf_status = 'RELEASE') -
                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 IN ('TERMINATE', 'REPUR', 'ET') 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_after,
                --逾期期数(保证金后)
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                (Select count(*)
                From con_contract_cashflow ccw
                Where trunc(ccw.due_date) <= trunc(to_date(${@cur_end},'yyyy-mm-dd'))
                And ccw.contract_id = ct.contract_id
                And ccw.cf_item In (1, 200,250)
                and ccw.cf_status = 'RELEASE'
                And ccw.times > 0
                and ccw.due_amount > ccw.received_amount)
                END) business_due_times,
                --逾期期数(保证金前)
                (case when ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE'
                AND trunc(ccw.due_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)<=0) then 0 else (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE'
                AND trunc(ccw.due_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
                end)                                                                         business_due_amount,
                --当月末逾期金额（合计）
                (case when (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE'
                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
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)<=0 then
                0
                else
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') 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, 250)
                AND ccw.cf_status = 'RELEASE'
                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
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
                end) business_due_amount_after,
                --当月末逾期金额（合计）保证金后
                ((SELECT SUM(nvl(tt.transaction_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
                AND tt.transaction_date <= trunc(to_date(${@cur_end}, 'yyyy-mm-dd')))
                -
                (SELECT sum(nvl(cwo.WRITE_OFF_DUE_AMOUNT,0))
                FROM csh_write_off cwo
                where   cwo.write_off_type in ('CSH_RETURN','DEPOSIT_TRANSFER','DEPOSIT_CREDIT')
                AND     cwo.csh_transaction_id in (select cts.transaction_id from csh_transaction cts where cts.ref_contract_id = ct.contract_id)
                AND     cwo.write_off_date <= trunc(to_date(${@cur_end}, 'yyyy-mm-dd')))) 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,
                -- 合同终了
                 /*(CASE
                WHEN ct.contract_status IN ('INCEPT', 'ETING', 'TERMINATE', 'ET') THEN
                (SELECT to_char(MAX(cc.last_received_date),'yyyy-mm-dd')
                FROM con_contract_cashflow cc
                WHERE cc.contract_id = ct.contract_id
                AND cf_status = 'RELEASE'
                AND cf_item = 8)
                WHEN ct.contract_status IN ('REPUR') THEN
                (SELECT to_char(MAX(cc.last_received_date),'yyyy-mm-dd')
                FROM con_contract_cashflow cc
                WHERE cc.contract_id = ct.contract_id
                AND cf_status = 'RELEASE'
                AND cf_item = 250)
                ELSE
                ''
                END) terminate_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,
                (select decode(bp.bp_class, 'NP', '自然人', '法人')
    from con_contract_bp bp
   where bp.contract_id = ct.contract_id
     and bp.bp_category = 'TENANT') bp_class_n,
 (select b.description
    from hls_business_type b
   where b.business_type = ct.business_type) business_type_n,
 NVL((Select h1.code_value_name value_name
       From sys_code_values_v h1
      Where h1.code = 'SECONDARY_LEASE'
        And h1.code_enabled_flag = 'Y'
        And h1.code_value_enabled_flag = 'Y'
        AND H1.code_value = CT.SECONDARY_LEASE),
     '否') SECONDARY_LEASE_N,
 (select d.description from hls_division d where d.division = ct.division) division_n,
 nvl((select '是'
       from con_contract_change_req ccr
      where ccr.contract_id = ct.contract_id
        and ccr.req_status = 'APPROVED'
        and ccr.document_type = 'LEASE_CHAG'
        and rownum = 1),
     '否') tenant_change_flag_n,
     nvl((select '是'
             from dual
            where not exists (select 1
                     from con_contract_cashflow ccc
                    where ccc.cf_item = 1
                      and ccc.times <= 12
                      and ccc.contract_id = ct.contract_id
                      and ccc.write_off_flag != 'FULL'
                      and ccc.cf_direction = 'INFLOW')
              and exists (select 1
                     from con_contract_cashflow ccc
                    where ccc.cf_item = 1
                      and ccc.times = 12
                      and ccc.contract_id = ct.contract_id
                      and ccc.write_off_flag = 'FULL'
                      and ccc.cf_direction = 'INFLOW')),
           '否') rental_12_pay_flag,
 nvl((select '是'
             from con_contract_cashflow ccc
            where ccc.cf_item = 9
              and ccc.cf_direction = 'INFLOW'
              and ccc.contract_id = ct.contract_id
              and (ccc.due_amount > 0 or exists
                   (select 1
                      from CON_CONTRACT_CASE_RECORD    cr,
                           CON_CONTRACT_PENALTY_REDUCE pr,
                           CON_CONTRACT_PENALTY_INFO   pi
                     where cr.record_id = pr.record_id
                       and pr.penalty_reduce_id = pi.penalty_reduce_id
                       and pr.contract_id = ccc.contract_id
                       and pi.penalty_reduce_amount > 0
                       and cr.status = 'APPROVED'))
              and rownum = 1),
           '否') cf_9_exists_flag,
       nvl((select '是'
             from con_contract_cashflow ccc
            where ccc.cf_item = 9
              and ccc.cf_direction = 'INFLOW'
              and ccc.contract_id = ct.contract_id
              and (ccc.due_amount > 0 or exists
                   (select 1
                      from CON_CONTRACT_CASE_RECORD    cr,
                           CON_CONTRACT_PENALTY_REDUCE pr,
                           CON_CONTRACT_PENALTY_INFO   pi,
                           con_contract_cashflow       cf
                     where cr.record_id = pr.record_id
                       and pr.penalty_reduce_id = pi.penalty_reduce_id
                       and pr.contract_id = ccc.contract_id
                       and pi.penalty_reduce_amount > 0
                       and cr.status = 'APPROVED'
                       and pi.cashflow_id = cf.cashflow_id
                       and cf.times > 1))
              and ccc.times > 1
              and rownum = 1),
           '否') cf_9_notfirsttime_exists_flag
                    ,decode(acr_invoice_pkg.check_rl_flag(ct.contract_id), 'N', '否', 'Y', '是') rl_flag_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" datatype="java.lang.String"/>
        <bm:field name="lease_times" datatype="java.lang.Long"/>
        <bm:field name="down_payment_ratio"/>
        <bm:field name="lease_item_amount" datatype="java.lang.Double"/>
        <bm:field name="residual_amount" datatype="java.lang.Double"/>
        <bm:field name="contract_amount" datatype="java.lang.Double"/>
        <bm:field name="down_payment" datatype="java.lang.Double"/>
        <bm:field name="month_due_amount" datatype="java.lang.Double"/>
        <bm:field name="month_due_amount_after" datatype="java.lang.Double"/>
        <bm:field name="month_due_principal" datatype="java.lang.Double"/>
        <bm:field name="month_due_principal_after" datatype="java.lang.Double"/>
        <bm:field name="month_interest" datatype="java.lang.Double"/>
        <bm:field name="month_interest_after" datatype="java.lang.Double"/>
        <bm:field name="month_residual" datatype="java.lang.Double"/>
        <bm:field name="business_due_times" datatype="java.lang.Long"/>
        <bm:field name="business_due_times_after" datatype="java.lang.Long"/>
        <bm:field name="business_due_amount" datatype="java.lang.Double"/>
        <bm:field name="business_due_amount_after" datatype="java.lang.Double"/>
        <bm:field name="deposit_amount" datatype="java.lang.Double"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="residual_date"/>
        <bm:field name="terminate_date"/>
        <bm:field name="is_buyout_n"/>
        <bm:field name="bp_class_n"/>
        <bm:field name="business_type_n"/>
        <bm:field name="division_n"/>
        <bm:field name="tenant_change_flag_n"/>
        <bm:field name="rental_12_pay_flag"/>
        <bm:field name="cf_9_exists_flag"/>
        <bm:field name="cf_9_notfirsttime_exists_flag"/>
        <bm:field name="secondary_lease_n"/>
        <bm:field name="rl_flag_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>
