Operation Details
Description: Searchs for the deductions (ILI type) that relate to an underpayment through a related overpayment case (using the case relationship reason code). The sum of the deductions found is returned.
Sterotype: ns
SQL 
SELECT 
SUM(InstructionLineItem.amount)

INTO
:amount

FROM 
InstructionLineItem
    LEFT OUTER JOIN 
    CaseRelationship
    ON (InstructionLineItem.caseID = CaseRelationship.caseID)

WHERE
InstructionLineItem.instructionLineItemType = :instructionLineItemType
AND
InstructionLineItem.caseID = (

  SELECT CaseRelationship.caseID
  FROM CaseRelationship
  WHERE
  CaseRelationship.reasonCode = :reasonCode 
AND
  CaseRelationship.relatedCaseID IN ( 
    SELECT
    CaseRelationship.relatedCaseID
     FROM
    InstructionLineItem
      LEFT OUTER JOIN 
      CaseRelationship
      ON (InstructionLineItem.caseID = CaseRelationship.caseID)
    WHERE
    InstructionLineItem.caseID = :caseID
  )
)