ERP 2.50:Developers Guide/Concepts/Multi-Currency
Contents |
Introduction
This document explains multi-currency from a development perspective.
Reports
Currency parameter definition
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