Re: trunk new view ap_paid_nok

[Chris Travers <..hidden..>]

On Fri, Dec 13, 2013 at 2:30 AM, herman vierendeels <..hidden..> 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