<?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,

                          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,
                          t.is_buyout,
                          (Select v1.code_value_name
                           From sys_code_values_v v1
                           Where v1.code = 'YES_OR_NO'
                                 And v1.code_enabled_flag = 'Y'
                                 And v1.code_value_enabled_flag = 'Y'
                                 and v1.CODE_VALUE = t.is_buyout)                is_buyout_n,
                              (select sh.large_balance from prj_quotation sh where sh.DOCUMENT_ID = t.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 = t.project_id)) large_balance_n,
                                 t.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 = t.branch_code) branch_code_n
                      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: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>
