Invoice open balance
From ADempiere
This Wiki is read-only for reference purposes to avoid broken links.
- 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