USP_DATALIST_SALESORDERPAYMENTREFUNDS

CREATE procedure dbo.USP_DATALIST_SALESORDERPAYMENTREFUNDS(
    @SALESORDERID uniqueidentifier
)
as
    set nocount on;

    select
        CREDITPAYMENT.CREDITID as ID,
        convert(date, CREDITPAYMENT.CREDITPAYMENTDATEWITHTIMEOFFSET) as DATE,
        case CREDITPAYMENT.PAYMENTMETHODCODE
            when 10 then -- 'Other' refunds need information from a different table

                OTHERPAYMENTMETHODCODE.DESCRIPTION
            else
                CREDITPAYMENT.PAYMENTMETHOD
        end as REFUNDMETHOD,
        CREDITPAYMENT.AMOUNT as AMOUNT,
        CREDITREASONCODE.DESCRIPTION as REASON,
        'Reservation payment' as DETAILS
    from 
        dbo.CREDITPAYMENT
    inner join
        dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = CREDITPAYMENT.REVENUEID
    left join
        dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = CREDITPAYMENT.OTHERPAYMENTMETHODCODEID
    inner join
        dbo.CREDIT_EXT on CREDIT_EXT.ID = CREDITPAYMENT.CREDITID
    left join
        dbo.CREDITREASONCODE on CREDITREASONCODE.ID = CREDIT_EXT.CREDITREASONCODEID
    where 
        SALESORDERPAYMENT.SALESORDERID = @SALESORDERID

    Union all

    select         CREDITPAYMENT.CREDITID as ID,
        convert(date, CREDITPAYMENT.CREDITPAYMENTDATEWITHTIMEOFFSET) as DATE,
        case CREDITPAYMENT.PAYMENTMETHODCODE
            when 10 then -- 'Other' refunds need information from a different table

                OTHERPAYMENTMETHODCODE.DESCRIPTION
            else
                CREDITPAYMENT.PAYMENTMETHOD
        end as REFUNDMETHOD,
        CREDITPAYMENT.AMOUNT as AMOUNT,
        CREDITREASONCODE.DESCRIPTION as REASON ,
        'Security deposit' as DETAILS
        from 
    dbo.CREDITPAYMENT 
    inner join 
        dbo.FINANCIALTRANSACTION FT on FT.ID = CREDITPAYMENT.REVENUEID
        left join
        dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = CREDITPAYMENT.OTHERPAYMENTMETHODCODEID
    inner join
        dbo.CREDIT_EXT on CREDIT_EXT.ID = CREDITPAYMENT.CREDITID
        inner join 
        dbo.CREDITITEM_EXT EXT on EXT.CREDITID = CREDIT_EXT.ID
    left join
        dbo.CREDITREASONCODE on CREDITREASONCODE.ID = CREDIT_EXT.CREDITREASONCODEID
    where 
        CREDIT_EXT.SALESORDERID = @SALESORDERID and EXT.TYPECODE = 12    --Reservation Security Deposit


    return 0;