(Back to B-FN Financials Module SQL Posted to Wiki Listing - B-FN Financials Module SQL Posted to Wiki )
TYPE: Monthly Special.
AUTHOR NAME: Neetu Dhami
PURPOSE AND BACKGROUND: This SQL was created in 8.8 PL16.This query can be used to generate report that enables you to show a list of journal entries posted to the company database based on Account code and Date range as selection criteria, and query can be modified as per user requirements.
Below is screen shot of results displayed by query: 
Copy and Paste this SQL:
--B-FN General Ledger Report Ver 1 ND 10June,2011
--DESCRIPTION: This SQL script can be used to generate general ledger report as shown above and be modified as required.
--AUTHOR(s):
--Version 1 Neetu Dhami 10 June,2011
Copy and Paste below script in Query Generator:
declare @sAccFrom as nvarchar(10)
declare @sAccTo as nvarchar(10)
declare @sPCFrom as nvarchar(10)
declare @sPCTo as nvarchar(10)
declare @dPostFromMax as datetime
declare @dPostToMax as datetime
declare @dPostFrom as datetime
declare @dPostTo as datetime
declare @dDocFrom as datetime
declare @dDocTo as datetime
declare @dTaxFrom as datetime
declare @dTaxTo as datetime
declare @dPeriodFrom as datetime
declare @dPeriodTo as datetime
declare @dPeriod as datetime
declare @sTmp as nchar(1)
declare @AcctCode as nvarchar(20)
declare @AcctCode_Tmp as nvarchar(20)
declare @AcctName as nvarchar(100)
declare @RefDate as datetime
declare @TransType as nvarchar(10)
declare @TransTypeD as nvarchar(4)
declare @TransId as int
declare @Desc2 as nvarchar(254)
declare @LineMemo as nvarchar(254)
declare @Debit as numeric(19,6)
declare @Credit as numeric(19,6)
declare @ValueTmp as numeric(19,6)
declare @DiscPrcnt as numeric(19,6)
declare @PrevBalance as numeric(19,6)
declare @PrevBalanceTmp as numeric(19,6)
declare @Balance as numeric(19,6)
declare @OpenBalance as numeric(19,6)
declare @GrandBalance as numeric(19,6)
declare @Index as int
declare @CreditAcc as nvarchar(1)
declare @PYear as int
declare @CurrPTmp as int
declare @CurrP as int
declare @PFrom as int
declare @PTo as int
/ * SELECT FROM [dbo].[JDT1] T0 * /
declare @FromAcct as nvarchar(20)
/ * WHERE * /
set @FromAcct = / * T0.Account * / '[%0]'
/ * SELECT FROM [dbo].[JDT1] T1 * /
declare @ToAcct as nvarchar(20)
/ * WHERE * /
set @ToAcct = / * T0.Account * / '[%1]'
CREATE TABLE [#GeneralLedger] (IdInt int PRIMARY KEY IDENTITY,
LineType int,
AcctCode nvarchar(20),
AcctName nvarchar(100),
RefDate datetime,
TransType nvarchar(4),
TransTypeCode nvarchar(10),
TransId int,
Desc2 nvarchar(254),
LineMemo nvarchar(254),
Debit numeric(19,6),
Credit numeric(19,6),
Balance numeric(19,6)
)
/ * SELECT FROM [dbo].[JDT1] T0 * /
declare @FromDate as datetime
/ * WHERE * /
set @FromDate = / * T0.RefDate * / '[%2]'
/ * SELECT FROM [dbo].[JDT1] T1 * /
declare @ToDate as datetime
/ * WHERE * /
set @ToDate = / * T0.RefDate * / '[%3]'
SET @dPostFromMax = GETDATE()
SET @dPostFromMax = @FromDate
SET @dPostToMax = GETDATE()
SET @dPostToMax = @ToDate
SET @Index=0
SET @PFrom = 0
SET @PYear = DATEPART(YEAR, @dPostFromMax)
SET @PFrom = (select top 1 T1.AbsEntry from OFPR T1 where DATEPART(YEAR,T1.F_RefDate)= @PYear AND DATEPART(MONTH,T1.F_RefDate)=DATEPART(MONTH, @dPostFromMax))
SET @PTo = (select top 1 T1.AbsEntry from OFPR T1 where DATEPART(YEAR,T1.T_RefDate)= @PYear AND DATEPART(MONTH,T1.T_RefDate)=DATEPART(MONTH, @dPostToMax))
DECLARE First1 CURSOR FOR
select T0.AcctCode, T0.AcctName,
T1.RefDate,T1.TransType,
case when T1.TransType=13 then 'IN'
when T1.TransType=14 then 'CN'
when T1.TransType=15 then 'DN'
when T1.TransType=16 then 'RE'
when T1.TransType=162 then 'MR'
when T1.TransType=18 then 'PU'
when T1.TransType=19 then 'PC'
when T1.TransType=20 then 'PD'
when T1.TransType=202 then 'PW'
when T1.TransType=21 then 'PR'
when T1.TransType=24 then 'RC'
when T1.TransType=30 then 'JE'
when T1.TransType=46 then 'PS'
when T1.TransType=58 then 'ST'
when T1.TransType=59 then 'SI'
when T1.TransType=60 then 'SO'
when T1.TransType=67 then 'IM'
else T1.TransType end,
T1.TransId,
'',
T1.LineMemo,
case when T1.Debit=0.0 then NULL else T1.Debit end,
case when T1.Credit=0.0 then NULL else T1.Credit end,
T0.LocManTran
from oact T0, jdt1 T1
where T1.TransType <> -3
AND (T1.Debit <> 0.0 or T1.Credit <> 0.0)
and T0.AcctCode = T1.Account
AND T1.Account >= @FromAcct
AND T1.Account <= @ToAcct
AND T1.RefDate >= @FromDate
AND T1.RefDate <= @ToDate
ORDER BY T0.AcctCode, T1.RefDate, T1.TransId
SET @AcctCode_Tmp=''
SET @PrevBalance=0.0
SET @Balance=0.0
SET @OpenBalance=0.0
SET @CurrP = @PFrom
OPEN First1
FETCH NEXT FROM First1
INTO @AcctCode, @AcctName,
@RefDate, @TransType, @TransTypeD,
@TransId, @Desc2, @LineMemo, @Debit, @Credit , @CreditAcc
WHILE @@Fetch_Status = 0
BEGIN
SET @CurrPTmp = (select top 1 AbsEntry from OFPR WHERE T_RefDate>=@RefDate and F_RefDate<=@RefDate (mailto:=@RefDate))
IF ( @CurrP <> @CurrPTmp and @AcctCode = @AcctCode_Tmp)
BEGIN
SET @dPeriodFrom = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrPTmp)))
SET @dPeriodTo = (select top 1 T1.T_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrPTmp)))
IF (SELECT COUNT(*) FROM [#GeneralLedger]) > 0
BEGIN
SET @Balance= @Balance + IsNULL( @PrevBalance,0.0)
SET @dPeriod = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' + DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL, @PrevBalance - @OpenBalance
INSERT INTO [#GeneralLedger] select 1,'', CAST(DATEPART(DAY, @dPeriodFrom) as NVARCHAR(2))' ' + DATENAME(MONTH, @dPeriodFrom) + ','+ DATENAME(YEAR, @dPeriodFrom)' - ' + CAST(DATEPART(DAY, @dPeriodTo) as NVARCHAR(2))' ' DATENAME(MONTH, @dPeriodTo)+',' + DATENAME(YEAR, @dPeriodTo),NULL,'','',NULL,'','',NULL,NULL,NULL
SET @OpenBalance= IsNULL( @PrevBalance,0.0)
END
SET @CurrP = @CurrPTmp
END
IF @AcctCode <> @AcctCode_Tmp
BEGIN
IF (SELECT COUNT(*) FROM [#GeneralLedger])>0
BEGIN
SET @Balance= @Balance + IsNULL( @PrevBalance,0.0)
SET @dPeriod = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' + DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL, @PrevBalance- @OpenBalance
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total For Account ' + @AcctCode_Tmp+ ' :',NULL,NULL, @PrevBalance
END
SET @CurrP = @CurrPTmp
SET @PrevBalance=(SELECT IsNULL(SUM(IsNULL(T1.Debit,0.0)),0.0) - IsNULL(SUM(IsNULL(T1.Credit,0.0)),0.0) FROM oact T0, jdt1 T1 where T1.TransType <> -3 and T1.Account = T0.AcctCode AND T1.Account >= @FromAcct AND T1.Account <= @ToAcct AND T1.RefDate < @FromDate)
SET @AcctCode_Tmp = @AcctCode
SET @Index=0
SET @OpenBalance = @PrevBalance
END
SET @dPeriodFrom = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
SET @dPeriodTo = (select top 1 T1.T_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
IF @Index=0
BEGIN
INSERT INTO [#GeneralLedger] select 0, @AcctCode, @AcctName,NULL,'','',NULL,'','',NULL,NULL,NULL
INSERT INTO [#GeneralLedger] select 1,'', CAST(DATEPART(DAY, @dPeriodFrom) as NVARCHAR(2)) + ' ' + DATENAME(MONTH, @dPeriodFrom) + ','+ DATENAME(YEAR, @dPeriodFrom)+' - ' + CAST(DATEPART(DAY, @dPeriodTo) as NVARCHAR(2)) + ' '+ DATENAME(MONTH, @dPeriodTo) + ','+ DATENAME(YEAR, @dPeriodTo) ,NULL,'','',NULL,'','Period Open Balance ' + @AcctCode + ' :',NULL,NULL, @PrevBalance
SET @Index =1
END
IF @Index = 1
BEGIN
BEGIN
SET @PrevBalance = @PrevBalance + IsNULL( @Debit,0.0) - IsNULL( @Credit,0.0)
IF @Debit < 0
BEGIN
SET @ValueTmp = ABS( @Debit)
SET @Debit = NULL
SET @Credit = IsNULL( @Credit,0) + @ValueTmp
END
IF @Credit < 0
BEGIN
SET @ValueTmp = ABS( @Credit)
SET @Credit = NULL
SET @Debit = IsNULL( @Debit,0) + @ValueTmp
END
IF @CreditAcc <> 'Y'
INSERT INTO [#GeneralLedger] select 3,'','', @RefDate , @TransTypeD , @TransType , @TransID , @Desc2 , @LineMemo , @Debit , @Credit , NULL
END
END
FETCH NEXT FROM First1
INTO @AcctCode , @AcctName ,
@RefDate , @TransType , @TransTypeD ,
@TransId , @Desc2 , @LineMemo , @Debit , @Credit , @CreditAcc
END
IF (SELECT COUNT(*) FROM [#GeneralLedger])>0
BEGIN
SET @Balance = @Balance + @PrevBalance
SET @dPeriod = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' + DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL , @PrevBalance - @OpenBalance
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total For Account ' + @AcctCode + ' :',NULL,NULL, @PrevBalance
END
CLOSE First1
DEALLOCATE First1
SELECT case when IsNULL(T0.AcctCode,'')='' then '' else T0.AcctCode + ' - ' end + T0.AcctName as [Acc Name/Period], T0.RefDate as [Trx Date], T0.TransType as [Trx Code], T0.TransTypeCode, T0.TransId as [SAP Jrn No] , T0.LineMemo as [Details], T0.Debit, T0.Credit, T0.Balance FROM [#GeneralLedger] T0
ORDER BY T0.IdInt
DROP TABLE [#GeneralLedger]
5 Comments
Balakumar Viswanathan
Hi Neetu,
I'm getting the following error while executing the above query
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '/'.
2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '/'.
3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '/'
Erley Peña
Thanks for sharing, but generates an error.
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '/'.
2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'SELECT'.
3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrec
Regards
Erley
Former Member
This query show good preview But it is showing error . Please look in to this query
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '/'.
2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'SELECT'.
3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrec
Regards,
Sudhir.J
Former Member
PLEASE USE THE FOLLWING CODE — ERROR REMOVED:
declare @sAccFrom as nvarchar(10)
declare @sAccTo as nvarchar(10)
declare @sPCFrom as nvarchar(10)
declare @sPCTo as nvarchar(10)
declare @dPostFromMax as datetime
declare @dPostToMax as datetime
declare @dPostFrom as datetime
declare @dPostTo as datetime
declare @dDocFrom as datetime
declare @dDocTo as datetime
declare @dTaxFrom as datetime
declare @dTaxTo as datetime
declare @dPeriodFrom as datetime
declare @dPeriodTo as datetime
declare @dPeriod as datetime
declare @sTmp as nchar(1)
declare @AcctCode as nvarchar(20)
declare @AcctCode_Tmp as nvarchar(20)
declare @AcctName as nvarchar(100)
declare @RefDate as datetime
declare @TransType as nvarchar(10)
declare @TransTypeD as nvarchar(4)
declare @TransId as int
declare @Desc2 as nvarchar(254)
declare @LineMemo as nvarchar(254)
declare @Debit as numeric(19,6)
declare @Credit as numeric(19,6)
declare @ValueTmp as numeric(19,6)
declare @DiscPrcnt as numeric(19,6)
declare @PrevBalance as numeric(19,6)
declare @PrevBalanceTmp as numeric(19,6)
declare @Balance as numeric(19,6)
declare @OpenBalance as numeric(19,6)
declare @GrandBalance as numeric(19,6)
declare @Index as int
declare @CreditAcc as nvarchar(1)
declare @PYear as int
declare @CurrPTmp as int
declare @CurrP as int
declare @PFrom as int
declare @PTo as int
/* SELECT FROM [dbo].[JDT1] T0 */
declare @FromAcct as nvarchar(20)
/* WHERE */
set @FromAcct = /* T0.Account */ '[%0]'
/* SELECT FROM [dbo].[JDT1] T1 */
declare @ToAcct as nvarchar(20)
/* WHERE */
set @ToAcct = /* T0.Account */ '[%1]'
CREATE TABLE [#GeneralLedger] (IdInt int PRIMARY KEY IDENTITY,
LineType int,
AcctCode nvarchar(20),
AcctName nvarchar(100),
RefDate datetime,
TransType nvarchar(4),
TransTypeCode nvarchar(10),
TransId int,
Desc2 nvarchar(254),
LineMemo nvarchar(254),
Debit numeric(19,6),
Credit numeric(19,6),
Balance numeric(19,6)
)
/* SELECT FROM [dbo].[JDT1] T0 */
declare @FromDate as datetime
/* WHERE */
set @FromDate = /* T0.RefDate */ '[%2]'
/* SELECT FROM [dbo].[JDT1] T1 */
declare @ToDate as datetime
/* WHERE */
set @ToDate = /* T0.RefDate */ '[%3]'
SET @dPostFromMax = GETDATE()
SET @dPostFromMax = @FromDate
SET @dPostToMax = GETDATE()
SET @dPostToMax = @ToDate
SET @Index=0
SET @PFrom = 0
SET @PYear = DATEPART(YEAR, @dPostFromMax)
SET @PFrom = (select top 1 T1.AbsEntry from OFPR T1 where DATEPART(YEAR,T1.F_RefDate)= @PYear AND DATEPART(MONTH,T1.F_RefDate)=DATEPART(MONTH, @dPostFromMax))
SET @PTo = (select top 1 T1.AbsEntry from OFPR T1 where DATEPART(YEAR,T1.T_RefDate)= @PYear AND DATEPART(MONTH,T1.T_RefDate)=DATEPART(MONTH, @dPostToMax))
DECLARE First1 CURSOR FOR
select T0.AcctCode, T0.AcctName,
T1.RefDate,T1.TransType,
case when T1.TransType=13 then 'IN'
when T1.TransType=14 then 'CN'
when T1.TransType=15 then 'DN'
when T1.TransType=16 then 'RE'
when T1.TransType=162 then 'MR'
when T1.TransType=18 then 'PU'
when T1.TransType=19 then 'PC'
when T1.TransType=20 then 'PD'
when T1.TransType=202 then 'PW'
when T1.TransType=21 then 'PR'
when T1.TransType=24 then 'RC'
when T1.TransType=30 then 'JE'
when T1.TransType=46 then 'PS'
when T1.TransType=58 then 'ST'
when T1.TransType=59 then 'SI'
when T1.TransType=60 then 'SO'
when T1.TransType=67 then 'IM'
else T1.TransType end,
T1.TransId,
'',
T1.LineMemo,
case when T1.Debit=0.0 then NULL else T1.Debit end,
case when T1.Credit=0.0 then NULL else T1.Credit end,
T0.LocManTran
from oact T0, jdt1 T1
where T1.TransType <> -3
AND (T1.Debit <> 0.0 or T1.Credit <> 0.0)
and T0.AcctCode = T1.Account
AND T1.Account >= @FromAcct
AND T1.Account <= @ToAcct
AND T1.RefDate >= @FromDate
AND T1.RefDate <= @ToDate
ORDER BY T0.AcctCode, T1.RefDate, T1.TransId
SET @AcctCode_Tmp=''
SET @PrevBalance=0.0
SET @Balance=0.0
SET @OpenBalance=0.0
SET @CurrP = @PFrom
OPEN First1
FETCH NEXT FROM First1
INTO @AcctCode, @AcctName,
@RefDate, @TransType, @TransTypeD,
@TransId, @Desc2, @LineMemo, @Debit, @Credit , @CreditAcc
WHILE @@Fetch_Status = 0
BEGIN
SET @CurrPTmp = (select top 1 AbsEntry from OFPR WHERE T_RefDate>=@RefDate and F_RefDate<=@RefDate )
IF ( @CurrP <> @CurrPTmp and @AcctCode = @AcctCode_Tmp)
BEGIN
SET @dPeriodFrom = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrPTmp)))
SET @dPeriodTo = (select top 1 T1.T_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrPTmp)))
IF (SELECT COUNT(*) FROM [#GeneralLedger]) > 0
BEGIN
SET @Balance= @Balance + IsNULL( @PrevBalance,0.0)
SET @dPeriod = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' + DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL, @PrevBalance - @OpenBalance
INSERT INTO [#GeneralLedger] select 1,'', CAST(DATEPART(DAY, @dPeriodFrom) as NVARCHAR(2))+' ' + DATENAME(MONTH, @dPeriodFrom) + ','+ DATENAME(YEAR, @dPeriodFrom) +' - ' + CAST(DATEPART(DAY, @dPeriodTo) as NVARCHAR(2))+' ' + DATENAME(MONTH, @dPeriodTo)+',' + DATENAME(YEAR, @dPeriodTo),NULL,'','',NULL,'','',NULL,NULL,NULL
SET @OpenBalance= IsNULL( @PrevBalance,0.0)
END
SET @CurrP = @CurrPTmp
END
IF @AcctCode <> @AcctCode_Tmp
BEGIN
IF (SELECT COUNT(*) FROM [#GeneralLedger])>0
BEGIN
SET @Balance= @Balance + IsNULL( @PrevBalance,0.0)
SET @dPeriod = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' + DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL, @PrevBalance- @OpenBalance
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total For Account ' + @AcctCode_Tmp+ ' :',NULL,NULL, @PrevBalance
END
SET @CurrP = @CurrPTmp
SET @PrevBalance=(SELECT IsNULL(SUM(IsNULL(T1.Debit,0.0)),0.0) - IsNULL(SUM(IsNULL(T1.Credit,0.0)),0.0) FROM oact T0, jdt1 T1 where T1.TransType <> -3 and T1.Account = T0.AcctCode AND T1.Account >= @FromAcct AND T1.Account <= @ToAcct AND T1.RefDate < @FromDate)
SET @AcctCode_Tmp = @AcctCode
SET @Index=0
SET @OpenBalance = @PrevBalance
END
SET @dPeriodFrom = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
SET @dPeriodTo = (select top 1 T1.T_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
IF @Index=0
BEGIN
INSERT INTO [#GeneralLedger] select 0, @AcctCode, @AcctName,NULL,'','',NULL,'','',NULL,NULL,NULL
INSERT INTO [#GeneralLedger] select 1,'', CAST(DATEPART(DAY, @dPeriodFrom) as NVARCHAR(2)) + ' ' + DATENAME(MONTH, @dPeriodFrom) + ','+ DATENAME(YEAR, @dPeriodFrom)+' - ' + CAST(DATEPART(DAY, @dPeriodTo) as NVARCHAR(2)) + ' '+ DATENAME(MONTH, @dPeriodTo) + ','+ DATENAME(YEAR, @dPeriodTo) ,NULL,'','',NULL,'','Period Open Balance ' + @AcctCode + ' :',NULL,NULL, @PrevBalance
SET @Index =1
END
IF @Index = 1
BEGIN
BEGIN
SET @PrevBalance = @PrevBalance + IsNULL( @Debit,0.0) - IsNULL( @Credit,0.0)
IF @Debit < 0
BEGIN
SET @ValueTmp = ABS( @Debit)
SET @Debit = NULL
SET @Credit = IsNULL( @Credit,0) + @ValueTmp
END
IF @Credit < 0
BEGIN
SET @ValueTmp = ABS( @Credit)
SET @Credit = NULL
SET @Debit = IsNULL( @Debit,0) + @ValueTmp
END
IF @CreditAcc <> 'Y'
INSERT INTO [#GeneralLedger] select 3,'','', @RefDate , @TransTypeD , @TransType , @TransID , @Desc2 , @LineMemo , @Debit , @Credit , NULL
END
END
FETCH NEXT FROM First1
INTO @AcctCode , @AcctName ,
@RefDate , @TransType , @TransTypeD ,
@TransId , @Desc2 , @LineMemo , @Debit , @Credit , @CreditAcc
END
IF (SELECT COUNT(*) FROM [#GeneralLedger])>0
BEGIN
SET @Balance = @Balance + @PrevBalance
SET @dPeriod = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' + DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL , @PrevBalance - @OpenBalance
INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total For Account ' + @AcctCode + ' :',NULL,NULL, @PrevBalance
END
CLOSE First1
DEALLOCATE First1
SELECT case when IsNULL(T0.AcctCode,'')='' then '' else T0.AcctCode + ' - ' end + T0.AcctName as [Acc Name/Period], T0.RefDate as [Trx Date], T0.TransType as [Trx Code], T0.TransTypeCode, T0.TransId as [SAP Jrn No] , T0.LineMemo as [Details], T0.Debit, T0.Credit, T0.Balance FROM [#GeneralLedger] T0
ORDER BY T0.IdInt
DROP TABLE [#GeneralLedger]
Former Member
Hi,
Can you Please add offset account column in the query.
Thanks.