The application provides for a given month, the average sale prices observed during the X previous months.
Data is both:
- Available in reporting through AFO workbook
- Monthly extracted into 2 flat files likely to be loaded into WP1 and PF1.
The aim of the application is to calculate and provide to transfer pricing team the monthly average sale price by BFC Company Code, Material, Revenue Type.
This historical sales price will used for internal control in internal flow.
For this Application the access is provided based on the BW menu "TP - Transfer Price Historical Sales" and it's done via Service one.
Fill the form
https://solvay-dwp.onbmc.com/dwp/app/#/itemprofile/622
In SAP system select the " WBP - BW Business Warehouse"
In WBP - BW Business Warehouse : Select the Business Role select the " TP - Transfer Price Historical Sales"
VERSION | DATE | MODIFIED BY | DESCRIPTION |
0.01 | 16.11.2023 | Inês Vilares | Initial draft |
| Data Product Type |
|
| Technologies |
|
Data Sources Note: list of all applications and various environment |
|
2.0 Business Process
This project was developed in September 2019 by Ludovic Depoix with BW technical support of Cyril Bianco and a new OFFSET parameters has been added to the data flow in June 2022 by Ludovic Depoix with BW technical support of Charles Benier.
To construct Internal Margin check flow in ECC (PF1 and WP1) the process need to have Historical Sales Price calculated each month at material / company code // Revenue Type level.
This historical sales price will used for internal control in internal flow.
With this it was built a solution in BW to have the necessary information to feed the systems (PF1 and WP1) to the HUSP table and to finalize the process in the TRSA table for the calculations for the Internal Margin flow.
3.0 Application Feature Overview
For this application find below all the workbooks available:
| Reports | Definition | Prompts | BW Workbook Query | Query Technical Name |
|---|---|---|---|---|
| Controlling - Sales Price - Detail | This report will show the sales prices to be checked by the users and to be used in SAP to calculate the right internal price. We have a lot of details regarding the documents associated to the sales. | Mandatory:
Optional:
| BW_WBK_COTP_0002 | BW_QRY_CPCOTP01_0004 |
| Controlling - Sales Price - Synthesis | This report it's the same information from "Controlling - Sales Price - Detail" but on a high level perspective. | Mandatory:
Optional:
| BW_WBK_COTP_0001 | BW_QRY_CPCOTP01_0003 |
4.0 Functional Specification
4.1 General Data/Calculations
| COPA Document | Refers to a document related to the Controlling module, specifically to the Profitability Analysis (COPA stands for CO-PA, where CO stands for Controlling). Profitability Analysis (CO-PA) in SAP is a sub-module of SAP Controlling that provides detailed information about the profitability of an organization's segments, such as products, customers, or business areas. COPA documents capture data related to sales, costs, and profitability. A COPA document contains information on revenues, costs, and other key financial metrics associated with specific segments. It helps businesses analyze and understand the profitability of different aspects of their operations. The data in COPA documents can be used for reporting and analysis purposes, enabling organizations to make informed decisions about their business strategies. The exact structure and content of COPA documents can vary based on the specific configuration and customization implemented by each organization in their SAP system. |
|---|---|
| GBU Assignment | The concept for GBU doesn't exist in SAP but it’s a requirement to have in BW since in BFC we have this definition. This GBU assignment is different for WP1 and PF1 and it's explained in detail in the Process Definition section in Profit and Loss functional documentation. |
4.2 Process Detail
4.2.1. Report/Process Definition
| Domain | Finance |
|---|---|
| Application | BW reports under TP - Transfer Price Historical Sales |
| Provider | CPCOTP01 |
SAP BW High Level View
Main Flow
This is a general view of the workflow to have the data available in the source systems.
In the next sections we have more details in the different steps based on the specification for this report. If we need to check the document we can see it in here Internal Margin : Historical sales price calculation.
Filters
Only consider :
- Only PF1 and WP1 data
- All what is posted to the R10000 BFC heading + BFC heading N8110 (for quantities)
- Only items with Material code and Distribution channel.
- All COPA of all record types (F, C, Z,...)
- Exclude Record type X for PF1 datas
Exclude:
PAX GBU (GBU : PV , PX , PM ).
Material type (C_MATNR2_0MATL_TYPE) different from :
- Z710 No-name article-service (WP1),
- ZDIE (WP1),
- ZNLA (WP1),
- ZUNB (WP1),
- Z710 (PF1),
- ZR09 (PF1),
- Z709 (PF1).
Month of calculation and month of selection
Each month M we calculate the historic sales price using M-1 to M-3 datas.
For example Historical Sales price of August 2019 is base and sales from May 2019 to July 2019. We have more information about this number month offset explaining in the technical part.
Dynamic View versus Historical Data
After meeting with Project, there is no need to save the data before load it in PF1/WP1 . Due to the fact data will be manually change in ERP ; traçability will be complex.
Only dynamic view is necessary . So when the user will launch the query only current business rules will be applied even if the user launch an previous month.
It will simplify developments especially line item query.
Revenue Type
Revenue type Characteristic
Revenue type should take 8 values + 1 Error value
- - Domestic Third Party sales (no distinction or included customs duties) => DoTP
- - Domestic Third Party sales duty free => DoTP_DF
- - Domestic Related Party sales (no distinction or included customs duties) => DoRP
- - Domestic Related Party sales duty free => DoRP_DF
- - Abroad-Export Third Party sales => AbExTP
- - Abroad-Export Related Party sales => AbEXRP
- - Abroad-EUdel Third Party sales => AbEUTP
- - Abroad-EUdel Related Party sales => AbEURP
This new object will 3 navigational Attributes
1- Partner Type
With values
a. RP “Related Party”
b. TP “Third Party”
2- Geographical flow
With values
a. ABEX “Abroad-export”
b. ABEU “Abroad-EUdel”
c. DOMS “Domestic”
3- Chinese Duty
With values
a. NC “Not Chinese Company”
b. DF “Duty Free”
c. ID “Included custom duties”
Revenue type determination
The differing type of revenue will be determined according to 3 steps:
- Partner type determination;
- Geographical flow;
- Chinese Custom.
1- Partner type determination
We must determine if the line item is linked to a
- Related Party
- Or Third Party
We must use the trading partner (C_PCOMPAN) of the line item and and the LE exception table.
1) If there is no trading partner OR the trading partner have a conso method ( C_PCOMPAN_C_CSMETH) = 90 => Third party
2) trading partner (C_PCOMPAN) not empty and consolidation method ( C_PCOMPAN_C_CSMETH) different from 90 sold but the Trading partner is founded in exception table (Z1F_TP-TPLE to be created) => Third party
3 -trading partner (C_PCOMPAN) not empty and consolidation method different from 90 sold and BFC Company of the Trading partner (C_COMPANY__0COMPANY) not founded in exception table (Z1F_TP-TPLE to be created) => Related Party
Remark : this way to split internal and external is completely different from the way it’s done in BW P&L application and Working Capital.
In ZFI_T_TP_TPLE , if a company code is maintained in here we will not consider for the calculation.
2- Geographical flow
The second distinction Domestic sales / abroad sale.
We will compare:
- the recipient country (ship to customer country) C_SHIPID_0COUNTRY.
- And the country of the selling company (company code country):C_PCOMPRS__0COUNTRY (= C_COMPCDE__0COUNTRY).
- Abroad-export :both countries are different and one of them at least is outside E.U.
- Abroad-EU : both countries are different and inside E.U.
- Domestic (same country).
3- Chinese Custom
The third distinction is only for chinese companies.
If the company code is not a chinese company ( C_PCOMPRS__0COUNTRY or C_COMPCDE__0COUNTRY <> CN) = > domestic included duties.
If the company code (C_COMPRS) is a chinese company ; to split the domestic line by "included customs duties" or "duty free", based on:
- the invoicing currency (0DOC_CURRCY) <> Local company currency(C_COMPRS_0CURRENCY ) => domestic duty free
- the invoicing currency = local company currency (CNY), the legal entity is SSP CHANGSHU (5991) and the customer is related party => domestic duty free
- other cases => domestic included duties
Remark : For the future we can have more than one exception than SSP CHANGSHU (5991). We must have a variable.
Additionally for the Transfer price EU definition:
A EU country is a country managed in T005 table with EU country X
+ Monaco country (for fiscal reason)
Key Figure
We must provide:
- Sales Qty(VVA01 );
- Sales Qty(VVA01 ) in UOM.
The quantities must be converted in the unit Base Unit of Measure of C_MATNR2.
We can use a unit conversion rule type RSUOM ; for example UOM_MATNR2 conversion type, c onvert firstly with table T0006 and after use DSO UOMCMAT2.
- Sales R10000 in local currency:
Amount and C_FIELDN__C_MGN_ACC = R10000
Remark : technically speaking for PF1 tadata the object of BFC account is C_FIELDN2__C_MGN_ACC. This object is identified with C_FIELDN__C_MGN_ACC in the multi provider.
- Historical Sales Price:
Sum of Sales R10000 in local currency on the M-1 to M-X /Sum of Sales Qty(VVA01 ) on the M-1 to M-X in UOM
- Historical Sales Price *1000 units:
Sum of Sales R10000 in local currency on the M-1 to M-X *1000 /Sum of Sales Qty(VVA01 ) on the M-1 to M-X in UOM
- Number of COPA line items:
Number of distincts 0CO_AREA/ C_PABELNR/ C_PAPOSNR used to calculate Historical Sales Price.
Remark : to calculate historical sales price we have quantities and perhaps more than 1 amount for the same 0CO_AREA/ C_PABELNR/ C_PAPOSNR. We must count this crossing 0CO_AREA/ C_PABELNR/ C_PAPOSNR only one time.
- Number of COPA line items different from “F”
Number of distincts 0CO_AREA/ C_PABELNR/ C_PAPOSNR used to calculate Historical Sales Price where 0REC_TYPE <> F.
Level of calculation of historical sales price
Revenu type will be calculated at:
- Month (calculation month);
- Company code (C_COMPCDE) / PRS Company code (C_COMPCDE _COMPPRS);
- Material Level (C_MATNR2);
- Source system;
- Revenue Type.
5.0 Non-functional Descriptions
5.1 Usability
as per standard.
5.2 Regulatory Compliance
as per standard.
5.3 Security
as per standard.
5.4 Performance
as per standard.
5.5 Reliability
as per standard.
5.6 Scalability
as per standard.
5.7 Compatibility
as per standard.
5.8 Availability
as per standard.
5.9 Refresh of the Data
The load is on a daily basis for master data around 2 a.m CET and for the controlling - TP - Sale Price Extracts it's on the third working day of each month around 5:30 a.m CET.