USP_R68_GENERATER68DETAIL

            CREATE procedure dbo.USP_R68_GENERATER68DETAIL
            (
                @PROCESSID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as 
            set nocount on;

            declare @IDSETREGISTERID uniqueidentifier
            declare @STARTDATE date
            declare @ENDDATE date
            declare @INCLUDEPOSTEDGIFTS bit
            declare @REFERENCENUMBER nvarchar(20)
            declare @SITESBYREFERENCENUMBER as table(SITEID uniqueidentifier)
            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)

            select
                @IDSETREGISTERID = IDSETREGISTERID,
                @STARTDATE = STARTDATE,
                @ENDDATE = ENDDATE,
                @INCLUDEPOSTEDGIFTS = INCLUDEPOSTEDGIFTS,
                @REFERENCENUMBER = REFERENCENUMBER
            from dbo.R68
            where ID = @PROCESSID

            insert into @SITESBYREFERENCENUMBER
            select SITEID 
            from dbo.UFN_R68_SITESBYREFERENCENUMBER(@REFERENCENUMBER)

            if @CHANGEAGENTID is null  
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            set nocount off;

            if @IDSETREGISTERID is not null
                begin
                    insert into dbo.R68DETAIL 
                        (R68ID, 
                        REVENUESPLITID, 
                        CONSTITUENTID, 
                        NAME, 
                        DATE, 
                        AMOUNTRECEIVED, 
                        AMOUNTCLAIMED, 
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED) 
                    select 
                        @PROCESSID, 
                        rs.ID, 
                        r.CONSTITUENTID, 
                        dbo.UFN_CONSTITUENT_BUILDNAME(r.constituentid), 
                        r.DATE, 
                        rs.AMOUNT, 
                        rsga.BASETAXCLAIMAMOUNT, 
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        GETDATE(), 
                        GETDATE() 
                    from dbo.REVENUE r 
                    inner join dbo.REVENUESPLIT rs on r.ID = rs.REVENUEID 
                    inner join dbo.REVENUESPLITGIFTAID rsga on rs.ID = RSGA.id 
                    inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) as s
                        on rs.ID = s.ID        
                    left join dbo.REVENUEPOSTED rp on rp.ID = r.ID
                    where r.TRANSACTIONTYPECODE = 0
                    and rsga.TAXCLAIMNUMBER = ''
                    and rsga.BASETAXCLAIMAMOUNT > 0
                    and (@STARTDATE is null or r.DATE >= @STARTDATE )
                    and (@ENDDATE is null or r.DATE <= @ENDDATE )
                    and (@INCLUDEPOSTEDGIFTS = 1 or rp.ID is null)
                    and rsga.DECLINESGIFTAID = 0
                    and rsga.RULES_STATUS = 1
                    and rsga.ATTRIBUTES_STATUS = 1 
                    and dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITY(rs.ID) = 'Valid declaration'
                    and exists (
                          select REVSITES.SITEID
                          from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(rs.ID) REVSITES
                          join @SITESBYREFERENCENUMBER REFSITES on (REVSITES.SITEID = REFSITES.SITEID) or (REVSITES.SITEID is null and REFSITES.SITEID is null)
                          --where (dbo.UFN_APPUSER_ISSYSADMIN(@CHANGEAGENTID) = 1 

                                --or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CHANGEAGENTID, '5F83E4C6-7CAD-4b2c-95A6-506BB18130AB', 4) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))

                          )
                    -- Record security filter

                    and (@ISADMIN = 1 or 
                        @APPUSER_IN_NONRACROLE = 1 or
                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, r.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1) 
                    -- Site security filter

                    and dbo.UFN_REVENUE_USERHASSITEACCESS(r.ID, @CURRENTAPPUSERID) = 1
                end
            else
                begin
                    insert into dbo.R68DETAIL 
                        (R68ID, 
                        REVENUESPLITID, 
                        CONSTITUENTID, 
                        NAME, 
                        DATE, 
                        AMOUNTRECEIVED, 
                        AMOUNTCLAIMED, 
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED) 
                    select 
                        @PROCESSID, 
                        rs.ID, 
                        r.CONSTITUENTID, 
                        dbo.UFN_CONSTITUENT_BUILDNAME(r.constituentid), 
                        r.DATE, 
                        rs.AMOUNT, 
                        rsga.BASETAXCLAIMAMOUNT, 
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        GETDATE(), 
                        GETDATE() 
                    from dbo.REVENUE r 
                    inner join dbo.REVENUESPLIT rs on r.ID = rs.REVENUEID 
                    inner join dbo.REVENUESPLITGIFTAID rsga on rs.ID = RSGA.id 
                    left join dbo.REVENUEPOSTED rp on rp.ID = r.ID
                    where r.TRANSACTIONTYPECODE = 0
                    and rsga.TAXCLAIMNUMBER = ''
                    and rsga.BASETAXCLAIMAMOUNT > 0
                    and (@STARTDATE is null or r.DATE >= @STARTDATE )
                    and (@ENDDATE is null or r.DATE <= @ENDDATE )
                    and (@INCLUDEPOSTEDGIFTS = 1 or rp.ID is null)
                    and rsga.DECLINESGIFTAID = 0
                    and rsga.RULES_STATUS = 1
                    and rsga.ATTRIBUTES_STATUS = 1 
                    and dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITY(rs.ID) = 'Valid declaration'
                    and exists (
                          select REVSITES.SITEID
                          from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(rs.ID) REVSITES
                          join @SITESBYREFERENCENUMBER REFSITES on (REVSITES.SITEID = REFSITES.SITEID) or (REVSITES.SITEID is null and REFSITES.SITEID is null)
                          --where (dbo.UFN_APPUSER_ISSYSADMIN(@CHANGEAGENTID) = 1 

                                --or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CHANGEAGENTID, '5F83E4C6-7CAD-4b2c-95A6-506BB18130AB', 4) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))

                          )
                    -- Record security filter

                    and (@ISADMIN = 1 or 
                        @APPUSER_IN_NONRACROLE = 1 or
                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, r.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1) 
                    -- Site security filter

                    and dbo.UFN_REVENUE_USERHASSITEACCESS(r.ID, @CURRENTAPPUSERID) = 1
                end