Skip to content

SQL-020: Search users with optional filters

Assembles a dynamic search query against EXT_USER with outer joins to related tables and optional predicates appended at runtime based on caller-supplied criteria. Supports filtering by test-user flag, name, email, user type, status, access group, and customer ID.

Classification

Field Value
SQL ID SQL-020
Operation type Read
Execution method Inline SQL + conditional assembly
Database Oracle
Schema [unqualified]**
Command type Text
Business object clsUser
Criteria property Local SQL
Lifecycle method GetUsers(...) search overload

Parameters

Parameter Direction Type Max length Description
:TestUserFlag Input String 1 Required: filter by test-user status.
:AccessGroupCode Input String 5 Optional: filter by access group code.
:CustomerID Input String 30 Optional: filter by customer ID.
:Email Input String 100 Optional: filter by email address.
:Name Input String 50 Optional: filter by person name.
:StatusCode Input String 5 Optional: filter by user status.
:UserTypeCode Input String 5 Optional: filter by user type code.

Tables

Table Access Columns used
EXT_USER SELECT All EXT_USER columns, TEST_USER_FLAG
PERSON LEFT JOIN PERSON_SEQ_NUM, NAME
EXT_USER_TYPE LEFT JOIN EXT_USER_SEQ_NUM, EXT_USER_TYPE_CODE, COMPANY_SEQ_NUM, CUSTOMER_ID
EXT_USER_ACCESS_GROUP LEFT JOIN EXT_USER_TYPE_SEQ_NUM, EXT_USER_ACCESS_GROUP_CODE
COMPANY LEFT OUTER JOIN COMPANY_SEQ_NUM, COMPANY_NAME
PERSON_EMAIL LEFT OUTER JOIN PERSON_SEQ_NUM, EMAIL_ADDRESS_SEQ_NUM
EMAIL_ADDRESS LEFT OUTER JOIN EMAIL_ADDRESS_SEQ_NUM, EMAIL_ADDRESS

Sample SQL

SELECT * FROM (
    SELECT DISTINCT
        -- (all EXT_USER columns with aliases as in SQL-015),
        c.company_name AS SearchForCompanyName
    FROM EXT_USER
    LEFT JOIN person p ON p.person_seq_num = ext_user.person_seq_num
    LEFT JOIN ext_user_type t ON t.ext_user_seq_num = ext_user.ext_user_seq_num
    LEFT JOIN ext_user_access_group g
        ON g.EXT_USER_TYPE_SEQ_NUM = t.EXT_USER_TYPE_SEQ_NUM
    LEFT OUTER JOIN company c ON c.company_seq_num = t.company_seq_num
    LEFT OUTER JOIN person_email pe ON pe.person_seq_num = p.person_seq_num
    LEFT OUTER JOIN email_address e
        ON e.email_address_seq_num = pe.email_address_seq_num
    WHERE EXT_USER.test_user_flag = :TestUserFlag
    -- Optional predicates appended at runtime:
    -- AND g.ext_user_access_group_code = :AccessGroupCode
    -- AND (UPPER(t.customer_id) = :CustomerID OR UPPER(t.credential_1_id) = :CustomerID2)
    -- AND LOWER(e.email_address) LIKE :Email
    -- AND UPPER(p.name) LIKE :Name
    -- AND t.ext_user_type_code = :UserTypeCode
    -- AND ext_user.status_code = :StatusCode
)
-- Optional outer row limit: WHERE ROWNUM <= <limit>

Operations and processes

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\clsUser.vb