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

trunk new view ap_paid_nok

I would like to add a view to track ap for which payments are not ok
(not paid, paid too little, paid too much)

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;