SQL-023: Retrieve all access-group definitions¶

Retrieves all possible access-group definitions from EXT_USER_TYPE_ACCESS_GROUP and EXT_USER_ACCESS_GROUP_CODE without filtering by existing user membership. Used by GetAllAccessGroups and GetAllUserTypeAccessGroups.

Classification¶

Field	Value
SQL ID	SQL-023
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsUserAccessGroup`
Criteria property	`RETRIEVE_ALL_ACCESS_GROUPS`
Lifecycle method	`GetAllAccessGroups`; `GetAllUserTypeAccessGroups`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:CultureCode`	Input	String	5	Culture code for globalized description.
`:UserTypeCode`	Input	String	5	Optional: filter by user type code (appended for `GetAllUserTypeAccessGroups`).

Tables¶

Table	Access	Columns used
EXT_USER_TYPE_ACCESS_GROUP	SELECT	All flag and sort-order columns
EXT_USER_ACCESS_GROUP_CODE	JOIN	`CODE`, `CODE_DESCR`
EXT_USER_ACCESS_GROUP_GCODE	LEFT OUTER JOIN	`CODE`, `CULTURE_CODE`, `CODE_DESCR`

Sample SQL¶

SELECT
    -1 AS UserAccessGroupID,
    -1 AS UserTypeID,
    tag.ext_user_type_code AS UserTypeCode,
    agc.CODE AS AccessGroupCode,
    CASE WHEN aggc.code_descr IS NULL
         THEN cast(agc.CODE_DESCR AS nvarchar2(65))
         ELSE aggc.code_descr
    END AS AccessGroupDescription,
    null AS ExpirationDate,
    'N' AS IsDefaultAccess,
    tag.ADMIN_ACCESS_FLAG AS CanAdminAccess,
    tag.STEWARD_ACCESS_FLAG AS CanStewardAccess,
    tag.SELF_REG_ACCESS_FLAG AS CanSelfRegistrationAccess,
    tag.SYSTEM_CONTROLLED_FLAG AS IsControlledBySystem,
    tag.DEFAULT_STATUS_FLAG AS DefaultStatus,
    tag.SORT_ORDER_NUM AS SortOrder,
    'NONE' AS CreateUser,
    null AS CreateDate,
    'NONE' AS UpdateUser,
    null AS UpdateDate,
    -1 AS UpdateNum,
    '{0}' AS AccessType
FROM ext_user_type_access_group tag
JOIN ext_user_access_group_code agc
    ON tag.ext_user_access_group_code = agc.code
LEFT OUTER JOIN ext_user_access_group_gcode aggc
    ON (agc.code = aggc.code AND upper(AGGC.CULTURE_CODE) = :CultureCode)
-- GetAllUserTypeAccessGroups appends:
-- WHERE upper(tag.ext_user_type_code) = :UserTypeCode
-- plus optional access-flag filters

Operations and processes¶

User access group management

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

Business\SAIF.Business.External.Users\clsUserAccessGroup.vb