General presentation

Objective of the application

ARTIST tool is composed of 3 BW reports whose aim are to:

• Provide to GBU’s Product Stewardship team automatically updated relevant reports (volumes + REACH data), intended to facilitate REACH audits performed either internally or by national enforcement bodies

• Help GBU’s Product Stewardship team to identify easily missing REACH information on Raw Materials and on Marketed Products which are bought or manufactured, based on effective volumes and flows

• Consolidate in SAP-EHS (CG02 transaction) all information giving the REACH regulatory status for any Solvay substance, especially :

• Legal Entity
• CLP notification
• REACH pre-registration and registration status

            as well as basic REACH information of couple (Raw Materials;supplier).

It is based on data coming from RCS only.

Wiki EHS support: https://sites.google.com/a/solvay.com/wiki-ehs-support/wiki/reach-reports-artist

Reporting Coordinator: Célia Gonzalez-Roux

Usage information

Around 80 users.

History

The application was released in 2013.

Roles & Access

Roles and access

Authorization objects

Link to the BW Catalog of role

https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I

Dataflow overview

DSO_EHS5 contains the compositions by regulatory list and material. For this application, only the REACH list is useful.

REACH class data are extracted into BW from RCS via 2 datasources :

• one for mono value (to MD C_OBJEK)
• the other for multi values (to DSO_EHSM).

The data required for the reporting comes from 2 flows : Tracking result and GSV:

• Tracking Result flow loads data from SVT application. These data are enhanced with REACH classes properties (mono and multi values) and the neutralized production.

           The neutralized production is extracted from an APD into the DSO DSR_IC02.

• GSV flow comes from Purchasing old application using an APD. These data are enhanced by

           - the REACH List (DSO_EHS5)

           - REACH class properties (on PURE_SUB and PROD_COM)  

Scenario definitions:

Source DSO / Regulatory list / Scenario matrix:

Reporting documentation drive folder:

https://drive.google.com/drive/folders/1q4MyJWaSs2FJLrboGIhq-zMvWaCUARPT

Functional and Technical rules

Rules & Explanations

Master data

Reach class (C_OBJEK) :

It corresponds to the instances of the REACH properties class for a substance. Attributes correspond to REACH properties with mono value or to information about the instance (Validity area, rating,...) or about the substance (Specification, specification type...). 

The extraction of the Reach class characteristics with mono value is done by the datasource 1CL_OEHS002 generated via transaction CTBW, with « basis » datasource 0EHS_PHRASE_ATTR.

 Warning : the client must be set manually in CTBW in every system.

The datasource was enhanced with the following fields via user exit (include ZXRSAU02). They are retrieved from tables ESTRH (EHS: Specification Header), ESTVA (EHS: Value Assignment) and ESTDU (EHS: Additional Information: Value Assignment Usage). 

Phrases extraction (texts of the classes characteristics) :

One datasource DTS_ZBW_ESTPP is used to update the texts of the EHS properties. The InfoObjects concerned are:

• C_OLDVAL "Old value" (do not concern this application)
• C_NEWVAL "New value" (do not concern this application)
• C_EHSRMK "Remarks"
• C_EXREASN "Reason of exemption"
• C_NOTEXR "Reason of notif exemption"
• C_TYPEDOSS "Type of dossier"
• C_USAGEJP "Product Usage Code"

The texts of the InfoObjects are updated only if the SID already exists.

The datasource DTS_ZBW_ESTPP is based on the view ZBW_ESTPP between ESTPH (EHS: Phrase Header) and ESTPP (EHS: Phrase Item).

It is restricted to the customer phrase library (ESTPH-catpin= "CUST") and to english texts (ESTPP-langu = "E"). 

Specifications (0EHS_SPEC) :

Cf. EHS documentation (Enhancement of datasource 0EHS_SPEC_ATTR : field « ZZNUNCE » (EC number) by look up in table ESTRI)

Country key (0COUNTRY) :

The field C_ZREACH is updated by flat file (list of the countries concerned by the REACH regulation).

Master data manually updated (Maintain master data):

- C_TONBDPR (Pre-Regist. Tonnage band)

- C_TONBDR (Registration tonnage band)

Transactional data

