1: SAP active price derived from COPC07
2: Last SAP active price (SAP active price= Net price where the value component is 2 or 15 and quantity ≠ 0. In case both are available for a given month, the record for which the value component =2 is considered. Last SAP active price= latest month for which the SAP active price is available )
3: Avg (last SAP active price) of similar records excluding incoterms for the given month
4: last invoice price associated with CPC
5: last invoice price associated with CPC excluding incoterms
6: non of the rules above applicable (assign 0)
View 1: FACT_Forcast _Sale→ raw from COPC07
(snapshot from COPC07→ only latest run are presented to the view accessible by the users)
What combination of fields is unique
| CPC key | GBU key | Loading source ID | Loading source name | Latest running month | Forecast month ID | Forecast month name | Material key | Customer sold to key | Customer ship to key | Incoterms key | Manufacturing plant ID | Manufacturing plant name (short, mid, or long) | Shipping plant name | Shipping plant ID | Shipping plant name | Value component ID | Value component name | Local Currency ID | Local currency name | Unit of measure ID | Unit of measure name | Forecast Net price EURO/KG | Forecast Sales EURO | Forecast quantity KG |
View 2:
FACT_Forcast _Sale→ Enriched
Mechanism to fill the missing attributes in CPC table
Current month: Oct
Latest closed month: Aug
Latest line item: SO4/1
Day of populating CPC table from COPA03
End use value of the CPC table: C or B?
Month | CPC | Sales order/Line item | Line item creation date | End use | Amount | QTY |
Apr | CPC1 | SO5 | Apr 1, 2023 | A | 1000 | 50 |
May | CPC2 | SO3 | May 20, 2023 | B | 3000 | 50 |
Aug | CPC2 | SO3 | May 21, 2023 | B | 6000 | 0 |
| Aug | CPC2 | SO3/1 | May 30, 2023 | B | 4000 | 20 |
Aug | CPC2 | SO4/0 | May 30, 2023 | B | 300 | 10 |
Aug | CPC2 | SO4/1 | May 30, 2023 | C | 500 | 20 |
Sep | CPC2 | SO3/1 | Jun 14, 2023 | B | 2000 | 0 |
Oct | CPC2 | SO3/3 | 15 July, 2023 | B | 100 | 10 |
Oct | CPC2 | SO4/1 | 15 July, 2023 | B | 300 | 0 |
Oct | CPC2 | SO2/1 | 15 July, 2023 | B | 1500 | 300 |
Missing attributes
(based on CS price optimization requirements)
Available already in the master data tables
GBU name (using GBU_key)
Group of activity (using GBU key)
Material name (using Material_key )
Customer ship to name (using customer_key )
Customer sold to name (using customer_key )
Country (using customer_key )
Incoterms name (using customer_key)
Not available in the master data tables/Available in the historical fact table
1) GBU region → Decide to add to CPC table or fact table
In transparency:
For all GBUs except SPP, GBU region is based on a field retrieved from COPA 03. To determine GBU region, GBU and customer ship to is required . Only for TS, we need also group of activity to assign the right GBU region
For SPP the data should be sourced from icare.
2) GBU customer segment → Add to CPC
In transparency
It is retrieved from COPA03. To determine GBU customer segment, GBU and customer is required. The mapping rules are applied for cleaning purposes.
Suggestion: Source it from salesforce CORE (CORE table: BU_CUSTOMER_SEGMENTATION) or BW Cube (not query)
3) GBU product family→ Add to CPC table
In transparency
It is retrieved from COPA03 for all GBUs except SPP. To determine this, GBU and product is required. For CS and TS , SOURCE_SYSTEM_KEY is also required.
4) End use→ Add to CPC table
In transparency:
It is retrieved from COPA03
Suggestion:
For all GBUs except Spp
Using the same methodology as the last invoice price calculation to identify the most recent line item associated with a specific combination of ship-to and material in Copa03. Once this line item is identified, retrieve the [4CPCOPA03-TGB2_C_GBR4] GBR: Corporate Enduser (Ship-To)_W, end use associated with the line item.
To confirm if it is available in copa03
For SPP use sales force icare. There is a table in icare that holds end use for the combination of customer and product.
SELECT End_Use__c,Material_Group__c,Sap_Code__c as Sold_to FROM End_Use__c
5) Enterprise segment
6) Product brand name
Main question is where to add above attributes? In Fact_forecast_Sales table or CPC++ table?
1) CS price optimization scope
In the forecast fact tables, there will be missing attributes that are available either in dimension tables or the historical sales fact table (P&L). List below:
| Attributes | Short term action (Q4) | Ticket | Mid term/Long term | Dimension table in data like | Link with Historical sales fact table | Link with Forecast fact table | Availability in COPC07 | Availability in COPA03 | |
|---|---|---|---|---|---|---|---|---|---|
| Group of activity | Keep the design as it is | Unique at CPC | V_DIM_activity1 | GBU_Key | GBU_Key | Yes | Yes | ||
| Country | Keep the design as it is | V_DIM_Customer | Customer ship to/sold to _Key | Customer ship to/sold to _Key | yes | Yes | |||
| GBU region | Forecast fact table or CPC table ? Decision? The same rules as copa03 but there is no rule currently in transparency for aroma | Unique per GBU and customer | Available | Not available | no (transformation rules on Zone attribute available in the query) | no (transformation rules on Zone attribute available in the query) | |||
| GBU customer segment | To add in the CPC table: (DIM_customer_material_rel) Add as an attribute in the CPC table and source it from salesforce CORE (CORE table: BU_CUSTOMER_SEGMENTATION) or BW Cube (not query) | Unique per GBU and customer | Available | Not available | No | Yes | |||
| Product brand name | To add in the CPC table: (DIM_customer_material_rel) Using the same methodology as the last invoice price calculation to identify the most recent line item associated with a specific combination of ship-to and material in Copa03. Once this line item is identified, retrieve the product brand name ([4CPCOPA03-TGB2_C_GBR19]GBR: GBU Material Group Ship-to) associated with the line item. | Unique per combination of GBU and product/material To look for the relation with the customer | Available | Not available | No | Yes | |||
| End use | To add in the CPC table: (DIM_customer_material_rel) For all GBUs except Spp Using the same methodology as the last invoice price calculation to identify the most recent line item associated with a specific combination of ship-to and material in Copa03. Once this line item is identified, retrieve the [4CPCOPA03-TGB2_C_GBR4] GBR: Corporate Enduser (Ship-To)_W, end use associated with the line item. For SPP use sales force icare. There is a table in icare that holds end use for the combination of customer and product. SELECT End_Use__c,Material_Group__c,Sap_Code__c as Sold_to FROM End_Use__c | Unique per combination of customer ship to and material | V_DIM_gbr_enduse | Available | Not available | No | Yes (Rules) | ||
| GBU product family | To add in the CPC table: (DIM_customer_material_rel) | Unique per combination of GBU and product/material. One product/material can have various product family from one GBU to another | Available | Not available | No | Yes (transformation applies) 2) Transactional data - Historical sales | |||
| Enterprise segment | To add in the CPC table: (DIM_customer_material_rel) Using the same methodology as the last invoice price calculation to identify the most recent line item associated with a specific combination of ship-to and material in Copa03. Once this line item is identified, retrieve the Enterprise segment ([C_GBR21__C_ENTSEG]) associated with the line item. | Available | Not available | No | Yes | ||||
| Market | One market has multiple end use | V_DIM_gbr_enduse | End_use_key | Not available | |||||
| Ship to KA/Final consignee | Available | Not available |
CPC
GBU+CPC (Material, ship to, incoterm)+ last invoice price