USP_REPORT_TAXCLAIMBREAKDOWN_DETAIL

            CREATE procedure dbo.USP_REPORT_TAXCLAIMBREAKDOWN_DETAIL
            (
                @CONSTITUENTID uniqueidentifier,
                @SELECTIONID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @REPORTUSERID nvarchar(128) = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED nvarchar(max) = null,
                @CURRENCYCODE smallint = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            as
                set nocount on

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

                declare @ISADMIN bit
                declare @APPUSER_IN_NONRACROLE bit
                declare @APPUSER_IN_NOSECGROUPROLE bit

                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)

                if @ISADMIN = 0
                    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID)

                if @ISADMIN = 0 and @APPUSER_IN_NOSECGROUPROLE = 0
                    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID)

                declare @STARTDATEEARLIESTTIME datetime
                if @STARTDATE is not null
                    set @STARTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)

                declare @ENDDATELATESTTIME datetime
                if @ENDDATE is not null
                    set @ENDDATELATESTTIME = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)

                declare @SITESSELECTEDXML xml
                set @SITESSELECTEDXML = dbo.UFN_REPORT_TAXCLAIMBREAKDOWN_GETSITESSELECTEDXML(@SITESSELECTED)


                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                if coalesce(@CURRENCYCODE, 1) = 1
                begin
                    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                end;

                with TAXCLAIM_CTE(
                    DATE,
                    APPLICATION,
                    RATE,
                    CURRENCYID,
                    SPLITAMOUNT,
                    TAXCLAIMAMOUNT,
                    BASETAXCLAIMAMOUNT,
                    TRANSITIONALTAXCLAIMAMOUNT,
                    TAXCLAIMNUMBER,
                    REFERENCE
                ) as (        

                    select
                        cast(FINANCIALTRANSACTION.DATE as datetime) as DATE,

                        case 
                            when FINANCIALTRANSACTION.TYPECODE <> 0 then FINANCIALTRANSACTION.TYPE
                            else
                                case
                                    when REVENUESPLIT_EXT.APPLICATIONCODE = 0 then REVENUESPLIT_EXT.APPLICATION
                                    else REVENUESPLIT_EXT.APPLICATION + ' payment'
                                end
                        end APPLICATION,

                        case
                            when REVENUESPLITGIFTAID.RATE > 0 then REVENUESPLITGIFTAID.RATE
                            when FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT + REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT = 0 then 0
                            else round(100 * (REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT / 
                                             (FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT + REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT)), 0)
                        end as RATE,

                        case @CURRENCYCODE
                            when 0 then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
                            when 2 then FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
                        end as CURRENCYID,

                        case @CURRENCYCODE
                            when 0 then FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
                            when 2 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                            else        FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT
                        end as SPLITAMOUNT,

                        case @CURRENCYCODE
                            when 0 then REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
                            when 2 then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
                            else        REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
                        end as TAXCLAIMAMOUNT,

                        case @CURRENCYCODE
                            when 0 then REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
                            when 2 then REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT
                            else        REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT
                        end as BASETAXCLAIMMOUNT,

                        case dbo.UFN_REVENUESPLITGIFTAID_TRANSITIONALREFLIEFEXPIRED(REVENUESPLITGIFTAID.ID, REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE) 
                            when 1 then 0 
                            else 
                                case @CURRENCYCODE
                                    when 0 then REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT
                                    when 2 then REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT
                                    else        REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT
                                end 
                        end as TRANSITIONALTAXCLAIMAMOUNT,

                        REVENUESPLITGIFTAID.TAXCLAIMNUMBER,                        
                        REVENUEREFERENCE.REFERENCE

                    from dbo.FINANCIALTRANSACTION
                    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                    inner join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) as ELIGIBLEREVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = ELIGIBLEREVENUESPLITGIFTAID.ID
                    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                    left join dbo.REVENUEREFERENCE on FINANCIALTRANSACTION.ID = REVENUEREFERENCE.ID
                    where
                        FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID and
                        FINANCIALTRANSACTION.DELETEDON is null and
                        FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
                        FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
                        (@STARTDATEEARLIESTTIME is null or cast(FINANCIALTRANSACTION.DATE as datetime) >= @STARTDATEEARLIESTTIME) and
                        (@ENDDATELATESTTIME is null or cast(FINANCIALTRANSACTION.DATE as datetime) <= @ENDDATELATESTTIME) and
                        (@SELECTIONID is null or FINANCIALTRANSACTIONLINEITEM.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID))) and
                        (@SITEFILTERMODE = 0
                            or exists
                            (
                                select 1
                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVENUESPLITSITE
                                inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTEDXML) SITEFILTER on REVENUESPLITSITE.SITEID = SITEFILTER.SITEID
                            )
                        ) and

                        -- Record security filter

                        (@ISADMIN = 1 or 
                            @APPUSER_IN_NONRACROLE = 1 or
                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, FINANCIALTRANSACTION.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)

                        -- Site security filter

                        and exists
                        (
                            select HASPERMISSION
                            from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
                            cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, 'cefcbe67-f272-411f-bede-e686fb8ae39f', REVSITES.SITEID)
                        )
                )

                select
                    DATE,
                    APPLICATION,
                    RATE,                    
                    SPLITAMOUNT + TAXCLAIMAMOUNT as GROSSAMOUNT,

                    BASETAXCLAIMAMOUNT,
                    TRANSITIONALTAXCLAIMAMOUNT,
                    SPLITAMOUNT,
                    TAXCLAIMNUMBER,

                    case    
                        when coalesce(TAXCLAIMNUMBER, '') = '' then TAXCLAIMAMOUNT
                        else 0.0
                    end as OUTSTANDINGCLAIMAMOUNT,

                    REFERENCE,
                    CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCYPROPERTIES.DECIMALDIGITS
                from 
                    TAXCLAIM_CTE    
                outer apply
                    dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@ORGANIZATIONCURRENCYID,TAXCLAIM_CTE.CURRENCYID)) CURRENCYPROPERTIES                        
                order by
                    DATE, APPLICATION, GROSSAMOUNT