General presentation

Objective of the application

P&L

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 :

• Solvay legacy (PQ1)
• BFC Data
• CICC
• Below Gross Margin
• Non ERP companies

Then, with SPS project, Solvay legacy PQ1 solution was replaced by PF1 flows.

IM

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 Charlotte Rollier.

Usage information

More than 500 users have access to P&L applications. Those users are worldwide and most of them are controllers.

History

P&L was done in 2015 and IM in 2016

Roles & Access

Roles and access

Authorisation objects

Link to the BW Catalog of role

https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I

Dataflow presentation

Overview

P&L

WP1

BFC

Non ERP

PQ1

PQ1 flows are kept only for historical data, they are replaced by PF1 flows since 01.2018.

PF1:

IM

WP1

PQ1:

PQ1 flows are kept only for historical data, they are replaced by PF1 flows since 01.2018.

PF1:

NON ERP:

Reporting documentation drive folder:

https://drive.google.com/drive/folders/0B0vENc8kM25OfmJzOTNoeFBpdm1vVGhHLUlaX1E0UElkSEJNaGdsRkVGUy1YUGpUdUdkOUU

https://drive.google.com/open?id=0B_p_Afe8sjVlN1J3YzYzdE8taDg

https://drive.google.com/open?id=0B_p_Afe8sjVlTEVnc3NjdDV5U0U

https://drive.google.com/drive/folders/0B0vENc8kM25OfjZybWxDdnlHQ0VCNnlHTFZYNHVycmpZZU1Ia2dNUlJtWkZHbmx5SG9nRTA

Query documentation folder:

https://drive.google.com/a/solvay.com/folderview?id=0B-qbTutzOfcHNjRBa2p0UjNZZ1E&usp=sharing_eid&ts=57514e3b

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Functional rules

COPA flows

• 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.

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

• P&L analysis is based on BFC Account with a detail by Value Field. For now, it is not useful to have an analysis directly by value field
  
  

Rhodia legacy

• Historic data have been recalcuted with the new allocation Value Field / BFC Account

• Historic views are avaible in BFC (not restated)

The organization determination must be as flexible and as dynamic as possible. We had to consider 2 cases:

• CASE A : COPA line items with Material and Distribution Channel valid
• CASE B : COPA line items with Material empty  and/or Distribution Channel  empty

Solvay legacy data (PQ1 based solution)

• Existing application in PQ1 with a lot of intelligence

• Easier to load from PQ1 than redoing what has already been done - it was a temporary solution and it is replaced with SPS project (P&L with COPA in PF1, go live was on January 2018)

BFC Data

• 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

CICC

• Loaded by BFC flat files

• Only for CICC companies

Below Gross Margin

• Loaded by BFC flat files only for companies loaded in Non ERP

Non ERP companies

• Some companies are in Solvay group but don’t use RCS of Solvay ERP : companies belonging to NOVECARE or SODA GBU

Systems involved

• Rhodia ERP WP1

• Solvay BW PQ1 (PQ1 is the view of the P&L for PF1 data)

• BFC (flat files)

• Flat files for Non ERP

Organisation

• BFC GBU

• BFC Group of Activities

• BFC Activity

Technical rules

P&L WP1

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.

Transformations on DBCOPA02 → DBCOPA02 and DBCOPA03 → DBCOPA03 are used to determine the flag intra gbu (C_INTGBUF). It compares the GBU ( 0g_cwwe01__cpfctr1_2 for DBCOPA02 and c_tecmat for DBCOPA03) and the partner GBU ( c_tecpara__cpfctr1_2). It was not possible to use a technical Master Data to fill this requirement as the needed key (the keys that would permit to determine the combination GBU and partner GBU) would have been too long. These loadings are done only once a week.

P&L PF1

We have 2 steps in the business layer.

From the propagation layer, we transpose data from DPCOPA01 to DBCOPA01.

In DPCOPA15, 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 use a second level of Business layer for other business rules:

• we apply the sign of the key figures according to DSO DPCOPA14
• we calculate document currency amounts for the key figures maintained under c_glbfilt (stream = “C_FIELDN”, rule = “AMOUNTDC”).

BFC

BFC Data comes from extraction sent by Marie-Yolande KUCZYNSKI

• Every day between 3rd and 20th day + 25th day of the month at 3:30AM (FR)
• In TXT format
• In /BW/exploit/PandL

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 

