Database SQL Operation: InstructionLineItem.readLastPaymentAndEffectiveDate
| Description: | Reads the amount and the date (effective date) of the last payment made and processed on the case the given ID refers to. Where more than one payment was issued on this date, the total amount of the payments (excluding cancelled payment) is returned. |
| Sterotype: | ns |
SELECT
SUM( PAYMENTINSTRUCTION.AMOUNT ) ,
MAX( FINANCIALINSTRUCTION.EFFECTIVEDATE )
INTO
:amount,
:effectiveDate
FROM
FINANCIALINSTRUCTION INNER JOIN PAYMENTINSTRUCTION ON PAYMENTINSTRUCTION.FININSTRUCTIONID = FINANCIALINSTRUCTION.FININSTRUCTIONID
WHERE
FINANCIALINSTRUCTION.EFFECTIVEDATE
IN
( SELECT MAX( FINANCIALINSTRUCTION.EFFECTIVEDATE )
FROM
FINANCIALINSTRUCTION ,
INSTRUCTIONLINEITEM
WHERE
INSTRUCTIONLINEITEM.CASEID= :caseID
AND INSTRUCTIONLINEITEM.STATUSCODE= :statusCode
AND INSTRUCTIONLINEITEM.FININSTRUCTIONID= FINANCIALINSTRUCTION.FININSTRUCTIONID )
AND FINANCIALINSTRUCTION.finInstructionID
IN
( SELECT DISTINCT INSTRUCTIONLINEITEM.FININSTRUCTIONID
FROM
INSTRUCTIONLINEITEM
WHERE
INSTRUCTIONLINEITEM.CASEID= :caseID
AND INSTRUCTIONLINEITEM.STATUSCODE= :statusCode )
|
