sql-ledger-users

[SL] General Ledger Transaction Listing

[David J <ja...@in...>]

Hi List

We're trying to produce a report that would probably be
called "GL Transaction list, sorted by account" or similar. 
Sometimes simply called a "General Ledger listing".

Company Auditors (and tax auditors) usually ask for this
report.  All GL transactions, within a date range, sorted by
account code (or range), and then sorted by date.  Presented
nicely, topped and tailed, and printed.

Most accounting systems I've ever used have a way of
producing such a list, for a range of accounts, and/or for a
range of dates.  Importantly, ALL the details of each
transaction need to be listed, ie Date, Source, Reference,
Memo, Description, Notes, Amount, and account balance.
Such a report is fairly standard for any accountant or
auditor seeking to confirm entries in the GL system. But we
can't quite find how to do this in SQL-L.

Can get close by using the Trial Balance (by date range),
then click at one account at a time.
That produces a list of transactions for a single account,
but also there's no full details, memos notes, etc. Have
also examined the SQL-L manual without finding how to do
this.

Has anybody else mastered this ?

TIA's

-David J

Re: [SL] General Ledger Transaction Listing

[Dr E. W L. <el...@li...>]

The postgreSQL log is your friend. You can use something like

SELECT  c.accno, c.description, ac.transdate, g.id,
    round(ac.amount::numeric,2) as Amount,
    g.reference,
    g.description, substr(g.notes,1,55) as Notes,
    ac.memo

