Operation Details
Description: Returns the delivery pattern for the primary case nominee on a case.
Sterotype: ns
SQL 
SELECT
  CaseNomineeProdDelPattern.caseNomineeProdDelPatternID,
  CaseNomineeProdDelPattern.fromDate,
  CaseNomineeProdDelPattern.toDate,
  CaseNomineeProdDelPattern.productDeliveryPatternID,
  CaseNomineeProdDelPattern.caseNomineeID,
  CaseNomineeProdDelPattern.statusCode
INTO
  :caseNomineeProdDelPatternID,
  :fromDate,
  :toDate,
  :productDeliveryPatternID,
  :caseNomineeID,
  :statusCode 
FROM
  CaseNomineeProdDelPattern 
WHERE CaseNomineeProdDelPattern.caseNomineeID in 
  ( 
  SELECT
    caseNomineeID 
  FROM
    CaseNominee,
    CaseParticipantRole 
  WHERE CaseParticipantRole.caseID = :caseID 
    AND CaseNominee.caseParticipantRoleID = CaseParticipantRole.caseParticipantRoleID 
    AND CaseNominee.defaultNomInd = :defaultNomInd 
  ) 
  AND CaseNomineeProdDelPattern.statusCode = :statusCode 
  AND 
  (
    CaseNomineeProdDelPattern.fromDate <= :effectiveDate 
  ) 
  AND 
  (
    
    (
      CaseNomineeProdDelPattern.toDate >= :effectiveDate 
    ) 
    OR 
    (
      CaseNomineeProdDelPattern.toDate IS NULL 
    ) 
  )