Operation Details
Description: Reads the details for the Appellants and Respondent of the appeal including the case where the appellant is the Organisation.
Sterotype: nsmulti
SQL 
SELECT  
ConcernRole.concernRoleName,  
ConcernRole.primaryAlternateID,  
ConcernRole.concernRoleID,  
ConcernRole.concernRoleType,  
CaseParticipantRole.typeCode,  
Address.addressData,  
ConcernRole.primaryPhoneNumberID,  
EmailAddress.emailAddress,  
isPrimaryClient.primaryClientInd    

INTO  
:concernRoleName,  
:primaryAlternateID,  
:concernRoleID,  
:concernRoleType,  
:caseParticipantTypeCode,  
:addressData,  
:phoneNumberID,  
:emailAddress,  
:primaryClientInd  

FROM     
CaseParticipantRole      
LEFT OUTER JOIN ConcernRole ON ( CaseParticipantRole.participantRoleID = ConcernRole.concernRoleID)      
LEFT OUTER JOIN Address ON ( ConcernRole.primaryAddressID = Address.addressID)      
LEFT OUTER JOIN EmailAddress ON ( ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID) 
LEFT OUTER JOIN ( SELECT '1' AS primaryClientInd, CaseParticipantRole.participantRoleID          
FROM              
CaseHeader, CaseParticipantRole         
WHERE              
CaseParticipantRole.participantRoleID = CaseHeader.concernRoleID AND
CaseHeader.caseID = CaseParticipantRole.caseID AND 
CaseParticipantRole.caseID = :caseID )
 isPrimaryClient ON ( isPrimaryClient.participantRoleID= ConcernRole.concernRoleID)  
 WHERE  CaseParticipantRole.caseID = :caseID      AND (CaseParticipantRole.typeCode = :respondentCaseParticipantTypeCode 
 OR CaseParticipantRole.typeCode = :appellantCaseParticipantTypeCode)  
 
 UNION ALL  

 SELECT  ConcernRole.concernRoleName,      
 ConcernRole.primaryAlternateID,      
 ConcernRole.concernRoleID,      
 ConcernRole.concernRoleType,      
 CaseParticipantRole.typeCode,      
 Address.addressData,     
 ConcernRole.primaryPhoneNumberID,      
 EmailAddress.emailAddress,      
 isPrimaryClient.primaryClientInd  FROM     
 CaseParticipantRole     
 LEFT OUTER JOIN ConcernRole ON  ( CaseParticipantRole.participantRoleID = ConcernRole.concernRoleID)      
 LEFT OUTER JOIN Address ON ( ConcernRole.primaryAddressID = Address.addressID)      
 LEFT OUTER JOIN EmailAddress ON ( ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID)      
 LEFT OUTER JOIN ( SELECT '1' AS primaryClientInd, CaseParticipantRole.participantRoleID  
 FROM  CaseHeader, CaseParticipantRole          
 WHERE   CaseParticipantRole.participantRoleID = CaseHeader.concernRoleID AND             
 CaseHeader.caseID = CaseParticipantRole.caseID AND              
 CaseParticipantRole.caseID = :caseID      ) 
 isPrimaryClient ON (          isPrimaryClient.participantRoleID= ConcernRole.concernRoleID)  
 WHERE      CaseParticipantRole.caseID IN 
 (  SELECT AppealRelationship.caseID          
 FROM  AppealRelationship 
 WHERE  AppealRelationship.appealCaseID = :caseID      )     
 AND CaseParticipantRole.typeCode = :productProviderCaseParticipantTypeCode  
 
 UNION ALL  
 
 SELECT      ConcernRole.concernRoleName, 
 ConcernRole.primaryAlternateID,
 ConcernRole.concernRoleID,
 ConcernRole.concernRoleType,
 CaseParticipantRole.typeCode, 
 Address.addressData,      
 ConcernRole.primaryPhoneNumberID, 
 EmailAddress.emailAddress, 
 isPrimaryClient.primaryClientInd  
 FROM  CaseParticipantRole      
 LEFT OUTER JOIN ConcernRole ON ( CaseParticipantRole.participantRoleID = ConcernRole.concernRoleID)      
 LEFT OUTER JOIN Address ON ( ConcernRole.primaryAddressID = Address.addressID) 
 LEFT OUTER JOIN EmailAddress ON ( ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID)      
 LEFT OUTER JOIN ( SELECT  '1' AS primaryClientInd, CaseParticipantRole.participantRoleID          
 FROM  CaseHeader, CaseParticipantRole          
 WHERE              
 CaseParticipantRole.participantRoleID = CaseHeader.concernRoleID AND        
 CaseHeader.caseID = CaseParticipantRole.caseID AND             
 CaseParticipantRole.caseID = :caseID      ) isPrimaryClient ON (       
 isPrimaryClient.participantRoleID= ConcernRole.concernRoleID)  WHERE      
 CaseParticipantRole.caseID IN (          
 SELECT  IssueDelivery.relatedCaseID         
 FROM   IssueDelivery WHERE             
 IssueDelivery.caseID IN (  
 SELECT                   
 AppealRelationship.caseID 
 FROM                     
 AppealRelationship                 
 WHERE                      
 AppealRelationship.appealCaseID = :caseID ) )      
 AND CaseParticipantRole.typeCode = :productProviderCaseParticipantTypeCode