General presentation

...

Access Management 

...

rovides repor

DataFlow

Overview

Solstice Google Drive document

MVCOPA01

ts 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

Query Technical Name: BW_QRY_MVCOPA01_0004

Note: Don't use the info objects C_GBR34, C_PROD, C_MAGNIT2 from the multi provider : MVCOPA01 in any of the queries built on the Multi provider MVCOPA01 as these objects are linked to only NON-ERP.

Solstice

An additional split (both in the main P&L dataflow and the IM dataflow from WP1) was deemed necessary to collect the Main Specification, Program and associated Market only for the concerned Solstice companies.

Owner : SL Finance Data & Reporting

SL Finance Data & Reporting Manager is ROLLIER, Charlotte

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. Solstice split was done in 2019.

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

Solstice Google Drive document

MVCOPA01

lref-gdrive-embedded-file

CPCOPA03

Image Removed

The dataflow is composed by 4 calculation views and one composite provider.

Calculation view:

• CV_FMCO_COPA_PL_SOLVAY_0001
• CV_FMCO_COPA_PL_RHODIA_0001
• CV_FMCO_COPA_PL_NONERP_0001
• CV_FMCO_COPA_PL_BFC_0001

It's the same techcnical source for multiprovider MVCOPA01, we used same DSO in different calculation views.

In CVs we just add 4 new fields:

• Sales Order
• Item Order
• Market Cluster
• Team Cluster.

To avoid store and multply the records in existing DSO and Cubes. Except these one, apart from those, we should have the same fields as the MVCOPA01

In the file below you can find the providers used as sources in calculations view and origin of each fields + filters applied.

In the file, if in cell we have:

"ok": field is directly mapped from DSO to composite provider (no change in CV)

"no": for the provider in column, the field associated is not used / mapped in composite provider.

"to do in cp": the navigationnal attribute is not defined in dso or calculation view but activated in composite provider.

P&L

WP1

Image Removed

Updated dataflow after Solstice update (Rules are unchanged except for the Solstice split)

Image Removed

BFC

Last change : PO2 Project 08/2023

Image Removed
Last change : PO2 Project 08/2023

Image Removed

Non ERP

Image Removed

PQ1

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

Image Removed

Image Removed

Image Removed

Image Removed

PF1:

Image Removed

Image Removed

Image Removed

IM

WP1

Image Removed

Updated dataflow after Solstice update (Rules are unchanged except for the Solstice split)

Image Removed

Image Removed

PQ1:

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

Image Removed

PF1:

Image Removed

NON ERP:

Image Removed

Image Removed

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 are the lowest key figures in COPA module (For BCS sourcing)

• Value Fields are the central elements of the BFC account determination. Since CROCO project, the determination of the BFC account depends of the value field and also the OECD activity for PF1 COPA flow.

• 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

P&L

WP1

Image Added

Updated dataflow after Solstice update (Rules are unchanged except for the Solstice split)

Image Added

BFC

Last change : PO2 Project 08/2023

Image Added
Last change : PO2 Project 08/2023

Image Added

Non ERP

Image Added

PQ1

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

Image Added

Image Added

Image Added

Image Added

PF1:

Image Added

Image Added

Image Added

CPCOPA03

Image Added

More details here.

General presentation

Objective of the application

P&L

P&L application provides repor ts 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

Query Technical Name: BW_QRY_MVCOPA01_0004

Note : Don't use the info objects C_GBR34, C_PROD, C_MAGNIT2 from the multi provider : MVCOPA01 in any of the queries built on the Multi provider MVCOPA01 as these objects are linked to only NON-ERP.

Documentation for IM is here:

Technical Documentation - BW Integrated Margin (WBP)

Solstice

An additional split (both in the main P&L dataflow and the IM dataflow from WP1) was deemed necessary to collect the Main Specification, Program and associated Market only for the concerned Solstice companies.

Owner : SL Finance Data & Reporting

SL Finance Data & Reporting Manager is ROLLIER, Charlotte

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. Solstice split was done in 2019.

Technical Rules on Workbench

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. Since CROCO project, the determination of the BFC account depends of the value field and also the OECD activity for PF1 COPA flow.

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

→08/2023 : PO2 Project 

BFC are data are now feeded from 2 BFC application (BFC ECO - the existing one and BFC SCO  )

BFC SCO will send data from 09/2023 

We still have 3 levels at  cube level 

