Operation Details
Description: Method to read the relationship evidence based on the case evidence group code, the participant role Id and by relationship type code one or two.
Sterotype: nsmulti
SQL 
SELECT
  ConcernRoleRelationship.concernRoleID,
  ConcernRoleRelationship.concernRoleType,
  ConcernRoleRelationship.relConcernRoleID,
  ConcernRoleRelationship.relConcernRoleType,
  ConcernRoleRelationship.relationshipType,
  ISHholdRshipEvidence.nonParentCaretakerRelInd,
  ISHholdRshipEvidence.recipISHholdRshipEvidenceID,
  ConcernRoleRelationship.statusCode
INTO
  :participantRoleID,
  :participantRoleType,
  :relParticipantRoleID,
  :relParticipantRoleType,
  :relationshipType,
  :nonParentCaretakerRelInd,
  :recipISHholdRshipEvidenceID,
  :statusCode 
FROM
  ISHholdRshipEvidence,
  CaseEvidenceGroup,
  CaseEvidenceGroupLink,
  ConcernRoleRelationship 
WHERE CaseEvidenceGroup.caseEvidenceGroupID = :caseEvidenceGroupID 
  AND CaseEvidenceGroup.caseEvidenceGroupID = CaseEvidenceGroupLink.caseEvidenceGroupID 
  AND CaseEvidenceGroupLink.relatedID = ISHholdRshipEvidence.isHholdRshipEvidenceID 
  AND ISHholdRshipEvidence.participantRoleRshipID = ConcernRoleRelationship.concernRoleRelationshipID 
  AND 
  (
    ConcernRoleRelationship.relationshipType = :relationshipTypeCodeOne 
    OR ConcernRoleRelationship.relationshipType = :relationshipTypeCodeTwo 
  ) 
  AND ConcernRoleRelationship.concernRoleID = :participantRoleID 
  AND ConcernRoleRelationship.statusCode = :statusCode