SELECT
  CaseNomineeProdDelPattern.caseNomineeProdDelPatternID,
  CaseNomineeProdDelPattern.fromDate,
  P.name,
  CaseNomineeProdDelPattern.statusCode
INTO
  :caseNomineeProdDelPatternID,
  :fromDate,
  :name,
  :statusCode 
FROM
  CaseNomineeProdDelPattern,
  ProductDeliveryPatternInfo P 
WHERE 
  (
    CaseNomineeProdDelPattern.caseNomineeID = :caseNomineeID 
  ) 
  AND 
  (
    CaseNomineeProdDelPattern.productDeliveryPatternID = P.productDeliveryPatternID 
  ) 
  AND 
  (
    P.recordStatus = :recordStatus 
  ) 
  AND 
  (
    P.fromDate = 
    ( 
    SELECT
      MAX(fromDate) 
    FROM
      ProductDeliveryPatternInfo 
    WHERE 
      (
        productDeliveryPatternID = CaseNomineeProdDelPattern.productDeliveryPatternID 
      ) 
      AND 
      (
        recordStatus = :recordStatus 
      ) 
      AND 
      (
        fromDate <= CaseNomineeProdDelPattern.fromDate 
      ) 
    ) 
  ) 
ORDER BY CaseNomineeProdDelPattern.fromDate DESC