SELECT
  ProviderOffering.providerConcernRoleID,
  COUNT(Place.placeID)
INTO
  :providerConcernRoleID,
  :numberOfRecords 
FROM
  Placement,
  ProviderOffering,
  Place 
WHERE Placement.recordStatus = :activeStatus 
  AND Placement.startDate <= :endDateTime 
  AND 
  (
    Placement.endDate >= :startDateTime 
    OR Placement.endDate IS NULL 
  ) 
  AND Placement.providerOfferingID = ProviderOffering.providerOfferingID 
  AND 
  (
    
    (
      :searchByProvider = '0' 
      OR ProviderOffering.providerConcernRoleID IN 
      ( 
      SELECT
        Provider.providerConcernRoleID 
      FROM
        Provider 
      WHERE Provider.providerConcernRoleID = :providerConcernRoleID 
        AND Provider.recordStatus = :providerStatus 
      ) 
    ) 
    AND 
    (
      :searchByServiceOffering = '0' 
      OR ProviderOffering.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 ProviderOffering.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 Placement.placeID = Place.placeID 
GROUP
  BY ProviderOffering.providerConcernRoleID 
ORDER BY ProviderOffering.providerConcernRoleID