AIPeopleSource_vw

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

UserID

int

null

Primary key - Staff member unique identifier linking to core user record

UserStatus

varchar

√

null

User status - Whether the staff member is currently active in the agency (Yes=active, No=inactive)

ICentralAccess

varchar

null

System access - Can login to the app or not. Whether the staff member has access to the iCentral system (Yes=has access, No=no access)

FirstName

varchar

√

null

Staff first name - Staff member’s given name for identification and communication

MidName

varchar

√

null

Staff middle name - Staff member’s middle name for complete identification

LastName

varchar

√

null

Staff last name - Staff member’s family name for identification and communication

ContactComments

varchar

5000

√

null

Contact notes - Additional notes or comments related to contacting or working with the staff member

Sex

varchar

√

null

Staff gender - Staff member’s gender for demographic reporting and HR purposes

DateHire

datetime

16,3

√

null

Hire date - Date when the staff member was hired by the organization

DateOfBirth

datetime

16,3

√

null

Birth date - Staff member’s date of birth for age verification and benefits administration

varchar

√

null

Email address - Primary email address for professional communication

StreetAddr

varchar

√

null

Street address - Staff member’s primary residential street address

City

varchar

√

null

City - City of residence for the staff member

State

varchar

√

null

State - State of residence for the staff member

Zip

varchar

√

null

ZIP code - Postal code for the staff member’s address

HomeAddr2

varchar

√

null

Address line 2 - Additional address information such as apartment or unit number

AltAddr1

varchar

√

null

Alternate address 1 - Secondary address for the staff member

AltAddr2

varchar

√

null

Alternate address 2 - Additional line for secondary address

AltCity

varchar

√

null

Alternate city - City for the secondary address

AltState

varchar

√

null

Alternate state - State for the secondary address

AltZip

varchar

√

null

Alternate ZIP - Postal code for the secondary address

Salutation

varchar

√

null

Salutation - Professional title or salutation (Dr., Mr., Ms., etc.)

DateTerminate

datetime

16,3

√

null

Termination date - Date when the staff member’s employment ended, if applicable

DateUpdated

datetime

16,3

√

null

Last update date - When the staff member’s information was most recently modified

SupportLevel

int

√

null

Support level - Internal classification of the staff member’s support or access level

RoleList

nvarchar

8000

√

null

Role assignments - Displays all roles assigned to the user. A user can have multiple roles. Bridge table: dbo.UserInRole, Lookup table: dbo.Roles

TeamList

nvarchar

8000

√

null

Team memberships - Displays all roles assigned to the user. A user can have multiple roles. Bridge table: dbo.UserInGroupClient, Lookup table: dbo.Groups

PhoneNumber

varchar

√

null

Phone number - Primary phone number for contacting the staff member

NPI

varchar

100

√

null

NPI number - The NPI of the user. Each user can have multiple IDs (e.g., NPI, Internal, SSN, etc.). ID data is stored in dbo.UserIDList table. Lookup ID types in ics2.dbo.UserIDTypes. The ID for NPI is 9. Value retrieved using getUserId_fn function

RateTitle

nvarchar

1073741823

√

null

Rate title - Pay grade or rate classification for the staff member

PayType

nvarchar

510

√

null

Pay type - Method of payment (Hourly, Salary, Contract, etc.)

CompanyName

varchar

√

null

Company affiliation - Name of the company or organization the staff member is affiliated with

InternalId

varchar

100

√

null

Internal identifier - The Internal of the user. Each user can have multiple IDs (e.g., NPI, Internal, SSN, etc.). ID data is stored in dbo.UserIDList table. Lookup ID types in ics2.dbo.UserIDTypes. The ID for NPI is 5

LicenseId

varchar

100

√

null

License number - The License of the user. Each user can have multiple IDs (e.g., NPI, Internal, SSN, etc.). ID data is stored in dbo.UserIDList table. Lookup ID types in ics2.dbo.UserIDTypes. The ID for NPI is 2

Regions

nvarchar

8000

√

null

Service regions - Displays all regions assigned to the user. A user can have multiple roles. Bridge table: dbo.UserInRegion, Lookup table: dbo.Regions

Languages

varchar

8000

√

null

Languages spoken - Displays all regions assigned to the user. A user can have multiple roles. Bridge table: dbo.UserInLanguage, Lookup table: dbo.Language

Supervisor

varchar

101

√

null

Supervisor name - Name of the staff member’s direct supervisor

TotalUnit

decimal

40,2

√

null

Total units delivered - Displays the total units from claims that are not in Void or Deleted status, where the provider is this user

ServiceTypes

nvarchar

1073741823

√

null

Service types provided - Displays all the services from claims that are not in Void or Deleted status, where the provider is this user

TotalClaims

int

√

null

Total claims count - Displays the number of claims that are not in Void or Deleted status, where the provider is this user

Total

money

21,4

√

null

Total revenue generated - Displays the sum (AmountCharged) of claims that are not in Void or Deleted status, where the provider is this user

TotalPayrollRate

money

21,4

√

null

Total payroll cost - Displays the sum of payroll rates (Rate) linked to claims that are not in Void or Deleted status, where the provider is the current user.

Profit

money

21,4

√

null

