<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: gaoyang 
    $Date: 2014-11-26 下午04:24:11  
    $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
                    faa.file_name,
                    faa.file_path,
                    faa.attachment_id,
                    a.project_number project_number,
                    (select hm.bp_name from hls_bp_master hm where hm.bp_id=a.bp_id_tenant) bp_name,
                    rownum rn
                FROM
                    fnd_atm_attachment_multi fam,
                    fnd_atm_attachment faa,
                    (select pc.check_id,pp.project_number,pp.bp_id_tenant
                      from prj_cdd_item_doc_ref pr,prj_project pp,prj_cdd_item_check pc,prj_cdd_item pi ,prj_project_zx_v pv
                      where pr.document_id=pp.project_id
                      and pv.project_id=pp.project_id
                      and pr.check_id=pc.check_id
                      and pc.cdd_item_id=pi.cdd_item_id
                      and pi.cdd_item in ('ID_CARD','CREDIT_AUTHORIZATION')
                      and pp.project_id in (select zt.project_id from zx_query_tmp zt where zt.session_id=${/session/@session_id})
                      order by pv.submit_date) a
                WHERE
                    fam.table_name         = 'PRJ_CDD_ITEM_CHECK' AND
                    fam.attachment_id      = faa.attachment_id AND
                    fam.table_pk_value     = a.check_id
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="file_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FILE_NAME"/>
        <bm:field name="file_path" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FILE_PATH"/>
        <bm:field name="attachment_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="ATTACHMENT_ID"/>
        <bm:field name="project_number" datatype="java.lang.String"/>
        <bm:field name="bp_name" datatype="java.lang.String"/>
        <bm:field name="rn" datatype="java.lang.Long"/>
    </bm:fields>
</bm:model>
