UPS_REPORT_SOLICITORREVENUE

            CREATE procedure dbo.UPS_REPORT_SOLICITORREVENUE(
                @STARTDATE datetime,
                @ENDDATE datetime,
                @BREAKDOWN bit = null,
                @BUSINESSUNITCODEID uniqueidentifier = null,    
                @APPEALCATEGORYCODEID uniqueidentifier = null,    
                @APPEALREPORT1CODEID uniqueidentifier = null,
                @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @REPORTUSERID nvarchar(128) = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as owner
            as
                set nocount on;

                if @CURRENTAPPUSERID is null
                    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);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                if @ORGPOSITIONSSELECTIONID is null
                begin
                    select
                        S.ID as SOLICITORID,
                        S_NF.NAME as SOLICITORNAME,
                        D.ID as DESIGNATIONID,
                        D.NAME as DESIGNATIONNAME,
                        R.AMOUNT as REVENUEAMOUNT,
                        RSOL.AMOUNT as SOLICITORAMOUNT,
                        R.CONSTITUENTID as DONORID,
                        R.ID as GIFTID,
                        MAXSOLGIFTAMOUNT
                    from dbo.REVENUESOLICITOR RSOL
                        inner join dbo.REVENUESPLIT RS on RSOL.REVENUESPLITID = RS.ID
                        inner join dbo.REVENUE R with (nolock) on RS.REVENUEID = R.ID
                        left join dbo.APPEAL A on R.APPEALID = A.ID
            --left join dbo.REVENUESPLITBUSINESSUNIT RBU on RBU.REVENUESPLITID = RS.ID

                        inner join dbo.DESIGNATION D on RS.DESIGNATIONID = D.ID
                        inner join dbo.CONSTITUENT S with (nolock) on RSOL.CONSTITUENTID = S.ID
                        inner join 
                        (
                            select CONSTITUENTID, MAX(SOLGIFTAMOUNT) MAXSOLGIFTAMOUNT
                            from 
                            (    select  REVENUESOLICITOR.CONSTITUENTID, SUM(REVENUESOLICITOR.AMOUNT) SOLGIFTAMOUNT
                                from REVENUESOLICITOR
                                inner join REVENUESPLIT on REVENUESPLIT.ID = REVENUESOLICITOR.REVENUESPLITID
                                inner join dbo.REVENUE with (nolock) on REVENUESPLIT.REVENUEID = REVENUE.ID
                                left join dbo.APPEAL on REVENUE.APPEALID = APPEAL.ID
                    --left join dbo.REVENUESPLITBUSINESSUNIT on REVENUESPLITBUSINESSUNIT.REVENUESPLITID = REVENUESPLIT.ID

                                where
                                    dbo.UFN_REVENUE_HASDESIGNATION(REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.APPLICATIONCODE) = 1 and
                                    (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                                    (REVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                                    (REVENUESOLICITOR.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                                    (APPEAL.APPEALCATEGORYCODEID = @APPEALCATEGORYCODEID or @APPEALCATEGORYCODEID is null) and
                                    (APPEAL.APPEALREPORT1CODEID = @APPEALREPORT1CODEID or @APPEALREPORT1CODEID is null)
                                    and (@ISADMIN = 1
                                        or dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(REVENUE.ID, @CURRENTAPPUSERID) = 1
                                    )
                                group by REVENUESOLICITOR.CONSTITUENTID, REVENUESPLIT.REVENUEID
                            ) SOLREVSUMTBL
                            group by SOLREVSUMTBL.CONSTITUENTID
                        ) SOLREVMAXTBL on SOLREVMAXTBL.CONSTITUENTID = RSOL.CONSTITUENTID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(S.ID) S_NF
                    where 
                        dbo.UFN_REVENUE_HASDESIGNATION(R.TRANSACTIONTYPECODE, RS.APPLICATIONCODE) = 1 and
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ENDDATE or @ENDDATE is null) and
                        (RSOL.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                        (A.APPEALCATEGORYCODEID = @APPEALCATEGORYCODEID or @APPEALCATEGORYCODEID is null) and
                        (A.APPEALREPORT1CODEID = @APPEALREPORT1CODEID or @APPEALREPORT1CODEID is null) and
                        (@ISADMIN = 1 or
                            (
                                (@APPUSER_IN_NONRACROLE = 1 
                                    or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, S.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
                                )
                                and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID, @CURRENTAPPUSERID) = 1
                            )
                        )

                end
                else
                begin
                    declare @IDS as table(ID uniqueidentifier);
                    insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

                    select
                        S.ID as SOLICITORID,
                        S.NAME as SOLICITORNAME,
                        D.ID as DESIGNATIONID,
                        D.NAME as DESIGNATIONNAME,
                        R.AMOUNT as REVENUEAMOUNT,
                        RSOL.AMOUNT as SOLICITORAMOUNT,
                        R.CONSTITUENTID as DONORID,
                        R.ID as GIFTID,
                        MAXSOLGIFTAMOUNT
                    from dbo.REVENUESOLICITOR RSOL
                        inner join dbo.REVENUESPLIT RS on RSOL.REVENUESPLITID = RS.ID
                        inner join dbo.REVENUE R with (nolock) on RS.REVENUEID = R.ID
                        left join dbo.APPEAL A on R.APPEALID = A.ID
            --left join dbo.REVENUESPLITBUSINESSUNIT RBU on RBU.REVENUESPLITID = RS.ID

                        inner join dbo.DESIGNATION D on RS.DESIGNATIONID = D.ID
                        inner join dbo.CONSTITUENT S with (nolock) on RSOL.CONSTITUENTID = S.ID
                        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                            on OPH.CONSTITUENTID = S.ID and R.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, R.DATE)
                        inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                        inner join 
                        (
                            select CONSTITUENTID, MAX(SOLGIFTAMOUNT) MAXSOLGIFTAMOUNT
                            from 
                            (    select  REVENUESOLICITOR.CONSTITUENTID, SUM(REVENUESOLICITOR.AMOUNT) SOLGIFTAMOUNT
                                from REVENUESOLICITOR
                                inner join REVENUESPLIT on REVENUESPLIT.ID = REVENUESOLICITOR.REVENUESPLITID
                                inner join dbo.REVENUE with (nolock) on REVENUESPLIT.REVENUEID = REVENUE.ID
                                left join dbo.APPEAL on REVENUE.APPEALID = APPEAL.ID
                    --left join dbo.REVENUESPLITBUSINESSUNIT on REVENUESPLITBUSINESSUNIT.REVENUESPLITID = REVENUESPLIT.ID

                                where
                                    dbo.UFN_REVENUE_HASDESIGNATION(REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.APPLICATIONCODE) = 1 and
                                    (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                                    (REVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                                    (REVENUESOLICITOR.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                                    (APPEAL.APPEALCATEGORYCODEID = @APPEALCATEGORYCODEID or @APPEALCATEGORYCODEID is null) and
                                    (APPEAL.APPEALREPORT1CODEID = @APPEALREPORT1CODEID or @APPEALREPORT1CODEID is null)
                                    and (@ISADMIN = 1
                                        or dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(REVENUE.ID, @CURRENTAPPUSERID) = 1
                                    )
                                group by REVENUESOLICITOR.CONSTITUENTID, REVENUESPLIT.REVENUEID
                            ) SOLREVSUMTBL
                            group by SOLREVSUMTBL.CONSTITUENTID
                        ) SOLREVMAXTBL on SOLREVMAXTBL.CONSTITUENTID = RSOL.CONSTITUENTID
                    where 
                        dbo.UFN_REVENUE_HASDESIGNATION(R.TRANSACTIONTYPECODE, RS.APPLICATIONCODE) = 1 and 
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ENDDATE or @ENDDATE is null) and
                        (RSOL.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                        (A.APPEALCATEGORYCODEID = @APPEALCATEGORYCODEID or @APPEALCATEGORYCODEID is null) and
                        (A.APPEALREPORT1CODEID = @APPEALREPORT1CODEID or @APPEALREPORT1CODEID is null) and
                        (@ISADMIN = 1 or
                            (
                                (@APPUSER_IN_NONRACROLE = 1 
                                    or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, S.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
                                )
                                and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID, @CURRENTAPPUSERID) = 1
                            )
                        )
                end