This page provides all the technical and detailed documentation concerning the ITC (Invoice to Cash FIAR) application in BW, including :
Objective of this application is to provide an unique reporting tool for Credit Management team.
ITC is a solution to measure, analyse and decide based on all Credit data available.
Reporting coordinator is Sandrine Micollet.
Tool leader is François Rublon.
Around 220 users have access to ITC applications. Those users are worldwide and most of them are controllers.
Project done between 2013 and 2015.
Role Code | Role Description | Explanation |
|---|---|---|
| ZR_RCS_CA_M39 | ITC - Credit Management | Role menu |
| ZBI_RCS_FI_A27 | ITC Credit Management Analysis - End User role | End user role |
Link to the BW Catalog of role
https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I
Reporting documentation drive folder:
https://drive.google.com/drive/folders/0B_p_Afe8sjVlUWNqbnAyejIwSXM
Query documentation:
https://drive.google.com/a/solvay.com/?tab=mo#folders/0B_p_Afe8sjVlS1NLLW9maEV5ZEU
Workbooks description (made by Credit Management Team):
https://docs.google.com/a/solvay.com/file/d/0B_p_Afe8sjVlOVl1M214ckFfMm5KUWQ5OVBiN2NISE1YTDdF/edit
Detailled FIAR data flow:
Detailled FIAR Factoring data flow:
https://drive.google.com/open?id=11SNCrHyxE1gkEOBKOK_cKTlfpuES121IaulIw1hgGLk
Rhodia ERP WP1
Solvay ERP PF1 (020)
CICC ERP PI1
Acetow ERP RHO
PRS ERP PF1 (050)
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.
We use same conversion rate as Working Capital.
The main object for business structure (BFC GBU for example) is the Sub-activity 0G_CWWE01.
Before ITC project, it was labelled IECRA. Now it containes IECRA values for WP1 and also Business area for other systems.
We use for company code the object C_COMPCDE. It's compounded with the source system.
The attributes of C_COMPCDE are read from C_COMPPRS master data (loaded from PRS system).
In the reporting, we use this master data to retrieve the percentage of contribution and to identify the Intercompany.
We use for customer number the object C_CUSTID. It's also compounded with the source system.
The attributes are read from C_CUSTPRS but some are loaded from the ERP systems.
The object C_CUSTPRS is also stored in the info providers but is not used anymore. At the beginning of the project we used C_CUSTPRS in order to use the attributes of PRS.
But due to bad data quality we oftenly had the case of missing link in the ERP between ERP number and PRS number. To avoid that, we decided to retrieve PRS attributes in C_CUSTID and to use only C_CUSTID.
So in case of update in the ERP, we are sure to have dynamism.
In the ERP, when a IECRA 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.
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 (DPFIAR03):
Full factoring introduced the notion of "Affiliate" and "Legal" objects.
In ITC multiproviders, the existing objects are usually identified to the "Affiliate" objects and some new objects where added to enable "legal" reporting. This way, the impact on the reporting was significantly lower than if the existing object for the company code (or other Master Data linked to the source system) was identified to the "Legal" company code.
Some more informations on full factoring (CAMS project):
https://drive.google.com/open?id=0B2w5MF8sg0HxMzJTRXFVOWZLem8
The factoring contract number is updated into FIAR Business Transformation Layer DSO from ZZF_BSEG DSO (DPFIWC*). This information is primordial for the CICC factoring flows in order to retrieve the original document.
An error stack is managed in order to reprocess entries when the FI document line item could not be found in the target DSO. It ensures to update the factoring contract number without manage it also from the FIAR Datapropagation Layer. If there is a synchronization error, so, it should be fixed during the next run. That is why the delta DTP is using "Update valid records, reporting possible (request green)" mode. The error DTP is also set to "Update valid records, reporting possible (request green)" so the error stack needs a regular monitoring in order to prevent an error to stay unnoticed.
Affiliate management (company of origin for FI document managed by CICC) : C_CMAFFIL
Loaded in one shot from flat file in DBFIAR05
If doc header text begins by ‘99999’
If characters 6 to 9 of doc header text is a company (check in C_COMPCDE)
=> C_CMAFFIL = this company code
Read DBFIAR05 for CICC company and Customer code to determine C_CMAFFIL
If doc header text doesn’t begin by ‘99999'
Read DBFIAR05 for CICC company and Customer code
Factoring flows DSO and cubes use the notion of "Affiliate" and "Legal" objects.
Most of the transformations used methods of the class ZZF_CL_FACTORING_TOOLS (Tools for the factoring process).
The main methods are:
Update of existing entries in DPFIAR03 only (no creation of new lines)
The factoring contract, Affiliate PRS company code are updated directly from the source
The factoring version is derived from the factoring contract number
An error stack is updated for the following cases:
1) The line item in DPFIWC03 can't be found in DPFIAR03 => no update in DPFIAR03 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 DPFIAR03 (that means that the factoring contract number and factoring version are updated in DPFIAR03 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.
Rules in common for all landscape (DPFIAR03 -> DBFIAR15/16/17) should be maintained in the transformation between the infosources "IB_FIAR_03" and "IB_FIAR_02".
Enhancements using data in AR ERP DSO:
The logical is similar for every landscape, however, the DSO to read the Affiliate data and the fields to retrieve are different.
The original FI document item is read using the factoring contract number.
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
We keep only the last image of a FI doc item, in consequence, all records for the same doc item must be gathered in the same packet using semantic group in DTP.
=> To not use key figures in "summation" in the transformations!
In order to handle contract number deletion or landscape change, if the last image of a FI doc item in the packet is not recordmode "N" or "", that means the item should not be in this flow anymore, so, we flag it for deletion (recordmode set to "D").
We delete records in the target if not on the good lanscape or factoring version.
The delta DTP is filtered on the landscape and factoring version, so it will not occur in a "normal" loading, however, this will help ensuring no data on bad landscape is loaded using incorrect filters in DTP.
It also allows simple deletion of records in the target if the landscape/contract has changed and the delta for the deletion was skipped for any reason.
This is done in the rule group "Technical Group" of the transformation.
An error stack is updated when the document can’t be found in ERP DSO (if error handling mode field = “1”). Error handling mode field (C_ERRORM) is different when data is loading from the CICC Data Propagation layer than when it comes from ERP DSO where there is no need to put entries in the error stack as the document processed by this flow is necessarily found in DSO ERP (or the contract number was changed and the old contract is brought by the reverse image, however the document will be reprocessed from the CICC Data Propagation layer).
The error stack is reprocessed at every loads.
To not forget that when we delete an error DTP request in a target, it will cleared the entire error stack, so we should also delete all the "normal" DTP requests that brought the data into the error stack or manually reprocessed the entries that were into the error stack.
Entries should not stay more than once into the error stack else, something must be wrong and an analysis must be done.
Existing solution for Rhodia legacy not modified
Specific tables created in ERP systems so new solution
Very simple data flow without any difficult rules inside
Was modified by another project after
KPI to know if the customers is review automatically or manually
Only CICC system PI1
Two data sources but ZZF_BW_CREDIT_SETTINGS is obsolete
Need has changed during the project
Very simple data flow : 1 DSO Propa & 1 Multiprovider
Sum of outstanding and open sales order, open delivery and open billing
For all systems (Rhodia, Solvay and Acetow)
Comparison with credit limit
Managed in CICC at customer level
Master data C_CREDEXP / DSO DBFIAR07
Included in FIAR data providers
Outstanding calculated in FIAR data flow
Credit Exposure into DBFIAR07
Sum of credit exposure from all systems (customers can have exposure in all systems)
Delete DSO before loading
Credit Exposure into into C_CREDEXP from DBFIAR07
Master data loaded from itself to set attributes to 0
Credit limit loaded into C_CREDEXP
DBFIAR12 to have credit exposure evolution
Weekly loading
Loading is too long to be daily (1h)
Comparison with ERP (FD33) impossible : Too much changes in the ERP to have same key figures in BW
WC applications is dependant with
List on info providers inside the technical cockpit.
Around 20min for all systems.
We keep all records.
All the reporting is available throught workbooks in the role "ITC - Credit Management" ZR_RCS_CA_M39
There are 2 difficult workbooks
Those workbooks are difficult because they contain a lot of sheets using many queries. So be careful when you modify it and don't hesitate to create back-up !
Workbooks description (made by Credit Management Team):
https://docs.google.com/a/solvay.com/file/d/0B_p_Afe8sjVlOVl1M214ckFfMm5KUWQ5OVBiN2NISE1YTDdF/edit
Functional description of the queries (made by Credi Management Team):
https://drive.google.com/open?id=0B_p_Afe8sjVlYXYtLXFWNGJuMTQ
There is a lot of queries but the main one is:
BW_QRY_MVFIAR01_0001 BW - Credit Management DSO (Core Query)
This query contains more than 200 key figures !
When possible, queries use structures for key figures.
Jump query available
No broadcasts
No known bug but we have frequendly asked questions
-About the BW Accelerator: When a cube is not in the BW Accelertor (for technical issues), reporting performance becomes very bad and users warn us about this. So we have to check if the cubes are well in the BW Accelerator and make the appropriate corrections.
-Details of key figures by FI Document. In ITC it has not been requested to provide a detailled reporting of key figures by FI document. When an user wants to know which FI documents is contained in the KF, we have to do it manually in the info providers by reproducing filters in the query.
Example of Freshdesk tickets
Example of UR Footprints
-Optimization of FIAR start routine between propagation and business layers could be optimized:
The code is correct but we use a FOR ALL ENTRIES not using the key of the info object.
We should also use C_CTR_AREA to make the code more efficient.
No procedure
No planned evolution
