Currency problem in choosing the appropriate supplier in requirement

[Shereen]

Hi
I have the following problem.

For a certain product I've many suppliers those suppliers have their prices in different currencies .
When I go to the order manager and then to the requirements
when I choose the product the system automatically chooses the supplier for me but IT DOESNT CONSIDER CURRENCY it just finds the lowest price but IT CONSIDERS the  ordered quantity

so finally it chooses the lowest price of the suitable ordered quantity without currency check

this problem in all versions I used 9.04 and 10.04

Any suggestions please? has anyone implemented something like a currency converter?

[BJ Freeman]

what needs to be implemented is a look up of current currency rates then
add that into the code for the comparison.
if you would like to contribute please read.
https://cwiki.apache.org/confluence/display/OFBADMIN/OFBiz+Contributors+Best+Practices

Sheree sent the following on 9/23/2010 3:58 AM:

>
> Hi
> I have the following problem.
>
> For a certain product I've many suppliers those suppliers have their prices
> in different currencies .
> When I go to the order manager and then to the requirements
> when I choose the product the system automatically chooses the supplier for
> me but IT DOESNT CONSIDER CURRENCY it just finds the lowest price but IT
> CONSIDERS the  ordered quantity
>
> so finally it chooses the lowest price of the suitable ordered quantity
> without currency check
>
> this problem in all versions I used 9.04 and 10.04
>
> Any suggestions please? has anyone implemented something like a currency
> converter?
>

[Shereen]

Thanks I'd be like to contribute. I just wanted to make sure that this function doesn't exist yet and I wasn't missing something

[BJ Freeman]

it has been discussed before but the hang up is finding a free source
for getting currency rates, that can be put in the code.

Shereen sent the following on 9/23/2010 4:36 AM:
>
> Thanks I'd be like to contribute. I just wanted to make sure that this
> function doesn't exist yet and I wasn't missing something

[Shereen]

Useful information thanks
I think I'll be back to the technical problem in a while and I'll have to fix that problem.
because I'm just testing all functionality and try to decide what needs to be done for my system.

[Shereen]

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