Projects:Multi Currency Reports Review/Technical Documentation

Contents 1 Multi Currency Reports Review - Technical Documentation 1.1 Overview 1.2 Current status 1.3 Technical design 1.3.1 Currency window 1.3.2 C_CURRENCY_SYMBOL function 1.3.3 C_CURRENCY_ISOSYM function 1.3.4 Application Dictionary filter window 1.3.5 HTML filter window 1.3.6 JRXML report 1.3.7 HTML report 1.3.8 FO report 1.4 Examples 1.4.1 Sales Dimensional Report 1.4.1.1 HTML filter window 1.4.1.2 JRXML report 1.4.2 Purchase Order Report

Multi Currency Reports Review - Technical Documentation

Overview

This article will explain how to proceed in order to add multi currency capabilities to reports.

Current status

At present, Openbravo ERP does not support multi currency in reports so this capability will be implemented from scratch.

Technical design

Let's go through each type of file involved in a report and see which modifications needs to be done.

Important notes:

• Please note that the majority of the reports are manually created (i.e., *.html, *.xml, *.java and *.xsql files are coded manually) but some of them are automatically created through the Application Dictionary of Openbravo ERP.
• In both cases, the final display of the report is done via *.jrxml files that will be modified using iReport 2.0.4 (*.jrxml files generated by iReport 2.0.5 are still not supported by Openbravo ERP).
• Besides, in a few cases, the display is not done via a *.jrxml file but through a coded *.html or *.fo file.

Currency window

In order to add Currency symbol at the right field to Currency window:

• Create a new non mandatory column ISSYMBOLRIGHTSIDE to C_Currency table.
• Log in as System Administrator, add ISSYMBOLRIGHTSIDE to C_Currency table in Application Dictionary || Tables and Columns || Table.
• Add Currency symbol at the right field to Currency tab in Application Dictionary || Windows, Tabs, and Fields || Window >> Tab.
• Run Synchronize Terminology process to fill Description and Help/Comment fields (Application Dictionary || Synchronize Terminology).

C_CURRENCY_SYMBOL function

C_Currency_Symbol function with three parameters (c_currency_id, amount-price-price/m2 and onlysymbol) will:

• Read ISSYMBOLRIGHTSIDE value from C_Currency table for the c_currency_id given.
• Form and return a string like Price Currency Symbol (if ISSYMBOLRIGHTSIDE value is Y) or Currency Symbol Price (if ISSYMBOLRIGHTSIDE value is N).
• If onlysymbol parameter is equal to Y, it will only return Currency Symbol.

C_CURRENCY_ISOSYM function

C_Currency_IsoSym function with one parameter (c_currency_id) will:

• Read ISO_CODE and CURSYMBOL values from C_Currency table for the c_currency_id given.
• Form and return a string like: (ISOCODE-SYMBOL).

Application Dictionary filter window

For instance, Purchase Order Report do not have *.html, *.xml, *.java and *.xsql files manually coded to build the report filter window. Filter window in these cases is coded automatically through the Application Dictionary of Openbravo ERP.

Logged as System Administrator go to Application Dictionary || Report and Process, selected Purchase Order Report. In Parameter tab, create a new record with the following values:

• Name: Currency
• Active: Yes (checked)
• Central Maintenance: Yes (checked)
• Sequence: In each case, select the number to place Currency drop-down list in the right order.
• DB Column Name: C_Currency_ID
• Application Element : C_Currency_ID - Currency
• Reference: TableDir
• Length: 22
• Mandatory: Yes (checked)
• Default Value: @C_Currency_ID@

Save the record and Synchronize Terminology (Application Dictionary || Synchronize Terminology) in order to fill Description and Help/Comment fields.

Compile the changes and see the result in Procurement Management || Analysis Tools || Purchase Order Report:

ant compile.development -Dtab="xx"

HTML filter window

Sales Dimensional Report is one of the multiple examples of report which HTML filter window is coded manually.

