<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hand  
    $Date: 2017-9-27 下午4:07:41  
    $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_id,
       hc.contract_number,
       hc.contract_name,
       hc.quotation_id,
       (select ccc.full_write_off_date from con_contract_cashflow ccc
        where ccc.contract_id=hc.CONTRACT_ID
          and ccc.cf_item=0) due_date,
          (select max(hd.closed_date)
          from csh_payment_req_hd hd, csh_payment_req_ln ln
         where hd.payment_req_id = ln.payment_req_id
           and ln.ref_doc_category = 'CONTRACT'
           and ln.ref_doc_id = hc.CONTRACT_ID
           and hd.approval_status = 'APPROVED'
           and ln.payment_status = 'FULL'
           and hd.closed_flag = 'Y') closed_date,
       (select sum(h.amount)
          from CSH_PAYMENT_REQ_HD h, CSH_PAYMENT_REQ_LN l
         where h.payment_req_id = l.payment_req_id
           and l.ref_doc_id = hc.CONTRACT_ID
         group by hc.CONTRACT_ID) amount,
       hc.employee_id_n,
       hc.employee_id_of_manager_n,
       hc.lease_organization_n,
       'Y' wfl_status_flag,
       hc.wfl_status_n,
       (zj_wfl_workflow_pkg.get_approve_user_name(hc.WFL_INTANCE_ID)) approve_user_name
  from hn_contract_quotation_lv hc
 where hc.WFL_STATUS NOT IN ('APPROVED','NEW','CANCEL')
 UNION
 select hc.contract_id,
       hc.contract_number,
       hc.contract_name,
       hc.quotation_id,
       (select ccc.full_write_off_date from con_contract_cashflow ccc
        where ccc.contract_id=hc.CONTRACT_ID
          and ccc.cf_item=0) due_date,
           (select max(hd.closed_date)
          from csh_payment_req_hd hd, csh_payment_req_ln ln
         where hd.payment_req_id = ln.payment_req_id
           and ln.ref_doc_category = 'CONTRACT'
           and ln.ref_doc_id = hc.CONTRACT_ID
           and hd.approval_status = 'APPROVED'
           and ln.payment_status = 'FULL'
           and hd.closed_flag = 'Y') closed_date,
       (select sum(h.amount)
          from CSH_PAYMENT_REQ_HD h, CSH_PAYMENT_REQ_LN l
         where h.payment_req_id = l.payment_req_id
           and l.ref_doc_id = hc.CONTRACT_ID
         group by hc.CONTRACT_ID) amount,
       hc.employee_id_n,
       hc.employee_id_of_manager_n,
       hc.lease_organization_n,
       'Y' wfl_status_flag,
       '未提交确认申请' wfl_status_n,
       (zj_wfl_workflow_pkg.get_approve_user_name(hc.WFL_INTANCE_ID)) approve_user_name
  from hn_contract_quotation_lv hc
 where hc.WFL_STATUS = 'NEW'
UNION
select CC.contract_id,
       cc.contract_number,
       cc.contract_name,
       (select hc.quotation_id from HN_CONTRACT_QUOTATION hc
        where hc.CONTRACT_ID=cc.contract_id) quotation_id,
       (select ccc.full_write_off_date from con_contract_cashflow ccc
        where ccc.contract_id=cc.CONTRACT_ID
          and ccc.cf_item=0) due_date,
          (select max(hd.closed_date)
          from csh_payment_req_hd hd, csh_payment_req_ln ln
         where hd.payment_req_id = ln.payment_req_id
           and ln.ref_doc_category = 'CONTRACT'
           and ln.ref_doc_id = cc.CONTRACT_ID
           and hd.approval_status = 'APPROVED'
           and ln.payment_status = 'FULL'
           and hd.closed_flag = 'Y') closed_date,
       (select sum(h.amount)
          from CSH_PAYMENT_REQ_HD h, CSH_PAYMENT_REQ_LN l
         where h.payment_req_id = l.payment_req_id
           and l.ref_doc_id = cc.CONTRACT_ID
           and cc.data_class = 'NORMAL'
         group by cc.contract_id) amount,
       (SELECT ee.name
          FROM exp_employees ee
         WHERE ee.employee_id = CC.employee_id) employee_id_n,
       (SELECT ee.name
          FROM exp_employees ee
         WHERE ee.employee_id = CC.employee_id_of_manager) employee_id_of_manager_n,
       (SELECT lo.description
          FROM hls_lease_organization lo
         WHERE lo.lease_organization = CC.lease_organization) lease_organization_n,
         'N' wfl_status_flag,
       '未创建确认申请' wfl_status_n,
       null approve_user_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
            #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="contract_id"/>
        <bm:field name="quotation_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="quotation_id"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="contract_number" prompt="合同编号"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="contract_name" prompt="合同名称"/>
        <bm:field name="due_date" databaseType="DATE" datatype="java.util.Date" physicalName="due_date" prompt="实际支付日期"/>
        <bm:field name="closed_date" databaseType="DATE" datatype="java.util.Date" physicalName="closed_date" prompt="财务关闭日期"/>
        <bm:field name="amount" databaseType="NUMBER" datatype="java.lang.Long" physicalName="amount" prompt="申请总金额"/>
        <bm:field name="employee_id_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="employee_id_n" prompt="项目经理"/>
        <bm:field name="lease_organization_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="lease_organization_n" prompt="业务部"/>
        <bm:field name="employee_id_of_manager_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="employee_id_of_manager_n" prompt="部门负责人"/>
        <bm:field name="wfl_status_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="wfl_status_flag" prompt="工作流状态标识"/>
        <bm:field name="wfl_status_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="wfl_status_n" prompt="工作流状态"/>
        <bm:field name="approve_user_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="approve_user_name" prompt="待审批人"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="due_date_from" queryExpression="due_date &gt;= to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="due_date_to" queryExpression="due_date &lt;= to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field field="contract_number" queryOperator="="/>
        <bm:query-field field="contract_name" queryOperator="="/>
        <bm:query-field field="employee_id_n" queryOperator="="/>
        <bm:query-field field="lease_organization_n" queryOperator="="/>
    </bm:query-fields>
</bm:model>
