Note: For all sheets to explore who owns the sheet and how it is maintained
Clean the fieklds which are
| Target model dimension | Requirements | Description | Comment | Existing source in transparency | Technical name in transparency source (mainly BW)→ table name and attribute name | Transformation/Rules for transparency dashboard | SF source | Comment about SF | Searching in BW (meeting with Anon & Saint) |
|---|---|---|---|---|---|---|---|---|---|
| Customer | Customer ID | Identified with PRS Code, RCS Code, SAP Code or SF Code | SAP BW/SF | table ACCOUNT in CORE | |||||
| Fact table | Parent ID = Customer group ID | A customer might not have any parent To investigate further to have one rule | SAP BW / File QVSBS_BW_QRY_CPCOPA03_0001 | → | To be confirmed by Ayoub and Franck Identified with file 1zicl5VUcl1NyDLGZXQRDmQypY0-uwc4yUtuPRu1scxU . Group Mapping By mapping with the GBU and C_SOLDID If no match, then take QVSBS_BW_QRY_CPCOPA03_0001 . C_SOLDID . C_CORPGR For Ship-To, take QVSBS_BW_QRY_CPCOPA03_0001 . C_SHIPID . TXTLG If composed, take the name after "/" | table ACCOUNT: Available in CORE and iCare | ParentID | ||
| Fact table | Ship to ID | SAP code for customer to whom Solvay is shipping the products | QVSBS_BW_QRY_CPCOPA03_0001 QV_BW_QRY_MVSDSO61_0001 QV_BW_QRY_CPCOPC07_0001 | [C_SHIPID] Ship_to_party Key_[C_SHIPID].[2C_SHIPID] | The attributes in the CORE in the table ACCOUNT Partner_Sub_type | There is no relation between the ship to and sold to in SF | |||
| fact table | Sold to ID | SAP code for customer to whom Solvay is selling the products | |||||||
| Fact table | Ship to KA ID | Final customer that receives the product. (it can be blank→ it is different than ship to mentioned above) | Currently in transparency it has been received from P&L query | C_GBR15 = ship-to-KA | |||||
| Fact table | Final consignee ID | =Ship to KA ID | Only SpP related. | C_CUSTID__0CUS_F_CONS not sure | |||||
| Customer | Sales rep ID | It is linked to account reccording | QVSBS_BW_QRY_CPCOPA03_0001 | Identified with QVSBS_BW_QRY_CPCOPA03_0001 . Sales_Employee__Sold_to__E_Mail_Address__Key_ Or QVSBS_BW_QRY_CPCOPA03_0001 . Sales_Employee__Ship_to__E_Mail_Address__Key_ | In CORE, Sales Rep (CSR Customer Service Representative) and account manger master data available in ACCOUNTTEAMMEMBER → attributes: Account ID, USER, Team member role (coming from SAP). One account can be managed by several GBUs so there is a one to many relation between account and CSR In icare it is different. | Master data of Sales rep and account manger are in SAP. The data flows daily basis to SF per GBU. This info are coming from sales area of the SAP side. Related to sales area in SAP. ??When data integrated from SAP to SF, there are rules to make it at GBU level | Sales Employee (Sold-to)_[C_CUSTSAL__C_SALEMP] Table: /BIC/MC_CUSTSAL containing the sales rep belonging to a sales area (sales area is a combination of division, distribution channel, organization) | ||
| Sales rep | Sales rep name | Table name: | |||||||
| Sales rep | Sales rep email | ||||||||
| Customer | Customer Short name | QVSBS_BW_QRY_CPCOPA03_0001 . C_SOLDID . TXTSH Or QVSBS_BW_QRY_CPCOPA03_0001 . C_SHIPID . TXTSH | |||||||
| Customer | Customer Long name | QVSBS_BW_QRY_CPCOPA03_0001 . C_SOLDID . TXTLG Or QVSBS_BW_QRY_CPCOPA03_0001 . C_SHIPID . TXTLG | |||||||
| Customer | Country | Country ship to: Country of the customer delivered ---------- Country sold to: Country of the sold-to company- Invoice party | Country is available in SF and BW. In case of discrepancy, SF is more reliable | QVSBS_BW_QRY_CPCOPA03_0001 QV_BW_QRY_MVSDSO61_0001 QV_BW_QRY_CPCOPC07_0001 ---------------- QV_BW_QRY_MVSDSO61_0001 | Country (Key)_[C_SHIPID].[20COUNTRY] [4CPCOPA03-SHIP_COUNTRY] Country of destination [10COUNTRY] Ship-to party > Country (Name) C_SHTCTRY ------------ Country (Name)_[C_SOLDID].[10COUNTRY] "[0COUNTRY] Country if empty => [C_SHIPTID] Ship-to party\Attributes\[0COUNTRY] Country key" | QVSBS_BW_QRY_CPCOPA03_0001 . C_SOLDID . 0COUNTRY Or QVSBS_BW_QRY_CPCOPA03_0001 . C_SHIPID . 0COUNTRY | |||
| Customer | Zone | Naming unificationn has been defined in transparency dashboard (see the transformation column) From one GBU to another zone might be different. It has been unified | GBU Ship-to zone (BW) 1_[4CPCOPA03-TGB2_ZONEH1] | Mapping on QVSBS_BW_QRY_CPCOPA03_0001 . Sold_to_party_Geographie_Zone__Key_ Or Mapping on QVSBS_BW_QRY_CPCOPA03_0001 . Ship_to_party_Geographie_Zone__Key_ | available in SF What table? | ||||
| Customer | PRS code | SF | Account . RCS_code_Account__c | In CORE, there is PRS=PF1 (PRS ID) , RCS = WP1 (RCS ID) . (other ERP ID). Available in ACCOUNT table. | To check→ in SAP it happens that there are two accounts for one customer. For example if customer change the VAT, a new account will be created. In SF it doesn't happen so that there is no one to one from SF to SAP. In such a case, the new account is replaced but all the changes in SAP are tracked an another attribute (PRS_ID_history) . Note: In general to track the changes in an attributes in SF, ACCOUNTHISTORY is the table that include historical and changes of the records. Example, if a customer number change, the new one is in the main table and the old number is backed up in ACCOUNTHISTORY table. | /BIC/MC_CUSTID-/BIC/C_CUSTPRS it is used to link PF1 and WP1 | |||
| Customer | RCS code | SF both | Account . PRS_Code_Account__c | Refer to RCS code comment | nothing | ||||
| Customer | SAP code | SF both?/SAP | Account . SAP_Sold_To_Number__c Or QVSBS_BW_QRY_CPCOPA03_0001 . C_SOLDID Or QVSBS_BW_QRY_CPCOPA03_0001 . C_SHIPID | Refer to RCS code comment | customer ID | ||||
| Customer | SF code | All the above codes are available in SF but the doubt/question is that if for a new customer, the corresponding data is available right away in the SF so that we won't have a gap when using SF as the source. It is also to check if data is available in both SF (icare and Core) Currently in the transparency dash all the codes are from SF but it is better o explore if there is a better source. | SF | Account . Id | there is no track in BW | ||||
| Customer | Account manager | Account manager and sales rep are both are solvay employee. The data at the target application is used at CPC level. The info are available in all data sources but the challenge is that there are different values from one to another. It has been prioritized for Q4 It is only for iCare not for CORE | SF | Account . Owner_Name__c | Refer to Sales rep ID comment combination of sales area, division, and distribution channel defined for a GBU. | ? | |||
| Customer | Team cluster | Only in iCare and should be empty for CORE Franck comment: Three ways to get the data
1NVHMJOTRYA3tR7Pf6HCirAJb2ik7JCRD5b2CrLnSVuc Note: it is used to get the market cluster (link with market cluster) | SF | Using API for Account . Team_Cluster__c | This concept is only available in iCare. it is a way in in SpP to associate an account to a team (Sales team?) Corresponding concept in CORE might be BU (to check) Notes: -Team cluster refers to the sales team in Solvay that handles an account. It is a way to associate an account to a sales team in solvay. Each sales team handles a specific (market) like automotive. battery, etc. One account might be purchasing from various teams. in that case, team cluster is the one with the biggest sale. → that makes the Market cluster different from team cluster. | ? | |||
| Market | GBU customer segment | A customer receiving different service levels could be classified as KA for one GBU and Standard for other. Exceptions: GSKA- they remain GSKA for all GBUs. | COPA03 The Gsheet below is used for this field as well | technical name: GBU Segm. (Sold-to)_[C_CUSTSAL__0CUST_GRP2] Customer_Sales_Area_Customer_group_2__Name_ | In CORE, the table name is GBU_CUSTOMER_SEGMENTATION . | GBU customer segmentation shows what GBUs are related to an account. There is one GBU segmentation per account for a GBU | C_CUSTAL | ||
| Customer | Group customer segment | It refers to Solvay group level of segmentation for customer. Depends on the service level a customer receives from Solvay, it can be classified as KA, or standard, etc. The same customer segmentation can be applied at GBU level (below) | only in BW | COPA03 Sold_to_party_Group_Segm___Corp___Name_ and COPA03 Sold_to_party_Group_Segm___Corp___Name_ check if the field: if it contains "not assigned" or is empty, we display "Not Assigned" Then use the mapping file to clean the values of the field retrieved from COPA Colors in the mapping file is should have for transparency dashboard Note: For all sheets to explore who owns the sheet and how it is maintained | Bus_Rank__Corp_ Group Segm. (Corp) (Name)_[C_SOLDID].[1C_CUSTGRC] | Special rule: strategic ley account at group level are enforced to be strategic account for all GBU child account are following the parent account for GBU and group segmentation both (there are exceptions that can be specified by users) | it is not available in SF |