<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: zhangxing5129  
    $Date: 2013-12-10 下午03:41:29  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:e="leaf.service.exception" xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <!-- <bm:fields>
<bm:field name="unit_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
</bm:fields> -->
    <bm:operations>
        <bm:operation name="query">
            <!--<bm:query-sql><![CDATA[-->
                <!--SELECT-->
                    <!--t1.*-->
                <!--FROM-->
                    <!--(SELECT-->
                        <!--NULL user_id,-->
                        <!--NULL employee_id,-->
                        <!--NULL employee_code,-->
                        <!--NULL employee_name,-->
                        <!--NULL email,-->
                        <!-- -1 * cv.company_id unit_id,-->
                        <!--cv.company_code unit_code,-->
                        <!--cv.company_short_name unit_name,-->
                        <!--NULL NAME,-->
                        <!--NULL parent_unit_id,-->
                        <!--NULL position_code,-->
                        <!--NULL user_name,-->
                        <!--NULL position_desc,-->
                        <!--rpad(cv.company_code, 10, '0')-->
                        <!--|| rpad('0', 10, '0') AS seq,-->
                        <!--NULL session_id,-->
                        <!--NULL checked,-->
                        <!--NULL bp_code,-->
                        <!--NULL bp_name,-->
                        <!--NULL extra_nam,-->
                        <!--NULL bp_category,-->
                        <!--NULL bp_category_n-->
                    <!--FROM-->
                        <!--fnd_companies_vl cv-->
                    <!--WHERE-->
                        <!--cv.enabled_flag = 'Y'-->
                    <!--UNION ALL-->
                    <!--SELECT-->
                        <!--NULL user_id,-->
                        <!--NULL employee_id,-->
                        <!--NULL employee_code,-->
                        <!--NULL employee_name,-->
                        <!--NULL email,-->
                        <!--u.unit_id,-->
                        <!--u.unit_code,-->
                        <!--u.unit_name,-->
                        <!--NULL NAME,-->
                        <!-- -1 * u.company_id parent_unit_id,-->
                        <!--NULL position_code,-->
                        <!--NULL user_name,-->
                        <!--NULL position_desc,-->
                        <!--rpad(u.unit_code, 10, '0')-->
                        <!--|| rpad('0', 10, '0') AS seq,-->
                        <!--NULL session_id,-->
                        <!--NULL checked,-->
                        <!--NULL bp_code,-->
                        <!--NULL bp_name,-->
                        <!--NULL extra_nam,-->
                        <!--NULL bp_category,-->
                        <!--NULL bp_category_n-->
                    <!--FROM-->
                        <!--exp_org_unit_v u-->
                    <!--WHERE-->
                        <!--u.enabled_flag = 'Y'-->
                    <!--UNION ALL-->
                    <!--SELECT-->
                        <!--s.user_id,-->
                        <!--(SELECT-->
                            <!--t.employee_id-->
                        <!--FROM-->
                            <!--exp_employee_assigns_v t-->
                        <!--WHERE-->
                            <!--t.user_id               = s.user_id AND-->
                            <!--t.enabled_flag          = 'Y' AND-->
                            <!--t.primary_position_flag = 'Y'-->
                        <!--) employee_id,-->
                        <!--(SELECT-->
                            <!--t.employee_code-->
                        <!--FROM-->
                            <!--exp_employee_assigns_v t-->
                        <!--WHERE-->
                            <!--t.user_id               = s.user_id AND-->
                            <!--t.enabled_flag          = 'Y' AND-->
                            <!--t.primary_position_flag = 'Y'-->
                        <!--) employee_code,-->
                        <!--(SELECT-->
                            <!--t.employee_name-->
                        <!--FROM-->
                            <!--exp_employee_assigns_v t-->
                        <!--WHERE-->
                            <!--t.user_id               = s.user_id AND-->
                            <!--t.enabled_flag          = 'Y' AND-->
                            <!--t.primary_position_flag = 'Y'-->
                        <!--) employee_name,-->
                        <!--NULL email,-->
                        <!--(SELECT-->
                            <!--t.unit_id-->
                        <!--FROM-->
                            <!--exp_employee_assigns_v t-->
                        <!--WHERE-->
                            <!--t.user_id               = s.user_id AND-->
                            <!--t.enabled_flag          = 'Y' AND-->
                            <!--t.primary_position_flag = 'Y'-->
                        <!--) unit_id,-->
                        <!--(SELECT-->
                            <!--t.unit_code-->
                        <!--FROM-->
                            <!--exp_employee_assigns_v t-->
                        <!--WHERE-->
                            <!--t.user_id               = s.user_id AND-->
                            <!--t.enabled_flag          = 'Y' AND-->
                            <!--t.primary_position_flag = 'Y'-->
                        <!--) unit_code,-->
                        <!--(SELECT-->
                            <!--t.unit_name-->
                        <!--FROM-->
                            <!--exp_employee_assigns_v t-->
                        <!--WHERE-->
                            <!--t.user_id               = s.user_id AND-->
                            <!--t.enabled_flag          = 'Y' AND-->
                            <!--t.primary_position_flag = 'Y'-->
                        <!--) unit_name,-->
                        <!--s.user_name AS NAME,-->
                        <!--NULL parent_unit_id,-->
                        <!--(SELECT-->
                            <!--t.position_code-->
                        <!--FROM-->
                            <!--exp_employee_assigns_v t-->
                        <!--WHERE-->
                            <!--t.user_id               = s.user_id AND-->
                            <!--t.enabled_flag          = 'Y' AND-->
                            <!--t.primary_position_flag = 'Y'-->
                        <!--) position_code,-->
                        <!--s.description user_name,-->
                        <!--(SELECT-->
                            <!--t.position_name-->
                        <!--FROM-->
                            <!--exp_employee_assigns_v t-->
                        <!--WHERE-->
                            <!--t.user_id               = s.user_id AND-->
                            <!--t.enabled_flag          = 'Y' AND-->
                            <!--t.primary_position_flag = 'Y'-->
                        <!--) position_desc,-->
                        <!--NULL seq,-->
                        <!--t.session_id,-->
                        <!--DECODE(t.session_id, NULL, 'N', 'Y') checked,-->
                        <!--(SELECT h.bp_code-->
                        <!--FROM hls_bp_master h-->
                        <!--where h.bp_id = s.bp_id) bp_code,-->
                        <!--(SELECT h.bp_name-->
                        <!--FROM hls_bp_master h-->
                        <!--where h.bp_id = s.bp_id) bp_name,-->
                        <!--(SELECT h.extra_nam-->
                        <!--FROM hls_bp_master h-->
                        <!--where h.bp_id = s.bp_id) extra_nam,-->
                        <!--s.bp_category,-->
                         <!--decode(s.bp_category,'EMPLOYEE','员工','AGENT','代理商')bp_category_n-->
                    <!--FROM-->
                        <!--sys_user s,-->
                        <!--aut_authorized_user_batch_tmp t-->
                    <!--WHERE-->
                        <!--s.user_id                           = t.user_id(+) AND-->
                        <!--TRUNC(NVL(s.start_date,sysdate-1)) <= TRUNC(sysdate)-->
                        <!--&#45;&#45;and trunc(nvl(s.end_date,sysdate+1)) > trunc(sysdate)-->
                        <!--AND-->
                        <!--t.session_id(+) = ${/session/@session_id}-->
                    <!--) t1 #WHERE_CLAUSE# START-->
                <!--WITH parent_unit_id IS NULL CONNECT BY t1.parent_unit_id = PRIOR t1.unit_id-->
                <!--ORDER BY-->
                    <!--t1.seq-->
            <!--]]></bm:query-sql>-->
            <bm:query-sql><![CDATA[
            select t1.* from (
 SELECT s.user_id,
               av.employee_id employee_id,
               av.employee_code employee_code,
               av.employee_name

               employee_name,
               NULL email,
               av.unit_id

               unit_id,
               av.unit_code

               unit_code,
               av.unit_name

               unit_name,
               s.user_name AS NAME,
               NULL parent_unit_id,
               av.position_code

               position_code,
               s.description user_name,
               av.position_name

               position_desc,
               NULL seq,
               t.session_id,
               DECODE(t.session_id, NULL, 'N', 'Y') checked,
               h.bp_code bp_code,
               h.bp_name bp_name,
               h.extra_nam extra_nam,
               s.bp_category,
               decode(s.bp_category, 'EMPLOYEE', '员工', 'AGENT', '代理商') bp_category_n
          FROM sys_user s
          left outer join hls_bp_master h
            on h.bp_id = s.bp_id
          left outer join exp_employee_assigns_v av
            on av.user_id = s.user_id
           AND av.enabled_flag = 'Y'
           AND av.primary_position_flag = 'Y'
          left outer join aut_authorized_user_batch_tmp t
            on s.user_id = t.user_id
           AND t.session_id = ${/session/@user_id}
         where TRUNC(NVL(s.start_date, sysdate - 1)) <= TRUNC(sysdate)) t1
#WHERE_CLAUSE#
 ORDER BY t1.seq
                ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
              declare
                v_count number;
            begin
              if ${@checked} = 'Y' then
                begin
                  select 1 into v_count from aut_authorized_user_batch_tmp where user_id = ${@user_id} and session_id = ${/session/@session_id};
                exception
                  when others then
                    INSERT INTO aut_authorized_user_batch_tmp
              (session_id,
               user_id,
               CREATED_BY,
               CREATION_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_DATE)
            VALUES
              (${/session/@session_id},
               ${@user_id},
               ${/session/@user_id},
               sysdate,
               ${/session/@user_id},
               sysdate);
                end;
              end if;
            end;

        ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="delete">
            <bm:update-sql><![CDATA[
              declare
                v_count number;
            begin
            delete from aut_authorized_user_batch_tmp where  session_id = ${/session/@session_id};

            end;

        ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <e:exception-descriptor-config>
        <e:exception-descriptor exception="java.sql.SQLException" handleClass="leaf.database.SQLExceptionDescriptor">
            <e:error-message code="1" message="AUT106.USER_ID_UNIQUE_ERROR"/>
        </e:exception-descriptor>
    </e:exception-descriptor-config>
    <bm:data-filters>
        <bm:data-filter name="position_desc" expression="(t1.position_desc is not null)"/>
    </bm:data-filters>
    <bm:query-fields>
        <!-- <bm:query-field name="unit_name" queryExpression="t1.unit_name =(select v.code_value from sys_code_values_v v where v.code_value_name=${@unit_name} and v.code =&apos;USER_NATURE&apos;)"/> -->
        <!--<bm:query-field name="province_description" queryExpression="t1.province_description =${@province_description}"/>-->
        <bm:query-field name="unit_name" queryExpression="t1.unit_name like ${@unit_name}"/>
        <bm:query-field name="position_desc" queryExpression="t1.position_desc like ${@position_desc}"/>
        <bm:query-field name="name" queryExpression="t1.name like ${@name}"/>
        <bm:query-field name="employee_name" queryExpression="t1.employee_name like ${@employee_name}"/>
        <bm:query-field name="bp_category" queryExpression="t1.bp_category like ${@bp_category}" />
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}" />
        <bm:query-field name="extra_nam" queryExpression="t1.extra_nam like ${@extra_nam}" />
        <bm:query-field name="bp_code" queryExpression="t1.bp_code like ${@bp_code}" />
    </bm:query-fields>
</bm:model>
