Reversal v2
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Asamco BV - Alex & Olaf
-- Create date: 21-03-2024
-- Description: Reverses a Journal type of tx based on audit number.
-- =============================================
CREATE or ALTER PROCEDURE _as_GL_ReverseGLJournal
@AuditNumber nvarchar(100),
@UseOriginalDate bit = 1,
@CustomReversalDate date = null
AS
BEGIN
/*Usage:
--ORIGINAL DATE:
EXEC _as_GL_ReverseGLJournal '6277.0001'
SELECT * FROM POSTGL WHERE CAUDITNUMBER = '6277.0001'
--CUSTOM DATE:
EXEC _as_GL_ReverseGLJournal '6277.0001', 0, '2023-12-01'
*/
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/*Create type if doesnt exist.*/
IF TYPE_ID(N'GLTransactionBatch_v2') IS NULL
BEGIN
CREATE TYPE [dbo].[GLTransactionBatch_v2] AS TABLE(
[TxDate] [datetime] NULL,
[EvoId] [varchar](10) NULL,
[AccountLink] [bigint] NULL,
[TrCodeID] [int] NULL,
[Debit] [float] NULL,
[Credit] [float] NULL,
[CurrencyID] [int] NULL,
[fExchangeRate] [float] NULL,
[ForeignDebit] [float] NULL,
[ForeignCredit] [float] NULL,
[Description] [varchar](100) NULL,
[Reference] [varchar](50) NULL,
[cReference2] [varchar](50) NULL,
[Order_No] [varchar](50) NULL,
[ExtOrderNum] [varchar](50) NULL,
[ProjectID] [bigint] NULL,
[UserName] [varchar](50) NULL,
[DrCrAccount] [bigint] NULL,
[Module] char(2) default 'GL'/*GL,AP,AR*/,
[ModuleAccountId] bigint,
[BranchId] int not null default 0,
OldTxId bigint null
)
END
/*ONLY SUPPORTING JOURNALS CURRENTLY!*/
IF NOT EXISTS (SELECT * FROM PostGL P WHERE P.Id = 'JL' AND P.cAuditNumber = @AuditNumber)
BEGIN
DECLARE @MSG nvarchar(1000) = CONCAT('Audit No ', @AuditNumber,' is not a journal transaction. Aborting...')
RAISERROR(@MSG,17,1);
RETURN -1
END
DECLARE @tblTxbatch GLTransactionBatch_v2
--SELECT * FROM PostGL P WHERE Description LIKE '%SOFORT%'
INSERT INTO @tblTxbatch (TxDate, EvoId, AccountLink, TrCodeID, Debit, Credit, CurrencyID, fExchangeRate
, ForeignDebit, ForeignCredit, Description, Reference, cReference2
, Order_No, ExtOrderNum, ProjectID, UserName, DrCrAccount
, Module, ModuleAccountId, BranchId, OldTxId)
--VALUES
(SELECT TxDate = CASE WHEN @UseOriginalDate = 1 THEN p.TxDate ELSE @CustomReversalDate END
,EvoId = p.Id, AccountLink, TrCodeID, Debit = Credit, Credit = Debit, iCurrencyID, fExchangeRate
, ForeignDebit = fForeignCredit, ForeignCredit = fForeignDebit , p.Description, Reference, cReference2
, Order_No, ExtOrderNum, p.Project, UserName, DrCrAccount
, Module = 'GL', ModuleAccountId = 0, BranchId = p.iTxBranchID, OldTxId = p.AutoIdx
FROM PostGL p
WHERE p.cAuditNumber = @AuditNumber
)
DECLARE @newAuditNumber nvarchar(100) = ''
EXEC [_as_PostGLBatch_Simple_v2] @txTable = @tblTxBatch, @AuditNumber = @newAuditNumber OUTPUT ;
--SELECT RESULTS AND PRIOR JOURNAL:
SELECT
NewOld = CASE WHEN p.cAuditNumber = @AuditNumber THEN 'Old Tx'
WHEN p.cAuditNumber = @newAuditNumber THEN 'Reverse Tx'
ELSE '<<UNKNOWN>>' END
,* FROM PostGL p WHERE p.cAuditNumber IN (@newAuditNumber , @AuditNumber)
END
GO
-- =============================================
-- Author: Asamco BV, Alex
-- Create date: 24/04/2023
-- Description: Post GL Batch - Simplified - with module account posting
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[_as_PostGLBatch_Simple_v2]
@txTable GLTransactionBatch_v2 READONLY
,@AuditNumber varchar(100) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
/*DECLARES:*/
DECLARE @msg varchar(1000),@PeriodID int,@TxDate datetime
DECLARE @tblResult TABLE (OldTxId bigint, NewTxId bigint, NewARId bigint, NewAPId bigint, cAuditNumber varchar(100))
/*VALIDATIONS*/
--Check nr of rows in the txTable
IF (SELECT COUNT(*) FROM @txTable) = 0
BEGIN
select @msg ='No rows found in batch.'
GOTO ErrorSection
END
--Check if there is only 1 date.
IF (SELECT COUNT(*) FROM (SELECT TxDate FROM @txTable GROUP BY TxDate) n) > 1
BEGIN
select @msg ='Different dates found in batch.'
GOTO ErrorSection
END
--Check if total is 0 per period (update; check that all dates are the same!)
IF NOT ROUND((SELECT SUM(Debit)-SUM(Credit) FROM @txTable),4) = 0
BEGIN
select @msg ='Total Debit less Credit of batch is not 0'
GOTO ErrorSection
END
--Check if all accounts are active and existing
IF EXISTS(SELECT * FROM @txTable WHERE COALESCE(AccountLink, 0) NOT IN (SELECT AccountLink FROM Accounts WHERE Accounts.ActiveAccount = 1 AND AccountLevel <> 1))
BEGIN
select @msg ='Invalid AccountLinks found (inactive, or non-existent).'
select @msg = CONCAT(@msg, CAST(accountlink as varchar(50))) from @txTable where COALESCE(AccountLink, 0) NOT IN (SELECT AccountLink FROM Accounts WHERE Accounts.ActiveAccount = 1 AND AccountLevel <> 1)
GOTO ErrorSection
END
--Check if all periods are not blocked
SELECT @TxDate = (SELECT TOP 1 TxDate FROM @txTable)
SELECT @PeriodID = (Select Max(idPeriod) + 1 from _etblPeriod where dPeriodDate < @TxDate)
IF NOT EXISTS(SELECT * FROM _etblPeriod P WHERE P.idPeriod = @PeriodID AND P.bBlocked = 0)
BEGIN
select @msg ='Invalid period (blocked or not found).'
GOTO ErrorSection
END
--Check if module account AR - Exists, and is in the correct branch.
IF EXISTS (SELECT * FROM @txTable t LEFT JOIN Client C on t.ModuleAccountId = C.DCLink AND t.Module = 'AR' WHERE C.DCLink IS NULL AND t.Module = 'AR' OR (COALESCE(C.Client_iBranchID,0) > 0 AND t.BranchId != C.Client_iBranchID))
BEGIN
select @msg = 'Module is AR, but client does not exist or is of the incorrect branch.'
GOTO ErrorSection
END
--Check if module account AP - Exists, and is in the correct branch.
IF EXISTS (SELECT * FROM @txTable t LEFT JOIN Vendor V on t.ModuleAccountId = V.DCLink AND t.Module = 'AP' WHERE V.DCLink IS NULL AND t.Module = 'AP' OR (COALESCE(V.Vendor_iBranchID,0) > 0 AND t.BranchId != V.Vendor_iBranchID))
BEGIN
select @msg = 'Module is AP, but client does not exist or is of the incorrect branch.'
GOTO ErrorSection
END
/*NEW SQL TRANSACTION*/
-- BEGIN TRAN glBatch
/*Get Audit Number*/
IF @AuditNumber = ''
BEGIN
-- Get the audit number
DECLARE @AuditTemp varchar(50)
,@AuditNo varchar(50)
,@TxBranchID int
,@IsBranch bit
SELECT @TxBranchID = 0, @IsBranch = 0
EXEC @AuditTemp = _bspNextAuditNo;
select @AuditNo = CASE
WHEN @IsBranch = 1 THEN Cast(@TxBranchID as varchar(20)) + '.' + CAST(@AuditTemp as varchar) + '.0001'
ELSE CAST(@AuditTemp as varchar) + '.0001' END; --'GET NEXT AUDIT NO!!!!' --Check SP _bspNextAuditNo
PRINT @AuditNo
SET @AuditNumber = @AuditNo
END
ELSE
BEGIN
SELECT @AuditNo = @AuditNumber;
END
/*CURSOR, to loop through TX, and Post them*/
--cursor declares;
DECLARE @RC int, @AutoIdx bigint
DECLARE @Outstanding FLOAT, @ForeignOutstanding float, @MainAccLink bigint, @ARAccountLink bigint, @APAccountLink bigint, @LinkAccLink bigint, @AutoIdx_AR bigint, @AutoIdx_AP bigint
DECLARE @TxDateCursor datetime
,@EvoId varchar(10)
,@AccountLink bigint
,@TrCodeID int
,@Debit float
,@Credit float
,@CurrencyID int
,@fExchangeRate float
,@ForeignDebit float
,@ForeignCredit float
,@Description varchar(100)
,@Reference varchar(50)
,@cReference2 varchar(50)
,@Order_No varchar(50)
,@ExtOrderNum varchar(50)
,@ProjectID bigint
,@UserName varchar(50)
,@DrCrAccount bigint
,@Module char(2)
,@ModuleAccountId bigint
,@BranchId int
,@OldTxId bigint
--end of cursor declares ,@Module,@ModuleAccountId,@BranchId,@OldTxId
DECLARE MyCursor CURSOR
FORWARD_ONLY
FOR
SELECT
t.TxDate
,t.EvoId
,t.AccountLink
,t.TrCodeID
,t.Debit
,t.Credit
,t.CurrencyID
,t.fExchangeRate
,t.ForeignDebit
,t.ForeignCredit
,t.[Description]
,t.Reference
,t.cReference2
,t.Order_No
,t.ExtOrderNum
,t.ProjectID
,t.UserName
,t.DrCrAccount
,t.Module
,t.ModuleAccountId
,t.BranchId
,t.OldTxId
FROM @txTable t
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @TxDateCursor ,@EvoId ,@AccountLink ,@TrCodeID ,@Debit ,@Credit ,@CurrencyID ,@fExchangeRate ,@ForeignDebit ,@ForeignCredit ,@Description ,@Reference ,@cReference2 ,@Order_No ,@ExtOrderNum ,@ProjectID ,@UserName ,@DrCrAccount,@Module,@ModuleAccountId,@BranchId,@OldTxId
WHILE @@FETCH_STATUS = 0
BEGIN
--Post Using _bspPostGLTrans
SELECT @AutoIdx = 0; --RESET AutoIdx (because it's also an output parameter)
EXECUTE @RC = [dbo].[_bspPostGLTrans]
@AutoIdx = @AutoIdx OUTPUT
,@TxDate = @TxDateCursor
,@Id = @EvoId
,@AccountLink = @AccountLink
,@TrCodeID = @TrCodeID
,@Debit = @Debit
,@Credit = @Credit
,@CurrencyID = @CurrencyID
,@ExchangeRate = @fExchangeRate
,@ForeignDebit = @ForeignDebit
,@ForeignCredit = @ForeignCredit
,@Description = @Description
,@TaxTypeID = 0
,@Reference = @Reference
,@Order_No = @Order_No
,@ExtOrderNum = @ExtOrderNum
,@AuditNumber = @AuditNo
,@Tax_Amount = 0
,@ForeignTaxAmount = 0
,@Project = @ProjectID
,@Period = @PeriodID
,@Reconciled = 0
,@DrCrAccount = @DrCrAccount
,@JobCodeLink = 0
,@MFPID = 0
,@UserName = @UserName
,@cPayeeName = ''
,@bPrintCheque = 0
,@Reference2 = @cReference2
,@TxBranchID = @BranchId
,@RepID = 0
,@JCRepCost = 0
,@IsJCDocLine = 0
,@IsSTGLDocLine = 0
,@iInvLineID = 0
,@cBankRef = ''
,@bMaintainBranchID = 1
,@GLTaxAccountID = 0
,@cLineUserFields = ''
---------------------------------------------------------------------
-- PostAR line (if applicable):
---------------------------------------------------------------------
IF (@Module = 'AR') --AR line.
BEGIN
SET @AutoIdx_AR = 0
SELECT @Outstanding = @Debit - @Credit
,@ForeignOutstanding = @ForeignDebit - @ForeignCredit
----------------MASTER Account handling (linked accounts)----------------
SELECT @MainAccLink = COALESCE((select MainAccLink from Client where DCLink = @ModuleAccountId),0)
IF (@MainAccLink > 0) --now handle linked account stuff.
BEGIN
SELECT @LinkAccLink = @ModuleAccountId,@ARAccountLink = @MainAccLink
END
ELSE --so @MainAccLink = 0...
BEGIN
SELECT @ARAccountLink = @ModuleAccountId, @LinkAccLink = 0
END
EXECUTE @RC = [dbo].[_bspPostARTrans]
@AutoIdx = @AutoIdx_AR OUTPUT
,@TxDate = @TxDateCursor
,@Id = @EvoId
,@AccountLink = @ARAccountLink
,@TrCodeID = @TrCodeID
,@Debit = @Debit
,@Credit = @Credit
,@CurrencyID = @CurrencyID
,@ExchangeRate = @fExchangeRate
,@ForeignDebit = @ForeignDebit
,@ForeignCredit = @ForeignCredit
,@Description = @Description
,@TaxTypeID = 0
,@Reference = @Reference
,@Order_No = @Order_No
,@ExtOrderNum = @ExtOrderNum
,@AuditNumber = @AuditNo
,@Tax_Amount = 0 --no tax on ar.
,@ForeignTaxAmount = 0 --no tax on ar
,@Project = @ProjectID
,@Outstanding = @Outstanding
,@ForeignOutstanding = @ForeignOutstanding
,@InvNumKey = 0
,@RepID = 0
,@LinkAccCode = @LinkAccLink
,@TillID = 0
,@UserName = @UserName
,@Reference2 = @cReference2
,@SettlementTermsID = 0 --TO BE FIXED LATER.
,@TxBranchID = @TxBranchID
,@iMBPropertyID = 0 --@iMBPropertyID
,@iMBPortionID = 0 --@iMBPortionID
,@iMBServiceID = 0 --@iMBServiceID
,@iMBMeterID = 0 --@iMBMeterID
,@iMBPropertyPortionServiceID = 0 --@iMBPropertyPortionServiceID
,@GLTaxAccountID = 0 --@iTaxAccountID
,@JCRepCost = 0
,@cLineUserFields = ''
,@iTransactionType = 0
,@SagePayExtra1 = ''
,@SagePayExtra2 = ''
,@SagePayExtra3 = '';
END --IF (@iModule = 1) --AR line.
---------------------------------------------------------------------
-- END OF: PostAR line (if applicable):
---------------------------------------------------------------------
--IF AP module, the post into PostAP
---------------------------------------------------------------------
-- PostAP line (if applicable):
---------------------------------------------------------------------
IF (@Module = 'AP') --AP line.
BEGIN
SET @AutoIdx_AP = 0
SELECT @Outstanding = @Credit - @Debit
,@ForeignOutstanding = @ForeignCredit - @ForeignDebit
EXECUTE @RC = [dbo].[_bspPostAPTrans]
@AutoIdx = @AutoIdx_AP OUTPUT
,@TxDate = @TxDateCursor
,@Id = @EvoId
,@AccountLink = @ModuleAccountId
,@TrCodeID = @TrCodeID
,@Debit = @Debit
,@Credit = @Credit
,@CurrencyID = @CurrencyID
,@ExchangeRate = @fExchangeRate
,@ForeignDebit = @ForeignDebit
,@ForeignCredit = @ForeignCredit
,@Description = @Description
,@TaxTypeID = 0 --no tax type in a postap
,@Reference = @Reference
,@Order_No = @Order_No
,@ExtOrderNum = @ExtOrderNum
,@AuditNumber = @AuditNo
,@Tax_Amount = 0 --no tax in ap
,@ForeignTaxAmount = 0
,@Project = @ProjectID
,@Outstanding = @Outstanding
,@ForeignOutstanding = @ForeignOutstanding
,@InvNumKey = 0
,@UserName = @UserName
,@Reference2 = @cReference2
,@SettlementTermsID = 0
,@TxBranchID = @BranchID
,@GLTaxAccountID = 0 --@iTaxAccountID
,@cLineUserFields = ''
,@SagePayExtra1 = ''
,@SagePayExtra2 = ''
,@SagePayExtra3 = ''
END --IF (@iModule = 2) --AP line.
---------------------------------------------------------------------
-- END OF: PostAP line (if applicable):
---------------------------------------------------------------------
--Insert full record into log-table which will be returned.
INSERT INTO @tblResult (OldTxId,NewTxId,NewARId,NewAPId) VALUES (@OldTxId, @AutoIdx, @AutoIdx_AR, @AutoIdx_AP);
--fetch next cursor row:
FETCH NEXT FROM MyCursor INTO @TxDateCursor ,@EvoId ,@AccountLink ,@TrCodeID ,@Debit ,@Credit ,@CurrencyID ,@fExchangeRate ,@ForeignDebit ,@ForeignCredit ,@Description ,@Reference ,@cReference2 ,@Order_No ,@ExtOrderNum ,@ProjectID ,@UserName ,@DrCrAccount,@Module,@ModuleAccountId,@BranchId,@OldTxId
END
CLOSE MyCursor
DEALLOCATE MyCursor
-- SELECT @AuditNo;
/*Log it:*/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '_as_PostingLog')
BEGIN
CREATE TABLE _as_PostingLog (
Id int identity(1,1) primary key,
AuditNumber nvarchar(100),
ResultIds nvarchar(1000),
InputData nvarchar(max),
CreatedDate datetime DEFAULT GETDATE()
)
END --end of table creation
INSERT INTO _as_PostingLog (AuditNumber,ResultIds,InputData)
(SELECT @AuditNo
,(SELECT * FROM @tblResult FOR JSON PATH)
,(SELECT * FROM @txTable FOR JSON PATH)
)
--COMMIT TRANSACTION glBatch
--returning the result table
SELECT * FROM @tblResult
GOTO EndProc
ErrorSection:
--ROLLBACK TRANSACTION glBatch
RaisError(@msg,17,1);
RETURN
--End of error section
--end of proc:
EndProc:
END