<?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,
        (select v.code_value_name value_name
  from sys_code_values_v v
 where v.code = 'HLS211_GENDER'
   and v.code_enabled_flag = 'Y'
   and v.code_value_enabled_flag = 'Y'
   and v.code_value = t.gender
       ) gender_n,
        
        t.phone,
        
        (select
         
          p.description as code_name
           FROM fnd_province p
          where p.province_id = t.liv_province) liv_province_n,
          t.liv_province,
        
        (select f.description
           from fnd_city f
          where f.enabled_flag = 'Y'
            and f.city_id = t.liv_city) liv_city_n,
            
        t.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_n,
            t.liv_district,
        t.industry,
        t.work_unit,
        t.work_unit_phone,
        
        t.id_type,
        (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_card_no
 
   from hls_bp_master t, hls_bp_master_role_lv ro
  where t.bp_class = 'NP'
    and t.bp_id(+) = ro.bp_id
    and ro.bp_type = 'TENANT'
    
    
) 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="gender_n"/>
        <bm:field name="phone"/>
        <bm:field name="liv_province"/>
        <bm:field name="liv_city"/>
        <bm:field name="liv_district"/>
        <bm:field name="liv_province_n"/>
        <bm:field name="liv_city_n"/>
        <bm:field name="liv_district_n"/>
        <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>
