on 06-03-2011 2:45 PM
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
User | Count |
---|---|
110 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.