Remember that the filter window has to include a drop-down list coded manually that will:

• List all the currency ISO codes.
• Get the System currency as default.
• Display as mandatory (yellow).

HTML

In the corresponding HTML file, add the following HTML cell code where one wants Currency drop-down list to be shown:

<TD class="TitleCell"><SPAN class="LabelText">Currency</SPAN></TD>
<TD class="Combo_ContentCell">
  <SELECT name="inpCurrencyId" id="inpCurrencyId" class="ComboKey Combo_OneCell_width" required="true">
    <OPTION value=""><DIV id="reportC_Currency_ID"></DIV></OPTION>
  </SELECT>
</TD>

Javascript

To ensure that a currency is selected before launching the report, add the following code to validate() Javascript function inside the corresponding HTML. If validate() function does not exist, add all the code below:

<script language="JavaScript" type="text/javascript"> 
function validate(action)
{
var frm = document.frmMain;
//Starting of Currency check
if (frm.inpCurrencyId.value == null || frm.inpCurrencyId.value == "") {
    setWindowElementFocus(frm.inpCurrencyId);
    showJSMessage(7);
    return false;
}
//Ending of Currency check
}
</script>

Make sure that the button that opens the report (usually, HTML Format or PDF Format) enters validate() function:

onclick="openServletNewWindow(..., '''true''', ...);return false;"

XML

Inside the corresponding XML file, add the following code:

<PARAMETER id="paramC_Currency_ID" name="ccurrencyid" attribute="value"/>
<SUBREPORT id="reportC_Currency_ID" name="reportC_Currency_ID" report="org/openbravo/erpCommon/reference/List">
  <ARGUMENT name="parameterListSelected" withId="paramC_Currency_ID"/>
</SUBREPORT>

Java

In the corresponding Java file, add inside public void doPost() (inside the if that gets the command to print the filter window) the following code to get the user's accounting schema currency (replacing ReportSalesDimensionalAnalyzeJR by the name corresponding to the Java class of the report):

//Get user Client's base currency
String strUserCurrencyId = Utility.stringBaseCurrencyId(this, vars.getClient());
 
...
 
String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", "ReportSalesDimensionalAnalyzeJR|currency", strUserCurrencyId);

Still, inside public void doPost () function add strCurrencyId argument to printPageDataSheet() function:

printPageDataSheet(..., '''strCurrencyId''',...);

Add strCurrencyId argument to void printPageDataSheet() function:

void printPageDataSheet(..., '''String strCurrencyId''',....){
...
}

Inside void printPageDataSheet() function add the following code replacing ReportSalesDimensionalAnalyzeJR by the name corresponding to the Java class of the report:

xmlDocument.setParameter("ccurrencyid", strCurrencyId);
try {
   ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_Currency_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportSalesDimensionalAnalyzeJR"), Utility.getContext(this, vars, "#User_Client", "ReportSalesDimensionalAnalyzeJR"), 0);
Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportSalesDimensionalAnalyzeJR", strCurrencyId);
   xmlDocument.setData("reportC_Currency_ID","liststructure", comboTableData.select(false));
   comboTableData = null;
} catch (Exception ex) {
   throw new ServletException(ex);
}

JRXML report

Java

In the corresponding Java file, add inside public void doPost() (inside the if(s) that gets the command(s) to print the report) the following code to get the currency selected in the filter window, that is, the currency to convert to (replacing ReportSalesDimensionalAnalyzeJR by the name corresponding to the Java class of the report):

String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", "ReportSalesDimensionalAnalyzeJR|currency", strUserCurrencyId);
 
...
 
printPageHtml(response, vars, strComparative, strDateFrom, strDateTo, strPartnerGroup, strcBpartnerId, strProductCategory,
strmProductId, strmWarehouseId, strNotShown, strShown,
strDateFromRef, strDateToRef, strOrg, strsalesrepId, strOrder,
strMayor, strMenor, strPartnerSalesrepId, '''strCurrencyId''', "html");
 
