P&L application provides reports fully aligned with BFC structure (BFC Headings) down to REBIT/REBITDA able to analyze P&L starting from the BFC view (Company/Activity) and ability to drill down to Customer Material level.
A profit and loss statement (P&L) is a financial statement that summarizes the revenues, costs and expenses incurred during a specific period of time, usually a fiscal quarter or year.
These records provide information about a company's ability – or lack thereof – to generate profit by increasing revenue, reducing costs, or both.
It was done in 2 phases :
Phase 1 : COPA model of Rhodia legacy (RCS)
Phase 2 :
Integrated Margin is linked with P&L data and providers BW and BO reports with details of the calculation of the integrated costs
Owner : RtR reporting
IS RtR Reporting coordinator is Sandrine Micollet.
More than 500 users have access to P&L applications. Those users are worldwide and most of them are controllers.
P&L was done in 2015 and IM in 2016
| Role Code | Role Description | Explanation |
|---|---|---|
| ZR_RCS_CA_M432 | P&L – Upload Data - Non-ERP ZPL_FILE | Role menu for non ERP |
| ZR_RCS_CA_M12 | PL - P&L Reporting | Role menu |
| ZBI_RCS_FI_A33 | P & L – Profit and Loss - End User role | End user role |
Link to the BW Catalog of role
https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I
WP1
BFC
Non ERP
PQ1
Reporting documentation drive folder:
https://drive.google.com/open?id=0B_p_Afe8sjVlN1J3YzYzdE8taDg
https://drive.google.com/open?id=0B_p_Afe8sjVlTEVnc3NjdDV5U0U
Query documentation folder:
COPA module is the ancestor of BW
COPA is divided in “tight” perimeters (in customizing, in VV list, for certain characteristics, for BW data sources) : Operating concern
The lines of the ERP data (COPA value fields from WP1 system) must be translated in BFC account. This translation should be dynamic.
Historic data have been recalcuted with the new allocation Value Field / BFC Account
Historic views are avaible in BFC (not restated)
Value Fields
Value Fields are the lowest key figures in COPA module (For BCS sourcing)
Value Fields are the central elements of the BFC account determination
The organization determination must be as flexible and as dynamic as possible. We had to consider 2 cases:
Existing application in PQ1 with a lot of intelligence
Easier to load from PQ1 than redoing what has already been done - temporary solution as a project is on going to have a P&L with COPA in PF1 (go live scheduled on January 2017)
Loaded by flat files
3 levels:
Version 1: Total P&L
Version 2: Interco Sales
Version 3 : Third Party
Another DSO for data without version
Loaded by BFC flat files
Only for CICC companies
Loaded by BFC flat files only for companies loaded in Non ERP
Some companies are in Solvay group but don’t use RCS of Solvay ERP : companies belonging to NOVECARE or SODA GBU
Rhodia ERP WP1
Solvay BW PQ1 (PQ1 is the view of the P&L for PF1 data)
BFC (flat files)
BFC GBU
BFC Group of Activities
BFC Activity
We have 2 steps in the business layer.
From the propagation layer, we transpose data from DPCOPA01 to DBCOPA01.
In DPCOPA01, each value field is in a dedicated key figures. Objective of the transposition, we have 1 key figure and 1 field in key (called Value Field) to identify them.
After the transposition, we split data in 2 target DSO: DBCOPA02 and DBCOPA03.
As we have common rules for both DSO, we use an Info source to have those rules inside an unique transformation.
BFC Data comes from extraction sent by Marie-Yolande KUCZYNSKI
The file loaded by cancel and replace (using recordmode to delete active records) to propagation layer. For this part, we don’t respect LSA methodology.
All business rules calculated between propa DSO DPCOPA02 and Info source
For business and reporting layers, we load by Delete + Full.
From the BFC File, we also load 2 other targets :
Those targets are in business layer.
We load them by Delete + Full for current and previous months : determination in process chains to update TVARVC table
We do selective deletion using programs and loading with selections in DTP
The time reference could be changed manually. There is a written procedure for that
The reporting layer is loaded by deletion and full loading.
We needed a solution for companies not working on ERP. We have created a specific transaction : ZPL_FILE
The transaction is based on program : ZBW_PL_PC
With this transaction, user can load excel file for
The transaction available in SOLIA portal. For information, it's better to use IE and not Chrome
We have created a new authorization object (C_SRSYST) to be sure that users can only load data for companies they have access to.
We test user’s authorizations with DPAUTH01 DSO
For master data, we can display content of master data in the screen and also save it in an excel file
For master data and transactional data, user can load excel file.
We control content of the file to be sure that there is no error (missing field or incorrect value)
Once data is loaded:
PCA: Profit Center Accounting
GM: Gross-Margin
We needed a solution to get the P&L data for Solvay legacy.
Delta loading is currently not possible in Solvay side due to cumulated key figure → Current solution is to delete previous month and reload it.
We synchronize process chain between PQ1 and WBP systems:
Key figure aggregation : Overwrite in WBP and Summation in PQ1
DSO keys are identical in PQ1 and WBP
Aggregation is done during transformation between DSO and Cube
P&L Element attributes
C_TECHBA and C_TECHPBA attributes are determined by reading 0G_CWWE01 attributes
The GM flow works with expert routines:
There is a simple ABAP to determine Source system, /BIC/C_PCOMPAN and /BIC/C_COMPPRS and a more complex ABAP to transpose Source Key Figure into Z* P&L Elements:
There is a ABAP Rule to determine Sign,C_PCOMPRS and Logical System (simple rules)
Be aware that data are filtered on PQ1 side to load only profit center:
There is a start Routine used to exclude value. It was a functional need to have all value for reporting in one DSO DBCOPA15 and filtered value in DSO DBCOPA14.
LER stands for Legal Entity Reduction. It allows to identify companies that works both in WP1 and PF1 systems.
We have 3 cases for LER
1: Company is not LER and Plant is not Trading Plant
2: Company is not LER and Plant is Trading Plant
3: Company is not LER
The list of companies is managed in Global filter master data C_GLBFILT manually or by flat file:
The Trading plant is managed in C_PLANT master data through attribute C_SORT2. If C_SORT2 = 'NDIR', it means the plant is a trading plant.
In transactional data flow, we determine the LER case using function module ZDETERMINE_LER
itb_plant is the list of plant with C_PLANT__C_SORT2 = NDIR and itb_glbfilt is the list extracted from C_GLBFILT master data
In the function module, we check if the company is in itb_glbfilt for the period.
If yes, LER value = 3. If not, we check if the plant is in itb_plant. If yes, LER value= 2 else LER value = 1
Basically, LER companies are selling in WP1 products made in PF1. So to calculate integrated margin we need to have the cost unit price from PF1 system.
To do so, we use IM from PQ1 DSO (explained after in case 4)
In the DSO, the key is the material, the origin plant in PF1, the calendar month and the value field
Example of results in DBCOPA25
Note: there is very important semantic group in the DTP
CALMONTH
C_MATNR2
C_MATGR1
To calculate costs and duties costs, we use TIERS application.
The easiest and most efficient for us was to use APD.
We have created 2 APD using the same query APD_DPCOPC01_0001 and APD_DPCOPC01_0002.
We need to run APD 2 times in the same process chain, for current and previous months. It was not possible to run the same APD 2 times so we had to create 2 identical APD
Query used is BW_QRY_MVSDTR01_9999.
In the query, the month is determined by a customer exit
The customer exit is updated in the process chain
We store result of the APD in DPCOPC01.
Then DPCOPC01 is loaded into DBCOPA24.
After that we load data from DBCOPA01.
The order is important, DPCOPC01 then DBCOPA24 because we don't fill K_LOTSZE key figures when data exists in DPCOPC01 and DBCOPA01 (it would double the key figure)
For WP1 flow, we have 2 sub-flows, 1 for CDSA and 1 for IECRA. And for each sub-flow, we split in 3 cases. So at the end, from 2 source DSO we have 6 target DSO.
To have a BW model as simple as possible to maintain, we have decided to use an Info source between sources and targets:
From source DSO to Info source we only have the LER determination rule to apply (function module ZDETERMINE_LER). In term of maintenance, it's easier because if we need to modify the rule, we only have the change the function module and not the transformation.
From Info source to target DSO, we have a start routine calling program Z_LER_RULES
The first row with LER flag is using the function module ZDETERMINE_LER and we keep useful data.
The following rows are to be sure that we keep only useful value fieds. This filter is also set in the DTP.
After the program, as we generated new value fields, we delete the original ones.
To have more details about Z_LER_RULES, there is a dedicated document about it:
https://drive.google.com/open?id=1-54NzGz63dagA-UyyIIoNgK8fwmzo-e-YAnASxInwjo
We have a special case because we may have documents without quantity and transaction types B or Z
In this case we determine VVD0C value field from VVD00, VVE0C from VVE00 and VVF0C from VVF00.
In the stardard case, we pick data from ODS_PCP4
And we calculate new value fields
We also convert quantitites and units.
If no records are found in ODS_PCP4, we determine value fields from source DSO
There is a start Routine used to exclude value. It was a functional need to have all value for reporting in one DSO DBCOPA15 and filtered value in DSO DBCOPA14.
For PF1 system, we only use the GM flow.
Expert routine is the same except for the selection of key figures (C_KEYFIGR info object)
In IM, we keep key figures with the flag as X and ZN8110BQTY P&L element. In the normal flow, we exclude key figures with flag as X
For Non ERP we load data from propagation layer but we only keep some value fields we modify
No dependencies
List on info providers inside the technical cockpit.
WP1 and BFC are loaded daily at 1h30 am (french time)
Non ERP is loaded 3 times a day
PQ1 loading: on demand from PQ1 system
WP1: 1h40 for P&L and IM
BFC: 10 min
Non ERP: 5 min
PQ1: 10min for GM and 1h for PCA
We keep all records.
All the reporting is available throught workbooks in the role "PL - P&L Reporting" ZR_RCS_CA_M12
Query documentation:
There are several queries but the main ones are:
BW_QRY_MVCOPA01_0001 BW P&L Query
BW_QRY_MVCOPA01_0002 BW P&L - Monthly Query
BW_QRY_MVCOPA01_0003 BW P&L Reconciliation Query
BW_QRY_MVCOPA01_0005 BW Integrated Contribution Margin Query
All queries use structures for key figures. There are many key figures, using calculated and restricted key figures inside.
Jump query available
No broadcast
No known bug
P&L : Non ERP transaction
https://drive.google.com/open?id=1gF426ok7VvnF1-idij_FCsMt7HMYQuY4LiN34CseSZs
P&L : Time reference for CICC and GM flat files
https://drive.google.com/open?id=1cqPl_gkWdFubpTRyxbSaJtsa4iv8m4uH-bRT8dGOyoY
IM: No Delta - Delete and full for current and previous months.
For manual full loading, DBCOPA20 and DBCOPA23 must be loaded month by month
Program for LER determination
https://drive.google.com/open?id=1pEJTKs37dEloE7HGiY9lVsPf0crLoxxbzVN9oN33cr8
Program for LER info providers
https://drive.google.com/open?id=1-54NzGz63dagA-UyyIIoNgK8fwmzo-e-YAnASxInwjo
PQ1 data flow will be removed and replaced by PF1 system (project SPS)
