SAIF.Business.External.Users SQL catalog

This page indexes all SQL statements and stored procedure calls in this library, grouped by CRUD type.

Sequence operations

SQL ID	Description	Sequence	Tables	Business object
SQL-001	Max PA auditor ID + 1	N/A (MAX + 1 pattern)	PA_AUDITOR	clsAuditor

Create operations

SQL ID	Description	Type	Tables	Business object
SQL-002	Insert audit assignment override	Inline	PA_ASSIGN_OVERRIDE	clsAuditAssignmentOverride
SQL-003	Insert auditor	Inline	PA_AUDITOR	clsAuditor
SQL-004	Insert auditor zip code	Inline	PA_AUDITOR_ZIP_CODE	clsAuditorZipCode
SQL-005	Insert external user	Inline	EXT_USER	clsUser
SQL-006	Insert user access group	Inline	EXT_USER_ACCESS_GROUP	clsUserAccessGroup
SQL-007	Insert deny-self-registration row	Inline	EXT_USER_DENY_SELF_REG	clsUserDenySelfRegistration
SQL-008	Insert security question	Inline	EXT_USER_SECURITY_QUESTION	clsUserSecurityQuestion
SQL-009	Insert user type	Inline	EXT_USER_TYPE	clsUserType

Read operations

SQL ID	Description	Type	Tables	Business object
SQL-010	Retrieve audit assignment override	Inline	PA_ASSIGN_OVERRIDE	clsAuditAssignmentOverride
SQL-011	Retrieve auditor by ID or user	Inline	PA_AUDITOR	clsAuditor
SQL-012	Retrieve auditors by vendor/flags	Inline	PA_AUDITOR	clsAuditorCollection
SQL-013	Retrieve auditor zip codes	Inline	PA_AUDITOR_ZIP_CODE	clsAuditorZipCodeCollection
SQL-014	Validate provider registration (Oracle function)	Inline + Oracle function	DUAL	clsNewUserArgs
SQL-015	Retrieve user by key / base user projection	Inline	EXT_USER	clsUser
SQL-016	Detect duplicate active email	Inline	EXT_USER, EMAIL_ADDRESS, EXT_USER_TYPE	clsUser
SQL-017	Retrieve forgotten user by customer and email	StringBuilder / Inline	EXT_USER, EXT_USER_TYPE, EMAIL_ADDRESS	clsUser
SQL-018	Retrieve provider identity by user type	Inline	EXT_USER_TYPE, EXT_USER, EMAIL_ADDRESS, PERSON	clsUser
SQL-019	Retrieve users by type and customer group	Inline	EXT_USER, EXT_USER_TYPE	clsUser
SQL-020	Search users with optional filters	Inline + conditional assembly	EXT_USER, PERSON, EXT_USER_TYPE, EXT_USER_ACCESS_GROUP, COMPANY, PERSON_EMAIL, EMAIL_ADDRESS	clsUser
SQL-021	Retrieve AD groups for an access group	Inline	EXT_USER_AD_GROUP, EXT_USER_ACCESS_GROUP_AD	clsUserAccessGroup
SQL-022	Retrieve user access group row	Inline	EXT_USER_ACCESS_GROUP, EXT_USER_ACCESS_GROUP_CODE, EXT_USER_TYPE, EXT_USER_TYPE_ACCESS_GROUP, EXT_USER_ACCESS_GROUP_GCODE	clsUserAccessGroup
SQL-023	Retrieve all access-group definitions	Inline	EXT_USER_TYPE_ACCESS_GROUP, EXT_USER_ACCESS_GROUP_CODE, EXT_USER_ACCESS_GROUP_GCODE	clsUserAccessGroup
SQL-024	Retrieve all possible access groups for a user type	Inline	EXT_USER_TYPE_ACCESS_GROUP, EXT_USER_ACCESS_GROUP_CODE, EXT_USER_ACCESS_GROUP, EXT_USER_ACCESS_GROUP_GCODE	clsUserAccessGroup
SQL-025	Find newly-added access groups from AD	Inline	EXT_USER_AD_GROUP, EXT_USER_ACCESS_GROUP_AD, EXT_USER_ACCESS_GROUP, EXT_USER_ACCESS_GROUP_GCODE	clsUserAccessGroup
SQL-026	Find removed access groups from AD	Inline + dynamic NOT IN	EXT_USER, EXT_USER_TYPE, EXT_USER_ACCESS_GROUP, EXT_USER_ACCESS_GROUP_AD, EXT_USER_AD_GROUP	clsUserAccessGroup
SQL-027	Retrieve deny-self-registration row	Inline	EXT_USER_DENY_SELF_REG	clsUserDenySelfRegistration
SQL-028	Retrieve security question row(s)	Inline	EXT_USER_SECURITY_QUESTION, EXT_USER_SEC_QUESTION_CODE, EXT_USER_SEC_QUESTION_GCODE	clsUserSecurityQuestion
SQL-029	Retrieve available security questions	Inline	EXT_USER_SEC_QUESTION_CODE, EXT_USER_SEC_QUESTION_GCODE	clsUserSecurityQuestion
SQL-030	Retrieve user type row	Inline	EXT_USER_TYPE, EXT_USER_TYPE_CODE, EXT_USER_TYPE_CODE_PROMPT	clsUserType
SQL-031	Retrieve all user type definitions	Inline	EXT_USER_TYPE_CODE, EXT_USER_TYPE_CODE_PROMPT	clsUserType
SQL-032	Find current admin user type for provider EFT	Inline	EXT_USER_TYPE	clsUserType
SQL-033	Quoted policy expiry check	Stored procedure	Oracle package (body not in repo)	clsUserType
SQL-034	Billing plan type retrieval	Stored procedure	Oracle package (body not in repo)	clsUserType
SQL-035	ePay eligibility check	Stored procedure	Oracle package (body not in repo)	clsUserType
SQL-036	Check whether user owns provider EOB rows	Inline	EXT_USER, EXT_USER_TYPE, PROVIDER_EOB_INTERFACE	clsUserType
SQL-037	Retrieve zip code mapping	Inline	PA_ZIP_CODE, COUNTY, OFFICE_CODE	clsZipCode
SQL-038	Load user-type code map	Inline	EXT_USER_TYPE_CODE	mdlCodeMaps
SQL-039	Load user-status code map	Inline	EXT_USER_STATUS_CODE	mdlCodeMaps
SQL-040	Load RTW codes	Inline	RTW_LOAD_CODE	mdlGroupAssociation
SQL-041	Load auditor by AD user and vendor; fallback by vendor	Inline	PA_AUDITOR	mdlAuditor

