Access Management 

DataFlow

Overview

The dataflow is in the following document : EHS-SVT-Documentation https://drive.google.com/open?id=1W-NDge82ePPYy4fBiMhr57DVYeBxzRsuyPxjK1ZWQuM

Old Dataflow:

Generic dataflow

Reporting documentation drive folder:

https://drive.google.com/drive/folders/0B0qn89R0RGdqYkZZOFZyYXlXVkE

Exemple of dataflow overview :

Template Application name DataFlow

Technical Rules on Workbench

Scenario Determination (C_SCEN)

In transformation TRCS IFS_EHS_SVT_RO2 -> ADSO ABEHSV01:

C_SCEN is defined in end routine in function of severals rules, the results could be:

When source = APEHSV01 (Neutralized Production (WP1)) → PRO

When source = APEHSV03 (Deliveries (WP1)) → LR, SO, EXP or ICI

When source = APEHSV02 (Direct Shipment (WP1)) → PUR, IMP or ICI.

In transformation TRCS IFS_EHS_SVT_RO3 -> ADSO ABEHSV03:

C_SCEN is defined in end routine in function of severals rules, the results could be:

APEHSV05 (Neutralized Production (PF1)) → PRO

APEHSV06 (Purchasing data (PF1)) → PUR, IMP or ICI

APEHSV07 (Deliveries (PF1)) → LR, SO, EXP or ICI

Unit conversions

The Unit conversion is done in the Bex queries (Quantity Conversion Type), depending on reports

• Reporting in pound (LB) for US
• Reporting in kilogram (KG) for Canada
• Reporting in Ton (TO) for all others

Transactionnal data are stored in material base unit, except of SPRINT Reception quantities (PO Unit)

• if base unit / po unit is managed in dimension MASS, conversion within table T006 works well

• if base unit / po unit is not managed in dimension MASS, conversions for KG/LB/TO have to be managed on ERP side (MM03 / Alternative Conversion).

Example : production done on unit KRE; other used units 1KG, M2, M3, KN3...

There are many "special" units on Solvay ERP.

Important : 

Quantities are not converted when loading data, and are not store into cubes (The conversion is done when running the report)

So, if a conversion is missing for a material ask the material referent to 

1- manage the conversion on ERP Side by managed the material master (MM03) / unit of measure tab

2- the day after, when the DSO UOMCMAT2 is filled, the conversion is applied in the reports. It applies to whole data without any reloading.

Bex variables & corresponding class

Following variables are used in the Bex Queries used to extract data for SVT and also in DTP for weekly loading.

See ABAP Code in Classe (SE24) / Table for BADI / Variable link =  ZBIU001_V_VAR

• BEFORE_VARIABLE_SCREEN method has been filled to BEx Queries (Souce of data)
• AUTHORITY_CHECK method has been filled to use variable in DTP selection

Bex queries used for data loading

Bex queries have been created to extract data from existing BW applications. Theses queries are dedicated to EHS, and all requested filters are done in the queries (interval of month, list of plant, material type limitation...). Queries are slightly different from RCS or Solvay.

Also Japan is always by its own has the corresponding year is from April Y to Mars Y+1

The interval of time is get from the global filter C_GLBFILT (stream EHS / rule DATA*)

 For purchasing data :

For production data :

On Generic dataflow, the DSO concerned by those Bex Queries as source of loading are;

• DSO APEHSV01 (Neutralized Production (WP1)) : loaded by query BW_QRY_MPR_IC001_066 (BW - EHS - Neutralized Production data for EHS (Hana Flow))
• DSO APEHSV02 (Purchasing data (WP1)) : loaded by query BW_QRY_MV_SPD01_0029 (SPRINT - Rhodia Purchase Data For EHS (Hana Flow))
• DSO APEHSV06 (Purchasing data (PF1)) : loaded by query BW_QRY_MV_SPD01_0030 (SPRINT - Solvay Purchase Data For EHS (Hana Flow))

For manual loadings, ouside of current loading: be careful with large extraction on those loadings, job can use a lot of memories on the system and generate locks and dumps. 

