SELECT
  conditionID,
  name,
  type,
  recordStatus
INTO
  :conditionID,
  :conditionName,
  :conditionType,
  :status 
FROM
  Condition 
WHERE 
  (
    
    (
      endDate IS NULL 
      OR endDate >= current_date 
    ) 
    AND 
    (
      recordStatus = :recordStatus 
    ) 
    AND 
    (
      conditionID in 
      ( 
      SELECT
        conditionID 
      FROM
        ICDCodeConditionLink,
        ICDCode 
      WHERE 
        (
          
          (
            ICDCode.ICDCodeID = ICDCodeConditionLink.iCDCodeID 
          ) 
          AND 
          (
            :searchKeyICDCodeInd = '0' 
            OR upper(ICDCode.ICDCode) like :iCDCode 
          ) 
          AND 
          (
            :searchKeyICDTextInd = '0' 
            OR upper(ICDCode.ICDText) like :iCDText 
          ) 
        ) 
      ) 
    ) 
  ) 
ORDER BY name