---
title: "SQL-016: Retrieve authorized menu items for a user (UNION query)"
summary: Three-part UNION query returning secured, unsecured, and favorite menu items for a specific user.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business.Menu
reference_type: package
---

# SQL-016: Retrieve authorized menu items for a user (UNION query)

Executes a three-part UNION query returning: (1) secured items where the user's security group matches, (2) unsecured items with no security record, and (3) the user's active favorites. Applies case-insensitive group matching per [BR-MNU-04](../business-rules.md#br-mnu-04-case-insensitive-security-group-matching) and active-favorite filtering per [BR-MNU-05](../business-rules.md#br-mnu-05-favorite-active-status-enforcement).

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-016 |
| **Operation type** | Read |
| **Execution method** | Inline (StringBuilder) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsMenuAuthorizedItemCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` (MenuUser > String.Empty) / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:MenuUser` | Input | String** | 30 | User login bound from `MyCriteria.MenuUser`. Used in all three UNION legs. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [MENU_ITEM](../database-tables.md#menu-item) | SELECT / JOIN | `MENU_ITEM_SEQ_NUM`, `PAGE_ORDER_NUM`, `MENU_CATEGORY_CODE`, `TOOL_TIP_TXT`, `VISIBLE_FLAG`, `MENU_PAGE_SEQ_NUM` |
| [MENU_SECURITY](../database-tables.md#menu-security) | JOIN | `MENU_ITEM_SEQ_NUM`, `SECURITY_GROUP_TXT` |
| [MENU_SECURITY_USER](../database-tables.md#menu-security-user) | JOIN | `MENU_USER`, `MENU_USER_EMPLID`, `SECURITY_GROUP_TXT` |
| [MENU_PAGE](../database-tables.md#menu-page) | JOIN | `MENU_PAGE_SEQ_NUM`, `PAGE_NAME`, `PAGE_LINK_TXT`, `APPLICATION_CODE`, `FAVORITE_NAME`, all flag and tip columns |
| [BUSONLINE_APPS_CODE](../database-tables.md#busonline-apps-code) | JOIN | `CODE`, `CODE_DESCR` |
| [MENU_CATEGORY_CODE](../database-tables.md#menu-category-code) | JOIN | `CODE`, `CODE_DESCR` |
| [MENU_FAVORITE](../database-tables.md#menu-favorite) | JOIN | `MENU_ITEM_SEQ_NUM`, `MENU_USER`, `MENU_USER_EMPLID`, `MENU_ORDER_NUM`, `ITEM_INACTIVE_DATE` |

## Sample SQL

```sql
-- Part 1: secured items for the user
SELECT DISTINCT
    ITM.MENU_ITEM_SEQ_NUM AS MenuItemSeqNum, USR.MENU_USER AS MenuUser,
    ITM.PAGE_ORDER_NUM AS MenuOrder, 'N' AS FavoriteFlag,
    USR.MENU_USER_EMPLID AS MenuUserEmplid,
    ITM.MENU_CATEGORY_CODE AS MenuCategoryCode, CAT.CODE_DESCR AS Category,
    ITM.TOOL_TIP_TXT AS ToolTip, ITM.VISIBLE_FLAG AS VisibleFlag,
    PAG.MENU_PAGE_SEQ_NUM AS MenuPageSeqNum, PAG.PAGE_NAME AS PageName,
    PAG.PAGE_LINK_TXT AS PageLink, PAG.APPLICATION_CODE AS ApplicationCode,
    APP.CODE_DESCR AS ApplicationPath, PAG.FAVORITE_NAME AS FavoriteName,
    PAG.SAVE_PREFERENCES_FLAG AS SavePreferencesFlag, PAG.PRINT_FLAG AS PrintFlag,
    PAG.DOWNLOAD_FLAG AS DownloadFlag, PAG.HELP_FLAG AS HelpFlag,
    PAG.SAVE_FLAG AS SaveFlag, PAG.INSERT_FLAG AS InsertFlag,
    PAG.DELETE_FLAG AS DeleteFlag, PAG.PRINT_TIP_TXT AS PrintTip,
    PAG.DOWNLOAD_TIP_TXT AS DownloadTip, PAG.SAVE_TOOLTIP_TXT AS SaveTip,
    PAG.DELETE_TOOLTIP_TXT AS DeleteTip, PAG.INSERT_TOOLTIP_TXT AS InsertTip,
    'N' as UnsecuredFlag
FROM menu_item itm, menu_security sec, menu_security_user usr,
     MENU_PAGE PAG, BUSONLINE_APPS_CODE app, MENU_CATEGORY_CODE cat
WHERE SEC.MENU_ITEM_SEQ_NUM = ITM.MENU_ITEM_SEQ_NUM
  AND PAG.MENU_PAGE_SEQ_NUM = ITM.MENU_PAGE_SEQ_NUM
  AND APP.CODE = PAG.APPLICATION_CODE
  AND CAT.CODE = ITM.MENU_CATEGORY_CODE
  AND upper(USR.SECURITY_GROUP_TXT) = upper(SEC.SECURITY_GROUP_TXT)
  AND USR.MENU_USER = :MenuUser
UNION
-- Part 2: unsecured items (no security record)
SELECT
    ITM.MENU_ITEM_SEQ_NUM AS MenuItemSeqNum, :MenuUser AS MenuUser,
    ITM.PAGE_ORDER_NUM AS MenuOrder, 'N' AS FavoriteFlag, NULL AS MenuUserEmplid,
    ITM.MENU_CATEGORY_CODE AS MenuCategoryCode, CAT.CODE_DESCR AS Category,
    ITM.TOOL_TIP_TXT AS ToolTip, ITM.VISIBLE_FLAG AS VisibleFlag,
    PAG.MENU_PAGE_SEQ_NUM AS MenuPageSeqNum, PAG.PAGE_NAME AS PageName,
    PAG.PAGE_LINK_TXT AS PageLink, PAG.APPLICATION_CODE AS ApplicationCode,
    APP.CODE_DESCR AS ApplicationPath, PAG.FAVORITE_NAME AS FavoriteName,
    PAG.SAVE_PREFERENCES_FLAG AS SavePreferencesFlag, PAG.PRINT_FLAG AS PrintFlag,
    PAG.DOWNLOAD_FLAG AS DownloadFlag, PAG.HELP_FLAG AS HelpFlag,
    PAG.SAVE_FLAG AS SaveFlag, PAG.INSERT_FLAG AS InsertFlag,
    PAG.DELETE_FLAG AS DeleteFlag, PAG.PRINT_TIP_TXT AS PrintTip,
    PAG.DOWNLOAD_TIP_TXT AS DownloadTip, PAG.SAVE_TOOLTIP_TXT AS SaveTip,
    PAG.DELETE_TOOLTIP_TXT AS DeleteTip, PAG.INSERT_TOOLTIP_TXT AS InsertTip,
    'Y' as UnsecuredFlag
FROM menu_item itm, MENU_PAGE PAG, BUSONLINE_APPS_CODE app, MENU_CATEGORY_CODE cat
WHERE PAG.MENU_PAGE_SEQ_NUM = ITM.MENU_PAGE_SEQ_NUM
  AND APP.CODE = PAG.APPLICATION_CODE
  AND CAT.CODE = ITM.MENU_CATEGORY_CODE
  AND NOT EXISTS (
      SELECT 1 FROM menu_security SEC
      WHERE SEC.MENU_ITEM_SEQ_NUM = ITM.MENU_ITEM_SEQ_NUM
  )
UNION
-- Part 3: user's active favorites
SELECT
    ITM.MENU_ITEM_SEQ_NUM AS MenuItemSeqNum, :MenuUser AS MenuUser,
    fav.menu_order_num AS MenuOrder, 'Y' AS FavoriteFlag,
    FAV.MENU_USER_EMPLID AS MenuUserEmplid,
    ITM.MENU_CATEGORY_CODE AS MenuCategoryCode, CAT.CODE_DESCR AS Category,
    ITM.TOOL_TIP_TXT AS ToolTip, ITM.VISIBLE_FLAG AS VisibleFlag,
    PAG.MENU_PAGE_SEQ_NUM AS MenuPageSeqNum, PAG.PAGE_NAME AS PageName,
    PAG.PAGE_LINK_TXT AS PageLink, PAG.APPLICATION_CODE AS ApplicationCode,
    APP.CODE_DESCR AS ApplicationPath, PAG.FAVORITE_NAME AS FavoriteName,
    PAG.SAVE_PREFERENCES_FLAG AS SavePreferencesFlag, PAG.PRINT_FLAG AS PrintFlag,
    PAG.DOWNLOAD_FLAG AS DownloadFlag, PAG.HELP_FLAG AS HelpFlag,
    PAG.SAVE_FLAG AS SaveFlag, PAG.INSERT_FLAG AS InsertFlag,
    PAG.DELETE_FLAG AS DeleteFlag, PAG.PRINT_TIP_TXT AS PrintTip,
    PAG.DOWNLOAD_TIP_TXT AS DownloadTip, PAG.SAVE_TOOLTIP_TXT AS SaveTip,
    PAG.DELETE_TOOLTIP_TXT AS DeleteTip, PAG.INSERT_TOOLTIP_TXT AS InsertTip,
    'N' as UnsecuredFlag
FROM menu_item itm, menu_favorite fav, MENU_PAGE PAG,
     BUSONLINE_APPS_CODE app, MENU_CATEGORY_CODE cat
WHERE ITM.MENU_ITEM_SEQ_NUM = FAV.MENU_ITEM_SEQ_NUM
  AND PAG.MENU_PAGE_SEQ_NUM = ITM.MENU_PAGE_SEQ_NUM
  AND APP.CODE = PAG.APPLICATION_CODE
  AND CAT.CODE = ITM.MENU_CATEGORY_CODE
  AND FAV.MENU_USER = :MenuUser
  AND FAV.ITEM_INACTIVE_DATE IS NULL
```

## Operations and processes
- [Menu authorization data lookup](../operations/menu-authorization-data-lookup.md)


## Business rules
- [BR-MNU-01: Menu item visibility determined by security-group or user mapping](../business-rules.md#br-mnu-01-menu-item-visibility-determined-by-security-group-or-user-mapping)
- [BR-MNU-04: Case-insensitive security-group matching](../business-rules.md#br-mnu-04-case-insensitive-security-group-matching)
- [BR-MNU-05: Favorite active-status enforcement](../business-rules.md#br-mnu-05-favorite-active-status-enforcement)


> **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.Menu\clsMenuAuthorizedItemCollection.vb`