Skip to Content

General ledger Query with Commulative Balance Required

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Feb 23 at 10:20 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 24 at 06:03 AM

    Michelle Crapo

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.