# HG changeset patch
# User Asier Lostalé <asier.lostale@openbravo.com>
# Date 1470384190 -7200
#      Fri Aug 05 10:03:10 2016 +0200
# Node ID d9d424dc595b5fb79e1722883aceba7d02fccada
# Parent  1a292c2195e6060110fd15c84de43fba54a4109e
fixed bug 33629: slow order/invoice grid population in Add Payment Description

  In evironments with big volumes it was slow because it performed a Seq Scan
  on FIN_Payment_ScheduleDetail table.

  It has been fixed for the cases when BP is preset by:
   - Removing left joins to get BP from order/invoice to retrieve it directly
     from FIN_Payment_ScheduleDetail
   - Adding an index on FIN_Payment_ScheduleDetail.BP so that previous query
     does not require of a Seq Scan

diff -r 1a292c2195e6 -r d9d424dc595b modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml	Tue Aug 02 14:07:10 2016 +0200
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml	Fri Aug 05 10:03:10 2016 +0200
@@ -996,7 +996,7 @@
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
-<!--2295DC7A28FE4F9DA842F453E2FF2F41-->  <ENTITY_ALIAS><![CDATA[COALESCE(invbp.name, ordbp.name)]]></ENTITY_ALIAS>
+<!--2295DC7A28FE4F9DA842F453E2FF2F41-->  <ENTITY_ALIAS><![CDATA[bp.name]]></ENTITY_ALIAS>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  <ALLOWSORTING><![CDATA[Y]]></ALLOWSORTING>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  <ALLOWFILTERING><![CDATA[Y]]></ALLOWFILTERING>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41--></AD_COLUMN>
diff -r 1a292c2195e6 -r d9d424dc595b modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml	Tue Aug 02 14:07:10 2016 +0200
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml	Fri Aug 05 10:03:10 2016 +0200
@@ -69,14 +69,13 @@
 <!--58AF4D3E594B421A9A7307480736F03E-->  <ISTREE><![CDATA[N]]></ISTREE>
 <!--58AF4D3E594B421A9A7307480736F03E-->  <HQLQUERY><![CDATA[select @selectClause@
 from FIN_Payment_ScheduleDetail as psd
+join psd.businessPartner as bp
 left outer join psd.orderPaymentSchedule as ops  
 left outer join ops.order as ord @joinClauseOrder@
-left outer join ord.businessPartner as ordbp
 left outer join ops.fINPaymentPriority as opriority 
 left outer join ops.finPaymentmethod opsfp
 left outer join psd.invoicePaymentSchedule as ips  
 left outer join ips.invoice as inv @joinClauseInvoice@
-left outer join inv.businessPartner as invbp
 left outer join ips.fINPaymentPriority as ipriority 
 left outer join ips.finPaymentmethod as ipsfp
 left outer join psd.organization as org 
diff -r 1a292c2195e6 -r d9d424dc595b modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java
--- a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java	Tue Aug 02 14:07:10 2016 +0200
+++ b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java	Fri Aug 05 10:03:10 2016 +0200
@@ -129,8 +129,6 @@
       selectClause
           .append(" COALESCE(ips.finPaymentmethod.id, ops.finPaymentmethod.id) as paymentMethod, ");
       selectClause.append(" COALESCE(ipsfp.name, opsfp.name) as paymentMethodName, ");
-      selectClause.append(" COALESCE(invbp.id, ordbp.id) as businessPartner, ");
-      selectClause.append(" COALESCE(invbp.name, ordbp.name) as businessPartnerName, ");
       selectClause.append(" COALESCE(ips.expectedDate, ops.expectedDate) as expectedDate, ");
       selectClause.append(" max(COALESCE(ips.amount, ops.amount)) as expectedAmount, ");
       selectClause.append(" max(COALESCE(inv.grandTotalAmount, 0)) as invoicedAmount, ");
@@ -145,8 +143,6 @@
       selectClause
           .append(" COALESCE(ops.finPaymentmethod.id, ips.finPaymentmethod.id) as paymentMethod, ");
       selectClause.append(" COALESCE(opsfp.name, ipsfp.name) as paymentMethodName, ");
-      selectClause.append(" COALESCE(invbp.id, ordbp.id) as businessPartner, ");
-      selectClause.append(" COALESCE(invbp.name, ordbp.name) as businessPartnerName, ");
       selectClause.append(" COALESCE(ops.expectedDate, ips.expectedDate) as expectedDate, ");
       selectClause.append(" max(COALESCE(ips.amount, ops.amount)) as expectedAmount, ");
       selectClause.append(" sum(COALESCE(inv.grandTotalAmount, 0)) as invoicedAmount, ");
@@ -160,12 +156,13 @@
       selectClause
           .append(" COALESCE(ips.finPaymentmethod.id, ops.finPaymentmethod.id) as paymentMethod, ");
       selectClause.append(" COALESCE(ipsfp.name, opsfp.name) as paymentMethodName, ");
-      selectClause.append(" COALESCE(invbp.id, ordbp.id) as businessPartner, ");
-      selectClause.append(" COALESCE(invbp.name, ordbp.name) as businessPartnerName, ");
       selectClause.append(" COALESCE(ips.expectedDate, ops.expectedDate) as expectedDate, ");
       selectClause.append(" max(COALESCE(ips.amount, ops.amount)) as expectedAmount, ");
       selectClause.append(" max(COALESCE(inv.grandTotalAmount, 0)) as invoicedAmount, ");
     }
+    selectClause.append(" bp.id as businessPartner, ");
+    selectClause.append(" bp.name as businessPartnerName, ");
+
     selectClause.append(" SUM(psd.amount + psd.writeoffAmount) as outstandingAmount, ");
     selectClause.append(" COALESCE(sum(pd.amount), 0) as amount, ");
     selectClause
@@ -244,38 +241,24 @@
       }
       whereClause.append(") or ");
     }
