SQL-005: Insert external user

Inserts a new external portal user record into EXT_USER with 21 parameters covering identity, status, authorization, credential, and audit-trail fields. The AUTHORIZED_USER_FLAG column is set from :IsAuthorizedUser, as required by BR-EU-01.

Classification

Field	Value
SQL ID	SQL-005
Operation type	Create
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsUser
Criteria property	InsertSQL
Lifecycle method	BeforeInsert

Parameters

Parameter	Direction	Type	Max length	Description
:UserID	Input	Decimal	N/A	EXT_USER_SEQ_NUM — primary key.
:ContactID	Input	Decimal	N/A	PERSON_SEQ_NUM — linked person record.
:ActiveDirectoryID	Input	String	30	AD login identifier.
:StatusCode	Input	String	5	Account status (ACTIV, DIS, etc.).
:LastLogonDate	Input	DateTime	N/A	Date of last successful login.
:DisableDate	Input	DateTime	N/A	Date the account was disabled.
:PasswordChangeRequired	Input	String	1	Forced password-change flag.
:AcceptedUserAgreement	Input	String	1	User agreement acceptance flag.
:IsAuthorizedUser	Input	String	1	Authorized-user flag (see BR-EU-01).
:EmailID	Input	Decimal	N/A	EMAIL_ADDRESS_SEQ_NUM reference.
:EmailVerificationExpDate	Input	DateTime	N/A	Email verification token expiry.
:TestUserExpDate	Input	DateTime	N/A	Test user account expiry.
:ProfileUpdateDate	Input	DateTime	N/A	Date of last profile update.
:IsTestUser	Input	String	1	Test account flag.
:IsProdTestUser	Input	String	1	Production test account flag.
:IsRelatedToInjuredWorker	Input	String	1	HAS_CLAIM_FLAG.
:CreateUser	Input	String	30	Username creating the record.
:CreateDate	Input	DateTime	N/A	Record creation timestamp.
:UpdateUser	Input	String	30	Username of last update.
:UpdateDate	Input	DateTime	N/A	Last update timestamp.
:UpdateNum	Input	Decimal	N/A	Optimistic-concurrency counter (initial value).

Tables

Table	Access	Columns used
EXT_USER	INSERT	EXT_USER_SEQ_NUM, PERSON_SEQ_NUM, ACTIVE_DIRECTORY_ID, STATUS_CODE, LAST_LOGON_DATE, DISABLE_DATE, PASSWORD_CHANGE_REQUIRED_FLAG, ACCEPTED_USER_AGREEMENT_FLAG, AUTHORIZED_USER_FLAG, EMAIL_ADDRESS_SEQ_NUM, EMAIL_VERIFY_EXP_DATE, TEST_USER_EXP_DATE, PROFILE_UPDATE_DATE, TEST_USER_FLAG, TEST_USER_PROD_FLAG, HAS_CLAIM_FLAG, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, UPDATE_NUM

Sample SQL

INSERT INTO EXT_USER (
    EXT_USER_SEQ_NUM, PERSON_SEQ_NUM, ACTIVE_DIRECTORY_ID, STATUS_CODE,
    LAST_LOGON_DATE, DISABLE_DATE, PASSWORD_CHANGE_REQUIRED_FLAG,
    ACCEPTED_USER_AGREEMENT_FLAG, AUTHORIZED_USER_FLAG, EMAIL_ADDRESS_SEQ_NUM,
    EMAIL_VERIFY_EXP_DATE, TEST_USER_EXP_DATE, PROFILE_UPDATE_DATE,
    TEST_USER_FLAG, TEST_USER_PROD_FLAG, HAS_CLAIM_FLAG,
    CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, UPDATE_NUM
)
VALUES (
    :UserID, :ContactID, :ActiveDirectoryID, :StatusCode,
    :LastLogonDate, :DisableDate, :PasswordChangeRequired,
    :AcceptedUserAgreement, :IsAuthorizedUser, :EmailID,
    :EmailVerificationExpDate, :TestUserExpDate, :ProfileUpdateDate,
    :IsTestUser, :IsProdTestUser, :IsRelatedToInjuredWorker,
    :CreateUser, :CreateDate, :UpdateUser, :UpdateDate, :UpdateNum
)

Operations and processes

• User self-registration / account management

Business rules

• BR-EU-01: Authorized-user flag persistence
• 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