1
WITH org_admins AS (
2
SELECT accountgroup.mgm_id,
3
accountgroup.account_id
4
FROM accountgroup
5
WHERE ((accountgroup.account_group_type_label)::text = 'org_admin'::text)
6
), system_users AS (
7
SELECT true AS is_admin,
8
account.mgm_id,
9
account.account_id,
10
system.system_id,
11
NULL::character varying AS group_name
12
FROM (system
13
JOIN account ON (((system.mgm_id = account.mgm_id) AND ((system.organization)::text = (account.organization)::text))))
14
UNION
15
SELECT false AS is_admin,
16
systemgrouppermission.mgm_id,
17
systemgrouppermission.account_id,
18
systemgroupmember.system_id,
19
systemgrouppermission.group_name
20
FROM (systemgrouppermission
21
JOIN systemgroupmember ON (((systemgrouppermission.mgm_id = systemgroupmember.mgm_id) AND (systemgrouppermission.system_group_id = systemgroupmember.system_group_id))))
22
), users_details AS (
23
SELECT account.mgm_id,
24
account.account_id,
25
account.username,
26
account.organization,
27
(org_admins.account_id IS NOT NULL) AS is_admin,
28
account.synced_date
29
FROM (account
30
LEFT JOIN org_admins ON (((account.mgm_id = org_admins.mgm_id) AND (account.account_id = org_admins.account_id))))
31
)
32
SELECT users_details.mgm_id,
33
users_details.account_id,
34
users_details.username,
35
users_details.organization,
36
system_users.system_id,
37
system_users.group_name,
38
users_details.is_admin,
39
users_details.synced_date
40
FROM (users_details
41
LEFT JOIN system_users ON (((users_details.mgm_id = system_users.mgm_id) AND (users_details.is_admin = system_users.is_admin) AND (users_details.account_id = system_users.account_id))))
42
WHERE (system_users.system_id IS NOT NULL)
43
ORDER BY users_details.mgm_id, users_details.account_id, system_users.system_id;