<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jun 1, 2007 10:33:33 AM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
    <class name="com.xxx.ptcof.beans.GbdStatuscode" table="gbd_statuscode" >
        <composite-id name="id" class="com.xxx.ptcof.beans.GbdStatuscodeId">
            <key-property name="statuslevel" type="char">
                <column name="statuslevel" length="1" />
            </key-property>
            <key-property name="code" type="string">
                <column name="code" length="3" />
            </key-property>
        </composite-id>
        <property name="description" type="string">
            <column name="description" length="25" />
        </property>
        <property name="statustype" type="java.lang.Character">
            <column name="statustype" length="1" />
        </property>
        <property name="causeflag" type="java.lang.Character">
            <column name="causeflag" length="1" />
        </property>
        <property name="effectivefrom" type="date">
            <column name="effectivefrom" length="4" not-null="true" />
        </property>
        <property name="effectiveto" type="date">
            <column name="effectiveto" length="4" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jun 1, 2007 10:33:33 AM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
    <class name="com.xxx.ptcof.beans.GbdStatuscodetree" table="gbd_statuscodetree" >
        <id name="codeid" type="int">
            <column name="codeid" />
            <generator class="assigned" />
        </id>
        <property name="level1" type="string">
            <column name="level1" length="3" />
        </property>
        <property name="level2" type="string">
            <column name="level2" length="3" />
        </property>
        <property name="level3" type="string">
            <column name="level3" length="3" />
        </property>
        <property name="level3qualifier" type="string">
            <column name="level3qualifier" length="6" />
        </property>
        <property name="level4" type="string">
            <column name="level4" length="3" />
        </property>
        <property name="level4qualifier" type="string">
            <column name="level4qualifier" length="6" />
        </property>
        <property name="level5" type="string">
            <column name="level5" length="3" />
        </property>
        <property name="indicator1" type="java.lang.Character">
            <column name="indicator1" length="1" />
        </property>
        <property name="indicator2" type="java.lang.Character">
            <column name="indicator2" length="1" />
        </property>
        <property name="indicator3" type="java.lang.Character">
            <column name="indicator3" length="1" />
        </property>
        <property name="indicator4" type="java.lang.Character">
            <column name="indicator4" length="1" />
        </property>
        <property name="indicator5" type="java.lang.Character">
            <column name="indicator5" length="1" />
        </property>
        <property name="indicator6" type="java.lang.Character">
            <column name="indicator6" length="1" />
        </property>
        <property name="indicator7" type="java.lang.Character">
            <column name="indicator7" length="1" />
        </property>
        <property name="indicator8" type="java.lang.Character">
            <column name="indicator8" length="1" />
        </property>
        <property name="indicator9" type="java.lang.Character">
            <column name="indicator9" length="1" />
        </property>
        <property name="indicator10" type="java.lang.Character">
            <column name="indicator10" length="1" />
        </property>
        <property name="effectivefrom" type="date">
            <column name="effectivefrom" length="4" not-null="true" />
        </property>
        <property name="effectiveto" type="date">
            <column name="effectiveto" length="4" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

SELECT  new StatusMix(tree.level3,code.code,code.description)
FROM    gbd_statuscodetree tree,
OUTER gbd_statuscode code
WHERE   code.statuslevel=3
    AND tree.level3     =code.code
    AND tree.level1     =:level1
    AND tree.level2     =:level2
    AND tree.indicator2 ='E'"