• Version 1: Total P&L (CRCOPA03)
• Version 2: Interco Sales (CRCOPA04)
• Version 3 : Third Party (CRCOPA05)

But at DSO level 

• ECO

• Loaded by flat files

• 3 levels:

• • Version 1: Total P&L (DBCOPA04)
  • Version 2: Interco Sales (DBCOPA05)
  • Version 3 : Third Party

Another DSO for data without version

→08/2023 : PO2 Project 

BFC are data are now feeded from 2 BFC application (BFC ECO - the existing one and BFC SCO  )

BFC SCO will send data from 09/2023 

We still have 3 levels at  cube level 

• • (DBCOPA06)

• SCO:
• • Version 1: Total P&L (
CRCOPA03
• • ABCOPA11)
  • Version 2: Interco Sales (
CRCOPA04
• • ABCOPA10)
  • Version 3 : Third Party (
CRCOPA05)

But at DSO level 

• ECO:

• • Version 1: Total P&L (DBCOPA04)
  • Version 2: Interco Sales (DBCOPA05)
  • Version 3 : Third Party (DBCOPA06)

• SCO:
• • Version 1: Total P&L (ABCOPA11)
  • Version 2: Interco Sales (ABCOPA10)
  • Version 3 : Third Party (ABCOPA12)
• Inside the cube we segregate the information of the three cubes from BFC ECO and BFC SCO identifiable thanks to C_INFOPRO

...

ECO

...

SCO

...

...

...

• • ABCOPA12)
• Inside the cube we segregate the information of the three cubes from BFC ECO and BFC SCO identifiable thanks to C_INFOPRO

BFC data are used in 1 Core query : BW_BW_QRY_MVCOPA01_0003

For PO2 project the content of BFC amounts (BFC ECO or BFC SCO) are depending 

...

If the month of analysis is after  BFC SCO deployment (09/2023) BUT before SPIN OFF

If the month of analysis is after SPIN OFF

<--- 08/2023 : PO2 Project 

CICC

• Loaded by BFC flat files

• Only for CICC companies (companies in c_compprs MD with landscape = "CICC") or Adjustment companies maintained in c_glbfilt (PL_BFC_ADJ/COMPANY)

Below Gross Margin

• Loaded by BFC flat files, only for companies loaded in Non ERP for the concerned month (companies can move from Non ERP to ERP) when Below Gross Margin is not uploaded by flat file (= only Non ERP system for which a non full flat file is loaded).

Non ERP companies

• Some companies are in Solvay group but don’t use RCS or 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.

...

BFC heading determination: the BFC heading is determined in master data C_FIELDN using DSO dpcopaa10 (data from WP1 table  ZWFAT110).

Solstice (WP1)

An additional split is done from ABCOPA03 to 2 ADSO: ABCOPA01 and ABCOPA04. ABCOPA01 contains Solstice data and ABCOPA04 contains non-Solstice data. Split is done on a list of Company Code in the DTPs.

...

• Delta from ABDPDY01: If Program, Market or Program Percentage have been modified/created.
• Delta from ABDPDY02 & from ADPSDAE01: Both ADSOs are used to feed Master Data C_ITM_SD, if Main Specification or Specification List are created/changed.

P&L PF1

We have 2 steps in the business layer.

...

-c_tecpct2 (reference c_tecpctr) - Technical Profit Center:
This object is used to determine dynamically the organisational structure (activity1, GBU...).
It follows the business rules in COPA, these rules have changed after SPS project and the old model technical MD, c_techba, is now used for historical data only (before 2018).
cf. part "8.7 Organisation structure" of the specification document: https://drive.google.com/open?id=1lBBAR1mIJpdyw-RHrglgSSGm14uY1J1Whx9-j0k5jZ8/file/d/19tbW1LlskpgOQHslJAH1Aby85wZYjmRsKc6BBtE5SVM/view

The entries in this MD are generated only by the SID generation in transactional flows then transformation from the MD to itself determines the attributes.

-c_techpac - Technical Part Activity:
This object is used to determine dynamically the partner organisational structure.
It follows the business rules in COPA, these rules have changed after SPS project and the old model technical MD, c_techpba, is now used for historical data only (before 2018).
cf. part "8.8 Partner Organisation structure" of the specification document: https://drive.google.com/open?id=1lBBAR1mIJpdyw-RHrglgSSGm14uY1J1Whx9-j0k5jZ8file/d/19tbW1LlskpgOQHslJAH1Aby85wZYjmRsKc6BBtE5SVM/view

