USP_DATALIST_JOBOCCURRENCE

CREATE procedure dbo.[USP_DATALIST_JOBOCCURRENCE]
(
  @JOBID uniqueidentifier,
  @JOBOCCURRENCETYPE tinyint = null,
  @ACTIVEONLY bit = 0,
  @LOCATIONCODEID uniqueidentifier = null,
  @DEPARTMENTCODEID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @CURRENCYCODE tinyint = 0
)
as
  set nocount on;

  declare @FROM datetime;
  declare @TO datetime;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  set @FROM = dbo.[UFN_DATE_GETEARLIESTTIME](getdate());
  set @TO = dateadd(yy, 1, @FROM);

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

  select
    [JOBOCCURRENCE].[ID], 
    [JOBOCCURRENCE].[DESCRIPTION],
    [JOBOCCURRENCE].[ISACTIVE],
    [JOBOCCURRENCE].[ID],
    [JOBOCCURRENCE].[VOLUNTEERSNEEDED],
    coalesce([JOBOCCURRENCE].[STARTDATE], [JOBOCCURRENCE].[RECURRENCESTARTDATE]),
    coalesce([JOBOCCURRENCE].[ENDDATE], [JOBOCCURRENCE].[RECURRENCEENDDATE]),
    case when [JOBOCCURRENCE].[RECURRENCESTARTDATE] is null then [JOBOCCURRENCE].[STARTMONTHDAY] else null end as [STARTMONTHDAY],
    case when [JOBOCCURRENCE].[RECURRENCEENDDATE] is null then [JOBOCCURRENCE].[ENDMONTHDAY] else null end as [ENDMONTHDAY],
    [JOBOCCURRENCE].[STARTTIME],
    [JOBOCCURRENCE].[ENDTIME],
    case [JOBOCCURRENCE].[TYPECODE] when 0 then '' else [JOBOCCURRENCE].[DAYOFWEEK] end,
    [JOBOCCURRENCE].[TYPE],
    case @CURRENCYCODE when 1 then [JOBOCCURRENCE].[ORGANIZATIONESTIMATEDVALUE] else [JOBOCCURRENCE].[ESTIMATEDVALUE] end as [ESTIMATEDVALUE],
    case @CURRENCYCODE when 1 then @ORGANIZATIONCURRENCYID else [JOBOCCURRENCE].[BASECURRENCYID] end as [BASECURRENCYID],
    dbo.[UFN_EVENT_GETNAME]([JOBOCCURRENCE].[EVENTID]) as [EVENTNAME]
  from dbo.[JOBOCCURRENCE]
  where [JOBOCCURRENCE].[JOBID] = @JOBID
  and [TYPECODE] = coalesce(@JOBOCCURRENCETYPE, [TYPECODE])
  and 1 = case when @ACTIVEONLY = 1 then 1 else [ISACTIVE] end
  and (@LOCATIONCODEID is null or @LOCATIONCODEID = [LOCATIONCODEID])
  and (@DEPARTMENTCODEID is null or @DEPARTMENTCODEID = [DEPARTMENTCODEID])
  and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [JOBOCCURRENCE].[SITEID]) = 1
  order by
    [JOBOCCURRENCE].[STARTDATE],
    [JOBOCCURRENCE].[STARTMONTHDAY],
    [JOBOCCURRENCE].[STARTTIME],
    [JOBOCCURRENCE].[DESCRIPTION];