V_QUERY_EVENT

Query view used for event information.

Fields

Field Field Type Null Description
ID uniqueidentifier Event ID
MAINEVENTID uniqueidentifier yes Main Event System ID
NAME nvarchar(100) Event name
DESCRIPTION nvarchar(255) Event description
STARTDATE date Event start date
STARTTIME UDT_HOURMINUTE Event start time
ENDDATE date Event end date
ENDTIME UDT_HOURMINUTE Event end time
CAPACITY int Capacity
EVENTLOCATIONID uniqueidentifier yes Location system ID
EVENTLOCATIONCONTACTID uniqueidentifier yes Event contact ID
EVENTCONTACTNAME nvarchar(154) yes Event contact name
EVENTCONTACTEMAIL UDT_EMAILADDRESS yes Event contact email
EVENTCONTACTPHONE nvarchar(100) yes Event contact phone
ISACTIVE bit Active
ADDEDBY_APPLICATION nvarchar(200) yes Added by application
ADDEDBY_USERNAME nvarchar(128) yes Added by user name
CHANGEDBY_APPLICATION nvarchar(200) yes Changed by application
CHANGEDBY_USERNAME nvarchar(128) yes Changed by user name
DATEADDED datetime Date added
DATECHANGED datetime Date changed
TSLONG bigint yes Timestamp value
SEATINGBASIS varchar(27) yes Seating basis
SEATINGAVAILABLE int yes Total seating available
SEATINGCAPACITY int yes Total seating capacity
KPICONTEXTRECORDID nvarchar(100) yes KPIs context record ID
CATEGORY nvarchar(100) yes Category
APPEALID uniqueidentifier yes Team fundraising appeal ID
PROGRAMID uniqueidentifier yes Program system ID
APPEALNAME nvarchar(100) yes Team fundraising appeal
EVENTLOCATIONROOMID uniqueidentifier yes Room/Unit ID
PARENTEVENTID uniqueidentifier yes Parent event ID
LOOKUPID nvarchar(100) yes Event lookup ID
BASECURRENCYID uniqueidentifier yes Base currency ID
EVENTURL UDT_WEBADDRESS yes Event login link
EVENTCATEGORY nvarchar(100) yes Event category
REPORTYEAR UDT_YEAR yes Report year
PREVIOUSEVENT nvarchar(100) yes Previous event
PREVIOUSEVENTID uniqueidentifier yes Previous event ID
EVENTTYPE nvarchar(43) yes Event type
SUPPORTPHONE nvarchar(100) yes Event support phone
SUPPORTURL UDT_WEBADDRESS yes Event support URL
SUPPORTEMAIL UDT_EMAILADDRESS yes Event support email
DESIGNATIONSONFEES bit
EVENTCATEGORYCODEID uniqueidentifier yes

Definition

Copy 
/*
Generated by Blackbaud AppFx Platform
Date:  10/23/2017 7:39:11 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.173.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EVENT AS



        select
          EVENT.ID,
          EVENT.MAINEVENTID,
          EVENT.NAME,
          EVENT.DESCRIPTION,
          EVENT.STARTDATE,
          EVENT.STARTTIME,
          EVENT.ENDDATE,
          EVENT.ENDTIME,
          EVENT.CAPACITY,
          EVENT.EVENTLOCATIONID,
          EVENT.EVENTLOCATIONCONTACTID,
          NF_C.NAME AS EVENTCONTACTNAME,
          EMAIL.EMAILADDRESS AS EVENTCONTACTEMAIL,
          PHONE.NUMBER AS EVENTCONTACTPHONE,
          EVENT.ISACTIVE,
          ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
          ADDEDBY.USERNAME as ADDEDBY_USERNAME,
          CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
          CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
          EVENT.DATEADDED,
          EVENT.DATECHANGED,
          EVENT.TSLONG,
          case
            when SEATING.SUBSECTIONTYPECODE = 0 then 'Sections, Tables, and Seats'
            when SEATING.SUBSECTIONTYPECODE = 1 then 'Sections, Rows, and Seats'
            when SEATING.SUBSECTIONTYPECODE = 2 then 'Sections and Seats'
          end [SEATINGBASIS],
          dbo.UFN_EVENTSEATING_GETSEATAVAILABLE(EVENT.ID) [SEATINGAVAILABLE],
          dbo.UFN_EVENTSEATING_GETSEATCAPACITY(EVENT.ID) [SEATINGCAPACITY],
          cast(EVENT.ID as nvarchar(100)) as [KPICONTEXTRECORDID],
          (SELECT DESCRIPTION FROM dbo.EVENTCATEGORYCODE WHERE ID=EVENT.EVENTCATEGORYCODEID) as [CATEGORY],
          EVENT.APPEALID,
          EVENT.PROGRAMID,
          APPEAL.NAME APPEALNAME,
          EVENT.EVENTLOCATIONROOMID,
          PARENTHIERARCHY.ID PARENTEVENTID,
          EVENT.LOOKUPID,
          EVENT.BASECURRENCYID,
          EVENTEXTENSION.EVENTURL,
          EC.DESCRIPTION AS EVENTCATEGORY,
          EVENTEXTENSION.EVENTYEAR REPORTYEAR,
          PREVIOUSEVENT.NAME PREVIOUSEVENT,
          PREVIOUSEVENT.ID PREVIOUSEVENTID,
          EVENTEXTENSION.EVENTTYPE ,
          EVENTEXTENSION.SUPPORTPHONE,
          EVENTEXTENSION.SUPPORTURL,
          EVENTEXTENSION.EVENTSUPPORTEMAIL SUPPORTEMAIL,
          EVENT.DESIGNATIONSONFEES,
          [EVENT].[EVENTCATEGORYCODEID]
          /*#EXTENSION*/
        from
          dbo.EVENT
          left join dbo.EVENTSEATING as SEATING on SEATING.ID = EVENT.ID
          left join dbo.CHANGEAGENT as ADDEDBY ON ADDEDBY.ID = EVENT.ADDEDBYID
          left join dbo.CHANGEAGENT as CHANGEDBY ON CHANGEDBY.ID = EVENT.CHANGEDBYID
          left join dbo.APPEAL on APPEAL.ID = EVENT.APPEALID
          left join dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
          left join dbo.EVENTHIERARCHY PARENTHIERARCHY on PARENTHIERARCHY.HIERARCHYPATH = EVENTHIERARCHY.HIERARCHYPATH.GetAncestor(1)
          left join dbo.EVENTEXTENSION ON EVENTEXTENSION.EVENTID=EVENT.ID
          left join dbo.EVENT PREVIOUSEVENT on PREVIOUSEVENT.ID=EVENTEXTENSION.PRIORYEAREVENTID
          left join dbo.EMAILADDRESS EMAIL on EVENT.EVENTLOCATIONCONTACTID = EMAIL.CONSTITUENTID and EMAIL.ISPRIMARY = 1
          left join dbo.PHONE on EVENT.EVENTLOCATIONCONTACTID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
          left join dbo.EVENTCATEGORYCODE EC ON EC.ID=EVENT.EVENTCATEGORYCODEID
          outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENT.EVENTLOCATIONCONTACTID) NF_C
        where
          EVENT.PROGRAMID is null