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 different from the ship to concept mentioned above. Ship to KA (Key account) is assigned to a combination of Ship-to code & Commercial product. By default it should be the the corporate ship to group. However, it can be changed for a specific product where the end user account is different (i.e. in case the ship to is a warehouse or a distributer of the ship to KA.   
    • 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 sales amount obtained directly from the Profit and Loss (P&L) query within the BW system. This query provides the actual sales figures in the local currency. The CAR3 exchange rate type will be utilized to convert these sales figures into Euro currency in the data lake. The sales orders are categorized into two main types:

      1. External Sales: These are sales transactions made to customers outside of the organization (external to the company).

      2. Internal Sales (Intercompany Sales) to Solvay: These are sales transactions that occur within the organization. 

      In the data lake, these two types of sales (external and internal) will be presented separately in distinct columns.

    • 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

 Delivered_order_lines_Key is the primary key.