It is better to launch those loadings after or before closing periods. If not possible to wait,  load on a few month time perimeter only, and not a long period.

With short time execution, loadings are OK and there is no issues.

Japan & Korean usages (C_USAGEJP) - Old dataflow only

Technical rule to avoid loading to many usage in MasterData C_USAGEJP

-> New usages are created into MasterData C_USAGEJP when these DSO are filled

Example : CUST-RH01.99000027

-> Attributs and Texts are only loaded if the usage exists in the Masterdata

Caution :

• the text transfo + DTP was created in WBP directly in 01.2014

• the C_USAGEJP Attr is loaded with masterdata but it need to be loaded after DSO (=> better to  change PCH, attributs are loading the following week)

• the C_USAGEJP Text is not automatic (=> better to be added in PCH)

Steps for to reload historical for a year :

• delete values for year to reload in DSOs manualy. Do not use the Abap program which is based on others Global Filters variables

• change Global Filter C_GLBFILT with year to reload

• load DSOs

• change Global Filter C_GLBFILT to set historical load inactive

• Load masterdata  C_USAGEJP attributs & texts

Registered Volums, Tonnage Bands, TSCA Section

Algorithm in place for North America (DSO DBEHS012/DBEHS011)

Step1 = search in EHSA with Reg/Substance (one access only)

-> flow year have to be into validity interval

-> store k_rellim into k_limqty (with unit / conversion possible) + u_limit into u_limit

-> store c_subsec into c_subsec

Set 2= search in EHSW with Reg/Substance

For all record with same Reg/Substance/Company Code

-> flow year have to be into validity interval

-> if 1st record store cregleve into c_thrhld

-> if 2nd or more and if cregleve > than previous cregleve, store new cregleve into c_thrhld

If c_thrhld is not initial and c_thrhld exist in masterdata :

-> store master data k_uppqty to k_limqty +  u_limit into u_limit

Step 2  possible pour North America ?

Algorithm in place for Europe : (DSO DBEHS010 / DBEHS009 )

Search in EHSW with Reg/Substance/Company Code

For all record with same Reg/Substance/Company Code

-> flow year have to be into validity interval

-> if 1st record store cregleve into c_thrhld

-> if 2nd or more and if cregleve > than previous cregleve, store new cregleve into c_thrhld

If c_thrhld is not initial and c_thrhld exist in masterdata :

-> store master data k_uppqty to k_limqty + u_limit into u_limit

Algorithm in place forAsia :(DSO DSO_EHSU / DBEHS006)

-> Case regulatory ZCN_NEW_CH' or 'ZJP_ISHL' or 'ZJP_CSCL'

search in EHSA with Reg/Substance (one access only)
store k_rellim into k_rellim (Relative Limit Value / no unit)
store c_regstat into c_regstat (Regulation/Notfication Status)
-> Case regulatory ZTR_CICR
store 1 into k_rellim (Relative Limit Value / no unit)
search in EHSA with Reg/Substance (one access only)
store c_regstat into c_regstat (Regulation/Notfication Status)
store c_rstat into c_rstat (Registration Status)
-> Case regulatory 'ZKR_NEW' or 'ZKR_DESIGN'

Search in EHSW with Reg/Substance
If k_rellim exists in masterdata c_thrhld-> store master data k_uppqty to k_rellim + c_thrhld

Report by report, which object is used?

• Reports for Japan => K_rellim is used (EHS_REG)

• Reports for Korea => C_thrhld is used (EHS_SLIMIT)
• Reports for China => none used

• Report for Turkey => C_thrhld is used but not filled for TR. Should we take the k_rellim ?
• Report for Europe => C_thrhld is used (EHS_SLIMIT)
• Report for US => none used
• Report for Canada => Report non DSL => C_thrhld is used => but shouldn't we take the k_limqty converted in KG (EHS_REG) ?

Object by objects, in with reports are they used?

• C_THRHLD Threshold Limit = Tonnage band (with text) from EHS_SLIMIT

=> For REACH + KOREA + NA (but real case?)

• K_LIMQTY Limit Quantity (with unit) from EHS_REG or upper value of Tonnage band
  

