Skip to Content
author's profile photo Former Member
Former Member

BP account balance query

Dear Experts I need BP account Balance query. Posting Date,Due Date,Trans. No.,Journal Voucher,Origin,Origin No.,Ref. 1,Ref. 2,Ref. 3,Offset Account,Details,Debit (LC),Credit (LC),Cumulative Balance (LC),Remarks [Example - A/P Invoice remark]

Screen shot attcahed for reference

Untitled.png (27.8 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Mar 05, 2015 at 01:47 PM

    Hi

    Try this query:

    SELECT T0.[RefDate],T0.[TransId],T0.[BaseRef],T1.[FormatCode],T0.[LineMemo],T0.[ShortName] as Name, T0.[Debit],T0.[Credit],T0.[Ref1], T0.[Ref2], T0.[Ref3Line],T0.[DueDate], T0.[TaxDate]

    FROM JDT1 T0 INNER JOIN OACT T1 ON T0.Account = T1.AcctCode INNER JOIN OJDT T2 ON T0.TransId = T2.TransId WHERE T2.[RefDate] between [%0] and [%1] and T0.[ShortName] = [%3]

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

    • Try this:

      SELECT T0.[RefDate],T0.[TransId],T0.[BaseRef],T1.[FormatCode],T0.[LineMemo],T0.[ShortName] as Name, T0.[Debit],T0.[Credit],T0.[Ref1], T0.[Ref2], T0.[Ref3Line],T0.[DueDate], T0.[TaxDate],T3.[Comments]

      FROM JDT1 T0 INNER JOIN OACT T1 ON T0.Account = T1.AcctCode INNER JOIN OJDT T2 ON T0.TransId = T2.TransId inner join OPCH T3 on T3.transID= T2.transID WHERE T2.[RefDate] between [%0] and [%1] and T0.[ShortName] = [%3]

  • Posted on Mar 05, 2015 at 10:26 AM

    Hi Rajeesh.

    By standard when you drill down on the Balance of the Business partner you will get the desired report your are looking for...

    Why you need a separate query report for the same..

    Regards

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

    • TRy this

      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]

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.