SQL-018: Retrieve provider identity by user type¶

Retrieves the Active Directory ID, email address, and person name for an external user type, used in provider identity lookup workflows.

Classification¶

Field	Value
SQL ID	SQL-018
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsUser`
Criteria property	`RetrieveProviderSqlStatement`
Lifecycle method	`GetProvider`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:ExtUserTypeSeqNum`	Input	Decimal	N/A	`EXT_USER_TYPE_SEQ_NUM` — user type primary key.

Tables¶

Table	Access	Columns used
EXT_USER_TYPE	SELECT	`EXT_USER_TYPE_SEQ_NUM`, `EXT_USER_SEQ_NUM`
EXT_USER	JOIN	`EXT_USER_SEQ_NUM`, `ACTIVE_DIRECTORY_ID`, `EMAIL_ADDRESS_SEQ_NUM`, `PERSON_SEQ_NUM`
EMAIL_ADDRESS	JOIN	`EMAIL_ADDRESS_SEQ_NUM`, `EMAIL_ADDRESS`
PERSON	JOIN	`PERSON_SEQ_NUM`, `NAME`

Sample SQL¶

SELECT
    EXT_USER.ACTIVE_DIRECTORY_ID AS ActiveDirectoryID,
    EMAIL_ADDRESS.EMAIL_ADDRESS AS EmailAddress,
    PERSON.NAME AS Name
FROM EXT_USER_TYPE
JOIN EXT_USER
    ON EXT_USER_TYPE.EXT_USER_SEQ_NUM = EXT_USER.EXT_USER_SEQ_NUM
JOIN EMAIL_ADDRESS
    ON EXT_USER.EMAIL_ADDRESS_SEQ_NUM = EMAIL_ADDRESS.EMAIL_ADDRESS_SEQ_NUM
JOIN PERSON
    ON EXT_USER.PERSON_SEQ_NUM = PERSON.PERSON_SEQ_NUM
WHERE ext_user_type.ext_user_type_seq_num = :ExtUserTypeSeqNum

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