The new wiki link for this data flow is here:
BW RTR - Profit and Loss (P&L) by BU (Soda Ash)
Please update the doc there and no longer here.
The aim of this application is to give a P&L reporting adapted to Soda Ash GBU requirements.
It is based on the corporated P&L query and offered special axis and calculation for Soda Ash.
Specifications: https://drive.google.com/open?id=1Q-QjleNGzLF1tbhALtbjN0hdp9x9Y4zBrR2gURi3yw4
Owner : RtR reporting
IS RtR Reporting coordinator is Charlotte Rollier.
The project was done in 2018.
List of application role + menu role and explanation if we have several applications role with specials rules.
| Role Code | Role Description | Explanation |
|---|---|---|
| ZR_RCS_CA_M66 | PL - P&L by BU (Soda Ash) | Role menu for all PL - P&L by BU (Soda Ash) queries |
ZR_RCS_CA_M661 | PL - Upload & Table Maint. (Soda Ash) | Give access to transaction ZPL_SD_RULES and ZPL_SD_TRFPRC. Not everybody with P&L by BU (Soda Ash) application has this role. |
| ZR_RCS_CA_M662 | PL - Update Customer / Company Integration Rates | This role is separated from other roles as it could concern other population than Soda Ash in the future. |
| ZBI_RCS_FI_A37 | Application role P & L – Profit and Loss by BU (Soda Ash) : | Application Role:
|
| On going: P&L by BU (Soda Ash) is a separate application from P&L application however, a user with P&L by BU must also have access to the P&L application (some Workbooks include P&L queries), that is why a composite role is created in order to give the P&L infoproviders access along with P&L by BU infoproviders. |
Link to the BW Catalog of role
https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I
Dataflow overview
https://drive.google.com/open?id=1KvtTRYpRfKjRJNCx0sxGMGQVSe04ARE9V0OnVvd70uI
Reporting documentation drive folder:
https://drive.google.com/drive/folders/1D0RpGQ3rTMDh_7S0AvhWBRkcT-2buhfZ
RtR reporting knowledge transfert documentation:
https://drive.google.com/open?id=1zwz7IlL6c9Eiurm325Whcl99J1OJ517mo22pIdqYRtw
Due to existing P&L flows complexity, to enhance all P&L flows with axis and calculations required by Soda Ash would have been more risky, more expensive and would have badly impacted corporate P&L flows complexity and performance.
Moreover, the reporting requirements could not be achieved directly in a BeX query on the multiprovider MVCOPA01 or very difficult to maintain and with uncertain performances.
The flow could have been based directly on the multiprovider MVCOPA01, however, tests reveal it was a lot slower than to retrieve the data from a query. Moreover, it permits to retrieve some shared restricted key figures and to manage the quantity translation (material dependent) in the BeX query.
The global filters of the extraction are similar to the P&L query (BW_QRY_MVCOPA01_0002).
Except that the P&L by BU is not all GBU SD , but GBU SD for activities only
25
51
82
Also, we need only BFC lines until Contribution margin, so only necessary BFC accounts are extracted.
Quantities are needed in VKG unit for calculations done inside the flow. The translation is managed in the source query. Nevertheless, if the quantity translation could have not been done, then the quantity is considered as VKG in the transformation (calculations are done in scenarii where there are few risks this case happen).
The Master Data contains the percentage of integration by company. It is updated from PRS for actual companies but contains also fictive companies, created for BW calculations only (one by possible integration rate : ZJV100 => 100%, ZJV075 => 75%, ...)
The update from PRS is done along with c_company.
There are 2 rules groups in the transformation in order to generate two lines from one PRS company code: a line with key = the BFC company code and another key = Enterprise code:
In the transformation to the cube, the technical partner company is determined using the table ZBW_CUSTCOM_INT, if no entry it is equal to the trading partner.
This technical trading partner is then used for internal / external calculations in the queries.
The use of this technical master data simplifies internal / external calculations as only one Master Data is read in the formula in comparison to the P&L queries where c_company and c_custcom are read. It improves the understanding of the formulas and the queries performances.
It can be #, internal or external.
The type of flow is used for internal / external calculation in Bex formulas: an external key figures has necessarly C_FLOWTSD = # or E, and an internal key figures has necessarly C_FLOWTSD = # or I.
It is used in order to be able to split a key figures internal and external part under different axis. For instance, for some scenarii, the internal part of the sales costs will be in BU Europe and the external part in the BU Seaborne.
It can be #, internal or external (however, all amounts assigned to an identified Soda Ash scope should be either internal or external).
This type of flow is used for display only and not for the calculation of the internal / external part, which means that external part of a key figure could be displayed as internal in the report (for instance, for the scope E1, the internal sales are displayed as external).
Step 1:
The determination of Soda Ash axis is done using the rules in the table ZBW_PL_SD_RULES.
It consists of interval of values for several criteria : landscape, activity, partner activity, partner GBU, BFC management area, company, product hierarchy, division and GBU zone.
Reading the table ZBW_PL_SD_RULES the tranformation will obtain a scope ID for which corresponds a value of each Soda ash axis: Sourcing (origin), BU Soda Ash (can be Europe, NAFTA or Seaborne), Type of Flow, Type of flow (display).
Step 2:
Then reassignments and new lines are created, each case is identified by a specific SD value type:
When new lines are created, the flag c_nwline is filled, so, to restrict key figures on c_nwline = # enable to retrieve the original P&L lines. Note that when a line is split in flow type I (Internal) and E (external), it is not considered as a new line because flow type I must be used in queries only for the internal part and flow type E only for the external part, so, in the reporting, the sum of the 2 lines is equivalent to one line with flow type #.
The table is maintained by Reporting Plateform using the program ZBW_PL_BY_BU? (TODO: to confirm).
The file to import is maintained here: https://drive.google.com/open?id=1xpkDgqQ4uO5TERMCTrdXClBIEmhH1sgJ
In order to simply the maintenance of this file, there is only one line by scopes. All the criteria are defined as intervals of values, if a criteria is not relevant for a scope, an interval containing all values must be put in the cell and not an empty cell.
The program ZBW_PL_BY_BU will create a line for each interval combinations and a column "From" and "To" for each criteria and put it into the table ZBW_PL_SD_RULES with information of the user who executed the program and the timestamp of the update.
All the table content is deleted before the update.
The program checks there is not duplicated entries in the file.
The "Description" column of the file is used to update the text of the MD c_scopesd. To avoid any problem, only this file should be used for updating the text of the MD c_scopesd and not a manual update in the MD.
DO NOT USE SM30 in order to update the table ZBW_PL_SD_RULES, the manual change would be replaced at the next update using the program ZBW_PL_BY_BU if the file was not synchronised.
The table contains the transfer prices for 1 TON for NAFTA and Europe, on a validity period.
The table is maintained in SM30 through the transaction ZPL_SD_TRFPRC by the Reporting Plateform (TODO: to confirm).
The table contains the technical trading partner(C_PCOMTEC) to be used for internal / external calculations (currently, there is no trading partner in P&L for the concerned perimeter) for a given customer / company code.
There is a check on existing company code ship-to and technical trading partner values in the master datas. The percentage of calculation associated to the technical trading partner is automatically filled using c_comptec master data and stored for information purpose only (calculations in query will use the rate in c_comptec table).
The table is maintained in SM30 through the transaction ZPL_CUSTCOM_INT by Reporting Plateform? (TODO: to confirm).
Because some navigational attributes are required for the Business Rules implemented in the flow or in order to keep the cube as small as possible for performance purposes, the following navigational attributes are stored in the cube and in consequence, the cube need to be reloaded regularly in case of reorganisation (currently, full perimeter is reloaded at each load):
Data comes from P&L application.
Process Chain | Code | Type | Frequency | Comments |
|---|---|---|---|---|
| COPA: PL - PL by BU - complete | PC_COPA_PL_44 | MAIN |
|
|
| COPA: PL - PL by BU - 2 months | PC_COPA_PL_43 | MAIN | Not used | Before to tests the full loading in WBP, it was planned to execute daily a 2 months load PC and a full reload only on Saturday. However, as we observed the full reload PC lasts less than 5minutes, we decided to use it as a daily PC. That is the reason why PC_COPA_PL_43 is finally not used. |
Loaded after P&L PC PC_COPA_PL_GLOBAL and PC_COPA_PL_22.
Full reload DTP = 1minute (around 170000 records)
| Key Figure | Estimation |
|---|---|
| ~ Average Process Chain Runtime | 3min |
| ~ Average nb of rows loaded per load | 170000 |
| ~ Total nb of rows loaded (if full) | 170000 |
| ~ Average Runtime for 10k lines |
Keep only 2 years
Query end user documentation should be created in the public "Customer Support Wiki" space under the corresponding BW application page : BW - Application. Technical query query documentation, if necessary should be added as a sub-page of this documentation using the BW Technical Query Documentation template.
List the most important and complex queries only with a link to the documentation
Give detail on all complex functionalities: list most important and/or complex KPI, query jump, alerts
Indicate if there are broadcasts and give some details on the broadcast settings.
Give the list and explanation on the known, not-solved, bugs.
List recurring procedures
Detail planned major evolution if already known. Example: complete decommissioning of application is planned in 2017 / Extension to solvay perimeter planned in 2nd semester of 2016