2)Transactional data - Historical sales

Final customer that receives the product.  (it can be blank→ it is different than ship to mentioned above) 

= Final consignee ID

Why empty here?

NG: Is this the right field to use for it

Key_[4CPCOPA03-TGB2_KEYACC].[24CPCOPA03-TGB2_KEYACC]

Yes, the field is 4CPCOPA03-TGB2_KEYACC.

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

Yes and no. Manufacturing plant is the main plant where the product has been built (not sure if it is always the last one). Shipping plant is the plant the product has shipped from.

It happens that the product is built in a plant, but for freight reasons, the product is first transferred to a different plant before being shipped to the customer. This last plant is the shipping plant.

Which one is correct? 

[C_MATNR2] Material\Attributes\[C_PLANT] Plant

[CPCOPA03P] Further Characteristics\[C_PLANT] Plant

We're taking the one from COPA03 but you have to check where it's really coming from.

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

NG: Relation between Order Line and DOCNUMB and the relation between them

The Order Line is a subset of the Sales Order (that's why you retrieve the Sales Order id as a prefix in the Order Line id).

[C_PLELMN2] P&L Element

[C_PLELMN2] P&L Element

 and use the Gsheet to group based on the records in COPA03

Consolidation view= yes

To capture the consolidated view in Transparency, the sales measures including QTY and Amount of the companies selling materials/products provided by the query  are multiplied by their corresponding integration rate available in COPA03

------------------

Consolidation view= No 

Company integration rate is not applicable

Note: CS price optimization  is interested in Console view= yes

if source system starts with NERP, transparency uses PRS_Company_code_Company_Integ_Rate__Key_

[4CPCOPA03-CCDE_COMPPRS] PRS Company code\Attributes\[K_INTRAT] Company Integ Rate

For other source sytems: 

Company_code_Company_Integ_Rate__Key_

[C_COMPCDE] Company code\Attributes\[K_INTRAT] Company Integ Rate

It is used for the calculations regarding the internal/external sales in the transparency. It is related to the companies that are buying the materials/products (Trading partner or customer). It specifies selling to Solvay or Non-Solvay. 

The sale can be either external or internal.

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

Note:

-CS price optimization  is interested only in External sales

-there are sales measures readily available in the query as internal and external sales. It is up to the designer to use 1) directly the external and internal measures offered by the query separately  or 2) use/calculate total (internal+external) and then multiplies it by the company integration rate of the trading partner 

[C_PCOMPAN] Trading Partner\Attributes\[K_INTRAT] Company Integ Rate

We're not using the company integration rate. The key figures we're using are already based on this information (for costs only).

It is used only in the case of internal sales calculations. It captures the internal sales whether the sales is within the GBUs or outside of the GBU. For example, CS can sell to other GBUs like SpP.  Also, it can sell internally to the  companies within the CS. 

I am not sure if it is the right field

[C_PCOMPAN] Trading Partner\Attributes\[C_FLGIN03] Intercomp. Flag(SCO)

The field shall be "Intra_GBU_Flag_Key":

[24CPCOPA03-ITBU_INTGBUF]

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

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.

Important note: Fore CS and TS, it uses the Zone available in the customer dimension: Master data: CUSTOMER

Yes, for both CS and TS, we use the field Zone from the Customer master data.

However, there are exceptions:

- for CS -

• if the customer country is "MX", then we push "LAM"

- for TS -

• if the group of activity is "TSMIN" and the customer country is either "GT", "HN", "NI" or "PA", then we push "NAM"
• if the group of activity is "TSMIN" and the customer country is "MN", then we push "EMEA"
• if the group of activity is "TSADT" and the customer country is "MX", then we push "LAM"

Note:

For CS and TS, the values are cleaned to match the following list:

• APAC
• EMEA
• LAM
• NAM

SpP : ECCO region (salesforce ) to discuss later 

Novecare/TS : Region (Zone from COPA03) of Ship to with MX moved to LATAM

Aroma = Region H1

(The red ones are not priority for this quarter)

NG: Need discussion to understand it

For NoveCare is the Combination of the ship to, material, and incoterm

Suggested to have a separate table (Dimension) for CPC. When designing the data model, it is important to consider that CPC definition varies from one target application to another.  

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 

Measures\N8110 - Qty Sold Internal

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

-----------------

Measures\Net sales Internal (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. The calculation of the unit price at line item level is also used to calculate the last invoice price below. 

Measures\N8110 - Qty Sold External

Measures\Net sales External (Sales+Royalties) - LC

=Sum (Measures\Net sales External (Sales+Royalties) - LC)/Measures\N8110 - Qty Sold External)

Measure:

Last invoice price (€/KG)

For the calculation of this measure see the following page: Last invoice price calculation 

Last invoice price is associated with a CPC and it is being recalculated every time the transparency dashboard is being refreshed synced with COPA03

This measure also used to calculate the forecast price