<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Clocc  
    $Date: 2015-8-3 上午9:49:00  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select contract_id,contract_number,bp_id_tenant_n
                 from (
                    select t.contract_id,
                           t.contract_number,
                           (select bp.bp_name
                              from hls_bp_master bp
                             where t.bp_id_tenant = bp.bp_id) bp_id_tenant_n
                      from con_contract t
                     where t.data_class = 'NORMAL'
                       and exists
                     (select 1
                              from csh_transaction t1
                             where t1.ref_contract_id = t.contract_id
                               and trunc(t1.transaction_date) >= to_date('2023-10-01', 'yyyy-mm-dd')
                               and (t1.transaction_type = 'DEPOSIT')
                               and t1.deposit_trans_type in ('rent_deposit') --租金保证金
                               and (t1.ref_contract_id is not null and
                                   t1.source_type is not null)
                               and (t1.returned_flag <> 'RETURN')
                               and (t1.transaction_amount - nvl(t1.returned_amount, 0) -
                                   nvl(t1.write_off_amount, 0)) > 0
                               and ((trunc(t1.transaction_date) < trunc(t.inception_of_lease) and
                                   t.lease_times >= 12) or
                                   (trunc(t1.transaction_date) >= trunc(t.inception_of_lease) and
                                   (select to_char(max(ccc.due_date), 'yyyymm')
                                        from con_contract_cashflow ccc
                                       where ccc.contract_id = t.contract_id
                                         and ccc.cf_item = 1
                                         and ccc.cf_status = 'RELEASE') -
                                   to_char(t1.transaction_date, 'yyyymm') >= 12)))
                       and not exists (select 1
                              from hlcm_deposit_bp b
                             where b.bp_id = t.bp_id_agent_level1
                               and b.deposit_calc_type = 'DEPOSIT_POOL'
                               and b.enabled_flag = 'Y')
                       and not exists
                     (select 1
                              from con_contract_lease_item li, hls_lease_item h, hls_bp_master m
                             where li.contract_id = t.contract_id
                               and li.lease_item_id = h.lease_item_id
                               and h.vender_id = m.bp_id
                               and m.bp_category = 'VENDER'
                               and m.bp_name in
                                   ('永立建机（中国）有限公司', '日立建机销售（中国）有限公司'))
                       and not exists (select 1
                              from hlcm_deposit_data d
                             where d.document_id = t.contract_id
                               and d.deposit_type = 'R')
                       and not exists (select *
                              from hlcm_deposit_calc_apply a
                             where a.document_id = t.contract_id
                               and a.wfl_status = 'APPROVING')
                    union all
                    select t.contract_id,
                           t.contract_number,
                           (select bp.bp_name
                              from hls_bp_master bp
                             where t.bp_id_tenant = bp.bp_id) bp_id_tenant_n
                      from con_contract t
                     where t.data_class = 'NORMAL'
                       and exists (select 1
                              from hlcm_deposit_data d
                             where d.document_id = t.contract_id
                               and d.deposit_type = 'R'
                               and d.enable_calc_flag = 'N')
                       and not exists (select *
                              from hlcm_deposit_calc_apply a
                             where a.document_id = t.contract_id
                               and a.wfl_status = 'APPROVING')) t1  #WHERE_CLAUSE#

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" forDisplay="true" forQuery="true" prompt="合同编号"/>
        <bm:field name="bp_id_tenant_n" databaseType="VARCHAR2" datatype="java.lang.String" forDisplay="true" forQuery="true" prompt="承租人"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_id_tenant_n" queryOperator="like"/>
    </bm:query-fields>
</bm:model>