The entries in this MD are generated only by the SID generation in transactional flows then transformation from the MD to itself determines the attributes.

...

-c_tgbr5:
This object is used to determine dynamically the GBR axis.
cf. specifications: https://drive.google.com/open?id=18IyjGuveYkJ0hnnCT2YfpjjL3lf0BvpkCxlSL3NLzScfile/d/1AL5UzU3oIFqyzMAwU9Dv6Ko6SNWQcF6Z992nB5WxhAU/view

The entries in this MD are generated only by the SID generation in transactional flows then transformation from the MD to itself determines the attributes.

BFC

08/2023 : PO2 Project changes

...

This flow is restricted to the magnitude nature  0LIA01 for CICC and 1RET90 (maintained in c_glbfilt PL_BFC_ADJ/NATMAG) for Adjustment companies.

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

Documentation about this transaction :

Embedded Google Drive File docidhttps://docs.google.com/document/d/1gF426ok7VvnF1-idij_FCsMt7HMYQuY4LiN34CseSZs/edit

With this transaction, user can load excel file for

...

• customer_master_data.csv
• material_master_data.csv
• non-erp_data.csv

Example of user manual to upload non-ERP file

Dataflow :

Embedded Google Drive File docidhttps://docs.google.com/presentation/d/1fmDajNiJ7SF12m8K_ytJgScvxoaMmcEnUCVP7-S1d8w/edit

Process chain:

• PC_COPA_PL_06 
• PC_COPA_PL_07 
• PC_COPA_PL_08

The transaction available in SOLIA portal. For information, it's better to use IE and not ChromeImage Removed

We have created a new authorization object (C_SRSYST) to be sure that users can only load data for companies they have access to.

...

The goal is to valuate IM Cost for NON ERP flow using WP1 Material IM Cost unit (for a restricted list of companies); if no correspondence can be found, current business rules is applied (IM Costs = Standard Costs)

Link to the functional document

BW implementation : Widget Connector

BW implementation :

Image Removed

Image Added

In "start routine" of the transformation TRFN: In "start routine" of the transformation TRFN: DBCOPA09 -> DBCOPA27, for a company list defined in the master data C_GLBFILT (See after bellow).

...

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

Sales view:

The distribution chanel and sales organisation in PQ1 flow is determined, depending of the customer and business area, reading the DSO DPCOPA34. It was updated by a flat file based on PQ1 combinations (the link customer / business area → sales organisation is not unique, so this Business rule was estimated to be correct at 98%).

The file loaded in WBP was stored the following directory: https://drive.google.com/drive/folders/1CxIL3OR68e3DGFi6a3SFxY3eOfGm0nbK1sPk5Jh8nJBNVeYIV2dX2yOB23XDQGjyU

GBU: the GBU in the reporting is the attribute of c_techba. The GBU stored in the DSO from the Business transformation layer is also the attribute of c_techba (replaced between propagation and business layer) and not the GBU from PQ1 which is linked to the business area in ZZCONACT.

Some objects in the DSO, as the GBU, the division and material group, are stored instead of being determined dynamically. It was required in order to determine some attributes (GBR axis or sales employee) in the reporting and it was considered not necessary to develop a more complex solution for this historical data flow.

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.

...

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:

...

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)

...

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.

...

Stream :  Z_INT_MG

Rule  GBU

Active : Y

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.

...

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.

ZDETERMINE_LER is used in:

TRFN: DBCOPA02 -> IB_COPA_03
TRSF: ABCOPA04 -> IB_COPA_03
TRSF: ABCOPA03 -> IB_COPA_03
TRFN: DBCOPA03 -> IB_COPA_03
TRSF: ABCOPA01 -> IB_COPA_07

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.

ZDETERMINE_LER is used in transformations:

TRFN: IB_COPA_03 -> DBCOPA22
TRFN: IB_COPA_03 -> DBCOPA21
TRSF: IB_COPA_07 -> ABCOPA02
TRFN: IB_COPA_03 -> DBCOPA18
TRFN: IB_COPA_03 -> DBCOPA20
TRFN: IB_COPA_03 -> DBCOPA19
TRFN: IB_COPA_03 -> DBCOPA23

To have more details about Z_LER_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

Image Removed

Image Removed

Image Removed

We have a special case because we may have documents without quantity and transaction types B or Z

