SELECT
  CaseHeader.caseID,
  CaseHeader.integratedCaseID,
  icHeader.caseReference,
  CaseHeader.caseReference,
  CaseHeader.startDate,
  CaseHeader.endDate,
  ProductDelivery.productID,
  Product.name,
  ProductDelivery.productType,
  CaseGroups.concernRoleID
INTO
  :caseID,
  :integratedCaseID,
  :icCaseReference,
  :caseReference,
  :startDate,
  :endDate,
  :productID,
  :productName,
  :productType,
  :concernRoleID 
FROM
  CaseHeader,
  CaseHeader icHeader,
  ProductDelivery,
  Product,
  CaseGroups 
WHERE icHeader.caseID = CaseHeader.integratedCaseID 
  AND ProductDelivery.caseID = CaseHeader.caseID 
  AND Product.productID = ProductDelivery.productID 
  AND CaseGroups.caseID = CaseHeader.caseID 
  AND CaseHeader.statusCode <> :caseStatusCode 
  AND CaseGroups.groupCode = :caseGroupTypeCode 
  AND CaseGroups.startDate <= :caseGroupDate 
  AND 
  (
    CaseGroups.endDate >= :caseGroupDate 
    OR CaseGroups.endDate IS NULL 
  ) 
  AND ProductDelivery.caseID IN 
  ( 
  SELECT
    DISTINCT CaseParticipantRole.caseID 
  FROM
    CaseParticipantRole,
    ProductDelivery 
  WHERE CaseParticipantRole.caseID = ProductDelivery.caseID 
    AND CaseParticipantRole.participantRoleID IN 
    ( 
    SELECT
      CaseParticipantRole.participantRoleID 
    FROM
      CaseParticipantRole 
    WHERE CaseParticipantRole.caseID = :caseID 
      AND 
      (
        CaseParticipantRole.typeCode = :caseParticipantRoleType1 
        OR CaseParticipantRole.typeCode = :caseParticipantRoleType2 
      ) 
    ) 
  ) 
ORDER BY CaseHeader.caseReference