<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="RTLTools.NHibernateTools.RTLFieldAccessor,RTLTools.NHibernateTools" default-lazy="false" assembly="CronosDomain" namespace="CronosDomain">
   <class name="AbstractProgram" table="T_PROGRAM">
    <cache usage="read-write"/>
   
    <id name="Id" column="ID" type="Int32" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">S_PROGRAM_ID</param>
         </generator>
      </id>
    <discriminator column="CLASS_ID" type="string"/>

    <list name="GenreForProgramList" table="T_GENRE_FOR_PROGRAM" lazy="true">
      <key column="PROGRAM_ID"/>
      <index column="POSITION" type="integer"/>
      <many-to-many class="ProgramGenre" column="PROGRAM_GENRE_ID"/>     
    </list>
   
    <list name="ProgramProductionCountryList" table="T_PROGRAM_PRODUCTION_COUNTRY" lazy="true">
      <key column="PROGRAM_ID"/>
      <index column="POSITION" type="integer"/>
      <many-to-many class="Country" column="COUNTRY_ID"/>
    </list>
   
    <bag name="SlotList" inverse="true" lazy="true" >
      <key column="PROGRAM_ID" />
      <one-to-many class="AbstractSlot" />
    </bag>
   
    <property column="EXTERNAL_ID" type="String" name="ExternalId" length="50" />
    <property column="LOGICAL_DELETE_DATE_TIME" type="DateTime" name="LogicalDeleteDateTime" />
    <property column="NAME" type="String" name="Name" not-null="true" length="50" />
    <many-to-one column="PROGRAM_NATURE_ID" name="ProgramNature" class="ProgramNature" />
    <many-to-one column="PROGRAM_RATING_ID" name="ProgramRating" class="ProgramRating" />
    <property column="ORIGINAL_TITLE" type="String" name="OriginalTitle" length="100" />
    <property column="LOCAL_TITLE" type="String" name="LocalTitle" length="100" />
    <property column="THEORETICAL_DURATION_IN_FRAMES" type="RTLTools.NHibernateTools.TimeSpanAsFramesType, RTLTools.NHibernateTools" name="TheoreticalDuration" />
    <property column="START_PRODUCTION_YEAR" type="Int32" name="StartProductionYear" />
    <property column="END_PRODUCTION_YEAR" type="Int32" name="EndProductionYear" />
    <property column="IS_UNBROADCAST" type="Boolean" name="IsUnbroadcast" not-null="true" />

    <subclass name="Series" discriminator-value="1">
      <bag name="SeasonList" inverse="true" lazy="true" cascade="all">
        <key column="CONTAINING_SERIES_ID" />
        <one-to-many class="Season" />
      </bag>
    </subclass>

    <subclass name="Season" discriminator-value="2">
      <many-to-one column="CONTAINING_SERIES_ID" name="ContainingSeries" class="Series" />
      <property column="SEASON_NUMBER" type="Int32" name="SeasonNumber" />
      <bag name="EpisodeList" inverse="true" lazy="true" cascade="all">
        <key column="CONTAINING_SEASON_ID" />
        <one-to-many class="Episode" />
      </bag>
    </subclass>

    <subclass name="AbstractBroadcastableProgram">

      <bag name="ContractLineList" inverse="true" lazy="true" >
        <key column="PROGRAM_ID" />
        <one-to-many class="ContractLine" />
      </bag>
      <bag name="ProgramVersionList" inverse="true" lazy="true" cascade="all">
        <key column="CONTAINING_BC_PROGRAM_ID" />
        <one-to-many class="ProgramVersion" />
      </bag>
      <bag name="ContractLineWithRightList" inverse="true" lazy="true" >
        <key column="PROGRAM_ID" />
        <one-to-many class="ContractLineWithRight" />
      </bag>

      <subclass name="Episode" discriminator-value="3">
        <many-to-one column="CONTAINING_SEASON_ID" name="ContainingSeason" class="Season" />
        <property column="EPISODE_NUMBER" type="Int32" name="EpisodeNumber" />
        <property column="SEQUENCE_NUMBER" type="Int32" name="SequenceNumber" />
      </subclass>

      <subclass name="StandaloneProgram" discriminator-value="4"/>       
     
    </subclass>
   </class>
</hibernate-mapping>

SELECT *
  FROM (SELECT   this_.ID AS id45_2_, this_.external_id AS external3_45_2_,
                 this_.logical_delete_date_time AS logical4_45_2_,
                 this_.NAME AS name45_2_,
                 this_.program_nature_id AS program6_45_2_,
                 this_.program_rating_id AS program7_45_2_,
                 this_.original_title AS original8_45_2_,
                 this_.local_title AS local9_45_2_,
                 this_.theoretical_duration_in_frames AS theoret10_45_2_,
                 this_.start_production_year AS start11_45_2_,
                 this_.end_production_year AS end12_45_2_,
                 this_.is_unbroadcast AS is13_45_2_,
                 genreforpr4_.program_id AS program1___,
                 programgen1_.ID AS program2_,
                 genreforpr4_.POSITION AS position__,
                 programgen1_.ID AS id18_0_,
                 programgen1_.external_id AS external2_18_0_,
                 programgen1_.logical_delete_date_time AS logical3_18_0_,
                 programgen1_.NAME AS name18_0_,
                 programgen1_.description AS descript5_18_0_,
                 programpro6_.program_id AS program1___,
                 country2_.ID AS country2_,
                 programpro6_.POSITION AS position__,
                 country2_.ID AS id15_1_,
                 country2_.iso_code AS iso2_15_1_,
                 country2_.NAME AS name15_1_,
                 country2_.is_visible AS is4_15_1_
            FROM t_program this_,
                 t_genre_for_program genreforpr4_,
                 t_program_genre programgen1_,
                 t_program_production_country programpro6_,
                 t_country country2_
           WHERE this_.ID = genreforpr4_.program_id
             AND genreforpr4_.program_genre_id = programgen1_.ID
             AND this_.ID = programpro6_.program_id
             AND programpro6_.country_id = country2_.ID
             AND this_.class_id = '4'
             AND LOWER (this_.original_title) LIKE 'lord of war%'
             AND LOWER (this_.local_title) LIKE 'lord of war%'
             AND this_.start_production_year = '2005'
             AND programgen1_.ID = 326
             AND country2_.ID = 227
             AND this_.logical_delete_date_time IS NULL
        ORDER BY this_.NAME ASC)
WHERE ROWNUM <= '1000';