Fixes issue 33620: Removes UNION ALL from view and merges it into one sentece
authorDavid Miguelez <david.miguelez@openbravo.com>
Fri, 05 Aug 2016 11:30:23 +0200
changeset 30078 3f38dea1af12
parent 30077 e49203fbb0c9
child 30079 6e46a5541bbd
Fixes issue 33620: Removes UNION ALL from view and merges it into one sentece
src-db/database/model/views/C_INVOICETAX_CASHVAT_V.xml
--- a/src-db/database/model/views/C_INVOICETAX_CASHVAT_V.xml	Mon Aug 01 02:13:20 2016 -0400
+++ b/src-db/database/model/views/C_INVOICETAX_CASHVAT_V.xml	Fri Aug 05 11:30:23 2016 +0200
@@ -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 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>
+    <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, COALESCE(fpsd.iscanceled, 'N') AS 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 c_invoicetax it ON it.c_invoicetax_id = itcv.c_invoicetax_id 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 LEFT 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) OR itcv.ismanualsettlement = 'Y']]></view>
   </database>