USP_DATALIST_RECENTREFUNDS

CREATE procedure dbo.USP_DATALIST_RECENTREFUNDS
(
    @REFUNDMETHODTYPECODE tinyint,
    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @INCLUDESENTCHECKS bit = null
)
as
    set nocount on;

    select top(4)
        FT.ID,
        cast(FT.DATE as datetime),
        NF.NAME as CONSTITUENT,
        FT.TRANSACTIONAMOUNT,
        dbo.UFN_CREDIT_GETPAYMENTMETHODLIST(FT.ID) as REFUNDMETHOD,
        case
            when exists (
                select CREDITPAYMENT.ID
                from dbo.CREDITPAYMENT
                where CREDITPAYMENT.CREDITID = FT.ID
                    and CREDITPAYMENT.PAYMENTMETHODCODE = 1
            ) then 1
            else 0            
        end as HASCHECK,
        case
            when exists (
                select CREDITPAYMENT.ID
                from dbo.CREDITPAYMENT
                inner join dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL
                    on CREDITPAYMENT.ID = CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.ID
                where CREDITPAYMENT.CREDITID = FT.ID
                    and CREDITPAYMENT.PAYMENTMETHODCODE = 1
                    and CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.CHECKDATE <> '00000000'
            ) then 1
            else 0            
        end as CHECKSENT
    from
        dbo.FINANCIALTRANSACTION as FT
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as NF
    where
        FT.TYPECODE = 23  -- Refund

        and (@STARTDATE is null or FT.CALCULATEDDATE >= @STARTDATE)
        and (@ENDDATE is null or FT.CALCULATEDDATE <= @ENDDATE)
        and (
            @REFUNDMETHODTYPECODE = 0
            or exists(
                select ID
                from dbo.CREDITPAYMENT
                where CREDITPAYMENT.CREDITID = FT.ID
                    and CREDITPAYMENT.PAYMENTMETHODCODE = @REFUNDMETHODTYPECODE - 1
                    and (
                        (CREDITPAYMENT.OTHERPAYMENTMETHODCODEID is null and @OTHERPAYMENTMETHODCODEID is null)
                        or CREDITPAYMENT.OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID
                    )
            )
        )
        and (
                @INCLUDESENTCHECKS = 1
                or not exists (
                        select CREDITPAYMENT.ID
                        from dbo.CREDITPAYMENT
                        inner join dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL
                            on CREDITPAYMENT.ID = CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.ID
                        where CREDITPAYMENT.CREDITID = FT.ID
                            and CREDITPAYMENT.PAYMENTMETHODCODE = 1
                            and CREDITPAYMENTCHECKPAYMENTMETHODDETAIL.CHECKDATE <> '00000000'
                    )
            )
    order by
        FT.DATE desc
    option (recompile);

    return 0;