<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: DJ  
    $Date: 2014-1-8 下午3:44:11  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                  select t1.*
  from (select null employee_id,
               null employee_code,
               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,
               rpad(cv.company_code, 10, '0') || rpad('0', 10, '0') as seq
          from fnd_companies_vl cv
         where cv.enabled_flag = 'Y'
        union all
        
        SELECT null employee_id,
               null employee_code,
               null email,
               u.unit_id,
               u.unit_code,
               u.unit_name,
               null name,
               -1 * u.company_id parent_unit_id,
               null position_code,
               rpad(u.unit_code, 10, '0') || rpad('0', 10, '0') as seq
          FROM exp_org_unit_v u
         WHERE u.enabled_flag = 'Y'
        
        union all
        
        select e.employee_id,
               e.employee_code,
               e.email,
               null unit_id,
               d.unit_code,
               null unit_name,
               e.name,
               d.unit_id parent_unit_id,
               c.position_code,
               rpad(d.unit_code, 10, '0') || rpad(c.position_code, 10, '0')||e.employee_code as seq
          from exp_employees        e,
               exp_employee_assigns b,
               exp_org_position     c,
               exp_org_unit_v       d
         WHERE e.employee_id = b.employee_id
           AND b.position_id = c.position_id(+)
           AND c.unit_id = d.unit_id(+)
           AND b.enabled_flag = 'Y'
           AND c.enabled_flag(+) = 'Y'
           AND d.enabled_flag(+) = 'Y') t1
 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:operation>
    </bm:operations>
</bm:model>