V_QUERY_JOBOCCURRENCE
This provides the ability to query all job occurrence fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| JOBID | uniqueidentifier | Job system record ID | |
| DESCRIPTION | nvarchar(30) | Occurrence name | |
| DEPARTMENT_TRANSLATION | nvarchar(100) | yes | Department |
| LOCATION_TRANSLATION | nvarchar(100) | yes | Location |
| TYPECODE | tinyint | Type code | |
| STARTDATE | datetime | yes | Start date |
| ENDDATE | datetime | yes | End date |
| STARTMONTHDAY | UDT_MONTHDAY | yes | Start month day |
| ENDMONTHDAY | UDT_MONTHDAY | yes | End month day |
| STARTTIME | UDT_HOURMINUTE | Start time | |
| ENDTIME | UDT_HOURMINUTE | End time | |
| DAYOFWEEKCODE | tinyint | Day of week code | |
| EVENT_TRANSLATION | nvarchar(100) | yes | Event name |
| VOLUNTEERSNEEDED | int | Volunteers needed | |
| COMMENTS | nvarchar(max) | Comments | |
| 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 |
| SITENAME | nvarchar(250) | yes | Site |
| NAME | nvarchar(100) | yes | Job name |
| TYPE | nvarchar(9) | yes | Type |
| DAYOFWEEK | nvarchar(9) | yes | Day of week |
| EVENTID | uniqueidentifier | yes | Event system record ID |
| ESTIMATEDVALUE | money | Estimated value | |
| BASECURRENCYID | uniqueidentifier | yes | Base currency |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization currency exchange rate |
| ORGANIZATIONESTIMATEDVALUE | money | Estimated value (organization currency) | |
| SITEID | uniqueidentifier | yes | Site ID |
| DEPARTMENTCODEID | uniqueidentifier | yes | |
| LOCATIONCODEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:50 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_JOBOCCURRENCE AS
select
[JOBOCCURRENCE].[ID],
[JOBOCCURRENCE].[JOBID],
[JOBOCCURRENCE].[DESCRIPTION],
(select [DEPARTMENTCODE].[DESCRIPTION] from dbo.[DEPARTMENTCODE] where [DEPARTMENTCODE].[ID] = [JOBOCCURRENCE].[DEPARTMENTCODEID]) as [DEPARTMENT_TRANSLATION],
(select [VOLUNTEERLOCATIONCODE].[DESCRIPTION] from dbo.[VOLUNTEERLOCATIONCODE] where [VOLUNTEERLOCATIONCODE].[ID] = [JOBOCCURRENCE].[LOCATIONCODEID]) as [LOCATION_TRANSLATION],
[JOBOCCURRENCE].[TYPECODE],
coalesce([JOBOCCURRENCE].[STARTDATE], [JOBOCCURRENCE].[RECURRENCESTARTDATE]) as [STARTDATE],
coalesce([JOBOCCURRENCE].[ENDDATE], [JOBOCCURRENCE].[RECURRENCEENDDATE]) as [ENDDATE],
case [JOBOCCURRENCE].[RECURRENCESTARTDATE]
when null then[JOBOCCURRENCE]. [STARTMONTHDAY]
else dbo.UFN_MONTHDAY_GETFROMDATE([JOBOCCURRENCE].[RECURRENCESTARTDATE])
end as [STARTMONTHDAY],
case [JOBOCCURRENCE].[RECURRENCEENDDATE]
when null then [JOBOCCURRENCE].[ENDMONTHDAY]
else dbo.UFN_MONTHDAY_GETFROMDATE([JOBOCCURRENCE].[RECURRENCEENDDATE])
end as [ENDMONTHDAY],
[JOBOCCURRENCE].[STARTTIME],
[JOBOCCURRENCE].[ENDTIME],
[JOBOCCURRENCE].[DAYOFWEEKCODE],
(select [EVENT].[NAME] from dbo.[EVENT] where [EVENT].[ID] = [JOBOCCURRENCE].[EVENTID]) as [EVENT_TRANSLATION],
[JOBOCCURRENCE].[VOLUNTEERSNEEDED],
[JOBOCCURRENCE].[COMMENTS],
[JOBOCCURRENCE].[ISACTIVE],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[JOBOCCURRENCE].[DATEADDED],
[JOBOCCURRENCE].[DATECHANGED],
[JOBOCCURRENCE].[TSLONG],
coalesce((select [NAME] from dbo.[SITE] where [ID] = [JOBOCCURRENCE].[SITEID]), '') as [SITENAME],
[JOB].[NAME],
[JOBOCCURRENCE].[TYPE],
case [JOBOCCURRENCE].[TYPECODE] when 0 then '' else [JOBOCCURRENCE].[DAYOFWEEK] end as [DAYOFWEEK],
[JOBOCCURRENCE].[EVENTID],
[JOBOCCURRENCE].[ESTIMATEDVALUE],
[JOBOCCURRENCE].[BASECURRENCYID],
[JOBOCCURRENCE].[ORGANIZATIONEXCHANGERATEID],
[JOBOCCURRENCE].[ORGANIZATIONESTIMATEDVALUE],
[JOBOCCURRENCE].[SITEID],
[JOBOCCURRENCE].[DEPARTMENTCODEID],
[JOBOCCURRENCE].[LOCATIONCODEID]
/*#EXTENSION*/
from dbo.[JOBOCCURRENCE]
left outer join dbo.[JOB] on [JOB].[ID] = [JOBOCCURRENCE].[JOBID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [JOBOCCURRENCE].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [JOBOCCURRENCE].[CHANGEDBYID];