USP_DATAFORMTEMPLATE_EDIT_PREREGISTEREDPROGRAMEVENTTICKET_PRELOAD_2

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PREREGISTEREDPROGRAMEVENTTICKET_PRELOAD_2
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @SALESORDERID uniqueidentifier = null output,
                        @CONSTITUENTID uniqueidentifier = null output,
                        @SALESMETHODTYPECODE tinyint = null output,
                        @REGISTRANTS xml = null output,
                        @EVENTID uniqueidentifier = null output,
                        @OCCURSTODAY bit = null output,
                        @MARKREGISTRANTSATTENDED bit = null output,
                        @SALESORDERITEMTICKETPRICETYPECODEID uniqueidentifier = null output,
                        @EVENTREGISTRATIONINFORMATION xml = null output                        
                    )
                    as
                        set nocount on;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        select
                            @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE,
                            @SALESORDERID = SALESORDER.ID,
                            @EVENTID = SALESORDERITEMTICKET.EVENTID,
                            @SALESORDERITEMTICKETPRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID,
                            @TSLONG = SALESORDERITEM.TSLONG,                            
                            @DATALOADED = 1
                        from dbo.SALESORDERITEM
                        inner join dbo.SALESORDERITEMTICKET
                            on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                        inner join dbo.SALESORDER
                            on SALESORDERITEM.SALESORDERID = SALESORDER.ID
                        where SALESORDERITEM.ID = @ID;

                        if @DATALOADED = 1
                        begin
                            --7/18/11: Constituent is the host of the registrants -- no longer the patron on the order
                            declare @HOSTREGISTRANTID uniqueidentifier
                            select top 1 @HOSTREGISTRANTID = coalesce([REGISTRANT].[GUESTOFREGISTRANTID], [REGISTRANT].[ID])
                            from dbo.[REGISTRANT]
                            inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
                                on [REGISTRANT].[ID] = [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID]
                            inner join dbo.[SALESORDERITEMTICKET]
                                on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
                            inner join dbo.[SALESORDERITEM]
                                on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
                            cross apply (
                                select case 
                                    when [REGISTRANT].[GUESTOFREGISTRANTID] is null then 1
                                    else 0
                                end as [VALUE]
                            ) [ISHOST]
                            where
                                [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                                [SALESORDERITEMTICKET].[EVENTID] = @EVENTID
                            order by [ISHOST].[VALUE] asc

                            select @CONSTITUENTID = [CONSTITUENTID]
                            from dbo.[REGISTRANT]
                            where [ID] = @HOSTREGISTRANTID

                            set @REGISTRANTS = (
                                select 
                                    REGISTRANT.ID,
                                    SALESORDERITEMTICKET.PRICETYPECODEID,
                                    REGISTRANT.CONSTITUENTID,
                                    REGISTRANT.NOTES,
                                    dbo.UFN_REGISTRANT_GETREGISTRATIONINFORMATION_TOITEMLISTXML(REGISTRANT.ID)
                                from dbo.SALESORDERITEM
                                inner join dbo.SALESORDERITEMTICKET
                                    on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                                left join dbo.SALESORDERITEMTICKETREGISTRANT
                                    on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID
                                left join dbo.REGISTRANT
                                    on 
                                        SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANT.ID and
                                        REGISTRANT.WILLNOTATTEND = 0
                                where 
                                    SALESORDERITEM.SALESORDERID = @SALESORDERID and
                                    SALESORDERITEMTICKET.EVENTID = @EVENTID
                                for xml raw('ITEM'), type, elements, root('REGISTRANTS'), binary base64
                            )

                            set @OCCURSTODAY = dbo.UFN_EVENT_OCCURSTODAY(@EVENTID)

                            if @OCCURSTODAY = 1
                            begin
                                -- If all of the registrants are marked as attended default checkbox to be checked
                                if not exists 
                                (
                                    select 
                                        1
                                    from dbo.REGISTRANT
                                    inner join dbo.SALESORDERITEMTICKETREGISTRANT
                                        on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
                                    inner join dbo.SALESORDERITEMTICKET
                                        on SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
                                    inner join dbo.SALESORDERITEM
                                        on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                                    where 
                                        SALESORDERITEM.SALESORDERID = @SALESORDERID and
                                        SALESORDERITEMTICKET.EVENTID = @EVENTID and
                                        REGISTRANT.WILLNOTATTEND = 0 and
                                        REGISTRANT.ATTENDED = 0
                                )
                                    set @MARKREGISTRANTSATTENDED = 1
                                else
                                    set @MARKREGISTRANTSATTENDED = 0

                            end
                            else
                                set @MARKREGISTRANTSATTENDED = 0

                            select @EVENTREGISTRATIONINFORMATION = dbo.UFN_EVENT_GETREGISTRATIONINFORMATION_TOITEMLISTXML(@EVENTID, null);
                        end

                        return 0;