SELECT
  Provider.providerConcernRoleID,
  Provider.providerEnquiryID,
  Provider.physicalCapacity,
  Provider.paymentFrequency,
  Provider.methodOfPayment,
  Provider.currencyType,
  Provider.reservationGracePeriod,
  Provider.overrideMDRInd,
  Provider.preferredSEMethod,
  Provider.areasSvdInfoTxtID,
  Provider.clientInfoTextID,
  Provider.recordStatus,
  Provider.name,
  Provider.nameUpper,
  Provider.versionNo,
  Provider.enrollmentDateTime,
  Provider.endDateTime
INTO
  :providerConcernRoleID,
  :providerEnquiryID,
  :physicalCapacity,
  :paymentFrequency,
  :methodOfPayment,
  :currencyType,
  :reservationGracePeriod,
  :overrideMDRInd,
  :preferredSEMethod,
  :areasSvdInfoTxtID,
  :clientInfoTextID,
  :recordStatus,
  :name,
  :nameUpper,
  :versionNo,
 :enrollmentDateTime,
  :endDateTime
FROM
  Provider,
  ConcernRole,
  AddressElement city,
  AddressElement addressLine1 
WHERE 
  (
    :searchByName = '0' 
    OR upper(ConcernRole.concernRoleName) like :name 
  ) 
  AND 
  (
    :searchByReferenceNumber = '0' 
    OR ConcernRole.primaryAlternateID = :referenceNumber 
  ) 
  AND ConcernRole.concernRoleID = Provider.providerConcernRoleID 
  AND addressLine1.addressID = ConcernRole.primaryAddressID 
  AND addressLine1.elementType = :addressTypeCode 
  AND city.addressID = ConcernRole.primaryAddressID 
  AND city.elementType = :cityTypeCode 
  AND 
  (
    :searchByStreet1 = '0' 
    OR upper(addressLine1.elementValue) like :street1 
  ) 
  AND 
  (
    :searchByCity = '0' 
    OR upper(city.elementValue) like :city 
  ) 
ORDER BY ConcernRole.primaryAlternateID