SELECT
    EvidenceTypeDef.evidenceTypeDefID,
    EvidenceTypeDef.evidenceTypeCode,
    EvidenceTypeDef.description,
    EvidenceTypeDef.evidenceTypeDefinition,
    EvidenceTypeDef.logicalName,
    EvidenceTypeDef.recordStatus,
    EvidenceTypeDef.securityGroupName,
   EvidenceTypeDef.versionNo,
   EvidenceTypeDef.maintenancePattern
  INTO
    :evidenceTypeDefID,
    :evidenceTypeCode,
    :description,
    :evidenceTypeDefinition,
    :logicalName,
    :recordStatus,
    :securityGroupName,
    :versionNo,
    :maintenancePattern
  FROM
    EvidenceTypeDef 
  WHERE EvidenceTypeDef.recordStatus = :recordStatus 
    AND :caseConfigurationID NOT IN 
    ( 
    SELECT
      CaseConfigEvidenceLink.caseConfigurationID 
    FROM
      CaseConfigEvidenceLink 
    WHERE CaseConfigEvidenceLink.evidenceTypeID = EvidenceTypeDef.evidenceTypeDefID 
    AND CaseConfigEvidenceLink.caseType = :caseType
    AND CaseConfigEvidenceLink.recordStatus = :linkRecordStatus
    )