Operation Details
Description: Returns a CaseNomineeObjective record by caseID, rulesObjectiveID, effectiveDate, and statusCode.
Sterotype: ns
SQL 
SELECT
  CaseNomineeObjective.caseNomineeObjectiveID,
  CaseNomineeObjective.caseNomineeID,
  CaseNomineeObjective.rulesObjectiveID,
  CaseNomineeObjective.fromDate,
  CaseNomineeObjective.toDate,
  CaseNomineeObjective.statusCode
INTO
  :caseNomineeObjectiveID,
  :caseNomineeID,
  :rulesObjectiveID,
  :fromDate,
  :toDate,
  :statusCode 
FROM
  CaseNomineeObjective,
  CaseNominee,
  CaseParticipantRole 
WHERE CaseNomineeObjective.rulesObjectiveID = :rulesObjectiveID 
  AND CaseNominee.caseNomineeID = CaseNomineeObjective.caseNomineeID 
  AND CaseParticipantRole.caseParticipantRoleID = CaseNominee.caseParticipantRoleID 
  AND CaseParticipantRole.caseID = :caseID 
  AND CaseNomineeObjective.statusCode = :statusCode 
  AND 
  (
    
    (
      
      (
        CaseNomineeObjective.fromDate <= :effectiveDate 
      ) 
      AND 
      (
        CaseNomineeObjective.toDate >= :effectiveDate 
      ) 
    ) 
    OR 
    (
      
      (
        CaseNomineeObjective.fromDate <= :effectiveDate 
      ) 
      AND 
      (
        CaseNomineeObjective.toDate IS NULL 
      ) 
    ) 
  )