USP_DATAFORMTEMPLATE_ADD_ITINERARYITEMEVENT_PRELOAD

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ITINERARYITEMEVENT_PRELOAD
                (
                    @ITINERARYID uniqueidentifier,
                    @EVENTS xml = null output,
                    @PRICINGCODE tinyint = null output,
                    @RESERVATIONRATESCALEINCLUDEALLPROGRAMS bit = null output,
                    @RESERVATIONRATESCALEPROGRAMS xml = null output,
                    @ITINERARYCAPACITY int = null output
                )
                as
                    set nocount on;

                    declare @ARRIVALDATETIMEWITHOFFSET datetimeoffset;
                    declare @MAXDATETIMEWITHOFFSET datetimeoffset;
                    declare @RESERVATIONID uniqueidentifier;

                    declare @CURRENTDATETIMEWITHOFFSET datetimeoffset

                    set @CURRENTDATETIMEWITHOFFSET = sysdatetimeoffset()

                    select
                        @ARRIVALDATETIMEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(dbo.UFN_DATE_ADDHOURMINUTE(ARRIVALDATE, ARRIVALTIME), 0),
                        @PRICINGCODE = RESERVATION.PRICINGCODE,
                        @RESERVATIONRATESCALEINCLUDEALLPROGRAMS = RESERVATIONRATESCALE.INCLUDEALLPROGRAMS,
                        @RESERVATIONRATESCALEPROGRAMS = dbo.UFN_RESERVATIONRATESCALE_GETPROGRAMS_TOITEMLISTXML(RESERVATION.ID),
                        @RESERVATIONID = ITINERARY.RESERVATIONID
                    from dbo.ITINERARY
                    inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
                    left outer join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
                    where ITINERARY.ID = @ITINERARYID

                    select @ITINERARYCAPACITY = sum(ITINERARYATTENDEE.QUANTITY)
                    from dbo.ITINERARYATTENDEE
                    where ITINERARYATTENDEE.ITINERARYID = @ITINERARYID

                    set @MAXDATETIMEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(dateadd(d, 4, dbo.UFN_DATE_GETLATESTTIME(@ARRIVALDATETIMEWITHOFFSET)), 0);


                    with BLOCKEDEVENTS_CTE as
                    (
                        select 
                            1 as BLOCK,
                            EVENTID
                        from 
                            dbo.ITINERARYITEM 
                            inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                            where ITINERARYITEM.BLOCKEVENT = 1 
                                and ITINERARY.RESERVATIONID = @RESERVATIONID
                                and ITINERARYITEM.ITEMTYPECODE = 0
                        group by EVENTID
                    )

                    select @EVENTS =
                    (
                        select 
                            EVENT.ID as EVENTID,
                            EVENT.PROGRAMID,
                            EVENT.NAME,
                            EVENT.STARTTIME,
                            EVENT.ENDTIME,
                            dbo.UFN_GETEVENT_TIMESPAN(EVENT.ID) as TIMESPAN,
                            dbo.UFN_EVENT_GETAVAILABILITY(EVENT.ID) as AVAILABILITY,
                            dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATION,
                            dbo.UFN_EVENTRESOURCE_GETRESOURCESTRING(EVENT.ID) as RESOURCES,
                            dbo.UFN_EVENTSTAFFRESOURCE_GETSTAFFRESOURCESTRING(EVENT.ID) as STAFFRESOURCES,
                            EVENT.STARTDATE,
                            EVENT.ENDDATE,
                            coalesce(BL.BLOCK, 0) as EVENTBLOCK
                        from dbo.EVENT
                            inner join dbo.EVENTSALESMETHOD on EVENT.ID = EVENTSALESMETHOD.EVENTID
                            inner join dbo.SALESMETHOD on EVENTSALESMETHOD.SALESMETHODID = SALESMETHOD.ID
                            left join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
                            left join BLOCKEDEVENTS_CTE BL on EVENT.ID = BL.EVENTID
                        where @ARRIVALDATETIMEWITHOFFSET <= EVENT.STARTDATETIMEWITHOFFSET
                            and @MAXDATETIMEWITHOFFSET >= EVENT.STARTDATETIMEWITHOFFSET
                            and EVENT.ISACTIVE = 1
                            and SALESMETHOD.TYPECODE = 3
                            and dbo.UFN_EVENT_ISONSALE_DATETIMEOFFSET(EVENT.ID, @CURRENTDATETIMEWITHOFFSET, 3) = 1
                            and PROGRAM.ISACTIVE = 1
                            and PROGRAM.ISPREREGISTERED = 0
                            and PROGRAM.ISDAILYADMISSION = 0
                        order by STARTDATETIME asc
                        for xml raw ('ITEM'), type, elements, root('EVENTS'), BINARY BASE64
                    );

                    return 0;