Quotes extract from SFDC
Interface SFDC Quotes for DynaSys
Context and Business value
For recurrent business, Sales Reps create Quotes in Salesforce. And on the same time they have to go to DynaSys to fill the recurrent forecast for volumes and prices, for the next 12 months.
This represents a double workload with no added value.
Peroxides asks for us to study the possibility to create a new interface between Salesforce and DynaSys, in order to upload directly in the DynaSys contract section all the accepted quotes.
Depending on the GBU, we will have to interface only volumes, or volumes and prices.
Functional Specification : 9723 - Interface CRM Quote to DynaSys DP
Functional Requirement
DynaSys is supporting the sales forecast process to produce sales forecasts shared in each GBU organisation. Currently, forecasts are managed mainly on a 18 months horizon, on products and customers already active in SAP.
The objective of this interface is to integrate SFDC accepted Quotes in DynaSys and get a sales forecast visibility on customers which are not currently active in SAP.
The requirement is coming from the GBU Peroxides and Soda Ash & Derivatives may be also interested.
See the presentation below detailing the functional specifications:
SFDC Quotes data extract
File format: CSV flat file.
Name: DYN_SF_QUOTES
Field separator = ‘;’
Decimal separator = ‘.’
Selection:
GBU = Peroxides or Soda Ash & Derivatives
Status EQUAL TO “Accepted”
Product level in Quote Line Item = Material (Product level 5)
Include in Reporting / Accepted = TRUE
Take the Ship to of the Quote Line Item if existing, otherwise take the one of the Quote header
Rules to be applied in DynaSys :
Ship-To.Record Type = SAP Customer
Ship To NOT NULL at Quote header AND Quote line item levels +
Fields:
csv text file | SFDC Object | SFDC Field | Example of value | |
GBU | Quote | GBU: Code | SELECT Code__c FROM SLV_Utility__c where SLV_Type__c = 'GBU' AND Name = SLV2_quoteline_Quote__r.SLV2_quote_GBUp__c | ‘SD’ for SA&D |
BU | BU | Opportunity | SLV2_quoteline_Quote__r.SLV2_quote_BUp__c | SLV6_OPP_BU__c |
DC | DC | Utility | SELECT SLV10_Distribution_Channel__c FROM SLV_Utility__c where SLV_Type__c = 'BU' AND Name = SLV2_quoteline_Quote__r.SLV2_quote_BUp__c if SLV2_quoteline_Quote__r.SLV2_quote_BUp__c is empty or if the result of the query is null, let the column DC empty | ‘9S’ |
Account Record Type | Quote | Account Name: Account Record Type | SLV2_quoteline_Quote__r.SLV2_quote_Account__r.RecordType.Name | ‘SAP Customer’ |
Account ID | Quote | Account Name: Account ID | SLV2_quoteline_Quote__r.SLV2_quote_Account__r.Id | ‘0012400000atlxb’ |
PRS ID | Quote | Account Name: PRS ID | SLV2_quoteline_Quote__r.SLV2_quote_Account__r.PRS_ID__c | ‘338671’ |
RCS ID | Quote | Account Name: RCS ID | SLV2_quoteline_Quote__r.SLV2_quote_Account__r.RCS_ID__c | ‘2014850’ |
Account Name | Quote | Account Name: Account Name | SLV2_quoteline_Quote__r.SLV2_quote_Account__r.Name | ‘PT SAYAP MAS UTAMA NPWP: 01.304.651.1-092.000’ |
City | Quote | Account Name: City | SLV2_quoteline_Quote__r.SLV2_quote_Account__r.BillingCity | JAKARTA |
Country | Quote | Account Country: Code | SELECT Code__c FROM SLV_Utility__c where SLV_Type__c = 'Country' AND Name = SLV2_quoteline_Quote__r.SLV2_quote_Account__r.BillingCountry and Language_Key__c = 'EN' | ‘ID’ for Indonesia |
Ship To Record Type | Quote Line Item or Quote if no input at Quote Line item level | Ship To Name: Account Record Type | If SLV2_quote_Ship_to__r.Id is not null Then SLV2_quote_Ship_to__r.RecordType.Name Else SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.RecordType.Name | ‘SAP Customer |
Ship To ID | Quote Line Item or Quote | Ship To Name: Account ID | If SLV2_quote_Ship_to__r.Id is not null Then SLV2_quote_Ship_to__r.Id Else SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.Id | ‘0012400000atlxb’ |
Ship To PRS code | Quote Line Item or Quote | Ship To Name: PRS ID | If SLV2_quote_Ship_to__r.Id is not null Then SLV2_quote_Ship_to__r.PRS_ID__c Else SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.PRS_ID__c | ‘338671’ |
Ship To RCS code | Quote Line Item or Quote | Ship To Name: RCS ID | If SLV2_quote_Ship_to__r.Id is not null Then SLV2_quote_Ship_to__r.RCS_ID__c Else SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.RCS_ID__c | ‘2014850’ |
Ship To Name | Quote Line Item or Quote | Ship To Name: Account Name | If SLV2_quote_Ship_to__r.Id is not null Then SLV2_quote_Ship_to__r.Name Else SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.Name | ‘PT SAYAP MAS UTAMA NPWP: 01.304.651.1-092.000’ |
Ship To City | Quote Line Item or Quote | Ship To Name: City | If SLV2_quote_Ship_to__r.Id is not null Then SLV2_quote_Ship_to__r.BillingCity Else SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.BillingCity | JAKARTA |
Ship To Country | Quote Line Item or Quote | Ship To Country: Code | If SLV2_quote_Ship_to__r.Id is not null Then SELECT Code__c FROM SLV_Utility__c where SLV_Type__c = 'Country' AND Name = SLV2_quote_Ship_to__r.BillingCountry and Language_Key__c = 'EN' Else SELECT Code__c FROM SLV_Utility__c where SLV_Type__c = 'Country' AND Name = SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.BillingCountry and Language_Key__c = 'EN' | ‘ID’ for Indonesia |
Product Code | Quote Line Item | Product: Product Id: Product Code | SLV2_quoteline_Product__r.ProductCode | ‘60102’ (Level 5) |
Employee Number | Quote | Quote Owner: Employee Number | EmployeeNumber | ‘63014233’ for Markus WU |
Employee Name | Name | |||
User | Interface Key | SLV3_Interface_Key__c | ||
Price validity from | Quote | Price validity from | SLV2_quoteline_Quote__r.SLV2_quote_Price_Validity_from__c | 01/01/2019 |
Price validity to | Quote | Price validity to | SLV2_quoteline_Quote__r.SLV2_quote_Price_Validity_to__c | 31/12/2019 |
Volume | Quote Line Item | Product: Volume From (TO) (if price 100% -> volume 100%) | If SLV2_quote_concentration__ == 100 Then SLV4_quoteline_volumeFrom_for_100_t__c Else SLV4_quoteline_volumeFrom_t__c | 3000 |
Unit of Measure (TO) | Quote Line Item | Product: Unit of Measure: Name | SLV2_quoteline_UoM__c | ‘TO’ |
Concentration | Quote Line Item | SLV5_QLI_Concentration__c | ||
Status | Quote | Status | SLV2_quoteline_Quote__r.SLV2_quote_Status__c | ‘Accepted’ |
Currency | Quote Line Item | Product Information: Currency | CurrencyIsoCode | USD |
Price (Currency/TO) | Quote Line Item | Price per UoM (currency/TO) If price 100% -> price for 100% concentration (currency/TO) | If SLV2_quote_concentration__ == 100 Then SLV4_quoteline_Price_for_100_t__c Else SLV4_quoteline_price_t__c | ‘1000’ |
Source System | Product? | ERP Source system of product code | SLV2_quoteline_Product__r.SLV5_1_SAP_Origin__c | PRS or PF1 / RCS or WP1 |
Quote ID | Quote | SLV2_quoteline_Quote__r.Id | ||
Quote Line Item ID | Quote Line Item | Id |
Select SLV2_quoteline_Quote__r.Id, SLV2_quoteline_Quote__r.SLV2_quote_GBUp__c, SLV2_quoteline_Quote__r.SLV2_quote_BUp__c,
SLV2_quoteline_Quote__r.SLV2_quote_Account__r.RecordType.Name, SLV2_quoteline_Quote__r.SLV2_quote_Account__r.Id, SLV2_quoteline_Quote__r.SLV2_quote_Account__r.PRS_ID__c, SLV2_quoteline_Quote__r.SLV2_quote_Account__r.RCS_ID__c, SLV2_quoteline_Quote__r.SLV2_quote_Account__r.Name, SLV2_quoteline_Quote__r.SLV2_quote_Account__r.BillingCity, SLV2_quoteline_Quote__r.SLV2_quote_Account__r.BillingCountry,
SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.RecordType.Name, SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.Id, SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.PRS_ID__c, SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.RCS_ID__c, SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.Name, SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.BillingCity, SLV2_quoteline_Quote__r.SLV2_quote_Ship_to__r.BillingCountry,
SLV2_quoteline_Quote__r.SLV2_quote_Price_Validity_from__c, SLV2_quoteline_Quote__r.SLV2_quote_Price_Validity_to__c, SLV2_quoteline_Quote__r.SLV2_quote_Status__c,
TYPEOF SLV2_quoteline_Quote__r.Owner
WHEN User THEN EmployeeNumber, Name, SLV3_Interface_Key__c, Email
END,
Id,
SLV2_quote_Ship_to__r.RecordType.Name, SLV2_quote_Ship_to__r.Id, SLV2_quote_Ship_to__r.PRS_ID__c, SLV2_quote_Ship_to__r.RCS_ID__c, SLV2_quote_Ship_to__r.Name, SLV2_quote_Ship_to__r.BillingCity, SLV2_quote_Ship_to__r.BillingCountry,
SLV2_quoteline_Product__r.ProductCode, SLV2_quoteline_Product__r.SLV5_1_SAP_Origin__c, SLV2_quote_concentration__c, SLV4_quoteline_volumeFrom_t__c, SLV4_quoteline_volumeFrom_for_100_t__c, SLV2_quoteline_UoM__c, SLV5_QLI_Concentration__c, CurrencyIsoCode, SLV4_quoteline_price_t__c, SLV4_quoteline_Price_for_100_t__c from SLV2_Quote_Line_Item__c where SLV2_quoteline_Product__r.Type__c = 'Level 5' and SLV4_quoteline_Included_in_Reporting__c = true and SLV2_quoteline_Quote__r.SLV2_quote_Status__c = 'Accepted' and SLV2_quoteline_Quote__r.SLV2_quote_GBUp__c in ('Peroxides', 'Soda Ash & Derivatives')
Interface schedule:
- Every day at 00:30 CET
- Transfer file by SFTP to destination
server wdcdyap21.eua.solvay.com
folder D:\DynaSys\ITF\Files\In
Mapping SFDC - DynaSys ⇒ TBD by DynaSys
File integration (Existing DFU)Lines to be taken into account
Only Quotes for which the Ship-To is a SAP Account should be taken into account :
> Delete lines with Ship-To Record <> SAP Account
Suffix rule
A suffix needs to be added to the product code and SAP customer codes when used:
- If SourceSystem = “PF1” then suffix = ‘S’
- If SourceSystem = “RCS” then suffix = ‘R’
Material:Shipto@DC + Shipto@DC tables
Field | Rule |
Material | = Product code + suffix |
Ship-to | If SourceSystem = “PF1” then Ship-To = Ship-To PRCS + Suffix = ‘S’ If SourceSystem = “RCS” then Ship-To= Ship-to RCS + Suffix = ‘R’ |
DC | If BU = Peroxides, DC = PEROX If BU =, Bicarbonate and Derivatives, DC = SDERI If BU = Soda Ash, DC = SDSOA |
Datafields
Parameter CRM Opportunities (ID 16795157), in U00 - Import - IN model:
Field | Rule |
Quote LC | Currency |
Quote Price [LC/TO] | Based on the Price and with the Price validity dates Price(Currency/TO) |
Quote Volume | Based on the Volume and with the Price validity dates |
Volume integration rule :
New DFU creation
Field | Rule |
Shipto | Result of customer algorithm (add suffix if SAP code) |
City | = [Customer_Country]_ZZ_[Customer_City] |
Region | = [Customer_Country]_ZZ |
Country | = Customer Country |
Sales Rep / Account Manager
If GBU = SD, PE
Interfaced in ProductGroup:Shipto@BU table
Field | Rule |
ProductGroup | = ProductGroup from Material hierarchy |
Shipto | = Result of customer algorithm (add suffix if SAP code) |
BU | = BU from Material hierarchy |
Account Manager | = Sales Rep Account Manager Code |
If GBU = SI, no mapping.