The new wiki link for this data flow is here:
Technical Documentation - RTR - Profit and Loss (P&L) + Integrated Margin (IM) (WBP)
Please update the doc there and no longer here.
The new wiki link for this data flow is here:
Technical Documentation - RTR - Profit and Loss (P&L) + Integrated Margin (IM) (WBP)
Please update the doc there and no longer here.
P&L application p
rovides 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 :
Then, with SPS project, Solvay legacy PQ1 solution was replaced by PF1 flows.
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.
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
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. Solstice split was done in 2019.
| 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
Solstice Google Drive document
WP1
Updated dataflow after Solstice update (Rules are unchanged except for the Solstice split)
BFC
Last change : PO2 Project 08/2023
Last change : PO2 Project 08/2023
Non ERP
PQ1
PQ1 flows are kept only for historical data, they are replaced by PF1 flows since 01.2018.
PF1:
WP1
Updated dataflow after Solstice update (Rules are unchanged except for the Solstice split)
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/1MNI3gBn64zL3Jc-f-QZxDUT0q7NYW05t
https://drive.google.com/drive/folders/1A2msoTMxPzUKtiq4Zw33bdGmgCKda_du
https://drive.google.com/drive/folders/1Px62ePROuMFGP-Pd4Aq7DIU3wvHhqo_U
https://drive.google.com/drive/folders/1cpCy7Kal4YJxrm1R6g0QjmeRmGxYWgAl
Query documentation folder:
https://drive.google.com/drive/folders/1bh0bpwqpxzVC83Wzc9tk-E4YD3IS4JXx?usp=drive_link
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.
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:
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)
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
But at DSO level
ECO | SCO |
| |
For PO2 project the content of BFC amounts (BFC ECO or BFC SCO) are depending
The conso view (0, 1, 2 or 3)
The Date of analysis (before/After BFC SCO deployment ; before/After Spin Off)
We will receive 2 BFC files (some companies will be in Both) with YTD Legal and YTD Conso
If the month of analysis is before BFC SCO deployment (09/2023)
If the month of analysis is after BFC SCO deployment (09/2023) BUT before SPIN OFF
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)
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).
Some companies are in Solvay group but don’t use RCS or 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 ABCOPA03.
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 ABCOPA03 → ABCOPA03 are used to determine the flag intra gbu (C_INTGBUF). It compares the GBU ( 0g_cwwe01__cpfctr1_2 for DBCOPA02 and c_tecmat for ABCOPA03) 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.
BFC heading determination: the BFC heading is determined in master data C_FIELDN using DSO dpcopaa10 (data from WP1 table ZWFAT110).
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 load to ABCOPA01 determines the Specification List, Main Specification, Program, Market and Program Percentage for each new entry, using lookups in Master Data C_ITM_SD (Main Specification and Specification List) and ADSO ABDPDY01 (Program, Market, Program Percentage).
Delta load to ABCOPA01 cannot be determined solely from new data in ABCOPA03, as we must take into account the updates of the added fields Main Specification, Program, Market, Program Percentage. This requires to know if these values have been changed in the lookup sources. This issue is solved by the field 0RSPL_TSTMP (Time Stamp of Last Change) in ABCOPA03, that is updated by 3 different Delta loads:
We have 2 steps in the business layer.
From the propagation layer, we transpose data from DPCOPA15 to DBCOPA29.
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: DBCOPA29 to DBCOPA30
we calculate document currency amounts for the key figures maintained under c_glbfilt (stream = “C_FIELDN”, rule = “AMOUNTDC”): to calculate the document currency amounts, we use the rate stored in the document (rate type "M"), however, it doesn't take into account the conversion factors (TCURF). In order to retrieve these factors to translate correctly the amounts, we use the remote Function Module " ZFM_BW_EXCHANGE_RATE_RFC" that calls in PF1, for all concerned currencies, the function module 'READ_EXCHANGE_RATE'. Note that an alternative solution was to load in WBP 'READ_EXCHANGE_RATE' results from PF1 for all possible combinations, however, it took a lot of time (the datasource created for this is DTS_EXCHANGE_RATE).
We retrieve the conversion factors for the document creation date => theorically, the TCURF factors could change during a day, so the factors at the beginning and at the end of the day could be different. If this occurs, a workaround in order to find the correct factors for a document could be to get the rate using 'READ_EXCHANGE_RATE' for the creation date and also for the creation date -1 and to use the factors for the day when the rate is the nearer to the document rate.
*---1:HIGH indicates there are decimals in the currency like:EUR,USD
*---0:LOW indicates there are No decimals in the currency like:JPY,ITL,KRW
Above cases details are given below
| Cases | Currency in TCURX | Document currency in TCURX | Action in the result conversion | Comment |
| 00 | Yes | Yes | External conversion of CCcurr | Currency <> Document Currency |
| 11 | No | No | No Action | Currency <> Document Currency |
| 01 | Yes | No | External conversion of CCcurr | Currency <> Document Currency |
| 10 | No | Yes | Internal conversion of DocCcurr | Currency <> Document Currency |
Step 4 : Maintained below Global filter code to rectify the issue with the currency conversions (INR - JPY,SGD-JPY,CNY-JPY , THB-JPY & KRW - JPY) for which the amount in company code currency value is wrong in SAP .Due to this issue ,Keyfigures Amount in document currencies K_AMNTDC & K_AMNTNDC are showing wrong values in BW.
Step 5 : APPLY CONVERSION RATE TO CALCULATE AMOUNT IN DC
Step 6 : Apply the correct factors using exchange rate function module .
Step 7 : Apply external format to the amount without dimension K_AMNTNDC
BFC heading determination:
WBS element determination:
The standard WBS element from COPA, for CROCO billing types, is always empty. In this case, the WBSE is retrieved in PF1 table Z1F_CRC_INV (Z1F_CRC_INV-PSPNR), during COPA extraction, searching on the Sales order item (VGBEL/VGPOS). It is not possible to found several WBSE for the same sales order item. This enhancement is limited to record type "F" and to CROCO billing types maintained in TVARVC parameter ZBW_FKART_CRC.
Z1F_CRC_INV-PSPNR (8 characters) must be convert into external format (POSID -> 24 characters) for BW (key of c_wbs_el2), we use the function "SBW_PRPS8_TO_PRPS24" for mass translation.
The following technical MD are used in these flows:
-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/file/d/12lbg2DFSNQPCAXaTiUxM-d3nFZa_Mw9G2Tbwhs0WR7U/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/file/d/12lbg2DFSNQPCAXaTiUxM-d3nFZa_Mw9G2Tbwhs0WR7U/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.
The MD is compounded with the Partner activity. This field is used for the partner organisation structure determination in the COPA flow, not in IM flow, and it allows to keep the Partner activity determinated in the document.
-c_intgbu7 (reference c_intgbu6) - Intra GBU (Solvay COPA):
This object is used to determine dynamically the intra GBU flag, reading the GBU and partner GBU in c_tecpctr and c_techpac.
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.
The MD is compounded with the Partner activity. This field is used for the partner organisation structure determination in the COPA flow, not in IM flow, and it allows to keep the Partner activity determinated in the document.
-c_tgbr5:
This object is used to determine dynamically the GBR axis.
cf. specifications: https://drive.google.com/file/d/1PWQ4lzkt2B9Z2UTscu6vn4-lj3gtl5PIRVyDdqn9IuA/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.
08/2023 : PO2 Project changes
BFC Data comes from extraction sent by Marie-Yolande KUCZYNSKI
The file is loaded by Process chain BFC: Global Process Chain (PC_COPA_PL_12). This one is a subchain called in metachains:
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 (BFC ECO or BFC SCO) , we also load 2 other targets :
Those targets are in business layer.
(BFC ECO or BFC SCO) we mange in C_GLBFILT (Stream PL_BFC_ADJ) the authorization scope allowed by Origin DSO
For instance Cube CRCOPA08
Only Company with authorization scope ECO are allowed from DSO DBCOPA12
Only Company with authorization scope SCO are allowed from DSO ABCOP14
For instance Cube CRCOPA07
Company with authorization scope ECO and SCO are coming from DBCOPA11
Those parameters will allow to manage the period BEFORE BFC SCO ; Between BFC SCO -> SPIN OFF and after SPIN OFF
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.
Adjustment companies and CICC companies shared the same flow. The source system has to be determined:
This flow is restricted to the magnitude nature 0LIA01 for CICC and 1RET90 (maintained in c_glbfilt PL_BFC_ADJ/NATMAG) for Adjustment companies.
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 :
With this transaction, user can load excel file for
This program will save the file from user upload to format filename + upload time + user ID in folder /exploit/BW/PandL
and copy to the constant files basing on user selection what the data is updated to
Example of user manual to upload non-ERP file
Dataflow :
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:
Link to the functional document:
Link to the files description:
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
Currency check:
The currency is retrieved from c_compprs where c_compprs__company = c_company in the file during the integration by the transaction ZPL_FILE. An error is raised if the currency can't be determined.
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".
MD Company update:
The MD company is updated for Non ERP source system from the company and the trading partner in the flat file.
All attributes are retrieved from MD PRS company (c_compprs). The rules are the same for the company and the trading partner, a rule group "Partner" was created to process them in the same transformation:
Problem with old BFC company code: If the BFC company in the file has no correspondance in c_compprs, it is certainly because the file has the old BFC company code => we must use the current BFC company code even for historical data upload. For instance, BFC company code 7989 must be used instead of 7988:
IM valuation for Non ERP entries in P&L flows :
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 :
In "start routine" of the transformation TRFN: DBCOPA09 -> DBCOPA27, for a company list defined in the master data C_GLBFILT (See after bellow).
We read the DSO of the integration margin ODS_PCP4 with the specific key.
In case we find a matching entry with the requested KPI different from 0, we create a new line by adjusting the amount and the Magnitu account as follows:
Unit and currency Conversion :
In case the above calculation is applied be careful to standardize the units and currency between the two sources via the updated function module :
Master Data C_GLBFILT - Global Filter :
The list of companies can be changed with the Master Data global filter with the following parameters :
| Global Filter Stream (Application) | "Global Filter Rule" | "Global Filter" | Global Filter Active | Global Filter Description | Global Filter Hight Value | Global Filter Low Value | Global Filter Option | Global Filter Sign |
| CO_NONERP | COMPCDE | 1 | Y | Company Code | 07735 | EQ | E |
PQ1 flows are kept only for historical data, they are replaced by PF1 flows since 01.2018!
PCA: Profit Center Accounting
GM: Gross-Margin
We needed a solution to get the P&L data for Solvay legacy.
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
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/1ov1RY95046_CQarQjhJQsZtGBYEwITxL
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.
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:
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
The customer exit is updated in the process chain
Program ZBW_GLBFILT_Z_INT_MG2 setup global filter Stream = 'Z_INT_MG' and Rule = 'CALMONTH' to be M-2 to M-1
Program ZBW_GLBFILT_Z_INT_MG setup global filter Stream = 'Z_INT_MG' and Rule = 'CALMONTH' to be M-1 to M
APD will use query BW_QRY_MVSDTR01_9999.
In the query, the month is determined by a customer exit (class ZCL_BIU001_V_0CALMONTH_0010 - After variable)
Query will select Stream = 'Z_INT_MG' and Rule = 'TIERS_MTH' for the high value = period and minus low value (normally last 6 month)
BW_QRY_MVSDTR01_9999 (APD_DPCOPC01_0001) -> DPCOPC01 -> DBCOPA24
The date is using 0RSTT_RMNTH while Tiers query using C_SHIPNU2__C_COMPMON. It is correct from ticket WO0000000137785 (May 2022) to change from 0CALMONTH to 0RSTT_RMNTH
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)
Becareful : there is an exclusion of GBU
The authorizerd GBU are managed in Global Filter
Stream : Z_INT_MG
Rule GBU
Active : Y
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/file/d/1g3C-PCuUTmyfZ8e2HHZjsby_Tl5glHUZyUzzorxce6U/view
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 amounts.
If no records are found in ODS_PCP4, we determine value fields from source DSO
We determine value fields from source DSO
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.
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 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 (concerning the PQ1 flow) , we exclude key figures with flag as X
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.
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.
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 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).
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.
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]:
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
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.
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.
For Non ERP we load data from propagation layer but we only keep some value fields we modify
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.
To calculate Integrated Margin in these cases, we must:
Every one of these operations are centered around new DataStore Object IM - Solvay Sales at Rhodia IM costs (DBCOPA35), which
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
No dependencies
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
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
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")
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.
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
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/file/d/1Id73NuwErYWiHjzwaS0EQYUifRFXmIwV4T-xQc1bA5w/view
P&L : Time reference for CICC and GM flat files
https://drive.google.com/file/d/1XsvE8ags8d4PD9er8yFHfVBcjKL9Cf7sNtld4w7VyIE/view
IM: Delta + full load of current and previous months.
Program for LER determination
https://drive.google.com/file/d/1FbC7Bt_yqh6oGDhqonllvSh9uM7rfIgXdOqd3p1jfNo/view
Program for LER info providers
https://drive.google.com/file/d/1g3C-PCuUTmyfZ8e2HHZjsby_Tl5glHUZyUzzorxce6U/view
Todo list: https://drive.google.com/file/d/1Tb62fPsb5krpSNixzrM7RauOSq3y2phGWpfHqPS03ok/view
