Operation Details
Description: Searches for any NPCR relationships that a household member has with other members of the household.
Sterotype: nsmulti
SQL 
SELECT
  ConcernRoleRelationship.relConcernRoleID
INTO
  :relParticipantRoleID 
FROM
  CaseEvidenceGroup,
  CaseEvidenceGroupLink,
  HouseholdRelationship,
  ConcernRoleRelationship 
WHERE CaseEvidenceGroup.caseEvidenceTreeID = :caseEvidenceTreeID 
  AND CaseEvidenceGroup.evidenceGroupNameCode = :caseEvidenceGroupNameCode 
  AND CaseEvidenceGroupLink.caseEvidenceGroupID = CaseEvidenceGroup.caseEvidenceGroupID 
  AND CaseEvidenceGroupLink.evidenceType = :caseEvidenceType 
  AND HouseholdRelationship.evidenceID = CaseEvidenceGroupLink.relatedID 
  AND HouseholdRelationship.nonParentCareRelInd = :npcrInd 
  AND ConcernRoleRelationship.concernRoleRelationshipID = HouseholdRelationship.concernRoleRelationshipID 
  AND ConcernRoleRelationship.concernRoleID = :participantRoleID 
  AND ConcernRoleRelationship.statusCode = :relationshipStatusCode