Operation Details
Description: Lists recently approved cases currently owned by current user or current user organization object.
Sterotype: nsmulti
SQL 
SELECT 
  CaseHeader.caseReference,
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleType,
  CaseHeader.caseTypeCode,
  CaseHeader.statusCode, 
  CaseHeader.caseID,
  CaseHeader.concernRoleID,
  UserRecentAction.actionDateTime,
  UserRecentAction.userName,
  CASEPARTICIPANTROLE.typecode,
  CRCPR.CONCERNROLENAME as cprClients,
  NULL AS cgMembers  
INTO
  :caseReference, 
  :concernRoleName,
  :concernRoleType,
  :caseTypeCode,
  :statusCode, 
  :caseID, 
  :concernRoleID,
  :approvedDateTime, 
  :approvedByUserName,
  :cprTypeOpt,
  :cprClientsOpt,
  :cgMembersOpt
FROM CaseHeader, ConcernRole, UserRecentAction, OrgObjectLink,  CASEPARTICIPANTROLE,
  CONCERNROLE CRCPR
WHERE CaseHeader.concernRoleID = ConcernRole.concernRoleID 
  AND CaseHeader.caseID = UserRecentAction.referenceNo 
  AND UserRecentAction.referenceNo = CaseHeader.caseID 
  AND UserRecentAction.type = :userRecentActionType 
  AND UserRecentAction.actionDateTime >= :actionDateTime 
  AND UserRecentAction.actionType = :userRecentActionActionType 
  AND OrgObjectLink.orgObjectLinkID = CaseHeader.ownerOrgObjectLinkID 
  AND OrgObjectLink.orgObjectType = :orgObjectType
  AND (OrgObjectLink.userName = :userName OR :userName IS NULL)
  AND (OrgObjectLink.orgObjectReference = :orgObjectReference OR :orgObjectReferenceIsNull <>'0')
  AND CASEPARTICIPANTROLE.CASEID= CASEHEADER.CASEID
  AND CASEPARTICIPANTROLE.RECORDSTATUS= :recordStatus
  AND CASEPARTICIPANTROLE.PARTICIPANTROLEID=CRCPR.CONCERNROLEID
UNION
SELECT 
  CaseHeader.caseReference,
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleType,
  CaseHeader.caseTypeCode,
  CaseHeader.statusCode, 
  CaseHeader.caseID,
  CaseHeader.concernRoleID,
  UserRecentAction.actionDateTime,
  UserRecentAction.userName,
  NULL AS TYPECODE, 
  NULL AS CPRCLIENTS, 
  CRCG.CONCERNROLENAME AS CGMEMBERS 
FROM CaseHeader, ConcernRole, UserRecentAction, OrgObjectLink, CASEGROUPS , 
CONCERNROLE CRCG
WHERE CaseHeader.concernRoleID = ConcernRole.concernRoleID 
  AND CaseHeader.caseID = UserRecentAction.referenceNo 
  AND UserRecentAction.referenceNo = CaseHeader.caseID 
  AND UserRecentAction.type = :userRecentActionType 
  AND UserRecentAction.actionDateTime >= :actionDateTime 
  AND UserRecentAction.actionType = :userRecentActionActionType 
  AND OrgObjectLink.orgObjectLinkID = CaseHeader.ownerOrgObjectLinkID 
  AND OrgObjectLink.orgObjectType = :orgObjectType
  AND (OrgObjectLink.userName = :userName OR :userName IS NULL)
  AND (OrgObjectLink.orgObjectReference = :orgObjectReference OR :orgObjectReferenceIsNull <>'0')
  AND CASEGROUPS.CASEID= CASEHEADER.CASEID 
  AND CASEGROUPS.GROUPCODE= 'CG3' 
  AND CRCG.CONCERNROLEID= CASEGROUPS.CONCERNROLEID