USP_DATAFORMTEMPLATE_VIEW_JOBOCCURRENCESCHEDULEPAGEDATA

The load procedure used by the view dataform template "Job Occurrence Schedule Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@JOBOCCURRENCEID	uniqueidentifier	INOUT	JOBOCCURRENCEID
@JOBID	uniqueidentifier	INOUT	JOBID
@FILTERSTARTDATE	datetime	INOUT	FILTERSTARTDATE
@JOBNAME	nvarchar(50)	INOUT	JOBNAME
@SECTIONHEADER	nvarchar(500)	INOUT	SECTIONHEADER
@STARTTIMEDATE	datetime	INOUT	STARTTIMEDATE
@ENDTIMEDATE	datetime	INOUT	ENDTIMEDATE

Definition

Copy



                create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_JOBOCCURRENCESCHEDULEPAGEDATA
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @JOBOCCURRENCEID uniqueidentifier = null output,
                    @JOBID uniqueidentifier = null output,
                    @FILTERSTARTDATE datetime = null output,
                    @JOBNAME nvarchar(50) = null output,
                    @SECTIONHEADER nvarchar(500) = null output,
                    @STARTTIMEDATE datetime = null output,
                    @ENDTIMEDATE datetime = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    declare @FROM datetime;
                    declare @TO datetime;

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

                    select
                        @DATALOADED = 1,
                        @JOBOCCURRENCEID = @ID,
                        @JOBNAME = JOB.NAME,
                        @JOBID = JOB.ID,
                        @STARTTIMEDATE = case when len(JOBOCCURRENCE.STARTTIME)=4 then convert(datetime, left(JOBOCCURRENCE.STARTTIME, 2) + ':' + right(JOBOCCURRENCE.STARTTIME, 2)) else null end,
                        @ENDTIMEDATE = case when len(JOBOCCURRENCE.ENDTIME)=4 then convert(datetime, left(JOBOCCURRENCE.ENDTIME, 2) + ':' + right(JOBOCCURRENCE.ENDTIME, 2)) else null end,
                        @SECTIONHEADER = JOBOCCURRENCE.DESCRIPTION,
                        @FILTERSTARTDATE = case when TYPECODE = 0 then JOBOCCURRENCE.STARTDATE
                                            else (select min(ASSIGNMENTDATE)
                                                from dbo.[UFN_JOBOCCURRENCE_SCHEDULE](@FROM,@TO,JOBOCCURRENCE.ID, null) SUB)
                                        end
                    from dbo.JOBOCCURRENCE
                    inner join dbo.JOB
                        on JOBOCCURRENCE.JOBID = JOB.ID
                    where JOBOCCURRENCE.ID = @ID

                    return 0;