cancel
Showing results for 
Search instead for 
Did you mean: 

Query for a sepcific GL account

Former Member
0 Kudos

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?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

You may check this:

Thanks,

Gordon

Former Member
0 Kudos

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

Former Member
0 Kudos

I know I can get all of that info from the standard report, but we are requiring a handful of other columns that need to be added to this. So I was hoping to be able to create a query that can be run daily to show this account and the activity. Just like the AR Aging... we had to create our own version so that we could place additional columns of information to it. I was hoping this query has been done before. The request from here is to make it look just like our AR aging report but for only the GL account code 41101-001.

Former Member
0 Kudos

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