MS CRM - New query to have an unsecured FilteredAppointment View called FilteredAppointmentUS

CreateUnsecureFilteredAppointmentView.sql

create view [dbo].[FilteredAppointmentUS] (
    activityid,
    actualdurationminutes,
    actualend, 
    actualendutc,
    actualstart, 
    actualstartutc,
    category,
    ccs_administrativeevent,
    ccs_administrativeeventname,
    ccs_clientevent,
    ccs_clienteventname,
    ccs_clinicalevent,
    ccs_clinicaleventname,
    ccs_humanresourcesevent,
    ccs_humanresourceseventname,
    ccs_sentinelevent,
    ccs_sentineleventname,
    createdby,
    createdbydsc,
    createdbyname,
    createdbyyominame,
    createdon, 
    createdonutc,
    description,
    globalobjectid,
    importsequencenumber,
    isalldayevent,
    isalldayeventname,
    isbilled,
    isbilledname,
    isworkflowcreated,
    isworkflowcreatedname,
    location,
    modifiedby,
    modifiedbydsc,
    modifiedbyname,
    modifiedbyyominame,
    modifiedon, 
    modifiedonutc,
    outlookownerapptid,
    overriddencreatedon, 
    overriddencreatedonutc,
    ownerid,
    owneriddsc,
    owneridname,
    owneridtype,
    owneridyominame,
    owningbusinessunit,
    owninguser,
    prioritycode,
    prioritycodename,
    regardingobjectid,
    regardingobjectiddsc,
    regardingobjectidname,
    regardingobjectidyominame,
    regardingobjecttypecode,
    scheduleddurationminutes,
    scheduledend, 
    scheduledendutc,
    scheduledstart, 
    scheduledstartutc,
    serviceid,
    statecode,
    statecodename,
    statuscode,
    statuscodename,
    subcategory,
    subject,
    timezoneruleversionnumber,
    utcconversiontimezonecode
) with view_metadata as 
select 
    Appointment.ActivityId,
    Appointment.ActualDurationMinutes,
    dbo.fn_UTCToTzSpecificLocalTime(Appointment.ActualEnd,
			us.TimeZoneBias,
			us.TimeZoneDaylightBias,
			us.TimeZoneDaylightYear,
			us.TimeZoneDaylightMonth,
			us.TimeZoneDaylightDay,
			us.TimeZoneDaylightHour,
			us.TimeZoneDaylightMinute,
			us.TimeZoneDaylightSecond,
			0,
			us.TimeZoneDaylightDayOfWeek,
			us.TimeZoneStandardBias,
			us.TimeZoneStandardYear,
			us.TimeZoneStandardMonth,
			us.TimeZoneStandardDay,
			us.TimeZoneStandardHour,
			us.TimeZoneStandardMinute,
			us.TimeZoneStandardSecond,
			0,
			us.TimeZoneStandardDayOfWeek), 
    Appointment.ActualEnd,
    dbo.fn_UTCToTzSpecificLocalTime(Appointment.ActualStart,
			us.TimeZoneBias,
			us.TimeZoneDaylightBias,
			us.TimeZoneDaylightYear,
			us.TimeZoneDaylightMonth,
			us.TimeZoneDaylightDay,
			us.TimeZoneDaylightHour,
			us.TimeZoneDaylightMinute,
			us.TimeZoneDaylightSecond,
			0,
			us.TimeZoneDaylightDayOfWeek,
			us.TimeZoneStandardBias,
			us.TimeZoneStandardYear,
			us.TimeZoneStandardMonth,
			us.TimeZoneStandardDay,
			us.TimeZoneStandardHour,
			us.TimeZoneStandardMinute,
			us.TimeZoneStandardSecond,
			0,
			us.TimeZoneStandardDayOfWeek), 
    Appointment.ActualStart,
    Appointment.Category,
    Appointment.CCS_AdministrativeEvent,
    CCS_AdministrativeEventPLTable.Value,
    Appointment.CCS_ClientEvent,
    CCS_ClientEventPLTable.Value,
    Appointment.CCS_ClinicalEvent,
    CCS_ClinicalEventPLTable.Value,
    Appointment.CCS_HumanResourcesEvent,
    CCS_HumanResourcesEventPLTable.Value,
    Appointment.CCS_SentinelEvent,
    CCS_SentinelEventPLTable.Value,
    Appointment.CreatedBy,
    Appointment.CreatedByDsc,
    Appointment.CreatedByName,
    Appointment.CreatedByYomiName,
    dbo.fn_UTCToTzSpecificLocalTime(Appointment.CreatedOn,
			us.TimeZoneBias,
			us.TimeZoneDaylightBias,
			us.TimeZoneDaylightYear,
			us.TimeZoneDaylightMonth,
			us.TimeZoneDaylightDay,
			us.TimeZoneDaylightHour,
			us.TimeZoneDaylightMinute,
			us.TimeZoneDaylightSecond,
			0,
			us.TimeZoneDaylightDayOfWeek,
			us.TimeZoneStandardBias,
			us.TimeZoneStandardYear,
			us.TimeZoneStandardMonth,
			us.TimeZoneStandardDay,
			us.TimeZoneStandardHour,
			us.TimeZoneStandardMinute,
			us.TimeZoneStandardSecond,
			0,
			us.TimeZoneStandardDayOfWeek), 
    Appointment.CreatedOn,
    Appointment.Description,
    Appointment.GlobalObjectId,
    Appointment.ImportSequenceNumber,
    Appointment.IsAllDayEvent,
    IsAllDayEventPLTable.Value,
    Appointment.IsBilled,
    IsBilledPLTable.Value,
    Appointment.IsWorkflowCreated,
    IsWorkflowCreatedPLTable.Value,
    Appointment.Location,
    Appointment.ModifiedBy,
    Appointment.ModifiedByDsc,
    Appointment.ModifiedByName,
    Appointment.ModifiedByYomiName,
    dbo.fn_UTCToTzSpecificLocalTime(Appointment.ModifiedOn,
			us.TimeZoneBias,
			us.TimeZoneDaylightBias,
			us.TimeZoneDaylightYear,
			us.TimeZoneDaylightMonth,
			us.TimeZoneDaylightDay,
			us.TimeZoneDaylightHour,
			us.TimeZoneDaylightMinute,
			us.TimeZoneDaylightSecond,
			0,
			us.TimeZoneDaylightDayOfWeek,
			us.TimeZoneStandardBias,
			us.TimeZoneStandardYear,
			us.TimeZoneStandardMonth,
			us.TimeZoneStandardDay,
			us.TimeZoneStandardHour,
			us.TimeZoneStandardMinute,
			us.TimeZoneStandardSecond,
			0,
			us.TimeZoneStandardDayOfWeek), 
    Appointment.ModifiedOn,
    Appointment.OutlookOwnerApptId,
    dbo.fn_UTCToTzSpecificLocalTime(Appointment.OverriddenCreatedOn,
			us.TimeZoneBias,
			us.TimeZoneDaylightBias,
			us.TimeZoneDaylightYear,
			us.TimeZoneDaylightMonth,
			us.TimeZoneDaylightDay,
			us.TimeZoneDaylightHour,
			us.TimeZoneDaylightMinute,
			us.TimeZoneDaylightSecond,
			0,
			us.TimeZoneDaylightDayOfWeek,
			us.TimeZoneStandardBias,
			us.TimeZoneStandardYear,
			us.TimeZoneStandardMonth,
			us.TimeZoneStandardDay,
			us.TimeZoneStandardHour,
			us.TimeZoneStandardMinute,
			us.TimeZoneStandardSecond,
			0,
			us.TimeZoneStandardDayOfWeek), 
    Appointment.OverriddenCreatedOn,
    Appointment.OwnerId,
    Appointment.OwnerIdDsc,
    Appointment.OwnerIdName,
    Appointment.OwnerIdType,
    Appointment.OwnerIdYomiName,
    Appointment.OwningBusinessUnit,
    Appointment.OwningUser,
    Appointment.PriorityCode,
    PriorityCodePLTable.Value,
    Appointment.RegardingObjectId,
    Appointment.RegardingObjectIdDsc,
    Appointment.RegardingObjectIdName,
    Appointment.RegardingObjectIdYomiName,
    Appointment.RegardingObjectTypeCode,
    Appointment.ScheduledDurationMinutes,
    dbo.fn_UTCToTzSpecificLocalTime(Appointment.ScheduledEnd,
			us.TimeZoneBias,
			us.TimeZoneDaylightBias,
			us.TimeZoneDaylightYear,
			us.TimeZoneDaylightMonth,
			us.TimeZoneDaylightDay,
			us.TimeZoneDaylightHour,
			us.TimeZoneDaylightMinute,
			us.TimeZoneDaylightSecond,
			0,
			us.TimeZoneDaylightDayOfWeek,
			us.TimeZoneStandardBias,
			us.TimeZoneStandardYear,
			us.TimeZoneStandardMonth,
			us.TimeZoneStandardDay,
			us.TimeZoneStandardHour,
			us.TimeZoneStandardMinute,
			us.TimeZoneStandardSecond,
			0,
			us.TimeZoneStandardDayOfWeek), 
    Appointment.ScheduledEnd,
    dbo.fn_UTCToTzSpecificLocalTime(Appointment.ScheduledStart,
			us.TimeZoneBias,
			us.TimeZoneDaylightBias,
			us.TimeZoneDaylightYear,
			us.TimeZoneDaylightMonth,
			us.TimeZoneDaylightDay,
			us.TimeZoneDaylightHour,
			us.TimeZoneDaylightMinute,
			us.TimeZoneDaylightSecond,
			0,
			us.TimeZoneDaylightDayOfWeek,
			us.TimeZoneStandardBias,
			us.TimeZoneStandardYear,
			us.TimeZoneStandardMonth,
			us.TimeZoneStandardDay,
			us.TimeZoneStandardHour,
			us.TimeZoneStandardMinute,
			us.TimeZoneStandardSecond,
			0,
			us.TimeZoneStandardDayOfWeek), 
    Appointment.ScheduledStart,
    Appointment.ServiceId,
    Appointment.StateCode,
    StateCodePLTable.Value,
    Appointment.StatusCode,
    StatusCodePLTable.Value,
    Appointment.Subcategory,
    Appointment.Subject,
    Appointment.TimeZoneRuleVersionNumber,
    Appointment.UTCConversionTimeZoneCode
