0/ Process
| Embedded Google Drive File | ||||||
|---|---|---|---|---|---|---|
|
1/ Summary
This objective of this page is to describe step by step the process of update the Salesforce Exchange rate based on the SAP BW data.
This process is trigger by the business to create Service One ticket to request the update
2/ Prerequisite
1/ This process is triggered by a business user that request the update of the Salesforce exchange rate. Click Here to review the process to create that request
2/ User that will perform this process must have the following access :
Access to BW query : BW_QRY_DAFICE01_0001 0001
Access to Salesforce as an Admin Line in order to be able to update select following salesforce object :
CurrencyType
DatedConversionRate
- Access to Salesfroce ConnecteSalesforce Connector
3/ Update Process
Step 1 : Extract data from BW
Access the BW Query : BW_QRY_DAFICE01_0001 (WBP NB-Netback authorization) and extract its content to a GSheet.
- Open "Analysis for Microsoft Excel"
- Open Excel
- Access Excel option and Addin, Manage : "disable items" then Go
- Select Addin "Analysis" then Click on Enable
- Click OK
- Close Excel
- Open Excel
- Access "The Analysis" Tab
- Access "The Analysis" Tab
- Select Data Source :
- Select BW - Product - WBP
- Click on Next
- (single signe on should log in)
- In the Search set the value "BW_QRY_DAFICE01_0001"or if not working "BW_QRY_DAFICE01_0002"
- then click on the Search Icon on the right
- if nothing happen
- Select ROLE then
- Click OK
- Set the first day of the month with the format Day.Month.Year (1.2.2023) + Exchange Rate type (CAR3 = Monthly Exchange rate, CAR4 = Yearly Exchange rate)
- For Monthly
- Calendar day = First of the month
- Exchange Rate type = CAR3
- For Yearly
- Calendar day = First of the year
- Exchange Rate type = CAR4
- Then Click on OK
- RESULT Should look like :
- Right click on Measures, chose"select all memebers"
- Copy Paste the whole table in a new GSheet to get the exchange rate
- final Exchange rate should be F multiply by G
From the Service One request, identify if the Yearly or the Monthly rate has to be updated. (select the correct step 2)
Step 2.1 - Monthly
rateRate Update
Weare going to prepare the file to be loaded in UAT & PROD.
UAT & PROD must be setup in the same time.
2.1 / From that GSheet connect it with Salesforce PROD in order to retreive the following data : /UAT CORE
- Click on Extensions then Salesforce Connector, click to Enable the Add On
- Access again te extension and click on OPEN
- Check what environement you are connected in :
- then Select
- Connection Information
- If it is not the correct one, click on "Disconnect" and redo the process from the begining
- Select the correct Login & Environment :
- For UAT connect to Sandbox , then Authorize
- Click Allow Access and check the login to validate the environment.
- Then close the dialog :
- Select the correct Login & Environment :
Now it's time to import existing record.
- Create a new SHEET
- Click Import then SOQL EDITOR
- Paste the query :
select id,IsoCode,ConversionRate from CurrencyType
- Click on execute
- the result should be like :
- the result should be like :
- Sheet 1 : PROD - CurrencyType
- SOQL : select id,IsoCode,ConversionRate from CurrencyType
- Sheet 2 : UAT - CurrencyType
- SOQL : select id,IsoCode,ConversionRate from CurrencyType
- SOQL : select id,IsoCode,ConversionRate from CurrencyType
2.2 / Map the new ConversionRate field from the BW Extract based on UAT Sheet & in PRD Sheet :
- CurrencyType.IsoCode = BWQuery.From Currency (column A)
- BWQuery.Column of the requested month to be updated and apply the following formula :
- if value < 0 then : apply -1 / value
2.3 / Run the update via the Salesforce inspector in UAT then in PRD.
2.4 / Close the Service One Request.
- Expand the Vlookup.
- Note : It is normal if some VLookup retreive N/A
- Column C Header should be renamed
- "_ConversionRate"
- Column D Header should be renamed
- "ConversionRate"
Step 2.2 - Yearly Rate
Step 2 - Yearly rateUpdate
Weare going to prepare the file to be loaded in UAT & PROD.
UAT & PROD must be setup in the same time.
In case of Yearly Rate, the Service One request need to provide a Start datestart date is set by default to January 1st.
2.1 / From that GSheet extract the following in order to create a new Sheet :
select id, IsoCode, ConversionRate from DatedConversionRate
- BWQuery.From Currency (column A) ==> Column name : "IsoCode"
- BWQuery.Conversion rate ==> Column name : "ConversionRate"
- BWQuery.Column of the requested year to be updated and apply the following formula :
- if value < 0 then : apply -1 / value
- new Column ==> "StartDate" = date provided in the Service one request
- As we did for 2024, startdate was not able to update.
Step 3 : Create a Jira ticket for maitenance
On the Core Maintenance Jira : CCCME board - Agile Board - Jira (atlassian.net), create a TASK
Example of TASK : [CCCME-6883] CORE - Update UAT & PROD April - Currency Rate - Jira (atlassian.net)
| Jira | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
Name of the task :
- For Monthly Update :
- Monthly FX Rate Update $Month
- For Yearly Update :
- Yearly FX Rate Update $Year
Description :
Please update the FX rate first in UAT then in PRD based on the following data :
Paste here the google sheet link
Once it it done, please add the result in the GSheet or tracking history.
Step 4 : Run the Update
Let's run the update
- Select The whole Values from A to D and the whole rows with values (do not select only Columns)
- In the Extension Click on back in order to get the menu
- Click on Update :
- Set the correct values
- Object type = Currency Type
- Update action
- ID Column shold be A
- Result should be E
- Click on Next
2.3 / Run the update via the Salesforce inspector in UAT then in PRD.
Gsheet where the data loads are saved.
2.4 / Close the Service One Request.
Step 2 - Yearly rate Update
2.2 / Copy the new sheet in Salesforce Inspector Import in order to INSERT new Records.
Do it first in UAT, then PRD
2.3 / Close the Service One Request.