Operation Details
Description: This method returns a list of all the evidence forms for a product which have not as yet been recorded with this case or its integrated case.
Sterotype: nsmulti
SQL 
SELECT
  ProductEvidenceFormLink.evidenceFormID,
  EvidenceForm.evidenceNameCode,
  EvidenceForm.caseEvidenceType,
  ProductEvidenceFormLink.startDate,
  ProductEvidenceFormLink.endDate
INTO
  :evidenceFormID,
  :evidenceNameCode,
  :caseEvidenceType,
  :startDate,
  :endDate 
FROM
  ProductEvidenceFormLink,
  EvidenceForm 
WHERE ProductEvidenceFormLink.productID = :productID 
  AND ProductEvidenceFormLink.recordStatus = :recordStatus 
  AND EvidenceForm.evidenceFormID = ProductEvidenceFormLink.evidenceFormID 
  AND NOT EXISTS 
  ( 
  SELECT
    * 
  FROM
    CaseEvidence,
    CaseEvidenceType,
    CaseHeader 
  WHERE CaseHeader.caseID = :caseID 
    AND 
    (
      CaseEvidence.caseID = CaseHeader.integratedCaseID 
      OR CaseEvidence.caseID = CaseHeader.caseID 
    ) 
    AND CaseEvidenceType.evidenceID = CaseEvidence.evidenceID 
    AND EvidenceForm.evidenceNameCode = CaseEvidenceType.evidenceFormName 
  )