When creating AdHocs for data that should be restricted based on the authenticated Users GLA permissions, the related permissions can be retrieved from the database in this fashion:
Query the CONTACTS_GLA_RESTRICT and DEPT_HIER_GLA_RESTRICT to retrieve the GLA Restrictions defined for the User contact or those inherited from the Departments for which the contact is a Coordinator. To avoid multiple lookups, the number of defined GLA Restrictions and a CSV containing the encapsulated GLA RECIDs for those restrictions should be cross-joined to the GLA table.
SQL
SELECT
ALLOWED_GLA.*
FROM
(
SELECT
GLA.RECID,
CASE
WHEN
-- The GLA Has been specifically allowed
GR.GR_GLA_RECIDS_CSV LIKE CONCAT("%\"", GLA.RECID ,"\"%")
-- Or All GLA are allowed (No GLA Restrictions found)
OR GR.CNT = 0
THEN TRUE
ELSE FALSE
END AS IS_ALLOWED_GLA
FROM GLA
-- Cross join to avoid multiple lookups
CROSS JOIN (
-- Get the GLA Restrictions CSV and counts to determine GLA Permissions
SELECT COUNT(*) AS CNT, GROUP_CONCAT(DISTINCT(CONCAT("\"", GLA_RECID, "\""))) AS GR_GLA_RECIDS_CSV
FROM (
-- If GLA Restrictions are defined for the contact they will override Dept Coord GLA restrictions
SELECT COALESCE(CGR.GLA_RECID, 0) AS GLA_RECID
FROM CONTACTS_GLA_RESTRICT CGR
WHERE CGR.CONTACTS_RECID = :USERS_CONTACTS_RECID
UNION ALL
-- If GLA Restrictions are NOT defined for the contact they will inherit Dept Coord GLA restrictions
SELECT COALESCE(DHGR.GLA_RECID, 0) AS GLA_RECID
FROM DEPT_HIER_GLA_RESTRICT DHGR
INNER JOIN DEPT_HIER_COORDINATORS DHC
ON DHGR.DEPT_HIERARCHY_RECID = DHC.DEPT_HIERARCHY_RECID
WHERE DHC.CONTACTS_RECID = :USERS_CONTACTS_RECID
AND DHC.CONTACTS_RECID NOT IN (SELECT CONTACTS_RECID
FROM CONTACTS_GLA_RESTRICT)
) AS GR_DEFS
) GR
HAVING IS_ALLOWED_GLA = TRUE
) ALLOWED_GLA