<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-6-20 下午2:55:23  
    $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 t.reservation_id,
        decode(v.DATA_CLASS,'NORMAL',v.project_number,'VIRTUAL_CON',v.VIRTUAL_CON_NUMBER) document_number,
        v.virtual_con_name document_name,
        v.bp_name,
        v.project_number,
        v.project_name,
        t.project_id,
        t.reserve_amount,
        t.paid_date,
        t.status,
        (SELECT fd.description_text
           FROM sys_codes sc, sys_code_values scv, fnd_descriptions fd
          WHERE sc.code = 'RESERVE_STATUS'
            AND sc.code_id = scv.code_id
            AND scv.code_value = t.status
            AND fd.description_id = scv.code_value_name_id
            AND fd.language = 'ZHS') status_n,
        t.description,
        t.paid_date_fin,
        t.confirm_status,
        (SELECT fd.description_text
           FROM sys_codes sc, sys_code_values scv, fnd_descriptions fd
          WHERE sc.code = 'RESERVATION_CONFIRM_STATUS'
            AND sc.code_id = scv.code_id
            AND scv.code_value = t.confirm_status
            AND fd.description_id = scv.code_value_name_id
            AND fd.language = 'ZHS') as confirm_status_desc,
        t.description_fin
   from tre_funds_reservation t, prj_project_v v
  where t.project_id = v.project_id
    and t.status = 'RESERVED'
    AND NOT EXISTS (SELECT 1
           FROM prj_project pp1
          WHERE pp1.ref_project_id = v.project_id)
    and EXISTS (SELECT 1
           FROM tre_funds_reservation r
          WHERE r.project_id = v.project_id
            AND r.status = 'RESERVED')
            ) tt #WHERE_CLAUSE#
            ORDER BY
                    tt.document_number DESC
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="document_name" queryExpression="tt.document_name like &apos;%&apos; || ${@document_name}|| &apos;%&apos;"/>
        <bm:query-field name="paid_date_from" queryExpression="tt.paid_date &gt;= to_date(${@paid_date_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="paid_date_to" queryExpression="tt.paid_date &lt;= to_date(${@paid_date_to},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="reserve_amount" queryExpression="tt.reserve_amount like &apos;%&apos; || ${@reserve_amount}|| &apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
