<?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 distinct
               
       p.bp_id_tenant bp_id,
       pp.bp_name bp_id_n,
       p.project_number,
       p.document_type,
       p.document_type_n,
       p.bp_class,
       p.bp_class_n,
       p.business_type,
       p.business_type_n,
       p.lease_channel,
       p.lease_channel_n,
       p.car_type,
       p.car_type_n,
       p.invoice_agent_id,
       p.invoice_agent_id_n,
       p.project_name,
       p.employee_id,
       p.employee_id_n,
       p.province,
       p.province_n,
       p.city,
       p.bp_code,
       p.city_n,
       p.district,
       p.district_n,
       pp.work_unit_phone,
       p.gender,
       p.gender_n,
       p.id_type,
       p.id_type_n,
       p.id_card_no,
       p.cell_phone,
       p.liv_province,
       p.liv_city,
       p.liv_district,
       p.liv_province_n,
       p.liv_city_n,
       p.liv_district_n,
       p.industry,
       p.industry_n,
       p.other_guarantee,
       p.work_unit,
       ss.guaranty_mode,
       (select  v.code_value_name as value_name
  from sys_code_values_v v
 where v.code = 'GUARANTY_MODE'
   and v.code_enabled_flag = 'Y'
   and v.code_value_enabled_flag = 'Y'
   and v.code_value = ss.guaranty_mode) guaranty_mode_n,
   ss.other_mortgage,
   (select  v.code_value_name as value_name
  from sys_code_values_v v
 where v.code = 'YES_NO'
   and v.code_enabled_flag = 'Y'
   and v.code_value_enabled_flag = 'Y'
   and v.code_value =ss.other_mortgage
   ) other_mortgage_n,
       ss.created_by,
       p.create_date create_date1,
       to_char(p.create_date,'yyyy-mm-dd') create_date,
       p.project_status,
       p.project_status_n,
       ss.division,
       p.division_n,
       ss.insurance_or_not,
       
       (SELECT 
       v.code_value_name value_name
  FROM sys_code_values_v v
 WHERE v.code = 'YES_NO'
       AND v.code_enabled_flag = 'Y'
       AND v.code_value_enabled_flag = 'Y'
       and v.code_value = ss.insurance_or_not) insurance_or_not_n,
       
       ss.lease_organization,
       ss.unit_id,
       p.lease_organization_n,
       p.unit_id_n,
       p.register_day,
       pp.prj_bp_id,
       p.REGIONAL_MANAGER_ID manager_id,
       p.regional_manager_id_n manager_id_n,
        ss.main_finance,
       pp.contact_person,
       dff.agent_type_n,
        dff.agent_type,
       
       pp.registered_capital,
       to_char(pp.founded_date,'yyyy-mm-dd') founded_date,
       pp.business_license_num,
       pp.project_id
    
  from PRJ_PROJECT_DETAIL_LV p, prj_project ss,prj_project_bp  pp,prj_project_dff_detail_lv dff
  where p.project_id = ss.project_id
  and dff.project_id = ss.project_id
  and pp.project_id = ss.project_id
  and pp.bp_type = 'TENANT'
  and ss.project_status  in ('NEW','PRE_APPROVING','PRE_APPROVED','APPROVED_RETURN')
  AND   p.create_date >= to_date(nvl(${@create_date},'1700-01-01'),'yyyy-mm-dd')
  AND   p.create_date <= to_date(nvl(${@to_create_date},'2900-12-30'),'yyyy-mm-dd')
  and exists (select 1 from aut_trx_user_authorize a1,aut_owner_user_authorize a2 where a1.trx_category = 'PROJECT' 
            and a1.trx_id = ss.project_id
            and trunc(sysdate) between a1.start_date and nvl(a1.end_date,trunc(sysdate)) 
            and a1.user_id = a2.owner_user_id 
            and a1.trx_category = a2.trx_category  
            and a2.authorized_user_id =${@user_id} 
            and trunc(sysdate) between a2.start_date and nvl(a2.end_date,trunc(sysdate)))
    order by pp.project_id desc

) t #WHERE_CLAUSE#
                        ORDER BY
                            t.project_id 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="agent_type"/>
        <bm:field name="insurance_or_not"/>
        <bm:field name="insurance_or_not_n"/>
        <bm:field name="agent_type_n"/>
        <bm:field name="main_finance"/>
        <bm:field name="contact_person"/>
        <bm:field name="prj_bp_id"/>
        <bm:field name="lease_organization"/>
        <bm:field name="lease_organization_n"/>
        <bm:field name="unit_id"/>
        <bm:field name="unit_id_n"/>
        <bm:field name="register_day"/>
        <bm:field name="registered_capital"/>
        <bm:field name="founded_date"/>
        <bm:field name="business_license_num"/>
        <bm:field name="division_n"/>
        <bm:field name="create_date"/>
        <bm:field name="project_status"/>
        <bm:field name="project_status_n"/>
        <bm:field name="division"/>
        <bm:field name="other_mortgage"/>
        <bm:field name="other_mortgage_n"/>
        <bm:field name="guaranty_mode"/>
        <bm:field name="guaranty_mode_n"/>
        <bm:field name="other_guarantee"/>
        <bm:field name="liv_city"/>
        <bm:field name="liv_city_n"/>
        <bm:field name="liv_district"/>
        <bm:field name="liv_district_n"/>
        <bm:field name="work_unit"/>
        <bm:field name="work_unit_phone"/>
        <bm:field name="gender"/>
        <bm:field name="gender_n"/>
        <bm:field name="id_type"/>
        <bm:field name="id_type_n"/>
        <bm:field name="id_card_no"/>
        <bm:field name="cell_phone"/>
        <bm:field name="liv_province"/>
        <bm:field name="liv_province_n"/>
        <bm:field name="project_name"/>
        <bm:field name="employee_id"/>
        <bm:field name="employee_id_n"/>
        <bm:field name="province"/>
        <bm:field name="province_n"/>
        <bm:field name="city"/>
        <bm:field name="city_n"/>
        <bm:field name="district"/>
        <bm:field name="district_n"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_n"/>
        <bm:field name="car_type"/>
        <bm:field name="car_type_n"/>
        <bm:field name="invoice_agent_id"/>
        <bm:field name="invoice_agent_id_n"/>
        <bm:field name="project_id"/>
        <bm:field name="bp_id"/>
        <bm:field name="bp_code"/>
        <bm:field name="bp_id_n"/>
        <bm:field name="project_number"/>
        <bm:field name="document_type"/>
        <bm:field name="document_type_n"/>
        <bm:field name="bp_class"/>
        <bm:field name="created_by"/>
        <bm:field name="manager_id"/>
        <bm:field name="manager_id_n"/>
        <bm:field name="bp_class_n"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="create_date1" queryExpression="t.create_date1 &gt;= to_date(${@create_date},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="create_date1" queryExpression="t.create_date1 &lt;= to_date(${@to_create_date},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="bp_code" queryExpression="t.bp_code=${@bp_code}"/>
        <bm:query-field name="created_by" queryExpression="t.created_by=${@user_id}"/>
        <bm:query-field name="project_id" queryExpression="t.project_id=${@project_id}"/>
        <bm:query-field name="bp_id_n" queryExpression="t.bp_id_n=${@bp_id_n}"/>
        <bm:query-field name="project_number" queryExpression="t.project_number=${@project_number}"/>
        <bm:query-field name="project_name" queryExpression="t.project_name=${@project_name}"/>
        <bm:query-field name="project_status_n" queryExpression="t.project_status_n=${@project_status_n}"/>
        <bm:query-field name="searchInput" queryExpression="(t.bp_id_n like &apos;%&apos;||${@searchInput}||&apos;%&apos; or t.bp_class_n like &apos;%&apos;||${@searchInput}||&apos;%&apos; or t.business_type_n like &apos;%&apos;||${@searchInput}||&apos;%&apos; or t.project_number like &apos;%&apos;||${@searchInput}||&apos;%&apos; or t.project_status_n like &apos;%&apos;||${@searchInput}||&apos;%&apos;)"/>
    </bm:query-fields>
</bm:model>
