<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        select decode(v.con_num, 0, 'N', 'Y') statusFlag
  from
  (select (select count(1)
                  from con_contract t1
                 where  t1.data_class in ('NORMAL','CHANGE_REQ')
                   and t1.ec_sign_type in ('INITIATE', 'SIGNING')
                   and t1.sign_type = 'ELE_SIGN'
                   and ((${@account_type} = 'PE' and exists
        (select 1
            from con_contract_bp ccb, hls_bp_master hb
           where ccb.bp_id = hb.bp_id
             and ccb.contract_id = t1.contract_id
             and (ccb.bp_category in ('TENANT', 'GUARANTOR') and
                 (ccb.id_card_no = ${@id_no} or (ccb.id_no_sp = ${@id_no} and ccb.sp_sign_flag = 'Y'))))) or
       (${@account_type} = 'AG' and exists
        (select 1
            from hls_bp_master_signer hs
           where hs.hls_bp_signer_id = t1.signer
             and hs.id_card = ${@id_no})
             and exists (select 1 from hls_bp_master hbm
             where hbm.bp_id = t1.bp_id_tenant
             and hbm.social_code = ${@organization_id})
            and exists (select 1 from con_contract_ele_signer cs
                  where cs.id_no = ${@id_no}
                  and cs.contract_id = t1.contract_id)) or
               (${@account_type} = 'AG' and exists
                (select 1
                    from hls_bp_master_signer hs,con_contract_change_req cr
                   where hs.hls_bp_signer_id = cr.signer
                     and cr.change_req_id = t1.contract_id
                     and hs.id_card = ${@id_no})
                 and exists (select 1 from con_contract_ele_signer cs
                  where cs.id_no = ${@id_no}
                  and cs.contract_id = t1.contract_id)
             ) or
       (${@account_type} = 'AG' and exists
        (select 1
            from con_contract_bp cb,hls_bp_master hbm
           where cb.contract_id = t1.contract_id
             and cb.bp_category = 'GUARANTOR'
             and cb.bp_class = 'ORG'
             and cb.bp_id = hbm.bp_id
             and hbm.social_code = ${@organization_id}
             and cb.id_card_no_leg = ${@id_no})) or
       (${@account_type} = 'LP' and exists
        (select 1
            from con_contract_bp cb, hls_bp_master hb
           where cb.bp_id = hb.bp_id
			 and cb.contract_id = t1.contract_id
             and hb.social_code = ${@id_no})))) con_num
          from dual) v
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="statusFlag"/>
    </bm:fields>
</bm:model>