Extraction of REACH class characteristics – multi values:

REACH properties with multi values are extracted via the datasource DTS_ZBW_AUSP based on the view ZBW_AUSP between AUSP and CABN.

If there are 3 characteristics with multi values, the datasource provides data like this :

In the transformation to update DSO DSO_EHSM, a line is created for each possible combinations in order to be able to filter on these elements:

Then the following fields are updated by look up to the MD C_OBJEK :

Analysis Proces Designer:

• Purchasing data

An APD is used to extract Purchasing data (APD_DSR_PUR1_0001).

It extracts results of the query BW_QRY_MPR_PUR03_0009, restricted to the countries of the REACH zone, to the DSO DSR_PUR1.

Used fields :

• Neutralized production

An APD is used to extract Production data (APD_DSR_IC01_0002).

It extracts results of the query BW_QRY_MPR_IC001_0036, restricted to a list of company codes, to the DSO ODS_IC02.

Used fields :

REACH List DSO - DSO_EHS5 (CCRCT_EHS_COMP Data):

It contains compositions depending of the regulation. For the REACH regulation, the composition is the “REACH list”, for the ZCLP_REACH, it is the legal composition with validity area “EU” or the standard composition.

Datasource DTS_CCRCT_EHS_COMP based on the table CCRCT_EHS_COMP (EHS: Composition (EH&S Data)).

Data on the current year are deleted in the DSO DSO_EHS5 before a new load. Exceptional historical year reloading can be done using 2 parameters in C_GLBFILT:

• Stream = “EHS”, rule = ”DSO_EHS5”, c_glbfilt = “1” => put C_LOW = “Y” and C_ACTIVE = “Y”.
• Stream = “EHS”, rule = ”DSO_EHS5”, c_glbfilt = “2” => put the year to reload in C_LOW (example “2015”) and C_ACTIVE = “Y”.

Special rules are applied for Japan regulation but it does not concern this application.

Tracking result flow (DSO_EHS7 -> DSO_EHSN):

DSO_EHS7 contains data from SVT tables in RCS:

• CCRCT_PR (EHS: Confirmed Produced Quantities)
• CCRCT_PU (EHS: Confirmed Purchased Quantities)
• CCRCT_PUPL (EHS: Planned Purchased Quantities)
• CCRCT_SO (EHS: Confirmed Sold Quantities)

The 2 lasts year are deleted in DSO_EHS7 then reloaded every day. Infopackages are not used but DTP with “direct access to datasource”.

Only data from the last loading into DSO_EHS7 are required for this application (filter on C_CUR_REC = « X »).

DSO_EHS7 contains all EHS regulations, only REACH and ZCL_REACH are required for the application (filter on C_REG= « REACH » & « ZCLP_REACH »).

The lines on the regulation « REACH » are deleted for the scenario « IMP ».

Enhancement with REACH class properties : each line from DSO DSO_EHS7 is duplicated for each line in DSO_EHSM (REACH characteristics -multi values) and C_OBJEK (REACH characteristics -mono values) on the same tracked substance (C_TRAKSUB) and the same company code (COMP_CODE).

That means that one line is duplicated for each REACH class instance and each combination of characteristic values for the tracked substance.

The neutralized production and consumption is retrieved in the DSO DRS_IC02 at a level material / plant / year and split to each line according to the percentage of the substance in the composition (ehs_spma).

GSV flow (DSR_PUR1 -> DSO_EHSN):

Scenario determination: if the country of the supplier (vendor__country) is in the REACH zone (C_ZREACH = « X ») => scenario = « PUR », else => scenario = « IMP ».

Look up in DSO DSO_EHS5 to split the receipt quantity on the Pure substances according to percentage usage on the REACH List.

Enhancement with REACH properties like in the tracking result flow. This enhancement is done for the REACH properties of the Pure Sub and also for the REACH properties of the prod com.

If there is a REACH property maintained in the Prod com (mono value or multi values), a line is created on the Prod com (specification number of the prod com is determined from the attribute c_prod of MD C_MATNR) with the total receipt quantity and the specification type “PROD_COM” (however, even so there is no REACH property for the Prod com, a line with the specification type "PROD_COM" is created during cube CUB_EHS04 update, once for every Prod com). 

