Operation Details
Description: Retrieves the latest recent case action and case details for reference number, type and action type specified.
Sterotype: ns
SQL 
SELECT
  CaseHeader.caseReference,
  CaseHeader.caseID,
  CaseHeader.caseTypeCode,
  CaseHeader.concernRoleID,
  CaseHeader.startDate,
  CaseHeader.statusCode,
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleType,
  UserRecentAction.actionDateTime,
  OrgObjectLink.userName,
  OrgObjectLink.orgObjectType,
  UserRecentAction.userName
INTO
  :caseReference,
  :caseID,
  :caseTypeCode,
  :concernRoleID,
  :startDate,
  :statusCode,
  :concernRoleName,
  :concernRoleType,
  :actionDateTime,
  :ownerName,
  :ownerType,
  :approvedByName 
FROM
  UserRecentAction,
  ConcernRole,
  CaseHeader,
  OrgObjectLink 
WHERE UserRecentAction.referenceNo = :referenceNo 
  AND UserRecentAction.type = :type 
  AND UserRecentAction.actionType = :actionType 
  AND actionDateTime IN 
  ( 
  SELECT
    MAX (actionDateTime) 
  FROM
    UserRecentAction 
  WHERE UserRecentAction.referenceNo = :referenceNo 
    AND UserRecentAction.type = :type 
    AND UserRecentAction.actionType = :actionType 
  ) 
  AND CaseHeader.caseID = :referenceNo 
  AND ConcernRole.concernRoleID = CaseHeader.concernRoleID 
  AND CaseHeader.ownerOrgObjectLinkID = OrgObjectLink.orgObjectLinkID 
ORDER BY CaseHeader.startDate ASC