Operation Details
Description: Join across CaseHeader and ConcernRole. Returns details about all cases of the specified type that are part of an Integrated Case or Screening Case specified .
Sterotype: nsmulti
SQL 
SELECT
 CaseHeader.caseID,
 CaseHeader.caseReference,
 CaseHeader.caseTypeCode,
 CaseHeader.startDate,
 CaseHeader.statusCode,
 ConcernRole.concernRoleID,
 ConcernRole.concernRoleName,
 ConcernRole.concernRoleType,
 CRCG.concernRoleName
INTO
   :caseID,
   :caseReference,
   :caseTypeCode,
   :startDate,
   :statusCode,
   :concernRoleID,
   :concernRoleName,
   :concernRoleType,
   :iCClientsOpt  
FROM
ConcernRole,
CaseHeader
LEFT JOIN
CaseGroups
ON
CaseHeader.caseID = CaseGroups.CaseID AND CASEGROUPS.GROUPCODE= 'CG3'
LEFT JOIN
ConcernRole CRCG
ON CRCG.CONCERNROLEID= CASEGROUPS.CONCERNROLEID
WHERE CaseHeader.integratedCaseID = :caseID 
   AND 
   (
     CaseHeader.caseTypeCode = :caseTypeCode 
     OR :caseTypeCode IS NULL 
   ) 
   AND ConcernRole.concernRoleID = CaseHeader.concernRoleID