SELECT
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleID,
  ConcernRoleAddress.addressID,
  CaseNomineeProdDelPattern.productDeliveryPatternID,
  ConcernRole.primaryAlternateID
INTO
  :concernRoleName,
  :concernRoleID,
  :addressID,
  :productDeliveryPatternID,
  :alternateID 
FROM
  ConcernRole,
  CaseNominee,
  CaseParticipantRole,
  CaseNomineeDestination,
  CaseNomineeProdDelPattern,
  ConcernRoleAddress 
WHERE 
  (
    CaseNominee.caseNomineeID = :caseNomineeID 
    AND CaseParticipantRole.caseParticipantRoleID = CaseNominee.caseParticipantRoleID 
    AND ConcernRole.concernRoleID = CaseParticipantRole.participantRoleID 
  ) 
  AND 
  (
    CaseNomineeDestination.destinationType = :destinationType 
    AND CaseNomineeDestination.caseNomineeID = :caseNomineeID 
    AND CaseNomineeDestination.statusCode = :statusCode 
  ) 
  AND ConcernRoleAddress.concernRoleAddressID = CaseNomineeDestination.destinationID 
  AND CaseNomineeProdDelPattern.caseNomineeID = :caseNomineeID 
  AND CaseNomineeProdDelPattern.statusCode = :statusCode 
  AND 
  (
    CaseNomineeProdDelPattern.fromDate <= :effectiveDate 
  ) 
  AND 
  (
    
    (
      CaseNomineeProdDelPattern.toDate >= :effectiveDate 
    ) 
    OR 
    (
      CaseNomineeProdDelPattern.toDate IS NULL 
    ) 
  )