Operation Details
Description: Returns a list of investigation cases and role details for a concern role id and not matching provided case id.
Sterotype: nsmulti
SQL 
SELECT DISTINCT
  RelatedInvestigation.caseID,
  RelatedInvestigation.caseReference,
  RelatedInvestigation.investigationType
INTO
  :caseID,
  :caseReference,
  :investigationType
FROM (
SELECT
  CaseHeader.caseID,
  CaseHeader.caseReference,
  InvestigationDelivery.investigationType,
  CaseParticipantRole.typeCode participantRoleType,
  '1'  investigationParticipantInd
FROM
  CaseHeader,
  InvestigationDelivery,
  CaseParticipantRole
WHERE (CaseHeader.caseID <> :caseID OR :caseIDIsNull <> '0')
  AND CaseHeader.caseTypeCode = :caseType
  AND CaseHeader.statusCode <> :caseStatusCodeCancelled
  AND CaseHeader.statusCode <> :caseStatusCodeClosed
  AND CaseHeader.caseID = InvestigationDelivery.caseID
  AND CaseHeader.caseID = CaseParticipantRole.caseID
  AND CaseParticipantRole.participantRoleID = :concernRoleID
  AND CaseParticipantRole.recordStatus = :recordStatus
  AND (CaseParticipantRole.typeCode = :participantTypePrimary OR CaseParticipantRole.typeCode = :participantTypeMember)
UNION
SELECT DISTINCT
  CaseHeader.caseID,
  CaseHeader.caseReference,
  InvestigationDelivery.investigationType,
  AllegationRole.roleType participantRoleType,
  '0' investigationParticipantInd
FROM
  CaseHeader,
  InvestigationDelivery,
  Allegation,
  AllegationRole,
  CaseParticipantRole
WHERE (CaseHeader.caseID <> :caseID OR :caseIDIsNull <> '0')
  AND CaseHeader.caseTypeCode = :caseType
  AND CaseHeader.statusCode <> :caseStatusCodeCancelled
  AND CaseHeader.statusCode <> :caseStatusCodeClosed
  AND CaseHeader.caseID = InvestigationDelivery.caseID
  AND Allegation.caseID = CaseHeader.caseID
  AND Allegation.recordStatus = :recordStatus
  AND Allegation.allegationID = AllegationRole.allegationID
  AND AllegationRole.caseParticipantRoleID = CaseParticipantRole.caseParticipantRoleID
  AND CaseParticipantRole.typeCode <> :participantTypeAllegationReporter
  AND CaseParticipantRole.participantRoleID = :concernRoleID) RelatedInvestigation