cancel
Showing results for 
Search instead for 
Did you mean: 

General ledger Query with Commulative Balance Required

former_member188586
Active Contributor
0 Kudos

Deal All

Thank for Advance Help

i need to

General ledger Query with Commutative Balance Required bellow Query

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]

--Ramudu

mmcisme1
Active Contributor
0 Kudos

I am not really that familiar with the general ledger. There are a lot of different answers though. If you do a search I'm sure you can find what you're looking for. I also have to believe a report would already exist for this.

Anyway take a look at this answer - perhaps it will help.

https://answers.sap.com/questions/8411213/query-for-a-sepcific-gl-account.html

I'm writting a comment so now one thinks that this one has been answered.

Accepted Solutions (0)

Answers (4)

Answers (4)

LoHa
Active Contributor
0 Kudos

Dear AndakondaRamudu,

you could try something like this;

SELECT
Top 100 CardCode
, CardName
, DocNum
, DocTotal
, CONVERT(VARCHAR(20),SUM(DocTotal) OVER (ORDER BY DocNum)) as [kumulativ]
FROM OINV
ORDER BY DocNum ASC

Regards

former_member188586
Active Contributor
0 Kudos

Thanks for replay it's not working

look bellow thread:some urgency

https://answers.sap.com/questions/13359314/ar-invoice-total-tax-igst-cgst-sgst-cess-total-tax.html?c...

Abdul
Active Contributor
0 Kudos

Dear

I suggest to caculate cummulatie balance in crystal report, its much easier all you need is a formula debit-credit and insert this formula in a running total

former_member188586
Active Contributor
0 Kudos

c436ae948d684935a91fce8b976e5aa7

Thanks for your Quick Replay, and which you provided link is not met my requirement

i want insert Commutative Balance as per Row level

--Ramudu

mmcisme1
Active Contributor

Try this search:

sap query Cumulative Balance (LC) for general ledger per row

There are a lot of links. I know it's a lot to look into, but I can't really give you an answer based upon my knowledge. There are a lot of answers that "seem" to work out for your issue. Or at least point you in the right direction. I like this link but it might have nothing to do with your question. I'm putting this as a comment and not an answer as I'm not giving you the exact answer, just a way to get to it.

https://answers.sap.com/questions/7009168/cumulative-balance---which-table.html

former_member188586
Active Contributor
0 Kudos

Kindly any one help me

travkin ankit.chauhan1 abdul.mannan6 kennedy.t kothandaraman.nagarajan c436ae948d684935a91fce8b976e5aa7 andrea.bassil unnikrishnan.balan