Access Management 

DataFlow

Overview

https://drive.google.com/open?id=1vK2YT2eTD6OxhhN5o7keDsc_5d_SBJ8jkI4hvD2G-90

Reporting documentation drive folder:

https://drive.google.com/open?id=0B03hryo6pwqdTUtSSldyOXB0U1k

Technical Rules on Workbench

Assessments dataflow

SAP system logs all the changes that are made within the Specification Management (changes on substances properties, compositions…). This flow extracts these changes, from the table EST_CHANGE_DOCS which updated using the program RC1_CHANGE_DOCS_GET.

Table EST_CHANGE_DOCS contains 2 years of data.

Program RC1_CHANGE_DOCS_GET is not automatically running in non productive systems.

https://drive.google.com/open?id=1CW1ccSYgS_Tjr1eyyZdvZan4v1Pu8tRK_cqc76RgY08

Zone determination (R1)

General case: the zone is obtained from the characteristics (C_ATWRT) attribute.

Special case: if the modification type is "Delete" and the characteritic value is empty, the zone is obtained from the Value Assignement Type (C_ESTCAT) attribute.

PURE_SUB determination when LIST_SUB inheritance (R2)

Some data are managed at LIST_SUB level and transfer to the PURE_SUB by inheritance link. In that case, the modification recovered in EST_CHANGE_DOCS table is at LIST_SUB level but not at PURE_SUB level. 

 In order to recover the LIST_SUB modifications at PURE_SUB level, the inheritance link are extracted into BW (DSO_EHSG). Each modification at LIST_SUB level is duplicated for each corresponding PURE_SUB. The LIST_SUB specification is kept in infoobject EHS_SPEC and the PURE_SUB specification in infoobject C_SPEC2. The flag "Inherited data" (C_SUBFLG) is filled.

Modification type (R3)

The field "Change Indicator Flag" (C_CHIND) is used to know how the EHS data have been modified: deletion, first load (insert), update, inactivation.

The change indicator field (CHNGIND) in source table EST_CHANGE_DOCS is used to determine a different value in C_CHIND, depending of the other lines on the change document:

• Modification type = Insert

If records with TABNAM = ESTCAT have CHNGIND = U, and have a record with TABNAM = ESTDU and CHNGIND = I and with same MASTER_RECN, then modification type field = Insert.

• Modification type = Update

If records with TABNAM = ESTCAT have CHNGIND = U, and do not have records with TABNAM = ESTDU with same MASTER_RECN, then modification type field = Update.

• Modification type = Delete

If records with TABNAM = ESTDU have CHNGIND = D, then modification type field = Delete.

• Modification type = Inactivate

If we have records with TABNAM = ESTDU, FNAM = ACTVFLG, VALUE_OLD = X and VALUE_NEW is empty, then modification type = Inactivate.

Instance ID determination (R3)

The instance ID on the lines concerning changes on characteristics value or usage is not filled in the table EST_CHANGE_DOCS. That is why the instance ID is determined in the extractor from ESTVA table. However, if the instance is deleted, the corresponding record is first flagged as "deleted" in the tables and then is physically removed from ESTVA. In consequence, to be able to retrieve the instance ID when the instance is deleted, we have to find it in the logs concerning ESTVA table changes => this is done by a look up in the propagation layer DSO DPEHS01 where tabname = "ESTVA".

Datasources customer exit:

• DTS_ZEST_CHANGE_DOCS (R4): 
  - determination of the instance (zzord) from table ESTVA. If this instance can't be determined because it was deleted from the table, a look up is done in BW (cf. Instance ID determination (R3))
  - determination of the usages (zzusage) from table ESTDU. If there are several usages, they are concatenated in the same field. The usages are not determined for lines with tabname = "ESTVA" or "ESTCAT" when they are deleted.

Legal and Standard Compositions dataflow (MVEHAD01)

Compositions are extracted using specific datasources based on Infosets. A filter in the Value Assignment Type (ESTCAT) allows to restrict the extraction to Legal (EHS_1012_024) and Standard (SAP_EHS_1012_003) compositions.

Full loading is required because data can be deleted in source system database: deletion flags are filled but database entries can be deleted before the loading into BW so we could miss some deletion if we work with delta.

https://drive.google.com/open?id=1e08i8ajCQmCLKuZqnYCKiu8GBGy3r3VgtuciyP-FAfo

To not delete Data Propagation / Business Transformation Layer DSO content

