---
title: SAIF.Business.External.Users database tables
summary: Database tables used by this library with relationships and usage context.
status: draft
owner: <owner>
last_reviewed: 2026-07-24
library: SAIF.Business.External.Users
reference_type: package
---

# SAIF.Business.External.Users database tables

This page catalogs database tables accessed by this library.

## Table catalog

| Table | Database | Schema/Link | CRUD | Purpose | Primary business object |
| --- | --- | --- | --- | --- | --- |
| [COMPANY](#company) | Oracle | [unqualified]** | R | Company identity lookup used in user-type joins | `clsUser` |
| [COUNTY](#county) | Oracle | [unqualified]** | R | County name and office-code mapping for zip code routing | `clsZipCode` |
| [DUAL](#dual) | Oracle | [unqualified]** | R | Oracle pseudo-table used for Oracle function calls | `clsNewUserArgs` |
| [EMAIL_ADDRESS](#email_address) | Oracle | [unqualified]** | R | Email address lookup joined to user records | `clsUser` |
| [EXT_USER](#ext_user) | Oracle | [unqualified]** | C/R/U | Core external portal user record | `clsUser`, `clsUserType`, `clsUserAccessGroup` |
| [EXT_USER_ACCESS_GROUP](#ext_user_access_group) | Oracle | [unqualified]** | C/R/U/D | Access-group membership rows for external users | `clsUserAccessGroup`, `clsUser` |
| [EXT_USER_ACCESS_GROUP_AD](#ext_user_access_group_ad) | Oracle | [unqualified]** | R | Mapping between access groups and AD groups | `clsUserAccessGroup` |
| [EXT_USER_ACCESS_GROUP_CODE](#ext_user_access_group_code) | Oracle | [unqualified]** | R | Code table for access-group descriptions | `clsUserAccessGroup` |
| [EXT_USER_ACCESS_GROUP_GCODE](#ext_user_access_group_gcode) | Oracle | [unqualified]** | R | Globalized descriptions for access-group codes | `clsUserAccessGroup` |
| [EXT_USER_AD_GROUP](#ext_user_ad_group) | Oracle | [unqualified]** | R | Active Directory group name lookup | `clsUserAccessGroup` |
| [EXT_USER_DENY_SELF_REG](#ext_user_deny_self_reg) | Oracle | [unqualified]** | C/R/U/D | Block-list entries that prevent self-registration | `clsUserDenySelfRegistration` |
| [EXT_USER_SEC_QUESTION_CODE](#ext_user_sec_question_code) | Oracle | [unqualified]** | R | Security question code definitions | `clsUserSecurityQuestion` |
| [EXT_USER_SEC_QUESTION_GCODE](#ext_user_sec_question_gcode) | Oracle | [unqualified]** | R | Globalized text for security question codes | `clsUserSecurityQuestion` |
| [EXT_USER_SECURITY_QUESTION](#ext_user_security_question) | Oracle | [unqualified]** | C/R/U/D | User-specific security question/answer records | `clsUserSecurityQuestion` |
| [EXT_USER_STATUS_CODE](#ext_user_status_code) | Oracle | [unqualified]** | R | Status code lookup for external users | `mdlCodeMaps` |
| [EXT_USER_TYPE](#ext_user_type) | Oracle | [unqualified]** | C/R/U/D | User type/role classification rows | `clsUserType`, `clsUser`, `clsUserAccessGroup` |
| [EXT_USER_TYPE_ACCESS_GROUP](#ext_user_type_access_group) | Oracle | [unqualified]** | R | Mapping of access groups allowed for each user type | `clsUserAccessGroup` |
| [EXT_USER_TYPE_CODE](#ext_user_type_code) | Oracle | [unqualified]** | R | Code table for user type definitions | `clsUserType`, `mdlCodeMaps` |
| [EXT_USER_TYPE_CODE_PROMPT](#ext_user_type_code_prompt) | Oracle | [unqualified]** | R | Prompt label definitions for user type codes | `clsUserType` |
| [OFFICE_CODE](#office_code) | Oracle | [unqualified]** | R | Office code descriptions and region mapping | `clsZipCode` |
| [PA_ASSIGN_OVERRIDE](#pa_assign_override) | Oracle | [unqualified]** | C/R/U | Policy-level audit assignment override records | `clsAuditAssignmentOverride` |
| [PA_AUDITOR](#pa_auditor) | Oracle | [unqualified]** | C/R/U | Premium auditor entity records | `clsAuditor`, `clsAuditorCollection`, `mdlAuditor`, `clsUser` |
| [PA_AUDITOR_ZIP_CODE](#pa_auditor_zip_code) | Oracle | [unqualified]** | C/R/U/D | Zip-code range assignments for auditors | `clsAuditorZipCode`, `clsAuditorZipCodeCollection` |
| [PA_ZIP_CODE](#pa_zip_code) | Oracle | [unqualified]** | R | Zip-code routing data (office, county, region) | `clsZipCode` |
| [PAYMENT_AUTHORIZATION](#payment_authorization) | Oracle | payment_owner | U | Payment authorization records updated with EFT contact email | `clsUserType` |
| [PERSON](#person) | Oracle | [unqualified]** | R | Person identity record linked to external users | `clsUser` |
| [PERSON_EMAIL](#person_email) | Oracle | [unqualified]** | R | Person-to-email address association | `clsUser` |
| [PROVIDER_EOB_INTERFACE](#provider_eob_interface) | Oracle | [unqualified]** | R | Provider EOB ownership check | `clsUserType` |
| [RTW_LOAD_CODE](#rtw_load_code) | Oracle | [unqualified]** | R | Return-to-work load codes for group association | `mdlGroupAssociation` |

---

## COMPANY

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Provides company name for external-user search result enrichment. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `COMPANY_SEQ_NUM`, `COMPANY_NAME` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_TYPE |
| **Business objects** | `clsUser` |

### SQL referencing this table
- [SQL-020: Search users with optional filters](sql/SQL-020-saif-business-external-users.md)

---

## COUNTY

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Provides county name and office code for zip-code routing lookups. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `COUNTY_CODE`, `COUNTY_NAME`, `OFFICE_CODE` |
| **Sequence** | N/A |
| **Related tables** | PA_ZIP_CODE, OFFICE_CODE |
| **Business objects** | `clsZipCode` |

### Operations referencing this table
- [Zip code routing lookup](operations/zip-code-routing-lookup.md)

### SQL referencing this table
- [SQL-037: Retrieve zip code mapping](sql/SQL-037-saif-business-external-users.md)

---

## DUAL

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Oracle pseudo-table used to execute function calls that return scalar values. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | N/A (pseudo-table) |
| **Sequence** | N/A |
| **Related tables** | N/A |
| **Business objects** | `clsNewUserArgs` |

### Operations referencing this table
- [Provider registration validation](operations/provider-registration-validation.md)

### SQL referencing this table
- [SQL-014: Validate provider registration](sql/SQL-014-saif-business-external-users.md)

---

## EMAIL_ADDRESS

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Stores email addresses linked to external users via `EMAIL_ADDRESS_SEQ_NUM`. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `EMAIL_ADDRESS_SEQ_NUM`, `EMAIL_ADDRESS` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER, PERSON_EMAIL |
| **Business objects** | `clsUser` |

### SQL referencing this table
- [SQL-016: Detect duplicate active email](sql/SQL-016-saif-business-external-users.md)
- [SQL-017: Retrieve forgotten user by customer and email](sql/SQL-017-saif-business-external-users.md)
- [SQL-018: Retrieve provider identity by user type](sql/SQL-018-saif-business-external-users.md)
- [SQL-020: Search users with optional filters](sql/SQL-020-saif-business-external-users.md)

---

## EXT_USER

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Core record for external (non-employee) portal users, holding identity, status, flags, and credential references. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Create, Read, Update |
| **Key columns used** | `EXT_USER_SEQ_NUM`, `PERSON_SEQ_NUM`, `ACTIVE_DIRECTORY_ID`, `STATUS_CODE`, `AUTHORIZED_USER_FLAG`, `EMAIL_ADDRESS_SEQ_NUM`, `TEST_USER_FLAG`, `HAS_CLAIM_FLAG`, `UPDATE_NUM` |
| **Sequence** | `EXT_USER_SEQ`* |
| **Related tables** | EXT_USER_TYPE, EMAIL_ADDRESS, PERSON |
| **Business objects** | `clsUser`, `clsUserType`, `clsUserAccessGroup` |

### Operations referencing this table
- [User self-registration / account management](operations/user-account-management.md)
- [User search](operations/user-search.md)

### Business rules referencing this table
- [BR-EU-01: Authorized-user flag persistence](business-rules.md#br-eu-01-authorized-user-flag-persistence)
- [BR-EU-04: Unique active email per non-test user](business-rules.md#br-eu-04-unique-active-email-per-non-test-user)
- [BR-EU-05: Optimistic concurrency via UPDATE_NUM](business-rules.md#br-eu-05-optimistic-concurrency-via-update_num)

### SQL referencing this table
- [SQL-005: Insert external user](sql/SQL-005-saif-business-external-users.md)
- [SQL-015: Retrieve user by key / base user projection](sql/SQL-015-saif-business-external-users.md)
- [SQL-016: Detect duplicate active email](sql/SQL-016-saif-business-external-users.md)
- [SQL-017: Retrieve forgotten user by customer and email](sql/SQL-017-saif-business-external-users.md)
- [SQL-019: Retrieve users by type and customer group](sql/SQL-019-saif-business-external-users.md)
- [SQL-020: Search users with optional filters](sql/SQL-020-saif-business-external-users.md)
- [SQL-045: Update external user](sql/SQL-045-saif-business-external-users.md)
- [SQL-046: Update last logon date](sql/SQL-046-saif-business-external-users.md)

---

## EXT_USER_ACCESS_GROUP

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Persists access-group membership rows for a user type, including expiration and default-access flag. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Create, Read, Update, Delete |
| **Key columns used** | `EXT_USER_ACCESS_GROUP_SEQ_NUM`, `EXT_USER_TYPE_SEQ_NUM`, `EXT_USER_ACCESS_GROUP_CODE`, `DEFAULT_ACCESS_FLAG`, `EXPIRATION_DATE`, `UPDATE_NUM` |
| **Sequence** | `EXT_USER_ACCESS_GROUP_SEQ`* |
| **Related tables** | EXT_USER_TYPE, EXT_USER_ACCESS_GROUP_CODE, EXT_USER_TYPE_ACCESS_GROUP |
| **Business objects** | `clsUserAccessGroup`, `clsUser` |

### Operations referencing this table
- [User access group management](operations/user-access-group-management.md)

### Business rules referencing this table
- [BR-EU-05: Optimistic concurrency via UPDATE_NUM](business-rules.md#br-eu-05-optimistic-concurrency-via-update_num)

### SQL referencing this table
- [SQL-006: Insert user access group](sql/SQL-006-saif-business-external-users.md)
- [SQL-022: Retrieve user access group row](sql/SQL-022-saif-business-external-users.md)
- [SQL-024: Retrieve all possible access groups for a user type](sql/SQL-024-saif-business-external-users.md)
- [SQL-047: Update user access group](sql/SQL-047-saif-business-external-users.md)
- [SQL-053: Delete user access group](sql/SQL-053-saif-business-external-users.md)

---

## EXT_USER_ACCESS_GROUP_AD

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Maps access-group codes to Active Directory group sequence numbers. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `EXT_USER_AD_GROUP_SEQ_NUM`, `EXT_USER_ACCESS_GROUP_CODE`, `EXT_USER_TYPE_CODE` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_AD_GROUP, EXT_USER_ACCESS_GROUP |
| **Business objects** | `clsUserAccessGroup` |

### SQL referencing this table
- [SQL-021: Retrieve AD groups for an access group](sql/SQL-021-saif-business-external-users.md)
- [SQL-025: Find newly-added access groups from AD](sql/SQL-025-saif-business-external-users.md)
- [SQL-026: Find removed access groups from AD](sql/SQL-026-saif-business-external-users.md)

---

## EXT_USER_ACCESS_GROUP_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Code table providing descriptions for access-group codes. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_ACCESS_GROUP, EXT_USER_TYPE_ACCESS_GROUP, EXT_USER_ACCESS_GROUP_GCODE |
| **Business objects** | `clsUserAccessGroup` |

### SQL referencing this table
- [SQL-022: Retrieve user access group row](sql/SQL-022-saif-business-external-users.md)
- [SQL-023: Retrieve all access-group definitions](sql/SQL-023-saif-business-external-users.md)
- [SQL-024: Retrieve all possible access groups for a user type](sql/SQL-024-saif-business-external-users.md)

---

## EXT_USER_ACCESS_GROUP_GCODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Provides globalized (culture-specific) descriptions for access-group codes. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CULTURE_CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_ACCESS_GROUP_CODE |
| **Business objects** | `clsUserAccessGroup` |

### SQL referencing this table
- [SQL-022: Retrieve user access group row](sql/SQL-022-saif-business-external-users.md)
- [SQL-023: Retrieve all access-group definitions](sql/SQL-023-saif-business-external-users.md)
- [SQL-025: Find newly-added access groups from AD](sql/SQL-025-saif-business-external-users.md)

---

## EXT_USER_AD_GROUP

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Stores Active Directory group names used for access-group reconciliation. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `EXT_USER_AD_GROUP_SEQ_NUM`, `AD_GROUP_NAME`, `EXT_USER_TYPE_CODE` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_ACCESS_GROUP_AD |
| **Business objects** | `clsUserAccessGroup` |

### Operations referencing this table
- [User access group management](operations/user-access-group-management.md)

### SQL referencing this table
- [SQL-021: Retrieve AD groups for an access group](sql/SQL-021-saif-business-external-users.md)
- [SQL-025: Find newly-added access groups from AD](sql/SQL-025-saif-business-external-users.md)
- [SQL-026: Find removed access groups from AD](sql/SQL-026-saif-business-external-users.md)

---

## EXT_USER_DENY_SELF_REG

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Block-list entries preventing self-registration for specific user-type/customer-group combinations. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Create, Read, Update, Delete |
| **Key columns used** | `CUSTOMER_GROUP_ID`, `EXT_USER_TYPE_CODE`, `CUSTOMER_REQUESTED_FLAG`, `REASON_TXT`, `UPDATE_NUM` |
| **Sequence** | N/A |
| **Related tables** | N/A |
| **Business objects** | `clsUserDenySelfRegistration` |

### Operations referencing this table
- [Deny self-registration check](operations/deny-self-registration-check.md)

### Business rules referencing this table
- [BR-EU-02: Deny-self-registration block-list](business-rules.md#br-eu-02-deny-self-registration-block-list)
- [BR-EU-05: Optimistic concurrency via UPDATE_NUM](business-rules.md#br-eu-05-optimistic-concurrency-via-update_num)

### SQL referencing this table
- [SQL-007: Insert deny-self-registration row](sql/SQL-007-saif-business-external-users.md)
- [SQL-027: Retrieve deny-self-registration row](sql/SQL-027-saif-business-external-users.md)
- [SQL-048: Update deny-self-registration row](sql/SQL-048-saif-business-external-users.md)
- [SQL-054: Delete deny-self-registration row](sql/SQL-054-saif-business-external-users.md)

---

## EXT_USER_SEC_QUESTION_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Defines available security question codes and their text, including an optional expiration date. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `QUESTION_TEXT`, `EXPIRATION_DATE` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_SECURITY_QUESTION, EXT_USER_SEC_QUESTION_GCODE |
| **Business objects** | `clsUserSecurityQuestion` |

### SQL referencing this table
- [SQL-028: Retrieve security question row(s)](sql/SQL-028-saif-business-external-users.md)
- [SQL-029: Retrieve available security questions](sql/SQL-029-saif-business-external-users.md)

---

## EXT_USER_SEC_QUESTION_GCODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Provides globalized (culture-specific) question text for security question codes. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CULTURE_CODE`, `QUESTION_TEXT` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_SEC_QUESTION_CODE |
| **Business objects** | `clsUserSecurityQuestion` |

### SQL referencing this table
- [SQL-028: Retrieve security question row(s)](sql/SQL-028-saif-business-external-users.md)
- [SQL-029: Retrieve available security questions](sql/SQL-029-saif-business-external-users.md)

---

## EXT_USER_SECURITY_QUESTION

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Stores each user's chosen security question and answer for password recovery. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Create, Read, Update, Delete |
| **Key columns used** | `EXT_USER_SEC_QUESTION_SEQ_NUM`, `EXT_USER_SEQ_NUM`, `EXT_USER_SEC_QUESTION_CODE`, `ANSWER_TEXT`, `UPDATE_NUM` |
| **Sequence** | `EXT_USER_SEC_QUESTION_SEQ`* |
| **Related tables** | EXT_USER, EXT_USER_SEC_QUESTION_CODE |
| **Business objects** | `clsUserSecurityQuestion` |

### Operations referencing this table
- [Security question management](operations/security-question-management.md)

### Business rules referencing this table
- [BR-EU-05: Optimistic concurrency via UPDATE_NUM](business-rules.md#br-eu-05-optimistic-concurrency-via-update_num)

### SQL referencing this table
- [SQL-008: Insert security question](sql/SQL-008-saif-business-external-users.md)
- [SQL-028: Retrieve security question row(s)](sql/SQL-028-saif-business-external-users.md)
- [SQL-049: Update security question](sql/SQL-049-saif-business-external-users.md)
- [SQL-055: Delete security question](sql/SQL-055-saif-business-external-users.md)

---

## EXT_USER_STATUS_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Code table for external user status values (e.g., ACTIV, PENDA, DIS). |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | N/A |
| **Business objects** | `mdlCodeMaps` |

### SQL referencing this table
- [SQL-039: Load user-status code map](sql/SQL-039-saif-business-external-users.md)

---

## EXT_USER_TYPE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | User type/role classification row linking a user to a source-system context (customer ID, credentials, flags). |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Create, Read, Update, Delete |
| **Key columns used** | `EXT_USER_TYPE_SEQ_NUM`, `EXT_USER_SEQ_NUM`, `EXT_USER_TYPE_CODE`, `COMPANY_SEQ_NUM`, `CUSTOMER_ID`, `CUSTOMER_GROUP_ID`, `PRIMARY_TYPE_FLAG`, `ADMIN_USER_FLAG`, `STEWARD_FLAG`, `UPDATE_NUM` |
| **Sequence** | `EXT_USER_TYPE_SEQ`* |
| **Related tables** | EXT_USER, EXT_USER_TYPE_CODE, EXT_USER_ACCESS_GROUP |
| **Business objects** | `clsUserType`, `clsUser`, `clsUserAccessGroup` |

### Operations referencing this table
- [User type management](operations/user-type-management.md)

### Business rules referencing this table
- [BR-EU-04: Unique active email per non-test user](business-rules.md#br-eu-04-unique-active-email-per-non-test-user)
- [BR-EU-05: Optimistic concurrency via UPDATE_NUM](business-rules.md#br-eu-05-optimistic-concurrency-via-update_num)

### SQL referencing this table
- [SQL-009: Insert user type](sql/SQL-009-saif-business-external-users.md)
- [SQL-030: Retrieve user type row](sql/SQL-030-saif-business-external-users.md)
- [SQL-031: Retrieve all user type definitions](sql/SQL-031-saif-business-external-users.md)
- [SQL-050: Update user type](sql/SQL-050-saif-business-external-users.md)
- [SQL-056: Delete user type](sql/SQL-056-saif-business-external-users.md)

---

## EXT_USER_TYPE_ACCESS_GROUP

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Defines which access groups are available for each user type, with admin, steward, and self-registration flags. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `EXT_USER_TYPE_CODE`, `EXT_USER_ACCESS_GROUP_CODE`, `ADMIN_ACCESS_FLAG`, `STEWARD_ACCESS_FLAG`, `SELF_REG_ACCESS_FLAG`, `SYSTEM_CONTROLLED_FLAG`, `DEFAULT_STATUS_FLAG`, `SORT_ORDER_NUM` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_TYPE, EXT_USER_ACCESS_GROUP_CODE |
| **Business objects** | `clsUserAccessGroup` |

### SQL referencing this table
- [SQL-022: Retrieve user access group row](sql/SQL-022-saif-business-external-users.md)
- [SQL-023: Retrieve all access-group definitions](sql/SQL-023-saif-business-external-users.md)
- [SQL-024: Retrieve all possible access groups for a user type](sql/SQL-024-saif-business-external-users.md)

---

## EXT_USER_TYPE_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Master code table for user type definitions including source-system ID descriptions. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CUSTOMER_ID_DESCR`, `CUSTOMER_GROUP_ID_DESCR` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_TYPE, EXT_USER_TYPE_CODE_PROMPT |
| **Business objects** | `clsUserType`, `mdlCodeMaps` |

### SQL referencing this table
- [SQL-030: Retrieve user type row](sql/SQL-030-saif-business-external-users.md)
- [SQL-031: Retrieve all user type definitions](sql/SQL-031-saif-business-external-users.md)
- [SQL-038: Load user-type code map](sql/SQL-038-saif-business-external-users.md)

---

## EXT_USER_TYPE_CODE_PROMPT

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Provides UI prompt label descriptions for specific user type code fields (e.g., customer ID prompt). |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `EXT_USER_TYPE_CODE`, `PROMPT_CODE`, `PROMPT_DESCR` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_TYPE_CODE |
| **Business objects** | `clsUserType` |

### SQL referencing this table
- [SQL-030: Retrieve user type row](sql/SQL-030-saif-business-external-users.md)
- [SQL-031: Retrieve all user type definitions](sql/SQL-031-saif-business-external-users.md)

---

## OFFICE_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Office code descriptions and policy region codes used in zip code routing. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR`, `POLICY_REGION_CODE` |
| **Sequence** | N/A |
| **Related tables** | COUNTY, PA_ZIP_CODE |
| **Business objects** | `clsZipCode` |

### Operations referencing this table
- [Zip code routing lookup](operations/zip-code-routing-lookup.md)

### SQL referencing this table
- [SQL-037: Retrieve zip code mapping](sql/SQL-037-saif-business-external-users.md)

---

## PA_ASSIGN_OVERRIDE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Policy-level overrides specifying the default auditor and a do-not-assign auditor for audit assignment. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Create, Read, Update |
| **Key columns used** | `POLICY_NUM`, `DEFAULT_PA_AUDITOR_ID`, `DONOT_ASSIGN_PA_AUDITOR_ID`, `CREATE_USER`, `CREATE_DATE`, `UPDATE_USER`, `UPDATE_DATE` |
| **Sequence** | N/A |
| **Related tables** | PA_AUDITOR |
| **Business objects** | `clsAuditAssignmentOverride` |

### Operations referencing this table
- [Auditor assignment / override](operations/auditor-assignment-override.md)

### Business rules referencing this table
- [BR-EU-03: Audit-assignment auditor distinctness](business-rules.md#br-eu-03-audit-assignment-auditor-distinctness)

### SQL referencing this table
- [SQL-002: Insert audit assignment override](sql/SQL-002-saif-business-external-users.md)
- [SQL-010: Retrieve audit assignment override](sql/SQL-010-saif-business-external-users.md)
- [SQL-042: Update audit assignment override](sql/SQL-042-saif-business-external-users.md)

---

## PA_AUDITOR

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Premium auditor entity records used for claim audit assignment, including scheduling and filter flags. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Create, Read, Update |
| **Key columns used** | `PA_AUDITOR_ID`, `ASSIGN_FLAG`, `AUDITOR_NAME`, `AUDITOR_SHORT_NAME`, `USER_ID`, `VENDOR_NUM`, `FILTER_LIST_FLAG`, `SCHEDULE_FLAG`, `REVIEWER_FLAG`, `ACTIVE_REVIEWER_FLAG`, `MONTHLY_MAXIMUM_COUNT` |
| **Sequence** | N/A (uses `MAX(PA_AUDITOR_ID) + 1` via SQL-001) |
| **Related tables** | PA_AUDITOR_ZIP_CODE, PA_ASSIGN_OVERRIDE |
| **Business objects** | `clsAuditor`, `clsAuditorCollection`, `mdlAuditor`, `clsUser` |

### Operations referencing this table
- [Auditor assignment / override](operations/auditor-assignment-override.md)
- [Premium auditor lookup](operations/premium-auditor-lookup.md)

### SQL referencing this table
- [SQL-001: Max PA auditor ID + 1](sql/SQL-001-saif-business-external-users.md)
- [SQL-003: Insert auditor](sql/SQL-003-saif-business-external-users.md)
- [SQL-011: Retrieve auditor by ID or user](sql/SQL-011-saif-business-external-users.md)
- [SQL-012: Retrieve auditors by vendor/flags](sql/SQL-012-saif-business-external-users.md)
- [SQL-041: Load auditor by AD user and vendor; fallback by vendor](sql/SQL-041-saif-business-external-users.md)
- [SQL-043: Update auditor](sql/SQL-043-saif-business-external-users.md)

---

## PA_AUDITOR_ZIP_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Associates auditors with specific zip codes defining their geographic assignment range. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Create, Read, Update, Delete |
| **Key columns used** | `PA_AUDITOR_ID`, `ZIP_CODE` |
| **Sequence** | N/A |
| **Related tables** | PA_AUDITOR |
| **Business objects** | `clsAuditorZipCode`, `clsAuditorZipCodeCollection` |

### Operations referencing this table
- [Auditor assignment / override](operations/auditor-assignment-override.md)

### SQL referencing this table
- [SQL-004: Insert auditor zip code](sql/SQL-004-saif-business-external-users.md)
- [SQL-013: Retrieve auditor zip codes](sql/SQL-013-saif-business-external-users.md)
- [SQL-044: Update auditor zip code](sql/SQL-044-saif-business-external-users.md)
- [SQL-052: Delete auditor zip code](sql/SQL-052-saif-business-external-users.md)

---

## PA_ZIP_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Maps zip codes to audit office codes and county codes for audit routing. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `ZIP_CODE`, `AUDIT_OFFICE_CODE`, `STATE_CODE`, `COUNTY_CODE` |
| **Sequence** | N/A |
| **Related tables** | COUNTY, OFFICE_CODE |
| **Business objects** | `clsZipCode` |

### Operations referencing this table
- [Zip code routing lookup](operations/zip-code-routing-lookup.md)

### SQL referencing this table
- [SQL-037: Retrieve zip code mapping](sql/SQL-037-saif-business-external-users.md)

---

## PAYMENT_AUTHORIZATION

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | payment_owner |
| **Purpose** | Payment authorization records; this library updates the `CUSTOMER_CONTACT_EMAIL_ADDR` column for provider EFT authorization. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Update |
| **Key columns used** | `CUSTOMER_CONTACT_EMAIL_ADDR`, `CUSTOMER_ID`, `UPDATE_DATE`, `UPDATE_USER` |
| **Sequence** | N/A |
| **Related tables** | N/A |
| **Business objects** | `clsUserType` |

### Operations referencing this table
- [User type management](operations/user-type-management.md)

### SQL referencing this table
- [SQL-051: Update EFT authorization email](sql/SQL-051-saif-business-external-users.md)

---

## PERSON

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Person identity record linked to external users via `PERSON_SEQ_NUM`; provides the person name for user retrieval. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `PERSON_SEQ_NUM`, `NAME` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER, PERSON_EMAIL |
| **Business objects** | `clsUser` |

### SQL referencing this table
- [SQL-018: Retrieve provider identity by user type](sql/SQL-018-saif-business-external-users.md)
- [SQL-020: Search users with optional filters](sql/SQL-020-saif-business-external-users.md)

---

## PERSON_EMAIL

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Associates person records with email address records; used in user search joins. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `PERSON_SEQ_NUM`, `EMAIL_ADDRESS_SEQ_NUM` |
| **Sequence** | N/A |
| **Related tables** | PERSON, EMAIL_ADDRESS |
| **Business objects** | `clsUser` |

### SQL referencing this table
- [SQL-020: Search users with optional filters](sql/SQL-020-saif-business-external-users.md)

---

## PROVIDER_EOB_INTERFACE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Provider EOB (Explanation of Benefits) interface records; used to check whether a user owns any EOB rows. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `EXT_USER_TYPE_SEQ_NUM` |
| **Sequence** | N/A |
| **Related tables** | EXT_USER_TYPE |
| **Business objects** | `clsUserType` |

### SQL referencing this table
- [SQL-036: Check whether user owns provider EOB rows](sql/SQL-036-saif-business-external-users.md)

---

## RTW_LOAD_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | [unqualified]** |
| **Purpose** | Return-to-work load code values used by the group-association module. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE` |
| **Sequence** | N/A |
| **Related tables** | N/A |
| **Business objects** | `mdlGroupAssociation` |

### SQL referencing this table
- [SQL-040: Load RTW codes](sql/SQL-040-saif-business-external-users.md)

---

## Table relationship diagram

The following diagram shows key relationships between core tables used by this library.

```mermaid
erDiagram
    EXT_USER ||--o{ EXT_USER_TYPE : "EXT_USER_SEQ_NUM"
    EXT_USER ||--o{ EXT_USER_SECURITY_QUESTION : "EXT_USER_SEQ_NUM"
    EXT_USER_TYPE ||--o{ EXT_USER_ACCESS_GROUP : "EXT_USER_TYPE_SEQ_NUM"
    EXT_USER ||--o| EMAIL_ADDRESS : "EMAIL_ADDRESS_SEQ_NUM"
    EXT_USER ||--o| PERSON : "PERSON_SEQ_NUM"
    PERSON ||--o{ PERSON_EMAIL : "PERSON_SEQ_NUM"
    PERSON_EMAIL }o--|| EMAIL_ADDRESS : "EMAIL_ADDRESS_SEQ_NUM"
    EXT_USER_TYPE }o--|| EXT_USER_TYPE_CODE : "EXT_USER_TYPE_CODE"
    EXT_USER_TYPE_CODE ||--o{ EXT_USER_TYPE_CODE_PROMPT : "EXT_USER_TYPE_CODE"
    EXT_USER_ACCESS_GROUP }o--|| EXT_USER_ACCESS_GROUP_CODE : "EXT_USER_ACCESS_GROUP_CODE"
    EXT_USER_SECURITY_QUESTION }o--|| EXT_USER_SEC_QUESTION_CODE : "EXT_USER_SEC_QUESTION_CODE"
    EXT_USER_ACCESS_GROUP_AD }o--|| EXT_USER_AD_GROUP : "EXT_USER_AD_GROUP_SEQ_NUM"
    PA_AUDITOR ||--o{ PA_AUDITOR_ZIP_CODE : "PA_AUDITOR_ID"
    PA_ZIP_CODE }o--o| COUNTY : "COUNTY_CODE"
    COUNTY }o--|| OFFICE_CODE : "OFFICE_CODE"
    EXT_USER_TYPE }o--o| COMPANY : "COMPANY_SEQ_NUM"
```

## Related documentation
- [Index](index.md)
- [Overview](overview.md)
- [Architecture](architecture.md)
- [Dependencies](dependencies.md)
- [Operations and processes](operations-processes.md)
- [Business rules](business-rules.md)
- [SQL catalog](sql/index.md)
- [API reference](api/index.md)
- [Configuration](configuration.md)

> **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`
- `Business\SAIF.Business.External.Users\clsAuditor.vb`
- `Business\SAIF.Business.External.Users\clsAuditorCollection.vb`
- `Business\SAIF.Business.External.Users\clsAuditAssignmentOverride.vb`
- `Business\SAIF.Business.External.Users\clsAuditorZipCode.vb`
- `Business\SAIF.Business.External.Users\clsAuditorZipCodeCollection.vb`
- `Business\SAIF.Business.External.Users\clsUserAccessGroup.vb`
- `Business\SAIF.Business.External.Users\clsUserDenySelfRegistration.vb`
- `Business\SAIF.Business.External.Users\clsUserSecurityQuestion.vb`
- `Business\SAIF.Business.External.Users\clsUserType.vb`
- `Business\SAIF.Business.External.Users\clsZipCode.vb`
- `Business\SAIF.Business.External.Users\mdlCodeMaps.vb`
- `Business\SAIF.Business.External.Users\mdlGroupAssociation.vb`