USP_RESERVATIONREFUNDADD_PRELOAD

CREATE procedure dbo.USP_RESERVATIONREFUNDADD_PRELOAD (
    @RESERVATIONID uniqueidentifier,
    @RESERVATIONNAME nvarchar(100) = null output,
    @SALESORDERSEQUENCEID int = null output,
    @PAYMENTINFORMATION xml = null output,
    @BALANCE money = null output
)
as
    set nocount on;

    select
        @RESERVATIONNAME = RESERVATION.NAME,
        @SALESORDERSEQUENCEID = SALESORDER.SEQUENCEID,
        @BALANCE = TOTALS.BALANCE
    from
        dbo.RESERVATION
    inner join
        dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
    outer apply
        dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
    where
        RESERVATION.ID = @RESERVATIONID;

    set @PAYMENTINFORMATION = (
        select
            SALESORDERPAYMENT.PAYMENTID as [@PAYMENTID],
            REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE as [@PAYMENTMETHODCODE],
            SALESORDERPAYMENT.AMOUNT as [@AMOUNT],
            AMOUNTS.OVERAGE as [@REFUNDABLEAMOUNT]
        from
            dbo.SALESORDERPAYMENT
        inner join
            dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
        outer apply
            dbo.UFN_PAYMENT_AMOUNTS(SALESORDERPAYMENT.PAYMENTID) as AMOUNTS
        where
            SALESORDERPAYMENT.SALESORDERID = @RESERVATIONID
        for xml path('ITEM'), type, elements, root('PAYMENTINFORMATION'), binary base64
    );

    return 0;