View source | View content page | Page history | Printable version   

Projects:Multi Currency Reports Review/Technical Documentation

Contents

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:


Currency window

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


C_CURRENCY_SYMBOL function

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

C_CURRENCY_ISOSYM function

C_Currency_IsoSym function with one parameter (c_currency_id) will:

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:

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


Currency-parameter.png


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

ant compile.development -Dtab="xx"


Purchase-order-report-filter.png

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:

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:

The main fields to be extracted are the following:

 
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:

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:

<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>
//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:

<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:

//Get user Client's base currency
String strUserCurrencyId = Utility.stringBaseCurrencyId(this, vars.getClient());
String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", "ReportSalesDimensionalAnalyzeJR|currency", strUserCurrencyId);
printPageDataSheet(..., strPartnerSalesRepId, '''strCurrencyId''');
void printPageDataSheet(..., String strPartnerSalesrepId, '''String strCurrencyId'''){
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:

String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", "ReportSalesDimensionalAnalyzeJR|currency", strUserCurrencyId);
printPageHtml(..., strPartnerSalesRepId, '''strCurrencyId''', "html");
printPageHtml(..., strPartnerSalesRepId, '''strCurrencyId''', "pdf");
void printPageHtml(..., String strPartnerSalesrepId, '''String strCurrencyId''', String strOutput)
//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:

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 */
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:

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:

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Multi_Currency_Reports_Review/Technical_Documentation"

This page has been accessed 11,919 times. This page was last modified on 8 June 2012, at 05:28. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.