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.versionNo,
  Provider.enrollmentDateTime,
  Provider.endDateTime
INTO
  :providerConcernRoleID,
  :providerEnquiryID,
  :physicalCapacity,
  :paymentFrequency,
  :methodOfPayment,
  :currencyType,
  :reservationGracePeriod,
  :overrideMDRInd,
  :preferredSEMethod,
  :areasSvdInfoTxtID,
  :clientInfoTextID,
  :recordStatus,
  :versionNo,
  :enrollmentDateTime,
  :endDateTime
FROM
  Provider,
  ConcernRole,
  ProviderCategoryPeriod CategoryPeriod,
  ProviderCategoryPeriod PrimaryCategory,
  ProviderType 
WHERE 
  (
    :searchByName = '0' 
    OR upper(ConcernRole.concernRoleName) like :name 
  ) 
  AND 
  (
    :searchByReferenceNumber = '0' 
    OR ConcernRole.primaryAlternateID = :referenceNumber 
  ) 
  AND ConcernRole.concernRoleID = Provider.providerConcernRoleID 
  AND Provider.providerConcernRoleID = CategoryPeriod.providerConcernRoleID 
  AND CategoryPeriod.providerConcernRoleID = PrimaryCategory.providerConcernRoleID 
  AND CategoryPeriod.providerCategoryID = ProviderType.providerCategoryID 
  AND PrimaryCategory.primaryInd = :primaryCategoryInd 
  AND 
  (
    :searchByCategory = '0' 
    OR CategoryPeriod.category = :category 
  ) 
  AND 
  (
    :searchByType = '0' 
    OR ProviderType.type = :providerCategoryType 
  ) 
ORDER BY ConcernRole.primaryAlternateID