Toolbox

Logged as System Administrator go to Application Dictionary || Report and Process, select your 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.

SQL query example

Below you'll find the complete Purchase Order Report query

 
SELECT DOCUMENTNO, DATEORDERED, CLIENT_NAME, SUM(QUANTITYORDER) AS QUANTITYORDER, PRICEACTUAL, CONVPRICEACTUAL,
    SUM(PRICELIST) AS PRICELIST, SUM(CONVPRICELIST) AS CONVPRICELIST, PRODUCT_NAME, UOMNAME,
    C_CURRENCY_SYMBOL(TRANSCURRENCYID, '0', 'Y') AS TRANSSYM,
    C_CURRENCY_SYMBOL($P{C_Currency_ID}, '0', 'Y') AS CONVSYM,
    C_CURRENCY_ISOSYM($P{C_Currency_ID}) AS CONVISOSYM
    FROM (
    SELECT C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS DATEORDERED, C_BPARTNER.NAME AS CLIENT_NAME, 
    SUM(C_ORDERLINE.QTYORDERED) AS QUANTITYORDER, C_ORDERLINE.PRICEACTUAL AS PRICEACTUAL,
    C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID}, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL, SUM(C_ORDERLINE.LINENETAMT) AS PRICELIST, 
    C_CURRENCY_CONVERT(SUM(C_ORDERLINE.LINENETAMT), COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID}, TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED,  NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICELIST,
    M_PRODUCT.NAME AS PRODUCT_NAME, C_UOM.NAME AS UOMNAME,
    COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID,
    TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRANSDATE,
    C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, 
    C_ORDERLINE.AD_ORG_ID AS TRANSORGID
    FROM C_ORDER, C_ORDERLINE, C_BPARTNER, M_PRODUCT, C_UOM
    WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
    AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
    AND C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
    AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
    AND 1=1  
    $P!{aux_partner}  
    $P!{aux_warehouse}
    $P!{aux_project} 
    $P!{aux_DateFrom} 
    $P!{aux_DateTo}
    AND C_ORDER.ISSOTRX = 'N'
    AND C_ORDER.AD_CLIENT_ID IN ($P!{USER_CLIENT})
    AND C_ORDER.AD_ORG_ID IN ($P!{USER_ORG})
    GROUP BY C_BPARTNER.NAME, C_ORDERLINE.PRICEACTUAL, M_PRODUCT.NAME, C_UOM.NAME, C_ORDER.DOCUMENTNO, 
    C_ORDER.DATEORDERED, C_ORDERLINE.DATEORDERED,
    C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID,
    C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID
    ) ZZ
    GROUP BY CLIENT_NAME, PRICEACTUAL, CONVPRICEACTUAL, PRODUCT_NAME, UOMNAME, DOCUMENTNO, DATEORDERED,
    TRANSCURRENCYID, TRANSDATE
    ORDER BY CLIENT_NAME, DATEORDERED

C_Currency_Convert usage

 
C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{C_Currency_ID},
TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL

C_ORDERLINE.PRICEACTUAL = The amount we want to convert
COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) = Pick the C_Currency_ID from the Order line or the Order header
$P{C_Currency_ID} = C_Currency_ID parameter passed from the UI
TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) = From date (defined in Conversion rates tab)
NULL = Conversion rate type
C_ORDERLINE.AD_CLIENT_ID = Client ID
C_ORDERLINE.AD_ORG_ID = Organization ID

Languages:

ERP 2.50:Developers Guide/Concepts/Multi-Language and Translations | ERP 2.50:Developers Guide/Concepts/Messages

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:Developers_Guide/Concepts/Multi-Currency"

This page has been accessed 7,750 times. This page was last modified on 14 June 2011, at 11:03. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.

Toolbox

PDF Books

Search

Participate

ERP 2.50:Developers Guide/Concepts/Multi-Currency

Contents

Introduction

Reports

Currency parameter definition

SQL query example

C_Currency_Convert usage