Image Removed

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

Image Removed

And we calculate new value fields

 Image Removed

Image Removed

Image Removed

Image Removed

Image Removed

/file/d/1AXQaMXmP4AdN9FeP8eByaRVQqDgs59BJBPL3NxSAhfc/view

1) determine where the program is sued to be able to select the data from corresponding dso:

Image Added

2) Select data from DBCOPA25:

Image Added

3) Determine the last day of COPA delivery month:

Image Added

4) Determine the LER FLag with help of DSO DBCOPA25 or master data C_PLANT:

Image Added

Image Added

In function of LER Flag:

Case 1 & 2: 1) No LER no Trading Plant  2) No LER Trading Plant

If c_fieldn =  VVD00 or  VVE00  or  VVF00 and transaction Type = B and Z:

Read data from COPA table and if  g_qvva04 is empty + if amount is not empty → Create a new line with fieldn  VVD0C:

Image Added

If c_fieldn = QUANTITY (standard case): we pick data from ODS_PCP4

Image Added

Image Added

Image Removed

Case 2: WP1 - No LER Trading Plant

Image Removed

We determine value fields from source DSO

Image Removed

Case 3: WP1 - LER

Image Removed

This case works only with quantity value field

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

Image Removed

Image Removed

Image Removed

Image Removed

Image Removed

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

Case 3: WP1 - LER

This case works only with quantity value field. We read COPA data.

Image Added

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

Image Added

If necessary, we convert quantitites with module function  'Z_MD_CONVERT_MATERIAL_UNIT_F' 

And we calculate new value fields:

Image Added

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

At the 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. 

Case 4: PF1

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

For PF1 system, we only use the GM flow (concerning the PQ1 flowthe PQ1 flox²²w).

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 (concerning the PQ1 flow) , we exclude key figures with flag as X

PF1 IM flow particularities:

ZZCONACT table in PF1 is the source for IM key figures in P&L model.

ZZCONACT is fully emptied each time a recalculation occurs. A recalculation is done for only one month and a list of companies (not always all the companies) => the solution should be adapted if several months are calculated at the same time.

ZZCONACT is loaded in DPCOPA23.

In WBP, a selective deletion on the recalculated perimeter (reading the PSA content) is done at each level of DSO/cube before loading. In consequence, no need to activate the data in DSO, so it was chosen to use write optimised DSO instead of standard one.

Selectives deletions are done in PC_COPA_PL_30 and PC_COPA_PL_33.

As the selective deletion is done according to PSA content, and only one month is allowed, please think to delete request in the PSA after a manual update!

A semantic key was chosen for the DSO in order to be sure that no duplicate data are loaded. All characteristics should part of the key but BW All characteristics should part of the key but BW do not permit more than 16 Infoobjects, in consequence, technical objects were created to concatenate the value of several characteristic into one objet (similarly to the PQ1 solution) in dso DPCOPA23 and DBCOPA34.

If there is duplicated data in the DSO, it could be due to an incorrect recovery procedure or because a new field was added to the ZZCONACT table and should be part of the key.

Allocation of internal logistic costs in IM (DBCOPA28 and DBCOPA36):

Percentage of allocation:

The DSO DPCOPA08 contains the result of the APD ADP_DSO_DPCOPA08. This APD retrieve, from the query BW_QRY_MVCOPA01_0011, the internal logistic costs (Euros) and the external sold quantity (VKG) at level industrial origin / business area / country of destination for the period [M-2 to M-1]:

• M = month defined in c_glbfilt__c_high stream = "PL_ELEMENT", rule = "INT_MARG", counter = "1", it is equal to the month being uploaded from ZZCONACT, derived from TVARVC parameter PL_TVARVC_CALMONTH_GM - 1.
• The number of months to take into account for the calculation, currently 2, can be changed using  c_glbfilt__c_low stream = "PL_ELEMENT", rule = "INT_MARG", counter = "1" (if c_low ="1" => calculation is done on M-2 to M-1, if c_low ="2" => calculation is done on M-3 to M-1,...)
• The percentage of allocation is calculated in the APD by the formula : ( 100 * R12910- Internal Log ) / ABS( N8110 - Qty Sold Ext ).

Remarks: DPCOPA08 contains only the result of the last APD execution. These data are then loaded into DBCOPA13 which keeps all historical data.