from Appointment
    left join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
    left join UserSettingsBase us on us.SystemUserId = u.SystemUserId
    left join OrganizationBase o on u.OrganizationId = o.OrganizationId
    left outer join StringMap CCS_AdministrativeEventPLTable on 
        (CCS_AdministrativeEventPLTable.AttributeName = 'ccs_administrativeevent'
        and CCS_AdministrativeEventPLTable.ObjectTypeCode = 4201
        and CCS_AdministrativeEventPLTable.AttributeValue = Appointment.CCS_AdministrativeEvent
        and CCS_AdministrativeEventPLTable.LangId = us.UILanguageId )
    left outer join StringMap CCS_ClientEventPLTable on 
        (CCS_ClientEventPLTable.AttributeName = 'ccs_clientevent'
        and CCS_ClientEventPLTable.ObjectTypeCode = 4201
        and CCS_ClientEventPLTable.AttributeValue = Appointment.CCS_ClientEvent
        and CCS_ClientEventPLTable.LangId = us.UILanguageId )
    left outer join StringMap CCS_ClinicalEventPLTable on 
        (CCS_ClinicalEventPLTable.AttributeName = 'ccs_clinicalevent'
        and CCS_ClinicalEventPLTable.ObjectTypeCode = 4201
        and CCS_ClinicalEventPLTable.AttributeValue = Appointment.CCS_ClinicalEvent
        and CCS_ClinicalEventPLTable.LangId = us.UILanguageId )
    left outer join StringMap CCS_HumanResourcesEventPLTable on 
        (CCS_HumanResourcesEventPLTable.AttributeName = 'ccs_humanresourcesevent'
        and CCS_HumanResourcesEventPLTable.ObjectTypeCode = 4201
        and CCS_HumanResourcesEventPLTable.AttributeValue = Appointment.CCS_HumanResourcesEvent
        and CCS_HumanResourcesEventPLTable.LangId = us.UILanguageId )
    left outer join StringMap CCS_SentinelEventPLTable on 
        (CCS_SentinelEventPLTable.AttributeName = 'ccs_sentinelevent'
        and CCS_SentinelEventPLTable.ObjectTypeCode = 4201
        and CCS_SentinelEventPLTable.AttributeValue = Appointment.CCS_SentinelEvent
        and CCS_SentinelEventPLTable.LangId = us.UILanguageId )
    left outer join StringMap IsAllDayEventPLTable on 
        (IsAllDayEventPLTable.AttributeName = 'isalldayevent'
        and IsAllDayEventPLTable.ObjectTypeCode = 4201
        and IsAllDayEventPLTable.AttributeValue = Appointment.IsAllDayEvent
        and IsAllDayEventPLTable.LangId = us.UILanguageId )
    left outer join StringMap IsBilledPLTable on 
        (IsBilledPLTable.AttributeName = 'isbilled'
        and IsBilledPLTable.ObjectTypeCode = 4201
        and IsBilledPLTable.AttributeValue = Appointment.IsBilled
        and IsBilledPLTable.LangId = us.UILanguageId )
    left outer join StringMap IsWorkflowCreatedPLTable on 
        (IsWorkflowCreatedPLTable.AttributeName = 'isworkflowcreated'
        and IsWorkflowCreatedPLTable.ObjectTypeCode = 4201
        and IsWorkflowCreatedPLTable.AttributeValue = Appointment.IsWorkflowCreated
        and IsWorkflowCreatedPLTable.LangId = us.UILanguageId )
    left outer join StringMap PriorityCodePLTable on 
        (PriorityCodePLTable.AttributeName = 'prioritycode'
        and PriorityCodePLTable.ObjectTypeCode = 4201
        and PriorityCodePLTable.AttributeValue = Appointment.PriorityCode
        and PriorityCodePLTable.LangId = us.UILanguageId )
    left outer join StringMap StateCodePLTable on 
        (StateCodePLTable.AttributeName = 'statecode'
        and StateCodePLTable.ObjectTypeCode = 4201
        and StateCodePLTable.AttributeValue = Appointment.StateCode
        and StateCodePLTable.LangId = us.UILanguageId )
    left outer join StringMap StatusCodePLTable on 
        (StatusCodePLTable.AttributeName = 'statuscode'
        and StatusCodePLTable.ObjectTypeCode = 4201
        and StatusCodePLTable.AttributeValue = Appointment.StatusCode
        and StatusCodePLTable.LangId = us.UILanguageId )
    cross join dbo.fn_GetMaxPrivilegeDepthMask(4200) pdm
where Appointment.DeletionStateCode in (0)