<?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_item = 8) residual_date ,-- 合同终了
    to_char(ct.et_date, 'yyyy-mm-dd') terminate_date
  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: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>
