USP_DATALIST_EVENTPROFILEREPORT_JOBOCCURRENCES

CREATE procedure dbo.USP_DATALIST_EVENTPROFILEREPORT_JOBOCCURRENCES
(
    @EVENTID uniqueidentifier,
  @CURRENCYCODE tinyint = null,
    @ISVISIBLE bit = 1,
    @CURRENTAPPUSERID uniqueidentifier
)
as
    set nocount on;

    if @ISVISIBLE = 1
        begin
            declare @ISADMIN bit;
            declare @APPUSER_IN_NONRACROLE bit;
            declare @APPUSER_IN_NOSECGROUPROLE bit;
          declare @SELECTEDCURRENCYID uniqueidentifier = null;

            set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
            set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
            set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

      if @CURRENCYCODE = 0
      begin
        select @SELECTEDCURRENCYID = EVENT.BASECURRENCYID
        from dbo.EVENT
        where EVENT.ID = @EVENTID
      end
      else
      begin
          set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
      end

            select         
                JOBOCCURRENCE.ID as JOBOCCURRENCEID,
                JOB.NAME as JOBNAME,
                JOBOCCURRENCE.DESCRIPTION as OCCURRENCENAME,
                JOBOCCURRENCE.ISACTIVE,
                JOBOCCURRENCE.VOLUNTEERSNEEDED,    
                JOBOCCURRENCE.TYPECODE,
                JOBOCCURRENCE.TYPE,
                JOBOCCURRENCE.STARTDATE,
                JOBOCCURRENCE.ENDDATE,
                left(JOBOCCURRENCE.STARTMONTHDAY, 2) + '/' + right(JOBOCCURRENCE.STARTMONTHDAY, 2) as STARTMONTHDAY,
                left(JOBOCCURRENCE.ENDMONTHDAY, 2) + '/' + right(JOBOCCURRENCE.ENDMONTHDAY, 2) as ENDMONTHDAY,                
                JOBOCCURRENCE.DAYOFWEEK,
                dbo.UFN_HOURMINUTE_DISPLAYTIME(JOBOCCURRENCE.STARTTIME) as STARTTIME,
                dbo.UFN_HOURMINUTE_DISPLAYTIME(JOBOCCURRENCE.ENDTIME) as ENDTIME,
                case
          when (@CURRENCYCODE = 0 and (JOBOCCURRENCE.BASECURRENCYID = @SELECTEDCURRENCYID)) then 
            JOBOCCURRENCE.ESTIMATEDVALUE
          when @CURRENCYCODE = 1 then JOBOCCURRENCE.ORGANIZATIONESTIMATEDVALUE
          else JOBOCCURRENCE.ESTIMATEDVALUE
        end as ESTIMATEDVALUE,
                CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                CURRENCYPROPERTIES.CURRENCYSYMBOL,
                CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                CURRENCYPROPERTIES.DECIMALDIGITS

            from 
                dbo.JOBOCCURRENCE
            inner join dbo.JOB
                on JOBOCCURRENCE.JOBID = JOB.ID
      outer apply
        dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES

            where 
                JOBOCCURRENCE.EVENTID = @EVENTID
                and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, JOBOCCURRENCE.SITEID) = 1

        end