SQL-019: Retrieve users by type and customer group¶

Retrieves external user records from EXT_USER joined to EXT_USER_TYPE where the user type code and source-system group ID match the given parameters.

Classification¶

Field	Value
SQL ID	SQL-019
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsUser`
Criteria property	Local SQL
Lifecycle method	`GetUsers(inUserType, inSourceSystemGroupID)`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:SourceSystemGroupID`	Input	String	30	Customer group ID (upper-cased comparison).
`:UserTypeCode`	Input	String	5	User type code (upper-cased comparison).

Tables¶

Table	Access	Columns used
EXT_USER	SELECT	All EXT_USER columns
EXT_USER_TYPE	JOIN	`EXT_USER_SEQ_NUM`, `CUSTOMER_GROUP_ID`, `EXT_USER_TYPE_CODE`

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 t
    ON t.ext_user_seq_num = ext_user.ext_user_seq_num
    AND upper(t.customer_group_id) = :SourceSystemGroupID
    AND upper(t.ext_user_type_code) = :UserTypeCode

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