_as_CreateSegmentedGLAccount
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Asamco BV - Alex
-- Create date: 26-05-2023
-- Description: Create Segmented GL Account
-- =============================================
CREATE OR ALTER PROCEDURE dbo._as_CreateSegmentedGLAccount
-- Add the parameters for the stored procedure here
@Segment1Code nvarchar(100),
@Segment1Desc nvarchar(100),
@Segment2Code nvarchar(100) = '',
@Segment2Desc nvarchar(100) = '',
@Segment3Code nvarchar(100) = '',
@Segment3Desc nvarchar(100) = '',
@Segment4Code nvarchar(100) = '',
@Segment4Desc nvarchar(100) = '',
@Segment5Code nvarchar(100) = '',
@Segment5Desc nvarchar(100) = '',
@AccountTypeDesc nvarchar(100),
@AllowSales bit = 0,
@AllowPurchases bit = 0,
@AllowJournal bit = 1,
@BranchId int = 0,
@CustomAccountDesc nvarchar(200) = NULL,
@AccountLink bigint OUTPUT
AS
BEGIN
/*
declare @AccountLink bigint =0
EXEC dbo._as_CreateSegmentedGLAccount
@Segment1Code = 'Value4',
@Segment1Desc = 'Desc1',
@Segment2Code = 'Value44',
@Segment2Desc = 'Desc2',
@Segment3Code = 'Value3',
@Segment3Desc = 'Desc3',
@AccountTypeDesc = 'Cash and Cash equivalents',
@AllowSales = 0,
@AllowPurchases = 1,
@AllowJournal = 1,
@BranchId = 0,
@AccountLink = @AccountLink OUTPUT;
SELECT @AccountLink, * FROM Accounts where AccountLink = @AccountLink
*/
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/*check and or set account type:*/
DECLARE @AccountTypeId int,@Msg nvarchar(1000)
SELECT TOP 1 @AccountTypeId = idGLAccountType FROM _etblGLAccountTypes WHERE cAccountTypeDescription = @AccountTypeDesc
IF COALESCE(@AccountTypeId,0) = 0
BEGIN
SET @Msg = CONCAT('Account Type ',@AccountTypeDesc,' doesnt exist')
RAISERROR(@msg,17,1); return -1
END
/*retrieve or create segment*/
DECLARE @Segment1Id bigint,@Segment2Id bigint,@Segment3Id bigint,@Segment4Id bigint,@Segment5Id bigint
/*Getting or creating segment Ids:*/
--EXEC _as_CreateSegment @Segment1Id output,@Segment1Code,@Segment1Desc,0
EXEC _as_CreateSegment @Segment1Id OUTPUT, @Segment1Code, @Segment1Desc, 0;
EXEC _as_CreateSegment @Segment2Id OUTPUT, @Segment2Code, @Segment2Desc, 1;
EXEC _as_CreateSegment @Segment3Id OUTPUT, @Segment3Code, @Segment3Desc, 2;
EXEC _as_CreateSegment @Segment4Id OUTPUT, @Segment4Code, @Segment4Desc, 3;
EXEC _as_CreateSegment @Segment5Id OUTPUT, @Segment5Code, @Segment5Desc, 4;
DECLARE @MasterSubAcc nvarchar(100), @AccountDesc nvarchar(300), @Divisor varchar(1)
SET @Divisor = COALESCE((SELECT TOP 1 E.cGLSegmentSeparator FROM Entities E),'/')
SET @CustomAccountDesc = CASE WHEN @CustomAccountDesc = '' THEN NULL ELSE @CustomAccountDesc END /*used to be able to use COALESCE down.*/
SELECT @MasterSubAcc = CONCAT(@Segment1Code
,CASE WHEN @Segment2Code <> '' THEN CONCAT(@Divisor,@Segment2Code) ELSE '' END
,CASE WHEN @Segment3Code <> '' THEN CONCAT(@Divisor,@Segment3Code) ELSE '' END
,CASE WHEN @Segment4Code <> '' THEN CONCAT(@Divisor,@Segment4Code) ELSE '' END
,CASE WHEN @Segment5Code <> '' THEN CONCAT(@Divisor,@Segment5Code) ELSE '' END
)
,@AccountDesc = COALESCE(@CustomAccountDesc,CONCAT(@Segment1Desc
,CASE WHEN @Segment2Desc <> '' THEN CONCAT(@Divisor,@Segment2Desc) ELSE '' END
,CASE WHEN @Segment3Desc <> '' THEN CONCAT(@Divisor,@Segment3Desc) ELSE '' END
,CASE WHEN @Segment4Desc <> '' THEN CONCAT(@Divisor,@Segment4Desc) ELSE '' END
,CASE WHEN @Segment5Desc <> '' THEN CONCAT(@Divisor,@Segment5Desc) ELSE '' END
))
IF NOT EXISTS(SELECT * FROM Accounts A WHERE A.Master_Sub_Account = @MasterSubAcc)
BEGIN
PRINT CONCAT('Creating account: ',
' @Segment1Code>>: ', @Segment1Code,
' @Segment1Desc>>: ', @Segment1Desc,
' @Segment2Code>>: ', @Segment2Code,
' @Segment2Desc>>: ', @Segment2Desc,
' @Segment3Code>>: ', @Segment3Code,
' @Segment3Desc>>: ', @Segment3Desc,
' @Segment4Code>>: ', @Segment4Code,
' @Segment4Desc>>: ', @Segment4Desc,
' @Segment5Code>>: ', @Segment5Code,
' @Segment5Desc>>: ', @Segment5Desc,
' @AccountTypeDesc>>: ', @AccountTypeDesc,
' @AllowSales>>: ', CASE WHEN @AllowSales = 1 THEN 'True' ELSE 'False' END,
' @AllowPurchases>>: ', CASE WHEN @AllowPurchases = 1 THEN 'True' ELSE 'False' END,
' @AllowJournal>>: ', CASE WHEN @AllowJournal = 1 THEN 'True' ELSE 'False' END,
' @BranchId>>: ', @BranchId
);
DECLARE @tblOutput TABLE (Id bigint)
/*Now create the account:*/
INSERT INTO [dbo].[Accounts]
([Master_Sub_Account]
,[AccountLevel]
,[Account]
,[iAccountType]
,[SubAccOfLink]
,[Dept]
,[Brch]
,[Jr]
,[Description]
,[CaseAcc]
,[ActiveAccount]
,[dAccountsTimeStamp]
,[cNextChequeNum]
,[iGLSegment0ID]
,[iGLSegment1ID]
,[iGLSegment2ID]
,[iGLSegment3ID]
,[iGLSegment4ID]
,[iGLSegment5ID]
,[iGLSegment6ID]
,[iGLSegment7ID]
,[iGLSegment8ID]
,[iGLSegment9ID]
,[iReportCategoryID]
,[fBankStatementBalance]
,[cExtDescription]
,[iTaxTypeINVID]
,[iTaxTypeCRNID]
,[iTaxTypeGRVID]
,[iTaxTypeRTSID]
,[iAllowICSales]
,[iAllowICPurchases]
,[iMBReportingCategoryID]
,[iMBCashFlowCategoryID]
,[bMBIsAsset]
,[bMBIsGrant]
,[iMBAssetClassificationID]
,[iMBAssetCategoryID]
,[iMBAssetTypeID]
,[iMBGrantLevel1TypeID]
,[iMBGrantLevel2TypeID]
,[iMBGrantLevel3TypeID]
,[bIsBranchLoanAccount]
,[bForeignBankAcc]
,[iForeignBankCurrencyID]
,[iForeignBankPEXAccID]
,[iForeignBankLEXAccID]
,[bRevalueWithSellingRate]
,[bPaymentsBasedTax]
,[cBankName]
,[cBankAccountName]
,[cBankCode]
,[cBankAccountNumber]
,[cBranchName]
,[cSEPABranchCode]
,[cBankRefNr]
,[Accounts_iBranchID]
)
OUTPUT inserted.AccountLink INTO @tblOutput (Id)
VALUES
(@MasterSubAcc --<Master_Sub_Account, varchar(91),>
,0 --<AccountLevel, int,>
,@MasterSubAcc --<Account, varchar(91),>
,@AccountTypeId --<iAccountType, int,>
,0 --<SubAccOfLink, int,>
,'' --<Dept, varchar(10),>
,'' --<Brch, varchar(10),>
,@AllowJournal --<Jr, bit,>
,@AccountDesc --<Description, varchar(255),>
,'' --<CaseAcc, varchar(10),>
,1 --<ActiveAccount, bit,>
,GETDATE() --<dAccountsTimeStamp, datetime,>
,'' --<cNextChequeNum, varchar(20),>
,@Segment1Id --<iGLSegment0ID, int,>
,@Segment2Id --<iGLSegment1ID, int,>
,@Segment3Id --<iGLSegment2ID, int,>
,@Segment4Id --<iGLSegment3ID, int,>
,@Segment5Id --<iGLSegment4ID, int,>
,0 --<iGLSegment5ID, int,>
,0 --<iGLSegment6ID, int,>
,0 --<iGLSegment7ID, int,>
,0 --<iGLSegment8ID, int,>
,0 --<iGLSegment9ID, int,>
,0 --<iReportCategoryID, int,>
,0 --<fBankStatementBalance, float,>
,@AccountDesc --<cExtDescription, varchar(255),>
,0 --<iTaxTypeINVID, int,>
,0 --<iTaxTypeCRNID, int,>
,0 --<iTaxTypeGRVID, int,>
,0 --<iTaxTypeRTSID, int,>
,@AllowSales --<iAllowICSales, bit,>
,@AllowPurchases --<iAllowICPurchases, bit,>
,0 --<iMBReportingCategoryID, int,>
,0 --<iMBCashFlowCategoryID, int,>
,0 --<bMBIsAsset, bit,>
,0 --<bMBIsGrant, bit,>
,0 --<iMBAssetClassificationID, int,>
,0 --<iMBAssetCategoryID, int,>
,0 --<iMBAssetTypeID, int,>
,0 --<iMBGrantLevel1TypeID, int,>
,0 --<iMBGrantLevel2TypeID, int,>
,0 --<iMBGrantLevel3TypeID, int,>
,0 --<bIsBranchLoanAccount, bit,>
,0 --<bForeignBankAcc, bit,>
,0 --<iForeignBankCurrencyID, int,>
,0 --<iForeignBankPEXAccID, int,>
,0 --<iForeignBankLEXAccID, int,>
,0 --<bRevalueWithSellingRate, bit,>
,0 --<bPaymentsBasedTax, bit,>
,'' --<cBankName, varchar(40),>
,'' --<cBankAccountName, varchar(50),>
,'' --<cBankCode, varchar(15),>
,'' --<cBankAccountNumber, varchar(40),>
,'' --<cBranchName, varchar(30),>
,'' --<cSEPABranchCode, varchar(30),>
,'' --<cBankRefNr, varchar(30),>
,@BranchId --<Accounts_iBranchID, int,>
)
SELECT TOP 1 @AccountLink = Id FROM @tblOutput
END --if not exists @MasterSubAcc
ELSE
BEGIN
PRINT CONCAT('Account already exists: ', @MasterSubAcc);
SELECT TOP 1 @AccountLink = AccountLink FROM Accounts A WHERE A.Master_Sub_Account = @MasterSubAcc
END
END
GO
Dependency:
_as_CreateSegment
GO
-- =============================================
-- Author: Asamco BV - Alex
-- Create date: 26-05-2023
-- Description: Create Segment
-- =============================================
CREATE OR ALTER PROCEDURE dbo._as_CreateSegment
@idSegment bigint = 0 OUTPUT , /*if set, we will update*/
@SegmentCode nvarchar(100),
@SegmentDesc nvarchar(100),
@SegmentNo int,
@BranchId int = 0
AS
BEGIN
SET NOCOUNT ON;
/*usage:
exec _as_CreateSegment 0,'bla','balbla',0
declare @segId bigint
exec _as_CreateSegment @segId output,'bla4','balbla4',0
select @segid
select * from _etblGLSegment
*/
/*early out if no segment code*/
IF (COALESCE(@SegmentCode,'') = '')
BEGIN
SET @idSegment = 0
RETURN -1
END
/*check if exists.*/
BEGIN TRANSACTION
IF (COALESCE(@idSegment,0) =0)
BEGIN
SELECT @idSegment = COALESCE((SELECT idSegment FROM _etblGLSegment S WHERE S.cCode = @SegmentCode AND S.iSegmentNo = @SegmentNo),0)
IF (COALESCE(@idSegment,0) = 0)
BEGIN
DECLARE @tblSegmentId TABLE (Id bigint)
PRINT CONCAT('Creating segment (Code: ',@SegmentCode,' Desc: ',@SegmentDesc, ' SegNo: ',@SegmentNo)
/*Create segment.*/
INSERT INTO [dbo].[_etblGLSegment]
([iSegmentNo]
,[cCode]
,[cDescription]
,[iSegmentBranchID]
,[_etblGLSegment_iBranchID]
,[imSCOAAccountID]
,[mSCOAId])
OUTPUT inserted.idSegment INTO @tblSegmentId (Id)
VALUES
(@SegmentNo --<iSegmentNo, int,>
,@SegmentCode --<cCode, varchar(40),>
,@SegmentDesc --<cDescription, varchar(40),>
,@BranchId --<iSegmentBranchID, int,>
,@BranchId --<_etblGLSegment_iBranchID, int,>
,0 --<imSCOAAccountID, int,>
,null --<mSCOAId, varchar(50),>
)
SELECT TOP 1 @idSegment = Id FROM @tblSegmentId
END
END --IF (@idSegment =0)
ELSE
BEGIN
UPDATE _etblGLSegment
SET cCode = @SegmentCode
,cDescription = @SegmentDesc
,iSegmentNo = @SegmentNo
WHERE idSegment = @idSegment
END
COMMIT TRANSACTION
END --sproc