USP_SEARCHLIST_PAYMENT

                CREATE procedure dbo.USP_SEARCHLIST_PAYMENT
                (
                    @PAYMENTSOURCE nvarchar(255) = null,
                    @PAYMENTDATE datetime = null,
                    --@PAYMENTMETHODCODE tinyint = null,

                    @PAYMENTMETHOD tinyint = null,
                    @DEPOSITNUMBER nvarchar(64) = null,
                    @AMOUNT decimal(20, 4) = null,
                    @POSTSTATUSCODE tinyint = null,
                    @POSTDATE datetime = null,
                    @MAXROWS smallint = 500,
                    @EXCLUDELINKEDPAYMENTS bit = 0,
                    @APPLICATIONCODE tinyint = null,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @PDACCOUNTSYSTEMID uniqueidentifier = null,
                    @NUMSYSTEMS tinyint = null,
                    @PAYMENTENDDATE datetime = null
                )
                as
                    set nocount on;

                    declare @NUMBER integer
                    set @NUMBER = CAST(@DEPOSITNUMBER AS integer);
                    set @NUMSYSTEMS = dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID);
                    declare @PAYMENTSTARTDATEOFFSET datetimeoffset = cast(@PAYMENTDATE as datetimeoffset)
                    declare @PAYMENTENDDATEOFFSET datetimeoffset = cast(@PAYMENTENDDATE as datetimeoffset)

                    select distinct top(@MAXROWS)
                        REVENUE.ID,
                        cast(REVENUE.DATE as datetime) as DATE,
                        REVENUE_EXT.REFERENCE AS [PAYMENTSOURCE],
                        REVENUE.TRANSACTIONAMOUNT [AMOUNT],
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD AS [PAYMENTMETHOD],
                        case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 then [CASH].REFERENCENUMBER
                                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 then [CHECK].CHECKNUMBER
                                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then [CREDIT].CREDITCARDPARTIALNUMBER
                                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then [OTHER].REFERENCENUMBER
                            end AS [REFERENCE],
                        (select TRANSACTIONNUMBER from dbo.BANKACCOUNTTRANSACTION where ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID),
                        (select dbo.UFN_BANKACCOUNT_GETACCOUNTNAME(BANKACCOUNTID) from BANKACCOUNTTRANSACTION where ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID),
                        REVENUESPLIT_EXT.APPLICATION,
                        TRANSACTIONCURRENCY.ID as TRANSACTIONCURRENCYID,
                        Cast(REVENUE.TRANSACTIONAMOUNT as nvarchar(20)) + ' ' + 
                                    REVENUE.TYPE as DESCRIPTION
                    from 
                        dbo.FINANCIALTRANSACTION as REVENUE
                        inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                        cross apply(select case when REVENUE.DELETEDON is null then REVENUE.TRANSACTIONCURRENCYID else null end as ID) TRANSACTIONCURRENCY
                        left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                        left outer join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
                        left outer join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
                        left outer join dbo.CASHPAYMENTMETHODDETAIL as [CASH] on [CASH].ID = REVENUEPAYMENTMETHOD.ID
                        left outer join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID 
                        left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CREDIT] on [CREDIT].ID = REVENUEPAYMENTMETHOD.ID
                        left outer join dbo.OTHERPAYMENTMETHODDETAIL as [OTHER] on [OTHER].ID = REVENUEPAYMENTMETHOD.ID
                        left join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID 
                        left join dbo.RECONCILIATION on SALESORDERPAYMENT.RECONCILIATIONID = RECONCILIATION.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID
                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.PDACCOUNTSYSTEMFORREVENUE on REVENUE.ID = PDACCOUNTSYSTEMFORREVENUE.ID
                        inner join dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) PDACCOUNTSYSTEMIDSFORUSER on PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMIDSFORUSER.ID
                        inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = PDACCOUNTSYSTEMIDSFORUSER.ID
                    where
                         (@PAYMENTSOURCE is null or (REVENUE_EXT.REFERENCE like @PAYMENTSOURCE + '%')) and
                         ((@PAYMENTSTARTDATEOFFSET is null or (REVENUE.DATE >= @PAYMENTSTARTDATEOFFSET)) and (@PAYMENTENDDATEOFFSET is null or (REVENUE.DATE <= @PAYMENTENDDATEOFFSET))) and
                         (@PAYMENTMETHOD is null or (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = @PAYMENTMETHOD)) and
                         (@AMOUNT is null or (REVENUE.TRANSACTIONAMOUNT = @AMOUNT)) and
                         (@NUMBER is null or (BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER = @NUMBER and BANKACCOUNTTRANSACTION.TRANSACTIONFLAGCODE = 1)) and
                         (@POSTSTATUSCODE is null or (@POSTSTATUSCODE = case when REVENUE.POSTSTATUSCODE = 2 then 2 else 1 end)) and
                         (@POSTDATE is null or (REVENUE.POSTDATE = @POSTDATE)) and
                         (@EXCLUDELINKEDPAYMENTS = 0 or (BANKACCOUNTDEPOSITPAYMENT.DEPOSITID is null)) and
                         (REVENUE.TYPECODE = 0 and (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE IN (0,1,2,9,10))) and
                         (@APPLICATIONCODE is null or REVENUESPLIT_EXT.APPLICATIONCODE = @APPLICATIONCODE) and
                         (SALESORDERPAYMENT.ID is null or RECONCILIATION.STATUSCODE = 3) and 
                         (@PDACCOUNTSYSTEMID is null or (PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID)) 
                    order by 
                        cast(REVENUE.DATE as datetime) desc