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. 
  • Important data objects (transactional) in COPA03 used in the context of pricing:
    • Source system: specifying what source system the sales order data has been generated  
    • Sales order document: Each sales order placed in SAP associated with a document which is tracible using the sales order number
    • Line item: Each item and its details is called the line item in a sales order document. In simple terminology, the line item is nothing but the number that uniquely identifies the item in the sales document. Each sales order can include multiple line items.
    • Customer sold to: Refers to the customer that the material/product are sold to. This is the customer who is going to be invoiced. 
    • Customer ship to: Refers to the customer that the material sold are shipped to. It is not necessarily the customer that buy the material.
    • Ship to KA (all GBUs)/Final consignee (SpP): Addresses the final customer receiving the material sold. It is important to note that it is different from the ship to concept mentioned above. 
    • Shipping plant: Plant from which the material is ship to a customer
    • Manufacturing plant: Last plant in the product/material production process
    • P&L element: Allow to split the costs of a material/product sold
    • Company integration rate: A company which is a legal entity owned by Solvay to sell materials/products, can be owned fully or partially by Solvay. The company integration rate specifies the percentage of the Solvay ownership ranged between 0 to 100% of the company. A company with an integration rate of 100 is fully owned by Solvay. To provide a consolidation view, the sales measures (see below Actual Volume & Actual Sales) of a company are multiplied by the company integration rate. 
    • Incoterm: Specifies the transfer responsibility between the supplier and the customer. It has impact on the price.
  • 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 which is directly retrieved from P&L query in BW. The query offers actual sales in local currency. The CAR3 exchange rate type will be used for conversion to Euro in the data lake. A sales order is either an external sales or an internal sales (intercompany sales) which belongs to the 
    • 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 Customer Product Combination (CPC) which is calculated by the total number of the sale divided by the total volume recorded for the latest line item. The calculation is performed in the data lake (link to the logic for the calculation-> Last invoice price calculation). This measure also has been used to calculate the forecast price (Link to the calculation-> Forecast price calculation). The forecast price also is being calculated in the data lake. 


Data Flow

  • Data will be refreshed from SAP-BW into data ocean on a daily basis

  • Will extract last 12 months of transactions on a daily basis and refresh into data  ocean


                         

Tables & Attributes

 Indicate what the primary key is in a dataset, i.e. which combination of fields is guaranteed to give a unique record.