SQL-016: Detect duplicate active email

Queries EXT_USER, EMAIL_ADDRESS, and EXT_USER_TYPE to detect whether an active, non-deleted, non-test user already exists with the given email address. Used to enforce BR-EU-04.

Classification

Field	Value
SQL ID	SQL-016
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsUser
Criteria property	Local SQL
Lifecycle method	Validation logic

Parameters

Parameter	Direction	Type	Max length	Description
:Email	Input	String	100	Email address to check for duplicates.

Tables

Table	Access	Columns used
EXT_USER	SELECT	ACTIVE_DIRECTORY_ID, TEST_USER_FLAG, STATUS_CODE, EMAIL_ADDRESS_SEQ_NUM
EMAIL_ADDRESS	JOIN	EMAIL_ADDRESS_SEQ_NUM, EMAIL_ADDRESS
EXT_USER_TYPE	JOIN	EXT_USER_SEQ_NUM, EXT_USER_TYPE_CODE, CUSTOMER_ID, CUSTOMER_GROUP_ID

Sample SQL

SELECT
    u.active_directory_id,
    e.email_address,
    t.ext_user_type_code,
    UPPER(t.customer_id) AS customer_id,
    UPPER(t.customer_group_id) AS customer_group_id
FROM ext_user u
JOIN email_address e ON e.email_address_seq_num = u.email_address_seq_num
JOIN ext_user_type t ON t.ext_user_seq_num = u.ext_user_seq_num
WHERE u.test_user_flag = 'N'
  AND u.status_code <> 'DEL'
  AND LOWER(email_address) = :Email

Operations and processes

• User self-registration / account management

Business rules

• BR-EU-04: Unique active email per non-test user

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