Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0007957
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] 01. General setupcriticalalways2009-03-05 09:212009-03-26 18:44
ReporterrafarodaView Statuspublic 
Assigned Torafaroda 
PriorityimmediateResolutionfixedFixed in Version2.40MP4
StatusclosedFix in branch2.40Fixed in SCM revision3d6a6294ac40
ProjectionnoneETAnoneTarget Version
OSLinux 32 bitDatabasePostgreSQLJava version1.5
OS VersionUbuntu 7.10Database version8.3Ant version1.7
Product VersionpiSCM revision8873 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0007957: Multi currency issues in some processes and tables

DescriptionThere 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 [^]
Proposed Solution1) 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.
TagsNo tags attached.
Attached Files

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

-  Notes
(0014777)
hgbot (developer)
2009-03-18 13:31

 Repository: erp/stable/2.40
Changeset: 3e6cc5386f261c48ead873dcf57ab8bcb438c2e2
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Fri Mar 13 17:13:03 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/3e6cc5386f261c48ead873dcf57ab8bcb438c2e2 [^]

Issue 0007957 Modified tables

---
M src-db/database/model/tables/AD_CLIENT.xml
M src-db/database/model/tables/M_INOUT.xml
---
(0014778)
hgbot (developer)
2009-03-18 13:31

 Repository: erp/stable/2.40
Changeset: 8e7ce118506c8f3935eef5675d630a8e0ec898e4
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Fri Mar 13 17:26:34 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/8e7ce118506c8f3935eef5675d630a8e0ec898e4 [^]

Issue 0007957 Modified sample and source data of AD_CLIENT

---
M src-db/database/sampledata/AD_CLIENT.xml
M src-db/database/sourcedata/AD_CLIENT.xml
---
(0014779)
hgbot (developer)
2009-03-18 13:31

 Repository: erp/stable/2.40
Changeset: fe9b886ea349d69ed3e823d8fc66baece5306f08
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Fri Mar 13 18:12:45 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/fe9b886ea349d69ed3e823d8fc66baece5306f08 [^]

Issue 0007957 Modified functions

---
M src-db/database/model/functions/C_CURRENCY_RATE.xml
M src-db/database/model/functions/GENERATE_AVERAGE_COSTS.xml
M src-db/database/model/functions/GET_PRODUCT_PRICE.xml
M src-db/database/model/functions/GET_PRODUCT_PRICE_DATE.xml
M src-db/database/model/functions/GET_PURCHASE_PRICE.xml
M src-db/database/model/functions/MA_STANDARD_COST.xml
M src-db/database/model/functions/MA_STANDARD_COST_SEQUENCE.xml
M src-db/database/model/functions/M_FREIGHT_CALCULATE.xml
A src-db/database/model/functions/C_CURRENCY_ISOSYM.xml
A src-db/database/model/functions/C_CURRENCY_SYMBOL.xml
---
(0014780)
hgbot (developer)
2009-03-18 13:31

 Repository: erp/stable/2.40
Changeset: be1214e99281cf5dc2e02e299d934aa0cfa90f29
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Fri Mar 13 18:29:10 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/be1214e99281cf5dc2e02e299d934aa0cfa90f29 [^]

Issue 0007957 Modified Utility

---
M src/org/openbravo/erpCommon/utility/Utility.java
M src/org/openbravo/erpCommon/utility/Utility_data.xsql
---
(0014781)
hgbot (developer)
2009-03-18 13:32

 Repository: erp/stable/2.40
Changeset: fee07653f206ccb8ad181365ab3e374e42f72a55
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Mon Mar 16 09:20:36 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/fee07653f206ccb8ad181365ab3e374e42f72a55 [^]

Issue 0007957 Modified AD_REFERENCE

---
M src-db/database/sourcedata/AD_REFERENCE.xml
---
(0014782)
hgbot (developer)
2009-03-18 13:32

 Repository: erp/stable/2.40
Changeset: 43237535501763c8ecb9a4a3d75d92aad6f1dc32
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Mon Mar 16 09:21:14 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/43237535501763c8ecb9a4a3d75d92aad6f1dc32 [^]

Issue 0007957 Modified sourcedata

---
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_ELEMENT.xml
M src-db/database/sourcedata/AD_FIELD.xml
---
(0014783)
hgbot (developer)
2009-03-18 13:32

 Repository: erp/stable/2.40
Changeset: 51f14458fd50da0783ac37f3a3b94fee35bdf8e9
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Mon Mar 16 09:24:09 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/51f14458fd50da0783ac37f3a3b94fee35bdf8e9 [^]

Fixes bug 0007957 Modified messaging

---
M src-db/database/sourcedata/AD_MESSAGE.xml
---
(0015020)
hgbot (developer)
2009-03-26 18:24

 Repository: erp/stable/2.40
