Operation Details
Description: Returns a list of active integrated cases having common participants between integrated case and given<br /> investigation case.
Sterotype: nsmulti
SQL 
SELECT 
DISTINCT
CASEHEADER.CASEID, 
CASEHEADER.CASEREFERENCE,
CONCERNROLE.CONCERNROLENAME,
USERS.FULLNAME,
CASEHEADER.STARTDATE
INTO
:caseID,
:caseReference,
:primaryClient,
:caseWorker,
:startDate
FROM CASEPARTICIPANTROLE, CASEHEADER, CONCERNROLE, USERS, ORGOBJECTLINK
WHERE CASEHEADER.CASEID = CASEPARTICIPANTROLE.CASEID
AND CASEPARTICIPANTROLE.PARTICIPANTROLEID = CONCERNROLE.CONCERNROLEID
AND CONCERNROLE.CONCERNROLEID IN(SELECT CONCERNROLE.CONCERNROLEID FROM 
INVESTIGATIONDELIVERY, CASEPARTICIPANTROLE, CONCERNROLE 
WHERE INVESTIGATIONDELIVERY.CASEID = CASEPARTICIPANTROLE.CASEID AND CASEPARTICIPANTROLE.PARTICIPANTROLEID = CONCERNROLE.CONCERNROLEID
AND INVESTIGATIONDELIVERY.CASEID = :caseID
AND NOT CASEPARTICIPANTROLE.TYPECODE = :participantRoleType)
AND CASEHEADER.STATUSCODE = :caseStatus
AND CASEHEADER.OWNERORGOBJECTLINKID = ORGOBJECTLINK.ORGOBJECTLINKID
AND ORGOBJECTLINK.USERNAME = USERS.USERNAME
AND CASEHEADER.INTEGRATEDCASETYPE=:integratedCaseType