SQL-015: Retrieve user by key / base user projection¶

Retrieves a single external user record from EXT_USER by EXT_USER_SEQ_NUM. This is the base user projection used by BeforeRetrieve and the GetUsers() factory method.

Classification¶

Field	Value
SQL ID	SQL-015
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsUser`
Criteria property	`RetrieveSQL` / `RetrieveSqlStatement`
Lifecycle method	`BeforeRetrieve`; `GetUsers()`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:UserID`	Input	Decimal	N/A	`EXT_USER_SEQ_NUM` — primary key.

Tables¶

Table	Access	Columns used
EXT_USER	SELECT	All EXT_USER columns (see SQL-005 for column list)

Sample SQL¶

SELECT DISTINCT
    EXT_USER.EXT_USER_SEQ_NUM AS UserID,
    EXT_USER.PERSON_SEQ_NUM AS ContactID,
    EXT_USER.ACTIVE_DIRECTORY_ID AS ActiveDirectoryID,
    EXT_USER.STATUS_CODE AS StatusCode,
    EXT_USER.LAST_LOGON_DATE AS LastLogonDate,
    EXT_USER.PASSWORD_CHANGE_REQUIRED_FLAG AS PasswordChangeRequired,
    EXT_USER.ACCEPTED_USER_AGREEMENT_FLAG AS AcceptedUserAgreement,
    EXT_USER.AUTHORIZED_USER_FLAG AS IsAuthorizedUser,
    EXT_USER.EMAIL_ADDRESS_SEQ_NUM AS EmailID,
    EXT_USER.EMAIL_VERIFY_EXP_DATE AS EmailVerificationExpDate,
    EXT_USER.TEST_USER_EXP_DATE AS TestUserExpDate,
    EXT_USER.PROFILE_UPDATE_DATE AS ProfileUpdateDate,
    EXT_USER.TEST_USER_FLAG AS IsTestUser,
    EXT_USER.TEST_USER_PROD_FLAG AS IsProdTestUser,
    EXT_USER.HAS_CLAIM_FLAG AS IsRelatedToInjuredWorker,
    EXT_USER.CREATE_USER AS CreateUser,
    EXT_USER.CREATE_DATE AS CreateDate,
    EXT_USER.UPDATE_USER AS UpdateUser,
    EXT_USER.UPDATE_DATE AS UpdateDate,
    EXT_USER.UPDATE_NUM AS UpdateNum,
    EXT_USER.DISABLE_DATE AS DisableDate,
    '$SEARCH$' AS SearchForCompanyName
FROM EXT_USER
WHERE EXT_USER.EXT_USER_SEQ_NUM = :UserID

Operations and processes¶

User self-registration / account management

Business rules¶

BR-EU-01: Authorized-user flag persistence

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