For the moment, the query BW_QRY_MVCOPA01_0011 can't give the result for several GBU (today only one GBU = SD) and it would not be easy to create a unique query that determine the key figure "R12910- Internal Logistic Cost" for several GBU as it should be filtered, for each GBU, by the partner = # or the concerned GBU. If necessary to enhance to several GBU in the future, a solution could be to determine the key figures split by GBU and Partner GBU and to complete the calculation in a transformation.

Reloading procedure:

The following steps must be done once by month to recover (it would be interessant to create a recovery Process Chain, that load for instance one complete year automatically, updating global filter before each APD execution)

1) Change the month in  c_glbfilt__c_high for stream = "PL_ELEMENT", rule = "INT_MARG", counter = "1" to the month to reload -1.

2) Execute APD ADP_DSO_DPCOPA08

3) Selective deletion on the month to recover in DBCOPA13 

4) Execute DTP: DPCOPA08 -> DBCOPA13 - Full then activate load requests in DBCOPA13

R1291C - Integrated Logistics costs - variable (TIERS)

The original solution (DBCOPA28) was created for PQ1 flow and is kept for historical data (<2018). This solution was reproduced for PF1 flow (DBCOPA36) for data >= 2018, the main difference between historical and new solution is that quantities are not coming from ZZCONACT anymore but from COPA.

PQ1 flow (<2018):

It consists to generate a line on P&L element ZR1291C => it is derived from the quantities on which we apply the percentage of allocation (= the internal logistic costs / the external sold quantity) in the DSO DBCOPA13 at a level industrial origin / business area / country of destination.

The quantities are determined from ZZCONACT lines with P&L element = "ZN8110BQTY".

This solution is currently used only for Soda-Ash: DTP and start routine filtered on C_TECHBA__CPFCTR1_2="SD", the GBU is maintained in C_GLBFILT MD: stream = "PL_ELEMENT", rule = " IM_IL_GBU"). In order to enhance to other GBU, it would be necessary to update DBCOPA13 to have the percentage of allocation by GBU.

PF1 flow:

It consists to generate a line on P&L element ZR1291C => it is derived from the quantities on which we apply the percentage of allocation (= the internal logistic costs / the external sold quantity) in the DSO DBCOPA13 at a level industrial origin / business area / country of destination.

The quantities are determined from COPA lines. Following CROCO project, some quantities are in unity "PRT" (services), these quantities must not be taken into account in the calculation.

The Process Chain is synchronised with ZZCONACT loading as it will determine the month to calculate (as today the percentage of allocation for M-1 is determined using costs on [M-3;M-2] there is no real dependance). This month is updated in TVARVC  variable "PL_TVARVC_CALMONTH_IM_SOLV" updated in PF1 IM Process Chain .PC_COPA_PL_36:

Image Added

Deletion of the data on the month to reload is done on the DSO in case the keys are not the same after reload (change on BU, country of destination or restated BA determination or less lines after ZZCONACT recalculation) but it should not be usefull normally.

This solution is currently used only for Soda-Ash: end routine filtered on C_TECPCT2__CPFCTR1_2="SD", the GBU is maintained in C_GLBFILT MD: stream = "PL_ELEMENT", rule = " IM_IL_GBU").  In order to enhance to other GBU, it would be necessary to update DBCOPA13 to have the percentage of allocation by GBU .(TRFN: DBCOPA29 -> DBCOPA36)

Case 5: Non ERP

For Non ERP we load data from propagation layer but we only keep some value fields we modify (TRFN: DBCOPA09 -> DBCOPA27)

Image AddedImage Removed

Case 7: Sales in PF1, Production in WP1

Image Removed

In some cases, Integrated Margin needs to be calculated for a sale stored in Solvay system PF1, containing materials produced by a RCS plant and thus stored in WP1 system.

A set of eligibility rules has been established to qualify the need to check in WP1 system for Integrated Margin, as displayed in the graph below.

Image Removed

To calculate Integrated Margin in these cases, we must:

1. Collect the Material information from WP1, associate the quantity discovered to an entry in the Cost Unit DSO (ODS_PCP4).
2. Suppress the existing Integrated Margin value calculated from PF1 entries (these "negative" entries are stored in DBCOPA41).
3. Make the data available for Reporting, via a new cube CRCOPA30 added to MultiProvider MVCOPA01.

Every one of these operations are centered around new DataStore Object IM - Solvay Sales at Rhodia IM costs (DBCOPA35), which

