440 likes | 448 Views
Learn about the process of frame creation in EGR 2.0 and how to validate and repair initial frames. Use the EGR Interactive Module for visualizing and modifying group data. Access and download relevant files for validation and repair tasks.
E N D
7 EGR initial and preliminary frames and validation tasks • ESTP Training on EGR • 22-23 May 2019
Frame creation • In the consolidation from the LEUs, relationships (and enterprises) EGR 2.0 creates groups • Consolidation results for reference year 2017: • Consolidated LEUs: 1.77 million, consolidated RELs: 1.44 million • Consolidated enterprise groups: 731 thousand (including single LEUs and domestic groups) • The data of the multinational groups are loaded to the EGR FATS initial frame • Multinational enterprise group = at least 2 legal units in at least 2 countries + Foreign controlled groups (1 LEU with control from outside country) • For 2016 more than 111 thousand MNEs, for 2017 121 MNEs thousand (+42 thousand FCs)
Validation / repair action (1) • The initial EGR data are loaded to EGR FATS in December T+1 • The initial picture includes errors, broken structures, missing links can be identified • E.g. due liquidated or not identified LEUs in the trees • NSIs should validate the initial frame • NSIs should repair group structures with pair-wise relationships (with REL file) • NSIs should add missing enterprises • Timing: December T+1 to January T+2 • Guidelines can be found on EGR wiki: • Guidelines 4 - Repair and GEG
Validation / repair action (2) • The following data exchange files can be sent to EGR any time in the process • REL file with relationships to be added / removed • ENT file with updates on the enterprises • LEU file with missing data of legal units on request of EGR (authentic store NSIs in EGR CORE will be asked to send LEUs for their legal units from EGR IS - selected by other NSIs) • NSIs can send more than one transmission of files • NSIs should use for the repair work: • EGR FATS - download files • EGR Interactive Module (EGR IM)
EGR Interactive Module • The EGR IM is a tool to validate and improve group data in the consolidation area of EGR • Visualisation tool for the production phase • Tree structure and graphical presentation of all groups in the EGR consolidation area (not only the MNEs) • Legal units and their relationships in EGR the consolidation area • Tool to modify EGR data • Edit relationships: add/remove relationships • Edit group data: GDC, group variables
EGR IM main functionalities • Look into the consolidation area of EGR in the context of reference year • Possibility to search and display groups based on: • Group attributes -name or identifiers • Legal unit attributes - name or identifiers • Retrieve data files from the EGR consolidation area • Compare groups between reference years • Update relationships between legal units • Update group attributes, incl. update GDC • View notifications for changes • Validation functionalities
EGR IM - access to data • Users can see group details only if at least one resident legal unit is part of the group • When searching in EGR IM, users will be able to see LEUs on record level as result of the search • User are also able to see domestic groups and unlinked resident legal units • These groups are marked
EGR IM - search page Search fields Search results
EGR IM - data display page Group data Legal units Parent RELs Selected LEU Subsidiary RELs
EGR IM - modification functions • Group data: • Change group attributes • Change GDC • Relationships: • Add parent • Add subsidiary • Change relationship • Remove relationship
EGR IM - download of files • Users can request in EGR download files on the actual data of the EGR consolidation area • 3 files will be generated on one request • The requested files will sent by Eurostat EGR Team in EDAMIS • EGROUT_SPECIAL_N dataset • The files will be delivered in zip format, the format of the zipped files is csv • My relationships file • My groups file • All EGR groups (MNEs) file
EGR IM - My relationships file • My relationships file • Export file from EGR consolidation area, which includes all RELs in the consolidation area where the parent or the subsidiary is from my country • Structure of the file: • 11 columns of the NSI input file for relationships + 6 additional columns with source code of relationship and basic data of the group of the subsidiary legal unit
EGR IM – My groups file • My groups file • Export file from EGR consolidation area, which includes all groups, where at least one legal unit of the group is from my country • Structure of the file • 14 columns of the NSI input file for enterprise groups + 6 additional columns with number of legal units, number of enterprises, group type • Group types • MNE groups (MNE), domestic groups without indicated foreign control (DOM), and foreign controlled resident legal units or resident groups (FC)
EGR IM – All EGR groups (MNEs) file • All EGR groups file • This file contains basic information (ID, name, GGH, GDC) on all initial or preliminary groups of EGR • Limitation of selection • Only groups where group type = MNE, which means more than one legal units located in at least two countries • Structure of the file • 5 columns from the NSI input file for enterprise groups + 7 additional columns with GGH data, number of legal units, number of enterprises
EGR IM Report on individual LEUs • Report on individual LEUs and their relationships • Users of EGR IM can generate a report on the processing results ofan individual LEU and their relationships • The report only requires one LEID number as input • The following information will be displayed on the report page: • The legal unit in source and transformation area • Connected legal units in the consolidation table • History of transformation of relationships • Relationships successfully consolidated
EGR IM Report on non-consolidated RELs • Users are able to download the list of the relationships sent by the respective NSI and not consolidated with the reasons • Main reasons for non-consolidated RELs: • Subsidiary or parent are liquidated or same relationship is sent by another source with higher priority • Files will be sent via eDamis after user has requested such report via the application
Possible causes for non-consolidated RELs • The parent legal unit is not consolidated • The subsidiary legal unit is not consolidated • Subsidiary or parents is missing in import area • The verification step of LEID numbers failed • Relation deleted due to circular relations detected • The status of the relationship is liquidated • Business rule violation (shares>100%) • Business rule violation (control>100%) • Priority REL consolidated
GEG structure validations EGR IM • There are different functionalities in EGR IM allowing structure validations: • Graphical presentation of the groups • Comparison of group structure over cycles • Report providing changes of composition of group based on LEUs • Report is based on country of user • Can be filtered by groups size in terms of LEUs and by percentage of change
Which groups to repair? Tasks of NSIs • The main objective of the validation is the repair of large and important MNE group structures with missing relationships • Each NSI can decide which units to check on the basis of available resources and dimension of the groups • NSIs are responsible for their own groups in the initial frame (GGH is in the country in the frame) as well as their national part in the other MNEs • If in the initial frame the NSI detects a new nationally based group which is a fragment of a large MNE group the NSI should deliver the relationship to EGR to repair
Conflicting cases and extra EU+EFTA groups • In complex and unclear cases NSIs should communicate with other NSIs to clarify and agree structures • For the moment tools (e.g. notifications in EGR IM) and rules (governance) are missing for these actions • At present the information sent later will overwrite the earlier data • Outside EU+EFTA groups will be checked and repaired by Eurostat • But EU+EFTA fragments of foreign groups should be linked back to the real groups by the NSIs
Which rules to apply? • The repair should be done by operating on the relationships in such a way that • Priority should be given to keeping groups aggregated rather than broken, even though the complete chain is not known • If the real parent legal unit is not known or not found in the EGR IS, an indirect relationship should be created
How to deliver information to EGR on structure repair? • Missing or ceased relationships can be sent in a relationship (REL) file • Same file as in data exchange on relationships • NSIs should send pairwise relationships to EGR where both parent and subsidiary are identified with LEID numbers • How to cancel a wrong relationship? • By reporting that a relationship is ceased (status=L) • How to add missing correct relationship? • By reporting an additional relationship (status=A, type=C, direct control or =A, through accounts) • Relationships can be added or ceased also in EGR IM
Additional information on the groups • In the repair period further data can be identified for the groups • Structure is correct, but GDC is wrong • Data of GDC to be sent in the GEG file (group phase) or in EGR IM • Additional information on groups • Characteristics of groups (group name, employment, turnover) to be sent in the GEG file (group phase) or in EGR IM
Scenario for NSI validation with EGR IM files (1) • NSI retrieves the lists of EGR IM after the initial frame is published • My relationships file • In the my relationships file all consolidated RELs will be visible with source • NSI can compare the RELs with own source code to the population sent, missing RELs can be identified and analysed • NSIs can analyse and validate the RELs with different source code, these relationships can be valid or wrong (for them repair REL to be prepared)
Scenario for NSI validation with EGR IM files (2) • My groups file • In the my groups file all consolidated groups will be visible with group type • MNEs, DOM and FC groups • Where GGH is not a real GGH, only a subsidiary, relationship should be added to the top • Where group is DOM or FC, a new cross-border relationship or a foreign GDC country code should be added to transform the group to MNE • Relationships can be sent in a REL file or in EGR IM • Feedback on groups (e.g. new country of GDC) can be sent based on the first 14 columns of the file (=GEG NSI input file) or in EGR IM
Scenario for NSI validation with EGR IM files (3) • Based on past experiences it can be useful to create an alphabetical list of groups on national level • This can be done based on the My groups file • Such list would allow detection of the duplicates and incorrect fragmented groups which could then be linked back to the real groups
Scenario for NSI validation with EGR IM files (4) • All EGR groups (MNEs) file • This file helps to have an overview on the EGR structures • The real groups of the fragments can be identified with name of group, GGH and number of legal units • GGH LEID numbers can be selected from the list, e.g. for missing cross-border relationships
Processing of NSI files and visibility of the changes (1) • Repair will be a continuous process • Several interactions expected between Eurostat – NSIs, e.g. requests for ENTs, LEUs • NSIs can continuously send new RELs and also GEG data (e.g. on GDC) in files and in EGR IM • In EGR CORE between December and March a complete consolidation will take place as soon as new data is delivered • New legal units, new relationships, new enterprises will be processed • Clusters and groups will be recalculated
Processing of NSI files and visibility of the changes (2) • Consolidation area data will be updated every day • NSI changes will be visible after process is finished • New frame, the preliminary frame will be created only once, in the end of January T+2 • The preliminary frame targets to have the EGR group structures final • Some structure modifications may be sent also later, in February T+2, when NSIs should focus on modifying group characteristics
Group data to EGR • Preliminary frame is available to NSIs in EGR FATS • End of January T+2 • Structures are considered as final • In February T+2 NSIs can modify the group data • Sending group data in GEG files • Some structure modifications may be also sent • EGR IM can be used • Guidelines can be found on EGR wiki: • Guidelines 4 - Repair and GEG
Files to be returned • EDAMIS dataset to be used: • GEG files: EGR_GEG_A • Where needed, other EGR communication files can be sent to EGR any time in the process • ENT file with updates on the enterprises • LEU file with missing data of legal units on request of EGR • Preferred: one GEG data file transmission per NSI
The EGR group file • EDAMIS datasets to be used: • EGR_GEG_A • Data file with 14 fields • One record = one group • NSI data on groups to EGR • Groups identified by group EGR ID • Filled fields are updated in EGR (where data are new) • GDC (UCI) should be part of group (GEG_UCI_LEID)
Scenario how to deliver group data to EGR • After the publication of EGR preliminary frame NSI download the list of groups from EGR IM • The My groups file is similar to the GEG input file (keep the first 14 columns) • For FC groups country of GDC should be defined in order for the group to be sent to EGR FATS! • NSI can add to the list group information (GDC, country of GDC, group characteristics) and send back the GEG file in csv format • File should be sent in EDAMIS • Changes can be inserted also into EGR IM
EGR final frame • Final consolidation of data at the end of February T+2 • EGR final frame: March T+2 • EGR output with data of MNEs and foreign controlled units where the foreign country of GDC is specified • Groups • Enterprises • Legal units • EGR final frame will be accessible via EGR FATS
EGR IM - Exercise • Search and display • LEU data, group data, relationship data • Update functions for relationships • Update functions • for group data • Retrieve files • Compare groups • in 2 years • https://webgate.acceptance.ec.europa.eu/pilot-b_egr-im