Operation Details
Description: Returns a list of Verifications and all associated data, based on the given Verification Linked ID and status. Because the returned list is an unstructured list, this operation should be used in conjunction with VerificationAdaptor.convertToStructuredList() to generate a structured list.
Sterotype: nsmulti
SQL 
SELECT 
v.verificationID, v.verificationStatus, v.dueDate, vr.mandatory, vr.clientSupplied, 
ed.participantID, 
vc.name, 
ed.statusCode, ed.evidencedescriptorid, ed.relatedid, vdi.name, vdi.evidencetype, vdi.dataitem, vdi.verifiableDataItemID,
vw.startDate, vw.endDate, 
xxx.name, xxx.type, 
xxx.addedByUser, xxx.dateAdded, xxx.receivedFrom, xxx.verificationItemProvidedId, xxx.verificationItemUtilizationID
INTO
:verificationID, :verificationStatus, :verificationDueDate, :verificationRequirementMandatory, :verificationRequirementClientSupplied,
:evidenceDescriptorParticipantID, 
:verificationCategoryName,
:evidenceStatus, :evidenceDescriptorID, :relatedEvidenceID, :verifiableDataItemName, :evidenceType, :verifiableDataItem, :verifiableDataItemID,
:waiverStartDate, :waiverEndDate,
:verificationItemName, :verificationItemType, 
:verificationItemProvidedAddedByUser, :verificationItemProvidedDateAdded, :verificationItemProvidedReceivedFrom, :verificationItemProvidedID, :verificationItemProvidedItemUtilizationID
FROM Verification v
INNER JOIN VerificationRequirement vr on v.verificationRequirementID = vr.verificationRequirementID AND vr.recordStatus = :recordStatusCode 
INNER JOIN VerifiableDataItem vdi on vr.verifiableDataItemID = vdi.verifiableDataItemID AND vdi.recordStatus = :recordStatusCode 
INNER JOIN VerificationCategory vc on vdi.verificationCategoryID = vc.verificationCategoryID AND vc.recordStatus = :recordStatusCode
LEFT JOIN (SELECT vip.VDIEDLinkID, vip.recordStatus, vip.addedByUser, vip.receivedFrom, vip.dateAdded, vip.verificationItemProvidedId, viu.verificationItemUtilizationID, vi.name, vi.type FROM VerificationItemProvided vip INNER JOIN VerificationItemUtilization viu on vip.verificationItemUtilizationID = viu.verificationItemUtilizationID INNER JOIN VerificationItem vi on viu.verificationItemID = vi.verificationItemID) xxx
on v.VDIEDLinkID = xxx.VDIEDLinkID AND xxx.recordStatus = :recordStatusCode
INNER JOIN VDIEDLink vel on v.VDIEDLinkID = vel.VDIEDLinkID
INNER JOIN EvidenceDescriptor ed on vel.evidenceDescriptorID = ed.evidenceDescriptorID
LEFT JOIN VerificationWaiver vw on v.verificationID = vw.verificationID AND vw.recordStatus = :recordStatusCode
WHERE v.verificationLinkedID = :verificationLinkedID