USP_DATAFORMTEMPLATE_VIEW_RESCHEDULETICKETSORDER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESCHEDULETICKETSORDER
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTNAME nvarchar(154) = null output,
    @ITEMS xml = null output,
    @ORDERNUMBER nvarchar(10) = null output,
    @CURRENCYID uniqueidentifier = null output,
    @TIMESTAMP bigint = null output
)
as
    set nocount on;
    set @DATALOADED = 0;

    declare @SALESMETHODTYPECODE tinyint;

    with TICKET_CTE as (
        select
            TICKET.ID,
            TICKET.PROGRAMID,
            TICKET.EVENTID,
            TICKET.PRICETYPECODEID,
            case when TICKET.STATUSCODE = 2 or TICKET.ISREFUNDED = 1 then 1 else 0 end as ISREFUNDED,
            TICKET.AMOUNTPAID,
            TICKET.TICKETNUMBER,
            row_number() over(partition by TICKET.EVENTID, TICKET.PRICETYPECODEID order by TICKET.ID) ROWNUM
        from dbo.TICKET
            inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
        where SALESORDERITEM.SALESORDERID = @ID
            and SALESORDERITEM.TYPECODE = 0
            and TICKET.EVENTID is not null
    ),
    REGISTRANT_CTE as (
        select
            REGISTRANT.ID,
            REGISTRANT.EVENTID,
            SALESORDERITEMTICKET.PRICETYPECODEID,
            REGISTRANT.CONSTITUENTID,
            row_number() over(partition by REGISTRANT.EVENTID, SALESORDERITEMTICKET.PRICETYPECODEID order by REGISTRANT.ID) ROWNUM
        from dbo.SALESORDERITEM
            inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
            inner join dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEM.ID
            inner join dbo.REGISTRANT on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
        where SALESORDERITEM.SALESORDERID = @ID
            and SALESORDERITEMTICKETREGISTRANT.ISHISTORICAL = 0
    )
    select
        @DATALOADED = 1,
        @CONSTITUENTID = SALESORDER.CONSTITUENTID,
        @CONSTITUENTNAME = dbo.UFN_CONSTITUENTSEARCH_DISPLAYNAME(SALESORDER.CONSTITUENTID),
        @ITEMS = (
            select
                TICKET_CTE.ID as [@TICKETID],
                TICKET_CTE.PROGRAMID as [@PROGRAMID],
                TICKET_CTE.EVENTID as [@ORIGINALEVENTID],
                EVENT.NAME as [@ORIGINALEVENTNAME],
                EVENT.STARTDATETIME as [@ORIGINALEVENTTIME],
                TICKET_CTE.PRICETYPECODEID as [@PRICETYPECODEID],
                PRICETYPECODE.DESCRIPTION as [@PRICETYPE],
                REGISTRANT_CTE.ID as [@REGISTRANTID],
                dbo.UFN_CONSTITUENT_BUILDNAME(REGISTRANT_CTE.CONSTITUENTID) as [@REGISTRANTNAME],
                TICKET_CTE.ISREFUNDED as [@ISREFUNDED],
                TICKET_CTE.AMOUNTPAID as [@AMOUNTPAID],
                TICKET_CTE.TICKETNUMBER as [@TICKETNUMBER]
            from TICKET_CTE
                inner join dbo.EVENT on EVENT.ID = TICKET_CTE.EVENTID
                inner join dbo.PRICETYPECODE on PRICETYPECODE.ID = TICKET_CTE.PRICETYPECODEID
                left join REGISTRANT_CTE on (REGISTRANT_CTE.ROWNUM = TICKET_CTE.ROWNUM and REGISTRANT_CTE.EVENTID = TICKET_CTE.EVENTID and REGISTRANT_CTE.PRICETYPECODEID = TICKET_CTE.PRICETYPECODEID)
            for xml path('ITEM'),type,elements,root('ITEMS'),binary base64
        ),
        @ORDERNUMBER = cast(SALESORDER.SEQUENCEID as nvarchar(10)),
        @CURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
        @TIMESTAMP = SALESORDER.TSLONG,
        @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE
    from dbo.SALESORDER
        left outer join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = SALESORDER.REVENUEID
    where SALESORDER.ID = @ID and SALESORDER.STATUSCODE = 1;  -- Complete

    if @SALESMETHODTYPECODE = 3
    begin
        raiserror('You cannot open a group sales order from here.  Please select the Sales Order Page to open this record or create a Group Sales Reservations query for other browsing options.', 13, 1);
    end

    return 0;