Re: trunk new view ap_paid_nok

[herman vierendeels <..hidden..>]

thank you, Chris , for the explanation
will commit this for the moment as possible sql-definition

2013/12/13 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
>
> ------------------------------------------------------------------------------
> 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
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>