Skip to content

Instantly share code, notes, and snippets.

@apeterson-BFI
Created March 27, 2017 13:31
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save apeterson-BFI/c48af519082f6aab63d991c177885987 to your computer and use it in GitHub Desktop.
Save apeterson-BFI/c48af519082f6aab63d991c177885987 to your computer and use it in GitHub Desktop.
VoidCheckProcedure SP errors on Close cursor
ALTER PROCEDURE VoidCheckProcedure
@checkID int
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
/* update status of Orig Check to V */
UPDATE [Check] SET [Status] = 'V' WHERE Check_ID = @checkID;
/* update PayableChecks on Check to inactive */
UPDATE PayableCheck SET Active = 0
WHERE CheckID = @checkID;
/* update payables on Check to status O */
UPDATE p SET [Status] = 'O'
FROM Payable p
INNER JOIN PayableCheck pc ON pc.PayableID = p.PayableID
WHERE pc.CheckID = @checkID;
/* update errors on Check to status O */
UPDATE Error SET [Status] = 'O'
FROM Error
INNER JOIN Payable on Error.ErrorID = Payable.ErrorID
INNER JOIN PayableCheck on Payable.PayableID = PayableCheck.PayableID
WHERE PayableCheck.CheckID = @checkID
/* 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
/* Insert offset Check */
INSERT INTO [Check]
(Check_Number, Check_Date, Check_Cleared, Grower_ID, [Status], Amount)
SELECT c.Check_Number, GETDATE(), 'P', c.Grower_ID, 'V', -c.Amount
FROM [Check] c
WHERE c.Check_ID = @checkID
/* Insert offset Errors */
DECLARE @growerID varchar(20)
DECLARE @amount decimal(19,2)
DECLARE error_cursor CURSOR FOR
SELECT GrowerID, Amount FROM Error WHERE CheckID = @checkID;
OPEN error_cursor;
FETCH NEXT FROM error_cursor INTO @growerID, @amount;
WHILE @@FETCH_STATUS = 0
BEGIN
/* Insert offset Errors */
INSERT INTO Error
(CheckID, GrowerID, Amount, [Status], ErrorDate)
VALUES
(@checkID, @growerID, -@amount, 'V', GETDATE())
/* Insert offset Payables */
INSERT INTO Payable
(GrowerID, Amount, ErrorID, [Status])
VALUES
(@growerID, -@amount, SCOPE_IDENTITY(), 'V')
FETCH NEXT FROM error_cursor INTO @growerID, @amount
END
CLOSE error_cursor;
DEALLOCATE error_cursor;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment