USP_DATAFORMTEMPLATE_VIEW_ORDERTICKETDATALISTDETAIL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ORDERTICKETDATALISTDETAIL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ISCOMBOTICKET bit = null output,
    @RESCHEDULINGS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @ISCOMBOTICKET = case
            when not exists(
                select top 1 ID
                from dbo.SALESORDERITEMTICKET
                where SALESORDERITEMTICKET.ID = @ID
            )
                then 1
            else 0
        end;

    if @ISCOMBOTICKET = 0 
    begin
        select        
            @RESCHEDULINGS = (
                select
                    EVENT.NAME PREVIOUSEVENTNAME,
                    EVENT.STARTDATE PREVIOUSEVENTDATE,
                    EVENT.STARTTIME PREVIOUSEVENTTIME,
                    COUNT(TICKETID) QUANTITY,
                    RESCHEDULETICKETSHISTORY.RESCHEDULEDDATE,
                    APPUSER.DISPLAYNAME RESCHEDULEDBYNAME
                from dbo.RESCHEDULETICKETSHISTORY
                    inner join dbo.EVENT on EVENT.ID = RESCHEDULETICKETSHISTORY.PREVIOUSEVENTID
                    inner join dbo.APPUSER on APPUSER.ID = RESCHEDULETICKETSHISTORY.RESCHEDULEDBYID
                where RESCHEDULETICKETSHISTORY.SALESORDERITEMTICKETID = @ID
                group by
                    EVENT.NAME,
                    EVENT.STARTDATE,
                    EVENT.STARTTIME,
                    RESCHEDULETICKETSHISTORY.RESCHEDULEDDATE,
                    APPUSER.DISPLAYNAME
                order by RESCHEDULEDDATE desc
                for xml raw ('ITEM'), type, elements, root('RESCHEDULINGS'), BINARY BASE64
            );
        end

    return 0;