<?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
                    *
                FROM
                    (SELECT
                        A.*,
                        ROWNUM RN
                    FROM
                        (SELECT
                            *
                        FROM
                            (
                select t.bp_id          code,
       t.bp_name        code_name,
       ro.bp_category,
       ro.bp_category_n,
       ro.bp_type,
       ro.bp_type_n,
       t.bp_code,
       t.gender,
       
       t.phone,
       (select
         
          p.description as code_name
           FROM fnd_province p
          where p.province_id = t.liv_province) liv_province,
        
        (select f.description
           from fnd_city f
          where f.enabled_flag = 'Y'
            and f.city_id = t.liv_city) liv_city,
        
       
        (select f.description a
         
           from fnd_district f, mor_register t
          where f.enabled_flag = 'Y'
            and f.district_id = t.liv_district
            and f.district_id = t.district_id(+)) liv_district,
       t.industry,
       t.work_unit,
       t.work_unit_phone,
        (select v.code_value_name 
  from sys_code_values_v v
 where v.code = 'HLS211_ID_TYPE'
   and v.code_enabled_flag = 'Y'
   and v.code_value_enabled_flag = 'Y'
   and  v.code_value = t.id_type
) id_type_n,
       t.id_type,
       t.id_card_no
       from hls_bp_master t, hls_bp_master_role_lv  ro 
  where  ro.bp_type = 'GUTA_NP'
   and       t.bp_id(+)=ro.bp_id
   and      t.bp_class = 'NP'
     
) t #WHERE_CLAUSE#
                        ORDER BY
                            t.code DESC
                        )A
                    WHERE
                        ROWNUM <= NVL(${@pagesize},10) * NVL(${@offsize},1)
                    )
                WHERE
                    RN >= 1 + ((NVL(${@offsize},1)-1) * NVL(${@pagesize},10))
      

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CODE" required="true"/>
        <bm:field name="code_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NAME"/>
        <bm:field name="bp_category"/>
        <bm:field name="bp_category_n"/>
        <bm:field name="bp_type"/>
        <bm:field name="bp_type_n"/>
        <bm:field name="bp_code"/>
        <bm:field name="gender"/>
        <bm:field name="phone"/>
        <bm:field name="liv_province"/>
        <bm:field name="liv_city"/>
        <bm:field name="liv_district"/>
        <bm:field name="industry"/>
        <bm:field name="work_unit"/>
        <bm:field name="work_unit_phone"/>
        <bm:field name="id_type"/>
        <bm:field name="id_type_n"/>
        <bm:field name="id_card_no"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="searchInput" queryExpression="(t.code_name like &apos;%&apos;||${@searchInput}||&apos;%&apos; or t.id_card_no like &apos;%&apos;||${@searchInput}||&apos;%&apos;)"/>
        <bm:query-field name="id_card_no" queryExpression="t.id_card_no=${@searchInput}"/>
        <bm:query-field name="code_name" queryExpression="t.code_name like &apos;%&apos;||${@searchInput}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