...
 
void printPageHtml(HttpServletResponse response, VariablesSecureApp vars, String strComparative,
String strDateFrom, String strDateTo, String strPartnerGroup, String strcBpartnerId, String strProductCategory,
String strmProductId, String strmWarehouseId, String strNotShown, String strShown, String strDateFromRef, String strDateToRef,
String strOrg, String strsalesrepId, String strOrder, String strMayor, String strMenor, String strPartnerSalesrepId,
 '''String strCurrencyId''', String strOutput)

Inside the same Java file, launch the report's select and check if any of the transactions has no conversion rate defined for the corresponding currency to, transaction date, transaction client and transaction organization. If this happens, display an error message. Otherwise, launch the report as usual.

//Checks if there is a conversion rate for each of the transactions of the report
String strConvRateErrorMsg = "";
OBError myMessage = null;
myMessage = new OBError();
try {
  data = ReportSalesDimensionalAnalyzeJRData.select(this, '''strCurrencyId''', ...);
} catch(ServletException ex) {
    myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage());
}
 
...
 
//If a conversion rate is missing for a certain transaction, an error message window pops-up.
if(!strConvRateErrorMsg.equals("") && strConvRateErrorMsg != null) {
  advisePopUp(response, "ERROR", Utility.messageBD(this, "NoConversionRateHeader", vars.getLanguage()), strConvRateErrorMsg);      
} else {
  //Launch the report as usual, calling the JRXML file
}

XSQL

Modify SQL queries in order to:

• Make the corresponding conversions.
• Append the corresponding currency symbol to the amount, price, price/m2, ...
• Get fields needed to construct the No conversion rate message, if necessary.

The main fields to be extracted are the following:

• AMOUNT (does not need to be added): the amount to be displayed in the Amount column of the report.
• AMOUNTSYM: the amount with its currency symbol placed in the correct side. If one only wants to get the currency symbol (because Amount) can not be a string, append Y to C_CURRENCY_SYMBOL function.
• CONVAMOUNT: the converted amount to be displayed in the Amount (ISO-Symbol) column of the report.
• CONVAMOUNTSYM: : the converted amount with its currency symbol placed in the correct side. If one only wants to get the currency symbol (because Amount (ISO-Symbol)) can not be a string, append Y to C_CURRENCY_SYMBOL function.
• CONVISOSYM: string formed as (ISO-Symbol).
• TRANSCURRENCYID: currency of the transaction.
• TRANSDATE: date of the transaction.
• TRANSCLIENTID: client of the transaction.
• TRANSORGID: organization of the transaction.

 
SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, 
SUM(AMOUNT) AS AMOUNT, SUM(QTY) AS QTY, SUM(WEIGHT) AS WEIGHT, 
SUM(AMOUNTREF) AS AMOUNTREF, SUM(QTYREF) AS QTYREF, SUM(WEIGHTREF) AS WEIGHTREF, UOMSYMBOL,
SUM(CONVAMOUNT) AS CONVAMOUNT, /* IMPORTANT */
SUM(CONVAMOUNTREF) AS CONVAMOUNTREF, /* IMPORTANT */
C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, /* IMPORTANT */
C_CURRENCY_ISOSYM(?) AS CONVISOSYM /* IMPORTANT */
FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, 
SUM(LINENETAMT) AS AMOUNT, SUM(QTYORDERED) AS QTY, SUM(WEIGHT) AS WEIGHT, 
SUM(LINENETREF) AS AMOUNTREF, SUM(QTYORDEREDREF) AS QTYREF, SUM(WEIGHTREF) AS WEIGHTREF, UOMSYMBOL,
C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS CONVAMOUNT, /* IMPORTANT */
0 AS CONVAMOUNTREF, /* IMPORTANT */
TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID /* IMPORTANT */
...
FROM
...
C_ORDERLINE.LINENETAMT, C_ORDERLINE.QTYORDERED, C_ORDERLINE.QTYORDERED*M_PRODUCT.WEIGHT AS WEIGHT,
0 AS LINENETREF, 0 AS QTYORDEREDREF, 0 AS WEIGHTREF, C_UOM.UOMSYMBOL,
COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID, /* IMPORTANT */
TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRDATE, /* IMPORTANT */
C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID, /* IMPORTANT */
C_ORDERLINE.AD_ORG_ID AS TRORGID /* IMPORTANT */
FROM
...
GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, '''TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID''', UOMSYMBOL) ZZ
GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, UOMSYMBOL, 1 
...
 
