---
title: "SQL-015: Retrieve all authorized menu items (UNION query)"
summary: Two-part UNION query returning all secured and unsecured menu items for all users.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business.Menu
reference_type: package
---

# SQL-015: Retrieve all authorized menu items (UNION query)

Executes a two-part UNION query joining `MENU_ITEM`, `MENU_SECURITY`, `MENU_SECURITY_USER`, `MENU_PAGE`, `BUSONLINE_APPS_CODE`, and `MENU_CATEGORY_CODE` to return the complete authorized-item projection without user-specific filtering. Used when `MenuUser` is empty. Applies case-insensitive security-group matching per [BR-MNU-04](../business-rules.md#br-mnu-04-case-insensitive-security-group-matching).

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-015 |
| **Operation type** | Read |
| **Execution method** | Inline (StringBuilder) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsMenuAuthorizedItemCollection` |
| **Criteria property** | `RetrieveAllSQL` |
| **Lifecycle method** | `BeforeRetrieve` (MenuUser empty) / `AfterRetrieve` |

## Parameters

No parameters.

## 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` |

## Sample SQL

```sql
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)
UNION
SELECT
    ITM.MENU_ITEM_SEQ_NUM AS MenuItemSeqNum,
    'UNSECURED' 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
  )
```

## 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)


> **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`