Profit generated - Total - TotalPayrollRate

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW [dbo].[AIPeopleSource_vw]
AS
	SELECT DISTINCT 
		u.UserID,
		[UserStatus] = u.AgencyStatus,
		[ICentralAccess] = CASE WHEN u.UserStatus = 'A' THEN 'Yes' ELSE 'No' END,
		u.FirstName,
		u.MidName,
		u.LastName,
		u.ContactComments,
		u.Sex,
		u.DateHire,
		u.DateOfBirth,
		u.Email,
		u.StreetAddr,
		u.City,
		u.State,
		u.Zip,
		u.HomeAddr2,
		u.AltAddr1,
		u.AltAddr2,
		u.AltCity,
		u.AltState,
		u.AltZip,
		u.Salutation,
		u.DateTerminate,
		u.DateUpdated,
		u.SupportLevel,

		[RoleList] = (
			SELECT STRING_AGG(r.RoleName, ',') 
			FROM dbo.Roles r WITH (NOLOCK)
			INNER JOIN dbo.UserInRole uir WITH (NOLOCK) ON uir.RoleId = r.RoleId 
			WHERE uir.UserId = u.UserID
		),

		[TeamList] = (
			SELECT STRING_AGG(g.GroupName, ',') 
			FROM dbo.Groups g WITH (NOLOCK)
			INNER JOIN dbo.UserInGroupClient uig WITH (NOLOCK) ON uig.GroupId = g.GroupId 
			WHERE uig.UserId = u.UserID
		),

		[PhoneNumber] = phoneNumber.Number,
		[NPI] = dbo.getUserId_fn(u.UserID, 9),
		[RateTitle] = upg.RateGroupName,
		[PayType] = upt.UserPayTypeName,
		[CompanyName] = av.CompanyName,
		[InternalId] = dbo.getUserId_fn(u.UserID, 5),
		[LicenseId] = dbo.getUserId_fn(u.UserID, 2),

		[Regions] = (
			SELECT STRING_AGG(r.RegionName, ',') 
			FROM dbo.UserInRegion uir WITH (NOLOCK)
			INNER JOIN dbo.Regions r WITH (NOLOCK) ON uir.fkRegionID = r.pkRegionID 
			WHERE uir.UserID = u.UserID
		),

		[Languages] = (
			SELECT STRING_AGG(l.LangDesc, ',') 
			FROM dbo.UserInLanguage uil WITH (NOLOCK)
			INNER JOIN dbo.Language l WITH (NOLOCK) ON uil.fkLangID = l.LangID 
			WHERE uil.fkUserID = u.UserID
		),

		[Supervisor] = supervisor.FullName,

		[TotalUnit] = (
			SELECT SUM(BillingUnit) 
			FROM dbo.Claim c2 WITH (NOLOCK) 
			WHERE c2.StatusID NOT IN (8, 16) AND c2.ClaimBy = u.UserID
		),

		[ServiceTypes] = STUFF((
			SELECT DISTINCT 
				', ' + 
				CASE WHEN c2.ClaimTypeID = 1 THEN 'Eval - ' ELSE '' END + st2.ServiceDesc
			FROM dbo.Claim c2 WITH (NOLOCK)
			INNER JOIN ics2.dbo.ServiceType st2 WITH (NOLOCK) ON st2.ServiceTypeID = c2.ServiceTypeID 
			WHERE c2.StatusID NOT IN (8, 16) AND c2.ClaimBy = u.UserID 
			FOR XML PATH('')
		), 1, 2, ''),

		[TotalClaims] = (
			SELECT COUNT(ClaimID) 
			FROM dbo.Claim c2 WITH (NOLOCK) 
			WHERE c2.StatusID NOT IN (8, 16) AND c2.ClaimBy = u.UserID
		),

		[Total] = (
			SELECT SUM(AmountCharged) 
			FROM dbo.Claim c2 WITH (NOLOCK) 
			WHERE c2.StatusID NOT IN (8, 16) AND c2.ClaimBy = u.UserID
		),

		[TotalPayrollRate] = (
			SELECT SUM(Rate) 
			FROM dbo.ClaimServiceRate WITH (NOLOCK) 
			WHERE StatusID <> 16 
				AND ClaimID IN (
					SELECT ClaimID 
					FROM dbo.Claim c2 WITH (NOLOCK) 
					WHERE c2.StatusID NOT IN (8, 16) AND c2.ClaimBy = u.UserID
				)
		),

		[Profit] = (
			SELECT SUM(AmountCharged) 
			FROM dbo.Claim c2 WITH (NOLOCK) 
			WHERE c2.StatusID NOT IN (8, 16) AND c2.ClaimBy = u.UserID
		) - (
			SELECT SUM(Rate) 
			FROM dbo.ClaimServiceRate WITH (NOLOCK) 
			WHERE StatusID <> 16 
				AND ClaimID IN (
					SELECT ClaimID 
					FROM dbo.Claim c2 WITH (NOLOCK) 
					WHERE c2.StatusID NOT IN (8, 16) AND c2.ClaimBy = u.UserID
				)
		)

	FROM dbo.AccessUser u WITH (NOLOCK)
		LEFT JOIN dbo.AgencyOutsideSrc_vw av WITH (NOLOCK) ON av.CompanyID = u.CompanyID
		LEFT JOIN dbo.UserPayGroup upg WITH (NOLOCK) ON upg.PayGroupID = u.RateTitle
		LEFT JOIN dbo.UserPayType upt WITH (NOLOCK) ON upt.pkUserPayTypeID = u.fkUserPayTypeID

		OUTER APPLY (
			SELECT TOP 1 
				FullName = FirstName + ' ' + LastName 
			FROM dbo.AccessUser WITH (NOLOCK) 
			WHERE UserID = u.Supervisor
		) supervisor

		OUTER APPLY (
			SELECT TOP 1 Number 
			FROM dbo.UserPhone WITH (NOLOCK) 
			WHERE UserID = u.UserID
		) phoneNumber

	WHERE u.AgencyStatus <> 'Deleted'

Possibly Referenced Tables/Views

AIPeopleSource_vw

AIPeopleSource_vw

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views