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.
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 from BW to data lake. Each refresh triggers the recalculation of the corresponding measurers for the current and 2 last months. Once a week, there is a full reload of at least past 3 years.
Once a week > full reload of at least past 3 years
- Measures available in the data lake that are important in the context of pricing:
- Actual Volume: This is the forecast sale quantity. It is directly retrieved from Forecast query.
- Actual Sale: This is the unit price forecast. (Calculation: Forecast price calculation)
- Actual Unit price: This is the forecast sale calculated by multiplying the forecast volume with forecast price. (Important Note: there is a measure in BW represents forecast sales but it has NOT been used in the context of pricing)
- Last invoice price: 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