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
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users
-------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/sql-ledger-users