FROM gl g
JOIN acc_trans ac ON (g.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
LEFT JOIN department d ON (d.id = g.department_id)
WHERE 1 = 1
    AND ac.transdate >= '2005-03-01'
    AND ac.transdate <= '2006-02-28'

UNION ALL

SELECT c.accno,  c.description, ac.transdate, a.id,
    round(ac.amount::numeric,2) as Amount,
    a.invnumber,
    ct.name, substr(a.notes,1,55) as Notes,
   ac.memo
FROM ar a
JOIN acc_trans ac ON (a.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
JOIN customer ct ON (a.customer_id = ct.id)
LEFT JOIN department d ON (d.id = a.department_id)
WHERE 1 = 1
   AND ac.transdate >= '2005-03-01'
   AND ac.transdate <= '2006-02-28'

UNION ALL

SELECT c.accno,  c.description, ac.transdate,  a.id,
    round(ac.amount::numeric,2) as Amount,
    a.invnumber,
    ct.name, substr(a.notes,1,55) as Notes,
   ac.memo
FROM ap a
JOIN acc_trans ac ON (a.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
JOIN vendor ct ON (a.vendor_id = ct.id)
LEFT JOIN department d ON (d.id = a.department_id)
WHERE 1 = 1
   AND ac.transdate >= '2005-03-01'
   AND ac.transdate <= '2006-02-28'

ORDER BY 1, 3, 4;

I was going to hack me a perl script this summer using a module
from the CPAN archive to generate a proper spreadsheet with formatted
columns.

greetings, el

David J wrote:
> Hi List
> 
> We're trying to produce a report that would probably be
> called "GL Transaction list, sorted by account" or similar. 
> Sometimes simply called a "General Ledger listing".
> 
> Company Auditors (and tax auditors) usually ask for this
> report.  All GL transactions, within a date range, sorted by
> account code (or range), and then sorted by date.  Presented
> nicely, topped and tailed, and printed.
> 
> Most accounting systems I've ever used have a way of
> producing such a list, for a range of accounts, and/or for a
> range of dates.  Importantly, ALL the details of each
> transaction need to be listed, ie Date, Source, Reference,
> Memo, Description, Notes, Amount, and account balance.
> Such a report is fairly standard for any accountant or
> auditor seeking to confirm entries in the GL system. But we
> can't quite find how to do this in SQL-L.
> 
> Can get close by using the Trial Balance (by date range),
> then click at one account at a time.
> That produces a list of transactions for a single account,
> but also there's no full details, memos notes, etc. Have
> also examined the SQL-L manual without finding how to do
> this.
> 
> Has anybody else mastered this ?
> 
> TIA's
> 
> -David J
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> sql-ledger-users mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users

Re: [SL] General Ledger Transaction Listing

[Max S. I. I. <ma...@im...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you looked at the General Ledger Report? It's "Reports" under the 
"General Ledger" menu; you can select date ranges, types of accounts, and 
details to include.


Regards,

Max

- ----- Original Message ----- 
From: "David J" <ja...@in...>
To: <sql...@li...>
Sent: Thursday, November 16, 2006 1:47 AM
Subject: [SL] General Ledger Transaction Listing


> Hi List
>
> We're trying to produce a report that would probably be
> called "GL Transaction list, sorted by account" or similar.
> Sometimes simply called a "General Ledger listing".
>
> Company Auditors (and tax auditors) usually ask for this
> report.  All GL transactions, within a date range, sorted by
> account code (or range), and then sorted by date.  Presented
> nicely, topped and tailed, and printed.
>
> Most accounting systems I've ever used have a way of
> producing such a list, for a range of accounts, and/or for a
> range of dates.  Importantly, ALL the details of each
> transaction need to be listed, ie Date, Source, Reference,
> Memo, Description, Notes, Amount, and account balance.
> Such a report is fairly standard for any accountant or
> auditor seeking to confirm entries in the GL system. But we
> can't quite find how to do this in SQL-L.
>
> Can get close by using the Trial Balance (by date range),
> then click at one account at a time.
> That produces a list of transactions for a single account,
> but also there's no full details, memos notes, etc. Have
> also examined the SQL-L manual without finding how to do
> this.
>
> Has anybody else mastered this ?
>
> TIA's
>
> -David J
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share 
> your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> sql-ledger-users mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users
>
> 


-----BEGIN PGP SIGNATURE-----
Version: PGP Desktop 9.0.6 (Build 6060)

iQA/AwUBRVymMqo/0JNz1jAsEQJZGgCg3lld18khOzHThoGRunEt7saJTDgAn2FQ
OHT3HAbc+vwRWBW7e9ck782K
=jj8d
-----END PGP SIGNATURE-----

Re: [SL] General Ledger Transaction Listing

[R P. <arc...@mt...>]

The only issue i see is sorting the GL report on both account AND on date.
You can only sort on account OR date with the current GL report.

Options:
You could easily copy the GL report into a spreadsheet and then do the double sort.
Or use a direct SQL query via psql like Eberhard's example.

According to the website, the new version will have more report options...maybe the ability to do this report sort format is part of that.

Other than that you can make a feature request at the bottom of this web page
http://www.sql-ledger.org/cgi-bin/nav.pl?page=ahead.html&title=What's%20Ahead

Cheers,
Roy

On Thursday 16 November 2006 11:55 am, Max Schneider, Image Innovation wrote:
> Have you looked at the General Ledger Report? It's "Reports" under the
> "General Ledger" menu; you can select date ranges, types of accounts, and
> details to include.
> 
> 
> Regards,
> 
> Max
> 
> ----- Original Message -----
> From: "David J" <ja...@in...>
> To: <sql...@li...>
> Sent: Thursday, November 16, 2006 1:47 AM
> Subject: [SL] General Ledger Transaction Listing
> 
> 
> > Hi List
> >
> > We're trying to produce a report that would probably be
> > called "GL Transaction list, sorted by account" or similar.
> > Sometimes simply called a "General Ledger listing".
> >
> > Company Auditors (and tax auditors) usually ask for this
> > report.  All GL transactions, within a date range, sorted by
> > account code (or range), and then sorted by date.  Presented
> > nicely, topped and tailed, and printed.
> >
> > Most accounting systems I've ever used have a way of
> > producing such a list, for a range of accounts, and/or for a
> > range of dates.  Importantly, ALL the details of each
> > transaction need to be listed, ie Date, Source, Reference,
> > Memo, Description, Notes, Amount, and account balance.
> > Such a report is fairly standard for any accountant or
> > auditor seeking to confirm entries in the GL system. But we
> > can't quite find how to do this in SQL-L.
> >
> > Can get close by using the Trial Balance (by date range),
> > then click at one account at a time.
> > That produces a list of transactions for a single account,
> > but also there's no full details, memos notes, etc. Have
> > also examined the SQL-L manual without finding how to do
> > this.
> >
> > Has anybody else mastered this ?
> >
> > TIA's
> >

[SL] Adding notes without reposting

[Kevin B. <kb...@fr...>]

Hi,

Does anyone have any ideas RE how to add to the internal notes without 
reposting the invoice?

I would rather not repost any transactions as this may affect how they 
are listed in the reconciliation screen.

Thanks,

Kevin

Re: [SL] Adding notes without reposting

[Wallace R. C. <wrc...@gm...>]

On 11/20/06, Kevin Bailey <kb...@fr...> wrote:
> Hi,
>
> Does anyone have any ideas RE how to add to the internal notes without
> reposting the invoice?
>
> I would rather not repost any transactions as this may affect how they
> are listed in the reconciliation screen.

Well...

I suppose you could sniff around in the database a bit, figure out
which table contains that information, & update it ("update
relevant_table set notes='notes go here' where key=1234567...").  Of
course, before doing any of that, you'd want to shutdown SL access &
backup the DB... just in case you didn't get it right. :-)

-- 
Best Regards,
Wallace

Re: [SL] Adding notes without reposting

[C. D. H. <Du...@Du...>]

Wallace Roberts Consulting wrote:
> On 11/20/06, Kevin Bailey <kb...@fr...> wrote:
>   
>> Hi,
>>
>> Does anyone have any ideas RE how to add to the internal notes without
>> reposting the invoice?
>>
>> I would rather not repost any transactions as this may affect how they
>> are listed in the reconciliation screen.
>>     
>
> Well...
>
> I suppose you could sniff around in the database a bit, figure out
> which table contains that information, & update it ("update
> relevant_table set notes='notes go here' where key=1234567...").  Of
> course, before doing any of that, you'd want to shutdown SL access &
> backup the DB... just in case you didn't get it right. :-)
>
>   
Not that I condone doing it, but the fields in question here are 
AR.NOTES and AR.INTNOTES.  We add notes to our INTNOTES field to record 
payment history.  Each time a payment is received a trigger fires that 
records the date, time, user amount paid and % paid in the internal 
notes field.  Perhaps you could build a separate table with a trigger on 
it - each time a record was entered it could update & append your note 
to the corresponding invoice record. 

Dunc

Re: [SL] Adding notes without reposting

[Stroller <lin...@my...>]

On 20 Nov 2006, at 11:31, Kevin Bailey wrote:
> ...
> Does anyone have any ideas RE how to add to the internal notes without
> reposting the invoice?

Adding the notes & emailing the invoice to yourself seems to work.

Of course this also adds the email content to the internal notes  
field, so it may be undesirable, but this seems like a useful way of  
adding ITEMNOTES to an already-posted invoice, too.

Stroller.