Data Object Definition
The Settled Orders dataset contains all Sales Orders which have been shipped & invoiced to customers. It does not contain the full orderbook - for example future orders or cancelled orders are not included, only orders which have been executed and have had a financial impact on the P&L. It is sourced from SAP COPA through BW query COPA03. It does contain the P&L at orderline level, i.e. for each orderline it shows cost & margin information which is not available in the regular Sales Order dataset.
In the context of Pricing and the Transparency Dashboard, this dataset is also known as the Sales Query or the P&L Query.
Deployment Status
This data is currently being worked on and is planned to be available by end of Q3 2023.
Data Model
Additional Info & Comments
Settled order in the data lake
- Data lake source: In the pricing data lake, Settled Orders dataset are sourced mainly from COPA03. Only for composite material until jun2021, there is a file (from Cognos) used as source. The query will be refreshed on daily basis from BW to data lake. Each refresh triggers the recalculation of the corresponding measurers. Once a week, there is a full reload of at least past 3 years.
- Measures available in the data lake that are important in the context of pricing:
- Actual Volume: The quantity of material sold. It is directly retrieved from P&L query in BW.
- Actual Sale: The total amount of sale in local currency (the car3 FX rate will be used for conversion to Euro)
- Actual Unit price: This is the average price per unit of measurement→ Actual unit price = Actual sale/Actual volume
- Last invoice price: The unit price of the latest line item per CPC which is calculated by the total number of the sale divided by the total volume recorded for the latest line item. (link to the calculation-> Last invoice price calculation). This measure also used to calculate the forecast price (Link to the calculation-> Forecast price calculation).