Final customer that receives the product. (it can be blank→ it is different than ship to mentioned above)
= Final consignee ID
Material
Suggested to consider one dimension for plant in the target data model that includes the list of plants with their SAP codes to be linked to the transactional data.
(Manufacturing plants might be intermediate plants before shipping plants. is this statement correct?)
[C_MATNR2] Material\Attributes\[C_PLANT] Plant
[CPCOPA03P] Further Characteristics\[C_PLANT] Plant
WP1: [4CPCOPA03-MAP2_PRPLANT] Production plant in Material_W (Key)
[C_ZZWWE41] Origin Plant (Key)
Merged based on the PF1 and WP1 sources
WP1: [4CPCOPA03-MAP2_PRPLANT] Production plant in Material_W (Key)
PF1: Origin_Plant_Key
[C_ZZWWE41] Origin Plant
Each sale order might include multiple line items
Suggestion is to consider to build another dimension in the target model for sales order (including sales order, line item, ....)
Note: the following fields in COPA03 are used in transparency. They are not relevant for CS price optimization (low priority)
[C_DOCNUMB] Sales document\Attributes\[C_SHIPCD2] Shipping conditions (core)
[C_ITM_SD] Sales document item\Attributes\[C_LSTREQ] Cust last req del dt
[C_ITM_SD] Sales document item\Attributes\[C_PO_ITM] Purchase Order Item
[C_DOCNUMB] Sales document (Key & name)
[C_DOCNUMB] Sales document\Attributes\[C_DOCTYP2] Order document type (key and name)
[C_ITM_SD] Sales document item (key & name)
[C_ITM_SD] Sales document item\Attributes\[C_CDATE2] Order Line Creation date
COPA03
There is file to categorize p&l element in transparency dashboard
[C_PLELMN2] P&L Element
[C_PLELMN2] P&L Element
and use the Gsheet to group based on the records in COPA03
To capture the consolidated view of a company in Transparency, the sales measures including QTY and Amount of the company provided by the query are multiplied by its integration rate available in COPA03.
[C_COMPCDE] Company code\Attributes[K_INTRAT] Company Integ Rate
To capture the External sales in Transparency, the sales measures including QTY and Amount of the company provided by the query are multiplied by its integration rate available in the Trading partner dimension of COPA03
Measures\N8110 - Qty Sold
inside GBUs: If it Only for internal calculations we use this field
One product (or material) belongs to a product family per GBU.
It is unique per combination of GBU and product/material
Suggested to be in the separate dimension table. (In Q3 it can be considered at transactional level in the fact table→ Improvement to be in Q4)
Currently in the transparency dashboard there are various rules based on the GBUs to populate this field. Using COPA03 for this field, the data has been populated at sales level where both product code and GBU are available to return the GBU product family. The challenge is that, there is one product/material sold by more than one GBU that might be given various GBU product family from one GBU to another . It makes it difficult to keep the GBU product family as an attribute of product dimension in the target model to be designed.
Aligning between all the GBUs might be considered
If GBU = SP then Mapping based on PRODUCT_KEY (from icare)
mapping is : input Product2 . Material_Group__c output Product2 . Family
if either GBU= CS or TS AND SOURCE_SYSTEM_KEY= PF1_020
Material_Product_Hier__LIP03___Key_
[C_MATNR2__C_PRDHIER] Product Hier (LIP03)
Else
if GBU is PA GBR__GBU_Material__Group__Ship_to_ Else Material_Product_Family_Code__Medium_Name_i
It is unique per combination of GBU and product/material
Suggested to be in the separate dimension table. (In Q3 it can be considered at transactional level in the fact table→ Improvement to be in Q4)
To find out if there is any relation with customer
Suggested to be in the separate dimension table. (In Q3 it can be considered at transactional level in the fact table→ Improvement to be in Q4)
To find out if there is any relation with customer
It is a customize dimension to reflect the pricing organization of the GBUs. There are 4 known Solvay regions but it can happen that for one activity, GBU decides to change the region for a country.
Keep it at transactional level at this Q. maybe discussing the improvement later to consider a separate dimension
Important note: it is different from Zone in the customer dimension page: Master data: CUSTOMER
SpP : ECCO region (salesforce ) to discuss later
Novecare/TS : Region (Zone from COPA03) of Ship to with MX moved to LATAM
Aroma = Region H1
Forr NoveCare is the Combination of the ship to, material, and incoterm
This is the lowest granularity required for the calculation of measures in the CS price optimization data set
CPC (Ship-to+Material+incoterms)
Ship-to
[QVSBS_BW_QRY_CPCOPA03_0001] QV - BW P&L & Integrated Margin Query (CAR3)\[CPCOPA03P] Further Characteristics\[C_SHIPID] Ship-to party
a table with many attributes
Material
[QVSBS_BW_QRY_CPCOPA03_0001] QV - BW P&L & Integrated Margin Query (CAR3)\[CPCOPA03P] Further Characteristics\[C_MATNR2] Material
Incoterms
[QVSBS_BW_QRY_CPCOPA03_0001] QV - BW P&L & Integrated Margin Query (CAR3)\[CPCOPA03P] Further Characteristics\[C_INCOTRM] Incoterms_P
Measures\N8110 - Qty Sold External
Measures\N8110 - Qty Sold Internal
Measures\N8110 - Qty Sold External
Measures\N8110 - Qty Sold
Measures\N8110 - Qty Sold Internal
EXternal + internal = QTY sold
The transparency dashboard uses the measures in COPA03 that offer the sale with LC (Local Currency).
Important: The car3 fx rate will be used for conversion to Euro
In COPA03, both external and internal sales are available. In some applications both are important but for CS price optimization only external sales is important.
Measures\Net sales External (Sales+Royalties) - LC
Measures\Net sales Internal (Sales+Royalties) - LC
Measures\Net sales External (Sales+Royalties) - LC
This is the average price per unit of measurement. This is calculated in the transparency dashboard:
Actual unit price = Actual sale/Actual volume
In the transparency dashboard, the lowest granularity level to calculate this measure is the line item but for CS optimization the lowest granularity level is CPC.
Measures\N8110 - Qty Sold External
Measures\Net sales External (Sales+Royalties) - LC
=Sum(Measures\N8110 - Qty Sold)/ sum(External/Measures\N8110 - Qty Sold External)
Measure:
Last invoice price (€/KG)
For the calculation of this measure see the following page: Last invoice price calculation