On Fri, Dec 13, 2013 at 2:30 AM, herman vierendeels <
[email protected]> wrote:
> I would like to add a view to track ap for which payments are not ok
> (not paid, paid too little, paid too much)
>
Ideally if we are going to go this way, it might make sense to design a
view which can be used by our stored procedures.
>
> Is this ok?
>
> Could someone other check validity of view-logic:
>
> View definition:
> WITH cte(ap_id, ac_amount) AS (
> SELECT ap.id, sum(ac.amount) AS sum
> FROM ap ap
> JOIN acc_trans ac ON ac.trans_id = ap.id
> JOIN account cnt ON cnt.id = ac.chart_id
> JOIN account_link al ON al.account_id = cnt.id
> WHERE al.description = 'AP'::text
> GROUP BY ap.id
> HAVING sum(ac.amount) <> 0::numeric
> )
> SELECT cte.ac_amount, e.name, ap.id, ap.invnumber, ap.transdate,
> ap.taxincluded, ap.amount, ap.netamount, ap.duedate, ap.invoice,
> ap.ordnumber, ap.curr, ap.notes, ap.person_id, ap.till, ap.quonumber,
> ap.intnotes, ap.shipvia, ap.language_code, ap.ponumber,
> ap.shippingpoint, ap.on_hold, ap.approved, ap.reverse, ap.terms,
> ap.description, ap.force_closed, ap.crdate
> FROM ap ap, entity_credit_account eca, entity e, cte cte
> WHERE ap.id = cte.ap_id AND ap.entity_credit_account = eca.id AND
> eca.entity_id = e.id;
>
Based on the rewrite below, I don't see anything wrong with your logic. I
think your view definition would work for businesses with a relatively
small number of AP transactions. I think you will run into issues with
more AP centered workflows (such as financial services businesses), so I
would probably prefer to optimize it (see below). The optimizations below
are just a matter of transforming what you have written.
CTE's are optimization fences, so you effectively have two scans on ap,
which may be a problem for larger data sets (for example some of our
financial services users). You don't get anything by putting that logic
in the CTE, and in fact you lose optimization possibilities. So if you are
going to go this way, unfold your CTE and put it in the main query. I
would also suggest that explicit joins are likely to be easier to manage
down the road. Also, the join on account is unnecessary since you are
joining all the way across on the same values to account_link. So
rewriting this you have:
SELECT sum(ac.amount) as balance, e.name, ap.id, ap.invnumber,
ap.transdate,
ap.taxincluded, ap.amount, ap.netamount, ap.duedate,
ap.invoice,
ap.ordnumber, ap.curr, ap.notes, ap.person_id, ap.till,
ap.quonumber,
ap.intnotes, ap.shipvia, ap.language_code, ap.ponumber,
ap.shippingpoint, ap.on_hold, ap.approved, ap.reverse,
ap.terms,
ap.description, ap.force_closed, ap.crdate
FROM ap
JOIN entity_credit_account eca ON ap.entity_credit_account = eca.id
JOIN entity e ON eca.entity_id = e.id
JOIN acc_trans ac ON ac.trans_id = ap.id
JOIN account_link al ON al.description = 'AP'::text AND al.account_id =
ac.chart_id
GROUP BY e.name, ap.id, ap.invnumber, ap.transdate,
ap.taxincluded, ap.amount, ap.netamount, ap.duedate, ap.invoice,
ap.ordnumber, ap.curr, ap.notes, ap.person_id, ap.till, ap.quonumber,
ap.intnotes, ap.shipvia, ap.language_code, ap.ponumber,
ap.shippingpoint, ap.on_hold, ap.approved, ap.reverse, ap.terms,
ap.description, ap.force_closed, ap.crdate
HAVING sum(ac.amount) <> 0;
This is, essentially, the same logic and it does away with the extra scan
through ap, and any scans on account. That should show the balance due in
the first column.
Also it is worth noting that due to SQL-Ledger rounding errors, we set the
threshold at $0.005 to pay (for those migrating there) so you might end up
with some small amounts on migrated databases. If this is a problem we'd
change the having statement to:
HAVING abs(sum(ac.amount)) > 0.005
To be honest, I don't know whether we want to do that (setting the
threshold to 0.005) out of the box or implement that as an add-on for those
who need it (my vote is probably for the latter because these sorts of
efforts to cover up bugs of other programs in the past may cover up bugs in
the present. So I would probably stick with the main rewrite and the
original HAVING statement. It's worth knowing though.
One question I have is whether we should put such views in a dedicated
reporting schema (maybe lsmb_reporting?). If the goal is external access
from spreadsheets, this would make this easier to manage and maybe provide
dedicated space for others for reports too.
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
