SQL - PostGL Transaction: _as_PostGL_SimpleJournal

Usage of proc:

       DECLARE @RC2 varchar(100)
        EXEC _as_PostGL_SimpleJournal @RC2 OUTPUT, '2024-12-31',57,44,219.79,'2024-Audit','2024-Audit','Ziektekosten - naar RC','Alex'
        SELECT @RC2
        select * from PostGL where cAUditNumber = @RC2

Dependency

Get period from date (v1)

create or ALTER FUNCTION [dbo].[_as_GetPeriodFromDate](@TxDate DATE)
RETURNS INT 
AS
BEGIN
/* Usage:
 SELECT dbo.[_as_GetPeriodFromDate]('2021-12-05')

*/

--declare @TxDate date =  '2021-12-05' -- getdate()
DECLARE @ret int

;WITH x as (
SELECT P1.idPeriod
			,COALESCE(DATEADD(DAY,1,P2.dPeriodDate)
			,(SELECT TOP 1 dCommencementDate FROM Entities E WHERE dCommencementDate IS NOT NULL ORDER BY idEntities DESC)) as FromDate
			,P1.dPeriodDate as EndDate
			,P1.bBlocked
		FROM _etblPeriod P1 LEFT JOIN _etblPeriod P2 ON P1.idPeriod -1 = P2.idPeriod
		
	) --P ON CAST(@TxDate as date) BETWEEN P.FromDate AND P.EndDate
SELECT @ret = COALESCE((select idPeriod from x P where @TxDate BETWEEN P.FromDate AND P.EndDate),0)

RETURN @ret

END

Get Period From Date (optimized for performance)

IF EXISTS(SELECT 1 FROM   INFORMATION_SCHEMA.ROUTINES WHERE  ROUTINE_NAME = '_as_GetPeriodFromDate' AND SPECIFIC_SCHEMA = 'dbo')
      DROP FUNCTION _as_GetPeriodFromDate;
GO
-- =============================================
-- Author:      Asamco BV - Alex
-- Create date: 28-12-2020
-- Description: Get period based on date.
-- =============================================
CREATE FUNCTION [dbo].[_as_GetPeriodFromDate](@Date Date)
RETURNS INT
AS
BEGIN
    DECLARE @Period INT
    DECLARE @CommencementDate DateTime
    set @CommencementDate = (select top 1 dCommencementDate from Entities)
    SET @Period = COALESCE((SELECT MIN(idPeriod) FROM _etblPeriod WHERE dPeriodDate >= @Date AND @Date >= @CommencementDate),0)
  
    RETURN @Period
END

Procedure

_as_PostGL_SimpleJournal

---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM   INFORMATION_SCHEMA.ROUTINES WHERE  ROUTINE_NAME = '_as_PostGL_SimpleJournal' AND SPECIFIC_SCHEMA = 'dbo')
      DROP PROCEDURE _as_PostGL_SimpleJournal;
GO
-- =============================================
-- Author:      Asamco BV - Alex
-- Create date: 28-12-2020
-- Description: Post GL Tx - Simple.
-- =============================================
CREATE PROCEDURE dbo._as_PostGL_SimpleJournal
    @AuditNumber varchar(100) = NULL OUTPUT
    ,@TxDate date
    ,@DebitAcc bigint
    ,@CreditAcc bigint
    ,@Amount float
    ,@Ref1 varchar(50)
    ,@Ref2 varchar(50)
    ,@Desc varchar(100)
    ,@UserName varchar(20)
    ,@TrCodeID int = 2
    ,@BranchID int = 0
    ,@ProjectID int = 0
--RETURNS VARCHAR(100)
AS
BEGIN
    /*USAGE:
        --v1 with no audit nr:
        DECLARE @RC2 varchar(100)
        EXEC _as_PostGL_SimpleJournal @RC2 OUTPUT, '2020-12-05',3,4,25.25,'ref1','ref2','desc','Alex'
        SELECT @RC2
        select * from PostGL where cAUditNumber = @RC2
  
        --v2 with audit nr:
        DECLARE @RC2 varchar(100) = 'bla2'
        EXEC _as_PostGL_SimpleJournal @RC2 OUTPUT, '2020-12-05',3,4,25.25,'ref1','ref2','desc','Alex'
        SELECT @RC2
  
        select * from PostGL where cAUditNumber = @RC2
    */
      
  
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
  
    --validations:
    DECLARE @production bit = 1
    --GL Account does not exist:
    --Period not valid
    DECLARE @TxPeriod int, @HasError int, @ErrMsgs nvarchar(1000)
    SELECT @TxPeriod = dbo._as_GetPeriodFromDate(@TxDate)
    SET @ErrMsgs = ''
    IF (@production = 0) PRINT 'Start: ' + CAST(@TxPeriod as varchar(100))
  
    --to date: check if period exists.
    IF (@TxPeriod = 0)
        BEGIN
            SET @HasError = 1
            SET @ErrMsgs = @ErrMsgs + CHAR(10) + 'Tx date is not in a valid period: ' + CONVERT(varchar,@TxDate,103)
        END
      