=> for Canada, REACH,

• K_RELLIM Relative Limit Value (without unit) from EHS_REG / Constant / EHS_SLIMIT

=> for ASIA and TR

MasterData Threshold Limit (C_THRHLD) & Attributs and descriptions

 Attributs were loaded from a flat file (by Jonathan in mai 2014)

Text were filled manually for Asia, and Reach.

Tonnage band alert

PO2 authorization scope:

For Power Of Two project, the scope by authorization group was recovered from table ZWEHST103 in WP1 system.

In master data 0EHS_AUTH (Authorization group) two attributes are added: CPFCTR1_2 (GBU) and C_AUTHMA (Scope).

Then in the transformation linked to 0EHS_SPEC a lookup is done with 0EHS_AUTH to take the GBU and C_AUTHMA associated (loading done in process chain "EHS_MASTER_DATA"):

Then in EHS queries, we can use the C_AUTHMA to segregate at authorization level between ECO and SCO data.

ABAP programs & functions

ZBW_EHS001 - EHS - SVT - Selective Deletion on DPEHS006 Based on year

This program deletes data on DSOs  and ADSO identified (not necessarily on DPEHS006 - program's text should be changed) in the selection criterium.

It deletes data regarding the variables values in the Global Filter (C_GLBFILT) for Stream EHS, and Rule DATA* for old dataflow, or for new dataflow Stream EHS_SVT Rule DATA_LOAD.

This program is used in the main process chain with following variants

At execution, on selection screen, we choose data to delete: Japan, Australia or other. 

If it concerns new dataflow, "Generic Dataflow ?" should be selected.

It it concerns TSCA dataflow, used in a specific process chains (PC_EHS_SVT_GENFLW_TSCA, PC_EHS_SVT_OLD_FLW_TSCA), "TSCA dataflow must be selected.

It it concerns ADSO of propagation layer, "ADSO Propa" must be selected.

ZBW_EHS002 EHS-SVT Determination of time intervals for loadings

This program updates variables in  the Global Filter (C_GLBFILT) for Stream EHS, and Rule DATA*

The intervals of months are determined using the current date (SY-DATUM).

Notice that if variable exist in C_GLBFILT but is not checked in the program, the variable will be updated to inactive (attribute C_ACTIVE changed to 'N'). 

Caution : if the option 'Do not update MD Global Filter' is set, values will have to be modified manually in the masterdata when changing year

This program is used in the main process chain with following variants

This program is not used on generic dataflow

ZBW_EHS003 - EHS - SVT - Selective Deletion on DPEHS008 and DSO_EHS5 about compositions data based on current year

This program deletes data in business DSOs identified in the selection criterium.

It deletes data regarding the current year based on actual date (sy-datum).

This program is used in the main process chain with following variants:

This program is not used on generic dataflow

ZBW_EHS001_GET_COMPOSITION  - Get Composition Data

This function is used in transformation loading DSO_EHSR (for RCS) and DBEHS005 (for Solvay) which are Flow data with compositions

It read the composition/regulatory list from DSOs DPEHS00800  (for Solvay)  or DSO_EHS5 (for RCS)

Import parameters :

• Material (C_MATNR2)

• Calendar Year (CALYEAR)

• Country key (COUNTRY)

• Source System (LOGSYS)

This program is not used on generic dataflow

Reporting

Main queries

For EHS SVT, queries have been developped, but for each query, an analysis workbook has been settled => only one access mode.

All queries/workbooks are published in an unique role menu “EHS – Substance Volume Tracking” (ZR_RCS_CA_M48)

New Query: SVT Report-Notification of nanomaterials Europe (core query) => BW_QRY_MPR_EHS07_EU_0002

NB : Following queries  are not yet available as workbook (not part of convergence project), a freshdesk has to be created by the Business Team.

• EHS - Imported Material without Composition (Core query) => BW_QRY_MPR_EHS02_0005
• EHS - Imports List by Material (Core query) => BW_QRY_MPR_EHS02_0004
• EHS - Imports list from GTS (Core query) => BW_QRY_MPR_EHS02_0002
• EHS - Imports List by Substance (Core query) => BW_QRY_MPR_EHS02_0006

All reports have following common filtrers

• Country of Origin (C_CNTRY_O) = autorisation

• Tracking Flag (C_TRCKFLG) = 'Y'

• Component Type Exclusion (C_IMPFLG) = 'N'

• Material Exclusion Flag (C_EXCLUSI) = 'N'

• Chemical reaction <> 0 'NO'

All reports have following condition 

• Quantites PRO >= 0
  
  

 Here are the different filters for each report

Main functionalities

Broadcast

The broadcast are run every Wednesday and Saturday, via the chain PC_EHS_014, which is included in weekly main chain PC_EHS_005(Saturday) and PC_EHS_NA_001 (Wednesday)

 For Broadcast diffusion, it has been decided to split broadcast by company code using Bursting option for Broadcast

Link to bursting documentation https://docs.google.com/document/d/15DaFZFEcYsfZc98zzlox-J3QaCYbbGsMTd7GxbSHOMM/edit#heading=h.fct6jj0qa4s

Availables broadcast

ONLY the US BroadCast is available.

Other broadcasts will be implemented later by BW maintenance team, via freshdesk process.

US Broadcast rules

Aim : hold alert for the first expedition, for a tracked substance, a company code, a destination country within the same year.

Alert limited to :

• regulatory list US_T12B

• scenario EXP

• country of origin US + PR, GU, VI, AS , MP

• country of destination (ship to) diffrent from US = US + PR, GU, VI, AS , MP

Additional rule :

The delivery creation date has to be the same year as goods issue date.

Broadcast solution

The bex query BW_QRY_MPR_EHS07_001 / US TSCA 12b - Alerts (BCast) identify possible alets.

A filter is handle in the DTP to limit the concerned countries for US :

• country of origin US + PR, GU, VI, AS , MP
• country of destination <> US + PR, GU, VI, AS , MP (means the country of the ship-to)

 The transformation determines :

• if the alert has already been sent by looking in the DSO  DBEHS013 / SVT - Alerts for Broadcast
• the delivery information by looking in Deliveries DSOs (DSO_DLV1 for Rhodia, DPSD001 for Solvay) as we do not have deliverie details in the SVT cubes
• the google group to which the alert has to be send (C_GLBFILT)
  
  

Important : 

The DSO DBEHS013 contains the already sent alert.
Do not empty it, history can not be reloaded without any flow modifications and alerts will be sent again.

The link company code / google group is stored in MD Global Filter (C_GLBFILT) => see current procedure

The google group email adress are stored in MD Google Group (C_GROUP) => see current procedure

Google groups have been listed by business and are available via following link https://drive.google.com/open?id=12ebK-h1px82ZNwE_mjjzTZVpjEn9SVkmwvpl_hp51ec

The creation of the 11 google groups have been asked to France Infra-servicedesk the 21st of december 2015.

=> but only the first one ‘SSP WW’ for US is read 

Queries used for broadcast

Detail of US Broadcast email

Broadcast with busting option using query BW_QRY_DBEHS013_0002 created for busting purpose and attribut "E-Mail Address" of master data Google Group (C_GROUP)

The alert of the week will be split by google group (C_Group)  and sent to the google group address or person address found in C_Group

Content of the email

Other info for futur broadcast

For other broadcast, another rule will be used to define a threshold for each regulatory list.

 Stream EHS / Rule SVT_LIMIT : One threshold by regulatory list

 To harmonize threshold, all should be set to 80% except Turkey which is 100%

No threshold for reg list ZUS_T12B (threshold = 1st expedition)

Broadcast tonnage band alert

Dependencies with other applications

EHS SVT is adding EHS information on transactional data : Purchasing (Application SPRINT), Production, Sales (OBAS)

Data Loading

Info Providers and objects loaded 

Slave Chains

Other Chains

Loading frequency

The application is done weekly on saturdays at 10 am and wednesday at 10:00am

Average performance

Record Keeping

Currently data from 2012 to current year

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

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