0007957: Multi currency issues in some processes and tables

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0007957

Openbravo ERP

01. General setup

public

2009-03-05 09:21

2009-03-26 18:44

Reporter

rafaroda

Assigned To

rafaroda

Priority

immediate

Severity

critical

Reproducibility

always

Status

closed

Resolution

fixed

Platform

OS Version

Ubuntu 7.10

Product Version

Target Version

Fixed in Version

2.40MP4

Merge Request Status

Review Assigned To

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0007957: Multi currency issues in some processes and tables

Description

There are multi currency issues in the following processes and tables:
   1. AD_CLIENT:
         1. PROBLEM: client does not have a "base" currency. This "base" currency is taken in some cases from the accounting schema of the client. This will not be valid anymore since one Client could have more than one accounting schema.
         2. SOLUTION: define a base currency for a client.
         3. IMPLEMENTATION: AD_CLIENT table [3] should have a mandatory Client_Currency column. 'Initial Client Setup' should fill this column (maybe based of the currency of the Chart of Accounts?). For existing clients, there should be a script that fills this column (maybe based on client's primary accounting schema currency?). This Base Currency should not be updateable, even if a client changes its accounting schemas.
   2. M_COSTING:
         1. PROBLEM: M_COSTING table [4] has two columns (Price and Cost) that do not have a corresponding currency. Three processes update these values without taking into account any currency: M_GENERATE_AVERAGE_COSTS [5], M_GENERATE_STANDARD_COSTS [6] and MA_PRODUCTION_COST [7].
         2. SOLUTION: assume that Price and Cost values are in the Base Currency of the Client.
         3. IMPLEMENTATION: Re-code M_GENERATE_AVERAGE_COSTS, M_GENERATE_STANDARD_COSTS and MA_PRODUCTION_COST processes in order that they do a conversion into the Client base currency before inserting/updating values into Price and Cost columns of M_COSTING. Additionally, for M_GENERATE_AVERAGE_COSTS process, make the corresponding conversions from invoices, orders or shipments currencies to client's base currency.
   3. MA_SEQUENCEPRODUCT:
         1. PROBLEM: MA_SEQUENCEPRODUCT table [8] (Production Management || Transactions || Process Plan || Process Plan >> Version >> Operation >> I/O Products ) has a COST column that has not an associated currency. MA_STANDARD_COST_SEQUENCE process [9] updates this value.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to MA_STANDARD_COST_SEQUENCE process in order to make conversions into the Client base currency.
   4. MA_COSTCENTER:
         1. PROBLEM: MA_COSTCENTER table [10] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   5. MA_COSTCENTER_VERSION:
         1. PROBLEM: MA_COSTCENTER_VERSION table [11] (Production Management || Setup || Cost Center || Cost Center >> Version) has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   6. MA_INDIRECT_COST_VALUE:
         1. PROBLEM: MA_INDIRECT_COST_VALUE table [12] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   7. MA_MACHINE_COST:
         1. PROBLEM: MA_MACHINE_COST table [13] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   8. MA_PL_INVOICELINE:
         1. PROBLEM: MA_PL_INVOICELINE table [14] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   9. C_SALARY_CATEGORY_COST:
         1. PROBLEM: C_SALARY_CATEGORY_COST table [15] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
  10. M_PRODUCTIONLINE:
         1. PROBLEM: M_PRODUCTIONLINE table [16] has a CALCCOST column that has not an associated currency. Find out which processes update/insert values in CALCCOST.
         2. SOLUTION: assume that CalcCost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
  11. M_INOUT:
         1. PROBLEM: M_INOUT table [17] (Procurement Management || Transactions || Goods Receipt || Header) has a FREIGHTAMT column that has not an associated currency. This column can be set via a process or calculated based on the values inside Warehouse Management || Setup || Shipping Company || Shipping Company >> Freight.
         2. SOLUTION: add a new non-mandatory column FREIGHT_CURRENCY to M_INOUT in order to indicate the currency of the freight amount.
         3. IMPLEMENTATION: if the freight amount is set manually one could select the currency. If it is calculated via the process, it should update the currency field.
  12. M_OFFER:
         1. PROBLEM: M_OFFER table [18] (Master Data Management || Pricing || Price Adjustments || Adjustments ) has ADDAMT and FIXED columns that have not an associated currency. These columns are used for making fixed price adjustments. The problem is that, taking a fixed discount of 50, if one customer A has a price list in EUR and a product's price is 250 €, he will get it for 150 €. If for the same product, a customer B has a price list in pesos and product's price is 10.000 Pesos, he will get it for 9.950 Pesos.
         2. SOLUTION: assume that the user setting up price adjustments is smart enough to take care of these things and registers different price adjustments for different price lists (specially if they have different currencies). Second solution will require to add a currency column to M_OFFER table to indicate the currency in which this price adjustment is done.
         3. IMPLEMENTATION: the second solution would require to review the processes that calculate the price adjustments and make the conversions between price list currency and price adjustment currency.
  13. Default currency for an user:
         1. PROBLEM: when a user accesses a multi currency report, he wants its "defect" currency to be selected by default in the currency window. Which is this "defect" currency?
         2. SOLUTION: take the base currency of the Client as the "defect" curerncy of the user?

There are other tables with a COST column:

    * M_PRODUCT -> COSTSTD
    * C_BPARTNER -> SO_CREDITLIMIT SO_CREDITUSED ACQUSITIONCOST
    * C_CAMPAIGN -> COSTS
    * I_PRODUCT -> COSTPERORDER
    * M_PRODUCT_PO -> COSTPERORDER --> HAS CURRENCY
    * C_PROJECT -> SERVCOST --> HAS CURRENCY
    * M_PRODUCTIONLINE -> CALCCOST COMPONENTCOST
    * MA_PRODUCTIONPLAN -> CALCCOST
    * MA_PL_EMPLOYEE -> CALCCOST
    * MA_PL_IC -> CALCCOST
    * MA_PL_MACHINE -> CALCCOST
    * MA_SEQUENCE_EMPLOYEE -> CALCCOST
    * MA_SEQUENCE_IC -> CALCCOST
    * MA_SEQUENCE_MACHINE -> CALCCOST
    * MA_SEQUENCE -> COSTCENTERCOST
    * MA_WRPHASEPRODUCT -> COMPONENTCOST
    * R_RESOURCEPLANCOST -> PLANCOST

[3] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/AD_CLIENT.xml [^]
[4] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/M_COSTING.xml [^]
[5] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml [^]
[6] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/M_GENERATE_STANDARD_COSTS.xml [^]
[7] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/MA_PRODUCTION_COST.xml [^]
[8] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_SEQUENCEPRODUCT.xml [^]
[9] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/MA_STANDARD_COST_SEQUENCE.xml [^]
[10] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_COSTCENTER.xml [^]
[11] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_COSTCENTER_VERSION.xml [^]
[12] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_INDIRECT_COST_VALUE.xml [^]
[13] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_MACHINE_COST.xml [^]
[14] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_PL_INVOICELINE.xml [^]
[15] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/C_SALARY_CATEGORY_COST.xml [^]
[16] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/M_PRODUCTIONLINE.xml [^]
[17] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/M_INOUT.xml [^]
[18] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/M_OFFER.xml [^]

Steps To Reproduce

Proposed Solution

1) Create the currency mandatory column in AD_CLIENT table (take care of sampledata and demo data). Add an onCreateDefault clause similar to:
<column name="C_CURRENCY_ID" primaryKey="false" required="true" type="VARCHAR" size="32" autoIncrement="false">
<default/>
<onCreateDefault>CASE WHEN AD_CLIENT_ID == 0 THEN '102' ELSE (SELECT C_ACCTSCHEMA.C_CURRENCY_ID FROM C_ACCTSCHEMA, AD_CLIENTINFO WHERE AD_CLIENTINFO.C_ACCTSCHEMA1_ID = C_ACCTSCHEMA.C_ACCTSCHEMA_ID) END</onCreateDefault>
</column>
2) Make changes in MA_STANDARD_COST_SEQUENCE process.
3) Make changes in M_GENERATE_AVERAGE_COSTS process.
4) Add a currency column to M_INOUT table to indicate the currency of FREIGHTAMT column. Modify the process that calculates this freight amount to update the value of this currency column.

Additional Information

Tags

No tags attached.

Relationships

depends on	backport	0008096	closed	marvintm	DBSourceManager: Constraint enabling should be done after onCreateDefault
related to	backport	0008246	closed	AinhoaPagola	Initial Client Setup fails - ERROR: null value in column "c_currency_id" violates not-null constraint
related to	defect	0010089	closed	rafaroda	on 2.40 the pl-function ma_standard_cost_sequence does not compile (call non-existant other pl-function)
blocks	defect	0005564	closed	rafaroda	Multi currency issues in some processes and tables
blocks	backport	0007956	closed	rafaroda	Sales Reports do not handle currencies
blocks	backport	0007305	closed	rafaroda	Procurement reports do not handle currencies