IF (@HasError = 1) GOTO ErrorHandling;
  
--Additional declares:
DECLARE @AutoIdx bigint, @RC int
  
--Audit nr handling:
-- Get the audit number
IF @AuditNumber IS NULL
BEGIN
        DECLARE @AuditNo varchar(100), @AuditTemp varchar(100), @Counter int, @AuditSuffix varchar(20)
        EXEC @AuditTemp = _bspNextAuditNo;
        SELECT @Counter = 1
        --Audit number:
        SELECT @AuditSuffix = RIGHT('.000' + CAST(@Counter as varchar(50)) , 5)
        select @AuditNo = CASE
            WHEN @BranchID NOT IN (-1,0) THEN Cast(@BranchID as varchar(20)) + '.' + CAST(@AuditTemp as varchar) + @AuditSuffix
            ELSE CAST(@AuditTemp as varchar) + @AuditSuffix END; --'GET NEXT AUDIT NO!!!!' --Check SP _bspNextAuditNo
        SET @Counter = @Counter + 1; --increase the counter after using it.
END --@AuditNumber is null
ELSE
BEGIN
    SET @AuditNo = @AuditNumber;
END
IF (@production = 0) PRINT 'Audit: ' +@AuditNo
  
    --Debit Tx:
EXECUTE @RC = [dbo].[_bspPostGLTrans]
   @AutoIdx OUTPUT
  ,@TxDate = @TxDate
  ,@Id = 'JL'
  ,@AccountLink = @DebitAcc
  ,@TrCodeID = @TrCodeID
  ,@Debit = @Amount
  ,@Credit = 0
  ,@CurrencyID = 0
  ,@ExchangeRate = 0
  ,@ForeignDebit = 0
  ,@ForeignCredit = 0
  ,@Description = @Desc
  ,@TaxTypeID = 0
  ,@Reference = @Ref1
  ,@Order_No = ''
  ,@ExtOrderNum = ''
  ,@AuditNumber = @AuditNo
  ,@Tax_Amount = 0
  ,@ForeignTaxAmount = 0
  ,@Project = @ProjectID
  ,@Period = @TxPeriod
  ,@Reconciled = 0
  ,@DrCrAccount = @CreditAcc
  ,@JobCodeLink = 0
  ,@MFPID = 0
  ,@UserName = @UserName
  ,@cPayeeName = ''
  ,@bPrintCheque = 0
  ,@Reference2 = @Ref2
  ,@TxBranchID = @BranchID
  ,@RepID = 0
  ,@JCRepCost = 0
  ,@IsJCDocLine = 0
  ,@IsSTGLDocLine = 0
  ,@iInvLineID = 0
  ,@cBankRef= ''
  ,@bMaintainBranchID = 0
  ,@GLTaxAccountID = 0
  ,@cLineUserFields = ''
  
  
    --Credit Tx:
    SET @AutoIdx = 0
    EXECUTE @RC = [dbo].[_bspPostGLTrans]
   @AutoIdx OUTPUT
  ,@TxDate = @TxDate
  ,@Id = 'JL'
  ,@AccountLink = @CreditAcc
  ,@TrCodeID = @TrCodeID
  ,@Debit = 0
  ,@Credit = @Amount
  ,@CurrencyID = 0
  ,@ExchangeRate = 0
  ,@ForeignDebit = 0
  ,@ForeignCredit = 0
  ,@Description = @Desc
  ,@TaxTypeID = 0
  ,@Reference = @Ref1
  ,@Order_No = ''
  ,@ExtOrderNum = ''
  ,@AuditNumber = @AuditNo
  ,@Tax_Amount = 0
  ,@ForeignTaxAmount = 0
  ,@Project = @ProjectID
  ,@Period = @TxPeriod
  ,@Reconciled = 0
  ,@DrCrAccount = @DebitAcc
  ,@JobCodeLink = 0
  ,@MFPID = 0
  ,@UserName = @UserName
  ,@cPayeeName = ''
  ,@bPrintCheque = 0
  ,@Reference2 = @Ref2
  ,@TxBranchID = @BranchID
  ,@RepID = 0
  ,@JCRepCost = 0
  ,@IsJCDocLine = 0
  ,@IsSTGLDocLine = 0
  ,@iInvLineID = 0
  ,@cBankRef= ''
  ,@bMaintainBranchID = 0
  ,@GLTaxAccountID = 0
  ,@cLineUserFields = '';
    
  SELECT @AuditNumber = @AuditNo; --Exit proc. returning
  PRINT @AuditNo + @AuditNumber
  RETURN
ErrorHandling:
    RaisError(@ErrMsgs,17,1)
    RETURN -1
  
END --Stored proc

Space shortcuts

Page tree

Dependency

Procedure