MS CRM - New query to have an unsecured FilteredAnnotation View called FilteredAnnontationUS

CreateUnsecuredFilteredAnnontationView.sql

create view [dbo].[FilteredAnnotationUS] (
    annotationid,
    createdby,
    createdbydsc,
    createdbyname,
    createdbyyominame,
    createdon,
    createdonutc,
    documentbody,
    filename,
    filesize,
    importsequencenumber,
    isdocument,
    isdocumentname,
    isprivatename,
    langid,
    mimetype,
    modifiedby,
    modifiedbydsc,
    modifiedbyname,
    modifiedbyyominame,
    modifiedon,
    modifiedonutc,
    notetext,
    objectid,
    objecttypecode,
    objecttypecodename,
    overriddencreatedon,
    overriddencreatedonutc,
    ownerid,
    owneriddsc,
    owneridname,
    owneridtype,
    owneridyominame,
    owningbusinessunit,
    owningteam,
    owninguser,
    stepid,
    subject
) with view_metadata as
select
    Annotation.AnnotationId,
    Annotation.CreatedBy,
    Annotation.CreatedByDsc,
    Annotation.CreatedByName,
    Annotation.CreatedByYomiName,
    dbo.fn_UTCToTzSpecificLocalTime(Annotation.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),
    Annotation.CreatedOn,
    Annotation.DocumentBody,
    Annotation.FileName,
    Annotation.FileSize,
    Annotation.ImportSequenceNumber,
    Annotation.IsDocument,
    IsDocumentPLTable.Value,
    IsPrivatePLTable.Value,
    Annotation.LangId,
    Annotation.MimeType,
    Annotation.ModifiedBy,
    Annotation.ModifiedByDsc,
    Annotation.ModifiedByName,
    Annotation.ModifiedByYomiName,
    dbo.fn_UTCToTzSpecificLocalTime(Annotation.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),
    Annotation.ModifiedOn,
    Annotation.NoteText,
    Annotation.ObjectId,
    Annotation.ObjectTypeCode,
    ObjectTypeCodePLTable.Value,
    dbo.fn_UTCToTzSpecificLocalTime(Annotation.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),
    Annotation.OverriddenCreatedOn,
    Annotation.OwnerId,
    Annotation.OwnerIdDsc,
    Annotation.OwnerIdName,
    Annotation.OwnerIdType,
    Annotation.OwnerIdYomiName,
    Annotation.OwningBusinessUnit,
    Annotation.OwningTeam,
    Annotation.OwningUser,
    Annotation.StepId,
    Annotation.Subject
from Annotation
    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 IsDocumentPLTable on
        (IsDocumentPLTable.AttributeName = 'isdocument'
        and IsDocumentPLTable.ObjectTypeCode = 5
        and IsDocumentPLTable.AttributeValue = Annotation.IsDocument
        and IsDocumentPLTable.LangId = us.UILanguageId )
    left outer join StringMap IsPrivatePLTable on
        (IsPrivatePLTable.AttributeName = 'isprivate'
        and IsPrivatePLTable.ObjectTypeCode = 5
        and IsPrivatePLTable.AttributeValue = Annotation.IsPrivate
        and IsPrivatePLTable.LangId = us.UILanguageId )
    left outer join StringMap ObjectTypeCodePLTable on
        (ObjectTypeCodePLTable.AttributeName = 'objecttypecode'
        and ObjectTypeCodePLTable.ObjectTypeCode = 5
        and ObjectTypeCodePLTable.AttributeValue = Annotation.ObjectTypeCode
        and ObjectTypeCodePLTable.LangId = us.UILanguageId )
    cross join dbo.fn_GetMaxPrivilegeDepthMask(5) pdm
where Annotation.DeletionStateCode in (0)