Fixes Issue
28157: Landed Cost takes into account exchange rate at invoice level
If an exchange rate different from the System is added in Purchase Invoice and this Invoice is matched in a Landed Cost, a new record will be created in 'Matched Amount' tab under 'Landed Cost Cost' tab of 'Landed Cost' window. This new record will have the new flag of 'Is Conversion Matching' as true, and the amount will be the difference obtained from using the Invoice exchange rate compared to the one of the System.
This will create a new Cost Adjustment of source 'Landed Cost' with the same amount.
--- a/src-db/database/model/tables/M_LC_MATCHED.xml Thu Dec 04 12:12:34 2014 +0100
+++ b/src-db/database/model/tables/M_LC_MATCHED.xml Wed Dec 10 14:09:54 2014 +0100
@@ -49,6 +49,10 @@
<default/>
<onCreateDefault/>
</column>
+ <column name="ISCONVERSIONMATCHING" primaryKey="false" required="false" type="CHAR" size="1" autoIncrement="false">
+ <default/>
+ <onCreateDefault><![CDATA['N']]></onCreateDefault>
+ </column>
<foreign-key foreignTable="AD_CLIENT" name="M_LC_MATCHED_CLIENT">
<reference local="AD_CLIENT_ID" foreign="AD_CLIENT_ID"/>
</foreign-key>
@@ -68,5 +72,6 @@
<index-column name="M_LC_COST_ID"/>
</index>
<check name="M_LC_MATCHED_ACTIVE_CHK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check>
+ <check name="M_LC_MATCHED_ISCONVERSIONMATCH"><![CDATA[ISCONVERSIONMATCHING IN ('Y', 'N')]]></check>
</table>
</database>
--- a/src-db/database/sourcedata/AD_COLUMN.xml Thu Dec 04 12:12:34 2014 +0100
+++ b/src-db/database/sourcedata/AD_COLUMN.xml Wed Dec 10 14:09:54 2014 +0100
@@ -275223,6 +275223,39 @@
<!--7CC03ABBEF3E410697A8A9D8C0FE7587--> <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
<!--7CC03ABBEF3E410697A8A9D8C0FE7587--></AD_COLUMN>
+<!--7D036A285F8E4475B0B613E421381C25--><AD_COLUMN>
+<!--7D036A285F8E4475B0B613E421381C25--> <AD_COLUMN_ID><![CDATA[7D036A285F8E4475B0B613E421381C25]]></AD_COLUMN_ID>
+<!--7D036A285F8E4475B0B613E421381C25--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--7D036A285F8E4475B0B613E421381C25--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--7D036A285F8E4475B0B613E421381C25--> <NAME><![CDATA[Isconversionmatching]]></NAME>
+<!--7D036A285F8E4475B0B613E421381C25--> <COLUMNNAME><![CDATA[Isconversionmatching]]></COLUMNNAME>
+<!--7D036A285F8E4475B0B613E421381C25--> <AD_TABLE_ID><![CDATA[EF0201D90F30499EBFAF53D0FC7452DB]]></AD_TABLE_ID>
+<!--7D036A285F8E4475B0B613E421381C25--> <AD_REFERENCE_ID><![CDATA[20]]></AD_REFERENCE_ID>
+<!--7D036A285F8E4475B0B613E421381C25--> <FIELDLENGTH><![CDATA[1]]></FIELDLENGTH>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISKEY><![CDATA[N]]></ISKEY>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISPARENT><![CDATA[N]]></ISPARENT>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISMANDATORY><![CDATA[N]]></ISMANDATORY>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISUPDATEABLE><![CDATA[Y]]></ISUPDATEABLE>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISIDENTIFIER><![CDATA[N]]></ISIDENTIFIER>
+<!--7D036A285F8E4475B0B613E421381C25--> <SEQNO><![CDATA[130]]></SEQNO>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISTRANSLATED><![CDATA[N]]></ISTRANSLATED>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISENCRYPTED><![CDATA[N]]></ISENCRYPTED>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISSELECTIONCOLUMN><![CDATA[N]]></ISSELECTIONCOLUMN>
+<!--7D036A285F8E4475B0B613E421381C25--> <AD_ELEMENT_ID><![CDATA[93ED20495884422A9CA628215BECE605]]></AD_ELEMENT_ID>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISSESSIONATTR><![CDATA[N]]></ISSESSIONATTR>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISSECONDARYKEY><![CDATA[N]]></ISSECONDARYKEY>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISDESENCRYPTABLE><![CDATA[N]]></ISDESENCRYPTABLE>
+<!--7D036A285F8E4475B0B613E421381C25--> <DEVELOPMENTSTATUS><![CDATA[RE]]></DEVELOPMENTSTATUS>
+<!--7D036A285F8E4475B0B613E421381C25--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--7D036A285F8E4475B0B613E421381C25--> <POSITION><![CDATA[13]]></POSITION>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISTRANSIENT><![CDATA[N]]></ISTRANSIENT>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISAUTOSAVE><![CDATA[Y]]></ISAUTOSAVE>
+<!--7D036A285F8E4475B0B613E421381C25--> <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW>
+<!--7D036A285F8E4475B0B613E421381C25--> <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION>
+<!--7D036A285F8E4475B0B613E421381C25--> <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
+<!--7D036A285F8E4475B0B613E421381C25--></AD_COLUMN>
+
<!--7D4D8EFED3404B81883AB4A6E385B3FF--><AD_COLUMN>
<!--7D4D8EFED3404B81883AB4A6E385B3FF--> <AD_COLUMN_ID><![CDATA[7D4D8EFED3404B81883AB4A6E385B3FF]]></AD_COLUMN_ID>
<!--7D4D8EFED3404B81883AB4A6E385B3FF--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src-db/database/sourcedata/AD_ELEMENT.xml Thu Dec 04 12:12:34 2014 +0100
+++ b/src-db/database/sourcedata/AD_ELEMENT.xml Wed Dec 10 14:09:54 2014 +0100
@@ -29366,6 +29366,18 @@
<!--93E29B3166F44C4D8C4BD58A5BC61AE9--> <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
<!--93E29B3166F44C4D8C4BD58A5BC61AE9--></AD_ELEMENT>
+<!--93ED20495884422A9CA628215BECE605--><AD_ELEMENT>
+<!--93ED20495884422A9CA628215BECE605--> <AD_ELEMENT_ID><![CDATA[93ED20495884422A9CA628215BECE605]]></AD_ELEMENT_ID>
+<!--93ED20495884422A9CA628215BECE605--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--93ED20495884422A9CA628215BECE605--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--93ED20495884422A9CA628215BECE605--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--93ED20495884422A9CA628215BECE605--> <COLUMNNAME><![CDATA[Isconversionmatching]]></COLUMNNAME>
+<!--93ED20495884422A9CA628215BECE605--> <NAME><![CDATA[Conversion Matching]]></NAME>
+<!--93ED20495884422A9CA628215BECE605--> <PRINTNAME><![CDATA[Conversion Matching]]></PRINTNAME>
+<!--93ED20495884422A9CA628215BECE605--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--93ED20495884422A9CA628215BECE605--> <ISGLOSSARY><![CDATA[N]]></ISGLOSSARY>
+<!--93ED20495884422A9CA628215BECE605--></AD_ELEMENT>
+
<!--9438EBF8347047E2B34A9861FECB2BAF--><AD_ELEMENT>
<!--9438EBF8347047E2B34A9861FECB2BAF--> <AD_ELEMENT_ID><![CDATA[9438EBF8347047E2B34A9861FECB2BAF]]></AD_ELEMENT_ID>
<!--9438EBF8347047E2B34A9861FECB2BAF--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src-db/database/sourcedata/AD_FIELD.xml Thu Dec 04 12:12:34 2014 +0100
+++ b/src-db/database/sourcedata/AD_FIELD.xml Wed Dec 10 14:09:54 2014 +0100
@@ -272266,6 +272266,32 @@
<!--C68C5F633788449BB2CB69AB15E6FCA4--> <EM_OBUIAPP_SHOWSUMMARY><![CDATA[N]]></EM_OBUIAPP_SHOWSUMMARY>
<!--C68C5F633788449BB2CB69AB15E6FCA4--></AD_FIELD>
+<!--C691203089A74263854843FDB9A73826--><AD_FIELD>
+<!--C691203089A74263854843FDB9A73826--> <AD_FIELD_ID><![CDATA[C691203089A74263854843FDB9A73826]]></AD_FIELD_ID>
+<!--C691203089A74263854843FDB9A73826--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--C691203089A74263854843FDB9A73826--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--C691203089A74263854843FDB9A73826--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--C691203089A74263854843FDB9A73826--> <NAME><![CDATA[Conversion Matching]]></NAME>
+<!--C691203089A74263854843FDB9A73826--> <ISCENTRALLYMAINTAINED><![CDATA[Y]]></ISCENTRALLYMAINTAINED>
+<!--C691203089A74263854843FDB9A73826--> <AD_TAB_ID><![CDATA[89A2764B004B4D95B09C9F4C1CB56BE5]]></AD_TAB_ID>
+<!--C691203089A74263854843FDB9A73826--> <AD_COLUMN_ID><![CDATA[7D036A285F8E4475B0B613E421381C25]]></AD_COLUMN_ID>
+<!--C691203089A74263854843FDB9A73826--> <IGNOREINWAD><![CDATA[N]]></IGNOREINWAD>
+<!--C691203089A74263854843FDB9A73826--> <ISDISPLAYED><![CDATA[Y]]></ISDISPLAYED>
+<!--C691203089A74263854843FDB9A73826--> <DISPLAYLENGTH><![CDATA[1]]></DISPLAYLENGTH>
+<!--C691203089A74263854843FDB9A73826--> <ISREADONLY><![CDATA[N]]></ISREADONLY>
+<!--C691203089A74263854843FDB9A73826--> <SEQNO><![CDATA[50]]></SEQNO>
+<!--C691203089A74263854843FDB9A73826--> <ISSAMELINE><![CDATA[N]]></ISSAMELINE>
+<!--C691203089A74263854843FDB9A73826--> <ISFIELDONLY><![CDATA[N]]></ISFIELDONLY>
+<!--C691203089A74263854843FDB9A73826--> <ISENCRYPTED><![CDATA[N]]></ISENCRYPTED>
+<!--C691203089A74263854843FDB9A73826--> <SHOWINRELATION><![CDATA[N]]></SHOWINRELATION>
+<!--C691203089A74263854843FDB9A73826--> <ISFIRSTFOCUSEDFIELD><![CDATA[N]]></ISFIRSTFOCUSEDFIELD>
+<!--C691203089A74263854843FDB9A73826--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--C691203089A74263854843FDB9A73826--> <STARTINODDCOLUMN><![CDATA[N]]></STARTINODDCOLUMN>
+<!--C691203089A74263854843FDB9A73826--> <STARTNEWLINE><![CDATA[N]]></STARTNEWLINE>
+<!--C691203089A74263854843FDB9A73826--> <ISSHOWNINSTATUSBAR><![CDATA[N]]></ISSHOWNINSTATUSBAR>
+<!--C691203089A74263854843FDB9A73826--> <EM_OBUIAPP_SHOWSUMMARY><![CDATA[N]]></EM_OBUIAPP_SHOWSUMMARY>
+<!--C691203089A74263854843FDB9A73826--></AD_FIELD>
+
<!--C691E8B3722A429B9F2F54037B1A950B--><AD_FIELD>
<!--C691E8B3722A429B9F2F54037B1A950B--> <AD_FIELD_ID><![CDATA[C691E8B3722A429B9F2F54037B1A950B]]></AD_FIELD_ID>
<!--C691E8B3722A429B9F2F54037B1A950B--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src/org/openbravo/common/inserters/LCMatchFromInvoiceInserter.java Thu Dec 04 12:12:34 2014 +0100
+++ b/src/org/openbravo/common/inserters/LCMatchFromInvoiceInserter.java Wed Dec 10 14:09:54 2014 +0100
@@ -24,6 +24,7 @@
import org.openbravo.base.util.Check;
import org.openbravo.dal.service.OBDal;
import org.openbravo.model.common.invoice.InvoiceLine;
+import org.openbravo.model.materialmgmt.cost.LCMatched;
import org.openbravo.model.materialmgmt.cost.LandedCostType;
import org.openbravo.service.datasource.hql.HQLInserterQualifier;
import org.openbravo.service.datasource.hql.HqlInserter;
@@ -51,7 +52,8 @@
strWhereClause += " and lct." + LandedCostType.PROPERTY_ACCOUNT + ".id = :glitem ";
queryNamedParameters.put("glitem", invLine.getAccount().getId());
}
+ strWhereClause += " and ( lcm is null or lcm." + LCMatched.PROPERTY_ISCONVERSIONMATCHING
+ + "=false) ";
return strWhereClause;
}
-
}
--- a/src/org/openbravo/costing/LCCostMatchFromInvoiceHandler.java Thu Dec 04 12:12:34 2014 +0100
+++ b/src/org/openbravo/costing/LCCostMatchFromInvoiceHandler.java Wed Dec 10 14:09:54 2014 +0100
@@ -19,6 +19,7 @@
package org.openbravo.costing;
import java.math.BigDecimal;
+import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@@ -26,12 +27,17 @@
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
+import org.hibernate.criterion.Restrictions;
import org.openbravo.base.provider.OBProvider;
import org.openbravo.client.application.process.BaseProcessActionHandler;
import org.openbravo.dal.core.OBContext;
+import org.openbravo.dal.service.OBCriteria;
import org.openbravo.dal.service.OBDal;
import org.openbravo.erpCommon.utility.OBMessageUtils;
import org.openbravo.financial.FinancialUtils;
+import org.openbravo.model.common.currency.ConversionRate;
+import org.openbravo.model.common.currency.ConversionRateDoc;
+import org.openbravo.model.common.currency.Currency;
import org.openbravo.model.common.invoice.InvoiceLine;
import org.openbravo.model.materialmgmt.cost.LCMatched;
import org.openbravo.model.materialmgmt.cost.LandedCostCost;
@@ -55,7 +61,9 @@
final InvoiceLine il = OBDal.getInstance().get(InvoiceLine.class, strInvoiceLineId);
List<String> existingMatchings = new ArrayList<String>();
for (LCMatched invmatch : il.getLandedCostMatchedList()) {
- existingMatchings.add(invmatch.getId());
+ if (invmatch.isConversionmatching()) {
+ existingMatchings.add(invmatch.getId());
+ }
}
JSONArray selectedLines = jsonparams.getJSONObject("LCCosts").getJSONArray("_selection");
@@ -116,6 +124,7 @@
match = OBDal.getInstance().get(LCMatched.class, strLCMatchedId);
existingMatchings.remove(strLCMatchedId);
}
+
if (isMatched) {
continue;
}
@@ -138,6 +147,37 @@
lcCost.setMatchingAdjusted(isMatchingAdjusted);
OBDal.getInstance().save(lcCost);
}
+
+ final OBCriteria<ConversionRateDoc> conversionRateDoc = OBDal.getInstance().createCriteria(
+ ConversionRateDoc.class);
+ conversionRateDoc.add(Restrictions.eq(ConversionRateDoc.PROPERTY_INVOICE, il.getInvoice()));
+ ConversionRateDoc invoiceconversionrate = (ConversionRateDoc) conversionRateDoc
+ .uniqueResult();
+
+ Currency currency = lcc.getOrganization().getCurrency() != null ? lcc.getOrganization()
+ .getCurrency() : lcc.getOrganization().getClient().getCurrency();
+ ConversionRate landedCostrate = FinancialUtils.getConversionRate(lcc.getLandedCost()
+ .getReferenceDate(), lcc.getCurrency(), currency, lcc.getOrganization(), lcc.getClient());
+
+ if (invoiceconversionrate != null
+ && invoiceconversionrate.getRate() != landedCostrate.getMultipleRateBy()) {
+ amount = amount
+ .multiply(invoiceconversionrate.getRate())
+ .subtract(amount.multiply(landedCostrate.getMultipleRateBy()))
+ .divide(landedCostrate.getMultipleRateBy(), currency.getStandardPrecision().intValue(),
+ RoundingMode.HALF_UP);
+ LCMatched lcmCm = OBProvider.getInstance().get(LCMatched.class);
+ lcmCm.setOrganization(lcc.getOrganization());
+ lcmCm.setLandedCostCost(lcc);
+ lcmCm.setAmount(amount);
+ lcmCm.setInvoiceLine(il);
+ lcmCm.setConversionmatching(true);
+ OBDal.getInstance().save(lcmCm);
+
+ lcc.setMatched(Boolean.FALSE);
+ lcc.setProcessed(Boolean.FALSE);
+ lcc.setMatchingAdjusted(true);
+ }
OBDal.getInstance().flush();
if (processMatching) {
message = LCMatchingProcess.doProcessLCMatching(lcCost);
--- a/src/org/openbravo/costing/LandedCostProcess.java Thu Dec 04 12:12:34 2014 +0100
+++ b/src/org/openbravo/costing/LandedCostProcess.java Wed Dec 10 14:09:54 2014 +0100
@@ -19,6 +19,7 @@
package org.openbravo.costing;
import java.math.BigDecimal;
+import java.math.RoundingMode;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
@@ -42,6 +43,9 @@
import org.openbravo.dal.service.OBDal;
import org.openbravo.dal.service.OBQuery;
import org.openbravo.erpCommon.utility.OBMessageUtils;
+import org.openbravo.financial.FinancialUtils;
+import org.openbravo.model.common.currency.ConversionRate;
+import org.openbravo.model.common.currency.ConversionRateDoc;
import org.openbravo.model.common.currency.Currency;
import org.openbravo.model.materialmgmt.cost.CostAdjustment;
import org.openbravo.model.materialmgmt.cost.CostAdjustmentLine;
@@ -208,6 +212,7 @@
hql.append(" from " + LCReceiptLineAmt.ENTITY_NAME + " as rla");
hql.append(" join rla." + LCReceiptLineAmt.PROPERTY_LANDEDCOSTRECEIPT + " as rl");
hql.append(" where rl." + LCReceipt.PROPERTY_LANDEDCOST + " = :lc");
+ hql.append(" and rla." + LCReceiptLineAmt.PROPERTY_ISMATCHINGADJUSTMENT + " = false ");
hql.append(" group by rla." + LCReceiptLineAmt.PROPERTY_LANDEDCOSTCOST + ".currency.id");
hql.append(" , rla." + LCReceipt.PROPERTY_GOODSSHIPMENTLINE + ".id");
hql.append(" order by trxprocessdate, amt");
@@ -278,6 +283,39 @@
lcm.setInvoiceLine(lcc.getInvoiceLine());
OBDal.getInstance().save(lcm);
+ final OBCriteria<ConversionRateDoc> conversionRateDoc = OBDal.getInstance().createCriteria(
+ ConversionRateDoc.class);
+ conversionRateDoc.add(Restrictions.eq(ConversionRateDoc.PROPERTY_INVOICE, lcm.getInvoiceLine()
+ .getInvoice()));
+ ConversionRateDoc invoiceconversionrate = (ConversionRateDoc) conversionRateDoc.uniqueResult();
+ Currency currency = lcc.getOrganization().getCurrency() != null ? lcc.getOrganization()
+ .getCurrency() : lcc.getOrganization().getClient().getCurrency();
+ ConversionRate landedCostrate = FinancialUtils.getConversionRate(lcc.getLandedCost()
+ .getReferenceDate(), lcc.getCurrency(), currency, lcc.getOrganization(), lcc.getClient());
+
+ if (invoiceconversionrate != null
+ && invoiceconversionrate.getRate() != landedCostrate.getMultipleRateBy()) {
+ BigDecimal amount = lcc
+ .getAmount()
+ .multiply(invoiceconversionrate.getRate())
+ .subtract(lcc.getAmount().multiply(landedCostrate.getMultipleRateBy()))
+ .divide(landedCostrate.getMultipleRateBy(), currency.getStandardPrecision().intValue(),
+ RoundingMode.HALF_UP);
+ LCMatched lcmCm = OBProvider.getInstance().get(LCMatched.class);
+ lcmCm.setOrganization(lcc.getOrganization());
+ lcmCm.setLandedCostCost(lcc);
+ lcmCm.setAmount(amount);
+ lcmCm.setInvoiceLine(lcc.getInvoiceLine());
+ lcmCm.setConversionmatching(true);
+ OBDal.getInstance().save(lcmCm);
+
+ lcc.setMatched(Boolean.FALSE);
+ lcc.setProcessed(Boolean.FALSE);
+ lcc.setMatchingAdjusted(true);
+ OBDal.getInstance().flush();
+ LCMatchingProcess.doProcessLCMatching(lcc);
+ }
+
lcc.setMatched(Boolean.TRUE);
lcc.setProcessed(Boolean.TRUE);
lcc.setMatchingAmount(lcc.getAmount());