Changeset: 3d6a6294ac4099672f4d3ccaa02d0a65451acd61
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Thu Mar 26 18:22:35 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/3d6a6294ac4099672f4d3ccaa02d0a65451acd61 [^]

Issue 0007957 Modified sample data of M_INOUT

---
M src-db/database/sampledata/M_INOUT.xml
---

- Issue History
Date Modified Username Field Change
2009-03-05 09:21 rafaroda New Issue
2009-03-05 09:21 rafaroda Assigned To => rafaroda
2009-03-05 09:21 rafaroda Regression testing => No
2009-03-05 09:21 rafaroda Issue generated from 0005564
2009-03-05 09:21 rafaroda Relationship added blocks 0005564
2009-03-05 09:22 rafaroda Status new => scheduled
2009-03-05 09:22 rafaroda fix_in_branch => 2.40
2009-03-05 09:22 rafaroda Relationship added blocks 0007956
2009-03-05 09:24 rafaroda Relationship added blocks 0007305
2009-03-12 15:51 rafaroda Relationship added depends on 0008096
2009-03-18 13:31 hgbot Checkin
2009-03-18 13:31 hgbot Note Added: 0014777
2009-03-18 13:31 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/stable/2.40/rev/3e6cc5386f261c48ead873dcf57ab8bcb438c2e2 [^]
2009-03-18 13:31 hgbot Checkin
2009-03-18 13:31 hgbot Note Added: 0014778
2009-03-18 13:31 hgbot Fixed in SCM revision http://code.openbravo.com/erp/stable/2.40/rev/3e6cc5386f261c48ead873dcf57ab8bcb438c2e2 [^] => http://code.openbravo.com/erp/stable/2.40/rev/8e7ce118506c8f3935eef5675d630a8e0ec898e4 [^]
2009-03-18 13:31 hgbot Checkin
2009-03-18 13:31 hgbot Note Added: 0014779
2009-03-18 13:31 hgbot Fixed in SCM revision http://code.openbravo.com/erp/stable/2.40/rev/8e7ce118506c8f3935eef5675d630a8e0ec898e4 [^] => http://code.openbravo.com/erp/stable/2.40/rev/fe9b886ea349d69ed3e823d8fc66baece5306f08 [^]
2009-03-18 13:31 hgbot Checkin
2009-03-18 13:31 hgbot Note Added: 0014780
2009-03-18 13:31 hgbot Fixed in SCM revision http://code.openbravo.com/erp/stable/2.40/rev/fe9b886ea349d69ed3e823d8fc66baece5306f08 [^] => http://code.openbravo.com/erp/stable/2.40/rev/be1214e99281cf5dc2e02e299d934aa0cfa90f29 [^]
2009-03-18 13:32 hgbot Checkin
2009-03-18 13:32 hgbot Note Added: 0014781
2009-03-18 13:32 hgbot Fixed in SCM revision http://code.openbravo.com/erp/stable/2.40/rev/be1214e99281cf5dc2e02e299d934aa0cfa90f29 [^] => http://code.openbravo.com/erp/stable/2.40/rev/fee07653f206ccb8ad181365ab3e374e42f72a55 [^]
2009-03-18 13:32 hgbot Checkin
2009-03-18 13:32 hgbot Note Added: 0014782
2009-03-18 13:32 hgbot Fixed in SCM revision http://code.openbravo.com/erp/stable/2.40/rev/fee07653f206ccb8ad181365ab3e374e42f72a55 [^] => http://code.openbravo.com/erp/stable/2.40/rev/43237535501763c8ecb9a4a3d75d92aad6f1dc32 [^]
2009-03-18 13:32 hgbot Checkin
2009-03-18 13:32 hgbot Note Added: 0014783
2009-03-18 13:32 hgbot Status scheduled => resolved
2009-03-18 13:32 hgbot Resolution open => fixed
2009-03-18 13:32 hgbot Fixed in SCM revision http://code.openbravo.com/erp/stable/2.40/rev/43237535501763c8ecb9a4a3d75d92aad6f1dc32 [^] => http://code.openbravo.com/erp/stable/2.40/rev/51f14458fd50da0783ac37f3a3b94fee35bdf8e9 [^]
2009-03-24 11:00 rafaroda Relationship added related to 0008246
2009-03-24 11:01 psarobe Fixed in Version => 2.40MP4
2009-03-26 18:24 hgbot Checkin
2009-03-26 18:24 hgbot Note Added: 0015020
2009-03-26 18:24 hgbot Fixed in SCM revision http://code.openbravo.com/erp/stable/2.40/rev/51f14458fd50da0783ac37f3a3b94fee35bdf8e9 [^] => http://code.openbravo.com/erp/stable/2.40/rev/3d6a6294ac4099672f4d3ccaa02d0a65451acd61 [^]
2009-03-26 18:44 psarobe Status resolved => closed
2009-07-28 15:32 shuehner Relationship added related to 0010089

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker