Created
March 27, 2017 13:31
-
-
Save apeterson-BFI/c48af519082f6aab63d991c177885987 to your computer and use it in GitHub Desktop.
VoidCheckProcedure SP errors on Close cursor
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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