details: https://code.openbravo.com/erp/devel/pi/rev/4e9e547d8199
changeset: 28585:4e9e547d8199
user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
date: Thu Jan 28 09:24:21 2016 +0100
summary: Fixes issue 31772: Improve performance in Costing Rule validation
Update M_TRANSACTION_TRG to execute M_UPDATE_INVENTORY only when updated
columns are not related to costing (like transactioncost,
m_costing_algorithm_id, iscostcalculated, c_currency_id, costing_status,
isprocessed, checkpricedifference, manualcostadjustment and iscostpermanent).
Update some methods in CostingRuleProcess and
CostingRuleProcessOnProcessHandler to use query.uniqueResult() != null instead
of query.count() > 0.
Update initializeOldTrx method in CostingRuleProcess to do an insert and an
update in hql instead of doing a loop in java.
Change second loop in updateInventoriesCostAndProcessInitInventories method in
CostingRuleProcess to use a scroll instead of looping over a list.
Use new getInventoryLineTransactions method in CostingRuleProcess instead of
getInventoryLineTransaction method, which returns a scroll.
Use trx.getPhysicalInventoryLine().getRelatedInventory() instead of
getInitIcl(cri.getInitInventory(), icl) in CostingRuleProcess.
Change updateInitInventoriesTrxDate method in CostingRuleProcess to do an
update in hql instead of doing a loop in java.
diffstat:
src-db/database/model/triggers/M_TRANSACTION_TRG.xml | 50 +-
src/org/openbravo/costing/CostingMigrationProcess.java | 5 +-
src/org/openbravo/costing/CostingRuleProcess.java | 273
++++++---
src/org/openbravo/costing/CostingRuleProcessOnProcessHandler.java | 6 +-
4 files changed, 216 insertions(+), 118 deletions(-)
diffs (truncated from 471 to 300 lines):
diff -r d2a064ba6376 -r 4e9e547d8199
src-db/database/model/triggers/M_TRANSACTION_TRG.xml
--- a/src-db/database/model/triggers/M_TRANSACTION_TRG.xml Mon Feb 01
18:49:06 2016 +0100
+++ b/src-db/database/model/triggers/M_TRANSACTION_TRG.xml Thu Jan 28
09:24:21 2016 +0100
@@ -18,7 +18,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
-* All portions are Copyright (C) 2001-2012 Openbravo SLU
+* All portions are Copyright (C) 2001-2016 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
@@ -127,7 +127,29 @@
END IF;
-- Updating inventory
- IF UPDATING OR DELETING THEN
+ IF DELETING OR (UPDATING AND (
+ (COALESCE(:OLD.M_TRANSACTION_ID, '0') <> COALESCE(:NEW.M_TRANSACTION_ID,
'0')) OR
+ (COALESCE(:OLD.AD_CLIENT_ID, '0') <> COALESCE(:NEW.AD_CLIENT_ID, '0')) OR
+ (COALESCE(:OLD.AD_ORG_ID, '0') <> COALESCE(:NEW.AD_ORG_ID, '0')) OR
+ (COALESCE(:OLD.ISACTIVE, 'Y') <> COALESCE(:NEW.ISACTIVE, 'Y')) OR
+ (COALESCE(:OLD.MOVEMENTTYPE, '.') <> COALESCE(:NEW.MOVEMENTTYPE, '.')) OR
+ (COALESCE(:OLD.M_LOCATOR_ID, '0') <> COALESCE(:NEW.M_LOCATOR_ID, '0')) OR
+ (COALESCE(:OLD.M_PRODUCT_ID, '0') <> COALESCE(:NEW.M_PRODUCT_ID, '0')) OR
+ (COALESCE(:OLD.MOVEMENTDATE, v_DateNull) <> COALESCE(:NEW.MOVEMENTDATE,
v_DateNull)) OR
+ (COALESCE(:OLD.MOVEMENTQTY, '0') <> COALESCE(:NEW.MOVEMENTQTY, '0')) OR
+ (COALESCE(:OLD.M_INVENTORYLINE_ID, '0') <>
COALESCE(:NEW.M_INVENTORYLINE_ID, '0')) OR
+ (COALESCE(:OLD.M_MOVEMENTLINE_ID, '0') <> COALESCE(:NEW.M_MOVEMENTLINE_ID,
'0')) OR
+ (COALESCE(:OLD.M_INOUTLINE_ID, '0') <> COALESCE(:NEW.M_INOUTLINE_ID, '0'))
OR
+ (COALESCE(:OLD.M_PRODUCTIONLINE_ID, '0') <>
COALESCE(:NEW.M_PRODUCTIONLINE_ID, '0')) OR
+ (COALESCE(:OLD.C_PROJECTISSUE_ID, '0') <> COALESCE(:NEW.C_PROJECTISSUE_ID,
'0')) OR
+ (COALESCE(:OLD.M_ATTRIBUTESETINSTANCE_ID, '0') <>
COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0')) OR
+ (COALESCE(:OLD.M_PRODUCT_UOM_ID, '0') <> COALESCE(:NEW.M_PRODUCT_UOM_ID,
'0')) OR
+ (COALESCE(:OLD.QUANTITYORDER, '0') <> COALESCE(:NEW.QUANTITYORDER, '0')) OR
+ (COALESCE(:OLD.C_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0')) OR
+ (COALESCE(:OLD.M_INTERNAL_CONSUMPTIONLINE_ID, '0') <>
COALESCE(:NEW.M_INTERNAL_CONSUMPTIONLINE_ID, '0')) OR
+ (COALESCE(:OLD.TRXPROCESSDATE, v_DateNull) <>
COALESCE(:NEW.TRXPROCESSDATE, v_DateNull)) OR
+ (COALESCE(:OLD.CHECKRESERVEDQTY, 'Y') <> COALESCE(:NEW.CHECKRESERVEDQTY,
'Y'))
+ )) THEN
M_UPDATE_INVENTORY(:OLD.AD_CLIENT_ID, :OLD.AD_ORG_ID, :OLD.UPDATEDBY,
:OLD.M_PRODUCT_ID, :OLD.M_LOCATOR_ID, :OLD.M_ATTRIBUTESETINSTANCE_ID,
:OLD.C_UOM_ID, :OLD.M_PRODUCT_UOM_ID, -:OLD.MOVEMENTQTY, -:OLD.QUANTITYORDER,
NULL, :OLD.MOVEMENTQTY, :OLD.QUANTITYORDER) ;
-- FIXME: this shall be removed/reviewed when new warehouse management is
implemented
IF (:OLD.M_PRODUCTIONLINE_ID IS NOT NULL) THEN
@@ -140,7 +162,29 @@
END IF;
-- END FIXME
END IF;
- IF INSERTING OR UPDATING THEN
+ IF INSERTING OR (UPDATING AND (
+ (COALESCE(:OLD.M_TRANSACTION_ID, '0') <> COALESCE(:NEW.M_TRANSACTION_ID,
'0')) OR
+ (COALESCE(:OLD.AD_CLIENT_ID, '0') <> COALESCE(:NEW.AD_CLIENT_ID, '0')) OR
+ (COALESCE(:OLD.AD_ORG_ID, '0') <> COALESCE(:NEW.AD_ORG_ID, '0')) OR
+ (COALESCE(:OLD.ISACTIVE, 'Y') <> COALESCE(:NEW.ISACTIVE, 'Y')) OR
+ (COALESCE(:OLD.MOVEMENTTYPE, '.') <> COALESCE(:NEW.MOVEMENTTYPE, '.')) OR
+ (COALESCE(:OLD.M_LOCATOR_ID, '0') <> COALESCE(:NEW.M_LOCATOR_ID, '0')) OR
+ (COALESCE(:OLD.M_PRODUCT_ID, '0') <> COALESCE(:NEW.M_PRODUCT_ID, '0')) OR
+ (COALESCE(:OLD.MOVEMENTDATE, v_DateNull) <> COALESCE(:NEW.MOVEMENTDATE,
v_DateNull)) OR
+ (COALESCE(:OLD.MOVEMENTQTY, '0') <> COALESCE(:NEW.MOVEMENTQTY, '0')) OR
+ (COALESCE(:OLD.M_INVENTORYLINE_ID, '0') <>
COALESCE(:NEW.M_INVENTORYLINE_ID, '0')) OR
+ (COALESCE(:OLD.M_MOVEMENTLINE_ID, '0') <> COALESCE(:NEW.M_MOVEMENTLINE_ID,
'0')) OR
+ (COALESCE(:OLD.M_INOUTLINE_ID, '0') <> COALESCE(:NEW.M_INOUTLINE_ID, '0'))
OR
+ (COALESCE(:OLD.M_PRODUCTIONLINE_ID, '0') <>
COALESCE(:NEW.M_PRODUCTIONLINE_ID, '0')) OR
+ (COALESCE(:OLD.C_PROJECTISSUE_ID, '0') <> COALESCE(:NEW.C_PROJECTISSUE_ID,
'0')) OR
+ (COALESCE(:OLD.M_ATTRIBUTESETINSTANCE_ID, '0') <>
COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0')) OR
+ (COALESCE(:OLD.M_PRODUCT_UOM_ID, '0') <> COALESCE(:NEW.M_PRODUCT_UOM_ID,
'0')) OR
+ (COALESCE(:OLD.QUANTITYORDER, '0') <> COALESCE(:NEW.QUANTITYORDER, '0')) OR
+ (COALESCE(:OLD.C_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0')) OR
+ (COALESCE(:OLD.M_INTERNAL_CONSUMPTIONLINE_ID, '0') <>
COALESCE(:NEW.M_INTERNAL_CONSUMPTIONLINE_ID, '0')) OR
+ (COALESCE(:OLD.TRXPROCESSDATE, v_DateNull) <>
COALESCE(:NEW.TRXPROCESSDATE, v_DateNull)) OR
+ (COALESCE(:OLD.CHECKRESERVEDQTY, 'Y') <> COALESCE(:NEW.CHECKRESERVEDQTY,
'Y'))
+ )) THEN
SELECT MAX(MOVEMENTDATE)
INTO v_DATEINVENTORY
FROM M_INVENTORY I,
diff -r d2a064ba6376 -r 4e9e547d8199
src/org/openbravo/costing/CostingMigrationProcess.java
--- a/src/org/openbravo/costing/CostingMigrationProcess.java Mon Feb 01
18:49:06 2016 +0100
+++ b/src/org/openbravo/costing/CostingMigrationProcess.java Thu Jan 28
09:24:21 2016 +0100
@@ -11,7 +11,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2012-2015 Openbravo SLU
+ * All portions are Copyright (C) 2012-2016 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
*************************************************************************
@@ -495,7 +495,6 @@
int costPrecision = cur.getCostingPrecision().intValue();
int stdPrecision = cur.getStandardPrecision().intValue();
- CostingRuleProcess crp = new CostingRuleProcess();
// Update cost of inventories and process starting physical inventories.
ScrollableResults icls = getCloseInventoryLines(orgs);
String productId = "";
@@ -512,7 +511,7 @@
totalStock = stock.get("stock");
}
- MaterialTransaction trx = crp.getInventoryLineTransaction(icl);
+ MaterialTransaction trx =
icl.getMaterialMgmtMaterialTransactionList().get(0);
trx.setTransactionProcessDate(DateUtils.addSeconds(trx.getTransactionProcessDate(),
-1));
trx.setCurrency(OBDal.getInstance().get(Currency.class, curId));
diff -r d2a064ba6376 -r 4e9e547d8199
src/org/openbravo/costing/CostingRuleProcess.java
--- a/src/org/openbravo/costing/CostingRuleProcess.java Mon Feb 01 18:49:06
2016 +0100
+++ b/src/org/openbravo/costing/CostingRuleProcess.java Thu Jan 28 09:24:21
2016 +0100
@@ -20,6 +20,7 @@
import java.math.BigDecimal;
import java.math.RoundingMode;
+import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
@@ -58,6 +59,8 @@
import org.openbravo.model.materialmgmt.transaction.InventoryCount;
import org.openbravo.model.materialmgmt.transaction.InventoryCountLine;
import org.openbravo.model.materialmgmt.transaction.MaterialTransaction;
+import org.openbravo.model.materialmgmt.transaction.ShipmentInOut;
+import org.openbravo.model.materialmgmt.transaction.ShipmentInOutLine;
import org.openbravo.scheduling.Process;
import org.openbravo.scheduling.ProcessBundle;
import org.openbravo.scheduling.ProcessLogger;
@@ -200,7 +203,8 @@
pQry.setFilterOnReadableOrganization(false);
pQry.setNamedParameter("porgs", naturalOrgs);
pQry.setNamedParameter("childOrgs", childOrgs);
- return pQry.count() > 0;
+ pQry.setMaxResult(1);
+ return pQry.uniqueResult() != null;
}
private void checkAllTrxCalculated(Set<String> naturalOrgs, Set<String>
childOrgs) {
@@ -219,7 +223,8 @@
pQry.setFilterOnReadableOrganization(false);
pQry.setNamedParameter("porgs", naturalOrgs);
pQry.setNamedParameter("childOrgs", childOrgs);
- if (pQry.count() > 0) {
+ pQry.setMaxResult(1);
+ if (pQry.uniqueResult() != null) {
throw new OBException("@TrxWithCostNoCalculated@");
}
}
@@ -240,57 +245,83 @@
pQry.setFilterOnReadableOrganization(false);
pQry.setNamedParameter("porgs", naturalOrgs);
pQry.setNamedParameter("childOrgs", childOrgs);
- if (pQry.count() > 0) {
+ pQry.setMaxResult(1);
+ if (pQry.uniqueResult() != null) {
throw new OBException("@ProductsWithTrxCalculated@");
}
}
- private void initializeOldTrx(Set<String> childOrgs, Date date) {
- StringBuffer where = new StringBuffer();
- where.append(" where " + MaterialTransaction.PROPERTY_ORGANIZATION + ".id
in (:orgs)");
- where.append(" and " + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <
:date");
- OBQuery<MaterialTransaction> trxQry = OBDal.getInstance().createQuery(
- MaterialTransaction.class, where.toString());
- trxQry.setFilterOnReadableOrganization(false);
- trxQry.setNamedParameter("orgs", childOrgs);
- trxQry.setNamedParameter("date", date);
- trxQry.setFetchSize(1000);
- ScrollableResults trxs = trxQry.scroll(ScrollMode.FORWARD_ONLY);
- int i = 1;
- try {
- while (trxs.next()) {
- MaterialTransaction trx = (MaterialTransaction) trxs.get(0);
+ private void initializeOldTrx(Set<String> childOrgs, Date date) throws
SQLException {
+ Client client = OBDal.getInstance().get(Client.class,
+ OBContext.getOBContext().getCurrentClient().getId());
- TransactionCost transactionCost =
OBProvider.getInstance().get(TransactionCost.class);
- transactionCost.setInventoryTransaction(trx);
- transactionCost.setCostDate(trx.getTransactionProcessDate());
- transactionCost.setClient(trx.getClient());
- transactionCost.setOrganization(trx.getOrganization());
- transactionCost.setCost(BigDecimal.ZERO);
- transactionCost.setCurrency(trx.getClient().getCurrency());
- transactionCost.setAccountingDate(trx.getGoodsShipmentLine() != null ?
trx
- .getGoodsShipmentLine().getShipmentReceipt().getAccountingDate() :
trx
- .getMovementDate());
- List<TransactionCost> trxCosts = trx.getTransactionCostList();
- trxCosts.add(transactionCost);
- trx.setTransactionCostList(trxCosts);
+ StringBuffer insert = new StringBuffer();
+ insert.append(" insert into " + TransactionCost.ENTITY_NAME);
+ insert.append(" (" + TransactionCost.PROPERTY_ID);
+ insert.append(", " + TransactionCost.PROPERTY_CLIENT);
+ insert.append(", " + TransactionCost.PROPERTY_ORGANIZATION);
+ insert.append(", " + TransactionCost.PROPERTY_CREATIONDATE);
+ insert.append(", " + TransactionCost.PROPERTY_CREATEDBY);
+ insert.append(", " + TransactionCost.PROPERTY_UPDATED);
+ insert.append(", " + TransactionCost.PROPERTY_UPDATEDBY);
+ insert.append(", " + TransactionCost.PROPERTY_ACTIVE);
+ insert.append(", " + TransactionCost.PROPERTY_INVENTORYTRANSACTION);
+ insert.append(", " + TransactionCost.PROPERTY_COST);
+ insert.append(", " + TransactionCost.PROPERTY_COSTDATE);
+ insert.append(", " + TransactionCost.PROPERTY_CURRENCY);
+ insert.append(", " + TransactionCost.PROPERTY_ACCOUNTINGDATE);
+ insert.append(")");
+ insert.append(" select get_uuid()");
+ insert.append(", t." + MaterialTransaction.PROPERTY_CLIENT);
+ insert.append(", t." + MaterialTransaction.PROPERTY_ORGANIZATION);
+ insert.append(", now()");
+ insert.append(", t." + MaterialTransaction.PROPERTY_CREATEDBY);
+ insert.append(", now()");
+ insert.append(", t." + MaterialTransaction.PROPERTY_UPDATEDBY);
+ insert.append(", t." + MaterialTransaction.PROPERTY_ACTIVE);
+ insert.append(", t");
+ insert.append(", cast(0 as big_decimal)");
+ insert.append(", t." +
MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE);
+ insert.append(", t." + MaterialTransaction.PROPERTY_CLIENT + "." +
Client.PROPERTY_CURRENCY);
+ insert.append(", coalesce(io." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE +
", t."
+ + MaterialTransaction.PROPERTY_MOVEMENTDATE + ")");
+ insert.append(" from " + MaterialTransaction.ENTITY_NAME + " as t");
+ insert.append(" left join t." +
MaterialTransaction.PROPERTY_GOODSSHIPMENTLINE + " as iol");
+ insert.append(" left join iol." +
ShipmentInOutLine.PROPERTY_SHIPMENTRECEIPT + " as io");
+ insert.append(" where t." + MaterialTransaction.PROPERTY_ORGANIZATION +
".id in (:orgs)");
+ insert.append(" and t." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <
:date");
+ insert.append(" and t." + MaterialTransaction.PROPERTY_ISPROCESSED + " =
false");
+ insert.append(" and t." + MaterialTransaction.PROPERTY_ACTIVE + " = true");
+ insert.append(" and t." + MaterialTransaction.PROPERTY_CLIENT + ".id =
:client");
- trx.setCostCalculated(true);
- trx.setCostingStatus("CC");
- trx.setTransactionCost(BigDecimal.ZERO);
- trx.setCurrency(trx.getClient().getCurrency());
- trx.setProcessed(true);
- OBDal.getInstance().save(trx);
+ Query queryInsert =
OBDal.getInstance().getSession().createQuery(insert.toString());
+ queryInsert.setParameterList("orgs", childOrgs);
+ queryInsert.setDate("date", date);
+ queryInsert.setString("client", client.getId());
+ queryInsert.executeUpdate();
- if ((i % 100) == 0) {
- OBDal.getInstance().flush();
- OBDal.getInstance().getSession().clear();
- }
- i++;
- }
- } finally {
- trxs.close();
- }
+ StringBuffer update = new StringBuffer();
+ update.append(" update " + MaterialTransaction.ENTITY_NAME);
+ update.append(" set " + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + "
= true");
+ update.append(", " + MaterialTransaction.PROPERTY_COSTINGSTATUS + " =
'CC'");
+ update.append(", " + MaterialTransaction.PROPERTY_TRANSACTIONCOST + " = "
+ BigDecimal.ZERO);
+ update.append(", " + MaterialTransaction.PROPERTY_CURRENCY + " =
:currency");
+ update.append(", " + MaterialTransaction.PROPERTY_ISPROCESSED + " = true");
+ update.append(" where " + MaterialTransaction.PROPERTY_ORGANIZATION + ".id
in (:orgs)");
+ update.append(" and " + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <
:date");
+ update.append(" and " + MaterialTransaction.PROPERTY_ISPROCESSED + " =
false");
+ update.append(" and " + MaterialTransaction.PROPERTY_ACTIVE + " = true");
+ update.append(" and " + MaterialTransaction.PROPERTY_CLIENT + ".id =
:client");
+
+ Query queryUpdate =
OBDal.getInstance().getSession().createQuery(update.toString());
+ queryUpdate.setParameter("currency", client.getCurrency());
+ queryUpdate.setParameterList("orgs", childOrgs);
+ queryUpdate.setDate("date", date);
+ queryUpdate.setString("client", client.getId());
+ queryUpdate.executeUpdate();
+
+ OBDal.getInstance().getSession().flush();
+ OBDal.getInstance().getSession().clear();
}
@Deprecated
@@ -366,6 +397,7 @@
stockLines.close();
}
// Process closing physical inventories.
+ rule = OBDal.getInstance().get(CostingRule.class, ruleId);
for (CostingRuleInit cri : rule.getCostingRuleInitList()) {
new InventoryCountProcess().processInventory(cri.getCloseInventory(),
false);
}
@@ -496,52 +528,67 @@
boolean existsPreviousRule) {
CostingRule rule = OBDal.getInstance().get(CostingRule.class, ruleId);
for (CostingRuleInit cri : rule.getCostingRuleInitList()) {
- for (InventoryCountLine icl :
cri.getCloseInventory().getMaterialMgmtInventoryCountLineList()) {
- MaterialTransaction trx = getInventoryLineTransaction(icl);
- // Remove 1 second from transaction date to ensure that cost is
calculated with previous
- // costing rule.
- trx.setTransactionProcessDate(DateUtils.addSeconds(startingDate, -1));
- BigDecimal trxCost = BigDecimal.ZERO;
- BigDecimal cost = null;
- Currency cur =
FinancialUtils.getLegalEntityCurrency(trx.getOrganization());
- if (existsPreviousRule) {
- trxCost = CostingUtils.getTransactionCost(trx, startingDate, true,
cur);
- if (trx.getMovementQuantity().compareTo(BigDecimal.ZERO) != 0) {
- cost = trxCost.divide(trx.getMovementQuantity().abs(),
cur.getCostingPrecision()
- .intValue(), RoundingMode.HALF_UP);
- trx = OBDal.getInstance().get(MaterialTransaction.class,
trx.getId());
+ ScrollableResults trxs =
getInventoryLineTransactions(cri.getCloseInventory());
+ int i = 1;
+ try {
------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
