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)
(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 |
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 |
(based on CS price optimization requirements)
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)
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
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 |
GBU+CPC (Material, ship to, incoterm)+ last invoice price