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. 

Access Management 

DataFlow

Overview

Reporting documentation drive folder:

https://drive.google.com/drive/folders/1aV0_djwZWywMJWPVg-yClLx2JdiqJuwy

Detailled FIAR data flow:

https://docs.google.com/a/solvay.com/presentation/d/1AwviCF1m66qqSNWWwUtp7sQdG1itGBuF0m8x7Wq9vz4/edit#slide=id.p13

Detailled FIAR Factoring data flow:

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

Technical Rules on Workbench

Systems involved

• Rhodia ERP WP1

• Solvay ERP PF1 (020)

• CICC ERP PI1

• Acetow ERP RHO

• PRS ERP PF1 (050)

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

We use same conversion rate as Working Capital.

Main objects

Sub-Activity 0G_CWWE01:

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.

Company code C_COMPCDE:

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. 

Customer number C_CUSTID:

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. 

Customer Number Compounded with Company Code C_CUSTCOM:

Several attributes are informations on reverse factoring for full factoring customers (attributes C_FF*), used for Credit Management - Anticipated payments report.

These attributes come from PI1 table Z3F_FA_REV_CUST which contains PRS company code and PRS customer code. PRS code are transcoded into local code in the transformation in BW.

To determine the local company code (and source system), the method  GET_AFF_COMPCDE_FROM_COMPPRS of the class ZZF_CL_FACTORING_TOOLS is used. 

In order to determine the local customer code, MD c_custid is read on the attribute c_custpr, for the source system of the company.

Reclassification of Sub-Activity C_SUBACT2:

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.

DBFIAR01 / 02 & 03 rules:

[ITC] Technical Specification Cockpit - FIAR

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 (DPFIAR03):

• the before CAMS project flow (to DBFIAR03) which concerns the documents not assigned to a factoring contract version "2"

  the technical rules are described in "CICC legacy rules (except Factoring flows)" part

• 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" part

Full factoring introduced the notion of "Affiliate" and "Legal" objects.

• "Affiliate" corresponds to data issued from the original FI document in ERP (WP1, PF1 or RHO)

• "Legal" corresponds to data issued from the FI document in CICC.

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

Specific business rules

Factoring Contract number determination:

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. 

CICC legacy rules (except Factoring flows):

Transformation Transf: DPFIAR03 -> DBFIAR03

• A Full DTP was created in order to reprocess the bill of exchanges every day (restricted to 2 years, manage in c_glbfilt FACTORING/ BOE_PER_D => around 5000 lines to process) → DTP: DPFIAR03 -> DBFIAR03 - Full Bill of Exchange.

• 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

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 DPFIAR03 -> DBFIAR03 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 DBFIAR03 (and that it has to be taken into account by the solutions based on this DSO).

FIAR Factoring data flow:

"Affiliate" and "Legal" InfoObjects:

Factoring flows DSO and cubes use the notion of "Affiliate" and "Legal" objects.

• "Affiliate" fields are identified by the suffix "AF" (for instance, the affiliate company is C_COMPCAF) and correspond to data issued from the original FI document in ERP (WP1, PF1 or RHO)

• "Legal" fields correspond to data issued from the FI document in CICC. It is usually non suffixed objects but when it was necessary to use a new object, it was suffixed by "LG". For instance, in the multicubes, 0LOGSYS and C_COMPCDE are identified to the Affiliate objects C_LGSYSAF and C_COMPCAF, but, as the legal company is also required in the multicube, it was necessary to use new objects C_LGSYSLG and C_COMPCLG for the "legal" objects in the DSO/cube instead of 0LOGSYS and C_COMPCDE.

• "Affiliate" and "Legal" InfoObjects are always in reference to the "normal" InfoObject.

• When we create a "legal" or "Affiliate" Infoobject, all compounded Infoobjects must also be "legal" or "Affiliate" infoobjects. For instance, C_COMPCLG is necessarly compounded with C_LGSYSLG as 0LOGSYS is identified by C_LGSYSAF in the multiproviders.

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

• GET_AFF_COMPCDE_FROM_COMPPRS : method used to determine the ERP company code from MD C_COMPCDE from a PRS company code and a landscape.

