Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(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]

  • No labels

5 Comments

  1. Hi Neetu,

    I'm getting the following error while executing the above query

     [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '/' 

    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 '/'

  2. 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

  3. 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

  4. 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]

  5. Former Member

    Hi,

    Can you Please add offset account column in the query.

    Thanks.