<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hand  
    $Date: 2017-9-29 下午2:26:55  
    $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 * FROM(
select hc.contract_number,
       hc.contract_name
  from hn_contract_quotation_lv hc
 where hc.WFL_STATUS NOT IN ('APPROVED')
UNION
select cc.contract_number,
       cc.contract_name  
  from con_contract cc
 where cc.data_class = 'NORMAL'
   AND CC.CONTRACT_STATUS NOT IN ('PENDING', 'CANCEL')
   AND NOT EXISTS
 (SELECT 1
          FROM HN_CONTRACT_QUOTATION CQ
         WHERE CQ.CONTRACT_ID = CC.CONTRACT_ID
           AND CQ.WFL_STATUS IN ('NEW', 'REJECT', 'APPROVING', 'APPROVED'))
   and not exists (select 1
          from con_contract_cashflow ccc
         where ccc.cf_item not in (0)
           AND CCC.CONTRACT_ID = CC.CONTRACT_ID
           AND CCC.TIMES > 0
           and ccc.write_off_flag <> 'NOT')
   and exists (select 1
          from con_contract_cashflow t
         where t.contract_id = cc.contract_id
           and t.cf_item = 0
           and t.write_off_flag = 'FULL')
   and exists
 (select 1
          from aut_trx_user_authorize a1, aut_owner_user_authorize a2
         where a1.trx_category = 'CONTRACT'
           and a1.trx_id = cc.contract_id
           and trunc(sysdate) between a1.start_date and
               nvl(a1.end_date, trunc(sysdate))
           and a1.user_id = a2.owner_user_id
           and a1.trx_category = a2.trx_category
           and a2.authorized_user_id = ${/session/@user_id}
           and trunc(sysdate) between a2.start_date and
               nvl(a2.end_date, trunc(sysdate)))
          )t1
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" forDisplay="true" forQuery="true" physicalName="contract_number" prompt="合同编号"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" forDisplay="true" forQuery="true" physicalName="contract_name" prompt="合同名称"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="="/>
        <bm:query-field field="contract_name" queryOperator="="/>
    </bm:query-fields>
</bm:model>
