<?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 v.*,
       --未收金额
       (due_amount-received_amount) unreceived_amount
  FROM (SELECT
     t.contract_number,
    (SELECT bp_name FROM hls_bp_master h WHERE h.bp_id=t.bp_id_tenant) bp_tenant_name,
    (SELECT H.extra_nam FROM hls_bp_master h WHERE h.bp_id=t.bp_id_agent_level1) agent_extra_name,
    to_char(t.lease_start_date, 'yyyy-mm-dd') lease_start_date,
    ccli.machine_number,
    ccli.modelcd,
   ( ccc.times||(SELECT ci.description FROM hls_cashflow_item ci WHERE ci.cf_item=ccc.cf_item) ) times,

    to_char(ccc.due_date, 'yyyy-mm-dd') due_date,
    (NVL(t.int_rate_display, 0) * 100 || '%') int_rate_display, --利率
    --应收款金额
   (ccc.due_amount - NVL((SELECT SUM(cwo.write_off_due_amount) FROM csh_transaction t,csh_write_off cwo
                                       WHERE cwo.cashflow_id=ccc.cashflow_id
                                          AND t.transaction_id=cwo.csh_transaction_id
                                          AND NVL(cwo.reversed_flag,'N')='N'
                                          AND TRUNC(T.transaction_date) <=TRUNC(to_date(${@before_end},'yyyy-mm-dd'))),0))due_amount,
   --已收款金额
    NVL((SELECT SUM(cwo.write_off_due_amount) FROM csh_transaction t,csh_write_off cwo
                                       WHERE cwo.cashflow_id=ccc.cashflow_id
                                          AND t.transaction_id=cwo.csh_transaction_id
                                          AND NVL(cwo.reversed_flag,'N')='N'
                                          AND TRUNC(T.transaction_date) >= TRUNC(to_date(${@cur_begin},'yyyy-mm-dd'))
                                          AND TRUNC(T.transaction_date) <= TRUNC(to_date(${@cur_end},'yyyy-mm-dd'))
                                          ),0) received_amount,
        --相减
      --逾期日
     (
        CASE
          WHEN (ccc.due_amount- NVL((SELECT SUM(cwo.write_off_due_amount) FROM csh_transaction t,csh_write_off cwo
                                       WHERE cwo.cashflow_id=ccc.cashflow_id
                                          AND t.transaction_id=cwo.csh_transaction_id
                                          AND NVL(cwo.reversed_flag,'N')='N'
                                          AND TRUNC(T.transaction_date) <= TRUNC(to_date(${@cur_end},'yyyy-mm-dd'))
                                          ),0)) >0 THEN
                FLOOR(TO_date(${@cur_end},'yyyy-mm-dd')-ccc.due_date)
          ELSE
              0
          END
          ) overdue_days,
        --保证金
       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 NVL(tt.reversed_flag,'N')='N'
          and tt.ref_contract_id = T.contract_id),0) deposit_amount

from con_contract_cashflow ccc,con_contract t,con_contract_lease_item ccli
 WHERE ccc.contract_id=t.contract_id
 AND ccli.contract_id(+)=t.contract_id
AND ccc.cf_item IN (1, 8, 200, 250)
   AND ccc.cf_status = 'RELEASE'
   AND t.data_class = 'NORMAL'
   AND trunc(ccc.due_date) <=
       trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))
   AND ((trunc(t.lease_start_date) <=
       trunc(to_date(${@cur_end}, 'yyyy-mm-dd')) AND
       t.contract_status = 'INCEPT') OR
       ( trunc(t.et_date) >= trunc(to_date(${@cur_begin}, 'yyyy-mm-dd')) AND
       t.contract_status = 'TERMINATE'))
   AND ccc.due_amount >
       nvl((SELECT SUM(cwo.write_off_due_amount)
             FROM csh_transaction t, csh_write_off cwo
            WHERE cwo.cashflow_id = ccc.cashflow_id
              AND t.transaction_id = cwo.csh_transaction_id
              AND nvl(cwo.reversed_flag, 'N') = 'N'
              AND trunc(t.transaction_date) <
                  trunc(to_date(${@cur_begin}, 'yyyy-mm-dd'))),
           0)
     order by t.contract_id
) v
                
            ]]></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: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="times"/>
        <bm:field name="due_date"/>
        <bm:field name="due_amount"/>
        <bm:field name="received_amount"/>
        <bm:field name="unreceived_amount"/>
        <bm:field name="overdue_days"/>
        <bm:field name="deposit_amount"/>

    </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>
