Data Object Definition

The Sales History 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. 
  • Important data objects (transactional) in COPA03 used in the context of pricing:
    • Source system: Indicating the origin or source system from which 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:  the line item is the number that uniquely identifies the item in the sales document. Each sales order can include multiple line items. Each line item has attributes such as creation date and measures such as quantity and volume. 
    • 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. (In COPA03, P&L element is associated with a line item. There is a mapping file that should be used to categorize P&L element retrieved from COPA03 in the data lake → not being applied in the data lake yet)
    • 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. Incoterms
  • 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: This metric represents the unit price of the most recent line item per Customer Product Combination (CPC). It is calculated by dividing the total sale amount by the total volume recorded for the latest line item in COPA03. This calculation is performed within the data lake, and you can find the logic for this calculation here Last invoice price calculation. It's worth noting that this measure is also used in the calculation of the forecast price, which is also performed within the data lake (link to the calculation Forecast price calculation). 


Data Flow

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

  • Will extract latest 2 months of transactions on a daily basis except Sunday and last 12 months of data on Sunday 

  • Stage table will be partitioned on meta_stg_Insert_date  
  • ODS table will be partitioned on meta_ods_Insert_date 
  • Fact table will be partitioned on calendar_month_id
  • Process will update below master tables incase the surrogate keys are not available
    • DIM_customer
    • DIM_material
    • DIM_product
    • DIM_incoterms
  • Will refresh DIM_cpc_master table with the latest key attributes associated to each CPC (Customer-Product combo) 


                         

Refer below data flow diagram for low level data flow design:   

Tables & Attributes