USP_DATALIST_SYSTEMCREDITCARDTRANSACTION

            CREATE procedure dbo.USP_DATALIST_SYSTEMCREDITCARDTRANSACTION
                (
                    @FROMDATE datetime,
                    @TODATE datetime,
                    @INCLUDESETTLEDTRANSACTIONS bit = 0,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @VENDORID nvarchar(50) = null,
                    @INCLUDEDTRANSACTIONS tinyint = 0
                )
                as
                    set nocount on;

                    if @VENDORID = '0'
                        set @VENDORID = '';
                    else if @VENDORID = ''
                        set @VENDORID = null;

                    if @INCLUDEDTRANSACTIONS = 0
                        set @INCLUDESETTLEDTRANSACTIONS = 0
                    else
                        set @INCLUDESETTLEDTRANSACTIONS = 1;

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

                    declare @BASICPROGRAMSINSTALLED bit = 0;
                    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1
                        set @BASICPROGRAMSINSTALLED = 1;

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

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

                    select
                        [CREDITCARDPAYMENTMETHODDETAIL].[ID],
                        null as [TRANSACTIONDATEGROUP],
                        convert(date, [FINANCIALTRANSACTION].[DATE]) as [TRANSACTIONDATE],
                        [CREDITTYPECODE].[DESCRIPTION] as [CARDTYPE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[CARDHOLDERNAME],
                        [CREDITCARDPAYMENTMETHODDETAIL].[AUTHORIZATIONCODE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
                        case @BASICPROGRAMSINSTALLED
                            when 0 then [FINANCIALTRANSACTION].[TRANSACTIONAMOUNT] + coalesce((select sum([CREDITPAYMENT].[AMOUNT]) from dbo.[CREDITPAYMENT] where [CREDITPAYMENT].[REVENUEID] = [FINANCIALTRANSACTION].[ID]), 0)
                            else [FINANCIALTRANSACTION].[TRANSACTIONAMOUNT]
                        end as [AMOUNT],
                        case [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTTYPECODE]
                            when 0 then 0
                            else 1
                        end,
                        [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTTYPECODE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTDATE],
                        0 as [ISPARENT],
                        cast(convert(date, [FINANCIALTRANSACTION].[DATE]) as nvarchar(100)) + cast([FINANCIALTRANSACTION].TRANSACTIONCURRENCYID as nvarchar(100)) as [GROUPID],
                        [FINANCIALTRANSACTION].TRANSACTIONCURRENCYID CURRENCYID,
                        [CREDITCARDPAYMENTMETHODDETAIL].VENDORID,
                        null [GROUP],
                        case [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTTYPECODE]
                            when 0 then 0
                            else 1
                        end as [STATUSCODE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID]
                    from dbo.[CREDITCARDPAYMENTMETHODDETAIL]
                    inner join dbo.[REVENUEPAYMENTMETHOD] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
                    inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
                    left join dbo.[CREDITTYPECODE] on [CREDITTYPECODE].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]
                    where convert(date, [FINANCIALTRANSACTION].[DATE]) between @FROMDATE and @TODATE
                    and (
                             @INCLUDEDTRANSACTIONS = 2
                             or (@INCLUDEDTRANSACTIONS = 0 and [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTTYPECODE] = 0)
                             or (@INCLUDEDTRANSACTIONS = 1 and [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTTYPECODE] <> 0)
                        )
                        and (@VENDORID is null or CREDITCARDPAYMENTMETHODDETAIL.VENDORID = @VENDORID)
                    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

                    --refunds

                    select
                        [CREDITPAYMENT].[ID],
                        null as [TRANSACTIONDATEGROUP],
                        convert(date, [CREDITPAYMENT].[DATEADDED]) as [TRANSACTIONDATE],
                        [CREDITTYPECODE].[DESCRIPTION] as [CARDTYPE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[CARDHOLDERNAME],
                        [CREDITCARDPAYMENTMETHODDETAIL].[AUTHORIZATIONCODE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
                        -[CREDITPAYMENT].[AMOUNT],
                        case [CREDITPAYMENT].[SETTLEMENTTYPECODE]
                            when 0 then 0
                            else 1
                        end,
                        [CREDITPAYMENT].[SETTLEMENTTYPECODE],
                        [CREDITPAYMENT].[SETTLEMENTDATE],
                        0 as [ISPARENT],
                        cast(convert(date, [CREDITPAYMENT].[DATEADDED]) as nvarchar(100)) + cast(@ORGCURRENCYID as nvarchar(100)) as [GROUPID],
                        case [CRM_REFUND].[ID]
                            when null then @ORGCURRENCYID
                            else [FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID]
                        end as [CURRENCYID],
                        [CREDITCARDPAYMENTMETHODDETAIL].VENDORID,
                        null [GROUP],
                        case [CREDITPAYMENT].[SETTLEMENTTYPECODE]
                            when 0 then 0
                            else 1
                        end as [STATUSCODE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID]
                    from dbo.[CREDITPAYMENT]
                    left join dbo.[FINANCIALTRANSACTIONLINEITEM] on [CREDITPAYMENT].[REVENUESPLITID] = [FINANCIALTRANSACTIONLINEITEM].[ID]
                    left join dbo.[REVENUEPAYMENTMETHOD] as [CRM_REFUND] on ([CRM_REFUND].[REVENUEID] = [CREDITPAYMENT].[CREDITID])
                    left join dbo.[REVENUEPAYMENTMETHOD] as [ALTRU_REFUND] on ([ALTRU_REFUND].[REVENUEID] = [CREDITPAYMENT].[REVENUEID] or [ALTRU_REFUND].[REVENUEID] = [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID])
                    inner join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on ([CREDITCARDPAYMENTMETHODDETAIL].[ID] = [CRM_REFUND].[ID] or ([CRM_REFUND].[ID] is null and [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [ALTRU_REFUND].[ID]))
                    left join dbo.[CREDITTYPECODE] on [CREDITTYPECODE].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]
                    left join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [CRM_REFUND].[REVENUEID]
                    where convert(date, [CREDITPAYMENT].[DATEADDED]) between @FROMDATE and @TODATE
                    and (
                             @INCLUDEDTRANSACTIONS = 2
                             or (@INCLUDEDTRANSACTIONS = 0 and [CREDITPAYMENT].[SETTLEMENTTYPECODE] = 0)
                             or (@INCLUDEDTRANSACTIONS = 1 and [CREDITPAYMENT].[SETTLEMENTTYPECODE] <> 0)
                         )
                        and (@VENDORID is null or CREDITCARDPAYMENTMETHODDETAIL.VENDORID = @VENDORID)
                        and [CREDITPAYMENT].[PAYMENTMETHODCODE] = 2