SELECT
  Reservation.reservationID,
  Reservation.placementID,
  Reservation.placeID,
  Reservation.providerOfferingID,
  Reservation.caseParticipantRoleID,
  Reservation.unitAmount,
  Reservation.unitAmountFixedIndicator,
  Reservation.fromDate,
  Reservation.toDate,
  Reservation.dateOfExpiry,
  Reservation.comments,
  Reservation.status,
  Reservation.versionNo
INTO
  :reservationID,
  :placementID,
  :placeID,
  :providerOfferingID,
  :caseParticipantRoleID,
  :unitAmount,
  :unitAmountFixedIndicator,
  :fromDate,
  :toDate,
  :dateOfExpiry,
  :comments,
  :status,
  :versionNo 
FROM
  ConcernRole,
  Reservation,
  CaseParticipantRole 
WHERE 
  (
    :searchByStatus = '0' 
    OR Reservation.status = :status 
  ) 
  AND 
  (
    :searchByName = '0' 
    OR upper(ConcernRole.concernRoleName) like :name 
  ) 
  AND Reservation.providerOfferingID IN 
  ( 
  SELECT
    providerOfferingID 
  FROM
    ProviderOffering,
    ServiceOffering 
  WHERE ProviderOffering.serviceOfferingID = ServiceOffering.serviceOfferingID 
    AND ServiceOffering.unitOfMeasure = :unitOfMeasure 
    AND ServiceOffering.recordStatus = :recordStatus 
    AND providerConcernRoleID = :providerConcernRoleID 
  ) 
  AND Reservation.caseParticipantRoleID = CaseParticipantRole.caseParticipantRoleID 
  AND CaseParticipantRole.participantRoleID = ConcernRole.concernRoleID 
ORDER BY Reservation.fromDate ASC