IMPORTANT Note: Only for composite material until jun2021, there was a file (file from Cognos), the rest from COPA03
| Fields in the P&L query QVSBS_BW_QRY_CPCOPA03_0001 | Rules | Corresponding measures in transparency | |
|---|---|---|---|
| 1 | [QVSBS_BW_QRY_CPCOPA03_0001] QV - BW P&L & Integrated Margin Query (CAR3)\[CPCOPA03P] Further Characteristics\[0LOGSYS] Source System | ||
| 2 | [0CALMONTH] Calendar Year/Month | ||
| 3 | Measures in the query: Measure\N8110 - Qty Sold External→ used for Actual Sales (€), Last invoice price and Actual unit price (€/KG), calculation Measures\Net sales External (Sales+Royalties) - LC→ used for Actual Volume (KG), Last invoice price, and Actual unit price (€/KG) calculation | Actual Sales (€) Actual Volume (KG) Last invoice price Actual unit price (€/KG) | |
| 4 | [C_COMPCDE] Company code\Attributes[K_INTRAT] Company Integ Rate | Alternatively the following fields can be considered with Company integration rate Maesure\N8110 - Qty Sold Internal Measures\Net sales Internal (Sales+Royalties) - LC In this case, to calculate the external sales & quantity, the figures of internal field are multiplied by the company integration rate precentage | |
| 6 | 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 | ||
[C_DOCNUMB] Sales document (Key & name) [C_ITM_SD] Sales document item (key & name) | |||
| Measures in the transparency | Definition | Calculations |
|---|---|---|
| Actual Sales (€) | The quantity of material sold per CPC. It has been calculated by the aggregation of quantities in sales orders transactions per CPC for the latest closed month | CPC Sum (Measure\N8110 - Qty Sold External |
| Actual Volume (KG) | CPC Sum (Measures\Net sales External (Sales+Royalties) - LC) | |
| Actual unit price (€/KG) | This is calculated in the transparency dashboard: Actual unit price = Actual sale/Actual volume To calculate the unit price per CPC, In the transparency dashboard, the calculation first is performed at the order/Line item level. see the table below for the calculation | [C_DOCNUMB] Sales document [C_ITM_SD] Sales document item Sum (Measure\N8110 - Qty Sold External/Sum (Measures\Net sales External (Sales+Royalties) - LC) |
| Last invoice price | The price for the last sales order item per CPC. For that the closing month is considered, the creation date of the last order line, and the latest sales order/order line in that day is considered Filter on the creation date of the last order line in case of many order created in the same day, the last order number which is the biggest one are taken intothe consideration |
| Requirements | Description | Comment | Source in transparency | Technical name of the source in transparency | Transformation |
|---|---|---|---|---|---|
| Source system | [0LOGSYS] Source System | ||||
| Sold to | COPA03 | [C_DOCNUMB] Sales document\Attributes\[C_SOLDTO] Sold-to party | |||
| Ship to | COPA03 | [CPCOPA03P] Further Characteristics\[C_SHIPID] Ship-to party | |||
Material | COPA03 | [C_MATNR2] Material | |||
| Shipping plant | Plant from which the material is ship to a customer | COPA03 | [C_MATNR2] Material\Attributes\[C_PLANT] Plant | ||
| Manufacturing plant | Last plant in the product/material production process | To ask [C_MATNR2] Material\Attributes\[C_PLANT] Plant For forcast COPC07 Manufacturing_Plant_Key ---------------------------------- Not relevant in this Q (planned for q4) → COPC04→ WP1 : C_PLANT PF1: ORIGIN PLANT [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\[CPCOPC02P] Further Characteristics\[C_MANPLNT] Manufacturing Plant | WP1: [4CPCOPA03-MAP2_PRPLANT] Production plant in Material_W → key PF1: Origin_Plant_Key | ||
| Sales order (Sales order number) | Include sales order and line item Each sale order might includes multiple line items | Suggestion is to consider to build another dimension in the target model for sales order (including sales order, line item, ....) | [C_DOCNUMB] Sales document (Key & name) [C_DOCNUMB] Sales document\Attributes\[C_DOCTYP2] Order document type (key and name) [C_DOCNUMB] Sales document\Attributes\[C_SHIPCD2] Shipping conditions (core) [C_ITM_SD] Sales document item (key & name) [C_ITM_SD] Sales document item\Attributes\[C_CDATE2] Order Line Creation date [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 | ||
| P&L element | The info that the color coding represents in Gsheet is not needed for Leo but the suggestion is to consider when designing the data mart for future applications. | [C_PLELMN2] P&L Element | |||
| 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 | Rule based on sales integration rate (console view) the integration rate is between 0 to 100% Integration rate equal to100% means the company is fully owned by Solvay. To calculate measures based on external/internal companies. The measures are multiplied by this integration rate. Fore example, to calculate the external sale, the amount of sale for a company with 50% integration rate will be multiplied by 50% to be counted for the external sale. | |||
| 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 | ||
| Measures: Actual Volume | The quantity of material sold per CPC. It has been calculated by the aggregation of quantities in sales orders transactions per CPC | In COPA03, both external and internal sales are available. In some applications both are important but for Leo only external sales is important. | COPA03 | Measures\N8110 - Qty Sold External Measures\N8110 - Qty Sold Internal | |
| Measures: Actual Sale | The amount of sale per CPC in local currency. It has been calculated by the aggregation of sales transactions per CPC | The transparency dashboard uses the measures in COPA03 that offer the sale with LC (Local Currency). The car3 fx rate will be used for conversion to Euro | COPA03 | Measures\Net sales Internal (Sales+Royalties) - LC Measures\Net sales External (Sales+Royalties) - LC | |
| Measure: Actual Unit price | This is the average price of a CPC per unit of measurement( s. This is calculated in the transparency dashboard: Actual unit price = Actual sale/Actual volume | ||||
| Last price | Last month available for CPC with external data, the qty assigned to this month. | Only for external sales and closed months. Filter on the creation date of the last order line in case of many order created in the same day, the last order number which is the biggest one are taken intothe consideration | |||
| Sales order number | Includes sales orders and corresponding line items |