SQL-017: Retrieve forgotten user by customer and email¶

Retrieves an external user record by customer ID and email address, used in forgot-user (password recovery initiation) lookup workflows.

Classification¶

Field	Value
SQL ID	SQL-017
Operation type	Read
Execution method	StringBuilder / Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsUser`
Criteria property	`RetrieveSqlStatement + ForgottenUserWhere`
Lifecycle method	`GetUserBySourceSystemIdAndEmail`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:CustomerID`	Input	String	30	Source-system customer ID (upper-cased).
`:Email`	Input	String	100	Email address of the user.

Tables¶

Table	Access	Columns used
EXT_USER	SELECT	All EXT_USER columns
EXT_USER_TYPE	JOIN	`EXT_USER_SEQ_NUM`, `CUSTOMER_ID`
EMAIL_ADDRESS	JOIN	`EMAIL_ADDRESS_SEQ_NUM`, `EMAIL_ADDRESS`

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
JOIN ext_user_type utype ON utype.ext_user_seq_num = EXT_USER.ext_user_seq_num
JOIN email_address eAdd ON eAdd.EMAIL_ADDRESS_SEQ_NUM = EXT_USER.EMAIL_ADDRESS_SEQ_NUM
WHERE upper(utype.CUSTOMER_ID) = :CustomerID
  AND eAdd.EMAIL_ADDRESS = :Email

Operations and processes¶

User self-registration / account management

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