SELECT
  evidenceType
INTO
  :typeCodeValue 
FROM
  EvidenceMetadata 
WHERE evidenceMetadataID IN 
  ( 
  SELECT
    evidenceMetadataID 
  FROM
    ProductEvidenceLink 
  WHERE productID = :productID 
  ) 
  AND evidenceType NOT IN 
  ( 
  SELECT
    evidenceType 
  FROM
    TemporalEvidenceApprovalCheck 
  WHERE approvalType = :approvalType 
  AND RECORDSTATUS = 'RST1'
  )