SELECT
  ProviderEnquiry.providerEnquiryID,
  ProviderEnquiry.referenceNumber,
  ProviderEnquiry.name,
  ProviderEnquiry.nameUpper,
  ProviderEnquiry.homeAddressID,
  ProviderEnquiry.workAddressID,
  ProviderEnquiry.homePhoneNumberID,
  ProviderEnquiry.workPhoneNumberID,
  ProviderEnquiry.mobilePhoneNumberID,
  ProviderEnquiry.providerCategoryPeriodID,
  ProviderEnquiry.additionalName,
  ProviderEnquiry.preferredSession,
  ProviderEnquiry.reasonForEnquiry,
  ProviderEnquiry.confirmedMeetingDetails,
  ProviderEnquiry.attendedMeeting,
  ProviderEnquiry.obtainedApplicationForm,
  ProviderEnquiry.scheduledMeeting,
  ProviderEnquiry.noOfChildren,
  ProviderEnquiry.availabilityForContact,
  ProviderEnquiry.additionalInformation,
  ProviderEnquiry.enquiryDate,
  ProviderEnquiry.endDate,
  ProviderEnquiry.ownerName,
  ProviderEnquiry.recordStatus,
  ProviderEnquiry.preferredLanguage,
  ProviderEnquiry.preferredCommunication,
  ProviderEnquiry.versionNo
INTO
  :providerEnquiryID,
  :referenceNumber,
  :name,
  :nameUpper,
  :homeAddressID,
  :workAddressID,
  :homePhoneNumberID,
  :workPhoneNumberID,
  :mobilePhoneNumberID,
  :providerCategoryPeriodID,
  :additionalName,
  :preferredSession,
  :reasonForEnquiry,
  :confirmedMeetingDetails,
  :attendedMeeting,
  :obtainedApplicationForm,
  :scheduledMeeting,
  :noOfChildren,
  :availabilityForContact,
  :additionalInformation,
  :enquiryDate,
  :endDate,
  :ownerName,
  :recordStatus,
  :preferredLanguage,
  :preferredCommunication,
  :versionNo 
FROM
  ProviderEnquiry 
  
  LEFT OUTER JOIN
  
  AddressElement addressline1 
  ON ProviderEnquiry.homeAddressID = addressline1.addressID 
  AND addressline1.elementType = :addressLine1Type 
  
  LEFT OUTER JOIN
  
  AddressElement city 
  ON ProviderEnquiry.homeAddressID = city.addressID 
  AND city.elementType = :cityTypeCode 
WHERE 
  (
    :searchByName = '0' 
    OR upper(name) like :name 
  ) 
  AND 
  (
    :searchByReferenceNumber = '0' 
    OR referenceNumber = :referenceNumber 
  ) 
  AND ProviderEnquiry.recordStatus = :enquiryStatus 
  AND 
  (
    :searchByStreet1 = '0' 
    OR upper(addressLine1.elementValue) like :street1 
  ) 
  AND 
  (
    :searchByCity = '0' 
    OR upper(city.elementValue) like :city 
  ) 
ORDER BY ProviderEnquiry.name