SELECT 
     DISTINCT Provider.providerConcernRoleID, 
     Provider.name, 
     ConcernRole.primaryAlternateID, 
     ConcernRole.primaryAddressID 

 INTO  
     :providerConcernRoleID, 
     :providerName, 
     :primaryAlternateID, 
     :primaryAddressID 

FROM 
     Provider, 
     ConcernRole, 
     ProviderOffering ,
     AddressElement addressLine1,
     AddressElement city  
 
 WHERE 
     
Provider.providerConcernRoleID = ConcernRole.concernRoleID AND     
 Provider.providerConcernRoleID = ProviderOffering.providerconcernroleID   AND 
     ProviderOffering.serviceOfferingID = :serviceID AND      
     ProviderOffering.recordStatus = :poStatus 
  AND
  (
    :searchByName = '0' OR
    Provider.nameUpper like :name
  ) 
  AND 
  (
      :searchByStreet1 = '0' OR
    (addressLine1.upperElementValue like :street1  AND 
     addressLine1.addressID = ConcernRole.primaryAddressID AND     
     addressLine1.elementType = :addressTypeCode)  
  )
  AND 
  (
    :searchByCity = '0' OR   
     (city.upperElementValue like :city  AND 
     city.addressID = ConcernRole.primaryAddressID AND 
     city.elementType = :cityTypeCode)   
  )
 AND
 (
     :searchByFromDate = '0'  OR
     ProviderOffering.startDate <= :fromDate
  )  
  AND 
  (
     :searchByToDate = '0' OR 
     (ProviderOffering.endDate IS NULL OR ProviderOffering.endDate >= :toDate) 
)
ORDER BY  Provider.name