• Collects data from existing Solvay DataFlow (DSO Profitability Analysis: COPA - Solvay Legacy DBCOPA30), Note: There is a filter in transformation between DBCOPA30 and IB_COPA_06 to delete all but Novecare (CS) data.
• Proceeds with eligibility checks (reading DSOs Profitability Analysis: COPA - Origin/Plant Match-up DPCOPA35 and IM - Solvay PF1 - GM Data ( Write-Optimized ) DBCOPA34 to qualify eligibility),
• Calculates Integrated Margin on eligible entries,
• Feeds the list of eligible entries to IM - Solvay PF1 - GM Data Annulation ( Write-Optimized ) DBCOPA41 to negate existing Integrated Margin entries in DBCOPA34,
• Feeds cube IM - Solvay PF1 - GM Data Annulation CRCOPA30 for Reporting of the new WP1 Integrated Margins.

Process Chain: PC_COPA_PL_45 - IM: RCS-Produced Solvay Materials

This PC will delete/reload the data on 0CALMONTH = parameter in C_GLBFILTER

Stream = COPA
Rule = DBCOPA35
Counter = 001

on following infoproviders (program Z_DEL_DBCOPA41 variant Z_MAIN)

DBCOPA41, DBCOPA35, CRCOPA30

Filter data to load from DBCOPA30 to DBCOPA35 on TRSF: ODSO DBCOPA30 -> TRCS IB_COPA_06

Counter 002 by company code

Coutner 003 by GBU

 Image Removed

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 & PF1 COPA, BFC and WP1 IM daily

PC_COPA_PL_22 : Load WP1 & PF1 COPA Extra load during closing period. This chain included too chain PC_COPA_PL_12, which load BFC File in WBP.

PC_COPA_PL_36 : Load PF1 IM and allocation of transportation costs PF1

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 4 other times between working day 2 and 4 (8am, 1pm, 3pm and 8pm, managed in c_glbfilt PL_ELEMENT/CHECK_LOAD).

PF1 IM are loaded usually once by day from D+2 to D+5 (it is executed automatically on event after ZZCONACT update in PF1)

BFC is loaded daily at 1h30 am (french time). On closing period from D+2 to D+5 it is loaded too on 08H, 13H, 15H and 20H.

NON ERP is loaded 4 times a day (7 AM, 13 PM, 18 PM, 24 PM):

PQ1 loading: on demand from PQ1 system => PQ1 is now decommissioned, no more loading

flag intra gbu decision variant:

Inside the PC PC_COPA_PL_03 (WP1 P&L), the loadings of DBCOPA02 → DBCOPA02 and ABCOPA03 → ABCOPA03 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")

Image Removed

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 to D+31 (so no restriction anymore). 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". 

Image Removed

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

Image Removed

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

Image Removed

As part of the change # 4287353 ,In the  meta chain PC_COPA_PL_36,added the PC_COPA_PL_45  process chain after the execution of PC_COPA_PL_42.

Image Removed

Average performance

WP1: 1h40 for P&L and IM

PF1: 20min for COPA and 20min 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

Image Removed

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

This PC will delete/reload the data on 0CALMONTH = parameter in  C_GLBFILTER

Stream = COPA
Rule = DBCOPA35
Counter = 001

on following infoproviders (program Z_DEL_DBCOPA41 variant Z_MAIN)

DBCOPA41, DBCOPA35, CRCOPA30

Filter data to load from DBCOPA30 to DBCOPA35 on TRSF: ODSO DBCOPA30 -> TRCS IB_COPA_06

Counter 002 by company code

Coutner 003 by GBU

 Image Added

Reporting

Queries End User Documentation

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

Image Added

Query documentation:

https://drive.google.com/drive/folders/1pFQ_tfTUYK1xEz4-cz0JFel7arKyY7Nn?usp=drive_link

Reporting documentation drive folder:

https://drive.google.com/drive/folders/19pPWUmAkTcW1aYlzlqyHOxvL6JfDdaKe

https://drive.google.com/drive/folders/1zF0862L7nRnu39MXdtVORT-HkmHavs-R

https://drive.google.com/open?id=0B_p_Afe8sjVlTEVnc3NjdDV5U0U https://drive.google.com/drive/folders/1miRFX_LJKQ5YGWq-r3-YETCNkZcRhLvy

https://drive.google.com/drive/folders/1xY6QOu8gmrvmyncdpSu5trUA5SQIliRX

