USP_SEARCHLIST_MISCELLANEOUSPAYMENT

Search for a miscellaneous payment.

Parameters

Parameter	Parameter Type	Mode	Description
@PAYMENTSOURCE	nvarchar(255)	IN	Reference
@PAYMENTDATE	datetime	IN	Payment date from
@PAYMENTMETHOD	tinyint	IN	Payment method
@DEPOSITNUMBER	nvarchar(64)	IN	Deposit number
@AMOUNT	decimal(20, 4)	IN	Amount
@POSTSTATUSCODE	tinyint	IN	Post status
@POSTDATE	datetime	IN	Post date
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@EXCLUDELINKEDPAYMENTS	bit	IN	Exclude linked payments
@APPLICATIONCODE	tinyint	IN	Application
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@PDACCOUNTSYSTEMID	uniqueidentifier	IN	Account system
@NUMSYSTEMS	tinyint	IN	Number of account systems
@PAYMENTENDDATE	datetime	IN	To

Definition

Copy



                CREATE procedure dbo.USP_SEARCHLIST_MISCELLANEOUSPAYMENT
                (
                    @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;
          if not @DEPOSITNUMBER is null
            set @NUMBER = CAST(@DEPOSITNUMBER AS integer);

                    set @PAYMENTSOURCE = REPLACE(REPLACE(COALESCE(@PAYMENTSOURCE,''), '*', '%'), '?', '_') + '%'
                    set @NUMSYSTEMS = dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID);
                    select top(@MAXROWS)
                        REVENUE.ID,
                        REVENUE.DATE,
                        dbo.REVENUEREFERENCE.REFERENCE as [PAYMENTSOURCE],
                        REVENUE.TRANSACTIONAMOUNT,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                        (select TRANSACTIONNUMBER from dbo.BANKACCOUNTTRANSACTION where ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID),
                        (select dbo.UFN_BANKACCOUNT_GETACCOUNTNAME(BANKACCOUNTID) from BANKACCOUNTTRANSACTION where ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID),
                        PDACCOUNTSYSTEM.NAME
            ,REVENUE.TRANSACTIONCURRENCYID
                    from 
                        dbo.REVENUE
                        inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID and REVENUESPLIT.APPLICATIONCODE=11 and 
                        REVENUESPLIT.TYPECODE=8
                        inner join dbo.REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID
                        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                        left join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
                        left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
                        left join dbo.REVENUESPLIT R on R.REVENUEID = REVENUE.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
                        left outer join dbo.REVENUEPOSTED RP on RP.ID = REVENUE.ID
                    where
                         ((@PAYMENTSOURCE is null or (REVENUEREFERENCE.REFERENCE like @PAYMENTSOURCE + '%')) and
                         ((@PAYMENTDATE is null or (REVENUE.DATE >= @PAYMENTDATE)) and (@PAYMENTENDDATE is null or (REVENUE.DATE <= @PAYMENTENDDATE))) and
                         (@PAYMENTMETHOD is null or (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = @PAYMENTMETHOD)) and
                         (@NUMBER is null or (BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER = @NUMBER and BANKACCOUNTTRANSACTION.TRANSACTIONFLAGCODE = 1)) and
                         (@AMOUNT is null or (REVENUE.TRANSACTIONAMOUNT = @AMOUNT)) and
                         (@POSTSTATUSCODE is null or (REVENUE.DONOTPOST = (case when @POSTSTATUSCODE = 2 then 1 else 0 end) and @POSTSTATUSCODE != 0 and RP.ID is null) or (@POSTSTATUSCODE = 0 and RP.ID is not null)) and
                         (@POSTDATE is null or (REVENUE.POSTDATE = @POSTDATE)) and
                         (@EXCLUDELINKEDPAYMENTS = 0 or (BANKACCOUNTDEPOSITPAYMENT.DEPOSITID is null)) and
                         (@APPLICATIONCODE is null or R.APPLICATIONCODE = @APPLICATIONCODE))
                         and
                         (@PDACCOUNTSYSTEMID is null or (PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID)) 
                    order by 
                        REVENUE.DATE desc