DSO DPEHS02 and DBEHAD02 content must not be deleted because deleted compositions and specifications are not kept in the source system, so it is not possible to recover historical data.

Historical data is only needed for the DSO DBEHAD02 which is used for EHS KPI.

Inherited compositions

Inherited compositions (from a template specification) are not extracted by the datasource “DTS_EHS_LEGAL_COMPO”. Another datasource “DTS_EHS_COMPO_INHERITED” was created in order to extract these inherited compositions.

No technical rules were given to handle the case of breaking an  inheritance as this case should not occur.

"General" authorization group

ODSO DPEHS034 -> ODSO DBEHAD01 & ODSO DPEHS02 -> ODSO DBEHAD01 & ODSO DPEHAD01 -> ODSO DBEHAD01

If the key of the authorization group  is ZZ_BU*X or ZZ_BU*R, the “general” authorization group is  the authorization group without the last character (example : authorization group = ZZ_BU_01R => “general” authorization group = ZZ_BU_01). We check the existence of the “general” authorization group in MD 0EHS_AUTH.

Else, the "general" authorization group is the authorization group.

Revision documents dataflow

Revision documents data are extracted using a specific datasources based on the Function Module Z_WBW_DTS_EHS_REV_MGMT. It extracts data from tables ESTRH (EHS: Specification Header), ESTVH (EHS: Specification - Value Assignment Type Assignment), ESTVA (EHS: Value Assignment), ESTDU (EHS: Additional Information: Value Assignment Usage), ESTDF (Additional Information - User-Defined Text), CABN (Characteristic) and AUSP (Characteristic Values).

The need for reporting is to see all PURE_SUB even if there is no revision document for the substance. That is why the flow is based on the extractor of the specifications (0EHS_SPEC_ATTR) and that a look up is done in the DSO DPEHAD03 to retrieve the Revision documents data. 

Full loading is required because data can be deleted in source system database: deletion flags are filled but database entries can be deleted before the loading into BW so we could miss some deletion if we work with delta.

https://drive.google.com/open?id=1BOoK5TPF5oIDSi5MdgMhNnObOsi1XxbO2xZO0mxCCZQ

Revision documents data (R1)

The revision documents data are added to the corresponding substances in DBEHAD03 by a look up in the DSO DPEHAD03.

If there are several revision document instances, only data from the last instance are taken.

Report shipping dataflow

SAP shipping process send SDS (Safety Data Sheet) to customers when Rhodia Legacy or Solvay legacy sold a product to them

SDS shipping trigger is the sale order. When a sale order is created, the SDS is sent to customer.

This flow extracts these shipping data from tables CVDDH (EHS: Report shipping orders), CVDDP (EHS: Parameter Values for Report Shipping) and VBAK (Sales Documents).

Full loading is required because data in EHS tables can be deleted in source system database: deletion flags are filled but database entries can be deleted before the loading into BW so we could miss some deletion if we work with delta.

To avoid a daily complete deletion and reloading, a selective deletion of the 12 last months in DSO and cubes is done and only this perimeter is reloaded.

https://drive.google.com/open?id=1A00ZuMNu5dj-061yInW3BoZc9Fo52qdqiaW22zvUJtU

Filter on status (C_ESTATUS) in Business Propagation Layer

Only status required for the reporting are updated:

