General presentation
Objective of the application
This page provides all the technical and detailed documentation concerning the "FIWC for Solvay Group" (Finance Working Capital) application in BW, including :
- General overview of the dataflow
- Query, Workbook, Webtemplate and broadcasts documentation
- Maintenance operations : necessary manual updates of master data.
This application replaces the former FIWC application which was created for Rhodia legacy (WP1). It was done in WISE project.
Working Capital global definition: The working capital is a financial indicator used to measure the Free Cash Flow of the enterprise.
It is equal to the sum of:
- Value of the stocks
- Value of the customer receivables
- Value of the vendor payables
It excludes:
- Intercompany
- Componies not in consolidation area (consolidation view) - for GBU usage only
In BFC:
Every month, persons in each company will get the values from RCS/BW for each account and enter it manually in BFC.
- Values are entered in local currency. BFC then converts to EUR.
- If amounts are not affected to a business unit then they will manually affect the amounts using rules.
Reporting coordinator is Sandrine Micollet.
Usage information
Around 220 users have access to WC applications. Those users are worldwide and most of them are controllers.
History
Project done between 2013 and 2015.
Roles & Access
Roles and access
| Role Code | Role Description | Explanation |
|---|---|---|
| ZR_RCS_CA_M41 | WCAP – Working Capital Solvay Group | Role menu |
| ZBI_RCS_FI_A31 | WCAP – Working Capital GBU Solvay Group - End User role | End user role |
Authorisation objects
Link to the BW Catalog of role
https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I
Dataflow presentation
Overview
https://drive.google.com/open?id=1X0aj6n8leT5osUadKx5AWm9Z4OXuh-6CihIEFbuSEjg
Reporting documentation drive folder:
Query documentation folder:
Functional and Technical rules on Workbench + Reporting
Rules & Explanations
Systems involved
Rhodia ERP WP1
Solvay ERP PF1 (020)
CICC ERP PI1
Acetow ERP RHO
PRS ERP PF1 (050)
BFC (flat files)
G/L Accounts
We can split the Working Capital in 3 main component (GL Type), which separates in sub-components (GL Sub types).
G/L Accounts restitution:
Account types and sub types are managed in WP1 and PF1 systems (PF1 and PI1 share GL accounts so no need to load from PI1 system).
For Acetow system, it's managed manually
Consolidation or Accouting view
Working Capital for GBU queries are working in consolidation view, meaning we exclude Intercompany and other filters in restricted key figures also the intergration rate.
Working Capital for Accounting offers with a variable to working in accounting view, without any filters.
Intercompany exclusion
In the queries, we keep only the company with Intercompany flag as X. This flag is determined in PRS source system.
The flag is stated as X for the consolidation methods 10 (IG - Fully consolidated), 30 (IP - Proportionately consolidated) and 55 (ME - Equity method futur IP). The table used is ZZF_T001_MGT.
Business Assignments
For data coming from FIAR, the base is the Sub-Activity 0G_CWWE01. It's a mix of IECRA (WP1 and RHO systems) and Business area (PF1 and PI1). Functionnally speaking IECRA and Business area are similar so we decided to mix it an existing object, 0G_CWWE01.
For data coming from FIGL and FIAP, the base of the business assignment is the Sub-activity 2 C_SUBACT2. It's a mix of Profit center (WP1) and Business area (PF1, PI1 and RHO systems). As Profit center has a compounded key with the controlling area we were not able to use the same info object as for FIAR.
The notion of Profit center exists in PF1/PI1 but for a different usage that accounting. So to avoid data pollution in existing object 0PROFIT_CTR, we decided to create a new info object, C_SUBACT2.
Both info objects give the Business structure with same attributes used for reporting purpose.
Illustration for WP1 system:
Example of the profit center hierarchy that is used to define the Industrial Axis in Sub-activity 2 info object:
PRS Source system
We load some master data from PRS system (PF1_050).
With this some source system, we can be sure to have an unique code for companies, customers and vendors.
In RCS and Acetow, the link between ERP and PRS is managed in tables ZZR_T001_MAP (company), KNA1 (customer) and LFA1 (vendor).
For Solvay and CICC, the PRS code is the same than ERP code.
PRS master data are stored in dedicated info objects: C_COMPPRS, C_CUSTPRS and C_VENDPRS.
We also have dedicated info objects from the ERP with the ERP system and code and the PRS attributes: C_COMPCDE, C_CUSTID and C_VENDID.
At the beginning we used C_COMPPRS, C_CUSTPRS and C_VENDPRS in the info providers in order to use their attributes but due to poor data quality we had too oftenly the case of missing link in the ERP between ERP and PRS codes. That's why we decided to replicate PRS attributes in C_COMPCDE, C_CUSTID and C_VENDID info objects.
Currency conversion
The BW working capital reports are all based on amounts in Local Currency Those amounts come directly from the source systems.
If user selects a currency in the prompt the query will convert, otherwise it will return the values in local currency.
This conversion is based on the ZRHO rate (coming from WP1).
- See private documenation on BW Currency Conversion
The current available rate s applied to ALL data (even past data) independently of the date.
For base amount we use the Debit/Credit Amount 0DEB_CRE_LC or FIGL/FIAR/FIAP data and the Stock Value for IM data.
We then apply conversion type ZRH2 for Working Capital - CTK_ZRHWC :
- Based on * ate ZRHO* :
- Uses the rate available for date = * revious Day Exit (Working Cap) -* DATEWC00
- We use the target currency from variable * urrency (Single Value, Optional)*- CURVAR01
Full factoring
The new factoring solution for Solvay leads to the transfer of invoices (AP and AR) from the different ERP (WP1, PF1, RHO) to the CICC system without replication in the ERP, so no possibility to follow the payments in the ERP only (documents are cleared in the ERP when transferred to CICC). Reporting needs are to continue to follow these transferred invoices with the same details, such as MM, SD data and GBU for instance, which can't be determined with CICC data only.
Two kind of flows were created from CICC data propagation layer (DPFIAP03):
- the before CAMS project flow (to DBFIAP03) which concerns the documents not assigned to a factoring contract version "2"
the technical rules are described in "FIAR data flow" and "FIAP data flow" parts - the "factoring" flows, created for CAMS project which concern the documents assigned to a factoring contract version "2"
the technical rules are described in "FIAR Factoring data flow" and "FIAP Factoring data flow" parts
Some more informations on full factoring (CAMS project):
https://drive.google.com/open?id=0B2w5MF8sg0HxMzJTRXFVOWZLem8
Technical rules
FIAR data flow
This part of the Working Capital has been done via ITC project. Please check ITC page for more details.
FIAR Factoring data flow
Please check ITC page for more details.
FIAP data flow
FIAP data re also be used for SPRINT (Purchasing), Project costs and CAPEX applications.
Net due date management:
The net due date is managed at the document item level and is calculated in the ERP. A new net due date managed manually has been set in each system.
It's available in ZZF_BSEG table.
For each system we created a new data source based on this table and a dedicated DSO (DPFIWC01 to 04).
In BW, there is a direct loading between Net due date DSO and Business layer DSO and also a look-up between Propagation and Business layers to read DPFIWC DSO.
Reclassification of Sub-Activity C_SUBACT2:
In the ERP, when a profit center or a business area is set, it's not possible to modify it later.
In order to resolve that issue, mecanisms have been created in the ERP.
In WP1
Documents can be modified in ZWFAT205 table (transaction ZWFAT205).
In the example below, document has been modified with a different profit center:
This table is loaded in DSO_FI11 in BW and data are updated in Business layer DSO.
In PF1 and PI1
In PI1 system, the mecanism is different and is not managed document by document, like in WP1.
It's done in PI1 (for PI1 and PF1) through data source ZZFCM_BU_EXCEPTIONS_NEW.
This table is loaded in DBFIWC01 DSO in BW.
Between Propagation and Business layers, we read this DSO to determine the new business area.
For example, document for company code 0005 and business area 3240, the new business area will be 8500.
Rhodia legacy rules:
Initial loading was done from existing DSO ODSFIAP1 and not from WP1 system due to archived document not loadable anymore.
A split of data is done between propagation and business DSO (DPFIAP01 and DBFIAP01), in the start routine inside program z_start__dpfiap01__dbfiap01.
The split is also done in WP1 system, in ZWFAT123 table. This table is filled in WP1 when the data source 0FI_AP_4 is launched. The data are retrieved in ODS_FIWC.
Solvay legacy rules:
A split of data is done between propagation and business DSO (DPFIAP02 and DBFIAP02) but the mecanism and the logic is much more simple.
We split data using DPFIGL02 DSO (FIGL data) and DBFIGL05 DSO (FIGL - AP data) with detail by material, plant, purchase order number / item, WBS element and CO order.
DBFIGL05 look up is used to retrieve CO order / WBS element enhancements done in this DSO.
We calculate the item's quote part in FIGL and apply this quote part on amount from FIAP document. For the last splitted item, amount is the total amount minus the sum of other splitted lines.
Net of Tax (NoT) amounts are also calculated between propagation and business DSO:
it corresponds to the amount of counterparts in the same accounting document (non FIAP line items and non tax line items).
When there are several K items in the document, the split is done according the prorata of the amounts in local currency.
If there is no tax line items in the document => NoT amounts are similar to the including tax amounts.
If the counterparts are only tax items, the NoT amounts are similar to the including tax amounts.
Tax line items are identified by a list of transaction keys. As transaction keys are not filled in the DSO DPFIGL02 before 2013, a list of G/L accounts is used instead to identified tax line items for postings before 2013 (identification by G/L accounts is less perennial than using the transaction keys).
CICC legacy rules:
This flow is filtered to the factoring version <> "2".
In some cases, the factoring contract could be assigned later to the FI document line item and the line item could be moved from this flow to the "factoring" flow. Moreover, as the factoring version is determined in the Data Propagation Layer using a secondary transformation, there is always old images of the FI document to are loaded to this flow at the document creation.
That is why a rule in the start routine of the transformation DPFIAP03 -> DBFIAP03 is used to delete entries that should not be in this flow by replacing recordmode "X" by "D". It is done only when the last image of the FI document line item is recordmode "X" (which means that the new image was routed to a "factoring" flow). In consequence, a semantic group is required in the DTP to ensure that all records on the same FI line item are gathered in the same source package.
In consequence, be aware that some entries could be deleted in the DSO DBFIAP03 (and that it has to be taken into account by the solutions based on this DSO).
Acetow legacy rules:
A split of data is done between propagation and business DSO (DPFIAP04 and DBFIAP04):
We split data using DPFIGL04 DSO (FIGL data) with detail by material, plant, purchase order number / item, WBS element and CO order, for RMRP documents only.
We calculate the item's quote part in FIGL and apply this quote part on amount from FIAP document. For the last splitted item, amount is the total amount minus the sum of other splitted lines.
Deletion of existing lines (in the DSO DBFIAP01/02/04):
As a document line item can be split by several criteria, if, for any reason the value of a split criteria has changed (that may normally not occur), the key on the old value still remain in the DSO and a new key is created which would lead to false result and duplicated amounts.
In consequence, before to update a document item in the DSO, the existing lines on this document item in the DSO are deleted by creating new entries in the datapackage with recordmode = "D".
Warning: in transformation to DBFIAP01 (RCS data), some key figures are in "summation" mode. In consequence, in the case, the keys values are changing for a line item, ensure to not load data until the previous request is activated in the DSO, else, it could lead to duplicated amounts (as the second request would try to delete the old key instead of the new one).
Generally, for every transformations with the deletion of existing lines in target DSO, ensure that every load requests are activated in the DSO before to load a new request (in case the key values changed).
Also note that a semantic group is defined in the DTPs in order to process every images of a FI document line item in a same package. It should be kept to ensure every cases are correctly handled.
FIAP Factoring data flow
https://drive.google.com/open?id=1sWAG-o9nYLzPOKkd4wHgUxR0nNEKcLpQoosRUaCROfA
Class ZZF_CL_FACTORING_TOOLS:
Most of the transformations used methods of the class ZZF_CL_FACTORING_TOOLS (Tools for the factoring process).
The main methods are:
- GET_INITIAL_ITEMS : method used to retrieve the original documents (invoices in the ERP transferred to CICC) from a list of factoring contract number.
- GET_LANDSCAPE_FROM_COMPPRS : method used to determine the landscape associated to a PRS company code
Data Propagation Layer (DPFIAP03) updated from DPFIWC03 (ZZF_BSEG CICC table):
Update of existing entries in DPFIAP03 only (no creation of new lines)
The factoring contract, Affiliate PRS company code, Affiliate document type, netDueDate are updated directly from the source
The factoring version is derived from the factoring contract number
- The landscape is obtained from C_COMPPRS MD using the Affiliate PRS company code
An error stack is updated for the following cases:
1) The line item in DPFIWC03 can't be found in DPFIAP03 => no update in DPFIAP03 but the record is put into the error stack to be reprocessed during the next process chain's run
2) The landscape is empty but the factoring version is "2", it should be due to inconsistencies in C_COMPPRS Master Data => the line item is updated in DPFIAP03 (that means that the factoring contract number and factoring version are updated in DPFIAP03 but, without a landscape, the entry will not be routed to any BTL DSO) but the record is put into the error stack in order to be reprocessed automatically when the master data is fixed.
Business Transformation Layer common rules:
Rules in common for all landscape (DPFIAP03 -> DBFIAP05/06/07) should be maintained in the transformation between the infosources "IB_FIAP_06" and "IB_FIAP_03".
Business Transformation Layer - landscape dependent rules:
Enhancements and split using data in AP ERP DSO
The logical is similar for every landscape, however, the DSO to read the Affiliate data and the fields to retrieve are different.
For “Agent” documents,
Landscape "ERPRCS":
Enhancements of Affiliate data using data in AR RCS DSO (DBFIAR01). The sub-activity 2 is the Affiliate profit center.
Landscape "ERPSOLV":
Enhancements of Affiliate data using data in AR RCS DSO (DBFIAR02). The sub-activity 2 is the Affiliate business area.
Landscape "ERPRHO":
Enhancements of Affiliate Acetow company code using the correspondence with the Affiliate PRS company code in the Master Data C_COMPCDE.
The sub-activity 2 is not determined.
Business Transformation Layer - Delta from ERP to redetermine Affiliate data :
The delta is required to redetermine Affiliate data when it has been changed in ERP DSO, it is particularly important when there is the reclassification of the FI document.
CICC data propagation layer is read to reprocess the documents assigned to the impacted factoring contract number
Business Transformation Layer - records deletion and recordmode management :
Business Transformation Layer - error stacks :
An error stack is updated when the document can’t be found in ERP DSO (if error handling mode field = “1”).
FIGL data flow
Common rules:
In business layer, we filter to keep posting on account types S (G/L Account) and M (Materials)
Rhodia legacy rules:
We continue to use ODSFIGL3. This DSO is very huge so it would have been to long to load other DSO and it would be to heavy in WBP system.
A split of data is done between propagation and business DSO (ODSFIGL1 and ODSFIGL3), in the start routine of the transformation.
The split is also done in WP1 system, in ZWFAT143 table. This table is filled in WP1 when the data source 0FI_GL_4 is launched. The data are retrieved in ODSFIGL2.
Data initialization:
For Solvay and CICC systems, initialization was done using PQ1 system. We made this choice for performance reasons.
FIGL-AR data flow
No specific rules.
FIGL-AP data flow
No specific rules.
An additional determination of the CO order and WBS Element is done when updating the FIGL-AP Solvay DSO (DBFIGL05), depending of the reference procedure:
- MKPF: retrieve the CO order and WBSe found in a P&L item of the FI document with the same Purchase Order item.
- RMRP: idem MKPF but if no P&L item was on the same PO item in this FI document, look at any MKPF document item on the same PO item.
- Other: retrieve the CO order and WBSe found in a P&L item of the FI document with the same amount.
FIGL-IM data flow
New master data C_MATPNT3
Technical object
Based on MBEW table
Attributes Valuation class, company code, GL account
To “correct” accounting from SAP systems : to have same BFC GBU account allocation as in SAP Logistic (could be different from allocation in Accounting)
Need to split FIGL-IM in two silos
Silo S with accounting on G/L account => based on postings
Silo M with accounting on Material => based on C_MATPNT3
2014 and after only
Snapshot at 31.12.2013
- Impossible to load full history
Rhodia legacy rules:
Snapshot was made at 31.12.2013 from CUB_IC001
Solvay legacy rules:
Snapshot was made at 31.12.2013 from PQ1.
Acetow legacy rules:
Snapshot was made at 31.12.2013 with flat files from RHO system.
We tried to use standard data sources 2LIS_03_BX, BF and UM to make an initialization at 31.12.2013.
Initialization with 2LIS_03_BX worked but we failed with the other data sources.
Cash out data flow
Please check Project Costs pages (Cash out part) : https://wiki.solvay.com/x/1Lo_AQ.
These cubes are used in Working Capital reporting in order to remove CAPEX amounts from the payables.
BFC data flow
Link to BFC data flow documentation: https://drive.google.com/open?id=0B_p_Afe8sjVlY29mX05aSEx1Umc.
Dependencies with other applications
WC applications is dependant with
- Project costs (FIAP and FIGL flows)
- ITC Credit Management (FIAR flow)
- SPRINT Purchasing (FIAP flow)
- CAPEX (FIAP and FIGL flows)
Data loadings
Info providers and objects loaded
List on info providers inside the technical cockpit.
Loading frequency
During closing period (last day of the month and 4 fist working days):
- WP1 data are loaded 7 times a day: 6am, 10am, 12am, 2pm, 4pm, 6pm and 10pm (french time)
- PF1, PI1 and RHO systems are loaded 3 times a day: 6am, 12am and 6pm (french time)
Outside closing period:
- All systems are loaded 3 times a day: 6am, 12am and 6pm (french time)
Average performance
Around 1h for WP1 and 1h for other systems.
Record Keeping
We keep all records.
Reporting
Queries End User Documentation
All the reporting is available throught workbooks in the role "WCAP - Working Capital Solvay Group" ZR_RCS_CA_M41
There are 2 sub-folders, 1 for GBU usage and 1 for Accounting and BFC usage.
All workbooks are based on the same info providers.
Query documentation:
Main queries
There is a lot of queries but the main one are:
BW_QRY_MVFIWC01_0002 BW - Working Capital (Core Query)
BW_QRY_MVFIWC01_0003 BW - Working Capital Receivables (Core Query)
BW_QRY_MVFIWC01_0004 BW - Working Capital Payables (Core Query)
BW_QRY_MVFIWC01_0005 BW - Working Capital Inventories (Core 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
A broadcast is sent to users. The schedule is determined in the BW process chains. Actually, the broadcasts are sent the 6th, 10th, 15th and 20th working day of the month.
For those days, at the end of the process chain, a file is put in the BO server. When the BO server idenfity this file (an empty txt) file, it triggers the broadcasts.
All the design and settings are managed in BO.
Maintenance
Known bugs
No known bug but the following bugs may happen:
- Process chain for Master Data: For some master data we may have invalid characters in some fields. In this case, we modify the value in PSA to remove invalid character and we ask for correction in the source system
- Process chain for Transaction Data: Some roll-up requests of cube may fails. We have to repeat the action in the process chain to solve the issue.
- Documents uploaded in ZWFAT205 table but not in BW. It happened that a document was updated in ZWFAT205 table in WP1 at the exact same time that the info package ran in BW. So the document was not updated. We had to load it manually in BW
Examples of Freshdesk tickets:
- 2973: Add more fields in 0FI_AP_4 data sources
- 3333: Master data attributes not filled in PF1
- 5008: Bad description of C_MAGNITU and 0BUS_AREA
- 9433: Question about filters
- 2324: WP1 AP data wrongly updated in WBP
Recurring procedure
FIGL Clearing zones
https://drive.google.com/open?id=1EcYdNGfbiOdFa1TSuqyoxYTKiW2ie9laDbZ1iM1YGUA
FIAP & FIGL Data recovery
https://docs.google.com/document/d/1RWQ2gvcuVVPAHMlYB1SFCtjPZfHtWM9tsIlblghbfkE/edit
Planned Evolution
No planned evolution