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.

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