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. 

1.0 Access Management 

2.0 DataFlow

2.1 Overview

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:

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

Detailled FIAR Factoring data flow:

https://drive.google.com/open?id=11SNCrHyxE1gkEOBKOK_cKTlfpuES121IaulIw1hgGLk

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

Rhodia legacy rules:

• Initial loading was done from existing DSO ODS_FI01 and not from WP1 system due to archived document not loadable anymore. It was also much faster

• The reclassification is done only in DBFIAR01, not in the propagation layer.

• We copied all existing rules applied in ODS_FI01. Maybe some them were obsolete.

• Some part of receivables must be considered as payables (0gl_account__c_gl_type = "PAYABLES" as account Z001/2100020000 for instance) in the Working Capital (in order to be aligned with PF1). These line items have the same structure as other receivables, they are assigned to an IECRA which is used to determined the GBU. However, payables must be followed by BFC activity 2 which can't be determined from the IECRA =>that is why the object c_subact2 was added in the this flow, to be mapped from the profit center for accounts to be considered as "payables" only.

• In DSO Business Layer (DBFIAR01), we determinate IECRA with those rules:

  • If data found in table ZWFAT205 (in BW DSO_FI11) and CDSA and DIVISION are informed, IECRA = IECRA from CDSA and division from ZWFAT205

  • Else if data not found in ZWFAT205, and CDSA and DIVISION are informed, IECRA  = IECRA from CDSA and division sent by Datasource 0FI_AR4. Here we search too in DSO DBFIAR25 if there is an IECRA to be replaced (for Pax Historical Data); if yes, IECRA = IECRA found in DSO DBFIAR25. 

  • Else if data found  in table ZWFAT205, IECRA = IECRA from ZWFAT205

  • Else IECRA  = IECRA from Datasource 0FI_AR4. In this case we search in DSO DBFIAR25 if there is an IECRA to be replaced (for Pax Historical Data); if yes, IECRA = IECRA found in DSO DBFIAR25.

  Those rules are applied on 2 transformations: from DSO DPFIAR01 (Propagation layer) to DBFIAR01, and loading of DSO DBFIAR01 on itself.

More details on IECRA Substitution done for PAX on FIAR flow:

PAX - IECRA substitution done on FIAR

Solvay legacy rules:

• Initial loading was done from PF1 system

• Business rules applied in end routine (easier to understand and maintain) : Reclassification by function module with data source ZZFCM_BU_EXCEPTIONS_NEW and DBFIWC01, fill info objects by reading master data

CICC legacy rules (except Factoring flows):

• Initial loading was done from PI1 system

• Business rules applied in end routine (easier to understand and maintain) : Reclassification by function module with data source ZZFCM_BU_EXCEPTIONS_NEW and DBFIWC01, fill info objects by reading master data

• A specific rule to determine the sub-activity for Bill of Exchange (BoE) for companies 0231 and 4044 (CICC factor companies): 

  BoE can be associated to multi factoring contract, so, no factoring contract could be filled in ZZF_BSEG for the document, in consequence, the link with the affiliate invoices and so the affiliate sub activity is lost. 

  To avoid a split mechanism, it was chosen the following solution: the amounts of all AR invoices cleared by the Bill of Exchange are totalized in absolute value by affiliate sub activity. The sub activity with the largest amount is then assigned to the BoE. 

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

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

Acetow legacy rules:

• Initial loading was done from RHO system

• Business rules applied in end routine (easier to understand and maintain) : Sub-activity from by business area (RHO + business area or constant value), fill info objects by reading master data.

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

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:

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

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

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

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

2.2 Dependencies with other applications

WC applications is dependant with

• WCAP Working Capital (FIAR flow)

• FIAR Account Receivables (FIAR data source)

2.3 Query

Reporting

Queries End User Documentation

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

 There are 2 difficult workbooks

• ITC Credit Management Workbook

• ITC 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/open?id=0B_p_Afe8sjVlYXYtLXFWNGJuMTQ

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. 

Main functionnalities

Jump query available

Broadcast

No broadcasts

Maintenance

Known bugs

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

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

3.0 Data Loading

3.1 Info Providers and objects loaded 

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