VW_ROX_Access_Log

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

Person_ID

varchar

√

null

FirstName

varchar

√

null

LastName

varchar

√

null

MiddleInitial

varchar

√

null

EmployeeNo

varchar

√

null

LockoutDate

varchar

√

null

CreatedDate

varchar

√

null

IsUser

varchar

√

null

IsActive_Person

varchar

√

null

LastLogout

varchar

√

null

Facility_ID

varchar

√

null

FacilityCode

varchar

√

null

Facility_Name

varchar

√

null

Role

varchar

√

null

LastSuccessfulLogin

varchar

√

null

Isactive_AppUser

varchar

√

null

cnt

varchar

null

View Definition

CREATE VIEW [dbo].[VW_ROX_Access_Log]
AS

SELECT CAST(P.Person_ID AS VARCHAR(50)) AS Person_ID, 
       CAST(P.FirstName AS VARCHAR(50)) AS FirstName, 
	   CAST(P.LastName AS VARCHAR(50)) AS LastName, 
	   CAST(P.MiddleInitial AS VARCHAR(50)) AS MiddleInitial, 
	   CAST(P.EmployeeNo AS VARCHAR(50)) AS EmployeeNo,
	   CAST(P.LockoutDate AS VARCHAR(24)) AS LockoutDate, 
	   CAST(P.CreatedDate AS VARCHAR(24)) AS CreatedDate, 
	   CAST(P.IsUser AS VARCHAR(50)) AS IsUser,
	   CAST(CASE WHEN P.IsInactive=0 THEN 1 ELSE 0 END AS VARCHAR(10)) AS IsActive_Person,
	   CAST(AU.LastLogout AS VARCHAR(24)) AS LastLogout, 
	   CAST(F.Facility_ID AS VARCHAR(50)) AS Facility_ID, 
	   CAST(F.FacilityCode AS VARCHAR(50)) AS FacilityCode, 
	   CAST(F.Name AS VARCHAR(50)) AS Facility_Name,
	   CAST(r.Name as VARCHAR(50)) AS Role,
	   CAST(au.LastSuccessfulLogin AS VARCHAR(24)) AS LastSuccessfulLogin,
	   CAST(case when au.IsInactive=0 then 1 else 0 end AS VARCHAR(10)) as Isactive_AppUser,
	   'B' as cnt	    --select * 
  FROM EnterpriseStaging.dbo.ROX_Person_Stage P
  JOIN EnterpriseStaging.dbo.ROX_Facility_Stage F
    ON F.Facility_ID=P.PrimaryFacility_ID
   AND F.Expiration_Date IS NULL
   AND F.Current_Ind='Y'
  JOIN EnterpriseStaging.dbo.ROX_PersonFacility PF
    ON PF.Person_ID=p.Person_ID
   AND PF.Facility_ID=F.Facility_ID
  JOIN EnterpriseStaging.dbo.ROX_PersonFacilityRole PFR
    ON PFR.PersonFacility_ID=PF.PersonFacility_ID
  JOIN EnterpriseStaging.dbo.ROX_Role R
    ON R.Role_ID=PFR.Role_ID
  JOIN EnterpriseStaging.dbo.ROX_AppUser AU
    ON AU.Person_ID=p.Person_ID
 WHERE P.Expiration_Date IS NULL
   AND P.Current_Ind='Y'

Columns

View Definition

Possibly Referenced Tables/Views