View 1: FACT_Forcast _Sale
(snapshot from COPC07→ only latest run are presented to the view accessible by the users)
| 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
Missing attributes
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
GBU region → Add to view one
GBU customer segment
Product brand name
End use
GBU product family
Enterprise segment
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