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