[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

AP-Reports-Transaction, column a.crdate does not exist.

Hi, I get the following error when trying to execute:
AP/AR -> Reports -> Transactions
I want to get a report / account statement for all vendors (preferably 
one report sorted by vendor).
There seem to be a column missing in my database.  How can I fix this?


SELECT a.id, a.invnumber, a.ordnumber, a.transdate, a.crdate,
a.duedate, a.netamount, a.amount,
(a.amount - pd.due) AS paid,
a.invoice, a.datepaid, a.terms, a.notes,
a.shipvia, a.shippingpoint, ee.name AS employee,
vce.name, vc.meta_number,
vc.entity_id, a.till, me.name AS manager, a.curr,
ex.sell AS exchangerate,
d.description AS department,
a.ponumber, as_array(p.projectnumber) as ac_projects,
as_array(ip.projectnumber) as inv_projects

FROM ap a
JOIN entity_credit_account vc ON (a.entity_credit_account = vc.id)
JOIN acc_trans ac ON (a.id = ac.trans_id)
JOIN chart c ON (c.id = ac.chart_id)
LEFT JOIN (SELECT acc_trans.trans_id,
sum(CASE WHEN 'ap' = 'ap' THEN amount
WHEN 'ap' = 'ar'
THEN amount * -1
END) AS due
FROM acc_trans
JOIN account coa ON (coa.id = acc_trans.chart_id)
JOIN account_link al ON (al.account_id = coa.id)
WHERE ((al.description = 'AP' AND 'ap' = 'ap')
OR (al.description = 'AR' AND 'ap' = 'ar'))
AND (approved IS TRUE)
GROUP BY acc_trans.trans_id) pd ON (a.id = pd.trans_id)
LEFT JOIN entity_employee e ON (a.person_id = e.entity_id)
LEFT JOIN entity_employee m ON (e.manager_id = m.entity_id)
LEFT JOIN entity ee ON (e.entity_id = ee.id)
LEFT JOIN entity me ON (m.entity_id = me.id)
JOIN entity vce ON (vc.entity_id = vce.id)
LEFT JOIN exchangerate ex ON (ex.curr = a.curr
AND ex.transdate = a.transdate)
LEFT JOIN department d ON (a.department_id = d.id)
LEFT JOIN invoice i ON (i.trans_id = a.id)
LEFT JOIN project ip ON (i.project_id = ip.id)
LEFT JOIN project p ON ac.project_id = p.id WHERE (FALSE OR a.approved) 
AND 1 = 1 AND a.transdate >= '01.01.2013' AND a.transdate <= '31.12.2013'

GROUP BY a.id, a.invnumber, a.ordnumber, a.transdate,
a.duedate, a.netamount, a.amount,
a.invoice, a.datepaid, a.terms, a.notes,
a.shipvia, a.shippingpoint, ee.name ,
vce.name, vc.meta_number, a.amount, pd.due,
vc.entity_id, a.till, me.name, a.curr,
ex.sell, a.ponumber,
ERROR: column a.crdate does not exist
LINE 2: ...ELECT a.id, a.invnumber, a.ordnumber, a.transdate, a.crdate,

Vennlig hilsen / Best regards      |\     ___,,--,        _
Arne Hanssen, Senja, Norway        /,`--''        \-,,__,'/
[ Linux counter #102028 @ ]       |,4   ) )_    ) /~-----'
[ http://linuxcounter.net ]------'---^~(_/-_)--(_/_)-------

WatchGuard Dimension instantly turns raw network data into actionable 
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
Ledger-smb-users mailing list