SELECT
  DISTINCT(Provider.providerConcernRoleID),
  ConcernRole.primaryAlternateID,
  ConcernRole.concernRoleName,
  ProviderCategoryPeriod.category,
  Provider.recordStatus,
  ProviderType.type,
  ProviderOffering.startDate,
  ProviderOffering.endDate
INTO
  :providerConcernRoleID,
  :primaryAlternateID,
  :concernRoleName,
  :category,
  :recordStatus,
  :providerType,
  :startDate,
  :endDate 
FROM
  Provider,
  ConcernRole,
  ProviderCategoryPeriod,
  ProviderType,
  ProviderOffering,
  Address 
WHERE ProviderOffering.providerConcernRoleID = Provider.providerConcernRoleID 
  AND ProviderOffering.serviceOfferingID = :serviceOfferingID 
  AND ConcernRole.concernRoleID = Provider.providerConcernRoleID 
  AND Provider.providerConcernRoleID = ProviderCategoryPeriod.providerConcernRoleID 
  AND ProviderCategoryPeriod.providerCategoryID = ProviderType.providerCategoryID 
  AND ConcernRole.primaryAddressID =Address.addressID 
  AND Provider.recordStatus ='PS3' 
  AND 
  (
    ProviderOffering.startDate <= CURRENT_DATE 
    AND 
    (
      ProviderOffering.endDate >= CURRENT_DATE 
      OR ProviderOffering.endDate IS NULL 
    ) 
  ) 
  AND ProviderOffering.recordStatus ='POS2' 
  AND 
  (
    :searchByName = '0' 
    OR upper(ConcernRole.concernRoleName) like :providerName 
  ) 
  AND 
  (
    :searchByReferenceNumber = '0' 
    OR ConcernRole.primaryAlternateID = :referenceNumber 
  ) 
  AND 
  (
    :searchByType = '0' 
    OR ProviderType.type = :providerCategoryType 
  ) 
  AND 
  (
    :searchByCategory = '0' 
    OR ProviderCategoryPeriod.category = :category 
  ) 
  AND 
  (
    :searchByAddress = '0' 
    OR upper(Address.addressData) like :address 
  ) 
ORDER BY Provider.providerConcernRoleID