• Easier for maintenance
• Very simple business rules
• Only exclusion of data between info source and business layer

For business and reporting layers, we load by Delete + Full.

From the BFC File, we also load 2 other targets :

• CICC system
• Companies not working on ERP : only a part of P&L

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.

Non-ERP

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

• Customer master data
• Material master data
• Non-ERP transactional data

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:

• Save file in csv in /BW/exploit/BW/PandL
• Execute process chain
  • By event
  • Load file to PSA
  • Manage “delta queue” is several loadings at the same time
• Another process chains to load from PSA to BW Info provider
  • 1 for Master data
  • 1 for DSO and cubes
  • Process chain runs 3 times a day (managed in Global filter master data)

Profit Center check:

As per the business requirement, program do lot of validations before loading the data. As part of this, For few source systems, there should not be profit center check. Where as for few other source systems, there must be a check for profit center (it should check against the profit center master data). The new source systems can be added into that list.

To have this functionality, program is modified and introduced global filter.

NERP system with Y → No profit center

NERP system with N → Profit center is mandatory & it checks the profit center against master data

In future, based on the business decision, by controlling the active parameter to Y/N for each system, we can handle whether profit center check is required or not. 

This change is handled as part of UC#4000005270

Full flat file check:

Using c_glbfilt parameter (steam = "FULLFF", rule = "NERP"), we control the list of source systems for which a full flat file is required:

When a full flat file is expected, that means below gross margin part should not comes from BFC but from the file uploaded using ZPL_FILE. The key figures only in full flat file are maintained in the structure ZNERP_FF_KF and a warning is raised if none of those key figures is filled. It is forbidden to fill one of those key figures if the source system is not in the c_glbfilt list.

Full flat file information is kept under the propagation DSO (DPCOPA03) using infoObject c_fullff = "X".

PQ1

PQ1 flows are kept only for historical data, they are replaced by PF1 flows since 01.2018!

Glossary

PCA: Profit Center Accounting
GM: Gross-Margin

Presentation

We needed a solution to get the P&L data for Solvay legacy.

• part of data are in GM Flow at detailed level loaded from a ZZ program
• rest of data are in PCA flow at aggregated level
• data are merged at multiprovider level, excluding duplicate data at business layer level

Specific rules

We synchronize process chain between PQ1 and WBP systems:

• WBP PCA PC_COPA_PL_17 is executed from PQ1 ZZKPCA_PL_TR and ZZKPCA_PL_TR_2
• WBP GM PC_COPA_PL_23 is executed from PQ1 ZZCONEXIA_04
• WBP C_TECHSEM PC_COPA_PL_14 and TECHBA / TECHPBA are executed from PQ1 ZZF_MD_BFC_PL

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

• Determined from ZFC-PL hierarchy for C_PLGRP (P&L Group)
  • P&L Group is the father node of P&L Element
  • When P&L Element is out of hierarchy (no P&L Group), the default P&L Group is ZZ_99999 (these values need to be updated by business in hierarchy)
• From dpcopa13 DSO for c_mgn_acc (BFC Account) and sign

C_TECHBA and C_TECHPBA attributes are determined by reading 0G_CWWE01 attributes

GM Flow

Classic delta loading is currently not possible in Solvay side due to cumulated key figure:

after delta infopackage ran, the list of companies in the PSA is updated in TVARVC parameter PL_TVARVC_COMPANY_GM by the program Z_UPDATE_COMP_TVARVC_GM in Process chains. Moreover, the calmonth in the PSA is read in a decision form in the Process Chain and updated in TVARVC parameter PL_TVARVC_CALMONTH_GM => the Process chain allow only to load one month, if there is several month in the PSA, the loads are aborted.

In order to be sure to have all data concerning the companies/month in the delta, a full infopackage is then executed using the companies in parameter PL_TVARVC_COMPANY_GM and the calmonth in PL_TVARVC_CALMONTH_GM.

Before, to load each target in this flow, a selective deletion is done on the companies in the parameter PL_TVARVC_COMPANY_GM and the month in PL_TVARVC_CALMONTH_GM.

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:

• Read table c_keyfigr and loop on each value
• Uses field symbol to assign Key Figure source field and Unit source field
• Depending on c_ratioim (Ratio Type) value, we have 3 different cases:
  • X means that key figure is assigned to 0AMOUNT and will be used in main P&L query
  • Q means that key figure is assigne to invoice quantity (G_QVVA01)
  • Empty value means that corresponding key figure will be loaded in K_AMNTDC (Amount in DC)

