SELECT
  Compartment.providerConcernRoleID,
  COUNT(Place.placeID)
INTO
  :providerConcernRoleID,
  :numberOfRecords 
FROM
  Place,
  Compartment 
WHERE 
  (
    
    (
      :searchByProvider = '0' 
      OR Compartment.providerConcernRoleID IN 
      ( 
      SELECT
        Provider.providerConcernRoleID 
      FROM
        Provider 
      WHERE Provider.providerConcernRoleID = :providerConcernRoleID 
        AND Provider.recordStatus = :providerStatus 
      ) 
    ) 
    AND 
    (
      :searchByServiceOffering = '0' 
      OR Compartment.providerConcernRoleID IN 
      ( 
      SELECT
        Provider.providerConcernRoleID 
      FROM
        Provider,
        ProviderOffering,
        ServiceOffering 
      WHERE ServiceOffering.serviceOfferingID = :serviceOfferingID 
        AND ServiceOffering.recordStatus = :activeStatus 
        AND ServiceOffering.serviceOfferingID = ProviderOffering.serviceOfferingID 
        AND ProviderOffering.recordStatus = :providerOfferingStatus 
        AND ProviderOffering.startDate <= :searchDate 
        AND 
        (
          ProviderOffering.endDate >= :searchDate 
          OR ProviderOffering.endDate IS NULL 
        ) 
        AND ProviderOffering.providerConcernRoleID = Provider.providerConcernRoleID 
        AND Provider.recordStatus = :providerStatus 
      ) 
    ) 
    AND 
    (
      :searchByProviderType = '0' 
      OR Compartment.providerConcernRoleID IN 
      ( 
      SELECT
        Provider.providerConcernRoleID 
      FROM
        Provider,
        ProviderCategoryPeriod,
        ProviderType 
      WHERE ProviderType.type = :providerType 
        AND ProviderType.recordStatus = :activeStatus 
        AND ProviderType.providerCategoryID = ProviderCategoryPeriod.providerCategoryID 
        AND ProviderCategoryPeriod.recordStatus = :activeStatus 
        AND ProviderCategoryPeriod.startDate <= :searchDate 
        AND 
        (
          ProviderCategoryPeriod.endDate >= :searchDate 
          OR ProviderCategoryPeriod.endDate IS NULL 
        ) 
        AND ProviderCategoryPeriod.providerConcernRoleID = Provider.providerConcernRoleID 
        AND Provider.recordStatus = :providerStatus 
      ) 
    ) 
  ) 
  AND Compartment.recordStatus = :compartmentStatus 
  AND Compartment.compartmentID = Place.compartmentID 
  AND Place.recordStatus <> :cancelledStatus 
  AND Place.startDate <= :searchDate 
  AND 
  (
    Place.endDate >= :searchDate 
    OR Place.endDate IS NULL 
  ) 
  AND Place.placeID NOT IN 
  ( 
  SELECT
    Placement.placeID 
  FROM
    Placement 
  WHERE Placement.recordStatus = :activeStatus 
    AND Placement.startDate <= :endDateTime 
    AND 
    (
      Placement.endDate >= :startDateTime 
      OR Placement.endDate IS NULL 
    ) 
  ) 
  AND Place.placeID NOT IN 
  ( 
  SELECT
    Reservation.placeID 
  FROM
    Reservation 
  WHERE Reservation.status = :reservationStatus 
    AND Reservation.fromDate <= :endDateTime 
    AND 
    (
      Reservation.toDate >= :startDateTime 
      OR Reservation.toDate IS NULL 
    ) 
  ) 
  AND Place.placeID NOT IN 
  ( 
  SELECT
    OutOfUsePeriod.placeID 
  FROM
    OutOfUsePeriod 
  WHERE OutOfUsePeriod.startDate <= :searchDate 
    AND 
    (
      OutOfUsePeriod.endDate >= :searchDate 
      OR OutOfUsePeriod.endDate IS NULL 
    ) 
  ) 
GROUP
  BY Compartment.providerConcernRoleID 
ORDER BY Compartment.providerConcernRoleID