<query name="searchPlicsPs">
      <![CDATA[
      select new com.rtefrance.declic.server.demand.projection.PlicModificationProjection(
         ps, d.number, d.type, d.demandDate, d.hasPersistedReserve, d.clientComment, d.hasClauseGrandFroid,
         d.client.userDisplayname, d.client.demandSociete, reserveCodeList)
      from  Demand d
         left join d.plicModificationPsList as ps
         left join ps.reserveCodeList as reserveCodeList
      where ps.state <> :draftStateExcluded
         and ( false=:teststate or  ps.state = :state )
         and ( false=:teststates or (ps.state in (:states )))
         and ( false=:testregion or ps.region = :region )
         and ( false=:testsociete or d.client.demandSociete.societeIdSap = :societe )
         and ( false=:testapplicationMonth or to_char(ps.applicateDate,'MM-yyyy') = :applicationMonth )
         and ps <> null
       ]]>
   </query>

select
        plicmodifi1_.PLIC_MODIFICATION_PS_ID as col_0_0_,
        demand0_.DEMAND_NUMBER as col_1_0_,
        demand0_.TYPE as col_2_0_,
        demand0_.DEMAND_DATE as col_3_0_,
        demand0_.HAS_PERSISTED_RESERVE as col_4_0_,
        demand0_.CLIENT_COMMENT as col_5_0_,
        demand0_.HAS_CLAUSE_GRAND_FROID as col_6_0_,
        demand0_.USER_DISPLAY_NAME as col_7_0_,
        demand0_.SOCIETE_ID_SAP as col_8_0_,
        . as col_9_0_
    from
        DEMAND demand0_,
        PLIC_MODIFICATION_PS plicmodifi1_,
        RESERVE_CODE_PS reservecod3_,
        DEMAND_SOCIETE demandsoci2_
    where
        demand0_.DEMAND_NUMBER=plicmodifi1_.DEMAND_NUMBER(+)
        and plicmodifi1_.PLIC_MODIFICATION_PS_ID=reservecod3_.PLIC_MODIFICATION_PS_ID
        and demand0_.SOCIETE_ID_SAP=demandsoci2_.SOCIETE_ID_SAP
        and plicmodifi1_.STATE<>?
        and (
            0=?
            or plicmodifi1_.STATE=?
        )
        and (
            0=?
            or plicmodifi1_.STATE in (
                ?
            )
        )
        and (
            0=?
            or plicmodifi1_.REGION=?
        )
        and (
            0=?
            or demand0_.SOCIETE_ID_SAP=?
        )
        and (
            0=?
            or to_char(plicmodifi1_.APPLICATE_DATE, 'MM-yyyy')=?
        )
        and (
            plicmodifi1_.PLIC_MODIFICATION_PS_ID is not null
        )