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

Query For General Ledger

Posted on Jul 17, 2012 at 03:22 AM 2.9k 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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

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

    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 a comment
    10|10000 characters needed characters exceeded

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

    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 a 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)
    Comment on This Answer

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.