• GET_FACTORING_VERSION: return a version  number  based  on  the contract  number. We will use the number range to know which version is used.

  • Versions : 01 : WARP or before

  • 02 : Full factoring

Data Propagation Layer (DPFIAR03) updated from DPFIWC03 (ZZF_BSEG CICC table):

Transformation ODSO DPFIWC03 -> ODSO DPFIAR03 (end routine):

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

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

Business Transformation Layer - common rules:

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

Business Transformation Layer - landscape dependent rules:

• 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. When the original FI document is transferred into CICC, the clearing document (doc type DS or DC for Acetow) is also assigned to the factoring contract number and, so, must be excluded from the look up.

          Due to merge without DTO (which means that ERP FI documents transferred to CICC are not duplicated into the new company when merging operations), the current Affiliate company code is different of the one in the original document. The reporting must be based on the current Affiliate, but we also offer the possibility to display the original company code. So we have two different notions of Affiliate company code in the flows:

- the current Affiliate Company code (C_COMPCAF): derived from ZZF_BSEG-ZZ_BUKRS (using the method get_aff_compcde_from_compprs of the class ZZF_CL_FACTORING_TOOLS). If C_COMPCAF can't be determined, the error message " No correspondance in c_compcde for PRS code: " is raised in the DTP monitor.

- the original Affiliate Company code (C_COMPOAF): retrieved from the original document (and should be same than if it was derived from ZZF_BSEG-ZZ_BUKRS_ORIG). In end routine of the transformations TRCS IB_FIAR_02 -> ODSO DBFIAR15 & TRCS IB_FIAR_02 -> ODSO DBFIAR16, fields "AF" are updated with work area "w_orig_doc_char_targetname":

Warning: the current solution can't handle merge without DTO if the landscape of the new company is different than the original one (for instance, merge a company in WP1 into a company in PF1). 

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.

Another simpler solution was considered, it consisted to update a timestamp in the Data Propagation layer in order to reprocess the documents. However, the chosen solution enables a better segmentation in the Process Chains (the Data Propagation layer is common but Business Transformation layer is split by landscape). 

Business Transformation Layer - records deletion and recordmode management :

• In the first transformation :

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").

• In the last transformation :

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.

SD KPI

Blocked orders: (MVFIAR03)

Query BW_QRY_MVFIAR03_0002 used in workbook BW_WBK_ITC_0001

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

Credit Settings (MVFIAR04):

Query BW_QRY_MVFIAR04_0001 used in workbook BW_WBK_ITC_0005

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

GBU Delegation Matrix (C_GBUDLG - IFRFIAR01) :

Query BW_QRY_IFRFIAR01_0001 in workbook BW_WBK_ITC_0004

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

    • Managed in PRS at customer group level

Credit Exposure:

Query BW_QRY_MVFIAR01_0008 used in workbooks:

BW_WBK_ITC_0003
BW_WBK_ITC_0004
BW_WBK_ITC_0018

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

Reporting

All the reporting is available throught workbooks in the role "ITC - Credit Management" ZR_RCS_CA_M39

Queries:

Workbooks:

 There are 2 difficult workbooks

• Credit Management Workbook

• Process Expert Workbook

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/file/d/1x8WtkIxtIWr2-pYgkw8PXh_X6yDsKcug/view

Query documentation:

https://drive.google.com/a/solvay.com/?tab=mo#folders/0B_p_Afe8sjVlS1NLLW9maEV5ZEU

Main queries

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. 

Dependencies with other applications

WC applications is dependant with

• WCAP Working Capital (FIAR flow)

• FIAR Account Receivables (FIAR data source)

Data Loading

Info Providers and objects loaded 

Detail of process chain, list + link between or special event done for the loading

Knowledge transfert documentation

Data Quality Control

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

Operational Documentation

Procedures

<Describe the recurring procedures needed to operate the application (eg. start/pause/terminate/restart the app processes, data preparation, data ingestion, ETL, data visualization, data export, other manual activities)>

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

No known bug but we have frequendly asked questions

-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

• 2470: Ask new report

• 2753: Modif in queries

• 5681: Request for new broadcast

Example of UR Footprints

• 346707: Overdue issue

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

Roadmap

<List past & future evolutions for the application (including links to MED/FSD/TSD)>