SELECT
  ExternalPartyOffice.concernRoleID,
  ExternalParty.primaryAlternateID,
  ExternalParty.name,
  ExternalParty.type,
  addressLine1.elementValue,
  ExternalPartyOffice.name,
  ExternalPartyOffice.externalPartyOfficeID
INTO
  :officeConcernRoleID,
  :externalPartyReferenceNumber,
  :externalPartyName,
  :externalPartyType,
  :officeAddressLine1,
  :officeName,
  :officeID 
FROM
  ExternalParty,
  ExternalPartyOffice,
  ConcernRole,
  AddressElement city,
  AddressElement addressLine1 
WHERE ConcernRole.concernRoleID = ExternalParty.concernRoleID 
  AND ExternalPartyOffice.concernRoleID = ExternalParty.concernRoleID 
  AND addressLine1.addressID = ConcernRole.primaryAddressID 
  AND addressLine1.elementType = :addressLine1Type 
  AND city.addressID = ConcernRole.primaryAddressID 
  AND city.elementType = :cityType 
  AND 
  (
    :searchByAddressLine1 = '0' 
    OR addressLine1.upperElementValue like :officeAddressLine1 
  ) 
  AND 
  (
    :searchByCity = '0' 
    OR city.upperElementValue like :officeCity 
  ) 
  AND 
  (
    :searchByName = '0' 
    OR UPPER(ExternalParty.name) like :externalPartyName 
  ) 
  AND 
  (
    :searchByReferenceNumber = '0' 
    OR ExternalParty.primaryAlternateID = :externalPartyReferenceNumber 
  ) 
  AND 
  (
    :searchByType = '0' 
    OR ExternalParty.type = :type 
  ) 
  AND 
  (
    :searchByOfficeType = '0' 
    OR ExternalPartyOffice.type = :officeType 
  ) 
  AND 
  (
    :searchByOfficeName = '0' 
    OR UPPER(ExternalPartyOffice.name) like :officeName 
  )