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.

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	SELECT / JOIN	MENU_ITEM_SEQ_NUM, PAGE_ORDER_NUM, MENU_CATEGORY_CODE, TOOL_TIP_TXT, VISIBLE_FLAG, MENU_PAGE_SEQ_NUM
MENU_SECURITY	JOIN	MENU_ITEM_SEQ_NUM, SECURITY_GROUP_TXT
MENU_SECURITY_USER	JOIN	MENU_USER, MENU_USER_EMPLID, SECURITY_GROUP_TXT
MENU_PAGE	JOIN	MENU_PAGE_SEQ_NUM, PAGE_NAME, PAGE_LINK_TXT, APPLICATION_CODE, FAVORITE_NAME, all flag and tip columns
BUSONLINE_APPS_CODE	JOIN	CODE, CODE_DESCR
MENU_CATEGORY_CODE	JOIN	CODE, CODE_DESCR

Sample 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

Business rules

• BR-MNU-01: Menu item visibility determined by security-group or user mapping
• 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