The DSO DRS_IC02 is read but apparently for nothing because the neutralized production seems never added in the result package.

Prod Com Source (C_PRCOSRC) mapping:

• If source DSO is DSO_EHS7, C_PRCOSRC = "DSO_EHS7"
• If source DSO is DSR_PUR1:
  - C_PRCOSRC = " DSO_EHS5" for all lines split on the substances of the REACH list
  - C_PRCOSRC = "C_MATNR" for the line at PROD_COM level 

Counter (C_COUNTER):

A counter is incremented for each line inserted into the cube CUB_EHS04. It used in the BEx queries in order to not sum some ratios during aggregation.

Dependencies with other applications

DSO DSO_EHS5 is also used by the SVT application.

Data loadings

Info providers and objects loaded

Main Process Chains

Average performance

Record Keeping

Reporting

Queries End User Documentation

Training on each ARTIST queries are available here: https://sites.google.com/a/solvay.com/wiki-ehs-support/wiki/reach-reports-artist

Main queries

All queries are available under the Role Menu "EHS - Environment, Health and Safety - REACH" (ZR_RCS_CA_M38)

Report 1: "REACH: Substances prod. & Imp. with compo" (BW_QRY_MPR_EHS06_0001)

The report lists REACH properties of the tracked substances which have been (pre)registered by Solvay: (pre)registration number, registration date, tonnage band, type of dossier, …

=>   restriction  on the regulatory list "REACH".

Substances concerned by this report are the ones produced by Solvay or imported with a composition=>  restriction on all scenario except "PUR".

It displays REACH data maintained at PURE SUB level in RCS => restriction on the specification type (0EHS_SUBC <> "PROD_COM" and C_OBJEK__0EHS_SUBC = "PURE_SUB" and "#" and C_PRCOSRC <> "C_MATNR").

It offers the following key figures:

• the quantity of the substance, converted in unit "TO" using the conversion type "QCT_CMALL". 
• the percentage of the susbtance in the REACH list, with exception agregation "first" for the reference characteristics "c_counter"  (keep the value of the first line when lines are agregated).
• the neutralized production, converted in unit "TO" using the conversion type "QCT_CMALL". There is an  exception agregation "first" for the reference characteristics "c_counter".
  
  

Report 2: "REACH: Purchase in REACH zone and imports with OR" (BW_QRY_MPR_EHS06_0002)

The report lists REACH properties provided by Solvay suppliers on the Prod com: (pre)registration number, registration date, external only representative, type of dossier, …

=> restriction on the regulatory list "REACH"

  Substances concerned by this report are the ones purchased in REACH zone or the ones imported from a supplier with an Only Representative in EU => restriction on all s cenario except "PRO" and "SO" and on External only representative (C_OBJEK__C_OR_EXT) = "YES", "Not applicable" and "#".

  It displays REACH data maintained at PROD_COM level => restriction on the specification type (0EHS_SUBC = "PROD_COM" and C_PRCOSRC = "C_MATNR" )

  Only one ratio: the quantity of material, converted in unit "TO" using the conversion type "QCT_CMALL" .  There is an  exception agregation "first" for the reference characteristics "c_counter".

Report 3: "CLP notif: Substances prod. & imp. with compo" (BW_QRY_MPR_EHS06_0003)

This report lists REACH properties of substances which have been notified by Solvay: notification number, submission notification number, reason of notification exemption …

=> restriction on  the regulatory list "ZCLP_REACH"

Substances concerned by this report are the ones produced by Solvay or imported with a composition => no restriction on the scenario

It displays CLP data maintained at PURE SUB level => restriction on the specification type (C_OBJEK__0EHS_SUBC = "PURE_SUB" and "#").

It offers the following key figures:

• the quantity of the substance, converted in unit "TO" using the conversion type "QCT_CMALL". 
• the percentage of the susbtance in the legal composition (validity are = "EU") or standard composition, with exception agregation "first" for the reference characteristics "c_counter"  (keep the value of the first line when lines are agregated).

Broadcast

No

Maintenance

Known bugs

Recurring procedure

Planned Evolution

Purchasing data flow should be reviewed (SPRINT project?)