Update operations

SQL ID	Description	Type	Tables	Business object
SQL-042	Inline update used by clsAuditAssignmentOverride	Inline	PA_ASSIGN_OVERRIDE	clsAuditAssignmentOverride
SQL-043	Inline update used by clsAuditor	Inline	PA_AUDITOR	clsAuditor
SQL-044	Inline update used by clsAuditorZipCode	Inline	PA_AUDITOR_ZIP_CODE	clsAuditorZipCode
SQL-045	Inline update used by clsUser	Inline	EXT_USER	clsUser
SQL-046	Inline update used by clsUser	Inline	EXT_USER	clsUser
SQL-047	Inline update used by clsUserAccessGroup	Inline	EXT_USER_ACCESS_GROUP	clsUserAccessGroup
SQL-048	Inline update used by clsUserDenySelfRegistration	Inline	EXT_USER_DENY_SELF_REG	clsUserDenySelfRegistration
SQL-049	Inline update used by clsUserSecurityQuestion	Inline	EXT_USER_SECURITY_QUESTION	clsUserSecurityQuestion
SQL-050	Inline update used by clsUserType	Inline	EXT_USER_TYPE	clsUserType
SQL-051	Inline update used by clsUserType	Inline	PAYMENT_AUTHORIZATION	clsUserType

Delete operations

SQL ID	Description	Type	Tables	Business object
SQL-052	Inline delete used by clsAuditorZipCode	Inline	PA_AUDITOR_ZIP_CODE	clsAuditorZipCode
SQL-053	Inline delete used by clsUserAccessGroup	Inline	EXT_USER_ACCESS_GROUP	clsUserAccessGroup
SQL-054	Inline delete used by clsUserDenySelfRegistration	Inline	EXT_USER_DENY_SELF_REG	clsUserDenySelfRegistration
SQL-055	Inline delete used by clsUserSecurityQuestion	Inline	EXT_USER_SECURITY_QUESTION	clsUserSecurityQuestion
SQL-056	Inline delete used by clsUserType	Inline	EXT_USER_TYPE	clsUserType

Related documentation

• Index
• Overview
• Architecture
• Dependencies
• Operations and processes
• Business rules
• Database tables
• API reference
• Configuration

Sources

• Business\SAIF.Business.External.Users\clsAuditAssignmentOverride.vb
• Business\SAIF.Business.External.Users\clsAuditor.vb
• Business\SAIF.Business.External.Users\clsAuditorCollection.vb
• Business\SAIF.Business.External.Users\clsAuditorZipCode.vb
• Business\SAIF.Business.External.Users\clsAuditorZipCodeCollection.vb
• Business\SAIF.Business.External.Users\clsNewUserArgs.vb
• Business\SAIF.Business.External.Users\clsUser.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\mdlAuditor.vb
• Business\SAIF.Business.External.Users\mdlCodeMaps.vb
• Business\SAIF.Business.External.Users\mdlGroupAssociation.vb