Operation Details
Description: Returns Income Support Product Adoption Evidence details including the participant and adoption parent full name.
Sterotype: ns
SQL 
SELECT
  Adoption.evidenceID,
  Adoption.caseParticipantRoleID,
  Adoption.parCaseParticipantRoleID,
  Adoption.adoptionAssistAgreementType,
  Adoption.state,
  Adoption.adoptionFinalizedDate,
  Adoption.startDate,
  Adoption.endDate,
  Adoption.comments,
  Adoption.versionNo,
  crCasePart.concernRoleName,
  crParPart.concernRoleName,
  crCasePart.primaryAlternateID,
  crParPart.primaryAlternateID
INTO
  :evidenceID,
  :caseParticipantRoleID,
  :parCaseParticipantRoleID,
  :adoptionAssistAgreementType,
  :state,
  :adoptionFinalizedDate,
  :startDate,
  :endDate,
  :comments,
  :versionNo,
  :childName,
  :parentName,
  :alternateID,
  :parAlternateID 
FROM
  Adoption,
  CaseParticipantrole cpCasePart,
  CaseParticipantrole cpParPart,
  ConcernRole crCasePart,
  ConcernRole crParPart 
WHERE Adoption.evidenceID = :evidenceID 
  AND cpCasePart.caseParticipantRoleID = Adoption.caseParticipantRoleID 
  AND crCasePart.concernRoleID = cpCasePart.participantRoleID 
  AND cpParPart.caseParticipantRoleID = Adoption.parCaseParticipantRoleID 
  AND crParPart.concernRoleID = cpParPart.participantRoleID