SELECT
  evidenceType
INTO
  :typeCodeValue 
FROM
  EvidenceMetadata 
WHERE evidenceMetadataID IN 
  ( 
  SELECT
    evidenceMetadataID 
  FROM
    ProductEvidenceLink 
  WHERE productID = :productID 
    AND recordStatus = :recordStatus 
  )