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

Query For General Ledger

Posted on Jul 17, 2012 at 03:22 AM 2k Views

Hi Experts,

I want the query for general Ledger

Same as Reports------> Financial-----> Accounting---------> General Ledger

I want the query to display the above report


Any Help Would Be Greatly Appreciated

Thanks

Shafi

SAP Business One
Add comment
10|10000 characters needed characters exceeded

2 Answers

  • Best Answer
    author's profile photo Kennedy T
    Kennedy T
    Posted on Jul 17, 2012 at 06:13 AM

    Hi Shafi,

    Try This

    DECLARE @D1 DATETIME

    DECLARE @D2 DATETIME

    SET @D1= '2012-04-01 00:00:00.000'

    SET @D2= '2012-04-01 00:00:00.000'

    select

    T0.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,

    T0.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' , T1.[ContraAct] 'OffsetAcct',

    Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ),

    T1.Debit, T1.Credit

    from OJDT T0

    inner join JDT1 T1 on T0.TransId =T1.TransId

    left outer Join OACT T2 ON T2.AcctCode=T1.ContraAct

    left outer join OCRD t3 on t3.CardCode=T1.ContraAct

    Where T0.RefDate>=@d1 and T0.RefDate<=@d2

    order by T0.TransId ,t1.Line_ID

    Regards

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Jimmy Michael
    Jimmy Michael
    Posted on Jul 17, 2012 at 03:29 AM

    Hi Shafi,

    Why don't you use general leddger report ?

    If you want the same report with the GL report, , you can use G/L report

    JM

    Add comment
    10|10000 characters needed characters exceeded

    • Jimmy Michael Former Member
      Jul 17, 2012 at 05:57 AM

      Hi Shafi,

      Me and Rahul have proposed that it would be better to use custom report. However it seems that you need to check the query generator.

      You may try this query:

      SELECT tr1.account[Acct Code], tr3.AcctName[Acct Name], tr1.RefDate[Posting Date], tr1.BaseRef[Doc. No.], tr1.transid[Trans. No.],

      tr1.linememo[Remarks], tr1.ContraAct[Offset Acct], (tr1.debit), (tr1.credit)

      FROM jdt1 tr1 inner join oact tr3 on tr3.acctcode = tr1.account order by line_id

      Here is the screen shot of result:

      Good luck

      JM

      query.png (38.4 kB)