Fixed bug 32216: Cash VAT tab with prepaid invoice
authorVíctor Martínez Romanos <victor.martinez@openbravo.com>
Fri, 12 Feb 2016 15:13:04 +0100
changeset 28607 5435ecfe6c1f
parent 28606 c400203ec54f
child 28608 8d06deadd164
Fixed bug 32216: Cash VAT tab with prepaid invoice

The C_INVOICETAX_CASHVAT_V view, which populates the Sales/Purchase Invoice | Cash VAT tab, has been rewritten to properly manage the scenarios of prepaid invoices where the invoice amount is lower than the original order, either because of a partial invoice or because of a cheaper invoice.

The view has been split in 2 queries merged with an union all to improve the performance:
1. The first one searches the Cash VAT records linked to a payment. In this part we only filter by the records linked to the invoice's FPS, which fixes the scenarios raised by the issue.
2. The second one searches the Cash VAT records linked to a manual cash vat settlement. In this part there is no need to join with the payment tables, so the global performance is better.
src-db/database/model/views/C_INVOICETAX_CASHVAT_V.xml
--- a/src-db/database/model/views/C_INVOICETAX_CASHVAT_V.xml	Tue Feb 16 10:15:42 2016 +0100
+++ b/src-db/database/model/views/C_INVOICETAX_CASHVAT_V.xml	Fri Feb 12 15:13:04 2016 +0100
@@ -1,4 +1,4 @@
 <?xml version="1.0"?>
   <database name="VIEW C_INVOICETAX_CASHVAT_V">
-    <view name="C_INVOICETAX_CASHVAT_V"><![CDATA[SELECT itcv.c_invoicetax_cashvat_id AS c_invoicetax_cashvat_v_id, itcv.ad_client_id, itcv.ad_org_id, itcv.created, itcv.createdby, itcv.updated, itcv.updatedby, itcv.isactive, fp.paymentdate, itcv.percentage, itcv.taxamt, itcv.taxbaseamt, fpsd.iscanceled, fp.fin_payment_id, fpd.fin_payment_detail_id, fp.isreceipt, fp.c_bpartner_id, fp.fin_paymentmethod_id, fp.status, fp.amount, fp.c_currency_id, itcv.c_invoicetax_id, itcv.ismanualsettlement FROM c_invoicetax_cashvat itcv LEFT JOIN fin_payment_detail fpd ON fpd.fin_payment_detail_id = itcv.fin_payment_detail_id LEFT JOIN fin_payment fp ON fp.fin_payment_id = fpd.fin_payment_id LEFT JOIN fin_payment_scheduledetail fpsd ON fpd.fin_payment_detail_id = fpsd.fin_payment_detail_id]]></view>
+    <view name="C_INVOICETAX_CASHVAT_V"><![CDATA[SELECT itcv.c_invoicetax_cashvat_id AS c_invoicetax_cashvat_v_id, itcv.ad_client_id, itcv.ad_org_id, itcv.created, itcv.createdby, itcv.updated, itcv.updatedby, itcv.isactive, fp.paymentdate, itcv.percentage, itcv.taxamt, itcv.taxbaseamt, fpsd.iscanceled, fp.fin_payment_id, fpd.fin_payment_detail_id, fp.isreceipt, fp.c_bpartner_id, fp.fin_paymentmethod_id, fp.status, fp.amount, fp.c_currency_id, itcv.c_invoicetax_id, itcv.ismanualsettlement FROM c_invoicetax_cashvat itcv JOIN c_invoicetax it ON it.c_invoicetax_id = itcv.c_invoicetax_id JOIN fin_payment_detail fpd ON fpd.fin_payment_detail_id = itcv.fin_payment_detail_id JOIN fin_payment fp ON fp.fin_payment_id = fpd.fin_payment_id JOIN fin_payment_scheduledetail fpsd ON fpd.fin_payment_detail_id = fpsd.fin_payment_detail_id JOIN fin_payment_schedule fpsi ON fpsi.fin_payment_schedule_id = fpsd.fin_payment_schedule_invoice WHERE itcv.ismanualsettlement = 'N' AND fpsi.c_invoice_id = it.c_invoice_id UNION ALL SELECT itcv.c_invoicetax_cashvat_id AS c_invoicetax_cashvat_v_id, itcv.ad_client_id, itcv.ad_org_id, itcv.created, itcv.createdby, itcv.updated, itcv.updatedby, itcv.isactive, NULL AS paymentdate, itcv.percentage, itcv.taxamt, itcv.taxbaseamt, 'N' AS iscanceled, NULL AS fin_payment_id, NULL AS fin_payment_detail_id, NULL AS isreceipt, NULL AS c_bpartner_id, NULL AS fin_paymentmethod_id, NULL AS status, NULL AS amount, NULL AS c_currency_id, itcv.c_invoicetax_id, itcv.ismanualsettlement FROM c_invoicetax_cashvat itcv WHERE itcv.ismanualsettlement = 'Y']]></view>
   </database>