https://drive.google.com/drive/folders/1rzUHdVecCOROXOkZBpp7uhbHNJqIc63Y

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.

Key figures logic

Main functionnalities: Jump query available

Dependencies with other applications

List of APD:

Data Loading

Info providers and objects loaded

List on info providers inside the technical cockpit.

Main process chains are:

PC_COPA_PL_GLOBAL : Load WP1 & PF1 COPA, BFC and WP1 IM daily

PC_COPA_PL_22 : Load WP1 & PF1 COPA Extra load during closing period. This chain included too chain PC_COPA_PL_12, which load BFC File in WBP.

PC_COPA_PL_36 : Load PF1 IM and allocation of transportation costs PF1

PC_COPA_PL_09 : Load Non ERP Master data and Transactional data

Other chains:

Loading frequency

WP1 and PF1(except for PF1 IM) are loaded daily at 1h30 am (french time) and also 4 other times between working day 2 and 4 (8am, 1pm, 3pm and 8pm, managed in c_glbfilt PL_ELEMENT/CHECK_LOAD).

PF1 IM are loaded usually once by day from D+2 to D+5 (it is executed automatically on event after ZZCONACT update in PF1)

BFC is loaded daily at 1h30 am (french time). On closing period from D+2 to D+5 it is loaded too on 08H, 13H, 15H and 20H.

NON ERP is loaded 4 times a day (7 AM, 13 PM, 18 PM, 24 PM):

PQ1 loading: on demand from PQ1 system => PQ1 is now decommissioned, no more loading

Flag intra gbu decision variant:

Inside the PC PC_COPA_PL_03 (WP1 P&L), the loadings of DBCOPA02  → DBCOPA02 and  ABCOPA03  → ABCOPA03 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")

Image Added

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 to D+31 (so no restriction anymore). 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". 

Image Added

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

Image Added

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

Image Added

As part of the change # 4287353 , In the  meta chain PC_COPA_PL_36,added the PC_COPA_PL_45  process chain after the execution of PC_COPA_PL_42.

Image Added

Average performance

WP1: 1h40 for P&L and IM

PF1: 20min for COPA and 20min for IM

BFC: 10 min

Non ERP: 5 min

PQ1: 10min for GM and 1h for PCA

Record Keeping

We keep all records.

Dependencies with other applications

Broadcast

No broadcast

Open Hub

APD

Data Quality Control

Data come from SAP system. To compare data between BW and sources systems, check propagation layers.

Table KE30 can valid with P&L on non-console view.

Table CE1+ Operating concern in the source should be the same value as Propagation layer of COPA data. 

Operational Documentation

Procedures

P&L : Non ERP transaction

https://drive.google.com/file/d/1Mqb9wnFqnp2tl6j4AFKbWjm6BnDGSxPTQJHyOYvwspQ/view

P&L : Time reference for CICC and GM flat files

https://drive.google.com/file/d/1EoZAjLveLXL2wASs1m-l1D8LO8l28y5A_Jze6sO-bS4/view

IM: Delta + full load of current and previous months.

Program for LER determination

https://drive.google.com/file/d/1VnOB0-jeV85c6kJYram-fuIh70U3gWU7LHiTHtB8e0I/viewP&L : Non ERP transaction

https://drive.google.com/open?id=1gF426ok7VvnF1-idij_FCsMt7HMYQuY4LiN34CseSZs/file/d/12hYsIOncEYpMI6aeOtjZtIHS3o2-GCLnnCZiKzJOUiU/view

Program for LER info providersP&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

/file/d/1AXQaMXmP4AdN9FeP8eByaRVQqDgs59BJBPL3NxSAhfc/view

Scheduling

<Describe the scheduling in place for the application (eg. existing jobs, trigger time/event based, dependencies)>

Monitoring

<Describe the monitoring checks to confirm the application is performing well (eg. check the overall status, check performance metrics like runtime/data volume/memory/disk/CPU, maintain and react to alerts/notifications)>

Error Handling

<Describe how to handle errors (eg. error codes, description and respective resolution, alert users)>

Known Bugs

<List the existing bugs, its criticity, workarounds and resolution plan.>

Roadmap

...

Todo list: https://drive.google.com/open?id=1GVVKIEbmSyeZx2lke4kmLg5MWHhjgsjka_7ewLcNGsY/file/d/1cvHfPpBByAQnYIZUZpvMkwFQmgmGESkSdrQrBhcTz9s/view