PCA Flow : DPCOPA11 -> DBCOPA15

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:

• S*
• D*
• SCHEF* (redundant)
• Note that a security filter has been added to DTP on WBP side

PCA Flow : DBCOPA15 -> DBCOPA14

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.

Integrated Margin (IM)

LER Determination

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

IM Unit cost determination

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 

Transport Costs & Duties

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)

Case 1: WP1 - No LER No Trading Plant

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

Case 2: WP1 - No LER Trading Plant

We determine value fields from source DSO

Case 3: WP1 - LER

This case works only with quantity value field

We get the value field VVD0C from DBCOPA25 (IM Cost Unit) to calculate new value field

It's the same logic for value fields VVE0C and VVE2C.

At the beginning of the project, we determined also VVC3C but it was dropped later.
The code is in comments so it's easy to add it. 

Dependencies with other applications

No dependencies

Data loadings

Info providers and objects loaded

List on info providers inside the technical cockpit.

Main process chains are:

PC_COPA_PL_GLOBAL : Load WP1 P&L, BFC and WP1 IM

PC_COPA_PL_09 : Load Non ERP Master data and Transactional data

Loading frequency

WP1 and PF1(except for PF1 IM) are loaded daily at 1h30 am (french time) and also on twice during the afternoon (1pm and 7pm between working day 2 and 4).

PF1 IM are loaded once by day from D+1 to D+3 at ?ToUpdate?

BFC is loaded daily at 1h30 am (french time)

Non ERP is loaded 3 times a day

PQ1 loading: on demand from PQ1 system

flag intra gbu decision variant:

Inside the PC PC_COPA_PL_03 (WP1 P&L), the loadings of DBCOPA02 → DBCOPA02 and DBCOPA03 → DBCOPA03 to redetermine the flag intra GBU are only once a week, determined by enhanced decision variants that read the last week of redetermination using MD c_glbfilt ( stream = "PL_ELEMENT", rule = "INTGBU")

IM PF1 decision variants:

• In PC PC_COPA_PL_36, the first decision variant is used to determine when loading is allowed, depending of the date => it is currently set to run from workday D+1 to D+3. This interval of workdays is defined in c_glbfilt MD, for stream = "PL_SOLV" and rule = "IM_RUN_PER". The calendar used to determine the workday is also set in c_glbfilt, for stream = "PL_SOLV" and rule = " IM_RUN_CAL". 

• The second decision variant read the PSA, using PSA table name in table RSTSODS for the datasource set in c_glbfilt MD (stream = "PL_SOLV", rule ="DSNAME"). It ensures that there is no remaining request in the PSA before to start a new loading which could mean a potential problem: that the last run was not completed as PSA is emptied at the end of the Process chain or the PSA was not cleaned after a manual loading.
  
  

• The third decision variant read the TVARV parameter "PL_TVARVC_CALMONTH_IM_SOLV" which contains the calmonths that were loaded in the PSA. The TVARV was updated previously in ABAP variant "Z_UPDATE_TVARVC_PL_IM_SOLV" which uses PSA table name in table RSTSODS for the datasource set in c_glbfilt MD (stream = "PL_SOLV", rule ="DSNAME").
  • If there is only one month in the PSA, then it is the normal behaviour and the loading is authorized.
  • If there are several months in the PSA, the PC is aborted and an error message is sent. In that case, an analysis must be done to understand why there are several months before to reload the data. 

Average performance

WP1: 1h40 for P&L and IM

PF1: 20min for COPA and ?ToUpdate? for IM

BFC: 10 min

Non ERP: 5 min

PQ1: 10min for GM and 1h for PCA

Record Keeping

We keep all records.

Reporting

Queries End User Documentation

All the reporting is available throught workbooks in the role "PL - P&L Reporting" ZR_RCS_CA_M12

Query documentation:

https://drive.google.com/a/solvay.com/folderview?id=0B-qbTutzOfcHNjRBa2p0UjNZZ1E&usp=sharing_eid&ts=57514e3b

Main queries

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.

Main functionnalities

Jump query available

Broadcast

No broadcast

Maintenance

Known bugs

No known bug

Recurring procedure

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: Delta + full load of current and previous months.

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

Planned Evolution

PQ1 data flow will be removed and replaced by PF1 system (project SPS)