Status

DRAFT

Release date


Issues

Last modified dateLast modified byComment
Romain BABLOT

 

Creation



0/ Process

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

  • Access to Salesforce as an Admin Line  in order to be able to select following salesforce object : 

    • CurrencyType

    • DatedConversionRate

  • Access to Salesforce 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
  • 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 Rate 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/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 :

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 : 





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)
    • 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 Update

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 start 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)


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.