Operation Details
Description: Searches and returns all the investigations where the provider is part of. Provider can play either a direct role like primary client , contact log attendee or can play an indirect role like victim party etc.
Sterotype: nsmulti
SQL 
SELECT DISTINCT

  RelatedInvestigation.caseID,
  RelatedInvestigation.caseReference,
  RelatedInvestigation.investigationType,
  RelatedInvestigation.participantRoleType,
  RelatedInvestigation.startDate,
  RelatedInvestigation.statusCode,
  RelatedInvestigation.participantRoleID,
  RelatedInvestigation.caseParticipantRoleID,
  RelatedInvestigation.resolutionStatus
  
  INTO  
  :caseID,
  :caseReference,
  :investigationType,
  :participantRoleType,
  :startDate,
  :statusCode,
  :participantRoleID,
  :caseParticipantRoleID,
  :resolutionStatus

FROM (
SELECT
  CaseHeader.caseID,
  CaseHeader.caseReference,
  InvestigationDelivery.investigationType,
  InvestigationDelivery.resolutionStatus,
  CaseParticipantRole.typeCode participantRoleType,
  CaseHeader.startdate,
  CaseHeader.statusCode,
  CaseParticipantRole.participantRoleID,
  CaseParticipantRole.caseParticipantRoleID
  

FROM
  CaseHeader,
  InvestigationDelivery,
  CaseParticipantRole
WHERE 
  CaseHeader.caseTypeCode = :caseTypeCode
  AND CaseHeader.caseID = InvestigationDelivery.caseID
  AND CaseHeader.caseID = CaseParticipantRole.caseID
  AND CaseParticipantRole.participantRoleID = :providerConcernRoleID
  AND CaseParticipantRole.recordStatus = :recordStatus
  

UNION

SELECT DISTINCT
  CaseHeader.caseID,
  CaseHeader.caseReference,
  InvestigationDelivery.investigationType,
  InvestigationDelivery.resolutionStatus,
  AllegationRole.roleType participantRoleType,
  CaseHeader.startdate,
  CaseHeader.statusCode,
  CaseParticipantRole.participantRoleID,
  CaseParticipantRole.caseParticipantRoleID

FROM

  CaseHeader,
  InvestigationDelivery,
  Allegation,
  AllegationRole,
  CaseParticipantRole

WHERE 

  CaseHeader.caseTypeCode = :caseTypeCode
  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 <> :typeCode
  AND CaseParticipantRole.participantRoleID = :providerConcernRoleID) RelatedInvestigation