IMPORTANT Note: Only for composite material until jun2021, there was a file (file from Cognos), the rest from COPA03
| Requirements | Description | Comment | Source of the transparency dashboard | Technical name in the source system (BW mainly) | Transformation |
| Source system | [0LOGSYS] Source System | ||||
| Month | Temporal attribute | [0CALMONTH] Calendar Year/Month | |||
| Sold to | Refers to customer sold-to | COPA03 | [CPCOPA03P] Further Characteristics\[C_SOLDID] Sold-to party | ||
| Ship to | Refers to customer Ship-to | COPA03 | [CPCOPA03P] Further Characteristics\[C_SHIPID] Ship-to party | ||
Material | Material sold by Solvay | COPA03 | [C_MATNR2] Material | ||
| Shipping plant | Plant from which the material is ship to a customer | COPA03 | [C_MATNR2] Material\Attributes\[C_PLANT] Plant [QVSBS_BW_QRY_CPCOPA03_0001] QV - BW P&L & Integrated Margin Query (CAR3)\[CPCOPA03P] Further Characteristics\[C_PLANT] Plant | ||
| Manufacturing plant | Last plant in the product/material production process | WP1: [4CPCOPA03-MAP2_PRPLANT] Production plant in Material_W (Key) [C_ZZWWE41] Origin Plant (Key) | Marged 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 | ||
| Sales order (Sales order number) & line items | 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 dashboard but less priority because they are not relevant for CS price optimization [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 | ||
| P&L element | Note: The info that the color coding represents in Gsheet is not needed for CS price optimization but the suggestion is to consider when designing the data mart for other applications. | [C_PLELMN2] P&L Element | [C_PLELMN2] P&L Element and use the Gsheet to group based on the records in COPA03 | ||
| Company | Legal entities owned by Solvay. It can be full ownership or partial ownership | CODE? NAME? | |||
| Consolidated view | In BW, the consolidation view deals with internal and external sales Consolidation view in transparency dashboard provides consolidated measures. To calculate consolidated measures, the external-related measures of COPA03 are multiplied by company integration rate. measures related to external sales. The integration rate is available in COPA03 and ranged between 0 to 100%. Integration rate equal to100% means the company is fully owned by Solvay. to calculate a consolidated view of the external sales in transparency for a company with 50% integration rate, the amount of externals sale offered by COPA03 will be multiplied by 50%. | ||||
| Company integration rate | It is related to the consolidation view. It specifies the % of ownership of Solvay of a company | COPA03 | [C_COMPCDE] Company code\Attributes[K_INTRAT] Company Integ Rate | ||
| GBU product family | One product (or material) belongs to a product family per GBU. It is unique per combination of GBU and product/material | 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 | ||
| GBU material group | It is unique per combination of GBU and product/material | To find out if there is any relation with customer | [4CPCOPA03-TGB2_C_GBR19] GBR: GBU Material Group (Ship-to) | ||
| GBU material subgroup | It is unique per combination of GBU and product/material | To find out if there is any relation with customer | [4CPCOPA03-TGB2_C_GBR20] GBR: GBU Material Sub-group (Ship-to) | ||
| CPC | 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 | 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: Actual Volume (KG) | The quantity of material sold. | 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\N8110 - Qty Sold External Measures\N8110 - Qty Sold Internal | COPA03 | Measures\N8110 - Qty Sold External | |
| Measures: Actual Sale (€) | The total amount of sale in local currency. | 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 | COPA03 | Measures\Net sales External (Sales+Royalties) - LC | |
| Measure: Actual Unit price (KG) | 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 page: Last invoice price calculation |