USP_DATALIST_EVENTJOBOCCURRENCE

Displays the job occurrences linked to a given event.

Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@JOBOCCURRENCETYPE	tinyint	IN	Type
@ACTIVEONLY	bit	IN	Active only
@LOCATIONCODEID	uniqueidentifier	IN	Location
@DEPARTMENTCODEID	uniqueidentifier	IN	Department
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@INCLUDESUBEVENTS	bit	IN	Include sub-events
@CURRENCYCODE	tinyint	IN	Currency

Definition

Copy



CREATE procedure dbo.USP_DATALIST_EVENTJOBOCCURRENCE
(
  @EVENTID uniqueidentifier,
  @JOBOCCURRENCETYPE tinyint = null, 
  @ACTIVEONLY bit = 1, 
  @LOCATIONCODEID uniqueidentifier = null, 
  @DEPARTMENTCODEID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @INCLUDESUBEVENTS bit = 0,
  @CURRENCYCODE tinyint = 0
)
as
  set nocount on;

  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  if @CURRENCYCODE = 1 set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  select 
    [JOBOCCURRENCE].[ID],
    [JOB].[ID],
    [JOB].[NAME],
    [JOBOCCURRENCE].[DESCRIPTION],
    [JOBOCCURRENCE].[ISACTIVE],
    [JOBOCCURRENCE].[STARTTIME],
    [JOBOCCURRENCE].[ENDTIME],
    [JOBOCCURRENCE].[TYPE],
    case @CURRENCYCODE when 1 then [JOBOCCURRENCE].[ORGANIZATIONESTIMATEDVALUE] else [JOBOCCURRENCE].[ESTIMATEDVALUE] end as [ESTIMATEDVALUE],
    [EVENTS].[NAME] as [EVENTNAME],
    case @CURRENCYCODE when 1 then @ORGANIZATIONCURRENCYID else [JOBOCCURRENCE].[BASECURRENCYID] end as [BASECURRENCYID]
  from dbo.[UFN_CHILDEVENTSWITHSITEACCESS](@EVENTID, @CURRENTAPPUSERID) as [EVENTS]
  inner join dbo.[JOBOCCURRENCE] on [JOBOCCURRENCE].[EVENTID] = [EVENTS].[ID]
  inner join dbo.[JOB] on [JOBOCCURRENCE].[JOBID] = [JOB].[ID]
  where 
    TYPECODE = coalesce(@JOBOCCURRENCETYPE, [JOBOCCURRENCE].[TYPECODE])
    and (@ACTIVEONLY = 0 or [JOBOCCURRENCE].[ISACTIVE] = 1)
    and (@LOCATIONCODEID is null or @LOCATIONCODEID = [JOBOCCURRENCE].[LOCATIONCODEID])
    and (@DEPARTMENTCODEID is null or @DEPARTMENTCODEID = [JOBOCCURRENCE].[DEPARTMENTCODEID])
    and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [JOBOCCURRENCE].[SITEID]) = 1
    and ([EVENTS].[ID] = @EVENTID or @INCLUDESUBEVENTS = 1)
  order by
    [EVENTS].[LEVEL] asc,
    [EVENTS].[NAME] asc,
    [JOBOCCURRENCE].[STARTDATE],
    [JOBOCCURRENCE].[STARTMONTHDAY],
    [JOBOCCURRENCE].[STARTTIME],
    [JOBOCCURRENCE].[DESCRIPTION];