Query GLA permissions
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.
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