•  “{8” (Completed)
• “{9” (Historical)
• “{B” (Error found)

Filter on document types (C_EHDOKAR) in Business Propagation Layer

The following type are excluded:

•  “DBE” (Cover Sheet Template)
•  “DBV” (Cover sheet - final version)

Data from sales documents (sales org, sales group, sales office) (R1)

The link between the report shipping order and the sales document exists in table CVDDP (EHS: Parameter Values for Report Shipping).

The sales document is filled in the field VALUE of the table CVDDP lines with ATTRIB = "VBELN".

The datasource DTS_ZEHS_CVDDP_S (resp. DTS_ZEHS_RS_CVDDP for Rhodia) is based on a view on tables CVDDP and VBAK (Sales Documents), restricted on CVDDP-ATTRIB = "VBELN".

Document generation dataflow

Through SAP EHS, a lots of EHS documents can be generated, e.g. SDS (Safety Data Sheet), Label (Sample Label, Transportation Label, GLM Label), TDS (Technical Data Sheet).

This flow extracts data from tables ESTDH (EHS: Report Header) and DRAP (Document Log File).

https://drive.google.com/open?id=1B8-mGCUiSkgz5_fuR7teEYzD_xHmaJcgWbuE3nQwegM

Datasources : 

• DTS_EHS_ESTDH (EHS: Report Header): extracts data from table ESTDH via the Function Module Z_WBW_DTS_EHS_ESTDH. The date of status change ZREPRDAT is obtained by look up in table DRAP.

Master Data dataflow

Specifications (0EHS_SPEC):

Attributes:

0EHS_SPEC attributes are loading from the standard datasource 0EHS_SPEC_ATTR.

The following zones are updated by the customer exit:

• ZZIDTYPE = ESTRI-idtype for ESTRI-langu = "EN". (first value)
• ZZIDCAT = ESTRI-idcat for ESTRI-langu = "EN". (first value)
• ZZNUNCAS = ESTRI-ident for ESTRI-idtype= "NUM", ESTRI-idcat="CAS" and  ESTRI-langu = "".
• ZZNANINT = ESTRI-ident for ESTRI-idtype= "NAM", ESTRI-idcat="Z_INTERNAL" and  ESTRI-langu = "".
• ZZNUNCE = ESTRI-ident for ESTRI-idtype= "NUM", ESTRI-idcat="EINECS" and  ESTRI-langu = "" or for ESTRI-idtype= "NUM", ESTRI-idcat=" CE" and  ESTRI-langu = "" or for ESTRIidtype= "NUM", ESTRI-idcat="Z_LISTNUM" and  ESTRI-langu = "".
• ZZNANPLC =  ESTRI-ident for ESTRI-idtype= "NAM", ESTRI-idcat="Z_PLC" and  ESTRI-langu = "".
• ZZOWNER =   ESTRI-ident for ESTRI-idtype= "NAM", ESTRI-idcat="Z_OWNER" and  ESTRI-langu = "".
• ZZPORTFO =   ESTRI-ident for ESTRI-idtype= "NUM", ESTRI-idcat="Z_PORTFO" and  ESTRI-langu = "".
• ZZOBSFLG = "X" if:
        - ESTRI-ident = "OBSOLETE" for ESTRI-idtype= "NAM", ESTRI-idcat="Z_PLC" and  ESTRI-langu = "".
        - ESTRI-ident = " *OBS*" for ESTRI-idtype= "NAM", ESTRI-idcat="PROD" and  ESTRI-langu = "" or "E".
• ZZCRDAT = ESTRH-CRDAT.
  
  

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"
• C_NEWVAL "New value"
• C_EHSRMK "Remarks" (do not concern this application)
• C_EXREASN "Reason of exemption" (do not concern this application)
• C_NOTEXR "Reason of notif exemption" (do not concern this application)
• C_TYPEDOSS "Type of dossier" (do not concern this application)
• C_USAGEJP "Product Usage Code" (do not concern this application)

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

Characteristic (C_ATWRT)

Attributes:

C_ATWRT attributes are loading from a flat file (DTS_C_ATWRT_FF).

This loading is not scheduled.

The file contains 2 attributes :

• Magnitude Geographie / Zone (C_ZONE): it contains the zone (AP, EU, LA, NA, WW) used for the Assessment report (first tab).
• Assessment report flag (C_ASSRFLG): "X" if the characteristic must be included in the Assessment report.

Flat file directory: https://drive.google.com/open?id=0B03hryo6pwqdbXhJd1VhVXJBbW8

Updated by EHS admin or IT.

Value Assignment Type (CLASS) (C_ESTCAT):

Attributes:

C_ATWRT attributes are loading from a flat file (DTS_C_ESTCAT_FF).

The file loading is not scheduled.

The file contains 3 attributes :

• Magnitude Geographie / Zone (C_ZONE): it contains the zone (AP, EU, LA, NA, WW) used for the Assessment report (first tab) for the modification type "Delete" when the characteristic value is empty.
• Assessment report flag (C_ASSRFLG): "X" if the characteristic must be included in the Assessment report.
• IH Report flag (C_IHFLG): "X" if the characteristic must be included in the IH report.

Flat file directory: https://drive.google.com/open?id=0B03hryo6pwqdbXhJd1VhVXJBbW8

Updated by EHS admin or IT.

Report Generation Variant (C_SBGVID)

Attributes:

C_SBGVID attributes are loading from a flat file (DTS_C_SBGVID_ATTR).

The file loading is not scheduled.

The file contains 2 attributes :

• Magnitude Geographie / Zone (C_ZONE): it contains the zone (AP, EU, LA, NA, WW) used for the Documents generation report.
• Report Type (C_DREPTYP): it contains the doc type (EU Label, certificate, TDS,...) used for the Documents generation report.

Flat file directory: https://drive.google.com/open?id=0B03hryo6pwqdbXhJd1VhVXJBbW8

Updated by EHS admin or IT.

Partner Email information : 

GDPR

The Partner's Email information come from ADR6 table (stored in DSO  DPEHRS07 from WP1 and  DPEHRS08 from PF1), this information is filled only for EHS Partners.

The information is filled by lookup in Report Shipping flow between the Propagation and Business Layer (DBEHRS01 on WP1 and DBEHRS02 on PF1).

The Logic used in the lookup is the following : 

• We read KNVK information stored in DSO  DPEHRS05, to take the Partner information with the condition : CVDDH Receip ID = KNKV Contact Personn.
• The corresponding KNVK lines are used tu read data from ADR6 table with the following condition : 
  • IF KNVK Person Number = ADR6 Person Number AND KNVK Adress Number = ADR6 Adress Number  → Email information filling
  • Else : KNVK Person Number = ADR6 Person Number → Email information filling
  • Else KNVK Adress Number = ADR6 Adress Number  → Email information filling
    

The information is stored in the BW targets below : so all queries created on the BW targets below could integrate the email address data

MVEHRS01 Report Shipping Multi

CREHRS02 Repport Shipping Cube (Solvay)

DBEHRS02  Report Shipping business layer (Solvay)

CREHRS01 Repport Shipping Cube (Rhodia)

DBEHRS01 Report Shipping business layer (Rhodia)

The field imapcted is C_EMAILID E-Mail Address

This development was  done in the scope of the following of SDS reports (Safety Data Sheet) . The report are mandatory in the REACH program (Europeen reglementation) 

SDS reports are  produced either sent to customer legal provision to customer who purchased Solvay products+ we must send the SDS for all the major changes for all the buyer customers in the last 12 months.

The GBU can used the BW report or directy on the ECC system in the mail error address generated each time the sending to the customer is not working, BW reports provide the mail of the customer. (

from PF1 system is customer Sold to and WP1 customer Ship to)

Useful transactions / programs

Reporting

All reports are available via workbooks published in the Roles menu.

Role menu "EHS Reporting - ADMIN Reports" (ZR_RCS_CA_M51)

• Assessment Report

When modifying data in the Pure Substances, it may have an impact on the Safety Data Sheet.
In order to simplify the communication process between the HazCom team and the SDS Writers, a report has been designed in BW that 
1) lists in the first tab the changes made to the Pure Substances. A change can be a deletion, an insertion or an update (in which case the old and new value are displayed)
2) lists in the following tabs all the products that contain these Pure Substances and at which concentration AND that have a released SDS in the zone
Then the Zone SDS Writer can easily identify which products may be impacted by the change and if the SDS needs to be revised accordingly.
This report is only available to the Admin team, it will be sent to the SDS Writers when needed.

 Tab 1:
BEx Query = BW - EHS - Assesment Report (Core Query) (BW_QRY_DBEHS001_0001)
List of EHS classes and characteristics displayed in the report are filtered using the attributes C_ATWRT__C_ASSRFLG and C_ESTCAT__C_ASSRFLG.
 
 Tab 2: 
BEx Query = BW - EHS - Product List (Core Query) (BW_QRY_DBEHS002_0001)
Exception aggregation "First value" for the ratio "Substance % in Material" for the Ref. Characteristic "C_SBGVID".
Filter on substances (C_SPEC2) : replacement path on query BW_QRY_DBEHS001_0001

• Shared Pure Sub
  

 The aim of this report is to see which Authorization group are using which Pure Sub in their Prod com.

The report is based on the Legal and Standard compositions of the Prod com. The Pure substances in the compositions are displayed by the "general" authorization groups of the Prod com.

The ratio "NB Authorization Group" count the number of times that a distinct "General" Authorization group uses the Pure Sub.

BEx Query = EHS - Shared Pure Sub (BW_QRY_MVEHAD01_0001)

Exception Aggregation "Count for all detailed values" for the ratio "NB Authorization Group" for the Ref. Characteristic "C_EHSAUTG".

• Pure Sub with Revision Document

 The aim of this report is to see which Pure Sub have a Revision document in SAP. When a substance has a revision document, some details on the document are displayed.

BEx Query = EHS: Revision Documents (BW_QRY_MVEHAD03_0001)

Role menu "EHS Reporting - SDS Reports" (ZR_RCS_CA_M45)

• EHS Report Generation
  

This report lists all the EHS documents with a released status: SDSs but also labels, transport documents, composition report and certificates.

The status of the product (obsolete or not) is also displayed, which is a good tool to check that we don't keep active documents for obsolete products.

The first tab is a dashboard that gives a summary and the second one provides the details of each document published: which language, created by whom and when. So it is also a good tool to check that there are no SDSs that were created more than 3 years ago.

 Tab 1:
BEx Query = BW - EHS - Spec & reports per GBU (Core Query) (BW_QRY_MVEHDG01_001)
Type of reports is an attribute of the Report generation variant (C_SBGVID__C_DREPTYP).
 
  Tab 2: 
BEx Query = BW - EHS - Report generation (Core Query) (BW_QRY_MVEHDG01_002)
Ratio "Last validation" = current day - generation date.

• SDS Shipping Reporting
  
  

This report lists all the orders that triggered a successful SDS sending or an error. In case of error, the reason is clearly explained: "SDS missing", "Technical error", "Occasional customer".
It is similar to the transaction CVD1 but offers several advantages: search by GBU, easy export to Excel, additionnal data (order number, customer)
Another advantage of having the report in BW is that we included directly some data from the order: especially the order number and customer information. It therefore becomes very easy to check how many SDSs were sent to a particular customer in a given period of time for example.
First tab is an overview page and second tab is a detailed view, where each line corresponds to one sales order.

 
 Tab 1:
BEx Query = BW – EHS - Report Shipping dashboard(Core Query) (BW_QRY_CREHRS01_001)
 
  Tab 2: 
BEx Query = BW – EHS - Report Shipping details(Core Query) (BW_QRY_CREHRS01_002)

Broadcast

IH report

• EHS - IH Report Weekly Broadcast (BD_EHS_001):

Technical name of object = BW_QRY_DBEHS001_0002 

This query is similar to "BW - EHS - Assesment Report (Core Query)" (BW_QRY_DBEHS001_0001) (first tab of the workbook Assessment report) but restricted to some Value Assignment Types (flag c_ihflg = "X") and to the changes that occured the previous week.

Format = XLS_XML

Variable Assignment = no filter in the broadcast settings 

Scheduling = every monday at 8:00am

Destination = Sent by email to Nathalie Berne. 

Documents generation

The documents generation report is split into 5 quarterly broadcasts sent by email to the product Stewards 

• Quaterly SDS generation report to Product Stewards - HazCom (BD_EHS_DG_001), 

• Quaterly SDS generation report to Product Stewards - SSP (BD_EHS_DG_002), 

• Quaterly SDS generation report to Prod. Stewards - Polyamide (BD_EHS_DG_003), 

• Quaterly SDS generation report to Prod. Stewards - Novecare (BD_EHS_DG_004),

• Quaterly SDS generation report to Prod. Stewards - Others (BD_EHS_DG_005):

Technical name of object = 7919WZ35DF7VENEQZTYBT0HBH (BW_WBK_EHS_0001). 

It is an Analyser workbook similar to the Analysis workbook  "EHS Report Generation", only the second tab is not based on the same query in order to filter on the reports that were generated before 2 years ago. The workbook is based on the queries "BW - EHS - Spec & reports per GBU (Core Query)" (BW_QRY_MVEHDG01_001) and "BW - EHS - Report generation (Broadcast)" (BW_QRY_MVEHDG01_003). An exception in the query BW_QRY_MVEHDG01_003 highlights the reports generated more than 3 years ago.

Format = XLS

Variable Assignment = filter on a list of Autorization groups (the list is different for each Broadcast)

Scheduling = every quarter, executed via the Process Chain PC_EHS_DG_BCAST_001.

Destination = Sent by email to EHS HazCom support and a list of product stewards (the list is different for each Broadcast). 

Dependencies with other applications

DSO DPEHKP01 and DBEHAD02 are used for the RCS KPI application (EHS KPI).

Data Loading

Info Providers and objects loaded 

Main Process Chains

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

Data Quality Control

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

<List the existing bugs, its criticity, workarounds and resolution plan.>

Roadmap

• A new report based with Legal and Standard compositions should be delivered soon in the EHS - Admin report menu.
• Broadcasts "SDS generation report to Prod Steward" to reschedule every months (instead of quaterly).