Operation Details
Description: Lists recently assigned cases currently owned by current user
Sterotype: nsmulti
SQL 
SELECT
  CaseHeader.caseReference,
  CaseHeader.caseID,
  CaseHeader.caseTypeCode,
  CaseHeader.concernRoleID,
  CaseHeader.startDate,
  CaseHeader.statusCode,
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleType,
  UserRecentAction.actionDateTime,
  UserRecentAction.userName,
  OrgObjectLink.orgObjectType,
  CASEPARTICIPANTROLE.typecode,
  CRCPR.CONCERNROLENAME as cprClients,
  cast(NULL as varchar(1)) AS cgMembers
INTO
  :caseReference,
  :caseID,
  :caseTypeCode,
  :concernRoleID,
  :startDate,
  :statusCode,
  :concernRoleName,
  :concernRoleType,
  :actionDateTime,
  :ownerName,
  :ownerType,
  :cprTypeOpt,
  :cprClientsOpt,
  :cgMembersOpt
FROM
  UserRecentAction,
  ConcernRole,
  CaseHeader,
  OrgObjectLink ,
  CASEPARTICIPANTROLE,
  CONCERNROLE CRCPR
WHERE UserRecentAction.userName = :userName 
  AND UserRecentAction.type = :type 
  AND UserRecentAction.actionType = :actionType 
  AND 
  (
    UserRecentAction.actionDateTime >= :actionDateTime 
    OR :actionDateTime IS NULL 
  ) 
  AND UserRecentAction.referenceNo = CaseHeader.caseID 
  AND ConcernRole.concernRoleID = CaseHeader.concernRoleID 
  AND CaseHeader.ownerOrgObjectLinkID = OrgObjectLink.orgObjectLinkID
  AND CASEPARTICIPANTROLE.CASEID= CASEHEADER.CASEID
  AND CASEPARTICIPANTROLE.RECORDSTATUS= :recordStatusOpt
  AND CASEPARTICIPANTROLE.PARTICIPANTROLEID=CRCPR.CONCERNROLEID
  UNION
  SELECT
  CaseHeader.caseReference,
  CaseHeader.caseID,
  CaseHeader.caseTypeCode,
  CaseHeader.concernRoleID,
  CaseHeader.startDate,
  CaseHeader.statusCode,
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleType,
  UserRecentAction.actionDateTime,
  UserRecentAction.userName,
  OrgObjectLink.orgObjectType,
  cast(NULL as varchar(1)) as typecode,
  cast(NULL as varchar(1)) as cprClients,
  CRCG.CONCERNROLENAME AS cgMembers
FROM
  UserRecentAction,
  ConcernRole,
  CaseHeader,
  OrgObjectLink,
  CASEGROUPS,
  CONCERNROLE CRCG
WHERE UserRecentAction.userName = :userName 
  AND UserRecentAction.type = :type 
  AND UserRecentAction.actionType = :actionType 
  AND 
  (
    UserRecentAction.actionDateTime >= :actionDateTime 
    OR :actionDateTime IS NULL 
  ) 
  AND UserRecentAction.referenceNo = CaseHeader.caseID 
  AND ConcernRole.concernRoleID = CaseHeader.concernRoleID 
  AND CaseHeader.ownerOrgObjectLinkID = OrgObjectLink.orgObjectLinkID 
  AND CASEGROUPS.CASEID= CASEHEADER.CASEID
  AND CASEGROUPS.GROUPCODE= 'CG3'
  AND CRCG.CONCERNROLEID= CASEGROUPS.CONCERNROLEID
  
ORDER BY actionDateTime ASC