Operation Details
Description: Reads the amount and the date (effective date) of the last payment made on the case the given ID refers to. Where more than one payment was issued on this date, the total amount of these payments is returned.
Sterotype: ns
SQL 
SELECT 
SUM (PaymentInstruction.amount),
MAX(FinancialInstruction.effectiveDate)

INTO
:amount,
:effectiveDate

FROM 
PaymentInstruction, 
FinancialInstruction
WHERE 
PaymentInstruction.finInstructionID = FinancialInstruction.finInstructionID AND
PaymentInstruction.finInstructionID IN (
 SELECT
  DISTINCT InstructionLineItem.finInstructionID 
 FROM 
  InstructionLineItem 
 WHERE
  InstructionLineItem.caseID=:caseID AND
  FinancialInstruction.effectiveDate IN (
    SELECT MAX(FinancialInstruction.effectiveDate) 
    FROM FinancialInstruction, InstructionLineItem
    WHERE InstructionLineItem.caseID=:caseID AND
    InstructionLineItem.finInstructionID = FinancialInstruction.finInstructionID
  )
)