Fixes issue 30170: Generic UpdatePaymentPlan module script
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Mon, 22 Jun 2015 18:36:18 +0200
changeset 27140 1cc71ac627a7
parent 27139 89faeb629846
child 27141 0a35ad8c7048
Fixes issue 30170: Generic UpdatePaymentPlan module script

Select query in UpdatePaymentPlan has been changed to make it generic.
Now it will fix all fin_payment_schedule related to an order or an invoice which paidamt or outstandingamt does not match with the sum of its fin_payment_scheduledetail amount.
src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class
src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class has changed
--- a/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql	Mon Jun 22 18:29:20 2015 +0200
+++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql	Mon Jun 22 18:36:18 2015 +0200
@@ -32,15 +32,17 @@
     <SqlMethodComment></SqlMethodComment>
     <Sql>
       <![CDATA[
-        SELECT t1.fin_payment_schedule_id as id, t2.linesamount as amount
-        FROM fin_payment_schedule t1 JOIN (
-            SELECT psd.fin_payment_schedule_order, sum(psd.amount) AS linesamount
-            FROM fin_payment_scheduledetail psd
-            WHERE psd.isinvoicepaid='Y'
-            GROUP BY psd.fin_payment_schedule_order
-        ) t2
-        ON t1.fin_payment_schedule_id = t2.fin_payment_schedule_order
-        WHERE t1.paidamt/2 = t2.linesamount
+        SELECT ps.fin_payment_schedule_id as id, sum(psd.amount + COALESCE(psd.writeoffamt, 0)) as amount
+        FROM fin_payment_scheduledetail psd
+        INNER JOIN fin_payment_schedule ps 
+        ON (ps.fin_payment_schedule_id = psd.fin_payment_schedule_order 
+        OR ps.fin_payment_schedule_id = psd.fin_payment_schedule_invoice)
+        WHERE psd.isinvoicepaid = 'Y'
+        AND psd.iscanceled = 'N' 
+        AND psd.fin_payment_detail_id IS NOT NULL
+        GROUP BY ps.fin_payment_schedule_id, ps.paidamt, ps.outstandingamt, ps.amount
+        HAVING (ps.paidamt <> sum(psd.amount + COALESCE(psd.writeoffamt, 0))
+        OR (ps.outstandingamt <> ps.amount - sum(psd.amount + COALESCE(psd.writeoffamt, 0))))
       ]]>
     </Sql>
   </SqlMethod>