+
+    whereClause.append(" ( ");
     if ("I".equals(transactionType)) {
-
-      whereClause.append(" ( ");
       whereClause.append(" inv.salesTransaction = :isSalesTransaction");
-      if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) {
-        whereClause.append(" and invbp.id = :businessPartnerId ");
-      }
       whereClause.append(" and inv.currency.id = :currencyId ) ");
-
     } else if ("O".equals(transactionType)) {
-      whereClause.append(" ( ");
       whereClause.append(" ord.salesTransaction = :isSalesTransaction");
-      if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) {
-        whereClause.append(" and ordbp.id = :businessPartnerId ");
-      }
       whereClause.append(" and ord.currency.id = :currencyId ) ");
-
     } else {
-
-      whereClause.append(" ( ");
       whereClause.append(" inv.salesTransaction = :isSalesTransaction");
-      if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) {
-        whereClause.append(" and invbp.id = :businessPartnerId ");
-      }
       whereClause.append(" and inv.currency.id = :currencyId ) ");
       whereClause.append(" or ( ");
       whereClause.append(" ord.salesTransaction = :isSalesTransaction");
-      if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) {
-        whereClause.append(" and ordbp.id = :businessPartnerId");
-      }
       whereClause.append(" and ord.currency.id = :currencyId ) ");
+    }
 
+    if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) {
+      whereClause.append(" and bp.id = :businessPartnerId ");
     }
 
     whereClause.append(")");
@@ -322,8 +305,7 @@
       groupByClause.append(" oinfo.aPRMPaymentDescription, ");
       groupByClause.append(" inv.orderReference, ");
     }
-    groupByClause.append(" COALESCE(invbp.id, ordbp.id), ");
-    groupByClause.append(" COALESCE(invbp.name, ordbp.name) ");
+    groupByClause.append(" bp ");
     return groupByClause;
   }
 
diff -r 1a292c2195e6 -r d9d424dc595b src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml
--- a/src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml	Tue Aug 02 14:07:10 2016 +0200
+++ b/src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml	Fri Aug 05 10:03:10 2016 +0200
@@ -143,6 +143,9 @@
       <foreign-key foreignTable="USER2" name="FIN_SCHEDULEDETAIL_USER2">
         <reference local="USER2_ID" foreign="USER2_ID"/>
       </foreign-key>
+      <index name="FIN_PAY_SCHEDULEDETAIL_BP" unique="false">
+        <index-column name="C_BPARTNER_ID"/>
+      </index>
       <index name="FIN_PAY_SCHEDULEDETAIL_IN" unique="false">
         <index-column name="FIN_PAYMENT_SCHEDULE_INVOICE"/>
       </index>