<Parameter name="cCurrencyConv"/> /* IMPORTANT */
<Parameter name="cCurrencyConv"/> /* IMPORTANT */
<Parameter name="cCurrencyConv"/> /* IMPORTANT */

JRXML

Using iReport, modify the look and feel of the Jasper Report in order to add:

• Amount (ISO-Symbol) column with its corresponding field where it is necessary.
• The fields for the converted amount.
• The currency of the amoutna and converted amount fields.

The following fields need to be added:

<field name="CONVAMOUNT" class="java.math.BigDecimal"/>
<field name="AMOUNTSYM" class="java.lang.String"/>
<field name="CONVAMOUNTSYM" class="java.lang.String"/>
<field name="CONVISOSYM" class="java.lang.String"/>

Some variables may also to be added or modified.

HTML report

XSQL

Modify SQL queries in order to append the corresponding currency symbol to the amount, price, price/m2, ...

HTML

Add Converted Amount column where it is necessary.

FO report

XSQL

Modify SQL queries in order to append the corresponding currency symbol to the amount, price, price/m2, ...

FO

Add Converted Amount column with its corresponding field where it is necessary.

Examples

Sales Dimensional Report

HTML filter window

Add currency mandatory drop-down list to the HTML filter window.

Inside ReportSalesDimensionalAnalyzeJRFilter.html file:

• Replaced lines 355 and 356 by HTML code:

<TD class="TitleCell"><SPAN class="LabelText">Currency</SPAN></TD>
<TD class="Combo_ContentCell">
  <SELECT name="inpCurrencyId" id="inpCurrencyId" class="ComboKey Combo_OneCell_width" required="true">
    <OPTION value=""><DIV id="reportC_Currency_ID"></DIV></OPTION>
  </SELECT>
</TD>

• Added in line 55 Javascript code (inside validate() function):

//Starting of Currency check
 if (frm.inpCurrencyId.value == null || frm.inpCurrencyId.value == "") {
     setWindowElementFocus(frm.inpCurrencyId);
     showJSMessage(7);
     return false;
 }
 //Ending of Currency check

Inside ReportSalesDimensionalAnalyzeJRFilter.xml file:

• Added after line 97 XML code:

<PARAMETER id="paramC_Currency_ID" name="ccurrencyid" attribute="value"/>
<SUBREPORT id="reportC_Currency_ID" name="reportC_Currency_ID" report="org/openbravo/erpCommon/reference/List">
  <ARGUMENT name="parameterListSelected" withId="paramC_Currency_ID"/>
</SUBREPORT>

Inside ReportSalesDimensionalAnalyzeJR.java file:

• Added in line 47 (inside void doPost):

//Get user Client's base currency
String strUserCurrencyId = Utility.stringBaseCurrencyId(this, vars.getClient());

• Added after line 66 (inside void doPost):

String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", "ReportSalesDimensionalAnalyzeJR|currency", strUserCurrencyId);

• Added in line 71 (printPageDataSheet argument) variable String strCurrencyId:

printPageDataSheet(..., strPartnerSalesRepId, '''strCurrencyId''');

• Added in line 120 (void printPageDataSheet argument) variable String strCurrencyId:

