<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: zc
    $Date: 2020年11月9日
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:fields>
        <bm:field name="file_name"/>
        <bm:field name="file_path"/>
        <bm:field name="file_size"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                  select fa.file_name,fa.file_path,fa.file_size
                    from fnd_atm_attachment fa, fnd_atm_attachment_multi fm
                   where fa.attachment_id = fm.attachment_id
                     and fm.table_name = 'CON_CONTRACT_CONTENT'
                     and fm.table_pk_value in
                         (select t.content_id
                            from con_contract_content t,con_ele_signer_file cf,con_contract_ele_signer cs,con_contract_bp cb,hls_doc_file_templet_signer hs
                           where t.contract_id = ${@contract_id}
                             and t.templet_id = cf.templet_id
                             and cf.con_ele_signer_id =  ${@con_ele_signer_id}
                             and cf.con_ele_signer_id = cs.con_ele_signer_id
                             and t.con_contract_bp_id = cb.record_id
                             and cb.bp_id = cs.bp_id
                             and t.clause_usage != 'AGENT_CONTRACT'
                             and cf.templet_code != 'SETTLE_ADVANCE_OEC'
                             and cs.templet_signer_id = hs.templet_signer_id
                             and hs.signer_category in ('GUARANTOR_NP','GUARANTOR_SP','GUARANTOR_ORG_SIGN','GUARANTOR_ORG_SEAL')
                           union all
                         select t.content_id
                          from con_contract_content t,con_ele_signer_file cf,con_contract_ele_signer cs,hls_doc_file_templet_signer hs
                         where t.contract_id = ${@contract_id}
                           and t.templet_id = cf.templet_id
                           and cf.con_ele_signer_id = ${@con_ele_signer_id}
                           and cf.con_ele_signer_id = cs.con_ele_signer_id
                           and t.clause_usage != 'AGENT_CONTRACT'
                           and cf.templet_code != 'SETTLE_ADVANCE_OEC'
                           and cs.templet_signer_id = hs.templet_signer_id
                           and hs.signer_category not in ('GUARANTOR_NP','GUARANTOR_SP','GUARANTOR_ORG_SIGN','GUARANTOR_ORG_SEAL'))
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
<!--                         (select t.content_id
                            from con_contract_content t,con_ele_signer_file cf
                           where t.contract_id = ${@contract_id}
                             and t.templet_id = cf.templet_id
                             and cf.con_ele_signer_id = ${@con_ele_signer_id}
                             and cf.templet_code != 'SETTLE_ADVANCE_OEC')-->