Remember that Qlikview is also using these rates by using a query based on the DSO DAFICE01. (BW_QRY_DAFICE01_0001)
Exchange Rates In WBP
| Type Code | BW description | Currencies | Frequency | Method | Periods |
|---|---|---|---|---|---|
| CAR1 | Magnitude Exchange Rate from from BFC file. | To EUR only | Monthly (BW load daily but BFC file update monthly on day 1 - 10) | ABAP | Up to Year + 1 |
| CAR3 | Copy of CAR1 + euro is reference currency to allow all conversion from BFC file. | All | Monthly (BW load daily but BFC file update monthly on day 1 - 10) | ABAP | Up to Year + 1 |
| CAR2 | Monthly Average Rate + SIMU Rate (All) from BFC file. | To EUR only | Monthly (BW load daily but BFC file update monthly on day 1 - 10) | ABAP | Up to Year + 1 |
| CAR4 | Copy of CAR2 + euro is reference currency to allow all conversion from BFC file. | All | Monthly (BW load daily but BFC file update monthly on day 1 - 10) | ABAP | Up to Year + 1 |
| CAR5 | Copy of CAR4 + BFR+ BUDGET data from BFC file. | All | Monthly (BW load daily but BFC file update monthly on day 1 - 10) | ABAP | Up to Year + 1 |
| CAR6 | Only Budget rates from BFC file. | All | Monthly (BW load daily but BFC file update monthly on day 1 - 10) | ABAP | Up to Year + 1 |
| ZBUD | ZBUD Monthly reval. for budget (spring) | To EUR only | Monthly | PC_FC_COMPLETE | Up to current month |
| ZRH2 | Exchange Rate base on ZHRO ref. crcy EUR | All | Monthly | ABAP | Up to last month |
| ZRH3 | Exchange Rate base on ZHRO ZBUD ref. EUR | All | Monthly | PC_FC_COMPLETE | Up to last month |
| ZRHO | Monthly valuation consolidation rate | To EUR only | Daily | PC_TECH_01 Variant WP1 | Up to last month |
| M | Standard translation at average rate (European Central Bank) | All | Daily | PC_TECH_01 Variant PRS | Up to last day |
| C | Y to date cumulated average monthly (reporting) | To EUR only | Daily | PC_TECH_01 Variant PI1 | Up to last month |
| A | Monthly average of 'M' rates since the beginning of the month | To EUR only | Monthly | PC_TECH_01 Variant PI1 | Up to last month |
| EURX | Derived currency rate used by any other currency rate | Daily | PC_TECH_01 Variant WP1 | ||
LBRD | Brazilian Central Bank | All | Daily | PC_TECH_01 Variant PF1 | |
| ZDAY | Custom Daily Rate (Stopped in 2017) | All | Daily | PC_TECH_01 Variant WP1 | Up to last day |
Process Chain - PC_TECH_01
Steps :
- Starter : Daily run at 1am
- Load Global Settings WP1 : Transfer Global Settings from WP1
- Contents: Currencies - Units of Measurement - Fiscal Year Variants
- Mode: Update tables
- Delete M factors from TCURF (Clean before loading from PRS)
- Load Global Settings PRS : Transfer Global Settings from PRS
- Contents: Currencies
- Mode: Update tables
- Load Exchange rates WP1
- Types: ZRHO - ZDAY - EURX
- Load Exchange rates PI1
- Types: C - A
- Load Exchange rates PRS
- Types: M
- Load Exchange rates PF1
- Types: LBRD
- Rebuild Factory Calendar WP1 (program RSIMPCUST, variant Z_FACT_CAL_WP1).
- Execute program ZBW_RSSM_EVENT_RAISE with variant Z_CPX_TRSV_01 → this program will use FM RSSM_EVENT_RAISE with system destination CPX to raise the event Z_EVT_PC_TRANSVERSE_01 and so execute chain PC_TRANSVERSE_01 in CPX system.
Variant for program RSIMPCURR and RSIMPCUST must be maintained on each environment. The Source System is not translated/converted during the releasing of the transport request from one BW system to another BW system.
CAR Currencies
The main dataflow is : Flat file uploaded on BW folder and dedicated process chain is used to load the data for CAR1, CAR2, CAR3 and CAR4 currency rate.
What is the Team in charge of the management of source of the data for CAR1 and CAR2?
Answer : BFC admin team.
$BFC-Admin : BFC-Admin@solvay.com
$BOIC-Admin : boic-admin@solvay.com
+ Key contacts :
Nicolas Donck : nicolas.donck@solvay.com
Tiago Rodrigues : tiago.rodrigues@solvay.com
Nicolas Bourgois : nicolas.bourgois@solvay.com (who replace Lionel DROUOT)
Main process chain used :
Z_TAUX_CARAT Magnitude Exchange Rates scheduled every BE working day 11:00pm CET Time
Main program used :
ZBW_LOAD_EXCHANGE_RATES_CAR
Variant :
Z_MONTHLY_FILE
Values used :
1000 Choose the type fo file P
1000 Exchange rate local file P
1000 Exchange rate server File P X
1000 Hist exchange rate server File P
1000 BFC Exhange Rate File name P Select your PREV_SIMUL_REEL Recurring File
1000 BFC Exhange Rate File name P //exploit/BW/Credit_Supplier_Automation/PREV_SIMUL_REEL_ExchangeRates_EurUsd.txt
1000 Histo Exhange Rate File name P //exploit/BW/Credit_Supplier_Automation/PREV_SIMUL_REEL_histo.txt
1000 TCURR Delete (X = Yes) P
1000 FIrst Recurring Period P 01.2020
Explanation how the program update CARx currencies rates :
Example of the content of the file : PREV_SIMUL_REEL_ExchangeRates_EurUsd.txt
Rules applied to extract data from the file :
Periods (Months) | CAR 1/3 | CAR2/4 | CAR5 (Real + Simulation (BFR) + PREV (Budget) exchange rates data) | CAR6 Only PREV (Budget) exchange rates data |
Past : M-X | column used =EUR_TMN type rate = REEL Period = M-X | EUR_TMN for REEL and M-X | EUR_TMN for REEL and M-X (first year added for CAR5 : 2019) | EUR_TC for PREV (replicate values of the quarter to previous month for example quarter 03.2021, the values for 01.2021 and 02.2021 will have the same values) |
Current: M | column used =EUR_TMN type rate = REEL Period = M-1 | EUR_TC for SIMULATION and M For QV extraction => exception EUR_TC For PREV if simulation empty | EUR_TC for SIMULATION and M For QV extraction => exception EUR_TC For PREV if simulation empty | EUR_TC for PREV (no special rules = value of the quarter linked to the current month) |
Futur: M+X Until end of current year | column used =EUR_TMN type rate = PREV Period = M+X | EUR_TC for SIMULATION and M+X Correction 01.2021: For QV extraction => exception EUR_TC For PREV if simulation empty | EUR_TC for SIMULATION and M+X Correction 01.2021: For QV extraction => exception EUR_TC For PREV if simulation empty | EUR_TC for PREV (replicate values of the quarter to previous month for example quarter 03.2022, the values for 01.2022 and 02.2022 will have the same values) |
| Next year | column used =EUR_TMN type rate = PREV Period = Next Year | Not generated due to some impact with Dynasys development | EUR_TC For PREV of last quarter of the current year for all months of next year | EUR_TC for PREV (if data not entered by BFC team, the rule is to take into account the last quarter known so could be 12.2022 for all months of 2023 the value will be the same) |
Remark:
REEL = real BFC cumulated rate (month by month for historical months) +
PREV = budget (quarter by quarter)
SIMU = BFR rate quarter by quarter for the mains currencies managed by BFC team.
eur_tc(10) TYPE c, "Closing=End-of Month in EUR |
usd_tc(10) TYPE c, "Closing=End-of Month in USD |
eur_tmn(10) TYPE c, "Year-To-Date in EUR |
usd_tmn(10) TYPE c, "Year-To-Date in USD |
eur_tmn_1(10) TYPE c, "Year-To-Date Dec. N-1 in EUR |
usd_tmn_1(10) TYPE c, "Year-To-Date Dec. N-1 in USD |
eur_to(10) TYPE c, "Opening rate in EUR |
usd_to(10) TYPE c, "Opening rate in USD |
eur_tcn_1(10) TYPE c, "Closing=End-of Month N-1 in EUR (same month previous year) |
usd_tcn_1(10) TYPE c, "Closing=End-of Month N-1 in USD (same month previous year) |
eur_tmyn_1(10) TYPE c, "Year-To-Date N-1 in EUR (same month previous year) |
usd_tmyn_1(10) TYPE c, "Year-To-Date N-1 in USD (same month previous year) |
+ Rules : Compute rates using following rules
*&- for REEL : compute the monthly rate TM with formula :
*& TM-reel(n) = (n x TMN(n)) - [(n-1) x TMN(n-1)]
*& TMN : year to date rate received n the file
*& n : number of the concerned month
*& computed for each month before current month
<FS_DATA> - RATEC =
( <FS_DATA> - EUR_TMN * <FS_DATA> - CALMONTH+4 ( 2 ) )
- ( L_DATA - EUR_TMN * L_DATA - CALMONTH+4 ( 2 ) ).
*&- for PREV (=BUDGET rate) --> computed by year
*& EUR_TC-budget(12) = (12 x EUR_TC(12)) - [9 x EUR_TC(9)]
*& the computed rate should be placed for month of quarters Q2, Q3, Q4
*& the received EUR_TC-prev rate should be placed for Q1
*&
*&- for SIMU (=BFR) --> computed by quater
*& EUR_TC-bfr(3*i) = (3*i x EUR_TC(3*i)) - [3*(i-1) x EUR_TC(3*(i-1))]
*& the 3 computed rate should be placed for corresponding mont of quarters Q2, Q3, Q4
*& the received TMN-simu rate should be placed for Q1
al11 folder used :
/exploit/BW/Credit_Supplier_Automation
Files :
PREV_SIMUL_REEL_histo.txt
PREV_SIMUL_REEL_ExchangeRates_EurUsd.txt
Schedules of the update on BW folder :
CAR1 - Magnitude Exchange Rate
Description
CAR1 is the joining of the "un-cumulated" version of the Cumulated Monthly Mean (CMM - Also refered as "YTD Monthly Avg Rate") AND the PREV (Budget) rates from BFC.
For past months, CAR1 is the un-cumulated CMM and for the future months, it is the PREV rate.
When to use it
Must be used for Profit & Loss accounts (cumulated) and most of balance sheet movement flows.
CAR3 - Exchange Rate base on CAR1 ref. crcy EUR
DescriptionCAR3 is the same rate as CAR1 but can be used to convert from any currency to any currency.As CAR1 can only be used to convert to EUR, CAR3 is set so that EUR is used as a "Pivot" currency.
When to use it CAR3 is used for SPRINT. P&L, Working Capital
CAR2 - COLMAR Exchange Rate
CAR2 used for Colmar or CAR4 ?
CAR4 - Exchange Rate base on CAR2 ref. crcy EUR
CAR4 is the same rate as CAR2 but can be used to convert from any currency to any currency.
As CAR2 can only be used to convert to EUR, CAR4 is set so that EUR is used as a "Pivot" currency.
Used by Colmar and Finance application
Tcode ZRATE → to be documented.
CAR5 - Exchange Rate base on CAR4 ref. crcy EUR + BFR + Budget for next year
Mainly used for Anaplan ZBB-F2G project and in the QlikSense Dashboard Cos$ta
CAR6 - Exchange Rate base only on PREV data = Budget rates
Mainly used for the QlikSense Dashboard Cos$ta
M - Standard translation at average rate
Source
The currency rate M is loaded from PF1_050 (see the detail in the google presentation at the end of this page)
Technical Details
Loaded with Process Chain : PC_TECH_01
ZRHO - Monthly valuation consolidation rate
Description
It's the End of month rate (FDM) from BFC. Rate is posted by the European Central Bank (ECB - TARGET system) or related National Central Bank, on last quotation day.
Values can be checked In the finance team site. Under Exchange Rate. "Fin mois / End of Month".
Or in the Solia CICC Online, Financial And Credit Tools :
https://solia.solvay.com/sites/eco-cicc#Home-show
http://solia.solvay.com/irj/portal/CICCOnline_FinancialAndCreditTools
Source
SAP WP1 - Rate ZRHO -
Remark : ZRHO must be manually created when a new currency is needed. Then a job will run and update it automaticaly using "M" of the last working day.
Main contact = Tiago.rodrigues and marie-yolande.kuczynski. Freshdesk group = IS-CGI-L2-RTR
Job in WP1 = FI_EXCHANGE_RATE_DAILY__UPDATE_2
Program = ZZRDICUR with parameter (DAILY_M, DAILY_M_X, DAILY_M_Z, DAILY_LPLN_ZDA)
When to use it
Must be used for Balance Sheet accounts, end of period flow.
Technical Details
Loaded with Process Chain : PC_TECH_01
ZDAY - Custom Exchange Rate
Source
SAP WP1
Technical Details
Loaded with Process Chain : PC_TECH_01
ZRH2 - Exchange Rate base on ZHRO ref. crcy EUR
Description
ZRH2 is the same rate as ZRHO but can be used to convert from any currency to any currency.
As ZRHO can only be used to convert to EUR, ZRH2 is set so that EUR is used as a "Pivot" currency.
Technical Details
Program used : Z_CREATE_EXCHANGE_RATES_ZRH2 Pgr: Exchange Rate ZRH2 base on ZRHO
Process chain : Z_TAUX_ZRH2 / Exchanges Rate ZRH2 from ZRHO => runs daily except weekends (sub chain of RSP_DAILY)
The ZRH2 is not as exact as the ZRHO rate because the "pivoting" calculation may loose some decimals information. One example well known is about KRW.
ZBUD
Program : Z_CREATE_EXCHANGE_RATES_ZBUD
Process chain : PC_FC_COMPLETE
Source : '//exploit/BW/Credit_Supplier_Automation/PREVExchangeRatesForBW-SCA-EurUsd.txt' .
ZRH3 - Exchange Rate base on ZHRO ZBUD ref. EUR
Description
ZRH2 is the concatenation of the ZRHO and ZBUD exchange rates. It takes the ZRHO rate for past periods and the ZBUD for the future periods.
EUR is set as a pivot currency so that the rate can be used from any currency to any currency.
Technical Details
Z_CREATE_EXCHANGE_RATES_ZRH3 Pgr: Exchange Rate ZRH3 base on ZRHO and ZBUD
Process chain : PC_FC_COMPLETE
The ZRH3 is not as exact as the ZRHO rate because the "pivoting" calculation may loose some decimals information.
C - Cumulated average monthly (reporting)
Description
It's the Cumulated Monthly Mean (CMM - Also refered as "YTD Monthly Avg Rate") from BFC.
Values can be checked In the finance team site. Under Exchange Rate. "Moyen / Average".
Or in the Solia CICC Online, Financial And Credit Tools :
http://solia.solvay.com/irj/portal/CICCOnline_FinancialAndCreditTools
When to use it
It is used by Qlikview for the "Fixed Cost Dashboard and in WBP for P&L and GBU fixed costs.
A - Average monthly (reporting)
Description
It is the Average monthly coming from CICC.
It is used by P&L community.
ZBUD - ZBUD Monthly reval. for budget (spring) => Obsolete (used in the past to create ZRH3)
Description
Exchange rates version PREV (the same as used for Budget and RSB Phases)
Source
BFC
Technical Details
File is : //exploit/BW/Credit_Supplier_Automation/PREVExchangeRatesForBW-SCA-EurUsd.txt
File sent via FTP from 6th to 20th day of the month at 0:30 am (Paris Time).
LBRD coming from the Brazilian Central Bank Daily exchange rate
Description
LBRD is coming from the Brazilian Central Bank
Documentation in the link below :
Examples of Rate associated to an Application
Application | Query | Rate | Time Reference |
|---|---|---|---|
GSV | CVA 01_00_00: Invoiced purchasing turnover (FI) GSV_QRY_CUB_FIAP1_CVA_010000 | CAR3 | 0CALMONTH - Invoice Posting Date |
TR | BW - Shipment costs analysis new (Core Query) BW_QRY_MPR_TR002_0001 | CAR1 | 0CALMONTH - Shipment Creation Date |
PS | BW - Projects PEC & Cash (Core Query) BW_QRY_MPR_PS004_0001 | CAR3 | 0CALMONTH :
|
FC2 | FC2-A01 - Cost Analyst - NVC destination (Core) BW_QRY_MPR_FC001_0010C | CAR3 | 0FISCPER - Posting Date |
CBS | CBS - Functions - end of month (Core query) BW_QRY_MPR_FC001_0001 | CAR3 | 0FISCPER - Posting Date |
IM | BW - Stock evolution with conversion (Core Query) BW_QRY_MPR_IC001_0062 | ZRHO | 0CALMONTH - Stock Month |
FIAR | BW - Credit Management Overdue (Core Query) BW_QRY_MVFIAR01_0002 | ZRH2 | Selected Date (Variable) |
FIWC | BW - Working Capital for GBU (Core Query) BW_QRY_MPR_WC02_0002 | ZRH2 | Selected Date (Variable) |
Principle of currency conversion in BW
Principle
Currency translation | The currency translation happens at different steps : As defined here above, the currency of the transactions are either kept as is or converted into another currency (controlling area currency, ...) when they are uploaded in the different infocubes. If you want to select another currency than that one stored in the infocube, you may translate it on spot when using a query or by creating a key figure with the requested currency and the appropriated available rate. |
Illustration
Example on invoices | Table 1: Solvay chemicals international (0005) has EUR as local currency but issued following invoices :
Table2: T-code: OB08 to see exchange rate (in case we have only following exchange rate in the system)
| ||||||||||||||||||||||||||||
Data base currency | The result of the query will be (with database currency - the amount without conversion by exchange rate of OB08. It is calculated during loading data) : It calculate by using table 1 with 3 decimals
The invoiced gross amount is always in local currency and is the result of the conversion of the gross amount in transaction currency as been converted at the rate M (with 3 decimals) of the posting date. Remark : the NETV is a calculation based on the invoiced gross amount. | ||||||||||||||||||||||||||||
Rate M | For example, we are querying on 17/02/2006. You want to convert the CHF to EUR, in the result of the query if you use rate : ZZTODAY (rate M) : It calculate by using table 2 with Rate M on 17/02/2006 (date of execution the report) for all key figures, which is 1,5588
| ||||||||||||||||||||||||||||
Rate C | On the 17/02 you want to convert the CHF to EUR, in the result of the query, if you use the rate : ZZVAVMONTC (rate C) It calculate by using table 2 with Rate C on 17/02/2006 (date of execution the report) for all key figures, which is 1,54942
| ||||||||||||||||||||||||||||
Enf of month rate | If your the definition of your key figure in your query specially refers to the rate :ZZVAVMONTH (Monthly end of month rate) :
It calculate by using table 2 with Rate M base on Cal. year / month 12.2005 use 1,5551 01.2006 use 1,5547 02.2006 use 1,5588 It is used exchange rate of last date on each month that available on table 2 (OB08) |
Currency Conversion How To
How to reload historical TCURR:
To reload the historical value in TCURR table, it's necessary to use program ZBW_LOAD_EXCHANGE_RATES_CAR with option "Hist exchange rate server File". This option will get REEL rate from PREV_SIMUL_REEL_histo.txt to replace table TCURR on CAR1 - CAR6 basing on the period selection.
Be careful, the date in "first recurring period" in case of historical reload, it's the "until" date, not the "from" date.
Here, the exchange rate before 01.2020 will be reloaded, not after.
Flag TCURR Delete, if mark it = X, it will deleted all the TCURR of CAR1 - CAR6 that time < "First Recurring Period". We use this option only when we are sure that the history file from BFC can replace all CARs type.
The impact will be on the currency that is not in the file will be deleted in TCURR table.
How to create a backup (TCURF and CAR* currencies only)
Go to transaction SE38
Execute program ZBW_CURRENCY_BACKUP
Select the requested option
Execute and check in AL11 in corresponding folder
TCURF directory /exploit/BW/TCURF. File name template TCURF_BACKUP_[SYSTEM]_[USER]_[DATE]_[TIME].csv
CAR directory /exploit/BW/TAUX_DEV. File name template TCURR_[CAR1/2/3/4]_BACKUP_[SYSTEM]_[USER]_[DATE]_[TIME].csv
How to check the rates
Go to transaction SE16
Select table TCURR
Enter a Exchange rate type
Enter a from currency / to currency
Note If there is no rate for a period BW will use the previous one! Using the example above: if I need to convert from USD to EUR with date = 10.02.2009 since there is no rate it will use the January rate
How to check the conversion types
Go to transaction RSCUR
Select a conversion type
You can then check :
The exchange rate used
The time characteristic used to apply the rate
The target currency (fixed, fixed variable, open)
How to create and transport currency factor in BW System - TCURF Table
How to create a new rate type in BW
Mains issues&Troubleshooting about currency exchange rate
1) NO_FACTORS_FOUND
=> Solution : Add new factors on the tcurf table. See : How to create and transport currency factor in BW System - TCURF Table (document in the drive + see below the link)
How to create and transport currency factor in BW system - TCURF - TCURR
Knowlegde Database
Specific currencies with no decimals (ie : KRW / JPY)
Currencies KRW and JPY don't have decimals. But you can see in in the Infoprovider that SAP stores in internal format with 2 decimals.
For example if you see the amount 12.34 in the infoprovider for these currencies, in the reporting it will display 1234.
This information is stored in TCURX table. CURRDEC column correponding to the number of decimals (0 in this case)
Exchange rates in CPX
In CPX System, exchanges rates (with factory calendar and global setting) are loading with process Chain PC_TRANSVERSE_01.
In CPX Exchange rates are updated from WPX system and also WBP system, it's why at the end of chain PC_TECH_01 there is a program ZBW_RSSM_EVENT_RAISE with variant Z_CPX_TRSV_01 to use FM RSSM_EVENT_RAISE with system destination CPX to raise the event Z_EVT_PC_TRANSVERSE_01 and so execute chain PC_TRANSVERSE_01 in CPX system.