GDPR Scope Queries for Oracle E-Business Suite

An important first step is to determine if the Oracle E-Business Suite application is within the scope of GDPR.  Any application or database than contains personal information on EU citizens or residents is within the scope of GDPR, including but not limited to customers, employees, contingent works, and suppliers.  The following SQL queries will help to determine if the Oracle E-Business Suite environment contains GDPR in-scope data.  These queries are not definitive but provide at least a starting point in the GDRP scoping process.

HR - Employee, Contingent Workers, Applicants

SELECT a.country, t.user_person_type, COUNT(*) 
FROM hr.per_all_people_f p
LEFT OUTER JOIN hr.per_person_types t
ON p.person_type_id = t.person_type_id
LEFT OUTER JOIN hr.per_addresses a
ON p.person_id = a.person_id
WHERE UPPER(a.country) IN
('AUSTRIA','AT','BELGIUM','BE','BULGARIA','BG','CROATIA','HR','CYPRUS','CY','CZECH REPUBLIC','CZ','DENMARK','DK','ESTONIA','EE','FINLAND','FI',
'FRANCE','FR','GERMANY','DE','GREECE','GR','HUNGARY','HU','IRELAND','IE','ITALY','IT','LATVIA','LV','LITHUANIA','LT','LUXEMBOURG','LU','MALTA','MT',
'NETHERLANDS','NL','POLAND','PL','PORTUGAL','PT','ROMANIA','RO','SLOVAKIA','SK','SLOVENIA','SI','SPAIN','ES','SWEDEN','SE','UNITED KINGDOM','GB')
GROUP BY a.country, t.user_person_type
ORDER BY 1,2;

TCA Parties - Customers, Organizations, Persons, Groups

SELECT country, party_type, count(*)
FROM (
SELECT p.party_type,
case 
when p.country in ('AT','BE','BG''HR','CY','CZ','DK','EE','FI',
'FR','DE','GR','HU','IE','IT','LV','LT','LU','MT',
'NL','PL','PT','RO','SK','SI','ES','SE','GB') then p.country || ' (PARTY)'
when c.country_code in ('AT','BE','BG''HR','CY','CZ','DK','EE','FI',
'FR','DE','GR','HU','IE','IT','LV','LT','LU','MT',
'NL','PL','PT','RO','SK','SI','ES','SE','GB') then c.country_code || ' (CITIZENSHIP)'
when l.country in ('AT','BE','BG''HR','CY','CZ','DK','EE','FI',
'FR','DE','GR','HU','IE','IT','LV','LT','LU','MT',
'NL','PL','PT','RO','SK','SI','ES','SE','GB') then l.country || ' (LOCATION)'
else null end country
FROM ar.hz_parties p
LEFT OUTER JOIN ar.hz_party_sites s
ON p.party_id = s.party_id
LEFT OUTER JOIN ar.hz_locations l
ON s.location_id = l.location_id
LEFT OUTER JOIN ar.hz_citizenship c
ON p.party_id = c.party_id
WHERE p.country IN
('AT','BE','BG''HR','CY','CZ','DK','EE','FI',
'FR','DE','GR','HU','IE','IT','LV','LT','LU','MT',
'NL','PL','PT','RO','SK','SI','ES','SE','GB')
OR c.country_code IN
('AT','BE','BG''HR','CY','CZ','DK','EE','FI',
'FR','DE','GR','HU','IE','IT','LV','LT','LU','MT',
'NL','PL','PT','RO','SK','SI','ES','SE','GB')
OR l.country IN 
('AT','BE','BG''HR','CY','CZ','DK','EE','FI',
'FR','DE','GR','HU','IE','IT','LV','LT','LU','MT',
'NL','PL','PT','RO','SK','SI','ES','SE','GB')
)
GROUP BY country, party_type
ORDER BY 1,2;

Suppliers

SELECT s.country, COUNT(*) 
FROM ap.ap_supplier_sites_all s
WHERE UPPER(s.country) IN
('AUSTRIA','AT','BELGIUM','BE','BULGARIA','BG','CROATIA','HR','CYPRUS','CY','CZECH REPUBLIC','CZ','DENMARK','DK','ESTONIA','EE','FINLAND','FI',
'FRANCE','FR','GERMANY','DE','GREECE','GR','HUNGARY','HU','IRELAND','IE','ITALY','IT','LATVIA','LV','LITHUANIA','LT','LUXEMBOURG','LU','MALTA','MT',
'NETHERLANDS','NL','POLAND','PL','PORTUGAL','PT','ROMANIA','RO','SLOVAKIA','SK','SLOVENIA','SI','SPAIN','ES','SWEDEN','SE','UNITED KINGDOM','GB')
GROUP BY s.country
ORDER by 1;