USP_DATALIST_SALESRECONCILIATIONREPORT_REFUNDDETAILS

CREATE procedure dbo.USP_DATALIST_SALESRECONCILIATIONREPORT_REFUNDDETAILS
(
    @RECONCILIATIONID uniqueidentifier = null
)
as
    set nocount on;

    declare @CREDITS table (
        [CREDITNUMBER] integer,
        [ID] uniqueidentifier,
        [TRANSACTIONDATE] datetime,
        [SALESORDERID] uniqueidentifier
    )

    insert into @CREDITS
    select
        row_number() over(order by FT.DATE),
        FT.ID as [CREDITID],
        cast(FT.DATE as datetime),
        isnull(SALESORDER.ID, EXT.SALESORDERID)
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    left outer join
        dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
    where exists(
            select *
            from dbo.[CREDITPAYMENT]
            where
                [CREDITPAYMENT].[RECONCILIATIONID] = @RECONCILIATIONID and
                [CREDITPAYMENT].[CREDITID] = FT.ID
        )

    select
        [CREDIT].[CREDITNUMBER],
        [CREDIT].[ID] as [CREDITID],
        [CREDITPAYMENT].[PAYMENTMETHODCODE],
        [CREDITPAYMENT].[PAYMENTMETHOD],
        convert(nvarchar(20), SALESORDER.SEQUENCEID) as [SALESORDERNUMBER],
        'http://' + CONVERT(nvarchar(36),SALESORDER.ID) as [SALESORDERLINK],
        [CREDIT].[TRANSACTIONDATE],
        [CREDITPAYMENT].[AMOUNT] as [AMOUNT],
        case [CREDITPAYMENT].[PAYMENTMETHODCODE]
            when 2 then (
                select top 1
                    dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDDETAILS.CREDITTYPECODEID)  + ' #' + CREDITCARDDETAILS.CREDITCARDPARTIALNUMBER
                from (
                    select
                        CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID,
                        CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
                    from dbo.CREDITCARDPAYMENTMETHODDETAIL
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
                    where REVENUEPAYMENTMETHOD.REVENUEID = CREDITPAYMENT.REVENUEID

                    union all
                    select
                        CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID,
                        CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
                    from dbo.FINANCIALTRANSACTIONLINEITEM as LI
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = LI.FINANCIALTRANSACTIONID
                    inner join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                    where LI.ID = CREDITPAYMENT.REVENUESPLITID
                ) as CREDITCARDDETAILS
            )
            when 10 then
                dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID])
        end as [PAYMENTDETAILS],
        NF.NAME as [PATRONNAME],
        'http://www.blackbaud.com/ORDERID?ORDERID=' + CONVERT(nvarchar(36),SALESORDER.ID) as [SALESORDERREPORTLINK]
    from
        @CREDITS as [CREDIT]
    inner join
        dbo.[CREDITPAYMENT] on [CREDIT].[ID] = [CREDITPAYMENT].[CREDITID]
    left join
        dbo.[SALESORDER] on [CREDIT].[SALESORDERID] = [SALESORDER].[ID]
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) as NF
    where
        [CREDITPAYMENT].[RECONCILIATIONID] = @RECONCILIATIONID
    order by 
        [CREDIT].[CREDITNUMBER] asc,
        [CREDITPAYMENT].[PAYMENTMETHOD] asc,
        [CREDITPAYMENT].[AMOUNT] asc;

    return 0;