Oracle电子商务套件的GDPR范围查询

重要的第一步是确定Oracle电子商务套件应用程序是否在GDPR范围内。 任何包含欧盟公民或居民个人信息的应用程序或数据库都在GDPR范围内，包括但不限于客户，员工，临时工和供应商。 以下SQL查询将有助于确定Oracle电子商务套件环境是否包含GDPR范围内的数据。 这些查询不是确定的，但至少提供了GDRP范围界定过程的起点。

人力资源-员工，临时工，申请人

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缔约方-客户，组织，人员，组

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;

供应商

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;