I wanted to make a simple regarding the requirement problem of assigning the
requirement to the supplier that has the least price regardless of the
difference in currencies.
I created a field in the entity supplier product called priceDefaultCurrency
which will hold the price in my default currency.
I've decided to use the table in the accounting that holds foreign currency
changes and idea is when we automatically assign a requirement we'll check
if the price is the default say USD or not
if it's in USD then priceDefaultCurrency will be equal the lastPrice and if
not the I'll get the record that holds the conversion rate from the table
uomConversionDated. and get the conversion factor and multiply it to the
price and this will be the value of priceDefaultCurrency
I'll depend in my code that there exists for example just oe rate from say
EUR to USD then I can fix that.
I've tried to that in minilang but I've an exception
the case where the price is in USD the code goes right and the
priceDefaultCurrency is updated right
the case where the price isn't in USD it gve me exception and the
priceDefaultCurrency is 0
here's the code I've it in the service autoAssignRequirementToSupplier
<simple-method method-name="autoAssignRequirementToSupplier"
short-description="If the requirement is a product requirement (purchasing)
try to assign it to the primary supplier">
<entity-one entity-name="Requirement" value-field="requirement"
auto-field-map="true"/>
<check-errors/>
<if-compare field="requirement.requirementTypeId" operator="equals"
value="PRODUCT_REQUIREMENT">
<if-not-empty field="requirement.productId">
<entity-condition entity-name="SupplierProduct"
list="supplierProducts">
<condition-list>
<condition-expr field-name="productId"
from-field="requirement.productId"/>
<condition-expr field-name="minimumOrderQuantity"
from-field="requirement.quantity" operator="less-equals"/>
</condition-list>
<order-by field-name="lastPrice"/>
<order-by field-name="supplierPrefOrderId"/>
</entity-condition>
<iterate entry="supplierProductsRecord"
list="supplierProducts">
<if-compare field="supplierProductsRecord.currencyUomId"
value="USD" operator="not-equals">
<entity-condition list="rateList"
entity-name="UomConversionDated">
<condition-list combine="and">
<condition-expr field-name="uomId"
operator="equals"
from-field="supplierProductsRecord.currencyUomId"/>
</condition-list>
</entity-condition>
<first-from-list entry="firstEntry" list="rateList"/>
<calculate
field="supplierProductsRecord.priceDefaultCurrency">
<calcop operator="multiply">
<calcop operator="get"
field="supplierProductsRecord.lastPrice"/>
<calcop operator="get"
field="firstEntry.conversionRate"/>
</calcop>
</calculate>
<store-value value-field="supplierProductsRecord"/>
<else>
<set field="supplierProductsRecord.priceDefaultCurrency"
from-field="supplierProductsRecord.lastPrice"/>
<store-value value-field="supplierProductsRecord"/>
</else>
</if-compare>
</iterate>
<filter-list-by-date list="supplierProducts"
valid-date="requirement.requiredByDate" from-field-name="availableFromDate"
thru-field-name="availableThruDate"/>
<first-from-list list="supplierProducts"
entry="supplierProduct"/>
<if-not-empty field="supplierProduct.partyId">
<make-value value-field="requirementSupplier"
entity-name="RequirementRole"/>
<set field="requirementSupplier.requirementId"
from-field="requirement.requirementId"/>
<set field="requirementSupplier.partyId"
from-field="supplierProduct.partyId"/>
<set field="requirementSupplier.roleTypeId"
value="SUPPLIER"/>
<now-timestamp field="requirementSupplier.fromDate"/>
<create-value value-field="requirementSupplier"/>
</if-not-empty>
</if-not-empty>
</if-compare>
</simple-method>
this the whole service code I know it doesn't do the assignmet in the right
way but at least I need to update the field priceDefaultCurrency
and the exception is Exception: org.ofbiz.entity.GenericDataSourceException
Message: SQL Exception while executing the following:SELECT COUNT(1) FROM
(SELEC
T COUNT(DISTINCT *) FROM OFBIZ.REQUIREMENT RQ INNER JOIN
OFBIZ.REQUIREMENT_ROLE
RQR ON RQ.REQUIREMENT_ID = RQR.REQUIREMENT_ID WHERE (RQR.ROLE_TYPE_ID = ?
AND R
Q.STATUS_ID = ? AND RQ.REQUIREMENT_TYPE_ID = ? AND ((RQR.THRU_DATE IS NULL
OR RQ
R.THRU_DATE > ?) AND (RQR.FROM_DATE IS NULL OR RQR.FROM_DATE <= ?))) GROUP
BY RQ
R.PARTY_ID) TEMP_NAME (Syntax error: Encountered "*" at line 1, column 45.)
---- cause
---------------------------------------------------------------------
Exception: java.sql.SQLSyntaxErrorException
Message: Syntax error: Encountered "*" at line 1, column 45.
---- cause
---------------------------------------------------------------------
Exception: org.apache.derby.impl.jdbc.EmbedSQLException
Message: Syntax error: Encountered "*" at line 1, column 45.
---- cause
---------------------------------------------------------------------
Exception: org.apache.derby.iapi.error.StandardException
Message: Syntax error: Encountered "*" at line 1, column 45.
---- stack trace
---------------------------------------------------------------
ERROR 42X01: Syntax error: Encountered "*" at line 1, column 45.
org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalS
tatement(Unknown Source)
org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnecti
--
View this message in context:
http://ofbiz.135035.n4.nabble.com/Currency-problem-in-choosing-the-appropriate-supplier-in-requirement-tp2551814p3061844.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