void printPageDataSheet(..., String strPartnerSalesrepId, '''String strCurrencyId'''){

• Added after line 232 (inside void printPageDataSheet):

xmlDocument.setParameter("ccurrencyid", strCurrencyId);
try {
   ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_Currency_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportSalesDimensionalAnalyzeJR"), Utility.getContext(this, vars, "#User_Client", "ReportSalesDimensionalAnalyzeJR"), 0);
Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportSalesDimensionalAnalyzeJR", strCurrencyId);
   xmlDocument.setData("reportC_Currency_ID","liststructure", comboTableData.select(false));
   comboTableData = null;
} catch (Exception ex) {
   throw new ServletException(ex);
}

JRXML report

Add multi-currency capability to the JRXML report.

Inside ReportSalesDimensionalAnalyzeJR.java file:

• Added after lines 90 and 112 (inside void doPost):

String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", "ReportSalesDimensionalAnalyzeJR|currency", strUserCurrencyId);

• Added in line 92 (printPageHtml argument) variable String strCurrencyId:

printPageHtml(..., strPartnerSalesRepId, '''strCurrencyId''', "html");

• Added in line 114 (printPageHtml argument) variable String strCurrencyId:

printPageHtml(..., strPartnerSalesRepId, '''strCurrencyId''', "pdf");

• Added in line 259 (void printPageHtml argument) variable String strCurrencyId:

void printPageHtml(..., String strPartnerSalesrepId, '''String strCurrencyId''', String strOutput)

• Replaced after line 394 (inside void printPageHtml):

//Checks if there is a conversion rate for each of the transactions of the report
String strConvRateErrorMsg = "";
OBError myMessage = null;
myMessage = new OBError();
if (strComparative.equals("Y")){
  try {
    data = ReportSalesDimensionalAnalyzeJRData.select(this, '''strCurrencyId''', ...);
} catch(ServletException ex) {
    myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage());
  }
} else {
  try {
    data = ReportSalesDimensionalAnalyzeJRData.selectNoComparative(this, '''strCurrencyId''', ...);
  } catch(ServletException ex) {
    myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage());
  }
}
 
//If a conversion rate is missing for a certain transaction, an error message window pops-up.
if(!strConvRateErrorMsg.equals("") && strConvRateErrorMsg != null) {
  advisePopUp(response, "ERROR", Utility.messageBD(this, "NoConversionRateHeader", vars.getLanguage()), strConvRateErrorMsg);      
} else { //Otherwise, the report is launched
  if (strComparative.equals("Y")){
    strReportPath = "@basedesign@/org/openbravo/erpCommon/ad_reports/WeightDimensionalComparative.jrxml";
  } else {
    strReportPath = "@basedesign@/org/openbravo/erpCommon/ad_reports/WeightDimensionalNoComparative.jrxml";
  }
...
}

Inside ReportSalesDimensionalAnalyzeJR_data.xsql file:

• Modified select method (used for the comparative mode):

SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, 	  
SUM(AMOUNT) AS AMOUNT, SUM(QTY) AS QTY, SUM(WEIGHT) AS WEIGHT, 	  
SUM(AMOUNTREF) AS AMOUNTREF, SUM(QTYREF) AS QTYREF, SUM(WEIGHTREF) AS WEIGHTREF, UOMSYMBOL,	  
SUM(CONVAMOUNT) AS CONVAMOUNT,           	        	  
SUM(CONVAMOUNTREF) AS CONVAMOUNTREF,
C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM,	  	  
C_CURRENCY_ISOSYM(?) AS CONVISOSYM,
'' AS ID, '' AS NAME, '' AS TRANSCURRENCYID, '' AS TRANSDATE, '' AS TRANSCLIENTID, '' AS TRANSORGID    
FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, 
SUM(LINENETAMT) AS AMOUNT, SUM(QTYORDERED) AS QTY, SUM(WEIGHT) AS WEIGHT, 
SUM(LINENETREF) AS AMOUNTREF, SUM(QTYORDEREDREF) AS QTYREF, SUM(WEIGHTREF) AS WEIGHTREF, UOMSYMBOL,
C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS CONVAMOUNT,
C_CURRENCY_CONVERT(SUM(LINENETREF), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS CONVAMOUNTREF,
TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID
FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, to_char('6') AS NIVEL6, to_char('7') AS NIVEL7, to_char('18') AS NIVEL8, to_char('19') AS NIVEL9, 
C_ORDERLINE.LINENETAMT, C_ORDERLINE.QTYORDERED, C_ORDERLINE.QTYORDERED*M_PRODUCT.WEIGHT AS WEIGHT, 
0 AS LINENETREF, 0 AS QTYORDEREDREF, 0 AS WEIGHTREF, C_UOM.UOMSYMBOL,
COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,      
TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRDATE,      
C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,      
C_ORDERLINE.AD_ORG_ID AS TRORGID
FROM C_ORDER LEFT JOIN AD_USER ON C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID,
C_BPARTNER LEFT JOIN C_BPARTNER CB ON C_BPARTNER.SALESREP_ID = CB.C_BPARTNER_ID,
C_BP_GROUP, C_ORDERLINE, M_PRODUCT, M_PRODUCT_CATEGORY, M_WAREHOUSE, C_UOM,  AD_ORG
WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
AND C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
AND C_ORDER.AD_ORG_ID = AD_ORG.AD_ORG_ID
AND C_ORDER.ISSOTRX = 'Y'
AND C_ORDER.PROCESSED = 'Y'
AND 0=0 AND C_ORDER.AD_ORG_ID IN ('1')
AND C_ORDER.AD_CLIENT_ID IN ('8')
AND 1=1
UNION ALL SELECT to_char('9') AS NIVEL1 , to_char('10') AS NIVEL2 , to_char('11') AS NIVEL3 , to_char('12') AS NIVEL4 , to_char('13') AS NIVEL5 , to_char('14') AS NIVEL6 , to_char('15') AS NIVEL7 , to_char('20') AS NIVEL8 , to_char('21') AS NIVEL9, 
0 AS LINENETAMT, 0 AS QTYORDERED, 0 AS WEIGHT, 
C_ORDERLINE.LINENETAMT AS LINENETREF, C_ORDERLINE.QTYORDERED AS QTYORDEREDREF, C_ORDERLINE.QTYORDERED*M_PRODUCT.WEIGHT AS WEIGHTREF, C_UOM.UOMSYMBOL,
COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,      
TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRDATE,      
C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,      
C_ORDERLINE.AD_ORG_ID AS TRORGID
FROM C_ORDER LEFT JOIN AD_USER ON C_ORDER.SALESREP_ID = AD_USER.AD_USER_Id, 
          C_BPARTNER LEFT JOIN C_BPARTNER CB ON C_BPARTNER.SALESREP_ID = CB.C_BPARTNER_ID,
C_BP_GROUP, C_ORDERLINE, M_PRODUCT, M_PRODUCT_CATEGORY, M_WAREHOUSE, C_UOM,  AD_ORG 
WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
AND C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
AND C_ORDER.AD_ORG_ID = AD_ORG.AD_ORG_ID
AND C_ORDER.ISSOTRX = 'Y'
AND C_ORDER.PROCESSED = 'Y'
AND 3=3 AND C_ORDER.AD_ORG_ID IN ('1')
AND C_ORDER.AD_CLIENT_ID IN('16')
AND 2=2) AA
GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID, UOMSYMBOL) ZZ      
GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, UOMSYMBOL, 1
 
...
 
<Parameter name="cCurrencyConv"/> /* IMPORTANT */
<Parameter name="cCurrencyConv"/> /* IMPORTANT */
<Parameter name="cCurrencyConv"/> /* IMPORTANT */
<Parameter name="cCurrencyConv"/> /* IMPORTANT */

• Modified selectNoComparative method (used for the non comparative mode):

SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9,
SUM(AMOUNT) AS AMOUNT, SUM(QTY) AS QTY, SUM(WEIGHT) AS WEIGHT, 
SUM(AMOUNTREF) AS AMOUNTREF, SUM(QTYREF) AS QTYREF, SUM(WEIGHTREF) AS WEIGHTREF, UOMSYMBOL,
SUM(CONVAMOUNT) AS CONVAMOUNT,
SUM(CONVAMOUNTREF) AS CONVAMOUNTREF,           
C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM,        
C_CURRENCY_ISOSYM(?) AS CONVISOSYM
FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, 
SUM(LINENETAMT) AS AMOUNT, SUM(QTYORDERED) AS QTY, SUM(WEIGHT) AS WEIGHT, 
SUM(LINENETREF) AS AMOUNTREF, SUM(QTYORDEREDREF) AS QTYREF, SUM(WEIGHTREF) AS WEIGHTREF, UOMSYMBOL,
C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS CONVAMOUNT,
0 AS CONVAMOUNTREF,
TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID
FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, to_char('6') AS NIVEL6, to_char('7') AS NIVEL7, to_char('10') AS NIVEL8, to_char('11') AS NIVEL9, 
C_ORDERLINE.LINENETAMT, C_ORDERLINE.QTYORDERED, C_ORDERLINE.QTYORDERED*M_PRODUCT.WEIGHT AS WEIGHT, 
0 AS LINENETREF, 0 AS QTYORDEREDREF, 0 AS WEIGHTREF, C_UOM.UOMSYMBOL,
COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,      
TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRDATE,      
C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,      
C_ORDERLINE.AD_ORG_ID AS TRORGID 
FROM C_ORDER LEFT JOIN AD_USER ON C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID, 
          C_BPARTNER LEFT JOIN C_BPARTNER CB ON C_BPARTNER.SALESREP_ID = CB.C_BPARTNER_ID,
      C_BP_GROUP, C_ORDERLINE, M_PRODUCT, M_PRODUCT_CATEGORY, M_WAREHOUSE, C_UOM,  AD_ORG
WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
AND C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
AND C_ORDER.AD_ORG_ID = AD_ORG.AD_ORG_ID
AND C_ORDER.ISSOTRX = 'Y'
AND C_ORDER.PROCESSED = 'Y'
AND 0=0 AND C_ORDER.AD_ORG_ID IN ('1')
AND C_ORDER.AD_CLIENT_ID IN ('8')
AND 1=1) AA
GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID, UOMSYMBOL) ZZ
GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, UOMSYMBOL, 1
 
...
 
<Parameter name="cCurrencyConv"/> /* IMPORTANT */
<Parameter name="cCurrencyConv"/> /* IMPORTANT */
<Parameter name="cCurrencyConv"/> /* IMPORTANT */

Modified src/org/openbravo/erpCommon/ad_reports/WeightDimensionalComparative.jrxml (comparative mode) and src/org/openbravo/erpCommon/ad_reports/WeightDimensionalNoComparative.jrxml (non comparative mode) Jasper reports in order to add the multi-currency capability.

Purchase Order Report

Application Dictionary filter window

Logged as System Administrator went to Application Dictionary || Report and Process, selected Purchase Order Report. In Parameter tab, created a new record with the following values:

• Name: Currency
• Active: Yes (checked)
• Central Maintenance: Yes (checked)
• Sequence: In each case, select the number to place Currency drop-down list in the right order.
• DB Column Name: C_Currency_ID
• Application Element : C_Currency_ID - Currency
• Reference: TableDir
• Length: 22
• Mandatory: Yes (checked)
• Default Value: @C_Currency_ID@

Saved the record and Synchronize Terminology (Application Dictionary || Synchronize Terminology) in order to fill Description and Help/Comment fields.

Finally compiled and exported database sourcedata to get AD_PROCESS_PARA.xml file:

• Added a new record Currency to it.