USP_DATALIST_CREDITREFUND_REFUNDMETHODS

            CREATE procedure dbo.USP_DATALIST_CREDITREFUND_REFUNDMETHODS
            (
                @CREDITID uniqueidentifier
            )
            as
                set nocount on;

                declare @CHECKS table (ID uniqueidentifier, CHECKNUMBER nvarchar(20));
                insert into @CHECKS
                (ID, CHECKNUMBER)
                (
                    select 
                        DETAIL.ID, 
                        DETAIL.CHECKNUMBER 
                    from dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL DETAIL
                    inner join dbo.CREDITPAYMENT on DETAIL.ID = CREDITPAYMENT.ID
                    where CREDITPAYMENT.CREDITID = @CREDITID
                );

                select 
                    [CREDITPAYMENT].[ID],
                    [CREDITPAYMENT].[PAYMENTMETHOD],
                    [CREDITPAYMENT].[AMOUNT],
                    case [CREDITPAYMENT].[PAYMENTMETHODCODE]
                        when 0 then ''
                        when 2 then 
                            (
                                select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID])  + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER]
                                from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                                inner join dbo.[REVENUEPAYMENTMETHOD]
                                    on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
                                where [REVENUEPAYMENTMETHOD].[REVENUEID] = [CREDITPAYMENT].[REVENUEID]
                            )
                        when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENT].[OTHERPAYMENTMETHODCODEID])
                    end as [PAYMENTTYPE],
                    [CREDITPAYMENT].[REFUNDPROCESSED],
                    case when [CREDITPAYMENT].[REFUNDPROCESSED] = 0 then 'res:x_16' end as [REFUNDERRORIMAGE],
                    [CREDITPAYMENT].[STATUS] as [BBPSSTATUS],
                    [CREDITPAYMENT].[PAYMENTMETHODCODE],
                    BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID,
                    BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER [DEPOSITNUMBER],
                    (select TOP 1 T.POSTDATE from dbo.GLTRANSACTION T inner join dbo.CREDITGLDISTRIBUTION GL on GL.GLTRANSACTIONID = T.ID where GL.CREDITPAYMENTID = CREDITPAYMENT.ID) [POSTDATE],
                    isnull((select TOP 1 T.POSTSTATUS from dbo.GLTRANSACTION T inner join dbo.CREDITGLDISTRIBUTION GL on GL.GLTRANSACTIONID = T.ID where GL.CREDITPAYMENTID = CREDITPAYMENT.ID), 'Do not post') [POSTSTATUS],
                    coalesce(nullif(DETAIL.CHECKNUMBER, ''), 'ed828b87-f4f2-4519-8665-1fa8aa036b3d') as [PARENTCHECKNUMBER],
                    null as [CHECKNUMBER],
                    case when (select count(CHECKS.ID) from @CHECKS CHECKS where CHECKS.CHECKNUMBER = DETAIL.CHECKNUMBER) = 1
                        then 1
                        else 0
                    end as [CHECKEDITABLE],
                    case when (select count(CHECKS.ID) from @CHECKS CHECKS where CHECKS.CHECKNUMBER = DETAIL.CHECKNUMBER) = 1
                        then 1
                        else 0
                    end as [SINGLECHECK]
                from dbo.[CREDITPAYMENT]
                left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on CREDITPAYMENT.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
                left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID
                left join dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL DETAIL on CREDITPAYMENT.ID = DETAIL.ID
                where [CREDITPAYMENT].[CREDITID] = @CREDITID

                union

                -- Adding a parent row for checks only so each refund detail record can be viewed, use a well known guid if the check number is empty

                select
                    @CREDITID,
                    [CREDITPAYMENT].[PAYMENTMETHOD],
                    sum([CREDITPAYMENT].[AMOUNT]),
                    null as [PAYMENTTYPE],
                    null as [REFUNDPROCESSED],
                    null as [REFUNDERRORIMAGE],
                    null as [BBPSSTATUS],
                    1 as [PAYMENTMETHODCODE],
                    null as [DEPOSITID],
                    null as [DEPOSITNUMBER],
                    null as [POSTDATE],
                    null as [POSTSTATUS],
                    null as [PARENTCHECKNUMBER],
                    coalesce(nullif(DETAIL.CHECKNUMBER, ''), 'ed828b87-f4f2-4519-8665-1fa8aa036b3d') as [CHECKNUMBER],
                    1 as [CHECKEDITABLE],
                    0 as [SINGLECHECK]
                from dbo.[CREDITPAYMENT]
                left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on CREDITPAYMENT.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
                left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID
                inner join dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL DETAIL on CREDITPAYMENT.ID = DETAIL.ID
                where [CREDITPAYMENT].[CREDITID] = @CREDITID
                    and (select count(CHECKS.ID) from @CHECKS CHECKS where CHECKS.CHECKNUMBER = DETAIL.CHECKNUMBER) > 1
                group by [CREDITPAYMENT].[PAYMENTMETHOD], DETAIL.CHECKNUMBER