on 02-20-2021 7:10 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for replay it's not working
look bellow thread:some urgency
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Kindly any one help me
travkin ankit.chauhan1 abdul.mannan6 kennedy.t kothandaraman.nagarajan c436ae948d684935a91fce8b976e5aa7 andrea.bassil unnikrishnan.balan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.