USP_SEARCHLIST_UNSETTLEDCREDITCARDTRANSACTION

                CREATE procedure dbo.USP_SEARCHLIST_UNSETTLEDCREDITCARDTRANSACTION
                (
                    @MAXROWS smallint = 500,
                    @CARDHOLDERNAME nvarchar(100) = null,
                    @ORGANIZATIONAMOUNT money = null,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null,
                    @CREDITTYPECODEID uniqueidentifier = null,
                    @AUTHORIZATIONCODE nvarchar(20) = null,
                    @LASTFOURDIGITS nvarchar(10) = null,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @TRANSACTIONAMOUNT decimal(19,4) = null
                )
                as

                    declare
                        @APPUSERISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID),
                        @FEATUREID uniqueidentifier = '9151decb-5734-4fd9-8884-7d18aba1e2ed';

                    declare @SITESFORUSERONFEATURE table (SITEID uniqueidentifier);
                    insert into @SITESFORUSERONFEATURE
                    select * from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@FEATUREID,2);

                    set @CARDHOLDERNAME = replace(replace(@CARDHOLDERNAME,'[','\['),']','\]');
                    set @CARDHOLDERNAME = replace(replace(@CARDHOLDERNAME,'*','%'),'?','_');
                    set @CARDHOLDERNAME = replace(@CARDHOLDERNAME,' ','%');
                    set @CARDHOLDERNAME = '%' + coalesce(@CARDHOLDERNAME,'') + '%' ;

                    set @LASTFOURDIGITS = replace(replace(@LASTFOURDIGITS,'[','\['),']','\]');
                    set @LASTFOURDIGITS = replace(replace(@LASTFOURDIGITS,'*','%'),'?','_');
                    set @LASTFOURDIGITS = '%' + coalesce(@LASTFOURDIGITS,'%') + '%';

                    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
                    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                    declare @ORGCURRENCYID uniqueidentifier = (select ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1);

                    with TRANSACTIONS_CTE as 
                    (

                        select
                            [CREDITCARDPAYMENTMETHODDETAIL].[ID],
                            [CREDITCARDPAYMENTMETHODDETAIL].[CARDHOLDERNAME] as [NAME],
                            [FINANCIALTRANSACTION].[TRANSACTIONAMOUNT] as [AMOUNT],
                            [FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID] [CURRENCYID],
                            cast([FINANCIALTRANSACTION].[DATE] as datetime) as [DATE],
                            [CREDITTYPECODE].[DESCRIPTION] as [CREDITCARDTYPE],
                            [CREDITCARDPAYMENTMETHODDETAIL].[AUTHORIZATIONCODE],
                            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER]
                        from dbo.[CREDITCARDPAYMENTMETHODDETAIL]
                        inner join dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[ID]
                        inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
                        inner join dbo.[CREDITTYPECODE] on [CREDITTYPECODE].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]
                        where
                            [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTTYPECODE] = 0
                            and [CREDITCARDPAYMENTMETHODDETAIL].[CARDHOLDERNAME] like @CARDHOLDERNAME
                            and (@TRANSACTIONAMOUNT is null or @TRANSACTIONAMOUNT = [FINANCIALTRANSACTION].[TRANSACTIONAMOUNT])
                            and ((@STARTDATE is null and @ENDDATE is null) or
                                (@STARTDATE is null and [FINANCIALTRANSACTION].[DATE] <= @ENDDATE) or
                                (@ENDDATE is null and [FINANCIALTRANSACTION].[DATE] >= @STARTDATE) or
                                ([FINANCIALTRANSACTION].[DATE] between @STARTDATE and @ENDDATE))
                            and (@CREDITTYPECODEID is null or [CREDITTYPECODE].[ID] = @CREDITTYPECODEID)
                            and (@AUTHORIZATIONCODE is null or [CREDITCARDPAYMENTMETHODDETAIL].[AUTHORIZATIONCODE] = @AUTHORIZATIONCODE)
                            and ([CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER] like @LASTFOURDIGITS)
                            and (
                                    ( --if there's no revenue

                                    select
                                        count(ID)
                                    from dbo.FINANCIALTRANSACTIONLINEITEM
                                    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                    ) = 0
                                or
                                    (--Check site security  

                                    select 
                                        count(*)   
                                    from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) as SITE  
                                    where (
                                        @APPUSERISSYSADMIN = 1 or 
                                        exists (
                                            select 1 
                                            from @SITESFORUSERONFEATURE
                                            where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)
                                        )
                                    ) 
                                ) > 0)

                        union all

                        select distinct
                            [CREDITPAYMENT].[ID],
                            [CREDITCARDPAYMENTMETHODDETAIL].[CARDHOLDERNAME] as [NAME],
                            -[CREDITPAYMENT].[AMOUNT],
                            [FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID] as [CURRENCYID],
                            [CREDITPAYMENT].[DATEADDED] as [DATE],
                            [CREDITTYPECODE].[DESCRIPTION] as [CREDITCARDTYPE],
                            [CREDITCARDPAYMENTMETHODDETAIL].[AUTHORIZATIONCODE],
                            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER]
                        from dbo.[CREDITPAYMENT]
                        left join dbo.[FINANCIALTRANSACTIONLINEITEM] [ORIGINALPAYMENTFTMLINEITEM] on [CREDITPAYMENT].[REVENUESPLITID] = [ORIGINALPAYMENTFTMLINEITEM].[ID]
                        inner join dbo.REVENUEPAYMENTMETHOD on ([REVENUEPAYMENTMETHOD].[REVENUEID] = [CREDITPAYMENT].[REVENUEID] or [REVENUEPAYMENTMETHOD].[REVENUEID] = [ORIGINALPAYMENTFTMLINEITEM].[FINANCIALTRANSACTIONID])
                        inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
                        inner join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
                        inner join dbo.[CREDITTYPECODE] on [CREDITTYPECODE].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]
                        where
                            [CREDITPAYMENT].[SETTLEMENTTYPECODE] = 0
                            and [CREDITCARDPAYMENTMETHODDETAIL].[CARDHOLDERNAME] like @CARDHOLDERNAME
                            and (@TRANSACTIONAMOUNT is null or @TRANSACTIONAMOUNT = -[CREDITPAYMENT].[AMOUNT])
                            and ((@STARTDATE is null and @ENDDATE is null) or
                                (@STARTDATE is null and [CREDITPAYMENT].[DATEADDED] <= @ENDDATE) or
                                (@ENDDATE is null and [CREDITPAYMENT].[DATEADDED] >= @STARTDATE) or
                                ([CREDITPAYMENT].[DATEADDED] between @STARTDATE and @ENDDATE))
                            and (@CREDITTYPECODEID is null or [CREDITTYPECODE].[ID] = @CREDITTYPECODEID)
                            and (@AUTHORIZATIONCODE is null or [CREDITCARDPAYMENTMETHODDETAIL].[AUTHORIZATIONCODE] = @AUTHORIZATIONCODE)
                            and ([CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER] like @LASTFOURDIGITS)
                    )
                    select top(@MAXROWS)
                        [ID],
                        [NAME],
                        [AMOUNT],
                        [CURRENCYID],
                        [DATE],
                        [CREDITCARDTYPE],
                        [AUTHORIZATIONCODE],
                        [CREDITCARDPARTIALNUMBER]
                    from TRANSACTIONS_CTE
                    order by [DATE] desc