<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng  
    $Date: 2017-7-14 上午10:10:50  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from(
              select t1.chance_bp_id chance_bp_id,
       t1.chance_bp_name chance_bp_name,
       t1.bp_class,
       (select v.code_value_name
        from sys_code_values_v v
        where v.code = 'HLS211_BP_CLASS'
          and v.code_value = t1.bp_class) bp_class_desc,
       'CHANCE_BP' trx_category,
       '潜在客户' trx_category_n,
       t1.owner_user_id owner_user_id,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'HLS211_BP_CLASS'
           and v.code_value = t1.bp_class) as trx_number,
       (select description
          from sys_user su
         where su.user_id = t1.owner_user_id) as owner_user_id_n,
       trunc(t1.creation_date) as creation_date_desc,
       (select un.unit_name
          from exp_org_unit_v un
         where un.unit_id = t1.unit_id) as unit_id_n
  from hn_chance_bp t1
 where t1.enabled_flag = 'Y'
   and not exists
 (select 1
          from aut_trx_user_authorize a1, aut_owner_user_authorize a2
         where a1.trx_category = 'CHANCE_BP'
           and a1.trx_id = t1.chance_bp_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 = ${/session/@user_id}
           and trunc(sysdate) between a2.start_date and
               nvl(a2.end_date, trunc(sysdate)))
union all

select t2.bp_id chance_bp_id,
       t2.bp_name chance_bp_name,
       t2.bp_class,
       (select v.code_value_name
        from sys_code_values_v v
        where v.code = 'HLS211_BP_CLASS'
          and v.code_value = t2.bp_class) bp_class_desc,
       'BP' trx_category,
       '商业伙伴' trx_category_n,
       t2.owner_user_id owner_user_id,
       t2.bp_code trx_number,
       (select description
          from sys_user su
         where su.user_id = t2.owner_user_id) as owner_user_id_n,
       trunc(t2.creation_date) as creation_date_desc,
       (select e1.unit_name
          from exp_emp_assign_e_v e1
         where e1.user_id = t2.owner_user_id
           and e1.primary_position_flag = 'Y') as unit_id_n
  from hls_bp_master t2
 where t2.enabled_flag = 'Y'
   and not exists
 (select 1
          from aut_trx_user_authorize a1, aut_owner_user_authorize a2
         where a1.trx_category = 'BP'
           and a1.trx_id = t2.bp_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 = ${/session/@user_id}
           and trunc(sysdate) between a2.start_date and
               nvl(a2.end_date, trunc(sysdate)))
            )t1 
             #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="chance_bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="chance_bp_id" prompt="chance_bp_id"/>
        <bm:field name="chance_bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="trx_name" prompt="trx_name"/>
        <bm:field name="trx_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="trx_category" prompt="trx_category"/>
        <bm:field name="trx_category_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="trx_category_n" prompt="trx_category_n"/>
        <bm:field name="bp_class_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_class_n" prompt="bp_class_n"/>
        <bm:field name="owner_user_id" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="document_owner_user_id" prompt="document_owner_user_id"/>
        <bm:field name="trx_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="trx_number" prompt="trx_number"/>
        <bm:field name="owner_user_id_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="owner_user_id_n" prompt="owner_user_id_n"/>
        <bm:field name="creation_date_desc" databaseType="DATE" datatype="java.util.Date" expression="trunc(creation_date)" forInsert="false" forUpdate="false"/>
        <bm:field name="unit_id_n" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false" physicalName="unit_id_n"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="chance_bp_name" queryExpression="t1.chance_bp_name like &apos;%&apos;||${@chance_bp_name}||&apos;%&apos;"/>
        <bm:query-field name="bp_class_desc" queryExpression="t1.bp_class_desc =${@bp_class_desc}"/>
        <bm:query-field name="owner_user_id_n" queryExpression="t1.owner_user_id_n  like &apos;%&apos; || ${@owner_user_id_n}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
