General presentation
Objective of the application
EHS stands for Environment, Health and Safety. The Goal of EHS is to protect employees, the public, the environment and to comply with applicable laws and protect the Company's reputation. EHS departments, of some companies are responsible for environmental protection, occupational health and safety.
This application described below concerns one of EHS goals : Reporting for Substance Volume Tracking (SVT)
In many countries, regulations state that, above a certain quantity of chemicals and other hazardous substances, you must register with the authorities the maximum quantities of these substances that you plan to purchase, import, produce, sell, or export within a certain period. Substance volume tracking helps you to comply with the relevant regulations by recording the quantities of substances needing to be tracked that you purchase, import, produce, sell, or export. By comparing the recorded quantities with the limit values, the system can warn you in good time before a limit value is exceeded
Tool Leader + IT leader of the application:
Usage information
Around 90 users, worldwide. Each country can only report on its data.
History
2014 - SVT Roll Out Asia/Turkey => KT document "SVT RO // Knowledge Transfer" => https://drive.google.com/open?id=1yWblSq3wGJGdm-T7D97nWF3I7Ufeawe-HYb91RmZ26Q
2015 - SVT Roll Out Europe/USA/Canada => KT document "EHS-SVT-Documentation" => https://drive.google.com/open?id=1W-NDge82ePPYy4fBiMhr57DVYeBxzRsuyPxjK1ZWQuM
Roles & Access
Roles and access
| Role Code | Role Description | Explanation |
|---|---|---|
| ZR_RCS_EHS_A07 | EHS – Substance Volume Tracking Application - End User Role | Role menu / Based on authorization object ZBI_SVT Caution: ZBI_SVT gives access to all C_PLANT including BUTA (CC ZFR9). As the roles where not ready it has been decided not to load Buta data in REACH cube (SVT Analysis - EU (Rhodia) / CREHS06) => the company code ZFR9 is filtered in both DTP. See below important information |
| ZR_RCS_CA_M48 | EHS – Substance Volume Tracking | Role menu which gives acces to all workbooks.
|
| ZBI_JP_EHS_P06 | EHS – Substance Volume Tracking Japan | Role perimeter / Based on authorization object ZJPEHSP06 / Country JP |
| ZBI_KR_EHS_P06 | EHS – Substance Volume Tracking Korean | Role perimeter / Based on authorization object ZKREHSP06 / Country KR |
| ZBI_CN_EHS_P06 | EHS – Substance Volume Tracking China | Role perimeter / Based on authorization object ZCNEHSP06 / Country CN |
| ZBI_TR_EHS_P06 | EHS – Substance Volume Tracking Turkish | Role perimeter / Based on authorization object ZTREHSP06 / Country TR |
| ZBI_TOUT_EHS_P06 | EHS – Substance Volume Tracking ALL PERIMETER | Role perimeter / Based on authorization object ZALLEHSP06 / all countries |
| ZBI_EU_EHS_P06 | EHS – Substance Volume Tracking Europe | Role perimeter / Based on authorization object ZEUEHSP06 / Country of the REACH area FR, BE, DE... |
| ZBI_US_EHS_P06 | EHS – Substance Volume Tracking USA | Role perimeter / Based on authorization object ZUSEHSP06 / Gives access to country US Associated country and islands PR, GU, VI, AS , MP should also be part of the perimeter role. Currently it is not the case. |
| ZBI_CA_EHS_P06 | EHS – Substance Volume Tracking Canada | Role perimeter / Based on authorization object ZCAEHSP06 / Country CA |
Usefull to know :
1- Menu Roles ZR_RCS_CA_M40 “EHS – Substance Volume Tracking Asia” and ZR_RCS_CA_M36 “EHS – Substance Volume Tracking North America” are now obsolete.
There are replaced by ZR_RCS_CA_M48.
2- Perimeter roles ZBI_RCS_EHS_P06 to ZBI_RCS_EHS_P12 are obsolete because Authorization Team has decided to use naming ZBI_xx_EHS_P06 (xx = country)
3- Perimeter roles ZR_RCS_EHS_P06 to P09 and P11, P13 to P15 are obsolete because old naming.
Usefull to know concerning BUTA Chimie (Company Code ZFR9) :
A filter on C_PLANT from authorization has been added in all SVT queries :
variable V_AUT_C_PLANT_0001 for EU report as the Plant is a selection criteria
variable V_C_PLANT_0001 for all other as the Plant is not a selection citeria
The C_PLANT will have to be removed in object ZBI_SVT (Application Role ZR_RCS_EHS_A07)
Following perimeter role will have to be managed for all EHS SVT users :
For all plants, except BUTA, use perimeter role ZR_TOUT_CA_P02 / object ZTOUTCAP02
- For BUTA plants, use perimeter role ZR_7866_CA_P02 / object Z7866CAP02
The BUTA data will have to be loaded in cube REACH (SVT Analysis - EU (Rhodia) / CREHS06).
Usefull current authorization matrix
See also file maintained by Authorization team : BW Catalog of Roles / link: https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I
Authorization objects
List of autorisation objects mandatory for the application.
| Authorization object | Explanation |
|---|---|
| Country of Origin (C_CNTRY_O) | |
| Plant (C_PLANT) | See above explanation for role ZR_RCS_EHS_A07 |
Dataflow overview
The dataflow is in the following document : EHS-SVT-Documentation https://drive.google.com/open?id=1W-NDge82ePPYy4fBiMhr57DVYeBxzRsuyPxjK1ZWQuM
Reporting documentation drive folder:
https://drive.google.com/drive/folders/0B0qn89R0RGdqYkZZOFZyYXlXVkE
Exemple of dataflow overview :
Template Application name DataFlow
Functional and Technical rules on Workbench + Reporting
Rules & Explanations
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
Variable Name | Variable Description | Related classe name | object |
V_0CALMONTH_0001 | Get period (Current or current+previous) for Japan from c_glbfilt in variable V_0CALMONTH_0001 | ZCL_BIU001_V_0CALMONTH_0001 | 0CALMONTH |
V_0CALMONTH_0002 | Get period (Current or current+previous) for Other cnrty from c_glbfilt in variable V_0CALMONTH_0002 | ZCL_BIU001_V_0CALMONTH_0002 | 0CALMONTH |
V_0CALMONTH_0003 | Get period current period for Japan from c_glbfilt in variable V_0CALMONTH_0003 | ZCL_BIU001_V_0CALMONTH_0003 | 0CALMONTH |
V_0CALMONTH_0004 | Get previous period for Japan from c_glbfilt in variable V_0CALMONTH_0004 | ZCL_BIU001_V_0CALMONTH_0004 | 0CALMONTH |
V_0CALMONTH_0005 | Get current period for Other country from c_glbfilt in variable V_0CALMONTH_0005 | ZCL_BIU001_V_0CALMONTH_0005 | 0CALMONTH |
V_0CALMONTH_0006 | Get previous period for other country from c_glbfilt in variable V_0CALMONTH_0006 | ZCL_BIU001_V_0CALMONTH_0006 | 0CALMONTH |
V_C_CNTRY_O_0004 | Get the list country related with REACH regulatory list from C_REG2 in variable V_C_CNTRY_O_0004 | ZCL_BIU001_V_C_CNTRY_O_0004 | C_CNTRY_O |
V_0PLANT_0006 | EHS - Get Japan plant list (Customer Exit) | ZCL_BIU001_V_0PLANT_0006 | 0PLANT |
V_0PLANT_0007 | EHS - Get other plant list (Customer Exit) | ZCL_BIU001_V_0PLANT_0007 | 0PLANT |
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 (intervalle 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 :
Rhodia | BW_QRY_MV_SPD01_0010 | SPRINT - Rhodia Purchase Other Data For EHS | 13/11 Exclusion PO Doc Type UB |
Rhodia | BW_QRY_MV_SPD01_0012 | SPRINT - Rhodia Purchase Japan Data For EHS | 13/11 Exclusion PO Doc Type UB |
Solvay | BW_QRY_MV_SPD01_0013 | SPRINT - Solvay Purchase Japan Data For EHS | |
Solvay | BW_QRY_MV_SPD01_0009 | SPRINT - Solvay Purchase Other Data For EHS |
For production data :
Rhodia | BW_QRY_MPR_IC001_063 | BW - EHS - Neutralized Production Other data for EHS | 26/10 Ajout des material type ZUNB / ZNLA |
Rhodia | BW_QRY_MPR_IC001_064 | BW - EHS - Neutralized Production Japan data for EHS | 26/10 Ajout des material type ZUNB / ZNLA |
Japan & Korean usages
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 for Asia : (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
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.
Dependencies with other applications
EHS SVT is adding EHS information on transactional data : Purchasing (Application SPRINT), Production, Sales (OBAS)
Data loadings
Info providers and objects loaded
Main Chains
Process Chain | Code | Type | Frequency | Comments |
|---|---|---|---|---|
| EHS - META - W - SVT 01.00 - Master Chain | PC_EHS_005 | MAIN |
| |
Slave Chains
Process Chain | Code | Type | Frequency | Comments |
|---|---|---|---|---|
| EHS - MD - W - SVT 01.11 - MD & Reference DSOs (Rhodia) | PC_EHS_004 | SLAVE | ||
| EHS - TD - W - SVT 01.21 - Business Layer (Rhodia) | PC_EHS_002 | SLAVE | ||
| EHS - TD - W - SVT 01.31 - Reporting Layer AP Area (Rhodia) | PC_EHS_007 | SLAVE | ||
| EHS - TD - W - SVT 01.41 - Reporting Layer EU Area (Rhodia) | PC_EHS_008 | SLAVE | ||
| EHS - TD - W - SVT 01.51 - Reporting Layer NA Area (Rhodia) | PC_EHS_010 | SLAVE | ||
| EHS - MD - W - SVT 01.10 - MD & Reference DSOs (Solvay) | PC_EHS_003 | SLAVE | ||
| EHS - TD - W - SVT 01.20 - Business Layer (Solvay) | PC_EHS_001 | SLAVE | ||
| EHS - TD - W - SVT 01.30 - Reporting Layer AP Area (Solvay) | PC_EHS_006 | SLAVE | ||
| EHS - TD - W - SVT 01.40 - Reporting Layer EU Area (Solvay) | PC_EHS_009 | SLAVE | ||
| EHS - TD - W - SVT 01.50 - Reporting Layer NA Area (Solvay) | PC_EHS_011 | SLAVE |
Other Chains
Process Chain | Code | Type | Frequency | Comments |
|---|---|---|---|---|
| EHS - Master Chain Hist Load | PC_EHS_MAIN_HIST_LOAD | ONE SHOT | This chain can be used to reload data without using global filters values, and without reload referentials Dsos Global filters C_GLBFILT : stream EHS / rule DATA* | |
Loading frequency
The application is done weekly on saturdays at 11am
Average performance
| Key Figure | Estimation |
|---|---|
| ~ Average Process Chain Runtime | 7 hours as currently 2 years are extracted |
| ~ Average nb of rows loaded per load | |
| ~ Total nb of rows loaded (if full) | |
| ~ Average Runtime for 10k lines |
Record Keeping
Reporting
Queries End User Documentation
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)
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
Reg List (C_REG) | Cube (0INFOPROV) | Scenario (C_SCEN) | Others | ||
BW_WBK_EHS_SVT_CN_001 CN Registered New Chemicals (Core Workbook) | BW_QRY_MPR_EHS07_CN_0001 CN Registered new chemicals (Core Query) | ZCN_NEW_CH | CUB_EHS07, CREHS02 | all except PUR | Regulation Code ( C_REGCODE) / Type of substance = CN001 + '#' |
BW_WBK_EHS_SVT_CN_002 CN KECH Chemicals (Core Workbook) | BW_QRY_MPR_EHS07_CN_0002 CN Key environmental controlled haz. chemicals (Core Query) | ZCN_KECHC | CUB_EHS07, CREHS02 | PRO | Regulation Code ( C_REGCODE) / Type of subst. = CN002 + '#' |
BW_WBK_EHS_SVT_JP_001 JP Annual Reporting (Core Workbook) | BW_QRY_MPR_EHS07_JP_0001 JP Annual reporting of chemicals (Core Query) | ZJP_AN_REP | CREHS01, CUB_EHS06 | All excpect PUR | Regulation Code ( C_REGCODE) / Type of subst. = JP001, JP002, JP003, # |
BW_WBK_EHS_SVT_JP_002 JP SVE-LPN Annual Reporting (Core Workbook) | BW_QRY_MPR_EHS07_JP_0002 JP Annual reporting of SVE/LPN of new chemicals (Core Query) | ZJP_CSCL, ZJP_ISHL | CREHS01, CUB_EHS06 | EXP, IMP, PRO, ICI | Regulation Code ( C_REGCODE) / Type of subst. = JP004, JP005, JP006, # |
BW_WBK_EHS_SVT_KR_001 KR Annual Reporting (Core Workbook) | BW_QRY_MPR_EHS07_KR_0001 KR Annual reporting of chemicals (Core Query) | ZKR_AN_REP | CREHS03, CUB_EHS08 | all | Regulation Code ( C_REGCODE) / Type of subst. = KR001, KR002, #' |
BW_WBK_EHS_SVT_KR_002 KR Registration (Core Workbook) | BW_QRY_MPR_EHS07_KR_0002 KR Registration (Core Query) | ZKR_DESIGN, ZKR_NEW | CREHS03, CUB_EHS08 | PRO/ICI/IMP On devrait ajouter le filtre dans qry | Regulation Code ( C_REGCODE) / Type of subst. = KR003, KR004, # |
BW_WBK_EHS_SVT_TR_001 TR Substances Notification (Core Workbook) | BW_QRY_MPR_EHS07_TR_0001 TR Substances Notification (Core Query) | ZTR_CICR | CUB_EHS07, CREHS02 | ICI, SO | Regulation Code ( C_REGCODE) / Type of substance = TR001, # |
BW_WBK_EHS_SVT_EU_001 EU REACH (Core Workbook) | BW_QRY_MPR_EHS07_EU_0001 EU REACH (Core Query) | REACH | CREHS04, CREHS06 | ICI, PRO, IMP | |
BW_WBK_EHS_SVT_US_001 US TSCA CDR (Core Workbook) | BW_QRY_MPR_EHS07_US_0001 US TSCA CDR (Core Query) | TSCA | CREHS05, CREHS07 | IMP, ICI, PRO, EXP | |
BW_WBK_EHS_SVT_US_002 US TSCA 12b (Core Workbook) | BW_QRY_MPR_EHS07_US_0002 US TSCA 12b (Core Query) | ZUS_T12B | CREHS05, CREHS07 | EXP | |
BW_WBK_EHS_SVT_CA_001 Canada Non DSL Tracking (Core Workbook) | BW_QRY_MPR_EHS07_CA_0001 Canada Non DSL Tracking (Core Query) | ZCA_DSL | CREHS05, CREHS07 | IMP, PRO, ICI | Substance <> EXCLUDED |
BW_WBK_EHS_SVT_CA_002 Extended tracking for Canada (Core Workbook) | BW_QRY_MPR_EHS07_CA_0002 Extended tracking for Canada (Core Query) | ZCA_SVT | CREHS05, CREHS07 | IMP, ICI, PRO | |
Main functionalities
Broadcast
Maintenance
Known bugs
Recurring procedure
- Reloading Japan & Korean usages => see Japan & Korean usage paragraph
Reloading data for a specific year => use chain PC_EHS_MAIN_HIST_LOAD / EHS - Master Chain Hist Load => see Other chains paragrap
Planned Evolution