Update receipt status on Void Check - GMS2

updatereceipt.sql

	/* update Receipts on Check to O, X, or V */
	UPDATE r1
	SET [Status] =
		CASE 
			WHEN MaxSeq.MaxSeqNo % 2 = 1 THEN			-- ends in a void
				CASE r1.SequenceNo
					WHEN MaxSeq.MaxSeqNo THEN 'V'
					WHEN MaxSeq.MaxSeqNo - 1 THEN 'V'
					ELSE 'X'
				END
			WHEN MaxSeq.MaxSeqNo % 2 = 0 THEN
				CASE r1.SequenceNo
					WHEN MaxSeq.MaxSeqNo THEN 'O'
					ELSE 'X'
				END
		END
	FROM Receipt r1
	INNER JOIN Payable on r1.TransactionKey = Payable.ReceiptTKey
	INNER JOIN PayableCheck ON Payable.PayableID = PayableCheck.PayableID
	INNER JOIN
	(
		SELECT r2.Receipt_ID, MAX(r2.SequenceNo) as MaxSeqNo
		FROM Receipt r2
		GROUP BY Receipt_ID
	) MaxSeq ON r1.Receipt_ID = MaxSeq.Receipt_ID
	WHERE PayableCheck.CheckID = @checkID