Operation Details
Description: Reads CaseNominee information for an Objective on a case by effectiveDate.
Sterotype: ns
SQL 
SELECT
  CaseNomineeObjective.caseNomineeObjectiveID,
  CaseNomineeObjective.rulesObjectiveID,
  CaseNomineeObjective.caseNomineeID,
  ConcernRole.concernRoleID,
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleType
INTO
  :caseNomineeObjectiveID,
  :rulesObjectiveID,
  :caseNomineeID,
  :concernRoleID,
  :concernRoleName,
  :concernRoleType 
FROM
  CaseNomineeObjective,
  CaseNominee,
  ConcernRole,
  CaseParticipantRole 
WHERE CaseNomineeObjective.rulesObjectiveID = :rulesObjectiveID 
  AND CaseNomineeObjective.statusCode = :statusCode 
  AND CaseNomineeObjective.caseNomineeID = CaseNominee.caseNomineeID 
  AND CaseParticipantRole.caseParticipantRoleID = CaseNominee.caseParticipantRoleID 
  AND ConcernRole.concernRoleID = CaseParticipantRole.participantRoleID 
  AND CaseParticipantRole.caseID = :caseID 
  AND 
  (
    CaseNomineeObjective.fromDate <= :effectiveDate 
  ) 
  AND 
  (
    
    (
      CaseNomineeObjective.toDate >= :effectiveDate 
    ) 
    OR 
    (
      CaseNomineeObjective.toDate IS NULL 
    ) 
  )