Update frequency
Refresh twice a day from BW query to data lake. Every time the data is refreshed, the price forecast is being recalculated.
| Requirements | Descriptions | Comments | Source | Fields in the BW (Sales Forecast Revenue QV_BW_QRY_CPCOPC07_0001 |
|---|
| ) | Transformation | ODS | |||
|---|---|---|---|---|---|
| Loading source | In sales forecast query there are two loading sources where the forecast transactional data are populated including DYNASYS and ORDERBOOK | In the context of price forecast in the transparency dashboard the ORDERBOOK as the source is irrelevant | QV_BW_QRY_CPCOPC07_0001 | [C_SOURCE] Loading Source | Values include DYNASYS and ORDERBOOK Exclude loading source= ORDERBOOK from the query |
| ok | |||||
| Running period | We are interested in the closed running month which is one month before the current month. 7th of each month the running period is closed. (E.g. Today being Sep 9, the closed running period is Aug) | QV_BW_QRY_CPCOPC07_0001 | [C_RUNPER] Run Period (calmonth | Extract only the last running period |
| ok |
| Value Component |
| QV_BW_QRY_CPCOPC07_0001 |
| [C_VALCOMP] Value Component | Used only to filter Net price | ok | ||||
| SAP active price | Note: Net price in the query known as SAP active price for which the value component field in the query (column above) is equal to 2 . The unit is euro (other currency?) per kg | This field is only relevant for calculating forecast price: 4) Forecast price calculation FLOW | QV_BW_QRY_CPCOPC07_0001 | \Measures\Net price [C_VALCOMP] Value Component | \Measures\Net price filtered on [C_VALCOMP] Value Component=2 For TS, if there is no record of value component=2, the filter is on value component key=15 | |
| Forecast month |
| QV_BW_QRY_CPCOPC07_0001 |
| [CPCOPC02P] Further Characteristics\[0CALMONTH] Forecast Month | |||
| Fcst Volume (KG) |
| This is the forecast sale quantity. It is directly retrieved from Forecast query. | [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\Measures\ |
Quantity | ||
| Fcst Sales (€) | This is the forecast sale. It is calculated in the transparency dashboard. Fcst Sales (€) =Fcst Volume (KG)* Fcst |
price (€/KG) |
Important Note: there is a measure in BW ( |
Snapshot\Measures\ |
Amount) but it has NOT been used in the transparency |
The last three is used for cleaning purposes→ the rule is to extract only records with values on at least one of them
dashboard | |||||
| Fcst price (€/KG) | This is the unit price forecast which calculated in transparency dashboard | '' |
\Measures\Net price |
Note: This is called SAP active price and the measure is euro per kg in transparency dashboard
For GBU= TS, only net price for which the value component key=15
For any other GBU Including Novecare, only net prices for which the value component key=2 (see item 5)
'' is a forecast unit price reported in the forecast query monthly basis for 18 months ahead this measures is used to calculated the Fcst price.
| Useful to keep it in the table as we'll certainly use it for reporting purpose. | Link to the logic https://app.diagrams.net/#G1GuqjSLqoK1zjw2jr4DRt07uxWzGFZEK6 | ||
| CPC | For Novecare CPC is the combination of ship-to, material, and incoterm. | Note: For other GBUs like SpP the definition of CPC is different |
CPC (Ship-to+Material+incoterms) Ship-to [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\[CPCOPC02P] Further Characteristics\[C_SHIPTID] Ship-to party Material [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\[CPCOPC02P] Further Characteristics\[C_MATNR2] Material Incoterms [ |
[QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\[CPCOPC02P] Further Characteristics\[C_INCDYNR] Incoterm Dyn. Rhodia
For Novecare is the combination of ship-to, material, and incoterm
Ask which field of incoterms is for Novecare?
C_INCOTRM]. | ||||||
Ship-To | [C_SHIPTID] Ship-to party | |||||
Sold-To | [C_SOLDID] Sold-to party | |||||
Ship-To KA | Important note: It is coming from order line transaction from copa03. The ship to ka assigned to the Last order line of a CPC from COPA03. It's a little bit complicated. As we lack the information, we retrieve it from historical sales. To proceed, we go through the same as "last price logic" but instead of retrieving a price, we just extract the dimension value. Multiple fields will be certainly retrieved that way (except if already part of a master data). | |||||
Manufacturing Plant | [C_MANPLNT] Manufacturing Plant | |||||
Shipping Plant | [C_PLANT] | |||||
Incoterms | [C_INCOTRM]. | NG(04/09): Should we use IC_INCOTERM or use C_INCDYNS/NR and which field to use part of CPC | ||||
Product | [C_MATNR2] Material (key & name) | |||||
| Unit of measure |
| [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\[ |
Ask which field for Novecare?
| 0UNIT] Unit of measure |
Converting currency → monthly value is collected.
Car 3 is used for historical and forecast sales.
Using the current month and future months the forecast rate the closed month is applied. (Closed month in this case is one month before the current month).
Every day the master data in transparency dashboard is refreshed from BW . It means that every day the rule of converting using CAR 3 is applied. Currently, the last rate updated for each month will be important to keep and expose to views and no need to keep the history.
Always data will be extracted from query and then we applied the business transformation, is converted based on the rule above according to CAR3