ARTIST tool is composed of 3 BW reports whose aim are to:
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
Around 80 users.
The application was released in 2013.
| Role Code | Role Description | Explanation |
|---|---|---|
| ZR_RCS_CA_M38 | EHS Reach - ARTIST Reports | Role menu for ARTIST reports |
| ZBI_RCS_EHS_A02 | Environment, Health and Safety Analysis - End User Role | Application Role:
|
Link to the BW Catalog of role
https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I
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 :
The data required for the reporting comes from 2 flows : Tracking result and GSV:
The neutralized production is extracted from an APD into the DSO DSR_IC02.
- the REACH List (DSO_EHS5)
- REACH class properties (on PURE_SUB and PROD_COM)
Scenario definitions:
| IMP | products or substances imported into REACH zone |
| PUR | products or substances purchased inside REACH zone |
| PRO | substances produced |
| SO | intercompany substances delivered directly to customer |
Source DSO / Regulatory list / Scenario matrix:
DSR_PUR1 | REACH | IMP |
DSR_PUR1 | REACH | PUR |
DSO_EHS7 | REACH | PRO |
DSO_EHS7 | REACH | SO |
DSO_EHS7 | ZCLP_REACH | PRO |
DSO_EHS7 | ZCLP_REACH | SO |
DSO_EHS7 | ZCLP_REACH | IMP |
Reporting documentation drive folder:
https://drive.google.com/drive/folders/1q4MyJWaSs2FJLrboGIhq-zMvWaCUARPT
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:
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)
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 :
Instance | Multi values fields | ||
C_OBJEK | Field 1 | Field 2 | Field 3 |
A | a1;a2 | b1 | c1;c2;c3 |
B | a1;a3 | c4 | |
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:
Instance | Multi values fields | ||
C_OBJEK | Field 1 | Field 2 | Field 3 |
A | a1 | b1 | c1 |
A | a2 | b1 | c1 |
A | a1 | b1 | c2 |
A | a2 | b1 | c2 |
A | a1 | b1 | c3 |
A | a2 | b1 | c3 |
B | a1 | c4 | |
B | a3 | c4 | |
Then the following fields are updated by look up to the MD C_OBJEK :
Analysis Proces Designer:
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 :
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:
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:
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:
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.
DSO DSO_EHS5 is also used by the SVT application.
Main Process Chains
| EHS Daily Transacional | EHS_TRANSACIONAL | SLAVE |
|
|
| EHS - ARTIST - Weekly - Transactional | ZEHS_ARTIST_TRANSAC | MAIN |
|
|
| EHS Dailly MD Attributes | EHS_MASTER_DATA | SLAVE |
|
|
| EHS Daily MD Text | EHS_MASTER_DATA_TXT | SLAVE |
|
|
if possible, give some information on average process chain duration, amount of data loaded and total data volume example: daily process chain loaded in 30 min, weekly chain loaded in 1h15, with around 2k to 10k lines in DELTA mode for a total of 10M lines in cube. The purpose is to give a general overview of the volume of data managed y the application
| Key Figure | Estimation |
|---|---|
| ~ Average Process Chain Runtime | |
| ~ Average nb of rows loaded per load | |
| ~ Total nb of rows loaded (if full) | |
| ~ Average Runtime for 10k lines |
Give details if any historisation is done, example: keep only data greater than beginning of Y-3
Training on each ARTIST queries are available here: https://sites.google.com/a/solvay.com/wiki-ehs-support/wiki/reach-reports-artist
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:
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:
No
Give the list and explanation on the known, not-solved, bugs.
List recurring procedures
Purchasing data flow should be reviewed (SPRINT project?)