Invoice open balance

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.

This modification adds the invoice open balance to the invoice format so that the customer can see how much they owe, or have paid, against a particular invoice.

This may not work for everyone as it requires the "payment"/receipt to be allocated against the invoice.

  • Re-define the view
replace view C_INVOICE_HEADER_V AS 
  SELECT i.ad_client_id, i.ad_org_id, i.isactive, i.created, i.createdby, i.updated, i.updatedby, 'en_US'::character varying AS ad_language, i.c_invoice_id, i.issotrx, i.documentno, i.docstatus, i.c_doctype_id, i.c_bpartner_id, bp.value AS bpvalue, bp.taxid AS bptaxid, bp.naics, bp.duns, oi.c_location_id AS org_location_id, oi.taxid, dt.printname AS documenttype, dt.documentnote AS documenttypenote, i.c_order_id, i.salesrep_id, COALESCE(ubp.name, u.name) AS salesrep_name, i.dateinvoiced, bpg.greeting AS bpgreeting, bp.name, bp.name2, bpcg.greeting AS bpcontactgreeting, bpc.title, bpc.phone, NULLIF(bpc.name::text, bp.name::text) AS contactname, bpl.c_location_id, bp.referenceno, l.postal::text || l.postal_add::text AS postal, i.description, i.poreference, i.dateordered, i.c_currency_id, pt.name AS paymentterm, pt.documentnote AS paymenttermnote, i.c_charge_id, i.chargeamt, i.totallines, i.grandtotal, i.grandtotal AS amtinwords, i.m_pricelist_id, i.c_campaign_id, i.c_project_id, i.c_activity_id, i.ispaid, invoiceopen(i.c_invoice_id, i.c_invoicepayschedule_id) AS openamt
  FROM c_invoice_v i
  JOIN c_doctype dt ON i.c_doctype_id = dt.c_doctype_id
  JOIN c_paymentterm pt ON i.c_paymentterm_id = pt.c_paymentterm_id
  JOIN c_bpartner bp ON i.c_bpartner_id = bp.c_bpartner_id
  LEFT JOIN c_greeting bpg ON bp.c_greeting_id = bpg.c_greeting_id
  JOIN c_bpartner_location bpl ON i.c_bpartner_location_id = bpl.c_bpartner_location_id
  JOIN c_location l ON bpl.c_location_id = l.c_location_id
  LEFT JOIN ad_user bpc ON i.ad_user_id = bpc.ad_user_id
  LEFT JOIN c_greeting bpcg ON bpc.c_greeting_id = bpcg.c_greeting_id
  JOIN ad_orginfo oi ON i.ad_org_id = oi.ad_org_id
  LEFT JOIN ad_user u ON i.salesrep_id = u.ad_user_id
  LEFT JOIN c_bpartner ubp ON u.c_bpartner_id = ubp.c_bpartner_id
  LEFT JOIN c_invoicepayschedule ips ON i.c_invoicepayschedule_id = ips.c_invoicepayschedule_id;
  • import the view fields
  • Add the field to the report
  • Result
Open invoice.png