USP_DATAFORMTEMPLATE_VIEW_PROGRAMSUMMARY

The load procedure used by the view dataform template "Program Summary View"

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.
@DESCRIPTION	nvarchar(255)	INOUT	Description
@CAPACITY	int	INOUT	Capacity
@LOCATIONS	nvarchar(1500)	INOUT	Location
@ISDAILYADMISSION	bit	INOUT	ISDAILYADMISSION
@ONSALEINFORMATION	xml	INOUT	Available for sale
@ISACTIVE	bit	INOUT	Status
@CATEGORY	nvarchar(100)	INOUT	Category
@ISPREREGISTERED	bit	INOUT	Requires registration
@ACKNOWLEDGEMENTEMAILTEMPLATEID	int	INOUT

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROGRAMSUMMARY
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @DESCRIPTION nvarchar(255) = null output,
                @CAPACITY integer=null output,
                @LOCATIONS nvarchar(1500)=null output,
                @ISDAILYADMISSION bit = null output,
                @ONSALEINFORMATION xml = null output,
                @ISACTIVE bit = null output,
                @CATEGORY nvarchar(100) = null output,
                @ISPREREGISTERED bit = null output,
                @ACKNOWLEDGEMENTEMAILTEMPLATEID int = null output
            )
            as
            set nocount on;
                set @DATALOADED = 0;

                select 
                     @DATALOADED = 1,
                     @DESCRIPTION = PROGRAM.DESCRIPTION,
                     @CAPACITY=CAPACITY,
                     @LOCATIONS=dbo.UFN_PROGRAM_GETLOCATIONNAME(@ID),
                     @ISDAILYADMISSION = ISDAILYADMISSION,
                     @ISACTIVE = ISACTIVE,
                     @CATEGORY = PROGRAMCATEGORYCODE.DESCRIPTION,
                     @ISPREREGISTERED = ISPREREGISTERED, 
                     @ACKNOWLEDGEMENTEMAILTEMPLATEID = [PROGRAM_MICROSITEEMAILTEMPLATE].[EMAILTEMPLATEID]
                from dbo.PROGRAM
                left join dbo.PROGRAMCATEGORYCODE on PROGRAM.PROGRAMCATEGORYCODEID = PROGRAMCATEGORYCODE.ID
                left join dbo.[PROGRAM_MICROSITEEMAILTEMPLATE] on
                    ([PROGRAM].[ID] = [PROGRAM_MICROSITEEMAILTEMPLATE].[PROGRAMID]) and ([PROGRAM_MICROSITEEMAILTEMPLATE].[ACTIVE] = 1)
                where PROGRAM.ID = @ID

                if @ISDAILYADMISSION = 1
                begin
                    set @ONSALEINFORMATION =
                    (
                        select
                            SALESMETHOD.ID as SALESMETHODID,
                            SALESMETHOD.TYPE as SALESMETHODTYPE,
                            1
                        from dbo.SALESMETHOD
                        inner join dbo.PROGRAMSALESMETHOD on
                            SALESMETHOD.ID = PROGRAMSALESMETHOD.SALESMETHODID
                        where 
                            SALESMETHOD.ISACTIVE = 1 and
                            PROGRAMSALESMETHOD.PROGRAMID = @ID
                        for xml raw ('ITEM'), type, elements, root('ONSALEINFORMATION'), BINARY BASE64
                    )
                end

            return 0;