Operation Details
Description: Reads service plan delivery details for all members of the integrated case.
Sterotype: nsmulti
SQL 
SELECT
  CaseHeader.caseID,
  CaseHeader.caseReference,
  ServicePlan.servicePlanType,
  CaseHeader.statusCode,
  CaseHeader.registrationDate,
  CaseParticipantRole.caseParticipantRoleID,
  ConcernRole.concernRoleName
INTO
  :caseID,
  :caseReference,
  :servicePlanType,
  :statusCode,
  :registrationDate,
  :caseParticipantRoleID,
  :memberName 
FROM
  CaseHeader,
  ConcernRole,
  CaseParticipantRole,
  ServicePlanDelivery,
  ServicePlan 
WHERE CaseHeader.integratedCaseID = :integratedCaseID 
  AND 
  (
    CaseParticipantRole.participantRoleID = CaseHeader.concernRoleID 
    AND CaseParticipantRole.caseID = CaseHeader.caseID 
    AND CaseParticipantRole.typeCode = :typeCode 
  ) 
  AND ConcernRole.concernRoleID = CaseHeader.concernRoleID 
  AND ServicePlanDelivery.caseID = CaseHeader.caseID 
  AND ServicePlan.servicePlanID = ServicePlanDelivery.servicePlanID