USP_DATAFORMTEMPLATE_VIEW_CREDITORDERVIEW2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITORDERVIEW2
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ITEMS xml = null output,
    @TOTAL money = null output,
    @PAYMENTS xml = null output,
    @TAXES xml = null output,
    @TAXESBYITEM xml = null output,
    @ORDERDISCOUNTS money = null output,
    @OTHERPAYMENTMETHODS xml = null output,
    @TRANSACTIONID uniqueidentifier = null output,
    @TIMESTAMP bigint = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTNAME nvarchar(154) = null output,
    @ORDERNUMBER int = null output,
    @ORDERHASTICKETS bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    begin try
        --Error if it's a group sales order
        if exists (select * from dbo.RESERVATION where ID = @ID) begin
            raiserror('The selected order is a group sales order.  Group sales orders may not be refunded.', 13, 1);
        end

        declare @CHANGEAGENTID uniqueidentifier;
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

        declare @CURRENTDATE datetime = getdate();

        select
            @DATALOADED = 1,
            @ITEMS = dbo.UFN_CREDIT_GETSALESORDERITEMS2_TOITEMLISTXML(@ID),
            @PAYMENTS = dbo.UFN_CREDIT_GETORDERPAYMENTS_2_TOITEMLISTXML(@ID),
            @TAXES = dbo.UFN_CREDIT_GETSALESORDERTAXITEMS_TOITEMLISTXML(@ID),
            @TAXESBYITEM = dbo.UFN_CREDIT_GETTAXES_TOITEMLISTXML(@ID),
            @TIMESTAMP = TSLONG,
            @CONSTITUENTID = SALESORDER.CONSTITUENTID,
            @CONSTITUENTNAME = dbo.UFN_CONSTITUENTSEARCH_DISPLAYNAME(SALESORDER.CONSTITUENTID),
            @ORDERNUMBER = SEQUENCEID
        from dbo.SALESORDER
        where ID = @ID;

        select
            @ORDERDISCOUNTS = isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0)
        from
            dbo.SALESORDERITEM
        left join
            dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
        inner join
            dbo.SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID
        where
            SALESORDERITEM.SALESORDERID = @ID
            and EXT.ID is null;

        select
            @TOTAL = sum(T.item.value('(@AMOUNTPAID)[1]','decimal(10,4)'))
        from @PAYMENTS.nodes('/PAYMENTS/ITEM') T(item);

        select @OTHERPAYMENTMETHODS = (
            select
                ID,
                [DESCRIPTION]
            from dbo.OTHERPAYMENTMETHODCODE
            for xml raw ('ITEM'), type, root('OTHERPAYMENTMETHODS'), binary base64
        );

        set @TRANSACTIONID = @ID;

        --Error if there are no items to return on this order
        if not exists (select * from @ITEMS.nodes('/ITEMS/ITEM') T(item)) begin
            raiserror('The selected order has no items to refund.', 13, 1);
        end

        if exists(
            select 1
            from dbo.UFN_SALESORDER_TICKETS (@ID)
        )
        begin
            set @ORDERHASTICKETS = 1
        end
        else
        begin
            set @ORDERHASTICKETS = 0
        end

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;