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

Query for a sepcific GL account

I am wanting to try and create a query that looks for a specific GL account and returns all the documents that are associated with it. Really similar to the AR aging report, but for just a certain GL account. This way we can see the balance and then the documents associated to it then be able to do some simple aging off of that.. I have some other fields I need to link to it, but I cant get a simple start. Can anyone help me with a start?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jun 03, 2011 at 01:56 PM

    HI

    You can do the same in Standard B1 right...

    Goto Financials->Chart of accounts.

    Click any G/L account and you will see the balance appear on the left side of the "G/L Account Details"....

    If you click the link of the 'Balance' it will show the document associated with it.....

    It will be too much complicated when you create on your own....

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Dayton,

      You can start maybe from something like this:

      select T0.Account 'Account', T0.sysdeb 'Debit GBP', T0.syscred 'Credit GBP',
      case T0.transtype 
      when '13' then 'AR Invoice'
      when '14' then 'AR Credit Note'
      when '24' then 'Incoming Payment'
      else 'Other'
      end 'Type',
      T0.linememo 'Memo',
      T0.Ref1,
      CONVERT(VARCHAR(10), T0.refdate, 103)'Posting Date' ,
      CONVERT(VARCHAR(10), T0.duedate, 103) 'Due Date',
      CONVERT(VARCHAR(10), T0.taxdate, 103) 'Doc Date' ,
      
      CASE when DATEDIFF(dd,T0.taxdate,current_timestamp) < 31 
      then case
      when T0.syscred <> 0 then T0.syscred * - 1
      else T0.sysdeb
      end
      end "0-30 days",
      
      CASE when (datediff(dd,T0.taxdate,current_timestamp) > 30 
      and datediff(dd,T0.taxdate,current_timestamp)< 61) 
      then case
      when T0.syscred <> 0 then T0.syscred * - 1
      else T0.sysdeb
      end 
      end "31 to 60 days",
      
      CASE when (datediff(dd,T0.taxdate,current_timestamp) > 60 
      and datediff(dd,T0.taxdate,current_timestamp)< 91) 
      then case
      when T0.syscred <> 0 then T0.syscred * - 1
      else T0.SYSDeb
      end 
      end "61 to 90 days",
      
      CASE when (datediff(dd,T0.TaxDate,current_timestamp) > 91 
      and datediff(dd,T0.TaxDate,current_timestamp)< 120) 
      then case
      when T0.SYSCred <> 0 then T0.SYSCred * - 1
      else T0.SYSDeb
      end 
      end "91 to 120 days",
      
      CASE
      when DATEDIFF(dd,T0.TaxDate,current_timestamp) > 121 
      then case
      when T0.SYSCred <> 0 then T0.SYSCred * - 1
      else T0.SYSDeb
      end
      end "121+ days"
      
      from JDT1 T0
       where T0.Account = [%1]
      

      It was originally an ageing report for BP so you might want to amend it to show the fields you need.

      Regards,

      Nat

  • author's profile photo Former Member
    Former Member
    Posted on Jun 03, 2011 at 03:40 PM

    Hi,

    You may check this: Detailed Aging Report from SAP B1 v2007A

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

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.