USP_REPORT_CREDITCARDTRANSACTIONSETTLEMENTSTATUS

                create procedure dbo.USP_REPORT_CREDITCARDTRANSACTIONSETTLEMENTSTATUS
                (
                    @FROMDATE datetime = null, 
                    @TODATE datetime = null,
                    @TRANSACTIONDATETYPE integer = null,
                    @ALTREPORTUSERID nvarchar(128) = null,
                    @REPORTUSERID nvarchar(128) = null
                )
                as
                        declare
                            @CURRENCYISOCURRENCYCODE nvarchar(3) = null,
                            @CURRENCYDECIMALDIGITS tinyint = 0, 
                            @CURRENCYSYMBOL nvarchar(5) = null,
                            @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,
                            @CURRENCYROUNDINGTYPECODE tinyint = 0;

                    declare @CURRENTAPPUSERID uniqueidentifier = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

                    declare
                        @APPUSERISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID),
                        @FEATUREID uniqueidentifier = '9e1afa09-20da-4bd1-8d42-5668f24c8c19';

                    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    select
                        @CURRENCYISOCURRENCYCODE = CURRENCY.ISO4217,
                        @CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                        @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
                        @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE,
                        @CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                    from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;


                    select
                        convert(date, [FINANCIALTRANSACTION].[DATE]) as [TRANSACTIONDATE],
                        [CREDITTYPECODE].[DESCRIPTION] as [CARDTYPE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[CARDHOLDERNAME],
                        [CREDITCARDPAYMENTMETHODDETAIL].[AUTHORIZATIONCODE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
                        [FINANCIALTRANSACTION].[ORGAMOUNT] as [AMOUNT],
                        [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTDATE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTTYPECODE],
                        @CURRENCYISOCURRENCYCODE as [CURRENCYISO],
                        @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
                        @CURRENCYSYMBOL as [CURRENCYSYMBOL],
                        @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                        @CURRENCYROUNDINGTYPECODE
                    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 ((@TRANSACTIONDATETYPE = 1 and convert(date, [FINANCIALTRANSACTION].[DATE]) between @FROMDATE and @TODATE)
                            or (@TRANSACTIONDATETYPE = 2 and [CREDITCARDPAYMENTMETHODDETAIL].[SETTLEMENTDATE] between @FROMDATE and @TODATE))
                            and (
                                    ( --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 dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '9e1afa09-20da-4bd1-8d42-5668f24c8c19', SITE.SITEID)
                                            where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)
                                        )
                                    )
                            ) > 0)

                    union all

                    --refunds
                    select
                        convert(date, [CREDITPAYMENT].[DATEADDED]) as [TRANSACTIONDATE],
                        [CREDITTYPECODE].[DESCRIPTION] as [CARDTYPE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[CARDHOLDERNAME],
                        [CREDITCARDPAYMENTMETHODDETAIL].[AUTHORIZATIONCODE],
                        [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
                        -[CREDITPAYMENT].[AMOUNT],
                        [CREDITPAYMENT].[SETTLEMENTDATE],
                        [CREDITPAYMENT].[SETTLEMENTTYPECODE],
                        @CURRENCYISOCURRENCYCODE as [CURRENCYISO],
                        @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
                        @CURRENCYSYMBOL as [CURRENCYSYMBOL],
                        @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                        @CURRENCYROUNDINGTYPECODE
                    from dbo.[CREDITPAYMENT]
                    left join dbo.[FINANCIALTRANSACTION] on [CREDITPAYMENT].[REVENUEID] = [FINANCIALTRANSACTION].[ID]
                    left join dbo.[FINANCIALTRANSACTIONLINEITEM] on [CREDITPAYMENT].[REVENUESPLITID] = [FINANCIALTRANSACTIONLINEITEM].[ID]
                    inner join dbo.[REVENUEPAYMENTMETHOD] on ([REVENUEPAYMENTMETHOD].[REVENUEID] = [FINANCIALTRANSACTION].[ID] or [REVENUEPAYMENTMETHOD].[REVENUEID] = [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID])
                    inner join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
                    left join dbo.[CREDITTYPECODE] on [CREDITTYPECODE].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]
                    where ((@TRANSACTIONDATETYPE = 1 and convert(date, [CREDITPAYMENT].[DATEADDED]) between @FROMDATE and @TODATE)
                            or (@TRANSACTIONDATETYPE = 2 and [CREDITPAYMENT].[SETTLEMENTDATE] between @FROMDATE and @TODATE))
                            and [CREDITPAYMENT].[PAYMENTMETHODCODE] = 2
                    order by [TRANSACTIONDATE]

                return 0;